MySQL SELECT查询训练：19道题覆盖单表、分组、连接与子查询

简介：面向国家开放大学 MySQL 数据库应用课程学习者与数据库初学者，这份实验训练2资料聚焦数据查询操作，围绕单表字段查询、多条件筛选、DISTINCT 去重、ORDER BY 排序、GROUP BY 分组、COUNT/SUM/AVG/MAX/MIN 聚合函数，以及内连接、外连接、复合条件连接和 IN、比较运算符、EXISTS 嵌套查询等实验任务展开，适合课堂实验对照、课后复习与查询语法查漏补缺。资源包共 1 个 PDF 文件，大小约 1.75MB，以文档形式集中呈现实验要求、案例分析与 SQL 查询思路，便于按实验编号检索和打印批注。已有 3338 人学习，说明其内容在课程配套练习中具有一定参考价值。读者可借助各实验的分析过程理解查询条件拆解、表间关联与结果去重排序方法，并据此完成上机操作和知识点复盘。

1. 19 道 SELECT 题摊开看：这套查询训练真正练的是什么

数据库课设里最不值钱、也最容易翻车的材料，就是这种按实验编号排下来的查询训练：19 道题，从SELECT * FROM goods WHERE name='挡风玻璃'一路排到ANY、ALL、UNION，看着每道题都懂，真上手写，结果集第一行就不对。这份 MySQL 数据库应用实验训练2 的价值不在题目本身，而在于它把数据查询操作从单表条件、去重排序、聚合分组、连接、嵌套到集合运算完整串了一遍，正好是一条可以照着自查的能力清单。

我把它当成一个最小可验证的 database 练习集来用：六张表、一批带脏数据的样本、一条能自己判对错的验证思路。适合三类人——刚学完 SELECT 语法要动手的学生、面试前想快速捡回手感的开发者、带课需要一套可复现数据的老师。下面从建库开始写，一路写到每条 SQL 的边界和常见翻车点。

2. 先把库建起来：六张表的关系、字段与造数脚本

2.1 六张表撑起一个商城：字段与外键关系

训练文档只给查询需求，不给建表语句，这是最容易被跳过的一步。一辆车用品商城的查询题要想跑通，至少需要六张表：类别表、商品表、用户表、订单表、订单明细表、评论表，购物车表算第七张。关系是一层层挂上去的：类别对商品是一对多，用户对订单是一对多，订单对明细是一对多，明细再指回商品，评论同时挂用户和商品。实验 2.11 那道「查询所有订单的发出者名字」之所以要连接，就是因为订单表里存的是user_id，姓名在用户表里。

字段设计上有几个地方直接决定后面题能不能写。第一，商品表要有is_promotion标记，实验 2.2 的「促销且价格小于 1000」才有落脚点，光靠价格字段是判不出促销的。第二，订单表要有order_total，实验 2.5、2.7、2.8 全在算这个字段；如果金额只存在明细表里，就得先聚合再连接，难度会上一个台阶。第三，用户表的create_time用DATETIME而不是DATE，因为实验 2.3 要按年去重、实验 2.4 要查「今年新增」，YEAR()函数对两种类型都成立，但DATETIME更接近真实业务。

表名	主要字段	在实验里的角色
category	id, name	2.6 统计类别数量
goods	id, name, category_id, price, stock, is_promotion	2.1、2.2、2.4、2.5、2.9、2.10
users	id, username, create_time	2.3、2.4、2.9、2.12
orders	id, user_id, order_total, order_date	2.1、2.5、2.6、2.7、2.8、2.11
order_detail	id, order_id, goods_id, num, price	2.14、2.15、2.17、2.18
comments	id, user_id, goods_id, content	2.3、2.12
cart	id, user_id, goods_id, num	2.11、2.13

提示：表名和字段名统一用英文小写加下划线，别用中文表名。倒不是为了好看，而是后面SHOW COLUMNS、EXPLAIN的输出里中文列名对不齐，排查时容易看错行。

2.2 建表脚本：字符集、主键与索引一次到位

