大型数据库管理系统技术、应用与实例分析：SQL Server 2005
上QQ阅读APP看本书，新人免费读10天
设备和账号都新为新人
上一章目录下一章

3.3 数据库创建与管理

3.3.1 创建数据库

SQL Server能够支持多个数据库。在一个服务器上，最多可以创建32 767个数据库。创建数据库的用户就成为该数据库的所有者。每个数据库都必须包含一个也只能包含一个主数据文件，必要时可以拥有多个次文件；每个数据库至少有一个日志文件，也可以有多个日志文件。可以把各个数据库文件组织成不同的文件组。

每个数据库由以下几个部分的数据库对象组成：关系图、表、视图、存储过程、用户、角色、规则、默认、用户自定义数据类型和用户自定义函数。

1.准备创建数据库

①确定数据库的名称、所有者（创建数据库的用户）。

②确定存储该数据库的数据文件的初始大小及文件空间增长方式、日志、备份和系统存储参数等配置。

下面以创建教学管理数据库为例说明。

数据库：教学管理。

数据文件逻辑名称：教学管理_data。

日志文件逻辑名称：教学管理_log。

数据文件存储：d:\server\MSSQL\data\教学管理_data.mdf，初始大小 2 MB，最大空间20 MB，增加量2 MB，主文件中包含数据库的系统表。

日志文件存储：d:\server\MSSQL\data\教学管理_data.ldf，初始大小 2 MB，最大空间20 MB，增加量2 MB。

备份设备名称：backup。

备份文件：教学管理_backup.dat。

2.创建数据库实例

创建数据库的方法有使用对象资源管理器和使用Transact-SQL命令两种。

方法一：使用对象资源管理器创建数据库。

【例3-1】创建“教学管理”数据库。

①在对象资源管理器中，在数据库文件夹下的“数据库”图标上单击鼠标右键，选择“新建数据库”选项，就会出现如图3-9所示的下拉菜单。

②在“常规”选项卡中，要求用户输入数据库名称。输入新建数据库名称“教学管理”，如图3-10所示。

图3-9 “新建数据库”快捷菜单

图3-10 “数据库属性”对话框

③单击数据文件的自动增长按钮，如图3-11所示。

  • “按兆字节”：表示指定数据文件按固定步长增长，并要求指定一个值。
  • “按百分比”：表示指定数据文件按当前大小的百分比增长，并要求指定一个值。
  • “文件增长不受限制”：表示允许文件按需求增长。
  • “将文件增长限制为（MB）”，表示允许文件增长到指定的最大值。

图3-11 “数据文件”选项卡

建议指定文件最大允许增长的大小，这样做可以防止文件在添加数据时无限增大，以至于用尽磁盘空间。

④单击“事务日志”选项卡。该选项卡用来设置事务日志文件信息，与图3-11类似。

⑤单击“确定”按钮，则开始创建新的数据库。

方法二：使用SQL命令创建数据库。

语法格式：

