三级数据库技术笔记

数据库应用系统开发方法

数据库应用系统生命周期

软件工程与软件开发方法

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

DBAS生命周期

DBAS生命周期：项目规划、需求分析、系统设计、实现与部署、运行与维护

规划与分析

可行性分析：经济可行性、技术可行性、操作可行性、开发方案选择

需求分析

数据字典：数据项、数据结构、数据流、数据存储和处理

功能需求分析：数据处理需求分析、业务规则需求分析

性能需求分析：操作响应时间、系统吞吐量、允许并发访问最大用户、每TPS代价值

其他需求分析：存储需求、安全性需求、备份和恢复需求

系统设计

概念设计：数据库概念模型设计（需求规格说明书）、系统总体设计

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

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

实现与部署

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

系统部署

运行管理与维护

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

第二章需求分析

需求分析

需求获取的方法

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

需求分析过程

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

需求分析方法

DFD需求建模方法

数据流

处理

处理序号

处理名称

数据存储

存储名称

序号

外部顶

数据源/

终点名称

IDEF0需求建模方法

IDEFO图基本元素是矩形和箭头，矩形框代表功能活动

活动名称

活动编号

控制

机制

输出

输入

UML用例模型

UML方法采用面向对象思想建模，由用例图组成。

用例图由系统、角色、用例三种模型元素及关系构成。

第三章 数据库结构设计

数据库概念设计

明确建模目标 定义实体集 定义联系 建立信息模型 确定实体集属性 对信息模型进行集成与优化

ER建模方法

实体或实例：客观存在并可互相区分的事务

实体集：同型实体的集合

属性：实体所具有的某一个特性，一个实体可由若干个属性刻画，每个属性的取值范围为域

码：实体集中唯一标识每一个实体的属性或实体组合，一个实体集中任意两个实体在主码上的取值不能相同

联系：描述实体之间的相互关系，可以有属性；一对一，一对多，多对多

ER图表示方法

实体集：矩形框

联系：菱形框

属性：椭圆或圆角矩形

数据库逻辑设计

函数依赖

R(U,D,DOM,F)

R：关系名

U：组成该关系的属性名集合

D：属性组U中的域

DOM：属性到域的映射

F：属性组U上的一组数据依赖

R<U,F> 当且仅当U上的一关系R满足

数据依赖：关系内部属性与属性之间的一种约束关系

完整性约束的表现形式： X ---> Y

多值依赖：函数是唯一确定的关系，多值依赖却不能唯一确定

非平凡函数依赖：如果 x-->y ，且y ⊈x

平凡函数依赖：如果x-->y,且y⊆x

例：SC(Sno,Cno,Grade)

非平凡函数依赖：（Sno,Cno）-->Grade

平凡函数依赖：（Sno,Cno）-->Sno , （Sno,Cno）-->Cno

f：完全函数依赖 p：部分函数依赖

数据库完整性约束条件

对列的约束：对其值的类型、范围、精度、排序等约束

对元组的约束：对记录中各个属性之间的联系约束

对关系的约束：对若干个记录之间（一个关系的各个元组之间）、关系集合以及之间联系的约束

高级数据查询

一般数据查询功能扩展

使用TOP限制结果集

语法格式：TOP n [percent] [WITH TIES]

①TOP n:取查询结果的前n行数据；

②TOP n percent:取查询结果的前n%行数据；

③WITH TIES：表示包括最后一行取值并列的结果

注意：用TOP谓语，与ORDER BY子句一起使用

用WITH TIES选项，必须使用ORDER BY子句

使用CASE函数

简单CASE函数

语法格式： CASE 测试表达式

WHEN 简单表达式1 THEN结果表达式1

WHEN 简单表达式2 THEN结果表达式2

......

WHEN 简单表达式n THEN结果表达式n

[ELSE 结果表达式n+1]

END

搜索CASE函数

语法格式： CASE

WHEN 布尔表达式1 THEN结果表达式1

WHEN 布尔表达式2 THEN结果表达式2

......

WHEN 布尔表达式n THEN结果表达式n

[ELSE 布尔表达式n+1]

END

将查询结果保存到新表中

