第5章 SQL Server 2008 数据库管理

第5章SQLServer2008数据库管理本章学习目标

理解SQLServer2008数据库结构和组成；

熟练掌握数据库创建、删除和修改等操作；

了解用户数据库的增缩、分离和附加等操作。5.1SQLServer2008数据库基本知识数据被DBMS管理与数据存储在物理磁盘上是数据库中两种完全不同的数据组织形式，分别称之为数据库的物理结构和数据库的逻辑结构。

5.1.1数据库的结构SQLServer2008DBMS将数据组织成数据表、视图等逻辑对象，这是从逻辑角度来组织与管理数据。为了数据库管理员管理数据的方便和与操作系统实际情况相一致，SQLServer2008又将数据库呈现为各种数据库的文件，这是从物理角度来组织与管理数据。1．数据库的逻辑结构

数据库逻辑结构主要应用于面向用户的数据组织和管理，从逻辑的角度，数据库由若干个用户可视的对象构成，如表、视图、角色等，由于这些对象都存在于数据库中，因此称为数据库对象。用户利用这些数据库对象存储或读取数据库中的数据，利用数据库对象直接或间接地用于不同应用程序的存储、操作、检索等工作。

SQLServer数据库内含的数据库对象包括数据表、视图、约束、规则、默认、索引、存储过程、触发器等。通过SQLServer2008对象资源管理器，可以查看当前数据库内的各种数据库对象。2．数据库的物理结构

数据库物理结构主要应用于面向计算机的数据组织和管理。数据库的物理结构表现就是操作系统文件，一个数据库由一个或多个磁盘上的文件组成。

SQLServer数据库的文件有两种类型：数据文件和日志文件。5.1.2数据文件1．数据文件类型

SQLServer数据库通过数据文件中保存与数据库相关的数据和对象。在SQLServer2008中有两种类型的数据文件。1）主数据文件

2）次数据文件

1）主数据文件

主数据文件是数据库的起点，其中包含了数据库的初始信息，并记录数据库还拥有哪些文件。每个数据库有且只能有一个主数据文件。主数据文件是数据库必需的文件，Microsoft建议的主数据文件的扩展名是.mdf。2）次要数据文件

除主数据文件以外的所有其他数据文件都是次要数据文件。次要数据文件不是数据库必需的文件。Microsoft建议的次要数据文件的扩展名是.ndf。2．数据文件结构

数据文件的结构按照层次可以划分为页和区，每个数据文件由若干个大小为64KB的区组成，每个区由8个8KB大小的连续空间组成，这些连续空间称为页。

1）页

在SQLServer中，页是数据存储的基本单位。为数据库中的数据文件分配的磁盘空间可以从逻辑上划分成带有连续编号的页（编号从0开始）。磁盘I/O操作在页级执行，SQLServer读取或写入的是所有的数据页。

数据文件中的页有8种类型。2）区

区是SQLServer分配给表和索引的基本单位。区有统一区、混合区两种类型。3．文件组

为了有助于数据布局和管理任务，用户可以在SQLServer中将多个文件划分为一个文件集合，并用一个的名称表示这一文件集合，这就是文件组。

文件组分为主要文件组、用户定义文件组、默认文件组3种类型。5.1.3事务日志文件在SQLServer2008中，每个数据库至少拥有一个自己的日志文件（也可以拥有多个日志文件）。日志文件的大小最少是1MB，默认扩展名是.ldf，用来记录数据库的事务日志，即记录了所有事务以及每个事务对数据库所做的修改。

5.2SQLServer2008数据库基本管理在SQLServer2008中，所有类型的数据库管理操作都包括两种方法：一种方法是使用SQLServerManagementStudio的对象资源管理器，以图形化的方式完成对于数据库的管理；另一种方法是使用T-SQL语句或系统存储过程，以命令方式完成对于数据库的管理。

1．利用对象资源管理器创建用户数据库

在SQLServerManagementStudio中，利用图形化的方法可以非常方便地创建数据库。5.2.1创建用户数据库2．利用T-SQL语句创建用户数据库CREATEDATABASEdatabase_name

ON

