Course
Create Tables
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
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
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