MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

MySQL - CREATE TABLE



The MySQL CREATE TABLE statement is used to create a new table. Creating a table involves providing a name to the table and defining name and data type (e.g. varchar, integer, date, etc.) of each column.

Syntax

The syntax of using CREATE TABLE statement is given below:

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  column3 datatype,
  .....
  .....
);

Example: Create a table

The below mentioned query creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary.

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255) NOT NULL,
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

In the above query, the data type specifies what type of data the column can hold. After the data type, optional attributes can be provided for each column, like NOT NULL is used to ensure that a column cannot have a NULL value. See MySQL Constraints for more information.

The above query will create a empty table named Employee containing five columns as shown below:

EmpIDNameCityAgeSalary

When the table is created successfully it displays a message. Along with this, it can also be checked using DESC command as follows:

DESC Employee;

FieldTypeNullKeyDefaultExtra
EmpIDint(11)No
Namevarchar(255)No
Cityvarchar(100)YesNULL
Ageint(11)YesNULL
Salarydecimal(18,2)YesNULL

This indicates that the Employee is now available in the database which can be used to store information related to employee.

Example: Create a table using another table

A new table can be created using existing table in the database. Consider a database table called Employee with the following records.

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

In the example below, a new table called New_Employee_table is created using this table.

CREATE TABLE New_Employee_table AS
  SELECT  EmpID, Name, City, Age
  FROM Employee;

-- see the result
SELECT * FROM New_Employee_table;

This result of the following code will be:

EmpIDNameCityAge
1JohnLondon25
2MarryNew York24
3JoParis27
4KimAmsterdam30
5RameshNew Delhi28
6HuangBeijing28

5