第3-2章管理数据库

1、第 3 章管 理 数 据 库 快速了解创建学校的数据库：快速了解创建学校的数据库：管理任务：管理任务： 小王的学校通过数据库管理所有老师和学生的信息，这台服务器配有三个磁盘驱动器。在安装了2005之后，作为一个DBA，需要实现以下任务： 创建数据库； 创建数据文件； 规划文件大小和位置； 配置数据库选项。 管理操作过程：管理操作过程： 小王分别在 SQL 2005 中建立了老师和学生的数据库的文件，分别用来存储老师和学生的信息。并且根据在校的老师和学生数量来估计文件的初始大小，老师的数据文件初始大小为20 MB，学生的数据文件初始大小为80 MB，设置日志文件的总大小为数据文件的25。 由于服

2、务器有三个硬盘驱动器，操作系统位于C盘，小王将数据文件分别放置于D盘和E盘，将日志文件放在C盘，这样就可以实现最好的性能。数据库的创建和管理数据库的创建和管理 数据库是表、视图、存储过程、触发器等数据库对象的集合，是数据数据库是表、视图、存储过程、触发器等数据库对象的集合，是数据库管理系统的核心内容。为了更好地理解数据库，应该首先了解数据库的库管理系统的核心内容。为了更好地理解数据库，应该首先了解数据库的物理文件和逻辑文件、事务日志、文件组、数据库的物理空间、数据库状物理文件和逻辑文件、事务日志、文件组、数据库的物理空间、数据库状态、数据库快照等基本概念。态、数据库快照等基本概念。 数据库：数

3、据库：用于存储特定格式的关系数据的总集。用于存储特定格式的关系数据的总集。 文件：文件：用于存放由数据构成的表。用于存放由数据构成的表。 文件组文件组 ：用于将多个文件组织起来进行管理。用于将多个文件组织起来进行管理。 架构：架构：用于管理数据表等对象的命名。用于管理数据表等对象的命名。 快照：快照：用于建立数据库的快速还原点，方便测试和故障恢复。用于建立数据库的快速还原点，方便测试和故障恢复。 在在 SQL Server 2005 系统中，一个数据库至少有一个数据文件和一系统中，一个数据库至少有一个数据文件和一个事务日志文件。当然，该数据库也可以有多个数据文件和多个事务日志个事务日志文件。当

4、然，该数据库也可以有多个数据文件和多个事务日志文件。数据文件用于存放数据库的数据和各种对象，事务日志文件用于存文件。数据文件用于存放数据库的数据和各种对象，事务日志文件用于存放事务日志。放事务日志。 概述：概述： 数据库的文件组成数据库的文件组成：（：（通常可以由三类文件组成） 主数据文件：默认的扩展名为.mdf 辅助数据文件：默认的扩展名为.ndf 事务日志文件：默认的扩展名为.ldf在在SQL Server 2005中一个数据库至少包含一个有数据库对象的主数据文中一个数据库至少包含一个有数据库对象的主数据文件和一个事务日志文件。件和一个事务日志文件。这些物理文件具有可用于这些物理文件具有可

5、用于Transact-SQL 语句的操作系统文件名称和逻辑语句的操作系统文件名称和逻辑文件名称文件名称所有的的数据文件和事务日志的所有的的数据文件和事务日志的默认位置默认位置是是 C: Progrom Files Mirosoft SQL Server MSSQL.n MSSQL Data最佳的实践：最佳的实践： 将所有的数据和对象存储在辅助数据文件中，而主数据文件只负责将所有的数据和对象存储在辅助数据文件中，而主数据文件只负责存储数据目录，这种配置可有效的避免访问时的磁盘争用。存储数据目录，这种配置可有效的避免访问时的磁盘争用。 配置事务日志：配置事务日志： 建立好数据库之后，在进行具体的操

6、作之前应当首先配置事务日志。建立好数据库之后，在进行具体的操作之前应当首先配置事务日志。事务日志文件记录事务日志文件记录SQL Server 对这个数据库的操作，以便于在系统出现对这个数据库的操作，以便于在系统出现故障时恢复数据库。故障时恢复数据库。 那事务日志又有什么样的作用？（看个场景）那事务日志又有什么样的作用？（看个场景）小张在书店买书。他和书店进行一次性交易付款过程包括下面这些数据小张在书店买书。他和书店进行一次性交易付款过程包括下面这些数据库操作库操作： 更新客户所购书籍的库存信息。 保存客户的付款信息和银行系统进行互动。 生成订单并保存到数据库中。 更新用户相关信息。 上面的场景

7、中每个数据库操作都被称为上面的场景中每个数据库操作都被称为事务。事务。 所有的数据库操作都将保存到相关的数据库日志中，发生故障后，所有的数据库操作都将保存到相关的数据库日志中，发生故障后，系统可以根据事务日志将数据库返回到用户下单前的状态。系统可以根据事务日志将数据库返回到用户下单前的状态。正常情况一切顺利进行，交易成功，与交易相关的所有数据也成功的更新。正常情况一切顺利进行，交易成功，与交易相关的所有数据也成功的更新。 创建数据库的示例：创建数据库的示例： 可以使用SQL Server Management Studio中的可视化工具来创建数据库，图示：创建数据库时，可指定以下选项：创建数据

