数据库原理与应用：第4章 SQL Server 2005数据库的创建和管理

《数据库原理与应用：第4章 SQL Server 2005数据库的创建和管理》由会员分享，可在线阅读，更多相关《数据库原理与应用：第4章 SQL Server 2005数据库的创建和管理（34页珍藏版）》请在装配图网上搜索。

1、数据库原理与应用数据库原理与应用第四讲第四讲 SQL Server 2005数据库数据库的创建的创建和管理和管理主要内容n SQL Server 特点特点n SQL Server的安装和配置的安装和配置n SQL Server 工具和实用程序工具和实用程序 4.1 数据库的存储结构 数据库的存储结构分为逻辑存储结构和物理存储结构两种。数据库的存储结构分为逻辑存储结构和物理存储结构两种。数据库的逻辑存储结构指的是数据库是由哪些性质的信息所数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成组成。实际上，实际上，SQL Server的数据库是由诸如表、视图、索的数据库是由诸如表、视图、索引等各

2、种不同的数据库对象所组成。引等各种不同的数据库对象所组成。数据库的物理存储结构是讨论数据库文件是如何在磁盘上存数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个数据件和事务日志文件组成，一个数据库至少应该包含一个数据库文件和一个事务日志文件。库文件和一个事务日志文件。4.1 数据库的存储结构4.1.1 数据库文件数据库文件 1主数据库文件（主数据库文件（Primary Database File）一个数据库可以有一个或多个数据库文件，一个数据

3、库文件只一个数据库可以有一个或多个数据库文件，一个数据库文件只能属于一个数据库。当有多个数据库文件时，有一个文件被定能属于一个数据库。当有多个数据库文件时，有一个文件被定义为主数据库文件（简称为主文件），其扩展名为义为主数据库文件（简称为主文件），其扩展名为mdf。4.1 数据库的存储结构主数据库文件用来存储数据库的启动信息以及部主数据库文件用来存储数据库的启动信息以及部分或者全部数据，是所有数据库文件的起点，包分或者全部数据，是所有数据库文件的起点，包含指向其它数据库文件的指针。一个数据库只能含指向其它数据库文件的指针。一个数据库只能有一个主数据库文件。有一个主数据库文件。4.1 数据库的存

4、储结构2辅助数据库文件（辅助数据库文件（Secondary Database File）用于存储主数据库文件中未存储的剩余数据和数用于存储主数据库文件中未存储的剩余数据和数据库对象，一个数据库可以没有辅助数据库文件，据库对象，一个数据库可以没有辅助数据库文件，但也可以同时拥有多个辅助数据库文件。但也可以同时拥有多个辅助数据库文件。辅助数据库文件的扩展名为辅助数据库文件的扩展名为ndf（简称为辅助文（简称为辅助文件）。件）。4.1 数据库的存储结构3事务日志文件事务日志文件 存储数据库的更新情况等事务日志信息存储数据库的更新情况等事务日志信息，当数据当数据库损坏时，管理员使用事务日志恢复数据库。

5、库损坏时，管理员使用事务日志恢复数据库。每一个数据库至少必须拥有一个事务日志文件，每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩而且允许拥有多个日志文件。事务日志文件的扩展名为展名为ldf，日志文件的大小至少是，日志文件的大小至少是512KB。SQL Server事务日志采用提前写入的方式事务日志采用提前写入的方式。4.1 数据库的存储结构注意：注意：SQL Server 2000中的数据和事务日志文中的数据和事务日志文件不能存放在压缩文件系统或象共享网络目录等件不能存放在压缩文件系统或象共享网络目录等远程的网络驱动器上。远程的网络驱动器上。SQL Ser

