1
第五章 管理事务和管理数据库
? 管理事务
? 管理数据库
2
事务的由来
? 使用 DELETE 命令或 UPDATE 命令对数据库进行更
新时, 一次只能操作一个表, 这会带来数据库的数据
不一致的问题 。 例如企业取消了后勤部, 需要将后勤
部从 department表中删除, 要修改 department 表,
而 employee 表中的部门编号与后勤部相对应的员工
也应删除 。 因此, 两个表都需要修改, 这种修改只能
通过两条 DELETE 语句进行 。 假设后勤部编号为
1012
? 第一条 DELETE 语句修改 department 表
? delete from department
? where dept_id = ’1012’
? 第二条 DELETE 语句修改 employee 表
? delete from employee where dept_id = ’1012’
3
? 在执行第一条 DELETE 语句后, 数据库中的数
据已处于不一致的状态 。 因为此时已经没有后
勤部了, 但 employee 表中仍然保存着属于后
勤部的员工记录 ； 只有执行了第二条 DELETE
语句后, 数据才重新处于一致状态 。 但是如果
执行完第一条语句后, 计算机突然出现故障,
无法再继续执行第二条 DELETE 语句, 则数据
库中的数据将处于永远不一致的状态 。 因此必
须保证这两条 DELETE 语句同时执行 。 为解决
类似的问题, 数据库系统通常都引入了事务
Transaction 的概念 。
4
事务的概念及特性
? 事务：指作为单个逻辑工作单元执行的
一系列操作，而这些逻辑工作单元需要
具有原子性，一致性，隔离性和持久性
四个属性，统称 ACID特性。
? 原子性：指事务必须是原子工作单元，
即对于事务所进行数据修改，要么全都
执行，要么全都不执行
5
? 一致性：指事务在完成时，必须使所有
的数据都保持一致性状态，而且在相关
数据库中，所有规则都必须应用于事务
的修改，以保持所有数据的完整性。事
务结束时，所有的内部数据结构都必须
是正确的。
6
? 隔离性：指由并发事务所做的修改必须与
任何其他并发事务所做的修改相隔离。事
务查看数据时数据所处的状态，要么是被
另一并发事务修改之前的状态，要么是被
另一事务修改之后的状态，即事务不会查
看正在由另一个并发事务正在修改的数据。
? 持久性：指事务完成之后，它对于系统的
影响是永久性的，即使出现系统故障也是
如此。
7
事务的类型
? 隐式事务,每次执行 SQL Server的任何
数据修改语句时，它都是一个隐式事务。
例如：下列 SQL语句是一个独立事务,
? Insert talbe values(1,’abc’)
? Update table set col1=5 where col1=1
? Delete from table1 where col1=5
? go
8
显式事务
? 通常在程序中用 BEGIN TRANSACTION 命令来标识一
个事务的开始, 用 COMMIT TRANSACTION 命令标识
事务结束 ； 这两个命令之间的所有语句被视为一体 。 只
有执行到 COMMIT TRANSACTION 命令时, 事务中对
数据库的更新操作才算确认 。 和 BEGIN…END 命令类
似 。 这两个命令也可以进行嵌套, 即事务可以嵌套执行 。
9
? 这两个命令的语法如下
? BEGIN TRAN[SACTION] [transaction_name |
@tran_name_variable]
? COMMIT [ TRAN[SACTION] [transaction_name |
@tran_name_variable] ]
? 其中 BEGIN TRANSACTION 可以缩写为 BEGIN TRAN 。
COMMIT TRANSACTION可以缩写为 COMMIT TRAN
或 COMMIT 。
? transaction_name,指定事务的名称, 只有前 32 个字
符会被系统识别 。
? @tran_name_variable,用变量来指定事务的名称变量 。
只能声明为 CHAR VARCHAR NCHAR 或 NVARCHAR
类型
10
? 例, 删除后勤部
? declare @transaction_name varchar(32)
? select @transaction_name = ' my_transaction_delete '
? begin transaction @transaction_name
? go
? use sample
? go
? delete from department where dept_id = ’1012’
? go
? delete from employee where dept_id = ’1012’
? go
? commit transaction my_transaction_delete
? go
11
事务回滚
? 事务回滚 (Transaction Rollback) 是指当事务中
的某一语句执行失败时,将对数据库的操作恢复到
事务执行前或某个指定位置 。
? 事务回滚使用 ROLLBACK TRANSACTION 命令,
其语法如下
? ROLLBACK [TRAN[SACTION]
[transaction_name | @tran_name_variable
? | savepoint_name | @savepoint_variable] ]
? 其中 savepoint_name 和 @savepoint_variable
参数用于指定回滚到某一指定位置 。
? 如果要让事务回滚到指定位置, 则需要在事务中
设定保存点 Save Point 。
12
? 所谓保存点是指定其所在位置之前的事务语句
不能回滚的语句, 即此语句前面的操作被视为
有效 。
? 其语法如下,
? SAVE TRAN[SACTION] {savepoint_name |
@savepoint_variable}
? 各参数说明如下,
? avepoint_name,指定保存点的名称 。 同事务
的名称一样, 只有前 32 个字符会被系统识别 。
? @savepoint_variable,用变量来指定保存点
的名称变量, 只能声明为 CHAR,VARCHAR,
NCHAR 或 NVARCHAR 类型 。
13
? 例, 删除后勤部再将后勤部的职工划归到经理室
? begin transaction my_transaction_delete
? use sample
? go
? delete from department where dept_id = ’1012’
? save transaction after_delete
? update employee set dept_id = ’1001’
where dept_id = ’1012’
? if @@error!=0 or @@rowcount=0 then
? begin
? rollback tran after_delete /* 回滚到保存点
after_delete
? 如果使用 rollback my_transaction_delete 则会回滚到事
务开始前 */
? print ‘更新员工信息表时产生错误 ’
? commit transaction my_transaction_delete
? go
14
事务工作机制
? 例如,begin tran
? Insert table1 values(1,’abc’)
? Update table1 set col1=5 where col1=1
? Delete from table1 where col1=5
? Commit tran
15
? 1,当 begin tran语句到达数据库时,sql server分
析出这是事务的开始,SQL server找到下一个可用
的内存日志页面，并给新事务分配一个事务 ID。
? 2,接着运行插入语句，新的行被记录到事务日志
中，数据页面在内存中进行修改，若所需页面不
在内存中，则从磁盘调出。
? 3,update语句以类似方式运行。
? 4、当 sql server 收到 commint tran时，日志页面被
写道数据库的日志设备上，这样才能保证日志页
面可被恢复。由于日志变化写入了硬盘，它保证
事务是可恢复的，即使掉电了或在数据页写入磁
盘时数据库崩溃了，也能进行事务恢复。
16
检查点处理
? 问题：系统何时将数据页写入磁盘呢？
? 日志页面是在 commit tran时写入磁盘的，
那么何时将数据页面写入磁盘呢？在处
理检查点时。
? 检查点的概念：是 sql server将数据页面
从内存拷贝到磁盘时的内部处理点。
? 检查点帮助确保恢复已提交事务而不会
花费额外的时间。当检查点出现时，将
写入一个日志项以指示所有修改了的页
面已写入磁盘。
17
锁 -锁的概念
? 锁 （ Lock) 是在多用户环境下对资源访问的一
种限制机制 。 当对一个数据源加锁后, 此数据
源就有了一定的访问限制, 我们就称对此数据
源进行了锁定 。 在 SQL Server中可以对以下
的对象进行锁定,
? 数据行, 数据页中的单行数据
? 索引行, 索引页中的单行数据即索引的键值
? 页, 页是 SQL Server 存取数据的基本单位其
大小为 8KB
? 盘区, 一个盘区由 8 个连续的页组成
? 表
? 数据库
18
锁的类别
? 在 SQL Server 中, 从数据库系统的角度
来看, 锁分为以下三种类型,
? 独占锁 Exclusive Lock,独占锁锁定的资
源只允许进行锁定操作的程序使用, 其它
任何对它的操作均不会被接受 。 执行数据
更新命令, 即 INSERT,UPDATE 或
DELETE 命令时 SQL Server 会自动使用
独占锁 。 但当对象上有其它锁存在时, 无
法对其加独占锁, 独占锁一直到事务结束
才能被释放 。
19
? 共享锁 Shared Lock,共享锁锁定的资源可以
被其它用户读取, 但其它用户不能修改它 。 在
SELECT 命令执行时, SQL Server 通常会对
对象进行共享锁锁定, 通常加共享锁的数据页
被读取完毕后, 共享锁就会立即被释放
? 更新锁 Update Lock,更新锁是为了防止死锁
而设立的 。 当 SQL Server 准备更新数据时,
它首先对数据对象作更新锁锁定, 这样数据将
不能被修改, 但可以读取 。 等到 SQL Server
确定要进行更新数据操作时, 它会自动将更新
锁换为独占锁 。 但当对象上有其它锁存在时,
无法对其作更新锁锁定 。
20
查看锁
? 可以通过企业管理器或存储过程来查看锁
? 用 Enterprise Manager 查看锁, 在企业管理器中
选择目录树窗口中,管理, 文件夹下,当前活动” 中的
锁 /进程 ID 节点, 则可以查看当前锁定的进程 。 选择同
级的 锁 /对象 节点下的相应字节点, 则可以查看当前锁定
的对象 。 若右键单击任务板窗口中的对象, 从快捷菜单
中选择属性选项, 则会出现锁的进程细节对话框, 在此
可以刷新或杀死锁的进程 。
? 杀死进程还可以用如下 Transact-SQL 命令来进行
? KILL spid
? spid 是 System Process ID,即系统进程编号的缩写 。
21
? 用系统存储过程 Sp_lock 查看锁
? 存储过程 Sp_lock 的语法如下
? sp_lock spid
? SQL Server 的进程编号 spid 可以在
master.dbo.sysprocesses 系统表中查到, spid 是 INT
类型的数据, 如果不指定 spid 则显示所有的锁 。
? 例, 显示当前系统中所有的锁
? use master
? exec sp_lock
? 例,use master
? exec sp_lock 52
22
死锁及其防止
? 死锁 是在多用户或多进程状况下, 为使用同一资源而产生的无法
解决的争用状态 。 通俗地讲, 就是两个用户各占用一个资源, 两
人都想使用对方的资源但同时又不愿放弃自己的资源, 就一直等
待对方放弃资源 。 如果不进行外部干涉就将一直耗下去 。 死锁会
造成资源的大量浪费, 甚至会使系统崩溃 。 在 SQL Server 中解
决死锁的原则是牺牲一个比两个都死强, 即挑出一个进程作为牺
牲者, 将其事务回滚并向执行此进程的程序发送编号为 1205 的
错误信息而防止死锁的途径就是不能让满足死锁条件的
? 情况发生为此用户需要遵循以下原则
? 尽量避免并发地执行涉及到修改数据的语句 。
? 要求每个事务一次就将所有要使用的数据全部加锁, 否则就不予
执行 。
? 预先规定一个封锁顺序, 所有的事务都必须按这个顺序对数据执
行封锁 。 例如不同的过程在事务内部对对象的更新执行顺序应尽
量保持一致 。
? 每个事务的执行时间不可太长, 对程序段长的事务可考虑将其分
割为几个事务 。
23
数据库管理
? 数据库的存储结构
? 创建、修改和删除数据库
? 数据库备份
24
数据库的存储结构
? 数据库文件
? 数据库文件组
25
数据库文件
? 逻辑存储结构和物理存储结构
? 一、数据库的逻辑存储结构指的是数据库
是由哪些性质的信息所组成,SQL Server的数
据库不仅仅只是数据的存储，所有与数据处理
操作相关的信息都存储在数据库中。
? 二、数据库的物理存储结构则是讨论数据
库文件是如何在磁盘上存储的，数据库在磁盘
上是以文件为单位存储的，由数据库文件和事
务日志文件组成，一个数据库至少应该包含一
个数据库文件和一个事务日志文件。
26
数据库文件
? 在 SQL Server 中，数据库是由数据库文
件和事务日志文件组成的。一个数据库
至少应包含一个数据库文件和一个事物
日志文件。
27
数据库文件
? 数据库文件是存放数据库数据和数据库对象的文件 。 一
个数据库可以有一个或多个数据库文件, 一个数据库文
件只属于一个数据库 。 当有多个数据库文件时, 有一个
文件被定义为主数据库文件, 扩展名为,mdf,它用来
存储数据库的启动信息和部分或全部数据 。 一个数据库
只能有一个主数据库文件, 其它数据库文件被称为次数
据库文件, 扩展名为,ndf,用来存储主文件没存储的
其它数据 。
? 采用多个数据库文件来存储数据的优点体现在,
? 数据库文件可以不断扩充而不受操作系统文件大小的限
制
? 可以将数据库文件存储在不同的硬盘中, 这样可以同时
对几个硬盘做数据存取
? 提高了数据处理的效率 。
28
数据库文件注意事项
? 数据是存储在邻接磁盘空间的 8KB 块中
的, 这些块称为 页
? 行不能跨页
? 表、其他数据库对象和索引存储在扩展
盘区中
? 事务日志文件包含恢复数据库所必需的
所有信息，以防系统故障
29
事务日志文件
? 事务日志文件是用来记录数据库更新情况的文件, 扩展名
为,ldf。 例如使用 INSERT,UPDATE,DELETE 等对数据库
进行更改的操作, 都会记录在此文件中 。 而如 SELECT 等
对数据库内容不会有影响的操作, 则不会记录在案 。 一个
数据库可以有一个或多个事务日志文件 。
? SQL Server 中采用 （ Write-Ahead） 提前写方式的事务,
即对数据库的修改先写入事务日志中, 再写入数据库 。 其
具体操作是系统先将更改操作写入事务日志中, 再更改存
储在计算机缓存中的数据 。 为了提高执行效率, 此更改不
会立即写到硬盘中的数据库, 而是由系统以固定的时间间
隔执行 CHECKPOINT 命令, 将更改过的数据批量写入硬盘 。
SQL Server 有个特点, 它在执行数据更改时, 会设置一
个开始点和一个结束点 。 如果尚未到达结束点, 就因某种
原因使操作中断, 则在 SQL Server 重新启动时会自动恢
复已修改的数据, 使其返回未被修改的状态
30
数据库文件组
? 为了便于分配和管理,SQL Server允许将
多个文件归纳为同一组，并赋予此组一个
名称，这就是文件组。 系统管理员可以为
每个磁盘驱动器创建文件组，然后将特定
的表、索引或表中的 text,ntext,image数
据指派给特定的文件组。
? 文件不可以在文件组之间共享。表、索引
或表中的 text,ntext,image数据可以与文
件组相关联，这时，它们的所有页都将分
配到与其关联的文件组中。日志文件不能
作为文件组的一部分，日志空间与数据空
间将分开进行管理。
31
数据库文件组
? 与数据库文件一样，文件组也分为主文件组和次
文件组 。
? 一个文件只能存在于一个文件组中，一个文件组
也只能被一个数据库使用。主文件组中包含了所
有的系统表。当建立数据库时，主文件组包括主
数据库文件和未指定给其它组的其它文件，系统
表的所有页均分配在主文件组中。在次文件组中
可以指定一个缺省文件组，那么在创建数据库对
象时，如果没有指定将其放在哪一个文件组中，
就会将它放在缺省文件组中。如果没有指定缺省
文件组，则主文件组为缺省文件组。
32
文件组
? 文件组是一个或多个文件的集合，构成分配和
管理的单元
? 可以在一开始创建数据库时创建，也可以在以
后多个文件添加到数据库中时再创建
? 文件组只能包含数据文件
? 一个文件不能属于多个文件组
? 表、索引,text,ntext 和 image 数据可以与一
个特定的文件组相关
33
创建用户数据库
? 每个数据库都由以下几个部分的数据库
对象所组成,
? 关系图、表、视图、存储过程、用户、
角色、规则、默认、用户自定义数据类
型和用户自定义函数。
34
创建数据库的方法
? 使用向导创建数据库
? 使用企业管理器创建数据库
? 使用 Transact-SQL语言创建数据库。
35
使用向导创建数据库
? 以下的图 1 到图 8是使用向导创建数据库,
用户根据提示操作，即可创建数据库。
36
图
1
选
择
向
导
对
话
框
37
图 2 欢迎使用创建数据库向导对话框
38
图 3 输入数据库名称和文件位置对话框
39
图 4 定义数据库文件逻辑名称及初始大小对话话框
40
图 5 定义数据库增长信息对话框
41
图 6 定义事务日志文件名称及初始大小对话框
42
图 7 定义事务日志文件增长信息对话框
43
图 8 确认创建数据库对话框
44
使用企业管理器创建数据库
?在企业管理器中, 单击工具栏中的 图标, 或在数据
库文件夹或其下属任一数据库图标上单击右键, 选择新建
数据库选项, 就会出现如图 9所示的对话框 。
?在常规（ General） 页框中，要求用户输入数据库名称。
?点击数据文件（ Data Files） 页框，该页框用来输入数据
库文件的逻辑名称、存储位置、初始容量大小和所属文件
组名称，如图 10所示。
?点击事务日志（ Transaction Log） 页框，该页框用来设置
事务日志文件信息，如图 11所示。
?单击图 11中的“确定”按钮，则开始创建新的数据库。
45
图 9 创建数据库对话框中的常规页框
46
图 10 创建数据库对话框中的数据文件页框
47
图 11 创建数据库对话框中的事务日志页框
48
使用 Transact-SQL语言创建数据库,
?语法如下,
?CREATE DATABASE database_name
[ON [PRIMARY] [<filespec> [1,…n]
[,<filegroupspec> [,…n]] ]
[LOG ON {<filespec> [1,…n]}]
[FOR LOAD|FOR ATTACH]
<filespec>::=([NAME=logical_file_name,]
FILENAME=‘os_file_name’
[,SIZE=size]
[,MAXSIZE={max_size|UNLIMITED}]
[,FILEGROWTH=growth_increment] ) [,…n]
<filegroupspec>::=FILEGROUP filegroup_name <filespec>
[1,…n]
49
? 各参数说明如下,
? database_name,数据库的名称，最长为 128个字符。
? ON,指明数据库文件和文件组的明确定义
? PRIMARY,指明主数据库文件或主文件组。主文件组的
第一个文件被认为是主数据库文件，其中包含了数据库
的逻辑启动信息和数据库的系统表。如果没有 PRIMARY,
项则在 CREATE DATABASE 命令中列出的第一个文件将被
默认为主文件。
? Filespec,文件说明
? Filegroupspec,文件组说明
? LOG ON,指明事务日志文件的明确定义。如果没有 LOG
ON 选项，则系统会自动产生一个文件名前缀与数据库
名相同，容量为所有数据库文件大小 1/4 的事务日志文
件。
50
? NAME,指定数据库的逻辑名称，这是在 SQL Server系统中使
用的名称，是数据库在 SQL Server中的标识符。
? FILENAME,指定数据库所在文件的操作系统文件名称和路径，
该操作系统文件名和 NAME的逻辑名称一一对应
? SIZE,指定数据库的初始容量大小。
? MAXSIZE,指定操作系统文件可以增长到的最大尺寸。
? FILEGROWTH,指定文件每次增加容量的大小，当指定数据为
0时，表示文件不增长。
? For load,提供与早期 SQL Server的兼容性，现在已不用该
语句。
? For Attach,指定从现有的操作系统文件中附加数据库，使
用该子句时必须指定该数据库的主文件。
51
例：创建了一个 Company数据库
? 创建了一个 Company数据库，该数据库的
主数据文件逻辑名称为 Company_data,物理
文件名为 Company.mdf,初始大小为 10MB，
最大尺寸为无限大，增长速度为 10%；数据库
的日志文件逻辑名称为 Company_log,物理文
件名为 Company.ldf,初始大小为 1MB,最大
尺寸为 5MB,增长速度为 1MB。
? 程序清单如下,
52
? Create database company on primary
? (name=company_data,
? filename='d:\mssql7\data\company.mdf',
? size=10,
? maxsize=unlimited,
? filegrowth=10%)
? log on
? (name=company_log,
? filename='d:\mssql7\data\company.ldf',
? size=1,
? maxsize=5,
? filegrowth=1)
53
例,创建一个指定多个数据文件和日
志文件的数据库
? 该数据库名称为 employees,有 1个 10MB和 1个
20MB的数据文件和 2个 10MB的事务日志文件。数据
文件逻辑名称为 employee1和 employee2,物理文件
名为 employee1.mdf和 employee2.mdf。 主文件是
employee1,由 primary指定，两个数据文件的最大
尺寸分别为无限大和 100MB,增长速度分别为 10%
和 1MB。 事务日志文件的逻辑名为 employeelog1和
employeelog2,物理文件名为 employeelog1.ldf和
employeelog2.ldf,最大尺寸均为 50MB,文件增长
速度为 1MB。
? 其源程序如下,
54
? create database employees on primary
? (name=employee1,
? filename=’d:\mssql7\data\ employee1.mdf’,
? size=10,maxsize=unlimited,
? filegrowth=10%),
? (name=employee2,
? filename=’d:\mssql7\data\ employee2.mdf’,
? size=20,maxsize=100,filegrowth=1)
? log on
? (name=employeelog1,
? filename=’d:\mssql7\data\ employeelog1.ldf’,
? size=10,maxsize=50,filegrowth=1),
? (name=employeelog2,
? filename=’d:\mssql7\data\ employeelog2.ldf’,
? size=10,maxsize=50,filegrowth=1)
55
设置数据库选项
? 设置数据库选项可以采用如下几种方法,
? 使用数据库属性对话框来设置数据库选项
? 在查询分析器中执行系统存储过程
sp_dboption查看数据库选项。
56
使用数据库属性对话框设置数
据库选项
? 方法如下,
? 打开企业管理器
? 选择需要查看的数据库
? 在工具栏中单击“属性”按钮，弹出
“数据库属性”对话框。
? 在数据库属性对话框中选择“选项”标
签，在此选项下，可以看到四个区域：
访问区、故障还原区、设置区和兼容性
区。
57
访问区
? 访问限制
Members of db_owner,dbcreator,or
sysadmin,只有数据库所有者, 数据库
创建者和系统管理员才有权使用数据库 。
Single user,数据库在同一时间只能供
一个用户使用 。
? Read only,数据库只读, 即可查看而不
可修改
58
故障还原区
? 该区用来设置数据库还原的模型，数据
库的还原模型将决定数据库的备份方式
和数据损失的风险。有三种还原模型可
供选择,
? 简单：将数据恢复到最近一次备份过的
状态
? 完全：将数据库恢复到失败点时的状态
? 大容量日志记录：允许大容量日志记录
的操作。
59
设置区
? ANSI Null 默认设置,允许在数据库表的列中输入
Null 值 。
? 递归触发器,允许触发器递归调用。 SQL Server 设
定的触发器递归调用的层数上限为 32
? 残缺页检测,自动检测有损坏的页。页是数据库内
容的基本存储单位, 每个页的大小为 8KB 。 由于
SQL Server 对页的读写单位是大小为 512 字节的扇
区, 当每个页的第一个扇区被成功写入后, SQL
Server 就认为此页已经被成功写入了 。 因此, 如果
写完第一个扇区后发生突发事件, 导致写入中断,
就会产生有损坏的页, 需要通过备份来恢复数据库
60
? 自动关闭,当数据库中无用户时，自动关闭此数据库，并
将所占用资源交还给操作系统。对那些不间断使用的数据
库, 不要使用此选项, 否则会额外增加开关数据库的运行
开销, 得不偿失 。
? 自动收缩：定期对数据库进行检查。当数据库文件或日志
文件的未用空间超过其大小的 25%时，系统将会自动缩减
文件，使其未用空间等于 25%； 当文件大小没有超过其建
立时的初始大小时，不会缩减文件。缩减后的文件也必须
大于或等于其初始大小。对事务日志文件的缩减只有在对
其作备份时，或将 Truncate log on checkpoint 选项设为
True 时才能进行 。
? 自动创建统计信息,启动数据库列的使用统计，以提供给
查询优化器 Query Optimizer 使用
? 自动更新统计信息,当数据库表更新时，更新所有的统计
? 使用被引用的标识符,标识符必须用双引号扩起来且可以
不遵循 Transact-SQL 命名标准
61
兼容性区
? Compatibility Level,数据库兼容性级别可以
指定与何种版本以前的数据库兼容
62
利用 sp_dboption
? 使用系统存储过程 sp_dboption查看数据库选
项的步骤如下,
? 1打开查询分析器
? 2选择需要查看或修改选项的数据库，在查
询窗口输入 exec sp_dboption。 则可以在结
果窗口中，看到一个该数据库中所有可设置
选项的列表。
63
? 使用系统存储过程 sp_dboption设置数据库选项。其语法如
下,
sp_dboption [[@dbname=]’database’]
[,@optname=]’option_name’]
[,[@optvalue=]’value’]]
? 参数如下,
– @dbname,指定对其进行选项设置的数据库名
– Optname,指定要设置的选项。如果选项名包括嵌入的空
格或者是一个关键字，则此选项名必须用引号括起来。
若忽略此参数,sp_dboption将列出处于打开状态的所有
选项。
– @optvalue,该参数将确定选项的新设置。若省略此参数，
则返回当前设置。这个值可以是 true,false,on或 off。
– 返回代码为 0，说明选项设置成功；返回代码为 1，说明
选项设置失败。
64
? 例 1：以下语句返回打开状态的数据库选
项
? Sp_dboption ‘员工数据库’
? 例 2：下列语句将‘员工数据库’的数据
库选项‘ auto close’设置为 true
? Sp_dboption ‘员工数据库’,‘ auto
close’,’true’
? 例 3：再次利用例 1的语句，观察结果多了
一项。
65
? SQL Server的数据库选项很多，使用管理
工具可以设置的数据库选项却很有限。但
是，可以使用系统存储过程 sp_dboption设
置所有的 SQL Server数据库选项。
? 数据库选项共有五类：自动选项、游标选
项、恢复选项,SQL选项和状态选项。各
选项的具体说明见课本 p163~165
66
修改数据库
? 1,利用企业管理器修改数据库
? 2,使用 ALTER DATABASE语句修改数
据库
67
用企业管理器缩小数据库文件
? 在数据库属性对话框中，虽然可以增加
数据库文件的大小，却不能缩小数据库
文件。缩小数据库文件的步骤如下,
? 1 启动企业管理器
? 2选择要缩小的数据库
? 3选择菜单“操作－所有任务－收缩数据
库”，弹出一个对话框，然后选择执行
数据库收缩的操作方式。
68
? 收缩后文件中的最大可用空间：设置
收缩数据库以后, 文件中还可以利用的
空间的百分比
? 在收缩前将页移到起始位置：指定在
收缩数据库前将页移到文件的起始位置
? 收缩文件：若需要进行更精确的设置,
可对数据库文件单独进行收缩, 还可以
根据需要设置收缩文件的时间 。
69
? 根据本调度来收缩数据库：如果选中这个复选框,
可以单击, 更改, 按钮, 弹出, 编辑调度, 对话框,
在对调度进行编辑 。
? 启动 SQL Server代理时自动启动：指定 SQL Server
代理启动时自动启动作业 。
? 每当 cpu闲置时就启动：指定只要 cpu闲置就启动作
业 。 可以在 SQL Server代理属性对话框中的, 高级,
选项卡上指定 cpu闲置时间 。
? 一次：可以选中该选项, 并指定启动作业的日期和
时间
? 反复出现：将使系统按照指定的时间周期启动完成 。
70
用企业管理器修改数据库结构
? 数据库结构的更改宝库添加、删除和修改文件
或文件组，更改数据文件和事务日志文件的容
量，增长方式和存储路径等。
? 利用企业管理器修改数据库结构的步骤如下,
? 1打开需要修改结构的用户数据库的数据库属性
对话框
? 2在属性对话框中对相关内容，如数据文件和事
务日志文件的属性进行修改。
? 注意：在数据库属性对话框内，只能增加数据
文件或日志文件的大小，而不能减小。
71
使用存储过程修改数据库名称
? 语法如下,
? Sp_renamedb @old_name,@new_name
? 其中,@old_name为原数据库名称；
? @new_name为新的数据库名称
? 注意：只有属于 sysadmin固定角色服务器的成
员才可以执行 sp_renamedb系统存储过程。在
数据库更改名称之前，必须将该数据库切换到
单用户模式下，更名之后再恢复为多用户模式。
72
例,
? Exec sp_dboption ‘产品数据库’,
‘ single user’,true
? Exec sp_renamedb ‘产品数据
库’,’ product’
? Exec sp_dboption ‘product’,
’single user’,false
73
增大数据库文件大小
? 例如,alter database 产品数据库
? Modify file(name=‘产品数据库 _log’，
size=3)
? 例如,alter database 产品数据库
? Modify file (name=‘产品数据库
_dat’,size=15)
74
压缩数据库
? SQL Server还提供一种可以压缩数据库大小的方法：, 数据
库一致性检查器 (DBCC)”命令。语法如下,
DBCC SHRINKFILE(filename
{ [,target_size]
|[,{EMPTYFILE|NOTRUNCATE | TRUNCATEONLY}]})
Filename,收缩文件的逻辑名称
Target_size,收缩后文件的大小。若没有指定，则将文件
收缩到默认大小。
Emptyfile,将所有数据从指定文件移至同一文件组中的其
它文件上
Notruncate,将释放的文件空间保留在数据库文件的范围
内，如果不设置这一选项，释放的空间将被系统收回。
Truncateonly,指定文件中的任何未使用的空间释放给操
作系统，若使用此选项,target_size值将被忽略。
? 例,dbcc shrinkfile (‘产品数据库 _dat’,6)
75
压缩数据库
? 另外，可以使用修改数据库选项的方法未数
据库设置自动收缩，语法为,
? Exec sp_dboption ‘数据库名 ’,‘auto
shrink’,true
76
使用 T-SQL语句修改数据库结构
?语法形式如下,
?Alter database databasename
?{add file<filespec>[,…n] [to filegroup filegroupname]
?|add log file <filespec>[,…n]
?|remove file logical_file_name
?|add filegroup filegroup_name
?|modify file <filespec>
?|modify filegroup filegroup_name
?}
<filespec>,:=
( NAME = logical_file_name
[,FILENAME = 'os_file_name' ]
[,SIZE = size]
[,MAXSIZE = { max_size | UNLIMITED } ]
[,FILEGROWTH = growth_increment] )
77
修改数据库
? 参数如下,
– DATABASE,正进行更改的数据库名
– ADD FILE, 指定正要添加一个文件
– TO FILEGROUP, 指定要添加到其中的文件组
– ADD LOG FILE, 指定要向数据库中添加一个日
志文件
– REMOVE FILE logical_file_name,删除某一操作
系统文件名, 注意,logical_file_name使该文件的
逻辑文件名, 而非磁盘文件名 。
– ADD FILEGROUP, 指定要添加的文件组
78
修改数据库
– REMOVE FILEGROUP, 从数据库中删除一
个文件组
– MODIFY FILE, 修改某操作系统文件的属性 。
– MODIFY FILEGROUP filegroup_name
filegroup_property, 修改某文件组的属性。文
件组的属性有三种,
– Readonly,指定文件组为只读，不允许更新其
中的对象。
– Readwrite,允许对文件组中的对象进行更新。
– Default,将文件组指定为默认数据库文件组，
但通常只能有一个数据库文件组是默认的。
– 注意：主文件组不能设置为只读。
79
? 例：添加一个包含两个数据文件的文件组和一个事务日志文件到
employees数据库中 。 程序清单如下,
? Alter database employees Add filegroup data1
? Alter database employees Add file
? (name=employee3,
? filename=’d:\mssql7\data\employee3.ndf’,
? size=1,maxsize=50,filegrowth=1),
? (name=employee4,filename=’d:\mssql7\data\employee4.ndf’,
? size=2,maxsize=50,filegrowth=10%)
? to filegroup data1
? Alter database employees add log file
? (name=employeelog3,
? filename=’d:\mssql7\data\employeelog3.ldf’,
? size=1,maxsize=50,filegrowth=1)
80
删除数据库
?1,利用企业管理器删除数据库：选中该数据库并单击
鼠标右键, 在弹出菜单中选择, 删除, 命令
?2,利用 Drop语句删除数据库,Drop语句可以从 SQL
Server中一次删除一个或多个数据库 。 其语法如下,
?Drop database database_name[1,…n]
?其中, database_name为数据库名 。
?注意：在使用 drop database 删除数据库之前, 必须
将数据库的 readonly选项设置为假, 否则不能删除 。
81
例：删除创建的数据库 company。
?程序清单,
?drop database company
?输出结果为,
?Deleting database file
'd:\mssql7\data\company.ldf',
?Deleting database file
'd:\mssql7\data\company.mdf',
第五章 管理事务和管理数据库
? 管理事务
? 管理数据库
2
事务的由来
? 使用 DELETE 命令或 UPDATE 命令对数据库进行更
新时, 一次只能操作一个表, 这会带来数据库的数据
不一致的问题 。 例如企业取消了后勤部, 需要将后勤
部从 department表中删除, 要修改 department 表,
而 employee 表中的部门编号与后勤部相对应的员工
也应删除 。 因此, 两个表都需要修改, 这种修改只能
通过两条 DELETE 语句进行 。 假设后勤部编号为
1012
? 第一条 DELETE 语句修改 department 表
? delete from department
? where dept_id = ’1012’
? 第二条 DELETE 语句修改 employee 表
? delete from employee where dept_id = ’1012’
3
? 在执行第一条 DELETE 语句后, 数据库中的数
据已处于不一致的状态 。 因为此时已经没有后
勤部了, 但 employee 表中仍然保存着属于后
勤部的员工记录 ； 只有执行了第二条 DELETE
语句后, 数据才重新处于一致状态 。 但是如果
执行完第一条语句后, 计算机突然出现故障,
无法再继续执行第二条 DELETE 语句, 则数据
库中的数据将处于永远不一致的状态 。 因此必
须保证这两条 DELETE 语句同时执行 。 为解决
类似的问题, 数据库系统通常都引入了事务
Transaction 的概念 。
4
事务的概念及特性
? 事务：指作为单个逻辑工作单元执行的
一系列操作，而这些逻辑工作单元需要
具有原子性，一致性，隔离性和持久性
四个属性，统称 ACID特性。
? 原子性：指事务必须是原子工作单元，
即对于事务所进行数据修改，要么全都
执行，要么全都不执行
5
? 一致性：指事务在完成时，必须使所有
的数据都保持一致性状态，而且在相关
数据库中，所有规则都必须应用于事务
的修改，以保持所有数据的完整性。事
务结束时，所有的内部数据结构都必须
是正确的。
6
? 隔离性：指由并发事务所做的修改必须与
任何其他并发事务所做的修改相隔离。事
务查看数据时数据所处的状态，要么是被
另一并发事务修改之前的状态，要么是被
另一事务修改之后的状态，即事务不会查
看正在由另一个并发事务正在修改的数据。
? 持久性：指事务完成之后，它对于系统的
影响是永久性的，即使出现系统故障也是
如此。
7
事务的类型
? 隐式事务,每次执行 SQL Server的任何
数据修改语句时，它都是一个隐式事务。
例如：下列 SQL语句是一个独立事务,
? Insert talbe values(1,’abc’)
? Update table set col1=5 where col1=1
? Delete from table1 where col1=5
? go
8
显式事务
? 通常在程序中用 BEGIN TRANSACTION 命令来标识一
个事务的开始, 用 COMMIT TRANSACTION 命令标识
事务结束 ； 这两个命令之间的所有语句被视为一体 。 只
有执行到 COMMIT TRANSACTION 命令时, 事务中对
数据库的更新操作才算确认 。 和 BEGIN…END 命令类
似 。 这两个命令也可以进行嵌套, 即事务可以嵌套执行 。
9
? 这两个命令的语法如下
? BEGIN TRAN[SACTION] [transaction_name |
@tran_name_variable]
? COMMIT [ TRAN[SACTION] [transaction_name |
@tran_name_variable] ]
? 其中 BEGIN TRANSACTION 可以缩写为 BEGIN TRAN 。
COMMIT TRANSACTION可以缩写为 COMMIT TRAN
或 COMMIT 。
? transaction_name,指定事务的名称, 只有前 32 个字
符会被系统识别 。
? @tran_name_variable,用变量来指定事务的名称变量 。
只能声明为 CHAR VARCHAR NCHAR 或 NVARCHAR
类型
10
? 例, 删除后勤部
? declare @transaction_name varchar(32)
? select @transaction_name = ' my_transaction_delete '
? begin transaction @transaction_name
? go
? use sample
? go
? delete from department where dept_id = ’1012’
? go
? delete from employee where dept_id = ’1012’
? go
? commit transaction my_transaction_delete
? go
11
事务回滚
? 事务回滚 (Transaction Rollback) 是指当事务中
的某一语句执行失败时,将对数据库的操作恢复到
事务执行前或某个指定位置 。
? 事务回滚使用 ROLLBACK TRANSACTION 命令,
其语法如下
? ROLLBACK [TRAN[SACTION]
[transaction_name | @tran_name_variable
? | savepoint_name | @savepoint_variable] ]
? 其中 savepoint_name 和 @savepoint_variable
参数用于指定回滚到某一指定位置 。
? 如果要让事务回滚到指定位置, 则需要在事务中
设定保存点 Save Point 。
12
? 所谓保存点是指定其所在位置之前的事务语句
不能回滚的语句, 即此语句前面的操作被视为
有效 。
? 其语法如下,
? SAVE TRAN[SACTION] {savepoint_name |
@savepoint_variable}
? 各参数说明如下,
? avepoint_name,指定保存点的名称 。 同事务
的名称一样, 只有前 32 个字符会被系统识别 。
? @savepoint_variable,用变量来指定保存点
的名称变量, 只能声明为 CHAR,VARCHAR,
NCHAR 或 NVARCHAR 类型 。
13
? 例, 删除后勤部再将后勤部的职工划归到经理室
? begin transaction my_transaction_delete
? use sample
? go
? delete from department where dept_id = ’1012’
? save transaction after_delete
? update employee set dept_id = ’1001’
where dept_id = ’1012’
? if @@error!=0 or @@rowcount=0 then
? begin
? rollback tran after_delete /* 回滚到保存点
after_delete
? 如果使用 rollback my_transaction_delete 则会回滚到事
务开始前 */
? print ‘更新员工信息表时产生错误 ’
? commit transaction my_transaction_delete
? go
14
事务工作机制
? 例如,begin tran
? Insert table1 values(1,’abc’)
? Update table1 set col1=5 where col1=1
? Delete from table1 where col1=5
? Commit tran
15
? 1,当 begin tran语句到达数据库时,sql server分
析出这是事务的开始,SQL server找到下一个可用
的内存日志页面，并给新事务分配一个事务 ID。
? 2,接着运行插入语句，新的行被记录到事务日志
中，数据页面在内存中进行修改，若所需页面不
在内存中，则从磁盘调出。
? 3,update语句以类似方式运行。
? 4、当 sql server 收到 commint tran时，日志页面被
写道数据库的日志设备上，这样才能保证日志页
面可被恢复。由于日志变化写入了硬盘，它保证
事务是可恢复的，即使掉电了或在数据页写入磁
盘时数据库崩溃了，也能进行事务恢复。
16
检查点处理
? 问题：系统何时将数据页写入磁盘呢？
? 日志页面是在 commit tran时写入磁盘的，
那么何时将数据页面写入磁盘呢？在处
理检查点时。
? 检查点的概念：是 sql server将数据页面
从内存拷贝到磁盘时的内部处理点。
? 检查点帮助确保恢复已提交事务而不会
花费额外的时间。当检查点出现时，将
写入一个日志项以指示所有修改了的页
面已写入磁盘。
17
锁 -锁的概念
? 锁 （ Lock) 是在多用户环境下对资源访问的一
种限制机制 。 当对一个数据源加锁后, 此数据
源就有了一定的访问限制, 我们就称对此数据
源进行了锁定 。 在 SQL Server中可以对以下
的对象进行锁定,
? 数据行, 数据页中的单行数据
? 索引行, 索引页中的单行数据即索引的键值
? 页, 页是 SQL Server 存取数据的基本单位其
大小为 8KB
? 盘区, 一个盘区由 8 个连续的页组成
? 表
? 数据库
18
锁的类别
? 在 SQL Server 中, 从数据库系统的角度
来看, 锁分为以下三种类型,
? 独占锁 Exclusive Lock,独占锁锁定的资
源只允许进行锁定操作的程序使用, 其它
任何对它的操作均不会被接受 。 执行数据
更新命令, 即 INSERT,UPDATE 或
DELETE 命令时 SQL Server 会自动使用
独占锁 。 但当对象上有其它锁存在时, 无
法对其加独占锁, 独占锁一直到事务结束
才能被释放 。
19
? 共享锁 Shared Lock,共享锁锁定的资源可以
被其它用户读取, 但其它用户不能修改它 。 在
SELECT 命令执行时, SQL Server 通常会对
对象进行共享锁锁定, 通常加共享锁的数据页
被读取完毕后, 共享锁就会立即被释放
? 更新锁 Update Lock,更新锁是为了防止死锁
而设立的 。 当 SQL Server 准备更新数据时,
它首先对数据对象作更新锁锁定, 这样数据将
不能被修改, 但可以读取 。 等到 SQL Server
确定要进行更新数据操作时, 它会自动将更新
锁换为独占锁 。 但当对象上有其它锁存在时,
无法对其作更新锁锁定 。
20
查看锁
? 可以通过企业管理器或存储过程来查看锁
? 用 Enterprise Manager 查看锁, 在企业管理器中
选择目录树窗口中,管理, 文件夹下,当前活动” 中的
锁 /进程 ID 节点, 则可以查看当前锁定的进程 。 选择同
级的 锁 /对象 节点下的相应字节点, 则可以查看当前锁定
的对象 。 若右键单击任务板窗口中的对象, 从快捷菜单
中选择属性选项, 则会出现锁的进程细节对话框, 在此
可以刷新或杀死锁的进程 。
? 杀死进程还可以用如下 Transact-SQL 命令来进行
? KILL spid
? spid 是 System Process ID,即系统进程编号的缩写 。
21
? 用系统存储过程 Sp_lock 查看锁
? 存储过程 Sp_lock 的语法如下
? sp_lock spid
? SQL Server 的进程编号 spid 可以在
master.dbo.sysprocesses 系统表中查到, spid 是 INT
类型的数据, 如果不指定 spid 则显示所有的锁 。
? 例, 显示当前系统中所有的锁
? use master
? exec sp_lock
? 例,use master
? exec sp_lock 52
22
死锁及其防止
? 死锁 是在多用户或多进程状况下, 为使用同一资源而产生的无法
解决的争用状态 。 通俗地讲, 就是两个用户各占用一个资源, 两
人都想使用对方的资源但同时又不愿放弃自己的资源, 就一直等
待对方放弃资源 。 如果不进行外部干涉就将一直耗下去 。 死锁会
造成资源的大量浪费, 甚至会使系统崩溃 。 在 SQL Server 中解
决死锁的原则是牺牲一个比两个都死强, 即挑出一个进程作为牺
牲者, 将其事务回滚并向执行此进程的程序发送编号为 1205 的
错误信息而防止死锁的途径就是不能让满足死锁条件的
? 情况发生为此用户需要遵循以下原则
? 尽量避免并发地执行涉及到修改数据的语句 。
? 要求每个事务一次就将所有要使用的数据全部加锁, 否则就不予
执行 。
? 预先规定一个封锁顺序, 所有的事务都必须按这个顺序对数据执
行封锁 。 例如不同的过程在事务内部对对象的更新执行顺序应尽
量保持一致 。
? 每个事务的执行时间不可太长, 对程序段长的事务可考虑将其分
割为几个事务 。
23
数据库管理
? 数据库的存储结构
? 创建、修改和删除数据库
? 数据库备份
24
数据库的存储结构
? 数据库文件
? 数据库文件组
25
数据库文件
? 逻辑存储结构和物理存储结构
? 一、数据库的逻辑存储结构指的是数据库
是由哪些性质的信息所组成,SQL Server的数
据库不仅仅只是数据的存储，所有与数据处理
操作相关的信息都存储在数据库中。
? 二、数据库的物理存储结构则是讨论数据
库文件是如何在磁盘上存储的，数据库在磁盘
上是以文件为单位存储的，由数据库文件和事
务日志文件组成，一个数据库至少应该包含一
个数据库文件和一个事务日志文件。
26
数据库文件
? 在 SQL Server 中，数据库是由数据库文
件和事务日志文件组成的。一个数据库
至少应包含一个数据库文件和一个事物
日志文件。
27
数据库文件
? 数据库文件是存放数据库数据和数据库对象的文件 。 一
个数据库可以有一个或多个数据库文件, 一个数据库文
件只属于一个数据库 。 当有多个数据库文件时, 有一个
文件被定义为主数据库文件, 扩展名为,mdf,它用来
存储数据库的启动信息和部分或全部数据 。 一个数据库
只能有一个主数据库文件, 其它数据库文件被称为次数
据库文件, 扩展名为,ndf,用来存储主文件没存储的
其它数据 。
? 采用多个数据库文件来存储数据的优点体现在,
? 数据库文件可以不断扩充而不受操作系统文件大小的限
制
? 可以将数据库文件存储在不同的硬盘中, 这样可以同时
对几个硬盘做数据存取
? 提高了数据处理的效率 。
28
数据库文件注意事项
? 数据是存储在邻接磁盘空间的 8KB 块中
的, 这些块称为 页
? 行不能跨页
? 表、其他数据库对象和索引存储在扩展
盘区中
? 事务日志文件包含恢复数据库所必需的
所有信息，以防系统故障
29
事务日志文件
? 事务日志文件是用来记录数据库更新情况的文件, 扩展名
为,ldf。 例如使用 INSERT,UPDATE,DELETE 等对数据库
进行更改的操作, 都会记录在此文件中 。 而如 SELECT 等
对数据库内容不会有影响的操作, 则不会记录在案 。 一个
数据库可以有一个或多个事务日志文件 。
? SQL Server 中采用 （ Write-Ahead） 提前写方式的事务,
即对数据库的修改先写入事务日志中, 再写入数据库 。 其
具体操作是系统先将更改操作写入事务日志中, 再更改存
储在计算机缓存中的数据 。 为了提高执行效率, 此更改不
会立即写到硬盘中的数据库, 而是由系统以固定的时间间
隔执行 CHECKPOINT 命令, 将更改过的数据批量写入硬盘 。
SQL Server 有个特点, 它在执行数据更改时, 会设置一
个开始点和一个结束点 。 如果尚未到达结束点, 就因某种
原因使操作中断, 则在 SQL Server 重新启动时会自动恢
复已修改的数据, 使其返回未被修改的状态
30
数据库文件组
? 为了便于分配和管理,SQL Server允许将
多个文件归纳为同一组，并赋予此组一个
名称，这就是文件组。 系统管理员可以为
每个磁盘驱动器创建文件组，然后将特定
的表、索引或表中的 text,ntext,image数
据指派给特定的文件组。
? 文件不可以在文件组之间共享。表、索引
或表中的 text,ntext,image数据可以与文
件组相关联，这时，它们的所有页都将分
配到与其关联的文件组中。日志文件不能
作为文件组的一部分，日志空间与数据空
间将分开进行管理。
31
数据库文件组
? 与数据库文件一样，文件组也分为主文件组和次
文件组 。
? 一个文件只能存在于一个文件组中，一个文件组
也只能被一个数据库使用。主文件组中包含了所
有的系统表。当建立数据库时，主文件组包括主
数据库文件和未指定给其它组的其它文件，系统
表的所有页均分配在主文件组中。在次文件组中
可以指定一个缺省文件组，那么在创建数据库对
象时，如果没有指定将其放在哪一个文件组中，
就会将它放在缺省文件组中。如果没有指定缺省
文件组，则主文件组为缺省文件组。
32
文件组
? 文件组是一个或多个文件的集合，构成分配和
管理的单元
? 可以在一开始创建数据库时创建，也可以在以
后多个文件添加到数据库中时再创建
? 文件组只能包含数据文件
? 一个文件不能属于多个文件组
? 表、索引,text,ntext 和 image 数据可以与一
个特定的文件组相关
33
创建用户数据库
? 每个数据库都由以下几个部分的数据库
对象所组成,
? 关系图、表、视图、存储过程、用户、
角色、规则、默认、用户自定义数据类
型和用户自定义函数。
34
创建数据库的方法
? 使用向导创建数据库
? 使用企业管理器创建数据库
? 使用 Transact-SQL语言创建数据库。
35
使用向导创建数据库
? 以下的图 1 到图 8是使用向导创建数据库,
用户根据提示操作，即可创建数据库。
36
图
1
选
择
向
导
对
话
框
37
图 2 欢迎使用创建数据库向导对话框
38
图 3 输入数据库名称和文件位置对话框
39
图 4 定义数据库文件逻辑名称及初始大小对话话框
40
图 5 定义数据库增长信息对话框
41
图 6 定义事务日志文件名称及初始大小对话框
42
图 7 定义事务日志文件增长信息对话框
43
图 8 确认创建数据库对话框
44
使用企业管理器创建数据库
?在企业管理器中, 单击工具栏中的 图标, 或在数据
库文件夹或其下属任一数据库图标上单击右键, 选择新建
数据库选项, 就会出现如图 9所示的对话框 。
?在常规（ General） 页框中，要求用户输入数据库名称。
?点击数据文件（ Data Files） 页框，该页框用来输入数据
库文件的逻辑名称、存储位置、初始容量大小和所属文件
组名称，如图 10所示。
?点击事务日志（ Transaction Log） 页框，该页框用来设置
事务日志文件信息，如图 11所示。
?单击图 11中的“确定”按钮，则开始创建新的数据库。
45
图 9 创建数据库对话框中的常规页框
46
图 10 创建数据库对话框中的数据文件页框
47
图 11 创建数据库对话框中的事务日志页框
48
使用 Transact-SQL语言创建数据库,
?语法如下,
?CREATE DATABASE database_name
[ON [PRIMARY] [<filespec> [1,…n]
[,<filegroupspec> [,…n]] ]
[LOG ON {<filespec> [1,…n]}]
[FOR LOAD|FOR ATTACH]
<filespec>::=([NAME=logical_file_name,]
FILENAME=‘os_file_name’
[,SIZE=size]
[,MAXSIZE={max_size|UNLIMITED}]
[,FILEGROWTH=growth_increment] ) [,…n]
<filegroupspec>::=FILEGROUP filegroup_name <filespec>
[1,…n]
49
? 各参数说明如下,
? database_name,数据库的名称，最长为 128个字符。
? ON,指明数据库文件和文件组的明确定义
? PRIMARY,指明主数据库文件或主文件组。主文件组的
第一个文件被认为是主数据库文件，其中包含了数据库
的逻辑启动信息和数据库的系统表。如果没有 PRIMARY,
项则在 CREATE DATABASE 命令中列出的第一个文件将被
默认为主文件。
? Filespec,文件说明
? Filegroupspec,文件组说明
? LOG ON,指明事务日志文件的明确定义。如果没有 LOG
ON 选项，则系统会自动产生一个文件名前缀与数据库
名相同，容量为所有数据库文件大小 1/4 的事务日志文
件。
50
? NAME,指定数据库的逻辑名称，这是在 SQL Server系统中使
用的名称，是数据库在 SQL Server中的标识符。
? FILENAME,指定数据库所在文件的操作系统文件名称和路径，
该操作系统文件名和 NAME的逻辑名称一一对应
? SIZE,指定数据库的初始容量大小。
? MAXSIZE,指定操作系统文件可以增长到的最大尺寸。
? FILEGROWTH,指定文件每次增加容量的大小，当指定数据为
0时，表示文件不增长。
? For load,提供与早期 SQL Server的兼容性，现在已不用该
语句。
? For Attach,指定从现有的操作系统文件中附加数据库，使
用该子句时必须指定该数据库的主文件。
51
例：创建了一个 Company数据库
? 创建了一个 Company数据库，该数据库的
主数据文件逻辑名称为 Company_data,物理
文件名为 Company.mdf,初始大小为 10MB，
最大尺寸为无限大，增长速度为 10%；数据库
的日志文件逻辑名称为 Company_log,物理文
件名为 Company.ldf,初始大小为 1MB,最大
尺寸为 5MB,增长速度为 1MB。
? 程序清单如下,
52
? Create database company on primary
? (name=company_data,
? filename='d:\mssql7\data\company.mdf',
? size=10,
? maxsize=unlimited,
? filegrowth=10%)
? log on
? (name=company_log,
? filename='d:\mssql7\data\company.ldf',
? size=1,
? maxsize=5,
? filegrowth=1)
53
例,创建一个指定多个数据文件和日
志文件的数据库
? 该数据库名称为 employees,有 1个 10MB和 1个
20MB的数据文件和 2个 10MB的事务日志文件。数据
文件逻辑名称为 employee1和 employee2,物理文件
名为 employee1.mdf和 employee2.mdf。 主文件是
employee1,由 primary指定，两个数据文件的最大
尺寸分别为无限大和 100MB,增长速度分别为 10%
和 1MB。 事务日志文件的逻辑名为 employeelog1和
employeelog2,物理文件名为 employeelog1.ldf和
employeelog2.ldf,最大尺寸均为 50MB,文件增长
速度为 1MB。
? 其源程序如下,
54
? create database employees on primary
? (name=employee1,
? filename=’d:\mssql7\data\ employee1.mdf’,
? size=10,maxsize=unlimited,
? filegrowth=10%),
? (name=employee2,
? filename=’d:\mssql7\data\ employee2.mdf’,
? size=20,maxsize=100,filegrowth=1)
? log on
? (name=employeelog1,
? filename=’d:\mssql7\data\ employeelog1.ldf’,
? size=10,maxsize=50,filegrowth=1),
? (name=employeelog2,
? filename=’d:\mssql7\data\ employeelog2.ldf’,
? size=10,maxsize=50,filegrowth=1)
55
设置数据库选项
? 设置数据库选项可以采用如下几种方法,
? 使用数据库属性对话框来设置数据库选项
? 在查询分析器中执行系统存储过程
sp_dboption查看数据库选项。
56
使用数据库属性对话框设置数
据库选项
? 方法如下,
? 打开企业管理器
? 选择需要查看的数据库
? 在工具栏中单击“属性”按钮，弹出
“数据库属性”对话框。
? 在数据库属性对话框中选择“选项”标
签，在此选项下，可以看到四个区域：
访问区、故障还原区、设置区和兼容性
区。
57
访问区
? 访问限制
Members of db_owner,dbcreator,or
sysadmin,只有数据库所有者, 数据库
创建者和系统管理员才有权使用数据库 。
Single user,数据库在同一时间只能供
一个用户使用 。
? Read only,数据库只读, 即可查看而不
可修改
58
故障还原区
? 该区用来设置数据库还原的模型，数据
库的还原模型将决定数据库的备份方式
和数据损失的风险。有三种还原模型可
供选择,
? 简单：将数据恢复到最近一次备份过的
状态
? 完全：将数据库恢复到失败点时的状态
? 大容量日志记录：允许大容量日志记录
的操作。
59
设置区
? ANSI Null 默认设置,允许在数据库表的列中输入
Null 值 。
? 递归触发器,允许触发器递归调用。 SQL Server 设
定的触发器递归调用的层数上限为 32
? 残缺页检测,自动检测有损坏的页。页是数据库内
容的基本存储单位, 每个页的大小为 8KB 。 由于
SQL Server 对页的读写单位是大小为 512 字节的扇
区, 当每个页的第一个扇区被成功写入后, SQL
Server 就认为此页已经被成功写入了 。 因此, 如果
写完第一个扇区后发生突发事件, 导致写入中断,
就会产生有损坏的页, 需要通过备份来恢复数据库
60
? 自动关闭,当数据库中无用户时，自动关闭此数据库，并
将所占用资源交还给操作系统。对那些不间断使用的数据
库, 不要使用此选项, 否则会额外增加开关数据库的运行
开销, 得不偿失 。
? 自动收缩：定期对数据库进行检查。当数据库文件或日志
文件的未用空间超过其大小的 25%时，系统将会自动缩减
文件，使其未用空间等于 25%； 当文件大小没有超过其建
立时的初始大小时，不会缩减文件。缩减后的文件也必须
大于或等于其初始大小。对事务日志文件的缩减只有在对
其作备份时，或将 Truncate log on checkpoint 选项设为
True 时才能进行 。
? 自动创建统计信息,启动数据库列的使用统计，以提供给
查询优化器 Query Optimizer 使用
? 自动更新统计信息,当数据库表更新时，更新所有的统计
? 使用被引用的标识符,标识符必须用双引号扩起来且可以
不遵循 Transact-SQL 命名标准
61
兼容性区
? Compatibility Level,数据库兼容性级别可以
指定与何种版本以前的数据库兼容
62
利用 sp_dboption
? 使用系统存储过程 sp_dboption查看数据库选
项的步骤如下,
? 1打开查询分析器
? 2选择需要查看或修改选项的数据库，在查
询窗口输入 exec sp_dboption。 则可以在结
果窗口中，看到一个该数据库中所有可设置
选项的列表。
63
? 使用系统存储过程 sp_dboption设置数据库选项。其语法如
下,
sp_dboption [[@dbname=]’database’]
[,@optname=]’option_name’]
[,[@optvalue=]’value’]]
? 参数如下,
– @dbname,指定对其进行选项设置的数据库名
– Optname,指定要设置的选项。如果选项名包括嵌入的空
格或者是一个关键字，则此选项名必须用引号括起来。
若忽略此参数,sp_dboption将列出处于打开状态的所有
选项。
– @optvalue,该参数将确定选项的新设置。若省略此参数，
则返回当前设置。这个值可以是 true,false,on或 off。
– 返回代码为 0，说明选项设置成功；返回代码为 1，说明
选项设置失败。
64
? 例 1：以下语句返回打开状态的数据库选
项
? Sp_dboption ‘员工数据库’
? 例 2：下列语句将‘员工数据库’的数据
库选项‘ auto close’设置为 true
? Sp_dboption ‘员工数据库’,‘ auto
close’,’true’
? 例 3：再次利用例 1的语句，观察结果多了
一项。
65
? SQL Server的数据库选项很多，使用管理
工具可以设置的数据库选项却很有限。但
是，可以使用系统存储过程 sp_dboption设
置所有的 SQL Server数据库选项。
? 数据库选项共有五类：自动选项、游标选
项、恢复选项,SQL选项和状态选项。各
选项的具体说明见课本 p163~165
66
修改数据库
? 1,利用企业管理器修改数据库
? 2,使用 ALTER DATABASE语句修改数
据库
67
用企业管理器缩小数据库文件
? 在数据库属性对话框中，虽然可以增加
数据库文件的大小，却不能缩小数据库
文件。缩小数据库文件的步骤如下,
? 1 启动企业管理器
? 2选择要缩小的数据库
? 3选择菜单“操作－所有任务－收缩数据
库”，弹出一个对话框，然后选择执行
数据库收缩的操作方式。
68
? 收缩后文件中的最大可用空间：设置
收缩数据库以后, 文件中还可以利用的
空间的百分比
? 在收缩前将页移到起始位置：指定在
收缩数据库前将页移到文件的起始位置
? 收缩文件：若需要进行更精确的设置,
可对数据库文件单独进行收缩, 还可以
根据需要设置收缩文件的时间 。
69
? 根据本调度来收缩数据库：如果选中这个复选框,
可以单击, 更改, 按钮, 弹出, 编辑调度, 对话框,
在对调度进行编辑 。
? 启动 SQL Server代理时自动启动：指定 SQL Server
代理启动时自动启动作业 。
? 每当 cpu闲置时就启动：指定只要 cpu闲置就启动作
业 。 可以在 SQL Server代理属性对话框中的, 高级,
选项卡上指定 cpu闲置时间 。
? 一次：可以选中该选项, 并指定启动作业的日期和
时间
? 反复出现：将使系统按照指定的时间周期启动完成 。
70
用企业管理器修改数据库结构
? 数据库结构的更改宝库添加、删除和修改文件
或文件组，更改数据文件和事务日志文件的容
量，增长方式和存储路径等。
? 利用企业管理器修改数据库结构的步骤如下,
? 1打开需要修改结构的用户数据库的数据库属性
对话框
? 2在属性对话框中对相关内容，如数据文件和事
务日志文件的属性进行修改。
? 注意：在数据库属性对话框内，只能增加数据
文件或日志文件的大小，而不能减小。
71
使用存储过程修改数据库名称
? 语法如下,
? Sp_renamedb @old_name,@new_name
? 其中,@old_name为原数据库名称；
? @new_name为新的数据库名称
? 注意：只有属于 sysadmin固定角色服务器的成
员才可以执行 sp_renamedb系统存储过程。在
数据库更改名称之前，必须将该数据库切换到
单用户模式下，更名之后再恢复为多用户模式。
72
例,
? Exec sp_dboption ‘产品数据库’,
‘ single user’,true
? Exec sp_renamedb ‘产品数据
库’,’ product’
? Exec sp_dboption ‘product’,
’single user’,false
73
增大数据库文件大小
? 例如,alter database 产品数据库
? Modify file(name=‘产品数据库 _log’，
size=3)
? 例如,alter database 产品数据库
? Modify file (name=‘产品数据库
_dat’,size=15)
74
压缩数据库
? SQL Server还提供一种可以压缩数据库大小的方法：, 数据
库一致性检查器 (DBCC)”命令。语法如下,
DBCC SHRINKFILE(filename
{ [,target_size]
|[,{EMPTYFILE|NOTRUNCATE | TRUNCATEONLY}]})
Filename,收缩文件的逻辑名称
Target_size,收缩后文件的大小。若没有指定，则将文件
收缩到默认大小。
Emptyfile,将所有数据从指定文件移至同一文件组中的其
它文件上
Notruncate,将释放的文件空间保留在数据库文件的范围
内，如果不设置这一选项，释放的空间将被系统收回。
Truncateonly,指定文件中的任何未使用的空间释放给操
作系统，若使用此选项,target_size值将被忽略。
? 例,dbcc shrinkfile (‘产品数据库 _dat’,6)
75
压缩数据库
? 另外，可以使用修改数据库选项的方法未数
据库设置自动收缩，语法为,
? Exec sp_dboption ‘数据库名 ’,‘auto
shrink’,true
76
使用 T-SQL语句修改数据库结构
?语法形式如下,
?Alter database databasename
?{add file<filespec>[,…n] [to filegroup filegroupname]
?|add log file <filespec>[,…n]
?|remove file logical_file_name
?|add filegroup filegroup_name
?|modify file <filespec>
?|modify filegroup filegroup_name
?}
<filespec>,:=
( NAME = logical_file_name
[,FILENAME = 'os_file_name' ]
[,SIZE = size]
[,MAXSIZE = { max_size | UNLIMITED } ]
[,FILEGROWTH = growth_increment] )
77
修改数据库
? 参数如下,
– DATABASE,正进行更改的数据库名
– ADD FILE, 指定正要添加一个文件
– TO FILEGROUP, 指定要添加到其中的文件组
– ADD LOG FILE, 指定要向数据库中添加一个日
志文件
– REMOVE FILE logical_file_name,删除某一操作
系统文件名, 注意,logical_file_name使该文件的
逻辑文件名, 而非磁盘文件名 。
– ADD FILEGROUP, 指定要添加的文件组
78
修改数据库
– REMOVE FILEGROUP, 从数据库中删除一
个文件组
– MODIFY FILE, 修改某操作系统文件的属性 。
– MODIFY FILEGROUP filegroup_name
filegroup_property, 修改某文件组的属性。文
件组的属性有三种,
– Readonly,指定文件组为只读，不允许更新其
中的对象。
– Readwrite,允许对文件组中的对象进行更新。
– Default,将文件组指定为默认数据库文件组，
但通常只能有一个数据库文件组是默认的。
– 注意：主文件组不能设置为只读。
79
? 例：添加一个包含两个数据文件的文件组和一个事务日志文件到
employees数据库中 。 程序清单如下,
? Alter database employees Add filegroup data1
? Alter database employees Add file
? (name=employee3,
? filename=’d:\mssql7\data\employee3.ndf’,
? size=1,maxsize=50,filegrowth=1),
? (name=employee4,filename=’d:\mssql7\data\employee4.ndf’,
? size=2,maxsize=50,filegrowth=10%)
? to filegroup data1
? Alter database employees add log file
? (name=employeelog3,
? filename=’d:\mssql7\data\employeelog3.ldf’,
? size=1,maxsize=50,filegrowth=1)
80
删除数据库
?1,利用企业管理器删除数据库：选中该数据库并单击
鼠标右键, 在弹出菜单中选择, 删除, 命令
?2,利用 Drop语句删除数据库,Drop语句可以从 SQL
Server中一次删除一个或多个数据库 。 其语法如下,
?Drop database database_name[1,…n]
?其中, database_name为数据库名 。
?注意：在使用 drop database 删除数据库之前, 必须
将数据库的 readonly选项设置为假, 否则不能删除 。
81
例：删除创建的数据库 company。
?程序清单,
?drop database company
?输出结果为,
?Deleting database file
'd:\mssql7\data\company.ldf',
?Deleting database file
'd:\mssql7\data\company.mdf',