建表这一步我一般直接写成一个可以反复执行的脚本，开头先DROP DATABASE IF EXISTS，避免上一次跑到一半留下的半张表干扰。字符集用utf8mb4，排序规则跟着走，因为商品名里有中文，ORDER BY username这类操作在实验 2.9 里还要依赖排序规则判断字母顺序。

-- 建库：字符集必须显式指定，否则继承服务器默认值 DROP DATABASE IF EXISTS auto_mall; CREATE DATABASE auto_mall DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_0900_ai_ci; USE auto_mall; -- 类别表 CREATE TABLE category ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL ) ENGINE=InnoDB; -- 商品表：is_promotion 是实验 2.2 的判定依据 CREATE TABLE goods ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, category_id INT NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL DEFAULT 0, is_promotion TINYINT NOT NULL DEFAULT 0, KEY idx_cate (category_id), KEY idx_price (price) ) ENGINE=InnoDB; -- 用户表：create_time 用 DATETIME，方便按年去重和筛今年 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, create_time DATETIME NOT NULL, KEY idx_create (create_time) ) ENGINE=InnoDB; -- 订单表：order_total 是后面 SUM/AVG 的主战场 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_total DECIMAL(10,2) NOT NULL, order_date DATE NOT NULL, KEY idx_user (user_id), KEY idx_date (order_date) ) ENGINE=InnoDB; -- 订单明细：num 用于实验 2.15 的数量总和统计 CREATE TABLE order_detail ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, goods_id INT NOT NULL, num INT NOT NULL, price DECIMAL(10,2) NOT NULL, KEY idx_order (order_id), KEY idx_goods (goods_id) ) ENGINE=InnoDB; -- 评论表：故意允许同一用户对同一商品多次评论，用来验证 DISTINCT CREATE TABLE comments ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, goods_id INT NOT NULL, content VARCHAR(255), KEY idx_goods (goods_id), KEY idx_user (user_id) ) ENGINE=InnoDB; -- 购物车：user_id + goods_id 建唯一键，避免同一商品重复入车 CREATE TABLE cart ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, goods_id INT NOT NULL, num INT NOT NULL, UNIQUE KEY uk_user_goods (user_id, goods_id) ) ENGINE=InnoDB;

这份脚本里有两处不是随手加的。goods.price和orders.order_total都用DECIMAL(10,2)而不是FLOAT，因为实验 2.9 要查「价格最低者」、2.8 要算平均金额，浮点类型在等值比较和求和时会出现0.30000000000000004这类结果，一旦和预期值对不上，你会怀疑 SQL 写错了，其实是字段类型的问题。另一处是cart上的唯一键，它顺手把「同一用户重复加同一商品」这种脏数据挡在入库阶段，省掉后面DISTINCT到底该不该加的争论。

注意：如果你的 MySQL 是 5.7 以前的老版本，utf8mb4_0900_ai_ci这个排序规则不存在，改成utf8mb4_general_ci即可；MySQL 8.0 默认就是前者。

2.3 造数：每张表要埋多少「坑」才够验证

数据不是随便插几行能跑就行，得让每道实验题都能区分出「写对了」和「写了个能跑的」。我造数时遵循三条：其一，评论表里让同一个用户对同一商品评论两次，实验 2.3 的DISTINCT才有意义，否则去掉DISTINCT结果也一样，你根本不知道它有没有生效；其二，商品价格里放一个低于 5 元的（气门嘴帽 3.50），实验 2.19 的price < 5才不会查出空集；其三，用户表里放一个 ID 为 100 的账号，实验 2.16 的EXISTS判断才有正例。

