昆山网站制作哪家好,优化服务,国际战事最新消息,wordpress传上去目录 MySQL 50 道查询题#xff0c;足以巩固大部分查询数据准备#xff1a;创建表sql添加表数据sql 50道查询题目汇总01 - 05 题#xff1a;1、查询 “01” 语文成绩比 “02” 数学成绩高的学生的信息及课程分数2、查询 01语文课程比02数学课程成绩… 目录 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 否则返回nullelse 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、查询两门及以上不及格课程的同学的学号、姓名及其平均成绩-- 写法1SELECTst.*,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-- 写法2SELECTst.*,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、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩-- 写法1SELECTst.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-- 写法2SELECTt1.*,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-80B优良为80-90A优秀为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-- 这个子查询报错信息【- Cant 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、查询每门课程被选修的学生数 -- 写法1SELECTsum( 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、查询男生、女生人数-- 写法1SELECTsum(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-- 写法3SELECTs_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 01select * from student -- 写法1select * 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 01select * 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)
