数据库期末复习

引言

1.1

1.1.2数据管理技术的发展

1. 人工管理阶段
2. 文件系统阶段
3. 数据库系统阶段

1.2 数据库管理的组成和特点

1.2.1组成

1. 计算机硬件系统
2. 数据库集合
3. 数据库管理系统(DBMS)
4. 相关的软件系统
5. 数据库管理员以及其他人员

1.2.2特点

1. 数据冗余度小,数据共享性高

2. 具有较高的数据独立性

数据库系统的数据独立性是指用户应用程序与数据库的数据相互独立,体现在不会因为数据存储结构与数据逻辑结构变化而影响应用程序

3. 数据结构化

4. 具有统一的数据控制功能

• 数据安全性控制
• 数据完整性控制

数据库的完整性是指数据的_正确性和相容性_

• 数据并发控制
• 数据恢复

数据库恢复要涉及到的两个技术是_数据转储、登记日志文件_

1.2.3数据库的基本特征

• 组织性（数据按一定的数据模型组织、描述和存储）
• 共享性(多种应用、多种语言、多个用户相互覆盖地使用数据集合)、冗余度较小、独立性高、易扩展性
• (永久存储、有组织、可共享）

1.3数据库管理系统的主要功能

• 数据定义
• 数据组织、存储和管理
• 数据操纵
• 数据库的事务管理和运行管理
• 数据库的运行和维护功能,运行控制功能
• 数据字典

1.4基本数据模型

1.4.1数据模型

1.概念模型

概念模型也叫信息模型,是按用户的观点来对数据和信息建模，用于数据库设计,E-R模型就是概念模型.概念模型独立于具体的机器(硬件设备)和DBMS

2.逻辑模型

1. 层次模型
   利用树型结构

2. 网状模型
   利用网状结构

层次模型和网状模型中的单位是基本层次联系，这是指两个记录(型)以及它们之间的一对多(包括一对一)的联系。

3. 关系模型
   利用二维表(最广泛的模型,具有严格的数学理论基础)

3.物理模型

物理模型是对数据最底层的抽象，描述数据在系统内部的表示方式和存取方法，在磁盘或磁带上的存储方式和存取方法。面向计算机系统，物理模型的具体实现是DBMS的任务。

1.4.2数据模型的组成要素

1. 数据结构

对数据系统的静态特性描述

2. 数据操作

对数据系统的动态特性描述

3. 数据的完整性约束条件

1.5数据库系统模式

1.5.2三级模式结构

1.模式

模式也叫逻辑模式,数据库系统模式结构中的中间层,是所有用户的公共数据视图,一个数据库只有一个模式

2.外模式

外模式也叫子模式或者用户模式,是模式的子集,一个数据库可以有多个外模式,每个用户只能看见和访问所对应的外模式的数据

3.内模式

内模式也叫存储模式,一个数据库只有一个内模式

1.5.3二级映像功能

模式: 全局逻辑独立性
外模式 :局部逻辑独立性
外模式/模式映像——数据的逻辑独立性
模式/内模式映像——数据的物理独立性

数据的物理独立性是指用户的应用程序与存储磁盘上数据库的数据是相互独立的

1.6数据库设计步骤

按照规范的设计方法，一个完整的数据库设计一般分为以下六个阶段：

1. 需求分析：分析用户的需求，包括数据、功能和性能需求；

2. 概念结构设计：主要采用E-R模型进行设计，包括画E-R图；

3. 逻辑结构设计：通过将E-R图转换成表，实现从E-R模型到关系模型的转换(其中包括对关系进行规范化处理)；

4. 数据库物理设计：主要是为所设计的数据库选择合适的存储结构和存取路径；

5. 数据库的实施：包括编程、测试和试运行；

6. 数据库运行与维护：系统的运行与数据库的日常维护。

第二章——关系模型介绍

2.1 关系数据库的结构

• 元组:行
• 关系:表
• 属性:列
• 域: 列的所有取值的集合 (通常叫做**属性的域)

2.3 码

• 超码: 一个或者多个属性的集合(里面有无关紧要的属性),这些属性上的取值保证可以唯一识别出关系中的元组

• 候选码:最小的超码(每个属性都有用,决定其他属性的属性集合)

• 主码:是被数据库设计者选中,只是用来区分而已(关系的一个候选码可以作为主码)

• 外码和参照关系

2.4完整性约束

1. 实体完整性（主码的值不能重复，主码的属性非空,primary key)

