数据库原理与安全 | 笔记1

第一章绪论

1.1 数据库系统概述

（1）数据

描述事物的符号记录叫做数据（Data），是数据库中存储的基本对象。
数据的种类
1. 结构化数据：可以使用关系型数据库进行表示和存储，可以表示为规范的二维表格形式。
2. 半结构化数据：不符合关系数据模型结构，但包含相关标记用来分隔语义元素，并对记录和字段进行分层。例如XML、JSON格式数据。
3. 非结构化数据：没有固定结构，如图片、视频等。
信息 = 数据 + 处理。信息是具有时效性的，有一定含义的，有逻辑的、经过加工处理的、对决策有价值的数据流。
数据是符号化的信息；信息是语义化的数据。
举例：学生档案中的学生记录（数据）：（李明，男，199505，江苏南京市，计算机系，2013）语义：学生姓名、性别、出生年月、出生地、所在院系、入学时间解释：李明是大学生，1995年5月出生，江苏南京市人，2013年考入计算机系上述语义集成起来，就构成了“信息”
知识
数据挖掘

（2）数据库

数据库（Database，简称DB）是长期储存在计算机内、有组织的、可共享的大量数据的集合。
数据库的基本特征：
- 数据按一定的数据模型组织、描述和储存
- 可为各种用户共享
- 冗余度较小
- 数据独立性较高
- 易扩展

（3）数据库管理系统

数据库管理系统（DataBase Management System, DBMS）是位于用户与操作系统之间的一层数据管理软件，帮助用户定义、创建、维护和控制数据库访问的软件，是基础软件，是一个大型复杂的软件系统。
数据库管理系统DBMS在数据库建立、运用和维护时对数据库进行统一管理和控制，以保证数据的完整性、安全性，并具备多用户同时使用数据库的并发控制，在发生故障后对数据库进行恢复。
主要功能：
1. 数据定义功能
  
  提供数据定义语言（DDL）；定义数据库中的数据对象
2. 数据组织、存储和管理
  
  分类组织、存储和管理各种数据；确定组织数据的文件结构和存取方式；实现数据之间的联系；提供多种存取方法提高存取效率
3. 数据操纵功能
  
  提供数据操纵语言（DML）；实现对数据库的基本操作：增、删、改、查
4. 数据库的事务管理盒运行管理
  
  数据库在建立、运行和维护时，由DBMS统一管理和控制；保证数据的安全性、完整性；支持多用户对数据的并发使用；发生故障后的系统数据恢复
5. 数据库的建立和维护功能
6. 其他功能

（4）数据库系统

数据库系统（DataBase System, DBS），简称数据库
数据库系统的构成：数据库(Database)、数据库管理系统（DBMS及其应用开发工具）、应用程序(Application)、数据库管理员(DBA)、用户

（5）数据库系统的特点

数据结构化
- 这是数据库的主要特征之一，是数据库系统与文件系统的本质区别
- 数据内部和整体都是结构化的，数据之间是有联系的
数据的共享性高，冗余度低且易扩充
数据独立性高
- 物理独立性：应用程序和数据库中的数据物理存储是相互独立的。
- 逻辑独立性：应用程序和数据库中的数据的逻辑结构是相互独立的。
数据由DBMS统一管理和控制
- 安全性保护：防止数据被泄露和破坏
- 完整性保护：指数据的正确、有效、相容
- 并发控制
- 数据库恢复：有从错误状态恢复到某一正确、完整状态的功能

1.2 数据模型

数据模型是对现实世界数据特征的抽象。
数据模型应满足三方面要求：
- 能比较真实地模拟现实世界
- 容易为人所理解
- 便于在计算机上实现
数据模型是数据库系统的核心和基础

1.2.1 两类数据模型

数据模型分为两类：

概念模型

也称信息模型，它是按用户的观点来对数据和信息建模，用于数据库的初始概念设计，反映对用户业务需求的基本理解
逻辑模型和物理模型

逻辑模型按计算机系统的观点对数据建模，建立的是具体应用的数据库结构，用于DBMS实现，但可不依赖具体的数据库软件厂商和版本。主要包括网状模型、层次模型、关系模型、面向对象数据模型、对象关系数据模型、半结构化数据模型等。逻辑模型：系统概要设计（数据建模）阶段

物理模型是对数据最底层的抽象，描述数据在系统内部的表示方式和存取方法，在磁盘或磁带上的存储方式和存取方法。物理模型：系统详细设计（数据建模）阶段

数据建模是业务需求理解和概念抽象的过程。抽象过程：理解-区分-命名-表达。

数据建模的三个层次：

概念建模：需求分析阶段
- 需求调研
- 理解需求：站在用户的角度
- 提取实体
逻辑建模：系统分析阶段
- 细化实体属性
- 分析实体与实体之间的逻辑关系
- 实体及其关系规范化（关系数据建模）：范式分解、第三范式（3NF）
物理建模：系统详细设计阶段
- 结合实际数据库微调
- 参考3NF，考虑性能，适当冗余
- 主/外键、约束、索引、触发器等

1.2.2 概念模型

概念模型的一种表示方法：实体-联系方法，Entity Relationship Diagram (ERD)

Crow’s Foot/Martin/Information Engineering style：

实体：人、事、物或抽象的概念。
属性：实体所具有的某一特性称为属性。
码/键：唯一标识实体的属性集称为码。
实体型：用实体名及其属性名集合来抽象和刻画同类实体称为实体型。（结构，类，class）
实体集：同一类型实体的集合称为实体集。（实例，instance）
联系：现实世界中事物内部以及事物之间的联系在信息世界中反映为实体（型）内部的联系和实体（型）之间的联系。
- 实体内部的联系通常是指组成实体的各属性之间的联系
- 实体之间的联系通常是指不同实体集之间的联系
- 实体之间的联系有一对一(1:1)、一对多(1:m)和多对多(m:n)等多种类型

1.2.3 数据模型的组成要素

数据模型的组成：

数据结构
数据操作
数据的完整性约束条件

数据结构：

描述数据库的组成对象，以及对象之间的联系
是对系统静态特性的描述

数据操作：

对数据库中各种对象（型）的实例（值）允许执行的操作的集合，包括操作及有关操作规则
操作包括：
- 查询（Select）
- 更新：插入（Insert）、删除（Delete）、修改（Update）

数据的完整性约束条件：

是一组完整性规则的集合
完整性规则：给定的数据模型中数据及其联系所具有的制约和依存规则
提供定义完整性约束条件的机制，以反映具体应用所涉及的数据必须遵守的特定的语义约束条件。

1.2.4 常用的数据模型

层次模型（Hierarchical Model）网状模型（Network Model）关系模型（Relational Model)）面向对象数据模型（Object Oriented Data Model）对象关系数据模型（Object Relational Data Model）半结构化数据模型（Semi-structured Data Model）图数据模型（Graph Data Model）

1.2.5 层次模型

层次模型用树形结构来表示各类实体以及实体间的联系。

特点：

结点的双亲是唯一的
只能处理一对多的实体关系
没有一个子女记录值能够脱离双亲记录值而独立存在

优点：

数据结构简单清晰
查询效率高
有良好的完整性支持

缺点：

多对多的联系不自然
对插入和删除操作的限制多，应用程序的编写比较复杂
查询子女结点必须通过双亲结点

1.2.6 网状模型

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

特点：

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

区别：

网状模型允许多个结点没有双亲结点
网状模型允许结点有多个双亲结点
网状模型允许两个结点之间有多种联系（复合联系）
层次模型实际上是网状模型的一个特例

1.2.7 关系模型

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

在用户观点下，关系模型中数据的逻辑结构是一张二维表，它由行和列组成：

关系：对应一张表（包括表中的数据）
元组：表中的一行
属性：表中的一列
主码/主键：表中的某个属性组，可以唯一确定一个元组
域：是一组具有相同数据类型的值的集合，例如性别的域是（男，女）
分量：元组中的一个属性值
关系模式：对关系的结构描述：关系名（属性1，属性2，…，属性n）关系必须是规范化的，满足一定的规范条件最基本的规范条件：关系的每一个分量必须是一个不可分的数据项，不允许表中还有表

关系模型的操纵与完整性约束：

数据操作是集合操作，操作对象和操作结果都是关系
关系的完整性约束条件：
- 实体完整性
- 参照完整性
- 用户定义的完整性

关系模型的优点：

建立在严格的数学概念的基础上
概念单一：实体和各类联系都用关系来表示、对数据的检索结果也是关系
关系模型的存取路径对用户透明

关系模式的缺点：

存取路径对用户透明，查询效率往往不如格式化数据模型（指层次模型和网状模型）
为提高性能，必须对用户的查询请求进行优化，增加了开发数据库管理系统的难度

1.3 数据库系统的结构

从数据库应用开发人员角度看，数据库系统通常采用三级模式结构，是数据库系统内部的系统结构。

相关概念：

型（Type）：对某一类数据的结构和属性的说明
值（Value）：是型的一个具体赋值

例如：学生记录（类型）：（学号，姓名，性别，系别，年龄，籍贯）-- 记录型一个记录值：（201315130，李明，男，计算机系，19，江苏南京市）
模式（Schema）：
- 数据库逻辑结构和特征的描述
- 是型的描述，不涉及具体值
- 反映的是数据的结构及其联系
- 模式是相对稳定的
实例（Instance）：
- 模式的一个具体值
- 反映数据库某一时刻的状态

三层模式结构：

模式（Schema）
- 模式（也称逻辑模式）是数据库中全体数据的逻辑结构和特征的描述 所有用户的公共数据视图
- 一个数据库只有一个模式
- 模式的地位：是数据库系统模式结构的中间层与数据库的物理存储细节、应用程序、开发工具、高级语言无关
- 模式的定义：数据的逻辑结构（数据项的名字、类型、取值范围）数据之间的联系安全性、完整性要求可以使用ERD表达和描述
外模式（External Schema）
- 数据库用户（包括应用程序员和最终用户）使用的局部数据的逻辑结构和特征的描述
- 外模式的地位：介于模式与应用之间
- 模式与外模式的关系：一对多外模式通常是模式的子集一个数据库可以有多个外模式
- 外模式与应用的关系：一对多同一外模式也可以为某一用户的多个应用系统所使用但一个应用程序只能使用一个外模式
- 用途：但一个应用程序只能使用一个外模式每个用户只能看见和访问所对应的外模式中的数据
内模式（Internal Schema）
- 是数据物理结构和存储方式的描述
- 是数据在数据库内部的表示方式：包括记录的存储方式、索引的组织方式、数据是否压缩、是否加密等等
- 一个数据库只有一个内模式
- 内模式对用户是“半透明”的

三级模式是对数据的三个抽象级别。

二级映象在数据库管理系统内部实现这三个抽象层次的联系和转换：

外模式／模式映像
模式／内模式映像

1.4 数据库系统的组成

组成：

硬件平台及数据库
软件
人员

第二章关系数据库

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

2.1.1 关系

关系是单一的数据结构，现实世界的实体以及实体间的各种联系均用关系来表示。

从用户角度，关系模型中数据的逻辑结构是一张二维表的表头，描述表的组成关系。

域（Domain）

域是一组具有相同数据类型的值的集合，值的集合是有限的、且不重复。

例子：整数、介于某个取值范围的整数、{男，女}
笛卡尔积（Cartesian Product）

给定一组域D1，D2，…，Dn，允许其中某些域是相同的，是所有域的所有取值的一个组合

组合结果不允许重复

笛卡尔积中的每一个元素（d1，d2，…，dn）叫作一个n元组（n-tuple）

元组中的每一个值di 叫作一个分量

基数是集合中包含的元素的个数

笛卡尔积可表示为一张二维表，表中的每行对应一个元组，表中的每列对应一个域
关系（Relation）

D1×D2×…×Dn 的子集叫作在域D1，D2，…，Dn 上的关系，表示为：R（D1，D2，…，Dn）

R为关系名字，n为关系的目或度（degree）

关系中的每个元素是关系中的元组，通常用t 表示

当n=1时，称该关系为单元关系(Unary relation)，或一元关系

当n=2时，称该关系为二元关系（Binary relation）

关系可以表示为一张二维表，表中的每行对应一个元组，表中的每列对应一个域

每列起个名字，称为属性，n目关系必有n个属性

码/键（Key）：

候选键/码（Candidate key）

若关系中的某一属性组的值能唯一地标识一个元组，则称该属性组为候选码

需要满足的条件：
1. 这个属性集合始终能够确保在关系中能唯一标识元组
2. 在这个属性集合中找不出真子集能够满足条件
有时，关系中有很多组候选码

简单的情况：候选码只包含一个属性，如学生实体中的学号S#
全码/键（All-key）

