MelonCholi 收录于专业课

2022-02-22 约 10238 字预计阅读 21 分钟

数据库原理一

第一章数据库发展史

数据管理技术发展三个阶段

人工管理阶段

磁带、卡片、纸带

数据不保存在计算机内
没有专用软件管理数据（逻辑结构 = 物理结构，数据和程序不具有独立性）
只有程序的概念，没有文件的概念
数据面向程序

文件系统阶段

数据以文件形式保存
操作系统接管读写细节
优点：
- 数据以文件形式存在磁盘上
- 逻辑结构和物理结构有简单区别，程序和数据间有设备独立性（程序只和文件打交道）
- 文件组织多样化（索引、链接、直接存取文件）
- 数据面向应用（可以重复使用）
- 对数据操作以记录为单位

文件系统三个缺点

数据冗余：同样数据在多个文件中重复
不一致性：更新操作导致重复数据不统一（由数据冗余造成）
数据联系弱：文件相互独立，缺乏联系

倒排文件系统阶段

推广索引文件，为每个字段提供单独索引
优点：允许用户按字段的任何组合检索记录
缺点：占用很多存储空间，数据更新复杂

数据库技术的产生和发展

数据库技术的三个阶段

三件大事

IMS 系统：层次模型
- 树形结构
- 表示一对多关系
- 表达多对多关系需要冗余节点
- 通过指针导航
- 查询速度快，但需要程序员掌握数据结构
DBTG 报告：网状模型
- 图形结构
- 表示多对多关系
- 通过指针导航
- 查询速度快，但需要程序员掌握数据结构
E.F.Codd 文章：
关系模型 -> 第三章

数据库阶段的特点 ✔️

采用数据模型表示复杂的数据结构
- 存储数据和数据间的关系
- 面向全组织的复杂的数据结构，实现了数据的结构化
有较高的数据独立性
- 用户只操作逻辑结构，物理结构对用户透明
提供方便的用户接口
- 终端 / 程序方式
提供数据控制功能
- 并发控制、恢复、完整性、安全性
增加系统灵活性
- 可以以数据项而非记录为单位

数据库术语

数据库 DB：长期存储在计算机内，有组织的、统一管理的相关数据的组合。
- 用户共享、冗余度小、数据联系紧密、独立性强
数据库管理系统 DBMS：介于 OS 和用户之间，为用户提供数据管理的软件。
- 层次型、网状型、关系型、面向对象型
数据库技术：研究数据库结构、存储、设计、管理、使用的一门学科
数据库系统 DBS：采用数据库技术的计算机系统

数据库技术的发展

分布式数据库技术
面向对象数据库技术

集中式 DB、C/S 架构、B/S 架构

第二章数据库系统结构

数据描述

从事物的特性到计算机中的具体表示，分别经历了三个阶段

概念设计中的数据描述（实体、属性、实体集、实体标识符）
逻辑设计中的数据描述（记录、字段、文件、关键码）
物理设计中的数据描述（位、字节、字、块、桶、卷）

信息的三个领域

从客观世界事物的特征到计算机中的表示经历了三个领域

现实世界
- 需求分析
信息世界
- 用概念设计描述数据库的概念结构
机器世界
- 在计算机中展示出来

概念设计中的数据描述

代表 E-R 模型

实体：客观存在，一个具体或抽象的对象
实体集：性质相同实体的集合
属性：实体的一个特性
实体标识符：唯一标识实体的属性或属性集

逻辑设计中的数据描述

代表关系模型等

字段（数据项）：标记实体属性的命名单位 → 属性
记录：字段的有序集合 → 实体
文件：同一类记录的集合 → 实体集
关键码（键）：唯一确定文件中每个记录的字段或字段集 → 实体标识符

数据联系的描述

联系：实体间的相互关系。
联系的元数：与一个联系有关的实体集个数
- 一元、二元、多元
- 二元联系：两个不同实体集实体之间的联系
联系的类型：一对一联系（1:1）、一对多联系（1:n）、多对多联系（m:n）

存储介质的层次

数据模型

定义

表示实体类型和实体间联系的模型

分类

概念模型（实体联系模型）
逻辑模型（结构数据模型）

概念数据模型——实体联系模型

ER 模型

独立于计算机系统的模型，用于建立信息世界的数据模型，语义表达能力丰富

构成
- 矩形表示实体类型
- 菱形表示实体间联系
- 椭圆形表示实体或联系的属性
优点
- 简单、容易理解，真实反映用户需求
- 与计算机无关，用户容易接受
缺点
- 只能说明实体间的语义联系，不能说明数据结构

逻辑数据模型——面向数据库逻辑结构的模型

从计算机的观点对数据建模，用于建立机器世界的数据模型

