第3章 SQL Server 数据库.docx

1、第3章 SQL Server 数据库第3章 SQL Server 2005数据库【学习目标】本章将主要介绍数据库的基本知识，包括数据库的设计、定义与维护过程。其中包括使用SSMS管理创建、管理数据库，以及使用T-SQL语句创建与管理数据库。本章学习要点： 数据库的管理问题 数据库文件与文件组的基本特征 数据库的创建与管理【学习导航】用户所关心的数据是分门别类地存放在数据库中的,表是放在数据库中的,除此之外视图、存储过程、触发器、约束等对象都存放在数据库中。数据库是数据库管理系统的核心内容,是存放数据库对象的容器,也是使用数据系统时首先要面对的对象。通俗地说，管理数据库就是管理数据文件。如何合理

2、的设计数据库，数据库中应该包含哪些文件，大小该如何设置，能否收缩与扩大数据库的容量、如何提高数据库的容错性、可管理性以及数据库的分离、备份、恢复、删除等内容。本章将详细研究这些问题的解决方案。表是数据库中最基本、最重要、最核心的对象，是组织数据的方式，是存储数据的地方。从某种意义上说，管理数据库说是管理数据库中的表。如何合理的设计表结构将直接影响到数据库中数据的使用效率。本章将全面讨论有关表的管理技术与方案。本章主要内容见图3-1所示的学习导航。图3-1 本章学习导航3.1 数据库3.1.1 数据库概述与术语数据库是表、视图、存储过程、触发器等数据库对象的集合，是数据库管理系统和核心。为了更好

3、的理解数据数据，应首先了解数据库的物理文件和逻辑文件、事务日志、文件组、数据库的物理空间、数据库状态等基本概念。在Microsoft SQL Server 2005系统中，一个数据库至少有一个数据库文件和一个事务日志文件。当然，数据库中也可以有多个数据库文件和多个事务日志。数据库文件存放数据库的数据和数据库的各种对象，事务日志用于存放事务日志。一个数据库中最多可发拥有32767个数据库文件和32767个日志文件。1SQL Server的数据库文件类型根据数据库文件的作用不同，可以将数据库文件分为发下三类：（1）主数据文件(Primary File)主数据文件是数据库的起点，用来存放数据库的数据

4、和数据库的启动信息。每个数据库都有且必须仅有一个主数据文件。主数据文件的默认扩展名是.MDF。主数据文件都有两个名称，操作系统文件名（物理文件名）和逻辑文件名。（2）次数据文件(Secondary File)次数据文件也叫辅助数据文件。次数据文件是可选的，主要用来存放不在主数据文件中的其他数据和对象。使用次数据文件可以扩展存储空间。次数据文件的默认扩展名是.NDF。（3）事务日志文件(Transaction Log)事务是一个单元的工作，该单元的工作要么全部完成，要么全部不完成。Microsoft SQL Server 2005系统具有事务的功能，可以保证数据库操作的一致性和完整性。SQL S

5、erver 系统使用数据库的事务日志来实现事务的功能。凡是对数据库进行增、删、改等操作，都会记录在事务日志文件中。如果数据库被破坏时可以使用该日志文件来恢复数据库的数据。每个数据库至少要有一个事务日志文件，事务日志文件的扩展名是.LDF。2SQL Server的数据库文件组文件组就是文件的逻辑集合。文件组可以把一些指定的文件组合在一起以方便管理和分配数据。以提高数据的查询速度，便于数据库的维护。例如：某个数据中，三个文件（data1.ndf、data2.ndf、data3.ndf）分别创建在三个不同的磁盘驱动器上，并且为他们指定了一个文件组fgroup1。创建表后可以明确的指定存放在fgrou

6、p1中。这样对该表中数据的查询将分布在这三个磁盘上同时进行，因此可以通过并行执行访问来提高查询的性能。通过使用文件组还可以简化数据库的维护工作：（1）备份和恢复单独的文件或文件组，而非整个数据库，可以提高效率。（2）将可维护性要求相近的表和索引分配到相同的文件组中。（3）为自己的文件组指定可维护性高的表。使用文件和文件组时，应该考虑以下因素：（1）一个文件或者文件组只能用于一个数据库，而不能用于多个数据库。（2）一个文件只能是某个文件组的成员，不能同时是多个文件给的成员。（3）数据库的数据信息和日志信息不能放在同一个文件或文件组中，数据文件和日志文件总是分开的。（4）日志文件永远也不能是任何文

