Mysql经典45道题目

最新推荐文章于 2023-11-26 11:22:03 发布

weixin_47600357

最新推荐文章于 2023-11-26 11:22:03 发布

阅读量384

点赞数 1

分类专栏： SQL 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_47600357/article/details/115177295

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

学完一段时间SQL，近期想通过重温SQL经典45题进行全面系统的复习，达到温故而知新的效果。
注：
1、SQL求解重在思路，很多时候同一种结果可能有多种写法
2、SQL重在练习！

此次练习主要有以下四个表：
学生表：
Student(s_id,s_name,s_birth,s_sex) – –学生编号,学生姓名, 出生年月,学生性别
课程表：
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
教师表：
Teacher(t_id,t_name) – –教师编号,教师姓名
成绩表：
Score(s_id,c_id,s_s_score) – –学生编号,课程编号,分数

在开始之前，我们需要先把4张表之间的关联关系搞清楚了（最好的办法是自己在草稿纸上画关联图，如下图所示），然后再编写对应的SQL语句就比较容易了。
在这里插入图片描述
一、创建数据库和表

为了演示题目的运行过程，我们先按下面语句在客户端Workbench中创建数据库和表。

1）创建学校数据库
在这里插入图片描述
2）创建四张表

--学生表
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`)
);

3）插入各表的测试数据

--插入学生表测试数据
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.查询" 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
1.1 查询同时存在” 01 “课程和” 02 “课程的情况
1.2 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
1.3 查询不存在” 01 “课程但存在” 02 “课程的情况
2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
3.查询在 SC 表存在成绩的学生信息
4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) 4.1 查有成绩的学生信息
5.查询「李」姓老师的数量
6.查询学过「张三」老师授课的同学的信息
7.查询没有学全所有课程的同学的信息
8.查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
9.查询和” 01 “号的同学学习的课程完全相同的其他同学的信息
10.查询没学过"张三"老师讲授的任一门课程的学生姓名
11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
12.检索” 01 "课程分数小于 60，按分数降序排列的学生信息
13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
14.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
18.查询各科成绩前三名的记录
19.查询每门课程被选修的学生数
20.查询出只选修两门课程的学生学号和姓名
21.查询男生、女生人数
22.查询名字中含有「风」字的学生信息
23.查询同名同性学生名单，并统计同名人数
24.查询 1990 年出生的学生名单
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
30.查询不及格的课程
31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
32.求每门课程的学生人数
33.假设成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
34.假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
36.查询每门功成绩最好的前两名
37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
38.检索至少选修两门课程的学生学号
39.查询选修了全部课程的学生信息
40.查询各学生的年龄，只按年份来算
41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
42.查询本周过生日的学生
43.查询下周过生日的学生
44.查询本月过生日的学生
45.查询下月过生日的学生

准备工作完成，进入答题环节。

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

select * from score s 
left join student st
    on s.s_id=st.s_id 
inner join score b 
    on s.s_id=b.s_id and s.c_id='01' and b.c_id='02'
where s.s_score>b.s_score;

select a.s_id,st.s_name,a.s_score,b.s_score from (
(select * from score where c_id='01') a
inner join 
(select * from score where c_id='02' )b on a.s_id=b.s_id)
inner join 
student st on st.s_id=a.s_id
group by a.s_id having a.s_score>b.s_score

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

提示：左边是01课程的记录，右边是02课程的记录，s_id 能关联上的就是说明 01 ，02 课程记录都有，用子查询+inner jion

select * from (select * from score where c_id='01') a 
inner join (select * from score where c_id='02') b 
on a.s_id=b.s_id;

select * from (score a 
inner join score b 
on a.s_id=b.s_id )
where a.c_id='01' and b.c_id='02';

select * from score a 
inner join score b 
on a.s_id=b.s_id and a.c_id='01' and b.c_id='02';

select * from score s 
left join student st
    on s.s_id=st.s_id 
inner join score b 
    on s.s_id=b.s_id and s.c_id='01' and b.c_id='02'

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

提示: 先找出存在 01 的课程记录，然后和其他课程做关联，如果是 02 就关联上了，如果不是 02 就关联不上，用left join 。

select * from (select * from score where c_id='01') a 
left join score b on a.s_id=b.s_id and b.c_id='02';

select * from score a left join score b on a.s_id=b.s_id  and b.c_id='02' where a.c_id='01'

select * from (
(select * from score where c_id='01') a
left join ((select * from score where c_id='02') b) on a.s_id=b.s_id
)

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

提示：找出不存在01课程的记录后，然后再进行关联找存在02课程，可以用子查询筛选+inner join

select   * from (select * from score where s_id not in (select s_id from score where c_id='01')) a 
inner join score b 
on a.s_id=b.s_id and b.c_id='02';

select
  *
from score a 
where s_id not in (select s_id from score where c_id='01')
and c_id='02';

select * from (
(select * from score where c_id='02') a
left join
(select * from score where c_id='01' ) b on a.s_id= b.s_id
) where b.s_id is null

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT 
    s_id, AVG(s_score) AS avg_sc
FROM
    score
GROUP BY s_id
HAVING AVG(s_score) >= 60;

select
  b.*
from 
(select
  s_id,
  avg(s_score) as avg_sc
from score 
group by s_id having avg(s_score)>=60) a
inner join student b
on a.s_id=b.s_id;

select a.s_id,st.s_name,avg(a.s_score) from(
(select * from score where s_score>=60) a 
inner join student st on st.s_id=a.s_id)
group by a.s_id

3.查询在 score 表存在成绩的学生信息

提示：成绩表肯定都是有学生的，所以用成绩表左关联就可以得到学生信息。

select * from student where s_id in (select s_id from score )

select 
    b.*
from score a
left join student b
on a.s_id=b.s_id
group by a.s_id;

select
  b.*
from 
(select 
    s_id
from score 
group by s_id) a
left join student b
on a.s_id=b.s_id;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)

提示：所有同学存在于学生表，课程数、成绩情况在成绩表，那就是要学生表左关联成绩汇总表得到记录，关联不上的说明就是没成绩。

SELECT 
    st.s_id,
    st.s_name,
    COUNT(DISTINCT (s.c_id)) '选课总数',
    SUM(s.s_score) '所有课程的总成绩'
FROM
    (student st
    LEFT JOIN score s ON st.s_id = s.s_id)
GROUP BY st.s_id , st.s_name

select 
a.s_id,
a.s_name,
count(b.c_id) as cons,
sum(b.s_score) as cours
from student a
left join score b
on a.s_id=b.s_id
group by a.s_id;

4.1 查有成绩的学生信息

SELECT st.*
FROM
    (student st
    inner JOIN score s ON st.s_id = s.s_id)
GROUP BY st.s_id

select
*
from student a
where a.s_id in (select s_id from score group by s_id);

select *
from student
where EXISTS(select * from score where student.s_id=score.s_id)

5.查询「李」姓老师的数量

select count(*) from teacher where t_name like '李%'

6.查询学过「张三」老师授课的同学的信息

select * from student where s_id 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 student.*
from (teacher  ,course  ,student,score )
where teacher.t_name='张三'
and   teacher.t_id=course.t_id
and   course.c_id=score.c_id
and   score.s_id=student.s_id

7.查询没有学全所有课程的同学的信息

select st.s_id, count(s.c_id)from student  st left join score s on st.s_id=s.s_id
group by st.s_id having count(s.c_id)<(select count(c_id) from course)

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

select distinct st.* 
from score s 
left join student st 
on s.s_id=st.s_id 
where c_id in (select c_id from score where s_id='01');

select * from student st where s_id in(
select s_id from score where c_id in (select c_id from score where s_id='01') group by s_id)

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

select s_id,count( c_id) from score  where c_id in (select c_id from score where s_id='01') group by s_id
 having count(c_id)= (select count(c_id) from score where s_id='01') and s_id !=01

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

select * 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='张三')))

**11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select  a.s_id, count(a.c_id),avg(a.s_score),st.s_name from(
select * from score where s_score<60) a 
inner join student st on st.s_id=a.s_id
group by a.s_id

12、检索" 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)

select *
from score a
left join student b
on a.s_id=b.s_id
where a.c_id='01' and a.s_score<60
order by a.s_score desc;

13、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select st.s_id, avg(s.s_score), a.s_score'01成绩',b.s_score'02成绩',c.s_score'03成绩'from student st
left join score s on s.s_id=st.s_id
left join (select * from score where c_id='01')a on a.s_id=s.s_id
left join (select * from score where c_id='02')b on b.s_id=s.s_id
left join (select * from score where c_id='03')c on c.s_id=s.s_id
group by s.s_id order by avg(s.s_score) desc

14、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select s.c_id'课程 ID',c.c_name'课程 name',count(s.s_id)'选修人数',max(s.s_score)'最高分',min(s.s_score)'最低分',avg(s.s_score)'平均分'
,sum(case when s.s_score>=60 then 1 else 0 end)/count(s.s_score) '及格率'
,sum(case when s.s_score>=70 and s.s_score<80 then 1 else 0 end)/count(s.s_score) '中等率'
,sum(case when s.s_score>=80 and s.s_score<90 then 1 else 0 end) /count(s.s_score)'优良率'
,sum(case when s.s_score>=90 then 1 else 0 end) /count(s.s_score)'优秀率'
from score s
inner join course c on s.c_id=c.c_id group by s.c_id
order by count(s.s_id) desc, s.c_id asc

15、按各科成绩进行排序，并显示排名， Score 重复时也继续排名`

