数据库系统概论

2024/3/28 Basic Sql

字数统计: 11.6k字 | 阅读时长: 42分 | 总文章阅读量：次

前言

学业所迫，~~教的居然不是Mysql而是SqlServer~~，说白了跟sql的实际运用关系不是特别大

于是继续坐牢

参考：《数据库系统概论（第5版）》以及各路博客

绪论

数据模型

概念：数据模型是对现实世界特征的抽象，用来描述数据、组织数据和对数据进行操作

数据模型是数据库系统的基础

概念模型

用途：

概念模型用于信息世界的建模
是现实世界到机器世界的一个中间层次
是数据库设计的有力工具
数据库设计人员和用户之间进行交流的语言

基本要求：

较强的语义表达能力
能够方便、直接地表达应用中的各种语义知识
简单、清晰、易于用户理解

信息世界的基本概念：

实体（Entity）：客观存在并可相互区别的事物称为实体。可以是具体的人、事、物或抽象的概念。
属性（Attribute）：实体所具有的某一特性称为属性。一个实体可以由若干个属性来刻画。
码（Key）：唯一标识实体的属性集称为码。
域（Domain）：属性的取值范围称为该属性的域。
实体型（Entity Type）：用实体名及其属性名集合来抽象和刻画同类实体称为实体型
实体集（Entity Set）：同一类型实体的集合称为实体集
联系（Relationship）：
- 现实世界中事物内部以及事物之间的联系在信息世界中反映为实体内部的联系和实体之间的联系
- 实体内部的联系通常是指组成实体的各属性之间的联系
- 实体之间的联系通常是指不同实体集之间的联系

最经常使用的概念模型是E-R 图（实体-联系模型，Entity-Relationship）

数据模型的组成要素

静态特征（数据结构）
动态特征（数据操作）
数据的完整性约束条件

层次模型

层次模型是数据库系统中最早出现的数据模型，用树形结构来表示各类实体以及实体间的联系

数据结构：

有且只有一个结点没有双亲结点，这个结点称为根结点
根以外的其它结点有且只有一个双亲结点

特点：

结点的双亲是唯一的
只能直接处理一对多的实体联系
每个记录类型可以定义一个排序字段，也称为码字段
任何记录值只有按其路径查看时，才能显出它的全部意义
没有一个子女记录值能够脱离双亲记录值而独立存在

网状模型

网状数据库系统采用网状模型作为数据的组织方式

数据结构：

允许一个以上的结点无双亲
一个结点可以有多于一个的双亲

关系模型

数据结构：一张规范化的二维表，它是关系型数据库的基础

术语：

关系（relation）：即一张表
元组（tuple）：表中的一行
属性（attribute）：表中的一列
码（key）：表中的某个属性组（属性+元组），如：学号确定学生
域（domain）：一组具有相同数据类型的值的集合。属性的取值范围来自某个域
分量：元组中的一个属性值。关系的每一个分量必须是一个不可分的数据项
关系模式：对关系的描述，如：学生（学号，姓名，年龄，性别，系名，年级）

数据操纵：查询、插入、删除和更新数据

完整性约束：实体完整性、参照完整性和用户定义的完整性，见下面的关系完整性

特点：实体及实体之间的联系都可以使用相同的结构类型来表示

数据库系统的三级模式结构

三级模式是对数据进行抽象的 3 个级别，把数据的具体组织留给 DBMS 管理

（概念）模式

也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图

一个数据库只有一个模式
是数据库数据在逻辑级上的视图
数据库模式以某一种数据模型为基础
定义模式时不仅要定义数据的逻辑结构（如数据记录由哪些数据项构成，数据项的名字、类型、取值范围等），而且要定义与数据有关的安全性、完整性要求，定义这些数据之间的联系

外模式

也称子模式（Subschema）或用户模式，是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。外模式面向具体的应用程序，它定义在模式之上，但独立于存储模式和存储设备

一个数据库可以有多个外模式
外模式就是用户视图
外模式是保证数据安全性的一个有力措施

内模式

也称存储模式（Storage Schema），它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式（例如，记录的存储方式是顺序存储、按照B树结构存储还是按hash方法存储；索引按照什么方式组织；数据是否压缩存储，是否加密；数据的存储记录结构有何规定）

一个数据库只有一个内模式
一个表可能由多个文件组成，如：数据文件、索引文件

数据库的二层映像功能与数据独立性

两层映像：

外模式/模式映像
模式/内模式映像

保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性

物理独立性：对数据库进行物理更改时，不需要修改应用程序或查询语句。即用户的应用程序与存储在磁盘上数据库中的数据是相互独立的
逻辑独立性：在修改数据库中的逻辑结构时，不需要修改应用程序或查询语句。要保证数据库的逻辑数据独立性，需要修改外模式/模式映像

关系数据库

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

域（domain）：一组具有相同数据类型的值的集合，例：{0,1}、{男、女}
笛卡尔积：域上的一种集合运算

给定一组域D1，D2，…，Dn，允许其中的某些域是相同的，其笛卡尔积为：D1*D2*…*Dn = {(d1，d2，…，dn) | di ∈ Di，i=1,2,…,n}

其中每一个元素叫做一个n元组（tuple），元素中的每一个值叫做一个分量（component）

一个域允许的不同取值个数称为这个域的基数

Screenshot_2024-03-29-08-34-30-360_com.orion.notein-edit