语法格式： SELECT 查询列表序列 INTO [# / ##] <新表名>

FROM 数据源

... ——其他行过滤、分组等子句

功能：

①查询语句列出的列以及其类型创建一个新表

②执行查询语句

③将查询结果插入新表

局部临时表标识“#”，全局临时表标识“##”。

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

并运算 UNION

语法格式： SELECT 语句1

UNION [ALL]

SELECT 语句2

UNION [ALL]

...

SELECT 语句n

JION是水平合并数据（添加更多的列），UNION是垂直地合并数据（添加更多的行）

注意：

①列的个数，语义相同

②列的数据类型与其他列的数据类型隐式兼容

③合并后采用第一个SELECT语句列标题

④结果进行排序，ORDER BY写在最后一个查询语句

交运算 INTERSECT

语法格式： SELECT 语句1

INTERSECT

SELECT 语句2

INTERSECT

...

SELECT 语句n

交运算返回两个查询结果集中各个列的值均相同，并用这些记录构成交运算的结果

差运算 EXCEPT

语法格式： SELECT 语句1

EXCEPT

SELECT 语句2

EXCEPT

...

SELECT 语句n

差运算将返回在第一个集合中有但第二个集合中没有的数据

相关子查询

子查询进行基于集合的测试 [NOT] IN

WHERE表达式[NOT] IN（子查询）

先执行子查询，然后在子查询的结果基础上再执行外层查询

子查询进行比较测试

WHERE表达式 比较运算符（子查询）

比较运算符：=、<>、<、>、<=、>=

运算结果为True，则返回True；运算结果为False，则为False。

注意：子查询先得到聚合函数的结果，再执行外层查询的比较。

子查询进行存在性测试 [NOT] EXISTS

WHERE [NOT] EXISTS（子查询）

注意：①先执行外层查询，再执行内层查询。

②只返回真或假值，子查询目标列通常用“*”

其他形式的子查询

替代表达式的子查询

在SELECT语句的选择列表中嵌入一个只返回一个标量值的语句

例：查询G001顾客姓名、地址以及顾客购买商品的次数

SELECT Cname,Address

(SELECT COUNT(*) FROM Table_SaleBill a

JION Table_Customer b ON a.CardID=b.CardID

WHERE CustomerID=’G001’) AS Totaltimes

FROM Table_Customer

WHERE CustomerID=’G001’

派生表

将子查询作为一个表来处理；简化查询，避免使用临时表

SELECT * FROM（SELECT * FROM T1） AS temp

其他一些查询功能

开窗函数

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

语法格式： <OVER_CLAUSE> : : =

OVER ( [PARTITION BY value_expression] )

PARTITION BY :将结果集划分为多个分区

value_expression：指定对行集进行分区所依据的列

例：SELECT Cno,Semester,Credit,

SUM(Credit) OVER (PARTITION BY Semester) AS ‘Total’

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

语法格式： RANK () OVER ( [ < partition_by_clause > ]

< order_by_clause> )

partition_by_clause :将FROM子句生成的结果集划分成排名函数使用的分区

order_by_clause ：指定应用于分区中的行时所基于的排序依据列

每行数据在每个分区内的排名；若值有相同的行，则值相同的行具有相同排名

DENSE——RANk（）：排名中间没有任何间断，该函数返回的是一整个连续的值

NTILE：有序分区中的行划分到指定数目的组中

ROW_NUMBER():返回结果集中每个分区内行的序列号

例：SELECT 列名 RANK() OVER

(PARTITION BY 列名 ORDER BY 列名 [DESC | ASC ] )

FROM 表名 ORDER BY 列名

公用表达式

将查询语句产生的结果集指定一个临时命名的名字

好处：①定义递归公用表达式

②操作数据代码清晰简洁

③GROUP BY子句作用在子查询的标量列上

④在一个语句中多次引用共用表达式

语法格式： WITH <common_table_expression> [...n]

<common_table_expression>: :=

expression_name [ ( column_name [...n] ) ]

AS

(SELECT 语句)

expression_name :公用表达式标识符

column_name：公用表达式指定列名

SELECT 语句：指定一个用其结果集填充到公用表达式的SELECT语句

第七章数据库及数据库对象

创建及维护数据库

SQL Server数据库概述

master：记录实例的所有系统级信息，包括实例范围。

msdb：代理服务调度报警和作业以及记录操作员时使用

model：实例上创建的所有数据库模板

tempdb：临时数据库

resource：：只读数据库

数据库的组成

数据文件包含数据和对象，日志文件包含恢复数据库中的所有事务需要的信息

数据文件

主要数据文件：扩展名 .mdf 只能有一个主要数据文件

次要数据文件：扩展名 .ndf 可以不包含次要数据文件，也可以包含多个次要数据文件

可以建立在一个磁盘上，也可以分别建立在不同磁盘上

在主要数据文件建立之后建立的所有文件都是次要数据文件

事务日志文件

扩展名 .ldf 存放恢复数据库的所有日志信息，必须至少一个日志文件， 也可以多个日志文件

数据存储空间的分配

①数据的分配单位是数据页，一页是一块8KB（8×1024B，用8060B存放数据，另外132B存放信息）的连续磁盘

②不能跨页存储，表中一行数据大小不能超过8060B

③创建用户数据库时，model数据库自动复制到新建数据库中，且复制到主数据文件中

数据库文件组

主文件组：包含主要数据文件和其他数据文件，系统所有页均分配在主文件组中

用户定义文件组：用户可以创建自己的文件组，将数据库文件组织起来，便于管理和数据分配

①日志文件不包括在文件组内，日志空间与数据空间分开管理

②一个文件可以是多个文件组的成员

③文件组中有多个文件，在所有文件被填满之前不会自动增长，填满后文件会循环增长

数据库文件属性

文件名及其位置：具有一个逻辑文件名和物理文件名，逻辑文件名必须是唯一。

多个数据文件，建议将文件分散存储在多个物理磁盘上

初始大小：指定主要数据文件的初始大小时，其大小不能小于model数据库

主要数据文件的大小

增长方式：可指定文件是否自动增，默认配置为自动增长

最大大小：文件增长的最大空间限制，默认是无限制

用T-SQL语句创建数据库

语法格式： 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 [ KB | 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:新数据库名

ON：指定数据文件

PRIMARY：指定关联数据文件的主文件组

LOG ON：指定日志文件

<filespec>：定义文件属性

NAME=logical_file_name：指定文件的逻辑名称

FILENAME= ‘ os_file_name’：指定操作系统（物理）文件名

SIZE = size：指定文件的初始大小

MAXSIZE = max_size ：指定文件可增大最大大小

UNLIMITED：指定文件的增长无限制

FILEGROWTH= growth_increment：指定文件的自动增量

growth_increment ：每次文件添加的空间量

< filegroup > :文件组属性

FILEGROUP filegroup_name：文件组的逻辑名称

修改数据库

扩大数据空间

语法格式： 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 }：将指定文件添加到文件组

<filespec>：文件属性

ADD LOG FILE：在数据库中添加新的日志文件

MODIFY FILE：指定要修改的文件

收缩数据空间

收缩整个数据库大小

语法格式： DBCC SHRINKDATABASE

（‘ database_name’ | database_id | 0 ‘）

[ ,target_percent ]

[ ,{ NOTRUNCATE | TRUNCATEONLY ]

)

‘database_name’ | database_id | 0 :要收缩的数据库名称和ID；指定0，则表示收缩当前正在使用的数据库

target_percent：数据库收缩后文件所需的剩余空间百分比

NOTRUNCATE：数据库文件保留释放的文件空间；若未指定，则释放给操作系统

TRUNCATEONLY：将文件中任何未使用的空间均释放给操作系统，收缩到最后分配大小

收缩指定文件大小

语法格式： DBCC SHRINKDATABASE

( ‘file_name’

{ [ ,EMPTYFILE ]

| [ [ ,target_size ] [ , [ {NOTRUNCATE | TRUNCATEONLY } ] ]

}

)

file_name:要收缩的文件逻辑名

target_size：指定收缩后目标文件大小

EMPTYFILE：指定将文件中的所有数据迁移到同一个文件组的其他文件中

NOTRUNCATE：数据库文件保留释放的文件空间；若未指定，则释放给操作系统

TRUNCATEONLY：将文件中任何未使用的空间均释放给操作系统，收缩到最后分配大小

添加和删除数据库文件

语法格式： ALTER DATABASE database_name

REMOVE FILE logical_file_name

database_name：要删除的数据库名

logical_file_name：被删除文件的逻辑名

分离和附加数据库

分离数据库

语法格式： sp_detach_db [ @ dbname= ] ‘ dbname ’

[ , [ @ skipchecks= ] ‘ skipchecks ’

[ @ dbname= ] ‘ dbname ’ ：要分离的数据库名称

[ @ skipchecks= ] ‘ skipchecks ’ ：指定跳过还是运行 “更新统计信息”；

若跳过，则true；若显示运行，则false

附加数据库

语法格式： CREATE DATABASE database_name

ON <filespec> [ , ...n]

FOR { ATTACH | ATTACH_REBUILD_LOG }

database_name：要附加的数据库名称

<filespec> ：指定要附加的数据库的主要数据文件

FOR ATTACH_REBUILD_LOG：指定附加现有的操作系统文件创建数据库

只限于读/写的数据库；若缺少日志文件，重新生成

架构

定义架构

架构：数据库下的逻辑命名空间，可存放数据库对象

一个数据库可以包含一个或多个架构，由特定的授权用户拥有。

同一数据库中架构名必须唯一；一个架构由零个或多个架构对象组成。

定义架构可同时定义表TABLE、视图VIEW、用户授权GRANT

语法格式： CREATE SCHEMA [ <架构名> ] AUTHORIZATION <用户名>

若未指定<架构名>，则<架构名>隐含为<用户名>

删除架构

语法格式： DROP SCHEMA <架构名> { <CASCADE> | <RESTRICT> }

CASCADE：删除架构的同时将该架构中的所有架构对象一起全部删除

RESTRICT：如果被删除的架构中包含架构对象，则拒绝删除此架构

批处理语句

BEGIN TRANSACTION ....... COMMIT TRANSACTION

分区表

创建分区函数

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：提供值的数目，n≦999

LEFT | RIGHT：指定间隔值由数据库引擎按升序从左到右排序；若未指定，则默认为LEFT

说明：

①分区函数的作用域仅限于创建该分区的数据库

②分区列空值的所有行在最左侧；最左侧分区为空分区，NULL值被放置在后面分区中

创建分区方案

SQL语句： CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ , ...n ] )

[ ; ]

partition_scheme_name：分区方案名

partition_function_name：分区函数名

ALL：指定所有分区映射到file_group_name中提供的文件组

{ file_group_name | [ PRIMARY ] } [ , ...n ]：指定分区的文件组名

索引

创建索引

语法格式： CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX INDEX_NAME

ON <object> (column [ASC | DESC ] [ , ...n ] )

[ INCLUDE ( column_name [ , ...n ] ) ]

[ WHERE <file_predicate> ]

[ ON { partition_scheme_name ( column_name )

| filegroup_name

| default

}

]

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | “NULL” } ]