包含
- 数据结构：对实体类型和实体间联系的表达和实现
- 数据操作：对数据库的检索和更新（包括插入、删除、修改）两类操作的实现
- 完整性约束：数据及其联系应具有的制约和依赖规则

层次模型

用树型结构表示实体类型及实体之间联系的模型
使用有根树型结构、记录间联系通过指针、只能表示 1:n 联系
优点
- 记录之间通过指针实现，查询效率高
- 逻辑和数据转换由 DBMS 完成
缺点
- 只能表示 1:n 联系，m:n 联系表示复杂
  - 层次顺序的严格和复杂引起查询更新很复杂，程序编写困难

网状模型

用有向图结构表示实体类型及实体间联系的模型
使用有向图结构、记录间联系通过指针、能表示 m:n 联系
特点
- 允许有一个以上的结点无双亲
- 结点可以有多于一个的双亲
- 结点间可有两种或多种联系
优点
- 容易实现 m:n 联系，查询效率高。
缺点
- 程序员必须熟悉数据库的逻辑结构才能编写相应的应用程序

关系模型

用二维表格结构来表示实体集，用外键表示实体间联系的模型
- 关系模型是由若干个关系模式组成的集合，关系模式相当于记录类型，它的每一个实例称为关系。每个关系实际上就是一张二维表格。
- 关系模型和层次、网络模型的最大差别是用关键码而不是用指针导航数据，表格简单，用户易懂，编写程序是并不涉及存储结构、访问技术等问题。
特征
- 每列不可分
- 没有两行完全相同
- 没有行序
- 没有列序
关系模式三要素：数据结构、数据操作、完整性约束
- 完整性约束：实体完整性、参照完整性、用户自定义完整性

面向对象模型

面向对象模型较完整地描述了现实世界的数据结构，能支持各类新的应用需求所满足的数据类型、能表达递归和嵌套的数据结构
基本概念：对象、类

数据库的体系结构

三级结构两级映像

三级模式结构

外模式

（可以有很多）用户与数据库系统的接口，外模式可以有很多

（概念）模式

（只有一个）数据库中全部数据的整体逻辑结构描述，模式只有一个

内模式

数据库在物理存储方面的描述，最贴近物理结构

记录的存储方式
索引的组织方式
文件的组织方式

两级映像

两级映像的作用：确保数据独立性

外模式 / 模式映像：体现逻辑数据独立性
模式 / 内模式映像：体现物理数据独立性

两级数据的独立性

数据独立性（DataIndependence）是指应用程序和数据库的数据结构之间相互独立，不受影响。

物理数据独立性：修改内模式不影响模式
逻辑数据独立性：修改模式不影响外模式

DML：数据操纵语言（用户使用）
DDL：外模式、模式、内模式中数据定义语言

数据库管理系统 DBMS

DMBS 是 DBS 的核心，一切对 DB 的操作都通过 DBMS 进行

事物是 DBMS 的基本工作单位，是由用户定义的一个操作序列，这些操作要么全做要么全不做，是不可分割的

工作模式

接受用户请求并处理
将用户的数据请求（高级指令）转换成机器代码（低层指令）
实现对数据库的操作
接受查询结果
对查询结果进行格式转换
返回用户

主要功能

数据库的定义功能
- DBMS 提供 DDL 定义数据库的三级结构、两级映像，定义数据的完整性约束、保密限制等约束。
- 因此，在 DBMS 中应包括 DDL 的编译程序。
数据库的操纵功能
- 检索（查询）和更新（插入、删除、更新）
数据库的保护功能
- 数据库的恢复
- 数据库的并发控制
- 数据完整性控制
- 数据安全性控制
- 其他
  - 系统缓冲区的管理
  - 数据存储的某些自适应调节机制
数据库的维护功能
- 包括数据库的数据载入、转换、转储，数据库的改组以及性能监控等功能。
数据字典
- 数据库系统中存放三级结构定义的数据库称为数据字典 (DataDictionary, DD)。
- 对数据库的操作都要通过 DD 才能实现。
- DD 中还存放数据库运行时的统计信息，例如记录个数、访问次数等。
- 管理 DD 的子系统称为 “DD 系统”。

模块组成

查询处理器：DML 编译器、DDL 编译器、嵌入式 DML 预编译器、查询运行核心程序
存储处理器：权限和完整性管理器、事务管理器、文件管理器、缓冲区管理器

数据库系统 DBS

组成

数据库 DB
硬件
软件
数据库管理员 DBA
- DBA 是控制数据整体结构的一组人员，负责 DBS 的正常运行，承担创建、监控和维护数据库结构的责任。
- 主要职责
  - 定义模式
  - 定义内模式
  - 与用户的联络。包括定义外模式、应用程序的设计、提供技术培训等专业服务。
  - 定义安全性规则，对用户访问数据库的授权
  - 定义完整性规则，监督数据库的运行
  - 数据库的转储与恢复工作。

