学生成绩管理系统的 SQL 表设计与多表查询实战-EW帮帮网

在数据库学习中，设计合理的表结构并掌握多表查询技巧是核心能力。本文将以一个学生成绩管理系统为例，详细讲解如何从创建表结构、插入测试数据到实现复杂的多表关联查询，帮助你快速掌握 SQL 实战技能。

一、需求分析：为什么需要这些表？

一个基础的学生成绩管理系统需要存储四类核心数据：

学生信息（学号、姓名、生日、性别等）
课程信息（课程号、课程名、授课教师）
教师信息（教师号、姓名）
成绩信息（学生 - 课程的分数关联）

这四类数据之间存在明确的关联关系：

学生通过 “成绩” 关联课程
课程通过 “教师号” 关联教师
成绩表是学生和课程的中间表（多对多关系）

基于此，我们需要设计 4 张表来分别存储这些数据，并通过主键和外键建立关联。

二、表结构设计与创建过程

1. 学生表（Student）

存储学生的基本信息，主键为学号（s_id）。

CREATE TABLE `Student`(
    `s_id` VARCHAR(20),  -- 学号（字符串类型，长度20）
    `s_name` VARCHAR(20) NOT NULL DEFAULT '',  -- 姓名（非空，默认空字符串）
    `s_birth` VARCHAR(20) NOT NULL DEFAULT '',  -- 生日（字符串类型，存储格式如1990-01-01）
    `s_sex` VARCHAR(10) NOT NULL DEFAULT '',  -- 性别（男/女）
    PRIMARY KEY(`s_id`)  -- 主键为学号
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  -- 引擎为InnoDB，支持事务和外键；编码为utf8mb4，支持 emoji

设计说明：

学号用VARCHAR而非INT，因为学号可能包含字母（如 "2023001A"）
所有字段设置NOT NULL DEFAULT ''，避免空值（NULL）带来的查询麻烦
选择InnoDB引擎，支持外键约束和事务，适合数据关联紧密的场景

2. 课程表（Course）

存储课程信息，关联授课教师，主键为课程号（c_id）。

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 DEFAULT CHARSET=utf8mb4;

设计说明：

t_id（教师号）未直接设置外键约束，但逻辑上关联教师表的t_id，后续可通过查询关联
课程名设置为非空，确保每门课程都有名称

3. 教师表（Teacher）

存储教师基本信息，主键为教师号（t_id）。

CREATE TABLE `Teacher`(
    `t_id` VARCHAR(20),  -- 教师号
    `t_name` VARCHAR(20) NOT NULL DEFAULT '',  -- 教师姓名
    PRIMARY KEY(`t_id`)  -- 主键为教师号
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

设计说明：

结构简单，仅包含必要的教师号和姓名，如需扩展可添加职称、部门等字段

4. 成绩表（Score）

存储学生的课程成绩，是学生表和课程表的中间表，主键为 “学号 + 课程号” 的组合。

CREATE TABLE `Score`(
    `s_id` VARCHAR(20),  -- 学生号（关联学生表）
    `c_id` VARCHAR(20),  -- 课程号（关联课程表）
    `s_score` INT(3),  -- 分数（整数，最大3位，如100分）
    PRIMARY KEY(`s_id`,`c_id`)  -- 联合主键：一个学生对一门课程只有一个成绩
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

设计说明：

联合主键（s_id,c_id）确保不会出现重复的学生 - 课程成绩记录
分数用INT(3)，适合存储 0-100 的整数分数，如需小数可改为DECIMAL(5,2)

三、插入测试数据：让表 “活” 起来

表结构创建完成后，需要插入测试数据才能进行查询练习。以下是为四张表插入的模拟数据：

1. 学生表数据

insert into Student values
('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1989-07-01' , '女'),
('08' , '王菊' , '1990-01-20' , '女');

2. 课程表数据

insert into Course values
('01' , '语文' , '02'),  -- 课程01（语文）由教师02授课
('02' , '数学' , '01'),  -- 课程02（数学）由教师01授课
('03' , '英语' , '03');  -- 课程03（英语）由教师03授课

3. 教师表数据

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

4. 成绩表数据

insert into Score values
('01' , '01' , 80),  -- 学生01的01课程（语文）成绩80
('01' , '02' , 90),  -- 学生01的02课程（数学）成绩90
('01' , '03' , 99),  -- 学生01的03课程（英语）成绩99
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);

四、多表查询实战：7 个经典案例解析

有了表和数据，就可以开始练习多表查询了。以下是 7 个经典场景的查询实现，涵盖连接查询、分组统计、条件筛选等核心技巧。

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

需求：找出同时选修了 01（语文）和 02（数学）课程，且语文成绩高于数学成绩的学生。

select 
  s.s_id, s.s_name, s.s_sex, s.s_birth, 
  sc1.s_score as '语文成绩', 
  sc2.s_score as '数学成绩' 
from Student s
inner join Score sc1 on s.s_id = sc1.s_id and sc1.c_id = '01'  -- 关联01课程成绩
inner join Score sc2 on s.s_id = sc2.s_id and sc2.c_id = '02'  -- 关联02课程成绩
where sc1.s_score > sc2.s_score;  -- 筛选语文成绩>数学成绩的学生

关键思路：
通过两次内连接Score表，分别获取 01 和 02 课程的成绩，再通过where子句比较分数。

2. 查询平均成绩≥60 分的学生信息及平均成绩

需求：统计所有学生的平均成绩，仅显示平均分及格（≥60 分）的学生。

select 
  s.s_id, 
  s.s_name, 
  avg(score.s_score) as '平均成绩' 
from student as s
inner join score on s.s_id = score.s_id  -- 关联成绩表（只统计有成绩的学生）
group by s.s_id, s.s_name  -- 按学生分组
having avg(score.s_score) >= 60;  -- 筛选平均分≥60的组

关键思路：

用inner join确保只统计有成绩的学生
group by按学号和姓名分组，使每个学生为一行
having子句用于筛选分组后的聚合结果（where不能直接用于聚合函数）

3. 查询平均成绩 < 60 分的学生（含无成绩的学生）

需求：包含所有学生（即使未选课），平均分按 0 处理，仅显示平均分不及格的学生。

select 
  s.s_id, 
  s.s_name, 
  ifnull(avg(score.s_score), 0) as '平均成绩' 
from student as s
left join score on s.s_id = score.s_id  -- 保留所有学生（包括无成绩的）
group by s.s_id, s.s_name
having ifnull(avg(score.s_score), 0) < 60;  -- 用ifnull处理空值

关键思路：

left join保留所有学生，未选课的学生成绩为null
ifnull(avg(score.s_score), 0)将空值转为 0，确保未选课的学生被正确统计
having子句直接使用处理后的平均成绩进行筛选

4. 查询学过 “张三” 老师授课的学生信息

需求：通过教师姓名反向查找所有上过该老师课程的学生。

select distinct s.*  -- distinct去重（同一学生可能选多门该老师的课）
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  -- 成绩→课程
inner join Teacher t on c.t_id = t.t_id  -- 课程→教师
where t.t_name = '张三';  -- 筛选教师名为“张三”的记录

关键思路：
通过四表联查（学生→成绩→课程→教师），逐层关联找到 “张三” 老师的课程对应的学生，用distinct去重避免重复记录。

5. 查询所有同学的选课总数和总成绩

需求：统计每个学生的选课数量和总分，包括未选课的学生（总分按 0 处理）。

select 
  student.s_id, 
  student.s_name, 
  count(score.c_id) as '选课总数',  -- 统计课程数量（null不计数）
  ifnull(sum(score.s_score), 0) as '总成绩'  -- 用ifnull处理无成绩的情况
from student
left join score on student.s_id = score.s_id
group by student.s_id, student.s_name;

关键思路：

left join确保所有学生都被统计
count(score.c_id)统计选课数量（未选课的学生计为 0）
sum(score.s_score)计算总分，用ifnull将无成绩的学生总分设为 0

6. 查询同时学过 “01” 和 “02” 课程的学生信息

需求：找出同时选修了 01（语文）和 02（数学）两门课程的学生。

select s.* 
from student as s
inner join Score sc1 on s.s_id = sc1.s_id and sc1.c_id = '01'  -- 选修了01课程
inner join Score sc2 on s.s_id = sc2.s_id and sc2.c_id = '02';  -- 同时选修了02课程

关键思路：
两次内连接Score表，分别限定课程号为 01 和 02，只有同时满足两个条件的学生才会被保留。

7. 查询学过 “01” 但未学过 “02” 课程的学生信息

需求：找出选修了 01（语文）但未选修 02（数学）课程的学生。

select s.* 
from student as s
inner join Score sc1 on s.s_id = sc1.s_id and sc1.c_id = '01'  -- 确保选修了01课程
left join Score sc2 on s.s_id = sc2.s_id and sc2.c_id = '02'  -- 关联02课程（未选修则为null）
where sc2.c_id is null;  -- 筛选未选修02课程的学生

关键思路：

先通过inner join筛选选修了 01 课程的学生
再通过left join关联 02 课程，未选修的学生sc2.c_id为null
用where sc2.c_id is null筛选出未学过 02 课程的学生

五、总结：SQL 多表查询的核心技巧

选择合适的连接方式：
- inner join：只保留匹配的记录，适合需同时存在的数据（如 “同时选修两门课的学生”）
- left join：保留左表所有记录，适合需包含全部主表数据的场景（如 “所有学生的成绩统计”）
灵活使用聚合函数与分组：
- 聚合函数（avg、sum、count）用于统计分析
- group by按字段分组，确保每个分组只出现在一行结果中
- having子句用于筛选分组后的聚合结果（替代where无法处理聚合函数的问题）
处理空值（null）：
用ifnull()（MySQL）或coalesce()（通用）将null转为有意义的值（如 0），避免统计失真。
多表关联的逻辑链：
复杂查询需拆解为 “表 1→表 2→表 3” 的关联链，通过共同字段（如s_id、c_id）逐层连接。

通过本文的表设计和查询案例，你可以清晰地看到：合理的表结构是高效查询的基础，而多表查询的核心是理解表之间的关系，通过连接、分组、筛选的组合实现需求。勤加练习这些案例，能快速提升你的 SQL 实战能力！

学生成绩管理系统的 SQL 表设计与多表查询实战

一、需求分析：为什么需要这些表？

二、表结构设计与创建过程

1. 学生表（Student）

2. 课程表（Course）

3. 教师表（Teacher）

4. 成绩表（Score）

三、插入测试数据：让表 “活” 起来

1. 学生表数据

2. 课程表数据

3. 教师表数据

4. 成绩表数据

四、多表查询实战：7 个经典案例解析

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

2. 查询平均成绩≥60 分的学生信息及平均成绩

3. 查询平均成绩 < 60 分的学生（含无成绩的学生）

4. 查询学过 “张三” 老师授课的学生信息

5. 查询所有同学的选课总数和总成绩

6. 查询同时学过 “01” 和 “02” 课程的学生信息

7. 查询学过 “01” 但未学过 “02” 课程的学生信息

五、总结：SQL 多表查询的核心技巧

网站公告

今日签到

热门文章

最新发布