UNSW-COMP3111-数据库系统笔记-全-

UNSW COMP3111 数据库系统笔记（全）

001：课程介绍与数据建模

在本节课中，我们将要学习COMP3311数据库系统课程的基本介绍，包括课程团队、运行方式、学习资源以及初步的实践准备。

课程团队介绍

首先，我们来认识一下负责本课程的人员。

  • 讲师：我是本课程的讲师，我的名字是Yuue Kang Li。你可以称呼我为Yuuekang、Doctor Li或YK。
  • 联系方式：课程相关的疑问请发送邮件至课程邮箱或使用课程论坛，请避免发送至我的个人邮箱。
  • 课程管理员：Dlan是我们的课程管理员，负责除讲座外的几乎所有事务。

课程运行方式

上一节我们介绍了课程团队，本节中我们来看看课程将如何运行。本课程在UNSW已开设约27年。

以下是本课程在2024年第一学期的主要信息源和活动安排：

  • 课程网站：你可以在此找到幻灯片、时间表和课程大纲等大部分信息。
  • 论坛：今年我们使用了一个新的论坛系统。
  • 录播与资源：讲座录播需要通过Moodle或Echo360访问。
  • 学习材料：今年的课程内容将主要整合在讲座笔记中，其中包含了详细内容、总结和练习。但你仍可能需要教科书来获取更多细节。

课程的主要活动包括：

  • 讲座：覆盖课程核心内容，讲解示例。
  • 辅导课：用于讲解练习，你可以向导师提问。
  • 实践练习：帮助你学习系统和技能，这是工程学习的重要部分。
  • 作业：用于练习你的技能。
  • 考试：用于展示你的知识和技能，占总成绩的60%。

幻灯片与学习提示

关于课程材料，今年的幻灯片采用了一些颜色编码和图标，以帮助你快速浏览。

  • 知识总结：幻灯片左上角出现特定图标时，表示该页是知识总结。
  • 练习相关：出现黄色“E”图标，表示内容与练习相关。
  • 需注意内容：出现特定小图标，表示该部分内容需要特别注意。

实践环境设置

正如之前提到的，我们是工程师，需要动手实践。本课程需要大量练习，因此你需要设置自己的实践环境。

你需要在学校提供的云服务上运行你自己的PostgreSQL服务器。如果尚未完成，请尝试在下一周的第一次辅导课前完成设置。

具体步骤如下：

  1. 访问相关资源链接，查看详细设置指南。
  2. 如果今早尝试设置时遇到了问题或错误，该问题已被修复。
  3. 课程管理员Dlan可以展示如何操作的实验演示。

请按照步骤尝试设置你自己的服务器。

本节课中我们一起学习了COMP3311数据库系统课程的概况，包括教学团队、课程资源获取方式、学习活动安排以及最重要的初步实践环境设置步骤。请务必完成服务器设置，为后续的学习和实践做好准备。

002：实体关系模型与关系模型 🗄️

在本节课中，我们将学习数据库设计的核心建模技术：实体关系模型和关系模型。我们将从基本概念开始，逐步了解如何用图形化的方式描述现实世界的数据及其关联。

课程更新与材料说明

在开始主要内容之前，有几个课程相关的更新需要说明。

首先，新的课程材料已经上线，所有辅导课内容现已在WebCMS上发布。

其次，在准备讲义时，我发现了一些“宝藏”——以前的课程笔记。这些笔记比当前的幻灯片内容更详细，这可能是因为新南威尔士大学以前实行的是12周学期制，而现在只有9周。请注意，并非笔记中的所有内容都是考试范围，只有讲义、幻灯片和辅导课中涵盖的内容才是可考的。这些笔记可能已有约20年历史，因此与当前幻灯片可能存在一些差异。

以下是三种主要的课程材料：

  • 练习幻灯片：包含核心知识点和练习题。
  • 历史课程笔记：提供更详细的背景和解释。
  • 教科书：作为详尽的参考手册，用于查询概念的准确定义。

关于如何利用这些材料：

  • 如果你的目标是通过课程，那么练习幻灯片和辅导课内容就足够了。
  • 如果你的目标是精通数据库，我建议你深入研究课程笔记和教科书。

最后，请注意本课程有一个重要的考核要求：你必须在期末考试中至少获得40%的分数，并且总评成绩达到50% 才能通过这门课。这一点非常重要。

数据建模与ER模型概述

上一节我们介绍了课程的基本信息，本节中我们来看看数据建模的核心过程，并引出实体关系模型。

数据建模是一个设计过程，其目标有三个：

  1. 涵盖数据库中需要存储的信息。
  2. 涵盖数据库中不同数据项之间的关系。
  3. 涵盖对数据的约束。

这个过程的输入通常是用户提供的需求，输出则是数据库结构的半形式化描述。实体关系模型 就是其中一种重要的形式化描述方法。

ER模型将世界建模为一组相互关联的实体。它包含三个主要组成部分：

  • 属性：描述实体或关系的特征，类似于面向对象设计中的对象变量。
  • 实体：具有相同属性集合的对象的集合，可以看作是对象本身。
  • 关系：实体之间的关联。

ER模型并非完全标准化，在实际应用中可能存在一些变体。为了简便起见，本课程笔记采用了两种教科书中通用的符号。

为了可视化ER模型，我们使用ER图。一个ER图包含：

  • 一组实体集定义。
  • 一组关系集定义。
  • 与实体和关系相关联的属性。
  • 实体与关系之间的连接。

请注意，在术语上，我们有时会用“实体”来简称“实体集”。

ER图符号详解

上一节我们了解了ER模型的基本构成，本节我们将通过一个示例图来详细讲解ER图中使用的各种符号。

这是一个ER图的例子，其中包含多种符号。别担心，我们会逐一讲解它们。

以下是ER图中常用的符号及其含义。请注意，我划掉了其中两个符号，这并不是因为它们不重要，而是在后续的幻灯片中较少出现。我们会先通过例子讲解其他符号，最后再回来解释这两个。

首先，让我们从ER建模中最核心的概念——实体集开始。

一个实体集可以被视为具有相同属性集合的一组实体。例如，“学生”是一个实体集，而“张三”是其中的一个具体实体。

以下是实体和关系的关键概念列表：

  • 实体集：用矩形表示，代表一类对象（如Student， Course）。
  • 关系集：用菱形表示，连接两个或多个实体集，表示它们之间的关联（如Enrols）。
  • 属性：用椭圆形表示，是描述实体或关系的性质（如studentID， name）。
  • 键属性：属性名下加下划线，用于唯一标识一个实体（如studentID）。
  • 多值属性：用双线椭圆形表示，表示一个属性可以有多个值（如phoneNumber）。
  • 派生属性：用虚线椭圆形表示，其值可以从其他属性推导出来（如age可从birthDate派生）。
  • 复合属性：可以划分为更小部分的属性（如address可包含street， city， postcode）。

现在，让我们回到之前被划掉的两个符号：

  • 弱实体集：用双线矩形表示，其存在依赖于另一个“强”实体集，没有自己的键。
  • 识别关系集：用双线菱形表示，是弱实体集与其所属强实体集之间的特殊关系。

关系模型基础

在深入理解了如何用ER图进行概念设计后，我们需要将其转化为计算机能够存储和处理的格式。这就是关系模型的作用。

关系模型使用表（在关系模型中称为“关系”）来组织数据。每个表有唯一的名称，由行（元组）和列（属性）组成。

关系模型的核心概念可以用以下公式表示：
关系 = 表头（属性集合） + 表体（元组集合）

一个关系模式可以形式化地定义为：
R(A1, A2, ..., An)
其中，R是关系名，A1到An是属性名。

从ER模型转换到关系模型有一套系统的规则：

  1. 实体集转换：每个实体集转换为一个单独的关系（表）。实体集的属性成为该表的属性，实体集的键成为该表的主键。
  2. 关系集转换：处理方式取决于关系的基数比（一对一、一对多、多对多）。
    • 多对多关系：转换为一个新的关系表。该表的主键由相关联的实体集的主键组合构成。
    • 一对多/一对一关系：通常通过将“一”方的主键作为外键添加到“多”方的表中来实现，而不是创建新表。

总结

本节课中我们一起学习了数据库系统设计的两个核心建模阶段。

首先，我们探讨了实体关系模型，这是一种用于概念设计的半形式化工具。我们学习了如何使用ER图、实体、关系、属性以及各种约束（如键、多值属性等）来图形化地描述现实世界的数据需求。

接着，我们了解了关系模型，它是数据库的逻辑实现基础。我们讨论了如何将ER图中的概念系统地转换为关系模式（即表结构），包括实体集和不同类型关系集（特别是多对多关系）的转换规则。

理解从ER模型到关系模型的转换过程，是设计一个结构良好、高效且无冗余的数据库的关键第一步。在接下来的课程中，我们将在此基础上学习如何使用SQL语言来操作这些关系表。

003：ER模型到关系模型的映射与SQL简介

在本节课中，我们将学习如何将概念层面的ER模型正式映射为逻辑层面的关系模型，并初步了解用于操作关系数据库的标准语言SQL。

课程公告

在进入主要内容之前，我们先了解几个重要通知。

以下是关于课程评估的重要信息：

  • 测验一：已发布。可通过指定链接访问，可多次尝试，仅最后一次尝试计分。截止时间为本周五午夜前。逾期提交将按规则扣分，严重逾期将无法获得该测验分数。
  • 考试门槛：本课程设有最终考试门槛。要及格，必须在期末考试中获得至少40%的分数，并且总评成绩达到50%或以上。
  • 技术问题：部分讲座录音存在音频串扰问题，技术团队正在修复。

ER模型回顾

上一节我们介绍了ER模型的基本概念，本节中我们来看看ER模型与关系模型之间的对应关系。

ER模型中的元素可以大致对应到关系模型中：

  • ER属性 大致对应 关系属性。但关系属性有更严格的约束，例如关联的域。
  • 关系表的行 可以看作 ER实体。
  • 关系表本身 可以看作 实体集 或用于表示 ER关系。

让我们通过一个例子来热身，回顾ER图的解读。

以下是图中关键元素的解读：

  • 继承与互斥：图中“Medical Professional”实体下方的圆圈和“d”表示继承，且子类“Doctor”与“Pharmacist”是互斥的。这意味着一个医疗专业人员只能是医生或药剂师中的一种，不能同时是两者。
  • 实体“Prescription”：该实体有两个属性：ID和Date。其中ID带有下划线，表示它是该实体的主键。
  • 关系“Contains”：这是一个从“Prescription”到“Drug”的关系。
    • 基数约束：关系两端都没有箭头，表示这是一个多对多关系。
    • 参与约束：连接到“Prescription”的线是粗线，表示完全参与。即，每一个处方实体都必须参与这个“Contains”关系。
    • 描述：因此，这个关系可以描述为“每一个处方都包含一种或多种药物”。反过来，由于连接到“Drug”的线是细线（部分参与），可以描述为“一种药物可能出现在一个或多个处方中”。