最极端的情况：关系模式的所有属性组是这个关系模式的候选码，称为全码
超键

只考虑候选码的第一个条件的属性集合称为超键。因此候选键为最小超键。
主码

若一个关系有多个候选码，则选定其中一个为主码

通常选择有代表性的、长度较短的候选码作为主码（主键），优先选择数字类型的候选键
主属性

候选码的诸属性称为主属性（Prime attribute）

不包含在任何侯选码中的属性称为非主属性（Non-Prime attribute）或非码属性（Non-key attribute）
外键（Foreign key）

一个实体的主键被另外一个实体使用，以表达不同实体元组之间的关系

实体（表）间的关系：

实体之间的关系依赖主键—外键关联实现
关系数据库支持的实体间关系类型
- 1：1
- 1：n
- m：n

三类关系：

基本关系（基本表）：实际存在的表，是实际存储数据的逻辑表示
查询表：查询结果对应的表
视图表：由基本表或其他视图表导出的表，是虚表，不对应实际存储的数据

基本关系的性质：

列是同质的，即每一列中的分量来自同一域，是同一类型的数据
不同的列可出自同一个域，也可出自不同域
列的顺序无所谓，列的次序可以任意交换
任意两个元组的候选码不能相同，元组相同是指两个元组的每个分量都相同
行的顺序无所谓，行的次序可以任意交换
分量必须取原子值

2.1.2 关系模式

定义：

关系模式（Relation Schema）是型(Type)，是结构
关系是值（二维表）
关系模式是关系的结构，关系是关系模式在某一时刻的数据关系模式是稳定的；而关系是某一时刻的值，是随时间可能变化的

关系模式可以形式化地表示为：R（U，D，DOM，F）

R：关系名
U：组成该关系的属性名集合
D：U中属性所来自的域
DOM：属性向域的映象集合
F：属性间数据的依赖关系的集合

关系模式通常可以简记为：R （A1，A2，…，An）

A1，A2，…，An : 属性名

关系模式和关系往往笼统称为关系

2.1.3 关系数据库

在一个给定应用领域中，所有关系的集合构成一个关系数据库

关系数据库的型：关系数据库模式(schema)，是对关系数据库结构的描述

关系数据库的值：关系模式在某一时刻对应的关系的集合，通常称为关系数据库(RDB)

2.2 关系操作

常用的关系操作：

查询操作：8种（5种基本）

选择、投影、连接、除、并、差、交、笛卡尔积

选择、投影、并、差、笛卡尔积是5种基本操作
数据更新：插入、删除、修改

关系操作的特点：

集合操作方式：操作的对象和结果都是集合，一次一集合的方式

关系数据库语言的分类：

关系代数语言：用对关系的运算来表达查询要求，对关系代数表达式求值，ISBL
关系演算语言
具有关系代数和关系演算双重特点的语言：SQL

2.3 关系的完整性

2.3.1 实体完整性

规则2.1 实体完整性规则若属性A是基本关系R的主属性，则属性A不能取空值。

空值 NULL：不知道、不存在或无意义的值

实体完整性规则的说明：

实体完整性规则是针对基本关系而言的。一个基本表通常对应现实世界的一个实体集。
关系模型中以主键作为唯一性标识。
主键（更严谨是候选键）中的属性即主属性不能取空值。
有空值的时候是需要特殊处理的，要特别注意。

2.3.2 参照完整性

在关系模型中实体及实体间的联系都是用关系来描述的，自然存在着关系与关系间的引用。

外码：

设F是基本关系R的一个或一组属性，但不是关系R的码。如果F与基本关系S的主码Ks相对应，则称F是R的外码。
基本关系R称为参照关系。
基本关系S称为被参照关系或目标关系。
外码并不一定要与相应的主码同名，但是通常情况下取相同名字便于识别

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

或者等于S中某个元组的主码值Ks
或者取空值（F的每个属性值均为空值）

为什么关系数据库不支持m:n联系：

数据库的特点之一就是数据冗余少，m:n的联系与此相矛盾，不符合数据库设计的基本要求
1:1、1:m的联系不存在实体数据冗余，但可能会存在同一个实体属性间的冗余（存在关系依赖）

2.3.3 用户定义的完整性

针对某一具体关系数据库的约束条件：反映具体应用所涉及的数据必须满足的语义要求

2.4 关系代数

关系代数是一种抽象的查询语言，它用关系运算表达查询

传统集合运算是从关系的“水平”方向，即行的角度进行

专门的关系运算，不仅涉及行而且涉及列

2.4.1 传统的集合运算

并（Union）
- R和S具有相同的目n（即两个关系都有n个属性），相应的属性取自同一个域
- 得到的结果是由两个关系相同的元素组成，即仍为n目关系，由属于R或属于S的元组组成
- 结果不允许重复
差（Except）
- R和S具有相同的目n，相应的属性取自同一个域
- 仍为n目关系，由属于R而不属于S的元组组成
交
- R和S具有相同的目n，相应的属性取自同一个域
- 仍为n目关系，由既属于R又属于S的元组组成
笛卡尔积
- R: n目关系，k1个元组。S: m目关系，k2个元组
- 结果：列：(n + m)列元组的集合，行：k1×k2个元组

2.4.2 专门的关系运算

几个记号：

分量：记关系模式为：，是一个元组。表示元组t种对应属性Ai的一个分量。
属性列或者属性组：若，其中是的一部分，则成A成为属性列或者属性组。

表示中去掉后剩余的属性组。
元组的连接：R为n目关系，S为m目关系。称为元组的连接，得到一个n+m列的元组，前n个分量为R中的一个n元组，后m个分量为S中的一个m元组。
象集，给定一个关系，X和Z为属性组。当时，x在R中的象集为：，它表示R中属性组X上值为x的诸元组在Z上对应分量的集合。

专门的关系运算：

选择（Selection）
- 其中F是选择条件，是一个逻辑表达式，取值为“真”或“假”。基本形式为：，其中是比较运算符，可以是
- 选择运算是从关系R中选取逻辑表达式F为真的元组，是从行的角度进行的运算，类似条件过滤
- 例如：
- 选择操作的优先从高到低为：{ 括号() ; θ ; ; ∧ ; ∨ }
投影（Projection）
- 其中A是R中的属性列，结果是从R中选择出若干属性列组成新的关系，投影操作主要是从列的角度进行运算。
- 投影之后不仅取消了原关系中的某些列，而且还可能取消某些元组（避免重复行）
- 例如：
连接（Join）
- 其中A和B分别为R和S上度数相等且可比的属性组。
- 连接运算从R和S的广义笛卡尔积R×S中选取R关系在A属性组上的值与S关系在B属性组上的值满足比较关系θ的元组。
- 等值连接：θ为等号的连接运算。即
- 自然连接：自然连接是一种特殊的等值连接。它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。即，B是相同的属性组，U是R和S的全体属性集合。
- 一般的连接操作是从行的角度进行运算，但是自然连接还需要取消重复列，所以是同时从行和列的角度进行运算。
- 悬浮元组：两个关系R和S在做自然连接时，关系R中某些元组有可能在S中不存在公共属性上值相等的元组，从而造成R中这些元组在操作时被舍弃了，这些被舍弃的元组称为悬浮元组。
- 外连接：如果把悬浮元组也保存在结果关系中，而在其他属性上填空值(Null)，就叫做外连接。
  
  左外连接：只保留左边关系R中的悬浮元组，即以左边关系R为基准，右边关系S不匹配的元组为NULL；或者说左边关系的元组全都保留
  
  右外连接：只保留右边关系S中的悬浮元组，即以右边关系S为基准，左边关系R不匹配的元组为NULL
  
  全外连接：保留左右两边的悬浮元组
除（Division）
- 给定关系R (X，Y) 和S (Y，Z)，其中X，Y，Z为属性组。R中的Y与S中的Y可以有不同的属性名，但必须出自相同的域集。一般地，有成立，否则除运算的结果为空集。
- R与S的除运算得到一个新的关系P(X)，P是R中满足下列条件的元组在 X 属性列上的投影：元组在X上分量值x的象集Yx包含S在Y上投影的集合
- 除操作是同时从行和列角度进行运算
- 例如：

第三章关系数据库标准语言SQL

3.1 SQL概述

特点：

综合统一
高度非过程化，只需提出“做什么”，无须了解存取路径，不需要知道“怎么做”
面向集合的操作方式，操作对象和结果都是集合
以同一种语法结构提供多种使用方式，是独立的语言又是嵌入式语言，同时还提供驱动API
语言简洁，易学易用，完成核心功能只用了9个动词

基本概念：

基本表：
- 本身独立存在的表
- 一个关系对应一个基本表
- 一个表可以有若干个索引
- 一个（或多个）基本表对应一个存储文件
存储文件：
- 由DBMS进行管理，实现各异
- 文件路径和文件名可由用户配置或指定
视图：
- 视图是虚表，是不存在的表
- 是从一个或几个基本表通过查询而导出的表
- 数据库中只存放视图的定义，不存放视图对应的数据
- 用户可以在视图上再定义视图

3.2 学生-课程数据库

1
2
3

学生表：	Student (Sno,Sname,Ssex,Sage,Sdept)
课程表：	Course (Cno,Cname,Cpno,Ccredit)
学生选课表： SC (Sno,Cno,Grade)

3.3 数据定义

SQL的数据定义功能：

模式定义（不同的DBMS实现各异）
表定义
视图和索引的定义

RDBMS提供了一个层次化的数据库对象命名机制：

一个RDBMS的实例（Instance）（或者称为目录）中可以建立多个数据库
一个数据库中可以建立多个模式
一个模式下通常包括多个表、视图和索引等数据库对象

3.3.1 模式的定义与删除

1. 模式的定义

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

例如：

1	`CREATE SCHEMA “S-T” AUTHORIZATION WANG;`

该语句没有指定<模式名>，<模式名>隐含为<用户名>。

定义模式实际上定义了一个命名空间：避免命名冲突。

在命名空间中可以定义该模式包含的数据库对象（基本表、视图、索引、数据类型、同义词等）。

2. 模式的删除：

1	`DROP SCHEMA <模式名> <CASCADE\|RESTRICT>;`

删除模式：

CASCADE（级联）删除模式的同时把该模式中所有的数据库对象全部删除
RESTRICT（限制）如果该模式中定义了下属的数据库对象（如表、视图等），则拒绝该删除语句的执行。仅当该模式中没有任何下属的对象时才能执行。

3. 不同数据库对Schema的定义

在MySQL中，Schema和Database是等价的概念。

Oracle的Schema相当于数据库对象的命名空间，和UserName是等价的。

SQL Server 2000之前和Oracle一样，Schema和用户没有概念上的区别。 SQL Server 2005以后的版本，user 和 schema是独立的objects，二者是独立的和各自分离的。在SQL Server中的层次结构为：Server(Instance). Database. DatabaseSchema. DatabaseObject

3.3.2 基本表的定义、删除与修改

1. 定义基本表

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

<列级完整性约束条件>：涉及相应属性列的完整性约束条件

<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件。如果约束条件涉及到该表的多个属性列时，必须定义在表级上。

例如：

建立一个学生表 Student

CREATE TABLE Student(
    Sno CHAR(9) PRIMARY KEY,   /* 列级完整性约束条件, Sno是主码*/
    Sname CHAR(20) UNIQUE,      /* Sname取唯一值*/
    Ssex CHAR(2),
    Sage SMALLINT，
    Sdept CHAR(20)
);

建立一个课程表 Course

CREATE TABLE Course(
    Cno CHAR(4) PRIMARY KEY,   /* 列级完整性约束条件, Cno是主码*/
    Cname CHAR(40) NOT NULL,    /* 列级完整性约束条件, Cname不能取空值*/
    Cpno CHAR(4),               /* Cpno的含义是先修课*/
    Ccredit SMALLINT，
    FOREIGN KEY (Cpno) REFERENCES Course(Cno)   
    /* 表级完整性约束条件, Cpno是外码，被参照表是Course，被参照列是Cno*/
);

建立一个学生选课表SC

CREATE TABLE SC(
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT，
    PRIMARY KEY (Sno,Cno),
    /* 主码由两个属性构成，必须作为表级完整性进行定义 */
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    /* 表级完整性约束条件，Sno是外码，被参照表是Student */
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
    /* 表级完整性约束条件， Cno是外码，被参照表是Course */
);

2. 数据类型

SQL 中域的概念用数据类型来实现。定义表的各个属性时需要指明其数据类型及长度。

一个属性选用哪种数据类型，需要考虑取值范围以及要做哪些运算。

