计算机科班知识整理专栏系列文章：

基础篇

第一章绪论

数据库系统概述

数据库的基本概念

数据（data）：是用来记录信息的可识别的符号，是信息的具体表现形式。
数据库管理系统（Database Management System,DBMS）：是位于用户与操作系统之间的一层数据管理软件，用于科学地组织和存储数据、高效地获取和维护数据。
数据库（database,DB）：是长期储存在计算机内、有组织的、可共享的数据集合。
数据库系统（DB system）：是指在计算机系统中引入数据库后的系统构成。

数据库管理的发展过程

人工管理阶段
文件系统阶段
数据库系统阶段

数据库系统的特点

面向全组织的复杂的数据结构（数据的结构化是数据库系统的主要特征，是与文件系统的根本差别）
数据冗余小、易扩充
数据独立性高（包括数据和应用程序物理独立性和逻辑独立性）
统一的数据管理、控制功能（数据的最小存取单位是数据项）

数据模型：真实性、易理解、易实现（是人们对现实世界的数据特征的抽象）

数据模型的三要素

数据结构
数据操作
完整性约束

三种主要数据库模型（数据模型是数据库系统的核心和基础）

概念模型：E-R模型（实体-关系模型）
- 实体：客观存在并可相互区别的事物称为实体。（注意：不仅可以是具体的人、事、物，还可以是抽象的概念和联系。）
- 属性：实体由若干属性刻画
- 码：唯一标识实体的属性集称为码。注意可以不唯一。反映语义范畴。
- 域：属性的取值范围。
- 实体型：具有相同属性的实体具有共性。用实体名及其属性名集合来刻画同类实体，称为实体型。如：学生（学号、姓名、性别、…、入学时间）
- 实体集：同一实体型实体的集合。
- 联系：主要研究不同实体集之间的联系。
逻辑模型：层次模型、网状模型、关系模型。（层次模型和网状模型属于格式化模型，非关模型，下面的概念主要针对关系模型）
- 数据结构：二维表
- 关系：对应不可分的二维表
- 元组：表中的一行即为一个元组，表示一条信息
- 属性：表中的一列即为一个属性，每个列有唯一的名字
- 码：表中的某属性组，它可以唯一确定一个元组
- 域：属性的取值范围
- 分量：元组中的一个属性值
- 关系模式 (型)：对关系的描述，关系名（属性1，属性2，…，属性n）
- 在关系模型中，实体以及实体之间的联系都用关系来表示。
- 关系模型的数据操纵与完整性约束：
  - 操作：查、插、删、改
  - 实体完整性、参照完整性、用户定义完整性
  - 一次一集合方式（非关模型是单记录操作方式），用户只需指出“What to do”，而非关模型中需要告诉系统“How to do”
  - 系统的数据独立性比非关模型更高，存取路径对用户透明，但查询效率往往比非关模型低。

物理模型：数据库的系统结构

数据库的系统结构

数据库系统三级模式（从DBMS看，DBMS内部的系统结构）

外模式（External Schema）：
- 又称为用户模式，是数据库用户和数据库系统的接口，是数据库用户的数据视图，是数据库用户可以看见和使用的局部数据的逻辑结构和特征的描述。
- 外模式通常是模式的子集。
- 一个数据库通常都有多个外模式。一个应用程序只能使用一个外模式，但同一外模式可为多个应用程序所用。
- 保证数据库安全性的一个有力措施。
模式（Schema）：
- 也称为逻辑模式，是所有数据库用户的公共数据视图，是数据库中全部数据的逻辑结构和特征的描述。
- 一个数据库只有一个模式。
- 模式不但要描述数据库数据的逻辑结构，还要描述数据之间的联系、数据的完整性、安全性要求。

内模式（Internal Schema）：
- 又称为存储模式，是数据库物理结构和存储方式的描述，是数据在数据库内部的表示方式。记录的存储方式（顺序存储，按照B树结构存储，按hash方法存储）索引的组织方式，数据是否压缩，存储数据是否加密，数据存储记录结构的规定。
- 一个数据库只有一个内模式。内模式并不涉及物理记录，也不涉及硬件设备。
三种模式之间的关系：
- 数据库模式是数据库的核心和关键
- 外模式通常是模式的子集
- 数据按外模式的描述提供用户，按内模式的描述存储在硬盘上
- 模式介于外、内模式之间，既不涉及外部的访问，也不涉及内部的存储，从而起到隔离作用，有利于保持数据的独立性

