第3章 SQL Server 数据库.docx

第3章 SQL Server 数据库.docx

《第3章 SQL Server 数据库.docx》由会员分享，可在线阅读，更多相关《第3章 SQL Server 数据库.docx（24页珍藏版）》请在冰豆网上搜索。

第3章SQLServer数据库

第3章SQLServer2005数据库

【学习目标】

本章将主要介绍数据库的基本知识，包括数据库的设计、定义与维护过程。

其中包括使用SSMS管理创建、管理数据库，以及使用T-SQL语句创建与管理数据库。

本章学习要点：

◆数据库的管理问题

◆数据库文件与文件组的基本特征

◆数据库的创建与管理

【学习导航】

用户所关心的数据是分门别类地存放在数据库中的,表是放在数据库中的,除此之外视图、存储过程、触发器、约束等对象都存放在数据库中。

数据库是数据库管理系统的核心内容,是存放数据库对象的容器,也是使用数据系统时首先要面对的对象。

通俗地说，管理数据库就是管理数据文件。

如何合理的设计数据库，数据库中应该包含哪些文件，大小该如何设置，能否收缩与扩大数据库的容量、如何提高数据库的容错性、可管理性以及数据库的分离、备份、恢复、删除等内容。

本章将详细研究这些问题的解决方案。

表是数据库中最基本、最重要、最核心的对象，是组织数据的方式，是存储数据的地方。

从某种意义上说，管理数据库说是管理数据库中的表。

如何合理的设计表结构将直接影响到数据库中数据的使用效率。

本章将全面讨论有关表的管理技术与方案。

本章主要内容见图3-1所示的学习导航。

图3-1本章学习导航

3.1数据库

3.1.1数据库概述与术语

数据库是表、视图、存储过程、触发器等数据库对象的集合，是数据库管理系统和核心。

为了更好的理解数据数据，应首先了解数据库的物理文件和逻辑文件、事务日志、文件组、数据库的物理空间、数据库状态等基本概念。

在MicrosoftSQLServer2005系统中，一个数据库至少有一个数据库文件和一个事务日志文件。

当然，数据库中也可以有多个数据库文件和多个事务日志。

数据库文件存放数据库的数据和数据库的各种对象，事务日志用于存放事务日志。

一个数据库中最多可发拥有32767个数据库文件和32767个日志文件。

1．SQLServer的数据库文件类型

根据数据库文件的作用不同，可以将数据库文件分为发下三类：

（1）主数据文件（PrimaryFile）

主数据文件是数据库的起点，用来存放数据库的数据和数据库的启动信息。

每个数据库都有且必须仅有一个主数据文件。

主数据文件的默认扩展名是.MDF。

主数据文件都有两个名称，操作系统文件名（物理文件名）和逻辑文件名。

（2）次数据文件（SecondaryFile）

次数据文件也叫辅助数据文件。

次数据文件是可选的，主要用来存放不在主数据文件中的其他数据和对象。

使用次数据文件可以扩展存储空间。

次数据文件的默认扩展名是.NDF。

（3）事务日志文件（TransactionLog）

事务是一个单元的工作，该单元的工作要么全部完成，要么全部不完成。

MicrosoftSQLServer2005系统具有事务的功能，可以保证数据库操作的一致性和完整性。

SQLServer系统使用数据库的事务日志来实现事务的功能。

凡是对数据库进行增、删、改等操作，都会记录在事务日志文件中。

如果数据库被破坏时可以使用该日志文件来恢复数据库的数据。

每个数据库至少要有一个事务日志文件，事务日志文件的扩展名是.LDF。

2．SQLServer的数据库文件组

文件组就是文件的逻辑集合。

文件组可以把一些指定的文件组合在一起以方便管理和分配数据。

以提高数据的查询速度，便于数据库的维护。

例如：

某个数据中，三个文件（data1.ndf、data2.ndf、data3.ndf）分别创建在三个不同的磁盘驱动器上，并且为他们指定了一个文件组fgroup1。

创建表后可以明确的指定存放在fgroup1中。

这样对该表中数据的查询将分布在这三个磁盘上同时进行，因此可以通过并行执行访问来提高查询的性能。

通过使用文件组还可以简化数据库的维护工作：

（1）备份和恢复单独的文件或文件组，而非整个数据库，可以提高效率。

（2）将可维护性要求相近的表和索引分配到相同的文件组中。

（3）为自己的文件组指定可维护性高的表。

使用文件和文件组时，应该考虑以下因素：

（1）一个文件或者文件组只能用于一个数据库，而不能用于多个数据库。

（2）一个文件只能是某个文件组的成员，不能同时是多个文件给的成员。

