Fork me on GitHub

MySQL50题-分类总结

MySQL经典50题

笔者最近将网上流传的MySQL数据库经典50题进行了练习,梳理了一份自己的练习成果。下图是MySQL练习题中涉及到的4张表和它们的具体字段:

  • 学生表
  • 课程表
  • 成绩表
  • 教师表

所有的题目都是根据4张表来进行出题,涉及到了很多的MySQL/SQL的知识点,希望对想提升SQL的读者朋友有所帮助。

如果有不对或者还可以优化的地方欢迎提出来

建表语句

4张表是自己手动创建的,具体语句如下:

插入数据

在建表之后,我们需要往每个表中插入模拟数据:

时间相关

时间相关的问题中涉及到年月日、星期、季度等的求解,同时需要注意边界问题。下面是整理的几个常见的时间处理函数:

  • year():函数返回的是年份
  • date_format(now(), '%Y%m%d') :返回的是当前日期的年月日
  • dayofyear() :该函数返回的是当前日期处于一年中的第几天
  • weekofyear():该函数返回的是该日期处于一年中的第几周
  • week():同样也是返回当前日期处于一年中的第几周
  • month():该函数返回的是月份,1-12
  • dayofweek():该函数返回的是星期索引,1代表星期1,国内的惯例
  • weekday():星期索引,0代表星期1,一般是国外的惯例

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
-- 46、查询各学生的年龄:按照出生日期来算,当前月日 < 出生年月的月日则,年龄减1
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;

-- 47、查询本周过生的同学
select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth);

-- 48、查询下周过生的同学
-- 需要考虑边界问题:就是可能下周刚好是明年的第一周
select *
from Student
where mod(week(now()), 52) + 1 = week(s_birth); -- mod函数求余数

-- 49、查询本月过生的同学
select *
from Student
where month(s_birth) = month(now());

-- 50、查询下月过生的同学
-- 同样需要考虑边界问题:下个月刚好是下年的第一个月
select * from Student
where mod(month(now()),12) + 1 = month(s_birth);

having子句使用

having子句的作用是将数据筛选出来之后在加上条件进行二次筛选,通常是最后执行过滤条件

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
-- 45、查询选修了全部课程的学生信息
select
s.*
,count(c_id) num -- 课程数目
from Score sc
left join Student s
on sc.s_id = s.s_id
group by s.s_id -- 分组
having num in (select count(*)
from Course); --满足全部课程

-- 44、查询至少选修两门课程的同学
select
s_id
,count(*) num
from Score
group by s_id
having count(*) >= 2; -- 至少两门课程

-- 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select
c_id
,count(s_score) num -- 统计课程数量
from Score
group by c_id
having num > 5 -- 课程数目大于5
order by num desc, c_id; -- 排序规则

-- 33、查询平均成绩大于等于75的所有学生的学号、姓名和平均成绩
select
sc.s_id
,s.s_name
,round(avg(sc.s_score),2) avg_score
from Score sc
left join Student s
on sc.s_id = s.s_id
group by sc.s_id,s.s_name
having avg_score >= 75;

-- 27、查询出只有两门课程的全部学生的学号和姓名
select
s.s_id
,s.s_name
from Student s
left join Score sc -- 连接两个表
on s.s_id = sc.s_id
group by 1,2
having count(sc.c_id) = 2; -- 分组后再过滤

-- 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select
s.s_id
,s_name
,round(avg(s_score), 2) avg_score -- 平均成绩
from Student s
left 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门课

-- 11、查询没有学完全部课程的同学的信息
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
)

-- 4、查询平均成绩小于70分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
-- 使用NULL判断
select
a.s_id
,a.s_name
,round(AVG(b.s_score), 2) avg_score
from Student a
left join Score b
on a.s_id = b.s_id
group by a.s_id
having avg_score < 70 or avg_score is null; -- 王菊同学没有任何哼唧,需要她也考虑进来

-- 使用ifnull判断
select
S.s_id
,S.s_name
,round(avg(ifnull(C.s_score,0)), 2) as avg_score -- ifnull 函数:第一个参数存在则取它本身,否则取第二个值
from Student S
left join Score C
on S.s_id = C.s_id
group by s_id
having avg_score < 70;

-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
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; -- 分组之后再进行过滤选择

多表连接查询

有时候我们需要的信息要通过几个表关联起来进行查询,这个时候就要用到SQL的关联查询,主要是LEFT JOIN方法比较多。

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
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; -- 学号不同

-- 30、同名同性的学生名单,并统计同名人数
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;

-- 12、查询至少有一门课与学号为01的同学所学相同的同学的信息
select s1.*
from Student s1
left 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; -- 根据学号分组

-- 9、查询学过编号为01和02课程的学生信息
-- 通过自连接来实现
select s1.*
from Student s1
where s_id in (
select s2.s_id from Score s2
left join Score s3
on s2.s_id=s3.s_id
where s2.c_id='01' and s3.c_id='02'
);

-- 7、查询学过李四老师授课的同学的信息
select s.*
from Teacher t -- 4张表全部连接起来查询
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、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
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的数据
left join Score c
on a.s_id=c.s_id and c.c_id='02'
where b.s_score < c.s_score;

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
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;

排名问题(重点)

在SQL的提数需求中经常会遇到求解前几名,或者前几个数据的需要。由于MySQL 5.X中没有开窗函数,所以我们只能通过自己写SQL脚本来解决排名问题。

在MySQL8.0中则可以直接使用窗口函数来求解。

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
-- 42、查询每门功课成绩最好的前3名
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) <= 3 -- 前3名
order by a.c_id;


