數(shù)據(jù)庫(kù)原理與應(yīng)用(10)第10章 SQL高級(jí)應(yīng)用

1、第第10章章SQL高級(jí)應(yīng)用高級(jí)應(yīng)用10.1 SELECT高級(jí)查詢高級(jí)查詢10.1.1 數(shù)據(jù)匯總數(shù)據(jù)匯總 為決策支持系統(tǒng)生成聚合事務(wù)的匯總報(bào)表是一項(xiàng)復(fù)雜并為決策支持系統(tǒng)生成聚合事務(wù)的匯總報(bào)表是一項(xiàng)復(fù)雜并且相當(dāng)消耗資源的工作。且相當(dāng)消耗資源的工作。SQL Server 2005提供兩個(gè)靈活且強(qiáng)提供兩個(gè)靈活且強(qiáng)大的工具，即大的工具，即SQL Server 2005分析服務(wù)和報(bào)表服務(wù)。分析服務(wù)和報(bào)表服務(wù)。但是對(duì)于生成簡(jiǎn)單匯總報(bào)表的應(yīng)用程序，可使用以下運(yùn)但是對(duì)于生成簡(jiǎn)單匯總報(bào)表的應(yīng)用程序，可使用以下運(yùn)算符：算符：l CUBE或或ROLLUP運(yùn)算符。均為運(yùn)算符。均為GROUP BY子句子句的一部分。的一部

2、分。l COMPUTE或或COMPUTE BY運(yùn)算符。均與運(yùn)算符。均與GROUP BY子句相關(guān)聯(lián)。子句相關(guān)聯(lián)。1聚合函數(shù)聚合函數(shù)聚合函數(shù)用來(lái)完成一定的統(tǒng)計(jì)功能，對(duì)一組值執(zhí)行計(jì)算并聚合函數(shù)用來(lái)完成一定的統(tǒng)計(jì)功能，對(duì)一組值執(zhí)行計(jì)算并返回單一的值。除返回單一的值。除COUNT函數(shù)之外，聚合函數(shù)忽略空值。函數(shù)之外，聚合函數(shù)忽略空值。聚合函數(shù)經(jīng)常與聚合函數(shù)經(jīng)常與SELECT語(yǔ)句的語(yǔ)句的GROUP BY子句一同使用。子句一同使用。所有聚合函數(shù)都具有確定性。任何時(shí)候用一組給定的輸入值調(diào)所有聚合函數(shù)都具有確定性。任何時(shí)候用一組給定的輸入值調(diào)用它們時(shí)，都返回相同的值。用它們時(shí)，都返回相同的值。聚合函數(shù)僅用于以下

3、子句中：聚合函數(shù)僅用于以下子句中：l SELECT子句（子查詢或外部查詢）。子句（子查詢或外部查詢）。l COMPUTE或或COMPUTE BY子句。子句。l HAVING子句。子句。COMPUTE BY子句可以用同一子句可以用同一SELECT語(yǔ)句既查看明語(yǔ)句既查看明細(xì)行，又查看匯總行?？梢杂?jì)算子組的匯總值，也可以計(jì)算細(xì)行，又查看匯總行?？梢杂?jì)算子組的匯總值，也可以計(jì)算整個(gè)結(jié)果集的匯總值。整個(gè)結(jié)果集的匯總值。COMPUTE 子句需要以下信息：子句需要以下信息： l 可選的可選的BY關(guān)鍵字。該關(guān)鍵字可按對(duì)一列計(jì)算指定的關(guān)鍵字。該關(guān)鍵字可按對(duì)一列計(jì)算指定的行聚合。行聚合。l 行聚合函數(shù)名稱。例如，

4、行聚合函數(shù)名稱。例如，SUM、AVG、MIN、MAX或或COUNT。l 要對(duì)其執(zhí)行行聚合函數(shù)的列。要對(duì)其執(zhí)行行聚合函數(shù)的列。（1）COMPUTE生成的結(jié)果集生成的結(jié)果集COMPUTE所生成的匯總值在查詢結(jié)果中顯示為分離的結(jié)所生成的匯總值在查詢結(jié)果中顯示為分離的結(jié)果集。包括果集。包括COMPUTE子句的查詢的結(jié)果類似于控制中斷報(bào)表，子句的查詢的結(jié)果類似于控制中斷報(bào)表，即匯總值由指定的組來(lái)控制的報(bào)表?？梢詾楦鹘M生成匯總值，即匯總值由指定的組來(lái)控制的報(bào)表?？梢詾楦鹘M生成匯總值，也可以對(duì)同一組計(jì)算多個(gè)聚合函數(shù)。也可以對(duì)同一組計(jì)算多個(gè)聚合函數(shù)。當(dāng)當(dāng)COMPUTE帶有可選的帶有可選的BY子句時(shí)，符合子句時(shí)

5、，符合SELECT條件的條件的每個(gè)組都有兩個(gè)結(jié)果集：每個(gè)組都有兩個(gè)結(jié)果集：l 每個(gè)組的第一個(gè)結(jié)果集是明細(xì)行集，其中包含該組每個(gè)組的第一個(gè)結(jié)果集是明細(xì)行集，其中包含該組的選擇列表信息。的選擇列表信息。l 每個(gè)組的第二個(gè)結(jié)果集有一行，其中包含該組的每個(gè)組的第二個(gè)結(jié)果集有一行，其中包含該組的COMPUTE子句中所指定的聚合函數(shù)的小計(jì)。子句中所指定的聚合函數(shù)的小計(jì)。當(dāng)當(dāng)COMPUTE不帶可選的不帶可選的BY子句時(shí)，子句時(shí)，SELECT語(yǔ)句有兩語(yǔ)句有兩個(gè)結(jié)果集：個(gè)結(jié)果集：n 每個(gè)組的第一個(gè)結(jié)果集是包含選擇列表信息的所有明每個(gè)組的第一個(gè)結(jié)果集是包含選擇列表信息的所有明細(xì)行。細(xì)行。n 第二個(gè)結(jié)果集有一行，其

6、中包含第二個(gè)結(jié)果集有一行，其中包含COMPUTE子句中所子句中所指定的聚合函數(shù)的合計(jì)。指定的聚合函數(shù)的合計(jì)?！纠纠?0.1】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolSELECT 學(xué)號(hào)學(xué)號(hào),課程號(hào)課程號(hào),分?jǐn)?shù)分?jǐn)?shù) FROM scoreWHERE 學(xué)號(hào)學(xué)號(hào) IN (103,105)ORDER BY 學(xué)號(hào)學(xué)號(hào)COMPUTE SUM(分?jǐn)?shù)分?jǐn)?shù))解：解：該程序中該程序中SELECT語(yǔ)句使用簡(jiǎn)單語(yǔ)句使用簡(jiǎn)單COMPUTE子句生子句生成成score表中分?jǐn)?shù)列的求和總計(jì)。執(zhí)行結(jié)果如下圖所示。表中分?jǐn)?shù)列的求和總計(jì)。執(zhí)行結(jié)果如下圖所示?！纠纠?0.2】 給出以下程序的執(zhí)行結(jié)果。給

7、出以下程序的執(zhí)行結(jié)果。USE schoolSELECT 學(xué)號(hào)學(xué)號(hào),課程號(hào)課程號(hào),分?jǐn)?shù)分?jǐn)?shù)FROM scoreWHERE 學(xué)號(hào)學(xué)號(hào) IN (103,105)ORDER BY 學(xué)號(hào)學(xué)號(hào)COMPUTE SUM(分?jǐn)?shù)分?jǐn)?shù)) BY 學(xué)號(hào)學(xué)號(hào)解：解：該程序中的查詢?cè)谠摮绦蛑械牟樵冊(cè)贑OMPUTE子句中加入可選的子句中加入可選的BY關(guān)鍵字，以生成每個(gè)組的小計(jì)。執(zhí)行結(jié)果如下圖所示。關(guān)鍵字，以生成每個(gè)組的小計(jì)。執(zhí)行結(jié)果如下圖所示。（2）比較）比較COMPUTE和和GROUP BY的功能的功能COMPUTE和和GROUP BY之間的區(qū)別匯總?cè)缦拢褐g的區(qū)別匯總?cè)缦拢簂 GROUP BY生成單個(gè)結(jié)果集。每個(gè)組都有一

