Data definition language

The create command is used to establish a new database, table, index, or stored procedure.

The CREATE statement in SQL creates a component in a relational database management system (RDBMS). In the SQL 1992 specification, the types of components that can be created are schemas, tables, views, domains, character sets, collations, translations, and assertions.[2] Many implementations extend the syntax to allow creation of additional elements, such as indexes and user profiles. Some systems, such as PostgreSQL and SQL Server, allow CREATE, and other DDL commands, inside a database transaction and thus they may be rolled back.[3][4]

A commonly used CREATE command is the CREATE TABLE command. The typical usage is:

CREATE TABLE [table name] ( [column definitions] ) [table parameters]

The column definitions are:

• A comma-separated list consisting of any of the following
• Column definition: [column name] [data type] {NULL | NOT NULL} {column options}
• Primary key definition: PRIMARY KEY ( [comma separated column list] )
• Constraints: {CONSTRAINT} [constraint definition]
• RDBMS specific functionality

An example statement to create a table named employees with a few columns is:

CREATE TABLE employees (
    id            INTEGER       PRIMARY KEY,
    first_name    VARCHAR(50)   not null,
    last_name     VARCHAR(75)   not null,
    mid_name      VARCHAR(50)   not null,
    dateofbirth   DATE          not null
);

Some forms of CREATE TABLE DDL may incorporate DML (data manipulation language)-like constructs, such as the CREATE TABLE AS SELECT (CTaS) syntax of SQL.[5]

The DROP statement destroys an existing database, table, index, or view.

A DROP statement in SQL removes a component from a relational database management system (RDBMS). The types of objects that can be dropped depends on which RDBMS is being used, but most support the dropping of tables, users, and databases. Some systems (such as PostgreSQL) allow DROP and other DDL commands to occur inside of a transaction and thus be rolled back. The typical usage is simply:

DROP objecttype objectname.

For example, the command to drop a table named employees is:

DROP TABLE employees;

The DROP statement is distinct from the DELETE and TRUNCATE statements, in that DELETE and TRUNCATE do not remove the table itself. For example, a DELETE statement might delete some (or all) data from a table while leaving the table itself in the database, whereas a DROP statement removes the entire table from the database.

The ALTER statement modifies an existing database object.

An ALTER statement in SQL changes the properties of an object inside of a relational database management system (RDBMS). The types of objects that can be altered depends on which RDBMS is being used. The typical usage is:

ALTER objecttype objectname parameters.

For example, the command to add (then remove) a column named bubbles for an existing table named sink is:

ALTER TABLE sink ADD bubbles INTEGER;
ALTER TABLE sink DROP COLUMN bubbles;

The TRUNCATE statement is used to delete all data from a table. It's much faster than DELETE.

TRUNCATE TABLE table_name;

Another type of DDL sentence in SQL is used to define referential integrity relationships, usually implemented as primary key and foreign key tags in some columns of the tables. These two statements can be included in a CREATE TABLE or an ALTER TABLE sentence;