MySQL Create Table Commands

Create Table command is used for creating tables in mysql.

Syntax 1:
CREATE TABLE [IF NOT EXISTS] table_name(
column_name1 type(size) constraints,
column_name2 type(size) constraints,
------------------------------------
------------------------------------
);

Constraints of tables: NOT NULL, AUTO_INCREMENT, PRIMARY KEY,UNIQUE
Note: The sequence of Constraints will be : NOT NULL, AUTO_INCREMENT, PRIMARY KEY,UNIQUE

Example:
CREATE TABLE student(
st_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
st_name varchar(200),
st_father varchar(200) NOT NULL
);

Syntax 2:
CREATE TABLE table_name(
column_name1 type(size) constraints,
column_name2 type(size) constraints,
------------------------------------
------------------------------------
column_name2 type(size) constraints,
PRIMARY KEY(column_name)
);

Example:
CREATE TABLE student(
st_id int(11) NOT NULL AUTO_INCREMENT,
st_name varchar(200),
st_father varchar(200) NOT NULL, PRIMARY KEY(st_id)
);

Syntax 3:
CREATE TABLE table_name SELECT column1,column2,column3 FROM table_name WHERE condition;

Note 1: The above syntax will create the backup of existing table, with the data structure and contents.

Note 2: The above command will not copy auto incremented fields and the primary key constraints.

Example:
CREATE TABLE student_backup SELECT * FROM student;

Show the structure of the table:

Syntax 1: DESC table_name;
Example: DESC student;

Syntax 2: EXPLAIN table_name;
Example: EXPLAIN student;

Syntax 3: EXPLAIN table_name;
Example: EXPLAIN student;

Note: EXPLAIN, DESC, DESCRIBE works as same way.

Syntax 4: SHOW CREATE TABLE table_name;
Example: SHOW CREATE TABLE student;