7、件组的一部分。3SQL Server的物理空间及状态在Microsoft SQL Server 2005系统中，页是最小可管理物理空间，每页大小为8KB，即8192字节。每八个连续页称为一个区，即区的大小是64KB，区用于控制表、索引的存储。通过理解数据库的空间管理，可以估算数据库的设计尺寸。数据库的大小等于所有表大小与索引大小之和。数据库总是存在某个特定的状态中，例如：ONLINE状态表示数据库正处于正常的在线状态，可以对数据执行正常的操作。数据库的状态清单和特征如表3-1所示。表3-1 数据库的状态清单和特征ONLINE在线状态或联机状态，可以执行对数据的访问OFFINE离线状态或脱机状态

8、，数据库不能正常使用。可以人工设置。用户可以执行对处于这种状态的数据库文件的移动等处理RESTORING还原状态，正在还原主文件组的一个或多个文件。这时，数据库不能使用RECOVERING恢复状态，正在恢复数据库。这是一个临时性状态。如果恢复成功，则数据库自动处于在线状态。如果恢复失败，则数据处于不能正常使用的可疑状态RECOVERYPENDING恢复未完成状态。恢复过程中缺少资源造成的问题状态。数据库不可使用。必须执行其他操作来解决这种问题SUSPECT可疑状态，主文件组可疑或可能被破坏。数据库不能使用。必须执行其他操作来解决这种问题EMERGENCY紧急状态。可以人工设置数据为该状态。这时

9、数据库处于单用户模式或只读状态，只能由sysadmin固定服务器角色成员访问。主要用于对数据的故障排除3.1.2 SQL Server 2005的系统数据库SQL Server数据库包含系统数据和用户数据库。其中，系统数据库是安装SQL Server时系统自动安装的，系统数据存储着系统的重要信息，用来操作和管理系统；用户数据库是由用户自己创建，存放用户自己的数据。在SQL Server Management Studio环境中，SQL Server 2005包含4个可见的系统数据库：master、tempdb、model、msdb,和一个逻辑上不单独存在，隐藏的系统数据库Resource。1m

10、aster数据库master数据库是SQL Server的主数据库，包含了SQL Server系统中系统级的信息，如系统配置信息、登录信息、系统错误信息、系统存储过程、系统视图等。另外，该数据库还记录了SQL Server的初始化信息。因此，如果该数据不可用，则SQL Server将无法启动。2tempdb数据库tempdb数据库为临时表和其他临时存储需求提供存储空间，是一个由SQL Server上所有数据库共享使用的工作空间。每次启动SQL Server时，都会重新创建tempdb数据库，以便系统启动时，该数据库是空的。当用户离开或关机时，临时数据库中创建的临时表会删除，当空间不够时，系统会

11、自动增加它的空间，临时数据库上系统中负担较重的数据库，可以将他置于RAM中以提高数据库的性能。3model数据库model数据库是中包含了每个数据所需要的系统表格，是SQL Server2005中的模板数据。当创建一个用户数据库时，模板数据库中的内容会自动复制到所创建的用户数据库中。通过修改模板数据库中的表格，可以实现用户自定义配置新建数据库的对象。4msdb数据库SQL Server用msdb数据支持SQL Server代理、安排作业、报警等。5Resource数据库Resource数据库是一个只读数据库，它包含了SQL Server2005中所有的数据库对象。系统对象在物理上保存在Reso

12、urce数据库文件中，在逻辑上显示在每个数据库的sys架构中。3.2 创建数据库 数据库是表、视图、存储过程、触发器等数据库对象的集合，是数据库管理系统和核心和基础。创建数据库就是确定数据库的名称、文件名称、数据文件大小、所有者、是否自动增长等等信息的过程。 具有CREATE DATABASE、CREATE ANY DATABASE、ALTER ANY DATABASE 权限的用户才可以执行创建数据库的操作。注意：在创建数据库时，系统自动将model数据库中的所用用户定义的对象都复制到新建的数据库中，用户可以在model系统数据中创建希望自动添加到所有新建数据库中的对象，例如后面要讲到的表、视

