数据库

4前言

本文只用于顽固（复习）数据库内容，不具备原创性

概念

数据库（DateBase/DB）：数据库是长期储存在计算机内、有组织的、可共享的大量数据的集合

数据库管理系统（DataBase Management System/DBMS）：DBMS 是位于用户和操作系统之间的一层数据管理软件。数据库管理系统和操作系统一样是计算机的基础软件，也是一个大型复杂的软件系统。而它的主要任务就是科学的组织和存储数据，高效的维护和获取数据。

数据库管理系统有着以下几个功能：

数据定义功能
DBMS提供数据定义语言（DDL），用户通过它可以方便的对数据库中的数据对象的组成和结构进行定义
数据组织、存储和管理
数据操作功能
DBMS还提供数据操作语言（DML），用户可以使用它操纵数据，实现CRUD
数据库的事务管理和运行管理
保证失误的正确性，保证数据的安全性、完整性、多用户对数据的并发使用以及发生故障后的系统恢复
数据库的建立和维护功能
……

特点

数据结构化：数据库的主要特征之一，也是数据库系统与文件系统的本质区别

数据的共享性高、冗余度低且容易扩充

数据独立性高

数据由数据管理系统统一管理和控制：DBMS提供以下几方面的数据控制功能

数据的安全性保护
数据的完整性检查
并发控制
数据库恢复：DBMS必须具有数据库从错误状态恢复到某一正确状态的功能

数据模型

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

两类数据模型

根据应用目的不同，模型可以划分成两类：概念模型（按用户的观点来对数据和信息建模，主要用于数据库设计）、逻辑模型（按照计算机系统的观点对数据建模，主要用于数据库管理系统的实现）和物理模型（对数据底层的抽象，描述数据在系统内部的表示方式和存取方法，是面向计算机系统的）

PS：概念模型是第一类，逻辑模型和物理模型是第二类。

概念模型

一些概念

实体：客观存在并可相互区别的事物，比如一个学生，一门课，学生的一次选课
属性：实体所具有的特性，比如学生的身高
码：唯一标识实体的属性集，比如学生的学号，学校的代码
实体型：实体名+属性名，比如学生（学号，姓名，性别）就是一个实体型
实体集：同一类型的实体的集合，比如全体学生
联系：实体之间的联系（有一对一、一对多、多对多等多种类型），实体之间的联系可以用E-R图表示

逻辑模型和物理模型

按计算机系统的观点进行建模，主要用于数据库管理系统的实现

层次模型
网状模型
关系模型
面向对象数据模型
对象关系数据模型
半结构化数据模型

关系模型

关系模型是最重要的一种数据模型

从用户观点来看，关系模型由一组关系组成，每个关系的数据结构是一张规范的二维表。

关系模型的一些术语如下：

关系：一个关系对应一张表
元组：表中的一行即是一个元组
属性：表中的一个列名即是一个属性
码：表中的某个属性组，可以唯一确定一个元组，该属性组称为码。
域：域是一组具有相同数据类型的值的集合，比如人的年龄是1-100岁，属性的取值范围来自该属性对应的域
关系模型：对关系的描述，一般表示为关系名（属性名1，属性名2….）。比如学生（学号，姓名，年龄，性别，年纪）。关系模式必须是规范化的，不允许表中还有表，每个属性都应该是不可分的

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

主要分三种，分别是：内模式、模式、外模式

内模式

内模式也叫存储模式，一个数据库中只有一个内模式，它是数据物理结构和存储方式的描述，是数据在数据库内部的组织方式

模式

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

外模式

外模式也叫子模式或用户模式，是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示

外模式通常是模式的子集，一个模式可以有多个外模式

如果不同的用户在应用需求、看待数据的方式、对数据保密的要求等方面存在差异，则其外模式的描述就是不同的

外模式是保证数据库安全的一个有力措施。每个用户只能看见和访问对应的外模式中的数据，数据库的其余数据是不可见的。

数据库系统的组成

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

关系数据库

关系模型的数据库结构非常节点，只包含单一的数据结构——关系。

在用户看来，关系模型中的数据的逻辑结构是一张扁平的二维表

域

域是一组相同数据类型的值的集合

笛卡尔积

笛卡尔积是域上的一种集合运算，具体定义如下

关系

之前说过的，学生（学号、姓名、性别）就是一个关系

如果只有一个属性，那么就叫它为单元关系/一元关系

如果有两个属性，则为二元关系

如果关系中的某一属性组的值能够唯一地标识一个元组（其子集是不能的），则称该属性组为候选码/候选键/键
如果一个关系有多个候选码，则选定其中一个作为主码/主键
候选码的各个属性称为主属性，不包括在主属性中的其他属性叫做非主属性/非码属性
常在主键的主属性下加下划线，标出主键
如果关系中的属性或属性组不是本关系的键，而是引用其他关系或本关系的键，就称它为外键

关系的完整性

关系模型的完整性规则是对关系的某种约束条件。任何关系在任何时刻都要满足这些语义约束

实体完整性
参照/引用完整性
用户自定义完整性

实体完整性

关系数据库中的每个元组应该是可区分的、唯一的。这样的约束条件用实体完整性来保证

实体完整性规则：每个关系都应该有至少一个主题性，且主属性不能为空值。

举个例子：
选秀（学号，课程号，成绩）关系中，学号和课程号不能为空值

参照/引用完整性

参照完整性规则：外键要么是孔雀的，要么是引用实际存在的主键值。

用户自定义完整性

任何关系数据库系统都应支持实体完整性和参照完整性，除此之外，用户还可以自定义完整性约束。

关系代数

关系代数是抽象的查询语言，它用对关系的运算来表达查询。

关系代数的运算对象是关系，运算结果也是关系

关系代数用到的运算符包括两类：

集合运算符
专门的关系运算符

传统的集合运算

传统的集合运算是二目运算，包括并、差、交、笛卡尔积

并（union）

1	R U S = {t \| t ∈ R V t ∈ S}

差（except）

设有兼容关系R、S，则二者的差运算定义为：

$R-S={t|t\in R \bigwedge t\notin S}$

式中“－”为差运算符，t为元组变量，结果R-S为一个新的与R*、*S兼容的关系，该关系是由属于R而且不属于S的元组构成的集合，即在R中减去与S中相同的那些元组。

交（intersection）

关系R与关系S的交记作

$R\bigcap S={t|t\in R\bigwedge t\in S}$

其结果关系仍为n目关系，由既属于R又属于S的元组组成，关系的交可以用差来表示，即$R \bigcap S=R-(R-S)$

笛卡尔积（Cartesian product）

综合示例

专门的关系运算符

专门的关系运算包括选择、投影、连接、除运算等

选择（selection）

选择元组

举个例子：
查询学生表student中年级小于20岁的学生的所有信息

σ age<20 (Student)

投影（projection）

选择列

举个例子：
查询学生表student中都由那些系

Ⅱ Sdept (Student)

PS：投影操作会去除列中的重复行

![](F:\博客图片\数据库\20200417150502 (1).png)

连接（join）

连接也称 θ 连接。从两个关系的笛卡尔积中选取属性间满足一定条件的元组

连接运算中有两种常用连接

等值连接：θ 为 = 的连接运算称为等值连接。他是从关系R与S的笛卡尔积中选取A、B 属性值相等的那些元组
自然连接：自然连接是一种特殊的等值连接。它要求两个关系中进行比较的分量必须是同名的属性组，并且在结果中把重复的属性列去掉

示例:

在做自然连接的时候，两个关系中的某些元组可能会被抛弃，这些被舍弃的元组就称为悬浮元组

如果要把悬浮元组也留在结果中，而在其他属性上填NULL，那么这种连接就叫做外连接 outer join

