数据库系统概念 - 笔记1

本文最后更新于 2026年4月5日下午

教材：Database System Concepts

参考的博客：

涵盖内容：课本Chapter1-5，大致是数据库基础概念+SQL

Chapter 1 Intro

什么是DBMS

What is DBMS (Database System Management System)? :

一组相关数据的集合
一组用于访问这些数据的程序的集合

数据库就是这里的一组相关数据的集合，数据库的发明是为了填补原有文件系统的缺陷。

文件系统的缺陷 / Purpose of Database Systems

数据会有冗余：多个人创建不同的文件包含相同的文件、一个数据的不同副本不一致。
数据访问困难：缺乏标准化
缺乏完整性约束，不存在统一的机制让数据合法
不支持并发访问
不安全
有原子性问题：传统文件系统不能保证事务的完整性，容易造成半成品数据
数据孤岛：数据分散在不同格式的文件中，难以编写检索程序，因为数据是割裂的。

View of Data / 数据视角

数据视角就是从不同的角度去看待和管理数据。数据本身是一样的，但不同的人 / 层次看它的重点不同。

视角	关注点	例如
物理视角	数据怎么存在磁盘上，存储细节	文件组织、索引结构、磁盘块管理
逻辑视角	数据怎么组织、联系，结构化表示	表、列、外键
用户视角	用户需要看到什么数据，怎样屏蔽不相关的信息	只显示学生姓名和成绩，不显示学号

数据抽象

数据抽象：数据视角的不同表达方式。可以使得用户在不同层次上操作数据，而不必关系底层的物理存储结构或复杂的逻辑关系。

三层数据抽象：

物理层
- 数据在物理存储介质上的组织架构，涉及文件组织、索引、存储结构（B+ 树等），优化存储效率和访问速度。
逻辑层
- 描述数据的逻辑约束，如关系、表
视图层
- 针对不同用户做出的屏蔽，比如某些用户不能看到某些表，而另一些用户就可以

数据模型

数据模型是描述数据、数据联系、数据意义、一致性约束的工具

A collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints.

什么是一致性约束(consistency constraints)?

数据库的一种“法则”，确保数据在更新的时候始终保持准确性、完整性、有效性。

将一致性约束分类：

实体完整性：确保每一行都是唯一的 -> 主键约束

域完整性：约束特定列中的数据类型与范围，比如某列不能为空（非空约束），某列存在缺省值（默认约束），年龄不能为负数（检查约束）

参照完整性 -> 外键约束

其他用户自定义约束

数据模型有数据结构（数据实体和它们的关系）、数据操作（CRUD）、数据约束三部分。

主要有这些数据模型：

数据模型	解释
关系模型	数据存在二维表里面
实体-联系模型	E-R图，用于设计阶段
基于对象的数据模型	OOP，把数据看成对象（存在属性、方法）
半结构化数据模型	数据不在固定表格里，比如JSON, XML
网状数据模型	一个网络图，通过复杂指针连接
层状数据模型	树形的

实例与模式

实例 Instances: The collection of information stored in the database at a particular moment is called an instance of the database.

模式 Schema: The overall design of the database is called the database schema. 分为物理模式 physical schema和逻辑模式 logical schema，对应物理层与逻辑层。

物理数据具有独立性，修改物理模式不影响逻辑模式。举个例子：原本表是顺序存储，改为用B+树存储，但是写SQL的逻辑模式不需要修改。

概念	解释
模式	数据库的结构设计图。比如有哪些表、字段是什么类型、外键怎么关联（是“固定的结构”）。
实例	某一时刻数据库里实际存放的数据内容

数据库语言 Database Languages

数据库语言：用来与DBMS进行交流的语言，主要作用：

定义数据结构（表、字段、约束）
CRUD

数据库语言的组成：DDL DML。SQL包含了这两种语言。

DDL: Data Defination Language, 定义Database Schema的语言，也可以规定consistency constraints, 授权authorize users，比如SQL中的create table
DML: Data Manipulation Language, for CRUD

Database Users and Adminstrators

其实顾名思义。

History

略

课本最后的Summary挺适合复习的。

Chapter 2 Introduction to the Relational Model

关系数据库的结构

表

关系数据库由表的集合组成，每一个表就是一个关系。

A relational database consists of a collection of tables, each of which is assigned a unique name.

表由表头 (Schema)，列名和数据类型和表体 (Instance)，具体的数据行组成。

关系模式 Schema

关系模式是表的结构定义，包括：

表名
属性名、数据结构
完整性约束

关系实例

某一时刻表的具体数据

概念	含义	举例
关系	一个二维表	学生表、员工表
关系模式	表的结构定义（字段名、数据类型、约束）	学生(学号, 姓名, 年龄)
关系实例	表中某个时间点的数据内容（若干行）	某一时刻，学生表中有 5 条记录
表	实际存储数据的结构；就是“关系”的表现形式	`Student` 表
关系数据库	一组相关的表组成的数据库系统	有学生表、课程表、选课表等

tuple 元组

In mathematical terminology, a tuple is simply a sequence(or list) of values. A relationship between n values is represented mathematically by an n-tuple of values, that is, a tuple with n values, which corresponds to a row in a table.

attribute 属性

attribute refers to a column of a table.

domain of an attribute: For each attribute of a relation, there is a set of permitted values.

atomic: 如果一个域的值在逻辑上/使用上还能再拆成更小的有意义的单元，那它就不是原子的

Key 码 / 键

Key 是用于唯一标识或建立关系的属性，分为以下几个：

super key

一个或多个属性的集合，其中可能包含冗余的属性。

在一个关系中唯一标识一个元组。

candidate key

