Mysql经典50题--第一弹_msyql经典50题

根据流传着各种版本的Mysql 50题，做一份自己的解答跟进；
已经是第二遍再次回顾这部分题目，如有错误欢迎纠正～

附第二弹题库链接：
SQL题库–45题版
——————————————————————————————————————————
另附个人期间所使用工具
sqlfiddle在线工具
网页版方便个人在闲暇时间的小题目练习

————————————————

正文

建表

表名和字段
–1.学生表
Student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名, 出生年月,学生性别
–2.课程表
Course(c_id,c_name,t_id) – --课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) --教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) --学生编号,课程编号,分数

--建表
--学生表
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
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

题目及解答

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
    select a.* , b.s_score from Student  as a
    join Score as b on a.s_id = b.s_id
    join Score as c on b.s_id = c.s_id
    where b.c_id = '01' and c.c_id = '02' and b.s_score > c.s_score
        
 

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数    
   select a.* ,b.s_score from student as a 
   join score as b on a.s_id=b.s_id
   join score as c on b.s_id=c.s_id
   where b.c_id = '01' and c.c_id = '02' and b.s_score < c.s_score
       		 
       		 
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
  select a.s_id, a.s_name , AVG(b.s_score) as avg_score from student as a 
  join score as b on a.s_id = b.s_id
  GROUP BY  a.s_id , a.s_name HAVING  avg_score >=60
       

-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
  select a.s_id ,a.s_name ,AVG(b.s_score)as avg_score from student as a
 join score as b on a.s_id = b.s_id
 GROUP BY a.s_id, a.s_name HAVING avg_score <60 
 union
 select a.s_id,a.s_name,0 as avg_score from student as a 
 where a.s_id not in (select distinct s_id from score)
        
	        
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
 select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) 
 as sum_score from  student as a 
 left join score b on a.s_id=b.s_id
 GROUP BY a.s_id,a.s_name;


-- 6、查询"李"姓老师的数量 
 select count(t_name) from teacher 
 where t_name like '李%'


-- 7、查询学过"张三"老师授课的同学的信息 
	  select * from student  
 		 where s_id in (select s_id from score 
        		where c_id in (select c_id from course
              		where t_id in (select t_id from teacher
                     		 where t_name = '张三')))

-- 8、查询没学过"张三"老师授课的同学的信息
  select * from student 
	where s_id in (select s_id from score 
   		where c_id not in (select c_id from course
      	   where t_id in (select t_id from teacher 
     		 where t_name = '张三')))

-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student as a
join score as b on a.s_id=b.s_id and b.c_id = '01'
join score as c on c.s_id=b.s_id and c.c_id = '02'


-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
  		
select * from student 
  where s_id in (select s_id from score where c_id = '01')
    and s_id not in (select s_id from score where c_id ='02')


-- 11、查询没有学全所有课程的同学的信息 
select a.* from student as a left join score as b on a.s_id=b.s_id
group by a.s_id having  count(b.c_id)  < (select count (*) from course)
--提示：使用left join保留student 表中一条数据，join会缺失（无分数）


-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 
select a.* from student as a 
join score as b on a.s_id = b.s_id
where b.c_id in (select c_id from score where s_id ='01')
group by a.s_id


-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 
-- 第二遍开始思路仍然是模糊的哈哈
select * from student 
where s_id in 
(select s_id from score group by s_id HAVING COUNT(s_id)=
-- 下面的语句是找到'01'同学学习的课程数
 (select count (c_id) from score where s_id='01'))
 
 
 and s_id not in
-- 下面的语句是找到学过‘01’同学没学过的课程，有哪些同学。并排除他们
 (select s_id from score where c_id in (
-- 下面的语句是找到‘01’同学没学过的课程
   select distinct c_id from score
 where c_id not in (
-- 下面的语句是找出‘01’同学学习的课程
   select c_id from score where s_id='01')
   group by s_id)) 
   
   
-- 下面的条件是排除01同学   
     and s_id not in ('01')


-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 
select s_name from student 
where s_id  not in (select s_id from score 
  where c_id in (select c_id from course 
     where t_id in (select t_id from teacher
        where t_name = '张三')))


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

--个人解法1，错误方法，暂未发现错误，欢迎纠正～
    select a.s_id,a.s_name,avg(b.s_score) as avg_score from student as a
    join score as b on a.s_id = b.s_id 
    group by a.s_id 
    having (select count 
           (case when (b.s_score < 60 ) then 1 else 0 end )from score)>=2
           
    
   
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


