第6章-SQL高級應(yīng)用教學(xué)課件

第6章SQL高級應(yīng)用電子商務(wù)數(shù)據(jù)庫技術(shù)7/26/20231華北科技學(xué)院李文武6.1SELECT高級查詢6.1.1數(shù)據(jù)匯總1.聚合函數(shù)：對一組值操作，返回單一的匯總值。聚合函數(shù)在如下情況下，允許作為表達(dá)式使用：(1)SELECT語句的選擇列表(子查詢或外部查詢)。(2)COMPUTE或COMPUTEBY子句。(3)HAVING子句。7/26/20232華北科技學(xué)院李文武COMPUTE和COMPUTEBYCOMPUTE子句用于分類匯總，格式為：[COMPUTE{聚合函數(shù)名(expression)}[,...n][BYexpression[,...n]]]其中expression是列名；COMPUTE將產(chǎn)生額外的匯總行。COMPUTEBY子句可以用同一SELECT語句既查看明細(xì)行，又查看匯總行。可計算分組的匯總值，也可計算整個結(jié)果集的匯總值。7/26/20233華北科技學(xué)院李文武COMPUTE生成的結(jié)果集當(dāng)COMPUTE帶有可選的BY子句時，符合SELECT條件的每個組都有兩個結(jié)果集：每個組的第一個結(jié)果集是明細(xì)行集，其中包含該組的選擇列表信息。每個組的第二個結(jié)果集有一行，其中包含該組的COMPUTE子句中所指定的聚合函數(shù)的小計。當(dāng)COMPUTE不帶可選的BY子句時，SELECT語句有兩個結(jié)果集：每個組的第一個結(jié)果集是包含選擇列表信息的所有明細(xì)行。第二個結(jié)果集有一行，其中包含COMPUTE子句中所指定的聚合函數(shù)的合計。7/26/20234華北科技學(xué)院李文武比較COMPUTE和GROUPBYGROUPBY生成單個結(jié)果集。每個組都有一個只包含分組依據(jù)列和顯示該組子聚合的聚合函數(shù)的行。選擇列表只能包含分組依據(jù)列和聚合函數(shù)。COMPUTE生成多個結(jié)果集。一類結(jié)果集包含每個組的明細(xì)行，其中包含選擇列表中的表達(dá)式。另一類結(jié)果集包含組的子聚合，或SELECT語句的總聚合。選擇列表可包含除分組依據(jù)列或聚合函數(shù)之外的其它表達(dá)式。聚合函數(shù)在COMPUTE子句中指定，而不是在選擇列表中。7/26/20235華北科技學(xué)院李文武2.GROUPBY子句指定用來放置輸出行的組，并且如果SELECT子句<selectlist>中包含聚合函數(shù)，則計算每組的匯總值。指定GROUPBY時，選擇列表中任一非聚合表達(dá)式內(nèi)的所有列都應(yīng)包含在GROUPBY列表中，或者GROUPBY表達(dá)式必須與選擇列表表達(dá)式完全匹配。語法[GROUPBY[ALL]group_by_expression[,...n]

[WITH{CUBE|ROLLUP}]

]7/26/20236華北科技學(xué)院李文武ALL：包含所有組和結(jié)果集，甚至包含那些任何行都不滿足WHERE子句指定的搜索條件的組和結(jié)果集。如果指定了ALL，將對組中不滿足搜索條件的匯總列返回空值。不能用CUBE或ROLLUP運算符指定ALL。如果訪問遠(yuǎn)程表的查詢中有WHERE子句，則不支持GROUPBYALL操作。group_by_expression：是對其執(zhí)行分組的表達(dá)式。group_by_expression也稱為分組列。group_byexpression可以是列或引用列的非聚合表達(dá)式。在選擇列表內(nèi)定義的列的別名不能用于指定分組列。7/26/20237華北科技學(xué)院李文武CUBE：指定在結(jié)果集內(nèi)不僅包含由GROUPBY提供的正常行，還包含匯總行。在結(jié)果集內(nèi)返回每個可能的組和子組組合的GROUPBY匯總行。GROUPBY匯總行在結(jié)果中顯示為NULL，但可用來表示所有值。使用GROUPING函數(shù)確定結(jié)果集內(nèi)的空值是否是GROUPBY匯總值。ROLLUP：指定在結(jié)果集內(nèi)不僅包含由GROUPBY提供的正常行，還包含匯總行。按層次結(jié)構(gòu)順序，從組內(nèi)的最低級別到最高級別匯總組。組的層次結(jié)構(gòu)取決于指定分組列時所使用的順序。更改分組列的順序會影響在結(jié)果集內(nèi)生成的行數(shù)。7/26/20238華北科技學(xué)院李文武6.1.2聯(lián)接查詢通過聯(lián)接根據(jù)各表間的邏輯關(guān)系從兩個或多個表中檢索數(shù)據(jù)。定義方法：可以在FROM或WHERE子句中使用比較運算符給出聯(lián)接條件對表進(jìn)行聯(lián)接。指定各表中用于聯(lián)接的列。比較符可以是<、<=、=、>、>=、!=、<>、!<和!>聯(lián)接條件與WHERE和HAVING子句的搜索條件配合完成查詢。查詢所選的行首先通過FROM子句聯(lián)接條件進(jìn)行篩選，其次由WHERE子句搜索條件篩選，然后由HAVING子句搜索條件篩選。7/26/20239華北科技學(xué)院李文武1.內(nèi)聯(lián)接僅顯示兩個聯(lián)接表中的匹配行的聯(lián)接。是查詢分析器中的默認(rèn)聯(lián)接類型。當(dāng)創(chuàng)建內(nèi)聯(lián)接時，包含NULL的列不與任何值匹配，因此不包括在結(jié)果集內(nèi)?？罩挡慌c其它的空值匹配。關(guān)鍵詞為：INNERJOIN7/26/202310華北科技學(xué)院李文武2.外聯(lián)接包括在聯(lián)接表中沒有相關(guān)行的行的聯(lián)接。左向外聯(lián)接：包括第一個命名表（“左”表，出現(xiàn)在JOIN子句的最左邊）中的所有行。不包括右表中的不匹配行。LEFTJOIN右向外聯(lián)接：包括第二個命名表（“右”表，出現(xiàn)在JOIN子句的最右邊）中的所有行。不包括左表中的不匹配行。RIGHTJOIN完整外部聯(lián)接：包括所有聯(lián)接表中的所有行，不論它們是否匹配。FULLJOIN7/26/202311華北科技學(xué)院李文武3.交叉聯(lián)接在這類聯(lián)接的結(jié)果集內(nèi)，兩個表中每兩個可能成對的行占一行。交叉聯(lián)接不使用WHERE子句。7/26/202312華北科技學(xué)院李文武6.1.3子查詢子查詢是一個SELECT查詢，它返回單個值且嵌套在SELECT、INSERT、UPDATE、DELETE語句或其它子查詢中。任何允許使用表達(dá)式的地方都可以使用子查詢。子查詢也稱為內(nèi)部查詢或內(nèi)部選擇，而包含子查詢的語句也稱為外部查詢或外部選擇。7/26/202313華北科技學(xué)院李文武嵌套在外部SELECT語句中的子查詢包括以下組件：包含標(biāo)準(zhǔn)選擇列表組件的標(biāo)準(zhǔn)SELECT查詢。包含一個或多個表或者視圖名的標(biāo)準(zhǔn)FROM子句?？蛇x的WHERE子句。可選的GROUPBY子句。可選的HAVING子句。子查詢的SELECT查詢總是使用圓括號括起來。且不能包括COMPUTE或FORBROWSE子句，如果同時指定TOP子句，則可能只包括ORDERBY子句。7/26/202314華北科技學(xué)院李文武子查詢可以嵌套在外部SELECT、INSERT、UPDATE或DELETE語句的WHERE或HAVING子句內(nèi)，或者其它子查詢中。盡管根據(jù)可用內(nèi)存和查詢中其它表達(dá)式的復(fù)雜程度不同，嵌套限制也有所不同，但嵌套到32層是可能的。如果某個表只出現(xiàn)在子查詢中而不出現(xiàn)在外部查詢中，那么該表中的列就無法包含在輸出中（外部查詢的選擇列表）。7/26/202315華北科技學(xué)院李文武有三種基本的子查詢。它們是：在通過IN引入的列表或者由ANY或ALL修改的比較運算符的列表上進(jìn)行操作。通過無修改的比較運算符引入，且必須返回單個值。通過EXISTS引入的存在測試。通常采用以下格式中的一種：WHERE表達(dá)式[NOT]IN(子查詢)WHERE表達(dá)式比較運算符[ANY|ALL](子查詢)WHERE[NOT]EXISTS(子查詢)7/26/202316華北科技學(xué)院李文武1.子查詢規(guī)則子查詢受以下條件的限制：通過比較運算符引入的子查詢的選擇列表只能包括一個表達(dá)式或列名稱（分別對SELECT*或列表進(jìn)行EXISTS和IN操作除外）。如果外部查詢的WHERE子句包括某個列名，則該子句必須與子查詢選擇列表中的該列在聯(lián)接上兼容。子查詢的選擇列表中不允許出現(xiàn)ntext、text和image數(shù)據(jù)類型。由于必須返回單個值，由無修改的比較運算符（指其后未接關(guān)鍵字ANY或ALL）引入的子查詢不能包括GROUPBY和HAVING子句。包括GROUPBY的子查詢不能使用DISTINCT關(guān)鍵字。不能指定COMPUTE和INTO子句。只有同時指定了TOP，才可以指定ORDERBY。由子查詢創(chuàng)建的視圖不能更新。通過EXISTS引入的子查詢的選擇列表由星號(*)組成，而不使用單個列名。通過EXISTS引入的子查詢進(jìn)行了存在測試，返回TRUE或FALSE而非數(shù)據(jù)，所以這些子查詢的規(guī)則與標(biāo)準(zhǔn)選擇列表的規(guī)則完全相同。7/26/202317華北科技學(xué)院李文武2.子查詢類型可以在許多地方指定子查詢：使用別名時使用IN或NOTIN時在UPDATE、DELETE和INSERT語句中使用比較運算符時使用ANY、SOME或ALL時使用EXISTS或NOTEXISTS時在有表達(dá)式的地方7/26/202318華北科技學(xué)院李文武使用IN或NOTIN的子查詢通過IN（或NOTIN）引入的子查詢結(jié)果是一列零值或更多值。子查詢返回結(jié)果之后，外部查詢將利用這些結(jié)果。使用聯(lián)接而不使用子查詢處理該問題及類似問題的一個不同之處在于，聯(lián)接使您可以在結(jié)果中顯示多個表中的列。聯(lián)接總是可以表示為子查詢。子查詢經(jīng)常（但不總是）可以表示為聯(lián)接。這是因為聯(lián)接是對稱的：可以以任意順序?qū)⒈鞟聯(lián)接到表B，而且會得到相同的答案。而對子查詢來說，情況則并非如此。7/26/202319華北科技學(xué)院李文武UPDATE、DELETE和INSERT語句中的子查詢子查詢可以嵌套在UPDATE、DELETE和INSERT語句以及SELECT語句中。7/26/202320華北科技學(xué)院李文武使用比較運算符的子查詢子查詢可由一個比較運算符（=、<>、>、>=、<、!>,!<或<=）引入。與使用IN引入的子查詢一樣，由未修改的比較運算符（后面不跟ANY或ALL的比較運算符）引入的子查詢必須返回單個值而不是值列表。如果這樣的子查詢返回多個值，將顯示錯誤信息。要使用由無修改的比較運算符引入的子查詢，必須對數(shù)據(jù)和問題的本質(zhì)非常熟悉，以了解該子查詢實際是否只返回一個值。7/26/202321華北科技學(xué)院李文武使用EXISTS的子查詢使用EXISTS關(guān)鍵字引入一個子查詢時，就相當(dāng)于進(jìn)行一次存在測試。外部查詢的WHERE子句測試子查詢返回的行是否存在。子查詢實際上不產(chǎn)生任何數(shù)據(jù)；它只返回TRUE或FALSE值。使用EXISTS引入的子查詢語法如下：WHERE[NOT]EXISTS(子查詢)7/26/202322華北科技學(xué)院李文武3.多層嵌套子查詢自身可以包括一個或多個子查詢。一個語句中可以嵌套任意數(shù)量的子查詢。7/26/202323華北科技學(xué)院李文武6.1.4在查詢基礎(chǔ)上創(chuàng)建新表創(chuàng)建新表并將結(jié)果行從查詢插入新表中。用戶若要執(zhí)行帶INTO子句的SELECT語句，必須在目的數(shù)據(jù)庫內(nèi)具有CREATETABLE權(quán)限。SELECT...INTO不能與COMPUTE子句一起使用。7/26/202324華北科技學(xué)院李文武6.2管理ntext、text、image數(shù)據(jù)ntext、text和image數(shù)據(jù)類型在單個值中可以包含非常大的數(shù)據(jù)量（最大可達(dá)2GB）。單個數(shù)據(jù)值通常比應(yīng)用程序在一個步驟中能夠檢索的大；某些值可能還會大于客戶端的可用虛擬內(nèi)存。因此，在檢索這些值時，通常需要一些特殊的步驟。如果ntext、text和image數(shù)據(jù)值不超過Unicode串、字符串或二進(jìn)制串的長度（分別為4,000個字符、8,000個字符和8,000個字節(jié)），就可以在SELECT、UPDATE和INSERT語句中引用它們，其引用方式與較小的數(shù)據(jù)類型相同。7/26/202325華北科技學(xué)院李文武包含短值的ntext列可在SELECT語句的選擇列表中引用，與nvarchar列的引用方式相同。引用時必須遵守一些限制，例如不能在WHERE子句中直接引用ntext、text或image列。這些列可以作為返回其它數(shù)據(jù)類型（例如ISNULL、SUBSTRING或PATINDEX）的某個函數(shù)的參數(shù)包含在WHERE子句中，也可以包含在ISNULL、ISNOTNULL或LIKE表達(dá)式中。7/26/202326華北科技學(xué)院李文武6.3事務(wù)處理事務(wù)是作為單個邏輯工作單元執(zhí)行的一系列操作。一個邏輯工作單元必須有四個屬性，稱為ACID（原子性、一致性、隔離性和持久性）屬性，只有這樣才能成為一個事務(wù)：原子性：事務(wù)必須是原子工作單元；對于其數(shù)據(jù)修改，要么全都執(zhí)行，要么全都不執(zhí)行。一致性：事務(wù)在完成時，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。在相關(guān)數(shù)據(jù)庫中，所有規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持所有數(shù)據(jù)的完整性。事務(wù)結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如B樹索引或雙向鏈表）都必須是正確的。7/26/202327華北科技學(xué)院李文武隔離性：由并發(fā)事務(wù)所作的修改必須與任何其它并發(fā)事務(wù)所作的修改隔離。事務(wù)查看數(shù)據(jù)時數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，要么是另一事務(wù)修改它之后的狀態(tài)，事務(wù)不會查看中間狀態(tài)的數(shù)據(jù)。這稱為可串行性，因為它能夠重新裝載起始數(shù)據(jù)，并且重播一系列事務(wù)，以使數(shù)據(jù)結(jié)束時的狀態(tài)與原始事務(wù)執(zhí)行的狀態(tài)相同。持久性：事務(wù)完成之后，它對于系統(tǒng)的影響是永久性的。該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。7/26/202328華北科技學(xué)院李文武6.3.1事務(wù)分類SQLServer以三種事務(wù)模式運行：自動提交事務(wù)：每條單獨的語句都是一個事務(wù)。是SQLServer的默認(rèn)事務(wù)管理模式。每個T-SQL語句在完成時，都被提交或回滾。如果一個語句成功地完成，則提交該語句；如果遇到錯誤，則回滾該語句。自動提交模式也是ADO、OLEDB、ODBC和DB-Library的默認(rèn)模式。7/26/202329華北科技學(xué)院李文武顯式事務(wù)：也稱為用戶定義或用戶指定的事務(wù)。每個事務(wù)均以BEGINTRANSACTION語句顯式開始，以COMMIT或ROLLBACK語句顯式結(jié)束。隱性事務(wù)：在前一個事務(wù)完成時新事務(wù)隱式啟動，但每個事務(wù)仍以COMMIT或ROLLBACK語句顯式完成。7/26/202330華北科技學(xué)院李文武6.3.2顯式事務(wù)1.啟動事務(wù)語法BEGINTRAN[SACTION][transaction_name|tran_name_variable

[WITHMARK['description']]]參數(shù)transaction_name：是給事務(wù)分配的名稱。必須遵循標(biāo)識符規(guī)則，但是不允許標(biāo)識符多于32個字符。僅在嵌套的BEGIN...COMMIT或BEGIN...ROLLBACK語句的最外語句對上使用事務(wù)名。tran_name_variable：是用戶定義的、含有有效事務(wù)名稱的變量的名稱。必須用char、varchar、nchar或nvarchar數(shù)據(jù)類型聲明。WITHMARK[‘description’]：指定在日志中標(biāo)記事務(wù)。Description是描述該標(biāo)記的字符串。如果使用了WITHMARK，則必須指定事務(wù)名。WITHMARK允許將事務(wù)日志還原到命名標(biāo)記。7/26/202331華北科技學(xué)院李文武2.結(jié)束事務(wù)語法COMMIT[TRAN[SACTION][transaction_name|tran_name_variable]]參數(shù)transaction_name：SQLServer忽略該參數(shù)。transaction_name指定由前面的BEGINTRANSACTION指派的事務(wù)名稱。通過向程序員指明COMMITTRANSACTION與哪些嵌套的BEGINTRANSACTION相關(guān)聯(lián)，transaction_name可作為幫助閱讀的一種方法。tran_name_variable：是用戶定義的、含有有效事務(wù)名稱的變量的名稱。必須用char、varchar、nchar或nvarchar數(shù)據(jù)類型聲明該變量。7/26/202332華北科技學(xué)院李文武結(jié)束一個成功的隱性事務(wù)或用戶定義事務(wù)。COMMITWORK標(biāo)志事務(wù)的結(jié)束。語法：COMMIT[WORK]與COMMITTRANSACTION相同，但COMMITTRANSACTION接受用戶定義的事務(wù)名稱。7/26/202333華北科技學(xué)院李文武3.回滾事務(wù)語法ROLLBACK[TRAN[SACTION]

[transaction_name|tran_name_variable

|savepoint_name|savepoint_variable]]參數(shù)transaction_name：是給BEGINTRANSACTION上的事務(wù)指派的名稱。嵌套事務(wù)時，transaction_name必須是來自最遠(yuǎn)的BEGINTRANSACTION語句的名稱。tran_name_variable：是用戶定義的、含有有效事務(wù)名稱的變量的名稱。savepoint_name：是來自SAVETRANSACTION語句的savepoint_name。savepoint_name必須符合標(biāo)識符規(guī)則。當(dāng)條件回滾只影響事務(wù)的一部分時使用savepoint_name。savepoint_variable：是用戶定義的、含有有效保存點名稱的變量的名稱。必須用char、varchar、nchar或nvarchar數(shù)據(jù)類型聲明該變量。7/26/202334華北科技學(xué)院李文武將顯式事務(wù)或隱性事務(wù)回滾到事務(wù)的起點或事務(wù)內(nèi)的某個保存點。ROLLBACKTRANSACTION清除自事務(wù)的起點或到某個保存點所做的所有數(shù)據(jù)修改。ROLLBACK還釋放由事務(wù)控制的資源。ROLLBACKWORK將用戶定義的事務(wù)回滾到事務(wù)的起點。語法：ROLLBACK[WORK]