左外连接 left join：只保留左表的悬浮元组
右外连接 right join：只保留右表的悬浮元组

除运算

关系数据库标准语言SQL

数据定义

从上面我们可以知道关系数据库系统支持三级模式结构，分别是模式、外模式和内模式。他们的基本对象有模式、表、视图和索引。所以SQL的数据定义功能包括模定义、表定义、视图和索引的定义。

模式的定义与删除

模式定义

1	create schema 模式名 authorization 用户名;

定义模式实际上就是定义了一个命名空间，可以在这个空间的基础上进一步定义数据库对象、基本表、视图、索引等

举个例子：
为用户ZHANG创建一个模式TEST，并且在其中定义一个表table1

create schema "TEST" authorization ZHANG
create tabel table1(col1 smallint,
                   col2 int,
                   col3 char(20));

删除模式

1	drop schema 模式名 CASCADE \| RESTRICT

CASCADE | RESTRICT 必选其一

CASCADE表示删除模式的同时会删除该模式下的所有数据库对象
RESTRICT 表示若该模式下存在数据库对象，则拒绝执行删除操作

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

1 2	数据库的创建和使用： CREATE DATABASE test; USE test;

定义基本表（CREATE）

举个例子：

CREATE TABLE mytable (
  # int 类型，不为空，自增
  id INT NOT NULL AUTO_INCREMENT,
  # int 类型，不可为空，默认值为 1，不为空
  col1 INT NOT NULL DEFAULT 1,
  # 变长字符串类型，最长为 45 个字符，可以为空
  col2 VARCHAR(45) NULL,
  # 日期类型，可为空
  col3 DATE NULL,
  # 设置主键为 id
  PRIMARY KEY (`id`)
  # 定义外键,被参照表是mytable2
  FOREIGN KEY(col1) REFERENCES mytable2(col1)
);

修改基本表（ALTER）

举个例子：

#向Student表中添加入学时间列
alter table Student add entrance_date DATE;

#将年龄的数据类型由字符型转为整数
alter table Student alter column age INT;

#增加课程名称必须取唯一值的约束条件
alter table Student add unique(cname);

删除基本表

1	DROP TABLE 表名 [RESTRICT \| CASCADE]；

一般默认是RESTRICT

索引的建立和删除

建立索引

1	create [unique][cluster] index 索引名 on 表名(列名[次序],列名[次序]...);

unique 表示此索引的每一个索引值都只对应唯一的数据记录

cluster 表示该索引是聚集索引。

举个例子：

create unique index sno_index on student(sno);
create unique index cno_index on cource(cno);
# sc表按学号升序和课程号降序建立唯一索引
create unique index sc_index on sc(sno asc, cno desc);

修改索引

1	alter index 旧索引名 rename to 新索引名

删除索引

1	drop index 索引名

数字字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息，包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQL的数据定义语句时，实际上就是在更新数据字典中的相应信息

数据查询

select 语句的一般格式

下面的例子中用的表为以下三个表：

Student(Sno,Sname,Sage,SDept)–学生表

Cource(Cno,Cname)–课程表

SC(Sno,Cno,Grade)–选修表

单表查询

单表查询是指仅涉及一个表的查询

查询表中的若干列

下面全部以举例子来说明查询语句

举个例子：
#查询全体学生的学号和姓名
select Sno, Sname from Student;

#目标列表达式 也可以是表达式
select Sno,2020 - age from Student;

#用户可以为查询的列定义别名
select Sname, 2020 - age Birthday from Student;

选择表中的若干元组

消除取值重复的行（distinct)

使用distinct 关键字去除重复记录

1	select distinct Sno from SC;

查询满足条件的行

使用where字句

比较

1	select Sname from Student where age <= 20;

确定范围

1 2	#查询年龄在20-33岁之间的学生姓名和年龄 select Sname,Sage from Student where age between 20 and 33;

确定集合

1 2	# 查询计算机系和数据系的学生姓名和性别 select sname,gender from student where dept in ('CS','Math');

字符匹配

1 2	# 查询姓欧阳且全名为三个汉字的学生的姓名 select sname from student where sname like '欧阳_';

涉及空值的查询

1 2	# 查询所有有成绩的学生姓名 select sname from student where grade is not null;

多重条件查询

and 和 or 可用来连接多个查询条件，and 的优先级高于 or，不过可以用括号来改变优先级

1 2	# 查询计算机系年龄20以下的学生姓名 select sname from student where sdept = 'CS' and sage < 20;

order by 语句

desc 降序
asc 升序（默认）

1 2	# 院系按升序排，年龄按降序排 select * from student order by sdept,sage desc;

top

# 查询成绩第一的学生姓名
select Sname from Student where Sgrade = (select top 1 Sgrade from Student order by Sgrade desc);

# 查询第21-30行的数据，id主键自增，但可能不连续
# 先查询出前20行的数据，后查询去除这20行的10行数据
select top 10 * from student where id not in(select top 20 id from student order by id
)order by id;

聚集函数

函数名	功能
COUNT	对元组计数
TOTAL	求总和
MAX	求最大值
MIN	求最小值
AVG	求平均值

举个例子：
#查询学生总人数
select count(*) from Student;

#查询选修了课程的学生人数（学生每选一门可都会在选修表中有记录）
select count(distinct Sno) as numbers from SC;

PS:起别名 as 可写可不写

group by 子句

group by 分组：把具有相同的数据值的行放在同一组中。

分组后聚集函数将作用于每一行，即每一组都有一个聚集函数数值

1
2
3

举个例子：
# 求各个课程号及相应的选课人数
select Cno, count(Sno) from sc group by Cno;

如果分组后还要进行过滤，则用HAVING语句

#查询选修了三门以上课程的学生学号
select Sno from SC group by Sno having count(*) > 3;

#有两个表Study(Sno,Cno)Student(sno,sname)，查询选修了2或3门课的学生
select * from Student s where s.sno in(select stu.sno from Study stu group by stu.Sno having count(*) >=2);

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

having 与 where 功能、用法相同，执行时机不同。

where 在开始时执行检测数据，对原数据进行过滤。

having 对筛选出的结果再次进行过滤。

having 字段必须是查询出来的，where 字段必须是数据表存在的。

where 不可以使用字段的别名，having 可以。因为执行 WHERE 代码时，可能尚未确定列值。

where 不可以使用聚集函数。一般需用聚集函数才会用 having

SQL标准要求HAVING 必须引用 GROUP BY 子句中的列或用于合计函数中的列。

GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前

1	SELECT col, COUNT(*) AS num FROM mytable where col > 2 GROUP BY col ORDER BY num;

连接查询

若一个查询同时涉及两个以上的表，则称为连接查询

等值与非等值连接查询

举个例子：

#查询每个学生及选修课的情况
select Student.*, SC.* from Student, SC where Student.Sno = SC.Sno;

#查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
select Student.Sno,Sname from Student, SC where Student.Sno = SC.Sno and SC.Cno = 2 and SC.Grade >= 90;

自身连接

一个表与自己进行连接

1	select FIRST.Cno, SECOND.Cpno from Course FIRST, Course SECOND where FIRST.Cpno = SECOND.Cno;

外连接

保存悬浮元组（即不满足条件的元组也要保存下来）

outer join
left outer join
right outer join

多表连接

两个以上的表进行连接

举个例子：

#查询每个学生的学号，姓名，选修的课成名即成绩
select Student.Sno,Sname,Cname,Grade from Student,Course,SC where Student.Sno = SC.Sno and SC.Cno = Course.Cno;

嵌套查询

在SQL语言中，一个select-from-where语句称为一个查询块。将一个查询块套在另一个查询块的where子句或者having短句的条件中的查询称为嵌套查询

带有in谓词的子查询

举个例子：

