当前位置： 首页 > news >正文

一文搞懂 SQL 五大分类：DDL, DML, DQL, DCL, TCL(语法 + 细节 + 注意事项 + 使用场景全覆盖)

news 2026/7/13 16:37:59

很多人学 SQL 时，都会被 DDL、DML、DQL 这些缩写和各种语法细节搞得晕头转向。其实它们只是 SQL 按照功能职责划分的五个 "部门"，每个部门有自己专属的 "工具"（语句）和 "工作流程"（语法）。

今天我用图书馆类比法，带你彻底搞懂这五大分类。我会列出每个分类的所有标准 SQL 语句，详细拆解完整语法结构，标注关键细节和注意事项，并给出真实业务使用场景，看完这篇，你不仅能写 SQL，还能写对 SQL、写好 SQL。

1. 前置知识：SQL 五大分类总览

1.1 图书馆类比：快速建立认知

我们把数据库比作一个大型图书馆：

  • 整个图书馆 = 数据库（Database）
  • 书架 = 数据表（Table）
  • 书架上的书 = 表中的行（Row）
  • 书的属性（书名、作者、ISBN） = 表中的列（Column）
  • 图书馆的规章制度 = 数据库约束（Constraint）

1.2 五大分类职责分工表

分类全称中文名称图书馆角色核心职责操作对象
DDLData Definition Language数据定义语言基建工程师搭建和改造图书馆基础设施数据库对象（库、表、索引、视图等）
DMLData Manipulation Language数据操纵语言图书管理员管理书架上的图书表中的数据行
DQLData Query Language数据查询语言读者查询和获取图书信息表中的数据行（只读）
DCLData Control Language数据控制语言安保主管管理人员进出和权限数据库用户和权限
TCLTransaction Control Language事务控制语言交易监督员监督交易过程，保证公平数据库事务

2. DDL：数据定义语言（基建工程师）

DDL 负责定义和管理数据库的结构，所有操作都会永久改变数据库的元数据，执行后隐式提交事务，不可回滚。

2.1 DDL 完整语句列表

标准 SQL 中，DDL 包含以下核心语句：

  • CREATE：创建数据库对象
  • ALTER：修改数据库对象结构
  • DROP：删除数据库对象
  • TRUNCATE：清空表数据（保留结构）
  • RENAME：重命名数据库对象
  • COMMENT：添加注释
  • GRANT/REVOKE：部分数据库将其归为 DCL，见第 5 章

2.2 CREATE 语句详解

2.2.1 完整语法结构
-- 创建数据库 CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET 字符集名] [COLLATE 排序规则名]; -- 创建表 CREATE TABLE [IF NOT EXISTS] 表名 ( 列名1 数据类型 [约束条件] [COMMENT '列注释'], 列名2 数据类型 [约束条件] [COMMENT '列注释'], ... [表级约束条件] ) [ENGINE=存储引擎] [DEFAULT CHARSET=字符集] [COMMENT='表注释'];
2.2.2 关键细节说明
  1. IF NOT EXISTS：如果对象已存在，不报错，仅跳过创建。生产环境必须加，避免脚本重复执行时出错。
  2. 字符集：MySQL 推荐使用utf8mb4（支持 emoji 和所有中文），不要用utf8（MySQL 的 utf8 是阉割版，只支持 3 字节）。
  3. 存储引擎：MySQL 默认使用InnoDB（支持事务和外键），除非有特殊需求，否则不要使用MyISAM。
  4. 约束条件：
    • NOT NULL：非空约束，该列不能为 NULL
    • UNIQUE：唯一约束，该列值不能重复
    • PRIMARY KEY：主键约束，非空且唯一
    • FOREIGN KEY：外键约束，关联其他表的主键
    • DEFAULT：默认值约束，插入数据时未指定则使用默认值
    • CHECK：检查约束，MySQL 8.0.16 + 才支持
2.2.3 注意事项
  • 数据库名和表名建议使用小写字母和下划线，不要使用中文或特殊字符
  • 每个表必须有一个主键，推荐使用自增整数INT AUTO_INCREMENT或 UUID
  • 不要创建过多的列，单表列数建议不超过 50 个
2.2.4 使用场景
  • 新项目初始化时，创建数据库和所有业务表
  • 新增业务模块时，创建对应的新表
  • 创建索引、视图、存储过程等其他数据库对象
2.2.5 完整示例
-- 创建一个电商数据库 CREATE DATABASE IF NOT EXISTS e_commerce CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 使用该数据库 USE e_commerce; -- 创建用户表 CREATE TABLE IF NOT EXISTS users ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID', username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名', password VARCHAR(255) NOT NULL COMMENT '密码（加密存储）', email VARCHAR(100) UNIQUE COMMENT '邮箱', phone VARCHAR(20) UNIQUE COMMENT '手机号', status TINYINT DEFAULT 1 COMMENT '用户状态：1-正常，0-禁用', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

2.3 ALTER 语句详解