关系模型核心概念

在学习了ER模型后，我们需要一个更形式化的模型来实现数据库，这就是关系模型。

关系模型基于数学中的集合论概念。以下是其核心组成部分：

  • 关系：一个关系可以看作一张表。它有一个关系名和一组属性。
  • 属性：每个属性有一个唯一的名称和一个域。域定义了该属性可以取的所有有效值的集合。
    • 例如，属性 Age 的域可能是 整数集合。
  • 元组：元组是属性值的有序列表，对应表中的一行。
    • 关键点：元组的顺序很重要。例如，元组 (John, 25) 与 (25, John) 是不同的。
  • 关系实例：一个关系在特定时刻的所有元组的集合，就是该关系的实例，也就是我们看到的表数据。

上图展示了一个关系实例的例子，它包含了具体的元组数据。

ER到关系模型的映射规则

理解了两种模型的基础后，我们现在来学习如何系统地将ER图转换为关系模式。这是数据库设计的关键一步。

以下是主要的映射规则：

1. 映射强实体
为每个强实体创建一个关系（表）。实体的属性映射为关系的属性。实体的主键成为关系的主键。

  • 示例：强实体 Student(ID, Name) 映射为关系：Student(ID, Name)，其中 ID 是主键。

2. 映射弱实体
为每个弱实体创建一个关系。包含弱实体的所有简单属性和派生属性。此外，必须包含所依赖的强实体的主键作为外键。弱关系的主键由其自身的分辨符与所依赖强实体的主键组合而成。

  • 公式：弱实体关系 = 弱实体属性 + 依赖强实体的主键（作为外键）。
  • 示例：弱实体 Dependent 依赖于强实体 Employee。映射结果为：Dependent(EmpID, DependentName, ... )，其中 (EmpID, DependentName) 构成复合主键，EmpID 同时也是引用 Employee 表的外键。

3. 映射二元关系
规则取决于关系的基数（一对一、一对多、多对多）。

  • 多对多 (N:M)：为关系本身创建一个新的关系。该关系的属性包括参与实体的主键（作为外键），以及关系本身的任何属性。这些外键的组合成为新关系的主键。
    • 代码表示：
      CREATE TABLE Contains (
    PrescriptionID INT,
    DrugID INT,
    PRIMARY KEY (PrescriptionID, DrugID),
    FOREIGN KEY (PrescriptionID) REFERENCES Prescription(ID),
    FOREIGN KEY (DrugID) REFERENCES Drug(ID)
);
  • 一对多 (1:N)：在“多”的一侧实体对应的关系中，加入“一”侧实体的主键作为外键。同时，也可以加入关系本身的属性。
  • 一对一 (1:1)：可以将任一侧实体的主键作为外键加入到另一侧实体对应的关系中。通常选择参与度更高（完全参与）的一方来包含外键，以减少空值。

4. 映射继承（ISA层次结构）
有多种映射方法，常用的是：

  • 为每个实体创建表：为父类和每个子类分别创建表。子类表包含自身特有属性，并包含父类的主键（同时作为自身主键和外键）。
    • 示例：MedicalProfessional(ID, Name)，Doctor(ID, Specialization)，Pharmacist(ID, PharmacyLicense)。Doctor.ID 和 Pharmacist.ID 既是主键，也引用 MedicalProfessional.ID。

SQL简介

将ER模型映射为关系模式后，我们需要一种语言来操作这些表，这就是SQL。

SQL是结构化查询语言，是与关系数据库交互的标准语言。它主要包含三个部分：

以下是SQL的主要组成部分：

  • 数据定义语言：用于定义和修改数据库结构，如创建、修改、删除表和索引。核心命令是 CREATE， ALTER， DROP。
  • 数据操作语言：用于操作表中的数据，包括插入 (INSERT)、更新 (UPDATE)、删除 (DELETE) 和查询 (SELECT) 数据。
  • 数据控制语言：用于控制数据库的访问权限，如授予 (GRANT) 或撤销 (REVOKE) 用户权限。

一个最基本的SQL查询示例是检索表中所有数据：

SELECT * FROM Student;

这条语句会从 Student 表中选择所有列 (*) 的所有行。

总结

本节课中我们一起学习了数据库设计从概念模型到逻辑模型的关键转换。我们首先回顾了ER模型，然后详细讲解了将ER图中的实体、关系和继承结构映射为关系模式（表结构）的正式规则。最后，我们介绍了用于操作这些关系表的标准语言SQL的基本概念和组成部分。掌握这些内容是进行实际数据库设计和操作的基础。

004：SQL DDL与ER模型映射 🗄️

在本节课中，我们将学习SQL的数据定义语言（DDL），并了解如何将实体-关系（ER）模型映射为SQL语句。课程首先会进行一些重要通知，然后深入讲解SQL语法、元数据定义语言，以及ER模型到SQL的具体映射方法。

课程通知 📢

上一节我们介绍了数据模型的设计过程。在进入本节主要内容前，有两项通知需要说明。

第一项通知关于测验截止日期。原定截止时间为明天午夜。但由于计算机科学与工程学院大楼将进行全天24小时的停电维护，这将影响包括WebCMS在内的所有网络服务。因此，测验截止日期已延长至周六午夜。尽管如此，仍建议你尽量在明天晚上8点前完成测验，以防意外情况发生。

第二项通知关于学习方法。上周我们学习了如何设计数据模型，这是一个思考过程。现在，你将接触到大量需要记忆的概念、语法和语句。学习本阶段课程时，分享一个我本科时的经历：我曾因缺乏编程基础，面对需要记忆的众多语法细节感到焦虑，例如C语言中a++与++a的区别。我的导师告诉我，作为计算机科学学生，最重要的是学会如何搜索。你无需记住所有细节，但应记住核心概念和关键词。当遇到不确定的问题时，可以随时搜索解决方案。实践同样至关重要，通过日常使用编程语言，语法会自然内化，无需刻意背诵。如今，你可以利用Stack Overflow、ChatGPT或在课程论坛提问。因此，不必为繁多的概念感到压力，讲座的目的是在你的脑海中存储关键词，以便在解决实际问题时能够联想并搜索。

ER关系描述示例 📝

在深入今天的主要内容之前，让我们快速回顾一下如何在ER图中描述关系，这对你的第一次测验很重要。

以下是一个具体的例子，展示如何描述这些关系。想象一个导师辅导学生的场景。

假设我们有三位导师：John、Yuekang和Dlan。我们有五位学生：Ashley、Bob、Chris、David和Edwin。在这个场景中：

  • John不教授任何课程。
  • Yuekang教授Ashley和Bob。
  • Dlan教授Chris和David。
  • 学生Edwin没有注册任何课程，不被任何导师教授。

那么，我们应如何描述从导师到学生的这个“指导”关系？

首先，看箭头方向。箭头指向导师端，意味着在每次“指导”关系实例中，有且仅有一位导师参与。另一端（学生端）没有箭头，意味着可以有多个学生参与同一次关系实例。

其次，看连接线的粗细。两端的线都是细线，这表示部分参与。也就是说：

  • 有些导师可能不进行教学（例如John）。
  • 有些学生可能不接受指导（例如Edwin）。

本节课中我们一起学习了课程的重要通知、高效学习本课程的方法，并通过一个具体示例回顾了ER图中基数性和参与度的描述方法。接下来，我们将正式进入SQL DDL与ER模型映射的核心内容。

005：数据修改与单表查询

在本节课中，我们将学习如何使用SQL修改数据库中的数据，以及如何编写针对单个表的查询语句。我们还会简要介绍视图的概念。

课程公告与回顾

在开始新内容之前，我们先来看一些课程相关的公告，并快速回顾一下之前学过的知识。

课程公告

以下是本周的重要通知。

  • 关于测验1：上周的测验1已完成。大部分提交了答案的同学都取得了不错的成绩，平均分略高于往期。请注意，测验2已经开始，截止日期是本周五午夜，请务必按时完成。
  • 关于作业1：第一次作业即将发布，提交截止日期是第5周结束前（即在灵活周之前）。关于作业1的详细说明，将在下一次课上进行讲解。请密切关注邮件和课程管理系统（WebCMS）的通知。

知识回顾：从ER图到关系模型

上一节我们介绍了如何将实体关系（ER）图映射到关系模型和SQL。本节中，我们来快速回顾一下核心的映射规则。

以下是主要的映射方法总结：

  • 实体映射：
    • 强实体：直接转换为一个表。
    • 弱实体：转换为一个表，并正确设置指向其所有者实体的外键。
  • 关系映射：
    • 多对多关系：创建一个新表，其主键是参与此关系的所有实体的主键的集合。
    • 一对多关系：将关系的属性放在“多”那一侧实体对应的表中。
    • 一对一关系：将关系的属性放在任意一侧参与实体的表中。
    • 多元关系（带箭头）：创建一个新表，其主键是所有没有箭头指向的参与实体的主键的集合。
  • 属性映射：
    • 复合属性：将其“扁平化”，拆分为表的多个列。
    • 多值属性：将其转换为一个弱实体（及对应的表）来处理。
  • 子类映射：
    • ER风格：为超类和每个子类创建单独的表。子类表包含自身特有属性，并通过外键关联到超类表。
    • 全部风格：为每个子类创建单独的表，每个表都包含超类和自身的所有属性。
    • 单表风格：只创建一个表，包含所有可能的属性，允许某些列为空值（NULL）。

这些知识告诉我们如何从ER图出发，设计出具体的关系数据库模式，并最终用SQL实现。

使用SQL修改数据

回顾了数据库设计后，现在我们将重点转向SQL的数据操作部分。我们将学习如何使用INSERT、UPDATE和DELETE语句来修改数据库中的数据。

INSERT 语句

INSERT语句用于向表中添加新的数据行。

其基本语法如下：

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

例如，向Students表插入一条新记录：

INSERT INTO Students (student_id, name, age)
VALUES (12345, ‘张三’, 20);

UPDATE 语句

UPDATE语句用于修改表中已有的数据。

其基本语法如下：

UPDATE 表名
SET 列1 = 新值1, 列2 = 新值2, ...
WHERE 条件;

