大三上数据库系统管理复习

大三上数据库系统管理复习

第1章 数据库基础和数据库设计

数据库是长期存储在计算机内的、有组织的、可共享的数据集合。数据库中的数据按一定的数据模型组织、描述和存储，用于满足各种不同的信息需求，并且集中的数据彼此之间有相互的联系。具有较小的冗余度，较高的数据独立性和易扩展性。

数据库管理系统（DBMS）是（ 一组软件 ）。
DBS包含DB和DBMS
数据库系统包含数据库和数据库管理系统

数据库管理系统功能

• 数据定义
• 数据操纵
• 数据库的运行管理
• 数据库的建立和维护

外模式/概念模式映象：逻辑独立性

概念模式/内模式映象：物理独立性

关系数据库设计

• 需求分析 数据流图，数据字典

• 概念结构设计 ER模型

• 逻辑结构设计 ER模型转换为关系模型

• 数据库表的优化与规范化 规范到第三范式

  关系数据库的完整性

• 实体完整性约束（primary) 主键不能为空

• 唯一性约束（unique) 不能重复

• 参照完整性约束（foreign）外关键字来源于被参照关系表的取值或空值

• 检查（check) 和缺省值（default）约束

第3章 SQL Server数据库结构和管理

• 主数据库文件 （Primary Database File）.mdf 一个
• 次数据库文件（Secondary Database File）.ndf 0个或多个
• 事务日志文件 .ldf 至少一个

数据文件和文件组

• 数据文件和文件组只能属于一个数据库，数据文件属于一个文件组
• 日志文件独立。数据库内数据和日志不能存入相同的文件或文件组

在缓冲区中被修改后而没有写入磁盘的页面称作“脏页”

创建数据库

数据文件逻辑名称：教学管理_data；日志文件逻辑名称：教学管理_log。
数据文件存储：D:\server\MSSQL\Data\教学管理_data.mdf，初始大小3MB，最大空间20MB，增加量2MB。
日志文件存储：D:\server\MSSQL\Data\教学管理_data.ldf，初始大小2	MB，最大空间20MB，增加量2MB。
create database
on primary
(
    name='教学管理_data',
    filename='D:\server\MSSQL\Data\教学管理_data.mdf',
    size=3,
    maxsize=20,
    filegrowth=2
),
log on
(
    name='教学管理_log',
    filename='D:\server\MSSQL\Data\教学管理_data.ldf',
    size=2,
    maxsize=20,
    filegrowth=2
)
该数据库名称为“教学练习”，其中数据文件“教学练习_data”分配20MB，属于文件组PRIMARY，“教学练习_data1”和“教学练习_data2”各分配10MB，属于文件组stugroup。有1个10MB的事务日志文件。
create database 教学练习
on primary
(
    name='教学练习_data',
    filename='d:\server\mssql\data\教学练习_data.mdf', 
    size=20,
    maxsize=150,
    filegrowth=10%
),
filegroup stugroup
(
    name=教学练习_data1,
    filename='d:\server\mssql\data\教学练习_data1.ndf',
    size=10,
    maxsize=100,
    filegrowth=1
),
(
    name=教学练习_data2,
    filename='d:\server\mssql\data\教学练习_data2.ndf',
    size=10,
    maxsize=100
    filegrowth=1
)
log on
(
    name=教学练习_log,
    filename='d:\server\mssql\data\教学练习_log.ldf',
	size=10,
    maxsize=50,
    filegrowth=1
)

修改数据库

alter database 数据库名
add file
add log file
remove file
add filegroup
remove filegroup
modify file
modify name=数据库名
modify filegroup 文件组名 name=新名

--增加
alter database 教学练习
add file(
	name='教学练习_add',
    Filename='d:\server\mssql\data\教学练习_add.ndf',
	size=5,
    filegrowth=0
)

alter database 教学练习
add log file
(
    name='教学练习_addlog',
    Filename='d:\server\mssql\data\教学练习_addlog.ldf',Size=1,Maxsize=10, 
    filegrowth=1
)

--删
alter database 教学练习
remove file 教学练习_data1

--改
修改教学练习数据库的程序清单：
alter database 教学练习
modify file(
	name='教学练习_data',
    maxsize=unlimited
)

--在BlueSkyDB中新增一个文件组UserGroupX
use master
go
alter database buleskydb
add filegroup usergroupx
go

--在BlueSkyDB中将文件组UserGroup1配置为默认文件组
use master
go
alter database blueskydb
modify filegroup usergroup1 default

--将刚刚增加的文件组UserGroupX重命名为UserGroup4
alter database blueskydb
modify filegroup usergroupx name=usergroup4

--因为文件组UserGroup4中不包含任何数据文件，将该文件组删除
use master
go 
alter database blueskydb
remove filegroup usergroup4

自动收缩数据库
alter database databasename
set auto_shrink on/off

收缩教学练习数据库文件，使使用空间为原来的40%。

DBCC SHRINKDATABASE(教学练习,40)
GO

dbcc shrinkdatabase(教学练习，40)

删除数据库

drop database database_name

第4章 表的存储原理及完整性创建管理

建表
约束
null/not null
constraint 约束名 primary key clustered/nonclustered
unique 唯一
foreign key(外键列名) references 参照表（参照列名）
小数类型 decimal
price decimal

修改表
alter table 表名
add 新列名 数据类型 约束
alter column 列名 新类型
drop column 列名
add primary key(列名，列名，。。)
add foreign key(列名) references 表名（列名）
add constraint ...
drop constraint 约束名

在数据库BlueSkyDB的Comments表中，rating列的取值有要求，只能是1-5的数字。

ALTER TABLE Comments
ADD CONSTRAINT CK_Comments_rating CHECK (rating>=1 and rating<=5)

在数据库BlueSkyDB的Comments表中，为rating列添加一个默认值约束，默认值为5。

ALTER TABLE Comments
ADD CONSTRAINT DF_Comments_rating DEFAULT 5 FOR rating

第5章 查询处理和表数据编辑

查询

select查询格式
select (distinct) ...
from 表名/视图名
where 条件
group by 列名
having 条件
order by 列名 asc/desc 

范围条件 between  and 
 where 累计学分 (not) between 150 and 159
 
 确定集合
 where 籍贯 (not) in ('','','')
 
 substring(s,p,c):取子串，返回字符串从第p个字符开始，长度为c的子串
 2019
 substring('2019',1,2) 是 ‘20’
 
 字符匹配
 %匹配任意长度的字符串（长度可以为0）
 _匹配任意一个字符
 [c1c2…cn] 	匹配字符c1, c2, …, cn中的一个。当c1, c2, …, cn		连续时可简化为[c1-cn] 

[^c1c2…cn]  	匹配除c1, c2, …, cn外的一个字符。当c1, c2, …, 		cn连续时可简化为[^c1-cn] 

查询学号长度不等于7，或者学号后6位含有非数字字符的学生学号和姓名。 
   SELECT 学号,姓名 FROM 学生表
 WHERE 学号 NOT LIKE 
       'S[0-9][0-9][0-9][0-9][0-9][0-9]'
       
查询学号最后一位既不是“1”和“3”，也不是“9”的学生学号和姓名。 
   SELECT 学号,姓名 FROM 学生表
 WHERE 学号 NOT LIKE '%[139]'

ESCAPE短语: 使模式串中的某个通配符恢复原来的含义。 
【例5-16】查询课程名以“DB_”开头的课程信息。 
   SELECT * FROM 课程表
 WHERE 课名 LIKE 'DB\_%' ESCAPE '\'

空值判断
is （not) null
查询没有成绩的学号和开课计划编号。
   SELECT 学号, 开课号  FROM 选课表
 WHERE 成绩 IS NULL 

统计

count((distinct) *)  统计元组个数
count((distinct) 列表达式) 统计列值的个数
sum,avg,max,min
① GROUP BY中的列名只能是FROM子句所列表的列名，不能是列的别名。例如下列查询是错误的：
      SELECT 开课号 AS 开课计划编号, COUNT(学号) 
FROM 选课表  GROUP BY 开课计划编号
② 使用GROUP BY子句后，SELECT子句的目标列表达式所涉及的列必须满足：要么在GROUP BY子句中，要么在某个统计函数中。

WHERE子句与HAVING短语的区别 
     (1)作用对象不同：WHERE作用表，HAVING作用于组。
     (2)条件构成不同：WHERE条件不能直接包含统计函数，而 HAVING条件所涉及的列必须要么在GROUP BY子句中，要么在某个统计函数中。
     
 种类：左外连接、右外连接、全外连接 
左外连接：将左边关系中的未用元组配上空值加到结果集中。操作符: *= 或 LEFT OUTER JOIN…ON。
右外连接：将右边关系中的未用元组配上空值加到结果集中。操作符: =*或 RIGHT OUTER JOIN…ON。
全外连接：将两边关系中的未用元组配上空值加到结果集中。操作符: FULL OUTER JOIN…ON。

检查结果集是否为空
查询选修了开课计划号为010101的学生姓名。
  SELECT 姓名  FROM 学生表 AS S
WHERE EXISTS ( SELECT * FROM 选课表 AS E
                 WHERE E.学号=S.学号 AND 开课号='010101' )
                 
TOP关键词写在SELECT子句的列表达式之前。
TOP n：表示返回最前面的n行。
TOP n PERCENT：用百分比表示返回的行数。

例 返回Books表中最贵的三本书的数据。
   SELECT TOP  3 *
   FROM Books
   order by unitprice desc
   GO 

例 返回Books表中前20%行的数据。
select top 20 percent *
from books