USE auto_mall; INSERT INTO category (id, name) VALUES (1, '外观件'), (2, '内饰件'), (3, '电子设备'), (4, '养护用品'); -- 9 号商品价格 3.50 且属于类别 2，用来制造 UNION 的重复行 INSERT INTO goods (id, name, category_id, price, stock, is_promotion) VALUES (1, '挡风玻璃', 1, 880.00, 30, 1), (2, '前保险杠', 1, 1200.00, 12, 0), (3, '后视镜', 1, 260.00, 80, 1), (4, '座套', 2, 199.00, 200, 1), (5, '脚垫', 2, 129.00, 150, 0), (6, '行车记录仪', 3, 499.00, 60, 1), (7, '车载充电器', 3, 39.90, 500, 0), (8, '玻璃水', 4, 4.90, 1000, 0), (9, '气门嘴帽', 2, 3.50, 500, 0); -- 5 号和 100 号用相对当前时间生成，「今年新增」的判断才稳定 INSERT INTO users (id, username, create_time) VALUES (1, 'zhang_san', '2023-03-11 10:00:00'), (2, 'li_si', '2023-07-02 09:30:00'), (3, 'wang_wu', '2024-01-15 14:20:00'), (4, 'zhao_liu', '2024-05-20 16:00:00'), (5, 'qian_qi', DATE_SUB(NOW(), INTERVAL 10 DAY)), (100, 'test_user', DATE_SUB(NOW(), INTERVAL 3 DAY)); INSERT INTO orders (id, user_id, order_total, order_date) VALUES (1, 1, 880.00, '2025-03-01'), (2, 1, 159.60, '2025-03-01'), (3, 2, 499.00, '2025-03-02'), (4, 3, 129.00, '2025-03-02'), (5, 4, 39.90, '2025-03-03'), (6, 2, 9.80, '2025-03-03'); INSERT INTO order_detail (order_id, goods_id, num, price) VALUES (1, 1, 1, 880.00), (2, 7, 4, 39.90), (3, 6, 1, 499.00), (4, 5, 1, 129.00), (5, 7, 1, 39.90), (6, 8, 2, 4.90); -- 用户 1 对商品 1 评论了两次，这是 DISTINCT 的判定点 INSERT INTO comments (user_id, goods_id, content) VALUES (1, 1, '安装贴合度不错'), (1, 1, '第二次回购'), (2, 1, '边缘有点毛刺'), (3, 6, '夜视清楚'), (1, 6, '走线简单'); INSERT INTO cart (user_id, goods_id, num) VALUES (1, 1, 1), (1, 6, 2), (2, 4, 1), (3, 7, 3), (4, 8, 10);

数据里的日期我写成了固定字符串，只有用户 5 和 100 用DATE_SUB(NOW(), ...)生成，因为实验 2.4 和 2.15 都涉及「今年新增的会员」这个条件做成动态的。如果你的机器时间正好在 1 月初，INTERVAL 3 DAY可能跨到去年，跑之前先SELECT CURDATE();确认一下日期，不行就把间隔调小。

提示：造数脚本执行完跑一句SELECT COUNT(*) FROM goods;确认行数是 9。如果行数不对，多半是主键冲突导致的INSERT中途失败，看SHOW WARNINGS;的输出。

3. 单表到分组：WHERE、DISTINCT、ORDER BY 与五个聚合函数的落点

3.1 WHERE 多条件：括号优先级和「条件写在哪个表」

实验 2.1 和 2.2 看着最简单，但它是后面所有查询的地基。单表查询的关键动作只有一个：先确认被查询的信息在不在同一张表里。查「挡风玻璃」的商品信息，商品名和商品详情都在goods，一个表就够；查 ID 为 1 的订单，订单信息都在orders，也不用连接。这一步判断错了，后面全白搭。

-- 2.1 单表字段查询：条件列和结果列在同一张表 SELECT * FROM goods WHERE name = '挡风玻璃'; SELECT * FROM orders WHERE id = 1; -- 2.2 多条件查询：促销 且 价格低于 1000 SELECT id, name, price, is_promotion FROM goods WHERE is_promotion = 1 AND price < 1000 ORDER BY price;

AND的优先级高于OR，这是多条件查询里第一个血泪经验。实验题只给了两个条件所以不会出事，但真实场景里「促销品中的低价」和「非促销品中的高价」这种混合条件，不写括号就会翻车：

