数据库知识点点

作者: Onion99 | 来源:发表于2019-05-25 14:00 被阅读0次

[TOC]

数据库复习

数据库应用系统开发方法

数据库基本概念

数据

定义：描述事物的符号序列

计算机中数据分为两部分：

临时性数据
持久性数据

数据有型（Type）与值（Value）之分

数据库（DB）

数据的集合，具有统一的结构形式并存放于统一的存储介质内，是多种应用数据的集成，并可被各个应用程序所共享

数据库系统（DBS）

组成：

* 数据库（数据）
　　* 数据库管理系统（软件）
　　* 数据库管理员（人员）
　　* 硬件平台：计算机和网络
　　* 软件平台：操作系统、数据库系统开发工具、接口软件

数据库应用系统（DBAS）

组成：数据库系统+应用软件+应用界面

1552808373218.png

软件工程

由方法、工具和过程三部分组成——软件工程的三要素

软件生存周期

定义开始，经过开发、使用与维护，直到最后退役

软件工程与数据库技术

数据库工程

内容

数据库设计
相应的应用的设计与实现

典型的软件开发模型

瀑布模型、快速原型模型、螺旋模型等

DBAS生命周期模型

组成

项目规划

系统规划与定义
- 任务陈述、确定任务目标、确定范围和边界、确定用户视图
可行性分析
- 技术、经济、操作可行性及开发方案选择
项目规划
- 项目团队、环境、活动，成本预算，进度计划

需求分析

组成
- 由需求获取、需求分析、需求描述与规范说明、需求验证等步骤
各种需求的主要工作
- 数据需求分析
- 功能需求分析
- 性能需求分析
- 其他需求

系统设计

概念设计
- 数据库概念模型设计、系统总体设计
逻辑设计
- 数据库逻辑结构设计、应用程序概要设计、数据库事务概要设计
物理设计
- 数据库物理结构设计、数据库事务详细设计、应用程序详细设计

实现与部署

建立数据库结构，数据加载，事务和应用程序的编码及测试，系统集成、测试与运行，系统部署

运行管理与维护

日常维护、系统监控与分析、系统性能优化调整、系统进化升级等

三条设计主线

数据组织与存储设计

设计数据库

数据访问与处理设计

数据库事务

应用设计

应用程序

设计阶段

概念设计

逻辑设计

物理设计

1552809214219.png

需求分析

需求

用户对软件的功能和性能的要求

需求分析

描述待开发的系统所要完成的功能

目标是深入描述软件的功能和性能，确定软件设计的约束和软件同其它系统元素的接口细节，定义软件的其它有效性需求

影响因素

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

通常，一个计算机应用系统的需求分析工作是在系统分析人员与用户不断交互的过程中完成的

需求获取的方法

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

需求分析方法

结构化分析与建模方法（SAD）

DFD建模
IDEF建模

面向对象分析与建模方法(OOAD)

UML用例建模

结构化分析任务

建立分析模型
- 功能模型
- 数据模型
- 行为模型
编写需求规格说明书
- 引言、信息描述、功能描述、行为描述、质量保证、接口描述以及其他需求等
结构化分析的指导思想
- 抽象
- 分解

数据流图(DFD)

建模方法的核心是数据流

1552810530967.png

数据流

数据在系统内传播的路径

由于数据流是流动中的数据，所以必须有流向，除了与数据存储之间的数据流不用命名外，数据流应该用名词或名词短语命名

数据源

代表系统之外的实体

对数据的加工

加工是对数据进行处理的单元

数据存储

表示信息的静态存储

DFD建模过程

明确目标，确定系统范围
建立顶层DFD图
- 顶层DFD只有一张
构建第一层DFD分解图
开发DFD层次结构图
检查确认DFD图
- 父图中描述过的数据流必须在相应子图出现。
- 一个处理至少有一个输入流和输出流。
- 一个存储必定有流入的数据流和流出的数据流。
- 一个数据流至少有一端是处理框。
- 表达描述的信息是全面、完整、正确和一致的

IDEF方法

IDEF0:描述系统功能及其相互关系
- 输入（Input）箭头表示完成特定活动所需的数据，置于矩形框的左侧
- 输出（Output）箭头说明由活动产生的结果及信息，置于矩形框的右侧
- 控制（Control）箭头描述了影响这个活动执行的事件或约束条件，置于矩形框的上方
- 机制（Mechanisms）箭头表示实施该活动的物理手段或完成活动需要的资源（计算机系统、人或组织），置于矩形框的下方
IDEF1: 系统信息及其数据之间联系
IDEF1X是数据建模方法
- 所有实体集间的联系都必须用确定联系来描述，不允许出现不确定联系，多对多联系为非确定联系，非确定联系需要分解为若干个一对多的联系
- 实体集（矩形框-独立实体集、圆角矩形框-从属实体集）、联系（标定型联系、非标定型联系、分类联系、非确定联系）。从属实体集可以有自己的主码
IDEF2: 系统模拟，动态建模
IDEF3: 过程描述及获取方法
IDEF4：面向对象设计

UML用例建模方法

系统
角色
用例

数据库结构设计

数据库概念设计

目标

定义和描述应用领域设计的数据范围
获取信息模型
描述数据的属性特征
描述数据之间的关系
定义和描述数据的约束
说明数据的安全性要求
支持用户的各种数据处理需求
保证信息模型能转化成数据库的逻辑结构

概念设计的依据及过程

依据

以需求分析的结果为依据

结果

概念模型（ER）与概念设计说明书

过程

明确建模目标（模型覆盖范围）
定义实体集（自底向上标识和定义实体集）
定义联系（实体间关联关系）
建立信息模型（构造ER模型）
确定实体集属性（属性描述一个实体集的特征或性质)
对信息模型进行集成与优化（检查和消除命名不一致、结构不一致等）

概念设计是DB设计的核心环节。概念数据模型是对现实世界的抽象和模拟

概念模型设计(ER)

实体（Entity）或实例（Instance）

客观存在并可相互区分的事物叫实体

实体集（Entity Set）

同型实体的集合称为实体集

属性（Attribute）

实体所具有的某一特性

每个属性的取值范围称为域

码（Key）

实体集中唯一标识每一个实体的属性或属性组合

联系（Relationship）

描述实体之间的相互关系

联系也可以有属性

类别

一对一联系（1：1）
一对多联系（1：n )
多对多联系（m：n）

表示

1552812658225.png

数据库逻辑设计

任务

将概念模型（如ER图）转化为DBMS支持的数据模型（如关系模型），并对其进行优化

逻辑设计的依据和阶段目标

1552812872602.png

数据库物理设计

物理设计概述

物理数据库设计是设计数据库的存储结构和物理实现方法

目的

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

数据库的物理结构

内容

确定数据的存储结构
设计数据的存取路径
确定数据的存放位置
确定系统配置

难题

文件组织
文件结构
文件存取
索引技术

索引

是数据库中独立的存储结构，其作用是提供一种无须扫描每个页面（存储表格数据的物理块）而快速访问数据页的方案

有序索引

数据文件（主文件）和索引文件（索引记录或索引项的集合）是有序索引技术中的两个主体

聚集索引（索引项与数据记录排列顺序一致，索引顺序文件）和非聚集索引
- 一个数据文件只可建立一个聚集索引，但可建立多个非聚集索引
稠密索引（数据文件中每个查找码都对应索引记录）和稀疏索引（部分查找码的值对应索引记录）
主索引（主码属性集上建立的索引）与辅索引（非主码上建立的索引）
唯一索引（索引列不包含重复值）
单层索引（线性索引，每个索引项顺序排列直接指向数据文件中的数据记录）和多层索引（大数据量文件中的采用多层树型（B,B+树）索引快速定位）

散列索引

利用散列函数实现记录域取值到记录物理地址间的直接映射关系

建立索引原则

一个（组）属性经常在操作条件中出现
一个（组）属性经常在连接操作的连接条件中出现
一个（组）属性经常作为聚集函数的参数

建立聚集索引原则

检索数据时，常以某个（组）属性作为排序、分组条件。
检索数据时，常以某个（组）属性作为检索限制条件，并返回大量数据。
表中某个（组）的值重复性较大

数据库的物理设计

目标

得到存储空间占用少，数据访问效率高和维护代价低的数据库物理模式

环节

数据库逻辑模式描述

文件组织与存取设计

基本原则
- 根据应用情况将易变部分与稳定部分、存取频率较高部分与存取频率较低部分分开存放，以提高系统性能
- 可以考虑将表和索引分别放在不同的磁盘上保证物理读写速度比较快
影响数据文件存储结构的因素
- 存取时间
- 存储空间利用率
- 维护代价
解决办法
- 适当冗余
- 增加聚簇功能
何谓存取路径
- 确定如何建立索引
- 对同一个关系要建立多条存取路径才能满足多用户的多种应用要求
- 物理设计的第一个任务
DBMS常用存取方法
- 索引方法，目前主要是B+树索引方法
- 聚簇（Cluster）方法
- HASH方法

数据分布设计

不同类型数据的物理分布
- 将应用数据（基本表）、索引、日志、数据库备份数据等合理安排在不同介质中
应用数据的划分与分布
- 根据数据的使用特征划分（频繁使用分区和非频繁使用分区）
- 根据时间、地点划分（时间或地点相同的属于同一分区）
- 分布式数据库系统（DDBS）中的数据划分（水平划分或垂直划分）
- 派生属性数据分布（增加派生列或不定义派生属性）
- 关系模式的去规范化（降低规范化提高查询效率）

确定系统配置

物理模式评估

对数据库物理设计结果从存取时间、存储空间、维护代价等方面进行评估，重点是时间和空间效率

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

功能设计过程一般被划分为总体设计、概要设计和详细设计。而具体到数据库事务设计部分，又可分成事务概要设计和事务详细设计

软件体系结构与设计过程

软件体系结构

软件体系结构={构件，连接件，约束}
软件体系结构是软件系统中最本质的东西。良好的体系结构必须是普适、高效和稳定的
软件体系结构有多种风格和类型，如分层体系结构、模型-视图-控制器（MVC）体系结构、客户端/服务器体系结构等

软件设计过程

软件开发由设计、实现、测试三个环节组成，设计又包含概要设计和详细设计
概要设计的任务是进行软件总体结构设计，可采用层次结构图建立软件总体结构图。详细设计的任务是进行数据设计、过程设计及人机界面设计
设计原则：模块化、信息隐藏、抽象与逐步求精。
软件设计可选用结构化设计方法、面向对象设计方法或面向数据设计方法等

DBAS总体设计

任务是确定系统总体框架

DBAS体系结构设计
- 将系统从功能、层次/结构、地理分布等角度进行分解，划分为多个子系统，定义各子系统功能；设计系统的全局控制，明确各子系统间的交互和接口关系
- 两种常见的DBAS体系结构
  - 客户/服务器体系结构（C/S）
  - 浏览器/服务器体系结构（B/S）
软件体系结构设计
软件硬件选型与配置设计
- 总体设计阶段需要对软硬件设备做出合理选择，并进行初步配置设计
- 软硬件选型涉及的内容
  - 网络及设备选型；数据存储及备份方案;服务器选型；
  - 终端软件环境；软件开发平台和语言、工具；
  - 系统中间件及第三方软件选型
业务规则初步设计
- DBAS的各项业务活动具有逻辑上的先后关系，可将它们表示成一个操作序列，并用业务流程图表

DBAS功能概要设计

在总体设计结果基础上，将DBAS应用软件进一步细化为模块/子模块，组成应用软件的系统-子系统-模块-子模块层次结构，并从结构、行为、数据三方面进行设计

四个层次实现

表示层
- 人机界面设计
- 设计原则
  - ‘用户自主控制’；反馈及时上下文感知；容错与错误恢复；界面标准常规；输入灵活；界面简洁交互及时
业务逻辑层
- 设计原则
  - 高内聚低（松）耦合，即构件单一原则；构件独立功能；接口简单明确；构件间关系简单，过于复杂，就细化，分解
- 设计内容
  - 结构，行为，数据，接口，故障处理、安全设计，系统维护和保障等
数据访问层
- 完整的事务概要设计
 - 事务名称
 - 访问的关系表及其数据项
 - 事务逻辑（事务描述）
 - 事务用户（使用、启动、调用该事务的软件模块和系统）
- 事务的特性(ACID特性)
 - 原子性（atomicity）
 - 原子性要求事务所包含的全部操作是一个不可分割的整体，这些操作要么全部提交成功，要么只要其中一个操作失败，就全部撤消
 - 一致性（consistency）
 - 隔离性（isolation）
 - 持久性（durability）
 - 永久性（permanence）
数据持久层
- 数据组织与存储方面

DBAS功能详细设计

表示层详细设计
- 原型迭代法
  - 初步设计：设计人机交互命令系统并优化。（总体设计）
  - 用户界面细节设计。如组织形式、风格、彩色，操作方式。（概要设计）
  - 原型设计与改进。（详细设计）
业务逻辑层详细设计
- 计各模块内部处理流程和算法、具体数据结构、对外详细接口等

应用系统安全架构设计

数据安全设计

数据库的安全性保护
- 用户身份鉴别
  - Windows身份验证
  - SQL身份验证
- 权限控制
- 视图机制
数据库的完整性保护
- 对数据设置一些约束条件（如实体完整性、参考完整性、用户自定义完整性）
- 约束条件作用对象
  - 列（类型、范围、精度、排序）
  - 元组（记录中各属性之间的联系约束）
  - 关系（若干记录间、关系集合与集合之间的联系）
数据库的并发性控制
- 实现数据库并发控制的常用方法是封锁技术
  - 排它锁
  - 共享锁
- 避免死锁的原则
  - 按照同一顺序访问资源。（如第一个事务提交或回滚后第二个事务进行）
  - 避免事务交互性
  - 采用小事务模式，缩短长度和占用时间
  - 尽量使用记录级别的（行锁），少用表级别锁
  - 使用绑定连接，同一用户打开的两个或多个连接可以互相合作
数据库的数据备份与恢复
- 恢复的基本原理
  - 利用存储在系统其他存储器上的冗余数据（即数据备份）来重建
- 备份与恢复策略
  - 双机热备（基于Active/Standby方式的服务器热备）
  - 数据转储（也称为数据备份）
  - 数据加密存储（针对高敏感数据）
数据加密传输
- 数字安全证书
- 对称密钥加密
- 数字签名
- 数字信封

环境安全设计

漏洞与补丁：定期查找漏洞更新补丁
计算机病毒防护：杀毒软件；实时监控
网络环境安全：防火墙；入侵检测系统；网络隔离（逻辑隔离与物理隔离）
物理环境安全：如防盗设施；UPS；温湿度报警器等

制度安全设计

DBAS实施

创建数据库
- 考虑因素：初始空间大小；数据库增量大小；访问性能（如并发数，访问频率）
数据装载
- 步骤：筛选数据—转换数据格式—输入数据—校验数据
编写与调试应用程序
数据库系统试运行（功能测试与性能测试）

UML与数据库应用系统

DBAS建模

统一建模语言（UML）

UML只是一种建模语言，不是一种建模方法
建模方法
- 建模语言
- 建模过程

UML的组成

由语义（自然语言）与表示法（可视化标准符号）组成

语义定义在一个四层建模概念框架

元元模型（Meta-Meta Model），代表要定义的所有事物。
元模型（ Meta Model ），UML的基本元素，“事物”概念的实例。
模型层（ Model ），UML的模型，类模型或类型模型。
用户模型（ User Model ），UML模型的实例，对象模型或实例模型。

组成

系统
角色
用例
- 关系包括扩展、使用和组合等几种关系

UML的五种视图

结构、实现、行为、环境和用例视图
13种图（UML2.0）：静态结构图及行为图两类

DBAS业务流程与需求表达

业务流程与活动图

最适合描述系统或子系统的工作流程

一张活动图中有且只能有一个起点，可有多个结束点

活动图用于低层次程序模块的作用类似于流程图，但活动图可以描述并行操作，而流程图只能描述串行操作

1553067713476.png

系统需求与用例图

系统：各种用例的“黑匣子”
角色：与系统交互的人或其他实体，可以是系统用户也可以是其他系统或者硬件设备
用例：完整功能所有动作（一次操作）集合

DBAS系统内部结构的表达

系统结构与类图

在UML中，用类图来描述系统静态结构，用顺序图和通信图来表示系统动态结构

类图主要表达的是问题领域的概念模型。
类图由类名、属性及操作组成
类与类之间的关系
- 关联（聚集（共享聚集，组成或组合）），继承（或叫泛化），依赖，精化（或叫实现）

系统结构与顺序图

针对每一个特定用例，如何利用类图规范的对象来完成用例交付的任务，必须要利用顺序图
顺序图主要用于描述系统内对象之间的消息发送和接收序列
顺序图中所有的元素，都必须在类图中存在。

系统结构与通信图

通信图显示对象间组织交互关系和链接。不侧重交互顺序，用序列号来确定消息及其并发线程的顺序。
顺序图强调时间，通信图强调空间

DBAS系统微观设计的表达

微观设计与对象图

对象图是类图的实例，描述特定时间中所有对象在系统中的结构，是一个快照

微观设计与状态机图

状态图用来描述有关事件或对象的状态转移。
状态图只能有一个起始状态，可有多个结束状态。
状态间的转移由事件驱动

微观设计与时间图

时间图中，整个矩形框就是一个生命线

DBAS系统宏观设计的表达

宏观设计与包图

表达系统中不同的包、命名空间或不同的项目间彼此关系的图称为包图

宏观设计与交互概述图

将活动图和顺序图嫁接在一起的图

宏观设计与复合结构图

复合结构图适用于系统间的沟通接口

DBAS系统实现与部署的表达

系统实现与组件图

用来表示系统的静态实现视图

系统实现与部署图

部署图说明实体组件，如可执行程序，将如何部署到实际的计算机中，描述系统中硬件和软件的物理配置情况和系统体系结构

数据库及数据库对象

创建及维护数据库

SQL Server中数据库的分类

系统数据库（自动创建）
master：所有系统级信息，元数据、端点、连接服务器和系统配置。
msdb：代理服务调度报警和作业、记录操作员时使用保存此类信息。
tempdb：用于保存临时对象和中间结果，每次启动会重新创建。
model: 所有数据库的模板，存放用户数据库公共信息。
resource：只读数据库。在对象资源管理器中看不到。
用户数据库（保存与用户业务有关的数据）

SQL Server数据库的组成

.mdf：主要数据文件。只有一个，大小不得小于3MB
.ndf ：次要数据文件。有0个或多个，可在一个磁盘或多个磁盘存放
.ldf：事务日志文件。至少有一个日志文件

数据库存储空间的分配

创建用户数据库时，model数据库被自动复制到新建库。
数据存储的最小单位：数据页（Page，简称页）。
1页是一块8KB的连续磁盘空间。
页的大小决定了数据库表中一行数据的最大大小。
行不能垮页存储

数据库文件组

主文件组（PRIMARY）
用户定义文件组

注意：

日志文件不在文件组中，日志空间与数据空间分开管理。
一个文件不可以是多个文件组成员。
若文件组包含多个文件，则在所有文件被填满后才会自动循环增长。
只能指定一个文件组为默认文件组

数据库文件的属性

文件名及其位置
- 逻辑文件名，物理文件名
初始大小
- 不能小于model数据库主要数据文件的大小

用T-SQL创建数据库

1553135348366.png

修改数据库

收缩数据库空间
- 自动收缩：AUTO_SHRINK，默认false。
- 手工收缩：收缩数据库中某个文件大小；
  按比例收缩整个数据库大小

添加和删除数据库文件

ALTER DATABASE database 
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ] 
| ADD LOG FILE < filespec > [ ,...n ] 
| REMOVE FILE logical_file_name 
| ADD FILEGROUP filegroup_name 
| REMOVE FILEGROUP filegroup_name 
| MODIFY FILE < filespec > 
| MODIFY NAME = new_dbname 
| MODIFY FILEGROUP filegroup_name {filegroup_property 
| NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ] 
| COLLATE < collation_name > 
}

扩大指定文件的大小

　　ALTER DATABASE STU_DB 
  　　MODIFY FILE（NAME=student_data,  SIZE=8MB）

添加新的数据文件

ALTER DATABASE STU_DB
　　ADD FILE（NAME=student_data2，
　　FILENAME=‘E:\Data\student_data2.ndf’,
　　SIZE=6MB,FILEGROWTH=0）

收缩整个数据库的大小
- DBCC SHRINKDATABASE
收缩指定文件的大小
- DBCC SHRINKFILE
删除数据库文件
- ALTER DATABASE STU_DB
- REMOVE FILE student_log1
分离和附加数据库
- 分离数据库
  - 实现将数据库从一台数据库服务器移到另一台，不需要重建。从实例中删除，不删除数据文件和日志文件，保持了数据文件和日志文件完整一致
  - EXEC sp_detach_db‘student’，‘true
- 附加数据库
  - 将分离的数据库重新附加到数据库管理系统中。必须指定主要数据文件的物理存储位置和文件名

CREATE DATABASE students
　　On（FILENAME=‘F：\Data\Students_data1.mdf’）
　　FOR ATTACH

架构

是数据库下的一个逻辑命名空间，是数据库对象的容器，一个数据库包含一个或多个构架，同一个数据库内架构名唯一

定义构架

　　CREATE SCHEMA [<构架名>]
　　AUTHORIZATION<用户名>

删除构架

DROP SCHEMA [<构架名>]

分区表

是否创建分区取决于表当前数据量大小，以及将来数据量，还取决于表中数据的操作特点

创建分区表

创建分区函数：告诉DBMS以什么方式进行分区
　　CREATE PARTITION FUNCTION
创建分区方案：作用是将分区函数生成的分区映射到文件组中
　　CREATE PARTITION SCHEME
使用分区创建表

1553147670365.png

1553147699151.png

索引

创建索引

1553147866081.png

删除索引

1553147932467.png

索引视图

建立唯一聚集索引的视图，称为索引视图，也称为物化视图。建立索引后，视图的结果集存放在数据库中。对基本表的修改会反映到索引视图存储的数据中

基本概念

标准视图也称虚拟表，返回结果集与基本表一致。标准视图的结果集不永久存放

适合建立索引视图的场合

若基础数据以批处理形式定期更新，且主要是作为只读数据进行处理，可考虑在更新前删除所有索引视图，然后重建，提高更新性能

很少更新基础数据，索引视图效果更好
索引视图可以提高这些查询类型性能
- 处理大量行的连接和聚合
- 许多查询经常执行的连接和聚合操作
索引视图通常不会提高这些查询类型性能
- 具有大量写操作的OLTP系统
- 具有大量更新操作的数据库
- 不涉及聚合或连接的查询
- GROUP BY具有高基数度的数据聚合

定义索引视图

定义索引视图时，视图只能引用基本表，不能是其他视图。
引用的所有基本表和视图同一数据库，所有者相同。
必须用SCHEMABINDING选项建视图。
视图中表达式引用的所有函数必须确定。
对视图建立的第一个索引是唯一聚簇索引，之后在创建其他

　　CREATE VIEW;WITH SCHEMABINDING; CREATE 
  　　UNIQUE CLUSTERED INDEX …

高级数据库查询

一般数据查询功能扩展

SELECT语句

SELECT [DISTINCT] [TOP n] select_list
查询指定的列,加DISTINCT表不重复
[INTO new_table]
将查询结果复制新表
[FROM table_source]
查询行所在的表
[WHERE search_conditition]
指定搜索条件
[GROUP BY group_by_expression]
指定查询结果分组条件
[HAVING search_condition]
指定组或聚合函数的搜索条件
[ORDER BY order_expression [ASC|DESC]]
指定结果集的排序方式
[COMPUTE expression] 
在结果集的末尾生成汇总数据行

使用TOP限制结果集

TOP n [percent][WITH TIES]
 Top n 前n行
 Top n [percent]前n%行
 [WITH TIES]：包括最后一行取值并列的结果

SELECT TOP 3 WITH TIES GoodsName， GoodsClassName，SaleUnitPrice，FORM Table_Goods a JOIN Table_GoodsClass b
ON a.GoodsClassID=b.GoodsClassID ORDER BY SaleUnitPrice DESC

eg:查询单价最高的前三种商品的商品名、商品类别和单价，包括并列情况

使用CASE函数

分情况显示不同类型的数据
两种类型
- 简单CASE函数
- 搜索CASE函数

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

将查询结果保存到新表中

/*SELECT 查询列表序列  INTO <新表名>
 FROM 数据源……(其他行过滤、分组语句)
注意：表名前加#为局部临时表，##为全局临时表，只有表名为永久表。*/
SELECT * INTO #HD_Customer FROM Table_Customer  WHERE ……

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

并运算（UNION）

将多个查询结果合并为一个结果集

 SELECT 语句1
    UNION [ALL]
    SELECT 语句2
    UNION [ALL]
     ……

要进行合并的查询，SELECT中列数必须相同，语义相同。
每个相对应列的数据类型隐式兼容，如char（20）与varchar（40）。
合并后结果采用第一个SELECT语句的列标题。
若需排序，则GROUP BY语句写在最后一个SELECT之后，且排序的语句是第一个SELECT中的列名。

交运算(INTERSECT)

返回同时在两个集合中出现的记录

SELECT 语句1
INTERSECT 
SELECT 语句2
INTERSECT ……
SELECT 语句n

差运算

返回第一个集合中有而第二个集合中没有的记录

SELECT 语句1
EXCEPT 
SELECT 语句2
EXCEPT……
SELECT 语句n

其他形式的子查询

替代表达式的子查询

在SELECT的选择列表中嵌入了一个只返回一个标量值的子查询

SELECT Cname，Address（SELECT COUNT(*) FROM Table_Customer b ON a.CardID=b. CardID WHERE CustomerID=‘C001’）AS TotalTimes FROM Table_Custmer Where CustomerID=‘C001’

派生表

也称为内联视图，是将子查询作为一个表处理，产生的新表为“派生表”

/*实例：查询至少买了C001和C002两种商品的顾客号和顾客名。*/
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

其他一些查询功能

开窗函数

与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口

在SQL Server 中，一组行被称为一个窗口
开窗函数的调用格式为：函数名（列）OVER（选项）

/*查询全部课程的Cno，CName，Semester， Credit信息及其该学期开设课程的总、最高、平均、最低Credit*/
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(Credit) OVER(PARTITION BY Semester) AS ‘Max’，
FROM Course

将OVER子句与排名函数一起使用

SELECT OrderID, ProductID, OrderQty 
    RANK() OVER(PARTITION BY OrderID ORDER　 BY　 OrderQty　DESC ) AS RANK
FROM OrderDetail
ORDER　 BY OrderID

其他排名函数

DENSE_RANK()
- 排名是连续整数
NTILE()
- 将有序分区中的行划分到指定数目的组中，编号从1开始，函数返回此行所属的组的编号。
ROW_NUMBER()
- 返回结果集中每个分区内的序列号，每个分区的第一行从1开始。

公用表表达式

将查询结果集指定一个临时名字，这些命名的结果集就是公用表表达式

格式： 
WITH <common_table_expression>[,…n]
<common_table_expression>::=
Expression_name[(column_name [,…n])]
AS
(SELECT语句)

/*定义一个统计每个会员购买商品总次数的CTE，并利用CTE查询会员卡号和购买商品的次数。*/
定义：WITH BuyCount（CardID,Counts）AS(SELECT CardID,Count(*) FROM Table_SaleBill GROUP BY CardID )
使用： AS(SELECT CardID,Counts FROM BuyCount ORDER BY Counts)

数据库后台编程技术

存储过程

基本概念

使用T-SQL语言编写代码时，有两种方式存储和执行代码

在客户端存储代码，通过客户端程序或SQL命令向DBMS发出操作请求，由DBMS将结果返回给用户程序。
以子程序的形式将程序模块存储在数据库中，供有权限的用户通过调用反复执行。

存储过程：即存储在数据库中供所有用户程序调用的子程序。

类别

系统存储过程
用户自定义存储过程
扩展存储过程

优点

极高的执行效率。
增强代码的重用性和共享性。
使用存储过程可以减少网络流量。
使用存储过程保证安全性。
在大型数据库中，应用程序访问数据库的最主要方式就是存储过程。
存储过程可以在系统启动时自动执行

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

1553151613387.png

1553152185380.png

/*建立查询某个指定地区购买了单价高于指定价格商品的顾客购买信息，列出顾客姓名，购买商品名，单价，购买日期，会员积分，其中默认地区是“长沙岳麓区”*/
CREATE PROCEDURE p_custbuy
@area varchar(20)=‘长沙岳麓区’，@Price money
AS
SELECT……FROM……JOIN……
WHERE Address=@area AND SaleUnitPrice>@Price
执行：EXEC  p_custbuy @Price=1000

删除存储过程

DROP PROCEDURE

用户定义函数

类似于编程语言中的函数，其结构与存储过程类似，但函数必须有一个RETURN子句，用于返回函数值

两类用户定义函数：量函数和表值函数。前者返回单个数据值，表值函数返回一个表

创建和调用标量函数

CREATE  FUNCTION ……RETURNS return_data_type
AS 
BEGIN
【函数体】
　　RETURN scalar_expression
END

/*创建查询指定商品类别的商品种类数的标量函数*/
CREATE FUNCTION dbo.f_GoodsCount(@class varchar(10))
   RETURN 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  
/*调用标量函数*/
SELECT GoodsName AS 商品名，dbo.f_GoodsCount(‘服装’)AS 种类数
FROM……WHERE……

创建和调用内嵌表值函数

/*创建内联表值函数：*/
CREATE FUNCTION ……RETURNS TABLE
AS 
RETURN [(]select_stmt[)]

/*【实例】创建查询指定类别的商品名和单价的内联表值函数。*/
CREATE FUNCTION f_GoodsInfo(@class char(10))
   RETURNS TABLE
AS 
   RETURN(
     SELECT GoodName,SaleUnitPrice FROM Table_GoodClass a JOIN Table_Goods b ON a.GoodsClassID= B.GoodsClassID
     WHERE GoodClassName=@class)
/*调用： */
SELECT * FROM dbo.f_GoodsInfo(‘服装’)

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

调用建多语句表值函数:在SELECT的FROM子句中使用

CREATE FUNCTION ……RETURNS @return_variable TABLE<table_type_definition定义返回的表结构>
AS 
BEGIN
【函数体:SQL语句】
    RETURN 
END

/*【实例】创建查询指定类别的商品名、单价、生产日期和商品种类的多语句表值函数。*/
CREATE FUNCTION f_GoodsDetails(@class varchar(20))
    RETURNS @f_GoodsDetails TABLE（
商品名 varchar（50），
单价 money，
生产日期 datetime，
种类数 int）
AS  
BEGIN
  INSERT INTO @f_GoodsDetails 
  SELECT GoodsName,SaleUnitPrice，ProductionDate，dbo. f_GoodsDetails (@class) FROM      Table_GoodsClass a JOIN  Table_Goods b ON a.GoodsClassID= B.GoodsClassID     WHERE GoodClassName=@class)
   RETURN
END
调用：SELECT * FROM dbo.f_GoodsDetail(‘服装’)

删除用户自定义函数

DROP FUNCTION f_GoodsCount
DROP FUNCTION f_GoodsInfo
DROP FUNCTION f_GoodsDetails

触发器

基本概念

特殊存储过程，在对表中的数据进行UPDATE、INSERT、DELETE操作时自动触发执行，常用于保证业务规则和数据完整性，增强数据完整性约束能力

在SQL Server 2008中，对于更新操作的触发器，系统将产生两张逻辑工作表，即DELETED表和INSERTED表。DELETED表用于存储更新前数据，INSERTED用于更新后的数据

SQL Server 2008支持三种类型

DML
DDL
登录触发器

适用场合:

完成比CHECK（只能实现同一表列之间取值约束）约束更复杂的数据约束。
保证数据库性能而维护的非规范化数据。
可实现复杂的商业规则。
评估数据修改前后的表状态，并采取对策

创建触发器

CREATE TRIGGER trigger_name ON 
{ table | view }
[WITH ENCRYPTION]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] 
[ DELETE ] }
AS 
sql_statement[…n]

参数说明：

FOR或AFTER：后触发型，操作、约束检查完成后触发。
INSTEAD OF ：前触发型，数据操作语句最多定义一个触发器。执行触发器而非引发语句。若满足完整性约束则需要重新执行这些数据操作

注意

一个表可建多个触发器，每个触发器可由三个操作触发。ALTER类型同一操作上建立多个触发器，INSTEAD OF类型同一操作上建立一个触发器
所有建立和更改数据库以及数据库对象的语句、DROP语句不允许在触发器中使用
触发器不要返回任何结果

/*维护不同列的取值完整性的触发器。保证“商品表”中单价列值与“商品价格变动表”中单价列值一致*/
CREATE TRIGGER UnitPriceConsistent
ON Table_PriceHistory FOR INSERT,UPDATE
AS
   DECLARE @NewPrice money
   SELECT @NewPrice=SaleUnitPrice FROM inserted   UPDATE Table_Goods SET SaleUnitPrice=@NewPrice 
    WHERE GoodsID IN (SELECT GoodsID FROM inserted)

/*【实例2】创建只允许删除会员卡积分低于500分的顾客记录的触发器。*/
CREATE TRIGGER DeleteCust
ON Table_Customer INSTEAD OF DELETE 
AS
   IF NOT EXISTS(SELECT * FROM deleted WHERE CardID IN (SELECT CardID FROM Table_Card WHERE Score>=500))
   DELETE FROM Table_Customer WHERE CardID IN (SELECT CardID FROM deleted)

删除触发器

DROP TRIGGER OperateCon
DROP TRIGGER UnitPriceConsistent
DROP TRIGGER DeleteCust

游标

现对SELECT结果集的逐行处理

组成

游标结果集（SELECT返回结果集）与游标当前行指针（指向结果集中某一行）

特点

定位特定行；
从当前位置检索一行或多行；
支持当前行数据修改；
对修改结果提供不同级别的可见性支持

使用游标

声明游标
- DECLARE cursor_name[1] CURSOR FOR select_statement[2]
打开游标
- OPEN cursor_name
提取数据
- FETCH [1]FROM cursor_name [INTO @ variable_name[,…n]]
关闭游标
- CLOSE cursor_name
释放游标
- DEALLOCATE cursor_name

/*对Table_Customer表，定义一个查询“长沙岳麓区”姓“王”的顾客姓名和邮箱的游标，并输出游标结果。*/
DECLARE @cn VARCHAR(10） @cn 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
WHILE @@ FETCH_STATUS=0
BEGIN
  PRINT’顾客姓名’+@cn +‘，邮箱：’+ @Email
  FETCH NEXT FROM Cname_cursor INTO @cn , @Email
   /*
NEXT：紧跟当前行返回结果行，并且当前行递增为返回行。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。NEXT 为默认的游标提取选项。
PRIOR：搜索返回紧邻当前行前面的结果行，并且当前行递减为返回行。如果 FETCH PRIOR： 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。
FIRST：返回游标中的第一行并将其作为当前行
LAST：返回游标中的最后一行并将其作为当前行搜索。
ABSOLUTE { n | @nvar}：如果 n 或 @nvar 为正，则返回从游标头开始向后的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar 为负，则返回从游标末尾开始向前的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar 为 0，则不返回行。n 必须是整数常量，并且 @nvar 的数据类型必须为 smallint、tinyint 或 int。
RELATIVE { n | @nvar}：如果 n 或 @nvar 为正，则返回从当前行开始向后的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar 为负，则返回从当前行开始向前的第 n 行，并将返回行变成新的当前行。如果 n 或 @nvar 为 0，则返回当前行。在对游标进行第一次提取时，如果在将 n 或 @nvar 设置为负数或 0 的情况下指定 FETCH RELATIVE，则不返回行。n 必须是整数常量，@nvar 的数据类型必须为 smallint、tinyint 或 int。*/       END
CLOSE Cname_cursor
DEALLOCATE Cname_cursor

安全管理

安全控制概述

安全性和完整性

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

安全控制的目标

保护数据免受意外或故意的丢失、破坏或滥用

数据库安全的威胁

可用性损失，机密性数据损失,私密性数据损失，偷窃和欺诈，意外的损害

安全控制模型

身份验证（用户）
操作权限控制（数据库应用程序与数据库管理系统）
文件操作控制（操作系统）
加密存储与冗余（数据库）

授权和认证

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

自主存取控制
强制存储控制

存取控制

自主存取控制

通过SQL的GRANT,REVOKE,DENY语句来实现

权限种类：
- 维护权限与操作权限（语句权限与对象权限）
用户分类：
- 系统管理员（sa）、数据库对象拥有者、普通用户

强制存取控制

为避免自主存取模式下数据的“无意泄露”

将全部实体分为主体和客体两大类
- 主体：系统活动实体，实际用户和进程。
- 客体：被动实体，受主体操纵，包括文件、基本表、视图
对于主体和客体，DBMS为它们的每个实例指派一个敏感度标记（Label）
敏感度标记：DBMS指派，主体为许可证级别，客体为密级。分绝密、秘密、可信和公开等若干级别
规则：
- 仅当主体许可证级别大于或等于客体密级，主体可以读取相应客体
- 仅当主体许可证级别等于客体密级，该主体才能写相应客体

审计跟踪

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

对数据安全有辅助作用

统计数据库的安全性

提供基于各种不同标准的统计信息或汇总数据

用于控制对统计数据库的访问。
统计数据库允许用户查询聚合类型的信息，如总和、平均等，但不允许查询个人信息

SQL Server 的安全控制

身份验证模式

Windows身份验证模式
混合身份验证模式

登录帐户

类别
- SQL Server自身负责身份验证的账户，内置系统账户与用户自己创建。
- 登录到SQL Server的Windows网络账户，可以是组账户或用户账户。
建立登录账户
- CREATE LOGIN login_name
修改登录账户属性
- ALTER LOGIN login_name
删除登录账户
- DROP LOGIN login_name

CREATE LOGIN SQL_User1 WITH PASSWORD=‘a1b2c3XY’
ALTER LOGIN SQL_User1 WITH PASSWORD=‘a4b5c6XY’
ALTER LOGIN SQL_User3 WITH NAME=NewUser

数据库用户

用户有了登录帐户，只能连接到SQL服务器，并不具有访问数据库的权限

映射
- 让登录账户成为数据库用户的操作成为映射。一个登录账户可以映射为多个数据库用户。默认情况下，新建数据库只有一个用户：dbo，数据库用户的拥有者

建立数据库用户

CREATE USER user_name[|FOR|FROM]
LOGIN login_name

Guest用户
- 特殊数据库用户，匿名访问，没有映射到登录账户的时候使用
- GRANT CONNECT TO guest
  REVOKE CONNECT TO guest
删除数据库用户
- DROP USER user_name

权限管理

对象级别的权限（6种）

SELECT、INSERT、UPDATE、DELETE、REFERENCES、EXECUTE

授权语句

/*GRANT 对象权限 ON 对象 TO （主体：数据库用户名或角色）[WITH GRANT OPTION]*/
实例：
GRANT SELECT ON Address TO abc
GRANT EXECUTE ON OBJECT::HR.EI TO abc
GRANT REFERENCES（EmployeeID）ON vEmp TO abc WITH GRANT OPTION

拒绝权限

/* DENY 对象权限 ON 对象 TO （主体：数据库用户名或角色） [CASCADE][AS主体]
实例：*/
DENY SELECT ON Address TO abc
DENY EXECUTE ON OBJECT::HR.EI TO abc
DENY REFERENCES（EmployeeID）ON vEmp TO abc CASCADE

收权语句

/* REVOKE 对象权限 ON 对象 TO （主体：数据库用户名或角色） [CASCADE][AS角色]*/
实例：
REVOKE SELECT ON Address TO abc
REVOKE EXECUTE ON OBJECT::HR.EI TO abc
REVOKE REFERENCES（EmployeeID）ON vEmp TO abc CASCADE

语句级别的权限

/* CREATE DATABASE|PROCEDURE|TABLE|VIEW|FUNCTION
BACKUP DATABASE|LOG */
实例：
GRANT CREATE DATABASE TO user0
GRANT CREATE DATABASE , CREATE VIEW TO user1,user2
DENY CREATE VIEW TO user1
REVOKE CREATE DATABASE FROM user0

角色

一组具有相同权限的用户就是角色

系统角色
- 固定服务器角色（服务器级角色）
- 固定数据库角色（数据库级角色）
用户角色
- 均是数据库级角色
固定服务器角色
- Bulkadmin：执行BULK INSERT语句权限。
- Dbcreator：创建、修改、删除还原数据库权限。
- Diskadmin：具有管理磁盘文件的权限
- Processadmin：管理运行进程权限。
- Securityadmin：专门管理登录账户、读取错误日志执行CREATE DATABASE 权限的账户，便捷
- Serveradmin：服务器级别的配置选项和关闭服务器权限。
- Setupadmin：添加删除链接服务器。
- Sysadmin：系统管理员，Windows超级用户自动映射为系统管理员。
- Public：系统预定义服务器角色，每个登录名都是这个角色的成员。没有授予或拒绝特定权限，则将具有这个角色权限。
- ```
/* （1）为固定服务器角色添加成员 */
Sp_addsrvrolemember
EXEC Sp_addsrvrolemember ‘user1’，‘sysadmin’
/* （2）删除固定服务器角色成员 */
Sp_dropsrvrolemember
EXEC Sp_dropsrvrolemember ‘user1’，‘sysadmin’
```
固定数据库角色
- Db_accessadmin:添加或删除数据库权限
- Db_backupoperator：备份数据库、日志权限
- Db_datareader:查询数据库数据权限
- Db_datawriter：具有插入、删除、更改权限
- Db_ddladmin：执行数据定义的权限
- Db_denydatareader：不允许具有查询数据库中所有用户数据的权限。
- Db_denydatawriter：不允许具有插入、删除、更改数据库中所有用户数据权限。
- Db_owner：具有全部操作权限，包括配置、维护、删除数据库。
- Db_securityadmin：具有管理数据库角色、角色成员以及数据库中语句和对象的权限。
- ```
/* （1）为固定数据库角色添加成员 */
Sp_addsrvrolemember
EXEC Sp_addrolemember ‘Db_datareader’，‘SQL_User2’
/* （2）删除固定服务器角色成员 */
Sp_droprolemember
EXEC Sp_droprolemember ‘Db_datareader’,‘SQL_User2’
```
用户定义的角色
- 用户定义的角色属于数据库一级。
 用来简化使用数据库时的权限管理。
- 用户定义的角色成员可以是用户定义角色或数据库用户。注意： 角色中的成员拥有的权限=成员自身权限+所在角色权限。但若某个权限在角色中被拒绝，则成员不再拥有
- ```
CREATE ROLE
实例： CREATE ROLE MathDept [AUTHORIZATION Software]
注意：为用户定义角色授权、添加、删除用户定义的角色中的成员与固定数据库角色一致。 
删除用户定义角色
DROP ROLE
实例：DROP ROLE MathDept
```

Oracle的安全管理

Oracle的安全机制

数据库级的安全控制
- 通过用户身份认证和授予用户相应系统权限来保证
表级、列级、行级的安全控制
- 通过授予或回收对象权限保证。支持集中式、分布式、跨平台应用

两级安全管理员

全局级
- 负责管理、协调，维护全局数据一致性和安全性；
场地级
- 负责本结点数据库安全性，用户管理、系统特权与角色管理

用户与资源管理

DBA用户
- 由DBMS自动创建，sys与system用户，拥有全部系统特权。
普通用户
- 由DBA用户或有相应特权的用户创建，并授予系统特权

建立用户

CREATE USER use1 IDENTIFIED BY 123456
DEFAULT TABLESPACE student（存储在student表空间）
QUOTA 5M ON student（限制使用空间为5M）

管理用户和资源

DBA特权用户可以改变一个用户资源使用限额、密码、登录次数等

ALTER USER use1
QUOTA 60M ON student（限制使用空间为60M）
ALTER USER use1 IDENTIFIED BY 12345678

删除用户

DROP USER 
DROP USER user1 CASCADE（删除用户及其所拥有的全部对象）

权限管理

系统特权
- Connect：不能建立任何对象，可以查询数据字典及访问数据库对象。
- Recource ：可建立数据库对象（表、视图、索引……）
- DBA ：拥有预定义的全部权限

对象特权

用于维护表级、行级、列级数据的安全性。

实例：
GRANT all ON dep TO user1
GRANT select(tno,tname,sal) ON teacher TO 
user2

数据库运行维护与优化

数据库运行维护基本工作

维护工作

数据库转储与恢复
数据库安全性、完整性控制
检测并改善数据库性能
数据库的重组和重构
- 重组不修改数据库原有设计的逻辑结构和物理结构
- 重构部分修改模式和内模式

运行状态监控与分析

根据监控分析实现不同，分为：
- 数据库系统建立的自动监控机制
- 由DBMS自动监测数据库的运行情况。
- 管理员手动实施的监控机制
根据监控对象不同，分为:
- 数据库构架体系的监控
  - 监控空间基本信息、空间使用率与剩余空间大小等。
- 数据库性能监控
  - 监控数据缓冲区命中率、库缓冲、用户锁、索引使用、等待事件等

数据库存储空间管理

SQL Server数据库中一个逻辑上的数据库直接和一组物理上的数据文件对应，没有表空间概念

空间使用情况变化带来的问题
- 降低数据库系统服务性能
- 空间溢出导致灾难停机事故
数据的存储结构分为
- 逻辑存储结构
- 物理存储结构
DBMS对空间的管理包括
- 创建数据库空间、更改空间大小、删除空间、修改空间状态，新建、移动、关联数据文件等。

数据库性能优化

数据库运行环境

外部环境
- 数据库性能和外部环境有很大关系，主要外部条件包括：CPU（CPU的处理能力是衡量计算机性能的一个标志）、网络（大量的SQL数据在网络上传输会导致网速变慢）
调整内存分配
- 调整相关参数控制数据库内存分配，很大程度改善数据库系统性能。
调整磁盘I/O
- 数据库性能优劣的重要度量是响应时间
- 令I/O时间最小化，减少磁盘上文件竞争带来的瓶颈
调整资源竞争
- 修改参数以控制连接到数据库的最大进程数。
- 减少调度进程的竞争
- 减少多线程服务进程竞争
- 减少重做日志缓冲区竞争
- 减少回滚段竞争

参数调整

模式调整

规范化
- 高效率利用存储空间，减少数据的冗余，减少数据的不一致性
- 规范化关系解决了数据维护的异常，并使数据冗余最小化，但会导致数据处理性能下降
反规范化
- 增加派生冗余列
  - 增加的列由表中的一些数据项经过计算生成。
  - 作用：查询时减少连接操作，避免使用聚合函数
- 增加冗余列
  - 增加的列由表中的一些数据项经过计算生成。
  - 作用：查询时减少连接操作，避免使用聚合函数
- 重新组表
  - 当用户经常查看的某些数据是由多个表连接之后才能得到，就可以考虑先把这些数据重新组成一个表，这样在查询时会减少连接提高效率
- 分割表
  - 水平分割：
    - 根据行的使用特点进行分割，分割之后所有表的结构都相同。而存储的数据不同。使用并（Union）操作。
  - 垂直分割：
    - 根据列的特点分割，分割后所得表除了都包含主码外其他列都不相同。通常将常用列与不常用列分别放在不同表中，查询减少I/O次数。缺点是使用连接（Join）操作
- 新增汇总表
  - 为降低汇总操作的时间，将频繁使用的统计中间结果或最终结果存储在汇总表中，从而降低数据访问量和汇总操作的CPU计算量

数据库存储优化

索引视图
- 定义：包括一个查询结果的数据库对象，是预先计算并保存表连接或聚集等耗时较多的操作结果。（一个定期刷新数据的视图，自动刷新或人工刷新）
  适用于多个数据量较大的表进行连接操作及分布式数据库中在多站点的表进行连接时使用。
  物化视图还可以进行远程数据的本地复制（物化视图的存储也称为快照），用于实施数据库间的同步
聚集
- 一个聚集是一组表，将经常一起使用的具有同一公共列值的多个表中的数据行存储在一起，由公共列构成聚集码。
- 作用：最小化必须执行的I/O次数。
- 注意：将记录插入聚集的表之前，必须建立聚集索引，且按聚集码进行索引；对于聚集中的多个表，聚集值只存储一次。
- 劣势：聚集表的插入、更新、删除性能差，具体使用要权衡

查询优化

合理使用索引
- 索引提高查询效率，索引增加系统开销
- 建立索引原则
  - 是否为一个属性建索引：该属性是码或存在某个查询中被使用
  - 在哪些属性建立索引：若一个关系的多个属性共同出现在若干个查询中，一般会采用多属性索引。
  - 是否建立聚簇索引：聚簇索引适合范围查询，可建立多属性索引。优点体现在数据记录存取过程中。
  - 使用散列还是树索引：散列适合等值查询；关系数据库多使用B+索引，支持作为搜索码的属性上的等值查询和范围查询
- 索引使用原则：
  - 经常在查询中作为条件被使用的列，应为其建立索引。
  - 频繁进行排序或分组的列，应为其建立索引。
  - 一个列的值域很大时，应为其建立索引。
  - 如果待排列的列有多个，建复合索引。
  - 可以使用系统工具来检查索引完整性，必要时进行修复。当数据表更新大量数据后，删除并重建索引以提高查询
  - 索引建立完成后，运行期间还需调优。
    优的目的：动态地评估需求
- 索引调整和修改的原因：
  - 由于缺少索引，某些查询语句执行时间过长。
  - 某些索引自始至终没有使用，却占用了较多磁盘空间。
  - 某些索引建立在被频繁改变的属性上，导致系统开销过大
避免或简化排序
- ORDER BY和GROUP BY语句的执行涉及排序，磁盘排序开销很大，应利用索引自动以适当的次序产生输出。
- 影响优化器的因素：
  - 由于现有索引不足，导致排序索引中不包括一个或几个待排序的列。
  - Group by和order by子句中列的次序与索引次序不一致。
  - 排列的列来自不同的表。
  - 为避免不必要的排序，要正确地增建索引，合理合并数据库表，如排序不可避免，则简化它，如缩小排序列的范围
消除对大型表数据的顺序存取
- 嵌套查询中，对表的顺序存取严重影响查询效率。
- 优化方法：对连接列进行索引，或使用并集来避免顺序存取
避免复杂正则表达式
- 消耗较多CPU资源进行字符串匹配
使用临时表加速查询
- 将表的一个子集进行排序并创建临时表
用排序来取代非顺序磁盘存取
- 非顺序磁盘存取最慢。使用以数据库排序功能为基础的SQL替代非顺序存取
不充分的连接条件
- 左（右）外连接包含与NULL数据匹配，相比内连接，代价可能很高
存储过程
- 尽量使用自带返回参数，而非自定义返回参数，减少不必要参数，避免数据冗余
不要随意使用游标
- 占用较多系统资源。尤其是大规模并发情况下，很容易使得系统资源耗尽而崩溃
事务处理
- 一旦将多个处理放入事务，会降低系统处理速度。将频繁操作的多个可分割的处理过程放入多个存储过程中，这样就大大提高系统响应速度

SQL Server 性能工具

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

故障管理

故障管理概述

故障类型及其解决方案

事务内部故障

预期的事务内部故障
- 可通过事务过程本身发现的。解决办法：事务回滚
非预期的事务内部故障
- 不能由事务程序处理的故障，如运算溢出等。事务故障的恢复由系统自动完成

系统故障

运行期间，由于硬件故障、数据库软件及OS漏洞、突然断电等故障，导致数据不一致
解决办法：重启后，撤销（UNDO）所有未提交的事务；重做（REDO）所有已提交的事务；

介质故障

又称硬故障，运行期间由于磁头碰撞、磁盘损坏、强磁干扰、天灾人祸等，使得数据丢失的一类故障
容错策略：
- 软件容错
  - 使用数据库备份及事务日志文件，通过恢复技术，恢复数据库到备份结束时的状态。若故障导致事务日志文件丢失，则不能完全恢复
- 硬件容错
  - 使用双物理存储设备
    - 如双硬盘镜像。缺点：自然灾害或机房水灾、火灾导致双硬盘同时损坏，则失去保护作用。
  - 设计两套相同的数据库系统，通过数据库软件机制，同步变化数据。

计算机病毒故障

病毒是恶意的计算机程序，破坏OS及数据库系统（破坏数据文件为主）
解决办法：防火墙、杀毒软件、数据库备份文件。

数据库恢复技术概述

恢复的基本原理是冗余。
数据库恢复：把数据库从错误状态恢复到某一已知的正确状态。
在DBMS中，数据库恢复子系统占10%以上。
恢复机制涉及两个问题：
- 如何建立冗余数据
 - 数据备份、登记日志文件、数据库复制、数据库镜像、为段设立保存点以及使用后备段与现行页表来支持对段的保存等
 - 最常用的是数据备份和登记日志文件
- 如何利用这些冗余数据实施数据库恢复。

数据转储

指DBA或DBMS定期复制数据库，并将其存放到其他介质的过程。这些保存的副本被称为后援副本或后备副本

静态转储和动态转储

静态转储：
- 期间系统不能运行其他事务，不允许任何存取、修改活动。
  静态转储保证数据的一致性，但降低了数据库的可用性。转储和事务是互斥的。
动态转储：
- 即允许转储和事务并发执行。动态转储不能保证转储数据的一致性
- 动态转储+日志文件（记录转储期间各事务对数据库的修改活动记录）：既保证数据一致性又提高了数据库的可用性

数据转储机制

完全转储
- 对数据库中所有数据进行转储。占据较多时间和空间，但恢复时间短。
增量转储
- 只复制上次转储后发生变化的文件或数据块。所需时间及空间短，但只能和完全转储配合才能对数据库进行恢复。
差量转储
- 对最近一次数据库完全转储以来发生的数据变化进行转储

多种转储方法结合使用

仅使用完全转储：占据时间和空间多，代价大。
完全转储+增量转储：恢复时间较长。
完全转储+差量转储：恢复时间短

日志文件

日志文件的概念

DBMS运行中，将所有事务的修改操作登记到日志文件

作用：事务故障恢复和系统故障恢复必须使用日志文件。
在动态转储方式中必须建立日志文件。
在静态转储方式中也可使用日志文件

日志文件的格式与内容

以记录为单位的日志文件
- 各个事务的开始标记 BEGIN TRANSACTION
- 各个事务的结束标记 COMMIT OR ROLLBACK
- 各个事务的所有更新操作
以数据块为单位的日志文件
- 事务标识和被更新的数据块

登记日志文件的原则

登记的次序严格按并行事务执行的时间次序。
- 保证事务对数据库的操作的可再现性和正确性。
必须先写日志文件，后写数据库。
后顺序导致必然性

检查点

检查点的作用
- 最大限度地减少数据完全恢复时必须执行的日志部分
检查点的引入
- 在日志中增加检查点记录，增加一个“重新开始文件”。
- 检查点记录的内容：建立检查点时刻正执行的事务清单；这些事务最近一个日志记录的地址。
- 重新开始文件记录的内容：各个检查点记录在日志中的地址
基于检查点的恢复步骤
1. 从“重新开始文件”中找到最后一个检查点记录在日志文件中的地址，从而找到最后一个检查点记录。
2. 由该检查点记录得到执行的事务清单ACTIVE_LIST。
3. 从检查点开始正向扫描文件。
4. 对UNDO-LIST中的事务执行UNDO，对REDO-LIST中的事务执行REDO

硬件容错方案

概述

由于数据库运行的硬件故障、机房电力故障、机房自然灾害等，要求从硬件级别对DBMS进行保护

磁盘保护技术RAID

冗余技术

镜像冗余
- 即把所有的数据复制到其他设备。
- 额外开销大：更多的磁盘、控制器、电缆。
校验冗余
- 对成员磁盘的数据执行异或（XOR）操作，得到其校验值，并存放在另外的校验盘上。
- 现复杂，但比镜像冗余占据的空间小

采用的方法

RAID-0：
- 将多个磁盘合并成一个大的磁盘，不具有冗余，并行I/O，速度最快。
RAID-1：
- 两组以上的N个磁盘相互作镜像，在一些多线程操作系统中能有很好的读取速度，理论上读取速度等于硬盘数量的倍数，另外写入速度有微小的降低。只要一个磁盘正常即可维持运作，可靠性最高。
RAID-5：
- RAID Level 5是一种储存性能、数据安全和存储成本兼顾的存储解决方案。它使用的是Disk Striping（硬盘分区）技术。RAID 5至少需要三颗硬盘，RAID 5不是对存储的数据进行备份，而是把数据和相对应的奇偶校验信息存储到组成RAID5的各个磁盘上，并且奇偶校验信息和相对应的数据分别存储于不同的磁盘上。
RAID-10：
- RAID0与RAID-1的组合体，继承了前者的快速，后者的安全。 RAID-10冗余度为50%

服务器容错技术

引入服务器容错原因
- 解决服务器硬件异常问题。
服务器容错技术简介
- 采用两台相同的服务器，共享存储设备。（双机热备，Active-Standby）
- 两台服务器之间会有私有网络进行心跳检测
服务器接管过程
其他服务器容错技术
- 硬件级别：自行设计制造的专用软硬件架构。
- 软件级别：专门的服务器级别容错技术，如Oracle提供了RAC架构

数据库镜像与数据库容灾

引入数据库镜像原因
- 前面几种恢复技术都必须及时正确地转储数据库。
数据库镜像简介
- 是一种用于提高数据库可用性的解决方案，它根据DBA的要求，自动把整个数据库或关键数据复制到另一个磁盘上。

数据库镜像分类

双机互备援模式
- 两台机器均为工作机。正常状况下均为系统提供支持，互相监视对方的运行情况。
双机热备份模式
- 一台为工作机，一台为备份机u。工作机为系统提供支持，备份机监视工作机的运行情况

工作方式

“数据库镜像会话”中，主体服务器和镜像服务器作为“伙伴”进行通信和协作。在会话中扮演互补角色。一旦出现故障，使用“角色切换”过程来互换主体服务器和镜像服务器

SQL Server数据库镜像简介

SQL Server数据库镜像是将数据库事务处理从一个SQL Server数据库移到不同的SQL Server数据库

镜像的复制是一个备用的复制，不能直接访问，只用来进行错误恢复。“见证服务器”使镜像服务器自动识别。

两种运行模式
- 高安全性模式
- 运行模式
三种实现方式
- 高可用性
  - 两台服务器同步事务写入，支持自动错误恢复。
- 高保护性
  - 两台服务器同步事务写入，手工错误恢复。
- 高性能
  - 两台服务器写入不同步，手工错误恢复

备份与恢复数据库

备份与恢复的概念

备份数据库

防止数据丢失
数据转移的一种方式

恢复数据库

介质故障恢复
非介质故障恢复

SQL Server的备份与恢复机制

恢复模式

简单恢复模式
- 此模式简略地记录大多数事务，不备份事务日志。所记录的信息只是为了确保在系统崩溃或还原数据备份之后数据库的一致性
完整恢复模式
- 此模式完整地记录了所有的事务，并保留所有的事务日志记录，直到将它们备份
大容量日志恢复模式
- 此模式简略地记录大多数大容量操作（例如索引创建和大容量加载），完整地记录其他事务。大容量日志恢复模式提高了大容量操作的性能，常用作完整恢复模式的补充

备份内容

用户数据库
系统数据库

备份时间

系统数据库：修改之后进行备份
用户数据库：周期性备份

触发备份的操作

创建数据库之后，或批量加载数据之后。
创建索引之后。
执行清理事务日志的操作之后。
执行大容量数据操作之后。

SQL Server的备份机制

备份设备

永久备份设备：在备份之前需要先建立。
临时备份设备：不需要预先建立，在备份时将数据库直接备份到物理文件上

/*使用系统存储过程创建备份设备
sp_addumpdevice 
　　[ @devtype = ] 'device_type' ,
　　[ @logicalname = ] 'logical_name' ,
　　[ @physicalname = ] 'physical_name‘
其中，device_type表示设备类型，其值可为disk和tape。logical_name表示设备的逻辑名称。physical_name表示设备的实际名称*/
/*创建一个名为 MYDISKDUMP 的磁盘备份设备，其物理名称为 D:\Dump1.bak */
　　EXEC sp_addumpdevice 'disk', 'mydiskdump', 'D:\dump1.ba

备份类型

数据库备份
- 完整数据库备份
  - 完整备份将备份整个数据库，包括事务日志部分（以便可以恢复整个备份）。完整备份代表备份完成时的数据库。
  - 是恢复的基线
  - 备份数据文件、数据库对象和数据的信息
  - 备份备份过程中发生的活动
  - 备份未提交的事务到日志
- 差异数据库备份
  - 备份从上次完整备份之后数据的改变
  - 备份差异备份过程中发生的活动
  - 备份未提交的事务到日志
  - 比完整备份节约时间
  - 以前一次完整备份为基准点（成为差异基准）。
  - 在还原差异备份前，应先还原最新的完整备份，再还原基于该完整备份的最新差异备份
文件备份
- 文件备份
  - 可以分别备份和还原数据库中的文件。使用文件备份使用户可以仅还原已损坏的文件，而不必还原数据库的其余部分，从而提高恢复速度
- 差异文件备份
  - 创建文件或文件组的完整备份后，可以基于该完整备份创建一系列的差异备份。
  - 差异文件备份只捕获自上一次文件备份以来更改的数据
事务日志备份
- 备份从上次日志备份之后的日志记录
  备份完成后要截断日志
- 事务日志备份仅用于完整恢复模式或大容量日志恢复模式
- 类型
  - 纯日志备份
  - 大容量操作日志备份
  - 结尾日志备份
常用备份策略
- 完整备份
  - 适合于数据库数据不是很大，而且数据更改不是很频繁的情况。
  - 完整备份一般可以几天进行一次或几周进行一次
  - 当对数据库数据的修改次数不是很频繁，而且允许一定量的数据丢失时，可以选择只用完整备份策略
  - 完整备份包括了对数据和日志的备份。
- 完整备份 + 日志备份
  - 如果不允许丢失太多的数据，而且又不希望经常地进行完全备份（因为完全备份进行的时间比较长），则可以在完全备份中间加一些日志备份。
- 完整备份 + 差异备份 + 日志备份
  - 完全备份加差异备份和日志备份的策略，即在完全备份中间加一些差异备份，在差异备份中间加一些日志备份。
实现备份
- 使用SQL Server管理平台
- 使用T-SQL语句

SQL Server的恢复机制

支持类型
- 数据库（数据库完整还原）
- 数据文件（文件还原）
还原的顺序
- 恢复最近的完全备份；
- 恢复最近的差异备份（如有）；
- 恢复自差异备份之后的所有日志备份（按备份的先后顺序）。
- 恢复数据库。
实现还原
- 使用SQL Server管理平台
- 使用T-SQL语句

/*假设已对pubs数据库进行了完全备份，并备份到MyBK_1备份设备上，假设此备份设备只含有对pubs数据库的完全备份。则恢复pubs数据库的语句为：*/
RESTORE DATABASE pubs 
FROM MyBK_1 
RESTORE DATABASE Students 
  FROM MyBK_2 
   WITH FILE=1, NORECOVERY
RESTORE DATABASE Students 
   FROM MyBK_2 
   WITH FILE=2, NORECOVERY 
RESTORE LOG Students 
   FROM MyBKLog1

Oracle的备份与恢复机制

故障类型

内部恢复机制自动处理
- SQL语句错误
- 进程失效
- 实例失效
- 网络失效
利用备份数据恢复
- 用户错误
- 介质失效

Oracle数据库物理备份与恢复

物理备份
- 用户管理的备份
- Recover Manager(RMAN)备份
- 第三方备份软件
逻辑备份
- EXP/IMP
- EXPDP/IMPDP

恢复数据库

Restore:
- 从备份数据中找到完整备份文件，恢复完整文件的过程。
Recover:
- 将日志和增量备份的改变应用到数据文件，使数据文件恢复到指定时间点上的过程

大规模数据库架构

分布式数据库

概述

分布式数据库系统数据分布存储于若干场地，并且每个场地由独立于其它场地的DBMS进行数据管理
物理上分散、逻辑上集中的数据库系统
由局部数据库管理系统、全局数据库管理系统GDBMS、全局数据字典、通信管理四部分组成

分布式数据库目标

本地自治、非集中式管理、高可用性。（最基本特征）
位置独立性、数据分片独立性、数据复制独立性。（分布透明性）
分布式查询、事务管理。（复杂性）
硬件独立性、操作系统独立性、网络独立性、数据库管理系统独立性

数据分布策略

数据分片（对关系操作）
按一定规则将某一个全局关系划分为多个片断
- 水平分片——每个分片是原始关系所有数据行的子集合。
- 垂直分片——每个分片是原始关系所有数据列的子集合。
- 导出分片——导出水平分片。
- 混合分片——以上三种的混合
数据分配（对分片结果操作）

将分片产生的片断分配存储在各个场地上。解决数据分配的方法
- 集中式——所有数据片断安排在一个场地上。
- 分割式——所有全局数据有且只有一份，分割成若干被分配在特定场地上的片断。
- 全复制式——全局数据有多个副本，每个场地上有一个完整的数据副本。
- 混合式—— 介于分割与全复制式之间

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

1553261177038.png

分布透明性

分片透明性
- 用户无需考虑数据分片。
- 位于全局概念模式与分片模式之间，当分片模式改变时，只需改变全局概念模式到分片模式之间的映像，而不会影响到全局概念模式和应用程序
位置透明性
- 用户只需考虑数据分片情况，无需考虑数据分片位置。
- 位于分片模式与分配模式之间，当存储场地发生变化时，只需改变分片模式到分配模式之间的映像
局部映像透明性
- 用户既要了解全局数据的分片情况，还要了解各片断的副本复制情况及位置分配情况。

1553261294799.png

分布式数据库的相关技术

分布式查询
分布式事务管理
- 恢复控制:
  　　基于两阶段的提交协议。
- 并发控制:
  　　基于封锁协议。

并行数据库

概述

通过并行实现各种数据操作，如数据载入、索引建立、数据查询等，可以提高系统的性能。

优势

增强的可用性：当存储某个关系的场地系统崩溃时，可继续使用存储在别的场地的副本。

并行数据库系统结构

共享内存系统(Shared Memory)
- 多个cpu通过连接网络进行通信，并能访问公共的主存。随着CPU增加，造成内存冲突
- 1553261609153.png
共享磁盘系统(Shared Disk)
- 共享磁盘系统：每个cpu拥有自己的私有内存，并通过连接网络直接访问所有磁盘。通过网络实现CPU之间的数据交换，增加了通信代价
  
  * 1553261696875.png
无共享资源系统(Shared Nothing)
- 每个cpu拥有自己的内存和磁盘空间，并无公共区域，cpu之间所有通信通过连接网络来完成。存在通信代价，非本地磁盘访问代价高
- 1553261771008.png
层次结构(Hierachical)
- 前三种体系的结合。分为两层，顶层是无共享结构，底层是共享内存或共享磁盘结构。集成了以上三种结构的优缺点
- 1553261831097.png

数据划分与并行算法

一维数据划分：将大数据集水平划分到多个磁盘上，可以通过并行读写有效地利用多磁盘的I/O带宽

轮转法——如果系统有n个cpu，将第i条记录划分到第i mod n 处理器的方法称为轮转划分方法。
散列法——使用特定的哈希函数，作用于选定的属性，将记录划分到不同的处理机。
范围划分法——首先对记录进行排序，然后按照排序码将其划分成n个区域，使每个区域中近似含有相同数目的记录，处于第i个区域的记录分布于处理机i

算法优势劣势

轮转法可有效应用于需要访问整个关系的查询处理，当需要访问部分记录时，散列法和范围更优。
范围法可能会导致数据偏斜，也就是不同分片含有的记录数目差别很大。数据偏斜会造成存有大片数据分片的处理机的性能瓶颈问题。
散列法优点是：即使数据随时间增加或减少，也能保持均匀分布。

多维数据划分

CMD多维划分法
BERD多维划分法
MAGIC多维划分法。

并行算法

并行排序
- 用区域划分法先将关系的所有记录重新分布再进行排序。
- 每个cpu使用排序算法对分配给它的记录排序。每个处理机得到分配给它的所有记录的有序序列。
- 通过按照区域划分的对应次序访问处理机得到完整的有序关系。
并行连接
- 假设：对关系A和B进行划分时，连接属性为age，关系初始分布在若干磁盘上，但不是基于连接属性分布的。
- 方法：对关系A和B重新划分：把连接属性age的取值分成k个区域

云计算数据库架构

云计算

云计算是一种商业计算模型。它将计算任务分布在大量计算机构成的资源池上，使各种应用系统能够根据需要获取计算力、存储空间和信息服务

1553262188822.png

云计算服务类型

1553262229664.png

云

云计算提供商的数据中心的软硬件设施

公共云：以即用即付的方式提供给公众。
私有云：不对公众开放的云。
混合云

云数据库体系结构

AWS（Amazon Web Services）
GAE（Google AppEngine）
Hadoop

云计算技术体系结构

[图片上传失败...(image-7182f3-1558763441920)]

谷歌云计算基础架构模式

Google File System文件系统
Map/Reduce分布式编程环境
Chubby分布式锁机制
BigTable大规模分布式数据库
- 表中的关键字可以是任意的字符串
- 列族是由列关键字组成的集合，是访问控制的基本单位
- 时间戳记录了BigTable是每一个数据项所包含的不同版本的数据的时间标识。在BigTable中，不仅可以随意地增减行的数量，在一定的约束条件下，还可以对列的数量进行扩展。

云数据库与传统数据库比较

数据安全问题
对云的管理问题
对因特网的依赖

XML数据库

概述

XML,可扩展标识语
XML数据库——支持对XML文档格式进行存储和查询等操作的数据库管理系统。

类型

XML Enabled Database(XEDB)——能处理XML的数据库。
Native XML Database(NXD)——纯XML数据库。
Hybird XML Database(HXD)——混合XML数据库

与传统数据库比较，XML数据库的优势

能够对半结构化数据进行处理。
提供对标签和路径的操作。
能清晰地表达数据的层次特征。

SQL Server 2008 与 XM

SQL Server中的XML语句

SELECT  * FROM  Table_cus 
WHERE  …. FOR XML RAW

SQL Server中的XML数据类型

CREATE  TABLE T_info
(
cid int ,
Content xml
)

数据仓库与数据挖掘

决策支持系统的发展

决策支持系统

一般将数据分为：
- 分析型数据
- 操作型数据
  - 由企业的基本业务系统产生的数据，用于联机处理环境（OLTP）
决策支持系统（DSS）：综合利用大量数据，有机组合众多模型（数学模型和数据处理模型），通过人机交互，辅助各级决策者实现科学决策的系统。

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

数据仓库是核心，操作型系统是数据源，决策支持系统是数据的需求者

[图片上传失败...(image-6b8064-1558763441920)]

数据仓库技术概述

数据仓库的概念与特性

建立数据仓库的目的

根据决策需求对企业的数据采取适当的手段进行集成，形成一个综合的、面向分析的数据环境，用于支持企业的信息型、决策型的分析应用。

数据仓库的特性

面向主题性
- 数据仓库中的数据是以面向主题的方式进行组织的。
- 主题也称为分析主题或分析领域，用于表示某一宏观的分析领域所涉及的对象，以及与对象有关的数据集合。
- 目前，数据仓库主题数据的实现采用关系型数据库技术
集成性
不可更新性
时间特性等

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

数据层次角度

操作型数据
操作型数据存储（Operation Data Store）
- ODS是能支持企业日常的全局应用的数据集合,是不同于DB的一种新的数据环境, 是DW 扩展后得到的一个混合形式。
- 四个基本特点：面向主题的( Subject –Oriented )、集成的、可变的、当前或接近当前的
- 可实行的全局应用
  - 实现企业全局的OLTP操作
    - ETL是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取、转换、加载至目的端的过程。采用数据抽取程序从OLTP系统中抽取数据的主要原因是解决不同类型应用程序之间的性能冲突问题
  - 实现即时的OLAP操作
- ODS分成4类
  - ODSⅠ：数据更新频率秒级。
  - ODSⅡ：数据更新频率小时级。
  - ODSⅢ：数据更新频率天级。
  - ODSⅣ：根据数据来源方向和类型区分
数据仓库（Data Warehouse）
数据集市（Data Mart）也可以包括个体层数据
- 小型的，面向部门或工作组级数据仓库

功能结构

数据处理
数据管理
数据应用

数据仓库的数据组织–-粒度

数据仓库的数据单位中保存数据的细化或综合程度的级别。细化程度越高，粒度越小
粒度影响到数据仓库的数据量及系统能回答的查询的类型。
进
行数据仓库的数据组织时，应根据当前应用的需求进行多粒度级设计。满足多角度、多层次数据查询要求

数据分区（分割）

数据分散到各自的物理单元中去，它们能独立地处理

最常见的是按照时间标准分区。
数据分区的方式可以分成系统层分区（数据库系统提供的机制）和应用层分区（由应用代码实现）两种。
如何分区由开发者和程序员控制

元数据

元数据描述了数据的结构、内容、链和索引等内容。
在数据仓库中，元数据分成技术型元数据和业务型元数据。
元数据解决了人在何时何地为了什么原因及如何使用数据仓库的问题。

设计与建造数据仓库

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

SDLC是典型的需求驱动开发生命周期，CLDS是典型的数据驱动开发生命周期。
- 传统的系统开发生命周期支持操作型环境，为建立系统，必须首先理解需求，然后进入到设计和开发阶段。
CLDS由数据开始，一旦数据到手，就集成数据，然后，如果数据有偏差，就检验看看数据存在什么偏差，再针对数据写程序，分析程序执行结果，最后，系统需求才得到理解
1553264662052.png
1553264682983.png

数据仓库的数据模型

概念（E-R图）、逻辑（关系型模型或多维数据模型）、物理三级数据模型。

数据仓库设计步骤

概念模型设计、技术评估与环境准备工作、逻辑模型设计、物理模型设计、数据生成与应用实现、数据仓库运行与维护

数据仓库的运行与维护

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

基本思路：
- 根据某种维护策略，在一定条件下触发维护操作；维护操作捕捉到数据源中的数据变化；通过一定策略对数据仓库中的数据进行相应的更新操作，以保持两者的一致性
维护策略
- 实时维护
  - 在数据源发生变化时，立即更新。
- 延时维护
  - 在数据仓库中的视图被查询时完成更新。
- 快照维护
  - 定期对数据仓库进行维护，触发条件是时间
捕捉数据源的变化
- 触发器
- 修改数据源应用程序
- 通过日志文件
- 快照比较法
导出数据的刷新
- 根据维护对象的数据源对其进行重新计算。
- 根据数据源的变化量在维护对象原有数据的基础上进行添加和修改，即增量式维护

数据仓库监控

对数据仓库的数据量、各种数据的使用率、用户和安全状况、网络通信情况、网络数据流量、数据源数据变化情况、集成和维护工具运行效率、查询响应时间、应用支持效率等进行必要的监控

元数据管理

一般采用元数据库来存储和管理元数据

联机分析处理与多维数据模型（OLAP）

OLAP简介

主要用于支持复杂的分析操作，侧重对决策人员和高层管理人员的决策支持。
特点
- 快速、可分析、多维

多维分析的基本概念

维——人们观察数据的角度。
数据的多维分析——针对数据仓库中以多维形式组织起来的数据，从多个角度、不同层次，采取各种数据分析技术，对数据进行剖析，以使用户能从多角度和不同层次观察和分析数据。

多维分析的基本操作

钻取与卷起
- OLAP分析最基本的操作。钻取指对应于某一维逐步向更细节层方向观察数据，卷起则反之。
切片和切块
- 实现局部数据的显示。
旋转
- 改变一个报告或页面显示的维方向，得到不同视角的数据。最简单的旋转就是数据交叉

OLAP的实现方式

基于多维数据库的OLAP（MOLAP）
- 以多维数组为基本存储结构。
基于关系数据库的OLAP（ROLAP）
- 采用关系表表示和存储。（星形模式或雪花模式）
混合型的OLAP（HOLAP）
- 结合MOLAP与ROLAP。具有最好的查询性能

数据挖掘技术

数据挖掘步骤

从人工智能机器学习中发展起来的。它研究各种方法和技术，从大量的数据中挖掘出有用的信息和知识。

数据准备

数据选取
数据预处理
数据变化。

数据挖掘

首先确定挖掘的任务，其次决定挖掘算法。

结果解释评估
剔除冗余或无关的模式

常见的数据挖掘任务

分类预测任务
- 常见的方法：决策树、神经网络、规则归纳、支持向量机、贝叶斯、粗糙集、回归分析、K-最近邻等。
描述型任务
- 典型的方法：聚类、关联、分析等

关联规则挖掘--“啤酒和尿布”

第一阶段必须先从资料集合中找出所有的高频项目组(Frequent Itemsets)，
第二阶段再由这些高频项目组中产生关联规则(Association Rules)。

分类挖掘

通过已知数据集，建立分类函数，构造分类器。
利用所获得的分类函数对未知类别标记的数据项进行分类操作

聚类挖掘

聚类挖掘方法——用于对集中的数据进行分组，使得每组内的数据尽量相似而不同组间的数据尽可能不同。

聚类方法
- 统计方法
- 机器学习方法
 - 通常，在训练有监督的机器学习模型的时候，会将数据划分为训练集、测试集和验证集合
- 神经网络方法和面向数据库的方法等

时间序列分析

时间序列——用时间排序的一组随机变量。
时间序列分析——又称为数据演变分析
- 描述行为随时间变化的对象的规律或趋势，并对其进行建模。
从不同角度可分为
- 一元时间序列和多元时间序列
- 等间隔时间序列和不等间隔时间序列
- 平稳时间序列和非平稳时间序列

数据库知识点点

数据库复习

数据库应用系统开发方法

数据库基本概念

数据

数据库（DB）

数据库系统（DBS）

数据库应用系统（DBAS）

软件工程

软件生存周期

软件工程与数据库技术

数据库工程

内容

典型的软件开发模型

DBAS生命周期模型

组成

项目规划

需求分析

系统设计

实现与部署

运行管理与维护

三条设计主线

数据组织与存储设计

数据访问与处理设计

应用设计

设计阶段

概念设计

逻辑设计

物理设计

需求分析

需求

需求分析

影响因素

需求获取的方法

需求分析方法

结构化分析与建模方法（SAD）

面向对象分析与建模方法(OOAD)

结构化分析任务

建立分析模型

编写需求规格说明书

结构化分析的指导思想

数据流图(DFD)

数据流

数据源

对数据的加工

数据存储

DFD建模过程

IDEF方法

UML用例建模方法

数据库结构设计

数据库概念设计

目标

概念设计的依据及过程

依据

结果

过程

概念模型设计(ER)

实体（Entity）或实例（Instance）

实体集（Entity Set）

属性（Attribute）

码（Key）

联系（Relationship）

类别

表示

数据库逻辑设计

任务

逻辑设计的依据和阶段目标

相关概念

关系模型

数据依赖

类型

完整性约束

候选码、主码、外码

范式

数据库逻辑设计方法

数据库物理设计

物理设计概述

目的

数据库的物理结构

内容