第三章关系数据库

关系模型概述

关系数据结构

关系数据模型源于数学。
用二维表来组织数据，这个二维表在关系数据库中就称为关系。
关系数据库就是表或者说是关系的集合。
关系系统要求让用户所感觉的数据库就是一张张表。
在关系系统中，表是逻辑结构而不是物理结构。

关系操作

传统的关系运算

并（Union）
交（Intersection）
差（Difference）
广义笛卡尔乘积（Extended Cartesian Product）

专门的关系运算

选择（Select）
投影（Project）
连接（Join）
除（Divide）

关系模型的数据操作

主要包括：查询、插入、删除和修改数据。
是基于集合的操作，操作对象和操作结果都是集合（或关系）。
是非过程化的。

关系模型的数据操作非过程化是指在关系型数据库管理系统（RDBMS）中进行数据操作时，用户无需关心数据操作的具体实现过程，而只需要描述需要操作的数据和所期望的结果。这种方式与过程化操作（例如编写详细的操作步骤和控制流）有所不同，强调的是“做什么”而不是“如何做”。

数据完整性约束

数据完整性是指数据库中存储的数据是有意义的或正确的。
主要包括三大类：
- 实体完整性
- 参照完整性
- 用户定义的完整性

关系数据模型的基本术语与形式化定义

关系数据模型的基本术语

关系：关系就是二维表。并满足如下性质：
- 关系表中的每一列都是不可再分的基本属性；
- 表中的行、列次序并不重要。
属性：表中的每一列是一个属性值集，列可以命名，称为属性名。
值域：属性的取值范围。如，性别只能是‘男’和‘女’两个值。
元组：表中的每一行称作是一个元组，它相当于一个记录值。
分量：元组中的每一个属性值称为元组的一个分量，n元关系的每个元组有n个分量。
关系模式：关系模式是关系的“型”或元组的结构共性的描述。关系模式实际上对应关系表的表头。设关系名为R，属性分别为A1，A2，…，An，则关系模式可以表示为：R（A1，A2，…，An）
关系数据库：对应于一个关系模型的所有关系的集合称为关系数据库。
候选码：能够唯一标识关系中的一个元组的一个属性或最小属性组。
主码：指定候选码中的一个作为主码。
主属性：包含在任一候选码中的属性称为是主属性。
非主属性：不包含在任一候选码中的属性称为是非主属性。
外码：如果某个属性不一定是所在关系的码，但是其他关系的码，则称该属性为外码。

关系数据结构及其形式化定义

关系的形式化定义

笛卡尔积：设 $D_1, D_2, \cdots, D_n$ 为任意集合，定义笛卡尔积 $D_1, D_2, \cdots, D_n$ 为： $D_1 × D_2 × … × D_n = {(d_1, d_2, \cdots, d_n) | d_i ∈ D_i, i = 1, 2, \cdots, n }$ 其中每一个元素 $(d_1,d_2, \cdots, d_n)$ 称为一个n元组，简称元组。元组中每一个 $d_i$ 称为是一个分量。
笛卡尔积 $D_1, D_2, \cdots, D_n$ 的任意一个子集称为 $D_1, D_2, \cdots, D_n$ 上的一个n元关系。

首先，笛卡尔积是指两个或多个集合之间的运算。给定集合 $𝐷_1, 𝐷_2, \cdots, 𝐷_𝑛$ ，它们的笛卡尔积表示的是所有可能的有序元组，每个元组的第 𝑖 个元素来自于集合 $𝐷_𝑖$ 。数学上，笛卡尔积可以表示为： $𝐷_1 × 𝐷_2 × \cdots × 𝐷_𝑛 = \{(𝑑_1, 𝑑_2, \cdots, 𝑑_𝑛)∣𝑑_𝑖 ∈ 𝐷_𝑖, ∀𝑖\}$ . 例如，如果 $D_1 = \{1,2\}, D_2 = \{a, b\}$ ，那么它们的笛卡尔积就是所有可能的有序元组： $D_1 × D_2 = \{(1,a),(1,b),(2,a),(2,b)\}$ .
一个n元关系是指从一个笛卡尔积中选出的一个子集。例如，在上面的笛卡尔积中，一个子集如 $\{(1,a),(2,b)\}$ 就是一个2元关系，表示了来自集合 $D_1$ 和 $D_2$ 的某些特定组合。
形式化的关系定义同样可以把关系看成二维表，给表的每个列取一个名字，称为属性。
n元关系有n个属性，一个关系中的属性的名字必须是唯一的。
属性 $D_i$ 的取值范围（i＝1，2，…，n）称为该属性的值域（domain）。
从集合论的观点也可以将关系定义为：关系是一个有K个属性的元组的集合。

对关系的限定

关系中的每个分量都必须是不可再分的最小数据项。
表中列的数据类型是固定的，即每个列中的分量是同类项的数据，来自相同的值域。
不同的列的数据可以取自相同的值域，每个列称为一个属性，每个属性有不同的属性名。
关系表中行、列的顺序不重要。
同一个关系中元组不能重复。

关系模型的完整性约束

实体完整性

实体完整性是保证关系中的每个元组都是可识别的和唯一的。
而且表中不允许存在如下的记录：
- 无主码值的记录
- 主码值相同的记录

参照完整性

用于描述实体之间的联系。

实体与表：在关系型数据库中，表用来存储实体的实例，表中的每一行是实体的一个实例（例如一个学生），每一列是实体的一个属性（例如学生的姓名、学号等）。
在很多情况下，一个实体可以被映射为一张表，这种映射关系是数据库设计中的常见做法。具体来说：
- 实体的每个属性通常对应表中的一个字段（列）。
- 实体的每个实例通常对应表中的一行（记录）。
- 实体的主键通常对应表中的唯一标识字段（通常是主键）。
参照完整性一般是指多个实体（表）之间的关联关系。
一般用外码实现。
外码：取作本表（子表）属性之一的外表（父表，主表）主码。
主码值先在主表中生成，后在子表中引用。
外码引用例：
参照完整性规则就是定义外码与主码之间的引用规则。
对于外码，一般应符合如下要求：
- 或者值为空；
- 或者等于其所应用的关系中的某个元组的主码值。

用户定义的完整性

按应用语义，属性数据有：
- 类型与长度限制：方便计算机操作
- 取值范围限制：防止属性值与应用语义矛盾
- 语义许可取值范围约束例：
  - 成绩取
    1. [0 .. 100],
    2. {优、良、中、及格、不及格}

关系代数

关系模型源于数学，关系是由元组构成的集合，可以通过关系的运算来表达查询要求。
关系代数是关系操作语言的一种传统的表示方式，它是一种抽象的查询语言。
关系代数的运算可分为两大类：
- 传统的集合运算：广义笛卡尔积运算、并、交和差运算。
- 专门的关系运算：选择、投影、连接和除运算。

关系运算符

传统的集合运算

传统的集合运算是二目运算，

二目运算指的是一个操作需要两个输入值的运算，这些输入值可以是数字、布尔值或其他类型的数据。
设关系R和S均是n元关系，且相应的属性值取自同一个值域，则可以定义三种运算：
- 并运算（∪）
  - 关系R与关系S的并记为：
    - R∪S＝{t | t∈R ∨t∈S }
  - 其结果仍是n目关系，由属于R或属于S的元组组成。
- 交运算（∩）
  - 关系R与关系S的交记为：
    - $R ∩ S ＝ \{ \, t \, | \, t ∈ R \,\; ∧ \, t ∈ S \}$
  - 其结果仍是n目关系，由属于R并且也属于S的元组组成。
- 差运算（―）
  - 关系R与关系S的差记为：
    - $R － S ＝ \{ \, t \, | \, t ∈ R \,\; ∧ \, t \notin S\}$
  - 其结果仍是n目关系，由属于R但不属于S的元组组成。