第三章关系运算

关系（数据）模型

定义

用二维表格表示实体集，用关键码（外键）表示实体间联系的数据模型

形式定义

关系是域的笛卡尔积的子集，元组的集合

属性：表格中的列
域：值的集合，即属性的取值范围
域的笛卡尔积：一组域的所有组合的集合
元组：表格中的行，域的笛卡尔积的元素
关系：二维表格
- 集合论：关系是一个元数为 K 的元组的集合，表示一个实体集
- 值域：关系是属性值域笛卡儿积的一个子集

关系模型特征

可以看做二维表格
表中的一行成为一个元组
列为属性
列的取值范围为域
任意两行不相同

关系的性质 ✔️

属性值是原子的，不可分解
没有重复的元组
没有行序
理论上没有列序（为方便设置列序）

关键码

超键

能唯一标识元组的属性组合（可能存在多余的属性）

候选键

能唯一标识元组的最小属性组合

主键

人为选定的一个候选键

若一个关系中有多个侯选键，则选其中的一个为关系的主键。

外键

若一个关系 R 中包含有另一个关系 S 的主键所对应的属性组 F，则称 F 为 R 的外键。称关系 S 为参照关系，R 为依赖关系。

主键使用下划线、外键使用下划波浪线

关系模式的三级体系结构

关系子模式 → 外模式
- 从若干关系中抽出满足一定条件的数据
关系模式 → 概念模式
- 定义模式名、属性名、值域名、模式的主键
存储模式 → 内模式
- 描述了关系如何在物理设备上存储

关系模型的完整性规则 ✔️

实体完整性：主键非空
参照完整性：外键必须为空或为对应关系的某个主键值
用户定义完整性：由应用环境决定数据需要符合的约束条件

关系模型的形式定义

也就是关系模型的三个组成

关系模型由数据结构、数据操作、完整性规则组成

关系代数 ⭐

关系运算分为关系代数和关系演算

数据操纵语言 DML

查询语句
更新语句

关系查询语言：非过程性语言

关系代数语言：查询操作以集合操作为基础
关系演算语言：查询操作以谓词演算（一阶逻辑）为基础，非过程性更强一些

关系代数的基本运算

并 $\bigcup$ 、差 $-$ 、笛卡尔积 $\times$、投影 $\pi$ 、选择 $\sigma$

并 $\bigcup$

Union，列要一样，相当于 or $$ R\bigcup S = { t|t \in R \vee t \in S } $$

差 $-$

Difference，列要一样，相当于 not exists $$ R-S= { t|t \in R \wedge t \notin S } $$

笛卡尔积 $\times$

Cartesian Product，相当于 from A, B

设关系 R 和关系 S 的元数分别为 r 和 s。定义 R 和 S 的笛卡儿积 R×S 是一个 (r+s) 元的元组集合，每个元组的前 r 个分量（属性值）来自 R 的一个元组，后 s 个分量是 S 的一个元组，记为 R×S $$ R \times S = { t|t=\langle t^r, t^s \rangle \wedge t^r \in R \wedge t^s\in S } $$

投影 $\pi$

对一个关系进行垂直分割，消去某些列，并重新安排列的顺序，再删去重复元组。

Projection，筛选出指定属性并自动去重，相当于 select distinct $$ {\pi_{i_1,\cdots ,i_m}} ( R )= { t|t= \langle t_{i_1},\cdots ,t_{i_m} \rangle \wedge \langle t_1,\cdots ,t_k \rangle \in R } $$

选择 $\sigma$

根据某些条件对关系做水平分割,选择符合条件的元组。

Select，根据条件筛选元组，形如 $\sigma _{\text{age=18}} ( S )$，相当于 where $$ \sigma_f ( R )= { t|t\in R\wedge f ( t )=\text{true} } $$

关系代数的组合运算

交 $\bigcap$ 、联接 $\triangleright \triangleleft $ 、自然联接 $\triangleright \triangleleft $ 、除 $\div$

交

Intersection，相当于 and $$ R\bigcap S=R- ( R-S )=S- ( S-R ) $$

联接 $\triangleright \triangleleft$

Join，是笛卡儿积、选择和投影操作的组合

$\theta$ 联接

$$ R \ \underset{i\ \theta \ j}{\mathop \triangleright \triangleleft },S={\sigma }_{i\ \theta \ ( r+j )} ( R\times S ) $$

其中 i，j 为下标，i，j 列都保留（笛卡尔积后选择）

如果 $\theta$ 为 $=$，那么就是等值连接

$F$ 联接

$$ R \ \underset{F}{\mathop \triangleright \triangleleft },S $$

