MySQL 50 道查询题汇总，足以巩固大部分查询（附带数据准备SQL、题型分析、演示、50道题的完整SQL）

MySQL 50 道查询题，足以巩固大部分查询

数据准备：

在这里插入图片描述

创建表sql

只是简单的演示，就没有搞什么主键和限制之类的。
需要的后续可以添加。
可视化工具使用的是Navicat。

-- 数据准备
-- 创建表命令==========================================================
-- 创建学生表
CREATE TABLE student ( 
	s_id   VARCHAR (50) COMMENT '学生编号',
	s_name VARCHAR (50) COMMENT '学生姓名', 
	s_birth DATE COMMENT '出生年月', 
	s_sex  VARCHAR (50)  COMMENT '性别'
);

-- 创建课程表
CREATE TABLE course ( 
	c_id   VARCHAR (50) COMMENT '课程编号',
	c_name VARCHAR (50) COMMENT '课程名称', 
	t_id   VARCHAR (50) COMMENT '教师编号'
);

-- 创建教师表
CREATE TABLE teacher ( 
	t_id   VARCHAR (50) COMMENT '教师编号',
	t_name VARCHAR (50) COMMENT '教师姓名'
);

-- 创建成绩表
CREATE TABLE score ( 
	s_id    VARCHAR (50) COMMENT '学生编号',
	c_id    VARCHAR (50) COMMENT '课程编号',
	s_score VARCHAR (20) COMMENT '分数'
);

添加表数据sql

-- 添加表数据 ==========================================================
-- 添加学生表数据
INSERT INTO student ( s_id, s_name, s_birth, s_sex )
VALUES
	( '01', '赵雷', '1990-01-01', '男' ),
	( '02', '钱电', '1990-12-21', '男' ),
	( '03', '孙风', '1990-05-20', '男' ),
	( '04', '李云', '1990-08-06', '男' ),
	( '05', '周梅', '1991-12-01', '女' ),
	( '06', '吴兰', '1992-03-01', '女' ),
	( '07', '郑竹', '1989-07-01', '女' ),
	( '08', '王菊', '1990-01-20', '女' );

-- 添加课程表数据
INSERT INTO course (c_id, c_name, t_id )
VALUES
	( '01', '语文', '02' ),
	( '02', '数学', '01' ),
	( '03', '英语', '03' );
	
-- 添加教师表数据
INSERT INTO teacher ( t_id, t_name )
VALUES
	( '01', '张三'),
	( '02', '李四'),
	( '03', '王五');

-- 添加成绩表数据
INSERT INTO score ( s_id, c_id, s_score )
VALUES
	( '01', '01', 80),
	( '01', '02', 90),
	( '01', '03', 99),
	( '02', '01', 70),
	( '02', '02', 60),
	( '02', '03', 80),
	( '03', '01', 80),
	( '03', '02', 80),
	( '03', '03', 80),
	( '04', '01', 50),
	( '04', '02', 30),
	( '04', '03', 20),
	( '05', '01', 76),
	( '05', '02', 87),
	( '06', '01', 31),
	( '06', '03', 34),
	( '07', '02', 89),
	( '07', '03', 98);

50道查询题目汇总

因为都写在一篇文章里面会太长，所以分成多篇文章，对应一些更详细的演示，截图和完整的sql，方便查看。

01 - 05 题：

1-5 题：涉及：内连接、inner join 三表联结，group by、case when ，子查询、聚合函数、子句等~~

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

2、查询 "01语文课程"比"02数学课程"成绩低的学生的信息及课程分数

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

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

06 - 10 题：

6-10 题：涉及 count(*/1/字段名)的区别、not in 、 not exists 、显示、隐式连接、case when 的用法等

6、查询姓“李”的老师的数量

7、查询学过“张三”老师课程的同学的信息（普通表连接）

8、查询没有学过“张三”老师课程的同学的信息

9、查询学过编号为’01语文’并且也学过编号‘02数学’的课程的同学的信息

10、查询学过编号为‘01语文’但是没有学过编号为‘02数学’的课程的同学的信息

11 - 15 题：

11-15 题：涉及子查询、in \ not in 关键字、distinct 去重、count()函数、group by、having、case when ~~~

11、查询没有学全所有课程的同学的信息（子查询）

12、查询至少有一门课与【学号为‘01’的同学所学课程相同】的同学的信息

13、查询和‘01’号的同学学习的课程完全相同的其他同学的信息

14、查询没学过“张三” 老师讲授的任一门课程的学生的姓名

15、查询两门及以上不及格课程的同学的学号、姓名及其平均成绩

16 - 20 题：

16-20 题：涉及临时表用法演示、子查询的使用，开窗函数 rank()、row_number()，其他包括round()、max、min、case when ~~

