计算机三级数据库技术 第6章 数据库及数据库对象

6.1 创建及维护数据库

6.1.1 SQL Server数据库概述

SQL Server的发展史：

SQL Server 7.0 ( 1999年,正式跻身企业数据库行列

SQL Server 2000 ( 2000年，代表产品)

SQL Server 2005 ( 2005年,代号"~Yukon" )

SQL Server 2008 ( 2008年,重大的产品版本)

SQL Server 2012

SQL Server 2014

.......

SQL Server的特色

保护数据库查询

在服务器的管理操作上花费更少的时间

增加应用程序稳定性

系统执行效能优化与预测功能

SQL Server的版本

企业版（Enterprise Edition）：为企业级应用提供综合数据平台。

标准版（Standard Edition）：为部门级应用提供支持。

开发版（Developer Edition）：拥有企业版特性，使用限制。

工作组版（Workgroup Edition）:为各分支应用提供支持。

网络版（Web Edition）:提供低成本的WEB应用解决方案。

移动版（Compact）支持移动设备。

免费版（Express）:适用于学习及构建小型应用。

SQL Server中数据库的分类

系统数据库(自动创建)

master：所有系统级信息，元数据、端点、连接服务器和系统配置。

msdb：代理服务调度报警和作业、记录操作员时使用保存此类信息。

tempdb：用于保存临时对象和中间结果，每次启动会重新创建。

model：所有数据库的模板，存放用户数据库公共信息。

resource：只读数据库。在对象资源管理器中看不到。

用户数据库（保存与用户业务有关的数据）

6.1.2 SQL Server数据库的组成

SQL Server将数据库映射为一组操作系统文件：

数据文件

.mdf :主要数据文件。只有一个，大小不得小于3MB。

.ndf :次要数据文件。有0个或多个,可在一个或多个磁盘存放。

日志文件

.ldf :事务日志文件。至少有一个日志文件。

数据库存储空间的分配

创建用户数据库时，mode数据库被自动复制到新建库。

数据存储的最小单位：数据页( Page，简称页)。

1页是一块8KB的连续磁盘空间。

页的大小决定了数据库表中一行数据的最大大小。

行不能垮页存储。

例:一个数据表10000行数据，每行3000字节,计算表需要的存储空间。

        10000/2*8KB=40MB

        空间利用率75%

6.1.3 数据库文件组

两种类型的文件组：

主文件组（PRIMARY）：

        系统定义，包含主要数据文件和任何没有明确分配的其他文件组的其他数据文件，系统表所有页均分配在主文件组中。

用户定义文件组：

        在定义或修改数据库时用FILEGROUP关键字指定。

注意：

        日志文件不在文件组中,日志空间与数据空间分开管理。

        一个文件不可以是多个文件组成员。

        若文件组包含多个文件，则在所有文件被填满后才会自动循环增长。

        文件加入数据库中后，不能移动到其他文件组。

        只能指定一个文件组为默认文件组。

6.1.4 数据库文件的属性

定义数据库的数据文件和日志文件所需信息：

文件名及其位置

逻辑文件名，物理文件名

初始大小

不能小于model数据库主要数据文件的大小

增长方式

可指定文件是否自增长（默认）

最大大小

文件增长的最大限制。默认无限制

6.1.5 用T-SQL创建数据库

创建数据库一般有两种方式：

通过SQL Server Management Studio创建数据库

通过 T-SQL 语句创建数据库

T-SQL 语法格式：

CREATE DATABASE 
database_name 
[ON 
    [<filespec>[,...n]] 
    [,<filegroup>[,...n]] 
] 
[LOG ON {<filespec>[,...n]}] 
[COLLATE collation_name] 
[FOR LOAD|FOR ATTACH]

说明:

PRIMARY：指定为主要数据库文件，没有指定默认第一个文件是主要数据文件。

LOG ON：自动创建日志文件，大小为数据文件总和25%或512KB中大的。

NAME:逻辑文件名,唯一。

FILENAME：物理文件名。

SIZE：初始大小，.mdf大小不小于model，.ndf默认为1MB.

MAXSIZE：最大大小，未指定则文件自动增长到磁盘满。

UNLIMITED：增长无限制，一般指定为日志文件2TB，数据文件16TB。

FILEGROWTH：指定文件自动增量，不超过MAXSIZE。默认数据文件1MB，日志文件为当前文件的10%。

FILEGROUP：文件组逻辑名，唯一，不能是系统名。

DEFAULE：指定该文件组为默认文件组。

举例-创建学生数据库

CREATE DATABASE Studentdb 
ON 
(   NAME = 'Studentdb_Data', 
    FILENAME = 'E\Studentdb.mdf', 
    SIZE = 3MB, 
    MAXSIZE = 50MB, 
    FILEGROWTH = 10% 
) 
LOG ON 
(   NAME = 'Studentdb_Log', 
    FILENAME = 'E:\Studentdb.ldf', 
    SIZE = 2MB, 
    MAXSIZE = 5MB, 
    FILEGROWTH = 1MB 
) 
GO

6.1.6 修改数据库

扩大数据库空间

        扩大数据库中已有文件的大小

        为数据库添加新的文件

收缩数据库空间

        即释放数据库中未使用的空间，文件的收缩从末尾开始。

        自动收缩：       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

例1 : DBCC SHRINKDATABASE ( students , 20 )

        收缩数据库，该数据库所用文件都有20%可用空间

例2 : DBCC SHRINKFILE ( students datal , 4 )

        收缩数据库到4MB大小

删除数据库文件：

ALTER DATABASE STU_DB REMOVE FILE student_log1

注意：

        添加文件时，每个文件组中的数据文件按比例填充，日志文件是依次增加的。

        文件为空才能删除。

6.1.7 分离和附加数据库

分离数据库

作用：实现将数据库从台数据库服务器移到另一台，不需要重建。

从实例中删除，不删除数据文件和日志文件，保持了数据文件和日志文件完整致。

使用 sp_detach_db 系统存储过程实现

如: EXEC sp_detach_db'student','true'

附加数据库

将分离的数据库重新附加到数据库管理系统中。

必须指定主要数据文件的物理存储位置和文件名。

CREATE DATABASE...... 
FOR ATTACH|ATTACH_ REBUILD_LOG

例:

CREATE DATABASE students 
On(FILENAME='F:\Data\Students_data1.mdf') 
FOR ATTACH

---

6.2 架构

架构（Schema ,也称模式）, 是数据库下的个逻辑命名空间 ，是数据库对象的容器，一个数据库包含个或多个构架， 同一个数据库内架构名唯一。

定义构架

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

删除构架

DROP SCHEMA [<构架名>]

---

6.3 分区表

6.3.1 基本概念

        分区表是将表中的数据按水平分割成不同子集，并将数据子集存储在数据库一个或多个文件组中。物理上将大表分成几个小表，逻辑上还是一个大表。

        合理使用分区能提高数据库性能。

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

        表包含（或将包含）以多种不同方式使用的大量数据

        数据是分段的，比如以年份分隔。

6.3.2 创建分区表

三个步骤：

( 1 )创建分区函数：告诉DBMS以什么方式进行分区

CREATE PARTITION FUNCTION

( 2 )创建分区方案：作用是将分区函数生成的分区映射到文件组中

CREATE PARTITION SCHEME

( 3 )使用分区创建表

实例1 : 在分区列coll(int)上创建左侧分区函数：

CREATE PARTITION FUCNTION myPF1(int) 
AS RANGE LEFT FOR VALUES(1, 100, 1000);

实例2：先创建分区函数，再创建分区方案，并创建使用分区的表

CREATE PARTITION FUCNTION myPF1(int) 
AS RANGE LEFT VALUES(1, 100，1000); 
GO 
CREATE PARTITION SCHEME myPS1 
AS PARTITION myPF1 TO ( test1fg, test2fg, test3fg, test4fg ) 
GO 
CREATE TABLE PartitionTable ( Coll int， Col2 char(10) ON myPS1(coll))

---

6.4 索引

6.4.1 创建索引

唯一索引 
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] 
INDEX index_name 
ON table_name(column_name...) [WITH FILLFACTOR=x]

