ACCP V4.0 q事务和视图 ACCP V4.0 回顾 q 下列T-SQL语句有错吗？实现了什么功能？ SELECT * FROM stuInfo WHERE stuNo IN (SELECT * FROM stuMarks) 上述语句可以用下面的连接替换吗？ SELECT * FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo q 下列语句实现了什么功能？ SELECT * FROM stuInfo WHERE stuNo NOT IN (SELECT stuNo FROM stuMarks) 上述语句可以用下面的连接替换吗？ SELECT * FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNostuMarks.stuNo SELECT stuNo FROM stuMarks 可 以 查看没有参加考试的学员 有的子查询不能用连接替换 。这就是典型的例子 ACCP V4.0 回顾 q下列语句实现了什么功能？ SELECT * FROM stuInfo WHERE EXISTS(SELECT * FROM stuMarks WHERE stuNo=stuInfo.stuNo) q请使用IN子查询改写上述语句？ q请使用连接改写上述语句 查询参加考试的学员 SELECT * FROM stuInfo WHERE stuNo IN (SELECT stuNo FROM stuMarks) SELECT * FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo ACCP V4.0 目标 q理解事务的概念 q掌握如何创建事务 q掌握如何创建索引 q掌握如何创建并使用视图 ACCP V4.0 为什么需要事务5-1 银行转帐 q例如，银行转帐问题： 假定资金从帐户A转到帐户B，至少需要两步： q帐户A的资金减少 q然后帐户B的资金相应增加 帐户A帐户B ACCP V4.0 q假定张三的帐户直接转帐1000元到李四的帐户 为什么需要事务5-2 CREATE TABLE bank ( customerName CHAR(10), -顾客姓名 currentMoney MONEY -当前余额 ) GO ALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney=1) GO INSERT INTO bank(customerName,currentMoney) VALUES(张三,1000) INSERT INTO bank(customerName,currentMoney) VALUES(李四,1) 创建帐户表，存放用户的帐户信息 添加约束：根据银行规定，帐户 余额不能少于1元，否则视为销户 张三开户，开户金额为1000元 ；李四开户，开户金额1元 ACCP V4.0 为什么需要事务5-3 q目前两个帐户的余额总和为：1000+1=1001元 ACCP V4.0 为什么需要事务5-4 q模拟实现转帐 ： 从张三的帐户转帐1000元到李四的帐户 /*-转帐测试：张三转账1000元给李四-*/ -我们可能会这样这样编写语句 -张三的帐户少1000元，李四的帐户多1000元 UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四 GO -再次查看转帐后的结果。 SELECT * FROM bank GO 请问： 执行转帐语句后，张三、李四的 帐户余额为多少？ l张三的帐户没有减少 l但李四的帐户却多了1000元 l100010012001元 总额多出了1000元！ ACCP V4.0 为什么需要事务5-5 -张三的帐户减少1000元，李四的帐户增加1000元 UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=张三 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=李四 GO q错误原因分析： UPDATE语句违反约束： 余额=1元 执行失败，所以张三还是1000元 继续往下执行：执行成功，所以李四变为1001元 如何解决呢？使用事务 ACCP V4.0 什么是事务 q事务(TRANSACTION)是作为单个逻辑工作单元 执行的一系列操作 q这些操作作为一个整体一起向系统提交，要么都 执行、要么都不执行 q事务是一个不可分割的工作逻辑单元 转帐过程就是一个事务。 它需要两条UPDATE语句来完成，这两条语句是一个整体 ，如果其中任一条出现错误，则整个转帐业务也应取消， 两个帐户中的余额应恢复到原来的数据，从而确保转帐前 和转帐后的余额不变，即都是1001元。 ACCP V4.0 事务的特性 事务必须具备以下四个属性，简称ACID 属性： q 原子性（Atomicity）：事务是一个完整的操作。事务 的各步操作是不可分的（原子的）；要么都执行，要么 都不执行 q 一致性（Consistency）：当事务完成时，数据必须 处于一致状态 q 隔离性（Isolation）：对数据进行修改的所有并发事 务是彼此隔离的，这表明事务必须是独立的，它不应以 任何方式依赖于或影响其他事务 q 永久性（Durability）：事务完成后，它对数据库的修 改被永久保持，事务日志能够保持事务的永久性 ACCP V4.0 如何创建事务 q T-SQL使用下列语句来管理事务： q开始事务：BEGIN TRANSACTION q提交事务：COMMIT TRANSACTION q回滚（撤销）事务：ROLLBACK TRANSACTION 一旦事务提交或回滚，则事务结束。 q 判断某条语句执行是否出错： q使用全局变量ERROR； qERROR只能判断当前一条T-SQL语句执行是 否有错，为了判断事务中所有T-SQL语句是否有错， 我们需要对错误进行累计； 如： SET errorSum=errorSum+error ACCP V4.0 如何创建事务 了解事务的分类： q 显示事务：用BEGIN TRANSACTION明确指定事务的开 始，这是最常用的事务类型 q 隐性事务：通过设置SET IMPLICIT_TRANSACTIONS ON 语句，将隐性事务模式设置为打开，下一个语句自动 启动一个新事务。当该事务完成时，再下一个 T-SQL 语 句又将启动一个新事务 q 自动提交事务：这是 SQL Server 的默认模式，它将每条 单独的 T-SQL 语句视为一个事务，如果成功执行，则自 动提交；如果错误，则自动回滚 ACCP V4.0 关键语句讲解 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 -累计是否有错误 如何创建事务 q使用事务解决银行转帐问题 开始事务（指定事务从此处开始， 后续的T-SQL语句都是一个整体） 累计是否有错误 ACCP V4.0 IF errorSum0 -如果有错误 BEGIN print 交易失败，回滚事务 ROLLBACK TRANSACTION END ELSE BEGIN print 交易成功，提交事务，写入硬盘，永久的保存 COMMIT TRANSACTION END GO print 查看转帐事务后的余额 SELECT * FROM bank GO 如何创建事务 根据是否有错误，确定 事务是提交还是撤销 如果有错，则回滚操作，事务结束 如果成功，则提交操作，事务结束 ACCP V4.0 如何创建事务 转帐事务前 转帐事务过程中 转帐事务结束后 q演示：转帐1000，转帐失败的情况 ACCP V4.0 如何创建事务 转帐事务前 转帐事务过程中 转帐事务结束后 q演示：转帐800，转帐成功的情况 ACCP V4.0 什么是视图 CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName, 学号=stuInfo.stuNo, 基于学员信息表和 成绩表创建视图 教员需要的视图： 方便查看学员的成绩 班主任需要的视图： 方便查看学员的档案 ACCP V4.0 什么是视图 q视图是一张虚拟表，它表示一张表的部分数据 或多张表的综合数据，其结构和数据是建立在 对表的查询基础上 q视图中并不存放数据，而是存放在视图所引用 的原始表（基表）中 q同一张原始表，根据不同用户的不同需求，可 以创建不同的视图 ACCP V4.0 什么是视图 q视图的用途 q筛选表中的行 q防止未经许可的用户访问敏感数据 q降低数据库的复杂程度 q将多个物理数据库抽象为一个逻辑数据库 ACCP V4.0 如何创建视图 q演示使用企业管理器创建视图 ACCP V4.0 如何创建视图 q使用T-SQL语句创建视图的语法 CREATE VIEW view_name AS ACCP V4.0 如何创建视图 q创建方便教员查看成绩的视图IF EXISTS (SELECT * FROM sysobjects WHERE name = view_stuInfo_stuMarks) DROP VIEW view_stuInfo_stuMarks GO CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName,学号=stuInfo.stuNo, 笔试成绩 =writtenExam, 机试成绩=labExam, 平均分=(writtenExam+labExam)/2 FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo GO SELECT * FROM view_stuInfo_stuMarks 检测是否存在 创建视图 使用视图 删除视图 ACCP V4.0 总结 2-1 q数据库事务具有如下特性： q原子性 q一致性 q隔离性 q持久性 以上四个特性也称为ACID特性。 q事务可以分为如下类型： q显式事务 q隐性事务 q自动提交事务 ACCP V4.0 总结 2-2 qTransact-SQL使用下列语句来管理事务： qBEGIN TRANSACTION qCOMMIT TRANSACTION qROLLBACK TRANSACTION q建立索引有助于快速检索数据。索引分为唯一索 引、主键索引、聚集索引、非聚集索引 q视图是一张虚拟表，可以方便不同用户的查询， 提高数据的安全性，筛选特定的数据行