MySQL SQL练习题实战：从单表查询到窗口函数

做练习之前先想清楚一个问题你练SQL到底是为了什么我见过不少人刷题刷得飞起一进公司写报表还是把三个临时表拖出来一顿join完事。道理很简单——光知道语法没用的你得把每个SQL都当成解决一个真实业务问题来看。这篇MySQL SQL练习题定位很清楚不搞偏题怪题全部是工作中天天碰得到的场景比如算平均分、查缺考、找各科前三、统计累计值。文章按难易程度递进从单表查询写到多表连接再从子查询写到窗口函数四段走完你的SQL水平基本能上一个台阶。新手拿它练手社招准备面试也能拿它查漏补缺。这套题我用的是经典的“学生-课程-成绩”三表结构你听着可能觉得老气但它确实覆盖了80%以上的常用查询场景。无论你以后是做电商订单分析、用户增长统计还是运营报表核心逻辑都能落到这几张表上。建议你先别急着看答案自己动手敲一遍再对着解析看思路差在哪里这样收获才最大。1. 练习环境准备与表结构设计1.1 三张业务表的设计思路先建表。很多人喜欢直接下载现成的练习数据库但我建议你自己敲一遍建表语句。建表本身就是一次很好的DDL练习而且你亲手建的表字段含义你心里有数后面写查询时脑子里自动就有数据分布的画面。这里用三张表学生表、课程表、成绩表。学生表和课程表就是基础资料成绩表是中间关系表。为什么要拆三张而不是全塞一张大宽表因为真实业务里学生信息和课程信息是各自独立维护的成绩是它们的关联结果。拆开之后为了查一个完整的信息你就不得不写join这正是练习的目的。CREATE TABLE student ( s_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 学生编号, s_name VARCHAR(20) NOT NULL COMMENT 姓名, s_class VARCHAR(20) COMMENT 班级, s_birth DATE COMMENT 出生日期, s_sex CHAR(1) COMMENT 性别 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生表; CREATE TABLE course ( c_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 课程编号, c_name VARCHAR(50) NOT NULL COMMENT 课程名称, c_teacher VARCHAR(20) COMMENT 授课教师, c_score DECIMAL(4,1) COMMENT 课程学分 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT课程表; CREATE TABLE score ( s_id INT NOT NULL COMMENT 学生编号, c_id INT NOT NULL COMMENT 课程编号, score DECIMAL(5,2) COMMENT 考试成绩, PRIMARY KEY (s_id, c_id), CONSTRAINT fk_student FOREIGN KEY (s_id) REFERENCES student(s_id), CONSTRAINT fk_course FOREIGN KEY (c_id) REFERENCES course(c_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT成绩表;建表时我故意留了几个值得琢磨的点。成绩表的主键用的是联合主键(s_id, c_id)这保证了同一学生同一课程只能有一条成绩记录从表结构层面杜绝了数据重复。外键约束注释里我保留着实际练习场景如果你嫌麻烦可以去掉外键先只保留联合主键但主键建议一定留着。字段类型上score用DECIMAL(5,2)因为成绩这种数据绝不能存成FLOAT或DOUBLE浮点数精度问题在金融和教育等精确场景是硬伤。见过不少人在这个细节上吃过亏成绩本来85分存完查出来变成84.9999999初学者心态直接崩掉。1.2 测试数据的填充技巧表建好之后要灌数据这里插数据也是有讲究的。我在测试数据里特意埋了几个“坑”一是有学生缺考成绩表里根本没记录不是NULL而是不存在二是有两位学生总分一模一样用来测排名函数的区分度三是某个班级只有一个人某些统计口径下会特别容易算错。INSERT INTO student (s_name, s_class, s_birth, s_sex) VALUES (张三, 一班, 2002-05-01, 男), (李四, 一班, 2002-08-12, 女), (王五, 二班, 2003-01-20, 男), (赵六, 二班, 2002-11-03, 女), (孙七, 二班, 2003-06-15, 男), (周八, 三班, 2001-12-30, 男), (吴九, 三班, 2002-03-22, 女), (郑十, 三班, 2003-02-14, 女); INSERT INTO course (c_name, c_teacher, c_score) VALUES (MySQL数据库, 陈老师, 3.5), (Python程序设计, 林老师, 4.0), (数据结构, 刘老师, 4.5), (计算机网络, 黄老师, 2.5); INSERT INTO score (s_id, c_id, score) VALUES (1, 1, 88.5), (1, 2, 76), (1, 3, 92), (1, 4, NULL), (2, 1, 91), (2, 2, 85), (2, 3, 88), (2, 4, 79), (3, 1, 65), (3, 2, 59), (3, 3, 72), (4, 1, 78.5), (4, 2, 82), (4, 3, 66), (4, 4, 90), (5, 1, 91), (5, 2, 88), (5, 3, 85), (5, 4, 87), (6, 1, 45), (6, 2, 63), (6, 3, 53), (7, 1, 82), (7, 2, 77), (7, 3, 95.5), (7, 4, 69), (8, 1, 72), (8, 2, 81.5), (8, 3, NULL);数据量不用太大8个学生、4门课、28条成绩记录已经足够支撑几十道练习。关键是数据里要有辨识度比如张三的计算机网络成绩是NULL王五和赵六的课程缺了一门完全没记录郑十的数据结构是NULL吴九的网络没考。每个NULL、每条缺失记录背后都对应一个真实业务语义后面写查询的时候你就知道为什么同样一个结果有的同学写对了有的就偏了。2. 单表查询基础题从取数到统计2.1 基础筛选与排序的三道送分题先来几道热身的别嫌简单后面所有复杂查询都是这些基础语法组合出来的。题目1查询一班的所有学生要求按照出生日期从早到晚排序。SELECT s_name, s_birth FROM student WHERE s_class 一班 ORDER BY s_birth ASC;这道题就考两个动作条件筛选和排序。WHERE筛选在前ORDER BY排序在后执行顺序上先有结果集再谈顺序。实际业务中“从早到晚”就是时间正序记住ASC可以省略但建议写清楚代码可读性比少敲两个字重要。题目2查询平均分大于等于80分的学生输出学号和平均分按平均分降序排列。SELECT s_id, AVG(score) AS avg_score FROM score GROUP BY s_id HAVING AVG(score) 80 ORDER BY avg_score DESC;这道题虽然也在单表范围内但已经涉及了一个关键概念AVG会忽略NULL值。张三的网络课成绩是NULL如果按照四门课算平均缺考计0分他的平均分应该是64.125缺考按NULL处理的话三门课平均是85.5。两种口径结果天差地别写SQL前一定要先和业务确认缺考到底算0还是不算这里按常规做法AVG自动忽略NULL值所以张三算的是85.5。WHERE和HAVING的区别也是这道题的重点。WHERE只能过滤原始行不能直接写聚合函数HAVING专门用来过滤分组后的聚合结果。为什么因为SQL执行顺序里WHERE在GROUP BY之前执行到WHERE那一步时AVG(score)根本还没算出来。这个执行顺序你要是记不住遇到复杂查询分析错误时会非常痛苦。题目3统计各班的总人数和男生人数。SELECT s_class, COUNT(*) AS total_cnt, SUM(CASE WHEN s_sex 男 THEN 1 ELSE 0 END) AS male_cnt FROM student GROUP BY s_class;这道题的考点是把行转成列也就是条件聚合。SUM结合CASE WHEN是行转列的基础思路比单独用COUNT加上WHERE再通过两次查询合并要优雅得多一次扫描就把两类统计都算完了。老手和新手的区别往往就体现在这种细节上前者习惯一次扫描解决多个统计维度。2.2 聚合函数与分组统计的隐藏细节题目4查询每个学生选修了几门课输出学号和选课数并筛选出选课数为3门及以上的学生。SELECT s_id, COUNT(c_id) AS course_cnt FROM score GROUP BY s_id HAVING COUNT(c_id) 3;这里要注意COUNT(c_id)和COUNT()的差别。COUNT()统计的是行数只要行存在就算COUNT(c_id)统计的是c_id非NULL的行数。因为在成绩表中c_id是主键组成部分不可能为NULL所以这题两者结果一致。但在其他场景里比如统计“有效成绩个数”就必须用COUNT(score)因为NULL分数不应该被计数。这是SQL新手踩坑重灾区不知道COUNT的NULL行为统计出来的数字永远对不上。题目5查询每门课程的最高分、最低分、平均分和考试人数。SELECT c_id, MAX(score) AS max_score, MIN(score) AS min_score, ROUND(AVG(score), 2) AS avg_score, COUNT(score) AS exam_cnt FROM score GROUP BY c_id;考试人数用COUNT(score)而不是COUNT(*)因为这题的业务口径就是“有成绩的人”缺考的行不算。ROUND保留两位小数让报表看起来更干净。这个“取数口径”的问题在实际开发中特别常见同样一张报表运营同事和产品经理统计的口径不一样数据就对不齐。你在练习时就要养成先明确口径的习惯——统计的是“记录数”还是“非NULL数”这直接决定你用哪个COUNT。3. 多表连接JOIN就是业务关联的桥梁3.1 内连接与左右连接的选型逻辑单表查询练熟之后连接查询是必须迈过的一道坎。你在真实业务里几乎没有只查一张表的场景订单要关联用户商品要关联品类内容要关联作者。连接的本质就是一个“查字典”的过程——主表里有个编号字段你拿着它去另一张表找对应的详细描述。题目6查询所有学生的学号、姓名及其中MySQL数据库课程的成绩。SELECT s.s_id, s.s_name, sc.score FROM student s LEFT JOIN score sc ON s.s_id sc.s_id AND sc.c_id 1 ORDER BY s.s_id;这里用的是LEFT JOIN而不是INNER JOIN并且把课程筛选条件放进了ON而不是WHERE这两个都是故意设计的。如果用INNER JOIN查询结果会少一条——因为有一个学生压根没选这门课内连接按匹配条件会把没匹配上的学生直接丢掉。而业务上你希望看到的是“所有人不管有没有成绩”必须用LEFT JOIN保住左表全部数据。为什么筛选条件要在ON里而不是WHERE里因为在LEFT JOIN语义下WHERE sc.c_id 1会把右表为NULL的扩展行全部过滤掉效果等于内连接。这是一个很经典的坑初学阶段经常发现改了WHERE之后数据少了几行其实不是数据问题是连接语义被WHERE悄悄改变了。候补如果想要连学生姓名和课程名都能显示出来顺便把INNER JOIN和LEFT JOIN的差别在结果里体会一下可以试试下面这个两表连接版本。SELECT s.s_name, c.c_name, sc.score FROM score sc JOIN student s ON sc.s_id s.s_id JOIN course c ON sc.c_id c.c_id WHERE sc.c_id 1;这个版本里JOIN顺序是先成绩后学生再课程SQL优化器一般会自己调整连接顺序你不需要太操心性能问题。关键是理解多表连接实际上是两个两表连接的组合成绩表和学生对一次得到的中间结果再和课程表对一次。3.2 多表连接的综合查询实战题目7查询每个学生选修的全部课程及成绩要求输出姓名、课程名、成绩并按照姓名字典序和成绩降序排列。SELECT s.s_name, c.c_name, sc.score FROM score sc INNER JOIN student s ON sc.s_id s.s_id INNER JOIN course c ON sc.c_id c.c_id ORDER BY s.s_name ASC, sc.score DESC;这道题本质上是一个“明细报表”是日常开发里最常写的查询类型。INNER JOIN在这里没毛病因为你要的是一个“上过课并且有成绩”的有效明细缺考的学生本来就不该出现在结果里。多个字段排序在ORDER BY里用逗号分隔先按姓名排姓名相同的再按成绩降序排。写复杂报表的时候排序字段的顺序至关重要经常出现“数据对但顺序不对”的返工根本原因就是排序字段优先级写反了。题目8查询一班没有选修任何课程的学生。也即“差集”问题。SELECT s_id, s_name FROM student WHERE s_class 一班 AND s_id NOT IN ( SELECT DISTINCT s_id FROM score );这道题用子查询找所有有过成绩记录的学生编号然后主查询反向排除。NOT IN的写法简单直观但后面会提到它有一个NULL相关的致命陷阱如果你希望一开始就避开那个坑可以直接用NOT EXISTS版本SELECT s.s_id, s.s_name FROM student s WHERE s.s_class 一班 AND NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.s_id s.s_id );两个版本在这个数据分布下结果一致但写法思路完全不同NOT IN是集合逻辑NOT EXISTS是逐行验证。解题的时候两种思路都值得练一练后面章节会专门分析为什么线上环境里我推荐NOT EXISTS更多一些。4. 子查询与高级条件写出业务的“如果则”4.1 标量子查询与WHERE嵌套子查询本质上是把业务逻辑写成分步骤的思维先查出一个结果再拿这个结果作为条件继续查。现实中“查所有分数高于平均分的记录”“查比同班所有人年龄都小的学生”“查每个类目下销售额最高的商品”等需求天然就适合子查询。题目9查询每门课程中成绩高于该课程平均分的学生及其成绩。SELECT sc.s_id, sc.c_id, sc.score, avg_t.avg_score FROM score sc JOIN ( SELECT c_id, AVG(score) AS avg_score FROM score GROUP BY c_id ) avg_t ON sc.c_id avg_t.c_id WHERE sc.score avg_t.avg_score;这道题的思路是先用子查询构建一张“课程平均分临时表”再把它和成绩表按课程关联起来最后用WHERE做逐行比较。这里特别注意子查询产生的平均分在JOIN之后就不能用AVG(sc.score)来比较了因为一旦GROUP BY c_id行粒度已经变成每门课一行不是每个学生一行了。把大问题拆成“先聚合再关联”是SQL开发最常用的套路记不住SQL关键词没关系这个思路远比语法重要。题目10查询没有选修数据结构课程的所有学生姓名。SELECT s_name FROM student WHERE s_id NOT IN ( SELECT s_id FROM score WHERE c_id (SELECT c_id FROM course WHERE c_name 数据结构) );这道题嵌套了两层子查询先在课程表里查数据结构对应的c_id再根据这个c_id找选修过该课程的学生ID列表最后外层排除。写多层嵌套的时候最容易出错的是括号匹配和子查询的返回列数建议你从最内层开始逐层验证每一层的查询先单独跑一遍。4.2 EXISTS与IN的取舍以及NULL陷阱题目11查询没有任何一门课程不及格的学生所有成绩大于等于60分输出学生姓名。SELECT s.s_id, s.s_name FROM student s WHERE NOT EXISTS ( SELECT 1 FROM score sc WHERE sc.s_id s.s_id AND sc.score 60 );这道题里NOT EXISTS的思路是典型的“找反例”逻辑正面直接查“所有成绩都及格”不太好写那就反着来先找出有不及格记录的学生再把这些人排除掉。有任何一个成绩低于60子查询就返回一条记录NOT EXISTS就把他过滤了完全没有低分记录子查询返回空集主查询就保留他。为什么在这里不用NOT IN回到前面埋下的那个坑如果子查询结果里包含NULL值NOT IN的行为会变得非常危险——比如子查询返回(59, NULL)NOT IN会判断“NULL是否不等于59”在SQL的三值逻辑里和NULL比较的结果既不是TRUE也不是FALSE而是UNKNOWNWHERE只保留TRUE的记录于是整张表都会被过滤掉。这是线上SQL事故级别的坑我见过不止一次因为某个统计表里有NULL导致查询结果莫名全空。IN本质上是多个OR条件的缩写只要有一个分支和NULL比较整个条件就变UNKNOWN。NOT EXISTS是逐行相关的NULL只会让该行的[EXISTS判断]返回FALSE并不会波及整表。稳妥起见只要子查询结果可能含NULL我建议一律用NOT EXISTS或者提前用IS NOT NULL把NULL过滤干净。4.3 窗口函数登场前的最后一道综合题题目12查询每个学生总成绩的排名输出名次、学号、姓名和总成绩。SELECT RANK() OVER (ORDER BY total_score DESC) AS rank_no, t.s_id, s.s_name, t.total_score FROM ( SELECT s_id, SUM(score) AS total_score FROM score GROUP BY s_id ) t JOIN student s ON t.s_id s.s_id ORDER BY rank_no;这里先用子查询算每个学生总分RANK() OVER窗口函数再对总分做排序。RANK和普通ORDER BY的区别就在于它能返回一个名次编号方便做“第几名”“前几名”的分析。窗口函数的话题下一节专门讲这里先打个样。5. 窗口函数分组排名的现代解法5.1 三种排名函数怎么选窗口函数是MySQL 8.0之后的必修课排名、累计、同比环比都靠它。最常用的三个排名函数是ROW_NUMBER、RANK和DENSE_RANK三者最大的区别在于对并列名次的处理方式。ROW_NUMBER不管成绩是否相同强制给每一行编一个连续不重复的序号适合“取前三条”这种场景。RANK成绩相同并列同一名次但下一个名次会跳过比如并列第二之后直接是第四。DENSE_RANK成绩相同并列同一名次下一个名次不跳过并列第二之后就是第三。打个比方三个人分数分别是95、90、90地排在第一名和第二名之后先说三个人排第一分数95再说两人并列第二分数90最后一人分数88。ROW_NUMBER的结果是1、2、3RANK的结果是1、2、2、4DENSE_RANK的结果是1、2、2、3。实际业务里“并列第二之后到底算第四还是第三”纯看需求拿不准就问业务方。老版本MySQL没有窗口函数排名只能靠变量模拟MySQL 8.0已经全面支持窗口函数了能用窗口函数就不要再手动搞变量那套东西维护成本天差地别。5.2 每科前三名的经典写法题目13查询每门课程成绩排名前三的学生学号、课程号、成绩和排名。SELECT c_id, s_id, score, ranking FROM ( SELECT c_id, s_id, score, DENSE_RANK() OVER (PARTITION BY c_id ORDER BY score DESC) AS ranking FROM score ) t WHERE ranking 3 ORDER BY c_id ASC, ranking ASC;这道题有两个关键点。一是PARTITION BY c_id意思是把数据按课程切开在每个切片区里独立排名。类比一下就是每个班独立排名次而不是所有班放在一起排名如果漏掉PARTITION BY你会得到全局排名业务上就完全错了。二是排名是在子查询里算出来的外层过滤排名小于等于3这是“先窗口后过滤”的标准姿势因为WHERE的执行顺序决定了它不能直接用窗口函数的别名作为过滤条件必须在外面包一层子查询。有些同学会问为什么不能直接WHERE ranking 3原因是WHERE的执行时机在SELECT投影之前窗口函数是在SELECT阶段计算的你在WHERE里根本访问不到这个还不存在的列。想过滤必须有子查询生成的临时结果再在外面套一层过滤。再看一个窗口函数的进阶用法累计总和。题目14查询每个学生的成绩明细并额外计算该生所有课程成绩的累计总和。SELECT s.s_name, c.c_name, sc.score, SUM(sc.score) OVER (PARTITION BY sc.s_id ORDER BY sc.c_id) AS cumulative_sum FROM score sc JOIN student s ON sc.s_id s.s_id JOIN course c ON sc.c_id c.c_id ORDER BY sc.s_id, sc.c_id;这个累计总和用的是带ORDER BY的窗口SUM它把同一学生的成绩按课程编号排序逐行累加出一个累计值。每扫到一行就把前面的成绩加进来。如果去掉ORDER BYSUM就会变成全部分区的汇总值而不是累计值。这个写法做逐月累计消费、累计销售额特别常用理解了它能省掉很多自连接。6. 练习踩坑与排查经验6.1 五个必踩的坑练题练得多了你会发现错误翻来覆去就那么几类。我复盘了一下自己包括身边同事踩过的坑最典型的有五个。第一GROUP BY漏列。SELECT里出现了一个非聚合字段但GROUP BY里没带上它MySQL 5.7及以下版本默认宽松模式不报错但结果完全不可控8.0版本默认开启ONLY_FULL_GROUP_BY才会直接报错。以前就有人在这种模式下写“SELECT s_name, s_class, AVG(score) FROM score GROUP BY s_class”结果s_name取的是组内随机一条运气好看着对运气差就是肉眼难查的脏数据。第二WHERE和HAVING混用导致语义错误。聚合前的条件放在WHERE聚合后的条件放在HAVING两者互换基本上会直接报错或者结果不对。第三COUNT写的时机不对。前面反复强调过COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)三个写法完全是三种统计口径写报表之前先问自己一句我要数的是行数还是非空值数量还是去重后的非空值数量第四NULL值参与运算的意外结果。NULL和任何数字做加减乘除结果都是NULL。比如算总成绩的时候如果直接用score字段相加某一个NULL会让整条记录的总分变成NULL而不是忽略它。遇到这种情况得用IFNULL(score,0)或者只在业务允许时忽略NULL行。第五等值判断写成了单等号。这个主要是新手刚转过来时候容易犯的错。SQL中的判断相等就是单等号赋值操作在UPDATE语句里才是等号。真遇到过写条件时用了双等号的MySQL直接把SQL当作语法错误抛出来倒不算隐蔽错误但经常刷题的话还是会碰到。6.2 排查SQL错误的方法论遇到查询结果不对别急着改SQL先按“拆-查-比”三步走。拆把复杂查询拆成最小单元。比如一个三层嵌套的子查询先把最内层单独跑一遍看它的结果是否符合预期。你可以顺手给每个子查询加一个能读懂的别名这样调试时心里清清楚楚哪一段对应哪个结果。查确认每一步的“数据输入”。子查询结果不确定时在子查询末尾临时去掉过滤条件输出原始明细人肉核对几条数据是否符合预期。SQL不报错不代表正确经常是执行成功了但逻辑和业务口径差了十万八千里。比写两个不同思路的SQL对照结果。比如排名题先用手工排序的结果去验证窗口函数的结果再算一遍分组求和验证窗口SUM的结果。如果两个不同实现方法的结果一致出错的概率就大大降低了。练习的时候还有个好习惯每次跑完查询盯着结果集看一遍不要直接复制粘贴就完事。某一行数据离奇地大或小你马上能感觉出来这种感觉就是数据敏感度很多老手写SQL速度快、正确率高靠的就是这种敏感度。它是靠大量练习喂出来的没有捷径。这套题练完我的建议是趁热打铁把你手头真实业务里的报表需求拿出来用这套思路重新写一遍别管写得慢只要能把之前“拖三个临时表到Excel里处理”的过程变成一个SQL搞定说明你对连接、聚合、窗口函数这些核心概念已经是真入门了。后面再碰到查询需求你会发现基本就是加条件、换维度、调排序的排列组合变化再多底层逻辑都是这几张大表和小表之间的关联关系。