电影、演员、票房这三张表凑在一起是我见过最适合练 MySQL 数据查询的素材组合。它不像电商订单表那样字段繁多到劝退也不像单表练习那样查完WHERE就没下文了——电影表给你维度、演员表给你多对多关系、票房表给你可计算的数值一条SELECT语句能从最简单的单表筛选一路练到多表连接加分组聚合。这套基于电影、演员及票房应用的数据查询练习核心就是把SELECT从能查出东西练到能按业务问题精确查出东西。不管你是刚学完SELECT * FROM的新手还是能写 JOIN 但一遇到每组取最大值就卡壳的进阶选手下面这些内容都能直接拿去对照练。1. 先把数据场景和表结构吃透1.1 四张表的定位与关系梳理很多人做查询练习有个坏习惯上来就写 SQL写完发现结果不对再回头猜字段含义来回折腾半小时。我的建议是动手前先花五分钟把表关系画清楚——不用画图在纸上列四行就够。这套场景的骨架是四张表电影表movie存影片自身属性演员表actor存演员自身属性演出关系表movie_actor记录谁演了哪部片子、演什么角色票房表box_office记录每部片子的上映日期、票房收入、制作成本和观影人次。关键在于理解movie_actor 这张中间表存在的必要性。一部电影有多个演员一个演员也参演多部电影这是典型的多对多关系。关系型数据库不能在一个字段里塞数组所以必须拆出一张只存电影ID 演员ID的关联表。这张表看着简单但它是后面所有查询某演员参演的全部电影统计每部片子的主演人数这类题目的必经之路。还有一点容易被忽略票房表用 movie_id 做主键意味着它和电影表是一对一关系。这个设计选择很实在——一部电影只有一条票房记录用 movie_id 当主键天然保证了唯一性也避免了有人往表里插重复的票房数据。如果换成自增 ID 做主键你就得额外加唯一约束多一层管理成本。把关系理清后画出来的结构是这样的movie 和 box_office 一对一movie 和 actor 通过 movie_actor 多对多。剩下的所有查询题本质都是在这三条线上做文章。1.2 一份可以直接抄走的建表与造数脚本练习查询必须有数据。空表跑SELECT什么都验证不了所以先把建表和插数的脚本准备好。我按最小可用原则设计字段够用又不冗余你可以直接复制执行。CREATE DATABASE IF NOT EXISTS film_demo DEFAULT CHARSET utf8mb4; USE film_demo; CREATE TABLE movie ( movie_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL, release_year YEAR, genre VARCHAR(30), director VARCHAR(50), duration_min INT, country VARCHAR(30), rating DECIMAL(3,1) ); CREATE TABLE actor ( actor_id INT PRIMARY KEY, actor_name VARCHAR(50) NOT NULL, gender CHAR(1), birth_year YEAR, nationality VARCHAR(30) ); CREATE TABLE movie_actor ( movie_id INT, actor_id INT, role_name VARCHAR(50), is_lead TINYINT DEFAULT 0, PRIMARY KEY (movie_id, actor_id), FOREIGN KEY (movie_id) REFERENCES movie(movie_id), FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ); CREATE TABLE box_office ( movie_id INT PRIMARY KEY, release_date DATE, domestic_gross DECIMAL(12,2), overseas_gross DECIMAL(12,2), budget DECIMAL(12,2), tickets_sold INT, FOREIGN KEY (movie_id) REFERENCES movie(movie_id) );字段类型上有几个地方值得说一说这些是练习时容易被判错、实际项目里也容易踩的点rating用DECIMAL(3,1)而不是FLOAT因为评分要参与AVG()计算和比较。浮点数在 MySQL 里存8.7可能变成8.699999你写WHERE rating 8.7就查不出来这是新手最常见的明明有数据却查不到的原因之一。DECIMAL是精确值类型AVG(rating)出来的结果也更符合预期。票房金额用DECIMAL(12,2)12 位有效数字、2 位小数最大能存到百亿级别国内加上海外票房完全够用也不会因为精度问题在求和时出现尾差。movie_actor的主键设成(movie_id, actor_id)的联合主键这一手同时解决了两件事保证同一部片子里同一个演员不会被重复插入以及让按电影查演员和按演员查电影两个方向都能走主键索引。紧接着把示例数据灌进去。数据量不用大十几部电影、二十来个演员、三十来条演出关系就足够覆盖所有查询题型而且结果你肉眼就能验证对错——这点非常重要数据量大到几十万行的时候你根本没法判断结果对不对练习效果反而变差。INSERT INTO movie VALUES (1,长风渡月,2019,剧情,陈默,128,中国,8.6), (2,深海回声,2021,科幻,林越,142,中国,8.1), (3,第八号站台,2018,悬疑,陈默,115,中国,7.9), (4,夏日邮局,2020,爱情,苏青,106,中国,8.3), (5,孤岛纪年,2022,剧情,周砚,134,中国,8.8), (6,迷城追踪,2017,动作,林越,121,中国,7.2), (7,雪线之上,2023,冒险,周砚,147,中国,8.4), (8,旧照片,2016,剧情,苏青,98,中国,7.6), (9,北纬四十度,2021,纪录片,陈默,89,中国,9.0), (10,无人区来信,2019,悬疑,周砚,127,中国,8.0); INSERT INTO actor VALUES (101,沈听澜,F,1985,中国), (102,陆行舟,M,1982,中国), (103,苏白露,F,1990,中国), (104,程亦寒,M,1978,中国), (105,江晚晴,F,1993,中国), (106,顾岩,M,1988,中国), (107,温桦,F,1995,中国), (108,谢承宇,M,1980,中国); INSERT INTO movie_actor VALUES (1,101,沈月如,1),(1,102,周砚清,1),(1,106,老陈,0), (2,103,林深,1),(2,104,舰长,1),(2,108,研究员B,0), (3,102,陆征,1),(3,105,苏晓,1), (4,105,江愿,1),(4,107,温小满,1),(4,101,客串老师,0), (5,104,岛主,1),(5,103,小满,1),(5,106,渔民甲,0), (6,106,顾锋,1),(6,108,反派B,1), (7,101,领队,1),(7,104,向导,1),(7,107,队员C,0), (8,103,成年女儿,1),(8,102,父亲,1), (9,108,旁白,0), (10,105,记者,1),(10,106,司机,1),(10,102,站长,0); INSERT INTO box_office VALUES (1,2019-07-12,52300.00,8100.00,12000.00,1250), (2,2021-08-06,98700.00,26400.00,32000.00,2340), (3,2018-11-23,31200.00,4300.00,9000.00,780), (4,2020-02-14,44800.00,5600.00,8000.00,1090), (5,2022-09-30,132600.00,41800.00,40000.00,3080), (6,2017-06-16,27600.00,7200.00,15000.00,690), (7,2023-01-20,76400.00,12900.00,22000.00,1780), (8,2016-04-08,18900.00,2100.00,6000.00,470), (9,2021-11-05,8700.00,600.00,3000.00,215), (10,2019-12-13,39500.00,4900.00,10000.00,960);注意box_office的金额单位我用的是万元这是一个刻意的设计。真实项目里一定要在字段注释或者文档里写清单位我见过不止一次因为一个按元存、一个按万元存导致汇总表差了四个数量级的翻车事故。练习时先约定好单位养成习惯。2. 单表查询SELECT 的执行顺序比语法顺序更要紧2.1 为什么 WHERE 里不能用 SELECT 起的别名这是每个学 SQL 的人都会撞一次的墙。你写了这么一条SELECT title, domestic_gross overseas_gross AS total_gross FROM box_office WHERE total_gross 100000;报错Unknown column total_gross in where clause。明明上面刚起的别名下面就不认识了。原因在于 SQL 的逻辑执行顺序和我们书写的顺序并不一致。写的时候是从上往下SELECT…FROM…WHERE…GROUP BY…HAVING…ORDER BY…LIMIT。但数据库内部的逻辑处理顺序是这样的执行阶段关键字作用1FROM / JOIN确定数据来源生成笛卡尔积再过滤2ON连接条件过滤3WHERE行级过滤4GROUP BY分组5HAVING组级过滤6SELECT计算输出列、生成别名7DISTINCT去重8ORDER BY排序9LIMIT截断SELECT排在第 6 位WHERE排在第 3 位。别名在第 6 步才产生第 3 步当然不认识它。而ORDER BY在第 8 步此时别名已经存在所以ORDER BY total_gross DESC完全合法。搞懂这张顺序表的好处远不止解决这一个报错。它解释了另外几个常见现象WHERE里不能用聚合函数聚合发生在GROUP BY之后WHERE之前还没分组HAVING里可以用聚合函数但通常不建议放非聚合的行级条件因为它在GROUP BY之后才执行过滤越早性能越好以及COUNT(*)配合LEFT JOIN时统计值和直觉不一致因为WHERE在连接之后执行会把LEFT JOIN补出来的 NULL 行干掉。正确写法很简单——要么把表达式重复一遍要么套一层子查询SELECT title, domestic_gross overseas_gross AS total_gross FROM box_office WHERE domestic_gross overseas_gross 100000;重复表达式的写法在 MySQL 里没有性能问题优化器能识别。但字段一多、表达式一长就很啰嗦这时候用子查询或者 CTE 会更清晰第 4 节会展开讲。2.2 条件过滤BETWEEN、IN、LIKE 各自的适用边界把WHERE用对是单表查询的核心。三个高频操作符各有各的坑。BETWEEN 是闭区间。WHERE release_year BETWEEN 2018 AND 2021包含 2018 和 2021 两年等价于 2018 AND 2021。这个事实听上去是常识但一旦字段换成DATETIME类型坑立刻出现WHERE release_date BETWEEN 2021-01-01 AND 2021-12-31会漏掉2021-12-31 14:30:00这一天的数据因为2021-12-31被补全成2021-12-31 00:00:00。安全的写法是查左闭右开区间WHERE release_date 2021-01-01 AND release_date 2022-01-01IN 相比一串 OR 更可读也更利于优化器。WHERE genre IN (剧情,悬疑,科幻)和三个OR连写逻辑等价但IN在 MySQL 8.0 里会被优化成更高效的索引查找方式range访问而且当列表元素超过十几个时IN写起来省事得多。需要注意的是IN里可以放子查询但数据量大时IN (子查询)的性能可能远不如EXISTS或 JOIN这一点在第 4 节细说。LIKE 的百分号位置决定能不能走索引。title LIKE 夏日%是前缀匹配能用上索引title LIKE %夏日%是全文模糊匹配索引直接失效得扫全表。这不是说模糊匹配不能用——数据量小的练习表无所谓——但你得知道代价在哪。真实场景里如果确实需要包含查询可以考虑全文索引或者额外的分词检索方案而不是硬用LIKE %...%。还有一组必须单独拎出来说的NULL的所有比较都返回UNKNOWN。WHERE nationality NULL、WHERE nationality NULL都是错的永远查不出数据必须用IS NULL或IS NOT NULL。这条规则在实际项目中造成的 bug 数量我觉得能排进 SQL 问题前三。2.3 排序与分页ORDER BY 的隐含规则和 LIMIT 的边界ORDER BY看着简单但有几个行为不写清楚绝对会误解。MySQL 默认把NULL当最小值升序时排最前、降序时排最后。如果业务上要求空值排最后得显式写ORDER BY rating IS NULL, rating DESC;这里rating IS NULL返回 0 或 1IS NULL的结果是 0非空时排在前面1空时排后面起到了空值沉底的作用。这个技巧在展示类查询里很实用。多列排序是从左到右逐级生效的。ORDER BY genre ASC, rating DESC的含义是先按类型分组排同类型内部再按评分从高到低。千万别理解成两个字段平行排序那是不可能的——排序必须有一个优先级链。分页的LIMIT offset, size在大偏移量下会变慢。LIMIT 100000, 20的执行方式是先扫描并丢弃前 100000 行再取 20 行。数据量一大就是灾难。常见的优化方案是基于游标的分页记住上一页最后一条记录的排序键下次用WHERE id 上次最后值 LIMIT 20把OFFSET彻底绕开。练习阶段可能体会不到差别但知道这个坑存在将来写真实业务代码时就不容易踩。顺便给一个练习里很有用的算式要取第 N 页每页 size 条偏移量offset (N - 1) * size。比如每页 3 条、要第 4 页就是LIMIT 9, 3。3. 聚合与分组票房统计类查询的看家本领3.1 GROUP BY 与聚合函数的搭档关系单表查询筛的是行分组聚合算的是类。票房场景天然适合聚合——按年份算平均票房、按类型算总票房、按导演算作品数全是这个套路。一个最容易出问题的地方是ONLY_FULL_GROUP_BY模式。MySQL 5.7 之后默认开启规则是SELECT列表里出现的非聚合列必须出现在GROUP BY里否则报错。比如-- 报错title 既不是聚合列也不在 GROUP BY 里 SELECT genre, title, AVG(rating) FROM movie GROUP BY genre; -- 正确要么不用 title要么给它一个聚合 SELECT genre, COUNT(*) AS cnt, AVG(rating) AS avg_rating FROM movie GROUP BY genre;有些人图省事直接把自己的 sql_mode 改掉关了这个检查这属于自找麻烦。因为在宽松模式下SELECT genre, title … GROUP BY genre虽然能跑但返回的title是随机某一行结果不可预测——同一句 SQL 换个执行计划结果就变了。老老实实按规则写。聚合函数的 NULL 行为必须记住这是最容易算错的点函数对 NULL 的处理空结果集返回COUNT(*)统计所有行包含 NULL 行0COUNT(col)跳过该列为 NULL 的行0SUM(col)跳过 NULLNULLAVG(col)跳过 NULL分母是非空行数NULLMAX/MIN(col)跳过 NULLNULLGROUP_CONCAT(col)跳过 NULLNULLAVG那个行为特别坑。假设有个演员的birth_year是 NULL当你要算每个年代出生的演员平均出生年份AVG(birth_year)会把这个 NULL 直接忽略掉分母少算一个人。业务上是应该算 0 还是应该排除这得由你来定但数据库默认帮你选了排除你就得知道。COUNT(*)和COUNT(col)的差异在多表连接里会放大成严重错误。像统计每部电影有几位演员参演如果写成SELECT m.movie_id, COUNT(a.actor_id) AS actor_cnt FROM movie m LEFT JOIN movie_actor ma ON m.movie_id ma.movie_id LEFT JOIN actor a ON ma.actor_id a.actor_id GROUP BY m.movie_id;用COUNT(a.actor_id)时没有演员的电影a.actor_id是 NULL计数为 0这恰好是想要的结果。但如果误写成COUNT(*)那部没有演员的电影也会被算成 1因为LEFT JOIN补了一行全 NULL 的记录。这个差异在练习里经常被判错值得反复念叨。3.2 WHERE 和 HAVING 到底谁管什么一句话概括WHERE过滤行HAVING过滤组。执行顺序上WHERE在分组前HAVING在分组后这就决定了它们能用的东西不一样。WHERE里不能写聚合函数因为那时还没分组聚合结果不存在。HAVING里可以写聚合函数也可以写SELECT里起的聚合别名MySQL 允许标准 SQL 不允许但既然用 MySQL 就可以用。看一个票房场景的典型需求找出总票房超过 5 亿、且影片数量不少于 2 部的类型。SELECT m.genre, COUNT(*) AS film_cnt, ROUND(SUM(b.domestic_gross b.overseas_gross), 2) AS total_gross FROM movie m JOIN box_office b ON m.movie_id b.movie_id WHERE m.release_year 2018 GROUP BY m.genre HAVING total_gross 50000 AND film_cnt 2 ORDER BY total_gross DESC;三个过滤层各司其职WHERE m.release_year 2018先砍掉 2018 年之前的数据行级GROUP BY按类型归堆HAVING再筛掉不达标的分组组级。性能上的重要区别能放WHERE的条件绝不要放HAVING。因为WHERE先执行过滤掉的行根本不进入分组计算运算量直接减少而HAVING必须先把所有行分组算完再筛掉整组前面的计算全白做了。这个差别在小表上看不出来几十万行的时候可能就是几百毫秒和几秒的差距。3.3 票房指标计算从原始字段推导业务指标练聚合最好不要只写SUM和COUNT那样太机械。真实业务里的指标几乎都是算出来的动手推导一遍参数理解会深很多。指标一总票房。domestic_gross overseas_gross。别直接假设有total_gross字段表里没有就是没有得自己算。这也是为什么很多练习判题会用总票房超过 X来卡人——考的就是你会不会做字段运算。指标二平均票价。总票房 / tickets_sold。这里有个单位陷阱票房存的是万元人次存的是万人直接除得到的是万元/万人数值上等于元/人恰好是对的。但如果票房按元存、人次按人存那就是元/人也是对的。只有当两边单位不一致一边万元一边人时才会错。所以写之前先在纸上把量纲对一遍万元 / 万人 元/人 ✓ 元 / 人 元/人 ✓ 万元 / 人 万分之一元/人 ✗ 要乘 10000指标三投资回报率。总票房 / budget。这个指标比单纯看票房高低更有信息量——一部 5 亿票房、成本 4 亿的片子远不如一部 1 亿票房、成本 2000 万的片子赚钱。计算时注意budget不能为 0实际写查询要加WHERE budget 0把它排除掉否则要么结果无穷大要么在别的数据库里直接除零报错。指标四单馆产出、场均人次这类需要额外院线数据的指标这个场景里没有但思路可以迁移——所有总产出 / 某类资源数量的比率型指标套路都一样关键是搞清楚分子分母的量纲和口径。把这些指标算出来之后再用ORDER BY排个序就能得到按投资回报率排名按平均票价排名这类很有业务价值的榜单。这类查询几乎就是真实业务报表的雏形。4. 多表连接三张表怎么串才不出错4.1 INNER JOIN 和 LEFT JOIN 的选型逻辑连接类型的选择是查询正确性的第一道关。选错了不会报错只会悄悄地少数据或者多数据比报错更危险。INNER JOIN只保留两边都能匹配上的行。用它来回答有哪些电影同时有票房记录这种问题逻辑上是交集。LEFT JOIN保留左表全部行右表匹配不上就补 NULL。用它来回答列出所有电影及其票房没票房的也要列出来。判断标准其实很简单问自己主表是谁主表的数据要不要全部保留。要保留全部就用LEFT JOIN把主表放左边只要匹配上的就用INNER JOIN。看一个考试里经典对比题查询每部电影的标题、导演和总票房没有票房数据的电影总票房显示为空。SELECT m.title, m.director, b.domestic_gross b.overseas_gross AS total_gross FROM movie m LEFT JOIN box_office b ON m.movie_id b.movie_id;如果把LEFT JOIN换成INNER JOIN没有票房记录的片子就直接从结果里消失了。这两种结果集的区别,取决于题目要求的口径但绝大多数列出全部…的描述都指向LEFT JOIN。一个高频的连锁陷阱LEFT JOIN之后再在WHERE里写右表的过滤条件会导致LEFT JOIN退化成INNER JOIN。比如-- 本意是所有电影票房超过 1 亿的显示金额否则显示空 -- 但这样写没票房的电影全没了 FROM movie m LEFT JOIN box_office b ON m.movie_id b.movie_id WHERE b.domestic_gross 50000;原因还是执行顺序——WHERE在连接之后执行NULL 去和 50000 比较返回UNKNOWN该行被过滤。正确做法是把条件挪到ON里FROM movie m LEFT JOIN box_office b ON m.movie_id b.movie_id AND b.domestic_gross 50000;条件放在ON里是连接时筛选不满足条件的右表行补 NULL 保留左表放在WHERE里是连接后筛选NULL 行被干掉。这一条我认为是 JOIN 部分最值得记住的细节没有之一。4.2 多对多关系一部电影的多位演员怎么展示movie_actor这张表的价值在多对多查询里体现得最明显。题目常问查询参演演员总数超过 2 人的电影。SELECT m.movie_id, m.title, COUNT(ma.actor_id) AS actor_cnt FROM movie m JOIN movie_actor ma ON m.movie_id ma.movie_id GROUP BY m.movie_id, m.title HAVING COUNT(ma.actor_id) 2 ORDER BY actor_cnt DESC;注意GROUP BY里同时写了movie_id和title。技术上只写movie_id就够了因为它是主键函数依赖能推出 titleMySQL 8.0 的ONLY_FULL_GROUP_BY也认这个函数依赖。但显式写出来可读性更好而且换到部分数据库比如某些旧版本就必须要写养成习惯不吃亏。如果题目要求把演员名字横向拼成一列用GROUP_CONCATSELECT m.title, GROUP_CONCAT(a.actor_name ORDER BY a.actor_id SEPARATOR / ) AS cast_list FROM movie m JOIN movie_actor ma ON m.movie_id ma.movie_id JOIN actor a ON ma.actor_id a.actor_id GROUP BY m.movie_id, m.title;GROUP_CONCAT有两个默认值要注意分隔符默认是逗号可以改长度默认限制 1024 字节超了会被静默截断不报错。演员多的片子确实可能超必要时用SET SESSION group_concat_max_len 100000;调大但改之前想清楚是不是真的需要把几十个名字拼一行——展示层做这件事往往更合适。反向查询同样重要查询参演电影数量 ≥ 3 部的演员。把movie_actor和actor连起来按演员分组计数就行逻辑结构完全对称唯一变化是分组维度和连接对象。这类题目练多了会形成肌肉记忆看到每个 X 的 Y 数量条件反射就是按 X 分组对 Y 计数。4.3 子查询、EXISTS 与 JOIN 的等价改写同一道题往往有三种以上写法选哪个不只是风格问题还涉及性能和可读性。拿这个需求举例查询参演过剧情类电影的演员姓名。写法一IN子查询SELECT actor_name FROM actor WHERE actor_id IN ( SELECT ma.actor_id FROM movie_actor ma JOIN movie m ON ma.movie_id m.movie_id WHERE m.genre 剧情 );写法二EXISTSSELECT a.actor_name FROM actor a WHERE EXISTS ( SELECT 1 FROM movie_actor ma JOIN movie m ON ma.movie_id m.movie_id WHERE ma.actor_id a.actor_id AND m.genre 剧情 );写法三JOINDISTINCTSELECT DISTINCT a.actor_name FROM actor a JOIN movie_actor ma ON a.actor_id ma.actor_id JOIN movie m ON ma.movie_id m.movie_id WHERE m.genre 剧情;三者结果一样但有细微差别。IN子查询语义最直观但 MySQL 对它的优化在历史上不太稳定早期版本会把它改写成半连接semi-join某些情况性能反而不如 JOIN。EXISTS是相关子查询外层每行执行一次只要找到匹配就短路返回在子查询结果集很大但外查询结果集较小时表现好。JOIN通常是最稳的但必须加DISTINCT否则一个演员参演多部剧情片就会重复出现。我的实际选择习惯是优先 JOIN语义要求存在性判断且只关心有没有时用 EXISTSIN留给简单的固定列表或者子查询结果集极小的情况。这个偏好没有什么理论绝对性但跟着它走基本不会出大错。5. 典型题型实战从入门题到综合题逐层拆解5.1 单表筛选与排序题入门题通常是这个形态查询 2020 年之后上映、评分不低于 8.0 的电影按评分降序排列评分相同的按上映年份升序排列只取前 5 条。SELECT title, release_year, genre, director, rating FROM movie WHERE release_year 2020 AND rating 8.0 ORDER BY rating DESC, release_year ASC LIMIT 5;这种题看着简单但判题系统往往卡细节字段顺序、别名拼写、大小写、排序方向。我见过有人把ORDER BY rating DESC, release_year ASC写成ORDER BY rating, release_year DESC结果列表整体不对。多字段排序时每个字段后面的方向修饰必须紧跟自己那个字段不能想当然地认为一个关键字管全局。另外注意release_year 2020和 2020的区别。2020 年之后是严格大于包含 2020 年得用。中文题干里以上/以后/之后在计算机语境里的边界含义并不统一遇到模棱两可的先用SELECT把临界数据查出来看看业务上合不合理比瞎猜强。5.2 分组统计与排名题进阶题会叠加聚合和排序统计每个类型电影的平均评分和总票房只统计票房记录存在的电影按总票房降序排列显示前三名。SELECT m.genre, COUNT(*) AS film_cnt, ROUND(AVG(m.rating), 2) AS avg_rating, ROUND(SUM(b.domestic_gross b.overseas_gross), 2) AS total_gross FROM movie m JOIN box_office b ON m.movie_id b.movie_id GROUP BY m.genre ORDER BY total_gross DESC LIMIT 3;拆解一下这道题的关键决策点。为什么用JOIN而不是LEFT JOIN题干写了只统计票房记录存在的电影这是明确的交集语义JOIN正确。如果题干是统计每个类型没有票房的按 0 计算那就必须LEFT JOIN加上IFNULL或者COALESCE兜底。为什么AVG要包ROUNDAVG出来的结果是DECIMAL位数可能很长判题时字符串比对会挂。养成对浮点或精度类结果做四舍五入的习惯一般在 1 到 2 位小数之间具体看题干要求。ORDER BY用别名还是表达式这里用别名total_gross没问题因为ORDER BY在SELECT之后执行。但如果同一句还要在HAVING里过滤这个值HAVING也能用别名MySQL 特性而WHERE不行——还是那句老话看执行顺序。5.3 多表综合题一次查询回答多个业务问题综合题的典型形态查询参演电影数不少于 2 部、且参演电影平均评分超过 8.0 的演员显示演员姓名、参演电影数、平均评分按平均评分降序。SELECT a.actor_name, COUNT(DISTINCT ma.movie_id) AS film_cnt, ROUND(AVG(m.rating), 2) AS avg_rating FROM actor a JOIN movie_actor ma ON a.actor_id ma.actor_id JOIN movie m ON ma.movie_id m.movie_id GROUP BY a.actor_id, a.actor_name HAVING film_cnt 2 AND avg_rating 8.0 ORDER BY avg_rating DESC;这道题有三个值得说的细节。第一COUNT(DISTINCT ma.movie_id)里的DISTINCT是不是多余在这个具体场景下确实多余因为movie_actor的主键是(movie_id, actor_id)同一个演员在同一部片子里不可能出现两行。但如果你在查询里还JOIN了别的表比如票房表那一个演员在多部电影、每部电影一条票房记录COUNT就可能重复计数。加上DISTINCT是低成本的保险代价几乎为零收益是结果一定对我倾向于加上。第二GROUP BY里带上a.actor_name。虽然a.actor_id是主键能函数依赖推出actor_name但显式列出来更直观也避免不同版本行为差异。第三HAVING里直接用了两个别名film_cnt和avg_rating。这是 MySQL 的便利特性标准 SQL 不允许但既然在用 MySQL 就不用纠结。如果你写的脚本将来要迁移到别的数据库老老实实把聚合表达式重复一遍。5.4 用窗口函数处理每组取第一名如果有查询每个类型评分最高的那部电影这类需求传统写法定是子查询配MAXSELECT m.genre, m.title, m.rating FROM movie m JOIN ( SELECT genre, MAX(rating) AS max_rating FROM movie GROUP BY genre ) t ON m.genre t.genre AND m.rating t.max_rating;这个写法的隐患是如果同一类型里有两部电影评分相同且都是最高两部都会被选出来。有时候这是期望行为并列第一都展示有时候不是只要一条。另外如果最高分有 5 部并列而业务只要 1 部还得加额外的去重逻辑。MySQL 8.0 之后可以用窗口函数写得干净利落SELECT genre, title, rating FROM ( SELECT genre, title, rating, ROW_NUMBER() OVER (PARTITION BY genre ORDER BY rating DESC) AS rn FROM movie ) t WHERE rn 1;ROW_NUMBER()保证每组只出一行RANK()会把并列的都留下下一名跳号DENSE_RANK()并列的都留下下一名不跳号。三个函数的选择取决于业务口径要唯一代表用ROW_NUMBER要并列第一都算用RANK或DENSE_RANK二者区别在于并列之后的编号是否连续。这个区别在奖金分配、等级评定这类场景里会造成实际差异不是学术争论。窗口函数不能直接写在WHERE里因为WHERE执行早于窗口函数计算必须套一层子查询或者 CTE这一点和聚合函数别名不能用在WHERE是同一个道理。6. 报错与结果不符的排查实录6.1 高频报错速查表练习时遇到的错误大致就这几类整理成表方便对号入座报错信息关键词常见原因处理方式Unknown column x字段名拼错 / 别名在 WHERE 中使用检查拼写别名移到 ORDER BY/HAVINGColumn x in field list is ambiguous多表连接时同名字段没加表前缀加m./a.前缀In aggregated query without GROUP BYSELECT 里有非聚合列但没分组加GROUP BY或给该列套聚合函数Subquery returns more than 1 row用接了返回多行的子查询改用IN或加LIMIT 1You cant specify target table for update in FROM clause子查询里引用了正在查询的同一张表套一层派生表包住Data truncated for column插入值超出字段长度或类型不符检查字段定义和插入值结果为空但表里有数据NULL用了比较 / 字符集或空格问题改用IS NULL用TRIM排查空格ambiguous字段歧义这个错误在电影场景里出现频率极高因为movie和box_office都有movie_idmovie和movie_actor也都有。只要连接两张表凡是同名字段就必须带前缀。有人图省事只在 SELECT 里加前缀WHERE 和 ON 里不加照样报错——规则是全句生效的不是只看 SELECT 那一块。Subquery returns more than 1 row也很典型。比如查询和《长风渡月》同导演的电影-- 错误示范 SELECT title FROM movie WHERE director (SELECT director FROM movie WHERE title 长风渡月);如果同名的电影有两部子查询返回两行直接炸。哪怕数据里只有一部这种写法也是脆弱的。正确姿势是INSELECT title FROM movie WHERE director IN (SELECT director FROM movie WHERE title 长风渡月) AND title 长风渡月;那个AND title 长风渡月别漏了否则结果里会包含它自己——这是另一个结果多了一条的常见来源。6.2 不报错但结果不对的五步排查法最难受的情况是 SQL 跑通了、有结果、但和预期不符。这种情况我一般按下面五步走基本都能定位。第一步把SELECT换回SELECT *并去掉所有聚合。先看一眼原始连接结果长什么样。很多数字对不上的问题在这一步就能看到原因——比如本该一对一的连接实际出来了一对多行数被放大了。第二步数行数。SELECT COUNT(*)对比各表数据量看看连接之后的行数是变多了还是变少了。变多通常是漏了连接条件或者连接条件不唯一笛卡尔积变少通常是INNER JOIN丢数据或者WHERE条件误杀。第三步检查NULL。把可疑字段单独SELECT出来用IS NULL数一下有多少空值。空值参与计算的结果几乎总是反直觉的。第四步检查GROUP BY的粒度。分组字段是不是漏了比如按电影统计演员数却只GROUP BY genre那统计的其实是每个类型的演员数。分组粒度和业务问题的粒度必须严格对齐。第五步用已知答案的小数据集验证。把范围缩到一部电影、一个演员手工算一遍期望值再和 SQL 结果对。范围缩小之后问题往往一眼可见。这五步里第一步是最有效的我把这条当铁律用。真实工作里遇到复杂的多表查询对不上数全都是从把中间结果摊开看这一招开始定位的。6.3 几个我踩过的具体坑和避坑技巧坑一ORDER BY遇到中文排序不符合预期。默认排序规则下中文按编码排结果和拼音顺序完全不同。如果业务要求按拼音排序得指定COLLATE或者加一个拼音字段。练习里一般不考但真实报表里是刚需。坑二DECIMAL参与除法后精度丢失。SELECT budget / tickets_sold FROM box_office返回的小数位数由除法规则决定可能和你预期的不一样。要控制精度显式CAST或ROUND比如ROUND(budget / tickets_sold, 2)。另外除法要注意分母为 0虽然 MySQL 的/遇 0 返回NULL不报错但结果变成 NULL 会让后续计算全部塌掉。坑三LIMIT和ORDER BY的顺序不能颠倒。LIMIT 5 ORDER BY rating DESC是语法错误必须ORDER BY在前。而且如果只写LIMIT不写ORDER BY取到的前 5 条顺序是不确定的每次执行可能不一样。凡是要前 N 条ORDER BY必须写全把并列情况也定下来否则就是赌博。坑四字符串比较默认不区分大小写。MySQL 默认排序规则下abc ABC返回 1这是很多人意想不到的。如果业务要求严格区分得用BINARY或者指定_bin结尾的排序规则。坑五COUNT(*)的统计口径。前面提过再强调一遍COUNT(*)数的是行数不是某个字段的非空值数量。多表连接后一行的实际含义可能已经不是一部电影而是一条电影-演员关系此时COUNT(*)数出来的是关系数COUNT(DISTINCT movie_id)才是电影数。这个区别在写统计报表的时候至关重要。坑六别在练习表上迷信执行计划。十几行数据的表MySQL 优化器有一半时间直接全表扫描因为你加了索引它也不用——数据量太小扫表比走索引还快。这时候纠结EXPLAIN的type字段没意义。想验证索引效果得自己造几万行数据再看。知道这一点能省下不少对着小表EXPLAIN自我怀疑的时间。我自己练这套东西的时候有个习惯每写出一条能跑的查询就故意改坏它一次——把LEFT JOIN换成INNER JOIN把WHERE里的条件挪到ON把COUNT(*)换成COUNT(col)然后看结果怎么变。改坏一次对这个语法的理解就深一层比看书上十行解释管用得多。这套电影、演员、票房的数据查询真正值钱的不是把题做对而是通过一次次故意改错把SELECT的执行顺序、JOIN的保留逻辑、聚合的 NULL 行为刻进脑子里。这几个点通了换成电商订单、社交关注、物流运单套路是一模一样的。