#查询与小明所在同一个系的学生
select Sno,Sname,Sdept from Student where Sdept in (select Sdept from Student where Sname = '小明');

带有比较运算符的子查询

举个例子：

#找出每个学生超过他自己选修课程平均成绩的课程号
select Sno,Cno from SC x where Grade > =(select AVG(Grade) from SC y where y.Sno = x.Sno);

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

子查询返回单值时可以用比较运算符，但返回多值时要用ANY（用的系统用SOME）或ALL谓词修饰符。而使用ANY或ALL谓词时必须同时使用比较运算符

举个例子：

#查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄
select Sname,Sage from Student where Sage < ANY(select Sage from Student where Sdept = 'CS') and Sdept !='CS';

带有EXISTS谓词的子查询

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

exists 引导的内层查询如果能查出数据，则继续外层查询
not exists 引导的内层查询如果查不出数据，则继续外层查询

exists的查询步骤是顺序执行，并不会先做子查询，与in相反。

顺序执行，如果exists的查询结果为真，则将最外层的查询结果添加进最终结果集。对外表进行循环

举个例子：

#查询所有选修了1号课程的学生姓名
select Sname from Student where exists(select * from SC where Sno = Student.Sno and Cno = 1);

由exists引出的子查询，其目标列表达式通常都用 * ，因为该子查询只返回 true 或 false，给出列名无实际意义

举个例子：

#查询选修了全部课程的学生名字
select Sname from Student where exists(
    #首先查询一共有哪些课程
    select * from Course where not exists(
    #其次，我们需要统计选修了所有课程的学生号
    select * from SC where Sno = Student.Sno and Cno = Course.Cno)
);

由于没有全程量词，可将题目的意思转化为没有一门课程是他不选修的

exists 可以理解为一个循环

for(循环从Student表拿一行学生数据){
　　for(循环从Course表拿一行课程信息){
　　　　for(循环在SC表拿一行进行比对){
　　　　　　SC表中的这条数据判断：
　　　　　   SC.Sno == Student.Sno ， SC.Cno == Course.Cno;
　　　　　　/*是否SC表中的学号 = Student表中的学号 且
　　　　　　　SC表中的Cno = Course表中的Cno*/
　　　　}
　　}
}

举个例子：

#查询至少选修了学生001选修的全部课程的学生号码

select distinct Sno 
#代表学生X的表
from SC SCX
where not exists(
select * from SC SCY
where SCY.Sno = '001'
and not exists(
select * from SC SCZ
#匹配学号
where SCZ.Sno = SCX.Sno
and
#001选修了该课程
SCZ.Cno = SCY.Cno)
);

翻译为：不存在这样的课程y，001选修了，而学生x没有选修

exists 和 in 的区别：

例如：

select * from A where id in(select id from B)

exists()适合B表比A表数据大的情况

当A表数据与B表数据一样大时, in与exists效率差不多,可任选一个使用.

集合查询

select 语句的查询结果是元组的结合，所以多个select语句的查询结果可进行集合操作。集合操作主要包括

并 UNION
交 INTERSECT
差 EXCEPT

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

举个例子：

#查询计算机系的学生以及年龄不大于19的学生
select * from Student where Sdept = 'CS' UNION select * from Studnet where Sage <= 19;

#也可以使用INTERSECT
select * from Student where Sdept = 'CS' INTERSECT select * from Student where Sage <= 19;

#还可以使用EXCEPT
select * from Student where Sdept = 'CS' EXCEPT select * from Student where Sage <= 19;

基于派生表的查询

子查询不仅可以出现在where子句中，还可以出现在子句中，这时子查询生成的临时派生表成为主查询的查询对象 (必须为派生关系置顶一个别名)

举个例子：

#找出每个学生超过自己选修课程平均成绩的课程号
select Sno,Cno from SC,(select Sno, AVG(Grade) from SC group by Sno) as AVG_SC(avg_sno,avg_grade);

数据更新

插入数据

插入元组

1
2
3

举个例子：

insert into Student(Sno,Sname,Sgender,Sdept,Sage) values('123','小红','男','CS',20);

若不指出要添加的属性，则需要添加表中的所有属性

插入子查询结果

举个例子：

#对每一个系，求学生的平均年龄，并把结果存入表Dept_age
insert into Dept_age(Sdept,Avg_age) select Sdept,AVG(Sage) from Student group by Sdept;

修改数据

修改某个元组的值

举个例子：

#修改学号001的年龄
update Student set Sage =15 where Sno = '001';

修改多个元组的值

举个例子：

#将所有学生年龄+1
update Student set Sage = Sage + 1;

带子查询的修改语句

举个例子：

#将计算机系全体学生成绩置0
update Student set Sgrade = 0 where Sno in(select Sno from Student where Sdept = 'CS');

#实际上我觉得可以这样写，还能更简便，但不知道为什么作者没有这样写
update Student set Sgrade = 0 where Sdept = 'CS';

删除数据

删除某个元组

举个例子：

#删除学号001学生记录
delete from Student where Sno = '001';

删除多个元组

举个例子：

#删除所有学生记录
delete from Student;

带子查询的删除语句

举个例子：

#删除计算机系所有学生的选课记录
delete from SC where Sno in(select Sno from Student where Sdept = 'CS');

#删除重复数据，只保留一条记录（除id意外，其他全部相同）
-- ② 删除除了分组中最小id意外的所有值，即重复数据--
delete from Student where id not in (select id from(
-- ① 按照除id以外的任意属性就行分组排列，并选出每个分组中的最小id -- 
select MIN(id) from Student group by Sname
)temp);

1
2
3
4
5
delete from test 
where id not in(
    select Min(id) from test 
    group by name
);
这样写会在MySQL中报错，You can't specify target table for update in FROM clause

不允许使用同一表中查询的数据作为同一表的更新数据。

所以我们需要在select外面套上一层，让数据库认为我们不是使用同一个表的查询数据作为更新数据

空值的处理

空值的产生

比如：插入语句中没有赋值的属性，其值为空值

举个例子：

insert into Student(Sno,Cno)
values('123','323');
# 除了Sno,Cno 外其余属性就是空值

空值的判断

is null/is not null

举个例子：

#查询漏填信息的学生
select * from Student where Sname in null or Sgender is null or Sage is null or Sdept is null;

空值的约束条件

属性定义中有 NOT NULL 约束条件时不能取空值
加了 UNIQUE 限制的属性不能取空值
主键不能取空值

视图

视图是从一个或几个基本表（或视图）导出的表。

它与基本表不同，是一个虚表。

数据库中只存放视图的定义，不存放视图对应的数据，这些数据任然存放在原来的基本表中。所以一旦基本表中的数据变化，那么视图中的数据也会相应变化。

其实视图就好像一个窗口，透过它可以看到自己想要看到的数据及其变化

建立视图

建立在单个表上的视图

举个例子：

#建立计算机系学生视图，并要求插入/修改/删除操作时，保证该视图只有计算机系学生
create view CS_Student as select * from Student where Sdept = 'CS' with check option;

由于加上了 with check option 子句，以后对视图进行修改 / 添加 / 删除操作时，DBMS都会自动加上 Sdept = ‘CS’ 这个条件

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

建立在多个表上的视图

举个例子：

#建立计算机系选修了1号课程的学生的视图（包括学号、姓名、成绩）
create view CS_S1(View_Sno,View_Sname,View_Grade) as select Student.Sno,Sname,Grade from Student,SC where Student.Sno = SC.Sno and Sdept = 'CS' and SC.Cno = '1';

由于视图的属性列中包含了两个表的同名列 Sno，所以必须在视图名后面说明视图的各个属性列名

建立在视图上的视图

举个例子：

#建立计算机系选修了1号课程且成绩在90分以上的学生的视图
create view CS_S2 as select View_Sno, View_Sname,View_Grade from CS_S1 where View_Grade >= 90;