7/26/202335華北科技學(xué)院李文武4.在事務(wù)中設(shè)置保存點語法SAVETRAN[SACTION]{savepoint_name|savepoint_variable}參數(shù)savepoint_name：是指派給保存點的名稱。保存點名稱必須符合標(biāo)識符規(guī)則，但只使用前32個字符。savepoint_variable：是用戶定義的、含有有效保存點名稱的變量的名稱。必須用char、varchar、nchar或nvarchar數(shù)據(jù)類型聲明該變量。7/26/202336華北科技學(xué)院李文武用戶可以在事務(wù)內(nèi)設(shè)置保存點或標(biāo)記。保存點定義的是：如果有條件地取消事務(wù)的一部分，事務(wù)可以返回的位置。7/26/202337華北科技學(xué)院李文武5.標(biāo)記事務(wù)WITHMARK['description']指定在日志中標(biāo)記事務(wù)。將數(shù)據(jù)庫還原到早期狀態(tài)時，可使用標(biāo)記事務(wù)替代日期和時間。Description是描述該標(biāo)記的字符串。如果使用了WITHMARK，則必須指定事務(wù)名。WITHMARK允許將事務(wù)日志還原到命名標(biāo)記。只有當(dāng)數(shù)據(jù)庫由標(biāo)記事務(wù)更新時，才在事務(wù)日志中放置標(biāo)記。不修改數(shù)據(jù)的事務(wù)不被標(biāo)記。7/26/202338華北科技學(xué)院李文武6.不能用于事務(wù)的操作一些不能撤銷的操作，其對數(shù)據(jù)庫的操作是不能恢復(fù)的。如創(chuàng)建、修改、刪除數(shù)據(jù)庫等。P1687/26/202339華北科技學(xué)院李文武6.3.3自動提交事務(wù)編譯錯誤將阻止SQLServer建立執(zhí)行計劃，這樣批處理中的任何語句都不會執(zhí)行。運行錯誤時，該語句之前的結(jié)果被保留下來。SQLServer使用延遲的名稱解析，其中對象名直到執(zhí)行時才被解析。所以錯誤的對象名不會在編譯時指出。7/26/202340華北科技學(xué)院李文武6.3.4隱式事務(wù)在為連接將隱性事務(wù)模式設(shè)置為打開之后，當(dāng)SQLServer首次執(zhí)行下列任何語句時，都會自動啟動一個事務(wù)：在COMMIT或ROLLBACK語句之前，該事務(wù)將一直保持有效。在第一個事務(wù)被提交或回滾之后，下次當(dāng)連接執(zhí)行這些語句中的任何語句時，SQLServer都將自動啟動一個新事務(wù)。SQLServer將不斷地生成一個隱性事務(wù)鏈，直到隱性事務(wù)模式關(guān)閉為止。ALTERTABLEINSERTCREATEOPENDELETEREVOKEDROPSELECTFETCHTRUNCATETABLEGRANTUPDATE7/26/202341華北科技學(xué)院李文武SETIMPLICIT_TRANSACTIONSON語句啟動隱性事務(wù)模式。