2.3.1 完整语法结构
-- 修改表名 ALTER TABLE 旧表名 RENAME TO 新表名; -- 添加列 ALTER TABLE 表名 ADD [COLUMN] 列名 数据类型 [约束条件] [FIRST|AFTER 已有列名]; -- 修改列数据类型 ALTER TABLE 表名 MODIFY [COLUMN] 列名 新数据类型 [约束条件]; -- 修改列名和数据类型 ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 新数据类型 [约束条件]; -- 删除列 ALTER TABLE 表名 DROP [COLUMN] 列名; -- 添加主键 ALTER TABLE 表名 ADD PRIMARY KEY (列名); -- 添加外键 ALTER TABLE 表名 ADD FOREIGN KEY (外键列名) REFERENCES 主表名(主键列名); -- 添加索引 ALTER TABLE 表名 ADD INDEX 索引名(列名);
2.3.2 关键细节说明
  1. FIRST|AFTER：指定新列的位置，FIRST表示放在第一列，AFTER 列名表示放在指定列之后。
  2. MODIFYvsCHANGE：
    • MODIFY：只能修改列的数据类型和约束，不能修改列名
    • CHANGE：可以同时修改列名和数据类型
  3. 外键约束：添加外键时，外键列的数据类型必须和主表主键列完全一致。
2.3.3 注意事项
  • 生产环境执行 ALTER 大表会锁表！当表数据量超过 100 万行时，ALTER 操作可能会导致表被锁定几分钟甚至几小时，期间所有读写操作都会被阻塞。
  • 解决方案：使用在线 DDL 工具（如 pt-online-schema-change），或者在业务低峰期执行。
  • 删除列前一定要确认该列没有被任何代码使用，否则会导致程序报错。
2.3.4 使用场景
  • 业务需求变更，需要给表添加新的字段
  • 字段设计不合理，需要修改数据类型或长度
  • 需要添加索引来优化查询性能
  • 需要添加外键来保证数据的引用完整性
2.3.5 完整示例
-- 给用户表添加一个性别列，放在email列之后 ALTER TABLE users ADD COLUMN gender TINYINT COMMENT '性别：1-男，2-女，0-未知' AFTER email; -- 修改phone列的长度为11位 ALTER TABLE users MODIFY COLUMN phone VARCHAR(11) UNIQUE COMMENT '手机号'; -- 给username列添加索引 ALTER TABLE users ADD INDEX idx_username(username);

2.4 DROP 语句详解

2.4.1 完整语法结构
-- 删除数据库 DROP DATABASE [IF EXISTS] 数据库名; -- 删除表 DROP TABLE [IF EXISTS] 表名1, 表名2, ...; -- 删除索引 DROP INDEX 索引名 ON 表名; -- 删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
2.4.2 关键细节说明
  1. IF EXISTS：如果对象不存在，不报错，仅跳过删除。生产环境必须加。
  2. 级联删除：如果表之间有外键关联，直接删除主表会失败。需要先删除从表，或者先删除外键约束。
2.4.3 注意事项
  • DROP 操作是毁灭性的！一旦执行，数据库或表及其所有数据都会被永久删除，无法恢复（除非有备份）。
  • 生产环境执行 DROP 前，一定要先备份数据！
  • 建议在执行 DROP 前，先执行SELECT * FROM 表名 LIMIT 10确认你要删除的是正确的表。
2.4.4 使用场景
  • 项目下线时，删除对应的数据库
  • 废弃的旧表，不再使用时删除
  • 错误创建的索引或外键，需要删除
2.4.5 完整示例
-- 删除测试用的临时表 DROP TABLE IF EXISTS temp_test_table; -- 删除无用的索引 DROP INDEX idx_username ON users;

2.5 TRUNCATE 语句详解

2.5.1 完整语法结构
TRUNCATE TABLE 表名;
2.5.2 关键细节说明
  • TRUNCATE 是DDL 语句，不是 DML 语句。
  • 它直接删除表的数据页，而不是一行一行删除数据。
  • 执行后会重置自增主键的值为初始值。
2.5.3 注意事项
  • TRUNCATE 执行后不可回滚。
  • TRUNCATE 不会触发 DELETE 触发器。
  • 如果表被其他表的外键引用，TRUNCATE 会失败。
2.5.4 使用场景
  • 测试环境中，需要快速清空表数据重新测试
  • 日志表或临时表，需要定期清空所有历史数据
  • 数据迁移完成后，清空源表数据
2.5.5 完整示例
-- 清空用户登录日志表 TRUNCATE TABLE user_login_log;

2.6 RENAME 语句详解

2.6.1 完整语法结构
-- 重命名表 RENAME TABLE 旧表名 TO 新表名; -- 同时重命名多个表 RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2;
2.6.2 关键细节说明
  • RENAME 操作是原子性的，不会出现中间状态。
  • 重命名表时，所有依赖该表的视图、存储过程、触发器都会失效，需要手动更新。