WHERE子句用于指定要更新哪些行。务必谨慎使用，否则可能更新整个表。
例如，将学号为12345的学生的年龄改为21岁：

UPDATE Students
SET age = 21
WHERE student_id = 12345;

DELETE 语句

DELETE语句用于从表中删除数据行。

其基本语法如下：

DELETE FROM 表名
WHERE 条件;

同样，WHERE子句至关重要，它指定了要删除哪些行。如果没有WHERE子句，将删除表中的所有数据。
例如，删除学号为12345的学生记录：

DELETE FROM Students
WHERE student_id = 12345;

单表查询与视图

掌握了如何修改数据后，接下来我们学习如何从单个表中检索数据，并了解视图这一有用工具。

单表查询基础

单表查询主要使用SELECT语句，从指定的一个表中获取数据。

一个完整的SELECT语句通常包含以下部分：

SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件
ORDER BY 排序列 ASC|DESC;
  • SELECT：指定要查询哪些列。
  • FROM：指定从哪个表查询。
  • WHERE：设置过滤条件，只返回满足条件的行。
  • ORDER BY：对结果进行排序（ASC为升序，DESC为降序）。

例如，查询Students表中所有年龄大于18岁的学生姓名和年龄，并按年龄降序排列：

SELECT name, age
FROM Students
WHERE age > 18
ORDER BY age DESC;

视图的概念

视图（View）是一种虚拟表，其内容由查询定义。你可以像使用普通表一样查询视图，但它并不实际存储数据。

创建视图的语法如下：

CREATE VIEW 视图名称 AS
SELECT 查询语句;

视图的主要作用包括：

  • 简化复杂查询：将复杂的查询逻辑封装在视图中，用户只需查询简单的视图即可。
  • 数据安全：可以只向用户暴露视图（包含部分列或行），而不是底层基表，从而隐藏敏感数据。
  • 逻辑数据独立性：即使底层表结构发生变化，只要视图的查询结果不变，依赖视图的应用程序就无需修改。

例如，创建一个只包含成年学生姓名和学号的视图：

CREATE VIEW Adult_Students AS
SELECT student_id, name
FROM Students
WHERE age >= 18;

创建后，你可以直接查询这个视图：

SELECT * FROM Adult_Students;

总结

本节课中我们一起学习了SQL数据操作的核心内容。我们首先回顾了从ER图到SQL的映射方法，这是数据库设计的基础。接着，我们详细讲解了如何使用INSERT、UPDATE和DELETE语句来修改表中的数据，并强调了WHERE子句的重要性。最后，我们介绍了单表查询的基本结构以及视图的概念和用途，视图是简化查询和增强安全性的有力工具。掌握这些知识，你将能够有效地管理和查询数据库中的信息。

006：视图与多表查询

在本节课中，我们将学习SQL中的两个重要概念：视图和多表查询。首先，我们会回顾一些课程公告和上节课的内容，然后详细介绍视图的创建与使用，最后探讨如何通过连接多个表来执行更复杂的查询。

课程公告

以下是本周的重要通知。

  • Quiz 2的截止日期是明天午夜。建议你完成它，因为即使随机选择答案，得到零分的可能性也很低。
  • Assignment 1将于本周末发布，提交截止日期是第5周结束前。你将有大约两周时间来完成它。本次作业将涵盖SQL视图、SQL函数以及PL/pgSQL函数。你可以使用任何你喜欢的方式完成作业，但希望你能够独立完成。
  • 下周我们将举办一些帮助课程。在这些课程中，你可以向助教提问，例如询问教程答案或澄清Assignment 1的问题，但不能询问作业的答案。

路线图与回顾

上一节我们介绍了课程安排，现在我们来快速回顾一下上节课（周一）学习的内容。

在上节课中，我们主要学习了数据修改语句和单表查询。

数据修改回顾

我们学习了三种数据操作语句：插入、删除和更新。

1. 数据插入
插入语句有三种风格。

  • 第一种：仅指定关系名，插入一行数据。语法为：INSERT INTO relation_name VALUES (...);
  • 第二种：在关系名后指定属性名，这样插入的数据就不必遵循模式定义的顺序，并且可以选择性地插入部分属性。语法为：INSERT INTO relation_name (attr1, attr2, ...) VALUES (val1, val2, ...);
  • 第三种：使用单个语句一次性插入多行数据。语法为：INSERT INTO relation_name VALUES (...), (...), ...;

此外，实践中常需要进行批量插入。PostgreSQL提供了 COPY 命令来从文件或其他源批量导入数据。

2. 数据删除
删除语句的基本语法是：DELETE FROM table_name WHERE condition;
我们可以使用WHERE子句来过滤要删除的数据。

删除操作有两种实现逻辑。

  • 第一种：遍历表，逐行检查条件，满足则立即删除该行。
  • 第二种：使用两个循环。第一个循环标记所有满足条件的元组，第二个循环一次性删除所有被标记的元组。

PostgreSQL实际采用的是第二种实现方式。在大多数情况下，两者结果相同，但在某些特定情况下，第二种方式可能会删除更多数据。

3. 数据更新
更新语句的基本语法是：UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
我们可以使用WHERE子句来指定要更新的数据行。

对于包含WHERE子句的语句（INSERT ... SELECT, DELETE, UPDATE），如果省略WHERE子句，其默认行为等同于 WHERE TRUE，即该语句会应用于表中的所有行。

单表查询回顾

上节课我们还介绍了单表查询的一些功能。

  • 过滤：使用WHERE条件进行数据筛选。
  • 投影：使用SELECT子句选择特定的列。
  • 排序：使用ORDER BY子句对结果进行排序。
  • 聚合：使用GROUP BY子句和聚合函数（如COUNT, SUM, AVG）对数据进行分组统计。
  • 分组后过滤：使用HAVING子句对分组后的结果进行筛选。

Assignment 1 数据库介绍

接下来，Dlan将向大家展示Assignment 1中将要用到的数据库。这个数据库将作为我们练习视图和多表查询的基础。

（此处应有数据库ER图或表结构介绍，但根据提供的文本，具体内容未详细展开。在实际教程中，这里会描述数据库包含哪些表、表之间的关系以及示例数据。）

SQL视图

上一节我们回顾了数据操作和基本查询，本节我们将学习第一个新概念：SQL视图。

视图是一个虚拟表，其内容由查询定义。与包含数据的物理表不同，视图只保存查询逻辑，每次查询视图时都会重新执行其定义中的查询。

创建视图的语法如下：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

视图的主要优点包括：

  • 简化复杂查询：可以将复杂的查询逻辑封装在视图中，之后只需像查询普通表一样查询视图。
  • 增强安全性：可以只向用户暴露视图，而不是底层基表，从而隐藏敏感数据或复杂结构。
  • 提供逻辑数据独立性：如果底层表结构发生变化（例如列名改变），只需修改视图定义，而无需修改依赖该视图的应用程序。

需要注意的是，某些视图是可更新的（即可以通过视图进行INSERT、UPDATE、DELETE操作），但这通常有严格的限制，例如视图必须基于单个表且未使用聚合或DISTINCT等操作。

多表查询

了解了如何通过视图简化查询后，本节我们来看看如何从多个表中组合数据，即多表查询。这是数据库系统的核心能力之一，通过连接操作实现。

当需要的信息分布在不同的表中时，我们需要使用连接来关联这些表。最常用的连接类型是内连接。

内连接的基本语法如下：

SELECT columns
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

ON 子句指定了连接条件，即两个表如何关联。只有满足连接条件的行才会出现在结果中。

除了 INNER JOIN，还有其他类型的连接，例如：

  • 左外连接：返回左表的所有行，即使右表中没有匹配的行。右表缺失的列以NULL填充。
  • 右外连接：返回右表的所有行，即使左表中没有匹配的行。
  • 全外连接：返回左右两表的所有行，任一表无匹配时，另一表的列填充NULL。

示例： 假设有 Students 表和 Courses 表，要查询每个学生选修的课程，可以使用：

SELECT Students.name, Courses.course_name
FROM Students
INNER JOIN Enrollments ON Students.id = Enrollments.student_id
INNER JOIN Courses ON Enrollments.course_id = Courses.id;

这个查询通过 Enrollments 连接表将 Students 和 Courses 关联起来。

总结

本节课中我们一起学习了SQL中的两个重要特性。
首先，我们介绍了视图，它是一种虚拟表，基于查询结果定义，可用于简化复杂操作、提高安全性和实现逻辑数据独立性。
其次，我们深入探讨了多表查询，特别是通过JOIN操作连接多个表来获取关联数据。我们学习了内连接的基本语法，并简要了解了外连接的概念。

掌握视图和多表查询是构建复杂数据库应用的基础。在接下来的Assignment 1中，你们将有机会实践这些概念。

007：更多SQL查询与SQL函数

在本节课中，我们将学习更多高级SQL查询技术，包括集合运算、分组操作的深入理解，以及使用视图和函数来抽象复杂查询。我们还将介绍SQL函数，这是完成作业1所需的重要工具。

课程公告 📢

在深入学习之前，我们先了解几项重要通知。

以下是本周及未来的课程安排：

  • 测验3：本周需要进行测验3。下周没有测验，以便大家专注于作业。
  • 作业1：作业1的说明将于本周三发布。提交截止日期为第6周周五（3月22日）上午10点。请注意，这不是午夜截止。作业将涉及使用SQL视图和PL/pgSQL函数。
  • 帮助课程：本周将开设帮助课程。如果在设置VX D2账户或使用已发布的作业1数据和模式时遇到问题，可以参加这些课程寻求帮助。

回顾与路线图 🗺️

上一讲我们介绍了SQL中的视图。

你还记得创建视图的语法吗？

创建视图的基本语法是：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

更多SQL查询 🔍

本节中，我们将探讨更复杂的SQL查询操作，包括集合运算和高级分组。

集合运算

SQL支持在查询结果集之间进行并集、交集和差集运算。这些运算符默认会消除重复行。

  • UNION：合并两个查询的结果，并自动去重。
    SELECT column FROM table1
UNION
SELECT column FROM table2;
  • INTERSECT：返回两个查询共有的结果。
    SELECT column FROM table1
INTERSECT
SELECT column FROM table2;
  • EXCEPT：返回存在于第一个查询但不在第二个查询中的结果。
    SELECT column FROM table1
EXCEPT
SELECT column FROM table2;

若要保留所有行（包括重复项），需使用 UNION ALL、INTERSECT ALL 和 EXCEPT ALL。

深入分组

GROUP BY 子句用于将行按一列或多列的值分组，以便对每个组应用聚合函数。