6、ver 2000的文件拥有两个名称，即逻辑的文件拥有两个名称，即逻辑文件名和物理文件名。当使用文件名和物理文件名。当使用Transact-SQL命命令语句访问某一个文件时，必须使用该文件的逻令语句访问某一个文件时，必须使用该文件的逻辑名辑名。物理文件名是文件实际存储在磁盘上的文件名，物理文件名是文件实际存储在磁盘上的文件名，而且可包含完整的磁盘目录路径。而且可包含完整的磁盘目录路径。4.1 数据库的存储结构4.1.2 数据库文件组数据库文件组 利用文件组可以使服务器的性能得到提高。利用文件组可以使服务器的性能得到提高。主文件组中包含了所有的系统表，当建立数据库主文件组中包含了所有的系统表，当建

7、立数据库时，主文件组包括主数据库文件和未指定组的其时，主文件组包括主数据库文件和未指定组的其他文件。他文件。用户定义文件组中可以指定一个缺省文件组，那用户定义文件组中可以指定一个缺省文件组，那么在创建数据库对象时如果没有指定将其放在哪么在创建数据库对象时如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中。如一个文件组中，就会将它放在缺省文件组中。如果没有指定缺省文件组，则主文件组为缺省文件果没有指定缺省文件组，则主文件组为缺省文件组。组。4.1 数据库的存储结构一个文件只能存在于一个文件组中，一个文件组一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用；日志文件是独立的

8、，也只能被一个数据库使用；日志文件是独立的，它不能作为任何文件组的成员。它不能作为任何文件组的成员。本章首页4.2 创建和删除数据库 4.2.1 创建数据库创建数据库 创建数据库需要一定许可，在默认情况下，只有创建数据库需要一定许可，在默认情况下，只有系统管理员和数据库拥有者可以创建数据库。数系统管理员和数据库拥有者可以创建数据库。数据库被创建后，创建数据库的用户自动成为该数据库被创建后，创建数据库的用户自动成为该数据库的所有者。据库的所有者。创建数据库的过程实际上就是为数据库设计名称、创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等。设计所占用的存储空间

9、和存放文件位置的过程等。4.2 创建和删除数据库1使用向导创建数据库使用向导创建数据库 2使用企业管理器创建数据库使用企业管理器创建数据库 3使用使用Transact-SQL语言创建数据库语言创建数据库4.2 创建和删除数据库说明：在说明：在Transact-SQL语言的命令格式中，用语言的命令格式中，用 括起来的内容表示是可选的；括起来的内容表示是可选的；，n表示重复前表示重复前面的内容；用面的内容；用括起来表示在实际编写语句时，括起来表示在实际编写语句时，用相应的内容替代；用用相应的内容替代；用 括起来表示是必选的；括起来表示是必选的；类似类似A|B的格式，表示的格式，表示A和和B只能选择

10、一个，不能只能选择一个，不能同时都选。同时都选。4.2 创建和删除数据库CREATE DATABASE database_nameON PRIMARY ，n ，n LOG ON ，n FOR LOAD|FOR ATTACH 4.2 创建和删除数据库:=（NAME=logical_file_name，FILENAME=os_file_name，SIZE=size，MAXSIZE=max_size|UNLIMITED，FILEGROWTH=growth_increment），n:=FILEGROUP filegroup_name ，n 4.2 创建和删除数据库database_name：新数据库的

11、名称。数据库名称在服务：新数据库的名称。数据库名称在服务器中必须唯一，最长为器中必须唯一，最长为128个字符，并且要符合标识符的个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为命名规则。每个服务器管理的数据库最多为32767个。个。ON：指定存放数据库的数据文件信息。：指定存放数据库的数据文件信息。列表列表用于定义主文件组的数据文件，用于定义主文件组的数据文件，列表用于定义列表用于定义用户文件组及其中的文件。用户文件组及其中的文件。PRIMARY：用于指定主文件组中的文件。主文件组的第：用于指定主文件组中的文件。主文件组的第一个由一个由指定的文件是主文件。如果不指定指定的文件是