例 列出Customers表和Comments表中所有的姓名，保留重复的数据行。
(SELECT customerName FROM Customers)
UNION ALL
(SELECT userName FROM Comments)
GO 

例 列出Customers表和Comments表中都存在的姓名，去除重复的数据行。
(SELECT customerName FROM Customers)
intersect
(SELECT userName FROM Comments)
GO  

列出Customers表中有而Comments表中没有的姓名，去除重复的数据行。
(SELECT customerName FROM Customers)
EXCEPT 
(SELECT userName FROM Comments)
GO 

数据更新

插入数据
insert 
into 表名（列名） --into 也可以省略
values(),()

修改数据
update 表名
set 列名=表达式
（from 表名，表名
where 。。。）

删除
delete from 表名 --这个from也可以省略
（from 表名，表名
where 删除条件）

清空
truncate table 表名

第6章 索引的机理、规划和管理

聚集索引每个表只能有一个

每个表最多可以创建249个非聚集索引

创建索引

要实现记录的物理顺序与索引项次序一致，应选择的索引类型是（ 聚簇索引 ）。

create unique/clustered/nonclustered 唯一/聚集/非聚集
index 索引名
on 数据表名/视图（字段名 asc/desc,...)
with 
pad_index,--是否使用索引填充
filefactor=填充因子,
ignore_dup_key, --插入或更新记录时，忽略重复键值
drop_existing,--删除并重新创建
statistics_norecompute, --用于指定过期的索引统计不会自动重新计算。 

sort_in_tempdb--用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中
on 文件组名

为数据库“教学管理”中数据表关于选课表.学号降序，选课表.开课号升序建立组合唯一索引IX_学号_开课号，填充因子为90，在插入数据时，可以忽略重复的值。如果已经存在IX_学号_开课号索引，则先删除后重建。 

USE 教学管理
   GO
CREATE UNIQUE INDEX IX_学号_开课号 ON 选课表(学号  DESC,开课号 ASC)
  WITH(
  PAD_INDEX=ON,      --保持索引开放的空间
  FILLFACTOR=90,     --填充因子90
  IGNORE_DUP_KEY=ON, --忽略重复键值
   DROP_EXISTING=ON)   --如果存在IX_学号_开课号索引
                       --则删除
           
删除索引
drop index 表名.索引名[，…n] 

第7章 SQL Server 事务和并发控制

事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中数据的一致性和可恢复性。

事务的特性

• 原子性：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。
• 一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
• 隔离性：一事务的执行不能被其它事务干扰。
• 持续性（永久性）：指事务一旦提交，则其对数据库中数据的改变就应该是永久的。

事务的分类

• 显式事务：用户自定义的事务。

  启动事务：begin transaction

  事务结束：commit transaction

  遇到错误，回滚到事务执行前的状态：rollback transaction

• 隐式事务：当前事务提交或回滚后，自动开始下一个事务。

• 自动事务：自动提交，自动回滚

显示事务
delete from 学生表
where 学号=‘’
select 查询批次=0，*
from 学生表
go 
begin transaction
insert 学生表
values('','')
commit transaction
go

select 查询批次=1,* FROM 学生表         
GO

加入错误处理程序
delete from 学生表
where 学号=‘’
select 查询批次=0，*
from 学生表
go 
begin try
begin transaction
insert 学生表
values('','')
commit transaction
end try
begin catch
	rollback transaction
end catch
go
select 查询批次=1,* FROM 学生表         
GO

隐式事务模式
set implicit_transacions on --进入隐式事务模式
insert 学生表 values('','')
select 查询批次=5，* from 学生表
rollback
go
select 查询批次=1，* from 学生表
set implicit_transactions off --隐式事务模式结束
go


定义事务，使事务回滚到指定的保存点，分批执行，观察执行的过程。
use 数据库名
go
select 查询批次=0，* from 学生表
go
begin transaction
insert 表名 values('','')
save transaction save_p
insert 表名 values('','')
select 查询批次=1，* from 学生表
go
rollback transaction save_p
select 查询批次=2，* from 学生表
go
rollback transaction
select 查询批次=3，* from 学生表
go

在教学管理数据的学生表表中先删除一条记录，然后再插入一条记录，通过测试错误值确定提交还是回滚。
use 数据库名
go
declare @del_error int,@ins_error int
begin tran
delete 学生表 where 学号=‘’
select @del_error=@@error
-- @@ERROR以int类型返回最后执行的SQL语句的错误代码
-- 若为0，表示语句执行成功
insert into 学生表
values('','')
select @ins_error=@@error
if @del_error=0 and @ins_error=0
begin
	commin tran
end
else
begin
	if @del_error<>0
		print '错误发生在删除语句'
	if @ins_error <> 0
		print '错误发生在插入语句'
	rollback tran
end
go

分布式事务：事务跨服务器进行数据操作。

分布式事务的两阶段提交

• 准备阶段：分布式事务管理器接受到提交请求后，向所有参与该事务的服务器发出准备命令，服务器返回工作状态给事务管理器。
• 提交阶段：事务管理器向服务器发出提交命令。服务器提交，成功则报告成功，如果有一个失败，则所有服务器回滚，报告失败。

并发控制

• 共享锁（S锁）：用于只读数据操作，它允许多个并发事务对资源锁定进行读取，但禁止其他事务对锁定资源的修改操作。
• 排它锁（X锁）：它锁定的资源不能被其它并发事务再进行任何锁定，所以其它事务不能读取和修改。锁定的资源用于自己的数据修改。

一般更新模式是由一个事务组成，该事务先读取记录，要获取资源的共享锁，然后修改记录，此操作要求锁转换为排它锁。如果两个事务都获得了资源上的共享锁，然后试图同时更新数据，这样肯定有一个事务要将共享锁转化为排它锁，因为一个事务的排它锁与其它事务的共享锁不兼容，发生锁等待。另一个事务也会出现这个问题，由于两个事务都要转化为排它锁，并且都等待另一个事务释放共享锁，因此发生死锁。

• 1级封锁协议：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。在1级封锁协议中，如果是读数据，不需要加锁的，所以它不能保证可重复读和不读“脏”数据。
• 2级封锁协议：1级封锁协议+事务T在读取数据R前必须先加S锁，读完后即可释放S锁
  2级封锁协议可以防止丢失修改和读“脏”数据。
  在2级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读。
• 1级封锁协议 + 事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放
  3级封锁协议可防止丢失修改、读脏数据和不可重复读。
  容易造成比较多的死锁

事务隔离级别

• 未提交读：这是4种隔离级别中限制最低的级别，它仅能保证SQL Server不读取物理损坏的数据。在这种隔离级别下，不发出共享锁，也不接受排它锁，事务可以对数据执行未提交读或脏读；在事务结束前可以更改数据集内的数值，行也可以出现在数据集中或从数据集消失。
• 提交读：它要求在读取数据时控制共享锁以避免发生脏读，但数据可在事务结束前更改，这可能产生不能重复读或幻影读问题。
• 可重复读：锁定查询中使用的所有数据以防止其他用户更新，但是其他用户可以将新的幻影行插入到数据集中，新插入的幻影行将出现在当前事务的后续读取结果集中。可重复读能够避免产生脏读和非重复读问题，但仍可能导致幻影读问题。
• 可串行读：这是事务隔离的最高级别，它使事务之间完全隔离，所以将导致并发级别较低。在这种隔离级别下，SQL Server在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或向数据集内插入数据行，从而避免出现脏读、非重复读或幻影读等

设置事务隔离级别
set transaction isolation level{
read uncommitted/read committed/repeatable read/serializable
}

模拟实现脏读
在【…Query1.sql】查询窗体内输入以下代码：
use 教学管理
begin tran
update 学生表 set 专业=‘计算机’
where 学号=‘S060201’
waitfor delay '00:00:10'
select *
from 学生表
where 学号=‘S060201’
rollback transaction
在【…Query2.sql】查询窗体内输入以下代码：
use 教学管理
set transaction isolation level read uncommitted
begin transaction
select *
from 学生表
where 学号=‘S060201’
waitfor delay '00:00:10'
select *
from 学生表
where 学号=‘S060201’
commit transaction

设置事务的隔离级别(幻影读)
事务T1：
USE 教学管理
delete  学生表 where 学号='S060112'
set tran isolation level Repeatable Read --Serializable 
begin tran
select 姓名 from 学生表 where 姓名 like '王%' 
 WAITFOR DELAY '00:00:10'
select 姓名 from 学生表 where 姓名 like '王%' 
Commit
事务T2：
USE 教学管理
BEGIN TRANSACTION 
INSERT 学生表 VALUES('S060112','******19870818***','王东生','男','','上海','计算机','信息学院',2)
WAITFOR DELAY '00:00:10'
commit

第8章 T-SQL程序结构

批是一组SQL语句的集合，一个批以结束符GO而终结。批中的所有语句被一次提交给SQL Server，SQL Server将这些语句编译为一个执行单元在执行时全部执行。

使用批的基本规则：
（1）所有CREATE语句应单独构成一个批，不能在批中和其它SQL语句组合使用。
（2）使用ALTER TABLE语句修改表结构后，不能在同一个批中使用新定义的列。
（3）EXCUTE语句为批中第一个语句时，可以省略EXCUTE关键字，否则，必须使用EXCUTE关键字。
（4）批命令GO和SQL语句不能在同一行上。但在GO命令中可以包含注释。

变量
T-SQL使用两种变量，即全局变量和局部变量。
全局变量是SQL Server 系统内部使用的变量，其作用范围并不局限于某一程序，而是任何程序均可随时调用。
局部变量是用户自定义的变量，它仅在定义它的批处理内有效，常用来暂存从表中查询到的数据，或作为流程控制变量。SQL Server规定，局部变量必须以@开头，而且必须先用DECLARE命令定义后才可使用。
DECLARE命令的格式： DECLARE  {@变量名 变量类型}[, …n]
其中变量类型可以是SQL Server支持的所有数据类型,也可以是用户自定义的数据类型。

T-SQL使用SELECT或SET命令为变量赋值，其语法如下：
     SELECT {@变量名=<表达式>}[, …n]
     SET @变量名=<表达式>
注意：一条SET语句只能为一个变量赋值，而一条SELECT可以为多个变量赋值。 

简单CASE语句
  CASE <输入表达式>
   WHEN <当表达式1> THEN <结果表达式1>
   …………………………………………
   WHEN <当表达式n> THEN <结果表达式n>
   [ ELSE <结果表达式n+1> ]
END 

CASE搜索语句
  CASE
   WHEN <条件表达式1> THEN <结果表达式1>
   …………………………………………
   WHEN <条件表达式n> THEN <结果表达式n>
   [ ELSE <结果表达式n+1> ]
END

简单CASE语句
【例】显示每个学生的姓名、来自的省份和主修专业。
SELECT  姓名,  CASE 籍贯 
         	 WHEN '杭州' THEN '浙江'
         	 WHEN '宁波' THEN '浙江'
         	 WHEN '温州' THEN '浙江'
         	 WHEN '金华' THEN '浙江'
         	 WHEN '西安' THEN '陕西'
         	 WHEN '太原' THEN '山西'
      	END AS 省份, 专业 
FROM 学生表

CASE搜索语句
【例】显示每个学生的学号以及学生成绩（按优、良、中、及格、不及格、未知显示）。
SELECT 学号, 成绩 = CASE 		
		     WHEN 成绩>=90 THEN '优'
		     WHEN 成绩>=80 THEN '良'
		     WHEN 成绩>=70 THEN '中'
		     WHEN 成绩>=60 THEN '及格'
		     WHEN 成绩>=0  THEN '不及格'
          ELSE '未知' 
      	END
FROM 选课表

循环语句 
      基本格式为：
       WHILE <条件表达式>  
       BEGIN
    T-SQL语句[ …n]
    [BREAK]        /*跳出循环*/
    [CONTINUE]     /*进入下一次循环*/
 END

等待语句:
用来暂停程序执行，直到所设定的等待时间已过，或所设定的时间已到或设定的事件发生才继续往下执行，其语法如下：
      WAIT FOR { DELAY <’时间’> | TIME <’时间’>
		               | ERROREXIT | PROCESSEXIT | MIRROREXIT }
其中时间必须为DATETIME 类型的数据，如’11:15:27’，但不能包括日期。各关键字含义如下：
DELAY：用来设定等待的时间，最多可达24小时；
TIME：用来设定等待结束的时间点；
ERROREXIT：一直等待到处理非正常中断；
PROCESSEXIT：一直等待到处理正常或非正常中断；
MIRROREXIT： 一直等待到镜像设备失败

等待1小时2分零3秒后才执行SELECT语句。
waitfor delay ’01:02:03’
select *
from 教师表

等到晚上11点零8分后才执行SELECT 语句
waitfor time ’23:08:00’
select *
from 教师表

8.3.7 返回语句  
    常在存储过程中使用, 结束当前程序的执行，返回到上一个调用它的程序或其它程序，语法为：RETURN ([整型表达式]) 。
【例】简单存储过程中RETURN语句的使用。
        IF EXISTS (SELECT * FROM sysobjects WHERE name='CmpInt' and 
         xtype='P') DROP PROC CmpInt    --若存储过程已存在则删除之
GO
CREATE PROC CmpInt(@x INT, @y INT)  --创建存储过程
AS	IF @X>@Y  RETURN(1)
       ELSE  RETURN(2) 
GO
DECLARE @rc INT, @X INT, @Y INT
SELECT @X=100, @Y=200
EXEC @rc=CmpInt @X, @Y                           --调用存储过程
IF @rc =1 
      PRINT LTRIM(STR(@X)) + '比' + LTRIM(STR(@Y)) + '大'
ELSE 
      PRINT LTRIM(STR(@X)) + '比' + LTRIM(STR(@Y)) + '小'

学生选课处理
 
【例】学生“赵青山”计划选修“管理信息系统”课程，根据开课计划，每个关于“管理信息系统”的开课计划选修人数不能超过30人，如果都超过，则该课程不能再增加新的选修学生，如果有一个计划没有超过，则可以在这个计划里增加选修学生，如果有多个计划都没有超过计划人数，可在任一个计划里增加选修学生。
  已知学生“赵青山”的学号是“S060110”。 
   
USE 教学管理
GO
DECLARE @OFF_NO CHAR(6)
--检查是否有选修少于30人的开课计划，如果有，开课号送变量@OFF_NO
SELECT @OFF_NO=开课号 FROM 选课表 WHERE 开课号 IN
       (SELECT 开课号 FROM 开课表 WHERE 课号 IN
              (SELECT 课号 FROM 课程表 WHERE 课名='管理信息系统'))
       GROUP BY 开课号 HAVING COUNT(*)<30
--检查申请选修该课的学生是否已经选修了该课程，如果没有，增加选修记录
IF NOT EXISTS(SELECT * FROM 选课表 WHERE 开课号 IN
                    (SELECT 开课号 FROM 开课表 WHERE 课号 IN
                           (SELECT 课号 FROM 课程表 WHERE 课名='管理信息系统')) AND 学号='S060110')
   BEGIN
--开始一个插入事务
      BEGIN TRAN
      INSERT INTO 选课表 VALUES('S060110',@OFF_NO,NULL)
      IF @OFF_NO=NULL   --没有少于30人的开课计划，增加取消，否则提交
        ROLLBACK TRAN
      ELSE
        COMMIT TRAN
   END 

USE 教学管理
GO
DECLARE @OFF_NO CHAR(6)
set @OFF_NO =''
--检查是否有选修少于30人的开课计划，如果有，开课号送变量@OFF_NO
SELECT @OFF_NO=开课号 FROM 选课表 WHERE 开课号 IN
       (SELECT 开课号 FROM 开课表 WHERE 课号 IN
              (SELECT 课号 FROM 课程表 WHERE 课名='管理信息系统'))
       GROUP BY 开课号 HAVING COUNT(*)<30
--检查申请选修该课的学生是否已经选修了该课程，如果没有，增加选修记录
IF NOT EXISTS(SELECT * FROM 选课表 WHERE 开课号 IN
               (SELECT 开课号 FROM 开课表 WHERE 课号 IN(SELECT 课号 FROM 课程表 WHERE 课名='管理信息系统')) AND 学号='S060110')
   BEGIN

--开始一个插入事务
      BEGIN TRAN
        IF @OFF_NO=''  --没有少于30人的开课计划，增加取消，否则提交
          begin 
            rollback
            print '人数已满，选课失败！'
          end
        ELSE
          begin 
            INSERT INTO 选课表 VALUES('S060110',@OFF_NO,NULL)
            COMMIT TRAN
            print '选课成功！'
          end
   END 
 else
   print '已选此课程！'

函数

CAST和CONVERT ：将某种数据类型的表达式显式转换为另一种数据类型的值并返回 。格式：
   CAST ( <表达式> AS <目标数据类型>[(<长度>)] ) 
   CONVERT (<目标数据类型>[(<长度>)], <表达式> [, style]) 

【例】下面是使用CAST和CONVERT的简单例子。 
  SELECT CAST('ABC' AS CHAR(10) )+'CDE'
SELECT CONVERT(VARCHAR(60), GETDATE(), 126) 

 时间日期函数
1．DATEADD(datepart, number, date) 
在指定日期时间上加一段时间，得到新的DATETIME值并返回。
datepart是规定应向日期和时间的哪一部分计算新值的参数，具体取值如表8-3所示；
number是将要累加到datepart所规定部分的值；
date用来指定基础日期时间。
【例】下面的示例将当前日期时间加上3周后显示出来。
  DECLARE @old DATETIME, @new DATETIME
SELECT @old=GETDATE( ), @new=DATEADD(wk, 3, @old ) 
SELECT @old AS '当前日期时间', @new '三周后的日期时间'

2．DATEDIFF(datepart, startdate, enddate ) 
以INT类型返回两个给定日期时间值之差。
datepart规定了计算差额的日期时间部分，取值如表8-3所示；
startdate和enddate分别给出了起点日期时间和终止日期时间。 
【例】计算执行一个语句块所消耗的时间(单位为毫秒)。 
  DECLARE @start DATETIME, @end DATETIME 
SELECT @start=GETDATE( ) /*获得语句块的时间起点*/ 
BEGIN /*语句块开始*/ 
    IF EXISTS(SELECT * FROM sysobjects 
        WHERE  name='mytb' AND xtype='U' ) DROP TABLE mytb 
   SELECT * INTO mytb FROM sysobjects 
END  /*语句块结束*/ 
SELECT @end=GETDATE( )  /*获得语句块的时间终点*/ 
SELECT DATEDIFF(ms, @start, @end) AS '消耗的时间(ms) '

3．DATENAME(datepart, date ) 
以NVARCHAR类型返回代表指定日期时间的指定部分的字符串。 
datepart规定了日期时间部分，取值如表8-3所示；
date给出了指定日期时间。 
【例】从当前日期中提取所处月份和本年的第几个星期信息。 
  DECLARE @date DATETIME 
