DDL, DML, DQL, DCL and TCL

SQL statements are often grouped into families. The names look scary, but the idea is simple: what does the statement do?

Some books count SELECT as part of DML. Both views are common, so you will meet both.

An example of each

1
2
3
4
5
6
7
8
9
10

-- DDL: define a table
CREATE TABLE notes (id INT AUTO_INCREMENT PRIMARY KEY, body VARCHAR(100));

-- DML: change data
INSERT INTO notes (body) VALUES ('first'), ('second');
UPDATE notes SET body = 'FIRST' WHERE id = 1;
DELETE FROM notes WHERE id = 2;

-- DQL: read data
SELECT * FROM notes;

1
2
3
4
5
6

+----+-------+
| id | body  |
+----+-------+
|  1 | FIRST |
+----+-------+
1 row in set

1
2
3
4
5

-- TCL: control a transaction
START TRANSACTION;
INSERT INTO notes (body) VALUES ('temporary');
ROLLBACK;
SELECT COUNT(*) AS rows_now FROM notes;

1
2
3
4
5
6

+----------+
| rows_now |
+----------+
|        1 |
+----------+
1 row in set

The ROLLBACK cancelled the INSERT, so the table still has one row.

DCL (GRANT and friends) is shown in the Users, Roles and Security chapter.

DDL is special in MySQL

In MySQL, a DDL statement commits automatically. It ends any open transaction and cannot be rolled back.

1
2
3
4
5

START TRANSACTION;
INSERT INTO notes (body) VALUES ('before the DDL');
CREATE TABLE side_effect (id INT);
ROLLBACK;
SELECT body FROM notes;

1
2
3
4
5
6
7

+----------------+
| body           |
+----------------+
| FIRST          |
| before the DDL |
+----------------+
2 rows in set

The CREATE TABLE forced a commit, so the ROLLBACK found nothing to undo and the row before the DDL stayed. Many other databases (PostgreSQL, for example) allow DDL inside a transaction. MySQL does not.

Why the families matter

• Permissions follow them. A reporting user may get only SELECT. An app user gets SELECT, INSERT, UPDATE, DELETE. Only an admin (or a migration tool) gets DDL rights.
• Risk follows them. DQL cannot damage data. DML can. DDL can destroy it.