实验一 数据库的管理(实验指导)

实验一数据库的管理

一、实验目的

1.掌握数据库创建与修改的基本方法。

2.熟悉数据导入与导出的方法。

3.掌握数据库分离与附加的操作方法。

二、实验环境

Windows XP/2000/2003/7操作系统、Microsoft SQL Server2005

三、相关知识

SQL Server 2005数据库分为系统数据库、数据库快照和用户数据库（包括示例数据库AdventureWorks等）。其中数据库快照是SQL Server 2005新增设的，数据库快照是数据库（源数据库）的只读、静态视图。每个数据库快照都与创建快照时存在的源数据库在事务上一致。

系统数据库包括master、model、msdb、resource和tempdb共五个，是保证SQL Server 2005服务器正常运行和管理数据库的数据库。

用户数据库是用户根据需要在SQL Server 2005服务器上创建的数据管理单位。对于一个SQL Server实例，最多可以创建32767个数据库。

SQL Server 2005的每个数据库有下面两类文件，缺一不可。

数据文件：用于存放所有的数据和数据库中建立的所有逻辑数据对象。数据文件又分为主要数据文件(扩展名为mdf) 和次要数据文件(扩展名为ndf) 两类。一个数据库必须有且只有一个主要数据文件，可以有0~n个次要数据文件。

日志文件：记录了用户对数据库进行的所有操作。日志文件对数据库的作用好比是“黑匣子”对飞机的作用。日志文件的扩展名为ldf，一个SQL Server 2005数据库至少要有一个日志文件，也可以有多个日志文件。

1.用户数据库的创建和删除

若要创建数据库，必须确定数据库的名称、所有者、大小以及存储该数据库的文件和文件组，数据库的名称必须遵循标识符指定的规则。创建数据库时，根据数据库中预期的最大数据量，应创建尽可能大的数据文件。创建数据库的用户将成为该数据库的所有者。

创建数据库有二种方法：利用对象资源管理器创建数据库、使用CREATE DATABASE 语句创建数据库。

（1）利用对象资源管理器创建数据库

步骤1：启动“SQL Server Management Studio”，在【数据库】上右击，选择快捷菜单中的【新建数据库】命令。

步骤2：在弹出“新建数据库”对话框中【常规】选择页的“数据库名称”文本框中，输入要创建的数据库的名称；在“数据库文件”信息中设置数据文件与日志文件的逻辑名称、文件类型、文件组、初始大小、自动增长要求、文件所在路径等。当需要更多数据库文件时，

可以单击下边的【添加】按钮。

步骤3：在【选项】选择页中，可以设置数据库的属性选项。

步骤4：在【文件组】选择页中，可以增加或删除文件组，

（2）使用CREATE DATABASE语句创建数据库。

步骤1：在SQL Server Management Studio中单击工具栏中【新建查询】按钮，弹出新建查询窗口。

步骤2：在新建的查询窗口中输入CREATE DATABASE语句，其语法格式如下：CREATE DATABASE database_name