这里 F 是形如 F1∧F2∧…∧Fn 公式，每个 $F_i$ 是形为 $i\theta j$ 的式子。

自然联接

特殊的等值连接

将关系 R 和 S 中公共属性组满足对应分量相等的元组联接起来，并且要在结果中将重复的属性去掉。

先笛卡尔积，再选择，再投影掉重复的列，相当于 inner join $$ R\triangleright \triangleleft \ S=\pi ( \sigma ( R\times S ) ) $$

除 $\div$

R 中满足 S 中所有条件的元组的剩余信息 not exists(not exists) $$ R\div S=\pi_{1,2,…,r-s} ( R )-\pi_{1,2,…,r-s} ( \pi_{1,2,…,r-s} ( R )\times S-R ) $$

扩充的关系代数操作

外联接：（左、右、全）（级联左右带双横线 =）哪边有 = 哪边有表对面有空
外部并：把两表并起来，并填上 null（普通并要求属性一致）
半联接：⋉ 自然联接的投影左表属性 S⋉SC 为选了课的学生

常用技巧

例子

关系演算

关系代数和关系演算的区别 ✔️ ：关系代数以集合操作为基础，关系演算以谓词演算为基础（谓词即离散数学中的一阶逻辑）

元组关系演算

${ t|P ( t ) }$ ，$t$ 代表一个元组（一行）

域关系演算

${ t_1\cdots t_k|P ( t_1\cdots t_k ) }$ ，$t_1\cdots t_k$ 代表一个元组

关系运算的安全性

关系代数操作结果不应包括无限关系和无穷验证

无限关系：指元组个数为无穷多个的关系
无穷验证：验证公式真假时需要进行无限次验证

关系运算的等价性

关系代数 = 安全的元组关系演算 = 安全的域关系演算

例子

元组关系演算

域关系演算

查询优化

关键：如何花费较少的时间和空间，有效地执行笛卡尔积操作

等价变换规则

投影串接： $\pi _{L_1} ( \pi _{L_2} ( E ) ) \equiv \pi _{L_1} ( E )$
选择串接：$\sigma _{F_1} ( \sigma _{F_2} ( E ) )\equiv\sigma _{F_1\wedge F_2} ( E )$
选择投影交换：$\pi _{L} ( \sigma _{F} ( E ) )\equiv\sigma _{F} ( \pi _{L} ( E ) )$；前提是后者依然可执行
选择对笛卡尔积分配： $\sigma _F ( E_1\times E_2 )\equiv\sigma _{F_1} ( E_1\times \sigma _{F_2} ( E_2 ) )$
选择对并分配： $\sigma _F ( E_1\bigcup E_2 )\equiv\sigma _F ( E_1 )\bigcup \sigma _F ( E_2 )$
选择对差分配： $\sigma _F ( E_1-E_2 )=\sigma _F ( E_1 )-\sigma _F ( E_2 )=\sigma _F ( E_1 )-E_2$
投影对笛卡尔积分配： $\pi _L ( E_1\times E_2 )=\pi _{L_1} ( E_1 )\times \pi _{L_2} ( E_2 )$
投影对并分配： $\pi _L ( E_1\bigcup E_2 )=\pi _L ( E_1 )\bigcup \pi _L ( E_2 )$

优化的一般策略

尽早执行选择操作
笛卡尔积和其后的选择、投影合并运算
同时计算一连串的选择和投影操作，以免分开运算造成多次扫描文件，节省操作时间。
预处理多次出现的子表达式
- 如在一个表达式中多次出现某个子表达式，可先对该子表达式进行计算并保存结果，以免重复计算
适当对关系文件进行预处理，建立合理的索引方式
优化表达式的计算方法，如选择哪个作为外层循环

优化算法 ⭐

由 DBMS 中的 DML 编译器完成

将自然联接转为笛卡尔积 + 选择 + 投影
逆用选择串接，将选择拆开
将每个选择和投影尽可能移向树叶
将相邻的投影和选择转化为 (0/1 个) 选择 + (0/1 个) 投影
- 把选择和投影合并成单个选择、单个投影或一个选择后跟一个投影。
按二元运算分组，保证每组只有一个二元运算

例

第四章结构化查询语言

Structured Query Language, SQL

SQL 概述

定义：结构化查询语言，一种介于关系代数和关系演算之间的语言。

SQL 体系结构

三级体系结构对应关系

内模式：对应存储文件
模式：对应基本表（元组称为行、属性称为列）
外模式：对应视图

SQL 体系结构的特征

一个 SQL 模式是表和约束的集合
表是行的集合
表可以是基本表，也可以是视图。视图只存放定义不存放数据
一张表可以存放在多个存储文件中，一份存储文件可以存放多张表
用户使用 SQL 语句对视图和表进行操作；表和视图对用户而言都是关系（即表格）
用户可以是程序也可以是终端