一个常见的进阶用法是 HAVING 子句，它用于过滤分组后的结果集，其作用类似于 WHERE 子句，但针对的是组而非单个行。

以下是一个示例，查找订单数量超过5笔的客户：

SELECT customer_id, COUNT(order_id) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

复杂查询的抽象 🧩

对于复杂的查询逻辑，我们可以使用视图和函数进行封装和抽象，使代码更清晰、更易复用。

上一节我们介绍了视图。本节我们来看看SQL函数。

SQL函数

SQL函数允许你将一段SQL逻辑封装起来，通过调用函数名并传递参数来执行。这在作业1中会用到。

以下是一个简单的SQL函数示例，它接收一个客户ID并返回该客户的订单数量：

CREATE FUNCTION get_order_count(customer_id INT)
RETURNS INT AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM orders WHERE orders.customer_id = $1);
END;
$$ LANGUAGE plpgsql;

你可以这样调用它：SELECT get_order_count(123);

总结 📝

本节课我们一起学习了更多SQL查询技术。我们回顾了使用UNION、INTERSECT、EXCEPT进行集合运算，深入探讨了GROUP BY与HAVING子句在数据分组与过滤中的应用。最后，我们介绍了如何使用SQL函数来封装和抽象复杂的查询逻辑，这是完成即将发布的作业1的关键技能。请务必利用好帮助课程，并按时完成测验和作业。

008：PL/pgSQL 入门教程

在本节课中，我们将学习关于PL/pgSQL的重要知识，这是完成课程作业和深入理解数据库编程的关键。课程开始前，我们先了解一些重要的通知。

课程公告

以下是关于课程和作业的重要信息。

关于作业一

作业一的说明文件已于本周三发布，但其中不包含PL/pgSQL相关的问题。完整的说明文件将于明天发布。

与作业说明一同发布的，还有一些示例输出。部分同学可能已经注意到示例输出中存在一些小错误，并且已在论坛上提出了相关问题。我们正在修复这些示例和问题，它们很快会被更新。

在当前的作业说明中，第一题是一个格式规范题，旨在考察提交代码的格式。这部分分数几乎是白送的。因此，你需要对你提交的SQL语句进行格式化。

建议你使用一些工具来帮助你完成格式化。例如，如果你使用VS Code等IDE，可以安装相关插件。此外，也有一些在线服务可以完成这项工作。

其中一个在线工具叫做 SQL Format。你可以将未格式化的SQL语句复制粘贴到其输入框中，它会自动为你格式化。

例如，这是一个未格式化的SQL语句：

SELECT * FROM students WHERE age > 20 ORDER BY name;

你可以使用上述网站来格式化你的作业答案。在提交前进行格式化，你就能轻松获得格式规范的分数。这几乎是一个送分框架，请务必完成。

关于帮助课程

从本周开始，我们将举办帮助课程。课程时间表主要安排在周二、周三和周四。具体时间安排由学院制定，如果你对时间有疑问，我会尝试与学院管理人员沟通，看是否能进行调整。

关于测验截止日期

最后一个提醒是，测验的截止日期是明天午夜。有同学询问第3题和第4题，因为它们涉及PL/pgSQL函数。然而，这两道题并不需要太多特定的PL/pgSQL知识。

例如，第3题主要是一个过程性函数，你应该运用之前学到的知识来解决它。

关于解答测验题目，由于我们现在在学习编程，测验题会涉及一些代码片段。你可以尝试在VLab数据库服务器或在线编译器上执行题目中提供的函数或语句，然后观察结果并选择正确答案。这几乎是一个解题框架，请直接实践。

知识回顾：集合与包

在上一讲中，我们学习了包（Bags）和集合（Sets）及其操作符。现在，让我们快速回顾一下核心概念。

首先，集合和包在概念上的主要区别是什么？

集合不允许重复元素，而包允许重复元素。大多数情况下，一个SELECT查询的结果是一个包，因为结果中可能包含重复的数据行。

那么，如何将一个包的结果转换为集合呢？这里的关键字是DISTINCT。我们可以使用SELECT DISTINCT，这样得到的结果就是一个集合。

接下来，我们学习了三种不同类型的集合操作符。它们分别是：

  • UNION（并集）
  • INTERSECT（交集）
  • EXCEPT（差集）

这三个操作符之间有什么区别？

UNION和INTERSECT与数学中的集合运算等价。而EXCEPT操作的结果顺序很重要。例如，对于 A EXCEPT B，结果是属于A但不属于B的元素。如果我们调换顺序，B EXCEPT A 的结果则是属于B但不属于A的元素。因此，对于EXCEPT，操作数的顺序会影响结果。

此外，所有这些集合操作符默认产生的结果都是集合（即会消除重复项）。如果我们想保留重复项，得到包的结果，该怎么办？这里的关键字是ALL。

例如，如果我们想保留并集操作中的重复项，可以使用 UNION ALL。

总结

本节课中，我们一起学习了关于作业提交、帮助课程的重要公告，并回顾了集合与包的核心概念及其操作符。理解这些基础概念，特别是DISTINCT和ALL关键字在转换结果类型时的作用，以及EXCEPT操作符的顺序敏感性，对于编写正确的SQL查询至关重要。在接下来的课程中，我们将深入探讨PL/pgSQL的具体内容。

009：课程前半部分回顾 🗂️

在本节课中，我们将进行课程前半部分的知识点回顾。首先，我们会发布几项课程相关的通知，然后系统地梳理从开课至今所学的核心内容。

课程通知 📢

以下是本周需要关注的几项课程通知。

关于作业1

作业1的大部分具体题目已经发布，但第10题仍在准备中。这是因为助教Dlan正在为所有题目创建相应的测试脚本，以确保每个问题在各种边界情况下都能正常运行。作业1的提交截止日期是第6周周五之前。请务必按时提交，以获得相应的分数。

关于测验3第1题

一些同学询问了测验3第1题中选项E的正确性。选项E是不正确的。关键在于，题目中表X的name字段虽然被定义为UNIQUE，但并未指定为NOT NULL。这意味着name字段允许存储NULL值。

我们可以通过运行SQL语句来验证。以下是题目涉及的两个表和数据示例：

-- 表X：name字段UNIQUE但允许NULL
CREATE TABLE X (
    id INT PRIMARY KEY,
    name VARCHAR(255) UNIQUE -- 注意：没有NOT NULL约束
);

-- 表Y：引用X.id
CREATE TABLE Y (
    id INT PRIMARY KEY,
    x_id INT REFERENCES X(id)
);

-- 插入数据：向X插入两条name为NULL的记录
INSERT INTO X (id, name) VALUES (1, NULL), (2, NULL);
INSERT INTO Y (id, x_id) VALUES (1, 1), (2, 2);

选项E的查询可能使用了COUNT(DISTINCT x.name)。在SQL中，多个NULL值在DISTINCT比较中被视为相等，因此只会被计数一次。然而，根据数据，我们实际插入了两条name为NULL的X记录。因此，选项E无法正确处理NULL值，不是正确答案。该题唯一正确的选项是B。

另外，请注意，部分同学可能参考了以往学期的测验答案。你们不应该也无法访问以往学期的WebCMS内容，请以本学期发布的官方材料和答案为准。

关于辅导课时间调整

有同学指出，原定于周四的辅导课与本次讲座时间冲突。我们已经将此情况通知教学支持团队，正在协调重新安排该辅导课的时间，请关注后续通知。

关于本周测验

本周没有安排测验。大家可以利用这段时间，集中精力完成作业1。

课程学习情况统计与建议 📈

在开始回顾知识点之前，我们先了解一下本课程目前的学习情况。

  • 测验平均分：与往届学期相比，本学期测验1和测验2的平均分高出约3-4%，测验3的平均分更是高出约10%。这是一个非常积极的信号。
  • 分数趋势：一个值得注意的现象是，平均分随着测验的进行呈下降趋势。尽管课程内容确实在逐渐深入，但就像刚才演示的测验3第1题一样，许多问题可以通过亲自运行SQL代码来验证和理解。希望大家能共同努力，扭转这个下降的趋势。
  • 参与度：每学期总有约10%的学生从未参与任何测验。请注意，测验是课程评估的重要组成部分。
  • 评分说明：本课程的评分不采用“钟形曲线”法。你的最终成绩不会因为其他同学的表现而受到影响。因此，鼓励大家互相帮助，共同进步。
  • 论坛参与：我们很高兴看到许多同学在课程论坛上积极为他人解答问题，例如助教Dlan和一些热心的同学。这种互助精神非常值得赞扬。当然，论坛也欢迎任何与课程相关的问题，无论是关于为何学习某个特定知识点、技术细节，还是关于课程安排的建议。

上一部分我们了解了课程近况，接下来我们将正式进入课程核心内容的回顾环节。

课程前半部分核心内容回顾 🔍

自课程开始以来，我们主要围绕数据库系统的设计和查询语言展开学习。以下是主要知识点的梳理。

1. 数据库设计与ER模型

我们首先学习了如何使用实体-关系（ER）模型进行概念数据库设计。

  • 核心概念：
    • 实体：表示现实世界中可区分的对象。
    • 属性：描述实体的特性。
    • 关系：表示实体之间的关联。
  • 设计要点：重点是识别正确的实体、属性和关系，并确定关系的基数约束（如一对一、一对多、多对多）。

2. 关系模型与SQL

在ER设计之后，我们学习了如何将ER图转换为关系模型，并使用SQL进行实际操作。

  • 从ER到关系模式：掌握了将实体、属性和关系映射为数据库表和列的规则。
  • SQL数据定义语言（DDL）：用于创建和修改数据库结构。
    CREATE TABLE Student (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT
);
  • SQL数据操作语言（DML）：用于查询和操作数据。
    • 基本查询：SELECT ... FROM ... WHERE ...
    • 连接操作：INNER JOIN, LEFT JOIN 等，用于合并多个表的数据。
    • 聚合与分组：使用 COUNT, SUM, AVG 等函数与 GROUP BY 子句进行数据汇总。
    • 子查询：在查询中嵌套另一个查询。

3. 约束与数据完整性

为了保证数据库中数据的准确性和一致性，我们学习了多种约束。

  • 主键约束：PRIMARY KEY，唯一标识每条记录。
  • 外键约束：FOREIGN KEY，维护表之间的引用完整性。
  • 唯一约束：UNIQUE，确保某列的值唯一。
  • 非空约束：NOT NULL，确保某列必须有值。
  • 检查约束：CHECK，确保列值满足特定条件。

4. 空值（NULL）处理

