《SQL Server 数据库案例教程》课件第10章事务索引和视图

课程内容回顾变量的含义和作用SQLServer在T-SQL中支持下列两种类型的变量：全局变量和局部变量局部变量的定义和赋值输出结果的两种方式批处理和注释控制流语句语句块本章目标理解事务的概念理解事务的特性、分类在SQLServer中启动、提交和回滚事务掌握如何创建索引掌握如何创建并使用视图为什么需要事务事务能确保把对多个数据操作作为一个单元来处理例如银行转帐:转入和转出问题:假定张三账户直接转1000元到李四账户,模拟过程如下:准备:建立账户表bank,包括客户名字和当前余额银行规定，帐户余额不能少于1元，否则视为销户存钱:插入测试数据，张三开户，开户金额为1000元；李四开户，开户金额为1元演示开户过程为什么需要事务模拟实现转帐：使用UPDATE语句,从张三的帐户直接转帐1000元到李四的帐户

,两账户总额应保持不变--张三帐户减少1000元，李四帐户增多1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=’张三’UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=’李四’转帐后:两账户总余额为2001元转帐前:两账户总余额为1001元事务的概念及特性事务提供了一种机制、是一个操作序列，它包含了一组数据库操作命令，并且所有的命令作为一个整体一起向系统提交或撤消操作请求事务是作为单个逻辑工作单元执行的一系列操作事务的四个特性:原子性（Atomicity）：事务能确保把对多个数据修改作为一个单元来处理，也就是原子操作。一致性（Consistency）：当事务完成时，数据必须处于一致状态隔离性（Isolation）:对数据进行修改的所有并发事务是彼此隔离的。这表明事务必须是独立的，它不应以任何方式依赖或影响其他事务持久性（Durability）：当事务完成之后，它对于系统的影响是永久性的。事务的分类事务分类显式事务隐性事务自动提交事务显式事务:显式事务是显式地定义其开始和结束的事务

BEGINTRANSACTION…数据库操作如:插入记录

…数据库操作如:删除记录COMMITTRANSACTION事务的分类隐性事务:通过Transact-SQL的SETIMPLICIT_TRANSACTIONSON语句，将隐性事务模式设置为打开。

SETIMPLICIT_TRANSACTIONSONGO/*第一次执行Insert语句的时候将自动启动一个隐性事务*/INSERTINTOImpTranVALUES(1,'aaa')INSERTINTOImpTranVALUES(2,'bbb')GO/*提交第一个事务*/COMMITTRANSACTIONGO/*执行SELECT语句将启动第二个隐性事务*/SELECTCOUNT(*)FROMImpTranGOINSERTINTOImpTranVALUES(3,'ccc')GOSELECT*FROMImpTranGO/*提交第二个事务*/COMMITTRANSACTIONGOSETIMPLICIT_TRANSACTIONSOFFGO事务的分类自动提交事务:所有Transact-SQL语句在完成时，都会提交或回滚。如果一条语句成功完成，则将其提交，如果遇到任何错误，则将其回滚;默认操作模式用T-SQL表示事务Transact-SQL使用下列语句来管理事务开始事务：BEGINTRANSACTION提交事务：COMMITTRANSACTION回滚（撤消）事务：ROLLBACKTRANSACTION下列变量在事务处理中非常有用。@@ERROR@@TRANCOUNT事务的应用USEempDBGOSETNOCOUNTON--不显示受影响的行数信息print‘查看转帐事务前的余额’SELECT*FROMbankGO/*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体）*/BEGINTRANSACTION/*定义变量，用于累计事务执行过程中的错误--*/DECLARE@errorNoINTSET@errorNo=0--初始化为0，即无错误/*--张三的帐户减少1000元，李四的帐户多1000元*/UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=’张三’Set@errorNo=@errorNo+@@error--累计是否有错误Print‘查看转帐过程中的余额’SELECT*FROMbank/*--根据语句执行情况，确定事务是提交或撤消--*/IF@errorNo<>0--如果有错误

BEGINPrint‘交易失败，回滚事务’

ROLLBACKTRANSACTIONENDELSEBEGINPrint‘交易成功，提交事务，永久保存’

COMMITTRANSACTIONENDGOPrint‘查看转帐事务后的余额’SELECT*FROMbankGO

索引索引的概念索引：是SQLServer编排数据的内部方法。它为SQLServer提供一种方法来编排查询数据的路由索引页：数据库中存储索引的数据页。索引页存放检索数据行的关键字页以及该数据行的地址指针。索引页类似于汉语字典中按拼音或笔画排序的目录页创建索引创建索引有两种方法:使用SSMS(SQLServerManagementStudio)T-SQL语句语法:其中：UNIQUE指定唯一索引，可选CLUSTERED、NOCLUSTERED指定是聚集索引还是非聚集索引,可选。CREATE[UNIQUE][CLUSTERED][NONCLUSTERED]INDEXindex_nameONtable_name(column_name[,column_name]。。。)创建索引示例问题:员工表EMP中工资列（sal）经常查询，为了加快查询速度,在工资列上创建索引，该索引为非聚集索引