2.6.3 注意事项
  • 重命名表前，一定要确认所有使用该表的代码都已经修改完毕，否则会导致程序报错。
  • 建议先创建新表，将数据迁移过去，验证无误后再删除旧表，而不是直接重命名。
2.6.4 使用场景
  • 表名设计不合理，需要修改为更有意义的名称
  • 数据迁移完成后，将旧表重命名为备份表
2.6.5 完整示例
-- 将旧的用户表重命名为备份表 RENAME TABLE users TO users_backup_20240520;

2.7 COMMENT 语句详解

2.7.1 完整语法结构
-- 修改表注释 ALTER TABLE 表名 COMMENT '新的表注释'; -- 修改列注释 ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型 COMMENT '新的列注释';
2.7.2 关键细节说明
  • 注释是数据库元数据的一部分，不会影响 SQL 执行性能。
  • 好的注释能大大提高代码的可维护性。
2.7.3 注意事项
  • 注释要简洁明了，说明该表或列的用途。
  • 对于状态列，要明确说明每个值代表的含义。
2.7.4 使用场景
  • 创建表时添加注释
  • 业务逻辑变更时，更新注释
2.7.5 完整示例
-- 修改用户表的注释 ALTER TABLE users COMMENT '电商平台用户信息表'; -- 修改status列的注释 ALTER TABLE users MODIFY COLUMN status TINYINT DEFAULT 1 COMMENT '用户状态：1-正常，0-禁用，2-待审核';

3. DML：数据操纵语言（图书管理员）

DML 负责对表中已存在的数据进行增、删、改操作，是业务系统中最常用的 SQL 类别之一。

3.1 DML 完整语句列表

标准 SQL 中，DML 包含以下核心语句：

  • INSERT：插入数据
  • UPDATE：更新数据
  • DELETE：删除数据
  • MERGE：合并数据（UPSERT，MySQL 8.0.20 + 支持）

3.2 INSERT 语句详解

3.2.1 完整语法结构
-- 插入单行数据 INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...); -- 插入多行数据 INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), ...; -- 插入查询结果 INSERT INTO 表名 (列1, 列2, ...) SELECT 列1, 列2, ... FROM 其他表 WHERE 条件;
3.2.2 关键细节说明
  1. 列名可以省略：如果省略列名，VALUES 子句中的值必须按照表定义的列顺序一一对应。不推荐省略列名，因为表结构变化时会导致插入失败或数据错位。
  2. 多行插入：一次插入多行数据比多次执行单行插入性能高得多。
  3. 插入查询结果：可以将一个查询的结果直接插入到另一个表中，常用于数据迁移和备份。
3.2.3 注意事项
  • 插入的数据类型必须和列的数据类型一致。
  • 非空列必须提供值，否则会报错。
  • 唯一约束列不能插入重复值。
  • 字符串和日期类型的值必须用单引号括起来。
3.2.4 使用场景
  • 用户注册时，插入一条新的用户记录
  • 批量导入数据时，使用多行插入
  • 数据备份时，将数据从一个表复制到另一个表
3.2.5 完整示例
-- 插入单行用户数据 INSERT INTO users (username, password, email, phone, gender) VALUES ('zhangsan', 'e10adc3949ba59abbe56e057f20f883e', 'zhangsan@example.com', '13800138000', 1); -- 批量插入3条用户数据 INSERT INTO users (username, password, email, phone, gender) VALUES ('lisi', 'e10adc3949ba59abbe56e057f20f883e', 'lisi@example.com', '13800138001', 1), ('wangwu', 'e10adc3949ba59abbe56e057f20f883e', 'wangwu@example.com', '13800138002', 2), ('zhaoliu', 'e10adc3949ba59abbe56e057f20f883e', 'zhaoliu@example.com', '13800138003', 0);

3.3 UPDATE 语句详解

3.3.1 完整语法结构
UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... [WHERE 条件] [ORDER BY 排序列] [LIMIT 行数];
3.3.2 关键细节说明
  1. WHERE 子句：指定要更新哪些行。如果省略 WHERE 子句，会更新表中所有行，这是非常严重的生产事故！
  2. ORDER BY 和 LIMIT：MySQL 支持在 UPDATE 语句中使用 ORDER BY 和 LIMIT，可以控制更新的顺序和行数。
3.3.3 注意事项
  • 永远不要省略 WHERE 子句！除非你真的想更新表中所有行。
  • 执行 UPDATE 前，建议先执行 SELECT 语句确认要更新的行是否正确。
  • 更新数据时要注意事务安全，特别是涉及多个表的更新操作。
3.3.4 使用场景
  • 用户修改个人信息时，更新用户表中的对应记录
  • 订单状态变更时，更新订单表的状态列
  • 批量更新数据，如给所有 VIP 用户增加积分
3.3.5 完整示例
-- 更新张三的邮箱和手机号 UPDATE users SET email = 'zhangsan_new@example.com', phone = '13900139000' WHERE id = 1; -- 给所有年龄大于18岁的用户设置为VIP UPDATE users SET is_vip = 1 WHERE age > 18;