CREATE DATABASE 数据库名
        [ON [PRIMARY]  [＜NAME='数据库逻辑名称'＞ [,…n]  [,＜ 文件组名＞ [,…n]]  ]
        [LOG ON {＜LOG_NAME ＞ [,…n]}]
        [FOR RESTORE]
        FILENAME='数据库物理文件名'
                [,SIZE=size]
                [,MAXSIZE={max_size|UNLIMITED}]
                [,FILEGROWTH=growth_increment] ）  [,…n]

参数说明：

  • PRIMARY是一个关键字，指定主数据库文件，若未给出这个关键字，则默认文件序列第一个是主数据文件。
  • LOG ON指明事务日志文件的明确定义。
  • NAME='数据库逻辑名称'是在SQL Server系统中使用的名称，是数据库在SQL Server中的标识符。
  • FILENAME='数据库物理文件名'指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名与NAME的逻辑名称一一对应。
  • SIZE指定数据库的初始容量大小。
  • MAXSIZE指定操作系统文件可以增长到的最大尺寸。
  • FILEGROWTH指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。

【例3-2】指定文件组，创建一个多数据文件和日志文件的数据库。

该数据库名称为“教学练习”。其中，数据文件“教学练习_data”分配20 MB，属于文件组PRIMARY；“教学练习_data1”和“教学练习_data2”各分配10 MB，属于文件组stugroup。有1个10 MB的事务日志文件。

创建命令的程序清单如下：

CREATE DATABASE  教学练习    --数据库名称：教学练习
ON PRIMARY                   --下面主文件属于主文件组
（NAME= 教学练习_data,       --对应数据库第一个逻辑文件名
FILENAME='d:\server\mssql\data\教学练习_data.mdf',--对应的主文件名和存储位置
SIZE=20,                     --初始分配的空间
MAXSIZE=150,                 --指出最大空间为150MB
FILEGROWTH=10%）,            --空间增长按10%增长
FILEGROUP stugroup           --指定新的文件组，下面文件属于stugroup文件组
（NAME= 教学练习_data1,      --对应数据库第二个逻辑文件名
FILENAME='d:\server\mssql\data\教学练习_data1.ndf',--对应的第一个次文件名和存储位置
SIZE=10,                     --初始分配的空间
MAXSIZE=100,                 --指出最大空间为100MB
FILEGROWTH=1）,              --空间增长按1MB增长
（NAME= 教学练习_data2,      --对应数据库第三个逻辑文件名
FILENAME='d:\server\mssql\data\教学练习_data2.ndf',--对应的第二个次文件名和存储位置
SIZE=10,                     --初始分配的空间
MAXSIZE=100,                 --指出最大空间为100 MB
FILEGROWTH=1）               --空间增长按1 MB增长
LOG ON                       --日志文件
（NAME= 教学练习_log,
FILENAME='d:\server\mssql\data\教学练习_log.ldf',
SIZE=10,
MAXSIZE=50,
FILEGROWTH=1）

输出结果如下：

命令已成功完成。

说明：

①执行命令后，如果语句执行正确，则出现如上所述的数据库创建成功的信息。

②如果命令未成功执行，则出现出错信息，比如，出现如下提示信息：

设备激活错误。
物理文件名'd:\server\mssql\data\教学练习_data.mdf'可能有误，创建数据库失败。
未能创建所列出的某些文件名，请检查前面的错误信息。

上述命令失败的主要原因是存储物理文件的目录'd:\server\mssql\data'不存在，解决的办法是建立该文件夹。

3.3.2 管理数据库

1.修改数据库

数据库创建后，主数据文件和日志文件的物理地址就不允许被改变和删除了。但数据文件和日志文件的大小、增长方式等属性可以改变，可以增加或删除次数据文件、次日志文件、文件组。

修改数据库的方法有使用SQL命令和使用对象资源管理器两种。

方法一：使用对象资源管理器。

【例3-3】修改“教学练习”数据库。

①在对象资源管理器的“教学练习”数据库结点上，单击右键，在出现的快捷菜单中选择“属性”，再单击“文件”标签，如图3-12所示。

②当数据文件的容量不够存储数据时，可以考虑增加数据文件。单击页面下面的“添加”按钮，直接在文件名一列的新行处，输入要添加的数据文件逻辑名称“教学练习_add”，单击自动增长按钮（见图3-12）分配空间5，选择“限制文件增长”；再单击路径按钮确定物理文件的存储位置。

③选中要删除的数据文件“教学练习_data2”，单击页面下面的“删除”按钮，出现“确定删除数据文件吗？”询问对话框，单击“确定”按钮。

在“教学练习属性”对话框中，选择文件类型“日志”，也可以修改日志文件的属性。

图3-12 “数据库属性”对话框

方法二：使用Transact-SQL命令。

语法格式：

ALTER DATABASE 数据库名
    { ADD FILE ＜ filespec ＞ [ ,...n ] [ TO FILEGROUP 文件组名 ]
    | ADD LOG FILE ＜ filespec ＞ [ ,...n ]
    | REMOVE FILE数据库逻辑名
    | ADD FILEGROUP文件组名
    | REMOVE FILEGROUP文件组名
    | MODIFY FILE ＜ filespec ＞
    | MODIFY NAME=数据库名
    |MODIFY FILEGROUP 文件组名{NAME=新文件组名 }

参数说明：

①filespec参数定义如下。

（ NAME =数据库逻辑名
      [ , NEWNAME =新数据库逻辑名]
      [ , FILENAME = '数据库物理文件名' ]
      [ , SIZE = size ]
      [ , MAXSIZE = { max_size | UNLIMITED } ]
      [ , FILEGROWTH = growth_increment ] ）

②ADD FILE指定要添加的文件。

③TO FILEGROUP指定要将指定文件添加到的文件组。

④ADD LOG FILE指定要将日志文件添加到的指定数据库。

⑤REMOVE FILE从数据库系统表中删除文件描述并删除物理文件。只有在文件为空时才能删除。

⑥ADD FILEGROUP指定要添加的文件组。

⑦REMOVE FILEGROUP从数据库中删除文件组并删除该文件组中的所有文件。只有在文件组为空时才能删除。

⑧MODIFY FILE指定要更改的指定文件，更改选项包括 FILENAME、SIZE、FILEGROWTH和MAXSIZE。一次只能更改这些属性中的一种。必须在＜filespec＞中指定数据库逻辑名，以标识要更改的文件。如果指定了SIZE，那么新大小必须比文件当前大小要大。

⑨SIZE指定数据库的初始容量大小。

⑩MAXSIZE指定操作系统文件可以增长到的最大尺寸。

⑪FILEGROWTH指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。

【例3-4】修改“教学练习”数据库。

主数据文件的最大文件大小为文件增长不受限制。增加次数据文件教学练习_add，分配空间5MB，不允许自动增长空间。删除教学练习_data1次数据文件。

修改教学练习数据库的程序清单如下：

ALTER DATABASE 教学练习                  --修改教学练习数据库
MODIFY FILE（                            --修改数据文件教学练习_data
            NAME='教学练习_data',
            MAXSIZE=UNLIMITED
            ）
GO
ALTER DATABASE 教学练习
ADD FILE （                              --增加数据文件教学练习_add
          NAME='教学练习_add',
          FILENAME='d:\server\mssql\data\教学练习_add.mdf',
          SIZE=5,
          FILEGROWTH=0                   --不允许自动增长
          ）
GO
ALTER DATABASE 教学练习
REMOVE FILE 教学练习_data2               --删除次数据文件教学练习_data2
GO
ALTER DATABASE 教学练习
ADD LOG FILE （                          --增加日志文件教学练习_addlog
            NAME='教学练习_addlog',
            FILENAME='d:\server\mssql\data\教学练习_addlog.ldf',
            SIZE=1,
            MaxSIZE=10,
            FILEGROWTH=1
            ）
GO

输出结果如下：

文件'教学练习_data2' 已删除。

2.收缩数据库

SQL Server 2005提供收缩过于庞大的数据库的手段，以收回未使用的数据页面。可以用手动的方法单独收缩某一数据库文件，也可以收缩整个文件组的长度，还可以设置数据库在达到一定大小前自动执行收缩操作，自动收缩操作是在后台运行的，不会影响当前前台的任何活动。

注意：不能将数据库收缩到小于创建的长度。日志文件不可以被收缩。

收缩数据库的方法有使用对象资源管理器和使用Transact-SQL命令两种。

使用SQL命令收缩数据库的语法格式如下：

DBCC  SHRINKDATABASE
        （database_name [,target_percent]
        [,{NOTRUNCATE | TRUNCATEONLY}]
        ）

参数说明：

  • target_percent：表示当数据库收缩后还剩下的自由空间。
  • NOTRUNCATE：被释放的文件空间还保持在数据库文件的范围内，否则释放的空间被系统收回。
  • TRUNCATEONLY：将所有未使用的数据空间释放给操作系统使用。使用该关键字，将忽略target_percent限制。

【例3-5】收缩教学练习数据库文件，使使用空间为原来的40%。

方法一：使用对象资源管理器。

①在对象资源管理器的“教学练习”数据库结点上，单击右键，在出现的快捷菜单中选择“所有任务”→“收缩数据库”，出现“收缩数据库”对话框，如图3-13所示。

②“收缩后文件中的最大可用空间”表示用于设置压缩后文件的最大空闲空间。我们在此输入40。

图3-13 “收缩数据库”对话框

③在对象资源管理器的“教学练习”数据库结点上，单击右键，在出现的快捷菜单中选择“所有任务”→“收缩文件”，同样出现“收缩文件”对话框。

④因为一个数据库往往不是一个数据文件，所以单击“文件”按钮可以选择数据库的某个数据文件单独进行收缩。

⑤单击“确定”按钮。

方法二：使用Transact-SQL命令。

语句如下：

DBCC SHRINKDATABASE （教学练习,40）
GO

返回结果：

DBCC 执行完毕。

3.查看数据库信息

（1）查看数据库定义信息

程序如下：

sp_helpdb                    --返回所有定义的数据库信息
sp_helpdb 教学练习            --返回指定数据库的定义信息

结果如图3-14所示。

图3-14 查看数据库定义信息

图3-14（a）所示为数据库的信息，图3-14（b）所示为库内文件的信息。

（2）查看数据空间使用状况

程序如下：

USE 教学练习
GO
sp_spaceused                  --返回教学练习数据库数据文件使用的空间
Go
DBCC SQLPERF（LOGSPACE）      --返回所有数据库的日志文件使用的空间

4.删除数据库

【例3-6】删除数据库教学练习（注：因为后面还要使用该数据库，故先不要真正删除）。

方法一：使用对象资源管理器。

操作步骤如下：

①在对象资源管理器的“教学练习”数据库结点上，单击右键，在出现的快捷菜单中选择“删除”。

②在弹出的对话框中选择“是”按钮。

方法二：使用Transact-SQL命令

利用DROP语句删除数据库。DROP语句可以从SQL Server中一次删除一个或多个数据库。其语法如下：

DROP DATABASE database_name[,...n]

例如，删除创建的数据库“教学练习”的语句如下：

DROP DATABASE 教学练习         -- 教学练习中所包含的文件都被删除
上一章目录下一章