第2章 数据库的创建和管理 学习要点： 2.1 基本概念 2.2 创建数据库 2.3 数据库的管理 2.4 删除数据库 第2章 数据库的创建和管理 学习要点： SQL Server数据库的存储结构 事务日志的概念 数据库的创建、删除、管理 2.1 基本概念 2.2 创建数据库 2.3 数据库的管理 2.4 删除数据库

2.1 基本概念 主要数据文件 数据文件 数据库 次要数据文件 事务日志文件 2.1 基本概念 用于存储数据库中的所有对象 主要数据文件 数据文件 数据库 次要数据文件 事务日志文件 主要数据文件用来存储数据库的启动信息、部分或全部数据，扩展名为.mdf 每个数据库只能有一个主要数据文件 次要数据文件用于保存所有主要数据文件中容纳不下的数据，扩展名为.ndf 保存用于恢复数据库的日志信息

文件组 :将多个数据文件集合起来形成的一个整体 目的：便于进行管理和数据的分配 类型： 主文件组：包含了所有的系统表 用户自定义文件组 默认文件组：包含所有在创建时没有指定文件组的表、索引等数据库对象 注意：一个数据文件不能存在于两个或两个以上的文件组里，日志文件不属于任何文件组

2.1.1 数据库的物理存储结构

2.1.2 事务日志 事务日志文件，用以记录所有事务和每个事务对数据库所做的修改，当数据库破坏时可以用事务日志恢复数据库内容。 2.1.3 系统数据库和示例数据库 安装SQL Server2000时，系统自动创建4个系统数据库和2个示例数据库。

master数据库 master数据库记录了SQL Server系统的所有系统级别的信息，包括所有的登录帐户和密码、系统的配置信息以及所有用户定义数据库的存储位置、初始化信息。 master是SQL Server中重要的数据库，如果master数据库受到损坏，SQL Server将不能启动。 model数据库 model数据库是建立新数据库的模板，它包含了将复制到每个新建数据库中的系统表。

msdb数据库 该数据库主要被SQL Server Agent用于进行复制、作业调度以及管理报警等活动。 tempdb数据库 示例数据库 pubs数据库记录一个虚拟的图书出版公司的数据信息 northwind数据库保存了一个虚拟的贸易公司的数据信息

2.2 创建数据库 创建数据库是创建表及其它数据库对象的第一步。数据库的创建包括为数据库确定名称、决定数据库大小，及在数据库中用来存储数据的文件。 2.2.1 使用企业管理器创建数据库 （1）进入SQL Server企业管理器，在左边的目录树结构中选择“数据库”文件夹 。 （2）单击“操作”菜单中的“新建数据库”命令或右键单击“数据库”文件夹，在弹出的快捷菜单上选择“新建数据库”命令 。系统打开“数据库属性”对话框。

（3）在“常规”选项卡中输入数据库的名称，假设为Sales。 输入数据库名称

（4）选择“数据文件”选项卡，在该窗口中设置数据文件的属性。 数据文件的逻辑名 文件所在的文件组 文件位置 初始大小 文件否允许自动增长

（5）选择“事务日志”选项卡，在该窗口中可设置日志文件的属性。 （6）单击“确定”按钮，数据库创建完成。