标识一个元组的最小属性集合，一个表可以有多个candidate key

primary key

候选码中选出的唯一标识符

不为NULL，值唯一。

foreign key

本关系/表中的某个属性恰好是另外一个关系里的主码，由此可建立表间关系。

schema diagram 模式图

模式图是一个 图形化工具，表示一个关系数据库中各个关系（表）之间的结构与联系。

每个矩形代表一个关系（表）；
每个矩形中列出该表的属性（字段）；
- 主码一般加下划线；
- 外码一般用箭头指向它所引用的主码；
箭头用于表示外码 → 主码的引用关系。

We use a two-headed arrow, instead of a single-headed arrow, to indicate a referential integrity constraint that is not a foreign-key constraints.

意义不明，留待TODO 略

Relational Algebra

Data Manipulation Languages (DML):

Procedural 过程性的，需要一步一步指出如何计算得到结果
Non-Procedural，指出想要得到的结果而不在乎过程
SQL 是最典型的非过程化语言，你只需要描述你想要查询的数据，数据库系统会负责如何执行这个查询。
关系代数 就是过程化语言的一种。你需要通过一系列操作来获取数据，比如选择、投影、联接等。

关系代数有8种基本运算，运算得到的结果全都是表：

运算	符号	描述	示例	功能说明
union	$\cup$	取两个关系中所有的元组（去重合并）	$R \cup S$	合并两个结构相同的关系
intersection	$\cap$	取两个关系中共同的元组	$R \cap S$	找出两个关系都包含的数据
difference	$-$	得到只属于第一个关系的元组	$R - S$	从$R$中去除所有也属于$S$的元组
select	$\sigma$	从关系中选出满足条件的元组（行）	${\sigma}_{age>30}(Employee)$	选出年龄大于30的员工
projection	$\Pi$	从关系中选出特定的属性（列）（去重）	${\Pi}_{name,salary}(Employee)$	只显示姓名和薪水字段
Cartesian-Product	$\times$	求笛卡尔积	$R \times S$	用于构造连接
join	$\bowtie$	按条件将两个关系中的元组连接	$R \bowtie_{R.a=S.a} S$	按公共属性把两表拼接起来
assignment	$\leftarrow$	将运算结果赋值给一个关系变量	$T \leftarrow {\sigma}_{age>30}(Employee)$	保存中间结果供后续运算使用
rename	$\rho$	对关系或属性进行重命名	${\rho}_{E(name,salary)}(Employee)$	给关系或列改名，便于后续引用

其中union, intersection, difference, Cartesian-Product就是很朴素的集合运算，补充一些要点：

求并集、交集、差集的前提条件:
1. 输入的两个关系具有相同数量的属性
2. 当属性相关联时，两关系中对应属性的类型相同（即不要求同名）
求笛卡尔积要求r ∩ s=∅

assignment和rename也没什么好说的

select和projection也不难理解：select是选出特定的行，projection是选出特定的列

join比较复杂：

单纯的join，叫做等值join，这个不会把相同的列给去掉，举个例子：

这里用于匹配的ID列仍然存在。

然后引出natural join，就是在上面的一般的join的基础上去掉相同的列，这很自然，所以叫这个名字。一般直接记作 $\bowtie$。

natural join不产生含null的记录

如何产生含null的记录？有Left Outer Join和Right Outer Join和Full Outer Join，可以考虑选课名单（课程 - 学号）和学生选课（学生 - 选课）的两个关系：从学生视角来看，不需要没选的课；从教务处视角来看，需要知道哪些课没人选。

Chapter 3 Introduction to SQL

Overview of the SQL

The SQL language has several parts:

DDL: the DDL part in SQL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.
DML: the DML part in SQL provides the ability to query info from the databse and to insert tuples into, delete tuples from, and modify tuples in the database.

-> Data-defination 是操作 schema, Data-manipulation是操作实例。

Integrity: 保证传入数据的规范性

Data Defination 数据定义

数据定义是 SQL 定义数据库结构及其元信息的能力，是数据库设计的第一步，涉及表的结构、数据类型、约束规则、存储策略等。

基础数据类型 Basic Types

数据类型规定了值的取值范围、精度和运算。

数据类型	含义
`CHAR(n)`	固定长度字符串，占用 n 个字符，不足补空格
`VARCHAR(n)`	可变长度字符串，最大长度为 n
`INT`, `INTEGER`	整数（通常 4 字节）
`SMALLINT`	小整数（通常 2 字节）
`NUMERIC(p,d)`	精确小数，总位数为 p，小数位数为 d
`REAL`	单精度浮点数（近似值）
`DOUBLE PRECISION`	双精度浮点数
`FLOAT(n)`	精度为 n 的浮点数，n 是二进制有效位数

Each type may include a special value called the null value. A null value indicates an absent value that may exist but be unknown or that may not exist at all. In certain cases, we may wish to prohibit null values from being entered, as we shall see shortly.

对于字符串其实总是推荐使用VARCHAR，因为可变数组和固定长度数组在比较的时候可能不符合预期。

even if the same value “Avi” is stored in the attributes A and B above, a comparison A=B may return false. We recommend you always use the varchar type instead of the char type to avoid these problems.

Basic Schema Defination

关系模式：描述一张表的结构：表名 + 属性（列名）+ 属性的数据类型。

CREATE TABLE Student (
    ID INT,
    Name VARCHAR(50),
    Age INT
    PRIMARY KEY(ID)
);

注意类型的后置。

integrity constraints 完整性约束

主要包括（这里只提及这些）：

主键（PRIMARY KEY）
非空（NOT NULL）
外键（FOREIGN KEY）
检查条件（CHECK）