16、查询 "01"语文课程分数小于60，按分数降序排列的学生信息

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

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分、及格率，中等率，优良率，优秀率

19、按各科成绩进行排序，并显示排名

20、查询学生的总成绩并进行排名

21 - 25 题：

21-25 题：涉及子查询、开窗函数dense_rank和rank的区别、case when、round()、in、between 等~~~

21、查询不同老师所教不同课程平均分从高到低显示

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

23、统计各科成绩各分数段人数:课程编号,课程名称,[100-86],[85-70],[69-60]，[0-59]及所占百分比

24、查询学生平均成绩及其名次

25、查询各科成绩前三名的记录

26 - 35 题：

26-35 题：解释在case when 前面加max()函数的原因、一些函数使用、分组排序查询等

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

27、查询出只有两门课程的全部学生的学号和姓名

28、查询男生、女生人数

29、查询名字中含有"风"字的学生信息

30、查询同名同性别学生名单，并统计同名人数

   (演示：创建临时表并插入演示数据）

31、查询1990年出生的学生名单

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

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

34、查询课程名称为"数学”，且分数低于60的学生姓名和分数

35、查询所有学生的课程及分数情况

36 - 45 题：

36-45 题：一些分组排序查询、开窗函数 dense_rank、distinct 去重函数等 ~

36、查询每一门课程成绩都在70分以上的姓名、课程名称和分数

37、查询不及格的课程及学生

38、查询课程编号为01语文且课程成绩在80分以上的学生的学号和姓名

39、求每门课程的学生人数

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其所有成绩

41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩

42、查询每门课程成绩最好的前两名

43、统计每门课程的学生选修人数(超过5人的课程才统计)，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

44、检索至少选修两门课程的学生学号

45、查询选修了全部课程的学生信息

46 - 50 题：

46-50 题：日期相关、生日、年份距离等~

日期相关的查询函数

46、查询各学生的年龄

47、查询本周过生日的学生

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

49、查询本月过生日的学生

50、查询下月过生日的学生

具体 SQL 汇总

01 - 05 题：



-- 1、查询 “01” 语文成绩比 “02” 数学成绩高的学生的信息及课程分数======================================

-- 涉及到 student、score 这两张表


-- 写法1：内连接

SELECT
	s.*,
	s1.s_score AS '语文成绩',
	s2.s_score '数学成绩' 
FROM
	score s1,-- 隐式内连接的写法来连接这三个表
	score s2,
	student s 
WHERE
	s1.c_id = '01' -- 查询score表的语文成绩
	
	AND s2.c_id = '02' -- 查询score表的数学成绩
	
	AND s1.s_id = s2.s_id -- 进行表连接
	
	AND s1.s_score > s2.s_score -- 进行成绩判断
	
	AND s.s_id = s1.s_id -- 关联student表的条件




-- 写法2：长型数据变宽型数据写法

SELECT
	s.*,
	t.s01 '语文成绩',
	t.s02 '数学成绩' 
FROM
	(
	SELECT
		s.s_id,
		-- 	如果 s.c_id = '01' ，则返回 s.s_score ，否则返回null（else null可省略）
		max( CASE WHEN s.c_id = '01' THEN s.s_score ELSE NULL END ) AS s01,
		max( CASE WHEN s.c_id = '02' THEN s.s_score ELSE NULL END ) AS s02 
	FROM
		score s 
	GROUP BY
		s.s_id 
	) t
	LEFT JOIN student s ON s.s_id = t.s_id 
WHERE
	t.s01 > t.s02




-- 写:3：子查询

select t1.s_name ,t1.c_name, t1.s_score, t2.c_name,  t2.s_score from 
(
-- 查询所有学生的数学成绩 01
select st.s_name , st.s_id, c.c_name, sc.s_score  from student st 
left join score sc on  st.s_id = sc.s_id
left join course c on c.c_id = sc.c_id
where sc.c_id = '01' 

) t1 
join 
(
-- 查询所有学生的语文成绩 02
select st.s_name, st.s_id, c.c_name, sc.s_score  from student st 
left join score sc on  st.s_id = sc.s_id
left join course c on c.c_id = sc.c_id
where sc.c_id = '02'
) t2

on t1.s_id = t2.s_id  

where t1.s_score > t2.s_score



-- 写法4：三表联结


SELECT
	st.*,
	s1.s_score AS '01语文成绩',
	s2.s_score AS '02数学成绩' 
FROM
	student AS st
	INNER JOIN ( SELECT * FROM score WHERE c_id = '01' ) AS s1 ON st.s_id = s1.s_id
	INNER JOIN ( SELECT * FROM score WHERE c_id = '02' ) AS s2 ON s1.s_id = s2.s_id 
	AND s1.s_score > s2.s_score;




-- 2、查询 "01语文课程"比"02数学课程"成绩低的学生的信息及课程分数======================================

SELECT
	sc.*,
	s1.c_id '语文课程编号',
	s1.s_score '语文成绩',
	s2.c_id '数学课程编号',
	s2.s_score '数学成绩' 
FROM
	score s1,
	score s2,
	student sc 
WHERE
	s1.s_id = s2.s_id -- 表的内连接
	
	AND s1.c_id = '01' -- 表示s1这张表查询出来的是“01”语文成绩
	
	AND s2.c_id = '02' -- 表示s2这张表查询出来的是“02”数学成绩
	
	AND s1.s_score < s2.s_score -- 查询语文成绩比数学成绩低的条件
	
	AND s1.s_id = sc.s_id -- 连接学生表



-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩======================================


-- 用到 student 学生表 、score 成绩表，
-- AVG() 函数用于取平均数
-- ROUND() 函数用于将数值四舍五入到指定的小数位数
-- FLOOR() 函数会返回不大于给定参数的最大整数值

SELECT
	st.*,
	ROUND( avg( sc.s_score ), 1 ) '分数' -- 取小数点后1位数
	
FROM
	score sc
	LEFT JOIN student st ON sc.s_id = st.s_id 
	
GROUP BY
	sc.s_id 
	
HAVING
	ROUND( avg( sc.s_score ), 1 ) >= 60



-- 子查询

SELECT
	
	sc.s_id,
	
	-- 这个子查询的作用是动态地根据主查询中的每个学生 ID，在 student 表中查找对应的学生姓名，并将这个学生姓名作为一个查询字段返回给用户
	-- 在这个查询中，首先是从 score 表开始获取数据，然后才会执行子查询来获取对应的学生姓名
	
	(select st.s_name from	student st where sc.s_id = st.s_id ) '学生姓名',
	
	
	ROUND( avg( sc.s_score ), 1 ) '分数' -- 取小数点后1位数
	
FROM
	score sc
	
GROUP BY
	sc.s_id 
	
HAVING
	ROUND( avg( sc.s_score ), 1 ) >= 60



-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩======================================

SELECT
	st.*,
	IFNULL( ROUND( AVG( sc.s_score ), 1 ), 0 ) '分数' -- ROUND() 函数用于取小数点后1位数
	
FROM
	score sc
	RIGHT JOIN student st ON sc.s_id = st.s_id  -- 右连接，就是连接student这张表
GROUP BY
	sc.s_id 
HAVING
	IFNULL( ROUND( avg( sc.s_score ), 1 ), 0 ) < 60  -- having 后面写条件判断



-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩======================================

SELECT
	st.s_id,
	st.s_name,
	count( sc.c_id ) '选课总数',
	IFNULL(sum( sc.s_score ),0) '总成绩'  -- 如果成绩为null，则返回0
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	sc.s_id

06 - 10 题：


-- 6、查询 姓“李”的老师的数量======================================

-- 用到的是 teacher 表

select  COUNT(t_name) from teacher where t_name like '李%'


-- 7、查询学过“张三”老师课程的同学的信息======================================


-- 用到 student、course、score、teacher 这四张表

SELECT
	st.* 
FROM
	student st
	JOIN score sc ON sc.s_id = st.s_id
	JOIN course co ON co.c_id = sc.c_id
	JOIN teacher t ON t.t_id = co.t_id 
WHERE
	t.t_name = '张三'


-- 8、查询没有学过“张三”老师课程的同学的信息======================================


-- not in 写法

SELECT
	* 
FROM
	student st 
WHERE
	st.s_id NOT IN (
	
	SELECT
		sc.s_id 
	FROM
		course co
		JOIN score sc ON sc.c_id = co.c_id
		JOIN teacher t ON t.t_id = co.t_id 
	WHERE
		t.t_name = '张三' 
		
	)

-- not exists 写法

EXPLAIN SELECT
	* 
FROM
	student st 
WHERE
	NOT EXISTS (
	SELECT
		1 
	FROM
		(
		SELECT
			sc.s_id 
		FROM
			course co
			JOIN score sc ON sc.c_id = co.c_id
			JOIN teacher t ON t.t_id = co.t_id 
		WHERE
			t.t_name = '张三' 
		) t 
	WHERE
		t.s_id = st.s_id 
	)



-- 9、查询学过编号为'01语文'并且也学过编号‘02数学’的课程的同学的信息======================================


-- 要查一张表内的同个字段的值的两种判断，可以连接该表2次

-- join 写法

SELECT
	st.* 
FROM
	student st
	JOIN score s1 ON st.s_id = s1.s_id
	JOIN score s2 ON s1.s_id = s2.s_id 
WHERE
	s1.c_id = '01' 
	AND s2.c_id = '02'



-- 自连接写法

SELECT
	st.* 
FROM
	student st,
	score s1,
	score s2 
WHERE
	st.s_id = s1.s_id 
	AND s1.s_id = s2.s_id 
	AND s1.c_id = '01' 
	AND s2.c_id = '02'


-- 10、查询学过编号为‘01语文’但是没有学过编号为‘02数学’的课程的同学的信息======================================


SELECT
	st.* 
FROM
	student st
	LEFT JOIN
	(
	-- 子表，用来把score表里面每个学生的‘01’和‘02’课程数据拿出来
	SELECT
		s.s_id,
		max( CASE WHEN c_id = '01' THEN s_score ELSE NULL END ) s01,
		max( CASE WHEN c_id = '02' THEN s_score ELSE NULL END ) s02 
	FROM
		score s 
	GROUP BY
		s_id 
	) t ON t.s_id = st.s_id 
WHERE
	t.s01 >= 0         -- 查询学过‘01’课程的学生
	AND t.s02 IS NULL  -- 查询没有学过‘02’课程的学生

11 - 15 题：



-- 11、查询没有学全所有课程的同学的信息======================================

SELECT
	st.*,
	count(sc.c_id) 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
	sc.s_id 
HAVING
	count(sc.c_id) < ( SELECT count(c_id) FROM course )







-- 12、查询至少有一门课与【学号为‘01’的同学所学课程相同】的同学的信息======================================


-- 先查出学号为‘01’的同学所学的课程有哪些
-- 再查有哪些同学所学的课程至少有一课和‘01同学’相同
SELECT 
	st.* 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
WHERE
	sc.c_id IN ( SELECT sc.c_id FROM score sc WHERE sc.s_id = '01' )

GROUP BY
  1,2,3,4







-- 13、查询和‘01’号的同学学习的课程完全相同的其他同学的信息======================================






-- 14、查询没学过“张三” 老师讲授的任一门课程的学生的姓名======================================


-- 先把学过张三老师课程的学生的id查询出来，然后再使用not in 学生的id是否存在这个数据集合里面

SELECT
	* 
FROM
	student st 
WHERE
  -- 2、然后再使用 not in 查出没学过的学生
	st.s_id NOT IN (
	
	-- 1、子查询，先把学过张三老师课程的学生的id查询出来
	SELECT
		sc.s_id 
	FROM
		score sc
		LEFT JOIN course co ON co.c_id = sc.c_id
		LEFT JOIN teacher t ON t.t_id = co.t_id 
	WHERE
		t.t_name = '张三' 
		
	)









-- 15、查询两门及以上不及格课程的同学的学号、姓名及其平均成绩======================================

-- 写法1：

SELECT
	st.*,
	count( sc.s_score ) '课程数量',
	ROUND( avg( sc.s_score ), 1 ) '平均成绩' 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
WHERE
	sc.s_score < 60 
GROUP BY 
  st.s_id 
HAVING 
  count( sc.s_score ) >= 2


-- 写法2：

 
SELECT
	st.*,
	ROUND( AVG( sc.s_score ), 1 ) 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	st.s_id 
HAVING
  -- 分数小于60则返回1，然后计算总数，大于2表示有2科不及格
	sum(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END ) >=2

16 - 20 题：



-- 16、查询 "01"语文课程分数小于60，按分数降序排列的学生信息======================================

SELECT
	st.*,
	sc.s_score '分数' 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
WHERE
	sc.s_score < 60 
GROUP BY
	sc.s_id 
ORDER BY
	sc.s_score DESC











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


-- 写法1：

SELECT
	st.s_name,
	MAX(case when sc.c_id = '01' then sc.s_score else 0 end) '01语文',
	MAX(case when sc.c_id = '02' then sc.s_score else 0 end) '02数学',
	MAX(case when sc.c_id = '03' then sc.s_score else 0 end) '03英语',
	ROUND( AVG( SC.s_score ), 1 ) '平均成绩' 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	sc.s_id 
ORDER BY
	ROUND( AVG( SC.s_score ), 1 ) DESC


-- 写法2：

SELECT
	t1.*,
	t2.avg_s 
FROM
	( SELECT * FROM score sc1 ) t1,
	( SELECT sc2.s_id, ROUND( avg( sc2.s_score ), 1 ) avg_s FROM score sc2 GROUP BY sc2.s_id ) t2 
WHERE
	t1.s_id = t2.s_id
ORDER BY 
  avg_s DESC


-- 写法3：开窗函数
SELECT 
	sc.*,
	avg(sc.s_score) over(partition by sc.s_id) '平均分数'
FROM
	score sc







-- ======================================



-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分、及格率，中等率，优良率，优秀率
-- D及格为： >= 60 , C中等为：70-80，B优良为：80-90，A优秀为：>=90

SELECT
	sc.c_id,
	co.c_name,
	round(MAX( sc.s_score ),2) max_s,
	round(MIN( sc.s_score ),2) min_s,
	round(AVG( sc.s_score ),2) avg_s,
	-- 如果分数>=60,则返回1，表示该行数据符合条件，否则返回0，
	-- 然后用sum函数把符合条件的数据求和，再除以总数，就是及格率
	round(sum(case when sc.s_score >= 60 then 1 else 0 end )/count(1),2) D,
	round(sum(case when 80 > sc.s_score >= 70 then 1 else 0 end )/count(1),2) C,
	round(sum(case when 90 > sc.s_score >= 80 then 1 else 0 end )/count(1),2) B,
	round(sum(case when sc.s_score >= 90 then 1 else 0 end )/count(1),2) A
FROM
	course co
	LEFT JOIN score sc ON sc.c_id = co.c_id 
GROUP BY
	sc.c_id









-- 19、按各科成绩进行排序，并显示排名======================================



-- 开窗函数写法


explain 

SELECT
	sc.*,
	rank() over(partition by sc.c_id  order by sc.s_score desc) rk
FROM
	score sc 


SELECT
	sc.*,
	row_number() over(partition by sc.c_id  order by sc.s_score desc) rk
FROM
	score sc 


-- 子查询写法

SELECT
	sc.* ,
	-- 这个子查询：拿主查询的表数据和相同数据的子表进行比较，用 count() 函数统计，
	-- 比较当前学生（sc表的数据）所在课程（c_id）的成绩（s_score）是否比其他学生（sc2表）在同一门课程下的成绩低
	-- +1 是因为排名是从1开始的，不是从0开始的
	(select count(s_score) from score sc2 where sc.c_id = sc2.c_id and sc.s_score < sc2.s_score)+1 '分数排名'
	
FROM
	score sc 
ORDER BY
	sc.c_id,
	sc.s_score DESC






-- 20、查询学生的总成绩并进行排名======================================


-- 写法1：子查询+开窗函数

SELECT
	t.*,
	rank() over(ORDER BY t.sum_s DESC) rk
FROM 
	(
		SELECT
			sc.s_id,
			sum( sc.s_score ) sum_s 
		FROM
			score sc 
		GROUP BY
			sc.s_id 
	) t


-- 写法2：临时表+子查询

-- 创建临时表

CREATE TEMPORARY TABLE sum_s_temp AS
	SELECT
		sc.s_id,
		sum( sc.s_score ) sum_s 
	FROM
		score sc 
	GROUP BY
		sc.s_id


-- 查询临时表

select * from sum_s_temp

-- 用两张一样的临时表来一行一行比对

SELECT
	t1.*,
	rank() OVER (ORDER BY sum_s DESC) AS rk
	-- 这个子查询报错信息【- Can't reopen table: 't1'】
	-- (SELECT count(sum_s) FROM sum_s_temp t2 WHERE t1.sum_s < t2.sum_s )+1 rk 
FROM
	sum_s_temp t1 
ORDER BY
	sum_s DESC

21 - 25 题：


-- 21、查询不同老师所教不同课程平均分从高到低显示

-- 普通分组排序写法：

SELECT
	te.*,
	co.c_name,
	ROUND(AVG( sc.s_score ),1) avg_s 
FROM
	teacher te
	LEFT JOIN course co ON te.t_id = co.t_id
	LEFT JOIN score sc ON sc.c_id = co.c_id 
GROUP BY
	te.t_id 
ORDER BY
	AVG( sc.s_score ) DESC
	


-- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆


-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 即语文数学英语成绩都放在一起的第二和第三名

-- 2、然后主查询这里再根据条件获取数据
SELECT
	* 
FROM
	(
	-- 1、先用子查询，把分数排名排好序
	SELECT
		st.s_name,
		sc.c_id,
		co.c_name,
		sc.s_score,
		rank () over ( ORDER BY sc.s_score DESC ) rk 
	FROM
		course co
		LEFT JOIN score sc ON sc.c_id = co.c_id
		LEFT JOIN student st ON st.s_id = sc.s_id 
	) t 
WHERE
	t.rk IN ( 2, 3 )


-- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆

-- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-86],[85-70],[69-60]，[0-59]及所占百分比