体现：对基本表插入或更新操作的时候，
（1）检查主码是否唯一，如果不唯一则拒绝插入或修改。
（2）检查主码的各个属性是否为空，只要有一个为空则拒绝插入或修改。

2. 参照完整性（外码可以是空值或者另一个关系主码的有效值,foreign key）

解决措施：
1.拒绝执行，不允许该操作执行。
2.级联操作，当删除或修改被参照表的一个元组导致与参照表不一致的时候，删除或修改参照表中的所有导致不一致的元组。
3.设置为空值，当删除或修改被参照表的一个元组导致与参照表不一致的时候，将参照表中的所有导致不一致的元组设置为空值。【需要考虑是否能为空】

3. 用户定义完整性(constraint)

包括：
1.列值非空
2.列值唯一
3.检查列值是否满足一个通过Check语句定义的布尔表达式。通过Check语句可以定义在列级、行级的约束

2.5用户5大完整性约束

#主键约束
alter table [表名] add constraint [列名] primary key (id)
#check约束
alter table [表名] add constraint [列名] check(条件)
#unique约束
alter table [表名] add constraint [列名] unique(ename)
#默认约束
alter table [表名] add constraint default 约束类型 默认值 for 列名
#外键约束
alter table [表名] add constraint foreign key (列名) references 被引用的表名称（列名）

2.6 关系运算

符号	使用实例
${\alpha }_{条件}(表名)$ 选择	${\alpha }_{salary>=5000}(instructor)$
${\Pi }_{属性名}(表名)$ 投影	${\Pi }_{ID,salary}(instructor)$
自然连接
$\times$笛卡尔积	[表名]$\times$[表名]
$U$并	表名$U$表名(要求两个表的属性完全相同)

关系模型的优缺点

优点：

• 它是建立在严格的数学概念的基础上的；
• 关系模式的概念单一；
• 实体和各类联系都用关系来表示；
• 对数据的检索结果也是关系；
• 关系模型的存取路径对用户透明；
• 具有更高的数据独立性，更好的安全保密性；
• 简化了程序员的工作和数据库开发建立的工作。
  缺点：
• 存取路径对用户透明导致查询效率往往不如非关系数据模型
• 为提高性能，必须对用户的查询请求进行优化增加了开发DBMS的难度。
• 数据库系统的三级模式（每个模式的定义和特点）

第三章——SQL

特点

1. 统一综合

集数据定义语言,数据操作语言,数据控制语言功能于一体

2. 高度非过程化

存取路径的选择以及SQL的操作过程由系统自动完成

3. 面向集合的操作方式

操作对象、查找结果、一次插入、删除、更新操作的对象都可以是元组的集合

4. 以同一种语言结构提供多种使用方式

SQL是独立的语言,又是嵌入式的语言,关系数据库语言

5. 语言简洁、易学易用

功能

1. 数据定义 :数据定义语言用于定义数据的结构，比如创建、修改或者删除数据库对象，相应的语句是CREATE、ALTER 和 DROP；

2. 数据操纵 :数据操纵语言用于修改数据，修改数据包括插入、更新和删除，对应的语句是 INSERT、UPDATE 和 DELETE；

3. 数据控制 :数据控制语言用于定义数据库用户的权限，其中授予权限使用 GRANT 语句，收回权限使用 REVOKE 语句等等。

4. 数据查询 :数据查询语言用于检索，检索数据使用 SELECT 语句；

3.2基本数据类型

• char(n) 用户指定长度

• varchar(n) 指定最大长度

• int

• smallint

• numeric(p,d) 定点数(p个数字,d是小数点右边数字个数)

• float

3.2.2基本模式定义

• 创建

create table(
  primary key (course_id,sex_id),
  foreign key (dept_name) references tabke_name(dept_name)
);

• 删除元组

delete from table_name where ; 

• 删除表

drop table table_name

• 添加列

ALTER TABLE 【表名】 ADD 【列名】 【类型】

• 删除列

ALTER TABLE 【表名】 DROP 【列名】

• 重命名列

ALTER TABLE 【表名】 CHANGE 【列名】【新名】

• 修改表字段

alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

• 修改表名

ALTER TABLE tbl_name rename new_tbl_name;

3.3 SQL查询基本结构

• all :不去重
• distinc :去重

3.3.3 自然连接

• natural join

3.5 集合运算

运算	名称
union	并集
intersect	交集
expect	减去

3.6集合的比较

• all
• some

< >是不等于的意思
< >ALL 与所有都不相等 那么与NOT IN的意思相同
< >SOME 与部分不相等
= SOME 与部分相等
= ALL 与全部相等