SETIMPLICIT_TRANSACTIONSOFF語句關(guān)閉隱性事務(wù)模式。使用COMMITTRANSACTION、COMMITWORK、ROLLBACKTRANSACTION或ROLLBACKWORK語句結(jié)束每個事務(wù)。7/26/202342華北科技學(xué)院李文武如果連接已經(jīng)在打開的事務(wù)中，則上述語句不啟動新事務(wù)。對于因為該設(shè)置為ON而自動打開的事務(wù)，用戶必須在該事務(wù)結(jié)束時將其顯式提交或回滾。否則當(dāng)用戶斷開連接時，事務(wù)及其所包含的所有數(shù)據(jù)更改將回滾。在事務(wù)提交后，執(zhí)行上述任一語句即可啟動新事務(wù)。隱性事務(wù)模式將保持有效，直到連接執(zhí)行SETIMPLICIT_TRANSACTIONSOFF語句使連接返回到自動提交模式。在自動提交模式下，如果各個語句成功完成則提交。7/26/202343華北科技學(xué)院李文武6.4數(shù)據(jù)的鎖定通過鎖定確保事務(wù)完整性和數(shù)據(jù)庫的一致性。如：讀取正由其他用戶更改的數(shù)據(jù)多個用戶同時更改同一數(shù)據(jù)7/26/202344華北科技學(xué)院李文武6.4.1并發(fā)問題如果沒有鎖定且多個用戶同時訪問一個數(shù)據(jù)庫，則當(dāng)他們的事務(wù)同時使用相同的數(shù)據(jù)時可能會發(fā)生問題。并發(fā)問題包括：丟失或覆蓋更新。未確認(rèn)的相關(guān)性（臟讀）。不一致的分析（非重復(fù)讀）。幻像讀。7/26/202345華北科技學(xué)院李文武1.丟失更新當(dāng)兩個或多個事務(wù)選擇同一行，然后基于最初選定的值更新該行時，會發(fā)生丟失更新問題。每個事務(wù)都不知道其它事務(wù)的存在。最后的更新將重寫由其它事務(wù)所做的更新，這將導(dǎo)致數(shù)據(jù)丟失。7/26/202346華北科技學(xué)院李文武2.未確認(rèn)的相關(guān)性（臟讀）當(dāng)?shù)诙€事務(wù)選擇其它事務(wù)正在更新的行時，會發(fā)生未確認(rèn)的相關(guān)性問題。第二個事務(wù)正在讀取的數(shù)據(jù)還沒有確認(rèn)并且可能由更新此行的事務(wù)所更改。7/26/202347華北科技學(xué)院李文武3.不一致的分析（非重復(fù)讀）當(dāng)?shù)诙€事務(wù)多次訪問同一行而且每次讀取不同的數(shù)據(jù)時，會發(fā)生不一致的分析問題。不一致的分析與未確認(rèn)的相關(guān)性類似，因為其它事務(wù)也是正在更改第二個事務(wù)正在讀取的數(shù)據(jù)。然而，在不一致的分析中，第二個事務(wù)讀取的數(shù)據(jù)是由已進(jìn)行了更改的事務(wù)提交的。而且，不一致的分析涉及多次（兩次或更多）讀取同一行，而且每次信息都由其它事務(wù)更改；因而該行被非重復(fù)讀取。7/26/202348華北科技學(xué)院李文武4.幻像讀當(dāng)對某行執(zhí)行插入或刪除操作，而該行屬于某個事務(wù)正在讀取的行的范圍時，會發(fā)生幻像讀問題。事務(wù)第一次讀的行范圍顯示出其中一行已不復(fù)存在于第二次讀或后續(xù)讀中，因為該行已被其它事務(wù)刪除。同樣，由于其它事務(wù)的插入操作，事務(wù)的第二次或后續(xù)讀顯示有一行已不存在于原始讀中。7/26/202349華北科技學(xué)院李文武樂觀和悲觀并發(fā)SQLServer2000提供了樂觀并發(fā)控制和悲觀并發(fā)控制。樂觀并發(fā)控制使用游標(biāo)。SQLServer默認(rèn)使用悲觀并發(fā)控制。樂觀并發(fā)：樂觀并發(fā)控制假定不太可能（但不是不可能）在多個用戶間發(fā)生資源沖突，允許不鎖定任何資源而執(zhí)行事務(wù)。只有試圖更改數(shù)據(jù)時才檢查資源以確定是否發(fā)生沖突。如果發(fā)生沖突，應(yīng)用程序必須讀取數(shù)據(jù)并再次嘗試進(jìn)行更改。悲觀并發(fā)：悲觀并發(fā)控制根據(jù)需要在事務(wù)的持續(xù)時間內(nèi)鎖定資源。除非出現(xiàn)死鎖，否則事務(wù)肯定會成功完成。7/26/202350華北科技學(xué)院李文武6.4.2事務(wù)的隔離級別當(dāng)鎖定用作并發(fā)控制機制時，它可以解決并發(fā)問題。這使所有事務(wù)得以在彼此完全隔離的環(huán)境中運行，但是任何時候都可以有多個正在運行的事務(wù)。盡管可串行性對于事務(wù)確保數(shù)據(jù)庫中的數(shù)據(jù)在所有時間內(nèi)的正確性相當(dāng)重要，然而許多事務(wù)并不總是要求完全的隔離。事務(wù)準(zhǔn)備接受不一致數(shù)據(jù)的級別稱為隔離級別。隔離級別是一個事務(wù)必須與其它事務(wù)進(jìn)行隔離的程度。較低的隔離級別可以增加并發(fā)，但代價是降低數(shù)據(jù)的正確性。相反，較高的隔離級別可以確保數(shù)據(jù)的正確性，但可能對并發(fā)產(chǎn)生負(fù)面影響。應(yīng)用程序要求的隔離級別確定了SQLServer使用的鎖定行為。7/26/202351華北科技學(xué)院李文武SQLServer支持所有這些隔離級別：未提交讀（事務(wù)隔離的最低級別，僅可保證不讀取物理損壞的數(shù)據(jù)）。提交讀（SQLServer默認(rèn)級別）。不允許臟讀。可重復(fù)讀。允許幻像讀?？纱凶x（事務(wù)隔離的最高級別，事務(wù)之間完全隔離）。事務(wù)必須運行于可重復(fù)讀或更高的隔離級別以防止丟失更新。當(dāng)兩個事務(wù)檢索相同的行，然后基于原檢索的值對行進(jìn)行更新時，會發(fā)生丟失更新。如果兩個事務(wù)使用一個UPDATE語句更新行，并且不基于以前檢索的值進(jìn)行更新，則在默認(rèn)的提交讀隔離級別不會發(fā)生丟失更新。7/26/202352華北科技學(xué)院李文武6.4.3SQLServer中的鎖定SQLServer可以鎖定以下資源（按粒度增加的順序列出）。資源描述RID行標(biāo)識符。用于單獨鎖定表中的一行。鍵(KEY)索引中的行鎖。用于保護(hù)可串行事務(wù)中的鍵范圍。頁(PAG)8千字節(jié)(KB)的數(shù)據(jù)頁或索引頁。擴展盤區(qū)(EXT)相鄰的八個數(shù)據(jù)頁或索引頁構(gòu)成的一組。表(TAB)包括所有數(shù)據(jù)和索引在內(nèi)的整個表。DB數(shù)據(jù)庫。7/26/202353華北科技學(xué)院李文武SQLServer使用不同的鎖模式鎖定資源，這些鎖模式確定了并發(fā)事務(wù)訪問資源的方式。鎖模式描述共享(S)用于不更改或不更新數(shù)據(jù)的操作（只讀操作），如SELECT語句。更新(U)用于可更新的資源中。防止當(dāng)多個會話在讀取、鎖定以及隨后可能進(jìn)行的資源更新時發(fā)生常見形式的死鎖。排它(X)用于數(shù)據(jù)修改操作，例如INSERT、UPDATE或DELETE。確保不會同時對同一資源進(jìn)行多重更新。意向用于建立鎖的層次結(jié)構(gòu)。意向鎖的類型為：意向共享(IS)、意向排它(IX)以及與意向排它共享(SIX)。架構(gòu)在執(zhí)行依賴于表架構(gòu)的操作時使用。架構(gòu)鎖的類型為：架構(gòu)修改(Sch-M)和架構(gòu)穩(wěn)定性(Sch-S)。大容量更新(BU)向表中大容量復(fù)制數(shù)據(jù)并指定了TABLOCK提示時使用。7/26/202354華北科技學(xué)院李文武1.共享鎖共享(S)鎖允許并發(fā)事務(wù)讀取(SELECT)一個資源。資源上存在共享(S)鎖時，任何其它事務(wù)都不能修改數(shù)據(jù)。一旦已經(jīng)讀取數(shù)據(jù)，便立即釋放資源上的共享(S)鎖，除非將事務(wù)隔離級別設(shè)置為可重復(fù)讀或更高級別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享(S)鎖。7/26/202355華北科技學(xué)院李文武2.更新鎖更新(U)鎖可以防止通常形式的死鎖。一般更新模式由一個事務(wù)組成，此事務(wù)讀取記錄，獲取資源（頁或行）的共享(S)鎖，然后修改行，此操作要求鎖轉(zhuǎn)換為排它(X)鎖。如果兩個事務(wù)獲得了資源上的共享模式鎖，然后試圖同時更新數(shù)據(jù)，則一個事務(wù)嘗試將鎖轉(zhuǎn)換為排它(X)鎖。共享模式到排它鎖的轉(zhuǎn)換必須等待一段時間，因為一個事務(wù)的排它鎖與其它事務(wù)的共享模式鎖不兼容；發(fā)生鎖等待。第二個事務(wù)試圖獲取排它(X)鎖以進(jìn)行更新。由于兩個事務(wù)都要轉(zhuǎn)換為排它(X)鎖，并且每個事務(wù)都等待另一個事務(wù)釋放共享模式鎖，因此發(fā)生死鎖。若要避免這種潛在的死鎖問題，使用更新(U)鎖。一次只有一個事務(wù)可以獲得資源的更新(U)鎖。如果事務(wù)修改資源，則更新(U)鎖轉(zhuǎn)換為排它(X)鎖。否則，鎖轉(zhuǎn)換為共享鎖。7/26/202356華北科技學(xué)院李文武3.排它鎖排它(X)鎖可以防止并發(fā)事務(wù)對資源進(jìn)行訪問。其它事務(wù)不能讀取或修改排它(X)鎖鎖定的數(shù)據(jù)。7/26/202357華北科技學(xué)院李文武4.意向鎖意向鎖表示SQLServer需要在層次結(jié)構(gòu)中的某些底層資源上獲取共享(S)鎖或排它(X)鎖。例如，放置在表級的共享意向鎖表示事務(wù)打算在表中的頁或行上放置共享(S)鎖。在表級設(shè)置意向鎖可防止另一個事務(wù)隨后在包含那一頁的表上獲取排它(X)鎖。意向鎖可以提高性能，因為SQLServer僅在表級檢查意向鎖來確定事務(wù)是否可以安全地獲取該表上的鎖。而無須檢查表中的每行或每頁上的鎖以確定事務(wù)是否可以鎖定整個表。7/26/202358華北科技學(xué)院李文武意向鎖包括意向共享(IS)、意向排它(IX)以及與意向排它共享(SIX)。