CHAR, VARCHAR
- 以byte字节计数
- CHAR(n)类型数据插入数据库后，自动补齐空格到n字节，VARCHAR(n)不会自动补充空格
- MySQL >5.6 后是字符个数，不是字节数
NCHAR, NVARCHAR
- 国家字符集
- 定义的是字符的个数，非字节数
日期时间类型
1. MySQL
  - DATE：只有日期，没有时间，YYYY-MM-DD，范围从最小1000到9999
  - DATETIME：日期和时间两部分，YYYY-MM-DD hh:mm:ss ，范围从最小1000到9999
  - TIMESTAMP: 日期和时间两部分，范围：'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
2. Oracle
  - DATE：日期+时间，精确到秒，7bytes，范围1/1, 4712 BC – 12/31, 9999
  - TIMESTAMP：日期+时间，可以精确到毫秒ms

注：

标准SQL使用单引号标识字符串常量，两个连续的单引号转义为一个单引号
双引号通常用来标识关键字，对象名、字段名。 Oracle中双引号(“”)标识的对象名/字段名区分大小写
字符串的拼接：
- MySQL：使用空格
- Oracle：使用||
MySQL在Windows 通常不区分大小写，Linux系统区分。单引号和双引号可以混用。反引号用来用于标记「表名」和「列名」，通常情况加不加都行；但如果表名或列名为 MySQL 保留符（例如 cource），那么一定要加反引号作为区分。
Oracle区分大小写，使用单引号标记常量，Oracle中双引号标识的对象名/字段名区分大小写

字符编码：

GB2312
GBK
GB18030
Unicode
UTF-8
UTF-16

3. 模式与表

每一个基本表都属于某一个模式

定义基本表所属模式的三种方法：

在表名中明显地给出模式名。

1	`CREATE TABLE "S-T".Student(...)； /Student 所属的模式是 S-T/`

在创建模式语句中同时创建表

1 2	`CREATE SCHEMA TEST AUTHORIZATION ZHANG CREATE TABLE TAB1(...);`

设置所属的模式，这样在创建表时表名中不必给出模式名。

创建基本表（其他数据库对象也一样）时，若没有指定模式，系统根据搜索路径来确定该对象所属的模式。

关系数据库管理系统会使用模式列表（search_path, 类似OS的path变量）中第一个存在的模式作为数据库对象的模式名（create table）。

4. 修改基本表

ALTER TABLE <表名>
    [ADD [COLUMN] <新列名><数据类型> [完整性约束]] 
    [ADD <表级完整性约束>]
    [DROP [COLUMN] <列名> [CASCADE| RESTRICT]]
    [DROP CONSTRAINT<完整性约束名>[RESTRICT | CASCADE]]
    [ALTER COLUMN<列名><数据类型>];

不管基本表中原来是否已有数据，新增加的列一律为空值，可以使用采用缺省值(DEFAULT)避免新增的字段取NULL，约束条件为： NOT NULL DEFAULT (current_date()); 。

5. 删除基本表

1	`DROP TABLE <表名>[RESTRICT\| CASCADE];`

3.3.3 索引的建立与删除

建立索引的目的：加快查询速度
数据库索引的类型：顺序文件上的索引、B+树索引、散列（hash）索引、位图索引
索引需要占用一定的存储空间，当基本表更新时，索引要进行相应的维护，因此需要根据实际应用的需要有选择地创建索引。
建立与删除索引由数据库管理员或表的属主（owner），即建立表的人，负责完成。
关系数据库管理系统在执行查询时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。
索引是关系数据库管理系统的内部实现技术，属于内模式的范畴。

1. 建立索引

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

UNIQUE：此索引的每一个索引值只对应唯一的数据记录
CLUSTER：表示要建立的索引是聚簇索引（SQL Server语法）
索引：可以建立在该表的一列或多列上，各列名之间用逗号分隔
<次序>：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC

例如：

CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);	
/*SC表按学号升序和课程号降序建唯一索引*/

2. 修改索引

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

3. 删除索引

1	`DROP INDEX <索引名>;`

3.3.4 数据字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。

关系数据库管理系统在执行 SQL 的数据定义语句时，实际上就是在更新数据字典表中的相应信息。

MySQL中：INFORMATION_SCHEMA是每个MySQL实例中的数据库，存储有关 MySQL服务器维护的所有其他数据库的信息。INFORMATION_SCHEMA数据库包含只读表，实际上是视图，而不是基表。

Oracle中：有静态字典和动态字典。静态字典中的视图分为三类，它们分别由三个前缀够成：user_*、 all_*、 dba_*，存储有用户所拥有对象、用户能访问对象、所有对象信息。动态字典中包含了一些潜在的由系统管理员(如SYS)维护的表和视图，包括关于内存和磁盘等的运行情况等，只读不能修改。

3.4 数据查询

语句格式：

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

SELECT子句：指定要显示的属性列（投影）
FROM子句：指定查询对象（基本表或视图）
WHERE子句：指定查询条件（连接或选择条件）
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
HAVING短语：只有满足指定条件的分组才予以输出
ORDER BY子句：对查询结果表按指定列值的升序或降序排序

3.4.1 单表查询

1. 选择表中的若干列

查询指定列

1 2	`SELECT Sname,Sno,Sdept FROM Student; SELECT * FROM Student;`

查询经过计算的值

1 2	`SELECT Sname, 2023-Sage FROM Student; /假设当时为2023年，查询出生年份/ SELECT Sname,'Year of Birth: ',2014-Sage,LOWER(Sdept) FROM Student;`

使用列别名改变查询结果的列标题（原名+空格+别名）

1
2
3

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

2.选择表中的若干元组

使用DISTINCT消除取值重复的行：如果没有指定DISTINCT关键词，则缺省为ALL

1
2
3

SELECT Sno FROM SC;
SELECT ALL Sno FROM SC;			/*两者等价*/
SELECT DISTINCT Sno FROM SC;	/*去除重复行*/

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

WHERE 子句常用的查询条件

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

比较大小

1 2	`SELECT Sname FROM Student WHERE Sdept='CS'; SELECT Sname,Sage FROM Student WHERE Sage<20;`

确定范围（between and，not between and）

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

确定集合（in，not in）

1	`SELECT Sname, Ssex FROM Student WHERE Sdept IN ('CS','MA','IS');`

字符匹配（like，not like）

匹配串为固定字符串（精确匹配）

1 2	`SELECT * FROM Student WHERE Sno LIKE '201215121'; SELECT * FROM Student WHERE Sno='201215121'; /等价/`

匹配串为含通配符的字符串

% (百分号) 代表任意长度(长度可以为0)的字符串，_ (下横线) 代表任意单个字符

1
2
3

SELECT Sname FROM Student WHERE Sname LIKE '欧阳_';
SELECT Sname,Sno FROM Student WHERE Sname LIKE '_阳%';	/*第二个字为阳*/
SELECT Sname,Sno,Ssex FROM Student WHERE Sname NOT LIKE '刘%';/*不姓刘*/

使用转义字符将通配符转义为普通字符

ESCAPE '<换码字符>' 表示该字符为转移字符，缺省的转义字符为\

1
2
3

SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB\_Design' ESCAPE '\';
SELECT * FROM Course WHERE Cname LIKE 'DB\_%i__' ESCAPE '\';
/*以"DB_"开头，且倒数第三个字符为i*/

涉及空值的查询（IS NULL，IS NOT NULL）

“IS” 不能用 “=” 代替
1
SELECT Sno, Cno FROM SC WHERE Grade IS NULL;

多重条件查询（and，or，可以用括号改变优先级）

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

关于NULL的说明：

NULL参与数学运算时，其结果NULL。
统计聚合计算，一般将有空值的数据忽略不计。count除外，因为统计的是行数。
所有与NULL的比较运算的结果是 unknown，既不是true也不是false

如果where条件的求解结果是“unknown”，等价于where条件=false
与NULL的逻辑运算部分结果明确，例如true or null = true。部分结果为unknown，例如false or null = unknown
NULL与空字符‘’有区别。在MySQL中，NULL与空字符串‘’是不同的；在Oracle中，NULL与空字符串‘’是等价的，但是在where条件比较时两者不同。
包含NULL的表达式结果是NULL
不能使用等号查询NULL值

3.ORDER BY子句

ORDER BY子句，可以按一个或多个属性列排序

升序(缺省)：ASC；降序：DESC；
缺省值为升序
对于NULL空值，排序时显示次序由具体系统实现来决定

1	`SELECT Sno,Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;`

4.聚集函数

常用聚集函数：

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

聚集函数只能用于 SELECT 子句和 GROUP BY 中的 HAVING 子句。

例如：

1
2
3

SELECT COUNT(DISTINCT Sno) FROM SC;
SELECT AVG(Grade) FROM SC WHERE Cno='1';
SELECT SUM(Ccredit) FROM SC,Course WHERE Sno='201215012' AND SC.Cno=Course.Cno;

5.GROUP BY子句

GROUP BY子句分组：细化聚集函数的作用对象：

如果未对查询结果分组，聚集函数将作用于整个查询结果
对查询结果分组后，聚集函数将分别作用于每个组
按指定的一列或多列值分组，值相等的为一组

Group By的分组字段包括NULL，分组时将多个NULL分为一组（根据SQL标准）

例如：

/*各个课程号及相应的选课人数*/
SELECT Cno,COUNT(Sno) FROM SC GROUP BY Cno;
/*选修了 3 门以上课程的学生学号*/
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)>3;
/*平均成绩大于等于 90 分的学生学号和平均成绩*/
SELECT Sno, AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)>=90;

HAVING 短语与 WHERE 子句的区别：作用对象不同

WHERE 子句作用于基本表或视图，从中选择满足条件的元组
HAVING 短语作用于组，从中选择满足条件的组

3.4.2 连接查询

连接查询：同时涉及两个以上的表的查询

连接条件或连接谓词：用来连接两个表的条件

查询语句：

1	`select * from table1, table2 where [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>`

连接字段：连接条件中的各连接字段类型必须是可比的，但名字不必相同

1. 等值与非等值连接查询

等值连接：连接运算符为 =

1
2
3

SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;

连接操作的执行过程：

嵌套循环法（NESTED-LOOP）
- 在表1中找到第一个元组，然后从头扫描表2，找到满足的元组与表1的第一个元组拼接起来
- 表2查找结束后，查找表1中的第二个元组，然后从头扫描表2
- 重复上述操作
排序合并法（SORT-MERGE）
- 常用于等值连接
- 首先按连接属性对表1和表2排序
- 对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组
- 当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续
- 查找表1中第二条元组，从刚刚的中断点除继续扫描表2，重复上述操作
索引连接（INDEX-JOIN）
- 对表2按连接字段建立索引
- 对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组

非等值连接也是连接的2张表先做笛卡尔积，然后按照“非等值”的连接条件对每一条记录进行过滤：

1
2
3

SELECT * FROM course, sc
WHERE course.Cno < sc.cno
ORDER BY course.cno, sno, sc.cno;

过滤条件：

过滤条件是在连接条件的基础上，进一步对结果集进行选择（过滤），要区分连接条件（关系代数中的θ连接条件）和过滤条件（关系代数中的选择σ条件）

SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno = SC.Sno
AND SC.Cno = '2' AND SC.Grade > 85;

这里的WHERE子句是由连接谓词和选择谓词（过滤）组成的复合条件。

执行过程：

先从SC中挑选出Cno='2'并且Grade>85的元组形成一个中间关系
再和Student中满足连接条件的元组进行连接得到最终的结果关系

2. 自身连接

自身连接：一个表与其自己进行连接，需要给表起别名以示区别，由于所有属性名都是同名属性，因此必须使用别名前缀。

1
2
3

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

3. 外连接

外连接与普通连接的区别：

普通连接操作只输出满足连接条件的元组
外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
分为：
- 左外连接：列出左边关系中所有的元组
- 右外连接：列出右边关系中所有的元组

/*查询每个学生及其选修课程的情况*/
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT OUTER JOIN SC ON
(Student.Sno=SC.Sno);
/*使用Join自然连接*/
SELECT * FROM Student NATURAL LEFT OUTER JOIN SC;
/*查找没有选任何课的学生学号和姓名*/
select sno, count(distinct cno) cnt
from ( SELECT * FROM Student NATURAL LEFT OUTER JOIN sc ) temp
GROUP BY sno having count(distinct cno) = 0;

4. 多表连接

/*查询每个学生的学号、姓名、选修的课程名及成绩*/
SELECT Student.Sno, Sname, Cname, Grade
FROM Student, SC, Course /*多表连接*/
WHERE Student.Sno = SC.Sno
AND SC.Cno = Course.Cno;
/*使用Join自然连接*/
SELECT * FROM Student NATURAL JOIN SC NATURAL JOIN Course;

