第 4 章 SQLSERVER 数据库管理

第4章 SQLSERVER数据库管理

本章的学习目标： • 了解数据库的存储结构 • 理解数据库文件和文件组的基本特征 • 了解SQL Server 2008 R2系统的数据库 • 了解数据库的物理存储方式和大小估算方法 • 熟练掌握用Management Studio工具和T-SQL语句创建数据库 • 熟练掌握用Management Studio工具和T-SQL语句修改数据库 • 熟练掌握扩大数据库的原因和方法 • 掌握数据库的分离和附加 • 掌握收缩数据库的原因和方法 • 掌握数据库的删除 • 掌握数据库的备份和还原

本章内容 • 4.1 SQL SERVER数据库的存储结构 • 4.2 数据库的创建 • 4.3 数据库的修改 • 4.4 数据库的分离和附加 • 4.5 数据库的收缩 • 4.6 数据库删除 • 4.7 数据库的备份和还原 • 4.8 本章小结

4.1 SQL SERVER数据库的存储结构 • 数据库的两种存储结构: • 逻辑存储结构和物理存储结构。 • 数据库物理存储结构表现为存储数据的各类操作系统文件，SQL Server 2008 R2数据库在磁盘上是以文件为单位存储的，由数据文件和事物日志文件组成，每个数据库至少要具有两种操作系统文件：一个数据文件和一个事务日志文件。 • 数据库的逻辑存储结构，是指数据库有哪些性质的信息组成，即一个数据库由若干用户可视的各种数据库对象构成，诸如表、视图、索引、存储过程等。

4.1.1 数据库对象 • 数据库的逻辑存储结构 • 数据库的逻辑存储结构，是指数据库有哪些性质的信息组成，即一个数据库由若干用户可视的各种数据库对象构成，诸如表、视图、索引、存储过程等。

SQL Server 2008 R2中常用的数据库对象 • 1.表：表是数据库中用于容纳所有数据的最常用的数据库对象，由行和列组成的二维的行列结构。 • 2.视图：视图是用户查看数据库表中数据的一种方式，它其实相当于一种虚拟表，但是可以像使用真实表一样使用视图。视图又被称为一种逻辑对象，并不占用物理空间，其作用像相当于查询，所包含的列和行的数据只来源于视图所查询的表（这种实际的表称为视图的基表），在引用视图时动态生成。 • 3.索引：数据库中的索引类似于书籍中的目录。对一个没有索引的表进行的查询操作，系统将检查表中的每一个数据行，这就好比在一本没有目录的书中查找信息。

4. 存储过程：存储过程是SQL Server 2008 R2服务器上一组预编译的Transact-SQL 语句，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。使用服务器上存储过程而不使用客户机本地上的Transact-SQL 程序的优点有：保证各个客户端操作一致性，提供安全机制（有权限的才能操作）及减少网络传输等。 • 5.触发器：触发器是一种特殊类型的存储过程，当执行某些操作导致表中的数据被修改时，触发器会自动触发执行。它主要用来实现复杂的数据完整性。 • 6.函数：SQL Server 2008 R2中包含有大量的函数，我们用函数可以完成特定的计算功能。在SQL Server 2008 R2中除了有内置函数，还允许用户自定义函数。

4.1.2 数据库文件及文件组 • 数据库的物理存储结构 • 数据库的物理存储结构，表现为存储数据的各类操作系统文件，SQL Server 2008 R2数据库在磁盘上是以文件为单位存储的，由数据文件和事物日志文件组成，每个数据库至少要具有两种操作系统文件：一个数据文件和一个事务日志文件。 • 数据文件可以隶属于某个文件组中以便于对该文件中数据存储的管理。

数据文件：又可以分成主数据文件和次数据文件两种形式。主数据文件是数据库的起点，用来存储数据库的启动信息和部分或全部数据，每一个数据库都有且仅有一个主数据文件。主数据文件名称的默认后缀是mdf。次数据文件是可选的，它们可以存储不在主数据文件中的全部数据和对象。数据库既可能没有次数据文件，也可能有多个次数据文件。次数据文件名称的默认后缀是ndf。数据文件：又可以分成主数据文件和次数据文件两种形式。主数据文件是数据库的起点，用来存储数据库的启动信息和部分或全部数据，每一个数据库都有且仅有一个主数据文件。主数据文件名称的默认后缀是mdf。次数据文件是可选的，它们可以存储不在主数据文件中的全部数据和对象。数据库既可能没有次数据文件，也可能有多个次数据文件。次数据文件名称的默认后缀是ndf。 • 事务日志文件： • 事务就是一个单元的工作，该单元的工作要么全部完成，要么全部不完成。SQL Server 2008 R2系统具有事务功能，可以保证数据库操作的一致性和完整性。 • 事务日志以操作系统文件的形式存在，在数据库中被称为事务日志文件。每一个数据库都至少有一个事务日志文件。事务日志文件名称的后缀默认是ldf。

