30道经典笔试SQL题

男娘i 2024-03-09 00:45 50阅读 0赞

-- 创建数据库并指定
    create database sql_30;
    use `sql_30`;
    
    -- 创建表和插入数据(windows不区分大小写，linux区分大小写)
    create table student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
    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' , '女');
    create table course(cid varchar(10),cname varchar(10),tid varchar(10));
    insert into course values('01' , '语文' , '02');
    insert into course values('02' , '数学' , '01');
    insert into course values('03' , '英语' , '03');
    create table teacher(tid varchar(10),tname varchar(10));
    insert into teacher values('01' , '张三');
    insert into teacher values('02' , '李四');
    insert into teacher values('03' , '王五');
    create table sc(sid varchar(10),cid varchar(10),score decimal(18,1));
    insert into sc values('01' , '01' , 80);
    insert into sc values('01' , '02' , 90);
    insert into sc values('01' , '03' , 99);
    insert into sc values('02' , '01' , 70);
    insert into sc values('02' , '02' , 60);
    insert into sc values('02' , '03' , 80);
    insert into sc values('03' , '01' , 80);
    insert into sc values('03' , '02' , 80);
    insert into sc values('03' , '03' , 80);
    insert into sc values('04' , '01' , 50);
    insert into sc values('04' , '02' , 30);
    insert into sc values('04' , '03' , 20);
    insert into sc values('05' , '01' , 76);
    insert into sc values('05' , '02' , 87);
    insert into sc values('06' , '01' , 31);
    insert into sc values('06' , '03' , 34);
    insert into sc values('07' , '02' , 89);
    insert into sc values('07' , '03' , 98);
    
    # 1、查询 “01” 课程比 “02” 课程成绩高的学生的学号、姓名
    -- 1.查出每个学生课程01的成绩
    select * from sc where cid = '01';
    -- 2.查出每个学生课程02的成绩
    select * from sc where cid = '02';
    -- 3.联合查询(使用内连接：没修满两科的不比较)
    select t1.sid, t1.score, t2.score
    from (select * from sc where cid = '01') t1
    inner join (select * from sc where cid = '02') t2
    on t1.sid = t2.sid
    where t1.score > t2.score;
    -- 4.附加关联学生表的学号、姓名
    select student.sid, sname from student
    inner join(
        select t1.sid, t1.score score01, t2.score score02
        from (select * from sc where cid = '01') t1
        inner join (select * from sc where cid = '02') t2
        on t1.sid = t2.sid
        where t1.score > t2.score) t
    on student.sid = t.sid;
    
    -- 1.直接通过if组合列
    select sid, sum(if(cid = '01', score, null)) score01, sum(if(cid = '02', score, null)) score02
    from sc
    group by sid
    having score01 > score02; # null列不会进行比较
    -- 1.直接通过case when then [else] end组合列
    select sid, sum(case cid when '01' then score end) score01, sum(case cid when '02' then score end) score02
    from sc
    group by sid
    having score01 > score02;
    -- 2.附加关联学生表的学号、姓名
    select student.sid, sname from student
    inner join(
        select sid, sum(if(cid = '01', score, null)) score01, sum(if(cid = '02', score, null)) score02
        from sc
        group by sid
        having score01 > score02) t
    on student.sid = t.sid;
    
    # 2、查询平均成绩大于60分的学生的学号、平均成绩
    select sid, round(avg(score), 2) score_avg # 四舍五入保留两位小数
    from sc
    group by sid
    having score_avg > 60;
    
    # 3、查询各个学生的学号、姓名、选课数量、总成绩
    -- 1.查出复合子列
    select sid, count(1) class_name, sum(score) total
    from sc
    group by sid;
    -- 2.附加关联学生表学号、姓名
    select student.sid, sname, class_name, total
    from student
    inner join(
        select sid, count(1) class_name, sum(score) total
        from sc
        group by sid) t
    on student.sid = t.sid;
    
    # 4、查询姓 “李” 的老师的个数
    select count(1) from teacher where tname like '李%';
    
    # 5、查询没学过 “张三” 老师的课程的学生的学号、姓名
    -- 1.先查询学过“张三”老师的课程的学号
    select sid from teacher
    inner join course on teacher.tid = course.tid
    inner join sc on course.cid = sc.cid
    where tname = '张三';
    -- 2.再过滤掉学过的学生即可not in(其中，内层sid不会外传，外层不用指定)
    select sid, sname from student where sid not in(
        select sid from teacher
        inner join course on teacher.tid = course.tid
        inner join sc on course.cid = sc.cid
        where tname = '张三');
    -- 2.另一种写法not exists(其中，内层sid不会外传，外层不用指定；内层要指定并加where/and过滤笛卡尔积)
    select sid, sname from student where not exists(
        select 1 from teacher
        inner join course on teacher.tid = course.tid
        inner join sc on course.cid = sc.cid
        where tname = '张三' and student.sid = sc.sid);
    
    # 6、查询学过 “01” 课程和 “02” 课程的学生的学号、姓名
    -- 1.先查询学过01课程的学号
    select sid from sc where cid = '01';
    -- 2.先查循学过02课程的学号
    select sid from sc where cid = '02';
    -- 3.后汇总查询学过01且02课程的学号
    select t1.sid
    from (select sid from sc where cid = '01') t1
    inner join (select sid from sc where cid = '02') t2
    on t1.sid = t2.sid;
    -- 4.附加关联学生表学号、姓名(效率：inner join>left/right join>[not]exists>[not]in)
    select student.sid, sname from student
    inner join (
        select t1.sid
        from (select sid from sc where cid = '01') t1
        inner join (select sid from sc where cid = '02') t2
        on t1.sid = t2.sid) t
    on student.sid = t.sid;
    
    # 7、查询各个课程成绩均小于60分的学生的学号、姓名
    -- 1.查询出学生最大成绩小于60分的学号
    select sid from sc
    group by sid
    having max(score) < 60;
    -- 2.附加关联学生表查询姓名
    select student.sid, sname
    from student
    inner join(
        select sid from sc
        group by sid
        having max(score) < 60) s
    on student.sid = s.sid;
    
    # 8、查询没有学全所有课程的学生的学号、姓名
    -- 1.查询课程数量少于所有课程的学号
    select sid from sc
    group by sid
    having count(1) < 3;
    -- 2.附加关联学生表查询姓名
    select student.sid, sname
    from student
    inner join(
        select sid from sc
        group by sid
        having count(1) < 3) s
    on student.sid = s.sid;
    
    # 9、查询至少有一门课与学号为 “01” 的学生所学相同的学生的学号、姓名
    -- 1.查询出01学生所学的课程
    select cid from sc where sid = '01';
    -- 2.查询出学过01学生所学课程的学号
    select distinct sid from sc # distinct和group by本质差不多，均用于去重，效率也差不多，distinct更简洁优雅
    where cid in(select cid from sc where sid = '01');
    -- 3.附加关联学生表查询姓名
    select student.sid, sname
    from student
    inner join(
        select distinct sid from sc
        where cid in(select cid from sc where sid = '01')) s
    on student.sid = s.sid;
    
    # 10、查询和学号为 “01” 的学生学习课程完全相同的其他学生的学号、姓名
    -- 1.查询出01学生所学的课程号
    select cid from sc where sid = '01';
    -- 2.查询出01学生所学的课程数量
    select count(1) from sc where sid = '01';
    -- 3.查询出其它同学学过01所学课程的课程
    select *
    from (select cid from sc where sid = '01') s
    inner join sc on sc.cid = s.cid
    where sid != '01'; # 一般单判断直接用!=
    -- 3.查询出其它同学学过01所学课程的课程
    select *
    from (select cid from sc where sid = '01') s
    inner join sc on sc.cid = s.cid
    where not sid = '01'; # 一般not用于多判断或比较</<=/>/>=
    -- 4.查询出其它同学学过01所学课程的数量
    select sid, count(1)
    from (select cid from sc where sid = '01') s
    inner join sc on sc.cid = s.cid
    where sid != '01'
    group by sid;
    -- 5.查询出课程量和01所学课程数量相等的学号
    select sid
    from (select cid from sc where sid = '01') s
    inner join sc on sc.cid = s.cid
    where sid != '01'
    group by sid having count(1) = (select count(1) from sc where sid = '01');
    -- 6.附加关联学生表查询姓名
    select student.sid, sname
    from student
    inner join(
        select sid
        from (select cid from sc where sid = '01') s
        inner join sc on sc.cid = s.cid
        where sid != '01'
        group by sid having count(1) = (select count(1) from sc where sid = '01')) s1
    on student.sid = s1.sid;
    
    # 11、查询 “张三” 老师所教的课程的平均成绩
    -- 1.查询张三老师的教师号
    select tid from teacher where tname = '张三';
    -- 2.查询张三老师所教的课程号
    select cid
    from course
    inner join (select tid from teacher where tname = '张三') t on course.tid = t.tid;
    -- 2.查询张三老师所教的课程号
    select cid
    from (select tid from teacher where tname = '张三') t # 写法符合优化SQL之小表驱动大表(inner join中可略)
    inner join course on t.tid = course.tid;
    -- 3.查询张三老师所教课程的平均成绩
    select round(avg(score), 2) socre_avg
    from sc where cid in (
        select cid
        from (select tid from teacher where tname = '张三') t
        inner join course on t.tid = course.tid)
    group by cid;
    
    # 12、查询没有学习过 “张三” 老师所教的任一门课程的学生姓名
    -- 1.查询学过张三老师的课程的学生号
    select sid
    from teacher
    inner join course on teacher.tid = course.tid
    inner join sc on course.cid = sc.cid
    where teacher.tname = '张三';
    -- 2.查询没学过张三老师的学生信息(not in写法)
    select sname
    from student
    where sid not in (
        select sid
        from teacher
        inner join course on teacher.tid = course.tid
        inner join sc on course.cid = sc.cid
        where teacher.tname = '张三');
    -- 2.查询没学过张三老师的学生信息(not exists写法)
    select sname
    from student
    where not exists (
        select 1
        from teacher
        inner join course on teacher.tid = course.tid
        inner join sc on course.cid = sc.cid
        where teacher.tname = '张三' and student.sid = sc.sid);
    
    # 13、查询两门及其以上课程80分以下的学生的学号，姓名、平均成绩
    -- 1.查询两门及以上80分以下的学号(count+if)
    select sid, round(avg(score), 2)
    from sc
    group by sid
    having count(if(score < 80, 0, null)) > 1; # count不统计null列
    -- 1.查询两门及以上80分以下的学号(sum+if)
    select sid, round(avg(score), 2)
    from sc
    group by sid
    having sum(if(score < 80, 1, null)) > 1; # if适用于单项判断if/else
    -- 1.查询两门及以上80分以下的学号(count+case when then [else] end)
    select sid, round(avg(score), 2)
    from sc
    group by sid
    having count(case when score < 80 then 0 end) > 1; # else null可略，不满足条件默认为null
    -- 1.查询两门及以上80分以下的学号(sum+case when then [else] end)
    select sid, round(avg(score), 2)
    from sc
    group by sid
    having sum(case when score < 80 then 1 end) > 1; # case when then [else] end适用于多项判断if/else if/else
    -- 2.附加关联学生表查询姓名
    select student.sid, sname, score_avg
    from student
    inner join (
        select sid, round(avg(score), 2) score_avg
        from sc
        group by sid
        having count(if(score < 80, 0, null)) > 1) s
    on student.sid = s.sid;
    
    # 14、查询各个课程的课程号、课程名、最高成绩、最低成绩、平均成绩、及格率
    -- 1.查询各个课程的已有和组合信息
    select cid, max(score) score_max, min(score) score_min, round(avg(score), 2) score_avg, round(count(if(score >= 60, 0, null)) / count(1), 2) pass_rate
    from sc
    group by cid;
    -- 2.附加关联课程表查询课程名
    select course.cid, course.cname, score_max, score_min, score_avg, pass_rate
    from course
    inner join (
        select cid, max(score) score_max, min(score) score_min, round(avg(score), 2) score_avg, round(count(if(score >= 60, 0, null)) / count(1), 2) pass_rate
        from sc
        group by cid) s
    on course.cid = s.cid;
    
    # 15、查询各个老师的职工号、所教课程的平均成绩，按所教课程的平均成绩降序排列
    -- 1.查询所有老师下的所有课程的平均成绩
    select tid, round(avg(score), 2) score_avg
    from course
    inner join sc on course.cid = sc.cid
    group by tid
    order by score_avg desc;
    -- 1.查询所有老师下的各个课程的平均成绩
    select tid, round(avg(score), 2) score_avg
    from course
    inner join sc on course.cid = sc.cid
    group by tid, course.cid
    order by score_avg desc;
    
    # 16、查询各个课程中，成绩排名为第2名或第3名的学生的学号、课程号、课程成绩、成绩排名
    -- 1.查询各课程排行等信息
    select sid, cid, score, rank() over (partition by cid order by score desc) score_rank
    from sc;
    -- (分组前的统计列也是用where过滤[where分组前过滤，having分组后过滤]，统计列在当前层sql仅能对having可视，外层sql则where/having均可视)
    -- 2.过滤第二和第三名(where+between and写法)
    select sid, cid, score, score_rank
    from (
        select sid, cid, score, rank() over (partition by cid order by score desc) score_rank
        from sc) s
    where score_rank between 2 and 3;
    -- 2.过滤第二和第三名(where+in写法)
    select sid, cid, score, score_rank
    from (
        select sid, cid, score, rank() over (partition by cid order by score desc) score_rank
        from sc) s
    where score_rank in (2, 3); # between and效率过于in(前者为有序过滤，后者为无序匹配)
    -- 2.过滤第二和第三名(having+between and写法)
    select sid, cid, score, score_rank
    from (
        select sid, cid, score, rank() over (partition by cid order by score desc) score_rank
        from sc) s
    having score_rank between 2 and 3; # where效率高于having(where是过滤数据集后做操作，having是获取全部数据后做过滤)
    -- 2.过滤第二和第三名(having+in写法)
    select sid, cid, score, score_rank
    from (
        select sid, cid, score, rank() over (partition by cid order by score desc) score_rank
        from sc) s
    having score_rank in (2, 3);
    
    # 17、查询各个课程在不同成绩区间人数的所占百分比，成绩区间分别为 [85-100]、[70-84]、[60-69]、[0-59]
    -- 1.统计课程的分布情况
    select cid,
           round(count(if(score between 85 and 100, 0, null)) / count(1), 2) 85_100,
           round(count(if(score between 70 and 84, 0, null)) / count(1), 2)  70_84,
           round(count(if(score between 60 and 69, 0, null)) / count(1), 2)  60_69,
           round(count(if(score between 0 and 59, 0, null)) / count(1), 2)   0_59
    from sc
    group by cid;
    -- 2.附加关联课程表查询课程名
    select course.cid, cname, 85_100, 70_84, 60_69, 0_59
    from course
    inner join (
        select cid,
               round(count(if(score between 85 and 100, 0, null)) / count(1), 2) 85_100,
               round(count(if(score between 70 and 84, 0, null)) / count(1), 2)  70_84,
               round(count(if(score between 60 and 69, 0, null)) / count(1), 2)  60_69,
               round(count(if(score between 0 and 59, 0, null)) / count(1), 2)   0_59
        from sc
        group by cid) s
    on course.cid = s.cid;
    
    # 18、查询各个学生的学号、平均成绩、平均成绩的排名
    select sid, round(avg(score), 2) score_avg, rank() over (order by avg(score) desc) score_rank
    from sc
    group by sid;
    -- (排名rank函数是mysql8.0后引入的，企业环境Linux部署的mysql通常是5.7)
    select sid, score_avg, rank() over (order by score_avg desc) score_rank
    from (
        select sid, round(avg(score), 2) score_avg
        from sc
        group by sid) s;
    
    # 19、查询各课程成绩前三名的课程号、学号、成绩排名、课程成绩
    -- 1.查询各课程排行等信息
    select cid, sid, rank() over (partition by cid order by score desc) score_rank, score
    from sc;
    -- (分组前的统计列也是用where过滤[where分组前过滤，having分组后过滤]，统计列在当前层sql仅能对having可视，外层sql则where/having均可视)
    -- 2.过滤前三名(between and写法)
    select cid, sid, score_rank, score
    from (
        select cid, sid, rank() over (partition by cid order by score desc) score_rank, score
        from sc) s
    where score_rank between 1 and 3;
    -- 2.过滤前三名(in写法)
    select cid, sid, score_rank, score
    from (
        select cid, sid, rank() over (partition by cid order by score desc) score_rank, score
        from sc) s
    where score_rank in (1, 2, 3);
    
    # 20、查询所有学生中男生数量、女生数量
    select ssex, count(1) from student group by ssex;
    
    # 21、查询姓名中含有 “风” 字的学生的学号、姓名
    select sid, sname from student where sname like '%风%';
    
    # 22、查询同名同性的学生的姓名、性别、数量
    select sname, ssex, count(1) num
    from student
    group by sname, ssex
    having num > 1;
    
    # 23、查询1990年出生的学生的学号、出生日期（注：student 表中 sage 列的类型是 datetime）
    -- year年份函数
    select sid, sage,
           year(sage), # 年份
           quarter(sage), # 季度
           month(sage), # 月份
           week(sage), # 周
           day(sage), # 日
           hour(sage), # 小时
           minute(sage), # 分钟
           second(sage), # 秒
           microsecond(sage), # 微秒(1秒=1*10^6微秒)
           date(sage), # 日期
           time(sage), # 时间
           timestamp(sage) # 时间戳(展示日期时间)
    from student
    where year(sage) = '1990';
    -- extract提取函数
    select sid, sage,
           extract(year from sage), # 年份
           extract(quarter from sage), # 季度
           extract(month from sage), # 月份
           extract(week from sage), # 周
           extract(day from sage), # 日
           extract(hour from sage), # 小时
           extract(minute from sage), # 分钟
           extract(second from sage), # 秒
           extract(microsecond from sage) # 微秒(1秒=1*10^6微秒)
    from student
    where extract(year from sage) = '1990';
    -- date_format格式化函数
    select sid, sage,
           date_format(sage, '%Y'), # 年份
           date_format(sage, '%m'), # 月份
           date_format(sage, '%d'), # 日
           date_format(sage, '%H'), # 小时
           date_format(sage, '%i'), # 分钟
           date_format(sage, '%s'), # 秒
           date_format(sage, '%p') # AM上午/PM下午
    from student
    where date_format(sage, '%Y') = '1990';
    
    # 24、查询所有选修 “张三” 老师所教课程的学生中，课程成绩最高的学生姓名、课程成绩
    select sname, score
    from teacher
    inner join course on teacher.tid = course.tid
    inner join sc on course.cid = sc.cid
    inner join student s on sc.sid = s.sid
    where tname = '张三'
    order by score desc
    limit 1;
    -- inner join写里面和外面都行，limit 1等价于limit 0,1
    select sname, score
    from student
    inner join (
        select sid, score
        from teacher
        inner join course on teacher.tid = course.tid
        inner join sc on course.cid = sc.cid
        where tname = '张三'
        order by score desc
        limit 0, 1) t
    on student.sid = t.sid;
    
    # 25、查询选修了全部课程的学生的学号
    -- 1.查询全部课程的数量
    select count(1) from course;
    -- 2.查询选修课程数量等于全部课程数量的学号
    select sid
    from sc
    group by sid
    having count(sid) = (select count(1) from course);
    
    # 26、查询各个学生的学号、年龄
    select sid, sage,
           now(), # 当前日期时间
           curdate(), # 当前日期
           curtime(), # 当前时间
           datediff('2024-10-09 13:39:40', '2023-09-08 12:38:39'), # 日期差(前参大于后参)
           timediff('2024-10-09 13:39:40', '2023-09-08 12:38:39'), # 时间差(前参大于后参)
           timestampdiff(year, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 年份差(后参大于前参)
           timestampdiff(quarter, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 季度差(后参大于前参)
           timestampdiff(month, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 月份差(后参大于前参)
           timestampdiff(week, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 周差(后参大于前参)
           timestampdiff(day, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 日差(后参大于前参)
           timestampdiff(hour, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 小时差(后参大于前参)
           timestampdiff(minute, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 分钟差(后参大于前参)
           timestampdiff(second, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 秒差(后参大于前参)
           timestampdiff(microsecond, '2023-09-08 12:38:39', '2024-10-09 13:39:40'), # 微秒差[1秒=1*10^6微秒](后参大于前参)
           year(now()) - year(sage) age1, # 精确度低
           floor(datediff(now(), sage) / 365) age2, # 精确度中
           timestampdiff(year, sage, now()) age3 # 精确度高
    from student;
    
    -- 向上取整(ceil是ceiling函数的别名)
    select ceil(3.1);
    select ceiling(3.1);
    -- 向下取整(floor函数)
    select floor(3.9);
    -- 四舍五入取整(round函数，第二个参数是保留小数位)
    select round(3.4);
    select round(3.5);
    select round(3.004, 2);
    select round(3.005, 2);
    
    # 27、查询在本周过生日的学生的 学号
    select sid
    from student
    where week(sage) = week(now());
    
    select week('2023-09-08'), # 日期中第几周
           weekofyear('2023-09-08'), # 日期中第几周
           weekday('2023-09-08'), # 周中第几天(0周一~6周日)，4即为周五
           dayofweek('2023-09-08'), # 周中第几天(1周日~7周六)，6即为周五
           yearweek('2023-09-08'); # 年份和日期中第几周组合
    
    select day('2023-09-08'), # 日期中几号
           dayname('2023-09-08'), # 周几英文名称
           dayofmonth('2023-09-08'), # 月份中第几天
           dayofyear('2023-09-08'); # 年份中第几天
    
    select month('2023-09-08'), # 日期中几月
           monthname('2023-09-08'); # 月份英文名称
    
    select year('2023-09-08'); # 日期中几年
    
    # 28、查询在下12周过生日的学生的学号
    select sid
    from student
    where week(sage) = week(now()) + 12;
    
    select sid
    from student
    where week(sage) = week(date_add(now(), interval 12 week));
    
    select date_add('2023-09-08 12:38:39.123456789', interval 1 microsecond), # 添加1微秒(1秒=1*10^6微秒)
           date_add('2023-09-08 12:38:39.123456789', interval 1 second), # 添加1秒
           date_add('2023-09-08 12:38:39.123456789', interval 1 minute), # 添加1分钟
           date_add('2023-09-08 12:38:39.123456789', interval 1 hour), # 添加1小时
           date_add('2023-09-08 12:38:39.123456789', interval 1 day), # 添加1日
           date_add('2023-09-08 12:38:39.123456789', interval 1 week), # 添加1周
           date_add('2023-09-08 12:38:39.123456789', interval 1 month), # 添加1月
           date_add('2023-09-08 12:38:39.123456789', interval 1 quarter), # 添加1季度
           date_add('2023-09-08 12:38:39.123456789', interval 1 year); # 添加1年
    
    select date_sub('2023-09-08 12:38:39.123456789', interval 1 microsecond), # 减少1微秒(1秒=1*10^6微秒)
           date_sub('2023-09-08 12:38:39.123456789', interval 1 second), # 减少1秒
           date_sub('2023-09-08 12:38:39.123456789', interval 1 minute), # 减少1分钟
           date_sub('2023-09-08 12:38:39.123456789', interval 1 hour), # 减少1小时
           date_sub('2023-09-08 12:38:39.123456789', interval 1 day), # 减少1日
           date_sub('2023-09-08 12:38:39.123456789', interval 1 week), # 减少1周
           date_sub('2023-09-08 12:38:39.123456789', interval 1 month), # 减少1月
           date_sub('2023-09-08 12:38:39.123456789', interval 1 quarter), # 减少1季度
           date_sub('2023-09-08 12:38:39.123456789', interval 1 year); # 减少1年
    
    # 29、查询在本月过生日的学生的学号
    select sid
    from student
    where month(sage) = month(now());
    
    # 30、查询在下3个月过生日的学生的学号
    select sid
    from student
    where month(sage) = month(now()) + 3;
    
    select sid
    from student
    where month(sage) = month(date_add(now(), interval 3 month));
    
    ### 总结：先拆分查询小SQL组件，后拼接小SQL和附加条件，最后通过外键关联表信息 ###