8、個(gè)只包含分組生成單個(gè)結(jié)果集。每個(gè)組都有一個(gè)只包含分組依據(jù)列和顯示該組子聚合的聚合函數(shù)的行。選擇列表只能依據(jù)列和顯示該組子聚合的聚合函數(shù)的行。選擇列表只能包含分組依據(jù)列和聚合函數(shù)。包含分組依據(jù)列和聚合函數(shù)。l COMPUTE生成多個(gè)結(jié)果集。一類結(jié)果集包含每個(gè)組的明生成多個(gè)結(jié)果集。一類結(jié)果集包含每個(gè)組的明細(xì)行，其中包含選擇列表中的表達(dá)式。另一類結(jié)果集包含細(xì)行，其中包含選擇列表中的表達(dá)式。另一類結(jié)果集包含組的子聚合，或組的子聚合，或SELECT語(yǔ)句的總聚合。選擇列表可包含語(yǔ)句的總聚合。選擇列表可包含除分組依據(jù)列或聚合函數(shù)之外的其他表達(dá)式。聚合函數(shù)在除分組依據(jù)列或聚合函數(shù)之外的其他表達(dá)式。聚合函數(shù)在C

9、OMPUTE子句中指定，而不是在選擇列表中。子句中指定，而不是在選擇列表中。2GROUP BY子句子句GROUP BY子句的語(yǔ)法格式為：子句的語(yǔ)法格式為：GROUP BY ALL 分組表達(dá)式分組表達(dá)式 ,nWITH CUBE | ROLLUP l ALL。包含所有組和結(jié)果集，甚至包含那些任何行都不滿。包含所有組和結(jié)果集，甚至包含那些任何行都不滿足足WHERE子句指定的搜索條件的組和結(jié)果集。如果指定了子句指定的搜索條件的組和結(jié)果集。如果指定了ALL，將對(duì)組中不滿足搜索條件的匯總列返回空值。，將對(duì)組中不滿足搜索條件的匯總列返回空值。l CUBE。指定在結(jié)果集內(nèi)，不僅包含由。指定在結(jié)果集內(nèi)，不僅包含

10、由GROUP BY提供的提供的正常行，還包含匯總行。在結(jié)果集內(nèi)返回每個(gè)可能的組和正常行，還包含匯總行。在結(jié)果集內(nèi)返回每個(gè)可能的組和子組組合的子組組合的GROUP BY匯總行。匯總行。GROUP BY匯總行在結(jié)果匯總行在結(jié)果中顯示為中顯示為NULL，但可用來(lái)表示所有值。，但可用來(lái)表示所有值。l ROLLUP。指定在結(jié)果集內(nèi)不僅包含由。指定在結(jié)果集內(nèi)不僅包含由GROUP BY提供的提供的正常行，還包含匯總行。按層次結(jié)構(gòu)順序，從組內(nèi)的最低正常行，還包含匯總行。按層次結(jié)構(gòu)順序，從組內(nèi)的最低級(jí)別到最高級(jí)別匯總組。級(jí)別到最高級(jí)別匯總組?！纠纠?0.3】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。U

11、SE schoolSELECT student.班號(hào)班號(hào),course.課程名課程名,AVG(score.分?jǐn)?shù)分?jǐn)?shù)) AS 平均分平均分FROM student,course,score圖圖10.3 程序執(zhí)行結(jié)果程序執(zhí)行結(jié)果WHERE student.學(xué)號(hào)學(xué)號(hào)=score.學(xué)號(hào)學(xué)號(hào) AND course.課程號(hào)課程號(hào)=score.課程號(hào)課程號(hào)GROUP BY student.班號(hào)班號(hào),course.課程名課程名 WITH CUBE帶帶ROLLUP參數(shù)會(huì)依據(jù)參數(shù)會(huì)依據(jù)GROUP BY后面所列第一個(gè)字段后面所列第一個(gè)字段做匯總運(yùn)算。做匯總運(yùn)算?！纠纠?0.4】 給出以下程序的執(zhí)行結(jié)果。圖給出以下

12、程序的執(zhí)行結(jié)果。圖10.4 程序執(zhí)行程序執(zhí)行結(jié)果結(jié)果USE schoolSELECT student.班號(hào)班號(hào),AVG(score.分?jǐn)?shù)分?jǐn)?shù)) AS 平均分平均分FROM student,course,scoreWHERE student.學(xué)號(hào)學(xué)號(hào)=score.學(xué)號(hào)學(xué)號(hào)GROUP BY student.班號(hào)班號(hào) WITH ROLLUP10.1.2 復(fù)雜連接查詢復(fù)雜連接查詢通過(guò)連接，可以根據(jù)各個(gè)表之間的邏輯關(guān)系從兩個(gè)或多個(gè)通過(guò)連接，可以根據(jù)各個(gè)表之間的邏輯關(guān)系從兩個(gè)或多個(gè)表中檢索數(shù)據(jù)。連接表示表中檢索數(shù)據(jù)。連接表示SQL Server 2005應(yīng)如何使用一個(gè)表應(yīng)如何使用一個(gè)表中的數(shù)據(jù)來(lái)選擇另一個(gè)

13、表中的行。中的數(shù)據(jù)來(lái)選擇另一個(gè)表中的行?？稍诳稍贔ROM或或WHERE子句中指定連接。連接條件與子句中指定連接。連接條件與WHERE和和HAVING搜索條件組合，用于控制搜索條件組合，用于控制FROM子句引用子句引用的基表中所選定的行。的基表中所選定的行。在在FROM子句中指定連接條件，有助于將這些連接條件與子句中指定連接條件，有助于將這些連接條件與WHERE子句中可能指定的其他搜索條件分開(kāi)，指定連接時(shí)建子句中可能指定的其他搜索條件分開(kāi)，指定連接時(shí)建議使用這種方法。簡(jiǎn)單的子句連接語(yǔ)法如下：議使用這種方法。簡(jiǎn)單的子句連接語(yǔ)法如下：FROM 第一個(gè)表名第一個(gè)表名 連接類型連接類型 第二個(gè)表名第二個(gè)

14、表名 ON (連接條件連接條件)其中，連接類型有內(nèi)連接、外連接或交叉連接。其中，連接類型有內(nèi)連接、外連接或交叉連接?！纠纠?0.5】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolSELECT allocate.班號(hào)班號(hào),allocate.課程號(hào)課程號(hào),teacher.姓名姓名FROM allocate JOIN teacher ON (allocate.教師編號(hào)教師編號(hào)=teacher.編號(hào)編號(hào))ORDER BY allocate.班號(hào)班號(hào)1內(nèi)連接內(nèi)連接內(nèi)連接是用比較運(yùn)算符比較要連接列的值的連接。在內(nèi)連接是用比較運(yùn)算符比較要連接列的值的連接。在SQL-92標(biāo)準(zhǔn)中，內(nèi)連

15、接可在標(biāo)準(zhǔn)中，內(nèi)連接可在FROM或或WHERE子句中指定。這是子句中指定。這是WHERE子句中唯一一種子句中唯一一種SQL-92支持的連接類型。支持的連接類型。WHERE子句中指定的子句中指定的內(nèi)連接稱為舊式內(nèi)連接。內(nèi)連接稱為舊式內(nèi)連接。內(nèi)連接使用內(nèi)連接使用INNER JOIN關(guān)鍵詞，上面查詢各課程的任課教關(guān)鍵詞，上面查詢各課程的任課教師姓名的例子就是一個(gè)內(nèi)連接的例子，也可以按下面方式查詢：師姓名的例子就是一個(gè)內(nèi)連接的例子，也可以按下面方式查詢： USE schoolSELECT allocate.班號(hào)班號(hào),allocate.課程號(hào)課程號(hào),teacher.姓名姓名FROM allocate I