主数据文件 次数据文件事务日志文件

文件组 • 文件组就是文件的逻辑集合。为了方便数据的管理和分配，文件组允许对文件进行分组以便于管理数据的分配或放置，可以把一些指定的文件组合在一起。 • 也就是说：当你的数据库中包含很多数据文件时，你可以将这些数据文件存储在不同的地方，然后用文件组把它们作为一个单元来管理。文件组对组内的所有文件都使用按比例填充策略，即当将数据写入文件组时，SQL Server 2008 R2根据文件中的可用空间量将按一定比例的数据写入文件组的每个文件，而不是将所有的数据先写满第一个文件，接着再写入下一个文件。 • 例如，在某个数据库中，3个文件(data1.ndf、data2.ndf和data3.ndf)分别创建在3个不同的磁盘驱动器中，然后为它们指定一个文件组fgroup1。以后，所创建的表可以明确指定放在文件组fgroup1上。对该表中数据的查询将分布在这3个磁盘上，因此，可以通过执行并行访问而提高查询性能。在创建表时，不能指定将表放在某个文件上，只能指定将表放在某个文件组上。因此，如果希望将某个表放在特定的文件上，那么必须通过创建文件组来实现。

文件组 • SQL Server 2008 R2一共有三种类型的文件组，它们分别是主文件组(primary)、用户定义文件组，还可选中一个文件组为默认文件组(default)。SQL Server 2008 R2至少包含一个文件组，即主文件组。主文件组包含主数据文件，存放系统表格等。Primary不能被更改。默认文件组（default），用来存放任何没有指定文件组的对象。任何时候只能有一个文件组被指定为default，默认情况下主文件组被当做默认的文件组。 • 使用文件和文件组时，应该考虑下列因素： • 一个文件或者文件组只能用于一个数据库，不能是多个数据库； • 一个文件只能是某一个文件组的成员，不能是多个文件组的成员； • 数据库的数据信息和日志信息不能放在同一个文件中，即数据文件和日志文件总是分开的； • 日志文件永远也不能是任何文件组的一部分。

4.1.3 SQL Server 2008 R2数据库类型 • SQL Server 2008 R2中的数据库有两种类型：系统数据库和用户数据库。 • 1. 系统数据库 • 系统数据库由master、model、msdb、tempdb和隐藏的Resource数据库组成。 • （1）master数据库：master数据库用于记录SQL Server 2008 R2中所有服务器级别的对象。这包括了服务器登录帐户、链接服务器定义以及端点。master数据库同时还记录服务器上其他所有数据库的信息。SQL Server 2008 R2并不把系统信息存储在master数据库中，而是存储在Resource数据库中。不过在master数据库中，系统信息逻辑呈现为sys架构。

4.1.3 SQL Server 2008 R2数据库类型 • （2）model数据库：model数据库是一个模板数据库。每当创建新的数据库时(包括系统数据库tempdb)，就会创建model数据库的一个副本，并以新创建数据库的名称重命名该副本。 • （3）msdb数据库 • 可以把msdb数据库看作是SQL Server代理的数据库，这是因为SQL Server 2008 R2代理广泛地使用msdb数据库存储自动化作业定义、作业计划、操作员定义以及警报定义。 • （4） tempdb数据库 • SQL Server 2008 R2使用tempdb数据库临时性地存储数据。在SQL Server 2008 R2操作中，tempdb数据库有广泛的运用，因此要确保SQL Server 2008 R2数据库有效的操作，就需要仔细计划和评估tempdb数据库的规模和位置。 • （5） Resource数据库 • 它是一个只读数据库，包含SQL Server 2008 R2实例使用的所有系统对象。

