SQL练习题
sql文件
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`c_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('01', '语文', '02');
INSERT INTO `course` VALUES ('02', '数学', '01');
INSERT INTO `course` VALUES ('03', '英语', '03');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`c_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`s_score` int NULL DEFAULT NULL,
PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of score
-- ----------------------------
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);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`s_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`s_birth` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`s_sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
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', '女');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`t_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('01', '张三');
INSERT INTO `teacher` VALUES ('02', '李四');
INSERT INTO `teacher` VALUES ('03', '王五');
SET FOREIGN_KEY_CHECKS = 1;
数据表关系结构
1.查询课程编号为"01"的课程比"02"的课程成绩高的学生学号、姓名和这两门课的成绩
难点在于在成绩表中将学号和两门成绩放在同一行，如果做到这一点就很容易解决问题了
先在score表分别找出课程01和02的信息，然后内连接使得01和02的信息凑成一行
然后再内连接学生表按要求查询
-- 查询课程编号为"01"的课程比"02"的课程成绩高的学生学号、姓名和这两门课的成绩
SELECT a.s_id '学号',s.s_name '姓名',a.s_score '01成绩',b.s_score '02成绩' from
(SELECT * from score WHERE c_id = '01') a
INNER JOIN
(SELECT * from score WHERE c_id = '02') b
on a.s_id = b.s_id
INNER JOIN student s on a.s_id = s.s_id
WHERE a.s_score > b.s_score
小bug：上述情况没考虑只修了一门课的学生如06、07号，就当语数英可选修，sql可改写成左连接，保证查到所有选修了课程01的学生
方法2：自连接
再和学生表进行多表查询
-- 查询课程编号为"01"的课程比"02"的课程成绩高的学生学号、姓名和这两门课的成绩
SELECT s.s_id '学号',s.s_name '姓名', a.s_score '01成绩',b.s_score '02成绩'FROM
score a,score b,student s
WHERE a.c_id = '01'
and b.c_id = '02'
and a.s_id = b.s_id
and s.s_id = a.s_id
and a.s_score > b.s_score
方法3：casewhen
casewhen能对每一条数据进行筛选，符合条件就返回then后的数据，否则返回null，截图太长就以三个学生的数据为例。
加上max...group by是为了提取有用的数据，按学号分组，学号01的学生01成绩的三条数据max就取80,02成绩同理取90，如此就能合成为1条数据了。
然后把整个结果当成一个表t，但是要把t内的别名都去掉，不然不好调用t的字段
-- 查询课程编号为"01"的课程比"02"的课程成绩高的学生学号、姓名和这两门课的成绩
SELECT
s.s_id '学号',
s.s_name '姓名',
t.s01 '01成绩',
t.s02 '02成绩'
FROM
(
SELECT a.s_id,
max(case when a.c_id='01' then a.s_score end) s01,
max(case when a.c_id='02' then a.s_score end) s02
from score a
GROUP BY a.s_id
HAVING s01 > s02 OR s02 is null
) t,student s
WHERE s.s_id = t.s_id
2.查询平均成绩大于60分的学生学号、姓名和平均成绩
先以学号为分组查出score表的每个学生的平均分（round用于定义保留小数点后几位数），把结果当成a表和学生表联合查询
-- 查询平均成绩大于60分的学生学号、姓名和平均成绩
SELECT a.id '学号',b.s_name '姓名',a.score '平均成绩'
FROM
(
SELECT s_id id,ROUND(AVG(s_score),2) score
FROM score
GROUP BY s_id
) a,student b
WHERE a.id = b.s_id AND a.score > 60
简化score表的查询结果
SELECT s_id id,ROUND(AVG(s_score),2) score
FROM score
GROUP BY s_id HAVING score > 60
用having直接对分组后的数据进行筛选，就不用嵌套查询了
3.查询所有学生的学号、姓名、选课数和总成绩
单纯看成绩表是不能把没有选课的学生统计到的，如8号王菊
-- 查询所有学生的学号、姓名、选课数和总成绩
SELECT a.s_id '学号',a.s_name '姓名',COUNT(b.c_id) '选课数',IFNULL(SUM(b.s_score),0) '总成绩'
FROM student a
LEFT JOIN score b
ON a.s_id = b.s_id
GROUP BY b.s_id
-- ifnull处理sum结果为null时转为0
4.查询姓“猴”的老师的个数
-- 查询姓“猴”的老师的个数
SELECT COUNT(t_id) '个数'
FROM teacher
WHERE t_name like '猴%'
5.查询没学过张三老师课的学生的学号和姓名
先在teacher表根据名字查询tid，tid能在course表中进一步查到cid，cid能在score表中查到sid，sid能在student表中查到sname。整个链条是步步关联的，所以可以联表查询。
-- 查询没学过张三老师课的学生的学号和姓名
SELECT s_id '学号',s_name '姓名'
FROM student
WHERE s_id NOT IN
(
SELECT sc.s_id FROM teacher t
LEFT JOIN course c ON t.t_id = c.t_id
LEFT JOIN score sc ON c.c_id = sc.c_id
LEFT JOIN student st ON sc.s_id = st.s_id
WHERE t_name = '张三'
)
当然也可以一步步地嵌套查询，但太多了。
6.查询学过张三老师所有课的学生的学号和姓名
这里为了避免歧义，往course表新增张三的物理课，再在score表新增8号学生的数学物理成绩。这样就指明是查张三所有课的学生信息，而不是查学过张三课的所有学生信息，两者区别在于张三教一门课还是多门课。
如果沿用上一题的思路，查到的会是学过张三课的学生，而不是所有课的。
按理应该只有08号学生王菊符合要求,学号08的结果有两条，可通过以学号来分组，再having筛选出结果数与张三课程数相等的结果
-- 查询学过张三老师所有课的学生的学号和姓名
SELECT sc.s_id '学号',st.s_name '姓名' FROM teacher t
LEFT JOIN course c ON t.t_id = c.t_id
LEFT JOIN score sc ON c.c_id = sc.c_id
LEFT JOIN student st ON sc.s_id = st.s_id
WHERE t_name = '张三'
GROUP BY sc.s_id
HAVING COUNT(sc.s_id) = (
SELECT COUNT(a.c_id)
FROM course a
INNER JOIN teacher b ON a.t_id = b.t_id
WHERE b.t_name = '张三'
)
7.查询学过01和02课程的学生学号与姓名
由于不能通过c_id = '01' and c_id = '02'查到
需要取这两个结果的交集
-- 查询学过01和02课程的学生学号与姓名
SELECT s_id '学号',s_name '姓名'
FROM student
WHERE s_id IN
(
SELECT a.s_id
FROM
(
SELECT s_id FROM score
WHERE c_id = '01'
) a
INNER JOIN
(
SELECT s_id FROM score
WHERE c_id = '02'
) b
ON a.s_id = b.s_id
)
8.查询02号课程的总成绩
-- 查询02号课程的总成绩
SELECT *,SUM(s_score) FROM score WHERE c_id = '02'
方法2
分组，不过复杂了
-- 查询02号课程的总成绩
SELECT c_id,SUM(s_score) FROM score
GROUP BY c_id
HAVING c_id = '02'
9.查询所有课程成绩都小于60分的学生学号和姓名
为更好表现，把score表的5号学生的01课程分数从76改成56.符合要求的只有04和06
方法1
根据学号分组，再筛选出最大值小于60的学生
-- 查询所有课程成绩都小于60分的学生学号和姓名
SELECT sc.s_id '学号',s_name '姓名'
FROM score sc,student st
WHERE sc.s_id = st.s_id
GROUP BY sc.s_id
HAVING MAX(s_score) < 60
方法2
找到所学课程数与不合格课程数相等的学生
-- 查询所有课程成绩都小于60分的学生学号和姓名
SELECT a.s_id,cnt '所学课程数',cntm '不合格课程数' FROM
(
SELECT s_id,COUNT(c_id) cnt FROM score GROUP BY s_id -- 所学课程的数量
) a
INNER JOIN
(
SELECT s_id,COUNT(s_id) cntm FROM score WHERE s_score < 60 GROUP BY s_id -- 成绩小于60的课程数
) b
ON a.s_id =b.s_id
WHERE cnt = cntm
10.查询没有学全所有课的学生学号和姓名
新开个数据库，使用原sql文件建表
以学生为单位筛选a表选修课程数小于课程总数的学生
-- 查询没有学全所有课的学生学号和姓名
SELECT s_id '学号',s_name '姓名'
FROM (
SELECT st.s_id,s_name,c_id,s_score FROM student st
LEFT JOIN score sc ON st.s_id = sc.s_id
) a -- 学生表左连接成绩表，防止漏掉没选课的学生
GROUP BY s_id HAVING IFNULL(COUNT(c_id),0) < -- 以学生为单位筛选a表选修课程数小于课程总数的学生
(
SELECT COUNT(c_id) cnt FROM course -- 课程总数
)
另一种思路就是找出学过全部课程的学号然后反选
11.查询至少有一门课与学号01的学生所学课程相同的其他学生学号和姓名
-- 查询至少有一门课与学号01的学生所学课程相同的其他学生学号和姓名
SELECT DISTINCT sc.s_id '学号',s_name '姓名'
FROM score sc,student st
WHERE c_id
IN
(
SELECT c_id FROM score WHERE s_id = '01'
)
AND sc.s_id != '01' AND sc.s_id = st.s_id
12.查询与学号01的学生所学课程完全相同的其他学生学号和姓名
为突出题目效果，在score表中给7号学生新增4号课程的成绩74分
-- 查询与学号01的学生所学课程完全相同的其他学生学号和姓名
SELECT s_id,s_name FROM student WHERE s_id IN
(
SELECT s_id FROM score
WHERE s_id != '01'
GROUP BY s_id HAVING COUNT(c_id) = (
SELECT COUNT(c_id) FROM score WHERE s_id = '01' -- 01学生所学课程数
) -- 找出学了跟01学生学了同等数量课程的其他学生
)
AND s_id NOT IN -- 排除肯定不与01的学生所学课程相同的学生
(
SELECT DISTINCT s_id FROM score WHERE c_id NOT IN -- 找出相比01所选课程选了其他课的学生
(
SELECT c_id FROM score WHERE s_id = '01' -- 学号01的学生所学课程
)
)
13.查询两门及其以上课程不及格的学生学号、姓名和平均成绩
-- 查询两门及其以上课程不及格的学生学号、姓名和平均成绩
SELECT a.s_id '学号',s_name '姓名',b.avg '平均分' FROM student a
INNER JOIN
(
SELECT s_id,ROUND(AVG(s_score),1) avg
FROM score
WHERE s_score < 60
GROUP BY s_id
HAVING COUNT(c_id) >= 2
) b
ON a.s_id = b.s_id
14.查询01课程小于60分的学生信息并按分数降序排列
-- 查询01课程小于60分的学生信息并按分数降序排列
SELECT b.*,a.s_score FROM
(
SELECT s_id,s_score FROM score WHERE c_id = '01' AND s_score < 60 -- 01课程小于60分的学生
) a
INNER JOIN student b
ON a.s_id = b.s_id
ORDER BY a.s_score DESC
15.查询所有学生的所有课程成绩以及平均分并按平均分从高到低排列
方法1
-- 查询所有学生的所有课程成绩以及平均分并按平均分从高到低排列
SELECT a.*,b.avg FROM
(
SELECT st.s_id,c_id,s_score FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id
) a -- 避免没选课的学生被漏掉
INNER JOIN
(
SELECT s_id,ROUND(AVG(s_score),1) avg FROM score GROUP BY s_id
) b -- 各学生的平均分
ON a.s_id = b.s_id
ORDER BY avg DESC
方法2
仅适用于课程数较少的情况，因为这种方法需要手动行转列，有几门课就要写几次case when
-- 查询所有学生的所有课程成绩以及平均分并按平均分从高到低排列
SELECT s_id,
MAX(CASE WHEN c_id = '01' THEN s_score ELSE NULL END) '语文',
-- GROUP BY后利用case when行转列，但出于规范在GROUP BY中查询的应该是统计函数相关的
-- 所以用max或者min也行
MAX(CASE WHEN c_id = '02' THEN s_score ELSE NULL END) '数学',
MAX(CASE WHEN c_id = '03' THEN s_score ELSE NULL END) '英语',
MAX(CASE WHEN c_id = '04' THEN s_score ELSE NULL END) '物理',
ROUND(AVG(s_score),1) '平均分'
FROM
(SELECT st.s_id,c_id,s_score FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id) a -- 避免没选课的学生被漏掉
GROUP BY s_id
16.查询各科成绩最高分、最低分、平均分和及格率
关键在于巧用外连接把score缺少的各科目合格数pass连接上去
-- 查询各科成绩最高分、最低分、平均分和及格率
SELECT c_id '课程id',MAX(s_score) '最高分',
MIN(s_score) '最低分',
ROUND(AVG(s_score),1) '平均分',
(CASE WHEN pass/COUNT(c_id) THEN CONCAT(ROUND((pass/COUNT(c_id)*100),1),'%')ELSE CONCAT(0,'%') END) '合格率' -- concat将数值以%形式显示
-- 判断条件可以不写成IFNULL(pass,0)/COUNT(a.c_id)，因为当case when的条件是null时，会去到else的分支
FROM
(
SELECT a.*,b.pass FROM score a LEFT JOIN
(
SELECT c_id,COUNT(c_id) pass FROM score WHERE s_score >= 60 GROUP BY c_id -- 统计出每门课合格的数量
) b -- 不包含所有成绩都不合格的课程，所以要left join和select要用case when显示合格率结果
ON a.c_id = b.c_id
) c
GROUP BY c_id
17.按各科成绩排序，并显示排名
如图是三个常用的排序类窗口函数，row_number(),rank(),dense_rank()
row_number()是连续的，即使同分排名也会有先后。
dense_rank()在同分的情况下排名相同，下一个不同分的排名+1
rank()在同分的情况下排名相同，但下一个不同分的排名+同分的数量（是第几个人就排几个）
-- 按各科成绩排序，并显示排名
SELECT sc.s_id '学号',s_name '姓名',c_name '科目',s_score '成绩',
RANK() OVER(PARTITION BY c_name ORDER BY s_score DESC) '排名' FROM score sc -- 按rank()的方式以科目为分组，根据成绩降序排列
INNER JOIN course c ON sc.c_id = c.c_id
INNER JOIN student st ON sc.s_id = st.s_id
18.查询学生总成绩并降序排名
新增9号学生陈空，体现没有选课的情形
不需要partition by
-- 查询学生总成绩并降序排名
SELECT s_id,SUM(score) total,RANK() OVER(ORDER BY SUM(score) DESC) 'rank' FROM
(
SELECT st.s_id,c_id,IFNULL(s_score,0) score FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id -- 避免漏掉没成绩的学生
) a
GROUP BY s_id
19.查询各老师所教课程的平均分，从高到低显示
数据表只涉及一个老师教多门课程的情况，如果后续有一门课程有多个老师的情况再补充sql
-- 查询各老师所教课程的平均分，从高到低显示
SELECT t_name '姓名',c_name '课程',ROUND(AVG(a.s_score),1) '平均分'FROM
(
SELECT t.t_id,t_name,c.c_id,c_name,s.s_id,s_score FROM teacher t
LEFT JOIN course c ON t.t_id = c.t_id
LEFT JOIN score s ON c.c_id = s.c_id
) a
GROUP BY t_id,c_id -- 以联合老师id和课程id为分组
ORDER BY AVG(a.s_score) DESC
20.查询每门课程的成绩在第二和第三名的学生信息及该课程成绩
先排名再筛选
用in筛选范围
-- 查询每门课程的成绩在第二和第三名的学生信息及该课程成绩
SELECT * FROM
(
SELECT st.*,sc.c_id,c_name,s_score,
DENSE_RANK() OVER(PARTITION BY sc.c_id ORDER BY s_score DESC) 'rank'
FROM score sc
INNER JOIN student st ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
) a
WHERE a.rank in (2,3)
21.使用分段[100-85),[85-70),[70-60),[<60]来统计各科成绩，分别统计各分数段人数
统计人数用sum或者count，而要显示多个分数段就需要多列，用casewhen更合适
-- 使用分段[100-85),[85-70),[70-60),[<60]来统计各科成绩，分别统计各分数段人数
SELECT c.c_id,c.c_name,
SUM(CASE WHEN s.s_score <= 100 AND s.s_score > 85 THEN 1 ELSE 0 END) '(85,100]',
-- 85-100分数段的人数，case when来判断，sum来统计
COUNT(CASE WHEN s.s_score <= 85 AND s.s_score > 70 THEN 1 ELSE NULL END) '(70,85]',
-- 第二种写法count只统计not null的，0也算，所以else要写null
SUM(CASE WHEN s.s_score <= 70 AND s.s_score > 60 THEN 1 ELSE 0 END) '(60,70]',
SUM(CASE WHEN s.s_score <= 60 AND s.s_score > 0 THEN 1 ELSE 0 END) '(0,60]'
FROM score s
INNER JOIN course c ON s.c_id= c.c_id
GROUP BY c.c_id,c.c_name -- 为了显示课程名就联合分组了
22.查询学生平均成绩及其名次
-- 查询学生平均成绩及其名次
SELECT s_id,s_name,ROUND(AVG(score),1) avg,
RANK() OVER(ORDER BY AVG(score) DESC) 'rank'
FROM
(
SELECT st.s_id,s_name,c_id,IFNULL(s_score,0) score FROM student st LEFT JOIN score sc ON st.s_id = sc.s_id
) a
GROUP BY s_id
23.查询每门课被选修的学生数
新增5号生物课，模拟存在有的课没人选的情况
-- 查询每门课被选修的学生数
SELECT c.c_id,c_name,COUNT(s_id)
FROM score s
RIGHT JOIN course c ON s.c_id = c.c_id -- 避免漏掉没人选的课
GROUP BY c.c_id,c_name -- 如果只以c_id分组，mysql中没错，sql sever会报错
24.查询只有两门课的学生学号和姓名
-- 查询只有两门课的学生学号和姓名
SELECT sc.s_id,s_name FROM score sc
INNER JOIN student st ON sc.s_id = st.s_id
GROUP BY s_id HAVING COUNT(c_id) = 2
25.查询男女生人数
-- 查询男女生人数
SELECT s_sex,COUNT(s_id) FROM student GROUP BY s_sex
-- 不分组的话可以用sum或者count结合case when
26.查询名字含有“风”的学生信息
-- 查询名字含有“风”的学生信息
SELECT * FROM student WHERE s_name LIKE '%风%'
27.查询1990年出生的学生信息
两种方法，1是like，2是时间函数
-- 查询1990年出生的学生信息
SELECT * FROM student WHERE s_birth LIKE '1990%'
SELECT * FROM student WHERE YEAR(s_birth) = '1990' -- YEAR(date)函数能返回date格式的年份值
-- Date格式YYYY-MM-DD
-- YYYYMMDD
-- YYYY/MM/DD
SELECT MONTH('2022-05-23 15:30:00'); -- 结果为5，Month(date)函数能返回date格式的月份值
SELECT DAY('2022-05-23 15:30:00'); -- 结果为23，Day(date)函数能返回date格式的日份值
select sysdate(); -- 2023-07-23 18:29:03 获得当前日期+时间
select curdate(); -- 2023-07-23 获取当前日期
select curtime(); -- 18:30:58 获取当前时间
SELECT NOW() -- 2023-07-23 18:29:15 当前UTC日期时间函数
SELECT DATE(NOW()) -- 2023-07-28 年月日
28.查询平均成绩大于等于85的所有学生的信息
-- 查询平均成绩大于等于85的所有学生的信息
SELECT st.*,ROUND(AVG(s_score),1) avg FROM score sc
INNER JOIN student st ON st.s_id = sc.s_id
GROUP BY s_id HAVING AVG(s_score) >= 85
29.查询每门课的平均分，按平均分升序排序，同分则按课程号降序排列
-- 查询每门课的平均分，按平均分升序排序，同分则按课程号降序排列
SELECT s.c_id,c_name,ROUND(AVG(s_score),1) avg FROM score s
INNER JOIN course c ON c.c_id = s.c_id
GROUP BY c_id ORDER BY AVG(s_score),c_id DESC
30.查询数学成绩不及格（<60）的学生姓名和分数
-- 查询数学成绩不及格（<60）的学生姓名和分数
SELECT c_name,s_name,s_score FROM score sc
INNER JOIN course c ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE c_name = '数学' AND s_score < 60
31.查询所有学生的各课程成绩
暂时把course的04号生物课删掉
-- 查询所有学生的各课程成绩
SELECT st.s_id,st.s_name,
MAX(CASE WHEN c.c_name = '语文' THEN sc.s_score ELSE NULL END) '语文',
MAX(CASE WHEN c.c_name = '数学' THEN sc.s_score ELSE NULL END) '数学',
MAX(CASE WHEN c.c_name = '英语' THEN sc.s_score ELSE NULL END) '英语',
MAX(CASE WHEN c.c_name = '物理' THEN sc.s_score ELSE NULL END) '物理'
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
GROUP BY st.s_id
32.查询成绩在70分以上的学生姓名、课程名称和分数
-- 查询成绩在70分以上的学生姓名、课程名称和分数
SELECT s_name,c_name,s_score FROM score sc
INNER JOIN course c ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE s_score > 70
33.查询不及格的学生及其课程，按课程号从大到小排列
-- 查询不及格的学生及其课程，按课程号从大到小排列
SELECT s_name,sc.c_id,c_name,s_score FROM score sc
INNER JOIN student st ON st.s_id = sc.s_id
INNER JOIN course c ON c.c_id = sc.c_id
WHERE s_score < 60
ORDER BY sc.c_id DESC
34.查询课程编号为03且成绩在80分以上的学生学号和姓名
-- 查询课程编号为03且成绩在80分以上的学生学号和姓名
SELECT sc.s_id,s_name,s_score FROM score sc
INNER JOIN student st ON sc.s_id = st.s_id
WHERE c_id = '03' AND s_score > 80
35.查询每门课程的学生人数
-- 查询每门课程的学生人数
SELECT c.c_id,c_name,COUNT(s_id) 'num'FROM course c
LEFT JOIN score s ON c.c_id = s.c_id
GROUP BY c.c_id
36.查询选修“张三”老师所授课程成绩最好的学生学号、姓名和课程成绩
方法1 子查询max&With
-- 查询选修“张三”老师所授课程成绩最好的学生学号、姓名和课程成绩
SELECT *
FROM
(
SELECT sc.s_id,s_name,c_name,s_score FROM course c
INNER JOIN teacher t ON c.t_id = t.t_id
INNER JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE t_name = '张三'
) a
WHERE a.s_score = -- WHERE后面不能等于聚合函数如where a.s_score = MAX(a.s_score)，所以要子查询
(
SELECT MAX(s_score)
FROM a
)
可以使用with把联表查询得到的新表a设为临时内存表，这样系统就能直接select from了。
在 MySQL 中，WITH 子句通常被称为 "Common Table Expressions"（CTE），俗称内存临时表，并且是从 MySQL 8.0 版本开始引入的。
-- 查询选修“张三”老师所授课程成绩最好的学生学号、姓名和课程成绩
WITH target AS ( -- 创建一个临时内存表
SELECT sc.s_id,s_name,c_name,s_score FROM course c
INNER JOIN teacher t ON c.t_id = t.t_id
INNER JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE t_name = '张三'
)
SELECT *
FROM
target
WHERE s_score = -- WHERE后面不能等于聚合函数如where a.s_score = MAX(a.s_score)，所以要子查询
(
SELECT MAX(s_score)
FROM target
)
方法2 limit
-- 查询选修“张三”老师所授课程成绩最好的学生学号、姓名和课程成绩
SELECT sc.s_id,s_name,c_name,s_score FROM course c
INNER JOIN teacher t ON c.t_id = t.t_id
INNER JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = sc.s_id
WHERE t_name = '张三'
ORDER BY s_score DESC
LIMIT 1 -- 等值于limit 0,1
37.统计每门课程的选修人数，按人数降序排列，若人数相同则按课程号升序排列
-- 统计每门课程的选修人数，按人数降序排列，若人数相同则按课程号升序排列
SELECT c.c_id,c_name,COUNT(s_id) FROM course c
LEFT JOIN score s ON c.c_id = s.c_id
GROUP BY c.c_id
ORDER BY COUNT(s_id) DESC,c.c_id
38.查询至少选修两门课的学生学号和姓名
-- 查询至少选修两门课的学生学号和姓名
SELECT sc.s_id,s_name,COUNT(c_id) FROM score sc
INNER JOIN student st ON sc.s_id = st.s_id
GROUP BY sc.s_id
HAVING COUNT(c_id) >= 2
39.查询选修全部课程的学生信息
新增9号学生，学过所有课程
UPDATE `exercises`.`student` SET `s_birth` = '1990-09-09', `s_sex` = '男' WHERE `s_id` = '09';
INSERT INTO `score`(`s_id`, `c_id`, `s_score`) VALUES ('09', '01', 91);
INSERT INTO `score`(`s_id`, `c_id`, `s_score`) VALUES ('09', '02', 92);
INSERT INTO `score`(`s_id`, `c_id`, `s_score`) VALUES ('09', '03', 93);
INSERT INTO `score`(`s_id`, `c_id`, `s_score`) VALUES ('09', '04', 94);
INSERT INTO `score`(`s_id`, `c_id`, `s_score`) VALUES ('09', '05', 95);
-- 查询选修全部课程的学生信息
SELECT st.* FROM score sc
INNER JOIN student st ON sc.s_id = st.s_id
GROUP BY st.s_id
HAVING COUNT(c_id) = (
SELECT COUNT(c_id) FROM course
)
40.查询各学生的年龄
-- 查询各学生的年龄
SELECT s_name,s_birth,YEAR(NOW())-YEAR(s_birth) age FROM student
-- 方式2 DATEDIFF(NOW(),s_birth)/365
SELECT s_name,s_birth,FLOOR(DATEDIFF(NOW(),s_birth)/365) age FROM student -- floor向下取整
-- DATEDIFF(a,b) a-b的天数差
-- SELECT DATEDIFF('2022-04-30','2022-04-29'); -- 1
-- SELECT DATEDIFF('2022-04-30','2022-04-30'); -- 0
-- SELECT DATEDIFF('2022-04-29','2022-04-30');-- -1
41.查询下周过生日的学生
题目的意思是下一周过生日，不是7天后过生日
考虑使用week函数通过对比周数来找到结果。
周数的范围从一年的总时间来划分的
-- SELECT WEEK(date[,mode])
SELECT WEEK(NOW(),1) -- 30
SELECT WEEK('1990-05-07',1) -- 19
SELECT WEEK('2000-05-07',1) -- 18
-- week(s_birth,1) = WEEK(date(NOW()),1) + 1行不通
但是这种方法对于不同年份的同一天week的结果是不同的，也对于生日在本月最后一周的情况不适用。
所以把前面生日的年份换成今年的年份，通过拼接和截取的方式换成今年的；再比较周数
不过当现在时间的周数是52的时候，无法确定下一周还是不是在今年。53的情况可以case when，当前周数53说明下一周必定在明年，下周的week值必是1
-- 查询下周过生日的学生
SELECT * FROM student WHERE WEEK(CONCAT(YEAR(NOW()),SUBSTRING(s_birth,5,6)),1) = WEEK(NOW(),1) + 1
/*
SELECT YEAR(NOW()) -- 2023
SELECT SUBSTRING(s_birth,5,6) FROM student -- 比如-01-01
SELECT CONCAT(YEAR(NOW()),SUBSTRING(s_birth,5,6)) FROM student --比如2023-01-01
SELECT WEEK(NOW(),1) -- 30
SELECT WEEK('2023-08-06',1) -- 31
*/
42.查询本月过生日的学生
-- 查询本月过生日的学生
SELECT * FROM student WHERE MONTH(s_birth) = MONTH(DATE(NOW()))
-- month返回月份值，DATE(NOW())可换成CURRENT_DATE
43.查询下个月过生日的学生
-- 查询下个月过生日的学生
SELECT * FROM student WHERE MONTH(s_birth) = MOD(MONTH(CURRENT_DATE)+1,12) -- 对12取余，解决1月生日的情况
44.冒险者和金币
假设有一家冒险者公会，他们有一张名为 rewards 的表格，用于记录每个冒险者在各个任务中获得的金币奖励情况。
表格字段如下：
- adventurer_id：冒险者ID，唯一标识每个冒险者。
- adventurer_name：冒险者姓名。
- task_id：任务ID，唯一标识每个任务。
- task_name：任务名称。
- reward_coins：冒险者在该任务中获得的金币奖励数量。
请你编写一条 SQL 查询语句，依次输出每个冒险者的 id（adventurer_id）、冒险者姓名（adventurer_name）、获得的总金币奖励（total_reward_coins），并按照总金币奖励从高到低排序，其中只列出总金币奖励排名前 3 的冒险者。
SELECT adventurer_id, adventurer_name,
SUM(reward_coins) AS total_reward_coins
FROM rewards
GROUP BY adventurer_id, adventurer_name
ORDER BY total_reward_coins DESC
LIMIT 3;
45.魔法学院
假设有一家魔法学院，里面有许多学员在不同科目上进行学习和考试。有一张名为magic_scores的表格，用于记录每位学员在不同科目中的考试成绩情况。表格字段如下：
- student_id：学员ID，唯一标识每位学员。
- student_name：学员姓名。
- subject_id：科目ID，唯一标识每个科目。
- subject_name：科目名称。
- score：学员在该科目的考试成绩。
请你编写一条 SQL 查询语句，依次输出每位学员的学院 ID（student_id）、学员姓名（student_name）、科目 ID（subject_id）、科目名称（subject_name）、学员在该科目的考试成绩（score）、该学员在每个科目中的成绩排名（score_rank），并将结果按照成绩从高到低进行排序。
select student_id,student_name,subject_id,subject_name,score,
rank() over(partition by subject_id order by score desc) as score_rank
from magic_scores
46.大浪淘鸡
在神秘的海岛上，有一只传说中的大浪淘鸡，它身躯高大威武，羽毛闪烁着神秘的光芒。岛上的居民都传说大浪淘鸡是海洋之神的化身，它能够操纵海浪，带来平静或狂暴的海洋。为了验证这个传说是否属实，岛上的居民决定对大浪淘鸡进行观测和记录。
有一张 chicken_observation 的表格，用于记录居民观测大浪淘鸡的信息。表格字段如下：
- observation_id：观测记录ID，唯一标识每条观测记录
- observer_name：观测者姓名
- observation_date：观测日期
- observation_location：观测地点
- wave_intensity：观测到的海浪强度，用整数表示，数值越大，海浪越狂暴
请你编写一条 SQL 查询语句，找出观测地点包含 "大浪淘鸡" 且海浪强度超过 5 的观测记录，并依次输出每位观测者的姓名（observer_name）、观测日期（observation_date）以及观测到的海浪强度（wave_intensity）。
select observer_name,observation_date,wave_intensity
from chicken_observation
where observation_location like '%大浪淘鸡%'
and wave_intensity > 5
