高级数据库 T-SQL 事务索引和视图

1、实验四 事务、索引和视图1. 实验目的掌握事务、视图和索引的使用方法。2. 实验内容（1）银行转帐模仿教案，创建帐户表（加一个属性：卡号），再创建交易记录表（至少包含卡号，交易类型，交易金额，交易时间），实现转账事务，当从一个帐户转出金额时，记入交易记录表（支取），同理，往另一个帐户写入金额时，也记入交易记录表（存入）。要求：每个帐户金额最少不得低于1元，交易时间就是当前的日期时间。现实现从一个帐户向另一帐户转账1000元的情形。if exists(select * from sysobjects where name='users')drop table userscreat

2、e table users(sname char(10), -用户名smoney money, -余额sno int primary key-卡号)goalter table users add constraint smoney check(smoney>=1)insert into users values('李燕',3345,20070201)insert into users values('张立',4345,20070202)if exists(select * from sysobjects where name='bank')

3、drop table bankcreate table bank(sno int , -卡号b_money money, -交易金额date datetime, -交易时间b_type char(5), -交易类型foreign key(sno) references users(sno)begin transaction/*-定义变量，用于累计事务执行过程中的错误-*/declare errorsum intset errorsum=0update users set smoney=smoney-1000 where sno=20070201set errorsum=errorsum+err

4、orupdate users set smoney=smoney+1000 where sno=20070202set errorsum=errorsum+error-累计是否有错insert into bank(sno,b_money,date,b_type)values(20070201,1000,getdate(),'转出')insert into bank(sno,b_money,date,b_type)values(20070202,1000,getdate(),'转入')if errorsum<>0 -转账有错beginprint 

5、9;交易失败，事务回滚'rollback transactionendelsebeginprint '交易成功'select 姓名=sname, 卡号=users.sno,余额=smoney,交易时间=getdate(),交易类型=b_type from users left join bank on users.sno=bank.snocommit transactionend交易成功姓名 卡号 余额 交易时间 交易类型- - - - -李燕 20070201 2345.00 2010-10-22 17:08:57.293 转出李燕 20070201 2345.00

6、2010-10-22 17:08:57.293 转出张立 20070202 5345.00 2010-10-22 17:08:57.293 转入张立 20070202 5345.00 2010-10-22 17:08:57.293 转入（2）ATM机存取款模拟模拟实现ATM机的存、取款业务。取款步骤：1）向交易信息表中插入交易信息（交易类型为“支取”）；2）更新帐户余额。 存款步骤：1）向交易信息表中插入交易信息（交易类型为“存入”）；2）更新帐户余额。 请修改取款的金额，分别完成交易失败和交易成功的测试。-取款步骤begin transactiondeclare errorsum intse

7、t errorsum=0declare sno1 intset sno1=20070201insert into bank(b_type,sno) values('支取',sno1)set errorsum=errorsum+errorupdate users set smoney=smoney-100 where sno=sno1if errorsum<>0 -支取有错beginprint '交易失败，事务回滚'rollback transactionendelsebeginprint '交易成功'select 用户名=sname,

8、交易类型=b_type,账户余额=smoney from bank left join users on users.sno=bank.snocommit transactionend交易成功用户名 交易类型 账户余额- - -李燕 转出 2145.00张立 转入 5345.00李燕 转出 2145.00张立 转入 5345.00李燕 支取 2145.00李燕 支取 2145.00-存款步骤begin transactiondeclare errorsum intset errorsum=0declare sno2 intset sno2=20070202insert into bank(b_

9、type,sno) values('存入',sno2)set errorsum=errorsum+errorupdate users set smoney=smoney+100 where sno=sno2if errorsum<>0 -支取有错beginprint '交易失败，事务回滚'rollback transactionendelsebeginprint '交易成功'select 用户名=sname,交易类型=b_type,账户余额=smoney from bank left join users on users.sno=b

10、ank.snocommit transactionend交易成功用户名 交易类型 账户余额- - -李燕 转出 2145.00张立 转入 5445.00李燕 转出 2145.00张立 转入 5445.00李燕 支取 2145.00李燕 支取 2145.00张立 存入 5445.00（3）使用视图实现学生成绩排名编写T-SQL程序，创建一个新的表存储每位同学所修科目总数，和平均成绩，在平均成绩列上创建索引；创建一个用户排名的视图，按平均成绩排序，平均成绩相同者，按所修科目多少排序。要求用汉字显示名次，如：“第一名”。if exists(select * from sysobjects where

11、 name='student')drop table studentcreate table student(sno int, -学号sname char(10), -姓名ssex char(2), -性别s_avg int, -平均成绩s_sum int -所修课目总数)insert into student values(20070201,'张立','男',87,3)insert into student values(20070202,'李燕','女',79,6)insert into student values(20070203,'李勇','男',94,5)insert into student values(20070204,'吴欣','女',85,4)create index ix_avg