4.1.3 SQL Server 2008 R2数据库类型 • 2. 用户数据库 • 用户数据库就是用户创建的数据库。它们存储数据应用程序所使用的数据，这也是拥有一个数据库服务器的主要目的。

4.2 数据库的创建 • 数据库创建的方法： • 创建数据库的方法有多种：使用Management Studio工具创建数据库、可以使用CREATE DATABASE语句创建数据库。 • 在创建数据库之前要需估算所建数据库的大小和增幅。 • 创建数据库就是确定数据库名称、文件名称、数据文件大小、数据库的字符集、是否自动增长以及如何自动增长等信息的过程。数据库的名称必须满足系统的标识符规则。在命名数据库时，一定要使数据库名称简短和有一定的含义。

4.2.1 使用Management Studio工具创建数据库 • 要求：创建一个酒店管理系统所使用的数据库，名为JdglSys。因为酒店管理系统中涉及到的容纳数据的表大致有7个，估计总数据量为20000行记录，根据课本介绍的估算数据库大小的方法，估算 JdglSys数据库中的数据文件的大小为10M，日志文件为3M 。 • 步骤: • （1）从“开始”-“程序”-“Microsoft SQL Server 2008 R2”-“SQL Server 2008 R2 Management Studio”点击后，打开该工具，首先点击“连接”到SQL Server 2008 R2数据库引擎实例，详见图4-3连接Management Studio工具。

4.2.1 使用Management Studio工具创建数据库 图4-3 连接Management Studio工具

4.2.1 使用Management Studio工具创建数据库 • （2）展开该实例后，如图4-4 Management Studio 工具界面图。图4-4 Management Studio工具界面

4.2.1 使用Management Studio工具创建数据库 • （3）右击上图中的“数据库”，在弹出菜单中选择“新建数据库”，会弹出新建数据库窗口，如图4-5新建数据库所示。在“数据库名称”项中输入新数据库的名称JdglSys。下面数据库文件中的逻辑名称就会自动设置完成，注意：此处要修改一下数据文件的大小为10M，日志文件的大小为3M，单击“确定”按钮即完成。图4-5 新建数据库JdglSys

4.2.1 使用Management Studio工具创建数据库 • （4）创建完成JdglSys数据库后的Management Studio的工具界面如下：新建的JdglSys数据库图4-6 Management Studio工具界面

4.2.2 使用T-SQL语言创建数据库 • CREATE DATABASE语句的常用语法格式如下 : CREATE DATABASE database_name ON { [ PRIMARY ] ( NAME = logical_file_name , FILENAME = 'os_file_name' , [ , SIZE = size] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ] ) [ ,...n ] LOG ON { ( NAME = logical_file_name , FILENAME = 'os_file_name' , [ , SIZE = size] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ] ) [ ,...n ]

4.2.2 使用T-SQL语言创建数据库 Create database语句中参数说明： database_name：新数据库的名称。 ON ：指定用来存储数据库数据部分的磁盘文件（数据文件）。 LOG ON ：指定显式定义用来存储数据库日志的磁盘文件（日志文件）。 NAME logical_file_name：指定文件的逻辑名称。 FILENAME ：指定操作系统（物理）文件名称。 SIZE ：指定文件的大小。 MAXSIZE ：指定文件可增大到的最大大小。 FILEGROWTH ：指定文件的自动增量。也可以指定百分比。

4.2.1 使用Management Studio工具创建数据库 【例4-1】创建JdglSys数据库，其数据文件初始大小为10MB，最大值UNLIMITED，文件大小增长量为1MB，日志文件初始大小为3MB，最大大小为UMLIMITED，增量为10%。文件的位置在默认安装的位置。

4.2.1 使用Management Studio工具创建数据库 具体的T-SQL语句为： CREATE DATABASE JdglSys ON PRIMARY ( NAME = JdglSys_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSER VER\MSSQL\DATA\JdglSys_Data.mdf', SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1) LOG ON ( NAME = JdglSys_Log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSER VER\MSSQL\DATA\JdglSys_Log.ldf', SIZE = 3MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) GO

4.2.2 使用T-SQL语言创建数据库 • 连接上Management Studio工具，点击右上角的“新建查询”，出现如下图示，在其中输入该语句，点击“执行”即可。