数据库系统两层映像系统结构（从DB最终用户看，DB系统外部的体系机构）

外模式/模式映象：逻辑独立性
模式/内模式映象：物理独立性

数据库系统的组成

数据库DB、数据库管理系统DBMS 、应用系统、数据库管理员DBA……

第二章关系数据库

关系模型

关系模式概述

关系模型由关系数据结构、关系操作集合和关系完整性约束三部分组成。

关系数据结构：单一的数据结构——关系（二维表）
关系操作集合：一次一集合的操作方式，操作的对象和结果都是关系：
- 早期的关系操作能力都是用代数方式或逻辑方式来表示，分别称为关系代数和关系演算。
- SQL则是介于关系代数和关系演算之间的标准，由IBM提出，是应用得最广泛的关系数据库标准语言。
关系完整性约束：（注意：这是由DBMS来保证的，而非应用程序保证。）
- 实体完整性
- 参照完整性约束
- 用户定义完整性

关系模式具体描述

关系模式是对关系的描述(型的描述)，需描述元组集合的结构：
- 属性构成
- 属性来自的域
- 属性与域之间的映象关系
- 元组语义以及完整性约束条件
- 属性间的数据依赖关系集合

关系的描述称为关系模式，可表述为：R(U, D, dom, F)，其中：
- R为关系名，
- U为组成该关系的属性名集合，
- D为属性组U中属性所来自的域，
- dom为属性向域的映象集合，
- F为属性间数据的依赖关系集。

关系模式通常可以简记为： R(U)或R(A1, A2, …, An)。其中R为关系名，A1, A2, …, An为属性名。而域名及属性向域的映象常常直接说明为属性的类型、长度。关系模式是静态的、稳定的，而关系是动态的、随时间变化的，两者是型与值的关系。
通常的约定是，关系名以大写字母开头，属性名以小写字母开头。
在给定应用领域中，用于描述所有实体和实体间联系的关系的集合构成一个关系数据库。同样，关系数据库也有型和值之分。型：关系数据库模式，是对关系数据库的描述。值：一般就称为关系数据库(实例)。

关系数据结构

关系的一些基本概念

域：是具有相同数据类型的值的集合。如自然数，全班同学的名字等。
笛卡尔积（卡氏积）：给定一组域D1, D2, …, Dn, 这些域中可以有相同的域。D1, D2, …, Dn的笛卡儿积为： D1×D2×…×Dn＝{(d1,d2,…,dn)| di∈Di, i=1, 2, …, n}，其中每一个元素(d1,d2,…,dn)称为一个n元组。元素中的每个值di称为一个分量。（笛卡儿积不满足交换率，而关系通过给关系的列附加属性名的方式取消笛卡儿积元组的有序性。）例如：
- 例：D1={大，中，小}，D2={红，绿}，则D1×D2={(大，红)，(大，绿)，(中，红)，(中，绿)，(小，红)，(小，绿)}，共有3×2＝6个2元组。（注意：集合论中笛卡儿积不满足交换率，即笛卡儿积的元组有序。）
基数：若Di (i=1, 2, …, n)为有限集，其基数为|Di|，则D1×D2×… ×Dn的基数为：|D1|×|D2|×… ×|Dn|
关系：笛卡尔积D1×D2×… ×Dn的任意子集（包括空集）叫做在域D1, D2, …, Dn上的关系，记做R(D1, D2, …, Dn)，其中R为关系名，n是关系的目或度(degree)。
基本关系六性质：（记忆方法：三列两行一分量）
1. 列是同质的；
2. 不同列可出自同一个域，每一列为一个属性，不同属性(列)给不同属性名；
3. 列的顺序可任意交换；
4. 任意两个元组不能完全相同(任意两个元组的候选码不能相同)；
5. 行的顺序可任意交换；
6. 每一分量是不可分的数据项。

码的一些概念

