数据库基础概论期末复习（已完结）

所有内容均总结自自中国大学慕课 MOOC-北京信息科技大学-车蕾老师的数据库系统基础课程 https://www.icourse163.org/shortUrl/parse/RcDZo可以从该链接直接观看讲解视频和课件

第一章 数据库概论

1.1 数据管理及其发展过程

1.1.1 人工管理阶段

背景

• 硬件上没有磁盘，软件上没有操作系统和、管理数据的软件。

特点

•  数据不保存；
• 没有软件系统对数据进行管理；
•  只有程序的概念，没有文件的概念；
• 一组数据对应一个程序，数据是面向程序的；
• 没有形成完整的数据管理的概念。

1.1.2 文件系统阶段

背景

• 直接存取存储设备；
• 操作系统（含文件系统）和高级语言。

特点

• 数据可长期保存在磁盘上，可经常对文件进行增删改查等操作；
• 有软件（文件系统）对数据进行管理，程序和数据有了一定独立性（数据不再属于某个特定的程序，可以重复使用；
• 文件的形式多样化；
• 数据的存取基本上以记录为单位。

文件系统的缺陷

• 程序和数据之间的独立性差；
• 数据联系弱；
• 数据冗余大；
• 数据不一致性。

1.1.3 数据库系统阶段

标志着数据库技术诞生的三个事件

1.2 数据库系统的特点

1.2.1 数据库是相互关联的数据的集合

1.2.2 用综合的方法组织数据

1.2.3 低冗余与数据共享

        保证数据的一致性

1.2.4 数据具有较高的独立性

• 如数据库的三层模式结构

        数据独立性是指数据的组织和存储方式与应用程序互不依赖、彼此独立。

1.2.5 保证数据的正确性

• 如关系模型的完整性约束

        保证数据正确的特性在数据库中称之为数据完整性。

1.2.6 保证数据的安全、可靠

• 主动安全（安全机制）：有效地防止数据库中的数据被非法使用或非法修改；
• 被动安全（备份恢复）：数据遭到破坏时能立刻将数据完全恢复。

1.2.7 数据可以并发使用并能同时保证其一致性

        在多个用户同时使用数据库时，能够保证不产生冲突和矛盾，保证数据的一直信奉和正确性。

• 允许并发的同时采取了一些保障（事务管理与并发控制)来保障数据一致性。

1.3 数据模型初步

1.3.1 数据模型

1. 概念：描述数据、组织数据、对数据进行操作，是对现实世界数据特征的描述。
2. 建立数据的目的： 将现实事物转成数字化的数据，才能让计算机识别处理。
3. 数据模型应满足三个条件：

1.3.2 概念数据模型（认知模型）

        描述现实世界的数据模型成为概念模型或概念数据模型。

E-R（Entity Relationship）的三要素

• 实体
• 属性
• 联系

1.3.3 组织层数据模型（实现模型）

1 层次数据模型

• 用树形结构来表达实体之间联系；
• 层次模型表示一对多的联系直接而方便；
• 层次模型有两点限制：1.有且仅有一个节点无父结点，这个节点即为树的根；2.其他结点有且仅有一个父结点；
• 不能表示多对多；
• eg：IMS。

2 网状数据模型

• 用网状结构来表示实体之间联系；
• 取消了层次模型中的两点限制；
• eg：CODASYL。

3 关系数据模型

• 用关系（表格数据）表示实体之间联系；
• 关系：二维表格
• eg：E.F.Codd。

1.4 三层模式结构与数据库管理系统

1.4.1 三层模式结构与数据独立性

1 数据库管理系统的基本功能

• 数据库管理系统管理数据库的系统软件；
• 数据库管理系统（DBMS）；
• 数据库的诸多特点是靠用好的DBMS来保证。

DBMS基本功能：

• 数据库定义功能；
• 数据库操纵功能；
• 数据库查询功能；
• 数据库控制功能；
• 数据库通讯功能。

2 数据库的三层模式结构

3 数据独立性：

        指应用程序与数据的组织和存储结构相互独立的特性。（具体说，就算修改数据的组织方法和存储结构，应用程序不用修改）。

4 数据库管理系统的基本框架

当一个用户程序通过DBMS读取一条记录时，

1. 用户程序向DBMS发出读一条记录的指令；
2. DBCS分析指令，访问对应的外部模式；
3. DBCS完成外部模式到概念模式的转换，决定访问哪个（些）概念文件；
4. DBSS完成概念模式到存储模式的转换，决定访问哪个（些）存储文件；
5. DBSS调用存取方式，通过操作系统将读取到的记录送到系统缓冲区；
6. 用户程序从系统缓冲区得到所需记录和DBMS返回的状态信息；
7. 用户程序在工作区中使用所得到的记录。

1.5 数据库系统

1.5.1 组成

        以数据为主体的数据库，和管理数据库的系统软件数据库管理系统（DBMS），····。

1.5.2 数据库管理和数据库管理员

  数据库管理员（DBA，Database Adminstrator)

•  从事数据库管理工作；
• 是一种角色；
• 负责数据库规划、设计、实施、运行各个阶段。

第二章 概念数据模型

2.1 概念数据模型及实体联系方法

2.1.1 概念

• 信息：客观事物在人脑中产生的反映；
• 概念数据模型：描述现实世界数据及其之间联系的方法。
• 数据：数据化后的信息

2.1.2 实体-联系方法（E-R图）

 1 三要素：实体、属性、联系

2 概念

        实体：客观存在并可以相互区分的客观事物或抽象事件

        属性：实体的特征（性质）；

        实体集：具有相同属性的一类实体的集合；

        弱实体：仅靠自身的特征不能区分一个个实体，需要借助其他实体的特征才能够进行区分；

        联系：数据之间的关联集合。

3 实体之间的基本联系

        1：1；

        1：n；

        m：n；

4 依赖联系

        被弱实体所依赖的实体集也称作强实体集，强弱实体机之间的联系叫依赖联系。

2.2 深入讨论联系的几个问题

2.2.1 多对多联系

由于技术上的原因，不直接使用多对多，而是将它们转换为一对多联系。

并且，两个实体之间多对多的联系一定能转换成一对多的联系！

2.2.2 概念、逻辑、物理数据模型

1. 概念数据模型：确定实体、实体间的关系
2. 逻辑数据模型：确定实体的属性
3. 物理数据模型：确定关系、属性、联系如何映像到具体实现，数据类型等

第三章 关系数据库基础

3.1 关系数据库系统概述

3.1.1 SQL（关系数据库标准语言）

SQL的特点：

• SQL是一种一体化的语言，它包含了数据定义、查询、操作和控制等方面的功能；
• SQL语言是一种高度非过程化的语言；
• SQL语言非常简洁；
• SQL语言可以直接以命令方式交互使用，也可以嵌入到程序设计语言以程序方式使用。

3.1.2 关系数据库的三层模式结构

用户可以直接进行操作的:基本表和视图.

3.1.3 SQL Server的数据库存储结构

master数据库

 用于存储所有系统级信息，包括：       

• 所有的其他数据库的信息（包括）；
• 所有数据库注册用户的信息；
• 系统配置信息等。

tempdb数据库

用于保存所有的临时表和临时存储过程，还可以满足任何其它的临时存储要求 

• 全局资源；
• 在SQL Server每次启动时都重新创建；
• 空间管理

model数据库

是一个模板数据库，必须一直存在于SQL Server系统中。

数据库的物理存储基本上是由SQL Server自动管理的。

3.1.4 SQL Server的用户数据库

1. 用户数据存储在用户数据库中；
2. 像操作系统申请存储空间；
3. 用来存储数据库数据的操作系统文件可以分为：

1. 主数据文件

• 存储数据库的启动信息和系统表，也可以用来存储用户数据；
• 每个数据库有且仅有一个主数据文件；
• 主数据文件扩展名：.mdf。

2. 次数据文件

• 保存所偶主数据文件中容纳不下的数据；
• 通过次数据文件，可以将一个数据库的数据分布在多个磁盘上；
• 每个数据库有0至多个次数据文件。
• 次数据文件扩展名：.ndf。

3. 事务日志文件

• 保存恢复数据库的日志信息；
• 每个数据库有1至多个事务日志文件；
• 日志文件扩展名：.ldf。

3.2 关系数据模型

3.2.1 关系数据模型的三个要素和关系的形式定义

1. 三个要素

a.关系数据结构：

        实体及实体与实体之间的联系均用关系来表示，关系就是数二维表；

b.关系操作集合：

        关系操作可以用代数操作（通过代数对关系的运算来表达查询要求的方式）和逻辑方式（通过关系演算、用谓词表达对关系的查询要求的方法）来表示；                     

          关系数据语言分三类：关系代数语言、关系演算语言、具有双重特点的SQL语言。

c.关系完整性约束：

        分三类：实体完整性、参照完整性、用户自定义完整性。

2.关系的形式定义

a.

• 关系（二维表）是笛卡尔积的子集；
• 表的每一行数据都是一个元组；
• 表的每一列称为属性；
• 元组中的每一个属性值称为元组的一个分量；
• 属性取值范围称为值域。

b.

需要说明两点：

• 关系是元组的集合，集合中元素（元组）无需，元组中分量有序；
• 有限关系（元组个数有限）。

3.2.2 关系的基本性质

X

1. 每一分量都是不可分的；
2. 列的个数和每列的数据类型固定；
3. 不同的列可以出自同一个值域；
4. 列顺序无关紧要（可以任意交换，但是属性名和属性值必须作为整列同时交换）；
5. 行的顺序无关紧要（可以任意交换）；
6. 元组不可以重复（完全一样）。

3.2.3 关系模型的数据结构和基本术语

1. 候选关键字：能唯一标识一个关系的元组的最小属性集（为主关键字做准备）；
2. 主关键字（PK）：能唯一标识一个关系的元组的最小属性集；
3. 外部关键字（FK）：一个属性集不是所在关系的关键字，但是是其他关系的关键字；
4. 参照关系：1(悲惨找关系)：n(参照关系)。

3.3 关系模型的完整性约束

1. 实体完整性约束：每个元组都是可识别和唯一的；

2. 参照完整性约束：参照关系中的值要么取空，要么是被参照元组中的值；

3. 用户自定义完整性约束：CHECK, DEFAULT。

3.4 关系代数

3.4.1传统的:

        交,   并,   差,   广义笛卡尔积

3.4.2专门的:

	SELECT	选择运算	行筛选
	PROJECT	投影运算	列筛选
	JOIN	连接运算(选择某些行形成新关系)	自然连接(除去重复)
÷	DIVISION	除运算

第四章 关系数据理论

4.1 函数依赖

4.1.1 定义

        对于Y=f(X),给定一个X,都有一个Y对应,则X决定Y(X→Y),Y依赖于X；

严格形式化定义：

4.1.2 术语和符号

"不良"函数依赖:传递, 部分.

4.1.3 为什么要讨论函数依赖

1. 数据冗余问题;
2. 数据更新问题;
3. 数据插入问题;
4. 数据删除问题.

4.1.4 模式分解(拆表)

定义

        把一个关系模式分解成两个或多个关系模式，在分解的过程中消除那些“不良”的函数依赖，从而获得好的关系模式。

例如：

        仓库（仓库号，地点，设备号，设备名，库存数量）

-----> 分解为：仓库（仓库号，地点） 

                        设备（设备号，设备名） 

                        库存（仓库号，设备号，库存数量）。

要求:

1. 无损连接: 生成的表数据没变，经过自然连接可以恢复成原来的关系;
2. 保持函数依赖: 函数依赖没多没少.

4.2 函数依赖的推理规则

4.2.1 函数依赖的推理规则、Amstrong公理的推论

        自反律、增广率、传递律

定理：Amstrong公理是正确的

Amstrong公理的推论及正确性

        合并、分解、伪传递

引理：

        根据合并规则和分解规则，可以推导出：

4.2.2 逻辑蕴涵和闭包

1. 逻辑蕴涵：                                                                            
2. 闭包：                             

属性集闭包

        直接计算F的闭包太复杂，所以引入了属性集闭包。

4.2.3 公理的完备性

4.2.4 属性集闭包的计算

算法：

举例：

例1：关系模式R（U,F），U={A,B,C,D,E}，F={AB→C,B→D,C→E,EC→B,AC→B},求。

1	令={A,B}
2	F中左部为{A,B}的任意子集的函数依赖有：AB→C，B→D  Z = {C,D}
3	{A,B,C,D}
4	则F中左部为{A,B,C,D}的任意子集的函数依赖有：AB→C,B→D,C→E,AC→B
5	= {A,B,C,D,E}
6	因为 = U，所以 =  {A,B,C,D,E}

4.2.5 函数依赖集的等价和最小化

1 覆盖和等价

2 最小函数依赖

证明：

方法：

        也就是X的任何真子集Z无法把X→A的所有内容包括，即A完全函数依赖于X 。            

举例：

例1：假设有属性集U={A,B,C,D,E}，函数依赖集F={A→B,B→C,AD→E}，请问F是否是最小函数依赖。

1	F中任一函数依赖的右部都仅含有一个属性；	√
2	只用找左部个数大于1的就行	√
3		√

求解

算法：

举例：

例3：关系模式R(U,F), U={A,B,C,D,E}，F={AB→E,DE→B,B→C,C→E,E→A}，求F的最小覆盖。 。

4.3 规范化

        规范化的目的就说要设计“好”的关系，使关系尽量减少操作异常甚至拒绝操作异常现象。

4.3.1 第一范式（1NF）

        所有分量都必须是不可分的最小数据项。

4.3.2 第二范式（2NF）

        如果R(U,F) ∈1NF，并且R中的每个非主属性都完全函数依赖于关键字，则R (U,F) ∈2NF。

判断关系模式是否满足2NF的方法：

• 主关键字为单个属性时，一定为2NF；
• 主关键字为多个属性时，如果存在构成主关键字属性组的真子集决定非主属性，则不为2NF，否则为2NF

4.3.3 第三范式（3NF）

        如果R(U,F) ∈2NF，并且所有非主属性都不传递依赖于关键字，则R(U,F) ∈3NF。

判断是否为第三范式的方法：

• 满足2NF，判断非主属性之间有无函数依赖。若有，则不满足3NF；若无，则满足3N；
• 满足2NF，并且最多只有一个非主属性，则一定满足3NF；
• 满足1NF，并且没有非主属性，则一定满足3NF。

4.3.4 BC范式

定义：

结论：

        不能将3NF分解成BCNF。

4.4 模式分解

4.4.1 模式分解的准则

• 模式分解具有无损连接性
• 模式分解能够保持函数依赖

1 无损连接

a 形式定义

b 判断一个分解是否具有无损连接特性的法则

        关系模式R分解为R1和R2时无损链接分解的充要条件：

2 保持函数依赖

a 定义

分解后的函数依赖的集合，与原函数依赖等价

b 判断

        判断一个分解是否保持函数依赖，可以根据函数依赖的最小覆盖和等价来判断。

3 例题

例1.设有关系模式R(U,F)，U={职工号,仓库号,城市}，F={职工号→仓库号, 仓库号→城市}，如下分解哪个是保持函数依赖和保证无损连接的分解。

A . ρ1={R1 (职工号,Φ),R2 (仓库号, Φ),R3 (城市, Φ)}

B. ρ2={R1 ({职工号,仓库号},{职工号→仓库号}), R2 ({职工号,城市},{职工号→城市})}

4.4.2 3NF无损链接和保持函数依赖算法

例题

0	先求候选关键字： 因为所有函数的依赖的右部都没有 AB，所以首先求     ={A,B,C}=U，则 AB为候选关键字。
1	计算得到F={B→C}
2	不终止
3	R0（{A},Φ）,R1（{B,C}，{B→C}）
4	不存在分组问题
5	ρ={R0 ({A},Φ),R1 ({B,C},{B→C})，U={B,C}
6	X = {A,B}，Rx（{A,B}，Φ） τ=ρ∪Rx (X,Fx ) ={R0 ({A},Φ),R1 ({B,C},{B→C}),Rx ({A,B},Φ)
7	U0X，将R0从τ中去掉
8	分解结果：τ={R1({B,C},{B→C}),Rx({A,B},Φ)}

例2.设有关系模式R=（{C,T,S,N,G},{C→T,CS→G,S→N}）

1. 指出R的候选关键字
2. R是第几范式
3. 使用3NF保持函数依赖和无损连接算法给出分解结果

解：

1. 所有函数依赖的右部都不包括CS，先求={C,S,T,G,N}=U，则CS为候选关键字。
2. CS→T属于部分函数依赖（C→T），所以R∈1NF。

3. 0	关键字X={C,S}
   1	F={C→T,CS→G,S→N}
   2	不终止
   3	无R0
   4	R1=({C,T},{C→T)} R2=({C,S,G},{CS→G}) R3=({S,N},{S→N})
   5	ρ=(R1,R2,R3)   ={({C,T}, {C→T} ) , ({C,S,G}, {CS→G}) , ({S,N}, {S→N})}
   6	Rx({C,S},{Φ}) τ=ρ∪({C,S} ,{Φ})   ={({C,S} , Φ), ({C,T}, {C→T}) , ({C,S,G}, {CS→G}) , ({S,N}, {S→N})}
   7	XU2,将Rx去掉， 即τ={({C,T}, {C→T}) , ({C,S,G}, {CS→G}) , ({S,N}, {S→N})}
   8	最终的τ就是分解结果

---

第五章 逻辑数据模型和物理数据模型

5.1 数据库设计的概念和方法

5.1.1 数据库设计的概念

        描述了 现实世界的数据管理需求。

        这里只关注侠义的，即数据模型的设计。

5.1.2 数据库设计的一般步骤

1. 需求分析：功能需求、应用需求、安全性需求；
2. 概念模型设计：E-R图；
3. 逻辑模型设计：将概念结构设计的结果转换成选定的DBMS所支持的关系模型；
4. 物理模型设计：为逻辑结构设计的结果选取一个最适合应用环境的数据库物理结构；                                        包括某些操作约束、响应时间与存储要求。

---

5.2 逻辑数据模型设计

设计关系数据模型，决定了用什么方法来实现现实世界的数据管理需求。

5.2.1 主要内容

确定各个关系模式的主关键字、外部关键字、属性的约束，三种完整性约束，设计视图。

5.2.2 把E-R模型转换为关系数据模型

① 将每一个实体转换为一个关系模式，使其包含对应实体的全部属性，并确定关键字

② 联系：

• 将一对多关系直接并入n端实体的关系模式，这需要将“1”端实体的关系模式的主关键字，纳入n端实体的关系模式，并作为外部关键字。
• 将一对一联系的两个关系模式合并为一个关系模式。
• 将具有相同关键字的关系模式合并为一个关系模式。

例1        1：1联系

或者↓

亦或者↓

例2        1：n联系

例3        m：n联系

例4        单选

• A. 合并成一个关系模式，1端实体的主关键字为合并后的关系模式的主关键字，n端实体的主关键字做外部关键字。 （合并成一个是1：1）
• B. 两个实体分别对应一个关系，然后提取两个实体的主关键字，再引 入一个新的关系来表示联系。 （引入一个新的关系是m:n）
• C. 将一对多的联系直接并入n端实体的关系模式，并将1端实体的主关键字纳入n端实体作外部关键字。

答案：C

5.3 物理数据模型设计

        决定如何去实现现实世界的数据管理。

5.3.1 有逻辑数据模型生成物理数据模型

 一般涉及如下内容：（就是一堆SQL语句实现的东西）

• 确定使用的数据库管理系统；
• 定义表；
• 定义关键字；
• 定义或完善视图；
• 定义列；
• 定义域；
• 定义约束规则；
• 定义索引；
• 定义触发器；
• 定义参照联系（外部关键字）；
• 定义扩展属性等。

5.3.2 创建数据库

例：建立“仓储订货”数据库，其中数据主文件的初始大小为10MB，最大为50MB，增 量为5MB；日志文件的初始大小为5MB，最大为25MB，增量为5MB，并将数据 文件安排在c盘\mssql\data\文件夹下，把日志文件安排在d盘的\mssql\log\文件夹下。

CREATE DATABASE 仓储订货
ON
(NAME = order_dat,
        FILENAME = 'C:\mssql\data\orderdat.mdf',
        SEIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON  
(NAME = order_log,
        FILENAME = 'D:\mssql\log\orderlog.ldf',
        SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB)

下面是建了三个文件组来存放数据文件，然后一个来放日志文件

第六章 数据定义、操作、完整性约束

6.1 SQL的架构和定义

6.1.1 什么是架构（schema）

        一个逻辑上的概念，是数据库中一组用户对象的逻辑集合。通过架构（schema）管理对象

6.1.2 定义架构

• 定义：

  CREATE SCHEMA shema_name [AUTHORIATION owner_name]
  

         架构通常由数据库管理员创建，AUTHORIATION owner_name可以指定架构的管理者（默认是创建者）。

• 删除：

  DROP SCHEMA schema_name
  

  只有在架构为空 不包含任何对象时才可以删除。

6.1.3 架构的使用（对象的引用格式）

• 每一个数据库对象都在某个架构下，为此引用对象的基本格式是       [<架构名>.] <对象名>
• 例如创建表的命令格式是     CREATE TABLE [<架构名>.]<表名>（列定义或描述）
• 再如查询的命令格式是    SELECT ... FROM [<架构名>.]<表名>...

6.1.4 默认架构

• 默认架构名可以省略（直接CREATE TABLE不写schema_name会在默认架构下创建表）
• 系统预定义的默认架构是dbo
• 在CREATE USER命令中可以为用户指定默认架构 

6.2 SQL的表定义和完整性定义功能

        主要内容：1.CREATE TABLE命令的基本格式； 2.列的定义； 3.计算列的定义； 4.表级约束

6.2.1 定义表及其完整性约束

1 CREATE TABLE命令的基本格式

2 列的定义（<column_definition>）

        基本形式

• NO ACTION：禁止
• CASCADE：进行级联处理（连着被参照一起删除）
• SET NULL：将参照记录的对应字段设为空
• SET DEFAULT：将参照记录的对应字段设为默认值
• 默认NO ACTION

        SQL Server的数据类型

Tips：

1. char定长，varchar不定长；
2. char()和varchar()中的n指的是字节数，一个英文1字节，一个中文2-4字符；
3. Unicode类型的n指的是字符数（无论中英文），一个字符2字节。

3 计算列的定义

 ON DELETE 和ON UPDATE少了 SET NULL和SET DEFAULT（因为没有外键约束或参照完整性约束）

4 表级约束

在列的定义后面，例如：

PRIMARY KEY(column1,column2,...)

UNIQUE(column1,column2,...)

5 例子

CREATE TABLE 订货.订购明细(
    订购单号 CHAR(5) FOREIGN KEY REFERENCES 订货.订购单
            ON DELETE CASCADE ON UPDATE CASCADE,
    序号 SMALLINT CHECK(序号>=1),
    器件号 CHAR(8) FOREIGN KEY REFERENCES 基础.器件,
    单价 SMALLMONEY,
    数量 INT CONSTRAINT num CHECK(数量>=0),
    PRIMARY KEY(订购单号,序号)
)

6 总结：

在完整性约束中：

• PRIMARY KEY用于实现实体完整性约束；
•  FOREIGN KEY REFERENCES用于实现参照完整性约束；
•  CHECK、UNIQUE 和 NOT NULL用于实现用户定义完整性约束。

6.2.2 修改表结构

例子

/*删除完整性约束*/
ALTER TABLE 基础.职工 
DROP CONSTRAINT ref_wh
 
/*添加完整性约束*/
ALTER TABLE 基础.职工
ADD CONSTRAINT ref_wh
FOREIGN KEY(仓库号) REFERENCES 仓储.仓库(仓库号)
ON DELETE SET NULL
ON UPDATE CASCADE
 
/*增加和删除字段*/
ALTER TABLE 订货.订购明细
ADD 完成日期 datatime
 
ALTER TABLE 订货.订购明细
DROP COLUMN 完成日期

6.3 SQL数据操作与完整性约束的作用

6.3.1 插入操作及其完整性约束

1 基本形式

Tips：

1. INSERT向所有列插入值的时候，不用写column_list，但是，如果只由部分列有值，要按顺序写出给谁插
2. 当插入的值是SELECT到的结果时，不写VALUES，如：INSERT INTO table_name SELECT ...FROM

2 例子

INSERT INTO 订货.订购明细 VALUES('OR67',1,'P2',120,5)

3 插入操作涉及的完整性约束：

• 实体完整性约束：主键重复、主键为空
• 参照完整性约束：被参照表无这个关键字
• 用户定义完整性约束：CHECK( ... )、UNIQUE、NOT NULL

 6.3.2 删除操作及其完整性约束

1 基本形式

2 例子

DELETE FROM 仓储.仓库 WHERE 仓库号='WH4'

3 删除操作涉及的完整性约束

        删除操作只与参照完整性有关，并且只有删除被参照表的记录时，才需要检查参照完整性，这时，系统会根据定义参照完整性时确定的处理方法（拒绝删除-ON ACTION、控制删除-SET NULL、默认值删除-SET DEFAULT、级联删除-CASCADE）进行处理

6.3.3 更新操作及其完整性约束

1 基本形式

2 例子

UPDATE 仓储.仓库 SET 面积=800 WHERE 仓库号='WH1'

3 更新操作涉及的完整性约束

        更新操作可以看作时先删除旧记录再插入新记录，因此更新操作的数据完整性检查综合了插入操作和删除操作的数据完整性检查。

第七章 SQL查询

7.1 查询语句基本格式

7.1.1 基本格式

• ALL说明不去掉重复数组、DISTINCT说明要去掉重复数组。

• 说明查询的数据来源，可以基于单个或多个表插；
• 可以基于基本表、也可以基于视图、也可以一起用。

• 说明查询条件，最常用的就是=，还有其他的运算符

SELECT * FROM 基础.职工
 
/*查询职工的年工资（=工资*12）*/
SELECT 姓名,工资*12 AS 年工资 FROM 基础.职工    --用AS对查询到的列命名，AS可以省略
 
/*去掉重复行*/
SELECT DISTINCT 班组长 FROM 基础.职工

--查询名字有三个字，且中间是靖的员工
SELECT * FROM 基础员工 WHERE 姓名 LIKE '_靖_'
 
--查询任意位置包含字符串'DDR'的所有记录
SELECT * FROM 基础.器件 WHERE 规格 LIKE '%DDR%'
 
--查询前三位是OR6、最后一位为0~9的所有订购单记录
SELECT * FROM 订货.订购单 WHERE 订购单号 LIKE 'OR7[0-9]'

SELECT * FROM 基础.器件 WHERE 器件名称 IN ('内存','鼠标')
 
SELECT * FROM 基础.器件 WHERE 器件名称 = '内存' OR 器件名称 = '鼠标'

SELECT 订购单号,供货方,订购日期 INTO 订货.E3 FROM 订货.订购单 WHERE 经手人 = 'E3'

--按单价升序列出所有器件信息
SELECT * FROM 基础.器件 ORDER BY 单价

--查询所有订购明细，先按订购单号升序排序，再按金额降序排序
SELECT * FROM 订货.订购明细 ORDER BY 订购单号,金额 DESC

         TOP(expression) [PERCENT] [WITH TIES]

-- 查询在北京或是上海工作的员工的仓库号、职工号、姓名
SELECT 职工.仓库号, 职工号, 姓名
FROM 基础.职工 JOIN 仓储.仓库
ON 职工.仓库号 = 仓库.仓库号
WHERE 城市 IN('北京','上海')

--找出供应商所在地的数目
SELECT COUNT (DISTINCT 地址) FROM 订货.供应商
 
--求支付的工资总数、职工人数、所有职工的平均工资、最高工资、最低工资
SELECT SUM(工资) 总工资,COUNT(*) 人数,
    AVG(工资) 平均工资,
    MAX(工资) 最高工资,MIN(工资) 最低工资
  FROM 基础.职工

--查询每个仓库的职工人数和平均工资
SELECT 仓库号,COUNT(*) 人数,AVG(工资) 平均工资
FROM 基础.职工
GROUP BY 仓库号

--查询哪些仓库目前还没有职工
SELECT * FROM 仓储.仓库
WHERE 仓库号 NOT IN
    (SELECT 仓库号 FROM 基础.职工)
 
--找出哪些城市的仓库向北京的供应商发出了订购单
SELECT 城市 FROM 仓储.仓库 
WHERE 仓库号 IN (SELECT 仓库号 FROM 基础.职工
    WHERE 职工号 IN (SELECT 经手人 FROM 订货.订购单
        WHERE 供货方 IN (SELECT 供应商号 FROM 订货.供应商
                WHERE 地址='北京')))

7.6 需要查询支持的数据操作

7.6.1 插入

INSERT INTO 仓储.库存
    SELECT 'WH3',器件号,NULL FROM 基础.器件
--第一列、第二列是常量，第二列时从器件表查询到的器件号，查询结果插入库存表

7.6.2 更新

UPDATE 订货.订购单 SET 金额 = 
(SELECT SUM(金额*数量) FROM 订货.订购明细
WHERE 订购单号 = 订购单.订购单号)

7.6.3 删除

DELETE FROM 订货.供应商 WHERE 供应商号 NOT IN
(SELECT 供货方 FROM 订货.订购单 WHERE 供货方 IS NOT NULL)
--删除现在没有任何订购单记录的供应商记录

7.7 视图及其操作

7.7.1 视图的建立和使用

1 行列子集视图

        从单个基本表选取某些行和某些列，并且包含基本表的关键字所定义的视图

CREATE VIEW emp_v1 AS
        SELECT 职工号,仓库号,姓名
        FROM 基础.职工

视图是虚拟表，所有对视图的操作实际上都要转换成对基本表的操作！ 

执行查询：等价于AS后面的SELECT语句；

SELECT * FROM emp_v1

同时，也可以对视图进行插入、删除、更新等，这里就提一下插入：

INSERT INTO emp_v1 VALUES('E13','WH1','张三')

 等价于对基本表中，被选取的职工号、仓库号、姓名这三列进行插入，其他的列会为NULL或DEFAULT，这时可能会因为完整性约束而导致插入失败。

2  WITH CHECK OPTION的作用

• 有WITH CHECK OPTION：视图上的插入或更新操作必须满足视图定义的WHERE条件，否则操作会被拒绝并产生错误。
• 没有WITH CHECK OPTION：插入或更新操作不会受视图WHERE条件的限制，但插入或更新后的数据可能不会在视图中显示出来。

 3 基于多个表的视图

CREATE VIEW emp_v2 AS
SELECT 仓库.仓库号,城市,职工号,姓名
FROM 基础.职工 JOIN 仓储.仓库 ON 职工.仓库号 = 仓库.仓库号

 之后查询：

SELECT 职工号,姓名 FROM emp_v2
WHERE 城市 = '北京'

4 包含虚列的视图

--定义视图的时候可以指定列名，只是一般都直接用基本表的名字，所以省略了
CREATE VIEW emp_v3(职工号,姓名,月工资,年工资) AS
SELECT 职工号,姓名,工资,工资*12
FROM 基础.职工

         计算列只能查询、不能操作

7.7.2 视图的修改和删除

 修改：

删除：

7.7.3 视图的作用

1. 视图可用作安全机制；
2. 着重于特定数据；
3. 简化数据操作；
4. 可以保证概念数据独立性

第八章 数据库编程基础

8.1  Transact-SQL

变量声明

        DECLARE @<变量名> <数据类型> [,@<变量名> <数据类型>...]

         注意：变量名前必须有@前缀，以便和关系的属性名相区分

赋值

8.2.1 游标的使用

1 声明游标（文件）

        DECLARE <游标名> CURSOR FOR <SELECT-查询>

游标可以看作是一个临时存储或临时文件，它的内容就是SELECT语句的查询结果。

DECLARE wh_cursor CURSOR FOR 
SELECT 仓库号,城市,面积 FROM 仓储.仓库

2 打开游标

 执行该语句意味着执行CURSOR语句中的SELECT查询，并使游标指针指向查询结果的第一条记录。只能打开已经声明但还没有打开的游标。

OPEN wh_cursor

3 从游标中读数据

INTO后的主变量要与游标中的SELECT的字段相对应。

该语句的功能是取出游标的当前记录并送入主变量，同时使游标指针指向下一条记录。

使用全局变量@@FETCH_STATUS来判断FETCH语句对游标的操作状态

• 0：FETCH语句成功；
• 1：FETCH语句失败或不在结果集中；
• 2：提取的行不存在

循环是否继续读取游标： WHILE @@FETCH_STATUS=0

FETCH FROM wh_cursor INTO @whno,@city,@area
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT @whno+' '+@ciry+STR(@area,4)
    FETCH FROM wh_cursor INTO @whno,@city,@area
END

4 关闭游标

        CLOSE <游标名>

此时系统并没有完全释放游标，可以再次OPEN打开

CLOSE wh_cursor

5 释放游标

        DEALLOCATE <游标名>

释放以后就无法再OPEN打开了

DEALLOCATE wh_cursor

 8.2.2 游标概念归纳

游标还可以嵌套：

8.3 存储过程

        来源于C/S服务器数据库体系结构，在C/S结构的数据库中，数据库端还可以存放程序，因为这些程序以数据库对象的形式存储在数据库中，所以称为存储过程。

8.3.1 存储过程的作用

• 避免在网络上传输大量无用的信息或原始数据，只需要传输调用存储过程的指令和数据库服务器返回的处理结果；
• 便于程序反复使用，减轻程序的编写工作量
• 存储过程间接实现一些安全控制功能。

8.3.2 存储过程的创建与使用

几点说明：

• 创建存储过程通常是在数据库设计和开发阶段完成的；
• 存储过程一般用来完成数据查 询和数据处理操作；
• 存储过程可以嵌套 ；
• 在存储过程中不可以使用任何CREATE

 8.3.2 执行存储过程

8.3.3 存储过程的修改和删除

• ALTER PROCEDURE，格式与CREATE PROCEDURE命令的格式类似。
• DROP PROCEDURE [schema_name.]procedure_name

8.3.4 例子

使用带有参数和返回值的简单过程查询指定仓库订单金额大于指定值的订单数，查询结果通过RETURN语句返回

--创建
CREATE PROCEDURE useGetOrderNum
@whno char(6), @sum money
AS
DECLARE @count int
SELECT @count = COUNT(*) FROM 订货.订购单
WHERE 金额>=@sum AND 经手人 IN
(SELECT 职工号 FROM 基础.职工 WHERE 仓库号 = @whno)
RETURN @count
 
 
--执行
DECLARE @count int
EXECUTE @count = uspGetOrderNum 'WH1',1000
PRINT 'WH1仓库金额在1000以上的订单数是：'+STR(@count)

8.4 触发器及其用途

• 触发器可以看作是一类特殊的存储过程。
• 基本特点：在满足某个特定条件时自动触发执行。
• 触发器和存储过程同是提高数据库服务器性能的有力
  工具。

 8.4.1 概念

        触发器和存储过程都是事先设计好存储在数据库中的，但是不同的是，触发器不需要专门调用或执行。（有DML（数据操作语言）、DDL（数据定义语言）、LOGIN触发器）

1. DML触发器依附于表（或视图）。

2. DML触发器分为

• 插入触发器
• 删除触发器
• 更新触发器

 3. 触发器的三个要素：

• 定义触发的表（或视图）
• 激活触发器的数据操作语言
• 采取的动作

 8.4.2 建立触发器

8.4.3 删除触发器

8.4.4 重点！

1 触发器FOR和INSTEAD OF的区别

2 deleted表和inserted表

它们的作用：

• 可以使用 deleted 表和 inserted 表判断正在操作的记录是否符合要求，从而检查错误并采取相应的措施；
• 找出数据修改前、后表的状态差异，并基于该差异 采取相应的措施；
• 可以扩展表之间的参照完整性。

8.4.5 触发器的应用举例

1 使用包含提醒消息的DML触发器

CREATE TRIGGER reminder1
ON 仓储.库存
FOR UPDATE 
AS 
DECLARE @amount int
SELECT @amount=数量 FROM 
inserted
IF @amount<5
RAISERROR (‘库存数量已经小于5！’, 16, 10) --报错语句

2  由于CHECK约束只能定义列级或表级约束，表间的任何约束或业务规则都必须通过定义触发器来实现。

CREATE TRIGGER isSameCity ON 订货.订购单
FOR INSERT,UPDATE AS
DECLARE @city char(10), @addr char(20), @sno char(5)
--从新欸顶订货单记录得到供货方和经手人信息
SELECT @sno = 供货方, @eno = 经手人 FROM inserted
--若指定了供应商则做如下操作
IF @sno IS NOT NULL
BEGIN --得到发出该订单仓库所在城市
    SELECT @city = 城市 FROM 仓储.仓库 WHERE 仓库号 IN
    (SELECT 仓库号 FROM 基础.职工 WHERE 职工号 = @eno)
    --得到供应商的地址
    SELECT @addr = 地址 FROM 订货.供应商 WHERE 供应商号 = @sno
    --如果仓库和供应商在不同城市，且存在同城的供应商则拒绝
    IF @city!=@addr AND
        EXISTS (SELECT * FROM 订货.供应商 WHERE @city = 地址)
        BEGIN
            RAISERROR('本地有供应商，订购单不许发往异地！',16,1)
            ROLLBACK TRANSACTION
        END
END

第九章 数据库安全

9.1 安全性概述

        保证数据库安全是对数据库系统的基本要求。

        数据库安全的核心问题：防止数据被非法使用和恶意破坏，是要防范非法用户的故意窃取和破坏（人为）

9.1.1 安全性措施的层次

• 物理层
• 人员层
• 操作系统层
• 网络层
• 数据库系统层（只有这一层是DBMS要考虑的，保护数据库的安全最后落在这里）

 数据库安全的核心问题是：身份识别。

9.1.2 数据库管理系统的安全功能

1 访问控制

• 用户管理（LOGIN、USER）
• 角色管理（ROLE）
• 权限管理（GRANT、ROVOKE）

2 数据加密

3 管好数据库的安全是DBA的重要职责

9.1.3 数据库管理系统的身份验证模式

1 登录用户的主要来源

• SQL授权用户：来源于非Windows的用户
• Windows授权用户：来源于Windows的用户或组

 9.2 用户管理

9.2.1 登录用户和数据库用户

• 登陆到系统的用户
• 使用某个数据库的用户

9.2.2 登录用户管理

        谁来管理？

1 建立登录用户   

• CHECK_EXPIRATION = ON：第一次登陆时强制必须改密码

2 修改登陆用户

• ALTER LOGIN login_name ENABLE / DISABLE ：启用 / 禁用用户
• 用户在登陆后自己修改自己的密码，需要提供OLD_PASSWORD

3 删除数据库用户

9.2.3 数据库用户管理

        要访问哪个数据库，就必须在此数据库下建立与登录用户相对应的数据库用户

1 建立数据库用户

在已经 USE 本数据库之后，建立角色不需要指定数据库

• 如果省略了LOGIN login_name，就默认登录名和用户名一样
• 可以为一个登录用户在多个用户数据库下建立对应的数据库用户，但是一个登录用户咋i要给用户数据库下只能对应一个数据库用户。

2 修改数据库用户属性

3 删除数据库用户

• 删除数据库角色不影响他的登陆身份，只是他不再是当前数据库的用户。

9.2.4 数据库用户的分类

• 系统管理员用户：在所有数据库中拥有所有权限
• 数据库管理员用户：在某一个数据库上拥有一切权限，dbo（database owner）
• 数据库对象用户：在自己建立的数据库对象（表、视图）上拥有一切权力，dboo（database object owner）
• 数据库访问对象：一般的数据库用户，可以对被授权的数据库对象进行操作。

9.3 角色管理

        角色一般分为：系统管理员角色、后台数据管理角色、客户角色

9.3.1 用户和角色的关系

        1.可以将用户指定为角色的成员

        2.用户自动从角色继承权限

        3.可以取消用户的角色成员资格

9.3.2 Public角色

• 每个数据库都有；
• public角色的权限是数据库中所有角色的默认权限（默认为空，可以授权）；
• 没有必要，也不能将用户指定为public角色的成员（默认所有用户都属于public）；
• 不能取消用户的public角色成员资格；
• 不能删除public角色。

9.3.3 角色管理

1 定义角色（CREATE ROLE）

• 通常角色由定义角色的用户所有，使用AUTHORIZATION可以定义owner_name（用户或角色）可以指定谁拥有该角色。

 2 指定用户角色（sp_addrolemember）

        数据库管理中角色代表了一种只能，，每个数据库用户可以担当一个或多个角色，为此需要将数据库用户指定为数据库角色的成员。

• 指定和取消都使用系统存储过程
• 可以直接sp_addrolemember 'role','membername'

3 取消用户角色（sp_droprolemember）

• 指定和取消都使用系统存储过程
• 任何用户不能取消public角色

4 修改角色名称（ALTER ROLE）

5 删除角色（DROP ROLE）

• 不能删除仍带有用户的角色，在删除前要解除该角色的所有成员。

9.3.4 系统预定义角色

1 系统管理预定义角色

• sysadmin：具有系统管理员全部权限的角色
• securityadmin：负责管理数据库的登陆
• dbcreator：负责创建和改变数据库
• diskadmin：负责管理磁盘文件

 a.指定系统预定义角色

• 同样可以省略参数声明

b.取消系统角色成员资格

先切换到sa用户

2 数据库预定义角色

• db_owner：在数据库中有全部权限，即具有数据库管理员全部权限的角色
• db_backupoperator：负责数据库的备份
• db_datareader：可以查询任何用户表中的所有数据
• db_datawriter：可以更改任何用户表中的数据
• db_denydatareader：不能查询任何用户表中的任何数据
• db_denydatawriter：不能更改任何用户表中的任何数据

a.数据库预定义角色的指定

9.4 权限管理

        系统初始只有一个sa用户，每个数据库用户的权限都来源于sa

        通过授权GRANT，收回授权REVOKE动态管理权限

9.4.1 对象权限管理

• SELECT、INSERT、UPDATE、DELETE语句权限，被应用到整个表或视图
• SELECT和UPDATE语句权限，可以有选择地应用到表或视图的某些列
• INSERT和DELETE语句权限，会影响整行，只能应用到表或视图，不能应用到单个列
• EXECUTE语句权限，执行存储过程和函数

1 授予对象操作权限

• 这里的权限(permission)主要是创建各种数据库对象的CREATE命令，包括 CREATE TABLE、CREATE VIEW、CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE等
• 还包括备份数据库的BACKUP DATABASE和BACKUP LOG权限
• sa还可以授权CREATE DATABASE权限
• ALL 说明授予全部权限，也可以用选项 permission [ ,...n ] 逐一授予指定的权限；
• TO principal [ ,...n ]说明被授予权限的一个或多个主体，可以是用户名或角色
  名
• WITH GRANT OPTION说明被授予权限的用户或角色也可以行使同样的
  GRANT 权限；
• AS principal说明当使用 GRANT 授权时是作为某个用户或某个角色在行使职权。

例题：仓储订货”数据库的安全控制解决方案

• 除职工工资所有信息对所有用户均可查询
• store_man库存管理角色，可以对“库存”表插入、删除，可以修改库存数量， 至少有一个用户担当此角色
• order_man订单管理角色，可以对“订购单”表插入、删除，可以修改经手人、供货方、订购日期和金额；可以对“订购明细”表插入、删除，可以修改数量和单价，至少有一个用户担当此角色
• 其他临时授权和权限管理

--将wang指定为”仓储订货“的DBA
sp_addrolemember 'db_owner','wang'
 
--授权所有用户都可以查询除职工工资以外的所有信息
GRANT SELECT ON 仓储.仓库 TO public
GRANT SELECT ON 仓储.库存 TO public
GRANT SELECT ON 基础.职工(职工号,仓库号,姓名,班组长) TO public
GRANT SELECT ON 基础.器件 TO public
GRANT SELECT ON 订货.供应商 TO public
GRANT SELECT ON 订货.订购单 TO public
GRANT SELECT ON 订货.订购明细 TO public
 
--建立角色store_man和order_man，然后按要求授权
CREATE ROLE store_man
CREATE ROLE order_man
 
GRANT INSERT,UPDATE(数量),DELETE ON 仓储.库存 TO store_man
 
GRANT INSERT,UPDATE(经手人,供货方,订购日期,金额),DELETE ON 订货.订购单 TO order_man
GRANT INSERT,UPDATE(数量,单价),DELETE ON 订货.订购明细 TO order_man WITH GRANT OPTION
 
--指定用户zhang为角色store_man的成员，指定wu为角色order_man的成员
sp_addrolemember 'store_man','zhang'
sp_addrolemember 'order_man','wu'
 
--授权用户zhang可以对”器件“表进行插入操作
GRANT INSERT ON 基础.器件 TO zhang WITH GRANT OPTION
 
--授权zhang可以对”器件“表进行删除操作
GRANT DELETE ON 基础.器件 TO zhang

在这之后，登陆zhang，给huang授权向基础.器件的插入、删除操作，插入可以成功，因为有 WITH GRANT OPTION。

但是，以wu的身份给huang授权：

GRANT INSERT,DELETE ON 订货.订购明细 TO huang

这时就不能成功了，因为wu的这个权限是从order_man身上继承下来的，继承要用AS短语说明以哪个角色进行授权，所以应该：

GRANT INSERT,DELETE ON 订货.订购明细 TO huang AS order_man

2 收回对象操作权限

REVOKE INSERT ON 基础.器件 FROM huang

同样，如果以wu的身份收回对huang的授权，也无法收回，因为wu是以order_man角色完成授权的，要加AS：

REVOKE INSERT ON 订货.订购明细 FROM huang AS order_man

9.4.2 架构权限管理

        架构：对象的容器

        通过用户管理对象，删除用户时，通过该用户建的表或对象都要删除，为安全管理带来很多麻烦

        通过架构管理对象的特点：

• 机构的所有权和架构范围内的安全对象可以转移；
• 对象可以在架构之间移动；
• 每个架构可以包含由多个数据库用户创建的对象；
• 多个数据库用户可以共享一个默认的架构dbo；
• 架构可以由任何数据库主题拥有（角色或角色）；
• 可以删除数据库用户而不删除对应架构中的对象。

 1 架构权限的授予

• 只有拥有架构的CONTROL权限才能够创建对象；

--用户wang以DBA身份将对架构”仓储“的INSERT权限授予zhang
GRANT INSERT ON SCHEMA :: 仓储 TO zhang
 
--用户wang以DBA身份授予zhang在”仓储“架构上的CONTROL权限
GRANT CONTROL ON SCHEMA :: 仓储 TO zhang

2 架构权限的收回

--收回用户zhang在架构”仓储“上的INSERT权限
REVOKE INSERT ON SCHEMA :: 仓储 FROM zhang

3 转移架构所有权

--将”器件“表的所有权传递给用户wu
ALTER AUTHORIZATION ON 基础.器件 TO wu
 
--将”基础“架构的所有权传递给用户tang
ALTER AUTHORIZATION ON SCHEMA :: 基础 TO tang
 
--将”器件“表的所有权传递给所属架构的所有者
ALTER AUTHORIZATION ON 基础.器件 TO SCHEMA OWNER

d 在架构之间移动对象

--将器件表从基础架构移动到仓储架构
ALTER SCHEMA 仓储 TRANSFER 基础.器件

9.4.3 授予语句权限

1 授予语句权限

• ALL：只有系统管理员可以使用；
• name_list：数据库用户名或角色（包括public）

授权可以成功，但是只有用户zhang在”仓储“架构下可以创建表，tang在”基础“架构下可以创建表，wu在两种架构下都无法创建表。

2 收回语句权限

3 总结

一个用户的权限包括：

• 自己固有的权限
• 其他用户直接授予的权限
• 作为角色成员继承到的权限

 9.4.4 禁止继承权限

执行DENY X TO A的效果

• 用户A之前从任何角色继承得到的权限X被取消；
• 用户A之后从任何角色都得不到权限X；
• 直接授予给用户的权限X被取消。

 主观题

10.1.2 SQL对事务的支持

1 开始事务

        BEGIN TRANSACTION

2 结束事务

        成功：COMMIT TRANSACTION

        失败：ROLLBACK TRANSACTION（回到事务起始点）

 3 事务保存点

        保存：SAVE TRANSACTION savepoint_name

        取消：ROLLBACK TRANSACTION savepoint_name

10.1.3 隐含事务与自动提交

10.2 并发控制

10.2.1 干扰问题（并发事务中的不一致问题）

1 丢失更新

2 未提交依赖（读”脏“数据）

3 不一致分析（不可重复读--更新）

4 幻像读问题（--插入/更新）

10.2.2 可串行性

• 看作是多个事务并发执行的正确性准则
• 如果多个事务并发执行和它们按照某种顺序串行执行效果等价，称作并发事务的可串行性或可串行化。

判断

• 一个事务能把数据库从一个正确状态转变成另一个正确状态，这个事务就是正确的；
• 按任何串行顺序执行多个事务也是正确的；
• 事务交叉执行是正确的，当且仅当其与串行执行过程等价，则事务是可串行化的。

        可串行性描述的是事务的隔离性。 

 10.2.3 封锁

• 并发控制就是要用正确的方式调度并发操作，使一个用户事务的执行不受其他事务的干扰，从而避免造成数据的不一致性；
• 并发控制的主要技术：封锁（Locking）；
• 封锁的基本思想：需要查询或更新数据时，先对数据进行封锁，以避免来自其他事务的干扰，即隔离其他事务
• 针对不同的干扰问题有不同的封锁机制。

1 封锁机制

a.共享封锁（读）

        读封锁，简称S锁； 在读数据的时候，不允许其他用户对该数据进行任何修改；

• 允许共享封锁、SELECT；
• 不允许独占封锁、更新封锁、INSERT、UPDATE、DELETE

b.独占封锁（修改操作，最严格）

        排他锁，简称X锁； 进行修改操作时，拒绝来自其他用户的任何封锁，但不拒绝一般的查询（即不能避免脏读）

• 允许SELECT（NO-LOCK）；
• 不允许共享操作、独占操作、更新操作

c.更新封锁（更新）

        防止其他用户在同一时刻修改同意记录

• 允许SELECT（NO-LOCK）、更新封锁（对同一表中的其他记录）；
• 不允许共享封锁、独占封锁、更新封锁（同一条记录）

有些封锁操作完成就释放，有些事务结束才释放

2 SQL Server中与封锁有关的命令

        WITH(<table_hint>)

关键字：

10.2.4 死锁

1 产生死锁的原因

2 避免死锁

• 相同顺序法：所有用户程序约定都按相同的顺序来封锁表
• 一次封锁法：为了完成一个事务，一次性封锁所需要的全部表

3 发现死锁和解决死锁

• 超时发：一个事务等待时间超过规定时限就认定发生了死锁（非常不靠谱，时限过长不能及时发现死锁，过段会将没发生死锁的事务误判为死锁）
• 等待图法：通过有向图判定是否可串行化，不能（有回路）就发生死锁了
• 解决死锁：自动使”年轻“的事务（完成工作量少的事务）先退回去，先让年老（完成工作量多的事务先执行，等”年老“的事务完成并释放封锁后，”年轻“的事务再重新执行。

 10.2.5 活死锁（活锁）

        没有发生死锁也有可能某个事务永远处于等待状态

避免活锁：先来先服务（排队）

10.2.6 隔离级别

1 设置隔离级别的命令

第十一章 数据库存储管理与数据恢复

11.1 数据库存储管理与存储优化

11.1.1 SQL Server数据库的存储结构

优化存储的手段

• 文件组：实现分类存储，可以把指定的数据存储到指定的物理文件；
• 分区：将超大型的表按指定的分区函数存储到指定的物理文件；
• 索引：提高查询性能的常用手段。

 11.1.2 调整数据库

--为”仓储订货“数据库增加一个5M大小的物理文件
ALTER DATABASE 仓储.订货
ADD FILE
(NAME = Test1dat2,
 FILENAME = 'C:\mssql\data\t1dat2.ndf',
 SIZE = 5MB,
 MAXSIZE = 100MB,
 FILEGROWTH = 5MB)
 
-- 将”仓储订货“数据库test1dat2所对应的物理文件增加到10M大小
ALTER DATABASE 仓储.订货
MODIFY FILE
(NAME = test1dat2,
 SIZE = 10MB)
 
-- 将“仓储订货”数据库的test1dat2文件改名为test1dat1
ALTER DATABASE 仓储.订货
MODIFY FILE
(NAME = Test1dat2,
 NEWNAME = Test1dat1)
 
-- 将“仓储订货”数据库的test1dat1文件删除
ALTER DATABASE 仓储订货
REMOVE FILE test1dat1

例：将“仓储订货”数据库的主数据文件orderdat/mdf（逻辑文件名是order_dat）移动到

d:\mssql\data目录下

实现步骤：

• 停止SQL Server服务；
• 手工将文件移动到指定目录；
• 启动SQL Server服务；
• 执行如下命令：

  ALTER DATABASE 仓储订货
  MODIFY FILE
  (NAME = order_dat,
   FILENAME = 'd:\mssql\dadta\orderdat.mdf')
  

•  再重新启动SQL Server服务

11.1.3 文件组

•  文件组是将物理存储文件分组；

• 文件组分为主文件组和用户定义文件组（FILEGROUP指定）两大类；

• 一个物理文件值只可以是一个文件组的成员；

• 文件组与日志无关，日志空间与数据空间是分开管理的；

• 使用文件组可以把指定数据存储到指定位置。

1 文件组的目的

• 解决硬件空间不够 ；

• 提高对数据库的访问速度。

2 文件组的概念 

例6 创建一个数据库MyDB，该数据库包括一个主数据文件、一个用户定义文件组和一个日志文件。

如何验证MyTable及其数据存储到了指定物理文件？

1. 向MyTable表插入几条数据；
2. 尝试用'ALTER DATABASE mydb REMOVE FILE MyDB_FG1_Dat1'，删除命令不能执行，说明物理文件不为空，MyTable表机器数据存储在物理文件中。
3. 删除MyTable表：’DROP TABLE MyTable‘
4. 再次删除文件组'ALTER DATABASE mydb REMOVE FILE MyDB_FG1_Dat1'，成功删除。

添加文件组 

 添加物理文件

11.1.4 分区

• 分区就是把一个大型表的数据放在不同位置，这样查询数据的时候，就去指定位置查找，从而提高查询速度；
• 分区正对大型表，好处是方便管理、提高效率（特别是提高并行处理能力） 

 1 建立分区表的步骤

11.1.5 索引

• 逐行扫描的搜索效率太低

11.2 备份与恢复

• 数据库安全性控制防范的是人，数据库恢复防范的是非人因素的计算机故障；
• 先备份，再故障排除后，再利用备份的数据进行恢复；
• 事务的原子性、一致性、持久性均需要恢复技术的的支持

11.2.1 故障类型

• 造成事务中断 的故障：掉电、应用程序故障等，  撤销执行了一半的事务即可；

• 存储介质故障： 解决方法：备份，在修复或更换磁盘后再恢复。

11.2.2 备份类型

• 双热机备份：作为服务器的两台计算机同时同步工作，一个主，一个从，连哥哥服务器的数据每时每刻都同步；

• 双工备份：一台计算机上有双路存储通道（两个控制器和两组存储介质，每组存储介质在各自控制器控制下），计算机工作时两组存储介质同步更新；

• 镜像：一个存储控制器下有两组存储介质，以镜像方式工作，具有相同数据；

• 冗余磁盘阵列：RAID0，RAID1，RAID2；

• 数据库备份技术（数据库管理系统功能，前面几个都是操作系统的）：将数据库存储介质上的数据备份到脱机的存储介质上。

11.2.3 日志的概念

• 备份是定期的，不是定时的，因此不能完全恢复数据库，只能恢复到制作备份的那一时刻；

• 日志是实时的，是对备份的补充，记录下所有对数据库的更新操作；

• 为了保证日志的安全，应该将日志和主数据库安排在不同的存储设备上。

11.2.4 恢复模型

• 简单恢复模型 ：恢复到最新的备份，只能做数据库备份，不能做日志备份，会丢失数据；

• 完全恢复模型：恢复到故障点状态，使用数据库备份和事务日志备份提供对介质故障的完全防范。

'ALTER DATABASE database_name SET RECOVERY FULL' 修改为完全恢复。

恢复的时候不允许操作，备份的时候允许操作。 

11.2.5 备份和恢复策略

1 备份的类型

• 全备份：完整地备份整个数据库，是恢复的基线；
• 增量备份（差异备份）：备份自上次全备份依赖更改的数据，以全备份为基线；
• 事务日志备份：以上次备份为基准点；
• 文件和文件组备份

2 动态备份和静态备份

• 动态备份：在线备份，备份时不中断数据库的运行；
• 静态备份：离线或脱机备份，备份时关闭数据库服务器。

 11.2.6 备份操作

例子

系统数据库也要备份，必须经常性地、定期地备份master数据库（只能进行全备份）

11.2.7 恢复或还原

期末加油！