[ ; ]

<object> : :=

{

[database_name. [ schema_name ]. | schema_name. ] table_or_view_name

}

UNIQUE:为表或视图创建唯一索引

CLUSTERED:创建聚集索引

NONCLUSTERED:默认选项，创建一个非聚集索引

Index_name：索引名

Column：索引所基于的一个列或多列

[ ASC | DESC ]：ASC为升序，DESC为降序

INCLUDE ( column [ , ...n ] )：指定要添加非聚集索引的叶级别的非键列。

WHERE< filter_predicate >：指定筛选条件后系统满足筛选条件的数据行上建立索引

ON partition_scheme_name (column_name)：指定分区方案

ON filegroup_name：指定文件组索引

ON “ default”：默认文件组索引

删除索引

语法格式： DROP INDEX { index_name ON <object> [, ...n] }

{

[database_name. [ schema_name ]. | schema_name. ] table_or_view_name

}

视图索引

适合建立视图索引的场合：①很少更新基础数据

②以批处理的形式更新，只读数据进行处理

提高性能：①处理大量行的连接和聚合

②许多查询经常执行的连接和聚合操作

不能提高性能：①具有大量写操作的OLTP系统

②具有大量更新操作的数据库

③不涉及聚合或连接的查询

④GROUP BY列具有高基数度的数据聚合

定义索引视图：①视图只能引用基本表

②基本表与视图位于同一个数据库

③必须使用SCHEMABINDING选项创建视图

④引用的函数必须正确

⑤第一个索引必须是唯一聚集索引

第八章数据库后台编程技术

存储过程

存储过程的好处：①允许模块程序化设计

②改善性能

③减少网络流量

④增强应用程序安全设计

创建存储过程

语法格式： 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：数据库引擎不缓存该计划，在运行时被重新编译

执行存储过程

语法格式： [ { EXEC | EXECUTE } ]

{

[ @return_status= }

{ proc_name }

[ [ @parameter_name= ] { value

| @variable [ OUTPUT ]

| [DEFAULT ]

}

]

[ , ...n ]

[ WITH RECOMPILE ]

}

[ ; ]

return_status：可选的整型标量，存储过程的返回状态

proc_name：要调用的存储过程名

@variable：用来存储参数或返回参数的变量

Parameter：存储过程的参数，必须与存储过程中定义的相同

删除存储过程

语法格式： DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ , ...n ]

用户定义函数

创建和调用标量函数

语法格式： 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：参数的数据类型及其所属的架构

return_data_type：用户定义函数返回的标量值

scalar_expression：指定标量函数返回的标量值

调用标量函数： 例 SELECT dbo.CubicVolume(4,6,8)

创建和调用内联表值函数

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

语法格式： CREATE FUNCTION [ schema_name. ] function_name

( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

[ =default ] }

[ , ...n ]

]

)

RETURNS TABLE

[ AS ]

RETIURN [ ( ] select_stmt [ ) ]

[ ; ]

调用内联表值函数：SELECT * FROM dbo.f_GoodsInfo(‘服装’)

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

语法格式： 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：定义返回表的结构

调用多语句表值函数：例 SELECT * FROM dbo.f_GoodsType (‘家用电器’)

触发器

触发器场合：①完成比CHECK约束更复杂的数据约束

②为保证数据库性能而维护的非规范化数据

③可实现复杂的商业规则

④可以评估数据修改前后的表状态，并根据其差异采取对策

语法格式： 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：关联的表或视图

FOR | AFTER ：不能在视图上定义AFTER触发器

INSTEAD OF ：指定执行触发器

INSERT UPDATE DELETE ：引发触发器执行的操作

DELETED表 ：用于存储DELETE和UPDATE语句所影响的行复本

INSERTED表 ：用于存储INSERT和UPDATE语句所影响的行复本

UPDATE ：类似于在删除之后执行插入

创建后触发型触发器

使用FOR或AFTER选项定义的触发器为后触发型触发器

只有在引发触发器执行的语句中的操作已成功执行，且所有约束检查成功后，才执行触发器

创建前触发型触发器

使用INSTEAD OF选项定义的触发器以前触发型触发器

指定执行触发器而不是执行引发触发器执行的SQL语句，从而代替引发语句的操作】

删除触发器

DROP TRIGGER schema_name. Trigger_name [ , ...n ] [ ; ]

游标

声明游标：

语法格式：DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [, ...n ] ] } ]

cursor_name：服务器游标名

INSENSITIVE：定义一个游标，创建将由该游标使用的数据临时复本

SCROLL：指定的所有提取项

select_statement：定义游标结果集的标准SELECT语句

READ ONLY：禁止通过该游标更新数据

UPDATE：定义游标中可更新的列、

打开游标：

OPEN cursor_name

Cursor_name:游标名

提取数据：

语法格式： FETCH [ [ NEXT | PRIOR | FIRSE | LAST

| ABSOLUTE n

| RELATIVE n ]

FROM

]

cursor_name [ INTO @variable_name [ , ...n ]

NEXT：返回紧跟在当前行之后的数据行，并且当前行递增为结果行

PRIOR：返回紧临当前行前面的数据行，并且当前行递减为结果行

FIRST:返回游标中的第一行将其作为行

LAST：返回游标中的最后一行将其作为行

ABSOLUTE：n必须为整型常量

RELATIVE：n必须为整型常量

cursor_name：从中进行提取数据的游标名

INTO @variable_name：将提取的列数据保存到局部变量中

@@FETCH_STATUS：返回的数据类型是int

关闭游标：

CLOSE cursor_name

释放游标：

DEALLOCATE cursor_name

第九章安全管理

安全控制概述

安全性：保护数据以防止不合法用户故意造成破坏

完整性：保护数据以防止合法用户无意中造成破坏

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

数据库安全威胁：可用性损失、机密性 私密性数据损失、偷窃和欺诈、意外损害

安全控制：身份验证——操作权限控制——文件操作控制——加密存储与冗余

认证：鉴定用户身份的机制

存取控制

自主存取控制

权限种类：维护权限、操作权限（语句、对象权限）、隐含权限

用户分类：系统管理员、数据库对象拥有者、普通用户

强制存取控制

主体：活动实体 许可证级别 客体：被动实体 密级

敏感度标记（Label）：绝密（Top Secret）、秘密（Secret）、可信（Confidential）、公开（Public）

仅当主体许可证级别大于或等于客体密级，主体可以读取相应客体

仅当主体许可证级别等于客体密级，该主体才能写相应客体。

安全性分级模式：D类最小保护、C类自主保护、B类强制保护、A类验证保护

SQL Server安全控制

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

登陆账户：自身负责身份验证、Windows网络（组/用户）

只能连接SQL Server数据库服务器，并不具有访问任何用户数据库权限

建立登录账户：CREATE LOGIN login_name WITH PASSWORD=’****’

| FROM (域) | MUST_CHANGE (必须更改密码)

修改登录帐户属性：ALTER LOGIN login_name WITH PASSWORD=’****’ (修改密码)

| ENABLE (启用) | WITH NAME=*** (改名)

删除登录帐户：DROP LOGIN login_name

数据库用户：默认情况，新建立的数据库只有一个用户：database owner，数据库的拥有者

登录账户成为数据库用户的操作称为“映射”

每个数据库中均已存在guest用户，默认禁用状态，授予CONNECT启用

先删除或转移安全对象的所有者，再删除拥有这些对象的数据库用户

创建数据库用户：CREATE USER user_name [|FOR|FROM]

LOGIN login_name

Guest用户： GRANT CONNECT TO guest 启用

REVOKE CONNECT TO guest 禁用

删除数据库用户：DROP USER user_name

权限管理

对象级别权限：SELECT、INSERT、UPDATE、DELETE、REFERENCES、EXECUTE

语句级别权限：CREATE DATABASE、PROCEDURE、TABLE、VIEW、FUNCTION、

BACKUP DATABASE、LOG

语句权限：授予语句级别权限使用GRANT语句

拒绝权限：拒绝用户具有某权限使用DENY语句

收权语句：将已授权用户权限收回来，语句使用REVOKE语句

角色

一组具有相同权限的用户就是角色；角色分为预定义的系统角色和用户角色

固定服务器角色：作用域属于服务器范围，可以将登录账户添加到固定服务器角色

Bulkadmin：执行BULK INSERT语句权限。

Dbcreator：创建、修改、删除还原数据库权限。

Diskadmin：具有管理磁盘文件的权限

Processadmin管理运行进程权限。

Securtyadmin：专门管理登录账户、读取错误日志执行CREATE DATABASE 权限的账户，便捷。

Serveradmin：服务器级别的配置选项和关闭服务器权限。

Setupadmin：添加删除链接服务器。

Sysadmin：系统管理员 ，Windows超级用户自动映射为系统管理员。

Public：系统预定义服务器角色，每个登录名都是这个角色的成员。没有授予或拒绝特定权限，则将具有这个角色权限。

添加成员：EXEC Sp_addsrvrolemember ‘user1’ (登录名)，‘sysadmin’(角色名)

删除成员：EXEC Sp_dropsrvrolemember ‘user1’，‘sysadmin’

固定数据库角色：存在于每个数据库中，具有数据库角色权限

Db_accessadmin:添加或删除数据库权限

Db_backupoperator：备份数据库、日志权限

Db_datareader:查询数据库数据权限

Db_datawriter：具有插入、删除、更改权限

Db_ddladmin：执行数据定义的权限

Db_denydatareader：不允许具有查询数据库中所有用户数据的权限。

Db_denydatawriter：不允许具有插入、删除、更改数据库中所有用户数据权限。

Db_owner：具有全部操作权限，包括配置、维护、删除数据库。

Db_securityadmin：具有管理数据库角色、角色成员以及数据库中语句和对象的权限。

用户定义角色：数据库一级角色，可以是用户定义角色或数据库用户

创建用户定义的角色：CREATE ROLE MathDept [AUTHORIZATION (拥有者) Software]

删除用户定义角色：DROP ROLE MathDept

注意： 角色中的成员拥有的权限=成员自身权限+所在角色权限

但若某个权限在角色中被拒绝，则成员不再拥有。

数据库运行维护与优化

数据库运行维护基本工作

数据库的转储与恢复

数据库的安全性、完整性控制

检测并改善数据库的性能

数据库的重组和重构

重组：不修改数据库原有设计的逻辑和物理结构

重构：部分修改数据库的模式和内模式

运行状态监控与分析

自主监控机制 手动监控机制

对数据库构架体系的监控：空间使用率与剩余大小的空间、空间扩展

对数据库性能的监控：数据缓冲区的命中率、用户锁、回滚段、临时段、索引、事件

数据库存储空间管理

空间使用情况问题：降低数据库系统服务性能 空间溢出导致灾难停机事故

DBMS对空间的管理包括：创建数据库空间、更改空间大小、删除空间、修改空间状态，新建、移动、关联数据文件等。

数据库性能优化

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

外部调整：CPU性能和网络传输

调整内存分配：调整相关参数控制数据库的内存分配

调整磁盘I/O：性能优劣度量是时间；令I/O时间最小化，减少磁盘文件竞争

调整竞争：①修改参数以控制连接到数据库的最大进程

②减少调度进程竞争

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

④减少重做日志缓冲竞争

⑤减少回滚段竞争

模式调整与优化

规范化过程：高效率利用存储空间，减少数据冗余，减少数据的不一致

反规范化：将规范化关系转换成非规范化的关系的过程；破坏数据完整性

增加派生性冗余列：增加的列由表中的一些数据项经过计算生成

提高查询统计速度，空间换时间；减少连接操作，避免使用聚合函数

增加冗余列：在多个表中增加具有相同语义的列；避免连接操作。

重新组表：用户查看的某些数据由多个表连接之后得到，把这些数据重组成一个表

分割表：①水平分割：表结构相同，存储数据不同，需要union操作

②垂直分割：除了主码外其他列不相同，常用列与不常用列分别放在不同表中

查询减少I/O次数，缺点是使用连接（Join）操作

新增汇总表：频繁使用的统计操作的中间结果或最终结果存储在汇总表

降低数据访问量和汇总操作的CPU计算量

存储优化

物化视图：包括一个查询结果的数据库对象，预先计算并保存耗时较多的操作结果

可以进行远程数据的本地复制

聚集：物理存储表中数据的可选择的方法；可最小化必须执行的I/O次数

聚集表的插入、更新、删除性能差

查询优化：

1.合理使用索引：索引提高查询效率，增加系统开销

建立原则：是否为一个属性建索引

在哪些属性建立索引

是否建立聚簇索引

使用散列还是树索引

使用原则：经常在查询中作为条件被使用的列

频繁进行排序或分组的列

一个列的值域很大时

待排列的列有多个

数据表更新大量数据后，删除并重建索引

调整和修改原因：缺少索引，某些查询语句执行时间过长

索引没有使用，占用较多磁盘空间

索引建立在被频繁改变的属性上，导致系统开销过大

1. 避免或简化排序

影响：现有索引不足，导致排序索引中不包括一个或几个待排序的列

Group by和Order by子句中列的次序与索引次序不一致

排列的列来自不同的表

1. 消除对大型表数据的顺序存取：对连接列进行索引，或使用并集来避免顺序存取。
2. 避免复杂正则表达式：消耗较多CPU资源进行字符串匹配
3. 使用临时表加速查询：将表的一个子集进行排序并创建临时表
4. 用排序来取代非顺序磁盘存取：非顺序磁盘存取最慢

7.不充分的连接条件：左（右）外连接包含与NULL数据匹配，相比内连接，代价可能很高。

8.存储过程：尽量使用自带返回参数，减少不必要参数，避免数据冗余

9.不要随意使用游标：占用较多系统资源

1. 事务处理：将频繁操作的多个可分割的处理过程放入多个存储过程中

SQL Server性能工具

SQL Server Profiler：监视SQL Server事件的工具，存储在跟踪文件中，分析文件诊断问题

数据库引擎优化顾问：测试数据库工作负荷，给出优化建议。

第十一章故障管理

故障管理概述

事务内部故障

预期：事务程序本身发现的事务内部故障，事务回滚

非预期：事物内部故障不能由事务程序处理

系统（软）故障

由于硬件故障、突然停电等情况，导致系统停止运行

需要系统重新启动；回滚未完成的事务，重做已提交的事务

介质（硬）故障

由于磁盘损坏、强磁干扰等情况，导致数据部分丢失或全部丢失

故障发生可能性小，但破坏大

软件容错：备份与日志文件，只能恢复到备份数据库后的某个时间点

硬件容错：双物理存储设备，达到数据库完全恢复效果

计算机病毒故障

防火墙、杀毒软件、数据库备份文件

数据库技术恢复概述

把数据库从错误状态恢复到某一已知的正确状态

在DBMS中，数据库恢复子系统占10%以上

恢复机制涉及两个问题：建立冗余数据

利用这些冗余数据实施数据库恢复

数据转储

定期复制数据库，并将数据存放其他介质中，被称为后援副本或后备副本

静态转储和动态转储

静态转储：不能运行其他事务；转储前后系统必须处于一致的状态

保证数据有效性，但降低数据库可用性

动态转储：允许转储操作和用户事务并发进行；在数据库进行存取和修改操作

提高数据库可用性，但数据有效性不能保证

动态转储+日志文件：记录转储期间各事务对数据库的修改活动记录

保证数据一致性，提高数据库可用性

数据转储机制

完全转储：对数据库所有数据进行转储

增量转储：只复制上次转储之后发生变化的文件或数据块

差量转储：对近一次数据库完全转储发生的数据变化进行转储

仅使用完全转储：大量数据移动，占用时间和空间

完全转储加增量转储：每隔一段时间进行一次完全转储，在其中间执行多次增量转储

避免大量数据移动，恢复时间长，转储出问题导致失败

完全转储加差量转储：操作简单、时间比较短

日志文件

日志文件概念

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

在动态转储方式中必须建立日志文件

在静态转储方式中也可使用日志文件

故障恢复的操作：①UNDO () 撤销事务

②REDO () 重做事务

日志文件的格式与内容

①以记录为单位的的日志文件

开始标记：BEGIN TRANSACTION

结束标记：COMMIT OR ROLLBACK

各个事务的所有更新操作

②以数据块为单位的日志文件

事务标识和被更新的数据块

日志记录：开始标记、结束标记、更新操作

登记日志文件原则

①登记的次序严格按并行事务的执行时间次序

②必须先写日志文件，后写数据库

检查点

作用：最大限度的减少数据库完全恢复时必须执行的日志部分

检查点的引入

①检查点记录的内容

②重新开始文件记录的内容

③动态维护日志文件的方法：建立检查点，保存数据库状态

硬件容错方案

相关度最紧密的技术：数据库存储保护技术、服务器容错技术、数据库镜像与容灾技术

磁盘保护技术

RAID:廉价冗余磁盘阵列，多块磁盘构成一个整体

镜像冗余：把所有数据复制到其他设备上或其他地方；开销大

校验冗余：对成员磁盘上的数据执行异或（XOR）操作，其校验值放在另外校验磁盘

RAID-0：将多个磁盘合并一个大的磁盘，不具有冗余，并行I/O，速度最快

RAID-1：两组以上的N个磁盘相互作镜像；并行传输，提高读速度，加强可靠性

RAID-5：把分块数据和奇偶校验信息写入硬盘阵列；至少需要三颗硬盘

RAID-10：RAID0与RAID-1的组合体；前者的快速，后者的安全，冗余度为50%

服务器容错技术

引入服务器容错原因：解决服务器硬件异常问题

服务器容错技术简介：采用两台相同的服务器，共享存储设备

双机热备，Active-Standby

两台服务器之间会有私有网络进行心跳检测

其他服务器容错技术 ：硬件、软件级别

数据库镜像与数据库容灾

数据库镜像分类：①双机互备援模式；均为工作机，互相监视对方

②双机热备份模式：工作机和备份机，备份机监视工作机

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

实现方式：①高可用性：同步事务写入，自动错误恢复

②高保护性：同步事务写入，手动错误恢复

③高性能：不同步写入，手动错误恢复

第十二章备份与恢复数据库

备份与恢复概念

造成数据丢失的原因：存储介质故障、用户的操作失误、服务器故障、病毒侵害、自然灾害

恢复数据库：①介质故障恢复：还原最近的数据库副本并利用备份日志重做已提交的操作

②非介质故障,在数据库系统重启之后，进行REOD和UNDO操作

备份与恢复机制

简单恢复模式：简略地记录大多数事务，不备份事务日志 SIMPLE

减少事务日志的管理开销，只能恢复到最新备份状态

完整恢复模式：完整地记录了所有的事务，并保留所有的事务日志记录 FULL

支持还原单个数据页

大容量日志恢复模式：只对大容量操作进行最小记录 BULK_LOGGED

提供最佳性能并占用最小日志空间 不支持时间点恢复

备份内容及时间：用户数据：周期性备份

系统数据：修改之后进行备份

立刻备份：创建数据库、索引之后，清理事务日志、大容量数据操作之后

备份机制

备份方式：①永久备份设备：先建立备份设备，再将数据库被分到备份设备

②临时备份设备：直接将数据库备份到物理文件上

远程备份：启动数据库引擎时对远程计算机有写的权限

备份类型：①数据库备份：完整数据库备份、差异数据库备份

②文件备份：文件备份、差异文件备份

③事务日志备份：纯日志备份，大容量操作日志备份，结尾日志备份

备份策略：

完整备份：数据库数据不大，更改不频繁

完整备份 + 日志备份：不允许丢失太多数据，不希望经常完整备份

完整备份 + 差异备份 + 日志备份：备份和恢复速度快、数据丢失少

实现备份：

BACKUP DATABASE | LOG database_name

FILEFROUP=’ ***’ (辅助文件组)

TO 备份设备名 | {DISK |TAPE} (磁盘 磁带)

WITH INIT (覆盖) | NOINIT (追加) | DIFFERENTIAL (差异数据库备份)

恢复机制

数据库：数据库完整还原

数据文件：文件还原

还原顺序：恢复最近的完全备份

恢复最近的差异备份

恢复自差异备份之后的所有日志备份

恢复数据库

实现还原：RESTORE DATABASE | LOG database_name

FROM 备份设备名

WITH FILE | RECOVER | NORECOVER | STANDBY

第十三章大规模数据库架构

分布式数据库

分布式数据库系统：数据分布在物理位置不同的计算机，由通信网络连接

场地既能独立处理，也可和其他场地协同工作

物理上分散、逻辑上集中；

分布式数据库：分布式数据库系统中各个场地上数据库的逻辑集合

分布式数据库目标：本地自治、非集中式管理、高可用性 （最基本特征）

位置独立性、数据分片独立性数据复制独立性 （分布透明性）

分布式查询处理、分布式事务管理 （复杂性）

硬件独立性、操作系统独立性、网络独立性、数据库管理系统独立性

最基本特征：本地自治、非集中式管理、高可用性

分布透明性：位置独立性、、数据分片独立性、数据复制独立性

数据分布策略

数据分片：①水平分片；数据行的子集合，每一行至少属于一个片段

②垂直分片；数据列的子集合，各片段包含关系主码属性

③导出分片；导出水平分片

④混合分片：以上三中的混合

数据分配：①集中式；所有数据片段都在安排在一个场地上

②分割式：被分割若干份，每个片段被分配在特定场地

③全复制式：有多个副本，每个场地有一个完整数据副本

④混合式：被分若干个数据子集，子集被安排一个或多个不同场地

分布式数据库的参考模式架构

外顶层
全局外模式：全局应用的用户视图

中间层

全局概念模式：描述全体数据的逻辑结构和特征

分片模式：全局数据的逻辑划分视图

分配模式：片段到物理存放地的映像

底层

局部概念模式：物理片段的逻辑结构和特征

局部内模式：局部概念涉及本场地的物理存储

分布透明性

分片透明性：最高级别透明性，无需考虑数据分片（全局概念与分片模式）

位置透明性：只需考虑数据分片情况，无需考虑数据分片位置

局部数据模型透明性：了解全局数据、副本复制及位置分配（分配与局部概念）

分布式数据库管理系统

全局数据库管理系统 GDBMS 核心

全局数据字典 GDD

局部数据库管理系统 LDBMS

通信管理 CM

分布式查询
集中式数据库查询代价是CPU和I/O

分布式数据库查询代价是通信

分布式查询优化考虑：操作执行顺序

操作执行算法（连接和并操作）

不同场地数据流动顺序

分布式事务管理

恢复控制：基于两阶段的提交协议

并发控制：基于封锁协议

并行数据库

并行数据库结构：共享内存结构 内存冲突

共享磁盘结构 通信代价

无共享结构 访问代价

层次结构 集成三优

一维划分

轮转法 扫描整个关系

散列划分 哈希索引，点查询，数据扫描

范围划分 记录排序

多维数据划分：CMD多维划分、BERD多维划分法、MAGIC多维划分法

并行算法

并行顺序：①重新按排序属性进行范围划分，然后划分排序，最后将结果合并

②并行外排序归并算法

并行连接：划分连接、分片—复制连接

云计算数据库架构

云：公共云、私有云、混合云

Saas：软件即服务 PaaS：平台即服务

云计算平台：Amazon的AWS、Google的GAE、开放云Hadoop

云数据库体系结构：

文件系统Google File System

分布式编程环境Map/Reduce

分布式锁机制Chubby

大规模分布式数据库Big Table

云数据库缺点：数据安全、云管理、因特网依赖

Big Table数据模型：行关键字（Row Key）、列关键字（Column Key）、时间戳（Timestamp）

XML数据库

XML:可扩展标识语言，用标签来描述数据；标记电子文件，具有结构性

XML数据库三种类型：XML (能处理XML的数据库)

NXD (纯XML数据库)

HXD (混合XML数据库)

优势：能够对半结构化数据进行处理。

提供对标签和路径的操作。

能清晰地表达数据的层次特征

第十四章数据仓库与数据挖掘

决策支持系统的发展

数据仓库是核心

操作型系统是基本数据源

决策支持系统是数据的需求者

两类数据：原始数据（操作）、导出数据（分析）

数据仓库技术概述

数据仓库的特性：面向主题、集成、不可更新、时间特性

主题与面向主题

主题称为分析主题或分析领域

数据主题仓库的实现采用关系型数据库技术

数据仓库的其他特征

集成是最为重要的特性，分为数据抽取、转换、清理、装载

不可更新

随时间变化

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

层次结构：操作型数据、操作型数据存储、数据仓库、数据集市、个体层数据

功能结构：数据处理、数据管理、数据应用

数据仓库的数据组织

数据组织结构：早期细节级、当前细节级、轻度综合级、高度综合级

粒度：粒度越大，表示综合程度越高

粒度越小，细节程度越高，能回答的查询越多

数据分区

按照时间标准划分

系统层分区 数据库系统提供的机制

应用层分区 应用代码实现

如何分区：开发者和程序员控制

元数据

描述数据的结构、内容、链和索引等

技术元数据 细节

业务元数据 业务

操作型数据存储

ODSⅠ：数据更新频率秒级。

ODSⅡ：数据更新频率小时级。

ODSⅢ：数据更新频率天级。

ODSⅣ：根据数据来源方向和类型区分

基本特点：面向主题的、集成的、可变的、 当前或接近当前的。

目的是支持：即时（up-to-sencond）联机分析应用、全局型PLTP应用

设计与建造数据仓库

数据仓库的需求设计

传统的系统开发生命周期 SDLC是典型的需求驱动开发生命周期

数据仓库环境的系统开发生命周期 CLDS是典型的数据驱动开发生命周期

数据仓库的数据模型

概念（E-R图）

逻辑（关系模型或多维数据模型）

物理三级：①不包含纯操作型数据

②需要扩充关键字，加入时间属性

③需要增加导出数据

数据仓库的设计步骤

概念模型设计

技术评估与环境准备工作

逻辑模型设计

物理模型设计

数据生成与应用实现

数据仓库运行与维护

数据集成：抽取、转换、清理、装载

数据仓库的运行与维护

基本思路：根据某种维护策略，在一定条件下触发维护操作；

维护操作捕捉到数据源中的数据变化；

通过一定策略对数据仓库中的数据进行相应的更新操作，以保持两者的一致性。

维护策略

实时维护、延时维护、快照维护

捕捉数据源变化

触发器、修改数据源应用程序、通过日志文件、快照比较法

导出数据的刷新

方法：①维护对象的数据源对其进行重新计算。

②数据源的变化量在维护对象原有数据的基础上进行添加和修改，增量式维护

用户不可存取数据仓库时间的长短是衡量数据维护效率的重要指标。

将维护分为“聚集”和“更新”两个部分

元数据

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

联机分析与多维数据模型

联机分析处理或在线分析处理（OLAP）特点： 快速、可分析和多维

多维分析基本操作

钻取与卷起 对某一维向更细节层方向观察数据，卷起则反之

切片 局部数据的显示

旋转 改变维方向，不同视角数据；数据交叉

OLAP实现方式

多维数据库（MOLAP） 多维数组为基本存储结构。

关系数据库（ROLAP） 关系表表示和存储。（星形模式或雪花模式）

混合型的（HOLAP） 结合MOLAP与ROLAP。具有最好的查询性能。

数据挖掘技术

数据挖掘步骤

数据准备：数据选取、数据预处理、数据变换

数据挖掘：先确定挖掘的任务，其次决定挖掘算法

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

常见的挖掘任务：分类预测任务（决策树、贝叶斯），描述型任务（聚类、关联）

关联规则挖掘

阶段一：寻找高频项目组

阶段二：由高频项目组中产生关联规则

分类挖掘

预测数据对象的离散类别

①建立分类函数，构造分类器

②将分类函数对未知类别标记的数据项进行分类操作

聚类挖掘

对集中的数据进行分组，组内的数据尽量相似而不同组间的数据尽可能不同。

方法：统计、机器学习、神经网络、面向数据库

时间序列分析

用时间排序的一组随机变量（数据演变分析）

角度：①一元时间序列和多元时间序列

②等间隔时间序列和不等间隔时间序列

③平稳时间序列和非平稳时间序列