斯坦福-CS145-数据库导论笔记-全-

斯坦福 CS145 数据库导论笔记（全）

1：01-01 - 数据库导论 🗄️

在本节课中，我们将要学习数据库及数据库管理系统的基本概念。我们将从数据库应用的设计者、用户和开发者的角度，探讨数据库系统为何如此普及，以及它们提供了哪些核心功能。

数据库管理系统的核心功能

数据库管理系统为应用程序提供了一系列关键功能。这些功能可以用一个长句来概括：它提供了高效、可靠、方便、安全的多用户存储和访问大量持久性数据的方法。接下来，我们将逐一详细解析这些形容词，以理解数据库系统为何如此流行。

1. 海量数据

数据库系统的第一个特点是能够处理海量数据。当今世界每天产生的数据量巨大，有时甚至达到几TB。数据库管理系统被设计用来处理那些远超计算机内存容量的数据。

2. 持久性

数据库中的数据是持久的。这意味着数据比操作它的程序存活得更久。一个典型的程序结束后，其数据会消失；而数据库中的数据在程序停止后依然存在，并且可以被多个程序反复操作。

3. 安全性

数据库系统运行着银行、电信等关键应用，因此必须保证数据的一致性，即使在发生硬件故障、软件故障或停电时，数据也不会丢失或被破坏。系统内置了多种机制来抵御故障和恶意攻击。

4. 多用户

多个程序或用户可以同时操作同一个数据库。系统通过并发控制机制来协调这些访问，确保数据的一致性。这种控制发生在数据项级别，允许多个用户高效地并行工作。

5. 便利性

数据库系统旨在让处理大量数据变得更容易。这主要体现在两个概念上：

物理数据独立性：数据在磁盘上的实际存储方式与程序如何看待数据是相互独立的。存储方式的改变通常不需要修改应用程序。
高级查询语言：用户使用声明式语言来描述想要获取什么数据，而无需指定如何获取的算法。系统会自动寻找最高效的执行方式。

6. 高效性

性能是数据库系统的重中之重。系统必须能够在海量数据（如TB级别）上，每秒执行成千上万次复杂查询或更新操作。

7. 可靠性

对于关键系统，高可用性至关重要。数据库管理系统为其应用程序提供极高的正常运行时间保证（如99.9999%）。

综上所述，数据库系统集成了处理海量、持久、安全、多用户数据的便利且高效的方法。如果你有一个需要处理数据的应用程序，利用数据库系统来实现这些功能将是理想的选择。

课程范围与相关概念

上一节我们介绍了数据库系统的核心功能，本节中我们来看看本课程的范围以及一些相关的技术概念。

并非所有数据处理都通过数据库系统完成。在构建数据库应用时，还会涉及其他工具和方式：

框架：如Django、Ruby on Rails，它们帮助开发程序并生成数据库调用。本课程不深入讨论框架。
中间件：如应用服务器、Web服务器，它们帮助应用程序与数据库交互。这部分内容也超出本课程范围。
替代方案：历史上，很多数据存储在文件或Excel表格中。像Hadoop这样的处理框架可以直接对文件中的数据进行操作。

本系列课程将专注于数据库管理系统本身，以及如何通过它来存储和操作数据。

数据库核心概念

了解了课程范围后，我们来看看数据库领域的几个核心概念。

数据模型

数据模型描述了数据如何被结构化。常见的数据模型包括：

关系数据模型：将数据视为一组记录（行）。
XML文档模型：将数据组织为带标签值的层次结构。
图数据模型：将数据表示为节点和边。

模式与数据

可以将其类比为编程语言中的类型与变量。

模式：定义了数据库的结构，相当于类型。通常使用数据定义语言来设置。
数据：是存储在模式中的实际内容，相当于变量。模式相对稳定，而数据变化频繁。

数据操作

一旦模式建立且数据加载完毕，就可以对数据进行查询和修改。这通常通过数据操作语言来完成。

数据库系统中的角色

数据库系统的建设和使用涉及不同的人员角色。

以下是参与数据库系统的主要角色：

数据库实现者：负责构建数据库管理系统本身。这不是本课程的重点。
数据库设计者：负责为特定应用程序设计数据库的模式。这是一项关键且复杂的工作。
应用程序开发者：负责编写在数据库上运行的程序，作为最终用户与数据之间的接口。多个程序可以操作同一个数据库。
数据库管理员：负责加载数据、启动系统、进行性能调优和保障系统平稳运行。这是一个非常重要且高薪的职位。

在本课程中，我们将主要从应用程序设计者和开发者的角度来学习和思考。

总结

本节课中，我们一起学习了数据库管理系统的基础知识。我们了解了它提供的七大核心功能：处理海量数据、保证数据持久性与安全性、支持多用户并发访问、提供使用便利性、追求运行高效性以及确保系统可靠性。我们还明确了本课程的范围，介绍了数据模型、模式等核心概念，并认识了数据库系统中涉及的各类角色。无论你是否意识到，数据库系统已经渗透到日常生活的方方面面，支撑着众多关键应用。

2：02-01-关系模型 🗄️

在本节课中，我们将要学习关系模型。关系模型是数据库管理系统的基石，已有超过35年的历史。它催生了一个价值数十亿美元的产业，目前所有商业数据库系统都基于此模型。它的优势在于模型本身极其简单，支持通过高级查询语言进行高效的数据访问，并且其实现也极为高效。

关系与表格 📊

上一节我们介绍了关系模型的重要性，本节中我们来看看它的基本构造。关系模型中最核心的构造就是关系，通常也被称为表格。一个数据库由一组关系（或表格）组成。

每个表都有一个名称。在本教程的示例中，我们将使用一个关于学生申请大学的虚构数据库，并聚焦于两个表格：学生表和大学表。

属性与类型 📝

在关系型数据库中，每个关系都有一组预定义的列，这些列被称为属性。每个属性都有一个名称和一种类型（有时也称为域）。

以下是两个表格的属性定义：

学生表：包含ID（整数）、姓名（字符串）、GPA（浮点数）和照片（JPEG文件）等属性。
大学表：包含名称（字符串）、州（枚举类型，如50个州的缩写）和注册人数（整数）等属性。

元组与实例 📄

实际的数据存储在称为元组（或表格中的行）的结构中。在特定时间点，表格中所有元组的集合称为该关系的实例。而数据库的模式则定义了关系的结构，包括名称、属性和属性类型。模式通常提前设定，而数据实例会随时间变化。

以下是数据实例的示例：

学生表实例：
- (123, ‘Amy’, 3.9, 😊)
- (234, ‘Bob’, 3.4, 😟)
大学表实例：
- (‘斯坦福大学’, ‘CA’, 15000)
- (‘伯克利’, ‘CA’, 36000)
- (‘麻省理工学院’, ‘MA’, 10000)

空值（NULL）及其处理 ⚠️

关系型数据库中有一个特殊的值，称为NULL。它可以出现在任何类型的列中，用于表示值未知或未定义。

例如，如果学生Craig的GPA未知，其元组可能为(345, ‘Craig’, NULL, 😐)。NULL值在使用时必须非常小心，因为它会影响查询逻辑。

以下是查询示例，说明NULL值的影响：

查询GPA > 3.5的学生：结果包含Amy，不包含Bob，也不包含Craig（因为无法判断NULL > 3.5是否成立）。
查询GPA <= 3.5的学生：结果包含Bob，不包含Craig。
查询GPA > 3.5 OR GPA <= 3.5的学生：结果仍然不包含Craig。这说明即使逻辑上应包含所有元组，NULL值也可能导致其被排除在外。

键（Key）的概念 🔑

键是关系型数据库中的另一个核心概念。键是一个或一组属性，其值能唯一标识关系中的每一个元组。

以下是键的示例：

在学生表中，ID属性很可能是一个键，能唯一标识每个学生。
在大学表中，仅名称可能不够唯一（例如可能存在多个“华盛顿学院”），因此名称和州的组合更可能成为键。

键非常重要，其主要用途包括：

标识元组：用于精确查找特定数据行。
提升效率：数据库系统会为键建立索引，以加速基于键的查询。
关系引用：在关系模型中，一个关系通过另一个关系的键来引用其元组，取代了指针的概念。

SQL创建表语句 💻

在SQL语言中，创建关系（表）的语句非常简单直接。使用CREATE TABLE命令，后跟表名和属性定义列表。

创建学生表的SQL代码示例：

CREATE TABLE 学生 (
    ID INT,
    姓名 VARCHAR(255),
    GPA FLOAT,
    照片 BLOB
);

创建大学表的SQL代码示例：

CREATE TABLE 大学 (
    名称 VARCHAR(255),
    州 CHAR(2),
    注册人数 INT
);

总结 📚

本节课中我们一起学习了关系模型的基础知识。我们了解到关系模型是一个简单而强大的基础，它使用表格来组织数据，通过属性定义结构，用元组存储实际数据，并通过模式来描述整体设计。我们还探讨了NULL值的特殊性及其在查询中需要谨慎处理的原因，以及键对于唯一标识和数据关联的重要性。最后，我们看到了如何使用SQL语句来创建表。这些概念共同构成了理解和运用现代数据库系统的基石。

3：02-02 关系型数据库查询 🗄️

在本节课中，我们将要学习如何查询关系型数据库。课程不会专注于某一种特定的查询语言，而是讨论查询关系型数据库的一般性方法和核心概念。

数据库的基本使用流程

上一节我们介绍了课程目标，本节中我们来看看创建和使用关系型数据库的基本步骤。数据库领域通常习惯将数据库和数据库系统画成巨大的磁盘图标，本教程也将沿用这一表示方式。

以下是创建和使用数据库的核心步骤：

设计并创建模式：使用数据定义语言来定义数据库的模式。模式规定了数据库中关系的结构以及这些关系的属性。
加载初始数据：数据库通常需要从外部数据源（如文件）进行初始化，将数据加载到已创建好的关系中，形成初始的元组集合。
查询与修改数据：当数据加载完毕后，就可以开始对数据库进行查询和更新操作。这个过程在数据库的整个生命周期中会持续发生。

查询交互模式

在数据库就绪后，用户（通常通过应用程序或网站）可以向数据库提出问题（查询）或请求修改数据。

其基本交互模式如下：

用户提出查询 Q1，数据库返回答案 A1。
用户（可能是同一用户或其他用户）提出另一个查询 Q2，数据库返回答案 A2。
用户可能请求修改数据（如插入或更新），数据库执行操作并确认更改完成。

查询语言的特点

关系型数据库支持使用高级语言进行即席查询。即席查询意味着你可以随时提出事先未预料到的问题，而无需为每个特定查询编写冗长的程序。

关系型查询语言是高级语言，具有以下优势：

表达简洁：可以用相对紧凑的代码编写复杂的查询。
非过程化：用户只需声明“想要什么”，而无需指定“如何获取”的具体算法。

例如，针对一个学生申请大学的数据集，你可以轻松提出以下查询：

找出所有GPA大于3.7且只申请了斯坦福大学和麻省理工学院的学生。
找出所有位于加利福尼亚州、申请人数少于500人的工程院系。
找出过去五年内平均录取率最高的大学。

这些查询虽然看起来复杂，但使用如SQL这样的语言，只需几行代码即可表达。

查询的复杂度与语言特性

有些查询易于表述但难以高效执行，反之亦然。值得注意的是，查询的表述难度和执行难度并不总是一致的。

在关系型查询语言中，一个关键特性是：对关系进行查询操作，得到的结果也是一个关系。这被称为语言的闭包特性。

闭包特性带来了组合性，即允许在一个查询的结果之上再次执行新的查询，或者将查询结果与数据库中已有的关系进行组合操作。

两种主要的查询语言

最后，我们来简要了解两种重要的查询语言：关系代数和SQL。

关系代数：一种形式化、理论基础坚实的代数语言。它使用一系列操作符（常以希腊字母表示）来定义查询。
SQL：一种在实际数据库系统中广泛使用的实践性语言。SQL的语义建立在关系代数的基础之上。

例如，查询“GPA大于3.7且申请了斯坦福大学的学生的ID”：

在关系代数中，可能表示为：π_ID (σ_(GPA>3.7 ∧ cName=‘Stanford’)(Student ⋈ Apply))

在SQL中，对应的查询语句是：

SELECT s.ID
FROM Student s, Apply a
WHERE s.ID = a.ID
  AND s.GPA > 3.7
  AND a.cName = ‘Stanford’;

从教学角度，建议先理解关系代数以建立扎实的理论基础，再学习SQL以掌握实践技能。当然，你也可以根据兴趣直接开始学习SQL。

本节课中我们一起学习了关系型数据库查询的基本流程、交互模式、查询语言的特点以及关系代数与SQL这两种核心语言的基本概念。理解这些基础是进一步掌握具体数据库查询技术的关键。

4：结构良好的 XML 📖

在本节课中，我们将要学习 XML 的基础知识。XML 是一种重要的数据表示和交换标准，可以作为关系模型的替代方案来结构化数据。我们将介绍 XML 的核心组成部分，并将其与关系模型进行比较，帮助你理解两者的区别和适用场景。

概述：什么是 XML？

XML 的全称是可扩展标记语言。它是一种用于数据表示和交换的标准，最初为互联网信息交换而设计。XML 可以被看作是一种数据模型，也是一种文档格式，类似于 HTML。但与 HTML 不同，XML 的标签描述的是数据的内容，而非数据的格式。此外，XML 也常作为一种流格式，在程序间传输数据。

XML 的核心组成部分 🧱

XML 文档由三个基本组件构成，它们共同定义了数据的结构和含义。

1. 标签与元素

元素是 XML 的基本构建块，由开标签、内容和闭标签组成。例如，一个表示名字的元素如下所示：

<firstname>杏仁</firstname>

元素可以嵌套，形成层次结构。例如，一个 author 元素可以包含 firstname 和 lastname 子元素。

2. 属性

属性提供了关于元素的额外信息，位于元素的开标签内。一个属性由属性名和属性值组成，格式为 name=“value”。一个元素可以有多个属性，但属性名必须唯一。

<book ISBN=“123456” price=“29.99” edition=“3”>

3. 文本内容

文本是元素的实际数据内容，构成了 XML 树的叶子节点。例如，在 <title> 和 </title> 标签之间的“杏仁”就是文本内容。

综上所述，XML 的结构可以看作一棵树，其中元素是节点，属性是节点的附加信息，文本是叶子节点。

XML 与关系模型的比较 ⚖️

上一节我们介绍了 XML 的结构，本节中我们来看看它与传统的关系模型有何不同。了解这些差异有助于你在设计应用时选择合适的方案。

以下是两者在几个关键方面的对比：

数据结构
- 关系模型：数据以表格形式组织，包含行和列，结构扁平。
- XML：数据以嵌套的标签元素组织，形成层次化或树形结构，更灵活。
模式（Schema）
- 关系模型：模式是严格且必须预先定义的，所有数据必须符合模式。
- XML：模式是灵活且可选的。数据和描述（标签）混合在一起，允许结构上的不一致性（例如，某些元素可有可无，元素数量可变）。
查询语言
- 关系模型：使用 SQL 或关系代数，语言成熟且相对简单。
- XML：查询语言（如 XPath、XQuery）稍显复杂，技术仍在发展中。
数据顺序
- 关系模型：表中的数据本质上是无序的集合。
- XML：文档或流中元素的出现顺序隐含了一种排序，这个顺序可以被应用程序利用。
实现与成熟度
- 关系模型：已有数十年历史，拥有非常成熟和高效的数据库系统实现。
- XML：相对较新，在传统数据库中常作为附加层实现，而非原生模型。

什么是结构良好的 XML？✅

“结构良好”是 XML 最基本的要求。一个 XML 文档或数据流如果满足以下所有条件，就被认为是结构良好的：

有且仅有一个根元素：所有其他元素都必须嵌套在这个根元素之内。
标签正确匹配与嵌套：每个开标签都必须有对应的闭标签，且标签之间不能交错。
属性名唯一：在同一个元素内，每个属性名只能出现一次。

程序通过 XML 解析器 来检查 XML 是否结构良好。如果不符合要求，解析器会报错；如果符合，解析器会输出解析后的 XML 树，供程序通过 DOM（文档对象模型） 或 SAX（流式 API） 等标准接口进行访问和处理。

XML 的展示与样式 🎨

由于 XML 关注内容而非格式，直接查看原始 XML 文档可能不够直观。为了在浏览器等环境中更好地展示 XML 数据，通常需要将其转换为更友好的格式（如 HTML）。

以下是实现此转换的常见方法：

CSS：层叠样式表，可以为 XML 元素定义简单的显示规则。
XSL：可扩展样式表语言，功能更强大，可以将 XML 转换为 HTML 或其他格式。其基本流程是：XML 文档经过解析器检查后，由 XSL 处理器根据预定义的规则集将其转换为 HTML，最终在浏览器中渲染。

总结 📝

本节课中我们一起学习了 XML 的基础知识。XML 是一种用于数据表示和交换的灵活标准，它采用层次化的标签结构来描述数据内容。我们了解了 XML 的核心组成部分（元素、属性、文本），并将其与关系模型在结构、模式、查询等方面进行了比较。我们还明确了“结构良好”的 XML 所需满足的三个基本条件，并简要介绍了如何通过样式表（如 XSL）来展示 XML 数据。

记住，结构良好的 XML 提供了最大的灵活性，是使用 XML 的起点。在接下来的课程中，我们将探讨如何为 XML 引入更严格的模式定义（有效的 XML），以及如何查询 XML 数据。

5：XML Schema 入门教程 🗂️

在本课程中，我们将学习 XML Schema（XSD），这是一种用于定义和验证 XML 文档结构和内容的强大语言。我们将了解其核心概念、基本语法，并通过示例演示其关键特性。

概述

XML Schema 与 DTD 类似，为 XML 数据提供了内容规范的描述方式。我们将 XML 文档及其模式描述文件发送给验证解析器，解析器会检查文档是否符合规范。如果符合，解析成功；如果不符合，则会报告文档无效。

与 DTD 相比，XML Schema 功能更强大，它允许我们指定数据类型、键约束、类型化引用以及元素出现次数等。此外，XML Schema 规范本身是用 XML 语言编写的。

XML Schema 的核心特性

上一节我们介绍了 XML Schema 的基本概念，本节中我们来看看它的几个核心特性。以下是 XML Schema 相较于 DTD 的主要增强功能：

数据类型：可以为元素和属性指定具体的数据类型（如整数、字符串），而不仅仅是字符串。
键声明：可以定义类似关系数据库中主键的唯一性约束。
键引用：可以定义类型化的指针，确保引用指向特定类型的元素。
出现次数约束：可以精确控制元素出现的次数范围。

数据类型

在 DTD 中，所有属性值本质上都是字符串。XML Schema 允许我们为属性指定具体的数据类型。

例如，我们可以规定 price 属性必须是整数类型。在 XSD 中，相应的声明可能如下所示：

<xs:attribute name="price" type="xs:integer" use="required"/>

如果 XML 文档中的 price 值不是整数（例如是 “foo”），验证将会失败并报错。

键声明

键（Key）类似于 DTD 中的 ID，但功能更强大。它可以确保某个属性或元素值在特定范围内是唯一的。

例如，我们可以声明书籍的 ISBN 属性和作者的 ident 属性为键：

<xs:key name="bookKey">
  <xs:selector xpath="book"/>
  <xs:field xpath="@ISBN"/>
</xs:key>
<xs:key name="authorKey">
  <xs:selector xpath="author"/>
  <xs:field xpath="@ident"/>
</xs:key>

如果两个作者具有相同的 ident 值，验证将失败，提示键冲突。

键引用

键引用（Keyref）用于创建类型化的指针，确保引用指向已定义的键。这比 DTD 中的 IDREF 更严格，因为它可以指定引用的目标类型。

例如，书籍中引用作者的 authIdent 属性，必须指向一个已定义的作者键：

<xs:keyref name="authorRef" refer="authorKey">
  <xs:selector xpath="book/authors/author"/>
  <xs:field xpath="@authIdent"/>
</xs:keyref>

如果 authIdent 的值指向一个不存在的作者，或者指向一个非作者元素（如书籍），验证将会失败。

出现次数约束

XML Schema 允许我们通过 minOccurs 和 maxOccurs 属性来精确控制子元素出现的次数。

以下是几个常见的约束示例：

默认情况：未指定时，minOccurs 和 maxOccurs 默认为 1。
零个或多个：minOccurs="0" 和 maxOccurs="unbounded"。
至少一个：minOccurs="1" 和 maxOccurs="unbounded"。
零个或一个：minOccurs="0" 和 maxOccurs="1"。

例如，规定每本书必须至少有一个作者，但可以有任意多个：

<xs:element name="authors" minOccurs="1" maxOccurs="unbounded">
  ...
</xs:element>

如果一本书没有作者子元素，或者备注子元素超过一个，验证将失败。

总结

本节课中我们一起学习了 XML Schema（XSD）的基础知识及其核心特性。我们了解到，XML Schema 通过支持数据类型、键声明、键引用和出现次数约束，提供了比 DTD 更强大、更精确的 XML 文档验证能力。虽然其语法比 DTD 更复杂，但它在确保数据完整性和结构正确性方面优势明显。建议下载示例文件进行实践，以加深理解。

6：JSON 简介 📖

在本课程中，我们将学习 JSON（JavaScript Object Notation）的基础知识。我们将了解 JSON 是什么、它的基本结构，并将其与关系数据模型和 XML 进行比较，以便更好地理解其特性和适用场景。

概述 📋

JSON 是一种轻量级的数据交换格式。它源于 JavaScript，但现在已独立于任何编程语言。JSON 易于人类阅读和编写，同时也易于机器解析和生成。它非常适合表示半结构化数据，并广泛用于数据交换和存储。

JSON 的发音与背景 🗣️

关于 JSON 的发音存在不同说法。有些人读作 “JAY-sawn”，有些人读作 “JAY-sahn”。根据 JSON 原始开发者的说法，它应读作 “JAY-sawn”。与 XML 类似，JSON 可被视为一种数据模型，是关系数据模型的一种替代方案，尤其适合处理半结构化数据。

JSON 的基本概念 💡

JSON 代表 JavaScript Object Notation（JavaScript 对象表示法）。尽管其名称中包含 JavaScript，但它现在已基本独立于该语言。JSON 最初设计用于数据对象的序列化，即将程序中的数据对象以文本形式写入文件。

JSON 具有可读性，类似于 XML，常用于数据交换。例如，一个程序可以将对象写成 JSON 格式，以便另一个程序读取。此外，由于 JSON 不像关系模型那样严格，它也被广泛用于表示和存储没有严格结构的数据，即半结构化数据。

如今，许多编程语言都提供了用于读取和写入 JSON 数据的解析器。

JSON 的基本结构 🧱

JSON 的基本结构是递归定义的。以下是 JSON 中的基本构造：

基本原子值

JSON 支持几种基本数据类型：

数字：例如 42 或 3.14
字符串：例如 "Hello, World!"
布尔值：true 或 false
空值：null

复合值类型

JSON 有两种主要的复合值类型：对象和数组。

对象 (Object)

对象用大括号 {} 括起来，由一组标签-值对（也称为属性）组成。标签是字符串，值可以是任何 JSON 值。

{
  "firstName": "John",
  "lastName": "Doe"
}

数组 (Array)

数组用方括号 [] 括起来，是值的有序列表，元素之间用逗号分隔。

["apple", "banana", "orange"]

这些结构是递归的。数组中的值可以是任何类型（包括其他数组或对象）。同样，对象中标签-值对的值也可以是任何复合值或基本值。

JSON 格式灵活，不要求数据具有统一的结构。例如，一本书的数据可能包含 edition 字段，而另一本书可能没有。

JSON 与关系模型的比较 ⚖️

上一节我们介绍了 JSON 的基本结构，本节中我们来看看它与关系模型的区别。许多比较点与我们之前对比 XML 和关系模型时相似。

以下是 JSON 与关系模型的主要区别：

基本结构：关系模型基于表，有固定的列结构，数据是表中的行。JSON 基于集合，是标签-值对和数组的集合，且可以嵌套。
模式 (Schema)：关系模型需要预先定义严格的模式，所有数据必须符合该模式。JSON 通常不需要预先定义模式，模式信息常与数据混合在一起，称为自描述数据。这使 JSON 比关系模型更灵活。
查询语言：关系模型拥有强大且表达力强的查询语言（如 SQL）。对于 JSON，目前（指课程制作时的2012年2月）还没有被广泛使用的标准查询语言。JSON 数据通常被读入程序后以编程方式操作。
排序：关系模型本质上是无序的集合。JSON 中的数组是有序的列表。虽然 JSON 数据写入文件时文件本身有序，但数据的顺序通常不重要。
系统实现：关系模型有成熟、高效的原生数据库系统实现。JSON 本身通常没有独立的数据库系统，而是与编程语言配合使用。不过，JSON 在 NoSQL（非关系型）数据库系统中有所应用，一些文档型数据库使用 JSON 格式存储文档。

