学生成绩管理系统是数据库学习中的经典实战场景，合理的表结构设计配合高效的多表查询，能够满足成绩录入、统计、查询等各类业务需求。系统核心需要存储学生信息、课程信息以及学生对应的课程成绩，通过表之间的关联实现数据的统一管理。
一、数据库表结构设计
首先需要根据业务需求拆分核心实体，学生成绩管理系统主要包含三个核心实体：学生、课程、成绩，对应设计三张表，表之间通过外键建立关联。
1. 学生表 student
存储学生的基础信息，主键为学生ID，避免存储冗余信息。
-- 创建学生表
CREATE TABLE student (
student_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
student_name VARCHAR(50) NOT NULL COMMENT '学生姓名',
gender ENUM('男','女') NOT NULL COMMENT '性别',
age INT COMMENT '年龄',
class_name VARCHAR(50) NOT NULL COMMENT '班级名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';
2. 课程表 course
存储课程的基础信息，主键为课程ID，每门课程有独立的编号和名称。
-- 创建课程表
CREATE TABLE course (
course_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程ID',
course_code VARCHAR(20) NOT NULL UNIQUE COMMENT '课程编号',
course_name VARCHAR(100) NOT NULL COMMENT '课程名称',
credit INT NOT NULL COMMENT '学分'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程信息表';
3. 成绩表 score
存储学生对应课程的成绩，通过学生ID和课程ID关联另外两张表，一个学生一门课程只有一条成绩记录。
-- 创建成绩表
CREATE TABLE score (
score_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '成绩ID',
student_id INT NOT NULL COMMENT '学生ID',
course_id INT NOT NULL COMMENT '课程ID',
score DECIMAL(5,2) NOT NULL COMMENT '成绩',
exam_date DATE NOT NULL COMMENT '考试日期',
-- 建立外键关联
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (course_id) REFERENCES course(course_id),
-- 保证一个学生一门课程只有一条成绩记录
UNIQUE KEY uk_student_course (student_id, course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生成绩表';
二、多表查询实战场景
完成表结构设计后，结合实际业务需求演示多表查询的使用方法，以下是常见的查询场景。
1. 查询所有学生的各科成绩及对应课程名称
需要关联学生表、课程表、成绩表三张表，获取学生姓名、课程名称、成绩信息。
-- 三表连接查询学生成绩详情
SELECT
s.student_name AS 学生姓名,
c.course_name AS 课程名称,
sc.score AS 成绩,
sc.exam_date AS 考试日期
FROM score sc
JOIN student s ON sc.student_id = s.student_id
JOIN course c ON sc.course_id = c.course_id
ORDER BY s.student_name, c.course_name;
2. 查询某门课程的平均分、最高分、最低分
先通过课程名称筛选课程ID，再关联成绩表做聚合计算。
-- 统计MySQL课程的成绩情况
SELECT
c.course_name AS 课程名称,
AVG(sc.score) AS 平均分,
MAX(sc.score) AS 最高分,
MIN(sc.score) AS 最低分,
COUNT(sc.score) AS 参考人数
FROM course c
JOIN score sc ON c.course_id = sc.course_id
WHERE c.course_name = 'MySQL数据库'
GROUP BY c.course_id, c.course_name;
3. 查询没有参加考试的学生名单
使用左连接查询学生表中在成绩表没有对应记录的学生，即缺考学生。
-- 查询缺考学生
SELECT
s.student_id AS 学生ID,
s.student_name AS 学生姓名,
s.class_name AS 班级
FROM student s
LEFT JOIN score sc ON s.student_id = sc.student_id
WHERE sc.score_id IS NULL;
4. 查询每门课程成绩排名前3的学生
使用窗口函数按课程分组排序，筛选排名前3的记录，需要mysql 8.0及以上版本支持。
-- 查询每门课程成绩前3的学生
SELECT
课程名称,
学生姓名,
成绩,
课程内排名
FROM (
SELECT
c.course_name AS 课程名称,
s.student_name AS 学生姓名,
sc.score AS 成绩,
ROW_NUMBER() OVER (PARTITION BY sc.course_id ORDER BY sc.score DESC) AS 课程内排名
FROM score sc
JOIN student s ON sc.student_id = s.student_id
JOIN course c ON sc.course_id = c.course_id
) t
WHERE 课程内排名 <= 3;
三、设计注意事项
- 外键关联可以保证数据的一致性，但是如果数据量较大，也可以根据实际需求选择不建立物理外键，通过业务逻辑保证关联正确性。
- 成绩字段使用
DECIMAL(5,2)类型，可以满足0到999.99分的成绩存储需求，避免浮点类型计算精度问题。 - 多表查询时尽量指定需要查询的字段，避免使用
SELECT *，减少不必要的IO开销，提升查询效率。 - 如果查询频繁，可以给关联字段如
student_id、course_id建立索引，加快连接查询的速度。