-- 25、各科成绩的前3名
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 -- 判断a的分数小于等于b的分数,要带上等号
group by 1,2,3
having count(b.s_id) <= 3 -- b中的个数至少有3个,等号用来应对分数相同情形
order by 2, 3 desc; -- 课程升序,成绩降序

-- 24、查询每个学生的平均成绩及名次
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同t1
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;


-- 22、所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 找出各科成绩2-3名再进行拼接,方法有待优化!!!
(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))


-- 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 -- 总成绩的降序排列,只是没有排名;
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 -- 总成绩的降序排列,只是没有排名;t2和t1相同
order by 3 desc;

-- 19、按照各科成绩进行排序,并且显示排名
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

上面第19题的结果如下图:

最值问题

经常会在实际的工作需求中遇到最值问题的求解。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
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) -- 找出张三老师教授的课程中的最大分值
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 = '张三');

统计count

在实际需求中我们需要统计 一些个数或者人数,用到的是count函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 39:每门课程的学生人数
select
c_id
,count(s_id) -- 统计数目
from Score
group by c_id;

-- 28、查询男女人数
select
s_sex
,count(s_sex) as `人数`
from Student
group by s_sex; -- 性别分组

-- 26、每门课程的选修人数
select
c.c_id
,c.c_name
,count(s.s_id) -- 统计学生人数
from Course c
left join Score s
on c.c_id = s.c_id
group by c.c_id; -- 课程号分组

where语句

where语句的功能是加入条件进行过滤选择。where是先过滤再进行选择筛选,having是完成分组聚合之后再进行过滤。

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
-- 38、查询课程编号为02且课程成绩大于等于75的学生的学号和姓名

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 = 02
and sc.s_score >= 75;

-- 37、查询不及格的课程
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;

-- 36、查询任何一门课程成绩都在75分以上的学生姓名、课程名和分数
select
s.s_name
,c.c_name
,sc.s_score
from Score sc -- 成绩表
left join Student s -- 学生信息表
on sc.s_id = s.s_id
left join Course c -- 课程表
on sc.c_id = c.c_id
where sc.s_score > 75
group by s.s_name, c.c_name, sc.s_score;

-- 34、课程名称为语文,且分数低于70的学生姓名和分数
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 < 70; -- 指定成绩

-- 16、检索02课程分数小于70,按分数降序排列的学生信息
select
s.*
,sc.s_score
from Student s
join Score sc
on s.s_id=sc.s_id
where sc.c_id=02 and sc.s_score < 70
order by sc.s_score desc; -- 指定为降序方式desc

-- 14、查询没有修过李四老师讲授的任何一门课程的学生姓名
-- 一步步得到结果:老师姓名--->老师编号--->课程号---> 学号--->学生姓名
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='李四')
));

-- 13、查询和01同学学习的课程完全相同的同学的信息
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学生需要本身排除

-- 12、查询至少有一门课与学号为01的同学所学相同的同学的信息
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);

-- 11、查询没有学完全部课程的同学的信息
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)); -- 别名

-- 10、查询学过01课程,但没有学过02课程的学生信息
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,需要排除

-- 9、查询学过01并且学过02课程的学生信息
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;

-- 8、找出没有学过李四老师课程的学生
select * -- 3. 通过学号找出全部学生信息
from Student
where s_id not in ( -- 2.学号取反:不在李四老师授课的学生的学号中
select s_id
from Score S
left join Course C
on S.c_id = C.c_id
where C.t_id=(select t_id from Teacher where t_name ="李四") -- 1.查询李四老师的课程
);

-- 7、查询学过李四老师授课的同学的信息
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="李四")
)

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select
a.*
,b.s_score as score_1
,c.s_score as score_2
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; -- 课程01分数比02分数低

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select
a.*
,b.s_score as score_1
,c.s_score as score_2
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; -- 前者成绩高

分组聚合

分组聚合统计在SQL中也是很常见的,需要用到group by和sum、max、min、count等聚合函数

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
-- 35、所有学生的课程及分数情况
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; -- 学号和姓名的分组

-- 33、平均成绩大于等于75的所有学生的学号、姓名和平均成绩
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 >= 75;

-- 32、每门课程的平均成绩,结果按平均成绩降序排列;平均成绩相同时,按课程编号c_id升序排列
select
c_id
,round(avg(s_score),2) avg_score -- 平均函数聚合
from Score
group by 1
order by 2 desc, c_id; -- 指定字段和排序方法

-- 21、查询不同老师所教不同课程平均分从高到低显示
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; -- 降序

-- 21、查询不同老师所教不同课程平均分从高到低显示
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; -- 分组后排序,降序方式

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

-- 17、按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
select
s.s_id
,s.c_id
,s.s_score
,t.avg_score
from Score s
left 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;

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
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;

模糊匹配

SQL中模糊匹配使用的关键字是like,符号是%

1
2
3
4
5
6
7
8
9
10
11
-- 31、1991年出生的学生信息
select *
from Student
where s_birth like '1991%'; -- 模糊匹配like关键字

-- 29、名字中带有“云”的学生信息
-- 我们使用左右匹配:考虑到有人可能姓云(虽然很少)
select * from Student where s_name like "%云%";

-- 6、查询“李”姓老师的数量
select count(t_name) from Teacher where t_name like "李%"; -- 模糊查询和通配符的使用

case语句

case语句用来进行条件判断,下图中介绍了SQL中的两种case表达式的写法,来自《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
-- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
-- case表达式若为真则为1,并进行sum求和操作

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; -- 分课程统计总数和占比

-- 18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90
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 及格率 -- case语句使用
,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;

本文标题:MySQL50题-分类总结

发布时间:2020年12月15日 - 19:12

原始链接:http://www.renpeter.cn/2020/12/15/MySQL50-%E5%88%86%E7%B1%BB%E6%80%BB%E7%BB%93.html

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

Coffee or Tea