網(wǎng)絡(luò)數(shù)據(jù)庫 課件 項目5、6 索引和視圖、程序設(shè)計

項目五

索引和視圖任務(wù)1索引任務(wù)2視圖任務(wù)1索引任務(wù)引入小林在創(chuàng)建大型數(shù)據(jù)庫時發(fā)現(xiàn)信息比較混亂，要是能像書的目錄一樣通過搜索索引找到特定的值就好了。那么，怎么在數(shù)據(jù)庫中創(chuàng)建索引？怎么查看和刪除索引？知識準(zhǔn)備可以利用索引快速訪問數(shù)據(jù)庫表中的特定信息。索引是對數(shù)據(jù)庫表中一個或多個列的值進(jìn)行排序的結(jié)構(gòu)。索引提供指針以指向存儲在表中指定列的數(shù)據(jù)值，然后根據(jù)指定的排序次序排列這些指針。數(shù)據(jù)庫使用索引的方式與使用書的目錄很相似：通過搜索索引找到特定的值，然后跟隨指針到達(dá)包含該值的行。在數(shù)據(jù)庫關(guān)系圖中，可以為選定的表創(chuàng)建、編輯或刪除索引/鍵屬性頁中的每個索引類型。當(dāng)保存附加在此索引上的表或包含此表的數(shù)據(jù)庫關(guān)系圖時，索引同時被保存。一、創(chuàng)建索引SQLServer提供了兩種方法來創(chuàng)建索引：直接創(chuàng)建索引：

間接創(chuàng)建索引：在創(chuàng)建索引時，需要指定索引的特征。這些特征包括下面幾項：聚集還是非聚集。惟一還是不惟一。單列還是多列。索引中的列順序?yàn)樯蜻€是降序。覆蓋還是非覆蓋。1．使用SSMS創(chuàng)建索引選擇“管理索引”命令“從dbo.student中選擇列”對話框2．使用SQL語言創(chuàng)建索引只有表或視圖的所有者才能為表創(chuàng)建索引。

設(shè)置參數(shù)

新建索引案例——在score表中的course_ID列上創(chuàng)建一個非聚集索引。二、查看和刪除索引查看和刪除索引均有兩種方法：使用圖形化界面和SQL語言。1．使用SSMS查看和刪除索引創(chuàng)建IDX_ID索引“索引屬性”對話框2．使用SQL語言查看和刪除索引（1）顯示索引要查看索引信息，可使用存儲過程sp_helpindex。案例——顯示student表上的索引信息。（2）刪除索引刪除索引使用DROPINDEX語句，其語法格式如下：DROPINDEX'table.index|view.index'[,…n]其中，“table”和“view”是索引列所在的表或索引視圖；“index”是要除去的索引名稱。索引名必須符合標(biāo)識符的規(guī)則?！皀”表示可以指定多個索引的占位符。顯示索引任務(wù)2視圖任務(wù)引入小林想將數(shù)據(jù)庫中的幾個表導(dǎo)出為視圖，然后通過視圖檢索數(shù)據(jù)和修改數(shù)據(jù)。那么，怎么在數(shù)據(jù)庫中創(chuàng)建視圖？怎么在視圖中進(jìn)行數(shù)據(jù)檢索？怎么通過視圖修改數(shù)據(jù)？知識準(zhǔn)備視圖是從一個或者多個表中使用SELECT語句導(dǎo)出的。那些用來導(dǎo)出視圖的表稱為基表。視圖也可以從一個或者多個其他視圖中產(chǎn)生。導(dǎo)出視圖的SELECT語句存放在數(shù)據(jù)庫中，而與視圖定義相關(guān)的數(shù)據(jù)并沒有在數(shù)據(jù)庫中另外保存一份，因此，視圖也稱為虛表。視圖的行為和表類似，可以通過視圖查詢表的數(shù)據(jù)，也可以修改表的數(shù)據(jù)。對其中所引用的基礎(chǔ)表來說，視圖的作用類似于篩選。定義視圖的篩選可以來自當(dāng)前或其他數(shù)據(jù)庫的一個或多個表，或者其他視圖。所以說，視圖是一種SQL查詢。在數(shù)據(jù)庫中，存儲的是視圖的定義，而不是視圖查詢的數(shù)據(jù)。通過這個定義，對視圖查詢最終轉(zhuǎn)換為對基表的查詢。一、創(chuàng)建視圖要使用視圖，首先必須創(chuàng)建視圖。視圖在數(shù)據(jù)庫中是作為一個獨(dú)立的對象進(jìn)行存儲的。創(chuàng)建視圖要考慮如下的原則：