8、库时，可指定以下选项： 主文件：默认文件名为：database_name.mdf，位于SQL Server 实例的Data 文件夹中。 辅助文件：创建可选的辅助文件，将其分配到主文件组和任何现存用户定义的主文件组中 事务日志：默认文件名为：database_name.ldf，位于SQL Server 实例的Data 文件夹中。 文件组：文件组： 如果在数据库中存在大量的文件，如何才能方便管理，同时提高数如果在数据库中存在大量的文件，如何才能方便管理，同时提高数据库的性能呢？据库的性能呢？探讨：探讨： SQL Server 2005在数据文件中存储数据，数据文件可分组为一个或在数据文件中存储数据

9、，数据文件可分组为一个或多个文件组。虽然数据库只用一个文件组即可成功运行，但创建多个文多个文件组。虽然数据库只用一个文件组即可成功运行，但创建多个文件组可能更有利。件组可能更有利。 大小大小：可指定每个数据文件和日志文件的大小。初始大小与：可指定每个数据文件和日志文件的大小。初始大小与model 数据库中使用的值相同。数据库中使用的值相同。 文件增长文件增长：可指定文件是否在必要时增长。该选项称为自动增长，：可指定文件是否在必要时增长。该选项称为自动增长，即为启用文件增长。即为启用文件增长。 最大值最大值：可指定：可指定MB或百分比作为文件可增长的最大值，建议指或百分比作为文件可增长的最大值，

10、建议指定文件增长的最大值。定文件增长的最大值。 排序规则排序规则：数据的排序次序原则，用以确定进行数据排序时数据：数据的排序次序原则，用以确定进行数据排序时数据值的具体位置。值的具体位置。 不同的排序规则将数据以不同顺序进行排序，最主要取决于排不同的排序规则将数据以不同顺序进行排序，最主要取决于排序规则是否区分大小写、特殊字符的排序规则等等。序规则是否区分大小写、特殊字符的排序规则等等。结论：结论： 我们要学会文件组的创建、设计和维护，以及利用文件组改善数据我们要学会文件组的创建、设计和维护，以及利用文件组改善数据库的性能。库的性能。文件组的概念文件组的概念： 文件组是一种逻辑结构，它允许文件

11、组是一种逻辑结构，它允许DBA组织数据文件，并把它们作为组织数据文件，并把它们作为一个逻辑单元进行管理。一个逻辑单元进行管理。 文件组可以控制数据库中各个对象的物理布局，这可提供大量可管文件组可以控制数据库中各个对象的物理布局，这可提供大量可管理性和性能方面的好处。理性和性能方面的好处。 SQL Server 支持支持两种两种文件组：主要文件组和用户定义的文件组。文件组：主要文件组和用户定义的文件组。主要文件组主要文件组包含主要数据文件以及任何没有被存储到另一文件组中的次包含主要数据文件以及任何没有被存储到另一文件组中的次要数据文件。要数据文件。 创建用户定义的文件组的目的是对次要文件进行分组

12、，并将数据库创建用户定义的文件组的目的是对次要文件进行分组，并将数据库对象分配给文件组。对象分配给文件组。问：每个文件组可以有多少个日志文件？问：每个文件组可以有多少个日志文件？ 可在不同的磁盘上创建多个数据文件，并创建用户定义的文件组以可在不同的磁盘上创建多个数据文件，并创建用户定义的文件组以包含这些文件。使用文件组主要的两个原因是提高性能以及控制数据的包含这些文件。使用文件组主要的两个原因是提高性能以及控制数据的物理放置布局。物理放置布局。多媒体：创建数据库多媒体：创建数据库 你需要为公司创建一个名为 Sales 的数据库，请使用 SSMS 按照要求实现该目标。请注意，将数据库的不同文件放

13、置于和操作系统不同的分区有助于提高数据库的性能。 创建数据库方法：创建数据库方法： 创建数据库的方法有以下三种：创建数据库的方法有以下三种： 1、使用、使用SQL Server管理平台创建数据库管理平台创建数据库 2、使用模板创建数据库、使用模板创建数据库 使用模板创建数据库使用模板创建数据库 ，用户根据提示操作，即可创建数据库。，用户根据提示操作，即可创建数据库。 使用使用 CREATE DATABASE 语句创建数据库：语句创建数据库：Transact-SQL语言使用语言使用CREATE DATABASE命令来创建数据库。该命令来创建数据库。该命令的语法如下命令的语法如下： CREATE

14、DATABASE database_name ON PRIMARY ，n ， ，n LOG ON ，n FOR RESTORE :=（NAME=logical_file_name， FILENAME=os_file_name ，SIZE=size ，MAXSIZE=max_size|UNLIMITED ，FILEGROWTH=growth_increment ） ，n :=FILEGROUP filegroup_name ，n各参数说明如下：各参数说明如下： database_name：数据库的名称，最长为128个字符。 PRIMARY：该选项是一个关键字，指定主文件组中的文件。 LOG ON

