Working with Tables - Part I

Creating Tables

To create table we use the CREATE TABLE statement. The usual form of this statement is:

CREATE TABLE [IF NOT EXISTS] table_name(
  column_list
  ) type=table_type

MySQL supports IF NOT EXISTS after CREATE TABLE statement to prevent you from error to create table which already exists on the database server. table_name is the name of table you would like to create. After that, you can define a set of columns which is usually in this form: column_name data_type(size) [NOT] NULL. And finally, you can specify the storage engine type you prefer to use for the table. MySQL supports various storage engines such as InnoDB, MyISAM... If you don't explicit declare storage engine type, MySQL will use MyISAM by default.

In our classicmodels sample database, to create employees table we can apply the statement above as follows:

CREATE TABLE employees (             
            employeeNumber into(11) NOT NULL,   
            lastName varchar(50) NOT NULL,     
            firstName varchar(50) NOT NULL,    
            extension varchar(10) NOT NULL,    
            email varchar(100) NOT NULL,       
            officeCode varchar(10) NOT NULL,   
            reportsTo int(11) default NULL,    
            jobTitle varchar(50) NOT NULL,     
            PRIMARY KEY  (employeeNumber)      
          );

You specify table name employees after CREATE TABLE statement. Then columns list with its characteristics followed such as data type, size, NOT NULL or not; And finally you can specify the primary key of the table, in this case the primary key is employeeNumber. If the table has more than one primary key, you can seperate them by a comma. For example, the payments table has two primary keys customerNumber and checkNumber, you can create it by execute following query:

CREATE TABLE payments (                       
           customerNumber int(11) NOT NULL,            
           checkNumber varchar(50) NOT NULL,           
           paymentDate datetime NOT NULL,              
           amount double NOT NULL,                     
           PRIMARY KEY  (customerNumber,checkNumber) 
         );

By default, we use MyISAM storage engine for the table we created.

Showing and Describing Tables in a Database

In order to show all tables in a database you use SHOW TABLES statment, the server will returns all tables name of the current selected database you work with.

SHOW TABLES

Here is the output of classicmodels database

+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers               |
| employees               |
| offices                 |
| orderdetails            |
| orders                  |
| payments                |
| productlines            |
| products                |
+-------------------------+
8 rows in set (0.00 sec)

In some cases, you need to see the table characteristics or table metadata, you can use DESCRIBE statement as follows:

DESCRIBE employees;

for instance, we can describe employees table like this query

DESCRIBE employees;

The output return from the database server:

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11)      | NO   | PRI | NULL    |       |
| lastName       | varchar(50)  | NO   |     | NULL    |       |
| firstName      | varchar(50)  | NO   |     | NULL    |       |
| extension      | varchar(10)  | NO   |     | NULL    |       |
| email          | varchar(100) | NO   |     | NULL    |       |
| officeCode     | varchar(10)  | NO   |     | NULL    |       |
| reportsTo      | int(11)      | YES  |     | NULL    |       |
| jobTitle       | varchar(50)  | NO   |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
8 rows in set (0.02 sec)