超经典MySQL练习50题，做完这些你的SQL就过关了！

小F

发布于 2020-12-16 10:53:20

2.5K0

发布于 2020-12-16 10:53:20

文章被收录于专栏：用户6291251的专栏用户6291251的专栏

相信大多学习了 Mysql 数据库语言的同学都会上网找练习来练手，而大部分的人肯定知道有一篇 Mysql 经典练习题50题的帖子，上面的题目基本上涵盖了 Mysql 查询语句的关键知识点。

笔者近期对又将这 50 题进行了练习，同时整理了相关的参考答案，有自己的思路和方法，也有参考大神们的。不得不说，这50题对SQL的提升真的很有帮助！

笔者使用的 MySQL 版本是 MySQL 5.7.28 。

鉴于 50 题篇幅太长，本文只展示了其中10题及笔者的思考，50 题完整版练习题以及笔者的答案实践已整理在pdf文件中，共有100多页，在文末提供获取的方法。

建表和插入数据

在开始之前，先建立本文所需要的数据表格：

-- 建表
-- 学生表
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;   -- 前者成绩高

第二种方法实现：

题目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;

附加题：总分超过200分的同学

题目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;

结果为：

没有成绩的同学：

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   
);

最后将两个部分的结果连起来即可：通过union方法

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;

题目6

题目需求

查询“李”姓老师的数量

SQL实现

select count(t_name) from Teacher where t_name like "李%";   -- 通配符

这题怕是最简单的吧?

题目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='张三';

自己的方法：

方法2来实现：

方法3实现：

题目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="张三"
  )
);

方法2：

题目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同学是满足的

通过自连接查询的语句如下：

查询出学号后再匹配出学生信息：

通过where语句实现：

方法3的实现：

题目10

题目需求

查询学过01课程，但是没有学过02课程的学生信息（注意和上面?题目的区别）

SQL实现

首先看看哪些同学是满足要求的：只有06号同学是满足的

错误思路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'
);

错误思路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://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，需要排除

！！！！！方法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')
);

鉴于篇幅，本文只展示了50题中的10道题的答案以及笔者的实践心得。

50道题的标题整理如下：

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-12-07，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

数据库

云数据库 SQL Server

本文分享自法纳斯特微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

sql

数据库

云数据库 SQL Server

登录后参与评论

0 条评论

热度