16、NNER JOIN teacher ON (allocate.教師編號(hào)教師編號(hào)=teacher.編號(hào)編號(hào))ORDER BY allocate.班號(hào)班號(hào)2外連接外連接僅當(dāng)至少有一個(gè)同屬于兩表的行符合連接條件時(shí)，內(nèi)連接僅當(dāng)至少有一個(gè)同屬于兩表的行符合連接條件時(shí)，內(nèi)連接才返回行。內(nèi)連接消除與另一個(gè)表中的任何行不匹配的行。而才返回行。內(nèi)連接消除與另一個(gè)表中的任何行不匹配的行。而外連接會(huì)返回外連接會(huì)返回FROM子句中提到的至少一個(gè)表或視圖的所有行，子句中提到的至少一個(gè)表或視圖的所有行，只要這些行符合任何只要這些行符合任何WHERE或或HAVING搜索條件。搜索條件。將檢索通過(guò)左外連接引用的左表的所有行，

17、以及通過(guò)右外將檢索通過(guò)左外連接引用的左表的所有行，以及通過(guò)右外連接引用的右表的所有行。全外連接中兩個(gè)表的所有行都將返連接引用的右表的所有行。全外連接中兩個(gè)表的所有行都將返回?；?。SQL Server 2005對(duì)在對(duì)在FROM子句中指定的外連接使用以下子句中指定的外連接使用以下關(guān)鍵字：關(guān)鍵字：l LEFT OUTER JOIN或或LEFT JOIN（左外連接）（左外連接）l RIGHT OUTER JOIN或或RIGHT JOIN（右外連接）（右外連接）l FULL OUTER JOIN或或FULL JOIN（全外連接）（全外連接）（1）左外連接）左外連接左外連接簡(jiǎn)稱為左連接，其結(jié)果包括第一個(gè)命

18、名表（左外連接簡(jiǎn)稱為左連接，其結(jié)果包括第一個(gè)命名表（“左左”表，出現(xiàn)在表，出現(xiàn)在JOIN子句的最左邊）中的所有行，不包括右表中的子句的最左邊）中的所有行，不包括右表中的不匹配行。不匹配行?！纠纠?0.6】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolINSERT INTO allocate(班號(hào)班號(hào),課程號(hào)課程號(hào)) VALUES(1031,9-888)SELECT allocate.班號(hào)班號(hào),allocate.課程號(hào)課程號(hào),teacher.姓名姓名FROM allocate LEFT JOIN teacher ON (allocate.教師編號(hào)教師編號(hào)=teacher

19、.編號(hào)編號(hào))ORDER BY allocate.班號(hào)班號(hào)DELETE allocate WHERE 班號(hào)班號(hào)=1031 AND 課程號(hào)課程號(hào)=9-888（2）右外連接）右外連接右外連接簡(jiǎn)稱為右連接，其結(jié)果中包括第二個(gè)命名表右外連接簡(jiǎn)稱為右連接，其結(jié)果中包括第二個(gè)命名表（“右右”表，出現(xiàn)在表，出現(xiàn)在JOIN子句的最右邊）中的所有行，不包括子句的最右邊）中的所有行，不包括左表中的不匹配行。左表中的不匹配行?！纠纠?0.7】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolINSERT INTO allocate(班號(hào)班號(hào),課程號(hào)課程號(hào)) VALUES(1031,9-888)S

20、ELECT allocate.班號(hào)班號(hào),allocate.課程號(hào)課程號(hào),teacher.姓名姓名FROM allocate RIGHT JOIN teacher ON (allocate.教師編號(hào)教師編號(hào)=teacher.編號(hào)編號(hào)) ORDER BY allocate.班號(hào)班號(hào)DELETE allocate WHERE 班號(hào)班號(hào)=1031 AND 課程號(hào)課程號(hào)=9-888 （3）全外連接）全外連接若要通過(guò)在連接結(jié)果中包括不匹配的行保留不匹配信息，若要通過(guò)在連接結(jié)果中包括不匹配的行保留不匹配信息，可以使用全外連接?？梢允褂萌膺B接。SQL Server 2005提供全外連接運(yùn)算符提供全外連接運(yùn)算

21、符FULL OUTER JOIN，不管另一個(gè)表是否有匹配的值，此運(yùn)算，不管另一個(gè)表是否有匹配的值，此運(yùn)算符都包括兩個(gè)表中的所有行。符都包括兩個(gè)表中的所有行?！纠纠?0.8】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolINSERT INTO allocate(班號(hào)班號(hào),課程號(hào)課程號(hào)) VALUES(1031,9-888)SELECT allocate.班號(hào)班號(hào),allocate.課程號(hào)課程號(hào),teacher.姓名姓名FROM allocate FULL OUTER JOIN teacher ON (allocate.教師編號(hào)教師編號(hào)=teacher.編號(hào)編號(hào))ORDE

22、R BY allocate.班號(hào)班號(hào)DELETE allocate WHERE 班號(hào)班號(hào)=1031 AND 課程號(hào)課程號(hào)=9-8883交叉連接交叉連接在這類連接的結(jié)果集內(nèi)，兩個(gè)表中每?jī)蓚€(gè)可能成對(duì)的行占在這類連接的結(jié)果集內(nèi)，兩個(gè)表中每?jī)蓚€(gè)可能成對(duì)的行占一行。交叉連接不使用一行。交叉連接不使用WHERE子句。在數(shù)學(xué)上，就是表的笛子句。在數(shù)學(xué)上，就是表的笛卡爾積。第一個(gè)表的行數(shù)乘以第二個(gè)表的行數(shù)等于笛卡爾積結(jié)卡爾積。第一個(gè)表的行數(shù)乘以第二個(gè)表的行數(shù)等于笛卡爾積結(jié)果集的大小。果集的大小?！纠纠?0.9】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolSELECT course.

23、課程名課程名,teacher.姓名姓名FROM course CROSS JOIN teacher結(jié)果沒(méi)有實(shí)際意義。結(jié)果沒(méi)有實(shí)際意義。10.1.3 復(fù)雜子查詢復(fù)雜子查詢子查詢能夠?qū)⒈容^復(fù)雜的查詢分解為幾個(gè)簡(jiǎn)單的查詢，而子查詢能夠?qū)⒈容^復(fù)雜的查詢分解為幾個(gè)簡(jiǎn)單的查詢，而且子查詢可以嵌套。且子查詢可以嵌套。 1子查詢規(guī)則子查詢規(guī)則嵌套在外部嵌套在外部SELECT語(yǔ)句中的子查詢可以包括以下子句：語(yǔ)句中的子查詢可以包括以下子句：l 包含標(biāo)準(zhǔn)選擇列表組件的標(biāo)準(zhǔn)包含標(biāo)準(zhǔn)選擇列表組件的標(biāo)準(zhǔn)SELECT查詢。查詢。l 包含一個(gè)或多個(gè)表或者視圖名的標(biāo)準(zhǔn)包含一個(gè)或多個(gè)表或者視圖名的標(biāo)準(zhǔn)FROM子句。子句。l 可選

24、的可選的WHERE子句。子句。l 可選的可選的GROUP BY子句。子句。l 可選的可選的HAVING子句。子句。在在SQL Server 2005中，子查詢還要受下面的條件限制：中，子查詢還要受下面的條件限制：l 通過(guò)比較運(yùn)算符引入的子查詢的選擇列表只能包括一個(gè)通過(guò)比較運(yùn)算符引入的子查詢的選擇列表只能包括一個(gè)表達(dá)式或列名稱（分別對(duì)表達(dá)式或列名稱（分別對(duì)SELECT *或列表進(jìn)行或列表進(jìn)行EXISTS和和IN操作除外）。操作除外）。l 如果外部查詢的如果外部查詢的WHERE子句包括某個(gè)列名，則該子句子句包括某個(gè)列名，則該子句必須與子查詢選擇列表中的該列在連接上兼容。必須與子查詢選擇列表中的該列

25、在連接上兼容。l 子查詢的選擇列表中不允許出現(xiàn)子查詢的選擇列表中不允許出現(xiàn)ntext、text和和image數(shù)據(jù)數(shù)據(jù)類型。類型。l 由于不修改數(shù)據(jù)的比較運(yùn)算符（指其后未接關(guān)鍵字由于不修改數(shù)據(jù)的比較運(yùn)算符（指其后未接關(guān)鍵字IN、ANY或或ALL等）的引入，這類子查詢必須返回單個(gè)值，等）的引入，這類子查詢必須返回單個(gè)值，而且子查詢中不能包括而且子查詢中不能包括GROUP BY和和HAVING子句。子句。l 包括包括GROUP BY的子查詢不能使用的子查詢不能使用DISTINCT關(guān)鍵字。關(guān)鍵字。l 不能指定不能指定COMPUTE和和INTO子句。子句。l TOP子句子句用于指定要返回的記錄個(gè)數(shù)，如用