[ON [PRIMARY] [ [ ,...n]

[ , [ ,...n ] ] ]

[[LOG ON { [ ,...n ] } ] ]

::=

{

( NAME=logical_file_name ,

FILENAME = 'os_file_name'

[ , SIZE=size [KB|MB|GB|TB] ]

[ , MAXSIZE={max_size [KB|MB|GB|TB] | UNLIMITED } ]

[ , FILEGROWTH =growth_increment [KB|MB|GB|TB|% ] ]

) [ ,...n ]

}

::=

{ FILEGROUP filegroup_name [ DEFAULT ]

[ ,...n ]

}

其中database_name：新数据库的名称。数据库名称在SQL Server 2005的实例中必须唯一，并且必须符合标识符规则。

PRIMARY：指定主文件组中的主数据文件。

LOG ON：指定日志文件的文件属性。

NAME=logical_file_name：指定文件的逻辑名称。FILENAME=' os_file_name '：指定操作系统(物理) 文件名称。SIZE：

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

FILEGROWTH：指定文件的自动增量。

：控制文件组属性。

创建完毕业后，可以“对象资源管理器”查看到相应的数据库名。如果要删除数据库只须在对象资源管理器中右击数据库名，选择“删除”命令即可。也可以可以使用命令进行删除：

DROP DATABASE database_name

2.数据库的修改

（1）在对象资源管理器中修改数据库

在“对象资源管理器”中右击创建好的数据库名，选择“属性”命令，查看相关数据库信息，包括常规，文件，文件组、权限，扩展属性等。也可在相应的文本框中修改数据库的属性。

（2）利用ALTER DATABASE命令修改数据库

修改命令ALTER DATABASE，具体的语法形式如下：

ALTER DATABASE databasename

{

ADD FILE [,…n] [to filegroup filegroupname] --添加数据文件

| ADD LOG FILE [,…n] --添加日志文件

| REMOVE FILE logical_file_name [WITH DELETE] --移除或删除日志文件

| MODIFY FILE --修改文件属性

| MODIFY NAME=new_databasename --修改文件名

| ADD FILEGROUP filegroup_name --添加文件组

| REMOVE FILEGROUP filegroup_name --移除文件组

| MODIFY FILEGROUP filegroup_name --修改文件组名

{

FILEGROUP_PROPERTY | NAME=new_filegroup_name

}

}

虽然ALTER DATABASE 命令语句看上去很多，但是在实际操作中，使用命令ALTER DA TABASE一次只能修改一种参数。

注意：

只有数据库管到员或具有CREATE DA TABASE权限的数据库所有者才有权执行ALTER DATABASE语句。

ALTER DATABASE 命令语句不能用来修改数据库库名，如果要修改数据库库名可以使用系统提供的存储过程sp_rename。

3.数据库的分离

分离数据库是指将数据库从SQL Server实例中删除，但数据库在其数据文件和事务日志文件中保持不变。之后，可以根据需要使用这些文件将数据库附加到任何SQL Server实例中去，包括分离该数据库的服务器。

如果存在下列任何情况，则不能分离数据库：

●已复制并发布的数据库。如果要进行复制，则数据库必须是未发布的。如果已经发

布则必须通过运行系统存储过程sp_replicationdboption 禁用发布后，才能分离数据

库。

●数据库中存在数据库快照。必须首先删除所有数据库快照，然后才能分离数据库。

●数据库处于可疑（SUSPECT）状态。在SQL Server 2005 中，无法分离可疑数据

库，必须将数据库置入紧急（EMERGENCY）状态，才能对其进行分离。

与创建和修改数据库相似，分离数据库也有二种方法，可以在对象资源管理器中分离数据库，也可在查询编辑器通过调用系统过程用命令语句方式实现数据库的分离（1）在对象资源管理器中分离数据库

在对象资源管理器中分离数据的操作方法为：右击要分离的数据库名，选择【任务】/【分离】命令。

（2）调用系统存储过程实现数据库的分离

调用系统存储过程sp_detach_db进行数据库分离命令语句的语法如下：

sp_detach_db [ @dbname= ] 'dbname'

[ , [ @skipchecks= ] 'skipchecks' ]

[ , [ @KeepFulltextIndexFile= ] 'KeepFulltextIndexFile' ]

其中，

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

[ @skipchecks = ] 'skipchecks' ：指定跳过还是运行UPDATE STATISTIC。skipchecks 的数据类型为nvarchar(10)，默认值为NULL。要跳过UPDA TE STATISTICS，应指定skipchecks的值为true。如果要显式运行UPDATE STATISTICS，则指定该值为false。默认情况下，执行UPDA TE STA TISTICS 以更新有关Microsoft SQL Server 2005 Database Engine 中的表数据和索引数据的信息。对于要移动到只读媒体的数据库，执行UPDA TE STA TISTICS 非常有用。

[ @KeepFulltextIndexFile = ] 'KeepFulltextIndexFile' ：指定在数据库分离操作过程中不要删除与正在被分离的数据库关联的全文索引文件。KeepFulltextIndexFile 的数据类型为nvarchar(10)，默认值为true。如果KeepFulltextIndexFile 为NULL 或false，则会删除与数据库关联的所有全文索引文件以及全文索引的元数据。

4.数据库的附加

分离了数据库之后，可以将与数据库相关的数据文件和日志文件复制到需要该数据库的地方，然后将其附加到数据库服务器中。同样，附加数据库的操作也可以分别利用对象资源管理器中和查询编辑器中通过利用命令语句实现数据库的附加。

（1）在对象资源管理器中附加数据库

在【对象资源管理器】中右击【数据库】，选择【附加】命令。

（2）利用命令语句实现数据库的分离

可以利用CREATE DATABASE命令语句中的“FOR ATTACH”参数来附加数据库。5.数据的导入

数据的导入是指从其他数据源中把数据复制到SQL Server数据库中，数据的导出是指从SQL Server数据库中把数据复制到其他数据源中。其他数据源可以是：同版本或旧版本的SQL Server、Excel、Access、通过OLE DB或ODBC来访问的数据源、纯文本文件等。

可以在“Microsoft SQL Server Management Studio”的对象资源管理器中导入其他系统中的数据。在对象资资源管理器中导入数据的方法：在对象资源管理器中右击要导入数据的数据库名，选择【任务】｜【导入数据】命令。然后根据导入向导一步一步实现从不同数据源中导入数据到选定的数据库中。

6.数据的导出

数据的导出操作是数据导入操作的逆操作，我们仍可以使用“SQL Server 导入导出向导”进行导出操作。其操作方法为：在对象资源管理器中右击要导出的数据库，选择【任务】｜【导出数据】命令。然后根据导出向导一步一步实现从所选数据库中将数据导出到其它数据源中。

四、实验内容

1．分别利用对象资源管理器和T-SQL命令创建数据库

2．分别利用对象资源管理器和T-SQL命令修改数据库

3．将Excel文件中的数据导入到数据库中

4．将SQL Server2005中的数据表导出到ACCESS数据库中

5．将数据库从SQL Server2005服务器中分离

五、实验步骤

（一）数据库的创建

1、使用Microsoft SQL Server Management Studio创建数据库“teachingDataA”，要求将数据库存放在D盘的teaching management目录下。

步骤1：在资源管理器中选择D盘根目录，新建一目录“teaching management”。

步骤2：打开Microsoft SQL Server Management Studio。在Windows的【开始】菜单中选择【程序】|【Microsoft SQL Server2005】，在“连接到服务器”窗口中选择相应的服务器和身份验证方式（这里选择“Windows验证方式”），如图1所示，单击“连接”按钮。

图1 连接服务器

步骤3：在【对象资源管理器】中右击【数据库】，选择命令【新建数据库】，如图2所示。

图2 选择【新建数据库】命令

步骤4：在“新建数据库”窗口中输入数据库名“teachingDataA”，将路径设置为D:\teaching management。

步骤5：完成后单击【确定】按钮。

2、使用CREATE DA TABASE命令创建数据库“teachingDataB”，要求将数据文件和日志文件均存放在D盘的“teaching management”中。

步骤1：打开Microsoft SQL Server Management Studio，单击工具栏中的按钮【新建查询】。

步骤2：在查询编辑器中输入建库命令，如图3所示：

图3 在查询编辑器中键入建库命令

步骤3：单击查询编辑器上方的【执行】按钮，可以看【消息】框中显示“命令已成功完成”，则表明已完成建库。

如果希望在“对象资源管理器”中查看新建的数据库，可以单击“对象资源管理器”中

的刷新按钮，然后展开数据库即可看到新建的数据库teachingDataA和teachingDataB。

如果在Windows中打开资源管理器，可以在D盘的teaching management文件夹中看到二个文件：teachingDataA.mdf，teachingDataA_log.ldf和teachingDataB.mdf，teachingDataB_log.ldf。

（二）数据库的修改

1、使用Microsoft SQL Server Management Studio修改数据库“teachingDataA”，将自动增长方式修改为2M，并添加次要数据文件teachingDataA3.ndf，其数据增长也设置为２M。

步骤1：在图4的【对象资源管理器】中右击teachingDataA，选择【属性】命令，然后在“数据库属性”窗口的【选择页】中选择【文件】，单击主文件行中的自动增长按钮，然后按要求将文件增长【按MB】设置为“２”，如图4所示，单击【确定】按钮。

图4 设置文件增长为“２MB”

步骤2：在“数据库属性”窗口中单击【添加】按钮，输入逻辑文件名teachingDataA3，并采用与步骤１类似的方法设置文件增长为２MB。

步骤3：单击【确定】按钮。

此时，可以在Windows资源管理器中看到D盘下的新文件teachingDataA3.ndf。

2、使用ALTER DATABASE命令进行修改数据库“teachingDataB”，将自动增长方式修改为2M，并添加次要数据文件teachingData3.ndf，其数据增长也设置为２M。。

步骤1：在查询编辑器中输入如图5所示命令，单击【执行】按钮，即可将自动增长方式修改为２MB。

图5 修改自动增长方式语句

步骤2：在查询编辑器中输入如图6所示命令，单击查询编辑器上方的【执行】命令。

图6 添加次要文件

（三）数据的导入

1、在“Microsoft SQL Server Management Studio”中将Access数据库文件coursedata.mdb 中的表course导入到数据库teachingDataA中。

步骤1：在“Microsoft SQL Server Management Studio”的【对象资源管理器】中右击数据库名“teachingDataA”，选择【任务】｜【导入数据】命令。

步骤2：在弹出的“欢迎使用SQL Server导入导出向导”的对话框中单击【下一步】，然后在图7所示的对话框中选择数据源类型“Microsoft Access”、文件名“coursedata.mdb”

等选项。

图7 选择要导入的Access文件

步骤3：单击【下一步】，弹出【选择目标】选项，在该对话框中选择要导入的数据的目标数据库“teachingDataA”，如图8所示。

图8 选择目标

步骤4：单击【下一步】，弹出【指定表复制或查询】选项，这里采用默认的选择。

步骤5：单击【下一步】，选择源表和目标如图8所示，这时如果单击【预览】按钮可以预览course数据表。

图8 选择源表course

步骤6：单击【下一步】，选择【立即执行】后，单击【完成】按钮。

步骤7：关闭“SQL Server导入导出向导”

完成后，在“Microsoft SQL Server Management Studio”中展开【对象资源管理器】中的【数据库】和【teachingData】，选中【表】，单击【对象资源管理器】中的刷新按钮，可以看到表“dbo.course”。右击“dbo.course”选择打开表命令，即可打开该表。

（四）数据的导出

将teachingDataA数据库中的表course导出到Excel表中，要求将表存在D盘teaching management文件夹中，保存为course.xls。

步骤1：在“Microsoft SQL Server Management Studio”的【对象资源管理器】中右击数据库名“teachingDataA”，选择【任务】｜【导出数据】命令。

步骤2：在弹出的“欢迎使用SQL Server导入导出向导”的对话框中单击【下一步】，然后在图9所示的对话框中选择文件名“teachingDataA”等选项。

图9 选择导出数据源

步骤3：单击【下一步】，然后在如图10所示的对话框中选择目标为“Microsoft Excel”，文件路径为“D:\teaching management\course.xls”。

图9 选择导出目标

步骤8：单击【下一步】，弹出【指定表复制或查询】选项，这里采用默认的选择。步骤4：单击【下一步】，选择源表和目标视图。（这时如果单击【预览】按钮可以预览

course数据表。单击【编辑】按钮可以进行列映射编辑）

步骤9：单击【下一步】，选择【立即执行】后，单击【完成】按钮。

步骤10：关闭“SQL Server导入导出向导”

完成后，可以在Windows的资源管理器中看到D盘的teaching management文件夹中有一个course.xls文件。

（五）数据库的分离

1、使用Microsoft SQL Server Management Studio将数据库teachingDataA分离。

步骤1：打开Microsoft SQL Server Management Studio，右击【对象资源管理器】中【数据库】下的【teachingDataA】选择命令【任务】|【分离】。

步骤2：单击【确定】按钮。

2、使用T-SQL命令语句将数据库teachingDataB分离。

步骤1：在查询编辑器中输入命令：SP_DETACH_DB 'teachingDataB'

步骤2：单击“执行”按钮。

六、实验要求

1．独立完成实验内容；

2．记录实验过程存在的问题，书写实验报告。

3. 完成思考题

七、思考题

1．如果出于对数据库的安全考虑，希望创建一个数据库文件teaching_DataC，要求将数据库的数据文件名和日志文件的逻辑文件名分别指定为teaching_Data1和teaching_Data2，而数据文件和日志文件名分别指定为Mycollege_Data.mdf，Mycollege_log.ldf，问：应该如何操作？

2．小黄在家里电脑上完成了老师规定的操作练习，建立了数据库“teachingData”，他想将相关的文件复制到U盘，递交给老师，但在复制过程中系统弹出如下图所示提示框，不允许小黄复制文件，小黄将认为可能是因为SQL Server正在使用的缘故，因此将其关闭，但系统仍不允许小黄复制文件，问：小黄如何操作才能复制文件并将文件提交给老师？

3．数据库的附加、分离与数据的导入、导出有什么不同？