数据库技术

一只小小小杨

于 2023-03-03 22:24:02 发布

阅读量1.3k

点赞数

文章标签：经验分享数据库架构数据库 sqlserver

本文链接：https://blog.csdn.net/weixin_61694871/article/details/129328283

版权

数据库技术

文章目录

数据库技术

1.数据库应用系统开发方法

1.1 数据库应用系统生命周期

1.1.1 软件工程与软件开发方法

瀑布模型（也称为软件生命周期模型）

这个模型把软件开发的全过程定义为六个阶段：
- 项目规划
- 系统分析
- 总体设计
- 详细设计
- 编码测试与集成测试
- 运行维护阶段
快速原型模型

划分为五个阶段：
- 快速分析
- 设计构造原型
- 运行原型
- 评估原型
- 改进原型
螺旋模型

划分为四个阶段（也称四个象限）
- 项目规划
- 风险评估
- 工程实现
- 用户评估
软件工程的目标是：在给定成本、进度的前提下，开发出满足用户需求并具有可修改性、有效性、可靠性、可维护性、可重用性、可适应性、可移植性、可追踪性和可互操作性。

1.1.2 DBAS生命周期模型

数据库应用系统（DBAS）是一类典型的面向数据管理和数据处理的复杂软件系统。

DBAS的生命周期：

项目规划
需求分析
系统设计：概念设计、逻辑设计、物理设计
实现与部署
运行与维护

1.2 规划与分析

规划与分析是数据库应用系统生命周期的起点。其工作内容主要包括：

系统规划与定义

（1）任务陈述

（2）确定任务目标

（3）确定系统范围和边界

（4）确定用户视图
可行性分析

1.经济可行性

2.技术可行性

3.操作可行性

4.开发方案选择
项目规划
- 确定项目的目标和范围
- 根据DBAS软件开发模型，分解和定义整个项目包括的工作活动和任务
- 估算完成项目的规模及所需各种资源
- 指定合理的DBAS项目计划

1.3 需求分析

1.3.1 数据需求分析

数据需求分析是从对数据进行组织与存储的角度，从用户视图出发，分析与辨识应用领域所管理的各类数据项和数据结构，形成数据字典的主要内容。

数据字典包括数据项、数据结构、数据流、数据存储和处理过程五个部分。数据项是数据的最小组成单位，若干个数据项可以组成一个数据结构。

1.3.2 功能需求分析

数据处理需求分析
数据处理需求分析结果可以表示为数据流图（DFD)或者事务规范。
- 事务规范包括一下几方面的描述：
  
  （1）事务名称
  
  （2）事务描述。指对事务功能、性能、完整性约束等方面的描述
  
  （3）事务所访问数据项
  
  （4）事务用户。指启动该事务执行的事件/用户
业务规则需求分析

1.3.3 性能需求分析

DBAS应具有的性能指标：

（1）数据操作响应时间，或数据访问响应时间。

（2）系统吞吐量。指系统在单位时间内可以完成的数据库事务或数据查询的数量。

（3）允许并发访问的最大用户数。

（4）每TPS代价值。用于衡量系统性价比的指标。

影响DBAS性能的主要因素：

（1）系统硬件资源。如CPU数量与速度、I/O系统容量与速度、内存大小、内存缓冲区大小等。

（2）网络通信设备性能

（3）操作系统环境

（4）数据库的逻辑设计和物理设计质量

（ 5）DBMS的配置和性能

（6）数据库应用程序自身

1.3.4 其他需求分析

存储需求分析
安全性需求分析
备份和恢复需求分析

1.4 系统设计

数据库应用系统设计包括概念设计、逻辑设计、物理设计三个步骤。

1.4.1 概念设计

数据库应用系统的概念设计包括数据库概念模型设计和系统总体设计。

数据库概念模型设计
系统总体设计

1.4.2 逻辑设计

数据库逻辑结构设计
应用程序概要设计
数据库事务概要设计

1.4.3 物理设计

数据库物理结构设计
数据库事务详细设计
应用程序详细设计

1.5 实现与部署

DBAS实现与部署包括以下工作内容：

（1）建立数据库结构

（2）数据加载

（3）事务和应用程序的编码及测试

（4）系统集成、测试与试运行

（5）系统部署

1.6 运行管理与维护

主要工作内容是：

（1）日常维护

（2）系统监控与分析

（3）系统性能优化调整

（4）系统进化（升级）等

1.7 小结

数据库应用系统是面向数据管理和处理领域的复杂软件系统。根据软件工程基本原理和数据库系统三级模式结构，提出了DBAS生命周期模型，将DBAS生命周期划分为规划与分析、需求分析、系统设计、系统实现与部署、运行维护五个阶段，并规定了各阶段的主要目标、工作内容和采用的关键技术。

2.需求分析

2.1 需求分析

2.1.1 需求分析的概念和意义

概念：所谓需求分析，就是对待开发的系统要做什么，完成什么功能的全面描述。

意义：以使用者和开发人员都容易理解的文档形式提供一个关于目标系统所完成的全部功能及性能等需求的完整描述，以保证目标系统后续阶段，如系统设计、编码、测试等阶段工作的顺利完成，为最终开发出一个满意度较高的系统打下基础。

需求获取的困难性：

软件功能复杂
需求的可变性
软件产品的不可见性

需求分析的结果通常以模型形式展示，如用DFD图、IDEFO图等建模工具和方法描述系统的信息流、功能结构及完成各功能需求的数据，通常把描述系统功能的这类模型称为功能模型。

2.1.2 需求获取的方法

需求获取的方法：

面谈
实地观察
问卷调查
查阅资料

2.1.3 需求分析过程

需求分析的过程：

标识问题
建立需求模型
描述需求
确认需求

2.2 需求分析方法

2.2.1 需求分析方法概述

目前在信息系统的需求分析中广为使用的结构化分析与功能建模方法主要有DFD、IDEFO等。

2.2.2 DFD需求建模方法

DFD建模方法，也被称为过程建模和功能建模方法。DFD建模方法的核心是数据流。

DFD方法的基本元素
- 数据流（Data Flow）。数据流用一个箭头描述数据的流向，箭头上标注的内容可以是信息说明或数据项。
- 处理（Process）。表示对数据进行的加工和变换，在图中用矩形框表示。
- 数据存储。表示用数据库形式（或文件形式）存储的数据。
- 外部项（也称数据源或数据终点）。描述系统数据的提供者或数据的使用者。在图中用圆角框或平行四边形表示。
DFD图

DFD图采用自顶向下逐步细化的结构化分析方法表示目标系统。其层次结构图如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NgsMzAi0-1677853054033)(C:\Users\萧瑟\AppData\Roaming\Typora\typora-user-images\image-20230202162919684.png)]
DFD建模过程

建立DFD图的目的是描述系统的功能需求。建模的步骤如下：
- 明确目标，确定系统范围
- 建立顶层DFD图
- 构建第一层DFD分解图
- 开发DFD层次结构图
- 检查确认DFD图

2.2.3 其他需求建模方法

IDEFO方法

组成IDEFO图的基本元素是矩形框和箭头。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ufceMT4m-1677853054034)(C:\Users\萧瑟\AppData\Roaming\Typora\typora-user-images\image-20230202165210821.png)]

左边的输入箭头表示完成活动需要的数据；上方的控制箭头描述了影响这个活动执行的事件或约束条件；右边的输出箭头说明由活动产生的结果及信息，下方进入的机制箭头表示实施该活动的物理手段或完成活动需要的资源（计算机系统、人或组织）。
UML方法（统一建模语言）

统一建模语言(Unified Modeling Language，UML)是一种为面向对象系统的产品进行说明、可视化和编制文档的一种标准语言，是非专利的第三代建模和规约语言。UML是面向对象设计的建模工具，独立于任何具体程序设计语言。

3.数据库结构设计

3.1 数据库概念设计

数据库概念设计主要解决数据需求，即如何准确地理解数据需求，真实地把应用领域中要处理的数据组织、定义描述清楚，一支持数据库设计后续阶段的工作。数据库概念设计阶段通常采用自底向上的方式。

3.1.1 概念设计的任务

数据库概念设计阶段的目标：

定义和描述应用领域涉及的数据范围
获取应用领域或问题域的信息模型
描述清楚数据的属性特征
描述清楚数据之间的关系
定义和描述数据的约束
说明数据的安全性要求
支持用户的各种数据处理需求
保证信息模型方便地转换成数据库的逻辑结构（数据库模式），同时也便于为用户解释。

3.1.2 概念设计的依据及过程

概念设计的依据

数据库概念设计的依据是需求分析阶段的文档，包括需求说明书、功能模型（数据流程图或IDEFO图）以及在需求分析阶段收集到的应用领域或问题域中的各类报表等。
概念设计的过程

（1）明确建模目标

（2）定义实体集

（3）定义联系。联系用于描述实体集之间的关联关系。

（4）建立信息模型。如ER模型（实体联系模型）。

（5）确定实体集属性

（6）对信息模型进行集成与优化

3.1.3 数据建模方法

数据建模的相关方法：ER建模方法、IDEF1X、UML中的类图等。

ER建模方法

ER模型的相关概念：

（1）实体或实例（Instance）。实体指客观存在并可相互区分的事物（也称为实体集实例或者实例）。实体可以是一个具体的人或者事物，如张三、一辆汽车等，也可以是抽象的事件或概念，如学生的一次选课等。

（2）实体集。实体集表示一个现实的和抽象事物的集合，这些事物必须具有相同的属性或特征。例如，学生实体集指全部学生的集合。

（3）属性。属性用于描述一个实体集的性质和特征。例如，学生Student实体集的属性有学号（Sno）、姓名（Sname）、性别（Sex）、出生年月（Birth）等。人们用一组属性来描述一个实体集的属性特征。

（4）码。实体集中能唯一标识每一个实例的属性或属性组织称为该实体集的码。例如，在学生实体集中，学号就是码，它可以用来标识学生集合中的每一个对象，而姓名不能，因为姓名可能有重名。

（5）联系。联系描述现实世界中实物之间的关系。例如，一名学生选了一门课程，一个供应商供应多种零件等。

现实世界事物之间的联系可归纳为三类：

（1）一对一联系（1:1）。若对于实体集A中的每一个实例，实体集B中至多有一个实例与之联系，反之亦然，则称实体集A和实体集B具有1:1联系。

例如：系和系主任两个实体集就存在1：1的联系，因为一个系只有一个系主任，而一个系主任只负责管理一个系。

（2）一对多联系（1：n）。若对于实体集A中的每一个实例，实体集B中有n个实例（n>=0）与之联系，而对于实体集B中的每个实例，实体集A中至多有一个实例与之联系，则称实体集A与实体集B存在1：n的联系。

例如：系和学生两个实体集之间就存在1：n的联系，因为一个系招收若干名学生，而每一名学生只属于一个系。

（3）多对多联系（m:n）。若对于实体集A中的每一个实例，实体集B中有n个实例（n>=）与之联系，而对于实体集B中的每一个实例，实体集A中有m个实例（m>=）与之联系，则称实体集A与实体集B存在m:n的联系。

例如：学生和课程两个实体集之间就存在着m：n的联系，因为一名学生可以选修多门课程，而一门课程也可以被多名学生选修。

注：ER方法中用矩形框表示实体集，矩形框内写上实体集的名称。用菱形框表示联系，菱形框内写上联系名。用椭圆或圆角矩形框表示实体集的属性，属性的名字写在椭圆或圆角矩形框中。

IDEF1X建模方法

IDEF1X与前面介绍的IDEF0是一个系列的建模工具。IDEF0侧重描述系统功能需求，被称为功能建模方法。IDEF1X侧重分析、抽象和概括应用领域中的数据需求，被称为数据建模方法。

IDEF1X模型有如下建模元素：

（1）实体集

IDEF1X用矩形框来表示独立实体集，用加了圆角的矩形框来表示从属实体集。

（2）联系
- 标定型联系
- 非标定型联系
- 分类联系
- 非确定联系

3.2 数据库逻辑设计

3.2.1 概述

数据库逻辑设计的任务是把数据库概念设计的结果（ER模型），转换为具体的数据库管理系统支持的数据模型。

数据库逻辑设计的依据和阶段如下图所示：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9oZ6j4Gu-1677853054034)(C:\Users\萧瑟\AppData\Roaming\Typora\typora-user-images\image-20230203145043219.png)]

3.3 数据库物理设计

数据库物理结构是影响数据库系统功能和性能的重要因素。

3.3.1 物理设计概述

数据库物理设计的目的是将数据的逻辑描述转换为实现技术规范，其目的是设计数据存储方案，以便提供足够好的性能并确保数据库数据的完整性、安全性和可恢复性。

在数据库物理设计阶段，需要进一步考虑应用数据在实际数据库管理系统和操作系统平台下的具体存储结构和数据存取方式，即数据库的物理结构。

3.3.2 数据库的物理结构

数据库中的应用数据是以文件形式存储在外设存储介质（如磁盘）上的，文件在逻辑上被组织成记录的序列，也就是说，每个DB文件可以看作是逻辑记录的集合。

一个文件在磁盘上占有一定的物理存储空间，文件中的每个逻辑记录被映射存储到某个特定的磁盘上。一个文件在物理上可以看作是由存放文件记录的一系列磁盘块组成的，称之为物理文件。

文件的逻辑记录与磁盘块见的映射关系是由操作系统或DBMS来管理的。当需要对一个文件逻辑记录查询、插入、删除、修改时，首先要根据这种映射关系找到该逻辑记录所在的磁盘块，然后进行相应的操作。

从数据库物理结构角度需要解决如下的几个问题：

（1）文件的组织

（2）文件的结构

（3）文件的存取

（4）索引技术

3.3.3 索引

索引技术

索引技术（Indexing）是一种快速数据访问技术，它将一个文件的每个记录在某个或某些域（或称为属性）上的取值与该记录的物理地址直接联系起来，提供一种根据记录域的取值快速访问文件记录的机制。

索引技术的关键是建立记录域取值到记录的物理地址间的映射关系，这种映射关系称为索引（Index）。

索引为性能所带来的好处是有代价的，因为索引在数据库中会占用一定的存储空间。另外，在对数据进行插入、更改和删除操作时，为了使索引与数据保持一致，还需要对索引进行维护，索引的维护是需要花费时间的。因此，在设计和创建索引时，应确保对性能的提高程度大于存储空间和处理资源方面的代价。
索引技术分类

根据索引的实现方式，索引技术被分成两大类：有序索引和散列索引。
有序索引

数据文件和索引文件是有序索引技术中的两个主体，数据文件也称为被索引文件或主文件。当需求采用有序索引机制快速访问一个数据文件时，首先要为该数据文件建立一个索引文件，索引文件时索引记录或索引项的集合。

索引建立在查找码上。如果对一个数据文件需要从几个方面去查询文件记录，可以定义多个查找码，针对每个查找码建立相应的索引记录。因此，一个数据文件可以有多个查找码和多个索引文件。

有序索引有多种多样的形式，下面介绍几类主要的有序索引机器特点：

（1）聚集索引和非聚集索引

对数据文件和它的一个特定的索引文件，如果数据文件中数据记录的排列顺序与索引文件中索引项的排列顺序一致，或者说索引文件按照其查找码指定的顺序与数据文件中数据记录的排列顺序一致，则称该索引文件为聚集索引（Clustering Index）；否则，称该索引文件为非聚集索引（Non-clustering Index）。

注：在一个数据文件上除了可以建立一个聚集索引外，还可以建立多个非聚集索引。

如果一个数据文件按照某个查找码组织为顺序文件，同时在该数据文件上又建立有聚集索引，则该数据文件称为索引顺序文件（Index-Sequential File）。对索引顺序文件而言，既可以对数据记录进行顺序访问（Sequential Access），又可以根据数据文件上的聚集索引对数据记录进行直接访问（Direct Access）。

（2）稠密索引和稀疏索引

如果数据文件中的每个查找码值在索引文件中都对应一个索引记录，则该索引称为稠密索引（Dense Index）。如果只是一部分查找码的值有对应的索引记录，则该索引称为稀疏索引（Sparse Index）。

注：在带稠密索引的数据文件中，查找速度较快。

（3）主索引和辅索引

在数据文件的主码属性集上建立的索引称为主索引（Primary Index）。在数据文件的非主属性上建立的索引称为辅索引（Secondary Index）。

（4）唯一索引

唯一索引可以确保索引列不包含重复的值。在多列唯一索引的情况下，可以确保索引列中的每个值的组合都是唯一的。

（5）单层索引和多层索引

单层索引也称为线性索引，其特点是索引项根据键值在索引文件中顺序排列，组织成一堆线性结构，每个索引项直接指向数据文件中的数据记录。

当数据文件很大时，即使采用稀疏索引，建成的索引文件也会很大，只能以顺序文件形式存放在磁盘上。如果需要读取某个索引项，必须读取磁盘块，导致对索引文件本身的访问效率非常低。为解决这个问题，可以以读索引文件中的索引项本身再建立一级稀疏索引，组成二层索引结构，用于快速查找索引项。对于大数据量中的数据记录，可以建立多层树型索引结构来快速定位。多层索引的典型例子是数据库系统中广泛应用的B树和B+树索引。

3.3.4 数据库物理设计

物理设计内容

数据库物理结构设计是在具体的硬件环境、操作系统和DBMS约束下，根据数据库逻辑设计结果设计合适的数据库物理结构。其目标是得到存储空间占用少、数据访问效率高和维护代价低的数据库物理模式。

数据库物理设计主要包括以下环节：

（1）数据库逻辑模型描述

（2）文件组织与存取设计

（3）数据分布设计

（4）确定系统配置

（5）物理模式评估
数据库逻辑模式描述

数据库物理设计需要根据数据库逻辑结构信息设计目标DBMS平台可支持的关系表（这里称为基本表）对的模式信息，这些模式信息代表了所要开发的具体目标数据库的结构。这个过程称为数据库逻辑模式描述，主要包括以下设计内容。

（1）面向目标数据库描述基本表和视图。采用目标DBMS所支持的建表语法，描述基本表及满足应用要求的完整性约束。

（2）设计基本表业务规则。使用CREATE TABLE语句可以定义主码、外码、值域、空值等较为简单的、面向模型的完整性约束。
DB文件组织与存取设计

一个好的数据库物理模式应当满足存储空间占用少、数据访问效率高和维护代价低的要求。为了进行有效的数据库文件组织和存取路径设计，必须分析和理解数据库事务的数据访问特性。

事务分析可以按照如下步骤进行：

（1）使用事务-基本表交叉引用矩阵，分析系统内（部分重要的）数据库事务对各个基本表的访问情况，确定事务访问了哪些基本表，对这些基本表执行了何种操作，并进一步分析个操作涉及的基本表属性。

（2）估计各事务的执行效率，即单位时间内事务的执行次数。

（3）对每张基本表，汇总所有作用于该表上的各事务的操作频率信息，得到如下数据访问估计信息：该表是否被频繁访问，该表中哪些属性列的访问频率较高和作用于这些属性上的操作类型和查询条件类型（等值查询、范围查询）。

根据事务数据访问特性分析结果，可以对基本表设计成更有效的文件组织和索引方式。

下面给出一些为基本表选择合适的文件结构的原则：

（1）如果数据库中的一个基本表中的数据量很少，并且插入、删除、更新等操作非常频繁，该基本表可以采用堆文件组织方式。因为堆文件无须建立索引，维护代价非常低。虽然堆文件的数据访问效率较低，但在数据量很少时，定位文件记录的时间非常短。

当需要向新创建的基本表批量加载数据时，可将表的文件结构先选为堆文件，向表中加载数据后再重新调整文件结构，如改为数据查询效率更高的B+树文件。

（2）顺序文件支持基于查找码的顺序访问，也支持快速的二分查找。如果用户的查询条件定义在查找码上，则顺序文件是比较合适的文件结构。

（3）如果用户查询是基于散列域值的等值匹配，特别是如果访问顺序是随机的，则散列文件比较合适。

但散列文件组织不适合下述情况：

1）基于散列域值的非精确查询（如模糊查询、范围查询）。

2）基于非散列域进行的查询。

（4）B-树和B+树文件是实际数据库系统中使用非常广泛的索引文件结构，适合定义在数据量基本表、基于查找码的等值查询、范围查询、模糊查询和部分查询。B-树和B+树属于动态索引，可以随着数据文件的内容变化不断调整，保证数据查询的性能不会恶化。

（5）如某些频繁执行且需要进行多表连接操作的查询，可以考虑将这些基本表组织为聚集文件，以改善查询效率。

对数据库中的基本表等数据库对象选择合适的文件结构后还需要为其设计存取路径，设计内容包括：为数据库文件设计合理的物理存储位置和为基本表设计索引。设计数据库文件物理存储位置属于数据分布。将在下一小节讨论。在SQL Server中，可以利用CREATE DATABASE和CREATE TABLE创建数据库文件和基本表，并可将数据库文件分布到不同的磁盘上。

设计存取路径时要考虑的另一个问题是索引设计。索引可以提高文件存取速度，改善大数据量文件的访问性能。但索引是由DBMS管理的，它的建立和维护需要一定的系统开销，数据的插入、删除或更新也可能引起索引的重新调整。因此，索引加快了数据查询速度，却减慢了数据更新速度。索引本身还会占用一定的存储空间。因此，必须权衡考虑索引的优缺点。可根据以下原则决定是否为一个基本表建立索引：

1）对于经常需要进行查询、连接、统计操作，且数据量大的基本表可考虑建立索引；而对于经常执行插入、删除、更新操作或小数据量的基本表应尽量避免建立索引。

2）一个基本表除了可以建立一个聚集索引外，还可以建立多个非聚集索引。多个索引为用户提供了根据多个查找码快速访问文件的手段。但是索引越多，对表内数据更新时维护索引所需的开销就越大。因此，对于一个更新频繁的表应少建或不建索引。

3）索引可以由用户根据需要随时创建或删除，以提高数据查询性能。例如，对表进行大批量数据插入和更新时，可以先删除索引，以保证插入、更新操作的系统响应时间，因为在数据插入或更新过程中维护索引需要花费很大代价。数据插入或更新完成后再重建索引，以加快查询和统计等操作的系统响应时间。

对于基本表，可考虑在下面一些属性上建立索引：

（1）表的主码。

（2）在WHERE查询子句中引用率较高的属性。

（3）参与连接操作的属性。

（4）在Order By子句、Group By子句中出现的属性。

（5）在某一范围内频繁搜索的属性，但只有当使用索引的查询其结果不超过记录总数的20%时索引才会有明显的效果。

