SQL查询技巧：学生成绩与课程分析

        在教育管理系统中，对学生成绩和课程的分析是至关重要的。本文通过一系列SQL查询示例，展示如何使用SQL查询来分析学生成绩、课程信息以及它们之间的关系。

一、数据库表的创建

数据库结构：

我们有四个主要表：

1. Student - 存储学生信息，包括学生编号、姓名、出生日期和性别。
2. Course - 存储课程信息，包括课程编号、课程名称和教师编号。
3. Teacher - 存储教师信息，包括教师编号和姓名。
4. Score - 存储学生成绩，包括学生编号、课程编号和分数。

创建数据表

在进行任何查询之前，首先需要创建适当的数据库表。以下是四个基本表的创建语句：

1. 学生表（Student）

   CREATE TABLE Student(
     s_id VARCHAR(20),
     s_name VARCHAR(20) NOT NULL DEFAULT '',
     s_birth VARCHAR(20) NOT NULL DEFAULT '',
     s_sex VARCHAR(10) NOT NULL DEFAULT '',
     PRIMARY KEY(s_id)
   ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '学生表';

2. 课程表（Course）

   CREATE TABLE Course(
     c_id VARCHAR(20),
     c_name VARCHAR(20) NOT NULL DEFAULT '',
     t_id VARCHAR(20) NOT NULL,
     PRIMARY KEY(c_id)
   ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '课程表';

3. 教师表（Teacher）

   CREATE TABLE Teacher(
     t_id VARCHAR(20),
     t_name VARCHAR(20) NOT NULL DEFAULT '',
     PRIMARY KEY(t_id)
   ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '教师表';

4. 成绩表（Score）

   CREATE TABLE Score(
     s_id VARCHAR(20),
     c_id VARCHAR(20),
     s_score INT(3),
     PRIMARY KEY(s_id,c_id)
   ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '成绩表';

二、数据插入

在表创建完成后，我们需要插入一些示例数据以供查询使用。以下是插入数据的 SQL 语句：

学生表数据

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' , '女');

 课程表数据

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

 教师表数据

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

成绩表数据

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');

三、SQL查询

查询示例一

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

SELECT stu.*,
       MAX(CASE WHEN sco.c_id = '01' THEN sco.s_score ELSE 0 END) AS '课程01的成绩',
       MAX(CASE WHEN sco.c_id = '02' THEN sco.s_score ELSE 0 END) AS '课程02的成绩'
FROM Score sco
JOIN Student stu ON sco.s_id = stu.s_id
GROUP BY stu.s_id
HAVING MAX(CASE WHEN sco.c_id = '01' THEN sco.s_score ELSE 0 END) > MAX(CASE WHEN sco.c_id = '02' THEN sco.s_score ELSE 0 END);

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

SELECT stu.*, AVG(IFNULL(sco.s_score, 0)) AS 平均成绩
FROM Student stu
LEFT JOIN Score sco ON sco.s_id = stu.s_id
GROUP BY stu.s_id
HAVING AVG(IFNULL(sco.s_score, 0)) < 60;

3. 查询没学过"张三"老师授课的同学的信息

SELECT stu.*
FROM Student stu
WHERE NOT EXISTS (
    SELECT 1
    FROM Score sco
    JOIN Course cou ON sco.c_id = cou.c_id
    WHERE stu.s_id = sco.s_id
    AND cou.c_id IN (
        SELECT c_id
        FROM Course
        WHERE t_id = (
            SELECT t_id
            FROM Teacher
            WHERE t_name = '张三'
        )
    )
);

4. 查询没有学全所有课程的同学的信息

SELECT stu.*
FROM Student stu
LEFT JOIN Score sco ON stu.s_id = sco.s_id
GROUP BY stu.s_id
HAVING COUNT(DISTINCT sco.c_id) < (SELECT COUNT(*) FROM Course);

5. 查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT stu.*
FROM Student stu
WHERE stu.s_id != '01'
AND NOT EXISTS (
    SELECT 1
    FROM Score sco1
    WHERE sco1.s_id = '01'
    AND NOT EXISTS (
        SELECT 1
        FROM Score sco2
        WHERE sco2.s_id = stu.s_id AND sco2.c_id = sco1.c_id
    )
);

6. 查询各科成绩最高分、最低分和平均分

以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率，及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT cou.c_id, cou.c_name,
       MAX(sco.s_score) AS 最高分,
       MIN(sco.s_score) AS 最低分,
       AVG(sco.s_score) AS 平均分,
       SUM(CASE WHEN sco.s_score >= 60 THEN 1 ELSE 0 END) / COUNT(sco.s_score) AS 及格率,
       SUM(CASE WHEN sco.s_score >= 70 AND sco.s_score < 80 THEN 1 ELSE 0 END) / COUNT(sco.s_score) AS 中等率,
       SUM(CASE WHEN sco.s_score >= 80 AND sco.s_score < 90 THEN 1 ELSE 0 END) / COUNT(sco.s_score) AS 优良率,
       SUM(CASE WHEN sco.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(sco.s_score) AS 优秀率
FROM Course cou
JOIN Score sco ON cou.c_id = sco.c_id
GROUP BY cou.c_id;

四、总结

        通过本文的示例，我们可以看到 SQL 在数据管理和分析中的强大功能。从基础的表创建到复杂的查询操作，这些SQL示例展示了如何使用高级SQL技巧来分析和查询学生成绩、课程信息以及它们之间的关系。希望本文能够帮助你更好地理解和应用 SQL，提高你的数据处理能力。