关系：D1*D2*…*Dn的子集叫做在域D1，D2，…，Dn上的关系，表示为R(D1，D2，...，Dn)。这里R表示关系的名字，n是关系的目或度

单元关系：n=1

二元关系：n=2
候选码（candidate key）：关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，则称该属性组为候选码
若一个关系有多个候选码，则选定其中一个为主码（primary key）

例：关系（SUPERVISOR，MAJOR，POSTGRADUATE）中，假设 POSTGRADUATE 不会重名，则可以作为SMP关系的主码，用下划线表示 POSTGRADUATE

全码（all-key）：关系模式的所有属性是这个关系模式的候选码

关系模式

关系的描述称为关系模式，表示为R(U,D,DOM,F)，简记为R(U)或R(A1,A2, ... ,An)（A为属性名）

R：关系名

U：组成该关系的属性名集合

D：U中属性所来自的域

DOM：属性向域的映像集合

F：属性间数据的依赖关系集合

关系数据库

在关系模型中，实体以及实体间的练习都是用关系来表示的

关系数据库的型也称为关系数据库模式，是对关系数据库的描述

关系数据库的值是这些关系模式在某一时刻对应的关系的集合，通常称为关系数据库

关系操作

基本关系操作

增（insert）删（delete）查（query）改（update）：查询、插入、删除和修改等

查询操作：选择、投影、连接、除、并、差、交、笛卡尔积，其中选择、投影、并、差、笛卡尔积是5种基本操作

操作的对象和结果都是集合，这种操作方式也称为一次一集合的方式。而非关系数据库模型的数据操作方式则为一次一记录的方式

关系数据语言的分类

分为三类：

关系代数语言：关系操作能力用代数方式表示，如 ISBL
关系演算语言：关系操作能力用逻辑方式表示，其中又分为元组关系演算语言（ALPHA、QUEL）和域关系演算语言（如 QBE）
具有关系代数和关系演算双重特点的语言：如 SQL

关系的完整性

关系模型的完整性规则是对关系的某种约束条件，也就是说关系的值随着时间变化应该满足一些约束条件

关系模型汇总有三类完整性约束：实体完整性、参照完整性、用户定义的完整性

实体完整性

关系必须有主键，且主键不能重复，不能为空

若属性A是基本关系R的主属性，则A不能取空值

注：关系模型中，非主属性不可能出现在任何候选码中

参照完整性

以外键的形式维护实体间的引用关系，可以为空或者值为被参照关系的主键值

设 F 是基本关系 R 的一个或一组属性，但不是关系 R 的码， Ks 是基本关系 S 的主码。如果 F 与 Ks 相对应，则称 F 是 R 的外码（foreign key），并称基本关系R为参照关系，基本关系S为被参照关系或目标关系

例：

学生（学号，姓名，性别，专业号，年龄）

专业（专业号，专业名）

这两个关系之间存在属性的引用，即学生关系引用了专业关系的主码“专业号”。此时学生关系的“专业号”属性与专业关系的主码“专业号”相对应，因此“专业号”属性是学生关系的外码。这里专业关系是被参照关系，学生关系为参照关系。

参照完整性规则（定义外码与主码之间的引用规则）：若属性（或属性组）F 是基本关系 R 的外码，它与基本关系 S 的主码 Ks 相对应（基本关系 R 和 S 不一定是不同的关系），则对于 R 中每个元组在 F 上的值必须：

或者取空值（F中的每个属性值均为空值，即外码中的每个属性值均为空值）
或者等于S中某个元组的主码值（以上面的例子为例，学生关系中每个元组的“专业号”属性只能取空值（未分配专业）或非空值（必须是专业关系中某个元组的“专业号”值））

对于上面的例子：即”专业号“属性只能取两类值：

空值：表示尚未给该学生分配专业
非空值：这时该值必须是专业关系中某个元组的”专业号“值，表示该学生不可能分配到一个不存在的专业中

用户定义的完整性

用户由应用环境决定的针对具体关系数据库的约束（属性类型等）

针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。例如：某个属性必须取唯一值，某个非主属性不能取空值等。

继续前面的例子：定义学生姓名不能取空值，学生的成绩取值范围可以定义在0~100之间，学生的性别不是男就是女…

关系代数

集合运算

并：R∪S
差：R-S，属于R但是不属于S
交：R∩S = R-(R-S)
笛卡尔积：广义的笛卡尔积，其中的元素是元组

两个分别为 n 目和 m 目的关系 R 和 S 的笛卡尔积是一个 (n+m) 列的元组的集合。元组的前n列是关系R的一个元组。若R有k₁个元组，S有k₂个元组，则关系R和关系S的笛卡尔积有k₁*k₂个元组。记作：R×S = {tr⌒ts | tr ∈R ∧ ts ∈S}

就下面这张表而言，其笛卡尔积为 R 的每一行遍历 S 的每一行拼接成 R×S 的每一行

Screenshot_2024-04-07-08-34-08-817_com.orion.notein-edit

关系运算

选择（限制）σ：在关系R中选择满足给定条件的诸元组。σ_F(R) = {t | t ∈ R∧F(t) = '真'}，其中F表示选择条件，是一个逻辑表达式，取 true 或 false
投影 Π：从R中选择出若干属性列组成新的关系。

Π_A(R) = {t[A] | t∈R}，其中A为R中的属性列
连接 ⋈：从两个关系的笛卡尔积中选取属性间满足一定条件的元组