广义笛卡尔积
- 两个分别为n目和m目的关系R和关系S的广义笛卡尔积是一个（m＋n）列的元组的集合。
- 元组的前n个列是关系R的一个元组，后m个列是关系S的一个元组。
- 若R有K1个元组，S有K2个元组，则关系R和关系S的广义笛卡尔积有K1×K2个元组，记做： $R × S ＝ \{t_r \text{\textasciicircum} t_s | t_r \in R ∧ t_s \in S \}$
  $t_r \text{\textasciicircum} t_s$ 表示由两个元组 $t_r$ 和 $t_s$ 前后有序连接而成的一个元组。

专门的关系运算

专门的关系运算包括：选择、投影、连接和除等操作，其中第一个为一元操作，后三者为二元操作。

选择

$σ_{F}（R）＝ \{ r | r∈R ∧ F（t）＝‘真’ \}$
其中：σ是选择运算符，R是关系名，r是元组，F是逻辑表达式，取逻辑“真”值或“假”值。
例，选择系为计算机系的学生信息： $σ_{Sdept＝‘计算机系’}（学生表）$

投影

$∏_A（R）＝ \{ r.A | r∈R \}$
其中：
- ∏是投影运算符，
- R是关系名，
- A是被投影的属性或属性组。
- r.A表示r这个元组中相应于属性（集）A的分量，也可以表示为r[A]。
例如，选择sname，sdept两个列构成新关系：
- $∏_{sname, sdept}（Student）$

连接

其中：
- A和B分别是关系R和S上可比的属性组，
- θ是比较运算符，
- 连接运算从R和S的广义笛卡尔积R×S中选择（R关系）在A属性组上的值与（S关系）在B属性组上值满足比较运算符θ的元组。
等值连接
- 当θ为“＝”时的连接为等值连接，
- 是从关系R与关系S的广义笛卡尔积中选取A，B属性值相等的那些元组。
自然连接
- 自然连接去掉结果中的重复列。
- 自然连接与等值连接的差别为：
  - 自然连接要求相等的分量必须有共同的属性名，等值连接则不要求；
  - 自然连接要求把重复的属性名去掉，等值连接却不这样做。
除（Division）
- （1）除法的简单形式
  - 设关系S的属性是关系R的属性的一部分，则R÷S为这样一个关系：
  - 此关系的属性是由属于R但不属于S的所有属性组成；
  - R÷S的任一元组都是R中某元组的一部分。但必须符合下列要求，即任取属于R÷S的一个元组t，则t与S的任一元组连接后，都为R中原有的一个元组。
- （2）除法的一般形式
  - 设有关系R（X，Y）和S（Y，Z），其中X、Y、Z为关系的属性组，则：
    - $R（X,Y）÷ S（Y,Z）＝R（X,Y）÷ ∏_Y（S）$
- 象集：给定一个关系R（X，Y），X和Y为属性组。定义，当t［X］＝x时，x在R中的象集为：
  - $Y_x =\{ t[Y] | t \in R ∧ t[X]=x \}$
  - 上式中： $t[Y]$ 和 $t[X]$ 分别表示R中的元组t 在属性组Y和X上的分量的集合。
- 除法的一般形式：
- 设有关系R（X，Y）和S（Y，Z），其中X、Y、Z为关系的属性组，则：
  - $R÷S＝ \{t_r[X] | t_{r} \in R ∧ ∏_{Y}(S) = Y_x \}$
查询修c02号课程的学生的学号和成绩。
$∏_{sno, grade}(σ_{cno=‘c02’}(SC))$
查询计算机系修c02号课程的学生的姓名和成绩。
$∏_{sname, grade}(σ_{cno=‘c02’}(SC) \bowtie σ_{sdept=‘计算机系’}(Student))$
查询修了第2学期课程的学生的姓名和所在系。
$∏_{sname, sdept}(σ_{semester=2}(Course) \bowtie SC \bowtie Student)$
查询修了全部课程的学生的学号和姓名。
$∏_{sno, sname}(Student \bowtie (SC ÷ ∏_{cno}(Course)))$

第4章 SQL语言基础

基本概念

SQL语言的发展

1986年10月由美国ANSI 公布最早的SQL标准。
1989年4月，ISO提出了具备完整性特征的SQL，称为SQL-89 。
1992年11月，ISO又公布了新的SQL标准，称为SQL-92（以上均为关系形式）。
1999年颁布SQL-99，是SQL92的扩展。

SQL语言的特点

一体化。

一体化指的是SQL语言提供了一个统一的接口来进行数据库的各种操作。无论是数据的查询、插入、更新、删除，还是数据库的结构设计、权限管理等，SQL都可以在一个统一的框架下完成。这种统一性意味着开发者不需要了解底层的数据库实现细节，而只需专注于如何表达自己的需求。
举个例子，在SQL中，查询、插入和更新操作有统一的语法结构，开发者可以通过简单的SQL语句实现复杂的数据操作，不需要涉及底层的技术细节。
高度非过程化。

SQL是非过程化的，即在SQL中，开发者不需要关心数据操作的具体执行过程，只需要说明”做什么”（例如查询、更新、删除），而不需要明确告诉数据库”如何做”。
非过程化意味着你只需描述你想要的结果，数据库系统会根据内部优化器自动决定最合适的执行计划。例如，执行一个查询时，SQL不要求你指定数据是如何检索的（比如先查哪个表，如何连接等），这些由数据库系统自动完成。
简洁。

SQL语言非常简洁，使用自然语言的结构来描述数据操作。SQL的语法是基于英语的，使得它容易理解和使用。由于SQL是声明式语言，开发者只需要描述查询的目的，而无需关注实现细节。通过简洁的语法，SQL可以在非常短的代码行内表达复杂的数据库操作。
声明式语言（Declarative Language）是一类编程语言，其主要特点是程序员只需要描述想要做什么，而不需要详细地描述如何去做。也就是说，在声明式语言中，程序员关注的是结果而不是实现过程。
使用方式多样。

SQL不仅用于查询数据，还可以用于多种数据库操作和管理任务。它支持数据定义（创建表、修改表结构等）、数据操作（插入、更新、删除等）、数据控制（权限管理等）等功能。

SQL语言功能概述

SQL的数据类型

数值型

准确型
- 整数
  Bigint: 8字节， Int：4字节
  Smallint：2字节， Tinyint：1字节
  Bit：1位，存储1或0
- 小数
  Numeric（p,q）或Decimal（p,q），
  其中：p为数字位长度，q：小数位长度。
近似型
Float：8字节
Real：4字节

字符串型

普通编码字符串类型

这些类型通常使用数据库的默认字符集（如 ASCII 或其他单字节编码）进行存储，因此每个字符通常占用 1个字节。它们适用于不需要多语言支持、主要处理英文字符或其他单字节字符的场景。

Char（n）:定长存储，n<=8000
Varchar（n）：不定长存储（按实际长度存储），长度最大不超过n , n<=8000

注：n 为字符个数
Text：存储大于8000字节的文本

统一字符编码字符串类型

nchar（n）：定长存储，n<=4000
nvarchar（n）：不定长存储，长度最大不超过n , n<=4000
ntext：存储大于8000字节的文本

特点：每个字符占两个字节

二进制字符串类型

Binary(n)：固定长度，n <= 8000。
Varbinary(n)：可变长度，n <＝8000 。

注：n为二进制数据的字节数
image：大容量、可变长二进制字符数据，可用于存储文件。

日期时间型

Datetime：8字节，年月日时分秒毫秒
（例：‘2001/08/03 10:30:00.000’ ）
SmallDateTime：4字节，年月日时分
（例： ‘2001/08/03 10:30:00’ ）
日期、时间的输入格式

货币型

Money： 8 个字节，精确到货币单位的千分之十。
Smallmoney： 4 个字节，精确到货币单位的千分之十。
限制到小数点后 4 位。
可以带有适当的货币符号。例如，100 英镑可表示为 £100。

基本表的定义、删除及修改

基本表的定义与删除

定义基本表
使用SQL语言中的CREATE TABLE语句实现，其一般格式为：