5. Join总结

JOIN连接类型：

INNER JOIN
LEFT JOIN OR LEFT OUTER JOIN
RIGHT JOIN OR RIGHT OUTER JOIN
FULL OUTER JOIN
NATURAL JOIN
CROSS JOIN
SELF JOIN

Inner Join:

即关系代数中的θ连接运算
返回符合连接字段条件的所有记录

Outer Join:

即关系代数中的外连接运算
左连接、右连接、全连接

3.4.3 嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块，将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

SELECT Sname            /*外层查询或父查询*/
FROM Student
WHERE Sno IN
    (SELECT Sno   		/*内层查询或子查询*/
    FROM SC 
    WHERE Cno='2')

子查询的限制：不能使用ORDER BY子句

嵌套查询求解方法：

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

由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
1
2
Select Sname from Student S where Sno in ( select Sno from SC where Cno='2' );
相关子查询：子查询的查询条件依赖于父查询

首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表。然后再取外层表的下一个元组重复这一过程，直至外层表全部检查完为止。
1
2
3
SELECT Sno, Cno FROM SC x WHERE Grade >= ( SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno );

1. 带有IN谓词的子查询

查询与“刘晨”在同一个系学习的学生：

SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
    (SELECT Sdept
    FROM Student
    WHERE Sname= '刘晨' );
/*用自身连接完成查询要求*/
SELECT S1.Sno, S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept = S2.Sdept AND
	S2.Sname = '刘晨';

若IN的比较值列表中包含 NULL：
- IN谓词是三值逻辑，可返回TRUE, FALSE or NULL
- 返回TURE：列表中有正常值
- 返回FALSE：返回列表中没有正常值，并且也没有NULL值
- 返回NULL：值是NULL，或者列表中没有正常值并且拥有至少一个NULL
NOT IN的比较值列表中包含 NULL：
- NOT IN谓词也是是三值逻辑，可返回TRUE, FALSE or NULL
- 如果NOT IN列表中有一个值是NULL，则返回结果为空
- 返回FALSE：IN会返回TRUE时，NOT IN将会返回FALSE
- 返回NULL：IN会返回NULL时，NOT IN将会返回NULL

2. 带有比较运算符的子查询

当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。

上例中由于一个学生只可能在一个系学习， IN 可以改为 =

找出每个学生超过他选修课程平均成绩的课程号：

SELECT Sno, Cno
FROM SC x
WHERE Grade >=(SELECT AVG(Grade)
                FROM SC y
                WHERE y.Sno=x.Sno);

执行过程：

从外层中取出SC的一个元组，将X.Sno的值传递给内层
执行内层查询，得到一个值代替整个内层查询
执行外层查询，得到记过，然后取出外层的下一个元组重复上述步骤

3. 带有ANY（SOME）或ALL谓词的子查询

> ANY：大于子查询结果中的某个值
>ALL：大于子查询结果中的所有值（或者叫任意一个值）
<ANY：小于子查询结果中的某个值
<ALL：小于子查询结果中的所有值
>=ANY：大于等于子查询结果中的某个值
>=ALL：大于等于子查询结果中的所有值
<=ANY：小于等于子查询结果中的某个值
<=ALL：小于等于子查询结果中的所有值
=ANY：等于子查询结果中的某个值
=ALL：等于子查询结果中的所有值（通常没有实际意义，如果结果集的记录数大于1时，永为假）
!=（或<>）ANY：不等于子查询结果中的某个值（通常没有实际意义，如果结果集的记录数大于1时，永为真）
!=（或<>）ALL：不等于子查询结果中的所有值

查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄：

SELECT Sname,Sage
FROM Student
WHERE Sage<ANY(SELECT Sage
               FROM Student
               WHERE Sdept='CS')
AND Sdept<>'CS';
/*用聚集函数实现*/
SELECT Sname, Sage
FROM Student
WHERE Sage <
            (SELECT MAX（Sage）
            FROM Student WHERE Sdept= ‘CS’ )
AND Sdept <> 'CS';

ANY、ALL 与聚集函数的对应关系如表所示。

	=	<>或!=	<	<=	>	>=
ANY	IN	--	<MAX	<=MAX	>MIN	>=MIN
ALL	--	NOT IN	<MIN	<=MIN	>MAX	>=MAX

事实上，用聚集函数实现子查询通常比直接用 ANY 或 ALL 查询效率要高。

ANY: 与子查询结果/列表每一个值比较结果 OR等价
ALL: 与子查询结果/列表每一个值比较结果 AND等价

如果被比较的值域集合中包括NULL，则NOT IN、!=ALL的最终果为UNKNOWN (==False)。

4. 带有EXISTS谓词的子查询

存在量词 $$

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值”true”或逻辑假值”false”。

若内层查询结果非空，则外层的WHERE子句返回真值，NOT EXIST返回假值
若内层查询结果为空，则外层的WHERE子句返回假值，NOT EXIST返回真值

查询所有选修了1号课程的学生姓名：

SELECT Sname
FROM Student
WHERE EXISTS
      (SELECT *
       FROM SC
       WHERE Sno=Student.Sno AND Cno='1');

NOT EXISTS仅与是否有结果返回有关，与NULL无关
在列表中没有NULL的情况下，not in 与 not exists等价
在列表中有NULL的情况下，只有not exists可以返回结果，NOT IN 返回空集
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查等价替换 [特别是含有NULL值的某些字段]
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用EXISTS谓词的子查询等价替换

全称量词 $$：

SQL语言中没有全称量词
可以把全称量词的谓词转换为等价的存在量词的谓词：否定之否定

查询与“刘晨”在同一个系学习的学生（使用EXISTS谓词）：

SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS (SELECT *
              FROM Student S2
              WHERE S2.Sdept=S1.Sdept AND S2.Sname='刘晨');

※ 查询选修了全部课程的学生姓名：

SELECT Sname
FROM Student
WHERE NOT EXISTS        /* 查询这样的学生，没有一门课程是他不选修的 */
        (SELECT *
         FROM Course 
         WHERE NOT EXISTS
            (SELECT * 
             FROM SC
             WHERE Sno=Student.Sno AND Cno=Course.Cno));

R(X,Y)÷S(Y,Z)的运算使用SQL 语句可表达为下列通用形式：双层NOT EXIST

select distinct R.X from R R1
where not exists
(
    select S.Y from S
    where not exists
        (
        select * from R R2
        where R2.X = R1.X and R2.Y = S.Y
        )
);

3.4.4 集合查询

集合操作的种类：

并操作UNION
- UNION：将多个查询结果合并起来时，系统自动去掉重复元组
- UNION ALL：将多个查询结果合并起来时，保留重复元组。
交操作INTERSECT
差操作EXCEPT/MINUS

参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同。

查询计算机科学系的学生及年龄不大于19岁的学生：

查询选修了课程 1 或选修了课程 2 的学生：

1 2	`SELECT Sno FROM SC WHERE Cno='1' UNION SELECT Sno FROM SC WHERE Cno='2';`

查询计算机科学系的学生与年龄不大于 19 岁的学生的交集：

1 2	`SELECT * FROM Student WHERE Sdept='CS' INTERSECT SELECT * FROM Student WHERE Sage<=19;`

查询计算机科学系的学生与年龄不大于 19 岁的学生的差集：

1 2	`SELECT * FROM Student WHERE Sdept='CS' EXCEPT SELECT * FROM Student WHERE Sage<=19;`

3.4.5 基于派生表的查询

子查询不仅可以出现在 WHERE 子句中，还可以出现在 FROM 子句中，这时子查询生成的临时派生表（derived table）成为主查询的查询对象。

找出每个学生超过他自己选修课程平均成绩的课程号：

SELECT Sno, Cno
FROM SC, (SELECT Sno, Avg(Grade)
          FROM SC
          GROUP BY Sno)
          AS Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno=Avg_sc.avg_sno and SC.Grade>=Avg_sc.avg_grade;

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询 SELECT 子句后面的列名为其默认属性。

查询所有选修了1号课程的学生姓名：

1
2
3

SELECT Sname
FROM Student, (SELECT Sno FROM SC WHERE Cno='1') AS SC1
WHERE Student.Sno=SC1.Sno;

通过 FROM 子句生成派生表时，AS 关键词可以省略，但必须为派生关系指定一个别名。

3.4.6 Select语句的一般形式

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

1. 目标列表达式的可选格式

<表名>.*
COUNT( [DISTINCT|ALL] * )
[<表名>.]<属性列名表达式> [,<表名>.]<属性列名表达式>]…

其中<属性列名表达式>可以是由属性列、作用于属性列的聚集函数和常量的任意算术运算（+，-，*，/）组成的运算公式。

2. 聚集函数的一般格式 $列名$ 3. WHERE子句的条件表达式的可选格式

$属性列名属性列名常量语句$
$属性列名属性列名常量语句属性列名常量语句$
$属性列名值值语句$
$属性列名匹配串$
$属性列名$
$语句$
$条件表达式条件表达式条件表达式$

Select的一些特别用法：

CASE WHEN：条件返回

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

WITH … AS：定义了一个临时作用域，便于随后的查询引用，类似临时表/视图

WITH query_name (column_name1, ...) AS (SELECT ...)
	SELECT ( main ) ...
/*举例*/
WITH
    cte1 AS (SELECT a, b FROM table1),
    cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
	WHERE cte1.a = cte2.c;

3.5 数据更新

3.5.1 插入数据

1. 插入元组

1
2
3

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

INTO子句：

指定要插入数据的表名及属性列
属性列的顺序可与表定义中的顺序不一致
没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
指定部分属性列：插入的元组在其余属性列上取空值；但是，如果定义了缺省值(default)，则取缺省值

VALUES子句：

提供的值必须与INTO子句匹配

2. 插入子查询结果

1
2
3

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

子查询中SELECT子句目标列必须与INTO子句匹配。

对每一个系，求学生的平均年龄，并把结果存入数据库。

-- 第一步：建表
CREATE TABLE Dept_age 
        (Sdept CHAR(15),        /*系名*/ 
         Avg_age SMALLINT);     /*学生平均年龄*/
-- 第二步：插入数据                        
INSERT
INTO Dept_age(Sdept,Avg_age) 
SELECT Sdept, AVG(Sage) 
FROM Student 
GROUP BY Sdept;

关系数据库管理系统在执行插入语句时，会检查所插元组是否破坏表上已定义的完整性规则。

3. 复制表结构/数据

MySQL

-- 复制表结构
CREATE TABLE new_tbl LIKE orig_tbl;
CREATE TABLE SC_2 LIKE SC;
-- 复制表结构和数据
CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;
CREATE TABLE SC_2 SELECT * FROM SC;
-- 复制数据
INSERT INTO new_tbl SELECT * FROM orig_tbl;
CREATE TABLE SC_2 LIKE SC;
INSERT INTO SC_2 SELECT * FROM SC;

表结构复制的时候需要注意的问题：

主键、外键、索引是否同样复制（通常没有创建）
每个字段的约束条件是否被复制 NULL， NOT NULL 缺省值，default value Check 约束等
表级的约束关系是否被复制
眼见为实：复制完后，仔细对照检查，缺少的补上

在数据迁移期间禁用约束（性能考虑）：禁止索引、外键、触发器等

3.5.2 修改数据

1
2
3

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

功能是修改指定表中满足 WHERE 子句条件的元组。其中 SET 子句给出 <表达式> 的值用于取代相应的属性列值。

如果省略WHERE子句，表示要修改表中的所有元组。

子查询也可以嵌套在 UPDATE 语句中，用以构造修改的条件。

将计算机科学系全体学生的成绩置零：

UPDATE SC 
SET Grade=0 
WHERE Sno IN 
    (SELETE Sno 
     FROM Student 
     WHERE Sdept='CS');

3.5.3 删除数据

1
2
3

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

功能：从指定表中删除满足 WHERE 子句条件的所有元组。

WHERE子句

指定要删除的元组
省略 WHERE 子句表示要删除表中的全部元组，但表的定义仍在字典中
DELETE 语句删除的是表中的数据，而不是关于表的定义

三种修改方式：

修改某一个元组的值
修改多个元组的值
带子查询的修改语句

3.6 空值的处理

空值就是“不知道”、“不存在”或“无意义”的值。

SQL 语言中允许某些元组的某些属性在一定情况下取空值。一般有以下几种情况：

该属性应该有一个值，但目前不知道它的具体值。例如：某学生的年龄属性，因该学生没有填写年龄信息，不知道该学生的年龄，因此取空值。
该属性不应该有值。例如，缺考学生的成绩为空，因为该学生没有参加考试。
由于某种原因不便于填写。例如，一个人的电话号码不想让大家知道，则取空值。