等值连接：选取属性值相等的那些元组

自然连接：要求两个关系中进行比较的分量必须是同名的属性组，并在结果中把重复的属性列去掉。即要求两个关系要含有一个或多个共有的属性
- left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录
- right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录
- inner join （等值连接或内连接）：只返回两个表中连接字段相等的行
- full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录
除：设关系R除以关系S的结果为关系T，则T包含所有在R但不在S中的属性及其值，且T的元组与S的元组的所有组合都在R中

例：

假设有一个数据库包含以下关系模型：

Teacher(Tno, Tname, Tage, Tsex); Department(Dno, Dname, Tno); Work(Tno,Dno,Year,Salary)

请使用关系代数表示每个查询

列出工资超过 5000 的教师的不同年龄：Π𝑇𝑎𝑔𝑒(𝑇𝑒𝑎𝑐ℎ𝑒𝑟 ⋈ 𝜎𝑆𝑎𝑙𝑎𝑟𝑦>5000(𝑊𝑜𝑟𝑘))

工资超过5000：𝜎𝑆𝑎𝑙𝑎𝑟𝑦>5000(𝑊𝑜𝑟𝑘)

的教师：𝑇𝑒𝑎𝑐ℎ𝑒𝑟 ⋈ 𝜎𝑆𝑎𝑙𝑎𝑟𝑦>5000(𝑊𝑜𝑟𝑘)

列出年龄：Π𝑇𝑎𝑔𝑒(𝑇𝑒𝑎𝑐ℎ𝑒𝑟 ⋈ 𝜎𝑆𝑎𝑙𝑎𝑟𝑦>5000(𝑊𝑜𝑟𝑘))
查找不在计算机系工作的教师代码：Π𝑇𝑛𝑜(𝑇𝑒𝑎𝑐ℎ𝑒𝑟) − Π𝑇𝑛𝑜 (𝑊𝑜𝑟𝑘 ⋈ Π𝐷𝑛𝑜(𝜎𝐷𝑛𝑎𝑚𝑒=′计算机系′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡)))

在计算机系：𝜎𝐷𝑛𝑎𝑚𝑒=′计算机系′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡)

在计算机系的部门代码：Π𝐷𝑛𝑜(𝜎𝐷𝑛𝑎𝑚𝑒=′计算机系′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡))

在计算机系工作：𝑊𝑜𝑟𝑘 ⋈ Π𝐷𝑛𝑜(𝜎𝐷𝑛𝑎𝑚𝑒=′计算机系′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡))

不在计算机系工作的教师代码：Π𝑇𝑛𝑜(𝑇𝑒𝑎𝑐ℎ𝑒𝑟) − Π𝑇𝑛𝑜 (𝑊𝑜𝑟𝑘 ⋈ Π𝐷𝑛𝑜(𝜎𝐷𝑛𝑎𝑚𝑒=′计算机系′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡)))
系主任 T1 管辖范围内的所有教师姓名：Π𝑇𝑛𝑎𝑚𝑒(𝑇𝑒𝑎𝑐ℎ𝑒𝑟 ⋈ Π𝑇𝑛𝑜(𝑊𝑜𝑟𝑘 ⋈ Π𝑇𝑛𝑜= ′𝑇1′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡)))

T1：Π𝑇𝑛𝑜= ′𝑇1′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡)

T1管辖范围：𝑊𝑜𝑟𝑘 ⋈ Π𝑇𝑛𝑜= ′𝑇1′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡)

T1管辖范围的教师代码：Π𝑇𝑛𝑜(𝑊𝑜𝑟𝑘 ⋈ Π𝑇𝑛𝑜= ′𝑇1′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡))

T1管辖范围内的所有教师：𝑇𝑒𝑎𝑐ℎ𝑒𝑟 ⋈ Π𝑇𝑛𝑜(𝑊𝑜𝑟𝑘 ⋈ Π𝑇𝑛𝑜= ′𝑇1′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡))

T1管辖范围内的所有教师姓名：Π𝑇𝑛𝑎𝑚𝑒(𝑇𝑒𝑎𝑐ℎ𝑒𝑟 ⋈ Π𝑇𝑛𝑜(𝑊𝑜𝑟𝑘 ⋈ Π𝑇𝑛𝑜= ′𝑇1′ (𝐷𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡)))
假设对关系 r, ρx(r)表示得到别名为 x 的一个相同的关系，系里面的每个教师都有工资，列出比 D1 系的所有教师工资都高的教师代码：Π𝑇𝑛𝑜(𝑊𝑜𝑟𝑘) − (Π𝑊𝑜𝑟𝑘.𝑇𝑛𝑜 (𝑊𝑜𝑟𝑘 ⋈𝑊𝑜𝑟𝑘.𝑆𝑎𝑙𝑎𝑟𝑦≤𝑊𝑜𝑟𝑘2.𝑆𝑎𝑙𝑎𝑟𝑦∧𝑊𝑜𝑟𝑘2.𝐷𝑛𝑜=′𝐷1′𝜌𝑊𝑜𝑟𝑘2 (𝑊𝑜𝑟𝑘)))

Teacher

Tno	Tname	Tage	Tsex
T1	张丽	42	女
T2	李波	45	男
T3	王艳	33	女
T4	赵明	29	男

Work

Tno	Dno	Year	Salary
T1	D1	1995	6000
T2	D2	1992	6500
T3	D1	2005	4500