（3）数据库的数据信息和日志信息不能放在同一个文件或文件组中，数据文件和日志文件总是分开的。

（4）日志文件永远也不能是任何文件组的一部分。

3．SQLServer的物理空间及状态

在MicrosoftSQLServer2005系统中，页是最小可管理物理空间，每页大小为8KB，即8192字节。

每八个连续页称为一个区，即区的大小是64KB，区用于控制表、索引的存储。

通过理解数据库的空间管理，可以估算数据库的设计尺寸。

数据库的大小等于所有表大小与索引大小之和。

数据库总是存在某个特定的状态中，例如：

ONLINE状态表示数据库正处于正常的在线状态，可以对数据执行正常的操作。

数据库的状态清单和特征如表3-1所示。

表3-1数据库的状态清单和特征

ONLINE

在线状态或联机状态，可以执行对数据的访问

OFFINE

离线状态或脱机状态，数据库不能正常使用。

可以人工设置。

用户可以执行对处于这种状态的数据库文件的移动等处理

RESTORING

还原状态，正在还原主文件组的一个或多个文件。

这时，数据库不能使用

RECOVERING

恢复状态，正在恢复数据库。

这是一个临时性状态。

如果恢复成功，则数据库自动处于在线状态。

如果恢复失败，则数据处于不能正常使用的可疑状态

RECOVERY

PENDING

恢复未完成状态。

恢复过程中缺少资源造成的问题状态。

数据库不可使用。

必须执行其他操作来解决这种问题

SUSPECT

可疑状态，主文件组可疑或可能被破坏。

数据库不能使用。

必须执行其他操作来解决这种问题

EMERGENCY

紧急状态。

可以人工设置数据为该状态。

这时数据库处于单用户模式或只读状态，只能由sysadmin固定服务器角色成员访问。

主要用于对数据的故障排除

3.1.2SQLServer2005的系统数据库

SQLServer数据库包含系统数据和用户数据库。

其中，系统数据库是安装SQLServer时系统自动安装的，系统数据存储着系统的重要信息，用来操作和管理系统；用户数据库是由用户自己创建，存放用户自己的数据。

在SQLServerManagementStudio环境中，SQLServer2005包含4个可见的系统数据库：

master、tempdb、model、msdb,和一个逻辑上不单独存在，隐藏的系统数据库Resource。

1．master数据库

master数据库是SQLServer的主数据库，包含了SQLServer系统中系统级的信息，如系统配置信息、登录信息、系统错误信息、系统存储过程、系统视图等。

另外，该数据库还记录了SQLServer的初始化信息。

因此，如果该数据不可用，则SQLServer将无法启动。

2．tempdb数据库

tempdb数据库为临时表和其他临时存储需求提供存储空间，是一个由SQLServer上所有数据库共享使用的工作空间。

每次启动SQLServer时，都会重新创建tempdb数据库，以便系统启动时，该数据库是空的。

当用户离开或关机时，临时数据库中创建的临时表会删除，当空间不够时，系统会自动增加它的空间，临时数据库上系统中负担较重的数据库，可以将他置于RAM中以提高数据库的性能。

3．model数据库

model数据库是中包含了每个数据所需要的系统表格，是SQLServer2005中的模板数据。

当创建一个用户数据库时，模板数据库中的内容会自动复制到所创建的用户数据库中。

通过修改模板数据库中的表格，可以实现用户自定义配置新建数据库的对象。

4．msdb数据库

SQLServer用msdb数据支持SQLServer代理、安排作业、报警等。

5．Resource数据库

Resource数据库是一个只读数据库，它包含了SQLServer2005中所有的数据库对象。

系统对象在物理上保存在Resource数据库文件中，在逻辑上显示在每个数据库的sys架构中。

3.2创建数据库

数据库是表、视图、存储过程、触发器等数据库对象的集合，是数据库管理系统和核心和基础。

创建数据库就是确定数据库的名称、文件名称、数据文件大小、所有者、是否自动增长等等信息的过程。

具有CREATEDATABASE、CREATEANYDATABASE、ALTERANYDATABASE权限的用户才可以执行创建数据库的操作。

注意：

在创建数据库时，系统自动将model数据库中的所用用户定义的对象都复制到新建的数据库中，用户可以在model系统数据中创建希望自动添加到所有新建数据库中的对象，例如后面要讲到的表、视图、数据类型、存储过程等。

在SQLServer2005中创建数据库主要有如下两种方法。

3.2.1使用SQLServerManagementStudio创建数据库

【案例3-1】创建名为"stu"的数据库,数据库包含一个主数据文件和一个事务日志文件。