JSON 与 XML 的对比 🔄

JSON 和 XML 在用途上有很大重叠，都适合表示半结构化数据和用于数据交换。以下是它们的一些对比：

冗长性：通常情况下，表达相同数据时，XML 比 JSON 更冗长，主要因为 XML 的结束标签等特性。
复杂性：普遍认为 XML 比 JSON 更复杂。XML 规范包含许多特性，而 JSON 的规范更简洁、易于理解。
有效性验证：XML 拥有成熟的模式定义和验证工具，如 DTD 和 XML Schema。JSON 也有类似的 JSON Schema，但在2012年2月时，其使用远不如 XML Schema 广泛。
编程接口：JSON 的亮点在于它与许多编程语言数据结构的映射更直接。XML 的数据模型（属性、子元素等）与编程语言的数据结构存在“阻抗不匹配”，操作起来可能更繁琐。
查询：XML 拥有 XPath、XQuery、XSLT 等查询和转换技术。JSON 在当时有一些提案，如 JSONPath、JSONQuery 和 Jackal，但均未形成被广泛使用的标准。

JSON 数据的有效性 ✅

那么，如何判断一份 JSON 数据是否有效呢？

语法有效：数据必须遵循 JSON 的基本结构规则（正确的括号、逗号分隔、预定义的数据类型等）。语法检查通常由 JSON 解析器完成。
语义有效（符合模式）：除了语法正确，数据还需符合特定的结构约束或模式。这可以通过 JSON Schema 等工具来验证。验证器会检查数据是否符合模式中定义的规则。

处理流程通常是：JSON 文件 -> 语法验证 -> (可选) 模式验证 -> 解析为编程语言中的对象。

总结 🎯

本节课中我们一起学习了 JSON 的基础知识。

JSON 代表 JavaScript Object Notation，是一种将数据对象序列化为人类可读格式的标准。
它非常适用于程序间的数据交换，以及以灵活的方式表示和存储半结构化数据。
JSON 的基本结构包括对象（用 {} 表示）和数组（用 [] 表示），以及数字、字符串、布尔值和 null 等基本值。
与关系模型相比，JSON 没有固定的预定义模式，更加灵活。
与 XML 相比，JSON 通常更简洁，与编程语言的集成更直接，但在模式验证和标准化查询方面，其工具生态在当时（2012年）不如 XML 成熟。

在下一个视频中，我们将通过实际操作演示来进一步探索 JSON。

7：关系代数基础 🧮

在本课程中，我们将学习关系代数的基本概念和核心操作符。关系代数是一种形式化语言，它为SQL等数据库查询语言提供了理论基础。我们将通过一个大学招生数据库的示例，逐步了解如何通过不同的操作符来查询和组合数据。

回顾：关系查询的本质

上一节我们介绍了关系查询的基本概念。查询关系型数据库时，操作作用于关系，并且查询结果本身也是一个关系。这意味着我们可以对查询结果进行进一步的查询，或者将其与其他关系组合。

在本课程的示例中，我们将使用一个包含三个关系的简单大学招生数据库：

College：包含大学名称、所在州和注册人数。
Student：包含学生ID、姓名、GPA和高中规模。
Apply：包含学生ID、申请的大学名称、申请专业和申请结果。

键（Key）是能唯一标识元组的属性或属性组。在本示例中，我们假设cName、sID和(sID, cName, major)组合分别是三个关系的键。

基础操作符

关系代数中最简单的查询是直接引用一个关系的名称，例如 Student，这将返回整个Student关系。

接下来，我们将学习用于过滤、切片和组合关系的操作符。

选择操作符 (σ)

选择操作符用于从关系中筛选出满足特定条件的行（元组）。

其一般形式为：σ_条件(关系名)

以下是三个示例：

找出GPA大于3.7的学生：
σ_{GPA>3.7}(Student)
找出GPA大于3.7 且高中规模小于1000的学生：
σ_{GPA>3.7 ∧ sizeHS<1000}(Student)
找出申请了斯坦福大学计算机科学专业的学生：
σ_{cName=‘Stanford’ ∧ major=‘CS’}(Apply)

投影操作符 (π)

投影操作符用于从关系中选择特定的列（属性）。

其一般形式为：π_属性列表(关系名)

例如，如果我们只关心申请记录中的学生ID和申请结果：
π_{sID, decision}(Apply)

组合选择与投影

由于每个操作符的输入和输出都是关系，因此我们可以将它们组合使用。

例如，要找出GPA大于3.7的学生的姓名：
π_{sName}(σ_{GPA>3.7}(Student))

关于重复项的重要说明

在关系代数中，结果集自动消除所有重复的元组。这与标准的SQL（基于多重集合）不同，后者默认保留重复项。

组合多个关系

为了回答涉及多个关系的问题，我们需要将关系组合起来。

笛卡尔积 (×)

笛卡尔积（叉积）将两个关系的所有元组进行两两组合。

其形式为：关系1 × 关系2

例如，Student × Apply 的结果是一个新关系，其模式是Student和Apply模式的并集。如果两个关系有同名属性（如sID），则会通过添加关系名前缀来区分（如Student.sID和Apply.sID）。

使用笛卡尔积进行查询

假设我们要查询：“高中规模大于1000、申请了计算机科学专业并被拒绝的学生的姓名和GPA。”

这个查询需要结合Student和Apply关系的信息。我们可以分步构建查询：

组合所有可能的学生和申请记录：Student × Apply
筛选出有意义的组合（同一学生的记录），并加上其他条件：
σ_{Student.sID=Apply.sID ∧ sizeHS>1000 ∧ major=‘CS’ ∧ decision=‘R’}(Student × Apply)
最后，投影出所需的姓名和GPA：
π_{sName, GPA}(σ_{Student.sID=Apply.sID ∧ sizeHS>1000 ∧ major=‘CS’ ∧ decision=‘R’}(Student × Apply))

自然连接 (⋈)

自然连接是一种更便捷的操作符，它执行笛卡尔积，但自动强制所有同名属性的值相等，并去除重复的同名列。

其形式为：关系1 ⋈ 关系2

使用自然连接重写上面的复杂查询：
π_{sName, GPA}(σ_{sizeHS>1000 ∧ major=‘CS’ ∧ decision=‘R’}(Student ⋈ Apply))

可以看到，查询变得简洁许多，因为我们不再需要手动编写连接条件（如Student.sID=Apply.sID）。

连接更多关系

自然连接可以连续使用。例如，若想在上面的查询中增加“只考虑注册人数超过20000的大学”这一条件，只需引入College关系：
π_{sName, GPA}(σ_{sizeHS>1000 ∧ major=‘CS’ ∧ decision=‘R’ ∧ enrollment>20000}(Student ⋈ Apply ⋈ College))

自然连接会自动处理Apply.cName和College.cName的相等性。

θ-连接 (⋈_θ)

θ-连接是笛卡尔积后加选择条件的一个缩写。

其形式为：关系1 ⋈_条件关系2，等价于 σ_条件(关系1 × 关系2)

虽然它没有增加新的表达能力，但在数据库系统实现和讨论中，“连接”一词通常指的就是θ-连接。

总结

在本节课中，我们一起学习了关系代数的基础知识。关系代数是一种对关系集合进行操作并生成新关系的形式化语言。

我们掌握了以下核心操作符：

选择 (σ)：根据条件筛选行。
投影 (π)：根据属性名选择列。
笛卡尔积 (×)：组合两个关系的所有元组对。
自然连接 (⋈)：基于同名属性等值组合关系，并去重，是实践中非常有用的工具。
θ-连接 (⋈_θ)：笛卡尔积加选择条件的缩写。

这些操作符构成了关系查询的基石。在下一节课中，我们将学习关系代数的其他附加操作符和表达式的替代表示法。

8：WHERE子句中的子查询 🧩

在本节课中，我们将学习如何在SQL的WHERE子句中使用子查询。子查询是嵌套在另一个查询内部的查询，它允许我们基于更复杂的条件来筛选数据。我们将通过具体的例子，学习使用IN、NOT IN、EXISTS、ANY和ALL等操作符来构建强大的查询条件。

概述与准备工作

我们将使用一个包含大学、学生以及学生申请记录的示例数据库。数据表结构如下：

大学表 (College): 包含大学名称、所在州和入学人数。
学生表 (Student): 包含学生ID、姓名、GPA和毕业高中规模。
申请表 (Apply): 包含学生ID、大学名称和专业。

使用 `IN` 和 `NOT IN` 进行成员资格测试

上一节我们介绍了子查询的基本概念，本节中我们来看看如何使用IN和NOT IN操作符来检查某个值是否存在于子查询返回的结果集中。

示例1：查找申请了计算机科学专业的学生

以下查询使用子查询来查找所有申请了计算机科学（CS）专业的学生ID和姓名。

SELECT sid, sname
FROM Student
WHERE sid IN (
    SELECT sid
    FROM Apply
    WHERE major = 'CS'
);

查询逻辑：

内部子查询 (SELECT sid FROM Apply WHERE major = 'CS') 返回所有申请了CS专业的学生的ID集合。
外部查询 SELECT sid, sname FROM Student 会检查每位学生的ID是否在这个集合中。
最终，只返回那些ID在集合中的学生信息。

注意重复项问题：我们也可以使用JOIN来实现相同的查询，但需要小心处理重复记录。例如，如果一个学生申请了多所大学的CS专业，使用JOIN会导致该学生的记录出现多次。这时，我们需要使用SELECT DISTINCT来去重。然而，当查询结果包含可能重复的值（如姓名）时，DISTINCT可能会合并掉本应不同的记录，导致信息丢失。因此，在涉及聚合计算（如求平均GPA）时，使用子查询的WHERE ... IN形式通常是更安全、更准确的选择。

示例2：查找申请了CS但未申请EE专业的学生

这个查询展示了如何使用NOT IN来执行集合的差集操作，找出那些申请了CS但没有申请电子工程（EE）专业的学生。

SELECT sid, sname
FROM Student
WHERE sid IN (
    SELECT sid FROM Apply WHERE major = 'CS'
)
AND sid NOT IN (
    SELECT sid FROM Apply WHERE major = 'EE'
);

查询逻辑：

第一个条件 sid IN (... 'CS' ...) 确保学生申请了CS。
第二个条件 sid NOT IN (... 'EE' ...) 确保学生没有申请EE。
两个条件同时满足的学生才会被选中。

使用 `EXISTS` 和 `NOT EXISTS` 测试存在性

上一节我们使用IN检查了值的成员资格，本节我们学习EXISTS操作符，它用于检查子查询是否返回任何行（即结果集是否非空）。

示例3：查找存在同州其他大学的大学

这个查询使用了关联子查询，即子查询引用了外部查询中的列（C1.state）。

SELECT cname
FROM College C1
WHERE EXISTS (
    SELECT *
    FROM College C2
    WHERE C2.state = C1.state
    AND C2.cname <> C1.cname
);

查询逻辑：

对于College表（别名为C1）中的每一所大学，执行内部子查询。
子查询在College表（别名为C2）中寻找另一所大学（C2.cname <> C1.cname），且这所大学与外部查询中的大学位于同一个州（C2.state = C1.state）。
如果子查询找到了至少一条这样的记录（即EXISTS返回真），那么外部查询中的这所大学就会被包含在最终结果中。

示例4：查找GPA最高的学生（使用`NOT EXISTS`）

我们可以利用NOT EXISTS来寻找最大值。这个查询找出那些不存在GPA比他们更高的学生。

SELECT sname, GPA
FROM Student S1
WHERE NOT EXISTS (
    SELECT *
    FROM Student S2
    WHERE S2.GPA > S1.GPA
);

查询逻辑：

对于学生S1，子查询检查是否存在另一个学生S2，其GPA高于S1的GPA。
如果不存在这样的学生S2（即NOT EXISTS返回真），那么S1的GPA就是最高的（或并列最高），他/她将被选中。
如果有多个学生拥有相同的最高GPA，他们都会被返回。

使用 `ANY` 和 `ALL` 进行量化比较

ANY和ALL操作符允许我们将一个值与子查询返回的一组值进行比较。

value operator ANY (subquery): 如果value与子查询结果中的至少一个值满足operator（如>，=，<）关系，则条件为真。
value operator ALL (subquery): 如果value与子查询结果中的每一个值都满足operator关系，则条件为真。

示例5：查找GPA最高的学生（使用`ALL`）

我们可以用ALL重写寻找最高GPA的查询。

SELECT sname, GPA
FROM Student
WHERE GPA >= ALL (
    SELECT GPA FROM Student
);

查询逻辑：选择那些GPA大于或等于所有学生GPA的学生，这自然就是GPA最高（含并列）的学生。

示例6：查找不是来自最小规模高中的学生（使用`ANY`）

SELECT sname
FROM Student
WHERE sizeHS > ANY (
    SELECT sizeHS FROM Student
);

查询逻辑：选择那些高中规模大于至少一个其他学生高中规模的学生。这意味着，只要数据库中存在高中规模更小的学生，当前学生就会被选中。因此，结果将排除来自规模最小高中的学生。

重要提示：使用ANY和ALL时需要格外小心逻辑的正确性。例如，查询“不等于任何”（<> ANY）与“不等于所有”（<> ALL）含义截然不同。有些数据库系统（如SQLite）不支持ANY/ALL，但我们可以总是用EXISTS/NOT EXISTS来等价地重写查询。

总结

本节课中我们一起学习了在SQL WHERE子句中使用子查询的强大功能。我们掌握了：

使用 IN 和 NOT IN 来测试值是否在子查询的集合中，常用于成员资格检查和集合差集操作。
使用 EXISTS 和 NOT EXISTS 来测试子查询是否返回结果，特别适用于关联子查询和寻找最大/最小值。
使用 ANY 和 ALL 将一个值与子查询结果集进行量化比较，它们提供了简洁的语法，但使用时需仔细斟酌逻辑。

通过灵活组合这些技术，我们可以构建出非常复杂和精确的数据筛选条件，以满足多样的查询需求。记住，许多使用ANY/ALL的查询都可以用EXISTS/NOT EXISTS重写，这有助于提高代码的兼容性和可读性。

9：06-05-在FROM和SELECT子句中使用子查询 🧩

在本节课中，我们将学习如何在SQL查询的FROM子句和SELECT子句中使用子查询。我们将通过具体的示例，了解这两种子查询的用途、语法以及它们如何帮助我们编写更简洁、更高效的SQL语句。

概述 📋

在前面的课程中，我们介绍了子查询，并学习了如何在WHERE子句中使用它们来构建查询条件。本节课，我们将进一步探索子查询的另外两种常见用法：在FROM子句中生成临时表，以及在SELECT子句中作为计算列的值。我们将使用一个简单的大学招生数据库（包含college、student和apply表）来演示这些概念。

在FROM子句中使用子查询 🗂️

上一节我们介绍了子查询的基本概念，本节中我们来看看如何在FROM子句中使用子查询。其核心思想是：将一个SELECT语句（子查询）的结果作为一个临时的“表”，供外层查询使用。

应用场景：简化复杂计算

假设我们需要根据学生的高中规模来调整其GPA（绩点），调整公式为：GPA * (高中规模 / 1000)。我们想找出那些调整后的GPA与原GPA相差超过1.0的学生。

以下是不使用子查询的初始写法，其中调整GPA的复杂表达式需要重复书写多次：

SELECT sID, sName, GPA, GPA*(sizeHS/1000.0) AS scaledGPA
FROM Student
WHERE GPA*(sizeHS/1000.0) - GPA > 1.0
   OR GPA - GPA*(sizeHS/1000.0) > 1.0;

我们可以使用ABS()函数简化WHERE条件，但调整GPA的表达式仍然出现了两次：

SELECT sID, sName, GPA, GPA*(sizeHS/1000.0) AS scaledGPA
FROM Student
WHERE ABS(GPA*(sizeHS/1000.0) - GPA) > 1.0;

为了彻底避免重复计算，我们可以将计算调整GPA的部分放入FROM子句的子查询中：

SELECT *
FROM (
    SELECT sID, sName, GPA, GPA*(sizeHS/1000.0) AS scaledGPA
    FROM Student
) AS G
WHERE ABS(G.scaledGPA - G.GPA) > 1.0;

代码解析：

FROM子句中的子查询 (SELECT ... FROM Student) 会先执行，生成一个包含sID、sName、GPA和新增列scaledGPA的临时结果集。
我们使用AS G为这个临时结果集指定了一个别名G。
外层查询的SELECT *和WHERE子句就可以像操作普通表一样，引用G中的列（例如G.scaledGPA和G.GPA）。

这种方法使查询逻辑更清晰，复杂计算只书写一次，便于维护。

在SELECT子句中使用子查询 🔍

在FROM子句中，子查询生成的是一个表。而在SELECT子句中，子查询的作用是为结果集的每一行生成一个单独的值。这意味着SELECT子句中的子查询必须返回标量值（即单个值）。

应用场景一：为每所大学查找最高申请者GPA

假设我们想列出每所大学及其申请者中的最高GPA。一种方法是使用多表连接和GROUP BY。另一种更直观的方法是，在SELECT列表中为每所大学动态计算其最高GPA。

以下是使用SELECT子句中子查询的写法：

SELECT cName, state,
       (SELECT MAX(GPA)
        FROM Apply, Student
        WHERE Apply.sID = Student.sID
          AND Apply.cName = College.cName) AS maxGPA
FROM College;

代码解析：

对于FROM College表中的每一行（即每一所大学），外层查询都会执行一次SELECT子句中的子查询。
该子查询通过连接Apply和Student表，并利用条件Apply.cName = College.cName，找出申请当前这所大学的所有学生的最高GPA（MAX(GPA)）。
这个计算出的最高GPA值，会作为该行结果中maxGPA列的值输出。

重要限制：必须返回单个值

SELECT子句中的子查询必须且只能返回一个值。如果它返回多行，数据库将无法决定使用哪个值来填充结果单元格，从而导致错误。

例如，下面的查询试图为每所大学列出所有申请者的姓名，这是错误的：

-- 错误示例！子查询返回了多行
SELECT cName, state,
       (SELECT sName
        FROM Apply, Student
        WHERE Apply.sID = Student.sID
          AND Apply.cName = College.cName) AS students
FROM College;

执行此查询会报错，因为对于一所大学（如“Stanford”），子查询可能返回多个学生姓名（“Amy”、“Bob”等），而一个结果单元格无法容纳多个值。

总结 🎯

本节课我们一起学习了子查询的两种高级用法：

在FROM子句中使用子查询：将子查询的结果作为临时表使用，常用于简化复杂表达式、分步计算或组织查询逻辑。
在SELECT子句中使用子查询：为结果集的每一行动态计算一个列值。这是其核心公式：SELECT 列1, 列2, (返回单个值的子查询) AS 新列 FROM 表。务必记住，此类子查询必须返回标量值（单行单列）。

通过将子查询灵活运用于FROM和SELECT子句，我们可以构建出更强大、更模块化的SQL语句，有效解决复杂的数据检索问题。在后续关于聚合函数的课程中，我们还会看到更多结合子查询的实用技巧。

10：SQL 聚合操作详解 🧮

在本节课中，我们将要学习 SQL 中的聚合操作。聚合是数据分析的核心，它允许我们对数据集中的多行数据进行计算，例如求和、求平均值、计数等。我们将从基本的聚合函数开始，逐步深入到 GROUP BY 和 HAVING 子句，并通过一系列示例来理解它们的工作原理。

聚合函数简介

上一节我们介绍了 SQL 的基本查询结构。本节中我们来看看如何对数据进行汇总计算。

聚合函数对关系中多行的值集进行计算。所有 SQL 系统都支持以下基本聚合函数：

最小值：MIN(column_name)
最大值：MAX(column_name)
总和：SUM(column_name)
平均值：AVG(column_name)
计数：COUNT(column_name)

一旦引入聚合函数，我们就可以向 SELECT FROM WHERE 语句添加两个新子句：

GROUP BY：允许我们将关系划分为不同的组，然后对每个组独立计算聚合函数。
HAVING：允许我们在聚合值的结果上进行筛选。WHERE 条件适用于单行数据，而 HAVING 条件将应用于 GROUP BY 生成的各个组。

基础聚合查询示例

我们将使用一个简单的大学招生数据库来演示，其中包含 college、student 和 apply 表。

计算平均 GPA

第一个查询计算数据库中所有学生的平均 GPA。

SELECT AVG(GPA) FROM student;

该查询对 student 表的 GPA 列所有值执行计算，然后生成一个包含该平均值的元组。

查找特定专业的最低 GPA

第二个查询涉及连接操作，查找申请计算机科学专业的学生的最低 GPA。

SELECT MIN(s.GPA)
FROM student s, apply a
WHERE s.sID = a.sID AND a.major = ‘CS’;

此查询先通过学生 ID 连接 student 和 apply 表，并筛选出专业为 ‘CS’ 的记录，然后取 GPA 列中的最小值。

避免重复计算的聚合

计算申请计算机科学专业的学生的平均 GPA 时，需要注意重复申请的情况。

-- 可能重复计算的查询
SELECT AVG(GPA)
FROM student s, apply a
WHERE s.sID = a.sID AND a.major = ‘CS’;

-- 正确的查询：使用子查询确保每个学生只计算一次
SELECT AVG(GPA)
FROM student
WHERE sID IN (SELECT sID FROM apply WHERE major = ‘CS’);

第一个查询中，如果一名学生多次申请同一专业，其 GPA 会被重复计算。使用子查询可以确保每个学生只被计入一次。

使用 COUNT 函数

COUNT 函数返回结果中元组的数量。

-- 计算录取人数超过 15000 的大学数量
SELECT COUNT(*)
FROM college
WHERE enrollment > 15000;

该查询统计满足 enrollment > 15000 条件的大学数量。

使用 COUNT(DISTINCT ...)

COUNT(DISTINCT column_name) 用于计算特定列中不同值的数量。

-- 计算申请康奈尔大学的不同学生人数
SELECT COUNT(DISTINCT sID)
FROM apply
WHERE cName = ‘Cornell’;

如果不使用 DISTINCT，查询将统计申请次数，而非学生人数。COUNT(DISTINCT sID) 确保了每个学生只被计数一次。

使用 GROUP BY 进行分组聚合

上一节我们介绍了基础的聚合函数。本节中我们来看看如何将数据分组后进行聚合计算。

GROUP BY 子句将一个关系按照给定属性的值进行划分，然后对每个组独立计算聚合函数。

按单属性分组

以下是按单属性进行分组的示例。

查找每所大学的申请人数。

SELECT cName, COUNT(*)
FROM apply
GROUP BY cName;

该查询将 apply 关系按 cName（大学名称）分组，然后返回每个大学的名称及其对应的申请记录数量。

查找每个州的大学生总入学人数。

SELECT state, SUM(enrollment)
FROM college
GROUP BY state;

该查询将 college 表按 state（州）分组，然后返回每个州的名字及其所有大学入学人数的总和。

按多属性分组

我们可以按多个属性进行分组。

计算每个大学和专业的组合中，申请学生的最高和最低 GPA。

SELECT cName, major, MIN(GPA), MAX(GPA)
FROM student s, apply a
WHERE s.sID = a.sID
GROUP BY cName, major;

该查询先连接 student 和 apply 表，然后按 cName 和 major 的组合进行分组。对于每个分组，计算申请学生的 GPA 最小值和最大值。

GROUP BY 的注意事项

在 SELECT 子句中，通常只应包含分组属性和聚合函数。包含非分组属性可能导致不可预测的结果，因为系统会从该组中随机选择一个值返回。

-- 可能产生意外结果的查询
SELECT s.sID, sName, COUNT(DISTINCT a.cName), a.cName
FROM student s, apply a
WHERE s.sID = a.sID
GROUP BY s.sID;

在这个查询中，a.cName 不是分组属性，其返回值是未定义的（取决于数据库系统，可能返回组内一个随机值或报错）。

使用 HAVING 筛选分组结果

上一节我们学习了如何分组数据。本节中我们来看看如何对分组后的结果进行筛选。

HAVING 子句在 GROUP BY 之后应用，允许我们对聚合函数的结果设置条件。

基础 HAVING 查询

查找申请人数少于五人的大学。

SELECT cName
FROM apply
GROUP BY cName
HAVING COUNT(*) < 5;

该查询先按大学名称分组，然后只保留那些元组数量（即申请人数）少于 5 的分组。

HAVING 与子查询结合

查找数据库中所有专业，其中申请该专业的学生的最高 GPA 低于全体学生的平均 GPA。

SELECT a.major
FROM student s, apply a
WHERE s.sID = a.sID
GROUP BY a.major
HAVING MAX(s.GPA) < (SELECT AVG(GPA) FROM student);

该查询先连接表并按专业分组。在 HAVING 子句中，它检查每个专业的最高 GPA 是否小于子查询计算出的全体学生平均 GPA。

复杂查询与技巧