Department

Dno	Dname	Tno
D1	计算机系	T1
D2	数学系	T2
D3	电子系	NULL

关系系统

表式系统：仅支持关系（即表）数据结构，不支持关系（即集合）操作
（最小）关系系统：仅支持关系数据结构和三种关系搡作
关系完备的系统：这类系统支持关系数据结构和所有的关系代数操作（功能上与关系代数等价）
全关系系统：这类系统支持关系模型的所有特征。即不仅是关系上完备的而且支持数据结构中域的概念，支持实体完整性和参照完整性

SQL

SQL功能：

数据定义：CREATE、DROP、ALTER
数据查询：SELECT
数据操纵：INSERT、UPDATE、DELETE
数据控制：GRANT、REVOKE

数据定义

操作对象	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

定义模式：

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

模式名没指定的话默认是用户名，要创建模式，需要有数据库admin权限或admin给的 CREATE SCHEMA 权限

删除模式：

DROP SCHEMA <模式名><CASCADE | RESTRICT>;

其中 CASCADE 和 RESTRICT 两者必选其一

CASCADE（级联）：在删除模式的同时把该模式中所有的数据库对象全部删除

RESTRICT（限制）：如果在该模式中已经定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。只有当该模式中没有任何下属的对象时才能执行 DROP SCHEMA 语句

定义基本表

CREATE TABLE <表名>(<列名><数据类型> [列级完整性约束条件]
                  [,<列名><数据类型> [列级完整性约束条件]]
                  ...
                  [,<表级完整性约束条件>]);

例：建立一个“学生”表Student：

CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,	/*列级完整性约束条件，Sno是主码*/
 Sname CHAR(20) UNIQUE,		/*Sname取唯一值*/
 Ssex CHAR(2) NOT NULL,		/*约束强制列不接受NULL值，即始终包含值*/
 Sage SMALLINT,
 Sdept CHAR(20)
);

索引的建立与删除

建立索引：使用CREATE INDEX语句

CREATE [UNIQUE][CLUSTER] INDEX <索引名>
ON <表名>(<列名>[<次序>][,<列名>[<次序>]]...);

修改索引：

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

删除索引：

DROP INDEX <索引名>;

数据查询

SELECT [ALL | DISTINCT] <目标列表达式> [,<...>]...
FROM <表名或视图名> [,<...>...] | (<SELECT语句>) [AS] <别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]
[ORDER BY <列名2> [ASC | DESC];

根据 WHERE 子句的条件表达式从 FROM 子句指定的基本表、视图或派生表中找出满足条件的元组，再按 SELECT 子句中的目标列表达式选出元组中的属性值形成结果表

如果有 GROUP BY 子句，则将结果按 <列名1> 的值进行分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。如果 GROUP BY 子句带 HAVING 短语，则只有满足指定条件的组才予以输出

如果有 ORDER BY 子句，则结果表还要按 <列名2> 的值的升序或降序排序

单表查询

选择表中的若干列

查询指定列：

SELECT Sno,Sname
FROM Student;

查询全部列：

SELECT *
FROM Student

查询经过计算的值：

SELECT Sname,'Year of Birth:',2014-Sage ,LOWER(Sdept)
FROM Student;

Screenshot_2024-04-17-14-33-00-261_com.orion.notein-edit

指定别名：

SELECT Sname NAME,'Year of Birth:' BIRTH,2014-Sage BIRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student;

Screenshot_2024-04-17-14-33-42-004_com.orion.notein-edit

选择表中的若干元组

常用的查询条件：

查询条件	谓词
比较	=，>，<，>=，<=，!=，<>，!>，!<; NOT+上述比较运算符
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE, NOT LIKE
空值	IS NULL, IS NOT NULL
多重条件（逻辑运算）	AND, OR, NOT

消除取值重复的行：指定DISTINCT可以去掉结果表中的重复行，如果没指定DISTINCT，则默认为ALL

SELECT DISTINCT Sno
FROM SC;

查询满足条件的元组：WHERE子句实现

WHERE查询条件：比较、确定范围（BETWEEN AND, NOT BETWEEN AND）、确定集合（IN, NOT IN）、字符匹配（LIKE, NOT LIKE）、空值（IS NULL, IS NOT NULL）、多重条件（逻辑运算）

比较大小：

SELECT Sname,Sage
FROM Student
WHERE Sage<20;

确定范围：BETWEEN ... AND ...

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

确定集合：IN查找属性值属于指定集合的元组
```
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ('CS','MA','IS');
```
!=ALL与NOT IN等价
字符匹配：LIKE，如果匹配串中不含通配符，则可以用=替换；同理，NOT LIKE可以用!=或<>取代
```
[NOT] LIKE '<匹配串>' [ESACPE '<换码字符>']
```
通配符%：代表任意长度的字符串
通配符_：代表任意单个字符

如果查询字符串本身有 % 或 _ ，则要用ESCAPE '<换码字符>'进行转义

SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE '\';

这样_就不会被解释为通配符

涉及空值的查询：

SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;

多重条件查询：

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

ORDER BY子句

可用指定ASC升序或DESC降序，默认值为升序

聚集函数

COUNT(*)    统计元组个数
COUNT( [DISTINCT | ALL] <列名>)    统计一列中值的个数
SUM( [DISTINCT | ALL] <列名>)    计算一列值的总和（此列必须是数值型）
AVG( [DISTINCT | ALL] <列名>)    计算一列值的平均值（此列必须是数值型）
MAX( [DISTINCT | ALL] <列名>)    求一列值中的最大值
MIN( [DISTINCT | ALL] <列名>)    求一列值中的最小值

当聚集函数遇到空值时，除COUNT(*)外，都跳过空值而只处理非空值，COUNT(*)是对元组进行计数，某个元组的一个或部分列取空值不影响统计结果

注：WHERE子句是不能用聚集函数作为条件表达式的，聚集函数只能用于 SELECT 子句和 GROUP BY 中的 HAVING 子句

GROUP BY 子句

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

查询结果按 Cno 的值分组，所以具有相同 Cno 值的元组为一组

连接查询

等值与非等值连接查询

连接条件/连接谓词：[<表名1.>]<列名1><比较运算符>[<表名2>.]<列名2>

当连接运算符为 = 时，称为等值连接，其它为非等值连接

连接谓词中的列名称为连接字段，字段类型必须是可比的

例：
```
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno -- 将Student与SC中同一学生的元组连接起来
```

自身连接

SELECT FIRST.Cno,SECOND.Cpno
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno

外连接：悬浮元组填NULL

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno)