13、图、数据类型、存储过程等。在SQL Server 2005中创建数据库主要有如下两种方法。3.2.1 使用SQL Server Management Studio创建数据库【案例3-1】创建名为stu的数据库,数据库包含一个主数据文件和一个事务日志文件。主数据文件的逻辑名为stu_data，初始容量大小为3MB，最大容量为20MB，文件增长量为1MB；日志文件的逻辑名为stu_log , 初始容量大小为1MB，最大容量为10MB，文件增长量为10% ，数据文件和日志文件都保存在D盘的根目录下。操作步骤如下：（1）启动SQL Server Management Studio，在“对象资源管理器”

14、窗口中，右击数据库节点，在弹出的快捷菜单中选择“新建数据库”命令，如图3-2所示。图3-2 选择“新建数据库”（2）单击“新建数据库”命令，打开“新建数据库”对话框，如图3-3所示，在这个对话框中包含3个选择页：“常规”、“选项”和“文件组”。图3-3 “新建数据库”对话框（3）在常规选项页中，可以设置新建数据库名称、数据文件和日志文件的名称、初始大小、自动增长及路径信息等。根据题意，输入数据库名称为“stu”，将主数据文件的逻辑名修改为“stu_data”，设置初始大小，单击自动增长选项中的“”命令按钮，弹出“更改stu_data的自动增长设置”对话框，如图3-4所示，按题意要求修改自动增长

15、设置，单击“确定”，返回“常规”选项页，并按题意修改文件保存路径为“D：”；用同样的方法设置日志文件的名称、大小、自动增长及保存路径。图3-4更改stu_data的自动增长设置”对话框（4）若需要增加辅助数据文件或日志文件，单击“添加”按钮，在“逻辑名称”栏中输入要添加的文件的逻辑名，在“文件类型”栏选择“数据”或“日志”，设置文件大小、增长方式及保存路径即可。（5）在“选项”选择页中，可以设置数据库选项；在“文件组”页中可以添加“文件组”，数据库设置全部完成后，单击“确定”按钮，系统即按刚才的设置自动创建“stu”数据库。（6）数据库创建完成后，在“对象资源管理器”窗口中，即可看到新建的“s

16、tu”数据库，如图3-5所示。图3-5 “stu”数据库3.2.2 使用Transact-SQL创建数据库在SQL Server 2005中，除使用SQL Server Management Studio以图形界面创建数据库外,还可以在查询编辑器中用T-SQL语句中的CREATE DATABASE语句创建数据库,下面介绍如何使用 CREATE DATABASE语句创建数据库。使用CREATE DATABASE语句创建数据库的最简单的方式是：“CREATE DATABASE 数据库名”,该语句可以创建一个默认选项的数据库，其中数据库文件和日志文件按服务器属性中指定的默认位置存放，数据文件默认大小

17、3MB，日志文件默认大小是1MB，并且数据文件和日志文件都自动增长。【案例3-2】创建名为“testdb”的数据库,数据库包含一个主数据文件和一个事务日志文件。主数据文件的逻辑名为“testdb_data”，操作系统文件名为“testdb_data.mdf”，初始容量大小为5MB，最大容量为20MB，文件增长量为15%；日志文件的逻辑名为“testdb _log” ，操作系统文件名为“testdb_log.ldf”, 初始容量大小为3MB，最大不受限制，文件增长量为1MB ，数据文件和日志文件都保存在D盘的根目录下。用CREATE DATABASE语句创建数据库的操作步骤如下：（1）启动SQL

18、 Server Management Studio，单击工具栏上的“新建查询”按钮，打开查询编辑窗口，输入如下SQL语句：CREATE DATABASE testdbON PRIMARY(NAME=testdb_data, FILENAME=d:testdb_data.mdf,SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=15%)LOG ON(NAME=testdb_log,FILENAME=d:testdb_log.ldf,SIZE=3MB,FILEGROWTH=1MB)GO（2） 输入上面代码后，单击工具栏上的“分析”按钮或用“CTRL+F5”键，对输入代码进行分析查询

19、，通过检查后，单击工具栏上的“执行”按钮或用“F5”键，消息框中返回“命令成功完成”表示完成数据库的创建。（3）在“对象资源管理器”窗口中，右击数据库节点，在弹出的快捷菜单中选择“刷新”命令，就可看到新创建的“testdb”数据库，如下图3-6所示。图3-6 使用查询编辑器窗口创建“testdb”数据库3.3使用SQL Server Management Studio管理数据库 上一节我们介绍了数据库的创建方法，随着数据库使用的深入，需要进行数据库的日常管理和维护，包括：查看数据库信息、修改数据库、设置数据库选项等等操作。其中有手动和自动两种操作方式。下面我们就以stu数据库为例，使用SQL