删除视图

1	drop view 视图名

若该视图上还导出了一个视图，则删除视图操作拒绝执行

或者可以使用 CASCADE进行级联删除，删除该视图和由它导出的所有视图

1	drop view 视图名 CASCADE;

查询视图

视图的查询和表的查询是一样的

举个例子：

#建立计算机系学生视图，并要求插入/修改/删除u操作时，保证该视图只有计算机系学生
create view CS_Student as select * from Student where Sdept = 'CS' with check option;

#查询选修了1号课程的计算机系学生
select CS_Student.Sno,Sname from CS_Student,SC where CS_Student.Sno = SC.Sno and SC.Cno = '1';

更新视图

更新视图和更新表操作基本一致，不过有些时候视图是不允许更新的。

视图的优点

视图能够简化用户的操作
视图使用户能以多种角度看待同一数据
视图对重构数据库提供了一定数据的逻辑独立性

数据的逻辑独立性是指当数据库数据库构造时，如增加新的关系或对原来关系增加新的字段等，用户的应用程序不会受到影响
视图能够对机密数据提供安全保护
适当利用视图可以更清晰的表达查询

视图的缺点

查询视图时，必须把对视图的查询转化为对基本表的查询。如果这个视图是由一个复杂的多表查询所定义，那么即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。
当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改，如果视图涉及多个表的话，由于完整性约束，可能是无法修改的

数据库的安全性和完整性

安全性

什么是数据库的安全性

数据库的安全性是指保护数据库以防止不合法使用所造成的数据泄露、更改或破坏。

对数据安全性产生威胁的因素主要有以下几个方面

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

数据库安全性控制

数据库有关的安全性控制主要包括用户身份鉴别、多层存取控制、审计、视图和数据加密等技术

用户身份鉴别

用户身份鉴别是数据库管理系统提供的最外层安全保护措施，每个用户在系统中都有一个用户标识，每个用户标识由用户名和用户标识号UID两部分组成。UID在系统的整个生命周期中是唯一的。系统内部记录着所有合法用户的标识。

每个用户要求进入系统时，由系统进行核对，通过鉴定后才提供使用数据库管理系统的权限。

用户身份鉴别的方法主要有以下几种：

静态口令鉴别

静态口令一般由用户自己设定，鉴别使输入正确口令即可获得权限
动态口令鉴别

每次鉴别时均需使用动态产生的新口令登录数据库管理系统。比如短信验证码登录
生物特征鉴别

比如指纹、虹膜鉴别
智能卡鉴别

智能卡由用户随身携带，插入专用的读卡器进行身份验证

存取控制

数据库安全最重要的一点就是确保只能有资格的用户授予访问权限，这主要通过存取控制机制实现。存取控制机制主要包括定义用户权限和合法权限检查两部分

权限给予和收回

GRANT 授予权限

不允许循环授权，即被授权者不能把权限再授回授权者或者其祖先

举个例子：

#把查询Student表的权限授给用户User1，并允许他将此权限授予其他用户
grant select on table Student to User1 with grant option;

#把对student表和cource表的全部操作权限授予用户User2和User3
grant all privileges on table Student,Cource to User2,User3;

#把对表SC的查询权限授予所有用户
grant select on table SC to public;

#把查询student表和修改学生学号的权限授予用户User4
grant update(Sno),select on table Student to User4;

REVOKE 收回权限

举个例子：

# 收回Uer4修改学生学号的权限
revoke update(Sno) on table Student from User4;

# 收回用户User1对Student表的查询权限，并级联收回User1授予的其他用户的该权限
revoke select on table Student from User1 CASCADE;

视图机制

还可以为不同的用户定义不同的视图，把要保密的数据对无权存取的用户隐藏起来，从而自动对数据提供一定程度的安全保护。

举个例子：

#建立计算机学生的视图，并把对该视图的select权限授予User1,对该视图的所有操作权限授予User2
create view CS_Student as select * from STudent where Sdept = 'CS';

grant select on CS_Student to User1;

grant all privileges on CS_Student to User2;

审计/跟踪审查

审计功能把用户对数据库的所有操作自动记录下来放入审计日志（audit log)中。审计员可以利用审计日志监控数据库中的各种行为，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容等

AUDIT 设置审计功能

举个例子：

#对修改Student表结构和修改Student表数据的操作进行审计
AUDIT alter,update on Student;

NOAUDIT 取消审计功能

举个例子:

#取消取Student 表的一切审计
noaudit alter,update on Student;

数据加密

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

完整性

什么是数据库的完整性

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

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

例如：学生的学号必须唯一，性别只能是男或女等等

为了维护数据库的完整性，DBMS必须提供如下功能

提供定义完整性约束条件的机制
提供完整性检查的方法
进行违约处理

完整性和安全性的区别

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

因此完整性检查和控制的防范对象是不合语义的、不正确的数据，防止它们进入数据库；
数据的安全性是保护数据库防止恶意破坏和非法存取。

因此安全性控制的防范对象是非法用户和非法操作，防止他们对数据库数据的非法存取

实体完整性

主键必须存在且不为空

定义实体完整性

create tabel Student(
	Sno char(9) primary key,
	Sname char(20) not null,
	Sex char(2)
	);
	
举个例子：

#将SC表中的Sno，Cno属性组定义为主键
create tbale SC(
	Sno char(9) not null,
	Cno char(9) not null,
	Grade smallint,
	primary key(Sno,Cno)
	);

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

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

参照完整性

外键要么不存在，要么存在就不为空

定义参照完整性

create table SC(
	Sno char(9) not null,
	Cno char(9) not null,
	Grade smallint,
	primary key (Sno,Cno),
	foreign key(Sno) references Student(Sno),
	foreign key(Cno) references Student(Cno)
);

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

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

拒绝执行 NO ATION：默认策略
级联操作 CASCADE

当删除或修改被参照表的一个元组导致与参照表的不一致时，删除或修改参照表中的所有导致不一致的元组。

例如：删除Student表中001学生，则SC表中关于001的记录也全部删除
设置为空值

当删除或修改被参照表的一个元组导致与参照表的不一致时，将不一致的属性设置为空值

用户定义完整性

属性上的约束条件

当不满足属性约束条件的时候，操作将被拒绝执行

不允许空值

create table SC(
	Sno char(9) not null,
	......
	);

列值唯一

create table Dept(
	Deptno numeric(2),
	Dname char(9) unique not null,//列值唯一且不能为空
	......
	);

用check短语指定列值应该满足的条件

create table Student(
	Sno char(9) primary key,
	Ssex char(2) check(Ssex in ('男','女')),//性别属性只能取男或女
	Grade smallint check(Grade >= 0 and Grade <= 100),//分数属性只能取值0-100
	......
	);

元组上的约束条件

元组级的约束可以设置不同属性之间的相互约束条件。

当不满足这些约束条件的时候，操作将被拒绝执行

create table Student(
	Sno char(9),
	Sname char(9) not null,
	primary key(Sno),
	check(Ssex = '女' or Sname not like 'MS.%')//性别是女或者名字不以MS.%开头则可以通过check检查
	)；

命名完整性约束Constraint

完整性约束命名

constraint 命名完整性约束，方便增加和删除一个完整性约束条件

1	格式：constrain 完整性约束条件名完整性约束条件

完整性约束条件包括

not null
unique
primary key
foreign key
check

举个例子：

Create table Student(
	Sno numberic(9)
		constraint c1 check(Sno between 100-10000),
	Sname char(9)
		constraint c2 not null,
	Sage numeric(3)
		constraint c3 check(Sage < 30),
	constrain StudentKey primary key(Sno)
	);

修改表中的完整性约束

举个例子：

