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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
| -- 1. 查询课程编号 01 比 02 高的学生的学号和姓名
select a.s_id '学号', c.s_name '姓名'
from (select s_id, s_score 's01'
from Score
where c_id = '01') a
inner join
(select s_id, s_score 's02'
from Score
where c_id = '02') b
on a.s_id = b.s_id
inner join Student as c on c.s_id = a.s_id
where s01 > s02;
-- 3. 查询所有学生的学号、姓名、选课数和总成绩
select a.s_id '学号', a.s_name '姓名', sum(ifnull(b.s_score, 0)) '总成绩', count(b.c_id) '选课数'
from Student as a
left join Score b on a.s_id = b.s_id
group by a.s_id, a.s_name;
-- 5. 查询[没]学过'张三'老师课的学生的学号、姓名
select s_id '学号', s_name '姓名'
from Student
where s_id not in (select s_id
from Score
where c_id = (select c_id
from Course
where t_id = (select t_id
from Teacher
where t_name = '张三')));
select s_id '学号', s_name '姓名'
from Student
where s_id not in (select s_id
from Score as s
inner join Course as c on s.c_id = c.c_id
inner join Teacher t on c.t_id = t.t_id
where t.t_name = '张三');
-- 6. 查询学过'张三'老师[所有课]的学生的学号、姓名
select s.s_id '学号', s.s_name '姓名'
from Student as s
inner join Score s2 on s.s_id = s2.s_id
inner join Course c on s2.c_id = c.c_id
inner join Teacher t on c.t_id = t.t_id
where t.t_name = '张三'
group by s.s_id
having count(*) = (select count(*)
from Course c
inner join Teacher t on c.t_id = t.t_id
where t_name = '张三'
group by t.t_id, t.t_name);
-- 10. 查询没有学全所有课的学生的学号、姓名
select s.s_id '学号', s.s_name '姓名'
from Student as s
left join Score s2 on s.s_id = s2.s_id
group by s.s_id
having count(distinct c_id) < (select count(distinct c_id)
from Course);
-- 12. 查询和'01'号同学所学课程完全相同的其他同学的学号
select distinct s.s_id '学号', s.s_name '姓名'
from Student as s
inner join Score s2 on s.s_id = s2.s_id
-- 去除选了 01 学生所学课程之外课程的学生
where s2.s_id not in
(select s_id
from Score
where c_id not in (select c_id
from Score
where s_id = '01'))
and s.s_id != '01'
group by s.s_id, s.s_name
having count(distinct c_id) = (select count(*)
from Score
where s_id = '01');
-- 17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select distinct s.s_id '学号',
st.s_name '姓名',
c_01_score '语文',
c_02_score '数学',
c_03_score '英语',
c_04_score '奥数',
avg_score '平均成绩'
from Score s
inner join (select s_id,
max(case when c_id = '01' then s_score end) 'c_01_score',
max(case when c_id = '02' then s_score end) 'c_02_score',
max(case when c_id = '03' then s_score end) 'c_03_score',
max(case when c_id = '04' then s_score end) 'c_04_score',
avg(s_score) 'avg_score'
from Score
group by s_id) s2 on s.s_id = s2.s_id
inner join Student st on s.s_id = st.s_id
order by avg_score desc;
-- 18. 查询各科成绩最高分、最低分、平均分，展示：课程号、课程名、最高分、最低分、平均分、及格率、中等率、优良率、优秀率
select sc.c_id '课程号',
c.c_name '课程名',
max(sc.s_score) 'max',
min(sc.s_score) 'min',
avg(sc.s_score) 'avg',
sum(IF(sc.s_score >= 60, 1, 0)) / count(sc.s_id) '及格率',
sum(IF(sc.s_score >= 70 and sc.s_score < 80, 1, 0)) / count(sc.s_id) '中等率',
sum(IF(sc.s_score >= 80 and sc.s_score < 90, 1, 0)) / count(sc.s_id) '优良率',
sum(IF(sc.s_score >= 90, 1, 0)) / count(sc.s_id) '优秀率'
from Score sc
inner join Course c on sc.c_id = c.c_id
group by sc.c_id;
-- 19. 按各科成绩排序，并显示排名 80 85 85 90
-- MySQL 8.0 窗口函数：不会改变记录条数
-- row_number() 排序不会重复 1 2 3 4 没有并列
-- dense_rank() 有并列，1 2 2 3 最后一名不一定等于总人数
-- rank() 有并列， 1 2 2 4 月考排名
select *,
rank() over (partition by c_id order by s_score desc) 'rank',
dense_rank() over (partition by c_id order by s_score desc) 'dense_rank',
row_number() over (partition by c_id order by s_score desc) 'row_number'
from Score;
# where c_id ='01';
-- 测试最左匹配原则
CREATE INDEX idx_test2
ON Student (s_birth, s_name, s_sex);
explain
select s_name
from Student
where s_birth = '12345'
# and s_name = '赵雷'
and s_sex = '男';
-- 22. 查询所有课程的成绩第 2 名到第 3 名的学生信息和该课程成绩
-- row_number() 排序不会重复 1 2 3 4 没有并列 (见 19 题)
select *
from (select sc.c_id '课程号',
C.c_name '课程',
sc.s_score '分数',
row_number() over (partition by sc.c_id order by s_score desc) 'sort',
st.*
from Score sc
inner join Student st on sc.s_id = st.s_id
join Course C on sc.c_id = C.c_id) a
where sort in (2, 3);
-- 23. 使用分段(100-85,85-70,70-60,<60)来统计各科成绩，分别统计各分段人数，课程 id 和课程名称
select c.c_id '课程 id',
c.c_name '课程名称',
sum(IF(sc.s_score <= 100 and sc.s_score > 85, 1, 0)) '100-85',
sum(IF(sc.s_score <= 85 and sc.s_score > 70, 1, 0)) '85-70',
sum(IF(sc.s_score <= 70 and sc.s_score > 60, 1, 0)) '70-60',
sum(IF(sc.s_score < 60, 1, 0)) '<60'
from score as sc
inner join Course c on sc.c_id = c.c_id
group by c.c_id, c.c_name;
-- 24. 查询学生的平均成绩及其名次
-- 可以没有 partition by
select s.s_id '学号', st.s_name '姓名', avg(s_score) '平均分', rank() over (order by avg(s.s_score) desc) 'rank'
from Score s
join Student st on s.s_id = st.s_id
group by s.s_id;
-- 27. 查询出只有两门课程的全部学生的学号和姓名
select s_id '学号', s_name '姓名'
from Student
where s_id in (select s.s_id
from Score s
group by s.s_id
having count(distinct s.c_id) = 2);
-- 28. 查询男女生人数
-- 两行
select s_sex, count(s_sex)
from Student
group by s_sex;
-- 列转行，同一行展示
select sum(if(s_sex = '男', 1, 0)) '男生数', sum(if(s_sex = '女', 1, 0)) '女生数'
from Student;
-- 31. 查询 1990 年出生的学生名单
-- 日期 varchar 存储时（不推荐）
select *
from Student
where s_birth like '1990%';
-- year() 函数，兼容多种格式:
-- YYYY-MM-DD
-- YYYYMMDD
-- YYYY/MM/DD
-- YYMMDD
select *
from Student
where year(s_birth) = 1990;
-- 35. 查询所有学生的课程和分数情况
select s.s_id '学号',
s2.s_name '姓名',
max(if(c.c_name = '数学', s.s_score, null)) '数学成绩',
max(if(c.c_name = '语文', s.s_score, null)) '语文成绩',
max(if(c.c_name = '英语', s.s_score, null)) '英语成绩',
max(if(c.c_name = '奥数', s.s_score, null)) '奥数成绩'
from Score s
inner join Course c on s.c_id = c.c_id
inner join Student s2 on s.s_id = s2.s_id
group by s.s_id, s2.s_name;
-- 40. 查询选修张三老师所教课程的学生中最高的学生的姓名和成绩
explain
select *
from (select sc.c_id '课程号',
c.c_name '课程名',
sc.s_score '成绩',
st.s_id '学号',
st.s_name '姓名',
rank() over (partition by sc.c_id order by s_score desc) 'rank'
from Score sc
inner join Course c on sc.c_id = c.c_id
inner join Student st on sc.s_id = st.s_id
inner join Teacher t on c.t_id = t.t_id
where t.t_name = '张三') a
where `rank` = 1;
-- 41. 查询所有课程成绩相同的学生的学号、课程号、成绩
select s_id '学号', c_id '课程号', s_score '成绩'
from Score
where s_id =
(select a.s_id as_id
from (select s_id, s_score, count(*) count
from Score
group by s_id, s_score) a
where count > 1);
-- 45. 查询选修了全部课程的学生信息
select *
from Student
where s_id = (select s_id
from Score
group by s_id
having count(distinct c_id) = (select count(*) from Course));
-- 46. 查询各学生的年龄
-- datediff（a,b）两个时间相差几天
-- round 四舍五入取整
-- floor 向下取整
select s_id, s_name, s_birth, floor(DATEDIFF(now(), s_birth) / 365)
from Student;
-- 48. 查询下周过生日的学生
select *, week('1990-05-20', 1), week('2019-05-20', 1), concat(year(now()), substring(s_birth, 5, 6))
from Student
-- week函数对于不同年份会有一点问题
# where week(s_birth,1) = week(now(),1)+1
where week(now(), 1) + 1 = week(concat(year(now()), substring(s_birth, 5, 6)), 1);
-- 50. 查询下个月过生日的学生
select *
from Student
# where month(s_birth) = if(month(now()) = 12, 1, month(now()) + 1)
where month(s_birth) = month(date_add(now(),interval 1 month ));
|
都将会
收录于 Practice