做开发这些年我见过太多同事一提到 SQL 就现搜现用。不是说查文档不好但如果你连 SELECT、JOIN、GROUP BY 这些最基础的语句都要临时找模板写出来的查询大概率有两个问题一个是慢一个是错。SQL常用语句这门基本功说难不难说简单也不简单关键是你有没有把底层逻辑理顺。这篇文章我打算把最常用、也最容易踩坑的 SQL 基础语句完整过一遍从建表到查询、从增删改到多表连接再补充一些面试和工作中高频出现的进阶用法。不管你是刚入门的新手还是写了几年业务代码想补一补数据库短板的同学这份内容都能当一份能查能抄的手册用。1. 动笔之前先搞清楚 SQL 的底层逻辑1.1 SQL 的本质你在跟数据库说“要什么”不是“怎么做”很多人学 SQL 总觉得语法太多记不住其实是因为没理解 SQL 和普通编程语言最大的区别。你用 Java、Python 写逻辑时要一步步告诉计算机“先做什么、再做什么”但 SQL 是声明式语言你只需要告诉数据库“我要什么”数据库引擎自己会决定怎么取数据。打个比方。你去餐厅点菜说“我要一份红烧肉”不用跟厨师说“先把锅烧热再倒油再把肉放进去翻炒”。SQL 也是这个道理。你写SELECT name FROM users WHERE id 1不需要关心数据库是先读索引还是先全表扫描只需要表达清楚“我要 id 为 1 的用户的 name 字段”。理解了这一点你就知道为什么 SQL 的写法顺序和执行顺序不一样了。这个点后面讲 SELECT 时会细说这里先记住一个结论SQL 是一门“表达需求”的语言不是“实现步骤”的语言。SQL 语句按功能可以分成几大类这是基础中的基础先建个框架分类作用常见关键字DQL数据查询SELECTDML数据操作INSERT、UPDATE、DELETEDDL数据定义CREATE、ALTER、DROPDCL数据控制GRANT、REVOKETCL事务控制BEGIN、COMMIT、ROLLBACK日常开发中DQL 用得最多DML 次之DDL 主要在建表和改表结构时用。DCL 一般由 DBA 管你只要知道有这个东西就行。TCL 是保护数据安全的重要工具后面第 3 章会展开。1.2 建库建表DDL 是后面所有操作的地基没有表后面所有的查询和操作都是空谈。所以第一步是建库建表。这里给一套最小的练习环境我后面所有示例都会基于这套表来演示。CREATE DATABASE IF NOT EXISTS shop DEFAULT CHARSET utf8mb4; USE shop; CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(20) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) );有两个细节值得强调。第一个是字符集。建库时我指定了utf8mb4不是utf8。utf8mb4是完整的 UTF-8 实现能存 emoji 和生僻字而 MySQL 里的utf8只是utf8mb3最多只能存 3 个字节遇到特殊字符会报错。这几乎是新手折腾半小时才能发现的坑。第二个是外键。我加了FOREIGN KEY约束目的是保证数据完整性防止你插入一个不存在的user_id。但在实际的高并发业务系统里很多团队会刻意不用物理外键因为外键会带来额外的锁开销和写入性能损耗改为在应用层保证关联关系。这个取舍没有绝对对错看业务场景。作为学习建外键能帮你理解表之间的关系。改表结构也是 DDL 的一部分常用语句就是ALTER TABLE-- 加列 ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email; -- 删列慎用 ALTER TABLE users DROP COLUMN phone; -- 改列类型 ALTER TABLE users MODIFY COLUMN name VARCHAR(100) NOT NULL;生产环境改表要非常谨慎尤其是大表加列。MySQL 8.0 支持ALGORITHMINSTANT快速加列但老版本或某些操作依然会锁表导致线上服务卡顿。建议在大表上做 DDL 之前先查一下目标数据库版本和当前表的行数。1.3 准备一份练习数据别拿生产库练手我见过不少新人刚学了几天 SQL 就在生产库上试手这是大忌。数据无价一条没带 WHERE 的 DELETE 可能让整个团队连夜加班。练习就用练习数据自己造几行数据完全够用。基于上面的表结构我准备几条示例数据后面所有查询演示都用它INSERT INTO users (name, email) VALUES (张三, zhangsanexample.com), (李四, lisiexample.com), (王五, wangwuexample.com); INSERT INTO orders (user_id, amount, status) VALUES (1, 199.00, 已完成), (1, 99.00, 待付款), (2, 399.00, 已完成), (3, 59.00, 已取消), (2, 129.00, 已完成);建议你本地装一个 MySQL 8.0 或者 SQL Server 2019 以上版本把这段 SQL 跑一遍后面看到每个示例都亲手执行一次。只看不练等于没学。2. SELECT 查询SQL 里 80% 的日常都在这2.1 基础查询与运算列SELECT 是最基础、最常用的语句。基础写法就这么几种-- 查所有列开发中少用但学习时方便看数据 SELECT * FROM users; -- 查指定列 SELECT name, email FROM users; -- 给列起别名AS 可以省略但建议写上清楚 SELECT name AS 用户名, email AS 邮箱 FROM users; -- 查询时做运算这列叫“计算列” SELECT user_id, amount, amount * 0.9 AS 折扣价 FROM orders;这里要引出一个核心概念SQL 的书写顺序和执行顺序。书写顺序是SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY - LIMIT但数据库实际执行顺序是FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT。这个顺序差异直接解释了一个新手常犯的错在 WHERE 里用 SELECT 里起的别名。-- 这段会报错Unknown column n in where clause SELECT name AS n FROM users WHERE n 张三;因为 WHERE 在 SELECT 之前执行这时候别名n还不存在。而 ORDER BY 在 SELECT 之后执行所以 ORDER BY 里可以用别名。理解执行顺序比死记硬背“WHERE 不能用别名”这种结论靠谱得多。2.2 WHERE 过滤的写法与坑WHERE 负责过滤行核心运算符是这么几类比较运算符、、、、、、范围判断BETWEEN AND、IN、模糊匹配LIKE和空值判断IS NULL。实际使用中有几个坑最常踩。第一个坑是 NULL 的判断。NULL 不是空字符串也不是 0它是“不知道”的意思。任何 NULL 参与比较结果都是“不知道”所以WHERE email NULL永远查不到数据必须写成WHERE email IS NULL。第二个坑是LIKE的匹配规则。%代表任意多个字符_代表单个字符。查所有张姓用户写WHERE name LIKE 张%。注意如果字段里存了 NULLLIKE是匹配不上的NULL 需要单独处理。第三个坑是逻辑运算符的优先级。AND的优先级高于OR这跟大部分编程语言一样。所以下面这两条 SQL 意思完全不同-- 实际含义status已完成 OR (status待付款 AND amount100) SELECT * FROM orders WHERE status 已完成 OR status 待付款 AND amount 100; -- 想表达“已完成或待付款且金额大于100”必须加括号 SELECT * FROM orders WHERE (status 已完成 OR status 待付款) AND amount 100;我自己见过不止一次因为忘记加括号导致统计数据出错的情况。写复杂条件时一律用括号把逻辑分组表达清楚别依赖默认优先级。2.3 排序、分页与去重日常看数据几乎离不开排序和分页。-- 金额从高到低金额相同按创建时间从早到晚 SELECT * FROM orders ORDER BY amount DESC, created_at ASC; -- 分页跳过前 20 条取 10 条 SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 20;LIMIT的写法在不同数据库里差异很明显这个要注意MySQLLIMIT 10 OFFSET 20等价于LIMIT 20, 10。注意 MySQL 的LIMIT 偏移量, 行数很多人会把顺序记反。SQL Server 2012OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY。Oracle 12c跟 SQL Server 类似也是OFFSET ... FETCH NEXT ...老版本只能用ROWNUM。分页还有一个性能问题。LIMIT 100000, 20不是直接跳过 10 万条而是扫描完前 10 万条再丢掉数据量大时会非常慢。优化的方式是用“延迟关联”SELECT * FROM orders WHERE id (SELECT id FROM orders ORDER BY id LIMIT 1 OFFSET 100000) LIMIT 20;先用覆盖索引快速定位到目标位置再回表取数据而不是一路扫过去。去重是高频操作基础写法是DISTINCT-- 所有不同的订单状态 SELECT DISTINCT status FROM orders; -- 有多少个不同的用户下过单 SELECT COUNT(DISTINCT user_id) FROM orders;需要注意DISTINCT作用于整行不是只作用于后面的第一列。SELECT DISTINCT status, user_id的意思是“status 和 user_id 组合起来不重复”。关于去重更进阶的写法第 5 章会展开。2.4 聚合函数与 GROUP BY 分组聚合函数是用来做统计的最常用的五个是COUNT、SUM、AVG、MAX、MIN。-- 订单总数、总金额、平均金额 SELECT COUNT(*) AS 订单数, SUM(amount) AS 总金额, AVG(amount) AS 平均金额 FROM orders;GROUP BY解决的是“按组统计”的问题。比如按用户统计消费情况SELECT user_id, COUNT(*) AS 订单数, SUM(amount) AS 消费总额 FROM orders GROUP BY user_id;这里有一个必须要守的规则SELECT里的非聚合列必须出现在GROUP BY里。什么意思-- 错误name 既不在 GROUP BY 里也不是聚合函数 SELECT user_id, name, SUM(amount) FROM orders GROUP BY user_id;因为分组后每个组可能有多行数据库不知道该选哪个name。MySQL 在关闭ONLY_FULL_GROUP_BY时不会报错会随机取值这是很多统计结果莫名其妙出错的根源。建议你把ONLY_FULL_GROUP_BY开着让数据库帮你把关。HAVING是对分组后的结果做过滤。它和WHERE最大的区别是WHERE在分组前过滤行HAVING在分组后过滤组。WHERE里不能用聚合函数HAVING里可以-- 找出消费总额超过 100 的用户 SELECT user_id, SUM(amount) AS 消费总额 FROM orders GROUP BY user_id HAVING SUM(amount) 100;如果能把WHERE能完成的事放WHERE里做就别放到HAVING提前过滤掉无用的行能减少分组计算量性能更好。3. 增删改INSERT / UPDATE / DELETE 的实操细节3.1 INSERT 的三种写法插入数据有三种常见写法按场景选用。单行插入和最常用的多行插入INSERT INTO users (name, email) VALUES (赵六, zhaoliuexample.com); INSERT INTO users (name, email) VALUES (钱七, qianqiexample.com), (孙八, sunbaexample.com);从另一张表复制数据过来这在做数据迁移、测试环境造数时特别好用INSERT INTO users (name, email) SELECT name, email FROM users WHERE id 10;MySQL 还有一个特有写法INSERT ... SET我偶尔会在初始化脚本里用因为一眼能看出列和值的对应关系不容易错位INSERT INTO users SET name 周九, email zhoujiuexample.com;插入数据时最常报的错是字段约束冲突NOT NULL字段没给值、唯一键重复、外键关联的数据不存在。所以插入前先看一下表结构确认哪些字段是必填的哪些有默认值。3.2 UPDATE 必须带 WHEREUPDATE的坑是历史级的。几乎所有新手都栽过“忘记写 WHERE全表被更新”的跟头。-- 正确只更新指定订单 UPDATE orders SET status 已完成 WHERE id 1001; -- 灾难把整张表的状态都改成已完成 UPDATE orders SET status 已完成;为什么容易忘因为写SELECT时会很自然地写条件但写UPDATE时心里想着“就改这一条”手一快就漏了。我的建议有两个。第一养成先查后改的习惯。执行 UPDATE 之前先用同一条 WHERE 条件跑一遍 SELECT确认命中数据是自己想改的那些。-- 先查 SELECT * FROM orders WHERE id 1001; -- 确认无误再改 UPDATE orders SET status 已完成 WHERE id 1001;第二敏感操作一定开事务。打开事务后UPDATE 执行完先 SELECT 检查确认没问题再 COMMIT不对就 ROLLBACK。这个习惯能救你一命。一次更新多列时用逗号分隔不要每列写一个 SETUPDATE orders SET status 已完成, paid_at NOW() WHERE id 1001;3.3 DELETE 与 TRUNCATE 怎么选DELETE删除指定行TRUNCATE清空整张表。看着都是删数据区别很大对比项DELETETRUNCATE能否加 WHERE可以不可以只能全清删除方式逐行删除直接释放整张表的数据页执行速度慢尤其数据量大时非常快事务回滚可以回滚多数数据库不可回滚SQL Server 在事务内可回滚自增 ID不重置大多数数据库会重置触发器每行都会触发不触发-- 删除指定订单 DELETE FROM orders WHERE id 1001; -- 清空订单表自增 ID 会重置 TRUNCATE TABLE orders;生产环境删除大量数据时最忌讳一条DELETE FROM big_table WHERE ...一把梭。一次性删上百万行会导致锁表时间过长、主从延迟、事务日志暴涨。正确做法是分批删除-- 每批只删 1000 行删完一次 Commit 一次 DELETE FROM big_table WHERE create_time 2024-01-01 LIMIT 1000;重复执行直到影响行数为 0 为止。这个技巧在很多大厂的数据清理场景里是标准操作。3.4 事务让增删改更安全的保护壳事务简单理解就是“一组要么全成功、要么全失败的操作”。最经典的场景是转账BEGIN; UPDATE accounts SET balance balance - 100 WHERE id 1; UPDATE accounts SET balance balance 100 WHERE id 2; COMMIT;如果第二条 SQL 执行失败直接ROLLBACK第一条的扣款也会被撤销。没有事务的话钱就凭空消失了。事务有四个特性缩写是 ACID原子性Atomicity要么全成功要么全回滚。一致性Consistency事务前后数据总量不变约束不被破坏。隔离性Isolation事务之间互不干扰。持久性Durability提交后数据永久保存。基础用法只需要掌握BEGIN或START TRANSACTION、COMMIT、ROLLBACK三个关键字。注意MySQL 中 DDL 语句是隐式提交的也就是说 CREATE TABLE、ALTER TABLE 这些操作不能放在事务里回滚事务只对 DML 生效。4. 多表连接与子查询从单表走向真实业务4.1 JOIN 系列INNER、LEFT、RIGHT 怎么选真实业务几乎没有只查一张表的时候。订单要关联用户查姓名商品要关联分类查名称报表要关联三四张表是常态。JOIN 就是把多张表按关联条件拼在一起。以users和orders为例先看最常用的两种。INNER JOIN内连接只返回两表都能匹配上的行SELECT u.name, o.id, o.amount, o.status FROM users u INNER JOIN orders o ON u.id o.user_id;LEFT JOIN左连接左表users全部保留右表orders没有匹配的则为 NULLSELECT u.name, o.id, o.amount, o.status FROM users u LEFT JOIN orders o ON u.id o.user_id;怎么选核心看“保留哪边”。你要统计所有用户以及他们的订单包括没下过单的用户就得用 LEFT JOIN。如果只关心有订单的用户用 INNER JOIN。RIGHT JOIN用得很少因为把表顺序换一下就能用 LEFT JOIN 替代可读性反而更好。有个记忆技巧LEFT JOIN看左表RIGHT JOIN看右表INNER JOIN两边都要匹配。JOIN 最容易踩的坑是数据翻倍。如果orders表里有 3 条订单都属于用户 1那么users 表 JOIN orders 表后用户 1 会变成 3 行。这时候如果你想统计用户数直接COUNT(*)是不对的要COUNT(DISTINCT u.id)。很多报表数据对不上账都是这个原因。4.2 子查询标量、IN、EXISTS子查询就是“嵌套在查询里的查询”可以出现在 SELECT、WHERE、FROM 等位置。标量子查询返回单一值可以用在比较运算中-- 查询 id1001 的订单对应的用户姓名 SELECT name FROM users WHERE id (SELECT user_id FROM orders WHERE id 1001);IN 子查询判断某个值是否在子查询结果集里-- 查下过单的用户 SELECT * FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders);EXISTS 子查询只判断子查询有没有返回行不关心具体值-- 同样查下过单的用户 SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id u.id);这里有一个关联子查询的概念。上面这条 SQL 就是关联子查询因为子查询里的o.user_id u.id引用了外层表的字段。数据库会对每个外层行执行一次子查询判断。EXISTS 和 IN 怎么选简单的经验是子查询结果集很小、外层表很大时IN 通常够用外层表小、子查询表大时EXISTS 往往更合适因为它找到一条匹配就会停不用把子查询结果全部算出来。现代优化器有时候会把 IN 改写成 EXISTS但你不能完全依赖优化器大查询里最好自己控制。4.3 UNION 与 UNION ALL纵向拼接JOIN 是把两张表左右拼在一起加宽UNION 是把两个查询结果上下拼在一起加长。要求两个查询的列数一致列类型尽量一致。-- 把已完成和已取消的订单信息合并 SELECT id, user_id, amount, status FROM orders WHERE status 已完成 UNION ALL SELECT id, user_id, amount, status FROM orders WHERE status 已取消;UNION会去重UNION ALL不去重。去重是有代价的数据库必须对结果集排序或哈希才能去掉重复行。如果你能确认两个结果集没有重复或者重复数据无所谓一律用UNION ALL性能差距在大数据量下很明显。4.4 自连接处理层级和上下行数据自连接就是一张表和自己连接核心技巧是必须用别名把“两个自己”区分开。最经典的场景是员工-领导层级关系-- 假设 employees 表结构为 id, name, manager_id SELECT e.name AS 员工姓名, m.name AS 领导姓名 FROM employees e LEFT JOIN employees m ON e.manager_id m.id;用LEFT JOIN是为了照顾老板manager_id为 NULL 时也能查出来只是领导姓名为 NULL。如果建表时有外键也可以让manager_id指向本表id方便管理。自连接另一个场景是找“连续”的数据比如查每天都比前一天销量高的商品本质就是同一张表按日期错位连接。这类写法面试偶尔会考核心思路是先给表起两个别名一个代表“今天”一个代表“昨天”然后关联日期差 1 天的条件。5. 高频进阶语法去重、空值、函数与窗口5.1 DISTINCT 与 GROUP BY 去重的选择很多人只知道DISTINCT能去重但要论灵活GROUP BY其实更强。两者在最简单的场景下结果一样-- 两种写法结果一样 SELECT DISTINCT user_id FROM orders; SELECT user_id FROM orders GROUP BY user_id;差异体现在两个地方。第一GROUP BY可以配合聚合函数做统计DISTINCT不行。你要统计每个用户下单了几次只能用GROUP BY。第二DISTINCT去重是对 SELECT 的整行去重。如果你同时 SELECT 了两三个字段它按这几个字段的组合去重。如果只需要对单个字段去重同时保留其他字段的信息DISTINCT就无能为力了得换窗口函数。按字段去重并保留完整行的经典写法是ROW_NUMBER()-- 取每个用户最新的一条订单 SELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders o ) t WHERE rn 1;这段 SQL 里PARTITION BY user_id表示按用户分组ORDER BY created_at DESC表示组内按时间倒序ROW_NUMBER()生成 1、2、3 的序号。最后取序号为 1 的行就是每个用户最新的一条订单。这个写法在数据清洗里非常常用热搜词里提到的“SQL 语句去重”很大一部分场景就是它。5.2 NULL 空值的判断与处理NULL 是 SQL 里最容易让人迷糊的概念。记住三条铁律NULL 不是空字符串也不是 0它表示“未知”。所有运算遇到 NULL结果都是 NULL。1 NULL结果是 NULLa || NULL结果也是 NULL。判断 NULL 只能用IS NULL和IS NOT NULL不能用 NULL或 NULL。处理 NULL 最常用的是COALESCE它的作用是返回参数列表里第一个非 NULL 的值-- 邮箱为空的显示“未填写” SELECT name, COALESCE(email, 未填写) AS email FROM users;MySQL 里有IFNULLSQL Server 里有ISNULL作用类似但COALESCE是标准 SQL 语法能在多种数据库里通用建议优先用它。还有一个高频面试点COUNT(*)和COUNT(列名)的区别。COUNT(*)统计行数包括 NULLCOUNT(列名)只统计该列非 NULL 的行。所以你要统计“有多少人填了邮箱”应该写COUNT(email)而不是COUNT(*)。5.3 常见字符串、日期、条件函数函数是写 SQL 的加速器。掌握最常用的十几个能覆盖绝大多数业务场景。字符串函数-- 拼接 SELECT CONCAT(first_name, , last_name) FROM employees; -- 截取从第1位开始取5个字符 SELECT SUBSTRING(hello world, 1, 5); -- 去空格 SELECT TRIM( abc ); -- 替换 SELECT REPLACE(hello world, l, L); -- 转大小写 SELECT UPPER(abc), LOWER(ABC);日期函数在不同数据库里差异很大。这里给一个对比表方便你在 MySQL 和 SQL Server 之间切换时不抓瞎功能MySQLSQL Server当前时间NOW()GETDATE()日期加减DATE_ADD(date, INTERVAL 1 DAY)DATEADD(day, 1, date)日期差值DATEDIFF(date1, date2)DATEDIFF(day, date1, date2)日期格式化DATE_FORMAT(date, %Y-%m-%d)FORMAT(date, yyyy-MM-dd)取日期部分DATE(date)CAST(date AS DATE)条件判断函数是CASE WHEN它相当于 SQL 里的 if-else写法和可读性都很好SELECT id, amount, CASE WHEN amount 100 THEN 大额订单 WHEN amount 50 THEN 中等订单 ELSE 小额订单 END AS amount_level FROM orders;CASE WHEN不只是用来打标签它还是后面行列转换的核心工具第 6 章会用到。5.4 窗口函数排名、累计、分组内计算窗口函数是近些年面试和实战的绝对高频考点。它跟聚合函数的区别在于聚合函数会把多行压成一行窗口函数不会它保留每一行同时在行的基础上做计算。语法结构是函数() OVER (PARTITION BY 分组列 ORDER BY 排序列)。最常见的三个排名函数对比ROW_NUMBER()生成连续编号1、2、3、4不并列。RANK()并列跳号两个第 1 名后下一个是第 3 名。DENSE_RANK()并列不跳号两个第 1 名后下一个是第 2 名。SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_salary FROM employees;窗口函数的另一大用途是累计计算。比如计算每天的累计销售额SELECT created_at, SUM(amount) OVER (ORDER BY created_at) AS 累计销售额 FROM orders;这里没写 PARTITION BY表示把所有行当作一个大组按 created_at 排序后逐行累加。还有LAG和LEAD可以取当前行的上一行或下一行的值做环比、同比非常方便SELECT created_at, amount, LAG(amount, 1) OVER (ORDER BY created_at) AS 前一天金额 FROM orders;注意一点窗口函数是在 SELECT 阶段计算的不能直接出现在 WHERE 里。如果你想按窗口函数的结果过滤必须套一层子查询就像前面去重示例那样。5.5 公用表表达式 CTE用 WITH 理顺复杂查询CTE 是“先定义一个临时结果集再在后面引用它”的语法。写法是以WITH开头给一段查询起个名字然后主查询直接引用这个名字。WITH 订单汇总 AS ( SELECT user_id, SUM(amount) AS total_amount FROM orders GROUP BY user_id ) SELECT u.name, s.total_amount FROM users u LEFT JOIN 订单汇总 s ON u.id s.user_id;这比直接写长嵌套子查询可读性好太多。我自己的体会是超过两层嵌套的子查询读起来就像绕迷宫而 CTE 能把逻辑切成几个有名字的模块别人看代码时一眼就能明白每一步在做什么。CTE 还可以连续定义多个用逗号分隔WITH 用户订单数 AS ( SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id ), 大客户 AS ( SELECT user_id FROM 用户订单数 WHERE order_count 3 ) SELECT * FROM users WHERE id IN (SELECT user_id FROM 大客户);MySQL 8.0 起才支持WITHSQL Server 2005 起支持。如果你还在用 MySQL 5.7只能继续写嵌套子查询。这也是我一直建议新项目直接用 MySQL 8.0 的原因之一。6. 面试和实战中最常考的 SQL 场景6.1 TopN 问题每个分组取前 N 条这是面试出现频率极高的一道题查出每个部门工资最高的前三个人。窗口函数版本非常简洁SELECT * FROM ( SELECT e.*, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees e ) t WHERE rn 3;想清楚用RANK()还是ROW_NUMBER()取决于业务要不要并列。如果薪资第 3 高的人有两个人RANK()会返回 4 个人ROW_NUMBER()只返回 3 个人。实际面试中通常先说需求再决定用哪个。MySQL 5.7 没有窗口函数时的解法是用用户变量模拟写法很绕我已经很久不写了。如果你还在维护老项目优先建议推动升级数据库版本而不是花时间背这些临时方案。6.2 行列转换行转列与列转行报表需求里经常要把一列的值转成多列展示。比如把不同订单状态的数量转成三列。行转列的核心是CASE WHEN配合聚合函数SELECT COUNT(CASE WHEN status 已完成 THEN 1 END) AS 已完成数量, COUNT(CASE WHEN status 待付款 THEN 1 END) AS 待付款数量, COUNT(CASE WHEN status 已取消 THEN 1 END) AS 已取消数量 FROM orders;这里有个小技巧为什么用COUNT而不是SUM因为CASE WHEN不满足条件时返回 NULLCOUNT自动忽略 NULL所以只统计了符合条件的行如果用SUM就需要写SUM(CASE WHEN status 已完成 THEN 1 ELSE 0 END)多写一个 ELSE。列转行的思路正好相反用 UNION ALL 把多列拆成多行SELECT 已完成 AS status, COUNT(*) AS cnt FROM orders WHERE status 已完成 UNION ALL SELECT 待付款, COUNT(*) FROM orders WHERE status 待付款 UNION ALL SELECT 已取消, COUNT(*) FROM orders WHERE status 已取消;理解行列转换关键在于想清楚“转换前后哪个是行、哪个是列”。多练几次后遇到这类需求心里就有数了。6.3 连续登录天数DATE_SUB 与 ROW_NUMBER 的经典组合连续登录天数是一道非常经典的面试题。题目长这样有一张登录日志表login_log(user_id, login_date)求连续登录 3 天及以上的用户。解题思路分四步。第一步对登录日期去重避免同一天登录多次影响计数SELECT DISTINCT user_id, login_date FROM login_log;第二步用ROW_NUMBER()按用户分组、按日期排序生成行号SELECT user_id, login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn FROM (SELECT DISTINCT user_id, login_date FROM login_log) t;第三步用登录日期减去行号。这里有一个很巧妙的性质如果一个用户连续登录那么“日期减行号”得到的日期是同一个。比如用户 1 在 1 月 1 日、2 日、3 日登录行号分别是 1、2、3减去后分别是 12 月 31 日、12 月 31 日、12 月 31 日完全一样。SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp FROM (SELECT DISTINCT user_id, login_date FROM login_log) t;第四步按用户和 grp 分组统计数量是否达到 3SELECT user_id, COUNT(*) AS 连续天数 FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp FROM (SELECT DISTINCT user_id, login_date FROM login_log) t ) t2 GROUP BY user_id, grp HAVING COUNT(*) 3;这套思路同样适用于连续签到、连续消费等问题掌握了它同类题目基本都能解。6.4 SQL 注入是什么日常如何避免SQL 注入是 Web 安全里最常见的漏洞之一本质是应用程序在拼接 SQL 字符串时没有过滤用户输入导致攻击者把恶意 SQL 拼进了正常语句。一个非常典型的简化例子登录功能里写了一句WHERE username 输入的用户名 AND password 输入的密码如果用户在用户名框输入admin --闭合引号后把后面的密码判断注释掉就可能绕过登录。万能的修复方式就是参数化查询也叫预编译语句。用 Java 的 PreparedStatement 或 PHP 的 PDO 预处理占位符把 SQL 结构和参数分开传给数据库数据库只把参数当数据不会当 SQL 执行。还有几点日常要注意动态排序字段名和表名不能直接拼接要做白名单校验数据库账号按最小权限分配应用账号只给必要的增删改查权限不给 DROP、TRUNCATE 这类危险权限。安全无小事宁可多写几行代码也别拿数据开玩笑。7. SQL 性能与常见问题排查7.1 慢 SQL 的常见原因慢 SQL 是热搜词里的大热门也是生产环境最让人头疼的问题之一。排查慢 SQL先看几个最常见的病因。第一是没走索引全表扫描。数据量小的时候感觉不出来一旦上了几百万行一条没走索引的查询能把数据库拖垮。第二是SELECT *。查了所有列传输的数据量大还经常导致排序、临时表等操作变慢。写查询时只写需要的列别图省事。第三是对索引列做函数运算。比如WHERE DATE(created_at) 2024-01-01因为对列做了函数处理索引就失效了。正确写法是用范围条件WHERE created_at 2024-01-01 AND created_at 2024-01-02。第四是隐式类型转换。字段是varchar类型查询条件却传了数字数据库会隐式转换同样可能让索引失效。第五是深分页。LIMIT 100000, 20需要扫描前 10 万行再丢弃数据量越大越慢。用上一章提到的“延迟关联”可以缓解。第六是 JOIN 字段没索引或者关联字段的类型不一致比如一边是int一边是varchar两边没法走索引。7.2 常用索引原理与使用建议索引的本质是额外维护一套有序的数据结构最常见的实现是 B 树。你可以把索引想象成书的目录没有目录时要找某个内容只能一页一页翻有了目录直接翻到对应的页码就行。但不是所有列都适合建索引。我的建议是适合建索引的列WHERE里高频出现的列、ORDER BY和GROUP BY的列、JOIN ON的关联列。这些列上用索引能显著减少扫描范围。不适合建索引的列区分度很低的列比如性别只有男女两种值查出来的数据还是接近一半索引帮不上忙频繁更新的列因为每次更新都要同步维护索引写入成本变高大文本字段比如TEXT类型不能直接建普通索引一般用全文索引或前缀索引。联合索引要关注“最左前缀原则”。索引(a, b, c)可以用于查询条件包含a、或者包含a和b、或者a、b、c的查询。但如果查询条件只写了b或c用不上这个联合索引。写查询时字段顺序要考虑和联合索引对齐。怎么看 SQL 到底有没有走索引用EXPLAINEXPLAIN SELECT * FROM orders WHERE user_id 1;重点看三个字段type从好到差依次是const、eq_ref、ref、range、index、ALL其中ALL是全表扫描能避免尽量避免key表示实际用到的索引名rows是预估扫描行数越小越好。7.3 常见报错与解决速查下面这张表是我在带新人和平时工作中频繁遇到的报错合集每一类都对应一个具体场景。遇到类似报错先对照排查报错信息原因解决办法Column id in field list is ambiguous多表连接时多个表都有 id 列没指定哪个表列前加表别名如u.idUnknown column xxx in where clause列名不存在或拼写错误DESC 表名确认列名Every derived table must have its own alias子查询作为临时表时没有起别名子查询后面加AS tmpYou cant specify target table orders for update in FROM clauseMySQL 不允许在 UPDATE 时直接查询同一个表并更新再多套一层子查询让数据先物化In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated columnMySQL ONLY_FULL_GROUP_BY 模式下SELECT 列不在 GROUP BY 中把多余列加入 GROUP BY或用聚合函数Cannot add or update a child row: a foreign key constraint fails插入或更新时外键关联的数据不存在先插入或确认主表的数据还有一些需要靠经验识别的“不报错但结果错”的情况比如 JOIN 导致的数据翻倍、NULL 参与计算导致结果为 NULL、忘记加 WHERE 导致全表更新。这些比报错更危险因为它们不会被数据库拦截只能靠开发者的细心和规范来防。7.4 平时练习和工作的建议最后给几条实操层面的建议都是我踩过坑换来的经验。先选对工具。DBeaver 是目前我用下来比较顺手的跨平台数据库客户端免费版就够用自带 SQL 格式化、执行计划查看、数据导出等功能Windows 上还有 HeidiSQL轻量好用导出 SQL 文件很方便。用工具的目的不是炫技而是减少手误、提升效率。写完 SQL 先格式化再检查。很多人写长 SQL 时括号和条件容易乱格式化之后缩进清晰问题一眼就能看出来。DBeaver 和 Navicat 都有快捷键一键格式化这是一个性价比极高的习惯。复杂查询拆开验证。不要试图一次写出一条十几行的 SQL正确做法是先分别确认每个子查询的结果再一步步拼起来。我写过很多复杂报表 SQL几乎都是先跑通最内层的查询确认数据没问题再一层一层套上去。养成看执行计划的习惯。写完一条关键查询顺手EXPLAIN一下看看有没有走索引、扫了多少行。不用做到专家水平能看懂type、key、rows三个字段就能避开绝大多数性能大坑。版本能新则新。MySQL 8.0 支持窗口函数、WITH语法、更好的优化器SQL Server 2019 也有不少实用新特性。守着老版本很多时候不是水平问题而是工具限制了你的写法。能用新特性解决的需求不用再绕老路。我自己平时写完一条稍微复杂的 SQL一定先EXPLAIN看一眼再决定要不要优化。很多时候问题不是 SQL 写错而是没走索引。最后再送大家一个小技巧建一张专门用来练手的表把网上看到的 SQL 面试题都在里面模拟一遍练到形成条件反射面试和实战都不会慌。