SELECT
	co.c_id,co.c_name,
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END ),2) AS "[100-86]",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END ),2) AS "[85-70]",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END ),2) AS "[69-60]",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END ),2) AS "[0-59]",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 86 AND 100 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[100-86]%",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 70 AND 85 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[85-70]%",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[69-60]%",
	ROUND(sum( CASE WHEN sc.s_score BETWEEN 0 AND 59 THEN 1 ELSE 0 END )/ count( 1 ),2) AS "[0-59]%" 
FROM
	score sc
	left join course co on sc.c_id = co.c_id
	GROUP BY sc.c_id




-- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆



-- 24、查询学生平均成绩及其名次 


SELECT
	st.s_name,
	t.avg_s,
	-- 2、再用 rank 函数给成绩排名并加上排序
	rank () over ( ORDER BY t.avg_s DESC ) rk 
FROM
	student st
LEFT JOIN 
	( 
	  -- 1、先查询每个学生的平均成绩
		SELECT 
			s_id, 
			round( avg( s_score ), 2 ) avg_s 
		FROM score 
		GROUP BY s_id 
	
	) t ON st.s_id = t.s_id








-- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆

-- 25、查询各科成绩前三名的记录  
SELECT
	st.s_id,
	st.s_name,
	co.c_name,
	t.s_score,
	t.rk '名次' 
