MYSQL练习篇21-30题

总结：21-30题的难度相对较小，需要下来加强的方面有时间函数及case when...then...else...end 一般需要结合统计函数使用，因为如果存在分组时，只会返回第一条数据

21、查询男生、女生人数
知识点：count计数

SELECT Ssex,count(*)
from student
GROUP BY Ssex

22、查询名字中含有「风」字的学生信息
知识点：模糊查询like及通配符%（任意个字符串），_（单个字符串）

SELECT*
FROM student
WHERE Sname like "%风";

23、查询同名同性学生名单，并统计同名人数
最初思路：只用到单张表，先查询出同名同姓的学生再去重统计
修改：关联两张相同表student
知识点：

select *
from student LEFT JOIN (
select Sname,Ssex,COUNT(*)同名人数 from Student group by Sname,Ssex) as t1
on student.Sname =t1.Sname and student.Ssex=t1.Ssex
where t1.同名人数>1

24、查询 1990 年出生的学生名单
知识点：时间函数year

时间函数

SELECT *
from student
where YEAR(Sage)='1990'

25、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
知识点：分组排序

SELECT cid,avg(score) '平均分'
from sc
GROUP BY cid
ORDER BY 平均分 desc,CId 

完善课程名称
SELECT sc.CId,course.Cname,avg(sc.score) '平均分'
from sc INNER JOIN course on sc.CId=course.CId
GROUP BY cid 
ORDER BY 平均分 desc ,CId

26、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
知识点：分组及having
有个疑问点：我把过滤条件放在最后

我的：
SELECT t1.SId,t1.Sname,avg(t2.score) as '平均分'
from student as t1
INNER JOIN sc as t2 on t1.SId=t2.SId
GROUP BY t1.SId
having 平均分>=85;

答案：
select student.SId,student.Sname,t1.avgscore
from student INNER JOIN 
(select sc.SId ,AVG(sc.score) as avgscore from sc GROUP BY sc.SId HAVING AVG(sc.score)>=85) 
as t1 on student.SId=t1.SId

27、查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT t1.Sname,t2.score
from student as t1
INNER JOIN sc as t2 on t1.SId=t2.SId
INNER JOIN course as t3 on t2.CId=t3.CId
where t3.Cname='数学' and t2.score<60

28、查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
考点：case when...then...else...end一般需要联合统计函数使用

select student.SId,sc.CId,sc.score
from Student  left join sc  on student.SId=sc.SId 

完善：
SELECT t1.SId,t1.Sname,t3.Cname,
max(case when t3.Cname='语文' then t2.score else null END)as '语文',
max(case when t3.Cname='数学' then t2.score else null END)as '数学',
max(case when t3.Cname='英语' then t2.score else null END)as '英语'
FROM student as t1 
INNER JOIN sc as t2 on t1.SId=t2.SId
INNER JOIN course as t3 on t2.CId=t3.CId
GROUP BY t1.SId,t1.Sname

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

SELECT t1.Sname,t2.score,t3.Cname
from student as t1
INNER JOIN sc as t2 on t1.SId=t2.SId
INNER JOIN course as t3 on t2.CId=t3.CId
where t2.score>70

30、查询存在不及格的课程

SELECT *
from sc
where score<60

完善：
SELECT t2.sid,t2.Sname,t3.Cname,t1.score
from sc as t1
INNER JOIN student as t2 on t1.SId=t2.SId
INNER JOIN course as t3 on t1.CId=t3.CId
WHERE score<60