#去除对年龄小于30的约束
alter table Student
	drop constraint c3;
	
#添加年龄小于40的约束
alter table Student
	add constraint c4 check(Sage < 40);

断言ASSERTION

关键词：ASSERTION

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

举个例子：

#限制数据库课程最多60名学生选修
create assertion asse_sc_db_num
	check(60 >= (select count(*)
	from Course,SC
	where SC.Cno = Course.Cno and
	Couce.Cname = '数据库')
	);

触发器Trigger

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

创建触发器

触发器仅限于数据库中增、删、改三种操作

触发器的定义如下:

create trigger 触发器名 
    before/after 触发事件 /*指明触发器的激活时间*/
    on 表名 /*触发器只能定义在基本表上，不能定义在视图上*/
    [referencing 引用名] 可选的
    for each row/statement /*定义触发器的类型，指明动作体执行的频率*/
    when SQL语句
    动作

before/after：触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

触发事件：

insert：触发器包含一个名为 NEW 的虚拟表。
delete ：触发器包含一个名为 OLD 的虚拟表，并且是只读的。
update：触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

也可以是 update of < 触发列名1，触发列名2 ... >

触发器事件既然是数据库更新操作，这些操作的执行势必会引起数据库中某些值的改变，即由旧值变成新值，这些新旧值称为过渡值。在触发器的条件和动作中可以引用这些过渡值

OLD【ROW】AS 旧元组别名 （row旧元组名是可选的）
NEW【ROW】AS 新元组别名
OLD TABLE AS 旧表别名
NEW TABLE AS 旧表别名

触发器类型：

for each row：行级触发器
for each statement : 语句级触发器

比如修改一个Teacher表中的deptno字段（一共1000条记录）

update teacher set deptno = 5;

若是行级触发器，update后触发动作执行一次

若是语句级触发器，触发动作将执行1000次

举个例子：

#cource表中删除一个元组，若该元组的主键是sc表中的外键，则卷回删除该元组的操作。
CREATE TRIGGER mytrigger 
BEFORE DELETE ON cource  
referencing old as o 
for each row
when (exists (select * from sc
             where cno = o.cno))
ROLLBACK;

删除触发器

1	DROP TRIGGER 触发器名

触发器实现参照完整性

比如有三个表：student（学生表），cource（课程表），sc（选修表），其中sc定义了两个外键sno和cno以及其完整性约束，试写出触发器实现该参照完整性约束的规则

首先分析：有哪些操作会影响到本例的完整性约束

sc 表的 insert 操作
cource 表的 delete 操作
student 表的 delete 操作
sc 表的 update（sno, cno) 操作
cource 表的 update（cno) 操作
student 表的 update（sno) 操作

对上述6中操作分别定义6条规则，实现参照完整性约束

规则1
create trigger referential_integrity_check
before insert on sc
referencing new as n
when (not(exists(select * from student
                where sno = n.sno)
         and
         exists(select * from cource
               where cno = n.cno)
         )
     )
rollback;

如果 sc 表中插入元组，其外键在 student 和 cource 表中均不存在，则卷回插入该元组操作

规则2
create trigger cource_delete
before delete on cource
referencing old as o
for each row
when (exists(select * from sc
            where o.cno = sc.cno)
     )
rollback;

如果 cource 表中删除一个元组，若该元组是 sc 表中的外键，则卷回删除该元组的操作（此处我们假定在sc表的定义中，外键 cno 使用了 restrict 选项）

规则3

create trigger student_delete
before delete on student
referencing old as o
for each row
when(exists(select * from sc
           where sc.sno = o.sno)
    )
delete from sc
where sc.sno = o.sno;

假设在 sc 表的定义中，外键 sno 的定义中采用了 cascade 选项，即当在 student 表中删除一个元组的时候，则在 sc 表中删除引用该元组主键作为外键的所有元组

规则4
create trigger sc_fk_update
before update of sno,cno on sc
referencing new as n
for each row
when(not(exists(select * from student
               where sno = n.sno)
        and
        exists(select * from cource
              where cno = n.cno)
        )
    )
rollback;

对于 sc 表的更新操作，若更新的外键 sno 或者 cno 在 student 和 cource 表中无相应的主键供其引用，则卷回更新该元组的操作

规则5

create trigger cource_cno_update
before update of cno on cource
referencing old as o
for each row
when(exists(select * from sc
               where sc.cno = o.cno)
     )
    
rollback;

对于 cource 表的更新操作，在修改主键cno的同时，如果sc表中有元组正引用修改前的cno值作为外键，则卷回该操作

规则6
create trigger student_sno_update
before update of sno of student
referencing old as o
for each row
when (exists(select * from sc
            where sc.sno = o.sno)
     )
rollback;

对于 student 表的更新操作，在修改主键sno的同时，如果sc表中有元组正引用修改前的sno值作为外键，则卷回该操作

关系数据库设计理论

异常

不符合范式的关系，会产生很多异常，主要有以下四种异常：

数据冗余：同一个数据出现了两次
更新异常：修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改。
删除异常：删除一个信息，那么也会丢失其它信息。
插入异常：例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入。

数据依赖是一个关系内部属性和属性之间的一种约束关系。这种约束关系是通过属性间值的相等与否体现出来的数据间的相关联系。其中最重要的是函数依赖和多值依赖。

一个模式的数据依赖会有哪些不好的性质，如何改造一个不好的模式，这就是规范化要讨论的内容

规范化

规范化的目的

关系数据库进行规范化的目的：使结构更合理，解决数据中可能出现的异常情况（比如数据冗余、更新异常、删除异常、插入异常），从而增强数据的稳定性和灵活性
关系模式进行规范化的原则：遵从概念单一化“一事一地”原则，即一个关系模式描述一个实体或实体间的一种联系。规范的实质就是概念的单一化。
关系模式进行规范化的方法：将关系模式投影分解成两个或两个以上的关系模式。

函数依赖（functional dependency，FD）

概念

记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。

在一个关系中，任意元组，若属性 A1,A2….An 一样，则属性 B1,B2…Bm 必一样，那么称 A1,A2…An 函数决定 B1,B2…Bm。

记号为 A1,A2...An → B1,B2...Bm Ai与Bi有函数依赖）