鎖模式描述意向共享(IS)通過在各資源上放置S鎖，表明事務(wù)的意向是讀取層次結(jié)構(gòu)中的部分（而不是全部）底層資源。意向排它(IX)通過在各資源上放置X鎖，表明事務(wù)的意向是修改層次結(jié)構(gòu)中的部分（而不是全部）底層資源。IX是IS的超集。與意向排它共享(SIX)通過在各資源上放置IX鎖，表明事務(wù)的意向是讀取層次結(jié)構(gòu)中的全部底層資源并修改部分（而不是全部）底層資源。允許頂層資源上的并發(fā)IS鎖。例如，表的SIX鎖在表上放置一個SIX鎖（允許并發(fā)IS鎖），在當(dāng)前所修改頁上放置IX鎖（在已修改行上放置X鎖）。雖然每個資源在一段時間內(nèi)只能有一個SIX鎖，以防止其它事務(wù)對資源進(jìn)行更新，但是其它事務(wù)可以通過獲取表級的IS鎖來讀取層次結(jié)構(gòu)中的底層資源。7/26/202359華北科技學(xué)院李文武5.架構(gòu)鎖執(zhí)行表的數(shù)據(jù)定義語言(DDL)操作（例如添加列或除去表）時使用架構(gòu)修改(Sch-M)鎖。當(dāng)編譯查詢時，使用架構(gòu)穩(wěn)定性(Sch-S)鎖。架構(gòu)穩(wěn)定性(Sch-S)鎖不阻塞任何事務(wù)鎖，包括排它(X)鎖。因此在編譯查詢時，其它事務(wù)（包括在表上有排它(X)鎖的事務(wù)）都能繼續(xù)運行。但不能在表上執(zhí)行DDL操作。7/26/202360華北科技學(xué)院李文武6.大容量更新鎖當(dāng)將數(shù)據(jù)大容量復(fù)制到表，且指定了TABLOCK提示或使用sp_tableoption設(shè)置了tablelockonbulk表選項時，使用大容量更新(BU)鎖。大容量更新(BU)鎖允許進(jìn)程將數(shù)據(jù)并發(fā)地大容量復(fù)制到同一表，同時防止其它不進(jìn)行大容量復(fù)制數(shù)據(jù)的進(jìn)程訪問該表。7/26/202361華北科技學(xué)院李文武7.鎖兼容性只有兼容的鎖類型才可以放置在已鎖定的資源上。當(dāng)控制排它(X)鎖時，在第一個事務(wù)結(jié)束并釋放排它(X)鎖之前，其它事務(wù)不能在該資源上獲取任何類型的（共享、更新或排它）鎖。另一種情況下，如果共享(S)鎖已應(yīng)用到資源，其它事務(wù)還可以獲取該項目的共享鎖或更新(U)鎖，即使第一個事務(wù)尚未完成。但是，在釋放共享鎖之前，其它事務(wù)不能獲取排它鎖。7/26/202362華北科技學(xué)院李文武資源鎖模式有一個兼容性矩陣，顯示了與在同一資源上可獲取的其它鎖相兼容的鎖（按鎖強度增長順序列出）。

