MySQL - CREATE TABLE Statement



MySQL CREATE TABLE Statement

The CREATE TABLE statement is used to create tables in MYSQL database. Here, you need to specify the name of the table and, definition (name and datatype) of each column.

Syntax

Following is the syntax to create a table in MySQL −

CREATE TABLE [IF NOT EXISTS] table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

Where, table_name is the name of the table you need to create, column1, column2, column3, ……..… columnN are the names of the columns and datatype is the name of the datatypes of the respective columns.

Example

Following query creates a table with name Employee −

CREATE TABLE Employee(
   Name VARCHAR(255),
   Salary INT NOT NULL,
   Location VARCHAR(255)
);

The SHOW TABLES statements gives you the list of tables in the current database, if the creation is successful, you can see the name of the above created table in it.

show tables;

Output

The above query produces the following output −

Tables_in_sample
employee

The IF NOT EXISTS clause

If you try to create a table with an existing name an error will be generated −

CREATE TABLE Employee(Name VARCHAR(255));
ERROR 1050 (42S01): Table 'employee' already exists

If you use the IF NOT EXISTS clause along with the CREATE statement as shown below a new table will be created and if a table with the given name, already exists the query will be ignored.

CREATE TABLE Test(Name VARCHAR(255));

Creating a table using an existing one

You can also create a table using the existing table (with same definition), following is the syntax to do so −

CREATE TABLE [IF NOT EXISTS] table_name {LIKE old_table_name}

Where, table_name is the name of the table you need to create and old_table_name is the name of the table from which you need to create the new one.

Example

Following query create a new table sample same as the table Employee

CREATE TABLE sample LIKE Employee;

You can verify the above created table using the DESC statement too.

DESC sample;

Output

Following is the output of the above program −

Field Type Null Key Default Extra
Name varchar(255) YES NULL
Salary int NO NULL
Location varchar(255) YES NULL
Advertisements