创建索引：

USEempDBGO/*--工资列创建非聚集索引*/CREATENONCLUSTEREDINDEXIX_emp_salONemp(sal)GO

/*--指定按索引：IX_emp_sal查询--*/CREATENONCLUSTEREDINDEXIX_emp_salONemp(sal)SELECT*FROMemp(INDEX=IX_emp_sal)WHERESalBETWEEN2000AND3000指定按索引查询：索引的类型索引分类聚集索引非聚集索引聚集索引:聚集索引基于数据行的键值在表内排序和存储这些数据。聚集索引类似于电话簿

索引前索引后创建聚集索引注意事项:选择唯一值比例高的列。一般应选择主键列。先创建聚集索引，再创建非聚集索引在经常被使用联结或GROUPBY子句的查询访问列上创建聚集索引；聚集索引不适用于频繁更改的列，因为这将导致整行移动。CREATECLUSTEREDINDEXClindx_empnoONemp(empno)在emp表的empno列上创建聚集索引Clindx_empno非聚集索引非聚集索引非聚集索引指定表的逻辑顺序。因此，一个表可以有多个非聚集索引数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。大多数所以都是非聚集所在emp表的ename列创建非聚集索引CREATENOCLUSTEREDINDEXNCLINDX_enameONemp(ename)索引的特性索引唯一索引组合索引唯一索引:唯一索引不允许两行具有相同的索引值

问题:在EMP表中的员工姓名列上创建了唯一索引，则所有的员工都不能重名

创建唯一索引需要注意的事项

:只有当唯一性是数据本身的特征时，指定唯一索引才有意义。如果必须实施唯一性以确保数据的完整性，则应在列上创建UNIQUE或PRIMARYKEY约束，而不要创建唯一索引。创建PRIMARYKEY或UNIQUE约束会在表中指定的列上自动创建唯一索引

CREATEUNIQUECLUSTEREDINDEXempno_indONemp_pay(empno)索引的特性索引唯一索引组合索引组合索引:组合索引包含两个或更多为创建索引而组合在一起的列。最多可以组合16列

问题:stuDB数据库的成绩表grade，在学号sno和课程号cno列的组合上创建聚集索引

创建组合索引时应遵循下列原则:当需要频繁地将两个或多个列作为一个整体进行搜索时，可以创建组合索引。创建组合索引时，先列出唯一性最好的列。组合索引中列的顺序和数量会影响查询的性能

CREATEUNIQUECLUSTEREDINDEXUPKCL_gradeONgrade(sno,cno)

创建索引的指导原则

使用索引虽然可以加快数据检索速度，但没必要为每个字段都建立索引在经常要搜索的列上创建索引，例如：主键所在的列外键或在联结表中经常使用的列以排序次序访问的列在聚合过程中被划分为一组的列。不需要索引的列在查询中很少引用的列由text、ntext或image数据类型定义的列，具有这些数据类型的列不能进行索引删除索引使用DROPINDEX语句可以删除表的索引

删除索引时的注意事项:不能将DROPINDEX语句用于由PRIMARYKEY或UNIQUE约束创建的索引，必须先删除这些约束才能删除索引在删除表时，该表所有的索引也将随之删除在删除聚集索引时，表中所有的非聚集索引都会被自动重键DROPINDEX表名.索引名

USEempDBDROPINDEXemp.IX_emp_sal语法:示例:

从emp表中删除IX_emp_sal索引视图的概念及优点概念:视图是从不同的视角查看数据库中一个或多个表中数据的方法。视图是一种虚拟表，它的数据并不真正存储，仅保存视图定义

优点:对最终用户的好处结果更容易理解。

对开发人员的好处

简化查询限制数据检索

创建视图创建视图的方法有两种：使用SSMS和T-SQL语句使用SSMS创建视图使用T-SQL语句创建视图

语法:演示CREATEVIEWview_nameAS<select语句>

示例:创建视图，显示部门表中部门名称和位置USEempDBGO/*创建视图时，如果查询中没指定列别名，则默认视图列名与表的列名相同*/CREATEVIEWdept_viewASSELECTdname,locFROMDEPTGO--使用视图SELECT*FROMdept_view视图分类分类:简单视图复杂视图简单视图:视图的列取自于一个表简单视图可以像基表一样进行增、删、改操作，操作后的数据会直接反应到源表。复杂视图:复杂视图是视图中列取自多个表，比如：建立视图的查询包含多表联接、聚合函数等例如:建立视图complex_view,求出部门号和每个部门的人数复杂视图不能直接进行增、删、改操作，如果进行修改操作，要通过INSTEADOF触发器进行。

CREATEVIEWcomplex_viewASSELECTdeptno