UNIQUE表示唯一索引，可选

CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，

可选FILLFACTOR表示填充因子,指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比

6.4.2 删除索引

DROP INDEX 'tablename.indexname | viewtable.indexname'[,...n]

---

6.5 索引视图

6.5.1 基本概念

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

建立唯聚集索引的视图， 称为索引视图，也称为物化视图。建立索引后，视图的结果集存放在数据库中。

对基本表的修改会反映到索引视图存储的数据中。

6.5.2 适合建立索引视图的场合

很少更新基础数据，索引视图效果更好

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

➢索引视图可以提高这些查询类型性能

处理大量行的连接和聚合

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

➢索引视图通常不会提高这些查询类型性能

具有大量写操作的OLTP系统

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

不涉及聚合或连接的查询

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

6.5.3 定义索引视图

创建聚簇索引前视图必须符合的条件：

定义索引视图时，视图只能引用基本表，不能是其他视图。

引用的所以基本表和视图同一数据库，所有者相同。

必须用SCHEMABINDING选项建视图。

视图中表达式引用的所有函数必须确定。

对视图建立的第一个索引是唯一聚簇索引，之后再创建其他

CREATE VIEW;
WITH SCHEMABINDING; 
CREATE UNIQUE CLUSTERED INDEX...

---

章末测试