在 FROM 子句中使用子查询进行聚合

计算申请 CS 专业的学生与未申请 CS 专业的学生之间的平均 GPA 差值。

SELECT cs.avgGPA - noncs.avgGPA AS GPA_difference
FROM (SELECT AVG(GPA) AS avgGPA FROM student WHERE sID IN (SELECT sID FROM apply WHERE major=‘CS’)) cs,
     (SELECT AVG(GPA) AS avgGPA FROM student WHERE sID NOT IN (SELECT sID FROM apply WHERE major=‘CS’)) noncs;

该查询在 FROM 子句中创建了两个临时关系：cs（CS申请者的平均GPA）和 noncs（非CS申请者的平均GPA），然后在主查询中计算两者的差值。

列出所有学生及其申请大学数量（包括零申请者）

要列出所有学生，包括那些没有申请任何大学的学生，并显示其申请大学数量，可以使用 UNION 操作符。

-- 已申请大学的学生
SELECT s.sID, COUNT(DISTINCT a.cName)
FROM student s, apply a
WHERE s.sID = a.sID
GROUP BY s.sID
UNION
-- 未申请任何大学的学生
SELECT sID, 0
FROM student
WHERE sID NOT IN (SELECT sID FROM apply);

该查询首先找出已申请大学的学生及其申请的不同大学数量，然后与未申请任何大学的学生（申请数量为0）合并。

本节课中我们一起学习了 SQL 聚合操作的核心概念。我们从 MIN、MAX、AVG、SUM、COUNT 等基本聚合函数开始，理解了它们如何对多行数据进行汇总。接着，我们深入探讨了 GROUP BY 子句，它允许我们将数据划分为逻辑组并对每个组进行独立计算。最后，我们学习了 HAVING 子句，它用于对分组聚合后的结果进行条件筛选，这与针对单行数据的 WHERE 子句形成了对比。通过结合子查询、连接和集合操作，聚合功能成为了 SQL 中进行复杂数据分析的强大工具。

SQL 11：空值处理 🧩

在本节课中，我们将学习 SQL 中一个特殊且重要的概念——空值（NULL）。我们将了解空值的含义，探索它在查询中的行为，并学习如何正确地处理包含空值的数据。

空值简介

上一节我们介绍了基础的查询结构，本节中我们来看看数据中可能存在的特殊值——空值。

在关系数据库中，除非另有规定，任何属性的值都可以取一个特殊值 NULL。NULL 通常表示该值未定义或未知。

例如：

一个学生的 GPA 可能未知，我们会用 NULL 表示。
一个申请关系中的决定可能尚未做出，该决定值也可能是 NULL。

空值在查询中的行为

为了探索空值在查询中的影响，我们向示例的“学生”表中插入了两名 GPA 为 NULL 的新学生：Kevin 和 Lori。

以下是包含空值的学生表示例（在界面中，NULL 通常显示为空白）：

学生	GPA
Alice	3.9
Bob	3.2
Kevin
Lori

条件查询与空值

让我们运行几个查询，观察空值如何影响 WHERE 子句的结果。

查询1：查找 GPA > 3.5 的学生

SELECT * FROM students WHERE GPA > 3.5;

结果：返回 GPA 明确大于 3.5 的学生，不包含 Kevin 和 Lori。因为无法确定 NULL > 3.5 是否为真。

查询2：查找 GPA <= 3.5 的学生

SELECT * FROM students WHERE GPA <= 3.5;

结果：返回 GPA 明确小于等于 3.5 的学生，同样不包含 Kevin 和 Lori。因为无法确定 NULL <= 3.5 是否为真。

查询3：查找 GPA > 3.5 或 GPA <= 3.5 的学生

SELECT * FROM students WHERE GPA > 3.5 OR GPA <= 3.5;

结果：这个看似“永远为真”的逻辑表达式，仍然不会返回 GPA 为 NULL 的学生。因为对于 NULL，这两个比较运算的结果都是“未知”，而非“真”。

为了在条件中明确包含空值，我们需要使用 IS NULL 操作符。

查询4：查找所有学生（包含空值）

SELECT * FROM students WHERE GPA > 3.5 OR GPA <= 3.5 OR GPA IS NULL;

结果：这次查询将返回所有学生，包括 Kevin 和 Lori。IS NULL 是 SQL 中专门用于匹配空值的关键短语。

三值逻辑

涉及空值的 WHERE 子句评估基于三值逻辑：真（TRUE）、假（FALSE）、未知（UNKNOWN）。只有当整个 WHERE 条件最终评估为“真”时，元组才会被包含在结果中。

聚合函数与空值

空值与聚合函数（如 COUNT, SUM, AVG）的交互也需要特别注意。以下是 COUNT 函数与空值交互的例子。

假设我们最初有 12 名 GPA 非空的学生，后来加入了 GPA 为空的 Kevin 和 Lori。

查询5：统计 GPA 非空的学生数量

SELECT COUNT(GPA) FROM students;

结果：返回 12。COUNT(column) 只统计该列中非空值的数量。

查询6：统计不同的非空 GPA 数量

SELECT COUNT(DISTINCT GPA) FROM students;

结果：返回 7（假设在12个非空GPA中有7个不同的值）。COUNT(DISTINCT column) 同样不计算空值。

查询7：查看所有不同的 GPA（包含空值）

SELECT DISTINCT GPA FROM students;

结果：返回8行数据，其中包含一个 NULL 值。DISTINCT 会将 NULL 视为一个独立的值包含在结果集中。

关键差异：SELECT DISTINCT 会列出 NULL，而 COUNT(DISTINCT ...) 却不会统计它。这是处理空值时需要留意的微妙之处。

总结

本节课中我们一起学习了 SQL 中的空值（NULL）。

我们了解到：

NULL 表示未知或未定义的值。
在 WHERE 条件中，任何与 NULL 的比较运算（如 =, >, <）结果都是“未知”。要检查空值，必须使用 IS NULL 或 IS NOT NULL。
大多数聚合函数（如 COUNT, SUM, AVG）在执行计算时会自动忽略空值。
空值的行为可能带来非直观的结果（例如 DISTINCT 与 COUNT(DISTINCT) 的差异），因此在编写涉及可能为空的数据的查询时，必须格外小心，明确理解查询的预期行为。

处理空值是编写健壮、准确 SQL 查询的关键技能。

12：SQL 数据修改语句 🛠️

在本课程中，我们将学习 SQL 中用于修改数据库数据的核心语句，包括插入（INSERT）、删除（DELETE）和更新（UPDATE）数据。这些操作是管理和维护数据库内容的基础。

概述 📋

在之前的课程中，我们学习了如何查询数据。本节课程将重点转向如何修改数据。我们将通过具体的例子，学习三种主要的数据修改语句：INSERT、DELETE 和 UPDATE。我们将使用一个简单的“学院招生”数据库进行演示，该数据库包含 college、student 和 apply 三个表。

1. 插入数据（INSERT）

插入操作用于向数据库表中添加新的数据行。SQL 提供了两种主要的插入数据方法。

1.1 插入指定值

第一种方法是直接指定要插入的元组（行）的各个字段值。其基本语法如下：

INSERT INTO 表名 VALUES (值1, 值2, 值3, ...);

执行此命令后，一个具有指定值的新行将被添加到目标表中。

示例：向 college 表中添加一所新大学“卡内基梅隆大学”。

INSERT INTO college VALUES (‘Carnegie Mellon‘, ‘PA‘, 11500);

执行后，查询 college 表，可以看到新大学已被成功添加。

1.2 通过查询插入数据

第二种方法是通过 SELECT 查询语句生成一组元组，并将这组元组插入到目标表中。这要求查询结果的列结构与目标表完全匹配。

示例：让尚未申请任何学校的学生申请卡内基梅隆大学的计算机科学专业。

首先，找出未申请任何学校的学生：

SELECT sID FROM student
WHERE sID NOT IN (SELECT sID FROM apply);

然后，将此查询转化为插入语句，构造包含学生ID、大学名称、专业和决策（设为NULL）的元组：

INSERT INTO apply
SELECT sID, ‘Carnegie Mellon‘, ‘CS‘, NULL
FROM student
WHERE sID NOT IN (SELECT sID FROM apply);

执行后，这些学生的申请记录就被添加到了 apply 表中。

2. 删除数据（DELETE）

删除操作用于从表中移除满足特定条件的行。其基本语法如下：

DELETE FROM 表名 WHERE 条件;

WHERE 子句中的条件可以像 SELECT 语句中的条件一样复杂，可以包含子查询和聚合函数。

示例：删除所有申请了超过两个不同专业的学生。

首先，找到这些学生：

SELECT sID FROM apply
GROUP BY sID
HAVING COUNT(DISTINCT major) > 2;

然后，从 student 表中删除这些学生：

DELETE FROM student
WHERE sID IN (
    SELECT sID FROM apply
    GROUP BY sID
    HAVING COUNT(DISTINCT major) > 2
);

需要注意的是，某些数据库系统可能对 DELETE 语句中的子查询有限制。如果遇到限制，可以先将子查询结果存入临时表，再进行删除。

另一个示例：删除没有任何学生申请计算机科学专业的学院。

DELETE FROM college
WHERE cName NOT IN (
    SELECT cName FROM apply WHERE major = ‘CS‘
);

3. 更新数据（UPDATE）

更新操作用于修改表中现有行的数据。它可以同时更新一个或多个列。其基本语法如下：

UPDATE 表名
SET 列名1 = 表达式1, 列名2 = 表达式2, ...
WHERE 条件;

WHERE 子句用于筛选需要更新的行，SET 子句指定如何修改这些行的值。表达式和条件都可以非常复杂。

示例：接受所有GPA低于3.6且申请了卡内基梅隆大学的学生，并将其专业改为“经济学”。

UPDATE apply
SET decision = ‘Y‘, major = ‘economics‘
WHERE cName = ‘Carnegie Mellon‘
AND sID IN (SELECT sID FROM student WHERE GPA < 3.6);

复杂示例：将所有申请了EE（电气工程）专业且GPA最高的学生的专业改为“CSEE”（计算机科学与电子工程）。

UPDATE apply
SET major = ‘CSEE‘
WHERE major = ‘EE‘
AND sID IN (
    SELECT sID FROM student
    WHERE GPA >= ALL (
        SELECT GPA FROM student
        WHERE sID IN (SELECT sID FROM apply WHERE major = ‘EE‘)
    )
);

全局更新示例（无 WHERE 条件）：将所有学生的GPA设置为数据库中的最高GPA，并将所有学生的高中规模设置为最小值。

UPDATE student
SET GPA = (SELECT MAX(GPA) FROM student),
    sizeHS = (SELECT MIN(sizeHS) FROM student);

接受所有申请者：

UPDATE apply SET decision = ‘Y‘;

总结 🎯

在本课程中，我们一起学习了 SQL 中三种核心的数据修改操作：

INSERT：用于向表中添加新数据，可以通过直接指定值或通过 SELECT 查询结果来实现。
DELETE：用于从表中移除满足特定条件的数据行，条件可以包含复杂的子查询。
UPDATE：用于修改表中现有行的数据，可以同时更新多个列，并支持在 SET 和 WHERE 子句中使用子查询。

掌握这些语句，你就能有效地对数据库中的数据进行增、删、改操作，这是进行数据库管理和应用开发的重要基础。请务必通过实践来巩固对这些命令的理解。

13：关系数据库设计概述 🗂️

在本节课中，我们将学习如何为关系数据库设计良好的模式。我们将探讨为什么某些设计优于其他设计，并介绍一种通过分解大型关系来避免数据冗余和异常的理论方法。

设计目标与挑战

假设我们正在为一个应用程序构建数据库，需要确定存储数据的模式。数据库通常有多种可能的设计方案，其中一些设计远比其他设计更好。

那么，我们如何选择合适的设计呢？虽然人们通常使用高级工具来设计关系数据库，但直接理解关系设计本身以及设计工具背后的原理是非常有用的。

从学术角度看，关系数据库设计有一套完善的理论。

一个设计示例：学生申请数据库

让我们考虑为一个大学申请学生数据库设计模式。对于每个学生，我们想存储以下信息：社会安全号码、姓名、申请的大学、就读的高中、高中所在城市以及学生的爱好。

初始设计：单一关系

一种直接的方法是创建一个名为 apply 的单一关系，包含所有属性。

CREATE TABLE apply (
    ssn INT,
    name VARCHAR(255),
    college VARCHAR(255),
    high_school VARCHAR(255),
    hs_city VARCHAR(255),
    hobby VARCHAR(255)
);

现在，让我们看看如何用数据填充这个数据库。假设有一个学生（社会安全号码123，名叫Anne），她申请了三所大学，就读于两所高中，并有两个爱好。

为了表示Anne的所有信息，我们需要创建多个元组。例如，她申请斯坦福、就读帕洛阿尔托高中、爱好网球，这需要一个元组。她申请伯克利、就读同一所高中、同一爱好，这需要另一个元组。以此类推，最终需要12个元组来表示Anne的所有信息组合。

初始设计的问题

这个设计存在几种异常类型：

冗余：同一条信息被多次记录。例如，Anne的社会安全号码和姓名被记录了12次，她上帕洛阿尔托高中的事实被记录了6次。
更新异常：由于冗余，更新信息时可能无法保持一致性。例如，如果将Anne的爱好“小号”改为“短号”，但只更新了部分记录，数据库就会变得不一致。
删除异常：删除某些信息时，可能会意外删除其他信息。例如，如果删除所有“冲浪”爱好的记录，那么唯一爱好是冲浪的学生信息将被完全从数据库中移除。

改进的设计：分解关系

现在，让我们看看同一数据的另一种设计。我们创建五个不同的关系：

students(ssn, name)
applications(ssn, college)
schools(ssn, high_school)
locations(high_school, hs_city)
hobbies(ssn, hobby)

在这个设计中，上述三种异常都不会发生。信息没有冗余，更新和删除操作不会导致不一致或信息丢失。更重要的是，我们可以从这五个关系中重建出初始设计中的所有原始数据。

设计调整的考量

设计可以根据实际需求进行调整：

标识高中：如果仅凭高中名称不足以唯一标识一所学校（例如，不同城市可能有同名高中），那么更好的设计是将 schools 关系改为 schools(ssn, high_school, hs_city)，并去掉单独的 locations 关系。
隐私考虑：如果学生不希望所有大学都知道其全部爱好，我们可以修改设计，将爱好与特定大学关联：hobbies(ssn, college, hobby)。

这表明，最佳的关系数据库设计不仅取决于如何构建关系，还取决于数据的实际语义和业务需求。

设计方法论：通过分解进行规范化

上一节我们看到了分解如何改进设计。接下来，我们将形式化这一过程。

基本思路是：首先创建一个包含所有待存储属性的“超级关系”，然后根据数据的属性（约束）将其自动分解为更小的、更好的关系，同时保证不丢失任何信息。

这个过程称为规范化。系统将保证最终的关系集满足特定的范式要求，这些范式正是为了消除我们之前讨论的异常而定义的。

我们将主要关注两种范式：

Boyce-Codd范式：基于函数依赖。
第四范式：基于函数依赖和多值依赖。

所有满足第四范式的关系也一定满足Boyce-Codd范式，但反之则不成立。

注：第一范式要求关系中的值是原子的；第二、三范式如今较少讨论，第三范式是Boyce-Codd范式的一种轻微弱化形式。本课程将重点讲解最常用的Boyce-Codd范式和第四范式。

核心概念预览

在后续课程中，我们将深入讲解以下概念。这里先做一个简要介绍以激发理解。

函数依赖与Boyce-Codd范式

考虑一个简化的 apply 关系：apply(ssn, name, college)。

即使在这个小关系中，也存在冗余：如果学生123（Anne）申请了7所大学，那么她的社会安全号码和名字会被存储7次。

函数依赖：ssn -> name。这表示社会安全号码函数决定姓名，即相同的ssn总是对应相同的name。
Boyce-Codd范式要求：如果一个关系中有函数依赖 X -> Y，那么 X 必须是该关系的超键（即能唯一标识元组）。
在我们的例子中，ssn 不是 apply 关系的键（因为一个ssn会对应多个college），所以它不符合Boyce-Codd范式。
解决方案：根据函数依赖进行分解。我们可以将其分解为：
- student(ssn, name) （其中 ssn 是键）
- applies(ssn, college)

多值依赖与第四范式

考虑另一个关系：apply2(ssn, college, high_school)。

假设一个学生申请了C所大学，就读过H所高中。在这个设计中，我们将得到 C × H 个元组，因为每个大学都要与每所高中组合记录一次。这导致了乘法冗余。

多值依赖：ssn ->> college 和 ssn ->> high_school。这表示对于给定的ssn，其对应的college值和high_school值是相互独立的，并且所有组合都会出现。
这个关系可能没有违反Boyce-Codd范式（如果没有函数依赖），但它违反了第四范式。
第四范式要求：如果一个关系中有非平凡的多值依赖 X ->> Y，那么 X 必须是该关系的超键。
解决方案：根据多值依赖进行分解。我们可以将其分解为：
- applies_to(ssn, college)
- attended(ssn, high_school)
- 这样，元组数量就从 C × H 减少到了 C + H。

总结

本节课中，我们一起学习了关系数据库设计的基本目标和方法。

我们了解到，糟糕的设计会导致数据冗余、更新异常和删除异常。通过将包含所有信息的大型关系分解为更小的关系，可以消除这些异常。

这一分解过程可以基于数据的属性（如函数依赖和多值依赖）自动进行，并保证结果满足特定的规范化形式（如Boyce-Codd范式和第四范式），从而确保设计既高效又可靠。

在接下来的课程中，我们将深入探讨函数依赖、Boyce-Codd范式、多值依赖和第四范式的形式化定义、性质及分解算法。

14：07-02-功能依赖 🧩

在本节课中，我们将要学习数据库设计中的一个核心概念——功能依赖。功能依赖是理解关系数据库设计、数据存储优化和查询处理的基础。我们将从基本定义出发，逐步学习其形式化表示、相关规则、计算方法及其在数据库系统中的应用。

关系设计与功能依赖回顾

上一节我们介绍了通过分解进行关系设计的概念。其核心思想是，应用设计师首先定义一个包含所有信息的“超级关系”，然后系统根据指定的属性自动将其分解为多个关系。

最终的分解关系集将满足特定的范式，这意味着它们是结构良好的关系，没有数据异常，也不会丢失原始关系中的信息。这些分解所依据的属性规则，正是通过功能依赖来定义的。

系统利用功能依赖可以生成Boyce-Codd范式关系，或者利用多值依赖生成第四范式关系。因此，功能依赖是关系设计理论的关键组成部分。

功能依赖的广泛应用

功能依赖不仅仅用于关系设计，它在整个数据库系统中都有广泛的应用。

以下是功能依赖的几个主要用途：

关系设计：作为分解关系、实现范式的基础。
数据存储与压缩：系统在了解功能依赖后，可以更高效地存储和压缩数据。
查询优化：功能依赖作为键概念的推广，可用于推理查询逻辑，帮助系统优化查询执行计划。
理论与教学：功能依赖拥有完善的理论体系，是数据库课程中编写考题的常见主题。

本视频将重点讲解功能依赖作为一个通用概念，后续视频会将其与具体的分解设计联系起来。

示例数据库：大学申请系统

我们将使用一个大学申请数据库作为贯穿本课程的示例。该系统包含两个关系：

学生关系：包含学生个人信息和高中信息。
- 属性：Ssn（社会保障号码）， Sname（学生姓名）， Address（住址）， HS_code（高中代码）， HS_name（高中名称）， HS_city（高中所在城市）， GPA（平均绩点）， Priority（招生优先级）。
申请关系：包含学生的大学申请信息。
- 属性：Ssn（社会保障号码）， Cname（大学名称）， State（大学所在州）， Date（申请日期）， Major（申请专业）。

功能依赖的定义与形式化

让我们通过学生关系中的GPA和Priority属性来理解功能依赖。假设存在一条业务规则：学生的优先级由其GPA决定（例如，GPA>3.8为优先级1）。这意味着，任何两个GPA相同的学生，其优先级也必须相同。

我们可以将此概念形式化。对于关系R中的任意两个元组t和u，如果它们在属性集A上的值相等，则它们在属性集B上的值也必须相等。这被称为“A功能决定B”，记作 A → B。

其正式的逻辑定义如下：

∀ t, u ∈ R, if t[A] = u[A] then t[B] = u[B]

功能依赖的两边都可以是属性集合。设A = {A1, A2, ..., An}， B = {B1, B2, ..., Bm}，则依赖 A → B 表示：

∀ t, u ∈ R, if t[A1...An] = u[A1...An] then t[B1...Bm] = u[B1...Bm]

为简化表示，我们常用Ā和B̄来代表属性集A和B。

功能依赖示例

根据对现实世界数据的理解，我们可以为学生关系和申请关系指定一系列功能依赖。

以下是学生关系中可能存在的功能依赖：

Ssn → Sname：一个学生的社会保障号码唯一确定其姓名。
Ssn → Address：假设学生不搬家，其社保号唯一确定住址。
HS_code → HS_name, HS_city：高中代码唯一确定该高中的名称和所在城市。
HS_name, HS_city → HS_code：假设同一城市内高中名称不重复，则高中名称和城市的组合能唯一确定高中代码。
Ssn → GPA：一个学生有一个确定的GPA。
GPA → Priority：GPA决定招生优先级（根据既定规则）。
由Ssn → GPA和GPA → Priority，可推导出Ssn → Priority。

以下是申请关系中可能存在的功能依赖：

Cname → Date：假设每所大学有统一的申请截止日期。
Ssn, Cname → Major：假设一个学生对同一所大学只能申请一个专业。
Ssn → State：假设一个学生只能申请一个州内的大学（这是一个较强的约束示例）。

现实世界中的约束决定了哪些功能依赖在关系中成立。准确识别这些约束是进行良好关系设计的前提。

功能依赖与键

功能依赖是键概念的推广。在关系R中，如果一组属性K功能决定了R中的所有其他属性（即 K → R），并且K的任何真子集都不能功能决定R中的所有属性，那么K就是R的一个超键。如果K是极小的（即不含多余属性），那么K就是R的一个候选键。

例如，在学生关系中，如果我们发现属性集{Ssn, HS_code}的闭包包含了所有属性，那么{Ssn, HS_code}就是一个候选键。

功能依赖的分类与规则

功能依赖可以分为以下几类：

平凡函数依赖：如果 B ⊆ A，则 A → B 是平凡的。它总是成立。
非平凡函数依赖：如果 B ⊈ A，则 A → B 是非平凡的。
完全非平凡函数依赖：如果 A ∩ B = ∅，则 A → B 是完全非平凡的。这类依赖最有意义。

功能遵循一组推理规则，以下是几个基本规则：

自反律：若 B ⊆ A，则 A → B。
增广律：若 A → B，则 A C → B C（对于任意属性集C）。
传递律：若 A → B 且 B → C，则 A → C。
合并规则：若 A → B 且 A → C，则 A → B C。
分解规则：若 A → B C，则 A → B 且 A → C。

这些规则构成了Armstrong公理系统，是功能依赖理论推导的基础。

属性闭包及其计算

给定关系R及其功能依赖集F，属性集A的闭包（记作 A⁺）是指由A通过F中的功能依赖所能推导出的所有属性的集合。

计算属性闭包A⁺的算法如下：

初始化 result = A。
循环检查F中的每一个功能依赖 X → Y，如果 X ⊆ result，则将 Y 加入到 result 中。
重复步骤2，直到result不再发生变化。
最终的result即为 A⁺。

示例：计算学生关系中{Ssn, HS_code}的闭包。
已知功能依赖：

Ssn → Sname, Address, GPA
GPA → Priority
HS_code → HS_name, HS_city

计算过程：

result = {Ssn, HS_code}
应用依赖1：Ssn在result中，加入{Sname, Address, GPA}，result = {Ssn, HS_code, Sname, Address, GPA}
应用依赖3：HS_code在result中，加入{HS_name, HS_city}，result = {Ssn, HS_code, Sname, Address, GPA, HS_name, HS_city}
应用依赖2：GPA在result中，加入{Priority}，result = {Ssn, HS_code, Sname, Address, GPA, HS_name, HS_city, Priority}
此时result已包含所有属性，停止计算。因此，{Ssn, HS_code}⁺ 包含所有属性，说明{Ssn, HS_code}是一个超键。

属性闭包可用于：

判断是否为键：计算A⁺，若A⁺包含所有属性，则A是超键。
判断函数依赖是否成立：判断 A → B 是否由F逻辑蕴含，只需计算A⁺，并检查是否 B ⊆ A⁺。
计算函数依赖集的覆盖：寻找最小、完全非平凡的依赖集。

函数依赖集的覆盖与最小集

设F和G是两个函数依赖集。如果F逻辑蕴含G中的所有依赖，且G也逻辑蕴含F中的所有依赖，则称F和G是等价的，或称F覆盖G，G也覆盖F。