現(xiàn)有的授權(quán)模式請求模式ISSUIXSIXX意向共享(IS)是是是是是否共享(S)是是是否否否更新(U)是是否否否否意向排它(IX)是否否是否否與意向排它共享(SIX)是否否否否否排它(X)否否否否否否7/26/202363華北科技學(xué)院李文武意向排它(IX)鎖與IX鎖模式兼容，因為IX表示打算更新一些行而不是所有行。還允許其它事務(wù)讀取或更新部分行，只要這些行不是其它事務(wù)當(dāng)前所更新的行即可。架構(gòu)穩(wěn)定性(Sch-S)鎖與除了架構(gòu)修改(Sch-M)鎖模式之外的所有鎖模式相兼容。架構(gòu)修改(Sch-M)鎖與所有鎖模式都不兼容。大容量更新(BU)鎖只與架構(gòu)穩(wěn)定性(Sch-S)鎖及其它大容量更新(BU)鎖相兼容。7/26/202364華北科技學(xué)院李文武6.4.4自定義鎖雖然SQLServer2000自動執(zhí)行鎖定，但它仍可以通過以下方法自定義應(yīng)用程序中的鎖定：處理死鎖和設(shè)置死鎖優(yōu)先級。處理超時和設(shè)置鎖超時持續(xù)時間。設(shè)置事務(wù)隔離級別。對SELECT、INSERT、UPDATE和DELETE語句使用表級鎖定提示。配置索引的鎖定粒度。7/26/202365華北科技學(xué)院李文武1.死鎖當(dāng)某組資源的兩個或多個線程之間有循環(huán)相關(guān)性時，將發(fā)生死鎖。

