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

怀念夏天 · 2024-7-12 05:01:50

所有内容均总结自自中国大学慕课 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.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读取一条记录时，

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

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 概念、逻辑、物理数据模型

概念数据模型：确定实体、实体间的关系
逻辑数据模型：确定实体的属性
物理数据模型：确定关系、属性、联系怎样映像到具体实现，数据类型等

第三章关系数据库基础

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. 主数据文件

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

2. 次数据文件

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

3. 事务日志文件

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

3.2 关系数据模型

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

1. 三个要素
a.关系数据结构：
        实体及实体与实体之间的联系均用关系来表现，关系就是数二维表；
b.关系操纵聚集：
        关系操纵可以用代数操纵（通过代数对关系的运算来表达查询要求的方式）和逻辑方式（通过关系演算、用谓词表达对关系的查询要求的方法）来表现；
        关系数据语言分三类：关系代数语言、关系演算语言、具有双重特点的SQL语言。
c.关系完整性约束：
分三类：实体完整性、参照完整性、用户自定义完整性。
2.关系的形式定义
a.

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

b.
须要说明两点：

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

3.2.2 关系的根天性质

X

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

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

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

3.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 为什么要讨论函数依靠

数据冗余标题;
数据更新标题;
数据插入标题;
数据删除标题.

4.1.4 模式分解(拆表)

定义
把一个关系模式分解成两个或多个关系模式，在分解的过程中消除那些“不良”的函数依靠，从而获得好的关系模式。
比方：
仓库（仓库号，地点，设备号，设备名，库存数量）
-----> 分解为：仓库（仓库号，地点）
设备（设备号，设备名）
库存（仓库号，设备号，库存数量）。
要求:

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

4.2 函数依靠的推理规则

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

自反律、增广率、传递律

定理：Amstrong公理是精确的
Amstrong公理的推论及精确性
合并、分解、伪传递

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

4.2.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的最小覆盖。。

1	因为右部都是单一属性，以是不须要用分解规则化简；
2	化简左侧使得每一个函数依靠的左部没有多余属性等价变换后的函数依靠集为：{B→E,DE→B,B→C,C→E,E→A}
3	令G=F-{B→E}={DE→B,B→C,C→E,E→A}，则 ={A,B,C,E} E F与G等价，即B→E是多余的 F=G={DE→B,B→C,C→E,E→A}
4	令G=F-{DE→B}={B→C,C→E,E→A}，则 ={A,D,E} B DE→B不是多余函数依靠，不能去除
5	令G=F-{B→C}={DE→B,C→E,E→A}，则 = {B} C B→C不是多余函数依靠，不能去除
6	同样，检验C→E,E→A都不是多余函数依靠，不能去除。 F的最小函数依靠是：{DE→B,B→C,C→E,E→A}

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 (都会, Φ)}

显然不满足函数依靠，都是Φ，无法推出F；

∵ R1∩R2=Φ
∴ 及不存在R1∩R2→R1-R2，也不存在R1 ∩ R2 → R2 – R1
∴ ρ1不满足无损连接

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

∵ R1∩R2={职工号}，R1-R2={仓库号}
又 ∵ 存在公共号→仓库号
∴ 存在R1∩R2→R1-R2
∴ 满足无损链连接；

由ρ2无法推导出仓库号→都会
∴ ρ2不满足保持函数依靠

C.ρ3= {R1 ({职工号,仓库号},{职工号→仓库号}), R2 ({仓库号,都会},{仓库号→都会})}

∵ R1∩R2={仓库号}，R2-R1={都会}
又∵ 存在仓库号→都会
∴ 存在R1∩R2→R2-R1
∴ 满足无损连接；

由ρ3可以推导出职工号→仓库号，仓库号→都会
∴ρ3还满足保持函数依靠。

  例2.设有关系模式R(U,F)，U={A,B,C,D},F={AD→C, B→D }, ρ={R1 (A,B,C),R2 (B,D)} 为R的一个分解,那么分解ρ为：
          A 保持函数依靠和无损连接
          B 无损连接，但不保持函数依靠
          C 不是无损连接，但保持函数依靠
          D 即不是无损连接，也不保持函数依靠