多表连接：先进行两个表的连接操作，再将其连接结果与第三个表进行连接

嵌套查询

查询块：SELECT-FROM-WHERE语句

嵌套查询：查询块套查询块

SELECT Sname,Sage
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno='2')

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

EXISTS：带有 EXISTS 谓词的子查询不返回任何数据，只产生true或false

例：查询所有选修了1号课程的学生姓名
```
SELECT Sname
FROM Student
WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno='1')
```

集合查询

SELECT 语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作

并操作UNION

交操作INTERSECT

差操作EXCEPT

SELECT Sname,Sage
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';

基于派生表的查询

数据更新

插入数据

INSERT
INTO <表名> [(<属性列1>[,<属性列2>]...)]
VALUES (<常量1> [,<常量2>] ...);

插入子查询结果

INSERT
INTO <表名> [(<属性列1>[,<属性列2>]...)]
子查询;

修改数据

UPDATE <表名>
SET <列名>=<表达式> [,<列名>=<表达式>]...
[WHERE <条件>];

删除数据

DELETE <表名>
FROM <表名>
[WHERE <条件>];

视图

从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表

就像一个窗口，能看到数据库中自己感兴趣的数据及其变化

定义视图

建立视图：

CREATE VIEW <视图名> [(<列名> [,<列名>] ...)]
AS <子查询>
[WITH CHECK OPTION]

删除视图：

DROP VIEW <视图名> [CASCADE];

查询视图

更新视图

视图的作用

能够简化用户的操作
使用户能以多种角度看待同一数据
对重构数据库提供了一定程度的逻辑独立性
对机密数据提供安全保护

数据库安全性

安全标准

TCSEC/TDI 安全级别划分：

安全级别	定义
A1	验证设计
B3	安全域
B2	结构化保护
B1	标记安全保护（这个级别的产品才被认为是真正意义上的安全产品）
C2	受控的存取保护。如Windows 2000、Oracle 7
C1	自主安全保护。能够实现对用户和数据的分离，进行自主存取控制（DAC）
D	最小保护。例如DOS，具有操作系统的基本功能，如文件系统、进程调度等

CC 评估保证级（EAL）的划分：

评估保证级	定义	TCSEC安全级别（近似相当）
EAL1	功能测试
EAL2	结构测试	C1
EAL3	系统地测试和检查	C2
EAL4	系统地设计、测试和复查	B1
EAL5	半形式化设计和测试	B2
EAL6	半形式化验证的设计和测试	B3
EAL7	形式化验证的设计和测试	A1

安全性控制

用户身份鉴别

用户标识（username + UID）

静态口令鉴别
动态口令鉴别
生物特征鉴别
智能卡鉴别

存取控制

C2级的数据管理系统支持自主存取控制（DAC），B1级的数据库管理系统支持强制存取控制（MAC）

定义用户权限，并将用户权限登记到数据字典中
合法权限检查

自主存取控制方法

用户权限由两个要素构成：数据库对象和操作类型

在数据库系统中，定义存取权限称为授权（authorization）

在非关系系统中，用户只能对数据进行操作，存取控制的数据库对象也仅限于数据本身

在关系数据库系统中，存取控制的对象不仅有数据本身（基本表中的数据、属性列上的数据），还有数据库模式（包括数据库、基本表、视图和索引的创建等）

授权：授予与收回

GRANT语句：向用户授予权限

GRANT <权限> [,<权限>]...
ON <对象类型> <对象名> [,<对象类型> <对象名>]...
TO <用户> [,<用户>]...
[WITH GRANT OPTION];

SQL标准允许具有 WITH GRANT OPTION 的用户把相应权限或其子集传递授予其他用户，但不允许循环授权，即被授权者不能把权限再授回给授权者或者其祖先

例：

GRANT SELECT
ON TABLE Student
TO U1;
-- 把查询Student表的权限授给用户U1

GRANT ALL PRIVILEGES
ON TABLE Student,Course
TO U2,U3;
-- 把对Student表和Course表的全部操作权限授予用户U2和U3

GRANT SELECT
ON TABLE SC
TO PUBLIC;
-- 把对表SC的查询权限授予所有用户