3.4 DELETE 语句详解

3.4.1 完整语法结构
DELETE FROM 表名 [WHERE 条件] [ORDER BY 排序列] [LIMIT 行数];
3.4.2 关键细节说明
  1. WHERE 子句：指定要删除哪些行。如果省略 WHERE 子句，会删除表中所有行！
  2. ORDER BY 和 LIMIT：MySQL 支持在 DELETE 语句中使用 ORDER BY 和 LIMIT，可以控制删除的顺序和行数。
3.4.3 注意事项
  • 永远不要省略 WHERE 子句！除非你真的想删除表中所有行。
  • 执行 DELETE 前，建议先执行 SELECT 语句确认要删除的行是否正确。
  • 删除数据时要注意外键约束，如果有其他表引用了要删除的行，删除会失败。
  • 对于大量数据的删除，建议分批删除，避免锁表时间过长。
3.4.4 使用场景
  • 用户注销账号时，删除用户表中的对应记录
  • 删除过期的订单或日志数据
  • 删除错误插入的数据
3.4.5 完整示例
-- 删除id为4的用户 DELETE FROM users WHERE id = 4; -- 删除所有禁用状态的用户 DELETE FROM users WHERE status = 0;

3.5 MERGE 语句详解（UPSERT）

3.5.1 完整语法结构
MERGE INTO 目标表 t USING 源表 s ON (t.主键列 = s.主键列) WHEN MATCHED THEN UPDATE SET t.列1 = s.列1, t.列2 = s.列2, ... WHEN NOT MATCHED THEN INSERT (列1, 列2, ...) VALUES (s.列1, s.列2, ...);
3.5.2 关键细节说明
  • MERGE 语句也叫 UPSERT（UPDATE + INSERT），它会先检查主键是否存在。
  • 如果存在，则执行 UPDATE 操作；如果不存在，则执行 INSERT 操作。
  • MySQL 8.0.20 及以上版本才支持标准的 MERGE 语句。
  • 低版本 MySQL 可以使用INSERT ... ON DUPLICATE KEY UPDATE来实现相同的功能。
3.5.3 注意事项
  • 必须有主键或唯一约束才能使用 MERGE 语句。
  • 源表和目标表的列数据类型必须一致。
3.5.4 使用场景
  • 数据同步时，将源表的数据同步到目标表
  • 统计数据更新时，如果记录存在则更新，不存在则插入
3.5.5 完整示例
-- MySQL 8.0.20+ 标准MERGE语法 MERGE INTO user_statistics t USING (SELECT user_id, COUNT(*) AS login_count FROM user_login_log GROUP BY user_id) s ON (t.user_id = s.user_id) WHEN MATCHED THEN UPDATE SET t.login_count = s.login_count, t.update_time = NOW() WHEN NOT MATCHED THEN INSERT (user_id, login_count, create_time, update_time) VALUES (s.user_id, s.login_count, NOW(), NOW()); -- 低版本MySQL兼容写法 INSERT INTO user_statistics (user_id, login_count, create_time, update_time) SELECT user_id, COUNT(*) AS login_count, NOW(), NOW() FROM user_login_log GROUP BY user_id ON DUPLICATE KEY UPDATE login_count = VALUES(login_count), update_time = NOW();

4. DQL：数据查询语言（读者）

DQL 负责从表中查询并获取数据，是 SQL 中使用频率最高、语法最复杂的部分。它只读取数据，不修改数据，因此不会改变数据库状态。

4.1 DQL 完整语句列表

标准 SQL 中，DQL 只有一个核心语句：

  • SELECT：查询数据

但 SELECT 语句包含非常多的子句和功能，我们将逐一拆解。

4.2 SELECT 语句完整语法结构

SELECT [DISTINCT] 列表达式1 [AS 别名1], 列表达式2 [AS 别名2], ... FROM 表名1 [AS 别名1] [JOIN类型] JOIN 表名2 [AS 别名2] ON 关联条件 [WHERE 行级筛选条件] [GROUP BY 分组列1, 分组列2, ...] [HAVING 组级筛选条件] [UNION [ALL] SELECT语句] [ORDER BY 排序列1 [ASC/DESC], 排序列2 [ASC/DESC], ...] [LIMIT [偏移量,] 行数];

4.3 SELECT 基础子句详解

4.3.1 SELECT 子句：指定要查询的列
-- 查询所有列（不推荐在生产环境使用） SELECT * FROM users; -- 查询指定列 SELECT id, username, email FROM users; -- 使用别名 SELECT id AS user_id, username AS user_name FROM users; -- 去重查询 SELECT DISTINCT gender FROM users;

注意事项：

  • 生产环境尽量不要使用SELECT *，因为它会查询很多不需要的列，浪费性能，而且当表结构变化时，代码可能会出错。
  • 使用别名可以让查询结果更易读，也可以解决列名冲突的问题。
  • DISTINCT会对结果集进行去重，会消耗一定的性能，尽量只在必要时使用。
