–三、实验步骤:
–1、事务操作
–在选修数据库中,以Student(sno,sname,ssex,sage,sdept),Course(cno,cname,cpno,credit),SC(sno,cno,grade)表创建下列事务。
–(1)将学生“吴敏”的“计算机基础”的课程成绩改为77分。
DECLARE @sno char(9),@cno char(5)
select @sno=sno from student where sname='吴敏'
select @cno=cno from course where cname='计算机基础'
BEGIN TRANSACTION
UPDATE SC
SET grade=77
WHERE sno =@sno and cno=@cno
COMMIT TRAN ;
select sc.*,s.sname from sc left join student s on s.sno=sc.sno;
–(2)将课程“数据结构”和“计算机基础”的课程号互换。
DECLARE @cno1 char(5),@cno2 char(5)
select @cno1=cno from course where cname='数据结构'
select @cno2=cno from course where cname='计算机基础'
BEGIN TRANSACTION
UPDATE Course
SET cname='计算机基础'
WHERE cno=@cno1
UPDATE Course
SET cname='数据结构'
WHERE cno=@cno2
COMMIT TRAN;
select *from course;
–(3)将学生“吴敏”选修的“计算机基础”课程转给“李勇”,“数据结构”转给“王敏”。
DECLARE @cno11 char(5),@cno22 char(5) ,@grade1 int,@grade2 int,@sno1 char(9),@sno2 char(9),@sno3 char(9)
select @sno1=sno from student where sname='吴敏'
select @sno2=sno from student where sname='李勇'
select @sno3=sno from student where sname='王敏'
select @cno11=cno from course where cname='数据结构'
select @cno22=cno from course where cname='计算机基础'
select @grade1=grade from sc where cno=@cno11
select @grade2=grade from sc where cno=@cno22
BEGIN TRANSACTION
delete sc where sno=@sno1 and cno in(@cno11,@cno22)
insert into sc values(@sno2,@cno22,@grade2)
insert into sc values(@sno1,@cno11,@grade1)
COMMIT TRAN;
select sc.*,s.sname,c.cname from sc ,course c, student s where s.sno=sc.sno and c.cno=sc.cno;
–2、安全管理
–(1)创建一个Windows认证的登录账户newuser,并定义数据库用户new_user,允许该用户对选修数据库进行查询。
exec sp_addlogin newuser,'123456';
exec sp_grantdbaccess newuser,new_user ;
grant select on student to new_user;
grant select any table to new_user; -- 所有表的select 权限赋予user
–(2)创建一个Windows认证的登录账户student,并定义数据库用户student_user,设置允许该用户对选修数据库进行查询,对SC的grade列进行插入、修改和删除。
exec sp_addlogin student,'123456';
exec sp_grantdbaccess student,student_user ;
grant select on sc to student_user;
grant insert,update,delete on sc(grade) to student_user;
–3、数据库的备份与恢复
–(1)对学生选修数据库进行完整备份、差异备份、事务日志备份和文件组备份。
– mydb2是数据库名
--完整备份
BACKUP DATABASE mydb2 TO disk='D:\sc\sc_backup.bak';
--差异备份
BACKUP DATABASE mydb2 TO disk='D:\sc\firstbackup' with differential,noinit ;
--事务日志备份
BACKUP LOG mydb2 TO disk='D:\sc\firstbackup' with noinit ;
--文件组备份
BACKUP DATABASE mydb2 FILEGROUP='文件组名' TO disk='firstbackup' with noinit ;