GRANT UPDATE(Sno),SELECT
ON TABLE Student
TO U4;
-- 把查询Student表和修改学生学号的权限授给用户U4

GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;
-- 把对表SC的INSERT权限授予U5用户，并允许将此权限再授予其他用户

GRANT INSERT
ON TABLE SC
TO U6;
-- U5授予U6权限，但是没给传播的权限，因此U6不能再传播此权限

REVOKE语句：收回已经授予用户的权限

REVOKE <权限> [,<权限>]...
ON <对象类型> <对象名> [,<对象类型> <对象名>]...
FROM <用户> [,<用户>]...[CASCADE|RESTRICT];

例：

REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;
-- 把用户U4修改学生学号的权限收回

REVOKE SELECT
ON TABLE SC
FROM PUBLIC;
-- 收回所有用户对表SC的查询权限

REVOKE INSERT
ON TABLE SC
FROM U5 CASCADE;
-- 把用户U5对SC表的INSERT权限收回

从上面的例子可以发现，用户可以”自主“地决定将数据的存取权限授予何人、决定是否也将”授权“的权限授予别人

因此称这样的存取控制是自主存取控制

CREATE USER语句：创建数据库模式的权限

CREATE USER <username> [WITH] [DBA|RESOURCE|CONNECT];

只有系统的超级用户才有权创建一个新的数据库用户
新创建的数据库用户有三种权限：CONNECT、RESOURCE和DBA，默认是CONNECT权限

权限与可执行的操作如下表：

拥有的权限	CREATE USER	CREATE SCHEMA	CREATE TABLE	登录数据库，执行数据查询和操纵
DBA	可以	可以	可以	可以
RESOURCE	不可以	不可以	可以	可以
CONNECT	不可以	不可以	不可以	可以，但必须拥有相应权限

数据库角色

数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合

角色的创建
```
CREATE ROLE <角色名>
```
刚创建的角色是空的，可以用GRANT给角色授权，和上面授权一样
将一个角色授予其他的角色或用户，同上
收回同上

强制存取控制方法

敏感度标记：绝密（Top Secret，TS）、机密（Secret，S）、可信（Confidential，C）、公开（Public，P）

视图机制

例：建立计算机系学生的视图，把对该视图的 SELECT 权限授予王平，把该视图上的所有操作权限授予张明

CREATE VIEW CS_Student
AS
SELECT *
FROM Student
WHERE Sdept='CS';
/*先建立视图CS_Student*/

GRANT SELECT
ON CS_Student
TO 王平;
/*王平只能检索计算机系学生的信息*/

GRANT ALL PRIVILEGES
ON CS_Student
TO 张明;
/*具有检索和增删改计算机系学生信息的所有权限*/

审计

审计——数据库管理系统达到 C2 以上安全级别必不可少的一项指标

审计功能把用户对数据库的所有操作自动记录下来放入审计日志中

审计事件
- 服务器事件
- 系统权限
- 语句事件
- 模式对象事件
审计功能
- 基本功能
- 提供多套审计规则
- 提供审计分析和报表功能
- 审计日志管理功能
- 系统提供查询审计设置及审计记录信息的专门视图
AUDIT 语句和 NOAUDIT 语句

前者用来设置审计功能，后者则取消审计功能

例：
```
AUDIT ALTER,UPDATE
ON SC;
-- 对修改SC表结构或修改SC表数据的操作进行审计

NOAUDIT ALTER,UPDATE
ON SC；
-- 取消对SC表的审计
```
审计设置以及审计日志一般都存储在数据字典中。必须把审计开关打开（audit_trail = true），才能在系统表 SYS_AUDITTRAIL 中看到审计信息

数据加密

数据库完整性

指数据的正确性和相容性

实体完整性

在 CREATE TABLE 中用PRIMARY KEY定义，对单属性构成的码有两种说明方法，一种是定义为列级约束条件，另一种是定义为表级约束条件；对多属性构成的码只有后者

CREATE TABLE Student
    (Sno CHAR(9) PRIMARY KEY,    /*在列级定义主码*/
    Sname CHAR(20) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
    );

或者

CREATE TABLE Student
    (Sno CHAR(9),
    Sname CHAR(20) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20),
    PRIMARY KEY(Sno)    /*在表级定义主码*/
    );

实体完整性检查和违约处理：

用 PRIMARY KEY 定义了关系的主码后，每当用户程序对基本表插入一条记录或对主码列进行更新操作时，关系数据库管理系统将自动进行检查：

检查主码值是否唯一，如果不唯一则拒绝插入或修改
检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改

检查记录中的主码值：全表扫描，但是需要建立索引（如B+树索引）

参照完整性

在CREATE TABLE中用FOREIGN KEY定义哪些为外码，用REFERENCES指明这些外码参照哪些表的主码

CREATE TABLE SC
    (Sno CHAR(9) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno),    /*在表级定义实体完整性*/
    FOREIGN KEY(Sno) REFERENCES Student(Sno)    /*在表级定义参照完整性*/
    FOREIGN KEY(Cno) REFERENCES Student(Cno)    /*在表级定义参照完整性*/

拒绝（NO ACTION）执行：不允许该操作执行，该策略一般设置为默认策略

级联（CASCADE）操作：当删除或修改被参照表的一个元组导致与参照表的不一致时，删除或修改参照表中的所有导致不一致的元组

设置为空值

用户定义的完整性