4.3.2 FROM 子句：指定数据来源
-- 从单个表查询 SELECT * FROM users; -- 从多个表查询（笛卡尔积，不推荐） SELECT * FROM users, orders;

注意事项：

  • 当从多个表查询时，如果没有指定关联条件，会产生笛卡尔积，结果集的行数是两个表行数的乘积，这通常不是我们想要的。
  • 多表查询应该使用 JOIN 子句，并指定明确的关联条件。
4.3.3 WHERE 子句：行级筛选
-- 等于条件 SELECT * FROM users WHERE username = 'zhangsan'; -- 比较条件 SELECT * FROM users WHERE age > 18; SELECT * FROM users WHERE age BETWEEN 18 AND 30; -- 逻辑条件 SELECT * FROM users WHERE age > 18 AND gender = 1; SELECT * FROM users WHERE status = 1 OR is_vip = 1; -- 模糊查询 SELECT * FROM users WHERE email LIKE '%@example.com'; SELECT * FROM users WHERE username LIKE 'zhang%'; -- IN条件 SELECT * FROM users WHERE id IN (1, 2, 3); -- IS NULL条件 SELECT * FROM users WHERE phone IS NULL;

注意事项：

  • NULL 值不能用=或!=来比较，必须用IS NULL或IS NOT NULL。
  • LIKE查询中，%表示任意长度的任意字符，_表示单个任意字符。
  • 避免在 WHERE 子句中对列进行函数操作，这会导致索引失效。
4.3.4 ORDER BY 子句：结果排序
-- 升序排序（默认） SELECT * FROM users ORDER BY age ASC; -- 降序排序 SELECT * FROM users ORDER BY create_time DESC; -- 多列排序 SELECT * FROM users ORDER BY is_vip DESC, create_time DESC;

注意事项：

  • ASC表示升序，是默认值，可以省略。
  • DESC表示降序。
  • 多列排序时，先按第一列排序，第一列值相同的再按第二列排序。
4.3.5 LIMIT 子句：分页查询
-- 返回前N条数据 SELECT * FROM users LIMIT 10; -- 分页查询：跳过offset条，返回count条 SELECT * FROM users LIMIT 20, 10; -- 第3页，每页10条

注意事项：

  • LIMIT offset, count中，offset是偏移量，从 0 开始。
  • 当 offset 很大时，LIMIT 查询性能会很差，需要优化。

4.4 SELECT 进阶子句详解

4.4.1 JOIN 子句：多表关联查询

JOIN 是 DQL 中最重要的功能之一，用于将多个表的数据关联起来查询。

-- 内连接：只返回两个表中匹配的行 SELECT u.username, o.order_no, o.total_amount FROM users u INNER JOIN orders o ON u.id = o.user_id; -- 左连接：返回左表的所有行，右表中匹配的行返回，不匹配的返回NULL SELECT u.username, o.order_no, o.total_amount FROM users u LEFT JOIN orders o ON u.id = o.user_id; -- 右连接：返回右表的所有行，左表中匹配的行返回，不匹配的返回NULL SELECT u.username, o.order_no, o.total_amount FROM users u RIGHT JOIN orders o ON u.id = o.user_id; -- 全连接：返回两个表的所有行，不匹配的返回NULL（MySQL不支持，可用UNION模拟）

注意事项：

  • 内连接是最常用的连接类型。
  • 左连接和右连接可以互换，只要交换两个表的位置即可。
  • 关联条件要使用主键和外键，并且确保关联列上有索引。
4.4.2 GROUP BY 子句：分组聚合查询

GROUP BY 用于将数据按指定列分组，然后对每个组进行聚合计算。

-- 统计每个性别的用户数量 SELECT gender, COUNT(*) AS user_count FROM users GROUP BY gender; -- 统计每个用户的订单数量和总金额 SELECT u.username, COUNT(o.id) AS order_count, SUM(o.total_amount) AS total_amount FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.username;

常用聚合函数：

  • COUNT(*)：统计行数
  • SUM(列名)：计算列的总和
  • AVG(列名)：计算列的平均值
  • MAX(列名)：计算列的最大值
  • MIN(列名)：计算列的最小值

注意事项：

  • SELECT 子句中只能出现 GROUP BY 子句中的列和聚合函数。
  • MySQL 5.7 及以上版本默认开启了ONLY_FULL_GROUP_BY模式，强制遵守上述规则。
4.4.3 HAVING 子句：组级筛选

HAVING 用于对 GROUP BY 分组后的结果进行筛选。

-- 统计订单数量大于5的用户 SELECT u.username, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.username HAVING order_count > 5;

WHERE vs HAVING：

  • WHERE：在分组前对行进行筛选，不能使用聚合函数
  • HAVING：在分组后对组进行筛选，可以使用聚合函数
4.4.4 UNION 子句：联合查询

