mysql练习_呕心吐血之作-Mysql经典练习题的答案以及解析

相信大多学习了Mysql数据库语言的同学都会上网找练习来练手，而大部分的人肯定知道有一篇Mysql经典练习题50题的帖子，上面的题目基本上涵盖了Mysql查询语句的关键知识点，我前前后后刷了也有三四遍，所以就针对这50道经典题中稍微有些难度的题进行解析，把知识分享给大家的同时自己也做一个复习吧，首先先奉上这篇帖子的链接

https://blog.csdn.net/flycat296/article/details/63681089​blog.csdn.net

这篇帖子的名字叫“超经典SQL练习题，做完这些你的SQL就过关了”，名字确实比较撩人哈哈哈，还有如果你们还没做过这组题目的话建议先刷一遍题目再来看我的帖子哈，我先声明，我不是每一个题目都解析的，因为有些确实很基础，我就不浪费时间了，但是稍微要费一点点脑的题目我也是会讲的，下面进入正题哈。

首先我给到你们数据，为了方便你们看懂我把原来的代码里的“#”都改为了“id”

学生表 Student

Sid 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

create table Student(Sid varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')

科目表 Course

Cid 课程编号,Cname 课程名称,Tid 教师编号

create table Course(Cid varchar(10),Cname nvarchar(10),Tid varchar(10))
insert into Course values('01' , '语文' , '02')
insert into Course values('02' , '数学' , '01')
insert into Course values('03' , '英语' , '03')

教师表 Teacher

Tid 教师编号,Tname 教师姓名

create table Teacher(T# varchar(10),Tname nvarchar(10))
insert into Teacher values('01' , '张三')
insert into Teacher values('02' , '李四')
insert into Teacher values('03' , '王五')

成绩表 SC

Sid 学生编号,Cid 课程编号,score 分数

create table SC(Sid varchar(10),Cid varchar(10),score decimal(18,1))
insert into SC values('01' , '01' , 80)
insert into SC values('01' , '02' , 90)
insert into SC values('01' , '03' , 99)
insert into SC values('02' , '01' , 70)
insert into SC values('02' , '02' , 60)
insert into SC values('02' , '03' , 80)
insert into SC values('03' , '01' , 80)
insert into SC values('03' , '02' , 80)
insert into SC values('03' , '03' , 80)
insert into SC values('04' , '01' , 50)
insert into SC values('04' , '02' , 30)
insert into SC values('04' , '03' , 20)
insert into SC values('05' , '01' , 76)
insert into SC values('05' , '02' , 87)
insert into SC values('06' , '01' , 31)
insert into SC values('06' , '03' , 34)
insert into SC values('07' , '02' , 89)
insert into SC values('07' , '03' , 98)

下面我就开始解题了

难题1：查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

此题我们得先把成绩表（sc）里不同的学生号（sid）的课程01（cid=01）的分数（score）和课程02（cid=02）的分数（score）分别拎出来，然后通过join进行一个内连接，接着使用where子句把课程02分数高于课程02分数的学号给筛选出来，然后把筛选出来的学生号再和学生表（student）里的学生号进行一个匹配，把对应的学生信息找到就可以了，这题主要还是检测你对inner join on的理解程度，另外记得给每一个筛选出来的子表格起一个别名（alias），否则后面饮用的时候系统会不知道该引用哪个表的列，代码如下（答案非唯一，请多多思考是否有其他方式），仅供参考：

select a.*, b.*, c.* from 
(select * from sc where Cid = '01') a
left join (select * from sc where Cid = '02') b
on a.Sid = b.Sid
left join student c
on a.Sid = c.Sid
where a.score > b.score;

难题2：查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

这题乍一看其实很简单，但是粗心的同学很可能会在这里栽跟头，因为我们常常直接使用join就把两个子表格连接了起来，但是join等同于inner join，其作用是内连接，而题干的要求是“存在'01'课程但可能不存在'02' ”，所以这个时候我们就得用到left outer join，当然也可以写成left join。其他的跟上一题类似我们分别把成绩表（sc）里的课程01和课程02分别拎出来通过left join连接，记住，如果课程01在join前面的话就用left join，如果课程01在后面的话就得用right join了，代码如下，仅供参考：

select a.*, b.* from
(select * from sc where Cid = '01') a
left join
(select * from sc where Cid = '02') b
on a.Sid = b.Sid;

难题3：查询学过「张三」老师授课的同学的信息

其实这题并不算难，只不过涉及多层嵌套，所以也拿出讲一下，大致的解题思路是，我们要找到学习过张三老师授课的学生，就要在教师表（teacher）内找到张三老师对应的教师编号（tid），然后在课程表（course）内找到这个教师编号所对应的课程编号（cid）是多少，最后要找到这个课程编号在成绩表（sc）内所对应的学生号（sid）是多少，最后再根据找到的学生号从学生表（student）匹配到相应的学生的信息就可以了，下面是这道题的代码：

select * from student
where sid in(select sid from sc
             where cid in(select cid from course
                          where tid=(select tid from teacher
                                     where tname='张三')));

这里还需要注意的一点是，where筛选的时候，sid和cid两项一定要用“in”而不能用“=”，因为会有多个学生号学习同一个课程，一个教师号也可以对应多个课程号，但是最后一个where tid后面是可以换成“=”的因为一个教师只会对应一个教师号，当然你要用“in”的话也可以，总之一句话，用“in”是肯定错不了滴~

难题4：查询没有学全所有课程的同学的信息

我第一次做到这个题的时候感觉有点发懵，因为题干啥都没给到，没有给到特定的任何数据，不过冷静下来自己在脑子里过一遍的时候发现其实也蛮简单的，“所有课程”不就对应着count(distinct sid)么，所以这题我们只要把根据sid分组，计算出每个sid所对应的的cid的数量，小于或者说不等于整个sc表的distinct sid的数量的sid，就是题目要求我们查询到学生的sid嘛，代码如下：

select * from student
where sid in (select sid from sc
              group by sid
              having count(distinct Cid) < (select count(DISTINCT cid) from sc));

难题5：查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

这道题我们首先要把学号为01的同学的课程给找出来，然后再把这些课程所对应的除了01号同学外其他同学的学号，再根据这些学生号去匹配学生表内的学生信息，代码如下：

select * from student
where sid in(select sid from sc 
             where cid in(select cid from sc
                          where sid=01))
and sid<>01;

难题6：查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

题干中的“完全相同”隐含着两个维度，第一是数量相同，第二是课程相同，我们就从这两个维度，通过where...and... 筛选出正确的答案。首先我们来看下数量相同，即count(cid)=(select count(cid) from sc where sid=01，课程相同就是sid not in cid not in(select cid from sc where sid=01)，记住，这里一定要使用sid not in~cid not in，因为只有把拥有一个或多个和01同学所拥有的任意一个课程不用样的课程的的同学筛选去掉，才能留下所拥有的课程是属于01同学所有课程的范畴里面的，最后把这两个条件拼接起来，就可以求出和01号同学学习的课程完全相同的其他同学的信息了，代码如下：

select * from student
where sid in (select sid from sc
                      where sid in (select sid from
                                            (select sid,count(cid) counts from sc
                                             group by sid
                                             having counts=(select count(cid) from sc where sid=01))a)
AND sid not in
(select sid from sc where cid not in(select cid from sc where sid=01))
AND sid !=01);     ### 加一个sid！=01是因为题干要求查询“其他同学的信息”

难题7：查询没学过"张三"老师讲授的任一门课程的学生姓名

这道题其实不难，但是有些人容易犯逻辑上的错误，比如他书写的代码很可能就会是这样的：

select * from student
where sid in(select sid from sc
where cid in (select cid from course
                     where tid  <> (select tid from teacher
                                          where tname ='张三')));

乍一看书写也没啥毛病，但这样书写其实是错误的，因为以上的语句只是把学了除张三之外的其他老师的课程的学生的相关信息，并不能代表他们没学过张三老师的课程，我们要做的应该是，首先把学习了张三老师课程的所有的sid都找出来，然后再用not in来筛选，所以一定要将“<>”或者“not in”放到第一层筛选，即筛选sid那一层中，正确的代码是：

select * from student
where sid not in(select sid from sc
where cid in (select cid from course
                     where tid = (select tid from teacher
                                          where tname ='张三')));

难题8：查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

对于这道题你，我们首先要先求出每个sid所对应的平均分，即 avg(score) group by sid，再将其和sc原表拼接得到一个新表，接着通过筛选分数不及格所在的记录，即where score<60，将所有不及格的记录筛选出来，然后用 count(score) group by sid having count(score)>=2将两门及以上不及格课程的同学的学号求出，再通过sid和学生表(student)来一个拼接，就能求出题目要求的学号，姓名以及平均成绩了，以下是此题的答案：

解法一：

select s.sname, e.* from
(select sid, average FROM 
                     (select * from
                              (select a.*,b.score from
                                                 (select sid,avg(score) as average from sc
                                                  group by Sid) a
join sc b
on a.sid=b.sid) c
where score<60) d
group by sid
having count(score)>=2) e
JOIN student s
on e.sid=s.sid;

解法二：

这个方法直接把where score<60和having count(score)>=2整合到了一起，等于是节省了一个步骤，所以看起来代码会简洁一点，新人我推荐解法一，按部就班自己更容易理清思路。

select s.sname,b.*from
(select sid,count(score) from sc
                  where score<60
                    group by Sid
                    having count(score)>=2) a
join
(select sid,avg(score) from sc
                       group by sid) b
on a.sid=b.sid
join student s
on b.sid=s.sid;

难题9：查询各科成绩最高分、最低分和平均分。以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率，及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

乍一看这个题目又臭又长，再一看，哦其实也没有多复杂，就是要求你呈现的内容多了点而已，包括聚合用的max，min，avg等，因为是查看每个课程的数据所以后面要加上group by cid。不过及格率之类的这些该怎么求出来，这个我们就得用到case when 语句了，比如题干要求及格线是60分，我们该怎么求及格率，废话我当然知道是60分及以上的同学人数除以总人数，那用case when该咋写，没事我来教你哈哈哈哈，我们就拿60分这一档来举例，我们通过case when设定一个score>=60的条件，符合条件的同学记为1，不符合条件的同学记为0，那么符合条件的同学总数就可以通过sum函数统计为sum(case when score>=60 then 1 else 0 end)，然后除以总的学生数，就是count(sid)或者count(score)都可以，记住这里不能加distinct。所以及格率的公式就是

sum(case when score>=60 then 1 else 0 end)/count(score)

能理解吧，不能理解的先去补补case when表达式，补完回来就知道有多简单了哈哈哈哈~其他的一些就比较简单了吧，我们一方面通过sc表将这些个什么率算出来后再和course表通过cid拼接起来，就能得到题干要求做的这些个数据了，下面是我写的查询语句，仅供参考

select a.cname,b.* from
course a inner join
(select cid,max(score) as score_high, min(score) as score_low, avg(score) as score_avg,
sum(case when score>=60 then 1 else 0 end)/count(sid) as 及格率,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(sid) as 中等率,
sum(case when score>=80 and score<90 then 1 else 0 end)/count(sid) as 优良率,
sum(case when score>=90 then 1 else 0 end)/count(sid) as 优秀率
from sc
group by cid) b
on a.cid=b.cid;

难题10：按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

因为要保留名次空缺即若有两个第1，则下一个是第3，第2就被跳过了，所有这道题的话我们需要用到rank() over()函数，题干要求我们根据按课程对每个学生的成绩进行排序，所以rank函数就需要书写成rank() over(partition by cid order by score desc)，所以完整的代码如下：

select *, rank() over(partition by cid order by score desc) from sc;

难题11：按各科成绩进行排序，并显示排名， Score 重复时合并名次

这题的要求和上一题差不多只不过要合并重复名次，所以不会跳过上一题所提到的第2名，这个时候我们无需对代码进行什么调整，只需要将rank改为 dense_rank即可，代码如下：

select *, dense_rank() over(partition by cid order by score desc) from sc;

难题12：查询各科成绩前三名的记录

这题依旧要用到rank()函数，记住要使用rank()，不能使用dense_rank()，根据rank产生的排序列来筛选小于3的记录就可以了，另外一个是不能直接

select sid,cid,score,rank() over(partition by Cid order by score desc) as ranking
from sc
where ranking<= 3

以上这样是错误的，因为where的执行顺序在select前面，所以系统是无法判断anking<=3这个条件的，正确的代码如下：

select a.*
from
(select sid,cid,score,rank() over(partition by Cid order by score desc) as ranking from sc)a
where ranking<= 3;

因为from的执行顺序是排在第一位的，所以先将rank固定到一个临时表里，这样where就可以发挥筛选功能了

难题13：统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

此题的解法与上面第9题的思路是一致的，所以我这里就不做过多的解释了，大家切记把case when的表达式写对写完整即可

select a.cname,b.* from
course a inner join
(select cid,
sum(case when score<60 then 1 else 0 end)/count(sid) as low_rate,
sum(case when score>=60 and score<70 then 1 else 0 end)/count(sid) as lowmid_rate,
sum(case when score>=70 and score<85 then 1 else 0 end)/count(sid) as mid_rate,
sum(case when score>=85 then 1 else 0 end)/count(sid) as high_rate 
from sc group by cid) b on a.cid=b.cid;

难题14：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

此题稍微的考验基本的时间判断能力，我们先来理一理什么情况下，年龄是要减一的，是不是你还没过当年生日的情况下呢？也就是说，当现在的月份小于你的生日月份，年龄是不是要减一，还有就是当现在的月份等于你生日的月份且现在的日期是小于你生日的日期的情况下，是不是要减一，所以我们把以上这两种情况用case when...and...形成条件语句来告知系统该如何判定每个学生目前的年龄，即"month(now())<month(sage) or (month(now())=month(sage) and day(now())<day(sage))" 记住这只是条件语句，or后面的条件要用括号括起来，因为后一个条件里包含and关键词，而系统是优先处理and前后的条件语句的，所以括起来后系统才会知道后面的and只是连接了后一条条件语句的前后两半的部分，另外当中的"month(now())返回当前的月份，day(now())返回当前的日期，对于sage也是同理的，所以我们再把这个条件语句嵌套到case when里面，最后会得到如下的代码：

select sname,sage,case when
month(now())<month(sage) or (month(now())=month(sage) and day(now())<day(sage))
then year(now())-year(sage)-1
else year(now())-year(sage)
end as age from student;

难题15：查询本月过生日的学生

其实这题不算难，就这上一题的month()再给还不了解的同学拓展一下，month()同样也可放在where后面充当过滤条件，所以本题的答案是：

select sname from student
where month(sage)=month(now())

关于时间函数，其实不止上面说的month()和day()，还有week()，year()等等一些常用的

难题16：查询同名同性学生名单，并统计同名人数

我其实一直在纠结这道题有没有必要讲，最后想想还是讲吧虽然感觉有点费劲哈哈哈~

首先我们要先确定题干的要求，同名同性，即sname=sname，ssex=ssex，如此的话我们就将两个student的表通过以上条件join起来就可以了，即student a join student b on sname=sname，ssex=ssex，但这个时候我们还得用select语句将这个join后产生的表呈现出来，也就是说，如果select出来的表格有任意2条记录，他们的两个sname和两个ssex(a和b）的都能对上的话，那么这两个学生就是同名同姓的学生，所以我们就可以根据这个，用count()函数以及group by 和 having>=2来判定即可；

select sid,count(sid) counts from
(select a.* from 
(select Sid, Sname, Ssex from student) a
inner join student b
on a.Sname = b.Sname
and a.Ssex = b.Ssex) c
group by sid
having counts>=2;

在这里我要解释一下，第二行我使用了select a.* 而不是 a.* , b.* 是因为这select是被最外层的select所引用的，因为a和b其实是一样的表格，select a.* , b.*的话那这个引用就会产生重复列，如此的话是无法被最外层的select引用的，所以我就select a.*了，当然也可以只select b.*，只要保证最外层的select所引用的表不包含重复即可。

以上的所有东西都是本人个人原创想法哈，不保证百分百的正确，你们可以先拿代码到客户端里先运行一遍，如果有啥异议也欢迎告知，另外若撞了想法，不胜荣幸嘻嘻嘻~~