关系数据理论

参考：https://www.cnblogs.com/linfangnan/p/16675859.html

规范化

规范化理论：是用来改造关系模式，通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异常、更新异常和数据冗余问题

一个不好的模式设计：

以借书为例，建立一个数据库存储借书信息，包括书号、书名、出版社、学号和借书日期。得到的关系模式的属性集合为：U = {书号, 书名, 类别号, 类别名, 出版社, 学号, 借书日期}

数据依赖是一个关系内部属性与属性之间的一种约束关系，通过属性间值的相等与否体现出来的数据间相关联系

一本书对应一个类别号；
一个类别号对应一个类别名；
一个出版社可以出版多本书，一本书通常情况下对应一个出版社；
一本书可以在不同的时间被不同的同学借走，也可以被同一位同学多次借阅。

某一时刻该关系模式的实例为：

不难看出，当对这个实例进行增删改查操作时将发生以下问题：

异常情况	举例
数据冗余	同一本书的类别号、类别名和出版社重复出现，占用大量空间
更新异常	当某本书的出版社写错时，需要修改所有这本书的借书记录
插入异常	如果一本书没有任何同学借过，数据库就无法存储这本书的信息
删除异常	如果借过同一本书的所有同学都退学了，这本书的信息就会丢失

函数依赖

若在一张表中，在属性（或属性组）X的值确定的情况下，必定能确定属性Y的值，那么就可以说Y函数依赖于X，写作 X → Y

码

若K为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K，那么我们称 K 为候选码，简称为码

例子：

关系模式R(A,B,C,D) 中，存在函数依赖关系{A→B,A→C,A→D,(B,C)→A} , 则侯选码是A和(B,C)

范式

一个好的模式应当不会发生插入异常、删除异常和更新异常，数据冗余应尽可能少。根据关系数据库中的关系满足的不同程度，可以归纳为多种范式

满足最低要求的叫第一范式 1NF，在第一范式中满足进一步要求的为第二范式，其余以此类推

若关系模式 R 满足范式 xNF，记 R∈xNF

不同级别的范式要求各不相同，因此范式可以作为衡量一个关系模式好坏的标准，将低一级范式的关系模式通过模式分解转换为高一级范式的关系模式集合的过程称为规范化。规范化的基本思想是逐步消除数据以来中不合适的部分，实质上是概念的单一化。

1NF

作为一个二维表，关系要符合一个最基本的条件：每一个分量必须是不可分的数据项。即不能以集合、序列等作为属性值，不能有大表套小表的情况，满足了这个条件的关系模式就属于第一范式（1NF)

2NF

若 R∈1NF，且每一个非主属性完全依赖于 R 的候选码，则 R∈2NF，2NF 在 1NF 的基础上消除了非主属性对码的部分依赖

3NF

若 R∈1NF，且每一个非主属性都不传递依赖于 R 的候选码，则 R∈3NF，3NF 消除了非主属性对码的传递函数依赖

BCNF

若 R∈1NF，且每一个属性都不传递依赖于 R 的候选码，则 R∈BCNF

例如：

关系模式 SJP(S,J,P)中，S 是学生，J 表示课程，P 表示名次。每一个学生选修每门课程的成绩有一定的名次，每门课程中每一名次只有一个学生（即没有并列名次）。

可得到下面的函数依赖：

F = {(S,J)→P, (J,P)→S}

可知 (S,J) 与 (J,P) 都可以作为候选码，这两个码各由两个属性组成且相交。这个关系模式中显然没有属性对码传递依赖或部分依赖，所以 SJP∈3NF，而且除 (S,J)与(J,P)以外没有其他决定因素，所以 SJP∈BCNF

再看一个不是BCNF的例子：

例如关系模式 STJ(S,T,J)中，S 表示学生，T 表示教师，J 表示课程。每一教师只教一门课，每门课有若干教师，某一学生选定某门课，就对应一个固定的教师。

可得到如下的函数依赖：

F = {(S,J)→T, (S,T)→J, T→J}

可知 (S, J)、(S, T) 都是候选码，因为没有任何非主属性对码传递依赖或部分依赖，所以 STJ 是 3NF。但 STJ 不是 BCNF，因为 T 是决定因素但是 T 不包含码（T -> J）

4NF

多值依赖

DBMS

数据库管理系统（Database Management System）是数据库系统的核心，它是以事务为单位，在操作系统上运行的系统软件，用于建立、使用和维护数据库。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。

组成

按功能划分：

模式翻译：提供数据定义语言（ddl）。用它书写的数据库模式被翻译为内部表示。数据库的逻辑结构、完整性约束和物理储存结构保存在内部的数据字典中。数据库的各种数据操作（如查找、修改、插入和删除等）和数据库的维护管理都是以数据库模式为依据的
应用程序的编译：把包含着访问数据库语句的应用程序，编译成在dbms支持下可运行的目标程序
交互式查询：提供易使用的交互式查询语言，如sql。dbms负责执行查询命令，并将查询结果显示在屏幕上
数据的组织与存取：提供数据在外围储存设备上的物理组织与存取方法
事务运行管理：提供事务运行管理及运行日志，事务运行的安全性监控和数据完整性检查，事务的并发控制及系统恢复等功能
数据库的维护：为数据库管理员提供软件支持，包括数据安全控制、完整性保障、数据库备份、数据库重组以及性能监控等维护工具

特点

