4 数据库管理.

Presentation on theme: "4 数据库管理."— Presentation transcript:

1 4 数据库管理

2 4.1 数据库存储结构 数据库的存储结构分为逻辑存储结构和物理存储结构两种。
1、数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成，SQL Server的数据库不仅仅只是数据的存储，所有与数据处理操作相关的信息都存储在数据库中。实际上，SQL Server的数据库是由诸如表、视图、索引等各种不同的数据库对象所组成，它们分别用来存储特定信息并支持特定功能，构成数据库的逻辑存储结构。 2、数据库的物理存储结构则是讨论数据库文件是如何在磁盘上存储的。数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个数据库文件和一个事务日志文件。

3 4.1.1 数据库文件 1. 主数据库文件（Primary Database File）
2. 辅助数据库文件（Secondary Database File） 3. 事务日志文件

4 4.1.2 数据库文件组 为了便于分配和管理，SQL Server允许将多个文件归纳为同一组，并赋予此组一个名称，这就是文件组。
与数据库文件一样，文件组也分为主文件组（Primary File Group）和次文件组（Secondary File Group）。

5 4.2 创建、修改和删除数据库 创建数据库 修改数据库 删除数据库

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

7 4.2.1 创建数据库 创建数据库的方法有以下三种（实际为两种）：
创建数据库 创建数据库的方法有以下三种（实际为两种）： 1、使用模板创建数据库（实际上也是使用Transact-SQL语言创建数据库） 2、使用SQL Server管理平台创建数据库

8 4.2.1 创建数据库 1、使用模板创建数据库 以下图4-1 到图4-2是使用模板创建数据库 ，用户根据提示操作，即可创建数据库。
创建数据库 1、使用模板创建数据库 以下图4-1 到图4-2是使用模板创建数据库 ，用户根据提示操作，即可创建数据库。 图4-1 选择创建数据库模板对话框

9 4.2.1 创建数据库 1、使用模板创建数据库 在图4-1中双击“创建数据库”命令，就会出现创建数据库的SQL语言模板，如图4-2所示。
创建数据库 1、使用模板创建数据库 在图4-1中双击“创建数据库”命令，就会出现创建数据库的SQL语言模板，如图4-2所示。 图4-2 创建数据库的SQL语言模板