正如在测验3中强调的，NULL值的处理是SQL中的一个重要且易错点。

  • NULL的含义：表示值未知、不存在或不适用。
  • 与NULL的比较：任何与NULL的比较操作（如 = NULL, < NULL）结果都是UNKNOWN。正确检查NULL应使用 IS NULL 或 IS NOT NULL。
  • 在聚合函数中的行为：COUNT(*) 计算所有行数，而 COUNT(column) 会忽略该列为NULL的行。DISTINCT 会将所有NULL值视为相同。

5. 关系代数

关系代数提供了描述数据库查询的数学基础。

  • 基本运算：选择（σ）、投影（π）、并集（∪）、差集（-）、笛卡尔积（×）。
  • 派生运算：连接（⨝）、自然连接、除运算等。
  • 作用：帮助我们从逻辑上理解和构建复杂的SQL查询。

总结 📝

本节课中，我们一起回顾了课程前半部分的核心内容。我们首先处理了关于作业、测验答案澄清和课程安排的通知。接着，通过分析学习数据，我们看到了大家的进步空间。最后，我们系统梳理了从数据库设计（ER模型） 到关系模型实现，再到使用SQL进行数据定义、操作和约束管理的知识链条，并特别强调了NULL值处理这一关键细节。

希望这次回顾能帮助你巩固所学知识，为后续更深入的学习打下坚实基础。请继续专注于当前的作业，并善用论坛进行交流。

010：用户自定义聚合函数与触发器 🗂️

在本节课中，我们将要学习 PostgreSQL 数据库的两个高级特性：用户自定义聚合函数和触发器。我们将了解如何创建自己的聚合函数，以及如何使用触发器在特定数据库事件发生时自动执行操作。

概述

上一讲我们介绍了数据建模和基本的 SQL 查询。本节中，我们来看看 PostgreSQL 提供的更多功能，特别是如何扩展其聚合能力以及实现自动化的数据操作。

课程公告与路线图 📢

首先，有一些课程相关的公告需要说明。

关于作业1

作业1第10题的详细说明尚未发布，但会在今天或明天公布。提交截止时间为下周晚上10点之前。截止时间后仍可提交，但会面临迟交扣分。

论坛上有同学反映自动测试脚本存在问题。需要明确的是，自动测试脚本的目的并非在提交前给出分数，而是确保你的 SQL 查询能够基本正常运行。它并不覆盖所有测试用例，因此请不要完全依赖它。

另外再次强调，通过规范格式化你的提交内容，你可以获得格式分。

对于需要特殊考试安排的学生，必须在作业截止时间前至少24小时提交延期申请，晚于此时间的申请将不被受理。

关于辅导课

我们正在组织一些辅导课，但其中一次与今天的讲座时间冲突。教学支持团队正在处理这个问题。

关于测验与灵活周

本周没有测验。下周是灵活周，没有讲座、没有辅导课、也没有测验。建议你利用这段时间完成作业，并重新学习 Python，因为在本学期后半段以及你的作业中都会用到它。

课程路线图

以下是本课程的后续安排：

  • 已学习内容：数据建模、基础 SQL 函数与查询。
  • 本周内容：更多 SQL 高级功能（即本节课内容）。
  • 第7周：学习如何使用编程语言（特别是 Python）与数据库管理系统交互。将发布作业2，内容涉及编写处理 SQL 查询的 Python 脚本。
  • 第8周：在掌握了 SQL 基础和编程交互后，我们将接触数据库系统的一些理论部分，学习关系设计理论。
  • 第9周：学习关系代数，这也是一个理论性主题。作业2的预计提交截止时间是第9周末，但可能会延期至第10周，以给大家更多时间。
  • 第10周：最后一周，学习数据库管理系统的事务管理，并进行课程总复习。届时将有一场来自行业专家的客座讲座。请注意，我可能会在周末前往悉尼以外参加会议，如果成行，我会为大家提供讲座录像。

用户自定义聚合函数 🧮

现在，让我们进入今天的核心内容。第一个主题是聚合函数。实际上我们已经学习过聚合操作。

回顾：标准聚合函数

聚合运算符用于将一组值汇总为单个值。

以下是我们已经学过的聚合运算符示例：

  • COUNT：用于计算元组的数量。
  • SUM：对数值进行操作，返回一组值的总和。
  • MAX：从一组值中获取最大值。

聚合函数的操作语义可以这样理解：我们有一个初始状态，然后遍历整个值列表；对于遇到的每一个新值，我们都用它来更新当前状态；遍历结束后，将最终状态返回给用户。这更像是一个实现聚合函数的伪代码逻辑。

聚合函数通常与 GROUP BY 子句一起使用，用于为每个分组提供汇总信息。

引入用户自定义聚合

今天要学习的新知识是用户自定义聚合函数。除了标准的 SQL 聚合函数外，PostgreSQL 提供了一种机制，允许用户定义自己的聚合函数。

要定义一个新的聚合函数，用户需要提供以下几部分。在考虑需要提供什么之前，让我们回想一下上面提到的伪代码逻辑。所有创建自定义聚合所需的材料都源于这个算法。

以下是定义聚合函数时需要指定的组件：

  1. 基础类型：即伪代码中每个值 T 的数据类型，也就是聚合函数要处理的输入值的类型。
  2. 状态类型：即伪代码中 state 变量的数据类型。它可能与最终结果类型相同，也可能不同。
  3. 初始状态值：聚合开始时 state 的初始值。
  4. 状态转换函数：一个函数，它接收当前 state 和一个输入值 T，并返回更新后的新 state。这对应了伪代码中的 update(state, T) 步骤。
  5. 最终计算函数：一个可选的函数。在遍历完所有输入值后，调用此函数对最终的 state 进行处理，然后返回给用户。如果省略，则最终状态就是聚合结果。

定义聚合函数的语法

在 PostgreSQL 中，使用 CREATE AGGREGATE 命令来定义新的聚合函数。

其基本语法结构如下：

CREATE AGGREGATE aggregate_name (input_data_type)
(
    INITCOND = initial_state_value,
    STYPE = state_data_type,
    SFUNC = state_transition_function_name,
    FINALFUNC = final_function_name -- 可选
);

应用示例：自定义字符串连接聚合

假设我们想创建一个聚合函数，将一组字符串值用指定的分隔符连接起来，类似于 string_agg 函数，但我们来自定义。

我们需要：

  • 基础类型：TEXT（输入是文本）。
  • 状态类型：可以是一个包含“当前连接结果”和“分隔符”的复合类型，或者简单地用一个 TEXT 变量，并在首次更新时加入分隔符逻辑。为简单起见，我们使用 TEXT 作为状态类型。
  • 初始状态：空字符串 ''。
  • 状态转换函数：一个函数，将新字符串附加到当前状态字符串后面，并在非首次附加时添加分隔符。
  • 最终计算函数：可能不需要，除非我们需要对最终结果做额外处理（如修剪末尾多余的分隔符）。

由于涉及具体的函数定义，完整的代码示例可能较长，但其核心思想是遵循上述五个步骤来构建聚合逻辑。

触发器 ⚡

上一节我们介绍了如何自定义聚合函数来扩展 SQL 的汇总能力。本节中，我们来看看如何通过触发器实现数据库操作的自动化。

什么是触发器？

触发器是一种特殊的存储过程，它会在指定的数据库事件（如 INSERT、UPDATE、DELETE）发生在特定表上时自动执行。触发器常用于强制实施复杂的业务规则、维护数据完整性、审计数据变更或同步相关表的数据。

触发器的关键组件

一个触发器定义主要包括以下几个部分：

  1. 触发时机：BEFORE、AFTER 或 INSTEAD OF 事件。
  2. 触发事件：INSERT、UPDATE 或 DELETE。
  3. 关联表：触发器附加在哪张表上。
  4. 触发条件：可选的 WHEN 子句，用于指定触发器仅在满足某些条件时才执行。
  5. 执行函数：触发器被激活时运行的函数。这个函数必须被定义为返回 TRIGGER 类型。

创建触发器的步骤

在 PostgreSQL 中创建触发器通常分为两步：

第一步：创建触发器函数
这是一个普通的函数，但它必须声明返回 TRIGGER 类型。在函数内部，你可以通过特殊变量（如 NEW、OLD）来访问被修改行的数据。

CREATE OR REPLACE FUNCTION my_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    -- 在这里编写触发器逻辑
    -- 例如，可以使用 NEW.column_name 访问新插入或更新的值
    -- 使用 OLD.column_name 访问更新前或删除前的值
    RETURN NEW; -- 对于BEFORE触发器，通常需要返回NEW或OLD
END;
$$ LANGUAGE plpgsql;

第二步：创建触发器
将触发器函数绑定到指定的表和事件上。

CREATE TRIGGER my_trigger
    AFTER INSERT ON my_table
    FOR EACH ROW
    EXECUTE FUNCTION my_trigger_function();

触发器应用场景示例

  1. 审计日志：在 AFTER UPDATE 或 AFTER DELETE 触发器中将旧数据记录到另一张审计表中。
  2. 数据验证：在 BEFORE INSERT 或 BEFORE UPDATE 触发器中检查数据的有效性，如果无效，可以抛出异常阻止操作。
  3. 自动计算字段：在 BEFORE INSERT 或 BEFORE UPDATE 触发器中，基于其他字段的值自动计算并设置某个字段（如总价 = 单价 * 数量）。
  4. 维护数据一致性：当主表数据删除时，使用触发器自动删除从表中的相关记录（级联删除的替代方案）。

总结 🎯

本节课中我们一起学习了 PostgreSQL 的两个强大特性。

首先，我们深入了解了用户自定义聚合函数。我们回顾了标准聚合函数的工作原理，然后学习了如何通过指定基础类型、状态类型、初始状态、状态转换函数和最终计算函数来构建自己的聚合函数，从而满足特定的数据汇总需求。

接着，我们探讨了触发器。我们了解了触发器是什么，它如何在特定数据操作事件发生时自动执行代码。我们学习了创建触发器的两个关键步骤：编写返回 TRIGGER 类型的函数，以及将函数绑定到表的特定事件上。触发器是实现复杂业务逻辑、数据完整性和自动化任务的必备工具。

掌握这些高级功能将极大地增强你利用 PostgreSQL 管理和处理数据的能力。

011：使用Python连接PostgreSQL

在本节课中，我们将学习如何使用Python编程语言与PostgreSQL数据库进行交互。我们将从课程公告和路线图开始，然后快速回顾Python基础知识，最后重点介绍用于连接数据库的psycopg2模块。

课程公告

以下是本周的重要通知。

