MySQL经典45题（二）

21. 查询男生、女生人数

SELECT ssex,count(1) as 人数
from Student
GROUP BY ssex

 22. 查询名字中含有「风」字的学生信息

SELECT *
FROM Student
WHERE sname like '%风%'

23. 查询同名同性学生名单，并统计同名人数

解题思路：按照姓名分组，姓名形同的情况下按照性别分组统计人数，如果统计人数大于等于1，那说明这个人就是同名同性的

SELECT sname,ssex,count(sname)
FROM Student
GROUP BY sname,ssex
HAVING count(sname)>1

24. 查询 1990 年出生的学生名单

SELECT * 
FROM Student
WHERE year(sage)='1990'

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

SELECT cid,avg(score) as avg_score
FROM SC
GROUP BY cid
ORDER BY avg_score DESC,cid ASC

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

SELECT 
a.sid,
b.sname,
a.avg_score
FROM
(SELECT sid,avg(score) as avg_score
FROM SC
GROUP BY sid
HAVING avg(score)>=85 ) a left join Student b
on a.sid=b.sid 

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

SELECT b.sname, a.score 
FROM SC a
left join Student b on a.sid=b.sid
WHERE a.cid IN
(SELECT cid FROM Course WHERE cname='数学') and a.score<60

 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

SELECT a.*,b.*
FROM Student a
left join SC b
on a.sid=b.sid

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

SELECT b.sname,c.cname,a.score
FROM SC a LEFT JOIN Student b 
on a.sid=b.sid LEFT JOIN Course c on a.cid=c.cid
WHERE a.score>70

 30. 查询不及格的课程

SELECT distinct cid 
FROM SC
WHERE score<60

 31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

SELECT a.sid,b.sname
FROM
(SELECT sid
from SC
where cid='01' and score>=80) a left JOIN Student b on a.sid=b.sid

 32. 求每门课程的学生人数

select cid,
count(1) as con
from SC
group by cid

 33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

解题思路：4张表连接起来，按成绩排序降序，取第一条记录，就是成绩最高的

SELECT a.*,b.score
FROM Student a  INNER JOIN SC  b on a.sid=b.sid
INNER JOIN Course c on b.cid=c.cid 
INNER JOIN Teacher d on c.tid=d.tid
WHERE d.Tname='张三'
ORDER BY b.score desc 
LIMIT 1

34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩（Mysql版本太低，无法使用窗口函数，略）

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩（Mysql版本太低，无法使用窗口函数，略）

36. 查询每门功成绩最好的前两名（Mysql版本太低，无法使用窗口函数，略）

37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。

SELECT cid,count(1) as cons 
FROM SC
GROUP BY cid
HAVING count(1)>5

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

SELECT sid,count(1) as cons 
FROM SC
GROUP BY sid
HAVING count(1)>=2

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

SELECT * from Student where sid IN
(select sid from  SC  GROUP BY sid HAVING COUNT(*)=
(SELECT count(cid) FROM Course) )

 40. 查询各学生的年龄，只按年份来算

select *, year(NOW())-year(sage) as 年龄
from Student

  41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select *, TIMESTAMPDIFF(YEAR,sage,NOW()) as 年龄
from Student

 42. 查询本周过生日的学生

select *,week(Sage),week(now()) 
from Student 
where week(Sage)=week(now());

 43. 查询下周过生日的学生

select *,week(Sage),week(now()) 
from Student 
where week(Sage)=week(now())+1;

 44. 查询本月过生日的学生

select *,month(Sage),month(now()) 
from Student 
where month(Sage)=month(now());

 45. 查询下月过生日的学生

select *,month(Sage),month(now()) 
from Student 
where month(Sage)=month(now())+1;