单元7 事务、视图和索引

账户A的资金减少 然后账户B的资金相应增加
银行转账 账户A 账户B
为什么需要事务5-2 假定张三的账户直接转账1000元到李四的账户
CREATE TABLE bank 创建账户表，存放用户的账户信息 ( customerName CHAR(10), --顾客姓名 currentMoney MONEY --当前余额 ) 添加约束：根据银行规定，账户余 GO 额不能少于1元，否则视为销户 ALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney >= 1) GO 张三开户，开户金额为1000元 ；李四开户，开户金额1元 INSERT INTO bank(customerName, currentMoney) VALUES('张三', 1000) INSERT INTO bank(customerName, currentMoney) VALUES('李四', 1)
预习检查
T-SQL语句如何提交一个事务？ 事务具有的四个特性是什么？ 视图是虚拟表吗？ 索引的作用是什么？
本章任务
模拟实现银行转帐功能 批量插入学生考试成绩 办理毕业学生离校手续 查看学生成绩 查看学生档案
本章目标
使用事务保证操纵数据的完整性
掌握如何创建并使用视图
为什么需要事务5-1
银行转账问题： 假定资金从账户A转到账户B，至少需要两步：
请使用IN子查询改写上述语句？
SELECT * FROM Student WHERE StudentNo IN (SELECT StudentNo FROM Result)源自请使用联接查询改写上述语句
SELECT * FROM Student INNER JOIN Result ON Student.StudentNo = Result.StudentNo
单元7
事务、视图和索引
回顾与作业点评3-1
下面的SQL语句有错吗？实现了什么功能？
SELECT * FROM Student * FROM Result) FROM Result) WHERE StudentNo IN (SELECT StudentNo
上述语句可以用下面的联接替换吗？
SELECT Student.* FROM Student INNER JOIN Result ON Student.StudentNo=Result.StudentNo
永久性（Durability）
事务完成后，它对数据库的修改 被永久保持
如何创建事务2-1
使用SQL语句管理事务
开始事务
BEGIN TRANSACTION
提交事务
COMMIT TRANSACTION
回滚（撤销）事务
ROLLBACK TRANSACTION
如何创建事务2-2 一旦事务提交或回滚，则事务结束 判断某条语句执行是否出错：
隐性事务
通过设置SET IMPLICIT_TRANSACTIONS ON 语句， 将隐性事务模式设置为打开 其后的T-SQL语句自动启动一个新事务 提交或回滚一个事务后，下一个 T-SQL 语句又将启动 一个新事务
自动提交事务
SQL Server 的默认模式 每条单独的 T-SQL 语句视为一个事务
练习——办理毕业学生离校手续
BEGIN TRANSACTION DECLARE @errorSum INT 需求说明： SET @errorSum=0 /*--查询Result表中所有三年级学生的考试成绩，保存到新表 将毕业学生的基本信息和考试成绩分别保存到历史 HistoreResult*/ 表中 SELECT Result.* INTO HistoreResult FROM Result INNER JOIN Student ON Result.StudentNo=Student.StudentNo 提示： INNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName=‘ 三年级' 使用显式事务 SET @errorSum=@errorSum@@ERROR 查询Result表中所有三年级学生的考试成绩，保存 /*--删除Result表中所有三年级学生的考试成绩 */ DELETE Result FROM Result JOIN Student ON 到表 HistoreResult 中 Result.StudentNo=Student.StudentNo 删除ON Result 表中所有三年级学生的考试成绩 INNER JOIN Grade Grade.GradeId=Student.GradeId WHERE GradeName=‘ 三年级' 表中所有三年级的学生记录，保存到 查询Student SET @errorSum=@errorSum+@@ERROR 表HistoryStudent中 /*--将Student表中所有三年级的学生记录，保存到新表 HistoryStudent*/ …… 删除Studet表中所有三年级学生记录 /*--删除Studet表中所有三年级学生记录*/ …… 完成时间：20分钟 /*--根据是否有错误，确定事务是提交还是撤销--*/
使用事务解决银行转账问题4-1
从张三的账户转出1000元，存入李四的账户中
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 累计是否有错误 开始事务（指定事务从此处开始，后续 的T-SQL语句都是一个整体）
事务的特性
事务必须具备以下四个属性，简称ACID 属性：
原子性（Atomicity）
事务是一个完整的操作 事务的各步操作是不可分的（原 子的）；要么都执行，要么都不 执行 当事务完成时，数据必须处于一 致状态
一致性（Consistency）
隔离性（Isolation）
并发事务之间彼此隔离、独立， 它不应以任何方式依赖于或影响 其他事务
可以
回顾与作业点评3-2
下列语句实现了什么功能？
查看没有参加考试的学生
SELECT * FROM Student WHERE StudentNo NOT IN ( SELECT StudentNo FROM Result )
上述语句可以用下面的联接替换吗？
SELECT * FROM Student INNER JOIN Result ON Student.StudentNo<>Result.StudentNo
不可以
有的子查询不能用联接替换。这就是典型的例子
回顾与作业点评3-3
下列语句实现了什么功能？
查询参加考试的学生
SELECT * FROM Student WHERE EXISTS ( SELECT * FROM Result WHERE StudentNo = Student.StudentNo )
为什么需要事务5-5 发生错误的原因
UPDATE语句违反约束： 余额>=1元
--张三的账户减少1000元，李四的账户增加1000元 UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三' 执行失败，张三账户余额还是1000元 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四' GO 继续往下执行：执行成功，李四账户余额变为1001元 如何解决呢？使用事务
什么是事务
事务(TRANSACTION)是作为单个逻辑工作单元 执行的一系列操作 多个操作作为一个整体向系统提交，要么都执行、 要么都不执行 事务是一个不可分割的工作逻辑单元
转账过程就是一个事务
它需要两条UPDATE语句来完成，这两条语句是一个整体
如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的 余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即 都是1001元
使用事务解决银行转账问题4-2 从张三的账户转出1000元，存入李四的账户中
IF @errorSum<>0 --如果SQL语句执行出错 根据执行是否有错误，决定 BEGIN 提交事务，或撤销事务 PRINT '交易失败，回滚事务' ROLLBACK TRANSACTION END 如果有错，则回滚操作，事务结束 ELSE BEGIN PRINT '交易成功，提交事务，写入硬盘，永久的保存' COMMIT TRANSACTION 如果成功，则提交操作，事务结束 END GO PRINT '查看转账事务后的余额' SELECT * FROM bank GO
训练要点：
使用事务向表中插入多条记录
需求说明：
批量插入参加今天“Java Logic”课程考试的十名 学生成绩 如果输入的成绩大于100分，将违反约束
讲解需求说明
指导——批量插入学生考试成绩2-2