简介本资源为国家开放大学MySQL数据库应用课程的实验训练2配套资料面向正在学习数据库基础与SQL查询的在校学生及自学者帮助系统梳理数据查询操作的核心语法与典型应用场景。压缩包内共1个PDF文档约1.75MB内容围绕汽车用品网上商城案例展开涵盖字段查询、多条件查询、DISTINCT去重、ORDER BY排序、GROUP BY分组、COUNT/SUM/AVG/MAX/MIN聚合函数以及内连接、外连接、复合条件连接、IN与EXISTS嵌套查询等实验模块。每个实验均配有题目描述与思路分析便于读者对照理解查询逻辑、掌握语句写法并完成课后练习。目前已有3334人学习下载适合作为课堂实验参考、期末复习提纲或SQL入门阶段的练习手册帮助读者在真实业务场景中巩固数据查询技能。1. 从一份国开实验包说起为什么数据查询才是 MySQL 的分水岭很多人学 MySQL 卡在安装配置装完就以为万事大吉结果一上手写业务查询就翻车。这份国家开放大学《MySQL 数据库应用》实验训练 2 的资料包恰好卡在一个很关键的位置——它不讲建库建表专攻数据查询操作从最简单的字段查询一路推到嵌套查询和集合查询覆盖了 SELECT 语句几乎全部的核心玩法。如果你正在做数据库课程设计或者刚入行需要补 SQL 基本功这份实验文档的价值在于它把 19 个实验拆成了可复现的操作单元每个实验都带分析思路不是干巴巴丢一句 SQL 让你猜。汽车用品网上商城这个业务场景贯穿始终商品表、订单表、用户表、评论表、购物车表、类别表之间的关联查询全都有对应练习。换句话说你拿到的不是零散语法点而是一套完整的查询训练路径。适合谁数据库课程设计的学生、准备面试 SQL 题的求职者、以及日常写业务查询但总觉得不够扎实的开发者。2. 单表查询与条件过滤把 WHERE 和 DISTINCT 用对地方2.1 字段查询的边界什么时候只查一张表实验 2.1 的两个例子看起来简单但背后有一个判断逻辑值得说清楚。查询商品名称为“挡风玻璃”的商品信息为什么只涉及商品表因为商品名称这个字段本身就在商品表里查询条件和返回结果都落在同一张表上不需要引入其他表。同理查询 ID 为 1 的订单订单 ID 和订单信息都在订单表里单表就能搞定。这个判断在实际工作中非常重要。很多新手一上来就 JOIN 一堆表结果性能差还容易出重复行。我一般会先问自己查询条件涉及的字段在哪张表返回结果需要的字段在哪张表如果答案是同一张就别 JOIN。-- 查询商品名称为“挡风玻璃”的商品信息 SELECT * FROM 商品表 WHERE 商品名称 挡风玻璃; -- 查询 ID 为 1 的订单 SELECT * FROM 订单表 WHERE 订单ID 1;逻辑说明第一条 SQL 的 WHERE 子句直接匹配商品名称字段返回该商品的所有列。第二条同理通过主键订单 ID 精确定位。参数方面字符串条件需要用单引号包裹数值条件直接写。注意如果商品名称字段有索引这种等值查询效率很高如果没有索引且数据量大全表扫描会慢这是后面避坑章节要展开的点。2.2 多条件查询AND 的连接顺序有讲究实验 2.2 要求查询所有促销且价格小于 1000 的商品信息。两个条件——是否促销、价格——都在商品表里所以仍然是单表查询只是 WHERE 子句里用 AND 连接两个条件。-- 查询所有促销的价格小于 1000 的商品信息 SELECT * FROM 商品表 WHERE 是否促销 是 AND 价格 1000;逻辑说明AND 要求两个条件同时为真才返回该行。参数上是否促销这个字段如果是枚举类型比如 是/否直接用字符串匹配如果是布尔类型1/0就写是否促销 1。这里有个常见坑AND 和 OR 混用时优先级问题。AND 的优先级高于 OR所以A OR B AND C会被解析成A OR (B AND C)如果你想要(A OR B) AND C必须加括号。这个坑在复合条件连接查询里还会遇到。2.3 DISTINCT 去重的真实场景实验 2.3 有两个 DISTINCT 的用例。第一个是查询所有对商品 ID 为 1 发表过评论的用户 ID。一个用户可能对同一商品发表多条评论所以用户 ID 会重复需要 DISTINCT 去重。第二个是查询会员的创建时间段按 1 年为一段同一年的会员创建记录会有多条但年份只需要出现一次。-- 查询所有对商品 ID 为 1 的商品发表过评论的用户 ID去重 SELECT DISTINCT 用户ID FROM 评论表 WHERE 商品ID 1; -- 查询会员创建时间段1 年为一段去重 SELECT DISTINCT YEAR(创建日期) AS 创建年份 FROM 用户表;逻辑说明DISTINCT 作用于 SELECT 后面所有列的组合不是只作用于第一列。如果你写SELECT DISTINCT 用户ID, 商品ID去重的是用户 ID 和商品 ID 的组合不是单独的用户 ID。参数上YEAR() 函数提取日期中的年份AS 给结果列起别名。注意DISTINCT 会对结果排序数据量大时有性能开销如果只是想去重但不需要排序可以考虑用 GROUP BY 替代这个后面会讲。3. 排序、分组与聚合让数据从“能查”到“好用”3.1 ORDER BY 的排序方向与多列排序实验 2.4 要求查询类别 ID 为 1 的所有商品结果按商品 ID 降序排列。降序用 DESC 关键字升序是 ASC默认可以省略。-- 查询类别 ID 为 1 的所有商品按商品 ID 降序排列 SELECT * FROM 商品表 WHERE 类别ID 1 ORDER BY 商品ID DESC; -- 查询今年新增的所有会员按用户名字排序 SELECT * FROM 用户表 WHERE YEAR(创建日期) YEAR(CURDATE()) ORDER BY 用户名 ASC;逻辑说明ORDER BY 放在 WHERE 之后如果有 LIMIT 则放在 LIMIT 之前。多列排序时先按第一列排第一列相同再按第二列排比如ORDER BY 价格 DESC, 商品ID ASC。参数上CURDATE() 返回当前日期YEAR() 提取年份。注意ORDER BY 的列可以是 SELECT 中没有出现的列但如果是 DISTINCT 查询ORDER BY 的列必须出现在 SELECT 列表中否则会报错。3.2 GROUP BY 与聚合函数的配合逻辑实验 2.5 到 2.10 集中练习了 GROUP BY 和聚合函数。先看 GROUP BY 的两个例子查询每个用户的消费总金额以及查询类别价格一样的各种商品数量总和。-- 查询每个用户的消费总金额 SELECT 用户ID, SUM(订单总价) AS 消费总金额 FROM 订单表 GROUP BY 用户ID; -- 查询类别价格一样的各种商品数量总和多列分组 SELECT 类别ID, 价格, SUM(数量) AS 商品数量总和 FROM 商品表 GROUP BY 类别ID, 价格;逻辑说明GROUP BY 把指定列值相同的行归为一组然后对每组执行聚合函数。第一条 SQL 按用户 ID 分组SUM 对每组的订单总价求和。第二条是多列分组类别 ID 和价格都相同才归为一组。参数上SELECT 后面只能出现 GROUP BY 的列和聚合函数如果出现其他列MySQL 在 ONLY_FULL_GROUP_BY 模式下会报错。这个模式在 MySQL 5.7 之后默认开启很多人升级后查询突然报错就是这个原因。3.3 五个聚合函数的适用场景实验 2.6 到 2.10 分别练习了 COUNT、SUM、AVG、MAX、MIN。这几个函数看起来简单但用错场景很常见。-- COUNT查询类别的数量 SELECT COUNT(*) AS 类别数量 FROM 类别表; -- COUNT GROUP BY查询每天的接单数 SELECT DATE(下单日期) AS 日期, COUNT(*) AS 接单数 FROM 订单表 GROUP BY DATE(下单日期); -- SUM查询每天的销售额 SELECT DATE(下单日期) AS 日期, SUM(订单总价) AS 销售额 FROM 订单表 GROUP BY DATE(下单日期); -- AVG查询所有订单的平均销售金额 SELECT AVG(订单总价) AS 平均销售金额 FROM 订单表; -- MAX查询所有商品中数量最大者 SELECT MAX(数量) AS 最大数量 FROM 商品表; -- MAX 用于文本列查询用户按字母排序中名字最靠前者 SELECT MAX(用户名) AS 最靠前用户名 FROM 用户表; -- MIN查询所有商品中价格最低者 SELECT MIN(价格) AS 最低价格 FROM 商品表;逻辑说明COUNT() 统计行数COUNT(列名) 统计该列非 NULL 的行数两者结果可能不同。SUM 和 AVG 只对数值列有效AVG 会自动忽略 NULL 值但 COUNT() 不会。MAX 和 MIN 可以用于文本列按字母顺序比较。参数上DATE() 函数提取日期部分GROUP BY 后面可以跟表达式而不只是列名。注意聚合函数不能嵌套比如SUM(AVG(列))是非法的需要子查询实现。4. 连接查询与嵌套查询多表关联的实战拆解4.1 内连接INNER JOIN 的驱动表选择实验 2.11 要求查询所有订单的发出者名字。订单表里有用户 ID用户表里有用户名需要通过用户 ID 连接两张表。-- 查询所有订单的发出者名字 SELECT 订单表.订单ID, 用户表.用户名 FROM 订单表 INNER JOIN 用户表 ON 订单表.用户ID 用户表.用户ID; -- 查询每个用户购物车中的商品名称 SELECT 购物车表.用户ID, 商品表.商品名称 FROM 购物车表 INNER JOIN 商品表 ON 购物车表.商品ID 商品表.商品ID;逻辑说明INNER JOIN 只返回两张表中匹配的行。ON 后面是连接条件通常是外键等于主键。参数上表名可以用别名简化比如FROM 订单表 o INNER JOIN 用户表 u ON o.用户ID u.用户ID。驱动表的选择会影响性能一般把小表或过滤后结果集小的表作为驱动表。MySQL 优化器会自动选择但你可以通过 STRAIGHT_JOIN 强制指定。4.2 外连接LEFT JOIN 和 RIGHT JOIN 的列位置陷阱实验 2.12 要求列出所有用户 ID 以及他们的评论如果有的话。这里的关键是“所有用户”即使没有评论的用户也要列出来所以用 LEFT JOIN。-- LEFT JOIN列出所有用户 ID 及他们的评论 SELECT 用户表.用户ID, 评论表.评论内容 FROM 用户表 LEFT JOIN 评论表 ON 用户表.用户ID 评论表.用户ID; -- RIGHT JOIN同样效果但列的位置要写在右边 SELECT 评论表.评论内容, 用户表.用户ID FROM 评论表 RIGHT JOIN 用户表 ON 评论表.用户ID 用户表.用户ID;逻辑说明LEFT JOIN 返回左表所有行右表没有匹配的用 NULL 填充。RIGHT JOIN 反过来。实验分析里特别提到“需将全部显示的列名写在 JOIN 语句左边/右边”这其实是在说驱动表的位置。实际工作中我几乎只用 LEFT JOIN因为 RIGHT JOIN 可以通过交换表顺序改写成 LEFT JOIN可读性更好。参数上ON 条件和 WHERE 条件的区别要注意LEFT JOIN 后如果 WHERE 里对右表列加条件可能把外连接变成内连接这是经典坑。4.3 复合条件连接与嵌套查询的 IN、EXISTS实验 2.13 到 2.18 覆盖了复合条件连接、IN、比较运算符、EXISTS、ANY、ALL。挑几个典型的说。-- 复合条件连接查询用户 ID 为 1 的客户的订单信息和客户名 SELECT 订单表.订单ID, 订单表.订单总价, 用户表.用户名 FROM 订单表 INNER JOIN 用户表 ON 订单表.用户ID 用户表.用户ID WHERE 用户表.用户ID 1; -- IN 子查询查询订购商品 ID 为 1 的订单 ID并查发出此订单的用户 ID SELECT 用户ID FROM 订单表 WHERE 订单ID IN ( SELECT 订单ID FROM 订单明细表 WHERE 商品ID 1 ); -- NOT IN查询未发出此订单的用户 ID SELECT 用户ID FROM 订单表 WHERE 订单ID NOT IN ( SELECT 订单ID FROM 订单明细表 WHERE 商品ID 1 ); -- EXISTS查询是否存在用户 ID 为 100 的用户 SELECT * FROM 用户表 WHERE EXISTS ( SELECT 1 FROM 用户表 WHERE 用户ID 100 ); -- ANY查询价格比订单表中商品 ID 对应价格大的商品 ID SELECT 商品ID FROM 商品表 WHERE 价格 ANY ( SELECT 价格 FROM 订单明细表 WHERE 商品ID 商品表.商品ID ); -- ALL查询价格比订单表中所有商品 ID 对应价格大的商品 ID SELECT 商品ID FROM 商品表 WHERE 价格 ALL ( SELECT 价格 FROM 订单明细表 WHERE 商品ID 商品表.商品ID );逻辑说明IN 子查询先执行内层把结果集作为外层 WHERE 的条件。NOT IN 要注意 NULL 值陷阱——如果子查询结果包含 NULLNOT IN 会返回空结果集因为任何值与 NULL 比较都是 UNKNOWN。EXISTS 只判断子查询是否返回行不关心返回什么所以SELECT 1就够了。ANY 是“任意一个满足”ALL 是“所有都满足”。参数上ANY 和 ALL 必须跟在比较运算符后面不能单独使用。4.4 集合查询UNION 和 UNION ALL 的去重差异实验 2.19 对比了 UNION 和 UNION ALL。查询价格小于 5 的商品以及类别 ID 为 1 和 2 的商品用 UNION 连接会自动去重用 UNION ALL 则保留所有行。-- UNION去重 SELECT 商品ID, 商品名称 FROM 商品表 WHERE 价格 5 UNION SELECT 商品ID, 商品名称 FROM 商品表 WHERE 类别ID IN (1, 2); -- UNION ALL不去重 SELECT 商品ID, 商品名称 FROM 商品表 WHERE 价格 5 UNION ALL SELECT 商品ID, 商品名称 FROM 商品表 WHERE 类别ID IN (1, 2);逻辑说明UNION 要求两个 SELECT 的列数相同、类型兼容结果列名以第一个 SELECT 为准。UNION 会排序并去重有性能开销UNION ALL 直接合并效率更高。如果确定没有重复或者不需要去重优先用 UNION ALL。参数上ORDER BY 只能放在最后一个 SELECT 后面作用于整个合并结果。5. 避坑与排查那些实验文档没写但一定会遇到的问题5.1 ONLY_FULL_GROUP_BY 报错现象SELECT 用户ID, 用户名, SUM(订单总价) FROM 订单表 GROUP BY 用户ID报错提示用户名不在 GROUP BY 中。原因MySQL 5.7 之后默认开启 ONLY_FULL_GROUP_BY要求 SELECT 中的非聚合列必须出现在 GROUP BY 中。解决要么把用户名加到 GROUP BY 里要么用 ANY_VALUE(用户名) 包裹要么关闭这个模式不推荐。我一般会检查业务逻辑确认这个列是否真的需要出现在结果中。5.2 LEFT JOIN 后 WHERE 条件把外连接变内连接现象SELECT * FROM 用户表 LEFT JOIN 订单表 ON 用户表.用户ID 订单表.用户ID WHERE 订单表.订单总价 100结果只返回有订单且总价大于 100 的用户没有订单的用户消失了。原因WHERE 在 JOIN 之后执行对右表列加条件会过滤掉 NULL 行相当于内连接。解决把条件放到 ON 后面写成LEFT JOIN 订单表 ON 用户表.用户ID 订单表.用户ID AND 订单表.订单总价 100。5.3 NOT IN 遇到 NULL 返回空结果现象SELECT * FROM 用户表 WHERE 用户ID NOT IN (SELECT 用户ID FROM 评论表)如果评论表中有用户 ID 为 NULL 的记录查询返回空。原因NOT IN 等价于! ALL任何值与 NULL 比较都是 UNKNOWN整个条件不成立。解决子查询中加WHERE 用户ID IS NOT NULL或者改用 NOT EXISTS。5.4 UNION 列数不匹配报错现象SELECT 商品ID, 商品名称 FROM 商品表 UNION SELECT 商品ID FROM 商品表报错提示列数不同。原因UNION 要求所有 SELECT 的列数一致。解决补齐列数用 NULL 或常量填充缺失列比如SELECT 商品ID, NULL FROM 商品表。5.5 聚合函数嵌套使用报错现象SELECT SUM(AVG(订单总价)) FROM 订单表报错。原因聚合函数不能直接嵌套。解决用子查询SELECT SUM(平均价) FROM (SELECT AVG(订单总价) AS 平均价 FROM 订单表 GROUP BY 用户ID) AS 临时表。6. 进阶技巧用 EXPLAIN 验证查询是否走索引实验文档里的查询语句在数据量小的时候都能跑但数据量上去之后性能问题就暴露了。我一般会养成一个习惯写完一条稍微复杂的查询先用 EXPLAIN 看一眼执行计划。-- 查看查询执行计划 EXPLAIN SELECT 用户ID, SUM(订单总价) AS 消费总金额 FROM 订单表 WHERE 下单日期 2024-01-01 GROUP BY 用户ID ORDER BY 消费总金额 DESC;逻辑说明EXPLAIN 返回的字段里重点看 type、key、rows、Extra。type 是访问类型从好到差依次是 system、const、eq_ref、ref、range、index、ALL。ALL 表示全表扫描数据量大时需要考虑加索引。key 显示实际使用的索引如果为 NULL 说明没走索引。rows 是预估扫描行数越小越好。Extra 里出现 Using filesort 表示需要额外排序Using temporary 表示用了临时表这两个都可能是性能瓶颈。参数上EXPLAIN 可以加 FORMATJSON 看更详细的成本信息。MySQL 8.0 还支持 EXPLAIN ANALYZE会实际执行查询并返回真实耗时比 EXPLAIN 的预估更准。我踩过的一个血泪坑实验里查询“今年新增的会员”用了YEAR(创建日期) YEAR(CURDATE())这个写法对创建日期列做了函数运算导致索引失效。改成创建日期 2024-01-01 AND 创建日期 2025-01-01就能走索引。从那以后我每次写日期条件都强制走一遍 EXPLAIN确认 key 列不是 NULL 才放心。还有一个玄学问题同样的 SQL在实验环境跑得飞快放到生产环境就慢。后来发现是数据分布不同实验数据只有几百行生产环境几百万行优化器选择的执行计划完全不一样。所以验证查询性能一定要用接近真实的数据量。希望帮到你。本文还有配套的精品资源点击获取
MySQL数据查询实战：从单表到嵌套查询的完整训练路径
简介本资源为国家开放大学MySQL数据库应用课程的实验训练2配套资料面向正在学习数据库基础与SQL查询的在校学生及自学者帮助系统梳理数据查询操作的核心语法与典型应用场景。压缩包内共1个PDF文档约1.75MB内容围绕汽车用品网上商城案例展开涵盖字段查询、多条件查询、DISTINCT去重、ORDER BY排序、GROUP BY分组、COUNT/SUM/AVG/MAX/MIN聚合函数以及内连接、外连接、复合条件连接、IN与EXISTS嵌套查询等实验模块。每个实验均配有题目描述与思路分析便于读者对照理解查询逻辑、掌握语句写法并完成课后练习。目前已有3334人学习下载适合作为课堂实验参考、期末复习提纲或SQL入门阶段的练习手册帮助读者在真实业务场景中巩固数据查询技能。1. 从一份国开实验包说起为什么数据查询才是 MySQL 的分水岭很多人学 MySQL 卡在安装配置装完就以为万事大吉结果一上手写业务查询就翻车。这份国家开放大学《MySQL 数据库应用》实验训练 2 的资料包恰好卡在一个很关键的位置——它不讲建库建表专攻数据查询操作从最简单的字段查询一路推到嵌套查询和集合查询覆盖了 SELECT 语句几乎全部的核心玩法。如果你正在做数据库课程设计或者刚入行需要补 SQL 基本功这份实验文档的价值在于它把 19 个实验拆成了可复现的操作单元每个实验都带分析思路不是干巴巴丢一句 SQL 让你猜。汽车用品网上商城这个业务场景贯穿始终商品表、订单表、用户表、评论表、购物车表、类别表之间的关联查询全都有对应练习。换句话说你拿到的不是零散语法点而是一套完整的查询训练路径。适合谁数据库课程设计的学生、准备面试 SQL 题的求职者、以及日常写业务查询但总觉得不够扎实的开发者。2. 单表查询与条件过滤把 WHERE 和 DISTINCT 用对地方2.1 字段查询的边界什么时候只查一张表实验 2.1 的两个例子看起来简单但背后有一个判断逻辑值得说清楚。查询商品名称为“挡风玻璃”的商品信息为什么只涉及商品表因为商品名称这个字段本身就在商品表里查询条件和返回结果都落在同一张表上不需要引入其他表。同理查询 ID 为 1 的订单订单 ID 和订单信息都在订单表里单表就能搞定。这个判断在实际工作中非常重要。很多新手一上来就 JOIN 一堆表结果性能差还容易出重复行。我一般会先问自己查询条件涉及的字段在哪张表返回结果需要的字段在哪张表如果答案是同一张就别 JOIN。-- 查询商品名称为“挡风玻璃”的商品信息 SELECT * FROM 商品表 WHERE 商品名称 挡风玻璃; -- 查询 ID 为 1 的订单 SELECT * FROM 订单表 WHERE 订单ID 1;逻辑说明第一条 SQL 的 WHERE 子句直接匹配商品名称字段返回该商品的所有列。第二条同理通过主键订单 ID 精确定位。参数方面字符串条件需要用单引号包裹数值条件直接写。注意如果商品名称字段有索引这种等值查询效率很高如果没有索引且数据量大全表扫描会慢这是后面避坑章节要展开的点。2.2 多条件查询AND 的连接顺序有讲究实验 2.2 要求查询所有促销且价格小于 1000 的商品信息。两个条件——是否促销、价格——都在商品表里所以仍然是单表查询只是 WHERE 子句里用 AND 连接两个条件。-- 查询所有促销的价格小于 1000 的商品信息 SELECT * FROM 商品表 WHERE 是否促销 是 AND 价格 1000;逻辑说明AND 要求两个条件同时为真才返回该行。参数上是否促销这个字段如果是枚举类型比如 是/否直接用字符串匹配如果是布尔类型1/0就写是否促销 1。这里有个常见坑AND 和 OR 混用时优先级问题。AND 的优先级高于 OR所以A OR B AND C会被解析成A OR (B AND C)如果你想要(A OR B) AND C必须加括号。这个坑在复合条件连接查询里还会遇到。2.3 DISTINCT 去重的真实场景实验 2.3 有两个 DISTINCT 的用例。第一个是查询所有对商品 ID 为 1 发表过评论的用户 ID。一个用户可能对同一商品发表多条评论所以用户 ID 会重复需要 DISTINCT 去重。第二个是查询会员的创建时间段按 1 年为一段同一年的会员创建记录会有多条但年份只需要出现一次。-- 查询所有对商品 ID 为 1 的商品发表过评论的用户 ID去重 SELECT DISTINCT 用户ID FROM 评论表 WHERE 商品ID 1; -- 查询会员创建时间段1 年为一段去重 SELECT DISTINCT YEAR(创建日期) AS 创建年份 FROM 用户表;逻辑说明DISTINCT 作用于 SELECT 后面所有列的组合不是只作用于第一列。如果你写SELECT DISTINCT 用户ID, 商品ID去重的是用户 ID 和商品 ID 的组合不是单独的用户 ID。参数上YEAR() 函数提取日期中的年份AS 给结果列起别名。注意DISTINCT 会对结果排序数据量大时有性能开销如果只是想去重但不需要排序可以考虑用 GROUP BY 替代这个后面会讲。3. 排序、分组与聚合让数据从“能查”到“好用”3.1 ORDER BY 的排序方向与多列排序实验 2.4 要求查询类别 ID 为 1 的所有商品结果按商品 ID 降序排列。降序用 DESC 关键字升序是 ASC默认可以省略。-- 查询类别 ID 为 1 的所有商品按商品 ID 降序排列 SELECT * FROM 商品表 WHERE 类别ID 1 ORDER BY 商品ID DESC; -- 查询今年新增的所有会员按用户名字排序 SELECT * FROM 用户表 WHERE YEAR(创建日期) YEAR(CURDATE()) ORDER BY 用户名 ASC;逻辑说明ORDER BY 放在 WHERE 之后如果有 LIMIT 则放在 LIMIT 之前。多列排序时先按第一列排第一列相同再按第二列排比如ORDER BY 价格 DESC, 商品ID ASC。参数上CURDATE() 返回当前日期YEAR() 提取年份。注意ORDER BY 的列可以是 SELECT 中没有出现的列但如果是 DISTINCT 查询ORDER BY 的列必须出现在 SELECT 列表中否则会报错。3.2 GROUP BY 与聚合函数的配合逻辑实验 2.5 到 2.10 集中练习了 GROUP BY 和聚合函数。先看 GROUP BY 的两个例子查询每个用户的消费总金额以及查询类别价格一样的各种商品数量总和。-- 查询每个用户的消费总金额 SELECT 用户ID, SUM(订单总价) AS 消费总金额 FROM 订单表 GROUP BY 用户ID; -- 查询类别价格一样的各种商品数量总和多列分组 SELECT 类别ID, 价格, SUM(数量) AS 商品数量总和 FROM 商品表 GROUP BY 类别ID, 价格;逻辑说明GROUP BY 把指定列值相同的行归为一组然后对每组执行聚合函数。第一条 SQL 按用户 ID 分组SUM 对每组的订单总价求和。第二条是多列分组类别 ID 和价格都相同才归为一组。参数上SELECT 后面只能出现 GROUP BY 的列和聚合函数如果出现其他列MySQL 在 ONLY_FULL_GROUP_BY 模式下会报错。这个模式在 MySQL 5.7 之后默认开启很多人升级后查询突然报错就是这个原因。3.3 五个聚合函数的适用场景实验 2.6 到 2.10 分别练习了 COUNT、SUM、AVG、MAX、MIN。这几个函数看起来简单但用错场景很常见。-- COUNT查询类别的数量 SELECT COUNT(*) AS 类别数量 FROM 类别表; -- COUNT GROUP BY查询每天的接单数 SELECT DATE(下单日期) AS 日期, COUNT(*) AS 接单数 FROM 订单表 GROUP BY DATE(下单日期); -- SUM查询每天的销售额 SELECT DATE(下单日期) AS 日期, SUM(订单总价) AS 销售额 FROM 订单表 GROUP BY DATE(下单日期); -- AVG查询所有订单的平均销售金额 SELECT AVG(订单总价) AS 平均销售金额 FROM 订单表; -- MAX查询所有商品中数量最大者 SELECT MAX(数量) AS 最大数量 FROM 商品表; -- MAX 用于文本列查询用户按字母排序中名字最靠前者 SELECT MAX(用户名) AS 最靠前用户名 FROM 用户表; -- MIN查询所有商品中价格最低者 SELECT MIN(价格) AS 最低价格 FROM 商品表;逻辑说明COUNT() 统计行数COUNT(列名) 统计该列非 NULL 的行数两者结果可能不同。SUM 和 AVG 只对数值列有效AVG 会自动忽略 NULL 值但 COUNT() 不会。MAX 和 MIN 可以用于文本列按字母顺序比较。参数上DATE() 函数提取日期部分GROUP BY 后面可以跟表达式而不只是列名。注意聚合函数不能嵌套比如SUM(AVG(列))是非法的需要子查询实现。4. 连接查询与嵌套查询多表关联的实战拆解4.1 内连接INNER JOIN 的驱动表选择实验 2.11 要求查询所有订单的发出者名字。订单表里有用户 ID用户表里有用户名需要通过用户 ID 连接两张表。-- 查询所有订单的发出者名字 SELECT 订单表.订单ID, 用户表.用户名 FROM 订单表 INNER JOIN 用户表 ON 订单表.用户ID 用户表.用户ID; -- 查询每个用户购物车中的商品名称 SELECT 购物车表.用户ID, 商品表.商品名称 FROM 购物车表 INNER JOIN 商品表 ON 购物车表.商品ID 商品表.商品ID;逻辑说明INNER JOIN 只返回两张表中匹配的行。ON 后面是连接条件通常是外键等于主键。参数上表名可以用别名简化比如FROM 订单表 o INNER JOIN 用户表 u ON o.用户ID u.用户ID。驱动表的选择会影响性能一般把小表或过滤后结果集小的表作为驱动表。MySQL 优化器会自动选择但你可以通过 STRAIGHT_JOIN 强制指定。4.2 外连接LEFT JOIN 和 RIGHT JOIN 的列位置陷阱实验 2.12 要求列出所有用户 ID 以及他们的评论如果有的话。这里的关键是“所有用户”即使没有评论的用户也要列出来所以用 LEFT JOIN。-- LEFT JOIN列出所有用户 ID 及他们的评论 SELECT 用户表.用户ID, 评论表.评论内容 FROM 用户表 LEFT JOIN 评论表 ON 用户表.用户ID 评论表.用户ID; -- RIGHT JOIN同样效果但列的位置要写在右边 SELECT 评论表.评论内容, 用户表.用户ID FROM 评论表 RIGHT JOIN 用户表 ON 评论表.用户ID 用户表.用户ID;逻辑说明LEFT JOIN 返回左表所有行右表没有匹配的用 NULL 填充。RIGHT JOIN 反过来。实验分析里特别提到“需将全部显示的列名写在 JOIN 语句左边/右边”这其实是在说驱动表的位置。实际工作中我几乎只用 LEFT JOIN因为 RIGHT JOIN 可以通过交换表顺序改写成 LEFT JOIN可读性更好。参数上ON 条件和 WHERE 条件的区别要注意LEFT JOIN 后如果 WHERE 里对右表列加条件可能把外连接变成内连接这是经典坑。4.3 复合条件连接与嵌套查询的 IN、EXISTS实验 2.13 到 2.18 覆盖了复合条件连接、IN、比较运算符、EXISTS、ANY、ALL。挑几个典型的说。-- 复合条件连接查询用户 ID 为 1 的客户的订单信息和客户名 SELECT 订单表.订单ID, 订单表.订单总价, 用户表.用户名 FROM 订单表 INNER JOIN 用户表 ON 订单表.用户ID 用户表.用户ID WHERE 用户表.用户ID 1; -- IN 子查询查询订购商品 ID 为 1 的订单 ID并查发出此订单的用户 ID SELECT 用户ID FROM 订单表 WHERE 订单ID IN ( SELECT 订单ID FROM 订单明细表 WHERE 商品ID 1 ); -- NOT IN查询未发出此订单的用户 ID SELECT 用户ID FROM 订单表 WHERE 订单ID NOT IN ( SELECT 订单ID FROM 订单明细表 WHERE 商品ID 1 ); -- EXISTS查询是否存在用户 ID 为 100 的用户 SELECT * FROM 用户表 WHERE EXISTS ( SELECT 1 FROM 用户表 WHERE 用户ID 100 ); -- ANY查询价格比订单表中商品 ID 对应价格大的商品 ID SELECT 商品ID FROM 商品表 WHERE 价格 ANY ( SELECT 价格 FROM 订单明细表 WHERE 商品ID 商品表.商品ID ); -- ALL查询价格比订单表中所有商品 ID 对应价格大的商品 ID SELECT 商品ID FROM 商品表 WHERE 价格 ALL ( SELECT 价格 FROM 订单明细表 WHERE 商品ID 商品表.商品ID );逻辑说明IN 子查询先执行内层把结果集作为外层 WHERE 的条件。NOT IN 要注意 NULL 值陷阱——如果子查询结果包含 NULLNOT IN 会返回空结果集因为任何值与 NULL 比较都是 UNKNOWN。EXISTS 只判断子查询是否返回行不关心返回什么所以SELECT 1就够了。ANY 是“任意一个满足”ALL 是“所有都满足”。参数上ANY 和 ALL 必须跟在比较运算符后面不能单独使用。4.4 集合查询UNION 和 UNION ALL 的去重差异实验 2.19 对比了 UNION 和 UNION ALL。查询价格小于 5 的商品以及类别 ID 为 1 和 2 的商品用 UNION 连接会自动去重用 UNION ALL 则保留所有行。-- UNION去重 SELECT 商品ID, 商品名称 FROM 商品表 WHERE 价格 5 UNION SELECT 商品ID, 商品名称 FROM 商品表 WHERE 类别ID IN (1, 2); -- UNION ALL不去重 SELECT 商品ID, 商品名称 FROM 商品表 WHERE 价格 5 UNION ALL SELECT 商品ID, 商品名称 FROM 商品表 WHERE 类别ID IN (1, 2);逻辑说明UNION 要求两个 SELECT 的列数相同、类型兼容结果列名以第一个 SELECT 为准。UNION 会排序并去重有性能开销UNION ALL 直接合并效率更高。如果确定没有重复或者不需要去重优先用 UNION ALL。参数上ORDER BY 只能放在最后一个 SELECT 后面作用于整个合并结果。5. 避坑与排查那些实验文档没写但一定会遇到的问题5.1 ONLY_FULL_GROUP_BY 报错现象SELECT 用户ID, 用户名, SUM(订单总价) FROM 订单表 GROUP BY 用户ID报错提示用户名不在 GROUP BY 中。原因MySQL 5.7 之后默认开启 ONLY_FULL_GROUP_BY要求 SELECT 中的非聚合列必须出现在 GROUP BY 中。解决要么把用户名加到 GROUP BY 里要么用 ANY_VALUE(用户名) 包裹要么关闭这个模式不推荐。我一般会检查业务逻辑确认这个列是否真的需要出现在结果中。5.2 LEFT JOIN 后 WHERE 条件把外连接变内连接现象SELECT * FROM 用户表 LEFT JOIN 订单表 ON 用户表.用户ID 订单表.用户ID WHERE 订单表.订单总价 100结果只返回有订单且总价大于 100 的用户没有订单的用户消失了。原因WHERE 在 JOIN 之后执行对右表列加条件会过滤掉 NULL 行相当于内连接。解决把条件放到 ON 后面写成LEFT JOIN 订单表 ON 用户表.用户ID 订单表.用户ID AND 订单表.订单总价 100。5.3 NOT IN 遇到 NULL 返回空结果现象SELECT * FROM 用户表 WHERE 用户ID NOT IN (SELECT 用户ID FROM 评论表)如果评论表中有用户 ID 为 NULL 的记录查询返回空。原因NOT IN 等价于! ALL任何值与 NULL 比较都是 UNKNOWN整个条件不成立。解决子查询中加WHERE 用户ID IS NOT NULL或者改用 NOT EXISTS。5.4 UNION 列数不匹配报错现象SELECT 商品ID, 商品名称 FROM 商品表 UNION SELECT 商品ID FROM 商品表报错提示列数不同。原因UNION 要求所有 SELECT 的列数一致。解决补齐列数用 NULL 或常量填充缺失列比如SELECT 商品ID, NULL FROM 商品表。5.5 聚合函数嵌套使用报错现象SELECT SUM(AVG(订单总价)) FROM 订单表报错。原因聚合函数不能直接嵌套。解决用子查询SELECT SUM(平均价) FROM (SELECT AVG(订单总价) AS 平均价 FROM 订单表 GROUP BY 用户ID) AS 临时表。6. 进阶技巧用 EXPLAIN 验证查询是否走索引实验文档里的查询语句在数据量小的时候都能跑但数据量上去之后性能问题就暴露了。我一般会养成一个习惯写完一条稍微复杂的查询先用 EXPLAIN 看一眼执行计划。-- 查看查询执行计划 EXPLAIN SELECT 用户ID, SUM(订单总价) AS 消费总金额 FROM 订单表 WHERE 下单日期 2024-01-01 GROUP BY 用户ID ORDER BY 消费总金额 DESC;逻辑说明EXPLAIN 返回的字段里重点看 type、key、rows、Extra。type 是访问类型从好到差依次是 system、const、eq_ref、ref、range、index、ALL。ALL 表示全表扫描数据量大时需要考虑加索引。key 显示实际使用的索引如果为 NULL 说明没走索引。rows 是预估扫描行数越小越好。Extra 里出现 Using filesort 表示需要额外排序Using temporary 表示用了临时表这两个都可能是性能瓶颈。参数上EXPLAIN 可以加 FORMATJSON 看更详细的成本信息。MySQL 8.0 还支持 EXPLAIN ANALYZE会实际执行查询并返回真实耗时比 EXPLAIN 的预估更准。我踩过的一个血泪坑实验里查询“今年新增的会员”用了YEAR(创建日期) YEAR(CURDATE())这个写法对创建日期列做了函数运算导致索引失效。改成创建日期 2024-01-01 AND 创建日期 2025-01-01就能走索引。从那以后我每次写日期条件都强制走一遍 EXPLAIN确认 key 列不是 NULL 才放心。还有一个玄学问题同样的 SQL在实验环境跑得飞快放到生产环境就慢。后来发现是数据分布不同实验数据只有几百行生产环境几百万行优化器选择的执行计划完全不一样。所以验证查询性能一定要用接近真实的数据量。希望帮到你。本文还有配套的精品资源点击获取