数据结构化
数据的共享性高，冗余度低，易扩充
数据独立性高：包括数据的物理独立性和逻辑独立性
数据由DBMS统一管理和控制：数据库的共享是并发的（concurrency）共享，即多个用户可以同时存取数据库中的数据，甚至可以同时存取数据库中的同一个数据

数据库设计

数据库设计是指对于一个给定的应用环境，构造（设计）优化的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效地存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据操作要求

特点

三分技术，七分管理，十二分基础数据：强调数据的收集、整理、组织和不断更新是数据库建设中的重要环节

设计方法

基本步骤：

需求分析
概念结构设计：整个数据库设计的关键
逻辑结构设计
物理结构设计
数据库实施
数据库运行和维护

需求分析

结构化分析（SA）方法：自顶向下、逐层分解

数据字典：包括数据项、数据结构、数据流、数据存储和处理过程，其中数据项是数据的最小组成单位，若干个数据项可以组成一个数据结构

概念结构设计

概念模型

特点：

能真实、充分地反映现实世界，包括事物和事物之间的联系，能满足用户对数据的处理要求，是现实世界的一个真实模型
易于理解
易于更改
易于向关系、网状、层次等各种数据模型转换

E-R模型

E-R图：提供了表示实体型、属性和联系的方法

实体型：用矩形表示，矩形框内写明实体名
属性：用椭圆形表示，并用无向边将其与相应的实体型连接起来
联系：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体型连接起来，同时在无向边旁标上联系的类型（1:1、1:n 或 m:n）

1对1：

一个员工可以领取一台办公电脑，办公电脑只要有员工编号，就可以通过编号查询到这台办公电脑

实体如下：

员工：属性有工号、姓名、性别
办公电脑：属性有编号、配置信息

E-R图之间的冲突：

属性冲突
命名冲突
结构冲突

逻辑结构设计

把概念结构设计阶段设计好的基本 E-R 图转换为与选用数据库管理系统产品所支持的数据模型相符合的逻辑结构

任务：设计关系模式

E-R图向关系模型的转换

一般原则：一个实体型转换为一个关系模式，关系的属性就是实体的属性，关系的码就是实体的码

实体型间的联系：

一个 1:1 联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并
一个 1:n 联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并
一个 m:n 联系转换为一个关系模式
三个或三个以上实体间的一个多元联系可以转换为一个关系模式
具有相同码的关系模式可合并

例：

转化后的关系模式：

工厂(工厂编号，厂名，地址)

产品(产品编号，产品名，规格)

职工(职工号，姓名，工厂编号，聘期，工资)

生产(工厂编号，产品编号，计划数量)

数据库恢复技术

事务

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

例如，在关系数据库中，一个事务可以是一条SQL语句、一组SQL语句或整个程序

在SQL中，定义事务的语句一般有三条：

BEGIN TRANSACTION;
COMMIT;
ROLLBACK;

事务通常以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束

COMMIT：提交事务的所有操作

ROLLBACK：表示回滚，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始的状态

事务的ACID特性：

原子性（Atomicity）：事务是数据库的逻辑工作单位，事务中包括的所有操作要么都做，要么都不做
一致性（Consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态
隔离性（Isolation）：一个事务的执行不能被其他事务干扰。即一个事务的内部操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰
持续性（Durability）：也称永久性，指一个事务一旦提交，对数据库的改变是永久的

故障的种类

事务内部的故障
系统故障
介质故障
计算机病毒

恢复的实现技术

数据转储

数据库管理员定期地将整个数据库复制到磁带、磁盘或其他存储介质上保存起来的过程，这些备用的数据称为后备副本

重装后备副本只能将数据库恢复到转储时的状态

静态转储：是在系统中无运行事务时的转储操作
动态转储：转储期间允许对数据库进行存取或修改，即转储和用户事务可以并发执行。但是不能保证转储结束时后备副本上的数据正确有效

为此，必须把转储期间各事务对数据库的修改活动登记下来，建立日志文件（log file）

登记日志文件

必须遵循两条原则：

登记的次序严格按并发事务执行的时间次序
必须先写日志文件，后写数据库

恢复策略

事务故障的恢复

系统故障的恢复

介质故障的恢复

目录

LOADING

数据库系统概论

前言

绪论

数据模型

概念模型

数据模型的组成要素

层次模型

网状模型

关系模型

数据库系统的三级模式结构

（概念）模式

外模式

内模式

数据库的二层映像功能与数据独立性

关系数据库

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

关系模式

关系数据库

关系操作

基本关系操作

关系数据语言的分类

关系的完整性

实体完整性

参照完整性

用户定义的完整性

关系代数

集合运算

关系运算

关系系统

SQL

数据定义

定义基本表

索引的建立与删除

数据查询

单表查询

选择表中的若干列

选择表中的若干元组

ORDER BY子句

聚集函数

GROUP BY 子句

连接查询

嵌套查询

集合查询

基于派生表的查询

数据更新

插入数据

修改数据

删除数据

视图

定义视图

查询视图

更新视图

视图的作用

数据库安全性

安全标准

安全性控制

用户身份鉴别

存取控制

自主存取控制方法

授权：授予与收回

数据库角色

强制存取控制方法

视图机制

审计

数据加密

数据库完整性

实体完整性

参照完整性

用户定义的完整性

关系数据理论

规范化

函数依赖

码

范式

1NF

2NF

3NF

BCNF