select 
    s_id,c_id,s_score,
    @rank:=@rank+1 as rn 
from score ,(select @rank:=0) as t order by s_score desc;

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

select 
  *,
  case when (@s_score=s_score) then @rank 
       else @rank:=@rank+1 end as rn,
  @s_score:=s_score  -- 保存上一次的分数
from score ,(select @rank:=0,@s_score:=null) as t order by s_score desc

16、查询学生的总成绩，并进行排名，总分重复时保留名次空缺

-- 1、先求学生总成绩
select 
    s_id,
    sum(s_score) as scos 
from score s
group by s_id 
order by sum(s_score) desc;

-- 2、使用自定义变量来记录排名值
select 
    y.*, 
    @rank:=if(@s_score=scos,'',@rank+1) as rn , 
    @s_score:=scos 
from (select 
        s_id,
        sum(s_score) as scos 
    from score group by s_id order by sum(s_score) desc) y
    , (select @rank:=0,@s_score:=null) as t;

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
这个空缺的定义比较模糊，看看就好。

select 
    s.*, 
    @rank:=if(@s_score=scos,@rank,@rank+1) as rn , 
    @s_score:=scos 
from (select 
        s_id,
        sum(s_score) as scos 
    from score group by s_id order by scos desc) s
    , (select @rank:=0,@sco:=null) as t;