CREATE TABLE Course (
    CourseID INT,
    CourseName VARCHAR(100) NOT NULL,
    TeacherID INT,
    PRIMARY KEY(CourseID)
    FOREIGN KEY (TeacherID) REFERENCES Teacher(ID)
);

完整性约束是指数据库在逻辑层面上为保证数据正确性、合法性和一致性而设置的规则。主要类型包括：

实体完整性：

主键（PRIMARY KEY）不能为 NULL，唯一标识一条记录。

参照完整性：

外键（FOREIGN KEY）必须引用主表中存在的记录，防止“悬挂指针”。

域完整性：

每个字段的数据类型、取值范围（如 CHECK, NOT NULL, 数据类型本身）。

用户定义的完整性：

开发者自己规定的业务规则（如工资必须大于 0）。

例如：
CREATE TABLE Student (
    ID INT PRIMARY KEY,               -- 实体完整性
    Age INT CHECK (Age >= 0),         -- 域完整性
    ClassID INT,
    FOREIGN KEY (ClassID) REFERENCES Class(ID) -- 参照完整性
);
一致性约束通常是指在数据库操作前后，数据要保持符合数据库的所有完整性约束的规则——数据库处于一个“一致状态”。它强调的是数据库整体状态在事务执行前后不会出现非法或冲突数据。

它并不是一种“单独的约束类型”，而是指多个完整性约束共同作用的结果。

一致性约束强调的是事务操作对数据一致性的维护。

对比项完整性约束一致性约束

概念本质一种具体的规则机制一种结果状态（是否满足全部约束）

作用时间点约束规则在建表/修改表时定义并持续生效通常在事务前后判断数据库是否仍然满足完整性约束

是否可单独定义可以明确写出（如 PRIMARY KEY, CHECK, FOREIGN KEY）不能单独定义，是对多个完整性规则是否被违反的总体评估

举例 CHECK (age >= 0)、FOREIGN KEY 插入一个不存在外键值时，破坏一致性约束（即违反特定的完整性约束）

关系完整性约束是具体规则 一致性约束是整体状态结果，是否“违反”完整性约束决定一致性是否成立

对比项	完整性约束	一致性约束
概念本质	一种具体的规则机制	一种结果状态（是否满足全部约束）
作用时间点	约束规则在建表/修改表时定义并持续生效	通常在事务前后判断数据库是否仍然满足完整性约束
是否可单独定义	可以明确写出（如 `PRIMARY KEY`, `CHECK`, `FOREIGN KEY`）	不能单独定义，是对多个完整性规则是否被违反的总体评估
举例	`CHECK (age >= 0)`、`FOREIGN KEY`	插入一个不存在外键值时，破坏一致性约束（即违反特定的完整性约束）
关系	完整性约束是具体规则	一致性约束是整体状态结果，是否“违反”完整性约束决定一致性是否成立

创建关系模式

基本格式：

CREATE TABLE r (
    A1 D1,
    A2 D2,
    ...
    An Dn,
    -- 完整性约束
    完整性约束1,
    完整性约束2,
    ...
);

删除关系模式

drop table r

修改关系模式

alter table r add A D
-- 添加一个名为A类型为D的字段，注意这里也是**类型后置**
alter table r drop A
-- 删除字段A

delete from r
-- 保留关系r，但是删除r中的所有tuples

数据查询 SQL Queries

其实就是SELECT的用法大全，这一块其实讲也没什么好讲的，感觉多写点就会了，稍微记一些关键的，权当摘要。

select A from r where p

等价于关系代数 $$ \Pi_{A}(\sigma_p(r)) $$

一套完整的SQL查询语句：

SELECT [DISTINCT|ALL] column_list
FROM table_list
[WHERE condition]
[GROUP BY group_columns]
[HAVING group_condition]
[ORDER BY sort_columns [ASC|DESC]]
[LIMIT n]   -- 有些方言如 MySQL 支持，限制查询数量

子句	功能描述
`DISTINCT`	去重，只保留唯一结果。
`ALL`	不去重，保留所有结果。（默认）
`GROUP BY`	分组，将结果按某一列归类，通常与聚合函数（如 `SUM`, `COUNT`）配合使用。
`HAVING`	对分组后的结果进行筛选（类似 `WHERE`，但作用于分组之后，也就是说`HAVING`必须和`GROUP BY`搭配使用）。
`ORDER BY`	指定结果排序顺序（升序 `ASC` / 降序 `DESC`）。
`LIMIT`	限制返回的行数（如 `LIMIT 10` 表示最多返回 10 行）。只有部分方言支持。

`AS`关键字

一个别名定义的关键字，用于给列名或表名起一个临时的新名字，让查询结果更具有可读性。

字符串

字符串常量用单引号扩起。

字符串中包含单引号时需双写单引号：

SELECT 'Hello World'; -- 正常字符串
SELECT 'It''s OK';     -- 字符串中包含单引号

`CASE`

用于条件分支

CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    ...
    ELSE 默认值
END

`LIKE`运算符

使用wildcard进行匹配：

通配符	作用	示例
`%`	匹配任意长度的子串（包括空串）	`'A%Z'` 可匹配 `'AZ'`, `'%A%Z%'` 可匹配 `'天下トーイツA to Z☆'`
`_`	匹配任意一个字符	`'A_Z'` 可匹配 `'ABZ'`

如果需要匹配_ %本身，使用ESCAPE关键字定义一个转义符：

-- 查找包含文字 "ab%cd%" 的字段
SELECT col
FROM table
WHERE col LIKE 'ab#%cd#%' ESCAPE '#';
-- # 是自定义的转义字符，这样 #% 就会被解释为实际的 % 字符，而不是通配符。