SQL 的组成

数据定义（SQL DDL）：定义 SQL 模式、基本表、视图和索引
数据操纵（SQL DML）：数据查询（DQL）、数据更新（插入删除修改）
数据控制（SQL DCL）：对基本表和视图的授权、完整性规则描述、事务控制语句
嵌入式 SQL：嵌入 SQL 的规则

SQL 数据定义

SQL 模式的操作

SQL 模式的定义：基本表的集合

创建 `CREATE SCHEMA`

1
CREATE SCHEMA <模式名> AUTHORIZATION <拥有者>;

撤销 `DROP SCHEMA`

1
DROP SCHEMA <模式名> [CASECADE|RESTRICT];

CASECADE（级联）连锁式
RESTRICT 约束式：只有当模式中没有任何下属元素时允许撤销

一般不用模式 SCHEMA，而用数据库 DATABASE

基本数据类型

数值型
- INTEGER：长整数
- DOUBLE PRECISION：双精度浮点数
- FLOAT(n)：浮点数，精度至少为 n 位数字;
- NUMERIC(p,d)：p.d 位定点小数，也可写成 DECEMIAL(p,d) 或 DEC(p,d)
字符串型
- CHAR(n)：定长 n
- VACHAR(n)：最大长度 n
位串型
- BIT(n)：定长 n
- BIT VARYING(n)：最大长度 n
时间型
- DATE(YYYY-MM-DD)
- TIME(HH:MM:SS)
- 允许比较操作

基本表的操作

基本表创建 ⭐ `CREATE TABLE`

列级约束 + 表级约束
三个子句：主键子句、外键子句、检查子句

1
2
3
4
5
6
CREATE TABLE S (
    SNO CHAR(4) NOT NULL,
    PRIMARY KEY (SNO,PNO),             -- 主键子句
    FOREIGN KEY(JNO) REFERENCES J(JNO) -- 外键子句
    CHECK (QTY BETWEEN 0 AND 10000)    -- 检查子句
);

基本表修改 `ALTER TABLE`

增删属性

增加属性

1
2
3
ALTER TABLE S ADD 属性名 属性类型
# 例
ALTER TABLE S ADD TELE CHAR(12);

删除属性

1
ALTER TABLE S DROP 属性名 [CASCADE(连锁)|RESTRICT(约束)]

基本表删除 `DROP TABLE`

1
DROP TABLE S [CASCADE(连锁)|RESTRICT(约束)];

视图的操作

视图创建 `CREATE VIEW`

在创建一个视图时，系统把视图的定义存放在数据字典中，而不存储视图对应的数据，在用户使用视图时才去求对应的数据。

1
2
3
4
5
6
7
CREATE VIEW <视图名>(列名表)
AS  <SELECT  查询语句>
# 例
CREATE VIEW JSP_NAME(JNO,JNAME) AS
    SELECT (J.JNO,J.JNAME)
    FROM S,J
    WHERE …

视图撤销 `DROP VIEW`

1
DROP VIEW JSP_NAME

视图查询

系统会根据数据字典的定义将视图查询转换为对基本表的查询

HAVING：组条件

WHERE：行条件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE VIEW S_GRADE(sno, c_num, avg_grade) AS (
    SELECT sno, COUNT(cno),AVG(grade)
    FROM SC
    GROUP BY sno
);

SELECT sno, avg_grade
FROM S_GRADE
WHERE c_num >(
    SELECT c_num
    FROM S_GRADE
    WHERE sno=’S4’
);

会转变成：

1
2
3
4
5
6
7
8
9
SELECT sno, AVG(grade) AS avg_grade
FROM SC
GROUP BY sno
HAVING COUNT(cno)>(
    SELECT COUNT(cno)
    FROM SC
    WHERE sno=’S4’
    GROUP BY sno
);

视图更新

只有行列子集视图可以进行更新

行列子集视图：当视图是从单个基本表仅使用选择和投影导出，并包含了基本表的主键或某个候选键，则可以进行更新操作。

拒绝更新的视图：

视图从多个基本表联接导出
视图有分组或聚合操作

1
2
3
4
5
6
/* 以下更新会被拒绝：
** 因为视图包含分组聚合操作COUNT(cno), AVG(grade)
**/
UPDATE S_GRADE
SET sno=’S3’
WHERE sno=’S4’

在 SQL2 中,允许更新的视图在定义时，必须加上 WITH CHECK OPTION 短语。

索引的操作

索引创建 `CREATE INDEX`

1
2
3
CREATE [UNIQUE] INDEX 索引名 ON 基本表名 (<列名>[<次序>]...)
# 例
CREATE UNIQUE INDEX SPJ_INDEX ON SPJ(SNO ASC, PNO, ASC, JON DESC);