在数据库设计中，我们通常希望找到一个最小函数依赖集，它满足：

每个依赖的右边都是单个属性。
每个依赖的左边都是极小的（删除任何属性都会改变依赖集的闭包）。
删除任何一个依赖都会改变依赖集的闭包。

这样的最小集没有冗余，能最简洁地描述数据中的所有约束。

总结

本节课中我们一起学习了数据库中的核心概念——功能依赖。

我们首先回顾了功能依赖在关系模式分解设计中的核心作用。接着，我们形式化地定义了功能依赖 A → B，并通过大学申请数据库的示例展示了如何根据业务规则识别功能依赖。

我们探讨了功能依赖与键的关系，指出键是一种特殊的、能决定所有属性的功能依赖。然后，我们学习了功能依赖的分类（平凡、非平凡、完全非平凡）和基本的推理规则（如自反律、增广律、传递律）。

最后，我们介绍了属性闭包的概念及其计算算法。属性闭包是一个强大的工具，可用于验证功能依赖、寻找关系的键以及计算函数依赖集的覆盖。理解并掌握功能依赖，是学习后续数据库范式理论、进行有效的数据库设计和优化的坚实基础。

15：XML 查询语言入门 🧭

在本节课中，我们将要学习 XML 查询语言的基础知识，特别是 XPath 的核心概念和工作原理。我们将了解如何将 XML 数据视为树形结构，并使用路径表达式和条件来导航与查询数据。

现在让我们来讨论查询 XML 的话题。

首先，需要明确一点，查询 XML 远没有关系型数据库查询那么成熟。这背后有几个原因。XML 实际上要新得多。其次，它的设计不够简洁。XML 没有类似于关系型数据库查询所依赖的关系代数。

接下来，我们来谈谈 XML 查询语言的发展历程。第一个被开发的语言是 XPath。XPath 由路径表达式和条件构成。接下来开发的语言是 XSLT。XSLT 包含了 XPath 作为组件，但它还包括了转换功能，这也是 T 的含义。它还具备输出格式化的构造。XSLT 常用于将 XML 转换为 HTML 进行渲染。

最后，最新且最具表现力的语言是 XQuery。因此，它也包含了 XPath 作为组件，此外，它还是一种功能全面的查询语言。它在某种程度上与 SQL 最为相似。我们将按以下顺序介绍它们：首先是 XPath，然后是 XQuery，最后是 XSLT。

还有一些其他的语言，如 XLink 和 XPointer。这些语言是用于指定链接和指针的。它们也使用 XPath 语言作为组件。我们在这个视频中不会涉及这些内容。现在，我们将详细讨论 XPath、XQuery 和 XSLT。我们不会覆盖每个语言构造，但我们会讲解足够的内容，帮助你使用这些语言编写各种查询。

将 XML 视为树 🌲

为了理解 XPath 如何工作，我们可以将 XML 看作一棵树。我将绘制一下树结构，表示我们正在处理的书店数据的树编码。我们会将 bookstore 元素作为根节点，然后会有子元素，包含书店的书籍。我们可能还有另一本书。也许这边有一本杂志。

而在这些书籍中，你可能还记得我们有一些属性和子元素。例如，我们有 ISBN 号码。我们有作为属性写出的内容。还有价格。当然，还有书名。我们当然还列出了作者信息。显然，我不会在这里填充所有的子元素结构。我们这里只看一本书作为示例。

ISBN 号码位于树的叶子节点处。所以我们可以在这里放一个字符串值来表示这个叶子节点，也许是价格为 100，或者是标题“一个数据库系统的初步课程”。然后我们的作者有进一步的子元素。也许我们这里有两个作者子元素。我在下面做了一些简化，写了一个名字（first name）和姓氏（last name）。所以这里可能是“杰夫”（Jeff），“奥门”（Omen）等。

我们这么做的原因是为了能够将 XPath 中的表达式视为对树形结构的导航。具体来说，XML 的组成部分是描述在树形结构中向下导航，有时也涉及横向和向上导航的路径表达式。然后，我们还会有一些条件，用来评估并选择我们感兴趣的 XML 组件。

XPath 基本构造 🛠️

让我简单介绍一下 XPath 中的一些基本构造。我将使用这个小框，将构造放进去，然后解释它是如何工作的。

第一个构造就是简单的斜杠 /。斜杠用于指定根元素。所以我们会在 XPath 查询的开头加上斜杠，表示我们希望从根元素开始。斜杠也可以作为分隔符使用。因此，我们将编写路径，以便在树形结构中进行导航，并且在路径的各个元素之间加上斜杠。

下一个构造就是简单地写出元素的名称。我在这里写了 X，但我们可以写出其他的名称，比如 book。当我们在 XPath 表达式中写出 book 时，我们的意思是希望导航到 book 子元素，作为我们路径表达式的一部分。

