《SQL Server 數(shù)據(jù)庫案例教程》課件第10章事務(wù)索引和視圖

課程內(nèi)容回顧變量的含義和作用SQLServer在T-SQL中支持下列兩種類型的變量：全局變量和局部變量局部變量的定義和賦值輸出結(jié)果的兩種方式批處理和注釋控制流語句語句塊本章目標(biāo)理解事務(wù)的概念理解事務(wù)的特性、分類在SQLServer中啟動、提交和回滾事務(wù)掌握如何創(chuàng)建索引掌握如何創(chuàng)建并使用視圖為什么需要事務(wù)事務(wù)能確保把對多個數(shù)據(jù)操作作為一個單元來處理例如銀行轉(zhuǎn)帳:轉(zhuǎn)入和轉(zhuǎn)出問題:假定張三賬戶直接轉(zhuǎn)1000元到李四賬戶,模擬過程如下:準(zhǔn)備:建立賬戶表bank,包括客戶名字和當(dāng)前余額銀行規(guī)定，帳戶余額不能少于1元，否則視為銷戶存錢:插入測試數(shù)據(jù)，張三開戶，開戶金額為1000元；李四開戶，開戶金額為1元演示開戶過程為什么需要事務(wù)模擬實現(xiàn)轉(zhuǎn)帳：使用UPDATE語句,從張三的帳戶直接轉(zhuǎn)帳1000元到李四的帳戶

,兩賬戶總額應(yīng)保持不變--張三帳戶減少1000元，李四帳戶增多1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=’張三’UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName=’李四’轉(zhuǎn)帳后:兩賬戶總余額為2001元轉(zhuǎn)帳前:兩賬戶總余額為1001元事務(wù)的概念及特性事務(wù)提供了一種機(jī)制、是一個操作序列，它包含了一組數(shù)據(jù)庫操作命令，并且所有的命令作為一個整體一起向系統(tǒng)提交或撤消操作請求事務(wù)是作為單個邏輯工作單元執(zhí)行的一系列操作事務(wù)的四個特性:原子性（Atomicity）：事務(wù)能確保把對多個數(shù)據(jù)修改作為一個單元來處理，也就是原子操作。一致性（Consistency）：當(dāng)事務(wù)完成時，數(shù)據(jù)必須處于一致狀態(tài)隔離性（Isolation）:對數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的。這表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴或影響其他事務(wù)持久性（Durability）：當(dāng)事務(wù)完成之后，它對于系統(tǒng)的影響是永久性的。事務(wù)的分類事務(wù)分類顯式事務(wù)隱性事務(wù)自動提交事務(wù)顯式事務(wù):顯式事務(wù)是顯式地定義其開始和結(jié)束的事務(wù)

BEGINTRANSACTION…數(shù)據(jù)庫操作如:插入記錄

…數(shù)據(jù)庫操作如:刪除記錄COMMITTRANSACTION事務(wù)的分類隱性事務(wù):通過Transact-SQL的SETIMPLICIT_TRANSACTIONSON語句，將隱性事務(wù)模式設(shè)置為打開。

SETIMPLICIT_TRANSACTIONSONGO/*第一次執(zhí)行Insert語句的時候?qū)⒆詣訂右粋€隱性事務(wù)*/INSERTINTOImpTranVALUES(1,'aaa')INSERTINTOImpTranVALUES(2,'bbb')GO/*提交第一個事務(wù)*/COMMITTRANSACTIONGO/*執(zhí)行SELECT語句將啟動第二個隱性事務(wù)*/SELECTCOUNT(*)FROMImpTranGOINSERTINTOImpTranVALUES(3,'ccc')GOSELECT*FROMImpTranGO/*提交第二個事務(wù)*/COMMITTRANSACTIONGOSETIMPLICIT_TRANSACTIONSOFFGO事務(wù)的分類自動提交事務(wù):所有Transact-SQL語句在完成時，都會提交或回滾。如果一條語句成功完成，則將其提交，如果遇到任何錯誤，則將其回滾;默認(rèn)操作模式用T-SQL表示事務(wù)Transact-SQL使用下列語句來管理事務(wù)開始事務(wù)：BEGINTRANSACTION提交事務(wù)：COMMITTRANSACTION回滾（撤消）事務(wù)：ROLLBACKTRANSACTION下列變量在事務(wù)處理中非常有用。@@ERROR@@TRANCOUNT事務(wù)的應(yīng)用USEempDBGOSETNOCOUNTON--不顯示受影響的行數(shù)信息print‘查看轉(zhuǎn)帳事務(wù)前的余額’SELECT*FROMbankGO/*--開始事務(wù)（指定事務(wù)從此處開始，后續(xù)的T-SQL語句都是一個整體）*/BEGINTRANSACTION/*定義變量，用于累計事務(wù)執(zhí)行過程中的錯誤--*/DECLARE@errorNoINTSET@errorNo=0--初始化為0，即無錯誤/*--張三的帳戶減少1000元，李四的帳戶多1000元*/UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName=’張三’Set@errorNo=@errorNo+@@error--累計是否有錯誤Print‘查看轉(zhuǎn)帳過程中的余額’SELECT*FROMbank/*--根據(jù)語句執(zhí)行情況，確定事務(wù)是提交或撤消--*/IF@errorNo<>0--如果有錯誤

