SQL CREATE TABLE Statement

Initializing a table in a database

The CREATE TABLE statement is used to create a new table in a database. Contents of the relation and the relation itself are stored as files by the DBMS (known as "base relations").

Syntax:

CREATE TABLE table_name(
	column1 datatype constraint, // e.g. NOT NULL
	column2 datatype constraint, // can list more than 1 constraint
	column3 datatype constraint1 constraint2 ...,
	...
)

The column parameters specify the names of the columns or fields of the table.
The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

SQL Datatypes

An SQL developer must decide what type of data that will be stored inside each column when creating a table.
This serves as a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.

See reference of MySQL datatypes

Alternatively, we can also specify the schema (or database), in which we want to create the table object:

CREATE TABLE dbName.tableName (
	column1 datatype constraint,
	...
)

Example:

CREATE TABLE Persons (
    PersonID int NOT NULL,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) 
);

Creating a new table from existing table

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions.

All columns or specific columns can be selected.

Copied Data

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

Selecting `all columns`

CREATE TABLE new_table_name AS
    SELECT * FROM existing_table_name
    WHERE ....

Selecting `specific columns`

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