如果 {A1，A2，... ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小集合，那么该集合就称为键码。

对于 A->B，如果能找到 A 的真子集 A’，使得 A'-> B，那么 A->B 就是 部分函数依赖，否则就是 完全函数依赖。
对于 A->B，B->C，则 A->C 是一个传递函数依赖。

范式理论

关系数据库中的关系是要满足一定要求的，满足不同程度要求的为不同范式。

范式理论是为了解决以上提到四种异常。

高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

一个低一级的范式的关系模式通过模式分解可以转换为若干个高一级的关系模式的集合，这个过程就叫 规范化（normalization）

第一范式（1NF）

定义：

属性不可分。可以认为任何表都属于第一范式，因为每个表的最小单位为表中的各个属性。

第二范式（2NF）

定义：

在满足第一范式前提下，在所有函数依赖表达式中，不存在任何候选键的真子集决定非主属性。即消除非主属性对于主键的部分依赖，使得非主属性完全依赖于主键

一个关系模式不符合 2NF 定义，会导致如下问题

插入异常
删除异常
修改复杂

修正的第三范式（BCNF）

定义：在满足第二范式的条件下，消除所有属性对主属性的传递依赖。即如果一个属性/属性组 A 决定其他属性/属性组B，则 A 必须包含主键

关系模式 R 属于 3NF，但 R 不一定属于 BCNF

多值依赖

概念

范式理论

4NF

4NF就是限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖

规范化总结

规范化的思想是逐步消除数据依赖中不合适的部分，使模式中的各关系模式达到某种程度的分离。

数据库设计之E-R图

P.P.S.Chen 提出的 E-R 模型使用 E-R 图来描述现实世界的概念模型。E-R 模型涉及的主要概念包括实体、属性、实体之间的联系等

实体 entity：客观存在并可相互区别的事物，比如一个学生，一门课，学生的一次选课
属性：实体所具有的特性，比如学生的身高
码：唯一标识实体的属性集，比如学生的学号
实体型：实体名+属性名，比如学生（学号，姓名，性别）就是一个实体型
实体集：同一类型的实体的集合，比如全体学生
联系 relationship：实体之间的联系（有一对一，一对多，多对多等多种类型）

实体之间的联系

两个实体6-型之间的联系

总要分为以下三种：

一对一联系 1：1

例如一个班级一个只有班长，班长和班级之间具有一对一联系

一对多联系 1：n

例如一个班级中有若干个学生，且每个学生只在一个班级中学习，班级与学生之间具有一对多联系

多对多联系 m:n

例如一门课程同时有若干个学生选修，而一个学生可以选修多个课程，则课程和学生之间具有多对多联系

两个以上实体型之间的联系

两个以上实体型之间的联系也存在一对一，一对多，多对多的联系

例如，对于课程，教师，参考书三个实体型，一门课程可以有若干个教师讲授，使用若干本参考书，而每一个教师只讲授一门课程，每一本参考书只供一门课程使用，则课程与教师、参考书之间的联系是一对多的

单个实体型内的联系

同一个实体型内的各实体之间也存在一对一、一对多、多对多的联系。

例如：职工实体型内部具有领导和被领导的联系，即某一职工领导若干名职工，而一个职工仅被另外一个职工直接领导，因此这是一对多的联系

E-R图

基本表示方法

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

实例

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

转换的一般原则

一个实体型转换为一个关系模式
- 关系的属性就是实体的属性
关系的码就是实体的码
一个 1：1 联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并
一个 1：n 联系可以转换为一个独立的关系模式，也可以与 n 端对应的关系模式合并
一个 m：n 联系可以转换为一个独立的关系模式
- 关系的属性：与该联系相连的各实体的码以及联系本身的属性
- 关系的码：各实体型码的组合
三个或三个以上实体间的一个多元联系可以转换为一个关系模式
- 关系的属性：与该多元联系相连的各实体的码以及联系本身的属性
- 关系的码：各实体码的组合
具有相同码的关系模式可合并

目的：减少系统中的关系个数

实例

一个实体型转换为一个关系模式：

供应商（供应商号，姓名，地址，电话号，账号）

项目（项目号，预算，开工日期）

零件（零件号，名称，规格，单价，描述）

仓库（仓库号，面积，电话号）
三个或三个以上实体间的一个多元联系可以转换为一个关系模式：

供应（供应商号，项目号，零件号，供应量）
一个 1：n 联系可以转换为一个独立的关系模式，也可以与 n 端对应的关系模式合并

职工（职工号，姓名，年龄，职称，仓库号，领导职工号）
一个 m：n 联系可以转换为一个独立的关系模式

库存（仓库号，零件号，库存量）

查询处理和优化

查询处理

查询处理是数据库管理系统把用户提交上来的查询语句转换成高效的查询执行计划。

关系数据库管理系统查询处理可以分为4个阶段：

查询分析
查询检查
查询优化
查询执行

查询分析

首先对查询语句进行扫描、语法分析和词法分析。从查询语句中识别出语言符号，如SQL关键字、属性名和关系名等，进行语法检查和语法分析，判断查询语句是否符合SQL语法规则

查询检查

对合法的查询语句进行语义检查，即检查数据库对象，如关系名、属性名是否存在和有效。

还要根据用户权限和完整性约束定义对用户的存取权限进行检查。如果用户没有相应权限或者违反了完整性约束，就拒绝执行该查询。

检查过后将SQL查询语句转成内部表示即等价的关系代数表达式，一般用 查询树 / 语法分析树 来表示扩展的关系代数表达式

查询优化

查询优化就是优化器选择一个高效执行的查询处理策略，以获得最好的查询优化效果

按照优化的层次分为代数优化和物理优化

查询执行

根据优化器得到的执行策略生成查询执行计划，由代码生成器生成执行这个查询计划的代码，然后加以执行，并返回查询结果

实现查询操作的算法

选择操作的实现

全表扫描算法 table scan

适用于规模较小的表

对于大规模的表，当选择率较低时，这个算法的效率很低

索引扫描算法 index scan

如果选择条件中的属性上有索引，可以用索引扫描算法，通过索引先找到满足条件的元组指针，再通过元组指针在查询的基本表中找到元组

连接操作的实现/多表连接

以下面这条SQL语句为例

1	select * from Student,SC where Student.Sno = SC.Sno;

嵌套循环算法 nested loop

这是最简单可行的算法

取 Student 表的一个元组，与 SC 表的所有元组进行比较，凡满足连接条件的元组就进行连接并且作为结果输出
然后再取 Student 表的下一个元组，与 SC 的所有元组比较，直至 Student 表的所有元组与 SC 表的所有元组比较完毕为止

排序-归并算法 sort-merge

等值连接常用的算法，如果Student表和SC表已经按连接属性排好序了，则可按序比较两个表的连接属性，找出匹配的所有元组。

核心思想：分别从两个表中取出一行元组进行比较，如果匹配就连接起来放入结果集；如果不匹配，将较小的那个元组丢弃，继续匹配这个表的下一行，依次处理直到将两表的数据取完。

如果 Student 表和 SC 表在做连接操作之前没有按连接属性进行排序，则我们需要事先为之排序，由于排序是开销很大的操作，在此情况下是否值得使用排序归并法，那就需要权衡了。

索引连接算法 index join

在SC表上已经建立了Sno的索引
对Student中的每一个元组，在SC表中通过Sno的索引查找对应的SC元组，把相匹配的两个表中的元组连接起来。循环执行，直到Student表扫描结束

散列连接算法 hash join

此方法仅Oracle支持，MySQL不支持

用来处理等值连接。把连接属性作为hash的value，用同一个hash函数把Student表和SC表中的元组散列到hash表中。

创建阶段：创建hash表，对包含较小元组的表进行处理，把他的元组按hash函数分散到hash桶中（采用拉链法）
连接阶段：对另一个表进行hash。并把这个表中元组和上一个表中相匹配的元组（同义词）连接起来。如果一个桶中只有Student或者SC的元组，则不进行连接

查询优化

每个查询都会有许多可供选择的执行策略和操作算法，查询优化就是选择一个高效优化的查询处理策略。

查询优化的优点不仅在于用户不必考虑如何最好的表达查询以获得较高的效率，而在于系统可以比用户程序的优化做的更好

代数优化

代数优化就是通过对关系代数式的等价边换来提高查询效率

代数优化改变的是查询语句中操作的次序和组合，但不涉及底层的存取路径

最常用的优化原则是尽量缩减查询过程中的中间结果。由于选择、投影等一元操作分别从水平或垂直方向减少关系的大小，而连接、并等二元操作不但操作本身的开销较大，同时很可能产生大的中间结果。因此在做查询优化时，总是先做选择和投影，然后在做连接等二元操作。在连接时也是先做小关系之间的连接，再做大关系之间的连接。

常见的对关系表达式进行查询优化的方法有：

选择运算尽可能先做
若投影运算和选择运算都是对同一个关系进行操作，则将投影运算和选择运算同时进行
把投影同其前或后的双目运算符结合起来
把某些选择同在它前面要执行的笛卡尔积结合起来成为一个来连接运算（连接，特别是等值连接，要比同样关系上的笛卡尔积省很多时间）
找出公共子表达式（比如查询视图的时候，定义视图的表达式就是公共子表达式）

物理优化

物理优化就是选择高效合理的操作算法或者存取路径来达到查询优化的目标

选择的方法如下：

基于规则的启发式优化
基于代价估算的优化：选择代价最小的执行计划
两者结合的优化方法

基于跪着的启发式优化

启发式优化：在大部分情况下使用，但不是在所有情况下都是最好的规则

对于选择操作的启发式规则：

对于小关系，使用全表顺序扫描，即使选择列上有索引
对于大关系，启发式规则有：
- 选择条件是主键=值，采用逐渐索引
- 选择条件是非主属性=值，并且选择列上有索引，估算查询结果的元组数目，如果比列较小，可以使用索引，否则仍然使用全表顺序扫描
- 选择条件是非等值查询或范围查询，并且选择列上有索引，估算查询结果的元组数目，如果比列较小，可以使用索引，否则仍然使用全表顺序扫描
- 使用AND连接的合取选择条件，如果有涉及这些属性的组合索引，则优先使用索引，否则使用全表顺序扫描
- 对于OR连接的析取选择条件，一般使用全表顺序扫描

对于连接操作的启发式规则：

如果两个表都已经按照连接属性排序，则使用排序-合并算法
如果一个表在连接属性上有索引，则使用索引连接算法
如果上面两个规则不适用，且其中一个表较小，则使用hash join算法
最后可以使用嵌套循环算法

基于代价估算的优化

基于代价的优化方法要计算各种操作算法的执行代价，它与数据库的状态密切相关。为此在数据字典中存储了优化器需要的统计信息，主要包括以下几个方面

事务处理

事物处理技术包括两方面

数据库恢复技术
并发控制技术

事务的概念

事务

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

一个程序包含多个事务

最经典的例子就是转账了

假如小明要给小红转账1000元，这个转账会涉及到两个关键操作

1：小明的余额减少1000元

2：小红的余额增加1000元

但如果在这两个操作之间突然出现问题，如银行系统崩溃，导致小明余额减少而小红的余额没有增加，就出大问题了。

事务就是要保证这两个关键操作必须要么都成功，要么都失败。

事务的开始和结束都可以由用户显示控制，在SQL中，定义事务的语句有3条

BEGIN TRANSACTION; ：事务以此语句开始
COMMIT; : 提交事务的所有操作
ROLLBACK;：回滚

一般事务都以commit 或者 rollback 结束

事务的ACID特性

原子性（Atomicity） :事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚
一致性（Consistency） :数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对同一个数据的读取结果都是相同的。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的
隔离性（Isolation） 一个事务的执行不能被其他事务干扰，即一个事务的内部操作即使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰
持久性（Durability） 一旦事务提交，则其所做的修改将会永远保存到数据库中。接下来的操作和故障不应该对其执行结果有任何影响

事务的ACID特性遭到破坏的因素

事务是恢复和并发控制的基本单位，保证事务ACID特性是事务管理的重要任务，事务ACID特性可能遭到破坏的因素有：

多个事务并发执行，相互干扰
事务在运行过程中被强行终止

数据库恢复技术作用

数据库恢复技术就是把数据库从错误状态恢复到某一已知的正确状态

数据恢复技术是衡量系统性能优劣的重要指标

故障的种类

事务内部的故障

事务内部的故障更多是非预期的，不能由应用程序处理的故障。一般我们苏哦说的事务故障都是指这类非预期的故障。

事务故障意味着事务没有到达预期的终点（commit 或者 rollback）因此，数据库可能处于不正确的状态。

恢复程序要在不影响其他事务运行的情况下，强行回滚该事务，即撤销该事务已经做出的任何对数据库的修改。这类恢复操作称为 事务撤销 UNDO

系统故障

系统故障是指造成系统停止运转的任何时间，使得系统要重新启动。

特定类型的硬件错误（CPU故障）、操作系统故障、DBMS代码错误、系统断电等，这些故障都会影响正在运行的所有事务，但它不会破坏数据库。

此时主存内容，尤其是数据库缓冲区中的内容都被丢失，所有运行事务都非正常终止。发生系统故障时，一些尚未完成的事务的结果可能已送入物理数据库，从而造成数据库可能处于不正确的状态。为保证数据库的一致性，需要清除这些事务对数据库的所有修改。

所以系统重新启动后，恢复子系统除需要撤销所有未完成的事务外，还需要重做(REDO)所有已提交的事务，以将数据库真正恢复到一致状态

介质故障

系统故障称软故障，介质故障称硬故障

同时硬故障也指外存损坏

恢复的实现技术

数据转储

数据转储就是管理员定期的将整个数据库复制到磁带、磁盘或其他存储介质上。这些备用的数据称为后备副本 backup

重装后备副本只能将数据库恢复到转储时的状态，其之后的事务操作都必须重新执行一遍才能恢复到故障发生时的状态

但是转储十分耗时，不能频繁进行

登记日记文件

日志文件中需要登记的内容包括：

各个事务的开始标记
各个事务的结束标记
各个事务的更新操作

登记日志文件时必须遵循两条原则：

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

并发控制

并发事务带来的一些问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。

脏读（Dirty read）

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是 “脏数据” ，依据 “脏数据” 所做的操作可能是不正确的。（T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。）

丢失修改（Lost update）

指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。（T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。）

不可重复读（no-repeatable read）

一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。（T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同）

幻读（Phantom read）

幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。（T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同）

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了）：

事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样）：

假如某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读。

封锁

并发控制的主要技术有封锁 locking、时间戳 timestamp、乐观控制法 optimistic scheduler 和多版本控制 MVCC 等

封锁是众多数据库产品采用的基本方法

所谓封锁就是事务T在对某个数据对象例如表、记录等操作之前，先向系统发出请求，对其加锁，在事务T释放它的锁之前，其他事务不更新此对象

确切的控制由封锁的类型决定

封锁类型

基本的封锁类型有两种：排他锁 X 锁和共享锁 S锁

排他锁——X锁/写锁

一个事务对数据对象 A 加了 X 锁，就可以对 A 进行读取和修改。

加X锁期间其它事务不能对 A 加任何锁。这就保证了其他事务在该事务释放X锁之前不能读取和修改A

共享锁——S锁/读锁

一个事务对数据对象 A 加了 S 锁，可以对 A 进行读取操作，但是不能进行更新操作。

加S锁期间其它事务能对 A 加 S 锁，但是不能加 X 锁。这就保证了其他事务可以读A，但在该事务释放S锁之前不能对A进行修改

数据锁相容矩阵

封锁协议—三级封锁协议

在运用X锁和S锁对数据对象加锁的时候，还需要约定一些规则。比如何时申请X锁或S锁、持锁时间、何时释放等。这些规则称为封锁协议。此处介绍的是三级封锁协议，后续还有两段锁协议

一级封锁协议

事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。

可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。

但不能解决读脏数据和不可重复读的问题，因为在一级封锁协议中，仅仅读数据而对其进行修改是不需要进行加锁的

二级封锁协议

在一级的基础上，要求读取数据 A 时必须加 S 锁, 读取完马上释放 S 锁。

可以解决读脏数据问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。

但不能解决不可重复读问题，因为读完数据后就释放S锁，其他事务可以再加锁进行修改

三级封锁协议

在一级协议的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。

可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。

总结

活锁和死锁

和操作系统一样，封锁的方法可能引起活锁和死锁问题

活锁

避免活锁的方法就是采用先来先服务的策略

死锁

对于死锁问题，要么采取措施预防死锁发生，要么允许死锁发生，检测到死锁后采取策略解除死锁

死锁的预防

破坏产生死锁的条件

一次封锁法

每个事务必须一次性将所有需要的数据全部加锁，否则不能执行
顺序封锁法

预先对数据对象规定一个封锁顺序，所有事务都按照整个顺序进行封锁

死锁的检测和处理

超时法

如果一个事务的等待时间超过了规定的时限，就认为发生了死锁
等待图法

事务等待图是一个有向图 G = (T, U) ，T是结点的集合，每个结点表示正在运行的事务；U为边的集合，每条边表示事务等待的情况，T1——>T2 表示 T1 正在等待 T2.

如果图中存在回路，则表示系统中出现了死锁

数据库检测到死锁后，一般采取的死锁解除策略是：选择一个处理死锁代价最小的事务，将其撤销，释放此事务持有的所有的锁，使其他事务得以继续运行下去。

并发调度的可串行性

数据库管理系统对并发事务不同的调度可能会产生不同的结果，只有串行调度才能得到正确的结果

可串行化调度

多个事务的并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同，称这种调度策略为 可串行 serializable 调度

一个给定的并发调度，当且仅当它是可串行化的，才认为是正确调度

冲突可串行化调度

冲突操作是指不同的事务对同一个数据的读写操作和写写操作

不同事务或者同一事务的冲突操作是不能交换的。

一个调度在保证冲突操作次序不变的情况下，通过交换两个事务不冲突操作的次序得到另一个调度B，则称调度B是冲突可串行化的调度。

若一个调度是冲突可串行化调度，那么一定是可串行化调度

两段锁协议

目前数据库管理系统普遍采用 两段锁 TwoPhase Locking 协议（简称 2PL）的方法实现并发调度的可串行性，从而保证调度的正确性

两段锁协议就是指所有事务必须分两个阶段对数据项进行加锁和解锁

扩展阶段：在对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁
收缩阶段：在释放一个封锁的时候，事务不再申请和获得任何其他锁

封锁的粒度

封锁对象的大小称为 封锁粒度 granularity

MySQL 中提供了两种封锁粒度：行级锁 以及 表级锁

表级锁： MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

行级锁： MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。

但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大。

因此如果在一个系统中同时支持多种封锁粒度供不同的事务选择是比较理想的，这种封锁方法称为 多粒度封锁 multiple granularity locking

多粒度封锁

首先我们需要知道多粒度树：多粒度树的根节点是整个数据库，表示最大的数据粒度，叶结点表示最小的数据粒度

多粒度封锁协议允许多粒度树中的每个结点被独立的加锁，对一个结点加锁意味着这个结点的所有后裔结点都被加以同样的锁

显示封锁：应事务的要求直接加到数据对象上的锁
隐式封锁：该数据对象没有被独立加锁，继承上级结点的锁

系统检查封锁冲突时不仅要检查显示封锁，还要检查隐式封锁。

显然，这样的检查方法效率很低，为此人们引进了意向锁

意向锁

意向锁表示如果对一个结点加锁，则说明该结点的下层结点正在被加锁；对任一结点加锁时，必须先对它的上层结点加意向锁

例如：对任一元组加锁时，必须先对它所在的关系或者数据库加意向锁

目前存在三种常用的意向锁

IS锁

如果对一个数据对象加 IS 锁，则表示它的后裔结点想要加 S 锁

IX锁

如果对一个数据对象加 IX 锁，则表示它的后裔结点想要加 X 锁

SIX锁

如果对一个数据对象加 SIX 锁，则表示对他加 S 锁，再加 IX 锁

例如：对某个表加 SIX 锁，则表示该事务先要读整个表，读表过程中不允许其他事务进行修改；读表的同时还会对该表中的个别元组进行修改，所以加 IX 锁，表示表下面的元组想要加X锁。

数据锁相容矩阵

从上图我们可以看出锁的强弱程度，即对其他锁的排斥程度。一个事务在申请封锁的时候，以强锁代替弱锁时安全的，反之则不然

总结

X 锁不兼容任何锁
任意IS / IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁
这里兼容关系针对的是表级锁，而 表级的 IX 锁和行级的 X 锁兼容，两个事务可以对两个数据行加 X 锁。（事务 T1 想要对数据行 R1 加 X 锁，事务 T2 想要对同一个表的数据行 R2 加 X 锁，两个事务都需要对该表加 IX 锁，但是 IX 锁是兼容的，并且 IX 锁与行级的 X 锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）

事务的隔离级别

事务具有隔离性，理论上说事务之间的执行不应该相互影响，其读数据库的影响应该和他们串行时执行一样。完全的隔离性会导致系统并发性能很低，降低对资源的利用率，因而实际上会对隔离性的要求会有所放松。

SQL 标准为事务定义了四个不同的隔离级别，从低到高依次是：

READ-UNCOMMITTED(读取未提交)：

最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：

允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：

对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：

最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。（该隔离级别需要加锁实现，因为要使用加锁机制保证同一时间只有一个事务执行，也就是保证事务串行执行。）

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）

这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求，即达到了 SQL标准的SERIALIZABLE(可串行化) 隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化) 隔离级别。