BEGINPrint‘交易失敗，回滾事務(wù)’

ROLLBACKTRANSACTIONENDELSEBEGINPrint‘交易成功，提交事務(wù)，永久保存’

COMMITTRANSACTIONENDGOPrint‘查看轉(zhuǎn)帳事務(wù)后的余額’SELECT*FROMbankGO

索引索引的概念索引：是SQLServer編排數(shù)據(jù)的內(nèi)部方法。它為SQLServer提供一種方法來編排查詢數(shù)據(jù)的路由索引頁：數(shù)據(jù)庫中存儲索引的數(shù)據(jù)頁。索引頁存放檢索數(shù)據(jù)行的關(guān)鍵字頁以及該數(shù)據(jù)行的地址指針。索引頁類似于漢語字典中按拼音或筆畫排序的目錄頁創(chuàng)建索引創(chuàng)建索引有兩種方法:使用SSMS(SQLServerManagementStudio)T-SQL語句語法:其中：UNIQUE指定唯一索引，可選CLUSTERED、NOCLUSTERED指定是聚集索引還是非聚集索引,可選。CREATE[UNIQUE][CLUSTERED][NONCLUSTERED]INDEXindex_nameONtable_name(column_name[,column_name]。。。)創(chuàng)建索引示例問題:員工表EMP中工資列（sal）經(jīng)常查詢，為了加快查詢速度,在工資列上創(chuàng)建索引，該索引為非聚集索引

創(chuàng)建索引：

USEempDBGO/*--工資列創(chuàng)建非聚集索引*/CREATENONCLUSTEREDINDEXIX_emp_salONemp(sal)GO

/*--指定按索引：IX_emp_sal查詢--*/CREATENONCLUSTEREDINDEXIX_emp_salONemp(sal)SELECT*FROMemp(INDEX=IX_emp_sal)WHERESalBETWEEN2000AND3000指定按索引查詢：索引的類型索引分類聚集索引非聚集索引聚集索引:聚集索引基于數(shù)據(jù)行的鍵值在表內(nèi)排序和存儲這些數(shù)據(jù)。聚集索引類似于電話簿

索引前索引后創(chuàng)建聚集索引注意事項:選擇唯一值比例高的列。一般應(yīng)選擇主鍵列。先創(chuàng)建聚集索引，再創(chuàng)建非聚集索引在經(jīng)常被使用聯(lián)結(jié)或GROUPBY子句的查詢訪問列上創(chuàng)建聚集索引；聚集索引不適用于頻繁更改的列，因為這將導(dǎo)致整行移動。CREATECLUSTEREDINDEXClindx_empnoONemp(empno)在emp表的empno列上創(chuàng)建聚集索引Clindx_empno非聚集索引非聚集索引非聚集索引指定表的邏輯順序。因此，一個表可以有多個非聚集索引數(shù)據(jù)存儲在一個位置，索引存儲在另一個位置，索引中包含指向數(shù)據(jù)存儲位置的指針。大多數(shù)所以都是非聚集所在emp表的ename列創(chuàng)建非聚集索引CREATENOCLUSTEREDINDEXNCLINDX_enameONemp(ename)索引的特性索引唯一索引組合索引唯一索引:唯一索引不允許兩行具有相同的索引值

問題:在EMP表中的員工姓名列上創(chuàng)建了唯一索引，則所有的員工都不能重名

