数据库系统模块知识点故事集

1. 数据的 “智能仓库”—— 数据库与数据库系统

老林开了一家 “环球商品贸易公司”，业务覆盖全球，客户、商品、订单数据越来越多。一开始他用 Excel 记录，结果出现 “客户信息重复存”“订单找不到对应商品”“多人同时改数据导致错乱” 的问题，生意越做越乱。

后来他请了个 “数据管家团队” 搭建了 “智能仓库”（数据库系统 DBS），整个团队分工明确：

• 核心仓库（数据库 DB）：按统一规则存放所有数据，比如 “客户表”“商品表”“订单表”，数据有序且不重复；
• 硬件设备：高性能服务器和存储设备，保证仓库 24 小时稳定运行；
• 管理软件（DBMS，数据库管理系统）：负责数据的存入、查询、修改、删除，还能解决多人同时操作的冲突；
• 团队人员：
  • 系统分析师：摸清公司数据需求，设计仓库存储规则；
  • 数据库管理员（DBA）：维护仓库秩序，保证数据安全和系统稳定；
  • 应用程序员：开发查询、下单等操作界面；
  • 最终用户：通过界面查询商品、提交订单。

这个 “智能仓库” 有个神奇之处：数据按规则组织，冗余少；所有人都能共享数据，不用各自存一份；数据独立性高，比如改了商品存储方式，不影响用户查询；还能随时扩展，新增 “物流表”“售后表” 都很方便。

2. 仓库的 “三层架构”—— 三级模式 - 两级映像

“智能仓库” 能稳定运行，核心是有一套 “三层存储规则” 和 “两层翻译机制”，确保数据安全又灵活：

• 内模式（物理层）：相当于仓库的 “货架布局图”，记录数据实际存在服务器的哪个磁盘、哪个扇区，比如 “客户表数据存在磁盘 1 的第 3 扇区”，只有 DBA 知道；
• 模式（概念层）：就是仓库的 “货物分类表”，比如把所有商品按 “电子产品”“日用品” 分类存放，对应数据库里的基本表，是所有用户共享的核心结构；
• 外模式（用户层）：相当于给不同用户定制的 “专属视图”，比如销售人员只能看到 “商品名称、价格、库存”，财务人员只能看到 “订单金额、收款状态”，看不到无关数据。

两层 “翻译机制”（映像）解决了 “用户怎么找到数据” 和 “数据存储变了怎么办” 的问题：

• 外模式 - 模式映像：比如销售人员查 “商品库存”（外模式），映像会自动对应到 “商品表”（模式）的 “库存字段”。如果商品表新增了 “供应商字段”，只要修改映像，销售人员的查询界面不用变；
• 模式 - 内模式映像：如果服务器升级，数据从磁盘 1 移到磁盘 2（内模式变了），只要修改映像，用户查询时还是能通过 “商品表” 找到数据，完全不受影响。

3. 数据的 “蓝图设计”—— 数据库设计六步走

搭建 “智能仓库” 前，“数据管家团队” 花了 6 个月做设计，一步都不能少：

（1）需求分析：摸清家底

团队和销售、财务、物流部门反复沟通，明确要存哪些数据（客户、商品、订单）、怎么用数据（下单、查库存、统计销量）、有什么约束（订单必须关联客户和商品）。最终产出了数据流图（比如 “客户下单→订单审核→库存扣减” 的流程）、数据字典（记录每个数据的含义和格式）、需求说明书。

（2）概念结构设计：画 E-R 图

团队用 “实体 - 联系图（E-R 图）” 画出数据关系：

• 长方形代表实体：比如 “客户”“商品”“订单”；
• 椭圆代表属性：比如 “客户” 有客户 ID、姓名、电话；
• 菱形代表联系：“客户” 和 “订单” 是 “下订单” 联系（1 个客户能下多个订单，1 个订单属于 1 个客户，即 1:N）；“商品” 和 “订单” 是 “包含” 联系（1 个订单能包含多个商品，1 个商品能在多个订单中，即 M:N）。