FROM
	( SELECT *, dense_rank () over ( PARTITION BY c_id ORDER BY s_score DESC ) rk FROM score ) t
	LEFT JOIN student st ON st.s_id = t.s_id
	LEFT JOIN course co ON co.c_id = t.c_id 
WHERE
	t.rk IN ( 1, 2, 3 ) 
	


-- dense_rank 和 rank 的区别
SELECT
	*,
	dense_rank() over ( PARTITION BY c_id ORDER BY s_score DESC ) rk 
FROM
	score

SELECT
	*,
	rank() over ( PARTITION BY c_id ORDER BY s_score DESC ) rk 
FROM
	score

26 - 35 题：


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


-- 写法1：

SELECT
	sum( CASE WHEN c_id = '01' THEN 1 ELSE 0 END ) '语文',
	sum( CASE WHEN c_id = '02' THEN 1 ELSE 0 END ) '数学',
	sum( CASE WHEN c_id = '03' THEN 1 ELSE 0 END ) '英语' 
FROM
	score
	
	
	
	

-- 写法2：
SELECT
	sc.c_id,
	co.c_name,
	count( sc.c_id ) '选修人数' 
FROM
	score sc
	LEFT JOIN course co ON sc.c_id = co.c_id 
GROUP BY
	sc.c_id




