前言：本博客仅作记录学习使用，部分图片出自网络，如有侵犯您的权益，请联系删除
前期准备
首先我们准备4个表（学生表、课程表、教师表和成绩表）
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` int(0) NOT NULL COMMENT '课程编号',
`c_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程名称',
`t_id` int(0) NOT NULL COMMENT '教师编号',
PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '语文', 2);
INSERT INTO `course` VALUES (2, '数学', 1);
INSERT INTO `course` VALUES (3, '英语', 3);
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'id',
`s_id` int(0) NOT NULL COMMENT '学生编号',
`c_id` int(0) NOT NULL COMMENT '课程编号',
`s_score` int(0) NOT NULL COMMENT '分数',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 80);
INSERT INTO `score` VALUES (2, 1, 2, 90);
INSERT INTO `score` VALUES (3, 1, 3, 99);
INSERT INTO `score` VALUES (4, 2, 1, 70);
INSERT INTO `score` VALUES (5, 2, 2, 60);
INSERT INTO `score` VALUES (6, 2, 3, 80);
INSERT INTO `score` VALUES (7, 3, 1, 80);
INSERT INTO `score` VALUES (8, 3, 2, 80);
INSERT INTO `score` VALUES (9, 3, 3, 80);
INSERT INTO `score` VALUES (10, 4, 1, 50);
INSERT INTO `score` VALUES (11, 4, 2, 30);
INSERT INTO `score` VALUES (12, 4, 3, 20);
INSERT INTO `score` VALUES (13, 5, 1, 76);
INSERT INTO `score` VALUES (14, 5, 2, 87);
INSERT INTO `score` VALUES (15, 6, 1, 31);
INSERT INTO `score` VALUES (16, 6, 3, 34);
INSERT INTO `score` VALUES (17, 7, 2, 89);
INSERT INTO `score` VALUES (18, 7, 3, 98);
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` int(0) NOT NULL COMMENT '学生编号',
`s_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '课程名称',
`s_birth` date NOT NULL COMMENT '出生年月',
`s_sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '学生性别',
PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES (2, '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES (3, '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES (4, '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES (5, '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES (6, '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES (7, '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES (8, '王菊', '1990-01-20', '女');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` int(0) NOT NULL COMMENT '教师编号',
`t_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '教师姓名',
PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '张三');
INSERT INTO `teacher` VALUES (2, '李四');
INSERT INTO `teacher` VALUES (3, '王五');
50道练习题
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
（1）使用自连接（INNER JOIN）连接student表和score表，获取学生信息和成绩信息，再将score表自身连接，分别获取课程01和02的成绩信息，并通过成绩比较筛选出符合条件的记录
SELECT
c.*,
a.s_score AS s01,
b.s_score AS s02
FROM
student c
INNER JOIN score a ON c.s_id = a.s_id AND a.c_id = '01'
INNER JOIN score b ON c.s_id = b.s_id AND b.c_id = '02'
WHERE
a.s_score > b.s_score;关键点： 确保表连接顺序合理，连接条件完整，避免笛卡尔积；确保score表的s_id和c_id字段上有索引，以提高查询效率
（2） 用 case when 语句构造子查询，从 score 表里提取出每个学生的课程 01 和 02 的成绩，再将子查询结果和 student 表连接，通过比较筛选出课程 01 成绩高于课程 02 的学生成绩及信息
select
s.*,
t.s01,t.s02
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) t ,student s
where
t.s01>t.s02
and t.s_id=s.s_id;2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
（1） 先按学生ID分组计算平均成绩，再用子查询关联学生表获取对应名字，最后用HAVING筛选出平均成绩≥60分的学生
SELECT
a.s_id, --成绩表中的学生ID
(SELECT s_name FROM student s WHERE s.s_id = a.s_id) s_name, --子查询获取对应学生姓名
AVG(a.s_score) avg_s --计算平均成绩，别名为avg_s
FROM score a
GROUP BY a.s_id --按学生ID分组
HAVING AVG(a.s_score) >= 60; --筛选平均成绩大于等于60分的组（2） 通过连接成绩表和学生表，按学生ID分组计算平均成绩，最后用HAVING子句筛选出平均成绩≥60分的学生，返回其ID和名字
SELECT
a.s_id, --成绩表中的学生ID
s.s_name, --学生表中的学生姓名
AVG(a.s_score) avg_s --计算平均成绩，别名为avg_s
FROM score a, student s
WHERE a.s_id = s.s_id --连接条件，关联成绩表和学生表
GROUP BY a.s_id -- 按学生ID分组
HAVING AVG(a.s_score) >= 60; -- 筛选平均成绩大于等于60分的组
--更清晰的连接写法:------------------
SELECT
a.s_id,
s.s_name,
AVG(a.s_score) AS avg_s
FROM score a
INNER JOIN student s ON a.s_id = s.s_id --显式连接学生表和成绩表
GROUP BY a.s_id, s.s_name --按学生ID和姓名分组
HAVING AVG(a.s_score) >= 60 --筛选平均成绩大于等于60分的学生
ORDER BY avg_s DESC; --可选：按平均成绩降序排列4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
若使用上题连接的话，会因为在score表中没有8号的成绩而导致查询不出8号成绩也小于60，因此改用外连接即可
SELECT
s.s_id,
s.s_name,
IFNULL(AVG(a.s_score), 0) avg_s --计算平均成绩，如果没有成绩则默认为0
FROM score a
RIGHT JOIN student s ON a.s_id = s.s_id --右连接确保所有学生都被包含
GROUP BY s.s_id --按学生ID分组
HAVING IFNULL(AVG(a.s_score), 0) <= 60; --筛选平均成绩小于等于60分的组5、 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
使用右连接包含所有学生，按学生分组统计选课数和总成绩，用 IFNULL 处理成绩为空的情况
SELECT
b.s_id, -- 学生ID
b.s_name, -- 学生姓名
COUNT(a.c_id) cnt_s, -- 统计选课数
IFNULL(SUM(a.s_score), 0) sum_s -- 计算总成绩，无成绩时默认为0
FROM
score a
RIGHT JOIN student b ON a.s_id = b.s_id -- 右连接确保所有学生都被包含
GROUP BY
b.s_id, b.s_name; -- 按学生分组6、查询"李”姓老师的数量
通过 LIKE '李%' 筛选名字以 “李” 开头的教师，用 COUNT 统计数量
SELECT
COUNT(t_name) cnt_t -- 统计以'李'开头的教师数量
FROM teacher a
WHERE a.t_name LIKE '李%'; -- 筛选名字以'李'开头的教师7、查询学过"张三"老师授课的同学的信息
通过多表连接，从教师表出发，依次关联课程表、成绩表和学生表，筛选出教师名为“张三”所教授课程的学生信息
/*隐式连接*/
SELECT c.* FROM
course a, score b, student c, teacher d
WHERE
d.t_id = a.t_id -- 教师表与课程表关联
AND a.c_id = b.c_id -- 课程表与成绩表关联
AND b.s_id = c.s_id -- 成绩表与学生表关联
AND d.t_name = '张三'; -- 筛选教师名为'张三'的记录
/*显式连接*/
SELECT c.s_id, c.s_name FROM
teacher d
JOIN course a ON d.t_id = a.t_id -- 教师表与课程表关联
JOIN score b ON a.c_id = b.c_id -- 课程表与成绩表关联
JOIN student c ON b.s_id = c.s_id -- 成绩表与学生表关联
WHERE
d.t_name = '张三';8、查询没学过”张三"老师授课的同学的信息
通过子查询找出张三授课的学生ID并排除
/*隐式连接*/
SELECT * FROM student WHERE s_id NOT IN ( -- 排除在子查询结果中的学生ID
SELECT
b.s_id
FROM
course a, score b, teacher d
WHERE
d.t_id = a.t_id -- 教师表与课程表关联
AND a.c_id = b.c_id -- 课程表与成绩表关联
AND d.t_name = '张三' -- 筛选教师名为'张三'的记录
);
/*显式连接*/
SELECT c.* FROM student c
LEFT JOIN (
SELECT b.s_id
FROM
teacher d
JOIN course a ON d.t_id = a.t_id
JOIN score b ON a.c_id = b.c_id
WHERE
d.t_name = '张三'
) t ON c.s_id = t.s_id
WHERE
t.s_id IS NULL;查询不在教师“张三”授课名单中的学生，通过显式 JOIN 关联教师、课程和成绩表，筛选出张三授课的学生ID，并用 NOT EXISTS 排除这些学生
SELECT s.* FROM student s WHERE NOT EXISTS(
SELECT 1 FROM teacher d
JOIN course a ON d.t_id = a.t_id
JOIN score b ON a.c_id = b.c_id
WHERE d.t_name = '张三' AND b.s_id = s.s_id
);9、查询学过编号为"1"并且也学过编号为"2”的课程的同学的信息
（1） 查询同时选修了课程1和课程2的学生信息，通过多表连接筛选出符合条件的学生
/*隐式连接*/
SELECT c.*
FROM
score a, score b, student c
WHERE
a.c_id = '1' -- 筛选课程ID为1的成绩记录
AND b.c_id = '2' -- 筛选课程ID为2的成绩记录
AND a.s_id = b.s_id -- 关联两个成绩表中的学生ID
AND a.s_id = c.s_id; -- 关联学生表中的学生ID
/*显式连接*/
SELECT c.*
FROM
student c
INNER JOIN score a ON c.s_id = a.s_id -- 关联学生表和成绩表（课程1）
INNER JOIN score b ON c.s_id = b.s_id -- 关联学生表和成绩表（课程2）
WHERE
a.c_id = '1' -- 筛选课程ID为1的成绩记录
AND b.c_id = '2'; -- 筛选课程ID为2的成绩记录（2） 通过 CASE WHEN 将成绩按课程ID分类，再用 MAX 聚合函数提取成绩，确保每个学生每门课程的成绩只出现一次，最后用 HAVING 筛选出有成绩的记录
SELECT
c.*,
MAX(CASE WHEN a.c_id = '1' THEN a.s_score END) AS score_course1,
MAX(CASE WHEN a.c_id = '2' THEN a.s_score END) AS score_course2
FROM
student c
LEFT JOIN score a ON c.s_id = a.s_id
GROUP BY
c.s_id
HAVING
score_course1 IS NOT NULL
AND score_course2 IS NOT NULL;10、查询学过编号为"1"但是没有学过编号为"2"的课程的同学的信息
通过子查询将每个学生的课程1和课程2成绩分别提取为两列，再与学生表连接，筛选出有课程1成绩但没有课程2成绩的学生
/*隐式连接*/
SELECT s.*
FROM
(SELECT
s_id,
MAX(CASE WHEN c_id = '1' THEN s_score ELSE NULL END) s1, --提取课程1成绩
MAX(CASE WHEN c_id = '2' THEN s_score ELSE NULL END) s2 --提取课程2成绩
FROM
score a
GROUP BY
a.s_id) t, student s
WHERE
t.s_id = s.s_id --连接子查询和学生表
AND t.s1 IS NOT NULL --筛选有课程1成绩的学生
AND t.s2 IS NULL; --筛选没有课程2成绩的学生
/*显式连接
使用显式JOIN将子查询结果与学生表连接，利用CASE WHEN提取课程成绩并筛选出符合条件的学生
*/
SELECT s.*
FROM student s
JOIN (
SELECT
s_id,
MAX(CASE WHEN c_id = '1' THEN s_score END) s1, --提取课程1成绩
MAX(CASE WHEN c_id = '2' THEN s_score END) s2 --提取课程2成绩
FROM score
GROUP BY s_id
) t ON s.s_id = t.s_id --连接子查询和学生表
WHERE t.s1 IS NOT NULL --筛选有课程1成绩的学生
AND t.s2 IS NULL; --筛选没有课程2成绩的学生11、查询没有学全所有课程的同学的信息
查询选课数量少于总课程数的学生，通过左连接包含所有学生，按学生ID分组统计选课数，并与总课程数比较
SELECT
a.*,
COUNT(b.c_id) cnt --统计每个学生的选课数量
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id --左连接确保所有学生都被包含
GROUP BY
a.s_id --按学生ID分组
HAVING
COUNT(b.c_id) < (SELECT COUNT(c_id) FROM course); --筛选选课数少于总课程数的学生12、查询至少有一门课与学号为"1"的同学所学相同的同学的信息
找出选修了学号为“1”的学生所选课程的学生，通过左连接确保所有学生都被考虑，并用子查询筛选出符合条件的课程，下图是通过左连接后的表格
SELECT
DISTINCT a.* -- 查询学生表中的所有字段并去重
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id -- 左连接确保所有学生都被包含
WHERE
b.c_id IN (SELECT c_id FROM score WHERE s_id = '1'); -- 筛选选修了特定课程的学生不使用distinct去重的话，可以使用group by对字段位置进行分组：
SELECT
DISTINCT a.*
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id
WHERE
b.c_id IN (SELECT c_id FROM score WHERE s_id = '1')
GROUP BY
1, 2, 3, 4; -- 按学生表的字段位置分组还可以使用 EXISTS 子查询判断学生是否选修了特定课程，提高查询效率，避免不必要的连接操作。
SELECT
a.* -- 查询学生表中的所有字段
FROM
student a
WHERE
EXISTS (
SELECT 1
FROM score b
WHERE b.s_id = a.s_id
AND b.c_id IN (SELECT c_id FROM score WHERE s_id = '1')
);13、查询和'1'号同学学习的课程完全相同的其他同学的信息
先创建临时表s01_s_temp，通过左连接和右连接结合UNION，找出所有学生与课程1的关联情况，再查询不在临时表中且非学生1的学生信息。
-- 创建临时表s01_s_temp，存储学生与课程1的关联情况
CREATE TABLE s01_s_temp AS
SELECT
t.*, b.c_id cid2
FROM
(SELECT
a.*, b.c_id
FROM
student a,
(SELECT c_id FROM score WHERE s_id = '1') b) t
LEFT JOIN
score b
ON t.s_id = b.s_id
AND t.c_id = b.c_id
UNION
SELECT
t.*, b.c_id cid2
FROM
(SELECT
a.*, b.c_id
FROM
student a,
(SELECT c_id FROM score WHERE s_id = '1') b) t
RIGHT JOIN
score b
ON t.s_id = b.s_id
AND t.c_id = b.c_id;
-- 查询未选课程1且非学生1的学生信息
select * from student where s_id not in(
select s_id from s01_s_temp where cid2 is null or c_id is null)
and s_id !='01';14、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT
a.s_id, a.s_name, AVG(b.s_score) avg_s
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id
GROUP BY
a.s_id
HAVING
SUM(CASE WHEN b.s_score >= 60 THEN 0 ELSE 1 END) >= 2; -- 筛选不及格科目数大于等于2的学生可以将HAVING条件中的CASE WHEN逻辑提前到WHERE子句中，减少分组后的计算量：
SELECT
a.s_id, a.s_name, AVG(b.s_score) avg_s
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id
WHERE
b.s_score < 60
GROUP BY
a.s_id
HAVING
COUNT(b.s_score) >= 2;15、检索'01'课程分数小于60，按分数降序排列的学生信息
SELECT
a.*, b.s_score
FROM
student a
INNER JOIN score b ON a.s_id = b.s_id --内连接学生表和成绩表
WHERE
b.c_id = '01' -- 筛选课程ID为'01'的成绩
AND b.s_score < 60 -- 筛选分数小于60的成绩
ORDER BY
b.s_score DESC; -- 按分数降序排列可以将LEFT JOIN改为INNER JOIN，因为WHERE条件中已经限制了b.c_id和b.s_score，这意味着只有匹配的记录才会被返回，LEFT JOIN在这里显得多余
SELECT
a.*, b.s_score
FROM
student a
INNER JOIN score b ON a.s_id = b.s_id -- 内连接学生表和成绩表
WHERE
b.c_id = '01' -- 筛选课程ID为'01'的成绩
AND b.s_score < 60 -- 筛选分数小于60的成绩
ORDER BY
b.s_score DESC; -- 按分数降序排列16、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
查询每个学生的成绩记录，并附加每个学生的平均成绩，最后按平均成绩降序排列
SELECT
a1.*, a2.avg_s
FROM
(SELECT * FROM score) a1,
(SELECT a.s_id, ROUND(AVG(s_score), 2) avg_s FROM score a GROUP BY s_id) a2 -- 子查询计算每个学生的平均成绩
WHERE
a1.s_id = a2.s_id -- 关联两个子查询结果
ORDER BY
a2.avg_s DESC; -- 按平均成绩降序排列通过开窗函数计算每个学生的平均成绩，仅选择必要的字段，提高查询效率
SELECT
a.s_id, -- 学生ID
a.c_id, -- 课程ID
a.s_score, -- 成绩
AVG(a.s_score) OVER (PARTITION BY a.s_id) avg_S -- 使用开窗函数按学生ID分组计算平均成绩
FROM
score a;17、查询各科成绩的最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率（及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）
SELECT
a.c_id, a.c_name, --查询课程ID和课程名称
ROUND(MAX(b.s_score), 2) max_s, --最高分
ROUND(MIN(b.s_score), 2) min_s, --最低分
ROUND(AVG(b.s_score), 2) avg_s, --平均分
ROUND(SUM(CASE WHEN b.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(*), 2) jige, --及格率
ROUND(SUM(CASE WHEN b.s_score >= 70 AND b.s_score < 80 THEN 1 ELSE 0 END) / COUNT(*), 2) zhongdeng, --中等率
ROUND(SUM(CASE WHEN b.s_score >= 80 AND b.s_score < 90 THEN 1 ELSE 0 END) / COUNT(*), 2) youliang, --优良率
ROUND(SUM(CASE WHEN b.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(*), 2) youxiu --优秀率
FROM
course a
LEFT JOIN score b ON a.c_id = b.c_id --左连接课程表和成绩表
GROUP BY
a.c_id; -- 按课程ID分组18、按各科成绩进行排序，并显示排名（实现不完全）
18.1、排序开窗函数
- row_number() over()：对相等的值不进行区分，其实就是行号，相等的值对应的排名不同，序号从1到n连续
- rank() over()：相等的值排名相同，若有相等的值，则序号从1到n不连续。如果两个都排第三，则没有第四名
- dens_rank() over()：对相等的值排名相同，但序号从1到n连续。如果有两个人排在第1，则后续是第3名
MySQL8.0以上：查询成绩表的所有记录，并通过开窗函数RANK()对每个课程的成绩进行降序排名
SELECT
a.*,
RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) rk -- 使用开窗函数按课程ID分组并按分数降序排名
FROM score a;
MySQL5.7的话：使用子查询
SELECT
a.*,
(SELECT COUNT(s_score) FROM score b WHERE a.c_id = b.c_id AND a.s_score < b.s_score) + 1 rk
FROM
score a
ORDER BY
c_id, s_score DESC;19、查询学生的总成绩并进行排名
查询每个学生的总分，并通过开窗函数RANK()按总分降序计算排名。
SELECT
t.*,
RANK() OVER (ORDER BY sum_s DESC) rk -- 使用开窗函数按总分降序计算排名
FROM
(SELECT
s_id, SUM(s_score) sum_s -- 分组计算每个学生的总分
FROM score
GROUP BY s_id) t;20、查询不同老师所教不同课程平均分从高到低显示
查询每个教师所教课程的平均成绩，通过两次左连接关联教师、课程和成绩表，按教师姓名和课程名称分组计算平均成绩（ 同时，如果不需要显示NULL值的记录，可以将LEFT JOIN改为INNER JOIN）
SELECT
a.t_name,
b.c_name,
ROUND(AVG(c.s_score),2) avg_s
FROM
teacher a
LEFT JOIN course b ON a.t_id = b.t_id
LEFT JOIN score c ON b.c_id = c.c_id
GROUP BY
a.t_name, b.c_name;21、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
查询每个学生的成绩，并通过开窗函数RANK()按成绩降序计算排名，筛选出排名为2或3的学生
SELECT
a.*, t.c_id, t.s_score
FROM
student a
LEFT JOIN (
SELECT
a.*,
RANK() OVER (ORDER BY s_score DESC) rk -- 按成绩降序计算排名
FROM
score a
) t ON a.s_id = t.s_id
WHERE
rk IN (2, 3); -- 筛选出排名为2或3的学生22、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT
a.c_id, a.c_name,
SUM(CASE WHEN s_score > 85 AND s_score <= 100 THEN 1 ELSE 0 END) "[100-85]",
SUM(CASE WHEN s_score > 70 AND s_score <= 85 THEN 1 ELSE 0 END) "[85-70]",
SUM(CASE WHEN s_score > 60 AND s_score <= 70 THEN 1 ELSE 0 END) "[70-60]",
SUM(CASE WHEN s_score > 0 AND s_score <= 60 THEN 1 ELSE 0 END) "[0-60]",
SUM(CASE WHEN s_score > 85 AND s_score <= 100 THEN 1 ELSE 0 END) / COUNT(*) "[100-85]%",
SUM(CASE WHEN s_score > 70 AND s_score <= 85 THEN 1 ELSE 0 END) / COUNT(*) "[85-70]%",
SUM(CASE WHEN s_score > 60 AND s_score <= 70 THEN 1 ELSE 0 END) / COUNT(*) "[70-60]%",
SUM(CASE WHEN s_score > 0 AND s_score <= 60 THEN 1 ELSE 0 END) / COUNT(*) "[0-60]%"
FROM
course a
LEFT JOIN score b ON a.c_id = b.c_id
GROUP BY
a.c_id, a.c_name;23、查询学生平均成绩及其名次
先通过子查询计算每个学生的平均成绩，再使用开窗函数RANK()按平均成绩降序计算排名
SELECT
t.*,
RANK() OVER (ORDER BY avg_s DESC) rk -- 为每个学生的平均成绩计算降序排名
FROM
(SELECT
s_id, -- 学生ID
AVG(s_score) avg_s -- 计算每个学生的平均成绩
FROM
score
GROUP BY s_id) t; -- 对每个学生分组直接在主查询中使用开窗函数RANK()按平均成绩降序计算排名，避免嵌套子查询，提高查询效率
SELECT
s_id, -- 学生ID
AVG(s_score) AS avg_s, -- 计算每个学生的平均成绩
RANK() OVER (ORDER BY AVG(s_score) DESC) rk -- 按平均成绩降序计算排名
FROM
score
GROUP BY
s_id; -- 对每个学生分组24、查询各科成绩前三名的记录
查询每个课程中成绩排名前3的学生信息，通过开窗函数RANK()按课程分组并按成绩降序计算排名，筛选出排名为1、2或3的记录
SELECT
t.*
FROM
(SELECT
a.c_id, a.c_name, b.s_score, -- 查询课程ID、课程名称和成绩
RANK() OVER (PARTITION BY a.c_id ORDER BY b.s_score DESC) rk -- 按课程分组并按成绩降序计算排名
FROM
course a
LEFT JOIN score b ON a.c_id = b.c_id -- 左连接课程表和成绩表
ORDER BY
a.c_id, b.s_score DESC) t -- 按课程ID和成绩降序排列
WHERE
rk IN (1, 2, 3); -- 筛选出排名为1、2或3的记录25、查询每门课程被选修的学生数
查询每门课程的选课学生数量，通过左连接课程表和成绩表，按课程ID分组统计选课学生数量
SELECT
a.c_id, a.c_name, COUNT(b.s_id) cnt -- 查询课程ID、课程名称和选课学生数量
FROM
course a
LEFT JOIN score b ON a.c_id = b.c_id -- 左连接课程表和成绩表
GROUP BY
a.c_id; -- 按课程ID分组26、查询出只有两门课程的全部学生的学号和姓名
通过左连接学生表和成绩表，按学生ID分组统计选课数量，筛选出选课数量为2的学生
select
a.s_id,a.s_name
from
student a
left join score b on a.s_id=b.s_id
group by a.s_id
having
count(c_id)=227、查询男生、女生人数
查询学生表中每种性别的学生数量，通过按性别分组并计数实现
SELECT
s_sex, COUNT(s_id) cnt
FROM
student
GROUP BY
s_sex;28、查询名字中含有“风”字的学生信息
使用LIKE通配符实现模糊匹配
SELECT * FROM student WHERE s_name LIKE '%风%';29、查询同名同性学生名单，并统计同名人数
查询学生表中每个姓名和性别的组合出现的次数，并筛选出出现次数大于1的组合
SELECT
s_name, s_sex, COUNT(s_name) cnt
FROM
student
GROUP BY
s_name, s_sex
HAVING
COUNT(s_name) > 1; -- 筛选出同名同性别的学生数量大于1的组合30、查询1990年出生的学生名单
SELECT * FROM student WHERE YEAR(s_birth) = 1990;31、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT
c_id, AVG(s_score) avg_s
FROM
score
GROUP BY c_id
ORDER BY avg_s DESC, c_id;32、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
查询每个学生的平均成绩，使用LEFT JOIN确保所有学生都被包含，通过IFNULL处理没有成绩的学生，默认平均成绩为0，最后筛选出平均成绩大于等于85分的学生
SELECT
a.s_id, a.s_name, IFNULL(AVG(b.s_score), 0) avg_s
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id
GROUP BY
a.s_id
HAVING
IFNULL(AVG(b.s_score), 0) >= 85;33、查询课程名称为“数学”，且分数低于60的学生姓名和分数
SELECT
a.s_name, b.s_score
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id
LEFT JOIN course c ON c.c_id = b.c_id
WHERE
c.c_name = '数学'
AND b.s_score < 60;34、查询所有学生的课程及分数情况
通过两次左连接关联学生、成绩和课程表
SELECT
a.s_name, c.c_name, b.s_score
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id
LEFT JOIN course c ON c.c_id = b.c_id;35、查询每一门课程成绩都在70分以上的姓名、课程名称和分数
通过子查询筛选出符合条件的学生ID，再与学生表和课程表连接获取详细信息
SELECT
a.s_name, c.c_name, b.s_score -- 查询学生姓名、课程名称和成绩
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id -- 左连接学生表和成绩表
LEFT JOIN course c ON c.c_id = b.c_id -- 左连接成绩表和课程表
WHERE
a.s_id IN (
SELECT s_id FROM score GROUP BY s_id HAVING MIN(s_score) > 70 -- 筛选出所有最低分大于70分的学生ID
);36、查询不及格的课程
SELECT
b.s_id, a.c_name
FROM
course a
LEFT JOIN score b ON a.c_id = b.c_id
WHERE
b.s_score < 60;37、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT
a.s_id,a.s_name
FROM
student a
LEFT JOIN score b ON a.s_id=b.s_id
WHERE b.c_id='01'
AND s_score>=8038、求每门课程的学生人数
SELECT
a.c_name,count(b.s_id) cnt
FROM
course a
LEFT JOIN score b ON a.c_id=b.c_id
GROUP BY a.c_id;39、查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
通过多次左连接关联学生、成绩、课程和教师表，按成绩降序排列并限制结果为1条
SELECT
a.*, c.c_name, b.s_score
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id -- 左连接学生表和成绩表
LEFT JOIN course c ON c.c_id = b.c_id -- 左连接课程表
LEFT JOIN teacher d ON d.t_id = c.t_id -- 左连接教师表
WHERE
d.t_name = '张三' -- 筛选教师名为“张三”的记录
ORDER BY
b.s_score DESC -- 按成绩降序排列
LIMIT 1; -- 限制结果为1条，即最高分的学生40、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
通过自连接成绩表，筛选出不同课程但分数相同的记录，并使用DISTINCT去重
SELECT
DISTINCT a.*
FROM
score a, score b
WHERE
a.c_id != b.c_id
AND a.s_score = b.s_score;显式JOIN语法替代逗号分隔的隐式连接，提高可读性和查询效率
SELECT
DISTINCT a.*
FROM
score a
INNER JOIN score b ON a.c_id != b.c_id AND a.s_score = b.s_score; --自连接成绩表41、查询每门功成绩最好的前两名
通过开窗函数RANK()按课程分组并按成绩降序计算排名，最后筛选出排名在前2名的记录
SELECT * FROM (
SELECT
a.c_id, a.s_score, -- 查询课程ID和成绩
RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) rk --按课程分组并按成绩降序计算排名
FROM
score a
) t
WHERE
rk <= 2; -- 筛选出排名在前2名的记录通过子查询计算每个成绩在课程内的相对排名，筛选出排名在前2名的记录
SELECT
a.c_id, a.s_score
FROM
score a
WHERE
(SELECT COUNT(s_score) FROM score b WHERE a.c_id = b.c_id AND a.s_score < b.s_score) + 1 <= 2; -- 筛选出每个课程中成绩排名前2的学生
order by c_id,s_score desc42、统计每门课程的学生的选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
通过左连接课程表和成绩表，按课程ID分组统计选课学生数量，筛选出符合条件的课程，并按选课学生数量降序排列，若数量相同则按课程ID升序排列
SELECT
a.c_id, COUNT(b.s_id) cnt -- 查询课程ID和选课学生数量
FROM
course a
LEFT JOIN score b ON a.c_id = b.c_id -- 左连接课程表和成绩表
GROUP BY
a.c_id -- 按课程ID分组
HAVING
COUNT(b.s_id) >= 5 -- 筛选出选课学生数量大于等于5的课程
ORDER BY
cnt DESC, a.c_id ASC; -- 按选课学生数量降序排列，若数量相同则按课程ID升序排列43、检索至少选修两门课程的学生学号
按学生ID分组统计选课数量，筛选出符合条件的学生
SELECT
s_id
FROM
score
GROUP BY s_id
HAVING COUNT(c_id) >= 2; --筛选出选课数量大于等于2的学生44、查询选修了全部课程的学生信息
SELECT
A.*
FROM
student A,
(SELECT
s_id
FROM
score
GROUP BY s_id
HAVING COUNT(c_id) = (SELECT COUNT(*) FROM course)) B -- 子查询找出选了所有课程的学生
WHERE
A.s_id = B.s_id;45、查询各学生的年龄
SELECT a.*, YEAR(NOW()) - YEAR(s_birth) AS age FROM student a;46、查询本周过生日的学生
通过str_to_date和date_format函数将出生日期转换为当前年份的日期，再使用weekofyear函数判断是否在当前周
SELECT a.* FROM student a
WHERE
weekofyear(str_to_date(concat(year(now()),date_format(s_birth,'%m%d')),'%Y%m%d'))
=weekofyear(now());47、查询下周过生日的学生
SELECT a.* FROM student a
WHERE
weekofyear(str_to_date(concat(year(now()),date_format(s_birth,'%m%d')),'%Y%m%d'))
=weekofyear(now()+interval '7' day);48、查询本月过生日的学生
SELECT a.* FROM student a
WHERE
month(now())=month(s_birth);49、查询下月过生日的学生
SELECT a.* FROM student a
WHERE
month(now()+interval '1' month)=month(s_birth);三、刷题网站
牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网
https://www.nowcoder.com/
LintCode 炼码 - 更高效的学习体验！https://www.lintcode.com/
力扣 (LeetCode) 全球极客挚爱的技术成长平台https://leetcode.cn/
学习永无止境，让我们共同进步！！