12、主文件。如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默关键字，则在命令中列出的第一个文件将被默认为主文件。认为主文件。4.2 创建和删除数据库LOG ON：指明事务日志文件的明确定义。如果没有本选项，则系统会自：指明事务日志文件的明确定义。如果没有本选项，则系统会自动产生一个文件名前缀与数据库名相同，容量为所有数据库文件大小动产生一个文件名前缀与数据库名相同，容量为所有数据库文件大小1/4的的事务日志文件。事务日志文件。FOR LOAD：表示计划将备份直接装入新建的数据库，主要是为了和过：表示计划将备份直接装入新建的数据库，主要是为了和过去的去的SQL Server版本兼容。

13、版本兼容。FOR ATTACH：表示在一组已经存在的操作系统文件中建立一个新的数：表示在一组已经存在的操作系统文件中建立一个新的数据库。据库。NAME：指定数据库的逻辑名称。：指定数据库的逻辑名称。FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和系统文件名和NAME的逻辑名称一一对应。的逻辑名称一一对应。4.2 创建和删除数据库SIZE：指定数据库的初始容量大小。如果没有指定主文：指定数据库的初始容量大小。如果没有指定主文件的大小，则件的大小，则SQL Server默认其与模板数据库中的主文件默认其与模板数据库

14、中的主文件大小一致，其它数据库文件和事务日志文件则默认为大小一致，其它数据库文件和事务日志文件则默认为1MB。指定大小的数字指定大小的数字size可以使用可以使用KB、MB、GB和和TB后缀，后缀，默认的后缀为默认的后缀为MB。Size中不能使用小数，其最小值为中不能使用小数，其最小值为512KB，默认值为，默认值为1MB。主文件的。主文件的size不能小于模板数据不能小于模板数据库中的主文件。库中的主文件。MAXSIZE：指定操作系统文件可以增长到的最大尺寸。：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增长直到充满磁盘。如果没有指定，则文件可以不断增长直到充满磁盘。4

15、.2 创建和删除数据库FILEGROWTH：指定文件每次增加容量的大小，：指定文件每次增加容量的大小，当指定数据为当指定数据为0时，表示文件不增长。增加量可时，表示文件不增长。增加量可以确定为以以确定为以KB、MB作后缀的字节数或以作后缀的字节数或以%作后作后缀的被增加容量文件的百分比来表示。默认后缀缀的被增加容量文件的百分比来表示。默认后缀为为MB。如果没有指定。如果没有指定FILEGROWTH，则默认值，则默认值为为10%，每次扩容的最小值为，每次扩容的最小值为64KB。4.2 创建和删除数据库例例4-2-1：使用：使用CREATE DATABASE创建一个创建一个student数据库，所

16、有参数均取默认值。数据库，所有参数均取默认值。例例4-2-2：创建一个：创建一个Student1数据库，该数据库数据库，该数据库的主文件逻辑名称为的主文件逻辑名称为Student1_data，物理文件，物理文件名为名为Student1.mdf，初始大小为，初始大小为10MB，最大尺，最大尺寸为无限大，增长速度为寸为无限大，增长速度为10%；数据库的日志文；数据库的日志文件逻辑名称为件逻辑名称为Student1_log，物理文件名为，物理文件名为Student1.ldf，初始大小为，初始大小为1MB，最大尺寸为，最大尺寸为5MB，增长速度为，增长速度为1MB。4.2 创建和删除数据库例例4-2-

17、3：创建一个指定多个数据文件和日志文件的数据库。：创建一个指定多个数据文件和日志文件的数据库。该数据库名称为该数据库名称为students，有，有1个个10MB和和1个个20MB的数据的数据文件和文件和2个个10MB的事务日志文件。数据文件逻辑名称为的事务日志文件。数据文件逻辑名称为student1和和student2，物理文件名为，物理文件名为student1.mdf和和student2.mdf。主文件是。主文件是student1，由，由primary指定，两指定，两个数据文件的最大尺寸分别为无限大和个数据文件的最大尺寸分别为无限大和100MB，增长速度，增长速度分别为分别为10%和和1MB