画 E-R 图时还解决了三个冲突：

• 属性冲突：销售说 “客户电话” 要存 11 位手机号，财务说要加区号，最终统一为 “带区号的 13 位电话”；
• 命名冲突：销售叫 “商品编号”，仓库叫 “货品 ID”，统一为 “商品 ID”；
• 结构冲突：物流部门把 “物流信息” 当属性，销售部门把 “物流信息” 当实体，最终确定 “物流” 为独立实体，和订单关联。

（3）逻辑结构设计：E-R 图转关系模式

把 E-R 图变成数据库能识别的 “表结构”：

• 实体直接转表：“客户” 实体→“客户表”（客户 ID，姓名，电话，地址）；
• 1:1 联系：比如 “客户” 和 “会员账户” 是 1:1，可在 “客户表” 加 “会员账户 ID” 字段，或单独建 “会员账户表” 关联客户 ID；
• 1:N 联系：“客户（1）- 订单（N）”，在 “订单表” 加 “客户 ID” 字段（外键）；
• M:N 联系：“商品（M）- 订单（N）”，必须单独建 “订单商品表”（订单 ID，商品 ID，购买数量），用两个实体的主键当联合主键。

（4）物理设计：确定存储细节

DBA 决定：客户表和订单表存在高速磁盘，提高查询速度；历史订单表存在普通磁盘，节省成本；给 “订单 ID”“商品 ID” 建索引，加快查询速度；数据按 “订单日期” 分区存储，方便统计每月销量。

（5）数据库实施：落地搭建

团队用 SQL 语句创建表、设置主键外键，把原来 Excel 里的数据导入新数据库，开发查询、下单等应用程序，然后试运行，发现 “订单表缺少‘支付状态’字段”“商品库存更新不及时” 等问题，逐一修复。

（6）运行与维护：长期保障

系统上线后，DBA 定期备份数据、监控系统性能，比如发现 “查询近 3 个月订单变慢”，就优化索引；业务扩展时新增 “售后表”，确保数据库长期稳定支持公司发展。

4. 数据的 “抽象模型”—— 数据模型三要素

“智能仓库” 能适配不同业务，靠的是灵活的 “数据模型”，每个模型都包含三个核心要素：

• 数据结构：数据的组织形式，比如 “客户表” 按 “客户 ID” 排序，“订单表” 和 “客户表” 通过 “客户 ID” 关联，就像仓库里 “按区域放货，贴标签关联”；
• 数据操作：对数据的增删改查，比如 “新增客户”“修改商品价格”“查询未付款订单”“删除过期数据”；
• 数据约束条件：数据必须遵守的规则，比如 “客户 ID 不能重复”“商品价格不能为负数”“订单必须关联存在的客户和商品”。

公司常用的有四种数据模型：

• 关系模型：最常用，数据按二维表组织，比如 “客户表”“订单表”，就像 Excel 表格但更规范；
• 概念模型（E-R 模型）：设计阶段用，比如之前画的 E-R 图，从用户角度描述数据关系；
• 网状模型：比如 “商品” 关联 “订单” 和 “供应商”，“订单” 关联 “客户” 和 “物流”，形成一张网，适合复杂关系；
• 面向对象模型：把数据和操作封装成 “对象”，比如 “客户对象” 包含客户信息和 “下单”“查询订单” 的方法，支持继承和多态，适合复杂业务场景。

5. E-R 图的 “细节讲究”—— 实体、属性与联系

画 E-R 图时，“数据管家团队” 抠了很多细节，确保数据关系准确：

实体与属性