{[PRIMARY](NAME=logical_file_name,

FILENAME=‘os_file_name’

[,SIZE=size]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROWTH=growth_increment])

}[,...n]

LOGON

{[PRIMARY](NAME=logical_file_name,

FILENAME=‘os_file_name’

[,SIZE=size]

[,MAXSIZE={max_size|UNLIMITED}]

[,FILEGROWTH=growth_increment])

}[,...n]参数说明如下：

database_name：新数据库的名称。

ON：指定显式定义用来存储数据库数据部分的磁盘文件（数据文件）。

PRIMARY：在主文件组中指定文件。

LOGON：指定用来存储数据库日志的磁盘文件（日志文件）。

NAME：指定文件的逻辑名称。

FILENAME：指定操作系统（物理）文件名称。

os_file_name：创建文件时由操作系统使用的路径和文件名。

SIZE：指定文件的大小。

MAXSIZE：指定文件可增大到的最大大小。

UNLIMITED：指定文件将增长到整个磁盘。

FILEGROWTH：指定文件的自动增量。5.2.2修改用户数据库在数据库创建之后，还可以使用SQLServerManagementStudio和T-SQL语句来查看和修改数据库的配置信息。1．利用对象资源管理器修改用户数据库如果想要查看或修改数据库的配置信息，打开SQLServerManagementStudio，在“对象资源管理器”窗口展开数据库实例下的“数据库”节点，接着选中需要查看或配置的数据库并单击鼠标右键，从弹出的快捷菜单中选择“属性”命令。2．利用T-SQL语句修改用户数据库1）选择数据库

在SQLServer服务器上，可能存在多个用户数据库，用户只有连接上所要使用的数据库，才能对该数据库中的数据进行操作。选择数据库语句的语法格式如下：

USEdatabase_name

其中，database_name为选择的数据库名称。2）查看数据库属性

数据库的属性信息都保存在系统数据库和系统数据表中，可以通过系统提供的存储过程来获取有关数据库的属性信息。

sp_helpdb显示数据库和数据库参数信息。

sp_spaceused查看数据库空间信息。

sp_options查看数据库选项信息。【例5-1】查询数据库student的相关参数信息。

Execsp_helpdbstudent

【例5-2】查询数据库student的空间信息。

Usestudent

Execsp_spaceused

【例5-3】查询数据库student的选项信息。

Execsp_dboptionstudent3）修改数据库ALTERDATABASEdatabase_name

{ADDFILE<filespec>[,...n][TOFILEGROUP{filegroup_name}]

|ADDLOGFILE<filespec>[,...n]

|REMOVEFILElogical_file_name

|MODIFYFILE<filespec>

|ADDFILEGROUPfilegroup_name

|REMOVEFILEGROUPfilegroup_name

|MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}

参数说明如下。

ADDFILE：向数据库文件组添加新的数据文件。

ADDLOGFILE：向数据库添加事务日志文件。

REMOVEFILE：从SQLServer的实例中删除逻辑文件说明并删除物理文件。

MODIFYFILE：修改某一文件的属性。

ADDFILEGROUP：向数据库添加文件组。

REMOVEFILEGROUP：从实例中删除文件组。

MODIFYFILEGROUP：修改某一文件组的属性。5.2.3删除用户数据库1．利用对象资源管理器删除用户数据库

如果数据库再需要，为节省存储空间，可删除数据库。数据库一旦被删除，即被永久删除，文件和其数据都将从服务器上的磁盘中删除。2．利用T-SQL语句删除用户数据库

使用T-SQL的DROPDATABASE语句可以删除用户数据库，其语法格式为如下：

DROPDATABASEdatabase_name

其中，database_name：指定要删除的数据库的名称。

5.3SQLServer2008数据库高级管理SQLServer采取预先分配空间的方法来建立数据库的数据文件或者日志文件，比如数据文件的空间分配了100MB，而实际上只占用了50MB空间，这样造成存储空间的浪费。为此，SQLServer2008提供了收缩数据库的功能，允许对数据库中的每个文件进行收缩，删除已经分配但没有使用的页。5.3.1收缩用户数据库1．利用对象资源管理器收缩用户数据库

1）自动收缩用户数据库

2）手动收缩用户数据库

3）手动收缩数据文件

