Fork me on GitHub

MySQL50-13-习题及答案汇总

MySQL经典50题

MySQL经典50题的习题及参考答案💪

题目1

题目要求

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

SQL实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 方法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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 方法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实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 执行顺序:先执行分组,再执行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

1
2
3
4
5
6
7
8
9
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;

结果为:

没有成绩的同学:

1
2
3
4
5
6
7
8
9
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函数

1
2
3
4
5
6
7
8
9
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判断

1
2
3
4
5
6
7
8
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实现

1
2
3
4
5
6
7
8
9
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实现

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

这题怕是最简单的吧😭

题目7

题目需求

查询学过张三老师授课的同学的信息

SQL实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 方法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实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 自己的方法:通过自连接实现
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);
  1. 先从Score表中看看哪些人是满足要求的:01-05同学是满足的

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

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

通过where语句实现:

方法3的实现:

题目10

题目需求

查询学过01课程,但是没有学过02课程的学生信息(注意和上面👆题目的区别)

SQL实现

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

错误思路1

直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08

1
2
3
4
5
6
7
8
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的同学也会出现

1
2
3
4
5
6
7
8
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
2
3
4
5
6
-- 方法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号学生找出来

1
2
3
4
5
6
7
8
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')
);

题目11

题目需求

查询没有学完全部课程同学的信息

SQL实现

1
2
3
4
5
6
7
8
9
-- 自己的方法
select * -- 排除学号后得到的结果
from Student
where s_id not in (select s_id from (select s_id, count(s_id) as number -- 3.最大课程数所在的学号需要排除
from Score
group by s_id) s -- 取别名
where number=(select max(number) -- 2.保证最大的课程数
from( select s_id, count(s_id) as number -- 1.学号和个数统计(即修了几门课)
from Score group by s_id)t)); -- 别名

自己的方法一开始在课程的最大数中没有使用Course表,导致多使用了一个临时表的结果,现在改成使用Course表的统计值(3)作为课程的总数:

1
2
3
4
5
6
7
8
select s.*
from Student s
where s_id not in (
select s_id
from Score s1
group by s_id
having count(*) = (select count(*) from Course)
);

1
2
3
4
5
6
7
8
9
10
-- 方法2:having

select s.*
from Student s -- 学生表
left join Score s1 -- 成绩表
on s1.s_id = s.s_id
group by s.s_id -- 学号分组
having count(s1.c_id) < ( -- 分组后学生的课程数<3
select count(*) from Course -- 全部课程数=3
)

题目12

题目需求

查询至少有一门课与学号为01的同学所学相同的同学的信息

SQL实现

首先看看结果的:因为01号同学修了全部课程,所以其他的同学都是满足要求,除了08号同学没有任何成绩,不符合

具体实现过程为:

1
2
3
4
5
6
7
8
9
10
11
select *   -- 3、求出学生信息
from Student
where s_id in (
select distinct s_id -- 2、找出满足课程在01学生课程中的全部学号(学生),学号去重,同时将01自己排除
from Score
where c_id in (
select c_id
from Score
where s_id=01 -- 1、找出学号01同学的全部课程
)
and s_id != 01);

1
2
3
4
5
6
7
-- 方法2
select s1.*
from Student s1
join Score s2
on s1.s_id = s2.s_id -- 学生表和成绩表的关联
and c_id in (select c_id from Score where s_id=01) -- 对课程进行限制,只在01学生的课程内
group by s1.s_id; -- 根据学号分组

题目13

题目需求

查询和01同学学习的课程完全相同的同学的信息

SQL实现

  1. 自己的方法
1
2
3
4
5
6
7
8
9
select *
from Student
where s_id in (select s_id -- 3、步骤2中得到的学号是满足要求的
from(select distinct(s_id), count(c_id) number
from Score
group by s_id)t1 -- 1、学号和所修课程分组的结果t1
where number=3 -- 2、投机:选择出所修课程数是3(01修了3门)的学号
and s_id !=01 -- 01 本身排除
);

我们在上面的步骤2中不考虑直接指定3(where number=3),而是用01学生所修的课程数(虽然也是3)来代替:

1
2
3
4
5
6
7
8
9
10
11
12
select *
from Student
where s_id in(
select s_id -- 3、步骤2中得到的学号是满足要求的
from(select distinct(s_id), count(c_id) number
from Score
group by s_id)t1 -- 1、学号和所修课程分组的结果t1
where number=(select count(c_id) number
from Score
group by s_id having s_id=01) -- 2、改变的地方:使用学号01的课程数3来代替
and s_id !=01 -- 01 本身排除
);

  1. 使用group_concat函数

group_concat的使用方法为:

1
group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

我们将Score表中每个s_idc_id进行分组合并,实际的效果如下:

1
2
3
4
5
select
s_id
,group_concat(c_id order by c_id) -- 分组合并,同时排序
from Score
group by s_id;

需要进行排序的原因是防止出现这种情况:01修的课程顺序是:01,02,03;如果有同学修课的顺序是02,03,01,虽然顺序不同,但是本质上他们修的课程是相同的

