实验三 数据完整性、存储过程与触发器、
数据库备份与恢复
1、 数据完整性 （1） 实体完整性
在数据库中建立表S，进行主键约束，在没有违反实体完整性的前提下插入并更新一条记录。
CREATE TABLE S
(Sno CHAR(8) NOT NULL UNIQUE,
Sname CHAR(8), Ssex CHAR(1), Sage INT, Sdept CHAR(20), CONSTRAINT PK_S PRIMARY KEY(Sno)); INSERT S VALUES('S01','王兵','M',23,'CS'); SELECT * FROM S;
【练习1】执行下列语句，会发生什么？为什么？给出答案。 INSERT S VALUES ('S01','黄山','M',23,'CS')
违反了PRIMARY KEY 约束'PK_S1'。不能在对象'dbo.S1' 中插入重复键。
Sno 是主键不能有俩个重复值
【练习2】执行下列语句，会发生什么？为什么？给出答案。 UPDATE S SET Sno=NULL WHERE Sno='S02';
0行受影响 无S02
（2） 参照完整性
建立表C，令Cno为其主键，并在表S中插入两条记录。 CREATE TABLE C
(Cno CHAR(4) NOT NULL UNIQUE, Cname VARCHAR(50) NOT NULL,
Cpoints INT, CONSTRAINT PK PRIMARY KEY(Cno)); INSERT C VALUES('C01','ComputerNetworks',2); INSERT C VALUES('C02','ArtificialIntelligence',3);
建立表SC，令Sno和Cno分别为参照S表以及C表的外键，设定为“级联删除”，并令(Sno,Cno)为其主键。在不违反参照完整性的前提下，插入4条选课记录。 CREATE Table SC (Sno CHAR(8), Cno CHAR(4), Scredit INT,
CONSTRAINT PK_SC PRIMARY KEY(Sno,Cno),
CONSTRAINT FK_SC_Sno FOREIGN KEY(Sno) REFERENCES S (Sno) ON DELETE CASCADE,
CONSTRAINT FK_SC_Cno FOREIGN KEY(Cno) REFERENCES C(Cno) ON DELETE CASCADE);
INSERT INTO SC VALUES('S02','C01',2); INSERT INTO SC VALUES ('S02','C02',2); INSERT INTO SC VALUES ('S01','C01',2); INSERT INTO SC VALUES ('S01','C02',2); SELECT * FROM SC;
【练习3】执行下列语句，会发生什么？为什么？给出答案。 INSERT INTO SC VALUES('S99','C99',2); 插不进去
S与c表中无s99与c99
【练习4】用SQL命令在S中删除Sno='S01'的学生记录，并查看SC表中数据，看看发生了什么变化？为什么？给出答案。 Sc中关于s01信息都删除啦 因为级联删除
（3）用户自定义完整性
【练习5】创建Teacher表，并自定义2个约束U1以及U2，其中U1规定Tname字段值唯一，U2规定Tage (级别)字段的上限是28。给出代码截屏。
CREATE TABLE Teacher (Tno CHAR(5),
Tname CHAR(8) CONSTRAINT U1 UNIQUE, Tsex CHAR(1),
Tage INT CONSTRAINT U2 CHECK (Tage<=28),
Tdept CHAR(20),
CONSTRAINT PK_Teacher PRIMARY KEY(Tno))
【练习6】在Teacher表中插入一条记录(‘T02’,’王勇’,’M’,38,’ 后勤部’)，会发生什么？为什么？给出答案。
插不进去 Tage<=28不符合用户自定义完整性
【练习7】通过管理工具或alter table命令去除U2约束后，重新插入记录(‘T02’,’王勇’,’M’,38,’ 后勤部’)，会发生什么？ 可以插了
除了使用SQL语言，利用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性以外，我们还可以通过创建规则的方式保证用户完整性的实现。例如下面一段代码：
CREATE RULE Rule_sex AS @Value IN ('F','M') Go
EXEC SP_bindrule Rule_sex, 'Teacher.[Tsex]';
实现功能：创建规则Rule_sex，规定插入或更新的值只能是M或F，并绑定到Teacher的Tsex字段。 【练习8】在创建规则Rule_sex后，试在Teacher表中插入一条记录(‘T03’,’黄号’,’1’,’25’,’ 后勤部’)，看看会发生什么？为什么？请给出答案。 插不进去默认为f与m, 1插不进去 2、 存储过程与触发器 （1） 存储过程
掌握存储过程的基本操作包括创建存储过程、执行存储过程、修改和删除存储过程等。 例1：创建名为P_student1的存储过程，查看所有学生的基本信息。 Create procedure P_student1 As
Select * from Students
再执行该存储过程exec P_student1，即可查看到Students表中所有信息。 例2：Create procedure P_student2
@sex varchar(10) /*定义输入参量@sex */ As
Select * from Students where Ssex=@sex
这样可以根据用户传递进来的sex参数，来确定要查询男生信息还是女生信息。当然在调用该存储过程时，需要给出参数@sex的具体值。如：Exec P_student2 @sex=’男’
请创建以上两例存储过程，并调用它们，查看执行结果。
【练习9】创建一个存储过程student_grade，查询数据库中每个学生每门功课的成绩，要求查询结果中反映出学生学号、姓名、课程名及成绩。创建好后调用student_grade查看结果，并将命令及结果截屏。
Create procedure student_grade as
select s.sno,s.sname,cname,grade from s,R,c where
s.sno=R.sno and c.cno=R.cno
例3：按系别和选修课程查询，其中选修课程设置默认查找值为“数据库原理”。 Create proc P_student3
@department varchar(10),@cname varchar(20)='数据库原理' /*定义两个输入参量*/ As
Select Students.Sno,Students.Sname,Students.Sdept,Students.Ssex,Courses.Cname from Strdents,Reports,Courses
where Students.Sno=Reports.Sno and Reports.Cno=Courses.Cno and Courses.Cname=@cname and Students.Sdept=@department
然后exec P_student3 @department=’计算机’就会显示所有计算机系选修数据库原理的学生相 果，如exec P_student3 @department=’计算机’,@cname=’程序设计语言’
【练习10】请执行存储过程P_student3，查看计算机系选修数据库原理的学生情况，以及计算机系选修程序设计语言的学生情况，并将结果分别进行截图。 Create proc P_student3
@speiality varchar(20)='计算机', @cname varchar(30)='数据库原理'
As
Select s.sno,s.sname,s.sex,c.cname from s,R,c
where s.sno=R.sno and R.cno=c.cno and c.cname=@cname and s.speiality=@speiality
**【练习11】创建名为P_student4的存储过程，要求根据用户提供的姓名，查询某学生的平均成绩。（提示：本例需要一个输入参量（传入用户提供的学生姓名）和一个输出参量（输出学生平均成绩））。 Create proc P_student4 @sname varchar(20) as
select avg(grade) from R
group by sno having sno in (
select sno from s
where sname=@sname )
**【练习12】请执行下列语句，并解释其执行结果的含义。 Create proc P_student5 As
Select * from S
exec P_student2 @sex=’男’ exec P_student5;
注：嵌套存储过程最多可以嵌套32层，可以通过NESTLEVEL返回存储过程的级数。如在
练习13中P_student5的级数是2，P_student2的级数是1。将P_student2和P_student5的创建命令都修改一下，让它们可以自动显示自己的级数。 Alter procedure P_student2 @sex varchar(10) As
Select * from S where 性别=@sex Print @@NESTLEVEL 以及：
Alter proc P_student5 As
Select * from S
exec P_student2 @sex=’男’ /*先执行了P_student2中的Print @@NESTLEVEL */ Print @@NESTLEVEL /*再执行P_student5中的Print @@NESTLEVEL */
这时再exec P_student5，返回的两层结果与刚才一致，但是在“消息”栏中会显示2,1分别表示P_student2与P_student5的层次（因为是P_student2中的Print @@NESTLEVEL先执行）。 （2） 触发器
在数据库中创建表Teacher00，表结构及内容如下表所示： Tno char(3),主码 T01 T02 T03 T04 T05 T06 T07 Tname char(8) 吴用 吴碧燕 张颖 张宁雅 叶烨 杨澜 程媛媛 Tsex char(1) F M F F M F F Tage INT 26 25 34 44 36 38 28 Tdept char(20) 计算机 计算机 计算机 自动化 自动化 自动化 自动化 职务 char(20) 系主任 办公室主任 例4：为Teacher00表建立触发器T1，当插入或使更新表中的数据时，保证所操作的纪录的Tage值大于等于22。
CREATE TRIGGER T1 ON Teacher00 FOR INSERT,UPDATE AS
IF(SELECT Tage FROM INSERTED)<22 BEGIN
PRINT ‘职工年龄必须是大于等于22的整数! 操作失败！’ ROLLBACK TRANSACTION END
【练习13】为Teacher00表建立触发器T2，禁止删除编号为T01的系主任，并提示“此人是系主任! 删除操作失败!”
CREATE TRIGGER T1 ON Teacher FOR delete AS
IF(SELECT Tno FROM deleted)='T01' BEGIN
PRINT '此人是系主任! 删除操作失败！' ROLLBACK TRANSACTION END
【练习14】Teacher00表中的人员的编号是唯一且不可更变的，创建触发器T3实现更新中编号的不可改变性。
CREATE TRIGGER T3 ON Teacher FOR update,insert AS
IF update(Tno) BEGIN
PRINT '人员的编号是唯一且不可更变的！' ROLLBACK TRANSACTION END
【练习15】在Teacher00表中插入记录(‘T08’,’ 李宏’,’F’,20,’开发部’,null)，看看会发生什么？为什么？给出答案。
职工年龄必须是大于等于22的整数! 操作失败! 消息3609，级别16，状态1，第1 行 事务在触发器中结束。批处理已中止。 李宏Sage<22
【练习16】将教师“吴用”的记录删除，看看会发生什么？为什么？给出答案。
此人是系主任! 删除操作失败！ 吴用是系主任(T01)
【练习17】将教师“杨澜”的编号改为T09，看看会发生什么？为什么？给出答案。
人员的编号是唯一且不可更变的！ 触发器T3
【练习18】当修改某学生学号时，选课表中对应的选课记录也做相应的修改，创建触发器T4实现该功能。命令实现界面截图于此。 create trigger T4 on s for update as
update R
set sno=(select sno from inserted) where sno=(select sno from deleted) Go
3、数据库备份与恢复
右键数据库—属性—选项，在恢复模式中，可选择需要的恢复模式。
备份数据库会产生备份文件，备份文件需要有介质来存放它，就是需要的备份设备。备份设备可分为三种：磁盘备份设备，磁带备份设备，逻辑备份设备。
服务器对象—备份设备—新建备份设备，在设备名称中输入设备名，如myfirstdisk，给它选定一个存放位置或使用系统默认存放位置（即，定位数据库文件。推荐同学自己给定一个存放位置，便于查找）。再给出一个文件名（注意，不要忘记在文件名后写上.bak的文件类型后缀，不然无法正确恢复），这样在备份设备下就可以找到该设备了。
若只是把数据库备份为一个文件，操作过程如下：
数据库名上击右键—任务—备份—目标—备份到磁盘—添加—磁盘上的目标选择“文件
名”—选择存放备份文件的位置—给出文件名，如mydatabase.bak—在“选项”卡中做相应选择—确定。
将原有数据库@@@删除，创建新的数据库000，此000中当然没有任何数据库对象，是个空数据库。想要在000种恢复数据库@@@中的表及其他数据库对象，数据库名点击右键—任务—还原—数据库—选择源设备—找到刚才创建的数据库备份文件--在“选项”卡中做相应选择，点击确认即可还原数据库。 创建一个完整备份
右击数据库—任务—备份，备份类型选择完整，备份组件选择“数据库”，备份集过期时间选择晚于0天，即永不过期。在目标中选择“磁盘”，“删除”系统推荐的默认存放位置，选择“添加”，在“选择备份目标”中，选择“备份设备”（不选择文件，是因为，每备份一次都产生一个文件，重新给一个文件名。如果用备份设备就不用这么麻烦了。但是大家是要把数据库拷贝走，所以还是应该以备份文件的形式存储）。
在将“常规”选项配置好以后，再进入“选项”，覆盖媒体，如果我们要创建完整备份，那么选择“覆盖所有现有备份集”；如果是其他种类的备份，就选择“追加到现有备份集”。在“可靠性”中选择“完成后验证备份”和“出错时继续”；在“压缩”中选择“使用默认服务器设置”。压缩是SQL Server2008提供的新增功能，因为备份文件可能非常大，这时启用备份压缩，可以节省备份的时间和空间。
这时备份文件就已经创建完成，通过查看“服务器对象”中的“备份设备”—myfirstdisk右键属性，“媒体内容”中即可看到刚刚创建的完整的数据库备份。 创建其他备份
当数据库文件很大时，完整备份将会使用很多的时间和空间。
可以每月进行一次完整备份，每周进行一次差异备份。备份过程与完整备份是一样的，只不过在备份类型中选择“差异”。同样可以备份到刚才创建的备份设备myfirstdisk中。覆盖媒体处选择“追加到现有备份集”，可靠性依旧选择“完成后验证备份”和“出错时继续”。
注意：差异备份的前提是，已经进行了完整备份，不然不能进行差异备份。如1月1号进行过完整备份，则1月5号可以进行差异备份。
若只想备份1月4号至1月5号的内容，则可以进行“事务日志”备份，过程同差异备份。在“事务日志”中选择“截断事务日志”。
如果是进行文件和文件组备份，则备份组件选择“文件和文件组”，选择需要备份的文件组，选择“追加到现有备份集”，选择“完成后验证备份”和“出错时继续”，完成备份。 这时在myfirstdisk中就存在了4个不同的备份文件，分别是完整、差异、日志、文件组四种类型的备份文件。
【练习19】利用可视化方法，在SSMS中备份和还原数据库：（1）用创建一个备份设备；（2）完成某个数据库的备份；（3）还原该数据库。
**【练习20】使用SQL命令（1）用创建一个备份设备；（2）完成某个数据库的备份；（3）还原该数据库。
带有**的题目表示附加题，有能力的同学可以选做，不作为考核内容。