死鎖經(jīng)常與正常阻塞混淆。當(dāng)一個事務(wù)鎖定了另一個事務(wù)需要的資源，第二個事務(wù)等待鎖被釋放。默認(rèn)情況下，SQLServer事務(wù)不會超時（除非設(shè)置了LOCK_TIMEOUT）。第二個事務(wù)被阻塞，而不是被死鎖。7/26/202366華北科技學(xué)院李文武SQLServer在識別死鎖后，通過自動選擇可以打破死鎖的線程（死鎖犧牲品）來結(jié)束死鎖。SQLServer回滾作為死鎖犧牲品的事務(wù)，通知線程的應(yīng)用程序（通過返回1205號錯誤信息），取消線程的當(dāng)前請求，然后允許不間斷線程的事務(wù)繼續(xù)進(jìn)行。SQLServer通常選擇運行撤消時花費最少的事務(wù)的線程作為死鎖犧牲品。7/26/202367華北科技學(xué)院李文武下列方法有助于最大限度地降低死鎖：按同一順序訪問對象。避免事務(wù)中的用戶交互。保持事務(wù)簡短并在一個批處理中。使用低隔離級別。使用綁定連接。7/26/202368華北科技學(xué)院李文武2.自定義鎖超時LOCK_TIMEOUT設(shè)置允許應(yīng)用程序設(shè)置語句等待阻塞資源的最長時間。當(dāng)語句等待的時間大于LOCK_TIMEOUT設(shè)置時，系統(tǒng)將自動取消阻塞的語句，并給應(yīng)用程序返回“已超過了鎖請求超時時段”的1222號錯誤信息。SQLServer不回滾或取消任何包含該語句的事務(wù)。因此，應(yīng)用程序必須有捕獲1222號錯誤信息的錯誤處理程序。如果應(yīng)用程序沒有捕獲錯誤，則會繼續(xù)運行，并未意識到事務(wù)中的個別語句已取消，從而當(dāng)事務(wù)中的后續(xù)語句可能依賴于那條從未執(zhí)行的語句時，導(dǎo)致應(yīng)用程序出錯。7/26/202369華北科技學(xué)院李文武3.自定義事務(wù)隔離級別語法SETTRANSACTIONISOLATIONLEVEL