使用排序后都会变成:01,02,03,保证结果相同

那么之后,我们只需要判断合并后和01号同学相同的结果即可,取出学号:

1
2
3
4
5
6
7
8
9
10
11
12
13
select *   -- 3、查询信息
from Student
where s_id in(
select s_id
from Score
group by s_id
having group_concat(c_id order by c_id)=( -- 2、找出和01号学生分组合并结果相同的学号s_id;也需要排序
select group_concat(c_id order by c_id) -- 1、找出01号学生分组合并的结果,同时排序;排序很重要
from Score
group by s_id
having s_id=01)
and s_id != 01 -- 将自身排除
);

题目14

题目需求

查询没有修过张三老师讲授的任何一门课程的学生姓名

SQL实现

自己的方法,具体过程如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
select s_name   -- 4、学号取反找到学生姓名
from Student
where s_id not in(
select distinct(s_id) -- 3、课程号找到对应的学号
from Score
where c_id=(
select c_id -- 2、教师编号找到对应的课程号
from Course
where t_id=(
select t_id -- 1、姓名找到教师编号
from Teacher
where t_name='张三')
));

1
2
3
4
5
6
7
8
9
10
-- 修过张老师课程的学生的学号
select distinct(s_id)
from Score
where c_id=(
select c_id
from Course
where t_id=(
select t_id
from Teacher
where t_name='张三')); -- 修过张三老师课的学生

题目15

题目需求

查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

SQL实现

首先看看哪些同学是满足两门或者两门以上是及格的

1
2
3
4
5
6
7
8
9
-- 2门及以上不及格的

select
s_id
,round(avg(s_score)) avg_score
from Score
where s_score < 60 -- 小于60分,不及格
group by s_id
having count(s_score) >= 2; -- 不及格的有2门以上

说明04,06是我们最终想要的结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 自己的方法
select
s.s_id
,s.s_name
,t.avg_score
from Student s
join (select
s_id
,round(avg(s_score)) avg_score
from Score
where s_score < 60
group by s_id
having count(s_score) >= 2)t
on s.s_id=t.s_id

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 参考方法1

select
a.s_id,
a.s_name,
ROUND(AVG(b.s_score))
from Student a
left join Score b
on a.s_id = b.s_id
where a.s_id in(
select s_id
from Score
where s_score<60
group by s_id
having count(1)>=2)
group by a.s_id,a.s_name

-- 参考方法2
select
s.s_id
,s_name
,round(avg(s_score), 2) avg_score
from Student s
join Score sc
on s.s_id=sc.s_id
and sc.s_score < 60 -- 不及格
group by s.s_id -- 学号分组
having count(sc.c_id )>= 2; -- 2门课

改进点

上面的两种方法都没有考虑都08学生,3门都没有成绩,这个本题需要改进的地方。

题目16

题目需求

检索01课程分数小于60,按分数降序排列的学生信息

SQL实现

自己的方法如下:

首先从Score表中找出哪些学生是满足这个要求:

1
2
3
4
5
6
select
s_id
,s_score
from Score
where s_score < 60
and c_id = 01;

然后直接将上面的结果和Student表查询:

1
2
3
4
5
6
7
8
9
10
select s.*
from Student s
where s.s_id in (
select
s_id
,s_score
from Score
where s_score < 60
and c_id = 01
);

1
2
3
4
5
6
7
8
9
select
s.*
,t.s_score
from Student s
join (select s_id,s_score -- 2、Student和t的连接查询
from Score
where s_score < 60
and c_id = 01 )t -- 1、将第一步结果作为中间表t
on s.s_id=t.s_id;

1
2
3
4
5
6
7
8
9
-- 自己的方法2:两个表的直接连接查询+where条件
select
s.*
,sc.s_score
from Student s
join Score sc
on s.s_id=sc.s_id
where sc.c_id=01 and sc.s_score < 60
order by sc.s_score desc; -- 默认就是降序desc

题目17

题目需求

按平均成绩从高到低(降序)显示所有学生所有课程的成绩以及平均成绩

SQL实现

下面是自己的解法:

1、先求出每个同学的平均分,并降序排列

1
2
3
4
5
6
select
s_id
,round(avg(s_score),2) avg_score
from Score
group by s_id
order by 2 desc;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 自己的方法
select
s.s_id
,s.c_id
,s.s_score
,t.avg_score
from Score s
join (select
s_id
,round(avg(s_score),2) avg_score
from Score
group by s_id)t
on s.s_id = t.s_id
order by 4 desc; -- 指的是第4个字段

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 参考方法1
select
s.s_id
,(select s_score from Score where s_id=s.s_id and c_id='01')
as 语文
,(select s_score from Score where s_id=s.s_id and c_id='02')
as 数学
,(select s_score from Score where s_id=s.s_id and c_id='03')
as 英语
,round(avg(s_score),2) 平均分
from Score s
group by s.s_id
order by 5 desc;

