MySQL经典50题（部分题目包含窗口函数只支持MySQL版本8.x及以后或使用hive操作）

-- 1、学生表
-- Student(s_id,s_name,s_birth,s_sex) ：学生编号、姓名、年月、性别
CREATE TABLE
IF NOT EXISTS `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`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
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', '女');

-- 2、课程表
-- Course(c_id,c_name,t_id) ：课程编号、 课程名称、 教师编号
CREATE TABLE
IF NOT EXISTS `Course` (
	`c_id` VARCHAR (20),
	`c_name` VARCHAR (20) NOT NULL DEFAULT '',
	`t_id` VARCHAR (20) NOT NULL,
	PRIMARY KEY (`c_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
INSERT INTO Course VALUES ('01', '语文', '02');
INSERT INTO Course VALUES ('02', '数学', '01');
INSERT INTO Course VALUES ('03', '英语', '03');

-- 3、教师表
-- Teacher(t_id,t_name) ：教师编号、教师姓名
CREATE TABLE
IF NOT EXISTS `Teacher` (
	`t_id` VARCHAR (20),
	`t_name` VARCHAR (20) NOT NULL DEFAULT '',
	PRIMARY KEY (`t_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
INSERT INTO Teacher VALUES ('01', '张三');
INSERT INTO Teacher VALUES ('02', '李四');
INSERT INTO Teacher VALUES ('03', '王五');

-- 4、成绩表
-- Score(s_id,c_id,s_score) ：学生编号、课程编号、分数
CREATE TABLE
IF NOT EXISTS `Score` (
	`s_id` VARCHAR (20),
	`c_id` VARCHAR (20),
	`s_score` INT (3),
	PRIMARY KEY (`s_id`, `c_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
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);



-- 2.1 题目1：查询"01"课程比"02"课程成绩高的学生的信息、课程分数
select stu.*,a.s_score,b.s_score from student stu 
JOIN score a on stu.s_id=a.s_id AND a.c_id='01'
JOIN score b on stu.s_id=b.s_id AND b.c_id='02'
WHERE a.s_score>b.s_score

-- 方法二：采用where语句
SELECT
	a.*, 
  b.s_score,
	c.s_score
FROM
	Student a,
	Score b,
	Score c
WHERE
	a.s_id = b.s_id
AND a.s_id = c.s_id
AND b.c_id = '01'
AND c.c_id = '02'
AND b.s_score > c.s_score;


-- 2.2 题目2：查询平均成绩大于等于60分且总分大于200分的同学且必须考3门的学生编号和学生姓名和平均成绩
SELECT stu.s_name,AVG(sc.s_score) FROM score sc 
JOIN student stu ON stu.s_id=sc.s_id
GROUP BY stu.s_id
HAVING COUNT(sc.c_id)=3 AND AVG(sc.s_score)>60 AND SUM(sc.s_score)>200;
   
-- 2.3 题目3：查询平均成绩小于60分的同学的学生编号、学生姓名、平均成绩（包括有成绩的和无成绩）
SELECT stu.s_id,stu.s_name,AVG(IFNULL(sc.s_score,0)) avg FROM student stu
LEFT JOIN score sc ON sc.s_id=stu.s_id 
GROUP BY stu.s_id
HAVING avg<60;

-- 方法2：采用union函数进行合并
SELECT
	a.s_id,
	a.s_name,
	ROUND(AVG(b.s_score), 2) AS avg_score
FROM
	Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY
	a.s_id
HAVING
	avg_score < 60
UNION
SELECT
	a.s_id,
	a.s_name,
	0 AS avg_score
FROM
	Student a
WHERE
	a.s_id NOT IN (
		-- 学生的学号不在给给定表的学号中
		SELECT DISTINCT
			s_id -- 查询出全部的学号
		FROM
			Score
	);

-- 方法3：采用null函数
SELECT
	a.s_id,
	a.s_name,
	ROUND(AVG(b.s_score), 2) AS avg_score
FROM
	Student a
LEFT JOIN Score b ON a.s_id = b.s_id
GROUP BY
	a.s_id
HAVING
	avg_score < 60
OR avg_score IS NULL; -- 最后的NULL判断

-- 2.4 题目4：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT stu.s_id,stu.s_name,COUNT(sc.c_id),SUM(sc.s_score)
FROM student stu LEFT JOIN score sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id

-- 2.5 题目5：查询“李”姓老师的数量
SELECT COUNT(*) FROM teacher t
where t.t_name like '李%'

-- 2.6 题目6：查询学过张三老师授课的同学信息
SELECT stu.* from student stu
JOIN score sc ON stu.s_id=sc.s_id
JOIN course c ON sc.c_id=c.c_id
JOIN teacher t ON c.t_id=t.t_id
WHERE t.t_name='张三';

-- 方法2：通过筛选出张三的id，并匹配相应的课程id，最后将学生的成绩与课程id进行匹配，最后筛选出符合条件的学生。
SELECT a.* 
FROM Student a
JOIN Score b ON a.s_id = b.s_id 
	AND b.c_id =(SELECT c_id FROM Course WHERE t_id =( SELECT t_id FROM Teacher WHERE t_name = '张三' ));

-- 2.7 题目7：找出没有学过张三老师课程的学生
SELECT stu.* from student stu where stu.s_id not in (
SELECT stu.s_id from student stu
JOIN score sc ON stu.s_id=sc.s_id
JOIN course c ON sc.c_id=c.c_id
JOIN teacher t ON c.t_id=t.t_id
WHERE t.t_name ='张三');

-- 2.8 题目8：查询学过编号为01，并且学过编号为02课程的学生信息
SELECT stu.* from student stu
JOIN score a ON stu.s_id=a.s_id AND a.c_id='01'
JOIN score b ON stu.s_id=b.s_id AND b.c_id='02'

-- 2.9 题目9：查询学过01课程，但是没有学过02课程的学生信息
SELECT stu.* from student stu
JOIN score a ON stu.s_id=a.s_id AND a.c_id='01'
WHERE stu.s_id not IN(
SELECT b.s_id from score b WHERE b.c_id='02'
);

-- 2.10 题目10：查询没有学完全部课程的同学的信息
SELECT stu.* from student stu 
LEFT JOIN score sc ON stu.s_id=sc.s_id
GROUP BY stu.s_id
having count(sc.c_id) < (  
  select count(*) from Course
)

-- 2.11 题目11：查询至少有一门课与学号为01的同学所学相同的同学的信息
SELECT stu.* FROM student stu 
LEFT JOIN score sc ON stu.s_id=sc.s_id
WHERE sc.c_id IN(
SELECT c_id FROM score sc 
WHERE sc.s_id='01'
) AND stu.s_id !='01'
GROUP BY stu.s_id;

-- 2.12 题目12：查询和01同学学习的课程完全相同的同学的信息
SELECT stu.* FROM student stu
LEFT JOIN score sc ON stu.s_id=sc.s_id
WHERE stu.s_id !='01'
GROUP BY stu.s_id  
HAVING count(sc.c_id)=
(
SELECT COUNT(c_id) FROM score sc 
WHERE sc.s_id='01');

-- 方法2：使用group_concat函数（分组合并，同时排序）
-- group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
SELECT
	b.s_id,
	b.s_name,
	b.s_sex,
	GROUP_CONCAT( c.c_id ORDER BY c.c_id ) AS concat_course 
FROM
	student b
	JOIN score c ON b.s_id = c.s_id 
GROUP BY
	b.s_id 
HAVING
	GROUP_CONCAT( c.c_id ORDER BY c.c_id )=(
	SELECT
		GROUP_CONCAT( c_id ORDER BY c_id ) 
	FROM
		score 
	WHERE
		s_id = '01' 
	);

-- 2.13 题目13：查询没有修过张三老师讲授的任何一门课程的学生姓名
SELECT stu.s_name FROM student stu 
WHERE stu.s_id NOT IN (
SELECT sc.s_id from score sc
JOIN course c ON sc.c_id=c.c_id
JOIN teacher t ON c.t_id=t.t_id
WHERE t.t_name='张三');

-- 2.14 题目14：查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT stu.s_id, stu.s_name, AVG(sc.s_score) FROM score sc
LEFT JOIN student stu ON sc.s_id=stu.s_id
WHERE sc.s_id IN (
SELECT sc.s_id FROM score sc
WHERE sc.s_score<60
GROUP BY sc.s_id
HAVING COUNT(sc.c_id)>=2)
GROUP BY sc.s_id; 

SELECT
	s.s_id,
	s.s_name,
	t.avg_score
FROM
	Student s
JOIN (
	SELECT
		s_id,
		ROUND(AVG(s_score)) avg_score  -- 求均值再取整
	FROM
		Score
	WHERE
		s_score < 60
	GROUP BY
		s_id
	HAVING
		COUNT(s_score) >= 2
) t ON s.s_id = t.s_id;

WITH
	t AS( SELECT s_id,avg(s_score) avg FROM score WHERE s_score<60
GROUP BY s_id HAVING COUNT(c_id)>=2 )
SELECT stu.s_id, stu.s_name,t.avg FROM t,student stu WHERE t.s_id=stu.s_id;

-- 2.16 题目16：求出第n高的成绩（找出语文科目第2高的成绩和学号）
WITH
	t1 AS(SELECT sc.s_id, sc.s_score,DENSE_RANK() over(ORDER BY sc.s_score DESC) as r
FROM score sc
JOIN course c ON sc.c_id=c.c_id AND c.c_name='语文')
SELECT t1.s_id,t1.s_score FROM t1 WHERE t1.r=2;

-- 2.17 题目17：LeetCode-SQL-596-超过5名学生的课程
SELECT sc.c_id,COUNT(sc.c_id) FROM course c 
LEFT JOIN score sc ON c.c_id=sc.c_id
GROUP BY sc.c_id
HAVING COUNT(sc.c_id)>5;

-- 2.19 题目19：检索01课程分数小于60，按分数降序排列的学生信息
SELECT * FROM student stu
JOIN score sc ON stu.s_id=sc.s_id AND sc.c_id=01
WHERE sc.s_score<60
ORDER BY sc.s_score DESC;

-- 2.20 题目20：按平均成绩从高到低（降序）显示所有学生的所有课程的成绩以及平均成绩
SELECT s_id,c_id,s_score,avg(s_score) over(PARTITION by s_id) as avgSC 
FROM score
ORDER BY avgSC DESC; 

select 
 s.s_id
 ,max(case s.c_id when '01' then s.s_score end) 语文 -- 由于有些数据是空值，所以必须采用聚合函数，才能将真实值取出
 ,max(case s.c_id when '02' then s.s_score end) 数学
 ,max(case s.c_id when '03' then s.s_score end) 英语
 ,avg(s.s_score)
 ,b.s_name  -- 没有出现在group by子句中，导致报错
from score s
join Student b
on s.s_id = b.s_id
group by s.s_id, b.s_name
order by 5 desc;

-- 2.21 题目21：查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率（及格：>=60），中等率（中等为：70-80），优良率（优良为：80-90），优秀率（优秀为：>=90）；—比较综合，多看！
SELECT sc.c_id,c.c_name,
	max(sc.s_score)  maxSC,
	min(sc.s_score)  minSC,
	avg(sc.s_score)  avgSC,
	concat(100*(SUM(CASE WHEN sc.s_score>=60 THEN 1 ELSE 0 END)/sum(CASE WHEN sc.s_score THEN 1 ELSE 0 END)),'%')  '及格率'
	,concat(100*(SUM(CASE WHEN sc.s_score>=70 AND sc.s_score<=80 THEN 1 ELSE 0 END)/sum(CASE WHEN sc.s_score THEN 1 ELSE 0 END)),'%')  '中等率'
	,concat(100*(SUM(CASE WHEN sc.s_score>=80 AND sc.s_score<=90 THEN 1 ELSE 0 END)/sum(CASE WHEN sc.s_score THEN 1 ELSE 0 END)),'%')  '优良率'
	,concat(100*(SUM(CASE WHEN sc.s_score>=90 THEN 1 ELSE 0 END)/sum(CASE WHEN sc.s_score THEN 1 ELSE 0 END)),'%') '优秀率'
FROM score sc JOIN course c ON sc.c_id=c.c_id
GROUP BY sc.c_id; 

-- 2.22 题目22：按照各科成绩进行排序，并且显示排名—比较综合，多看！
SELECT sc.c_id,sc.s_id,sc.s_score,
	DENSE_RANK() over(PARTITION by c_id ORDER BY s_score DESC) rankSC
from score sc; 

-- 2.23 题目23：查询学生的总成绩，并进行排名—比较综合，多看！
WITH
	t1 as ( SELECT sc.s_id, SUM(s_score) sumSC from score sc GROUP BY sc.s_id )
SELECT s_id,sumSC,DENSE_RANK() over(ORDER BY sumSC DESC) rankSC FROM t1;

-- 2.26 题目26：LeetCode-SQL-184-部门工资(各科成绩)最高/N高的员工(学生)
SELECT *,
	DENSE_RANK() over(PARTITION by c_id ORDER BY s_score DESC)
FROM score;

-- 2.27 题目27：查询不同老师所教不同课程平均分从高到低显示
SELECT t.t_name,c.c_name,AVG(sc.s_score) avgSC from score sc
RIGHT JOIN course c ON sc.c_id=c.c_id 
RIGHT JOIN teacher t ON t.t_id=c.t_id 
GROUP BY t.t_name,c.c_name 
ORDER BY avgSC DESC;

-- 2.28 题目28：查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
WITH
	t1 as(SELECT s_id,s_score,
		DENSE_RANK() over(PARTITION by c_id ORDER BY s_score DESC) as rankNUM
	FROM score)
SELECT stu.s_name,t1.s_score,t1.rankNUM FROM t1 JOIN student stu ON stu.s_id=t1.s_id
WHERE t1.rankNUM=2 OR t1.rankNUM=3;

-- 2.29 题目29：统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[0-60]及所占百分比
SELECT sc.c_id,c.c_name,
	SUM(CASE WHEN sc.s_score>85 AND s_score<=100 THEN 1 ELSE 0 END) '85-100分人数',
	concat(100*(SUM(CASE WHEN sc.s_score>=85 AND s_score<=100 THEN 1 ELSE 0 END)/sum(CASE WHEN sc.s_score THEN 1 ELSE 0 END)),'%') '85-100分比率',
	SUM(CASE WHEN sc.s_score>70 AND sc.s_score<=85 THEN 1 ELSE 0 END) '70-85分人数',
	concat(100*(SUM(CASE WHEN sc.s_score>=70 AND sc.s_score<=85 THEN 1 ELSE 0 END)/sum(CASE WHEN sc.s_score THEN 1 ELSE 0 END)),'%') '70-85分比率',
	SUM(CASE WHEN sc.s_score>60 AND sc.s_score<=70 THEN 1 ELSE 0 END) '60-70分人数',
	concat(100*(SUM(CASE WHEN sc.s_score>=60 AND sc.s_score<=70 THEN 1 ELSE 0 END)/sum(CASE WHEN sc.s_score THEN 1 ELSE 0 END)),'%') '60-70分比率',
	SUM(CASE WHEN sc.s_score>=0 AND sc.s_score<=60 THEN 1 ELSE 0 END) '0-60分人数',
	concat(100*(SUM(CASE WHEN sc.s_score>=0 AND sc.s_score<=60 THEN 1 ELSE 0 END)/sum(CASE WHEN sc.s_score THEN 1 ELSE 0 END)),'%') '0-60分比率'
FROM score sc JOIN course c ON sc.c_id=c.c_id
GROUP BY sc.c_id; 

-- 2.30 题目30：查询学生的平均成绩及名次—比较综合，多看，定义变量，实现rank函数
WITH
	t1 AS (SELECT s_id,AVG(s_score) avgSC FROM score GROUP BY s_id)
SELECT t1.*,DENSE_RANK() over(ORDER BY avgSC DESC) FROM t1;

-- 2.31 题目31：查询各科成绩前三名的记录—比较综合，多看
WITH
	t1 as(SELECT c_id,s_id,s_score,
					DENSE_RANK() over(PARTITION by c_id ORDER BY s_score DESC) rankNUM
				FROM score)
SELECT * FROM t1 WHERE t1.rankNUM=1 OR t1.rankNUM=2 OR t1.rankNUM=3;

-- 2.32 题目32：查询每门课被选修的学生数
SELECT c.c_id,c.c_name,COUNT(sc.c_id) FROM course c 
LEFT JOIN score sc ON c.c_id=sc.c_id
GROUP BY 1,2;

-- 2.33 题目33：查询出只有两门课程的全部学生的学号和姓名
SELECT sc.s_id,stu.s_name,COUNT(sc.c_id) countNUM FROM score sc
JOIN student stu ON stu.s_id=sc.s_id
GROUP BY sc.s_id
HAVING countNUM=2;

-- 2.34 题目34：查询男女生人数
SELECT s_sex,COUNT(s_sex) FROM student
GROUP BY s_sex;

-- 2.35 题目35：查询名字中含有风字的学生信息
SELECT * FROM student
WHERE s_name like '%风%';

-- 2.36 题目36：查询同名同性的学生名单，并统计同名人数
SELECT s_name,COUNT(s_name) countNUM FROM student_copy1
GROUP BY s_name
HAVING countNUM>1;

-- 2.37 题目37：查询每门课程的平均成绩，结果按平均成绩降序排列；平均成绩相同时，按课程编号c_id升序排列
SELECT c_id,AVG(s_score) avgSC FROM score 
GROUP BY c_id
ORDER BY avgSC DESC,c_id; 

-- 2.38 题目38：查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT sc.s_id,stu.s_name,AVG(sc.s_score) avgSC FROM score sc
JOIN student stu ON stu.s_id=sc.s_id 
GROUP BY sc.s_id
HAVING avgSC>=85;

-- 2.39 题目39：查询所有学生的课程及分数（均分、总分）情况
SELECT stu.s_id,
	SUM(CASE c.c_name WHEN '语文' THEN sc.s_score ELSE 0 END ) '语文',
	SUM(CASE c.c_name WHEN '数学' THEN sc.s_score ELSE 0 END ) '数学',
	SUM(CASE c.c_name WHEN '英语' THEN sc.s_score ELSE 0 END ) '英语',
	avg(sc.s_score),
	SUM(sc.s_score) 
FROM student stu
LEFT JOIN score sc ON stu.s_id=sc.s_id
LEFT JOIN course c ON sc.c_id=c.c_id
GROUP BY 1;

-- 2.40 题目40：查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
WITH
	t1 AS( SELECT sc.*,DENSE_RANK() over(ORDER BY sc.s_score DESC) rankNUM FROM score sc
					JOIN course c ON sc.c_id=c.c_id
					JOIN teacher t ON t.t_id=c.t_id AND t.t_name='张三' )
SELECT stu.*,t1.s_score FROM t1 
JOIN student stu ON t1.s_id=stu.s_id
WHERE t1.rankNUM=1;

-- 2.41 题目41：查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩—比较综合，多看！一个表自连
SELECT a.s_id,a.c_id,a.s_score FROM score a
JOIN score b ON a.s_score=b.s_score AND a.c_id != b.c_id AND a.s_id != b.s_id
GROUP BY 1,2,3;

SELECT s_id, c_id, s_score
FROM score
WHERE (s_id, s_score) IN (
    SELECT s_id, s_score
    FROM score
    GROUP BY s_id, s_score
    HAVING COUNT(DISTINCT c_id) > 1
)
ORDER BY s_id, c_id;

-- 2.42 题目42：题目的要求就是找出每门课的前2名同学—多看，比较综合，解决前几名排序的问题
WITH
	t1 as(SELECT c_id,s_id,s_score,
					DENSE_RANK() over(PARTITION by c_id ORDER BY s_score DESC) rankNUM
				FROM score)
SELECT t1.c_id,stu.s_name,t1.s_score,t1.rankNUM FROM t1 
JOIN student stu ON stu.s_id=t1.s_id
WHERE t1.rankNUM=1 OR t1.rankNUM=2;

-- 2.43 题目43：统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT sc.c_id,COUNT(sc.c_id) '选修人数' FROM course c 
LEFT JOIN score sc ON c.c_id=sc.c_id
GROUP BY sc.c_id
HAVING COUNT(sc.c_id)>5
ORDER BY COUNT(sc.c_id) DESC,sc.c_id;

-- 2.44 题目44：检索至少选修两门课程的学生学号
SELECT
	s_id,
	count(*) num 
FROM
	Score 
GROUP BY
	s_id 
HAVING
	count(*) >= 2;

-- 2.45 题目45：查询选修了全部课程的学生信息
select *   -- 3、s_id对应的学生信息
from Student 
where s_id in(select s_id -- 2、最大课程数对应的s_id
              from Score 
              group by s_id 
              having count(*)=(select count(*) from Course)  -- 1、全部课程数
             );

-- 2.46 题目46：查询各学生的年龄：按照出生日期来算，当前月日 < 出生年月的月日则，年龄减1
SELECT CEIL(DATEDIFF(CURRENT_DATE,s_birth)/365) FROM student;

select
 s_name
 ,s_birth
 ,date_format(now(), '%Y') - date_format(s_birth, '%Y') - (case when date_format(now(), '%m%d') < date_format(s_birth, '%m%d') then 1 else 0 end) as age  -- 当前日期大，说明已经过生了，年龄正常；反之说明今年还没有到年龄-1
from Student;

-- 2.47 题目47：查询本周过生日的学生
SELECT s_name FROM student_copy1 WHERE WEEKOFYEAR(CURRENT_DATE)=WEEKOFYEAR(s_birth);

SELECT WEEKOFYEAR(CURRENT_DATE);

select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth);  -- 方式2

select * from student where yearweek(s_birth) = yearweek(date_format(now(),'%Y%m%d'));   -- 方式3

-- 2.48 题目48：查询下周过生日的学生
SELECT s_name FROM student_copy1 WHERE WEEKOFYEAR(CURRENT_DATE)+1=WEEKOFYEAR(s_birth);

-- 2.49 题目49：查询本月过生的同学
SELECT s_name FROM student_copy1 WHERE month(CURRENT_DATE)=month(s_birth);

-- 2.50 题目50：查询下月过生的同学
SELECT s_name FROM student_copy1 WHERE IF(month(CURRENT_DATE)+1<=12,month(CURRENT_DATE)+1,1)=month(s_birth);
 
-- 方法2，mod模，取余
select * from Student
where mod(month(now()),12)  + 1 =  month(s_birth);