-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select * from student 
where s_id in 
(select s_id from score 
 where c_id = '01' and s_score <60
 order by s_score desc)


-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_id,
(select s_score from score where a.s_id=s_id and c_id='01') as 语文,
(select s_score from score where a.s_id=s_id and c_id='02') as 数学,
(select s_score from score where a.s_id=s_id and c_id='03') as 英语,
 avg(a.s_score) as avg_score from score as a 
group by a.s_id
order by avg_score desc


-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：
-- 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
-- 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
--原解法使用round四舍五入。也可以使用cast优化显示
select a.c_id,b.c_name,max(a.s_score),min(a.s_score),avg(a.s_score) as avg_score,
(sum (case when a.s_score >=60 then 1 else 0 end) / sum(case when a.s_score then 1 else 0 end)) as 及格率,
(sum (case when 70<a.s_score and a.s_score<80 then 1 else 0 end) / sum(case when a.s_score then 1 else 0 end)) as 中等率,
(sum (case when 80<a.s_score and a.s_score<90 then 1 else 0 end) / sum(case when a.s_score then 1 else 0 end)) as 优良率,
(sum (case when 90<= a.s_score then 1 else 0 end)/ sum (case when a.s_score then 1 else 0 end)) as 优秀率
from score as a join course as b on a.c_id=b.c_id
group by a.c_id

-- 19、按各科成绩进行排序，并显示排名
-- 筛选课程号‘01’的排序
select * from  
(select t1.c_id,t1.s_score,
-- 选择2号score表中不重复的项目，条件为，2号score表中的分数大于1号score表并且课程号是‘01’，注释为rank
(select count(distinct t2.s_score) from score t2 where t2.s_score>=t1.s_score and t2.c_id='01') rank
-- 大条件限制为1号score表中课程号为‘01’的数据
FROM score t1 where t1.c_id='01'
order by t1.s_score desc) t1

union
-- 筛选课程号‘02’的排序
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
-- 筛选课程号‘03’的排序
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

-- ADD：还有一种全局变量赋值的解法，还没研究完毕 后续有机会补充

-- 20、查询学生的总成绩并进行排名
select s_id 
  @r := @r + 1 as i
  @s := (case when @score =a.sum_score then @s else @r end) as rank
  @score := a.sum_score as score 
from(select s_id ,sum(s_score) as sum_score from score group by s_id order by sum_score desc) as a
  (select @k:=0,@i:=0,@score:=0) as s
  --sql fiddle 不支持，需mysql跑

-- 21、查询不同老师所教不同课程平均分从高到低显示 
select c.t_id , b.c_id , avg(a.s_score) as avg_score from score as a 
left join course as b on a.c_id = b.c_id
left join teacher as c on b.t_id = c.t_id
group by c.t_id 
order by avg_score desc

-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select a.c_id ,a.s_score from score as a
where (select count(1) from score as b 
       where b.c_id=a.c_id and b.s_score >=a.s_score) =2
   or (select count(1) from score as c
       where c.c_id=a.c_id and c.s_score >=a.s_score) =3

-- 23、统计各科成绩各分数段人数：
-- 课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
-- 第二次做题的遗漏点是，忘记了筛选和join的匹配，遗失了几行数据
select a.c_id,'85-100',百分比1,'70-85',百分比2,'60-70',百分比3,'0-60',百分比4 from score as a
 join (select c_id, sum(case when s_score>85 and s_score <=100 then 1 else 0 end) as '85-100',
 round ('85-100'/count(*),2) as 百分比1 from score group by c_id) as b on a.c_id=b.c_id  
 join (select c_id, sum(case when s_score>70 and s_score <=85 then 1 else 0 end) as '70-85',
 round ('70-85'/count(*),2) as 百分比2 from score group by c_id) as c on a.c_id=c.c_id 
 join (select c_id, sum(case when s_score>60 and s_score <=70 then 1 else 0 end) as '60-70',
 round ('60-70'/count(*),2) as 百分比3 from score group by c_id) as d on a.c_id=d.c_id 
 join (select c_id, sum(case when s_score>=0 and s_score <=60 then 1 else 0 end) as '0-60',
 round ('0-60'/count(*),2) as 百分比4 from score group by c_id) as e on a.c_id=e.c_id 
 group by a.c_id


-- 24、查询学生平均成绩及其名次 
-- 全局变量解法典型例题    
select a.s_id,
				@i:=@i+1 as '不保留空缺排名',
				@k:=(case when @avg_score=a.avg_s then @k else @i end) as '保留空缺排名',
				@avg_score:=avg_s as '平均分'
		from (select s_id,ROUND(AVG(s_score),2) as avg_s from score GROUP BY s_id ORDER BY avg_s DESC)a,(select @avg_score:=0,@i:=0,@k:=0)b;