26、于指定要返回的記錄個(gè)數(shù)，如SELECT TOP 3或或30 PERCENT FROM student表示顯示前表示顯示前3個(gè)記錄或者滿個(gè)記錄或者滿足條件的足條件的30的記錄。只有同時(shí)指定了的記錄。只有同時(shí)指定了TOP，才可以指定，才可以指定ORDER BY。l 由子查詢創(chuàng)建的視圖不能更新。由子查詢創(chuàng)建的視圖不能更新。l 按約定，通過(guò)按約定，通過(guò)EXISTS引入的子查詢的選擇列表由星號(hào)（引入的子查詢的選擇列表由星號(hào)（*）組成，而不使用單個(gè)列名。由于通過(guò)組成，而不使用單個(gè)列名。由于通過(guò)EXISTS引入的子查引入的子查詢進(jìn)行了存在測(cè)試，并返回詢進(jìn)行了存在測(cè)試，并返回TRUE或或FALSE而非數(shù)據(jù)，所

27、而非數(shù)據(jù)，所以這些子查詢的規(guī)則與標(biāo)準(zhǔn)選擇列表的規(guī)則完全相同。以這些子查詢的規(guī)則與標(biāo)準(zhǔn)選擇列表的規(guī)則完全相同。2子查詢類型子查詢類型有如下有如下3種常用的子查詢類型：種常用的子查詢類型：l 在通過(guò)在通過(guò)IN引入的列表或者由引入的列表或者由ANY或或ALL修改的比較運(yùn)算符修改的比較運(yùn)算符的列表上進(jìn)行操作。的列表上進(jìn)行操作。l 通過(guò)不修改數(shù)據(jù)的比較運(yùn)算符（指其后未接關(guān)鍵字通過(guò)不修改數(shù)據(jù)的比較運(yùn)算符（指其后未接關(guān)鍵字IN、ANY或或ALL等）引入，并且必須返回單個(gè)值。等）引入，并且必須返回單個(gè)值。l 通過(guò)通過(guò)EXISTS引入的存在測(cè)試。引入的存在測(cè)試。上述上述3種子查詢通常采用的格式有下面幾種：種子查

28、詢通常采用的格式有下面幾種：l WHERE 表達(dá)式表達(dá)式 NOT IN (子查詢子查詢)l WHERE 表達(dá)式表達(dá)式 比較運(yùn)算符比較運(yùn)算符 ANY | ALL (子查詢子查詢)l WHERE NOT EXISTS (子查詢子查詢) （1）使用）使用IN或或NOT IN通過(guò)通過(guò)IN（或（或NOT IN）引入的子查詢結(jié)果是沒(méi)有值或多個(gè)）引入的子查詢結(jié)果是沒(méi)有值或多個(gè)值。子查詢返回結(jié)果之后，外部查詢將利用這些結(jié)果。值。子查詢返回結(jié)果之后，外部查詢將利用這些結(jié)果?！纠纠?0.10】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolSELECT student.學(xué)號(hào)學(xué)號(hào),stude

29、nt.姓名姓名FROM studentWHERE student.學(xué)號(hào)學(xué)號(hào) IN(SELECT score.學(xué)號(hào)學(xué)號(hào) FROM score WHERE score.課程號(hào)課程號(hào)=6-166)執(zhí)行結(jié)果執(zhí)行結(jié)果USE schoolSELECT student.學(xué)號(hào)學(xué)號(hào),student.姓名姓名 FROM studentWHERE student.學(xué)號(hào)學(xué)號(hào) NOT IN (SELECT score.學(xué)號(hào)學(xué)號(hào) FROM score WHERE score.課程號(hào)課程號(hào)=6-166)執(zhí)行結(jié)果執(zhí)行結(jié)果（2）使用）使用ANY或或ALLANY或或ALL通常與關(guān)系運(yùn)算符連用，如通常與關(guān)系運(yùn)算符連用，如 ANY(

30、子查詢子查詢)表表示大于任意子查詢的結(jié)果。示大于任意子查詢的結(jié)果?！纠纠?0.12】 給出功能為給出功能為“查詢其平均分高于所有課程查詢其平均分高于所有課程平均分的學(xué)生學(xué)號(hào)和它們的平均分平均分的學(xué)生學(xué)號(hào)和它們的平均分”的程序及其執(zhí)行結(jié)果。的程序及其執(zhí)行結(jié)果。圖圖10.13 程序執(zhí)行結(jié)果程序執(zhí)行結(jié)果解：解：對(duì)應(yīng)的程序如下：對(duì)應(yīng)的程序如下：USE schoolSELECT 學(xué)號(hào)學(xué)號(hào),AVG(分?jǐn)?shù)分?jǐn)?shù)) AS 平均分平均分FROM score GROUP BY 學(xué)號(hào)學(xué)號(hào)HAVING AVG(分?jǐn)?shù)分?jǐn)?shù)) ALL (SELECT AVG(分?jǐn)?shù)分?jǐn)?shù)) FROM score WHERE 分?jǐn)?shù)分?jǐn)?shù)IS NO

31、T NULL)（3）使用）使用EXISTS在子查詢中，還可以使用在子查詢中，還可以使用EXISTS，它一般用在，它一般用在WHERE子句中，其后緊跟一個(gè)子查詢，從而構(gòu)成一個(gè)條件，當(dāng)該子子句中，其后緊跟一個(gè)子查詢，從而構(gòu)成一個(gè)條件，當(dāng)該子查詢至少存在一個(gè)返回值時(shí)，這個(gè)條件為真，否則為假。查詢至少存在一個(gè)返回值時(shí)，這個(gè)條件為真，否則為假?！纠纠?0.13】 給出功能為給出功能為“查詢所有任課教師的姓名和查詢所有任課教師的姓名和單位單位”的程序及其執(zhí)行結(jié)果。的程序及其執(zhí)行結(jié)果。解：解：對(duì)應(yīng)的程序如下：對(duì)應(yīng)的程序如下：USE schoolSELECT 姓名姓名,單位單位FROM teacher a

32、WHERE EXISTS(SELECT * FROM allocate b WHERE a.編號(hào)編號(hào)=b.教師編號(hào)教師編號(hào))【例【例10.14】 給出功能為給出功能為“查詢所有未講課的教師的姓名查詢所有未講課的教師的姓名和單位和單位”的程序及其執(zhí)行結(jié)果。的程序及其執(zhí)行結(jié)果。解：解：對(duì)應(yīng)的程序如下：對(duì)應(yīng)的程序如下：USE schoolSELECT 姓名姓名,單位單位FROM teacher a WHERE NOT EXISTS(SELECT * FROM allocate b WHERE a.編號(hào)編號(hào)=b.教師編號(hào)教師編號(hào))3多層嵌套多層嵌套子查詢可以嵌套在外部子查詢可以嵌套在外部SELECT、

33、INSERT、UPDATE或或DELETE語(yǔ)句的語(yǔ)句的WHERE或或HAVING子句內(nèi)，或者其他子查子句內(nèi)，或者其他子查詢中。詢中。盡管根據(jù)可用內(nèi)存和查詢中其他表達(dá)式的復(fù)雜程度不同，盡管根據(jù)可用內(nèi)存和查詢中其他表達(dá)式的復(fù)雜程度不同，嵌套限制也有所不同，但一般均可以嵌套到嵌套限制也有所不同，但一般均可以嵌套到32層。層?！纠纠?0.15】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolSELECT 姓名姓名,班號(hào)班號(hào)FROM studentWHERE 學(xué)號(hào)學(xué)號(hào)=(SELECT 學(xué)號(hào)學(xué)號(hào) FROM score WHERE 分?jǐn)?shù)分?jǐn)?shù)=(SELECT MAX(分?jǐn)?shù)分?jǐn)?shù)) FRO