SQL没有自己的转义符，需要用户自定义

拼接字符串

使用||。

大小写转换

LOWER() UPPER()

数据重复问题

在标准关系代数中，关系是集合，不允许有重复的元组；但在 SQL 中，默认查询结果是一个多重集（Multiset），也称袋（bag），是允许重复数据的。

SELECT branch_name FROM loan;

-- 如果表 loan 中有多个贷款来自同一家银行，比如 "Perryridge" 出现了 3 次，那么这个查询结果就会返回 3 个 "Perryridge"，不会自动去重。
-- 只有使用 DISTINCT 关键字才会去重

多关系查询

这里解析一下from：如果不加where，那么会生成一个超大的笛卡尔积，性能不好。一般来说，先筛再Join的性能会好很多。

where里面的谓词

where salary between 9000 and 11000

等价于

where salary <= 11000 and salary >= 9000

上面表示更清晰。

<>是不等于！！！不是!=

元组解包 / 匹配

SQL支持类似python的元组匹配

where (instructor.ID, dept name) = (teaches.ID, 'Biology');

等价于

where instructor.ID= teaches.ID and dept name = 'Biology';

这个写法可以用在where (...) in cte中，很好用。

集合运算

SQL 的集合运算（UNION、INTERSECT、EXCEPT）和数学集合一样默认去重，但也提供了 ALL 版本用于保留重复。

用法举例：

SELECT ... FROM R
UNION -- 换成 INTERSECT EXCEPT都可以
SELECT ... FROM S

如果希望保留重复数据，必须显式使用 ALL 关键字。

操作	解释
`UNION ALL`	保留所有重复结果
`INTERSECT ALL`	保留重复次数为两个集合中相同元组出现次数的最小值
`EXCEPT ALL`	按出现次数逐个剔除

举个例子，给定两个查询结果：

查询1返回{a, a, b}
查询2返回{a, b, b}

操作	结果	解释
`UNION`	`{a, b}`	去重合并
`UNION ALL`	`{a, a, a, b, b}`	保留全部重复
`INTERSECT`	`{a, b}`	`a` 、 `b` 都出现过，去重
`INTERSECT ALL`	`{a, b}`	`a` 最小次数是1，`b` 是1
`EXCEPT`	`{}`	`a`和`b`都有互相出现，全部去除
`EXCEPT ALL`	`{a}`	两个 `a` 被保留（`a` 有两个，第二个有一个，保留差值）

对于null的处理

任何有null参与的数学运算结果都是null。

涉及null的比较会带来问题，因此SQL引入了第三种逻辑值：unknown

运算符 (AND)	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

运算符 (OR)	TRUE	FALSE	UNKNOWN
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN

AND：像“找茬”，只要有 FALSE 就是 FALSE；如果没有 FALSE 但有 UNKNOWN，结果就是 UNKNOWN。
OR：像“找闪光点”，只要有 TRUE 就是 TRUE；如果没有 TRUE 但有 UNKNOWN，结果就是 UNKNOWN。
UNKNOWN：它就像一个黑洞，除非碰到了能直接决定结果的 FALSE (在 AND 中) 或 TRUE (在 OR 中)，否则它会让结果一直保持 UNKNOWN。

Aggregate Functions

SQL 提供了一些聚集函数，它们用于对一组数据做运算，返回一个单一值：

函数	含义
`AVG(x)`	平均值
`MIN(x)`	最小值
`MAX(x)`	最大值
`SUM(x)`	总和
`COUNT(*)`	总行数（包括 `NULL`）
`COUNT(x)`	非 `NULL` 值的个数

GROUP BY将结果按字段分组，通常和聚集函数一起使用

SELECT branch_name, AVG(balance)
FROM account
GROUP BY branch_name;

-- 每个 branch_name 是一个组
-- 每组分别计算平均 balance

HAVING子句用于对分组后的结果进行筛选：

SELECT branch_name, AVG(balance)
FROM account
GROUP BY branch_name
HAVING AVG(balance) > 1200;

-- 首先 GROUP BY 将 account 表按 branch_name 分组
-- 再用 HAVING 过滤掉平均余额不大于 1200 的组

Nested Subqueries

A subquery is a select-from-where expression that is nested within another query. A common use of subqueries is to perform tests for set membership, make set comparisons, and determine set cardinality by nesting subqueries in the where clause.

e.g.

SELECT ...
FROM ...
WHERE 某列 OPERATOR (SELECT ... FROM ... WHERE ...);

-- 其中的 (SELECT ...) 就是一个 子查询。

用于实现集合运算

这一点很重要，就如果某个需求表现为集合运算的形式，就可以用nested queries来解决。

select distinct course_id
from section
where semester = 'Fall' and year = 2017 and
course_id in (
	select course_id
    from section
    where semester = 'Spring' and year = 2018
)

这一步其实也可以用intersect来实现：

(select course_id
from section
where semester = 'Fall' and year = 2017)
intersect
(select course_id
from section
where semester = 'Spring' and year = 2018
)

如果把in改成not in，那就变成了except。

some all exists unique

"greater than at least one": 在SQL里面用some：

select name
from instructor
where salary > some (
    select salary
    from instructor
    where dept_name = 'Biology'
)

< > <= >= = <>都可以搭配some。其中= some等价于in，<> some不等价于not in

"greater than all": 在SQL里面用all：

select name
from instructor
where salary > all (
    select salary
    from instructor
    where dept_name = 'Biology'
)

和some的规则同理，其中<> all等价于not in，= all不等价于in

exists用于判断子查询的结果是否非空（存在）