我们还可以编写特殊的元素符号星号 *，星号可以匹配任何内容。所以如果我们写上 /*，那么我们将匹配当前元素的任何子元素。当我们执行 XPath 时，在编写路径表达式时，实际上有一种概念是我们处在某个特定的位置。所以我们可能已经从 book 导航到了这里，然后我们会继续导航。

如果我们想匹配一个属性，我们会写 @ 符号，然后是属性名称。例如，如果我们在 book 元素下，想要匹配到 ISBN 号，我们会在查询或路径表达式中写出 @ISBN。

我们看到单斜杠是用于导航一步。还有一个双斜杠构造 //。双斜杠会匹配当前元素的任何后代元素。例如，如果我们在 book 元素下，写上 //，我们将匹配 title、author、author 的 first-name 和 last-name 等每一个后代元素。实际上，我们也会匹配到自身。所以这个符号意味着任何后代元素，包括我们当前所处的元素。

条件与函数 📝

那么条件又该如何编写呢？如果我们想在路径中的当前位置评估一个条件，我们需要将它放在方括号 [] 中，并在其中写下条件。例如，如果我们希望价格小于 50，这就是我们可以放入方括号中的条件，前提是我们实际上是在属性中。

如果我们在导航的这一点上，现在我们不应将方括号中的条件与方括号中的数字混淆。如果我们在方括号中写入一个数字，例如写 [3]，这不是一个条件，而是匹配当前元素的第 n 个子元素。例如，如果我们在 authors 节点上，并且我们写 [2]，那么我们将匹配 authors 节点下的第二个子元素。

XPath 还包含很多内置函数。我将随机举出两个例子。有一个函数叫做 contains()。如果你写 contains()，然后写两个表达式，每个轴都有一个字符串值——这实际上是一个谓词——如果第一个字符串包含第二个字符串，它将返回 true。

作为第二个函数的例子，有一个叫做 name() 的函数。如果我们在路径中写入 name()，它将返回当前元素的标签名。

导航轴 🧭

我想谈的最后一个概念是被称为“导航轴”的内容。XPath 中有 13 个轴。轴实际上是一个关键词，允许我们在 XML 树中进行导航。

例如，一个轴被称为 parent。你可能已经注意到，当我们谈到基本构造时，大多数内容是关于向下遍历树的。如果你想向上遍历树，那么你可以使用父元素轴，它会告诉你返回到父元素。

有一个叫做 following-sibling 的轴。后续兄弟轴的作用是匹配当前元素的所有后续兄弟元素。如果我们有一棵树，并且我们正处于树的某个位置，接下来，后续兄弟轴会匹配树中当前元素之后的所有兄弟元素。

还有一个叫做 descendants 的轴。后代轴，顾名思义，匹配当前元素的所有后代。这与 // 不完全相同，因为需要提醒的是，// 也会匹配当前元素及其后代。实际上，XPath 中有一个导航轴叫做 descendants-and-self，它等同于 //。

顺便提一下，还有一个叫做 self 的轴，会匹配当前元素。这个轴可能看起来没有什么用处，但我们将在示例中看到它的应用场景，结合我们之前讨论过的 name() 函数，这将给我们当前元素的标签。

总结与建议 📚

最后补充一些细节。技术上，XPath 查询是在元素序列上操作并返回结果。这是它们的正式语义。对于 XML 文档和 XML 流，有一个规范将它们映射为元素序列。你会发现这非常自然。当我们运行 XPath 查询时，有时结果可以表示为 XML，但并不总是如此。

本节课介绍了 XPath。我们展示了如何将 XML 数据视为一棵树，然后将 XPath 看作是穿梭在树中的表达式，也可以用来评估条件。我们已经看过了一些路径表达式的构造方式，以及条件部分。我们也看过了一些内建函数，并且我介绍了导航轴的概念。

但是，学习和理解 XPath 的真正方法是运行一些查询。所以我强烈建议你观看下一个视频，它展示了如何在我们的书店数据上运行 XPath 查询。然后自己尝试一些查询。

16：XPath 查询实战教程 📚

在本课程中，我们将通过一个书店数据集的系列查询，来演示 XPath 的核心语法和功能。我们将从简单的路径导航开始，逐步深入到包含条件、函数和复杂逻辑的查询。

数据概览 📂

首先，我们来看看将要使用的 XML 数据。数据基于一个书店目录，结构如下：

根元素是 bookstore。
包含多个 book 和 magazine 元素。
每本书 (book) 包含属性（如 ISBN、price）和子元素（如 title、author、remark）。
每个作者 (author) 包含 first_name 和 last_name 子元素。
杂志 (magazine) 的结构与书籍类似。

数据示例包含了多本教材、补充书籍和几本杂志，用于演示各种查询场景。

基础路径导航 🧭

上一节我们介绍了数据集，本节中我们来看看如何使用 XPath 进行最基本的导航。

XPath 表达式通常从指定文档根目录开始，然后使用路径操作符逐级向下导航。单斜杠 / 表示直接子元素关系。

以下是几个基础路径查询示例：

查询所有书籍的标题:
```
doc("bookstoreQ.xml")/bookstore/book/title
```
这个表达式从根 bookstore 开始，找到其下的所有 book 子元素，再获取每个 book 下的 title 子元素。

查询所有书籍和杂志的标题:
我们可以使用类似正则表达式的语法 (book|magazine) 来匹配多种元素。
```
doc("bookstoreQ.xml")/bookstore/(book|magazine)/title
```

使用通配符 *:
星号 * 可以匹配任何元素名。
```
doc("bookstoreQ.xml")/bookstore/*/title
```
这个表达式会匹配 bookstore 下任何直接子元素的 title 子元素。

使用轴与通配符进行深度搜索 🔍

上一节我们学习了基础的子元素导航，本节中我们来看看如何搜索整个文档树。

双斜杠 // 是一个强大的操作符，它表示“当前节点或任意深度的后代节点”。结合通配符，可以实现灵活的搜索。

以下是相关示例：

搜索文档中任意位置的 title 元素:
```
doc("bookstoreQ.xml")//title
```
这等价于之前 /(book|magazine)/title 的结果。
匹配文档中的每一个元素:
```
doc("bookstoreQ.xml")//*
```
这个查询会返回 XML 树中从根元素开始的所有元素，通常结果集很大。

处理属性与简单条件 ⚙️

在掌握了元素导航后，我们来看看如何访问属性以及为查询添加简单的过滤条件。

属性使用 @ 符号访问。条件使用方括号 [] 添加，它会对当前路径节点集进行过滤。

以下是相关操作：

获取所有书籍的 ISBN 号:
直接返回属性节点有时会出错，通常我们提取其值。
```
doc("bookstoreQ.xml")/bookstore/book/@ISBN/data()
```

查找价格低于 $90 的书籍:
在 book 节点后添加条件 [price < 90]。
```
doc("bookstoreQ.xml")/bookstore/book[price < 90]
```
查找价格低于 $90 的书籍的标题:
在条件筛选后，可以继续向下导航。
```
doc("bookstoreQ.xml")/bookstore/book[price < 90]/title
```

基于子元素存在的条件:
在方括号中直接放入子元素名，表示检查该子元素是否存在。
```
doc("bookstoreQ.xml")/bookstore/book[remark]/title
```
此查询返回所有带有 remark 子元素的书籍的标题。

组合条件与常见错误 🧩

单一条件往往不能满足需求，本节我们学习如何组合多个条件，并注意一个常见的逻辑错误。

可以在方括号 [] 内使用 and、or 来连接多个条件。需要注意的是，路径表达式中的条件存在“隐式存在量化”，即只要存在一个节点满足条件，整个条件就为真。

以下是组合查询示例与陷阱：

查找价格低于 $90 且作者包含 “Omen” 的书籍标题:

doc(“bookstoreQ.xml”)/bookstore/book[price < 90 and author/last_name = “Omen”]/title

一个常见的错误模式:
假设我们想找作者是 “Jeffrey Widom” 的书。错误的写法可能如下：
```
//book[author/first_name=“Jeffrey” and author/last_name=“Widom”]
```
这个查询的实际含义是：找一本有作者名叫 “Jeffrey” 并且有作者（可以是另一个人）姓 “Widom” 的书。这可能导致错误匹配。
正确的写法:
我们需要确保名字和姓氏属于同一个 author 元素。这需要在条件内部再指定路径。
```
//book[author[first_name=“Jeffrey” and last_name=“Widom”]]
```
这个查询的含义是：查找存在一个 author 子元素，且该元素的 first_name 为 “Jeffrey” 同时 last_name 为 “Widom” 的书籍。

使用函数与轴进行高级查询 🚀

XPath 提供了丰富的内置函数（如 contains(), count(), name()）和轴（如 parent::, following-sibling::），用于编写更强大的查询。

以下是高级功能示例：

使用 contains() 函数:
查找备注中包含 “great” 一词的书籍标题。
```
//book[contains(remark, “great”)]/title
```

使用 parent:: 轴:
查找所有父元素名不是 “bookstore” 或 “book” 的元素（演示用途）。
```
//*[name(parent::*) != “bookstore” and name(parent::*) != “book”]
```

使用 following-sibling:: 轴实现“自连接”:
查找有同名书籍的杂志。这个查询巧妙地引用了文档两次。
```
//magazine[title = doc(“bookstoreQ.xml”)//book/title]
```

查找有重复标题的书籍或杂志:
通过检查是否存在前驱或后继兄弟节点有相同标题来实现。
```
//(book|magazine)[title = following-sibling::*/title or title = preceding-sibling::*/title]
```

实现“全称量化”逻辑 🎯

XPath 的条件默认是“存在量化”的。若要表达“所有作者都满足某条件”这类“全称量化”逻辑，需要借助函数进行变通。

一个常用的技巧是使用 count() 函数：满足条件的节点数量等于总节点数量。

以下是具体应用：

查找所有作者名字都包含字母 “J” 的书籍:
```
//book[count(author[contains(first_name, “J”)]) = count(author)]
```
这个查询统计名字含 “J” 的作者数，并检查它是否等于作者总数。
查找有作者姓 “Omen” 但没有作者姓 “Widom” 的书籍:
这是我们之前用简单条件无法正确表达的查询。
```
//book[author/last_name=“Omen” and count(author[last_name=“Widom”]) = 0]
```
首先要求存在姓 “Omen” 的作者，然后要求姓 “Widom” 的作者数量为 0。

课程总结 📝

在本节课中，我们一起学习了 XPath 的核心查询技术：

基础导航：使用 /、//、* 进行元素路径匹配。
属性访问：使用 @ 符号。
条件过滤：使用 [] 添加比较、存在性及位置条件。
组合条件：使用 and/or，并注意隐式存在量化带来的逻辑陷阱。
函数与轴：利用 contains()、count()、name() 等函数和 parent::、following-sibling:: 等轴进行复杂查询。
高级逻辑：通过 count() 函数变通实现“全称量化”查询。

通过这个从简单到复杂的演示，你应该已经能够使用 XPath 对 XML 数据执行各种查询操作。建议你使用提供的数据集进行练习，以巩固所学知识。

17：XQuery 入门教程 🧩

在本节课中，我们将学习 XQuery 查询语言及其在 XML 数据查询中的应用。XQuery 是一种功能强大的表达式语言，允许我们以组合的方式查询和转换 XML 数据。

XML 查询语言的发展背景

上一节我们介绍了 XML 查询的背景。查询 XML 的技术不如查询关系数据库的技术成熟，它更新且缺乏像关系代数那样清晰的理论基础。

以下是 XML 查询语言的发展序列：

XPath：一种用于在 XML 文档中定位节点的语言。
XSLT：一种用于转换 XML 文档的语言。
XQuery：一种功能更全面的 XML 查询和数据处理语言，也是本节课的重点。

XQuery 的核心概念：组合性

本节中我们来看看 XQuery 的核心特性——组合性。XQuery 是一种表达式语言，也被称为组合语言。这意味着，当我们对某种数据类型执行该语言的表达式时，表达式的结果将是相同类型的数据。

我们可以用以下模型来理解：

原始数据 -> [执行查询/表达式] -> 结果数据（同类型）

这个结果数据本身又可以作为输入，再次执行新的查询。这种特性允许我们将简单的查询组合成更复杂的表达式。

在关系模型中，组合操作的数据类型是关系。在 XML 和 XQuery 中，操作和返回的是元素序列。XQuery 的一种基本表达式类型就是 XPath，因此每个 XPath 表达式都是一个有效的 XQuery 查询。

FLWOR 表达式：XQuery 的“SQL”

XQuery 中最常用且最接近 SQL 风格的构造是 FLWOR 表达式。它得名于其主要的五个子句（For, Let, Where, Order by, Return）。

以下是 FLWOR 表达式的各个子句及其作用：

FOR：设置迭代变量。该子句的表达式会计算出一个集合，变量会依次绑定集合中的每个元素，并为每个元素执行后续查询。
LET：进行赋值操作。该子句的表达式只会计算一次，并将结果（即使是集合）一次性赋值给变量，不进行迭代。
WHERE：指定过滤条件，类似于 SQL 中的 WHERE 子句。
ORDER BY：对结果进行排序。
RETURN：定义查询最终返回的内容。这是 FLWOR 表达式中唯一必须的子句。

需要注意的是，FOR 和 LET 子句可以出现多次，并且可以交替使用，以设置多个变量。

在 XQuery 中直接构造 XML 结果

XQuery 允许我们将查询评估与直接编写期望的 XML 输出结构结合起来。其核心语法是使用大括号 {} 来包裹需要执行的查询表达式。

例如：

<结果>
  { 这里放置 XQuery 查询 }
</结果>

大括号内的查询会被执行，其结果将作为 XML 内容嵌入到外部的标签中，共同构成最终的输出。

课程总结与展望

本节课中，我们一起学习了 XQuery 语言的基础。我们了解了它的组合性特性，掌握了其核心的 FLWOR 表达式结构，并学习了如何直接构造包含查询结果的 XML 输出。

接下来，我们将通过实际演示，在书店 XML 数据上运行一系列 XQuery 示例。虽然 XQuery 是一门庞大的语言，但通过这些核心概念和结构，我们已经能够编写出非常强大的查询。

18：XSLT 查询语言入门教程 📖

在本课程中，我们将学习如何使用 XSLT（可扩展样式表语言转换）来查询和转换 XML 数据。XSLT 是一种基于模板匹配和转换的查询语言，其工作方式与 SQL、XPath 或 XQuery 有显著不同。

概述：什么是 XSLT？ 🤔

查询 XML 数据的技术不如查询关系型数据的技术成熟，因为它相对较新，并且缺乏像关系代数那样优雅的底层理论。我们已经学习过 XPath，它是为查询 XML 数据而开发的最早的语言之一。我们也讨论过 XQuery，它实际上是在 XSLT 之后开发的，但在风格上与 XPath 类似。

本节我们将要讲解的 XSLT 则非常不同。XSL 代表可扩展样式表语言，最初被提出用于样式化，但很快扩展了转换功能。如今，XSLT 比 XSL 本身应用得更为广泛。

XSLT 的工作原理 ⚙️

我们可以将 XSLT 视为一种查询语言。其工作流程如下：

一个 XSLT 处理器 接收输入。
输入包括 XML 数据（以文档或流的形式）和一个 XSLT 规范（该规范本身也是用 XML 格式编写的）。
处理器根据规范对数据进行转换。
输出一个结果，该结果也可以表示为 XML 文档或流。

这个过程可以类比传统的数据库查询处理：在关系型处理中，我们将数据和查询输入查询处理器，然后得到答案。因此，虽然 XSLT 是通过转换来实现的，但可以将其视为一种查询数据库的方式。不过，它的查询范式与我们习惯的方式有很大不同。

核心概念：将 XML 视为树 🌳

为了理解 XSLT 的规则和转换如何工作，将 XML 数据视为一棵树会非常有帮助。让我们以书店数据为例，将其构建成一棵树，就像我们初次学习 XPath 时那样。

根节点是 bookstore。
它有 book 和 magazine 等子元素。
对于 book，可能有 title 和 author 等子元素。
title 可能是一个叶节点，其值为“数据库系统入门”。
author 可能包含 first-name 和 last-name 子元素，其值为字符串。

这种树形结构是我们理解 XSLT 操作的基础。

XSLT 的基本操作：匹配与替换 🔍

XSLT 的核心思想是 匹配模板并进行替换。

匹配：我们编写一个表达式（通常使用 XPath）来查找 XML 树中的特定部分。例如，查找具有特定作者的书籍。
替换：一旦找到匹配的部分，我们就用模板中定义的结果替换整个子树。
- 例如，可以匹配书籍并用其书名替换整个书籍子树。
- 或者匹配作者，然后用其名字和姓氏的连接字符串替换整个作者子元素。

这种替换可以是递归进行的。例如，我们可以决定用一个不同的元素替换一本书，然后递归地将我们的模板应用到它的子元素上。

XSLT 语言还具有提取值的能力（通常使用 XPath 表达式），并包含一些类似编程语言的构造，如 for-each 循环和 if 条件语句。

实战演示：XSLT 示例 🖥️

现在让我们通过实际示例来了解 XSLT 的应用。演示环境如下：

左上角窗口：包含要查询的 XML 文档（书店数据）。
右上角窗口：编写 XSLT 模板的地方。
底部窗口：显示转换后的结果。

以下是几个关键示例：

示例 1：简单模板匹配

这个示例将查找 book 和 magazine 子元素，并用它们各自的标题元素进行替换。

XSLT 模板核心代码：

<xsl:template match="book">
  <book-title>
    <xsl:value-of select="title"/>
  </book-title>
</xsl:template>
<xsl:template match="magazine">
  <magazine-title>
    <xsl:value-of select="title"/>
  </magazine-title>
</xsl:template>

结果：输出所有书籍和杂志的标题。

示例 2：条件匹配与默认文本处理

这个示例只匹配价格低于 90 的书籍，并将其复制到结果中。

XSLT 模板核心代码：

<xsl:template match="book[@price < 90]">
  <xsl:copy-of select="."/>
</xsl:template>

初次运行会发现结果中包含了许多多余的文本节点。这是因为 XSLT 的默认行为会将未被任何模板匹配的元素的文本叶子节点值串联输出。

解决方法：添加一个匹配文本节点并返回空的模板。

<xsl:template match="text()"/>

添加此模板后，无关的文本即被移除。

示例 3：模板优先级

当多个模板匹配同一元素时，XSLT 会根据模板的优先级来决定使用哪一个。通常，更具体的模板（匹配条件更严格）优先级更高。

例如，有两个模板：

匹配 book[@price < 90]（复制书籍）。
匹配 book（删除书籍）。

由于第一个模板更具体（增加了价格条件），它将获得更高优先级，因此价格低于90的书籍会被复制，而其他书籍则被第二个模板删除。

如果两个模板特异性相同（例如，都使用等价的条件），则后出现的模板可能被优先应用，但这可能导致非预期的模糊匹配警告。

示例 4：复制与重构整个文档

有多种方式可以复制整个 XML 文档。

最简单的方式：匹配根元素并复制。

<xsl:template match="/">
  <xsl:copy-of select="."/>
</xsl:template>

更通用的递归方式：匹配任何元素，复制它，并递归应用模板到其子元素。这种方式允许我们在复制整体结构的同时，添加额外的规则来修改特定部分。

<xsl:template match="*|@*|text()">
  <xsl:copy>
    <xsl:apply-templates select="*|@*|text()"/>
  </xsl:copy>
</xsl:template>

例如，我们可以在此基础上添加规则，将 ISBN 属性改为子元素，或者将作者的 first-name 和 last-name 子元素改为属性，从而实现文档结构的重构。

示例 5：编程式转换与 HTML 输出

XSLT 可以像编程一样使用循环、排序和条件判断，并生成 HTML 等格式。

以下示例将书籍数据转换为一个 HTML 表格，按价格排序，并将价格低于90的书籍标题以斜体显示。

XSLT 模板核心代码：

<xsl:template match="/">
  <html>
    <body>
      <table border="1">
        <tr><th>Title</th><th>Price</th></tr>
        <xsl:for-each select="bookstore/book">
          <xsl:sort select="@price"/>
          <xsl:if test="@price < 90">
            <tr>
              <td><i><xsl:value-of select="title"/></i></td>
              <td><xsl:value-of select="@price"/></td>
            </tr>
          </xsl:if>
        </xsl:for-each>
      </table>
    </body>
  </html>
</xsl:template>

结果：生成一个格式良好的 HTML 表格，展示了 XSLT 在数据呈现方面的强大能力。

示例 6：复杂内容替换

我们可以使用多个模板或单个复杂模板来实现精细的内容替换。

目标：将名为“Jennifer WIDM”的作者替换为单个元素“Ms. WIDM”。

方法一（使用两个模板）：

匹配 first-name 值为“Jennifer”的元素，并移除它（空模板）。
匹配 last-name 值为“WIDM”的元素，将其替换为 <name>Ms. WIDM</name>。

方法二（使用单个模板）：
直接匹配 author[first-name='Jennifer' and last-name='WIDM']，并将其整个替换为新的 author 元素，其内容为 Ms. WIDM。

两种方法都能达到相同的转换效果。

总结 📝

在本课程中，我们一起学习了 XSLT 查询语言的基础知识和核心概念：

工作原理：XSLT 基于模板匹配和替换，将 XML 输入转换为新的输出（如 XML、HTML）。
核心操作：包括使用 XPath 进行匹配、替换子树、递归应用模板，以及使用 for-each、if 等编程构造。
重要特性：
- 需要注意默认的文本节点处理行为，通常需要显式模板来管理。
- 当多个模板匹配相同数据时，理解模板优先级规则至关重要。
- XSLT 非常适合用于数据转换和结构重构，也能生成用于展示的格式（如 HTML）。

XSLT 功能强大且灵活，虽然其范式与声明式查询语言不同，但一旦掌握，它能高效地处理复杂的 XML 数据查询与转换任务。

19：UML 数据建模 🗺️

在本节课中，我们将学习统一建模语言（UML）在数据建模领域的应用。UML 是一种强大的图形化建模语言，它不仅用于软件设计，也包含一个专门用于数据库设计的子集。我们将重点介绍如何使用 UML 的五个核心概念来构建高层次的数据模型，这些模型最终可以自动转换为关系数据库模式。

概述：什么是 UML 数据建模？

统一建模语言（UML）是一个庞大的语言体系，用于软件和系统的可视化建模。数据建模是其中的一个重要领域，它关注如何为一个应用程序表示和组织数据。

我们已经详细讨论过关系数据模型和 XML 数据模型。然而，在实际的数据库设计过程中，设计师通常会使用一种更高层次、专门用于设计的模型。这些模型（如 UML 数据模型）本身并不由数据库系统直接实现，而是通过一个翻译过程，被转化为数据库系统（如关系型数据库管理系统 RDBMS）能够理解和执行的模型。

这个过程可以形象地理解为：设计师在 UML 层面绘制图形化的“蓝图”，然后通过自动化工具将其“翻译”成具体的关系表。

核心概念一：类 📦

在 UML 中，类是一个核心概念，它同样用于程序设计和数据建模。一个类由名称、属性和方法组成。在数据建模的上下文中，我们主要关注数据，因此会强调属性和主键的概念，而通常忽略方法部分。

一个类在 UML 图中被表示为一个矩形框。框的顶部是类名，中间部分列出属性，底部（在数据建模中通常省略）用于列出方法。我们可以使用 PK 来指明哪个属性是主键。

示例：学生类与大学类

假设我们有一个大学招生数据库，其中包含“学生”和“大学”两类信息。

学生类 (Student):
- 属性：studentID (PK), studentName, GPA
大学类 (College):
- 属性：collegeName (PK), state

在 UML 图中，它们看起来像这样（此处用文字描述）：

[学生]
---------
studentID (PK)
studentName
GPA

[大学]
---------
collegeName (PK)
state

类的设计非常直观，它们看起来很像关系数据库中的表，并且可以直接转换为关系模式。

核心概念二：关联 🔗

上一节我们介绍了如何用类表示实体，本节我们来看看如何表示实体之间的关系。关联用于捕捉两个不同类的对象之间的连接。

示例：学生申请大学

在学生类和大学类之间，我们可以画一条线来表示“申请”关系。我们可以为这条线命名，例如“applied”。如果需要表示方向（例如学生申请大学），可以在线上添加一个小箭头。

基数约束

关联的一个重要方面是基数，它描述了一个类的对象可以与另一个类的多少个对象相关联。这可以捕捉一对一、一对多、多对多等关系。

基数在 UML 图中通过在线段两端标注 M..N 的格式来指定，表示“至少 M 个，最多 N 个”。有一些特殊的缩写：

* 是 0..* 的缩写，表示“零到任意多个”。
单独的 1 是 1..1 的缩写。
如果不标注，默认是 1..1。

示例：为“申请”关系添加基数

假设我们规定：

每个学生必须至少申请1所大学，且最多申请5所大学。
每所大学收到的申请数量没有下限，但最多不能超过20,000份。

那么，在学生端的基数应为 1..5，在大学端的基数应为 0..20000。

基数与关系术语的对应

以下是基数约束如何对应常见的关系术语：

一对一 (One-to-One): 两边都是 0..1。
完整一对一: 两边都是 1..1。
多对一 (Many-to-One): 一端是 *，另一端是 0..1。
完整多对一: 一端是 1..*，另一端是 1..1。
多对多 (Many-to-Many): 两边都是 *。
完整多对多: 两边都是 1..*。

核心概念三：关联类 🧩

有时，关联本身也具有需要记录的属性。这时，我们就需要使用关联类。关联类看起来像一个普通的类，但它通过一条虚线连接到关联线上。

示例：为申请添加详细信息

在学生“申请”大学这个关联中，我们可能想记录“申请日期”和“录取决定”。仅仅使用关联无法存储这些信息。我们可以创建一个名为“AppInfo”的关联类，它拥有 applicationDate 和 decision 属性，并将其连接到“申请”关联线上。

重要说明：
UML 的关联默认假设两个特定对象之间最多只有一个关联实例。如果一个学生可以向同一所大学申请多次（例如申请不同专业），则需要用更复杂的方式（例如引入单独的“申请”类并与学生、大学分别关联）来建模，而不能简单地用同一个关联类表示。

何时不需要关联类？
考虑一个关联，如果一端（例如学生端）的基数是 1..1（即每个学生只关联一所大学），那么关联类的属性（申请日期、决定）实际上可以直接作为学生类的属性，因为每个学生只对应一份申请信息。

自关联
关联也可以发生在一个类与其自身之间。例如，“学生”类可以有一个“兄弟姐妹”关联，连接两个学生对象。我们可以在关联两端使用标签（如“兄/姐”和“弟/妹”）和基数（如两边都是 *）来更清晰地描述这种关系。

核心概念四：子类 🎯

子类用于表示类之间的“是一种”关系，即特化与泛化。子类继承超类的所有属性，并可以拥有自己特有的属性。

示例：学生的子类

假设我们将“学生”作为超类，并定义几个子类：

外国学生 (ForeignStudent): 特有属性 countryOfOrigin。
国内学生 (DomesticStudent): 特有属性 state, socialSecurityNumber。
AP学生 (APStudent): 无特有属性，但只有AP学生可以与“AP课程”类关联。

“AP课程”类可能有属性 courseNumber (PK), title, credits。AP学生与AP课程之间通过“参加”关联连接，并且可以有一个关联类“APInfo”来记录 yearTaken 和 grade 等信息。

子类关系的性质

子类关系有两个重要性质：

完整性: 如果超类中的每个对象都至少属于一个子类，则关系是完整的，否则是不完整的（或部分的）。
互斥性: 如果超类中的每个对象最多只属于一个子类，则关系是互斥的（或独占的），否则是重叠的。

这些性质可以组合，例如：完整且互斥、完整且重叠、不完整且互斥、不完整且重叠。

示例分析：
在我们的例子中，如果每个学生要么是外国学生，要么是国内学生（完整），并且一个学生可以同时是国内学生和AP学生（重叠），那么这就是一个完整且重叠的子类关系。在UML图中，可以在子类连接处用 {complete, overlapping} 标注。

核心概念五：组合与聚合 ⚙️

最后，我们来看两个特殊的关联类型：组合和聚合。请注意，这里的“聚合”与SQL中的聚合函数毫无关系。

组合

组合表示一种强烈的“拥有”关系，其中一个类的对象是另一个类对象不可分割的一部分。在UML中，组合通过在关联的“所有者”端画一个实心菱形来表示。

示例：学院与系
一个“系”完全属于一个“学院”。如果学院不存在了，其下属的系也就没有意义了（在业务逻辑上）。这里，“学院”是所有者，关联末端是实心菱形。组合隐含了“系”端是 1..1 的基数（每个系属于且仅属于一个学院）。

聚合

聚合表示一种较弱的“整体-部分”关系，部分可以独立于整体而存在。在UML中，聚合通过在关联的“整体”端画一个空心菱形来表示。

示例：学院与公寓楼
一些“公寓楼”可能由某个“学院”拥有或管理，但并非所有公寓楼都如此。公寓楼可以独立存在，即使没有关联的学院。这里，“学院”是整体，关联末端是空心菱形。

总结 📝

本节课我们一起学习了UML数据建模的五个核心概念：

类：表示实体及其属性。
关联：表示实体之间的关系，可通过基数约束定义关系类型。
关联类：为关联本身添加属性。
子类：表示特化与泛化关系，具有完整性和互斥性。
组合与聚合：表示两种不同强度的“整体-部分”关系。

UML数据建模提供了一种图形化、高层次的方法来设计数据库。它的最大优势在于，这些设计通常可以自动或半自动地转换为标准的关系数据库模式，这将是下一节课的主题。通过掌握这些概念，你可以更清晰、更系统地进行数据库结构设计。

20：参照完整性约束 🧩

在本节课中，我们将要学习一种非常重要的数据库约束类型——参照完整性。我们将了解它的定义、作用、如何声明以及数据库系统如何强制执行它，以确保数据之间引用的有效性。

概述

参照完整性是关系型数据库中的一种关键约束。它确保一个关系（表）中某个属性（列）的值，必须存在于另一个关系（表）的指定属性中。这类似于编程中确保指针不指向无效内存地址的概念。通过本课，你将理解参照完整性的核心概念、如何定义它，以及当数据被修改时，系统如何处理潜在的违规情况。

什么是参照完整性？

上一节我们介绍了完整性约束的基本概念，本节中我们来看看参照完整性的具体定义。

在关系型数据库中，一个元组（行）引用另一个元组是通过指定属性值来实现的。参照完整性规定这些引用的值必须是有效的。

例如，在一个包含学生、大学和申请的数据库中，申请表中的学生ID值必须存在于学生表中，大学名称值必须存在于大学表中。这保证了每一条申请记录都对应一个真实的学生和一所真实的大学。

参照完整性约束可以形式化地描述为：如果关系R的属性A参照关系S的属性B，那么R.A中的每一个值都必须在S.B中存在对应值。

公式表示：
∀r ∈ R, ∃s ∈ S 使得 r.A = s.B

参照完整性的方向与外键

参照完整性具有方向性。我们通常说“引用表”参照“被引用表”。在引用表中的参照属性被称为外键。

以下是关于外键的几个关键点：

术语：参照完整性约束常被称为外键约束。
被引用属性要求：被参照的属性（如S.B）通常要求是其所在表的主键，或者至少被声明为UNIQUE。这主要是为了实现的效率。
复合外键：外键可以由多个属性共同组成，就像主键可以是复合主键一样。

可能违反约束的操作

并非所有数据库操作都会违反参照完整性。我们需要识别哪些操作可能带来风险。

以下是可能违反参照完整性约束的几种数据修改操作：

向引用表插入数据：例如，向申请表插入一条记录，但其学生ID在学生表中不存在。
从被引用表删除数据：例如，从学生表删除一个学生，但申请表中仍有记录引用该学生的ID。
更新引用表的外键值：例如，将申请表中的某个学生ID改为一个不存在的值。
更新被引用表的主键值：例如，更改大学表中的大学名称，导致申请表中对旧名称的引用失效。

对于在引用表上的插入或更新操作，如果导致违反约束，数据库系统通常会直接报错并拒绝执行该操作。

处理违反约束的策略：ON DELETE 与 ON UPDATE

对于在被引用表（如学生、大学）上进行的、可能导致违反约束的删除或更新操作，数据库系统提供了几种自动处理策略。这些策略在定义外键约束时通过ON DELETE和ON UPDATE子句指定。

以下是三种主要的处理策略：

RESTRICT / NO ACTION（限制/无动作）：默认行为。如果删除或更新被引用表的行会导致违反参照完整性，则操作被禁止，并产生错误。
SET NULL（设为空）：如果删除或更新被引用表的行，系统会自动将引用表中所有对应外键列的值设置为NULL。这要求外键列允许为NULL。
CASCADE（级联）：如果删除被引用表的行，则引用表中所有引用该行的记录也会被自动删除。如果更新被引用表的主键值，则引用表中所有对应外键值会被自动更新为新值。级联操作可能会产生连锁反应。

实践演示：定义与测试参照完整性

现在，让我们通过SQL代码来实际定义和测试参照完整性约束。

首先，我们创建被引用的学生表和大学表。

CREATE TABLE 学生 (
    SID INT PRIMARY KEY,
    ... -- 其他属性
);

CREATE TABLE 大学 (
    Cname VARCHAR(50) PRIMARY KEY,
    ... -- 其他属性
);

接着，我们创建申请表，并声明其外键约束。这里展示了基本的约束声明方式。

CREATE TABLE 申请 (
    SID INT,
    Cname VARCHAR(50),
    ... -- 其他属性如专业、决定等
    FOREIGN KEY (SID) REFERENCES 学生(SID),
    FOREIGN KEY (Cname) REFERENCES 大学(Cname)
);

如果尝试先向申请表插入数据，而引用的学生或大学不存在，操作将会失败。

-- 这将失败，因为学生123和大学‘Stanford’尚不存在
INSERT INTO 申请 VALUES (123, ‘Stanford‘, ...);

正确的顺序是先插入被引用表的数据，再插入引用表的数据。

INSERT INTO 学生 VALUES (123, ...);
INSERT INTO 大学 VALUES (‘Stanford‘, ...);
INSERT INTO 申请 VALUES (123, ‘Stanford‘, ...); -- 现在成功了

尝试删除被引用的数据（如学生表中ID为123的记录），如果申请表中有记录引用它，默认的RESTRICT行为会阻止删除。

DELETE FROM 学生 WHERE SID = 123; -- 默认情况下会失败

实践演示：使用 SET NULL 和 CASCADE

我们可以定义更灵活的外键行为。以下示例展示了如何定义ON DELETE SET NULL和ON UPDATE CASCADE。

CREATE TABLE 申请 (
    SID INT,
    Cname VARCHAR(50),
    ...,
    FOREIGN KEY (SID) REFERENCES 学生(SID) ON DELETE SET NULL,
    FOREIGN KEY (Cname) REFERENCES 大学(Cname) ON UPDATE CASCADE
);

插入一些测试数据后，我们进行以下操作：

测试 ON DELETE SET NULL：删除一个被申请表引用的学生。删除后，申请表中对应记录的SID将自动变为NULL，而记录本身保留。
测试 ON UPDATE CASCADE：更新大学表中的大学名称（如将‘Berkeley‘改为‘Berserkley‘）。更新后，申请表中所有引用旧名称‘Berkeley‘的记录，其Cname字段会自动更新为新名称‘Berserkley‘。

高级示例：表内引用与级联删除

参照完整性不仅限于表之间，也可以在单个表内部定义。这个复杂的例子展示了表内引用、复合外键以及级联删除的连锁反应。

我们创建一个表T，其属性(A, B)是主键，并定义属性(B, C)参照同一个表的(A, B)，且删除时级联。

CREATE TABLE T (
    A INT,
    B INT,
    C INT,
    PRIMARY KEY (A, B),
    FOREIGN KEY (B, C) REFERENCES T(A, B) ON DELETE CASCADE
);

插入数据形成一条引用链：(1,1,1) <- (1,1,2) <- (2,1,3) <- (3,2,4) ... 每个元组的(B,C)值都引用前一个元组的(A,B)值。

当我们删除链头的第一个元组(A=1, B=1)时，由于设置了ON DELETE CASCADE：

引用(1,1)的元组(1,1,2)会被删除。
接着，引用(1,1,2)的(A,B)值(2,1)的元组(2,1,3)又会被删除。
这个连锁反应会一直持续，直到整张表变空。

这个例子生动地展示了级联操作的传播效应。

总结

本节课中我们一起学习了参照完整性约束的核心知识。我们了解到：

参照完整性确保了数据库中外键引用的有效性，是维护数据一致性的基石。
外键约束具有方向性，并可以指向自身（表内引用）。
插入、更新、删除操作都可能违反参照完整性。
通过ON DELETE和ON UPDATE子句，我们可以指定违反约束时的自动处理策略：禁止操作（RESTRICT）、设为空值（SET NULL）或级联执行（CASCADE）。
参照完整性约束还会影响表的删除操作，必须先删除依赖它的表，才能删除被引用的表。

在实际数据库设计中，合理地使用参照完整性约束，可以让数据库系统自动帮我们维护复杂的业务规则和数据关联，是构建健壮应用的重要一环。

21：触发器实战教程（第一部分）📘

在本课程中，我们将学习数据库触发器的核心概念，并通过一系列实际演示来探索其工作原理。触发器是数据库管理系统中的一种强大工具，它能在特定事件发生时自动执行预定义的操作。我们将重点介绍插入、删除和更新事件的前触发器与后触发器，并展示如何访问修改前后的数据。

触发器概述与系统差异 🔍

上一节我们介绍了课程目标，本节中我们来看看不同数据库系统对触发器的支持情况。

虽然触发器遵循SQL标准，但没有任何数据库系统完全实现该标准。各系统在语法和行为上存在差异，因此理解所使用的特定系统至关重要。

以下是当前主要开源数据库系统对触发器的支持情况：

PostgreSQL：最接近SQL标准，支持行级和语句级触发器，并能访问新旧表数据，但语法较为独特。
SQLite：仅支持行级触发器，且触发器在每次行修改后立即激活（与标准的语句结束后激活不同）。它通过预定义的 OLD 和 NEW 变量访问行数据，不支持访问整个新旧表。
MySQL：功能限制较多，仅支持行级触发器和即时激活。每个表对每种事件（如INSERT）只能定义一个触发器，且触发器链式调用能力有限。

本教程的演示将基于 SQLite 进行。

演示准备与第一个触发器 ✨

在开始具体演示前，我们需要准备好数据库环境。我们将使用一个简单的大学招生数据库，包含 college（大学）、student（学生）和 apply（申请）三个表。

现在，让我们创建第一个触发器。这个触发器将在向学生表插入数据时被激活。

触发器功能：当插入一名GPA在3.3到3.6之间的学生时，自动为该生生成两条申请记录，分别申请斯坦福大学的地质专业和麻省理工学院的生物专业。

触发器代码：

CREATE TRIGGER auto_apply
AFTER INSERT ON student
FOR EACH ROW
WHEN (NEW.gpa > 3.3 AND NEW.gpa <= 3.6)
BEGIN
    INSERT INTO apply VALUES (NEW.sid, ‘Stanford‘, ‘geology‘, NULL);
    INSERT INTO apply VALUES (NEW.sid, ‘MIT‘, ‘biology‘, NULL);
END;

代码解释：

CREATE TRIGGER auto_apply：创建名为 auto_apply 的触发器。
AFTER INSERT ON student：指定触发事件为在 student 表执行 INSERT 操作之后。
FOR EACH ROW：声明为行级触发器（在SQLite中可省略，因默认即为行级）。
WHEN (...)：设置触发条件，仅当新插入行的 gpa 字段值满足条件时执行操作。
NEW.sid：引用被插入行的 sid（学号）值。

演示操作：

插入学生Kevin（GPA=3.5）和Lori（GPA=3.8）。
查询发现，只有Kevin被自动添加了两条申请记录。
执行一个批量插入，复制所有现有学生（ID递增），导致所有GPA在范围内的新记录都自动生成了申请。

实现参照完整性：级联删除与更新 ⛓️

上一节我们看到了一个用于自动生成数据的触发器，本节中我们来看看如何使用触发器来维护数据之间的关联，即参照完整性。

模拟级联删除

当从学生表删除一条记录时，我们希望自动删除申请表中所关联的该学生的所有申请记录。

触发器代码：

CREATE TRIGGER cascade_delete
AFTER DELETE ON student
FOR EACH ROW
BEGIN
    DELETE FROM apply WHERE apply.sid = OLD.sid;
END;

代码解释：

AFTER DELETE ON student：在 student 表执行 DELETE 操作后触发。
OLD.sid：引用被删除行的 sid 值。

演示操作：删除所有学号大于500的学生后，查询发现申请表中学号大于500的记录也被自动删除。

模拟级联更新

当更新大学表的名称时，我们希望自动更新申请表中所有引用该旧名称的记录。

触发器代码：

CREATE TRIGGER cascade_update
AFTER UPDATE OF cname ON college
FOR EACH ROW
BEGIN
    UPDATE apply SET cname = NEW.cname WHERE apply.cname = OLD.cname;
END;

代码解释：

AFTER UPDATE OF cname ON college：仅在更新 college 表的 cname 字段后触发，提高了效率。
OLD.cname 和 NEW.cname：分别引用更新前和更新后的大学名称。

演示操作：将Stanford更新为Farm，将Berkeley更新为Berserkly后，申请表中对应的大学名称也被自动更新。

实现键约束：唯一性保障 🔑

除了维护表间关系，触发器还可以用来实现更复杂的约束，例如保证表中某个字段的唯一性（类似主键约束）。

以下是两个触发器，分别在插入前和更新前检查大学名称是否重复，如果重复则阻止操作。

防止重复插入的触发器：

CREATE TRIGGER check_unique_insert
BEFORE INSERT ON college
FOR EACH ROW
WHEN EXISTS (SELECT * FROM college WHERE cname = NEW.cname)
BEGIN
    SELECT RAISE(IGNORE);
END;

防止重复更新的触发器：

CREATE TRIGGER check_unique_update
BEFORE UPDATE OF cname ON college
FOR EACH ROW
WHEN EXISTS (SELECT * FROM college WHERE cname = NEW.cname)
BEGIN
    SELECT RAISE(IGNORE);
END;

代码解释：

BEFORE：在操作执行之前触发，这样可以在实际修改数据前进行检查和干预。
RAISE(IGNORE)：这是SQLite特有的语句，用于中止当前操作并忽略错误。
WHEN EXISTS (...)：条件检查是否存在同名的大学记录。

演示操作：

尝试插入已存在的MIT会被触发器阻止，而插入不存在的Stanford则成功。
尝试将Farm更新为已存在的Stanford会被阻止，而将其更新为不存在的Stanford2则成功。

触发器链与复杂业务逻辑 🧩

触发器可以相互激活，形成链式反应，用于实现复杂的业务逻辑。本节我们将看到一个监控申请数量并自动更新大学状态的例子。

申请数量监控触发器：

CREATE TRIGGER app_monitor
AFTER INSERT ON apply
FOR EACH ROW
WHEN ( (SELECT COUNT(*) FROM apply WHERE cname = NEW.cname) > 10 )
BEGIN
    UPDATE college SET cname = cname || ‘-done‘ WHERE cname = NEW.cname;
END;

代码解释：

在每次向apply表插入新申请后触发。
检查该大学的申请总数是否超过10。
如果超过，则在大学名称后追加“-done”后缀。

演示的链式反应：

插入GPA符合条件的学生，会激活第一个auto_apply触发器，从而向apply表插入申请记录。
向apply表插入记录，激活了app_monitor触发器。
app_monitor触发器修改了college表的名称。
修改college表名称，又激活了之前定义的cascade_update触发器，将apply表中的旧大学名更新为新大学名。
这个过程清晰地展示了触发器如何一环扣一环地执行。

前触发器与后触发器的交互 ⚖️

一个操作可以同时激活多个触发器，特别是既有BEFORE触发器又有AFTER触发器时。它们的执行顺序和交互需要仔细设计。

以下是两个作用于student表插入操作的触发器：

前触发器（数据校验）：

CREATE TRIGGER check_hs_size
BEFORE INSERT ON student
FOR EACH ROW
WHEN (NEW.sizehs < 100 OR NEW.sizehs > 5000)
BEGIN
    SELECT RAISE(IGNORE);
END;

后触发器（业务操作）：

-- 即之前定义的 auto_apply 触发器

演示操作：尝试插入三名学生，其中两名的高中人数(sizehs)超出范围。

只有高中人数合规的学生插入成功。
该成功插入的学生，因其GPA符合条件，又激活了auto_apply后触发器，生成了申请记录。
这体现了BEFORE触发器用于校验和过滤，AFTER触发器用于执行后续业务逻辑的典型分工。

监控动态阈值的触发器 📈

最后，我们来看一个更接近实际应用的触发器，它监控一个动态变化的数值（大学注册人数），并在其超过阈值时执行一系列复杂的更新操作。

动态阈值监控触发器：

CREATE TRIGGER enroll_threshold
AFTER UPDATE OF enrollment ON college
FOR EACH ROW
WHEN (NEW.enrollment > 16000)
BEGIN
    -- 删除所有申请该大学EE专业的学生
    DELETE FROM apply WHERE cname = NEW.cname AND major = ‘EE‘;
    -- 将该大学其他已录取的申请设为未决定
    UPDATE apply SET decision = NULL WHERE cname = NEW.cname AND decision = ‘Y‘;
END;

演示操作：将所有大学的注册人数增加2000后，斯坦福大学的注册人数超过了16000的阈值。触发器随即被激活，执行了以下操作：

删除了所有申请斯坦福大学EE专业的记录。
将其他申请斯坦福且已被录取的记录状态置为“未决定”。

这个例子展示了触发器如何响应数据状态的变化，执行约束无法实现的、基于业务规则的复杂操作。

本节总结 🎯

在本节课中，我们一起学习了数据库触发器的核心概念和多种应用场景：

触发器基础：了解了不同数据库系统（PostgreSQL, SQLite, MySQL）对触发器支持的差异。
自动执行业务逻辑：创建了在插入学生时自动生成申请记录的触发器。
维护数据完整性：使用触发器模拟了级联删除和级联更新，以维护表间的参照完整性。
实施数据约束：通过BEFORE触发器实现了大学名称的唯一性约束。
理解触发器链：观察了触发器如何相互激活，形成复杂的执行链。
区分执行时机：实践了BEFORE触发器（用于校验）和AFTER触发器（用于后续操作）的不同用途。
响应动态变化：创建了监控数据阈值并执行批量更新的触发器。

通过以上演示，我们看到触发器是数据库自动化管理中极其灵活和强大的工具。在下一部分课程中，我们将进一步探讨触发器可能引发的冲突、循环以及嵌套调用等更复杂的行为。

22：触发器演示（第二部分）🔁

在本节课中，我们将继续学习数据库触发器的进阶概念。我们将探讨触发器的自我触发、循环行为、冲突处理以及嵌套调用等复杂场景，并通过具体的 SQLite 示例来演示这些行为。课程最后，我们还会专门展示 SQLite 中行级立即激活触发器的特性。

触发器实现差异回顾

上一节我们介绍了触发器的基本概念和演示。本节中我们来看看不同数据库系统在触发器实现上的差异。

作为提醒，关于触发器的介绍视频使用了 SQL 标准，但没有数据库系统完全实现这个标准，大多数系统有很大的偏差。Postgres 是最接近标准且最具表现力的触发器系统，但其语法比较繁琐，因此未在演示中使用。

SQLite 遵循 Postgres，也非常灵活。相比之下，MySQL 则表现得较为有限，主要体现在触发器的交互上存在一些限制。

SQLite 触发器特性

我们的演示使用的是 SQLite，且与标准有一些差异。以下是 SQLite 触发器的关键特性：

仅支持行级触发器：没有语句级触发器。
立即激活语义：触发器在每行数据修改后立即激活。
无 OLD TABLE 或 NEW TABLE：由于只有立即激活语义，因此没有这些表。
无 REFERENCING 子句：在每个触发器中，变量 OLD 和 NEW 会自动绑定到行数据。
动作部分：触发器的动作部分是嵌入在 BEGIN 和 END 块中的 SQL 语句。

演示功能概览

以下是我们在两部分演示中涵盖的功能清单。第一部分涵盖了前五个功能，在第二部分我们将讨论以下内容：

自触发触发器：触发器如何触发自身。
循环行为：多个触发器互相触发形成的循环。
冲突：当多个触发器在同一时间被激活并修改相同数据时的情况。
嵌套触发器调用：触发器的动作部分触发其他触发器。
SQLite 行级立即激活演示：展示 SQLite 与标准在激活时机上的差异。

自触发触发器

首先，我们来看一个触发器如何触发自身的简单示例。

我们创建一个在表 T1 上进行插入操作时激活的触发器 R1。它没有条件，在插入之后，它会向 T1 中插入一行数据，这行数据包含插入的值加一。

CREATE TRIGGER R1 AFTER INSERT ON T1
BEGIN
    INSERT INTO T1 VALUES (NEW.a + 1);
END;

当我们向 T1 插入值 1 时：

第一个元组 1 是我们通过命令插入的。
第二个元组 2 是由触发器 R1 自动插入的。

然而，我们预计 R1 会再次被激活，插入更多的元组（3, 4, ...）。但 SQLite 默认情况下不允许在一次触发器处理会话中，同一个触发器被激活多次，这是为了防止无限循环。

我们可以通过启用递归触发器来关闭此限制：

PRAGMA recursive_triggers = ON;

启用后，R1 会无限次地激活自己。为了避免无限循环，我们可以为触发器添加一个终止条件。例如，修改 R1，使其仅在 T1 中的元组数少于 10 时才执行操作：

CREATE TRIGGER R1 AFTER INSERT ON T1
WHEN (SELECT COUNT(*) FROM T1) < 10
BEGIN
    INSERT INTO T1 VALUES (NEW.a + 1);
END;

现在，当我们插入 1 时，触发器会自我触发，依次插入 2, 3, ..., 直到表中有 10 个元组后条件不满足，触发停止。

触发器循环

现在，让我们将自触发的概念推广到三个互相触发的触发器形成的循环。

以下是三个触发器的定义：

R1：在 T1 插入时激活，将值加1后插入 T2。
R2：在 T2 插入时激活，将值加1后插入 T3。
R3：在 T3 插入时激活，将值加1后插入 T1。

-- 触发器 R1
CREATE TRIGGER R1 AFTER INSERT ON T1
BEGIN
    INSERT INTO T2 VALUES (NEW.a + 1);
END;

-- 触发器 R2
CREATE TRIGGER R2 AFTER INSERT ON T2
BEGIN
    INSERT INTO T3 VALUES (NEW.a + 1);
END;

-- 触发器 R3 (带终止条件)
CREATE TRIGGER R3 AFTER INSERT ON T3
WHEN (SELECT COUNT(*) FROM T1) < 100
BEGIN
    INSERT INTO T1 VALUES (NEW.a + 1);
END;

当我们向 T1 插入值 1 时，会触发以下链式反应：

R1 激活，向 T2 插入 2。
R2 激活，向 T3 插入 3。
R3 激活，检查 T1 大小 (<100)，向 T1 插入 4。
R1 再次激活，向 T2 插入 5。
... 如此循环，直到 T1 大小达到 100，R3 的条件不满足，循环终止。

这个例子演示了多个触发器如何通过互相调用形成复杂的循环逻辑。

触发器冲突

接下来，我们看一个情况：假设我们有两个触发器在完全相同的时间被激活，并且它们对数据库的相同部分进行更新。

我们定义两个都在 T1 插入时激活的触发器：

R1：无条件，将 T1 中的所有值更新为 2。
R2：检查 T1 中是否存在值为 2 的项，如果有，则将这些值设置为 3。

-- 触发器 R1
CREATE TRIGGER R1 AFTER INSERT ON T1
BEGIN
    UPDATE T1 SET a = 2;
END;

-- 触发器 R2
CREATE TRIGGER R2 AFTER INSERT ON T1
WHEN EXISTS (SELECT * FROM T1 WHERE a = 2)
BEGIN
    UPDATE T1 SET a = 3;
END;

当我们向 T1 插入值 1 时，哪个触发器先执行会导致不同的结果：

如果 R1 先执行：它将所有值设为 2，然后 R2 的条件满足，将所有值设为 3。最终结果为 3。
如果 R2 先执行：此时表中没有 2，其条件不满足，操作不执行。然后 R1 执行，将所有值设为 2。最终结果为 2。

实验表明，在 SQLite 中，当触发器同时激活时，后创建的触发器会先执行。如果我们先创建 R2 再创建 R1，结果是 3；如果顺序相反，结果是 2。

嵌套触发器调用

现在让我们实验触发器的嵌套调用，即一个触发器的动作触发了另一个触发器。

我们使用四个表 (T1 到 T4) 和三个触发器：

R1：在 T1 插入后激活，向 T2 和 T3 各插入一个元组。
R2：在 T2 插入后激活，向 T3 和 T4 各插入一个元组。
R3：在 T3 插入后激活，向 T4 插入一个元组。

-- 触发器 R1
CREATE TRIGGER R1 AFTER INSERT ON T1
BEGIN
    INSERT INTO T2 VALUES (1);
    INSERT INTO T3 VALUES (1);
END;

-- 触发器 R2
CREATE TRIGGER R2 AFTER INSERT ON T2
BEGIN
    INSERT INTO T3 VALUES (2);
    INSERT INTO T4 VALUES (2);
END;

-- 触发器 R3
CREATE TRIGGER R3 AFTER INSERT ON T3
BEGIN
    INSERT INTO T4 VALUES (3);
END;

当我们向 T1 插入值 0 时：

R1 激活，尝试向 T2 和 T3 插入 1。
向 T2 插入 1 的操作会立即激活 R2。
R2 向 T3 插入 2，向 T4 插入 2。
向 T3 插入 2 的操作会激活 R3，R3 向 T4 插入 3。
然后，R1 中向 T3 插入 1 的操作才执行，这会再次激活 R3，向 T4 插入另一个 3。

最终，我们会在表中看到嵌套执行产生的数据顺序，例如 T3 中可能有 2, 1，T4 中可能有 2, 3, 3。这演示了触发器动作如何以嵌套方式激活其他触发器。

SQLite 行级立即激活演示

我们的最后一个例子专门展示 SQLite 中“立即激活”的行级触发器与 SQL 标准的差异。

根据 SQL 标准，所有触发器都在整个 SQL 命令执行结束时才激活。而在 SQLite（和 MySQL）中，触发器会在每一行级别的修改后立即激活。

假设表 T1 已有 4 个值为 1 的元组。我们创建一个触发器，在向 T1 插入时，将 T1 中所有值的平均值插入到 T2。

CREATE TRIGGER AvgTrigger AFTER INSERT ON T1
BEGIN
    INSERT INTO T2 SELECT AVG(a) FROM T1;
END;

现在，我们执行一个批量插入，向 T1 插入 4 个值为 2 的元组：

INSERT INTO T1 SELECT a+1 FROM T1; -- 假设T1原有4个1，此操作会插入4个2

如果遵循 SQL 标准：触发器在整个 INSERT 语句结束后激活一次。此时 T1 有 4个1 和 4个2，平均值为 1.5。T2 中只会插入一个值 1.5。
在 SQLite 的立即激活语义下：触发器会在每插入一行 2 之后立即激活。因此：
- 插入第一个 2 后，平均值是 (1+1+1+1+2)/5 ≈ 1.2，插入 T2。
- 插入第二个 2 后，平均值是 (1+1+1+1+2+2)/6 ≈ 1.33，插入 T2。
- ... 以此类推。

最终，T2 中会插入 4 个逐步增长的平均值，而不是一个固定的 1.5。这个例子清晰地展示了 SQLite 触发器立即激活的特性。

总结

本节课中我们一起学习了触发器的多种复杂交互行为。

我们首先回顾了不同数据库在触发器实现上的差异，并明确了演示所使用的 SQLite 的特性。接着，我们深入探讨了：

自触发触发器：如何通过递归设置让触发器调用自身，并利用条件语句控制循环。
触发器循环：多个触发器如何互相调用形成逻辑循环，以及如何安全地终止循环。
触发器冲突：当多个触发器同时激活并修改相同数据时，它们的执行顺序对最终结果的影响。
嵌套触发器调用：一个触发器的动作如何触发另一个触发器，形成嵌套执行流。
立即激活语义：通过平均值计算的例子，直观展示了 SQLite 行级触发器立即激活与 SQL 标准语句级激活的显著区别。

触发器在应用程序中常用于执行简单的约束和审计日志。然而，当触发器用于更复杂的目的，并且彼此之间或与数据库的交互变得复杂时，必须非常小心，需要完全理解它们是如何工作的，以避免不可预期的行为和无限循环。

23：事务的ACID属性详解 🧩

在本节课中，我们将深入探讨数据库事务的核心属性——ACID。事务是数据库管理系统中用于处理并发控制和系统故障的关键概念。我们将逐一解析原子性、一致性、隔离性和持久性这四个属性，并通过简单的例子说明它们如何保证数据库操作的可靠性与一致性。

事务的隔离性 🔒

上一节我们介绍了事务的基本概念，本节中我们来看看隔离性。隔离性确保多个并发执行的事务互不干扰，每个客户端都感觉自己在独立操作数据库。这是通过一个称为“可串行化”的正式概念来实现的。

可串行化 意味着，尽管多个事务的操作可能交错执行，但最终效果必须等同于这些事务按某个顺序串行执行的结果。数据库系统通常使用锁机制来实现这一点。

以下是几个并发访问的例子，展示了隔离性如何解决问题：

例1：更新注册人数：两个事务T1和T2分别增加注册人数。通过可串行化保证，最终结果（17,500）将是正确的，避免了并发更新导致的数据不一致。
例2：修改学生申请信息：一个事务修改专业，另一个修改决策。可串行化保证两个修改都会生效，不会出现只有一个生效的情况。
例3：基于GPA更新决策：一个事务根据GPA更新申请决策，另一个事务修改GPA本身。可串行化保证结果一致，但执行顺序（先更新决策还是先修改GPA）会影响中间状态，数据库只保证等效于某种顺序。
例4：移动数据与计数：一个事务将数据从A表移到B表，另一个事务计数。可串行化保证要么先移后数，要么先数后移，结果确定。

关键点：数据库保证行为等同于某种串行顺序，但不保证具体的顺序。如果应用程序对执行顺序有严格要求，需要在代码中显式控制。

事务的持久性 💾

理解了隔离性如何管理并发后，我们来看看持久性。持久性关注的是系统故障后数据的存续问题。

持久性保证：一旦一个事务成功提交（Commit），它对数据库所做的所有更改将是永久性的，即使后续发生系统崩溃、断电等故障，这些更改也不会丢失。

实现持久性通常依赖于预写日志等复杂机制。从用户角度看，只需知道提交后数据即获持久化保证即可。

事务的原子性 ⚛️

接下来我们探讨原子性。原子性处理的是事务执行过程中发生故障的情况。

原子性保证：一个事务中的所有操作被视为一个不可分割的单元。事务要么全部成功执行，要么完全不执行（即没有任何效果）。 如果在事务提交前系统崩溃，数据库系统必须能够撤销（回滚）该事务已执行的部分操作，使数据库恢复到事务开始前的状态。

撤销事务部分影响的操作称为 事务回滚 或 事务中止。这不仅在系统崩溃恢复时自动发生，应用程序也可以主动调用。

以下是一个应用程序利用回滚的示例代码：

BEGIN TRANSACTION;
-- 执行一些SQL操作，例如UPDATE、INSERT
UPDATE accounts SET balance = balance - 100 WHERE user_id = 123;

![](https://github.com/OpenDocCN/cs-notes-zh/raw/master/docs/stf-cs145-db/img/2dc76992b10ad5e451848b74c0ab5a67_13.png)

-- 询问用户是否确认
-- 如果用户确认
COMMIT;
-- 如果用户取消
ROLLBACK; -- 撤销上面的UPDATE操作

重要提示：

ROLLBACK 只能撤销对数据库数据的更改，无法撤销外部操作（如发送邮件、支付现金）。
应避免长时间保持事务打开并等待（如等待用户输入），因为事务持有的锁可能会阻塞其他客户端，影响数据库整体性能。

事务的一致性 ✅

最后，我们来总结一致性属性。一致性关注的是事务如何维护数据库的完整性约束。

一致性保证：事务将数据库从一个一致的状态（满足所有完整性约束）转换到另一个一致的状态。 其工作流程如下：

每个事务开始时，都假设数据库处于一致状态。
事务执行过程中可能暂时违反约束。
事务结束时，必须保证所有约束再次得到满足。

结合隔离性（可串行化），这确保了即使事务并发执行，数据库也能始终保持一致性。因为可串行化保证了事务效果等同于串行执行，而每个事务在串行序列中都是从前一个一致状态开始，并结束于一个新的一致状态。

总结 📝

本节课中我们一起学习了事务的四个ACID属性：

隔离性：通过可串行化保证并发事务互不干扰。
持久性：保证已提交事务的更改永久有效。
原子性：保证事务要么全部完成，要么全部回滚。
一致性：保证事务始终将数据库从一个有效状态转换到另一个有效状态。

事务是数据库系统中用于解决并发和故障问题的强大工具，ACID属性为其提供了坚实的理论基础和行为保证。在接下来的课程中，我们将进一步探讨隔离性的不同级别，了解在某些场景下如何适当放宽隔离要求以提升性能。

24：使用触发器修改视图 🛠️

在本节课中，我们将学习如何通过编写特殊的“替代触发器”来允许对数据库视图执行修改操作（如插入、更新、删除）。由于视图本身并不存储数据，对视图的修改需要被正确地“翻译”并应用到其底层的基础表上。

视图修改的基本概念

上一节我们介绍了视图的定义和查询。本节中我们来看看如何修改视图。

视图是一个虚拟表，基于对基础表的查询结果。当我们希望对视图运行 INSERT、UPDATE 或 DELETE 命令时，这些命令必须被重写为对基础表的操作。

然而，并非所有视图的修改都能被数据库系统自动翻译。主要有两种处理方法：

由视图创建者手动指定重写规则。
限制可修改的视图类型，以便系统能自动翻译。

本节课我们聚焦于第一种方法，即使用一种特殊的触发器——INSTEAD OF 触发器——来手动定义修改操作应如何作用于基础表。

演示环境与初始数据

我们将使用一个简单的大学招生数据库进行演示，它包含以下表：

College（学院表）
Student（学生表）
Apply（学生申请记录表）

初始数据包括四所大学（斯坦福、伯克利、麻省理工、康奈尔）、一组学生以及他们的申请记录。

示例一：在简单视图上启用删除操作

首先，我们创建一个名为 CSaccept 的视图，它包含所有申请了计算机科学（CS）专业并被录取的学生ID和大学名称。

CREATE VIEW CSaccept AS
SELECT sID, cName
FROM Apply
WHERE major = 'CS' AND decision = 'Y';

当我们尝试从该视图中删除记录时，例如：

DELETE FROM CSaccept WHERE sID IN (123, 345);

系统会报错，因为默认不允许直接修改视图。

创建删除触发器

我们需要创建一个 INSTEAD OF DELETE 触发器来拦截删除操作，并将其转化为对基础表 Apply 的删除。

以下是该触发器的核心逻辑：

系统为每个被删除的视图行提供一个特殊的 OLD 变量，其中包含了该行的值（如 sID 和 cName）。
触发器利用这些值，删除 Apply 表中对应的、且满足视图定义条件（major='CS' AND decision='Y'）的记录。

CREATE TRIGGER Delete_CSaccept
INSTEAD OF DELETE ON CSaccept
FOR EACH ROW
BEGIN
    DELETE FROM Apply
    WHERE sID = OLD.sID
      AND cName = OLD.cName
      AND major = 'CS'
      AND decision = 'Y';
END;

创建此触发器后，之前的 DELETE 命令就能成功执行。它会从 Apply 表中移除相应的记录，从而使这些记录从 CSaccept 视图中消失。

示例二：在视图上启用更新操作（及错误示范）

假设我们想更新 CSaccept 视图，将学生345的录取学校从“康奈尔”改为“卡内基梅隆”（CMU）。

UPDATE CSaccept SET cName = 'CMU' WHERE sID = 345;

同样，没有触发器时此操作会失败。

创建更新触发器

我们需要一个 INSTEAD OF UPDATE 触发器。在触发器内部，我们可以通过 OLD 和 NEW 变量访问更新前后的值。

重要警示：触发器由开发者编写，系统不会检查其逻辑是否正确。如果编写错误，会导致数据不一致。

例如，一个错误的触发器可能将更新错误地应用到 Apply 表中专业为‘EE’且未被录取的记录上，而不是正确的‘CS’录取记录。这会导致视图数据没有如预期般更新，但底层数据已被错误修改。

正确的触发器应确保修改应用于符合视图定义条件的记录：

CREATE TRIGGER Update_CSaccept_CName
INSTEAD OF UPDATE OF cName ON CSaccept
FOR EACH ROW
BEGIN
    UPDATE Apply
    SET cName = NEW.cName
    WHERE sID = OLD.sID
      AND cName = OLD.cName
      AND major = 'CS'
      AND decision = 'Y';
END;

示例三：在视图上启用插入操作及条件检查

我们创建另一个视图 CSE，显示所有申请了CS或EE专业的学生ID、大学和专业。

CREATE VIEW CSE AS
SELECT sID, cName, major
FROM Apply
WHERE major IN ('CS', 'EE');

初始的插入触发器（有缺陷）

一个简单的插入触发器可能如下：

CREATE TRIGGER Insert_CSE
INSTEAD OF INSERT ON CSE
FOR EACH ROW
BEGIN
    INSERT INTO Apply(sID, cName, major, decision)
    VALUES (NEW.sID, NEW.cName, NEW.major, NULL);
END;

这个触发器的问题是：即使用户尝试插入一个专业为‘Biology’的记录（这不符合视图条件），它也会向 Apply 表插入数据。虽然新记录不会出现在 CSE 视图中（因为不满足 WHERE 条件），但底层数据库已被修改，这通常不是我们想要的。

改进的插入触发器（带条件检查）

更好的做法是在触发器中使用 WHEN 子句进行检查，只允许插入符合视图定义条件的数据：

CREATE TRIGGER Insert_CSE_Conditional
INSTEAD OF INSERT ON CSE
FOR EACH ROW
WHEN (NEW.major IN ('CS', 'EE'))
BEGIN
    INSERT INTO Apply(sID, cName, major, decision)
    VALUES (NEW.sID, NEW.cName, NEW.major, NULL);
END;

这样，只有专业为‘CS’或‘EE’的插入请求才会被实际执行。

不适合进行修改的视图类型

并非所有视图都适合允许修改。以下是一些例子：

包含聚合函数（如 AVG(), SUM()）的视图：例如，一个显示各高中平均GPA的视图。将平均GPA从3.5更新为3.6意味着什么？这很难翻译成对单个学生记录的具体修改。
使用了 DISTINCT 或对少数列进行投影的视图：例如，一个仅列出所有不重复专业的视图。向其中插入一个新专业“Chemistry”，应该对应在 Apply 表中为哪个学生、哪所大学插入记录？语义模糊。
包含复杂自连接或子查询的视图：例如，一个找出具有相同GPA和高中背景的学生的视图。从该视图中删除一个学生，可能意味着删除该学生本人，也可能意味着删除另一个使他“重复”的学生，翻译规则复杂且不直观。

对于这些视图，即使技术上可以通过编写复杂的触发器来实现修改，但在业务逻辑上往往没有意义，甚至可能引发混乱。

示例四：在连接视图上启用修改

创建一个更复杂的视图 Berkeley，显示所有申请了伯克利大学的学生ID和专业。

CREATE VIEW Berkeley AS
SELECT sID, major
FROM Apply
WHERE cName = 'Berkeley';

为连接视图编写触发器

这个视图涉及对 Apply 单表的筛选。为其编写触发器时，需要考虑业务逻辑：

插入触发器：在插入前检查学生是否存在于 Student 表中。只有学生存在，才向 Apply 表插入一条申请伯克利大学的记录。

CREATE TRIGGER Insert_Berkeley
INSTEAD OF INSERT ON Berkeley
FOR EACH ROW
WHEN EXISTS (SELECT 1 FROM Student WHERE sID = NEW.sID)
BEGIN
    INSERT INTO Apply(sID, cName, major, decision)
    VALUES (NEW.sID, 'Berkeley', NEW.major, NULL);
END;

删除触发器：删除视图中的记录，应转化为删除 Apply 表中对应的、学校为‘Berkeley’的申请记录。

CREATE TRIGGER Delete_Berkeley
INSTEAD OF DELETE ON Berkeley
FOR EACH ROW
BEGIN
    DELETE FROM Apply
    WHERE sID = OLD.sID
      AND cName = 'Berkeley'
      AND major = OLD.major;
END;

更新触发器：通常只允许更新专业字段。更新学生ID在业务上可能意味着另一个学生的申请，这通常不合理。
```
CREATE TRIGGER Update_Berkeley_Major
INSTEAD OF UPDATE OF major ON Berkeley
FOR EACH ROW
BEGIN
    UPDATE Apply
    SET major = NEW.major
    WHERE sID = OLD.sID
      AND cName = 'Berkeley'
      AND major = OLD.major;