-- 27、查询出只有两门课程的全部学生的学号和姓名  

SELECT
	st.s_id,
	st.s_name 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BY
	st.s_id 
HAVING
	count( sc.c_id ) = 2





-- 28、查询男生、女生人数

-- 写法1：

SELECT
	sum(case when s_sex = '男' then 1 else 0 end) '男生人数',
	sum(case when s_sex = '女' then 1 else 0 end) '女生人数'
FROM
	student
	

-- 写法2：
SELECT
	t1.boy,
	t2.girl 
FROM
	( SELECT count( s_sex ) boy FROM student WHERE s_sex = '男' ) t1
	JOIN ( SELECT count( s_sex ) girl FROM student WHERE s_sex = '女' ) t2



-- 写法3：

SELECT
	s_sex,
	count( s_id ) 
FROM
	student 
GROUP BY
	s_sex






-- 29、查询名字中含有"风"字的学生信息

SELECT
	* 
FROM
	student 
WHERE
	s_name LIKE '%风%'





-- 30、查询同名同性别学生名单，并统计同名人数


-- 创建临时表
CREATE TEMPORARY TABLE temp_students (
    s_id INT PRIMARY KEY,
    s_name VARCHAR(255),
    s_birth VARCHAR(255),
    s_sex VARCHAR(255)
);



-- 插入数据
-- INSERT INTO ... SELECT 是一种从一个表复制数据到另一个表的方法
INSERT INTO temp_students ( s_id, s_name, s_birth, s_sex )
SELECT
s_id,
s_name,
s_birth,
s_sex 
FROM
	student

-- 插入同名同性别的数据
INSERT INTO temp_students ( s_id, s_name, s_birth, s_sex )
VALUES
	( '9', '赵雷' ,'1990-01-01', '男' ),
	( '10', '钱电', '1990-01-01' ,'女' )




-- 查询数据
SELECT * FROM temp_students 




-- 查询同名同性别学生名单，并统计同名人数

SELECT
	s_name,
	s_sex,
	count( s_name ) 
FROM
	temp_students 
GROUP BY
	s_name,
	s_sex 
HAVING
	count( s_name ) > 1






-- 31、查询1990年出生的学生名单 

-- like 写法

select * from student where s_birth like '1990%'

-- YEAR() 函数用于从日期中提取年份部分

select * from student where year(s_birth) = 1990




-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
	co.c_name,
	round( avg( sc.s_score ), 2 ) 
FROM
	score sc
	LEFT JOIN course co ON co.c_id = sc.c_id 
GROUP BY
	sc.c_id 
ORDER BY
	round( avg( sc.s_score ), 2 ) DESC, sc.c_id ASC






-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
SELECT
	st.s_id,
	st.s_name,
	IFNULL(round(avg( sc.s_score )),0) '平均成绩'
FROM
	score sc
	RIGHT JOIN student st ON st.s_id = sc.s_id 
GROUP BY 
  sc.s_id
HAVING
	round(avg( sc.s_score )) >= 85





-- 34、查询课程名称为"数学”，且分数低于60的学生姓名和分数

SELECT
	st.s_name,
	sc.s_score 
FROM
	course co
	LEFT JOIN score sc ON co.c_id = sc.c_id
	LEFT JOIN student st ON st.s_id = sc.s_id 
WHERE
	co.c_name = "数学" 
	AND sc.s_score < 60



select * from score where c_id = '02' and s_score < 60




-- 35、查询所有学生的课程及分数情况 
select 
	t.s_name,
	max(case when t.c_id = '01' then t.s_score else 0 end) '语文',
	max(case when t.c_id = '02' then t.s_score else 0 end) '数学',
	max(case when t.c_id = '03' then t.s_score else 0 end) '英语'
from
(select st.s_name,sc.* from student st left join score sc on st.s_id = sc.s_id) t
GROUP BY t.s_id



select 
	t.s_name,
	case when t.c_id = '01' then t.s_score else 0 end '语文',
	case when t.c_id = '02' then t.s_score else 0 end '数学',
	case when t.c_id = '03' then t.s_score else 0 end '英语'
	
from
(select st.s_name,sc.* from student st left join score sc on st.s_id = sc.s_id) t

GROUP BY t.s_id

36 - 45 题：



-- 36、查询每一门课程成绩都在70分以上的学生的姓名、课程名称和分数 

SELECT
	st.s_name,
	co.c_name,
	sc.s_score 
FROM
	score sc
	LEFT JOIN course co ON co.c_id = sc.c_id
	LEFT JOIN student st ON st.s_id = sc.s_id 
WHERE
st.s_id in (

	-- 先查询出3个成绩都70分以上的学生的id
	select s_id from score group by s_id having min(s_score) >= 70

)




-- 37、查询不及格的课程及学生

SELECT
	st.s_name,
	co.c_name,
	sc.s_score 
FROM
	score sc
	LEFT JOIN course co ON sc.c_id = co.c_id
	LEFT JOIN student st ON st.s_id = sc.s_id 
WHERE
	sc.s_score < 60




-- 38、查询课程编号为01语文且课程成绩在80分以上的学生的学号和姓名


SELECT
	st.s_id,
	st.s_name,
	co.c_name,
	sc.s_score 
FROM
	course co
	LEFT JOIN score sc ON sc.c_id = co.c_id
	LEFT JOIN student st ON st.s_id = sc.s_id 
WHERE
	co.c_id = '01' 
	AND sc.s_score >= 80





-- 39、求每门课程的学生人数

-- 宽型数据格式

SELECT 
	sum(case when sc.c_id = '01' then 1 else 0 end) '语文',
	sum(case when sc.c_id = '02' then 1 else 0 end) '数学',
	sum(case when sc.c_id = '03' then 1 else 0 end) '英语'
FROM
	course co
	LEFT JOIN score sc ON co.c_id = sc.c_id




-- 长型数据格式

SELECT
	co.c_name, count(sc.s_id) '人数'
FROM
	course co
	LEFT JOIN score sc ON sc.c_id = co.c_id 
GROUP BY
	co.c_id





-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其所有成绩


SELECT
	st.*,co.c_name,sc.s_score 
FROM
	student st
	LEFT JOIN score sc ON sc.s_id = st.s_id 
	LEFT JOIN course co ON co.c_id = sc.c_id
WHERE
	st.s_id = (
	SELECT
		sc.s_id
	FROM
		teacher te
		LEFT JOIN course co ON co.t_id = te.t_id
		LEFT JOIN score sc ON sc.c_id = co.c_id 
	WHERE
		te.t_name = '张三' 
	ORDER BY
		sc.s_score DESC 
		
		-- limit 1 返回查询结果的第一行数据
		LIMIT 1 
	)





-- 41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩

SELECT 
  distinct s1.*,
	co.c_name,
	st.s_name
FROM
	score s1
	LEFT JOIN score s2 ON s1.c_id != s2.c_id
	LEFT JOIN course co ON co.c_id = s1.c_id
  LEFT JOIN student st ON st.s_id = s1.s_id  	
WHERE
	s1.s_score = s2.s_score




-- 42、查询每门课程成绩最好的前两名 

-- 开窗函数 dense_rank 写法

SELECT
	st.s_id,
	st.s_name,
	t.c_name,
	t.s_score,
	t.drk 
FROM
	student st
RIGHT JOIN ( 
	SELECT 
		sc.*, 
		co.c_name,
		dense_rank () over ( PARTITION BY sc.c_id ORDER BY sc.s_score DESC ) drk 
	FROM score sc
	LEFT JOIN course co on co.c_id = sc.c_id
) t ON t.s_id = st.s_id 
WHERE
t.drk IN (1,2)
	

