搞 MySQL 的时候日常打交道最多的就是 DQL也就是数据查询语言。别管你是写报表、做后台管理还是处理临时数据需求本质上都是在跟 SELECT 打交道。而单表查询又是这一切的地基——连一张表都查不明白后面 JOIN、子查询这些复杂操作基本就是空中楼阁。这篇内容我打算把 DQL 单表查询从头到尾拆开讲包含 SELECT、WHERE、ORDER BY、GROUP BY、HAVING、LIMIT 这些核心语法也把 NULL 判断、执行顺序、分页性能这些容易踩坑的地方一并说清楚。零基础可以当入门教程来读写过一段时间 SQL 但总感觉不扎实的人同样值得拿这篇来对照补漏。1. 先把 DQL 的脉络理清楚单表查询到底在干什么1.1 为什么单表查询是 SQL 学习中最重要的一块很多初学者有个误区觉得单表查询太简单上来就直接研究多表 JOIN。我见过不少写 SQL 的人JOIN 语法背得滚瓜烂熟结果连“统计每个部门平均工资”这种单表分组需求都写得磕磕绊绊。原因很简单JOIN、子查询这些高级玩法本质上是把多张表先变成一张虚拟的大表然后仍然用单表查询的思维去过滤和聚合。也就是说你在单表里学到的 WHERE 过滤逻辑、GROUP BY 分组逻辑、HAVING 过滤组的逻辑到了任何复杂 SQL 里都同样生效。单表查询解决的典型问题包括从某张表里取哪些列、按什么条件筛选行、怎么排序、怎么分组统计、怎么分页。这些问题在真实业务里无处不在。比如看用户表里最近注册的人有哪些统计每天订单量有多少查某个分类下价格最高的商品是哪个。把单表查询练熟等于掌握了 SQL 的基本思维方式后面再接触复杂查询无非是在这张虚拟大表上做同样的筛选和统计而已。1.2 SELECT 的书写顺序和执行顺序是两码事新手写 SQL 时感觉语法难记往往是因为没有搞明白 SQL 的执行顺序。很多人以为 SQL 是从 SELECT 开始执行的实际上完全不是。一个标准的单表查询包含多个子句它们在数据库内部的执行顺序大致如下FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY - LIMIT数据库会先确定从哪张表取数据然后逐行过滤 WHERE 条件再把过滤后的结果按 GROUP BY 分组HAVING 对分组后的结果做二次过滤接着才计算 SELECT 中要展示的列最后按 ORDER BY 排序并用 LIMIT 截取行数。这个执行顺序不光是用来背的它直接决定了你能在哪些子句里使用什么内容。比如 WHERE 里不能出现聚合函数因为 WHERE 执行时还没做分组再比如 SELECT 里定义的别名在大多数数据库里不能用在 WHERE 中因为 WHERE 执行时 SELECT 的列还没计算出来。理解了这条执行链很多报错就不用死记硬背了。2. 基础查询从 SELECT 开始迈出第一步2.1 先用一张表把环境搭起来理论说再多不如动手跑一遍。为了后续的示例能直接复制执行先创建一张员工表顺便插入十条测试数据。这张表会贯穿整篇文章后面的过滤、排序、分组、分页示例都以它为基础。CREATE TABLE employee ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 员工编号, name VARCHAR(50) NOT NULL COMMENT 姓名, dept_id INT COMMENT 部门编号, salary DECIMAL(10, 2) COMMENT 月薪, bonus DECIMAL(10, 2) DEFAULT NULL COMMENT 奖金, hire_date DATE COMMENT 入职日期 ) COMMENT 员工测试表; INSERT INTO employee (name, dept_id, salary, bonus, hire_date) VALUES (张三, 1, 8000.00, 1000.00, 2021-03-15), (李四, 1, 9500.00, 1500.00, 2020-07-01), (王五, 2, 12000.00, NULL, 2019-11-20), (赵六, 2, 10500.00, 800.00, 2022-01-10), (孙七, 3, 7800.00, NULL, 2022-06-05), (周八, 3, 8900.00, 1200.00, 2021-09-30), (吴九, 1, 9600.00, 2000.00, 2020-02-14), (郑十, 2, 13500.00, 2500.00, 2018-05-22), (钱十一, 3, 7000.00, 500.00, 2023-01-08), (黄十二, 1, 8200.00, NULL, 2021-12-01);先说明一下为什么 bonus 字段允许为 NULL而不是直接写成 0。在真实业务中NULL 和 0 的含义是不同的0 表示这个值确定是 0而 NULL 表示这个值根本不存在比如某个员工没有发放奖金记录。这个语义上的区别在后续的统计查询里会产生非常大的影响后面讲聚合函数时会专门说到。2.2 SELECT 列查询最基础的取数操作最简单的查询就是把整张表所有列都拿出来SELECT * FROM employee;*代表所有列适合快速看数据但在生产环境里我强烈建议少用。主要原因有两个一是如果你只需要两三列却把几十个列的数据全部查出来网络传输和内存占用都会浪费二是当表结构发生变化比如新增了一个很大的字段SELECT *可能会把不需要的大字段也带出来导致查询变慢。更规范的做法是显式列出需要查询的列名SELECT id, name, dept_id, salary FROM employee;这样写有两个好处查询意图一目了然别人看你的 SQL 就知道要什么同时数据库在执行时可以只读取需要的列效率更高。如果你想要给列起一个更易读的名字可以用别名SELECT name AS 姓名, salary AS 月薪, salary * 12 AS 年薪 FROM employee;这里值得注意的一点是salary * 12这种写法是允许的SELECT 子句里可以直接写表达式不一定非要是表里的原始列。这是一个很实用的能力很多报表需求里的计算列都是这么出来的。至于中文别名MySQL 是支持的但为了兼容性和可读性一般建议用英文别名或者加引号避免某些客户端工具因为字符集问题显示乱码。2.3 DISTINCT 去重看似简单实则容易翻车当你想知道一张表里到底有哪些部门编号时直接SELECT dept_id FROM employee会返回重复值。这时候用 DISTINCT 去重SELECT DISTINCT dept_id FROM employee;DISTINCT 的逻辑是对返回结果中的所有列做组合去重而不是只对某一列去重。这句话听起来简单但很多人实际用的时候会犯错。比如你写SELECT DISTINCT dept_id, salary FROM employee去重判断的是 dept_id 和 salary 这两个列的组合是否相同而不是只看 dept_id。这符合 SQL 的标准语义但在业务中经常导致查询结果出乎意料。另外还有一个高频陷阱不要把 DISTINCT 当成“去重后取前几条”的工具。去重和排序、限制行数之间有不同的执行时机一旦组合不当就会出现语义混乱。如果只是想看有哪些部门用 DISTINCT 没问题如果要统计每个部门的数据量应该用 GROUP BY后面的章节会详细讲。3. WHERE 条件过滤让查询精准命中目标行3.1 条件过滤的本质就是对每一行做判断WHERE 子句的作用是从表中筛选出满足条件的行。它的执行逻辑很有画面感数据库把表里的数据逐行扫描一遍每一行拿到 WHERE 条件里去判断条件为真就留下为假就丢掉无法判断为真的也不留。所以 WHERE 里写的是什么决定了你能留下什么。最常见的写法是使用比较运算符比如等值比较、大小比较。我平时最常使用的条件运算符整理成了下面的表新手可以先把这个表存下来写 SQL 时对照着用运算符含义示例等于WHERE salary 8000! 或 不等于WHERE dept_id 1大于WHERE salary 8000大于等于WHERE salary 8000小于WHERE salary 8000小于等于WHERE salary 8000BETWEEN AND闭区间范围WHERE salary BETWEEN 8000 AND 10000IN在指定值列表中WHERE dept_id IN (1, 3)LIKE模糊匹配WHERE name LIKE 张%IS NULL判断为空WHERE bonus IS NULL其中和!是完全等价的不等号写法两者都能用选一个自己习惯的就好。BETWEEN AND 是一个闭区间也就是包含两个边界值。拿BETWEEN 8000 AND 10000来说它等价于salary 8000 AND salary 10000千万别理解成开区间。3.2 LIKE 模糊查询注意百分号和下划线的区别LIKE 是单表查询里非常常用的模糊匹配方式它支持两个通配符%代表任意长度的任意字符包括零个字符_代表任意单个字符。举个例子-- 查询所有姓张的员工 SELECT * FROM employee WHERE name LIKE 张%; -- 查询名字以“四”结尾的员工 SELECT * FROM employee WHERE name LIKE %四; -- 查询名字长度为两个字的员工且第一个字是王 SELECT * FROM employee WHERE name LIKE 王_;第一个 SQL 匹配的是姓张的人第二个 SQL 匹配的是最后一个字为“四”的人第三个 SQL 用下划线强制姓名长度为两个字。理解%和_的差别很重要很多人写 LIKE 时习惯性全部用%导致_能实现的精确长度匹配总是写不出来。这里要提前打个预防针如果 LIKE 的前导通配符是%比如LIKE %四数据库通常无法使用索引会做全表扫描。数据量小的时候感觉不到问题一旦表里有几百万行数据这种查询会明显变慢。业务上如果可以避免头部的模糊匹配比如只查询“以某个前缀开头”的模糊条件索引就能生效。这一点在真实项目优化中经常被忽略。3.3 多条件组合AND、OR 与 NOT 的优先级陷阱实际业务中的过滤条件往往不止一个这时候需要组合条件。AND 表示同时满足OR 表示满足任意一个即可NOT 表示取反。比如查部门 1 并且薪资大于 8000 的员工SELECT * FROM employee WHERE dept_id 1 AND salary 8000;查部门 1 或者部门 3 的员工SELECT * FROM employee WHERE dept_id 1 OR dept_id 3;问题来了如果 AND 和 OR 混在一起写优先级怎么算SQL 里 AND 的优先级高于 OR所以WHERE dept_id 1 OR dept_id 2 AND salary 9000实际执行的是dept_id 1 OR (dept_id 2 AND salary 9000)。很多业务 bug 就是这么来的看起来条件没错查出来的结果却莫名其妙多了一堆行。我的建议是只要条件里同时出现 AND 和 OR一定要加括号明确优先级。括号不改变 SQL 的正确性但能保证你写的意图和实际执行的结果完全一致也让后来维护的同事不用猜你的心思。另外还有一个经验之谈OR 条件在数据库优化器里往往不如 UNION 或改写后的多个条件好用尤其是涉及索引时OR 很容易让索引失效。这个先记在心里等学到执行计划优化时会有更深的体会。3.4 NULL 的判断所有初学者都绕不开的坑NULL 在 SQL 里是一个非常特殊的概念它既不等于空字符串也不等于数字 0它表示“未知”或“不存在”。最让新手困惑的是任何值与 NULL 做比较结果都是 NULL而不是 TRUE 或 FALSE。比如下面这条 SQL你可能希望查出 bonus 为空的员工但它的结果一定是空-- 错误写法结果为空 SELECT * FROM employee WHERE bonus NULL;判断 NULL 只能用IS NULL或IS NOT NULLSELECT * FROM employee WHERE bonus IS NULL; SELECT * FROM employee WHERE bonus IS NOT NULL;为什么bonus NULL查不出数据因为 NULL 不是一个具体的值比较运算符在遇到 NULL 时结果也是 NULL而 WHERE 只保留结果为 TRUE 的行。这是 SQL 的三值逻辑TRUE、FALSE、NULL。理解了这个逻辑后面遇到的很多诡异查询结果就都解释得通了。比如WHERE bonus 1000这个查询看起来是想排除 bonus 等于 1000 的员工但那些 bonus 为 NULL 的员工也会被排除因为 NULL 在比较运算里同样产生了不确定的结果不会保留。4. ORDER BY 排序让查询结果有序可读4.1 排序列和排序方向从数据库里查出来的数据默认是按照存储顺序返回的但这个顺序通常没有意义所以需要 ORDER BY 来指定排序规则。最基本的排序分两种方向ASC 表示升序从小到大DESC 表示降序从大到小。不写排序方向时默认是升序。-- 按薪资从低到高排序 SELECT name, salary FROM employee ORDER BY salary ASC; -- 按薪资从高到低排序 SELECT name, salary FROM employee ORDER BY salary DESC;多列排序是面试里很喜欢考的一个点。比如先按部门升序排列部门相同的再按薪资降序排列SELECT name, dept_id, salary FROM employee ORDER BY dept_id ASC, salary DESC;这种写法的执行逻辑是先按第一个排序字段 dept_id 排列只有在 dept_id 相同的情况下才会用第二个字段 salary 来决定先后顺序。注意不要理解成“同时按两个字段排序”它们是分先后判断的。实际应用中报表经常需要“先按部门再按薪资”这样的层级排序这个语法非常常用。4.2 NULL 在排序中的默认位置当排序列里出现 NULL 时不同数据库的处理规则不一样。在 MySQL 中升序时 NULL 默认排在最前面降序时 NULL 默认排在最后面。这个行为在很多场景下可能不是你想要的效果。比如按 bonus 降序排列你希望奖金多的人排在前面但发现 bonus 为空的员工被排到了最后这没问题可如果按 bonus 升序排列NULL 排在最前面可能就不是合理的业务顺序了。如果希望 NULL 总是排在最后面可以配合IS NULL进行条件排序SELECT name, bonus FROM employee ORDER BY bonus IS NULL ASC, bonus ASC;这里bonus IS NULL这个表达式在行里是 1 或 0先按这个值升序非 NULL 的行值为 0 会排在前面NULL 的行值为 1 会排在后面同组内再按 bonus 升序。这种方式既保证了 NULL 的位置又不影响普通值的排序规则我在实际项目中用过很多次。4.3 用表达式和自定义顺序进行排序ORDER BY 不仅支持列名还支持表达式。比如按年薪排序SELECT name, salary, salary * 12 AS annual_salary FROM employee ORDER BY salary * 12 DESC;这里有个小细节ORDER BY 子句的执行在 SELECT 之后所以你也可以直接引用 SELECT 里的别名。比如写成ORDER BY annual_salary DESC也是可以的MySQL 支持这种写法。但在 WHERE 里就不能直接引用别名因为执行顺序不同。还有一种业务场景希望按照某个特定顺序排列比如部门顺序按照 2、1、3 这种自定义顺序来展示而不是字母序或数字序。MySQL 提供了 FIELD 函数来实现SELECT name, dept_id FROM employee ORDER BY FIELD(dept_id, 2, 1, 3);FIELD 函数会把第二个参数之后的列表值转换成序号匹配到哪个就返回哪个位置的下标。排序时 2 对应 11 对应 23 对应 3于是实现了自定义顺序。这个技巧在处理枚举状态排序时特别实用比如按业务流程状态“待审核、已审核、已驳回”排序而不是按字典序排。5. 聚合函数与分组统计从看明细到看汇总5.1 五个常用聚合函数单表查询不总是为了看明细数据更多时候是为了做统计汇总。SQL 提供了聚合函数它们可以把多行数据聚合成一个结果。核心聚合函数如下函数作用示例COUNT(expr)统计行数COUNT(*)、COUNT(id)SUM(expr)求和SUM(salary)AVG(expr)求平均值AVG(salary)MAX(expr)求最大值MAX(salary)MIN(expr)求最小值MIN(salary)最常用也最容易出错的是 COUNT。COUNT(*)统计的是整个结果集的行数不管某一列是否是 NULLCOUNT(列名)统计的是该列非 NULL 的值的个数。比如查 bonus 字段COUNT(bonus)不会统计 NULL 的员工而COUNT(*)会统计所有员工。所以“统计有多少条记录”这种需求我建议都用COUNT(*)语义明确MySQL 对它的优化也做得最好。另一种常见用法是COUNT(DISTINCT 列名)统计某列的去重数量比如查一共有几个部门SELECT COUNT(DISTINCT dept_id) FROM employee;SUM 和 AVG 会忽略 NULL 值这其实是个好特性。比如AVG(bonus)只会对 bonus 非 NULL 的行求平均不会把 NULL 当成 0 算出偏小的结果。但这也可能带来误解当你平均部门奖金时如果有些员工没有奖金你会发现平均结果比直觉要高因为分母不包括 NULL 的员工。5.2 GROUP BY 分组按维度把数据归类GROUP BY 的作用是把结果集按一个或多个列分成不同的组然后和聚合函数配合对每组分别做统计。比如统计每个部门的平均工资SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id;这条 SQL 的执行逻辑是这样的先把所有行按 dept_id 分成三组第 1 组、第 2 组、第 3 组然后对每组分别计算 AVG(salary)最终返回每个部门一行结果。这个思维模式非常重要一旦使用了 GROUP BY查询返回的结果就从“每一行是一个员工”变成了“每一行是一个分组”。在分组查询里有一个原则要遵守SELECT 子句里出现的列要么是被分组的列要么是被聚合函数包裹的列。比如SELECT name, dept_id, AVG(salary) FROM employee GROUP BY dept_id这种写法在 MySQL 的 ONLY_FULL_GROUP_BY 模式下会直接报错因为 name 没有出现在 GROUP BY 里也没有被聚合函数包裹数据库不知道该返回组内哪个员工的名字。MySQL 早期版本对这个限制比较宽松会随机取一个值很多看起来正常、实际上结果不确定的 SQL 就是这么来的。现在的 MySQL 5.7 以上默认开启了 ONLY_FULL_GROUP_BY遇到这种报错是在帮你避开逻辑错误。5.3 WHERE 和 HAVING 的区别过滤时机完全不同分组之后还有一个非常高频的问题分组前过滤和分组后过滤有什么区别WHERE 在分组前执行过滤的是“行”HAVING 在分组后执行过滤的是“组”。举两个例子感受一下区别-- 先过滤出部门 1 和部门 2 的员工再按部门统计平均工资 SELECT dept_id, AVG(salary) AS avg_salary FROM employee WHERE dept_id IN (1, 2) GROUP BY dept_id; -- 统计每个部门的平均工资只保留平均工资大于 9000 的部门 SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id HAVING AVG(salary) 9000;第一个例子是先按条件缩小数据集再进行分组WHERE 在 GROUP BY 之前执行第二个例子是先分组再对聚合结果进行过滤HAVING 在 GROUP BY 之后执行。因为执行顺序不同导致一个关键限制WHERE 里不能用聚合函数比如WHERE AVG(salary) 9000就是错的因为在 WHERE 执行的时候还没有分组AVG 无从谈起而 HAVING 里可以写聚合函数作为过滤条件。实际写统计 SQL 时建议遵循这样的步骤先想清楚需要哪些行参与统计用 WHERE 把无关数据过滤掉再考虑按什么维度分组最后看是否需要对分组结果做条件筛选有的话写在 HAVING 里。这样写出来的 SQL 高效、逻辑清晰别人也容易看懂。6. LIMIT 分页控制返回行数的必备手段6.1 LIMIT 的两种写法与分页公式LIMIT 用于限制查询返回的行数它也是分页功能的核心语法。MySQL 的 LIMIT 支持两种写法-- 只返回前 5 行 SELECT * FROM employee LIMIT 5; -- 从第 3 行开始取 2 行 SELECT * FROM employee LIMIT 2 OFFSET 2; -- 等价的另一种写法偏移量, 行数 SELECT * FROM employee LIMIT 2, 2;这里最需要注意的是 MySQL 的第二种写法第一个参数是偏移量 offset第二个参数才是行数 count。新手经常搞反把LIMIT 10, 20理解成取前 10 行实际它的意思是跳过 10 行取 20 行。为了避免混淆我在项目里一般都用LIMIT count OFFSET offset这种明确写法可读性更高不容易踩坑。分页查询有一个非常常用的公式。假设每页显示 pageSize 行当前要查询第 pageNum 页那么偏移量就是(pageNum - 1) * pageSize。比如每页 3 条查第 2 页就是偏移 3 条取 3 条SELECT * FROM employee ORDER BY id LIMIT 3 OFFSET 3;这里我特意加了 ORDER BY id原因后面会讲。分页查询不加排序返回的结果顺序不确定就可能出现前后两页数据重复或遗漏的问题。6.2 大偏移量的分页性能坑与优化思路LIMIT 看起来简单但遇到大偏移量时会非常危险。比如LIMIT 1000000, 10数据库并不是直接“跳过”前面一百万行它需要把这一百万行先查出来然后丢掉再取后面的 10 行。随着偏移量越来越大查询会越来越慢这是一个典型的性能陷阱。解决这个问题常用的思路是“基于主键的位置分页”。比如上一页查到了最大 id 是 100000下一页就可以用这样的条件SELECT * FROM employee WHERE id 100000 ORDER BY id LIMIT 10;这种写法利用主键索引快速定位不需要扫描和丢弃大量的行即使翻到很深的页码性能也能保持稳定。这里的前提是排序字段必须是 id 或者有索引的字段如果排序字段是普通字段就需要结合子查询来做延迟关联比如把主键查出来再关联回原表取数据。单表查询虽然入门但把这个优化思路刻在脑子里后面处理百万级数据量时能省下不少排查问题的时间。7. 单表查询常见问题与排查技巧7.1 明明有索引查询还是很慢的几个原因索引失效是 DQL 性能问题的重灾区。我总结过几个最典型的情况写 SQL 时只要下意识避开就能省掉很多临时优化的工作。第一在索引列上做运算。比如WHERE salary * 12 100000数据库无法直接用 salary 索引来定位因为它要先对每一行做乘法运算才知道结果。应该尽量写成WHERE salary 100000 / 12。第二隐式类型转换。比如字符串类型的列和数字比较或者数字列和字符串比较MySQL 会在内部做类型转换转换后索引可能失效。典型例子是手机号字段存的是 VARCHAR但查询时写WHERE phone 13800138000数据库会把 phone 转成数字再比导致全表扫描。正确的做法是写成WHERE phone 13800138000。第三LIKE 模糊匹配的前导通配符。LIKE %关键词会让索引失效但LIKE 关键词%可以正常走索引。和前两条一样核心都是让索引列保持原始状态参与判断。7.2 快速定位问题的 EXPLAIN 工具发现 SQL 慢第一时间应该用 EXPLAIN 查看执行计划。使用方法很简单在 SELECT 前面加上 EXPLAIN 关键字EXPLAIN SELECT * FROM employee WHERE dept_id 1;执行结果里会显示这张查询语句的执行信息。重点看三个字段type 表示访问类型从好到差依次是 system、const、eq_ref、ref、range、index、ALL其中 ALL 是全表扫描性能最差key 表示实际用到的索引如果是 NULL 就代表没走任何索引rows 是一个估算值表示要扫描多少行数字越大风险越高。单表查询的性能排查基本就是围绕这三列来定位问题如果 type 是 ALL 且 rows 很大就该回到上一条检查索引失效的原因。7.3 日常项目里踩过的高频坑最后再整理几条我在实际开发中遇到过的单表查询问题每条都是真实场景里踩过坑后总结出来的。第一个坑是分页查询忘记排序。做分页接口时如果不加 ORDER BYMySQL 返回的顺序在极端情况下可能不稳定导致第一页和第二页有重复数据。特别是当表数据发生变更后这个现象很容易被用户举报。分页 SQL 里固定带一个排序条件是成本最低的防护手段。第二个坑是字符串日期和日期类型混淆。DATE 类型的字段直接用字符串比较通常没问题比如WHERE hire_date 2022-01-01MySQL 会做隐式转换大多数时候结果正确。但如果字符串格式不规范比如写成2022/01/01就可能出现转换异常或索引失效。第三个坑是聚合查询里混入非分组列。前面提到过SELECT name, dept_id, AVG(salary) FROM employee GROUP BY dept_id这种写法在 MySQL 5.7 以后直接报错这其实是好事。但如果你的 MySQL 版本比较老或者偏移量配置关闭了 ONLY_FULL_GROUP_BY这种 SQL 能跑出来结果却是随机值。这种问题最难排查因为不是每次都报错。我的习惯是写 GROUP BY 时严格检查 SELECT 列凡是没有被聚合的列必须出现在 GROUP BY 中。说实话DQL 单表查询这部分技术点说多不多说少不少。但它就像武术里的马步姿势标准与否直接决定了后面能走多远。我在带新人的时候特别强调一个习惯把 WHERE、GROUP BY、HAVING、ORDER BY、LIMIT 这套组合拳的结构在脑子里画清楚每写一条 SQL 都能说清楚它先做什么后做什么。能做到这一步单表查询就算真正过关了。接下来再学多表 JOIN、子查询和窗口函数你会发现它们都是在今天的思路上做加法不会再有那种“每个单词都认识连起来看不懂”的无力感。
