第6章 創(chuàng)建和管理數(shù)據(jù)庫對象

第6章創(chuàng)建(chuàngjiàn)和管理數(shù)據(jù)庫對象計算機系胡駿共八十五頁數(shù)據(jù)庫的對象(duìxiàng)數(shù)據(jù)庫的對象包括索引、視圖、觸發(fā)器、存儲過程、關(guān)系圖、用戶定義的數(shù)據(jù)類型、用戶定義的函數(shù)。索引加快查詢速度視圖控制用戶對數(shù)據(jù)的訪問觸發(fā)器對表進行插入(chārù)、更改、刪除操作時自動運行的命令存儲過程完成特定功能的命令。用戶定義的數(shù)據(jù)類型豐富數(shù)據(jù)類型的種類，方面用戶使用用戶定義的函數(shù)靈活的為各種計算提供函數(shù)支持。共八十五頁6.1創(chuàng)建和管理(guǎnlǐ)索引SQL訪問(fǎngwèn)表中數(shù)據(jù)的方式：①表掃描方式②遍歷索引共八十五頁SQL訪問表中數(shù)據(jù)(shùjù)的方式共八十五頁使用(shǐyòng)索引的優(yōu)缺點優(yōu)點：加快查詢搜索數(shù)據(jù)的速度缺點：若對索引列上數(shù)據(jù)進行刪除、更新、插入操作所耗費的時間比沒有(méiyǒu)索引時要長。結(jié)論：小心設(shè)定索引。共八十五頁6.1.1索引的設(shè)計原則(yuánzé)和索引類型1.索引設(shè)計原則：⑴創(chuàng)建索引對精確查詢、范圍查詢、以及外鍵約束都是有利的。⑵一個表中有較多索引，影響數(shù)據(jù)的添加、更新(gēngxīn)、刪除時的性能。⑶小型表不需要索引，因為遍歷表所花費的時間會比遍歷索引短。共八十五頁6.1.1索引的設(shè)計原則(yuánzé)和索引類型⑷在頻繁搜索的字段上建立(jiànlì)索引：①主鍵②外鍵③經(jīng)常進行范圍搜索、精確搜索的字段④按關(guān)鍵字排序的字段共八十五頁6.1.1索引(suǒyǐn)的設(shè)計原則和索引(suǒyǐn)類型⑸不需要索引的字段①查詢中很少涉及(shèjí)的字段②有大量重復(fù)值的字段③更新性能比查詢性能更重要的列④定義TEXT、NTEXT或IMAGE數(shù)據(jù)類型的字段⑹對外鍵創(chuàng)建索引時先創(chuàng)建聚集索引在創(chuàng)建非聚集索引。共八十五頁6.1.1索引(suǒyǐn)的設(shè)計原則和索引(suǒyǐn)類型2.索引(suǒyǐn)的類型聚集索引：基于數(shù)據(jù)行的鍵值在表內(nèi)排序和存儲這些數(shù)據(jù)行。非聚集索引：具有完全獨立于數(shù)據(jù)行的結(jié)構(gòu)。非聚集索引的最低行包含非聚集索引的鍵值，并且每個鍵值項都有指針指向包含該鍵值的數(shù)據(jù)行。共八十五頁共八十五頁共八十五頁何時使用(shǐyòng)聚集索引或非聚集索引動作描述使用聚集索引使用非聚集索引列經(jīng)常被分組排序應(yīng)應(yīng)返回某范圍內(nèi)的數(shù)據(jù)應(yīng)不應(yīng)一個或極少不同值不應(yīng)不應(yīng)小數(shù)目的不同值應(yīng)不應(yīng)大數(shù)目的不同值不應(yīng)應(yīng)頻繁更新的列不應(yīng)應(yīng)外鍵列應(yīng)應(yīng)主鍵列應(yīng)應(yīng)頻繁修改索引列不應(yīng)應(yīng)共八十五頁測試聚集(jùjí)索引和非聚集(jùjí)索引性能現(xiàn)搭建(dājiàn)好測試平臺：學(xué)生基本情況表中有100萬條記錄。運行程序：select*from學(xué)生基本情況表where出生日期>‘1980-3-20’①當(dāng)在出生日期上建立非聚集索引時用時：53763毫秒（54秒）

