第五讲事务索引视图存储过程函数

第四章事务、索引、视图联结查询都可以使用子查询替代任何允许使用表达式的地方都可以使用子查询基本子查询有三类：通过IN、ANY、ALL修改的比较运算符引入的、在列表上操作的子查询通过未修改的比较运算符引入的必须返回单个值的子查询通过EXISTS引入的存在测试的子查询在子查询中使用IN子句可以返回多行记录，用于检测字段的值是否存在于一个范围内。在子查询中使用EXISTS子句，可以对子查询中的行是否存在进行检查。在子查询中使用ALL子句时，该子句的全部行必须都满足条件才返回真，ALL等效于NOTIN。相关子查询可用于从外部查询所引用的表中选择数据之类的操作。在这种情况下，必须使用表别名（又称相关名称）明确指定要使用哪个表引用。事务的概念和使用索引的概念和创建视图的创建和使用1.1为什么需要事务insertintoALL_CARD_EXCHANGE(CARD_ID,EXGET,EXTIME,EXADDRESS)values(1,600,'2012-1-5','三国商城')updateALL_PURPOSE_CARDsetBALANCE=BALANCE-600whereCARD_ID=1添加消费记录到ALL_CARD_EXCHANGE表更新信用卡表ALL_PURPOSE_CARD如果第一步成功而第二步失败，怎么办？具体实现步骤1.2什么是事务事务是作为单个逻辑工作单元执行的一系列操作事务中的操作作为整体向系统提交，要么都执行、要么都不执行事务是一个不可分割的工作逻辑单元1.2什么是事务事务的四个特性：原子性（Atomicity）：事务中各元素不可分割，全部执行成功或者撤销所有的操作一致性（Consistency）：事务完成后数据保持一致的状态隔离性（Isolation）：事务是相对独立的，对某数据进行修改时，其他事务不变持久性（Durability）：事务完成后对系统的影响是永久性的。在航空售票系统中若A与B售票点同时售出同一班次的同一座位机票，但又不会发生并行操作的冲突，这体现了事务的哪个特性？1.2什么是事务事务的隔离级别：未提交读（READUNCOMMITTED）：俗称“脏读”。事务之间没有隔离，能够读取其他事务正在修改但尚未提交的数据，该隔离级别无法确保数据的正确性。已提交读（READCOMMITTED）：SQLSERVER默认的隔离级别，能够确保其他事务不能读取当前正在修改但尚未提交的记录。重复读（REPEATABLEREAD）：确保其他事务不能修改当前事务正在读取但尚未提交的数据。可串行化（SERIALIZABLE）：最高隔离级别。事务之间完全隔离，按串行化的方式执行，所以该隔离级别不存在并行化的操作。1.3如何在T-SQL编程中创建和使用事务SQLSERVER有三类事务：显示事务：使用BEGINTRANSACTION语句明确指定事务的开始。隐式事务：设置SETIMPLICIT_TRANSACTIONSON语句将隐式事务模式设为打开，此时SQLSERVER将在提交和回滚事务后自动启动新的事务，该事务无法描述事务的开始，只需要提交或回滚每个事务。自动提交事务：SQLSERVER默认的事务模式，将每个T-SQL语句视为一个事务，成功执行时自动提交，出现错误时自动回滚。1.3如何在T-SQL编程中创建和使用事务T-SQL使用下列语句管理事务：开始事务：BEGINTRANSACTION提交事务：COMMITTRANSACTION回滚（撤销）事务：ROLLBACKTRANSACTION一旦事务提交或回滚，则事务结束判断某条语句执行是否出错：使用全局变量@@ERROR@@ERROR只能判断当前一条T-SQL语句的执行是否有错，为了判断事务中所有T-SQL语句是否有错，需要对错误进行累计累计错误：SET@errorSum=@errorSum+@@error1.3如何在T-SQL编程中创建和使用事务使用显示事务解决Bank数据库的用户转账实现一卡通中账户名为“诸葛亮”的卡号为“6225678934623552”的用户向账户名为“大乔”的卡号为“6225972472383739”的用户转账2000元。USEBankGObegintransactiondeclare@Errorint--定义一个错误号updateALL_PURPOSE_CARDsetBALANCE=BALANCE+2000whereCARD_NO='6225972472383739'andLAST_USED_DATE=getdate()set@Error=@@ERROR+@ErrorupdateALL_PURPOSE_CARDsetBALANCE=BALANCE-2000whereCARD_NO='6225678934623552'andLAST_USED_DATE=getdate()定义变量存储错误号开始事务1.3如何在T-SQL编程中创建和使用事务接前段代码：set@Error=@@ERROR+@Error--添加转账明细insertinto[TRANSFER]values('6225678934623552','6225972472383739',2000,GETDATE())declare@balanceOutfloat--定义余额--获得转出账用户的余额select@balanceOut=balancefromALL_PURPOSE_CARDwhereCARD_NO='6225678934623552'--添加“诸葛亮”的交易记录insertintoALL_CARD_EXCHANGEvalues(5,null,null,2000,null,@balanceOut-2000,GETDATE(),null,'网上银行转账')declare@balanceInfloat检查添加是否有错，将错误号累计到变量@errsum1.3如何在T-SQL编程中创建和使用事务接前段代码：select@balanceIn=balancefromALL_PURPOSE_CARDwhereCARD_NO='6225972472383739'insertintoALL_CARD_EXCHANGEvalues(2,null,null,null,2000,@balanceIn+2000,GETDATE(),null,'网上银行转账')if@Error=0beginprint'转账成功'commitendelsebeginprint'转账失败'rollbackendGO提交事务回滚事务2.索引2.1什么是索引按拼音查词按部首查词数据字典（Datadictionary）是一种用户可以访问的记录数据库和应用程序元数据的目录。主动数据字典是指在对数据库或应用程序结构进行修改时，其内容可以由DBMS自动更新的数据字典。被动数据字典是指修改时必须手工更新其内容的数据字典。词语“数据字典”在汉语词典中的解释索引页2.1什么是索引索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的列生成的键。这些键存储在B树结构中，使SQLServer可以快速有效地查找与键值关联的行。数据页…索引用于提高数据库从表或视图中查询数据的速度，改善数据库性能。根据索引键查找定位数据行2.1什么是索引SQLSERVER索引分为三类唯一索引：不允许有两行相同的索引值，一般在主键或创建了唯一约束的列上创建聚集索引：根据数据行的键值在表或视图中的排序和存储这些数据行，每个表只有一个聚集索引。非聚集索引：具有独立于数据行的结构，包含非聚集索引键值，且每个键值项都有指向包含该键值的数据行的指针。2.2如何创建索引使用SqlServerManagementStudio创建索引2.2如何创建索引使用T-SQL语句创建索引USEBankGOifexists(selectnamefromsysindexeswherename='index_card_no')begindropindexALL_PURPOSE_CARD.index_card_noendcreatenonclusteredindexindex_card_noonALL_PURPOSE_CARD(CARD_NO)withfillfactor=30GO在ALL_PURPOSE_CARD表的CARD_NO列上创建索引从系统视图检查要创建的索引是否已经存在填充因子，指取值范围是0-100，0等同于100，用于指定索引页填满的空间所占的百分比3.视图3.1什么是视图视图为虚拟表，其内容由查询定义。与真实表相同，视图包含一系列带有名称的列和行数据。但数据库中视图并不以存储的数据值集形式存在。视图中的行与列数据自由定义视图的查询所引用的表，并且在引用视图时动态生成。3.1什么是视图视图的作用：视点集中：允许用户只关心某些特定数据及特定任务简化操作：隐藏表与表之间复杂的连接操作定制数据：实现以不同的方式查看数据集合并分割数据：使用视图水平或垂直分割数据量大的表，可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视图来访问数据。安全性：视图可以作为一种安全机制，用户只能查看和修改其所能看到的数据，其他数据库或表不可见也不可以访问。3.2如何创建视图使用SqlServerManagementStudio创建视图3.2如何创建视图使用T-SQL语句创建视图。语法如下：createviewview_credit_detailasSELECTACCOUNT.ACCOUNT_NAME,CREDIT_CARD.CREDIT_CARD_NO,EXCHANGE.LOAN,EXCHANGE.EXTIME,EXCHANGE.EXADDRESS,EXCHANGE.PAY_MONTHFROMACCOUNTINNERJOINCREDIT_CARDONACCOUNT.ACCOUNT_ID=CREDIT_CARD.ACCOUNT_IDINNERJOINEXCHANGEONCREDIT_CARD.CREDIT_CARD_ID=EXCHANGE.CREDIT_CARD_IDGOCREATEVIEWviewnameASselect语句3.2如何创建视图使用视图USEBankgoselect*fromview_credit_detailwhereaccount_name='陆逊'andloanisnotnull本章总结事务具有ACID4个特性：原子性、一致性、隔离性、持久性事务可以分为3类：显示事务、隐式事务、自动提交事务T-SQL管理事务使用3条语句：BEGINTRANSACTIONCOMMITTRANSACTIONROLLBACKTRANSACTION建立索引有助于快速检索数据。索引分为：唯一索引、主键索引、聚集索引、非聚集索引。聚集索引用于决定表中数据的物理存放顺序，所以一个表只能有一个聚集索引；非聚集索引用于决定表的逻辑顺序，一个表可以有多个非聚集索引。视图是查看表中数据的方法，它是一种虚拟表，是作为执行查询结果而创建的；视图充当筛选器，可以按要求对表数据进行行和列的筛选。第五章函数和存储过程课程回顾事务具有ACID4个特性：原子性、一致性、隔离性、持久性事务可以分为3类：显示事务、隐式事务、自动提交事务T-SQL管理事务使用3条语句：BEGINTRANSACTIONCOMMITTRANSACTIONROLLBACKTRANSACTION建立索引有助于快速检索数据。索引分为：唯一索引、主键索引、聚集索引、非聚集索引。聚集索引决定表中数据的物理存放顺序，所以一个表只能有一个聚集索引；非聚集索引决定表的逻辑顺序，一个表可以有多个非聚集索引。视图是查看表中数据的方法，它是一种虚拟表，作为执行查询结果而创建；视图充当筛选器，可以按要求对表数据进行行和列的筛选。本章内容表值函数和标量值函数系统存储过程用