技术 · 思考 · 实践 · 成长

首页/MySQL8专栏/正文

数据库中的 DDL、DML、DCL 有什么区别?一文讲清 SQL 语句分类

MySQL8专栏310 次阅读

SQL 语句通常按照作用分为 DDL、DML、DCL、DQL 和 TCL。它们分别用于定义数据库结构、修改数据、管理权限、查询数据和控制事务。理解这些分类,有助于正确设计权限、事务和数据库操作流程。

一、SQL 语句分类概览

分类英文全称主要作用常见命令
DDLData Definition Language定义或修改数据库对象结构CREATE、ALTER、DROP、TRUNCATE
DMLData Manipulation Language新增、修改或删除表中数据INSERT、UPDATE、DELETE
DQLData Query Language查询数据SELECT
DCLData Control Language管理用户权限GRANT、REVOKE
TCLTransaction Control Language控制事务COMMIT、ROLLBACK、SAVEPOINT

不同资料可能把 SELECT 归入 DML,也可能单独称为 DQL;把事务控制语句单独称为 TCL,则更便于理解和学习。

二、DDL:数据定义语言

DDL 用于创建、修改和删除数据库对象,例如数据库、表、索引和视图。它改变的是数据结构,而不是某一条业务数据。

1. CREATE:创建对象

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. ALTER:修改结构

ALTER TABLE users
ADD COLUMN email VARCHAR(255) NULL AFTER username;

3. DROP:删除对象

DROP TABLE users;

DROP TABLE 会删除表结构和其中的数据,执行前必须确认对象并做好备份。

4. TRUNCATE:清空表

TRUNCATE TABLE users;

TRUNCATE TABLE 会快速清空整张表,并保留表结构。它与没有 WHERE 的 DELETE 并不等价,在事务、触发器、自增值和外键限制方面可能存在差异。

三、DML:数据操作语言

DML 用于修改表中的数据,常见命令包括 INSERT、UPDATE 和 DELETE。

1. INSERT:新增数据

INSERT INTO users (username, email)
VALUES ('zhangsan', 'zhangsan@example.com');

2. UPDATE:更新数据

UPDATE users
SET email = 'new@example.com'
WHERE id = 1;

3. DELETE:删除数据

DELETE FROM users
WHERE id = 1;

UPDATE 和 DELETE 如果省略 WHERE,会影响表中的全部记录。执行前应先使用相同条件进行 SELECT,确认影响范围。

四、DQL:数据查询语言

DQL 主要指 SELECT,用于读取和统计数据,不直接修改表中的业务数据。

SELECT id, username, email
FROM users
WHERE created_at >= '2026-01-01 00:00:00'
ORDER BY id DESC
LIMIT 20;

SELECT 可以配合连接、分组、聚合、子查询和窗口函数完成复杂查询。需要注意的是,带有 FOR UPDATE 的锁定查询虽然不修改字段值,但会对匹配记录加锁:

SELECT id, username
FROM users
WHERE id = 1
FOR UPDATE;

五、DCL:数据控制语言

DCL 用于授予或撤销数据库权限。实际生产环境应遵循最小权限原则,只授予账号完成工作所必需的权限。

1. GRANT:授予权限

GRANT SELECT, INSERT, UPDATE
ON app_db.*
TO 'app_user'@'localhost';

2. REVOKE:撤销权限

REVOKE INSERT, UPDATE
ON app_db.*
FROM 'app_user'@'localhost';

3. 查看权限

SHOW GRANTS FOR 'app_user'@'localhost';

应用账号通常不应拥有 DROP、ALTER 或全局管理权限。建表和发布变更可交给专门的迁移账号执行。

六、TCL:事务控制语言

TCL 用于控制一组数据库操作是整体提交还是整体撤销,常见于 InnoDB 表。

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

如果执行过程中发现异常,应使用 ROLLBACK 撤销尚未提交的事务:

ROLLBACK;

还可以使用保存点回滚事务中的部分操作:

START TRANSACTION;

UPDATE users SET email = 'a@example.com' WHERE id = 1;
SAVEPOINT after_first_update;

UPDATE users SET email = 'b@example.com' WHERE id = 2;
ROLLBACK TO SAVEPOINT after_first_update;

COMMIT;

七、DDL 与 DML 的关键区别

对比项DDLDML
操作对象数据库、表、索引等结构表中的具体记录
典型命令CREATE、ALTER、DROPINSERT、UPDATE、DELETE
事务特性MySQL 中许多 DDL 会隐式提交InnoDB 中通常可由事务提交或回滚
主要风险结构改变、对象或整表数据丢失业务数据被错误新增、修改或删除

八、MySQL 8 中的隐式提交

MySQL 中许多 DDL 语句会在执行前或执行后触发隐式提交。例如,在事务中执行 ALTER TABLE 后,不能简单依靠 ROLLBACK 恢复到执行前的结构。

START TRANSACTION;

UPDATE users SET email = 'test@example.com' WHERE id = 1;

-- ALTER TABLE 通常会触发隐式提交
ALTER TABLE users ADD COLUMN status TINYINT NOT NULL DEFAULT 1;

ROLLBACK;

不要假设最后的 ROLLBACK 能撤销上面的全部操作。数据库结构变更应使用经过验证的迁移脚本,并提前做好备份和回滚方案。

MySQL 8 的原子 DDL 能保证受支持的单条 DDL 操作整体成功或整体失败,但这不等于 DDL 可以像普通 DML 一样被用户事务随意回滚。

九、如何快速判断语句类型

  • 改变表、索引或数据库结构:通常属于 DDL。
  • 新增、修改或删除具体记录:属于 DML。
  • 只读取数据:通常称为 DQL。
  • 授予或撤销账号权限:属于 DCL。
  • 提交、回滚或设置保存点:属于 TCL。

十、常见误区

  • 认为所有 SQL 语句都可以使用 ROLLBACK 撤销。
  • 混淆 DROP、TRUNCATE 和 DELETE 的作用。
  • 给应用账号授予过大的 DDL 或管理权限。
  • 执行无条件 UPDATE、DELETE 前没有预览影响范围。
  • 将 MySQL 8 的原子 DDL 理解成“DDL 支持事务回滚”。

十一、总结

DDL 管结构,DML 改数据,DQL 查数据,DCL 管权限,TCL 管事务。实际开发中,尤其要注意 MySQL 的 DDL 隐式提交行为,并通过最小权限、事务、备份和迁移脚本降低误操作风险。

十二、官方文档

所有的努力,不是为了让别人觉得你了不起,而是为了能让自己打心眼里看得起自己。

评论交流

0 条评论 · 支持多级回复
验证码
暂无评论,来说说你的想法吧

相关文章

继续阅读