-- 25、查询各科成绩前三名的记录
-- 1.选出b表比a表成绩大的所有组
-- 2.选出比当前id成绩大的 小于三个的           
select * from score as a join score as b 
on a.s_id=b.s_id and b.s_score>a.s_score
group by a.s_id 
having count(b.s_score)<3
order by b.s_score desc


-- 26、查询每门课程被选修的学生数
select c_id,count(s_id) from score a GROUP BY c_id
    

-- 27、查询出只有两门课程的全部学生的学号和姓名 
select a.s_id,b.s_name from score as a 
join student as b on a.s_id=b.s_id
group by a.s_id 
    having count(s_score)=2


-- 28、查询男生、女生人数 
select s_sex, count(*) from student 
group by s_sex


-- 29、查询名字中含有"风"字的学生信息
select * from student
where s_name like '%风%'
       

-- 30、查询同名同性学生名单，并统计同名人数 
select *,count(*) from student as a
join student as b on a.s_name=b.s_name and a.s_sex=b.s_sex
group by a.s_name,a.s_sex 


-- 31、查询1990年出生的学生名单
select s_name from student 
where s_birth like '1990%'
      
      
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列， 平均成绩相同时，按课程编号升序排列 
select c_id,round(avg(s_score),2) as avg_score from score 
group by c_id
order by avg(s_score) desc,c_id asc      


-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
select a.s_id,a.s_name,avg(b.s_score) as avg_score from student as a 
join score as b on a.s_id=b.s_id
group by a.s_id 
having avg_score>=85
    
                
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 
select a.s_name ,b.s_score from student as a 
join score as b on a.s_id = b.s_id
where b.c_id in (select c_id from course 
where c_name='数学') and b.s_score<60
   
   
-- 35、查询所有学生的课程及分数情况； 
select a.c_id ,
sum(case when b.c_name='语文' then a.s_score else 0 end) as '语文',
sum(case when b.c_name='数学' then a.s_score else 0 end) as '数学',
sum(case when b.c_name='英语' then a.s_score else 0 end) as '英语',
sum(a.s_score)as '总分'
from score as a join course as b on a.c_id=b.c_id


 -- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
 select c.s_name ,b.c_name ,a.s_score from score as a
 join course as b on a.c_id=b.c_id
 join student as c on a.s_id=c.s_id
 where a.s_score>=70


-- 37、查询不及格的课程
select a.c_id,b.c_name,a.s_score from score a 
left join course b on a.c_id = b.c_id
where a.s_score<60 


-- 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select a.s_id ,a.s_name from student as a 
join score as b on a.s_id=b.s_id
where b.c_id='01' and b.s_score >80


-- 39、求每门课程的学生人数 
select a.c_id ,b.c_name,count(*) from score as a
join course as b on a.c_id=b.c_id
group by a.c_id


-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.*,max(b.s_score)  from student as a
join score as b on a.s_id=b.s_id
where c_id in (select c_id from course
   where t_id in (select t_id from teacher 
        where t_name = '张三'))


-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
select  distinct a.s_id,a.c_id,a.s_score from score as a
join score as b on a.c_id != b.c_id and a.s_score =b.s_score


-- 42、查询每门功成绩最好的前两名 
 select a.s_id ,a.c_id ,a.s_score from score as a
 where (select count(1) from score as b 
   where b.c_id=a.c_id and b.s_score >=a.s_score ) <=2
 order by a.c_id


-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
select c_id ,count(*) as total  from score 
group by c_id
having total>5  
order by total desc ,c_id asc


-- 44、检索至少选修两门课程的学生学号 
select s_id,count(s_score)as tol from score
group by s_id
having tol >=2



-- 45、查询选修了全部课程的学生信息 
select * from student 
where s_id in ( select s_id from score group by s_id having count(*) =(select count(*) from course))


-- 46、查询各学生的年龄
-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select 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 from student


-- 47、查询本周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=WEEK(s_birth)


-- 48、查询下周过生日的学生
select * from student where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =WEEK(s_birth)
        

-- 49、查询本月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d')) =MONTH(s_birth)

	
-- 50、查询下月过生日的学生
select * from student where MONTH(DATE_FORMAT(NOW(),'%Y%m%d'))+1 =MONTH(s_birth)
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
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397

Finish~~~