判断一个属性的值是否为空值，用 IS NULL 或 IS NOT NULL 来表示。

空值的约束条件：

有 NOT NULL 约束条件的不能取空值
加了 UNIQUE 限制的属性不能取空值
码属性不能取空值

空值的运算：

空值与另一个值（包括另一个空值）的算术运算的结果为空值
空值与另一个值（包括另一个空值）的比较运算的结果为 UNKNOWN
有 UNKNOWN 后，传统的逻辑运算中二值（TRUE，FALSE）逻辑就扩展成了三值逻辑

3.7 视图

视图的特点：

虚表，是从一个或几个基本表（或视图）导出的表
只存放视图的定义，不存放视图对应的数据
基表中的数据发生变化，从视图中查询出的数据也随之改变

3.7.1 定义视图

1. 建立视图

1
2
3

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

子查询可以是任意的 SELECT 语句，是否可以含有 ORDER BY 子句和 DISTINCT 短语，则取决于具体系统的实现。
WITH CHECK OPTION 表示对视图进行 UPDATE、INSERT 和 DELETE 操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。
组成视图的属性列名或者全部省略或者全部指定。如果省略了视图的各个属性列名，则隐含该视图由子查询中 SELECT 子句目标列中的诸字段组成。

建立信息系学生的视图：

CREATE VIEW IS_Student
AS
    SELECT Sno,Sname,Sage
    FROM Student
    WHERE Sdept='IS';

建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。WITH CHECK OPTION子句使得该视图进行插入、修改和删除操作时，RDBMS会自动加上Sdept='IS'的条件。

CREATE VIEW IS_Student
AS
    SELECT Sno,Sname,Sage
    FROM Student
    WHERE Sdept='IS'
    WITH CHECK OPTION;

若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，则称这类视图为行列子集视图。

视图可以建立在多个基本表上。

建立信息系选修了 1 号课程的学生的视图（包括学号、姓名、成绩）：

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Sdept='IS' AND Student.Sno=SC.Sno AND SC.Cno='1';

视图可以建立在一个或多个已定义好的视图上，或建立在基本表与视图上。

建立信息系选修了 1 号课程且成绩在 90 分以上的学生的视图：

CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;

由于视图中的数据并不实际存储，所以定义视图时可以根据应用的需要设置一些派生属性列。这些派生属性在基本表中并不实际存在，所以也称为虚拟列。带虚拟列的视图也称为带表达式的视图。

定义一个反映学生出生年份的视图：

CREATE VIEW BT_S(Sno,Sname,Sbirth) 
AS
SELECT Sno,Sname,2021-Sage 
FROM Student;

可以用带有聚集函数和 GROUP BY 子句的查询来定义视图，这种视图称为分组视图。

将学生的学号及平均成绩定义为一个视图：

CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade) 
FROM SC
GROUP BY Sno;

由于 AS 子句中 SELECT 语句的目标列平均成绩是通过作用聚集函数得到的，所以 CREATE VIEW 中必须明确定义组成 S_G 视图的各个属性列名。

2. 删除视图

1	`DROP VIEW <视图名> [CASCADE];`

视图删除后视图的定义将从数据字典中删除。
如果该视图上还导出了其他视图，使用 CASCADE 级联删除语句，把该视图和由它导出的所有视图一起删除。
基本表删除后，由该基本表导出的所有视图均无法使用了，但是视图的定义没有从字典中清除。删除这些视图定义需要显式地使用 DROP VIEW 语句。

3.7.2 查询视图

用户角度：查询视图与查询基本表相同

关系数据库管理系统实现视图查询的方法：视图消解法（View Resolution）

首先进行有效性检查，检查查询中涉及的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义，把定义中的子查询和用户的查询结合起来，转换成等价的对基本表的查询，然后再执行修正了的查询。

在信息系学生的视图中找出年龄小于 20 岁的学生：

1
2
3

SELECT Sno,Sage
FROM IS_Student
WHERE Sage<20;

视图消解转换后的查询语句为：

1
2
3

SELECT Sno,Sage
FROM Student 
WHERE Sdept='IS' AND Sage<20;

视图消解法的局限：有些情况下，视图消解法不能生成正确的查询。

定义视图并查询视图与基于派生表的查询是有区别的：

视图一旦定义，其定义将永久保存在数据字典中，之后的所有查询都可以直接引用该视图
派生表只是在语句执行时临时定义，语句执行后该定义即被删除

3.7.3 更新视图

更新视图是指通过视图来插入、删除和修改数据。

由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新。
像查询视图那样，对视图的更新操作也是通过视图消解，转换为对基本表的更新操作。
为防止用户通过视图对数据进行增加、删除、修改时，有意无意地对不属于视图范围内的基本表数据进行操作，可在定义视图时加上 WITH CHECK OPTION 子句。

更新视图的限制：一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新：

如果视图的select目标列包含聚集函数，则不能更新
如果视图的select子句使用了unique或distinct，则不能更新
如果视图中包括了group by子句，则不能更新
如果视图中包括经算术表达式计算出来的列，则不能更新
如果视图是由单个表的列构成，但没有包括主键，则不能更新
对于由单一Table子集构成的视图，即如果视图是从单个基本表使用选择、投影操作导出的，并且包含了基本表的主键，则可以更新（即允许对行列子集视图进行更新）
对其他类型视图的更新不同系统有不同限制

3.7.4 视图的作用

视图能够简化用户的操作
视图使用户能以多种角度看待同一数据
视图对重构数据库提供了一定程度的逻辑独立性
视图能够对机密数据提供安全保护
适当利用视图可以更清晰地表达查询

第四章数据库安全性

4.1 数据库安全性概述

4.1.1 数据库的不安全因素

非授权用户对数据库的恶意存取和破坏
数据库中重要或敏感的数据被泄露
安全环境的脆弱性

4.1.2 安全标准简介

1985 年美国国防部正式颁布《DoD可信计算机系统评估准则》（Trusted Computer System Evaluation Criteria，简称 TCSEC 或 DoD85）。

TCSEC标准的目的:

提供一种标准，使用户可以对其计算机系统内敏感信息安全操作的可信程度做评估。
给计算机行业的制造商提供一种可循的指导规则，使其产品能够更好地满足敏感应用的安全需求。

不同国家建立在 TCSEC 概念上的评估准则：

欧洲的信息技术安全评估准则（ITSEC）
加拿大的可信计算机产品评估准则（CTCPEC）
美国的信息技术安全联邦标准（FC）

1993 年，CTCPEC、FC、TCSEC 和 ITSEC 联合行动，解决原标准中概念和技术上的差异，将各自独立的准则集合成一组单一的、能被广泛使用的 IT 安全准则，这一行动被称为通用准则（Common Criteria，CC）项目。

1999 年 CC V2.1版被 ISO 采用为国际标准，2001 年 CC V2.1 版被我国采用为国家标准。

目前 CC 已基本取代了 TCSEC，成为评估信息产品安全性的主要标准。

1. TCSEC标准

TCSEC/TDI 中定义了数据库管理系统的设计与实现中需满足和用以进行安全性级别评估的标准，从 4 个方面来描述安全性级别划分的指标，即安全策略、责任、保证和文档。

TCSES/TDI 将系统划分为 4 组（division）7 个等级，依次是 D、C（C1，C2）、B（B1，B2，B3）、A（A1），按系统可靠或可信程度逐渐增高。

安全级别	定义
A1	验证设计
B3	安全域
B2	结构化保护
B1	标记安全保护
C2	受控的存取保护
C1	自主安全保护
D	最小保护

D 级：该级是最低级别。保留 D 级的目的是为了将一切不符合更高标准的系统均归于 D 组。如 DOS 就是操作系统中安全标准为 D 级的典型例子，在安全性方面几乎没有什么专门的机制来保障。
C1 级：该级只提供了非常初级的自主安全保护，能够实现对用户和数据的分离，进行自主存取控制（DAC），保护或限制用户权限的传播。现有的商业系统往往稍作改进即可满足要求。
C2 级：该级实际上是安全产品的最低档，提供受控的存取保护，即将 C1 级的 DAC 进一步细化，以个人身份注册负责，并实施审计和资源隔离。达到 C2 级的产品在其名称中往往不突出“安全”（security）这一特色。
B1 级：标记安全保护。对系统的数据加以标记，并对标记的主体和客体实施强制存取控制（MAC）以及审计等安全机制。B1 级别的产品被认为是真正意义上的安全产品，满足此级别的产品前一般多冠以“安全”（security）或“可信的”（trusted）字样，作为区别于普通产品的安全产品出售。
B2 级：结构化保护。建立形式化的安全策略模型，并对系统内的所有主体和客体实施 DAC 和 MAC。
B3 级：安全域。该级的 TCB （Trusted Computing Base）必须满足访问监控器的要求，审计跟踪能力更强，并提供系统恢复过程。
A1 级：验证设计，即提供 B3 级保护的同时给出系统的形式化设计说明和验证，以确信各安全保护真正实现。

2. CC通用准则

CC 是在上述各评估准则及具体实践的基础上通过相互总结和互补发展而来的。和早期的评估准则相比，CC 具有结构开放、表达方式通用等特点。

CC 提出了目前国际上公认的表述信息技术安全性的结构，即把对信息产品的安全要求分为

安全功能要求：用以规范产品和系统的安全行为。
安全保证要求：解决如何正确有效地实施这些功能。

安全功能要求和安全保证要求都以“类-子类-组件”的结构表述，组件是安全要求的最小构件块。

CC 的文本由三部分组成，三个部分相互依存，缺一不可。

简介和一般模型：介绍 CC 中的有关术语、基本概念和一般模型以及与评估有关的一些框架
安全功能要求：列出了一系列类、子类和组件
安全保证要求：列出了一系列保证类、子类和组件，根据系统对安全保证要求的支持情况提出了评估保证级（Evaluation Assurance Level，EAL），从 EAL1 至 EAL7 共分为七级，按保证程度逐渐增高。

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

粗略而言，TCSEC 的 C1 和 C2 级分别相当于 EAL2 和 EAL3；B1、B2 和 B3 分别相当于 EAL4、EAL5 和 EAL6；A1 对应于 EAL7。

3. 等级保护

等级保护1.0：《计算机信息系统安全保护等级划分准则》国家标准(GB 17859-1999)，于 2001年1月1日执行

第一级：用户自主保护级；
第二级：系统审计保护级；
第三级：安全标记保护级；
第四级：结构化保护级；
第五级：访问验证保护级。

等级保护5级大致与C1,C2,B1,B2,B3相对应：掐头(D)去尾(A)

等级保护2.0：GB/T 22239-2019《信息系统安全等级保护基本要求》：1-5级的通用要求、扩展要求

4.2 数据库安全性控制

非法使用数据库的情况

编写合法程序绕过数据库管理系统及其授权机制
直接或编写应用程序执行非授权操作
通过多次合法查询数据库从中推导出一些保密数据

在一般计算机系统中，安全措施是一级一级层层设置的，相互之间缺乏联动机制，不方便溯源，不利于整体安全控制：

系统首先根据用户标识鉴定用户身份，合法用户才准许进入计算机系统
数据库管理系统还要进行存取控制，只允许用户执行合法操作
操作系统有自己的保护措施
数据可以以密码形式存储到数据库中

计算机系统的安全模型

数据库安全保护的存取控制流程

首先，数据库管理系统对提出 SQL 访问请求的数据库用户进行身份鉴别，防止不可信用户使用系统。
然后，在 SQL 处理层进行自主存取控制和强制存取控制，进一步还可以进行推理控制。
为监控恶意访问，可根据具体安全需求配置审计规则，对用户访问行为和系统关键操作进行审计，对异常用户行为进行简单入侵检测。

以下为数据库安全性控制的常用方法。

4.2.1 用户身份鉴别

用户身份鉴别是数据库管理系统提供的最外层安全保护措施。

每个用户在系统中都有一个用户标识，每个用户标识由用户名（user name）和用户标识号（UID）组成，用户标识号在系统的整个生命周期内是唯一的。

1. 静态口令鉴别

静态口令一般由用户自己设定，这些口令是静态不变的。
可设置password的复杂度要求、过期的时间，用户访问的有效IP范围等
特点：简单，容易被攻击，安全性较低

2. 动态口令鉴别

口令是动态变化的，每次鉴别时均需使用动态产生的新口令登录数据库管理系统，即采用一次一密的方法。
常用方式：短信密码和动态令
特点：与静态口令鉴别相比，安全性相对高一些

3. 生物特征鉴别