-- 找出既有存款又有贷款的顾客
SELECT DISTINCT customer_name
FROM borrower AS R
WHERE EXISTS (
    SELECT *
    FROM depositor AS S
    WHERE S.customer_name = R.customer_name
);

当然也有not exists

unique用于判断子查询的结果是否包含重复的tuple，如果有就返回FALSE，反之。即使结果为空也会返回TRUE。

一个应用场景是使用not unique，找有>2个的属性

-- 找出在Perryridge有多个存款账户的顾客
SELECT T.customer_name
FROM depositor AS T
WHERE NOT UNIQUE (
    SELECT R.customer_name
    FROM account AS S, depositor AS R
    WHERE T.customer_name = R.customer_name
      AND R.account_number = S.account_number
      AND S.branch_name = 'Perryridge'
);

这里可以看出subquery可以访问外层的关系。

现代SQL很少使用UNIQUE，更多使用GROUP BY+HAVING，比如对于上面的例子：

SELECT customer_name
FROM depositor D, account A
WHERE D.account_number = A.account_number
  AND A.branch_name = 'Perryridge'
GROUP BY customer_name
HAVING COUNT(*) > 1;

Subqueries in the From Clause

SELECT ...
FROM (SELECT ... FROM ...) AS 别名(列名1, 列名2, ...)

-- 也可以写成：

SELECT ...
FROM (SELECT ... FROM ...) AS 别名

必须给subquery起别名，否则会报错

with子查询

把嵌套子查询写得更优雅

Scalar Subqueries

the subquery returns only one tuple containing a single attribute; such sub-queries are called scalar subqueries.

即：只包含一行一列

select dept_name,
        (select count(*)
          from instructor
          where department.dept_name = instructor.dept_name)
        as num_instructors
from department;

使用标量子查询进行单次计算

(select count (*) from teaches) / (select count (*) from instructor);
-- 在某些数据库中会由于没有from语句而报错
select (select count (*) from teaches) / (select count (*) from instructor) from dual;
-- 提供了一个dummy relation来提供from语句且不产生其他副作用

这里的dual是一个 dummy relation, 一行一列，始终存在。

Modification of the Database

deletion

DELETE FROM 表名
[WHERE 条件];
-- 如果忽略掉 WHERE 子句就会把整张表的内容全删了，只留下框架

下面是一些更具体的delete案例：

delete from instructor
where dept_name = 'finance'

-- 下面是一个典型的`where xxx in cte`的应用场景
delete from instructor
where dept_name in 
(select dept_name
from department
where building = 'Watson'
)

delete from instructor
where salary < (select avg (salary)
from instructor);

多表关联删除的语法：

delete from owns
where driver_id = '12345'
  and license_plate in (
    select license_plate 
    from car 
    where year = 2010
  );

insertion

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

-- 也可以用下面更清晰的写法，显式指定列名：

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

可以使用insert into select子句插入多条tuple：

-- 给Perryridge分行的所有存在贷款的客户送$200，并新建账户
insert into account
select loan_number, branch_name, 200
from loan
where branch_name = 'Perryridge'

插入数据需要符合以下条件：

列数和指定类型匹配
满足主键唯一约束，保证主键不重复

对于未指定的列，插入动作会有以下行为：

若定义了默认值，则用默认值插入当前列
否则会被赋空值 NULL

updates

UPDATE 表名
SET 列名 = 新值
[WHERE 条件];

-- 如果省略 WHERE 语句，则会更新整张表的所有记录

比如

update account
set balance = case
	when balance <= 10000 then balance * 1.05
	else balance * 1.06
end;

Chapter 4 Intermediate SQL

Join Expressions 再探

natural join

要求所有同名属性全部相等

select name, course_id
from student natural join takes;

本质是：

找到同名列
过滤掉同名列中值不相等的行
两个同名列只保留一列

Join Conditions

可以用on，这个简单，按下不表。

on有一个语法糖join ... using，例：

select *
from student join course_id using(id)

只用using()里面那个属性在join的时候来判断,而不像natural join一样在有多个同名属性的情况下全都要求相等

outer join

select *
from student natural join takes;

natural join会丢弃takes.id <> student.id的行，对于只在一个表中存在的ID，由于在另一个表中找不到对等的值,也会被抛弃，但在实际需求中，即使没选课的学生，我们也希望展示出来，故这里需要使用outer join。

分为：

left outer join
right outer join
full outer join

前面其实写过这一部分，按下不表。

用outer join的时候，注意最好是先把条件全部过滤好的两个subquery最后一步再进行outer join，这是因为如果不是最后一步进行outer join，在outer join之后会进行where操作，而where操作里面但凡有null就不会为True，会被过滤，相当于白outer join了。

Views 视图

视图是一个虚拟表，是一个由SQL查询语句的结果定义的表。视图本身不存储数据，而是依赖于底层表的数据。

视图本质是查询的一个 alias / encapsulation。在查询中使用视图时，SQL引擎不会直接从某个表中读取数据，而是将视图的定义展开成原始查询语句再执行，这就是视图展开，如果视图里面还包含了视图，那么展开是递归的。

create view 视图名 as
select ...
from ...
......

定义完了视图之后，就可以在任何地方像使用普通的表一样使用了。

视图中的数据是动态的：每次查询视图时，系统都会执行它背后的查询语句。
对于需要频繁使用的复杂查询，视图可以提升开发效率，但不要滥用（尤其在高性能场景中）。

视图的更新：不建议。

Transaction 事务

事务：用户定义的一个操作序列。这个序列中的所有操作要么全部成功执行，要么全部不执行。

事务的四大特性：

