Home

Data definition language (DDL)

The language known as “SQL” usually refers to the various CRUD operations: select, insert, update, delete. A separate language known as “DDL” allows us to create tables and relations between tables.

Creating tables

Here is the grammar for creating a table:

CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]

create_definition:
    col_name column_definition

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE | PRIMARY KEY]

data_type:
    BIT[(length)]
  | INT
  | FLOAT
  | DECIMAL[(length[,decimals])]
  | DATE
  | TIME
  | TIMESTAMP
  | YEAR
  | VARCHAR(length)
  | TEXT

Simplified from MySQL docs.

The grammar shows that the “create definition” (i.e., column names and types) must be in parentheses, while optional “table options” may follow.

Column types

Numeric types:

Date/time types:

String types:

Column attributes

Besides the column name and type, a column can have additional attributes:

Table options

Deleting tables

Tables may deleted with the “drop” command:

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...

One or multiple table names may be provided.

Altering tables

ALTER TABLE tbl_name
    alter_specification, ...

alter_specification:
   ADD COLUMN col_name col_definition
 | ADD INDEX index_name (col_name, ...)
 | ADD PRIMARY KEY (col_name, ...)
 | ADD UNIQUE (col_name, ...)
 | ADD FOREIGN KEY fk_index_name (col_name, ...) REFERENCES tbl_name (col_name, ...)
 | CHANGE old_col_name new_col_name col_definition
 | MODIFY col_name col_definition
 | DROP col_name
 | DROP PRIMARY KEY
 | DROP INDEX index_name
 | DROP FOREIGN KEY fk_index_name
 | RENAME new_tbl_name
CINF 201 material by Joshua Eckroth is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. Source code for this website available at GitHub.