20、Server Management Studio可视化图形界面管理数据库。3.3.1 数据库信息查看数据库的信息包括：数据库基本信息、状态、空间大小、已经使用空间等信息。使用SQL Server Management Studio查看库数据库信息的方法和步骤如下：（1）启动SQL Server Management Studio，在“对象资源管理器”窗口中，展开“数据库”节点，右击要查看的stu数据库，在弹出的快捷菜单中选择“属性”命令。 (2) 在弹出的“数据库属性”对话框中，包括有常规、文件、文件组、选项、权限、扩展属性。镜像和事务日志传送8个选择项，任意单击选择项上的选项，可以看到该数据

21、库的信息。如图3-7所示，显示有关stu数据库的有关文件的信息。图3-7 【数据库属性】对话框3.3.2 设置数据库选项数据库创建成功后，总是处在某个特定的状态中，如“在线或联机”、“恢复”、“紧急”状态等等。为了更好的管理和维护数据库，需要掌握数据库的运行特征，这就需要对数据库的状态和选项有一定的了解。表3-2 列出了数据库的状态清单和特征描述：表3-2 数据库的状态状态描述ONLINE在线或联机状态，可以执行对数据库的访问。OFFLINE离线或脱机状态，数据库不能正常使用。RESTORING还原状态，正在还原原主文件组的一个或多个文件，这时，数据库不能使用。RECOVERING恢复状态，正

22、在恢复数据库。是一个临时状态。若恢复成功，数据库处于自动在线状态；否则，数据库处于不能正常使用的可疑状态。RECOVERYPENGING恢复未完成状态。恢复过程中由于缺少资源造成的问题状态。数据库不可使用。必须执行其他操作来解决这种问题。SUSPECT可疑状态，主文件组可疑或可能被破坏。数据库不能使用。EMERGENCY紧急状态，可以人工通过更改数据设置为该状态。这时数据处于单用户模式和只读状态，只能由sysadmin固定服务器角色成员访问。主要用于对数据库的故障排除。数据库有很多选项，这些选项决定了数据库如何工作，使用SQL Server Management Studio修改库数据库选项的

23、方法和步骤如下：（1）启动SQL Server Management Studio，在“对象资源管理器”窗口中，展开“数据库”节点，右击要查看的stu数据库，在弹出的快捷菜单中选择“属性”命令，打开“数据库属性”对话框中。（2）在弹出的“数据库属性”对话框中,单击“选项”选择项，就会显示数据库的各个选项。如图3-8所示。图3-8 【数据库选项】对话框在Microsoft SQL Server 2005系统中，共有约40个数据库选项，大致分为14个类别，详细的可以参见Microsoft SQL Server 2005系统帮助。这里介绍一些常用的数据库选项。 排序规则：用于设置数据排序和比较的规则

24、。可以通过点击下拉列表框选择其中一种排序类别。默认值是：Chinese_PRC_CI_AS. 恢复模式：用于设置数据库的恢复和备份的操作模式。供选择的有“完整”、“简单”、“大容量日志”三种模式。 兼容级别：用于设置与指定的Microsoft SQL Server 2005的早期版本兼容的某种数据库。供选择的有“SQL Server 7。0（70）”、“SQL Server 2000（80）”、“SQL Server 2005(90)”三种级别。 默认游标：建立游标的控制作用域，LOCAL表示本地游标，GLOBAL表示全局游标。默认值是“GLOBAL”。 限制访问：用于设置用户访问数据的模式。

25、供选择的有：“Single(单用户模式)”、“Multipe(多用户模式)”、“Restricted(限制用户模式)”三种模式。 自动收缩：用于设置数据是否自动收缩。默认值是：“False”。 （3）设置完成后，单击“确定”按钮，所操作的设置立即生效。3.3.3 修改数据库数据库创建成功后，可以根据需要随时修改数据库信息，除上节介绍的修改数据选项外，还包括：数据库更名、扩大数据库容量、收缩数据库容量等操作。1数据库更名数据库一旦创建成功后，一般情况下不要轻易的更改数据库的名称。因为可能有一些应用程序已经使用了该数据库。如果要更改了数据库名，相应的在其他使用了该数据库的地方也要做修改。使用SQL

