MySQL数据查询实验训练2：六类SQL查询骨架与避坑

简介这份PDF实验指导书对应国家开放大学MySQL数据库应用课程实验训练2聚焦数据查询操作适合正在学习SQL基础、需要按实验步骤完成课堂练习与课后复盘的初学者。内容围绕字段查询、多条件筛选、DISTINCT去重、ORDER BY排序、GROUP BY分组及COUNT/SUM/AVG/MAX/MIN聚合函数展开并延伸到内连接、外连接、复合条件连接、IN与NOT IN子查询、比较运算符和EXISTS判断按实验2.1至2.16组织知识点衔接清楚。资源包共1个PDF文件大小约1.75MB便于下载后直接查阅和打印练习。目前已有3339人学习说明其作为课程配套训练材料具有一定参考价值。读者可据此梳理查询语句的写法、理解各实验的分析思路并借助示例场景查漏补缺形成较完整的MySQL查询知识框架。1. 从 19 道题倒推MySQL 数据查询实验训练 2 到底练哪六种骨架很多人做 MySQL 数据库应用的数据查询实验是按题号一道一道抄过去SELECT 会写、JOIN 会拼一旦换成自己手里的业务表就卡住。实验训练 2 的价值不在题目本身而在于它把单表过滤、去重、排序、分组聚合、连接、嵌套子查询这六类写法按难度串成了一条线每道题背后都是一个真实问法查某个商品、算每个用户消费、找没下过单的人。配套的文档资料里还有完整的需求描述能看出出题人的意图。跟着做完你要拿走的不是 19 条 SQL而是一套能把中文需求翻译成查询语句的骨架。2. 建表到单表查询7 张表 3 类过滤怎么写才不返工单表查询看着最简单其实是整组实验的地基。表结构没设计好后面的分组、连接、嵌套会一路别扭。我一般先按实验里出现的实体把表补齐商品、类别、用户、订单、订单明细、评论、购物车一共七张。这一步花二十分钟后面省两小时。2.1 先把七张表建出来字段类型和索引决定后面好不好写实验描述里反复提到“商品表中包含数量信息”“订单表中包含订单总价”说明出题人默认这些字段是冗余存储的。照着这个思路建模价格用 DECIMAL 而不是 FLOAT日期拆成 DATE 单独一列促销状态用 TINYINT 表示字段名统一小写下划线。建库时把字符集钉死能少掉一大半中文乱码的麻烦。-- 建库字符集固定 utf8mb4避免中文商品名和评论内容出现乱码 CREATE DATABASE IF NOT EXISTS shop_lab DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE shop_lab; CREATE TABLE category ( cate_id INT PRIMARY KEY AUTO_INCREMENT, cate_name VARCHAR(50) NOT NULL ); CREATE TABLE goods ( goods_id INT PRIMARY KEY AUTO_INCREMENT, goods_name VARCHAR(100) NOT NULL, cate_id INT NOT NULL, price DECIMAL(10,2) NOT NULL, stock INT NOT NULL DEFAULT 0, -- 实验 2.9 里的“商品数量” is_promotion TINYINT NOT NULL DEFAULT 0, -- 1 促销0 非促销 KEY idx_cate (cate_id), KEY idx_price (price) ); CREATE TABLE users ( user_id INT PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(50) NOT NULL, create_time DATETIME NOT NULL, KEY idx_create (create_time) -- 实验 2.4 “今年新增会员”靠它 ); CREATE TABLE orders ( order_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) ); CREATE TABLE order_detail ( detail_id INT PRIMARY KEY AUTO_INCREMENT, order_id INT NOT NULL, goods_id INT NOT NULL, quantity INT NOT NULL, price DECIMAL(10,2) NOT NULL, KEY idx_order (order_id), KEY idx_goods (goods_id) ); CREATE TABLE comments ( comment_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, goods_id INT NOT NULL, content VARCHAR(255), KEY idx_goods (goods_id) ); CREATE TABLE cart ( cart_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, goods_id INT NOT NULL, num INT NOT NULL DEFAULT 1 );几个取舍值得说明。order_total冗余存在订单表里是因为实验 2.5、2.7、2.8 都在直接对订单总价求和求平均如果只存明细再去 JOIN 汇总每个聚合题都要多写一层子查询反而不利于理解聚合函数本身。is_promotion用 TINYINT 而不是 ENUM是为了在 2.2 的“是否促销”条件里能直接写 1。索引只建在会被 WHERE 和 JOIN 用到的列上商品名称这种模糊查询列先不建避免新手误以为索引越多越好。灌一批能覆盖所有实验分支的测试数据数量不用多关键是要有边界有促销商品、有非促销商品、有下过单和没下过单的用户、有重复评论。INSERT INTO category (cate_name) VALUES (汽车玻璃), (车载电器), (养护用品); INSERT INTO goods (goods_name, cate_id, price, stock, is_promotion) VALUES (挡风玻璃, 1, 880.00, 30, 1), (侧窗玻璃, 1, 320.00, 50, 0), (行车记录仪, 2, 499.00, 120, 1), (车载吸尘器, 2, 199.00, 80, 0), (玻璃水, 3, 19.90, 500, 1); INSERT INTO users (user_name, create_time) VALUES (A客户, 2023-03-11 10:00:00), (B客户, 2024-01-05 09:20:00), (C客户, 2024-06-18 16:40:00); INSERT INTO orders (user_id, order_total, order_date) VALUES (1, 880.00, 2024-02-01), (1, 199.00, 2024-02-01), (2, 499.00, 2024-03-15), (3, 19.90, 2024-03-16); INSERT INTO order_detail (order_id, goods_id, quantity, price) VALUES (1,1,1,880.00), (2,4,1,199.00), (3,3,1,499.00), (4,5,2,19.90); INSERT INTO comments (user_id, goods_id, content) VALUES (1,1,装车合适), (2,1,包装完好), (2,1,第二次买); INSERT INTO cart (user_id, goods_id, num) VALUES (1,3,1), (2,5,3);跑完这段comments表里商品 1 有两条评论来自同一个用户正好用来验证 2.3 的 DISTINCTusers表里三个人都下过单想测 LEFT JOIN 的 NULL 分支得手动删一条订单这点后面讲外连接时会再说。2.2 字段查询与多条件过滤条件写在 WHERE 里坑藏在类型和 NULL 里实验 2.1 是纯粹的单表等值查询2.2 在它基础上加了 AND。这两个的 SQL 结构一模一样区别只在过滤条件的个数写的时候没必要当成两类知识。-- 实验 2.1(1)商品名精确匹配只碰 goods 一张表 SELECT goods_id, goods_name, price, stock FROM goods WHERE goods_name 挡风玻璃; -- 实验 2.1(2)按主键查订单走的是 PRIMARY KEY速度最快的一类查询 SELECT order_id, user_id, order_total, order_date FROM orders WHERE order_id 1; -- 实验 2.2两个条件同时成立促销 1 且价格低于 1000 SELECT goods_id, goods_name, price FROM goods WHERE is_promotion 1 AND price 1000;这三条语句的执行路径完全不同值得掰开看。第一条在goods_name上没有索引走的是全表扫描五条数据无所谓五十万条就很疼第二条命中主键索引是常数级第三条有两个可选索引优化器大概率选idx_price做范围扫描再回表用is_promotion过滤。理解这一点之后你会自然养成一个习惯写多条件时先想哪个条件过滤性最强。需要单独强调的是 NULL。is_promotion建表时给了 NOT NULL 默认值 0所以 1和 1加起来正好是全表。如果这个字段允许 NULL 1会把 NULL 行整行漏掉因为 NULL 参与任何比较结果都是 UNKNOWN而不是 TRUE。这类问题不报错、不告警结果就是少几行是查询里最典型的黑匣子。字符串匹配还有个常见需求是模糊查goods_name LIKE %玻璃%能用但前置通配符会让 B-tree 索引彻底失效数据量上来只能换全文索引或者外部检索引擎。实验里没考这层实际项目里绕不开。2.3 DISTINCT 去的是整行不是某一列评论去重与年份段查询实验 2.3 的两道题看起来都是“去重”但去重对象不一样理解偏差会直接导致结果错。-- 实验 2.3(1)对商品 1 发过评论的用户 ID同一人多条评论只留一条 SELECT DISTINCT user_id FROM comments WHERE goods_id 1; -- 实验 2.3(2)会员创建年份每年只出现一次 SELECT DISTINCT YEAR(create_time) AS create_year FROM users ORDER BY create_year;第一条的语义很直接SELECT后面只有user_id一列DISTINCT 作用的也就是这一列同一个用户发十条评论也只返回一行。但如果把语句改成SELECT DISTINCT user_id, content去重就变成对(user_id, content)这个组合判重同一用户的两条不同评论会双双保留。这是 DISTINCT 最容易被误解的地方——它永远作用于整个选择列表不是某一列。真要对单列去重又想带出其他列正确做法是分组取聚合比如GROUP BY user_id再配MIN(comment_id)。第二条里YEAR(create_time)是一个函数表达式DISTINCT 对它去重等价于按年份分组。这种写法在数据量小的时候没问题但YEAR()包住列之后索引失效而且它只能拿到年份拿不到“1 年为一段”的完整语义。要做时间段统计我更倾向用DATE_FORMAT(create_time, %Y)或者直接GROUP BY YEAR(create_time)后者在需要同时输出每年人数时更自然-- 同样一份数据按年份分组还能顺带算出每年新增人数 SELECT YEAR(create_time) AS create_year, COUNT(*) AS user_cnt FROM users GROUP BY YEAR(create_time) ORDER BY create_year;两条语句的结果行数一样但后者多给了一列信息也更容易扩展成“每年每月”这种多级统计。写实验报告时把这两个版本都放上去能看出你是理解语义而不是背语法。3. 排序与分组ORDER BY 的排序键和 GROUP BY 的聚合边界从这一章开始查询从“取出哪些行”转向“这些行怎么组织”。ORDER BY 管输出顺序GROUP BY 管行的归并聚合函数负责把一组行压成一个值。三者经常一起出现但只要有一个用错位置结果就会错得很有迷惑性——行数对、字段名对、数值不对。3.1 ORDER BY排序键选谁、DESC 落在哪一列实验 2.4 的两道题一道按商品 ID 降序一道按用户名升序前者考 DESC 的位置后者考日期条件加排序的组合。-- 实验 2.4(1)类别 1 的商品按商品 ID 降序 SELECT goods_id, goods_name, price FROM goods WHERE cate_id 1 ORDER BY goods_id DESC; -- 实验 2.4(2)今年新增会员按用户名升序 SELECT user_id, user_name, create_time FROM users WHERE create_time MAKEDATE(YEAR(CURDATE()), 1) AND create_time MAKEDATE(YEAR(CURDATE()) 1, 1) ORDER BY user_name ASC;第二条的日期条件值得单独讲。写成YEAR(create_time) YEAR(CURDATE())结果一样但列被函数包住之后idx_create用不上只能全表扫描。改写成左闭右开的时间区间索引就能正常走范围扫描这个习惯在大表上是数量级的差别。MAKEDATE(年, 1)返回当年 1 月 1 日加一年就是次年 1 月 1 日和的边界刚好不重叠也不遗漏比BETWEEN 2024-01-01 AND 2024-12-31更安全因为后者会把 12 月 31 日当天带时分秒的记录漏掉。DESC 和 ASC 只作用于紧跟其后的那一列多列排序要逐列写-- 先按类别升序类别内部再按价格降序 SELECT cate_id, goods_id, price FROM goods ORDER BY cate_id ASC, price DESC;排序还有两个隐性成本。一是排序字段如果没有索引MySQL 会走 filesort在内存里排完再返回数据量大时吃内存也吃时间二是ORDER BY和LIMIT配合时才有机会用上“优先队列”式的优化只排前 N 条。实验数据量小这些差异看不出来但你把这套 SQL 拿到真实库上跑差距立刻显现。3.2 GROUP BY 把明细压成一行COUNT、SUM、AVG、MAX、MIN 各自的落点实验 2.5 到 2.10 是一整组聚合练习核心只有一句话GROUP BY 后面跟的是分组键SELECT 里除分组键以外的列必须被聚合函数包起来。-- 实验 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 cate_id, price, COUNT(*) AS goods_cnt, SUM(stock) AS stock_sum FROM goods GROUP BY cate_id, price ORDER BY cate_id, price;第一题的分组键是user_id聚合对象是order_total得到的是每个用户一行。第二题是“多列分组”分组键变成(cate_id, price)这个组合只有两列都相同才会被归到一组粒度比第一题细得多。多列分组时结果里最好把两个分组键都 SELECT 出来否则你看到一行数字根本不知道它属于哪个组合。五个聚合函数各有落点实验里对应的业务问法可以这样对照实验函数业务问法关键点2.6COUNT类别数量、每天接单数COUNT(*)数行COUNT(列)跳过 NULL2.7SUM每天销售额只能作用于数值列NULL 按 0 累加但不计入行数2.8AVG所有订单平均金额分母是非 NULL 行数不是总行数2.9MAX商品最大库存、用户名最大值文本列按排序规则比较不是“字母最靠前”2.10MIN商品最低价格与 MAX 对称同样受 NULL 影响-- 实验 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 day_amount FROM orders GROUP BY order_date ORDER BY order_date; -- 实验 2.8所有订单的平均销售金额 SELECT AVG(order_total) AS avg_amount FROM orders; -- 实验 2.9最大库存 用户名最大值 SELECT MAX(stock) AS max_stock FROM goods; SELECT MAX(user_name) AS max_name FROM users; -- 实验 2.10最低价格 SELECT MIN(price) AS min_price FROM goods;这里有个必须点破的地方实验 2.9(2) 的原始分析写的是“获得按字母顺序排列的最高或者最低者”但MAX()在文本列上返回的是排序规则下的最大值中文环境里按 utf8mb4 的排序规则比较得到的不一定是你直觉里“字母最靠前”的那个。想要严格按字母序取最小用MIN(user_name)想要按长度或拼音排得显式写ORDER BY ... LIMIT 1。这类语义误读不会报错只会让你在答辩时说不出为什么结果是这样。另一个高频误区是COUNT(*)和COUNT(列)混用。前者数行数后者只数该列非 NULL 的行。如果某个字段允许 NULL两个值会不一样而实验数据里恰好没有 NULL所以看不出差别换到生产库上就是数据对不上的事故。3.3 WHERE 与 HAVING过滤发生在分组前还是分组后聚合查询里最容易写错的是过滤位置。WHERE 在分组之前筛行HAVING 在分组之后筛组两者不能随便替换。-- 先筛掉金额过小的订单再按用户汇总——WHERE 作用在明细行上 SELECT user_id, SUM(order_total) AS total_amount FROM orders WHERE order_total 100 GROUP BY user_id; -- 先按用户汇总再筛掉总额不足 500 的用户——HAVING 作用在分组结果上 SELECT user_id, SUM(order_total) AS total_amount FROM orders GROUP BY user_id HAVING SUM(order_total) 500;两条语句的差别不是写法风格是语义。第一条里的 100 是单笔订单的门槛被过滤掉的订单根本不参与求和第二条里的 500 是用户总额的门槛所有订单都参与求和只是总额不够的用户不出现在结果里。把 HAVING 的条件挪到 WHERE 里或者反过来结果都会变而且往往变得很隐蔽——行数差不多数字差一点不核对根本发现不了。HAVING 里可以直接用 SELECT 中定义的别名这是 MySQL 对标准 SQL 的扩展写起来更短但一旦把这段 SQL 迁到别的 database 上别名可能不被识别稳妥的写法还是重复写聚合表达式。4. 连接查询与嵌套子查询从 INNER JOIN 一路写到 UNION ALL单个表能回答的问题有限一旦需求里出现“订单的发出者名字”“购物车里的商品名称”就必须跨表。实验 2.11 到 2.19 把这部分拆成了连接和嵌套两条路线实际项目里两条路线经常混用选哪条主要看可读性和执行计划。4.1 内连接拿外键把订单表和用户表对齐实验 2.11 的两道题是标准的内连接场景连接条件都是“从表的外键 主表的主键”。-- 实验 2.11(1)所有订单的发出者名字 SELECT o.order_id, u.user_name, o.order_total, o.order_date FROM orders o INNER JOIN users u ON o.user_id u.user_id; -- 实验 2.11(2)每个用户购物车里的商品名称 SELECT c.user_id, g.goods_name, c.num FROM cart c INNER JOIN goods g ON c.goods_id g.goods_id ORDER BY c.user_id;连接条件的写法决定了结果。ON o.user_id u.user_id是等值连接只有两边都匹配上的行才会出现用户表里没下过单的人不会出现在结果里订单表里如果存在一个指向已删除用户的user_id那一行同样会被丢掉。这正是 INNER JOIN 的语义不是 bug。表别名不是可选项而是必需品。两张表都有user_id列不写别名 MySQL 会直接报 ambiguous column写了别名之后o.user_id和u.user_id指向明确语句也短了一截。多表连接时我习惯按“主表在前、从表在后”的顺序写读起来像一句话从订单出发找到它的用户。连接字段上有没有索引直接决定这条语句是走 nested loop 还是全表扫。orders.user_id建了idx_userusers.user_id是主键这条 JOIN 的执行代价很低如果连接列两边都没索引MySQL 会退化成笛卡尔积再过滤几十万行就能把数据库拖住。4.2 外连接LEFT JOIN 与 RIGHT JOIN 的等价改写和 NULL 处理实验 2.12 要求“列出所有用户 ID以及他们的评论如果有的话”关键在“所有”和“如果有”。内连接做不到这一点因为没评论的用户会被直接过滤掉。-- 实验 2.12(1)用户表在左全部保留评论没有就补 NULL SELECT u.user_id, c.comment_id, c.content FROM users u LEFT JOIN comments c ON u.user_id c.user_id ORDER BY u.user_id; -- 实验 2.12(2)同一件事换成 RIGHT JOIN用户表放在右边 SELECT u.user_id, c.comment_id, c.content FROM comments c RIGHT JOIN users u ON c.user_id u.user_id ORDER BY u.user_id;两条语句结果完全一样区别只在于“谁是保留全部行的那一侧”。LEFT JOIN 保留左表全部行RIGHT JOIN 保留右表全部行写的时候把要全量展示的表放在对应位置就行。原文的分析提到“需将全部显示的列名写在 JOIN 语句左边/右边”说的就是这个位置关系。外连接最常翻车的地方在 WHERE 子句。如果在 LEFT JOIN 之后写WHERE c.comment_id IS NOT NULL等于把没评论的用户又筛掉了整个外连接退化成内连接。想只筛评论内容但不丢用户条件要写在 ON 里-- 条件写在 ON仍然保留全部用户只是不匹配的评论列为 NULL SELECT u.user_id, c.comment_id, c.content FROM users u LEFT JOIN comments c ON u.user_id c.user_id AND c.content LIKE %包装%;这个差别在实际排查里非常关键一个报表“用户数突然少了一半”八成就是有人在 WHERE 里加了从表字段的条件把外连接悄悄改成了内连接。4.3 复合条件连接连接之后再筛再排实验 2.13 在连接的基础上叠加过滤和排序形式上就是 JOIN WHERE ORDER BY 的组合但执行顺序有讲究。-- 实验 2.13(1)用户 1 的订单信息和客户名 SELECT o.order_id, o.order_total, u.user_name FROM orders o INNER JOIN users u ON o.user_id u.user_id WHERE o.user_id 1; -- 实验 2.13(2)购物车里的商品价格按价格升序 SELECT c.user_id, g.goods_name, g.price FROM cart c INNER JOIN goods g ON c.goods_id g.goods_id ORDER BY g.price ASC;第一题里o.user_id 1这个条件既可以写在 WHERE 里也可以追加到 ON 后面内连接下结果一样。但对外连接就不等价了原因和上一节说的一样。所以我的习惯是内连接的条件统一放 WHERE外连接的保留条件放 ON过滤条件放 WHERE位置固定下来后面改语句不容易出错。第二题的排序键是g.price来自被连接的表。排序发生在连接结果集上如果连接产生了很多行排序开销会跟着放大。真实场景里这种查询通常会加LIMIT让 MySQL 只维护前 N 条的有序队列。4.4 嵌套子查询IN、EXISTS、ANY/ALL 的适用边界实验 2.14 到 2.18 全是子查询五种写法的区别主要在“子查询返回什么”和“外层怎么用”。-- 实验 2.14(1)订购了商品 1 的订单对应的用户 SELECT user_id FROM orders WHERE order_id IN ( SELECT order_id FROM order_detail WHERE goods_id 1 ); -- 实验 2.14(2)没订购商品 1 的订单对应的用户 SELECT user_id FROM orders WHERE order_id NOT IN ( SELECT order_id FROM order_detail WHERE goods_id 1 );IN的语义是“等于子查询结果集中的任意一个值”NOT IN是它的反面。这里有一个必须记住的陷阱如果子查询返回的集合里混进了 NULLNOT IN会整体返回空集因为x NULL的结果是 UNKNOWN 而不是 TRUE。本次实验里order_detail.order_id是 NOT NULL所以安全换成允许 NULL 的列就得改成NOT EXISTS或者加WHERE 列 IS NOT NULL。-- 实验 2.15今年新增会员中订单总额小于 100 的订单 SELECT order_id, user_id, order_total FROM orders WHERE user_id IN ( SELECT user_id FROM users WHERE create_time MAKEDATE(YEAR(CURDATE()), 1) AND create_time MAKEDATE(YEAR(CURDATE()) 1, 1) ) AND order_total 100; -- 实验 2.15(2)被订购总量小于 100 的商品所在类别之外的类别 SELECT DISTINCT cate_id FROM goods WHERE goods_id IN ( SELECT goods_id FROM order_detail GROUP BY goods_id HAVING SUM(quantity) 100 ) AND cate_id (SELECT cate_id FROM goods WHERE goods_id 1);第二题是嵌套里套聚合的典型。内层先按商品分组求订购总量用 HAVING 筛出小于 100 的那批goods_id外层再拿这批 ID 去商品表反查类别最后用排除掉商品 1 所在的类别。原文对这道题的描述比较绕我按“可执行、结果可解释”的原则做了简化逻辑主干没变。-- 实验 2.16(1)用户 100 存在且下过单就列出它的信息 SELECT u.user_id, u.user_name, u.create_time FROM users u WHERE u.user_id 100 AND EXISTS (SELECT 1 FROM orders o WHERE o.user_id u.user_id); -- 实验 2.16(2)类别 100 存在时列出其中价格小于 5 的商品 SELECT g.goods_id, g.price FROM goods g WHERE g.cate_id 100 AND g.price 5 AND EXISTS (SELECT 1 FROM category c WHERE c.cate_id g.cate_id);EXISTS 只关心子查询有没有返回行不关心返回什么所以子查询里写SELECT 1就够了写成SELECT *也不会更慢但读起来意图不清晰。它和 IN 的一个重要区别是 EXISTS 通常是相关子查询外层每行都要带着自己的值进子查询跑一次数据量大时性能差异明显而在子查询结果集很小的情况下IN 往往更快。选哪个不是背结论是看两边数据量。-- 实验 2.17比订单明细里任意一个价格都高的商品 SELECT goods_id, price FROM goods WHERE price ANY (SELECT price FROM order_detail); -- 实验 2.18比订单明细里所有价格都高的商品 SELECT goods_id, price FROM goods WHERE price ALL (SELECT price FROM order_detail); ANY等价于 MIN(...) ALL等价于 MAX(...)。ANY 只要有一个值满足就通过ALL 要求全部满足。这两个关键字对 NULL 极其敏感子查询结果里一旦有 NULL ALL很可能返回空集因为和 NULL 比较得到的是 UNKNOWN。实际项目里我会直接用 (SELECT MAX(price) ...)语义一样还没有 NULL 的歧义。4.5 集合运算UNION 去重、UNION ALL 不去重实验 2.19 把两个独立查询的结果拼成一个结果集这类需求在报表里很常见比如“低价商品”和“某几个类别的商品”要放在一张表里看。-- 实验 2.19(1)UNION 自动去掉重复行 SELECT goods_id, goods_name, price FROM goods WHERE price 5 UNION SELECT goods_id, goods_name, price FROM goods WHERE cate_id IN (1, 2); -- 实验 2.19(2)UNION ALL 保留全部行包括重复的 SELECT goods_id, goods_name, price FROM goods WHERE price 5 UNION ALL SELECT goods_id, goods_name, price FROM goods WHERE cate_id IN (1, 2);两条语句唯一的差别是去不去重但这个差别带来两个后果。结果行数上UNION 会少掉交集部分执行代价上UNION 需要对合并后的结果做一次去重通常是排序或哈希数据量大时比 UNION ALL 慢不少。能用 UNION ALL 的地方别用 UNION这是性能优化里成本最低的一条。参与 UNION 的各个 SELECT 必须列数相同、对应列的类型兼容列名以第一个 SELECT 为准。如果两张表的列顺序不一样写的时候要显式对齐不要靠“碰巧对上”。5. 避坑与排查5 个把结果查错却看不出来的地方查询语句最麻烦的地方在于它几乎不报错。语法错了有提示语义错了只会安安静静地返回一批看起来很正常的数据。下面这几条都是我在这套实验里真实翻过的车每条按“现象 → 原因 → 解决”记。5.1 行数突然翻倍连接少写条件或者一对多没处理现象orders表和order_detail表连接之后本来 4 条订单变成了 6 条、8 条金额求和结果比预期大了一倍多。原因订单和订单明细是一对多关系一条订单对应多条明细连接之后订单行会被复制。如果这时再对order_total求和同一笔订单的总额会被重复累加。另一种情况是忘了写 ON 条件直接退化成笛卡尔积行数是两张表行数的乘积。解决先确认连接之后的行数是否符合业务粒度需要按订单汇总时要么先对明细做子查询聚合再连接要么改用SUM(DISTINCT ...)这类非常规手段并明确注释。我的做法是先单独跑一次SELECT COUNT(*)看连接结果的行数和预期对上了再往上加聚合这一步多花十秒能省掉半小时的返工。5.2 报错 1055GROUP BY 的列和 SELECT 的列对不上现象SELECT cate_id, goods_name, COUNT(*) FROM goods GROUP BY cate_id;直接报Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column。原因MySQL 5.7 之后默认开启ONLY_FULL_GROUP_BY要求 SELECT 里的非聚合列必须出现在 GROUP BY 中。分组之后一组有多行goods_name取哪一行是不确定的严格模式直接拒绝执行。解决要么把goods_name加进 GROUP BY要么用聚合函数包起来比如MIN(goods_name)要么明确用ANY_VALUE(goods_name)告诉数据库“随便取一个我知道不确定”。三种做法里只有第一种是真的把语义理清了后两种都是在绕过检查。上线前在本地把sql_mode设成和线上一致能提前发现这类问题。5.3 结果集为空NOT IN 撞上 NULL或者日期条件写死了年份现象NOT IN子查询明明有数据外层却一行都不返回另一台机器上同样的语句能查出结果换台机器就查不到。原因有两类。一是子查询结果里含 NULLNOT IN的整个判断变成 UNKNOWN一行都不匹配。二是日期条件写成了固定的YEAR(create_time) 2024换了年份自然查不到“今年”的数据而实验环境数据是分批导入的。解决NOT IN一律先确认子查询列是否 NOT NULL不确定就换成NOT EXISTS。日期条件统一改成相对当前时间的区间写法用CURDATE()推导边界不要手写年份。改完之后顺手EXPLAIN一下看索引有没有用上。5.4 MAX 用在文本列以为拿到的是“字母最靠前”现象实验 2.9(2) 用MAX(user_name)取“字母排序最靠前者”结果拿到的是排序靠后的那个名字。原因MAX 返回的是排序规则下的最大值不是最小值。中文环境里 utf8mb4 的排序规则还会让中文名和英文名混在一起比较结果更不符合直觉。解决要最小值用MIN要按特定规则排就用ORDER BY user_name ASC LIMIT 1规则可以显式指定COLLATE。这类语义问题不会报错只能靠对函数定义的准确理解来防。5.5 同一条 SQL 两个环境结果不同字符集、排序规则和时区现象本地跑出来 3 条测试环境跑出来 5 条或者按名字排序的顺序在两个环境里不一样。原因建库时没指定字符集不同环境默认值不同比较和排序规则跟着变日期字段用NOW()写入服务器时区不一致跨天边界的数据就会漂移。解决建库建表时把字符集和排序规则写死日期统一用DATE类型并在应用层确定时区别依赖数据库默认值。这套习惯看着啰嗦但它是让“同一份 SQL 在哪都能复现”的唯一办法。6. 进阶验证把 19 个实验压成一份可回归的自测清单题目做完不等于写对。我现在的习惯是给每个实验配一条“判据查询”用行数或聚合值做断言跑一遍就知道哪条改坏了。做法很简单把要验证的查询包成派生表外层只取一个统计值。-- 每条实验对应一行输出编号 判据值方便和历史结果对比 SELECT 2.3-1 AS item, COUNT(*) AS val FROM ( SELECT DISTINCT user_id FROM comments WHERE goods_id 1 ) t UNION ALL SELECT 2.5-1, COUNT(*) FROM ( SELECT user_id FROM orders GROUP BY user_id ) t UNION ALL SELECT 2.7, ROUND(SUM(day_amount), 2) FROM ( SELECT order_date, SUM(order_total) AS day_amount FROM orders GROUP BY order_date ) t UNION ALL SELECT 2.12-1, COUNT(*) FROM ( SELECT u.user_id FROM users u LEFT JOIN comments c ON u.user_id c.user_id ) t;关键判据可以整理成一张对照表交付前逐条核对实验判据期望特征2.3(1)去重后用户数小于评论总条数2.5(1)分组后行数等于有订单的用户数2.7每日销售额合计等于订单总额合计2.12(1)LEFT JOIN 行数不小于用户数2.19UNION 与 UNION ALL 行数差差值等于两结果集的交集行数这套清单最大的价值不是省时间是让“改一条 SQL”变成可验证的动作。有几次我为了加一个过滤条件动了 JOIN结果 2.12 的行数从 3 掉到 2一眼就看出外连接被写成了内连接。后来我把这份判据脚本存成.sql文件配合文档资料里的实验说明一起归档换台机器重新灌数据也能一键比对。从那以后我每写完一组查询都会先跑一遍行数判据再去看具体数据长什么样顺序反了就容易自我说服。希望帮到你。本文还有配套的精品资源点击获取