含义	解释
Atomicity	原子性	事务中所有操作是一个整体，不可分割，要么全部执行成功，要么全部失败。
Consistency	一致性	事务执行前后，数据库都处于一致状态，不会破坏数据的完整性约束。
Isolation	隔离性	多个事务并发执行时，彼此操作互不干扰。如 A 在转账，B 不能看到中间过程。
Durability	持久性	事务一旦提交，所做的修改永久生效，即使断电或崩溃也能恢复。

简称ACID

事务的语法：

begin atomic
	-- 一组语句
	update ...;
	insert ...;
end;

定义完成事务之后则执行

commit;

如果事务在执行过程中任一语句出错，则执行

rollback;

进行回滚，回到最初状态。

integrity constraints 完整性约束

单个关系上的约束

not null：无需多解释。
unique：表示字段（或字段组合）的值必须唯一（允许 NULL 值）。通常用于定义候选键。
check：用于指定某个逻辑条件必须成立，**不支持使用 select **子查询作为判断条件

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    
    -- 1. NOT NULL 约束：姓名不能为空
    Name varchar(50) NOT NULL,
    
    -- 2. UNIQUE 约束：身份证号不能重复，但可以有一个人为 NULL（取决于数据库实现）
    ID_Card varchar(18) UNIQUE,
    
    -- 3. CHECK 约束：限制年龄范围
    Age int CHECK (Age >= 18 AND Age <= 65),
    
    -- CHECK 约束：限制性别只能是特定的值
    Gender varchar(10) CHECK (Gender IN ('Male', 'Female', 'Other'))
);

Referential Integrity 参照完整性约束

用于跨表约束，确保一个表中的某字段引用另一个表中已有的数据。

默认引用 (Implicit Reference)

foreign key (dept_name) references department

其中dept_name是当前定义的Schema的一个Attribute，而department是另一个Relation。

底层行为：当你省略括号中的属性名时，SQL 标准规定该外键必须引用被参照表的主键 (Primary Key)

即：now.dept_name必须在department的主键中存在

显式引用 (Explicit Reference)

foreign key (dept_name) references department(dept_name)

底层行为：目标列（dept_name）不必是主键，但必须具有唯一性约束或本身就是主键。即它必须是一个超键 (Superkey)。

回顾一下Super key的概念：

只要能唯一标识relation中的一个tuple的attribute set，就是super key。

即：now.dept_name必须在department.dept_name中存在

级联操作用于自动传播更新或删除操作：

FOREIGN KEY (branch_name) REFERENCES branch(branch_name)
ON DELETE CASCADE
ON UPDATE CASCADE

含义：

ON DELETE CASCADE：如果分支被删除，该分支的内容也会被自动删除；
ON UPDATE CASCADE：如果分支名被修改，引用它的表中的名字也会自动跟着改。

Assigning Names to Constraints

-- 使用constraint关键字命名该限制
salary numeric(8,2), constraint minsalary check (salary > 29000),
-- 删除该限制
alter table instructor drop constraint minsalary;

Assertion 断言

断言在主流数据库就不怎么受支持，所以这里按下不表。

高级数据类型

内建数据类型

除了 基本数据类型 ， SQL 还支持其他的一些类型，例如：

类型	含义	示例
`DATE`	表示日期（年-月-日）	`'2025-04-29'`
`TIME`	表示时间（时:分:秒）	`'14:30:00'`
`TIMESTAMP`	表示完整的日期时间（含毫秒）	`'2025-04-29 14:30:00'`

使用extract(field from d)来提取出相应的域，这里的field可以是year month day hour minute second。

大对象类型 Big Object Types

类型	含义	用于
`BLOB`	Binary Large Object（字节流）	二进制数据，如图片、音频
`CLOB`	Character Large Object（字符流）	文本数据，如文档、大段文字

用户定义类型

只有部分SQL支持

CREATE TYPE Dollars AS NUMERIC(12, 2) FINAL;

FINAL 表示该类型不可被继承或扩展

类型转换 cast

SQL 提供 CAST() 函数用于显式地将一个值转换为另一种数据类型：

CAST(expression AS target_type)

空值合并 conalesce

语法： coalesce(arg1, arg2, ...) 逻辑：物理扫描参数列表，返回第一个非null的值。要求所有参数物理类型一致。

-- 如果 salary 是 null，物理替换为 0 以便后续数学运算
select ID, coalesce(salary, 0) as actual_salary
from instructor;

decode

Oracle特有

语法：decode (value, match-1, replacement-1, match-2, replacement-2, …, match-N, replacement-N, default-replacement);

select ID, decode (salary, null, 'N/A',salary) as salary
from instructor

default value

create table student
    (ID varchar (5),
    name varchar (20) not null,
    dept_name varchar (20),
    tot_cred numeric (3,0) default 0,
    primary key (ID));

SQL类型系统

SQL 的类型系统是弱类型的，也就是说：

SQL 会自动进行隐式转换，如字符串 '123' 自动转为整数；
类型冲突不一定导致编译错误（如比较 VARCHAR 与 INT 可能可行）；
某些数据库（如 MySQL）甚至允许插入错误类型的数据（比如把字母插进数值字段）。

所以重要字段应使用 CAST() 或 DOMAIN 等方法明确其类型。

Authorization

有这么几种权限（privilege）：select, insert, update, delete

授权 / 收回权限

grant语句用于授权，基本语法形式：

grant <privilege list>
on <name of realations or views>
to <users or roles>

e.g.

grant select on department to Amit, Satoshi;

grant update(budget) on department to Amit,Satoshi;

如果用户名是public，就是相当于授权所有用户

收回权限用revoke，语法和grant基本一致。

role

相当于一个语法糖