∵ R1∩R2={B}，R1-R2={A，C}，R2-R1={D}
又∵ 存在B→D
∴ 存在R1∩R2→R2-R1
∴ 满足无损连接；

由于ρ中，A、D、C三个并没有放在一个函数依靠中，因此AD→C丢失了
∴ ρ不满足保持函数依靠

例3. 设有关系模式R(U,F)，U={A,B,C,D},F={A→B, C→D },ρ={={R1(A,B),R2(C,D)}为R的一个分解，那么分解ρ为：

∵ R1∩R2=Φ，R1-R2={A，B}，R2-R1={C，D}
∴ Φ 无法决定R1-R2或R2-R1
∴ 不满足无损链接；

满足保持函数依靠。

紧张结论：

如果要求分解既 保持函数依靠 又具有 无损连接的特性 ，那么分解 可以达到 3NF ，但是不一定能达到 BCNF 。 4.4.2 3NF无损链接和保持函数依靠算法

例题
例1.设有关系模式R(U,F)，其中U={A,B,C}，F={AB→C,B→C}，请使用算法4.3举行模式分解

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	U0 X，将R0从τ中去掉
8	分解效果：τ={R1({B,C},{B→C}),Rx({A,B},Φ)}

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

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

解：

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

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	X U2,将Rx去掉，即τ={({C,T}, {C→T}) , ({C,S,G}, {CS→G}) , ({S,N}, {S→N})}
8	最终的τ就是分解效果

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

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

  5.1.1 数据库设计的概念

    描述了现实世界的数据管理需求。
    这里只关注侠义的，即数据模型的设计。
  5.1.2 数据库设计的一样寻常步骤

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

  5.2 逻辑数据模型设计

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

  确定各个关系模式的主关键字、外部关键字、属性的约束，三种完整性约束，设计视图。
  5.2.2 把E-R模型转换为关系数据模型

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

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

例1 1：1联系

或者↓

亦或者↓

例2 1：n联系

例3 m：n联系

例4 单选
把 E-R 模型转换成关系模型时，其中两个一对多联系的实体（）

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 定义架构

定义：
1. CREATE SCHEMA shema_name [AUTHORIATION owner_name]
复制代码

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

删除：
1. 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：

char定长，varchar不定长；
char()和varchar()中的n指的是字节数，一个英文1字节，一个中文2-4字符；
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：

INSERT向所有列插入值的时间，不用写column_list，但是，如果只由部分列有值，要按顺序写出给谁插
当插入的值是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 根本格式

SELECT [ALL|DISTINCT] {*|< 表达式 >,…,< 表达式 >}

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

FROM < 数据来源 >

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

WHERE < 逻辑表达式 >

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

GROUPBY < 列名 >[,< 列名 >…] [HAVING < 谓词 >]

用于对查询效果举行分组，也可以用它们举行分组汇总（对查询效果举行计算汇总，如SUM、COUNT、MAX）

ORDER BY < 列名 > [ASC|DESC] ,[ < 列名 > [ASC|DESC]]…

排序，可以根据一或多列来举行排序

7.2 简朴查询

7.2.1 简朴无条件查询

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

复制代码

7.2.2 简朴条件查询

  如这张图（简朴的大于小于 BETWEEN IS NULL就不说了）
  1 字符串匹配查询

    格式为：列名[NOT] LIKE 匹配串；
    匹配串类型：可以是字符串常量，也可以含有通配符；
    通配符种类：

%（百分号）	匹配0或多个字符
_（下划线）	匹配1个字符
[]	匹配括号中的字符，如[a-f]=[abcdef]
[^]	不匹配括号中的字符

字符串，LIKE后面的匹配串记着加' '。

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

复制代码

2 使用[NOT] IN表达式的查询

以下两个SQL语句等价

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

复制代码

7.2.3 存储查询效果

使用INTO短语将查询效果存储到指定的新表中

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

复制代码

7.2.4 查询效果的排序

ORDER BY order_expression[ASC|DESC]（默认ASC升序）

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

复制代码

！如果ORDER BY column1, column2 DESC，指的是先按column1升序，再按column2降序！

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

复制代码

7.2.5 TOP短语的作用

TOP(expression) [PERCENT] [WITH TIES]
其中expression指出返回多少效果，如果同时选用了PERCENT则按百分比举行计算，如果选用WITH TIES则会取并列，末了返回的数量多于expression。

  7.3 连接查询

  7.3.1 连接查询的语法格式

内连接：

7.3.2 一样寻常连接

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

复制代码

如果连接的列名相同，那么就须要表明哪个表的列
7.3.3 多个表的连接

  7.3.4 自连接查询

  只是逻辑上通过取别名的方法，来把一张表分成两张，物理上还是一张表。

  7.3.5 外连接查询

  左右全连接要理解！

  7.4 分组及汇总查询

  7.4.1 聚合函数与汇总查询

  1 常见聚合函数

    COUNT（计数）、AVG（平均数）、MIN（最小值）、MAX（最大值）、SUM（求和）
  7.4.2 一样寻常汇总查询

  1 根本格式

2 举例

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

复制代码

3 COUNT(*)和COUNT(<列名>)的区别

          在聚合函数碰到空值的时间，除COUNT(*)外，其他都跳过空值而只处置惩罚非空值。
  7.4.3 使用GROUP BY的分组汇总查询

  1 格式

2 例子

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

复制代码

3 使用GROUP BY + HAVING 的分组汇总查询举例

这里可以看出来，HAVING的用法和WHERE很像，那么可以使用WHERE来代替吗？

7.5 嵌套查询

7.5.1 普通嵌套查询

1 两种形式：

Tip：只有确切知道子查询返回的是单值时，才可以使用比较运算符（>、<、=、>=、<=、<>）。
2 例子

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

复制代码

7.5.2 使用量词的嵌套查询

1 格式

2 例子

7.5.3 内、外层互相关嵌套查询

一样寻常的嵌套查询都是外层查询依靠于内层查询的效果，而内层查询与外层查询无关。
事实上，有时也须要内、外层互相关的查询，即内层查询须要外层查询提供数据，而外层查询又依靠内层查询的效果。

7.5.4 使用EXISTS的嵌套查询

Tip：EXISTS或NOT EXISTS是用来检查在子查询中是否有效果返回的

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 视图的作用

视图可用作安全机制；
侧重于特定命据；
简化数据操纵；
可以包管概念数据独立性

第八章数据库编程基础

8.1  Transact-SQL

变量声明
        DECLARE @<变量名> <数据类型> [,@<变量名> <数据类型>...]
         注意：变量名前必须有@前缀，以便和关系的属性名相区分
赋值
         SELECT @local_variable=expression[,@local_variable=expression…]         FROM <表名>|<视图名> …

8.2 游标与SQL的宿主使用

  8.2.1 游标的使用

  1 声明游标（文件）

        DECLARE <游标名> CURSOR FOR <SELECT-查询>
游标可以看作是一个临时存储或临时文件，它的内容就是SELECT语句的查询效果。

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

复制代码

2 打开游标

OPEN < 游标名 > 实验该语句意味着实验CURSOR语句中的SELECT查询，并使游标指针指向查询效果的第一条记录。只能打开已经声明但还没有打开的游标。

OPEN wh_cursor

复制代码

3 从游标中读数据

FETCH <游标名 > [INTO < 主变量 1 >, <主变量 2 > …] 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约束只能定义列级或表级约束，表间的任何约束或业务规则都必须通过定义触发器来实现。
使用DML触发器实现表之间逼迫业务规则“如果当地有供应商则订购单不许发往异地”。

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 授权时是作为某个用户或某个角色在行使职权。

例题：仓储订货”数据库的安全控制办理方案
一个 DBA

除职工工资所有信息对所有用户均可查询
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被取消。

主观题

设 wang 为“门生”数据库用户，系统管理员兼数据库管理员sa创建 role1 角色，并指定 wang 为角色成员， sa 授予角色role1查询表 t7 的权限；除此之外， sa 授予 wang 如下权限：查询表t1 、 t2 ，向表 t3 、 t4 插入数据；更新表 t5 、 t6 ，随后禁止wang对 t5 举行更新；撤销角色 role1 对表 t7 的查询权限再授权的权利。叨教最终wang 具有哪些权限？

  第十章事务管理与并发控制

  10.1 事务管理

  10.1.1 事务的性质（ACID特性）

原子性（Atomicity）：一个事务要么做完，要么不做，不可分割
同等性（Consistency）：实验前后，数据库从一种同等变成另一种同等
隔离性（Isolation）：事务并发实验也能保持原子性和同等性（由并发控制包管）
持久性（Durability）：事务一旦成功，施加的更新是永世的

  10.1.2 SQL对事务的支持

1 开始事务

BEGIN TRANSACTION
2 结束事务

成功：COMMIT TRANSACTION
        失败：ROLLBACK TRANSACTION（回到事务起始点）
3 事务保存点

保存：SAVE TRANSACTION savepoint_name
取消：ROLLBACK TRANSACTION savepoint_name
事务和步伐是两个概念。一样寻常来讲，一个步伐中包含多个事务。一个事  务可以是一条SQL语句、一组SQL语句或整个步伐。  10.1.3 隐含事务与自动提交

10.2 并发控制

10.2.1 干扰标题（并发事务中的不同等标题）

1 丢失更新

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

查询一个已经被其他事务更新、但尚未提交的元组，将会引起未提交依靠标题。

3 不同等分析（不可重复读--更新）

连续两次或多次读数据举行校验和分析，效果由于其他事务的干扰，使得前后效果不同等，从而产生校验错误（即不同等的分析）。

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

与不同等分析标题有关，当事务A 读数据时，事务 B 在对同一个关系举行插入或删除操纵，这时事务A 再读同一条件的元组时，会发现神秘地多出了一些元组或丢失了一些元组，把这种征象称作幻象读

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>)
关键字：

TABLOCK	对表共享封锁，读完立刻释放	可以避免脏读，但不具有可重复读
HOLDLOCK	与TABLOCK一起用，将共享锁持续到事务结束	包管可重复读
NOLOCK	不举行封锁，仅应用于SELECT	大概会读取未提交的数据，导致脏读
TABLOCKX	实施独占封锁
UPDLOCK	实施更新封锁	可以对其他记录实施共享封锁，但是不允许对表实施共享封锁和独占封锁

10.2.4 死锁

1 产生死锁的原因

2 避免死锁

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

3 发现死锁息争决死锁

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

10.2.5 活死锁（活锁）

没有发生死锁也有大概某个事务永远处于期待状态

避免活锁：先来先服务（排队）
10.2.6 隔离级别

隔离级别	封锁	脏读	不可重复读	幻象	丢失更新
未提交读（READ UNCOMMITTED）	NOLOCK	是	是	是	是
提交读（READ COMMITTED）	TABLOCK	否	是	是	是
可重复读（REPEATABLE READ）	TABLOCK+HOLDLOCK	否	否	否	是
可串行化（SERIALIZABLE）	TABLOCKX 或UPDLOCK	否	否	否	否

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服务；
实验如下命令：
1. ALTER DATABASE 仓储订货
2. MODIFY FILE
3. (NAME = order_dat,
4. FILENAME = 'd:\mssql\dadta\orderdat.mdf')
复制代码
再重新启动SQL Server服务

11.1.3 文件组

文件组是将物理存储文件分组；
文件组分为主文件组和用户定义文件组（FILEGROUP指定）两大类；
一个物理文件值只可以是一个文件组的成员；
文件组与日志无关，日志空间与数据空间是分开管理的；
使用文件组可以把指定命据存储到指定位置。

1 文件组的目标

办理硬件空间不够；
进步对数据库的访问速率。

2 文件组的概念

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

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

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

添加文件组