18、。事务日志文件的逻辑名为。事务日志文件的逻辑名为studentlog1和和studentlog2，物理文件名为，物理文件名为studentlog1.ldf和和studentlog2.ldf，最大尺寸均为，最大尺寸均为50MB，文件增长速度为文件增长速度为1MB。4.2 创建和删除数据库4.2.2 删除数据库删除数据库 1利用企业管理器删除数据库利用企业管理器删除数据库 2利用利用Drop语句删除数据库语句删除数据库 Drop database database_name,n 说明：只有处于正常状态下的数据库，才能使用说明：只有处于正常状态下的数据库，才能使用DROP语句删除。当数据库处于以下状

19、态时不能语句删除。当数据库处于以下状态时不能被删除：数据库正在使用；数据库正在恢复；数被删除：数据库正在使用；数据库正在恢复；数据库包含用于复制的已经出版的对象。据库包含用于复制的已经出版的对象。本章首页4.3 修改数据库 4.3.1 数据库更名数据库更名 在重命名数据库之前，应该确保没有用户使用该在重命名数据库之前，应该确保没有用户使用该数据库，而且数据库应该设置为数据库，而且数据库应该设置为“单用户单用户”模式。模式。系统存储过程系统存储过程sp_renamedb语法如下：语法如下：sp_renamedb dbname=old_name,newname=new_name 4.3 修改数据库

20、4.3.2 利用企业管理器修改数据库属性利用企业管理器修改数据库属性 限制访问包含两个选项：限制访问包含两个选项：“db_owner,dbcreator或或sysadmin的成员的成员”选项表示只有数据库的所有者、数据选项表示只有数据库的所有者、数据库创建者和系统管理员才有权使用数据库；库创建者和系统管理员才有权使用数据库；“单用户单用户”选选项表示数据库在同一时间只能供一个用户使用。项表示数据库在同一时间只能供一个用户使用。ANSI NULL默认设置：允许在数据库表的列中输入空默认设置：允许在数据库表的列中输入空（NULL）值。）值。递归触发器：指定是否允许触发器递归调用。递归触发器：指定是

21、否允许触发器递归调用。自动更新统计信息：允许使用自动更新统计信息：允许使用SELECT INTO或或BCP、WRITETEXT、UPDATETEXT命令向表中大量插入数据。命令向表中大量插入数据。在开发数据库时常常将此选项设置为真。在开发数据库时常常将此选项设置为真。4.3 修改数据库残缺页检测：允许自动检测有损坏的页。页是数据库内容残缺页检测：允许自动检测有损坏的页。页是数据库内容的基本存储单位，每个页的大小为的基本存储单位，每个页的大小为8KB。由于。由于SQL Server对页的读写单位是大小为对页的读写单位是大小为512字节的扇区，当每个页的第字节的扇区，当每个页的第一个扇区被成功写入

22、后，一个扇区被成功写入后，SQL Server就认为此页已经被成就认为此页已经被成功写入。因此，如果写完第一个扇区后发生突发事件，导功写入。因此，如果写完第一个扇区后发生突发事件，导致写入中断，就会产生有损坏的页，需要通知备份来恢复致写入中断，就会产生有损坏的页，需要通知备份来恢复数据库。数据库。自动关闭：当数据库中无用户时，自动关闭该数据库，并自动关闭：当数据库中无用户时，自动关闭该数据库，并将所占用的资源交还给操作系统。对那些不间断使用的数将所占用的资源交还给操作系统。对那些不间断使用的数据库不要使用此选项。据库不要使用此选项。4.3 修改数据库自动收缩：允许定期对数据库进行检查，当数据库

23、文件或自动收缩：允许定期对数据库进行检查，当数据库文件或日志文件中未用空间超过其大小的日志文件中未用空间超过其大小的25%时，系统将会自动时，系统将会自动缩减文件，使其未用空间等于缩减文件，使其未用空间等于25%。当文件大小没有超过。当文件大小没有超过其建立时的初始大小时，不会缩减文件。缩减后的文件也其建立时的初始大小时，不会缩减文件。缩减后的文件也必须大于或等于其初始大小。必须大于或等于其初始大小。自动创建统计信息：在优化查询时，根据需要自动创建统自动创建统计信息：在优化查询时，根据需要自动创建统计信息。计信息。使用被引用的标识符：标识符必须用双引号括起来，且可使用被引用的标识符：标识符必须

