数据库原理与应用实验2(二版)2

1、数据库原理与应用实验教材实验二 数据库的创建与管理一、实验目的及要求本实验要求学生掌握能够运用企业管理器熟练创建数据表，并对数据进行各种方式的访问。主要要求有：l 掌握在企业管理器中创建数据库l 掌握数据库的SQL创建方式。l 掌握掌握CREATE、ALTER、DROP语句的使用。l 掌握数据库分离、附加、收缩的SQL实现方式。l 学会使用系统存储过程查看数据库信息。二、实验原理及背景知识（一）SQL Server 2005数据库系统数据库1、master数据库Master数据库存储了SQL Server系统的所有系统级信息，包括实例范围的元数据（例如登录账户、端点、链接服务器等）、系统设置参

2、数、所有其它数据库是否存在，以及这些数据库文件的位置、SQL Server实例的初始化信息。使用该数据库时，一旦系统级别的信息发生改变，就应该进行备份，不要在master数据库中创建用户对象。2、model数据库该系统数据库主要是保存实例上所有数据库的模板，也就是说，在创建新数据库时必须使用该系统数据库的有关内容。每次创建新的数据库，将通过复制model数据库中的内容来创建数据库，如果修改了model数据库的内容，则之后创建的所有数据库都将继承这些修改，例如：可以设置权限或数据库选项、添加表、函数和存储过程等对象。3、msdb数据库该系统数据库用于SQL Server 2005代理服务，代理服

3、务主要用于DBA来自动执行某些管理任务，例如在每天的晚上18:00自动备份数据库等，凡是这些自动执行的任务就需要代理服务的支持。如果不需要执行这些代理服务功能，则该系统数据库可有可无。4、tempdb数据库该系统数据库是连接到SQL Server 实例的所有用户都可用的全局资源，它保存所有临时表和临时存储过程，不能在该数据库中保存永久数据。5、resource数据库该系统数据库是SQL Server 2005第五个“隐藏的”数据库，是只读数据库，它包含了SQL Server 2005实例中的所有系统对象，如系统表和系统视图等。SQL Server 系统对象（如sys.objects）在物理上持

4、续存在于resource数据库中，但在逻辑上，他们出现在每个数据库的sys架构中。该数据库无法使用可以列出所有数据库的一般SQL命令来看到它。Resource 数据库的 ID 始终是 32767。若要确定 Resource 数据库的版本号，请使用：SELECT SERVERPROPERTY('ResourceVersion');若要访问系统对象的 SQL 定义，请使用 OBJECT_DEFINITION 函数：SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects');（二）SQL Server 2005数据库的结构和组成

5、1、数据库逻辑结构从逻辑角度，SQL Server 2005将数据库组织成为各种数据库对象，如数据表、视图、索引、数据类型、存储过程和触发器等。这些数据库对象是用户使用数据库的基本单位。在这些对象中，数据表是由行和列组成的二维表，用来存储大量的数据，视图是一种虚拟的数据表，它好像数据表的窗户，索引是将数据表中若干列作为整个表的引导，就像图书索引可加快查询图书速度一样；数据类型是存储在数据表中的各种数据的类型；存储过程是用T-SQL语句编写的一组操纵数据库的语句集合。2、数据库物理结构从物理角度看，SQL Server 2005数据库以多种操作系统文件的形式存储在计算机硬盘上，一般一个数据库被组

6、织成数据文件和日志文件两种类型的文件。在SQL Server 2005中，数据文件分为主数据文件和二级数据文件两种形式，使用一组物理文件来映射数据库，数据库中所有数据和对象都存储在如下的三种操作系统文件中。（1）主数据库文件：该文件包含数据库的启动信息，用来存放数据，它是所有数据库文件的起点（包含指向其他数据库文件的指针）；每个数据库都必须有而且仅有一个主数据文件；主数据文件的默认扩展名为*.mdf。（2）二级数据文件：也用来存放数据。一个数据库中，可以没有二级数据文件，也可以拥有多个，如果数据库文件可以包含数据库中所有数据，则数据库就不需要二级数据文件，如果数据库很大，主数据文件的容量超过系

7、统的限制，就需要设置一个活多个二级数据文件。二级数据文件的扩展名为*.ndf（3）日志文件：用来存放事务日志，记录了用户对数据库的所有操作，因此，根据日志文件信息可以恢复一个数据库。每个数据库至少有一个日志文件，也可以拥有多个日志文件；日志文件的扩展名为*.ldf。3、文件组数据库文件组（File Group）是数据库中数据文件的逻辑集合，包含一个或多个数据文件，使用文件组可以实现将数据分布在多个磁盘上并在查询过程中使用并行线程同时扫描多个磁盘，有效地提高数据库的读写速度。如：假如一个文件组中的数据文件分布在不同的硬盘驱动器上，在创建数据表时，表中的数据便分布在不同的硬盘上，读写表时，可以通过