• 实体是客观存在的事物：比如 “客户”“商品”“订单”，甚至抽象的 “物流”“售后”；
• 属性是实体的特性：
  • 简单属性：不能再分，比如 “客户姓名”；
  • 复合属性：能再分，比如 “客户地址” 可分为 “省、市、街道、邮编”；
  • 单值属性：一个实体只有一个值，比如 “客户身份证号”；
  • 多值属性：一个实体有多个值，比如 “客户的多个联系电话”，存储时要单独建表；
  • NULL 属性：值未知或不存在，比如 “客户的备用电话” 可能没有；
  • 派生属性：由其他属性计算得出，比如 “客户总消费额” 由所有订单金额相加得到，不用单独存储。

弱实体与强实体

“售后记录” 必须依赖 “订单” 存在，没有订单就没有售后，所以 “售后记录” 是弱实体，“订单” 是强实体，E-R 图中弱实体用虚线长方形表示。

联系类型

• 一对一（1:1）：比如 “客户” 和 “会员账户”，一个客户只能有一个会员账户，一个会员账户属于一个客户；
• 一对多（1:N）：比如 “部门” 和 “员工”，一个部门有多个员工，一个员工只属于一个部门；
• 多对多（M:N）：比如 “学生” 和 “课程”，一个学生能选多门课程，一门课程能被多个学生选，必须转成中间表 “选课表”。

6. 数据的 “运算法则”—— 关系代数

“智能仓库” 里的数据能灵活组合查询，靠的是 “关系代数” 这套 “运算法则”：

（1）并、交、差：数据的 “组合与筛选”

• 并（∪）：比如 “北京客户表” 和 “上海客户表” 合并，相同客户只保留一条，最终得到 “华北华东客户表”；
• 交（∩）：找出 “北京客户表” 和 “购买过电子产品的客户表” 的共同客户，即 “北京地区购买过电子产品的客户”；
• 差（-）：“所有客户表” 减去 “已下单客户表”，得到 “未下单客户表”，方便销售跟进。

（2）笛卡尔积：数据的 “全排列”

“商品表” 有 3 条记录，“订单表” 有 2 条记录，笛卡尔积（×）会生成 3×2=6 条记录，每条商品记录都和每条订单记录组合。但大多是无效组合，需要后续筛选。

（3）投影与选择：数据的 “裁剪与过滤”

• 投影（π）：只选需要的列，比如从 “客户表” 中只选 “客户姓名” 和 “电话”，得到 “客户联系方式表”；
• 选择（σ）：按条件选记录，比如从 “订单表” 中选 “支付状态 = 未付款” 的记录，得到 “待付款订单表”。

（4）自然连接：数据的 “智能匹配”

比如 “订单表”（订单 ID，客户 ID，商品 ID，数量）和 “客户表”（客户 ID，姓名，电话）自然连接，会自动匹配 “客户 ID” 相同的记录，合并后只保留一个 “客户 ID” 列，得到 “包含客户信息的订单表”，不用手动关联，比笛卡尔积高效得多。

7. 数据的 “依赖规则”—— 函数依赖与 Armstrong 公理

“智能仓库” 的数据之所以不混乱，是因为遵循 “函数依赖” 规则，比如 “客户 ID” 能唯一确定 “客户姓名”，就说 “客户 ID→客户姓名”，就像身份证号能唯一确定一个人。

还有两种特殊依赖：

• 部分函数依赖：比如 “订单 ID + 商品 ID” 能确定 “商品价格”，但单独 “商品 ID” 也能确定 “商品价格”，这就是 “部分函数依赖”，会导致数据冗余；
• 传递函数依赖：“客户 ID→省份”，“省份→省会”，所以 “客户 ID→省会”（客户 ID 和省份不等价），这就是传递函数依赖，可能导致数据不一致。

为了规范依赖，有一套 “Armstrong 公理”：

• 自反律：如果 “客户姓名” 属于 “客户 ID + 客户姓名”，那么 “客户 ID + 客户姓名→客户姓名”，显然成立；
• 增广律：如果 “客户 ID→电话”，那么 “客户 ID + 姓名→电话 + 姓名”；
• 传递律：如果 “客户 ID→省份”“省份→省会”，那么 “客户 ID→省会”；
• 合并规则：如果 “客户 ID→姓名”“客户 ID→电话”，那么 “客户 ID→姓名 + 电话”；
• 伪传递律：如果 “客户 ID→省份”“省份 + 年份→GDP”，那么 “客户 ID + 年份→GDP”；
• 分解规则：如果 “客户 ID→姓名 + 电话”，那么 “客户 ID→姓名”“客户 ID→电话”。