主数据文件的逻辑名为"stu_data"，初始容量大小为3MB，最大容量为20MB，文件增长量为1MB；日志文件的逻辑名为"stu_log",初始容量大小为1MB，最大容量为10MB，文件增长量为10%，数据文件和日志文件都保存在D盘的根目录下。

操作步骤如下：

（1）启动SQLServerManagementStudio，在“对象资源管理器”窗口中，右击数据库节点，在弹出的快捷菜单中选择“新建数据库”命令，如图3-2所示。

图3-2选择“新建数据库”

（2）单击“新建数据库”命令，打开“新建数据库”对话框，如图3-3所示，在这个对话框中包含3个选择页：

“常规”、“选项”和“文件组”。

图3-3“新建数据库”对话框

（3）在常规选项页中，可以设置新建数据库名称、数据文件和日志文件的名称、初始大小、自动增长及路径信息等。

根据题意，输入数据库名称为“stu”，将主数据文件的逻辑名修改为“stu_data”，设置初始大小，单击自动增长选项中的“

”命令按钮，弹出“更改stu_data的自动增长设置”对话框，如图3-4所示，按题意要求修改自动增长设置，单击“确定”，返回“常规”选项页，并按题意修改文件保存路径为“D：

\”；用同样的方法设置日志文件的名称、大小、自动增长及保存路径。

图3-4更改stu_data的自动增长设置”对话框

（4）若需要增加辅助数据文件或日志文件，单击“添加”按钮，在“逻辑名称”栏中输入要添加的文件的逻辑名，在“文件类型”栏选择“数据”或“日志”，设置文件大小、增长方式及保存路径即可。

（5）在“选项”选择页中，可以设置数据库选项；在“文件组”页中可以添加“文件组”，数据库设置全部完成后，单击“确定”按钮，系统即按刚才的设置自动创建“stu”数据库。

（6）数据库创建完成后，在“对象资源管理器”窗口中，即可看到新建的“stu”数据库，如图3-5所示。

图3-5“stu”数据库

3.2.2使用Transact-SQL创建数据库

在SQLServer2005中，除使用SQLServerManagementStudio以图形界面创建数据库外,还可以在查询编辑器中用T-SQL语句中的CREATEDATABASE语句创建数据库,下面介绍如何使用CREATEDATABASE语句创建数据库。

使用CREATEDATABASE语句创建数据库的最简单的方式是：

“CREATEDATABASE数据库名”,该语句可以创建一个默认选项的数据库，其中数据库文件和日志文件按服务器属性中指定的默认位置存放，数据文件默认大小3MB，日志文件默认大小是1MB，并且数据文件和日志文件都自动增长。

【案例3-2】创建名为“testdb”的数据库,数据库包含一个主数据文件和一个事务日志文件。

主数据文件的逻辑名为“testdb_data”，操作系统文件名为“testdb_data.mdf”，初始容量大小为5MB，最大容量为20MB，文件增长量为15%；日志文件的逻辑名为“testdb_log”，操作系统文件名为“testdb_log.ldf”,初始容量大小为3MB，最大不受限制，文件增长量为1MB，数据文件和日志文件都保存在D盘的根目录下。

用CREATEDATABASE语句创建数据库的操作步骤如下：

（1）启动SQLServerManagementStudio，单击工具栏上的“新建查询”按钮，打开查询编辑窗口，输入如下SQL语句：

CREATEDATABASEtestdb

ONPRIMARY

（NAME='testdb_data',

FILENAME='d:

\testdb_data.mdf',

SIZE=5MB,

MAXSIZE=20MB,

FILEGROWTH=15%）

LOGON

（NAME='testdb_log',

FILENAME='d:

\testdb_log.ldf',

SIZE=3MB,

FILEGROWTH=1MB）

GO

（2）输入上面代码后，单击工具栏上的“分析”按钮或用“CTRL+F5”键，对输入代码进行分析查询，通过检查后，单击工具栏上的“执行”按钮或用“F5”键，消息框中返回“命令成功完成”表示完成数据库的创建。

（3）在“对象资源管理器”窗口中，右击数据库节点，在弹出的快捷菜单中选择“刷新”命令，就可看到新创建的“testdb”数据库，如下图3-6所示。

图3-6使用查询编辑器窗口创建“testdb”数据库

3.3使用SQLServerManagementStudio管理数据库

上一节我们介绍了数据库的创建方法，随着数据库使用的深入，需要进行数据库的日常管理和维护，包括：

查看数据库信息、修改数据库、设置数据库选项等等操作。

其中有手动和自动两种操作方式。

下面我们就以"stu"数据库为例，使用SQLServerManagementStudio可视化图形界面管理数据库。