24、用双引号括起来，且可以不遵循以不遵循Transact-SQL命名标准。命名标准。4.3 修改数据库4.3.3 使用使用Transact-SQL语言修改数据库语言修改数据库 只有数据库管理员或具有只有数据库管理员或具有CREATE DATABASE权限的数据库所有者才有权执行该语句。权限的数据库所有者才有权执行该语句。ALTER DATABASEALTER DATABASE语句的语法格式如下：语句的语法格式如下：4.3 修改数据库Alter database databasenameadd file,n to filegroup filegroupname|add log file,n|remo

25、ve file logical_file_name|remove filegroup filegroup_name|modify file|modify name=new_databasename|add filegroup filegroup_name|modify filegroup filegroup_namefilegroup_property|name=new_filegroup_name 4.3 修改数据库add file,n to filegroup filegroupname：表示：表示向指定的文件组中添加新的数向指定的文件组中添加新的数据文件。据文件。add log file

26、,n：增加新的日志文：增加新的日志文件。件。remove file logical_file_name：删除指定的操作删除指定的操作系统文件。系统文件。remove filegroup filegroup_name：删除指定的删除指定的文件组。文件组。modify file：修改某个操作系统文件。：修改某个操作系统文件。4.3 修改数据库modify name=new_databasename：重命名数：重命名数据库。据库。add filegroup filegroup_name：增加一个文件：增加一个文件组。组。modify filegroup filegroup_name：修改某个：修改某

27、个指定文件组的属性。指定文件组的属性。4.3 修改数据库4.3.4 缩小数据库缩小数据库 1使用企业管理器可以缩小数据库。使用企业管理器可以缩小数据库。2使用使用Transact-SQL语言缩小数据库。语言缩小数据库。（1）使用）使用DBCC SHRINKDATABASE命令收缩命令收缩指定数据库中的数据文件。指定数据库中的数据文件。DBCC SHRINKDATABASE(database_name,target_percent,NOTRUNCATE|TRUNCATEONLY)4.3 修改数据库database_name：要收缩的数据库名称。：要收缩的数据库名称。Target_percent：

28、当数据库收缩后，数据库文件中剩余可：当数据库收缩后，数据库文件中剩余可用空间的百分比。用空间的百分比。NOTRUNCATE：被释放的文件空间依然保持在数据库文：被释放的文件空间依然保持在数据库文件中。如果未指定，将所释放的文件空间被操作系统回收。件中。如果未指定，将所释放的文件空间被操作系统回收。TRUNCATEONLY：将数据文件中未使用的空间释放给：将数据文件中未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小。使用操作系统，并将文件收缩到上一次所分配的大小。使用 TRUNCATEONLY 时，将忽略时，将忽略 target_percent的限制。的限制。4.3 修改数据库（2）

29、使用）使用DBCC SHRINKFILE命令收缩数据库命令收缩数据库的指定数据文件或日志文件大小的指定数据文件或日志文件大小DBCC SHRINKFILE的语法格式如下：的语法格式如下：DBCC SHRINKFILE(file_name,target_size|,EMPTYFILE|NOTRUNCATE|TRUNCATEONLY)4.3 修改数据库file_name：要收缩文件的逻辑名称。：要收缩文件的逻辑名称。target_size：将文件缩小到指定的长度，以：将文件缩小到指定的长度，以MB为单位。如果不指定，将文件减少到最大程度。为单位。如果不指定，将文件减少到最大程度。EMPTYFILE：将数据从指定文件中转移到同一：将数据从指定文件中转移到同一文件组中的其它文件。文件组中的其它文件。NOTRUNCATE和和TRUNCATEONLY：与：与DBCC SHRINKDATABASE命令中相同。命令中相同。本章首页