8. 数据的 “标识与约束”—— 键与完整性约束

为了确保数据准确唯一，“智能仓库” 有一套 “标识体系” 和 “约束规则”：

键的分类

• 超键：能唯一标识记录的属性组合，比如 “客户 ID + 姓名” 能唯一确定客户，但 “姓名” 可能重复，所以 “客户 ID” 就够了；
• 候选键：超键去掉冗余属性，比如 “客户 ID”“身份证号” 都是候选键，都能唯一标识客户；
• 主键：从候选键中选一个当 “主标识”，比如选 “客户 ID” 当主键，每张表只能有一个主键；
• 外键：其他表的主键，比如 “订单表” 的 “客户 ID” 是 “客户表” 的主键，所以 “客户 ID” 是 “订单表” 的外键；
• 主属性：候选键里的属性，比如 “客户 ID”“身份证号”，其他属性（如姓名、电话）是非主属性。

完整性约束

• 实体完整性约束：主键值不能为空，也不能重复，比如 “客户 ID” 不能是 “NULL”，也不能有两个客户用同一个 “客户 ID”；
• 参照完整性约束：外键必须是其他表中存在的主键值，或为空，比如 “订单表” 的 “客户 ID” 必须在 “客户表” 中存在，不能填一个不存在的 “客户 ID”；
• 用户自定义完整性约束：自定义规则，比如 “商品价格≥0”“订单数量≥1”“年龄在 0-150 之间”。

9. 数据的 “规范标准”—— 范式

“智能仓库” 刚设计时，“订单表” 里存了 “客户 ID、客户姓名、商品 ID、商品名称、价格、数量”，导致 “客户姓名”“商品名称” 重复存储（比如一个客户下多个订单，客户姓名存多次），修改时要改所有记录，容易出错。这时候需要用 “范式” 规范表结构：

• 1NF（第一范式）：表中不能有 “小表”，每个属性必须是不可分的数据项。比如不能把 “客户地址” 存成 “省 - 市 - 街道”，要拆成三个独立字段，否则不符合 1NF；
• 2NF（第二范式）：在 1NF 基础上，非主属性完全依赖候选键，消除部分函数依赖。比如 “订单表” 主键是 “订单 ID + 商品 ID”，“客户姓名” 依赖 “客户 ID”（部分依赖），要拆成 “订单表”（订单 ID，客户 ID，商品 ID，数量）和 “客户表”（客户 ID，姓名，电话）；
• 3NF（第三范式）：在 2NF 基础上，消除非主属性对候选键的传递依赖。比如 “客户表”（客户 ID，姓名，省份，省会），“省会” 依赖 “省份”（传递依赖），要拆成 “客户表”（客户 ID，姓名，省份）和 “省份表”（省份，省会）；
• BCNF（BC 范式）：在 3NF 基础上，消除主属性对候选键的部分依赖和传递依赖。比如 “课程表”（课程 ID，教师 ID，教师姓名），候选键是 “课程 ID”“教师 ID + 课程 ID”，“教师姓名” 依赖 “教师 ID”（主属性部分依赖），要拆成 “课程表”（课程 ID，教师 ID）和 “教师表”（教师 ID，姓名）。

范式越高，数据冗余越少，但查询时可能需要关联多张表，所以实际设计时会平衡范式和查询效率。

10. 数据的 “并发安全”—— 事务与并发控制

公司业务繁忙时，多个销售人员同时操作数据库：A 销售给客户下单，B 销售查该客户库存，C 销售改商品价格，容易出现问题，这时候 “事务” 和 “并发控制” 就派上用场了。

