一. 绪论
1.1 数据库系统概论
1.1.1 数据库的4个基本概念
- 数据（Date)
- 数据库（DB）
- 数据库管理系统（DBMS）
- 数据库系统（DBS）
1. 数据（Date）
-
定义：是数据库存储的基本对象
-
数据的定义：描述事物的符号
-
数据的种类：数字、文字、图形、图像、音视频等
-
数据的含义称为数据的语义，数据和语义是不可分的
-
数据是有结构的：记录是计算机存储数据的一种格式或者方法
2. 数据库（DB）
- 定义：是长期存储在计算机内，有组织、可共享的大量数据的集合
- 建立数据库的原因：收集并抽取一个应用所需要的大量数据，将其保存，以供进一步加工处理，抽取有用信息，转换成有价值知识。
- 数据库的基本特征
- 数据可以按照一定的数据模型组织、描述和存储
- 可为各种用户共享、冗余度较小、易拓展
- 数据独立性较高
3. 数据库管理系统（DBMS)
- 定义：
- 位于用户应用和操作系统之间的一层数据库管理模型
- 是基础软件，是一个大型复杂的软件系统
-
数据库管理系统的用途：科学地组织和管理数据、高效地获取和维护数据
-
数据库管理系统的主要功能
-
数据定义功能（提供数据定义语言（DDL）/定义数据库中的数据对象）
-
数据组织、存储和管理
-
数据操纵功能（数据操纵语言（DML）)
-
数据库的事务管理和运行管理（数据库的恢复）
由数据库管理系统统一管理和控制，保证事物正确运行
-
数据库的建立和维护功能
-
其他功能
4. 数据库系统(DBS)
- 定义：是指在计算机系统中引入数据库后的系统组成
在不引起混淆的情况下，常常把数据库系统简称为数据库
- 构成：
- 数据库（DB)
- 数据库管理系统及其开发工具（DBMS）
- 应用程序
- 数据库管理员（DBA)
1.1.2 数据管理技术的产生和发展
1. 数据管理
- 定义：对数据进行分类、组织、编码、存储、检索和维护
数据处理和数据分析的中心问题
2. 发展过程
- 人工管理阶段 无结构 应用程序和数据一一对应
- 文件系统阶段 记录有结构 整体无结构 应用程序和数据一一对应
- 数据库系统阶段 整体结构化 数据共享
1.1.3 数据库系统的特点
-
数据结构化
- 数据的整体结构化
- 数据用数据模型描述，无需应用程序定义
-
数据的共享性高，冗余度低且易于扩充
- 面向整个系统，可以被多个用户、多个应用共享使用
- 减少数据冗余、节约存储空间
- 避免数据的不相容性和不一致性
- 易于扩充
- 数据共享指的是多种应用、多种语言、多个用户互相覆盖的使用数据集合
-
数据独立性高
-
物理独立性
当数据的物理存储改变了，应用程序不用改变。
-
逻辑独立性
数据的逻辑结构改变了，引用程序不用改变。
数据独立性由数据库管理系统的二级映像功能来保证。
所有要保证数据库的数据独立性，需要修改的的是三级模式之间的两层映射。
-
-
数据由数据库管理系统统一管理和控制
- 数据的安全性保护
- 数据的完整性检查
- 并发控制
- 数据库恢复
1.2 数据模型
-
数据模型是对现实世界数据特征的抽象
通俗来讲数据模型就是现实世界的模拟
-
数据模型应满足三方面要求
- 能比较真实的模拟现实世界
- 容易为人所理解
- 便于在计算机上实现
-
数据模型是数据库系统的核心和基础
1.2.1 两类数据模型
数据模型分为两类（两个不同的层次)
-
概念模型，也称信息模型
他是按照用户的观点来对数据和信息建模，用于数据库设计
-
逻辑模型和物理模型
-
逻辑模型主要包括网状模型、层次模型、关系模型、面向对象数据模型、对象关系数据模型、半结构化数据模型等。
按照计算机系统的观点对数据建模，用于DBMS实现。
-
物理模型是对数据最底层的抽象
描述数据在系统内（磁盘上）的表述方法和存取方法。
-
1.2.2 概念模型
- 概念模型的用途
- 用于信息世界的建模
- 是现实世界到机器世界的一个中间层次
- 是数据库设计的有力工具
- 数据库设计人员和用户之间进行交流的语言
- 对概念模型的基本要求
- 有较强的语义表达能力
- 简单、清晰、易于用户理解
- 信息世界的基本概念
-
实体：客观存在并且可以相互区别的事物称为实体，可以是具体 的人、事、物品、或抽象的概念、
-
属性：实体所具有的某一特性叫做属性，一个实体可以由若干个属性刻画
-
码：唯一标识实体的属性集称为码
-
实体型：用实体名及其属性名集合来抽象和刻画同类实体称为实体型
-
实体集：同一类型实体的集合称为实体集
-
联系
- 实体内部的联系：是指组成实体的各属性之间的联系
- 实体之间的联系：通常是指不同实体集之间的联系
实体之间的联系有一对一（1：1）
一对多（1：m）
多对多（m：n）
等多种类型。
概念模型的一种表示方法：实体-联系方法
- 用E-R图来描述现实世界 的概念模型
- E-R图的方法也称为E-R模型
1.2.3 数据模型的组成要素
-
数据模型是严格定义的一组概念的集合
精确的描述了系统的静态特性、动态特性和完整性约束条件
-
数据模型由三部分组成
- 数据结构–描述系统的静态特性
- 数据操作–描述系统的动态特性
- 完整性约束
1. 数据结构
-
刻画数据模型性质的重要方面
数据结构的类型来命名数据模型
层次结构–层次模型 网状结构–网状模型 关系结构–关系模型
-
描述数据库的组成对象–对象的类型、内容、性质
-
描述对象之间的联系
2. 数据操作
-
对数据库中的各种对象的实例允许执行的操作的集合
包括操作和有关的操作规则
-
数据操作的类型
- 查询
- 更新（包括插入、删除、修改）
-
数据操作语言
- 定义数据操作的确切含义、符号、优先级别
- 实现数据操作的语言
- 查询语言（QL）
- 更新语言（DML）
3. 数据完整性约束条件
- 一组完整性规则的集合
- 完整性集合：给定数据模型中数据及其联系所具有的制约和依存关系；
- 用以限定符合数据类型的数据库状态及其数据变化，以保证数据的正确、有效和相容。
- 数据模型对完整性约束条件的定义
- 反映和规定必须遵守基本的通用的完整性约束条件；
- 提供定义完整性约束条件的机制，以反映具体应用所涉及的数据必须遵守的特定的语义约束条件。
1.2.4 常用的数据模型
格式化模型（层次模型、网状模型）中数据结构的单位：基本层次联系
1.2.5 层次模型
1. 基础
-
层次模型是树形结构来表述各类实体以及实体之间的联系
-
表示方法
- 实体型：用记录来描述 每一个结点表示一个记录类型（实体）
- 属性：用字段来描述 每个记录类型可包含若干个字段
- 联系：用结点之间的连线来表示记录类型（实体）之间的一对多的父子联系
-
层次模型的定义
满足下面两个条件的基本层次联系的集合称为层次模型
- 有且仅有一个结点没有双亲结点，这个结点称为根结点
- 根结点以外的其他结点有且仅有一个双亲结点
-
特点
- 结点的双亲结点是唯一的
- 只能处理一对多的实体联系
- 任何记录值只能按其路径查看
- 没有一个子女记录值能脱离双亲记录值而独立存在
2. 层次模型的数据操纵与完整性约束
- 层次模型的数据操纵
- 查询
- 插入
- 删除、
- 更新
- 层次模型的完整性约束条件
- 无相应的双亲结点就不能插入子女结点值
- 如果删除双亲结点，则相应的子女结点也被同时删除
- 更新操作时，应更新所有相应记录，以保证数据的一致性
3. 优缺点
- 优点
- 层次模型的数据结构性比较简单清晰
- 查询效率高，性能优于关系模型，不低于网状模型
- 层次数据模型提供了良好 的完整性支持
- 缺点
- 结点之间的多对多联系不自然
- 对插入和删除操作的限制多，应用程序的编写比较复杂
- 查询子女结点必须通过双亲结点
- 层次数据库命令趋于程序化
1.2.6 网状结构
1. 基础
-
网状数据库系统采用网状结构来表示各类实体以及实体之间的关系
-
表示方法
-
实体型：用记录类型描述
每个结点表示一个记录类型（实体）
-
属性：用字段描述
每个记录类型可包含若干字段
-
联系：用结点之间的连线表示记录类型(实体)之间的一对多的父子联系
-
-
网状模型的定义：
满足下面两个条件的基本层次联系的集合
- 允许一个以上的结点无双亲
- 一个结点可以有多于一个的双亲
-
多对多联系在网状模型中的表示
- 网状模型间接表示多对多联系
- 方法
- 将多对多分解成多个一对多联系
2. 网状模型的数据操纵与完整性约束
- 导航式的查询语言和增删改操作语言
- 完整性约束条件不严格
- 允许插入尚未确定双亲结点值的子女结点值；
- 允许值删除双亲结点值。
- 实际的网状数据库系统提供了一定的完整性约束条件
- 支持码的概念：唯一标识记录的数据项的集合，取唯一的值；
- 保证一个记录中双亲记录和子女记录之间是一对多联系；
- 可以定义双亲记录和子女记录之间的某些约束条件。
3. 优缺点
- 优点
- 能够更为直观的描述现实世界，如一个结点可以有多个双亲；
- 具有良好的性能，存取效率较高。
- 缺点
- 结构比较复杂；
- DDL\DML语言复杂，不易使用；
- 记录之间联系是通过存取路径实现的，应用程序必须选择存取路径，加重了程序员的负担。
1.2.7 关系模型
1. 基础
-
关系数据库系统采用的是关系模型作为数据的组织方式
-
数据库厂商推出的数据库管理系统几乎都支持关系模型
-
在用户观点下，关系模型中的数据的逻辑结构是一张二维表
-
关系模型的数据结构的一些术语
-
关系：一个关系对应通常说的一张表
-
元组：表中的一行即为一个元组
-
属性：表中的一列即为一个属性，给每一个属性起一个名称即属性名
-
主码：也称码键，表中的某个属性组，它可以是唯一确定的一个元组
-
域： 是一组具有某个相同数据类型的值的集合
属性的取值范围来自于某个域
-
分量：元组中的一个属性值
-
关系模式：对关系的一个描述
关系名（属性1，属性2，属性3、、、属性n）
-
-
关系必须是规范的，满足一定的规范关系
最基本的规范条件：关系中的每一个分量必须是一个不可分的数据项，不允许表中还有表
2. 关系模型的操纵与完整性约束
-
数据操作是集合操作，操作对象和操作结果都是关系
- 查询
- 插入
- 删除
- 更新
-
存取路径对用户隐蔽，用户只需要指出“找什么”，不必详细说明，怎么找
提高了数据的独立性，提高了用户生产率
-
关系的完整性约束条件
- 实体完整性
- 参照完整性
- 用户定义的完整性
4. 优缺点
- 优点
- 建立在严格的数据概念的基础上
- 概念单一
- 实体和各类联系都用关系来表示
- 对数据的检索结果也是关系
- 关系模型的存取路径对用户透明
- 缺点
- 存储路径对用户透明，查询效率往往不如格式化数据模型
- 为提高性能，必须对用户的查询请求进行优化，增加了开发数据库管理系统的难度
1.4 数据库系统的组成
- 数据库
- 数据库管理系统（及其开发工具）
- 应用程序
- 数据库管理员
1.硬件平台及数据库
数据库系统对硬件资源的要求
- 足够大的内存
- 足够大的磁盘或磁盘阵列等外部设备
- 较高的通道能力，提供数据传送率
2.软件
- 数据库管理系统
- 支持数据库管理系统运行的操作环境
- 与数据库接口的高级语言及其编译系统
3.人员
- 数据库管理员（DBA)
- 系统分析员和数据库设计人员
- 应用程序员
- 最终用户
二. 关系数据库
关系数据库简介
IBM公司 1970 提出关系数据库模型
2.1 关系数据结构
-
单一的数据结构–关系
现实世界的实体以及实体间的各种联系均用关系来表示
2.1.1 域（Domain）和关系
1. 域（Domain）
-
定义：域是一组具有相同数据类型的值的集合
-
笛卡尔积
给定一组域D1,D2,D3…Dn ，允许其中某些域是相同的
D1,D2,D3…Dn 的笛卡尔积为：
D1D2D3…Dn ={（d1,d2,d3…dn)|di属于Di，i=1，2，3，4、、、n}
- 所有域的所有取值的任意组合
- 笛卡尔积可以看作是关系的“域”
-
基数：
-
笛卡尔积的表示方法
- 笛卡尔积可表示为一张二维表
- 表中的每行对应一个元组，表中的每列对应一个域
2. 关系
-
定义：D1D2D3…Dn的子集叫做域D1,D2,D3…Dn 上的关系，表示为
R(D1,D2,D3…Dn )
- R:关系名
- n：关系的目或度（Degree）
-
元组：关系中的每个元素（d1,d2,d3…dn）叫做一个n 元组或者简称元组，通常用t表示
-
属性：
- 关系中不同列可以对应相同的域
- 为了加以区分，必须给每列起一个名字，称为属性（Attribute)
- n目关系必有n个属性
-
码
-
候选码：
若关系中的某一属性值能唯一标识一个元组，则称该属性组为候选码
简单的情况：候选码只包含一个属性
-
全码：
最极端的情况：关系模式中所有属性组是这个关系模式的候选码，称为全码
-
主码：
若一个关系有多个候选码，则选定其中一个为主码
-
主属性：
候选码的诸属性称为主属性
不包含在任何候选码中的属性称为非主属性或非码属性
-
-
单元关系与二元关系
- 当n=1时，称该关系为单元关系或者 一元关系
- 当n=2 时，称该关系为二元关系
-
基本关系的性质
-
列时同质的；
-
不同的列可以出自同一个域；
-
列的顺序无所谓，列的次序可以任意调换；
-
任意两个元组的候选码不能相同；
-
行的顺序无所谓，行的次序可以任意调换；
-
分量必须取原子值。
这是规范条件中最基本的一条，即元素不可再分
-
-
笛卡尔积的某个子集才有实际意义，全集没有实际意义。
2.1.2 关系模式
1. 什么是关系模式
-
关系模式是型
-
关系是值
-
关系模式是对关系的描述
-
元组集合的结构
-
属性构成
-
属性来自的域
-
属性与域之间的映像关系
-
-
完整性约束条件
-
2. 如何定义关系模式
-
关系模式可以形象化的表示为：
R(U,D,DOM,F)
R 关系名 U 组成该关系的属性名集合 D U中属性来自的域 DOM 属性向域的映像集合 F 属性间数据的一来关系的集合 关系模式通常简记为
R(U) 或者 R(A1,A2,A3…An)
- R：关系名
- A1,A2,A3…An：属性名
域名及属性向域的映像通常直接说明为属性的类型、长度
3. 关系模式与关系的关系
-
关系模式：型
- 对关系的描述
- 静态的，稳定的
-
关系：值
- 关系模式在任意时刻的状态或内容
- 动态的，随时间不断变化的
-
关系模式和关系往往笼统的称为关系
通过上下文进行区分
2.1.3 关系数据库
-
关系数据库
在一个给定的应用领域中，所有关系的集合构成了一个关系数据库
-
关系数据库的型与值
- 关系数据库的型：关系数据库模式，是对关系数据库的描述
- 关系数据库的值：关系模式子啊某一时刻对应的关系的集合。通常称为关系数据库
2.2 关系完整性约束
关系的三类完整性约束
- 实体完整性和参照完整性
关系模式必须满足的完整性约束条件称为关系的两个不变性，应该由关系系统自当支持
- 用户定义的完整性
应用领域需要遵循的约束条件，体现了具体领域中的语义约束
2.2.1.实体完整性
- 实体完整性：
- 关系的主属性不能取空值
- 空值就是不知道或者不存在或无意义的值
2.2.2.参照完整性
-
关系间的引用
- 在关系模型中实体及实体间的联系通常都是用关系来描述的，自然存在这关系和关系间的引用
-
外码
- 设F 是基本关系R的一个或一组属性，但不是关系R的码，如果F与基本关系S的主码 Ks相对应，则称F 是R 的外码
- 基本关系R 称为参照关系
- 基本关系S 称为被参照关系或目标关系
-
参照完整性规则
- 若属性（或属性组）F是基本关系R 的外码，它与基本关系 S 的主码 Ks 相对应（基本关系R和S不一定的是不同的关系，也就是说可以是一个关系），则对于 R 中每个元组在 F 上的值必须为：
- 或者取空值（F 的每个属性值均为空值）因为它在参照关系（目标关系）中不一定是主码
- 或则等于 S （被参照关系）中的某个元组的主码值
- 若属性（或属性组）F是基本关系R 的外码，它与基本关系 S 的主码 Ks 相对应（基本关系R和S不一定的是不同的关系，也就是说可以是一个关系），则对于 R 中每个元组在 F 上的值必须为：
2.2.3 用户定义的完整性
- 针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求
- 关系模型应提供定义和检测这类完整性的机制，以便统一的系统的方法来处理他们，而不需由应用程序来承担这一功能
2.3 关系代数
关系代数是一种抽象的查询语言，它对关系的运算来表达查询
关系代数
- 运算对象是关系
- 运算结果亦为关系
- 关系代数的运算符有两类：集合运算符和专门的关系运算符
2.3.1 传统的集合运算
一些记号
| R | 关系 |
|---|---|
| t属于R | t是R的一个元组 |
| t[Ai] | 表示元组t中相应于属性Ai的一个分量 |
-
并
-
R 和 S
- 具有相同的目n（即两个关系都有n个属性）
- 相应的属性取自同一个域
-
结果
- 结果仍为n目关系，由属于 R 或属于 S 的元组组成
- 自动去重
-
-
差
-
R 和 S
- 具有相同的目n
- 相应的属性取自同一个域
-
R-S
- 仍为n 目关系，由属于R但不属于 S 的所有元组组成
-
-
交
- R 和 S
- 具有相同的目n
- 相应的属性取自同一个域
- 结果
- 仍为n 目关系，由即属于R 又属于 S 的元组组成
- R 和 S
-
笛卡尔积
2.3.2 专门的关系运算
1. 选择
- 选择又称限制
- 选择符号的含义
- 选择运算是从关系R中选取是逻辑表达式F 为真的元组，是从行的角度进行的运算
2. 投影
- 从R 中选择出若干属性列组成新的关系
- 投影操作主要从列的角度进行运算
- 投影之后不仅取消了原关系中的某些列，而且还可能取消某些元组（避免重复行）
3. 连接
-
从俩个关系的笛卡尔积中选取属性间满足一定条件的元组
-
两类常用连接
- 等值连接
- 自然连接：是一种特殊的等值连接
- 两关系中进行比较的分量必须是相同的属性
- 在结果中把重复的属性去掉
- 悬浮元组：两个关系在做自然连接时，被舍弃的元组
-
一般的操作连接是从行的角度进行计算
自然连接还需要取消重复列，所有同时从行和列的角度进行运算
-
外连接
-
如果把悬浮元组也保存在结果关系中，而在其他属性上填空值，就叫做外连接
-
左外连接
- 只保留左边关系R中的悬浮元组
-
右外连接
- 只保留右边关系S中的悬浮元组
-
4. 除运算
- 除操作是同时在行和列角度进行计算
2.3.3 综合运算
三. 关系数据库标准语言SQL
3.1 SQL的基本概念
SQL：结构化查询语言，是关系数据库的标准语言
目前没有一个数据库系统可以支持SQL标准的所有概念和特性
SQL 的特点
-
综合统一
- 集数据定义语言（DDL)，数据操纵语言(DML)，数据控制语言(DCL)功能于一体
- 可以独立完成数据库生命周期的全部活动
-
高度非过程化
-
面向集合的操作方式
- 非关系数据模型采用面向记录的操作方式，操作对象是一条记录
- SQL采用集合操作方式
- 操作对象、查找结果可以是元组的集合
- 一次插入、删除、更新操作的对象可以是元组的集合
-
以同一种语言结构提供多种使用方式
- 是独立的语言
- 嵌入式语言
-
语言简洁、易学易用
-
SQL功能极强，完成核心功能只用到9个动词
SQL功能 动词 数据查询 SELECT 数据定义 CREATE, DROP, ALTER 数据操纵 INSERT, UPDATE, DELETE 数据控制 GRANT, REVOKE
-
SQL 支持关系数据库三级模式结构
1. 基本表（模式）
- 本身独立存在的表
- SQL中一个关系就对应一个基本表（模式）
- 一个或者（多个）基本表就对应一个存储文件（内模式）
- 一个表可以带若干索引
2. 存储文件（内模式）
- 逻辑结构组成了关系数据库的内模式
- 物理结构对用户是隐蔽的
3. 视图（外模式）
- 从一个或者几个基本表导出的表
- 数据库中只存放视图的定义而不存放视图对应的数据
- 视图是一个虚表
- 用户可以在视图上在定义视图
实例数据库
Student(Sno,Sname,Ssex,Sage,Sdept)
Course(Cno,Cname,Cpno,Ccredit)
SC(Sno,Cno,Grade)
3.2 数据定义
-
SQL 的数据定义功能：定义各种数据库对象
- 模式定义
- 表定义
- 视图定义
- 索引定义
-
一个数据库里可以建立多个模式
-
一个模式下通常包括多个表、视图和索引等数据库对象
数据字典
- 数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有对象的定义信息以及一些统计信息
- 关系模式、表、视图、索引的定义
- 完整性约束条件
- 各类用户对数据库的操作权限
- 统计信息等
- 关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典表中的信息
1. 模式定义
定义模式
eg.3.1 为用户WANG定义一个学生-课程模式S-T
CREATE SCHEMA "S-T" AUTHORIZATION WANG;
eg.3.2 该语句没有定义模式名，模式名隐含为用户名
CREATE SCHEMA AUTHORIZATION WANG;
-
定义模式实际上定义了一个命名空间（或者说目录）
-
在这个空间上可以定义该模式包含的数据库对象，例如基本表，索引，视图等
-
在CREATE SCHEMA 中可以接受 CREATE TABLE（基本表）,CREATE VIEW（视图）,GRANT（索引） 子句
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>]
eg.3.3 为用户WANG 创建了一个模式TEST ，并在其中定义一个表 TAB1
CREATE SCHEMA TEST AUTHORIZATION WANG
CREATE TABLE TAB1 (COL1 SMALLINT,
COL2 INT,
COL3 CHAR(20),
COL4 NUMERIC(10,3),
COL5 DECIMAL(5,2)
);
删除模式
语法
DROP SCHEMA <模式名> <CASCADE|RESTRICT>
- CASSADE(级联)
- 删除模式的同时把该模式中所有的数据库对象全部删除
- RESTRICT(限制)
- 如果该模式中定义了下属的数据库对象（如表，视图等），则拒绝该删除语句的执行
- 仅当该模式中没有任何下属对象时才能执行
eg.3.4 删除模式ZHANG 同时该模式中定义的表TAB1也被删除
DROP SCHEMA ZHANG CASCADE;
2. 表定义
定义基本表
CREATE TABLE <表名> /*基本表的名称*/
(<列名><数据类型>)[<列级完整性约束条件>] /*组成该表的列*/
[,<列名><数据类型>][<列级完整性约束条件>]]
...
[,<表级完整性约束条件>]);
- 列级完整性约束条件：涉及相应属性列的完整性约束条件
- 表级完整性约束条件：涉及一个或多个属性列的完整性约束条件
- 如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上
【eg.3.5】 建立“学生”表 Student.学号时主码，姓名值取唯一。
CREATE TABLE Student
(
Sno CHAR(9) PAIMARY KEY, /*列级完整性约束条件，Sno为主码*/
Sname CHAR(20) UNIQUE, /*Sname取唯一值*/
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
【eg.3.6】建立一个”课程“表 Course
CREATE TABLE Course
(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY(Cpno) REFERENCES Course(Cno) /*表级完整性，Cpno是外码，被参照表是Course，被参照列的是Cno*/
);
【eg.3.7】建立一个学生选课表 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*/
);
数据类型
- 关系模式中"域"的概念用数据类型来实现
- 定义表的属性时需要指明其数据类型及长度
- 选用那种数据类型
- 取值范围
- 要做那些运算
模式与表
-
每一个基本表需属于某个模式
-
定义基本表所属模式的方式：
-
方法一：在表名中明显给出模式名
CREATE TABLE "S-T".Student(...); /* 模式名为S-T*/ CREATE TABLE "S-T".Course(...); CREATE TABLE "S-T".SC(...); -
方法二：在创建模式的同时创建表
-
方法三：设置所属的模式
-
修改基本表
ALTER TABLE<表名>
[ADD[COLUMN]]<新列名><数据类型>[完整性约束]]
[ADD <表级完整性约束>]
[DROP[COLUMN]<列名>[CASCADE|RESTRICT]]
[DROP CONSTRAINT<完整性约束名>[RESTRICT|CASCADE]]
[ALTER COLUMN<列名><数据类型>];
-
表名是要修改的基本表
-
ADD子句用于添加新列、新的列级完整性约束条件和新的表级完整性约束条件
-
DROP COLUMN 子句用于删除表中的列
- 如果指定了CASCADE短语，则自动删除引用该列的对象
- 如果指定了RESTRICT 短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列
-
DROP CONSTRAINT子句用于删除指定的完整性约束条件
-
ALTER COLUMN 子句用于修改原有的列定义包括修改列名和数控类型
【eg. 3.8】向Student表中增加“入学时间”列，其数据类型为日期型
ALTER TABLE Student ADD S_entrance DATE;
不管基本表中有无数据，新增加的列一列为空值
【eg. 3.9】将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数；
ALTER TABLE Student ALTER COLUMN Sage INT;
【eg. 3.10】添加课程名称必须取唯一值的约束条件
ALTER TABLE Course ADD UNIQUE(Cname);
删除基本表
DROP TABLE<表名>[RESTRICT|CASCADE];
- RESTRICT: 删除表的时候是有限制的
- 欲删除的基本表不能被其他表的约束所引用
- 如果存在依赖该表的对象，则此不能被删除
- CASCADE: 删除该表没有限制
- 在删除基本表的同时，相关的依赖对象一起被删除
【eg. 3.11】删除Student表
DROP TABLE Student CASCADE;
- 基本表定义被删除，数据被删除
- 表上建立的索引，视图，触发器等一般也将会被删除
3. 索引定义
- 建立索引的目的：加快查询速度
- 关系数据库管理系统中常见索引：
- 顺序文件上的索引
- B+树索引
- 散列(hash)索引
- 位图索引
- 特点：
- B+树索引具有动态平衡的优点
- HASH索引具有查找速度快的特点
- 谁可以建立索引
- DBA或者表的属主（即建立表的人）
- 谁维护索引
- 关系DBMS自动完成
- 使用索引
- 关系DBMS自动选择合适的索引作为存取路径，用户不必也不能显示的选择索引
建立索引
-
语句格式
CREATE[UNIQUE][CLUSTER] INDEX<索引名> ON<表名>(<列名>[<次序>]， [,<列名>[<次序>]]...);- 表名：要建立索引的基本表的名字
- 索引：可以建立在该表的一列或多列上，各列名之间用逗号分开
- 次序：指定索引值的排序顺序，升序：ASC，降序：DESC，缺省值：ASC
- UNIQUE：此索引的每一个索引值只对应唯一的数据记录
- CLUSTER：表示要建立的索引是聚簇索引
【eg. 3.13】为学生-课程数据库中Student，Course,SC三个表建立索引。
Student表按照学号升序建唯一索引，
Course表按课程号升序建立唯一索引，
SC表按照学号升序和课程号降序建唯一索引。
CRATE UNIQUE INDEX Stusno ON Student(Sno);
CRATE UNIQUE INDEX Coucno ON Course(Cno);
CRATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);
修改索引
-
语法格式
ALTER INDEX<旧索引> RENAME TO <新索引>；
【eg. 3.14】将SC表中的SCno索引名改为SCSno
ALTER INDEX SCno RENAME TO SCSno；
删除索引
-
语法格式
DROP INDEX <索引名>；删除索引时，系统会从数据字典里删去有关该索引的描述
【eg. 3.15】删除Student 表中的Stusname索引
DROP INDEX Stusname;
3.3 数据查询
3.3.1 单表查询
数据查询
-
语法格式
SELECT[ALL|DISTINCT]<目标列表达式>[,<目标列>]... FROM<表名或者视图名>[,<表名或者视图名>]...|(SELECT 语句) [AS]<别名> [WHERE<条件表达式>] [GROUP BY <列名1>][HAVING<条件表达式>]] [ORDER BY <列名2>[ASC|DESC]];子句 作用 SELECT子句 指定要现实的属性列 FROM子句 指定查询对象 WHERE子句 指定查询条件 GROUP BY 子句 对查询结果按指定列的值分组，该属性列值相等的元组为一组。通常会在每组中作用聚集函数。 HAVING 子句 只有满足指定田条件的组才予以输出 ORDER BY 子句 对查询结果表按指定列值的升序或降序排列
1.选择表中的若干列
- 查询指定列
【eg. 3.16】查询全体学生的学号和姓名
SELECT Sno,Sname
FROM Student;
- 查询全部列
- 选出所有属性列：
- 在SELECT关键词后面列出所有列名；
- 将 目标列表达式 指定为 *
- 选出所有属性列：
【eg. 3.17】查询全体学生的详细记录
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;
或
SELECT *
FROM Student;
- 查询经过计算的值
- “虚”列
- SELECT 子句的 目标列表达式 不仅可以为表中的属性列，也可以是表达式
- “虚”列
【eg. 3.18】查询全体学生的姓名及其出生年份
SELECT Sname,2020-Sage
FROM Student;
【eg. 3.19】查询全体学生的姓名，出生年份和所在的院系，要求小写字母表示系名
SELECT Sname,'Year of Birth:',2020-Sage,LOWER(Sdept)
FORM Student;
【eg. 3.21】使用列别名改变查询结果的列标题
SELECT Sname NAME,
'Year of Birth:' BIRTH,2020-Sage BIRTHDAY,LOWER(Sdept) DEPARTMANT
FROM Student;
2 选择表中的若干元组
(1).
- 取消选择重复的行
如果没有指定DISTINCT关键词，则缺省值为ALL(不去重)
【eg. 3.21】查询选修了课程的学生学号
SELECT Sno
FROM SC;
- 指定DISTINCT 关键词，去掉表中的重复列
SELECT DISTINCT Sno
FROM SC;
(2).查询满足条件的元组
- 比较大小
【eg. 3.22】查询计算机科学系全体学生的名单
SELECT Sname
FROM Student
WHERE Sdept='CS';
【eg. 3.23】查询所有年龄在20岁以下的学生姓名及年龄
SELECT Sname,Sage
FROM Student
WHERE Sage<20;
【eg. 3.24】查询考试有不及格的学生的学号
SELECT DISTINCT Sno
FROM Student
WHERE Grade<60;
-
确定范围
-
谓词：BETWEEN… AND…
NOT BETWEEN… AND…
-
【eg. 3.25】查询年龄在20—23岁（包括20和23）之间的学生姓名、系别、年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;
/*年龄不在20—23岁（包括20和23）之间的学生姓名、系别、年龄*/
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;
-
确定集合
-
谓词：IN<值表>,
NOT IN<值表>
-
【eg. 3.27】查询计算机系（CS）、数学系（MA)和信息系(IS) 学生姓名和性别
SELECT Sname,Sage
FROM Student
WHERE Sdept IN ('CS','MA','IS');
/*查询不是计算机系（CS）、数学系（MA)和信息系(IS) 学生姓名和性别*/
SELECT Sname,Sage
FROM Student
WHERE Sdept NOT IN ('CS','MA','IS');
-
字符匹配
-
谓词：[NOT] LIKE ‘<匹配串>’ [ESCAPE’<换码字符>’]
匹配串可以是一个完整的字符串，也可以含有通配符%和_
-
%(百分号) 代表任意长度（长度也可以为0）的字符串
例如a%b 表示以a 开头，以b 结尾的任意长度的字符串
-
_(下横线) 代表任意单个字符
例如a_b 表示以a 开头，以b 结尾的长度为3 的任意字符串
-
-
-
- 匹配串为固定字符串
【eg. 3.29】查询学号为2018304188 的学生的详细情况
SELECT *
FROM Student
WHERE Sno LIKE '2018314188';
等价于
SELECT *
FROM Student
WHERE Sno='2018314188';
-
- 匹配串为含通配符的字符串
【eg. 3.30】查询所有姓刘学生的姓名、学号和性别；
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE '刘%'；
【eg. 3.31】查询姓“欧阳”且全名为三个汉字的学生的姓名；
SELECT Sname
FROM Student
WHERE Sname LIKE '欧阳__'; /*一个汉字两个字符长度*/
【eg. 3.32】查询名字中第二个字为“阳”字的学生的姓名和学号；
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '__阳%';
【eg. 3.33】查询所有不姓刘的学生姓名、学号和性别；
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE '刘%'；
-
- 使用换码字符将通配符转义成普通字符
【eg. 3.34】查询DB_Design 课程的课程号和学分；
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE 'DB\_Design' ESCAPE'\'; /* ESCAPE'\' 表示\ 之后的字符为普通字符*/
-
- 使用换码字符将通配符转义成普通字符
【eg. 3.35】 查询以“DB_"开头，且倒数第三个字符为”i“ 的课程的详细情况
SELECT *
FROM Course
WHERE Cname LIKE 'DB\_%i__' ESCAPE'\'; /* ESCAPE'\' 表示"\"为转换字符 */
- 涉及空值的查询
谓词：
IS NULL或IS NOT NULL
【eg. 3.36】 某些学生选修课程后没有参加考试，所以有选课记录但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号
SELECT Sno,Cno
FROM SC
WHERE Gade IS NULL;
-
多重条件查询
逻辑运算符：AND 和OR 来连接多个查询条件
- AND 优先级高于OR
- 可以用括号改变优先级
【eg. 3.38】 查询计算机系年龄在20岁以下的学生姓名
SELECT Sname
FROM Student
WHERE Sdept='CS' AND Sage<20;
【eg. 3.27】查询计算机系（CS）、数学系（MA)和信息系(IS) 学生姓名和性别
SELECT Sname,Sage
FROM Student
WHERE Sdept IN ('CS','MA','IS');
/*查询不是计算机系（CS）、数学系（MA)和信息系(IS) 学生姓名和性别*/
SELECT Sname,Sage
FROM Student
WHERE Sdept NOT IN ('CS','MA','IS');
也可以改写为
SELECT Sname,Sage
FROM Student
WHERE Sdept='CS' OR Sdept='MA' OR Sdept='IS';
3. ORDER BY子句
- ORDER BY子句
- 可以按照一个或者多个属性列排序
- 升序：ASC，降序：DESC；缺省值为升序
- 对于空值，排序时显示的次序由具体系统实现来决定
【eg. 3.39】查询选修了三门课的学生的学号以及成绩，查询结果按照分数降序排列
SELECT Sno,Grade
FROM SC
WHERE Cno='3'
ORDER BY Grade DESC;
【eg. 3.40】查询全体学生情况，查询结果按照所在院系的系号排列，同一系的学生按照年龄降序排列
SELECT *
FROM Student
ORDER BY Sdept (ASC),Sage DESC;
4. 聚集函数
-
聚集函数：
-
统计元组的个数
COUNT(*) -
统计一列中值的个数
COUNT([DISTINCT|ALL]<列名>) -
计算一列值的总和（此列必须为数值型）
SUM([DISTINCT|ALL<列名>]) -
计算一列值的平均值（此列必须为数值型）
AVG([DICTINCT|ALL<列名>]) -
求一列中最大值和最小值
MAX([DISTINCT|ALL<列名>]) MIN([DISTINCT|ALL<列名>])
-
【eg. 3.41】查询学生总人数
SELECT COUNT(*)
FROM Student；
【eg. 3.42】查询选修了课程的总人数
SELECT COUNT(DISTINCT Sno)
FROM SC;
【eg. 3.43】计算1号课程的学生平均成绩
SELECT AVG(Grade)
FROM SC
WHERE Cno='1';
【eg. 3.44】查询选修了1号课程的学生的最高分数
SELECT MAX(Grade)
FROM SC
WHERE Cno='1';
【eg. 3.45】查询学生2018304188选修课程的总学分数
SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='2018304188' AND SC.Cno=Course.Cno;
5. GROUP BY子句
-
GROUP BY 子句
细化聚集函数的作用对象
- 如果未对查询结果分组，聚集函数将作用于整个查询结果
- 对查询结果分组后，聚集函数将分别作用于每个组
- 按指定的一列或多列分组，值相等的为一组
【eg. 3.46】求每个课程号及相应的选课人数
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno; /* 以Cno为分组属性 */
【eg. 3.47】查询选修了三门以上的课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;
【eg. 3.48】查询平均成绩大于等于90分的学生学号和平均成绩
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90;
WHERE 子句不能用聚集函数作为条件表达式。
- HAVING短句和WHERE 子句的区别
- 作用对象不同
- WHERE子句作用于基表或视图，从中选择满足条件的元组
- HAVING短语作用于组，从中选择满足条件的组
6.综合练习
【练习一】列出计算机系姓刘的同学的信息，按照学号大小排序
SELECT *
FROM Student
WHERE Sdept='CS' AND Sname LIKE '刘%'
ORDER BY Sno;
【练习二】按系区分男女统计各系学生的人数、并按照人数降序排列
SELECT Sdept,Ssex,COUNT(Sno)
FROM Student
GRUOP BY Sdept,Ssex
ORDER BY COUNT(Sno)DESC;
3.3.2 连接查询
-
不像关系代数中”连接“是用一个特殊符号来表达的，在SQL中”连接“是用”连接条件“来表达的
-
连接条件或连接谓词：用来连接两个表的条件
一般格式
- [<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
-
连接字段：连接谓词中的列名称
- 连接条件中的各连接字段类型必须是可比较的，但名字不必相同
1.等值与非等值查询
- 等值连接：连接运算符为”=”
【eg. 3.49】查询每个学生及其选修课程的情况
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;
- 自然连接
- 采用在SELECT中去掉重复字段的方式实施
【eg. 3.50】对上例用自然连接完成
SELECT Student.Sno,Sname,Ssex,Sage.Sdept,Cno,Grade/*Student.Sno表明只保留了Student中的Sno */
FROM Student,SC
WHERE Student.Sno=SC.Sno;
连接操作的执行过程
(1). 嵌套循环法（NESTED-LOOP)
- 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中第一个元组与该元组拼接起来，形成结果表中一个元组；
- 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2 ，逐一查找满足连接条件的元组，找到后就将表1 中的第二个元组与该元组拼接起来，形成结果表中一个元组；
- 重复上述操作，直到表1中全部元组都处理完毕。
(2).排序合并法（SORT-MERGE)
(3).索引连接法（INDEX-JOIN)
【eg. 3.51】查询选修了2号课程且成绩在90分以上的所有学生的学号和姓名
SELECT Student.Sno,Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND SC.Cno='2' AND SC.Grade>90;
/* Student.Sno=SC.Sno 连接谓词“=” 选择谓词 AND*/
2. 自身连接
- 自身连接：一个表与其自己进行连接，是一种特殊的连接
- 需要给表起别名以示区别
- 由于所有属性名都是同名属性，因此必须使用别名前缀
【eg. 3.52】查询每一门课的直接先修课的名称
SELECT FIRST.Cname,SECOND.Cname
FROM Course FIRST,Course SECOND
WHERE FIRST.Cpno=SECOND.Cno;
3. 外连接
- 外连接与普通连接的区别
- 普通连接操作只输出满足连接条件的元组
- 外连接操作以指定表为连接主体，将主题表中不满足连接条件的元组一并输出
- 左外连接
- 列出左边关系中所有元组
- 右外连接
- 列出右边关系中所有元组
【eg. 3.53】改写【eg. 3.49】查询每个学生及其选修课程的情况
SELECT Student.*,SC.*
FROM Student,SC
WHERE Student.Sno=SC.Sno;
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno);
或
WHERE Student.Sno(+)=SC.Sno;
4. 多表连接
- 两个以上的表进行连接
【eg. 3.53】查询每个学生的学号、姓名、选修的课程名以及成绩
SELECT Student.Sno,Sname,Cno,Grade
FROM Student,SC,Course /*多表连接*/
WHERE Student.Sno=SC.Sno
AND SC.Cno=Course.Cno;
3.3.3 嵌套查询
-
嵌套查询概述
- 一个SELECT-FROM-WHERE 语句称为一个查询块
- 将一个查询块嵌套在另外一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
SELECT Sname /*外层查询/父查询*/ FROM Student WHERE Sno IN (SELECT Sno /*内层查询/子查询*/ FROM SC WHERE Cno='2' )；- 上层查询快称为外层查询或父查询
- 下层查询称为内层查询或子查询
- SQL语句允许多层嵌套查询
- 子查询的限制
- 不能使用ORDER BY 子句
-
嵌套查询求解方法
- 不相关子查询：子查询的查询条件不依赖于父查询
- 由里向外逐层处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。
- 相关子查询：子查询的查询条件依赖于父查询
- 首先取外层查询表中的第一个元素，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表
- 然后再取外层表的下一个元组
- 重复这一过程，直至外层表全部检查完为止
- 不相关子查询：子查询的查询条件不依赖于父查询
1.带有IN谓词的子查询
【eg. 3.55】查询与“刘晨”在同一个系学习的学生，此查询要求可以分布完成
/*1.确定“刘晨”所在系名*/
SELECT Sdept
FROM Studnt
WHERE Sname='刘晨'；
/*结果为：CS*/
/*2. 查找所有在CS系学习的学生*/
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept='CS';
/*将第一步查询嵌入第二步查询的条件中*/
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept IN
(SELECT Sdept
FROM Student
WHERE Sname='刘晨')；
此查询为不相关子查询
用自身连接完成【eg. 3.55】查询要求
SELECT S1.Sno,S1.Sname,S1.Sdept
FROM Student S1,Student S2
WHERE S1.Sdept=S2.Sdept AND S2.Sname='刘晨'；
【eg. 3.56】查询选修了课程名为“信息系统”的学生姓名和学号
SELECT Sno,Sname /*3. 最后再Student关系中取出Sno和Snmae*/
FROM Student
WHERE Sno IN
(SELECT Sno /*2. 然后再SC关系里找到选修了三号课程的学生学号*/
FROM SC
WHERE Cno IN
(SELECT Cno /*1. 首先在Course关系中找出“信息系统”的课程号，为三号*/
FROM Course
WHERE Cname='信息系统'
)
);
用连接查询来实现【eg. 3.56】
SELET Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND
SC.Cno=Course.Cno AND
Course.Cname='信息系统'；
2. 带有比较运算符的子查询
- 当能确切知道内层查返回单值时。可以用比较运算符（>,<,=,>=,<=,!=,或<>）。
在【eg. 3.55】查询与“刘晨”在同一个系学习的学生，此查询要求可以分布完成
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept = /*由于一个学生只能在一个系学习，用=代替*/
(SELECT Sdept
FROM Student
WHERE Sname='李晨'
)；
【eg. 3.57】找出每个学生超过他选修课平均成绩的课程号
SELECT Cno
FROM SC x
WHERE Grade>=(SELECT AVG(Grade)
FROM SC y
WHERE y.Sno=x.Sno
);
此查询为相关子查询
-
可能执行的过程
- 从外查询中选出SC的一个元组x，将元组x的Sno值(2018304188)传送给内查询
SELECT AVG(Grade) FROM SC y WHERE y.Sno='2018304188';- 执行内查询，得到近似值88，用该值代替内层查询，得到外层查询
SELECT Sno,Cno FROM SC x WHERE Grade>=(SELECT AVG(Grade) FROM SC y WHERE y.Sno='2018304188');执行该查询，得到结果
3. 带有ANY(SOME) 或 ALL 谓词的子查询
- 引入ANY和ALL谓词，其对象为某个查询结果，表示其中任意一个值或全部值
- 引入EXIST谓词，其对象也是某个查询结果，但表示这个查询结果是否为空，返回空值
使用ANY 或 ALL 谓词时必须同时使用比较运算
语义为：
| >ANY | 大于子查询结果中的某个值 |
| >ALL | 大于子查询中的所有值 |
| <ANY | 小于子查询结果中的某个值 |
| <ALL | 小于子查询结果中的所有值 |
| >=ANY | 大于等于子查询结果中的某个值 |
| >=ALL | 大于等于子查询结果中的所有值 |
【eg. 3.58】查询非计算机科学系中比计算机科学系 任意一个 学生连接小的学生姓名和年龄
SELECT Sname,Sage
FROM Student
WHERE Sage<ANY (SELECT Sage
FROM Student
WHERE Sdept='CS'
)
AND Sdept<>'CS'; /*父查询块中的条件 Sdept!='CS'*/
- 用聚集函数实现【eg. 3.58】
SELECT Sname
FROM Student
WHERE Sage<
(SELECT MAX(Sage)
FROM Student
WHERE Sdept='CS'
)
AND Sdept<>'CS';
【eg. 3.59】查询非计算机科学系中比计算机科学系 所有 学生年龄都小的学生姓名及年龄
方法一：用ALL谓词
SELECT Sname,Sage
FROM Student
WHERE Sage<ALL
(SELECT Sage
FROM Student
WHERE Sdept='CS')
AND Sdept<>'CS';
方法二：用聚集函数
SELECT Sname,Sage
FROM Student
WHERE Sage<
(SELECT MIN(Sage)
FROM Student
WHERE Sdept='CS'
)
AND Sdept<>'CS';
4. 带有EXISTS 谓词的子查询
- EXISTS谓词
- 带有EXISTS 谓词的子查询不返回任何数据，只产生逻辑真值“true” 或逻辑假值 “false”
- 若内层查询结果非空，则外层的WHERE子句返回真值
- 若内层查询结果为空，则外层的WHERE子句返回假值
- 由EXISTS 引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只能返回真值或者假值，给出列名无意义
- 带有EXISTS 谓词的子查询不返回任何数据，只产生逻辑真值“true” 或逻辑假值 “false”
【eg. 3.60】查询所有选修了1号课程的学生姓名
思路分析：
-
-
- 本查询涉及Student和SC关系
- 在Student中依次取每个元组的Sno值，用此值取检查SC表
- 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=‘1’，则取此Student.Sname 送入结果值
-
SELECT Sname
FROM Student
WHERE EXISTS
(SELECT*
FROM SC
WHERE Sno=Student.Sno AND Cno='1'
);
【eg. 3.61】查询没有选修课程1号的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS
(SELECT*
FROM SC
WHERE Sno=Student.Sno AND Cno='1');
- 用EXISTS代替其他谓词
- 所有带IN 谓词，比较运算符，ANY和ALL谓词的子查询都能用带EXISTS 谓词的子查询等价替换
- 用EXISTS/NOT EXISTS 实现全称量词
- 用EXISTS实现逻辑蕴涵
- 可以把带有全称量词的谓词转换成等价的带有存在量词的谓词
【eg. 3.55】查询与“刘晨”在同一个系学习的学生，此查询要求可以分布完成
可以用带EXISTS谓词的相关子查询替代
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE EXISTS
(SELECT*
FROM Student S2
WHERE S2.Sdept=S1.Sdept AND S2.Sname='刘晨')；
【eg. 3.62】查询选修了全部课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS /*返回真值*/
(SELECT *
FROM Course
WHERE NOT EXISTS /*返回真值*/
(SELECT *
FROM SC
WHERE Sno=Student.Sno
AND Cno=Course.Cno)
);
- 用EXISTS实现逻辑蕴涵
【eg. 3.63】查询了至少选修了学生2018304188选修的全部课程的学生号码
SELECT DISTINCT SCX.Sno
FROM SC SCX
WHERE NOT EXISTS
(SELECT *
FROM SC SCY
WHERE SCY.Sno='2018304188' AND
NOT EXISTS /*选修了学生2018304188选修的全部课程*/
(SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND
SCZ.Cno=SCY.Cno)
);
3.3.4 集合查询
- 集合操作的种类
- 并操作UNION
- 交操作INTERSECT
- 差操作EXCEPT
- 参加集合操作的各查询结果的列数必须相同，对应的数据类型也必须相同
【eg. 3.64】查询计算机科学系的学生及年龄不大于19岁的学生
SELECT *
FROM Student
WHERE Sdept='CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;
- UNION:将多个查询结果合并起来，系统自动去除重复元组
- UNION ALL:将多个查询结果合并起来，保留重复元组
【eg. 3.65】查询选修了课程1或者课程2 的学生
SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';
【eg. 3.66】查询计算机系的学生与年龄不大于19岁的学生的交集
SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WEERE Sage<=19;
/*等价于*/
SELECT *
FROM Student
WHERE Sdept='CS' AND Sage<=19;
【eg. 3.67】查询即选修了课程1，又选修了课程2 的学生
SELECT Sno
FROM SC
WHERE Cno='1'
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='2';
/*等价于*/
SELECT Sno
FROM SC
WHERE Cno='1' AND Sno IN
(SELECT Sno
FROM SC
WHERE Cno='2');
【eg. 3.68】查询计算机系的学生与年龄不大于19岁的差集
SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage<=19;
/*实际上是查询计算机系中年龄大于19岁的学生，故等价于*/
SELECT *
FROM Student
WHERE Sdept='CS' AND Sage>19;
3.4 数据更新
3.4.1 插入数据
- 两种插入数据方式
- 插入元组
- 插入子查询结果
- 可以一次插入多个元组
1. 插入元组
-
语句格式
INSERT INTO <表名>[(<属性列1>)[,<属性列2>....)]] VALUES(<常量1>[,<常量2>]...); -
功能
- 将新元组插入指定表中
-
INTO子句
- 指定要插入数据的表名及属性列
- 属性列的顺序可与表定义中的顺序不一致
- 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
- 没有指定部分属性列：插入元组在其余属性列上取空值
-
VALUES子句
提供的值必须与INTO子句匹配
- 值的个数
- 值的类型
【eg. 3.69】将一个新学生元组（学号：2018304188；姓名：王；性别：男；所在系：IS；年龄：18）插入到Student表中
INSERT
INTO Student(Sno,Sname,Ssex,Sdept,Sage)
VALUES('2018304188','王','男',IS','18');
【eg. 3.71】插入一条选课记录(‘2018304188’,‘1’).
INSERT
INTO SC(Sno,Cno)
VALUES('2018304188','1');
关系DBMS 将在新插入记录的Grade列上自动赋空值
或者
INSERT
INTO SC
VALUES('201215128','1',NULL);
【eg. 3.70】将学生张成民的信息插入到Student表中
INSERT
INTO Student
VALUES('201215126','张成民','男','18','CS');
2. 插入子查询结果
-
语句格式
INSERT INTO<表名>[(<属性列1>)[,<属性列2>...]] 子查询; -
INTO子句
-
子查询
- SELECT 子句目标列必须与INTO子句匹配
- 值的个数
- 值的类型
- SELECT 子句目标列必须与INTO子句匹配
【eg. 3.72】对每个系，求学生的平均年龄，并把结果存入数据库
第一步：建表
CREATE TABLE Dept_age /*表名*/
(Sdept CHAR(15) /*系名*/
AVG_age SMALLINT); /*学生平均年龄*/
第二步：插入数据
INSERT
INTO Dept_age(Sdept,AVG_age)
SELECT Sdept,AVG_age
FROM Student
GROUP BY Sdept;
3.4.2 修改数据
-
语句格式
UPDATE<表名> SET<列名>=<表达式>[,<列名>=<表达式>]... [WHERE<条件>]; -
功能
- 修改指定表中满足WHERE子句条件的元组
- SET子句给出表达式的值用于取代相应的属性列
- 如果省略WHERE子句，表示要修改表中的所有元组
-
三种修改方式
- 修改某一元组的值
- 修改多个元组的值
- 带子查询的修改语句
1. 修改某一元组的值
【eg. 3.73】将学生201215121的年龄改为22岁
UPDATE Student
SET Sage=22
WHERE Sno='201215121';
2. 修改多个元组的值
【eg. 3.74】把所有学生的年龄都增加1
UPDATE Student
SET Sage=Sage+1;
3. 带子查询的修改语句
【eg. 3.75】将计算机系全体学生的成绩置为0
UPDATE SC
SET Grade=0
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='CS'
);
3.4.3 删除数据
-
语句格式
DELETE FROM <表名> [WHERE<条件>]; -
功能
- 删除指定表中满足WHERE 子句条件的元组
-
WHERE子句
- 指定要删除的元组
- 无该子句将会删除表中的全部元组
-
三种删除方式
- 删除某一元组的值
- 删除多个元组的值
- 带子查询的删除语句
1. 删除某一元组的值
【eg. 3.76】删除学号为201215128的学生记录
DELETE FROM Student
WHERE Sno='201215128';
2. 删除多个元组的值
【eg. 3.77】删除所有学生选课记录
DELETE FROM SC;
3. 带子查询的删除语句
【eg. 3.78】删除计算机系所有学生的选课记录
DELETE
FROM sc
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sdept='cs');
3.5 空值的处理
- 空值就是“不知道”或“不存在”或“无意义”的值
- 一般有以下几种情况
- 该属性应该有一个值，但目前不知道它的具体值
- 该属性不应该有值
- 由于某种原因不便于填写
1. 空值的产生
-
空值是一个很特殊的值，含有不确定性。对关系运算带来特殊问题，需要做特殊的处理
-
空值产生有其实际需要
例如学生在选课中，用空值来表示没选课
2. 空值的判断
- 判断一个属性的值是否为空值，用IS NULL 或 IS NTI NULL 来表示
【eg. 3.81】找出漏填了性别或者年龄信息的记录
SELECT *
FROM Student
WHERE Ssex IS NULL OR Sage IS NULL;
3. 空值的约束条件
- 属性定义（或者域定义中）
- 有NOT NULL约束条件的不能取空值
- 加了UNIQUE 限制的不能取空值
- 码属性不能取空值
- 空值和另外一个值（包括另外一个空值）的运算结果为空值
- 空值与另外一个值的比较运算结果为UNKNOWN
- 有UNKNOWN 后，传统二值（TRUE,FALSE)运算逻辑就扩展到了三值运算
【eg. 3.82】找出选修一号课程的不及格的学生
SELECT Sno
FROM SC
WHERE Grade<60 AND Cno='1';
查询结果不包括缺考的学生，因为他们的Grade值为NULL
【eg. 3.83】选出课程以号不及格的学生以及缺考的考生
SELECT Sno
FROM SC
WHERE Cno='1' AND (Grade<60 OR Grade IS NULL);
4. 空值的算数运算、比较运算和逻辑运算
3.6 视图
- 视图的特点
- 虚表：是从一个表或者几个基本表（或视图）导出的表；
- 只存放视图的定义，不存放视图对应的数据；
- 基表中的数据发生变化，从视图中查询的数据也随之改变；
3.6.1 定义视图
1. 定义视图
-
语句格式
CREATE VIEW <视图名>[(<列名>[,<列名>]...)] AS <子查询> [ WITH CHECK OPTION]; -
WITH CHECK OPTION
- 对视图进行UPDATE,INSERT,DELETE操作时要保证更新、插入、删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
-
子查询可以是任何SELECT 语句，是否含有ORDER BY子句和DISTINCT 短语，则决定具体系统的实现
-
组成视图的属性列名：全部省略或全部指定
- 全部省略：由子查询中SELECT 目标列中的诸字段组成
- 明确指定视图中的所有列名
- 某个目标列时聚集函数或列表达式
- 多表连接时选出几个同列名作为视图的字段
- 需要在视图中为某个列启用新的名字
-
关系DBMS执行CREATE VIEW 语句时，只是把视图定义存入数据字典，并不执行其中的SELECT语句
-
在对视图查询时，按视图的定义从基本表中将数据查出
【eg. 3.84】建立信息系学生的视图
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';
【eg. 3.84】建立信息系学生的视图，并要求进行修改和插入操作时任保证该视图只有信息系的学生
CREATE VIEW IS_Studnt
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS'
WITH CHECK OPTION;
-
定义IS_Student 视图时加上WITH CHECK OPTION 子句，对该视图进行插入、修改、删除操作时，RDBMS会自动加上Sdept='IS’的条件
-
若一个视图是从单个基本表中导出的，并且只是去掉了基本表的某些行和某些列，但保留了主码，我们称这类视图为行列子集视图
- IS_Student视图就是一个行列子集视图
-
基于多个表的视图
【eg. 3.86】建立信息系选修了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';
- 基于视图的视图
【eg. 3.87】建立信息系选修了1号课程且成绩在90分以上的学生的视图
CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM IS_S1
WHERE Grade>=90;
- 带表达式的视图
【eg. 3.88】定义一个反映学生出生年份的视图
CREATE VIEW BT_S(Sno,Sname,Sbirth)
AS
SELECT Sno,Sname,2020-Sage
FROM Student;
- 分组视图
【eg. 3.89】将学生的学号及平均成绩定义为一个视图
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
【eg. 3.90】将Student表中所有女生记录定义为一个视图
CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
AS
SELECT * /*没有不指定属性列*/
FROM Student
WHERE Ssex='女'；
缺点：修改基本表Student的机构后，Student表与F_Student视图的映像关系被破坏，导致视图不能正确工作
2. 删除视图
-
语句的格式
DROP VIEW <视图名>[CASCADE]- 该语句从数据字典中删除指定的视图定义
- 如果该视图上还导出了其他视图，使用CASCADE 级联删除语句，就是把该视图和由他导出的所有视图一起删除
- 删除基表时，由该基表导出的所有视图定义都必须显式的使用DROP VIEW 语句删除
【eg. 3.91】删除视图BT_1和IS_S1
DROP VIEW BT_S; /*成功执行*/
DROP VIEW IS_S1; /*拒绝执行*/
要删除IS_S1 需使用级联删除：
DROP VIEW IS_S1 CASCADE;
3.6.2 查询视图
- 用户角度：查询视图与查询基本表相同
- 关系数据库管理系统实现视图查询的方法
- 视图消解法
- 进行有效检查
- 转换成等价的对基本表的查询
- 执行修正后的查询
- 视图消解法
【eg. 3.92】在信息系学生的视图中找出年龄小于20岁的学生
SELECT Sno,Sage
FROM Student
WHERE Sage<20;
视图消极转换后的查询语句为
SELECT Sno,Sage
FROM Student
WHERE Sdept='IS' AND Sage<20;
【eg. 3.93】查询选修了课程1号的信息系学生
SELECT IS_Student.Sno,Sname
FROM IS_Student,SC
WHERE IS_Student.Sno=SC.Sno AND SC.Cno='1';
【eg. 3.94】在S_G 视图中查询平均成绩在90分以上的学生学号和平均成绩
SELECT *
FROM S_G
GROUP BY Sno
HAVING Gavg>=90;
/*S_G视图的定义如下*/
CREATE VIEW S_G(Sno,Gavg)
AS
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
也可以用SQL语句完成
SELECT *
FROM (SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno)
AS
S_G(Sno,Gavg)
WHERE Gavg>=90;
3.6.3 更新视图
【eg. 3.95】将信息系学生视图IS_Student中学号为2018304188的学生姓名改为小王
UPDATE IS_Student
SET Sname='小王'
WHERE Sno='2018304188';
转换后的语句
UPDATE Student
SET Sname='小王'
WHERE Sno='2018304188' AND Sdept='IS';
【eg. 3.96】向信息系学生视图IS_Student 中插入一个新的学生记录，其中学号为“2018304188”，姓名为小郑，年龄为20岁
INSERT
INTO IS_Stdent(Sno,Sname,Sage)
VALUES('2018304188','小郑','20');
转换为对基本表的更新
INSERT
INTO Student(Sno,Sname,Sage,Sdept)
VALUES('2018304188','小郑','20','IS');
【eg. 3.97】删除信息系学生视图IS_Student中学号为2018304188的记录
DELETE
FROM IS_Student
WHERE Sno='2018304188'
转换为对基本表的更新
DELETE
FROM Student
WHERE Sno='2018304188' AND Sdept='IS';
-
更新视图的限制：有一些视图是不可更新的，因为对这些视图的更新不能唯一的有意义的转换称对相应基本表的更新
-
允许对行列子集视图（单表上的视图）进行更新
-
对其他类型的视图的更新不同系统由不同限制
-
DB2对视图的更新的限制
- 若视图是由两个以上基本表导出的，则此视图不允许更新
- 若视图的字段来自字段表达式或常数，则不允许执行INSERT 和UPDATE 操作，但允许DELETE 操作
- 若视图是字段来自集函数，则此视图不允许更新
- 若视图定义中含有GROUP BY子句 或DISTINCT短语
- 若视图定义中有嵌套查询
3.6.4 视图的作用
- 视图能简化用户的操作
- 视图使用户能以多个角度看待同一数据
- 视图对重构数据库提供了一定的逻辑独立性
- 视图能对机密数据提供安全保护
- 适当的利用视图能更清晰的表达查询
四. 数据库安全性
- 问题的提出
- 数据库的一大特点就是数据可以共享
- 数据共享必然带来数据库的安全性问题
- 数据库中的数控共享不可能是无条件的共享
4.1 数据库安全性概述
4.1.1 数据库的不安全因素
- 非授权用户对数据库的恶意存取和破坏
- 数据库中重要或敏感的数据被泄露
- 安全环境的脆弱性
4.1.2 安全标准简介
-
TCSES标准
-
TCSEC/TDI标准的基本内容
从四个方面来描述安全界别划分的指标
- 安全策略
- 责任
- 保证
- 文档
-
-
TCSES标准
- D级
- 将一切不符合更高标准的系统归于D组
- 典型例子：DOS是安全标准为D的操作系统
- C1级
- 非常初级的自主安全保护
- 能够实现对用户和数据的分离，进行自主存取控制（DAC），保护或限制用户权限的传播
- 现有的商业系统稍微改进即可满足
- C2级
- 是安全产品的最低档次
- 提供受控的存取保护，将C1级的DAC进一步细化，以个人身份注册服务，并实施审计和资源隔离
- 达到C2级的产品在其名称上往往不突出“安全”（Security)这一特色
- 典型例子
- Windows 2000
- Oracle 11g
- B1级
- 标记安全保护
- 对系统的数据加以标记，对标记的主体和客体实施强制存取服务（MAC）、审计等安全机制
- B1级典型例子
- 操作系统：惠普公司的HP-UX BLX release9.09+
- 数据库
- B2级
- 结构化保护
- 建立形式化 的安全策略模型并对系统内的所有主体和客体实施DAC和MAC
- B3级
- 安全域
- 该级的TCB必须满足访问监控器的要求，审计跟踪能力更强，并提供系统恢复过程
- A1级
- 验证设计
- D级
-
CC标准
- 提出国际公认的表述信息技术安全性的结构
- 把信息产品的安全要求分为
- 安全功能要求
- 安全保证要求
4.2 数据安全性控制
- 计算机系统中，安全措施是一级一级层层设置
- 数据库安全性控制的常用方法
- 用户身份鉴别
- 存取控制
- 视图
- 审计
- 数据加密
4.2.1 用户身份鉴别
- 用户身份鉴别
- 系统提供的最外层安全保护措施
- 用户标识：由用户名和用户标识号组成
- 鉴别方法
- 静态口令鉴别
- 动态口令鉴别
- 智能卡鉴别
- 生物 特征鉴别
4.2.2 存取控制
-
存取控制机制组成
- 定义用户权限，并将用户权限登记到数据字典中
- 合法权限检查
-
用户权限的定义和合法权检查机制一起组成了DBMS 的存取控制子系统
4.2.3 自主存取控制方法
- 自主存取控制机制（DAC）
- 用户对不同的数据对象有不同的存取权限
- 不同的用户对同一对象也有不同的权限
- 用户还可以将其拥有的存取权限转授给其他用户
- 通过 SQL 的GRANT 语句 和RVOKE 语句实现
4.2.4 授权：授予与回收
1. 权限授予：GRANT
-
GRANT 语句的一般格式
GRANT<权限>[,<权限>]... ON<对象类型><对象名>[,<对象类型><对象名>]... TO<用户>[,<用户>]... [WITH GRANT OPTION]； /*指定该子句：可以在授权权限 没有指定：不能授权传播*/语义：将对指定操作对象的指定操作权限授予指定的用户
不允许循环授权
-
发出GRANT
- DBA
- 数据库对象创建者（即属主Owner）
- 拥有该权限的用户
【eg.4.1】把查询Student 表权限授予用户U1
GRANT SELECT
ON TABLE Student
TO U1;
将一种权限授予一种用户
【eg.4.2】把对Student和Course表的全部授权授予用户U2和U3
GRANT ALL PRIVILEGES /*全部权限*/
ON TABLE Student,Course
TO U2,U3;
一次向多个用户传播多种同类对象的权限
【eg.4.3】把对SC表的查询权限授予所有用户
GRANT SELECT
ON SC
TO PUBLIC;
【eg.4.4】把查询Student表和修改学生学号的权限授予给用户U4
GRANT UPDATE(Sno),SELECT
ON Student
TO U4;
对属性列的授权必须明确指明相应属性列名
一次完成了对基本表和属性列这些不同对象的授权
【eg.4.5】把对表SC的INSERT 权限授予U5用户，并允许它将此权限授予其他用户
GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;
执行后，U5不仅拥有了对表SC的INSERT 权限，还可以传播此途径
【eg.4.6】
GRANT INSERT
ON TABLE SC
TO U6
WITH GRANT OPTION;
2. 权限回收：REVOKE
-
授权的权限可以由数据库管理员或其他授权者用REVOKE语句收回
-
语法格式
REVIKE<权限>[,<权限>]... ON<对象类型><对象名>[,<对象类型><对象名>]... FROM<用户>[,<用户>]... [CASCADE|RESTRICT]；- CASCADE:级联回收
- RESTRICT:受限回收
- 不允许循环授权的意义
【eg.4.8】把用户U4修改学生的权限收回来
REVOKE UPDATE(Sno),SELECT ON TABLE Student FROM U4;【eg.4.9】收回所有用户对表SC的查询权限
REVOKE SELECT ON TABLE SC FROM PUBLIC;【eg.4.10】把用户U5对SC表的INSERT权限收回
REVOKE INSERT ON TABLE SC FROM U5 CASCADE;
3. 创建数据库模式的权限
-
CREATE USER 语句格式
CREATE USER <usename> [WITH][DBA|RESOURCE|CONNECT]
4.2.5 数据库角色
- 数据库角色：被命名的一组与数据库操作相关的权限
- 角色是权限的集合
- 可以为一组具有相同权限的用户创建一个角色
- 简化授权的过程
1. 角色的创建
CREATE ROLE<角色名>
2. 给角色授权
GRANT <权限>[,<权限>]...
ON<对象类型>对象名
TO <角色>[,<角色>]...
3. 将一个角色授权其他的角色或用户
GRANT <角色1>[,<角色2>]...
TO <角色3>[,<用户1>]...
[WITH ADMIN OPTION]
指定了WITH ADMIN OPTION ，则获得权限的角色或用户还可以把这种权限授权给其他角色
授予者是角色的创建者或拥有在这个角色上的ADMIN OPTION
4. 角色权限的收回
REVOKE<权限>[,<权限>]...
ON<对象类型><对象名>
FROM <角色>[,<角色>]...
- 对象可以回收角色的权限
- REVOKE执行者是
- 角色的创建者，
- 拥有这个（些）角色上的ADMIN OPTION
【eg.4.11】通过角色来实现权限管理
步骤如下
-
首先创建一个角色R1
CREATE ROLE R1; -
然后使用GRANT语句，使角色R1拥有Student表的SELECT UPDATE INSERT权限
GRANT SELECT ,UPDATE,INSERT ON TABLE Student TO R1; -
然后将这个角色授予王平，张明，赵林，使他们具有角色R1包含的全部权限
GRANT R1 TO 王平，张明，赵林; -
可以一次性通过R1来灰沙欧王平的这三个权限
REVOKE R1 FROM 王平；
【eg.4.12】增加角色的权限
GRANT DELETE
ON TABLE Student
TO R1;
使角色R1在原有的基础上增加了Student表的DELETE权限
【eg.4.13】减少角色的权限
REVOKE SELECT
ON TABLE Student
FROM R1;
使R1减少了SELECT权限
4.2.6 强制存取控制方法
-
自主存取控制缺点
- 可能出现数据的“无意泄露”
-
强制存取控制（MAC)
- 保证更高程度的安全性
- 用户不能直接感知或进行控制
- 适用于对数据有严格而固定密集分类的部门
- 军事部门
- 政府部门
-
在强制存取控制中，DBMS所管理的全体实体被分为主体和客体两大类
-
主体使系统中的活动实体（用户)
- DBMS所管理的实际用户
- 代表用户的各进程
-
客体是系统中的被动受体，受主体操纵(用户访问的对象)
- 文件、基本表、索引、视图
-
敏感度标记（Label）
- 对于主体和客体DBMS为他们每个实例（值）指派一个敏感度标记（Label）
- 敏感度标记分为若干级别
- 绝密（TS)
- 机密（s）
- 可信©
- 公开（P）
- TS>=S>=C>=P
- 主体的敏感度标记称为许可证级别
- 客体的敏感度标记称为密级
-
强制存取控制规则
- 仅当主体的许可证级别大于或等于客体的密级时，该主体才能读取相应的客体
- 仅当主体的许可证级别小于或等于客体的密级时，该主体才能写相应的客体
4.3 视图机制
- 把要保密的数据对无权存取这些数据的用户隐藏起来，对数据提供一定程度的安全保护
【eg.4.14】授权王平老师能查询计算机系学生的情况，授权系主任张明能对计算机系学生信息进行所有操作
-
先建立计算机系学生的视图CS_Student
CREATE VIEW CS_Student AS SELECT* FROM Student WHERE Sdept='CS'; -
在视图上进一步定义存取权限
GRNANT SELECT ON CS_Student TO 王平； GRANT ALL PRIVILGES ON SC_Student ON 张明；
4.4 审计
-
数据库安全性控制措施
- 预防性措施
- 用户身份鉴别
- 用户存取控制
- 强制存取控制
- 监控措施
- 审计
- 预防性措施
-
什么是审计
-
启用一个专门的审计日志
将用户对数据库的所有操作记录在上面
-
审计员利用审计日志
监控数据库中的各种行为
发现非法存取，发现潜在威胁
-
C2以上安全级别的DBMS必须具有审计功能
-
-
可以被审计的事件
- 服务器事件
- 系统事件
- 语句事件
- 模式对象事件
-
审计日志管理
-
审计功能的可选性
- 审计很费时间和空间
- DBA可以根据应用对安全性的要求，灵活的打开或关闭审计功能
-
审计功能设置
- AUDIT语句：设置审计功能
- NOAUDIT语句：取消审计功能
-
用户级审计
- 任何用户可设置的审计
- 主要是用户针对自己创建的数据库表和视图进行审计
-
系统级审计
- 只能由DBA来设置
- 监测成功或失败的登录要求，监测授权和回收操作以及其他数据库级权限以下的操作
【eg.4.15】对修改SC表结构或修改SC表数据的操作进行审计
AUDIT ALTED,UPDATE
ON SC;
【eg.4.16】取消对SC表的一切审计
NOAUDIT ALTED,UPDATE
ON SC;
4.5 数据加密
- 防止数据库中数据在存储和传输过程中失密的有效手段
- 加密的基本思想
- 根据一定的算法将原始数据——明文变化成不可直接识别的格式——密文
- 加密方法
- 存储加密
- 传输加密
4.6 其他安全性保护
- 推理控制
- 处理强制存取控制未解决的问题，避免用户利用能够访问的数据推知更高机密的数据
- 常用方法
- 基于函数依赖 的推理控制
- 基于敏感关联 的推理控制
- 隐秘信道
五. 数据库完整性
-
数据完整性
- 数据的正确性
- 是指数据是否符和现实世界语义，反映了当前实际状况
- 数据的相容性
- 是指数据库同一对象在不同关系表中的数据是否是符合逻辑的
- 数据的正确性
-
数据的完整性和安全性是不同的概念
- 数据库的完整性
- 防止数据库中存在不符合语义的数据，也就防止数据库中存在不正确的数据
- 防范对象：不合语义、不正确的数据
- 数据的安全性
- 保护数据库防止恶意的破坏和非法的存取
- 防范对象：非法用户和非法操作
完整性是阻止合法用户通过合法操作向数据库中加入不正确的数据
安全性防范的是非法用户和非法操作存取数据库中的正确数据
- 数据库的完整性
-
为维护数据库的完整性，数据库管理系统必须
- 提供定义完整性约束条件的机制
- 提供完整性检查机制
- 违约处理
- 拒绝(NO ACTION)执行该操作
- 级联（CASCADE）执行该操作
5.1 实体完整性
5.1.1 实体完整性定义
关系模型的实体完整性
CREATE TABLE 中用 PRIMARY KEY定义
- 单属性构成码有两种说明方法
- 定义为列级约束条件
- 定义为表级约束条件
- 对多个属性构成的码只有一种说明方法
- 定义为表级约束条件
【eg.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)
);
【eg.5.2】将SC表中的Sno，Cno属性定义为码
CREATE TABLE SC
( Sno CHAR(9) NOT NULL,
Cno CHAR(4) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno) /* 只能在表级定义主码*/
);
5.1.2 实体完整性检查和违约处理
- 插入或者对主码列进行更新操作时检查
- 检查主码值是否唯一，如果不唯一则拒绝插入或修改（进行全表扫描）
- 检查主码的各个属性是否为空，只要有 一个为空就拒绝插入或修改
RDBMS完整性控制机制的三个组成部分
- 定义机制
- 检查机制
- 违约处理
5.2 参照完整性
5.2.1 参照完整性定义
- 关系模型的参照完整性定义
- 在CREATE TABLE 中用FOREIGN KEY短语定义那些列为外码
- 用REFERENCES短语指明这些外码参照那些表的主码
【eg.5.3】关系SC中(Sno,Cno)时主码，Sno，Cno分别参照Student表中的主码和Course表中的主码
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), /*在表级定义参照完整性*/
FROEIGN KEY (Cno) REFERENCES Course(Cno) /*在表级定义参照完整性*/
);
5.2.2 参照完整性检查和违约处理
- 在对被参照表各参照表进行增删改操作时可能破坏参照完整性，必须进行检查
- 插入元组或修改外码只：拒绝
- 删除元组或修改主码值：拒绝/级联删除/设置为空值
5.3 用户定义的完整性
5.3.1 属性上的约束条件
-
用户定义的完整性是：针对某一具体应用的数据必须满足的语义要求
-
关系数据库管理系统提供了定义和检验用户定义完整性的机制
-
CREATE TABLE 时定义属性上的约束条件
- 列值非空（NOT NULL)
- 列值唯一（UNIQUE）
- 检查列值是否满足一个条件表达式（CHECK)
-
属性上的约束条件检查和违约处理
-
插入元组或修改属性时，RDBMS检查到属性上的约束条件是否满足
-
如果不满足则该操作被拒绝执行
-
5.3.2 元组上的约束条件
-
属性上的约束条件：只涉及单个属性
-
元组级的限制：可以设置不同属性之间的取自的相互约束条件
-
在CREATE TABLE 时可以用CHECK子句来定义元组上的约束条件
-
插入元组或者修改属性的值时，RABMS检查到元组上的约束条件是否被满足
-
如果不满足则拒绝执行
5.4 完整性约束命名字句
-
完整性约束命名子句
CONSTRAINT<完整性约束条件名><完整性约束条件>-
完整性约束条件包括
NOT NULL ,UNIQUE.PRIMARY KEY短语,FOREIGN KEY短语,CHECK短语
-
【eg. 5.10】建立学生登记表Student，要求学号在90000_99999之间
姓名不能取空值，年龄小于30，性别只能取男女
CREAATE TABLE
( Sno NUMERIC(6)
CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),
Sname CHAR(10) CONSTRAINT C2 NOT NULL,
Sage NUMERIC(3) c3 CHECK (Sage<30),
Ssex CHAR(2) CONSTRAINT C4 CHECK(Ssex IN ('男','女'))，
CONSTRAINT StudentKey PRIMARY KEY(Sno)
);
- 修改表中的完整性限制
- 使用ALTER TABLE 语句修改表中的完整性限制
【eg. 5.12】去掉Student中对性别的限制
ALTER TABLE Student
DROP CONSTRAINTC4;
【eg. 5.13】修改Student中的约束条件，要求学生改为在90000——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.5 断言
语法格式
CREATE ASSERTION <断言名><CHECK子句>
- 每个断言都被赋予一个名字
- CHECK子句 中的约束条件与WHERE子句的条件表达式类似
【eg. 5.18】限制数据库课程最多60名学生选修
CREATE ASSERTION ASSE_SC_DB_NUM
CHECK (60>=(select count(*)
FROM Cuorse,SC
WHERE SC.Cno=Course.Cno AND
Course.name='数据库')
)；
触发时机：向SC表中插入元组
违约反应：拒绝执行
【eg. 5.19】限制每一门课程最多60名学生选修
CREATE ASSERTION ASSE_SC_CNUM1
CHECK(60>=(SELECT count(*)
FROM SC
GRUOP BY Cno)
);
【eg. 5.20】限制每个学期每一门课最多60名学生选修
/*首先需要修改SC表的模式，增加一个‘学期’(TERM）属性*/
ALTER TABLE SC ADD TERM DATE;
/*然后定义断言*/
CREATE ASSERTION ASSE_SC_CNUM2
CHECK(60>=(SELECT count(*)
FROM SC
GROUP BY Cno,TERM)
);
-
删除断言的语句格式
DROP ASSERTION <断言名>；
5.7 触发器
5.7.1 定义触发器
-
触发器
- 用户定义在关系表上的一类由时间驱动的特殊过程
- 任何用户对表的增删改操作均有服务器自动激活相应的触发器
- 触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力
-
CREATE TRIGGER语法格式
CREATE TRIGGER<触发器名> {BEFORE|AFTER}<触发事件>ON<表名> REFERENCING NEW|OLD ROWS AS<变量> FOR EACH {ROW|STATEMENT} [WHERE <触发条件>]<触发动作体>三要素
触发事件
触发条件
触发动作
5.7.2 激活触发器
一个数据表上可能定义了多个触发器，遵循如下的执行顺序
- 执行该表上的BEFORE触发器
- 激活触发器的SQL语句
- 执行该表上的AFTER触发器
5.7.3 删除触发器
六. 关系数据库理论
6.1 问题提出
1. 好的数据库逻辑模式
-
什么是一个好的模式
好的模式不会发生插入，删除，更新异常，数据冗余度近可能的少
-
问题的原因
由于模式中的某些数据依赖引起的
2. 数据依赖
-
完整性约束的一种表现形式
-
限定属性取值范围：例如学生成绩必须在0—100之间
-
定义属性值之间的相互关联（主要体现于值的相等与否）
即通过属性值的相等与否来描述
-
它是数据库模式设计的关键
-
-
数据依赖
- 是通过一个关系中属性值间值的相等与否体现出来的数据间的相互关系
- 是现实世界属性间相互联系的抽象
- 是数据内在的性质
- 是语义的体现
-
数据依赖的主要类型
- 函数依赖（FD）
- 多值依赖（MVD）
- 连接依赖
- …
-
数据依赖对关系模式的影响
- 不适合的数据依赖，造成插入，删除，更新异常和数据冗余问题
3. 关系模式的简化模式
关系模式可以形象化的表示为：
R(U,D,DOM,F)
| R | 关系名 |
|---|---|
| U | 组成该关系的属性名集合 |
| D | U中属性来自的域 |
| DOM | 属性向域的映像集合 |
| F | 属性间数据的依赖关系的集合 |
-
关系模式可以简化为
R<U,F>
将关系模式简化成一个三元组，影响数据库模式设计的主要是U和F
当且仅当U上的一个关系r满足F时，r称为关系模式R(U,F)的一个关系
6.2 规范化
6.2.1 函数依赖
1. 函数依赖
函数依赖不是指关系模式R中某个或某些关系实例r满足的约束条件，而是指R中的所有关系实例r均要满足的约束条件。
-
如何确定函数依赖
- 根据数据的语义来确定函数依赖；
- 数据库的设计者可以实现对现实世界作强制的规定；
2. 平凡函数依赖与非平凡函数依赖
在关系SC(Sno,Cno,Grade)中
非平凡函数依赖：(Sno,Cno)确定Grade //Grade不包含于(Sno,Cno)
平凡函数依赖： (Sno,Cno)确定Sno
(Sno,Cno)确定Cno
对于任意关系模式，平凡函数依赖总是成立的，它不反映新的语义，因此不做特殊声名，我们总是讨论非函数依赖。
3. 完全函数依赖与部分函数依赖
F：完全函数依赖
P：部分函数依赖
4. 传递函数依赖
在关系STUDENT (Sno,Sdept,Mname,Cno,Grade)中，
Sno函数确定Sdept,Sdept函数确定Mname,
Sno传递确定Mname
6.2.2 码
-
假定K为关系模式R<U,F>z中的属性或属性组合，若K完全函数依赖U，则K称为R的一个候选码。
- 如果U部分依赖于K，则称K为超码；
- 候选码是最小的超码，则K 的任意一个真子集都不是候选码；
-
若关系模式R有多个候选码，则选定其中一个做为主码。
-
主属性和非主属性
- 包含在任何一个候选码中的属性，称为主属性
- 不包含在任何候选码中的属性称为非主属性或非码属性
-
全码：整个属性组是码(ALL-Key)
-
外码
关系模式R<U,F>，U中属性或者属性组X并非R的码，但X是另外一个关系模式的码，则称X是R的外部码，也称外码。
SC(Sno,Cno,Grade)中，Sno不是码，但Sno是关系模式 S(Sno,Sdept,Sage)的码，则Sno是关系模式SC的外部码主码与外码一起提供了表示关系间联系的手段
6.2.3 范式
-
范式是符合某一种级别的关系模式的集合
-
关系数据库中的关系必须满足一定的要求
满足不同程度要求的为不同范式
-
范式的种类
范式 表示 第一范式 1NF 第二范式 2NF 第三范式 3NF BC范式 BSNF,Boyce和Codd共同提出的范式 第四范式 4NF 第五范式 5NF -
各种范式之间存在联系
- 某一种关系模式R为第n范式，可简记为R属于nNF
-
一个低一级范式的关系模式，通过模式分解可以转换成若干个高一级范式的关系模式的集合，这种过程就叫做规范化
-
1NF的定义
如果一个关系模式R中的所有属性都是不可分的数据项，则R属于1NF
第一范式是对关系模式的最起码的要求，不满足第一范式的数据库模式不能称为关系数据模式
6.2.4 第二范式（2NF)
-
满足第一范式的关系模式不一定是一个好的关系模式
-
2NF的定义：
若关系模式R属于1NF，并且每一个主属性都完全函数依赖于R的码，则R属于2NF
S-L-C(Sno,Cno,Sdept,Sloc,Grade)属于1NF
S-L-C(Sno,Cno,Sdept,Sloc,Grade)不属于2NF
非主属性Sdept和Sloc部分函数依赖于(Sno,Cno)
6.2.5 第三范式(3NF)
-
2NF还不能完全消除关系模式中的各种异常情况和数据冗余。
-
原因：可能存在非主属性的传递函数依赖。
-
解决方法：采用投影分解法，将关系继续分解为两个关系模式，以消除传递函数依赖。
分解后的关系模式，既没有非主属性对码的部分函数依赖，也没有非主属性对码的传递函数依赖，进一步的解决了插入异常、删除异常、数据冗余度大和修改复杂四个问题。
-
3NF的定义：
关系模式R属于第一范式，且R中不存在部分函数依赖和传递函数依赖，就称R属于3NF。
-
3NF的一些性质
- 若R属于3NF，则R的每一个主属性既不部分函数依赖于候选码，也不传递函数依赖于候选码；
- 如果R属于3NF，则R属于2NF；
- 采用投影分解法将一个2NF的关系分解成多个3NF的关系，可以在一定程度上解决原2NF关系中存在的插入异常，删除异常，数据冗余度大和修改复杂等问题；
- 并不能完全消除关系模式中的各种异常情况和数据冗余。
6.2.6 BC范式（BCNF)
-
BCNF比3NF更近一步，有时也称为扩展的第三范式
-
BCNF定义：
在关系模式R<U,F>中，如果每一个决定因素都包含码，则R属于BCNF
-
BCNF的关系模式所具有的性质
- 所有非主属性对每一个码都是完全函数依赖；
- 所有主属性对每一个不包含它的码也都是完全函数依赖;
- 没有任何属性完全函数依赖于非码的任何一组属性;
- 如果关系模式R属于BCNF，必定有R属于3NF;
- 如果关系模式R属于3NF，不一定有R属于BCNF;
- 如果一个关系数据库中的所有模式都属于BCNF，那么函数依赖范畴内，它已经实现了模式的彻底分解，达到了最高的规范化程度，消除了操作异常诸多问题。
七. 数据库设计
7.1 数据库设计概述
-
广义：是数据库及其应用系统的设计，即设计整个数据库应用系统
狭义：是设计数据库本身，即设计数据库的各级模式并建立数据库，这是数据库应用系统的一部分
-
数据库设计
是指对于一个给定的应用环境，设计一个优良的数据库逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效的存储和管理数据，满足各种用户应用需求，包括信息管理要求和数据处理要求
- 信息管理要求：在数据库中存储和管理需要的数据对象
- 数据处理要求：对数据对象需要进行的处理：查询、增删改、统计和分析等。
7.1.1 数据库设计的特点
-
基本规律
- 技术、管理、基础数据
- 管理
- 基础数据：数据的收集、整理、组织和不断更新
-
结构（数据）设计和行为（处理）设计相结合
- 将数据库结构设计和数据处理设计密切结合
7.1.2 数据库设计方法
- 手工设计法
- 规范设计法
- 典型方法：新奥尔良法
- 基于E-R模型的设计方法
- 数据库设计工具
7.1.3 数据库设计的基本步骤
-
数据库设计可以分为六个阶段
- 需求分析 （独立于任何数据库管理系统）是基础
- 概念结构设计（独立于任何数据库管理系统）是关键
- 逻辑结构设计（与选用的数据库管理系统密切相关）把概念结构转换成数据模型
- 物理结构设计（与选用的数据库管理系统密切相关）
- 数据库实施
- 数据库运行和维护
7.1.4 数据库设计过程中的各级模式
7.2 需求分析
-
需求分析：分析用户的需求，是数据库设计的起点
-
重要性
结果是否准确的反映了用户的实际要求，将直接影响到后面各个阶段的设计，并将影响到设计结果能否合理和实用
7.2.1 需求分析的任务
- 调查的重点是“数据”和“处理”，获得用户对数据库的要求
- 信息要求
- 处理要求
- 安全性和完整性要求
7.2.2 需求分析的方法
7.2.3 数据字典
-
数据字典
数据字典是关于数据库中数据的描述，称为元数据
它不是数据本身，而是数据的数据
-
数据字典在需求分析阶段建立，在数据库设计中不断修改，充实，完善
-
数据字典是进行详细的数据收集和分析所得到的主要结果
-
和dbms数据字典有区别
-
数据字典的内容
- 数据项
- 数据结构
- 数据流
- 数据存储
- 处理过程
-
数据项是数据的最小组成单位
-
若干个数据项可以组成一个数据结构
-
通过对数据项和数据结构的定义来描述数据流、数据存储的逻辑内容
-
数据项
- 数据项是不可再分的数据单位
- 数据项描述={数据项名，数据项含义说明，别名，数据类型，长度，取值范围，取值含义，与其他数据项的逻辑关系，数据项之间的关系}
- 关系规范化理论为指导，用数据依赖的概念分析和抽象了数据项之间的联系——函数依赖
- 取值范围 与其他数据项的逻辑关系 定义了数据的完整性约束条件，是模式设计、完整性检查条件、触发器、存储过程的依据
-
数据结构
- 数据结构反映了数据之间的组合关系
- 一个数据结果可以由若干个数据项组成，也可以由若干个数据结构组成，或由若干个数据项和数据结构混合组成
- 数据结构描述={数据结构名，含义说明，组成：{数据项或数据结构}}
-
数据流
- 数据流是数据结构在系统内部传输的路径
- 数据流的描述={数据流名，说明，数据流来源，数据流去向，组成：{数据结构}，平均流量，高峰期流量}
- 数据流来源：说明该数据流来自那个处理过程/数据存储
-
数据存储
- 是数据结构停留或保存的地方，也是数据流的来源和去向之一
- 数据存储描述={数据存储名，说明，编号，输入的数据流，输出的数据流，组成：{数据结构}，数据量，存取频度，存取方式}
-
处理过程
- 具体处理逻辑一般用判定表或判定树来描述
- 数据字典中只需要描述处理过程的说明性信息
- 处理过程描述{处理过程名，说明，输入：{数据流}，输出：{数据流}，处理：{简要说明}}
7.3 概念结构设计
7.3.1 概念模型
-
将需求分析得到的用户需求抽象为信息结构即概念模型的过程
-
概念模型就是现实世界的一个真实模型，是各种数据模型的共同基础，他比数据模型更独立于机器，更抽象，从而更稳定
-
回顾
-
实体：客观存在并且可以相互区别的事物称为实体，可以是具体 的人、事、物品、或抽象的概念、
-
属性：实体所具有的某一特性叫做属性，一个实体可以由若干个属性刻画
-
码：唯一标识实体的属性集称为码
-
实体型：用实体名及其属性名集合来抽象和刻画同类实体称为实体型
-
实体集：同一类型实体的集合称为实体集
-
联系
- 实体内部的联系：是指组成实体的各属性之间的联系
- 实体之间的联系：通常是指不同实体集之间的联系
实体之间的联系有一对一（1：1）
一对多（1：m）
多对多（m：n）
等多种类型。
概念模型的一种表示方法：实体-联系方法
- 用E-R图来描述现实世界 的概念模型
- E-R图的方法也称为E-R模型
-
7.3.2 E-R模型
7.4 逻辑结构设计
-
把概念结构设计阶段设计好的基本E-R图转换为与选用的DBMS产品所支持的逻辑结构
-
目前主要使用关系模型，关系模型的逻辑结构是一组关系模式的集合
7.4.2 数据模型的优化
-
数据库逻辑设计的结果不是唯一的
-
得到初步数据模式后，还应当适当的修改、调整数据库逻辑结构，以进一步提高数据库应用系统的性能，这就是数据模型的优化
-
关系数据库的优化通常以规范化理论为指导
-
方法：
-
确定函数依赖
-
对于各个关系模式之间的数据依赖进行极小化处理，消除冗余的联系
-
按照数据依赖的理论对关系模式进行分析，考察是否存在部分函数依赖、传递函数依赖，多值依赖等，确定各关系模式分别属于第几范式
-
按照需求分析阶段得出的各种应用对数据处理的要求，分析对于这样的应用环境这些模式是否合适，确定是否要对他们进行合并或者分解
-
7.4.3 设计用户子模式
- 数据库模式：全局模式
- 用户子模式：视图机制
十. 数据库恢复技术
10.1 事务的基本概念
10.1.1 事务
1. 基础概念
- 事务：用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位
- 事务和程序是两个概念
- 在关系数据库中，一个事务可以是一条SQL语句（单语句事务），一组SQL语句或者整个程序（多语句事务）
- 一个程序通常包含多个事务
- 事务是恢复并非控制的基本单位
2. 定义事务
-
显示定义方式
BEGIN TRANSACTION SQL 语句1 SQL 语句2 .... COMMIT /*事务正常结束 提交事务的所有操作 事务中所有对数据库的更新写回到磁盘上的物理数据库中 相当于全做*/ BEGIN TRANSACTION SQL 语句1 SQL 语句2 .... ROLLBACK /*事务异常终止 事务运行的过程中发生了故障，不能继续执行 系统将事务中对数据库的所有已完成的操作全部撤销 事务滚回开始时的状态 相当于全不做*/ -
隐式方式
当用户没有显示的定义事务时，数据库管理系统按缺省值规定自动划分事务
10.1.2 事务的ACID特性
1. 原子性
-
事务时数据库的逻辑工作单位
事务中包括的诸操作要么全做，要么全不做
2. 一致性
- 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态
- 一致性状态
- 数据库中只包含成功事务提交的结果
- 不一致状态
- 数据库系统运行中发生故障，有些事务尚未完成就被迫中断
- 这些未完成的事务对数据库所作的修改有一部分已经写入物理数据库，这时数据库就处于一个不正确的状态
3. 隔离性
- 一个事务的执行不能被其他事务干扰
- 一个事务内部的操作以及使用的数据对其他并发事务时隔离的
- 并发执行的各个事务之间不能相互干扰
4.持续性
- 持续性也称永久性
- 一个事务一旦提交，它对数据库中数据的改变就应该是永久的
- 接下来的其他操作或故障不应该对其执行结果有任何影响
- 保证事务ACID特性是事务处理的任务
- 破坏事务ACID特性的因素
- 多个事务并行时，不同事物的操作交叉执行
- 事务在运行过程中被强行停止
10.2 数据库恢复概述
-
故障时不可避免的
- 计算机硬件故障
- 软件的错误
- 操作员的失误
- 恶意的破坏
-
故障的影响
- 运行事务非正常中断，影响数据的正确性
- 破坏数据库，全部或者部分丢失数据
-
数据库的恢复
数据库管理系统必须具有把数据库从错误状态恢复到某一已知的正确状态（亦称为一致状态或完整状态）的功能，这就是数据库的恢复管理系统对故障的对策。
-
恢复子系统是数据库管理系统的一个重要组成部分
-
恢复技术是衡量系统优劣的重要指标
10.3 故障的种类
1. 事务内部的故障
- 有的是可以通过事务程序本身发现的
- 有的是非预期的，不能有事务程序处理的
- 事务内部更多的故障是非预期的，是不能用应用程序处理的
- 运算溢出
- 并发事务发生死锁而被选中撤销该事务
- 违反了某些完整性限制而被终止等
- 后面，事务故障仅指这内非预期的故障
- 事务故障意味着
- 事务没有达到预期的终点（COMMIT或显示的ROLLBACK）
- 数据库可能处于不正确状态
- 事务故障的恢复：事务撤销（UNDO)
- 强行回滚（ROLLBACK)该事务
- 撤销该事务已经作出的任何对数据库的修改，使得该事务像没有启动一样
2. 系统故障
-
系统故障
称为软故障，是指造成系统停止运转的任何事件，使得系统要重新启动
- 特定类型的硬件错误（如CPU故障）
- 操作系统故障
- 数据库管理系统代码错误
- 系统断电
-
影响
- 整个系统的正常运行突然被破坏
- 所有正在运行的事务都非正常终止（所有活跃事务都只运行 了一部分，没有完全完成）
- 内存中数据库缓冲区的信息全部丢失
- 不破坏数据库
-
发生系统故障时，一些尚未完成的事务的结果可能已经送入到物理数据库，造成数据库可能处于不正确状态
- 恢复策略：系统重新启动时，恢复程序让所有非正常终止的事务回滚，强行撤销（UNDO）所有未完成事务。
-
发生系统故障时，有些已完成的事务可能有一部分甚至全部留在缓冲区，尚未写回到磁盘上的物理数据库中，系统故障使得这些事务对数据库的修改部分或全部丢失
- 恢复策略：系统重新启动时，恢复程序需要重做（REDO）所有已经提交的事务
-
系统故障的恢复需要做两件事
- 撤销所有未完成的事务
- 重做所有已提交的事务
3. 介质故障
- 称为硬故障，指外存故障
- 磁盘损坏
- 磁头碰撞
- 瞬时强磁场干扰
- 介质故障破坏数据库或部分数据库，并影响正在存取这部分数据的数据库的所有事务
- 介质故障比前两类故障的可能性小的多，但破坏性大得多
4. 计算机病毒
- 一种人为的故障或破坏，是一些恶作剧者研制的一种计算机程序
- 可以繁殖和传播，造成对计算机系统包括数据库的危害
- 计算机病毒已成为计算机系统的主要威胁，自然也是数据库系统的主要威胁
- 数据库一旦被破坏仍要用恢复技术把数据库加以恢复
10.4 恢复的实现技术
-
恢复操作的基本原理：冗余
- 利用存储在系统别处的冗余数据来重建数据库中已被破坏或不正确的数据
-
恢复的实现技术：复杂
- 一个大型的数据库产品，恢复子系统的代码要占全部代码的10%以上
-
如何建立冗余数据
-
数据转储（backup)
-
登录日志文件(logging)
-
10.4.1 数据存储
1. 什么是数据存储
- 转储是指数据库管理员定期将整个数据库复制到磁带、磁盘或其他存储介质上保存起来的过程
- 备用数据文本称为后备副本（backup)或者后援副本
- 数据库遭到破坏以后可以将后被副本重新装入
- 重装后备副本只能将数据库恢复到转储时的状态
- 要想恢复故障发生时的状态，必须重新运行自转储以后的所有更新事务
2. 存储方法
静态转储和动态转储
- 静态转储
- 在系统中五运行事务时进行的转储操作
- 转储开始时数据库处于一致性状态
- 转储期间不允许对数据库的任何存取修改活动
- 得到的一定是一个数据一致性的副本
- 优点：简单
- 缺点：降低了数据库的可用性
- 转储必须要等正运行的用户事务结束
- 新书屋必须等转储技术
- 转储操作与用户事务并发进行
- 转储期间允许对数据库进行存取或修改
- 优点
- 不用等待正在运行的用户事务结束
- 不会影响新事物的运行
- 缺点
- 不能保证副本中的数据正确有效
- 利用动态转储得到的副本进行故障恢复
- 需要把动态转储期间各事务对数据库的修改活动登记下来，建立日志文件
- 后备副本加上日志文件就能把数据库恢复到某以时刻的正确状态
海量转储与增量转储
- 海量转储：每次转储全部数据库
- 增量转储：只转储上次转储后更新过的数据
- 海量转储和增量转储比较
- 从恢复角度来看，使用海量转储得到的后备副本进行恢复往往更方便
- 如果数据库很大，事务处理又非常繁琐，则增量转储方式更实用有效
转储方式小结
| 转储方式 | 转储状态 | |
|---|---|---|
| 动态转储 | 静态转储 | |
| 海量转储 | 动态海量转储 | 静态海量转储 |
| 增量转储 | 动态增量转储 | 静态增量转储 |
在数据转储效率、数据库运行效率和故障恢复效率三方面各有利弊
10.4.2 登录日志文件
1. 日志文件的格式和内容
-
日志文件
- 日志文件是用来记录事务对数据库的更新操作的文件
-
以记录为单位的日志文件内容
- 各个事务的开始标记（BEGIN TRANSACTION)
- 各个事务的结束标记（COMMIT或ROLLBACK）
- 各个事务的所有更新操作
以上均作为日志文件中的一个日志记录
-
记录事务开始标记的日志记录
- 事务标记+BEGIN TRANSACTION
-
记录事务结束标志的日志记录
- 事务标记+COMMIT
- 事务标记+ROLLBACK
-
记录事务更新操作的日志记录
2. 日志文件的作用
- 用途
- 进行事务故障恢复
- 进行系统故障恢复
- 协助后备副本进行介质故障恢复
- 具体作用
- 事务故障恢复和系统故障恢复必须使用日志文件
- 在动态转储方式中必须建立日志文件，后备副本和日志五年间结合起来才能有效的恢复数据库
- 在静态转储方式中，也可以建立日志文件
3. 登录日志文件
- 为保证数据库是可恢复的，登录日志文件时必须遵循两条原则
- 登录的次序严格按并发事务执行的事件次序
- 必须先写日志文件，后写数据库
10.5 恢复策略
10.5.1 事务故障的恢复
- 事务故障：事务在运行至正常终点前被终止
- 恢复方法
- 由恢复子系统利用日志文件撤销（UNDO）此事务已对数据库进行的修改
- 事务故障的恢复主要由系统自动完成，对用户时透明的，不需要用户干涉
10.5.2 系统故障的恢复
- 系统故障造成数据库不一致状态的原因
- 未完成事务对数据库的更新可能已经写入数据库
- 已提交的事务对数据库更新可能还留在缓冲区没来得及写入数据库
- 恢复方法
- UNDO故障发生时未完成的事务
- REDO已完成的事务
- 系统故障的恢复由系统在重新启动时自动完成，不需要用户干预
10.5.3 介质故障的恢复
- 恢复步骤
- 装入最新的后备数据库副本（离故障发生时刻最近的转储副本），使数据库恢复到最近一次转储时的一致性状态
- 对于静态转储的数据库副本装入数据库即处于一致性状态
- 对于动态转储的数据库副本，还需要同时装入转出时刻的日志文件副本，利用恢复系统故障的方法（REDO+UNDO),才能将数据库恢复到一致性状态。
- 装入有关的日志文件副本（转储结束时刻的日志文件副本）重做已完成的事务
- 装入最新的后备数据库副本（离故障发生时刻最近的转储副本），使数据库恢复到最近一次转储时的一致性状态
- 介质故障的恢复需要DBA的介入
- DBA的工作
- 重装最近转储的数据库副本和有关的各日志文件副本
- 执行系统提供的恢复命令
- 具体的恢复操作任由DBMS完成
2049
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