create role instructor;
grant select on takes to instructor;
create role dean;
grant instructor to dean; -- 可以给角色也可以给用户
grant dean to Satoshi;

模式的授权

grant references(dept_name) on department to Mariano;

这主要是为了满足外码的约束，和check约束。

其实没太理解，我觉得用到了自然就懂了）

权限的转移

grand select on department to Amit with grant option;

with grant option授予Amit授权他人的权限。

权限的收回

默认是级联收回，也就是上游断了下游也得断。

如果不想级联收回，使用restrict

revoke select on department from Amit, Satoshi restrict

但是这个在现实生活中经常是不合适的，因此SQL允许通过role来授权而不是通过user来授权。

Chapter 5 Advanced SQL

Accessing SQL from a Programming Language

似乎主要是讲了Java，但是感觉不太会考，基于速通的目的此处暂且放掉，简单说两句，TODO

JDBC：Java提供的连接到数据库服务器的API

OCBC：the Open Database Connectivity standard defines an API that applications can use to open a connection with a database, send queries and updates, and get back results.

函数与过程化结构

SQL的模块化编程部分包括：

函数：有返回值，适合用于查询中直接调用
过程：无返回值，但可通过参数传入/传出信息，适合复杂的数据操作流程
触发器：无返回值，当某些事件（如插入、更新、删除）发生在某个表上时，自动执行的一段代码

下面逐一展开。

函数 function

主要特点：

有返回值
一般用于查询，SELECT语句中可直接调用
不能对数据库进行modification，比如insert, update

例：给定客户的名字，返回其拥有的存款账户的数目：

create function account_count(customer_name varchar(20))
returns integer -- 返回值后置
begin
	declare account integer; -- 声明变量
	select count(*) into account
	from depositor
	where depositor.account_name = customer_name;
	return account
end;

如何调用：

select customer_name, customer_street
from customer
where account_count(customer_name) > 1

过程 procedure

主要特点：

无返回值
可以通过参数来传入传出信息
可以修改数据库内容，也可以做查询
适合封装复杂逻辑和批处理操作

例：给定客户的名字，返回其拥有的存款账户的数目：

create procedure account_count_proc (
	in customer_name varchar(20)
    out a_count integer
    -- in 和 out 分别为传入和传出的参数
)
begin
	select count(*) into a_count
	from depositor
	where depositor.account_name = customer_name
end;

调用：

declare a_count integer;
call account_count_proc('Smith', a_count);

触发器 trigger

主要特点：

无返回值
无需手动调用，由DBMS自动触发
可以绑定在 INSERT、UPDATE、DELETE 上
每个触发器与具体的表、操作类型相关联

例：插入用户时，created_at字段会自动设置为当前时间：

create trigger before_insert_user
before insert on users 
referencing new row as nrow
referencing old row as orow	
-- before 指定触发时机。在执行 insert 操作之前运行
for each row -- 行级触发
begin
	set nrow.created_at = NOW()
	-- NEW 代表即将被插入的那一行
end

过程化结构

SQL 引入了类似编程语言的控制流程结构，即过程化结构，如：

循环
分支
复合语句

这些结构使得 SQL 不只是查询语言，还可以实现复杂的逻辑控制。

它的特性有：

支持控制结构：如 if-then-else、while、repeat 等
可组合语句块：使用 begin...end 包含多条语句
可用于存储过程、触发器等模块中
支持变量声明与赋值
属于持久存储模块：代码可以保存在数据库中并长期使用

循环语句

while 循环

declare n integer default 0;
while n < 10 do
    set n = n + 1;
end while;
-- 当满足条件时重复执行

repeat 循环

declare n integer default 10;
repeat
    set n = n - 1;
until n = 0
end repeat;
-- 先执行，后判断，直到满足条件为止

for 循环

declare n integer default 0;
for r as select balance from account
where branch name = 'Perryridge'
do
	set n = n + r.balance;
end for

分支语句

if r.balance < 1000 then
    set l = l + r.balance;
elseif r.balance < 5000 then
    set m = m + r.balance;
else
    set h = h + r.balance;
end if;

复合语句

SQL 很多地方要求只能写一条语句，但是一般的逻辑往往是要写很多句语句的，此时就可以用 BEGIN...END 来组合它们，把它变成一条语句。

相当于大括号 {} 的用法。

begin
    -- 声明变量、赋值、条件判断、循环等都可以写在这里
end;

递归查询

递归查询用于查询结构层次化 / 多层依赖数据。任何递归查询都由两部分你组成：

基查询
递归查询

使用UNION连接。

例：找出员工的所有上级

WITH RECURSIVE empl(employee_name, manager_name) AS (
    -- 基查询：直接管理关系
    SELECT employee_name, manager_name FROM manager

    UNION

    -- 递归查询：间接管理关系
    SELECT m.employee_name, e.manager_name
    FROM manager m, empl e
    WHERE m.manager_name = e.employee_name
)

SELECT * FROM empl;

LLM解释：

