SQL CREATE TABLE Statement

Syntax

The CREATE TABLE statement is used for creating a new table in SQL.

Here is the generic syntax:

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

You have to specify the name of the table, the name of the columns, and the corresponding data type for each column.

You can also copy the definition from an existing table to create a new table:

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

Example

Create a new table from scratch:

CREATE TABLE employees (
 emp_no int(11) NOT NULL,
 birth_date date NOT NULL,
 first_name varchar(14) NOT NULL,
 last_name varchar(16) NOT NULL,
 gender enum('M','F') NOT NULL,
 hire_date date NOT NULL,
 email text,
);

So we have table employees with 7 columns: emp_no, birth_date, first_name, last_name, gender, hire_date, email.

Create a new table by copying an existing table:

CREATE TABLE simple_employees AS
SELECT emp_no, first_name, last_name
FROM employees;

And we have a new table simple_employees with three columns: emp_no, first_name, last_name. The definitions of these three columns match with those from original table employees.

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS