三级数据库技术-第七章考点详解
考点1：创建及维护数据库
SQL Server 2008中的数据库包含数据的表集合，以及其他对象（如视图，索引，存储过程等）
SQL Server 2008包含两种数据库：系统数据库和用户数据库
系统数据库由DBMS自动创建和维护。用户数据库保存业务有关数据。
通常说的创建数据库指的是创建用户数据库
一般用户对系统数据只有查询权
五个系统数据库
master : 最重要的系统数据库。记录了所有其他数据库文件的物理存储位置以及SQL Server 的初始化信息
msdb : 保存调度报警，作业，操作员等信息。作业是自动执行的操作集合，作业的执行无需人工干预
model : 创建数据库的模板。用户创建数据库时，系统自动将model中的全部内容复制到新数据库中。
tempdb : 临时数据库，保存临时对象或者中间结果。每次启动SQL Server 都会重新创建tempdb
Resource : 只读数据库，包含了SQL Server 的所有系统对象。资源管理器中不可见
数据库的组成
文件包括数据文件和日志文件。
日志文件用于恢复数据库。
数据文件分为主要数据文件和次要数据文件
- 主要数据文件
扩展名：.mdf
每个数据库只能有一个主要数据文件
不小于3MB - 次要数据文件
扩展名：.ndf
一个数据库可以包含0个或多个次要数据文件
次要数据文件可以放在不同的磁盘上 - 日志文件
扩展名：.ldf
日志文件存放恢复数据库的日志信息。一个数据库至少有一个日志文件
日志文件不能删除
数据库存储空间的分配
用户创建数据库时，系统自动复制model 到新建数据库的主要数据文件中。
SQL Server 2008 中，数据的存储分配单位是数据页
一页是8KB，页是最小的存储单位，数据表的一行只能存储在一页中，不能分页存储。
因此一行的大小不能超过8060KB(8KB-132B的系统信息)
- 空间利用率
一页中数据大小占总容量的比例：
![]()
在该题目中，空间利用率为4031B/8060B*100%
数据库文件组
文件组是数据文件的逻辑集合。
文件组分为主文件组（PRIMAR) 和 用户定义文件组
规则：
- 日志文件不包含在文件组中。独立于文件组体系。日志文件和数据文件是分开管理的
- 一个数据库可以包含多个文件组，但是只能有一个默认文件组（默认PRIMARY）
- 一个数据文件只能属于一个文件组
- 一个文件组可以包含多个数据文件
- 次要数据文件可以放在主文件组中。
数据文件和日志文件的属性
每个文件都有逻辑文件名和物理文件名
- 逻辑文件名 NAME
- 物理路径 FILENAME
- 初始大小 SIZE
- 最大大小 MAXSIZE
- 增长方式 FILEGROWTH
其中，初始大小不能低于model数据库主要数据文件的大小
增长方式默认为自动增长，也可以指定每次增长大小，默认单位是MB
最大大小规定了文件的空间限制。默认无限制
T-SQL语句创建数据库
使用CREATE DATABASE创建数据库
语法：
CREATE DATABASE db_name
ON [PRIMARY] -- 指定主文件组
(
NAME = 逻辑文件名 ,
FILENAME = 操作系统物理路径 ,
SIZE = 初始大小（默认MB）,
MAXSIZE = max_size | UNLIMITED,
FILEGROWTH = 增长量（可指定%或固定值,默认单位MB）
)
LOG ON -- 日志文件
(
NAME = 逻辑文件名
FILENAME = 操作系统物理路径 ,
SIZE = 初始大小（默认MB) ,
MAXSIZE = max_size | UNLIMITED ,
FILEGROWTH = 增长量（可指定%或固定值,默认单位MB）
)
关键词：
ON后定义数据文件，LOG ON后定义日志文件- NAME = 逻辑文件名（数据库内部引用用），FILENAME = 物理路径（OS 级别）
- FILEGROWTH 可写 10% 或 10MB，表示增长幅度
如果想设置不自动增长，令FILEGROWTH = 0
修改数据库
- 添加新的数据文件和日志文件
语法：
ALTER DATABASE db_name ADD [LOG] FILE (
NAME = 逻辑文件名
FILENAME = 操作系统物理路径 ,
SIZE = 初始大小（默认MB) ,
MAXSIZE = max_size | UNLIMITED ,
FILEGROWTH = 增长量（可指定%或固定值,默认单位MB）
)
关键词：
ADD FILE 用于添加新的数据文件，ADD LOG FILE用于添加新的日志文件
- 修改文件属性
使用MODIFY FILE修改文件属性
语法：
ALTER DATABASE db_name MODIFY FILE (
NAME = 逻辑文件名
FILENAME = 操作系统物理路径 ,
SIZE = 初始大小（默认MB) ,
MAXSIZE = max_size | UNLIMITED ,
FILEGROWTH = 增长量（可指定%或固定值,默认单位MB）
)
可以通过MODIFY FILE来改变已有文件的大小
- 删除文件
使用ALTER DATABASE db_name REMOVE FILE logical_filename删除文件
语法：
ALTER DATABASE db_name
REMOVE FILE 逻辑名称
🔺只有当日志文件不包含任何活动或者不活动的事务时才可以删除日志文件
- 收缩数据库空间
收缩数据库空间可以释放数据库中未使用的空间，并返还给操作系统。
数据文件和日志文件都可以收缩。
可以收缩整个数据库，按比例收缩
也可以收缩指定文件，并且可以一次性收缩多个文件，按大小收缩
规则:
- 收缩整个数据库时，收缩后的各个文件大小不能低于设置的初始文件大小
- 收缩指定文件大小时，没有限制
语法：
-- 收缩整个数据库
DBCC SHRINKDATABASE ( db_name , 20 ) -- 20表示收缩到20%剩余空间
-- 收缩指定文件
DBCC SHRINKFILE ( 文件逻辑名称, 4 ) -- 4表示收缩到4MB
分离和附加数据库
分离数据库：将数据库从 SQL Server 实例中删除，但不删除数据文件和日志文件。
附加数据库：将分离的数据库文件重新加载到 SQL Server 实例中。
分离和附加数据库的目的：将数据库从一台服务器移动到另一台服务器
分离数据库语法：
EXEC sp_detach_db '数据库名称 '， 'true'|'false'
true表示跳过更新统计信息，fasle表示显式更新统计信息
案例1 ：
附加数据库语法：
CREATE DATABASE db_name
ON ( NAME = 逻辑路径 )
FOR ATTACH
FOR ATTACH指定通过现有数据文件来创建数据库。
分离和附加数据库时，SQL 服务器应处于启动状态
正在使用的数据库不能分离
考点二：架构
架构（SCHEMA）也称为模式，是数据库下的一个容器或者逻辑命名空间，可以存放表，视图，存储过程等对象。
架构里的对象称为架构对象，包括基本表，视图，触发器，存储过程等
要点
- 🔺一个数据库可以包含多个架构。架构由特定的授权用户所有
- 一个架构包含0个到多个架构对象。
- 🔺一个用户可以拥有多个架构，一个架构可以被多个用户共享
- 一个数据库的不同架构内，可以包含同名表
- 🔺架构可以显式命名。如果没有命名，则为默认名（用户名）。
- 🔺创建架构必须有数据库管理员权限或者
CREATE SCHEMA权限 - 如果没有指定架构，直接使用
CREATE创建表等对象时，默认在当前架构中创建。
- 创建架构
使用CREATE SCHEMA创建架构
CREATE SCHEMA schema_name AUTHORIZATION owner_name
创建架构时可以同时创建建构对象。如创建架构时创建表：
- 删除架构
使用DROP SCHEMA删除架构，删除架构有两种模式
- CASCADE:连同架构内的所有对象一起删除
- RESTRICT : 若架构中有对象则拒绝删除
语法：
DROP SCHEMA schema_name CASCADE | RESTRICT
但是SQL Server中的删除架构没有可选项，若架构内有对象则无法删除。需要先删除架构内所有对象，然后再删除架构
语法： DROP SCHEMA schema_name
例题
考点三：分区表
分区表将一个基本表中的数据按照水平方式划分为不同的子集。
物理上分区 这些子集可以存储在不同的文件组中。物理上分散存储。
从逻辑上看，仍是一个完整的表
使用分区可以有效管理或者访问数据，提高数据库性能
适合使用分区表的场景
- 如果数据量大，而且数据分段，并且对不同段的数据使用的操作不同，可以使用分区表
- 操作只涉及部分数据
🔺如果表中大量数据经常使用，而且操作方式基本相同，无需使用分区表
创建分区表的步骤
- 创建分区函数：告诉DBMS以什么方式进行分区。怎么分
- 创建分区方案：将分区函数生成的分区映射到文件组中。分完放哪
- 使用分区方案创建表
（1）创建分区函数
使用CREATE PARTITION FUNCTION创建分区函数
语法：
CREATE PARTIITON FUNCTION pf_name ( input_parameter_type)
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( boundary_value1, boundary_value2, ... )
参数：
pf_name:分区函数名。分区函数名在数据库中必须唯一。
input_parameter_type 分区键的数据类型（int、date、datetime 等）
🔺LEFT / RIGHT 边界值归属方向 。默认为LEFT。即左开右闭区间。若使用RIGHT，则为左闭右开区间。
boundary_value 边界值列表，N 个边界 = N+1 个分区
案例1 ：
（2）创建分区方案
使用CREATE PARTITION SCHEMA创建分区方案
CREATE PARTITION SCHEMA ps_name
AS PARTITION pf_name
[ALL] TO (filegroup1, filegroup2, ..., filegroupN | PRIMARY)
ps_name : 分区方案名。在数据库中必须唯一。
pf_name : 使用分区方案的分区函数名。
TO指定分区的文件组名。
如果指定了ALL,则只能指定一个filegroup。一般用ALL TO (PRIMARY)将所有分区映射到一个主文件组中。
如果指定多个文件组。则文件组数量应该多于分区函数指定的分区数量。
案例2：
（3）使用分区方案创建表
CREATE TABLE tb_name( . . . )
ON ps_name(column)
ON ps_name(column)使用指定列创建分区。
综合案例 ：
辨析
- 分区表水平划分
- 分区对用户透明，用户访问表中数据时不需要指明分区
- 分区步骤：创建分区函数（
CREATE PARTITION FUNCTION...AS RANGE）,创建分区方案(CREATE PARTITION SCHEMA... AS PARTITION),创建表（CREATE TABLE . . . ON ps_name(column)） - 多个分区可以映射到同一个文件组中
- LEFT是左开右闭，RIGHT左闭右开
- 分区数 = 边界数+1
- 如果指定多个分区组，分区方案指定的文件组数量>=分区函数指定的分区数
![image]()
![image]()
![image]()
![image]()
![image]()
考点4：索引
创建索引
使用CREATE INDEX为基本表中的指定列创建索引
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED ] INDEX idx_name
ON tb_name (column1 [ASC|DESC] , column2 [ASC|DESC], ...)
[INCLUDE (non_key_column, ...)] 指定要添加到非聚集索引叶级别的非键列。
[Where]指定索引包含的数据行
[ON partition_shema_name （column）指定分区方案
| filegroup_name为指定文件组创建索引
| default 为默认文件组创建索引
]
其中：
[CLUSTERED | NONCLUSTERED ] 用于指定聚集索引或者非聚集索引。默认NONCLUSTERED非聚集
🔺一个表只能有一个聚集索引。可以有多个非聚集索引。拥有唯一聚集索引的视图称为索引视图。
🔺先创建聚集索引，再创建非聚集索引
ON partition_shema_name （column）为指定分区方案创建索引。column指定分区方案依据的列。
案例1 ：
删除索引
如果频繁地对数据进行增加、删除和更改操作，则系统会花费很多时间来维护索引，这会降低数据的修改效率
存储索引需要占用额外的空间，这增加了数据库的空间开销。因此，当不需要某个索引时可将其删除。
DROP INDEX idx_name ON tb_name
案例1：
最左前缀原则
对于复合索引只能按顺序使用索引列。不能跳层
案例：
索引视图
标准视图
视图是从一个或几个基本表中导出的表，是一个虚拟表，
数据库中只存放视图的定义，而不存储具体数据。
视图对应数据库的外模式，提供逻辑独立性
索引视图
对标准视图创建唯一聚集索引后，视图的结果集将会被物理存储在数据库中。
建有唯一聚集索引的视图称为索引视图，也称为物化视图。
与标准视图对比：
适合建立索引视图的场合：
- 很少更新基础数据。
- 如果经常更新基础数据，维护索引视图的成本会超过带来的性能提升
- 如果基础数据定期更新，但在更新之间主要作为只读数据。可以在更新前删除索引视图，然后再重建索引视图，从而提高性能。
索引视图可以提高性能的查询类型：
- 涉及大量行的连接和聚合
- 许多查询经常执行相同的连接和聚合（一次计算，多次复用）
🔺不适合建立索引视图的场景 - 大量写操作的OLTP系统
- 大量更新的数据库
- 不涉及聚合或者连接的查询
- GROUP BY 列具有高基数度（一个列包含多个不同值）
建立索引视图的规则
🔺1. 对视图建立的第一个索引必须是聚集索引，之后可以创建其他非聚集索引
2. 索引视图和基本表必须在同一数据库中，同一所有者
3. 函数必须确定性,不能使用如GETDATE()
4. 索引视图只能引用基本表，不能引用其他视图、派生表、子查询等。
🔺5. 必须使用SCHEMABINDING选项
创建步骤
- 创建带SCHEMABINDING选项的视图
CREATE VIEW view_name
WITH SCHEMABINDING
AS
...
- 为视图创建唯一聚集索引
CREATE UNIQUE CLUSTERED INDEX idx_name
ON view_name (column1,....)
3.可以根据需要创建非聚集索引
CREATE NONCLUSTERED INDEX idx_name2
ON view_name (column)
真题
浙公网安备 33010602011771号