34、M score)10.1.4 數(shù)據(jù)來(lái)源是一個(gè)查詢的結(jié)果數(shù)據(jù)來(lái)源是一個(gè)查詢的結(jié)果在查詢語(yǔ)句中，在查詢語(yǔ)句中，F(xiàn)ROM指定數(shù)據(jù)來(lái)源，它可以是一個(gè)或指定數(shù)據(jù)來(lái)源，它可以是一個(gè)或多個(gè)表。實(shí)際上，由多個(gè)表。實(shí)際上，由FROM指定的數(shù)據(jù)來(lái)源也可以是一個(gè)指定的數(shù)據(jù)來(lái)源也可以是一個(gè)SELECT查詢的結(jié)果。查詢的結(jié)果?！纠纠?0.16】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolSELECT 課程號(hào)課程號(hào),avgs AS 平均分平均分FROM (SELECT 課程號(hào)課程號(hào),AVG(分?jǐn)?shù)分?jǐn)?shù)) avgs FROM score GROUP BY 課程號(hào)課程號(hào)) TORDER BY avg

35、s DESC【例【例10.17】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolSELECT 班號(hào)班號(hào),學(xué)號(hào)學(xué)號(hào),姓名姓名,MAX(分?jǐn)?shù)分?jǐn)?shù)) 最分?jǐn)?shù)最分?jǐn)?shù)FROM (SELECT s.學(xué)號(hào)學(xué)號(hào),s.姓名姓名,s.班號(hào)班號(hào),c.課程名課程名,sc.分?jǐn)?shù)分?jǐn)?shù) FROM student s,course c,score sc WHERE s.學(xué)號(hào)學(xué)號(hào)=sc.學(xué)號(hào)學(xué)號(hào)AND c.課程號(hào)課程號(hào)=sc.課程號(hào)課程號(hào)AND 分?jǐn)?shù)分?jǐn)?shù)IS NOT NULL) TGROUP BY 班號(hào)班號(hào),學(xué)號(hào)學(xué)號(hào),姓名姓名ORDER BY 班號(hào)班號(hào),學(xué)號(hào)學(xué)號(hào)10.2 事事 務(wù)務(wù) 處處 理理 事務(wù)是事務(wù)

36、是SQL Server中的單個(gè)邏輯單元，一個(gè)事務(wù)內(nèi)的中的單個(gè)邏輯單元，一個(gè)事務(wù)內(nèi)的所有所有SQL語(yǔ)句作為一個(gè)整體執(zhí)行，要么全部執(zhí)行，要么都語(yǔ)句作為一個(gè)整體執(zhí)行，要么全部執(zhí)行，要么都不執(zhí)行。不執(zhí)行。一個(gè)邏輯工作單元必須有四個(gè)特性，稱為一個(gè)邏輯工作單元必須有四個(gè)特性，稱為ACID（原子（原子性、一致性、隔離性和持久性）屬性，只有這樣才能成為性、一致性、隔離性和持久性）屬性，只有這樣才能成為一個(gè)事務(wù)。一個(gè)事務(wù)。 10.2.1 事務(wù)分類事務(wù)分類按事務(wù)的啟動(dòng)和執(zhí)行方式，可以將事務(wù)分為三類：按事務(wù)的啟動(dòng)和執(zhí)行方式，可以將事務(wù)分為三類：l 顯式事務(wù)。也稱為用戶定義或用戶指定的事務(wù)，即可以顯式事務(wù)。也稱為用戶

37、定義或用戶指定的事務(wù)，即可以顯式地定義啟動(dòng)和結(jié)束的事務(wù)。顯式地定義啟動(dòng)和結(jié)束的事務(wù)。l 自動(dòng)提交事務(wù)。自動(dòng)提交模式是自動(dòng)提交事務(wù)。自動(dòng)提交模式是SQL Server的默認(rèn)事務(wù)的默認(rèn)事務(wù)管理模式。每個(gè)管理模式。每個(gè)T-SQL語(yǔ)句在完成時(shí)，都被提交或回滾。語(yǔ)句在完成時(shí)，都被提交或回滾。如果一個(gè)語(yǔ)句成功地完成，則提交該語(yǔ)句；如果遇到錯(cuò)如果一個(gè)語(yǔ)句成功地完成，則提交該語(yǔ)句；如果遇到錯(cuò)誤，則回滾該語(yǔ)句。誤，則回滾該語(yǔ)句。l 隱性事務(wù)。當(dāng)連接以隱性事務(wù)模式進(jìn)行操作時(shí)，隱性事務(wù)。當(dāng)連接以隱性事務(wù)模式進(jìn)行操作時(shí)，SQL Server將在提交或回滾當(dāng)前事務(wù)后自動(dòng)啟動(dòng)新事務(wù)。無(wú)須將在提交或回滾當(dāng)前事務(wù)后自動(dòng)啟動(dòng)新

38、事務(wù)。無(wú)須描述事務(wù)的開(kāi)始，只須提交或回滾每個(gè)事務(wù)。描述事務(wù)的開(kāi)始，只須提交或回滾每個(gè)事務(wù)。10.2.2 顯式事務(wù)顯式事務(wù)顯式事務(wù)需要顯式地定義事務(wù)的啟動(dòng)和結(jié)束。它是通過(guò)顯式事務(wù)需要顯式地定義事務(wù)的啟動(dòng)和結(jié)束。它是通過(guò)BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION或或ROLLBACK WORK等等T-SQL語(yǔ)句來(lái)完成的。語(yǔ)句來(lái)完成的。1啟動(dòng)事務(wù)啟動(dòng)事務(wù)啟動(dòng)事務(wù)使用啟動(dòng)事務(wù)使用BEGIN TRANSACTION語(yǔ)句，執(zhí)行該語(yǔ)語(yǔ)句，執(zhí)行該語(yǔ)句會(huì)將句會(huì)將TRANCOUNT加加1。其語(yǔ)法格式如下：。其語(yǔ)法格式如下

39、：BEGIN TRANSACTION tran_name | tran_name_variable WITH MARK desp2結(jié)束事務(wù)結(jié)束事務(wù)如果沒(méi)有遇到錯(cuò)誤，可使用如果沒(méi)有遇到錯(cuò)誤，可使用COMMIT TRANSACTION語(yǔ)語(yǔ)句成功地結(jié)束事務(wù)。該事務(wù)中的所有數(shù)據(jù)修改在數(shù)據(jù)庫(kù)中都將句成功地結(jié)束事務(wù)。該事務(wù)中的所有數(shù)據(jù)修改在數(shù)據(jù)庫(kù)中都將永久有效。事務(wù)占用的資源將被釋放。永久有效。事務(wù)占用的資源將被釋放。COMMIT TRANSACTION語(yǔ)句的語(yǔ)法格式如下：語(yǔ)句的語(yǔ)法格式如下：COMMIT TRANSACTION tran_name | tran_name_variable3回滾事務(wù)回滾事

40、務(wù)如果事務(wù)中出現(xiàn)錯(cuò)誤，或者用戶決定取消事務(wù)，可回滾該如果事務(wù)中出現(xiàn)錯(cuò)誤，或者用戶決定取消事務(wù)，可回滾該事務(wù)。回滾事務(wù)是通過(guò)事務(wù)?；貪L事務(wù)是通過(guò)ROLLBACK語(yǔ)句來(lái)完成的。其語(yǔ)法格語(yǔ)句來(lái)完成的。其語(yǔ)法格式如下：式如下：ROLLBACK TRANSACTION tran_name | tran_name_variable | savepoint_name | savepoint_variable【例【例10.18】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolGOBEGIN TRANSACTION-啟動(dòng)事務(wù)啟動(dòng)事務(wù)INSERT INTO student VALUES(10

41、0,陳浩陳浩,男男,1992/03/05,1033)-插入一個(gè)學(xué)生記錄插入一個(gè)學(xué)生記錄ROLLBACK-回滾事務(wù)回滾事務(wù)GOSELECT * FROM student-查詢查詢student表的記錄表的記錄GO解：解：該程序啟動(dòng)一個(gè)事務(wù)向該程序啟動(dòng)一個(gè)事務(wù)向student表中插入一個(gè)記錄，然表中插入一個(gè)記錄，然后回滾該事務(wù)。正是由于回滾了事務(wù)，所以后回滾該事務(wù)。正是由于回滾了事務(wù)，所以student表中沒(méi)有真表中沒(méi)有真正插入該記錄。正插入該記錄。4在事務(wù)內(nèi)設(shè)置保存點(diǎn)在事務(wù)內(nèi)設(shè)置保存點(diǎn)設(shè)置保存點(diǎn)使用設(shè)置保存點(diǎn)使用SAVE TRANSACTION語(yǔ)句，其語(yǔ)法格式語(yǔ)句，其語(yǔ)法格式為：為：SAVE T