2．利用T-SQL语句收缩用户数据库

在SQLServer中，收缩用户数据库包括自动收缩数据库、手动收缩数据库和收缩指定数据文件3种方式。

1）自动收缩数据库

使用ALTERDATABASE语句可以实现用户数据库自动收缩，其语法格式如下：

ALTERDATABASEdatabase_name

SETAUTO_SHRINKON/OFF

参数说明如下。

ON：将数据库设为自动收缩；

OFF：将数据库设为不自动收缩。

2）手动收缩数据库

使用DBCCSHRINKDATABASE语句可以实现用户数据库手动收缩，其语法格式如下：

DBCCSHRINKDATABASE

(database_name|database_id|0[,target_percent]

[,{NOTRUNCATE|TRUNCATEONLY}]

)

参数说明如下。

database_name：要收缩的数据库的名称或ID。target_percent：数据库收缩后的数据库文件中所需的剩余可用空间百分比。

NOTRUNCATE：通过将已分配的页从文件末尾移动到文件前面的未分配页来压缩数据文件中的数据。

TRUNCATEONLY：将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。3）收缩指定数据文件

使用DBCCSHRINKFILE语句可以实现收缩指定数据文件，其语法格式如下：

DBCCSHRINKFILE

({file_name|file_id}{[,EMPTYFILE]|[[,target_size]

[,{NOTRUNCATE|TRUNCATEONLY}]]}

)

[WITHNO_INFOMSGS]参数说明如下。

file_name：要收缩的文件的逻辑名称或文件的标识（ID）号。

target_size：用兆字节表示的文件大小（用整数表示）。如果未指定，则将文件大小减少到默认文件大小。默认大小为创建文件时指定的大小。

5.3.2分离与附加用户数据库在SQLServer中用户数据库可以从服务器的管理中分离出来，脱离服务器的管理，同时保持数据文件和日志文件的完整性和一致性，这样分离出来的数据库的日志文件和数据文件可以附加到其他SQLServer2000服务器上构成完整的数据库，附加的数据库和分离时完全一致。

与分离对应的是附加数据库操作。附加数据库可以很方便地在SQLServer2008服务器之间利用分离后的数据文件和日志文件组织成新的数据库。

在实际工作中，分离数据库作为对数据基本稳定的数据库的一种备份的办法来使用。

1．利用对象资源管理器分离与附加用户数据库

1）分离用户数据库

分离数据库是指将数据库从SQLServer服务器实例中删除，但是数据库的数据文件和事务日志文件在磁盘中依然存在。2）附加用户数据库

在SQLServer中，用户可以在数据库实例上附加被分离的数据库。附加时，DBMS会启动数据库。通常情况下，附加数据库时会将数据库重置为分离或复制时的状态。2．利用系统存储过程分离与附加用户数据库

1）分离用户数据库

sp_detach_db[@dbname=]‘database_name’

[,[@skipchecks=]‘skipchecks’]

[,[@keepfulltextindexfile=]‘KeepFulltextIndexFile’

参数说明如下。

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

[@skipchecks=]‘skipchecks’：指定跳过还是运行UPDATESTATISTIC。

[@keepfulltextindexfile=]‘KeepFulltextIndexFile’：指定在数据库分离操作过程中不会删除与所分离的数据库关联的全文索引文件。2）附加用户数据库

使用系统存储过程sp_attach_db来执行附加用户数据库的操作，其语法格式如下：

sp_attach_db[@dbname=]‘dbname’

,[@filename1=]‘filename_n’[,...16]

参数说明如下。

[@dbname=]‘dbnam’：要附加到该服务器的数据库的名称。该名称必须是唯一的。

[@filename1=]‘filename_n’：数据库文件的物理名称，包括路径。文件名列表至少必须包括主文件。主文件中包含指向数据库中其他文件的系统表。5.3.3数据库快照1．数据库快照的作用

数据库快照就是数据库的一个只读副本，就像给数据库照了照片一样，这是从SQLServer2005才新增的功能，只有SQLServer2005EnterpriseEdition和更高版本才提供数据库快照功能

2．创建用户数据库快照

任何能创建数据库的用户都可以创建数据库快照。SQLServerManagementStu