一、一份成绩单背后有四拨人

和前三章最大的区别：系统第一次出现「只能看自己数据」的用户。 酒店里前台能看所有房，人事里普通员工只能看自己——但那都是桌面程序，界面挡住就算数。 这里换成了浏览器，界面挡不住任何人。 用户可以在地址栏里随便改参数、可以直接调接口、可以看页面源码。 所以「藏起来」在这个环境里等于没做。

二、业务动作对到表：三张表就够

只有三张表，但够用了。 课程没单独建表，直接做成了 tb_score.subject 这个字段—— 这是教材的简化，理由是「科目固定，就三科」。 真要做成选课系统，必须加 tb_course 表 + 选课关联表。 判断标准三条：科目会不会变、要不要记学分、要不要记任课老师。 三条里有一条是「要」，就该建表。

三、制度条款就是约束

第三条 UNIQUE (stu_id, subject, term) 是这一章最要紧的一条。 没有它，录两次成绩就变成两条记录—— 算平均分时这个学生的成绩被算了两次，平均分就偏了，而且偏得看不出来。 第四条要「NOT NULL + 外键」两个一起才有效。 只写外键不写 NOT NULL，class_id 可以是 NULL—— 外键放行 NULL，这个学生就成了「没有班的学生」，界面上到处查不到。

四、角色与权限：这次多了「数据范围」

注意第二、三行：这不是「菜单不同」，是「同一条 SQL 加不同的 WHERE」。 这种限制叫「数据范围权限」，比菜单权限难做得多。 「教师只能看自己带的班」最容易做漏。 因为菜单上教师只能点「我的班级」，看着就限制了—— 但接口是可以直接调 URL 的，?classId=2 改一下就看到了别人班。 所以每个「按班级查」的接口，第一句都必须是「这个班是不是我带的」。 这一句不是「顺便校验」，是「唯一的安全线」。

五、建出来看看：四班十二人三十六条成绩

12 × 3 = 36，这个乘法关系要记住。 它是后面所有「汇总」和「核对」的基数—— 数不对，一定是重复录入或者漏录。 注意 tb_score 里没有 class_id。 学生转班了，他的历史成绩不该跟着变班。 需要「按班统计」时，从成绩连到学生再连班级——多一次 JOIN，换「转班不改历史」。

六、动手 Demo：三种角色、三张表、四个班

这一段把 20.1 那三张表落成真库，重点看两处： 一是「制度原话 → 约束」那些条款真的建进去了，二是三种角色的「能看范围」怎么表达。 最后看第四节那张表——它把「角色」翻译成了「WHERE 该怎么写」。

import sqlite3

DDL = '''
CREATE TABLE tb_student (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  sno      TEXT NOT NULL UNIQUE,
  sname    TEXT NOT NULL,
  sex      TEXT NOT NULL CHECK (sex IN ('男','女')),
  class_id INTEGER NOT NULL REFERENCES tb_class(id),
  state    TEXT NOT NULL DEFAULT '在读'
                    CHECK (state IN ('在读','转出','毕业'))
);
CREATE TABLE tb_score (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  stu_id  INTEGER NOT NULL REFERENCES tb_student(id),
  subject TEXT    NOT NULL,
  term    TEXT    NOT NULL,
  score   REAL    NOT NULL CHECK (score >= 0 AND score <= 100),
  UNIQUE (stu_id, subject, term)
);
'''

c.execute('PRAGMA foreign_keys = ON')
c.executescript(DDL)
print(c.execute('SELECT COUNT(*) FROM tb_class').fetchone()[0])
print(c.execute('SELECT COUNT(*) FROM tb_student').fetchone()[0])
print(c.execute('SELECT COUNT(*) FROM tb_score').fetchone()[0])

实跑输出：

--------------------------------------------------------------------
需求分析：三种角色三个界面
--------------------------------------------------------------------


  ① 业务背景：一份成绩单背后有四拨人
  --------------------------------------------------------------------
| 谁 | 想干什么 | 最关心什么 |
|---|---|---|
| 教务（管理员） | 建班级、建课程、发账号 | 数据全不全 |
| 班主任（教师） | 录成绩、改成绩、看班级排名 | 录得快、改得对 |
| 学生 | 查自己的成绩 | 看得见、看不到别人的 |
| 家长 | （不在系统里，看纸质单） | — |

  这和前两章最大的区别：系统第一次出现了「只能看自己数据」的用户。
  酒店里前台能看到所有房，人事里普通员工只能看自己——但那还是桌面程序，界面挡住就算数。
  这里换成了浏览器，界面挡不住任何人。
  所以这一章所有权限判断都必须落到 SQL 的 WHERE 上。
  「学生只能看自己的」，意思就是「学生那次查询一定要带 AND stu_id = 自己」。

  ② 业务动作对到表：三张表就够
  --------------------------------------------------------------------
