【无标题】 MySQL部门表1~45道（二）由浅到深

MySQL部门表1~45道（二）由浅到深

---

提示：写完文章后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

提示：这里可以添加本文要记录的大概内容：

解题方法不唯一，有部分是解题的思路及其函数的使用方法，仅参考。

提示：以下是本篇文章正文内容，下面案例可供参考

一、21~30题

​
21.查询男生、女生人数
select s_sex,count(s_sex) '人数'
from student
GROUP BY s_sex

22.查询名字中含有「风」字的学生信息
select *
from student
where s_name like '%风%'

23.查询同名同性学生名单，并统计同名人数
// 按名字和性别分组，总计记录；用having过滤
select count(s_name)
from student 
GROUP BY s_name,s_sex
HAVING count(s_name)>1

24.查询 1990 年出生的学生名单
select *
from student
where DATE_FORMAT(s_birth,'%Y')='1990'

25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id,avg(s_score) '平均成绩'
from score
GROUP BY c_id
ORDER BY 平均成绩 desc,c_id asc

26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
// having 过滤平均成绩大于等于 85
select a.s_id,b.s_name,avg(a.s_score) '平均成绩'
from score a join student b on a.s_id=b.s_id
GROUP BY a.s_id,b.s_name
HAVING avg(a.s_score)>85

27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
// 先关联课程表和成绩表，条件是a.c_name='数学'--查看结果是，再关联学生表
select c.s_name,b.s_score
from course a join score b on a.c_id=b.c_id
join student c on c.s_id=b.s_id
where a.c_name='数学'
HAVING b.s_score<60

28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
// 学生表左关联成绩表
select a.*,b.c_id,b.s_score
from student a left join score b on a.s_id=b.s_id


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

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

二、31~40题

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

32.求每门课程的学生人数
select c_id,count(s_id) '学生人数'
from score
GROUP BY c_id

33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
// 先查出张三的所授课程c_id,在关联score表和student表
select DISTINCT n.s_id,m.s_name,m.s_birth,m.s_sex,n.c_id,n.s_score
from score n join student m on n.s_id=m.s_id
where n.c_id=(select b.c_id
from teacher a join course b on a.t_id=b.t_id
where t_name='张三')
ORDER BY n.s_score desc
LIMIT 1

34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select n.s_id,m.s_name,m.s_birth,m.s_sex,n.c_id,n.s_score
from score n join student m on n.s_id=m.s_id
where n.c_id=(select b.c_id
from teacher a join course b on a.t_id=b.t_id
where t_name='张三')
ORDER BY n.s_score desc
LIMIT 1
35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
// 自连接，where 成绩相等and课程不等
select a.s_id,a.c_id,a.s_score
from score a join score b on a.s_id=b.s_id
where a.s_score=b.s_score and a.c_id<>b.c_id
GROUP BY a.s_id,a.c_id,a.s_score

36.查询每门功成绩最好的前两名

// 1.对成绩表自连接,(a.c_id=b.c_id and a.s_score>b.s_score)是课程相同的成绩高排在前面
// 2.对课程升序，对成绩降序
// 3.使用dense_rank()over()对成绩进行排名
// 4.对排名进行约束
select *
from(
select DISTINCT a.s_id,a.c_id,a.s_score,dense_rank()over(partition by c_id ORDER BY s_score desc) '排名'
from score a join score b on a.c_id=b.c_id
where a.c_id=b.c_id and a.s_score>b.s_score
ORDER BY a.c_id asc,a.s_score desc
) c
where 排名<=2

37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select c_id,count(s_id)
from score
GROUP BY c_id

38.检索至少选修两门课程的学生学号
select s_id,count(c_id) '选修课程数'
from score 
GROUP BY s_id
HAVING count(c_id)>=2

39.查询选修了全部课程的学生信息
select d.*
from(
select a.s_id
from score a join student b on a.s_id=b.s_id
GROUP BY a.s_id
having count(a.c_id)=(select count(c_id) from course)) c 
join student d 
on c.s_id=d.s_id

40.查询各学生的年龄，只按年份来算
select *,date_format(now(),'%Y')-date_format(s_birth,'%Y') '年龄'
from student

三、41~45题

代码如下（示例）：

41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
// 用timestampdiff函数计算年龄，
// date_format函数截取月日进行判断
select *,TIMESTAMPdiff(year,s_birth,now()) '年龄'
from student
where date_format(now(),'%m-%d')<date_format(s_birth,'%m-%d')

//补充说明
-- timestampdiff函数是对日期差值的计算，date2-date1
timestampdiff(interval,date1,date2)
interval可以是一下的值：
year -- 年
month。-- 月
day -- 天
week -- 星期
quarter -- 季度
hour -- 小时
minute -- 分钟
second -- 秒

select timestampdiff(week,now(),'2022-06-24')
日期函数
year()
month()
day()
select day('2022-07-04')

42.查询本周过生日的学生
// 使用weekofyear()函数获取周数
select * 
from student
where weekofyear(s_birth)=weekofyear(now())


select weekofyear(now()) -- 获取现在的第几周
select curdate() -- 返回年月日
select now() -- 返回具体时间

43.查询下周过生日的学生
// date_add()让周数加一周
select * 
from student
where weekofyear(s_birth)=weekofyear(date_add(curdate(),interval 1 week))

//补充说明
select weekofyear('2022-07-07')=weekofyear(now())+1
select weekofyear(now())
select weekofyear(date_add(curdate(),interval 1 week))

44.查询本月过生日的学生
// 方法一
select *
from student
where date_format(now(),'%m')=date_format(s_birth,'%m')
// 方法二
select *
from student
where month(now())=month(s_birth)

45.查询下月过生日的学生
// 方法一
select *
from student
where MOD(date_format(now(),'%m')+1,12)=date_format(s_birth,'%m')
// 方法二
select *
from student
where month(s_birth)=month(date_add(now(),interval 1 month))

总结

MySQL有很多解题方法，学会使用不同的函数，能让你解题更快，SQL语句变得更优美简洁。