出品:Python数据之道
作者:Peter
编辑:Lemon
相信大多学习了 Mysql 数据库语言的同学都会上网找练习来练手,而大部分的人肯定知道有一篇 Mysql 经典练习题50题的帖子,上面的题目基本上涵盖了 Mysql 查询语句的关键知识点。
笔者近期对又将这 50 题进行了练习,同时整理了相关的参考答案,有自己的思路和方法,也有参考大神们的。不得不说,这50题对SQL的提升真的很有帮助!
笔者使用的 MySQL 版本 是 MySQL 5.7.28
。
鉴于 50 题篇幅太长,本文只展示了其中10题及笔者的思考,50 题完整版练习题以及笔者的答案实践已整理在pdf文件中,共有100多页,在文末提供获取的方法。
![](https://i-blog.csdnimg.cn/blog_migrate/35cb5a0318fd4d55e77f137fa8bb1ecc.png)
建表和插入数据
在开始之前,先建立本文所需要的数据表格:
-- 建表
-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
题目1
题目要求
查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SQL实现
-- 方法1
select
a.*
,b.s_score as 1_score
,c.s_score as 2_score
from Student a
join Score b on a.s_id = b.s_id and b.c_id = '01' -- 两个表通过学号连接,指定01
left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02,或者c中的c_id直接不存在
-- 为NULL的条件可以不存在,因为左连接中会直接排除c表中不存在的数据,包含NULL
where b.s_score > c.s_score; -- 判断条件
-- 方法2:直接使用where语句
select
a.*
,b.s_score as 1_score
,c.s_score as 2_score
from Student a, Score b, Score c
where a.s_id=b.s_id -- 列出全部的条件
and a.s_id=c.s_id
and b.c_id='01'
and c.c_id='02'
and b.s_score > c.s_score; -- 前者成绩高
![](https://i-blog.csdnimg.cn/blog_migrate/91f5e89f5bd0a4bb2c4176abb14a5b18.png)
第二种方法实现:
![](https://i-blog.csdnimg.cn/blog_migrate/c4c4100e463966ab8a9d804527d5283c.png)
![](https://i-blog.csdnimg.cn/blog_migrate/006edd243d8a151e135df00f0b3a71af.png)
题目2
题目要求
查询"01"课程比"02"课程成绩低的学生的信息及课程分数(题目1是成绩高)
SQL实现
类比题目1的实现过程
-- 方法1:通过连接方式实现
select
a.*
,b.s_score as 1_score
,c.s_score as 2_score
from Student a
left join Score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL -- 包含NULL的数据
join score c on a.s_id=c.s_id and c.c_id='02'
where b.s_score < c.s_score;
-- 通过where子句实现
select
a.*
,b.s_score as 1_score
,c.s_score as 2_score
from Student a, Score b, Score c
where a.s_id=b.s_id
and a.s_id=c.s_id
and b.c_id='01'
and c.c_id='02'
and b.s_score < c.s_score; -- 前者比较小
题目3
题目需求
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SQL实现
-- 执行顺序:先执行分组,再执行avg平均操作
select
b.s_id
,b.s_name
,round(avg(a.s_score), 2) as avg_score
from Student b
join Score a
on b.s_id = a.s_id
group by b.s_id -- 分组之后查询每个人的平均成绩
having avg_score >= 60;
-- 附加题:总分超过200分的同学
select
b.s_id
,b.s_name
,round(sum(a.s_score),2) as sum_score -- sum求和
from Student b
join Score a
on b.s_id=a.s_id
group by b.s_id
having sum_score > 200;
![](https://i-blog.csdnimg.cn/blog_migrate/2067bcc0730c9e331517825e65f42ab2.png)
附加题:总分超过200分的同学
![](https://i-blog.csdnimg.cn/blog_migrate/01862761732a35260ca7e319f84733fb.png)
题目4
题目要求
查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SQL实现1-两种情况连接
平均分小于60
select
b.s_id
,b.s_name
,round(avg(a.s_score), 2) as avg_score -- round四舍五入函数
from Student b
join Score a
on b.s_id = a.s_id
group by b.s_id -- 分组之后查询每个人的平均成绩
having avg_score < 60;
结果为:
![](https://i-blog.csdnimg.cn/blog_migrate/7215b5f1d36f6ec3d123b26bb496b9a1.png)
没有成绩的同学:
select
a.s_id
,a.s_name
,0 as avg_score
from Student a
where a.s_id not in ( -- 学生的学号不在给给定表的学号中
select distinct s_id -- 查询出全部的学号
from Score
);
![](https://i-blog.csdnimg.cn/blog_migrate/1bcf6757179f772d9b1ed051c35e49bb.png)
![](https://i-blog.csdnimg.cn/blog_migrate/6f8b09a760562244b4341d6b1fa06ae0.png)
最后将两个部分的结果连起来即可:通过union
方法
![](https://i-blog.csdnimg.cn/blog_migrate/a5b8e690faf22610ee8af59f2ea6b135.png)
SQL实现2-ifnull函数判断
使用ifnull函数
select
S.s_id
,S.s_name
,round(avg(ifnull(C.s_score,0)), 2) as avg_score -- ifnull 函数:第一个参数存在则取它本身,不存在取第二个值0
from Student S
left join Score C
on S.s_id = C.s_id
group by s_id
having avg_score < 60;
使用null判断
select
a.s_id
,a.s_name
,ROUND(AVG(b.s_score), 2) as avg_score
from Student a
left join Score b on a.s_id = b.s_id
GROUP BY a.s_id
HAVING avg_score < 60 or avg_score is null; -- 最后的NULL判断
题目5
题目需求
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SQL实现
select
a.s_id
,a.s_name
,count(b.c_id) as course_number -- 课程个数
,sum(b.s_score) as scores_sum -- 成绩总和
from Student a
left join Score b
on a.s_id = b.s_id
group by a.s_id,a.s_name;
![](https://i-blog.csdnimg.cn/blog_migrate/a820f619fc7f4046b8f40e62f2d93063.png)
题目6
题目需求
查询“李”姓老师的数量
SQL实现
select count(t_name) from Teacher where t_name like "李%"; -- 通配符
![](https://i-blog.csdnimg.cn/blog_migrate/6a9e40310735709e8e97391a57918bf5.png)
这题怕是最简单的吧????
题目7
题目需求
查询学过张三老师授课的同学的信息
SQL实现
-- 方法1:通过张三老师的课程的学生来查找;自己的方法
select * -- 3. 通过学号找出全部学生信息
from Student
where s_id in (
select s_id -- 2.通过课程找出对应的学号
from Score S
join Course C
on S.c_id = C.c_id -- 课程表和成绩表
where C.t_id=(select t_id from Teacher where t_name="张三") -- 1.查询张三老师的课程
);
-- 方法2:通过张三老师的课程来查询
select s1.*
from Student s1
join Score s2
on s1.s_id=s2.s_id
where s2.c_id in (
select c_id from Course c where t_id=( -- 1. 通过老师找出其对应的课程
select t_id from Teacher t where t_name="张三"
)
)
-- 方法3
select s.* from Teacher t
left join Course c on t.t_id=c.t_id -- 教师表和课程表
left join Score sc on c.c_id=sc.c_id -- 课程表和成绩表
left join Student s on s.s_id=sc.s_id -- 成绩表和学生信息表
where t.t_name='张三';
自己的方法:
![](https://i-blog.csdnimg.cn/blog_migrate/84f0dc4471b93939cb9bc1b02e693596.png)
方法2来实现:
![](https://i-blog.csdnimg.cn/blog_migrate/63b4bd544c8ca29d1b5fbbd38b642fa4.png)
方法3实现:
![](https://i-blog.csdnimg.cn/blog_migrate/90e5d400de97d345c536bdfc10171610.png)
题目8
题目需求
找出没有学过张三老师课程的学生
SQL实现
select * -- 3. 通过学号找出全部学生信息
from Student
where s_id not in ( -- 2.通过学号取反:学号不在张三老师授课的学生的学号中
select s_id
from Score S
join Course C
on S.c_id = C.c_id
where C.t_id=(select t_id from Teacher where t_name ="张三") -- 1.查询张三老师的课程
);
-- 方法2:
select *
from Student s1
where s1.s_id not in (
select s2.s_id from Student s2 join Score s3 on s2.s_id=s3.s_id where s3.c_id in(
select c.c_id from Course c join Teacher t on c.t_id=t.t_id where t_name="张三"
)
);
-- 方法3
select s1.*
from Student s1
join Score s2
on s1.s_id=s2.s_id
where s2.c_id not in (
select c_id from Course c where t_id=( -- 1. 通过老师找出其对应的课程
select t_id from Teacher t where t_name="张三"
)
);
![](https://i-blog.csdnimg.cn/blog_migrate/c26c58cb3a7c74219abf7ee4e92e7b21.png)
方法2:
![](https://i-blog.csdnimg.cn/blog_migrate/32754f64318e131bdd926657207b8800.png)
题目9
题目需求
查询学过编号为01,并且学过编号为02课程的学生信息
SQL实现
-- 自己的方法:通过自连接实现
select s1.*
from Student s1
where s_id in (
select s2.s_id from Score s2
join Score s3
on s2.s_id=s3.s_id
where s2.c_id='01' and s3.c_id='02'
);
-- 方法2:直接通过where语句实现
select s1.*
from Student s1, Score s2, Score s3
where s1.s_id=s2.s_id
and s1.s_id=s3.s_id
and s2.c_id=01 and s3.c_id=02;
-- 方法3:两个子查询
-- 1. 先查出学号
select sc1.s_id
from (select * from Score s1 where s1.c_id='01') sc1,
(select * from Score s1 where s1.c_id='02') sc2
where sc1.s_id=sc2.s_id;
-- 2.找出学生信息
select *
from Student
where s_id in (select sc1.s_id -- 指定学号是符合要求的
from (select * from Score s1 where s1.c_id='01') sc1,
(select * from Score s1 where s1.c_id='02') sc2
where sc1.s_id=sc2.s_id);
先从Score表中看看哪些人是满足要求的:01-05同学是满足的
![](https://i-blog.csdnimg.cn/blog_migrate/80b394c6f44a165a26e2302005e0c663.png)
通过自连接查询的语句如下:
![](https://i-blog.csdnimg.cn/blog_migrate/d5b45f8bd0aae1e806091db2eddeaaca.png)
查询出学号后再匹配出学生信息:
![](https://i-blog.csdnimg.cn/blog_migrate/0082b0a70077993c9bc7ef39635e0fb8.png)
通过where语句实现:
![](https://i-blog.csdnimg.cn/blog_migrate/35b4d9b132799a755c5bfe30c70befc2.png)
方法3的实现:
![](https://i-blog.csdnimg.cn/blog_migrate/4bee705e083d1be660f92b8590584db6.png)
题目10
题目需求
查询学过01课程,但是没有学过02课程的学生信息(注意和上面????题目的区别)
SQL实现
首先看看哪些同学是满足要求的:只有06号同学是满足的
![](https://i-blog.csdnimg.cn/blog_migrate/771a3e0b678394e9ddd42b89d7a2ecf3.png)
错误思路1
直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08
select s1.*
from Student s1
where s_id not in ( -- 直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08
select s2.s_id from Score s2
join Score s3
on s2.s_id=s3.s_id
where s2.c_id='01' and s3.c_id ='02'
);
![](https://i-blog.csdnimg.cn/blog_migrate/2cff3a887dda1f12731d893465f64830.png)
错误思路2
将上面题目中的02课程直接取反,导致同时修过01,02,03或者只修01,03的同学也会出现
select s1.*
from Student s1
where s_id in (
select s2.s_id from Score s2
join Score s3
on s2.s_id=s3.s_id
where s2.c_id='01' and s3.c_id !='02' -- 直接取反是不行的,因为修改(01,02,03)的同学也会出现
);
![](https://i-blog.csdnimg.cn/blog_migrate/2a843b391d958f38f1bc3e03bab59d7a.png)
正确思路
https://www.jianshu.com/p/9abffdd334fa
-- 方法1:根据两种修课情况来判断
select s1.*
from Student s1
where s1.s_id in (select s_id from Score where c_id='01') -- 修过01课程,要保留
and s1.s_id not in (select s_id from Score where c_id='02'); -- 哪些人修过02,需要排除
![](https://i-blog.csdnimg.cn/blog_migrate/4e35095f415d8d3cab318efd5b32cec5.png)
![](https://i-blog.csdnimg.cn/blog_migrate/07f5306293ccc442d90730c5363f5d8e.png)
!!!!!方法2:先把06号学生找出来
select * from Student where s_id in (
select s_id
from Score
where c_id='01' -- 修过01课程的学号
and s_id not in (select s_id -- 同时学号不能在修过02课程中出现
from Score
where c_id='02')
);
![](https://i-blog.csdnimg.cn/blog_migrate/88611c49b90212492c97e35bd87cbaaf.png)
![](https://i-blog.csdnimg.cn/blog_migrate/17fbee0edaa9ad0bb294696217588ba7.png)
鉴于篇幅,本文只展示了50题中的10道题的答案以及笔者的实践心得。
50道题的标题整理如下:
为方便大家练习,可以在公号「柠檬数据」后台回复 “SQL50”获取完整的MySQL经典50题及笔者实践的答案。
????长按上方二维码 2 秒
回复「SQL50」即可获取资料
作者简介Peter,硕士毕业僧一枚,从电子专业自学Python入门数据行业,擅长数据分析及可视化。喜欢数据,坚持跑步,热爱阅读,乐观生活。个人格言:不浮于世,不负于己个人站点:www.renpeter.cn,欢迎常来小屋逛逛
本文来自公众号读者投稿,欢迎各位童鞋向公号投稿,点击下面图片了解详情!