索引撤销 `DROP INDEX`

1
DROP INDEX JNO_INDEX, SPJ_INDEX;

SQL 数据查询 ⭐

SQL 查询语句

select 投影、from 笛卡尔积、where 选择（条件）

一些技巧

在 SQL 中要表达涉及到 “全部“ 值的查询时，可以将问题转换为两次否定的语义表达形式（即：不存在……，……没有……＂），然后用两次 NOT EXISTS 的方式来实现。
先用 where 筛选，再 having，最后运行聚合函数

格式

1
2
3
4
5
SELECT [DISTINCT] 列名序列
FROM 表名 
[WHERE 行条件表达式]                       -- 行条件子句
[GROUP BY 列名序列 [HAVING 组条件表达式]]    -- 分组子句
[ORDER BY 列名 [ASC|DESC] 序列]            -- 排序子句

WHERE 子句称为 “行条件子句”
GROUP 子句称为 “分组子句”
HAVING 子句称为 “组条件子句”
ORDER 子句称为 “排序子句”

执行过程

`WHERE` 运算符

SELECT 语句的语义

SELECT 语句中未使用分组子句，也未使用聚合操作
- 那么 SELECT 子句的语义是对查询的结果执行投影操作
SELECT 语句中未使用分组子句，但在 SELECT 子句中使用了聚合操作
- 此时 SELECT 子句的语义是对查询结果执行聚合操作
SELECT 语句使用了分组子句和聚合操作
- 此时 SELECT 子句的语义是对查询结果的每一分组去做聚合操作

多表查询与联接操作

INNER JOIN：内联接 → 匹配行

LEFT JOIN：左外联接 → 匹配行 + 左表；保证左边所有行都存在

RIGHT JOIN：右外联接 → 匹配行 + 右表

FULL JOIN：完全外联接 → 匹配行+左表+右表；左右不匹配的行也保留

CROSS JOIN：交叉联接 → 笛卡尔积

以下五者等价：查询选修 2 号课程的学生姓名

联接查询

from 表 1 联接类型表 2 on 联接条件

1
2
3
SELECT SNAME
FROM SC INNER JOIN S ON S.SNO=SC.SNO   -- 注意 ON
WHERE CNO=’2’;

多表查询

1
2
3
SELECT SNAME 
FROM S,SC 
WHERE S.SNO=SC.SNO AND CNO=’2’;

嵌套操作

不相关子查询：子查询条件不依赖父查询，效率最高

1
2
3
SELECT SNAME FROM S WHERE SNO IN (
    SELECT SNO FROM SC WHERE CNO=’2’
);

相关子查询：子查询条件依赖父查询

1
2
3
SELECT SNAME FROM S WHERE ‘2’ IN (
    SELECT CNO FROM SC WHERE SNO=S.SNO
);

EXISTS：也是相关子查询

1
2
3
SELECT SNAME FROM S WHERE EXISTS (
    SELECT * FROM SC WHERE SNO=S.SNO AND CNO=’2’
);

ANY、ALL 谓词

ANY、ALL 用于单属性表前以聚合，MAX、MIN 用于 SELECT 后以聚合。

查询非信息系中比信息系至少某一个学生年龄小的学生姓名年龄：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT Sname, Sage
FROM S
WHERE Sdept!=’IS’ AND Sage < ANY (
    SELECT Sage FROM S WHERE Sdept=’IS’
);
# 等价于
SELECT Sname, Sage
FROM S
WHERE Sdept!=’IS’ AND Sage<(
    SELECT MAX(Sage)
    FROM S
    WHERE Sdept=’IS’
);

EXISTS 存在谓词

用 EXISTS/NOT EXISTS 实现全称量词

存在表任意关系：$\forall x ( p ) \equiv \neg \exists x ( \neg p )$

查询选修全部课程的学生姓名：（也就是不存在一门课它没选）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 找到某学生并满足：不存在这样一种情况：有课程该学生没有选修
# “有课程该学生没有选修”的写法：找到某课程并满足：不存在这样一种情况：该课程被这个学生选修
SELECT Sname
FROM S
WHERE NOT EXISTS(
    SELECT Cno
    FROM C
    WHERE NOT EXISTS(
        SELECT Sno
        FROM SC
        WHERE Cno=C.Cno AND Sno=S.Sno
    )
);
# 等价于
SELECT Sname
FROM S
WHERE NOT EXISTS( /*查询S没选的课*/
    SELECT Cno 
    FROM C INNER JOIN SC
    ON C.Cno=SC.Cno
    WHERE NOT EXISTS Sno=S.sno
);

用 EXISTS/NOT EXISTS 实现逻辑蕴函

存在表蕴含关系： $ \forall y ( p arrow q ) \equiv \neg \exists y ( \neg ( \neg p \vee q ) ) \equiv \neg \exists y ( p \wedge \neg q ) $