創(chuàng)建唯一索引需要注意的事項

:只有當(dāng)唯一性是數(shù)據(jù)本身的特征時，指定唯一索引才有意義。如果必須實施唯一性以確保數(shù)據(jù)的完整性，則應(yīng)在列上創(chuàng)建UNIQUE或PRIMARYKEY約束，而不要創(chuàng)建唯一索引。創(chuàng)建PRIMARYKEY或UNIQUE約束會在表中指定的列上自動創(chuàng)建唯一索引

CREATEUNIQUECLUSTEREDINDEXempno_indONemp_pay(empno)索引的特性索引唯一索引組合索引組合索引:組合索引包含兩個或更多為創(chuàng)建索引而組合在一起的列。最多可以組合16列

問題:stuDB數(shù)據(jù)庫的成績表grade，在學(xué)號sno和課程號cno列的組合上創(chuàng)建聚集索引

創(chuàng)建組合索引時應(yīng)遵循下列原則:當(dāng)需要頻繁地將兩個或多個列作為一個整體進(jìn)行搜索時，可以創(chuàng)建組合索引。創(chuàng)建組合索引時，先列出唯一性最好的列。組合索引中列的順序和數(shù)量會影響查詢的性能

CREATEUNIQUECLUSTEREDINDEXUPKCL_gradeONgrade(sno,cno)

創(chuàng)建索引的指導(dǎo)原則

使用索引雖然可以加快數(shù)據(jù)檢索速度，但沒必要為每個字段都建立索引在經(jīng)常要搜索的列上創(chuàng)建索引，例如：主鍵所在的列外鍵或在聯(lián)結(jié)表中經(jīng)常使用的列以排序次序訪問的列在聚合過程中被劃分為一組的列。不需要索引的列在查詢中很少引用的列由text、ntext或image數(shù)據(jù)類型定義的列，具有這些數(shù)據(jù)類型的列不能進(jìn)行索引刪除索引使用DROPINDEX語句可以刪除表的索引

刪除索引時的注意事項:不能將DROPINDEX語句用于由PRIMARYKEY或UNIQUE約束創(chuàng)建的索引，必須先刪除這些約束才能刪除索引在刪除表時，該表所有的索引也將隨之刪除在刪除聚集索引時，表中所有的非聚集索引都會被自動重鍵DROPINDEX表名.索引名

USEempDBDROPINDEXemp.IX_emp_sal語法:示例:

從emp表中刪除IX_emp_sal索引視圖的概念及優(yōu)點(diǎn)概念:視圖是從不同的視角查看數(shù)據(jù)庫中一個或多個表中數(shù)據(jù)的方法。視圖是一種虛擬表，它的數(shù)據(jù)并不真正存儲，僅保存視圖定義

優(yōu)點(diǎn):對最終用戶的好處結(jié)果更容易理解。

對開發(fā)人員的好處

簡化查詢限制數(shù)據(jù)檢索

創(chuàng)建視圖創(chuàng)建視圖的方法有兩種：使用SSMS和T-SQL語句使用SSMS創(chuàng)建視圖使用T-SQL語句創(chuàng)建視圖

語法:演示CREATEVIEWview_nameAS<select語句>

示例:創(chuàng)建視圖，顯示部門表中部門名稱和位置USEempDBGO/*創(chuàng)建視圖時，如果查詢中沒指定列別名，則默認(rèn)視圖列名與表的列名相同*/CREATEVIEWdept_viewASSELECTdname,locFROMDEPTGO--使用視圖SELECT*FROMdept_view視圖分類分類:簡單視圖復(fù)雜視圖簡單視圖:視圖的列取自于一個表簡單視圖可以像基表一樣進(jìn)行增、刪、改操作，操作后的數(shù)據(jù)會直接反應(yīng)到源表。復(fù)雜視圖:復(fù)雜視圖是視圖中列取自多個表，比如：建立視圖的查詢包含多表聯(lián)接、聚合函數(shù)等例如:建立視圖complex_view,求出部門號和每個部門的人數(shù)復(fù)雜視圖不能直接進(jìn)行增、刪、改操作，如果進(jìn)行修改操作，要通過INSTEADOF觸發(fā)器進(jìn)行。

CREATEVIEWcomplex_viewASSELECTdeptno