事务的 ACID 特性

一个 “下单操作” 就是一个事务，必须满足四个特性：

• 原子性：要么全做，要么全不做。比如 “扣减库存 + 生成订单”，不能只扣库存不生成订单，也不能只生成订单不扣库存；
• 一致性：事务前后数据一致。比如下单前商品库存 10 件，下单后库存 9 件，总库存变化正确；
• 隔离性：多个事务同时执行，互不干扰。比如 A 销售下单时，B 销售查库存，要么看到下单前的 10 件，要么看到下单后的 9 件，不会看到 9.5 件；
• 持续性：事务提交后，数据变化永久保存。比如下单成功后，就算服务器断电，再次启动后库存还是 9 件。

并发带来的三个问题

没有控制时，并发操作会出乱子：

• 丢失更新：A 销售和 B 销售同时给客户下单，都扣减 1 件库存，库存从 10 变成 8，而实际应该变成 9，丢失了一次更新；
• 不可重复读：B 销售第一次查库存是 10 件，A 销售下单后库存变成 9 件，B 销售再次查询发现库存变了，无法重复得到相同结果；
• 读脏数据：A 销售下单后扣减库存到 9 件，但事务没提交（还能回滚），B 销售查到库存 9 件，之后 A 销售回滚，库存变回 10 件，B 销售读到的 9 件就是 “脏数据”。

封锁协议解决并发问题

“数据管家” 用 “封锁” 来解决问题，就像给数据加 “锁”：

• X 锁（排它锁 / 写锁）：A 销售给商品加 X 锁后，只能 A 读写，其他人不能加任何锁，直到 A 释放；
• S 锁（共享锁 / 读锁）：B 销售给商品加 S 锁后，只能读不能改，其他人能加 S 锁（一起读），但不能加 X 锁，直到 B 释放。

还有三级封锁协议：

• 一级封锁协议：修改数据前加 X 锁，事务结束释放，解决丢失更新问题；
• 二级封锁协议：一级 + 读数据前加 S 锁，读完就释放，解决丢失更新和读脏数据问题；
• 三级封锁协议：一级 + 读数据前加 S 锁，事务结束释放，解决所有三个问题。

11. 数据的 “安全备份”—— 数据库备份与恢复

“智能仓库” 怕数据丢失，所以有一套完善的 “备份与恢复” 机制：

备份类型

• 静态转储（冷备份）：半夜仓库不营业时备份，备份期间不能操作数据库。优点是备份快、容易归档；缺点是只能恢复到备份时间点，不能按表恢复；
• 动态转储（热备份）：白天营业时也能备份，不影响业务。优点是数据库可正常使用，能秒级恢复；缺点是备份不能出错，否则数据无效；
• 完全备份：备份所有数据，比如每周日做一次完全备份；
• 差量备份：备份上一次完全备份后变化的数据，比如周一到周六做差量备份；
• 增量备份：备份上一次备份（不管是完全还是增量）后变化的数据，比如每天做增量备份。

日志文件的作用

数据库会记录 “日志文件”，把事务开始、结束、数据增删改的每一步都记下来。如果服务器崩溃，能通过日志文件撤销未提交的事务，回滚到事务初始状态；也能重做已提交但没写入数据库的事务，确保数据不丢失。

12. 数据的 “分布式管理”—— 分布式数据库

公司业务拓展到全球，在亚洲、欧洲、美洲都有分公司，数据存放在一个地方会导致查询慢、跨地区访问不稳定。“数据管家” 搭建了 “分布式数据库”，把数据分散存放在各地服务器，用一个全局 DBMS 统一管理：

分片模式

• 水平分片：把 “订单表” 按地区拆分，亚洲的订单存在亚洲服务器，欧洲的存在欧洲服务器；
• 垂直分片：把 “客户表” 按字段拆分，“客户基本信息” 存在本地服务器，“客户详细档案” 存在中央服务器。

分布透明性

用户使用时完全不用关心数据存在哪里：

• 分片透明性：查 “所有订单” 时，不用知道订单按地区拆分了；
• 位置透明性：欧洲服务器迁移了，用户查询欧洲订单的操作不变；
• 逻辑透明性：不用知道各地服务器用的是 MySQL 还是 Oracle；
• 复制透明性：同一客户数据在亚洲和中央服务器都有备份，用户不用知道从哪个备份读取。

13. 数据的 “决策支持”—— 数据仓库

公司要分析 “近三年各地区销量趋势”“不同商品的季节销量”，但业务数据库里的数据是实时变化的，不适合复杂分析。“数据管家” 搭建了 “数据仓库”，专门用于决策支持：

数据仓库有四个特点：

• 面向主题：按 “销售分析”“客户分析”“商品分析” 等主题组织数据；
• 集成的：从业务数据库、物流系统、售后系统抽取数据，清理后汇总，消除数据不一致；
• 非易失的：数据一旦进入，很少修改和删除，主要用于查询；
• 随时间变化的：记录历史数据，比如每月的销量数据都保存，能分析趋势。

数据仓库结构分四层：

• 数据源：业务数据库、Excel 文件、日志等数据来源；
• 数据存储与管理：核心部分，按主题存储整合后的数据；
• OLAP 服务器：按多维模型组织数据，比如 “地区 × 商品 × 时间” 三维模型，方便多角度分析；
• 前端工具：报表工具、数据分析工具，帮管理层生成销量报表、预测未来趋势。

14. 数据的 “性能优化”—— 反规范化技术

数据仓库按 3NF 设计后，查询时要关联多张表，速度变慢。“数据管家” 采用 “反规范化技术”，牺牲部分规范化来提高性能：

反规范化的益处与问题

• 益处：减少表连接，降低外键和索引数量，查询速度加快；
• 问题：数据重复存储，浪费磁盘空间；数据一致性难保证，修改时要改多个地方，降低修改速度。

具体方法

• 增加冗余列：在 “订单表” 中保留 “商品名称”，不用每次查询都关联 “商品表”；
• 增加派生列：在 “客户表” 中增加 “总消费额”（由订单金额汇总得到），不用每次查询都计算；
• 重新组表：把 “订单表” 和 “订单商品表” 合并，减少连接；
• 水平分割表：把 “历史订单表” 按年份分成 “2022 订单表”“2023 订单表”，查询时只访问对应年份的表；
• 垂直分割表：把 “客户表” 分成 “客户基本信息表”（常用字段）和 “客户详细信息表”（不常用字段），查询基本信息时减少 I/O 次数。

15. 数据的 “操作语言”——SQL 语句

用户和 “智能仓库” 交互，靠的是 SQL 语言，关键字不区分大小写：

• 创建表：create table 客户表(客户ID int primary key, 姓名 varchar(20), 电话 varchar(11));
• 指定主键：primary key(客户ID)，确保客户 ID 唯一；
• 指定外键：foreign key(客户ID) references 客户表(客户ID)，确保订单关联的客户存在；
• 修改表：alter table 客户表 add 邮箱 varchar(50);（新增邮箱字段）；
• 删除表：drop table 临时表;；
• 建索引：create index idx_商品名称 on 商品表(商品名称);（加快商品名称查询）；
• 建视图：create view 未付款订单视图 as select * from 订单表 where 支付状态='未付款';（给销售定制专属视图）；
• 查询数据：select 姓名, 电话 from 客户表 where 省份='北京';；
• 分组查询：select 商品ID, avg(价格) from 订单商品表 group by 商品ID having avg(价格)>100;（查询平均价格超过 100 的商品）；
• 更名运算：select 客户ID as "学号" from 客户表;（把客户 ID 显示为学号）；
• 字符串匹配：select * from 商品表 where 商品名称 like '华为%';（查询华为开头的商品）；
• 排序：select * from 订单表 order by 下单时间 desc;（按下单时间降序排列）。