42、RANSACTION savepoint_name | savepoint_variable用戶可以在事務(wù)內(nèi)設(shè)置保存點(diǎn)或標(biāo)記。保存點(diǎn)是如果有條用戶可以在事務(wù)內(nèi)設(shè)置保存點(diǎn)或標(biāo)記。保存點(diǎn)是如果有條件地取消事務(wù)的一部分，事務(wù)可以返回的位置。件地取消事務(wù)的一部分，事務(wù)可以返回的位置?！纠纠?0.19】 給出以下程序的執(zhí)行結(jié)果。給出以下程序的執(zhí)行結(jié)果。USE schoolGOBEGIN TRANSACTION Mytran-啟動(dòng)事務(wù)啟動(dòng)事務(wù) INSERT INTO student VALUES(100,陳浩陳浩,男男,1992/03/05,1033) -插入一個(gè)學(xué)生記錄插入一個(gè)學(xué)生記錄SAVE TRA

43、NSACTION Mytran-保存點(diǎn)保存點(diǎn) INSERT INTO student VALUES(200,王浩王浩,男男,1992/10/05,1031) -插入一個(gè)學(xué)生記錄插入一個(gè)學(xué)生記錄ROLLBACK TRANSACTION MytranCOMMIT TRANSACTIONGOSELECT * FROM student -查詢查詢student表的記錄表的記錄GODELETE student WHERE 學(xué)號(hào)學(xué)號(hào)=100-刪除插入的記錄刪除插入的記錄GO 解：解：該程序設(shè)置了在事務(wù)內(nèi)設(shè)置保存點(diǎn)。執(zhí)行結(jié)果如圖該程序設(shè)置了在事務(wù)內(nèi)設(shè)置保存點(diǎn)。執(zhí)行結(jié)果如圖10.19所示。從結(jié)果看到，由于在事

44、務(wù)內(nèi)設(shè)置保存點(diǎn)所示。從結(jié)果看到，由于在事務(wù)內(nèi)設(shè)置保存點(diǎn)Mytran，ROLLBACK只回滾到該保存點(diǎn)為止，所以只插入保存點(diǎn)前的只回滾到該保存點(diǎn)為止，所以只插入保存點(diǎn)前的一個(gè)記錄。一個(gè)記錄。5標(biāo)記事務(wù)標(biāo)記事務(wù) WITH MARK選項(xiàng)使事務(wù)名置于事務(wù)日志中。將數(shù)據(jù)選項(xiàng)使事務(wù)名置于事務(wù)日志中。將數(shù)據(jù)庫(kù)還原到早期狀態(tài)時(shí)，可使用標(biāo)記事務(wù)替代日期和時(shí)間。庫(kù)還原到早期狀態(tài)時(shí)，可使用標(biāo)記事務(wù)替代日期和時(shí)間。6不能用于事務(wù)的操作不能用于事務(wù)的操作 在事務(wù)處理中，并不是所有的在事務(wù)處理中，并不是所有的T-SQL語(yǔ)句都可以取消執(zhí)語(yǔ)句都可以取消執(zhí)行，一些不能撤消的操作（如創(chuàng)建、刪除和修改數(shù)據(jù)庫(kù)的操行，一些不能撤消的操

45、作（如創(chuàng)建、刪除和修改數(shù)據(jù)庫(kù)的操作），即使作），即使SQL Server取消了事務(wù)執(zhí)行或者對(duì)事務(wù)進(jìn)行了回取消了事務(wù)執(zhí)行或者對(duì)事務(wù)進(jìn)行了回滾，這些操作對(duì)數(shù)據(jù)庫(kù)造成的影響也是不能恢復(fù)的。滾，這些操作對(duì)數(shù)據(jù)庫(kù)造成的影響也是不能恢復(fù)的。 10.2.3 自動(dòng)提交事務(wù)自動(dòng)提交事務(wù)SQL Server使用使用BEGIN TRANSACTION語(yǔ)句啟動(dòng)顯式事務(wù)，語(yǔ)句啟動(dòng)顯式事務(wù)，或隱性事務(wù)模式設(shè)置為打開(kāi)之前，將以自動(dòng)提交模式進(jìn)行操作?；螂[性事務(wù)模式設(shè)置為打開(kāi)之前，將以自動(dòng)提交模式進(jìn)行操作。當(dāng)提交或回滾顯式事務(wù)或者關(guān)閉隱性事務(wù)模式時(shí)，當(dāng)提交或回滾顯式事務(wù)或者關(guān)閉隱性事務(wù)模式時(shí)，SQL Server將返回到自動(dòng)提

46、交模式。將返回到自動(dòng)提交模式。在自動(dòng)提交模式下，有時(shí)看起來(lái)在自動(dòng)提交模式下，有時(shí)看起來(lái)SQL Server好像回滾了整好像回滾了整個(gè)批處理，而不是僅僅一個(gè)個(gè)批處理，而不是僅僅一個(gè)SQL語(yǔ)句。語(yǔ)句。這種情況只有在遇到的錯(cuò)誤是編譯錯(cuò)誤而不是運(yùn)行時(shí)錯(cuò)誤這種情況只有在遇到的錯(cuò)誤是編譯錯(cuò)誤而不是運(yùn)行時(shí)錯(cuò)誤時(shí)才會(huì)發(fā)生。編譯錯(cuò)誤將阻止時(shí)才會(huì)發(fā)生。編譯錯(cuò)誤將阻止SQL Server建立執(zhí)行計(jì)劃，這樣建立執(zhí)行計(jì)劃，這樣批處理中的任何語(yǔ)句都不會(huì)執(zhí)行。盡管看起來(lái)好像是產(chǎn)生錯(cuò)誤批處理中的任何語(yǔ)句都不會(huì)執(zhí)行。盡管看起來(lái)好像是產(chǎn)生錯(cuò)誤之前的所有語(yǔ)句都被回滾了，但實(shí)際情況是該錯(cuò)誤使批處理中之前的所有語(yǔ)句都被回滾了，但實(shí)際情

47、況是該錯(cuò)誤使批處理中的任何語(yǔ)句都沒(méi)有執(zhí)行。的任何語(yǔ)句都沒(méi)有執(zhí)行。10.2.4 隱式事務(wù)隱式事務(wù)在為連接將隱性事務(wù)模式設(shè)置為打開(kāi)之后，當(dāng)在為連接將隱性事務(wù)模式設(shè)置為打開(kāi)之后，當(dāng)SQL Server首次執(zhí)行某些首次執(zhí)行某些T-SQL語(yǔ)句時(shí)，都會(huì)自動(dòng)啟動(dòng)一個(gè)事務(wù)，而不需語(yǔ)句時(shí)，都會(huì)自動(dòng)啟動(dòng)一個(gè)事務(wù)，而不需要使用要使用BEGIN TRANSACTION語(yǔ)句。這些語(yǔ)句。這些T-SQL語(yǔ)句包括：語(yǔ)句包括：ALTER TABLE INSERTOPENCREATEDELETEREVOKEDROPSELECTFETCHTRUNCATE TABLEGRANTUPDATE10.3 數(shù)據(jù)的鎖定數(shù)據(jù)的鎖定10.3.1

48、SQL Server中的鎖定中的鎖定SQL Server 2005具有多粒度鎖定，允許一個(gè)事務(wù)鎖定不具有多粒度鎖定，允許一個(gè)事務(wù)鎖定不同類型的資源。同類型的資源。 SQL Server可以鎖定的資源如下表所示（表可以鎖定的資源如下表所示（表中按粒度增加的順序列出）。中按粒度增加的順序列出）。資源資源描述描述RID行標(biāo)識(shí)符。用于單獨(dú)鎖定表中的一行行標(biāo)識(shí)符。用于單獨(dú)鎖定表中的一行鍵（鍵（KEY）索引中的行鎖。用于保護(hù)可串行事務(wù)中的鍵范圍索引中的行鎖。用于保護(hù)可串行事務(wù)中的鍵范圍頁(yè)（頁(yè)（PAG）8 KB數(shù)據(jù)頁(yè)或索引頁(yè)數(shù)據(jù)頁(yè)或索引頁(yè)擴(kuò)展盤區(qū)（擴(kuò)展盤區(qū)（EXT）相鄰的相鄰的8個(gè)數(shù)據(jù)頁(yè)或索引頁(yè)構(gòu)成的一組個(gè)

49、數(shù)據(jù)頁(yè)或索引頁(yè)構(gòu)成的一組表（表（TAB）包括所有數(shù)據(jù)和索引在內(nèi)的整個(gè)表包括所有數(shù)據(jù)和索引在內(nèi)的整個(gè)表DB數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)SQL Server使用不同的鎖定模式鎖定資源，這些鎖定模使用不同的鎖定模式鎖定資源，這些鎖定模式確定了并發(fā)事務(wù)訪問(wèn)資源的方式，如下表所示。式確定了并發(fā)事務(wù)訪問(wèn)資源的方式，如下表所示。鎖模式鎖模式描述描述共享（共享（S）用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如SELECT語(yǔ)句語(yǔ)句更新（更新（U）用于可更新的資源中。防止當(dāng)多個(gè)會(huì)話在讀取、鎖定以及用于可更新的資源中。防止當(dāng)多個(gè)會(huì)話在讀取、鎖定以及隨后可能進(jìn)行的資源更新時(shí)發(fā)生常見(jiàn)形式的死