10 创建数据库 1、使用模板创建数据库 Transact-SQL语言使用CREATE DATABASE命令来创建数据库。该命令的语法如下： CREATE DATABASE database_name [ON [PRIMARY] [<filespec> [，…n] [，<filegroupspec> [，…n]] ] [LOG ON {<filespec> [，…n]}] [FOR RESTORE] <filespec>::=（[NAME=logical_file_name，] FILENAME=‘os_file_name’ [，SIZE=size] [，MAXSIZE={max_size|UNLIMITED}] [，FILEGROWTH=growth_increment] ） [，…n] <filegroupspec>::=FILEGROUP filegroup_name <filespec> [，…n]

11 4.2.1 创建数据库 1、使用模板创建数据库 database_name：数据库的名称，最长为128个字符。
创建数据库 1、使用模板创建数据库 各参数说明如下： database_name：数据库的名称，最长为128个字符。 PRIMARY：该选项是一个关键字，指定主文件组中的文件。 LOG ON：指明事务日志文件的明确定义。 NAME：指定数据库的逻辑名称，这是在SQL Server系统中使用的名称，是数据库在SQL Server中的标识符。 FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。 SIZE：指定数据库的初始容量大小。 MAXSIZE：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增长直到充满磁盘。 FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。

12 4.2.1 创建数据库 2、使用SQL Server管理平台创建数据库
创建数据库 2、使用SQL Server管理平台创建数据库 （1）在SQL Server管理平台中，在数据库文件夹或其下属任一用户数据库图标上右击，从弹出的快捷菜单中选择新建数据库选项，出现如图4-3所示的对话框。 图4-3 创建数据库对话框

13 4.2.1 创建数据库 2、使用SQL Server管理平台创建数据库
创建数据库 2、使用SQL Server管理平台创建数据库 （2）在选项页框中，如图4-4所示，可设置数据库的排序规则，恢复模式，兼容级别以及其他一些选项的设置。 图4-4 创建数据库的选项页框

14 4.2.1 创建数据库 2、使用SQL Server管理平台创建数据库
创建数据库 2、使用SQL Server管理平台创建数据库 （3）在文件组页框中，如图4-5所示，可设置或添加数据库文件和文件组的属性，如是否只读，是否为默认值等。 图4-5 创建数据库的文件组页框

15 创建数据库 例4-1　创建了一个Test数据库，该数据库的主数据文件逻辑名称为Test_data，物理文件名为Test.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为Test_log，物理文件名为Test.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。 程序清单如下： CREATE DATABASE test ON PRIMARY ( NAME = 'test', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\data\test.mdf', SIZE=10240KB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) LOG ON ( NAME='test_log', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL\data\test_log.ldf', SIZE=1024KB, MAXSIZE = 5120KB, FILEGROWTH = 1024KB ) GO

16 4.2.2 修改数据库 利用SQL Server管理平台修改数据库 图4-6 数据库属性设置对话框
图4-6 数据库属性设置对话框

17 4.2.2 修改数据库 利用SQL Server管理平台修改数据库 图4-7 数据库选项设置对话框

18 4.2.2 修改数据库 利用SQL Server管理平台修改数据库 图4-8 数据库权限设置对话框

19 4.2.2 修改数据库 2. 使用ALTER DATABASE语句修改数据库 ALTER DATABASE语法形式如下：
Alter database databasename {add file<filespec>[,…n] [to filegroup filegroupname] |add log file <filespec>[,…n] |remove file logical_file_name [with delete] |modify file <filespec> |modify name=new_databasename |add filegroup filegroup_name |remove filegroup filegroup_name |modify filegroup filegroup_name {filegroup_property|name=new_filegroup_name}} 图4-6 数据库属性设置对话框

20 4.2.2 修改数据库 2. 使用ALTER DATABASE语句修改数据库
例4-3　将两个数据文件和一个事务日志文件添加到test数据库中。 程序清单如下： ALTER DATABASE Test ADD FILE (NAME = Test1, FILENAME='c:\Program Files\Microsoft SQL Server\MSSQL\Data\test1.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB), (NAME = Test2, FILENAME='c:\Program Files\Microsoft SQL Server\MSSQL\Data\test2.ndf', SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 1MB) GO ADD LOG FILE ( NAME = testlog1, FILENAME='c:\Program Files\Microsoft SQL Server\MSSQL\Data\testlog1.ldf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB)

21 4.2.3 删除数据库 利用SQL Server管理平台删除数据库 图4-9 确认删除数据库对话框
图4-9 确认删除数据库对话框

22 4.2.3 删除数据库 2.利用Drop语句修改数据库 Drop语句可以从SQL Server中一次删除一个或多个数据库。其语法如下：
Drop database database_name[,…n] 例4-4　删除创建的数据库Test。 程序清单如下： drop database Test 图4-6 数据库属性设置对话框

23 4.3 数据库备份 备份概述 创建备份设备 备份的执行

24 4.3.1 备份概述 Microsoft SQL Server 2005提供了高性能的备份和还原机制。数据库备份可以创建备份完成时数据库内存在的数据的副本，这个副本能在遇到故障时恢复数据库。这些故障包括：媒体故障，硬件故障（例如，磁盘驱动器损坏或服务器报废），用户操作错误（例如，误删除了某个表），自然灾害等。此外，数据库备份对于例行的工作（例如，将数据库从一台服务器复制到另一台服务器、设置数据库镜像、政府机构文件归档和灾难恢复）也很有用。 对SQL Server数据库或事务日志进行备份时，数据库备份记录了在进行备份这一操作时数据库中所有数据的状态，以便在数据库遭到破坏时能够及时地将其恢复。SQL Server备份数据库是动态的，在进行数据库备份时，SQL Server允许其他用户继续对数据库进行操作。执行备份操作必须拥有对数据库备份的权限许可，SQL Server只允许系统管理员、数据库所有者和数据库备份执行者备份数据库。备份是数据库系统管理的一项重要内容，也是系统管理员的日常工作。

25 4.3.1 备份概述 SQL Server 2005提供了四种不同的备份方式，它们分别为： （1）完整备份和完整差异备份
（2）部分备份和部分差异备份 （3）事务日志备份 （4）数据库文件和文件组备份。

26 4.3.2 创建备份设备 备份或还原操作中使用的磁带机或磁盘驱动器称为“备份设备”。
在创建备份时，必须选择要将数据写入的备份设备。Microsoft SQL Server 2005 可以将数据库、事务日志和文件备份到磁盘和磁带设备上。

27 4.3.2 创建备份设备 1. 使用SQL Server 管理平台创建备份设备 图4-11 输入备份设备属性对话框
图 输入备份设备属性对话框

28 4.3.2 创建备份设备 2. 使用系统存储过程创建备份设备
在SQL Server 中，可以使用sp_addumpdevice语句创建备份设备，其语法形式如下： sp_addumpdevice {‘device_type’} [,’logical_name’][,’physical_name’][,{{controller_type|’device_status’}}] 其中，device_type表示设备类型，其值可为disk和tape。logical_name表示设备的逻辑名称。physical_name表示设备的实际名称。controller_type和device_status可以不必输入。 例4-5　在磁盘上创建了一个备份设备。 程序清单如下： use master exec sp_addumpdevice ‘disk’, ‘test_backup’, ‘C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\test_backup.bak’

29 4.3.3 备份的执行 使用SQL Server 管理平台进行备份

30 4.3.3 备份的执行 使用SQL Server 管理平台进行备份 图4-13 SQL Server备份对话框

31 4.3.3 备份的执行 使用SQL Server 管理平台进行备份 图4-14 选择备份目的对话框
（3）单击“添加”按钮可以选择将备份添加备份文件还是设备，如图4-14所示。 图 选择备份目的对话框 图 SQL Server备份对话框

32 4.3.3 备份的执行 使用SQL Server 管理平台进行备份 （4）备份数据库的选项页框 图 备份数据库的选项页框

33 4.3.3 备份的执行 2.使用备份向导 图4-16 备份数据库模板
（1）在SQL Server管理平台中，点击视图菜单中的模板资源管理器。 （2）模板资源管理器中的模板是分组列出的。展开“backup”，再双击“backup database”。 在“连接到数据库引擎”对话框中，填写连接信息，再单击“连接”。此时将打开一个新查询编辑器窗口，其中包含“备份数据库”模板的内容，如图4-16所示。 图 备份数据库模板

34 4.3.3 备份的执行 2.使用备份向导 （3）按照backup database的语法规则，书写数据库备份的sql语句，完成后执行此语句，即可完成数据库备份的操作。 Backup语句的语法形式如下： Backup database to <backup_file>[,…n] [with [[,]format] [[,]{init|noinit}] [[,]restart] ]

35 4.3.3 备份的执行 2.使用备份向导 例4-7 将数据库db备份到备份设备db.bak上，使用WITH FORMAT子句初始化备份设备。
程序清单如下： BACKUP DATABASE db TO DISK=' C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\db.bak' WITH FORMAT

36 4.4 还原数据库 还原概述 还原数据库

37 4.4.1 还原概述 数据库备份后，一旦系统发生崩溃或者执行了错误的数据库操作，就可以从备份文件中还原数据库。数据库还原是指将数据库备份加载到系统中的过程。系统在还原数据库的过程中，自动执行安全性检查、重建数据库结构以及完成填写数据库内容。安全性检查是还原数据库时必不可少的操作。这种检查可以防止偶然使用了错误的数据库备份文件或者不兼容的数据库备份覆盖已经存在的数据库。SQL Server还原数据库时，根据数据库备份文件自动创建数据库结构，并且还原数据库中的数据。

38 还原数据库 由于数据库的还原操作是静态的，所以在还原数据库时，必须限制用户对该数据库进行其他操作，因而在还原数据库之前，首先要设置数据库访问属性。如图4-17所示。 图4-17 设置数据库访问属性对话框

39 4.4.2 还原数据库 1. 使用SQL Server管理平台还原数据库 图4-18 还原数据库对话框
还原数据库 1. 使用SQL Server管理平台还原数据库 （1）打开SQL Server管理平台，在数据库上单击鼠标右键，从弹出的快捷菜单中选择“任务”选项，再选择“还原数据库”命令，弹出还原数据库对话框，如图4-18所示。 图 还原数据库对话框

40 4.4.2 还原数据库 1. 使用SQL Server管理平台还原数据库 （2）选中“选项”页框，进行其他选项的设置，如图4-19所示
还原数据库 1. 使用SQL Server管理平台还原数据库 （2）选中“选项”页框，进行其他选项的设置，如图4-19所示 图 还原数据库对话框-选项页框

41 4.4.2 还原数据库 2.使用Transact-SQL语句还原数据库
还原数据库 2.使用Transact-SQL语句还原数据库 Transact-SQL提供了restore语句还原数据库，其语法形式如下： Restore database [from <backup_device[],…n>] [with [[,]file=file_number] [[,]move ‘logical_file_name’ to ‘operating_system_file_name’] [[,]replace] [[,]{norecovery|recovery|standby=undo_file_name}] ] |{disk|tape|pipe}

42 4.4.2 还原数据库 2.使用Transact-SQL语句还原数据库
还原数据库 2.使用Transact-SQL语句还原数据库 例4-8　从backup_company备份设备中还原数据库company。 程序清单如下： use master restore database company from backup_company

43 4.5 数据库维护 数据库维护概述 数据库维护计划向导

44 4.5.1 数据库维护概述 数据库创建后，所有的对象和数据均已添加且都在使用中，需要对其进行维护，数据库的维护可以使它保持运行的最佳状态。例如，定期备份数据库是很重要的。创建数据库维护计划可以让SQL Server自动而有效地维护数据库，为系统管理员节省大量时间，也可以防止延误数据库的维护工作。 在 SQL Server 2005数据库引擎中，维护计划可创建一个作业以按预定间隔自动执行这些维护任务。 维护计划向导可以用于设置核心维护任务，从而确保数据库执行良好，做到定期备份数据库以防系统出现故障，对数据库实施不一致性检查。维护计划向导可创建一个或多个 SQL Server 代理作业，代理作业将按照计划的间隔自动执行这些维护任务。

45 4.5.2 数据库维护计划向导 利用数据库的维护计划向导可以方便地设置数据库的核心维护任务，以便于定期地执行这些任务，其创建数据库维护计划的步骤如下： （1）展开服务器。 （2）展开“管理”文件夹，右键单击“维护计划”，然后选择“维护计划向导”。 （3）单击“下一步”按钮，则会出现选择目标服务器对话框，如图4-21所示，选择服务器名称，身份验证模式。 （4）单击“下一步”按钮，就会出现“选择维护任务”对话框，如图4-22所示。 图 选择目标服务器对话框 图 选择维护任务对话框

46 4.5.2 数据库维护计划向导 （5）单击“下一步”按钮，则会出现“选择维护任务顺序”对话框，在此对话框中可以改变执行任务的顺序，如图4-23所示。 （6）单击“下一步”按钮，如图4-24所示，出现定义“数据库检查完整性”对话框，选择进行维护的数据库。 图 选择维护任务顺序对话框 图 定义“数据库检查完整性”对话框

47 4.5.2 数据库维护计划向导 （7）单击“下一步”按钮，出现定义“收缩数据库”对话框，如图4-25所示。可以确定收缩数据库的条件，收缩后保留的可用空间，释放后的空间存放位置。 （8）单击“下一步”按钮，则会出现定义“重新组织索引”任务对话框，如图4-26所示。其中可以选择数据库，数据库对象类型（表，视图），具体的表或视图。 图 定义“收缩数据库”对话框 图 定义“重新组织索引”对话框

48 4.5.2 数据库维护计划向导 （9）单击“下一步”按钮，则会出现定义“重新生成索引”任务对话框，如图4-27所示。其中可以设定可用空间选项及高级选项。 （10）单击“下一步”按钮，则出现定义“更新统计信息”任务对话框，如图4-28所示。 图 定义“重新生成索引”对话框 图 定义“重新统计信息”对话框

49 4.5.2 数据库维护计划向导 （11）单击“下一步”按钮，出现定义“清除历史记录”任务对话框，如图4-29所示。其中可以选择要删除的历史数据。 （12）单击“下一步”按钮，出现定义“备份数据库（完整）”任务对话框，如图4-30所示。其中可以设定备份组件，备份目标等。 图 定义“清除历史记录”任务对话框 图 定义“备份数据库（完整）”任务对话框

50 4.5.2 数据库维护计划向导 （13）单击“下一步”按钮，出现定义“备份数据库（事务日志）”任务对话框，如图4-31所示。
（14）单击“下一步”按钮，出现“选择计划属性”对话框，如图4-32所示。其中可设定执行的频率和起止时间。 图 定义“备份数据库（事务日志）”任务对话框 图 “选择计划属性”对话框

51 4.5.2 数据库维护计划向导 （15）单击“下一步”按钮，出现“选择报告”选项对话框，如图4-33所示。（16）单击“下一步”按钮，出现“完成该向导”对话框，如图4-34所示。 图 维护计划操作报告保存或分发对话框 图 指定数据库维护计划名称对话框

52 4.5.2 数据库维护计划向导 （17）执行的进度及结果如图4-35所示。 图 “维护计划向导进度”对话框