17、统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[7060]，[60-0] 及所占百分比

提示：分组统计数据，判断数据进行累加统计

select s.c_id'课程编号',c.c_name'课程名称',count(s.s_id)'人数'
,sum(case when s.s_score<60 then 1 else 0 end )'[60-0]'
,sum(case when s.s_score>=60 and s.s_score<70 then 1 else 0 end )'[70-60]'
,sum(case when s.s_score>=70 and s.s_score<85 then 1 else 0 end )'[85-70]'
,sum(case when s.s_score>=85 and s.s_score<100 then 1 else 0 end )'[100-85]'
 from score s 
 inner join course c on c.c_id=s.c_id
 group by s.c_id

select s.c_id'课程编号',c.c_name'课程名称',count(s.s_id)'人数'
,sum(case when s.s_score<60 then 1 else 0 end )/count(s.s_id)'[60-0]'
,sum(case when s.s_score>=60 and s.s_score<70 then 1 else 0 end )/count(s.s_id)'[70-60]'
,sum(case when s.s_score>=70 and s.s_score<85 then 1 else 0 end )/count(s.s_id)'[85-70]'
,sum(case when s.s_score>=85 and s.s_score<100 then 1 else 0 end )/count(s.s_id)'[100-85]'
 from score s 
 inner join course c on c.c_id=s.c_id
 group by s.c_id

18、查询各科成绩前三名的记录（无水印）
思路：前三名转化为若大于此成绩的数量少于3即为前三名。

select 
*
from score s
where (select count(1) from score b where s.c_id=b.c_id and b.s_score>s.s_score)<3;

19、查询每门课程被选修的学生数

select c_id, count(1) from score group by c_id

20、查询出只选修两门课程的学生学号和姓名

提示：分组查询，根据分组情况进行筛选

select s.s_id, st.s_name,count( s.c_id) from score s 
inner join student st on s.s_id=st.s_id
group by s_id having count( c_id)=2

21.查询男生、女生人数

提示:根据性别分组就好


select s_sex, count(s_sex) from student group by s_sex

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

提示：使用 like 语法

select * from student where s_name like '%风%'

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

是不是需要一条记录和学生表的其他记录都去关联然后判断是不是同名同姓？
提示：自关联筛选出符合条件的记录，要求名字，性别相等但是s_id不同

select
 a.* 
from student a 
inner join student b 
on a.s_name=b.s_name and a.s_sex=b.s_sex and a.s_id!=b.s_id;

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

select * from student where s_birth like "1990%"

select * from student where year(s_birth)=1990

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

提示：分组聚合然后排序

select c_id,avg(s_score) from score group by c_id order by avg(s_score) desc, c_id asc

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

提示：需要分组聚合求平均值，然后筛选学生信息