50、鎖隨后可能進(jìn)行的資源更新時(shí)發(fā)生常見(jiàn)形式的死鎖排它（排它（X）用于數(shù)據(jù)修改操作，例如用于數(shù)據(jù)修改操作，例如INSERT、UPDATE或或DELETE。確保不會(huì)同時(shí)對(duì)同一資源進(jìn)行多重更新確保不會(huì)同時(shí)對(duì)同一資源進(jìn)行多重更新意向意向用于建立鎖的層次結(jié)構(gòu)。意向鎖的類型為：意向共享用于建立鎖的層次結(jié)構(gòu)。意向鎖的類型為：意向共享（IS）、意向排它（）、意向排它（IX）以及與意向排它共享（）以及與意向排它共享（SIX）架構(gòu)架構(gòu)在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用。架構(gòu)鎖的類型為：架在執(zhí)行依賴于表架構(gòu)的操作時(shí)使用。架構(gòu)鎖的類型為：架構(gòu)修改（構(gòu)修改（Sch-M）和架構(gòu)穩(wěn)定性（）和架構(gòu)穩(wěn)定性（Sch-S）大容量更新（大容

51、量更新（BU）向表中大容量復(fù)制數(shù)據(jù)并指定了向表中大容量復(fù)制數(shù)據(jù)并指定了TABLOCK提示時(shí)使用提示時(shí)使用1共享鎖共享鎖共享鎖允許并發(fā)事務(wù)讀取（共享鎖允許并發(fā)事務(wù)讀?。⊿ELECT）一個(gè)資源。資源）一個(gè)資源。資源上存在共享鎖時(shí)，任何其他事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)上存在共享鎖時(shí)，任何其他事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)讀取數(shù)據(jù)，便立即釋放資源上的共享鎖，除非將事務(wù)隔離級(jí)讀取數(shù)據(jù)，便立即釋放資源上的共享鎖，除非將事務(wù)隔離級(jí)別設(shè)置為可重復(fù)讀或更高級(jí)別，或者在事務(wù)生存周期內(nèi)用鎖別設(shè)置為可重復(fù)讀或更高級(jí)別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享鎖。定提示保留共享鎖。2更新鎖更新鎖更新鎖可以防止通常形式的死鎖

52、。一般更新模式由一個(gè)更新鎖可以防止通常形式的死鎖。一般更新模式由一個(gè)事務(wù)組成，此事務(wù)讀取記錄，獲取資源（頁(yè)或行）的共享鎖，事務(wù)組成，此事務(wù)讀取記錄，獲取資源（頁(yè)或行）的共享鎖，然后修改行，此操作要求鎖轉(zhuǎn)換為排它鎖。然后修改行，此操作要求鎖轉(zhuǎn)換為排它鎖。如果兩個(gè)事務(wù)獲得了資源上的共享模式鎖，然后試圖同如果兩個(gè)事務(wù)獲得了資源上的共享模式鎖，然后試圖同時(shí)更新數(shù)據(jù)，則一個(gè)事務(wù)嘗試將鎖轉(zhuǎn)換為排它鎖。共享模式時(shí)更新數(shù)據(jù)，則一個(gè)事務(wù)嘗試將鎖轉(zhuǎn)換為排它鎖。共享模式到排它鎖的轉(zhuǎn)換必須等待一段時(shí)間，因?yàn)橐粋€(gè)事務(wù)的排它鎖到排它鎖的轉(zhuǎn)換必須等待一段時(shí)間，因?yàn)橐粋€(gè)事務(wù)的排它鎖與其他事務(wù)的共享模式鎖不兼容，發(fā)生鎖等待。第

53、二個(gè)事務(wù)與其他事務(wù)的共享模式鎖不兼容，發(fā)生鎖等待。第二個(gè)事務(wù)試圖獲取排它鎖以進(jìn)行更新。由于兩個(gè)事務(wù)都要轉(zhuǎn)換為排它試圖獲取排它鎖以進(jìn)行更新。由于兩個(gè)事務(wù)都要轉(zhuǎn)換為排它鎖，并且每個(gè)事務(wù)都等待另一個(gè)事務(wù)釋放共享模式鎖，因此鎖，并且每個(gè)事務(wù)都等待另一個(gè)事務(wù)釋放共享模式鎖，因此發(fā)生死鎖。發(fā)生死鎖。3排它鎖排它鎖排它鎖可以防止并發(fā)事務(wù)對(duì)資源進(jìn)行訪問(wèn)。其他事務(wù)不能排它鎖可以防止并發(fā)事務(wù)對(duì)資源進(jìn)行訪問(wèn)。其他事務(wù)不能讀取或修改排它鎖鎖定的數(shù)據(jù)。讀取或修改排它鎖鎖定的數(shù)據(jù)。4意向鎖意向鎖意向鎖表示意向鎖表示SQL Server需要在層次結(jié)構(gòu)中的某些底層資源需要在層次結(jié)構(gòu)中的某些底層資源上獲取共享鎖或排它鎖。例如，

54、放置在表級(jí)的共享意向鎖表示上獲取共享鎖或排它鎖。例如，放置在表級(jí)的共享意向鎖表示事務(wù)打算在表中的頁(yè)或行上放置共享鎖。在表級(jí)設(shè)置意向鎖可事務(wù)打算在表中的頁(yè)或行上放置共享鎖。在表級(jí)設(shè)置意向鎖可防止另一個(gè)事務(wù)隨后在包含那一頁(yè)的表上獲取排它鎖。防止另一個(gè)事務(wù)隨后在包含那一頁(yè)的表上獲取排它鎖。意向鎖可以提高性能，因?yàn)橐庀蜴i可以提高性能，因?yàn)镾QL Server僅在表級(jí)檢查意向僅在表級(jí)檢查意向鎖來(lái)確定事務(wù)是否可以安全地獲取該表上的鎖。而無(wú)須檢查表鎖來(lái)確定事務(wù)是否可以安全地獲取該表上的鎖。而無(wú)須檢查表中的每行或每頁(yè)上的鎖以確定事務(wù)是否可以鎖定整個(gè)表。中的每行或每頁(yè)上的鎖以確定事務(wù)是否可以鎖定整個(gè)表。5架構(gòu)鎖