4.2.3 查看数据库的相关信息 • SQL Server 2008 R2系统中，可以使用一些系统视图、存储过程查看有关数据库的基本信息。 • 常用的有以下几个： • 1. sys.databases：可以查看该服务器上所有数据库的基本信息。图4-8使用sys.databases系统视图查看服务器上的数据库

4.2.3 查看数据库的相关信息 • 2.sp_helpdb：可以查看该服务器上所有数据库或指定单个数据库的基本信息。如图4-9所示存储过程查看所有数据库。使用sp_helpdb JdglSys命令可以查看单个数据库的详细信息。图4-9 使用sp_helpdb存储过程查看所有数据库

4.2.3 查看数据库的相关信息 • 使用sp_helpdb JdglSys命令可以查看单个数据库的详细信息。图4-10 使用sp_helpdb存储过程查看单个数据库

4.2.3 查看数据库的相关信息 • 3. sp_helpfile：显示当前数据库关联的文件的名称及属性。如图4-11所示。其中，use语句用来改变当前数据库，把JdglSys数据库作为当前查询的数据库。图4-11使用sp_helpfile查看相关数据库的文件信息

4.2.3 查看数据库的相关信息 • 4.sp_helpfilegroup系统存储过程 • 查看JdglSys数据库中的所有文件组或某一个文件组的信息。 • 如图4-12所示。图4-12使用sp_helpfilegroup查看相关数据库的文件组信息

4.2.3 查看数据库的相关信息 • 5.sp_spaceused系统存储过程 • 显示由整个数据库保留和使用的磁盘空间。如图4-13所示。图4-13使用sp_spaceused查看数据库空间使用状况信息

4.3 数据库的修改 • 数据库创建之后，根据需要可以使用Management Studio工具或ALTER DATABASE语句对数据库进行修改。修改操作包括更改数据库名称、扩大数据库、修改数据库文件、管理数据库文件组等。 • 数据库修改的方法： • 修改数据库的方法也有两种：使用Management Studio工具修改数据库、可以使用alter DATABASE语句创建数据库。

4.3.1 使用Management Studio工具修改数据库 • 1.重命名数据库 • 数据库创建之后，一般情况下不要更改数据库的名称，因为许多应用程序都可能使用了该数据库的名称。数据库名称更改之后，需要修改相应的应用程序。但是，如果确实需要更改数据库名称，可以使用Management Studio工具修改数据库很轻松的修改数据库名。比如，修改数据库JdglSys为hotel。

4.3.1 使用Management Studio工具修改数据库 “选中” 要更名的数据库 “右击”“重命名” JdglSys可更改为hotel

4.3.1 使用Management Studio工具修改数据库 • 2.扩大数据库 • 在SQL Server 2008 R2系统中，如果数据库的数据量不断膨胀，可以根据需要扩大数据库的尺寸。有3种扩大数据库的方式。第一种方式是设置数据库为自动增长方式，可以在创建数据库时设置。第二种方式是直接修改数据库的数据文件或日志文件的大小，第三种方式是在数据库中增加新的次要数据文件或日志文件。

4.3.1 使用Management Studio工具修改数据库 • 例如，JdglSys数据库的大小是13MB，如果希望扩大到20MB（其中，数据文件大小15M，日志文件大小5M）。 • 有三种方法实现： • （1）、那么可以通过在创建数据库时，设定文件为自动增长的，一旦数据库中的数据量增多，数据文件或日志文件容量不够就会自动扩展增大。（此方法不再说明） • （2）、还可以使用修改文件的大小把数据文件改为15M，把日志文件改为5M。（介绍该方法） • （3）、还可以使用第三种方法，为该数据库增加一个大小为5MB的数据文件、一个2M的日志文件来达到。（介绍该方法）

4.3.1 使用Management Studio工具修改数据库 • 第二种方法：在Management Studio工具，“右击”JdglSys，在弹出菜单中选择“属性”，就会弹出 “数据库属性”对话框。 • 选择“文件”，就 • 可以看到该数据库相关 • 的文件，修改其中的数 • 据文件的初始大小为15M • ，日志文件的初始大小为 • 5M，点击“确定”即可。

4.3.1 使用Management Studio工具修改数据库 • 第三种方法： • 在Management Studio工具，“右击”JdglSys，就会弹出 “数据库属性”对话框，直接点击下面的“添加”按钮，在其中添加一行，把逻辑名称修改为JdglSys2，初始大小设为5M，其他不做修改。再点击“添加”按钮，添加一行，把逻辑名称修改为JdglSys_log2，文件类型点击下拉菜单，修改为“日志”，初始大小设定为2M，点击确定。也能够实现将数据库扩容为20M。

4.3.1 使用Management Studio工具修改数据库 图4-16使用Management Studio工具增加文件来扩大数据库

4.3.1 使用Management Studio工具修改数据库 • 3. 添加文件组 • 在数据库中可以很方便的添加新的文件组，可以在Management Studio工具，【选中】要添加文件组的数据库，【右击】，在弹出菜单中选中【属性】，选择【文件组】，点击下面的【添加】按钮，就可以添加新的文件组。 • 例如在数据库JdglSys中添加文件组SECOND。

4.3.2 使用T-SQL语言修改数据库 • 数据库的修改操作还可以使用T-SQL语句中的alter database来进行修改完成。Alter database语句的语法格式是： ALTER DATABASE database_name {ADD FILE <filespec>[,…n][TO FILEGROUP{filegroup_name}] |ADD LOG FILE <filespec>[,…n] |REMOVE FILE logical_file_name [WITH DELETE] |MODIFY FILE < filespec > |MODIFYname=new_database_name |ADD filegroup filegroup_name |MODIFY filegroup filegroup_name DEFAULT |REMOVE filegroup filegroup_name }

4.3.2 使用T-SQL语言修改数据库 • 其中： • <filespec>可以是： • ( NAME = logical_file_name , • FILENAME = 'os_file_name' , • [ , SIZE = size] • [ , MAXSIZE = { max_size | UNLIMITED } ] • [ , FILEGROWTH = growth_increment ] • ) [ ,...n ]

4.3.2 使用T-SQL语言修改数据库 • 可以使用Alter Database命令来完成数据库修改操作。如：重命名数据库、扩大数据库、修改数据库文件、管理数据库文件组等。 • 重命名数据库 • 使用ALTER DATABASE语句更改数据库名称的语法形式简写如下： • ALTER DATABASE database_name Modify Name = new_database_name; • 【例4-2】把demodb数据库更名为Studentdb。 • ALTER DATABASE demodb Modify Name = Studentdb;

4.3.2 使用T-SQL语言修改数据库 • 修改数据库文件 • 【例4-3】数据库JdglSys的数据文件JdglSys.mdf'的初始分配大小为10MB，请将其大小扩充20MB。 • ALTER DATABASE JdglSys • MODIFY FILE • (NAME =’JdglSys’, • FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys.mdf', • SIZE=20MB) • GO

4.3.2 使用T-SQL语言修改数据库 • 添加数据库文件 • 【例4-4】给数据库JdglSys添加一个新的数据文件，逻辑名称为JdglSys3，物理名称及位置为：C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA • \JdglSys3.ndf。大小为5M，每次增长1M，最大值为15M。增加一个日志文件逻辑名称为JdglSys_log3，物理名称及位置：C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys_log3.ldf。大小为2M，每次增长1M，最大值5M。

4.3.2 使用T-SQL语言修改数据库 • ALTER DATABASE JdglSys • ADD FILE • (NAME=JdglSys3, • FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys3.ndf', • SIZE = 5MB , • MAXSIZE = 15MB , • FILEGROWTH = 1MB • ) • Go • ALTER DATABASE JdglSys • ADD LOG FILE • (NAME=JdglSys_log3, • FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys_log3.ldf'’, • SIZE = 1MB , • MAXSIZE = 5MB , • FILEGROWTH = 1MB • )Go

4.3.2 使用T-SQL语言修改数据库

4.3.2 使用T-SQL语言修改数据库 • 添加数据文件到指定的文件组 • 【例4-5】给数据库JdglSys添加一个新的数据文件到文件组SECOND，逻辑名称为JdglSys4，物理名称及位置为：C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA • \JdglSys4.ndf。大小为2M，每次增长1M，最大值为10M。

4.3.2 使用T-SQL语言修改数据库 • alter database JdglSys • add file • ( • NAME =JdglSys4, • FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\JdglSys4.ndf', • SIZE = 1MB , • MAXSIZE = 10MB , • FILEGROWTH = 1MB • )to filegroup second • Go；

第 4 章 SQLSERVER 数据库管理