• exists

3.8 重复元组

• unique :是否存在重复元组,没有就是true
• with as用法

with [表名](属性名字,属性名字) as (
      # 查询的结果
)

• update 用法

update [表名] set [列名]=[值]
where 条件

• case 用法

case 
	when pred1 then result1
	when pred2 then result2
	else result0
end

第四章 —— 中级SQL

4.2 视图

• view 的使用

create view [名字] as select
#条件
;

• 特别注意
  一般来说,如果定义视图的查询对下列条件都能满足,我们称SQL视图是可更新的(即视图上可以执行插入、更新和删除)
  • from语句中只有一个数据库关系
  • select子句中只包含关系的属性名,不包含任何表达式、聚集或者distinct声明
  • 任何没有出现在select子句中的属性可以取空值,即这些属性上没有not null约束,也不构成主码的一部分
  • 查询语句中不含有group by和having语句

4.4 完整性约束

• 分类
  • not null
  • unique :放在属性后面
  • check :放在属性后面,括号里面加入条件
• 完整性约束作用
  • 保证授权用户对数据库的所做的改变不会导致数据一致性的破坏
• 参照完整性约束作用
  • 保证出现在一个关系的给定属性集上的值同样出现在另一个关系的特定属性集上
• 域约束作用
  • 指定了在一个属性上可能取值的集合,可以禁止在特定属性上使用空值

4.5 SQL中的数据类型

• date : ‘2001-04-25’
• time : ‘09:30:00’
• timestamp :‘2001-04-25 09:30:00’

4.5.3 创建索引

create index Student_index on student(ID);

4.6 授权

• 权限授予与收回

grant [权限列表‘select,delete,*,...(update 后面可加([属性名]))’]
on [表名或者视图]
to [角色]

revoke [权限列表‘select,delete,*,...’]
on [表名或者视图]
from [角色]

• 角色

create role [角色名]

• 授权角色

grant [角色名] to [角色名]

• 权限转移

grant [权限列表] on [表名] to [角色名] with grant option #表示允许该角色将权限授权给其他角色

第五章——高级SQL

5.2函数与过程

• 函数

 create function 函数名([<参数1><类型1>[,<参数2><类型2>...]]) 
    returns 数据类型 
    begin
      sql语句;
      return 值;
    end

