SQL初学、精通者必看：10个学生成绩查询史上最强技巧全攻略

SQL初学、精通者必看：10个学生成绩查询史上最强技巧全攻略

本文提供了一个含有学生、成绩、课程和教师信息的完整数据库，并为读者提供了 SQL 查询练习题，还包含了练习的答案以及解析。这些题目旨在帮助有一定SQL使用经验的读者更深入地了解学生成绩相关的知识，也有助于初学者提高其SQL使用技能。这些题目涉及学生成绩排名、平均成绩、选修课程及格率、选课信息等多个方面，且不同的题目有着不同的难度和解题思路。如果您是SQL查询方面的新手，这些题目可能会极具挑战性。但是，如果您已经具备一定的SQL使用经验，这些题目将是一个很好的检验和提高自己技能的途径。

一、完整数据库信息

– 创建课程表，用于存储课程信息

CREATE TABLE `course` (
`c_id` int(8) NOT NULL COMMENT '课程ID',
`c_name` varchar(16) DEFAULT NULL COMMENT '课程名称',
`t_id` int(8) DEFAULT NULL COMMENT '教师ID',
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';

– 插入课程信息

INSERT INTO `course`(`c_id`,`c_name`,`t_id`) VALUES(1,'语文',2),(2,'数学',1),(3,'英语',3);

– 创建成绩表，用于存储学生课程成绩

CREATE TABLE `score` (
`s_id` int(8) DEFAULT NULL COMMENT '学生ID',
`c_id` int(8) DEFAULT NULL COMMENT '课程ID',
`s_score` int(8) DEFAULT NULL COMMENT '成绩',
INDEX `idx_score_cid`(`c_id`) COMMENT '基于课程ID的索引'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='成绩表';

– 插入学生课程成绩信息

INSERT INTO `score`(`s_id`,`c_id`,`s_score`) VALUES
(1,1,80),(1,2,90),(1,3,99),(2,1,70),(2,2,60),(2,3,80),
(3,1,80),(3,2,80),(3,3,80),(4,1,50),(4,2,30),(4,3,20),
(5,1,76),(5,3,87),(6,1,31),(6,3,34),(7,3,89),(7,1,98);

– 创建学生表，用于存储学生信息

CREATE TABLE `student` (
`s_id` int(8) NOT NULL AUTO_INCREMENT COMMENT '学生ID',
`s_name` varchar(16) DEFAULT NULL COMMENT '学生姓名',
`s_birth` date DEFAULT NULL COMMENT '学生出生日期',
`s_sex` char(1) DEFAULT NULL COMMENT '学生性别',
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='学生表';

– 插入学生信息

INSERT INTO `student`(`s_id`,`s_name`,`s_birth`,`s_sex`) VALUES
(1,'赵磊','2021-07-30','男'),(2,'钱电','2021-07-30','男'),
(3,'孙峰','2021-07-29','男'),(4,'李白','2021-07-14','女'),
(5,'二狗','2021-07-21','男'),(6,'吴兰','2021-07-07','女'),
(7,'李顺','2021-07-13','女'),(8,'舟券','2021-09-01','男'),
(9,'无邪','2021-07-07','男');

– 创建教师表，用于存储教师信息

CREATE TABLE `teacher` (
`t_id` int(8) NOT NULL AUTO_INCREMENT COMMENT '教师ID',
`t_name` varchar(16) DEFAULT NULL COMMENT '教师姓名',
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='教师表';

– 插入教师信息

INSERT INTO `teacher`(`t_id`,`t_name`) VALUES(1,'张三'),(2,'李四');

二、10个学生成绩查询题

问题1 - 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT 
    s.s_id AS '学生ID',
    s.s_name AS '学生姓名',
    sc.s_score AS '01课程分数', 
    sc2.s_score AS '02课程分数'
FROM 
    student s 
    INNER JOIN score sc ON s.s_id = sc.s_id AND sc.c_id = '01'
    INNER JOIN score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02'
WHERE 
    sc.s_score > sc2.s_score;

这是一个比较简单的SQL查询语句，使用INNER JOIN查询两个不同的课程成绩，然后使用WHERE子句将比较结果进行筛选返回满足条件的学生信息。

问题2 - 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（包括有成绩的和无成绩的）

SELECT 
    s.s_id AS '学生编号',
    s.s_name AS '学生姓名',
    COALESCE(ROUND(AVG(sc.s_score), 2), 0) AS '平均成绩'
FROM 
    student s 
    LEFT JOIN score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
HAVING 
    AVG(COALESCE(sc.s_score, 0)) < 60;

这个查询通过GROUP BY 和HAVING 条件子句来计算学生的平均成绩，并筛选出平均成绩小于60的学生信息。这个查询也考虑了学生没有成绩的情况，并将它们的平均成绩赋值为 0。

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

SELECT 
    s.s_id AS '学生编号',
    s.s_name AS '学生姓名',
    COUNT(sc.c_id) AS '选课总数',
    SUM(COALESCE(sc.s_score, 0)) AS '总成绩'
FROM 
    student s 
    LEFT JOIN score sc ON s.s_id = sc.s_id
    LEFT JOIN course c ON sc.c_id = c.c_id
GROUP BY 
    s.s_id, s.s_name;

该查询使用左连接将学生表与成绩表和课程表连接起来，保证返回所有的学生信息。然后使用COUNT和SUM函数分别计算每个学生选修的课程总数和所有课程成绩的总和。

问题4 - 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT 
    s.s_id AS '学生编号',
    s.s_name AS '学生姓名'
FROM 
    student s 
    INNER JOIN score sc ON s.s_id = sc.s_id AND sc.c_id = '01'
    INNER JOIN score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02';

这个查询通过使用INNER JOIN将两个成绩表进行比较。仅返回学过编号为 ‘01’ 和编号为 ‘02’ 两门课程的学生信息。

问题5 - 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT 
    s.s_id AS '学生编号',
    s.s_name AS '学生姓名'
FROM 
    student s 
    INNER JOIN score sc ON s.s_id = sc.s_id AND sc.c_id = '01'
    LEFT JOIN score sc2 ON s.s_id = sc2.s_id AND sc2.c_id = '02'
WHERE 
    sc2.s_score IS NULL;

这个查询使用LEFT JOIN结合条件筛选出学过编号为 ‘01’ 但是没有学过编号为 ‘02’ 的学生信息。

问题6 - 查询没有学全所有课程的同学的信息

SELECT 
    s.s_id AS '学生编号',
    s.s_name AS '学生姓名'
FROM 
    student s 
    INNER JOIN score sc ON s.s_id = sc.s_id
GROUP BY 
    s.s_id, s.s_name
HAVING 
    COUNT(DISTINCT sc.c_id) < (SELECT COUNT(*) FROM course);

该查询根据每个学生参加的课程数量进行分组。然后计算总课程数，并通过HAVING筛选出未学完所有课程的学生信息。

问题7 - 查询两门及其以上不及格课程的同学的学号，姓名及其不及格课程数

SELECT 
    s.s_id AS '学生编号',
    s.s_name AS '学生姓名',
    COALESCE(ROUND(AVG(sc.s_score), 2), 0) AS '平均成绩'
FROM 
    student s 
    INNER JOIN score sc ON s.s_id = sc.s_id AND sc.s_score < 60
GROUP BY 
    s.s_id, s.s_name
HAVING 
    COUNT(DISTINCT sc.c_id) >= 2;

这个查询使用INNER JOIN 和GROUP BY来聚合学生成绩，并使用HAVING筛选出两门或以上超过不及格的课程的学生信息。

问题8 - 查询各科成绩最高分、最低分和平均分，以及及格率、中等率、优良率和优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT
    c.c_id AS '课程ID',
    c.c_name AS '课程名称',
    MAX(s.s_score) AS '最高分',
    MIN(s.s_score) AS '最低分',
    ROUND(AVG(s.s_score), 2) AS '平均分',
    ROUND(100 * SUM(CASE WHEN s.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(s.s_score), 2) AS '及格率',
    ROUND(100 * SUM(CASE WHEN s.s_score >= 70 AND s.s_score < 80 THEN 1 ELSE 0 END) / COUNT(s.s_score), 2) AS '中等率',
    ROUND(100 * SUM(CASE WHEN s.s_score >= 80 AND s.s_score < 90 THEN 1 ELSE 0 END) / COUNT(s.s_score), 2) AS '优良率',
    ROUND(100 * SUM(CASE WHEN s.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(s.s_score), 2) AS '优秀率'
FROM
    course c
    INNER JOIN score s ON c.c_id = s.c_id
GROUP BY
    c.c_id, c.c_name;

这个查询分别计算了每个课程的最高分、最低分、平均分以及及格、中等、优良和优秀的百分比。这个查询演示了如何使用聚合函数和CASE语句来计算和分组数据。

问题9 - 按照平均成绩从高到低显示所有学生的信息和排名

-- 查询每位学生的平均成绩和排名，并按照平均成绩进行排序

SELECT 
    s.s_id AS '学生编号',                         -- 选择学生表中的学生编号列，并将该列列别名定义为"学生编号"
    s.s_name AS '学生姓名',                       -- 选择学生表中的学生姓名列，并将该列列别名定义为"学生姓名"
    AVG(sc.s_score) AS '平均成绩',                  -- 计算每位学生的平均成绩，并将该列列别名定义为"平均成绩"
    (
        SELECT COUNT(*)+1                       -- 使用子查询计算排名
        FROM (
            SELECT s2.s_id, AVG(sc2.s_score) AS avg_score 
            FROM student s2 JOIN score sc2 ON s2.s_id=sc2.s_id 
            GROUP BY s2.s_id
        ) AS t2
        WHERE t2.avg_score > AVG(sc.s_score) AND t2.s_id <> s.s_id     -- 当前学生的平均分数比其他学生的平均分数高时，排名+1
    ) AS '排名'                                      -- 将该列列别名定义为"排名"
FROM 
    student s 
    JOIN score sc ON s.s_id=sc.s_id
GROUP BY 
    s.s_id, s.s_name                           -- 按学生编号和学生姓名进行分组
ORDER BY 
    AVG(sc.s_score) DESC;                       -- 按平均成绩进行降序排列

该查询使用了子查询以计算每个学生的排名，并使用AS语法在查询中为列指定了别名。在主查询的SELECT语句中，s.s_id, s.s_name和AVG(sc.s_score)分别是学生编号、学生姓名和平均成绩，而(SELECT COUNT()+1…)是计算出的排名。在使用子查询计算排名时，首先使用JOIN将学生表和成绩表连接，然后按学生编号进行分组，并计算每个学生的平均成绩。WHERE子句中的条件或排除了当前学生，以及那些平均分数高于当前学生但排名上比当前学生靠前的其他学生。最后，COUNT() + 1计算出当前学生的排名。在主查询中，按学生编号和学生姓名进行分组，然后计算每位学生的平均成绩和排名。在ORDER BY子句中按平均成绩进行降序排列。

MySQL 8 以后的版本，可以使用窗口函数来完成分组后对某个字段的排序和排名。使用窗口函数可以减少代码量、简化 SQL 查询语句。以下是使用窗口函数的 SQL 查询语句：

SELECT 
    s.s_id AS '学生编号',                 -- 选择学生表中的学生编号列，并将该列列别名定义为"学生编号"
    s.s_name AS '学生姓名',               -- 选择学生表中的学生姓名列，并将该列列别名定义为"学生姓名"
    AVG(sc.s_score) AS '平均分数',        -- 计算每位学生的平均成绩，并将该列列别名定义为"平均分数"
    RANK() OVER(ORDER BY AVG(sc.s_score) DESC) AS '排名'    -- 使用RANK函数通过平均成绩进行排名，并将该列列别名定义为"排名"
FROM 
    student s 
    JOIN score sc ON s.s_id = sc.s_id     -- 使用学生表和成绩表进行连接
GROUP BY 
    s.s_id, s.s_name                -- 按学生编号和学生姓名进行分组
ORDER BY 
    '平均分数' DESC                  -- 按平均成绩进行降序排列

上述查询语句使用了 RANK() 窗口函数来计算排名。窗口函数 OVER() 定义了排序规则，我们将其设置为按照平均成绩从高到低排序。这样，RANK() 函数就会自动计算出每个学生的排名。

在 GROUP BY 语句中，我们分组查询每个学生的平均成绩，并将其命名为 avg_score。在最后的 ORDER BY 子句中，我们按照平均成绩从高到低进行排序。

问题10 - 查询任何一门课程成绩在70分以上的学生的姓名、课程名称和分数

SELECT
    s.s_name AS '学生姓名',
    c.c_name AS '课程名称',
    sc.s_score AS '分数'
FROM
    student s
    INNER JOIN score sc ON s.s_id = sc.s_id
    INNER JOIN course c ON sc.c_id = c.c_id
WHERE
    sc.s_score >= 70
LIMIT 1;

这个查询返回任何一名学生的分数超过70分的课程信息。如果多个学生分数超过70分，则只选择一个学生的信息返回，并使用LIMIT 1 来限制SELECT 子句返回的行数。

从以上的SQL查询语句中，可以发现SQL语言的强大之处。通过巧妙的组合和运用各种SQL操作和函数，我们可以轻松地求出各种统计数据，快捷地查询所需信息。在实际开发中，我们应尽可能地熟练掌握SQL语言，善于发掘SQL语言的特性和优势，提高自己的开发效率和运维水平。

总之，通过以上的问题和SQL查询语句的分析介绍，相信大家能更深入地理解SQL的运用及其丰富的操作手法和函数特性。对于SQL的学习及其技巧的掌握，更需要大家在实践中不断实践，享受其优越性和乐趣，深入挖掘其对数据整理、查询和分析的巨大价值。

在学习过程中，我们应该尊重知识的来源，用积极的态度去学习和应用知识。如果您喜欢作者的内容，可以通过关注、点赞、分享等方式支持一下，这对于作者将是一份很好的鼓励。