END;
```
如果用户尝试更新视图中不允许的列（如 sID），而你没有对应的触发器，操作将会失败。

视图修改与约束的交互

视图修改触发器最终是对基础表执行操作，因此必须遵守基础表上的所有约束。

示例冲突：

如果 Apply 表的 decision 列有 NOT NULL 约束，而你的视图插入触发器总是插入 NULL 到该列，则插入会失败。
如果 Apply 表有主键约束（如 (sID, cName, major) 组合必须唯一），那么：
- 通过视图插入重复的申请记录会失败。
- 通过视图更新，导致两条记录拥有相同的主键值也会失败。

解决方案：编写触发器时，必须考虑目标表的所有约束，并在触发器逻辑中妥善处理（例如，在插入前检查唯一性，或为必填字段提供合理的默认值）。

总结

本节课中我们一起学习了如何使用 INSTEAD OF 触发器来实现对数据库视图的修改。

核心机制：通过创建 INSTEAD OF INSERT/UPDATE/DELETE 触发器，拦截对视图的修改操作，并将其重写为对底层基础表的操作。
关键变量：在触发器体内，使用 NEW 和 OLD 变量来访问试图插入、更新或删除的视图行数据。
开发者责任：系统不检查触发器翻译的逻辑正确性。由开发者确保修改能正确反映到视图和基础表，并保持数据一致性。
适用性：不是所有视图都适合修改。对于包含聚合、DISTINCT 或复杂逻辑的视图，允许修改可能没有业务意义。
约束遵守：触发器执行的操作必须满足基础表的所有完整性约束，否则修改会失败。

通过谨慎地设计和编写触发器，可以灵活、安全地扩展视图的功能，使其在某些场景下表现得像可修改的真实表一样。

25：SQL 递归查询实战演示 🧠

在本课程中，我们将通过实际演示，深入学习 SQL 中的递归查询。我们将回顾递归查询的基本结构，并通过三个具体示例（家族树、公司层级、航班路径）来展示其应用。最后，我们还将探讨当数据中存在循环时，递归查询可能遇到的问题及其解决方案。

递归查询回顾 📚

上一节我们介绍了递归查询的基本概念。本节中，我们来看看其核心结构。

递归查询通过 WITH RECURSIVE 语句实现。其核心思想是定义一个关系，该关系的查询可以引用自身。一个典型的递归定义包含两部分：

基础查询：不依赖于递归关系 R 的初始数据。
递归查询：依赖于递归关系 R，用于扩展数据。

最终，我们可以编写一个查询，使用这个递归定义的关系以及数据库中的其他表。

其通用形式可以用以下伪代码表示：

WITH RECURSIVE R (attributes) AS (
    -- 基础查询 (Base Query)
    SELECT ...
    UNION ALL
    -- 递归查询 (Recursive Query)
    SELECT ... FROM R, ... WHERE ...
)
-- 最终查询 (Final Query)
SELECT ... FROM R, ... WHERE ...;

示例一：计算家族祖先 👨‍👩‍👧‍👦

在第一个示例中，我们将学习如何利用递归查询，从仅包含父子关系的表中，找出任意人的所有祖先。

我们有一个名为 parent 的表，结构如下：

parent (parent_name, child_name)

我们的目标是找到 ‘Mary’ 的所有祖先。以下是实现此目标的递归查询。

WITH RECURSIVE ancestor (ancestor, descendant) AS (
    -- 基础查询：父子关系即直接的祖先关系
    SELECT parent_name AS ancestor, child_name AS descendant
    FROM parent
    UNION ALL
    -- 递归查询：如果 A 是 B 的祖先，且 B 是 C 的父母，则 A 是 C 的祖先
    SELECT a.ancestor, p.child_name
    FROM ancestor a, parent p
    WHERE a.descendant = p.parent_name
)
-- 最终查询：找出 Mary 的所有祖先
SELECT ancestor FROM ancestor WHERE descendant = ‘Mary’;

查询执行与验证

执行上述查询后，我们得到了 Mary 的五位祖先。通过修改最终查询中的名字，我们可以查找其他人的祖先。例如，查找 ‘Frank’ 的祖先会包含 Mary，而查找根节点 ‘Bob’ 的祖先则返回空结果。

示例二：计算项目总薪资成本 💼

上一节我们计算了家族关系。本节中，我们来看看如何将递归应用于公司管理结构，以计算一个项目的总人力成本。

我们有三张表：

employee(id, salary)
manages(mgr_id, eid)
project(name, mgr_id)

项目 ‘X’ 的总成本是其经理及其所有下属（递归向下）的薪资总和。

方法一：先计算完整管理闭包

以下是计算项目 X 总成本的查询。首先计算整个公司的上下级关系（传递闭包），然后汇总相关人员的薪资。

WITH RECURSIVE superior (mgr_id, eid) AS (
    -- 基础查询：直接管理关系
    SELECT mgr_id, eid FROM manages
    UNION ALL
    -- 递归查询：传递管理关系
    SELECT s.mgr_id, m.eid
    FROM superior s, manages m
    WHERE s.eid = m.mgr_id
)
-- 最终查询：汇总项目 X 经理及其所有下属的薪资
SELECT SUM(salary)
FROM employee
WHERE id IN (
    SELECT mgr_id FROM project WHERE name = ‘X’
    UNION
    SELECT eid FROM superior WHERE mgr_id IN (SELECT mgr_id FROM project WHERE name = ‘X’)
);

执行查询后，得到项目 X 的总成本为 400。

方法二：将项目约束融入递归

我们可以编写一个更高效的查询，在递归过程中直接限定只计算与项目 X 相关的员工。

WITH RECURSIVE x_emps(id) AS (
    -- 基础查询：项目 X 的经理
    SELECT mgr_id FROM project WHERE name = ‘X’
    UNION ALL
    -- 递归查询：所有由 X 相关员工管理的员工
    SELECT m.eid
    FROM manages m, x_emps x
    WHERE m.mgr_id = x.id
)
-- 最终查询：汇总这些员工的薪资
SELECT SUM(salary) FROM employee WHERE id IN (SELECT id FROM x_emps);

此查询同样返回总成本 400，但递归范围更小，可能效率更高。

同时计算多个项目

我们还可以扩展查询，同时计算项目 Y 和 Z 的总成本。以下是实现方式。

WITH RECURSIVE
y_emps(id) AS ( ... ), -- 类似 x_emps 的定义，针对项目 Y
z_emps(id) AS ( ... )  -- 类似 x_emps 的定义，针对项目 Z
SELECT ‘Y’ AS project, SUM(salary) AS total_cost FROM employee WHERE id IN (SELECT id FROM y_emps)
UNION ALL
SELECT ‘Z’ AS project, SUM(salary) AS total_cost FROM employee WHERE id IN (SELECT id FROM z_emps);

执行后，我们得到项目 Y 总成本 300，项目 Z 总成本 70。

示例三：寻找最便宜航班路线 ✈️

前面我们处理了树状结构的递归。本节中，我们来看看在图状结构（航班网络）中如何使用递归，并处理可能出现的循环问题。

我们有一张 flights(origin, destination, airline, cost) 表。目标是找到从城市 ‘A’ 到城市 ‘B’ 总费用最低的路线，允许任意次中转。

查询所有可能路径及费用

首先，我们编写一个查询，列出所有从 A 到 B 的路径及其总费用。

WITH RECURSIVE routes(origin, destination, total_cost) AS (
    -- 基础查询：所有直飞航班
    SELECT origin, destination, cost FROM flights
    UNION ALL
    -- 递归查询：连接已有路径与新航班以扩展路径
    SELECT r.origin, f.destination, r.total_cost + f.cost
    FROM routes r, flights f
    WHERE r.destination = f.origin
)
-- 最终查询：筛选从 A 到 B 的路径
SELECT * FROM routes WHERE origin = ‘A’ AND destination = ‘B’;

执行后，我们得到三条路径：直飞（195）、经芝加哥（300）和一条复杂路径（175）。

查询最低费用

要找到最便宜的路线，只需修改最终查询，使用聚合函数 MIN。

WITH RECURSIVE routes(...) AS ( ... ) -- 同上
SELECT MIN(total_cost) AS cheapest_price FROM routes WHERE origin = ‘A’ AND destination = ‘B’;

查询返回最低费用 175。

处理循环与无限递归问题

当航班网络中存在循环（例如城市间可互相往返）时，上述简单递归将产生无限多的路径，导致查询无法终止或报错。

解决方案：限制递归深度

SQL 标准并未直接提供在递归中避免循环的机制。一种实用的解决方案是为递归添加一个深度限制。例如，我们假设不会有人为了省钱乘坐超过 10 次航班。

WITH RECURSIVE routes(origin, destination, total_cost, depth) AS (
    -- 基础查询：添加深度为1
    SELECT origin, destination, cost, 1 FROM flights
    UNION ALL
    -- 递归查询：深度加1，并限制深度小于10
    SELECT r.origin, f.destination, r.total_cost + f.cost, r.depth + 1
    FROM routes r, flights f
    WHERE r.destination = f.origin AND r.depth < 10
)
SELECT MIN(total_cost) AS cheapest_price FROM routes WHERE origin = ‘A’ AND destination = ‘B’;

通过引入 depth 字段并在递归条件中限制 depth < 10，我们确保了递归会在有限步骤内终止。只要设定的深度上限足够大，能包含实际的最优路径，我们就能得到正确结果。

课程总结 🎯

在本节课中，我们一起学习了 SQL 递归查询的实战应用。

核心结构：我们回顾了 WITH RECURSIVE 语句，它由基础查询、递归查询和最终查询三部分组成。
典型应用：我们通过三个例子演示了递归如何解决传递闭包问题：
- 计算祖先：在树状结构中向上追溯。
- 汇总成本：在管理层级中向下汇总。
- 寻找路径：在网络图中查找连接。
循环处理：我们探讨了当数据中存在循环时，递归可能导致的无限循环问题，并介绍了通过限制递归深度来确保查询终止的实用方法。

递归是 SQL 中处理层次化或图形化数据的强大工具，理解其原理和潜在陷阱对于编写正确高效的查询至关重要。

26：SQL 中的非线性递归与互递归 🧩

在本节课中，我们将深入探讨 SQL 递归查询中的两个高级概念：非线性递归与互递归。我们将了解它们是什么、如何工作、各自的优缺点，以及 SQL 标准对它们的支持情况。

概述

SQL 通过 WITH RECURSIVE 语句支持递归查询，这极大地扩展了其表达能力，使其能够处理诸如计算传递闭包等需要无界迭代的问题。上一节我们介绍了递归的基本形式——线性递归。本节中，我们将来看看更复杂的非线性递归和互递归，并探讨 SQL 标准对它们的限制。

1. 线性递归回顾

首先，我们回顾一下 SQL 如何实现递归。其核心是 WITH RECURSIVE 语句，它允许定义一个可以引用自身的关系。其典型结构包含一个不涉及递归的基本查询（Base Case）和一个引用自身进行扩展的递归查询（Recursive Case），两者通过 UNION 连接。

例如，在查找“Mary的所有祖先”这个经典例子中，查询结构如下：

WITH RECURSIVE Ancestor(ancestor, descendant) AS (
    -- 基础查询：父子关系即祖先关系
    SELECT parent, child FROM Parent
    UNION
    -- 递归查询：将已有的祖先关系扩展一代
    SELECT a.ancestor, p.child
    FROM Ancestor a, Parent p
    WHERE a.descendant = p.parent
)
SELECT ancestor FROM Ancestor WHERE descendant = ‘Mary’;

这个查询是线性递归的，因为在递归查询部分，递归定义的关系 Ancestor 只出现了一次。

2. 非线性递归

2.1 什么是非线性递归？

非线性递归指的是在递归查询部分，递归定义的关系出现了两次或更多次。以上面的祖先查询为例，它的非线性版本如下：

WITH RECURSIVE Ancestor(ancestor, descendant) AS (
    SELECT parent, child FROM Parent
    UNION
    -- 注意：这里引用了两次 Ancestor 关系
    SELECT a1.ancestor, a2.descendant
    FROM Ancestor a1, Ancestor a2
    WHERE a1.descendant = a2.ancestor
)
SELECT ancestor FROM Ancestor WHERE descendant = ‘Mary’;

这里的递归部分将 Ancestor 关系的两个实例（a1 和 a2）进行连接，因此它是非线性的。

2.2 执行过程对比

为了理解差异，我们对比两者的执行过程。假设 Parent 表包含 (Sue, John) 和 (John, Mary)。

线性递归执行：
1. 基础查询：Ancestor 初始化为 {(Sue, John), (John, Mary)}。
2. 第一次递归：将 Ancestor 与 Parent 连接，得到 (Sue, Mary) 并加入 Ancestor。
3. 第二次递归：没有新元组产生，迭代停止。
- 特点：每次迭代只将关系扩展“一代”。
非线性递归执行：
1. 基础查询：Ancestor 初始化为 {(Sue, John), (John, Mary)}。
2. 第一次递归：将 Ancestor 与自身连接，同样得到 (Sue, Mary)。
3. 第二次递归：此时 Ancestor 包含三代关系 (Sue, John), (John, Mary), (Sue, Mary)。连接自身可能直接产生更远代的关系（例如，如果图更深）。
- 特点：一次迭代可能连接多代关系，收敛更快。

2.3 优缺点分析

以下是线性和非线性递归在这个查询中的主要优缺点，通常也适用于其他可互换表达的场景：

非线性递归的优点：
1. 查询更简洁：在某些情况下，非线性表达更对称、更简短。
2. 收敛速度更快：理论上，线性递归需要 O(n) 次迭代（n为深度），而非线性递归可能只需要 O(log n) 次迭代。对于大型数据库，这能显著提升性能。

非线性递归的缺点：
- 实现复杂：高效实现非线性递归对数据库系统更具挑战性。
- 支持有限：因此，SQL 标准只要求支持线性递归。像 PostgreSQL 这样的主流数据库系统也只支持线性递归，不支持非线性递归。

3. 互递归

上一节我们介绍了单个关系的递归，本节中我们来看看关系之间相互引用的互递归。

3.1 互递归的概念

互递归是指两个或多个递归定义的关系相互引用，形成一个循环依赖。例如，关系 R1 的定义引用了 R2，而 R2 的定义又引用了 R1。它们必须被同时计算。

3.2 实例：枢纽节点与权威节点

我们用一个经典的“枢纽节点（Hub）”和“权威节点（Authority）”算法来演示互递归。该算法用于分析网页链接图：

枢纽节点：指向多个权威节点的网页。
权威节点：被多个枢纽节点指向的网页。

假设我们有一个表示链接的 Link(src, dst) 表，以及初始的 HubStart 和 AuthStart 节点集合。以下是计算所有枢纽和权威节点的互递归查询：

WITH RECURSIVE
    Hub(node) AS (
        -- 基础：初始枢纽节点
        SELECT node FROM HubStart
        UNION
        -- 递归：找到指向至少3个权威节点的源节点
        SELECT l.src
        FROM Link l, Auth a
        WHERE l.dst = a.node
        GROUP BY l.src
        HAVING COUNT(*) >= 3
    ),
    Auth(node) AS (
        -- 基础：初始权威节点
        SELECT node FROM AuthStart
        UNION
        -- 递归：找到被至少3个枢纽节点指向的目标节点
        SELECT l.dst
        FROM Link l, Hub h
        WHERE l.src = h.node
        GROUP BY l.dst
        HAVING COUNT(*) >= 3
    )
SELECT * FROM Hub, Auth; -- 最终查询

可以看到，Hub 的定义依赖于 Auth，而 Auth 的定义又依赖于 Hub，形成了互递归。

3.3 互递归的限制与问题

互递归虽然强大，但也带来复杂性。考虑一个修改场景：我们希望一个节点不能同时是枢纽和权威。我们可能在定义中加入排除条件：

-- 在Hub定义中增加：且不是权威节点
... AND l.src NOT IN (SELECT node FROM Auth)
-- 在Auth定义中增加：且不是枢纽节点
... AND l.dst NOT IN (SELECT node FROM Hub)

这引入了负向依赖（NOT IN）。这会导致一个严重问题：结果可能不确定。如果一个节点同时满足成为枢纽和权威的条件，它最终被归为哪一类，可能取决于系统先计算哪个关系。这种非确定性是数据库系统不希望的。

因此，SQL 标准禁止在递归定义的关系中出现这种负向子查询，并且对互递归的支持也非常有限。PostgreSQL 目前也不支持互递归。

4. 递归的其他限制

除了非线性递归和带有负向依赖的互递归，SQL 标准还对递归查询施加了其他重要限制：

禁止与聚合函数共用：在递归查询部分使用 SUM、COUNT 等聚合函数通常是被禁止的。因为这可能导致无法定义明确的固定点（结果集无限增长或无法确定）。
- 示例：R(x) AS (SELECT a FROM P UNION SELECT SUM(x) FROM R)，这种查询没有明确定义。
禁止的递归子查询：如前所述，在子查询中对递归定义关系进行否定引用（如 NOT EXISTS）是被禁止的。

总结

本节课中我们一起学习了 SQL 递归查询的两个高级主题：

非线性递归：递归部分多次引用自身，能写出更简洁、理论上更高效的查询，但因实现复杂，不被 SQL 标准要求，主流数据库也不支持。
互递归：多个递归关系相互引用，能表达复杂的相互依赖逻辑（如枢纽-权威算法），但易因引入负向依赖导致非确定性结果，因此受到 SQL 标准的严格限制。

尽管存在这些限制，但 SQL 线性递归的基本功能已经非常强大，足以表达大多数自然的递归查询（如路径查找、层次结构展开），极大地扩展了 SQL 的语言表达能力。对于更复杂的图计算，可能需要借助专门的图数据库或过程化代码。

27：OLAP 演示教程 🧮

在本课程中，我们将通过一个具体的星型架构销售数据示例，演示联机分析处理（OLAP）的核心操作。我们将学习如何创建星型架构，执行不同类型的OLAP查询，并了解SQL中为OLAP新增的特定语法。

概述

我们将创建一个包含事实表和多个维度表的星型架构，用于分析销售数据。演示将涵盖星型连接、钻取、汇总、切片、切块等操作，并介绍SQL标准中的 CUBE 和 ROLLUP 操作。所有演示将使用MySQL数据库进行。

创建星型架构

首先，我们创建一个星型架构，它包含一个销售事实表和三个维度表：顾客表、商品表和商店表。

以下是创建这些表的SQL代码：

-- 创建顾客维度表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50),
    gender CHAR(1),
    age INT
);

-- 创建商品维度表
CREATE TABLE items (
    item_id INT PRIMARY KEY,
    category VARCHAR(50),
    color VARCHAR(20)
);

-- 创建商店维度表
CREATE TABLE stores (
    store_id INT PRIMARY KEY,
    city VARCHAR(50),
    county VARCHAR(50),
    state VARCHAR(50)
);

-- 创建销售事实表
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    store_id INT,
    item_id INT,
    customer_id INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (store_id) REFERENCES stores(store_id),
    FOREIGN KEY (item_id) REFERENCES items(item_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

执行星型连接

星型连接是将事实表与所有维度表通过外键连接起来，以扩展每个销售记录的信息。

以下是执行完全星型连接的SQL查询：

SELECT *
FROM sales s
JOIN stores st ON s.store_id = st.store_id
JOIN items i ON s.item_id = i.item_id
JOIN customers c ON s.customer_id = c.customer_id;

这个查询会返回60个元组，每个元组对应事实表中的一行，并附加了来自维度表的详细信息。

在星型连接上添加约束

通常，我们不会进行完全的星型连接，而是会添加一些选择和投影操作来筛选数据。

以下查询将限制结果，只显示加利福尼亚州、T恤类别、年龄小于22岁且购买价格低于25的销售记录：

SELECT st.city, i.color, c.name, s.price
FROM sales s
JOIN stores st ON s.store_id = st.store_id
JOIN items i ON s.item_id = i.item_id
JOIN customers c ON s.customer_id = c.customer_id
WHERE st.state = 'California'
  AND i.category = 'T-shirt'
  AND c.age < 22
  AND s.price < 25;

运行此查询将返回符合所有条件的五笔销售记录。

分组与聚合分析

在OLAP应用中，常见的操作是对数据进行分组和聚合，以便进行分析。

按商店和顾客分组

以下查询按商店ID和顾客ID分组，计算每个组合的总销售额：

SELECT s.store_id, s.customer_id, SUM(s.price) AS total_sales
FROM sales s
GROUP BY s.store_id, s.customer_id;

此查询的结果显示了每个商店和顾客组合的总销售额。

钻取操作

钻取表示在已聚合的数据上添加更多细节。例如，我们可以将商品ID添加到分组中，以获得更详细的分析：

SELECT s.store_id, s.customer_id, s.item_id, SUM(s.price) AS total_sales
FROM sales s
GROUP BY s.store_id, s.customer_id, s.item_id;

运行此查询后，结果会变得更加详细，显示了按商店、顾客和商品细分的总销售额。

切片与切块操作

切片和切块是与数据立方体相关的操作，用于分析数据的特定部分。

切片操作

切片通过约束某一维度来分析数据立方体的一个切片。以下查询限制只分析华盛顿州的销售数据：

SELECT s.store_id, s.customer_id, s.item_id, SUM(s.price) AS total_sales
FROM sales s
JOIN stores st ON s.store_id = st.store_id
WHERE st.state = 'Washington'
GROUP BY s.store_id, s.customer_id, s.item_id;

此查询返回华盛顿州商店的销售数据，即数据立方体的一个切片。

切块操作

切块通过约束多个维度来分析数据立方体的一个块。以下查询限制分析华盛顿州购买的红色商品：

SELECT s.store_id, s.customer_id, s.item_id, SUM(s.price) AS total_sales
FROM sales s
JOIN stores st ON s.store_id = st.store_id
JOIN items i ON s.item_id = i.item_id
WHERE st.state = 'Washington'
  AND i.color = 'Red'
GROUP BY s.store_id, s.customer_id, s.item_id;

此查询返回华盛顿州红色商品的销售数据，即数据立方体的一个块。

汇总操作

汇总表示降低数据的细节层次，进行更多的聚合。例如，我们可以只按商品来汇总销售数据：

SELECT s.item_id, SUM(s.price) AS total_sales
FROM sales s
GROUP BY s.item_id;

此查询返回按商品汇总的总销售额，数据更加聚合。

基于维度属性的分组

除了按ID属性分组，我们还可以基于维度表中的有意义属性进行分组。以下查询按州和商品类别分组，计算总销售额：

SELECT st.state, i.category, SUM(s.price) AS total_sales
FROM sales s
JOIN stores st ON s.store_id = st.store_id
JOIN items i ON s.item_id = i.item_id
GROUP BY st.state, i.category;

此查询显示了不同州和商品类别的总销售额，例如加利福尼亚州和华盛顿州的夹克与T恤销量对比。

进一步钻取

我们可以在上述查询的基础上进一步钻取，例如添加县和性别属性：

SELECT st.state, st.county, i.category, c.gender, SUM(s.price) AS total_sales
FROM sales s
JOIN stores st ON s.store_id = st.store_id
JOIN items i ON s.item_id = i.item_id
JOIN customers c ON s.customer_id = c.customer_id
GROUP BY st.state, st.county, i.category, c.gender;

此查询提供了更详细的分析，例如按州、县、类别和性别细分的总销售额。

SQL中的OLAP扩展：CUBE和ROLLUP

SQL标准为OLAP查询引入了 CUBE 和 ROLLUP 操作，用于生成数据立方体或进行层次化汇总。

CUBE操作

CUBE 操作会生成数据立方体的所有可能组合。以下查询尝试使用 CUBE：

SELECT store_id, item_id, customer_id, SUM(price) AS total_sales
FROM sales
GROUP BY CUBE(store_id, item_id, customer_id);

然而，MySQL目前不支持 CUBE 操作。我们可以通过其他方式模拟其行为。

创建数据立方体表

我们可以将 CUBE 查询的结果存储在一个表中，以便后续高效查询：

CREATE TABLE cube AS
SELECT store_id, item_id, customer_id, SUM(price) AS total_price
FROM sales
GROUP BY store_id, item_id, customer_id WITH ROLLUP;

此表包含了数据立方体的预聚合数据，包括面、边和角的总和。

查询数据立方体

直接查询数据立方体表比查询原始事实表更高效。以下查询找出加利福尼亚州蓝色商品的总销售额：

SELECT SUM(c.total_price) AS total_sales
FROM cube c
JOIN stores st ON c.store_id = st.store_id
JOIN items i ON c.item_id = i.item_id
WHERE st.state = 'California'
  AND i.color = 'Blue'
  AND c.customer_id IS NULL;

此查询仅使用数据立方体表中的六个元组，就得到了总销售额为1365的结果。

ROLLUP操作

ROLLUP 操作基于 GROUP BY 子句中属性的排序，生成层次化的汇总数据。以下查询按州、县和市进行分组，并使用 ROLLUP：

SELECT st.state, st.county, st.city, SUM(s.price) AS total_sales
FROM sales s
JOIN stores st ON s.store_id = st.store_id
GROUP BY st.state, st.county, st.city WITH ROLLUP;

此查询的结果显示了原始数据以及按县、州和总计的汇总数据，非常适合层次化维度的分析。

总结

在本课程中，我们一起学习了OLAP的核心概念和操作。我们创建了一个星型架构，并演示了星型连接、钻取、汇总、切片和切块等查询。我们还介绍了SQL中的 CUBE 和 ROLLUP 操作，展示了如何创建和查询数据立方体以提高分析效率。这些技术在处理大规模分析数据集时非常有用，能够显著提升查询性能。

28：XML 查询与 XSLT 实践教程 📘

在本节课中，我们将学习如何使用 XPath、XQuery 和 XSLT 对 XML 数据进行查询与转换。我们将通过两个数据集（课程目录和世界各国）的练习题，掌握这些技术的核心语法和应用场景。

🗂️ 概述与数据集介绍

首先，我们需要了解我们将要使用的两个 XML 数据集的结构。

课程目录数据集 (courses.xml) 的根元素是 <catalog>，其下包含 <department> 元素。每个 <department> 下又有多个 <course> 元素。课程信息包括编号（@number 属性）、标题（<title>）、描述（<description>）、注册人数（<enrollment>）和讲师（<instructors>）等。

世界各国数据集 (countries.xml) 的根元素是 <mondial>，其下包含多个 <country> 元素。每个国家有名称（@name）、人口（@population）、面积（@area）等属性，并可能包含 <language> 和 <city> 子元素。

理解数据结构是编写正确查询的第一步。接下来，我们将开始具体的练习。

🔍 第一部分：XPath 与 XQuery 练习

上一节我们介绍了数据集的结构，本节中我们来看看如何使用 XPath 和 XQuery 进行查询。

练习 1：查找跨列课程

目标：返回描述中包含 “cross-listed as Ling 180” 的课程编号。

我们需要定位到 <course> 元素，并筛选其 <description> 中的文本。课程编号存储在 @number 属性中。

XPath 查询：

//course[contains(description, "cross-listed as Ling 180")]/data(@number)

解释：

//course：选择文档中所有 <course> 元素。
[contains(description, “...”)]：筛选条件，要求 <description> 元素包含指定文本。
/data(@number)：从筛选后的课程中提取 @number 属性的值。

此查询将返回 CS124。

练习 2：查找特定讲师教授的课程

目标：返回由名字是 “Daphne” 或 “Julie” 的讲师教授的课程编号。

我们需要在 <course> 元素内，检查 <instructors> 下的子元素（可能是 <lecturer> 或 <professor>）的 <first_name>。

XPath 查询：

//course[instructors/*[first_name="Daphne" or first_name="Julie"]]/data(@number)

解释：

instructors/*：使用通配符 * 匹配 <instructors> 下的任何直接子元素（即讲师或教授）。
[first_name=“Daphne” or first_name=“Julie”]：筛选条件，要求名字匹配。
最终返回满足条件的课程的编号，例如 CS107 和 CS228。

练习 3：查找同时拥有讲师和教授的课程

目标：返回同时拥有 <lecturer> 和 <professor> 作为讲师的课程标题，且每个标题只出现一次。

这个查询更适合用 XQuery 表达，因为它涉及对元素集合的迭代和条件判断。

XQuery 查询：

for $c in doc(“courses.xml”)//course
where $c/instructors/lecturer and $c/instructors/professor
return $c/title

解释：

for $c in …：遍历每一个 <course> 元素。
where …：条件为课程下同时存在 <instructors/lecturer> 和 <instructors/professor> 路径。
return $c/title：返回满足条件的课程的 <title> 元素内容。

此查询将返回 Programming Methodology 和 Programming Abstractions。

🌍 第二部分：世界各国数据集查询

完成了课程目录的练习，我们接下来看看更复杂的国家数据查询。

练习 11：查找符合特定条件的国家

目标：返回所有没有语言或城市数据，但人口超过 1000 万的国家的名称。

我们需要使用 count() 函数来检查子元素的数量，并结合属性值进行筛选。

XPath 查询：

//country[count(language)=0 and count(city)=0 and @population > 10000000]/data(@name)

解释：

count(language)=0：确保 <country> 下没有 <language> 子元素。
count(city)=0：确保没有 <city> 子元素。
@population > 10000000：确保人口属性值大于一千万。
最终返回满足所有条件的国家的 @name 属性值。

练习 12：查找拥有最大城市的国家

目标：返回拥有所有城市中人口最多的城市所在国家的名称。

这是一个复杂的查询，需要比较所有城市的人口。我们需要使用 XQuery 的 every 量词进行遍历比较，并注意将人口数据转换为整数类型。

XQuery 查询：

for $c in doc(“countries.xml”)//country
for $city in $c/city
where every $city2 in doc(“countries.xml”)//city
satisfies xs:integer($city2/@population) <= xs:integer($city/@population)
return $c/data(@name)

解释：

使用两层循环：外层遍历国家 $c，内层遍历该国的每个城市 $city。
where every … satisfies …：核心条件。它检查对于数据集中的每一个其他城市 $city2，其人口是否都小于或等于当前城市 $city 的人口。如果成立，则 $city 就是人口最多的城市。
xs:integer(…)：将 @population 属性值显式转换为整数，以确保进行数值比较而非字符串比较。
最终返回该城市所属国家的名称。正确结果是 South Korea。

🛠️ 第三部分：XSLT 转换练习

上一节我们使用 XPath 和 XQuery 进行查询，本节我们将学习如何使用 XSLT 对 XML 文档进行转换。

练习 1：筛选并保留课程结构

目标：返回所有注册人数大于 500 的课程，并保留其完整的原始 XML 结构。

XSLT 通过模板匹配和复制来实现。我们需要一个模板来匹配目标课程，另一个模板来阻止无关文本的输出。

XSLT 样式表：

<xsl:stylesheet version=“1.0” xmlns:xsl=“ http://www.w3.org/1999/XSL/Transform ”>
    <!-- 匹配并复制注册人数大于500的课程 -->
    <xsl:template match=“course[enrollment > 500]”>
        <xsl:copy-of select=“.”/>
    </xsl:template>
    <!-- 匹配文本节点但不输出，防止无关文本泄露 -->
    <xsl:template match=“text()”/>
</xsl:stylesheet>

解释：

第一个模板匹配 enrollment > 500 的 <course> 元素，并使用 <xsl:copy-of select=“.”/> 将其完整复制到输出。
第二个模板 match=“text()” 匹配所有文本节点，但不产生任何输出，这可以防止未被显式处理的文本出现在结果中。

练习 2：从数据中删除特定课程

目标：从课程目录中删除所有注册人数大于 60 或没有列出注册人数的课程，并保持其他结构不变。

思路是使用一个“通配”模板复制整个文档结构，然后为需要删除的元素创建一个“空”模板。

XSLT 样式表：

<xsl:stylesheet version=“1.0” xmlns:xsl=“ http://www.w3.org/1999/XSL/Transform ”>
    <!-- 通配模板，复制所有元素和属性并继续处理其子节点 -->
    <xsl:template match=“@*|node()”>
        <xsl:copy>
            <xsl:apply-templates select=“@*|node()”/>
        </xsl:copy>
    </xsl:template>
    <!-- 匹配需要删除的课程：注册人数>60 或 无注册人数元素 -->
    <xsl:template match=“course[enrollment > 60 or not(enrollment)]”/>
</xsl:stylesheet>

解释：

match=“@*|node()”：这是一个强大的模板，匹配所有属性(@*)和所有类型的节点(node()``)。它通过 xsl:copy和xsl:apply-templates` 递归地复制整个文档的骨架。
match=“course[enrollment > 60 or not(enrollment)]”：这个模板匹配需要删除的课程。模板体为空，意味着这些元素不会被复制到输出结果中，从而实现了“删除”。