只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建視圖。視圖名稱必須遵循標(biāo)識符的規(guī)則，且對每個用戶必須惟一。此外，該名稱不得與該用戶擁有的任何表的名稱相同?？梢栽谄渌晥D和引用視圖的過程之上建立視圖。定義視圖的查詢不可以包含ORDERBY、COMPUTE或COMPUTEBY子句以及INTO關(guān)鍵字。不能在視圖上定義全文索引。不能創(chuàng)建臨時視圖，也不能在臨時表上創(chuàng)建視圖。不能對視圖執(zhí)行全文查詢，但是如果查詢所引用的表被配置為支持全文索引，就可以在視圖定義中包含全文查詢。1．使用SSMS創(chuàng)建視圖視圖保存在數(shù)據(jù)庫中而查詢不是，因此創(chuàng)建新視圖的過程與創(chuàng)建查詢的過程不同。通過SSMS不但可以創(chuàng)建數(shù)據(jù)庫和表，也可以創(chuàng)建視圖?？旖莶藛?/p>

“添加表”對話框添加表到視圖選擇需要的字段2．使用SQL語句創(chuàng)建視圖案例——創(chuàng)建student_specialty視圖，其中包括了學(xué)生姓名、專業(yè)和學(xué)院視圖中可以使用的列最多可達(dá)1024列。另外，在創(chuàng)建視圖時，視圖的名稱存儲在sysobjects表中。有關(guān)視圖中所定義的列的信息添加到syscolumns表中，而有關(guān)視圖相關(guān)性的信息添加到sysdepends表中。另外，CREATEVIEW語句的文本添加到syscomments表中。輸入視圖名稱

創(chuàng)建student_grade視圖

創(chuàng)建student_specialty視圖二、使用視圖通過視圖可以檢索基表中的數(shù)據(jù)，也可以通過視圖來修改基表中的數(shù)據(jù)，例如插入、刪除和修改記錄。1．使用視圖進(jìn)行數(shù)據(jù)檢索視圖是基于基表生成的，因此可以用來將需要的數(shù)據(jù)集中在一起，而不需要的數(shù)據(jù)則不需要顯示。使用視圖來檢索數(shù)據(jù)，可以像對表一樣來對視圖進(jìn)行操作。案例——使用創(chuàng)建的student_grade視圖來查詢成績大于90分的學(xué)生案例——使用SSMS查看視圖的數(shù)據(jù)查詢成績大于90分的學(xué)生通過視圖檢索數(shù)據(jù)2．通過視圖修改數(shù)據(jù)通過視圖修改其中的某些行時，SQLServer將把它轉(zhuǎn)換為對基表的某些行的操作。對于簡單的視圖來說，可能比較容易實(shí)現(xiàn)，但是對于比較復(fù)雜的視圖，可能就不能通過視圖進(jìn)行修改。案例——新建視圖specialty1并添加數(shù)據(jù)案例——更新數(shù)據(jù)案例——新建視圖，刪除學(xué)號為16053113的數(shù)據(jù)插入數(shù)據(jù)后的specialty表

更新后的college表