SELECT @date= GETDATE( ) 
SELECT DATENAME(m,@date) 月份, DATENAME(wk,@date) 星期
4．DATEPART(datepart, date) 
以INT类型返回代表指定日期时间的指定部分的整数。
5．DAY(date) ：以INT类型返回给定日期时间是本月的几号。 
6．GETDATE( )：以DATETIME类型返回当前系统日期和时间。
7．GETUTCDATE( ) ：以DATETIME返回当前格林尼治标准时间。
8．MONTH(date) 、YEAR(date) ：以INT返回日期时间的月份、年份

字符串函数
LEN(string_expression) ：返回给定字符串表达式的字符数，不包含尾随空格。
LOWER(character_expression) 、UPPER(character_expression) 
      将给定字符串中的大写(小写)字母转换为小写(大写)字母。
 REPLACE(字符串1,字符串2,字符串3)：用字符串3替换字符串1中字符串2的所有出现并返回替换结果。
 REVERSE(character_expression)：将给定字符串反转后返回。 
 STUFF(string_expression1, start, length, string_expression2)
用字符串string_expression2替换string_expression1中从start开始的length个字符并返回替换结果。 
【例】用'ijklmn'替换'ABCDEF'中第2个字符开始的3个字符。 
      PRINT STUFF('ABCDEF', 2, 3, 'ijklmn')

第9章 视图的规划与操作

创建视图

create view 视图名(列名，列，，，)
with encryption --加密
as

使用WITH CHECK OPTION, 该视图使得只能对指定条件的学生做数据修改。
USE 教学管理
IF EXISTS (SELECT TABLE_NAME 
           FROM INFORMATION_SCHEMA.VIEWS
           WHERE TABLE_NAME = 'V_仅对信电学院')
   DROP VIEW V_仅对信电学院
GO
CREATE VIEW V_仅对信电学院
AS 
SELECT 学号, 身份证号, 姓名, 性别, 移动电话, 籍贯,  
       专业, 所在院系, 累计学分
FROM 学生表
WHERE 所在院系 = '信电学院'
WITH CHECK OPTION
GO

修改

alter view 视图名
as
...

创建名“为V_全体教师信息”的视图，该视图包含全部的教师，并将该视图的查询权授予所有用户。但是由于该视图中包含了教师的编号、身份证号等个人信息，需使用ALTER VIEW替换该视图，不包括工号、身份证号、籍贯等个人信息，以保护教师个人隐私。
-- 根据教师表创建包含所有教师信息的视图.
CREATE VIEW V_全体教师信息 (工号,身份证号,姓名,移动电话,籍
                               贯,所在院系,职称)
AS 
SELECT 工号,身份证号,姓名,移动电话,籍贯,所在院系,职称
FROM 教学管理..教师表
GO
-- 将该视图的查询权授予所有用户.
GRANT SELECT ON V_全体教师信息 TO public
GO

-- 改变视图，去掉工号, 身份证号, 籍贯等个人信息。 
ALTER VIEW V_全体教师信息 (姓名,移动电话,所在院系,职称)
AS 
SELECT 姓名,移动电话,所在院系,职称
FROM 教学管理..教师表
GO

重命名

exec sp_rename '旧名字'，'新名字'

删除视图

drop view 视图名

USE 教学管理
IF EXISTS (SELECT TABLE_NAME 
           FROM INFORMATION_SCHEMA.VIEWS
           WHERE TABLE_NAME = 'V_学生平均成绩')
   DROP VIEW V_学生平均成绩
GO

第10章 游标操作和应用

游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

游标的使用

1. 声明游标

2. 打开游标

3. 从一个游标中提取信息

4. 关闭（释放）游标

声明
declare 游标名 insensitive/scroll cursor--SCROLL指出该游标可以用FETCH命令里定义的所有方法来存取数据，允许删除和更新
for 
select查询
for read only/update of 列名

打开游标
open global 游标名 --全局变量

读取游标数据
fetch next/prior/first/last/absolute n/@nvar/relative n
from global 游标名
into 变量名，。。

1）NEXT说明如果是在OPEN后第一次执行FETCH命令，则返回结果集的第一行，否则使游标（指针）指向结果集的下一行；NEXT是默认的选项，也是最常用的一种方法

（2）PRIOR、FIRST、LAST、ABSOLUTE { n|@nvar}、RELATIVE { n|@nvar}等各项，只有在定义游标时使用了SCROLL选项才可以使用；
      PRIOR是返回结果集当前行的前一行；如果FETCH PRIOR是第一次读取游标中数据，则无数据记录返回，并把游标位置设为第一行。
      FIRST是返回结果集的第一行；
      LAST是返回结果集的最后一行；
ABSOLUTE {n | @nvar} 如果n或@nvar为正数，则返回游标结果集中的第n或@nvar行数据。如果n或@nvar 为负数，则返回结果集内倒数第n或@nvar 行数据。若n或@nvar超过游标的数据子集范畴，则@@FETCH_STARS返回-1，在该情况下，如果n或@nvar为负数，则执行FETCH NEXT命令会得到第一行数据，如果n或@nvar为正值，执行FETCH PRIOR命令则会得到最后一行数据。n或@nvar 可以是一固定值也可以是一smallint, tinyint 或int类型的变量。

@@FETCH_STATUS变量
返 回 值	描    述
0	FETCH命令被成功执行。
-1	FETCH命令失败或所指定的范围超出了范围。
-2	要取的行不在记录集内，已从集合中删除

关闭游标
close global 游标名

使用游标的执行过程：
（1）声明一个游标
（2）打开游标
（3）读取游标
（4）BEGIN TRANSATION
（5）数据处理
（6）COMMIT TRANSATION
（7）回到步骤(3)

释放游标
deallocate global 游标名

USE 教学管理
GO
-- 创建一个全局游标，使其在创建它的批的外部仍然有效。
DECLARE S_cur CURSOR GLOBAL SCROLL FOR
SELECT * FROM 学生表
OPEN S_cur
GO
-- 定义一个游标变量并使其与S_cur游标关联。
DECLARE @mycrsrref1 CURSOR
SET @mycrsrref1 = S_cur
--取消游标变量和游标的关联。
DEALLOCATE @mycrsrref1 
-- 游标S-CUR依然存在。
FETCH NEXT FROM S_cur
GO
-- 再次与游标建立关联。
DECLARE @mycrsrref2 CURSOR
SET @mycrsrref2 = S_cur
-- 现在释放游标S-CUR。
DEALLOCATE S_cur
-- 但游标依然存在，因为被游标变量@mycrsrref2引用着。
FETCH NEXT FROM @mycrsrref2
-- 游标在批结束后，随着最后一个游标变量作用域的结束而最终被释放，因变量的作用域是定义它的批。
GO
-- 创建一个未命名的游标
DECLARE @mycursor CURSOR
SET @mycursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM 课程表
-- 以下语句释放游标，因为已没有其它变量引用该游标
DEALLOCATE @mycursor 
GO

如果在声明游标时使用了FOR UPDATE语句，那么就可以在UPDATE或DELETE命令中以WHERE CURRENT OF关键字直接修改或删除当前游标中当前行的数据。当改变游标中数据时，这种变化会自动地影响到游标的基础表。但是如果在声明游标时选择了INSENSITIVE选项时，该游标中的数据不能被修改

游标定位修改UPDATE语句的格式如下
UPDATE 表名
     SET 子句
     WHERE CURRENT OF {{[GLOBAL] 游标名}
               |游标变量}


游标定位删除DELETE语句的格式为如下
DELETE FROM 表名
     WHERE CURRENT OF {{[GLOBAL] 游标名}
               |游标变量}
首先查看学生表表中每一行，将学号等于’S060109’记录的移动电话改为13888320247，并将城市改为‘天津’。
SET NOCOUNT ON
DECLARE @学号 CHAR(6), @姓名 CHAR(10),@移动电话 CHAR(11), @籍贯 CHAR(10)
DECLARE stu_up_cur cursor
FOR
SELECT 学号,姓名,移动电话,籍贯
FROM 学生表
    FOR UPDATE OF 移动电话,籍贯  
OPEN stu_up_cur
FETCH NEXT FROM stu_up_cur INTO @学号, @姓名,@移动电话,@籍贯
WHILE @@fetch_status=0
BEGIN
  SELECT @学号, @姓名, @移动电话, @籍贯
  IF @学号='S060109'
   UPDATE 学生表
   SET 移动电话='13888320247',籍贯='天津'
   WHERE CURRENT OF stu_up_cur
   FETCH NEXT FROM stu_up_cur INTO @学号, @姓名, @移动电话, @籍贯
END
CLOSE stu_up_cur


下面的示例显示一个基于游标读取数据的进程。
USE 教学管理
GO
--申明一个教师信息游标
DECLARE 教师表_cursor CURSOR FOR 
SELECT 工号, 姓名,所在院系, 职称
FROM 教师表
ORDER BY 工号
DECLARE @tnum CHAR(5),
         @tname CHAR(10),
         @tdepa CHAR(20),
         @trank CHAR(10)
--打开游标
OPEN 教师表_cursor  
--取游标第一行数据
FETCH NEXT FROM 教师表_cursor 
   INTO @tnum, @tname, @tdepa, @trank
--逐行显示教师信息，并取下一行数据
WHILE @@FETCH_STATUS = 0
BEGIN 
   SELECT @tnum, @tname, @tdepa, @trank
   FETCH NEXT FROM 教师表_cursoR 
   INTO @tnum, @tname, @tdepa, @trank
END
--关闭游标，此时游标还可以重新打开
CLOSE 教师表_cursor
--释放游标
DEALLOCATE 教师表_cursor
GO

第11章 用户自定义函数设计

标量型函数返回在RETURNS子句中定义的类型的单个数据值。

内嵌表值型函数以表的形式返回一个返回值，即它返回的是一个表。

多语句表值型函数可以看作标量型和内嵌表值型函数的结合体。

标量型
create function 函数名 (参数 参数数据类型)
return 返回值数据类型
as
begin
	函数体
	return 返回值
end

内嵌表值型函数
create function 函数名 (参数 参数数据类型)
return table
as
return  

多语句表值型函数
create function 函数名 (参数 参数数据类型)
return table
as
begin
	函数体
	return 
end

创建标量值用户自定义函数
create function F_成绩分级（@成绩 float)
return char(16)
as
begin
declare @等级 char(16)
select @等级=case
 WHEN @成绩 IS NULL THEN '还没参加考试'
    WHEN @成绩 < 60 THEN '不及格'
    WHEN @成绩 >= 60 and @成绩 < 70 THEN '及格' 
    WHEN @成绩 >= 70 and @成绩 < 80 THEN '中等'
    WHEN @成绩 >= 80 and @成绩 < 90 THEN '良好'
    ELSE 
      '优秀' 
end
return (@等级)

创建内联表值型函数，返回指定学院学生的信息 
create function F_学生信息（@院系 char(20))
return table
as
return (select 学号, 姓名, 性别, 所在院系, 专业, 籍贯
from 学生表
where 所在院系=@院系)

创建多语句表值型函数，返回指定教师某学年的开课信息
create function F_教师课表（@教师姓名 CHAR(8),@开课学年 char(9))
return @教师课表 TABLE(
课名 varchar(30),开课地点 char(6),
开课学年 char(9),开课学期 int,
开课周数 int,开课时间 varchar(20),
已选人数 int
)   
AS
BEGIN
    INSERT @教师课表
    SELECT 课名,开课地点,开课学年,开课学期,开课周数, 开课时间,已选人数
    FROM 教师表 T,开课表 O,课程表 C
    WHERE T.工号=O.工号 
   AND O.课号=C.课号 
   AND 姓名=@教师姓名 
   AND 开课学年=@开课学年
    RETURN
END 

调用函数

调用创建的函数，按五等成绩显示每个学生所选课程的成绩。
SELECT E.学号,姓名,O.开课号,O.课号,课名, DBO.F_成绩分级(成绩) AS '成绩等级'
FROM 选课表 E, 学生表 S,开课表 O, 课程表 C
 WHERE S.学号 =E.学号
 AND E.开课号= O.开课号
     AND C.课号= O.课号  
     
 调用创建的函数F_学生信息，返回指定学院的学生的基本信息
--下面调用该函数
DECLARE @院系 CHAR(20)
SET @院系='信电学院'
SELECT * FROM F_学生信息(@院系) 

调用创建的函数F_教师课表，能返回指定老师某学年所上的课程信息。
--下面调用该函数
DECLARE @姓名 CHAR(10)
SET @姓名='黄中天'
SELECT * FROM F_教师课表(@姓名,'2007-2008') 

修改函数

alter function 函数名（参数，参数类型）
。。。

删除函数

alter function 函数名

第12章 存储过程和用户存储过程设计

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。
用户或应用程序通过指定存储过程的名字并给出参数来执行它，而且允许用户声明变量、有条件执行以及其它强大的编程功能。

用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。

创建存储过程

create procedure 存储过程名(@参数名，数据类型（output）)
with encryption
for replication
as
SQL语句

创建一个简单的存储过程，返回所有学生的基本信息，包括学生的学号、姓名、所学专业、所在二级学院、来自的城市。
create procedure p_学生部分信息
as
select 学号，姓名，专业，所在院系，籍贯
from 学生表
go

创建带参数的存储过程，实现对指定的某一专业某门课程的学生选课信息和成绩的查询。
 USE 教学管理
IF EXISTS(SELECT* 
        FROM sysobjects
        WHERE name='P_学生选课信息' AND type='p')
     BEGIN
       DROP PROCEDURE P_学生选课信息
     END
GO
CREATE PROCEDURE P_学生选课信息(@专业 CHAR(20), @课名 CHAR(20))
  AS
  SELECT S.学号,姓名,专业,所在院系,O.课号,课名,成绩
    FROM 学生表 S, 选课表 E, 开课表 O, 课程表 C
    where 专业=@专业
     AND 课名=@课名
     AND S.学号=E.学号
     AND E.开课号=O.开课号
     AND O.课号=C.课号
GO

执行存储过程

exec(ute) 存储过程名（参数）
EXEC P_学生部分信息 

按参数位置传递值

 EXEC P_学生选课信息 '计算机','数据结构'
 
 DECLARE @smajor CHAR(20), @cname CHAR(20)
SET @smajor='计算机'
SET @cname='数据结构'
EXEC P_学生选课信息 @smajor,@cname

按参数名传递值可以改变参数的顺序

使用OUTPUT参数的存储过程及其执行
create procedure P_成绩检索和平均（@学号 char(7),@平均成绩 float output)
as
SELECT S.学号,姓名,课号,成绩
FROM 学生表 S, 开课表 O, 选课表 E
WHERE S.学号=@学号
AND E.学号=S.学号 
AND E.开课号=O.开课号
SELECT @平均成绩 =AVG(成绩)
FROM 学生表 S, 开课表 O, 选课表 E
WHERE S.学号=@学号
   AND E.学号=S.学号
   AND E.开课号=O.开课号
return
Go

--然后在查询分析器中调用 P_成绩检索和平均 存储过程

DECLARE @学号 CHAR(7),@平均成绩 FLOAT
SET @学号='S060102‘
EXEC P_成绩检索和平均 @学号, @平均成绩 OUTPUT 

修改和删除

alter proc 存储过程名（参数 数据类型）
as
SQL语句

drop proc 存储过程名

当删除开课计划时，须先查看该开课计划有没有学生注册，如果有，则不能删除，否则进行删除。

USE 教学管理
GO
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'P_开课信息删除' AND type = 'P')
   DROP PROCEDURE P_开课信息删除
GO
--创建存储过程
CREATE PROCEDURE P_开课信息删除(@开课号 CHAR(6))
AS 
BEGIN
  IF EXISTS(SELECT * FROM 选课表 WHERE 开课号=@开课号) 
--有学生注册该课程开课计划，不能删除
     RETURN 1
  ELSE
--没有学生注册该课程开课计划，可以删除
     BEGIN
       DELETE FROM 开课表 WHERE 开课号=@开课号
       RETURN 2
     END
END
declare @return_status int, @开课号 CHAR(6)
set @开课号 ='010106'
exec @return_status = P_开课信息删除 @开课号 
if(@return_status =1) 
   print '有学生注册该课程开课计划，不能删除!'
if(@return_status =2) 
   print '开课计划成功删除!'

第13章 触发器原理及使用

触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。存储过程和触发器同是提高数据库服务器性能的有力工具。

AFTER触发器
即为SQL Server 2000版本以前所介绍的触发器。该类型触发器要求只有执行完某一操作（INSERT、UPDATE、DELETE），并处理过所有约束后，触发器才被触发，且只能在表上定义。如果操作违反约束条件，将导致事务回滚，这时就不会执行触发器。

INSTEAD OF触发器
该类触发器表示并不执行其所定义的操作（INSERT、UPDATE、DELETE），而仅是执行触发器本身。可在表上定义INSTEAD OF触发器，也可以在视图上定义INSTEAD OF触发器，但对同一操作只能定义一个INSTEAD OF触发器。

• 对一个定义了插入类型触发器的表来讲，一旦对该表执行了插入（INSERT）操作，系统就会生成一个特殊表–插入表（inserted），这个表驻留在内存中，不是存储在数据库中，因此不允许用户直接对其修改。
  对于用户插入的所有行来说，都有一个相应的副本拷贝存放到插入表（inserted）中，即插入表就是用来存储原表插入的新数据行。
• 对一个定义了删除类型触发器的表来讲，一旦对该表执行了删除（DELETE）操作，则将所有的被删除的行存放至删除表（deleted表）中。这样做的目的是，一旦触发器遇到了强迫它中止的语句被执行时，删除的那些行可以从删除表（deleted表）中得以还原。
• 需要强调的是，更新（UPDATE）操作包括两个部分，即先将旧的内容删除，然后将新值插入。因此，对一个定义了更新类型触发器的表来讲，当执行更新操作时，在删除表中存放了修改之前的旧值，然后在插入表中存放的是修改之后的新值。

创建触发器

create trigger 触发器名字
on table/view 
with encryption
for/after/instead of insert,update,delete
as
if update (列名) and/or update (列名)
sql语句

修改触发器

sp_rename oldname,newname

删除触发器

USE 教学管理
GO
IF EXISTS (SELECT name FROM sysobjects
WHERE name ='T_学生表改变显示' AND type = 'TR')
   DROP TRIGGER T_学生表改变显示
GO

insert触发器

INSERT触发器通常被用来验证被触发器监控的字段中数据满足要求的标准，以确保数据的完整性。例：建立一个触发器，当向sc表中添加数据时，如果添加的数据与student表中的数据不匹配（没有对应的学号），则将此数据删除。
create trigger tr_sc_insert 
on sc
after insert
as 
begin
	declare @bh char(10)
	select @bh=sno from inserted
	if no exists(
    	select sno
        from student
        where sno=@bh
    )
    delete sc
    where sno=@bh
end
insert into sc values('07','2',78)

在大学数据库中，当新的学生选课注册信息增加到选课表表中的时候，要对开课表表中学生选课人数进行更新，且当人数超过最多能容纳的人数时，要提示选课人数已满的信息。
  USE 教学管理
  GO
  CREATE TRIGGER T_选课表插入触发
  ON 选课表
  After INSERT
BEGIN
DECLARE @已选人数 INT,@限选人数 INT
SELECT @已选人数=已选人数+1, @限选人数=限选人数
FROM 开课表 O,inserted i
WHERE O.开课号=i.开课号
IF (@已选人数 > @限选人数)
  BEGIN
   PRINT '选修人数已满！'
   ROLLBACK TRANSACTION
  END
UPDATE 开课表
SET 已选人数=@已选人数
 FROM 开课表 O, inserted i
 WHERE O.开课号=i.开课号
END
--验证：
--查看已选人数
SELECT 开课号,限选人数,已选人数 FROM 开课表 WHERE 开课号='020102'
--在选课表里增加一条记录。下面用了错误捕捉方法，见例7-2。
BEGIN TRY 
BEGIN TRANSACTION
INSERT INTO 选课表 VALUES('S060306', '020102',NULL)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
     ROLLBACK TRANSACTION
END CATCH
--再查看已选人数
SELECT 开课号,限选人数,已选人数 FROM 开课表 WHERE 开课号='020102' 

update触发器

教师表里的工号和负责人具有外键关系，当负责人工号修改了，负责人内容也要跟着改变。
USE 教学管理
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name ='T_负责人工号变化' AND type = 'TR')
   DROP TRIGGER T_负责人工号变化
GO
CREATE TRIGGER T_负责人工号变化
ON 教师表
FOR UPDATE
AS
BEGIN
DECLARE @old_工号 CHAR(6),@new_工号 CHAR(6)
SELECT @old_工号=工号
FROM deleted
SELECT @new_工号=i.工号
FROM inserted i
UPDATE 教师表
SET 负责人=@new_工号
WHERE 负责人=@old_工号
END
     --此时用到inserted和deleted表，在inserted表中，存放的是执行UPDATE操作的表中被修改的那些记录修改之后的新值，而在delelted表中，存放的是执行UPDATE操作的表中被修改的那些记录修改之前的旧值。
--验证
BEGIN TRAN
--查询教师表
SELECT * FROM 教师表
--修改教师表中的工号，将’T01001’变为’T01003’
UPDATE 教师表
  SET 工号='T01003' 
  WHERE 工号='T01001'
--再查询教师表
SELECT * FROM  教师表
ROLLBACK

delete 触发器

DELETE触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除，第二种情况是执行可删除主记录的子记录的级联删除操作。
例  建立一个与sc表结构一样的表s1，当删除表sc中的记录时，自动将删除掉的记录存放到s1表中。
create trigger tr_student_delete
on sc
after delete
as
begin
insert
into s1
select *
from deleted
end

当某个学生退学时，须删除该学生的基本数据，并级联删除该学生的选课记录（需暂时去掉选课表上相对学生表的外键约束）。 
CREATE TRIGGER T_学生数据删除
ON 学生表
FOR DELETE
AS
BEGIN
DELETE FROM 选课表
FROM 选课表 E,deleted d
WHERE E.学号=d.学号
END
--验证
BEGIN TRAN
--查看删除前的记录
SELECT * FROM 学生表 S,选课表 E WHERE S.学号=E.学号
--删除学生表，同时触发删除选课表中对应的数据
DELETE FROM 学生表 WHERE 学号='S060101'
--查看删除后的记录
SELECT * FROM 学生表 S,选课表 E WHERE S.学号=E.学号
ROLLBACK 

instead of 触发器

前面三类触发器统称为AFTER 触发器（也叫“FOR”触发器），只能用在表上，而INSTEAD OF触发器既可以用在表上，也可以使用在视图上。用INSTEAD OF可以指定执行触发器而不是执行触发语句本身，从而屏蔽原来的SQL语句，而转向执行触发器内部的SQL语句。
对同一操作只能定义一个INSTEAD OF触发器。

当删除教师表某教师信息时，需先查看开课表有没有该教师的开课情况，如果有，则不能删除。如果没有，就执行触发器中的删除语句完成删除。
USE 教学管理
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name ='T_教师表信息删除' AND type = 'TR')
   DROP TRIGGER T_教师表信息删除
GO
create trigger T_教师表信息删除
on 教师表
instead of delete
as
BEGIN
DECLARE @姓名 CHAR(20)
SELECT @姓名=姓名 FROM deleted
IF EXISTS(SELECT * FROM 开课表 O,deleted d
                 WHERE O.工号=d.工号)
    PRINT @姓名+'教师有开课计划，不能删除'
ELSE
    BEGIN
      DELETE FROM 教师表 FROM 教师表 T,deleted d
         WHERE T.工号=d.工号
      PRINT @姓名+'教师没有开课计划，已经删除'
  END
END
--验证
BEGIN TRAN
--查看删除前的信息
SELECT * FROM 教师表 
SELECT * FROM 开课表
--删除教师信息
DELETE FROM 教师表 WHERE 姓名='曲宏伟'
--查看删除后的信息
SELECT * FROM 教师表 
SELECT * FROM 开课表
ROLLBACK

例9-11视图“V_信电学生成绩”引用了多个表，对视图不能直接执行更新删除，但可以使用INSTEAD OF触发器完成以上功能。

USE 教学管理
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name ='T_视图信息删除1' AND type = 'TR')
   DROP TRIGGER T_视图信息删除1
GO
CREATE TRIGGER T_视图信息删除1
ON V_信电学生成绩
INSTEAD OF DELETE
AS
BEGIN
DECLARE @学号 CHAR(7),@开课号 CHAR(6)
SELECT @学号=学号,@开课号=开课号 FROM deleted
--在INSTEAD OF触发器里实际是对表的操作
DELETE FROM 选课表 WHERE 学号=@学号 AND 开课号=@开课号
END
--验证
BEGIN TRAN
SELECT * FROM V_信电学生成绩 WHERE 学号='S060101'
--对视图进行删除操作
DELETE FROM V_信电学生成绩 WHERE 学号='S060101' AND 开课号='010201'
SELECT * FROM V_信电学生成绩 WHERE 学号='S060101'
ROLLBACK

触发器应用实例分析

	分析1：由于学生选课管理的实际情况，学生在学期初或前一学期结束之前就进行选课，而成绩是在学期末考试后输入，所以录入成绩实际上是对选课表的数据的修改。故我们可以创建该表的修改触发器，实现学分的自动累计。由于成绩修改UPDATE语句可能涉及多个学生，故我们要在触发器中使用游标对每个学生进行判断修改。

CREATE TRIGGER T_选课学分修改
ON 选课表
FOR UPDATE
AS
BEGIN
IF (@@ROWCOUNT>0)
 BEGIN
  DECLARE @old_成绩 FLOAT, @new_成绩 FLOAT
  DECLARE @学号_d CHAR(7),@开课号_d CHAR(6),@学号_i CHAR(7),@开课号_i CHAR(6)
  DECLARE @学分 INT
  DECLARE CUR_选课新信息 CURSOR
  FOR 
  SELECT 学号,开课号,成绩  FROM inserted 

  DECLARE CUR_选课旧信息  CURSOR
  FOR 
  SELECT 学号,开课号,成绩
  FROM deleted
  OPEN CUR_选课新信息
  OPEN CUR_选课旧信息
  FETCH NEXT FROM CUR_选课新信息
   INTO @学号_i, @开课号_i,@new_成绩
  FETCH NEXT FROM CUR_选课旧信息
   INTO @学号_d, @开课号_d,@old_成绩
  SELECT @学分=学分
  FROM 开课表 O,课程表 C
  WHERE O.课号=C.课号 AND 开课号=@开课号_i 
 WHILE @@fetch_status=0
  BEGIN
   IF (@old_成绩 is NULL) AND (@new_成绩>=60)
    UPDATE 学生表
    SET 累计学分=累计学分+@学分
    WHERE 学号=@学号_i
  IF (@old_成绩< 60) AND (@new_成绩>= 60)
    UPDATE 学生表
    SET 累计学分=累计学分+@学分
    WHERE 学号=@学号_i
  IF (@old_成绩>= 60) AND (@new_成绩< 60 or @new_成绩 is NULL)
    UPDATE 学生表
    SET 累计学分=累计学分-@学分
    WHERE 学号=@学号_i
   FETCH NEXT FROM CUR_选课新信息
     INTO @学号_i, @开课号_i,@new_成绩
   FETCH NEXT FROM CUR_选课旧信息
     INTO @学号_d, @开课号_d,@old_成绩
   SELECT @学分=学分
   FROM 开课表 O,课程表 C
   WHERE O.课号=C.课号 AND 开课号=@开课号_i
 END
  CLOSE CUR_选课新信息
  CLOSE CUR_选课旧信息
  DEALLOCATE CUR_选课新信息
  DEALLOCATE CUR_选课旧信息
 END 
END
--验证
BEGIN TRAN
--查看修改前的信息
SELECT * FROM 选课表 WHERE 学号='S060101' 
SELECT * FROM 学生表 WHERE 学号='S060101' 
--进行修改
update 选课表 set 成绩=85 WHERE 学号='S060101' and 开课号='010201'
--查看修改后的信息
SELECT * FROM 选课表 WHERE 学号='S060101' 
SELECT * FROM 学生表 WHERE 学号='S060101' 
ROLLBACK

      分析2：对于成绩取消的情况，相当于删除了该课程的选课记录和成绩，因此我们可以设计选课表的删除触发器来实现学分的取消。
USE 教学管理
GO
IF EXISTS (SELECT name FROM sysobjects
 WHERE name ='T_选课记录删除' AND type = 'TR')
   DROP TRIGGER T_选课记录删除
GO
CREATE TRIGGER T_选课记录删除
ON 选课表
FOR DELETE
AS
BEGIN
IF (@@ROWCOUNT>0)
BEGIN
   DECLARE @成绩FLOAT
   DECLARE @学号CHAR(7),@开课号CHAR(6)
   DECLARE @学分INT
   DECLARE CUR_选课删除 CURSOR
           FOR 
           SELECT 学号,开课号,成绩
           FROM deleted
OPEN CUR_选课删除
FETCH NEXT FROM CUR_选课删除
INTO @学号, @开课号, @成绩
SELECT @学分=学分
    FROM 开课表O,课程表C
    WHERE O.课号=C.课号AND 开课号=@开课号
WHILE @@fetch_status=0
BEGIN
 IF (@成绩>= 60)
  UPDATE 学生表
  SET 累计学分=累计学分-@学分
  WHERE 学号=@学号

 FETCH NEXT FROM CUR_选课删除
  INTO @学号, @开课号,@成绩
 SELECT @学分=学分
   FROM 开课表O,课程表C
   WHERE O.课号=C.课号 AND 开课号=@开课号
END
CLOSE CUR_选课删除
DEALLOCATE CUR_选课删除
END 
END

--验证
BEGIN TRAN
--查看删除前的信息
SELECT * FROM 选课表 WHERE 学号='S060101' 
SELECT * FROM 学生表 WHERE 学号='S060101' 
--删除选课信息
DELETE FROM 选课表 WHERE 学号='S060101' and 开课号='010101'
--查看删除后的信息
SELECT * FROM 选课表 WHERE 学号='S060101' 
SELECT * FROM 学生表 WHERE 学号='S060101' 
ROLLBACK




       

第14章 数据库安全及访问控制

数据库安全控制层次

• 用户标识与鉴别
• DBMS存取控制
• 数据加密
• 审计追踪

将新建的Windows NT账户meng用系统存储过程添加到SQL Server系统中。
    EXEC sp_grantlogin  'ZUFE-MXH\meng'     
    -- ZUFE-MXH是计算机名，meng是Windows 用户   
    
创建基于Win1的Windows登录账户
CREATE LOGIN [win2k3\Win1] FROM WINDOWS
WITH DEFAULT_DATABASE=stu

可以使用DROP LOGIN命令删除登录账户，语法格式如下所示。
DROP LOGIN LoginName 

创建SQL Server登录账户Sql1，密码为“*123456”，要求用户在下一次登录时更改密码，用户默认使用的数据库是stu，对该账户应用本地密码策略并检查Windows过期策略。
create login Sql1 with password='' must_change,
default_database=stu,
check_expiration=on,
check_policy=on
go

删除SQL Server登录账户Sql2。
DROP LOGIN Sql2

删除SQL Server登录账户Sql3。
sp_droplogin Sql3
GO

数据库访问权限的建立与删除

将上述建立的Windows 用户“ZUFE-MXH\meng” 添加到“教学管理”数据库，并取名MANAGER。
USE 教学管理
GO
EXEC sp_grantdbaccess ‘ZUFE-MXH\meng’,’ MANAGER’
GO
可以使用CREATE USER语句创建数据库用户，其基本的语法如下所示：
CREATE USER UserName [FOR LOGIN LoginName]
例 ：在教学管理数据库中为登录账户Sql1创建用户SqlUser1。
USE 教学管理
GO
CREATE USER SqlUser1 FOR LOGIN Sql1
GO
例： 在教学管理数据库中为登录账户[win2k3\Win1]创建同名用户。
USE 教学管理
CREATE USER [win2k3\Win1]
GO 

删除windows账户“ZUFE-MXH\meng” 名为“MANAGER”访问“教学管理”数据库的访问权限。
USE 教学管理
GO
EXEC sp_revokedbaccess 'MANAGER'
GO

可以使用DROP USER UserName语句删除指定的数据库用户。
例  将数据库用户SqlUser1删除。
USE 教学管理
GO
DROP USER SqlUser1
GO

角色管理

角色是一个强大的工具，可以将用户集中到一个单元中，然后对该单元应用权限。可以建立一个角色来代表单位中一类工作人员所执行的工作，然后给这个角色授予适当的权限。当工作人员开始工作时，只须将他们添加为该角色成员，当他们离开工作时，将他们从该角色中删除。而不必在每个人接受或离开工作时，反复授予、拒绝和废除其权限。权限在用户成为角色成员时自动生效。
管理员和数据库拥有者在设置访问权限时，应首先建立角色，并将访问权限集中授予角色，之后将需要拥有这一权限的用户加入到角色中，这些用户即继承角色的访问权限。需要撤消用户的访问权限时，将用户从角色中删除即可。

将账户ZUFE-MXH\meng添加到固定服务器角色sysadmin中，或将固定服务器角色sysadmin添加分配给账户ZUFE-MXH\meng。
EXEC sp_addsrvrolemember 'ZUFE-MXH\meng','sysadmin'

使用系统存储过程
语法格式：
   sp_dropsrvrolemember   
   [@loginame=]’login’[,[@rolename=]’role’]
【例】将账户ZUFE-MXH\meng从固定服务器角色sysadmin中删除掉。
EXEC sp_dropsrvrolemember 'ZUFE-MXH\meng','sysadmin‘

（1）创建用户自定义角色 
sp_addrole [@rolename=]’role’ 
                       [,[@ownername=]’owner’]
参数说明：
（1）‘role’为新建立的数据库角色名称。
（2）‘owner’为新建角色的所有者，默认为dbo。
【例】为数据库“教学管理”设置教师角色。
USE 教学管理
GO
EXEC sp_addrole '教师'
GO

（2）删除用户自定义角色
 sp_droprole[@rolename=]’role’
 参数说明：
 ‘role’为要删除的的数据库自定义角色名称。
 【例】删除教学管理数据库中建立的数据库角色教务 
  管理员。
    USE 教学管理
    GO
    EXEC sp_droprole  '教务管理员' 

（1）添加数据库角色成员 
       sp_addrolemember [@rolename=]’role’，
                [@membername=]’security_account’ 
【例】将Windows用户“ZUFE-MXH\meng”添加到教学管理数据库，使其成为用户STU_ ZHANGNAGER，然后再将STU_ZHANGNAGER添加为教学管理数据库教师角色成员。
EXEC sp_grantdbaccess 'ZUFE-MXH\meng','STU_ZHANGNAGER'  
    --建立用户‘ZUFE-MXH\meng’访问数据库‘教学管理’的权限，并命名为‘STU_ZHANGNAGER’。
GO
EXEC sp_addrolemember '教师','STU_ZHANGNAGER'  
    --将用户‘STU_ZHANGNAGER’添加到角色‘教师’中成为其一个成员
    
    （2）删除数据库角色成员 
   sp_droprolemember [@rolename=]’role’，
                                   [@membername=]’security_account’ 

【例】将用户STU_ZHANGNAGER从教师角色中删除。
USE 教学管理
GO
EXEC sp_droprolemember '教师','STU_ZHANGNAGER'  
      --将用户‘STU_ZHANGNAGER’从角色‘教师’中删除。 

（1）建立应用程序角色 
SQL Server中，只有固定服务器角色sysadmin成员、固定数据库角色db_owner和db_securityadmin成员才能运行以下存储过程创建或删除应用程序角色：
sp_addapprole [@rolename=]’role’,[@password=]‘password’
sp_dropapprole [@rolename=]’role’
例如：在教学管理数据库中建立应用程序角色“P_学生部分信息”，设口令为123。
USE 教学管理
GO
EXEC sp_addapprole 'P_学生部分信息','123'

（2）激活和使用应用程序角色 
建立应用角色后，SQL Server数据库应用程序可以调用系统存储过程sp_setapprole激活角色。其语法格式为：
sp_setapprole [@rolename=]’role’,[@password=] {Encrypt N ‘password’}|’password’[,[@encrypt=]’encrypt_style’]
其中，Encrypt N选项要求应用程序在向SQL Server传递应用程序角色口令之前，将其加密。
 encrypt_style说明加密方式，它有以下两种取值：
 none:用明文方式传递，这是默认方式；
 odbc:使用ODBC规范定义的Encrypt加密函数对角色口令进行加密
例如，在一个客户端应用程序中可以执行以下语句激活前面创建的应用程序角色‘P_学生部分信息’。

USE 教学管理
GO
EXEC sp_setapprole 'P_学生部分信息','123', 'none'
GO

  

数据库权限管理

revoke 无权操作
grant 有权操作
deny 不能操作

系统管理员授予注册名为meng的用户CREATE DATABASE的权限。
use master
go
grant create any database to meng
授予用户STU_MANAGER（ZUFE-MXH\meng登录标识的名称）具有创建表的权限。
use 教学管理
go
grant create table to stu_manager
go

授予角色‘教师’具有查询学生表、课程表、教师表、开课表和选课表的权限。
UES 教学管理
GO
GRANT SELECT ON 学生表 TO 教师
GRANT SELECT ON 课程表 TO 教师
GRANT SELECT ON 教师表 TO 教师
GRANT SELECT ON 开课表 TO 教师
GRANT SELECT ON 选课表 TO 教师

如果给public角色授予教学管理数据库的学生表有SELECT权限，就使得所有用户和角色都有了这个权限。但不让部分用户或角色（比如用户zhang和更新角色）有这个权限。
USE 教学管理
GO
GRANT SELECT ON 学生表 TO public
GO
DENY SELECT ON 学生表  TO zhang,更新
GO

REVOKE语句中的GRANT OPTION FOR选项有以下两种作用：
. 如果REVOKE语句所撤消的对象权限是GRANT语句使用WITH GRANT OPTION 选项授予用户的，则REVOKE语句撤消用户所得到的该对象权限的转授权限，但GRANT语句授予用户的对象权限仍然保留。此外，如果该用户已经使用转授权限向其他用户授予他所得到的对象权限，REVOKE语句将一并撤消这些用户所得到的权限。这时，在REVOKE语句中必须同时使用CASCADE选项。
. 如果REVOKE语句所撤消的对象权限不是GRANT语句使用WITH GRANT OPTION 选项授予用户的，则REVOKE语句撤消用户所得到的对象权限。这时，在REVOKE语句中必须不使用CASCADE。

除教师角色查询学生表的权限

REVOKE select ON 学生表  FROM 教师

第15章 数据备份与恢复

使用ALTER DATABASE命令可以修改数据库的恢复模式属性，该命令的语法如下。
ALTER DATABASE DatabaseName 
SET RECOVERY { FULL | BULK_LOGGED | SIMPLE }

例 将BlueSkyDB数据库的恢复模式设置为简单恢复模式。
USE master
GO
ALTER DATABASE BlueSkyDB SET RECOVERY SIMPLE
GO 
1．创建备份设备 
（1）使用系统存储过程创建
语法格式：
sp_addumpdevice [@devtype =] '备份设备类型',
        [@logicalname=] '备份设备的逻辑名称',
        [@physicalname=] '备份设备的物理名称'
其中：‘备份设备类型’可以取下列三个值之一：
disk：磁盘
pipe：命名管道
tape：磁带设备 
（2）使用ssms创建
（3）创建备份设备实例

【例】创建名为‘教学管理_FULL’的磁盘备份设备，其物理名称为‘D:\Server\MSSQL\Backup\教学管理_FULL.dat’。
USE master
GO
EXEC sp_addumpdevice
    'disk','教学管理_FULL','d:\server\mssql\backup\教学管理_FULL.dat'
GO

例 在C盘根目录创建一个文件夹dump。创建一个名为mydiskdump的磁盘备份设备，其物理名称为C:\dump\dump1.bak。

USE master
GO
EXEC sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump\dump1.bak'
GO

例 创建一个名为tapedevice的磁带备份设备，其物理名称为\\.\tape0。

USE master
GO
EXEC sp_addumpdevice 'tape',' tapedevice ','\\.\tape0'
GO

删除备份设备的命令是存储过程sp_dropdevice，该系统存储过程的使用方法如下。
sp_dropdevice [ @logicalname = ] 'device' 
    [ , [ @delfile = ] 'delfile' ]

例：删除磁盘备份设备‘B_备份设备’，其物理名称为D:\Server\MSSQL\Backup\B_备份设备.bak
EXEC  sp_dropdevice 'B_备份设备', 'DELFILE‘

例：删除名为tapedump1的磁带备份设备。
EXEC sp_dropdevice 'apedevice'

【例】完整备份教学管理数据库
BACKUP DATABASE 教学管理
TO 教学管理_FULL
WITH INIT


提示：使用INIT选项覆盖上一次的备份。 

对教学管理数据库进行差异备份，备份设备是‘教学管理_DIFF’。
backup database 教学管理
to 教学管理_DIFF
with differential

备份教学管理数据库的日志到备份设备‘教学管理_LOG’。

BACKUP LOG 教学管理
TO 教学管理_LOG
WITH INIT

【例】“教学练习”数据库包括四个数据文件：教学练习_data、教学练习_add、教学练习_data1、教学练习_data2，备份教学练习_data1、教学练习_data2数据文件到备份设备教学练习_FILE，备份事务日志教学练习_LOG到备份设备教学练习_BAKLOG。

BACKUP DATABASE 教学练习
        FILE='教学练习_data1',  
        FILE='教学练习_data2'
TO 教学练习_FILE

 BACKUP   LOG  教学练习_LOG 
 TO     教学练习_BAKLOG    
（不支持）
【例】从完整备份‘教学管理_FULL’中恢复‘教学管理’数据库。
RESTORE DATABASE 教学管理
FROM 教学管理_FULL
WITH RECOVERY 

【例】从差异备份‘教学管理_DIFF’中恢复‘教学管理’数据库。
--从差异备份中恢复需要两步操作：
--①先从完整备份恢复，使用NORECOVERY选项。
RESTORE DATABASE 教学管理
FROM 教学管理_FULL
WITH NORECOVERY
--②再从差异备份中恢复，使用RECOVERY选项。
RESTORE DATABASE 教学管理
FROM 教学管理_DIFF
WITH RECOVERY

【例】从日志备份‘教学管理_LOG’中恢复‘教学管理’数据库。
   恢复日志备份的时候，SQL Server只恢复事务日志中所记录的数据更改。恢复步骤是首先恢复完整数据库备份，如果存在差异备份，则恢复差异备份，最后恢复日志备份。
--①先从完整备份恢复，使用NORECOVERY选项。
RESTORE DATABASE 教学管理
FROM 教学管理_FULL
WITH NORECOVERY
--②再从差异备份中恢复，使用NORECOVERY选项。
RESTORE DATABASE 教学管理
FROM 教学管理_DIFF
WITH NORECOVERY
--③最后使用日志备份恢复，使用RECOVERY选项。
RESTORE LOG 教学管理
FROM 教学管理_LOG
WITH RECOVERY

【例】从文件或文件组备份‘教学练习_GROUP’和日志备份‘教学练习_GROUPLOG’中恢复‘教学练习’数据库。
  恢复步骤如下：
  ① 从文件组备份恢复，使用NORECOVERY选项。
RESTORE DATABASE 教学练习
    FILE='教学练习_data1',
    FILE='教学练习_data2'
    FROM 教学练习_GROUP
    WITH NORECOVERY
  ② 使用日志备份恢复，使用RECOVERY选项。
RESTORE LOG 教学练习
FROM 教学练习_GROUPLOG
        WITH RECOVERY 








C:\dump\dump1.bak。

USE master
GO
EXEC sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump\dump1.bak'
GO

例 创建一个名为tapedevice的磁带备份设备，其物理名称为\\.\tape0。

USE master
GO
EXEC sp_addumpdevice 'tape',' tapedevice ','\\.\tape0'
GO

删除备份设备的命令是存储过程sp_dropdevice，该系统存储过程的使用方法如下。
sp_dropdevice [ @logicalname = ] 'device' 
    [ , [ @delfile = ] 'delfile' ]

例：删除磁盘备份设备‘B_备份设备’，其物理名称为D:\Server\MSSQL\Backup\B_备份设备.bak
EXEC  sp_dropdevice 'B_备份设备', 'DELFILE‘

例：删除名为tapedump1的磁带备份设备。
EXEC sp_dropdevice 'apedevice'

【例】完整备份教学管理数据库
BACKUP DATABASE 教学管理
TO 教学管理_FULL
WITH INIT


提示：使用INIT选项覆盖上一次的备份。 

对教学管理数据库进行差异备份，备份设备是‘教学管理_DIFF’。
backup database 教学管理
to 教学管理_DIFF
with differential

备份教学管理数据库的日志到备份设备‘教学管理_LOG’。

BACKUP LOG 教学管理
TO 教学管理_LOG
WITH INIT

【例】“教学练习”数据库包括四个数据文件：教学练习_data、教学练习_add、教学练习_data1、教学练习_data2，备份教学练习_data1、教学练习_data2数据文件到备份设备教学练习_FILE，备份事务日志教学练习_LOG到备份设备教学练习_BAKLOG。

BACKUP DATABASE 教学练习
        FILE='教学练习_data1',  
        FILE='教学练习_data2'
TO 教学练习_FILE

 BACKUP   LOG  教学练习_LOG 
 TO     教学练习_BAKLOG    
（不支持）
【例】从完整备份‘教学管理_FULL’中恢复‘教学管理’数据库。
RESTORE DATABASE 教学管理
FROM 教学管理_FULL
WITH RECOVERY 

【例】从差异备份‘教学管理_DIFF’中恢复‘教学管理’数据库。
--从差异备份中恢复需要两步操作：
--①先从完整备份恢复，使用NORECOVERY选项。
RESTORE DATABASE 教学管理
FROM 教学管理_FULL
WITH NORECOVERY
--②再从差异备份中恢复，使用RECOVERY选项。
RESTORE DATABASE 教学管理
FROM 教学管理_DIFF
WITH RECOVERY

【例】从日志备份‘教学管理_LOG’中恢复‘教学管理’数据库。
   恢复日志备份的时候，SQL Server只恢复事务日志中所记录的数据更改。恢复步骤是首先恢复完整数据库备份，如果存在差异备份，则恢复差异备份，最后恢复日志备份。
--①先从完整备份恢复，使用NORECOVERY选项。
RESTORE DATABASE 教学管理
FROM 教学管理_FULL
WITH NORECOVERY
--②再从差异备份中恢复，使用NORECOVERY选项。
RESTORE DATABASE 教学管理
FROM 教学管理_DIFF
WITH NORECOVERY
--③最后使用日志备份恢复，使用RECOVERY选项。
RESTORE LOG 教学管理
FROM 教学管理_LOG
WITH RECOVERY

【例】从文件或文件组备份‘教学练习_GROUP’和日志备份‘教学练习_GROUPLOG’中恢复‘教学练习’数据库。
  恢复步骤如下：
  ① 从文件组备份恢复，使用NORECOVERY选项。
RESTORE DATABASE 教学练习
    FILE='教学练习_data1',
    FILE='教学练习_data2'
    FROM 教学练习_GROUP
    WITH NORECOVERY
  ② 使用日志备份恢复，使用RECOVERY选项。
RESTORE LOG 教学练习
FROM 教学练习_GROUPLOG
        WITH RECOVERY