通过生物特征进行认证的技术，生物特征是指生物体唯一具有的，可测量、识别和验证的稳定生物特征，如指纹、虹膜和掌纹等。
特点：与传统的口令鉴别相比，安全性较高

4. 智能卡鉴别

智能卡是一种不可复制的硬件，内置集成电路的芯片，具有硬件加密功能。
智能卡由用户随身携带，登录数据库管理系统时用户将智能卡插入专用的读卡器进行身份验证。
由于每次从智能卡中读取的数据是静态的，通过内存扫描或网络监听等技术还是可能截取到用户的身份验证信息，存在安全隐患。因此，实际应用中一般采用个人身份识别码（PIN）和智能卡相结合的方式。

5. 多因子认证MFA(Multi-Factor Authentication)

一种身份验证方法，要求用户除了密码以外至少再提供一个身份验证因素，或者至少提供两个非密码的身份验证因素，才能访问网站、应用或网络等

4.2.2 存取控制

存取控制机制主要包括定义用户权限和合法权限检查两部分。

（1）定义用户权限，并将用户权限登记到数据字典中

用户对某一数据对象(资源)的操作(如增删改查)权力称为权限
数据库管理系统提供适当的语言来定义用户权限，这些定义经过编译后存储在数据字典中，被称做安全规则或授权规则。

（2）合法权限检查

用户发出存取数据库操作请求，数据库管理系统查找数据字典，根据安全规则进行合法权限检查。

权限定义和合法权检查机制一起组成了数据库管理系统的存取控制子系统。

自主存取控制（Discretionary Access Control, DAC）

C2级：非常灵活
用户对不同的数据库对象有不同的存取权限
不同的用户对同一对象也有不同的权限
用户还可将其拥有的存取权限转授给其他用户

强制存取控制（Mandatory Access Control, MAC）

B1级：相对比较严格
每一个数据库对象（客体）被标以一定的密级
每一个用户（主体）也被授予某一个级别的许可证
对于任意一个对象，只有具有合法许可证的用户才可以存取

4.2.3 自主存取控制方法

自主存取控制主要通过 SQL 的 GRANT 语句和 REVOKE 语句实现。
用户权限由数据库对象和操作类型组成。
定义用户的存取权限：就是定义用户可以在哪些数据库对象上进行哪些类型的操作。
在数据库系统中，定义存取权限称为授权（authorization）。
在非关系系统中，用户只能对数据进行操作，存取控制的数据库对象也仅限于数据本身。
在关系数据库系统中，存取控制的对象不仅有数据本身（基本表中的数据、属性列上的数据），还有数据库模式（包括模式、基本表、视图和索引的创建等）。

关系数据库系统中存取控制对象：2大类：系统权限和（数据）对象权限

4.2.4 授权：授予与收回

1. GRANT

GRANT 语句的一般格式：

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

语义：将对指定操作对象的指定操作权限授予指定的用户。

发出 GRANT 语句的可以是：

数据库管理员
数据库对象创建者（即属主 owner）、
已经拥有该权限的用户（WITH GRANT OPTION）

按受权限的用户可以是：

一个或多个具体用户
PUBLIC（即全体用户）

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

指定 WITH GRANT OPTION 子句：获得某种权限的用户可以把这种权限再授予其他的用户。

没有指定 WITH GRANT OPTION 子句：获得某种权限的用户只能使用该权限，不能传播该权限。

把查询 Student 表权限授给用户 U1：

1	`GRANT SELECT ON TABLE Student TO U1;`

把查询 Student 表和修改学生学号的权限授给用户 U4：

1	`GRANT UPDATE(Sno), SELECT ON TABLE Student TO U4;`

对属性列授权时必须明确指出相应的属性列名。

2. REVOKE

授予用户的权限可以由数据库管理员或其他授权者用 REVOKE 语句收回。

REVOKE 语句的一般格式为：

1
2
3

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

把用户 U4 修改学生学号的权限收回：

1	`REVOKE UPDATE(Sno) ON TABLE Student FROM U4;`

收回所有用户对表 SC 的查询权限：

1	`REVOKE SELECT ON TABLE SC FROM PUBLIC;`

3. 创建数据库模式的权限

对创建数据库模式一类的数据库对象的授权由数据库管理员在创建用户时实现。

CREATE USER 语句格式：

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

说明：

只有系统的超级用户才有权创建一个新的数据库用户。
新创建的数据库用户有三种权限：CONNECT、RESOURCE 和 DBA（Oracle中三个都一样）。
如没有指定创建的新用户的权限，默认该用户拥有 CONNECT 权限。拥有 CONNECT 权限的用户不能创建新用户，不能创建模式，也不能创建基本表， 只能登录数据库。
拥有 RESOURCE 权限的用户能创建基本表和视图，成为所创建对象的属主。但不能创建模式，不能创建新的用户。
拥有 DBA 权限的用户是系统中的超级用户，可以创建新的用户、创建模式、创建基本表和视图等；DBA拥有对所有数据库对象的存取权限，还可以把这些权限授予一般用户。

注：CREATE USER 语句不是 SQL 标准，因此不同的关系数据库管理系统的语法和内容相差甚远。

4. Oracle的权限管理

2大类权限：

系统权限
- 允许用户执行特定的数据库动作，如创建表、创建索引、连接实例等。
- 数据字典：system_privilege_map
- 传递授权：with admin option
- 例如：CREATE TABLE，DROP ANY TABLE (NO DROP TABLE priv)
对象权限
- 允许用户操纵一些特定对象，如读取视图，可更新某些列、执行存储过程等。
- 数据字典：table_privilege_map
- 传递授权：with grant option
- 数据对象的owner拥有该对象的所有权限

Oracle权限管理的基本原则：

User对其schema下的对象具有全部的权限，不用Grant授权
如果一个用户没有被Grant授权过，也就无法使用Revoke取消该权限

4.2.5 数据库角色 (Role)

数据库角色是被命名的一组与数据库操作相关的权限，角色是权限的集合。可以为一组具有相同权限的用户创建一个角色，使用角色来管理数据库可以简化授权的过程。

1. 角色的创建

1	`CREATE ROLE <角色名>;`

刚刚创建的角色是空的，没有任何内容。

2. 给角色授权

1
2
3

GRANT <权限>[,<权限>]…
ON <对象类型>对象名
TO <角色>[,<角色>]…

数据库管理员和用户可以利用 GRANT 语句将权限授予某一个或几个角色。

Role-Based Access Control (RBAC) 支持的三个安全原则：

最小权限原则：将用户角色配置成其完成任务所需要的最小的权限集
责任分离原则：通过调用相互独立互斥的角色来共同完成敏感的任务
数据抽象原则：通过权限的抽象来体现

静态职责分离（Static Separation of Duty, SSD）：当角色授权给用户时，不能同时将两个冲突的角色授权给用户，只能选择其一。

动态职责分离（Dynamic Separation of Duty, DSD）：在角色分配时可以将冲突的角色赋予给同一个用户，但是在用户使用系统时，一次会话中不能同时激活两个角色。

3. 将一个角色授予其他的角色或用户

1
2
3

GRANT <角色1>[,<角色2>]…
TO <角色3>[,<用户1>]…
[WITH ADMIN OPTION];

该语句把角色授予某用户，或授予另一个角色。

授予者或者是角色的创建者，或拥有在这个角色上的ADMIN OPTION。

如果指定了WITH ADMIN OPTION，则获得某种权限的角色或用户还可以把这种权限再授予其他角色。

一个角色包含的权限包括直接授予这个角色的全部权限加上其他角色授予这个角色的全部权限。

4. 角色权限的收回

1
2
3

REVOKE <权限>[,<权限>]…
ON <对象类型> <对象名>
FROM <角色>[,<角色>]…;

用户可以收回角色的权限，从而修改角色拥有的权限。

REVOKE 动作的执行者：角色的创建者、拥有在这个（些）角色上的 ADMIN OPTION。

通过角色来实现将一组权限授予一个用户：

步骤如下：

① 首先创建一个角色 R1。

1	`CREATE ROLE R1;`

② 然后使用 GRANT 语句，使角色 R1 拥有 Student 表的 SELECT、UPDATE、INSERT 权限。

1	`GRANT SELECT, UPDATE, INSERT ON TABLE Student TO R1;`

③ 将这个角色授予王平，张明，赵玲。使他们具有角色 R1 所包含的全部权限。

1	`GRANT R1 TO 王平,张明,赵玲;`

④ 可以一次性通过 R1 来回收王平的这 3 个权限。

1	`REVOKE R1 FROM 王平;`

4.2.6 强制存取控制方法

自主存取控制（DAC）缺点：可能存在数据的“无意泄露”

原因：这种机制仅仅通过对数据的存取权限来进行安全控制，而数据本身并无安全性标记
解决：对系统控制下的所有主客体实施强制存取控制策略

强制存取控制（MAC）：系统为保证更高程度的安全性，按照 TDI/TCSEC 标准中安全策略的要求所采取的强制存取检查手段

用户不能直接感知或进行控制
适用于对数据有严格而固定密级分类的部门，如军事部门、政府部门

在强制存取控制中，数据库管理系统所管理的全部实体被分为主体和客体两大类。

主体是系统中的活动实体，包括数据库管理系统所管理的实际用户和代表用户的各进程。
客体是系统中的被动实体，受主体操纵，包括文件、基本表、索引、视图等。
对于主体和客体，数据库管理系统为它们每个实例（值）指派一个敏感度标记（label）。

敏感度标记被分成若干级别：

绝密（Top Secret，TS）、机密（Secret，S）、可信（Confidential，C）、公开（Public，P）
密级的次序：TS>=S>=C>=P
主体的敏感度标记称为许可证级别（clearance level）
客体的敏感度标记称为密级（classification level）

强制存取控制机制就是通过对比主体的敏感度标记和客体的敏感度标记，最终确定主体是否能够存取客体。

保密性规则：（MAC—BLP模型）

下读：用户级别高于文件级别的读操作
上写：用户级别低于文件级别的写操作
保证数据只能从低级别往高级别流动

数据完整性规则：（Biba模型）

下写：用户级别高于文件级别的写操作。
上读：用户级别低于文件级别的读操作。
保证数据只能从高级别往低级别流动

强制存取控制规则：（下读，上写）

（1）仅当主体的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体。

（2）仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体。

强制存取控制是对数据本身进行密级标记，无论数据如何复制，标记与数据是一个不可分的整体，只有符合密级标记要求的用户才可以操纵数据，从而提供了更高级别的安全性。
较高安全性级别提供的安全保护要包含较低级别的所有保护，因此在实现强制存取控制时要首先实现自主存取控制。
自主存取控制与强制存取控制共同构成数据库管理系统的安全机制。
提供了更高级别的安全性

DAC+MAC安全检查：

系统首先进行自主存取控制检查，对通过自主存取控制检查的允许存取的数据库对象再由系统自动进行强制存取控制检查，只有通过强制存取控制检查的数据库对象方可存取。

4.3 视图机制

可以为不同的用户定义不同的视图，把数据对象限制在一定的范围内。即通过视图机制把要保密的数据对无权存取这些数据的用户隐藏起来，从而自动对数据提供一定程度的安全保护。

视图机制间接地实现支持存取谓词的用户权限定义。

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

/* 先建立计算机系学生的视图 CS_Student */
CREATE VIEW CS_Student AS SELECT * FROM Student WHERE Sdept='CS'; 
/* 在视图上进一步定义存取权限 */ 
GRANT SELECT ON CS_Student TO 王平; 
GRANT ALL PRIVILIGES ON CS_Student TO 张明;

4.4 审计（Audit）

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

审计员可以利用审计日志监控数据库中的各种行为，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容等。

C2 以上安全级别的数据库管理系统必须具有审计功能。

审计通常是很费时间和空间的
数据库管理员可以根据具体应用对安全性的要求，灵活地打开或关闭审计功能
审计功能主要用于安全性要求较高的部门

1. 审计事件

服务器事件：审计数据库服务器发生的事件，包括数据库服务器的启动、停止、数据库服务器配置文件的重新加载。
系统权限：对系统拥有的结构或模式对象进行操作的审计，要求该操作的权限是通过系统权限获得的。
语句事件：对 SQL 语句，如 DDL、DML、DQL 及 DCL 语句的审计。
模式对象事件：对特定模式对象上进行的 SELECT 或 DML 操作的审计。模式对象包括表、视图、存储过程、函数等，不包括依附于表的索引、约束、触发器、分区表等。

2. 审计功能

基本功能，提供多种审计查阅方式
提供多套审计规则：一般在初始化设定，以方便审计员管理
提供审计分析和报表功能
审计日志管理功能，包括为防止审计员误删审计记录，审计日志必须先转储后删除；对转储的审计记录文件提供完整性和保密性保护；只允许审计员查阅和转储审计记录，不允许任何用户新增和修改审计记录等
系统提供查询审计设置及审计记录信息的专门视图