1
2
3
4
5
6
7
8
9
10
11
select
s.s_id
,max(case s.c_id when '01' then s.s_score end) 语文
,max(case s.c_id when '02' then s.s_score end) 数学
,max(case s.c_id when '03' then s.s_score end) 英语
,avg(s.s_score)
,b.s_name -- 没有出现在group by子句中,导致报错
join Student b
on s.s_id = b.s_id
group by s.s_id
order by 5 desc;

严格模式的报错:

ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.b.s_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现否则就会报错,或者这个字段出现在聚合函数里面

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 参考方法2:将上面的b.s_name去掉

select
s.s_id
,max(case s.c_id when '01' then s.s_score end) 语文
,max(case s.c_id when '02' then s.s_score end) 数学
,max(case s.c_id when '03' then s.s_score end) 英语
,round(avg(s.s_score),2) avg_score
from Score s
join Student b
on s.s_id = b.s_id
group by s.s_id
order by 5 desc;

题目18

题目需求

查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90

SQL实现

思路清晰:统计每个阶段的总人数,再除以总共的人数即可

将成绩表和课程表联合起来进行查询:

  • case 语句用于对每个分数贴标签
  • sum 语句对相应的语句中的1进行求和
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select
s.c_id
,c.c_name
,max(s.s_score)
,min(s.s_score)
,round(avg(s.s_score), 2)
,round(100 * (sum(case when s.s_score >= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 及格率
,round(100 * (sum(case when s.s_score >= 70 and s.s_score <= 80 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 中等率
,round(100 * (sum(case when s.s_score >= 80 and s.s_score <= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优良率
,round(100 * (sum(case when s.s_score >= 90 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as 优秀率
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name;

题目19

题目需求

按照各科成绩进行排序,并且显示排名

分析过程

题目的意思是:将每科的成绩单独进行排名,类似如下的效果:

课程名 分数 排名
英语 99 1
英语 92 2
英语 89 3
数学 88 1
数学 85 2
…… …… ……

SQL实现

第一步:我们对Score表中的一门课程进行排名,比如01课程

1
2
3
4
5
6
7
8
9
10
11
12
select * from(
select
t1.c_id -- 课程号
,t1.s_score -- 分数
,(select count(distinct t2.s_score) -- 课程去重
from Score t2
where t2.s_score >= t1.s_score -- SQL实现排序
and t2.c_id = '01') rank
from Score t1 -- 通过相同的表实现自连接
where t1.c_id = '01'
order by t1.s_score desc
)t1

上面是针对01课程,结果为:

第二步:我们将01、02、03课程全部连接起来,通过union实现

  • 表的自连接
  • SQL实现排序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- 自己的方法

select * from(
select
t1.c_id -- 课程号
,t1.s_score -- 分数
,(select count(distinct t2.s_score) -- 课程去重
from Score t2
where t2.s_score >= t1.s_score -- SQL实现排序
and t2.c_id = '01') rank
from Score t1 -- 通过相同的表实现自连接
where t1.c_id = '01'
order by t1.s_score desc
)t1

union
select * from(
select
t1.c_id -- 课程号
,t1.s_score -- 分数
,(select count(distinct t2.s_score) -- 课程去重
from Score t2
where t2.s_score >= t1.s_score -- SQL实现排序
and t2.c_id = '02') rank
from Score t1 -- 通过相同的表实现自连接
where t1.c_id = '02'
order by t1.s_score desc
)t2

union
select * from(
select
t1.c_id -- 课程号
,t1.s_score -- 分数
,(select count(distinct t2.s_score)
from Score t2
where t2.s_score >= t1.s_score
and t2.c_id = '03') rank
from Score t1
where t1.c_id = '03'
order by t1.s_score desc
)t3;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 参考代码

select * from (select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score)
from Score t2
where t2.s_score>=t1.s_score and t2.c_id='01') rank
from Score t1 where t1.c_id='01'
order by t1.s_score desc) t1

union
select * from (select
t1.c_id
,t1.s_score
,(select count(distinct t2.s_score)
from Score t2
where t2.s_score>=t1.s_score and t2.c_id='02') rank
from Score t1 where t1.c_id='02'
order by t1.s_score desc) t2

union
select * from (select
t1.c_id,
t1.s_score,
(select count(distinct t2.s_score) from Score t2 where t2.s_score>=t1.s_score and t2.c_id='03') rank
from Score t1 where t1.c_id='03'
order by t1.s_score desc) t3

题目20

题目需求

查询学生的总成绩,并进行排名

SQL实现

1、先查询每个学生的总成绩

1
2
3
4
5
6
select
s_id
,sum(s_score)
from Score
group by s_id
order by 2 desc;

将上面的结果和学生信息表进行关联查询:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--
select
s.s_name
,s.s_id
,t.score
from Student s
join(select
s_id
,sum(s_score) score
from Score
group by s_id
order by 2 desc
)t
on s.s_id = t.s_id;

1
2
3
4
5
6
7
8
9
10
11
-- 不使用中间表查询

select
s.s_id
,s.s_name
,sum(sc.s_score) score
from Student s
join Score sc
on s.s_id = sc.s_id
group by s.s_id
order by 3 desc;

如果想给排名加上一个排序号,参考之前的文章

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 加上排序号

select
t1.s_id ,t1.s_name, t1.score
,(select count(t2.score)
from(select s.s_id, s.s_name, sum(sc.s_score) score
from Student s
join Score sc
on s.s_id = sc.s_id
group by s.s_id
order by 3 desc)t2 -- t2和t1相同
where t2.score > t1.score) + 1 as rank
from(
select s.s_id ,s.s_name ,sum(sc.s_score) score
from Student s
join Score sc
on s.s_id = sc.s_id
group by s.s_id
order by 3 desc)t1 -- t1
order by 3 desc;

题目21

题目需求

查询不同老师所教不同课程平均分从高到低显示

SQL实现

先找出每个老师教授了哪些课程:

1
2
3
4
5
6
select
c.c_name
,t.t_name
from Course c
left join Teacher t
on c.t_id = t.t_id;

将上面的结果和成绩表连接起来:

1
2
3
4
5
6
7
8
9
10
11
select
c.c_name
,t.t_name
,round(avg(s.s_score),2) score -- 课程分组后再求均值
from Course c -- 主表,通过两次连接
left join Teacher t
on c.t_id = t.t_id
left join Score s
on c.c_id = s.c_id
group by c.c_id -- 课程分组
order by 3 desc; -- 降序

题目22

题目需求

查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SQL实现

自己的方法

1、课程表和成绩表连接起来,显示所有的课程和成绩信息

1
2
3
4
5
6
7
8
select
s.s_id
,s.c_id
,s.s_score
,c.c_name
from Score s
join Course c
on s.c_id = c.c_id

2、查出全部的语文成绩

1
2
3
4
5
select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc;

3、我们找出语文的第2、3的学生

1
2
3
4
5
6
select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc
limit 1, 2;

4、同时求出语文、数学、英语的分数,并且通过union拼接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- union连接

(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc
limit 1, 2)

union

(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '数学'
order by s.s_score desc
limit 1, 2)

union
((select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '英语'
order by s.s_score desc
limit 1, 2))

5、将上面的结果学生信息表进行连接即可

好歹是实现了😭

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 最终脚本
-- !!!!真的需要好好优化下

select
s.s_id
,s.s_name
,t.c_name
,t.s_score
from Student s
join (-- union连接

(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc
limit 1, 2)

union

(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '数学'
order by s.s_score desc
limit 1, 2)

union
((select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '英语'
order by s.s_score desc
limit 1, 2)))t -- 临时表t

on s.s_id = t.s_id

和第25题相同的方法

1、以语文为例,首先我们找出前3名的成绩(包含相同的成绩)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 语文
select
a.s_id
,a.c_id
,a.s_score -- 3、此时a表的成绩就是我们找的
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形
order by 3 desc
limit 1,2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 语文
select
a.s_id
,a.c_id
,a.s_score -- 3、此时a表的成绩就是我们找的
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形
order by 3 desc
limit 1,2; -- 取得第2、3名

在通过数学和英语的类似操作得到2、3名的成绩,再进行拼接即可

题目23

题目需求

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

SQL实现

自己的方法

1、如何对每个成绩进行分组展示:ABCD代表相应的等级

1
2
3
4
5
6
7
8
9
select
c_id
,s_score
,case when s_score >= 85 and s_score<= 100 then 'A' -- 大小关系必须分两次写,一次写的话MySQL无法识别
when 70 <= s_score and s_score < 85 then 'B'
when 60 <= s_score and s_score < 70 then 'C'
when 0 <= s_score and s_score < 60 then 'D'
else '其他' end as 'category'
from Score s;

2、将两个表关联起来展示数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 1、查看全部课程和成绩信息

select
s.c_id
,c.c_name
,s.s_score
,case when s.s_score >= 85 and s.s_score<= 100 then 'A' -- 大小关系必须分两次写,一次写的话MySQL无法识别
when 70 <= s.s_score and s.s_score < 85 then 'B'
when 60 <= s.s_score and s.s_score < 70 then 'C'
when 0 <= s.s_score and s.s_score < 60 then 'D'
else '其他' end as 'category'
from Score s
join Course c
on s.c_id = c.c_id;

3、完整代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
s.c_id 编号
,c.c_name 科目
,sum(case when s.s_score >= 85 and s.s_score<= 100 then 1 else 0 end) "[85,100]人数"
,round(100 * (sum(case when s.s_score >= 85 and s.s_score<= 100 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[85,100]百分比'
,sum(case when s.s_score >= 70 and s.s_score<= 85 then 1 else 0 end) "[70,85]人数"
,round(100 * (sum(case when s.s_score >= 70 and s.s_score<= 85 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[70,85]百分比'
,sum(case when s.s_score >= 60 and s.s_score<= 70 then 1 else 0 end) "[60,70]人数"
,round(100 * (sum(case when s.s_score >= 60 and s.s_score<= 70 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[60,70]百分比'
,sum(case when s.s_score >= 0 and s.s_score<= 60 then 1 else 0 end) "[0,60]人数"
,round(100 * (sum(case when s.s_score >= 0 and s.s_score<= 60 then 1 else 0 end) / sum(case when s.s_score then 1 else 0 end)), 2) as '[0,60]百分比'
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name

参考方法

1、先统计每个阶段的人数和占比

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select
c_id
,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100'
,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '占比'
from Score
group by c_id; -- 分课程统计总数和占比


-- 方式2
select
c_id
,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100'
,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(case when s_score then 1 else 0 end)), 2) '占比' -- 不同count(*)
from Score
group by c_id;

注意对比:

2、我们将4种情况同时查出来

1
2
3
4
5
6
7
8
9
10
11
12
select
c_id
,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100'
,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '[85,100]占比'
,sum(case when s_score > 70 and s_score <=85 then 1 else 0 end) as '70-85'
,round(100 * (sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*)), 2) '[70,85]占比'
,sum(case when s_score > 60 and s_score <=70 then 1 else 0 end) as '60-70'
,round(100 * (sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*)), 2) '[60,70]占比'
,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as '0-60'
,round(100 * (sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*)), 2) '[0,60]占比'
from Score
group by c_id; -- 分课程统计总数和占比

3、将科目名称连接起来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 整体和自己的方法是类似的
select
s.c_id
,c.c_name
,sum(case when s_score > 85 and s_score <=100 then 1 else 0 end) as '85-100'
,round(100 * (sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end) / count(*)), 2) '[85,100]占比'
,sum(case when s_score > 70 and s_score <=85 then 1 else 0 end) as '70-85'
,round(100 * (sum(case when s_score > 70 and s_score <= 85 then 1 else 0 end) / count(*)), 2) '[70,85]占比'
,sum(case when s_score > 60 and s_score <=70 then 1 else 0 end) as '60-70'
,round(100 * (sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) / count(*)), 2) '[60,70]占比'
,sum(case when s_score >=0 and s_score <=60 then 1 else 0 end) as '0-60'
,round(100 * (sum(case when s_score > 0 and s_score <= 60 then 1 else 0 end) / count(*)), 2) '[0,60]占比'
from Score s
left join Course c
on s.c_id = c.c_id
group by s.c_id, c.c_name; -- 分课程统计总数和占比

题目24

题目需求

查询学生的平均成绩及名次

SQL实现

自己的方法

1、先求出每个人的平均分

1
2
3
4
5
6
7
8
9
10
-- 自己的方法

select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id=s.s_id
group by sc.s_id,s.s_name

2、我们对上面的结果进行排序

!!!MySQL5中是没有rank函数的,需要自己实现排序功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- MYSQL5.7中没有rank函数,所以通过自连接实现

select
t1.s_id
,t1.s_name
,t1.avg_score
,(select count(distinct t2.avg_score)
from (select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id=s.s_id
group by sc.s_id,s.s_name)t2 -- 临时表t2也是上面的结果
where t2.avg_score >= t1.avg_score
) rank

from (select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id=s.s_id
group by sc.s_id,s.s_name)t1 -- 临时表t1就是上面的结果
order by t1.avg_score desc;

参考方法
1
2
3
4
5
6
7
8
9
10
11
12
13
select
a.s_id -- 学号
,@i:=@i+1 as '不保留空缺排名' -- 直接i的自加
,@k:=(case when @avg_score=a.avg_s then @k else @i end) as '保留空缺排名'
,@avg_score:=avg_s as '平均分' -- 表a中的值

from (select
s_id
,round(avg(s_score), 2) as avg_s
from Score
group by s_id
order by 2 desc)a -- 表a:平均成绩的排序和学号
,(select @avg_score:=0, @i:=0, @k:=0)b -- 表b:通过变量设置初始值

实现rank函数

1
2
3
4
5
6
7
8
select
s.s_name -- 姓名
,s.s_score -- 成绩
,(select count(distinct t2.s_score)
from Score t2
where t2.s_score >= t1.s_score) rank -- 在t2分数大的情况下,统计t2的去重个数
from Score t1
order by t1.s_score desc; -- 分数降序排列

举例子来说明这个脚本:

姓名 成绩
张三 89
李四 90
王五 78
小明 98
小红 60
  1. 当t1.s_score=89,满足t2.s_score > = t1.s_score的有98,90和89,此时count(distinct t2.s_score) 的个数就是3
  2. 当t1.s_score=90,满足t2.s_score > = t1.s_score的有98和90,此时count(distinct t2.s_score) 的个数就是2
  3. 当t1.s_score=78,满足t2.s_score > = t1.s_score的有98、90、89和78,此时count(distinct t2.s_score) 的个数就是4
  4. 当t1.s_score=98,满足t2.s_score > = t1.s_score的只有98,此时count(distinct t2.s_score) 的个数就是1
  5. 当t1.s_score=60,满足t2.s_score > = t1.s_score的有89、90、78、98、60,此时count(distinct t2.s_score) 的个数就是5

通过上面的步骤,我们发现:t1中每个分数对应的个数就是它的排名

题目25

题目需求

查询各科成绩前三名的记录

SQL实现

自己的方法

1、首先我们找出语文的前3名

1
2
3
4
5
6
select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc -- 降序之后取出前3条记录
limit 3;

2、通过同样的方法我们可以求出数学和英语的前3条记录,然后通过union进行联结,有待优化😭

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 自己的脚本

(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '语文'
order by s.s_score desc -- 降序之后取出前3条记录
limit 3)

union

(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '数学'
order by s.s_score desc
limit 3)

union

(select s.s_id, s.s_score, c.c_name
from Score s
join Course c on s.c_id = c.c_id
where c.c_name = '英语'
order by s.s_score desc
limit 3)

参考方法

通过Score表的自连接,表a中的值小于表b中的值,排序之后我们取前3

1
2
3
4
5
6
7
8
9
10
11
select
a.s_id
,a.c_id
,a.s_score -- 取出a中的成绩
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 表b中的成绩大
group by 1,2,3
having count(b.s_id) = 3
order by 2, 3 desc;

我们通过语文这个科目来理解上面的代码:前3名是80,80,76

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 语文
select
a.s_id
,a.c_id
,a.s_score -- 3、此时a表的成绩就是我们找的
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号
and a.c_id="01"
group by 1,2
having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形
order by 3 desc;

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 语文

select
a.s_id
,a.c_id
,a.s_score -- a表的成绩
from Score a
join Score b
on a.c_id = b.c_id
and a.s_score <= b.s_score -- 1、判断a的分数小于等于b的分数,要带上等号
group by 1,2,3
having count(b.s_id) <= 3 -- 2、b中的个数至少有3个,应对分数相同的情形
order by 2, 3 desc; -- 课程(2)的升序,成绩()3的降序

题目26

题目需求

查询每门课被选修的学生数

SQL实现

1
2
3
4
5
6
7
8
select
c.c_id
,c.c_name
,count(s.s_id)
from Course c
join Score s
on c.c_id = s.c_id
group by c.c_id;

题目27

题目需求

查询出只有两门课程的全部学生的学号和姓名

SQL实现

having条件是分组之后在执行的,where语句是分组前先执行的

1
2
3
4
5
6
7
8
select
s.s_id
,s.s_name
from Student s
join Score sc
on s.s_id = sc.s_id
group by 1,2
having count(sc.c_id) = 2;

题目28

题目需求

查询男女生人数

SQL实现

先看看数据:男女人数都是4

1
2
3
4
5
6
7
8
9
10
11
12
--  自己的方法
select
count(case when s_sex='男' then 1 end) as '男'
,count(case when s_sex='女' then 1 end) as '女'
from Student;

-- 参考方法
select
s_sex
,count(s_sex) as `人数`
from Student
group by s_sex;

题目29

题目需求

查询名字中含有字的学生信息

SQL实现

先看看哪些同学的名字中有风

1
2
-- 模糊匹配:我们在两边都加上了%,考虑的是姓或者名字含有风,虽然风姓很少见
select * from Student where s_name like "%风%";

题目30

题目需求

查询同名同性的学生名单,并统计同名人数

SQL实现

1、先看看班级的学生信息

现有的数据中没有同名的学生,但是当班级人数增多的时候很有可能在班级上出现同名的学生

2、假设有同名同性的学生

1
2
3
4
5
6
7
8
9
10
select
a.s_name
,a.s_sex
,count(*)
from Student a -- 同一个表的自连接
join Student b
on a.s_id != b.s_id -- 连接的时候不能是同一个人:学号保证,每个人的学号是唯一的,其他字段都可能重复
and a.s_sex = b.s_sex -- 性别相同
and a.s_name = b.s_name -- 名字相同
group by 1,2;

题目31

题目需求

查询1990年出生的学生信息

SQL实现

1
2
3
select *
from Student
where s_birth like '1990%'; -- 模糊匹配

题目32

题目需求

查询每门课程的平均成绩,结果按平均成绩降序排列;平均成绩相同时,按课程编号c_id升序排列

SQL实现

1
2
3
4
5
6
7
-- 自己的方法
select
c_id
,round(avg(s_score),2) avg_score
from Score
group by 1
order by 2 desc, c_id; -- 指定字段和排序方法

如果想带上课程的名称,需要和Course表进行联结

1
2
3
4
5
6
7
8
9
10
-- 自己的方法
select
c.c_id
,c.c_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Course c
on sc.c_id = c.c_id
group by 1,2
order by 3 desc, c.c_id; -- 指定字段和排序方法

题目33

题目需求

查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SQL实现

1
2
3
4
5
6
7
8
9
10
-- 自己的方法
select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
join Student s
on sc.s_id = s.s_id
group by sc.s_id,s.s_name
having avg_score >= 85;

题目34

题目需求

查询课程名称为数学,且分数低于60的学生姓名和分数

SQL实现

1
2
3
4
5
6
7
8
9
10
select
s.s_name
,sc.s_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 c.c_name = '数学'
and sc.s_score < 60; -- 指定成绩

看看真正的数据,的确只有一个人满足

题目35

题目需求

查询所有学生的课程及分数情况

SQL实现

1
2
3
4
5
6
7
8
9
10
11
12
13
select
s.s_id
,s.s_name
,sum(case c.c_name when '语文' then sc.s_score else 0 end) as '语文' -- 语文分数
,sum(case c.c_name when '数学' then sc.s_score else 0 end) as '数学'
,sum(case c.c_name when '英语' then sc.s_score else 0 end) as '英语'
,sum(sc.s_score) as '总分' -- 每个人的总分
from Student s
left join Score sc
on s.s_id = sc.s_id
left join Course c
on sc.c_id = c.c_id
group by s.s_id, s.s_name; -- 学号和姓名的分组

题目36

题目需求

查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SQL实现

1
2
3
4
5
6
7
8
9
10
11
select
s.s_name
,c.c_name
,sc.s_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.s_score > 70
group by s.s_name, c.c_name, sc.s_score;

题目37

题目需求

查询不及格的课程

SQL实现

1
2
3
4
5
6
7
8
select
sc.c_id
,c.c_name
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where sc.s_score < 60;

题目38

题目需求

查询课程编号为01且课程成绩大于等于80的学生的学号和姓名

SQL实现

1
2
3
4
5
6
7
8
9
10
11
select
sc.s_id
,s.s_name
,sc.s_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 c.c_id = 01
and sc.s_score >= 80;

题目39

题目需求

每门课程的学生人数

SQL实现

1
2
3
4
5
select
c_id
,count(s_id)
from Score
group by c_id;

如果想连接到课程名称:

1
2
3
4
5
6
7
8
-- 报错!!!
select
sc.c_id
,c.c_name
,count(sc.s_id)
from Score sc
join Course c
group by sc.c_id;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.c.c_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决的方法是将我们之前的结果作为临时表和Course表来连接查询:

1
2
3
4
5
6
7
8
9
10
11
select
c.c_name 课程名称
,c.c_id 课程编号
,t.num 人数
from Course c
join(select
c_id
,count(s_id) num
from Score
group by c_id)t -- 上面结果的临时表
on c.c_id = t.c_id;

题目40

题目需求

查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩

SQL实现

1、我们先找出张三老师教了哪些课程

1
2
3
4
5
6
7
select
c.c_id
,c.c_name
from Course c
join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三';

2、找出哪些人修了数学

1
2
3
4
5
6
7
8
9
select
sc.s_id
,sc.s_score
from Score sc
left join Course c
on sc.c_id = c.c_id
left join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三';

3、通过max函数找出成绩的最高分

1
2
3
4
5
6
7
8
select
max(sc.s_score)
from Score sc
left join Course c
on sc.c_id = c.c_id
left join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三';

4、连接Student表,找出学生信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select
s.*
,sc.s_score
,sc.c_id
,c.c_name
from Student s
left join Score sc
on s.s_id = sc.s_id
left join Course c
on sc.c_id = c.c_id
where sc.s_score in (select max(sc.s_score) -- 最大值90分的确定
from Score sc
left join Course c
on sc.c_id = c.c_id
left join Teacher t
on c.t_id = t.t_id
where t.t_name = '张三');

题目41

题目需求

查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SQL实现

1
2
3
4
5
6
7
8
9
select
a.s_id
,a.c_id
,a.s_score
from Score a
join Score b
on a.c_id != b.c_id
and a.s_score = b.s_score
and a.s_id != b.s_id;

image-20201122001411784

我们对学号还需要去重下:

1
2
3
4
5
6
7
8
9
select
distinct a.s_id
,a.c_id
,a.s_score
from Score a
join Score b
on a.c_id != b.c_id
and a.s_score = b.s_score
and a.s_id != b.s_id;

再看看原始的数据中是否符合要求:

题目42

题目需求

查询每门功成绩最好的前两名

SQL实现

自己的方法

还需要好好优化的😭

1
2
3
4
5
6
7
8
9
10
11
12
-- 先找出语文的前2名同学

select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '语文' -- 改成数学和英语即可求出相应的信息
order by sc.s_score desc
limit 2;

将3门学科的信息进行拼接即可求出答案:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- 最终脚本

(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '语文'
order by sc.s_score desc
limit 2)

union

(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '数学'
order by sc.s_score desc
limit 2)

union

(select
c.c_id
,sc.s_id
,sc.s_score
from Score sc
join Course c
on sc.c_id = c.c_id
where c.c_name = '英语'
order by sc.s_score desc
limit 2)

参考方法(好方法)

如何解决前几名排序的问题🐂🍺🚗太牛了

1
2
3
4
5
6
7
8
9
10
select
a.c_id
,a.s_id
,a.s_score
from Score a
where (select count(1) -- count(1)类似count(*):统计表b中分数大的数量
from Score b
where b.c_id=a.c_id -- 课程相同
and b.s_score >= a.s_score) <= 2 -- 前2名
order by a.c_id;

首先我们看看真实的数据,我们以01课程来进行解释上面的代码:

符合要求count(1)<=2的只有两种情况

还需要好好理解下😭

题目43

题目需求

统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

SQL实现

1
2
3
4
5
6
7
select
c_id
,count(s_score) num
from Score
group by c_id
having num > 5
order by num desc, c_id;

题目44

题目需求

检索至少选修两门课程的学生学号

SQL实现

结果显示全部满足要求

1
2
3
4
5
6
select
s_id
,count(*) num
from Score
group by s_id
having num >= 2;

题目45

题目需求

查询选修了全部课程的学生信息

SQL实现

自己的方法

1、全部的课程数目num

1
select count(*) from Course;   -- 总共3门

2、从Score表分组统计每个人的课程数目,满足是3的学生信息

1
2
3
4
5
6
7
select
s_id
,count(c_id) num -- 课程数目
from Score
group by s_id
having num in (select count(*)
from Course); --满足全部课程

3、我们找出上面结果中的学生信息即可

1
2
3
4
5
6
7
8
9
select
s.*
,count(c_id) num -- 课程数目
from Score sc
join Student s
on sc.s_id = s.s_id
group by s.s_id
having num in (select count(*)
from Course); --满足全部课程

参考方法
1
2
3
4
5
6
7
select *   -- 3、s_id对应的学生信息
from Student
where s_id in(select s_id -- 2、最大课程数对应的s_id
from Score
group by s_id
having count(*)=(select count(*) from Course) -- 1、全部课程数
)

题目46

题目需求

查询各学生的年龄:按照出生日期来算,当前月日 < 出生年月的月日则,年龄减1

参考资料

SQL实现

自己的方法
1
2
3
4
5
6
7
-- 自己的方法

select *
,case when dayofyear(now()) >= dayofyear(s_birth) then year(now()) - year(s_birth)
when dayofyear(now()) < dayofyear(s_birth) then year(now()) - year(s_birth) - 1
else 'other' end as 'age'
from Student;

参考方法
1
2
3
4
5
select
s_name
,s_birth
,date_format(now(), '%Y') - date_format(s_birth, '%Y') - (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end) as age -- 当前日期大,说明已经过生了,年龄正常;反之说明今年还没有到年龄-1
from Student;

如何返回年份/日期

通过date_format函数能够指定返回的数据

1
2
3
4
-- 两个方法

select year(now());
select date_format(now(), '%Y');

返回具体的日期:

题目47

题目需求

查询本周过生日的学生

1
select week(now());   -- 47
  1. DAYOFWEEK(date)

返回日期date的星期索引(1=星期天,2=星期一, ……7=星期六),符合国内标准

  1. WEEKDAY(date)

返回date的星期索引(0=星期一,1=星期二, ……6= 星期天),国外标准

SQL实现

自己的方法
1
2
3
select *
from Student
where week(s_birth) = week(now());

参考方法
1
2
3
select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth);  -- 方式1

select * from student where yearweek(s_birth) = yearweek(date_format(now(),'%Y%m%d')); -- 方式2

题目48

题目需求

查询下周过生日的学生

SQL实现

1
2
3
4
5
6
7
-- 自己的方法
select *
from Student
where week(s_birth) = week(now()) + 1; -- 往前推1周

-- 参考方法
select * from Student where week(date_format(now(),'%Y%m%d')) + 1= week(s_birth);

边界问题

如果现在刚好的是今年的最后一个周,那么下周就是明年的第一个周,我们如何解决这个问题呢??改进后的脚本:

1
2
3
4
-- 自己的方法

select * from Student
where mod(week(now()), 52) + 1 = week(s_birth);

当现在刚好是第52周,那么mod函数的结果是0,则说明出生的月份刚好是明年的第一周

题目49

题目需求

查询本月过生的同学

SQL实现

1
2
3
4
5
6
7
-- 自己的方法
select *
from Student
where month(s_birth) = month(now());

-- 参考方法
select * from Student where month(date_format(now(), '%Y%m%d')) = month(s_birth);

返回的是空值,是因为数据本身就没有在11月份出生的同学

题目50

题目需求

查询下月过生的同学

SQL实现

1
2
3
4
5
6
7
-- 自己的方法
select * from Student
where month(s_birth) = month(now()) + 1; -- 推迟一个月

-- 参考方法
select * from Student
where month(date_format(now(), '%Y%m%d')) + 1= month(s_birth);

边界问题

假设现在是12月份,那么下个月就是明年的1月份,我们如何解决???将上面的代码进行改进:

1
2
select * from Student
where mod(month(now()),12) + 1 = month(s_birth);

如果现在是12月份,则mod函数的结果是0,说明生日刚好是1月份

本文标题:MySQL50-13-习题及答案汇总

发布时间:2020年11月22日 - 15:11

原始链接:http://www.renpeter.cn/2020/11/22/MySQL50-13-%E4%B9%A0%E9%A2%98%E5%8F%8A%E7%AD%94%E6%A1%88%E6%B1%87%E6%80%BB.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

Coffee or Tea