UNION 用于将多个 SELECT 语句的结果合并成一个结果集。

-- 合并两个查询结果（自动去重） SELECT username, email FROM users WHERE gender = 1 UNION SELECT username, email FROM users WHERE is_vip = 1; -- 合并两个查询结果（不去重，性能更好） SELECT username, email FROM users WHERE gender = 1 UNION ALL SELECT username, email FROM users WHERE is_vip = 1;

注意事项：

  • 多个 SELECT 语句的列数必须相同。
  • 对应的列的数据类型必须兼容。
  • UNION会自动去重，UNION ALL不会去重，性能更好。

5. DCL：数据控制语言（安保主管）

DCL 负责管理数据库的访问权限和安全级别，所有操作都会永久改变数据库的权限体系，执行后隐式提交事务，不可回滚。

5.1 DCL 完整语句列表

标准 SQL 中，DCL 包含以下核心语句：

  • CREATE USER：创建数据库用户
  • ALTER USER：修改用户属性
  • DROP USER：删除数据库用户
  • GRANT：授予用户权限
  • REVOKE：撤销用户权限
  • SET PASSWORD：设置用户密码（MySQL 特有）

5.2 CREATE USER 语句详解

5.2.1 完整语法结构
CREATE USER [IF NOT EXISTS] '用户名'@'主机名' IDENTIFIED BY '密码';
5.2.2 关键细节说明
  1. 主机名：指定用户可以从哪个主机登录。
    • 'localhost'：只能从本地登录
    • '%'：可以从任何主机登录
    • '192.168.1.%'：可以从 192.168.1 网段的任何主机登录
  2. 密码：必须使用强密码，包含大小写字母、数字和特殊字符。
5.2.3 注意事项
  • 不要创建'root'@'%'用户，这会带来严重的安全风险。
  • 每个应用程序应该使用单独的数据库用户，不要共享用户。
5.2.4 使用场景
  • 新应用上线时，创建对应的数据库用户
  • 给开发人员创建只读权限的数据库用户
5.2.5 完整示例
-- 创建一个只能从本地登录的电商应用用户 CREATE USER IF NOT EXISTS 'e_commerce_app'@'localhost' IDENTIFIED BY 'StrongPassword123!'; -- 创建一个可以从任何主机登录的只读用户 CREATE USER IF NOT EXISTS 'readonly_user'@'%' IDENTIFIED BY 'ReadOnlyPassword456!';

5.3 ALTER USER 语句详解

5.3.1 完整语法结构
-- 修改用户密码 ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码'; -- 锁定用户 ALTER USER '用户名'@'主机名' ACCOUNT LOCK; -- 解锁用户 ALTER USER '用户名'@'主机名' ACCOUNT UNLOCK;
5.3.2 注意事项
  • 定期修改数据库用户的密码，提高安全性。
  • 当用户不再需要访问数据库时，应该及时锁定或删除用户。
5.3.3 使用场景
  • 用户密码泄露时，修改密码
  • 员工离职时，锁定其数据库用户
5.3.4 完整示例
-- 修改电商应用用户的密码 ALTER USER 'e_commerce_app'@'localhost' IDENTIFIED BY 'NewStrongPassword789!'; -- 锁定只读用户 ALTER USER 'readonly_user'@'%' ACCOUNT LOCK;

5.4 DROP USER 语句详解

5.4.1 完整语法结构
DROP USER [IF EXISTS] '用户名'@'主机名';
5.4.2 注意事项
  • 删除用户时，该用户拥有的所有权限都会被自动撤销。
  • 删除用户不会删除该用户创建的数据库对象。
5.4.3 使用场景
  • 应用下线时，删除对应的数据库用户
  • 不再需要的用户，及时删除
5.4.4 完整示例
-- 删除旧的测试用户 DROP USER IF EXISTS 'test_user'@'localhost';

5.5 GRANT 语句详解

5.5.1 完整语法结构
GRANT 权限1, 权限2, ... ON 数据库名.表名 TO '用户名'@'主机名' [WITH GRANT OPTION];
5.5.2 关键细节说明
  1. 权限列表：
    • ALL PRIVILEGES：所有权限
    • SELECT：查询权限
    • INSERT：插入权限
    • UPDATE：更新权限
    • DELETE：删除权限
    • CREATE：创建权限
    • ALTER：修改权限
    • DROP：删除权限
  2. 数据库名。表名：
    • *.*：所有数据库的所有表
    • e_commerce.*：e_commerce 数据库的所有表
    • e_commerce.users：e_commerce 数据库的 users 表
  3. WITH GRANT OPTION：允许该用户将自己拥有的权限授予其他用户。不推荐使用，会带来权限管理混乱的问题。
5.5.3 注意事项
  • 遵循最小权限原则：只给用户授予他们需要的最小权限。
  • 不要给普通用户授予ALL PRIVILEGES权限。