②當(dāng)在出生日期上建立聚集索引時用時：2423毫秒（2秒）想一想為什么？共八十五頁怎樣測量查詢(cháxún)時間？declare@ddatetimeset@d=getdate()select*from學(xué)生基本(jīběn)情況表where出生日期>‘1980-3-20‘select[執(zhí)行時間(毫秒)]=Datediff(ms,@d,getdate())共八十五頁6.1.2創(chuàng)建(chuàngjiàn)索引1索引向?qū)?企業(yè)(qǐyè)管理器3查詢分析器4企業(yè)管理器中索引優(yōu)化向?qū)Ч舶耸屙摾肨-SQL語言(yǔyán)創(chuàng)建、查看、修改索引創(chuàng)建索引語法格式(géshi)：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}查看索引語法格式：sp_helpindextable_name修改索引名稱sp_renameold_index_name,new_index_name,‘index’刪除索引dropindexindex_name

共八十五頁利用(lìyòng)T-SQL語言創(chuàng)建、查看、修改索引在學(xué)生基本情況表中對學(xué)號建立聚集(jùjí)索引Createclusteredindexxs_聚集on學(xué)生基本情況（學(xué)號）把上述索引名稱改為學(xué)生索引sp_rename‘學(xué)生基本情況表.xs_聚集’,’學(xué)生索引’,‘index’查看學(xué)生基本情況表中索引信息sp_helpindex學(xué)生基本情況表刪除學(xué)生索引dropindex學(xué)生基本情況表.學(xué)生索引

共八十五頁6.2創(chuàng)建(chuàngjiàn)和管理視圖視圖作為一種基本的數(shù)據(jù)庫對象，是查詢一個表或多個表的另一種方法，它是通過把預(yù)先定義的查詢存儲在數(shù)據(jù)庫中，然后就可以再查詢語句中調(diào)用(diàoyòng)它。提示：①對于查詢的表我們把它叫做基表

