DDL commands create and reshape database objects. They change the schema, so they are usually run through versioned migration scripts, not by hand.

CREATE TABLE

SQL

CREATE TABLE projects (
    project_id  INTEGER PRIMARY KEY,
    title       VARCHAR(100) NOT NULL,
    budget      DECIMAL(12, 2) DEFAULT 0,
    dept_id     INTEGER REFERENCES departments(dept_id),
    started_on  DATE
);

CREATE TABLE IF NOT EXISTS projects (id INTEGER);   -- no error: already exists, skipped

SELECT name, type, "notnull", dflt_value, pk
FROM pragma_table_info('projects')
ORDER BY cid;

Output

name       | type           | notnull | dflt_value | pk
-----------+----------------+---------+------------+---
project_id | INTEGER        | 0       | NULL       | 1
title      | VARCHAR(100)   | 1       | NULL       | 0
budget     | DECIMAL(12, 2) | 0       | 0          | 0
dept_id    | INTEGER        | 0       | NULL       | 0
started_on | DATE           | 0       | NULL       | 0

CREATE TABLE … AS SELECT

Creates a table and fills it from a query — handy for snapshots and backups. Constraints and indexes are not copied.

SQL

CREATE TABLE eng_staff AS
SELECT emp_id, name, salary FROM employees WHERE dept_id = 1;

SELECT * FROM eng_staff ORDER BY emp_id;

Output

emp_id | name  | salary
-------+-------+-------
1      | Asha  | 150000
2      | Ravi  | 120000
3      | Meera | 95000
4      | Karan | 95000

SQL Server (SELECT … INTO)

SELECT emp_id, name, salary
INTO eng_staff
FROM employees WHERE dept_id = 1;

ALTER TABLE

SQL

CREATE TABLE projects (project_id INTEGER PRIMARY KEY, title TEXT NOT NULL);

ALTER TABLE projects ADD COLUMN status TEXT DEFAULT 'planned';
ALTER TABLE projects RENAME COLUMN title TO project_name;
ALTER TABLE projects RENAME TO initiatives;
INSERT INTO initiatives (project_name) VALUES ('Mobile app');
ALTER TABLE initiatives DROP COLUMN status;

SELECT * FROM initiatives;

Output

project_id | project_name
-----------+-------------
1          | Mobile app

Change	SQLite	MySQL	PostgreSQL
Add column	`ADD COLUMN c T`	`ADD COLUMN c T`	`ADD COLUMN c T`
Rename column	`RENAME COLUMN a TO b`	`RENAME COLUMN a TO b`	`RENAME COLUMN a TO b`
Change type	not supported — rebuild the table	`MODIFY COLUMN c NEWTYPE`	`ALTER COLUMN c TYPE NEWTYPE`
Drop column	`DROP COLUMN c` (3.35+)	`DROP COLUMN c`	`DROP COLUMN c`
Add constraint	not supported — rebuild	`ADD CONSTRAINT …`	`ADD CONSTRAINT …`

MySQL / PostgreSQL

ALTER TABLE employees MODIFY COLUMN name VARCHAR(150) NOT NULL;   -- MySQL
ALTER TABLE employees ALTER COLUMN name TYPE VARCHAR(150);        -- PostgreSQL
ALTER TABLE employees ADD CONSTRAINT chk_sal CHECK (salary < 1000000);

DROP, TRUNCATE, DELETE

	`DELETE FROM t`	`TRUNCATE TABLE t`	`DROP TABLE t`
Removes	Rows (all, or those matching `WHERE`)	All rows	Rows and the table definition
Category	DML	DDL	DDL
`WHERE` allowed	Yes	No	No
Speed on big tables	Slow — row by row, fully logged	Fast — deallocates pages	Fast
Fires triggers	Yes (`DELETE` triggers)	No	No
Resets auto-increment	No	Yes (most engines)	Table is gone
Rollback	Yes	PostgreSQL/SQL Server: yes in a transaction; MySQL/Oracle: no (auto-commit)	Same as `TRUNCATE`

SQL

CREATE TABLE temp_log (msg TEXT);
INSERT INTO temp_log VALUES ('a'), ('b');

DELETE FROM temp_log;                 -- SQLite has no TRUNCATE; an unqualified DELETE is optimised the same way
SELECT COUNT(*) AS rows_left FROM temp_log;

DROP TABLE temp_log;
DROP TABLE IF EXISTS temp_log;        -- no error the second time
SELECT COUNT(*) AS tables_named_temp_log FROM sqlite_schema WHERE name = 'temp_log';

Output

rows_left
---------
0

tables_named_temp_log
---------------------
0

MySQL / PostgreSQL / SQL Server / Oracle

TRUNCATE TABLE temp_log;

DELETE vs TRUNCATE vs DROP?

Can TRUNCATE be used on a table referenced by a foreign key?