-- 错误写法：OR 后面的条件会把前面的过滤全部放开 SELECT id, name, price, is_promotion FROM goods WHERE is_promotion = 1 AND price < 100 OR is_promotion = 0 AND price > 500; -- 正确写法：每一组条件都用括号包住 SELECT id, name, price, is_promotion FROM goods WHERE (is_promotion = 1 AND price < 100) OR (is_promotion = 0 AND price > 500);

上面这两条在只有 9 行数据的表上跑，结果可能刚好一样，所以很多人写完就过了。等到数据量上去、条件变成三四个，OR会把前面AND的过滤效果逐层吃掉，你看到的是一个明显偏大的结果集，却不知道从哪一层开始错的。我的习惯是只要语句里出现OR，就先把它拆成两条SELECT分别验证行数，再用UNION ALL合起来对总数，最后才写成一条带括号的语句。

3.2 DISTINCT 的作用域：它去重的是整行，不是某一列

实验 2.3 的两道题是去重的典型场景。第一道查对商品 1 发表过评论的用户 ID，评论表里用户 1 出现了两次，所以结果需要去重；第二道查会员的创建年份，多个用户同一年注册，按年去重后每年只留一行。

-- 2.3-1 对商品 1 发表过评论的用户，去重 SELECT DISTINCT user_id FROM comments WHERE goods_id = 1; -- 2.3-2 会员创建年份，按年去重（等价于按 1 年一个时间段） SELECT DISTINCT YEAR(create_time) AS join_year FROM users ORDER BY join_year;

这里最容易误解的是DISTINCT的作用范围：它作用在整条SELECT列表上，不是紧跟着的那一列。写成SELECT DISTINCT user_id, goods_id FROM comments时，去重的是(user_id, goods_id)这个组合，只要两列不完全相同就不会被合并。实验里因为只查一列所以看不出差别，一旦加上第二列，很多人会发现「去重没生效」，其实是一直在按组合去重。

第二种误解是把DISTINCT当成GROUP BY的替代品。两者在只取一列时结果一样，但执行路径不同：DISTINCT是拿结果集做去重，GROUP BY是先分组再取值，可以用聚合函数。当你需要「每个用户评论了几次」这种带统计的需求，DISTINCT就无能为力了，必须换成GROUP BY user_id加COUNT(*)。判断标准很简单：只要结果里出现「每个……的……数量/总额/平均」，就别想DISTINCT，直接上分组。

3.3 ORDER BY 的落点：排序发生在筛选之后、截断之前

实验 2.4 的两道排序题，一道按商品 ID 降序，一道按用户名字升序。语法没什么可讲的，真正需要记住的是执行顺序：FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT。排序发生在筛选之后，所以WHERE里不能引用SELECT中起的别名，但ORDER BY可以。

-- 2.4-1 类别 1 的商品，按 id 降序 SELECT id, name, price FROM goods WHERE category_id = 1 ORDER BY id DESC; -- 2.4-2 今年新增会员，按用户名排序 SELECT id, username, create_time FROM users WHERE YEAR(create_time) = YEAR(CURDATE()) ORDER BY username;

第二条里的YEAR(create_time) = YEAR(CURDATE())有个副作用：create_time上的索引会失效，因为函数作用在了列上。9 行数据无所谓，几十万行的表上这就是一次全表扫描。常见的改法是写成范围条件，让索引能用上：

-- 等价但能用上 idx_create 索引的写法 SELECT id, username, create_time FROM users WHERE create_time >= DATE_FORMAT(CURDATE(), '%Y-01-01') AND create_time < DATE_FORMAT(CURDATE() + INTERVAL 1 YEAR, '%Y-01-01') ORDER BY username;

还有一点：ORDER BY如果排序列有大量重复值，结果顺序是不稳定的，两次执行可能不一样。实验题里按id排序不存在这个问题（主键唯一），但按username排、按price排就未必。要结果稳定，可以在排序键后面补一个唯一列，比如ORDER BY price, id。

3.4 GROUP BY 与五个聚合函数：多列分组和过滤顺序

实验 2.5 到 2.10 是聚合函数的主场。核心逻辑只有一句：先把行按某个维度归堆，再对每堆算一个值。GROUP BY user_id就是按用户归堆，SUM(order_total)就是算每堆的总金额。