8、并行线程操作，大大提高数据的存取效率。数据库文件组分为以下3种类型：（1）主文件组（Primary）：包含主数据文件和没有被包含在其他文件组里的文件。数据库的系统表都被包含在主文件组里。（2）自定义文件组（user_defined）：包含所有在使用Create database或Alter Database时用FileGroup关键字进行约束的文件。（3）默认文件组：包含所有在创建时没有指定文件组的表、索引，以及text、ntext和image数据类型的数据。创建数据库文件组一般遵循的原则为：（1）一个文件或文件组只能被一个数据库使用。（2）一个文件只能属于一个文件组。数据和事务日志不能共存于

9、同一个文件或文件组上。（3）日志文件不能属于文件组（三）创建数据库语法格式CREATE DATABASE database_name ON PRIMARY ( NAME = logical_file_name , FILENAME = 'os_file_name' , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) ,.n , < filegroup > ,.n LOG ON(NAME=logical_file_name,FILENAME=os_file_n

10、ame,SIZE=size,MAXSIZE=max_size|UNLIMTED,FILEGROWTH=grow_increment),n（四）修改数据库语法格式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 |

11、 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 > （五）收缩数据库语法格式 DBCC shrinkdatabase（database_name,target_percent） with

12、 no_infomsgs（六）删除数据库语法格式DROP DATABASE database_name三、实验内容及步骤：基本实验内容（一）企业管理管理数据库1、建立一个名为stumanage的数据库，其中主数据文件的逻辑名称为stumanage_mdf，物理文件的名称为d: datastumanage.mdf，大小为20MB，最大为50MB，以25%的速度增长，数据库的日志文件为stumanage_log，物理文件的名称为d: data stumanage.ldf，大小为3MB，最大为10MB，以1MB的速度增长。其操作步骤如下：（1）打开SQL Server Management Stud

13、io，右击“数据库”，如图2-2-1。在弹出的快捷菜单中选择“新建数据库”， 弹出图2-2-2。图2-1-1 选择“新建数据库”图2-2-2 “新建数据库”的“常规”标签页（2）在图2-2-2的“常规”标签页中，定义数据库数据文件的逻辑文件名称stumanage_mdf，在路径下设置物理文件路径为d: data，初始大小位置设置大小为20MB，打开自动增长对话框，如图2-2-2所示，在该子对话框中设置文件自动增长方式为25%，文件最大为50MB，日志文件采用和数据文件相似的方法进行属性设置。注意：物理文件名一经确定，便不能更改，且路径d: data在本机上必须存在，否则，需要在d盘上建好dat

14、a文件夹后才能建立数据库。2、在第二个磁盘上添加三个次数据文件：Data1_ndf、Data2_ndf 和 Data3_ndf，Data1_ndf的物理文件名称为e:datadata1.ndf，Data2_ndf 和 Data3_ndf的物理文件名称分别为d:datadata2.ndf和e:datadata3.ndf，三个数据文件的其余属性自己设置。将Data1_ndf组成文件组fgroup1，将 Data2_ndf 和 Data3_ndf组成文件组fgroup2。其步骤如下：（1）在图2-2-2中，选择左侧的“文件组”选项页，如图2-2-3，在该页面上，点击下方的“添加”按钮，在名称列输入文

15、件组名称fgroup1，用同样的方法再添加一个文件组fgroup2。图2-2-4 “新建数据库”的“文件组”标签页（2）再次回到图2-2-3的页面，点击下方的“添加”按钮，在逻辑名称列输入data1_ndf，并进行属性的设置，在“文件组”列打开文件组下拉框，如图2-2-5，选择fgroup1文件组，用同样的方法，添加data2_ndf和data3_ndf数据文件，并将其添加到相应的文件组中。图2-2-5 将数据文件添加到文件组注意：日志文件不属于任何文件组，故日志文件没有可选的文件组。3、修改数据库。右击“数据库”，在出现的快捷菜单上选择“刷新”，则所建立的数据库stumanage将出现在左侧

16、的数据库列表中。右击“stumanage”，如图2-2-6，在出现的快捷菜单中选择“属性”，在如图2-2-7的界面中可以修改、添加和删除数据库文件和文件组。 图2-2-6 选择“属性” 图2-2-7 修改数据库选项页注意：此时，文件路径不能再进行修改，且对文件大小进行修改时，修改的文件大小要大于或等于修改前数据文件大小。4、重命名数据库为pupil。在所建立的数据库上点击右键，选择“重命名”，输入新的数据库名称“pupil”，完成数据库更名。注意：一般在数据库创建好之后，不要更改数据库名称5、删除所建立的数据库pupil。在所建立的数据库上点击右键，选择“删除”，在出现的删除对话框中，选择“关

17、闭现有连接”，点击确定，完成数据库删除。（二）SQL命令管理数据库1、创建数据库stumanage：建立一个名为stumanage的数据库，其中主数据文件的逻辑名称为stumanage_data，物理文件名称为d:datastumanage_mdf，大小为20MB，最大为50MB，以25%的速度增长，数据库的日志文件为stumanage_log,操作系统文件的名称为d:datastumanage_ldf，大小为3MB，最大为10MB，以1MB的速度增长。2、修改数据库stumanage（1）在该数据库上添加一个文件组，文件组逻辑名为group17、删除数据库Drop database new_stumanage：高级实验内容1、按照下面要求使用SQL命令创建一个数据库。名称：Test主要数据文件：逻辑名称：Test_M_Data物理名称：Test_MData初始大小：4M