②視圖來自于一個基表還是幾個基表對視圖中的操作有絕對的影響共八十五頁6.2.1視圖(shìtú)1.視圖的概念是一種虛擬的表或存儲查詢，它只包含表的一部分，其內(nèi)容有查詢需求定義。2.視圖的作用①返回用戶需要的數(shù)據(jù)②是數(shù)據(jù)庫查詢簡便直觀③方便數(shù)據(jù)的導(dǎo)出④實現(xiàn)(shíxiàn)對創(chuàng)建視圖的內(nèi)部表進行數(shù)據(jù)修改共八十五頁6.2.2創(chuàng)建(chuàngjiàn)視圖1.使用(shǐyòng)企業(yè)管理器創(chuàng)建視圖見錄像2.使用T-SQL語言創(chuàng)建視圖略共八十五頁6.2.3查看(chákàn)視圖1.使用企業(yè)管理器查看(chákàn)視圖共八十五頁6.2.3查看(chákàn)視圖2.使用存儲過程(guòchéng)查看視圖共八十五頁6.2.4修改、刪除(shānchú)及重命名視圖1.修改視圖ALTERVIEW班級視圖ASSELECT專業(yè)代碼(dàimǎ),系部名稱,專業(yè)名稱FROMdbo.班級2.重命名視圖SP_RENAME‘班級視圖’,‘班級視圖列表’3.刪除視圖DROPVIEW班級視圖列表共八十五頁6.2.5使用視圖操作(cāozuò)表數(shù)據(jù)1.使用視圖檢索(jiǎnsuǒ)數(shù)據(jù)可以在視圖中查詢數(shù)據(jù)SELECT班級名稱FROM班級視圖WHERE班級名稱=‘計算機網(wǎng)絡(luò)’共八十五頁6.2.5使用(shǐyòng)視圖操作表數(shù)據(jù)討論在視圖中添加、修改、刪除數(shù)據(jù)的時候(shíhou)，分為兩種情況：1視圖來自于一個基表2視圖來自于幾個不同的基表共八十五頁視圖(shìtú)與單個基表之間的關(guān)系①基表數(shù)據(jù)更改后，視圖數(shù)據(jù)會更改嗎？見錄像實驗結(jié)果：基表數(shù)據(jù)更改后，視圖數(shù)據(jù)也更改。②視圖中數(shù)據(jù)更改后，基表數(shù)據(jù)會更改嗎？見錄像實驗結(jié)果：視圖數(shù)據(jù)更改后，基表數(shù)據(jù)也會更改。③視圖更改后，其他(qítā)引用這個視圖的視圖會更改嗎？實驗結(jié)果：會（自己試驗）共八十五頁視圖(shìtú)與單個基表之間的關(guān)系④修改視圖中的數(shù)據(jù)，基表中的數(shù)據(jù)跟著進行修改嗎？會（自己試驗(shìyàn)）。⑤刪除視圖中的數(shù)據(jù)，基表中的數(shù)據(jù)會跟著刪除嗎？會（自己試驗）。共八十五頁視圖(shìtú)與多個基表之間的關(guān)系共八十五頁視圖與多個(duōɡè)基表之間的關(guān)系共八十五頁視圖(shìtú)與多個基表之間的關(guān)系①基表數(shù)據(jù)更改后，視圖數(shù)據(jù)會更改嗎？實驗結(jié)果(jiēguǒ)：基表數(shù)據(jù)更改后，視圖數(shù)據(jù)也更改。②視圖更改后，其他引用這個視圖的視圖會更改嗎？實驗結(jié)果：會（自己試驗）共八十五頁視圖與多個(duōɡè)基表之間的關(guān)系③視圖中數(shù)據(jù)(shùjù)更改后，基表數(shù)據(jù)(shùjù)會更改嗎？實驗結(jié)果：視圖數(shù)據(jù)更改后，多個基表數(shù)據(jù)也會更改。共八十五頁視圖(shìtú)與多個基表之間的關(guān)系④向視圖(shìtú)中插入數(shù)據(jù)，基表中的數(shù)據(jù)跟著進行修改嗎？不會（自己試驗）。共八十五頁視圖與多個(duōɡè)基表之間的關(guān)系⑤刪除視圖(shìtú)中的數(shù)據(jù)，基表中的數(shù)據(jù)會跟著刪除嗎？不會（自己試驗）。共八十五頁總結(jié)(zǒngjié)查詢插入刪除更新視圖與單個基表能查詢視圖中插入數(shù)據(jù)影響到基表視圖中刪除數(shù)據(jù)影響到基表視圖中更新數(shù)據(jù)影響到基表視圖與多個基表能查詢視圖中插入數(shù)據(jù)不成立視圖中刪除不成功視圖中更新數(shù)據(jù)影響到基表共八十五頁總結(jié)(zǒngjié)不能對視圖某些列進行數(shù)據(jù)操作。不允許改變是某個計算的結(jié)果的列，例如包括計算值、內(nèi)建函數(shù)或行聚合函數(shù)的列

若修改影響了在視圖中未引用的列，也有可能引起錯誤。例如往視圖中插入行的時候，其他(qítā)未被引用的列不允許空值且沒有默認(rèn)值共八十五頁6.3創(chuàng)建和管理(guǎnlǐ)存儲過程存儲過程的定義：將固定的操作使用控制流語句編寫(biānxiě)出來經(jīng)過預(yù)編譯存儲在數(shù)據(jù)庫中，在需要運行時進行調(diào)用，以實現(xiàn)某個特定的任務(wù)。優(yōu)點：①允許模塊化的程序設(shè)計②更快的執(zhí)行速度③有效降低網(wǎng)絡(luò)流量④較好的安全機制共八十五頁6.3創(chuàng)建(chuàngjiàn)和管理存儲過程存儲過程的分類：系統(tǒng)存儲過程：sp_rename，sp_help，……用戶自定義存儲過程：自己編寫

擴展(kuòzhǎn)存儲過程：僅存在于MASTER數(shù)據(jù)庫中。用于外部程序編寫好放在SQL服務(wù)器上共八十五頁6.3.1創(chuàng)建存儲(cúnchǔ)過程1.使用T-SQL語言(yǔyán)創(chuàng)建存儲過程CREATEPROCEDUREprocedure_nameASsql_statement[…n]

共八十五頁使用(shǐyòng)T-SQL語言創(chuàng)建存儲過程簡單應(yīng)用：createprocedure打印(dǎyìn)

asdeclare@varchar(10)set@var='你好'select@vargo共八十五頁注意(zhùyì)①不能將CREATEPROCEDURE語句與其他(qítā)SQL語句組合到單個批處理中。

共八十五頁注意(zhùyì)②創(chuàng)建存儲過程的權(quán)限默認(rèn)屬于數(shù)據(jù)庫所有者，該所有者可以將此權(quán)限授予其他用戶(yònghù)。在某數(shù)據(jù)庫下有兩個用戶a和b在a用戶下創(chuàng)建了一個存儲過程名稱叫PROC現(xiàn)在a用戶下把使用此存儲過程的權(quán)利授予bgrantexecuteonctob共八十五頁注意(zhùyì)可對存儲過程進行(jìnxíng)加密，這樣別人無論用什么方法也看不到你的腳本（程序）createprocedure打印withencryptionasdeclare@varchar(10)set@var='你好'select@vargo共八十五頁創(chuàng)建帶輸入(shūrù)參數(shù)的存儲過程輸入?yún)?shù)是指由調(diào)用程序(chéngxù)向存儲過程傳遞的參數(shù)。它們在創(chuàng)建存儲過程語句中被定義，而在執(zhí)行該存儲過程中給出相應(yīng)的變量值。語法:{@parameter

data_type}[=default]參數(shù)：@parameter：參數(shù)名，必須以@符號為前綴。data_type：參數(shù)的數(shù)據(jù)類型說明。default：如果執(zhí)行存儲過程時未提供該參數(shù)值，則使用默認(rèn)值。共八十五頁創(chuàng)建帶輸入?yún)?shù)(cānshù)的存儲過程例：創(chuàng)建一個(yīɡè)指定姓名學(xué)生成績單的存儲過程。createprocedure成績存儲過程

@namechar(10)asselect*from學(xué)生成績視圖

where姓名=@name執(zhí)行存儲過程：EXEC成績存儲過程‘張三’或者：EXEC成績存儲過程@name=‘張三’共八十五頁創(chuàng)建帶輸出參數(shù)(cānshù)的存儲過程從存儲過程(guòchéng)中返回一個或多個值。語法：

@參數(shù)名數(shù)據(jù)類型[=默認(rèn)值]OUTPUT@參數(shù)名：存儲過程的輸出參數(shù)名，必須以@符號為前綴。數(shù)據(jù)類型：該參數(shù)的數(shù)據(jù)類型說明。OUTPUT：指明輸出參數(shù)。注意，輸出參數(shù)必須位于所有輸入?yún)?shù)說明之后。共八十五頁創(chuàng)建(chuàngjiàn)帶輸出參數(shù)的存儲過程例：從下圖所示的視圖(shìtú)中查詢指定姓名同學(xué)的各科平均分。共八十五頁創(chuàng)建帶輸出參數(shù)(cānshù)的存儲過程CreateprocedurePROC_查詢成績(@NAMECHAR(10),@平均分intoutput)Asset@平均分=(selectavg(成績)from學(xué)生(xuésheng)成績視圖where姓名=@name)執(zhí)行：共八十五頁思考題創(chuàng)建一個存儲過程(guòchéng)能向下面的表中添加一條記錄，并寫出執(zhí)行語句共八十五頁6.3.2查看(chákàn)修改刪除存儲過程1.查看存儲過程sp_helpsp_helptext2.修改(xiūgǎi)存儲過程ALTERPROCEDUREprocedure_nameas3.刪除存儲過程dropprocedureprocedure_name4.重命名存儲過程sp_renameold_name,new_name共八十五頁6.4創(chuàng)建(chuàngjiàn)和管理觸發(fā)器觸發(fā)器是什么?觸發(fā)器是一類特殊(tèshū)的存儲過程，它們之間的主要區(qū)別在于，觸發(fā)器不允許用戶調(diào)用，當(dāng)對表進行插入、刪除、修改操作時由系統(tǒng)自動調(diào)用并執(zhí)行，而存儲過程必須通過用戶的調(diào)用才能執(zhí)行。共八十五頁6.4.1觸發(fā)器作用(zuòyòng)觸發(fā)器設(shè)計的初衷是：實現(xiàn)數(shù)據(jù)庫中相關(guān)表的級聯(lián)修改；但是級聯(lián)引用完整性中的級聯(lián)更新選項很好的完成了這項任務(wù)。雖然設(shè)計的初衷被另外的方法很好很方便的實現(xiàn)，并不表示觸發(fā)器不具備(jùbèi)應(yīng)用的價值，相反在數(shù)據(jù)庫中觸發(fā)器應(yīng)用越來越廣泛。共八十五頁6.4.1觸發(fā)器作用(zuòyòng)其他作用：①檢查數(shù)據(jù)輸入的正確性：說明：在數(shù)據(jù)庫中檢查數(shù)據(jù)輸入的正確性，可以使用CHECK約束，或者其他約束；但是CHECK約束有其本身的缺陷(quēxiàn)：不能參照其他表中的數(shù)據(jù)。共八十五頁說明(shuōmíng)這里只能用觸發(fā)器不能用CHECK約束(yuēshù)，因為CHECK約束(yuēshù)只能規(guī)定商品銷售數(shù)量在某個確定值之內(nèi)，而這里這里銷售數(shù)量的最大值是隨著庫存量變化而變化的此時要規(guī)定銷售數(shù)量不大于庫存數(shù)量怎么辦？商品庫存數(shù)量隨時在跟新共八十五頁6.4.1觸發(fā)器作用(zuòyòng)②檢查數(shù)據(jù)修改的正確性：當(dāng)對表中受觸發(fā)器保護的數(shù)據(jù)修改時，觸發(fā)器不但會自動更新其他表與其相關(guān)的數(shù)據(jù)，還可以自動檢查這些數(shù)據(jù)，只要有一個不符合條件，則修改數(shù)據(jù)失敗。如對于工資表中工資數(shù)據(jù)的更改規(guī)定不可以超過40%，使用觸發(fā)器可以檢查變更前后的數(shù)據(jù)如果幅度超過40%，那么回滾該操作(cāozuò)事務(wù)。共八十五頁6.4.2創(chuàng)建(chuàngjiàn)觸發(fā)器CREATETRIGGER觸發(fā)器名ON{表|視圖}{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}AS[{IFUPDATE(列名)[{AND|OR}UPDATE(列名)][...n]|IF(COLUMNS_UPDATED(){位操作符}更新位標(biāo)志){比較(bǐjiào)運算符}列的位標(biāo)志[,…n]}]SQL語句[…n]共八十五頁6.4.2創(chuàng)建(chuàngjiàn)觸發(fā)器觸發(fā)器的類型：

①after觸發(fā)器（后觸發(fā)）這種觸發(fā)器將在數(shù)據(jù)(shùjù)操作（insert,delete,update）之后才觸發(fā)，對變動的數(shù)據(jù)進行檢查，如果發(fā)現(xiàn)錯誤則取消或回滾數(shù)據(jù)更改。②insteadof觸發(fā)器（替代觸發(fā)）這種觸發(fā)器將在數(shù)據(jù)操作（insert,delete,update）之前觸發(fā)，轉(zhuǎn)而去執(zhí)行觸發(fā)器所定義的操作共八十五頁6.4.2創(chuàng)建(chuàngjiàn)觸發(fā)器③for觸發(fā)器（一般觸發(fā)器）這種觸發(fā)器就是after觸發(fā)器。如果在書寫程序的時候(shíhou)只有for參數(shù)那么系統(tǒng)默認(rèn)是后觸發(fā)器。共八十五頁注意(zhùyì)觸發(fā)器使用限制CREATETRIGGER必須是批處理中的第一條語句，并且只能應(yīng)用到一個表中。觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫中創(chuàng)建(chuàngjiàn)，不過觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫的外部對象。如果一個表的外鍵在DELETE/UPDATE操作上定義了級聯(lián)，則不能在該表上定義INSTEADOFDELETE/UPDATE觸發(fā)器。共八十五頁觸發(fā)器結(jié)構(gòu)(jiégòu)事件(shìjiàn)UPDATEINSERTDELETE條件AFTERINSTEADOF動作各種Transact-SQL語句共八十五頁觸發(fā)器示意圖共八十五頁共八十五頁共八十五頁修改(xiūgǎi)觸發(fā)器名觸發(fā)器語句(yǔjù)共八十五頁AFTER觸發(fā)器例題(lìtí)此時要規(guī)定(guīdìng)銷售數(shù)量不大于庫存數(shù)量，怎么辦？商品庫存數(shù)量隨時在跟新共八十五頁解答(jiědá)共八十五頁解答(jiědá)CREATETRIGGER銷售觸發(fā)器ON[dbo].[銷售]AFTERUPDATEASdeclare@xsslchar(10),@kcslchar(10)select@xssl=銷售數(shù)量from銷售select@kcsl=商品(shāngpǐn)數(shù)量from庫存IF@xssl>=@kcslraiserror('銷售數(shù)量不能大于庫存數(shù)量',16,1)rollbacktransaction共八十五頁結(jié)果(jiēguǒ)驗證圖共八十五頁INSTEADOF觸發(fā)器例題(lìtí)設(shè)置替代觸發(fā)器，不允許庫存表進行修改和刪除(shānchú)操作。CREATETRIGGER庫存觸發(fā)器ON[dbo].[庫存]INSTEADOFUPDATE,DELETEASraiserror(‘不允許更新、刪除庫存表中記錄',16,10)共八十五頁結(jié)果(jiēguǒ)驗證圖共八十五頁附加內(nèi)容(nèiróng)：觸發(fā)器使用的臨時表在創(chuàng)建觸發(fā)器的過程中，數(shù)據(jù)庫系統(tǒng)會自動創(chuàng)建deleted,inserted兩個臨時表供觸發(fā)器使用。

①deleted表用于存儲被update,delete語句影響的行的副本，當(dāng)系統(tǒng)對某個附加了觸發(fā)器的表執(zhí)delete語句的時候，被刪除的記錄(jìlù)從原表中消失，但會存儲在deleted表中。而執(zhí)行update語句的時候，更新后的記錄(jìlù)會替代原來舊的記錄(jìlù)，而舊的記錄(jìlù)會傳輸?shù)絛eleted表中存儲。共八十五頁附加內(nèi)容(nèiróng)：觸發(fā)器使用的臨時表②inserted表用來存儲insert,update命令所影響到行的副本。當(dāng)對一個帶有觸發(fā)器的表進行insert操作(cāozuò)的時候，新的記錄不但添加進表中而且會復(fù)制到inserted表中。而進行update操作(cāozuò)的時候，表中原有記錄被更新，而且更新后的記錄會被存儲在inserted表中。共八十五頁怎樣(zěnyàng)看到inserted,deleted表？CREATETRIGGERtr1ON銷售(xiāoshòu)FORINSERT,UPDATE,DELETEASSelect*frominsertedSelect*fromdeletedGo共八十五頁臨時(línshí)表的應(yīng)用請使用觸發(fā)器實現(xiàn)(shíxiàn)庫存表中商品名稱修改，銷售表中上品名稱也跟著修改。修改前修改后共八十五頁臨時(línshí)表的應(yīng)用CREATETRIGGER修改姓名ON庫存afterupdateASDECLARE@spmc1varchar(10),@spmc2varchar(10)SELECT@spmc1=商品名稱FROMdeleted--從deleted表得到被刪除的原商品名稱SELECT@spmc2=商品名稱FROMinserted--從inserted表得到被更新(gēngxīn)的新商品名稱UPDATE庫存SET商品名稱=@spmc2WHERE商品名稱=@spmc1UPDATE銷售SET商品名稱=@spmc2WHERE商品名稱=@spmc1共八十五頁練習(xí)(liànxí)請使用觸發(fā)器實現(xiàn)(shíxiàn)庫存表和銷售表之間的級聯(lián)刪除操作。共八十五頁解答(jiědá)Createtrigger級聯(lián)刪除on庫存(kùcún)AfterdeleteAsDeclare@商品名稱char(10)Select@商品名稱=商品名稱fromdeletedDeletefrom銷售Where商品名稱=@商品名稱Go共八十五頁IFUPDATE(column)參數(shù)(cānshù)的使用

IFUPDATE(column)測試在指定的列上進行(jìnxíng)的INSERT或UPDATE操作，不能用于DELETE操作。

例：創(chuàng)建觸