通過視圖刪除數(shù)據(jù)三、刪除視圖在創(chuàng)建視圖后，如果不再需要該視圖，或想清除視圖定義及與之相關(guān)聯(lián)的權(quán)限，可以刪除該視圖。刪除視圖后，表和視圖所基于的數(shù)據(jù)并不受影響。任何使用基于已刪除視圖的對象的查詢將會失敗，除非創(chuàng)建了同樣名稱的一個視圖。在刪除視圖時，定義在系統(tǒng)表sysobjects、syscolumns、syscomments、sysdepends和sysprotects中的視圖信息也會被刪除，而且視圖的所有權(quán)限也一并被刪除。1．使用SSMS刪除視圖（1）在對象資源管理器中，展開“數(shù)據(jù)庫”文節(jié)點(diǎn)，展開該視圖所屬的數(shù)據(jù)庫，然后單擊“視圖”節(jié)點(diǎn)。（2）這里選擇“school”數(shù)據(jù)庫“視圖”節(jié)點(diǎn)中的st2_degree，右擊鼠標(biāo)，在彈出的快捷菜單中選擇“刪除”選項，打開“刪除對象”對話框，其中的“顯示依賴關(guān)系”按鈕用于查看刪除此視圖對數(shù)據(jù)庫的影響。（3）單擊“確定”按鈕即可刪除視圖；單擊“取消”按鈕取消刪除操作。2．使用SQL刪除視圖使用DROPVIEW語句可從當(dāng)前數(shù)據(jù)庫中刪除一個或多個視圖。項目總結(jié)項目實(shí)戰(zhàn)實(shí)戰(zhàn)一為emp表的“員工號”和“工作名稱”列創(chuàng)建聚集索引，并且強(qiáng)制惟一性（1）因?yàn)檫@里要創(chuàng)建強(qiáng)制唯一性的索引，必須先刪除emp中已經(jīng)存在的索引。（2）在對象資源管理器中選取“factory”數(shù)據(jù)庫，單擊工具欄中的“新建查詢”按鈕創(chuàng)建IDX_no索引實(shí)戰(zhàn)二創(chuàng)建View1視圖，查詢所有員工的姓名、員工號、所在部門以及工資

查詢所有員工的所在部門以及工資項目六

程序設(shè)計任務(wù)1程序設(shè)計基礎(chǔ)任務(wù)2事務(wù)處理任務(wù)3使用游標(biāo)任務(wù)1程序設(shè)計基礎(chǔ)任務(wù)引入小林不僅想查詢數(shù)據(jù)，還想對表中的數(shù)據(jù)進(jìn)行運(yùn)算和控制。那么，怎么利用SQL語言進(jìn)行編程滿足要求呢？編寫的程序中會用到什么變量以及流程控制語句呢？知識準(zhǔn)備一、變量在SQLServer中，變量分為局部變量和全局變量。全局變量名稱前面有兩個at符號（＠＠），由系統(tǒng)定義和維護(hù)。局部變量前面有一個at符號（＠），由用戶定義和使用。1．局部變最局部變量由用戶定義，僅在聲明它的批處理、存儲過程或者觸發(fā)器中有效。批處理結(jié)束后，局部變量將變成無效。各參數(shù)含義如下：@local_variable：是變量的名稱。data_type：是任何由系統(tǒng)提供的或用戶定義的數(shù)據(jù)類型。案例——查詢并輸出學(xué)院號為03的院長姓名案例——查詢score表中選修課程號為“01054010”且成績高于80的記錄