超码：关系中一个或多个属性的集合，这个集合可以唯一标识出一个元组。超码的子集也可能是超码，最小的超码，称为候选码。
候选码：若关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，则称该属性组为该关系的一个候选码(Candidate Key)。
主码：一个关系可能有多个候选码，则选定其中一个作为主码(Primary Key)。
任意两个元组不能完全相同，也即任意两个元组的都不允许在主码属性上具有相同的值。因此，主码也被称作主码约束。
全码(All-key)：关系模式的所有属性组构成此关系模式的唯一候选码。
外码：
- 外码定义：设F是基本关系R的一个或一组属性，但不是关系R的码，如果F与另一基本关系S的主码K相对应，则称F是基本关系R的外码(Foreign key)，并称基本关系R为参照(引用)关系(Referencing relation)，基本关系S为被参照(被引用)关系(Referenced relation)或目标关系(Target relation)。
- 关系R和S不一定是不同的关系。
- 外码定义了参照(引用)完整性约束：要求参照关系中任意元组在指定属性上的取值必然出现在被参照关系中至少一个元组的指定属性上。

关系操作

查询：选择、投影、连接、除、并、差、交、广义笛卡尔积等
更新：插入、删除、修改
关系操作的特点是集合操作方式：一次一集合

选择（σ）

例如：

例1 查询信息系（IS系）全体学生：

σSdept=‘IS’(Student)

例2 查询年龄小于20岁的学生：

σSage<20(Student)

例3: 查询信息系的年龄小于20岁的学生：

σSdept=‘IS’∧Sage<20(Student)

投影（Π）

与投影有关的两个记号：
- 元组属性分量记号：设关系模式为R(A1, A2, …, An)。它的一个关系设为R。t∈R表示t是R的一个元组。 t[Ai]则表示元组t中相应于属性Ai的一个分量。
- 元组属性列分量记号：若A={Ai1, Ai2, …, Aik}，其中Ai1, Ai2, …, Aik是A1, A2, …, An中的一部分，则A称为属性列或域列。¬A则表示{A1, A2, …, An}中去掉{Ai1, Ai2, …, Aik} 后剩余的属性组。
- t[A]=(t[Ai1], t[Ai2], …, t[Aik])表示元组t在属性列A上诸分量的集合。
例子：
- 查询学生关系Student在学生姓名和所在系两个属性上的投影：
```
ΠSname,Sdept(Student)
```
- 查询学生关系Student中都有哪些系，即查询学生关系Student在所在系属性上的投影：
```
ΠSdept(Student)
```

投影与选择的混合计算

例子：

查询信息系学生的姓名和年龄：

ΠSname,Sage ( σSdept=‘IS’(Student) ) （注：单目运算为右结合）

连接（）

它是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。实际上，连接运算把选择和笛卡尔积合并到单个运算中。（RθS=σθ(R × S)）例如：
- 查询所有老师及他们讲授的所有课程的信息：
  - instructor instructor.id = teaches.id(teaches) 等效于 - σ instructor.id = teaches.id (instructor × teaches)
最常见的位等值连接与自然连接：
- 等值连接：θ为“＝”的连接运算称为等值连接。它是从关系R与S的笛卡尔积中选取A、B属性值相等的那些元组。如上面的例子。
- 自然连接：是一种特殊的等值连接（找到相同属性相同的元组进行笛卡尔积即可），它要求两个关系中进行比较的分量必须是相同的属性组，并且要在结果中把重复的属性去掉。假设R和S具有相同的属性组B，则

例子：
- 普通连接：
- 自然连接：

补充例子：
- 求选了2号课程的学生的学号和姓名。
- 求选修数据库原理的学生的学号和姓名。

广义笛卡尔积（×）

概念：
例子：

并（∪）

例子：

交（∩）

例子：

差（-）

例子：

除（÷）

下面说下除运算的具体计算方法

首先，两个关系：

R÷S 计算步骤：

找到R和S的共同列，即 B,C
找到R中有S中没有的列，这些列就是结果列即 A，此时列数就是相除结果的属性个数。
这里就用到了第一步的结果，找出S中--R与S的共同列的数据组合，即（B,C）的组合（书上叫做投影）， {(b1,c2),(b2,c1),(b2,c3)}
找出R中A列中的数据对应的R中的（B,C）组合（书上叫象集），即
- a1：{(b1,c2),(b2,c3),(b2,c1)}
- a2：{(b3，c7), (b2,c3)}
- a3：{(b4，c6)}
- a4：{(b6，c6}
现在就看R中A列哪个属性值对应的（B,C）组合集，包含了，S中的（B,C）组合集。

很明显只有a1,所以 R÷S结果就是

关系代数中各个运算符所含的属性个数和元组个数

关系的完整性

实体完整性规则：每一关系必有一主码，构成主码的各属性值均不能取空值查询
参照完整性规则：就是定义外码与主码之间的引用规则，若属性(或属性组)F是基本关系R的外码，它与基本关系S的主码Ks相对应(基本关系R和S不一定是不同的关系)，则对于R中每个元组在F上的值必须为：取空值(F的每个属性值均为空值)或者等于S中某个元组的主码值
用户定义的完整性规则：就是针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。

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

SQL概述

SQL对象与三级模式结构的对应关系：
- 外模式－－视图(view)、部分基本表
- 模式－－基本表(base table)
- 内模式－－存储文件(stored file)

模式的定义与删除

模式的定义

模式的定义格式：CREATE SCHEMA AUTHORIZATION 例如：
定义模式实际上定义了一个数据库的命名空间，在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。
在CREATE SCHEMA中可以接受CREATE TABLE，CREATE VIEW和GRANT子句。例如：

模式的删除

模式的删除格式：DROP SCHEMA < CASCADE | RESTRICT > 。其中：

定义、删除和修改基本表

基本表的定义

，例如：

带列级约束：
带表级约束：
约束更完整的形式：

修改基本表

，例如：

问题：如何解决多个表之间的引用问题？

解决方案：首先正常定义两个基本表，然后再分别增加外码约束：

删除基本表

基本表的删除格式：DROP TABLE <表名>［RESTRICT| CASCADE］。其中：

SQL查询

基本查询

‘_’匹配单个任意字符，’%’匹配0个或多个任意字符, ‘[ ]’匹配括号内所列字符中的一个, ‘[^]’匹配不在括号内所列字符中的单个字符。

查询表中的若干列：
- 查询指定列，例如：
  - 查询全体学生的学号与姓名：SELECT Sno, Sname FROM Student;
- 查询全部列，例如：
  - 查询全体学生的详细记录：SELECT *FROM Student;
- 查询经过计算的值，例如：
  - 查询全体学生的姓名、出生年份和所有系。要求用小写字母表示所有系名。
```
SELECT Sname, 'Year of Birth:', 2022-Sage, LOWER(Sdept) FROM Student;
```
查询表中的若干元组
- 消除取值重复的行(使用DISTINCT)，例如：
  - 查询所有选修过课的学生的学号（结果要消除重复值）：SELECT DISTINCT Sno FROM SC;
- 改变列标题（使用AS），例如：
  - SELECT Sname AS Name, 'Year of Birth:' AS Birth, 2020-Sage AS BirthYear, LOWER(Sdept) AS DepartmentFROM Student;
- AS子句也可以出现在FROM子句中，用于重命名关系，即把一个长的关系名替换成短的：
  - SELECT S.Sname AS Name, 'Year of Birth:' AS Birth, 2022-Sage AS BirthYear, LOWER(S.Sdept) AS DepartmentFROM Student AS S;

条件查询

比较大小，例如：
- 查询计算机系全体学生的名单：
  - SELECT Sname FROM Student WHERE Sdept =‘CS’;
- 查询选修2号课程成绩在90分以上(含)的学生的学号：
  - SELECT Sno FROM SC WHERE Cno = ‘2’ AND Grade>=90;
确定范围，（使用BETWEEN AND 与 NOT BETWEEN AND），例如：
- 查询年龄在20～23岁（包括）之间的学生的姓名、系别和年龄：
  - SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23;
  - 也可表示为：SELECT Sname, Sdept, Sage FROM Student WHERE Sage>=20 AND Sage <=23;
确定集合，（使用IN 与 NOT IN），例如：
- SELECT Sname, Sdept, Sage FROM Student WHERE Sage IN (20,21,22,23);
- 查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别：
  - SELECT Sname, Sgender FROM Student WHERE Sdept NOT IN (‘CS’,’MA’,’IS’);

上述这条语句有问题，即如果某些学生的Sdept为空(NULL)，其学号姓名不会出现在结果中，此时

WHERE子句计算的结果为unknow，介于true和false之间，因此不会出现。

使用IS NOT FALSE做出修改，只要WHERE子句计算的结果不为FALSE，则出现在结果中：

     SELECT Sname, Sgender FROM Student WHERE Sdept NOT IN (‘CS’,’MA’,’IS’) IS

NOT FALSE;（类似的还有IS TRUE IS NOT TRUE IS FALSE IS NOT FALSE）

字符匹配，（使用LIKE），例如：
- 匹配串为固定字符串，查询学号为201215121的学生的详细情况：
  - SELECT *FROM Student WHERE Sno LIKE‘201215121';
  等价于：
  - SELECT * FROM Student WHERE Sno = '201215121';
匹配串为含通配符的字符串：
- 查询所有姓刘学生的姓名、学号和性别：
  - SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE '刘%';
- 查询姓"欧阳"且全名为3个汉字的学生的姓名：
  - SELECT Sname FROM Student WHERE Sname LIKE '欧阳__';
  如何表示%和_本身？ESCAPE换码转义：
- 查询DB_Design课程的课程号和学分：
  - SELECT Cno, Ccredit FROM Course WHERE Cname LIKE 'DB_Design' ESCAPE‘';
- 涉及空值的查询，使用IS NULL和IS NOT NULL，例如查询缺少成绩的学生的学号及相应课程号：
  - SELECT Sno, Cno FROM SC WHERE Grade IS NULL;
多重条件（逻辑连接）查询，（WHERE中用AND和OR连接多个条件。 (AND优先级高于OR)）
- 查询计算机科学系或数学系年龄在18到23岁的学生的姓名：
  - SELECT Sname FROM Student WHERE (Sdept='CS' OR Sdept='MA') AND Sage BETWEEN 18 AND 23;
传统集合操作，关系代数中的∪，∩，-在SQL中的对应为并UNION，交 INTERSECT，差 EXCEPT，两条SQL语句间使用：
- 查询数学系选了’3’号课程的学生的学号：
  - (SELECT Sno FROM Student WHERE Sdept=‘MA’) INTERSECT (SELECT Sno FROM SC WHERE Cno=‘3’);
- 查询选了‘1’号课程但是没有选‘2’号课程的学生的学号：
  - (SELECT Sno FROM SC WHERE Cno=‘1’) EXCEPT (SELECT Sno FROM SC WHERE Cno=‘2’);
Order by子句：一个或多个属性列排序;升序:ASC;降序:DESC;缺省值/默认为升序ASC：
- 查询计算机系(CS)学生的学号和姓名，按年龄从大到小排，相同年龄的按学号升序排。
  - SELECT Sno, Sname FROM Student WHERE Sdept=‘CS’ORDER BY Sage DESC, Sno;
聚集函数：

（如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语（ALL为缺省值），则表示不取消重复值。）

例子：（COUNT(*)总是返回记录的个数，而COUNT(字段)返回指定字段值非空的记录个数。）

   查询学生总人数：SELECT COUNT (*) FROM student;
                                                                                  
   查询选修了课程的学生人数 ：SELECT COUNT(DISTINCT Sno) FROM SC;

分组查询：使用GROUP BY，将查询结果分组，例子：

Having子句：对分组限定条件，SQL在形成分组形成后才应用having子句中的谓词，因此having子句中可以使用聚集函数。例如：
- 查询选修了3门以上课程的学生学号：SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >3;

嵌套查询

带有IN谓词的子查询：例子：

找出2017年秋季和2018年春季学期都开课的所有课程：

等效于

查询选修了课程名为“信息系统”的学生学号和姓名：

带有比较运算符的子查询：当用户能确切知道内层查询返回的是单值时，可以用>、 <、 =、 >=、<=、!=或<>等比较运算符。例如：
带有ANY（SOME）或ALL的子查询，使用集函数统计查询：比较运算符一般不能与集合比较。集合前加以谓词ANY（SOME）或ALL修饰后可以比较。例如：

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

带有EXISTS谓词的子查询，EXISTS代表存在量词彐。带有EXISTS谓词的子查询不返回任何实际数据，而是根据子查询结果是否非空产生逻辑值，它只产生逻辑真值"true"或逻辑假值"false" 。例如：

相关子查询：
利用谓词演算将一个带有全称量词的谓词转换为等价的带有存在量词的谓词，例如：

查询选修了全部课程的学生姓名，可以转化为：查询这样的学生姓名，没有一门课程是他不选的。也可用集函数COUNT()代替（用Course表找出所有课程的数量，将SC表按学号分组（每组有COUNT()个元组），相对较为简便：

连接查询

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

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

连接查询的WHERE子句中用来连接两个表的条件称为链接条件或连接谓词，其一般格式如下：

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

运算符为 = 时称为等值连接，否则称为非等值连接，如：

SELECT Student. *, SC. *

FROM Student, SC

WHERE Student.Sno = SC.Sno

自然连接（在等值连接中把目标列中重复的属性列去掉）

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student, SC

WHERE Student.Sno = SC.Sno

2、自身连接

需要取两个表名：

SELECT FIRST.Cno, SECOND.Cpno

FROM Course FIRST, Course SECOND

WHERE FIRST.Cpno = SECOND.Cno

3、外连接（通过创建空值元组来保留那些在连接中丢失的元组）

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade

FROM Student LEFT OUTER JOIN SC ON(Student.Sno = SC.Sno)

4、复合条件连接

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

SQL更新

删除数据

删除某一条元组的值：
- 删除学号为200215128的学生记录：DELETE FROM Student WHERE Sno='200215128';
删除多个元组的值：
- 删除所有的学生选课记录：DELETE FROM SC;
带子查询的删除语句：
- 删除计算机科学系所有学生的选课记录：DELETE FROM SC WHERE 'CS'= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno);
  
  等效于：
```
DELETE FROM SC WHERE Sno IN (SELETE Sno FROM Student WHERE Sdept = 'CS');
```

插入数据

插入单个元组，例如：

插入子查询结果，例如：

修改数据

修改某一个元组的值，例如：

修改多个元组的值：

带子查询的修改语句：

视图

视图的概念：视图是从一个或几个基本表（或视图）导出的表，数据库只存放视图的定义而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中数据发生变化，从视图查询出的数据也会随之改变。

视图建立后，在数据字典中存放的是视图的定义。

视图的作用：

简化用户的操作：用户所做的只是对一个虚表的简单查询，而这个虚表是怎样得来的，用户无须了解。
使用户能以多种角度来看待同一数据：当许多不同种类的用户共享同一个数据库时，这种灵活性是非常有必要的。
对重构数据库提供了一定的逻辑独立性：即使重构数据库也不一定需要修改应用程序。
能够对机密数据提供安全保护：只允许用户查询提供给他的视图而不是直接查询表，可以隐藏表中的机密数据
适当的利用视图可以更清晰地表达查询语句：利用视图来表达查询语句

CREATE VIEW

<视图名> [(<列名> [，<列名>]…)]

AS <子查询>

[WITH CHECK OPTION]；

第四章数据库安全性

实现数据库安全性的技术和方法有多种，如存取控制技术、视图技术和审计技术。

并发控制技术用于保证事务的隔离性和一致性。

专有名词及解释

数据库管理员：拥有所有对象的所有权限，根据实际情况将不同的权限授予给不同的用户。
用户：拥有自己所建立对象的全部操作权限，可以使用GRANT，把权限授予其他用户。
角色：数据库角色是权限的集合。可以为一组具有相同权限的用户创建一个角色，使用角色来管理数据库权限可以简化授权的过程。
审计：启用一个专用的审计日志（Audit Log），将用户对数据库的所有操作记录在上面；审计员利用审计日志监控数据库中的各种行为，找出非法存取数据的人、时间和内容。

- 审计很费时间和空间
- DBA可以根据应用对安全性的要求，灵活地打开或关闭审计功能
- 审计功能主要用于安全性要求较高的部门。

用户标识和鉴定

即由系统提供一定的方式让用户标识自己的名字或身份，每次用户要求进入系统时，由系统进行核对，通过鉴定后才提供机器使用权。如连接数据库时输入的用户名和密码。

存取控制

存取控制机制主要包括两部分：
1. 定义用户权限，并将用户权限登记到数据字典中
2. 合法权限检查：每当用户发出存取数据库的操作请求后，DBMS查找数据字典，根据安全规则进行合法权限检查。
自主存取控制（Discretionary Access Control，DAC）：用户对于不同的数据库对象有不同的存取权限，不同的用户对同一对象也有不同的权限，而且用户还可以将其拥有的存取权限转授给其他用户。非常灵活。主要通过SQL的GRANT语句和REVOKE语句来实现。
强制存取控制（Mandatory Access Control，MAC）：每一个数据库对象被标以一定的密级，每一个用户也被授予某一个级别的许可证。对于任意一个对象，只有具有合法许可证的用户才可以存取。较为严格。

自主存取控制

授权格式如下：

GRANT <权限>[,<权限>]..

ON <对象类型> <对象名>[,<对象类型> <对象名>]…

TO <用户>[,<用户>]...

[WITH GRANT OPTION];

语义如下：

将对指定操作对象的指定操作权限授予指定的用户。发出该GRANT语句的可以是DBA，也可以是数据库对象创建者，也可以是已经拥有该权限的用户。

如果指定了WITH GRANT OPTION 子句，则获得某种权限的用户还可以把这种权限再授予其他的用户。不允许循环授权

例如：

GRANT SELECT ON TABLE Student TO U1; /* 把查询Student表的权限授予用户U1 */

回收权限格式如下：

REVOKE <权限>[,<权限>]...

ON <对象类型> <对象名>[,<对象类型> <对象名>]…

FROM <用户>[,<用户>]...[CASCADE|RESTRICT];

例如：

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

强制存取控制

特点：

保证更高程度的安全性
用户不能直接感知或进行控制
适用于对数据有严格而固定密级分类的部门

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

主体是系统中的活动实体，如：DBMS所管理的实际用户，代表用户的各进程
客体是系统中的被动实体，受主体操纵，如：文件、基表、索引、视图

敏感度标记（Label）：对于主体和客体，DBMS为它们每个实例（值）指派一个敏感度标记（Label），敏感度标记分成若干级别：

绝密（Top Secret，TS）
机密（Secret，S）
可信（Confidential，C）
公开（Public，P）
TS>=S>=C>=P

主体的敏感度标记称为许可证级别（Clearance Level）

客体的敏感度标记称为密级（Classification Level）

当一个用户以标记label注册入系统时，系统要求他对任何客体的存取必须遵循如下规则：

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

（注意第2点，即用户可以写入数据但是不能再读取自己写入的数据。）

第五章数据库完整性

数据库的完整性概述

数据库完整性是指数据的正确性和相容性。完整性是为了防止数据库中存在不符合语义的数据，即防止数据库中存在不正确的数据。

为了维护数据库的完整性，DBMS必须能够：

提供定义完整性约束条件的机制：完整性一般由SQL的DDL语句来实现，它们作为数据库模式的一部分存入数据字典中。
提供完整性检查的方法：检查数据是否满足完整性约束条件的机制
违约处理：若用户违背了完整性约束条件，应该采取的动作

实体完整性

关系模型的实体完整性在CREATE TABLE中用PRIMARY KEY定义。一种是定义为列级约束条件，另一种是定义为表级约束条件。例如：

    
CREATE TABLE Student (

    Sno CHAR(9) PRIMARY KEY, /*在列级定义主码*/

    Sname CHAR(20) NOT NULL,

    Ssex CHAR(2),

    Sage SMALLINT,

    Sdept CHAR(20)

);

CREATE TABLE Student (

    Sno CHAR(9) ,

    Sname CHAR(20) NOT NULL,

    Ssex CHAR(2),

    Sage SMALLINT,

    Sdept CHAR(20)

    PRIMARY KEY(Sno) /*在表级定义主码*/

);

CREATE TABLE Student (

    Sno CHAR(9) ,

    Sname CHAR(20) NOT NULL,

    Ssex CHAR(2),

    Sage SMALLINT,

    Sdept CHAR(20)

    PRIMARY KEY(Sno,Sname) /*属性组定义为主码，只能在表级定义*/

);

违约处理：

当用户程序对基本表插入一条记录或对主码列进行更新操作时，会进行如下检查

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

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

参照完整性

关系模型的参照完整性在CREATE TABLE中用FOREIGN KEY短语定义哪些列为外码，用REFERENCES短语指明这些外码参照哪些表的主码。

例如，关系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),

    FOREIGN KEY(Cno) REFERENCES Course(Cno) /*在表级定义参照完整性*/

);

违约检查：

对被参照表和参照表进行增删改操作时都会进行检查

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

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

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

（3）设置为空值：当删除或修改被参照表的一个元组时造成了不一致，则将参照表中所有造成不一致的元组的对应属性设置为空值。

如果要采取非默认策略需要显式地加以说明，如：

ON DELETE NO ACTION

ON UPDATE CASCADE

用户定义完整性

1、属性上的约束条件的定义：

NULL/NOT NULL、DEFAULT，UNIQUE、CHECK

列值非空（NOT NULL）

列值唯一（UNIQUE）

2、元组上的约束条件

使用CHECK短语定义元组上的约束条件，如下：

CREATE TABLE SC (

    Sno CHAR(9) NOT NULL,

    Cno CHAR(4) NOT NULL,

    Grade SMALLINT,

    PRIMARY KEY(Sno, Cno),

    CHECK(Sno='x' OR Cno NOT LIKE 'MS.%')

);

违约处理：拒绝执行

触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程，其不仅可以用于数据库完整性检查，也可以用来实现数据库的其他功能，包括数据库安全性，以及一些业务流程和控制流程。

定义触发器：

CREATE TRIGGER <触发器名> /*每当触发事件发生时，该触发器被激活*/

{BEFORE | AFTER} <触发事件> ON <表名> /*指明触发器激活的时间是在执行触发事件前或后*/

REFERENCING NEW|OLD ROW AS <变量> /*REFERENCING 指出引用的变量*/

FOR EACH {ROW | STATEMENT} /*定义触发器的类型，指明动作体执行的频率*/

[WHEN <触发条件> ] /*仅当触发条件为真时才执行触发动作体*/
    
<触发动作体>

要点:

只有创建表的用户才可以在表上创建触发器，并且一个表上只能创建一定数量的触发器
触发器名唯一
触发器只能定义在基本表上，不能定义在视图上
触发事件可以是INSERT DELETE 或 UPDATE，也可以是这几个事件的组合，如INSERT OR DELETE等，还可以是UPDATE OF <触发列, …>指明修改哪些列时激活触发器。
AFTER / BEFORE 是触发的时机，AFTER表示在触发事件的操作执行之后激活触发器。
触发器类型：FOR EACH ROW（行级触发器）和FOR EACH STATEMENT（语句触发器），假设表TEACHAR 有1000行，行级触发器将执行1000次，而语句触发器只执行1次
触发条件：触发器被激活时，只有当触发条件为真时触发动作体才执行；否则触发动作体不执行。如果省略WHEN触发条件，则触发动作体在触发器激活后立即执行。
触发动作体：在过程体中可以使用NEW和OLD来引用UPDATE/INSERT事件之后的新值和之前的旧值，如果是语句级触发器则不能使用NEW或OLD引用。

多个执行语句的触发器：

    
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件

ON 表名 FOR EACH ROW

BEGIN

执行语句列表

END

例如：

CREATE TRIGGER Insert_Or_Update_Sal

BEFORE INSERT OR UPDATE ON Teacher

FOR EACH ROW

AS BEGIN

IF (new.Job = ‘教授’) AND (new.Sal < 4000) THEN

new.Sal := 4000;

END IF;

END;

删除触发器：DROP TRIGGER <触发器名> ON <表名>

程序员不得不会的计算机科班知识——数据库原理篇（上）

基础篇

第一章 绪论