» Basic MySQL Tutorial » Working with Tables - Part I

Working with Database Table - Part I

Summary: In this tutorial you will learn how to create, show and describe tables in MySQL.

Database table or table in short is one of  the most important objects of relational database. How to create databases tables correctly is crucial when you work with any database system especially MySQL. In this tutorial, you will learn how to create a simple database table. You'll also learn how to list all database tables of a specific database.

Creating Tables

To create table we use the CREATE TABLE statement. The typical form of SQL CREATE TABLE statement is as follows:

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 of creating 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.
  • 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 use the CREATE TABLE statement 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)       
           );

First, you specify table name employees after CREATE TABLE statement.

Then you list the columns of the table with their attributes such as data type, size, NOT NULL.

And finally you 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 payments table by executing 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)  
          );

Note taht by default MySQL uses MyISAM storage engine for the table it created.

Showing and Describing Tables in a Database

In order to show all tables in a database, you use SHOW TABLES statment. By executing the SHOW TABLES statement, MySQL will returns all tables' name of the current selected database you're working 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's metadata, you can use DESCRIBE statement as follows:

DESCRIBE table_name;

For instance, we can describe employees table like below 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)
Related Tutorials
Search