关于作业2，其详细说明将于本周晚些时候发布，截止日期待定。我们将确保大家有超过两周的时间来完成。助教和我仍在讨论使用哪个数据库，但这将是一个有趣的练习。本次作业旨在练习使用Python与PostgreSQL交互，因此需要使用Python完成。

请尽早开始作业并在论坛上尽早提问。因为在作业1截止日期的前一两天，我们收到了大量问题，无法很好地回答每一个。如果尽早提问，你的问题得到更好解答的机会更大。论坛上有助教和乐于助人的同学可以帮助你。

设置指南比问题发布得更早，请务必尽早尝试设置环境。因为在截止日期前几小时，我们收到了一些关于在VSCode上设置或连接数据库的问题，这非常糟糕。你无法在最后几小时内完成所有事情，因此请尽早设置。

关于作业的最后一点是，你应该使用私密帖子来发布代码。有些学生在论坛上询问代码中的错误或问题，但没有将其标记为私密，导致其他所有学生都能看到他们的代码，这很不好。如果你真的想与我们讨论你的代码，请尝试使用私密帖子。

关于测验，本周有测验4，截止日期是本周五，请按时完成。

关于帮助课程，周四的帮助课程与讲座时间重叠，将被调整到周四上午11点至下午1点。但正式调整的时间还需要等待确认。

课程路线图

现在，让我们看看接下来的课程路线图。

此前，我们已经学习了所有关于SQL的知识，包括如何编写查询、函数和触发器等。今天，我们将学习如何使用编程语言与数据库交互。

接下来，我们将学习更多关于数据库的理论知识。最后，我们还将学习数据库系统的事务和并发控制，这在多用户使用同一数据库时非常有用。

课程结束时，我们将讨论数据库的未来，进行课程回顾，并预览期末考试。我们可能还会邀请来自业界的客座讲师，分享他们在实践中如何使用数据库。

在第10周，我将外出参加会议，但会为大家提前准备好讲座视频。

Python快速回顾

上一节我们介绍了课程安排，本节中我们来看看今天的主要内容。

如前所述，本课程将使用Python。希望这对你来说是一个回顾，希望你在此之前已经熟悉Python。Python是一种非常流行的编程语言，它易于学习和使用，并拥有广泛的实用库。如今，由于人工智能的流行，它尤其有用，因为有许多AI库都是用Python编写的。

在本课程中，我们假设你具备足够的Python基础知识。希望如此，因为本课程的主要主题是数据库，而不是Python编程。

如果你之前对Python不太熟悉，也无需担心。本课程中将有许多Python编码示例，并且网上有许多优秀的在线教程，例如learnpython.org，你可以快速学习。

为什么选择Python？

我们为什么要选择Python作为与数据库交互的编程语言呢？

第一个原因是Python是一种非常流行的编程语言。根据一份最新的排名，Python是最受欢迎的编程语言，其次是C/C++。这个排名是基于社区中程序员的评价，而不仅仅是统计GitHub上的代码量。C/C++排名稍高是因为它们对于构建操作系统等基础设施非常重要，例如PostgreSQL的实际代码就是用C编写的。但Python仍然是最重要的语言之一。你能在这个排名中找到SQL吗？是的，它在今年三月排名第七。我们在这里学习的是非常流行的编程语言，它们都非常有用。

第二个选择Python的原因是它相对容易学习。相信我，你不会想在这门课程中使用像Rust这样的语言。因此，Python可以说是这里最简单的选择。

对我来说，Python是一种优秀的脚本语言，也是构建大型系统的好语言。例如，你可以编写一些Shell脚本来进行实用程序操作，比如批量重命名文件或操作操作系统中的一些文件。但你不能用Shell脚本来编写大型服务器。你可以用C、C++或Java来编写Web服务器，但通常不会用它们来编写基本的实用程序。而Python则像是一个多面手，你可以用Python编写脚本，也可以用Python构建复杂的系统。有许多著名的框架就是用Python构建的，例如Django和Flask。

本节课中我们一起学习了课程公告、未来路线图，并回顾了Python的基础知识以及选择它作为数据库交互语言的原因。接下来，我们将深入探讨如何使用psycopg2模块具体连接和操作PostgreSQL数据库。

012：冗余与函数依赖

在本节课中，我们将学习关系数据库设计理论的核心概念，特别是冗余和函数依赖。我们将从课程公告开始，回顾上一讲的内容，然后深入探讨如何识别和消除数据冗余，并理解函数依赖在数据库设计中的关键作用。

课程公告

以下是本周的重要通知。

作业2发布

作业2的详细说明将于本周晚些时候发布，截止日期为第10周的星期三。本次作业的目的是练习使用Python模块psycopg2，但核心仍然是编写SQL查询。因此，这本质上是一次SQL查询的练习。由于涉及Python模块，你将使用Python来完成此作业。

建议尽早开始作业。如果遇到任何问题，可以及早提问，避免在截止日期前匆忙解决。

提醒：如果你需要在论坛上发布与作业代码相关的内容，请使用私密帖子。

测验安排

关于“事务”的测验将于本周五进行。请做好准备。

辅导课时间调整

周四的辅导课计划调整至周四上午11点至下午1点，但此安排尚未得到管理员的正式确认。不过，我们的一位助教愿意在此期间提供额外的辅导帮助。

关于作业2的数据库

本次作业将使用一个关于宝可梦的数据库。这个数据库在往年曾被学生反馈难度较高。

下图展示了该数据库的实体关系图：

如图所示，该数据库包含9种不同的实体类型，关系比之前的电影数据库更为复杂。但请放心，由于宝可梦的概念本身比较直观，理解这些实体和关系并不困难。其中，属性最多的实体是“宝可梦”本身，数据库中也包含一些复合实体和属性。

数据库的模式定义、数据转储文件以及所有图表均已发布在WebCMS上，你可以通过提供的链接随时访问。

建议：由于作业说明尚未发布，建议你尽早设置好数据库环境。在以往的作业1中，我们在截止日期前一天收到了许多环境设置问题，这非常不利于完成作业。因此，请提前按照发布的设置指南完成数据库搭建。

此外，本次作业的问题数量将少于作业1，大约只有4到5个问题，所以无需过度担忧。

关于作业难度的说明

往年学生反馈作业难，并非因为实体关系图的复杂性，而是源于问题设计的方式。

使用psycopg2编写的程序是一个多语言程序，它同时涉及Python代码和SQL代码。数据在这两种语言之间传递时，可能发生数据类型不匹配的问题，需要特别小心。

这一点在处理浮点数时尤为关键。因为计算可以在Python端进行，也可以在SQL端进行，两者可能产生不同精度的结果。去年，有些学生在Python端计算，有些则在SQL端计算，由于精度问题导致了不同的结果。虽然他们本质上都做了正确的事情，但结果却不同，这给评分带来了困难。

今年，我们将避免在作业中使用浮点数，以期解决这个问题。

核心建议：对于本次作业，你应尽可能使用SQL进行数据处理，而不是Python。有些学生倾向于用游标获取所有表的数据，然后在Python中进行全部处理，这种方法会非常慢。因为PostgreSQL是高度优化的，而Python作为一种解释型语言，执行速度相对较慢。

内容回顾

上一讲中，我们介绍了关系模型的基本概念，包括关系、元组、属性和键。

关系设计理论：冗余与函数依赖

现在，让我们进入今天的核心主题：关系设计理论。一个糟糕的数据库设计会导致各种问题，其中最常见的问题之一就是数据冗余。

什么是数据冗余？

数据冗余指的是相同的信息在数据库的多个地方重复存储。

例如，考虑一个存储学生和课程信息的简单表 Enrolment：

StudentID StudentName CourseCode CourseName
123 Alice COMP3311 Database Systems
123 Alice COMP1521 Computer Systems Fundamentals
456 Bob COMP3311 Database Systems

在这个表中，“Alice”的名字和“COMP3311”的课程名都被存储了多次。这就是冗余。

冗余会导致什么问题？

冗余主要会引发三个问题：

  1. 更新异常：如果“Database Systems”这门课改名了，我们需要更新所有相关的行（第1行和第3行）。如果漏掉一行，数据就会不一致。
  2. 插入异常：如果我们想新增一门课程“COMP9999”，但还没有任何学生选修，我们就无法插入这条课程记录，因为StudentID是主键的一部分，不能为空。
  3. 删除异常：如果学生Bob（ID 456）退选了COMP3311，当我们删除第3行时，关于“Database Systems”这门课程的信息也会随之丢失。

如何解决冗余？——引入函数依赖

为了解决冗余，我们需要理解数据之间的内在联系，这可以通过函数依赖来描述。

一个函数依赖表示一个属性集的值唯一决定另一个属性集的值。

其公式表示为：
X → Y
读作“X函数决定Y”或“Y函数依赖于X”。意思是，对于任意两个元组，如果它们在属性集X上的值相等，那么它们在属性集Y上的值也必须相等。

在上面的例子中，我们可以观察到：

  • CourseCode → CourseName：课程代码唯一决定了课程名称。
  • StudentID → StudentName：学号唯一决定了学生姓名。

然而，StudentID并不能决定CourseCode，因为一个学生可以选修多门课。

识别函数依赖

函数依赖不是由DBMS强制规定的，而是基于现实世界的语义。作为数据库设计者，你需要根据业务规则来识别它们。

例如：

  • 在一个公司里，员工ID → 部门。
  • 在一个产品库里，产品编号 → 价格。
  • 在一个订单系统里，(订单号，产品编号) → 数量。这里，数量依赖于订单和产品的组合。

函数依赖与键的关系

超键：是一个或多个属性的集合，可以唯一标识一个关系中的元组。
候选键：是最小超键（即没有多余属性）。
主键：是被选中的候选键。

从函数依赖的角度看：

  • 如果 K → R（即K函数决定所有其他属性），那么K是一个超键。
  • 如果 K → R，并且K的任何真子集都不能函数决定R，那么K是一个候选键。

因此，寻找键的过程就是寻找能够函数决定所有属性的最小属性集。

总结

本节课我们一起学习了数据库设计中的关键问题——数据冗余及其引发的更新、插入和删除异常。为了解决这些问题，我们引入了函数依赖的概念，它描述了属性之间的决定关系。理解函数依赖是进行数据库规范化（我们将在后续课程中学习）的基础，它能帮助我们设计出更高效、更少冗余的数据库结构。

下一讲中，我们将基于函数依赖，学习如何通过规范化过程来分解表，从而消除冗余和异常。

013：闭包、范式与规范化 🗃️

在本节课中，我们将学习如何系统性地使用函数依赖来优化数据库设计。我们将重点介绍三个核心概念：属性集的闭包、范式以及规范化过程。这些知识将帮助我们识别并消除数据库中的冗余，从而避免数据异常。