3. AUDIT 语句和 NOAUDIT 语句

AUDIT 语句用来设置审计功能，NOAUDIT 语句取消审计功能。

审计一般可以分为用户级审计和系统级审计。

用户级审计

任何用户可设置的审计
主要是用户针对自己创建的数据库表和视图进行审计

系统级审计

只能由数据库管理员设置
监测成功或失败的登录要求、监测授权和收回操作以及其他数据库级权限下的操作

【例 4.15】对修改 SC 表结构或修改 SC 表数据的操作进行审计。

1	`AUDIT ALTER,UPDATE ON SC;`

【例 4.16】取消对 SC 表的一切审计。

1	`NOAUDIT ALTER,UPDATE ON SC;`

数据库安全审计系统提供了一种事后检查的安全机制。

涉密信息系统中的“三员”：

系统管理员
安全保密管理员
安全审计管理员

4.5 数据加密

数据加密是防止数据库中数据在存储和传输中失密的有效手段。

加密的基本思想是根据一定的算法将原始数据——明文（plain text）变换为不可直接识别的格式——密文（cipher text），从而使得不知道解密算法的人无法获知数据的内容。

数据加密主要包括存储加密和传输加密。

1. 存储加密

透明存储加密（TDE）：内核级加密保护方式，对用户完全透明

透明存储加密是数据在写到磁盘时对数据进行加密，授权用户读取数据时再对其进行解密。
数据在数据库内存中是以明文形态存在的，而在数据文件中则以密文形态存在。
由于数据加密对用户透明，数据库的应用程序不需要做任何修改，只需在创建表语句中说明需加密的字段即可。
基于数据库内核的数据存储加密、解密方法性能较好，安全完备性较高。
缺点：仅在静止状态下保护数据、防护颗粒度较粗、数据库类型适用性上有限制。

非透明存储加密：通过多个加密函数实现

2. 传输加密

链路加密

在链路层进行加密
传输信息由报头（路由选择信息）和报文（传送的数据信息）两部分组成
对报文和报头均加密

端到端加密

在发送端加密，接收端解密
只加密报文，不加密报头
只在发送端和接收端需要密码设备，而中间节点不需要密码设备，因此它所需密码设备数量相对较少
不加密报头，从而容易被非法监听者发现并从中获取敏感信息

基于安全套接层协议（Security Socket Layer，SSL）传输方案的实现思路：

（1）确认通信双方端点的可靠性

数据库管理系统采用基于数字证书的服务器和客户端认证方式，实现通信双方的可靠性确认。
用户和服务器各自持有由知名数字证书认证中心或企业内建 CA 颁发的数字证书，双方在进行通信时，均首先向对方提供己方证书，然后使用本地的 CA 信任列表和证书撤销列表对接收到的对方证书进行验证。

（2）协商加密算法和密钥

确认双方端点的可靠性后，通信双方协商本次会话的加密算法与密钥。
通信双方利用公钥基础设施方式保证了服务器和客户端的协商过程通信的安全可靠。

（3）可信数据传输

业务数据在被发送之前将被用某一组特定的密钥进行加密和消息摘要计算，以密文形式在网络上传输。
当业务数据被接收的时候，需用相同一组特定的密钥进行解密和摘要计算。

4.6 其他安全性保护

推理控制（inference control）

数据推理(Inference)可以根据合法的非敏感数据推导出非法的敏感数据
处理强制存取控制未解决的问题
避免用户利用其能够访问的数据推知更高密级的数据
常用方法：基于函数依赖的推理控制、基于敏感关联的推理控制

隐蔽信道（covert channel）

处理强制存取控制未解决的问题

数据隐私（data privacy）

数据隐私是控制不愿被他人知道或他人不便知道的个人数据的能力。
数据隐私范围很广，涉及数据管理中的数据收集、数据存储、数据处理和数据发布等各个阶段。

数据库脱敏

利用数据脱敏技术将数据库中的敏感数据进行变换处理，在保持数据按需使用目标的同时，又能避免敏感数据泄露。
数据脱敏(Data Masking)按照脱敏规则对敏感数据进行变换，去除标识数据，数据实现匿名化处理，从而实现敏感数据的保护。
常见的数据脱敏技术方法有屏蔽、变形、替换、随机、加密等

数据库防火墙

通过SQL 协议分析，根据预定义的禁止/许可策略让合法的SQL 操作通过，阻断非法违规操作，形成数据库的外围防御圈，实现SQL 危险操作的主动预防、实时审计。
攻击检测、防止漏洞利用、防止敏感数据泄露、数据库安全审计

第五章数据库完整性

数据库的完整性（integrity）是指数据的正确性（correctness）和相容性（compat- ability）。

数据的正确性：指数据是符合现实世界语义、反映当前实际状况的
数据的相容性：指数据库同一对象在不同关系表中的数据是符合逻辑的

数据的完整性和安全性是两个既有联系又不尽相同的概念：

数据的完整性是为了防止数据库中存在不符合语义的数据，也就是防止数据库中存在不正确的数据。

防范对象：不合语义的、不正确的数据，防止它们进入数据库。
数据的安全性是保护数据库防止恶意破坏和非法存取。

防范对象：非法用户和非法操作，防止他们对数据库数据的非法存取。

为维护数据库的完整性，数据库管理系统必须：

提供定义完整性约束条件的机制
- 完整性约束条件也称为完整性规则，是数据库中的数据必须满足的语义约束条件。
- SQL 标准使用了一系列概念来描述完整性，包括关系模型的实体完整性、参照完整性和用户定义完整性。
- 这些完整性一般由 SQL 的数据定义语言语句来实现。
提供完整性检查的方法
- 数据库管理系统中检查数据是否满足完整性约束条件的机制称为完整性检查。
- 一般在 INSERT、UPDATE、DELETE 语句执行后开始检查，也可以在事务提交时检查。
进行违约处理
- 数据库管理系统若发现用户的操作违背了完整性约束条件，将采取一定的动作：拒绝（NO ACTION）执行该操作或级联（CASCADE）执行其他操作

5.1 实体完整性

5.1.1 定义实体完整性

关系模型的实体完整性：在 CREATE TABLE 中用 PRIMARY KEY 定义。
单属性构成的码有两种说明方法：定义为列级约束条件、定义为表级约束条件
对多个属性构成的码只有一种说明方法：定义为表级约束条件

【例 5.1】将 Student 表中的 Sno 属性定义为码。

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)   /* 在表级定义主码 */
);

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

用 PRIMARY KEY 短语定义了关系的主码后，每当用户程序对基本表插入一条记录或对主码列进行更新操作时，关系数据库管理系统按照实体完整性规则自动进行检查。包括：

（1）检查主码值是否唯一，如果不唯一则拒绝插入或修改。

（2）检查主码的各个属性是否为空，只要有一个为空就拒绝插入或修改。

检查记录中主码值是否唯一的一种方法是进行全表扫描，依次判断表中每一条记录的主码值与将插入记录的主码值（或者修改的新主码值）是否相同。

表扫描的缺点：十分耗时。

为避免对基本表进行全表扫描，RDBMS一般都在主码上自动建立一个索引。

5.2 参照完整性

5.2.1 定义参照完整性

关系模型的参照完整性定义

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

例如，关系 SC 中一个元组表示一个学生选修的某门课程的成绩，（Sno，Cno）是主码，Sno、Cno 分别参照 Student 表的主码和 Course 表的主码。

【例 5.3】定义 SC 中的参照完整性。

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 Course(Cno)   /*在表级定义参照完整性*/ 
    );

5.2.2 参照完整性检查和违约处理

参照完整性将两个表中的相应元组联系起来。

对被参照表和参照表进行增、删、改操作时有可能破坏参照完整性，必须进行检查以保证两个表的相容性。

例如，对表 SC 和 Student 有 4 种可能破坏参照完整性的情况：

SC 表中增加一个元组，该元组的 Sno 属性值在表 Student 中找不到一个元组，其 Sno 属性值与之相等。
修改 SC 表中的一个元组，修改后该元组的 Sno 属性值在表 Student 中找不到一个元组，其 Sno 属性值与之相等。
从 Student 表中删除一个元组，造成 SC 表中某些元组的 Sno 属性值在表 Student 中找不到一个元组，其 Sno 属性值与之相等。
修改 Student 表中一个元组的 Sno 属性，造成 SC 表中某些元组的 Sno 属性值在表 Student 中找不到一个元组，其 Sno 属性值与之相等。

当上述的不一致发生时，系统可以采用以下策略加以处理：

拒绝（NO ACTION）执行：不允许该操作执行。该策略一般设置为默认策略。
级联（CASCADE）操作：当删除或修改被参照表（Student）的一个元组导致与参照表（SC）的不一致时，删除或修改参照表中的所有导致不一致的元组。
设置为空值：当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。

对于参照完整性，除了应该定义外码，还应定义外码列是否允许空值。

【例 5.4】显式说明参照完整性的违约处理示例。

CREATE TABLE SC
    (Sno CHAR(9) NOT NULL,
     Cno CHAR(4) NOT NULL,
     Grade SMALLINT,
     PRIMARY KEY(Sno,Cno),  /*在表级定义实体完整性，Sno,Cno 都不能取空值*/ 
     FOREIGN KEY (Sno) REFERENCES Student(Sno)      /*在表级定义参照完整性*/ 
     ON DELETE CASCADE      /*级联删除SC表中相应的元组*/ 
     ON UPDATE CASCADE,     /*级联更新SC表中相应的元组*/  
     FOREIGN KEY (Cno) REFERENCES Course(Cno)       /*在表级定义参照完整性*/ 
     ON DELETE NO ACTION    /*当删除course 表中的元组造成与SC表不一致时拒绝删除*/ 
     ON UPDATE CASCADE      /*当更新course表中的cno时，级联更新SC表中相应的元组*/ 
    );

5.3 用户定义的完整性

用户定义的完整性是：针对某一具体应用的数据必须满足的语义要求。

关系数据库管理系统提供了定义和检验用户定义完整性的机制，不必由应用程序承担。

5.3.1 属性上的约束条件

1. 属性上约束条件的定义

在 CREATE TABLE 中定义属性的同时，可根据应用要求定义属性上的约束条件，即属性值限制。包括：

列值非空（NOT NULL）
列值唯一（UNIQUE）
检查列值是否满足一个条件表达式（CHECK 短语）

【例 5.8】要求 SC 表的 Grade 的值应该在 0 和 100 之间：

CREATE TABLE SC
    (Sno CHAR(9),
     Cno CHAR(4), 
     Grade SMALLINT CHECK (Grade>=0 AND Grade<=100), /*Grade取值范围是0到100*/ 
     PRIMARY KEY (Sno,Cno), 
     FOREIGN KEY (Sno) REFERENCES Student(Sno),  
     FOREIGN KEY (Cno) REFERENCES Course(Cno)
    );

2. 属性上约束条件的检查和违约处理

当往表中插入元组或修改属性的值时，关系数据库管理系统将检查属性上的约束条件是否被满足，如果不满足则操作被拒绝执行。

5.3.2 元组上的约束条件

1. 元组上约束条件的定义

在 CREATE TABLE 语句中可以用 CHECK 短语定义元组上的约束条件，即元组级的限制。

同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件。

【例 5.9】当学生的性别是男时，其名字不能以 Ms. 打头。

CREATE TABLE Student
    (Sno CHAR(9), 
     Sname CHAR(8) NOT NULL, 
     Ssex CHAR(2),  
     Sage SMALLINT, 
     Sdept CHAR(20),  
     PRIMARY KEY (Sno), 
     CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')
    );      /*定义了元组中Sname和Ssex两个属性值之间的约束条件*/

2. 元组上约束条件的检查和违约处理

当往表中插入元组或修改属性的值时，关系数据库管理系统将检查元组上的约束条件是否被满足，如果不满足则操作被拒绝执行。

5.4 完整性约束命名子句

1. 完整性约束命名子句

完整性约束命名子句：

1	`CONSTRAINT <完整性约束条件名> <完整性约束条件>`

<完整性约束条件> 包括 NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK 短语等。

【例 5.10】建立学生登记表 Student，要求学号在 90000~99999 之间，姓名不能取空值，年龄小于 30，性别只能是“男”或“女” 。

CREATE TABLE Student
    (Sno NUMERIC(6) 
     CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
     Sname CHAR(20)
     CONSTRAINT C2 NOT NULL,
     Sage NUMERIC(3)
     CONSTRAINT C3 CHECK (Sage<30),
     Ssex CHAR(2)
     CONSTRAINT C4 CHECK (Ssex IN ('男','女')),
     CONSTRAINT StudentKey PRIMARY KEY(Sno) 
    );

