《第6章 SQL高级应用.ppt》由会员分享，可在线阅读，更多相关《第6章 SQL高级应用.ppt（86页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第第6章章SQL高级应用高级应用6.1SELECT高级查询高级查询6.1.1数据汇总数据汇总1.聚合函数：对一组值操作，返回单一的汇总值。聚合函数：对一组值操作，返回单一的汇总值。聚合函数在如下情况下，允许作为表达式使用：聚合函数在如下情况下，允许作为表达式使用：(1)SELECT语句的选择列表语句的选择列表(子查询或外部查询子查询或外部查询)。(2)COMPUTE或或COMPUTEBY子句。子句。(3)HAVING子句。子句。11/2/20222华北科技学院 李文武COMPUTE和和COMPUTEBYnCOMPUTE子句用于分类汇总，格式为：子句用于分类汇总，格式为：COMPUTE聚合函数名
2、聚合函数名(expression),.nBYexpression,.nn其中其中expression是列名；是列名；COMPUTE将产生额将产生额外的汇总行。外的汇总行。nCOMPUTEBY子句可以用同一子句可以用同一SELECT语句语句既查看明细行，又查看汇总行。可计算分组的既查看明细行，又查看汇总行。可计算分组的汇总值，也可计算整个结果集的汇总值。汇总值，也可计算整个结果集的汇总值。11/2/20223华北科技学院 李文武COMPUTE生成的结果集生成的结果集n当当COMPUTE带有可选的带有可选的BY子句时，符合子句时，符合SELECT条件的每个组都有两个结果集：条件的每个组都有两个结果
3、集：每个组的第一个结果集是明细行集，其中包含该组的选择列每个组的第一个结果集是明细行集，其中包含该组的选择列表信息。表信息。每个组的第二个结果集有一行，其中包含该组的每个组的第二个结果集有一行，其中包含该组的COMPUTE子句中所指定的聚合函数的小计。子句中所指定的聚合函数的小计。n当当COMPUTE不带可选的不带可选的BY子句时，子句时，SELECT语句语句有两个结果集：有两个结果集：每个组的第一个结果集是包含选择列表信息的所有明细行。每个组的第一个结果集是包含选择列表信息的所有明细行。第二个结果集有一行，其中包含第二个结果集有一行，其中包含COMPUTE子句中所指定的子句中所指定的聚合函数
4、的合计。聚合函数的合计。11/2/20224华北科技学院 李文武比较比较COMPUTE和和GROUPBYnGROUPBY生成单个结果集。每个组都有一个只包含生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数。表只能包含分组依据列和聚合函数。nCOMPUTE生成多个结果集。生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表一类结果集包含每个组的明细行，其中包含选择列表中的表达式。达式。另一类结果集包含组的子聚合，或另一类结果集包含组的子聚合，或SELECT语句的总聚合
5、。语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其它表达式。选择列表可包含除分组依据列或聚合函数之外的其它表达式。聚合函数在聚合函数在COMPUTE子句中指定，而不是在选择列表中。子句中指定，而不是在选择列表中。11/2/20225华北科技学院 李文武2.GROUPBY子句子句n指定用来放置输出行的组，并且如果指定用来放置输出行的组，并且如果SELECT子句子句中包含聚合函数，则计算每中包含聚合函数，则计算每组的汇总值。指定组的汇总值。指定GROUPBY时，选择列表中时，选择列表中任一非聚合表达式内的所有列都应包含在任一非聚合表达式内的所有列都应包含在GROUPBY列表中，或者列表中
6、，或者GROUPBY表达式表达式必须与选择列表表达式完全匹配。必须与选择列表表达式完全匹配。n语法语法GROUPBYALLgroup_by_expression,.nWITHCUBE|ROLLUP11/2/20226华北科技学院 李文武nALL：包含所有组和结果集，甚至包含那些任何行都不：包含所有组和结果集，甚至包含那些任何行都不满足满足WHERE子句指定的搜索条件的组和结果集。如子句指定的搜索条件的组和结果集。如果指定了果指定了ALL，将对组中不满足搜索条件的汇总列返回，将对组中不满足搜索条件的汇总列返回空值。不能用空值。不能用CUBE或或ROLLUP运算符指定运算符指定ALL。如果访问远程
7、表的查询中有如果访问远程表的查询中有WHERE子句，则不支持子句，则不支持GROUPBYALL操作。操作。ngroup_by_expression：是对其执行分组的表达式。是对其执行分组的表达式。group_by_expression 也称为分组列。也称为分组列。group_by expression 可以是列或引用列的非聚合表达式。在选可以是列或引用列的非聚合表达式。在选择列表内定义的列的别名不能用于指定分组列。择列表内定义的列的别名不能用于指定分组列。11/2/20227华北科技学院 李文武nCUBE：指定在结果集内不仅包含由：指定在结果集内不仅包含由GROUPBY提供提供的正常行，还包含
8、汇总行。在结果集内返回每个可能的的正常行，还包含汇总行。在结果集内返回每个可能的组和子组组合的组和子组组合的GROUPBY汇总行。汇总行。GROUPBY汇汇总行在结果中显示为总行在结果中显示为NULL，但可用来表示所有值。使，但可用来表示所有值。使用用GROUPING函数确定结果集内的空值是否是函数确定结果集内的空值是否是GROUPBY汇总值。汇总值。nROLLUP：指定在结果集内不仅包含由：指定在结果集内不仅包含由GROUPBY提提供的正常行，还包含汇总行。按层次结构顺序，从组内供的正常行，还包含汇总行。按层次结构顺序，从组内的最低级别到最高级别汇总组。组的层次结构取决于指的最低级别到最高级
9、别汇总组。组的层次结构取决于指定分组列时所使用的顺序。更改分组列的顺序会影响在定分组列时所使用的顺序。更改分组列的顺序会影响在结果集内生成的行数。结果集内生成的行数。11/2/20228华北科技学院 李文武6.1.2联接查询联接查询n通过联接根据各表间的逻辑关系从两个或多个通过联接根据各表间的逻辑关系从两个或多个表中检索数据。表中检索数据。n定义方法：定义方法：可以在可以在FROM或或WHERE子句中使用比较运算符给子句中使用比较运算符给出联接条件对表进行联接。出联接条件对表进行联接。指定各表中用于联接的列。指定各表中用于联接的列。比较符可以是比较符可以是、=、!=、!联接条件与联接条件与WH
10、ERE和和HAVING子句的搜索条件配子句的搜索条件配合完成查询。查询所选的行首先通过合完成查询。查询所选的行首先通过FROM子句子句联接条件进行筛选，其次由联接条件进行筛选，其次由WHERE子句搜索条子句搜索条件筛选，然后由件筛选，然后由HAVING子句搜索条件筛选子句搜索条件筛选。11/2/20229华北科技学院 李文武1.内联接内联接n仅显示两个联接表中的匹配行的联接。是查询仅显示两个联接表中的匹配行的联接。是查询分析器中的默认联接类型。分析器中的默认联接类型。n当创建内联接时，包含当创建内联接时，包含NULL的列不与任何值的列不与任何值匹配，因此不包括在结果集内。空值不与其它匹配，因此
11、不包括在结果集内。空值不与其它的空值匹配。的空值匹配。n关键词为：关键词为：INNERJOIN11/2/202210华北科技学院 李文武2.外联接外联接n包括在联接表中没有相关行的行的联接。包括在联接表中没有相关行的行的联接。n左向外联接：包括第一个命名表（左向外联接：包括第一个命名表（“左左”表，出表，出现在现在JOIN子句的最左边）中的所有行。不包子句的最左边）中的所有行。不包括右表中的不匹配行。括右表中的不匹配行。LEFTJOINn右向外联接：包括第二个命名表（右向外联接：包括第二个命名表（“右右”表，出表，出现在现在JOIN子句的最右边）中的所有行。不包子句的最右边）中的所有行。不包括
12、左表中的不匹配行。括左表中的不匹配行。RIGHTJOINn完整外部联接：包括所有联接表中的所有行，完整外部联接：包括所有联接表中的所有行，不论它们是否匹配。不论它们是否匹配。FULLJOIN11/2/202211华北科技学院 李文武3.交叉联接交叉联接n在这类联接的结果集内，两个表中每两个可能在这类联接的结果集内，两个表中每两个可能成对的行占一行。成对的行占一行。n交叉联接不使用交叉联接不使用WHERE子句。子句。11/2/202212华北科技学院 李文武6.1.3子查询子查询n子查询是一个子查询是一个SELECT查询，它返回单个值查询，它返回单个值且嵌套在且嵌套在SELECT、INSERT、
13、UPDATE、DELETE语句或其它子查询中。任何允许使用语句或其它子查询中。任何允许使用表达式的地方都可以使用子查询。表达式的地方都可以使用子查询。n子查询也称为内部查询或内部选择，而包含子子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。查询的语句也称为外部查询或外部选择。11/2/202213华北科技学院 李文武n嵌套在外部嵌套在外部SELECT语句中的子查询包括以下语句中的子查询包括以下组件：组件：包含标准选择列表组件的标准包含标准选择列表组件的标准SELECT查询。查询。包含一个或多个表或者视图名的标准包含一个或多个表或者视图名的标准FROM子句。子句。可选
14、的可选的WHERE子句。子句。可选的可选的GROUPBY子句。子句。可选的可选的HAVING子句。子句。n子查询的子查询的SELECT查询总是使用圆括号括起来。查询总是使用圆括号括起来。且不能包括且不能包括COMPUTE或或FORBROWSE子子句，如果同时指定句，如果同时指定TOP子句，则可能只包括子句，则可能只包括ORDERBY子句。子句。11/2/202214华北科技学院 李文武n子查询可以嵌套在外部子查询可以嵌套在外部SELECT、INSERT、UPDATE或或DELETE语句的语句的WHERE或或HAVING子句内，或者其它子查询中。子句内，或者其它子查询中。n尽管根据可用内存和查询
15、中其它表达式的复杂尽管根据可用内存和查询中其它表达式的复杂程度不同，嵌套限制也有所不同，但嵌套到程度不同，嵌套限制也有所不同，但嵌套到32层是可能的。层是可能的。n如果某个表只出现在子查询中而不出现在外部如果某个表只出现在子查询中而不出现在外部查询中，那么该表中的列就无法包含在输出中查询中，那么该表中的列就无法包含在输出中（外部查询的选择列表）。（外部查询的选择列表）。11/2/202215华北科技学院 李文武n有三种基本的子查询。它们是：有三种基本的子查询。它们是：在通过在通过IN引入的列表或者由引入的列表或者由ANY或或ALL修改的比修改的比较运算符的列表上进行操作。较运算符的列表上进行操
16、作。通过无修改的比较运算符引入，且必须返回单个值。通过无修改的比较运算符引入，且必须返回单个值。通过通过EXISTS引入的存在测试。引入的存在测试。n通常采用以下格式中的一种：通常采用以下格式中的一种：WHERE表达式表达式NOTIN(子查询子查询)WHERE表达式表达式 比较运算符比较运算符ANY|ALL(子查询子查询)WHERENOTEXISTS(子查询子查询)11/2/202216华北科技学院 李文武1.子查询规则子查询规则n子查询受以下条件的限制：子查询受以下条件的限制：通过比较运算符引入的子查询的选择列表只能包括一个表达式或列名称通过比较运算符引入的子查询的选择列表只能包括一个表达式
17、或列名称（分别对（分别对SELECT*或列表进行或列表进行EXISTS和和IN操作除外）。操作除外）。如果外部查询的如果外部查询的WHERE子句包括某个列名，则该子句必须与子查询选择子句包括某个列名，则该子句必须与子查询选择列表中的该列在联接上兼容。列表中的该列在联接上兼容。子查询的选择列表中不允许出现子查询的选择列表中不允许出现ntext、text和和image数据类型。数据类型。由于必须返回单个值，由无修改的比较运算符（指其后未接关键字由于必须返回单个值，由无修改的比较运算符（指其后未接关键字ANY或或ALL）引入的子查询不能包括）引入的子查询不能包括GROUPBY和和HAVING子句。子
18、句。包括包括GROUPBY的子查询不能使用的子查询不能使用DISTINCT关键字。关键字。不能指定不能指定COMPUTE和和INTO子句。子句。只有同时指定了只有同时指定了TOP，才可以指定，才可以指定ORDERBY。由子查询创建的视图不能更新。由子查询创建的视图不能更新。通过通过EXISTS引入的子查询的选择列表由星号引入的子查询的选择列表由星号(*)组成，而不使用单个列组成，而不使用单个列名。通过名。通过EXISTS引入的子查询进行了存在测试，返回引入的子查询进行了存在测试，返回TRUE或或FALSE而非数据，所以这些子查询的规则与标准选择列表的规则完全相同。而非数据，所以这些子查询的规则
19、与标准选择列表的规则完全相同。11/2/202217华北科技学院 李文武2.子查询类型子查询类型n可以在许多地方指定子查询：可以在许多地方指定子查询：使用别名时使用别名时使用使用IN或或NOTIN时时在在UPDATE、DELETE和和INSERT语句中语句中使用比较运算符时使用比较运算符时使用使用ANY、SOME或或ALL时时使用使用EXISTS或或NOTEXISTS时时在有表达式的地方在有表达式的地方11/2/202218华北科技学院 李文武使用使用IN或或NOTIN的子查询的子查询n通过通过IN（或（或NOTIN）引入的子查询结果是一列零值）引入的子查询结果是一列零值或更多值。子查询返回结
20、果之后，外部查询将利用这或更多值。子查询返回结果之后，外部查询将利用这些结果。些结果。n使用联接而不使用子查询处理该问题及类似问题的一使用联接而不使用子查询处理该问题及类似问题的一个不同之处在于，联接使您可以在结果中显示多个表个不同之处在于，联接使您可以在结果中显示多个表中的列。中的列。n联接总是可以表示为子查询。子查询经常（但不总是）联接总是可以表示为子查询。子查询经常（但不总是）可以表示为联接。这是因为联接是对称的：可以以任可以表示为联接。这是因为联接是对称的：可以以任意顺序将表意顺序将表A联接到表联接到表B，而且会得到相同的答案。，而且会得到相同的答案。而对子查询来说，情况则并非如此。而
21、对子查询来说，情况则并非如此。11/2/202219华北科技学院 李文武UPDATE、DELETE和和INSERT语句中的子查询语句中的子查询n子查询可以嵌套在子查询可以嵌套在UPDATE、DELETE和和INSERT语句以及语句以及SELECT语句中。语句中。11/2/202220华北科技学院 李文武使用比较运算符的子查询使用比较运算符的子查询n子查询可由一个比较运算符（子查询可由一个比较运算符（=、=、,!或或=）引入。）引入。n与使用与使用IN引入的子查询一样，由未修改的比引入的子查询一样，由未修改的比较运算符（后面不跟较运算符（后面不跟ANY或或ALL的比较运算的比较运算符）引入的子查
22、询必须返回单个值而不是值列符）引入的子查询必须返回单个值而不是值列表。如果这样的子查询返回多个值，将显示错表。如果这样的子查询返回多个值，将显示错误信息。误信息。n要使用由无修改的比较运算符引入的子查询，要使用由无修改的比较运算符引入的子查询，必须对数据和问题的本质非常熟悉，以了解该必须对数据和问题的本质非常熟悉，以了解该子查询实际是否只返回一个值。子查询实际是否只返回一个值。11/2/202221华北科技学院 李文武使用使用EXISTS的子查询的子查询n使用使用EXISTS关键字引入一个子查询时，就相关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的当于进行一次存在测试。外部查询的
23、WHERE子句测试子查询返回的行是否存在。子查询实子句测试子查询返回的行是否存在。子查询实际上不产生任何数据；它只返回际上不产生任何数据；它只返回TRUE或或FALSE值。值。n使用使用EXISTS引入的子查询语法如下：引入的子查询语法如下：WHERENOTEXISTS(子查询子查询)11/2/202222华北科技学院 李文武3.多层嵌套多层嵌套n子查询自身可以包括一个或多个子查询。一个子查询自身可以包括一个或多个子查询。一个语句中可以嵌套任意数量的子查询。语句中可以嵌套任意数量的子查询。11/2/202223华北科技学院 李文武6.1.4在查询基础上创建新表在查询基础上创建新表n创建新表并将
24、结果行从查询插入新表中。创建新表并将结果行从查询插入新表中。n用户若要执行带用户若要执行带INTO子句的子句的SELECT语句，语句，必须在目的数据库内具有必须在目的数据库内具有CREATETABLE权权限。限。SELECT.INTO不能与不能与COMPUTE子句子句一起使用。一起使用。11/2/202224华北科技学院 李文武6.2管理管理ntext、text、image数据数据nntext、text和和image数据类型在单个值中可以包含数据类型在单个值中可以包含非常大的数据量（最大可达非常大的数据量（最大可达2GB）。单个数据值通）。单个数据值通常比应用程序在一个步骤中能够检索的大；某些
25、值可常比应用程序在一个步骤中能够检索的大；某些值可能还会大于客户端的可用虚拟内存。因此，在检索这能还会大于客户端的可用虚拟内存。因此，在检索这些值时，通常需要一些特殊的步骤。些值时，通常需要一些特殊的步骤。n如果如果ntext、text和和image数据值不超过数据值不超过Unicode串、字符串或二进制串的长度（分别为串、字符串或二进制串的长度（分别为4,000个字符、个字符、8,000个字符和个字符和8,000个字节），就可以在个字节），就可以在SELECT、UPDATE和和INSERT语句中引用它们，其引用方式语句中引用它们，其引用方式与较小的数据类型相同。与较小的数据类型相同。11/2
26、/202225华北科技学院 李文武n包含短值的包含短值的ntext列可在列可在SELECT语句的选择语句的选择列表中引用，与列表中引用，与nvarchar列的引用方式相同。列的引用方式相同。n引用时必须遵守一些限制，例如不能在引用时必须遵守一些限制，例如不能在WHERE子句中直接引用子句中直接引用ntext、text或或image列。列。n这些列可以作为返回其它数据类型（例如这些列可以作为返回其它数据类型（例如ISNULL、SUBSTRING或或PATINDEX）的某个）的某个函数的参数包含在函数的参数包含在WHERE子句中，也可以包子句中，也可以包含在含在ISNULL、ISNOTNULL或或
27、LIKE表达式表达式中。中。11/2/202226华北科技学院 李文武6.3事务处理事务处理n事务是作为单个逻辑工作单元执行的一系列操作。事务是作为单个逻辑工作单元执行的一系列操作。n一个逻辑工作单元必须有四个属性，称为一个逻辑工作单元必须有四个属性，称为ACID（原子（原子性、一致性、隔离性和持久性）属性，只有这样才能成性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务：为一个事务：原子性：事务必须是原子工作单元；对于其数据修改，要么原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。全都执行，要么全都不执行。一致性：事务在完成时，必须使所有的数据都保持一致
28、状态。一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（持所有数据的完整性。事务结束时，所有的内部数据结构（如如B树索引或双向链表）都必须是正确的。树索引或双向链表）都必须是正确的。11/2/202227华北科技学院 李文武隔离性：由并发事务所作的修改必须与任何其它隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态
29、，处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。结束时的状态与原始事务执行的状态相同。持久性：事务完成之后，它对于系统的影响是永持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。久性的。该修改即使出现系统故障也将一直保持。11/2/202228华北科技学院 李文
30、武6.3.1事务分类事务分类nSQLServer以三种事务模式运行：以三种事务模式运行：自动提交事务：每条单独的语句都是一个事务。自动提交事务：每条单独的语句都是一个事务。是是SQLServer的默认事务管理模式。的默认事务管理模式。n每个每个T-SQL语句在完成时，都被提交或回滚。语句在完成时，都被提交或回滚。n如果一个语句成功地完成，则提交该语句；如果遇到错如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。误，则回滚该语句。n自动提交模式也是自动提交模式也是ADO、OLEDB、ODBC和和DB-Library的默认模式。的默认模式。11/2/202229华北科技学院 李文武
31、显式事务：也称为用户定义或用户指定的事务。显式事务：也称为用户定义或用户指定的事务。每个事务均以每个事务均以BEGINTRANSACTION语句显式语句显式开始，以开始，以COMMIT或或ROLLBACK语句显式结束。语句显式结束。隐性事务：在前一个事务完成时新事务隐式启动，隐性事务：在前一个事务完成时新事务隐式启动，但每个事务仍以但每个事务仍以COMMIT或或ROLLBACK语句显语句显式完成。式完成。11/2/202230华北科技学院 李文武6.3.2显式事务显式事务n1.启动事务启动事务语法语法nBEGINTRANSACTIONtransaction_name|tran_name_var
32、iableWITHMARKdescription参数参数ntransaction_name：是给事务分配的名称。必须遵循标识符规则，但是给事务分配的名称。必须遵循标识符规则，但是不允许标识符多于是不允许标识符多于32个字符。仅在嵌套的个字符。仅在嵌套的BEGIN.COMMIT或或BEGIN.ROLLBACK语句的最外语句对上使用事务名。语句的最外语句对上使用事务名。ntran_name_variable：是用户定义的、含有有效事务名称的变量的是用户定义的、含有有效事务名称的变量的名称。必须用名称。必须用char、varchar、nchar或或nvarchar数据类型声明。数据类型声明。nWIT
33、HMARKdescription：指定在日志中标记事务。：指定在日志中标记事务。Description 是描述该标记的字符串。如果使用了是描述该标记的字符串。如果使用了WITHMARK，则必须指定事务，则必须指定事务名。名。WITHMARK允许将事务日志还原到命名标记。允许将事务日志还原到命名标记。11/2/202231华北科技学院 李文武2.结束事务结束事务n语法语法COMMITTRANSACTIONtransaction_name|tran_name_variable n参数参数transaction_name：SQLServer忽略该参数。忽略该参数。transaction_name指定
34、由前面的指定由前面的BEGINTRANSACTION指派的事务名称。通过向程序员指明指派的事务名称。通过向程序员指明COMMITTRANSACTION与哪些嵌套的与哪些嵌套的BEGINTRANSACTION相关相关联，联，transaction_name可作为帮助阅读的一种方法。可作为帮助阅读的一种方法。tran_name_variable：是用户定义的、含有有效事务名称是用户定义的、含有有效事务名称的变量的名称。必须用的变量的名称。必须用char、varchar、nchar或或nvarchar数据类型声明该变量。数据类型声明该变量。11/2/202232华北科技学院 李文武n结束一个成功的隐
35、性事务或用户定义事务。结束一个成功的隐性事务或用户定义事务。nCOMMITWORK标志事务的结束。标志事务的结束。语法：语法：COMMITWORK与与COMMITTRANSACTION相同，但相同，但COMMITTRANSACTION接受用户定义的事务名称。接受用户定义的事务名称。11/2/202233华北科技学院 李文武3.回滚事务回滚事务n语法语法ROLLBACKTRANSACTIONtransaction_name|tran_name_variable|savepoint_name|savepoint_variable n参数参数transaction_name：是给是给BEGINTRA
36、NSACTION上的事务上的事务指派的名称。嵌套事务时，指派的名称。嵌套事务时，transaction_name必须是来自最必须是来自最远的远的BEGINTRANSACTION语句的名称。语句的名称。tran_name_variable：是用户定义的、含有有效事务名称是用户定义的、含有有效事务名称的变量的名称。的变量的名称。savepoint_name：是来自是来自SAVETRANSACTION语句的语句的savepoint_name。savepoint_name 必须符合标识符规则。必须符合标识符规则。当条件回滚只影响事务的一部分时使用当条件回滚只影响事务的一部分时使用savepoint_n
37、ame。savepoint_variable：是用户定义的、含有有效保存点名称是用户定义的、含有有效保存点名称的变量的名称。必须用的变量的名称。必须用char、varchar、nchar或或nvarchar数据类型声明该变量。数据类型声明该变量。11/2/202234华北科技学院 李文武n将显式事务或隐性事务回滚到事务的起点或事将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。务内的某个保存点。nROLLBACKTRANSACTION清除自事务的清除自事务的起点或到某个保存点所做的所有数据修改。起点或到某个保存点所做的所有数据修改。ROLLBACK还释放由事务控制的资源。还释放由事务控
38、制的资源。nROLLBACKWORK将用户定义的事务回滚到事务的起点。将用户定义的事务回滚到事务的起点。语法：语法：ROLLBACKWORK11/2/202235华北科技学院 李文武4.在事务中设置保存点在事务中设置保存点n语法语法SAVETRANSACTIONsavepoint_name|savepoint_variable n参数参数savepoint_name：是指派给保存点的名称。保存点是指派给保存点的名称。保存点名称必须符合标识符规则，但只使用前名称必须符合标识符规则，但只使用前32个字符。个字符。savepoint_variable：是用户定义的、含有有效保是用户定义的、含有有效保
39、存点名称的变量的名称。必须用存点名称的变量的名称。必须用char、varchar、nchar或或nvarchar数据类型声明该变量。数据类型声明该变量。11/2/202236华北科技学院 李文武n用户可以在事务内设置保存点或标记。保存点用户可以在事务内设置保存点或标记。保存点定义的是：如果有条件地取消事务的一部分，定义的是：如果有条件地取消事务的一部分，事务可以返回的位置。事务可以返回的位置。11/2/202237华北科技学院 李文武5.标记事务标记事务nWITHMARKdescriptionn指定在日志中标记事务。将数据库还原到早期指定在日志中标记事务。将数据库还原到早期状态时，可使用标记事
40、务替代日期和时间。状态时，可使用标记事务替代日期和时间。Description 是描述该标记的字符串。是描述该标记的字符串。n如果使用了如果使用了WITHMARK，则必须指定事务名。，则必须指定事务名。WITHMARK允许将事务日志还原到命名标记。允许将事务日志还原到命名标记。n只有当数据库由标记事务更新时，才在事务日只有当数据库由标记事务更新时，才在事务日志中放置标记。不修改数据的事务不被标记。志中放置标记。不修改数据的事务不被标记。11/2/202238华北科技学院 李文武6.不能用于事务的操作不能用于事务的操作n一些不能撤销的操作，其对数据库的操作是不一些不能撤销的操作，其对数据库的操作
41、是不能恢复的。如创建、修改、删除数据库等。能恢复的。如创建、修改、删除数据库等。nP16811/2/202239华北科技学院 李文武6.3.3自动提交事务自动提交事务n编译错误将阻止编译错误将阻止SQLServer建立执行计划，建立执行计划，这样批处理中的任何语句都不会执行。这样批处理中的任何语句都不会执行。n运行错误时，该语句之前的结果被保留下来。运行错误时，该语句之前的结果被保留下来。nSQLServer使用延迟的名称解析，其中对象使用延迟的名称解析，其中对象名直到执行时才被解析。名直到执行时才被解析。所以错误的对象名不所以错误的对象名不会在编译时指出。会在编译时指出。11/2/20224
42、0华北科技学院 李文武6.3.4隐式事务隐式事务n在为连接将隐性事务模式设置为打开之后，当在为连接将隐性事务模式设置为打开之后，当SQLServer首次执行下列任何语句时，都会自动启动一首次执行下列任何语句时，都会自动启动一个事务：个事务：n在在COMMIT或或ROLLBACK语句之前，该事务将一语句之前，该事务将一直保持有效。在第一个事务被提交或回滚之后，下次直保持有效。在第一个事务被提交或回滚之后，下次当连接执行这些语句中的任何语句时，当连接执行这些语句中的任何语句时，SQLServer都将自动启动一个新事务。都将自动启动一个新事务。SQLServer将不断地生将不断地生成一个隐性事务链，
43、直到隐性事务模式关闭为止。成一个隐性事务链，直到隐性事务模式关闭为止。ALTERTABLEINSERTCREATEOPENDELETEREVOKEDROPSELECTFETCHTRUNCATETABLEGRANTUPDATE11/2/202241华北科技学院 李文武nSETIMPLICIT_TRANSACTIONSON语句启语句启动隐性事务模式。动隐性事务模式。nSETIMPLICIT_TRANSACTIONSOFF语句语句关闭隐性事务模式。关闭隐性事务模式。n使用使用COMMITTRANSACTION、COMMITWORK、ROLLBACKTRANSACTION或或ROLLBACKWORK语
44、句结束每个事务。语句结束每个事务。11/2/202242华北科技学院 李文武n如果连接已经在打开的事务中，则上述语句不启动新如果连接已经在打开的事务中，则上述语句不启动新事务。事务。n对于因为该设置为对于因为该设置为ON而自动打开的事务，用户必须而自动打开的事务，用户必须在该事务结束时将其显式提交或回滚。否则当用户断在该事务结束时将其显式提交或回滚。否则当用户断开连接时，事务及其所包含的所有数据更改将回滚。开连接时，事务及其所包含的所有数据更改将回滚。在事务提交后，执行上述任一语句即可启动新事务。在事务提交后，执行上述任一语句即可启动新事务。n隐性事务模式将保持有效，直到连接执行隐性事务模式将
45、保持有效，直到连接执行SETIMPLICIT_TRANSACTIONSOFF语句使连接返回语句使连接返回到自动提交模式。在自动提交模式下，如果各个语句到自动提交模式。在自动提交模式下，如果各个语句成功完成则提交。成功完成则提交。11/2/202243华北科技学院 李文武6.4数据的锁定数据的锁定n通过锁定确保事务完整性和数据库的一致性。通过锁定确保事务完整性和数据库的一致性。n如：如：读取正由其他用户更改的数据读取正由其他用户更改的数据多个用户同时更改同一数据多个用户同时更改同一数据11/2/202244华北科技学院 李文武6.4.1并发问题并发问题n如果没有锁定且多个用户同时访问一个数据库，
46、如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会则当他们的事务同时使用相同的数据时可能会发生问题。并发问题包括：发生问题。并发问题包括：丢失或覆盖更新。丢失或覆盖更新。未确认的相关性（未确认的相关性（脏读脏读）。）。不一致的分析（不一致的分析（非重复读非重复读）。）。幻像读。幻像读。11/2/202245华北科技学院 李文武1.丢失更新丢失更新n当两个或多个事务选择同一行，然后基于最初当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。每个事务都不知道其它事务的
47、存在。n最后的更新将重写由其它事务所做的更新，这最后的更新将重写由其它事务所做的更新，这将导致数据丢失。将导致数据丢失。11/2/202246华北科技学院 李文武2.未确认的相关性（脏读）未确认的相关性（脏读）n当第二个事务选择其它事务正在更新的行时，当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。会发生未确认的相关性问题。n第二个事务正在读取的数据还没有确认并且可第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。能由更新此行的事务所更改。11/2/202247华北科技学院 李文武3.不一致的分析（非重复读）不一致的分析（非重复读）n当第二个事务多次访问同一行
48、而且每次读取不当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。同的数据时，会发生不一致的分析问题。n不一致的分析与未确认的相关性类似，因为其不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数它事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。取的数据是由已进行了更改的事务提交的。n而且，不一致的分析涉及多次（两次或更多）而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；读取同一行，而且每次信息都由其它
49、事务更改；因而该行被非重复读取。因而该行被非重复读取。11/2/202248华北科技学院 李文武4.幻像读幻像读n当对某行执行插入或删除操作，而该行属于某当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读个事务正在读取的行的范围时，会发生幻像读问题。问题。n事务第一次读的行范围显示出其中一行已不复事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其存在于第二次读或后续读中，因为该行已被其它事务删除。它事务删除。n同样，由于其它事务的插入操作，事务的第二同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。次或后
50、续读显示有一行已不存在于原始读中。11/2/202249华北科技学院 李文武乐观和悲观并发乐观和悲观并发nSQLServer2000提供了乐观并发控制和悲观提供了乐观并发控制和悲观并发控制。乐观并发控制使用游标。并发控制。乐观并发控制使用游标。SQLServer默认使用悲观并发控制。默认使用悲观并发控制。乐观并发：乐观并发控制假定不太可能（但不是乐观并发：乐观并发控制假定不太可能（但不是不可能）在多个用户间发生资源冲突，允许不锁定不可能）在多个用户间发生资源冲突，允许不锁定任何资源而执行事务。只有试图更改数据时才检查任何资源而执行事务。只有试图更改数据时才检查资源以确定是否发生冲突。如果发生冲