{READCOMMITTED

|READUNCOMMITTED

|REPEATABLEREAD

|SERIALIZABLE

}參數(shù)READCOMMITTED：提交讀。該選項是SQLServer的默認(rèn)值。READUNCOMMITTED：未提交讀。是四個隔離級別中限制最小的。REPEATABLEREAD：可重復(fù)讀。SERIALIZABLE：可串行讀。這是四個隔離級別中限制最大的級別。一次只能設(shè)置這些選項中的一個，而且設(shè)置的選項將一直對那個連接保持有效，直到顯式更改該選項為止。這是默認(rèn)行為，除非在語句的FROM子句中在表級上指定優(yōu)化選項。7/26/202370華北科技學(xué)院李文武4.鎖定提示可以使用SELECT、INSERT、UPDATE和DELETE語句指定表級鎖定提示的范圍，以引導(dǎo)SQLServer2000使用所需的鎖類型。當(dāng)需要對對象所獲得鎖類型進(jìn)行更精細(xì)控制時，可以使用表級鎖定提示。這些鎖定提示取代了會話的當(dāng)前事務(wù)隔離級別。7/26/202371華北科技學(xué)院李文武5.自定義索引的鎖定使用sp_indexoption系統(tǒng)存儲過程設(shè)置用于索引的鎖定粒度。若要顯示給定索引的當(dāng)前鎖定選項，可用INDEXPROPERTY函數(shù)。7/26/202372華北科技學(xué)院李文武6.5使用游標(biāo)關(guān)系數(shù)據(jù)庫中的操作會對整個行集產(chǎn)生影響。由SELECT語句返回的行集包括所有滿足該語句WHERE子句中條件的行。由語句所返回的這一完整的行集被稱為結(jié)果集。應(yīng)用程序，特別是交互式聯(lián)機應(yīng)用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理。這些應(yīng)用程序需要一種機制以便每次處理一行或一部分行。游標(biāo)就是提供這種機制的結(jié)果集擴展。7/26/202373華北科技學(xué)院李文武6.5.1游標(biāo)的概念游標(biāo)結(jié)果集：SELECT語句結(jié)果集。游標(biāo)位置：指向集合中某一行的指針。游標(biāo)通過以下方式擴展結(jié)果處理：允許定位在結(jié)果集的特定行。從結(jié)果集的當(dāng)前位置檢索一行或多行。支持對結(jié)果集中當(dāng)前位置的行進(jìn)行數(shù)據(jù)修改。為由其他用戶對顯示在結(jié)果集中的數(shù)據(jù)庫數(shù)據(jù)所做的更改提供不同級別的可見性支持。提供腳本、存儲過程和觸發(fā)器中使用的訪問結(jié)果集中的數(shù)據(jù)的Transact-SQL語句。7/26/202374華北科技學(xué)院李文武6.5.2使用游標(biāo)使用Transact-SQL游標(biāo)的典型進(jìn)程為：聲明T-SQL變量包含游標(biāo)返回的數(shù)據(jù)。為每一結(jié)果集列聲明一個變量。聲明足夠大的變量以保存由列返回的值，并聲明可從列數(shù)據(jù)類型以隱性方式轉(zhuǎn)換得到的數(shù)據(jù)類型。使用DECLARECURSOR語句把T-SQL游標(biāo)與一個SELECT語句相關(guān)聯(lián)。DECLARECURSOR語句同時定義游標(biāo)的特征，比如游標(biāo)名稱以及游標(biāo)是否為只讀或只進(jìn)特性。使用OPEN語句執(zhí)行SELECT語句并生成游標(biāo)。7/26/202375華北科技學(xué)院李文武使用FETCHINTO語句提取單個行，并把每列中的數(shù)據(jù)轉(zhuǎn)移到指定的變量中。然后，其它T-SQL語句可以引用這些變量來訪問已提取的數(shù)據(jù)值。T-SQL不支持提取行塊。結(jié)束游標(biāo)時，使用CLOSE語句。關(guān)閉游標(biāo)可以釋放某些資源，比如游標(biāo)結(jié)果集和對當(dāng)前行的鎖定，但是如果重新發(fā)出一個OPEN語句，則該游標(biāo)結(jié)構(gòu)仍可用于處理。由于游標(biāo)仍然存在，此時還不能重新使用游標(biāo)的名稱。DEALLOCATE語句則完全釋放分配給游標(biāo)的資源，包括游標(biāo)名稱。在游標(biāo)被釋放后，必須使用DECLARE語句來重新生成游標(biāo)。7/26/202376華北科技學(xué)院李文武1.聲明游標(biāo)語法DECLAREcursor_name[INSENSITIVE][SCROLL]CURSOR