【例 5.11】建立教师表 TEACHER，要求每个教师的应发工资不低于 3000 元。应发工资是工资列 Sal 与扣除项 Deduct 之和。

CREATE TABLE TEACHER
    (Eno NUMERIC(4) PRIMARY KEY     /*在列级定义主码*/
     Ename CHAR(10),
     Job CHAR(8),
     Sal NUMERIC(7,2),
     Deduct NUMERIC(7,2), 
     Deptno NUMERIC(2),
     CONSTRAINT TEACHERFKey FOREIGN KEY(Deptno)
     REFERENCES DEPT(Deptno),
     CONSTRAINT C1 CHECK (Sal + Deduct >= 3000)
    );

2. 修改表中的完整性限制

使用 ALTER TABLE 语句修改表中的完整性限制。

【例 5.12】去掉例 5.10 Student 表中对性别的限制。

1	`ALTER TABLE Student DROP CONSTRAINT C4;`

【例 5.13】修改表 Student 中的约束条件，要求学号改为在 900000~999999 之间，年龄由小于 30 改为小于 40。

/* 可以先删除原来的约束条件，再增加新的约束条件。 */
ALTER TABLE Student
    DROP CONSTRAINT C1;
ALTER TABLE Student 
    ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999);
ALTER TABLE Student
    DROP CONSTRAINT C3;
ALTER TABLE Student
    ADD CONSTRAINT C3 CHECK(Sage < 40);

*5.5 域中的完整性限制

5.6 断言

SQL中，可以使用 ASSERTION 语句，通过声明性断言来指定更具一般性的约束。

可以定义涉及多个表的或聚集操作的比较复杂的完整性约束。

断言创建以后，任何对断言所涉及的关系的操作都会触发关系数据库管理系统对断言的检查，任何使断言不为真值的操作都会被拒绝执行。

1. 创建断言的语句格式

1	`CREATE ASSERTION <断言名>`

每个断言都被赋予一个名字，中的约束条件与 WHERE 子句的条件表达式类似。

【例 5.18】限制数据库课程最多 60 名学生选修。

CREATE ASSERTIONASSE_SC_DB_NUM 
    CHECK (60>=(SELECT COUNT(*) /*此断言的谓词涉及聚集操作 count 的 SQL 语句*/ 
                FROM Course,SC 
                WHERE SC.Cno=Course.Cno AND Course.Cname='数据库')
          );

【例 5.19】限制每一门课程最多 60 名学生选修。

CREATE ASSERTION ASSE_SC_CNUM1 
    CHECK(60>=ALL (SELECT COUNT(*) /* 此断言的谓词，涉及聚集操作 count */
                   FROM SC         /* 和分组函数 group by 的SQL语句 */
                   GROUP BY CNO) 
         );

【例 5.20】限制每个学期每一门课程最多 60 名学生选修。

/* 首先修改 SC 表的模式，增加一个“学期（TERM）”的属性。 */
ALTER TABLE SC ADD TERM DATE;
/* 然后定义断言 */
CREATE ASSERTION ASSE_SC_CNUM2
    CHECK (60 >= ALL (SELECT COUNT(*) FROM SC GROUP BY CNO,TERM) );

2. 删除断言的语句格式

1	`DROP ASSERTION <断言名>;`

如果断言很复杂，则系统在检测和维护断言上的开销较高，这是在使用断言时应该注意的。

5.7 触发器

触发器（trigger）是用户定义在关系表上的一类由事件驱动的特殊过程。

触发器保存在数据库服务器中
任何用户对表的增、删、改操作均由服务器自动激活相应的触发器，在关系数据库管理系统核心层进行集中的完整性控制
触发器类似于约束，但是比约束更加灵活，可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力

不同的关系数据库管理系统实现触发器的语法各不相同、互不兼容。

5.7.1 定义触发器

触发器又叫做事件-条件-动作（event-condition-action）规则。

当特定的系统事件（如对一个表的增、删、改操作，事务的结束等）发生时，对规则的条件进行检查，如果条件成立则执行规则中的动作，否则不执行该动作。

规则中的动作体可以很复杂，可以涉及其他表和其他数据库对象，通常是一段 SQL 存储过程。

建立触发器的一般格式：

CREATE TRIGGER <触发器名>             /*每当触发事件发生时，该触发器被激活*/
{BEFORE|AFTER} <触发事件> ON <表名>    /*指明触发器激活的时间是在执行触发事件前或后*/
REFERENCING NEW|OLD ROWAS<变量>       /* REFERENCING 指出引用的变量*/ 
FOR EACH {ROW|STATEMENT}             /* 定义触发器的类型，指明动作体执行的频率*/ 
[WHEN <触发条件>] <触发动作体>           /* 仅当触发条件为真时才执行触发动作体*/

定义触发器的语法说明

只有表的拥有者，即创建表的用户才可以在表上创建触发器。
触发器名
- 触发器名可以包含模式名，也可以不包含模式名。
- 同一模式下，触发器名必须是唯一的，且触发器名和表名必须在同一模式下。
表名
- 触发器只能定义在基本表上，不能定义在视图上。
- 当基本表的数据发生变化时，将激活定义在该表上相应触发事件的触发器。
触发事件
- 触发事件可以是 INSERT、DELETE 或 UPDATE，也可以是这几个事件的组合
- 还可以是 UPDATE OF <触发列，...>，即进一步指明修改哪些列时激活触发器。
- AFTER/BEFORE 是触发的时机，AFTER 表示在触发事件的操作执行之后激活触发器，BEFORE 表示在触发事件的操作执行之前激活触发器。
触发器类型：行级触发器（FOR EACH ROW）和语句级触发器（FOR EACH STATEMENT）

例如，在例 5.11 的 TEACHER 表上创建一个 AFTER UPDATE 触发器，触发事件是 UPDATE 语句：
1
UPDATE TEACHER SET Deptno=5;
假设表 TEACHER 有 1000 行，如果定义的触发器为语句级触发器，那么执行完该语句后触发动作只发生一次；如果是行级触发器，触发动作将执行 1000 次。
触发条件
- 触发器被激活时，只有当触发条件为真时触发动作体才执行，否则触发动作体不执行。
- 如果省略 WHEN 触发条件，则触发动作体在触发器激活后立即执行。
触发动作体
- 触发动作体可以是一个匿名 PL/SQL 过程块，也可以是对已创建存储过程的调用。
- 如果是行级触发器，用户可以在过程体中使用 NEW 和 OLD 引用 UPDATE/INSERT 事件之后的新值和UPDATE/DELETE事件之前的旧值；如果是语句级触发器，则不能在触发动作体中使用 NEW 或 OLD 进行引用。
- 如果触发动作体执行失败，激活触发器的事件就会终止执行，触发器的目标表或触发器可能影响的其他对象不发生任何变化。

【例 5.21】当对表 SC 的 Grade 属性进行修改时，若分数增加了10%，则将此次操作记录到另一个表中 SC_U（Sno,Cno,Oldgrade,Newgrade）中，其中 Oldgrade 是修改前的分数，Newgrade 是修改后的分数。

CREATE TRIGGER SC_T 								/* SC_T是触发器的名字*/ 
AFTER UPDATE OF Grade ON SC 						/* UPDATE OF Grade ON SC是触发事件 */
/* AFTER是触发的时机，表示当对 SC 的 Grade 属性修改完后再触发下面的规则 */  
REFERENCING 
    OLDROW AS OldTuple,
    NEWROW AS NewTuple
FOR EACH ROW 		/* 行级触发器，即每次执行一次 Grade 的更新，下面的规则就执行一次 */ 
WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade) 		/* 触发条件，只有该条件为真时才执行 */  
    INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade) 	/* 下面的 INSERT 语句 */   
    VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

【例 5.22】将每次对表 Student 的插入操作所增加的学生个数记录到表 StudentInsertLog 中。

CREATE TRIGGER Student_Count
AFTER INSERT ON Student /* 指明触发器激活的时间是在执行 INSERT 后 */ 
REFERENCING 
    NEW TABLE AS DELTA  
FOR EACH STATEMENT      /* 语句级触发器，即执行完 INSERT 语句后下面的触发动作体才执行一次 */ 
    INSERT INTO StudentInsertLog (Numbers)  
    SELECT COUNT(*) FROM DELTA

【例 5.23】定义一个 BEFORE 行级触发器，为教师表 Teacher 定义完整性规则 “教授的工资不得低于 4000 元，如果低于 4000 元，自动改为 4000 元”。

CREATE TRIGGER Insert_Or_Update_Sal /*对教师表插入或更新时激活触发器*/
BEFORE INSERT OR UPDATE ON Teacher  /* BEFORE触发事件*/
REFERENCING NEWrow AS NewTuple FOR EACH ROW /*行级触发器*/ 
BEGIN                   			/*定义触发动作体，这是一个PL/SQL过程块*/ 
    IF (newtuple.Job='教授') AND (newtuple.Sal < 4000)  
        THEN newtuple.Sal=4000; 
    END IF;
END;                                /*触发动作体结束*/

5.6.2 激活触发器

触发器的执行是由触发事件激活，并由数据库服务器自动执行的。

一个数据表上可能定义了多个触发器，同一个表上的多个触发器激活时遵循如下的执行顺序：

执行该表上的 BEFORE 触发器
激活触发器的 SQL 语句
执行该表上的 AFTER 触发器

对于同一个表上的多个 BEFORE（AFTER）触发器，遵循“谁先创建谁先执行”的原则，即按照触发器创建的时间先后顺序执行。有些关系数据库管理系统是按照触发器名称的字母排序顺序执行触发器。

5.6.3 删除触发器

删除触发器的 SQL 语法：

1	`DROP TRIGGER <触发器名> ON <表名>;`

触发器必须是一个已经创建的触发器，并且只能由具有相应权限的用户删除。

#大三上 #期末复习 #数据库

数据库原理与安全 | 笔记1

https://harrison-1eo.github.io/2023/12/18/数据库笔记/

作者

Harrison

发布于

2023年12月18日

更新于

2024年1月1日

许可协议

数据库原理与安全 | 笔记2 上一篇

网络内容安全 | 笔记下一篇

数据库原理与安全 | 笔记1

第一章 绪论

1.1 数据库系统概述

（1）数据

（2）数据库

（3）数据库管理系统

（4）数据库系统

（5）数据库系统的特点

1.2 数据模型

1.2.1 两类数据模型

1.2.2 概念模型

1.2.3 数据模型的组成要素

1.2.4 常用的数据模型

1.2.5 层次模型

1.2.6 网状模型

1.2.7 关系模型

1.3 数据库系统的结构

1.4 数据库系统的组成

第二章 关系数据库

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

2.1.1 关系

2.1.2 关系模式

2.1.3 关系数据库

2.2 关系操作

2.3 关系的完整性

2.3.1 实体完整性

2.3.2 参照完整性

2.3.3 用户定义的完整性

2.4 关系代数

2.4.1 传统的集合运算

2.4.2 专门的关系运算

第三章 关系数据库标准语言SQL

3.1 SQL概述

3.2 学生-课程数据库

3.3 数据定义

3.3.1 模式的定义与删除

1. 模式的定义

2. 模式的删除：

3. 不同数据库对Schema的定义

3.3.2 基本表的定义、删除与修改

1. 定义基本表

2. 数据类型

3. 模式与表

4. 修改基本表

5. 删除基本表

3.3.3 索引的建立与删除

1. 建立索引

2. 修改索引

3. 删除索引

3.3.4 数据字典

3.4 数据查询

3.4.1 单表查询

1. 选择表中的若干列

2.选择表中的若干元组

3.ORDER BY子句

4.聚集函数

5.GROUP BY子句

3.4.2 连接查询

1. 等值与非等值连接查询

2. 自身连接

3. 外连接

4. 多表连接

5. Join总结

3.4.3 嵌套查询

1. 带有IN谓词的子查询

2. 带有比较运算符的子查询

3. 带有ANY（SOME）或ALL谓词的子查询

4. 带有EXISTS谓词的子查询

3.4.4 集合查询

3.4.5 基于派生表的查询

3.4.6 Select语句的一般形式

3.5 数据更新

3.5.1 插入数据

1. 插入元组

2. 插入子查询结果

3. 复制表结构/数据

3.5.2 修改数据

3.5.3 删除数据

3.6 空值的处理

3.7 视图

第一章绪论

第二章关系数据库

第三章关系数据库标准语言SQL

第四章数据库安全性

第五章数据库完整性