SQL-Server实验讲义
fetch next from cur1 into @no , @name end
close cur1
dealLocate cur1 select * from #tmp drop table #tmp
--执行
usp_grade 'JSJ'
7 编写存储过程，统计男女生1001，1002，1003各自的选修人数，输出格式如下：
性别 1001人数 1002人数 1003人数 小计 男 3 5 2 10 女 2 4 1 7 合计 5 9 3 17
（数据为示意数据）
create procedure usp_tj as
create table #tmp (ssex char(2), rs1 int,rs2 int ,rs3 int ,xj int ) declare @nRs1 int , @nRs2 int, @nRs3 int
select @nRs1 = count(*) from student,sc where cno=’1001’and ssex=’男’ select @nRs2 = count(*) from student,sc where cno=’1002’and ssex=’男’ select @nRs3 = count(*) from student,sc where cno=’1003’and ssex=’男’ insert into #tmp(ssex,rs1,rs2,rs3,xj)
values (‘男’,@nRs1,@nRs2,@nRs3, @nRs1+@nRs2+@nRs3)
select @nRs1 = count(*) from student,sc where cno=’1001’and ssex=’女’ select @nRs2 = count(*) from student,sc where cno=’1002’and ssex=’女’ select @nRs3 = count(*) from student,sc where cno=’1003’and ssex=’女’ insert into #tmp(ssex,rs1,rs2,rs3,xj)
values (‘女’,@nRs1,@nRs2,@nRs3, @nRs1+@nRs2+@nRs3)
select * from #tmp drop table #tmp return
8 编写一个存储过程，利用存储过程的参数返回数据库服务器上的日期时间。
思考：何时需要存储过程？
试验十二 触发器
目的: 了解触发器的机制及编程设计、使用
一 建立学生表的触发器 usp_addstudent，当增加学生时，SX系的学生不能超过30岁。
21
SQL-Server实验讲义
1 写出触发器
2 执行下列语句块： begin tran
insert into student (sno,sname,ssex,sage,sdept)
values (‘0701’,’刘欢’,’男’,26,’SX’)
if @@error=0 commit else
rollback
end
观察该学生是否加入到 student
3执行下列语句块： begin tran
insert into student (sno,sname,ssex,sage,sdept) values (?0702?,?赵欢?,?男?,31,?SX?) if @@error=0 commit else
rollback
end
观察该学生是否加入到 student
二 实现下列触发器
1 不能删除年龄大于25岁的学生记录。 create trigger utr_student1 on student for delete as
declare @nCnt int --存储被删除的大于25岁的人数 select @nCnt = count(*) from deleted where sage>25 if @nCnt>0 begin
raiserror('不能删除大于25岁的学生',16,10) rollback transaction end
--测试
insert into student values ('8701','aa1','男',27,'JSJ') --不能被删除 insert into student values ('8702','bb1','男',24,'JSJ') --能删除
select * from student where sno in ('8701','8702') delete from student where sno='8701'
select * from student where sno in ('8701','8702') delete from student where sno='8702'
2 建立触发器 usp_delcourse , 使课程表中1001，1002，1003 三门课不会被删除。 注意如何调试。
create trigger utr_deleteCourse on course for delete as declare @nCnt int
select @nCnt = count(*) from deleted where cno in (‘1001’,’1002’,’1003’) if @nCnt>0 begin
22
SQL-Server实验讲义
raiserror('不能删除',16,10) rollback transaction end return
调试：
Delete from course where cno=’1001’ --不会被删除
Delete from course where cno=’1006’ --能被删除
3 对学生表建立一触发器，使更改后的年龄只能比原值大
create trigger utr_student_update1 on student for update as if not update(sage) return
declare @nCnt int
select @nCnt = count(*) from inserted ,deleted
where deleted.sno=inserted.sno and inserted.sage
raiserror('更改后的年龄比原值小了',16,10) rollback transaction end
4对sc表建立触发器，使‘JSJ’系的学生不可选择 ‘1004’号课程 create trigger utr_choose on sc for insert as
declare @nCnt int --存储被删除的大于25岁的人数 select @nCnt = count(*) from inserted ,student
where student.sno=inserted.sno and sdept='JSJ' and inserted.cno='1004' --inserted 存储insert 命令添加的数据 如 0001,1004,90 if @nCnt>0 begin
raiserror('JSJ不可选择 1004',16,10) rollback transaction end
--测试
insert into student values ('8701','aa1','男',27,'JSJ')
insert into sc(sno,cno,grade) values ('8701','1001',90) --可以
insert into sc(sno,cno,grade) values ('8701','1004',90) --不可以
select * from sc where sno='8701'
5 对表 course 建触发器，实现级联删除的功能，但某课选修人数大于3则不能删除。
(先删除 sc 表对course 的外码)
*三 建立一个触发器，使对sc表成绩的修改自动记录修改日志。
日志文件表(tablog)记录如下：
用户名 学号 课程号 原成绩 修改后成绩 更改日期
23
SQL-Server实验讲义
四 在School数据库中建立一个试验用的发票表bill，然后为发票bill建立触发器 utr_money ,实现当输入单价和数量后，自动填写金额，即发票金额不输入，由单价、数量相乘后自动填写到金额中。
Create table bill(
billID char(8), --发票编号 date datetime, --开票日期 product char(10), --产品编号 price int , --单价 qty int , --数量 charge int , --金额 primary key (billid) )
思考: 触发器中 inserted , deleted 表的作用? 在触发器中如没有用到此两个表中的任何一个,你认为触发器还有意义吗?
24
SQL-Server实验讲义
试验十二 恢复技术
目的：1 掌握数据库的备份及恢复的方法。
2 了解备份方案的设定
一 完全备份的建立与恢复 1建立完全备份 USE school GO
BACKUP DATABASE school TO DISK=’C:\\schooldata.bak’
2查看备份文件中的信息
RESTORE FILELISTONLY FROM DISK=’c:\\schooldata.bak’ RESTORE HEADERONLY FROM DISK=’c:\\schooldata.bak’
3恢复完全备份
1) 先删除数据库 School USE Master GO
DROP DATABASE school 2) 然后恢复.
RESTORE DATABASE school from DISK=’c:\\schooldata.bak’ 3): 查看 school 的student 中的数据
二 建立差异备份 1 建立备份
1） 制作数据文件备份 schoolDiff.bak
2） 把学号 7001， 姓名：王海，性别：男，年龄为23 的学生加入student 3） 制作school 的差异备份 ，存入schoolDiff.bak
BACKUP DATABASE school TO DISK=?schoolDiff.bak? WITH DIFFERENTIAL
4) 把学号 7002， 姓名：赵燕，性别：女,年龄为22 的学生加入student 5) 制作school 的差异备份 ，存入schoolDiff.bak
BACKUP DATABASE school TO DISK=?schoolDiff.bak? WITH DIFFERENTIAL
2查看备份文件 schoolDiff.bak 中的信息 3 删除 school 数据库
4 恢复数据库 school 到第2步状态
RESTORE DATABASE school from DISK=’c:\\schoolDiff.bak’WITH file=1 NORECOVERY RESTORE DATABASE school from DISK=’c:\\schoolDiff.bak’WITH file=2
Select * from student 观察student 数据
5 恢复数据库 school 到最新状态
RESTORE DATABASE school from DISK=’c:\\schoolDiff.bak’WITH file=1 NORECOVERY RESTORE DATABASE school from DISK=’c:\\schoolDiff.bak’WITH file=3
Select * from student 观察student 数据
25