练习 3：从国家数据中删除特定国家

目标：从世界各国数据中删除所有面积大于 40,000 或没有列出城市的国家。

此练习与上一个 XSLT 练习模式完全相同，只是匹配条件换成了国家数据集的属性。

XSLT 样式表：

<xsl:stylesheet version=“1.0” xmlns:xsl=“ http://www.w3.org/1999/XSL/Transform ”>
    <xsl:template match=“@*|node()”>
        <xsl:copy>
            <xsl:apply-templates select=“@*|node()”/>
        </xsl:copy>
    </xsl:template>
    <!-- 匹配需要删除的国家：面积>40000 或 无城市数据 -->
    <xsl:template match=“country[@area > 40000 or count(city)=0]”/>
</xsl:stylesheet>

解释：

逻辑与练习2完全一致。通配模板负责维持文档结构。
第二个模板匹配需要删除的 <country> 元素：@area > 40000 或 count(city)=0。空模板确保这些国家不会出现在最终输出里。

📝 总结

本节课中我们一起学习了 XML 数据处理的三种核心技术：

XPath：用于在 XML 文档中导航和定位节点，非常适合简单的条件筛选和值提取。
XQuery：功能更强大的查询语言，支持变量、循环（FLWOR 表达式）和复杂条件判断，适合完成需要遍历和比较的数据查询任务。
XSLT：一种转换语言，用于将 XML 文档转换为其他格式（如另一个 XML、HTML 或文本）。其核心思想是模板匹配，通过定义不同的模板规则来重组、筛选或修改原始数据。

