很多人学 SQL 时都会被 DDL、DML、DQL 这些缩写和各种语法细节搞得晕头转向。其实它们只是 SQL 按照功能职责划分的五个 部门每个部门有自己专属的 工具语句和 工作流程语法。今天我用图书馆类比法带你彻底搞懂这五大分类。我会列出每个分类的所有标准 SQL 语句详细拆解完整语法结构标注关键细节和注意事项并给出真实业务使用场景看完这篇你不仅能写 SQL还能写对 SQL、写好 SQL。1. 前置知识SQL 五大分类总览1.1 图书馆类比快速建立认知我们把数据库比作一个大型图书馆整个图书馆 数据库Database书架 数据表Table书架上的书 表中的行Row书的属性书名、作者、ISBN 表中的列Column图书馆的规章制度 数据库约束Constraint1.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 关键细节说明IF NOT EXISTS如果对象已存在不报错仅跳过创建。生产环境必须加避免脚本重复执行时出错。字符集MySQL 推荐使用utf8mb4支持 emoji 和所有中文不要用utf8MySQL 的 utf8 是阉割版只支持 3 字节。存储引擎MySQL 默认使用InnoDB支持事务和外键除非有特殊需求否则不要使用MyISAM。约束条件NOT NULL非空约束该列不能为 NULLUNIQUE唯一约束该列值不能重复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 更新时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 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 关键细节说明FIRST|AFTER指定新列的位置FIRST表示放在第一列AFTER 列名表示放在指定列之后。MODIFYvsCHANGEMODIFY只能修改列的数据类型和约束不能修改列名CHANGE可以同时修改列名和数据类型外键约束添加外键时外键列的数据类型必须和主表主键列完全一致。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 关键细节说明IF EXISTS如果对象不存在不报错仅跳过删除。生产环境必须加。级联删除如果表之间有外键关联直接删除主表会失败。需要先删除从表或者先删除外键约束。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合并数据UPSERTMySQL 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 关键细节说明列名可以省略如果省略列名VALUES 子句中的值必须按照表定义的列顺序一一对应。不推荐省略列名因为表结构变化时会导致插入失败或数据错位。多行插入一次插入多行数据比多次执行单行插入性能高得多。插入查询结果可以将一个查询的结果直接插入到另一个表中常用于数据迁移和备份。3.2.3 注意事项插入的数据类型必须和列的数据类型一致。非空列必须提供值否则会报错。唯一约束列不能插入重复值。字符串和日期类型的值必须用单引号括起来。3.2.4 使用场景用户注册时插入一条新的用户记录批量导入数据时使用多行插入数据备份时将数据从一个表复制到另一个表3.2.5 完整示例-- 插入单行用户数据 INSERT INTO users (username, password, email, phone, gender) VALUES (zhangsan, e10adc3949ba59abbe56e057f20f883e, zhangsanexample.com, 13800138000, 1); -- 批量插入3条用户数据 INSERT INTO users (username, password, email, phone, gender) VALUES (lisi, e10adc3949ba59abbe56e057f20f883e, lisiexample.com, 13800138001, 1), (wangwu, e10adc3949ba59abbe56e057f20f883e, wangwuexample.com, 13800138002, 2), (zhaoliu, e10adc3949ba59abbe56e057f20f883e, zhaoliuexample.com, 13800138003, 0);3.3 UPDATE 语句详解3.3.1 完整语法结构UPDATE 表名 SET 列1 值1, 列2 值2, ... [WHERE 条件] [ORDER BY 排序列] [LIMIT 行数];3.3.2 关键细节说明WHERE 子句指定要更新哪些行。如果省略 WHERE 子句会更新表中所有行这是非常严重的生产事故ORDER BY 和 LIMITMySQL 支持在 UPDATE 语句中使用 ORDER BY 和 LIMIT可以控制更新的顺序和行数。3.3.3 注意事项永远不要省略 WHERE 子句除非你真的想更新表中所有行。执行 UPDATE 前建议先执行 SELECT 语句确认要更新的行是否正确。更新数据时要注意事务安全特别是涉及多个表的更新操作。3.3.4 使用场景用户修改个人信息时更新用户表中的对应记录订单状态变更时更新订单表的状态列批量更新数据如给所有 VIP 用户增加积分3.3.5 完整示例-- 更新张三的邮箱和手机号 UPDATE users SET email zhangsan_newexample.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 关键细节说明WHERE 子句指定要删除哪些行。如果省略 WHERE 子句会删除表中所有行ORDER BY 和 LIMITMySQL 支持在 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 语句详解UPSERT3.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 语句也叫 UPSERTUPDATE 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; -- 全连接返回两个表的所有行不匹配的返回NULLMySQL不支持可用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 HAVINGWHERE在分组前对行进行筛选不能使用聚合函数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 关键细节说明主机名指定用户可以从哪个主机登录。localhost只能从本地登录%可以从任何主机登录192.168.1.%可以从 192.168.1 网段的任何主机登录密码必须使用强密码包含大小写字母、数字和特殊字符。5.2.3 注意事项不要创建root%用户这会带来严重的安全风险。每个应用程序应该使用单独的数据库用户不要共享用户。5.2.4 使用场景新应用上线时创建对应的数据库用户给开发人员创建只读权限的数据库用户5.2.5 完整示例-- 创建一个只能从本地登录的电商应用用户 CREATE USER IF NOT EXISTS e_commerce_applocalhost 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_applocalhost 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_userlocalhost;5.5 GRANT 语句详解5.5.1 完整语法结构GRANT 权限1, 权限2, ... ON 数据库名.表名 TO 用户名主机名 [WITH GRANT OPTION];5.5.2 关键细节说明权限列表ALL PRIVILEGES所有权限SELECT查询权限INSERT插入权限UPDATE更新权限DELETE删除权限CREATE创建权限ALTER修改权限DROP删除权限数据库名。表名*.*所有数据库的所有表e_commerce.*e_commerce 数据库的所有表e_commerce.userse_commerce 数据库的 users 表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_applocalhost; -- 给只读用户授予对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 默认开启autocommit1即每条 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原子性Atomicity事务是一个不可分割的最小单位要么全部成功要么全部失败。一致性Consistency事务执行前后数据的完整性没有被破坏。隔离性Isolation多个事务之间相互隔离互不干扰。持久性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。