第6章-SQL高级应用教学课件

第6章 SQL 高级应用电子商务数据库技术7/12/20241华北科技学院 李文武6.1SELECT高级查询高级查询6.1.1数据汇总数据汇总1.聚合函数：对一组值操作，返回单一的汇总值。聚合函数：对一组值操作，返回单一的汇总值。聚合函数在如下情况下，允许作为表达式使用：聚合函数在如下情况下，允许作为表达式使用：(1)SELECT语句的选择列表语句的选择列表(子查询或外部查询子查询或外部查询)。(2)COMPUTE或或COMPUTEBY子句。子句。(3)HAVING子句。子句。7/12/20242华北科技学院 李文武COMPUTE和和COMPUTEBYnCOMPUTE子句用于分类汇总，格式为：子句用于分类汇总，格式为：COMPUTE聚合函数名聚合函数名(expression),.nBYexpression,.nn其中其中expression是列名；是列名；COMPUTE将产生额将产生额外的汇总行。外的汇总行。nCOMPUTEBY子句可以用同一子句可以用同一SELECT语句语句既查看明细行，又查看汇总行。可计算分组的既查看明细行，又查看汇总行。可计算分组的汇总值，也可计算整个结果集的汇总值。汇总值，也可计算整个结果集的汇总值。7/12/20243华北科技学院 李文武COMPUTE生成的结果集生成的结果集n当当COMPUTE带有可选的带有可选的BY子句时，符合子句时，符合SELECT条件的每个组都有两个结果集：条件的每个组都有两个结果集：每个组的第一个结果集是明细行集，其中包含该组的选择列每个组的第一个结果集是明细行集，其中包含该组的选择列表信息。表信息。每个组的第二个结果集有一行，其中包含该组的每个组的第二个结果集有一行，其中包含该组的COMPUTE子句中所指定的聚合函数的小计。子句中所指定的聚合函数的小计。n当当COMPUTE不带可选的不带可选的BY子句时，子句时，SELECT语句语句有两个结果集：有两个结果集：每个组的第一个结果集是包含选择列表信息的所有明细行。每个组的第一个结果集是包含选择列表信息的所有明细行。第二个结果集有一行，其中包含第二个结果集有一行，其中包含COMPUTE子句中所指定的子句中所指定的聚合函数的合计。聚合函数的合计。7/12/20244华北科技学院 李文武比较比较COMPUTE和和GROUPBYnGROUPBY生成单个结果集。每个组都有一个只包含生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数。表只能包含分组依据列和聚合函数。nCOMPUTE生成多个结果集。生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表一类结果集包含每个组的明细行，其中包含选择列表中的表达式。达式。另一类结果集包含组的子聚合，或另一类结果集包含组的子聚合，或SELECT语句的总聚合。语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其它表达式。选择列表可包含除分组依据列或聚合函数之外的其它表达式。聚合函数在聚合函数在COMPUTE子句中指定，而不是在选择列表中。子句中指定，而不是在选择列表中。7/12/20245华北科技学院 李文武2.GROUPBY子句子句n指定用来放置输出行的组，并且如果指定用来放置输出行的组，并且如果SELECT子句子句中包含聚合函数，则计算每中包含聚合函数，则计算每组的汇总值。指定组的汇总值。指定GROUPBY时，选择列表中时，选择列表中任一非聚合表达式内的所有列都应包含在任一非聚合表达式内的所有列都应包含在GROUPBY列表中，或者列表中，或者GROUPBY表达式表达式必须与选择列表表达式完全匹配。必须与选择列表表达式完全匹配。n语法语法GROUPBYALLgroup_by_expression,.nWITHCUBE|ROLLUP7/12/20246华北科技学院 李文武nALL：包含所有组和结果集，甚至包含那些任何行都不：包含所有组和结果集，甚至包含那些任何行都不满足满足WHERE子句指定的搜索条件的组和结果集。如子句指定的搜索条件的组和结果集。如果指定了果指定了ALL，将对组中不满足搜索条件的汇总列返回，将对组中不满足搜索条件的汇总列返回空值。不能用空值。不能用CUBE或或ROLLUP运算符指定运算符指定ALL。如果访问远程表的查询中有如果访问远程表的查询中有WHERE子句，则不支持子句，则不支持GROUPBYALL操作。操作。ngroup_by_expression：是对其执行分组的表达式。是对其执行分组的表达式。group_by_expression 也称为分组列。也称为分组列。group_by expression 可以是列或引用列的非聚合表达式。在选可以是列或引用列的非聚合表达式。在选择列表内定义的列的别名不能用于指定分组列。择列表内定义的列的别名不能用于指定分组列。7/12/20247华北科技学院 李文武nCUBE：指定在结果集内不仅包含由：指定在结果集内不仅包含由GROUPBY提供提供的正常行，还包含汇总行。在结果集内返回每个可能的的正常行，还包含汇总行。在结果集内返回每个可能的组和子组组合的组和子组组合的GROUPBY汇总行。汇总行。GROUPBY汇汇总行在结果中显示为总行在结果中显示为NULL，但可用来表示所有值。使，但可用来表示所有值。使用用GROUPING函数确定结果集内的空值是否是函数确定结果集内的空值是否是GROUPBY汇总值。汇总值。nROLLUP：指定在结果集内不仅包含由：指定在结果集内不仅包含由GROUPBY提提供的正常行，还包含汇总行。按层次结构顺序，从组内供的正常行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取决于指的最低级别到最高级别汇总组。组的层次结构取决于指定分组列时所使用的顺序。更改分组列的顺序会影响在定分组列时所使用的顺序。更改分组列的顺序会影响在结果集内生成的行数。结果集内生成的行数。7/12/20248华北科技学院 李文武6.1.2联接查询联接查询n通过联接根据各表间的逻辑关系从两个或多个通过联接根据各表间的逻辑关系从两个或多个表中检索数据。表中检索数据。n定义方法：定义方法：可以在可以在FROM或或WHERE子句中使用比较运算符给子句中使用比较运算符给出联接条件对表进行联接。出联接条件对表进行联接。指定各表中用于联接的列。指定各表中用于联接的列。比较符可以是比较符可以是、=、!=、!联接条件与联接条件与WHERE和和HAVING子句的搜索条件配子句的搜索条件配合完成查询。查询所选的行首先通过合完成查询。查询所选的行首先通过FROM子句子句联接条件进行筛选，其次由联接条件进行筛选，其次由WHERE子句搜索条子句搜索条件筛选，然后由件筛选，然后由HAVING子句搜索条件筛选子句搜索条件筛选。7/12/20249华北科技学院 李文武1.内联接内联接n仅显示两个联接表中的匹配行的联接。是查询仅显示两个联接表中的匹配行的联接。是查询分析器中的默认联接类型。分析器中的默认联接类型。n当创建内联接时，包含当创建内联接时，包含NULL的列不与任何值的列不与任何值匹配，因此不包括在结果集内。空值不与其它匹配，因此不包括在结果集内。空值不与其它的空值匹配。的空值匹配。n关键词为：关键词为：INNERJOIN7/12/202410华北科技学院 李文武2.外联接外联接n包括在联接表中没有相关行的行的联接。包括在联接表中没有相关行的行的联接。n左向外联接：包括第一个命名表（左向外联接：包括第一个命名表（“左左”表，出表，出现在现在JOIN子句的最左边）中的所有行。不包子句的最左边）中的所有行。不包括右表中的不匹配行。括右表中的不匹配行。LEFTJOINn右向外联接：包括第二个命名表（右向外联接：包括第二个命名表（“右右”表，出表，出现在现在JOIN子句的最右边）中的所有行。不包子句的最右边）中的所有行。不包括左表中的不匹配行。括左表中的不匹配行。RIGHTJOINn完整外部联接：包括所有联接表中的所有行，完整外部联接：包括所有联接表中的所有行，不论它们是否匹配。不论它们是否匹配。FULLJOIN7/12/202411华北科技学院 李文武3.交叉联接交叉联接n在这类联接的结果集内，两个表中每两个可能在这类联接的结果集内，两个表中每两个可能成对的行占一行。成对的行占一行。n交叉联接不使用交叉联接不使用WHERE子句。子句。7/12/202412华北科技学院 李文武6.1.3子查询子查询n子查询是一个子查询是一个SELECT查询，它返回单个值查询，它返回单个值且嵌套在且嵌套在SELECT、INSERT、UPDATE、DELETE语句或其它子查询中。任何允许使用语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。表达式的地方都可以使用子查询。n子查询也称为内部查询或内部选择，而包含子子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。查询的语句也称为外部查询或外部选择。7/12/202413华北科技学院 李文武n嵌套在外部嵌套在外部SELECT语句中的子查询包括以下语句中的子查询包括以下组件：组件：包含标准选择列表组件的标准包含标准选择列表组件的标准SELECT查询。查询。包含一个或多个表或者视图名的标准包含一个或多个表或者视图名的标准FROM子句。子句。可选的可选的WHERE子句。子句。可选的可选的GROUPBY子句。子句。可选的可选的HAVING子句。子句。n子查询的子查询的SELECT查询总是使用圆括号括起来。查询总是使用圆括号括起来。且不能包括且不能包括COMPUTE或或FORBROWSE子子句，如果同时指定句，如果同时指定TOP子句，则可能只包括子句，则可能只包括ORDERBY子句。子句。7/12/202414华北科技学院 李文武n子查询可以嵌套在外部子查询可以嵌套在外部SELECT、INSERT、UPDATE或或DELETE语句的语句的WHERE或或HAVING子句内，或者其它子查询中。子句内，或者其它子查询中。n尽管根据可用内存和查询中其它表达式的复杂尽管根据可用内存和查询中其它表达式的复杂程度不同，嵌套限制也有所不同，但嵌套到程度不同，嵌套限制也有所不同，但嵌套到32层是可能的。层是可能的。n如果某个表只出现在子查询中而不出现在外部如果某个表只出现在子查询中而不出现在外部查询中，那么该表中的列就无法包含在输出中查询中，那么该表中的列就无法包含在输出中（外部查询的选择列表）。（外部查询的选择列表）。7/12/202415华北科技学院 李文武n有三种基本的子查询。它们是：有三种基本的子查询。它们是：在通过在通过IN引入的列表或者由引入的列表或者由ANY或或ALL修改的比修改的比较运算符的列表上进行操作。较运算符的列表上进行操作。通过无修改的比较运算符引入，且必须返回单个值。通过无修改的比较运算符引入，且必须返回单个值。通过通过EXISTS引入的存在测试。引入的存在测试。n通常采用以下格式中的一种：通常采用以下格式中的一种：WHERE表达式表达式NOTIN(子查询子查询)WHERE表达式表达式 比较运算符比较运算符ANY|ALL(子查询子查询)WHERENOTEXISTS(子查询子查询)7/12/202416华北科技学院 李文武1.子查询规则子查询规则n子查询受以下条件的限制：子查询受以下条件的限制：通过比较运算符引入的子查询的选择列表只能包括一个表达式或列名称通过比较运算符引入的子查询的选择列表只能包括一个表达式或列名称（分别对（分别对SELECT*或列表进行或列表进行EXISTS和和IN操作除外）。操作除外）。如果外部查询的如果外部查询的WHERE子句包括某个列名，则该子句必须与子查询选择子句包括某个列名，则该子句必须与子查询选择列表中的该列在联接上兼容。列表中的该列在联接上兼容。子查询的选择列表中不允许出现子查询的选择列表中不允许出现ntext、text和和image数据类型。数据类型。由于必须返回单个值，由无修改的比较运算符（指其后未接关键字由于必须返回单个值，由无修改的比较运算符（指其后未接关键字ANY或或ALL）引入的子查询不能包括）引入的子查询不能包括GROUPBY和和HAVING子句。子句。包括包括GROUPBY的子查询不能使用的子查询不能使用DISTINCT关键字。关键字。不能指定不能指定COMPUTE和和INTO子句。子句。只有同时指定了只有同时指定了TOP，才可以指定，才可以指定ORDERBY。由子查询创建的视图不能更新。由子查询创建的视图不能更新。通过通过EXISTS引入的子查询的选择列表由星号引入的子查询的选择列表由星号(*)组成，而不使用单个列组成，而不使用单个列名。通过名。通过EXISTS引入的子查询进行了存在测试，返回引入的子查询进行了存在测试，返回TRUE或或FALSE而非数据，所以这些子查询的规则与标准选择列表的规则完全相同。而非数据，所以这些子查询的规则与标准选择列表的规则完全相同。7/12/202417华北科技学院 李文武2.子查询类型子查询类型n可以在许多地方指定子查询：可以在许多地方指定子查询：使用别名时使用别名时使用使用IN或或NOTIN时时在在UPDATE、DELETE和和INSERT语句中语句中使用比较运算符时使用比较运算符时使用使用ANY、SOME或或ALL时时使用使用EXISTS或或NOTEXISTS时时在有表达式的地方在有表达式的地方7/12/202418华北科技学院 李文武使用使用IN或或NOTIN的子查询的子查询n通过通过IN（或（或NOTIN）引入的子查询结果是一列零值）引入的子查询结果是一列零值或更多值。子查询返回结果之后，外部查询将利用这或更多值。子查询返回结果之后，外部查询将利用这些结果。些结果。n使用联接而不使用子查询处理该问题及类似问题的一使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于，联接使您可以在结果中显示多个表个不同之处在于，联接使您可以在结果中显示多个表中的列。中的列。n联接总是可以表示为子查询。子查询经常（但不总是）联接总是可以表示为子查询。子查询经常（但不总是）可以表示为联接。这是因为联接是对称的：可以以任可以表示为联接。这是因为联接是对称的：可以以任意顺序将表意顺序将表A联接到表联接到表B，而且会得到相同的答案。，而且会得到相同的答案。而对子查询来说，情况则并非如此。而对子查询来说，情况则并非如此。7/12/202419华北科技学院 李文武UPDATE、DELETE和和INSERT语句中的子查询语句中的子查询n子查询可以嵌套在子查询可以嵌套在UPDATE、DELETE和和INSERT语句以及语句以及SELECT语句中。语句中。7/12/202420华北科技学院 李文武使用比较运算符的子查询使用比较运算符的子查询n子查询可由一个比较运算符（子查询可由一个比较运算符（=、=、,!或或=）引入。）引入。n与使用与使用IN引入的子查询一样，由未修改的比引入的子查询一样，由未修改的比较运算符（后面不跟较运算符（后面不跟ANY或或ALL的比较运算的比较运算符）引入的子查询必须返回单个值而不是值列符）引入的子查询必须返回单个值而不是值列表。如果这样的子查询返回多个值，将显示错表。如果这样的子查询返回多个值，将显示错误信息。误信息。n要使用由无修改的比较运算符引入的子查询，要使用由无修改的比较运算符引入的子查询，必须对数据和问题的本质非常熟悉，以了解该必须对数据和问题的本质非常熟悉，以了解该子查询实际是否只返回一个值。子查询实际是否只返回一个值。7/12/202421华北科技学院 李文武使用使用EXISTS的子查询的子查询n使用使用EXISTS关键字引入一个子查询时，就相关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的当于进行一次存在测试。外部查询的WHERE子句测试子查询返回的行是否存在。子查询实子句测试子查询返回的行是否存在。子查询实际上不产生任何数据；它只返回际上不产生任何数据；它只返回TRUE或或FALSE值。值。n使用使用EXISTS引入的子查询语法如下：引入的子查询语法如下：WHERENOTEXISTS(子查询子查询)7/12/202422华北科技学院 李文武3.多层嵌套多层嵌套n子查询自身可以包括一个或多个子查询。一个子查询自身可以包括一个或多个子查询。一个语句中可以嵌套任意数量的子查询。语句中可以嵌套任意数量的子查询。7/12/202423华北科技学院 李文武6.1.4在查询基础上创建新表在查询基础上创建新表n创建新表并将结果行从查询插入新表中。创建新表并将结果行从查询插入新表中。n用户若要执行带用户若要执行带INTO子句的子句的SELECT语句，语句，必须在目的数据库内具有必须在目的数据库内具有CREATETABLE权权限。限。SELECT.INTO不能与不能与COMPUTE子句子句一起使用。一起使用。7/12/202424华北科技学院 李文武6.2管理管理ntext、text、image数据数据nntext、text和和image数据类型在单个值中可以包含数据类型在单个值中可以包含非常大的数据量（最大可达非常大的数据量（最大可达2GB）。单个数据值通）。单个数据值通常比应用程序在一个步骤中能够检索的大；某些值可常比应用程序在一个步骤中能够检索的大；某些值可能还会大于客户端的可用虚拟内存。因此，在检索这能还会大于客户端的可用虚拟内存。因此，在检索这些值时，通常需要一些特殊的步骤。些值时，通常需要一些特殊的步骤。n如果如果ntext、text和和image数据值不超过数据值不超过Unicode串、字符串或二进制串的长度（分别为串、字符串或二进制串的长度（分别为4,000个字符、个字符、8,000个字符和个字符和8,000个字节），就可以在个字节），就可以在SELECT、UPDATE和和INSERT语句中引用它们，其引用方式语句中引用它们，其引用方式与较小的数据类型相同。与较小的数据类型相同。7/12/202425华北科技学院 李文武n包含短值的包含短值的ntext列可在列可在SELECT语句的选择语句的选择列表中引用，与列表中引用，与nvarchar列的引用方式相同。列的引用方式相同。n引用时必须遵守一些限制，例如不能在引用时必须遵守一些限制，例如不能在WHERE子句中直接引用子句中直接引用ntext、text或或image列。列。n这些列可以作为返回其它数据类型（例如这些列可以作为返回其它数据类型（例如ISNULL、SUBSTRING或或PATINDEX）的某个）的某个函数的参数包含在函数的参数包含在WHERE子句中，也可以包子句中，也可以包含在含在ISNULL、ISNOTNULL或或LIKE表达式表达式中。中。7/12/202426华北科技学院 李文武6.3事务处理事务处理n事务是作为单个逻辑工作单元执行的一系列操作。事务是作为单个逻辑工作单元执行的一系列操作。n一个逻辑工作单元必须有四个属性，称为一个逻辑工作单元必须有四个属性，称为ACID（原子（原子性、一致性、隔离性和持久性）属性，只有这样才能成性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务：为一个事务：原子性：事务必须是原子工作单元；对于其数据修改，要么原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。全都执行，要么全都不执行。一致性：事务在完成时，必须使所有的数据都保持一致状态。一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（持所有数据的完整性。事务结束时，所有的内部数据结构（如如B树索引或双向链表）都必须是正确的。树索引或双向链表）都必须是正确的。7/12/202427华北科技学院 李文武隔离性：由并发事务所作的修改必须与任何其它隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。结束时的状态与原始事务执行的状态相同。持久性：事务完成之后，它对于系统的影响是永持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。久性的。该修改即使出现系统故障也将一直保持。7/12/202428华北科技学院 李文武6.3.1事务分类事务分类nSQLServer以三种事务模式运行：以三种事务模式运行：自动提交事务：每条单独的语句都是一个事务。自动提交事务：每条单独的语句都是一个事务。是是SQLServer的默认事务管理模式。的默认事务管理模式。n每个每个T-SQL语句在完成时，都被提交或回滚。语句在完成时，都被提交或回滚。n如果一个语句成功地完成，则提交该语句；如果遇到错如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。误，则回滚该语句。n自动提交模式也是自动提交模式也是ADO、OLEDB、ODBC和和DB-Library的默认模式。的默认模式。7/12/202429华北科技学院 李文武显式事务：也称为用户定义或用户指定的事务。显式事务：也称为用户定义或用户指定的事务。每个事务均以每个事务均以BEGINTRANSACTION语句显式语句显式开始，以开始，以COMMIT或或ROLLBACK语句显式结束。语句显式结束。隐性事务：在前一个事务完成时新事务隐式启动，隐性事务：在前一个事务完成时新事务隐式启动，但每个事务仍以但每个事务仍以COMMIT或或ROLLBACK语句显语句显式完成。式完成。7/12/202430华北科技学院 李文武6.3.2显式事务显式事务n1.启动事务启动事务语法语法nBEGINTRANSACTIONtransaction_name|tran_name_variableWITHMARKdescription参数参数ntransaction_name：是给事务分配的名称。必须遵循标识符规则，但是给事务分配的名称。必须遵循标识符规则，但是不允许标识符多于是不允许标识符多于32个字符。仅在嵌套的个字符。仅在嵌套的BEGIN.COMMIT或或BEGIN.ROLLBACK语句的最外语句对上使用事务名。语句的最外语句对上使用事务名。ntran_name_variable：是用户定义的、含有有效事务名称的变量的名是用户定义的、含有有效事务名称的变量的名称。必须用称。必须用char、varchar、nchar或或nvarchar数据类型声明。数据类型声明。nWITHMARKdescription：指定在日志中标记事务。：指定在日志中标记事务。Description 是描述该标记的字符串。如果使用了是描述该标记的字符串。如果使用了WITHMARK，则必须指定事务，则必须指定事务名。名。WITHMARK允许将事务日志还原到命名标记。允许将事务日志还原到命名标记。7/12/202431华北科技学院 李文武2.结束事务结束事务n语法语法COMMITTRANSACTIONtransaction_name|tran_name_variable n参数参数transaction_name：SQLServer忽略该参数。忽略该参数。transaction_name指定由前面的指定由前面的BEGINTRANSACTION指派的事务名称。通过向程序员指明指派的事务名称。通过向程序员指明COMMITTRANSACTION与哪些嵌套的与哪些嵌套的BEGINTRANSACTION相关相关联，联，transaction_name可作为帮助阅读的一种方法。可作为帮助阅读的一种方法。tran_name_variable：是用户定义的、含有有效事务名称的是用户定义的、含有有效事务名称的变量的名称。必须用变量的名称。必须用char、varchar、nchar或或nvarchar数数据类型声明该变量。据类型声明该变量。7/12/202432华北科技学院 李文武n结束一个成功的隐性事务或用户定义事务。结束一个成功的隐性事务或用户定义事务。nCOMMITWORK标志事务的结束。标志事务的结束。语法：语法：COMMITWORK与与COMMITTRANSACTION相同，但相同，但COMMITTRANSACTION接受用户定义的事务名称。接受用户定义的事务名称。7/12/202433华北科技学院 李文武3.回滚事务回滚事务n语法语法ROLLBACKTRANSACTIONtransaction_name|tran_name_variable|savepoint_name|savepoint_variable n参数参数transaction_name：是给是给BEGINTRANSACTION上的事务上的事务指派的名称。嵌套事务时，指派的名称。嵌套事务时，transaction_name必须是来自最必须是来自最远的远的BEGINTRANSACTION语句的名称。语句的名称。tran_name_variable：是用户定义的、含有有效事务名称的是用户定义的、含有有效事务名称的变量的名称。变量的名称。savepoint_name：是来自是来自SAVETRANSACTION语句的语句的savepoint_name。savepoint_name 必须符合标识符规则。必须符合标识符规则。当条件回滚只影响事务的一部分时使用当条件回滚只影响事务的一部分时使用savepoint_name。savepoint_variable：是用户定义的、含有有效保存点名称是用户定义的、含有有效保存点名称的变量的名称。必须用的变量的名称。必须用char、varchar、nchar或或nvarchar数据类型声明该变量。数据类型声明该变量。7/12/202434华北科技学院 李文武n将显式事务或隐性事务回滚到事务的起点或事将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。务内的某个保存点。nROLLBACKTRANSACTION清除自事务的清除自事务的起点或到某个保存点所做的所有数据修改。起点或到某个保存点所做的所有数据修改。ROLLBACK还释放由事务控制的资源。还释放由事务控制的资源。nROLLBACKWORK将用户定义的事务回滚到事务的起点。将用户定义的事务回滚到事务的起点。语法：语法：ROLLBACKWORK7/12/202435华北科技学院 李文武4.在事务中设置保存点在事务中设置保存点n语法语法SAVETRANSACTIONsavepoint_name|savepoint_variable n参数参数savepoint_name：是指派给保存点的名称。保存点是指派给保存点的名称。保存点名称必须符合标识符规则，但只使用前名称必须符合标识符规则，但只使用前32个字符。个字符。savepoint_variable：是用户定义的、含有有效保存是用户定义的、含有有效保存点名称的变量的名称。必须用点名称的变量的名称。必须用char、varchar、nchar或或nvarchar数据类型声明该变量。数据类型声明该变量。7/12/202436华北科技学院 李文武n用户可以在事务内设置保存点或标记。保存点用户可以在事务内设置保存点或标记。保存点定义的是：如果有条件地取消事务的一部分，定义的是：如果有条件地取消事务的一部分，事务可以返回的位置。事务可以返回的位置。7/12/202437华北科技学院 李文武5.标记事务标记事务nWITHMARKdescriptionn指定在日志中标记事务。将数据库还原到早期指定在日志中标记事务。将数据库还原到早期状态时，可使用标记事务替代日期和时间。状态时，可使用标记事务替代日期和时间。Description 是描述该标记的字符串。是描述该标记的字符串。n如果使用了如果使用了WITHMARK，则必须指定事务名。，则必须指定事务名。WITHMARK允许将事务日志还原到命名标记。允许将事务日志还原到命名标记。n只有当数据库由标记事务更新时，才在事务日只有当数据库由标记事务更新时，才在事务日志中放置标记。不修改数据的事务不被标记。志中放置标记。不修改数据的事务不被标记。7/12/202438华北科技学院 李文武6.不能用于事务的操作不能用于事务的操作n一些不能撤销的操作，其对数据库的操作是不一些不能撤销的操作，其对数据库的操作是不能恢复的。如创建、修改、删除数据库等。能恢复的。如创建、修改、删除数据库等。nP1687/12/202439华北科技学院 李文武6.3.3自动提交事务自动提交事务n编译错误将阻止编译错误将阻止SQLServer建立执行计划，建立执行计划，这样批处理中的任何语句都不会执行。这样批处理中的任何语句都不会执行。n运行错误时，该语句之前的结果被保留下来。运行错误时，该语句之前的结果被保留下来。nSQLServer使用延迟的名称解析，其中对象使用延迟的名称解析，其中对象名直到执行时才被解析。名直到执行时才被解析。所以错误的对象名不所以错误的对象名不会在编译时指出。会在编译时指出。7/12/202440华北科技学院 李文武6.3.4隐式事务隐式事务n在为连接将隐性事务模式设置为打开之后，当在为连接将隐性事务模式设置为打开之后，当SQLServer首次执行下列任何语句时，都会自动启动一首次执行下列任何语句时，都会自动启动一个事务：个事务：n在在COMMIT或或ROLLBACK语句之前，该事务将一语句之前，该事务将一直保持有效。在第一个事务被提交或回滚之后，下次直保持有效。在第一个事务被提交或回滚之后，下次当连接执行这些语句中的任何语句时，当连接执行这些语句中的任何语句时，SQLServer都将自动启动一个新事务。都将自动启动一个新事务。SQLServer将不断地生将不断地生成一个隐性事务链，直到隐性事务模式关闭为止。成一个隐性事务链，直到隐性事务模式关闭为止。ALTERTABLEINSERTCREATEOPENDELETEREVOKEDROPSELECTFETCHTRUNCATETABLEGRANTUPDATE7/12/202441华北科技学院 李文武nSETIMPLICIT_TRANSACTIONSON语句启语句启动隐性事务模式。动隐性事务模式。nSETIMPLICIT_TRANSACTIONSOFF语句语句关闭隐性事务模式。关闭隐性事务模式。n使用使用COMMITTRANSACTION、COMMITWORK、ROLLBACKTRANSACTION或或ROLLBACKWORK语句结束每个事务。语句结束每个事务。7/12/202442华北科技学院 李文武n如果连接已经在打开的事务中，则上述语句不启动新如果连接已经在打开的事务中，则上述语句不启动新事务。事务。n对于因为该设置为对于因为该设置为ON而自动打开的事务，用户必须而自动打开的事务，用户必须在该事务结束时将其显式提交或回滚。否则当用户断在该事务结束时将其显式提交或回滚。否则当用户断开连接时，事务及其所包含的所有数据更改将回滚。开连接时，事务及其所包含的所有数据更改将回滚。在事务提交后，执行上述任一语句即可启动新事务。在事务提交后，执行上述任一语句即可启动新事务。n隐性事务模式将保持有效，直到连接执行隐性事务模式将保持有效，直到连接执行SETIMPLICIT_TRANSACTIONSOFF语句使连接返回语句使连接返回到自动提交模式。在自动提交模式下，如果各个语句到自动提交模式。在自动提交模式下，如果各个语句成功完成则提交。成功完成则提交。7/12/202443华北科技学院 李文武6.4数据的锁定数据的锁定n通过锁定确保事务完整性和数据库的一致性。通过锁定确保事务完整性和数据库的一致性。n如：如：读取正由其他用户更改的数据读取正由其他用户更改的数据多个用户同时更改同一数据多个用户同时更改同一数据7/12/202444华北科技学院 李文武6.4.1并发问题并发问题n如果没有锁定且多个用户同时访问一个数据库，如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会则当他们的事务同时使用相同的数据时可能会发生问题。并发问题包括：发生问题。并发问题包括：丢失或覆盖更新。丢失或覆盖更新。未确认的相关性（未确认的相关性（脏读脏读）。）。不一致的分析（不一致的分析（非重复读非重复读）。）。幻像读。幻像读。7/12/202445华北科技学院 李文武1.丢失更新丢失更新n当两个或多个事务选择同一行，然后基于最初当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。每个事务都不知道其它事务的存在。n最后的更新将重写由其它事务所做的更新，这最后的更新将重写由其它事务所做的更新，这将导致数据丢失。将导致数据丢失。7/12/202446华北科技学院 李文武2.未确认的相关性（脏读）未确认的相关性（脏读）n当第二个事务选择其它事务正在更新的行时，当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。会发生未确认的相关性问题。n第二个事务正在读取的数据还没有确认并且可第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。能由更新此行的事务所更改。7/12/202447华北科技学院 李文武3.不一致的分析（非重复读）不一致的分析（非重复读）n当第二个事务多次访问同一行而且每次读取不当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。同的数据时，会发生不一致的分析问题。n不一致的分析与未确认的相关性类似，因为其不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数它事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。取的数据是由已进行了更改的事务提交的。n而且，不一致的分析涉及多次（两次或更多）而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。因而该行被非重复读取。7/12/202448华北科技学院 李文武4.幻像读幻像读n当对某行执行插入或删除操作，而该行属于某当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读个事务正在读取的行的范围时，会发生幻像读问题。问题。n事务第一次读的行范围显示出其中一行已不复事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其存在于第二次读或后续读中，因为该行已被其它事务删除。它事务删除。n同样，由于其它事务的插入操作，事务的第二同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。次或后续读显示有一行已不存在于原始读中。7/12/202449华北科技学院 李文武乐观和悲观并发乐观和悲观并发nSQLServer2000提供了乐观并发控制和悲观提供了乐观并发控制和悲观并发控制。乐观并发控制使用游标。并发控制。乐观并发控制使用游标。SQLServer默认使用悲观并发控制。默认使用悲观并发控制。乐观并发：乐观并发控制假定不太可能（但不是乐观并发：乐观并发控制假定不太可能（但不是不可能）在多个用户间发生资源冲突，允许不锁定不可能）在多个用户间发生资源冲突，允许不锁定任何资源而执行事务。只有试图更改数据时才检查任何资源而执行事务。只有试图更改数据时才检查资源以确定是否发生冲突。如果发生冲突，应用程资源以确定是否发生冲突。如果发生冲突，应用程序必须读取数据并再次尝试进行更改。序必须读取数据并再次尝试进行更改。悲观并发：悲观并发控制根据需要在事务的持续悲观并发：悲观并发控制根据需要在事务的持续时间内锁定资源。除非出现死锁，否则事务肯定会时间内锁定资源。除非出现死锁，否则事务肯定会成功完成。成功完成。7/12/202450华北科技学院 李文武6.4.2事务的隔离级别事务的隔离级别n当锁定用作并发控制机制时，它可以解决并发问题。当锁定用作并发控制机制时，它可以解决并发问题。这使所有事务得以在彼此完全隔离的环境中运行，但这使所有事务得以在彼此完全隔离的环境中运行，但是任何时候都可以有多个正在运行的事务。是任何时候都可以有多个正在运行的事务。n尽管可串行性对于事务确保数据库中的数据在所有时尽管可串行性对于事务确保数据库中的数据在所有时间内的正确性相当重要，然而许多事务并不总是要求间内的正确性相当重要，然而许多事务并不总是要求完全的隔离。完全的隔离。n事务准备接受不一致数据的级别称为隔离级别。隔离事务准备接受不一致数据的级别称为隔离级别。隔离级别是一个事务必须与其它事务进行隔离的程度。较级别是一个事务必须与其它事务进行隔离的程度。较低的隔离级别可以增加并发，但代价是降低数据的正低的隔离级别可以增加并发，但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。应用程序要求的隔离级但可能对并发产生负面影响。应用程序要求的隔离级别确定了别确定了SQLServer使用的锁定行为。使用的锁定行为。7/12/202451华北科技学院 李文武nSQLServer支持所有这些隔离级别：支持所有这些隔离级别：未提交读（事务隔离的最低级别，仅可保证不读取物理损未提交读（事务隔离的最低级别，仅可保证不读取物理损坏的数据）。坏的数据）。提交读（提交读（SQLServer默认级别）。不允许脏读。默认级别）。不允许脏读。可重复读。允许幻像读。可重复读。允许幻像读。可串行读（事务隔离的最高级别，事务之间完全隔离）。可串行读（事务隔离的最高级别，事务之间完全隔离）。n事务必须运行于可重复读或更高的隔离级别以防止丢事务必须运行于可重复读或更高的隔离级别以防止丢失更新。失更新。n当两个事务检索相同的行，然后基于原检索的值对行当两个事务检索相同的行，然后基于原检索的值对行进行更新时，会发生丢失更新。进行更新时，会发生丢失更新。n如果两个事务使用一个如果两个事务使用一个UPDATE语句更新行，并且语句更新行，并且不基于以前检索的值进行更新，则在默认的提交读隔不基于以前检索的值进行更新，则在默认的提交读隔离级别不会发生丢失更新。离级别不会发生丢失更新。7/12/202452华北科技学院 李文武6.4.3SQLServer中的锁定中的锁定nSQLServer可以锁定以下资源（按粒度增加的顺序可以锁定以下资源（按粒度增加的顺序列出）。列出）。资源资源描述描述RIDRID行标识符。用于单独锁定表中的一行。行标识符。用于单独锁定表中的一行。键键(KEY)(KEY)索引中的行锁。用于保护可串行事务中的键范围。索引中的行锁。用于保护可串行事务中的键范围。页页(PAG)(PAG)8 8 千字节千字节(KB)(KB)的数据页或索引页。的数据页或索引页。扩展盘区扩展盘区(EXT)(EXT)相邻的八个数据页或索引页构成的一组。相邻的八个数据页或索引页构成的一组。表表(TAB)(TAB)包括所有数据和索引在内的整个表。包括所有数据和索引在内的整个表。DBDB数据库。数据库。7/12/202453华北科技学院 李文武SQLServerSQLServer使用不同的锁模式锁定资源，这些锁模式确定使用不同的锁模式锁定资源，这些锁模式确定使用不同的锁模式锁定资源，这些锁模式确定使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。了并发事务访问资源的方式。了并发事务访问资源的方式。了并发事务访问资源的方式。锁模式锁模式描述描述共享共享(S)(S)用于不更改或不更新数据的操作（只读操作），如用于不更改或不更新数据的操作（只读操作），如 SELECT SELECT 语句。语句。更新更新(U)(U)用于可更新的资源中。防止当多个会话在读取、锁定以及随用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。后可能进行的资源更新时发生常见形式的死锁。排它排它(X)(X)用于数据修改操作，例如用于数据修改操作，例如 INSERT INSERT、UPDATE UPDATE 或或 DELETE DELETE。确。确保不会同时对同一资源进行多重更新。保不会同时对同一资源进行多重更新。意向意向用于建立锁的层次结构。意向锁的类型为：意向共享用于建立锁的层次结构。意向锁的类型为：意向共享(IS)(IS)、意向排它、意向排它(IX)(IX)以及与意向排它共享以及与意向排它共享(SIX)(SIX)。架构架构在执行依赖于表架构的操作时使用。架构锁的类型为：架构在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改修改(Sch-M)(Sch-M)和架构稳定性和架构稳定性(Sch-S)(Sch-S)。大容量更新大容量更新(BU)(BU)向表中大容量复制数据并指定了向表中大容量复制数据并指定了 TABLOCK TABLOCK 提示时使用。提示时使用。7/12/202454华北科技学院 李文武1.共享锁共享锁n共享共享(S)锁允许并发事务读取锁允许并发事务读取(SELECT)一个一个资源。资源上存在共享资源。资源上存在共享(S)锁时，任何其它事锁时，任何其它事务都不能修改数据。务都不能修改数据。n一旦已经读取数据，便立即释放资源上的共享一旦已经读取数据，便立即释放资源上的共享(S)锁，除非将事务隔离级别设置为可重复读锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提或更高级别，或者在事务生存周期内用锁定提示保留共享示保留共享(S)锁。锁。7/12/202455华北科技学院 李文武2.更新锁更新锁n更新更新(U)锁可以防止通常形式的死锁。一般更新模式由一个事务锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享组成，此事务读取记录，获取资源（页或行）的共享(S)锁，然锁，然后修改行，此操作要求锁转换为排它后修改行，此操作要求锁转换为排它(X)锁。锁。n如果两个事务获得了资源上的共享模式锁，然后试图同时更新数如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它据，则一个事务尝试将锁转换为排它(X)锁。锁。n共享模式到排它锁的转换必须等待一段时间，因为一个事务的排共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它试图获取排它(X)锁以进行更新。锁以进行更新。n由于两个事务都要转换为排它由于两个事务都要转换为排它(X)锁，并且每个事务都等待另一锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。个事务释放共享模式锁，因此发生死锁。n若要避免这种潜在的死锁问题，使用更新若要避免这种潜在的死锁问题，使用更新(U)锁。一次只有一个锁。一次只有一个事务可以获得资源的更新事务可以获得资源的更新(U)锁。如果事务修改资源，则更新锁。如果事务修改资源，则更新(U)锁转换为排它锁转换为排它(X)锁。否则，锁转换为共享锁。锁。否则，锁转换为共享锁。7/12/202456华北科技学院 李文武3.排它锁排它锁n排它排它(X)锁可以防止并发事务对资源进行访问。锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它其它事务不能读取或修改排它(X)锁锁定的数锁锁定的数据。据。7/12/202457华北科技学院 李文武4.意向锁意向锁n意向锁表示意向锁表示SQLServer需要在层次结构中的需要在层次结构中的某些底层资源上获取共享某些底层资源上获取共享(S)锁或排它锁或排它(X)锁。锁。例如，放置在表级的共享意向锁表示事务打算例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享在表中的页或行上放置共享(S)锁。在表级设锁。在表级设置意向锁可防止另一个事务随后在包含那一页置意向锁可防止另一个事务随后在包含那一页的表上获取排它的表上获取排它(X)锁。锁。n意向锁可以提高性能，因为意向锁可以提高性能，因为SQLServer仅在仅在表级检查意向锁来确定事务是否可以安全地获表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。上的锁以确定事务是否可以锁定整个表。7/12/202458华北科技学院 李文武意向锁包括意向共享意向锁包括意向共享意向锁包括意向共享意向锁包括意向共享(IS)(IS)(IS)(IS)、意向排它、意向排它、意向排它、意向排它(IX)(IX)(IX)(IX)以及与以及与以及与以及与意向排它共享意向排它共享意向排它共享意向排它共享(SIX)(SIX)(SIX)(SIX)。锁模式锁模式描述描述意向共享意向共享 (IS)(IS)通过在各资源上放置通过在各资源上放置 S S 锁，表明事务的意向是读取层次结构中锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。的部分（而不是全部）底层资源。意向排它意向排它 (IX)(IX)通过在各资源上放置通过在各资源上放置 X X 锁，表明事务的意向是修改层次结构中锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。的部分（而不是全部）底层资源。IX IX 是是 IS IS 的超集。的超集。与意向排它共与意向排它共享享(SIX)(SIX)通过在各资源上放置通过在各资源上放置 IX IX 锁，表明事务的意向是读取层次结构中锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。允许顶层的全部底层资源并修改部分（而不是全部）底层资源。允许顶层资源上的并发资源上的并发 IS IS 锁。例如，表的锁。例如，表的 SIX SIX 锁在表上放置一个锁在表上放置一个 SIX SIX 锁（允许并发锁（允许并发 IS IS 锁），在当前所修改页上放置锁），在当前所修改页上放置 IX IX 锁（在已修锁（在已修改行上放置改行上放置 X X 锁）。虽然每个资源在一段时间内只能有一个锁）。虽然每个资源在一段时间内只能有一个 SIX SIX 锁，以防止其它事务对资源进行更新，但是其它事务可以通锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的过获取表级的 IS IS 锁来读取层次结构中的底层资源。锁来读取层次结构中的底层资源。7/12/202459华北科技学院 李文武5.架构锁架构锁n执行表的数据定义语言执行表的数据定义语言(DDL)操作（例如添加操作（例如添加列或除去表）时使用架构修改列或除去表）时使用架构修改(Sch-M)锁。锁。n当编译查询时，使用架构稳定性当编译查询时，使用架构稳定性(Sch-S)锁。架锁。架构稳定性构稳定性(Sch-S)锁不阻塞任何事务锁，包括排锁不阻塞任何事务锁，包括排它它(X)锁。因此在编译查询时，其它事务（包括锁。因此在编译查询时，其它事务（包括在表上有排它在表上有排它(X)锁的事务）都能继续运行。但锁的事务）都能继续运行。但不能在表上执行不能在表上执行DDL操作。操作。7/12/202460华北科技学院 李文武6.大容量更新锁大容量更新锁n当将数据大容量复制到表，且指定了当将数据大容量复制到表，且指定了TABLOCK提示或使用提示或使用sp_tableoption设置了设置了tablelockonbulk