55、架構(gòu)鎖執(zhí)行表的數(shù)據(jù)定義語(yǔ)言（執(zhí)行表的數(shù)據(jù)定義語(yǔ)言（DDL）操作（如添加列或刪除表）操作（如添加列或刪除表）時(shí)使用架構(gòu)修改鎖。時(shí)使用架構(gòu)修改鎖。當(dāng)編譯查詢時(shí)，使用架構(gòu)穩(wěn)定性鎖。架構(gòu)穩(wěn)定性鎖不阻塞當(dāng)編譯查詢時(shí)，使用架構(gòu)穩(wěn)定性鎖。架構(gòu)穩(wěn)定性鎖不阻塞任何事務(wù)鎖，包括排它鎖。因此在編譯查詢時(shí)，其他事務(wù)（包任何事務(wù)鎖，包括排它鎖。因此在編譯查詢時(shí)，其他事務(wù)（包括在表上有排它鎖的事務(wù)）都能繼續(xù)運(yùn)行。但不能在表上執(zhí)行括在表上有排它鎖的事務(wù)）都能繼續(xù)運(yùn)行。但不能在表上執(zhí)行DDL操作。操作。6大容量更新鎖大容量更新鎖當(dāng)將數(shù)據(jù)大容量復(fù)制到表，且指定了當(dāng)將數(shù)據(jù)大容量復(fù)制到表，且指定了TABLOCK提示或者提示或者使用

56、使用sp_tableoption設(shè)置了設(shè)置了table lock on bulk表選項(xiàng)時(shí)，將使用表選項(xiàng)時(shí)，將使用大容量更新鎖。大容量更新鎖。大容量更新鎖允許進(jìn)程將數(shù)據(jù)并發(fā)地大容量復(fù)制到同一表，大容量更新鎖允許進(jìn)程將數(shù)據(jù)并發(fā)地大容量復(fù)制到同一表，同時(shí)防止其他不進(jìn)行大容量復(fù)制數(shù)據(jù)的進(jìn)程訪問(wèn)該表。同時(shí)防止其他不進(jìn)行大容量復(fù)制數(shù)據(jù)的進(jìn)程訪問(wèn)該表。7鎖兼容性鎖兼容性只有兼容的鎖類型才可以放置在已鎖定的資源上。例如，只有兼容的鎖類型才可以放置在已鎖定的資源上。例如，當(dāng)控制排它鎖時(shí)，在第一個(gè)事務(wù)結(jié)束并釋放排它鎖之前，其他當(dāng)控制排它鎖時(shí)，在第一個(gè)事務(wù)結(jié)束并釋放排它鎖之前，其他事務(wù)不能在該資源上獲取任何類型的（

57、共享、更新或排它）鎖。事務(wù)不能在該資源上獲取任何類型的（共享、更新或排它）鎖。 資源鎖模式有一個(gè)兼容性矩陣，顯示了與在同一資源上可資源鎖模式有一個(gè)兼容性矩陣，顯示了與在同一資源上可獲取的其他鎖相兼容的鎖，如下表所示。獲取的其他鎖相兼容的鎖，如下表所示。請(qǐng)求模式請(qǐng)求模式現(xiàn)有的授權(quán)模式現(xiàn)有的授權(quán)模式ISSUIXSIXX意向共享（意向共享（IS）是是是是是是是是是是否否共享（共享（S）是是是是是是否否否否否否更新（更新（U）是是是是否否否否否否否否意向排它（意向排它（IX）是是否否否否是是否否否否與意向排它共享與意向排它共享（SIX）是是否否否否否否否否否否排它（排它（X）否否否否否否否否否否否否1

58、0.3.2 自定義鎖自定義鎖1死鎖死鎖封鎖機(jī)制的引入能解決并發(fā)用戶訪問(wèn)數(shù)據(jù)的不一致性封鎖機(jī)制的引入能解決并發(fā)用戶訪問(wèn)數(shù)據(jù)的不一致性問(wèn)題，但是，卻會(huì)引起死鎖。引起死鎖的主要原因是兩個(gè)問(wèn)題，但是，卻會(huì)引起死鎖。引起死鎖的主要原因是兩個(gè)進(jìn)程已經(jīng)各自鎖定一個(gè)頁(yè)，但是又要訪問(wèn)被對(duì)方鎖定的頁(yè)。進(jìn)程已經(jīng)各自鎖定一個(gè)頁(yè)，但是又要訪問(wèn)被對(duì)方鎖定的頁(yè)。因而會(huì)形成等待圈，導(dǎo)致死鎖。因而會(huì)形成等待圈，導(dǎo)致死鎖。圖圖10.22 死鎖死鎖2自定義鎖超時(shí)自定義鎖超時(shí)在默認(rèn)情況下，沒(méi)有強(qiáng)制的超時(shí)期限，并且除了試圖訪問(wèn)數(shù)在默認(rèn)情況下，沒(méi)有強(qiáng)制的超時(shí)期限，并且除了試圖訪問(wèn)數(shù)據(jù)外（有可能被無(wú)限期阻塞），沒(méi)有其他方法可以測(cè)試某個(gè)資源

59、據(jù)外（有可能被無(wú)限期阻塞），沒(méi)有其他方法可以測(cè)試某個(gè)資源在鎖定之前是否已經(jīng)、被鎖定。在鎖定之前是否已經(jīng)、被鎖定。LOCK_TIMEOUT語(yǔ)句設(shè)置允許應(yīng)用程序設(shè)置語(yǔ)句等待阻語(yǔ)句設(shè)置允許應(yīng)用程序設(shè)置語(yǔ)句等待阻塞資源的最長(zhǎng)時(shí)間。當(dāng)語(yǔ)句等待的時(shí)間大于塞資源的最長(zhǎng)時(shí)間。當(dāng)語(yǔ)句等待的時(shí)間大于LOCK_TIMEOUT設(shè)置時(shí)，系統(tǒng)將自動(dòng)取消阻塞的語(yǔ)句，并給應(yīng)用程序返回設(shè)置時(shí)，系統(tǒng)將自動(dòng)取消阻塞的語(yǔ)句，并給應(yīng)用程序返回“已超已超過(guò)了鎖請(qǐng)求超時(shí)時(shí)段過(guò)了鎖請(qǐng)求超時(shí)時(shí)段”的的1222號(hào)錯(cuò)誤信息。號(hào)錯(cuò)誤信息。3自定義事務(wù)隔離級(jí)別自定義事務(wù)隔離級(jí)別在數(shù)據(jù)庫(kù)操作過(guò)程中很可能出現(xiàn)以下幾種不確定情況：在數(shù)據(jù)庫(kù)操作過(guò)程中很可能出

60、現(xiàn)以下幾種不確定情況：l 更新丟失更新丟失：兩個(gè)事務(wù)都同時(shí)更新一行數(shù)據(jù)，但是第二：兩個(gè)事務(wù)都同時(shí)更新一行數(shù)據(jù)，但是第二個(gè)事務(wù)卻中途失敗退出，導(dǎo)致對(duì)數(shù)據(jù)的兩個(gè)修改都失個(gè)事務(wù)卻中途失敗退出，導(dǎo)致對(duì)數(shù)據(jù)的兩個(gè)修改都失效了。這是因?yàn)橄到y(tǒng)沒(méi)有執(zhí)行任何的鎖操作，因此并效了。這是因?yàn)橄到y(tǒng)沒(méi)有執(zhí)行任何的鎖操作，因此并發(fā)事務(wù)并沒(méi)有被隔離開(kāi)來(lái)。發(fā)事務(wù)并沒(méi)有被隔離開(kāi)來(lái)。l 臟讀臟讀：一個(gè)事務(wù)開(kāi)始讀取了某行數(shù)據(jù)，但是另外一個(gè)：一個(gè)事務(wù)開(kāi)始讀取了某行數(shù)據(jù)，但是另外一個(gè)事務(wù)已經(jīng)更新了此數(shù)據(jù)但沒(méi)有能夠及時(shí)提交。這是相事務(wù)已經(jīng)更新了此數(shù)據(jù)但沒(méi)有能夠及時(shí)提交。這是相當(dāng)危險(xiǎn)的，因?yàn)楹芸赡芩械牟僮鞫急换貪L。當(dāng)危險(xiǎn)的，因?yàn)楹芸赡芩?/p>