通过针对课程目录和世界各国数据集的实践练习，我们掌握了如何根据不同的需求选择合适的工具，并编写出正确的查询与转换语句。记住，理解 XML 文档的层次结构是成功编写任何查询的第一步。

P29：DBClass 办公时间 03_06_2013 📅

在本节课中，我们将回顾一次数据库课程的线上办公时间。主要内容包括课程互动形式的尝试、近期学习任务的总结，以及对期末考试的准备指导。

课程互动形式的探索 🤝

上一节我们介绍了课程背景，本节中我们来看看本次办公时间的开场。教师首先对学生表示欢迎，并说明自己一直在线提供支持。

在整个课程中，教学团队与Jennifer以及斯坦福大学在线教育团队合作，尝试了多种不同的办公时间安排方式。此举旨在探索大规模开放在线课程中，教学人员与学生互动的最佳实践。

以下是尝试过的几种互动形式：

IRC聊天
Reddit论坛办公时间
Google Hangout视频会议

本周学习任务与反馈 📝

上一节我们了解了多样的互动方式，本节中我们来看看本周的具体学习情况。教师通过录制视频解答练习题的方式，准备了本周的办公时间内容，并感谢学生在课程形式变化中保持耐心。

教师曾要求学生提前在Reddit论坛上提交关于课程主题的问题。但关于即将在周一截止的SQL触发器练习，收到的提问并不多。

因此，本次视频内容将较为简短。教师祝贺学生顺利推进到课程的当前阶段，并希望学生已经收获颇丰且享受学习过程。

期末考试准备指南 🎯

课程剩余时间已经不多，距离期末考试仅有两周。现在是开始准备期末考试的合适时机。

期末考试的格式将与期中考试基本相同。考试内容将主要聚焦于期中考试之后所学的主题。

但是，学生仍然需要掌握并能够解答期中考试之前所涵盖的主题问题。

课程后续内容已不多，主要包括以下几部分：

视图与授权
递归查询
在线分析处理
无SQL系统

后续安排与鼓励 💪

下周，教师将再次通过办公时间与大家见面，重点讨论SQL视图相关的附加问题，并提供解题思路，希望这些内容能成为有益的学习资料。

此外，教师将制作一个期末考试复习视频，计划于两周后的3月21日（星期四）发布。

教师再次祝贺学生坚持到课程的这一阶段，肯定目前的课程进展，鼓励大家继续保持，并约定下周再见。

本节课中我们一起学习了本次办公时间的核心内容：回顾了课程为促进互动所做的多种尝试，总结了近期的学习任务，并获得了关于期末考试范围、格式及后续复习安排的明确指导。

30：期中考试解答 📚

在本节课中，我们将一起回顾期中考试的所有问题，并提供快速解答。我们将涵盖XML、关系代数、SQL、函数依赖和多值依赖等多个主题，帮助你巩固所学知识。

📝 问题一：XML DTD 分析

我们首先来看一个关于城市信息的DTD。在一个符合此DTD的XML文档中，我们需要确定某些元素的最小和最大出现次数。

1. 市长元素的数量

我们看到，在DTD中，<government>元素内部包含一个<mayor>元素。<mayor>元素不是可选的，并且没有指定可以出现多次。

因此，<mayor>元素的数量是固定的。

结论：<mayor>元素的最小值和最大值都是 1。

2. 图书馆元素的数量

接下来，我们分析<library>元素。根据DTD，<community>元素是可选的，并且可以出现一次或多次。每个<community>元素内部，<library>是一个可选元素（用?表示）。

这意味着，整个文档中可以没有<library>元素。同时，由于可以有多个<community>，每个都可以包含一个<library>，因此<library>的数量没有上限。

结论：<library>元素的最小值是 0，最大值是 无限制。

📋 问题二：XML Schema 分析

现在，我们来看一个关于乘客信息的XML Schema。我们需要确定符合此模式的文档中，某些元素的最小和最大出现次数。

1. 名字元素的数量

在Schema中，<name>元素位于一个<sequence>内。它没有指定minOccurs，因此默认至少出现一次。同时，maxOccurs被指定为2。

结论：<name>元素的最小值是 1，最大值是 2。

2. 零食元素的数量

<snack>元素出现在一个<choice>组中，与<meal>元素二选一。它被指定maxOccurs为2，并且由于在<choice>中，它可以不出现。

结论：<snack>元素的最小值是 0，最大值是 2。

🧮 问题三：关系代数表达式

本节中我们来看看关系代数问题。我们有三张表：Cars, Manufacturers, 和 Owns。

1. 表达式一：寻找车主

考虑以下表达式：

π owner (Owns ⋈ σ color=‘red’(Cars) ⋈ σ maker=‘Toyota’(Manufacturers))

这个表达式执行了自然连接，并选择了颜色为红色、制造商为丰田的汽车，最后投影出车主姓名。

结论：该表达式的结果是 所有拥有红色丰田汽车的车主。对应选项 A。

2. 表达式二：寻找拥有多辆车的车主

考虑以下表达式：

π owner (σ owner=O2.owner ∧ serial≠S2.serial (ρ O2(Owns) × ρ S2(Owns)))

这是一个自连接操作，它查找同一车主（owner）但不同车辆（serial）的配对，然后投影出车主。

结论：该表达式的结果是 所有拥有至少两辆车的车主。对应选项 C。

3. 表达式三：寻找2010年生产汽车的制造商

我们需要一个表达式来找出在2010年至少生产过一辆车的所有公司。

正确的思路是：先选择2010年的汽车，然后与制造商表连接以获取制造商信息，最后投影出制造商字段。

结论：正确的表达式是 σ year=2010(Cars) ⋈ Manufacturers，然后投影 maker。对应选项 B。

4. 表达式四：寻找最新年份

要找到数据库中汽车的最新年份，我们需要找到year属性的最大值。在关系代数中，这通常通过“找出所有不是最大值的年份，然后从全部年份中减去它们”来实现。

结论：正确的表达式是最后一个选项，它通过自连接和差集操作实现了求最大值的逻辑。

💾 问题四：SQL 查询等价性判断

现在，我们进入SQL部分。我们需要判断给定的几对查询在所有可能的数据库实例上是否等价。数据库包含两个非空关系R和S，属性为A和B。A是主键，B不是，且没有空值。

以下是需要判断的查询对：

1. 查询对一

Q1: SELECT A FROM R;
Q2: SELECT R.A FROM R, S WHERE R.A = S.A;

分析：Q1返回R中所有的A。Q2返回R与S在A上做等值连接后，R中的A。如果S中没有与R的A匹配的值，Q2可能返回空集，而Q1不会。
结论：这两个查询 不等价。

2. 查询对二

Q1: SELECT MAX(A) FROM R;
Q2: SELECT A FROM R WHERE A >= ALL (SELECT A FROM R);

分析：Q1返回R中A的最大值。Q2返回那些大于等于R中所有A的A值，通常就是最大值。由于A是主键，没有重复值，因此Q2也只会返回一个值（最大值）。
结论：这两个查询等价。

3. 查询对三

Q1: SELECT B FROM R;
Q2: SELECT B FROM R GROUP BY B;

分析：Q1返回R中所有的B值（可能有重复，顺序任意）。Q2按B值分组，每组返回一个B值（去重效果），并且结果通常按B排序。
结论：这两个查询 不等价。

4. 查询对四

Q1: SELECT a FROM R WHERE EXISTS (SELECT * FROM S WHERE S.a = R.a);
Q2: SELECT a FROM R INTERSECT SELECT a FROM S;

分析：Q1返回R中那些a值也出现在S中的元组。Q2返回R和S在a属性上的交集。由于a是主键，两个查询都不会产生重复，且结果相同。
结论：这两个查询等价。

🗃️ 问题五：SQL 聚合查询

一个数据库使用scores表管理学生作业成绩，包含studentID, assignmentID, timestamp, score。主键是(studentID, assignmentID, timestamp)。

我们需要找出提交某个作业超过10次的学生ID。

正确的查询需要按studentID和assignmentID分组，然后筛选出提交次数大于10的组。

结论：正确的SQL语句是：

SELECT DISTINCT studentID
FROM scores
GROUP BY studentID, assignmentID
HAVING COUNT(*) > 10;

🔑 问题六与七：函数依赖与键

我们有一个关系R(A, B, C, D, E)和一组函数依赖(FDs)。

1. 推导新的函数依赖

已知FDs为：A -> D, C -> A, B, D, B -> E。
问题：C -> E是否一定成立？

分析：由 C -> A, B, D 和 A -> D，可知C能决定A、B、D。又由 B -> E，可知C也能决定E。
结论：C -> E 一定成立。

2. 寻找关系的键

已知FDs为：A -> B, B, C -> E, D -> A。
我们需要找出R的候选键。

分析：属性D从未出现在任何FD的右侧，因此任何候选键必须包含D。我们检查包含D的选项：

A, D: 由D可得A，由A可得B，但我们无法得到C和E。
C, D: 由D可得A，由A可得B。现在我们有了B和C，由B, C -> E可得E。因此C, D可以决定所有属性。
结论：R的键是 C, D。

🔗 问题八：多值依赖 (MVD)

给定一个关系R(A, B, C)的具体实例，我们需要判断哪个多值依赖被该实例满足。

回顾MVD X ->> Y 的定义：对于任意两个在X上一致的元组，交换它们的Y值后产生的新元组也必须存在于关系中。

我们逐一检查选项：

C ->> B: 不成立。例如，元组(4,2,3)和(5,1,3)在C上一致(都是3)，交换B值后应产生(4,1,3)和(5,2,3)，但(4,1,3)不存在。
B ->> C: 成立。检查所有B值相同的元组对，交换C值后产生的新元组均可在关系中找到。
C ->> A: 不成立。分析类似第一项。
A ->> B: 不成立。例如，元组(4,2,3)和(5,1,1)在A上不一致，无需检查；但元组(4,2,3)和(4,2,5)在A上一致(都是4)，交换B值后就是自身，成立。然而，对于(5,1,1)和(5,1,3)，交换B值后就是自身，也成立。但考虑(4,2,3)和(5,1,3)？它们的A不同。实际上，需要检查所有在A上一致的配对。元组(5,1,1)和(5,1,3)在A上一致(都是5)，交换B值后就是自身，没问题。但元组(5,1,1)和(5,1,3)已经涵盖了所有A=5的情况。看起来A->>B可能成立？但原题指出B->>C是正确答案。我们应相信系统性的检查：对于A ->> B，考虑元组(5,1,1)和(5,1,3)，它们A相同，B也相同，交换无意义。但考虑A=4的元组，只有(4,2,3)和(4,2,5)，B相同。似乎没有反例。但题目设定正确答案是B ->> C，因此A ->> B在该实例中不成立。可能存在反例，如考虑A=4和A=5的元组？不，A值必须相同。鉴于题目答案，我们采信B ->> C是唯一被满足的MVD。

结论：被满足的多值依赖是 B ->> C。

🧩 问题九：BCNF分解算法

考虑关系R(A, B, C, D)，具有函数依赖 A -> B 和 B -> C。我们需要找出遵循课堂所讲BCNF分解算法的一种可能结果。

分解过程：

初始关系 R(A, B, C, D)。由于 A -> B 且 A 不是超键（仅凭A无法决定C和D），此FD违反BCNF。
根据 A -> B 分解：
- R1(A, B)
- R2(A, C, D)
在R2中，有 A -> C（传递依赖：A -> B 且 B -> C）。A在R2中也不是超键（无法决定D），因此违反BCNF。
根据 A -> C 分解 R2：
- R21(A, C)
- R22(A, D)

最终分解结果：三个关系 - R1(A, B), R21(A, C), R22(A, D)。

🏆 问题十：BCNF 与 4NF 判断

最后，考虑关系R(A, B, C)，具有函数依赖 A, B -> C 和多值依赖 C ->> B。我们需要判断R属于哪种范式。

分析：

BCNF判断：唯一的函数依赖是 A, B -> C。左侧A, B是关系的超键（可以决定所有属性）。没有违反BCNF的函数依赖。
- 因此，R 是BCNF。
4NF判断：存在多值依赖 C ->> B。对于4NF，每个非平凡MVD的左侧必须是超键。这里，C不是超键（仅凭C无法决定A），且 C ->> B 是非平凡的（B不是C的子集，且B和C的并集未包含所有属性）。
- 因此，存在违反4NF的MVD，R 不是4NF。

结论：关系R 是BCNF，但不是4NF。

📖 课程总结

本节课中，我们一起学习了期中考试的解答，涵盖了以下核心内容：

如何根据DTD和XML Schema分析元素的最小和最大出现次数。
如何解读和编写关系代数表达式，以完成选择、投影、连接及聚合操作。
如何判断SQL查询的等价性，理解了集合操作与子查询的语义。
如何使用GROUP BY和HAVING子句进行分组统计和过滤。
如何根据函数依赖推导新的依赖关系并找出关系的候选键。
如何通过具体实例判断多值依赖是否成立。
如何应用BCNF分解算法将关系分解为符合范式的子关系。
如何区分BCNF和4NF，理解多值依赖对更高范式的影响。

希望这次详细的解答能帮助你澄清疑惑，并为期末考试做好充分准备。

posted @ 2026-02-20 16:44 绝不原创的飞龙阅读(73) 评论(0) 收藏举报

刷新页面返回顶部

龙哥盟

掠夺·扩张·投机·博弈

斯坦福-CS145-数据库导论笔记-全-

斯坦福 CS145 数据库导论笔记（全）

1：01-01 - 数据库导论 🗄️

数据库管理系统的核心功能

1. 海量数据

2. 持久性

3. 安全性

4. 多用户

5. 便利性

6. 高效性

7. 可靠性

课程范围与相关概念

数据库核心概念

数据模型

模式与数据

数据操作

数据库系统中的角色

总结

2：02-01-关系模型 🗄️

关系与表格 📊

属性与类型 📝

元组与实例 📄

空值（NULL）及其处理 ⚠️

键（Key）的概念 🔑

SQL创建表语句 💻

总结 📚

3：02-02 关系型数据库查询 🗄️

数据库的基本使用流程

查询交互模式

查询语言的特点

查询的复杂度与语言特性

两种主要的查询语言

4：结构良好的 XML 📖

概述：什么是 XML？

XML 的核心组成部分 🧱

1. 标签与元素

2. 属性

3. 文本内容

XML 与关系模型的比较 ⚖️

什么是结构良好的 XML？✅

XML 的展示与样式 🎨

总结 📝

5：XML Schema 入门教程 🗂️

概述

XML Schema 的核心特性

数据类型

键声明

键引用

出现次数约束

总结

6：JSON 简介 📖

概述 📋

JSON 的发音与背景 🗣️

JSON 的基本概念 💡

JSON 的基本结构 🧱

基本原子值

复合值类型

对象 (Object)

数组 (Array)

JSON 与关系模型的比较 ⚖️

JSON 与 XML 的对比 🔄

JSON 数据的有效性 ✅

总结 🎯

7：关系代数基础 🧮

回顾：关系查询的本质

基础操作符

选择操作符 (σ)

投影操作符 (π)

组合选择与投影

关于重复项的重要说明

组合多个关系

笛卡尔积 (×)

使用笛卡尔积进行查询

自然连接 (⋈)

连接更多关系

θ-连接 (⋈θ)

总结

8：WHERE子句中的子查询 🧩

θ-连接 (⋈_θ)

使用 `IN` 和 `NOT IN` 进行成员资格测试

使用 `EXISTS` 和 `NOT EXISTS` 测试存在性

示例4：查找GPA最高的学生（使用`NOT EXISTS`）

使用 `ANY` 和 `ALL` 进行量化比较

示例5：查找GPA最高的学生（使用`ALL`）

示例6：查找不是来自最小规模高中的学生（使用`ANY`）