FORselect_statement

[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]cursor_name：是所定義的Transact-SQL服務(wù)器游標(biāo)名稱。cursor_name必須遵從標(biāo)識符規(guī)則。INSENSITIVE：定義一個游標(biāo)，以創(chuàng)建將由該游標(biāo)使用的數(shù)據(jù)的臨時復(fù)本。對游標(biāo)的所有請求都從tempdb中的該臨時表中得到應(yīng)答；因此，在對該游標(biāo)進(jìn)行提取操作時返回的數(shù)據(jù)中不反映對基表所做的修改，并且該游標(biāo)不允許修改。7/26/202377華北科技學(xué)院李文武SCROLL：指定所有的提取選項（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。select_statement：是定義游標(biāo)結(jié)果集的標(biāo)準(zhǔn)SELECT語句。在游標(biāo)聲明的select_statement內(nèi)不允許使用關(guān)鍵字COMPUTE、COMPUTEBY、FORBROWSE和INTO。如果select_statement中的子句與所請求的游標(biāo)類型的功能發(fā)生沖突，則SQLServer隱性地將游標(biāo)轉(zhuǎn)換為另一種類型。READONLY：在UPDATE或DELETE語句的WHERECURRENTOF子句中不能引用游標(biāo)。該選項替代要更新的游標(biāo)的默認(rèn)功能。UPDATE[OFcolumn_name[,...n]]：定義游標(biāo)內(nèi)可更新的列。如果指定OFcolumn_name[,...n]參數(shù)，則只允許修改所列出的列。如果在UPDATE中未指定列的列表，則可以更新所有列。7/26/202378華北科技學(xué)院李文武2.打開游標(biāo)語法OPEN{{[GLOBAL]cursor_name}|cursor_variable_name}參數(shù)GLOBAL：指定cursor_name指的是全局游標(biāo)。cursor_name：已聲明的游標(biāo)的名稱。如果全局游標(biāo)和局部游標(biāo)都使用cursor_name作為其名稱，那么如果指定了GLOBAL，cursor_name指的是全局游標(biāo)，否則cursor_name指的是局部游標(biāo)。cursor_variable_name：游標(biāo)變量的名稱，該名稱引用一個游標(biāo)。只能打開已聲明但沒打開的游標(biāo)。7/26/202379華北科技學(xué)院李文武3.從打開的游標(biāo)中提取行語法FETCH

[[NEXT|PRIOR|FIRST|LAST

|ABSOLUTE{n|

nvar}

|RELATIVE{n|

nvar}

]

FROM

]

{{[GLOBAL]cursor_name}|cursor_variable_name}

[INTO

variable_name[,...n]]7/26/202380華北科技學(xué)院李文武參數(shù)NEXT：返回緊跟當(dāng)前行之后的結(jié)果行，并且當(dāng)前行遞增為結(jié)果行。如果FETCHNEXT為對游標(biāo)的第一次提取操作，則返回結(jié)果集中的第一行。NEXT為默認(rèn)的游標(biāo)提取選項。P