课程概述 📋

上一讲我们介绍了函数依赖及其重要性。本节我们将深入探讨如何利用函数依赖进行系统化的数据库设计优化。主要内容包括：计算属性集的闭包以确定超键和候选键；理解不同的范式（如BCNF和3NF）及其目标；以及通过分解关系模式来实现规范化的具体步骤。

公告事项 📢

以下是几项重要的课程公告。

关于测验与作业

  1. 测验5的截止日期是本周五，请按时完成。
  2. 作业1的初步成绩已发布。如果对成绩有疑问（例如，因语法错误导致自动测试得零分），请及时联系我们。助教会进行人工复核，你仍有机会获得部分分数，但请确保你的查询能通过基本的语法检查。
  3. 作业2的说明正在准备中，预计截止日期为下周五。我们会通过WebCMS发布正式通知。作业2的题量较少，不会占用过多时间，请不必担心影响期末复习。

关于课程安排

  1. 原定与本次课程冲突的辅导课已正式调整至下周上午。请注意，下周是最后一次线下课（因第10周讲师需参加会议）。该辅导课将由经验丰富的助教Kens主持，欢迎大家就作业、教程等问题前来咨询。

知识回顾 🔄

在进入新内容之前，我们先快速回顾上一讲的关键概念。

1. 数据库冗余与异常

如果数据库设计不当，可能会出现数据冗余。冗余会导致三种类型的操作异常：

  • 更新异常：更新部分数据时，可能导致数据不一致。
  • 插入异常：无法插入某些必要信息，除非同时插入其他无关信息。
  • 删除异常：删除某些信息时，可能会意外丢失其他重要数据。

我们可以通过编写大量触发器来处理这些异常，但这并非理想方案，因为过多的触发器会拖慢系统性能。更好的方法是主动从数据库设计中消除冗余。

2. 函数依赖

函数依赖是描述属性间决定关系的形式化工具。其定义是：给定关系模式R，若对于R中的任意两个元组，只要它们在属性集X上的值相同，则在属性集Y上的值也必然相同，则称X函数决定Y，或称Y函数依赖于X，记作 X → Y。

上一讲我们还学习了从已知函数依赖推导新函数依赖的6条推理规则。

核心概念一：属性集的闭包 🧮

上一节我们回顾了函数依赖的基础。本节中，我们来看看如何利用函数依赖计算属性集的闭包，这是一个非常实用的工具。

什么是闭包？

给定一个属性集X和一组函数依赖F，X关于F的闭包（记作 X⁺）是指：能够通过F中的函数依赖，从X直接或间接推导出的所有属性的集合。

简单来说，闭包 X⁺ 回答了这个问题：“如果我知道了X的值，那么我还能唯一确定哪些其他属性的值？”

闭包的算法

以下是计算属性集X闭包 X⁺ 的标准算法：

输入：属性集 X， 函数依赖集 F
输出：闭包 X⁺

步骤：
1.  令 result = X。
2.  重复以下过程，直到 result 不再变化：
    a. 对于 F 中的每一个函数依赖 Y → Z：
        b. 如果 Y ⊆ result，则将 Z 加入到 result 中。
3.  返回 result。

闭包的作用

计算闭包主要有两个用途：

  1. 判断函数依赖是否成立：要检查 X → Y 是否在 F⁺（F的闭包，即所有能推导出的函数依赖）中，只需计算 X⁺，然后看 Y 是否是 X⁺ 的子集。如果是，则 X → Y 成立。
  2. 寻找关系的键：如果某个属性集X的闭包 X⁺ 包含了关系的所有属性，那么X就是一个超键。进一步地，如果X的任何真子集的闭包都不能包含所有属性，那么X就是一个候选键。

核心概念二：范式 🏗️

理解了如何计算闭包和寻找键之后，我们就可以用它来评估和改善数据库设计了。数据库设计的好坏通常用范式来衡量。

范式是关系模式需要满足的规范等级。高级别的范式比低级别范式具有更少的冗余和异常。我们主要关注以下两种范式：

1. 鲍依斯-科德范式

BCNF的定义是：对于关系模式R中的每一个非平凡函数依赖 X → Y，其决定因素X必须是一个超键。

用公式表示，即对于 F⁺ 中所有形如 X → Y 的依赖（其中 Y ⊈ X），都必须满足 X⁺ 包含R的所有属性。

如果一个关系模式属于BCNF，那么它将完全消除由函数依赖引起的所有冗余和异常。

2. 第三范式

3NF的要求比BCNF稍宽松一些。它的定义是：对于关系模式R中的每一个非平凡函数依赖 X → Y，至少满足以下条件之一：

  1. X是超键，或者
  2. Y中的每个属性都包含于R的某个候选键中（即Y是主属性）。

3NF允许某些特定的冗余存在，但能保证不会产生更新和删除异常。

核心概念三：规范化 🛠️

当我们发现一个关系模式不满足BCNF或3NF时，就需要通过规范化过程来分解它，以提升其范式等级。

规范化的目标

规范化的核心思想是：将一个大关系模式分解成若干个更小的关系模式，使得每个小模式都满足更高的范式要求，同时分解必须是无损连接的（即通过自然连接能恢复原始数据），并尽可能保持函数依赖。

分解到BCNF的算法

以下是分解到BCNF的一个通用算法：

输入：关系模式 R， 函数依赖集 F
输出：满足BCNF的一组关系模式

步骤：
1.  初始化 result = {R}。
2.  当 result 中存在不满足BCNF的关系模式 S 时，执行：
    a. 在 S 中找到一个违反BCNF的非平凡函数依赖 X → Y（即 X 不是 S 的超键）。
    b. 将 S 分解为两个模式：
        - S1 = X⁺ （X的闭包）
        - S2 = (S - Y) ∪ X
    c. 在 result 中用 S1 和 S2 替换 S。
3.  返回 result。

关于3NF分解

存在一种算法可以保证将关系模式无损连接且保持函数依赖地分解到3NF。当BCNF分解无法保持所有函数依赖时，3NF是一个很好的折中选择。

总结 📝

本节课我们一起学习了数据库设计理论中的三个核心工具：

  1. 闭包：通过算法计算属性集的闭包，用于验证函数依赖和寻找关系的键。
  2. 范式：我们介绍了BCNF和3NF的定义与目标，它们是衡量设计好坏、减少冗余与异常的标准。
  3. 规范化：我们了解了通过分解关系模式来达到BCNF或3NF的算法过程，这是优化数据库设计的系统性方法。

掌握这些概念，你将能够分析并改进现有的数据库模式，使其更加健壮和高效。在接下来的课程和作业中，你将有机会应用这些知识解决实际问题。

014：关系代数与查询执行

在本节课中，我们将学习数据库系统的两个核心底层概念：关系代数和查询执行。关系代数是操作关系（即数据表）的数学基础，而查询执行则关注数据库系统如何实际处理和运行查询。理解这些内容对于深入掌握数据库的工作原理至关重要。

课程公告与回顾

首先，我们来看一下本周的重要通知。

  • 本周没有安排测验。
  • 最后一次测验（测验6）的截止日期是下周五。
  • 到目前为止，大家的测验平均成绩略高于往届。

关于作业2：

  • 作业2的前两道题目的说明已经发布。
  • 最后三道题目的说明正在最终确定中。
  • 作业的截止日期是下周五。
  • 如有特殊情况，请尽早提交特殊考虑申请。
  • 如需延期，请在截止日期前至少24小时提交ELP延期请求。

接下来，让我们快速回顾一下上一讲的内容。上一讲我们学习了函数依赖的实际应用，并掌握了几个重要概念。

以下是核心概念的回顾：

  1. 闭包：给定一组函数依赖，其闭包是指基于这组依赖可以推导出的所有函数依赖的集合。然而，函数依赖的闭包通常非常庞大，实际应用中我们更常使用属性闭包。
  2. 键的判定：属性闭包的一个主要用途是确定关系（表）的键。如果一个属性集的闭包包含了关系的所有属性，那么这个属性集就是一个超键。
  3. 最小覆盖：它是一组函数依赖的“最小完整集”，即没有冗余依赖的最简形式。
  4. 范式：我们学习了最常用的两种范式——第三范式（3NF）和BCNF。BCNF的限制更严格，能产生更少的冗余。其限制条件分别是：
    • BCNF：对于关系模式R中的每一个非平凡函数依赖 X → Y，X 必须是R的一个超键。
    • 3NF：对于关系模式R中的每一个非平凡函数依赖 X → A，要么 X 是R的一个超键，要么 A 是R的一个主属性（即包含在某个候选键中）。
  5. 规范化算法：我们了解了将数据库模式分解为3NF或BCNF的算法思路。你不需要背诵算法步骤，但需要理解其工作原理。

为了帮助大家更好地理解这些略显复杂的算法，我们的教学计划是：先快速讲解所有核心概念，然后在后续课程中通过更多实际例子来深化理解。

以下是近期的课程路线图：

  • 今天：学习关系代数与查询执行。
  • 周四：学习性能调优、事务与可串行化，并提供更多关于理论概念（如规范化算法）的实例讲解。
  • 下周：我将外出参加会议，但会为大家提供涵盖整个课程内容的总结视频和幻灯片。

关系代数介绍

上一节我们回顾了数据库设计的理论部分，本节中我们来看看操作数据的数学语言——关系代数。

关系代数可以被视为操作关系（即数据表）的数学系统，或者说是关系模型的数据操作语言。它由运算符、组合运算符构成表达式的规则以及计算这些表达式的规则组成。

学习关系代数非常重要，因为它构成了数据库管理系统（DBMS）实现的基础。在我们之前的课程中，我们主要从用户的角度学习如何设计数据库模式和使用数据库。而关系代数揭示了系统底层处理查询的逻辑。

关系代数核心运算符

关系代数包含一系列用于操作关系的运算符。以下是其主要的运算符分类及说明。

基本集合运算符

这些运算符继承自集合论，要求参与运算的关系必须兼容（即具有相同的属性集）。

  • 并集：R ∪ S，返回在R或S中出现的所有元组。
  • 交集：R ∩ S，返回同时在R和S中出现的元组。
  • 差集：R - S，返回在R中出现但不在S中出现的元组。

专门的关系运算符

