While there are many GUI methods to manage a database, such as PHPMyAdmin, the best way for programmers to get started in MySQL is to write
the code in the command line. This will help greatly as you progress to writing your MySQL code in php to form the bridge between the server
and the client.
The basic terminology used in database management includes the terms database, table and field. Think of a database as an Excel document.
Each excel document has a variety of information and data that is, in some way, related. Inside the document, we often have several tabs,
each of these represents a table. Each table has its own purpose and may refer to other tables, but can also stand on its own. Within each
table, we often label the columns as to the specific information that is to be stored in the table. These column headers are the fields.
Database Commands
Before we can start implementing tables and fields, we need to learn how to create and manage our databases. Here are the basic database commands for MySQL:
show databases; //shows a list of all databases
create database dbName; //used to create database dbName
use dbName; //make dbName the active database
DROP DATABASE dbName; //deletes an entire database
Typically, programmers will create a database, make sure that it is in the active records and then select the database to be used. That code may look like this:
mysql> create database accounting;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| accounting |
| c9 |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
mysql> use accounting;
Database changed
Table Commands
Once operating in a database, we need to use the tables available to us. The following commands are useful in managing tables in a database
show tables; //shows a list of all tables in the active database
create table tblName(varname TYPE, varname TYPE, etc); //creates a table with elements
describe tblName; //get information about the table
DROP TABLE tblName; //deletes the entire table (structure and data)
TRUNCATE TABLE tblName; //deletes all data from a table
It is important to note that you must assign field names and types at the time of creation. It is possible to alter the structure of the table at a
later time if necessary, but it is better to plan ahead of time and create it originally with all necessary fields. Let's create the client table of our
accounting database. We will first show the tables to make sure that it is not already created, and then we will create it with an abbreviated version of
the contents. Finally, we will ask the server to describe the table.
mysql> show tables;
Empty set (0.00 sec)
mysql> create table clients(
-> id int(5) unsigned not null primary key,
-> name tinytext not null,
-> bday date);
Query OK, 0 rows affected (0.05 sec)
mysql> describe clients;
+-------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| id | int(5) unsigned | NO | PRI | NULL | |
| name | tinytext | NO | | NULL | |
| bday | date | YES | | NULL | |
+-------+-----------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
See below for more information about creating fields (lines5-7) in the create table command.
Creating Fields
When creating a table, the fields of that table need to be created at the same time. At minimum, a field needs to have a name and a dataype (bday date).
However, there are many other qualifiers that can be added, including:
unsigned //extends the range of the data by using only positive numbers
not null //forces a non-null value to be input
auto increment //creates a new, sequential value based on the last input
primary key //identifies the field as holding a unique value in the table for record identification purposes
The general format of a field creation is: fieldName fieldType(length) [NOT NULL] [DEFAULT val] [AUTO_INCREMENT][PRIMARY KEY]
Field Types
Here are a few of the more commonly used field types for use in field creation:
CHAR[length] //a fixed-length field from 0 to 255 characters
VARCHAR(length) //a fixed-length field from 0 to 255 characters
TINYTEXT //a string with a max length of 255 characters
TEXT //a string with a maximum length of 65,535 characters
MEDIUMTEXT //a string with a max length of 16, 777, 215 characters
LONGTEXT //a string with a max length of 4,294,967,295 characters
FLOAT //a small number with a floating decimal point
DOUBLE //a large number with a floating decimal point
DATE //in the format of YYYY-MM-DD
DATETIME //in the format of YYYY-MM-DD HH:MM:SS
TIMESTAMP //in the format of YYYYMMDDHHMMSS; up to year 2037
TIME //in the format of HH:MM:SS
Inserting Data
Once a table has been created and populated with fields, we can use the INSERT INTO command to populate the table with
data. There are two methods of doing this.
Method 1
INSERT INTO tableName VALUES (value1, value2, value3, etc.);
In this format, the values must be entered in the same format as the fields are listed in the table AND ALL values must be listed.
OR
Method 2
INSERT INTO tableName (col1Name, col2Name, etc) VALUES (value1, value2, etc); //to enter only certain pieces of data
In this format, the programmer can pick and choose which fields get values. The only restriction is that the order of the fields (columns)
after the table name, must match the order of the values after VALUES.
While Method 1 looks like simpler syntax, it presents a possible problem. If you have to list all of the fields in any insert statement, what happens if, at a
future date, the programmers make adjustments to the table structure. This would mean going back and locating/fixing ALL insert statements anywhere in the program.
Method 2 negates this need as it specifies which fields to use. It would only need to be altered to add more information or if a specific field was deleted.
When assigning values, keep the following in mind:
VARCHAR values need to be in single quotes
Fields that are AUTO INCREMENT should be sent a value of NULL
DATE values need to be entered in YYYY-MM-DD format with single quotes