5.5.4 使用场景
  • 给应用用户授予对业务表的增删改查权限
  • 给开发人员授予对测试数据库的只读权限
5.5.5 完整示例
-- 给电商应用用户授予对e_commerce数据库所有表的增删改查权限 GRANT SELECT, INSERT, UPDATE, DELETE ON e_commerce.* TO 'e_commerce_app'@'localhost'; -- 给只读用户授予对e_commerce数据库所有表的查询权限 GRANT SELECT ON e_commerce.* TO 'readonly_user'@'%';

5.6 REVOKE 语句详解

5.6.1 完整语法结构
REVOKE 权限1, 权限2, ... ON 数据库名.表名 FROM '用户名'@'主机名';
5.6.2 注意事项
  • 撤销权限时，权限列表必须和授予时完全一致。
  • 撤销ALL PRIVILEGES会撤销该用户拥有的所有权限。
5.6.3 使用场景
  • 用户职责变更时，撤销不再需要的权限
  • 发现安全问题时，及时撤销用户的敏感权限
5.6.4 完整示例
-- 撤销只读用户对e_commerce数据库的删除权限（如果之前授予过） REVOKE DELETE ON e_commerce.* FROM 'readonly_user'@'%';

6. TCL：事务控制语言（交易监督员）

TCL 负责管理数据库事务，确保数据的一致性和完整性。事务是一组原子性的 SQL 操作，要么全部成功，要么全部失败。

6.1 TCL 完整语句列表

标准 SQL 中，TCL 包含以下核心语句：

  • START TRANSACTION/BEGIN：开启事务
  • COMMIT：提交事务
  • ROLLBACK：回滚事务
  • SAVEPOINT：设置保存点
  • ROLLBACK TO SAVEPOINT：回滚到保存点
  • SET TRANSACTION：设置事务属性

6.2 START TRANSACTION 语句详解

6.2.1 完整语法结构
START TRANSACTION; -- 或者 BEGIN;
6.2.2 关键细节说明
  • 开启事务后，所有的 DML 操作都不会立即生效，而是暂时存放在事务日志中。
  • MySQL 默认开启autocommit=1，即每条 DML 语句执行后自动提交事务。
  • 当执行START TRANSACTION后，autocommit会被临时关闭，直到执行COMMIT或ROLLBACK。
6.2.3 注意事项
  • 事务应该尽量短小，避免长时间占用数据库资源。
  • 不要在事务中执行耗时很长的操作，如查询大量数据或调用外部接口。
6.2.4 使用场景
  • 任何需要保证原子性的操作，如转账、下单、支付等
  • 多个 DML 操作需要同时成功或同时失败的场景

6.3 COMMIT 语句详解

6.3.1 完整语法结构
COMMIT;
6.3.2 关键细节说明
  • 执行COMMIT后，事务中的所有修改都会被永久保存到数据库中。
  • 提交后，事务结束，autocommit恢复到之前的状态。
6.3.3 注意事项
  • 只有当事务中的所有操作都执行成功时，才应该执行COMMIT。
  • 提交后，事务不可回滚。

6.4 ROLLBACK 语句详解

6.4.1 完整语法结构
ROLLBACK;
6.4.2 关键细节说明
  • 执行ROLLBACK后，事务中的所有修改都会被撤销，数据库恢复到事务开始前的状态。
  • 回滚后，事务结束，autocommit恢复到之前的状态。
6.4.3 注意事项
  • 当事务中的任何操作失败时，应该立即执行ROLLBACK。
  • 回滚会释放事务占用的所有锁和资源。

6.5 SAVEPOINT 语句详解

6.5.1 完整语法结构
-- 设置保存点 SAVEPOINT 保存点名; -- 回滚到保存点 ROLLBACK TO SAVEPOINT 保存点名; -- 删除保存点 RELEASE SAVEPOINT 保存点名;
6.5.2 关键细节说明
  • 保存点允许你将一个大事务分成多个小部分，可以只回滚到某个保存点，而不是回滚整个事务。
  • 回滚到保存点后，保存点之后的所有修改都会被撤销，但保存点之前的修改仍然保留。
6.5.3 注意事项
  • 不要创建过多的保存点，会增加事务的复杂度。
  • 事务结束后，所有的保存点都会被自动删除。
6.5.4 使用场景
  • 复杂的事务中，需要分步执行，并且可以部分回滚的场景
  • 批量操作中，出错时可以回滚到上一个成功的批次

6.6 完整事务示例

-- 经典转账事务示例 START TRANSACTION; -- 检查张三的账户余额是否足够 SELECT balance FROM accounts WHERE user_id = 1 FOR UPDATE; -- 张三的账户减去100元 UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- 李四的账户加上100元 UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; -- 记录转账日志 INSERT INTO transfer_log (from_user_id, to_user_id, amount, create_time) VALUES (1, 2, 100, NOW()); -- 如果所有操作都成功，提交事务 COMMIT; -- 如果任何操作失败，执行回滚 -- ROLLBACK;