这些是关系代数特有的运算符。

  • 选择：σ，根据指定条件过滤元组。例如，σ_{age>18}(Student) 会选择所有年龄大于18的学生。
  • 投影：π，选择指定的列（属性），并去除重复行。例如，π_{name, major}(Student) 会返回所有学生的姓名和专业。
  • 笛卡尔积：×，将两个关系的所有元组进行组合。若R有m行，S有n行，则 R × S 有 m*n 行。
  • 连接：多种连接是选择与笛卡尔积的组合，用于根据相关条件合并两个关系的元组。
    • θ连接：R ⋈_{condition} S
    • 等值连接：θ连接的一种特例，连接条件为相等比较。
    • 自然连接：R ⋈ S，自动在所有同名属性上做等值连接，并去除重复列。

查询执行概述

理解了表达查询的数学语言（关系代数）后，本节我们来看看数据库系统如何实际执行这些查询，即查询执行。

查询执行是数据库管理系统将用户提交的高层查询语句（如SQL）转换为一系列低级操作，并最终返回结果的过程。其核心目标是高效、正确地获取数据。

查询处理步骤

一个查询从提交到返回结果，通常需要经历多个步骤。以下是典型的查询处理流程。

  1. 解析与翻译：系统首先对SQL查询进行词法和语法分析，确保其格式正确，然后将其转换为内部表示形式，通常是关系代数表达式树。
  2. 优化：查询优化器是DBMS的大脑。它会对初始的关系代数表达式进行等价变换，考虑不同的连接顺序、访问路径（使用哪个索引）和算法，并基于成本模型估算每种执行计划的代价，最终选择一个它认为最高效的查询执行计划。
  3. 执行：查询执行引擎按照选定的执行计划，调用底层的存储管理器、索引管理器等组件，实际地读取数据、进行运算（如选择、投影、连接），并将最终结果返回给用户。

总结

本节课中我们一起学习了数据库系统的两个底层核心模块。

首先，我们学习了关系代数，它是操作关系型数据的数学基础，包含并、交、差、选择、投影、笛卡尔积和连接等运算符。理解关系代数有助于我们洞悉SQL查询背后的逻辑。

其次，我们探讨了查询执行的基本过程，包括解析、优化和执行三个阶段。其中，查询优化器通过选择高效的执行计划，对查询性能起着至关重要的作用。

掌握这些知识，为我们后续学习数据库性能调优、事务管理等更高级的主题奠定了坚实的基础。

015：性能调优、事务与可串行化 📊

在本节课中，我们将学习数据库性能调优的基本方法、事务的核心概念，以及如何判断事务调度是否可串行化。课程内容将包含具体算法和实践技巧，旨在帮助初学者理解和应用这些关键知识。

课程公告与回顾 📢

首先，我们来看一些课程相关的公告。本周没有测验，但下周将进行本课程的最后一次测验。关于作业2，目前已发布三道题目，最终将共有四道题目供大家练习。作业提交截止日期已延长三天，至第11周的星期一。请注意，如有特殊情况需要延期，请提前通过相应渠道提交申请。

上一讲我们学习了关系代数。关系代数是数据库操作的形式化表示，可以理解为数据库管理系统（DBMS）实际执行的操作。我们学习了重命名、选择、投影等基本操作，以及并集、交集、差集等集合运算符。此外，我们还了解了SQL查询如何映射到关系代数操作，以及DBMS如何利用由关系代数操作构成的执行计划来估算查询成本。

性能调优 ⚡

上一讲我们了解到，DBMS可以通过关系代数操作组成的执行计划来估算查询成本。那么，从开发者的角度，我们如何利用这些信息来优化查询性能呢？本节我们将探讨这个问题。

为了提高数据库应用的执行效率，了解应用所需的数据操作类型非常有用。同时，由于实现相同数据检索机制可能存在多种不同方案，我们需要了解每种实现的成本。作为开发者，我们应鼓励DBMS使用最高效的方法。虽然无法直接操控DBMS的工作方式，但我们可以通过使用索引和避免一些不良实践来引导其高效运行。

应用程序员的选择会影响查询成本。首先，查询语句的编写方式会影响其执行性能。

以下是一些通用建议。通常，使用连接（JOIN）比使用子查询更快，尤其是在子查询是相关子查询的情况下。相关子查询是指子查询内部引用了外部查询的变量，这种查询可能非常慢，稍后我们会举例说明。

另一个良好的实践是先进行过滤。这意味着应尽早应用筛选条件，以减少后续操作需要处理的数据量。

总结 📝

本节课我们一起学习了数据库性能调优的基本思路，包括理解操作成本、优化查询编写（如优先使用连接而非相关子查询）以及先过滤原则。我们还回顾了关系代数，并了解了课程相关的公告。掌握这些知识有助于你编写出更高效的数据库查询。

016：课程总结 📚

在本节课中，我们将对COMP3311数据库系统课程的全部内容进行回顾与总结。我们将梳理本学期所学的核心主题，并对照课程的学习成果，帮助你为期末考试做好准备。

课程概览

在本课程中，我们主要学习了三大主题。

上一节我们介绍了课程的整体框架，本节中我们来看看第一个核心主题。

数据建模与数据库设计

在这个主题中，我们主要学习了：

  • ER建模：学习如何使用实体-关系图来概念化地描述现实世界的数据结构。
  • 关系建模：理解如何将ER模型转换为形式化的关系模型。
  • 模型映射：掌握如何将ER模型和关系模型最终映射为具体的SQL表结构。

数据库应用开发

在掌握了数据建模的基础后，我们进入了应用开发环节。以下是本主题涵盖的关键子领域：

  • SQL查询：学习使用SQL语言从数据库中检索数据。
  • 数据定义与修改：学习使用SQL创建、修改数据库结构（如表、索引）以及更新数据。
  • PostgreSQL：我们使用开源的PostgreSQL作为实践平台，因为它功能强大且支持丰富的扩展。
  • 扩展SQL：学习如何通过自定义函数、聚合函数和触发器来增强SQL的功能。
  • 过程化SQL：学习使用PL/pgSQL编写存储过程和函数。
  • Python交互：学习使用psycopg2模块编写Python程序来连接和操作PostgreSQL数据库。

数据库管理系统理论与技术

为了设计出更优的数据库并开发出高效的应用，我们需要理解DBMS背后的原理。以下是相关的理论和技术：

  • 函数依赖与规范化：学习如何识别数据中的冗余，并通过规范化理论（如第三范式3NF、BCNF）来设计出结构良好的数据库模式。
  • 关系代数与查询处理：理解DBMS内部如何将SQL查询转换为关系代数操作并执行，这有助于我们进行查询优化。
  • 事务与可串行化：学习数据库事务的概念，以及如何保证并发执行的事务调度是正确的（即可串行化的）。

这些理论直接服务于前两个主题。例如，规范化能帮助我们设计出无冗余的数据库；理解查询处理过程则能指导我们编写出性能更优的SQL语句。

学习成果与考试关联

我相信你们更关心期末考试会考什么。考试的目的并非设置障碍，而是检验你是否达到了课程预设的学习成果。因此，理解学习成果是备考的关键。

本课程共有8项学习成果，我们将逐一回顾。

学习成果1：开发准确且无冗余的数据模型

此成果的核心是数据模型。你可以通过以下问题自测：

  • 你能理解ER图吗？ 你能描述ER图中的不同实体和关系吗？如果不确定，请回顾相关课件。
  • 你能将ER模型映射到关系模型吗？ 还记得不同实体和关系的映射规则吗？如果不确定，请回顾相关课件。
  • 你能将ER模型映射为SQL表模式吗？ 这是将设计付诸实践的关键步骤。相关链接可提供帮助。
  • 你能进行第三范式（3NF）规范化吗？ 这是实践中最常用的范式之一。相关链接可提供帮助。
  • 你能进行BCNF规范化吗？ 这是另一个重要的范式。相关链接可提供帮助。

为了实现规范化，你需要掌握以下核心概念：

  • 函数依赖：理解其定义和作用。
  • 推理规则：掌握从已知函数依赖推导新依赖的规则。
  • 闭包：计算属性集在函数依赖下的闭包。
  • 最小覆盖：这是计算3NF分解的关键步骤。
  • 范式：理解各范式的定义和目标。

学习成果2：将数据模型实现为关系数据库模式

这项成果已基本涵盖在学习成果1中，重点是如何将ER模型映射为具体的SQL模式，因为最终我们需要在数据库中实际创建这些结构。

学习成果3：构建查询

这项成果要求你能够针对给定的数据库模式，编写SQL查询来回答各种问题。这涵盖了从简单的单表查询到复杂的多表连接、子查询和聚合操作。

学习成果4：使用SQL定义和修改数据

这项成果要求你不仅会查询，还要会使用SQL来创建和修改数据库本身。这包括：

  • 使用CREATE, ALTER, DROP语句定义表、视图、索引等。
  • 使用INSERT, UPDATE, DELETE语句修改表中的数据。

学习成果5：使用高级数据库特性

这项成果要求你能够利用现代DBMS的高级功能来构建更强大的应用。包括：

  • 约束：使用主键、外键、唯一约束、检查约束来保证数据完整性。
  • 视图：创建虚拟表来简化复杂查询或隐藏数据细节。
  • 触发器：在特定数据操作（增、删、改）发生时自动执行预定义的操作。
  • 事务：将一系列操作作为一个原子单元来执行，保证ACID特性。
  • 存储过程/函数：使用PL/pgSQL在数据库服务器端封装复杂的业务逻辑。

学习成果6：通过Python程序与数据库交互

这项成果要求你能够编写Python应用程序，使用psycopg2等库连接数据库、执行SQL语句并处理返回的结果，从而构建完整的数据库应用。

学习成果7：解释查询处理与优化概念

这项成果关注DBMS内部原理。你需要能够：

  • 将SQL语句翻译成等价的关系代数表达式。
  • 理解查询执行计划的基本概念。
  • 解释索引如何提高查询性能。
  • 识别可能导致性能低下的查询模式，并提出优化建议。

学习成果8：分析事务调度

这项成果要求你理解数据库并发控制。你需要能够：

  • 判断一个给定的事务调度是否是可串行化的。
  • 理解锁机制等并发控制技术的基本思想。

总结

本节课中，我们一起回顾了COMP3311数据库系统课程的完整知识体系。我们从数据建模与设计出发，学习了数据库应用开发所需的SQL及编程技能，并深入探讨了支撑这些实践的DBMS核心理论（函数依赖、规范化、查询处理、事务）。

请根据上述八项学习成果进行自查，针对薄弱环节，利用课程提供的链接和资料进行复习。扎实掌握这些内容，不仅能助你顺利通过考试，更能为你未来从事任何与数据相关的工作打下坚实的基础。祝你好运！

posted @ 2026-03-29 09:32  布客飞龙II  阅读(8)  评论(0)    收藏  举报