-- 2.5-1 每个用户的消费总金额 SELECT user_id, SUM(order_total) AS total_amount FROM orders GROUP BY user_id ORDER BY total_amount DESC; -- 2.5-2 多列分组：同类别且同价格的商品有多少种 SELECT category_id, price, COUNT(*) AS goods_cnt FROM goods GROUP BY category_id, price ORDER BY category_id, price;

GROUP BY category_id, price这种多列分组，理解方式是把(category_id, price)当成一个整体键，只有两列都相同才归为同一堆。实验文档说它「比上一个例子更复杂」，复杂在结果的行数变多——单列分组最多 4 行（4 个类别），双列分组会展开成 9 行，看结果的时候容易误以为写错了。

五个聚合函数各有各的边界：

-- 2.6-1 类别数量 SELECT COUNT(*) AS cate_cnt FROM category; -- 2.6-2 每天的接单数 SELECT order_date, COUNT(*) AS order_cnt FROM orders GROUP BY order_date ORDER BY order_date; -- 2.7 每天的销售额 SELECT order_date, SUM(order_total) AS amount FROM orders GROUP BY order_date ORDER BY order_date; -- 2.8 所有订单的平均销售金额 SELECT AVG(order_total) AS avg_amount FROM orders; -- 2.9-1 商品数量最大者 SELECT MAX(stock) AS max_stock FROM goods; -- 2.9-2 用户名字母序最靠前者（MAX 也能用在文本列） SELECT MAX(username) AS last_name FROM users; -- 2.10 价格最低者 SELECT MIN(price) AS min_price FROM goods;

COUNT(*)和COUNT(列名)的区别必须记住：前者统计行数，后者统计该列非 NULL 的值个数。评论表的content如果允许 NULL，COUNT(content)可能小于COUNT(*)。SUM和AVG会跳过 NULL，但如果整组全是 NULL，AVG返回 NULL 而不是 0，这个差异在做报表时经常导致图表出现空洞。MAX和MIN用在文本列上按排序规则比较，实验 2.9 第二题就是利用这一点——它们不是数值专用函数。

分组之后要做条件过滤，必须用HAVING而不是WHERE。WHERE在分组前过滤行，HAVING在分组后过滤组：

-- 消费总额超过 500 的用户：过滤的是组，用 HAVING SELECT user_id, SUM(order_total) AS total_amount FROM orders GROUP BY user_id HAVING SUM(order_total) > 500;

注意：HAVING后面可以直接写聚合函数，也可以引用SELECT里的别名（MySQL 支持，标准 SQL 不支持）。写HAVING total_amount > 500在 MySQL 里能跑，但换数据库就未必，我一般还是写完整表达式。

4. 连接与嵌套：INNER/LEFT JOIN 到 EXISTS、ANY、UNION 的选型

4.1 内连接与外连接：驱动表和 ON 条件的区别

实验 2.11 和 2.12 是连接查询的分水岭。内连接只返回两边都匹配上的行，外连接会保留一边的全部行。判断用哪种，看题目里有没有「所有」两个字：「查询所有订单的发出者名字」用内连接就够了，因为每个订单必然有用户；「列出所有用户 ID，以及他们的评论，如果有的话」必须用左连接，因为没评论过的用户也要出现在结果里。

-- 2.11-1 所有订单的发出者名字：订单表和用户表通过 user_id 连接 SELECT o.id AS order_id, o.order_total, u.username FROM orders o INNER JOIN users u ON o.user_id = u.id ORDER BY o.id; -- 2.11-2 每个用户购物车中的商品名称：购物车表和商品表通过 goods_id 连接 SELECT c.user_id, g.name AS goods_name, c.num FROM cart c INNER JOIN goods g ON c.goods_id = g.id ORDER BY c.user_id;

外连接的关键在于「哪张表的行必须全留」。实验 2.12 要求列出所有用户 ID，所以用户表在左边，用LEFT JOIN；如果用RIGHT JOIN，就得把用户表放到右边，语义一样但读起来别扭。