6.7 事务的四大特性（ACID）

事务必须满足四大特性，简称 ACID：

  1. 原子性（Atomicity）：事务是一个不可分割的最小单位，要么全部成功，要么全部失败。
  2. 一致性（Consistency）：事务执行前后，数据的完整性没有被破坏。
  3. 隔离性（Isolation）：多个事务之间相互隔离，互不干扰。
  4. 持久性（Durability）：事务提交后，对数据的修改是永久的，不会因为系统故障而丢失。

7. 五大分类核心对比总结表

分类核心语句执行后是否可回滚权限要求生产环境风险等级
DDLCREATE、ALTER、DROP、TRUNCATE否（隐式提交）高极高（DROP/TRUNCATE 会导致数据丢失）
DMLINSERT、UPDATE、DELETE、MERGE是（开启事务后）中高（UPDATE/DELETE 不加 WHERE 会导致全表数据错误）
DQLSELECT不涉及低低（只会影响查询性能，不会修改数据）
DCLCREATE USER、GRANT、REVOKE否（隐式提交）极高中（权限管理不当会导致安全问题）
TCLCOMMIT、ROLLBACK、SAVEPOINT是中低（只会影响当前事务）

8. 写在最后

SQL 是后端开发人员必备的核心技能，而掌握这五大分类是学好 SQL 的基础。很多人写了多年 SQL，却依然分不清 TRUNCATE 和 DELETE 的区别，不知道 UPDATE 不加 WHERE 的后果，这都是因为没有从根本上理解 SQL 的分类和每个语句的本质。

希望这篇文章能帮你建立起完整的 SQL 知识体系，让你不仅能写 SQL，还能写出安全、高效、可维护的 SQL。

http://www.cnnetsun.cn/news/3347336.html

相关文章：

  • AI大模型学习指南：小白程序员必备，收藏这份高薪技能秘籍！
  • ZenTorch与vLLM协同优化：AMD 20B模型推理性能调优指南
  • MLX框架下的Laguna-M.1-4bit部署指南：从零到生产环境
  • 轨道交通企业怎么做许可证利用率优化：多项目并行下先解决高峰冲突，还是先清理闲置占用
  • Instant-NuRec模型架构深度解析：从Depth-Anything-v3到高斯溅射解码器
  • 元初混沌数学108篇｜华夏本源大道体系目录
  • Kimi-K2-Thinking-MXFP4-AttnFP8量化技术详解：MXFP4与FP8混合精度优化
  • Cursor多文件智能补全失效真相：3类隐式上下文断裂场景与4行配置修复方案
  • 终极指南：在AMD NPU上优化Qwen2.5-7B-Instruct_rai_1.7.1_npu_4K性能的10个技巧
  • 6-SOFA视觉模型Visual Model
  • 大数据毕设项目：基于 Python Django 的旅游用户画像构建与景点推荐系统的设计与实现 (源码+文档，讲解、调试运行，定制等)
  • c++秋招必刷：上百道c/c++面试八股文，含答案讲解Linux开发架构之路
  • 从源码到部署：AMD gpt-oss-20b-BF16-w4a16-asym-torchao-v0.17.0全生命周期教程
  • AMD Ryzen AI Llama-3.2-1B-Instruct模型缓存机制解析：Token_rms_norm缓存文件作用与优化
  • 如何在AMD MI350/MI355上部署gpt-oss-120b量化模型：超详细vLLM启动指南
  • Qwen2.5-Coder-7B-Instruct_rai_1.7.1_npu_4K模型量化原理：AWQ与BFP16技术详解
  • 基于深度学习的手势识别系统312(设计源文件+万字报告+讲解)（支持资料、图片参考_相关定制）_
  • SpringBoot + 微信小程序 环保公益服务系统设计与实现
  • 终极指南：Phi-3.5-mini-instruct_rai_1.7.1_npu_16K模型配置文件(genai_config.json)完全解读
  • 文心复制有星号 导出文档杂乱难整理？AI 导出鸭一站式搞定多格式转换难题
  • 学术/商务/新媒体文本润色全场景适配，ChatGPT改写避坑清单与效果验证SOP
  • Mistral-7B-Instruct-v0.1_rai_1.7.1_npu_4K提示词工程：提升NPU推理效率的10个技巧
  • W4A16不对称量化技术详解：为什么AMD选择这种量化方法
  • 密码与加密基础篇（3）：salt 和 pepper 到底是什么？为什么 salt 可以放数据库？
  • TLA2518与PIC18F26K40构建高精度数据采集系统
  • IndexTTS2配置详解：从config.yaml到模型文件的完整配置手册
  • 别只把微信 API 当聊天接口：盘点生产环境下的 4 个硬核工程玩法
  • 计算机毕业设计之jsp社区门诊管理系统的设计与实现
  • Pixhawk强制硬件配置：解锁前必须完成的底层校验流程
  • 【扩散模型】DDPM与DDIM：从概率扩散到确定性采样的演进之路