如果你在命令行里敲过select * from users也曾经因为一条update忘了加where而差点删掉整张表那你应该已经隐隐感觉到MySQL 里的 SQL 并不是一堆散装命令它背后有一套清晰的分类逻辑。把这套分类搞定你就掌握了 MySQL 的地图——以后不管写表、查数、处理事务还是管权限都不会迷路。这篇文章要聊的就是“MySQL 数据库SQL 分类”这件事。我会把 DDL、DML、DQL、DCL、TCL 这几大块掰开揉碎结合真实项目里的写法、常见的坑、面试爱问的点帮你把 SQL 分类从“背概念”变成“用得上”。无所谓你是刚装好 MySQL 5.7 的新手还是已经写了大半年增删改查的初级开发这篇文章都能帮你把地基重新打一遍。1. SQL分类先建立整体认知框架1.1 为什么学习 SQL 必须先搞懂分类我见过不少同学MySQL 装好了Navicat 也连上了结果第一件事就是到处搜“mysql 排序怎么排”“mysql 去重怎么写”。代码能跑但脑子里没有体系。一旦遇到报错、性能问题、权限问题就完全不知道去哪一环排查。SQL 分类的意义不在于考试而在于定位问题。你可以把 MySQL 当作一家公司SQL 命令就是这家公司的各种业务流程DDL 像是“土木工程部”——负责盖楼、拆楼、改楼也就是库和表的结构DML 像是“业务运营部”——负责往楼里搬东西、调换货架商品也就是数据的增删改DQL 是“数据报告部”——负责把仓库里的数据统计成报表相当于查询DCL 是“行政保安部”——负责给员工发门禁卡、收回权限TCL 是“财务结算部”——负责让多步操作要么全部成功、要么全部回滚。一旦这样理解你遇到“没权限建表”就知道问题在 DCL“表结构不对导致插入失败”就知道问题在 DDL“查询太慢”就知道问题在 DQL。排查路径清清楚楚。另外MySQL 安装好之后很多人看着黑窗口发呆不知道先干什么。其实你只要按“DDL 建库 → DDL 建表 → DML 写数据 → DQL 查数据”这条线走一遍MySQL 的核心使用流程就通了。本文后面会专门用一个实战案例串这条线到时候你感受会更直观。1.2 四类 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事务提交、回滚、保存点注意一点MySQL 官方文档和一些老教材会把 TCL 合并进 DML 里讲因为事务本质上管的是增删改操作的完整性。但在面试和实际沟通中单独把 TCL 拎出来说会更清楚因为你一旦提到“事务”面试官默认你聊的是 COMMIT、ROLLBACK、隔离级别这类东西。2. DDL实操库表设计的第一次接触2.1 建库、删库与字符集选择DDL 是你连上 MySQL 之后最先碰到的命令。第一件事通常是建库语法很简单CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;这里有两个关键参数很多人一开始不注意后面全改悔了。字符集选utf8mb4而不是utf8。MySQL 里的utf8其实是“阉割版”最多支持 3 字节像 emoji 这种 4 字节字符直接插入报错。utf8mb4才是真正完整的 UTF-8 编码现在 MySQL 8.0 的默认字符集已经就是utf8mb4了但如果你还在用 MySQL 5.7建库时一定要显式写。我在一个老项目里遇到过用户昵称带了一个 emoji结果数据写入直接报Incorrect string value查了半天才发现是字符集的问题。排序规则utf8mb4_general_ci里的_ci表示 case insensitive也就是大小写不敏感。这个影响的是字符串比较和排序行为。比如用where name tom能匹配到Tom。如果业务上需要区分大小写就得选utf8mb4_bin。大多数业务用general_ci就够了。删库命令是DROP DATABASE shop;但我强烈建议生产环境永远不要手滑执行这条。我之前就见过同事在错误的连接窗口执行了删库公司那天晚上灯火通明。真的想删先SHOW DATABASES;确认一遍再确认当前连接的是不是目标库。2.2 建表字段类型与约束建表是 DDL 里最有讲究的部分。随便写一个用户表CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 主键, username VARCHAR(50) NOT NULL UNIQUE COMMENT 用户名, email VARCHAR(100) NOT NULL COMMENT 邮箱, age TINYINT UNSIGNED DEFAULT 0 COMMENT 年龄, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常 0禁用, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT用户表;字段类型的选择直接决定这张表的性能和容量整数类型TINYINT占 1 字节范围 -128 到 127适合状态值、年龄INT占 4 字节BIGINT占 8 字节一般主键直接BIGINT UNSIGNED避免将来数据量大时INT溢出。这里有个生活化类比字段类型就像租房子TINYINT是小单间BIGINT是大平层。宁可一开始稍微宽一点也别等数据上百万了再回头改表结构那种操作会锁表非常疼。变长字符串VARCHAR(50)表示最多 50 个字符。注意是“字符”不是“字节”所以中文、英文、emoji 在VARCHAR里计算长度的方式对使用者来说是一致的。CHAR是定长适合身份证号、手机号这类长度固定的数据查询时因为长度固定理论上略快但实际业务里大部分字段还是VARCHAR更省空间。时间类型DATETIME和TIMESTAMP都存年月日时分秒。TIMESTAMP有时区概念、范围到 2038 年DATETIME没有时区概念、范围更大。一般情况下我建议用DATETIME省得服务器时区一换存进去的时间全乱了。约束这块NOT NULL加DEFAULT是黄金搭档。很多新手建表不写DEFAULT结果插入数据时少给一个字段就报错。其实大多数业务字段都能设计出合理的默认值比如状态默认1数量默认0时间默认CURRENT_TIMESTAMP。这样你的 INSERT 语句就能短很多也更加稳定。2.3 ALTER 操作与常见坑表建好后很少一成不变加字段、改字段类型都靠ALTER。-- 加字段 ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT COMMENT 手机号; -- 修改字段类型 ALTER TABLE users MODIFY COLUMN age SMALLINT UNSIGNED NOT NULL DEFAULT 0; -- 改字段名 ALTER TABLE users CHANGE COLUMN email email_address VARCHAR(100) NOT NULL; -- 删字段 ALTER TABLE users DROP COLUMN phone;ALTER TABLE在 MySQL 5.7 里执行时会触发表锁尤其对大表来说一条加字段的语句可能让线上服务卡死几十秒。所以在生产环境做大表结构变更我一般用pt-online-schema-change这类工具或者至少选在业务低峰期执行。这是 DDL 里最重要的一条实践经验常规文档不会告诉你。还有一个容易踩的坑CHANGE COLUMN和MODIFY COLUMN的区别。CHANGE可以同时改字段名和类型MODIFY只能改类型和约束。新手经常把字段名抄错然后 MySQL 报Unknown column其实只要确认你是在改名还是改类型就好。2.4 TRUNCATE 与 DELETE 的本质区别虽然DELETE属于 DML、TRUNCATE属于 DDL但很多新手分不清这里提前点一下。TRUNCATE TABLE users;是清空表数据而且它是 DDL 操作不走事务、不能回滚、速度极快DELETE FROM users;是 DML 操作逐条删除、可加WHERE、可回滚、速度慢。如果一个表几百条数据两者没啥差别如果是几百万条TRUNCATE秒回、DELETE可能跑几分钟。但TRUNCATE会重置自增 ID如果你不希望 ID 从 1 重新开始就得用DELETE配合事务。这个细节在业务上很重要比如清空一张流水表后你希望新流水从上次的 ID 继续往后排就不能TRUNCATE。3. DML实操数据增删改的现场笔记3.1 INSERT 的写法与细节DML 是跟数据直接打交道的部分写得好不好直接影响数据质量和系统性能。先看插入-- 单条插入 INSERT INTO users (username, email, age, status) VALUES (laozhang, laozhangexample.com, 28, 1); -- 批量插入 INSERT INTO users (username, email, age, status) VALUES (wangwu, wangwuexample.com, 25, 1), (zhaoliu, zhaoliuexample.com, 30, 1), (sunqi, sunqiexample.com, 22, 1);批量插入是我一直强调的性能习惯。如果你用过循环一条条插比如在 Python 里for跑 10 万条INSERT你会发现慢得离谱。改成一条语句里拼多个VALUES速度能提升一个数量级。原因很简单每条 SQL 都要经过网络传输、SQL 解析、执行计划的生成批量插入把这些开销摊薄了。还有个常用场景是“存在就更新不存在就插入”这在同步外部数据时特别常见INSERT INTO users (id, username, email, age) VALUES (1, laozhang, laozhangexample.com, 28) ON DUPLICATE KEY UPDATE email VALUES(email), age VALUES(age);如果主键或唯一键冲突就走UPDATE分支否则正常插入。这个写法在同步数据、导入 Excel 数据时能省掉大量先查再插的代码。3.2 UPDATE 与 DELETE先选条件再动手更新和删除是事故高发区。核心原则只有一条写UPDATE和DELETE之前必须确认WHERE条件精确。-- 危险写法会把全表 status 改成 0 UPDATE users SET status 0; -- 安全写法 UPDATE users SET status 0 WHERE id 10086;说实话MySQL 默认的sql_safe_updates是关闭的很多新手不小心就执行了不带WHERE的更新或删除。我建议你在自己的开发环境里把它打开SET sql_safe_updates 1;开了之后UPDATE和DELETE如果没有WHERE条件或者WHERE条件不是索引字段MySQL 会直接报错拒绝执行。这相当于给你的数据库操作加了一个物理保险栓习惯之后非常安心。DELETE 还有一个细节如果你要清空一张大表又不想用TRUNCATE因为要保留自增 ID可以考虑分批删除DELETE FROM logs WHERE created_at 2024-01-01 LIMIT 10000;一次删 1 万条循环执行避免一次性删百万行导致 Binlog 暴涨、从库延迟。这也是我在实际项目里处理历史数据时常用的思路。3.3 DML 与事务的衔接DML 的三大操作INSERT、UPDATE、DELETE都会改动数据所以它们和事务的关系特别密切。MySQL 默认每次单条 SQL 自动提交也就是说你执行一条UPDATE它立刻生效、不可回滚。但真实业务往往是多条 DML 绑定成一个逻辑单位。比如转账操作一条UPDATE扣出账账户的余额另一条UPDATE加进账账户的余额中间任何一条失败钱就凭空消失了。这时候就要手动开启事务START TRANSACTION; UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2; COMMIT;如果第二条UPDATE执行时报错你可以在COMMIT之前执行ROLLBACK;两条变更一起撤销数据恢复原样。这就是 TCL 的作用。事务有 ACID 四个特性面试常考原子性Atomicity、一致性Consistency、隔离性Isolation、持久性Durability。对应到实际操作原子性靠ROLLBACK保证持久性靠 InnoDB 的 Redo Log 保证隔离性靠锁机制和 MVCC 保证。这些概念学起来枯燥但一旦你在项目里遇到“数据对不上账”的问题回头再看这几个词体会完全不一样。4. DQL实操查询语句才是日常主角4.1 SELECT 骨架执行顺序比书写顺序更重要DQL 是四类 SQL 里最常用、也最能拉开水平差距的部分。先看一个典型语句的完整骨架SELECT column1, COUNT(*) FROM table_name WHERE condition GROUP BY column1 HAVING condition ORDER BY column1 DESC LIMIT offset, count;这里有一个关键认知大多数新手都不知道SQL 的书写顺序和执行顺序并不一样。真实的执行顺序是FROM先确定从哪张表取数WHERE过滤行GROUP BY分组HAVING分组后过滤SELECT选出需要的列、计算表达式ORDER BY排序LIMIT分页为什么要强调这个顺序因为很多问题都出在这里。比如有人写WHERE COUNT(*) 10数据库直接报语法错误因为WHERE在分组之前执行而此时COUNT(*)根本还不存在。正确的做法是把聚合条件放到HAVING里。搞懂执行顺序这类问题一眼就能看穿。4.2 WHERE 条件与运算符细节WHERE是过滤行的地方重点说几个容易犯错的细节。和的区别。是 MySQL 特有的“安全等于”它能拿来和NULL比较。普通写法WHERE name NULL永远查不出数据因为NULL和任何值比较都返回NULL而NULL在条件判断里等价于FALSE。正确写法是WHERE name IS NULL或者WHERE name NULL。IN和NOT IN的坑。如果IN的列表里有NULL比如WHERE id IN (1, 2, NULL)结果是正常的。但WHERE id NOT IN (1, 2, NULL)结果永远为空。原因和上面一样与NULL比较返回未知行被过滤掉了。这个问题在子查询里更容易出现比如WHERE id NOT IN (SELECT user_id FROM blacklist)如果黑名单表里有NULL整个查询就废了。模糊匹配。LIKE %keyword%这种写法很有意思——它加了前置通配符MySQL 就没办法走索引了。如果业务上确实需要模糊搜索数据量大时就该考虑全文索引或搜索引擎。这个先记住结论后面性能优化部分再展开。4.3 排序、分页与去重排序用ORDER BYSELECT username, age FROM users ORDER BY age DESC, id ASC;这里有个细微点多个排序字段时先按第一个字段排第一个字段相同再按第二个排。DESC只作用于紧跟它的那个字段所以ORDER BY age DESC, id ASC的意思是年龄降序、ID 升序。如果写成ORDER BY age, id DESC年龄就是升序。去重有两种思路。DISTINCT最简单SELECT DISTINCT status FROM users;DISTINCT会把所有返回列组合在一起去重所以SELECT DISTINCT username, email去重的是“用户名和邮箱的组合”不是单独某个字段。如果你要对单列去重、但还想带出其他字段就必须用GROUP BY或窗口函数了SELECT username, MAX(age) FROM users GROUP BY username;分页是另一个高频操作。MySQL 里用LIMIT offset, countSELECT * FROM users ORDER BY id LIMIT 20, 10;这条语句表示跳过 20 条、取 10 条也就是第 21 到第 30 条。offset的计算公式是(页码 - 1) * 每页条数。翻页翻得深时offset会越来越大MySQL 需要扫描并丢弃前面的数据性能明显下降。真正的优化方案是“延迟关联”或“基于游标分页”-- 延迟关联先只查主键再关联回原表 SELECT u.* FROM users u INNER JOIN ( SELECT id FROM users ORDER BY id LIMIT 100000, 20 ) tmp ON u.id tmp.id;这个技巧在大数据量分页时非常实用也常见于面试题。4.4 聚合函数与 GROUP BY聚合函数就是把多行计算成一行。常用的有COUNT、SUM、AVG、MAX、MIN。SELECT status, COUNT(*) AS user_count, AVG(age) AS avg_age, MAX(created_at) AS latest_time FROM users GROUP BY status;这里有个高频错误SELECT里的非聚合列必须出现在GROUP BY中。MySQL 5.7 默认没开ONLY_FULL_GROUP_BY所以允许你写出不规范语句但结果往往是随机的同一行数据每次查出来都不同。MySQL 8.0 默认开启了这个模式直接报错。我建议你不管用哪个版本都主动遵循这条规则——写清楚GROUP BY别依赖数据库的“宽容”。GROUP BY和HAVING配合时注意HAVING里可以直接写别名或聚合函数而WHERE不行。比如SELECT username, COUNT(*) AS cnt FROM users GROUP BY username HAVING cnt 10;这种写法在 MySQL 里是允许的因为HAVING在SELECT之后执行能看到别名。4.5 连接查询内连接、外连接与笛卡尔积真实业务很少只查一张表连接查询是 DQL 的硬骨头。内连接INNER JOIN只返回两表匹配的行。左外连接LEFT JOIN返回左表全部行右表不匹配的补NULL。右外连接RIGHT JOIN返回右表全部行左表不匹配的补NULL。用一个订单场景举例SELECT u.username, o.order_no, o.amount FROM users u LEFT JOIN orders o ON u.id o.user_id;这条语句返回所有用户即使某些用户没有订单order_no和amount会显示为NULL。如果你用INNER JOIN没下过单的用户就不会出现在结果里。需求是“列出所有用户及其订单”就用LEFT JOIN需求是“只要下过单的用户”就用INNER JOIN。新手最怕的是忘了写连接条件导致笛卡尔积。比如SELECT * FROM users, orders;两张表各 1 万行结果就是 1 亿行查询直接卡死。所以ON条件永远要写清楚这也是我检查代码时最关注的一处。4.6 子查询与 EXISTS子查询就是嵌套在另一条 SQL 里的查询分两种标量子查询返回单个值可以放在SELECT、WHERE、HAVING里。表子查询返回多行多列通常放在FROM或IN里。-- 查年龄大于平均年龄的用户 SELECT username, age FROM users WHERE age (SELECT AVG(age) FROM users);-- 查有订单的用户 SELECT username FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders);IN子查询和EXISTS执行机制不同IN是先把子查询结果集算出来再去外层匹配EXISTS是外层每行去驱动内层查询只要查到了就返回TRUE停止继续扫描。早期 MySQL 优化器不太聪明NOT IN子查询的性能经常很差后来版本好多了。不过有一条经验仍然有效小表驱动大表、索引能命中时EXISTS通常更稳。4.7 慢 SQL 优化思路从写法和索引两个方向推进很多人搜“慢sql优化”其实 DQL 优化就两条主线改写 SQL 逻辑、优化索引设计。先看 SQL 写法层面我最常见的优化手段是尽量用覆盖索引。也就是SELECT的字段都能从索引里取到不用回表。避免在索引字段上做函数运算。比如WHERE DATE(created_at) 2024-06-01这个写法让索引失效改成WHERE created_at 2024-06-01 AND created_at 2024-06-02索引就能用上了。禁止前置通配符模糊匹配LIKE %keyword%会让索引失效前面已经提过。大偏移量分页用延迟关联前面也提过。再看索引层面用EXPLAIN查看执行计划EXPLAIN SELECT u.username, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.status 1;EXPLAIN输出里的type字段从好到差大致是systemconsteq_refrefrangeindexALL。看到ALL就要警惕它代表全表扫描。key字段显示实际用到的索引如果为NULL说明没走索引。rows是预估扫描行数数值越小越好。我排查慢 SQL 时第一件事就是跑EXPLAIN基本能定位 80% 的问题。5. DCL实操用户与权限控制5.1 创建用户与授权DCL 在实际项目里用得不算频繁但每个团队总得有一个人负责。如果你自己搭的测试库知道基本授权就够了。创建用户CREATE USER app_userlocalhost IDENTIFIED BY StrongPss123;这里app_user是用户名localhost是允许登录的主机。如果想让任意主机都能连就写%但这是有安全风险的写法。生产环境我会尽可能限定主机比如只允许应用服务器 IP192.168.1.10。授权GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO app_userlocalhost;这条语句只给了应用用户 DML 权限它不能建表、删库。万一应用被 SQL 注入入侵者也拿不到DROP权限损失会小很多。这就是最小权限原则——只给够用的权限不给多余的。5.2 回收权限与查看权限权限收回用REVOKEREVOKE DELETE ON shop.* FROM app_userlocalhost;查看某个用户的权限SHOW GRANTS FOR app_userlocalhost;我建议每次新项目上线前都跑一遍SHOW GRANTS检查所有数据库账号的权限。很多公司的数据库事故不是被黑客攻击而是开发手里握着root账号半夜一条误操作直接删库。权限收紧这事越小越早做越省心。另外提醒一下MySQL 8.0 默认的认证插件是caching_sha2_password老版本客户端连接时可能报认证失败。如果你用一些旧工具连不上 MySQL 8.0可能需要调整认证插件ALTER USER app_userlocalhost IDENTIFIED WITH mysql_native_password BY StrongPss123;这是我在用旧版 Navicat 连 MySQL 8.0 时踩过的坑顺手分享给你。6. 一个完整的实战串联从建库到报表6.1 场景与表设计理论再好不如实战走一遍。我选一个轻量电商场景用户、订单、订单明细三张表。需求是用户浏览商品提交订单后订单表记录基本信息订单明细表记录买了哪些商品、多少钱。管理后台要能按用户统计总消费额。表结构设计成三张表users用户基本信息orders订单主表包含总金额、订单状态order_items订单明细包含商品名、单价、数量6.2 建库建表 DDLCREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci; USE shop; CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, order_no VARCHAR(32) NOT NULL UNIQUE, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已取消, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, KEY idx_user_id (user_id), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, product_name VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, quantity INT NOT NULL DEFAULT 1, KEY idx_order_id (order_id), CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;注意几个设计细节金额字段用DECIMAL(10,2)绝对不用FLOAT或DOUBLE。二进制浮点数有精度误差钱算错就是事故。order_no建了唯一索引防止重复订单号。在orders.user_id和order_items.order_id上建普通索引因为后续高频查询都是按这些字段关联、过滤的。外键约束在中小型项目里还能用但大厂一般会去掉外键用应用层保证一致性因为外键会影响写入性能。这里我加上是为了演示约束生产环境自己权衡。6.3 写入测试数据 DML插入用户、订单、明细并且演示事务START TRANSACTION; INSERT INTO users (username, email) VALUES (laozhang, laozhangexample.com); SET user_id LAST_INSERT_ID(); INSERT INTO orders (user_id, order_no, total_amount, status) VALUES (user_id, NO20250601001, 39.90, 1); SET order_id LAST_INSERT_ID(); INSERT INTO order_items (order_id, product_name, price, quantity) VALUES (order_id, 机械键盘, 299.00, 1), (order_id, 鼠标垫, 19.90, 1); COMMIT;LAST_INSERT_ID()是获取上一个自增 ID 的常用函数在事务里用它可以把三张表的数据关联起来。我把商品金额写大了实际总金额 318.90你按需求改就行。6.4 报表查询 DQL需求一每个用户的消费总额按消费额降序排序。SELECT u.username, COUNT(DISTINCT o.id) AS order_count, COALESCE(SUM(o.total_amount), 0) AS total_spent FROM users u LEFT JOIN orders o ON u.id o.user_id AND o.status 1 GROUP BY u.id, u.username ORDER BY total_spent DESC;这里用LEFT JOIN是为了把没有订单的用户也查出来COALESCE把NULL转成0。注意GROUP BY写的是u.id, u.username符合ONLY_FULL_GROUP_BY的要求。需求二查某笔订单的商品明细。SELECT o.order_no, oi.product_name, oi.price, oi.quantity, (oi.price * oi.quantity) AS line_total FROM orders o INNER JOIN order_items oi ON o.id oi.order_id WHERE o.order_no NO20250601001;明细金额用price * quantity在查询里实时计算日常报表足够如果数据量极大也可以考虑在表里冗余一个line_total字段这叫“用空间换时间”。6.5 权限配置 DCL给应用账号只授权shop库不授权其他库CREATE USER shop_app% IDENTIFIED BY ShopApp2026; GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO shop_app%; FLUSH PRIVILEGES;注意FLUSH PRIVILEGES在 MySQL 8.0 里执行GRANT后其实不必须但留着也不影响算是个传统习惯。这样应用侧拿到的就是一个只能操作shop库增删改查的账号连CREATE和DROP都没有。7. 常见问题排查与面试题速查7.1 SQL 注入的原理与防御既然排行榜里有“sql注入”我必须拎出来单独说。SQL 注入的本质是把用户输入拼进了 SQL 语句破坏了原始语句结构。经典的万能密码写法是SELECT * FROM users WHERE username admin AND password xxx OR 11;如果后端代码是字符串拼接输入admin OR 11之类的参数WHERE条件就成了恒真式登录直接被绕过。要提醒的是我这里只讲原理不建议你做任何绕过实验。防御手段是铁律使用预编译语句PreparedStatement或参数化查询永远不要拼接 SQL。在程序里Java 的PreparedStatement、Python 的pymysql参数占位符、MyBatis 的#{}都能自动转义输入值。简单说参数化查询让数据库把 SQL 结构和数据分开处理用户输入再特殊也只是“数据”不会变成“命令”。另外两个配套措施严格校验输入类型比如数字参数强制转int数据库账号用最小权限应用账号不给DROP、CREATE权限。这三件事都做到基本就没有 SQL 注入的活路了。7.2 MySQL 锁分类与死锁排查热词里有“mysql锁的分类”这是面试高频也是生产环境定位“卡死”问题的基础。按粒度分表级锁Table Lock、行级锁Row Lock、页级锁。InnoDB 支持行级锁MyISAM 只有表级锁。行级锁并发能力强但锁开销大表级锁反过来。现在主流都用 InnoDB所以要重点掌握行级锁。按类型分InnoDB 的锁主要分两种共享锁LOCK IN SHARE MODE简称 S 锁允许其他事务继续加 S 锁读但不允许加 X 锁写。排他锁FOR UPDATE简称 X 锁允许自己读和写其他事务既不能加 S 锁也不能加 X 锁。SELECT ... FOR UPDATE是最常见的悲观锁写法。比如扣库存START TRANSACTION; SELECT quantity FROM inventory WHERE product_id 1 FOR UPDATE; -- 业务判断库存是否充足充足则扣减 UPDATE inventory SET quantity quantity - 1 WHERE product_id 1; COMMIT;FOR UPDATE把这一行锁住其他事务想再FOR UPDATE同一行就必须等当前事务提交。这个机制在电商扣库存、抢红包等场景里是基础设施。死锁的典型场景是两把FOR UPDATE锁交叉。事务 A 锁了id1事务 B 锁了id2接着 A 想锁id2B 想锁id1两边互不释放数据库就会触发死锁检测自动回滚其中一个事务。排查死锁有两个常用命令SHOW ENGINE INNODB STATUS;这条命令的输出里有LATEST DETECTED DEADLOCK区块能看到互相冲突的 SQL 语句。另外从 MySQL 5.7 开始performance_schema.data_locks表也可以查当前锁持有情况比看一大坨 status 输出更方便。预防死锁靠两条保持事务短小尽量在同一个事务里固定加锁顺序。比如业务里同时锁users和orders就统一先锁users再锁orders别一会儿先锁订单、一会儿先锁用户死锁概率直接小一个数量级。7.3 高频面试题速查表我把 DDL、DML、DQL、DCL 相关的常见面试题整理成一张表你可以对着自查面试题核心回答要点DROP、TRUNCATE、DELETE三者的区别DROP删表结构TRUNCATE清数据但保留表结构、不能回滚DELETE可以按条件删、可以回滚WHERE和HAVING的区别WHERE在分组前过滤行不能使用聚合函数HAVING在分组后过滤常与GROUP BY配合内连接和外连接的区别内连接只返回匹配行左连接返回左表全部行右表不匹配补NULLUNION和UNION ALL的区别UNION去重、UNION ALL不去重后者性能更好事务的隔离级别读未提交、读已提交、可重复读、串行化MySQL InnoDB 默认可重复读乐观锁和悲观锁的区别悲观锁用SELECT ... FOR UPDATE提前锁行乐观锁用版本号或时间戳在更新时校验SQL 注入如何产生用户输入拼接进 SQL 语句、破坏结构用参数化查询防御索引失效的常见情况函数计算、隐式类型转换、前置通配符、OR连接非索引条件7.4 事务隔离级别与常见异常事务隔离级别是 MySQL 面试绕不开的高地。InnoDB 默认是“可重复读”配合 MVCC 机制解决了一部分幻读问题。但如果你开启了可重复读又在一个事务里做SELECT ... FOR UPDATE锁和 MVCC 的交互会变得复杂这也是网上很多“MySQL 幻读到底有没有解决”争论的来源。实操层面你只需要记住默认隔离级别不用改能处理绝大多数业务。如果出现并发写冲突或数据覆盖优先考虑在应用层加版本号或唯一约束而不是一上来就调隔离级别。调到“串行化”虽然解决了所有问题但并发能力急剧下降不到万不得已别碰。慢查询日志也是一个排查利器。开启的方式SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;这会把执行时间超过 1 秒的 SQL 记录到日志文件里。配合mysqldumpslow工具可以按频率排序快速找到最耗时的 SQL。我一般拿到一个新接手的老项目第一步就是开慢查询日志跑几天看结果比瞎猜高效太多。结语个人体会写了这么多最后分享几条我这些年跟 MySQL 打交道最深的感觉。SQL 分类这件事看着是基础概念其实决定了你解决问题的能力。我见过太多人遇到“没权限”报错就重装 MySQL遇到“数据乱”就手动一条条改都是因为没有建立分类思维。事后回想如果把 DCL 权限理清楚、把 DML 的事务边界想明白很大一部分问题在出现之前就能避免。如果你刚入门我建议不要急着去背那些冷门命令而是按本文第六章那种思路从建库、建表、写数据、查报表、配权限完整走一遍。走完这一遍你对 DDl、DML、DQL、DCL 的感受会从“四个名词”变成“四个工具箱”以后遇到问题一眼就知道该掏哪个工具。还有一个实用小建议在你自己的电脑上装一个 MySQL 8.0开一个测试库建几张表往里面灌个十万行数据再拿EXPLAIN折腾一遍。折腾完这篇文章里的很多点都会变成你自己的肌肉记忆。数据库这东西纸上得来终觉浅多动手才是最快的成长路径。