3.3.1数据库信息查看

数据库的信息包括：

数据库基本信息、状态、空间大小、已经使用空间等信息。

使用SQLServerManagementStudio查看库数据库信息的方法和步骤如下：

（1）启动SQLServerManagementStudio，在“对象资源管理器”窗口中，展开“数据库”节点，右击要查看的"stu"数据库，在弹出的快捷菜单中选择“属性”命令。

（2）在弹出的“数据库属性”对话框中，包括有常规、文件、文件组、选项、权限、扩展属性。

镜像和事务日志传送8个选择项，任意单击选择项上的选项，可以看到该数据库的信息。

如图3-7所示，显示有关"stu"数据库的有关文件的信息。

图3-7【数据库属性】对话框

3.3.2设置数据库选项

数据库创建成功后，总是处在某个特定的状态中，如“在线或联机”、“恢复”、“紧急”状态等等。

为了更好的管理和维护数据库，需要掌握数据库的运行特征，这就需要对数据库的状态和选项有一定的了解。

表3-2列出了数据库的状态清单和特征描述：

表3-2数据库的状态

状态

描述

ONLINE

在线或联机状态，可以执行对数据库的访问。

OFFLINE

离线或脱机状态，数据库不能正常使用。

RESTORING

还原状态，正在还原原主文件组的一个或多个文件，这时，数据库不能使用。

RECOVERING

恢复状态，正在恢复数据库。

是一个临时状态。

若恢复成功，数据库处于自动在线状态；否则，数据库处于不能正常使用的可疑状态。

RECOVERY

PENGING

恢复未完成状态。

恢复过程中由于缺少资源造成的问题状态。

数据库不可使用。

必须执行其他操作来解决这种问题。

SUSPECT

可疑状态，主文件组可疑或可能被破坏。

数据库不能使用。

EMERGENCY

紧急状态，可以人工通过更改数据设置为该状态。

这时数据处于单用户模式和只读状态，只能由sysadmin固定服务器角色成员访问。

主要用于对数据库的故障排除。

数据库有很多选项，这些选项决定了数据库如何工作，使用SQLServerManagementStudio修改库数据库选项的方法和步骤如下：

（1）启动SQLServerManagementStudio，在“对象资源管理器”窗口中，展开“数据库”节点，右击要查看的"stu"数据库，在弹出的快捷菜单中选择“属性”命令，打开“数据库属性”对话框中。

（2）在弹出的“数据库属性”对话框中,单击“选项”选择项，就会显示数据库的各个选项。

如图3-8所示。

图3-8【数据库选项】对话框

在MicrosoftSQLServer2005系统中，共有约40个数据库选项，大致分为14个类别，详细的可以参见MicrosoftSQLServer2005系统帮助。

这里介绍一些常用的数据库选项。

●排序规则：

用于设置数据排序和比较的规则。

可以通过点击下拉列表框选择其中一种排序类别。

默认值是：

Chinese_PRC_CI_AS.

●恢复模式：

用于设置数据库的恢复和备份的操作模式。

供选择的有“完整”、“简单”、“大容量日志”三种模式。

●兼容级别：

用于设置与指定的MicrosoftSQLServer2005的早期版本兼容的某种数据库。

供选择的有“SQLServer7。

0（70）”、“SQLServer2000（80）”、“SQLServer2005（90）”三种级别。

●默认游标：

建立游标的控制作用域，LOCAL表示本地游标，GLOBAL表示全局游标。

默认值是“GLOBAL”。

●限制访问：

用于设置用户访问数据的模式。

供选择的有：

“Single（单用户模式）”、“Multipe（多用户模式）”、“Restricted（限制用户模式）”三种模式。

●自动收缩：

用于设置数据是否自动收缩。

默认值是：

“False”。

（3）设置完成后，单击“确定”按钮，所操作的设置立即生效。

3.3.3修改数据库

数据库创建成功后，可以根据需要随时修改数据库信息，除上节介绍的修改数据选项外，还包括：

数据库更名、扩大数据库容量、收缩数据库容量等操作。

1．数据库更名

数据库一旦创建成功后，一般情况下不要轻易的更改数据库的名称。

因为可能有一些应用程序已经使用了该数据库。

如果要更改了数据库名，相应的在其他使用了该数据库的地方也要做修改。

使用SQLServerManagementStudio修改库数据库名的方法和步骤如下：

（1）启动SQLServerManagementStudio，在“对象资源管理器”窗口中，展开“数据库”节点，右击要修改的"stu"数据库，在弹出的快捷菜单中选择“属性”命令，打开“数据库属性”对话框中。

