数据库复习整理
数据库整理：
按照老师的PPT整理，感谢黄老师，特别鸣谢的整理，本整理的部分内容由AlvinZH辉哥的整理扩充而来，配套教材《数据库系统概论（第五版）》（王珊编著），所有SQL相关内容均采用SQL Server现行标准，尚不完整，有空会重新排版o(╥﹏╥)o欢迎指正！
引入
数据、数据库、数据库管理系统、数据库系统
数据：
描述事务的符号记录，有数据类型和数据的值，需要有语义来解释，数据操作主要有数据管理（数据库）、数据处理（程序）、数据传输（网络）
元数据（模式），几乎不变，在设定时定义
数据是数据库的实例，经常改变
数据库DB：
长期存储在计算机内、有组织、可共享的大量数据的集合。数据按照一定的数据模型组织、描述和存储，具有较小的冗余度、较高的数据独立性、易扩展性
安全、方便、高效
数据库管理系统DBMS：
计算机的基础软件
数据库系统DBS：
由数据库、数据库管理系统、应用程序、数据库管理员组成的存储、管理、处理和维护数据的系统。
数据管理技术发展
人工管理阶段
数据没有直接存取设备，存在纸带上；
应用程序管理数据；
数据不共享，不具有独立性（数据改变后应用程序也必须改变）；
文件系统阶段
数据存在文件里，数据可以长期保存；
使用操作系统的IO接口访问数据；
文件系统管理数据；
有加快数据访问的措施；
初级的数据独立性；
数据共享性差，冗余度大；
每个应用需要程序员从头开始设计自己的文件格式和描述；
数据库系统阶段
主要优势：
数据共享；
数据冗余少；
数据独立性好；
便捷的程序接口；
高效数据访问；
数据完整性和数据安全；
并发管理
特点：
1.数据结构化，整体数据结构化，是数据库的主要特征之一，也是和文件系统的本质区别
2.数据共享度高、冗余度低且易扩充，数据共享可以减少数据冗余，避免数据之间的不一致性和不相容性
3.数据独立性高，物理独立性指应用程序和数据库中数据的物理存储相互独立，逻辑独立性指应用程序和数据库的逻辑结构相互独立，逻辑结构修改时应用程序可以不变
4.数据由数据库管理系统统一管理和控制，有数据安全性保护，数据完整性检查
隔离并发访问，阻止产生脏数据的修改
数据库结构受底层计算机系统的影响
数据模型
数据模型：对现实世界数据特征的抽象。是数据库系统的核心和基础。数据模型三个组成要素：数据结构（最重要的方面）、数据操作、数据的完整性约束条件。
三层抽象（由浅入深）
概念模型：用于数据库设计，最流行的是E-R模型。（P16-17基本概念：实体、属性、码、实体类型、实体集、联系）；
逻辑模型：用于数据库管理系统的实现；层次模型（最早出现的数据模型）、网状模型、关系模型等；
物理模型：对数据最底层的抽象，描述数据表示方式和存取方法。
常见数据模型：
层次模型
网状模型
关系模型
面向对象数据模型
对象关系数据模型
半结构化数据模型
层次模型和网状模型统称为格式化模型
基本层次联系是指两个记录以及他们之间的一对多（包括一对一）的联系
层次模型：
采用树形结构
数据库中定义满足条件的基本层次联系的集合：
1.有且只有一个结点没有双亲结点，这个结点称为根结点；
2.根以外的其他结点有且只有一个双亲结点。
删除双亲结点会把子结点同时删除
优点：
1.数据结构简单清晰
2.查询效率高（优于关系数据库，不低于网状数据库）
3.提供了良好的完整性支持
缺点：
1.现实中很多联系是非层次性的，不适用
2.对插入和删除限制较多，应用程序编写较为复杂
3.查询子女结点必须通过双亲结点
4.结构严密，层次命令趋于程序化
突出优点是一对多层次联系的部门描述自然直观
网状模型
数据库中定义满足条件的基本层次联系的集合：
1.允许一个以上的结点无双亲；
2.一个结点可以有多于一个的双亲。
优点：
1.更为直接地描述现实世界
2.存取效率高
缺点：
1.结构复杂，不利于用户掌握
2.DDL、DML复杂，要嵌入一种高级语言，用户不容易掌握，不容易使用
3.访问数据时必须适当选取路径，用户必须了解系统结构的细节，加重负担
关系模型
优点：
1.建立在严格的数学概念的基础上
2.概念单一，数据结构简单清晰，易于使用
3.存取路径对用户透明，有更高的数据独立性，更好的安全保密性
ER模型
实体(Entity)：客观存在并可相互区别的事物
属性(Attribute)：实体所具有的某一特性称为属性
键(Key)：唯一标识实体的属性集
实体型(entity type)：用实体名及其属性名集合来抽象和刻画同类实体
实体集(entity set)：同一类型实体的集合
关系(relationship)：不同实体集直接的联系，有一对一，一对多，多对多的类型
多元关系要转换成多个二元关系
多元关系的箭头：
角色：
如果多元关系中一个实体集被用到了两次，可以通过角色来区分，如下：
一元关系的一对一：
多对一：
多对多：
ER模型（续）
约束：
始终为真的断言
常用约束:键（唯一标识）、单值约束（一个实体只能有一个某类的属性）、参照完整性约束（引用的数据库中需要存在对应的条目）、域约束（约束某个属性的值的范围）
键
超级键是一组一个或多个属性组成的唯一确定一个实体的键
候选键是最小（没有其真子集可以满足唯一标识）的超级键，候选键的一个被选作主键（primary key），主键要求not null
在ER图中用下划线标注
没有主键的实体集叫弱实体集，必须依赖于强实体集存在，需要和强实体集构成一对多的关系（弱实体集为多），有鉴别器（部分键）
E-R模型设计原则：1.避免冗余；2.限制使用弱实体集；3.能作为属性的尽量作为属性。
属性原则：属性不能再具有需要描述的性质and属性不能与其他实体具有联系
如果一个实体只有一个属性，那这个实体可以使用属性代替
尽量不用弱实体集，使用弱实体集通常的原因是没有能够创建唯一ID的全局权限（所有的弱实体集的属性不能重复，例如在世界上所有的足球队中设置独特的球员号码）。
关系模型
最重要的数据模型
ER模型和关系模型对比：
ER模型有很多概念，实体、属性、关系等，关系模型只有一个概念：关系
ER模型不适合计算机实现，关系模型适合高效操作电脑
关系实例：
每个属性的允许的值称为域（domain）
模式和实例
模式（schema）：型的描述，不涉及具体的值；
实例（instance）：模式的一个具体的值。
关系模式：关系的描述，R（U，D，DOM，F）
R为关系名，U为组成该关系的属性名集合，D为U中属性来自的域，DOM为属性向域的映像集合，F为属性间数据的依赖关系集合
关系模式是型，关系是值
一个关系的模式：一个关系名，若干属性名
一个数据库的模式：若干个关系的模式
对数据的改变很频繁，而对模式的改变很少见，所以模式是长期稳定的
笛卡尔积
给定一组域D1，D2，…，Dn（它们可以有相同的元素，即可以完全不同，也可以部分或全部相同）。D1，D2，…，Dn的笛卡尔积为
D1×D2×……×Dn={（d1，d2，…，dn）|di∈Di，i=1，2，…，n}。
由定义可以看出，笛卡尔积也是一个集合。
其中：
1. 元素中的每一个di叫做一个分量(Component)，来自相应的域（di∈Di）
2. 每一个元素（d1，d2，d3，…，dn）叫做一个n元组（n-tuple），简称元组（Tuple）。但元组不是di的集合，元组的每个分量（di）是按序排列的。如：
（1，2，3）≠（2，3，1）≠（1，3，2）；
而集合中的元素是没有排序次序的，如（1，2，3）=（2，3，1）=（1，3，2）。
关系的数学定义：
关系是笛卡尔积的子集，具有有限的元组
关系：D1*D2*…Dn的子集将在在D1、D2…Dn上的关系。
候选码：属性组能唯一标识一个元组，而其子集不能。
主码：多个候选码，选定一个为主码
主属性：各个候选码的属性
非主属性：不包含在任何候选码中的属性
全码：所有属性是这个关系模式的候选码
关系的特点：
1.不允许有所有值完全相同的元组
2.关系是无序的（元组的顺序是不相关的，可以按任意顺序存储，属性的顺序也是不相关的）
3.同一属性名下的各个属性值必须来自同一个域，是同一类型数据
4.各个属性名不能相同
5.不同的属性可以有相同的域
6.属性的值必须是原子的，不可再分（反例如中国式报表，大表头包含多个小表头）
关系操作
关系操作：查询、插入、删除、修改（P43）
查询：选择、投影、连接、除、并、差、交、笛卡尔积；其中选择、投影、并、差、笛卡尔积是5种基本操作。
关系模型（续）ER模型到关系模型的转化
关系：指具体的表
基本情况：
1.实体集E转换成具有E的属性的关系（表）
2.关联R转换成具有属性的关系（表），做相关的实体的键和关联R的属性
特殊情况
1)一对多、一对一关系
2)弱实体集
关系的合并
1.一对多关系可以转换为一个独立的关系，也可以与“多”端的关系合并，给“多”端的关系里加一个原来的“一”作为属性
2.一对一关系可以转换为一个独立的关系，也可以与任意一端的关系合并，添加一个被合并的一端作为属性
弱实体集的关联：
关系包含弱实体集的所有属性以及其依附的强实体集的键
Hosts(hostName)
Logins(loginName, hostname, time)
不需要At表，At成为了Logins的一部分
完整性约束
防止数据中语义不一致
域完整性约束（表内）：限制属性值的范围（限制是否可以为空）
实体完整性约束（表内）：不同元组的某个属性不能相同（主键、唯一键约束）
参照完整性约束（表与表）：一个表的某个属性是其他表的某个属性（外键，这个属性需要是被参照的表的主键或候选键）
唯一键约束：
可以唯一区分不同的元组，允许有null值，一张表只能有一个主键但是可以有多个唯一键
关系代数
从数据库中找出需要的内容
需要使用高级查询语言：
理论：关系代数
实践：SQL
关系代数：
对关系的运算来表达查询。（运算：运算对象、运算符、运算结果）
运算对象(Operands)
关系
运算符(Operators)
对关系的操作
Five basic RA operations:
Basic Set Operations（集合运算）
union（并）, difference （差）(no intersection, no complement)
Selection: s（选择）
Projection: p （投影）
Cartesian Product: X（笛卡尔积）
集合运算：
差：R1-R2 = {t|t∈R1^t∉R2}
选择：选择选择R中满足条件C的条目
投影：π A1,…,An (R)，选择R中的A1…An列组成新的元组，会去除重复的元组
笛卡尔积：R1 x R2 ,R1和R2中的每个元组组合
Derived operations
intersection（交）
complement（补）
join（连接）
交：R1∩R2，R1和R2*有的所有元组，R1和R2的模式必须相同，等价于R1-(R1-R2)
连接：
θ连接
自然连接
等值连接
外部连接
θ连接：
由定义可知，当自然连接的两个关系没有公共属性时，结果是笛卡尔积
θ为比较运算符，输入R1(A1,…,An), R2(B1,…,Bm)，输出S(A1,…,An,B1,…,Bm)，S中的所有元组都满足θ，运算时可以先求笛卡尔积，然后从中筛选符合条件的结果
自然连接：
θ连接的θ条件为等于，而且等于的条件同名，最终结果只保留一列同名属性
求解步骤：
1.求笛卡尔积R×S
2.选择所有满足r[Ai]=s[Bj]的元组
3.消除重复属性
等值连接：
θ的条件为等于时为等值连接，同名的列需要用表名加点来区分
外部连接：
防止信息遗漏，先求连接，然后把外连接的表的没有匹配上的元组全部添加进连接的结果中，用null来表示未匹配上的值
左外连接 = 自然连接 + 左侧表中失配的元组。
右外连接 = 自然连接 + 右侧表中失配的元组。
全外连接 = 自然连接 + 两侧表中失配的元组。
除运算
关系R除以S的结果为T，则T包含所有在R但不在S中的二属性集及其值，且T的元组与S的元组的所有组合都在S中。（可用于验算除运算）
方法一：设R(X,Y)和S(Y)是两个关系，则R÷S = ∏X(R) －∏X(( ∏X(R) X S) － R )。
方法二：利用象集，参考 妙啊！
RS÷S的意义就是：在R和S的联系RS中，找出与S中所有的元组全都有关系的R元组。
关系代数缺陷：不能传递闭包
SQL
DDL数据定义语言：
create、drop、alter、commit、rename、truncate。（P80-P85）
RESTRICT：表示删除时有限制条件，有依赖对象不允许删除；
CASCADE：级联删除，删除时相关的依赖对象也被删除。
建议看一下创建表、修改表、删除表、创建视图、删除视图看一下。比如主键怎么写呀（PRIMARY KEY），唯一（UNIQUE）、视图检查（WITH CHECK OPTION）等。
查询
DQL数据查询语言（P89-P115）SQL是结构化查询语言
SELECT [ALL|DISTINCT] <目标表达式> [,<目标表达式>]... FROM <表名或视图名> [,<表名或视图名>...] | (<SELECT 语句>)[AS] <别名> [WHERE <条件表达式>] [GROUP BY <列名1> [HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC]];
如果要结果中有新的列名，用as
需要删除结果中的相同元组，用distinct
select distinct branch_name from loan
保留所有的元组用all（默认保留所有）
select all branch_name from loan
1.SELECT语句后面可接：算术表达式、字符串常量、函数(比如转换大小写)、属性别名(as后的内容)等
2. WHERE语句后面可接：比较、范围（BETWEEN AND）、集合（IN）、匹配（NOT LIKE %_）、空值（IS NULL）、多重条件
注：ESCAPE ‘<换码字符>’对通配符进行转义； eg:
Order by默认升序，asc升序，desc降序，
必须用在查询最后，可以有多个排序关键字
两个单引号表示一个单引号
匹配中%匹配任意字符串，_匹配任意字符
3. 空值：不能用‘=’，判断用IS|IS NOT。空值具有不确定性。
3-valued logic: TRUE, FALSE, UNKNOWN.
假定TRUE = 1, FALSE = 0, and UNKNOWN = ½，AND = MIN; OR = MAX, NOT(x) = 1-x。
4. 聚集函数：COUNT、SUM、AVG、MAX、MIN
注：WHERE语句中不能用聚集函数作为条件表达式，SELECT、HAVING中才能用。
集函数内可以用distinct/all，计算 不重复的/所有的 结果
集函数在统计时如果值全为null，结果也为null，否则所有的null被忽略，只统计非null
5. 分组Group by：按照某一列或多列分组，值相等的为一组，目的是细化集函数的作用对象，分组后集函数作用于每一组，每组有一个函数值
注：如果使用了聚集函数，则SELECT后只能接聚集函数或者GROUP BY后面的属性（属性集）。
6. WHERE：作用于基本表或视图，选择满足条件的元组，对原始表筛选；
HAVING：作用于组，选择满足条件的组，条件是组属性/集函数，对原始表经处理后的表筛选。
7.
SELECT S FROM R1,…,Rn WHERE C1 GROUP BY a1,…,ak HAVING C2
执行顺序：
a.计算FROM-WHERE部分，得到有所有R1,…,Rn属性的表;
b.按照属性a1,…,ak分组;
c.计算C2中的集函数，只保留满足C2的组;
d.计算S，返回结果
8. 连接：有重名属性用表名加点表示，没有重名属性可以直接表示，在where里连接，实际上执行时是遍历FROM中表的所有元组，依次判断是否符合WHERE；
自身连接：取别名操作，在FROM中显式定义两个同表的元组变量；eg:
外连接：
FROM S LEFT|RIGHT|FULL OUTER JOIN SC ON (S.Sno=SC.Sno)
SQL Server不支持 Natual join
inner join等同于在where里写连接条件，R JOIN S on <condition>是θ连接
9. 子查询：如果子查询的结果可以保证只有一个元组，则可以用作值
在FEOM和WHERE中都可插入子查询。
不相关子查询：子查询的查询条件不依赖于父查询。
相关子查询：子查询的查询条件依赖于父查询。eg:
注：
子查询中最好用元组变量来命名结果元组
子查询中不可用ORDER BY。
有些嵌套查询可以用连接代替，有些不行。
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换；
所有带IN谓词（IN谓词通常出现在where中后跟子查询）、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。
ANY（SOME）：某个
ALL：所有
对应集函数：
EXIST：存在。不返回数据，只产生逻辑真值。
全称量词转换（难点）：选修了全部课程->没有一门课不选。（P110）
特称量词转换（难点）：选修了A选的全部课程->没有一门A选过的课不选。（P111）
10. 集合查询：并（UNION）、交（INTERSECT）、差（EXCEPT）。对多个SELECT结果进行操作。
SQL续
DDL数据定义语言：
create、drop、alter、commit、rename、truncate。（P80-P85）
RESTRICT：表示删除时有限制条件，有依赖对象（作外键、包含视图、触发器等）不允许删除；
CASCADE：级联删除，删除时相关的依赖对象也被删除。
建议看一下创建表、修改表、删除表、创建视图、删除视图看一下。比如主键怎么写呀（PRIMARY KEY），唯一（UNIQUE）、视图检查（WITH CHECK OPTION）等。
多属性键：Primary key(a, b)(a, b为属性名)
primary key 和 unique
1.每个关系可以有一个主键，但是可以有多个unique
2.主键的值永远是not null，而unique的值可以有null（最多一个）
3.数据库管理系统会给主键默认建立索引
4.都可以作为外键被引用
DML数据操作语言：insert、update、delete。（P115-P119）
1.
INSERT INTO <表名> [(<属性列1>[,<属性列2>]…)] 注：可以指定属性插入
VALUES (<常量1>[,<常量2>]…);
INSERT INTO <表名> [(<属性列1>[,<属性列2>]…)]
子查询;
注：使用子查询时在insert into 表A 后一定要写明属性，如果子查询的数据类型与要插入的表中的类型不一致时，会进行强制转换
2.
UPDATE <表名>
SET <列名>=<表达式>[,<列名>=<表达式>]…
[WHERE <条件>];
3.
DELETE FROM <表名>
[WHERE <条件>];
注：不写where时会删除表里所有的元组
视图（VIEW）：
视图是从一个或几个基本表（视图）导出的表。视图与基本表不同，视图是一个虚表，数据库中只存储视图的定义，而不存放视图所对应的数据，对视图查询时才会按视图的定义从基本表中查询。（P121-129）
作用：简化用户的操作；使用户能以多种角度看待同一数据；对重构数据库提供了一定程度的逻辑独立性；能够对机密数据提供安全保护。（压题哈哈哈）
CREATE VIEW <视图名> [(<列名1>[,<列名2>]…)] AS <子查询> [WITH CHECK OPTION];
注：必须指定视图的所有列名的情况:
a.有目标列不是单纯的属性名，而是聚集函数或表达式
b.多表连接时选出了几个同名列作为视图的字段
c.需要在视图中为某个列启用新的列名
1.行列子集视图：单表导出，单纯取出的某些列和行，并且保留了主码。
2.视图消解：把视图的定义和查询结合起来转换为等价的对基本表的查询，多数关系数据库对行列子集视图的查询可以正确转换，非行列子集视图就不一定了。
3.更新视图：也需要转换为对基本表的更新。一般的，行列子集视图是可更新的
with check opinion可更新视图，会在增删改时关系数据库管理系统会检查视图定义中的条件，如果不满足会拒绝执行操作，比如插入的数据与where子句中的条件不符，会拒绝插入。使用select * 的视图可扩充性差。
4.视图的作用：P128-P129
作用:
1简化查询
2屏蔽底层数据库的改变，实现数据独立
3提供权限，不同的用户可以使用不同的表中的不同字段，可以解决一个用户可以查看不同表中的部分字段的问题，把用户可以查看的字段放进一个视图
索引
索引（INDEX）：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，数据库索引类似图书索引，可以加快查询速度。（前年考题）
（1） 关系数据库管理系统会自动选择合适的索引作为存储路径，用户不需要显式选择索引。
（2） 聚簇索引：聚簇索引的索引项顺序与表中记录的物理顺序一致。所以在一个基本表上最多只能建立一个聚簇索引。对于基于聚簇索引列的查询（特别是范围查询），可以提高查询效率。
（3） 聚簇索引的适用范围：聚簇索引列存在大量非重复值；很少对基表进行增删操作；很少对其中的变长列进行修改操作。
声明格式：
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；
· 用<表名>指定要建索引的基本表名字
· 索引可以建立在该表的一列或多列上，各列名之间用逗号分隔
· 用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC
· UNIQUE表明此索引的每一个索引值只对应唯一的数据记录
· CLUSTER表示要建立的索引是聚簇索引，建立聚簇索引后，基表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致，一个基本表只能建立一个聚簇索引
适用范围:
a.聚簇索引列存在大量非重复值
b.很少对基表进行增删操作
c.很少对其中的变长列进行修改操作
对于已含重复值的属性列不能建UNIQUE索引，对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束
设计原则
· 索引是物理结构设计的主要内容之一
· 选择索引列的一般原则如下：
· 经常作为选择运算比较列的属性应设置为索引列
· 在学生表中，常按照学生姓名进行查找，则学生姓名应设定为索引
· 对索引列的常见查询的查询结果占总记录数的百分比不宜过高
· 学生表中性别列的取值只有“男”或“女”，在这个列上建索引意义不大（除非使用位图索引）
· 索引列数量应尽量少
· 索引太多会造成数据更新速度变慢
· 在查询条件子句中尽量不要对索引列进行函数计算
· 例如设salary为索引列，则salary/6000>age/50这样的语句会造成数据库无法使用索引
· 应改造成salary>6000*(age/50)的形式，即表达式左边是单独的索引列
完整性约束
数据库的完整性：数据的正确性和相容性。（看下概念P157）
约束是数据库需要强制执行的元素之间的关系，必须始终为真，防止出现不一致
约束分类：
1.码（主键、候选键）PRIMARY (A1, A2, .., An) or UNIQUE (A1, A2, ..., An)，插入时会检查是否已插入了键相同的元组;
2.属性约束，NOT NULL， CHECK，注:check里对属性约束，出现的其他属性需要写在子查询里,check只在insert/update时检查; e.g:
3.元组约束 eg:
4.外键或参照完整性,要求某个属性是另一个表里的主键，可以定义在属性后
REFERENCES <relation> ( <attributes> )
也可以定义在建表里作元素
FOREIGN KEY ( <list of attributes> ) REFERENCES <relation> ( <attributes> )
对被引用的表的元组修改的处理：(三种可以的方法)
a.默认default:拒绝修改被引用的表的元组
b.级联cascade:在引用的表中和被引用的元组进行相同的操作，主删外删，主改外改。比如Beers和Sells表，删除Beers中的元组，会把Sells里涉及被删除的beer的元组删除，同理对Beers的元组修改会对Sells里的值也修改
c.设空值set null:把引用的表的值设为null，主删外NULL，主改外改。比如Sells的被删除的beer被null替换
如果要在修改时不采用默认的（拒绝执行），需要显式在建表时说明
5.断言：复杂的check语句拿出来单独写。
格式：
CREATE ASSERTION <断言名> <check子句>
每次对关系修改都要执行，效率不高，系统不能“智能”判断何时执行断言检查。
drop constraint <约束名>
可以删除约束
drop assertion <断言名>
可以删除断言
触发器
1.格式
CREATE TRIGGER <触发器名>
{BEFORE|AFTER|INSTEAD OF} <触发事件>ON <表名>/*指明触发器激活的时间*/
REFERENCING NEW|OLD ROW AS <变量>/*指出引用的变量*/
FOR EACH {ROW|STATEMENT} /*指明触发器类型，即动作发生频率*/
[WHEN<触发条件>] <触发动作> /*当触发条件为真时才触发动作体*/
2.触发事件-条件-动作规则，当特定的系统事件（插入、更新等）发生时，如果规则的条件成立，则执行规则中的动作，否则不执行该动作。
3.为了解决断言效率低，不会判断是否应该执行check的问题，允许使用者指定何时执行check
4.触发事件 AFTER /BEFORE /INSTEDA OF，instead of是对视图的修改，会把被instead of的对基本表的修改替换成对视图的修改，SQL Server专有
5.触发器类型，for each row会对每一行都执行，for each statement只执行一次，默认是后者
6.REFERENCING里引用的内容，如果是for each row，则可以引用old row| new row表示修改前后的元组，如果是for each statement，则可以引用old table| new table表示修改前后的表
7. Inserted 、Deleted指代改变的表，前者指用户插入的数据，后者指用户删除的数据
如果是update事件，则Inserted、Deleted表里都有内容（update的实质是先delete再insert） 如果只是insert、delete事件，则只有对应的表里有内容
例:视图的触发器
存储过程和触发器
Transact-SQL数据库编程
存储过程
存储过程：一组完成特定功能的SQL 语句集。系统提供/用户自定义存储过程。
作用：在服务器端快速执行SQL语句，效率高
降低了客户机和服务器之间的通信量
方便实施企业规则
语法：
1.声明:
declare @局部变量 <变量类型> [,@局部变量 <变量类型>……]
全局变量用@@开头
2.赋值:
select @局部变量 = 变量值 set @局部变量 = 变量值
3.注释:
--单行注释； /* 多行注释 */
4.流程控制:begin end ，if else，
分支判断
格式a:
CASE <运算式>
WHEN <运算式> THEN <运算式>
…
WHEN <运算式> THEN <运算式>
[ELSE <运算式>]
END
格式b:
CASE
WHEN <条件表达式> THEN <运算式>
…
WHEN <条件表达式> THEN <运算式>
[ELSE <运算式>]
END
循环:
WHILE <条件表达式>
BEGIN
<命令行或程序块>
[BREAK]
[CONTINUE]
[命令行或程序块]
END
等待:
WAITFOR {DELAY <‘时间’> | TIME <‘时间’>| ERROREXIT | PROCESSEXIT | MIRROREXIT}
时间必须为DATETIME类型
a．DELAY：用来设定等待的时间，最多可达24 小时
b．TIME：用来设定等待结束的时间点
c．ERROREXIT：直到处理非正常中断
d．PROCESSEXIT：直到处理正常或非正常中断
e．MIRROREXI： 直到镜像设备失败
GOTO: 跳转到以”:”结尾的标识符
RETURN: RETURN [(整数值)]，结束当前程序，返回到上一个调用它的程序或其他程 序，括号内可以指定一个返回值，否则会根据程序执行结果返回默认值
EXEC: 用于执行存储过程，EXEC <存储过程名>[参数1[,参数2]……]
PRINT: 打印，CONVERT()可以把不是字符串的内容强制转换成字符串
触发器的作用
1.通过数据库中相关的表进行级联修改。
2.禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改事务。
3.实现比 CHECK 约束定义的限制更为复杂的限制。
4.找到数据修改前后表状态的差异，并基于此差异采取行动
规范化理论
摘抄：
关系模式的问题：
- 数据冗余：重复出现，浪费空间。（尽可能少）
- 更新异常：更新代价（最好没有）
- 插入异常：插入部分信息时无法插入（最好没有）
- 删除异常：可能删除了其他想要的数据（最好没有）
函数依赖
非平凡函数依赖
完全函数依赖
部分函数依赖
传递函数依赖
1.函数依赖：（概念省略，X、Y是属性组U的子集）X函数确定Y或Y函数依赖于X，记作X→Y。例如：系号→系名，学号→姓名。
（1）函数依赖不是指关系模式R中的某些关系满足的约束条件，而是指R上的一切关系都要满足的约束条件。函数依赖关系的存在与时间无关，而只与数据之间的语义规定有关。 函数依赖的存在与时间无关，只与数据之间的语义定义有关。
（2）函数依赖的基本性质：扩张性，投影性，合并性，分解性，
2.非平凡的函数依赖X→Y：X→Y，但Y不包含于X。默认我么讨论的都是非平凡的函数依赖。
3.平凡的函数依赖X→Y：X→Y，但Y包含于X。必然成立（好像是废话）。
4.若X→Y，则称X为这个函数依赖的决定属性组，也称决定因素，Y为依赖因素。
5.完全函数依赖：在R（U）中，如果X → Y，并且对于X的任何一个真子集X’，都有X’ /→ Y，则称Y对X完全函数依赖。记作X F→ Y。
推论：单一决定因素一定是完全函数依赖。
例：（学号，课程号）→成绩
6.部分函数依赖：在R（U）中，如果X→Y，且Y不完全函数依赖于X，则称Y对X部分函数依赖。记作X P→ Y。
例：（学号，课程号）→课程名 （因为课程号→课程名，而课程号是（学号，课程号）的真子集）
7.传递函数依赖：在R（U）中，如果X→Y（Y不包含于X），Y /→ X，Y→Z（Z不包含于Y），则称Z对X传递函数依赖。记为X 传递（t）→ Z。
注：条件中要有Y /→ X，是因为如果Y→ X，则Y←→ X，则X直接→ Z，属于直接函数依赖，而非间接。
例：系号→系名，系名→系主任名。
码
1.候选码：设K为R<U,F>中的属性或属性组合，若K F→ U，则称K为R的候选码（候选键）。（即U完全依赖于K）。
2.超码：若U部分依赖于K，即K P→ U，则称K为超码（超键）。候选码是最小的超码
3.候选码可能多于一个，可选其中一个作为主码。包含在任何一个候选码中的属性称为主属性；不包含在任何一个候选码中的属性称为非主属性（非码属性）。最简单的情况，单个属性是码（主码或候选码）；最极端的情况，整个属性组U是码，称为全码。（主码和候选码都简称码）
4.关系模式R中的属性或属性组X不是R的码，但X是另一个关系模式的码，则称X为R的外部码（外码）。
范式
1.第一范式（1NF）：每一个分量必须是不可分的数据项（关系中每个属性都是不可再分的简单项）。
2.第二范式（2NF）：若R满足第一范式，且每一个非主属性完全函数依赖于任何一个候选码。
推论：候选码为单属性或者全码，则属于2NF。
特点：不存在非主属性对候选码的部分函数依赖。
1NF→2NF：消除非主属性对候选码的部分函数依赖，把部分函数依赖投影出来单独成表。（一事一表）
3.第三范式（3NF）：若R满足第二范式，且它的每一个非主属性都不传递依赖于任何候选码。
定义：关系模式R<U，F>属于第一范式，若R中不存在这样的码X，属性组Y及非主属性Z（Y不包含于Z）使得X→Y，Y→Z成立，Y/→X，则称R属于3NF。
定义理解：3NF的定义由1NF推过来的，不太好理解，判定的话用上上行2NF推导过来的就可以了，这个定义同时也可以证明，若R属于3NF，则R必属于2NF。
特点：每一个非主属性对候选码没有部分函数依赖，也没有传递函数依赖。
缺点：3NF只限制了非主属性对键的依赖关系，而没有限制主属性对键的依赖。
2NF→3NF：消除非主属性对键的传递函数依赖，把传递依赖投影出来单独成表。（一事一表）
4.BCNF：关系模式R<U,F>中，每一个决定因素都包含R的一个码（候选键），则R属于BCNF。
定义：关系模式R<U，F>属于第一范式，若X→Y（Y不包含于X）时X必含有码，则R属于BCNF。
特点：排除任何属性对候选码的传递函数依赖和部分函数依赖。在函数依赖范畴内实现彻底分离，消除插入和删除异常。
3NF→BCNF：消除原关系中主属性对键的部分函数依赖和传递函数依赖。
推论：如果R属于BCNF，则
a.R中所有非主属性对每一个码都是完全函数依赖；
b.R中所有主属性对每一个不包含它的码，都是完全函数依赖；
c.R中没有任何属性完全函数依赖于非码的任何一组属性。
定理：如果R属于BCNF，则R属于3NF一定成立。反之不一定成立，因为3NF的不彻底性（可能存在主属性对码的部分依赖和传递依赖）。
第一、二范式（部分第三范式）的缺点：
1.数据冗余
2.插入异常
3.删除异常
4.更新异常
数据依赖的公理系统
U为属性集总体，R为关系模式
A1（自反性，reflexivity）：若YÍ XÍU，则X→Y在R上成立。
A2（增广性，augmentation）：若X→Y在R上成立，且ZÍU，则XZ→YZ在R上成立。
A3（传递性，transitivity）：若X→Y和Y→Z在R上成立，则X→Z在R上成立
注：XZ代指X∪Z
函数依赖的逻辑蕴涵
定义 设F是在关系模式R上成立的函数依赖的集合，X→Y是一个函数依赖。如果对于R的每个满足F的关系r也满足X→Y，那么称F逻辑蕴涵X→Y，记为F ⊨ X→Y。
定义 设F是函数依赖集，被F逻辑蕴涵的函数依赖全体构成的集合，称为函数依赖集F的闭包（closure），记为F+。即 F+=｛ X→Y |记为F⊨X→Y。 ｝
定理：推理规则(A1, A2, A3)是完备的，也就是所有F+中的函数依赖都可用此三个规则导出；能用此三规则导出的函数依赖都属于F+
属性集的闭包
设F是属性集U上的函数集，X是U的子集，那么（相对于F）属性集X的闭包用X+表示，它是一个从F集使用函数依赖推理规则推出的所有满足X→A的属性A的集合：X+=｛ 属性A | X→A在F+中 ｝
定理: X→Y能用函数依赖推理规则推出的充分必要条件是YÍX+。
例 属性集U为ABCD，函数依赖集为｛ A→B，B→C，D→B ｝。则可求出A+=ABC，（AD）+=ABCD，（BD）+=BCD，等等。
最小函数依赖集
定义 如果关系模式R（U）上的两个函数依赖集F和G，有F+=G+，则称F和G是等价的函数依赖集。
定义 设F是属性集U上的函数依赖集。如果Fmin是F的一个最小依赖集，那么Fmin应满足下列四个条件：
⑴ Fmin+ =F+；
⑵ 每个函数依赖的右边都是单属性；
⑶ Fmin中没有冗余的函数依赖（即Fmin中不存在这样的函数依赖X→Y，使得Fmin与Fmin －｛ X→Y ｝等价）；
⑷ 每个函数依赖的左边没有冗余的属性（即Fmin中不存在这样的函数依赖X→Y，X有真子集W使得Fmin －｛ X→Y ｝∪｛ W→Y ｝与Fmin等价）
例 设F是关系模式R（ABC）的函数依赖集，F=｛ A→BC，B→C，A→B，AB→C ｝，试求Fmin。
① 先把F中的函数依赖写成右边是单属性形式：
F=｛ A→B，A→C，B→C，A→B，AB→C ｝
显然多了一个A→B，可删去。得F=｛ A→B，A→C，B→C，AB→C ｝
② F中A→C可从A→B和B→C推出，因此A→C是冗余的，可删去。得F=｛ A→B，B→C，AB→C ｝
③ F中AB→C可从A→B和B→C推出，因此AB→C也可删去。最后得F=｛ A→B，B→C ｝，即所求的Fmin。
规范化：投影分解
规范化的基本原则就是遵从概念单一化“一事一表”的原则，即一个关系只描述一个实体或者实体间的联系。
若多于一个实体，就把它“分离”出来。
因此，所谓规范化，实质上是概念的单一化，即一个关系表示一个实体
规范化就是对原关系进行投影，消除决定属性不是候选键的任何函数依赖。具体可以分为以下几步：
1．对1NF关系进行投影，消除原关系中非主属性对键的部分函数依赖，将1NF关系转换成若干个2NF关系。
2．对2NF关系进行投影，消除原关系中非主属性对键的传递函数依赖，将2NF关系转换成若干个3NF关系。
3．对3NF关系进行投影，消除原关系中主属性对键的部分函数依赖和传递函数依赖，也就是说使决定因素都包含一个候选键。得到一组BCNF关系
■ 对于那些只要求查询而不要求插入、删除等操作的系统，几种异常现象的存在并不影响数据库的操作。这时便不宜过度分解，否则当要对整体查询时，需要更多的多表连接操作，这有可能得不偿失。
■ 在实际应用中，最有价值的是3NF和BCNF，在进行关系模式的设计时，通常分解到3NF就足够了。
无损连接性（Lossless Join）：设关系模式R(U，F)被分解为若干个关系模式R1(U1，F1)，R2(U2，F2)，…, Rn(Un，Fn)，其中U=U1U2…UN，且不存在UNUj式，Fi为F在Uj上的投影，如果R与R1，R2，…，Rn自然连接的结果相等，则称关系模式R的分解具有无损连接性。
简单来说，就是如果对分解后的新关系进行自然连接得到的元组的集合与原关系完全一致,则称为无损连接。
函数依赖保持性（Preserve Dependency）：设关系模式R(U，F)被分解为若干个关系模式R1(U1，F1)，R2(U2，F2)，…, Rn(Un，Fn)，其中U=U1U2…UN，且不存在UNUj式，Fi为F在Uj上的投影;如果F所蕴含的任意一个函数依赖一定也由(F1 U F2 …U Fn)所蕴含，则称关系模式R的分解具有函数依赖保持性
简单来说，如果F上的每一个函数依赖都在其分解后的某一个关系上成立，则这个分解是保持函数依赖的（注意：这是一个充分条件）
分解等价判断
判断对关系模式的一个分解是否与原关系模式等价可以有三种不同的标准：
1．分解要具有无损连接性。
2．分解要具有函数依赖保持性。
3．分解既要具有无损连接性，又要具有函数依赖保持性。
注：
a.如果一个分解具有无损连接性，则能够保证不丢失信息。如果一个分解具有函数 依赖保持性，则可以减轻或解决各种异常情况。
b.如果要求分解既具有无损连接性，又具有函数依赖保持性，则分解一定能够达到 3NF，但不一定能够达到BCNF。
c.在3NF的规范化中，既要检查分解是否具有无损连接性，又要检查分解是否具有 函数依赖保持性。只有这两条都满足，才能保证分解的正确性和有效性，才既不会 发生信息丢失，又保证关系中的数据满足完整性约束。
非规范化技术：
有时候可以适当降低甚至抛弃关系模式的范式，提高数据库运行效率。比如经常从两个表中查询数据，为了避免频繁连接，可以适当数据冗余。
（1）表分割：
水平分割: 根据一列或多列数据的值把数据行放到两个独立的表中,通常在查询时需要多个表名，查询所有数据需要union操作
垂直分割: 把主键和一些列放到一个表，然后把主键和另外的列放到另一个表中,需要管理冗余列，查询所有数据需要join操作
（2）非规范化设计的主要优点
减少了查询操作所需的连接
减少了外部键和索引的数量
可以预先进行统计计算，提高了查询时的响应速度
（3）非规范化存在的主要问题
增加了数据冗余
影响数据库的完整性
降低了数据更新的速度
增加了存储表所占用的物理空间
事务(Transaction)
用户定义的一个数据库操作序列。ACID特性（原子性Atomicity、一致性Consistency、隔离性Isolation、持续性Durability）。
原子性：事务是数据库的逻辑工作单位，一个事务中的操作要么都做，要么都不做
一致性：数据库中只包含成功事务提交的结果时，数据库处于一致性状态，否则处于不一致性状态
隔离性：事务的执行不能被其他事务干扰，并发执行的事务之间不互相干扰
持续性：一个事务提交以后对数据库数据的改变就是永久性的，其他操作不能对结果有影响
事务的状态：
active 初始状态，事务在执行时处于此状态
partially committed，事务最终的操作执行之前
failed，事务不能正常执行
aborted，在事务回滚后，数据库恢复到执行事务之前的状态，中止，可以有两种处理：1.重新执行事务；2.杀死事务
committed，在成功执行之后
SQL与Transaction
每个SQL语句都是默认的事务，隐式启动，如果成功执行会隐式提交
事务可以在SQL里表示，begin transaction , commit, rollback
数据库系统的恢复管理部件负责对原子性和持续性的支持
shadow-database方案：
1.假设每次只有一个事务处于激活状态.
2.指针db_pointer 总是指向当前的数据库的一致拷贝.
3.所有更新都是对数据库的一份shadow copy 进行的, 仅当事务到达部分提交状态并且所有更新页都已写回磁盘db_pointer 才指向更新后的shadow copy.
4.如果事务失败, db_pointer所指向的旧的一致拷贝仍可用, 而shadow copy 则被删除.
注：不处理并发事务
并发执行：
多个事务可同时运行，优点：
1.增加处理器和磁盘利用率
2.减少事务的平均响应时间（短事务不需要等在长事务后）
调度(Schedule)
指定并发事务指令执行的按时间顺序执行的指令序列
一组事务的调度必须由这些事务的所有指令，必须保存每个事务内部的指令顺序。
可串行性概念
1.可串行化调度：多个事务的并发执行结果与按某一次序串行地执行这些事务结果相同。（P317）
2.冲突操作：不同事务对同一数据的读写操作和写写操作（至少有一个写操作）。
3.冲突可串行化调度：判断可串行化调度的充分条件。一个调度Sc在保证冲突操作次 序不变的情况下交换两个事务不冲突操作次序得到调度Sc‘，若Sc‘是串行的，则Sc是 冲突可串行化的调度。
注：前趋图
事务作结点，对于冲突的事务Ti和Tj，不妨设Ti先访问冲突的内容，则画弧 Ti->Tj，可串行化的调度的前趋图没有闭环。
没有闭环的前趋图可以通过拓扑排序得到串行化序列
4.可恢复调度：对于每对事务Ti和Tj，如果Tj读取了由Ti所写的数据项，则Ti应先于 Tj提交。这样的调度称为可恢复调度。
不可恢复调度：事务Ti和Tj，Tj读取了由Ti所写的数据项，如果Ti后于Tj提交，那么 Ti提交时出现错误，Ti回滚，而Tj却回滚不了了。
5.级联回滚(Cascading Rollbacks)：事务Ti和Tj，Tj读取了由Ti所写的数据项，如果Ti回滚，那么Tj也要回滚。
系统应该避免级联回滚，因为级联回滚需要撤销大量的操作。
6.无级联调度：为了避免调度中事务的级联回滚，对于每对事务Ti和Tj，如果Tj读取 了由Ti所写的数据项，则Ti必须在Tj读取之前提交。这样的调度称为无级联调度。（同 时保证了可恢复性）
并发控制(Concurrency Control)
为保证数据库的一致性, 调度必须是冲突或观察可串行化的, 可恢复的, 并且最好是无级联回滚的。
并发操作带来的数据不一致性，主要原因是并发操作破坏了事务的隔离性。
1.丢失修改：
2.不可重复读：
3.读脏数据：
因此需要并发控制，其主要技术有：*，时间戳，乐
上一篇： Oracle常用功能集锦
下一篇： 用ASP实现远程将文件批量改名的代码