DBMS保证事务的ACID特性原理

事务的原子性、持久性、隔离性都是为了实现事务的一致性

原子性实现原理—Undo Log

为了实现原子性，需要通过日志：将所有对数据更新操作都写入日志，如果一个事务中的一部分已经操作成功，但以后的操作由于断电/系统崩溃/其他软硬件错误或者用户提交了rollback 导致无法进行，则通过回溯日志，将已经执行成功的操作撤销 undo，从而达到全部操作失败的目的，使得数据库恢复到一致性的状态，可以继续被使用

持久性实现原理—Redo Log

和Undo Log 相反，Redo（重做） Log 记录的是新数据的备份。在事务提交前，只是将Redo Log 持久化即可，不需要数据持久化。当系统崩溃时，虽然数据没有持久化，但Redo Log 已经持久化了。系统可以根据Redo Log 将数据更新到最新的状态

隔离性实现原理—锁

当然，保证事务的隔离性，即并发控制不止可用封锁协议，还有时间戳、多版本控制等等。

基于锁的并发控制流程：

事务根据自己对数据项进行的操作类型申请相应的锁（读申请共享锁，写申请排它锁）。
申请锁的请求被发给锁管理器。锁管理器根据当前页是否已经有锁以及申请的和持有的锁是否冲突决定是否为该请求授予锁。
若锁被授予，则申请锁的事务可以被继续执行；若被拒绝，则申请锁的事务将进行等待，直到锁被其它事务释放。