（2）在弹出的“数据库属性”对话框中,单击“选项”选择项，将数据库选项中的“限制访问”设置为“Single”用户模式，此时，"stu"数据库旁边就有个单用户标志，即

。

（3）右击"stu"数据库，在弹出的快捷菜单中，选择“重命名”命令，此时，数据库名称变成可编辑状态，输入新的数据库名称即可。

（4）更名后，将数据库选项中的“限制访问”设置为“Multipe”用户模式。

2.扩大数据库容量

在创建数据时，用户都会指定容量。

如果数据库的数据量不断膨胀，超过指定的容量时，必须为它增加容量。

可以通过3种方式来扩大数据库，第一种：

设置数据库自动增长，可以在创建数据库时设置；第二种：

直接修改数据库的数据文件和日志文件的大小；第三种：

在数据库中增加辅助数据库文件和日志文件。

使用SQLServerManagementStudio修改库数据库名的方法和步骤如下：

（1）启动SQLServerManagementStudio，在“对象资源管理器”窗口中，展开“数据库”节点，右击要修改的"stu"数据库，在弹出的快捷菜单中选择“属性”命令，打开“数据库属性”对话框中。

（2）在弹出的“数据库属性”对话框中,单击“选项”选择项，单击数据库选项中的“文件”选项，此时，就可以修改"stu"数据库的初始大小和增长方式，操作方法与创建数据库时的方法相同。

若要通过增加数据辅助文件和日志文件来增加数据库的容量，可以执行如下操作：

在弹出的“数据库属性”对话框中,单击“选项”选择项，单击数据库选项中的“文件”选项，对话框中显示当前数据库的数据文件和日志文件信息，单击“添加”按钮，就可以增加需要的辅助数据文件和日志文件，操作方法与创建数据库时的方法相同。

（3）设置完成后，单击“确定”按钮，所操作的设置立即生效。

3.收缩数据库容量

当在数据库的使用过程中，发现创建数据库时指定的容量过大，为减少存储空间的浪费，我们可以通过操作来减少数据库的容量，称“收缩数据库”。

可以通过3种方式来收缩数据库，第一种：

设置数据库自动收缩，前面已经介绍过；第二种：

收缩整个数据库文件；第三种：

收缩指定的数据文件。

使用SQLServerManagementStudio收缩库数据库的方法和步骤如下：

（1）启动SQLServerManagementStudio，在【对象资源管理器】窗口中，展开【数据库】节点，右击要修改的【stu】数据库，在弹出的快捷菜单中选择【任务】→【收缩】→【数据库】命令，打开【收缩数据库】对话框，如图3-9所示。

图3-9【收缩数据库】对话框

（2）选择【在释放未使用的空间前重新组织文件】复选框，在【收缩后文件中的最大可用空间】后的方框内输入0~99的值。

设置完成后，单击【确定】按钮，完成数据库的收缩操作。

若要收缩指定的数据文件，则按以下步骤执行：

（1）启动SQLServerManagementStudio，在【对象资源管理器】窗口中，展开【数据库】节点，右击要修改的【stu】数据库，在弹出的快捷菜单中选择【任务】→【收缩】→【文件】命令，打开【收缩文件】对话框，如图3-10所示。

图3-10【收缩文件】对话框

（2）在【收缩文件】对话框中，有【数据库】、【数据库文件和文件组】、【收缩操作】三项，选择要收缩的文件类型，窗口中会显示当前数据库中的文件所占用的空间信息，根据需要在【收缩操作】中选择合适有方法即可。

设置完成后，单击【确定】按钮，完成文件的收缩操作。

3.3.4附加、分离和删除数据库

除上面所介绍的数据库操作外，数据库管理操作还包括附加数据库、分离数据库和删除数据库。

下面分别介绍这些操作。

1.附加数据库

使用SQLServerManagementStudio附加数据库名的方法和步骤如下：

（1）启动SQLServerManagementStudio，在【对象资源管理器】窗口中，右击【数据库】节点，在弹出的快捷菜单中选择【附加】命令，打开【附加数据库】对话框中。

如图3-11所示。

图3-11【附加数据库】对话框

（2）单击【添加】按钮，弹出【定位数据库文件】对话框，选择将要附加的数据库文件（即扩展名为.mdf的文件）,点击【确定】按钮返回。

（3）单击【确定】按钮，完成数据库的附加操作。

2．分离数据库

分离数据库是指将数据库从MicrosoftSQLServer实例中删除，但是该数据库的数据文件和日志文件仍保存在该服务器的硬盘上。

使用SQLServerManagementStudio分离数据库名的方法和步骤如下：

（1）启动SQLServerMan