查詢03號學(xué)院的院長名稱查詢成績高于80的記錄2．全局變量全局變量記錄了SQLServer的各種狀態(tài)信息，它們不能被顯式地賦值或聲明，而且不能由用戶定義。在SQLServer中，定義了全局變量，如表所示。二、流程控制語句SQL提供稱為控制流的特殊關(guān)鍵字，用于控制SQL語句、語句塊和存儲過程的執(zhí)行流。這些關(guān)鍵字可用于SQL語句、批處理和存儲過程中?？刂屏髡Z句就是用來控制程序執(zhí)行流程的語句，使用控制流語句可以在程序中組織語句的執(zhí)行流程，提高編程語言的處理能力。SQLServer提供的控制流語句如表所示。1．BEGIN…END語句BEGIN…END語句用于將多個SQL語句組合為一個邏輯塊。在執(zhí)行時，該邏輯塊作為一個整體被執(zhí)行。案例——設(shè)置變量值設(shè)置變量值下面幾種情況經(jīng)常要用到BEGIN和END語句：WHILE循環(huán)需要包含語句塊。CASE函數(shù)的元素需要包含語句塊。IF或ELSE子句需要包含語句塊。2．IF…ELSE語句使用IF…ELSE語句，可以有條件地執(zhí)行語句。案例——查詢大學(xué)語文課程的成績狀況

查詢成績3．CASE語句使用CASE語句可以進(jìn)行多個分支的選擇。CASE具有兩種格式：簡單CASE格式將某個表達(dá)式與一組簡單表達(dá)式進(jìn)行比較以確定結(jié)果。搜索CASE格式計算一組布爾表達(dá)式以確定結(jié)果。（1）簡單CASE格式案例——查詢課程的課程類型（2）搜索CASE格式案例——查詢學(xué)生成績查詢老師的單位和職稱查詢學(xué)生成績4．WHILE語句WHILE語句可以設(shè)置重復(fù)執(zhí)行SQL語句或語句塊的條件。只要指定的條件為真，就重復(fù)執(zhí)行語句?？梢允褂肂REAK和CONTINUE關(guān)鍵字在循環(huán)內(nèi)部控制WHILE循環(huán)中語句的執(zhí)行。案例——在查詢到成績表中沒有學(xué)生成績超過95分的情況下，將所有成績提高5分，反復(fù)執(zhí)行，直到成績超過95分。5．GOTO語句GOTO語句可以實(shí)現(xiàn)無條件的跳轉(zhuǎn)。高學(xué)生成績案例——輸出18032142學(xué)號學(xué)生的平均成績6．RETURN語句使用RETURN語句，可以從查詢或過程中無條件退出?？稍谌魏螘r候用于從過程、批處理或語句塊中退出，而不執(zhí)行位于RETURN之后的語句。案例——查詢18032142學(xué)號學(xué)生的成績和平均分7．WAITFOR語句使用WAITFOR語句，可以在指定的時間或者過了一定時間后，執(zhí)行語句塊、存儲過程或者事務(wù)。輸出18032142學(xué)號學(xué)生的平均成績輸出18032142學(xué)號學(xué)生的成績和平均分任務(wù)2事務(wù)處理任務(wù)引入小林需要對之前做的數(shù)據(jù)庫進(jìn)行大量的修改，但是他又擔(dān)心修改后不對還得一點(diǎn)一點(diǎn)恢復(fù)，他咨詢老師，怎么處理這個問題，老師告訴他可以采用事物處理來解決這一問題。那么，什么是事物，事物又分為哪些類別呢？顯示事務(wù)有哪些步驟呢？知識準(zhǔn)備事務(wù)是SQLServer中的單個邏輯單元，一個事務(wù)內(nèi)的所有SQL語句作為一個整體執(zhí)行，要么全部執(zhí)行，要么都不執(zhí)行。一個邏輯工作單元必須有4個屬性，稱為ACID（原子性、一致性、隔離性和持久性）屬性，只有這樣才能成為一個事務(wù)。原子性（Atomicity）：事務(wù)必須是原子工作單元。一致性（Consistency）：事務(wù)在完成時，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。隔離性（Isolation）：由并發(fā)事務(wù)所做的修改必須與任何其他并發(fā)事務(wù)所做的修改隔離。持久性（Durability）：事務(wù)完成之后，它對于系統(tǒng)的影響是永久性的。一、事務(wù)分類按事務(wù)的啟動和執(zhí)行方式，可以將事務(wù)分為3類：顯式事務(wù)：也稱為用戶定義或用戶指定的事務(wù)，即可以顯式地定義啟動和結(jié)束的事務(wù)。自動提交事務(wù)：自動提交模式是SQLServer的默認(rèn)事務(wù)管理模式。

隱性事務(wù)：當(dāng)連接以隱性事務(wù)模式進(jìn)行操作時，SQLServer將在提交或回滾當(dāng)前事務(wù)后自動啟動新事務(wù)。二、顯式事務(wù)顯式事務(wù)需要顯式地定義事務(wù)的啟動和結(jié)束。它是通過BEGINTRANSACTION、COMMITTRANSACTION、COMMITWORK、ROLLBACKTRANSACTION或ROLLBACKWORK等SQL語句來完成的。1．啟動事務(wù)啟動事務(wù)使用BEGINTRANSACTION語句，該語句將@@TRANCOUNT加1。2．結(jié)束事務(wù)如果沒有遇到錯誤，可使用COMMITTRANSACTION語句成功地結(jié)束事務(wù)。3．回滾事務(wù)如果事務(wù)中出現(xiàn)錯誤，或者用戶決定取消事務(wù)，可回滾該事務(wù)。案例——啟動一個事務(wù)向college表中插入一個記錄，然后回滾該事務(wù)4．在事務(wù)內(nèi)設(shè)置保存點(diǎn)設(shè)置保存點(diǎn)使用SAVETRANSACTION語句案例——在事務(wù)內(nèi)設(shè)置保存點(diǎn)查詢表

查詢表5．標(biāo)記事務(wù)WITHMARK選項使事務(wù)名置于事務(wù)日志中。將數(shù)據(jù)庫還原到早期狀態(tài)時，可使用標(biāo)記事務(wù)替代日期和時間。6．不能用于事務(wù)的操作在事務(wù)處理中，并不是所有的SQL語句都可以取消執(zhí)行，一些不能撤消的操作（如創(chuàng)建、刪除和修改數(shù)據(jù)庫的操作），即使SQLServer取消了事務(wù)執(zhí)行或者對事務(wù)進(jìn)行了回滾，這些操作對數(shù)據(jù)庫造成的影響也是不能恢復(fù)的。因此，這些操作不能用于事務(wù)處理。這些操作如表所示。7．自動提交事務(wù)SQLServer使用BEGINTRANSACTION語句啟動顯式事務(wù)，或隱性事務(wù)模式設(shè)置為打開之前，將以自動提交模式進(jìn)行操作。不能用于事務(wù)的操作三、隱式事務(wù)在為連接將隱性事務(wù)模式設(shè)置為打開之后，當(dāng)SQLServer首次執(zhí)行某些SQL語句時，都會自動啟動一個事務(wù)，而不需要使用BEGINTRANSACTION語句。任務(wù)3使用游標(biāo)任務(wù)引入小劉老師讓小林幫忙從學(xué)校的數(shù)據(jù)庫中查詢他們專業(yè)前十名的學(xué)生信息并打印出來，可是小林查詢出來是所有學(xué)生信息按名次排名，這時小林想到用游標(biāo)來提取數(shù)據(jù)。那么，怎么使用游標(biāo)呢？知識準(zhǔn)備關(guān)系數(shù)據(jù)庫中的操作會對整個行集產(chǎn)生影響。由SELECT語句返回的行集包括所有滿足該語句WHERE子句中條件的行。由語句所返回的這一完整的行集稱為結(jié)果集。應(yīng)用程序，特別是交互式聯(lián)機(jī)應(yīng)用程序，并不總能將整個結(jié)果集作為一個單元來有效地處理。這些應(yīng)用程序需要一種機(jī)制，以便每次處理一行或一部分行。游標(biāo)就是用來提供這種機(jī)制的結(jié)果集擴(kuò)展。游標(biāo)包括以下兩個部分：游標(biāo)結(jié)果集（CursorResultSet）：由定義該游標(biāo)的SELECT語句返回的行的集合