| 业务动作 | 落到数据上 |
|---|---|
| 建班级，指定班主任 | tb_class 插一行 |
| 学生入学，分班 | tb_student 插一行，带 class_id |
| 期末录成绩，一科一批 | tb_score 每人每科一行 |
| 改成绩（录错了） | UPDATE tb_score |
| 查班级排名 | tb_score 按班级聚合 |
| 学生查自己 | tb_score 加 stu_id 过滤 |

  只有三张表，但够用了。
  课程没单独建表，直接做成了 tb_score.subject 这个字段——这是教材的简化，理由是「科目固定就三科」。
  真要做成选课系统，必须加 tb_course 表 + 选课关联表。
  判断标准：科目会不会变、要不要记学分、要不要记任课老师。三条里有一条是「要」，就该建表。

  ③ 制度条款就是约束
  --------------------------------------------------------------------
| 制度原话 | 落到哪 |
|---|---|
| 学号全校唯一 | sno UNIQUE |
| 成绩 0 到 100，不能负 | CHECK (score >= 0 AND score <= 100) |
| 一人一科一学期只有一条 | UNIQUE (stu_id, subject, term) |
| 学生必须属于一个班 | class_id NOT NULL + 外键 |
| 有学生的班级不能删 | 外键默认拦（RESTRICT） |
| 转学不删人，改状态 | state 字段 |

  第三条 UNIQUE (stu_id, subject, term) 是这一章最要紧的一条。
  没有它，录两次成绩就变成两条记录——算平均分时这个学生的成绩被算了两次，平均分就偏了，
  而且偏得看不出来。
  第四条「学生必须属于一个班」用 NOT NULL + 外键，两个一起才有效。
  只写外键不写 NOT NULL，class_id 可以是 NULL——外键放行 NULL，这个学生就成了「没有班的学生」，
  界面上到处查不到。

  ④ 角色与权限：谁看得到谁
  --------------------------------------------------------------------
| 角色 | 能看的范围 | 关键限制 |
|---|---|---|
| 管理员 | 全部班级、全部学生、全部成绩 | 能建账号 |
| 教师 | 自己带的班 | 只能录改自己班的成绩 |
| 学生 | 只有自己 | 一次查询必须带自己的 id |

  「教师只能看自己带的班」这一条最容易做漏。
  因为菜单上教师只能点「我的班级」，看着就限制了——但接口是可以直接调 URL 的，
  ?classId=2 改一下就看到了别人班。
  所以每个「按班级查」的接口，第一句都必须是「这个班是不是我带的」。
  这一句不是「顺便校验」，是「唯一的安全线」。

  ⑤ 建出来看看：三个班、十二个学生、三十六条成绩
  --------------------------------------------------------------------

| 表 | 行数 | 说明 |
|---|---:|---|
| tb_class | 4 | 四个班 |
| tb_student | 12 | 十二人 |
| tb_score | 36 | 每人三科 |
| tb_user | 0 | 账号，20.6 建 |

  12 人 × 3 科 = 36 条，这个乘法关系要记住。
  它是后面「按班级汇总」和「按科目汇总」的基数——数不对，一定是重复录入或者漏录。

三张表 4／12／36 这三个数，是这一章后面所有断言的基础。 录完成绩发现是 35 条，就说明少录了一条——这种「数不对」的检查，比逐行看页面快一百倍。 tb_score 只有三列进唯一键，但没有 class_id。 这不是漏了，是故意的——成绩属于「人」，不属于「班」。 人换了班，成绩还是他的。 state 这个字段在成绩系统里比在人事系统里更重要。 因为学生毕业是常态——每届都要「毕业」一批，而毕业后名单还留着，成绩还要能查。

30 秒实验

1. 把 tb_score 的 UNIQUE 去掉，录两次数学成绩，看平均分怎么偏。 再想想：这种偏，页面上看得出来吗？ 看不出来。
2. 把 class_id 的 NOT NULL 去掉，插一个 class_id 为 NULL 的学生， 看「按班级查名单」能不能查到他。 查不到——这就是「没有班的学生」。
3. 想一想：「学生只能看自己」这句话，翻译成 SQL 是哪几个字？ AND stu_id = ?——就这几个字，但它必须出现每一次。

常见误区

1. 「界面上不显示就安全了」。 浏览器里界面挡不住任何人。 地址栏改个参数就进去了。
2. 「课程不用建表，用字段就行」。 科目固定就还行，一旦要记学分、记任课老师，就得建表。 判断标准是「会不会变、要不要多存信息」。
3. 「class_id 能空着，先插入再说」。 NULL 外键不拦，学生会变成「没有班的人」，界面上到处查不到。 所以 NOT NULL 和外键要一起写。

下一篇 20.2，画功能结构： 这次要画「一棵全的树」，再决定三种角色各看哪几枝——顺便看一个反例：同一个页面，三种数据范围。

• ☐ （本篇完，下一篇 20.2 系统设计）

相关笔记

• 需求分析
• 需求分析
• 创建、删除和修改约束
• 19 课 网络与信息安全
• 🪟 SQL Server从入门到精通 · 索引