查询选过学生 A（学号95002）选过的全部课程的学生B学号：（不存在 A 选的课它没选）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SELECT DISTINCT Sno
FROM SC AS X
WHERE NOT EXISTS( /*不存在这样一个课程号*/
    SELECT Cno
    FROM SC AS Y
    WHERE Y.Sno=’95002’ /*95002选了*/
    AND NOT EXISTS( /*B没选*/
        SELECT *
        FROM SC AS Z
        WHERE Z.Cno=Y.Cno AND Z.Sno=X.Sno
    )
);

聚合函数

逻辑：先用 where 筛选，再 having，最后运行聚合函数

COUNT(*) 元组个数
COUNT(列名) 列中非空值个数
COUNT(DISTINCT 列名) 列中元素种数
SUM(列)、AVG(列)
MAX(列)、MIN(列)
DISTINCT

数据分组

Group By 属性必须在 SELECT 后出现；
在 SELECT 中指定的字段要么就要包含在 Group By 语句中，作为分组的依据；要么就要被包含在聚合函数中
通常 SELECT 语句中使用了分组子句就会有聚合操作；但执行聚合操作不一定要用分组子句。

查询每门课的选课人数：

1
2
3
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;

集合操作

UNION 并
INTERSACT 交
EXCEPT 差

例

SQL 数据更新 ⭐

数据插入 `INSERT`

插入单个元组

1
2
3
4
5
6
INSERT INTO 基本表名(列名表)
		VALUES (元组值)

# 例
INSERT INTO SC(SNO,CNO)
        VALUES ( 'S3','C3')

插入子查询结果

1
2
3
4
5
6
7
8
INSERT INTO 基本表名(列名表)
	SELECT  查询语句；

# 例
INSERT INTO S_AVG_GRADE
	SELECT SNO,AVG(GRADE)
	FROM SC
	GROUP BY SNO;

数据删除 `DELETE`

1
2
3
4
5
6
7
8
DELETE FROM <表名>  
    WHERE <条件表达式>

# 例
DELETE  FROM  SC 
   WHERE CNO IN
     (SELECT CNO FROM C
      WHERE CNAME='数据库原理');

数据修改 `UPDATE`

1
2
3
UPDATE 基本表名 
    SET 列名=值表达式[, 列名=值表达式…]
    [WHERE 条件表达式]

把课程名为 “数据库原理” 的成绩提高 10%.

1
2
3
4
UPDATE  SC 
	SET GRADE=1.1*GRADE
	WHERE CNO IN (SELECT CNO FROM C
                WHERE CNAME='数据库原理');

把课程名为 “数据库原理” 的学分增加 1 分，老师改为王。

1
2
3
UPDATE  C 
  SET CREDIT=CREDIT+1,TNAME=‘WANG’
  WHERE CNAME='数据库原理';

嵌入式 SQL

定义：嵌入在高级语言（宿主语言）的程序中使用的 SQL 语言

实现方式

扩充宿主语言的编译程序
预处理方式（常用）

使用规定

必须解决的问题

为区分 SQL 语句与宿主语言语句，在所有的 SQL 语句前必须加上前缀标识 EXEC SQL，并以 END EXEC 作为语句结束标志
通过共享变量进行数据交互
通过游标统一 SQL 中一次一集合的和程序中一次一记录工作方式
- 定义游标语句：DECLARE
- 打开游标语句：OPEN
- 游标推进语句：FETCH
- 关闭游标语句：CLOSE

只有查询结果是多条记录、或需要对当前记录进行操作时才需要使用游标

使用嵌入式的目标：统一 SQL 中一次一集合的和程序中一次一记录工作方式

使用技术：blablabla

第六章实体联系模型

ER 模型的基本元素

基本元素：实体、联系、属性

实体：一个数据对象 → 方框
联系：表示实体间的关系 → 菱形框，用线段和实体相连
属性：表示实体的特性 → 椭圆形框
- 实体标识符（主键）加下划线，用线段和实体相连

属性的分类

基本属性和复合属性

基本属性：不可再分割的属性。
复合属性：可再分解为其他属性的属性（即属性嵌套属性）
- 复合属性形成了一个属性的层次结构。

单值属性和多值属性

单值属性：同一实体的属性只能取一个值。
多值属性：同一实体的某些属性可能取多个值。
- 用双椭圆表示
- 学位、住址

多值属性的变换方法

添加新的属性：将多值属性展开成多个单值属性
添加弱实体：通过双菱形连双方框，属性为销售性质和价格

导出属性

可由其他信息推导的属性
如用出生日期推算年龄
用虚线和虚椭圆连接

空值

空值的三种意义