• 存储过程

 create procedure 名称(in [<参数1><类型1>[,<参数2><类型2>...], out [<参数1><类型1>[,<参数2><类型2>...]]) 
    begin
      sql语句;
    end

5.2.2 支持过程语言构造

• while and repeat

while 布尔表达式 do 
			语句序列	;
end while

repeat 语句序列;
until	布尔表达式
end repeat

• if

if 布尔表达式  
then		语句序列	;
elseif 	语句序列	;
else 	语句序列	;
end if

5.3触发器

• 语法

CREATE TRIGGER trigger_name
trigger_time(after,before)
([update,delete,insert] of [表名]) trigger_event ON tbl_name
(referencing new row as nrow,old row as orow )
FOR EACH ROW
trigger_stmt

闭包

第六章——形式化关系查询语言

1.关系运算

1. 并。计算两个关系在集合理论上的并集，即给出关系R和S（两者有相同元/列数），R∪S的元组包括R和S所有元组的集合，形式定义如下：
   
   式中 t是元组变量（下同）。显然，R∪S=S∪R。

2. 差。计算两个关系的区别的集合，即给出关系R和S（两者有相同元/列数），R-S的元组包
   括R中有而S中没有的元组的集合，形式定义如下：
   
   通俗点说，就是属于R但是属于S的元素。

3. 交。计算两个关系集合理论上的交集，即给出关系R和S（两者有相同元/列数），R∩S的元组包括R和S相同元组的集合，形式定义如下：
   
   显然，R∩S=R-(R-S)和R∩S=S-(S-R)成立。

4. 笛卡尔积。计算两个关系的笛卡尔乘积，令R为有m元的关系，S为有n元的关系，则R×S是m+n元的元组的集合，其前m个元素来自R的一个元组，而后n个元素来自S的一个元组。形成定义如下：
   
   若R有u个元组，S有v个元组，则R×S有u×v个元组。
   要记住笛卡尔积的数量是两者的乘积即可，相当于两者排列组合。

（5）投影。从一个关系中抽取指明的属性（列）。令R为一个包含属性A的关系，则

（6）θ连接。θ连接从两个关系的笛卡儿积中选取属性之间满足一定条件的元组，记作：

其中A和B分别为R和S上元数相等且可比的属性组。θ为“=”的连接，称为等值连接，记作：

如果两个关系中进行比较的分量必须是相同的属性组，并且在结果中将重复的属性去掉，则称为自然连接，记作：

第七章——数据库设计与E-R模型

7.1.2设计选择

我们主要避免一下两个问题

• 冗余 : 出现重复信息
• 不完整 :对关系处理的不完整

7.2实体-联系模型

7.2.3

• 简单和复合属性:简单属性不能被划分,复合属性还能被划分(比如name可以设计成为firstname和lastname)
• 单值属性和多值属性:多值属性(不同的老师可以有不同数量的电话号码)
• 派生属性:可以由其他属性计算而来

7.5 实体-联系图

7.5.1 基本结构

• 矩形:实体集的名字和所有属性的名字
• 菱形:代表联系集
• 未分割的矩形:代表联系的属性
• 线段:实体集-联系集
• 虚线:联系集-联系集属性
• 双线:实体在联系集的参与度
• 双菱形:连接到弱实体集(没有足够属性已形成主码)的标志性联系集

E-R图

第八章——范式理论

函数依赖

• 完全函数依赖：Y完全函数依赖X，就是Y依赖X的所有属性，即X中少一项都推不出Y
• 部分函数依赖：Y部分函数依赖X，就是Y依赖X的一部分属性，即X的真子集也可能推出Y

范式

• 第一范式：每个属性都不可再分
• 第二范式：第一范式 + 如果关系模式R（U，F）中的所有非主属性都完全依赖于任意一个候选关键字
• 第三范式：第二范式 + 如果关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递依赖
• BC范式：第一范式 + 如果关系模式R（U，F）的所有属性（包括主属性和非主属性）都不传递依赖于R的任何候选关键字。

范式分解

1. BCNF算法分解

1. 检验R是否属于BCNF。如果是，不需要做任何事，返回{R}作为结果。
2. 如果存在BCNF违例，假设为X→Y。计算X+。选择R1=X+作为一个关系模式，并使另一个关系模式R2包含属性X以及那些不在X+中的属性。
3. 计算R1和R2的FD集，分别记为S1和S2。
4. 递归地分解R1和R2。返回这些分解得到的结果集合。

• 举例
  
  

2. 3NF算法分解

第九章——事务

ACID

• 原子性 :事务所有操作要么全部正确反映,要么完全不反映，即不允许事务部分地完成

  保证原子性由恢复控制子系统处理

• 一致性 :即事务对数据库的作用应使数据库从一个一致状态转变到另外一个一致状态。

  完整性约束以及编写事务的应用程序员负责

• 隔离性 :即多个事务并发执行时，应像各个事务单独执行一样，互不干扰

  保证隔离性由并发控制子系统处理

• 持久性 :即一个事务成功执行之后，对数据库的影响应该是永久的，即使数据库故障或被破坏，DBMS也应该能够恢复

  保证原子性由恢复控制子系统处理

关系

• 只有满足一致性，事务的执行结果才是正确的。
• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
• 事务满足持久化是为了能应对系统崩溃的情况。
  

问题

1. 脏读
   
2. 不可重复读
   
3. 幻读
   
4. 更新丢失（Lost Update）

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改保存其更改副本的编辑人员覆盖另一个编辑人员所做的修改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题

InnoDB的行锁模式及加锁方法

InnoDB实现了以下两种类型的行锁

• 共享锁（s）：共享锁又叫读锁，如果事务T对A加上共享锁，则其他事务只能对A再加共享锁，不能加其他锁。共享锁的事务只能读数据，不能写数据。
• 排他锁（Ｘ）：排他锁又叫写锁，如果事务T对A加上排它锁，则其他事务都不能对A加任何类型的锁。获准排它锁的事务既能读数据，又能写数据

意向锁

• 其实有排它锁和共享锁就足够了为什么还需要有意向锁，这里举一个比较形象的例子：

  在mysql中有表锁，读锁锁表，会阻塞其他事务修改表数据。写锁锁表，会阻塞其他事务读和写。

  1. Innodb引擎又支持行锁，行锁分为共享锁，一个事务对一行的共享只读锁。排它锁，一个事务对一行的排他读写锁。
  2. 这两中类型的锁共存的问题考虑这个例子：事务A锁住了表中的一行，让这一行只能读，不能写。之后，事务B申请整个表的写锁。如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

  数据库要怎么判断这个冲突呢？

  • step1：判断表是否已被其他事务用表锁锁表
  • step2：判断表中的每一行是否已被行锁锁住。

  注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。于是就有了意向锁。在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

  在意向锁存在的情况下，上面的判断可以改成

  • step1：不变
  • step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

  注意：申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。

InnoDB行锁模式兼容性列表

当前锁模式/是否兼容/请求锁模式	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加排他锁（Ｘ）；对于普通SELECT语句，InnoDB会自动给涉及数据集加共享锁（S）；

产生死锁的原因以及预防死锁的三种方法

• 原因:一个事务若申请锁未被批准,则需等待其它事务释放锁。当事务之间出现循环等待时，如果不加干预，则会等待下去，从而产生死锁
• 方法如下
  1. 要求每一个事务必须同时封锁所需要使用的全部数据
  2. 对所有数据对象规定一个封锁的次序，要求所有事务必须按照这个次序封锁对象
  3. 当事务申请锁未被批准时，不等待加锁而是让一些事务回滚重新执行

特别注意

两段锁协议是指每个事务的执行可以分为两个阶段：生长阶段（加锁阶段）和衰退阶段（解锁阶段）。

• 加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁，在进行写操作之前要申请并获得X锁。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
• 解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。
  两段封锁法可以这样来实现：事务开始后就处于加锁阶段，一直到执行ROLLBACK和COMMIT之前都是加锁阶段。ROLLBACK和COMMIT使事务进入解锁阶段，即在ROLLBACK和COMMIT模块中DBMS释放所有封锁。
  两段可以简单理解成，加锁解锁分两步走，先都加锁，然后都解锁，不要在加锁的过程中包含解锁

考试试题易错

一.概念设计

1. 在数据库中,产生数据不一致的根本原因是数据冗余
2. DBS=DBMS+DB+DBA
3. 数据库管理系统的主要功能是定义数据库
4. 数据库系统的最大特点是数据的三级抽象和二级独立
5. 数据库系统的数据独立性是指不会因为系统数据存储结构与数据逻辑结构的变化而影响应用程序
6. 信息世界中的术语，与之对应的数据库术语为记录
7. 数据冗余可能导致的问题有浪费存储空间以及修改麻烦和潜在的数据不一致
8. 用二维表结构表示实体型，外键实现实体之间联系的模型称为关系模型 【❌】

二.关系运算

1. 在关系代数运算中，五种基本运算为并、差、选择、投影、笛卡尔积
2. 一个关系模式的定义主要包括 关系名 、属性名、属性类型、属性长度 和关键字 。
3. 关系数据库中基于数学上两类运算是关系代数、关系演算
4. 

三(数据库的安全性)

1. 保护数据库安全性的一般方法是设置用户标识和存储权限控制
2. 安全性控制的一般方法有用户标识鉴定、存取控制 、审计、数据加密和视图的保护五级安全措施

四(事务,触发器)

2.

事务提交后就不能回滚了

五(E-R图)

1. 关系R的属性A参照引用关系T的属性A，T的某条元组对应的A属性值在R中出现，当要删除T的这条元组时，系统可以采用的策略包括拒绝执行、级联删除、设为空值

七(数据库设计)

1. 数据库物理设计完成后，进入数据库实施阶段，下列各项中不属于实施阶段的工作是扩充功能

2. 在设计分 E-R 图时，由于各个子系统分别有不同的应用，而且往往是由不同的设计人员设计的，所以各个分 E-R 图之间难 免有不一致的地方，这些冲突主要有属性冲突 、命名冲突、结构冲突三类。

   1. 属性冲突：属性值的类型、取值范围或者取值集合的不同
   2. 结构冲突：包括同一个对象在不同应用中的不同抽象
   3. 命名冲突：包括属性名，实体名，联系民之间的冲突

3. 在关系数据库的规范化理论中,执行“模式分解”时，必须遵守的两条规范化准则是:保持原有的函数依赖关系和具有无损连接性。

八(数据库恢复)

1. 数据库系统在运行过程中，可能会发生故障。故障主要有数据库系统在运行过程中，可能会发生故障。故障(括号后为恢复方法)主要有**事务故障(事务撤销 UNDO)、系统故障(重做REDO)、计算机病毒、介质故障(数据转储 等级日志文件)**四类。

2. 在数据恢复时,对尚未做完的事务要进行UNDO处理

4. 加锁协议中规定“先申请先服务”的原则,可避免数据库出现活锁