26、 Server Management Studio修改库数据库名的方法和步骤如下：（1）启动SQL Server Management Studio，在“对象资源管理器”窗口中，展开“数据库”节点，右击要修改的stu数据库，在弹出的快捷菜单中选择“属性”命令，打开“数据库属性”对话框中。（2）在弹出的“数据库属性”对话框中,单击“选项”选择项，将数据库选项中的“限制访问”设置为“Single”用户模式，此时，stu数据库旁边就有个单用户标志，即。（3）右击stu数据库，在弹出的快捷菜单中，选择“重命名”命令，此时，数据库名称变成可编辑状态，输入新的数据库名称即可。（4）更名后，将数据库选项中的

27、“限制访问”设置为“Multipe”用户模式。2. 扩大数据库容量在创建数据时，用户都会指定容量。如果数据库的数据量不断膨胀，超过指定的容量时，必须为它增加容量。可以通过3种方式来扩大数据库，第一种：设置数据库自动增长，可以在创建数据库时设置；第二种：直接修改数据库的数据文件和日志文件的大小；第三种：在数据库中增加辅助数据库文件和日志文件。使用SQL Server Management Studio修改库数据库名的方法和步骤如下：（1）启动SQL Server Management Studio，在“对象资源管理器”窗口中，展开“数据库”节点，右击要修改的stu数据库，在弹出的快捷菜单中选择“

28、属性”命令，打开“数据库属性”对话框中。（2）在弹出的“数据库属性”对话框中,单击“选项”选择项，单击数据库选项中的“文件”选项，此时，就可以修改stu数据库的初始大小和增长方式，操作方法与创建数据库时的方法相同。若要通过增加数据辅助文件和日志文件来增加数据库的容量，可以执行如下操作：在弹出的“数据库属性”对话框中,单击“选项”选择项，单击数据库选项中的“文件”选项，对话框中显示当前数据库的数据文件和日志文件信息，单击“添加”按钮，就可以增加需要的辅助数据文件和日志文件，操作方法与创建数据库时的方法相同。（3）设置完成后，单击“确定”按钮，所操作的设置立即生效。3. 收缩数据库容量当在数据库的

29、使用过程中，发现创建数据库时指定的容量过大，为减少存储空间的浪费，我们可以通过操作来减少数据库的容量，称“收缩数据库”。可以通过3种方式来收缩数据库，第一种：设置数据库自动收缩，前面已经介绍过；第二种：收缩整个数据库文件；第三种：收缩指定的数据文件。使用SQL Server Management Studio收缩库数据库的方法和步骤如下：（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，右击要修改的【stu】数据库，在弹出的快捷菜单中选择【任务】【收缩】【数据库】命令，打开【收缩数据库】对话框，如图3-9所示。图3-9 【收缩数

30、据库】对话框（2）选择【在释放未使用的空间前重新组织文件】复选框，在【收缩后文件中的最大可用空间】后的方框内输入099的值。设置完成后，单击【确定】按钮，完成数据库的收缩操作。若要收缩指定的数据文件，则按以下步骤执行：（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，右击要修改的【stu】数据库，在弹出的快捷菜单中选择【任务】【收缩】【文件】命令，打开【收缩文件】对话框，如图3-10所示。图3-10 【收缩文件】对话框（2）在【收缩文件】对话框中，有【数据库】、【数据库文件和文件组】、【收缩操作】三项，选择要收缩的文件类型，窗口

31、中会显示当前数据库中的文件所占用的空间信息，根据需要在【收缩操作】中选择合适有方法即可。设置完成后，单击【确定】按钮，完成文件的收缩操作。3.3.4 附加、分离和删除数据库除上面所介绍的数据库操作外，数据库管理操作还包括附加数据库、分离数据库和删除数据库。下面分别介绍这些操作。1. 附加数据库使用SQL Server Management Studio附加数据库名的方法和步骤如下：（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，右击【数据库】节点，在弹出的快捷菜单中选择【附加】命令，打开【附加数据库】对话框中。如图3-11所示。图3-11 【附加数据库】对话框（2）单击【添加】按钮，弹出【定位数据库文件】对话框，选择将要附加的数据库文件（即扩展名为.mdf的文件）,点击【确定】按钮返回。（3）单击【确定】按钮，完成数据库的附加操作。2分离数据库分离数据库是指将数据库从Microsoft SQL Server实例中删除，但是该数据库的数据文件和日志文件仍保存在该服务器的硬盘上。使用SQL Server Management Studio分离数据库名的方法和步骤如下：（1）启动SQL Server Man