2.2.2 使用T-SQL语句创建数据库 创建数据库的T-SQL语句的语法形式： CREATE DATABASE database_name [ ON     [ < filespec > [ ,...n ] ] [ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ] <filespec>::=[ PRIMARY ] ( [ NAME = logical_file_name , ] FILENAME = 'os_file_name‘ [ , SIZE = size ] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ] ) [ ,...n ] < filegroup > ::=FILEGROUP filegroup_name < filespec > [ ,...n ]

database_name：新创建的数据库的名称。 ON：指出用来存储数据库中数据部分的磁盘文件（数据文件）。 PRIMARY：指定主文件组中的主文件。 LOG ON：指定用来存储数据库日志的磁盘文件。 NAME = logical_file_name：指定数据文件或日志文件的逻辑名。 FILENAME = 'os_file_name'：指定数据文件或日志文件的操作系统文件名，包括文件名和路径。 SIZE = size：指定数据文件或日志文件的初始大小，默认单位为MB。

MAXSIZE = { max_size | UNLIMITED }：指定数据文件或日志文件可以增长到的最大容量，默认单位为MB。 FILEGROWTH = growth_increment：指定数据文件或日志文件的增长幅度，默认单位为MB。 例2-1 创建一个数据库sample。主文件为sample_dat。 CREATE DATABASE sample ON PRIMARY ( NAME=sample_dat, FILENAME='d:\program files\microsoft sql server\mssql\data\sample_data.mdf', SIZE=5, MAXSIZE=50, FILEGROWTH=10 )

CREATE DATABASE Sales ON ( NAME=Sales_data, FILENAME='d:\program files\microsoft sql server\mssql\data\sales_data.mdf', SIZE=10MB, MAXSIZE=500MB, FILEGROWTH=10%) LOG ON ( NAME=Sales_log, FILENAME='d:\program files\microsoft sql server\mssql\data\sales_log.ldf', SIZE=5MB, MAXSIZE=100MB, FILEGROWTH=2MB) 例2-2 创建一个只包含一个数据文件和一个日志文件的数据库。该数据库名为Sales，数据文件的逻辑名为Sales_data，数据文件的操作系统名为sales_data.mdf，初始大小为10MB，最大可增加至500MB，增幅为10％；日志文件的逻辑名为Sales_log，操作系统名为sales_log.ldf，初始大小为5MB，最大值为100MB，日志文件大小以2MB增幅增加。

例2-3 创建一个包含多个数据文件和日志文件的数据库。该数据库名为student，含有三个初始大小为10MB的数据文件和两个8MB的日志文件。 CREATE DATABASE student ON PRIMARY ( NAME=std_dat1, FILENAME='d:\program files\microsoft sql server\mssql\data\student1.mdf', SIZE=10MB, MAXSIZE=200MB, FILEGROWTH=20), ( NAME=std_dat2, FILENAME='d:\program files\microsoft sql server\mssql\data\student2.ndf',

( NAME=std_dat3, FILENAME='d:\program files\microsoft sql server\mssql\data\student3.ndf', SIZE=10MB, MAXSIZE=200MB, FILEGROWTH=20) LOG ON ( NAME=std_log1, FILENAME='d:\program files\microsoft sql server\mssql\data\stdlog1.ldf', SIZE=8MB, MAXSIZE=100MB, FILEGROWTH=10MB), ( NAME=std_log2, FILENAME='d:\program files\microsoft sql server\mssql\data\stdlog2.ldf', SIZE=8MB, MAXSIZE=100MB, FILEGROWTH=10MB)

例2-4 创建一个包含两个文件组的数据库。该数据库名为business，主文件组包含business_dat1和business_dat2两个数据文件。文件组business_group包含文件数据文件business_dat3。该数据库还包含一个日志文件business_log。 CREATE DATABASE business ON PRIMARY ( NAME=business_dat1, FILENAME='d:\program files\microsoft sql server\mssql\data\businessdat1.mdf', SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=10), ( NAME=business_dat2, FILENAME='d:\program files\microsoft sql server\mssql\data\businessdat2.ndf', MAXSIZE=500MB,

FILEGROUP business_group ( NAME=business_dat3, FILENAME='d:\program files\microsoft sql server\mssql\data\businessdat3.ndf', SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=10%) LOG ON ( NAME=business_log, FILENAME='d:\program files\microsoft sql server\mssql\data\businesslog.ldf', SIZE=8MB, MAXSIZE=100MB, FILEGROWTH=10MB)

2.3 数据库的管理 2.3.1 查看数据库 1．用企业管理器查看数据库的属性 2.3 数据库的管理 2.3.1 查看数据库 1．用企业管理器查看数据库的属性 进入SQL Server企业管理器，在左边的目录树结构中选中要查看的数据库Sales，单击右键，在弹出的快捷菜单上单击“属性”命令，即可进入Sales数据库的属性窗口 。 该窗口共有6个选项卡，包括“常规”、“数据文件”、“事务日志”、“文件组”、“选项”及“权限”，我们可以根据需要进入选择不同的选项卡查看数据库相应的信息。

2．用T-SQL语句查看数据库的属性 使用系统存储过程sp_helpdb可以查看某个数据库或所有数据库的属性。 格式：sp_helpdb database_name 该存储过程显示了Sales数据库的名称、大小、所有者、创建日期以及数据文件和日志文件等属性。 例2-5 查看数据库Sales的属性。 sp_helpdb Sales 例2-6 查看所有数据库的属性 sp_helpdb

2.3.2 修改数据库 1．用企业管理器修改数据库属性 进入数据库的属性窗口 ，在属性窗口中修改数据库属性。 2.3.2 修改数据库 1．用企业管理器修改数据库属性 进入数据库的属性窗口 ，在属性窗口中修改数据库属性。 注意：在删除文件组时要求该文件组中不包含任何数据文件，否则SQL Server会给出错误信息 2．用T-SQL语句修改数据库属性 其语法形式为：

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 | MODIFY FILE < filespec > | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }

ADD FILE < filespec > [ , 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：删除某一文件组。 MODIFY FILE < filespec >：修改某个文件的属性。 MODIFY NAME = new_dbname：修改数据库的名字

例2-7 为数据库sample添加一个数据文件sample_dat2和一个日志文件sample_log2。 ALTER DATABASE sample ADD FILEGROUP USER1   ADD FILE ( NAME=sampuser_dat1, FILENAME='d:\program files\microsoft sql server\mssql\data\sampuser_dat1.ndf', SIZE=4, MAXSIZE=10, FILEGROWTH=1 ), ( NAME=sampuser_dat2, FILENAME='d:\program files\microsoft sql server\mssql\data\sampuser_dat2.ndf', FILEGROWTH=1 ) TO FILEGROUP USER1 MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }：修改某一文件组的属性。 ALTER DATABASE sample ADD FILE ( NAME=sample_dat2, FILENAME='d:\program files\microsoft sql server\mssql\data\sample_dat2.ndf', SIZE=4, MAXSIZE=10, FILEGROWTH=1 ) ADD LOG FILE ( NAME=sample_log2, FILENAME='d:\program files\microsoft sql server\mssql\data\sample_log2.ldf', 例2-7 为数据库sample添加一个数据文件sample_dat2和一个日志文件sample_log2。 例2-8 为数据库sample添加一个文件组USER1，并向该文件组添加一个二个数据文件sampuser_dat1和sampuser_dat2。

例2-9 从数据库sample中删除文件sampuser_dat2。 ALTER DATABASE sample REMOVE FILE sampuser_dat2 例2-10 删除数据库sample中的文件组USER1。 REMOVE FILE sampuser_dat1 REMOVE FILEGROUP USER1 注意：在删除文件组时必须先删除文件组中所包含的文件。

例2-11 修改数据库sample中数据文件sample_dat2的属性，将其初始大小改为10MB，最大容量改为80MB,增长幅度改为5MB。 ALTER DATABASE sample MODIF FILE ( NAME=sample_dat2, SIZE=10, MAXSIZE=80, FILEGROWTH=5 )

例2-12 修改数据库business中文件组business_group的属性，将其改名为group1，并设置为DEFAULT属性（即该文件组为默认文件组）。 ALTER DATABASE business MODIFY FILEGROUP business_group NAME=group1 MODIFY FILEGROUP group1 DEFAULT 例2-13 将数据库student改名为stud_teacher。 ALTER DATABASE student MODIFY NAME=stud_teacher

2.3.3 重命名数据库 语法形式为： sp_renamedb ‘old_name’,’new_name’ 2.3.3 重命名数据库 语法形式为： sp_renamedb ‘old_name’,’new_name’ old_name：数据库的当前名称； new_name是数据库的新名称。 例2-14 将数据库business更名为company。 sp_renamedb 'business','company

2.3.4 数据库的收缩 SQL Server2000允许收缩数据库中的每个文件以删除未使用的页。数据文件和事务日志文件都可以收缩。 2.3.4 数据库的收缩 SQL Server2000允许收缩数据库中的每个文件以删除未使用的页。数据文件和事务日志文件都可以收缩。 1．自动收缩 （1）使用企业管理器设置自动收缩数据库 在数据库的“属性对话框”中，点击“选项”选项卡，选中“自动收缩”复选框，即可完成数据库自动收缩的设置。 设置自动收缩

（2）T-SQL语句自动收缩数据库 使用ALTER DATABASE语句可以将数据库设为自动收缩。当数据库中有足够的可用空间时，就会发生收缩。其语法形式为： ALTER DATABASE database_name SET AUTO_SHRINK on/off 例2-15 将数据库sales的收缩设为自动收缩。 ALTER DATABASE sales SET AUTO_SHRINK on 注意：不能将整个数据库收缩到比其原始大小还要小。

2．手工收缩 DBCC SHRINKDATABASE ( database_name [ , target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ) database_name：是要收缩的数据库名称。 target_percent：是数据库收缩后的数据库文件中所要的剩余可用空间百分比。 NOTRUNCATE：被释放的文件空间依然保持在数据库文件的范围内。如果未指定，则被释放的文件空间将被操作系统回收利用。

TRUNCATEONLY：将数据文件中的任何未使用的空间释放给操作系统。 例2-16 缩小sales数据库的大小，使得该数据库中的文件有20％的可用空间。 DBCC SHRINKDATABASE(sales,20) 还可以使用DBCC命令来缩小某一个操作系统文件的长度，其语法形式为： DBCC SHRINKFILE  ( file_name   { [ , target_size ] | [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ] }    )

file_name：是要收缩的操作系统文件名。 target_size：将文件缩小到指定的长度，以MB为单位。如果缺省该项，文件将尽最大可能进行缩小。 EMPTYFILE：将指定文件上的数据全部迁移到本文件组的其它文件上，以后的操作将不会再再该文件上增加数据。 例2-17 将数据库sales中名为sales_data的文件收缩至7MB。 USE sales GO DBCC SHRINKFILE(sales_data,7)

2.4 删除数据库 1．用企业管理器删除数据库 （1）打开SQL Server企业管理器窗口，在左边的目录树结构中选择要删除的数据库文件夹，如company。 （2）选择“操作”菜单中的“删除”命令或右键单击要删除的数据库，在弹出的快捷菜单中选择“删除”命令，系统会弹出“删除数据库”的确认对话框。 （3）单击“是”按钮就会删除该数据库 。 2．T-SQL语句删除数据库 语法形式：DROP DATABASE database_name

例2-18 删除数据库student DROP DATABASE student 例2-19 删除sample和student数据库。 DROP DATABASE sample,student 注意： 4个系统数据库master、tempdb、model、msdb不能删除。 正在使用的数据库不能删除。 数据库被删除之后，文件及其数据都从服务器上的磁盘中删除。一旦删除数据库，它即被永久删除，所以删除数据库时一定要谨慎。

小结： 介绍了SQL Server数据库创建、管理和删除的方法，学习了数据库的基本存储结构。并对系统数据库和示例数据库进行了讲解 通过对本章的学习，应该重点掌握根据需要创建数据库，并对其进行有效的管理