select s.s_id,st.s_name,avg(s.s_score) from score s inner join student st on s.s_id=st.s_id 
group by s.s_id  having avg(s.s_score)>=85

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

提示：条件关联查询

select s.s_id,st.s_name ,s.s_score from score s 
inner join student st on s.s_id=st.s_id
where s.c_id=(
select c_id from course where c_name='数学') and s.s_score<60

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

提示：给学生信息加上课程情况

select st.s_id,st.s_name
,max(case when s.c_id='01' then s.s_score else null end )'01成绩'
,max(case when s.c_id='02' then s.s_score else null end )'02成绩'
,max(case when s.c_id='03' then s.s_score else null end )'03成绩'
 from student st inner join score s on st.s_id=s.s_id
group by s_id, s_name

select
 * 
from student a 
left join score b 
on a.s_id=b.s_id;

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

select st.s_name,c.c_name,s.s_score from  score s 
inner join student st on s.s_id=st.s_id
inner join course c on c.c_id=s.c_id
where s_score>70
order by s.s_score desc

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

select * from score where s_score<60

select
  a.*
from course a 
where a.c_id in (select c_id from score where s_score<60);

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

提示：关联筛选

select s.s_id,st.s_name,s.c_id, s.s_score from score  s 
 inner join student st on st.s_id=s.s_id
 where s.c_id='01'  and s.s_score>80

32、求每门课程的学生人数
提示：分组统计计数

select c_id, count(s_id) from score group by c_id

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

提示：条件筛选查询，只需要一个人

select * from student where s_id=(
select s_id from score where c_id=(
select c_id from course where t_id=(
select t_id from teacher where t_name='张三')
)
order by s_score desc limit 1)

34、成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
成绩重复，也就是有可能第一名是同分数的，也都要查询出来，这种也可以通过 count(*)<1 来实
修改为得到数据后，对子查询表进行排名编号，这样排名是对的
提示：使用分组排序

select 
a.*,
case when @s_score=s_score then @rank
when @s_score:=s_score then @rank:=@rank+1 end as rn
from 
(select 
a.s_id,
a.s_score,
c.c_id,
d.t_name
from score a
left join course c
on a.c_id=c.c_id 
left join teacher d
on c.t_id=d.t_id 
where d.t_name='张三') a
,(select @s_score:=null,@rank:=0) as t
order by a.s_score desc;

35、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
–有一个错误，没有重录

提示：条件关联，条件是 c_id 不同，s_id 相同，但是成绩相同

select 
*
from score a
inner join score b
on a.s_id=b.s_id
where a.c_id!=b.c_id and a.s_score=b.s_score
group by a.s_id ,a.c_id

select *
from score as t1
where exists(select * from score as t2 
        where t1.s_id=t2.s_id 
        and t1.c_id!=t2.c_id 
        and t1.s_score =t2.s_score )

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

提示：用户变量记录排名

select 
*
from score a
where (select count(1) from score b where a.c_id=b.c_id and b.s_score>a.s_score)<=1;

select *
from score as t1
where (select count(*) from score as t2 
    where t1.c_id=t2.c_id and t2.s_score >t1.s_score)<2
ORDER BY t1.c_id

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

提示：分组统计

select 
  *,
  count(*) 
from score 
group by c_id 
having count(*)>5;

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

提示：分组查询

select s_id from score group by s_id having count(*)>=2;

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

提示：条件筛选

select 
*
from student a
where (select count(1) from score b where a.s_id=b.s_id)
    =(select count(1) from course)

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

提示：使用 year函数

select *, (year(now())-year(s_birth)) as age from student;

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

提示：使用日期函数相减

select 
    student.s_id as 学生编号,
    student.s_name  as  学生姓名,
    TIMESTAMPDIFF(YEAR,student.s_birth,CURDATE()) as 学生年龄
from student

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

提示：使用 week 函数

select 
*,
substr(YEARWEEK(student.s_birth),5,2) as birth_week,
substr(YEARWEEK(CURDATE()),5,2) as now_week
from student 
where substr(YEARWEEK(student.s_birth),5,2)=substr(YEARWEEK(CURDATE()),5,2);

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

select 
*,
substr(YEARWEEK(student.s_birth),5,2) as birth_week,
substr(YEARWEEK(CURDATE()),5,2) as now_week
from student 
where substr(YEARWEEK(student.s_birth),5,2)=substr(YEARWEEK(CURDATE()),5,2)+1;

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

select 
*,
month(s_birth) as birth_month,
month(now()) as now_month
from student
where month(s_birth)=month(now())

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

select 
*,
month(s_birth) as birth_month,
month(now()) as now_month
from student
where month(s_birth)=month(now())+1