CREATE TABLE

-- Creates a table.
CREATE TABLE table_name (
    column1 data_type [constraint],
    column2 data_type [constraint],
    ...
);

-- Creates the table only if it does not already exist.
CREATE TABLE IF NOT EXISTS table_name (
    column1 data_type [constraint],
    ...
);

Data Type	Description
INT	Stores an integer. In MySQL, the range is -2,147,483,648 to 2,147,483,647.
BIGINT	Stores a large integer. Commonly used for ID columns.
DECIMAL(p, s)	Stores a fixed-point number with precision p and s decimal places. Use this for values where precision matters, such as monetary amounts.
VARCHAR(n)	Stores a variable-length string of up to n characters. Suitable for names, email addresses, and similar fields.
TEXT	Stores a long string. Suitable for article bodies, comments, and similar content.
DATE	Stores a date (YYYY-MM-DD).
DATETIME	Stores a date and time (YYYY-MM-DD HH:MM:SS).
TIMESTAMP	Stores a date and time as a timestamp. In MySQL, it can be configured to update automatically.
BOOLEAN	Stores a boolean value (TRUE / FALSE). In MySQL, this is handled internally as TINYINT(1).

-- Creates a members table.
CREATE TABLE IF NOT EXISTS members (
    member_id  INT           NOT NULL AUTO_INCREMENT,
    name       VARCHAR(100)  NOT NULL,
    email      VARCHAR(255)  NOT NULL,
    points     INT           NOT NULL DEFAULT 0,
    registered_at DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (member_id)
);

-- Creates a products table.
CREATE TABLE IF NOT EXISTS products (
    product_id   INT            NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(200)   NOT NULL,
    price        DECIMAL(10, 2) NOT NULL,
    stock        INT            NOT NULL DEFAULT 0,
    created_at   TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id)
);

-- Example output from: SHOW COLUMNS FROM members;
-- +---------------+--------------+------+-----+-------------------+
-- | Field         | Type         | Null | Key | Default           |
-- +---------------+--------------+------+-----+-------------------+
-- | member_id     | int          | NO   | PRI | NULL              |
-- | name          | varchar(100) | NO   |     | NULL              |
-- | email         | varchar(255) | NO   |     | NULL              |
-- | points        | int          | NO   |     | 0                 |
-- | registered_at | datetime     | NO   |     | CURRENT_TIMESTAMP |
-- +---------------+--------------+------+-----+-------------------+

The basic syntax for CREATE TABLE and IF NOT EXISTS is supported across major databases. However, data types and auto-increment syntax differ by database.

-- MySQL
CREATE TABLE IF NOT EXISTS members (
    member_id     INT           NOT NULL AUTO_INCREMENT,
    name          VARCHAR(100)  NOT NULL,
    points        INT           NOT NULL DEFAULT 0,
    registered_at DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (member_id)
);

PostgreSQL uses SERIAL or GENERATED AS IDENTITY for auto-increment columns. The BOOLEAN type is natively supported.

-- PostgreSQL
CREATE TABLE IF NOT EXISTS members (
    member_id     SERIAL        NOT NULL,
    name          VARCHAR(100)  NOT NULL,
    points        INT           NOT NULL DEFAULT 0,
    registered_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (member_id)
);

SQL Server uses IDENTITY for auto-increment columns. IF NOT EXISTS is not supported; use IF NOT EXISTS (SELECT ...) to check for an existing table.

-- SQL Server
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'members')
CREATE TABLE members (
    member_id     INT           NOT NULL IDENTITY(1,1),
    name          NVARCHAR(100) NOT NULL,
    points        INT           NOT NULL DEFAULT 0,
    registered_at DATETIME2     NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY (member_id)
);

SQLite has flexible data type handling. Declaring a column as INTEGER PRIMARY KEY makes it auto-increment automatically.

-- SQLite
CREATE TABLE IF NOT EXISTS members (
    member_id     INTEGER  PRIMARY KEY,
    name          TEXT     NOT NULL,
    points        INTEGER  NOT NULL DEFAULT 0,
    registered_at TEXT     NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE is classified as DDL (Data Definition Language). When creating a table, it is important to choose an appropriate data type for each column. For string data, choose between VARCHAR and TEXT based on your use case: use VARCHAR when there is a known character limit (such as names or email addresses), and TEXT when the length is open-ended (such as article bodies or descriptions).

Adding IF NOT EXISTS prevents an error if the table already exists. This is useful in migration scripts where you want idempotent behavior.

For details on constraints (NOT NULL, UNIQUE, PRIMARY KEY, etc.), see NOT NULL / UNIQUE / DEFAULT and PRIMARY KEY / FOREIGN KEY. To modify an existing table definition, see ALTER TABLE.