占位空值：表示无意义（未婚则配偶名无意义）
未知空值：表示未知（已婚但配偶名未知）
员工是否已婚未知

联系的设计

联系的元数

联系涉及到的实体集个数

一元联系：员工管理员工，零件使用零件
- 在同一个实体内部存在的联系，又叫递归联系
- 一对一、一对多：做外键
- 多对多：单独建表
二元联系：零件构成产品
- 一对一：双方表选一个建对方的外键
- 一对多：多的一方加入外键
- 多对多：单独建表（SC）
三元联系：学生选老师开设的课，单独建表（SPJ）

联系的联通词

1:1, 1:N, M:N, M:N:P 等

一元联系

二元联系

三元联系

联系的基数

有两个实体集 E1 和 E2，E1 中的每个实体与 E2 中有联系实体数目的最小值 Min 和最大值 Max，称为 E1 的基数

写在联系两侧，格式为 $ ( \min ,\max )$ ，靠近一侧做主语

ER 模型的扩充

依赖联系

一个实体的存在以另一个实体的存在为前提

如，一个职工可能有多个社会关系，社会关系是多值属性；为了消除冗余，设计两个实体：职工与社会关系。在职工与社会关系中，社会关系的信息是以职工信息的存在为前提。因此社会关系的存在是以职工的存在为前提，所以职工与社会关系是一种依赖联系。

弱实体

一个实体对于另一些实体具有很强的依赖联系，而且该实体的主码部分或全部从其父实体中获得。

子类、超类

当较低层上实体类型表达了与之联系的较高层上的实体类型的特殊情况时，就称较高层上实体类型为超类型（supertype），较低层上实体类型为子类型（subtype）。

有继承性，且实体标识符相同。超类用两端双线的矩形框表示，联系中间加圈

性质

子类继承超类的所有属性，但可以包含更多属性
通过子类实体和超类实体有相同的实体标识符实现（即子类和超类主键相同）

填空题 TLB：

SQL 三个阶段：人工管理、文件系统、数据库系统
数据库技术三个阶段：层次模型、网状模型、关系模型
数据控制四功能：并发控制、恢复、完整性、安全性
三个世界：现实世界、信息世界、机器世界
数据模型分类：概念模型、逻辑模型
数据模型三要素：数据结构、数据操纵、完整性约束
完整性约束：实体完整性、参照完整性、用户自定义完整性
三级模式：外模式、模式、内模式
两级映像：外模式模式映像、模式内模式映像
完整性规则：实体完整性、参照完整性、用户自定义完整性
空值三个意义：占位空值、未知空值、未知

数据库原理（1）

数据库原理一

第一章 数据库发展史

数据管理技术发展三个阶段

人工管理阶段

文件系统阶段

文件系统三个缺点

倒排文件系统阶段

数据库技术的产生和发展

数据库技术的三个阶段

数据库阶段的特点 ✔️

数据库术语

数据库技术的发展

第二章 数据库系统结构

数据描述

信息的三个领域

概念设计中的数据描述

逻辑设计中的数据描述

数据联系的描述

存储介质的层次

数据模型

概念数据模型——实体联系模型

逻辑数据模型——面向数据库逻辑结构的模型

层次模型

网状模型

关系模型

面向对象模型

数据库的体系结构

三级模式结构

外模式

（概念）模式

内模式

两级映像

两级数据的独立性

数据库管理系统 DBMS

工作模式

主要功能

模块组成

数据库系统 DBS

组成

分类

第三章 关系运算

关系（数据）模型

定义

定义

形式定义

关系模型特征

关系的性质 ✔️

关键码

超键

候选键

主键

外键

关系模式的三级体系结构

关系模型的完整性规则 ✔️

关系模型的形式定义

关系代数 ⭐

关系代数的基本运算

并 $\bigcup$

差 $-$

笛卡尔积 $\times$

投影 $\pi$

选择 $\sigma$

关系代数的组合运算

交

联接 $\triangleright \triangleleft$

$\theta$ 联接

$F$ 联接

自然联接

除 $\div$

扩充的关系代数操作

常用技巧

例子

关系演算

元组关系演算

域关系演算

关系运算的安全性

关系运算的等价性

例子

元组关系演算

第一章数据库发展史

第二章数据库系统结构

第三章关系运算

第四章结构化查询语言

创建 `CREATE SCHEMA`

撤销 `DROP SCHEMA`

基本表创建 ⭐ `CREATE TABLE`

基本表修改 `ALTER TABLE`

基本表删除 `DROP TABLE`

视图创建 `CREATE VIEW`

视图撤销 `DROP VIEW`

索引创建 `CREATE INDEX`

索引撤销 `DROP INDEX`

`WHERE` 运算符

数据插入 `INSERT`

数据删除 `DELETE`

数据修改 `UPDATE`

第六章实体联系模型