学习情境5事务索引和视图.docx
《学习情境5事务索引和视图.docx》由会员分享，可在线阅读，更多相关《学习情境5事务索引和视图.docx（15页珍藏版）》请在冰点文库上搜索。
学习情境5事务索引和视图
第十章事务、索引和视图
1回顾上一章:
[10分钟]2
2课程知识点讲解：
2
2.1事务[25分钟]2
2.1.1为什么需要事务（Why）?
2
2.1.2事务的概念（What）及特征3
2.1.3如何创建事务（How）4
2.2索引[35分钟]5
2.2.1概念（What）5
2.2.2如何创建索引（How）5
2.3视图[30分钟]6
2.3.1概念（What）6
2.3.2视图的作用6
2.3.3使用视图的好处（where）7
2.3.4如何创建视图（How）7
本章总结[10分钟]8
考核点8
测试题8
扩展部分8
学生问题汇总9
作业9
●本章主要目标
⏹本章的目标是
Ø理解事务的概念
Ø掌握如何创建事务
Ø掌握如何创建索引
Ø掌握如何创建并使用视图
●本章重点
Ø掌握如何创建事务
Ø掌握如何创建索引
Ø掌握如何创建并使用视图
●本章难点
⏹聚集索引和非聚集索引
⏹视图和查询的区别
1回顾上一章:
[10分钟]
2课程知识点讲解：
2.1事务[25分钟]
2.1.1为什么需要事务（Why）?
/*银行转帐问题*/
一般来说，只要是同一银行，一般都支持帐户间直接转帐。
假定资金从帐户A转到帐户B,至少需要两步,即帐户A的资金减少,然后帐户B的资金相应增加.但在转帐过程中,系统必须保证:
这些步骤是一个整体,如果其间任一步骤失败,则将撤消对这个两个帐户数据所做的任何修改。
引例讲解：
案例讲解：
（示例１：
为什么需要事务.sql）
USEstuDB
GO
--创建农行帐户表bank
IFEXISTS（SELECT*FROMsysobjectsWHEREname='bank'）
DROPTABLEbank
GO
CREATETABLEbank
（
customerNameCHAR（10）,--顾客姓名
currentMoneyMONEY--当前余额
）
GO
/*---添加约束：
根据银行规定，帐户余额不能少于1元，除非销户----*/
ALTERTABLEbank
ADDCONSTRAINTCK_currentMoneyCHECK（currentMoney>=1）
GO
/*--插入测试数据：
张三开户，开户金额为800；李四开户，开户金额1---*/
INSERTINTObank（customerName,currentMoney）VALUES（'张三',1000）
INSERTINTObank（customerName,currentMoney）VALUES（'李四',1）
GO
--查看结果
SELECT*FROMbank
GO
/*--转帐测试：
张三希望通过转账，直接汇钱给李四1000元--*/
--我们可能会这样这样写代码
--张三的帐户少1000元，李四的帐户多1000元
UPDATEbankSETcurrentMoney=currentMoney-1000
WHEREcustomerName='张三'
UPDATEbankSETcurrentMoney=currentMoney+1000
WHEREcustomerName='李四'
GO
--再次查看结果，结果发现了什么严重的错误?
如何解决呢？
SELECT*FROMbank
GO
强调：
在现实生活中两个帐户之间转帐后两个帐户的余额应保持不变
老师提问：
问题1：
在上例中，如把张三帐上所有的钱都转给了李四，当在查询他们两之间的帐户余额时会看到何现象？
为什么？
上两条UPDATE语句，哪条导致了引错误？
答：
查询时，看到张三帐户上的余额未做修改，且张四帐户上的余额却增加了，这时，他们两帐户之余额总和发生了改变。
当在招待更新数据时，违反了CK_currentMoney约束。
2.1.2事务的概念（What）及特征
概念：
事务是一种机制、是一个操作序列，它包含了一组数据操作命令，并且所有的命令作为一个整体一起向系统提交或撤消操作请求，即这一组数据命令要么都招待，要么都不执行。
强调：
事务是一个整体，如果其中一步失败了，整个操作都取消；如果每步都成功了，则整个操作才完成。
特征：
原子性（Atomicity）：
事务是一个整体，事务的各元素是不可分的（原子的）。
一致性（Consistency）：
当事务完成时，数据必须处于一致状态。
隔离性（Isolation）：
事务必须是独立的，它不以任何方式依赖于或影响其他事务。
永久性（Durability）：
事务完成后，它对于系统的影响是永久性的。
适用（Where）：
适用于多用户同时操作的数据库系统。
如航空订票，银行，保险公司，证券交易系统等。
2.1.3如何创建事务（How）
事务管理
Ø开始事务 ：
begintransaction
Ø提交事务：
committransaction
Ø回滚（撤消）事务 ：
rollbacktransaction
事务分类
Ø显示事务：
用begintransaction明确指定事务的开始．实际开发中最常用的．
Ø隐性事务：
通过设置setimplicit_transcationson语句．当以隐性事务操作时，将在提交或回滚事务后自动启动新事务．
Ø自动提交事务：
这是默认模式，它将每条单独的Ｔ-SQL语句视为一个事务．如果成功执行，则自动提交；如果错误，则自动回滚．
老师提问：
怎样判断Ｔ-SQL语句是否有错误？
答：
在第三章曾讲过全局变量＠＠error，用它来判断当前T-SQL语句执行是否有错，若有错，返回非零值．
案例讲解：
（示例２：
如何创建事务.sql）
SETNOCOUNTON--不显示受影响的行数信息
print'查看转帐事务前的余额'
SELECT*FROMbank
GO
/*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体--*/
BEGINTRANSACTION
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE@errorSumINT
SET@errorSum=0--初始化为0，即无错误
/*--转帐：
张三的帐户少1000元，李四的帐户多1000元*/
UPDATEbankSETcurrentMoney=currentMoney-800WHEREcustomerName='张三'
SET@errorSum=@errorSum+@@error--累计是否有错误
UPDATEbankSETcurrentMoney=currentMoney+800WHEREcustomerName='李四'
SET@errorSum=@errorSum+@@error--累计是否有错误
print'查看转帐事务过程中的余额'
SELECT*FROMbank
/*--根据是否有错误，确定事务是提交还是撤销---*/
IF@errorSum<>0--如果有错误
BEGIN
print'交易失败，回滚事务'
ROLLBACKTRANSACTION
END
ELSE
BEGIN
print'交易成功，提交事务，写入硬盘，永久的保存'
COMMITTRANSACTION
END
GO
print'查看转帐事务后的余额'
SELECT*FROMbank
GO
强调：
查看转帐前后的数据，目的是为了体现事务的回滚特征，即撤消所有的操作．
2.2索引[35分钟]
引例讲解：
/*新华字典查字*/
为了加快查找速度，汉字字典一般都有按拼音、笔画、偏旁部首等排序的目录（索引），可以选择按拼音或笔画查找方式，快速查到需要的字。
同样，ＳＱＬ 允许用户在表中创建索引，指定按某列预先排序。
2.2.1概念（What）
索引提供指针以指向存储在表中指定列的数据值，然后根据指定的排序次序排列这些指针．
概念描述：
ＳＱＬServer中的数据记录是按页存放的，每页容量一般为４ＫＢ。
索引是ＳＱＬServer编排数据的内部方法。
它提供一种方法来编排查询数据的路由。
索引的作用：
通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。
索引类型：
唯一索引：
不允许两行具有相同的索引值；如：
应在身份证号码列上创建唯一索引；如果一个表创建了唯一约束，将自动创建唯一索引。
主键索引：
数据库系统图中为表定义一个主键将自动创建主键索引，它是唯一索引的一个特殊类型。
聚集索引：
在聚集索引中，表中各行的物理顺序与索引键值的逻辑（索引）顺序相同。
一个表只能包含一个聚集索引；如果某列为主键，该列就默认为聚集索引。
聚集索引通常可加快UPDATE和DELETE操作的速度，因为这两个操作需要读取大量的数据。
非聚集索引：
表中各行的物理顺序与索引键值的逻辑（索引）顺序不区配;如果在为UNIQUE约束指定CLUSTERED的同时又指定了PRIMARYKEY约束，则PRIMARYKEY将默认为非聚集索引。
一个表中可创建多个非聚集索引.
2.2.2如何创建索引（How）
Ø使用企业管理器（演示）
Ø使用T-SQL语句创建索引
语法:
Create[unique][clustered|nonclustered]indexindex_nameONtable_name（column_name[,column_name]…）[withfllfactor=x]
说明:
Unique指定唯一索引,可选.
Clusterednonclustered指定聚集索引还是非聚集索引,可选.
Fllfactor表示填允因子,指定一个0-100的值,该值批示索引页填满的空间所占的百分比.
案例讲解：
（示例3:
如何创建索引.sql）
/*按学生笔试成绩创建索引*/
--代码
usestuDB
go
/*检测是否存在该索引*/
ifexists（selectnameformsysindexeswherename='index_stuMarks_wirttenExam'）
dropindexstuMarks.index_stuMarks_wirttenExam
/*笔方式列创建非聚集索引:
填充因子为30%*/
createnonclusteredindexstuMarks.index_stuMarks_wirttenExam
onstuMarks（writtenExam）
withfillfactor=30
go
/*查询索引*/
Select*fromstuMarks（index=index_stuMarks_wirttenExam）
WherewrittenExambetween60and90
老师提问：
问题:
为什么不在上例题中创建聚集索引？
答:
由于笔试成绩可能会重复,所以只能创建非聚集索引.
2.3视图[30分钟]
老师提问：
在月球上看地球形状各不相同,为什么?
答:
因为从不同的角度看到的形状不同.
/*查看数据*/
对于学员信息表和学员成绩表数据,班主任和教员所关心的数据都不同,那该怎么办?
这样做的目的是什么?
为了解决这一问题,引入视图.
引例讲解：
2.3.1概念（What）
视图是另一种查看数据库中一个或多个表中的数据的方法。
可以被看成是虚拟表或存储查询。
概念描述
可通过视图访问的数据不作为独特的对象存储在数据库内.充当着查询中指定的表的筛选器.定义视图的查询可以基于一个或多个表,也可以基于其他视图、当前数据库或其他数据库.
使用视图的原因（Why）
⏹一是出于安全上的考虑
⏹二是符合用户日常业务逻辑
2.3.2视图的作用
⏹筛选表中的行
⏹防止未经许可的用户访问敏感数据
⏹降低数据库的复杂程序
⏹将多个物理数据表抽象为一个逻辑数据表
2.3.3使用视图的好处（where）
Ø对最终用户的好处
⏹结果更容易理解:
创建视图时,可以将列名改为更有意义的名称,使用户更容易理解列所代表的内容.在视图中修改列名不会影响基表的列名.
⏹获得数据更容易:
很多人对SQL不太了解,因此对他们来说创建对多个表的复杂查询很困难.因而可以通过创建视图来方便用户访问多个表中的数据.
Ø对开发人员的好处
⏹限制数据检索更容易:
开发人员有时需要隐藏某些行或列中的信息,通过视图用户可以灵活地访问他们需要的数据,同时保证同一个表或其他表上的其他数据的安全性.
⏹维护应用程序更方便:
调试视图比调试查询更容易.
2.3.4如何创建视图（How）
Ø使用企业管理器（演示）
Ø使用T-SQL语句创建视图
语法：
Createviewview_nameAS
案例讲解：
（示例4：
如何创建视图.sql）
USEstuDB
GO
/*--检测是否存在：
视图存放在系统表sysobjects中--*/
IFEXISTS（SELECT*FROMsysobjects
WHEREname='view_stuInfo_stuMarks'）
DROPVIEWview_stuInfo_stuMarks
GO
/*--创建视图：
查看学员的成绩情况--*/
CREATEVIEWview_stuInfo_stuMarks
AS
SELECT姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,
机试成绩=labExam,平均分=（writtenExam+labExam）/2
FROMstuInfoLEFTJOINstuMarksONstuInfo.stuNo=stuMarks.stuNo
GO
/*--使用视图：
视图是一个虚拟表，可以像物理表一样打开--*/
SELECT*FROMview_stuInfo_stuMarks
说明：
从理论上可能像普通的物理表一样使用，即可以对它进行增、删、改、查等。
当修改视图时，害际上是修改原始数据表。
又因为修改视图有许多限制，所以在实际开发中一般视图仅做查询使用。
●本章总结[10分钟]
Ø事务具有如下特性
◆原子性
◆一致性
◆隔离性
◆持久性
Ø事务类型
◆显式事务
◆隐性事务
◆自动提交事务
ØT-SQL使用下列语句来管理事务
◆Begintanscation
◆Committransaction
◆Rollbacktransaction
Ø建立索引有助于快速检索数据，索引分为唯一索引、主键索引、聚集索引和非聚集索引
Ø聚集索引决定表中数据的存储顺序。
一个表只能有一个聚集索引，这是因为聚集索引决定数据的物理存储顺序。
非聚集索引指定表的逻辑顺序。
因此一个表可以有多个（最多249个）非聚集索引。
Ø视图是另一种查看数据库中一个或多个表中的数据方法。
视图是一种虚拟表，通常作为执行查询的结果而创建的。
视图充当着对查询中指定的表的筛选器。
考核点
考核点1：
如何创建并使用视图
考核点2：
测试题
测试题1：
如何创建视图？
测试题2：
索引类型？
扩展部分
扩展部分：
学生问题汇总
学生问题1：
视图与查询的区别?
作业
习题1：
（P77练习1-6）
习题2：
（P78作业）
习题3：
升级会员 