《SQL高级应用》PPT课件

第6章SQL高级应用,电子商务数据库技术,华北科技学院李文武,2,2020年5月7日星期四,6.1SELECT高级查询,6.1.1数据汇总1.聚合函数：对一组值操作，返回单一的汇总值。聚合函数在如下情况下，允许作为表达式使用：(1)SELECT语句的选择列表(子查询或外部查询)。(2)COMPUTE或COMPUTEBY子句。(3)HAVING子句。,华北科技学院李文武,3,2020年5月7日星期四,COMPUTE和COMPUTEBY,COMPUTE子句用于分类汇总，格式为：COMPUTE聚合函数名(expression),.nBYexpression,.n其中expression是列名；COMPUTE将产生额外的汇总行。COMPUTEBY子句可以用同一SELECT语句既查看明细行，又查看汇总行。可计算分组的汇总值，也可计算整个结果集的汇总值。,华北科技学院李文武,4,2020年5月7日星期四,COMPUTE生成的结果集,当COMPUTE带有可选的BY子句时，符合SELECT条件的每个组都有两个结果集：每个组的第一个结果集是明细行集，其中包含该组的选择列表信息。每个组的第二个结果集有一行，其中包含该组的COMPUTE子句中所指定的聚合函数的小计。当COMPUTE不带可选的BY子句时，SELECT语句有两个结果集：每个组的第一个结果集是包含选择列表信息的所有明细行。第二个结果集有一行，其中包含COMPUTE子句中所指定的聚合函数的合计。,华北科技学院李文武,5,2020年5月7日星期四,比较COMPUTE和GROUPBY,GROUPBY生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数。COMPUTE生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。另一类结果集包含组的子聚合，或SELECT语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其它表达式。聚合函数在COMPUTE子句中指定，而不是在选择列表中。,华北科技学院李文武,6,2020年5月7日星期四,2.GROUPBY子句,指定用来放置输出行的组，并且如果SELECT子句中包含聚合函数，则计算每组的汇总值。指定GROUPBY时，选择列表中任一非聚合表达式内的所有列都应包含在GROUPBY列表中，或者GROUPBY表达式必须与选择列表表达式完全匹配。语法GROUPBYALLgroup_by_expression,.nWITHCUBE|ROLLUP,华北科技学院李文武,7,2020年5月7日星期四,ALL：包含所有组和结果集，甚至包含那些任何行都不满足WHERE子句指定的搜索条件的组和结果集。如果指定了ALL，将对组中不满足搜索条件的汇总列返回空值。不能用CUBE或ROLLUP运算符指定ALL。如果访问远程表的查询中有WHERE子句，则不支持GROUPBYALL操作。group_by_expression：是对其执行分组的表达式。group_by_expression也称为分组列。group_byexpression可以是列或引用列的非聚合表达式。在选择列表内定义的列的别名不能用于指定分组列。,华北科技学院李文武,8,2020年5月7日星期四,CUBE：指定在结果集内不仅包含由GROUPBY提供的正常行，还包含汇总行。在结果集内返回每个可能的组和子组组合的GROUPBY汇总行。GROUPBY汇总行在结果中显示为NULL，但可用来表示所有值。使用GROUPING函数确定结果集内的空值是否是GROUPBY汇总值。ROLLUP：指定在结果集内不仅包含由GROUPBY提供的正常行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取决于指定分组列时所使用的顺序。更改分组列的顺序会影响在结果集内生成的行数。,华北科技学院李文武,9,2020年5月7日星期四,6.1.2联接查询,通过联接根据各表间的逻辑关系从两个或多个表中检索数据。定义方法：可以在FROM或WHERE子句中使用比较运算符给出联接条件对表进行联接。指定各表中用于联接的列。比较符可以是、=、!=、!联接条件与WHERE和HAVING子句的搜索条件配合完成查询。查询所选的行首先通过FROM子句联接条件进行筛选，其次由WHERE子句搜索条件筛选，然后由HAVING子句搜索条件筛选。,华北科技学院李文武,10,2020年5月7日星期四,1.内联接,仅显示两个联接表中的匹配行的联接。是查询分析器中的默认联接类型。当创建内联接时，包含NULL的列不与任何值匹配，因此不包括在结果集内。空值不与其它的空值匹配。关键词为：INNERJOIN,华北科技学院李文武,11,2020年5月7日星期四,2.外联接,包括在联接表中没有相关行的行的联接。左向外联接：包括第一个命名表（“左”表，出现在JOIN子句的最左边）中的所有行。不包括右表中的不匹配行。LEFTJOIN右向外联接：包括第二个命名表（“右”表，出现在JOIN子句的最右边）中的所有行。不包括左表中的不匹配行。RIGHTJOIN完整外部联接：包括所有联接表中的所有行，不论它们是否匹配。FULLJOIN,华北科技学院李文武,12,2020年5月7日星期四,3.交叉联接,在这类联接的结果集内，两个表中每两个可能成对的行占一行。交叉联接不使用WHERE子句。,华北科技学院李文武,13,2020年5月7日星期四,6.1.3子查询,子查询是一个SELECT查询，它返回单个值且嵌套在SELECT、INSERT、UPDATE、DELETE语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。,华北科技学院李文武,14,2020年5月7日星期四,嵌套在外部SELECT语句中的子查询包括以下组件：包含标准选择列表组件的标准SELECT查询。包含一个或多个表或者视图名的标准FROM子句。可选的WHERE子句。可选的GROUPBY子句。可选的HAVING子句。子查询的SELECT查询总是使用圆括号括起来。且不能包括COMPUTE或FORBROWSE子句，如果同时指定TOP子句，则可能只包括ORDERBY子句。,华北科技学院李文武,15,2020年5月7日星期四,子查询可以嵌套在外部SELECT、INSERT、UPDATE或DELETE语句的WHERE或HAVING子句内，或者其它子查询中。尽管根据可用内存和查询中其它表达式的复杂程度不同，嵌套限制也有所不同，但嵌套到32层是可能的。如果某个表只出现在子查询中而不出现在外部查询中，那么该表中的列就无法包含在输出中（外部查询的选择列表）。,华北科技学院李文武,16,2020年5月7日星期四,有三种基本的子查询。它们是：在通过IN引入的列表或者由ANY或ALL修改的比较运算符的列表上进行操作。通过无修改的比较运算符引入，且必须返回单个值。通过EXISTS引入的存在测试。通常采用以下格式中的一种：WHERE表达式NOTIN(子查询)WHERE表达式比较运算符ANY|ALL(子查询)WHERENOTEXISTS(子查询),华北科技学院李文武,17,2020年5月7日星期四,1.子查询规则,子查询受以下条件的限制：通过比较运算符引入的子查询的选择列表只能包括一个表达式或列名称（分别对SELECT*或列表进行EXISTS和IN操作除外）。如果外部查询的WHERE子句包括某个列名，则该子句必须与子查询选择列表中的该列在联接上兼容。子查询的选择列表中不允许出现ntext、text和image数据类型。由于必须返回单个值，由无修改的比较运算符（指其后未接关键字ANY或ALL）引入的子查询不能包括GROUPBY和HAVING子句。包括GROUPBY的子查询不能使用DISTINCT关键字。不能指定COMPUTE和INTO子句。只有同时指定了TOP，才可以指定ORDERBY。由子查询创建的视图不能更新。通过EXISTS引入的子查询的选择列表由星号(*)组成，而不使用单个列名。通过EXISTS引入的子查询进行了存在测试，返回TRUE或FALSE而非数据，所以这些子查询的规则与标准选择列表的规则完全相同。,华北科技学院李文武,18,2020年5月7日星期四,2.子查询类型,可以在许多地方指定子查询：使用别名时使用IN或NOTIN时在UPDATE、DELETE和INSERT语句中使用比较运算符时使用ANY、SOME或ALL时使用EXISTS或NOTEXISTS时在有表达式的地方,华北科技学院李文武,19,2020年5月7日星期四,使用IN或NOTIN的子查询,通过IN（或NOTIN）引入的子查询结果是一列零值或更多值。子查询返回结果之后，外部查询将利用这些结果。使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于，联接使您可以在结果中显示多个表中的列。联接总是可以表示为子查询。子查询经常（但不总是）可以表示为联接。这是因为联接是对称的：可以以任意顺序将表A联接到表B，而且会得到相同的答案。而对子查询来说，情况则并非如此。,华北科技学院李文武,20,2020年5月7日星期四,UPDATE、DELETE和INSERT语句中的子查询,子查询可以嵌套在UPDATE、DELETE和INSERT语句以及SELECT语句中。,华北科技学院李文武,21,2020年5月7日星期四,使用比较运算符的子查询,子查询可由一个比较运算符（=、=、,!或=）引入。与使用IN引入的子查询一样，由未修改的比较运算符（后面不跟ANY或ALL的比较运算符）引入的子查询必须返回单个值而不是值列表。如果这样的子查询返回多个值，将显示错误信息。要使用由无修改的比较运算符引入的子查询，必须对数据和问题的本质非常熟悉，以了解该子查询实际是否只返回一个值。,华北科技学院李文武,22,2020年5月7日星期四,使用EXISTS的子查询,使用EXISTS关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的WHERE子句测试子查询返回的行是否存在。子查询实际上不产生任何数据；它只返回TRUE或FALSE值。使用EXISTS引入的子查询语法如下：WHERENOTEXISTS(子查询),华北科技学院李文武,23,2020年5月7日星期四,3.多层嵌套,子查询自身可以包括一个或多个子查询。一个语句中可以嵌套任意数量的子查询。,华北科技学院李文武,24,2020年5月7日星期四,6.1.4在查询基础上创建新表,创建新表并将结果行从查询插入新表中。用户若要执行带INTO子句的SELECT语句，必须在目的数据库内具有CREATETABLE权限。SELECT.INTO不能与COMPUTE子句一起使用。,华北科技学院李文武,25,2020年5月7日星期四,6.2管理ntext、text、image数据,ntext、text和image数据类型在单个值中可以包含非常大的数据量（最大可达2GB）。单个数据值通常比应用程序在一个步骤中能够检索的大；某些值可能还会大于客户端的可用虚拟内存。因此，在检索这些值时，通常需要一些特殊的步骤。如果ntext、text和image数据值不超过Unicode串、字符串或二进制串的长度（分别为4,000个字符、8,000个字符和8,000个字节），就可以在SELECT、UPDATE和INSERT语句中引用它们，其引用方式与较小的数据类型相同。,华北科技学院李文武,26,2020年5月7日星期四,包含短值的ntext列可在SELECT语句的选择列表中引用，与nvarchar列的引用方式相同。引用时必须遵守一些限制，例如不能在WHERE子句中直接引用ntext、text或image列。这些列可以作为返回其它数据类型（例如ISNULL、SUBSTRING或PATINDEX）的某个函数的参数包含在WHERE子句中，也可以包含在ISNULL、ISNOTNULL或LIKE表达式中。,华北科技学院李文武,27,2020年5月7日星期四,6.3事务处理,事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务：原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如B树索引或双向链表）都必须是正确的。,华北科技学院李文武,28,2020年5月7日星期四,隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。,华北科技学院李文武,29,2020年5月7日星期四,6.3.1事务分类,SQLServer以三种事务模式运行：自动提交事务：每条单独的语句都是一个事务。是SQLServer的默认事务管理模式。每个T-SQL语句在完成时，都被提交或回滚。如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。自动提交模式也是ADO、OLEDB、ODBC和DB-Library的默认模式。,华北科技学院李文武,30,2020年5月7日星期四,显式事务：也称为用户定义或用户指定的事务。每个事务均以BEGINTRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。隐性事务：在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。,华北科技学院李文武,31,2020年5月7日星期四,6.3.2显式事务,1.启动事务语法BEGINTRANSACTIONtransaction_name|tran_name_variableWITHMARKdescription参数transaction_name：是给事务分配的名称。必须遵循标识符规则，但是不允许标识符多于32个字符。仅在嵌套的BEGIN.COMMIT或BEGIN.ROLLBACK语句的最外语句对上使用事务名。tran_name_variable：是用户定义的、含有有效事务名称的变量的名称。必须用char、varchar、nchar或nvarchar数据类型声明。WITHMARKdescription：指定在日志中标记事务。Description是描述该标记的字符串。如果使用了WITHMARK，则必须指定事务名。WITHMARK允许将事务日志还原到命名标记。,华北科技学院李文武,32,2020年5月7日星期四,2.结束事务,语法COMMITTRANSACTIONtransaction_name|tran_name_variable参数transaction_name：SQLServer忽略该参数。transaction_name指定由前面的BEGINTRANSACTION指派的事务名称。通过向程序员指明COMMITTRANSACTION与哪些嵌套的BEGINTRANSACTION相关联，transaction_name可作为帮助阅读的一种方法。tran_name_variable：是用户定义的、含有有效事务名称的变量的名称。必须用char、varchar、nchar或nvarchar数据类型声明该变量。,华北科技学院李文武,33,2020年5月7日星期四,结束一个成功的隐性事务或用户定义事务。COMMITWORK标志事务的结束。语法：COMMITWORK与COMMITTRANSACTION相同，但COMMITTRANSACTION接受用户定义的事务名称。,华北科技学院李文武,34,2020年5月7日星期四,3.回滚事务,语法ROLLBACKTRANSACTIONtransaction_name|tran_name_variable|savepoint_name|savepoint_variable参数transaction_name：是给BEGINTRANSACTION上的事务指派的名称。嵌套事务时，transaction_name必须是来自最远的BEGINTRANSACTION语句的名称。tran_name_variable：是用户定义的、含有有效事务名称的变量的名称。savepoint_name：是来自SAVETRANSACTION语句的savepoint_name。savepoint_name必须符合标识符规则。当条件回滚只影响事务的一部分时使用savepoint_name。savepoint_variable：是用户定义的、含有有效保存点名称的变量的名称。必须用char、varchar、nchar或nvarchar数据类型声明该变量。,华北科技学院李文武,35,2020年5月7日星期四,将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。ROLLBACKTRANSACTION清除自事务的起点或到某个保存点所做的所有数据修改。ROLLBACK还释放由事务控制的资源。ROLLBACKWORK将用户定义的事务回滚到事务的起点。语法：ROLLBACKWORK,华北科技学院李文武,36,2020年5月7日星期四,4.在事务中设置保存点,语法SAVETRANSACTIONsavepoint_name|savepoint_variable参数savepoint_name：是指派给保存点的名称。保存点名称必须符合标识符规则，但只使用前32个字符。savepoint_variable：是用户定义的、含有有效保存点名称的变量的名称。必须用char、varchar、nchar或nvarchar数据类型声明该变量。,华北科技学院李文武,37,2020年5月7日星期四,用户可以在事务内设置保存点或标记。保存点定义的是：如果有条件地取消事务的一部分，事务可以返回的位置。,华北科技学院李文武,38,2020年5月7日星期四,5.标记事务,WITHMARKdescription指定在日志中标记事务。将数据库还原到早期状态时，可使用标记事务替代日期和时间。Description是描述该标记的字符串。如果使用了WITHMARK，则必须指定事务名。WITHMARK允许将事务日志还原到命名标记。只有当数据库由标记事务更新时，才在事务日志中放置标记。不修改数据的事务不被标记。,华北科技学院李文武,39,2020年5月7日星期四,6.不能用于事务的操作,一些不能撤销的操作，其对数据库的操作是不能恢复的。如创建、修改、删除数据库等。P168,华北科技学院李文武,40,2020年5月7日星期四,6.3.3自动提交事务,编译错误将阻止SQLServer建立执行计划，这样批处理中的任何语句都不会执行。运行错误时，该语句之前的结果被保留下来。SQLServer使用延迟的名称解析，其中对象名直到执行时才被解析。所以错误的对象名不会在编译时指出。,华北科技学院李文武,41,2020年5月7日星期四,6.3.4隐式事务,在为连接将隐性事务模式设置为打开之后，当SQLServer首次执行下列任何语句时，都会自动启动一个事务：在COMMIT或ROLLBACK语句之前，该事务将一直保持有效。在第一个事务被提交或回滚之后，下次当连接执行这些语句中的任何语句时，SQLServer都将自动启动一个新事务。SQLServer将不断地生成一个隐性事务链，直到隐性事务模式关闭为止。,华北科技学院李文武,42,2020年5月7日星期四,SETIMPLICIT_TRANSACTIONSON语句启动隐性事务模式。SETIMPLICIT_TRANSACTIONSOFF语句关闭隐性事务模式。使用COMMITTRANSACTION、COMMITWORK、ROLLBACKTRANSACTION或ROLLBACKWORK语句结束每个事务。,华北科技学院李文武,43,2020年5月7日星期四,如果连接已经在打开的事务中，则上述语句不启动新事务。对于因为该设置为ON而自动打开的事务，用户必须在该事务结束时将其显式提交或回滚。否则当用户断开连接时，事务及其所包含的所有数据更改将回滚。在事务提交后，执行上述任一语句即可启动新事务。隐性事务模式将保持有效，直到连接执行SETIMPLICIT_TRANSACTIONSOFF语句使连接返回到自动提交模式。在自动提交模式下，如果各个语句成功完成则提交。,华北科技学院李文武,44,2020年5月7日星期四,6.4数据的锁定,通过锁定确保事务完整性和数据库的一致性。如：读取正由其他用户更改的数据多个用户同时更改同一数据,华北科技学院李文武,45,2020年5月7日星期四,6.4.1并发问题,如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。并发问题包括：丢失或覆盖更新。未确认的相关性（脏读）。不一致的分析（非重复读）。幻像读。,华北科技学院李文武,46,2020年5月7日星期四,1.丢失更新,当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。,华北科技学院李文武,47,2020年5月7日星期四,2.未确认的相关性（脏读）,当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。,华北科技学院李文武,48,2020年5月7日星期四,3.不一致的分析（非重复读）,当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。,华北科技学院李文武,49,2020年5月7日星期四,4.幻像读,当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。,华北科技学院李文武,50,2020年5月7日星期四,乐观和悲观并发,SQLServer2000提供了乐观并发控制和悲观并发控制。乐观并发控制使用游标。SQLServer默认使用悲观并发控制。乐观并发：乐观并发控制假定不太可能（但不是不可能）在多个用户间发生资源冲突，允许不锁定任何资源而执行事务。只有试图更改数据时才检查资源以确定是否发生冲突。如果发生冲突，应用程序必须读取数据并再次尝试进行更改。悲观并发：悲观并发控制根据需要在事务的持续时间内锁定资源。除非出现死锁，否则事务肯定会成功完成。,华北科技学院李文武,51,2020年5月7日星期四,6.4.2事务的隔离级别,当锁定用作并发控制机制时，它可以解决并发问题。这使所有事务得以在彼此完全隔离的环境中运行，但是任何时候都可以有多个正在运行的事务。尽管可串行性对于事务确保数据库中的数据在所有时间内的正确性相当重要，然而许多事务并不总是要求完全的隔离。事务准备接受不一致数据的级别称为隔离级别。隔离级别是一个事务必须与其它事务进行隔离的程度。较低的隔离级别可以增加并发，但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。应用程序要求的隔离级别确定了SQLServer使用的锁定行为。,华北科技学院李文武,52,2020年5月7日星期四,SQLServer支持所有这些隔离级别：未提交读（事务隔离的最低级别，仅可保证不读取物理损坏的数据）。提交读（SQLServer默认级别）。不允许脏读。可重复读。允许幻像读。可串行读（事务隔离的最高级别，事务之间完全隔离）。事务必须运行于可重复读或更高的隔离级别以防止丢失更新。当两个事务检索相同的行，然后基于原检索的值对行进行更新时，会发生丢失更新。如果两个事务使用一个UPDATE语句更新行，并且不基于以前检索的值进行更新，则在默认的提交读隔离级别不会发生丢失更新。,华北科技学院李文武,53,2020年5月7日星期四,6.4.3SQLServer中的锁定,SQLServer可以锁定以下资源（按粒度增加的顺序列出）。,华北科技学院李文武,54,2020年5月7日星期四,SQLServer使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。,华北科技学院李文武,55,2020年5月7日星期四,1.共享锁,共享(S)锁允许并发事务读取(SELECT)一个资源。资源上存在共享(S)锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享(S)锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享(S)锁。,华北科技学院李文武,56,2020年5月7日星期四,2.更新锁,更新(U)锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享(S)锁，然后修改行，此操作要求锁转换为排它(X)锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它(X)锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它(X)锁以进行更新。由于两个事务都要转换为排它(X)锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。若要避免这种潜在的死锁问题，使用更新(U)锁。一次只有一个事务可以获得资源的更新(U)锁。如果事务修改资源，则更新(U)锁转换为排它(X)锁。否则，锁转换为共享锁。,华北科技学院李文武,57,2020年5月7日星期四,3.排它锁,排它(X)锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它(X)锁锁定的数据。,华北科技学院李文武,58,2020年5月7日星期四,4.意向锁,意向锁表示SQLServer需要在层次结构中的某些底层资源上获取共享(S)锁或排它(X)锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享(S)锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它(X)锁。意向锁可以提高性能，因为SQLServer仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。,华北科技学院李文武,59,2020年5月7日星期四,意向锁包括意向共享(IS)、意向排它(IX)以及与意向排它共享(SIX)。,华北科技学院李文武,60,2020年5月7日星期四,5.架构锁,执行表的数据定义语言(DDL)操作（例如添加列或除去表）时使用架构修改(Sch-M)锁。当编译查询时，使用架构稳定性(Sch-S)锁。架构稳定性(Sch-S)锁不阻塞任何事务锁，包括排它(X)锁。因此在编译查询时，其它事务（包括在表上有排它(X)锁的事务）都能继续运行。但不能在表上执行DDL操作。,华北科技学院李文武,61,2020年5月7日星期四,6.大容量更新锁,当将数据大容量复制到表，且指定了TABLOCK提示或使用sp_tableoption设置了tablelockonbulk表选项时，使用大容量更新(BU)锁。大容量更新(BU)锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。,华北科技学院李文武,62,2020年5月7日星期四,7.锁兼容性,只有兼容的锁类型才可以放置在已锁定的资源上。当控制排它(X)锁时，在第一个事务结束并释放排它(X)锁之前，其它事务不能在该资源上获取任何类型的（共享、更新或排它）锁。另一种情况下，如果共享(S)锁已应用到资源，其它事务还可以获取该项目的共享锁或更新(U)锁，即使第一个事务尚未完成。但是，在释放共享锁之前，其它事务不能获取排它锁。,华北科技学院李文武,63,2020年5月7日星期四,资源锁模式有一个兼容性矩阵，显示了与在同一资源上可获取的其它锁相兼容的锁（按锁强度增长顺序列出）。,华北科技学院李文武,64,2020年5月7日星期四,意向排它(IX)锁与IX锁模式兼容，因为IX表示打算更新一些行而不是所有行。还允许其它事务读取或更新部分行，只要这些行不是其它事务当前所更新的行即可。架构稳定性(Sch-S)锁与除了架构修改(Sch-M)锁模式之外的所有锁模式相兼容。架构修改(Sch-M)锁与所有锁模式都不兼容。大容量更新(BU)锁只与架构稳定性(Sch-S)锁及其它大容量更新(BU)锁相兼容。,华北科技学院李文武,65,2020年5月7日星期四,6.4.4自定义锁,虽然SQLServer2000自动执行锁定，但它仍可以通过以下方法自定义应用程序中的锁定：处理死锁和设置死锁优先级。处理超时和设置锁超时持续时间。设置事务隔离级别。对SELECT、INSERT、UPDATE和DELETE语句使用表级锁定提示。配置索引的锁定粒度。,华北科技学院李文武,66,2020年5月7日星期四,1.死锁,当某组资源的两个或多个线程之间有循环相关性时，将发生死锁。死锁经常与正常阻塞混淆。当一个事务锁定了另一个事务需要的资源，第二个事务等待锁被释放。默认情况下，SQLServer事务不会超时（除非设置了LOCK_TIMEOUT）。第二个事务被阻塞，而不是被死锁。,华北科技学院李文武,67,2020年5月7日星期四,SQLServer在识别死锁后，通过自动选择可以打破死锁的线程（死锁牺牲品）来结束死锁。SQLServer回滚作为死锁牺牲品的事务，通知线程的应用程序（通过返回1205号错误信息），取消线程的当前请求，然后允许不间断线程的事务继续进行。SQLServer通常选择运行撤消时花费最少的事务的线程作为死锁牺牲品。,华北科技学院李文武,68,2020年5月7日星期四,下列方法有助于最大限度地降低死锁：按同一顺序访问对象。避免事务中的用户交互。保持事务简短并在一个批处理中。使用低隔离级别。使用绑定连接。,华北科技学院李文武,69,2020年5月7日星期四,2.自定义锁超时,LOCK_TIMEOUT设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于LOCK_TIMEOUT设置时，系统将自动取消阻塞的语句，并给应用程序返回“已超过了锁请求超时时段”的1222号错误信息。SQLServer不回滚或取消任何包含该语句的事务。因此，应用程序必须有捕获1222号错误信息的错误处理程序。如果应用程序没有捕获错误，则会继续运行，并未意识到事务中的个别语句已取消，从而当事务中的后续语句可能依赖于那条从未执行的语句时，导致应用程序出错。,华北科技学院李文武,70,2020年5月7日星期四,3.自定义事务隔离级别,语法SETTRANSACTIONISOLATIONLEVELREADCOMMITTED|READUNCOMMITTED|REPEATABLEREAD|SERIALIZABLE参数READCOMMITTED：提交读。该选项是SQLServer的默认值。READUNCOMMITTED：未提交读。是四个隔离级别中限制最小的。REPEATABLEREAD：可重复读。SERIALIZABLE：可串行读。这是四个隔离级别中限制最大的级别。一次只能设置这些选项中的一个，而且设置的选项将一直对那个连接保持有效，直到显式更改该选项为止。这是默认行为，除非在语句的FROM子句中在表级上指定优化选项。,华北科技学院李文武,71,2020年5月7日星期四,4.锁定提示,可以使用SELECT、INSERT、UPDATE和DELETE语句指定表级锁定提示的范围，以引导SQLServer2000使用所需的锁类型。当需要对对象所获得锁类型进行更精细控制时，可以使用表级锁定提示。这些锁定提示取代了会话的当前事务隔离级别。,华北科技学院李文武,72,2020年5月7日星期四,5.自定义索引的锁定,使用sp_indexoption系统存储过程设置用于索引的锁定粒度。若要显示给定索引的当前锁定选项，可用INDEXPROPERTY函数。,华北科技学院李文武,73,2020年5月7日星期四,6.5使用游标,关系数据库中的操作会对整个行集产生影响。由SELECT语句返回的行集包括所有满足该语句WHERE子句中条件的行。由语句所返回的这一完整的行集被称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的结果集扩展。,华北科技学院李文武,74,2020年5月7日星期四,6.5.1游标的概念,游标结果集：SELECT语句结果集。游标位置：指向集合中某一行的指针。游标通过以下方式扩展结果处理：允许定位在结果集的特定行。从结果集的当前位置检索一行或多行。支持对结果集中当前位置的行进行数据修改。为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。提供脚本、存储过程和触发器中使用的访问结果集中的数据的Transact-SQL语句。,华北科技学院李文武,75,2020年5月7日星期四,6.5.2使用游标,使用Transact-SQL游标的典型进程为：声明T-SQL变量包含游标返回的数据。为每一结果集列声明一个变量。声明足够大的变量以保存由列返回的值，并声明可从列数据类型以隐性方式转换得到的数据类型。使用DECLARECURSOR语句把T-SQL游标与一个SELECT语句相关联。DECLARECURSOR语句同时定义游标的特征，比如游标名称以及游标是否为只读或只进特性。使用OPEN语句执行SELECT语句并生成游标。,华北科技学院李文武,76,2020年5月7日星期四,使用FETCHINTO语句提取单个行，并把每列中的数据转移到指定的变量中。然后，其它T-SQL语句可以引用这些变量来访问已提取的数据值。T-SQL不支持提取行块。结束游标时，使用CLOSE语句。关闭游标可以释放某些资源，比如游标结果集和对当前行的锁定，但是如果重新发出一个OPEN语句，则该游标结构仍可用于处理。由于游标仍然存在，此时还不能重新使用游标的名称。DEALLOCATE语句则完全释放分配给游标的资源，包括游标名称。在游标被释放后，必须使用DECLARE语句来重新生成游标。,华北科技学院李文武,77,2020年5月7日星期四,1.声明游标,语法DECLAREcursor_nameINSENSITIVESCROLLCURSORFORselect_statementFORREADONLY|UPDATEOFcolumn_name,.ncursor_name：是所定义的Transact-SQL服务器游标名称。cursor_name必须遵从标识符规则。INSENSITIVE：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。,华北科技学院李文武,78,2020年5月7日星期四,SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。select_statement：是定义游标结果集的标准SELECT语句。在游标声明的select_statement内不允许使用关键字COMPUTE、COMPUTEBY、FORBROWSE和INTO。如果select_statement中的子句与所请求的游标类型的功能发生冲突，则SQLServer隐性地将游标转换为另一种类型。READONLY：在UPDATE或DELETE语句的WHERECURRENTOF子句中不能引用游标。该选项替代要更新的游标的默认功能。UPDATEOFcolumn_name,.n：定义游标内可更新的列。如果指定OFcolumn_name,.n参数，则只允许修改所列出的列。如果在UPDATE中未指定列的列表，则可以更新所有列。,华北科技学院李文武,79,2020年5月7日星期四,2.打开游标,语法OPENGLOBALcursor_name|cursor_variable_name参数GLOBAL：指定cursor_name指的是全局游标。cursor_name：已声明的游标的名称。如果全局游标和局部游标都使用cursor_name作为其名称，那么如果指定了GLOBAL，cursor_name指的是全局游标，否则cursor_name指的是局部游标。cursor_variable_name：游标变量的名称，该名称引用一个游标。只能打开已声明但没打开的游标。,华北科技学院李文武,80,2020年5月7日星期四,3.从打开的游标中提取行,语法FETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|nvar|RELATIVEn|nvarFROMGLOBALcursor_name|cursor_variable_nameINTOvariable_name,.n,华北科技学院李文武,81,2020年5月7日星期四,参数NEXT：返回紧跟当前行之后的结果行，并且当前行递增为结果行。如果FETCHNEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。PRIO