可能出现的问题：

死锁：多个事务持有锁并循环等待其它事务的锁导致所有的事务都无法继续执行。

参考文献

[]: https://veal98.gitee.io/cs-wiki/#/README

魂淡

4前言

概念

特点

数据模型

两类数据模型

概念模型

一些概念

逻辑模型和物理模型

关系模型

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

内模式

模式

外模式

数据库系统的组成

关系数据库

域

笛卡尔积

关系

关系的完整性

实体完整性

参照/引用完整性

用户自定义完整性

关系代数

传统的集合运算

并（union）

差（except）

交（intersection）

笛卡尔积（Cartesian product）

综合示例

专门的关系运算符

选择（selection）

投影（projection）

连接（join）

除运算

关系数据库标准语言SQL

数据定义

模式的定义与删除

模式定义

删除模式

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

定义基本表（CREATE）

修改基本表（ALTER）

删除基本表

索引的建立和删除

建立索引

修改索引

删除索引

数字字典

数据查询

单表查询

查询表中的若干列

选择表中的若干元组

消除取值重复的行（distinct)

查询满足条件的行

比较

确定范围

确定集合

字符匹配

涉及空值的查询

多重条件查询

order by 语句

top

聚集函数

group by 子句

连接查询

等值与非等值连接查询

自身连接

外连接

多表连接

嵌套查询

带有in谓词的子查询

带有比较运算符的子查询

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

带有EXISTS谓词的子查询

集合查询

基于派生表的查询

数据更新

插入数据

插入元组