引用这段 SQL 代码使用了递归公用表表达式 (Recursive Common Table Expression, 简称 Recursive C...

这段 SQL 代码使用了 递归公用表表达式 (Recursive Common Table Expression, 简称 Recursive CTE)。它的核心目的是为了处理层级结构（如公司组织架构、家族树、文件目录等），找出所有的直接和间接管理关系。

我们可以将这段代码拆解为三个主要部分：基查询 (Anchor)、递归查询 (Recursive) 和 终止条件。

1. 结构化解析

A. 定义部分 (WITH RECURSIVE empl(...))

这一行定义了一个虚拟的临时表 empl。它有两个字段：employee_name（员工）和 manager_name（管理者）。

B. 基查询 (Anchor Member)

SQL
SELECT employee_name, manager_name FROM manager
作用：这是递归的起点。它先从原始表 manager 中把所有直接汇报关系（第一层）取出来。

结果：比如表中有一行 (张三, 李四)，代表李四是张三的直接上级。

C. 递归查询 (Recursive Member)

SQL
SELECT m.employee_name, e.manager_name
FROM manager m, empl e
WHERE m.manager_name = e.employee_name
逻辑：它将原始表 manager (m) 与我们正在生成的临时表 empl (e) 进行连接。

连接条件：m.manager_name = e.employee_name。

意思是：如果 A 的上级是 B，而 B 又是 C 的下属，那么 A 也是 C 的下属。

作用：每一轮递归都会在上一层的基础上再向上寻找一级。第一轮找的是“上级的上级”，第二轮找“上级的上级的上级”，以此类推。

D. 最终查询

SQL
SELECT * FROM empl;
将所有层级的结果（基查询的结果 + 所有递归轮次的结果）合并后输出。

2. 它是如何运作的？（举个例子）

假设 manager 表数据如下：

employee_name manager_name

小王老李

老李老张

老张大老板

递归执行过程：

第一步 (基查询)：

找出直接关系：(小王, 老李), (老李, 老张), (老张, 大老板)。

第二步 (第一轮递归)：

系统发现：小王的上级是“老李”，而“老李”又是“老张”的下属。

结果增加：(小王, 老张)。

同理增加：(老李, 大老板)。

第三步 (第二轮递归)：

系统发现：小王现在的间接上级有“老张”，而“老张”又是“大老板”的下属。

结果增加：(小王, 大老板)。

第四步 (结束)：

没有新的管理关系可以推导，递归停止。

3. 这个查询的意义

如果没有递归查询，你只能查到“谁是谁的直接主管”。如果你想查“谁是这个员工的所有上级（包括爷爷辈、曾爷爷辈的领导）”，在传统 SQL 中会非常麻烦。

总结该查询的功能：

展平层级：将树状结构转变为路径列表。

全路径搜索：无论公司的管理层级有多深（5层还是50层），这个查询都能自动追踪到顶端。

提示

在某些数据库（如 MySQL 8.0+ 或 PostgreSQL）中，递归查询需要注意避免死循环。如果 A 管 B，B 又管 A，递归就会无限进行下去。生产环境下通常会加入 DEPTH（深度）限制或检测循环的逻辑。

employee_name	manager_name
小王	老李
老李	老张
老张	大老板

高级聚合特性

窗口函数 Window Functions

这一块其实不是按照课本顺序来的，是看CMU-15-445网课知道的知识点，但是我觉得很重要。

格式：

SELECT ARRTTRIBUTES... FUNC-NAME(...) OVER(...) FROM tableName

FUNC-NAME()里面可以是Aggregation Functions 也可以是 Special Functions；所谓的 Special Functions 就是 ROW_NUMBER()和RANK()。

OVER()里面填写PARTITION BY或者ORDER BY

有窗口函数的SELECT语句不能直接使用WHERE语句。

我们举一个例子：“找出每个系中 GPA 最高的一名学生”。

假设我们有两张表：

student (sid, name, dept_id)
enrolled (sid, gpa)

SELECT name, dept_id, gpa
FROM (
    SELECT s.name, s.dept_id, e.gpa,
           RANK() OVER (PARTITION BY s.dept_id ORDER BY e.gpa DESC) as rnk
    FROM student AS s
    JOIN enrolled AS e ON s.sid = e.sid
) AS ranked_students
WHERE rnk = 1;

窗口函数的性能很优秀。

课程笔记 > 数据库

#数据库 #SQL #并发 #事务

数据库第一次小测复习上一篇

CS168 SP25 Project2 Distance-Vector Routing笔记下一篇

数据库系统概念 - 笔记1

Chapter 1 Intro

什么是DBMS

文件系统的缺陷 / Purpose of Database Systems

View of Data / 数据视角

数据抽象

数据模型

实例与模式

数据库语言 Database Languages

Database Users and Adminstrators

History

Chapter 2 Introduction to the Relational Model

关系数据库的结构

表

关系模式 Schema

关系实例

tuple 元组

attribute 属性

Key 码 / 键

super key

candidate key

primary key

foreign key

schema diagram 模式图

Relational Algebra

Chapter 3 Introduction to SQL

Overview of the SQL

Data Defination 数据定义

基础数据类型 Basic Types

Basic Schema Defination

integrity constraints 完整性约束

创建关系模式

删除关系模式

修改关系模式

数据查询 SQL Queries

一套完整的SQL查询语句：

AS关键字

字符串

CASE

LIKE运算符

拼接字符串

大小写转换

数据重复问题

多关系查询

where里面的谓词

元组解包 / 匹配

集合运算

对于null的处理

Aggregate Functions

Nested Subqueries

用于实现集合运算

some all exists unique

Subqueries in the From Clause

with子查询

Scalar Subqueries

Modification of the Database

deletion

insertion

updates

Chapter 4 Intermediate SQL

Join Expressions 再探

natural join

Join Conditions

outer join

Views 视图

Transaction 事务

integrity constraints 完整性约束

单个关系上的约束

Referential Integrity 参照完整性约束

默认引用 (Implicit Reference)

显式引用 (Explicit Reference)

Assigning Names to Constraints

Assertion 断言

高级数据类型

内建数据类型

大对象类型 Big Object Types

用户定义类型

类型转换 cast

空值合并 conalesce

decode

`AS`关键字

`CASE`

`LIKE`运算符

A. 定义部分 (`WITH RECURSIVE empl(...)`)