-- 2.12-1 左连接：用户表在左，全部用户都出现 SELECT u.id AS user_id, c.id AS comment_id, c.content FROM users u LEFT JOIN comments c ON u.id = c.user_id ORDER BY u.id, c.id; -- 2.12-2 右连接：评论表在左、用户表在右，效果与上面等价 SELECT u.id AS user_id, c.id AS comment_id, c.content FROM comments c RIGHT JOIN users u ON c.user_id = u.id ORDER BY u.id, c.id;

这两条结果是一样的，但我不建议在项目里混用。团队里统一用LEFT JOIN，把「必须全留」的表写在左边，可读性最好。RIGHT JOIN只在改写别人写好的 SQL 时才用得上，因为改成左连接要调换表顺序，容易改错。

外连接有一个特别隐蔽的坑：在WHERE里对右表加条件，会把外连接退化成内连接。这个留到第 5 章展开。

4.2 复合条件连接：连接和过滤能不能写在一起

实验 2.13 的复合条件连接，本质是在连接的基础上再加过滤。要注意的是过滤条件放ON还是放WHERE，在内连接里两者等价，在外连接里完全不是一回事。

-- 2.13-1 用户 1 的订单信息和客户名 SELECT o.id, o.order_total, o.order_date, u.username FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.id = 1; -- 2.13-2 每个用户购物车里的商品价格，按价格排序 SELECT c.user_id, g.name, g.price FROM cart c INNER JOIN goods g ON c.goods_id = g.id ORDER BY g.price;

第二条的顺序值得留意：ORDER BY g.price排的是连接之后的整个结果集，不是每张表各自排。如果购物车表里有 5 行、商品表 9 行，连接后是 5 行，排序作用在这 5 行上。搞清「排序在连接之后」这一点，实验 2.4 和 2.13 就不会混。

4.3 嵌套查询：IN、NOT IN 与 EXISTS 的取舍

实验 2.14 到 2.16 是子查询。子查询分两类：非相关子查询先算出一个集合，外层再拿它做条件；相关子查询对外层的每一行都执行一次，EXISTS通常属于这一类。

-- 2.14-1 订购了商品 1 的订单，反查下单用户 SELECT DISTINCT user_id FROM orders WHERE id IN ( SELECT order_id FROM order_detail WHERE goods_id = 1 ); -- 2.14-2 NOT IN：没订购过商品 1 的订单对应的用户 SELECT DISTINCT user_id FROM orders WHERE id NOT IN ( SELECT order_id FROM order_detail WHERE goods_id = 1 );

NOT IN是所有子查询里最容易翻车的一个。如果子查询的结果里出现 NULL，整个NOT IN会返回空集，因为x <> NULL的结果是UNKNOWN而不是TRUE。这个坑在实验数据里不一定能复现，但只要order_detail.order_id允许为 NULL，线上就会出问题。规避方式是用NOT EXISTS或者给子查询加WHERE order_id IS NOT NULL。

-- 2.15 今年新增会员的订单中，总价低于 100 的订单 SELECT o.id, o.order_total FROM orders o WHERE o.user_id IN ( SELECT id FROM users WHERE YEAR(create_time) = YEAR(CURDATE()) ) AND o.order_total < 100; -- 2.15-2 订单商品数量总和小于 100 的商品所在类别之外的其他类别 SELECT DISTINCT category_id FROM goods WHERE category_id NOT IN ( SELECT g.category_id FROM goods g INNER JOIN ( SELECT goods_id, SUM(num) AS total_num FROM order_detail GROUP BY goods <p> <a href="https://download.csdn.net/download/weixin_44849804/85557577" style="color:#ec7500;font-size:14px;"> 本文还有配套的精品资源，点击获取 </a> <img alt="menu-r.4af5f7ec.gif" src="https://csdnimg.cn/release/wenkucmsfe/public/img/menu-r.4af5f7ec.gif" style="width:16px;margin-left:4px;vertical-align:text-bottom;cursor:text;"> </p>