仅供参考，有错误的地方请指出
实验一：实验二：实验三：实验四：实验五：实验一：
使用Management Studio图形界面创建studentdb数据库，使用SQL语句创建spjdb。在左侧的对象资源管理器中“数据库”上点右键，新建数据库，指定数据库名studentdb和各项参数如路径等，确定。
在上方工具条中点“新建查询”，新打开窗口中输入如下SQL语句：create database spjdb
然后点“执行”。
在studentdb数据库中新建查询，使用SQL语句创建以下3个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）。注意创建完成后需要刷新当前数据库的表目录。在studentdb中创建架构Production和Person并比较区别。
create schema Production --架构命名不能以数字开头
create schema Person AUTHORIZATION st
注意: 在创建Person架构前需要使用下面的三条语句先在当前数据库中添加用户，并仅仅授予该用户建表的权限。
CREATE LOGIN st WITH PASSWORD=‘suntao123’
CREATE USER st FOR LOGIN st
GRANT create table to st
然后用户st以SQL SERVER身份验证方式登录服务器，尝试执行如下的SQL语句：
create table Person.t1(id int,name char(10)) --成功
create table Production.t1(id int,name char(10)) --失败，原因？
修改表结构，具体要求如下：
(1) 将表course的cname列的数据类型改为varchar(40).
(2) 为表student增加一个新列: birthday(出生日期), 类型为datetime, 默认为空值.
(3) 将表sc中的grade列的取值范围改为小于等于150的正数.
(4) 为Student表的“Sex”字段创建一个缺省约束，缺省值为’男’
(5)为“Sdept”字段创建一个检查约束，使得所在系必须是’CS’、’MA’或’IS’之一。
(6)为Student表的“Sname”字段增加一个唯一性约束
(7)为SC表建立外键,依赖于Student表的fk_S_c约束。
(8)禁止启用Student表的“Sdept”的CHECK约束ck_student。
分别建立以下索引(如果不能成功建立,请分析原因)
(1) 在student表的sname列上建立普通降序索引.
(2) 在course表的cname列上建立唯一索引.
(3) 在sc表的sno列上建立聚集索引.
(4) 在spj表的sno(升序), pno(升序)和jno(降序)三列上建立一个普通索引.
索引能提高查询速度，可以使用下面的实验过程验证。
在“新建查询”窗口下，复制如下全部代码，直接点“执行”，在消息窗口的最下面，可以看到有索引和无索引的查询耗时。
create database spjdb;create database studentdb;use studentdb;create table student(sno char(9) primary key not null,sname nchar(10) not null,ssex nchar(2),sage smallint,sdept char(15),check (sage >= 12));use studentdb;create table course(cnochar(4) primary key not null ,cname nchar(20),cpno char(4),ccredit smallint,);create table sc(sno char(9) not null ,cno char(4) not null ,grade decimal(5,1),foreign key (sno)references student(sno),foreign key (cno)references course(cno),check (grade between 0 and 100));use spjdb;create table S(sno char(2) primary key not null ,sname nchar(10) not null ,status smallint,city nchar(10),check (status>0));create table P(pno char(2) primary key not null ,pname nchar(10) not null ,color nchar(2),weight smallint,check (weight>0));create table J(jno char(2) primary key not null ,jname nchar(10) not null ,city nchar(10));create table SPJ(sno char(2) not null ,pno char(2) not null ,jno char(2) not null ,qty smallint,primary key (sno,pno,jno),foreign key (sno)references S(sno),foreign key (pno)references P(pno),foreign key (jno)references J(jno),check (qty>0));use studentdb;create schema Production;CREATE LOGIN stu WITH PASSWORD='Sql@123456';CREATE USER stu FOR LOGIN stu;GRANT create table to stu;create schema Person AUTHORIZATION stu;create table Person.t1(id int,name char(10));--修改表--(1)--alter table course alter cname varchar(40); mysqlalter table course alter column cname varchar(40);--(2) alter table student add birthday datetime default null;--(3) alter table scdrop constraint CK__sc__grade__2A4B4B5E;alter table scadd constraint grade_new check (grade<=150);--(4) alter table studentadd constraint ssex__new default '男' for ssex;--(5)alter table studentadd constraint ck_student check (sdept in ('CS','MA','IS'));--(6)alter table studentadd unique (sname);--(7)alter table scadd constraint fk_S_c foreign key (sno) references student(sno);--(8)alter table studentnocheck constraint ck_student;--6. --(1) create unique index sname on student(sname desc );--(2) create unique index cname on course(cname);--(3) create clustered index 索引名 on sc(sno);--(4)
实验二：
使用INSERT语句将教材P82表中的数据添加到数据库STUDENTDB中.
将教材P70表中的数据添加到数据库SPJDB中. 体会执行插入操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果.
删除student表中学号为15121的学生，体会执行删除操作时检查参照完整性规则的效果.将参照完整性中的删除规则改为“级联(层叠)”(CASCADE)，重新删除该学生信息。
更新student表中15122学生的新学号为15128，体会执行更新操作时检查参照完整性规则的效果.将参照完整性中的更新规则改为“级联”，重新更新该学生信息。
设计一组更新操作, 它需要另外一个表中的数据作为更新条件(如将选修了“信息系统”课程的成绩均提高15% )。
设计一个删除操作，它需要另外一个表中的数据作为删除条件(如将“刘晨”的选课记录删除 )。
完成P128习题4和习题5中(1)~(7)的查询。
完成以下查询：
(1)查询所有课程都及格的学生信息。
(2)统计每一门课程的最高分、最低分和平均分。
(3)从course表中查询课程名中包含“数据”二字的课程的信息。
(4)统计所有学生3号课程的平均成绩，要求某个学生3号课程成绩为NULL，如不存在空值，请先插入。
(5)查询每个学生及其选修课情况，显示学生姓名、课程名称和成绩，要求使用外连接输出所有学生和所有课程的信息。
(6)查询所有学生中平均成绩最高的那个学生的学号。
(7)查询只选修了2号课程的学生的信息，可以使用集合查询。
(8)查询所有计算机系学生的学号、选修课程号以及分数。(使用IN谓词)。
(9)查询选修了课程名为“操作系统”的学生学号和姓名。
(10)查询所有计算机系学生的学号、选修课程号以及分数（使用EXISTS谓词）。
use spjdb;create unique index sno on SPJ(sno asc,pno asc,jno desc);use studentdb;insert into student values ('15121',N'李勇',N'男',20,'CS',null);insert into student values ('15122',N'刘晨',N'女',19,'CS',null);insert into student values ('15123',N'王敏',N'女',18,'MA',null);insert into student values ('15125',N'张立',N'男',19,'IS',null);insert into course values ('1',N'数据库','5',4);insert into course values ('2',N'数学',null,2);insert into course values ('3',N'信息系统','1',4);insert into course values ('4',N'操作系统','6',3);insert into course values ('5',N'数据结构','7',4);insert into course values ('6',N'数据处理',null,2);insert into course values ('7',N'PASCAL语言','6',4);insert into sc values ('15121','1',92);insert into sc values ('15121','2',85);insert into sc values ('15121','3',88);insert into sc values ('15122','2',90);insert into sc values ('15122','3',80);use spjdb;insert into S values ('S1',N'精益',20,N'天津');insert into S values ('S2',N'盛锡',20,N'北京');insert into S values ('S3',N'东方红',20,N'北京');insert into S values ('S4',N'丰泰胜',20,N'天津');insert into S values ('S5',N'为民',20,N'上海');insert into P values ('P1',N'螺母',N'红',12);insert into P values ('P2',N'螺栓',N'绿',17);insert into P values ('P3',N'螺丝刀',N'蓝',14);insert into P values ('P4',N'螺丝刀',N'红',14);insert into P values ('P5',N'凸轮',N'蓝',40);insert into P values ('P6',N'齿轮',N'红',30);insert into J values ('J1',N'三建',N'北京');insert into J values ('J2',N'一汽',N'长春');insert into J values ('J3',N'弹簧厂',N'天津');insert into J values ('J4',N'造船厂',N'天津');insert into J values ('J5',N'机车厂',N'唐山');insert into J values ('J6',N'无线电长',N'常州');insert into J values ('J7',N'半导体厂',N'南京');insert into SPJ values ('S1','P1','J1',200);insert into SPJ values ('S1','P1','J3',100);insert into SPJ values ('S1','P1','J4',700);insert into SPJ values ('S1','P2','J2',100);insert into SPJ values ('S2','P3','J1',400);insert into SPJ values ('S2','P3','J2',200);insert into SPJ values ('S2','P3','J4',500);insert into SPJ values ('S2','P3','J5',400);insert into SPJ values ('S2','P5','J1',400);insert into SPJ values ('S2','P5','J2',100);insert into SPJ values ('S3','P1','J1',200);insert into SPJ values ('S3','P3','J1',200);insert into SPJ values ('S4','P5','J1',100);insert into SPJ values ('S4','P6','J3',300);insert into SPJ values ('S4','P6','J4',200);insert into SPJ values ('S5','P2','J4',100);insert into SPJ values ('S5','P3','J1',200);insert into SPJ values ('S5','P6','J2',200);insert into SPJ values ('S5','P6','J4',500);--3use studentdb;delete from studentwhere sno='15121';alter table sc add constraint sno_cascadeforeign key (sno) references student(sno)on delete cascade on update cascade ;delete from studentwhere sno='15121';--4update student set sno='15128' where sno='15122';--5update sc set grade=grade * 1.15where grade in(select grade from sc inner join course c on o = o where ame=N'信息系统');--6delete from sc where sno in(select student.sno from student where sname=N'刘晨');/*习题4*/--1use spjdb;select distinct sno from SPJwhere jno='J1';--2select distinct sno from SPJwhere jno='J1' and pno='P1';--3select distinct sno from SPJinner join P on P.pno = SPJ.pnowhere jno='J1' and color = N'红';--4select sno from SPJinner join J on J.jno = SPJ.jno/*习题5*/--1use spjdb;select sname,city from S;--2select pname,color,weight from P;--2select distinct jno from SPJ where sno='S1';--4select pname,qty from SPJ,p where jno='J2' and spj.pno=p.pno;--5select pno from SPJinner join S on S.sno = SPJ.snowhere city=N'上海';--6select distinct JNAMEfrom S,SPJ,Jwhere S.CITY='上海' and SPJ.SNO=S.SNO and SPJ.JNO=J.JNO;--7select distinct JNOfrom SPJwhere JNO not in(select JNOfrom SPJ,Swhere SPJ.SNO=S.SNO and S.CITY='天津');use studentdb;--8.1select * from studentwhere not exists(select * from sc where grade<60 and student.sno=sc.sno);--8.2select cno,max(grade),min(grade),avg(grade) from scgroup by cno;--8.3select * from course where cname like N'%数据%';--8.4insert into sc values ('15125','3',null);select avg(grade) from sc where cno=3;--8.5select sname,cname,grade from student full outer join sc s on student.sno = s.sno full join course c on o = o;--8.6select sno from scgroup by snohaving avg(grade)>=all(select avg(grade) from sc);--8.7select * from student wherenot exists (select * from sc where cno in(select cno from sc where cno!=2) and student.sno=sno);--8.8select student.sno,cno,grade from student inner join sc s on student.sno = s.snowhere student.sno in(select sno from student where sdept='CS');--8.9select student.sno,sname from studentinner join sc s on student.sno = s.snoinner join course c on o = owhere cname=N'操作系统';--8.10select st.sno,cno,grade from student stinner join sc s on st.sno = s.snowhere exists(select sno from student where sdept='CS' and st.sno=sno)
实验三：
1.基于实验1中建立的表，完成如下的视图操作：
(1)建立一个计算机系(CS)学生的视图COMPUTE_S，并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生；
(2) 建立一个信息系(IS)选修了’1’号课程且成绩在80分以上的学生视图IS_S1；
(3) 建立一个所有计算机系学生的学号、姓名、选修课程名以及成绩的视图COMPUTE_GRADE;
对视图进行SELECT、INSERT、UPDATE、DELETE操作
（1）通过COMPUTE_S视图插入一个新学生的信息：15127，王建，21，男。
要求插入成功后student表中该学生的系值必须为“CS”，如不是，请修改视图后重新插入。
（2）更新COMPUTE_S视图中所有男生的年龄，都增加一岁，注意观察非计算机系男生的年龄是否被修改。
（3）假如你是计算机学院的教务人员，通过COMPUTE_GRADE视图查询数据库这门课程所有不及格学生的名单，体会视图在简化用户操作过程中的作用。
（4）对COMPUTE_GRADE进行插入：15128，张杰，数据库，80。观察结果并分析失败原因。
（5）删除COMPUTE_S视图中所有学生的信息
创建数据库用户和数据库角色，使之拥有对数据库StudentDB的一定权力。
（1）展开服务器的“安全性”目录树，右击“登录名”,新建登录名test1，SQL Server身份验证，默认数据库为studentdb，用户映射到studentdb数据库，角色成员为public。使用test1用户登录服务器并验证。
（2）使用SQL语句创建用户test2，使之能够访问studentdb数据库并具有建表权限。
CREATE LOGIN test2 WITH PASSWORD=‘test123’
Use studentdb
CREATE USER test2 FOR LOGIN test2
GRANT create table to test2
（3）使用系统存储过程创建用户test3，使之能够访问studentdb数据库并具有建表权限。
–登录用户的创建
exec sp_addlogin ‘test3’, ‘test123’,‘studentdb’
–更改当前数据库
use studentdb
–赋予用户访问当前数据库的权限
exec sp_grantdbaccess ‘test3’
–角色的创建
exec sp_addrole ‘teacher’
–添加用户到角色中
exec sp_addrolemember ‘teacher’,‘test3’
–给角色授权
grant create table to teacher
授予用户李勇查看自己成绩的权限，但不能查看其它人的成绩。
提示：先创建用户李勇，然后创建视图S，通过视图S能查看学生“李勇”的成绩，最后把视图S的查询权限赋予用户李勇。
启用SQL Server的审计功能并通过审计日志进行简单的审计跟踪。
右击左侧目录树最上端的服务器——>属性——>安全性，选中“启用c2审核跟踪”。
也可以通过如下存储过程启用c2审核跟踪。
–查看系统参数配置
sp_configure
–打开系统参数的高级选项
sp_configure ‘show advanced options’,1
RECONFIGURE
–再次查看系统参数配置
sp_configure
–启用c2级别的审计功能
sp_configure ‘c2 audit mode’,1
RECONFIGURE
启用c2审核跟踪后，重新启动服务器，在C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data目录下会产生以当前时间命名的trc跟踪文件。此时可以分别尝试使用错误口令和正确口令登录服务器并进行其它操作，然后停止服务器。
查看审计日志需要使用专门的查看工具，也可以使用操作系统的事件查看器或者SQL Server的扩展存储过程sp_readerrorlog。
--1create view COMPUTE_Sas select * from student where sdept='CS';select * from COMPUTE_S;--create view IS_S1as select student.* from student inner join sc s on student.sno = s.snowhere sdept='IS' and cno=1 and grade>80;select * from IS_S1;--create view COMPUTE_GRADEas select st.sno,sname,cno,grade from student st inner join sc on st.sno = sc.snowhere sdept='CS';--2insert into COMPUTE_S values ('15127',N'王建',N'男',21,'CS',null);--update COMPUTE_S set sage=sage+1;--select * from COMPUTE_GRADE where grade<60;--insert into COMPUTE_GRADE values ('15128',N'张杰',N'数据库',80)--delete from COMPUTE_S;--3/*3(1)展开服务器的“安全性”目录树，右击“登录名”,新建登录名test1，SQL Server身份验证，默认数据库为studentdb，用户映射到studentdb数据库，角色成员为public。使用test1用户登录服务器并验证。*/--3.2CREATE LOGIN test2 WITH PASSWORD='Sql@123456'Use studentdbCREATE USER test2 FOR LOGIN test2GRANT create table to test2--3.3--登录用户的创建exec sp_addlogin 'test3', 'Sql@123456','studentdb'--更改当前数据库use studentdb--赋予用户访问当前数据库的权限exec sp_grantdbaccess 'test3'--角色的创建exec sp_addrole 'teacher'--添加用户到角色中exec sp_addrolemember 'teacher','test3'--给角色授权grant create table to teacher--4create login 李勇 with password =N'李勇'use testcreate user 李勇 for login 李勇;create view grade_liyongasselect ame,sc.gradefrom course,sc,studentwhere o=o and student.sno=sc.sno;grant select on grade_liyong to 李勇--5--查看系统参数配置sp_configure--打开系统参数的高级选项sp_configure 'show advanced options',1RECONFIGURE--再次查看系统参数配置sp_configure--启用c2级别的审计功能sp_configure 'c2 audit mode',1RECONFIGURE
实验四：
定义变量并且通过select语句给变量赋值.查询学号为“15121”的学生的姓名和年龄，将其分别赋予变量name和age。
declare @name varchar(10)
declare @age int
select @name=sname,@age=sage from student where sno=‘15121’
select @name as 姓名,@age as 年龄
运行结果如下：
姓名 年龄
李勇 31
练习：查询学号为“15121”的学生选修“1”号课程的成绩，并赋值给变量grade。
if-lese选择结构的使用
查询学生信息，如果学生人数多于10人，则只显示前5名，否则显示所有学生信息。
DECLARE @num int
SELECT @num=count(*) from student
print ‘学生人数:’+convert(varchar(5),@num)
IF (@num>10)
BEGIN
print '前五名学生信息'SELECT TOP 5 * FROM student ORDER BY sno DESC
END
ELSE
BEGIN
print '所有学生信息'SELECT * FROM student
END
练习：查询“1”号课程的平均成绩，如果平均成绩大于等于70，则显示前5名的成绩，否则显示后5名的成绩。
while循环结构的使用
创建一个测试表，并使用循环结构快速插入20000条记录。
create table TableIndex (
ID int identity(1,1),DataValue decimal(18,2))
/—向TestIndex数据库表中插入20000条数据—/
declare @r numeric(15,8)
declare @n int
set @n = 1
while(1=1)
begin
set @r = rand()insert into TableIndex (DataValue) values(@r)set @n = @n + 1if(@n>20000)break
end
练习1：检查“1”号课程是否有不及格（60分及格）的学生。如有，每人加2分，直至所有学生选“1”号课程成绩均及格。练习2：检查“1”号课程是否有不及格（60分及格）的学生。如有，每人加2分，高于95分的学生不再加分，直至所有学生“1”号课程成绩均及格。
编写带有通配符参数的存储过程，查询学生表和成绩表，返回指定姓名的学生姓名、课程名和考试成绩。要求：执行该存储过程时，如果未提供参数，则使用预设的默认值（以“张”打头的姓）
/—创建存储过程—/
CREATE PROCEDURE sp_stuInfo
@sname varchar(50)=‘张%’
AS
PRINT @sname+‘同学的考试成绩单如下：’
SELECT sname,cname,grade
FROM student,course,sc
WHERE student.sno=sc.sno and o=o
and sname LIKE @sname
练习：创建存储过程，查看指定课程及格学生的成绩信息，并返回及格的人数。要求及格分数线作为参数由用户输入，默认为60。
创建触发器，当插入交易记录时，实现自动更新账户余额的功能。建表语句如下：
create table count(
id char(3) primary key,balance int)
insert into count values(‘001’, 1000)
create table traninfo(
id char(3) foreign key references count(id),
amount int)
/—创建触发器—/
create trigger autoupdate_balance
on traninfo
for insert
as
update count set balance = balance +i.amountfrom inserted iwhere count.id=i.id练习1：删除交易信息时，要求自动备份被删除的数据到表backupTable中 。
练习2：跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。
编写事务进行银行转账。
–创建农行帐户表bank
CREATE TABLE bank(
customerName CHAR(10), --顾客姓名currentMoney int--当前余额
)
/—添加约束：根据银行规定，帐户余额不能少于1元，除非销户----/
ALTER TABLE bank
ADD CONSTRAINT CK_currentMoney CHECK(currentMoney>=1)
/–插入测试数据：张三开户，开户金额为800 ；李四开户，开户金额1 —/
INSERT INTO bank(customerName,currentMoney) VALUES(‘张三’,1000)
INSERT INTO bank(customerName,currentMoney) VALUES(‘李四’,1)
/—创建事务—/
/–开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体–/
BEGIN TRANSACTION
/–定义变量，用于累计事务执行过程中的错误–/
DECLARE @errorSum INT
SET @errorSum=0 --初始化为0，即无错误
/–转帐：张三的帐户少1000元，李四的帐户多1000元/
UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=‘张三’
SET @errorSum=@errorSum+@@error --累计是否有错误
UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=‘李四’
SET @errorSum=@errorSum+@@error --累计是否有错误
/–根据是否有错误，确定事务是提交还是撤销—/
IF @errorSum<>0 --如果有错误
BEGIN
print '交易失败，回滚事务'ROLLBACK TRANSACTION
END
ELSE
BEGIN
print '交易成功，提交事务，写入硬盘，永久的保存'COMMIT TRANSACTION
END
练习：使用第5题中的count表和traninfo表，编写事务实现存取款过程。
要求首先插入交易记录，然后更新账户余额。
--1declare @name nchar(10)declare @age intselect @name=sname,@age=sage from student where sno='15121'select @name as 姓名,@age as 年龄;--练习：查询学号为“15121”的学生选修“1”号课程的成绩，并赋值给变量grade。declare @grade decimal(5,1)select @grade=grade from sc where sno='15121' and cno=1select @grade as 成绩;--2--查询学生信息，如果学生人数多于10人，则只显示前5名，否则显示所有学生信息。DECLARE @num intSELECT @num=count(*) from studentprint N'学生人数:'+convert(varchar(5),@num)IF (@num>10)BEGINprint N'前五名学生信息'SELECT TOP 5 * FROM student ORDER BY sno DESCENDELSEBEGINprint N'所有学生信息'SELECT * FROM studentEND--练习：查询“1”号课程的平均成绩，如果平均成绩大于等于70，则显示前5名的成绩，否则显示后5名的成绩。declare @avg decimal(5,1)select @avg=avg(grade) from scprint N'平均成绩:'+convert(varchar(5),@avg)if(@avg>70)beginprint N'前5名的成绩'select * from scendelsebeginprint N'后5名的成绩'select * from scend--3create table TableIndex (ID int identity(1,1),DataValue decimal(18,2))/*---向TestIndex数据库表中插入20000条数据---*/declare @r numeric(15,8)declare @n intset @n = 1while(1=1)beginset @r = rand()insert into TableIndex (DataValue) values(@r)set @n = @n + 1if(@n>20000)breakend--练习1：检查“1”号课程是否有不及格（60分及格）的学生。如有，每人加2分，直至所有学生选“1”号课程成绩均及格。declare @grade decimal(5,1)select @grade=grade from sc where cno=1while (@grade<60)beginset @grade=@grade + 2if(@grade>=60)breakendselect * from sc where cno=1--练习2：检查“1”号课程是否有不及格（60分及格）的学生。如有，每人加2分，高于95分的学生不再加分，直至所有学生“1”号课程成绩均及格。declare @grade decimal(5,1)select @grade=grade from sc where cno=1while (@grade<60)if(@grade>95)beginset @grade=@gradeendelsebeginset @grade=@grade+2if(@grade>=60)breakendselect * from sc where cno=1--4/*---创建存储过程---*/CREATE PROCEDURE sp_stuInfo@sname varchar(50)='张%'ASprint @sname+'同学的考试成绩单如下：'select sname,cname,gradefrom student,course,scwhere student.sno=sc.sno and o=oand sname like @sname--练习：创建存储过程，查看指定课程及格学生的成绩信息，并返回及格的人数。要求及格分数线作为参数由用户输入，默认为60。CREATE PROCEDURE sp_stuGradInfo @cno varchar(2),@gradeLine int=60AS declare @num intbeginselect *from sc where cno=@cno and grade>=@gradeLineselect @num=COUNT(*) from sc where cno=@cno and grade>=@gradeLineselect @num as 一号课程及格人数end--5. 创建触发器，当插入交易记录时，实现自动更新账户余额的功能。建表语句如下：create database count;use count;create table count(id char(3) primary key,balance int)insert into count values('001', 1000)create table traninfo(id char(3) foreign key references count(id),amount int)/*---创建触发器---*/create trigger autoupdate_balanceon traninfofor insertasupdate count set balance = balance +i.amountfrom inserted iwhere count.id=i.id--练习1：删除交易信息时，要求自动备份被删除的数据到表backupTable中 。create trigger autodelete_traninfoon traninfofor deleteasif not exists(select * from sysobjects where name='backupTable')select * into backupTable from deletedelseinsert into backupTableselect * from deletedselect * from backupTable--练习2：跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。create trigger trig_update_tranON traninfofor updateasdeclare @beforeMoney money,@afterMoney money/*从deleted表中获取交易前的余额，从inserted表中获取交易后的余额*/select @beforeMoney=current money from deletedselect @afterMoney=current money from insertedif ABS(@afterMoney-@beforeMoney)>20000/*交易金额是否>2万*/beginprint '交易金额:'+convert(varchar(8),ABS(@afterMoney-@beforeMoney))raiserror ('交易失败',16,1)rollback transaction /*回滚事务，撤销交易*/end--6--创建农行帐户表bankCREATE TABLE bank(customerName CHAR(10), --顾客姓名currentMoney int--当前余额)--添加约束：根据银行规定，帐户余额不能少于1元，除非销户ALTER TABLE bankADD CONSTRAINT CK_currentMoney CHECK(currentMoney>=1)/*--插入测试数据：张三开户，开户金额为800 ；李四开户，开户金额1 ---*/INSERT INTO bank(customerName,currentMoney) VALUES('张三',1000)INSERT INTO bank(customerName,currentMoney) VALUES('李四',1)/*---创建事务---*//*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体--*/BEGIN TRANSACTION/*--定义变量，用于累计事务执行过程中的错误--*/DECLARE @errorSum INTSET @errorSum=0 --初始化为0，即无错误/*--转帐：张三的帐户少1000元，李四的帐户多1000元*/UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三'SET @errorSum=@errorSum+@@error --累计是否有错误UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四'SET @errorSum=@errorSum+@@error --累计是否有错误/*--根据是否有错误，确定事务是提交还是撤销---*/IF @errorSum<>0 --如果有错误BEGINprint '交易失败，回滚事务'ROLLBACK TRANSACTIONENDELSEBEGINprint '交易成功，提交事务，写入硬盘，永久的保存'COMMIT TRANSACTIONEND--练习：使用第5题中的count表和traninfo表，编写事务实现存取款过程。insert into count values (001,2340);insert into count values (002,7000);insert into traninfo values (001,400);insert into traninfo values (002,-700);create procedure transfor @outAccount char(3),@inAccount char(3),@amount floatasbegin transactiondeclare @errorSum intset @errorSum = 0update count set balance=balance-@amountwhere id=@outAccountset @errorSum=@errorSum+@@errorupdate count set balance= balance-@amountwhere id = @inAccountset @errorSum=@errorSum+@@errorif (@errorSum<>0)beginprint '交易失败，回滚事务'rollbackendelsebeginprint '交易成功，提交事务，写入硬盘，永久的保存'commitend
实验五：
CREATE TABLE sales(id char(5),qty int,b int);insert into sales values( 'A0001', 0,10 );--定义如下事务，运行结束前，停止SQL SERVER服务，然后重启服务查看qty和b的值是否有变化。begin tranupdate sales set qty = qty - 10 where id = 'A0001'waitfor delay '00:00:30.000'update sales set b = b + 10 where id = 'A0001'commit tran--定义如下SQL语句，运行结束前，停止SQL SERVER服务，然后重启服务查看qty和b的值是否有变化。update sales set qty = qty - 10 where id = 'A0001'waitfor delay '00:00:30.000'update sales set b = b + 10 where id = 'A0001'declare @sl int--2.1丢失修改--例1：--事务一和事务二代码相同，如下begin transelect @sl = qty from sales where id = 'A0001'waitfor delay '00:00:30.000'update sales set qty = @sl - 1 where id = 'A0001'commit tran--执行结果：qty==1，事务一的修改丢失。--原因：事务执行过程中对操作的数据没有加锁。--2.2例2：--丢失修改--事务一代码declare @i intdeclare @sl intselect @i = 1while( @i <= 2000) beginbegin transelect @sl = qty from sales where id = 'A0001'waitfor delay '00:00:00.002'update sales set qty = @sl + 1 where id = 'A0001'commit transelect @i = @i + 1end--事物二代码declare @i intdeclare @sl intselect @i = 1while( @i <= 2000) beginbegin transelect @sl = qty from sales where id = 'A0001'waitfor delay '00:00:00.002'update sales set qty = @sl - 1 where id = 'A0001'commit transelect @i = @i + 1end--4. 不可重复读--例1:--事务一代码如下declare @qty intdeclare @b intbegin transelect @qty = qty from sales where id = 'A0001'select @b = b from sales where id = 'A0001'print(@qty+@b)waitfor delay '00:00:5.000'select @qty = qty from sales where id = 'A0001'select @b = b from sales where id = 'A0001'print(@qty+@b)commit tran--事务二代码declare @b intbegin transelect @b = b from sales where id = 'A0001'update sales set b = @b *2 where id = 'A0001'commit tran--例2：--不可重复读--事务一代码如下declare @i intdeclare @sl intdeclare @s2 intselect @i = 1while( @i <= 2000) beginbegin transelect @sl = qty from sales where id = 'A0001'waitfor delay '00:00:00.002'select @s2 = qty from sales where id = 'A0001'if(@sl<>@s2) print('non-repeatable read')commit transelect @i = @i + 1end--事务二代码declare @i intdeclare @sl intselect @i = 1while( @i <= 2000) beginbegin transelect @sl = qty from sales where id = 'A0001'waitfor delay '00:00:00.002'update sales set qty = @sl + 1 where id = 'A0001'commit transelect @i = @i + 1end--5. 读脏数据--事务一代码SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDdeclare @b intbegin transelect @b = b from sales where id = 'A0001'print(@b)update sales set b = @b *2 where id = 'A0001'select @b = b from sales where id = 'A0001'print(@b)waitfor delay '00:00:20.000'rollback--事务二代码SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDdeclare @b intbegin transelect @b = b from sales where id = 'A0001'print(@b)commit tran--6. 加锁实验--(1) 一级封锁协议不会丢失修改，但不能保证不出现另两种异常丢失修改.declare @sl intbegin transelect @sl = qty from sales with (xlock) where id = 'A0001'waitfor delay '00:00:10.000'update sales set qty = @sl - 1 where id = 'A0001'commit tran--不可重复读--事务一declare @qty intdeclare @b intbegin transelect @qty = qty from sales where id = 'A0001'select @b = b from sales where id = 'A0001'print(@qty+@b)waitfor delay '00:00:15.000'select @qty = qty from sales where id = 'A0001'select @b = b from sales where id = 'A0001'print(@qty+@b)commit tran--事务二declare @b intbegin transelect @b = b from sales with(xlock) where id = 'A0001'update sales set b = @b *2 where id = 'A0001'commit tran--读脏数据--事务一SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDdeclare @b intbegin transelect @b = b from sales with (xlock) where id = 'A0001'print(@b)update sales set b = @b *2 where id = 'A0001'select @b = b from sales where id = 'A0001'print(@b)waitfor delay '00:00:20.000'rollback--事务二SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDdeclare @b intbegin transelect @b = b from sales where id = 'A0001'print(@b)commit tran--(2) 二级封锁协议可以避免丢失修改和读脏，但不能保证可重复读。在sql server中默认设置即可避免读脏。--不可重复读--事务一declare @qty intbegin transelect @qty = qty from sales where id = 'A0001'select @b = b from sales where id = 'A0001'print(@qty+@b)waitfor delay '00:00:15.000'select @qty = qty from sales where id = 'A0001'select @b = b from sales where id = 'A0001'print(@qty+@b)commit tran--事务二declare @b intbegin transelect @b = b from sales with(xlock) where id = 'A0001'update sales set b = @b *2 where id = 'A0001'commit tran--(3) 三级封锁协议可以完全避免这三种异常.--可重复读--事务一declare @qty intdeclare @b intbegin transelect @qty = qty from sales with(holdlock) where id = 'A0001'select @b = b from sales with(holdlock) where id = 'A0001'print(@qty+@b)waitfor delay '00:00:15.000'select @qty = qty from sales where id = 'A0001'select @b = b from sales where id = 'A0001'print(@qty+@b)commit tran--事务二declare @b intbegin transelect @b = b from sales with(xlock) where id = 'A0001'update sales set b = @b *2 where id = 'A0001'commit tran