CREATE  TABLE  <表名>（
    <列名>  <数据类型>  [列级完整性约束定义]
{,  <列名>  <数据类型> 
    [列级完整性约束定义］ … }
[, 表级完整性约束定义 ] ）

在列级完整性约束定义处可以定义的约束
- NOT NULL：限制列取值非空。
- DEFAULT：给定列的默认值。
- UNIQUE：限制列取值不重。
- CHECK：限制列的取值范围。
- PRIMARY KEY：指定本列为主码。
- FOREIGN KEY：定义本列为引用其他表的外码。使用形式为：
- [FOREIGN KEY(<外码列名>)] REFERENCES <外表名>（<外表列名>）
几点说明
- NOT NULL和DEFAULT只能是列级完整性约束；
- 其他约束均可在表级完整性约束处定义。
- 注意以下几点：
  - 第一，如果CHECK约束是定义多列之间的取值约束，则只能在表级完整性约束处定义；
  - 第二，如果表的主码由多个列组成，则也只能在表级完整性约束处定义，并将主码列用括号括起来，即：PRIMARY KEY（列1｛[，列2 ] …｝）；
  - 第三，如果在表级完整性约束处定义外码，则“FOREIGN KEY (<外码列名>)”部分不能省。
约束说明
① 列取值非空约束
<列名> <类型> NOT NULL
例：
1
sname char(10) NOT NULL
② 表主码约束
在定义列时定义主码（仅用于单列主码）
列定义 PRIMARY KEY
例：
1
SNO char(7) PRIMARY KEY
在定义完列时定义主码（用于单列或多列主码）
PRIMARY KEY （<列名序列>）
例：
1
2
PRIMARY KEY(SNO)
PRIMARY KEY(SNO，CNO)
③ 外码引用约束
指明本表外码列引用的表及表中的主码列。
[ FOREIGN KEY （<本表列名>）]
REFERENCES <外表名>（<外表主码列名>）
例：
1
2
FOREIGN KEY （sno）
REFERENCES 学生表（sno）
④ 默认值约束
格式：DEFAULT 默认值
例：
定义系的默认值为“计算机系”。
1
DEFAULT ‘计算机系’
⑤ CHECK约束
格式：CHECK (约束表达式)
例：
定义成绩大于等于0。
1
CHECK ( grade >= 0 )
⑥ UNIQUE约束
在列级约束定义（仅用于单列约束）
列定义 UNIQUE
例：
1
SNAME char(7) UNIQUE
在表级约束定义（用于单列或多列组合约束）
UNIQUE （<列名序列>）
例：
1
UNIQUE (SNO，CNO)

创建学生表

CREATE TABLE Student (
  Sno   char ( 7 )  PRIMARY KEY,
  Sname char ( 10 ) NOT NULL,
  Ssex  char (2)  
        CHECK (Ssex = '男' OR Ssex = '女'), 
  Sage  tinyint  
        CHECK (Sage >= 15 AND Sage <=45),
  Sdept char (20 ) DEFAULT '计算机系'
)

创建课程表

CREATE TABLE Course (
  Cno    char(10)  NOT NULL,
  Cname  char(20)  NOT NULL,
  Ccredit  tinyint CHECK (Ccredit > 0),
  Semester tinyint CHECK (Semester > 0),
  Period  int CHECK (Period > 0),
  PRIMARY KEY(Cno) 
)

创建SC(Student-Course)表

CREATE TABLE SC (
  Sno char(7)  NOT NULL,
  Cno char(10) NOT NULL,
  Grade  tinyint,
      CHECK (Grade >= 0 and Grade <= 100),
  PRIMARY KEY ( Sno, Cno ),
  FOREIGN KEY ( Sno )  
      REFERENCES  Student ( Sno ),
  FOREIGN KEY ( Cno )  
      REFERENCES  Course ( Cno ) 
)

删除表

当确信不再需要某个表时，可以将其删除
删除表时会将与表有关的所有对象一起删掉，包括表中的数据。
删除表的语句格式为：
DROP TABLE <表名> { [, <表名> ] … }
例：
删除test表的语句为：
1
DROP TABLE test

修改表结构

在定义完表之后，如果需求有变化，比如添加列、删除列或修改列定义，可以使用ALTER TABLE语句实现。
ALTER TABLE语句可以对表添加列、删除列、修改列的定义、定义主码、外码，也可以添加和删除约束。
修改表结构语法

ALTER TABLE <表名>  
  [ ALTER COLUMN <列名> <新数据类型>] 
| [ ADD [COLUMN] <列名> <数据类型> 
| [ DROP COLUMN <列名>  ] 
| [ ADD PRIMARY KEY(列名 [，… n ] )] 
| [ ADD FOREIGN KEY(列名)
   REFERNECES 表名 (列名)]

示例
例2．为SC表添加“修课类别”列，此列的定义为：XKLB char(4)
1
2
ALTER TABLE SC
ADD XKLB char(4) NULL
例3．将新添加的XKLB的类型改为：char(6)。
1
2
ALTER TABLE SC
ALTER COLUMN XKLB char(6)
例4．删除Course表的Period列。
1
2
ALTER TABLE Course
DROP COLUMN Period

数据查询功能

查询语句的基本结构

SELECT <目标列名序列>    --需要哪些列
  FROM <数据源>              --来自于哪些表
 [WHERE <检索条件>]       --根据什么条件
 [GROUP BY <分组依据列>]      
 [HAVING <组提取条件>]
 [ORDER BY <排序依据列>]

简单查询

选择表中若干列

查询指定的列

查询表中用户感兴趣的部分属性列。
例5：查询全体学生的学号与姓名。
1
SELECT Sno，Sname FROM Student
例6：查询全体学生的姓名、学号和所在系。
1
2
SELECT Sname，Sno，Sdept
FROM Student

查询全部列
例7．查询全体学生的记录

1 2	SELECT Sno，Sname，Ssex, Sage, Sdept FROM Student

等价于：

1	SELECT * FROM Student

查询经过计算的列
例8．查询全体学生的姓名及其出生年份。
1
2
SELECT Sname, 2006 - Sage
FROM Student
例9．查询全体学生的姓名和出生年份所在系，并在出生年份列前加入一个列，此列的每行数据均为`出生年份`常量值。
1
2
SELECT Sname，`出生年份`, 2006-Sage
FROM Student
列别名(Alias)

在 SELECT 语句中，你可以使用 列别名（Alias） 给查询结果的列起一个更直观的名称，方便阅读。

语法：
列名 | 表达式 [ AS ] 列标题
或：
列标题 = 列名 | 表达式
例：

1 2	SELECT Sname AS 姓名, `Year of Birth` AS 出生年份, 2006 - Sage AS 年份 FROM Student

或：

1 2	SELECT 姓名 = Sname, 出生年份 = `Year of Birth`, 年份 = 2006 - Sage FROM Student;

选择表中若干元组

消除取值相同的记录
例10．在修课表中查询有哪些学生修了课程，要求列出学生的学号。
1
SELECT Sno FROM SC

结果中有重复的行。
用DISTINCT关键字可以去掉结果中的重复行。
DISTINCT关键字放在SELECT词的后边、目标列名序列的前边。
1
SELECT DISTINCT Sno FROM SC

查询满足条件的元组

比较大小
例11．查询计算机系全体学生的姓名。

1 2	SELECT Sname FROM Student WHERE Sdept = '计算机系'

例12．查询年龄在20岁以下的学生的姓名及年龄。

1 2	SELECT Sname, Sage FROM Student WHERE Sage < 20

例13．查询考试成绩有不及格的学生的学号

1 2	SELECT DISTINCT Sno FROM SC WHERE Grade < 60

确定范围

用BETWEEN…AND和NOT BETWEEN…AND
是逻辑运算符，可以用来查找属性值在或不在指定范围内的元组，其中BETWEEN后边指定范围的下限，AND后边指定范围的上限。
BETWEEN…AND…的格式为：
列名 | 表达式 [ NOT ] BETWEEN 下限值 AND 上限值
如果列或表达式的值[在/不在]下限值和上限值范围内，则结果为True，表明此记录符合查询条件。

示例
例14．查询年龄在20～23岁之间的学生的姓名、所在系和年龄。

1 2	SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23

例15．查询年龄不在20～23之间的学生姓名、所在系和年龄。

1 2	SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23

确定集合

使用IN运算符。
用来查找属性值属于指定集合的元组。
格式为：
列名 [ NOT ] IN （常量1, 常量2, … 常量n）
当列中的值与IN中的某个常量值相等时，则结果为True，表明此记录为符合查询条件的记录；
NOT IN：当列中的值与某个常量值相同时，则结果为False，表明此记录为不符合查询条件的记录

示例
例16．查询信息系、数学系和计算机系学生的姓名和性别。

1 2	SELECT Sname, Ssex FROM Student WHERE Sdept IN ('信息系', '数学系', '计算机系')

例17．查询既不是信息系、数学系，也不是计算机系学生的姓名和性别。

1 2	SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ('信息系', '数学系', '计算机系')

使用LIKE运算符
一般形式为：
列名 [NOT] LIKE <匹配串>
匹配串中可包含如下四种通配符：
- _：匹配任意一个字符；
- %：匹配0个或多个字符；
- [ ]：匹配[ ]中的任意一个字符；
- [ ^ ]：不匹配[ ]中的任意一个字符
  
  例：[^0-9]匹配任何不是数字的字符。
示例
例18．查询姓‘张’的学生的详细信息。
1
2
SELECT * FROM Student
WHERE Sname LIKE '张%'
例19．查询学生表中姓‘张’、‘李’和‘刘’的学生的情况。
1
2
SELECT * FROM Student
WHERE Sname LIKE ' [张李刘]%'
例20．查询名字中第2个字为‘小’或‘大’的学生的姓名和学号
1
2
SELECT Sname, Sno FROM Student
WHERE Sname LIKE '_[小大]%'
例21．查询所有不姓“刘”的学生。
1
2
SELECT Sname FROM Student
WHERE Sname NOT LIKE '刘%'
例22．查询学号的最后一位不是2、3、5的学生情况。
1
2
SELECT * FROM Student
WHERE Sno LIKE '%[^235]'
空值（NULL）在数据库中表示不确定的值。
例如，学生选修课程后还没有考试时，这些学生有选课记录，但没有考试成绩，因此考试成绩为空值。
判断某个值是否为NULL值，不能使用普通的比较运算符。
判断取值为空的语句格式为：
列名 IS NULL
判断取值不为空的语句格式为：
列名 IS NOT NULL
例23．查询无考试成绩的学生的学号和相应的课程号。
1
2
SELECT Sno, Cno FROM SC
WHERE Grade IS NULL
例24．查询所有有考试成绩的学生的学号和课程号。
1
2
SELECT Sno, Cno FROM SC
WHERE Grade IS NOT NULL
在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。
- 用AND连接的条件表示必须全部满足所有的条件的结果才为True；
- 用OR连接的条件表示只要满足其中一个条件结果即为True。
  
  AND 的优先级高于 OR。

例25．查询计算机系年龄在20岁以下的学生姓名。

1 2	SELECT Sname FROM Student WHERE Sdept = 'CS' AND Sage < 20

对查询结果进行排序

可对查询结果进行排序。
排序子句为：
ORDER BY <列名> [ASC | DESC ] [,<列名> … ]
说明：按<列名>进行升序（ASC）或降序（DESC）排序。
示例
例26．将学生按年龄的升序排序。
1
SELECT * FROM Student ORDER BY Sage
例27．查询选修了‘c02’号课程的学生的学号及其成绩，查询结果按成绩降序排列。
1
2
SELECT Sno, Grade FROM SC
WHERE Cno='c02' ORDER BY Grade DESC
例28．查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。
1
2
SELECT * FROM Student
ORDER BY Sdept, Sage DESC

使用计算函数汇总数据

SQL提供的计算函数有：
- COUNT（ * ）：统计表中元组个数；
- COUNT（[DISTINCT] <列名> ）：统计本列列值个数；
- SUM（[DISTINCT] <列名> ）：计算列值总和；
- AVG（[DISTINCT] <列名> ）：计算列值平均值；
- MAX（[DISTINCT] <列名> ）：求列值最大值；
- MIN（[DISTINCT] <列名> ）：求列值最小值。
上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。
示例
例29．统计学生总人数。
1
SELECT COUNT(*) FROM Student
例30．统计选修了课程的学生的人数。
1
2
SELECT COUNT(DISTINCT Sno)
FROM SC
例31．计算9512101号学生的考试总成绩之和。
1
2
SELECT SUM(Grade) FROM SC
WHERE Sno = '9512101'
例32．计算’C01’号课程学生的考试平均成绩。
1
2
SELECT AVG(Grade) FROM SC
WHERE Cno='C01'
例33．查询选修了’C01’号课程的学生的最高分和最低分。
1
2
SELECT MAX(Grade) , MIN(Grade)
FROM SC WHERE Cno='C01'
注意：计算函数不能出现在WHERE子句中

对查询结果进行分组计算

作用：可以控制计算的级别：对全表还是对一组。
目的：细化计算函数的作用对象。
分组语句的一般形式：
- [GROUP BY <分组条件>]
- [HAVING <组过滤条件>]

使用GROUP BY
例34．统计每门课程的选课人数，列出课程号和人数。

1
2
3

SELECT Cno as 课程号, 
  COUNT(Sno) as 选课人数 
FROM SC GROUP BY Cno

对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求得每组的学生人数。例35．查询每名学生的选课门数和平均成绩。
1
2
3
4
SELECT Sno as 学号,
COUNT(*) as 选课门数,
AVG(Grade) as 平均成绩
FROM SC GROUP BY Sno
HAVING用于对分组自身进行限制，它有点像WHERE子句，但它用于组而不是对单个记录。
例36．查询修了3门以上课程的学生的学号
1
2
3
SELECT Sno FROM SC
GROUP BY Sno
HAVING COUNT(*) > 3

示例
例37．查询修课门数等于或大于4门的学生的平均成绩和选课门数。

SELECT Sno, AVG(Grade) 平均成绩, 
  COUNT(*) 修课门数
  FROM SC 
  GROUP BY Sno 
  HAVING COUNT(*) >= 4

多表连接查询

若一个查询同时涉及两个或两个以上的表，则称之为连接查询。
连接查询是关系数据库中最主要的查询。
连接查询包括内连接、外连接和交叉连接等。
连接查询中用于连接两个表的条件称为连接条件或连接谓词。
一般格式为：
[<表名1.>][<列名1>] <比较运算符> [<表名2.>][<列名2>]
内连接

SQL-92 内连接语法如下：

SELECT …
  FROM 表名 [INNER] JOIN
  被连接表
  ON  连接条件

执行连接操作的过程：
- 首先取表1中的第1个元组，然后从头开始扫描表2，逐一查找满足连接条件的元组，
- 找到后就将表1中的第1个元组与该元组拼接起来，形成结果表中的一个元组。
- 表2全部查找完毕后，再取表1中的第2个元组，然后再从头开始扫描表2， …
- 重复这个过程，直到表1中的全部元组都处理完毕为止。
  例40.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。
  1
  2
  3
  4
  SELECT Sname, Cno, Grade
  FROM Student JOIN SC
  ON Student.Sno = SC.Sno
  WHERE Sdept = '计算机系'
  例41. 查询信息系修了VB课程的学生的修课成绩，要求列出学生姓名、课程名和成绩。
  1
  2
  3
  4
  5
  6
  SELECT Sname, Cname, Grade
  FROM Student s JOIN SC
  ON s.Sno = SC. Sno
  JOIN Course c ON c.Cno = SC.Cno
  WHERE Sdept = '信息系'
  AND Cname = 'VB'
自连接
- 为特殊的内连接
- 相互连接的表物理上为同一张表。
- 必须为两个表取别名，使之在逻辑上成为两个表。
  例43. 查询与刘晨在同一个系学习的学生的姓名和所在的系。
  1
  2
  3
  4
  5
  SELECT S2.Sname, S2.Sdept
  FROM Student S1 JOIN Student S2
  ON S1.Sdept = S2.Sdept
  WHERE S1.Sname = ‘刘晨’
  AND S2.Sname != ‘刘晨’

外连接

只限制一张表中的数据必须满足连接条件，而另一张表中数据可以不满足连接条件。

ANSI方式的外连接的语法格式为：

1 2	FROM 表1 LEFT \| RIGHT [OUTER] JOIN 表2 ON <连接条件>

theta方式的外连接的语法格式为：
左外连接：

1	FROM 表1, 表2 WHERE [表1.]列名(+) ＝ [表2.]列名

右外连接：

1	FROM 表1, 表2 WHERE [表1.]列名＝ [表2.]列名(+)

例44. 查询学生的修课情况，包括修了课程的学生和没有修课的学生。

1
2
3

SELECT Student.Sno, Sname, Cno, Grade
  FROM Student LEFT OUTER JOIN SC
  ON Student.Sno = SC.Sno

子查询

在SQL语言中，一个SELECT－FROM－WHERE语句称为一个查询块。
子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句的 WHERE 或 HAVING 子句内，或其它子查询中
子查询的 SELECT 查询总是使用圆括号括起来。
使用子查询进行基于集合的测试的语句的一般格式为：
列名 [NOT] IN (子查询)

示例
例45. 查询与刘晨在同一个系的学生。

SELECT Sno, Sname, Sdept
  FROM Student
  WHERE Sdept IN
    ( SELECT Sdept FROM Student
      WHERE Sname = ‘刘晨’ )
      AND Sname != ‘刘晨’

例45. 查询与刘晨在同一个系的学生。

SELECT Sno, Sname, Sdept
  FROM Student
  WHERE Sdept IN
    ( SELECT Sdept FROM Student
      WHERE Sname = ‘刘晨’ )
      AND Sname != ‘刘晨’

例47. 查询选修了“数据库基础”课程的学生的学号、姓名。

SELECT Sno, Sname FROM Student
  WHERE Sno IN
    ( SELECT Sno FROM SC
      WHERE Cno IN
      ( SELECT Cno FROM Course
        WHERE Cname = '数据库基础') )

使用子查询进行比较测试
- 带比较运算符的子查询指父查询与子查询之间用比较运算符连接，
- 当用户能确切知道内层查询返回的是单值时，可用>、<、=、>=、<=、<>运算符。
  例48. 查询修了‘c02’课程且成绩高于此课程的平均成绩的学生的学号和成绩。
  1
  2
  3
  4
  5
  SELECT Sno , Grade FROM SC
  WHERE Cno = `c02`
  and Grade > (
  SELECT AVG(Grade) from SC
  WHERE Cno = 'c02')
使用子查询进行存在性测试
- 一般使用EXISTS谓词。
- 带EXISTS谓词的子查询不返回查询的数据，只产生逻辑真值（有数据）和假值（没有数据）。
  例49.查询选修了‘c01’号课程的学生姓名。
  1
  2
  3
  4
  5
  SELECT Sname FROM Student
  WHERE EXISTS
  (SELECT * FROM SC
  WHERE Sno = Student.Sno
  AND Cno = 'c01')
  注1：处理过程为：先外后内；由外层的值决定内层的结果；内层执行次数由外层结果数决定。
  注2：由于EXISTS的子查询只能返回真或假值，因此在这里给出列名无意义。所以在有EXISTS的子查询中，其目标列表达式通常都用*。
上句的处理过程

找外层表Student表的第一行，根据其Sno值处理内层查询
由外层的值与内层的结果比较，由此决定外层条件的真、假

顺序处理外层表Student表中的第2、3、…行。
例50.查询没有选修‘c01’号课程的学生姓名和所在系。

SELECT Sname, Sdept FROM Student 
  WHERE NOT EXISTS
    (SELECT * FROM SC
      WHERE Sno = Student.Sno 
      AND Cno = 'c01')

数据更改功能

插入数据

插入单行记录的INSERT语句的格式为：

1 2	INSERT INTO <表名> [(<列名表>)] VALUES （值表）

功能：新增一个符合表结构的数据行，将值表数据按表中列定义顺序[或列名表顺序]赋给对应列名。
注意：值列表中的值与列名表中的列按位置顺序对应，它们的数据类型必须一致。
如果<表名>后边没有指明列名，则新插入记录的值的顺序必须与表中列的定义顺序一致，且每一个列均有值（可以为空）。

示例
例51．将新生记录（95020，陈冬，男，信息系，18岁）插入到Student表中。

1 2	INSERT INTO Student VALUES ('9521105', '陈冬', '男', 18, '信息系')

例52．在SC表中插入一新记录，成绩暂缺。

1 2	INSERT INTO SC(Sno, Cno, XKLB) VALUES('9521105', 'c01', '必修')

实际插入的值为：

1	('9521105', 'c01' ，NULL ，'必修')

更新数据

用UPDATE语句实现。

格式：

1
2
3

UPDATE <表名> 
  SET <列名＝表达式> [,… n]
  [WHERE <更新条件>]

无条件更新
例53. 将所有学生的年龄加1。
1
2
UPDATE Student
SET Sage = Sage + 1
有条件更新

基于本表条件的更新
例54. 将‘9512101’学生的年龄改为21岁
1
2
UPDATE Student SET Sage = 21
WHERE Sno = '9512101'

基于其他表条件的更新
例55：将计算机系全体学生的成绩加5分。
（1）用子查询实现

UPDATE SC SET Grade = Grade + 5
  WHERE Sno IN
  (SELECT Sno FROM Student
    WHERE Sdept = '计算机系' )

（2）用多表连接实现

1
2
3

UPDATE SC SET Grade = Grade + 5
  FROM SC JOIN Student ON SC.Sno = Student.Sno
  WHERE Sdept = '计算机系'

删除数据

用DELETE语句实现
格式：

1 2	DELETE [ FROM ] <表名> [WHERE <删除条件> ]

无条件删除
例56. 删除所有学生的选课记录。
1
DELETE FROM SC
有条件删除
1. 基于本表条件的删除。
  例57．删除所有不及格学生的修课记录。
  1
  2
  DELETE FROM SC
  WHERE Grade < 60

基于其他表条件的删除
例58．删除计算机系不及格学生的修课记录。

用子查询实现

DELETE FROM SC
  WHERE Grade < 60 AND Sno IN (
    SELECT Sno FROM Student 
      WHERE Sdept = ‘计算机系’ )

用多表连接实现

1
2
3

DELETE FROM SC 
  FROM SC JOIN Student ON SC.Sno = Student.Sno
  WHERE Sdept = ‘计算机系’AND Grade < 60

建立与删除索引

索引的概念

索引主要是为了加快数据的查询速度。
与书籍中的索引或目录类似。
索引为性能所带来的好处是有代价的，因为索引在数据库中会占用一定的存储空间。
在对数据进行插入、更改和删除操作时，需要对索引进行维护，这需要花费额外的时间。
在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。
在数据库管理系统中，数据是按数据页存储的，索引项也按数据页存储。
不同的数据库管理系统的数据页的大小不完全相同，在SQL Server 2000中一个数据页的大小是8 KB。

索引的分类

聚簇索引

数据按索引列进行物理排序。
类似于电话号码簿中数据按姓氏排列。
一个表只能包含一个聚簇索引。
但一个索引可以包含多个列。
将聚簇索引用于：
- 包含大量非重复值的列。
- 使用：BETWEEN、>、>=、< 和 <=返回一个范围值的查询。
- 被连续访问的列。
- 返回大型结果集的查询。
- 经常被用作联接的列，一般来说，这些是外码列。
- 对 ORDER BY 或 GROUP BY 子句中指定的列进行索引。
聚簇索引不适用于：
- 频繁更改的列。因为这将导致整行移动。
- 字节长的列。因为聚簇索引的键值将被所有非聚簇索引作为查找键使用，并被存储在每个非聚簇索引的B树的叶级索引项中。

非聚簇索引

数据存储在一个地方，索引存储在另一个地方。
索引带有指向数据的存储位置的指针。
索引中的索引项按索引键值顺序存储，而表中的信息按另一种顺序存储。
与图书的目录类似。
数据行不按非聚簇索引键的顺序排序和存储。
非聚簇索引的叶层不包含数据页。
非聚簇索引B树的叶节点包含索引行。每个索引行包含非聚簇索引键值以及一个或多个行定位器，这些行定位器指向该键值对应的数据行。
可以在有聚簇索引的表和无聚簇索引的表上定义。
SQL Server非聚簇索引中的行定位器有两种形式：
1. 如果表没有聚簇索引，则行定位器就是指向行的指针。该指针用文件标识符 (ID)、页码和页上的行数生成。整个指针称为行ID。
2. 如果表有聚簇索引，则行定位器就是行的聚簇索引键值。通过使用聚簇索引键搜索聚簇索引来检索数据行，而聚簇索引键存储在非聚簇索引的叶节点行内。
可考虑将非聚簇索引用于：
- 包含大量非重复值的列。
- 不返回大型结果集的查询。
- 经常作为查询条件使用的列。
- 经常作为连接和分组条件的列。

唯一索引

可以确保索引列不包含重复的值。
在多列唯一索引的情况下，该索引可以确保索引列中每个值的组合都是唯一的。
聚簇索引和非聚簇索引都可以是唯一的。只要列中的数据是唯一的，就可以在同一个表上创建一个唯一的聚簇索引和多个唯一的非聚簇索引。

创建和删除索引

创建索引：

CREATE 
  [ UNIQUE ][CLUSTERED|NONCLUSTERED ]
      INDEX <索引名>
ON <表名> ( <列名>  [, … n ] ）

UNIQUE：创建唯一索引。
CLUSTERED：创建聚簇索引。
NONCLUSTERED：创建非聚簇索引。
如果没有指定索引类型，则默认是创建非聚簇索引。

示例
例1．为Student表的Sname列创建非聚簇索引。

1 2	CREATE INDEX Sname_ind ON Stuent ( Sname )

例2．为Student表的Sid列创建唯一的聚簇索引。

1
2
3

CREATE UNIQUE CLUSTERED 
   INDEX Sid_ind
   ON Stuent (Sid )

删除索引
删除索引语句格式：
DROP INDEX <索引名>
例3．删除Student表中的Sname_ind索引。
DROP INDEX Sname_ind

第6章实现数据完整性约束

数据完整性基本概念

实现声明完整性

实现过程完整性

第7章关系数据库规范化理论

函数依赖

关系规范化

关系模式中的码

范式

数据库的三大范式（3NF）是关系数据库设计中的基本规则，主要用于减少数据冗余、提高数据一致性和完整性。它们分别是第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。

第一范式（1NF）——确保原子性

定义：
第一范式（1NF）要求数据表中的所有字段必须具有原子性（Atomicity），即每个字段的值必须是不可再分的最小单位，不能存储多个值。
要求：
1. 每一列只能存储单一值（即不允许有数组、列表、集合等）。
2. 所有列必须具有相同的数据类型。
3. 必须有唯一标识（主键），确保每一行是唯一的。
示例（未满足1NF）：

学号	姓名	课程
1001	张三	数学, 物理
1002	李四	英语, 计算机

上表中，”课程” 列存储了多个值（数学、物理），不满足1NF。

修改后（满足1NF）：

学号	姓名	课程
1001	张三	数学
1001	张三	物理
1002	李四	英语
1002	李四	计算机

现在，每个字段的值都是不可再分的最小单位，满足第一范式（1NF）。

第二范式（2NF）——消除部分依赖

定义：
第二范式（2NF）要求数据库表必须满足1NF，同时消除非主键列对主键的部分依赖（Partial Dependency）。
要求：
1. 先满足1NF。
2. 所有非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分。
示例（未满足2NF）：
假设一个课程成绩表：

学号	课程	课程名称	分数
1001	MATH	高等数学	90
1001	CS	C语言	85
1002	MATH	高等数学	88

这里，主键是 （学号，课程） 的组合，但”课程名称”只依赖于”课程”，而不是整个主键（学号, 课程）。部分依赖导致数据冗余，因为”课程名称”重复存储。

修改后（满足2NF）：
将数据拆分成两张表：
课程成绩表（满足2NF）：

学号	课程	分数
1001	MATH	90
1001	CS	85
1002	MATH	88

课程信息表（满足2NF）：

课程	课程名称
MATH	高等数学
CS	C语言

现在，”课程名称” 不再重复存储，解决了部分依赖问题。

第三范式（3NF）——消除传递依赖

定义：
第三范式（3NF）要求数据库表必须满足2NF，并消除非主键字段对主键的传递依赖（Transitive Dependency）。
要求：
1. 先满足2NF。
2. 非主键列必须直接依赖于主键，而不能通过其他非主键列间接依赖主键。
示例（未满足3NF）：
假设有一张员工表：

员工ID	姓名	部门ID	部门名称
101	张三	D01	财务部
102	李四	D02	IT部
103	王五	D01	财务部

这里，”部门名称” 依赖于 “部门ID”，而”部门ID” 又依赖于”员工ID”，导致传递依赖，会引发数据冗余和一致性问题。

修改后（满足3NF）：
拆分成两张表：
员工表（满足3NF）：

员工ID	姓名	部门ID
101	张三	D01
102	李四	D02
103	王五	D01

部门表（满足3NF）：

部门ID	部门名称
D01	财务部
D02	IT部

现在，”部门名称” 直接依赖于”部门ID”，而不是通过”员工ID” 间接依赖主键，解决了传递依赖问题。

总结

范式	主要目标	关键要求	解决的问题
1NF	确保数据原子性	每列只能存储单一值	不能有重复值或数组
2NF	消除部分依赖	非主键列必须完全依赖主键	避免数据冗余，减少更新异常
3NF	消除传递依赖	非主键列不能依赖于另一个非主键列	避免数据冗余，增强数据一致性

这样，数据库的设计就更加规范化、易维护、减少冗余，提高查询效率。

BCNF

在第三范式（3NF）的基础上，还有一个更严格的范式——巴斯-科德范式（BCNF，Boyce-Codd Normal Form）。BCNF是3NF的强化版本，进一步减少了数据冗余和异常情况。

任何一个BCNF必然满足：
1. 所有非主属性都完全函数依赖于每个候选键
2. 所有主属性都完全函数依赖于每个不包含它的候选键
3. 没有任何属性完全函数依赖于非候选键的任何一组属性

3NF 与 BCNF 的关系

BCNF 是 3NF 的加强版，满足 BCNF 一定满足 3NF，但满足 3NF 不一定满足 BCNF。
3NF 允许某些非主属性依赖于非主键的候选键，而 BCNF 不允许任何非主属性依赖于非主键。
BCNF 解决了 3NF 可能出现的候选键的部分依赖问题。

BCNF 示例

示例（满足3NF，但不满足BCNF）：

学生ID	课程	教师
S1	C1	T1
S2	C2	T2
S3	C1	T1

候选键：(学生ID, 课程)
函数依赖关系：
1. (学生ID, 课程) → 教师 ✅（主键决定教师）
2. 教师 → 课程 ❌（违反BCNF，因为”教师”不是候选键，但它能决定”课程”）
问题：
1. 一个教师只能教授一个课程，但由于”教师”不是候选键，它不应该决定”课程”。
2. 这可能导致数据异常，如如果一个教师更换课程，所有相关记录都要更新。

修改后（满足BCNF）：

拆分成两张表：

学生-选课表

学生ID	课程
S1	C1
S2	C2
S3	C1

课程-教师表

课程	教师
C1	T1
C2	T2

现在，所有的函数依赖的左侧都是候选键，满足 BCNF。

3NF 与 BCNF 的对比总结

	3NF	BCNF
要求	所有非主属性必须完全依赖于候选键；允许非主属性依赖于候选键（但不能传递依赖）	所有属性都必须完全依赖于候选键；不允许任何非主属性依赖于非主键
数据冗余	可能仍然存在一定的数据冗余	更严格地消除冗余
异常情况	可能仍然存在某些更新异常	彻底消除更新异常
使用场景	适用于大多数数据库设计	适用于更严格的数据库设计，特别是需要高数据一致性的场景

一般情况下，3NF 已经能满足大多数应用需求，但如果数据冗余仍然存在，就需要进一步拆分表，满足 BCNF。

关系模式的分解准则

关系模式的分解应满足以下三个基本准则：

1. 无损连接性（Lossless Join）

分解后的关系必须能够通过自然连接（natural join）或其他连接操作，不丢失原始数据，即：

R 被分解为 R1 和 R2，必须有 R1 ⨝ R2 = R（原始关系）。

重要性：

避免数据丢失，确保可以正确恢复原始关系。
如果不满足无损连接性，可能导致部分信息丢失，影响查询结果的完整性。

无损连接的充要条件（Heath定理）：
对于关系模式 R(A, B, C) 被分解为 R1(A, B) 和 **R2(A, C)**，只要：

R1 ∩ R2 能够决定其中任意一个关系（即 R1 ∩ R2 → R1 或 R1 ∩ R2 → R2），则该分解是无损的。

2. 依赖保持（Dependency Preservation）

分解后的关系应能保持原始关系中的所有函数依赖关系，即：

R1 和 R2 的联合应该能够表示原始关系 R 的所有函数依赖。

重要性：

确保在分解后，不需要进行额外的连接操作来检查数据一致性。
若不满足该条件，则可能需要跨多个表进行连接查询才能检查某些约束，影响效率。

示例（不满足依赖保持）：
假设有关系 **R(A, B, C)**，其函数依赖为 A → B, B → C，现在分解成：

R1(A, B)
R2(B, C)

在 R1 和 R2 中，我们无法直接在某个表内检查 A → B, B → C，必须连接查询，这不满足依赖保持。

通常，3NF 分解可以保证依赖保持，而 BCNF 分解可能会破坏依赖保持。

3. 维持数据库的最小冗余（避免数据冗余）

分解后的关系应减少数据冗余，同时避免引入额外的冗余信息，即：

避免由于分解导致的重复存储数据，提高存储效率，减少更新异常。

重要性：

过多的冗余会导致更新异常（如同一个值需要在多处修改）。
冗余会浪费存储空间，降低数据库操作的效率。

示例（存在冗余）：
假设有关系 **R(学号, 课程, 教师)**，其中函数依赖为 (学号, 课程) → 教师。
如果错误地分解成：

R1(学号, 课程)
R2(课程, 教师)

那么，如果一个课程由多个学生选修，则在 R2 中，同一门课程和教师的组合会被多次存储，增加冗余。

通常，BCNF 分解可以消除冗余，而 3NF 可能仍然存在少量冗余（但可以保持依赖）。

总结

准则	要求	重要性
无损连接性	分解后可以通过连接操作恢复原始关系	确保数据不会丢失
依赖保持	分解后仍能保持原始的所有函数依赖	避免查询时需要额外的连接操作
最小冗余	避免重复存储数据，减少存储空间占用	提高数据库的存储效率，减少更新异常

在实际应用中：

3NF 分解通常能保证“无损连接性”和“依赖保持”，但可能仍然存在少量冗余。
BCNF 分解可以消除冗余，但可能会破坏依赖保持，需要额外的查询来维护完整性。

在数据库设计中，通常需要在这三者之间进行权衡，以选择最适合应用需求的范式。

第8章数据库保护

事务的基本概念

事务

事务的特征

SQL事务处理模型

并发控制

并发控制概述

并发控制措施

控制目标：事务运行过程中尽可能隔离事务外操作对本事务数据环境的影响。
在数据库环境下，并发控制的主要方式是封锁机制，即加锁（Locking），加锁是一种并行控制技术，是用来调整对共享目标，如DB中共享的记录并行存取的技术。
在数据库管理系统（DBMS）中，锁（Lock）用于控制并发访问，防止数据不一致和丢失更新问题。其中，共享锁（Shared Lock, S 锁）和排他锁（Exclusive Lock, X 锁）是两种最基本的锁机制。
在大多数数据库管理系统（如 MySQL、PostgreSQL、SQL Server 等）中：
SELECT 语句默认不会加锁（除非显式使用 FOR SHARE 或 FOR UPDATE）。
UPDATE、DELETE、INSERT 语句都会自动申请排他锁（X 锁）。

1. 共享锁（S 锁）

定义
共享锁允许多个事务同时读取数据，但不允许修改数据。
特性
- 多个事务可以共享同一个数据的共享锁，因此可以同时执行 SELECT 语句，不会互相阻塞。
- 如果一个事务持有共享锁，其他事务仍然可以申请共享锁，但不能申请排他锁（X 锁）。
- 共享锁适用于只读操作，如 SELECT ... FROM 语句。
示例
假设有一个银行账户表，现在有两个事务：
- 事务 A（读取余额）：

1 2	BEGIN; SELECT 余额 FROM 账户 WHERE 账号 = '123456' FOR SHARE;

事务 B（读取余额）：

1 2	BEGIN; SELECT 余额 FROM 账户 WHERE 账号 = '123456' FOR SHARE;

结果：
- 事务 A 和事务 B 都可以同时读取账户余额，因为共享锁允许多个事务同时读取数据。
但如果事务 C 想要更新余额（加锁方式为排他锁 X），则会被阻塞：
1
2
BEGIN;
UPDATE 账户 SET 余额 = 余额 - 500 WHERE 账号 = '123456';
因为 S 锁阻止了 X 锁的获取，防止数据被修改。

2. 排他锁（X 锁）

定义
用于写操作。一个事务持有排他锁后，其他事务不能同时持有共享锁或排他锁，从而确保在写操作期间数据不会被其他事务读取或修改。
特性
- 排他锁是独占的，同一时间只能有一个事务对该数据持有排他锁。
- 持有排他锁的事务可以读取和修改数据，其他事务必须等该锁释放后才能访问该数据。
- 适用于 UPDATE、DELETE、INSERT 等操作。
示例
假设有一个转账事务：
- 事务 A（转账操作，获取排他锁 X）

1 2	BEGIN; UPDATE 账户 SET 余额 = 余额 - 500 WHERE 账号 = '123456'; -- 申请 X 锁

此时：
- 事务 A 持有排他锁，其他事务（如事务 B）不能再获取共享锁（S 锁）或排他锁（X 锁）。
事务 B（想要读取账户余额，但被阻塞）
1
2
BEGIN;
SELECT 余额 FROM 账户 WHERE 账号 = '123456' FOR SHARE; -- 被阻塞
事务 B 被阻塞，直到事务 A 提交或回滚后，X 锁被释放。

3. 共享锁 vs. 排他锁对比

锁类型	是否允许多个事务同时持有	是否允许读取数据	是否允许修改数据
共享锁（S）	是	是	否
排他锁（X）	否	是	是

4. 兼容性（锁冲突情况）

锁的兼容性决定了事务是否可以同时持有锁：

事务 B\事务 A	共享锁（S）	排他锁（X）	无锁
共享锁（S）	✅（允许）	❌（阻塞）	✅（允许）
排他锁（X）	❌（阻塞）	❌（阻塞）	✅（允许）
无锁	✅（允许）	✅（允许）	✅（允许）

S 锁 vs. S 锁 ✅（可以共存）
S 锁 vs. X 锁 ❌（冲突，S 锁必须释放后才能获取 X 锁）
X 锁 vs. X 锁 ❌（冲突，X 锁是独占的）

5. 使用场景

操作类型	适用的锁类型	示例 SQL
只读查询	共享锁（S）	`SELECT * FROM 账户 WHERE 账号 = '123456' FOR SHARE;`
更新数据	排他锁（X）	`UPDATE 账户 SET 余额 = 余额 - 500 WHERE 账号 = '123456';`
删除数据	排他锁（X）	`DELETE FROM 账户 WHERE 账号 = '123456';`
事务控制	排他锁（X）	`SELECT * FROM 账户 WHERE 账号 = '123456' FOR UPDATE;`

6. 共享锁与排他锁的实际应用

示例：银行转账
假设 Alice 向 Bob 转账 500 元，涉及两个账户：
1. 读取 Alice 账户余额（需要 S 锁）
2. 读取 Bob 账户余额（需要 S 锁）
3. 修改 Alice 余额（需要 X 锁）
4. 修改 Bob 余额（需要 X 锁）

BEGIN;
-- 读取 Alice 余额
SELECT 余额 FROM 账户 WHERE 账号 = 'Alice' FOR SHARE;

-- 读取 Bob 余额
SELECT 余额 FROM 账户 WHERE 账号 = 'Bob' FOR SHARE;

-- Alice 扣钱
UPDATE 账户 SET 余额 = 余额 - 500 WHERE 账号 = 'Alice';

-- Bob 收钱
UPDATE 账户 SET 余额 = 余额 + 500 WHERE 账号 = 'Bob';

COMMIT;

在这个过程中：

读取账户余额时，使用 S 锁，确保数据不会被其他事务修改。
更新余额时，使用 X 锁，确保数据的一致性和防止并发问题。

一致性：一致性指的是数据库在事务执行前后必须保持数据的完整性和正确性，不允许出现违反约束或逻辑错误的情况。
并发问题：并发问题指的是多个事务同时操作数据库时，可能导致数据不一致或错误的情况。

7. 死锁问题

当两个事务各自持有一个锁，并等待对方释放锁时，会出现死锁（Deadlock）。

示例死锁情况

事务 A 锁住了行 Alice，然后尝试锁定 Bob。
事务 B 锁住了行 Bob，然后尝试锁定 Alice。
由于相互等待，发生死锁。

解决方案：
- 设置超时时间（LOCK_TIMEOUT）
- 按顺序申请锁，避免交叉锁定
  
  按顺序加锁并不是完全避免等待，而是通过确保所有事务按照相同的顺序请求资源，避免了循环等待，从而减少了死锁的可能性。这里的关键是避免循环等待条件。
- 数据库自动检测死锁，强制回滚一个事务

总结

共享锁（S 锁） 允许多个事务同时读取数据，但不允许修改。
排他锁（X 锁） 允许读取和修改数据，但不能共享。
X 锁是独占的，不能与任何其他锁共存，而 S 锁可以共享。
锁兼容性决定事务是否会被阻塞，X 锁会阻止所有其他事务访问该数据。
在数据库事务（如银行转账）中，合理使用 S 锁和 X 锁可以防止数据不一致。
死锁是数据库锁机制中的常见问题，应使用适当的策略（如超时、顺序加锁）来避免。

封锁协议

一级封锁协议：事务在修改数据时加排他锁，直到事务结束才释放。这种协议可以防止丢失修改，但不保证不读脏数据和可重复读。
二级封锁协议：在一级的基础上，事务在读取数据时加共享锁，但读取完成后立即释放共享锁。这种协议可以防止丢失修改和不读脏数据，但不保证可重复读。

加共享锁的目的：
1. 防止修改：当一个事务对某个数据项加共享锁时，其他事务不能对该数据项加排他锁（X锁），从而防止了数据的修改。这确保了在共享锁持有期间，数据不会被其他事务更改。
2. 允许并发读取：多个事务可以同时对同一数据项加共享锁，这意味着多个事务可以并发读取该数据项，而不会相互阻塞。这提高了系统的并发性能。
三级封锁协议：在二级的基础上，事务在读取数据时加共享锁，并保持到事务结束。这种协议可以防止丢失修改、不读脏数据和保证可重复读。

死锁

预防死锁的方法：
1. 一次封锁法
  一次封锁法是每个事务一次将所有要使用的数据全部加锁。
2. 顺序封锁法
  顺序封锁法是预先对数据对象规定一个封锁顺序，所有事务都按这个顺序封锁。

并发调度的可串行性

多个事务的并发执行是正确的，当且仅当其结果与按某一顺序的串行执行的结果相同，则我们称这种调度为可串行化的调度。
可串行性是并发事务正确性的准则，按这个准则，一个给定的并发调度，当且仅当它是可串行化的时，才认为是正确的调度。

两段锁协议

两段锁协议是实现可串行化调度的充分条件。
可以将每个事务分成两个时期：申请封锁期和释放封锁期，申请期申请要进行的封锁，释放期释放所占有的封锁。
在申请期不允许释放任何锁，在释放期不允许申请任何锁，这就是两段式封锁。

数据库备份与恢复

数据库故障的种类

事务内部的故障

事务故障意味着事务没有达到预期的终点（COMMIT或ROLLBACK），因此，数据库可能处于不正确的状态。

系统故障

指造成系统停止运转、系统要重启的故障。例如，硬件错误（CPU故障）、操作系统故障、突然停电等。

其它故障

介质故障或由计算机病毒引起的故障或破坏。

数据库备份

指定期或不定期地对数据库数据进行复制。
可以复制到本地机器上，也可以复制到其它机器上。
备份的介质可以是磁带也可以是磁盘，但通常选用磁带。

现代备份策略通常是混合使用磁带和磁盘，根据数据的重要性、访问频率和恢复需求选择合适的介质。
对于需要快速恢复的数据，磁盘是更好的选择；对于长期归档和大规模数据备份，磁带仍然是一个经济高效的选择。
是保证系统安全的一项重要措施。
在制定备份策略时，应考虑如下几个方面：
- 备份的内容
- 备份频率

数据库恢复

恢复策略

事务故障的恢复
系统故障的恢复
介质故障的恢复

恢复方法

利用备份技术
利用事务日志
利用镜像技术

第三章 关系数据库

关系模型概述

关系数据结构

关系操作

传统的关系运算

专门的关系运算

关系模型的数据操作

数据完整性约束

关系数据模型的基本术语与形式化定义

关系数据模型的基本术语

关系数据结构及其形式化定义

关系模型的完整性约束

实体完整性

参照完整性

用户定义的完整性

关系代数

关系运算符

传统的集合运算

专门的关系运算

选择

投影

连接

第4章 SQL语言基础

基本概念

SQL语言的发展

SQL语言的特点

SQL语言功能概述

SQL的数据类型

数值型

字符串型

普通编码字符串类型

统一字符编码字符串类型

二进制字符串类型

日期时间型

货币型

基本表的定义、删除及修改

基本表的定义与删除

修改表结构

数据查询功能

查询语句的基本结构

简单查询

选择表中若干列

选择表中若干元组

对查询结果进行排序

使用计算函数汇总数据

对查询结果进行分组计算

多表连接查询

子查询

数据更改功能

插入数据

更新数据

删除数据

建立与删除索引

索引的概念

索引的分类

聚簇索引

非聚簇索引

唯一索引

创建和删除索引

第6章 实现数据完整性约束

数据完整性基本概念

实现声明完整性

实现过程完整性

第7章 关系数据库规范化理论

函数依赖

关系规范化

关系模式中的码

范式

第一范式（1NF）——确保原子性

第二范式（2NF）——消除部分依赖

第三范式（3NF）——消除传递依赖

总结

BCNF

3NF 与 BCNF 的关系

BCNF 示例

示例（满足3NF，但不满足BCNF）：

修改后（满足BCNF）：

学生-选课表

课程-教师表

3NF 与 BCNF 的对比总结

第三章关系数据库

第6章实现数据完整性约束

第7章关系数据库规范化理论

第8章数据库保护

3. 共享锁 vs. 排他锁对比