一、选择题

1、在SQL Server 2008中，如果数据库tempdb的空间不足，可能会造成一些操作无法进行 ，此时需要扩大tempdb的空间。下列关于扩大tempdb空间的方法，错误的是（ ）

A .手工扩大tempdb中某数据文件的大小

B .设置tempdb中的数据文件为自动增长方式，每当空间不够时让其自动增长

C .手工为tempdb增加个数据文件

D .删除tempdb中的日志内容，以获得更多的数据空间

答案: D

2、设有职工表(职工号，姓名，地址1，地址2)，其中，职工号为主码。现要求地址1和地址2组合起来不能有重复值。在SQL Server 2008环境中有下列创建该表的语句

I . CREATE TABLE职工表(

职工号int PRIMARY KEY,

姓名nchar(10)，

地址1 nvarchar(20)，

地址2 nvarchar(20)，

UNIQUE(地址1,地址2)

)

II . CREATE TABLE职工表(

职工号int PRIMARY KEY，

姓名nchar(10),

地址1 nvarchar(20)，

地址2 nvarchar(20) UNIQUE(地址1,地址2)

)

III . CREATE TABLE职工表(

职工号int PRIMARY KEY，

姓名nchar(10)，

地址1 nvarchar(20) UNIQUE，

地址2 nvarchar(20) UNIQUE

)

IV . CREATE TABLE职工表(

职工号int PRIMARY KEY，

姓名nchar(10)，

地址1 nvarchar(20) UNIQUE (地址1,地址2)，

地址2 nvarchar(20)

)

上述语句能正确实现此约束的是（ ）

A.仅I和III

B.仅II和IV

C.都正确

D.仅I、II和IV

答案: D

3、在进行数据库物理设计时，为提高查询效率，需要在基本表的一些列上建立索引。有下列情况：

I.查询语句的WHERE子句中引用率比较高的列

II.经常参与连接操作的列

III.经常在order by子句中出现的列

IV.经常使用LIKE操作符且字符串前后均带有%的列

上述情况中一般情况下适合建立索引的是（ ）

A.以上全部

B.仅I和III

C.仅II、III和IV

D.仅I、II和III

答案: D

二、填空

1、在SQL Server 2008中，主要数据文件必须建立在（ ）文件组中。

答案: 主

2、不同的数据库管理系统采用的日志文件格式不完全一样，概括起来主要有以记录为单位的日志文件和以（ ）为单位的日志文件两种。

答案: 数据块

3、在SQL Server 2008中，每个数据页可存储8060字节的数据。设表T有10000行数据，每行占用4031字节，则存储该表数据大约需要( ) MB存储空间，其空间利用率大约是( ) %。 (存储空间和空间利用率均保留到整数，小数点后按四舍五入处理)

答案: 80、50

---

内容补充

1、SQL Server 2008支持三种用户自定义函数:标量函数、内嵌表值函数和多语句表值函数。标量函数可以出现在SELECT语句目标列中，其他两个函数必须放在SELECT语句的FROM子句中。

2、在SQL中，用 DISTINCT 关键词消除复出现的元组。

3、UNION操作符用于合并两个或多 个SELECT 语句的结果集。请注意， UNION内部的SELECT语句必须拥有 相同数量的列，而且对应列的语义应该相同。 列也必须拥有兼容的数据类型。同时，每条SELECT语句中的列的顺序必须相同。

4、在使用TOP限定结果集时，WITH TIES表示包括最后一行取值并列的结果；TOP 表示取查询结果的前行数据；TOP npercent表示取查询结果的前n%行数据。