-- 子查询写法
SELECT
	* 
FROM
	score s1 
WHERE

  -- 这个子查询相当于上面的开窗函数
	( SELECT 
			count( s2.s_score ) 
		FROM score s2 
		WHERE 
		  s1.c_id = s2.c_id 
			AND s1.s_score < s2.s_score 
	) + 1 <= 2 
	
ORDER BY
	s1.c_id,
	s1.s_score DESC





-- 43、统计每门课程的学生选修人数(超过5人的课程才统计)，
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT
	sc.c_id '课程编号',
	count( 1 ) cnt 
FROM
	score sc 
GROUP BY
	sc.c_id 
HAVING
	count( 1 )>= 5 
ORDER BY
	cnt DESC,  -- 按人数降序排列
	sc.c_id ASC -- 按课程号升序排列






-- 44、检索至少选修两门课程的学生学号

SELECT
	sc.s_id ,
	st.s_name,
	count( sc.s_id ) '选修课程数'
FROM
	score sc
	LEFT JOIN student st ON st.s_id = sc.s_id 
GROUP BY
	sc.s_id 
HAVING
	count( sc.s_id ) >= 2






-- 45、查询选修了全部课程的学生信息

SELECT
	st.* 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BY
	sc.s_id 
HAVING
	count( sc.s_id ) = ( SELECT count( 1 ) FROM course )

46 - 50 题：



-- 日期相关==================================


-- 返回当前日期，格式为： 2024-04-05 22:21:27
select now();

-- 获取当前日期，返回 年月日，格式为：2024-04-05
select CURDATE();


-- 日期格式化，格式为：0405
select date_format(now(),'%m%d')

-- 不规范的字符串日期格式化
select STR_TO_DATE('980101','%y%m%d')  -- 格式为：1998-01-01

select STR_TO_DATE('170101','%y%m%d')  -- 格式为：2017-01-01


-- 返回当前日期的年份，格式为：2024
select year(now());


-- 返回当前日期的月份，格式为：4
select month(now());


-- 返回当前日期的天数，格式为：5
select day(now());


-- 返回当前日期在当前年份中的第几天，格式为：96
select dayofyear(now());


-- 返回当前日期所在的年份中的周数，就是这周是今年的第几个周，格式为：14
select weekofyear(now());






-- 46、查询各学生的年龄

select *,YEAR(now()) - YEAR(s_birth) '年龄' from student


select st.*,timestampdiff(YEAR,st.s_birth,CURDATE()) '年龄' from student st













-- 47、查询本周过生日的学生

-- 修改一条数据，让一个学生的生日是今天，CURRENT_DATE 返回当前年月日
UPDATE student set s_birth = CURRENT_DATE where s_id = '01'


select * from student 


-- 写法1：

select * from student where weekofyear(s_birth) = weekofyear(now())





-- 写法2： 针对不规范日期格式的判断写法

select
	* 
from
	student 
where
  
	weekofyear(
	
		str_to_date(
		
			concat( 
			
				year ( now()), -- 获取当前年份：2024
				date_format( s_birth, '%m%d' ) -- 把每个学生的生日的月份和天数
				
			), '%Y%m%d' -- 通过 concat 把两个值合并成这个年月日格式
			
		) -- 把不规范的字符串日期格式化
		
	) -- 获取学生生日日期所在的周是今年的第几个周
	
	= weekofyear(now()) -- 获取当前日期是今年的第几个周


-- 格式化：

SELECT
	* 
FROM
	student 
WHERE
	weekofyear( str_to_date( concat( YEAR ( now()), date_format( s_birth, '%m%d' ) ), '%Y%m%d' ) ) = weekofyear(now())












-- 48、查询下周过生日的学生

-- interval '7' day 给指定日期加 7 天, day就是+天数，month +月份，year +年份
select now(), now() + interval '7' day


-- 把今天生日的01号学生，再次修改为下周生日

UPDATE student set s_birth = now() + interval '7' day  where s_id = '01'


select * from student 




-- 和第47到一样，就是多了 【 + interval '7' day 】 

SELECT
	* 
FROM
	student 
WHERE
	weekofyear( str_to_date( concat( YEAR ( now()), date_format( s_birth, '%m%d' ) ), '%Y%m%d' ) ) = weekofyear(now() + interval '7' day)



-- 简单写法：

select * from student where weekofyear(s_birth) = weekofyear(now() + interval '7' day)







-- 49、查询本月过生日的学生

-- 查当前月份
select month(now())


-- 获取每个学生生日的月份
select *,month(s_birth) from student 


select * from  student where month(now()) = month(s_birth)














-- 50、查询下月过生日的学生

-- 当前日期

select now();


-- 当前日期再加一个月

select now() + interval '1' month;

select * from  student 


select * from  student where month(now() + interval '1' month) = month(s_birth)