15、：指明事务日志文件的明确定义。 NAME=logical_file_name：指定数据库的逻辑名称，这是在SQL Server系统中使用的名称，是数据库在SQL Server中的标识符。 FILENAME =os_file_name ：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。 SIZE：指定数据库的初始容量大小。主文件大小至少与model数据库中的主文件相同。 ON：允许显式定义用于存储数据的文件。 UNLIMITED：指定了文件将一直增长到磁盘变满为止。 FILEGROUP filegroup_name ：这是文件组在数据库中的唯一逻辑名称。

16、 DEFULT：参数制定了该文件组为默认文件组。 FILEGROWTH=growth_increment：指定文件每次增加容量的大小，该值以当前文件大小的百分比或KB、MB、GB和TB来表示。当指定数据为0时，表示文件不增长。 MAXSIZE=max_size：指定操作系统文件可以增长到的最大尺寸。以KB、MB、GB和TB为单元，默认为MB。如果没有指定，则文件可以不断增长直到充满磁盘。提示：提示：区分NAME与FILENAME。NAME是数据库内部处理文件的逻辑名称，FILENAME 是数据库在磁盘上存放文件的文件名称，两者可以不同。CREATE DATABASE TeachDBON - 定

17、义数据库主文件 ( NAME = TeachDB_Data, - 主文件逻辑名称 FILENAME = D:DATATeachDB.mdf, - 操作系统文件名称，包含路径 SIZE = 20 MB, - 主文件初始大小 FILEGROWTH = 0 ) - 未启用自动增长LOG ON - 定义数据库日志文件( NAME = TeachDB_Log, - 日志文件逻辑名称 FILENAME = D:DATATeachDB_Log.ldf, - 包含路径的操作系统文件名称 SIZE = 5 MB, - 日志文件初始大小 FILEGROWTH = 0 )； -未启用自动增长场景：场景： 你正在设计

18、开发一个学校的教务系统。数据库采用SQL Server 2005 ，数据库名为TeachDB。数据库的初始大小为20 MB，日志文件的大小为5 MB。 举例举例：创建了一个 TeachDB数据库，该数据库的主数据文件逻辑名称为TeachDB_data ，物理文件名为 TeachDB.mdf，初始大小为 10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为 TeachDB_log，物理文件名为 TeachDB_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。CREATE DATABASE TeachDB ON PRIMARY ( NAME = Teach

19、DB, FILENAME= D:dataTeachDB.mdf,SIZE=10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)LOG ON ( NAME= TeachDB _log, FILENAME= D:data TeachDB_log.ldf ,SIZE=1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB ) 修改数据库：修改数据库： 场景：场景： 在数据库设计过程中，有时候需要对数据库进行修改。可以使用SSMS 对数据库进行修改，还可以使用 Transact-SQL 脚本语句来完成数据库修改工作。 利用利用SQL Server管

20、理平台修改数据库：管理平台修改数据库： 在 SQL Server 管理平台中，右击所要修改的数据库，从弹出的快捷菜单中选择【属性】选项，出现如下图所示的数据库属性设置对话框。 可以看到，修改或查看数据库属性时，属性页框比创建数据库时多了两个，即选项和权限页框。利用利用SQL Server管理平台修改数据库（选项、权限）管理平台修改数据库（选项、权限） 使用使用ALTER DATABASE语句修改数据库：语句修改数据库：ALTER DATABASE database ADD FILE ,.,n TO FILEGROUP filegroup_name | ADD LOG FILE ,.,n | R

21、EMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name| REMOVE FILEGROUP filegroup_name| MODIFY FILE 各参数说明如下：各参数说明如下： database：要更改数据库名称。 ADD FILE：指定要添加文件。 TO FILEGROUP ：指定要将指定文件添加到的文件组。 filegroup_name：要添加指定文件的文件组的名称。 ADD LOG FILE ：指定要将日志文件添加到指定的数据库。 ADD FILEGROUP ：指定要添加的文件组。 REMOVE FILE ：从数据库系统

22、表中删除文件描述并删除物理文件。（只有在文件为空时才能删除） REMOVE FILEGROUP ：从数据库中删除文件组并删除该文件组中所有文件。 （只有在文件为空时才能删除） MODIFY FILE ：指定要更改给定的文件，更改选项包括 NAME、SIZE 、FILEGROUPTH和MAXSIZE。注意：注意： 若要更改数据文件或日志文件的逻辑名称，应在 NAME 选项中指定要改名的逻辑文件名称，并在 NEWNAME 选项中指定文件的新逻辑名称。举例：举例：将两个数据文件和一个事务日志文件（将两个数据文件和一个事务日志文件（ testlog1 ）添加到）添加到 test 数据库数据库中。中。 （test1：默认路径初始值为：默认路径初始值