当前位置：首页 > news >正文

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

news 来源：原创 2024/5/17 22:23:09

MySQL 50 道查询题，足以巩固大部分查询
- 数据准备：
- - 创建表sql
  - 添加表数据sql
- 50道查询题目汇总
- - 01 - 05 题：
  - - 1、查询 “01” 语文成绩比 “02” 数学成绩高的学生的信息及课程分数
    - 2、查询 "01语文课程"比"02数学课程"成绩低的学生的信息及课程分数
    - 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
    - 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
    - 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
  - 06 - 10 题：
  - - 6、查询姓“李”的老师的数量
    - 7、查询学过“张三”老师课程的同学的信息（普通表连接）
    - 8、查询没有学过“张三”老师课程的同学的信息
    - 9、查询学过编号为'01语文'并且也学过编号‘02数学’的课程的同学的信息
    - 10、查询学过编号为‘01语文’但是没有学过编号为‘02数学’的课程的同学的信息
  - 11 - 15 题：
  - - 11、查询没有学全所有课程的同学的信息（子查询）
    - 12、查询至少有一门课与【学号为‘01’的同学所学课程相同】的同学的信息
    - 13、查询和‘01’号的同学学习的课程完全相同的其他同学的信息
    - 14、查询没学过“张三” 老师讲授的任一门课程的学生的姓名
    - 15、查询两门及以上不及格课程的同学的学号、姓名及其平均成绩
  - 16 - 20 题：
  - - 16、查询 "01"语文课程分数小于60，按分数降序排列的学生信息
    - 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
    - 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分、及格率，中等率，优良率，优秀率
    - 19、按各科成绩进行排序，并显示排名
    - 20、查询学生的总成绩并进行排名
  - 21 - 25 题：
  - - 21、查询不同老师所教不同课程平均分从高到低显示
    - 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
    - 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-86],[85-70],[69-60]，[0-59]及所占百分比
    - 24、查询学生平均成绩及其名次
    - 25、查询各科成绩前三名的记录
  - 26 - 35 题：
  - - 26、查询每门课程被选修的学生数
    - 27、查询出只有两门课程的全部学生的学号和姓名
    - 28、查询男生、女生人数
    - 29、查询名字中含有"风"字的学生信息
    - 30、查询同名同性别学生名单，并统计同名人数
    - 31、查询1990年出生的学生名单
    - 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
    - 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
    - 34、查询课程名称为"数学”，且分数低于60的学生姓名和分数
    - 35、查询所有学生的课程及分数情况
  - 36 - 45 题：
  - - 36、查询每一门课程成绩都在70分以上的姓名、课程名称和分数
    - 37、查询不及格的课程及学生
    - 38、查询课程编号为01语文且课程成绩在80分以上的学生的学号和姓名
    - 39、求每门课程的学生人数
    - 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其所有成绩
    - 41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩
    - 42、查询每门课程成绩最好的前两名
    - 43、统计每门课程的学生选修人数(超过5人的课程才统计)，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
    - 44、检索至少选修两门课程的学生学号
    - 45、查询选修了全部课程的学生信息
  - 46 - 50 题：
  - - 46、查询各学生的年龄
    - 47、查询本周过生日的学生
    - 48、查询下周过生日的学生
    - 49、查询本月过生日的学生
    - 50、查询下月过生日的学生
- 具体 SQL 汇总
- - 01 - 05 题：
  - 06 - 10 题：
  - 11 - 15 题：
  - 16 - 20 题：
  - 21 - 25 题：
  - 26 - 35 题：
  - 36 - 45 题：
  - 46 - 50 题：

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：内连接SELECTs.*,s1.s_score AS '语文成绩',s2.s_score '数学成绩' 
FROMscore s1,-- 隐式内连接的写法来连接这三个表score s2,student s 
WHEREs1.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：长型数据变宽型数据写法SELECTs.*,t.s01 '语文成绩',t.s02 '数学成绩' 
FROM(SELECTs.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 FROMscore s GROUP BYs.s_id ) tLEFT JOIN student s ON s.s_id = t.s_id 
WHEREt.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'
) t2on t1.s_id = t2.s_id  where t1.s_score > t2.s_score-- 写法4：三表联结SELECTst.*,s1.s_score AS '01语文成绩',s2.s_score AS '02数学成绩' 
FROMstudent AS stINNER JOIN ( SELECT * FROM score WHERE c_id = '01' ) AS s1 ON st.s_id = s1.s_idINNER 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数学课程"成绩低的学生的信息及课程分数======================================SELECTsc.*,s1.c_id '语文课程编号',s1.s_score '语文成绩',s2.c_id '数学课程编号',s2.s_score '数学成绩' 
FROMscore s1,score s2,student sc 
WHEREs1.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() 函数会返回不大于给定参数的最大整数值SELECTst.*,ROUND( avg( sc.s_score ), 1 ) '分数' -- 取小数点后1位数FROMscore scLEFT JOIN student st ON sc.s_id = st.s_id GROUP BYsc.s_id HAVINGROUND( avg( sc.s_score ), 1 ) >= 60-- 子查询SELECTsc.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位数FROMscore scGROUP BYsc.s_id HAVINGROUND( avg( sc.s_score ), 1 ) >= 60-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩======================================SELECTst.*,IFNULL( ROUND( AVG( sc.s_score ), 1 ), 0 ) '分数' -- ROUND() 函数用于取小数点后1位数FROMscore scRIGHT JOIN student st ON sc.s_id = st.s_id  -- 右连接，就是连接student这张表
GROUP BYsc.s_id 
HAVINGIFNULL( ROUND( avg( sc.s_score ), 1 ), 0 ) < 60  -- having 后面写条件判断-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩======================================SELECTst.s_id,st.s_name,count( sc.c_id ) '选课总数',IFNULL(sum( sc.s_score ),0) '总成绩'  -- 如果成绩为null，则返回0
FROMstudent stLEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BYsc.s_id

06 - 10 题：


-- 6、查询 姓“李”的老师的数量======================================-- 用到的是 teacher 表select  COUNT(t_name) from teacher where t_name like '李%'-- 7、查询学过“张三”老师课程的同学的信息======================================-- 用到 student、course、score、teacher 这四张表SELECTst.* 
FROMstudent stJOIN score sc ON sc.s_id = st.s_idJOIN course co ON co.c_id = sc.c_idJOIN teacher t ON t.t_id = co.t_id 
WHEREt.t_name = '张三'-- 8、查询没有学过“张三”老师课程的同学的信息======================================-- not in 写法SELECT* 
FROMstudent st 
WHEREst.s_id NOT IN (SELECTsc.s_id FROMcourse coJOIN score sc ON sc.c_id = co.c_idJOIN teacher t ON t.t_id = co.t_id WHEREt.t_name = '张三' )-- not exists 写法EXPLAIN SELECT* 
FROMstudent st 
WHERENOT EXISTS (SELECT1 FROM(SELECTsc.s_id FROMcourse coJOIN score sc ON sc.c_id = co.c_idJOIN teacher t ON t.t_id = co.t_id WHEREt.t_name = '张三' ) t WHEREt.s_id = st.s_id )-- 9、查询学过编号为'01语文'并且也学过编号‘02数学’的课程的同学的信息======================================-- 要查一张表内的同个字段的值的两种判断，可以连接该表2次-- join 写法SELECTst.* 
FROMstudent stJOIN score s1 ON st.s_id = s1.s_idJOIN score s2 ON s1.s_id = s2.s_id 
WHEREs1.c_id = '01' AND s2.c_id = '02'-- 自连接写法SELECTst.* 
FROMstudent st,score s1,score s2 
WHEREst.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数学’的课程的同学的信息======================================SELECTst.* 
FROMstudent stLEFT JOIN(-- 子表，用来把score表里面每个学生的‘01’和‘02’课程数据拿出来SELECTs.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 FROMscore s GROUP BYs_id ) t ON t.s_id = st.s_id 
WHEREt.s01 >= 0         -- 查询学过‘01’课程的学生AND t.s02 IS NULL  -- 查询没有学过‘02’课程的学生

11 - 15 题：

-- 11、查询没有学全所有课程的同学的信息======================================SELECTst.*,count(sc.c_id) 
FROMstudent stLEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BYsc.s_id 
HAVINGcount(sc.c_id) < ( SELECT count(c_id) FROM course )-- 12、查询至少有一门课与【学号为‘01’的同学所学课程相同】的同学的信息======================================-- 先查出学号为‘01’的同学所学的课程有哪些
-- 再查有哪些同学所学的课程至少有一课和‘01同学’相同
SELECT st.* 
FROMstudent stLEFT JOIN score sc ON sc.s_id = st.s_id 
WHEREsc.c_id IN ( SELECT sc.c_id FROM score sc WHERE sc.s_id = '01' )GROUP BY1,2,3,4-- 13、查询和‘01’号的同学学习的课程完全相同的其他同学的信息======================================-- 14、查询没学过“张三” 老师讲授的任一门课程的学生的姓名======================================-- 先把学过张三老师课程的学生的id查询出来，然后再使用not in 学生的id是否存在这个数据集合里面SELECT* 
FROMstudent st 
WHERE-- 2、然后再使用 not in 查出没学过的学生st.s_id NOT IN (-- 1、子查询，先把学过张三老师课程的学生的id查询出来SELECTsc.s_id FROMscore scLEFT JOIN course co ON co.c_id = sc.c_idLEFT JOIN teacher t ON t.t_id = co.t_id WHEREt.t_name = '张三' )-- 15、查询两门及以上不及格课程的同学的学号、姓名及其平均成绩======================================-- 写法1：SELECTst.*,count( sc.s_score ) '课程数量',ROUND( avg( sc.s_score ), 1 ) '平均成绩' 
FROMstudent stLEFT JOIN score sc ON st.s_id = sc.s_id 
WHEREsc.s_score < 60 
GROUP BY st.s_id 
HAVING count( sc.s_score ) >= 2-- 写法2：SELECTst.*,ROUND( AVG( sc.s_score ), 1 ) 
FROMstudent stLEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BYst.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，按分数降序排列的学生信息======================================SELECTst.*,sc.s_score '分数' 
FROMstudent stLEFT JOIN score sc ON st.s_id = sc.s_id 
WHEREsc.s_score < 60 
GROUP BYsc.s_id 
ORDER BYsc.s_score DESC-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩======================================-- 写法1：SELECTst.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 ) '平均成绩' 
FROMstudent stLEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BYsc.s_id 
ORDER BYROUND( AVG( SC.s_score ), 1 ) DESC-- 写法2：SELECTt1.*,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 
WHEREt1.s_id = t2.s_id
ORDER BY avg_s DESC-- 写法3：开窗函数
SELECT sc.*,avg(sc.s_score) over(partition by sc.s_id) '平均分数'
FROMscore sc-- ======================================-- 18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分、及格率，中等率，优良率，优秀率
-- D及格为： >= 60 , C中等为：70-80，B优良为：80-90，A优秀为：>=90SELECTsc.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
FROMcourse coLEFT JOIN score sc ON sc.c_id = co.c_id 
GROUP BYsc.c_id-- 19、按各科成绩进行排序，并显示排名======================================-- 开窗函数写法explain SELECTsc.*,rank() over(partition by sc.c_id  order by sc.s_score desc) rk
FROMscore sc SELECTsc.*,row_number() over(partition by sc.c_id  order by sc.s_score desc) rk
FROMscore sc -- 子查询写法SELECTsc.* ,-- 这个子查询：拿主查询的表数据和相同数据的子表进行比较，用 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 '分数排名'FROMscore sc 
ORDER BYsc.c_id,sc.s_score DESC-- 20、查询学生的总成绩并进行排名======================================-- 写法1：子查询+开窗函数SELECTt.*,rank() over(ORDER BY t.sum_s DESC) rk
FROM (SELECTsc.s_id,sum( sc.s_score ) sum_s FROMscore sc GROUP BYsc.s_id ) t-- 写法2：临时表+子查询-- 创建临时表CREATE TEMPORARY TABLE sum_s_temp ASSELECTsc.s_id,sum( sc.s_score ) sum_s FROMscore sc GROUP BYsc.s_id-- 查询临时表select * from sum_s_temp-- 用两张一样的临时表来一行一行比对SELECTt1.*,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 
FROMsum_s_temp t1 
ORDER BYsum_s DESC

21 - 25 题：


-- 21、查询不同老师所教不同课程平均分从高到低显示-- 普通分组排序写法：SELECTte.*,co.c_name,ROUND(AVG( sc.s_score ),1) avg_s 
FROMteacher teLEFT JOIN course co ON te.t_id = co.t_idLEFT JOIN score sc ON sc.c_id = co.c_id 
GROUP BYte.t_id 
ORDER BYAVG( sc.s_score ) DESC-- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
-- 即语文数学英语成绩都放在一起的第二和第三名-- 2、然后主查询这里再根据条件获取数据
SELECT* 
FROM(-- 1、先用子查询，把分数排名排好序SELECTst.s_name,sc.c_id,co.c_name,sc.s_score,rank () over ( ORDER BY sc.s_score DESC ) rk FROMcourse coLEFT JOIN score sc ON sc.c_id = co.c_idLEFT JOIN student st ON st.s_id = sc.s_id ) t 
WHEREt.rk IN ( 2, 3 )-- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆-- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-86],[85-70],[69-60]，[0-59]及所占百分比SELECTco.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]%" 
FROMscore scleft join course co on sc.c_id = co.c_idGROUP BY sc.c_id-- ☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆-- 24、查询学生平均成绩及其名次 SELECTst.s_name,t.avg_s,-- 2、再用 rank 函数给成绩排名并加上排序rank () over ( ORDER BY t.avg_s DESC ) rk 
FROMstudent 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、查询各科成绩前三名的记录  
SELECTst.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 ) tLEFT JOIN student st ON st.s_id = t.s_idLEFT JOIN course co ON co.c_id = t.c_id 
WHEREt.rk IN ( 1, 2, 3 ) -- dense_rank 和 rank 的区别
SELECT*,dense_rank() over ( PARTITION BY c_id ORDER BY s_score DESC ) rk 
FROMscoreSELECT*,rank() over ( PARTITION BY c_id ORDER BY s_score DESC ) rk 
FROMscore

26 - 35 题：


-- 26、查询每门课程被选修的学生数 -- 写法1：SELECTsum( 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 ) '英语' 
FROMscore-- 写法2：
SELECTsc.c_id,co.c_name,count( sc.c_id ) '选修人数' 
FROMscore scLEFT JOIN course co ON sc.c_id = co.c_id 
GROUP BYsc.c_id-- 27、查询出只有两门课程的全部学生的学号和姓名  SELECTst.s_id,st.s_name 
FROMstudent stLEFT JOIN score sc ON sc.s_id = st.s_id 
GROUP BYst.s_id 
HAVINGcount( sc.c_id ) = 2-- 28、查询男生、女生人数-- 写法1：SELECTsum(case when s_sex = '男' then 1 else 0 end) '男生人数',sum(case when s_sex = '女' then 1 else 0 end) '女生人数'
FROMstudent-- 写法2：
SELECTt1.boy,t2.girl 
FROM( SELECT count( s_sex ) boy FROM student WHERE s_sex = '男' ) t1JOIN ( SELECT count( s_sex ) girl FROM student WHERE s_sex = '女' ) t2-- 写法3：SELECTs_sex,count( s_id ) 
FROMstudent 
GROUP BYs_sex-- 29、查询名字中含有"风"字的学生信息SELECT* 
FROMstudent 
WHEREs_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 
FROMstudent-- 插入同名同性别的数据
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 -- 查询同名同性别学生名单，并统计同名人数SELECTs_name,s_sex,count( s_name ) 
FROMtemp_students 
GROUP BYs_name,s_sex 
HAVINGcount( s_name ) > 1-- 31、查询1990年出生的学生名单 -- like 写法select * from student where s_birth like '1990%'-- YEAR() 函数用于从日期中提取年份部分select * from student where year(s_birth) = 1990-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECTco.c_name,round( avg( sc.s_score ), 2 ) 
FROMscore scLEFT JOIN course co ON co.c_id = sc.c_id 
GROUP BYsc.c_id 
ORDER BYround( avg( sc.s_score ), 2 ) DESC, sc.c_id ASC-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 
SELECTst.s_id,st.s_name,IFNULL(round(avg( sc.s_score )),0) '平均成绩'
FROMscore scRIGHT JOIN student st ON st.s_id = sc.s_id 
GROUP BY sc.s_id
HAVINGround(avg( sc.s_score )) >= 85-- 34、查询课程名称为"数学”，且分数低于60的学生姓名和分数SELECTst.s_name,sc.s_score 
FROMcourse coLEFT JOIN score sc ON co.c_id = sc.c_idLEFT JOIN student st ON st.s_id = sc.s_id 
WHEREco.c_name = "数学" AND sc.s_score < 60select * 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_idselect 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) tGROUP BY t.s_id

36 - 45 题：

-- 36、查询每一门课程成绩都在70分以上的学生的姓名、课程名称和分数 SELECTst.s_name,co.c_name,sc.s_score 
FROMscore scLEFT JOIN course co ON co.c_id = sc.c_idLEFT JOIN student st ON st.s_id = sc.s_id 
WHERE
st.s_id in (-- 先查询出3个成绩都70分以上的学生的idselect s_id from score group by s_id having min(s_score) >= 70)-- 37、查询不及格的课程及学生SELECTst.s_name,co.c_name,sc.s_score 
FROMscore scLEFT JOIN course co ON sc.c_id = co.c_idLEFT JOIN student st ON st.s_id = sc.s_id 
WHEREsc.s_score < 60-- 38、查询课程编号为01语文且课程成绩在80分以上的学生的学号和姓名SELECTst.s_id,st.s_name,co.c_name,sc.s_score 
FROMcourse coLEFT JOIN score sc ON sc.c_id = co.c_idLEFT JOIN student st ON st.s_id = sc.s_id 
WHEREco.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) '英语'
FROMcourse coLEFT JOIN score sc ON co.c_id = sc.c_id-- 长型数据格式SELECTco.c_name, count(sc.s_id) '人数'
FROMcourse coLEFT JOIN score sc ON sc.c_id = co.c_id 
GROUP BYco.c_id-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其所有成绩SELECTst.*,co.c_name,sc.s_score 
FROMstudent stLEFT JOIN score sc ON sc.s_id = st.s_id LEFT JOIN course co ON co.c_id = sc.c_id
WHEREst.s_id = (SELECTsc.s_idFROMteacher teLEFT JOIN course co ON co.t_id = te.t_idLEFT JOIN score sc ON sc.c_id = co.c_id WHEREte.t_name = '张三' ORDER BYsc.s_score DESC -- limit 1 返回查询结果的第一行数据LIMIT 1 )-- 41、查询不同课程但成绩相同的学生的学生编号、课程编号、学生成绩SELECT distinct s1.*,co.c_name,st.s_name
FROMscore s1LEFT JOIN score s2 ON s1.c_id != s2.c_idLEFT JOIN course co ON co.c_id = s1.c_idLEFT JOIN student st ON st.s_id = s1.s_id  	
WHEREs1.s_score = s2.s_score-- 42、查询每门课程成绩最好的前两名 -- 开窗函数 dense_rank 写法SELECTst.s_id,st.s_name,t.c_name,t.s_score,t.drk 
FROMstudent 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 scLEFT 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* 
FROMscore 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 BYs1.c_id,s1.s_score DESC-- 43、统计每门课程的学生选修人数(超过5人的课程才统计)，
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列SELECTsc.c_id '课程编号',count( 1 ) cnt 
FROMscore sc 
GROUP BYsc.c_id 
HAVINGcount( 1 )>= 5 
ORDER BYcnt DESC,  -- 按人数降序排列sc.c_id ASC -- 按课程号升序排列-- 44、检索至少选修两门课程的学生学号SELECTsc.s_id ,st.s_name,count( sc.s_id ) '选修课程数'
FROMscore scLEFT JOIN student st ON st.s_id = sc.s_id 
GROUP BYsc.s_id 
HAVINGcount( sc.s_id ) >= 2-- 45、查询选修了全部课程的学生信息SELECTst.* 
FROMstudent stLEFT JOIN score sc ON st.s_id = sc.s_id 
GROUP BYsc.s_id 
HAVINGcount( 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-01select 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 studentselect 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* 
fromstudent 
whereweekofyear(str_to_date(concat( year ( now()), -- 获取当前年份：2024date_format( s_birth, '%m%d' ) -- 把每个学生的生日的月份和天数), '%Y%m%d' -- 通过 concat 把两个值合并成这个年月日格式) -- 把不规范的字符串日期格式化) -- 获取学生生日日期所在的周是今年的第几个周= weekofyear(now()) -- 获取当前日期是今年的第几个周-- 格式化：SELECT* 
FROMstudent 
WHEREweekofyear( 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* 
FROMstudent 
WHEREweekofyear( 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)

Day106：代码审计-PHP原生开发篇文件安全上传监控功能定位关键搜索1day挖掘

【Web】2024红明谷CTF初赛个人wp(2/4)

场景文本检测识别学习 day02（AlexNet论文阅读、ResNet论文精读）

【话题：工作生活】2022年工作总结--疫情下的上海，疫情中的我。

大语言模型如何工作？

PTA 位运算

ffmpeg 从现有视频中截取一段

【Node.js】短链接

无线游戏手柄的测试（Windows11系统手柄调试方法）

构建高可用性数据库架构：深入探索Oracle Active Data Guard（ADG）

【Leetcode每日一题】递归 - 二叉树剪枝（难度⭐⭐）（50）

DataLoader的使用

RabbitMQ3.13.x之七_RabbitMQ消息队列模型

如何在Flutter应用中配置ipa Guard进行混淆

Spring之事务底层源码解析

【mysql】环境安装、服务启动、密码设置

android百种动画侧滑库、步骤视图、TextView效果、社交、搜房、K线图等源码

CSS实用技巧干货

Golang-长连接-状态推送

golang中接口赋值与方法集

java第三方包学习之lombok

JAVA多线程机制解析-volatilesynchronized

JS变量作用域

Linux下的乱码问题

React-生命周期杂记

React系列之 Redux 架构模式

Vue组件定义

程序员该如何有效的找工作?

分布式熔断降级平台aegis

简单数学运算程序（不定期更新）

聊聊flink的TableFactory

使用 Docker 部署 Spring Boot项目

小程序 setData 学问多

再次简单明了总结flex布局，一看就懂...

Installing ROS on the Raspberry Pi

#周末课堂# 【Linux + JVM + Mysql高级性能优化班】（火热报名中~~~）

(007)XHTML文档之标题——h1~h6

（1）STL算法之遍历容器

(1/2) 为了理解 UWP 的启动流程，我从零开始创建了一个 UWP 程序

(bean配置类的注解开发)学习Spring的第十三天

（笔试题）分解质因式

（第9篇）大数据的的超级应用——数据挖掘-推荐系统

（附源码）ssm高校实验室毕业设计 800008

（蓝桥杯每日一题）love

（六）软件测试分工

（四）TensorRT | 基于 GPU 端的 Python 推理

（学习日记）2024.01.09

(转)setTimeout 和 setInterval 的区别

(轉貼) 資訊相關科系畢業的學生，未來會是什麼樣子？(Misc)

、写入Shellcode到注册表上线

.NET 实现 NTFS 文件系统的硬链接 mklink /J（Junction）

.net和jar包windows服务部署

.net经典笔试题

/boot 内存空间不够

[ IOS ] iOS-控制器View的创建和生命周期

目录

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

数据准备：

创建表sql

添加表数据sql

50道查询题目汇总

01 - 05 题：

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

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

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

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

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

06 - 10 题：

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

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

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

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

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

11 - 15 题：

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

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

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

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

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

16 - 20 题：

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

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

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

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

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

21 - 25 题：

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

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

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

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

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

26 - 35 题：

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

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

28、查询男生、女生人数

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

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

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

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

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

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

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

36 - 45 题：

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

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

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

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

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

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

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

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

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

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

46 - 50 题：

46、查询各学生的年龄

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

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

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

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

具体 SQL 汇总

01 - 05 题：

06 - 10 题：

11 - 15 题：

16 - 20 题：

21 - 25 题：

26 - 35 题：

36 - 45 题：

46 - 50 题：

相关文章：

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