（6）如果在WHERE子句中同时包含一个表中的多个属性，则可以考虑在这些属性上建立多属性索引。

注：如果数据库文件需要频繁执行精确匹配查询（如等值查询），则可考虑建立散列索引，而B+树等有序索引更适合范围查询。

（7）当一个属性有相对较多的不同值时，使用索引用明显的作用；当一个属性的不同值很少时，使用索引没有好处。

（8）对包含大量空值的属性建立索引时要仔细考虑，因为很多数据库管理系统中的索引不引用具有空值的行，对空值的查找需要使用全表扫描来实现。
数据分布设计

（1）不同类型数据的物理分布

数据库备份数据、日志文件备份数据用于故障恢复，使用频率低，而且数据量很大，可以存储在磁带中。而应用数据、索引和日志则使用频繁，要求的响应时间短，必须放在支持直接存取（Direct Access）的磁盘存储介质上。

当系统采用RAID等多磁盘存储系统时，可以将基本表和建立在表上的索引分别放在不同的磁盘上。这样在访问基本表时，存放数据和存放索引的磁盘驱动器并行工作，可以得到较快的文件读写速度；类似的，日志文件与数据库对象（表、索引等）也可分别存放在不同磁盘上以改善系统I/O性能。

（2）应用数据的划分与分布

实际数据库中有可能存在一些非常大的基本表。这些表的属性很多，表中的元组也非常多。为改善对这类大数据量基本表的访问性能，可将基本表划分为若干分区，各分区数据分别存储在不同位置的磁盘上，并可采用不同的物理组织方式。对基本表的划分可以依据以下不同原则：
- 根据数据的使用特征划分。例如，如果一个基本表中有部分数据被频繁查询，并且要求查询响应速度快，则可以将基本表划分为频繁使用分区和非频繁使用分区，分别存储在不同磁盘上。对频繁使用分区中的数据可考虑建立B+树等多层索引，以提高数据访问速度；而对非频繁使用分区中的数据可以不建或者只建立单层索引。
  
  从提高数据访问速度角度出发，可以将一个大的基本表划分为多个分区，每个分区作为一个数据文件分别存储在不同的磁盘上。对基本表的访问可以通过对分布在个磁盘上的数据文件的并行访问来实现。
- 根据时间、地点划分。
- 分布式数据库系统（DDBS）中的数据划分。DDBS采用水平划分和垂直划分两种方法。
  
  水平划分：将一张基本表划分为多张具有相同属性、结构完全相同的子表，子表包含的元组是基本表中元组的子集。
  
  垂直划分：将一张基本表划分为多张子表，每张子表包含的属性是原基本表的子集。
（3）派生属性数据分布

基本表中的派生属性（Derived Attribute）是指该属性的取值可根据表中其他属性的取值唯一确定。对带有派生属性的基本表可采用如下两种方式：
- 将派生属性作为基本表中单独的一列，称为派生列。
- 派生属性不出现在基本表中。（该方式可以节省基本表的存储空间）
（4）关系模式的去规范化

在数据库逻辑设计阶段，由ER图转换得到的关系模式必须经过规范化处理，以避免关系表使用过程中出现插入异常、删除异常、更新异常和数据冗余等问题。原则上，经过规范化处理后，关系应达到一定的规范化程度，如满足第三范式。

3.3.5 其他物理设计环节

确定系统配置
物理模式评估

4.数据库应用系统功能设计与实施

DBAS的设计过程应包括概念设计、逻辑设计和物理设计三个阶段。对于DBAS功能设计部分而言，与总体设计的三个阶段对应，功能设计过程一般被划分为总体设计、概要设计和详细设计三个阶段。而具体到DBAS功能设计的数据库事务设计部分，又可以分成事务概要设计和事务详细设计两个阶段。完成系统设计工作后，就可以进入到系统实现与部署阶段。

4.1 软件体系结构与设计过程

4.1.1 软件体系结构

软件体系结构又称软件架构，软件体系结构={构件，连接件，约束}。软件体系结构描述了软件系统的总体组织和层次划分、系统元素及其功能分配、全局控制、系统元素间的协调和交互、数据存取等。

软件体系结构有多种风格和类型，如分层体系结构、模型-视图-控制器（MVC）体系结构、客户/服务器体系结构等。

4.1.2 软件设计过程

软件开发由设计、实现和测试三个环节组成，设计又包括概要设计和详细设计。软件设计阶段将产生系统的总体结构设计（或称体系结构设计）、系统的过程设计和系统的数据设计。从工程管理的角度，软件设计可分为概要设计（Preliminary）和详细设计（Detail)两大步骤。概要设计是根据需求确定软件和数据的总体框架，详细设计是将其进一步细化成软件的算法表示和数据结构。除了总体结构设计、数据设计和过程设计外，许多现代应用软件还包括一个独立人机界面（用户界面）设计活动。

概要设计

概要设计的任务是建立软件系统的总体结构和模块间的关系，定义各功能模块的接口，设计全局数据库或数据结构，规定设计约束，制定测试计划。

可以采用层次结构图表示软件总体结构。结构图中的节点代表功能模块。结构图中的上层模块可用一个或若干个下层模块表示，体现了自顶向下、逐步求精的设计思想。概要设计应提供概要设计说明书、数据库或数据结构设计说明书、组装测试计划等文件。
详细设计

概要设计侧重于模块的外部功能描述和模块间接口关系，详细设计则是细化概要设计产生的功能模块，形成可编程的程序模块，用某种过程设计语言（PDL）设计程序模块的内部细节，如算法和处理流程、数据结构、程序模块间详细接口信息等，为编写软件源代码提供依据。

软件设计可以选用结构化设计方法（也称为面向数据流的设计方法）、面向对象设计方法或面向数据设计方法等。
关于软件总体设计

软件设计的第一步是软件概要设计。概要设计是产生软件总体结构和软件功能模块。对于一些大型复杂软件系统，如分布式网络环境下的数据库应用系统，可根据逐步抽象和层次化原则，将概要设计分解成两个步骤：

（1）软件总体结构设计，也就是对软件需要进行分解。

（2）将每个子系统进一步划分为功能模块，定义各功能模块的数据结构、相互间交互关系。

一个大型复杂软件系统，按照上述软件概要设计方式，可以形成一个系统-子系统-模块-子模块的层次结构。

4.2 DBAS总体设计

一个大型数据库应用系统是由硬件和软件组成的复杂系统，在设计上应依据自上而下。循序渐进、逐步求精的原则。广义上讲，DBAS设计包括结构设计、过程设计和数据设计三个方面。

DBAS总体设计的任务是依据系统规划与分析结果，特别是其中的技术可行性分析内容，以及系统需求规范，确定系统总体框架，作为后续设计活动的基础。主要内容包括：

（1）DBAS体系结构设计。

（2）DBAS软件总体设计。

（3）软硬件选型与配置设计。

（4）业务规则初步设计。

4.2.1 DBAS体系结构设计

确定DBAS体系结构是指系统从功能、层次/结构、地理分布等角度进行分解，划分为多个子系统，定义各子系统应实现的功能；设计系统的全局控制，明确各子系统间的交互和接口关系。

将系统分解为多个子系统后，需要选择和设计合适的系统体系结构将这些子系统组织起来，并且设计子系统间的协调交互关系。DBAS体系结构可以采用一些通用体系结构，如客户/服务器体系结构、分层体系结构等。

客户/服务器体系结构

客户/服务器（C/S）结构是网络环境下一种主流的数据库应用系统体系，已得到广泛应用。C/S结构的数据库应用系统可以有多个数据库服务器，数据库服务器、客户端和应用服务器相互协同工作，实现了对数据库服务器上的数据资源的共享。

两层C/S结构的数据库应用系统，其特点为：

（1）DBAS的数据管理和数据处理功能被分解并分布在客户端的数据库服务器上。客户端通过用户界面实现对人机交互功能；数据库服务器通过DBMS,依据事务逻辑完成相应的数据管理功能。

（2）数据库服务器可以为多个客户端应用提供共享的数据管理功能，避免了为每一个新的应用单独开发对应的服务器端数据管理功能，提高了应用程序相对于数据库的独立性，也减少了应用程序的开发和维护代价。

（3）客户端应用可以通过网络访问多个不同数据源。客户端和数据库服务器可分别运行在不同的操作系统和硬件平台上，便于异构平台间的融合和匹配。

（4）客户端除了完成人机交互功能外，还需要完成面向应用的数据处理功能，负荷较重，属于典型的**“胖客户端”**。
浏览器/服务器结构

三层浏览器/服务器（B/S）结构的数据库应用系统是一种互联网环境下的新型数据库应用系统结构。它将数据处理功能分解并发布在表示层、功能层和数据层三个层面上，分别由Web浏览器、Web应用服务器和数据库服务器来实现，其特点为：

（1）表示层位于客户端，由Web浏览器实现。客户端根据显示逻辑完成具体的数据表示和人机交互功能。客户端功能单一，一般只安装Web浏览器，没有其他用户应用程序，属于典型的**“瘦客户端”**。

（2）功能层位于Web应用服务器，实现面向具体应用领域的业务规则。应用服务器接受来自Web浏览器的用户请求，根据应用领域业务规则执行相应的数据库应用程序，通过数据库访问接口向数据库服务器提出数据库操作请求；接收来自数据库服务器的数据访问结果，并通过客户端将结果返回给用户。

（3）数据层位于数据库服务器，通过DBMS完成具体的数据存储和数据存取等数据管理功能。数据库服务器接受应用服务器提出的数据操作请求，按照事务逻辑对数据库进行查询和修改，并将数据访问结果返回给应用服务器。

4.2.2 DBAS软件总体设计

DBAS软件包括操作系统、数据库管理系统、开发环境、中间件和应用系统。应用软件分为数据库事务和应用程序。应用软件分为数据库事务和应用程序。数据库事务通过对数据库的直接操作实现数据管理和数据处理功能。

4.2.3 软硬件选型与配置设计

软硬件选型涉及的内容包括：

网络及网络设备选型
数据存储设备及备份方案指定
应用服务器、Web服务器选型
确定系统终端软件环境
确定软件平台及开发语言、工具
系统中间件及第三方软件选型

在进行软硬件选型及配置设计时通常需要考虑以下因素：

数据规模：数据规模包括数据量大小、数据增长速度等，其数值直接决定着存储设备选型。
系统性能：如系统响应时间、并发访问需求、系统吞吐量、实时性需求、峰值时系统响应速度等。
安全可靠性：安全可靠性包括数据安全性、数据传输安全性、系统访问安全性、设备安全可靠性等，也是软硬件选型时考虑的重要因素。
用户需求。
项目预算情。

4.2.4 业务规则初步设计

业务规则初步设计的任务是从系统的角度，规划DBAS的业务流程使之符合客户的实际业务需要。

4.3 DBAS功能概要设计

DBAS功能概要设计按照逐步求精、模块化、信息隐藏和功能细化原则，根据DBAS需求分析阶段得到的系统功能和业务规则描述，在总体设计结果基础上，将DBAS应用软件进一步细化为模块/子模块，组成应用软件的系统-子系统-模块-子模块层次结构，并对系统、子系统、模块、子模块等系统元素从结构、行为和数据三方面进行设计。

从功能角度DBAS系统通常可以划分为四个层次实现：

表示层：负责所有与用户交互的功能，用户对数据库应用系统的最直观感受均在这层实现。
业务逻辑层：负责根据业务逻辑需要将表示层获取的数据进行组织后，传递给数据访问层，或将数据访问层获取的数据进行相应的加工处理后，传送给表示层用于展示。
数据访问层：负责与DBAS系统进行交互，提取或存入应用系统所需的数据。
数据持久层：负责保存和管理应用系统数据。

4.3.1 表示层概要设计

表示层概要设计的主要任务是进行人机界面设计。人机界面（也称用户界面，人机交互界面）是数据库应用系统的一个重要组成部分。与其他设计活动相比，人机界面的设计与用户和应用领域的关系更为紧密。人机界面设计与实现的好坏直接影响到系统的易用性，影响到用户对系统的认可和接受程度。

4.3.2 业务逻辑层概要设计

业务逻辑概要设计的主要任务是梳理DBAS的各项业务活动，将其表示为各种系统构件（如类、模块、组件等）。业务逻辑层是DBAS的重要组成部分，它承载了数据访问层与用户界面层的桥梁作用，DBAS的核心业务逻辑均在本层予以实现。在业务逻辑层概要设计阶段，设计人员需要将数据库应用软件划分为一系列程序模块，每个模块实现一个具体的功能。

业务逻辑层概要设计包括：结构、行为、数据、接口、故障处理、安全设计、系统维护与保障等方面的内容。

那么，如何对业务逻辑层进行模块划分尼？通常需要考虑高内聚与松耦合原则。这一原则在设计过程中体现在以下方面：

（1）构件本身应由相关性很强的代码组成，一个构件或一个模块只负责完成一项任务，也就是常说的单一责任原则。

（2）组成系统业务逻辑层的各个构件均应具有独立的功能，并且最大限度地减少与其构件功能重叠。

（3）构件之间的接口应尽量简单明确。

（4）如果某两个构件间的关系比较复杂的话，应考虑进一步进行模块划分。

（5）如果构件过于复杂，可以考虑将其细分。

总之，高内聚与松耦合原则的根本目的就是保持软件内在联系的前提下，分解软件系统，降低软件系统开发的复杂性。

高内聚和松耦合是相互矛盾的，分解程度越粗的系统耦合性越低，分解越细的系统内聚度越高 。过度松耦合的软件系统，软件模块内部不可能高内聚，而过度高内聚的软件模块之间必然是高度依赖的。

4.3.3 数据访问层概要设计

数据访问层概要设计的主要任务就是针对DBAS的数据处理需求设计用户操作数据库的各类事务。

事务是数据库系统中的一个逻辑数据处理单元，其中包含了一系列数据操作的集合。事务的实现与具体的DBMS平台密切相关，不同DBMS平台下的事务类型、执行模式和数据操作实现机制是有一定差别的，这里的数据操作指对数据库对象的查询、插入、删除和修改等。

事务的设计可以分解为概要设计和详细设计两个步骤。一个完整的事务概要设计包括以下几个方面的信息：事务名称、事务所访问的关系表及关系属性、事务处理逻辑、事务用户（指使用、启动、调用该事务的软件模块或系统）。

4.4 DBAS功能详细设计

4.4.1 表示层详细设计

人机界面设计最好采用原型迭代法。

原型迭代法由以下几个步骤组成：

（1）初步设计

（2）用户界面细节设计

（3）原型设计与改进

4.4.2 业务逻辑层详细设计

业务逻辑层详细设计的任务是根据概要设计中定义的各程序模块功能和输入输出数据需求，结合具体的程序设计环境和机制，设计各模块的内部处理流程和算法、具体数据结构、对外详细接口等，是对概要设计的细化。在业务逻辑详细设计阶段，设计人员也可以通过UML的类图、活动图、协作图来描述DBAS对用户业务的详细实现逻辑。

4.5 应用系统安全架构设计

4.5.1 数据安全设计

数据安全涉及的范围很广，从数据存储安全的角度出发，主要有以下五个方面：

安全性保护：即防止非法用户对数据库的非法使用，以避免数据的泄露、篡改或破坏。
完整性保护：即保证数据源的正确性和一致性。
并发控制：即保证多个用户能共享数据库，并维护数据的一致性。
数据库的备份与恢复：即在系统失效后的数据库恢复，配合定时备份数据库，使数据库不丢失数据。
数据加密传输：即将一些高密级的敏感数据通过一定的加密算法加密后传输。

1.数据库的安全性保护

在DBAS系统设计时，通常采用前台和后台相结合的安全性保护措施来共同维护DBAS的安全性。主要的保护方式有：

（1）用户身份鉴别。

（2）权限控制。

（3）视图机制。

2.数据库的完整性保护

数据库的完整性是指数据库中数据的正确性、一致性和相容性。完整性机制将防止合法用户使用数据库时向数据库中加入不合语义的数据。保证数据库中数据的完整性的方法之一是设置完整性检查，即对数据库中的数据设置一些约束条件。

完整性约束条件的作用对象分为列、元组和关系三种级别。完整性约束条件的设置一般由用户通过完整性约束语句给出，而完整性检查通常会由数据库管理系统负责完成。

3.数据库的并发控制

在数据库系统运行过程中，如果数据库管理系统可以同时接纳多个事务，事务可以在时间上重叠执行，则称这种执行方式为并发访问（Concurrent Access）。通常，一些大型DBMS都支持数据库并发访问性能。实现数据库并发控制的方式很多。常用的是封锁技术。所谓封锁即是在某一时间内禁止某用户对数据对象做某些操作以避免产生数据不一致的问题。基本的封锁一般有**排它锁（x锁）和共享锁（s锁)**两种类型。

锁机制能够很好地实现数据访问控制，保障数据的正确性和安全性，但与此同时它不可避免的带来了死锁问题。因此，在进行DBAS系统设计时，通常为了避免死锁，可以考虑以下原则：

（1）按同一顺序访问资源。

（2）避免事务中的用户交互。

（3）采用小事务模式，尽量缩短事务的长度，减少占有锁的时间。

（4）尽量使用记录级别的锁（行锁），少用表级别的锁。

（5）使用绑定连接，使同一应用程序所打开的两个或多个连接可以相互合作。

4.数据库的备份与恢复

数据库恢复的基本原理就是数据库中任何一部分数据可以利用存储在系统其他存储器上的冗余数据来重建。数据库恢复首先要建立冗余数据（即进行数据备份），然后利用这些冗余数据实施恢复。

数据备份与恢复策略可有不同的解决方案：

（1）双机热备。双机热备指基于Active/Standby方式的服务器热备。

（2）数据转储。数据转储（也称为数据备份）。

（3）数据加密存储。

5.数据加密传输

常用的数据加密传输手段有：

数字安全证书
对称密钥加密
数字签名
数字信封

4.5.2 环境安全设计

漏洞与补丁
计算机病毒防护

目前主要的病毒防护手段有：

（1）安装杀毒软件，定期查杀病毒。

（2）计算机实时监控
网络环境安全

（1）防护墙。

（2）入侵检测系统。入侵检测系统（简称IDS）

（3）网络隔离。
物理环境安全

4.5.3 制度安全设计

DBAS系统的有效运行必须有一套完整的制度保障机制。

4.6 DBAS实施

DBAS的实施阶段主要包括以下工作：

（1）创建数据库。

（2）装载数据。

（3）编写与调试应用程序。

（4）数据库试运行。

4.6.1 创建数据库

开发人员使用具体的DBMS提供的数据定义语言（DDL）或图形化工具建立数据库和数据库对象，如使用DDL语言创建数据库、在数据库中建立表、创建访问数据库的用户和密码、建立视图、触发器、索引、主外码关系等，从而建立起数据库系统的总体架构。

在DBAS实施过程中，为了保障系统的性能和稳定性，创建数据库时需要考虑以下因素：

初始空间大小。
数据库增量大小。
访问性能。

4.6.2 数据装载

存入数据库，具体步骤如下：

筛选数据。
转换数据格式。
输入数据。
效验数据。

4.6.3 编写与调试应用程序

4.6.4 数据库系统试运行

试运行的主要目的是对数据库系统的功能和性能进行进一步测试，找出其中的不足，加以完善与改进。

功能测试
性能测试

5.UML与数据库应用系统

5.1 DBAS建模

统一建模语言（Unified Modeling Language,UML），UML是一种定义良好、易于表达、功能强大、通用的可视化建模语言，为软件开发的各阶段提供模型化与可视化支持。

UML的定义由语义和表达法两部分组成。语义用自然语言描述，而表达法定义了UML的可视化标准表达符号，这决定了UML是一种可视化的建模语言。具体来说，UML的语义是定义在一个四层（或四个抽象级）建模概念框架中的，这四层分别是：

（1）元元模型（Meta-Meta Model）层，组成了UML的最基本的元素“事物”，代表要定义的所有事物。

（2）元模型（Meta Model）层，组成了UML的基本元素，包括面向对象和面向组件的概念。

（3）模型（Model）层，组成了UML的模型。这一层的模型通常叫做类模型（Class Model）或类型模型（Type Model）。

（4）用户模型（User Model）层，这层中的所有元素都是UML模型的实例。这一层的模型通常叫做对象模型（Object Model）或实例模型（Instance Model）。

使用UML时要从不同的角度观察和分析系统，因此产生了“视图”的概念。视图是对系统的模型在某方面的投影，注重于系统的某个方面。UML中包括以下五种视图：结构视图、实现视图、行为视图、环境视图和用例视图。

每一种UML的视图都是由一个或多个图（Diagram）组成的，一个图就是系统架构的某个侧面的哦展示，所有的图一起组成了系统的完整视图。UML2.0提供了13种不同的图，分为两类：一类是结构图，主要用于系统的静态结构建模，包括类图、对象图、复合结构图、包图、组件图、部署图；另一类是行为图，主要用于对系统的动态行为建模，包括用例图、交互图（顺序图、通信图、交互概述图、时间图）、状态图和活动图。

5.2 DBAS业务流程与需求表达

在UML中，对于业务流程支持的主要图是活动图（Activity Diagram），活动图主要的目的在于陈述活动与活动之间的流程控制的转移。在软件需求的领域中，用于捕捉需求的工具，就是用例图（Use Case Diagram）。

5.2.1 业务流程与活动图

活动图主要用于描述系统、用例和程序模块中逻辑流程的先后执行次序，并行次序。一般来说，活动图最适合用于描述系统或子系统的主要工作流程。

活动之间的转换由箭头表示，箭头表示执行方向，加粗直线为同步条，菱形代表分支，表示判断。

5.2.2 系统需求与用例图

用例模型是把满足用户需求的所有功能表示出来的工具。用例模型由用例、角色和系统三部分组成。

系统

在用例图中系统用一个长方框来表示，系统的名字写在方框上或方框里面，方框内部还可以包含该系统中国用符号表示的用例。
角色

角色是与系统交互的人或其他实体。所谓“与系统交互”指的是角色从系统中接受消息，或是向系统提交消息。一个角色可以执行多个用例，反过来，一个用例也可被多个角色使用。角色是类，所以它拥有与类相同的关系描述。在用例图中，用通用化关系来描述角色之间的行为。

通用化关系是指把某些角色的共同行为抽取出来作为通用行为，这些通用行为构成它们的超类（Superclass）。角色之间的通用化关系用带空心三角形（作为箭头）的直线表示，箭头的方向指向超类。
用例

用例代表的是一个完整的功能，是所有动作的集合。动作（Action）是系统的一次操作，如与角色通信、进行计算，在系统内进行的工作都可以称为动作。

在UML中，用例用椭圆表示，用例位于系统边界的内部。用例与角色之间有连接关系，次关系属于关联（Association），又称作通信关联（Communication Association）。关联关系是双向的一对一关系，表示不进角色可以用例通信，用例也可以与角色通信，表示方法是一条连接角色和用例的带箭头直线。

另外，用例之间也存在关系，包括扩展、使用、组合三种。

5.3 DBAS系统内部结构的表达

系统内部结构一般分为静态结构和动态结构。在UML中，用类图来描述系统的静态结构，而用顺序图和通信图来表示系统的动态结构。

5.3.1 系统结构与类图

类图展现了一组类、接口和协作以及它们间的关系。系统可以有多个类图，单个类图仅表达了系统的一个方面。

属性

属性包括属性的名称、类型和缺省值。UML规定语法为：

可见性名称：类型 = 缺省值{约束性}
操作

操作描述了类的动态行为，在UML中，操作的语法定义为：

可见性名称（参数表）：返回类型表达式{约束性}
关系

类与类之间通常有关联、通用化（继承）、依赖和精化四中关系。

（1）关联关系

通常情况下关联关系是双向的。如果类与类之间的关联是单向的，则称为导航关联。

聚集可以进一步划分成共享聚集（Share Aggregation）和组成（Composition）。在UML中，共享聚集表示为空心菱形，组成为实心菱形。

（2）继承关系

在UML中，继承表示为一头为空心三角形的连线。

（3）依赖关系

有两个元素X、Y，如果修改元素X的定义可能会引起元素Y的定义的修改，称元素Y依赖元素X。

（4）精化关系

精化关系用空心三角形的虚线表示。

5.3.2 系统结构与顺序图

顺序图的目的在于说明对象的协作如何达到系统的目标。顺序图主要用于描述系统内对象之间的消息发送和接受序列。顺序图有两个坐标轴：纵向表示时间的持续过程，横向表示对象，每一个对象用矩形框表示，纵向的虚线表示对象在序列中的执行情况，称为对象的“生命线”。

浏览顺序图的方法是从上到下查看对象间交换的信息。顺序图主要的目的是表达对象与对象之间如何沟通与合作，也因此被称为动态模型。

5.3.3 系统结构与通信图

通信图是交互图（Interaction Diagram）的一种，也被称为协作图。通信图中包含一组对象，并在图中展示这些对象之间的联系以及对象间发送和接受的消息。

顺序图和通信图都描述交互，但是顺序图强调的是时间，而通信图强调的是空间。

5.4 DBAS系统微观设计的表达

在UML中，对于细节方面的内容可用对象图（Object Diagram）、状态机图（State Machine Diagram）及时间图（Timing Diagram）来表达。分析和描述某个特定状况下的系统运作情况。

5.4.1 微观设计与对象图

对象图是某个特定时间点上，系统所存在的所有对象的一个快照。可以被用来解释类图以及验证所设计的类图是否符合实际情况，也可以帮助程序人员理解类图的复杂结构。

5.4.2 微观设计与状态机图

当某一个对象或某一个事件有非常复杂的状态转换时，可用状态机图来描述这个过程。

在状态图中，只能有一个起始状态，这一点与活动图中起始点是相同的，起始状态的图示是一个实心的圆形。结束状态代表整个状态机到此活动结束，可以有多个结束状态。

状态图的主要目的在于陈述系统中有关事件或对象的状态转移。

5.4.3 微观设计与时间图

在时间图中，整个矩形就是一个生命线（LIfeline），显示了商品价格在不同时间段的状态变化情况。状态可以用文字在内部进行描述，如原价状态。在矩形框的最下面为时间轴的线代表时间的进行以及状态的转移，称为时间线（Timeline）。

5.5 DBAS系统宏观设计的表达

5.5.1 宏观设计与包图

一个设计良好的命名空间，可以让程序开发人员容易理解并且让各个命名空间之间能够耦合，而命名空间内的类，则可以满足高内聚的要求。

UML的包图（Package Diagram）可以表达系统中不同的包、命名空间或不同的项目间彼此的关系。在这里，包及命名空间指的是在逻辑层次上的关联性，而项目则是指实体层次的关联性。包（Package）是一种组合机制，把模型元素通过内在的语义连在一起成为一个整体叫做包。

UML中对包定义了四种可见性：私有、保护、公有和实现。

5.5.2 宏观设计与交互概述图

交互概述图（Interaction Overview Diagram）主要是利用活动图作为基础，只是其在控制流间连接的UML元素并非活动，而是交互图（包括顺序图、通信图、时间图及交互概述图）。因此，交互概述图的主要元素和活动图完全一样，唯一不同的是一个交互框（Frames），用来取代活动图中的活动框。

5.5.3 宏观设计与复合结构图

在UM中，针对这样的系统接口架构，提供了一个全新的复合结构图（Composition Structure Diagram）来表达这一架构，复合结构图适用于需要进行系统整合的情况，可以利用复合结构图绘制出要出发的系统与外部系统间的关系。

复合结构图中最主要的元素就是部件（Part),一个部件可以代表某个实体组件，也可以代表一个子系统。部件与部件之间的连接关系主要是装配（Assembly）关系，这种关系要通过接口（Interface）来沟通。

5.6 DBAS系统实现与部署的表达

UML中的组件图（Component Diagram）和部署图（Deployment Diagram）可以简化团队管理上的难度。

5.6.1 系统实现与组件图

组件图用来表示系统的静态实现视图，展现了一组组件之间的组织和依赖，用于对源代码、可执行的发布、物理数据库等的系统建模。

5.6.2 系统实现与部署图

部署图又叫配置图，描述系统中硬件和软件的物流配置情况和系统体系结构。

6.小结

所以系统均由静态结构和动态行为组成。结构可以用静态模型元素来描述，如类、关系、结点和构件。行为主要描述结构内的元素如何交互。通常情况下，这些交互是确定的且是可以建模的。抽象系统的动态行为也称为动态建模，UML支持动态建模的有四类图：状态图、序列图、协作图和活动图。

状态图用于描述类（或子系统、系统）中的行为和内部状态，它侧重于描述随着时间的改变，对象如何改变其状态。状态的改变取决于出现的事件，状态中执行的行为和动作，状态转移等。一个事件的发生可能是因为：条件为真、接受一个信号、一个操作调用或满足指定时间。

顺序图主要用于描述在指定情节中一组对象是如何交互的。它着眼于消息序列，也就是在消息间如何发送和接受信息。顺序图有两个坐标：纵坐标轴显示时间，横坐标轴显示有关对象。顺序图最重要的特点是时间的概念。

协作图主要用于描述对象在空间中如何交互，即除了动态交互，它也直接描述了对象是如何链接在一起的。在协作图中没有时间轴，而是将消息按序编号。

活动图主要的目的在于陈述活动与活动之间的流程控制的转移，其作用类似于流程图，然而，与流程图不同的是，活动图支持并行行为。

6.高级数据查询

6.1 一般数据查询功能扩展

6.1.1 使用TOP限制结果集

当使用select语句进行查询时，有时只希望列出结果集中的前几行数据，而不是全部数据。例如，竞赛时，可能只取成绩最高的前三名，这时就可以使用TOP谓词来限制输出的结果。

使用TOP谓词的语法格式为：

TOP n [percent] [with ties]

其中，n为非负整数。

TOP n：取查询结果的前n行数据；
TOP n percent：取查询结果的前n%行数据；
WITH TIES：表示包括最后一行取值并列的结果。

TOP谓词写在select单词的后边（如果有distinct的话，则TOP写在distinct的后边）、查询列表的前边。

[例1] 查询单价最高的前三种商品的商品名、商品类别和单价，包括并列的情况。

SELECT TOP 3 WITH TIES GoodsName,GoodsClassName,SaleUnitPrice
FROM Table_Goods a JOIN Table_GoodsClass b
ON a.GoodsClassID = b.GoodsClassID
order by SaleUnitPrice desc;

```sql

**注意：在使用TOP谓词时，应该与ORDER BY子句一起使用，这样的前几名才有意义。当使用WITH TIES选项时，则要必须使用ORDER BY子句。**

------

**[例2]**  查询被卖出次数最多的前三种商品的商品编号和卖出次数。

```mysql
select top 3 GoodsID,count(*) from Table_SaleBillDetail
group by GoodsID
order by count(*) desc;

6.1.2 使用CASE函数

可以在查询语句中使用CASE函数，已达到分情况显示不同类型的数据的目的。CASE函数是一种多分支表达式，它计算条件列表并返回多个可能的结果表达式中的一个。

CASE函数分为简单CASE函数和搜索CASE函数两种类型。

简单CASE函数

简单CASE函数的语法格式为：
```
CASE 测试表达式
	WHEN 简单表达式1 THEN 结果表达式1
	WHEN 简单表达式2 THEN 结果表达式2
	...
	WHEN 简单表达式n THEN 结果表达式n
	[ELSE结果表达式n+1]
END
```
简单CASE函数的执行过程为：
- 计算测试表达式，然后按从上到下的顺序对每个WHEN子句的简单表达式进行计算。
- 如果某个简单表达式的值与测试表达式的值相匹配，则返回与第一个取值为True的WHEN对应的结果表达式的值。
- 如果所有的简单表达式的值都不与测试表达式的值相匹配，则当指定了ELSE子句时，将返回ELSE子句中指定的结果表达式的值；若没有指定ELSE子句，则返回NULL值。
搜索CASE函数

搜索CASE函数的语法格式为：
```
CASE 
	WHEN 布尔表达式1 THEN 结果表达式1
	WHEN 布尔表达式2 THEN 结果表达式2
	...
	WHEN 布尔表达式n THEN 结果表达式n
	[ELSE结果表达式n+1]
END
```
搜索CASE函数的各个WHEN子句的布尔表达式可以使用比较运算符，也可以使用逻辑运算符。

执行过程同简单CASE函数一样，这里就不多叙述了。

[例3] 对家用电器类商品进行分类显示。如果商品单价高于3000元，则显示“高档商品”；如果单价在1000~3000元，则显示“中档商品”；如果单价低于1000元，则显示“低档商品”。
```
select GoodsName,SaleUnitPrice,Type=
case
	when SaleUnitPrice > 3000 then '高档商品'
	when SaleUnitPrice between 1000 and 3000 then '中档商品'
	when SaleUnitPrice < 1000 then '低档商品'
end
from Table_Goods a JOIN Table_GoodsClass b
on a.GoodsClassID = b.GoodsClassID
where GoodsClassName = '家用电器';
```
[例4] 查询每种商品的销售次数，如果销售次数超过10次，则显示“热门商品”，如果销售次数在5~10次之间，则显示“一般商品”，如果销售次数低于5次，则显示“难销商品”，如果商品没有被销售过，则显示“滞销商品”。

分析：由于要查询的是全部商品的销售情况，包括有销售记录的和没有销售记录的，因此，应该用外连接实现。另外，在统计每种商品的销售次数时，应该使用count(列名)函数，不能使用count(*)函数，而且应该对外连接后的销售记录表中的商品编号列进行统计。
```
select a.GoodsID,商品销售类别 = case
	when count(b.GoodsID) > 10 then '热门商品'
	when count(b.GoodsID) between 5 and 10 then '一般商品'
	when count(b.GoodsID) between 1 and 4 then '难销商品'
	else '滞销商品'
end 
from Table_Goods a left join Table_SaleBillDetail b
on a.GoodsID = b.GoodsID
group by a.GoodsID
```

6.1.3 将查询结果保存到新表中

当使用select语句查询数据时，产生的结果是保存在内存中的。如果希望将查询结果永久保存下来，比如保存在一个表中，则可以通过在select语句中使用INTO子句实现。

包含INTO子句的SELECT语句的语法格式是：

select 查询列表序列 into <新表名>
from 数据源
...       ---其他行过滤、分组等子句

其中“新表名”是要存放查询结果的表名。这个语句将查询的结果保存在“新表名”所指定的表中。实际上这个语句包含如下三个功能：

根据查询语句列出的列以及其类型创建一个新表。
执行查询语句。
将查询的结果插入到新表中。

用INTO子句创建的新表可以是永久表（在执行这个语句时所使用的数据库中被物理的创建，并且是存储在磁盘上的表），也可以是临时表（在tempdb数据库中创建此表，其生存期是有限的）。临时表又根据其使用范围分为局部临时表和全局临时表两种。
- 局部临时表通过在表名前加一个“#”来标识，比如：#T1，表示‘#T1’是一个局部临时表。局部临时表的生存期与创建此局部临时表的用户的连接生存期相同，它只能在创建此局部临时表的当前连接中使用。
- 全局临时表通过在表名前加两个“##”来标识，比如：##T1，表示“##T1”是一个全局临时表。全局临时表的生存期与创建全局临时表的用户的连接生存期相同，并且在生存期内可以被所有的连接使用。
  
  可以对局部临时表和全局临时表中的数据进行查询，其方法同永久表一样。
  
  [例5] 将地址在海淀区的顾客信息（顾客编号、姓名、性别、会员卡号）保存到#HD_Customer局部临时表中。
```
select CustomerID,CName,Sex,CardID into #HD_Customer
from Table_Customer;
```
  [例6] 查询购买了“家用电器”类商品的顾客姓名、所在地址、购买日期、购买数量和购买单价，并将查询结果保存到一个新的永久表中，新表名为“家用电器销售表”，新表中各列名用中文。
```
select CName 顾客名，Address 地址，SaleDate 购买日期,Quantity 数量,
UnitPrice 购买价格
into 家用电器销售表
from Table_Customer a join Table_SaleBill b on a.CardID = b.CardID
join Table_SaleBillDetail c on c.SaleBillID = b.SaleBillID
join Table_Goods d on d.GoodsID = c.GoodsID
join Table_GoodsClass e on e.GoodsClassID = d.GoodsClassID
where GoodsClassName = '家用电器';
```

6.2 查询结果的并、交、差运算

数据查询的结果是一个集合，SQL语言支持对查询的结果再进行并、交、差运算。

6.2.1 并运算

并运算可将两个或多个查询语句的结果集并为一个结果集，这个运算可以使用UNION运算符实现。UNION是一个特殊的运算符，通过它可以实现让两个或更多的查询产生单一的结果集。

UNION操作与JOIN连接操作不同，UNION更像是将一个查询结果追加到另一个查询结果中**。JOIN操作是*水平地合并数据（添加更多的列）**，而UNION是垂直合并数据（添加更多的行）。

使用UNION谓词的语法格式为：

select 语句1
union [all]
select 语句2
union [all]
...
select 语句n

其中：all表示在结果集中包含所有查询语句产生的全部记录，包括重复的记录。如果没有指定all，则系统默认是删除合并后结果集中的重复记录。

使用UNION时，需要注意以下几点：

所有要进行UNION操作的查询，其SELECT列表中列的个数必须相同，而且对应列的语义应该相同。
各查询语句中每个列的数据类型必须与其他查询中对应列的数据类型是隐式兼容的，即只要它们能进行隐式转换即可。例如，如果第一个查询中第二个列的数据类型是char(20),而第二个查询中第二个列的数据类型是varchar（40）是可以的。
合并后的结果采用第一个SELECT语句的列标题。
如果要对查询的结果进行排序，则ORDER BY子句应该写在最后一个查询语句之后，且排序的依据列应该是第一个查询语句中出现的列名。

[例7] 利用集合并运算查询姓李和姓王的客户的姓名和电话，并将查询结果按电话升序排序。
```
select Name,Tel from Table_Customer where Name like '李%'
union
select Name,Tel from Table_Customer where Name like '王%'
order by Tel asc;
```
[例8] 查询顾客和销售人员所在的全部地址。
```
select Address from Table_Customer
union
select Address from Table_Salesperson;
```

6.2.2 交运算

交运算将返回同时在两个集合中出现的记录，即返回两个查询结果集中各个列的值均相同的记录，并用这些记录构成交运算的结果。

实现交运算的SQL运算符为INTERSECT，其语法格式为：

select 语句1
intersect
select 语句2
intersect 
...
select 语句n

intersect运算对各查询语句的要求同UNION运算。

[例9] 查询顾客朱时七和王达力所购买的相同商品，列出商品名和商品销售单价。

select GoodsName,SaleUnitPrice from Table_Goods G
join Table_SaleBillDetail SD on SD.GoodsID = G.GoodsID
join Table_SaleBill S on S.SaleBillID = SD.SaleBillID
join Table_Customer C on C.CardID = S.CardID
where Name = '朱时七'
intersect
select GoodsName,SaleUnitPrice from Table_Goods G
join Table_SaleBillDetail SD on SD.GoodsID = G.GoodsID
join Table_SaleBill S on S.SaleBillID = SD.SaleBillID
join Table_Customer C on C.CardID = S.CardID
where Name = '王达力';

6.2.3 差运算

差运算将返回在第一个集合中有但第二个集合中没有的数据。实现差运算的SQL运算符为EXCEPT，其语法格式为：

select 语法1
except
select 语法2
except
...
select 语句n

except运算对各查询语句的要求同UNION运算。

[例10] 查询顾客王达力购买了而朱时七没有购买的商品，列出商品名和商品销售单价。

select GoodsName,SaleUnitPrice from Table_Goods G
join Table_SaleBillDetail SD on SD.GoodsID = G.GoodsID
join Table_SaleBill S on S.SaleBillID = SD.SaleBillID
join Table_Customer C on C.CardID = S.CardID
where Name = '王达力'
except
select GoodsName,SaleUnitPrice from Table_Goods G
join Table_SaleBillDetail SD on SD.GoodsID = G.GoodsID
join Table_SaleBill S on S.SaleBillID = SD.SaleBillID
join Table_Customer C on C.CardID = S.CardID
where Name = '朱时七';

6.3 相关子查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。如果一个SELECT语句时嵌套在一个SELECT、INSERT、UPDATE或DELETE语句中，则称为子查询或内层查询，包含子查询的语句称为主查询或外层查询。一个子查询也可以嵌套在另外一个子查询中。为了与外层查询有所区别，总是把子查询写在圆括号中。与外层查询类似，子查询语句中也必须包含SELECT子句和FROM子句，并可以根据需要选择WHERE子句、GROUP BY子句和HAVING子句。

包含子查询的语句通常采用以下格式中的一种：

WHERE 表达式 [NOT] IN (子查询)
WHERE 表达式比较运算符（子查询）
WHERE [NOT] EXISTS （子查询）

通常，子查询一般用在外层查询的WHERE子句或HAVING字句中，与比较运算符或逻辑运算符一起构成查询条件。对于返回结果为单值的子查询语句，可以出现在任何允许使用表达式的地方。
1. 使用子查询进行基于集合的测试
  
  使用子查询进行基于集合的测试时，通过运算符IN和NOT IN，将一个表达式的值与子查询返回的结果集进行比较。其形式为：
```
WHERE 表达式 [NOT] IN (子查询)
```
  这种形式的子查询的语句是分步骤实现的，即先执行子查询，然后在子查询的结果基础上再执行外层查询。子查询返回的结果实际上就是一个集合，外层查询就是在这个集合上使用IN运算符进行比较。
  
  [例11] 查询与“王晓”在同一个地址（区相同）的顾客的姓名和所在地址。
```
select Cname,Address from Table_Customer
 where Address in(
	select Address from Table_Customer where Cname = '王晓')
 and Cname != '王晓';
```
  实际实现过程为：
  - 执行子查询：确定“王晓”的地址。
```
select Address from Table_Customer where Cname = '王晓';
```
    结果为：北京市海淀区
  - 在子查询的结果基础上，执行外层查询：查找地址在北京市海淀区的顾客，并去掉王晓本人。
```
select Cname,Address from Table_Customer
	where Address='北京市海淀区' and Cname != '王晓';
```
2. 使用子查询进行比较测试
  
  使用子查询进行比较测试时，通过比较运算符（=、<>、<、>、<=、>=），将一个表达式的值与子查询返回的单值进行比较。如果比较运算的结果为True，则比较测试返回True；如果比较运算的结果为False，则比较测试返回False.
  
  使用子查询进行比较测试的形式为：
```
WHERE 表达式 比较运算符 （子查询）
```
  使用子查询进行的比较测试要求子查询语句必须是返回单值的查询语句。
  
  [例12] 查询单价最高的商品名称和单价。
```
select GoodsName,SaleUnitPrice from Table_Goods a
	where SaleUnitPrice=(select max(SaleUnitPrice) from Table_Goods);
```
  [例13] 查询家用电器类商品中，单价高于家用电器类商品的平均价格的商品的名称和单价。
  
  **分析：**①首先查询出家用电器类商品的平均价格
```
select avg(SaleUnitPrice) from Table_Goods c
	join Table_GoodsClass d on c.GoodsClassID = d.GoodsClassID
	where GoodsClassName = '家用电器';
```
  ②然后再查询单价高于①的结果的家用电器类商品
```
select GoodsName,SaleUnitPrice from Table_Goods a
	join Table_GoodsClass b on a.GoodsClassID = b.GoodsClassID
	where SaleUnitPrice > (①)
	and GoodsClassName = '家用电器';
```
  ③最后将两个查询语句结合起来即为满足要求的查询语句：
```
select GoodsName,SaleUnitPrice from Table_Goods a
	join Table_GoodsClass b on a.GoodsClassID = b.GoodsClassID
	where SaleUnitPrice > (
      select avg(SaleUnitPrice) from Table_Goods c
		join Table_GoodsClass d on c.GoodsClassID = d.GoodsClassID
		where GoodsClassName = '家用电器')
	and GoodsClassName = '家用电器'; 
```
  **注意：**由于聚合函数不能出现在WHERE子句中，因此，当一个列的值与一个聚合函数的结果进行比较时，必须用子查询先得到聚合函数的结果，然后在此结果基础上再执行外层查询的比较。
  
  从上边的所有子查询的例子可以看出，子查询的查询条件不依赖于外层查询，称这样的子查询为不相关查询或嵌套子查询。
3. 使用子查询进行存在性测试
  
  使用子查询进行存在性测试时，通常使用EXISTS谓词，其形式为：
```
WHERE [NOT] EXISTS (子查询)
```
  带EXISTS谓词的子查询不返回查询的结果，只产生逻辑真值和逻辑假值。
  - EXISTS的含义是：当子查询中有满足条件的数据时，EXISTS返回真值，否则返回假值。
  - NOT EXISTS的含义是：当子查询中有满足条件的数据时，NOT EXISTS返回假值，当子查询中不存在满足条件的数据时，NOT EXISTS返回真值。
    
    [例14] 查询购买了单价高于2000元商品的顾客的会员卡号。
```
select distinct CardID from Table_SaleBill
where exists (
	select * from Table_SaleBillDetail
		where SaleBillID = Table_SaleBill.SaleBillID
		and UnitPrice > 2000);
```
    **注：**带EXISTS谓词的查询是先执行外层查询，然后再执行内层查询。由外层查询的值决定内层查询的结果；内层查询的执行次数由外层查询的结果数决定。
    
    上述查询语句的处理过程为：
    
    （1）无条件执行外层查询语句，在外层查询的结果集中取第一行结果，得到SaleBillID的一个当前值，然后根据此SaleBillID值处理内层查询。
    
    （2）将外层的SaleBillID值作为已知值执行内层查询，如果在内层查询中有满足其WHERE子句条件的记录存在，则EXIETS返回一个真值（TRUE），表示在外层查询结果集中的当前行数据为满足要求的一个结果。如果内层查询中不存在满足WHERE子句条件的记录，则EXISTS返回一个假值（FALSE），表示在外层查询结果集中的当前行数据不是满足要求的结果。
    
    （3）顺序取外层结果集中的第2、3、…行数据，重复第（2）不步过程，直到处理完外层查询的全部记录为止。
    
    **注：**由于带EXISTS的子查询只返回真或假值，因此在子查询里不必指定查询列表。所以在有EXISTS的子查询中，子查询中的目标列通常都用“*”。
    
    带EXISTS的子查询由于在子查询中要涉及与外层表数据的关联，因此经常将这种形式的子查询称为相关子查询。
    
    [例15] 查询没有购买过价格高于2000元商品的顾客的会员卡号。
    
    分析：这是一个带否定条件的查询，这种类型的查询只能用NOT IN 或 NOT EXISTS形式的子查询实现。
```
select distinct CardID from Table_SaleBill
	where not exists (
    	select * from Table_SaleBillDetail
    	  where SaleBillID = Table_SaleBill.SaleBillID
    		and UnitPrice > 2000);
 或者
select distinct CardID from Table_SaleBill
 	where SaleBillID not in (
    	select SaleBillID from Table_SaleBillIDDetail
    		where UnitPrice > 2000);
```

6.4 其他形式的子查询

替代表达式的子查询

替代表达式的子查询是指在select语句的选择列表中嵌入一个只返回一个标量值的select语句，这个查询语句通常都是通过一个聚合函数来返回一个单值。

[例16] 查询G001顾客的姓名、地址以及该顾客购买商品的总次数。
```
select CName,Address
	(select count(*) from Table_SaleBill a
       join Table_Customer b on a.CardID = b.CardID
    	where CustomerID = 'G001') as TotalTimes
    	from Table_Customer
    	  where CustomerID = 'G001';
```
派生表

派生表（有时也称为内联视图）是将子查询作为一个表来处理，这个由子查询产生的新表就称为“派生表”，这很类似于临时表。可以在查询语句中用派生表来建立与其他表的连接关系，在生成派生表后，在查询语句中对派生表的操作与普通表一样。

使用派生表可以简化查询，从而避免使用临时表，而且相比手动生成临时表的方法性能更优越。

派生表与其他表一样出现在查询语句的FROM子句中。例如：
```
SELECT * FROM (SELECT * FROM T1) AS temp
```
这里的temp就是派生表。

[例17] 查询至少买了G001和G002两种商品的顾客号和顾客名。

**分析：**可以将买了G001商品的顾客保存在一个派生表中，将买了G002商品的顾客保存在另一个派生表中，然后在这两个表中找出顾客号相同的顾客，即查找同时在两个表中出现的顾客，就是至少买了G001和G002两种商品的顾客。具体代码如下：
```
select CustomerID,CName
	from (select * from Table_SaleBill a
         join Table_SaleBillDetail b
         on a.SaleBillID = b.SaleBillID
         where GoodsID = 'G001') as T1
    join (select * from Table_SaleBill a
         join Table_SaleBillDetail b
         on a.SaleBillID = b.SaleBillID
         where GoodsID = 'G002') as T2
    on T1.CardID = T2.CardID
    join Table_Customer c on c.CardID = T1.CardID;
```

6.5 其他一些查询功能

6.5.1 开窗函数

在SQL Server中，一组行被称为一个窗口，开窗函数是指可以用于“分区”或“分组”计算的函数。这些函数结合OVER子句对组内的数据进行编号，并进行求和、计算平均值等统计。因此，从这个角度来说，SUM、AVG以及ROW_NUMBER （对数据进行编号的函数）等都可以称为开窗函数。

开窗函数可以分别应用于每个分区，把每个分区看成是一个窗口，并为每个分区进行计算，开窗函数必须放在OVER子句前边。

开窗函数是在ISO SQL标准中定义的。SQL Server提供了两种开窗函数：排名开窗函数和聚合开窗函数。

将OVER子句与聚合函数结合使用

OVER子句用于确定在应用关联的开窗函数之前对行集的分区和排序。

将OVER子句与聚合函数结合使用的语法格式为：
```
<over_clause>::=
	over ( [partition by value_expression,...[n]])
```
其中各参数说明如下：
- partition by:将结果集划分为多个分区。开窗函数分别应用于每个分区，并为每个分区计算函数值。
- value_expression:指定对行集进行分区所依据的列，该列必须是from子句中生产的列，而且不能引用选择列表中的表达式或别名。value_expression可以是列表达式、替代表达式的子查询、标量函数或用户定义的变量。
  
  可以在单个查询中使用多个开窗函数，每个函数的over子句在分区和排序上可以不同。
  
  [例18] 设有课程表Course(Cno,CName,Credit,Semester)，各列含义分别为课程号、课程名、学分和开课学期。查询全部课程的课程号、课程名、开课学期、学分以及该学期开设课程的总学分、平均学分、最低学分和最高学分。
```
select Cno,CName,Semester,Credit,
	sum(Credit) over(partition by Semester) as 'Total',
	avg(Credit) over(partition by Semester) as 'Avg',
	min(Credit) over(partition by Semester) as 'Min',
	max(Credir) over(Partition by Semester) as 'Max'
from Cource;
```
将OVER子句和排名函数一起使用

排名函数为分区中的每一行返回一个排名值。根据所用函数的不同，某些行可能与其他行具有相同的排名值。排名函数具有不确定性。

SQl Server提供了四个排名函数：rank,dense_rank,ntile和row_number。下面分别介绍这些排名函数。

(1) RANK（）函数。使用RANK（）函数的语法格式为：
```
rank() over([<pertition_by_Clause>,...[n]]
           <order_by_clause>)
```
其中各参数含义如下：
- <partiton_by_clause>：将from子句生成的结果集划分为排名函数适用的分区。
- <order_by_clause>:指定应用于分区中的行时所基于的排序依据列。
  
  RANK（）函数返回结果集中每行数据在每个分区内的排名。每个分区内行的排名从1开始。如果排序时有值相同的行，则这些值相同的行具有相同的排名。
  
  [例20] 本示例适用例19的OrderDetail表，查询订单号、产品号、订购数量以及每个产品在每个订单中的订购数据排名。
```
select OrderID,ProductID,OrderQty,
	rank() over
	(partiton by OrderID order by OrderQty desc) as rank
	from OrderDetail
	order by OrderID;
```
  (2) dense_rank（）函数。dense_rank()函数与rank()函数的作用基本一样，使用方法也一样，唯一的区别是 dense_rank()函数的排名中间没有任何间断，即该函数返回的是一个连续的整数值。
  
  [例21] 将例20的查询改为用dense_rank（）函数实现。
```
select OrderID,ProductID,OrderQty,
	dense_rank() over
	(partiton by OrderID order by OrderQty desc) as dense_rank
	from OrderDetail
	order by OrderId;
```
  (3) ntile（）。ntile（）函数的作用是将有序分区中的行划分到指定数目的组中，每个组有一个编号，编号从1开始。对于每一行，ntil（）函数将返回此行所属的组的编号。
  
  ntile（）函数的语法格式如下：
```
ntile(integer_expression)
	over([<partition_by_clause>] <order_by_clause>)
```
  各参数含义同RANK（）函数。
  
  [例22] 本示例使用例19的OrderDetail表。将该表数据按订购数量降序排序，并将该表数据划分到4个组中。
  
  分析：该查询只需将全部数据划分为4组，因此不需要进行分区，在OVER子句中只需要对订购数量进行降序排序即可。
```
select OrderID,ProductID,OrderQty,
	ntile(4) over(order by OrderQty desc) as FourGroups
	from OrderDetail;
```
  (4) row_number（）函数。row_number（）函数返回结果集中每个分区内行的序列号，每个分区的第一行从1开始。
  
  row_number（）函数的语法格式如下：
```
row_number()
	over([<partition_by_clause>] <order_by_clause> )
```
  各参数含义同RANK（）函数。
  
  [例24] 查询“电冰箱”类商品的商品名、销售单价以及该商品在该类商品中的价格排名。
```
select GoodsName,SaleUnitPrice,
	row_number() over(order by SaleUnitPrice desc) as 'Number'
from Table_Goodsclass C join Table_Goods G
on C.GoodsClassID = G.GoodsClassID
where GoodsClassName = '电冰箱';
```

6.5.2 公用表表达式

对于select语句来说，为了使代码更加简洁和可读，当在一个查询中引用另外的查询结果集时，一般都是通过视图来对查询进行分解。视图是作为数据库对象存在于数据库中的，如果这个结果集仅仅需要使用一次时，使用视图就显得有些奢侈了。

公用表表达式（Common Table Expression，CTE）是SQL server2005版本之后引入的一个特性。将查询语句产生的结果集指定一个临时命名的名字，这些命名的结果集就称为公用表表达式。命名后的公用表达式后就可以在select、insert、update、delete等语句中被多次引用。公用表表达式还可以包括对自身的引用，这种表达式称为递归公用表表达式。

使用公用表表达式有如下一些好处：

可以定义递归公用表表达式。
使数据操作代码更加清晰简洁。
group by子句可以直接作用在子查询所得的标量列上。
可以在一个语句中多次引用公用表表达式。

公用表表达式的语法格式如下：

with <common_table_expression>[,...n]
<common_table_expression>::=
	expression_name[(column_name[,...n])]
as 
	(select 语句)

各参数说明如下：

expression_name:公用表表达式的标识符。expression_name必须与在同一with<common_table_expression>子句中定义的任何其他公用表表达式的名称不同，但该名可以与基本表或视图名相同。在查询中对expression_name的任何引用都会使用公共表表达式。
column_name：在公用表表达式中指定列名。在一个CTE定义中不允许出现重名的列名。
select语句：指定一个用其结果集填充到公用表表达式的select语句。

[例26] 定义一个统计每个会员的购买商品总次数的CTE，并利用该CTE查询会员卡号和购买商品总次数。
```
with BuyCount(Card,Counts) as (
	select CardID,count(*) from Table_SaleBill
	   group by CardId )
select CardID,Counts from BuyCount
	order by Counts;
```
[例27] 使用公用表表达式来限制返回结果。修改例26的查询，定义一个统计每个会员的购买商品总次数的CTE，并利用该CTE查询购买次数超过10次的会员卡号。
```
with BuyCount(Card,Counts) as (
	select CardID,count(*) from Table_SaleBill
	   group by CardId )
select CardID,Counts from BuyCount
	where Counts > 10
	order by Counts;
```
可以在一个with子句中定义多个CTE，也可以在一个查询中多次引用同一个CTE。

7.数据库及数据库对象

7.1 创建及维护数据库

7.1.1 SQL Server数据库概述

SQL Server 2008中的数据库由包含数据的表集合以及其他对象（如视图、索引、存储过程等）组成，目的是为执行与数据有关的活动提供支持。

从数据库的应用和管理角度，SQL Server将数据库分为两大类：系统数据库和用户数据库。系统数据库时SQL Server数据库管理系统自动创建和维护的。用户数据库保存的是与用户的业务有关的数据，通常所说的建立数据库指的是创建用户数据库，一般用户对系统数据库只有查询权。

安装好SQL Server2008后，系统会自动安装五个系统数据库，分别是：master,msdb,model,tempdb和Rescource，各数据库的主要用途如下：

master：是SQL Server2008中最重要的数据库，记录SQL Server实例的所有系统级信息，包括实例范围的元数据（例如登录账号）、端点、连接服务器和系统配置设置。此外，master数据库还记录了所有其他数据库的存在、数据库文件的位置以及SQL Server的初始化信息。
msdb：供SQL Server代理服务调度报警和作业以及记录操作员时使用，保存关于调度报警、作业、操作员等信息，作业是在SQL Server中定义的自动执行的一系列操作的集合，作业的执行不需要任何人工干预。
model：用作SQL Server实例上创建的所有数据库的模板。对model数据库进行的修改（如数据库大小、排序规则、恢复模式和其他数据库选项）将应用与以后创建的所有用户数据库。当用户创建一个数据库时，系统自动将model数据库中的全部内容复制到新建数据库中。
tempdb:临时数据库，用于保存临时对象或中间结果集，并为数据的排序等操作提供一个临时工作空间。每次启动SQL Server时都会重新创建tempdb数据库。
Resource:是一个只读数据库，包含了SQL Server中的所有系统对象。SQL Server系统对象在物理上保存在Resource数据库中，但在逻辑上却显示在每个数据库的sys架构中。因此，在SSMS的对象资源管理器中，在“系统数据库”下看不到这个数据库。

7.1.2 SQL server数据库的组成

SQL server将数据库映射为一组操作系统文件，这些文件将被划分为两类：数据文件和日志文件。数据文件包含数据和对象，日志文件包含恢复数据库中的所有事务需要的信息。

数据文件

数据文件用于存放数据库数据。数据文件又分为主要数据文件和次要数据文件。
- 主要数据文件：主要数据文件的推荐扩展名是.mdf,它包含数据库的系统信息，也可存放用户数据。每个数据库都有且只能有一个主要数据文件。主要数据文件是为数据库创建的第一个数据文件。SQL Server2008要求主要数据文件的大小不能小于3MB。
- 次要数据文件：次要数据文件的推荐扩展名是.ndf。一个数据库可以不包含次要数据库文件，也可以包含多个，而且这些次要数据文件可以建立在一个磁盘上，也可以分别建立在不同的磁盘上。
注：在主要数据文件之后建立的所有数据文件都是次要数据文件。
事务日志文件

事务日志文件的推荐扩展名为.ldf，用于存放恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件，也可以有多个日志文件。
数据库存储空间的分配

在考虑数据库的空间分配时，需要了解如下规则：
- 在创建用户数据库时，model数据库自动被复制到新建用户数据库中，而且是复制到主要数据文件中。
- 在SQL Server2008中，数据的存储分配单位是数据页（Page，也简称为页）。一页是一块8KB（8*1024B，其中用8060B存放数据，另外的132B存放系统信息）的连续磁盘空间。页是存储数据的最小单位，页的大小决定了数据库表中一行数据的最大大小。
- SQL server不允许表中的一行数据存储在不同页上，即行不能跨页存储。

7.1.3 数据库文件组

文件组的概念类似于操作系统中的文件夹。在数据库中，可以定义多个文件组，然后将文件放置到不同的文件组中。

SQL server有两种类型的文件组：主文件组和用户定义的文件组。

主文件组

主文件组（PRIMARY）是系统定义好的一个文件组，它包含主要数据文件和任何没有明确分配给其他文件组的其他数据文件。系统表的所有页均分配到主文件组中。
用户定义文件组

用户可以创建自己的文件组，已经数据文件组织起来，便于管理和数据分配。用户定义文件组是通过在CREATE DATABASE或ALTER DATABASE语句中使用FILEGROUP关键字指定的任何文件组。

说明：

（1）日志文件不包括在文件组内，日志空间与数据空间是分开管理的。

（2）一个文件不可以是多个文件组的成员。

（3）如果文件组中有多个文件，则它们在所有文件被填满之前不会自动增长，而填满后这些文件会循环增长。

如果在定义数据文件时没有指定其所属的文件组，则新建数据文件将被分配到默认文件组。每个数据库只能指定一个文件组为默认文件组。如果用户没有显示地用ALTER DATABASE语句修改默认文件组，则SQL Servers的默认文件组是PRIMARY。

在SQL Server2008中，可以在首次创建数据库时创建文件组，也可以在以后向数据库中添加更多数据文件时创建文件组。但是，一旦将文件添加到数据库中，就不能再将这些文件移到其他文件组中。

7.1.4 数据库文件的属性

在定义数据库时，除了指定数据库的名字外，其余要做的工作就是定义数据库的数据文件和日志文件，定义这些文件需要指定的信息包括：

文件名及其位置

数据库的每个数据文件和日志文件都具有一个逻辑文件名和物理文件名。一个数据库中逻辑文件名必须是唯一的。物理文件名包括存储文件的路径和物理磁盘文件名，该文件名必须符合操作系统文件命名规则。
初始大小

可以指定每个数据文件和日志文件的初始大小。在指定主要数据文件的初始大小时，其大小不能小于model数据库主要数据文件的大小，因为系统是将model数据库主要数据文件的内容复制到用户数据库的主要数据文件上。
增长方式

如果需要的话，可以指定文件是否自动增长。
最大大小

文件的最大大小指的是文件增长的最大空间限制。默认情况是无限制。

7.1.5 用T-SQL语句创建数据库

创建数据库的T-SQL语句为CREATE DATABASE，此语句的简化语法格式为：

create database database_name
	[ on
    	[primary] [<filespec>[,...n
        [,<filegroup>[,..n] ]
        [LOG ON {<filespec>[,...n] } ]                     
        ]
     ]
 <filespec>::={
     (NAME = logical_file_name,
     filename = {'os_file_name' | 'fileStream_path'}
     [,SIZE = size[KM | MB | GB | TB ] ]
     [,maxsize = {max_size [ KB | MB | GB | TB ] | unlimited }]
     [,filegrowth = growth_increment[KB | MB | GB | TB | % ]]
     )[,...n] }
     <filegroup>::={
     	filegroup filegroup_name[default]
 		 <filespec>[,...n]     
     }

各参数含义如下：

database_name:新数据库名。新数据库名在SQL Server实例中必须唯一。
on：指定数据文件。
primary:指定关联数据文件的主文件组。
LOG ON :指定日志文件。
:定义文件的属性。各参数含义如下:
- NAME = logical_file_name:指定文件的逻辑名称。在一个数据库中逻辑名必须唯一。
- filename=‘os_file_name’：指定操作系统（物理）文件名。
- SIZE=size：指定文件的初始大小。
- maxsize=max_size:指定文件可增大到的最大大小。
- unlimited:指定文件的增长无限制。
- filegrowth=growth_increment:指定文件的自动增量。
:文件组属性。各参数含义如下：
- filegroup filegroup_name:文件组的逻辑名称。
- default:指定该文件组为数据库的默认文件组。

在使用T—SQL语句创建进数据库时，最简单的情况是省略所有的参数，只提供一个数据库名，这时系统会按各参数的默认值创建数据库。

[例1] 创建指定一个数据文件和一个日志文件的数据库。创建一个名为RShDB的数据库，该数据库由一个数据文件和一个日志文件组成。数据文件只有主要数据文件，其逻辑文件名为RShDB_Data，物理文件名为RShDB_Data.mdf，存放在D:\RShDB_Data文件夹下，初始大小为10MB，最大大小为30MB，自动增长时递增量为5MB。日志文件的逻辑文件名为RShDB_log，物理文件名为RShDB_log.ldf，也存放在D:\RShDB_Data文件夹下，初始大小为3MB，最大大小为12MB，自动增长时的递增量为2MB。

create database RShDb
on
	(
		name = RShDB_Data,
    	filename = 'D:\RShDB_Data\RShDB_Data.mdf',
        size = 10,
        maxsize = 30,
        filegrowth = 5
	)
log on
	(
    	name = RShDB_log,
        filename = 'D:\RShDB_Data\RShDB_log.ldf',
        size = 3,
        maxsize = 12,
        filegrowth = 2
    )

7.1.6 修改数据库

扩大数据库空间

如果数据空间不够，则意味着不能再向数据库中插入数据；如果日志空间不够，则意味着不能对数据库进行任何修改操作，因为对数据库的修改操作是要记入日志的。如果出现这两种情况就需要扩大数据库空间。扩大数据库空间有两种方式：一种是扩大数据库中已有文件的大小，另一种是为数据库添加新的文件。

扩大数据库空间的ALTER DATABASE语句的语法格式为：
```
alter database database_name
{ <add_or_modify_files> }
<add_or_modify_files>::=
{
	add file <filespec>[,...n]
		[ to filegroup {filegroup_name | default } ]
	| add log file <filespec> [,...n]
	| modify file <filespec>
}
```
各参数含义如下：
- database_name：要修改的数据库名。
- <add_or_modify_files>::= ：指定要添加或修改的文件。
- to filegroup {filegroup_name | default } ：说明要将指定文件添加到的文件组。如果指定了default，则将文件添加到当前的默认文件组中。
- ：同create database语句的.
- add log file :在数据库中添加新的日志文件。
- modify file：指定要修改的文件。

[例5] 为RShDB数据库添加一个新的数据文件，逻辑文件名为RShDB_Data2,物理存储位置为E:\Data文件夹下，物理文件名为RShDB_Data2.ndf，初始大小为6MB，不自动增长。

alter database RShDB
add file(
	name = RShDB_Data2,
    filename = 'E:\Data\RShDB_Data2.ndf',
    size = 6MB,
    filegrowth = 0
)

[例7] 为RShDB数据库添加一个新的文件，逻辑文件名为RShDB_log1,物理存储位置为E:\Data文件夹下，物理文件名为RShDB_log1.ldf，初始大小为4MB，每次增加1MB，最大多增加到10MB。

alter database RShDB
add log file(
	name = RShDB_log1,
    filename = 'E:\Data\RShDB_log1.ldf',
    size = 4MB,
    filegrowth = 1MB,
    maxsize = 10MB
)

2.收缩数据库空间

收缩数据库就是释放数据库中未使用的空间，并将释放的空间交还给操作系统。文件的收缩都是从末尾开始的。收缩分为自动收缩和手工收缩。手工收缩数据库空间分为两种情况，一种是收缩数据库中某个文件的大小，另一种是按比例收缩整个数据库的大小。

（1）收缩整个数据库的大小。收缩整个数据库大小的T-SQL语句是DBCC SHRINKDATABASE，其语法格式为：

dbcc shrinkdatabase
(
'database_name' | database_id | 0
    [,target_percent]
    [,{notruncate | truncateonly }]
)

各参数含义如下：

‘database_name’ | database_id | 0 ：要收缩的数据库的名称或ID。如果指定为0，则表示收缩正在使用的数据库。
target_percent ：数据库收缩后的文件中所需的剩余可用空间百分比。
notruncate ：在数据库文件中保留所释放的文件空间。如果未指定该选项，则将所释放的空间释放给操作系统。
truncateonly ：将文件中任何未使用的空间均释放给操作系统，并将文件收缩到最后分配的大小，从而无须移动任何数据即可减少文件大小。

[例8] 收缩Students数据库，使该数据库中所有的文件都有20%的可用空间。

dbcc shrinkdatabase(Students,20);

(2)收缩指定文件的大小。收缩指定文件大小的T-SQL语句是DBCC SHRINKFILE，其语法格式为：

dbcc shrinkfile
(
	'file_name'
    {[,emptyfile]
    | [[,target_size][,{notruncate | truncateonly } ] ]
    }
)

各参数含义如下：

‘file_name’ ：要收缩的文件的逻辑名。
target_size ：指定收缩后目标文件的大小（用整数表示，单位为MB）。如果未指定，则将文件大小减少到默认文件大小。
emptyfile ：指定将文件中的所有数据迁移到同一文件组的其他文件中，使该文件为空。
notruncate ：将释放的文件空间保留在文件中。如果未指定，则所有释放的文件空间都返回给操作系统。
truncateonly ：将文件中的所有未使用空间释放给操作系统，并将文件收缩到最后一次分配的大小。

[例9] 将Students数据库中的students_data1文件收缩到4MB。

dbcc shrinkfile(students_data1,4);

3.添加和删除数据库文件

可以通过在数据库中添加文件的方法来扩大数据库空间，也可以通过删除文件的方法来减小数据库空间。

（1）添加文件。

SQL Server对每个文件组中的所有数据文件都是按比例填充数据的，而日志文件彼此是相互独立的，没有文件组。使用T-SQL的ALTER DATABASE语句可以向数据库添加文件。

（2）删除文件。

只有当文件完全为空时，才可以从数据库中删除文件。

若要让某个数据文件为空，需要将该数据文件中的数据移到同一文件组的其他文件中，这可使用DBCC SHRINKFILE语句并指定EMPTYFILE子句来实现。但将日志信息从一个日志文件移到另一个日志文件后并不能删除该日志文件。只有当日志文件中不包含任何活动或不活动的事务时才可以从数据库中删除该日志文件。

删除数据库文件的T-SQL语句是ALTER DATABASE，其语法格式为：

alter database database_name
	remove file logical_file_name

各参数含义如下：

database_name ：要删除文件的数据库名。
logical_file_name ：被删除文件的逻辑文件名。

[例10] 删除Students数据库中的students_data1文件。

alter database students
	remove file students_data1;

7.1.7 分离和附加数据库

通过分离和附加数据库的操作可以实现将数据库从一台数据库服务器移动到另一台数据库服务器，而不需要重新创建数据库的目的。

分离数据库

分离数据库是指将数据从SQL Server实例中删除，但不删除数据库的数据文件和日志文件。分离数据库实际就是让数据库的文件不受数据库管理系统的管理，使用户可以将数据库的数据文件和日志文件复制到另一台计算机上或者是同一台计算机的其他地方。

分离数据库使用的是sp_detach_db系统存储过程，其简化语法格式为：
```
sp_detach_db [@dbname= ] 'dbname'
	[,[@skipchecks= ]'skipchechs']
```
各参数说明如下：
- [@dbname= ] ‘dbname’ ：要分离的数据库名称。
- [@skipchecks= ]‘skipchechs’] ：指定跳过还是运行“更新统计信息”。如果要跳过“更新统计信息”，则指定TRUE；如果要显示运行“更新统计信息”，则指定FALSE。

[例12] 分离Students数据库，并跳过“更新统计信息”。

exec sp_detach_db'Students','true';

2.附加数据库

附加数据库就是将分离的数据库重新附和到数据库管理系统中。在附加数据库时，必须指定主要数据文件的物理存储位置和文件名，因为主要数据文件中包含查找组成该数据库的其他文件所需的信息。

附加数据库的T-SQL语句是CREATE DATABASE，语法格式如下：

create database database_name
	on <filespec> [,...n]
	for {attach | attach_rebuild_log }

各参数说明如下：

database_name : 要附加的数据库名。
: 同创建数据库语句的,用于指定要附加的数据库的主要数据文件。
for attach : 指定通过附加一组现有的操作系统文件来创建数据库。
for attach_rebuild_log ：指定通过附加一组现有的操作系统文件来创建数据库。

[例13] 附加之前已分离的Students数据库。

create database Students
	on (filename = 'F:\Data\students_data1.mdf')
	for attach;

7.2 架构

架构（Schema，也称为模式）是数据库下的一个逻辑命名空间，可以存放表、视图等数据库对象，它是一个数据库对象的容器。如果将数据库比喻为一个操作系统，那么架构就相当于操作系统中的文件夹，而架构中的对象就相当于这个文件中的文件。因此，通过将同名表放置在不同架构中，使一个数据库中可以包含同名的表。

一个数据库可以包含一个或多个架构，架构由特定的授权用户所拥有。在同一个数据库中，架构名必须唯一。属于一个架构的对象称为架构对象，即它们依赖于该架构。架构对象的类型包括基本表、视图、触发器等。

一个架构可以由零个或多个架构对象组成。架构名可以是显式的，也可以是由DBMS提供的默认名。

定义架构

定义架构的SQL语句为CREATE SCHEMA，其语法格式如下：
```
create schema [<架构名>] authorization <用户名>;
```
如果没有指定<架构名>,则<架构名>隐含为<用户名>。一个<用户名>可以拥有多个架构。执行创建架构语句的用户必须具有数据库管理员的权限，或者是获得了数据库管理员授予的CREATE SCHEMA的权限。

[例15] 为用户ZHANG定义一个架构，架构名为S_C.
```
create schema s_c authorization ZHANG;
```
[例16] 定义一个用隐含名字的架构。
```
create schema authorization ZHANG;
```
定义架构实际上就是定义了一个命名空间，在这个空间中可以进一步定义该架构的数据库对象，比如表、视图等。

在定义架构时还可以同时定义表、视图、为用户授权等，即可以在CREATE SCHEMA语句中包含CREATE TABLE、CREATE VIEW 、GRANT等语句。

[例17] 在定义架构的同时定义表。
```
create schema test authorization ZHANG
	create table T1 (
    	C1 int,
        C2 char(10),
        C3 smalldatetime,
        C4 numeric(4,1)
    );
```
删除架构

删除架构的T-SQL语句是DROP SCHEMA，其语法格式如下：
```
drop schema <架构名> {<CASCADE> |<RESTRICT>}
```
其中：
- cascade : 删除架构的同时将该架构中的所有架构对象一起全部删除。
- restrict ：如果被删除的架构中包含架构对象，则拒绝删除次架构。

7.3 分区表

7.3.1 基本概念

分区表是将表中的数据按水平方式划分成不同的子集，这些数据子集存储在数据库的一个或多个文件组中。合理地使用分区会在很大程度上提高数据库的性能。是否创建分区表主要取决于表当前的数据量大小以及将来的数据量大小，同时还取决于对表中数据进行的操作特点。通常，如果某个大型表同时满足下列条件，则比较适于进行分区：

该表包含（或将包含）以多种不同方式使用的大量数据。
数据是分段的，比如数据以年份分隔。

分区表是物理上将一个大表分成几个小表，但从逻辑上来看还是一个大表。

7.3.2 创建分区表

在SQL Server2008中，创建分区表通过以下步骤实现：

创建分区函数。
创建分区方案。
使用分区方案创建表。

（1）创建分区函数

在SQL Server2008中，创建分区函数的SQL语句是：
```
create partition function partition_function_name (input_parameter_type)
as range [left | right ]
for values([boundary_value [ ,...n ]])
[ ; ]
```
各参数说明如下：
- partition_function_name ：分区函数名。分区函数名在数据库中必须唯一。
- input_parameter_type ：用于分区的列的数据类型。
- boundary_value ：为每个分区指定边界值。
- …n : 指定boundary_value 提供的值的数目。
- left | right ：指定当间隔值由数据库引擎按升序从左到右排序时，boundary_value [,…n]属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认LEFT。

[例18] 在int列上创建左侧分区函数。下列分区函数将表分为四个分区。

create partiton function myRangePF1(int)
as range left for values (1,100,1000);

显示对分区依据列coll使用此分区函数的表进行行分区的情况。

分区	1	2	3	4
值	coll<=1	coll>1 and coll <=100	coll>100 and coll<=100	coll>1000

（2）创建分区方案

创建分区方案的SQL语句是：

create partition schema partition_schema_name 
as partition partition_function_name 
[ all ] to({file_group_name | [primary]} [ ,...n])
[ ; ]

各参数说明如下：

partition_schema_name ：分区方案名。分区方案名在数据库中必须唯一。
partition_function_name ：使用分区方案的分区函数名。
all : 指定所有分区都映射到file_group_name中提供的文件组，或映射到主文件组（如果指定了primary)。

[例22] 创建用于将每个分区映射到不同文件组的分区方案。下列代码首先创建一个分区函数，并将表分为四个分区。然后创建一个分区方案，在其中指定拥有这四个分区中每一个分区的文件组。此市例假定数据库中已经存在文件组。

create partition function myRangePF1(int)
	as range left for values(1,100,1000);
go
create partition schema myRangePS1
	as partition myRangePF1
	to (tast1fg,test2fg,test3fg,test4fg);

文件组与分区的对照关系：

文件组	tast1fg	tast2fg	tast3fg	tast4fg
分区	1	2	3	4
值	coll<=1	coll>1 and coll<=100	coll>100 and coll<=1000	coll >1000

7.4 索引

创建索引

创建索引使用的是CREATE INDEX语句。语法格式如下：
```
create [ unique ] [ clustered | nonclustered ] index index_name
	on<object>(column [ asc | desc ] [ ,...n])
	 [include (column_name [ ,...n])]
	 [where <filter_predicate>]
	 [on {partition_schema_name(column_name)
      	| filegroup_name 
      	| default
      }
     ]
     [filestream_on { filestream_filgroup_name | partition_schema_name | 'NULL'}]
     [ ; ]
     <object>::=
     {
     	[database_name.[schema_name].| schema_name.] table_or_view_name
     }
```
各参数说明如下：
- unique ：为表或视图创建唯一索引，唯一索引不允许两行具有相同的索引键值。
- clustered ：创建聚集索引。一个表或视图只允许同时有一个聚集索引。具有唯一聚集索引的视图称为索引视图。为一个视图创建唯一聚集索引会在物理上具体化该视图。必须先为视图创建唯一聚集索引，然后才能为该视图定义其他索引。
  
  在为表创建索引时，应先创建聚集索引，然后再创建非聚集索引，因为在创建聚集索引时系统会重新生成表中现有的非聚集索引。
- nonclustered ：默认选项，创建一个非聚集索引。
- index_name ：索引名。索引名在表或视图中必须唯一，但在数据库中不必唯一。
- column ：索引所基于的一个列或多个列。
- [ asc | desc ] ：指定索引列的排序方式。asc为升序，desc为降序。默认为asc。
- include (column_name [ ,…n]) ：指定要添加到非聚集索引的叶级别的非键列。非聚集索引可以唯一，也可以不唯一。
- where <filter_predicate> ：指定分区方案，该方案定义要将分区索引的分区映射到文件组。
- on filegroup_name ：为指定文件组创建索引。
- on default ：为默认文件组创建索引。
  
  [例26] 在Table_Customer表的Cname列上创建非聚集索引。
```
create index Cname_ind on Table_Customer(Cname);
```
  [例27] 在Table_Customer表的IdentityCard列上创建唯一性聚集索引。
```
create unique clustered index ID_ind
	on Table_Customer(IdentityCard);
```
  [例28] 在Table_Customer表的Cname列和CardID列上创建一个非聚集索引，要求索引键值按Cname升序和CardID降序排序。
```
create index COMP_ind on table_Customer(Cname ASC , CardID desc);
```
删除索引

如果频繁地对数据进行增加、删除和更改操作，则系统会花费很多时间来维护索引，这会降低数据的修改效率；另外，村塾索引需要占用额外的空间，这增加了数据库的空间开销。因此，当不需要某个索引时，可将其删除。

在SQL语言中，删除索引使用的是DROP INDEX语句。其一般语法格式为：
```
drop index { index_name on <object> [ ,...n ]}
<object>::=
{
	[database_name.[schema_name].|schema_name.] table_or_view_name
}
```
各参数含义同create index

[例30] 删除Table_Customer表中的Cname_ind索引。
```
drop index Cname_ind;
```

7.5 索引视图

基本概念

标准视图也称虚拟表，因为这种视图所返回的结果集的格式与基本表相同，都是由列和行组成。建有唯一聚集索引的视图称为索引视图，也称物化视图。
适合建立索引视图的场合
- 如果很少更新基础数据，则索引视图的效果最佳。
- 如果经常更新基础数据，则维护索引视图的成本可能超过使用索引视图带来的性能收益。
- 如果基础数据以批处理的形式定期更新，但在更新之间主要作为只读数据进行处理，则可考虑在更新前删除所有索引视图，然后再重建索引视图，这样做可以提高更新的性能。
  
  索引视图可以提高下列查询类型的性能：
  - 处理大量行的连续和集合。
  - 许多查询经常执行的连接和聚合操作。
  索引视图通常不会提高下列查询类型的性能：
  - 具有大量写操作的OLTP系统。
  - 具有大量更新操作的数据库。
  - 不涉及聚合或连接的查询。
  - GROUP BY列具有高基数度的数据聚合。高基数度表示列包含许多不同的值。
定义索引视图

在对视图创建聚集索引之前，该视图必须符合下列要求：
- 定义索引视图时，视图不能引用任何其他视图，只能引用基本表。
- 视图引用的所有基本表必须与视图位于同一个数据库中，并且所有者也与视图相同。
- 必须使用SCHEMABINDING选项创建视图。
- 视图中的表达式引用的所有函数必须是确定的。
- 对视图创建的第一个索引必须是唯一聚集索引，之后再创建其他的非聚集索引。

7.6 小结

视图是一种由从数据库的基本表中选取出来的数据组成的对象，可以像查询基本表一样查询视图中的数据。视图主要被用于简化数据查询语句、定制数据以限制用户对数据库数据的操作，视图也可为应用程序提供一定程度的逻辑独立性。

8.数据库后台编程技术

8.1 存储过程

8.1.1 基本概念

使用T-SQL语句编写代码时，可以用两种方法存储和执行代码。一种是在客户端存储代码，通过客户端的程序或SQL命令向数据库管理系统发出对数据库的操作请求，由数据库管理系统将操作结果返回给用户程序；第二种是以子程序的形式将程序模块存储在数据库中，供有权限的用户通过调用反复执行。所谓存储过程，实际上是存储在数据库中供所有用户程序调用的子程序。

存储过程与其他程序设计语言中的子程序很类似，因为存储过程可以：

接收输入参数并以输出参数的形式将很多个值返回给调用过程。
包含执行数据库操作（包括调用其他存储过程）的编程语句。
向调用过程返回状态值，以表名执行成功或失败。

使用存储过程的好处有：
- 允许模块化程序设计。
- 改善性能。
- 减少网络流量。
- 增强应用程序的安全性。

8.1.2 创建、执行和删除存储过程

创建存储过程

创建存储过程的SQL语句为CREATE PROCEDURE。其语法格式为：
```
create {proc | procedure } [schema_name.] procedure_name
	[{@parameter [type_schema_name.] data_type }
    	[=default] [out | output ]
    ] [,...n][with recompile ]
   as { <sql_statement> [;][...n]}
   [;]
   <sql_statement>::={[begin] statements [end ]}
```
其中各参数说明如下：
- schema_name ：过程所属的架构名。
- procedure_name ：存储过程名，该名称在架构中必须唯一。
- @parameter ：存储过程的参数。
- [type_schema_name.] data_type ：参数以及所属架构的数据类型。
- default ：参数的默认值。
- output ：提示参数是输出参数。
- recompile ：指示数据库引擎不缓存存储过程的计划，该存储过程在运行时将被重新编译。
- sql_statement ：将要包含在存储过程中的一个或多个T-SQL语句。
执行存储过程

执行存储过程可以使用T-SQL的EXECUTE语句。如果存储过程是批处理中的第一条语句，那么不使用EXECUTE关键字也可以执行存储过程。

EXECUTE语句的语法格式为：
```
[ { EXEC | EXECUTE } ]
{
	[@return_status=]
	{proc_name}
		[[@parameter_name=] { value 
         					| @variable[ OUTPUT ] | [DEFAULT]
         											}
         ]
         [,...n]
         [WITH RECOMPILE]
         }
         
}
```
各参数说明如下：
- @return_status ：可选的整形变量，存储过程的返回状态。
- proc_name : 要调用的存储过程名。
- @parameter ：存储过程的参数，必须与存储过程中定义的相同。
- @variable : 是用来存储参数或返回参数的变量。
- DEFAULT ：使用定义存储过程时为参数指定的默认值。

[例1] 带有复杂select语句的存储过程。建立查询地址在海淀区的顾客的购买情况的存储过程，列出顾客姓名、购买的商品名、单价、购买日期、会员积分数。

create procedure p_CustBuy1
as
	select CName,GoodsName,SaleUnitPrice,SaleData,b.Score
	from Table_Customer a join Table_Card b on a.CardID = b.CardID
	join Table_SaleBill c on c.CardID = b.CardID
	join Table_saleBillDetail d on d.SaleBillID = c.SaleBillID
	join Table_Goods e on e.GoodsID = d.GoodsID
	where Address = '北京市海淀区';

[例2] 带有输入参数的存储过程。建立查询地址在指定地区的顾客的购买情况的存储过程，列出顾客姓名、顾客的商品名、单价、购买日期、会员积分数。

create procedure p_CustBuy2
  @area as varchar(20)
as
	select CName,GoodsName,SaleUnitPrice,SaleData,b.Score
	from Table_Customer a join Table_Card b on a.CardID = b.CardID
	join Table_SaleBill c on c.CardID = b.CardID
	join Table_saleBillDetail d on d.SaleBillID = c.SaleBillID
	join Table_Goods e on e.GoodsID = d.GoodsID
	where Address = @area;

执行例2定义的存储过程，查询"北京市朝阳区"的顾客的购买情况。

exec p_CustBuy2 '北京市朝阳区';

[例3] 带有多个输入参数并有默认值的存储过程。建立查询某个指定地区购买了单价高于指定价格的商品的顾客的购买信息，列出顾客姓名、购买的商品名、单价、购买日期、会员积分数。其中默认地区为“北京市朝阳区”。

create procedure p_CustBuy3
  @area as varchar(20) = '北京市海淀区' , @Price money
as
	select CName,GoodsName,SaleUnitPrice,SaleData,b.Score
	from Table_Customer a join Table_Card b on a.CardID = b.CardID
	join Table_SaleBill c on c.CardID = b.CardID
	join Table_saleBillDetail d on d.SaleBillID = c.SaleBillID
	join Table_Goods e on e.GoodsID = d.GoodsID
	where Address = @area and SaleUnitPrice > @Price;

执行例3的存储过程：

exec p_CustBuy3 @Price = 1000;

相当于执行了：

exec p_CustBuy3 '北京市海淀区' , 1000;

执行有多个输入参数的存储过程时，参数的传递方式有两种：

①按参数位置传递值

②按参数名传递值

3.删除存储过程

删除存储过程使用DROP PROCEDURE语句，该语句可以从当前数据库中删除一个或多个存储过程。其语法格式为：

drop { proc | procedure } { [schema_name.] procedure} [,...n]

[例4] 删除p_CustBuy1存储过程。

drop proc p_CustBuy1;

8.2 用户定义函数

用户定义函数与编程语言中的函数类似，其结构与存储过程类似，但函数必须有一个return子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

SQL Server2008支持两类用户定义函数：标量函数和表值函数，标量函数只返回单个数据值，表值函数将返回一个表。表值函数又分为内联表值函数和语句表值函数。

8.2.1 创建和调用标量函数

定义标量函数
```
create function [schema_name.] function_name
( [{@parameter_name [as] [type_schema_name.] parameter_data_type 
   [=default]}
  [,...n]
  ]
)
returns return_data_type
[as]
 begin
 		function_body
 	return scalar_expression
 end
[;]
```
各参数说明如下：
- schema_name : 用户定义函数所属架构的名称。
- function_name : 用户定义函数的名称，该名称必须符合有关标识符的规则，并且在数据库中以及其架构来说是唯一的。
- @parameter_name : 用户定义函数中的参数。
- [type_schema_name.] parameter_data_type ：参数的数据类型及其所属的架构，后者为可选项。
- [=default] ：参数的默认值。
- return_data_type ：用户定义函数的返回值类型。
- function_body : 定义函数值的一系列T-SQL语句。
- scalar_expression : 指定标量函数返回的标量值。
  
  [例10] 创建计算立方体体积的标量函数，此函数有三个输入参数，分别为立方体的长、宽和高，类型均为整型，函数的返回值的类型也为整型。
```
create function dbo.CubicVolume
  (@CubeLength int , @CubeWidth int , @CubeHeight int)
 returns int
as
 begin
  return (@CubeLength * @CubeWidth * @CUbeHeight)
 end
```
  [例11] 创建查询指定商品类别的商品种类数的标量函数。
```
create function dbo.f_GoodsCount(@class varchar(10))
  returns int
as
 begin
   declare @x int
   select @x = count(*) from Table_GoodsClass a join Table_Goods b
    on a.GoodsClassID = b.GoodsClassID
    where GoodsClassName = @class
   return @x
 end
```
调用标量函数

当调用标量函数时，必须提供至少由两部分组成的名称：函数拥有者名和函数名。

调用例10定义的函数，计算长、宽、高分别为4、6、8的立方体的体积的SQL语句为：
```
select dbo.CubicVolume(4,6,8);
```
调用例11定义的函数，查询“服装”类商品的商品名和种类数的SQL语句为：
```
select GoodsName as 商品名 , dbo.f_GoodsCount('服装') as 种类数
	from Table_GoodsClass a join Table_Goods b
	on a.GoodsClassID = b.GoodsClassID
	where GoodsClassName = '服装';
```

8.2.2 创建和调用内联表值函数

内联表值函数的返回值是一个表，该表的内容是一个查询语句的结果。

创建内联表值函数

定义内联表值函数的语法为：

create function [schema_name.] function_name
([{@parameter_name [ as ] [type_schema_name.] parameter_data_type
  [=default]
  [,...n]}
 ]
)
returns table
 [as]
  return [(] select_stmt [)]
[;]

其中，select_stmt是定义内联表值函数返回值的单个select语句。其他各参数含义同标量函数。

在内联表值函数中，通过单个select语句定义table返回值。内联表值函数没有相关联的返回变量，也没有函数体。

[例12] 创建查询指定类别的商品名称和单价的内联表值函数。

create function f_GoodsInfo(@class char(10))
	returns table
as
	return (
      select GoodsName,SaleUnitPrice from Table_GoodsClass a
    	join Table_Goods b on a.GoodsClassID = b.GoodsClassID
    	where GoodsClassName=@class)

调用内联表值函数

对内联表值函数的使用与视图非常类似，需要放置在查询语句的from子句部分，它的作用很像是带参数的视图。

利用例12定义的内联表值函数，查询“服装”类的商品信息的SQL语句为：
```
select * from dbo.f_GoodsInfo ('服装');
```

8.2.3 创建和调用多语句表值函数

多语句表值函数的功能是视图和存储过程的组合，可以利用多语句表值函数返回一个表。

创建多语句表值函数

create function [schema_name.] function_name
([{@parameter_name [as] [type_schema_name.] parameter_data_type
  [=default]}
  [,...n]
 ]
)
returns @return_variable table <table_type_definition>
	[as]
	  begin
	    function_body
	    return
	  end
	[;]
	<table_type_definition>::=
	({<column_definition> <column_constraint>
     |<computed_column_definition>}
           [<table_constraint>] [,...n]
    )

各参数说明如下：

function_body ：是一系列T-SQL语句，这些语句用于填充table返回变量。

table_type_definition : 定义返回的表的结构，该表结构的定义同创建表的语句。在表结构定义中，可以包含列定义、列约束定义、计算列以及表约束定义。

[例13] 定义查询指定类别的商品的名称、单价上产日期和新旧商品的多语句表值函数，其中新旧商品的值为：如果到目前为止此商品的生产月数超过12个月，则为“旧商品”；若生产月数在6~12月之间，则为“一般商品”；若生产月数小于6个月，则为“新商品”。

create function f_GoodsType(@class varchar(20))
	retruns @f_GoodsType table(
    	商品名 varchar(50),
    	单价 money,
    	生产日期 datatime,
    	类型 varchar(10) )
   as
    begin
    insert into @f_GoodsType
      select GoodsName,SaleUnitPrice,ProductionDate,CASE
        when datediff(month,ProductionDate,'2007/2/10') > 12
        		then '旧商品'
        when datediff(month,ProductionDate,'2007/2/10') between 6 and 12 then '一般商品'
        when datediff(month,ProductionDate,'2007/2/10') < 6
        		then '新商品'
        	end
        	from Table_GoodsClass a join Table_Goods b 
        	on a.GoodsClassID = b.GoodsClassID
        	where GoodsClassName = @class
        return
        end

注：在定义函数时，也可以调用另一个已定义好的函数。

调用多语句表值函数

多语句表值后函数的返回值是一个表。

调用例13定义的函数，查询“家用电器”类商品信息的SQL语句为：
```
select * from dbo.f_GoodsType ('家用电器');
```

8.2.4 删除用户自定义函数

删除函数使用DROP FUNCTION语句实现，它从当前数据库中删除一个或多个用户定义函数。其语法格式为：

drop function { [schema_name.] function_name} [,...n]

[例15] 删除f_GoodsType函数。

drop function f_GoodsType;

8.3 触发器

8.3.1 基本概念

触发器是一种特殊的存储过程，其特殊性在于它不需要由用户来直接调用，而是在对表中的数据进行UPDATE、INSERT或DELETE操作时自动触发执行的。触发器通常用于保证业务规则和数据完整性。

触发器通常用在下列场合：

完成比CHECK约束更复杂的数据约束。
为保证数据库性能而维护的非规范化数据。
可实现复杂的商业规则。
触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

SQL Server2008支持三种类型的触发器：DML、DDL和登录触发器。如果用户要通过数据操作语言（DML）事件编辑数据，则执行DML触发器。DML事件是针对表或视图的INSERT、UPDATE或DELETE语句。

8.3.2 创建触发器

建立DML触发器的SQL语句为CRESTE TRIGGER，其语法格式为：

create trigger [schema_name.] trigger_name
on { table | view }
{for | after | instead of }
{[insert] [,] [update] [,] [delete]}
as {sql_statement}
[;]

各参数说明如下：

schema_name : 触发器所属架构的名称。
trigger_name : 触发器名称。不能以#或##开头。
table | view : 与触发器相关联的表或视图，有时称为触发器表或触发器视图。在视图上只能定义INSTEAD OF触发器。
for | after : 指定触发器只有在引发的SQL语句中指定的操作都以成功执行，并且所有的约束检测也成功完成后，才执行此触发器。不能在视图上定义after触发器。
instead of : 指定执行触发器而不是执行引发触发器执行的SQL语句，从而替代触发语句的操作。
insert,delete和update ：引发触发器执行的操作，若同时指定多个操作，则各操作之间用逗号隔开。

创建触发器时，需要注意如下几点：
- 在一个表上可以建立多个名称不同、类型各异的触发器，每个触发器可由所有三个操作来引发。对于after型的触发器，可以在同一种操作上建立多个触发器；对于instead of型的触发器，在同一种操作上只能建立一个触发器。
- 大部分T-SQL语句都可用在触发器中，但也有一些限制。例如，所有的建立和更改数据库以及数据库对象的语句、所有的DROP语句都不允许在触发器中使用。
- 在触发器定义中，可以使用if update子句来测试insert和update语句是否对指定字段有影响。
- 通常不要在触发器中返回任何结果。

在触发器语句中可以使用两个特殊的临时工作表：inserted表和deleted表。这两个表是在用户执行数据的更改操作时，SQL Server自动创建和管理的。

deleted表用于存储delete和update语句所影响的行的副本。

inserted表用于存储insert和update语句所影响的行的副本。

创建后触发型触发器

使用for或after选项定义的触发器为后触发型触发器，即只有在引发触发器执行的语句中的操作都已成功执行，并且所有的约束检查也成功完成后，才执行触发器。

[例16] 维护数据操作完整性的触发器。当销售单据明细表中的商品销售数量大于此商品的库存数量（在商品表中）时，撤销此次商品的销售并给出提示信息。如果销售数量小于库存数量，则在插入销售单据明细记录时，应同时修改此商品的库存数量。代码如下：
```
create trigger OperateCon
	on Table_SaleBillDetail for insert
as
	if exists (select * from inserted a
              join Table_Goods b on a.GoodsID = b.GoodsID
              where a.Quantity > b.TotalStorage)
 begin
 	rollback
 	print '此商品的库存数量小于此次销售数量'
 end
 else
 	update Table_Goods set TotalStorage = TotalStorage - (select Quantity from inserted);
```

创建前触发型触发器

使用Instead of选项定义的触发器为前触发型触发器。

在表或视图上，每个insert、update、delete语句最多可以定义一个instead of触发器。

[例19] 创建保证销售单据表中使用的会员卡是有效日期内的会员卡的触发器。

create trigger CardValid
  on Table_SaleBill instead of insert,update
as
  if not exists (select * from inserted a
                join Table_Card b on a.CardID = b.CardID
                where SaleDate not between StartDate and EndDate)
     insert into Table_SaleBill select * from inserted;

8.3.3 删除触发器

删除触发器使用DROP TRIGGER语句来实现，它从当前数据库中删除一个或多个触发器。其中语法格式为：

drop trigger schema_name.trigger_name [,...n] [;]

[例21] 删除名为CardVaild的触发器的SQL语句为：

drop trigger CardVaild;

8.4 游标

一般在使用select语句进行查询时，就可以得到结果集，但有时用户需要对结果集的每一行或部分行进行单独的处理，这在select的结果集中是无法实现的。游标就是提供这种机制的结果集扩展，它使人们可以逐行处理结果集。

8.4.1 游标的组成

游标（Cursor）包括如下两部分内容：

游标结果集：指定义游标的select语句返回的结果的集合。
游标当前行指针：指向该结果集中的某一行的指针。

游标具有如下特点：
允许定位结果集中的特定行。
允许从结果集的当前位置检索一行或多行。
支持对结果集中当前行的数据进行修改。
为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持。

8.4.2 使用游标

使用游标的过程如下：

声明游标

ISO声明游标的简化语法格式为：
```
declare cursor_name [insensitive] [scroll] CURSOR
	for select_statement
	[for {read only | update [of column_name [,...n]]}]
```
各参数含义如下：
- cursor_name ：所定义的服务器游标名。
- insensitive : 定义一个游标，以创建将由该游标使用的数据的临时复本。
- scroll : 指定所有的提取选项（first,last,prior,next,relative,absolute）均可用。
- select_statement : 定义游标结果集的标准select语句。
- read only ：禁止通过该游标更新数据。在update或delete语句的where current of子句中不能引用该游标。
- update [of column_name [,…n]] ：定义游标中可能更新的列。
打开游标

打开游标的语句是OPEN，其语法格式为：
```
OPEN cursor_name;
```
提取数据

游标被声明和打开之后，游标的当前行指针就位于结果集中的第一行位置，可以使用FETCH语句从游标结果集中按行提取数据。其语法格式为：
```
fetch [[next | prior | first | last
       | absolute n
       | relative n ]
      from
      ]
 cursor_name [into @variable_name [,...n] ]
```
各参数含义如下：
- next ：返回紧跟在当前行之后的数据行，并且当前行递增为结果行。如果fetch next是对游标的第一次提取操作，则返回结果集中的第一行。
- prior ：返回紧跟当前行前面的数据行，并且当前行递减为结果行。
- first ：返回游标中的第一行并将其作为当前行。
- last ：返回游标中的最后一行并将其作为当前行。
- absolute n ：如果n为正数，返回从游标第一行开始的第n行，并将返回的行作为当前行。
- relative n ：如果n为正数，则返回当前行之后的第n行，并将返回的行成为新的当前行。
- cursor_name ：要从中进行提取数据的游标名。
- into @variable_name [,…n] : 将提取的列数据保存到局部变量中。
关闭游标

关闭游标使用CLOSE 语句，其语法格式为：
```
close cursor_name;
```
在使用close语句关闭游标后，系统并没有完全释放游标的资源，并且也没有改变游标的定义，当再次使用open语句时可以重新打开此游标。
释放游标

释放游标是释放分配给游标的所有资源。释放游标使用DEALLOCATE语句，其语法格式如下：
```
deallocate cursor_name;
```

8.4.3 游标示例

[例22] 对Table_Customer表，定义一个查询“北京市海淀区”姓“王”的顾客姓名和邮箱的游标，并输出游标结果。

declare @cn varchar(10), @Email varchar(50)
declare Cname_cursor CURSOR FOR
  select Cname,Email from Table_Customer
     where Cname like '王%' and Address like '北京市海淀区'
open Cname_cursor
fetch next from Cname_cursor into @cn , @Email
where @@FETCH_STATUS = 0
BEGIN
  PRIOR '顾客姓名:'+@cn +',邮箱:'+@Email
  FETCH NEXT FROM Cname_cursor INTO @cn ,@Email
end
close Cname_cursor
deallocate Cname_cursor

9.安全管理

9.1 安全控制概述

安全性是指保护数据以防止不合法的使用而造成数据被泄露、更改和破坏；完整性是指数据的准确性和有效性。通俗地讲：

安全性（Security）：保护数据以防止不合法用户故意造成的破坏。
完整性（Integrity）：保护数据以防止合法用户无意中造成的破坏。

在数据库中，对有意的非法活动可采用加密存、取数据的方法控制；对有意的非法操作可使用户身份验证、限制操作权限来控制；对无意的损坏可采用提高系统的可靠性和数据备份等方法来控制。

数据库安全控制的目标

数据库安全控制的目标是保护数据免受意外或故意的丢失、破坏或滥用。

数据库管理员（Data Base Administrator，DBA）负责数据库系统的全部完全。
数据库安全的威胁

全面的数据库安全计划必须考虑下列情况：
- 可用性的损失
- 机密性数据的损失
- 私密性数据的损失
- 偷窃和欺诈
- 意外的损害
安全控制模型

在一般的计算机系统中，完全措施是一级一级层层设置的。
授权和认证

授权是将合法访问数据库或数据库对象的权限授予用户的过程。认证是一种坚定用户身份的机制。

现在的DBMS通常采用自主存取控制和强制存取控制两种方法来解决数据库安全系统的访问控制问题。
- 自主存取控制（Discretionary Control）
- 强制存取控制（Mandatory Control）

9.2 存取控制

9.2.1 自主存取控制

大型数据库管理系统几乎都支持自主存取控制（又称为自主安全模式），目前的SQL标准也对自主存取控制提供支持，这主要是通过SQL的**GRANT（授予）、REVOKE（收回）和DENY（拒绝）**语句来实现。

授予和收回权限是数据库管理员（DBA）的职责。

权限种类

在自主存取控制中，通常将数据库中的权限划分为两类。一类是对数据库管理系统进行维护的权限，另一类是对数据库中的对象和数据进行操作的权限，这类权限又可分为两类：一类是对数据库对象的操作权限，包括创建、删除和修改数据库对象，称为语句权限；另一类是对数据库数据的操作权限，包括对表、视图数据的增、删、改、查权限，存储过程的执行权等，称为对象权限。
用户分类

在自主存取控制中，一般将数据库中的用户按其操作权限的不同划分为一下三类：

（1）系统管理员。系统管理员在数据库服务器上具有全部的权限。

（2）数据库对象拥有者。创建数据库对象的用户即为数据库对象拥有者。数据库对象拥有者对其所拥有的对象具有全部权限。

（3）普通用户。普通用户具有对数据库数据的操作权限。

9.2.2 强制存取控制

在强制存取控制中，DBMS将全部实体划分为主体和客体两大类。

主体是系统中的活动实体，既包括DBMS所管理的实际用户，也包括代表用户的各个进程。客体是系统中的被动实体，是受主体操纵的，包括文件、基本表、索引、视图等。对于主体和客体，DBMS为它们的每个实例指派一个敏感度标记（Label）。

敏感度标记被分为若干级别，例如绝密（Top Secret）、秘密（Secret）、可信（Confidential）和公开（public）等。主体的敏感度标记被称为许可证级别（Clearance Level）、客体的敏感度标记被称为密级（Classification Level）。

当某一用户（或某一主体）以标记Label注册到系统时，系统要求对他对任何客体的存取必须遵循如下原则：

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

通用安全性分级模式，共定义了D、C、B和A四类安全级别，从D类到A类级别依次增高。D类提供最小（Minimal）保护，C类提供自主（Discretionary）保护，B类提供强制（Mandatory）保护，A类提供验证（Verified）保护。

（1）自主保护。

（2）强制保护。

（3）验证保护。

9.3 审计跟踪

审计跟踪实质上是一种特殊的文件或数据库，系统在上面自动记录用户对常规数据的所有操作。

9.4 统计数据库的安全性

统计数据库允许用户查询聚合类型的信息，但不允许查询个人信息。

9.5 SQL Server的安全控制

9.5.1 身份验证模式

Windows身份验证

对于SQL Server来说，一般推荐使用Windows身份验证模式，因为这种安全模式能够与Windows操作系统的安全系统集成在一起，以提供更多的安全功能。
混合身份验证模型

SQL Server身份验证的登录信息（用户名和密码）都保存在SQL Server实例上，而Windows身份验证的登录信息是由Windows和SQL Server实例共同保存的。

9.5.2 登录账户

SQL Server2008的安全权限是基于标识用户身份的登录标识符（Login ID，登录ID）的，登录ID就是控制访问SQL Server数据库服务器的用户账户。如果未指定，则不能登录。

在SQL Server2008中，有两类登录账户。一类是由SQL Server自身负责身份验证的登录账户；另一类是登录到SQL Server的Windows网络账户，可以是组账户或用户账户。在安装完SQL Server2008之后，系统本身会自动地创建一些登录账户，称为内置系统账户，用户也可以根据自己的需要创建自己的登录账户。

建立登录账户

创建新的登录账户的T-SQL语句是CREATE LOGIN，其简化语法格式为：
```
create login login_name {with <option_list1> | from <source> }
<source>::=
	WINDOWS [WITH <windows_options>[,...]]
<option_list1>::=
	password='password'[,<option_list2>[,...]]
<option_list2>::=
	SID=sid
	| DEFAULT_DATABASE=database
	| DEFAULT_LANGUAGE=language
<windows_options>::=
	DEFAULT_DATABASE=database
	| DEFAULT_LANGUAGE=language
```
其中各参数的含义为：
- login_name ：指定创建的登录名。有四种类型的登录名：SQL Server身份验证的登录名、Windows身份验证的登录名、证书映射的登录名和非对称密钥映射的登录名。
- WINDOWS ：指定将登录名映射到Windows用户名。
- PASSWORD=‘password’ ：仅适用于SQL Server身份验证的登录名。指定新建登录名的密码。
- SID=sid ：仅适用于SQL Server身份验证的登录名。
- DEFAULT_DATABASE=database ：指定新建登录名的默认数据库。
- DEFAULT_LANGUAGE=language ：指定新建登录名的默认语言。
  
  [例1] 创建SQL Server身份验证的登录账户。登录名为SQL_User1，密码为a1b2c3XY。
```
create login SQL_User1 with password='a1b2c3XY'
```
  [例2] 创建Windows身份验证的登录账户。从Windows域账户创建[TEST\Win_User2]登录账户。
```
create login [TEST\Win_User2] from WINDOWS
```
  [例3] 创建SQL Server身份验证的登录账户。登录名为SQL_User3，密码为AD4HN45NPO。要求该登录账户首次连接服务器时必须要更改密码。
```
create login SQL_User3 with password='AD4HN45NPO'
MUST_CHANGE
```
修改登录账户属性

修改登录账户属性的T-SQL语句是ALTER LOGIN，其语法格式为：
```
ALTER LOGIN login_name
	{
	<status_option>
	| WITH <set_option>[,...]
	}
<status_option>::=
	ENABLE | DISABLE
<set_option>::=
	PASSWORD='password'
	[
        OLD_PASSWORD='oldpassword'
    ]
    | DEFAULT_DATABASE=database
    | DEFAULT_LANGUAGe=language
    | NAME=login_name
```
其中各参数含义为：
- login_name ：指定正在更改的登录名。
- ENABLE | DISABLE ：启用或禁止此登录名。
- PASSWORD=‘password’ ：仅适用于SQL Server身份验证的登录账户。指定正在更改的登录名密码。
- OLD_PASSWORD=‘old_password’ ：仅适用于SQL Server身份验证的登录账户。要指定新密码的登录名的当前密码。
- DEFAULT_DATABASE=database ：指定将指派给该登录名的默认数据库。
- DEFAULT_LANGUAGe=language ：指定将指派给该登录名的默认语言。
- NAME=login_name ：重命名该登录名的新名称。
  
  [例4] 启用已禁止的登录账户。启用SQL_User1登录账户。
```
alter login SQL_User1 enable
```
  [例5] 更改登录账户的密码。将SQL_User1登录账户的密码改为lasPwd134。
```
alter login SQL_User1 with password='lasPwd134'
```
  [例6] 更改登录账户名。将SQL_user3登录账户改为NewUser。
```
alter login SQL_User3 with name=NewUser
```
删除登录账户

删除登录账户的T-SQL语句为DROP LOGIN，其语法格式为：
```
drop login login_name
```
注意：不能删除正在使用的登录账户，也不能删除拥有任何数据库对象、服务器级别对象的登录账户。

[例7] 删除SQL_User2登录账户。
```
drop login SQL_User2
```

9.5.3 数据库用户

用户在有了登录账户之后，他只能连接到SQL Server数据库服务器上，并不具有访问任何用户数据库的权限，只有成为某数据库的合法用户之后才能访问该数据库。

数据库用户一般都是来自服务器上已有的登录账户，让登录账户成为数据库用户的操作称为“映射”。一个登录账户可以映射为多个数据库中的用户。默认情况下，新建立的数据库只有一个用户：dbo，它是数据库的拥有者。

建立数据库用户

建立数据库用户的T-SQL语句是CREATE USER，其简化的语法格式为：
```
create user user_name [{ {for | from }
      {
       login login_name                
      }                 
      }]
```
其中各参数说明如下：
- user_name ：指定在此数据库中用于识别该用户的名称。
- login login_name : 指定要映射为数据库用户的SQL Server登录名。必须是有效的登录名。
  
  注意：
  - 如果省略for login，则新的数据库用户将被映射到同名的SQL_Server登录名。
  - 不能使用create user语句创建guest用户，因为每个数据库中均已存在guest用户。默认guest用户是禁用状态，可通过授予guest用户connect权限来启用该用户。
[例8] 使SQL_User2登录账户成为某数据库中的用户，并且用户名同登录名。
```
create user SQL_User2
```
[例9] 首先创建名为SQL_JWC且具有密码的SQL Server身份验证的服务器登录名，然后在test数据库中创建与此登录名对应的数据库用户JWC。
```
create login SQL_JWC
	with password='jkJ13%N03kdj'
go
use test
go
create user JWC for login SQL_JWC
go
```
Guest用户

guest是SQL Server中一个特殊的数据库用户，在实际应用中经常作为数据库的匿名访问者使用。

启用guest用户（使guest用户具有连接权限）的语句如下：
```
create connect to guest;
```
禁用guest用户（收回guest用户的连接权限）的语句如下：
```
revoke connect to guest;
```
删除数据库用户

从当前数据库中删除一个用户，实际是解除登录账户和数据库用户之间得到映射关系，但并不影响登录账户的存在。删除数据库用户之后，其对应的登录账户依然存在。

删除数据库用户的T-SQL语句是DROP USER，其语法格式为：
```
drop user user_name
```

9.5.4 权限管理

当登录账户成为数据库中的合法用户之后，该账户除了具有一些系统视图的查询权限之外，并不具有数据库中的用户数据和对象具有任何操作权限，因此，下一步就需要为数据库中的用户授予数据库数据以及对象得到操作权限。

对象级别的权限

数据库对象上允许的操作权限可以分为六种不同的类型，如下表所示。

操作权限	使用说明
SELECT	允许用户查询数据。可以在表、视图和表值函数上授予用户权限。
INSERT	允许用户插入数据。可以在表、视图和表值函数上授予用户该权限。在SQL Server中，拥有INSERT权限的用户并不意味着一点拥有SELECT权限。
UPDATE	允许用户修改数据。可以在表、视图和表值函数上授予用户该权限。在SQL Server中，拥有UPDATE权限的用户并不意味着一点拥有SELECT权限。
DELETE	允许用户删除数据。可以在表、视图和表值函数上授予用户该权限。在SQL Server中，拥有DELETE权限的用户并不意味着一点拥有SELECT权限。
REFERENCES	如果用户要插入数据的表中有外键约束，而用户在该外键所引用的表上没有SELECT权限，则拥有该权限的用户能够向这样的表中插入数据。可以在表、视图、标量函数、表值函数上授予用户权限。
EXECUTE	允许用户具有执行存储过程和标量函数的权限。可以在存储过程、标量函数上授予用户该权限。

（1）授权语句

Grant <permission> [,...]
 on
  [object::] [schema_name].object_name [(column[,...] )]
   to<database_principal>[,...]
   [with grant option]
 <permisssion>::=
    all [privileges] | permission [(column[,...])]

其中各参数含义如下：

permission : 指定可以授权的对象权限。
all ：指授予适用于指定对象的所有ANSI-92权限。对于不同的对象，all的含义有所不同：
- 标量函数权限：EXECUTE、REFERENCES。
- 表值函数权限：DELETE、INSERT、DEFERENCES、SELECT、UPDATE。
- 存储过程权限：EXECUTE。
- 表权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。
- 视图权限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。
privileges：包含此参数是为了符合ANSI-92。
on [object::] [schema_name].object_name : 指定被授予权限的对象。
to<database_principal> ：指定要向其授予权限的主体，可以是数据库用户名，也可以是用户定义的角色名。
with grant option ：指示该主体还可以向其他主体授予所指定的权限。
column : 指定表、视图或表值函数中被授予权限的列名。只能在SELECT、REFERENCES及UPDATE操作上对列进行授权。

[例10] 授予用户RosaQdm对Address表具有SELECT权限。
```
grant select on Address to RosaQdm
```
[例11] 授予用户RosaQdm对HumanResources.EmployeeInfo存储过程具有EXECUTE权限。
```
grant execute on object::HUmanResources.EmployeeInfo to RosaQdm
```
[例12] 使用GRANT OPTION选项，授予用户Wanida对vEmployee视图中EmployeeID列具有REFERENCES权限。
```
grant references(EmployeeID) on vEmployee to Wanida with grant option
```
（2）拒绝权限

拒绝用户具有某权限使用的是DENY语句。
```
deny<permission>[,...n] on
	[object::] [schema_name].object_name[(column[,...n])]
		to<database_principal>[,...]
	[cascade]
	[as<database_principal>]
```
其中，cascade表示要拒绝的权限也会被对此主体授予该权限的其他主体拒绝。

[例13] 拒绝用户RosaQdm对Person.Address表具有SELECT权限。
```
deny select on object::Address to RosaQdm
```
[例14] 拒绝用户RosaQdm对HumanResources.EmployeeInfo存储过程具有EXECUTE权限。
```
deny execute on HumanResources.EmployeeInfo to RosaQdm
```
（3）收权语句

收权也就是将已授给用户的权限收回来，即不允许用户再具有该权限。收权语句使用REVOKE。
```
revoke[all[privileges]] | <permission>[,...]
	on
	[object::][schema_name].object_name[(column[,...])]
	{from | to}<database_principal>[,...]
	[cascade] [as<rode_name>]
```
其中：
- cascade ：对应grant语句中的with grant option,表示要撤销的权限也会从此主体授予或拒绝该权限的其他主体中撤销。
- as<rode_name> ：指定要基于哪个角色发出这个命令。
[例16] 撤销用户RosaQdm对Person.Address表的SELECT权限。
```
revoke select on object::Person.Address from RosaQdm
```
[例17] 撤销用户RosaQdm对HumanResources.EmployeeInfo存储过程的EXECUTE权限。
```
revoke execute on HumanResources.EmployeeInfo from RosaQdm
```

语句级别的权限

在数据库中，不但可以将用户权限与数据库中的对象关联起来，而且还可以将用户权限与特定对象的语句关联起来。SQL Server允许对执行不同的语句进行权限控制，这些语句主要包括：
- CREATE DATABASE
- CREATE PROCEDURE
- CREATE TABLE
- CREATE VIEW
- CREATE FUNCTION
- BACKUP DATABASE
- BACKUP LOG
其中BACKUP DATABASE和BACKUP LOG是备份数据库和日志权限。

（1）授权语句。在T-SQL中，授予语句级别权限的GRANT语句的简化语法格式如下：
```
grant {all | <statement_permission>[,...n]}
	to<database_principal>[,...n]
```
其中<statement_permission>权限如上所列，其他参数同对象级别授权的GRANT语句。

[例18] 授予用户RosaQdm具有创建表的权限。
```
grant create table to RosaQdm
```
[例19] 授予用户user1和user2都具有创建表和视图的权限。
```
grant create table,create view to user1,user2
```
（2）拒绝权限。
```
deny {all | <statement_permission>[,...n]}
	to<database_principal>[,...n]
```
[例20] 拒绝用户user1具有创建视图的权限。
```
deny create view to user1
```
（3）收权语句。
```
revoke {all | <statement_permission>[,...n]}
	{from | to } <database_principal>[,...n]
```
[例21] 收回用户RosaQdm创建表的权限。
```
revoke create table from RosaQdm
```

9.5.5 角色

在数据库中，为了便于对用户及权限进行管理，可以将一组具有相同权限的用户组织在一起，这一组具有相同权限的用户就称为角色（role）。

在SQL Server2008中，角色分为预定义的系统角色和用户定义角色两种。同时，根据角色作用范围的不同，系统角色又分为服务器角色（称为固定服务器角色）和数据库级角色（称为固定数据库角色）。用户定义的角色均是数据库级角色。

固定服务器角色

固定服务器角色的作用域属于服务器范围，这些角色具有完成特定服务器管理活动的权限。用户不能添加、删除或更改固定服务器角色。

SQL Server2008支持的固定服务器角色及其所具有的权限。

固定服务器角色	描述
bulkadmin	具有执行BULK INSERT语句的权限。
dbcreator	具有创建、修改、删除和还原数据库的权限。
diskadmin	具有管理磁盘文件（数据被指派给哪个文件组、附加和分离数据库等）的权限。
processadmin	具有管理运行在SQl Server中进程的权限，这个角色可以根据需要终止长时间运行的进程。
securityadmin	对于专门管理登录账户、读取错误日志以及执行CREATE DATABASE权限的账户来说，这个角色是非常便捷的。
serveradmin	该角色具有设置服务器级别的配置选项和关闭服务器的权限。
setupadmin	该角色的权限仅局限于添加和删除链接服务器。
sysadmin	系统管理员角色

除了以上列出的固定服务器角色外，还有一个系统预定义服务器角色public。每个SQL Server登录名都是public服务器角色中的成员。

（1）为固定服务器角色添加成员。在固定服务器中添加成员使用的是sp_addsrvrolemember系统存储过程。该存储过程的语法格式如下：

sp_addsrvrolemember [@loginame=] 'login',
	[@rolename=] 'role'

其中各参数为：

[@loginame=] ‘login’ ：要添加到固定服务器角色中的登录名。login可以是SQL Server身份验证的登录名，也可以是Windows身份验证的登陆名。
[@rolename=] ‘role’ : 要添加到固定服务器角色的名称，默认值为NULL。
该存储过程的返回值为：0（成功）或1（失败）。

[例22] 将Windows身份验证的TEST\Win_User1登录名添加到sysadmin角色中。

exec sp_addsrvrolemember 'TEST\Win_User1','sysadmin'

[例23] 将SQL身份验证的SQL_User2登录名添加到dbcreator角色中。

exec sp_addsrvrolemember 'SQL_User2','dbcreator'

（2）删除固定服务器角色成员。从固定服务器角色中删除成员使用的是sp_dropsrvrolemember系统存储过程。该存储过程的语法格式如下：

sp_dropsrvrolemember [@loginame=] 'login',
	[@rolename=] 'role'

其中各参数为：

[@loginame=] ‘login’ ：要从固定服务器角色中删除的登录名。
[@rolename=] ‘role’ ：固定服务器角色名称。
该存储过程的返回值为：0（成功）或1（失败）。

[例24] 从dbcreator角色中删除SQL_User2。

exec sp_dropsrvrolemember 'SQL_User2','dbcreator'

固定数据库角色

用户不能添加、删除或更改固定数据库角色，但可以将数据库用户添加到固定数据库角色中，使其成为数据库角色中的成员，从而具有数据库角色的权限。

以下列出SQL Server2008支持的固定数据库角色及其具有的权限。

固定服务器角色	描述
db_accessadmin	具有添加或删除数据库用户的权限。
db_backupoperator	具有备份数据库、备份日志的权限。
db_datareader	具有查询数据库中所有用户数据的权限。
db_datawriter	具有插入、删除和更改数据库中所有用户数据的权限。
db_ddladmin	具有执行数据定义语言（DDL）的权限。
db_denydatareader	不允许具有查询数据库中所有用户数据的权限，等同于对所有的表和视图授予了DENY SELECT权限。
db_denydatawriter	不允许具有INSERT、DELETE和UPDATE数据库中所有用户数据的权限。
db_owner	具有在数据库中进行全部操作的权限，包括配置、维护数据库以及删除数据库。
db_securityadmin	具有管理数据库角色、角色成员以及数据库中的语句和对象的权限。

（1）为固定服务器角色添加成员。

sp_addrolemember [@rolename=] 'role',
	[@membername=] 'security_account'

其中各参数为：

[@rolename=] ‘role’ ：当前数据库中的数据库角色名。
[@membername=] ‘security_account’ ：要添加到角色中的数据库用户名。
该存储过程的返回值为：0（成功）或1（失败）。

（2）删除固定数据库角色成员。

sp_droprolemember [@rolename=] 'role',
	[@membername=] 'security_account'

用户定义的角色

用户定义的角色主要是为了简化用户在使用数据库时的权限管理。

（1）创建用户定义的角色。创建用户自定义角色的T-SQL语句是CREATE ROLE。其语法格式为：
```
create role role_name [AUTHORIZATION owner_name]
```
其中各参数为：
- role_name : 待创建角色的名称。
- AUTHORIZATION owner_name ：将拥有新角色的数据库用户或角色。如果未指定用户，则执行CREATE ROLE的用户将拥有该角色。
[例28] 在当前数据库中创建用户自定义角色CompDept，其拥有者为创建该角色的用户。
```
create role CompDept
```
[例29] 在当前数据库中创建用户自定义角色InfoDept，其拥有者为SQL_User1。
```
create role InfoDept AUTHORIZATION SQL_User1
```
（2）为用户定义的角色授权。与实现对数据库用户授权完全一样。

（3）添加和删除用户定义的角色中的成员。与固定数据库角色添加和删除成员完全一样。

（4）删除用户定义的角色。删除用户定义的角色的T-SQL语句是DROP ROLE。其语法格式如下：
```
drop role role_name
```
**注意：**不能从数据库中删除拥有安全对象的角色。若要删除有成员的角色，必须首先删除角色中的成员。

9.6 Oracle的安全管理

Oracle的安全控制机制可分为数据库级的安全控制、表级、行级、列级的安全控制。数据库级的安全性通过用户身份认证和授予用户相应系统权限来保证，而表级、行级、列级的安全性是通过授予或回收对象特权保证的。

9.6.1 用户与资源管理

Oracle数据库的用户按其操作权限的大小可分为DBA用户和普通用户。

DBA用户：DBA用户是在数据库系统安转时由DNMS自动创建生成的，它们是sys和system用户，拥有全部的系统特权（权限）。
普通用户：普通用户是由DBA用户或具有相应特权的用户创建，并授予系统特权的用户。

（1）建立用户

[例32] 建立一个Oracle数据库用户user1。

create user user1 identified by u66771
default tablespace student
quota 5M on student

命令中的User1是新建的用户名，密码是u66771；default子句指定新用户创建的数据库对象，如表、视图等将被存储在student表空间中；quota子句限制user1使用student表空间中的空间限额为5MB。

（2）管理用户和资源。具有DBA特权的用户可以改变一个用户对系统资源的使用限额，并对用户密码进行管理，如设置用户密码的有效期，指定一个用户多长时间必须修改一次密码。设置一个用户连接不成功注册Oracle数据库的次数等。

[例33] 将User1用户对student表空间的空间使用限额增至60MB。

alter user user1
quota 60M on student

（3）删除用户

[例34] 从数据库中删除user1用户及其拥有者的全部数据库对象。

drop user user1 cascade

命令中的cascade选项指定在删除用户的同时，删除其所拥有的全部数据库对象，否则仅删除user1用户名。

9.6.2 权限管理

系统特权

为了兼容以前的版本，Oracle提供了三种默认的角色（一组特权），分别为：
- Connect : 具有这种角色的用户不能在数据库中建立任何对象，但可以查询数据字典及访问有权限的数据库对象。
- Resource : 具有这种角色的用户可以在数据库中建立关系表、视图、序列、索引、聚集、存储过程、存储函数、触发器、数据库链路。
- DBA ：这种角色拥有Oracle预定义的所有系统特权。
[例35] 授予用户User1具有建立表和视图的权限。
```
grant create table,create view to uaer1 with admin option
```
对象特权

对象特权用于维护表级、行级与列级数据的安全性。

操作权限|数据库对象表视图序列快照存储过程与函数
alter √ √
delete √ √
execute √
index √
insert √ √
references √
select √ √ √ √
update √ √
（1）授予对象权限。

[例41] 将dep表上的全部操作权授予user1用户。
```
grant all on dep to User1
```
[例42] 仅允许user2具有查询teacher表中tno、tname和sal列的权限。
```
grant select (tno,tname,sal) on teacher to user2
```
（2）回收对象特权
```
revoke all on dep from user1
```

10.数据库运行维护与优化

10.1 数据库运行维护基本工作

一般来说，维护工作主要包括：数据库的转储和恢复；数据库的安全性和完整性控制；数据库性能的监控分析和改进；数据库的重组和重构。

数据库的转储和恢复
数据库的安全性、完整性控制
检测并改善数据库的性能
数据库的重组合重构

10.2 运行状态监控与分析

根据监控分析实现方法的不同，监控分析机制分为两种，一种是由数据库系统建立的自动监控机制，另一种是由管理员手动实施的监控机制。

①自动监控机制

②手动监控机制

根据监控对象的不同，监控分析又可以分为对数据库架构体系的监控和对数据库性能的监控。

③对数据库架构体系的监控

④对数据库性能的监控

10.3 数据库存储空间管理

数据库的存储结构一般分为逻辑结构和物理存储结构，其物理存储结构决定了数据库存储数据时数据文件所占空间的大小及分布。

10.4 数据库性能优化

数据库性能优化一般可以从数据库运行环境、数据库参数调整、模式调整、数据库存储优化及查询优化几个方面考虑。

10.4.1 数据库运行环境与参数调整

数据库系统运行需要依赖服务器、操作系统、存储、网络等多种因素。一般来说，在数据库发现性能问题时，首先需要对数据库运行环境进行检查。可以从外部环境、调整内存分配、调整磁盘I/O、调整资源竞争等几个方面着手来改变数据库的参数，提高其性能。

外部调整

外部条件包括：

①CPU

②网络
调整内存分配
调整磁盘I/O
调整竞争

资源是有限的，多个进程对相同的资源发出申请时，导致了竞争的产生。为了解决这个问题，可以采用以下几种方法：

①修改参数以控制连接到数据库的最大进程数。

②减少调度进程的竞争。

③减少多线程服务进程竞争。

④减少重做日志缓冲区竞争。

⑤减少回滚段竞争。

10.4.2 模式调整与优化

在数据库设计阶段，主要强调的是高效率利用存储空间，减少数据的冗余，减少数据的不一致，这个过程也就是规范化的过程。

反规范化是将规范化的关系转换为非规范化的关系的过程。常用的反规范化方法有增加派生冗余列、增加冗余列、重新组表、分割表和新增汇总表等方法。

增加派生性冗余列

派生性冗余列是指表中增加的列由表中的一些数据项经过计算生成。它的作用是在查询时减少连接操作，避免使用聚合函数。
增加冗余列

增加冗余列是指在多个表中增加具有相同语义的列，它常用来在查询时避免连接操作。
重新组表

当用户经常查看的某些数据是由多个表连接之后才能得到，就可以考虑先把这些数据重新组成一个表，这样在查询时会减少连接从而提高性能。
分割表

表分割有两种方式：水平分割和垂直分割。

水平分割是根据数据行的使用特点进行分割，分割之后所得的所有表的结构都相同，而存储的数据不同。水平分割会给应用增加复杂度，特别在查询所有数据时需要Union（并）操作。

垂直分割是根据数据列的特点进行分割，分割之后所得的所有表中除了都含有主码列外其余列都不相同。缺点是查询所有数据时需要Join（连接）操作。
新增汇总表

数据库的反规划化是为了减少表间的连接，提高查询性能。

10.4.3 存储优化

物化视图

物化视图是包括一个查询结果的数据库对象（可由系统定期刷新其中的数据），物化视图不是在使用时才获取，而是预先计算并保存表连接或聚集等耗时较多的操作结果，这样在查询时大大提高了读取速度。

物化视图还可以进行远程数据的本地复制，此时物化视图的存储也可以称为快照。主要用于实施数据库间的同步。（物化视图的刷新可以采用自动刷新和人工刷新两种方式）。
聚集

聚集（Cluster）是物理存储表中数据的可选择的方法。一个聚集是一组表，可将经常一起使用的具有同一公共列值的多个表中的数据行进行存储在一起，由他们的公共列构成聚集码。如果经常被同时访问，就可以创建一个聚集，通过聚集码存储或查询这些表中的数据，从而最小化必须执行的I/O次数。改善系统性能。

10.4.4 查询优化

优化查询的常用方法：

合理使用索引

索引的物理设计决策涉及一下几类问题：

①是否为一个属性建立索引。

②在哪些属性上建立索引。

③是否建立聚簇索引。一个表中最多只能有一个主索引或聚簇索引。

④使用散列索引还是树索引。通常，关系数据库管理系统中使用B＋树作为索引，某些系统也提供散列索引。B+数索引支持作为搜索码的属性上的等值查询和范围查询，散列索引适用于等值查询，尤其是在连接操作中查找匹配记录时。

索引的使用要恰到好处，其使用原则如下：
- 经常在查询中作为条件被使用的列，应为其建立索引。
- 频繁进行排序或分组（即进行group by 或order by操作）的列，应为其建立索引。
- 一个列的值域很大时，应为其建立索引。
- 如果待排序的列有多个，应在这些列上建立复合索引。
- 可以使用系统工具来检查索引的完整性，必要时进行修复。
避免或简化排序
消除对大型表数据的顺序存取
避免复杂的正则表达式
使用临时表加速查询
用排序来取代非顺序磁盘存取
不充分的连接条件
存储过程
不要随意使用游标
事务处理

10.4.5 SQL Server性能工具

SQL Server Profiler
数据库引擎优化顾问

11.故障管理

11.1 故障管理概述

11.1.1 故障类型及其解决方法

在数据库系统中大致存在四类故障，即事务内部的故障、系统故障、介质故障以及计算机病毒故障。

事务内部的故障

事务内部故障分为预期的和非预期的，其中大部分是非预期的。

（1）预期的事务内部故障。预期的事务内部故障是指可以通过事务程序本身发现的事物内部故障。

（2）非预期的事务内部故障。非预期的事务内部故障是不能由事务程序处理的，如运算溢出故障、并发事务死锁故障、违反了某些完整性限制而导致的故障等。
系统故障

系统故障又称软故障，是指数据库在运行过程中，由于硬件故障、数据库软件及操作系统的漏洞、突然停电等情况，导致系统停止运转，所有正在运行的事务以非正常方式终止，需要系统重新启动的一类故障。这类故障影响正在运行的所有事务。

当数据库发生系统故障时，容错对策是在重新启动系统后，撤销（UNDO）所有未提交的事务，重做（REDO）所有已提交的事务，以达到容错的目的。
介质容错

介质故障又称硬故障，主要指数据库在运行过程中，由于磁头碰撞、磁盘损坏，强磁干扰、天灾人祸等情况，使得数据库中的数据部分或全部丢失的一类故障。

这类故障可能导致物理存储设备损坏，使数据库文件及数据全部丢失，所以，虽然它比前几类故障发生的肯能性小，但破坏性最大。
计算机病毒故障

11.1.2 数据库恢复技术概述

数据库系统必须具备以下功能，即在故障发生时，能够利用存储在系统其他地方的冗余数据来重建数据库中被破坏的或不正确的数据，把数据库从错误状态恢复到某一已知的正确状态，从而重新建立一个完整的数据库，这就是数据库恢复。

恢复机制涉及的两个关键问题是：第一，如何建立冗余数据；第二，如何利用这些冗余数据实施数据库恢复。

建立冗余数据的技术很多，例如为数据备份、登记日志文件、数据库复制、数据库镜像、为段设立保存点以及使用后备段与现行页表支持对段的保存等。

11.2 数据转储

所谓数据转储，就是指数据库管理员（DBA）或数据库管理系统定期复制数据库，并将复制得到的数据存放到其他介质中的过程，数据转储也称数据备份。

静态转储和动态转储

（1）静态转储。在静态转储过程中系统不能运行其他事务，不允许在转储期间对数据库有任何的存取、修改活动，即转储前后系统必须处于一个一致性的状态。

（2）动态转储。动态转储是指允许转储操作和用户事务并发执行，即允许在转储过程中对数据库进行存取和修改操作。

总结：静态转储虽然保证了数据的有效性，但是却是以降低数据库的可用性为代价的；而动态转储虽然提高了数据库的可用性，但数据的有效性却可能得不到保证。
数据转储机制

（1）完全转储。完全转储是对数据库中所有数据进行转储。这种转储方式需占用较多的时间和空间，但在系统失败时恢复的时间较短。

（2)增量转储。增量转储只复制上次转储后发生变化的文件或数据块。增量转储所需的时间和空间都比较短，但增量转储数据只能和完全转储配合，才能对数据库进行恢复，增量转储的恢复时间要比仅使用完全转储要长。

（3）差量转储。差量转储是对最近一次数据库完全转储以来发生的数据变化进行转储，差量转储也被称为差异转储。差量转储和完全转储相比速度快，占用较小的空间；差量转储和增量转储相比，速度慢、占用空间较多，但恢复速度比增量转储快。
多种转储方法结合使用

（1）仅使用完全转储。仅使用完全转储会产生大量数据移动，占用时间和空间较多，对数据性能可能产生较大的影响，这种方法代价比较大。

（2）完全转储加增量转储。

（3）完全转储加差量转储。

11.3 日志文件

11.3.1 日志文件的概念

日志文件记录每个事务对数据库的修改操作。数据库系统在运行过程中，将所有事务的修改操作登记到日志文件中。

日志文件的具体作用：

事务故障恢复和系统故障恢复必须使用日志文件

（1）故障恢复的两个基本操作。利用日志文件进行故障恢复时有两个基本操作：UNDO和REDO。

（2）事务故障恢复。

（3）系统故障恢复。
在动态转储方式中必须建立日志文件
在静态转储方式中，也可以使用日志文件

11.3.2 日志文件的格式与内容

不同的数据库系统采用的日志文件格式并不完全一样，但概括起来日志文件主要有两种格式：以记录为单位的日志文件和以数据块为单位的日志文件。

以记录为单位的日志文件

以记录为单位的日志文件内容包括每个事务的开始标记、每个事务的结束标记，以及每个事务的所有修改操作。
以数据块为单位的日志文件

11.3.3 登记日志文件的原则

为保证数据库是可恢复的，登记日志文件必须遵循两条原则：

（1）登记的次序严格按并行事务执行的时间次序。

（2）必须先写日志文件，后写数据库。

11.3.4 检查点

检查点的作用

检查点最大限度地减少了数据库完全恢复时所必须执行的日志部分。
检查点的引入

检查点记录的内容包括：

①建立检查点时刻所有正在执行的事务清单。

②这些事务最近一个日志记录的地址。
基于检查点的恢复步骤

11.4 硬件容错方案

11.4.1 概述

硬件容错的方案需要从数据库系统运行所需要的各种环境出发，分析支撑数据库系统运行的环节，例如机房的电力、机房空调环境、网络、存储、服务器、综合考虑，否则某一个环节出现故障都可能导致数据库系统不可运行。

11.4.2 磁盘保护技术

所谓RAID就是廉价冗余磁盘阵列，它是由多块磁盘构成的一个整体。

RAID的冗余技术主要有两个：

（1）镜像冗余

（2）效验冗余

RAID的级别主要有：RAID0、RAID1、RAID5、RAID10。

11.4.3 服务器容错技术

引入服务器容错原因

服务器容错技术就是为了解决服务器硬件异常问题而出现的解决方案。
服务器容错技术简介

服务器容错技术一般采用两台相同的服务器，两台服务器共享存储设备，其中一台服务器运行数据库系统，数据库数据存储在存储设备中。两台服务器之间使用专用的网络进行检测，当运行服务器出现问题时，由备用服务器接管数据库。在备用服务器接管数据库业务时，数据库数据保持一致状态。此种工作模式一般称为Active-Standby模式。
服务器接管过程

在Active-Standby工作模式中，公有网络为客户端和数据库服务器连接的网路，私有网络为两台数据库服务器之间检测状态的网络。

Active-Standby工作模式是最常用的服务器容错技术。
其他服务器容错技术简介

主要涉及硬件级别和软件级别。

11.4.4 数据库镜像与数据库容灾

引入数据库镜像的原因

为了避免介质故障对数据库可用性的影响，许多数据库管理系统都提供了数据库镜像的功能。
数据库镜像简介

数据库镜像是一种用于提高数据库可行性的解决方案。

数据库镜像的优点：

①数据库镜像提供完整或接近完整的数据冗余，增强数据保护功能。

②发生灾难时，数据库镜像可快速使数据库的备用副本提供服务，使数据不会丢失，提高数据库得到可用性。

③提高镜像数据库在升级期间的可用性。
数据库镜像分类

数据库镜像的基本架构共分为两种模式：双机互备援模式和双机热备份模式。

（1）双机互备援模式。所谓双机互备援就是两台主机均为工作机，在正常情况下，两台工作机均为信息系统提供支持，并互相监视对方的运行情况。

（2）双机热备份模式。所谓双机热备份就是一台主机为工作机，另一台主机为备份机。
工作模式

数据库正常运行时，数据库镜像可以用于并发操作。
SQL Server数据库镜像简介

SQL Server数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。

12.备份与恢复数据库

故障的原因有多种多样，包括磁盘故障、电源故障、软件故障、灾害故障、人为破坏等。

12.1 备份与恢复的概念、

备份数据库就是将数据库中的数据以及保证数据库系统正常运行的有关信息保存起来，以备系统出现问题时恢复数据库的使用。

12.1.1 备份数据库

备份是制作数据库的副本，包括数据库结构、对象和数据。备份数据库的主要目的是为了防止数据丢失。造成数据丢失的原因主要包括以下几种情况：

（1）存储介质故障。

（2）用户的操作错误。

（3）服务器故障。

（4）由于病毒的侵害而造成的数据丢失或损坏。

（5）由于自然灾害而造成的数据丢失或损坏。

备份数据库的另一个作用是进行数据转移，可以先对一台服务器上的数据库进行备份，然后在另一台服务器上进行恢复，从而使这两台服务器上具有相同的数据库。

12.1.2 恢复数据库

恢复数据库是指数据库系统在出现故障时利用先前建立的冗余数据（备份副本）把数据库恢复到某个正确、一致的状态的过程。根据故障对数据库系统的影响和故障的可修复程度，数据库系统的故障恢复可归为下列两种类型：

（1）介质故障恢复。介质故障也称为灾难性故障，这类故障的主要方法是还原最近的一个数据库副本，并利用备份日志重做（REDO）已提交事务的操作，使数据库还原到故障前的某个工作状态。

（2）非介质故障恢复。非介质故障也称为可修复性故障。非介质故障一般不需要数据库的后备副本，其恢复工作是由数据库管理系统自动完成的。

12.2 SQL Server的备份与恢复机制

12.2.1 恢复模式

简单恢复模式

简单恢复模式可最大限度地减少事务日志的管理开销，因为这种恢复模式不备份事务日志。对于用户数据库，简单恢复模式只用于测试和开发数据库，或用于主要包含只读数据的数据库（如数据仓库），这种模式不适合生产模式。
完整恢复模式

完整恢复模式完整地记录所有的事务，并将事务日志记录保留到对其备份完毕为止。
大容量日志恢复模式

大容量日志恢复模式只对大容量操作进行最小记录，使事务日志不会被大容量加载操作所填充。大容量日志恢复模式保护大容量操作不受媒体故障的危害，提供最佳性能并占用最小日志空间。
查看和更改恢复模式

使用ALTER DATABASE语句更改和设置数据库的恢复模式，其语法格式为：
```
alter database database_name set
	recovery {full|bulk_logged|simple}
```
其中，“FULL”为完整恢复模式，“bulk_logged"为大容量日志恢复模式，"simple"为简单恢复模式。

[例1] 将test数据库的恢复模式设置为完整恢复模式。
```
alter database test set recovery full
```

12.2.2 备份内容及时间

备份内容

在备份数据库时，不但要备份用户数据，同时还要备份系统数据，以保证在系统出现故障时，能够完全地恢复数据库。
备份时间

不同类型的数据库对备份的要求是不同的，对于SQL Server的系统数据库（不包括tempdb数据），一般是在进行了修稿之后立即做备份比较合适。对用户数据库则不能采用立即备份的方式。

12.2.3 SQL Server的备份机制

备份设备

备份设备即备份数据库的文件，这些文件可以建立在磁带上，也可以建立在磁盘上。SQl Server支持两种备份方式，一种是先建立备份设备，然后再将数据库备份到设备上，称这样的备份设备为永久备份设备；另一种是直接将数据库备份到物理文件上，称这样的备份设备为临时备份设备。

创建备份设备的T-SQL语句是sp_addumpdevice系统存储过程，其语法格式为：
```
sp_addumpdevice [@devtype=] 'device_type'
	'[@logicalname=]'logical_name'
	'[@physicalname=]'physical_name'
```
其中各参数含义：
- [@devtype=] ‘device_type’ ：备份设备的类型。dectype_type可以是下列值之一：
  - Disk : 备份设备为磁盘上的文件。
  - Type ：备份设备为Windows支持的任何磁盘设备。
- [@logicalname=]'logical_name ：在BACKUP（备份）和RESTORE（恢复）语句中使用的备份设备的逻辑名称。
- [@physicalname=]'physical_name ：备份设备的物理文件名。物理文件名必须遵从操作系统文件名规则或网络设备的通用命名规则，并且必须包含完整路径。

备份类型

（1）数据库备份。对于数据库备份，SQL Server支持完整数据库备份和差异数据库备份两种类型。

①完整数据库备份

完整数据库备份（也称为完全转储）将备份特定数据库中的所有数据，以及可以恢复这些数据的足够的日志。

②差异数据库备份

差异数据库备份（也称为差异转储）是备份从最近的完整备份之后数据库的全部变化内容。

在使用差异数据库备份时，建议遵循以下原则：

在每次完整数据库备份后，定期安排差异数据库备份。
在确保差异备份不会太大的情况下，定期安排新的完整数据库备份。

（2）文件备份

SQL Server支持文件备份和差异文件备份两种方式。

文件备份。一个或多个文件或文件组中所有数据的完整备份。
差异文件备份。差异文件备份仅用于完整备份之后发生了更改的数据区。

（3）事务日志备份。事务日志备份仅用于完整恢复模式和大容量日志恢复模式。

在SQL Server2008中，有三种类型的事务日志备份：

日志备份类型	说明
纯日志备份	仅包含一定间隔的事务日志记录，不包含在大容量日志恢复模式下执行的任何大容量更改的备份。
大容量操作日志备份	包含日志记录及由大容量操作更改的数据页的备份。不允许对大容量操作日志备份进行时点恢复。
结尾日志备份	对可能已损坏的数据库进行的日志部分，用于捕获尚未备份的日志记录。

常用备份策略

（1）完整数据库备份。完整数据库备份策略适合数据库数据不是很大，而且数据更改不是很频繁的情况。完整备份一般可以几天进行一次或几周进行一次。

（2）完整数据库备份加日志备份。

（3）完整数据库备份加差异数据库备份再加日志备份。
实现备份

备份数据库使用的是BACKUP语句。

12.2.4 SQL Server的恢复机制

SQL Server支持在以下级别恢复（还原）数据：

数据库（数据库完整还原）。还原和恢复整个数据库，并且数据库在还原和恢复操作期间处于脱机状态。
数据文件（文件还原）。还原和恢复一个数据文件或一组文件。在文件还原过程中，包含相应文件的文件组在还原过程中自动变为脱机状态。

还原的顺序

①还原最新完整数据库备份而不恢复数据库。

②如果存在差异备份，则还原最新的差异备份而不恢复数据库。

③从最后一次还原备份后创建的第一个事务日志备份开始，使用NORECOVERY选项依次还原日志。

④恢复数据库。
实现还原

还原数据库和事务日志的T-SQL语句分别是RESTORE DATABASE 和RESTORE LOG。

12.3 Oracle的备份与恢复机制

Oracle数据库将数据库系统常见的故障类型归纳为：SQL语句错误、进程失效、实例失效、网络失效、用户错误、介质失效。

Oracle数据库的备份方式主要分为逻辑备份和物理备份。

13.大规模数据库架构

13.1 分布式数据库

13.1.1 分布式数据库系统概述

分布式数据库系统是物理上分散、逻辑上集中的数据库系统。系统中的数据分布在物理位置不同的计算机上（通常称为场地、站点或结点），由通信网络将这些场地连接起来，每个场地既具有独立处理的能力，也可以和其他场地协同工作。

13.1.2 分布式数据库目标与数据分布策略

分布式数据库目标

分布式数据库要达到的12个目标：
- 本地自治
- 非集中式管理
- 高可用性
- 位置独立性
- 数据分片独立性
- 数据复制独立性
- 分布式查询处理
- 分布式事务管理
- 硬件独立性
- 操作系统独立性
- 网络独立性
- 数据库管理系统独立性
本地自治、非集中式管理以及高可用性是分布式数据库最基本的特征；位置独立性、数据分片独立性和数据复制独立性形成了分布式数据库系统中的分布透明性。
数据分布策略

分布式数据库中数据分布策略可以从数据分片和数据分配两个角度来考虑，一般先数据分片，再数据分配。

（1）数据分片。对某一个关系进行分片是将关系划分为多个片段，这些片段中包含足够的信息可以使关系重构。数据分片有四种基本方法：
- 水平分片
- 垂直分片
- 导出分片
- 混合分片
（2）数据分配
- 集中式：所有数据片段都安排在一个场地上。
- 分割式：所有全局数据有且只有一份，它们被分割成若干片段，每个片段被分配在一个待定场地上。
- 全复制式：全局数据有多个副本，每个场地上都有一个完整的数据副本。
- 混合式：全局数据被分为若干个数据子集，每个子集被安排在一个或多个不同的场地上。

13.1.3 分布式数据库系统的体系结构

分布式数据库的参考模式结构
- 全局外模式
- 全局概念模式
- 分片模式
- 分配模式
- 局部概念模式
- 局部内模式
分片透明性

分布透明性有如下几种级别：

（1）分片透明性。分片透明性是最高级别的透明性，位于全局概念模式与分片模式之间。

（2）位置透明性。位置透明性是指数据库分片的分配位置对用户是透明的，用户编写程序时只需考虑数据分片情况，不需要了解各分片在各个场地的分配情况。

（3）局部数据模型透明性。局部数据模型透明性处于分配模式与局部概念模式之间。
分布式数据库管理系统

分布式数据库管理系统是用于支持分布式数据库的创建、运行、管理和维护的一种数据库管理软件，它能够对各个场地的软硬件资源进行管理，为用户提供数据接口。

13.1.4 分布式数据库的相关技术

分布式查询

在集中式数据库系统中，查询代价主要是由CPU代价和I/O代价来衡量的，在分布式数据库系统中，由于数据分布在多个不同的场地上，使得查询处理中还要考虑站点间传输数据的通信代价。

一般来说，分布式查询优化主要考虑以下策略：

①操作执行的顺序

②操作的执行算法（主要是连接操作和并操作）

③不同场地之间数据流动的顺序

在分布式数据库的查询中，导致数据传输量大的主要原因是数据间的连接操作集和并操作，因此有必要这两种操作进行优化。目前，针对连接操作广泛使用的优化策略有两种：基于半连接的优化策略和基于连接的优化策略。
分布式事务管理

分布式事务管理主要包括恢复控制和并发控制。分布式数据库系统的恢复控制采用的最典型策略是基于两阶段的提交协议。

对并发控制而言，在大多数分布式系统中并发控制主要是基于封锁协议的。

13.2 并行数据库

13.2.1 并行数据库概述

13.2.2 并行数据库系统结构

共享内存结构。
共享磁盘结构。
无共享结构。
层次结构。

13.2.3 数据划分与并行算法

一维数据划分

一维数据划分是根据关系的某一个属性的值来划分整个关系，这个属性称之为划分属性。

（1）轮转法

（2）散列划分

（3）范围划分
多维数据划分
并行算法

（1）并行排序

（2）并行连接

（3）其他关系操作

13.3 云计算数据库架构

13.4 云数据库展望

14.数据仓库与数据挖掘

14.1 决策支持系统的发展

14.1.1 决策支持系统及其演化

14.1.2 基于数据仓库的决策支持系统

14.2 数据仓库技术概括

14.2.1 数据仓库的概念与特性

主题与面向主题
数据仓库的其他特征

14.2.2 数据仓库的体系结构与环境

14.2.3 数据仓库的数据组织

数据组织结构
粒度

粒度越大，表示综合程度越高；粒度越小，表示综合程度越低。
数据分区

14.2.4 元数据

在数据仓库中，元数据一般分为技术型元数据和业务型元数据两类。

14.2.5 操作型数据存储

14.3 设计与建造数据仓库

14.3.1 数据仓库设计的需求与方法

数据仓库设计需求
设计方法

14.3.2 数据仓库的数据模型

概念模型
逻辑模型
物理模型

14.3.3 数据仓库设计步骤

14.4 数据仓库的运行与维护

14.4.1 数据仓库数据的更新维护

维护策略
捕捉数据源的变化
导出数据的刷新

14.4.2 数据仓库监控与元数据管理

数据仓库监控
元数据管理

14.5 联机分析处理与多维数据模型

14.5.1 OLAP简介

14.5.2 多维分析的基本概念

14.5.3 多维分析的基本操作

14.5.4 OLAP的实现方式

基于多维数据库的OLAP（MOLAP）
基于关系数据库的OLAP（ROLAP）
混合型的OLAP（HOLAP）

14.6 数据挖掘技术

14.6.1 数据挖掘步骤

数据准备
数据挖掘
结果解释评估

14.6.2 关联规则挖掘

14.6.3 分类挖掘

14.6.4 聚类挖掘

14.6.5 时间序列分析

数据库备份

差异数据库备份（也称为差异转储）是备份从最近的完整备份之后数据库的全部变化内容。

在使用差异数据库备份时，建议遵循以下原则：

在每次完整数据库备份后，定期安排差异数据库备份。
在确保差异备份不会太大的情况下，定期安排新的完整数据库备份。

（2）文件备份

SQL Server支持文件备份和差异文件备份两种方式。

文件备份。一个或多个文件或文件组中所有数据的完整备份。
差异文件备份。差异文件备份仅用于完整备份之后发生了更改的数据区。

（3）事务日志备份。事务日志备份仅用于完整恢复模式和大容量日志恢复模式。

在SQL Server2008中，有三种类型的事务日志备份：

日志备份类型	说明
纯日志备份	仅包含一定间隔的事务日志记录，不包含在大容量日志恢复模式下执行的任何大容量更改的备份。
大容量操作日志备份	包含日志记录及由大容量操作更改的数据页的备份。不允许对大容量操作日志备份进行时点恢复。
结尾日志备份	对可能已损坏的数据库进行的日志部分，用于捕获尚未备份的日志记录。

常用备份策略

（1）完整数据库备份。完整数据库备份策略适合数据库数据不是很大，而且数据更改不是很频繁的情况。完整备份一般可以几天进行一次或几周进行一次。

（2）完整数据库备份加日志备份。

（3）完整数据库备份加差异数据库备份再加日志备份。
实现备份

备份数据库使用的是BACKUP语句。

12.2.4 SQL Server的恢复机制

SQL Server支持在以下级别恢复（还原）数据：

数据库（数据库完整还原）。还原和恢复整个数据库，并且数据库在还原和恢复操作期间处于脱机状态。
数据文件（文件还原）。还原和恢复一个数据文件或一组文件。在文件还原过程中，包含相应文件的文件组在还原过程中自动变为脱机状态。

还原的顺序

①还原最新完整数据库备份而不恢复数据库。

②如果存在差异备份，则还原最新的差异备份而不恢复数据库。

③从最后一次还原备份后创建的第一个事务日志备份开始，使用NORECOVERY选项依次还原日志。

④恢复数据库。
实现还原

还原数据库和事务日志的T-SQL语句分别是RESTORE DATABASE 和RESTORE LOG。

12.3 Oracle的备份与恢复机制

Oracle数据库将数据库系统常见的故障类型归纳为：SQL语句错误、进程失效、实例失效、网络失效、用户错误、介质失效。

Oracle数据库的备份方式主要分为逻辑备份和物理备份。

13.大规模数据库架构

13.1 分布式数据库

13.1.1 分布式数据库系统概述

13.1.2 分布式数据库目标与数据分布策略

分布式数据库目标

分布式数据库要达到的12个目标：
- 本地自治
- 非集中式管理
- 高可用性
- 位置独立性
- 数据分片独立性
- 数据复制独立性
- 分布式查询处理
- 分布式事务管理
- 硬件独立性
- 操作系统独立性
- 网络独立性
- 数据库管理系统独立性
本地自治、非集中式管理以及高可用性是分布式数据库最基本的特征；位置独立性、数据分片独立性和数据复制独立性形成了分布式数据库系统中的分布透明性。
数据分布策略

分布式数据库中数据分布策略可以从数据分片和数据分配两个角度来考虑，一般先数据分片，再数据分配。

（1）数据分片。对某一个关系进行分片是将关系划分为多个片段，这些片段中包含足够的信息可以使关系重构。数据分片有四种基本方法：
- 水平分片
- 垂直分片
- 导出分片
- 混合分片
（2）数据分配
- 集中式：所有数据片段都安排在一个场地上。
- 分割式：所有全局数据有且只有一份，它们被分割成若干片段，每个片段被分配在一个待定场地上。
- 全复制式：全局数据有多个副本，每个场地上都有一个完整的数据副本。
- 混合式：全局数据被分为若干个数据子集，每个子集被安排在一个或多个不同的场地上。

13.1.3 分布式数据库系统的体系结构

分布式数据库的参考模式结构
- 全局外模式
- 全局概念模式
- 分片模式
- 分配模式
- 局部概念模式
- 局部内模式
分片透明性

分布透明性有如下几种级别：

（1）分片透明性。分片透明性是最高级别的透明性，位于全局概念模式与分片模式之间。

（2）位置透明性。位置透明性是指数据库分片的分配位置对用户是透明的，用户编写程序时只需考虑数据分片情况，不需要了解各分片在各个场地的分配情况。

（3）局部数据模型透明性。局部数据模型透明性处于分配模式与局部概念模式之间。
分布式数据库管理系统

分布式数据库管理系统是用于支持分布式数据库的创建、运行、管理和维护的一种数据库管理软件，它能够对各个场地的软硬件资源进行管理，为用户提供数据接口。

13.1.4 分布式数据库的相关技术

分布式查询

在集中式数据库系统中，查询代价主要是由CPU代价和I/O代价来衡量的，在分布式数据库系统中，由于数据分布在多个不同的场地上，使得查询处理中还要考虑站点间传输数据的通信代价。

一般来说，分布式查询优化主要考虑以下策略：

①操作执行的顺序

②操作的执行算法（主要是连接操作和并操作）

③不同场地之间数据流动的顺序

在分布式数据库的查询中，导致数据传输量大的主要原因是数据间的连接操作集和并操作，因此有必要这两种操作进行优化。目前，针对连接操作广泛使用的优化策略有两种：基于半连接的优化策略和基于连接的优化策略。
分布式事务管理

分布式事务管理主要包括恢复控制和并发控制。分布式数据库系统的恢复控制采用的最典型策略是基于两阶段的提交协议。

对并发控制而言，在大多数分布式系统中并发控制主要是基于封锁协议的。