CREATE TABLE Syntax

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
) engine=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.

Example

CREATE TABLE IF NOT EXISTS `student` (
  `student_id` int(11) NOT NULL,
  `first_name` varchar(20) NOT NULL,
  `last_name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `email_id` varchar(20) DEFAULT NULL,
  `contact_no` varchar(20) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`),
  UNIQUE KEY `email_id` (`email_id`)
) ;

First, you specify table name students 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 student_id. If the table has more than one primary key, you can separate them by a comma.

In order to show all tables in a database, you use SHOW TABLES statement. By executing the SHOW TABLES statement, MySQL will returns all tables’ name of the current selected database you’re working with.

SHOW TABLES

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 student;

The output return from the database server: