1.1.1 信息与数据#

1. 信息的特征
   1. 信息源于物质或能量
   2. 信息是可感知的
   3. 信息是可存储、加工、传递和再生的
   4. 信息是有时效性和有价值的
2. 数据的特征：
   1. 数据有类型
   2. 数据有值
   3. 数据有语义
3. 信息与信息的联系：
   1. 数据是信息的具体表现形式，由记录信息的可识别的符号组合而成
   2. 信息 = 数据 + 语义

1.1.2 数据处理与数据管理#

1.1.3 数据库#

1. 数据库是存储在计算机内，有组织的，可共享的数据和数据对象（如表、视图、存储过程和触发器等）的集合
2. 数据库管理数据的特点
   1. 结构化的数据及其联系
   2. 数据共享性高、冗余度低
   3. 数据独立性高
   4. 具有统一的数据管理和控制功能

1.2.1 数据库管理系统的概念#

1.2.2 数据库管理系统的功能#

1. 数据定义功能：提供数据定义语言（DDL），比如 CREATE, DROP, ALTER
2. 数据操纵功能：提供数据操纵语言（DML），比如 INSERT, UPDATE, DELETE
3. 数据库运行管理功能：数据的安全性控制、完整性（一致性）控制（确保数据的正确、有效、相容）、多用户环境下的数据并发控制、数据恢复
4. 数据库的建立和维护功能
5. 数据组织、存储和管理
6. 数据通信接口功能

1.2.3 数据库管理系统的主要组成#

1. 查询处理器：对用户请求的 SQL 语句进行查询优化，然后向存储管理器发出指令使其执行
2. 存储管理器：根据执行策略，从数据库中获取/更新相应的数据
3. 事务管理器：负责资源管理、事务调度，保证数据库的一致性
4. 数据字典：描述数据库中有关信息的数据目录，包括数据库各种模式对象的结构、用户名、用户权限等有关数据库系统的信息

1.3.1 MySQL#

1.3.2 PostgreSQL#

1.3.3 Oracle#

1.4.1 数据库系统的概念#

数据库系统是指在计算机系统中引入数据库后的系统

1.4.2 数据库系统的组成#

1. 数据库：属于数据库系统的核心，为数据库系统的各应用提供数据服务
2. 用户：指使用数据库的人，可对数据库进行存储、维护、检索等操作，分为以下 3 类：
   1. 最终用户：使用数据库系统的各类业务人员，通过数据库应用系统间接使用数据库并完成各类业务操作，一般为非计算机专业人员
   2. 应用程序员：负责为最终用户设计和编写应用程序，对数据库进行操作
   3. 数据库管理员（DBA）：负责设计、建立、管理和维护数据库以及协调用户对数据库要求的个人或工作团队。DBA 的主要职责如下：
      1. 参与数据库设计的全过程
      2. 决定数据库的存储结构和存取策略
      3. 帮助应用程序员使用数据库系统
      4. 定义数据的安全性和完整性约束条件
      5. 监控数据库的使用和运行
      6. 改进和重构数据库
3. 软件系统：主要包括操作系统、数据库管理系统、应用开发工具和应用系统等
4. 硬件系统

1.4.3 内部体系结构#

1. 数据库系统模式的概念：
   1. 模式：数据库中全体数据的逻辑结构和特征的描述
   2. 实例：模式的一个具体值
2. 数据库系统的三级模式结构：
   1. 外模式（子模式/用户模式）：用户视图，一个数据库可以有多个外模式，但一个应用只能使用一个外模式
   2. 模式（概念模式）：数据库中全体数据的逻辑结构和特征的描述
   3. 内模式（存储模式/物理模式）：对数据库存储结构的描述
3. 数据库系统的二级映像功能与数据独立性：
   1. 物理独立性
   2. 逻辑独立性
   3. 外模式/模式映像
   4. 模式/内模式映像
4. 数据库系统的三级模式与二级映像功能的优点
   1. 保证数据的独立性
   2. 简化了用户接口
   3. 有利于数据共享
   4. 有利于数据的安全保密

1.4.4 外部体系结构#

1. 单用户结构（桌面型数据库系统）：将应用程序、DBMS 和数据库都装在一台计算机上，由一个用户独占使用
2. 主从式结构：将应用程序、DBMS 和数据库都集中在一台大型主机上，所有处理任务由这个大型主机来完成，各个用户通过主机并发地存储和共享数据资源
3. 分布式结构：数据库中的数据在逻辑上是一个整体，但在物理上分布在计算机网络的不同节点上
4. 客户机/服务器结构(C/S)：DBMS 和数据库存放于数据库服务器上，应用程序和相关开发工具存放于客户机上
5. 浏览器/服务器结构(B/S)：客户机仅安装通用的浏览器软件，应用程序安装在应用服务器端，充当客户机和数据库服务器的中介

1.5.1 数据模型的层次#

1. 概念模型：

   独立于计算机系统的模型。它不涉及信息在系统中的表示，只是用来描述某个特定组织所关心的信息结构，强调语义表达功能。

   特点：

   1. 语义表达能力丰富
   2. 便于交流和理解
   3. 易于修改和补充
   4. 易于向各种数据模型转换（常见的概念模型: ER 实体联系模型和 OO 面向对象模型）

2. 逻辑模型：

   数据抽象的中间层，用于描述数据库数据的整理逻辑结构，主要用于 DBMS 的实现。

   传统的逻辑数据模型有：层次模型、网状模型、关系模型

   非传统的逻辑数据模型有：面向对象模型、XML 模型、对象关系模型

3. 物理模型：

   用于描述数据的物理存储和存取方法

1.5.2 数据模型的分类#

1. 结构化数据模型：

   指的是具有明确的、预定义的数据模型，遵循一定顺序的数据。

   特征：有明确的含义、有严格一致的顺序、有明确的数据类型

2. 半结构化数据模型： 具有一定的结构化特征，但不完全符合结构化特征的数据，半结构化数据都是无模式的

   两种常用的半结构化模型：XML 和 JSON

1.5.3 数据模型组成要素#

1. 数据结构：描述了数据库的组成对象（类型、内容、性质等）及对象间的联系
2. 数据操作
3. 数据的完整性约束：一组完整性规则的集合。完整性规则是给定数据模型中的数据及其联系所具有的制约和依存规则，用以限定符合数据模型的数据库状态以及状态的变化，以保证数据的正确、有效与相容

1.5.4 数据模型与数据模式#

1.6.1 数据模型与 3 个世界#

1.6.2 现实世界#

1.6.3 信息世界及其模型#

1. 信息世界及其相关概念：

   1. 实体：客观存在并且可以相互区别的“事物”
   2. 属性：实体所具有的某一特性。一个实体可以由若干个属性共同来刻画
   3. 联系：单个实体型内部的联系（组成实体的各属性之间的联系）和实体型之间的联系（不同实体集之间的联系）。

2. 概念模型(E-R 实体联系模型)：

   E-R 模型的基本成分包括实体型、属性和联系：

   1. 实体型：用矩形框表示
   2. 属性：用椭圆框表示
   3. 联系：用菱形框表示

1.6.4 计算机世界及其模型#

1. 计算机世界常用概念：字段（field）、记录、文件（记录的集合）、关键字

1.7.1 数据仓库#

1. 联机事务处理(Online Transaction Processing, OLTP)
2. 联机分析处理(Online Analytical Processing, OLAP)
3. 数据仓库：面向主题的、集成的、相对稳定的、反映历史变化的数据集合

1.7.2 大数据#

1.7.3 数据湖#

存储企业各种原始数据的大型仓库

2.1.1 属性的概念#

属性是实体集中每个实体都具有的特征描述，是实体集某方面信息的数据化抽象，属性又分属性名和属性值

2.1.2 属性的类型#

1. 简单属性：不能再分的最小属性

   复合属性：可进一步划分为更小部分的属性

2. 单值属性：对一个特定实体任何时候都只能有一个单独的值（如学号、年龄）

   多值属性：在某种情况下，一个属性可能对应于一组值（如电话号码）

3. 派生属性：某属性的值可以从其他相关属性/实体（集）派生出来的值（如年龄可由出生日期派生）

2.1.3 多值属性处理#

1. 将多值属性转换为多个单值属性进行表示
2. 将多值属性单独建模为一个依赖于原实体集的弱实体集

2.2.1 实体的概念#

2.2.2 实体的码#

1. 超码(Super Key)：实体集中能够唯一标识一个实体的一个或多个属性的集合。超码可能包含无关紧要的属性
2. 候选键(Candidate Key)：任意真子集都不能成为超码的一个最小超码。一个实体可能存在多个候选键
3. 主键(Primary Key)：从多个候选键中选择一个作为标识实体集的属性

2.2.3 实体描述#

在 E-R 图中，实体集矩形表示，属性用椭圆表示，多值属性用双椭圆表示，派生属性用虚线椭圆表示，属性与实体之间用直线连线表示，加下划线的属性作为实体集的主键

diagrams 图

一个属性所允许的取值范围或集合称为该属性的域，对属性进行详细说明的内容集合称为属性的数据字典（属性名、属性类别、域及约束、实例）

2.3.1 联系的概念#

联系是指不同类实体间的相互关联。在 E-R 图中，用菱形表示联系

diagrams 图

2.3.2 联系的类型#

1. 一对一联系
2. 一对多联系
3. 多对多联系

2.3.3 联系的属性#

概念模型中使用联系的属性来记录联系产生的信息：

diagrams 图

2.3.4 3种特殊联系#

1. 三元联系：发生在 3 个实体间的联系

2. 多联系：给定的两个实体之间可以有多种不同的联系

3. 多值联系：在同一个给定的联系集中，相关联的同样两个实体之间可能存在多个联系（发生多次联系）。比如一个学生可以在多个项目中被同一个指导老师指导

   diagrams 图

2.3.5 联系的码#

二元关系的主键选择依赖于联系的映射基数，基本原则如下：

• 一对一联系：主键可以使用参与联系中的任何一方实体的主键
• 一对多联系：主键由“多”的一方实体的主键组成
• 多对多联系：主键由参与联系中的所有实体的主键组成

2.3.6 联系的描述#

绘制联系时既可以用箭头”→”表示指向联系中的一方实体集，线段 ”-” 表示参与联系中的多方实体集；

也可以统一使用线段相连，同时在线段旁标注联系类型

diagrams 图

2.4.1 弱实体的概念#

属性不足以形成主键，必须依赖于其他实体（强实体）的存在而存在的实体称为弱实体。

对应的强实体和弱实体必须是”一对多”联系。

部分码(Partial Key)：对于某个强实体，对应的一个弱实体中用来标识弱实体的属性（集）称为该弱实体的部分码。弱实体中的实体由其对应强实体的主键与自己的部分码共同标识。

在 E-R 图中，使用双矩形表示弱实体集，用虚下划线表示弱实体的部分码

diagrams 图

2.4.2 弱实体的作用#

1. 多值属性建模为弱实体
2. 多值联系建模为弱实体
3. 使用弱实体转化三元关系
4. 使用弱实体转换实体与联系的联系

2.5.1 E-R 模型设计原则#

1. 忠实性原则
2. 简单性原则
3. 避免冗余原则
4. 实体属性判别原则：
   • 作为属性，不能再具有描述的性质，即属性不可分
   • 属性不能和其他实体相联系
5. 实体联系判别原则：实体对应于现实世界中实际存在的事物，是一种静态的不会变化的状态或存在描述；而联系描述实体间的一种行为，一种动态的状态或动作记录

2.5.2 局部 E-R 设计#

1. 数据抽象方法：
   1. 聚集：用于定义某一事物类型的组成部分，将事物类型的组成成分抽象为实体的属性
   2. 分类：用于定义某一类概念作为现实世界中一组事物的类型说明，将一组具有某些共同特性和行为的事物抽象为一个实体

2.5.3 整体 E-R 设计#

1. E-R 图集成方法：
   1. 多元集成法：一次性将多个局部 E-R 图合并为一个全局 E-R 图
   2. 二元集成法：首先集成两个重要的局部 E-R 图，以后用累加的方法逐步将一个新的 E-R 图集成进来
2. 视图集成步骤：
   1. 合并：在合并局部 E-R 图过程中，消除局部 E-R 图之间的冲突，生成初步 E-R 图
   2. 优化：消除不必要的冗余，生成基本 E-R 图
3. E-R 图中的冲突：
   1. 属性冲突：又分为属性值域冲突和属性的取值单位冲突。属于用户业务上的约定
   2. 命名冲突：表现为实体名、属性、联系名的同名异义或异名同义
   3. 结构冲突：同一对象在不同应用中有不同的抽象（实体与属性冲突）；同一实体在不同应用中属性组成不同；同一联系在不同应用中呈现不同的类型

3.1.1 关系的概念#

1. 域(domain)：一组具有相同数据类型的值的集合，又称为值域（用 D 表示），例如：

   $$D_1=\{\text{李明}, \text{张英}, \text{王刚}\}$$

2. 笛卡尔积(Cartesian Product)：给定一组域 $D_1, D_2, ..., D_n$ （包含的元素既可以完全相同，也可以完全不同），则 $D_1, D_2, ..., D_n$ 的笛卡尔积为：

   $$D_1 * D_2 * ... * D_n = \{(d_1, d_2, d_n) | d_i \in D_i, i = 1, 2, ..., n\}$$

   例如：

   $$D_1 * D_2 = \{(\text{李明}, \text{男}), (\text{李明}, \text{女}), (\text{张英}, \text{男}), (\text{张英}, \text{女})\}$$

   其基数 $M = m_1 * m_2 = 2 * 2 = 4$

3. 关系(Relation)：笛卡尔积的任一子集称为定义在域 $D_1, D_2, ..., D_n$ 上的 n 元关系，可用 $R(D_1, D_2, .., D_n)$ 表示，其中 R 代表关系的名字，n 代表关系的目或度(Degree)

   关系由关系头（关系的型）和关系体（关系的值）组成

3.1.2 关系的性质#

1. 列是同质的，即每一列中的分量必须来自同一个域，必须是同一类型的数据

2. 不同的属性可来自同一个域，但不同的属性必须有不同的名字

3. 列的顺序可以任意交换，对关系无任何影响

4. 关系中元组的顺序（即行序）可任意，对关系无任何影响。因为关系是由无序的元组集合组成的

5. 关系中不允许出现相同的元组

6. 关系中每一分量必须是不可再分的数据项（所有属性值都是原子的）

   非规范化关系：表中有表，属性可以再分

3.1.3 关系模式#

关系的描述称为关系模式，一个完整的关系模式应当是一个五元组：

其中，R 为关系名；U 为组成该关系的属性名集合；D 为属性组 U 中属性所来自的域（属性的类型）；DOM 为属性向域的映像集合（属性的长度）；F 为属性间数据的依赖关系集合，书写过程中一般用下划线表示关系的主键。

关系模式还可以简记为：

其中， $A_1, A_2, ..., A_n$ 为各属性名

3.2.1 实体完整性#

1. 候选键

   能唯一标识关系中元组的一个属性或属性集，称为候选键。一个关系可能有多个候选键。

   形式化定义：

   在关系 R 中有属性 $A_1, A_2, ..., A_n$ ，其属性集 $K = A_i, A_j, ..., A_k$ 当且仅当满足下列条件时， K 被称为候选键：

   1. 唯一性：属性集 K 可以唯一确定一条元组
   2. 最小性：任一属性都不能从属性集 K 中删除，否则将破坏唯一性的性质

2. 主键

   如果一个关系中有多个候选键，可以从中选择一个作为操作变量的候选键称为主键

3. 主属性与非主属性

   • 主属性：包含在任何候选键中的各个属性
   • 非主属性：不包含在任何候选键中的属性

4. 实体完整性的含义

   主键的取值不能为空或部分为空

3.2.2 参照完整性#

1. 外键：如果 $R_1$ 的一个或多个属性 X 不是 $R_1$ 的主键，而是另一关系 $R_2$ 的主键，则称 X 为关系 $R_2$ 的外键，并称 $R_1$ 为参照关系， $R_2$ 为被参照关系
2. 参照完整性的含义：如果关系 $R_1$ 的外键 X 与关系 $R_2$ 的主键相符，则 X 的每一个值等于 $R_2$ 中主键的某个值或者取空值

3.2.3 用户自定义完整性#

用户自定义完整性是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及数据必须满足的语义要求

3.3.1 关系代数运算符#

3.3.2 集合运算符#

3.3.3 关系运算符#

1. 选择 (Selection)

   选择运算是关系代数中非常重要的一项运算，它对应于 SQL 中的 WHERE 子句。按照指定的条件筛选出满足条件的元组（行）。

   定义

   • 符号：σ
   • 表达式：$σ_F(R)$
   • 逻辑：从关系 R 中选择使得逻辑表达式 F 为真的所有元组t。

   逻辑表达式 F

   • 表达式 F 可以是任何能够对关系中的元组进行评估的逻辑条件，例如 $A_1=5$、$A_2>10$ 或者 $A_3=\text{John}$。
   • 这些条件可以使用逻辑运算符（AND, OR, NOT）组合起来，以形成更复杂的查询条件。

   示例

   如果我们有一个学生表 Students(ID, Name, Age, Major)，并且我们想选择所有主修计算机科学的学生，我们可以使用以下选择表达式：

   $σ_{Major}="Computer Science"(Students)$

   或者

   $σ_{(Major="Computer Science")}(Students)$

   这将返回一个新的关系，其中包含了所有满足条件 Major=“Computer Science” 的元组。

2. 投影 (Projection)

   投影运算是关系代数中的另一种基本操作，它对应于 SQL 中的 SELECT 语句（特定列的选择）。投影用于提取关系中的特定列，并创建一个新的关系，该关系仅包含指定的列。

   定义

   • 符号：π
   • 表达式：$π_A(R)$
   • 逻辑：从关系 R 中提取属性子集 A，并创建一个新关系，其中包含原关系的指定列。

   属性子集 A

   • A 是关系 R 中的属性集合，可以是任意数量的属性。
   • 投影操作将从原始关系中选择这些属性，并在新关系中仅包含这些列。

   示例

   假设我们有一个学生表 Students(ID, Name, Age, Major)，并且我们只对学生的名字和专业感兴趣，我们可以使用以下投影表达式：

   $π_{Name, Major}(Students)$

   这将返回一个新的关系，其中仅包含两列：Name 和 Major。

3. 连接 (Join)

   定义

   • 表示法：$R⋈_{AθB}S$
   • 逻辑：从关系 R 和 S 的广义笛卡儿积中选取满足 $AθB$ 条件的元组对。

   参数

   • A 和 B：分别是关系 R 和 S 上的属性组。
   • θ：比较运算符，例如 $=、≠、<、>$ 等。

   类型

   1. 等值连接 (Equi-Join)
      • 定义：当 θ 为等于（=）时的连接称为等值连接。
      • 表示法：$R⋈_{A=B}S$
      • 特点：只包含在指定属性上具有相等值的元组对。
   2. 自然连接 (Natural Join)
      • 定义：一种特殊的等值连接，它自动找出两个关系中名称相同的所有属性，并基于这些属性的相等性进行连接。
      • 表示法：$R⋈S$
      • 特点：结果中不会有重复的属性列，仅包含那些在两个关系中共有的属性的元组。

   示例

   假设有两个关系 R 和 S，其中 R 中有属性 A，S 中有属性 B，要在 A 和 B 上进行等值连接，你会写：

   $R⋈_{A=B}S$

   这将返回一个新的关系，其中包括了 R 和 S 的所有元组对，这些元组对在 A 和 B 上的值相等。

   

   外连接 (Outer Join)

   外连接允许在连接结果中包含即使在一个关系中没有匹配元组也会出现的行。外连接有以下三种类型：

   1. 左外连接 (Left Outer Join)

      • 包含左关系（R）的所有元组，如果在右关系（S）中没有匹配，则在右侧填充 NULL。

   2. 右外连接 (Right Outer Join)

      包含右关系（S）的所有元组，如果在左关系（R）中没有匹配，则在左侧填充 NULL。

   3. 全外连接 (Full Outer Join)

      包含左关系和右关系的所有元组，无匹配的地方填充 NULL。

4. 除运算 (Division)

除运算（Division）在关系代数中是一种稍微复杂一点的运算，它用于处理如“找到所有…”这类的查询。除运算找到所有在一个关系中的元组，它们与另一个关系中所有元组相关联。

定义

除运算是一种同时考虑行和列的运算。给定两个关系 R(X, Y) 和 S(Y, Z)，其中 X、Y、Z 为属性组，除运算的结果是一个新关系，该关系包含了 R 中那些与 S 中 Y 属性上所有值都有关联的 X 属性上的值。

数学表示

给定 R(X, Y) 和 S(Y, Z)，除运算定义为：

其中 $Y_{t_r[X]}$是 R 中 X 值为 $t_r[X]$ 的象集，即 R 中所有 X 属性值为 $t_r[X]$ 的元组在 Y 上的值的集合。

示例解释

我们有关系 R(AB, CD) 和 S(CD)，并且我们想找到所有与 S 中所有 CD 值都有关联的 AB 值。

• 关系 S 在 CD 上的投影是所有可能的 CD 组合，即 {(c, d), (e, f)}。
• 关系 R 中 AB 值为 (a, b) 的像集是 {(c, d), (e ,f), (h, k)}，它包含了 S 的所有 CD 值，因此 (a, b) 出现在结果中。
• 关系 R 中 AB 值为 (c, k) 的像集也包含了 S 的所有 CD 值，所以 (c, k) 也出现在结果中。

因此，R/S 的结果是关系中包含 (a, b) 和 (c, k) 的新关系。

注意#

• 除运算通常用于处理查询，这些查询需要找到“对于所有…”这类的情况。
• SQL 没有直接支持除运算，但可以使用组合查询来实现相同的效果。

3.4.1 实体转换#

1. 强实体转换方法
2. 弱实体转换方法：弱实体对应的关系模式属性由弱实体本身的描述属性加上所依赖的强实体的主键属性构成，关系主键由所依赖的强实体主键和弱实体的部分码组成
3. 复合属性及多值属性处理：
   1. 对于复合属性，应为每个子属性创建一个单独的属性，而不是为复合属性自身创建一个单独的属性
   2. 对于多值属性，应该在 E-R 图建模时转化为弱实体，然后再将弱实体转换为关系模式

3.4.2 联系转换#

1. 二元联系转换规则
2. 多元联系转换规则：3个或三个以上的实体间的一个多元联系在转换为一个关系模式时，与该多元联系相连的各实体的主键及联系本身的属性均转换为称为关系的属性，转换后所得到的关系的主键为各实体主键的组合
3. 多值联系转换规则：对于多值联系，在 E-R 建模时应该设计为弱实体，再转换为关系

3.4.3 关系模式整理#

4.1.1 SQL 的发展及标准#

1. SQL: 结构化查询语言(Structured Query Language)

4.1.2 SQL 的内容及特点#

SQL 具有数据查询(Query)、数据定义(Definition)、数据操纵(Manipulation)和数据控制(Control) 4 种功能

1. 数据定义语言(Data Definition Language, DDL): CREATE, DROP, ALTER
2. 数据操纵语言(Data Manipulation Language, DML): INSERT, UPDATE, DELETE
3. 数据查询语言(Data Query Language, DQL): SELECT
4. 数据控制语言(Data Control Language, DCL): GRANT, REVOKE, COMMIT, ROLLBACK

书写规则：

1. SQL 语句要以分号 “;” 结尾
2. SQL 语句不分大小写（数据本身除外）
3. SQL 语句中使用空格进行风格
4. 常数书写方式是固定的：使用单引号 “ ’ ” 括起来

4.2.1 数据库的组成#

1. 逻辑组成：基表、索引、视图、用户、约束
2. 物理组成：数据文件、日志文件

4.2.2 数据库的创建#

1. 登录本机数据库服务器

   1
   mysql -u root -p [-D db_name]
   

2. 创建数据库：

   1
   CREATE DATABASE|SCHEMA db_name
   2
   [[DEFAULT] CHARACTER SET charset_name]
   3
   [[DEFAULT] COLLATE collation_name];
   

   其中：

   • SCHEMA 与 DATABASE 都指代数据库
   • [DEFAULT] CHARACTER SET charset_name 用于为数据库设置默认字符集
   • [DEFAULT] COLLATE collation_name 用于为数据库的默认字符集设置默认校对规则

4.2.3 数据库的修改#

语法：

1
ALTER DATABASE|SCHEMA db_name
2
[[DEFAULT] CHARACTER SET charset_name]
3
[[DEFAULT] COLLATE collation_name];

eg:

1
ALTER DATABASE|SCHEMA teach
2
DEFAULT CHARACTER SET gbk
3
DEFAULT COLLATE gbk_chinese_ci;

4.2.4 数据库的删除#

语法：

1
DROP DATABASE db_name[, ...n];

4.3.1 常用数据类型#

4.3.2 数据表的创建#

1. 选择操作数据库

   1
   USE db_name;
   

2. 创建数据表

   1
   CREATE TABLE table_name (
   2
       column1 data_type constraint,
   3
       column2 data_type constraint,
   4
       ...
   5
   );
   

4.3.3 数据表的修改#

1. ADD 用于增加新列和完整性约束

   1
   ALTER TABLE table_name
   2
   ADD column_name data_type;
   

2. MODIFY 用于修改字段列的类型

   1
   ALTER TABLE table_name
   2
   MODIFY COLUMN column_name datatype;
   

   限制：

   • 不能将含有空值的列设置为 NOT NULL 约束
   • 若列中已有数据，则不能减少该列的宽度，也不能改变其数据类型

3. RENAME 用于重命名字段列

   1
   ALTER TABLE current_table_name RENAME new_column_name;
   

4. DROP 用于删除列或约束

   1
   ALTER TABLE table_name DROP COLUMN column_name;
   

4.3.4 数据表的删除#

1
DROP TABLE table_name;

4.4.1 NULL/NOT NULL 约束#

1
CREATE TABLE Persons
2
(
3
Id_P int NOT NULL,
4
LastName varchar(255) NOT NULL,
5
FirstName varchar(255),
6
Address varchar(255),
7
City varchar(255)
8
);

4.4.2 主键约束#

1
CREATE TABLE Persons
2
(
3
Id_P int PRIMARY KEY,
4
LastName varchar(255) NOT NULL,
5
FirstName varchar(255),
6
Address varchar(255),
7
City varchar(255),
8
);

1
CREATE TABLE Persons
2
(
3
Id_P int NOT NULL,
4
LastName varchar(255) NOT NULL,
5
FirstName varchar(255),
6
Address varchar(255),
7
City varchar(255),
8
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
9
);

SQL PRIMARY KEY Constraint on ALTER TABLE

1
ALTER TABLE Persons
2
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName);

4.4.3 唯一性约束#

1
CREATE TABLE Persons
2
(
3
Id_P int NOT NULL UNIQUE,
4
LastName varchar(255) NOT NULL,
5
FirstName varchar(255),
6
Address varchar(255),
7
City varchar(255),
8
);

注意：

• 区别与 PRIMARY KEY，UNIQUE 约束项可以为 NULL，并可以在表中定义多个 UNIQUE 约束
• 不能同时使用 PRIMARY KEY 和 UNIQUE 约束

4.4.4 外键约束#

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY

1
CREATE TABLE Orders
2
(
3
Id_O int NOT NULL PRIMARY KEY,
4
OrderNo int NOT NULL,
5
Id_P int,
6
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
7
);

4.4.5 CHECK 约束#

CHECK 约束用于限制列中的值的范围。

1
CREATE TABLE Persons
2
(
3
Id_P int NOT NULL,
4
LastName varchar(255) NOT NULL,
5
FirstName varchar(255),
6
Address varchar(255),
7
City varchar(255),
8
CHECK (Id_P>0)
9
);

4.5.1 INSERT 语句#

INSERT INTO 语句用于向表格中插入新的行。

语法：

1
INSERT INTO table_name [(列1, 列2,...)] VALUES (值1, 值2,....);

插入新的行：

1
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing');

在指定的列中插入数据：

1
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees');

4.5.2 UPDATE 语句#

Update 语句用于修改表中的数据。

语法：

1
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值;

更新某一行的多个属性：

1
UPDATE Person
2
SET Address = 'Zhongshan 23', City = 'Nanjing'
3
WHERE LastName = 'Wilson';

4.5.3 DELETE 语句#

DELETE 语句用于删除表中的行。

语法：

1
DELETE FROM 表名称 WHERE 列名称 = 值;

删除所有行：

1
DELETE FROM table_name;

4.5.4 数据操纵与约束#

1. ON UPDATE 和 ON DELETE

   ON UPDATE 和 ON DELETE 是两个常见的外键约束参数，它们的作用是定义外键关联中当出现更新或删除操作时，希望数据库对外键关联表作何种反应。

   • ON UPDATE: 当与主表相关的数据发生更新时，子表更新时所使用的操作方式。
   • ON DELETE: 当与主表相关的数据主动删除或被动删除时，子表更新操作所使用的操作方式

   ON UPDATE 和 ON DELETE 的选项有以下几个：

   • CASCADE: 级联，当主表中的数据更新或删除时，外键所关联的表的数据也会被更新或删除。具体而言，删除或更新主表数据的操作会自动删除或更新子表数据。但这种操作方式要谨慎，因为容易造成误删误改子表数据的情况。
   • SET NULL: 当主表中的数据更新或删除时，将外键值设为 NULL。这种操作方式较为安全，但会导致外键被设置为 NULL，默认的 NULL 值需要我们进行额外的处理。
   • NO ACTION: 这是默认的操作方式，当主表中的行更新或被删除时，不对从表的外键做任何处理。
   • RESTRICT: RESTRICT 经常也被称为“强制”，它会限制对父表和子表上的行进行更新和删除操作，在这里使用它等同于没有定义任何删除或更新行为。

   实例：

   1
   CREATE TABLE Parent (
   2
    id INT NOT NULL PRIMARY KEY,
   3
    name VARCHAR(50)
   4
   ) ENGINE=INNODB;
   5
   
   6
   CREATE TABLE Child (
   7
    id INT NOT NULL PRIMARY KEY,
   8
    parent_id INT,
   9
    FOREIGN KEY (parent_id) REFERENCES Parent(id) ON UPDATE CASCADE ON DELETE CASCADE
   10
   ) ENGINE=INNODB;
   

5.1.1 SELECT 语句基本结构#

1
SELECT [ ALL | DISTINCT ] select_expr [, ...]
2
[ FROM from_item [, ...] ]
3
[ WHERE condition ]
4
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
5
[ HAVING condition]
6
[ WINDOW window_name AS ( window_definition ) [, ...] ]
7
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
8
[ ORDER BY {column_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
9
[ LIMIT {[offset,] row_count | row_count OFFSET offset}]

5.1.2 无条件查询#

1
SELECT * FROM Persons;
2
SELECT LastName,FirstName FROM Persons;

关键词 DISTINCT 用于返回唯一不同的值。

1
SELECT DISTINCT Company FROM Orders;

指定列/表别名：

1
SELECT LastName AS 性, FirstName AS 名 FROM Persons;
2
SELECT LastName,FirstName FROM Persons AS P;

使用表达式：

1
SELECT CONCAT(LastName, FirstName) AS 姓名 FROM Persons;

5.1.3 查询中函数#

常用的聚合运算函数有：

• ARBITRARY：随机返回一组数据中的任意一个值
• AVG：该函数用于计算平均值
• COUNT：该函数用于计算记录数
• MAX：该函数用于计算最大值
• MIN：该函数用于计算最小值
• SUM：该函数用于计算汇总值
• GROUP_CONCAT：该函数用于将GROUP BY返回结果中属于同一个分组的值连接起来，返回一个字符串结果
• STD或STDDEV：返回数值的样本标准差
• STDDEV_POP：返回数值的总体标准差
• STDDEV_SAMP：返回一组数值（整数、小数或浮点）的样本标准差。
• VAR_POP（标准SQL函数）：返回一组数值（整数、小数或浮点）的总体方差
• VAR_SAMP：返回一组数值（整数、小数或浮点）的样本方差
• current_date: 返回系统时间
• EXTRACT(year | month | day | hour | minute | second FROM DATE)：从日期中提取指定元素
• COALESCE(field, data1, data2, …)：用参数中第一个非 NULL 值替换字段中的 NULL 值

一些有用的特性：

• COUNT(*)（针对全表）将返回表格中所有存在的行的总数包括值为NULL的行
• COUNT(列名)（针对某一列）将返回表格中某一列除去NULL以外的所有行的总数
• COUNT(DISTINCT 列名)，得到的结果将是除去值为NULL和重复数据后的结果。如COUNT(DISTINCT order_status) 计算不重复且不为NULL的订单状态有多少个
• SUM 空行为NULL，SUM(NULL) 为NULL，SUM(0) 为0
• COUNT 空行为0，COUNT(NULL) 为0，COUNT(0) 为0
• 有GROUP BY时，空结果被分为0个分组，求COUNT输出空
• 无GROUP BY时，空结果在默认分组，求COUNT输出0

5.1.4 排序及部分选取#

ORDER BY 关键字用于对结果集进行排序。

语法：

1
SELECT column1, column2, ...
2
FROM table_name
3
ORDER BY column1, column2, ... ASC|DESC;

实例：

1
SELECT * FROM Websites
2
ORDER BY country, alexa DESC;

LIMIT 子句用于限制结果集中的行数，LIMIT 子句可以和 ORDER BY 子句一起使用，对结果集排序后取前多少个，也就是TOP N；还可以指定 offset 来跳过结果集前面的行用于查询结果分页；对于分布式系统来说，如果没有和 ORDER BY 子句配合使用，多次执行返回的数据将会是不同的；LIMIT ALL与省略LIMIT子句相同

用法举例：

• 不限制行数：SELECT * FROM orders LIMIT ALL
• 单独使用：SELECT * FROM orders LIMIT 5
• 和ORDER BY配合使用：SELECT * FROM orders ORDER BY id DESC LIMIT 5
• 指定offeset跳过行（等同于下方语句）：SELECT * FROM orders LIMIT 100, 5
• 使用OFFSET子句跳过行：SELECT * FROM orders OFFSET 100 LIMIT 5

5.1.5 条件查询#

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句。

语法：

1
SELECT column1, column2, ...
2
FROM table_name
3
WHERE condition;

实例：

1
SELECT * FROM Websites WHERE (country='CN' AND country="US");
2
SELECT * FROM Persons WHERE sal between 1500 and 3000;
3
SELECT * from emp where sal in (5000, 3000, 1500);

5.1.6 模糊查询#

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

语法：

1
SELECT column_name(s)
2
FROM table_name
3
WHERE column_name LIKE pattern

在此语法中，LIKE 运算符测试 expression 是否与 pattern 匹配。

SQL 提供了以下通配符来构建模式：

• % 百分号通配符匹配零个、一个或多个字符。
• _ 下划线通配符匹配单个字符。
• 表示某个范围内的字符
• [^] 表示不在某一范围内的字符

实例：

1
SELECT * FROM Persons
2
WHERE City LIKE 'N%';

5.1.7 空值查询#

语法：

1
WHERE column_name IS [NOT] NULL;

5.2.1 无分组聚合查询#

1
SELECT COUNT(*) AS NumberOfOrders FROM Orders;

5.2.2 分组聚合查询#

GROUP BY 子句用于指定根据某列或某几列对查询结果进行分组，通常是为了分组后对数据做聚合(Aggregation)运算；用法举例：

• 按单个字段分组计算总数：SELECT name, COUNT(*) FROM orders GROUP BY name
• 按多个字段分组计算平均数：SELECT name, city, AVG(age) FROM users GROUP BY name, city;
• GROUP BY子句中使用序号来引用所需列：SELECT name, city, AVG(age) FROM users GROUP BY 1, 2;

5.2.3 HAVING 筛选#

HAVING 子句用于指定过滤分组行（GROUP BY 后的行）条件；WHERE 子句会在应用 GROUP BY 之前就过滤掉数据行，而 HAVING 子句则过滤 GROUP BY 创建的分组行，因此其条件表达式中的列必须是 GROUP BY 子句中出现的分组列、处理分组列的函数、处理其他列的聚合函数。用法如下：

• 分组列：... GROUP BY name HAVING ( name = "peter" )
• 处理分组列的函数： ... GROUP BY name HAVING ( UPPER(name) = "PETER" )
• 处理其他列的聚合函数：... GROUP BY name HAVING ( name = "peter" or AVG(age) > 20 )
• 没有GOURP BY时全局聚合函数： ... HAVING ( AVG(age) > 20 )

5.2.4 WHERE 与 HAVING#

5.2.5 聚合查询常见错误#

1. 不能在 WHERE 子句中使用聚合函数：聚合函数只能出现在 SELECT 子句和 HAVING 语句中

5.3.1 交叉查询#

如果在连接两个表时未指定连接条件，则数据库系统会将第一个表的每一行与第二个表的每一行合并。这种连接称为交叉连接或笛卡尔乘积。下面的维恩图说明了交叉联接的工作方式。

交叉联接中的行数是每个表中的行数的乘积。这是交叉连接操作的简单示例。

1
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
2
FROM employees AS t1 CROSS JOIN departments AS t2;

其中定义了以下表：

表： employees

1
+--------+--------------+------------+---------+
2
| emp_id | emp_name     | hire_date  | dept_id |
3
+--------+--------------+------------+---------+
4
|      1 | Ethan Hunt   | 2001-05-01 |       4 |
5
|      2 | Tony Montana | 2002-07-15 |       1 |
6
|      3 | Sarah Connor | 2005-10-18 |       5 |
7
|      4 | Rick Deckard | 2007-01-03 |       3 |
8
|      5 | Martin Blank | 2008-06-24 |    NULL |
9
+--------+--------------+------------+---------+

表： departments

1
+---------+------------------+
2
| dept_id | dept_name        |
3
+---------+------------------+
4
|       1 | Administration   |
5
|       2 | Customer Service |
6
|       3 | Finance          |
7
|       4 | Human Resources  |
8
|       5 | Sales            |
9
+---------+------------------+

执行完上述命令后，您将得到如下结果集：

1
+--------+--------------+------------+------------------+
2
| emp_id | emp_name     | hire_date  | dept_name        |
3
+--------+--------------+------------+------------------+
4
|      1 | Ethan Hunt   | 2001-05-01 | Administration   |
5
|      2 | Tony Montana | 2002-07-15 | Administration   |
6
|      3 | Sarah Connor | 2005-10-18 | Administration   |
7
|      4 | Rick Deckard | 2007-01-03 | Administration   |
8
|      5 | Martin Blank | 2008-06-24 | Administration   |
9
|      1 | Ethan Hunt   | 2001-05-01 | Customer Service |
10
|      2 | Tony Montana | 2002-07-15 | Customer Service |
11
|      3 | Sarah Connor | 2005-10-18 | Customer Service |
12
|      4 | Rick Deckard | 2007-01-03 | Customer Service |
13
|      5 | Martin Blank | 2008-06-24 | Customer Service |
14
|      1 | Ethan Hunt   | 2001-05-01 | Finance          |
15
|      2 | Tony Montana | 2002-07-15 | Finance          |
16
|      3 | Sarah Connor | 2005-10-18 | Finance          |
17
|      4 | Rick Deckard | 2007-01-03 | Finance          |
18
|      5 | Martin Blank | 2008-06-24 | Finance          |
19
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources  |
20
|      2 | Tony Montana | 2002-07-15 | Human Resources  |
21
|      3 | Sarah Connor | 2005-10-18 | Human Resources  |
22
|      4 | Rick Deckard | 2007-01-03 | Human Resources  |
23
|      5 | Martin Blank | 2008-06-24 | Human Resources  |
24
|      1 | Ethan Hunt   | 2001-05-01 | Sales            |
25
|      2 | Tony Montana | 2002-07-15 | Sales            |
26
|      3 | Sarah Connor | 2005-10-18 | Sales            |
27
|      4 | Rick Deckard | 2007-01-03 | Sales            |
28
|      5 | Martin Blank | 2008-06-24 | Sales            |
29
+--------+--------------+------------+------------------+

5.3.2 内连接查询#

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

语法：

1
SELECT table_name1.column_name
2
FROM table_name1
3
[INNER] JOIN table_name2
4
ON table_name1.column_name=table_name2.column_name | condiction

实例：

1
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
2
FROM employees AS t1
3
INNER JOIN departments AS t2
4
ON t1.dept_id = t2.dept_id
5
ORDER BY emp_id;

结果：

1
+--------+--------------+------------+-----------------+
2
| emp_id | emp_name     | hire_date  | dept_name       |
3
+--------+--------------+------------+-----------------+
4
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources |
5
|      2 | Tony Montana | 2002-07-15 | Administration  |
6
|      3 | Sarah Connor | 2005-10-18 | Sales           |
7
|      4 | Rick Deckard | 2007-01-03 | Finance         |
8
+--------+--------------+------------+-----------------+

5.3.3 外连接查询#

在外连接中，参与连接的表有主从之分，以主表的数据去匹配从表中的数据列，并显示全部主表的数据行。符合连接条件的从表中的数据将直接返回到结果集中，对那些不符合连接条件的从表中的列，将被填上 NULL 后，再返回到结果集中。

外连接分为左外连接(LEFT JOIN)和右外连接(RIGHT JOIN)。以主表所在的方向区分，主表在左边，则称为左外连接，右外连接同理。左右两表都是主表也都是从表，称为全外连接(FULL JOIN)

LEFT JOIN

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

语法：

1
SELECT table_name1.column_name
2
FROM table_name1
3
LEFT JOIN table_name2
4
ON table_name1.column_name=table_name2.column_name

实例：

1
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
2
FROM employees AS t1
3
LEFT JOIN departments AS t2
4
ON t1.dept_id = t2.dept_id
5
ORDER BY emp_id;

结果：

1
+--------+--------------+------------+-----------------+
2
| emp_id | emp_name     | hire_date  | dept_name       |
3
+--------+--------------+------------+-----------------+
4
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources |
5
|      2 | Tony Montana | 2002-07-15 | Administration  |
6
|      3 | Sarah Connor | 2005-10-18 | Sales           |
7
|      4 | Rick Deckard | 2007-01-03 | Finance         |
8
|      5 | Martin Blank | 2008-06-24 | NULL            |  <-- 与内连接不同的部分
9
+--------+--------------+------------+-----------------+

RIGHT JOIN

与左外连接同理，执行以下示例：

1
SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
2
FROM employees AS t1
3
RIGHT JOIN departments AS t2
4
ON t1.dept_id = t2.dept_id
5
ORDER BY dept_name;

结果：

1
+--------+--------------+------------+------------------+
2
| emp_id | emp_name     | hire_date  | dept_name        |
3
+--------+--------------+------------+------------------+
4
|      2 | Tony Montana | 2002-07-15 | Administration   |
5
|   NULL | NULL         | NULL       | Customer Service |
6
|      4 | Rick Deckard | 2007-01-03 | Finance          |
7
|      1 | Ethan Hunt   | 2001-05-01 | Human Resources  |
8
|      3 | Sarah Connor | 2005-10-18 | Sales            |
9
+--------+--------------+------------+------------------+

5.3.4 自连接查询#

自连接是一种比较同一表内行的连接。自连接使用内连接、左连接或右连接将一个表连接到其自身。它使用表别名将同一个表在同一个查询中视为不同的表。

语法：

1
SELECT select_list
2
FROM table1 t1
3
INNER JOIN table1 t2
4
ON t1.column1 = t2.column2;

5.4.1 单值子查询#

标量子查询是和外围查询不相关且只返回零行或一行一列的子查询，标量子查询产生多个行或多个列是错误的，如果没有产生行则返回值为NULL。标量子查询通常用于和外围查询的某个列做比较，支持 = > < >= <= <> != <=> 等比较运算符：

1
SELECT name FROM nation
2
WHERE regionkey = (SELECT max(regionkey) FROM region)

5.4.2 多值子查询#

如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入谓词 ANY 或 ALL。ANY关键字必须跟在比较操作符后面，意思是“如果子查询返回的列中的任何值的比较结果为TRUE，则返回TRUE”，SOME 是ANY的别名 :

1
SELECT name FROM nation WHERE regionkey = ANY (SELECT regionkey FROM region); ## 子查询里 = ANY 和 IN 等价
2
SELECT name FROM nation WHERE regionkey > SOME (SELECT regionkey FROM region);

ALL关键字也必须跟在比较操作符后面，意思是“如果子查询返回的列中的所有值的比较结果都是TRUE，则返回TRUE。”例如：

1
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
2

3
## NOT IN 与 <> ALL 等价
4
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
5
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

5.4.3 子查询与内连接#

1. 单字段子查询与内连接

   所谓单字段子查询就是子查询中 SELECT 子句只有一个字段的子查询

   1
   SELECT t1.tno, t1.tname
   2
   FROM teacher t1
   3
   JOIN teacher t2
   4
   ON t1.prof = t2.prof
   5
   WHERE t2.name = '王刚';
   

2. 多字段子查询与内连接

   多字段子查询就是子查询中 SELECT 子句有多个字段的子查询

   1
   SELECT DISTINCT tno
   2
   FROM tc
   3
   WHERE (cno, tchour) IN (
   4
   SELECT cn0, tchour
   5
   FROM tc
   6
   WHERE tno = (
   7
   SELECT tno
   8
   FROM teacher
   9
   WHERE tname='王刚'));
   

5.5.1 普通相关子查询#

1
SELECT tname, sal
2
FROM teacher t1
3
WHERE sal > (SELECT AVG(sal)
4
    FROM teacher t2
5
    WHERE t2.dno = t1.dno);

5.5.2 EXISTS 子查询#

如果子查询返回任何行，EXISTS子查询为TRUE，NOT EXISTS子查询为FALSE

以下 SQL 语句返回”在城市商店表中有记录的所有商店类型”：

1
SELECT DISTINCT store_type
2
FROM stores
3
WHERE EXISTS (
4
    SELECT *
5
    FROM cities_stores
6
    WHERE cities_stores.store_type = stores.store_type
7
);

在子查询部分: 检查 cities_stores 表中是否存在与当前 stores 表行的 store_type 相匹配的记录

对于 stores 表的每一行，子查询都会执行一次，检查该商店类型是否在 cities_stores 表中存在。如果存在至少一条匹配记录，EXISTS 返回 TRUE，该商店类型就会被包含在最终结果中。

5.6.1 SELECT 中的子查询#

在 SELECT 子句中的子查询一般是返回一个值用于表达式的计算。例如，查找每个员工所在的部门名称：

1
SELECT e.ename, e.deptno,
2
      (SELECT dname FROM dept d WHERE d.deptno = e.deptno) AS dname
3
FROM emp e;

5.6.2 FROM 中的子查询#

在FROM子句中使用子查询，可以将子查询的结果作为一个临时表来使用。例如，找出每个部门的平均薪资等级：

1
SELECT t.deptno, t.avgsal, s.grade
2
FROM (SELECT deptno, AVG(sal) AS avgsal FROM emp GROUP BY deptno) t
3
JOIN salgrade s ON t.avgsal BETWEEN s.losal AND s.hisal;

5.6.3 HAVING 中的子查询#

统计每个部门的平均工资，并返回平均工资高于公司平均工资的部门数据：

1
SELECT department, AVG(salary)
2
FROM employees
3
GROUP BY department
4
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

5.7.1 INSERT 与子查询#

1
INSERT INTO premium_customers
2
SELECT * FROM customers
3
WHERE cust_id IN (
4
    SELECT DISTINCT cust_id
5
    FROM orders
6
    WHERE order_value > 5000);

上面的语句将通过使用子查询返回的数据，将高级客户的记录插入名为 premium_customers 的表中。 这里的高级客户是已下订单价值超过5000美元的客户。

5.7.2 UPDATE 与子查询#

1
UPDATE orders
2
SET order_value = order_value + 10
3
WHERE cust_id IN (
4
    SELECT cust_id
5
    FROM customers
6
    WHERE postal_code = 75016);

通过将当前订单值增加10美元，上述语句将更新订单（orders）表中邮政编码为75016的地区的客户的订单值。

5.7.3 DELETE 与子查询#

1
DELETE FROM orders
2
WHERE order_id IN (
3
    SELECT order_id
4
    FROM order_details
5
    WHERE product_id = 5);

上面示例中的SQL语句将从包含 product_id 为 5 的产品的订单表中删除这些订单。

5.8.1 并集查询#

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。它可以从多个表中选择数据，并将结果集组合成一个结果集。使用 UNION 时，每个 SELECT 语句必须具有相同数量的列，且对应列的数据类型必须相似。

语法：

1
SELECT column1, column2, ...
2
FROM table1
3
UNION
4
SELECT column1, column2, ...
5
FROM table2;

下面的 SQL 语句从 “Websites” 和 “apps” 表中选取所有不同的country（只有不同的值）：

1
SELECT country
2
FROM Websites
3
UNION
4
SELECT country
5
FROM apps
6
ORDER BY country;

5.8.2 交集查询#

INTERSECT 运算 返回查询结果中相同的部分既他们的交集

查询同时选修了180101和180102号课程的学生学号、课程号和成绩：

1
(SELECT  学号, 课程号, 成绩
2
FROM   学习
3
WHERE 课程号='180101')
4
INTERSECT
5
(SELECT 学号, 课程号, 成绩
6
FROM 学习
7
WHERE 课程号='180102');

INTERSECT 运算自动去除重复，如果想保留所有的重复，必须用 INTERSECT ALL 代替 INTERSECT，结果中出现的重复元组数等于两集合出现的重复元组数里较少的那个。

5.8.3 差集查询#

EXCEPT 返回两个结果集的差（即从左查询中返回右查询没有找到的所有非重复值）。

查询选修了 180101 号课程的学生中没有选修 180102 号课程的学生学号、课程号和成绩。

1
(SELECT  学号, 课程号, 成绩
2
FROM   学习
3
WHERE  课程号='180101')
4
EXCEPT
5
(SELECT 学号, 课程号, 成绩
6
FROM 学习
7
WHERE   课程号='180102');

EXCEPT 运算自动去除重复，如果想保留所有的重复，必须用 EXCEPT ALL 代替 EXCEPT，结果中出现的重复元组数等于两集合出现的重复元组数之差（前提是差是正值）。

5.10.1 简单型 CASE 表达式#

1
CASE column_name
2
   WHEN value1 THEN result1
3
   WHEN value2 THEN result2
4
   ELSE default_value
5
END

5.10.2 搜索型 CASE 表达式#

1
CASE
2
   WHEN condition1 THEN result1
3
   WHEN condition2 THEN result2
4
   ELSE default_value
5
END

实例：

1
SELECT id, name, (
2
   CASE
3
       WHEN age > 18 THEN '成年人'
4
       WHEN age BETWEEN 6 AND 18 THEN '未成年人'
5
       ELSE '婴幼儿'
6
   END) AS age_group
7
FROM people;

6.1.1 视图概述#

视图是虚表，是从一个或几个基本表（或视图）中导出的表，在数据库系统表中仅存放视图的定义，不存放视图对应的数据。视图的主要作用如下：

1. 简化用户的操作
2. 使用户能以多种角度看待同一数据库模式
3. 提供一定程度的逻辑独立性
4. 能够对数据库中的机密数据提供一定程度的安全保护
5. 适当地利用视图可以更清晰地表达查询
6. 视图可用于数据集成

6.1.2 创建视图#

语法：

1
CREATE VIEW view_name AS
2
SELECT column1, column2, ...
3
FROM table_name
4
WHERE condition;

6.1.3 使用视图#

从用户地角度看，查询视图与查询基本表的方式是一样的。在查询视图时会将视图的定义子查询嵌入对视图的查询语句中进行执行，并获取数据

6.1.4 视图更新#

在 SQL 中，你不能直接使用 UPDATE 语句来更新视图，因为视图是基于查询结果生成的虚拟表，而不是实际存储数据的表。

更新视图的实质是通过更新视图所基于的表中的数据，然后视图会反映这些变化。

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。

1
UPDATE table_name
2
SET column1 = value1, column2 = value2, ...
3
WHERE condition;

现在，我们希望向 “Current Product List” 视图添加 “Category” 列。我们将通过下列 SQL 更新视图：

举例来说，如果你有一个名为 high_salary_employees 的视图，显示工资高于 50000 的员工信息，而这个视图基于 employees 表的查询结果，你可以通过以下步骤来更新数据：

1
-- 步骤 1: 更新 employees 表中的数据
2
UPDATE employees
3
SET salary = 60000
4
WHERE employee_id = 1001;
5

6
-- 步骤 2: 查询更新后的高工资员工视图
7
SELECT *
8
FROM high_salary_employees;

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如下情况时，视图不支持更新操作：

• 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持 INSERT 和 DELETE 操作；
• 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持 INSERT 操作；
• 在定义视图的 SELECT 语句中使用了 JOIN 联合查询 ，视图将不支持 INSERT 和 DELETE 操作；
• 在定义视图的 SELECT 语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT，也不支持 UPDATE 使用了数学表达式、子查询的字段值；
• 在定义视图的 SELECT 语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、 UNION 等，视图将不支持 INSERT、UPDATE、DELETE；
• 在定义视图的 SELECT 语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持 INSERT、UPDATE、DELETE；
• 视图定义基于一个不可更新视图 ；
• 常量视图。

6.1.5 修改视图#

您可以使用下面的语法来更新视图：

1
CREATE OR REPLACE VIEW view_name AS
2
SELECT column_name(s)
3
FROM table_name
4
WHERE condition

1
ALTER VIEW view_name AS
2
SELECT column_name(s)
3
FROM table_name
4
WHERE condition;

6.1.6 删除视图#

删除视图只是删除视图的定义，并不会删除基表的数据。

1
DROP VIEW IF EXISTS 视图名称;
2
DROP VIEW IF EXISTS 视图名称1, 视图名称2, 视图名称3, ...;

6.2.1 文件中记录组织#

数据库中在物理中主要由数据文件和事务日志文件组成，其中数据文件中存放的是数据表中的记录。

文件中组织记录的方法：

1. 堆文件组织

   一条记录可以放在文件中的任何地方，只要那个地方有空间存放该记录。文件中的记录是没有顺序的，是基本按照添加顺序自由堆积起来的

2. 顺序文件组织

   顺序文件是为了高效地按某个搜索码值（是一个属性或属性集合）的顺序有序处理记录而设计的。为了高效的按搜索码值的顺序获取记录，通常通过指针将操作系统文件块（存放文件记录的硬盘块）逻辑上有序地链接起来

3. 多表聚集文件组织

   多表聚集文件组织是一种在每一块中存储两个或者更多个关系的相关记录的文件结构。这样的文件组织允许我们使用一次块的读操作来读取满足连接条件的记录。

   对于多表聚簇，加速了对特定表连接的处理，但会导致其它类型的查询变慢。

4. B+ 树文件组织

   B+树是一种多路平衡查找树，广泛应用于数据库和文件系统中，MySQL的InnoDB存储引擎主要使用B+树作为索引的数据结构。相比其他数据结构（如B树、红黑树），B+树在数据库场景下有显著优势，尤其适合范围查询和高效的磁盘I/O操作。

   B+树的物理存储

   为了理解B+树的物理存储，我们需要结合MySQL的InnoDB存储引擎，分析其在磁盘和内存中的组织方式。

   InnoDB的页面（Page）与Buffer Pool

   InnoDB将数据和索引存储在页面（Page）中，默认页面大小为16KB。页面是InnoDB管理存储的基本单位，可以存储：

   • 表数据（数据页）
   • 索引数据（索引页）
   • 元数据或其他信息

   这些页面存储在磁盘上，但查询时会加载到内存中的Buffer Pool（缓冲池）中，以减少磁盘I/O。B+树的每个节点对应一个页面，无论是根节点、中间节点还是叶子节点。

   关键点：

   • B+树的节点不是抽象的概念，而是物理上存储在磁盘的页面。
   • Buffer Pool是内存中的缓存，存储最近访问的页面，B+树的操作（如查找、插入）会在Buffer Pool中进行，必要时再与磁盘同步。

   2.2 节点的物理存储内容

   B+树的每个节点是一个页面，页面内部存储的内容根据节点类型不同而异：

   根节点/中间节点

   存储内容：

   • 键（Keys） ：索引列的值（例如，主键或索引字段）。
   • 指针（Pointers） ：指向子节点的页面地址（逻辑上是子节点的物理位置）。
   • 元数据：页面的一些控制信息，如页面类型、键数量等。

   特点：

   • 不存储实际数据，只存储键和指针。
   • 一个页面可以存储数百到数千个键（取决于键的大小和页面大小）。
   • 键是按顺序排列的，便于二分查找。

   叶子节点

   存储内容：

   • 键（Keys） ：索引列的值。
   • 数据（Data） ：
     • 对于聚簇索引（如主键索引），叶子节点直接存储整行数据。
     • 对于辅助索引（非主键索引），叶子节点存储索引列值和主键值（需通过主键回表查询完整数据）。
   • 指针：指向前后叶子节点的指针（形成双向链表）。
   • 元数据：页面控制信息。

   特点：

   • 叶子节点存储实际数据或数据引用。
   • 所有叶子节点通过指针连接成双向链表，支持范围查询。

   形象化描述：

   想象一本书：

   • 书的目录类似根节点和中间节点，目录页只记录章节标题（键）和页面编号（指针）。
   • 书的正文页类似叶子节点，存储实际内容（数据）。
   • 每页的大小固定（16KB），目录页能列出很多章节，内容页能存很多文字。
   • 正文页之间有“上一页”“下一页”的链接（双向链表），方便连续阅读。

   子节点的结构（形象化）

   每个子节点（页面）的内部结构可以看作一个有序数组 + 指针的组合：

   • 中间节点：像一个“路标”，存储一组键（如[10, 20, 30]）和指向子节点的指针（如[Page1, Page2, Page3, Page4]）。键用于判断查询路径，例如值15会走Page2。
   • 叶子节点：像一个“数据盒”，存储一组键值对（如[(1, Row1), (2, Row2), (3, Row3)]）和指向相邻叶子节点的指针。

   物理结构示意图（以页面为单位）：

   1
   [页面头部 | 键1 | 指针1 | 键2 | 指针2 | ... | 键N | 指针N | 页面尾部]
   

   • 页面头部：存储元数据，如键数量、页面类型。
   • 键和指针：按顺序排列，键用于查找，指针用于导航。
   • 页面尾部：可能包含校验信息或空闲空间。

   形象比喻：

   • 每个页面像一个“抽屉”，抽屉里有多个“格子”，格子里放着键和指针（或数据）。
   • 中间节点的抽屉装的是“索引卡”，记录键和下一级抽屉的地址。
   • 叶子节点的抽屉装的是“数据卡”，记录实际数据。

5. 散列文件组织

   在散列的描述中，用散列桶来表示可以存储一条或多条记录的一个存储单位，通常一个桶就是一个硬盘块。通过散列函数计算搜索码的值，并根据该散列值来决定包含该搜索码值来决定包含该搜索码值的记录应该存储在哪个桶中

6.2.2 索引概述#

1. 索引的概念

   MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。

   MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

   MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。

   打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。

   拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

   索引分单列索引和组合索引：

   • 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
   • 组合索引，即一个索引包含多个列。

   创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

   实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

   索引虽然能够提高查询性能，但也需要注意以下几点：

   • 索引需要占用额外的存储空间。
   • 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。
   • 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。

2. 索引数据存储与组织

   逻辑上索引数据虽然与数据表数据分开存储，但是也需要存储在数据库文件中。从索引数据的存储结构上看，数据库的索引一般是按照 B+ 树结构来组织存储的，但也有 Hash 索引等其他类型。

6.2.3 索引的类型#

1. 聚集索引和非聚集索引

   聚集索引（Clustered Index）

   聚集索引决定了表中数据的物理存储顺序。在聚集索引中，数据行的物理顺序与索引键值的逻辑顺序是一致的。这意味着，聚集索引的叶节点直接存储了数据行本身。由于这种存储方式，一个表只能有一个聚集索引，因为数据不能同时按照两个不同的顺序进行物理存储。在SQL Server中，默认情况下主键会被创建为聚集索引，而在MySQL中，主键本身就是聚集索引。

   非聚集索引（Non-Clustered Index）

   与聚集索引不同，非聚集索引中数据行的物理存储顺序与索引键值的逻辑顺序不一致。非聚集索引的叶节点包含索引键值和指向数据行的指针，而不是数据行本身。这意味着，使用非聚集索引进行查询时，可能需要进行两次查找：首先在索引中查找到键值，然后通过指针查找到数据行。一个表可以有多个非聚集索引，因为它们不影响数据的物理存储顺序。

2. 单列索引与复合索引

   索引字段仅包括一个字段的索引为单列索引，索引字段包括多个字段的索引为复合索引。复合字段中各个字段的排列顺序不同，产生的索引也不同，字段排列顺序影响索引的效果，因此设计复合索引时要特别注意各字段的顺序。

3. 唯一索引

   唯一索引确保表中索引列的任意两行没有相同的值（不允许重复值）。创建 PRIMARY KEY 或 UNIQUE 约束时会为指定列自动创建唯一索引

4. 全文索引

   全文索引（Full-Text Search）是一种用于在大量文本数据中进行高效搜索的技术。它通过基于相似度的查询，而不是精确数值比较，来查找文本中的相关信息。相比于使用 LIKE + % 的模糊匹配，全文索引在处理大量数据时速度更快。

5. 函数索引

   函数索引是对表中的列执行表达式计算后的结构进行索引，而不是对列或列前缀值。使用函数索引可以对未直接存储在表中的数据进行索引。

6.2.4 索引的创建#

使用 CREATE INDEX 语句可以创建普通索引。

语法：

1
CREATE INDEX index_name
2
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

以下实例假设我们有一个名为 students 的表，包含 id、name 和 age 列，我们将在 name 列上创建一个普通索引。

1
CREATE INDEX idx_name ON students (name);

6.2.5 索引的使用#

在创建索引时一般遵循以下原则：

1. 经常用于查询且更新较少的表才适合创建索引
2. 数据量越大的表，越适合创建索引
3. 每次查询的数据比例越少，越适合创建索引
4. 在取值重复率大的字段上不要创建默认的 B 树索引
5. 要为经常需要排序、分组和连接查询字段建立索引

在 MySQL 可以使用 EXPLAIN + SELECT 监控索引使用情况，SELECT 不会真正执行

执行后，数据库将返回一个表格，显示查询的执行计划，包括以下关键列：

• id: 查询中每个子查询或表的执行顺序标识符。id 值越大，优先级越高。
• select_type: 查询类型，描述查询中每个 SELECT 子句的性质。常见值包括 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
• table: 查询中涉及的表名。
• type: 访问类型，表示查询的执行方式，通常是性能的关键指标。类型从最优到最差依次为system、const、eq_ref、ref、range、index、ALL。
• key: 使用的索引，如果没有使用索引，则为 NULL。
• rows: 预估扫描的行数，越小越好，表示查询性能越高。

6.2.6 索引的删除#

语法：

1
DROP INDEX index_name ON table_name;

7.2.1 函数依赖的概念#

定义：设 $R(U)$ 是属性集合 $U = { A_1, A_2, ... , A_n }$ 上的一个关系模式，X, Y 是 U 上的两个子集，若对 $R(U)$ 的任意一个可能的关系 r ，r 中不可能有两个元组满足在 X 中的属性值相等而在 Y 中的属性值不等，则称 “ X 函数决定 Y ” 或 “ Y函数依赖于X ” ，记作 $X \to Y$

白话：在一个关系 R 中，属性（组） Y 的值是由属性（组） X 的值所决定的 。又可以说，在关系 R 中，若两个元组的 X 属性值相同，那么这两个元组的 Y 属性值也相同。

对于函数依赖，需要做以下说明：

1. 函数依赖针对结构上约束，而非个别实例数据

2. 函数依赖与具体业务应用有关

3. 函数依赖关系的存在与时间无关

4. 平凡的函数依赖与非平凡的函数依赖

   函数依赖 分为 非平凡函数依赖 和 平凡函数依赖。 非平凡函数依赖：对 $X \to Y$ ，但 $Y \nsubseteq X$ ，则称 $X \to Y$ 为非平凡的函数依赖。也就是说 X 决定 Y，但是 Y 不在 X 中，这种叫做非平凡函数依赖。否则就是平凡函数依赖。

7.2.2 函数依赖的分类#

1. 完全函数依赖和部分函数依赖

   定义：在关系 $R(U)$ 中， 若 $X \to Y$ ，且对于 X 的任何真子集 X‘ 都有 $X’ \not \to Y$，则称 Y 完全函数依赖于 X，记为：$X \xrightarrow{f} Y$ 。否则称 Y函数部分依赖于 X，记为 $X \xrightarrow{p} Y$。

   白话：完全函数依赖就是说 属性组 X 的所有属性一起（即完全）才能决定属性 Y，去掉任何一个属性都不行。相反的，部分函数依赖就是说 属性组 X 中的 部分属性就可以决定 Y ，用不着全部。

   Insight：如果 属性（组）Y 部分函数依赖于 属性组 X，则说明属性组 X 中存在着对属性（组） Y 的非受控冗余。在设计数据库时应避免这种情况。（对应着 2NF ）。

   举例：

   $U = \{\text{学号}, \text{姓名}, \text{年龄}, \text{班号},\text{班长},\text{课号}, \text{成绩}\}$

   $\{\text{学号}，\text{课号}\} \xrightarrow{f} U$

   $\{\text{学号}，\text{课号}\} \xrightarrow{p} \{\text{姓名}，\text{年龄}\}$

   $\{\text{学号}，\text{课号}\} \xrightarrow{p} \{\text{成绩}\}$

   解释：单个学号可以决定性别和年龄，单个学号就可以决定成绩。因此姓名，年龄和成绩对学号，课号是部分函数依赖。

2. 传递函数依赖

   定义: 在 R(U) 中, 若 $X \rightarrow Y$, $Y \rightarrow Z$, 且 $Y \not\subset X$, $Z \not\subset Y$, $Z \not\subset X$, $Y \not\rightarrow X$, 则称 Z 传递函数依赖于 X。

   白话: 如果 X 函数决定 Y, Y 函数决定 Z, 且 Y、Z 都不包含于 X, Z 不包含于 Y, Y 不能决定 X, 则称 Z 传递函数依赖于 X。注意!: 若 $X \rightarrow Y$, $Y \rightarrow Z$, 可以得到 $X \rightarrow Z$, 但不能说 Z 传递函数依赖于 X。

   Insight: 传递函数依赖存在着非受控冗余。

   举例:

   学生( 学号, 姓名, 系号, 系主任 )

   • {学号} $\rightarrow$ {系号}
   • {系号} $\rightarrow$ {系主任}
   • {学号} $\rightarrow$ {系主任}

   “系主任” 是传递依赖于 “学号” 的。

7.2.3 函数依赖集#

1. 逻辑蕴含

   定义: 设 F 是关系 R(U) 中的一个函数依赖集合, X、Y 是 R 的属性子集, 如果能从 F 这个函数依赖集合中推导出 $X \rightarrow Y$, 则称 F 逻辑蕴含 $X \rightarrow Y$, 或者说 $X \rightarrow Y$ 是 F 的逻辑蕴含。记作 $F \models X \rightarrow Y$。

2. 闭包

   定义: 被 F 逻辑蕴含的所有函数依赖集合称为 F 的闭包, 记作 $F^+$。

   白话: 也就是说, 能从 函数依赖集 F 中推导出的所有函数依赖组成的集合, 称为 F 的闭包。(学过泛函的话应该能感觉到有种完备的概念)。

   如果 $F^+ = F$, 则称 F 是一个 全 函数依赖族。（函数依赖完备集）。

7.3.1 属性集闭包#

1. 属性集闭包

   给定：

   • 一个属性集 X
   • 一组函数依赖 F

   属性集闭包 X⁺ = 在依赖集 F 下，从 X 能推导出的所有属性 的集合。

   常用于：判断某个函数依赖是否成立

   要判断 X → A 是否成立，只需检查：如果 A ∈ X⁺ 那么依赖成立

2. 属性集闭包求解算法

   计算 X⁺ 的步骤如下：

   a. 初始化：

   1
   X⁺ = X
   

   b. 反复应用函数依赖 F：

   对于每条依赖 Y → Z：

   • 若 Y ⊆ X⁺ 则把 Z 也加到 X⁺ 中：

     1
     X⁺ = X⁺ ∪ Z
     

   c. 直到 X⁺ 不再变化，即达到闭包

   这是一个典型的迭代算法。

   实例

   给定关系 R(A, B, C, D) 函数依赖集 F：

   1. A → B
   2. B → C
   3. A → D

   求 A⁺ ?

   步骤：

   • 初始： A⁺ = {A}
   • 看依赖：
     • A → B A ∈ A⁺ → 加上 B A⁺ = {A, B}
     • B → C B ∈ A⁺ → 加上 C A⁺ = {A, B, C}
     • A → D A ∈ A⁺ → 加上 D A⁺ = {A, B, C, D}

   结果：

   1
   A⁺ = {A, B, C, D}
   

   说明 A 是这个关系的一个 键（至少是超键）。

7.3.2 候选键求解#

1. 候选键的定义

   在关系模式 R 中，如果一个属性集 K 具有以下两个性质：

   1. 唯一性（Superkey）

      K 的属性集闭包 K⁺ = R 的所有属性

      → 也就是说，K 能唯一标识一个元组。

   2. 最小性（Minimality）

      K 的任何真子集都 不是 超键（即去掉任一属性，闭包就无法覆盖全体属性）。

   那么 K 就是候选键。

2. 快速求解候选键的一个充分条件

   四类属性：L、R、N、LR 类

   给定关系模式属性集 R 和函数依赖集 F，对每个属性，将它在依赖中的出现位置分类：

   ① L 类（Left-only）

   • 属性 只出现在函数依赖的左边（LHS）
   • 从来不出现在右边（RHS）

   📌 特点： 它不能被其他属性推出，因此 必须包含在每一个候选键中。

   ② R 类（Right-only）

   • 属性 只出现在 RHS
   • 从不出现在 LHS

   📌 特点： 它必定由其他属性推导出来，因此 不会出现在任何候选键中。

   ③ N 类（Not involved）

   • 属性 既不出现在左边，也不出现在右边

   📌 特点： 没有任何依赖作用于它，也不能推出别人。 因此： 它必须包含在候选键中（否则无法确定它的取值）。

   ④ LR 类

   • 属性 同时出现在 LHS 和 RHS

   📌 特点： 既能推出别人，也能被别人推出。 是否属于候选键需要进一步分析，不能直接判断。 通常通过补全 L∪N 的闭包检查。

   ---

   ⭐ 快速求候选键的充分条件（核心）

   设：

   • L 类属性集合为 L
   • N 类属性集合为 N
   • LR 类属性集合为 LR
   • R 类属性集合为 R

   那么有一个重要的结论（充分条件）：

   ✔️ 候选键 ⊇ (L ∪ N)

   换句话说：

   • 所有 L 类属性（左边专属）必须在候选键中
   • 所有 N 类属性（完全不参与依赖）也必须在候选键中
   • R 类永远不在候选键
   • 真正需要做选择的，只有 LR 类属性

   ---

   ⭐ 求候选键的快速方法

   Step 1：把属性分成 L、R、N、LR 四类

   （只需检查所有函数依赖的左右两侧）

   Step 2：候选键至少包含 L ∪ N

   这是一个必要（且很容易验证的）条件。

   Step 3：判断 L ∪ N 是否已经是超键

   计算闭包 (L ∪ N)⁺ 是否覆盖所有属性：

   ★ 若覆盖 R 全部属性 →

   L ∪ N 即为候选键

   ★ 若未覆盖 →

   需要从 LR 中挑选尽量少的属性，使闭包覆盖全属性。

   这样就避免了盲目枚举所有子集。

   示例演示

   给定关系

   R(A, B, C, D, E)

   依赖集

   1. A → B
   2. C → D
   3. BD → E

   Step 1：分类

   属性	出现在 LHS？	出现在 RHS？	类别
   A	✓	✗	L 类
   B	✓	✓	LR 类
   C	✓	✗	L 类
   D	✓	✓	LR 类
   E	✗	✓	R 类

   没有 N 类属性。

   因此：

   • L = {A, C}
   • LR = {B, D}
   • R = {E}
   • N = ∅

   Step 2：候选键至少包含 L ∪ N = {A, C}

   初始候选键候选集： K = {A, C}

   Step 3：判断 K 是否是超键

   求：

   (A, C)⁺：

   • A → B
   • C → D
   • BD → E

   结果： (A, C)⁺ = {A, C, B, D, E} = 所有属性

   ✔️ 覆盖全关系 → {A, C} 是候选键

   最终候选键：{A, C}

   因为已经覆盖了全部属性，不需要加入 LR 的 B 或 D。

   总结

   类别	定义	是否必须在候选键中？
   L 类	仅在左边	✔ 必须包含
   N 类	不出现在任何依赖中	✔ 必须包含
   R 类	仅在右边	✘ 不可能在候选键中
   LR 类	左右都出现	❓ 需要视情况而定

   ★ 核心充分条件

   候选键 = (L ∪ N ∪ LR_sub) 其中 LR_sub 是从 LR 中选出的最小集合，使闭包覆盖全属性。

   这个方法是“充分条件”，因为它可以快速锁定候选键候选范围，避免暴力枚举。

3. 多属性函数依赖集候选键的求解算法

   输入：

   • 关系模式：R = {A₁, A₂, …, Aₙ}
   • 函数依赖集：F = {X₁→Y₁, X₂→Y₂, …}

   输出：

   • 所有候选键（可能有多个）

   求候选键的一般思路

   候选键 = 使闭包覆盖关系模式所有属性的最小属性集

   因此算法分为两步：

   1. 找到所有超键 (superkeys)：X⁺ = R
   2. 从中选出最小的（真子集不再是超键）

   但是直接枚举所有属性的子集 = 2ⁿ 复杂度，会爆炸。

   因此，求解算法必须 大幅缩小搜索空间。

   求候选键的标准算法（系统化步骤）

   这个算法是数据库教材中最经典、最通用的算法。

   STEP 1：属性分类（L / R / N / LR 分类）

   把属性分成：

   • L（Left-only）：只出现在 FD 左边 → 必须属于候选键
   • N（No-appearance）：不在任何 FD 中出现 → 必须属于候选键
   • R（Right-only）：只在右边 → 绝不属于候选键
   • LR（Both）：左右都在 → 候选键中可选可不选

   于是候选键必含部分为：

   1
   Base = L ∪ N
   

   搜索空间只需要在 LR 类属性中组合。

   （这是避免 2ⁿ 暴力搜索的关键技术）

   STEP 2：检查 Base 是否已经是超键

   计算 Base 的闭包 Base⁺：

   • 若 Base⁺ = R → Base 为候选键 → 无需扩展
   • 否则 → 必须加入 LR 属性

   STEP 3：搜索 LR 属性的最小扩展，直到闭包覆盖 R

   设 LR = {a₁, a₂, …, aₖ}

   要从中选最小的子集 S，使得：

   1
   (Base ∪ S)⁺ = R
   

   为了保持“最小性”，使用 按规模递增的组合搜索：

   对于 i = 0 → k：

   • 枚举 LR 中所有大小为 i 的子集 C
   • 若 (Base ∪ C)⁺ = R，则 C 最小 → 找到候选键

   所有满足条件的组合都要记录（因为候选键可能有多个）。

   注意：

   • 从小到大组合，是为了保证“最小性”
   • 不会产生冗余，因为 Base 固定

   STEP 4：检查冗余（确保最小化）

   对每个找到的候选键 K：

   对 K 中每个属性 A：

   • 若 (K – {A})⁺ = R，则 K 不是候选键（不最小），删除

   通常在步骤 3 已避免了冗余，但严格性要求加此步骤。

   STEP 5：输出所有候选键

   示例：完整演示

   关系模式： R = {A, B, C, D, E}

   函数依赖集： F =

   1. A → BC
   2. C → D
   3. BD → E

   STEP 1：分类

   出现情况：

   属性	LHS	RHS	类别
   A	✓	✗	L
   B	✓	✓	LR
   C	✓	✓	LR
   D	✓	✓	LR
   E	✗	✓	R

   分类结果：

   • L = {A}
   • N = ∅
   • LR = {B, C, D}
   • R = {E}

   Base = {A}

   STEP 2：Base⁺ ?

   A⁺ = {A, B, C, D, E} → = R

   所以 Base 本身就是超键 → 无需扩展 LR。

   候选键 = {A}

   （如果 Base 不是超键，才会进入 LR 组合搜索）

   完整总结：求候选键算法（最高效版本）

   1. 属性分类为 L / R / N / LR
   2. 候选键必包含 Base = L ∪ N
   3. 若 Base⁺ = R → Base 是候选键
   4. 否则，从 LR 中逐步加入属性直到闭包 = R
   5. 对所有找到的候选键做最小性检查
   6. 输出所有候选键

   这是数据库理论中求候选键最常用的 通用且高效 算法。

7.4.1 规范化概述#

在关系数据库设计中，规范化是一个关键的过程，用于减少数据冗余并避免更新异常。规范化是通过应用一系列称为“范式”的标准来完成的，它们定义了关系模式的不同级别的规范化要求。

关系数据库中的关系是要满足一定规范化要求的，对于不同的规范化程度，可以用“范式”来衡量，记为 xNF。范式是表示关系模式的级别，是衡量关系模式规范化程度的标准，达到范式的关系才是规范化的。满足最低要求的为第一范式，简称 1NF。在第一范式的基础上，进一步满足一些要求的为第二范式，简称 2NF，…，依此类推，各种范式之间的联系是

$5NF \sub 4NF \sub BCNF \sub 3NF \sub 2NF \sub 1NF$

规范化的目的：

减少数据冗余：避免同一数据在数据库中多次出现，减少存储空间的浪费。 避免更新异常：更新、删除或插入操作时防止数据不一致性。 提高查询效率：通过适当的分解，可以提高查询的效率和灵活性。

规范化过程

低一级范式的关系模式通过分解可以转换为若干个高一级范式的关系模式集合。这个过程称为规范化，其目标通常是达到第三范式或BCNF，以确保数据模型的有效性和高效性。

7.4.2 1NF 及其规范化#

第一范式（1NF）是关系数据库模式设计中的最基本要求。它确保了数据库的基础结构是规范的，并避免了复杂的数据组织形式。

定义

第一范式（1NF） 规定了关系模式的所有属性都必须是不可分的基本数据项，即每一列的值都是原子的，不可再分。

7.4.3 2NF 及其规范化#

第二范式（2NF）在第一范式（1NF）的基础上，进一步减少数据冗余和更新异常。

定义

• 第二范式（2NF） 要求关系模式R不仅满足1NF，而且所有非主属性完全函数依赖于任何候选键。

重要概念

• 完全函数依赖：如果属性A对候选键K的函数依赖是完全的，意味着A不能仅通过K的一部分属性就能确定。

例子分析

• 原关系模式R：(SNO, CNO, SDEPT, SLOC, CNAME, GRADE)
  • 问题：非主属性SDEPT、SLOC、CNAME对码(SNO, CNO)是部分函数依赖。
• 分解为2NF关系模式：
  • SC(SNO, CNO, GRADE)
  • C(CNO, CNAME)
  • S(SNO, SDEPT, SLOC)
  • 这样的分解解决了非完全函数依赖问题。

2NF分解的方法

• 通过投影分解，将部分函数依赖的属性从原关系中分离出来，形成新的关系。
• 分解得到的新关系应保持原有的函数依赖关系。

重要性

• 第二范式的目标是减少数据冗余和更新异常。
• 它通过确保非主属性依赖于整个候选键来实现。
• 2NF关系模式有助于简化数据库的维护工作，提高数据的准确性和完整性。

通过实现2NF，数据库设计可以避免大量的数据重复，减少插入、删除和更新操作中的问题，使数据库管理更为高效。在实际应用中，通常要求数据库至少达到2NF。

7.4.4 3NF 及其规范化#

第三范式（3NF）进一步减少数据冗余和更新异常，是数据库设计中非常重要的一个规范化阶段。

定义

• 第三范式（3NF） 要求关系模式R不仅满足2NF，而且每个非主属性既不部分函数依赖于码也不传递函数依赖于码。

重要概念

• 传递函数依赖：如果一个非主属性依赖于另一个非主属性，且这个非主属性依赖于码，那么存在传递函数依赖。

例子分析

• 原关系模式S：(SNO, SDEPT, SLOC)
  • 函数依赖：SNO->SDEPT, SDEPT->SLOC, SNO->SLOC
  • 问题：存在非主属性SLOC对码SNO的传递函数依赖。
• 分解为3NF关系模式：
  • SD(SNO, SDEPT)
  • DL(SDEPT, SLOC)
  • 这种分解解决了传递函数依赖问题。

分解的过程

• 目的：消除非主属性对码的传递函数依赖。
• 方法：通过将原关系模式进一步分解，把具有传递函数依赖的属性分离到不同的关系模式中。

解决问题

• 减少数据冗余：非主属性不再重复存储。
• 避免插入异常：每个实体的固有信息可以独立存储，无需依赖其他实体。
• 避免删除异常：删除一个实体的信息不会导致与其相关的其他信息丢失。
• 简化修改操作：不再需要跨多个记录的复杂修改。

3NF的重要性

• 3NF关系模式使得数据结构更加合理，更容易维护。
• 在大多数实际应用中，3NF是一个理想的目标，它足以消除大部分的数据冗余和更新异常。
• 达到3NF的数据库具有更高的查询效率和数据一致性。

7.4.5 BCNF 规范化#

BCNF（Boyce-Codd Normal Form）是第三范式（3NF）的加强版，用于解决3NF中仍然存在的某些问题。

定义

• BCNF：若关系模式R∈1NF，并且对于R的每个函数依赖 X->Y（$Y \nsubseteq X$），X都包含码，则R∈BCNF。

关键概念

• 函数依赖：X->Y表示Y的值依赖于X的值。
• 码：唯一标识关系中元组的属性集。

例子分析

• 原关系模式ORDER：(C, F, P)
  • 函数依赖：(C, P)->F, (C, F)->P, F->P
  • 问题：存在主属性P对码的部分函数依赖。
• 分解为BCNF关系模式：
  • CF(C, F)
  • PF(F, P)
  • 分解消除了主属性对码的部分函数依赖。

解决的问题

• 数据冗余：减少因为主属性对码的部分依赖而造成的数据冗余。
• 更新异常：简化了因为冗余数据而导致的复杂更新操作。
• 插入异常：允许独立地插入实体信息。
• 删除异常：避免因删除一个实体信息而导致与其相关的其他重要信息丢失。

与3NF的区别

• BCNF是3NF的特例，但3NF不一定是BCNF。
• 3NF允许非主属性对码的传递依赖，而BCNF则不允许。

重要性

• BCNF更严格地处理了函数依赖，使得关系模式的结构更加合理，减少数据冗余和更新异常。
• 达到BCNF的关系模式在许多情况下能提供更好的性能和数据完整性。

实际应用中的考虑

• 有时为了实用性和查询效率，某些关系模式可能不需要完全规范化到BCNF。
• BCNF的设计可能会导致过度分解，影响查询性能。

7.5.1 无损连接分解#

1. 什么是无损连接分解？

给定一个关系模式 R 及其函数依赖集 F，将 R 分解为多个子关系：

如果对任意实例 r(R)，我们都有：

也就是说，从子关系的自然连接中能够 恰好重建原关系，不丢数据、不增加伪组合。

那么这个分解就叫做：

无损连接分解（Lossless-join Decomposition）

否则是：

✘ 有损分解（Lossy Decomposition）

2. 为什么无损连接分解很重要？

在数据库设计中，我们经常把关系拆成多个表（为了范式、消除冗余等）。如果分解不满足无损性，就会：

• 连接后产生“伪元组”（不存在的组合）
• 或无法通过自然连接恢复原表

这会直接导致数据错误，是绝对不能接受的。

因此：

所有有效的关系模式分解必须满足无损性。

二元分解的经典判定条件（最常用）

关系 R 分解为两个模式：

1
R → {R₁, R₂}

设交集：

1
X = R₁ ∩ R₂

那么分解为 无损连接 的 必要且充分条件：

✔ X → R₁ 或 X → R₂（在 F⁺ 下成立）

也就是交集的属性组必须能函数决定至少一个子关系的全部属性。

★ 直观理解：

两个关系共享的部分 X，必须足够强，能“黏住”两个关系，不让错误组合产生。

4. 二元分解判定示例

例 1

R(A, B, C) F: A → B 分解：

R → {R₁(A, B), R₂(A, C)}

交集 X = {A}

检查： A → B?（是） → 能决定 R₁

✔ 无损

例 2（有损）

R(A, B, C) F = ∅ 分解：

R → {R₁(A, B), R₂(B, C)}

交集 X = {B}

检查： B → {A, B}?（否） B → {B, C}?（否）

✘ 有损 因为没有依赖去约束伪组合。