大學(xué)課件-sql-server-2008案例教程及實(shí)訓(xùn)

第10章存儲(chǔ)過(guò)程10.1存儲(chǔ)過(guò)程簡(jiǎn)介10.2界面中操作存儲(chǔ)過(guò)程10.3用代碼操作存儲(chǔ)過(guò)程

10.1存儲(chǔ)過(guò)程簡(jiǎn)介10.1.1存儲(chǔ)過(guò)程的概念 在數(shù)據(jù)庫(kù)系統(tǒng)中，存儲(chǔ)過(guò)程（storedProcedure，簡(jiǎn)寫為sproc）的作用很重要。 存儲(chǔ)過(guò)程包含一組T-SQL語(yǔ)句，這組語(yǔ)句包含一些固定的操作，可以實(shí)現(xiàn)某個(gè)具體任務(wù)。它被SQLServer服務(wù)器集中編譯處理后，存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，可以接受輸入?yún)?shù)、輸出參數(shù)、返回狀態(tài)值和嵌套調(diào)用。10.1存儲(chǔ)過(guò)程簡(jiǎn)介10.1.2存儲(chǔ)過(guò)程分類存儲(chǔ)過(guò)程可以分為： 系統(tǒng)存儲(chǔ)過(guò)程、擴(kuò)展存儲(chǔ)過(guò)程、用戶自定義存儲(chǔ)過(guò)程3種類型。10.1.2存儲(chǔ)過(guò)程分類（1）系統(tǒng)存儲(chǔ)過(guò)程： 以sp_開(kāi)頭，由SQLServer內(nèi)建，存儲(chǔ)在master數(shù)據(jù)庫(kù)中。系統(tǒng)存儲(chǔ)過(guò)程主要從系統(tǒng)表中查詢信息或完成與更新數(shù)據(jù)庫(kù)表相關(guān)的管理任務(wù)或其他的系統(tǒng)管理任務(wù)。系統(tǒng)存儲(chǔ)過(guò)程可以在任何一個(gè)數(shù)據(jù)庫(kù)中執(zhí)行。下面列出一些常見(jiàn)的系統(tǒng)存儲(chǔ)過(guò)程：sp_helptext 用于顯示存儲(chǔ)過(guò)程的定義文本sp_rename 用于修改當(dāng)前數(shù)據(jù)庫(kù)中用戶對(duì)象的名稱sp_renamedb用于修改指定數(shù)據(jù)庫(kù)的名稱10.1.2存儲(chǔ)過(guò)程分類（2）擴(kuò)展存儲(chǔ)過(guò)程 擴(kuò)展存儲(chǔ)過(guò)程是在SQLServer環(huán)境之外執(zhí)行的動(dòng)態(tài)鏈接庫(kù)，可以擴(kuò)展SQLServer的功能，通常以xp_開(kāi)頭。使用擴(kuò)展存儲(chǔ)過(guò)程時(shí)，需要先加載到SQLServer系統(tǒng)中，并按照存儲(chǔ)過(guò)程的方式執(zhí)行。（3）用戶自定義存儲(chǔ)過(guò)程 用戶自定義存儲(chǔ)過(guò)程是指在用戶數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程，可以完成特定的數(shù)據(jù)庫(kù)操作任務(wù)，其前綴不能是sp_。

本章后面講的主要是用戶自定義的存儲(chǔ)過(guò)程。10.2界面中操作存儲(chǔ)過(guò)程1.簡(jiǎn)單的存儲(chǔ)過(guò)程簡(jiǎn)單存儲(chǔ)過(guò)程直接完成一個(gè)程序段的運(yùn)行，不含任何輸入輸出參數(shù)和返回值。【例10.1】使用SQLServerManagementStudio圖形化工具創(chuàng)建一個(gè)簡(jiǎn)單的沒(méi)有參數(shù)的存儲(chǔ)過(guò)程proc_AllBooks，在數(shù)據(jù)庫(kù)中查詢BookInfo表(表4-7)中所有圖書信息。10.2界面中操作存儲(chǔ)過(guò)程2.含輸入?yún)?shù)的存儲(chǔ)過(guò)程【例10.2】使用SQLServerManagementStudio圖形化工具創(chuàng)建一個(gè)含輸入?yún)?shù)的存儲(chǔ)過(guò)程proc_CheapBooks，要求查詢BookInfo表(表4-7)中價(jià)格低于30元的所有圖書信息。

10.2.2執(zhí)行存儲(chǔ)過(guò)程【例10.3】使用SQLServerManagementStudio圖形化工具執(zhí)行簡(jiǎn)單的存儲(chǔ)過(guò)程proc_AllBooks。

(1)在SSMS中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，展開(kāi)“圖書借閱信息管理系統(tǒng)”—“可編程性”—“存儲(chǔ)過(guò)程”，找到proc_AllBooks，右鍵選擇“執(zhí)行存儲(chǔ)過(guò)程”。(2)在彈出的“執(zhí)行過(guò)程”對(duì)話框中，選擇“確定”，即執(zhí)行了存儲(chǔ)過(guò)程。需要說(shuō)明的是，因?yàn)閜roc_AllBooks存儲(chǔ)過(guò)程，不含任何參數(shù)，所以在彈出的“執(zhí)行過(guò)程”對(duì)話框中沒(méi)有需要填寫的參數(shù)信息（圖10-9）。執(zhí)行后的結(jié)果如圖10.10。10.2.2執(zhí)行存儲(chǔ)過(guò)程【例10.4】使用SQLServerManagementStudio圖形化工具執(zhí)行含參數(shù)的存儲(chǔ)過(guò)程proc_CheapBooks。

(1)在SSMS中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，展開(kāi)“圖書借閱信息管理系統(tǒng)”—“可編程性”—“存儲(chǔ)過(guò)程”，找到proc_CheapBooks，右鍵選擇“執(zhí)行存儲(chǔ)過(guò)程”。(2)在彈出的“執(zhí)行過(guò)程”對(duì)話框中（如圖10-11），有一個(gè)參數(shù)@value，需要設(shè)置它的值，此處以30為例，即表示“查詢價(jià)格在30元以下的書籍信息”（此處可參照例10.2創(chuàng)建的存儲(chǔ)過(guò)程）。選擇“確定”，即執(zhí)行了存儲(chǔ)過(guò)程。執(zhí)行后的結(jié)果如圖10-12。10.2.2執(zhí)行存儲(chǔ)過(guò)程【例10.4】使用SQLServerManagementStudio圖形化工具執(zhí)行含參數(shù)的存儲(chǔ)過(guò)程proc_CheapBooks。

(1)在SSMS中，展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，展開(kāi)“圖書借閱信息管理系統(tǒng)”—“可編程性”—“存儲(chǔ)過(guò)程”，找到proc_CheapBooks，右鍵選擇“執(zhí)行存儲(chǔ)過(guò)程”。(2)在彈出的“執(zhí)行過(guò)程”對(duì)話框中（如圖10-11），有一個(gè)參數(shù)@value，需要設(shè)置它的值，此處以30為例，即表示“查詢價(jià)格在30元以下的書籍信息”（此處可參照例10.2創(chuàng)建的存儲(chǔ)過(guò)程）。選擇“確定”，即執(zhí)行了存儲(chǔ)過(guò)程。執(zhí)行后的結(jié)果如圖10-12。10.3用代碼操作存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程是最常見(jiàn)的方法，可以使用CREATEPROC關(guān)鍵詞來(lái)實(shí)現(xiàn)。其語(yǔ)法格式如下：createproc[edure]

存儲(chǔ)過(guò)程名[{@參數(shù)數(shù)據(jù)類型}[=默認(rèn)值][output][,...n][withencryption]as{sql語(yǔ)句}10.3用代碼操作存儲(chǔ)過(guò)程1.簡(jiǎn)單的存儲(chǔ)過(guò)程【例10.7】使用T-SQL語(yǔ)句創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程proc_AllReaders，要求查詢ReaderCard表(表4-9)中所有讀者信息。use圖書借閱信息管理系統(tǒng)gocreateprocproc_AllReadersasselect*fromReaderCard10.3用代碼操作存儲(chǔ)過(guò)程2.含輸入?yún)?shù)的存儲(chǔ)過(guò)程 含參數(shù)的存儲(chǔ)過(guò)程，目的是通過(guò)使用參數(shù)向存儲(chǔ)過(guò)程輸入和輸出信息，從而擴(kuò)展存儲(chǔ)過(guò)程的功能?！纠?0.8】使用T-SQL語(yǔ)句創(chuàng)建一個(gè)含輸入?yún)?shù)的存儲(chǔ)過(guò)程proc_MorFReaders，要求查詢ReaderCard表(表4-9)中指定性別的讀者信息。createprocproc_MorFReaders@sexnchar(1) --輸入?yún)?shù)asselect*fromReaderCardwhere性別=@sex --使用輸入?yún)?shù)10.3用代碼操作存儲(chǔ)過(guò)程3.含輸出參數(shù)的存儲(chǔ)過(guò)程【例10.9】使用T-SQL語(yǔ)句創(chuàng)建一個(gè)含輸出參數(shù)的存儲(chǔ)過(guò)程proc_ReadersNum，要求查詢ReaderCard表(表4-9)中指定性別的讀者數(shù)量，并返回?cái)?shù)量的值。createprocproc_ReadersNum@sexnchar(1)='男',--輸入?yún)?shù)，并指定了默認(rèn)值@numintoutput --輸出參數(shù)asselect@num=count(*)fromReaderCard --輸出參數(shù)被賦值where性別=@sex --使用輸入?yún)?shù)10.3用代碼操作存儲(chǔ)過(guò)程4.含有參數(shù)和返回值的存儲(chǔ)過(guò)程【例10.10】使用T-SQL語(yǔ)句創(chuàng)建一個(gè)含返回值的存儲(chǔ)過(guò)程proc_ReadersNumInfo，要求查詢ReaderCard表(表4-9)中指定性別的讀者數(shù)量，并返回讀者人數(shù)信息。createprocproc_ReadersNumInfo@famalenchar(1)='女', @malenchar(1)='男', --輸入?yún)?shù)@f_numintoutput ,@m_numintoutput --輸出參數(shù)10.3用代碼操作存儲(chǔ)過(guò)程4.含有參數(shù)和返回值的存儲(chǔ)過(guò)程【例10.10】使用T-SQL語(yǔ)句創(chuàng)建一個(gè)含返回值的存儲(chǔ)過(guò)程proc_ReadersNumInfo，要求查詢ReaderCard表(表4-9)中指定性別的讀者數(shù)量，并返回讀者人數(shù)信息。asdeclare@nint

--聲明返回值set@n=1--為返回值賦值select@f_num=count(*)fromReaderCard --輸出參數(shù)被賦值where性別=@famale --使用輸入?yún)?shù)select@m_num=count(*)fromReaderCard --輸出參數(shù)被賦值where性別=@male --使用輸入?yún)?shù)if(@f_num>@m_num)set@n=0elseset@n=1return@n10.3.2執(zhí)行存儲(chǔ)過(guò)程【例10.12】使用T-SQL語(yǔ)句執(zhí)行簡(jiǎn)單的存儲(chǔ)過(guò)程proc_AllReaders，顯示所有讀者的信息。Execproc_AllReaders10.3.2執(zhí)行存儲(chǔ)過(guò)程【例10.13】使用T-SQL語(yǔ)句執(zhí)行含輸入?yún)?shù)的存儲(chǔ)過(guò)程proc_MorFReaders，顯示所有男讀者的信息。Execproc_AllReaders'男'

10.3.2執(zhí)行存儲(chǔ)過(guò)程【例10.14】使用T-SQL語(yǔ)句執(zhí)行含輸出參數(shù)的存儲(chǔ)過(guò)程proc_ReadersNum，顯示所有男讀者的數(shù)量。（如圖10.16）declare@numintExecproc_ReadersNum'男',@numoutputprint'男讀者的數(shù)量:'+cast(@numaschar(10))10.3.2執(zhí)行存儲(chǔ)過(guò)程【例10.15】使用T-SQL語(yǔ)句執(zhí)行含返回值及輸入輸出參數(shù)的存儲(chǔ)過(guò)程proc_ReadersNumInfo，顯示讀者的數(shù)量信息。declare@Fnumint,@Mnumint,@StrintExec@Str=proc_ReadersNumInfodefault,default,@Fnumoutput,@Mnumoutputprint'男讀者的數(shù)量:'+cast(@Mnumaschar(10))print'女讀者的數(shù)量:'+cast(@Fnumaschar(10))if@str=0print'女讀者多'elseprint'女讀者不多于男讀者'10.3.2執(zhí)行存儲(chǔ)過(guò)程【例10.16】使用Exec語(yǔ)句執(zhí)行字符串實(shí)例。declare@strvarchar(40)set@str='select*fromReaderCard'Exec(@str)注意，Exec執(zhí)行字符串時(shí)，后面的括號(hào)不能省略。10.3.3查看存儲(chǔ)過(guò)程1.查看存儲(chǔ)過(guò)程的定義文本

EXECsp_helptext存儲(chǔ)過(guò)程名2.查看存儲(chǔ)過(guò)程的依賴關(guān)系

EXECsp_depends存儲(chǔ)過(guò)程名3.查看存儲(chǔ)過(guò)程的參數(shù)

EXECsp_help存儲(chǔ)過(guò)程名4.查看數(shù)據(jù)庫(kù)中全部存儲(chǔ)過(guò)程

EXECsp_stored_procedures10.3.3查看存儲(chǔ)過(guò)程【例10.17】使用相關(guān)的系統(tǒng)存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程proc_ReadersNumInfo的定義、相關(guān)性和參數(shù)。EXECsp_helptextproc_ReadersNumInfo--查看存儲(chǔ)過(guò)程的定義EXECsp_dependsproc_ReadersNumInfo--查看存儲(chǔ)過(guò)程的相關(guān)性EXECsp_helpproc_ReadersNumInfo --查看存儲(chǔ)過(guò)程的參數(shù)EXECsp_stored_procedures --查看全部存儲(chǔ)過(guò)程10.3.4修改和刪除存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程的語(yǔ)法如下：alterproc[edure]存儲(chǔ)過(guò)程名[{@參數(shù)數(shù)據(jù)類型}[=默認(rèn)值][output][,...n][withencryption]as{sql語(yǔ)句}10.3.4修改和刪除存儲(chǔ)過(guò)程【例10.18】使用T-SQL語(yǔ)句修改存儲(chǔ)過(guò)程proc_ReadersNum，能夠顯示不同類型的讀者信息，并加密。執(zhí)行存儲(chǔ)過(guò)程，顯示學(xué)生讀者的信息。alterprocproc_ReadersNum@類型varchar(50)=‘Student’, --輸入?yún)?shù)@numintoutput --輸出參數(shù)WITHENCRYPTION--加密asselect@num=count(*)fromReaderCard --輸出參數(shù)被賦值where卡類型=@類型--使用輸入?yún)?shù)10.3.4修改和刪除存儲(chǔ)過(guò)程【例10.18】使用T-SQL語(yǔ)句修改存儲(chǔ)過(guò)程proc_ReadersNum，能夠顯示不同類型的讀者信息，并加密。執(zhí)行存儲(chǔ)過(guò)程，顯示學(xué)生讀者的信息。執(zhí)行存儲(chǔ)過(guò)程，顯示學(xué)生讀者的信息：declare@numintExecproc_ReadersNum'Student',@numoutputprint'學(xué)生讀者的數(shù)量:'+cast(@numaschar(10))8.3.3索引的刪除刪除存儲(chǔ)過(guò)程的語(yǔ)法如下：【例10.19】刪除存儲(chǔ)過(guò)程proc_ReadersNum。dropprocproc_ReadersNumdropproc[edure]存儲(chǔ)過(guò)程名第11章觸發(fā)器觸發(fā)器簡(jiǎn)介11.1

界面中創(chuàng)建和使用觸發(fā)器11.2用代碼創(chuàng)建和使用觸發(fā)器11.3

與存儲(chǔ)過(guò)程類似，觸發(fā)器也是SQL語(yǔ)句集，兩者惟一的區(qū)別是觸發(fā)器不能用EXECUTE語(yǔ)句調(diào)用，而是在用戶執(zhí)行Transact-SQL語(yǔ)句時(shí)自動(dòng)觸發(fā)（激活）執(zhí)行，所以我們觸發(fā)器就是一種特殊的存儲(chǔ)過(guò)程。

觸發(fā)器是一個(gè)在修改指定表中的數(shù)據(jù)時(shí)執(zhí)行的存儲(chǔ)過(guò)程。經(jīng)常通過(guò)創(chuàng)建觸發(fā)器來(lái)強(qiáng)制實(shí)現(xiàn)不同表中的邏輯相關(guān)數(shù)據(jù)的引用完整性或者一致性。當(dāng)對(duì)某一表進(jìn)行諸如UPDATE、INSERT、DELETE

這些操作時(shí)，SQLServer就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句11.1觸發(fā)器簡(jiǎn)介11.1.1觸發(fā)器的概念11.1.2觸發(fā)器的類型

觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，在語(yǔ)言事件發(fā)生時(shí)，所設(shè)置的觸發(fā)器就會(huì)自動(dòng)被執(zhí)行，以進(jìn)行維護(hù)數(shù)據(jù)完整性或其他一些特殊的任務(wù)。與上一章介紹的一般意義的存儲(chǔ)過(guò)程不同，觸發(fā)器是當(dāng)發(fā)生UPDATE、INSERT、DELETE這些操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程。不能直接被調(diào)用，也不能傳遞或接受參數(shù)。11.1.1觸發(fā)器的概念11.1.2觸發(fā)器的類型1．類型

根據(jù)觸發(fā)事件的不同，將觸發(fā)器分成兩大類：DML觸發(fā)器和DDL觸發(fā)器。（1）DML觸發(fā)器。DML觸發(fā)器是一種與表緊密關(guān)聯(lián)的特殊的存儲(chǔ)過(guò)程，當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言（DML）事件（Insert、Update、Delete）時(shí)將調(diào)用DML觸發(fā)器?！馎FTER觸發(fā)器●INSTEADOF觸發(fā)器

（2）DDL觸發(fā)器。DDL觸發(fā)器是在響應(yīng)數(shù)據(jù)定義語(yǔ)言（DDL）語(yǔ)句時(shí)激發(fā)。 這些語(yǔ)句主要是以CREATE、ALTER和DROP開(kāi)頭的語(yǔ)句。DDL觸發(fā)器可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫(kù)操作。功能after觸發(fā)器insteadof觸發(fā)器適用對(duì)象表表和視圖每個(gè)表或視圖可用的數(shù)量允許每個(gè)動(dòng)作有多個(gè)觸發(fā)器每個(gè)動(dòng)作（update、delete和insert）一個(gè)觸發(fā)器級(jí)聯(lián)引用沒(méi)有限制在作為級(jí)聯(lián)引用完整性約束目標(biāo)的表上限制應(yīng)用執(zhí)行時(shí)機(jī)聲明引用動(dòng)作之后在約束處理之前，代替了觸發(fā)動(dòng)作在創(chuàng)建inserted表和deleted表觸發(fā)時(shí)在inserted表和deleted表創(chuàng)建之后執(zhí)行順序可以指定第一個(gè)和最后一個(gè)觸發(fā)器執(zhí)行動(dòng)作不適用在inserted表和deleted表引用text、ntext和image類型的數(shù)據(jù)不允許允許（1）DML觸發(fā)器在建立DML觸發(fā)器時(shí)，①要指定觸發(fā)操作：insert、update或delete。至少要指定一種操作，也可以同時(shí)指定多種。②在同一個(gè)表中可以創(chuàng)建多個(gè)after觸發(fā)器，但在表或視圖上，每個(gè)insert、update或delete語(yǔ)句最多可以定義一個(gè)insteadof觸發(fā)器。（2）DDL觸發(fā)器DDL觸發(fā)器是響應(yīng)create、alter、drop、grant、deny、revoke和updatestatistics等語(yǔ)句而觸發(fā)的。

11.2.1創(chuàng)建觸發(fā)器11.2.2修改觸發(fā)器11.2.3刪除觸發(fā)器11.2界面中創(chuàng)建和使用觸發(fā)器【例11.1】創(chuàng)建一個(gè)簡(jiǎn)單的DML觸發(fā)器，當(dāng)讀者完成借書手續(xù)后，系統(tǒng)中的庫(kù)存數(shù)量要及時(shí)更新。11.2.1創(chuàng)建觸發(fā)器

圖11.1新建觸發(fā)器圖11.2新建觸發(fā)器模板

圖11.3“指定模板參數(shù)的值”對(duì)話框圖11.4觸發(fā)器代碼11.2.2修改觸發(fā)器

圖11.5修改觸發(fā)器圖11.6“修改觸發(fā)器代碼”窗口11.2.3刪除觸發(fā)器

在“對(duì)象資源管理器”中，展開(kāi)數(shù)據(jù)庫(kù)目錄，找到需要修改觸發(fā)器的表，展開(kāi)表目錄，展開(kāi)觸發(fā)器節(jié)點(diǎn)目錄，右擊需要?jiǎng)h除的觸發(fā)器，如圖11.5所示，單擊快捷菜單中的“刪除”命令。

用CREATETRIGGER語(yǔ)句創(chuàng)建DML觸發(fā)器，要注意的是該語(yǔ)句必須是批處理中的第一條語(yǔ)句，并且只能應(yīng)用于一個(gè)表。CREATETRIGGER語(yǔ)句的部分語(yǔ)法格式如下：11.3使用T-SQL創(chuàng)建和使用觸發(fā)器createtrigger

觸發(fā)器名稱on{表名|視圖}{for|after|insteadof}{[insert][,][update][,][delete]}as

ifupdate(列名)[and|orupdate(列名)][…n]

sql語(yǔ)句

[…n]DML觸發(fā)器創(chuàng)建的表觸發(fā)動(dòng)作創(chuàng)建inserted表創(chuàng)建deleted表insert是否update是是delete否是Sell表銷售編號(hào)商品編號(hào)數(shù)量售出時(shí)間售貨員工編號(hào)1212004-10-1513012212004-10-1613023522004-10-2613034612005-3-201301inserted表銷售編號(hào)商品編號(hào)數(shù)量售出時(shí)間售貨員工編號(hào)5722005-1-31301deleted表銷售編號(hào)商品編號(hào)數(shù)量售出時(shí)間售貨員工編號(hào)5722005-1-313015722005-1-31301insertsellvalues(’7',2,'2005-1-3','1303')updatesellset數(shù)量=22where銷售編號(hào)=52257222005-1-31301t_score表S_numberC_numberScorecredit040101188NULL040101592NULL040101685NULL040101788NULLdeleted表S_numberC_numberScorecredit040102273NULL040102273NULLDeleteformt_scorewhereS_number='040102'【例1】創(chuàng)建一個(gè)簡(jiǎn)單的DML替代觸發(fā)器，當(dāng)有人試圖更新BookInfo表的信息時(shí)，利用觸發(fā)器產(chǎn)生提示信息并阻止動(dòng)作。USE圖書借閱信息數(shù)據(jù)庫(kù)GOCREATETRIGGERtri_UpdateBooksONBookInfoInsteadOfUPDATEAS

Print‘更新BookInfo表的數(shù)據(jù)'--測(cè)試觸發(fā)器UPDATEBookInfoSET作者=‘譚宇之’WHERE圖書名稱=‘電腦樂(lè)園’消息如下：更新BookInfo表的數(shù)據(jù)改為后觸發(fā)：Insteadof改為For【例2】創(chuàng)建級(jí)聯(lián)刪除的觸發(fā)器，當(dāng)有人試圖刪除BookInfo表的信息時(shí)，利用觸發(fā)器級(jí)聯(lián)刪除與之相關(guān)的BorrowList表中的信息。CREATETRIGGERtriDelete_BookONBookInfoFORDELETEASBEGIN DELETEFROMBorrowList

WHERE圖書編號(hào)in(select圖書編號(hào)fromdeleted)END--測(cè)試觸發(fā)器DeleteFormBookInfoWHERE圖書編號(hào)=‘1008-2352’消息如下：*行受影響【例3】創(chuàng)建一個(gè)AFTERINSERT觸發(fā)器，當(dāng)在ReaderCard表中插入一條新記錄時(shí)，如果卡類型不是“Student、Teacher、Worker、Manager或other”時(shí)，則撤銷該插入操作，并返回出錯(cuò)消息。CREATETRIGGERtri_InsertReaderONReaderCardFORINSERTASDECLARE@typevarchar(16)--聲明變量SELECT@type=卡類型FROMinsertedif@typenotin('Student','Teacher','Worker','Manager','other')begin

rollbacktransaction--返回用戶定義的錯(cuò)誤信息，事務(wù)回滾

print'不能插入不合法類型！'end--測(cè)試觸發(fā)器insertintoReaderCardvalues(‘12121212’,‘章一',‘男’,‘1985-8-8’,‘2003-9-9’,‘正?！?‘卡類型')【例4】在BookInfo表和BorrowList表之間具有邏輯上的主外鍵關(guān)系，要求當(dāng)刪除或更新圖書信息的時(shí)候，要激發(fā)觸發(fā)器tri_Update_Delete，并在BorrowList表中刪除或更新相對(duì)應(yīng)的記錄行。CREATETRIGGERtri_Update_DeleteONBookInfoFOR

UPDATE,DELETEASSETNOCOUNTOFF--不返回結(jié)果

DECLARE@delcountINTDECLARE@BookNoCHAR(6)-------------------更新（級(jí)聯(lián)更新）---------------------IFUPDATE(圖書編號(hào))BEGINUPDATEBorrowListSET圖書編號(hào)=(SELECT圖書編號(hào)FROMinserted)WHERE圖書編號(hào)in(SELECT圖書編號(hào)FROMdeleted)END----------------刪除（級(jí)聯(lián)刪除）------------------------SELECT@delcount=COUNT(*)FROMdeletedIF@delcount>0BEGIN

--從臨時(shí)表deleted中獲取要?jiǎng)h除的圖書編號(hào)

SELECT@BookNo=圖書編號(hào)FROMdeleted

--從Sell表中刪除該員工的銷售記錄

DELETEFROMBorrowListWHERE圖書編號(hào)=@BookNoEND【例11.2】創(chuàng)建一個(gè)簡(jiǎn)單的DML觸發(fā)器，當(dāng)讀者還書的時(shí)候，計(jì)算其逾期罰款。CreateTRIGGER[dbo].[update_up]

ON[dbo].[BorrowList]

AFTERUPDATEASifupdate(實(shí)際歸還時(shí)間)declare@應(yīng)還書datetimedeclare@實(shí)際還書datetimeselect@應(yīng)還書=BorrowList.應(yīng)還書時(shí)間fromBorrowList,insertedwhereBorrowList.借閱編號(hào)=inserted.借閱編號(hào)select@實(shí)際還書=BorrowList.實(shí)際歸還時(shí)間fromBorrowList,insertedwhereBorrowList.借閱編號(hào)=inserted.借閱編號(hào)…………if(datediff(dd,@應(yīng)還書,@實(shí)際還書)<0)BEGINupdateBorrowlistset逾期罰款=0where借閱編號(hào)in(select借閱編號(hào)fromdeleted)ENDelseBEGINupdateBorrowlistset逾期罰款=datediff(dd,應(yīng)還書時(shí)間,實(shí)際歸還時(shí)間)*0.1where借閱編號(hào)in(select借閱編號(hào)fromdeleted)END--測(cè)試觸發(fā)器updateBorrowListset實(shí)際歸還時(shí)間='2013-08-23'where借閱編號(hào)='9'11.3.2修改觸發(fā)器

altertrigger[所有者.]觸發(fā)器名稱on{[所有者.]表名|視圖}{for|after|insteadof}{[insert][,][update][,][delete]}[notforreplication]as

ifupdate(列名)[and|orupdate(列名)][…n]sql語(yǔ)句[…n]【例11.3】修改一個(gè)DML觸發(fā)器，當(dāng)讀者還書的時(shí)候，庫(kù)存更新。ALTERTRIGGER[dbo].[insert_old]ON[dbo].[BorrowList]AFTERupdateASifupdate(實(shí)際歸還時(shí)間)BEGINupdatebookinfoset庫(kù)存量=庫(kù)存量+1where圖書編號(hào)in(selectBorrowList.圖書編號(hào)fromBorrowList,insertedwhereBorrowList.借閱編號(hào)=inserted.借閱編號(hào))END11.3.3刪除觸發(fā)器

DROPTRIGGERUpdate_up【例11.4】刪除一個(gè)名為Update_up的觸發(fā)器。11.3.4管理觸發(fā)器sp_helptextupdate_up1．查看觸發(fā)器 所有適用于存儲(chǔ)過(guò)程的管理方式都適用于觸發(fā)器?？梢允褂孟駍p_helptext、sp_help和sp_depends

等系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看觸發(fā)器的有關(guān)信息，也可以使用sp_rename系統(tǒng)存儲(chǔ)過(guò)程來(lái)重命名觸發(fā)器。【例11.5】使用sp_helptext系統(tǒng)存儲(chǔ)過(guò)程可以查看觸發(fā)器的定義語(yǔ)句。EXECsp_helptext'觸發(fā)器名稱'查看觸發(fā)器的定義文本。EXECsp_help'觸發(fā)器名稱'查看觸發(fā)器的所有者和創(chuàng)建日期。

EXECsp_rename‘原觸發(fā)器名稱’，‘新觸發(fā)器名稱’重命名觸發(fā)器。2.禁用觸發(fā)器 禁用觸發(fā)器只是暫時(shí)關(guān)系觸發(fā)器，而不會(huì)刪除它。該觸發(fā)器任然作為數(shù)據(jù)庫(kù)的對(duì)象存在于當(dāng)前數(shù)據(jù)庫(kù)中，但是執(zhí)行任何T-SQL語(yǔ)句時(shí)，不會(huì)激發(fā)此觸發(fā)器?！纠?1.6】使用DISABLETRIGGER命令可以禁用觸發(fā)器update_up觸發(fā)器。ALTERTABLEborrowlistDISABLETRIGGERupdate_up

3.啟用觸發(fā)器啟用觸發(fā)器恢復(fù)被禁用的觸發(fā)器，讓它起到應(yīng)有的作用?！纠?1.7】使用ENABLETRIGGER命令可以啟用觸發(fā)器update_up觸發(fā)器。ALTERTABLEborrowlistENABLETRIGGERupdate_up

觸發(fā)器是一種與數(shù)據(jù)庫(kù)和表相結(jié)合的特殊的存儲(chǔ)過(guò)程，SQLServer2008有兩類觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。當(dāng)表有INSERT、UPDATE、DELETE操作影響到觸發(fā)器所保護(hù)的數(shù)據(jù)時(shí)，DML觸發(fā)器就會(huì)自動(dòng)觸發(fā)執(zhí)行其中的T-SQL語(yǔ)句。本章小結(jié)

一般在使用DML觸發(fā)器之前應(yīng)優(yōu)先考慮使用約束，只在必要的時(shí)候才使用DML觸發(fā)器。觸發(fā)器主要用于加強(qiáng)業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性。通過(guò)本章的學(xué)習(xí)，應(yīng)掌握DML和DDL觸發(fā)器的創(chuàng)建、修改和刪除。數(shù)據(jù)庫(kù)管理系統(tǒng)主講：呂樹(shù)紅一、數(shù)據(jù)庫(kù)是什么？有什么用處？二、常見(jiàn)的數(shù)據(jù)庫(kù)系統(tǒng)有哪些？三、學(xué)習(xí)SQLServer后再轉(zhuǎn)為其他數(shù)據(jù)庫(kù)難不難？數(shù)據(jù)庫(kù)是什么？有什么用處？按照數(shù)據(jù)結(jié)構(gòu)來(lái)組織、存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)。確切說(shuō)他包括兩部分，一部分是數(shù)據(jù)庫(kù)，即存放數(shù)據(jù)的物理空間；第二部分是數(shù)據(jù)庫(kù)管理系統(tǒng)，即用于管理數(shù)據(jù)的核心軟件就像是人的大腦。DB2、Oracle、SQLServer、mySQL、Access、VFP、Sybase二、常見(jiàn)的數(shù)據(jù)庫(kù)系統(tǒng)有哪些？三、學(xué)習(xí)SQLServer后再轉(zhuǎn)為其他數(shù)據(jù)庫(kù)難不難？第1章數(shù)據(jù)庫(kù)的概述與安裝本章內(nèi)容：

數(shù)據(jù)庫(kù)概述1.1SQLServer2008的安裝1.2管理SQLServer2008服務(wù)器1.31.1數(shù)據(jù)庫(kù)概述

SQLServer是由Microsoft公司開(kāi)發(fā)和推廣的高性能的C/S結(jié)構(gòu)的關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS），最初由Microsoft、Sybase和Ashton-Tate3家公司共同開(kāi)發(fā)1988年推出第一個(gè)版本1990年，Ashton-Tate(安信達(dá))公司退出開(kāi)發(fā)1992年，SQLServer被移植到WindowsNT平臺(tái)上1994年兩家公司分別開(kāi)發(fā)，Microsoft公司專注于開(kāi)發(fā)和推廣SQLServer的WindowsNT版本;而Sybase公司則專注于SQLServer在UNIX操作系統(tǒng)上的應(yīng)用。1.1.1數(shù)據(jù)庫(kù)技術(shù)的發(fā)展史1、人工管理階段2、文件系統(tǒng)管理階段3、數(shù)據(jù)庫(kù)系統(tǒng)管理階段1.1.2數(shù)據(jù)庫(kù)系統(tǒng)的概念1.信息2.數(shù)據(jù)3.數(shù)據(jù)庫(kù)4.數(shù)據(jù)庫(kù)管理系統(tǒng)5.數(shù)據(jù)庫(kù)系統(tǒng)《305-12199-9，SQLServer2008案例教程及實(shí)訓(xùn)，呂樹(shù)紅，南京大學(xué)出版社，2013.9》數(shù)據(jù)庫(kù)是什么？有什么用處？按照數(shù)據(jù)結(jié)構(gòu)來(lái)組織、存儲(chǔ)和管理數(shù)據(jù)的倉(cāng)庫(kù)。確切說(shuō)他包括兩部分，一部分是數(shù)據(jù)庫(kù)，即存放數(shù)據(jù)的物理空間；第二部分是數(shù)據(jù)庫(kù)管理系統(tǒng)，即用于管理數(shù)據(jù)的核心軟件就像是人的大腦。數(shù)據(jù)庫(kù)管理系統(tǒng)：是一個(gè)系統(tǒng)軟件，對(duì)數(shù)據(jù)庫(kù)進(jìn)行管理和控制，它就像一個(gè)倉(cāng)庫(kù)的管理員，主要負(fù)責(zé)維護(hù)數(shù)據(jù)之間的關(guān)系，恢復(fù)系統(tǒng)崩潰時(shí)候被破壞的數(shù)據(jù)，及保證數(shù)據(jù)存儲(chǔ)的正確性。數(shù)據(jù)庫(kù)系統(tǒng)：定義：由數(shù)據(jù)庫(kù)、數(shù)據(jù)庫(kù)管理系統(tǒng)和用戶構(gòu)成的；如：圖書館：由圖書庫(kù)，圖書管理員和借書者共同構(gòu)成。1.1.3數(shù)據(jù)模型1.數(shù)據(jù)模型分析概念模型數(shù)據(jù)模型問(wèn)題：請(qǐng)你為本院的學(xué)生選課情況進(jìn)行數(shù)據(jù)分析，并創(chuàng)建數(shù)據(jù)模型。1.1.3數(shù)據(jù)模型2.常見(jiàn)數(shù)據(jù)模型關(guān)系模型1.1.3數(shù)據(jù)模型2.常見(jiàn)數(shù)據(jù)模型關(guān)系：二維表1.1.3數(shù)據(jù)模型2.常見(jiàn)數(shù)據(jù)模型行：記錄（元組）1.1.3數(shù)據(jù)模型2.常見(jiàn)數(shù)據(jù)模型列：字段（屬性）域：屬性取值范圍年齡的域：0~1501.1.3數(shù)據(jù)模型2.常見(jiàn)數(shù)據(jù)模型主關(guān)鍵字：主鍵（唯一標(biāo)識(shí)每條記錄）1.1.3數(shù)據(jù)模型2.常見(jiàn)數(shù)據(jù)模型關(guān)系的性質(zhì)：同一關(guān)系中，屬性名不能重復(fù)，但不同的屬性可以具有相同的值域。關(guān)系中的屬性必須是原子數(shù)據(jù)，即不可再分。關(guān)系中屬性的位置可任意交換。同一關(guān)系中，不能出現(xiàn)相同的元組。關(guān)系中元組的順序可任意交換。1.2.1安裝需求1.2.2安裝SQLServer20081.2.3安裝示例數(shù)據(jù)庫(kù)1.2SQLServer2008的安裝1．網(wǎng)絡(luò)軟件要求支持以下網(wǎng)絡(luò)協(xié)議：SharedmemoryNamedPipesTCP/IPVIA2．Internet要求 所有的SQLServer2008安裝都需要使用MicrosoftInternetExplorer6SP1或更高版本。3．軟件要求

SQLServer2008安裝程序安裝該產(chǎn)品時(shí)所需的軟件組件如下●.NETFramework2.0。●MicrosoftSQLServer本機(jī)客戶端●MicrosoftSQLServer安裝程序支持文件。 以微軟公司提供的免費(fèi)版本SQLServerExpress（32位）為例（1）硬盤空間要求（32位和64位）（2）其他計(jì)算機(jī)主要部件要求如表1.3所示4.硬件需求

SQLServer2008分為5個(gè)新的版本：企業(yè)版（EnterpriseEdition）、開(kāi)發(fā)人員版（DeveloperEdition）、標(biāo)準(zhǔn)版（StandardEdition）、工作組版（WorkgroupEdition）、簡(jiǎn)易版（ExpressEdition）。首先，確認(rèn)以管理員身份登錄，從而能夠在機(jī)器上創(chuàng)建文件和文件夾，這是成功安裝所必需的。1.2.2安裝SQLServer2008首次安裝可以用Windows身份驗(yàn)證安裝后在【開(kāi)始】菜單查看安裝的程序項(xiàng)在SQLServer2008中提供的示例數(shù)據(jù)庫(kù)是以一個(gè)虛擬的商業(yè)公司AdventureWorksCycles及其業(yè)務(wù)方案、雇員和產(chǎn)品作為示例數(shù)據(jù)庫(kù)的基礎(chǔ)，其中包括以下內(nèi)容?！馎dventureWorks示例OLTP數(shù)據(jù)庫(kù)?！馎dventureWorksDW示例數(shù)據(jù)庫(kù)?！馎dventureWorksAS示例分析數(shù)據(jù)庫(kù)。1.2.3安裝示例數(shù)據(jù)庫(kù)1.3管理SQLServer2008服務(wù)器1.3.1啟動(dòng)和停止服務(wù)器1.3.2注冊(cè)服務(wù)器1.3.3連接服務(wù)器

SSMS是SQLServer管理員與系統(tǒng)交互的主要工具。

SSMS是一個(gè)集成環(huán)境，用于訪問(wèn)、配置和管理所有SQLServer組件，并為數(shù)據(jù)庫(kù)管理人員提供了一個(gè)簡(jiǎn)單的實(shí)用工具，使數(shù)據(jù)庫(kù)管理人員能夠通過(guò)易用的圖形工具和豐富的腳本完成任務(wù)。SQLServerManagementStudio1.3.1啟動(dòng)和停止SQLServer服務(wù)器首先，運(yùn)行SQLServerManagementStudio(簡(jiǎn)稱SSMS,Win7系統(tǒng)中以管理員身份運(yùn)行）彈出‘連接到服務(wù)器’窗口……若服務(wù)器默認(rèn)未啟動(dòng)，則先選擇‘取消’，先去啟動(dòng)服務(wù)器若服務(wù)器已經(jīng)啟動(dòng)，則選擇“連接”，直接打開(kāi)數(shù)據(jù)庫(kù)服務(wù)器視圖—打開(kāi)‘已注冊(cè)的服務(wù)器’窗口，找到你的服務(wù)器，右鍵‘啟動(dòng)’，啟動(dòng)服務(wù)器。啟動(dòng)服務(wù)器后，圖標(biāo)變?yōu)榫G色三角1.3.2

注冊(cè)服務(wù)器1．添加服務(wù)器組2．注冊(cè)服務(wù)器在‘對(duì)象資源管理器’窗口選擇‘聯(lián)接對(duì)象資源管理器’1.3.3連接服務(wù)器彈出“連接到服務(wù)器”窗口……Windows身份驗(yàn)證，可直接登錄，即直接使用操作系統(tǒng)用戶驗(yàn)證；SQLServer身份驗(yàn)證，需要sa用戶名和密碼，是你在安裝時(shí)設(shè)置的……第一次，直接使用Windows身份驗(yàn)證身份驗(yàn)證啟動(dòng)后的SSMS界面如下：以后，就在這個(gè)界面下學(xué)習(xí)SQLServer2008了……用這種方法還可以停止、暫停、恢復(fù)和重新啟動(dòng)服務(wù)器。服務(wù)器菜單（1）啟動(dòng)SSMS:“開(kāi)始”→“所有程序”→“MicrosoftSQLServer2008”→“SQLServerManagementStudio”

SSMS基本操作SSMS系統(tǒng)數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)用圖形化界面操作數(shù)據(jù)表：選擇要打開(kāi)的表，右鍵‘打開(kāi)表’用T-SQL語(yǔ)句來(lái)查看表（8）接下來(lái)介紹一下查詢編輯器工具欄的常用按鈕。查詢編輯器工具欄微軟（MicrosoftCorporation）是一家基于美國(guó)的跨國(guó)電腦科技公司。以研發(fā)、制造、授權(quán)和提供廣泛的電腦軟件服務(wù)業(yè)務(wù)為主。總部位于美國(guó)華盛頓州，最為著名和暢銷的產(chǎn)品為MicrosoftWindows操作系統(tǒng)和MicrosoftOffice系列軟件。2001年10月25日，微軟公司發(fā)布了迄今為止最受歡迎的操作系統(tǒng)WindowsXP。

身份驗(yàn)證：身份驗(yàn)證模式是SQLServer2008管理系統(tǒng)用于驗(yàn)證客戶端連接服務(wù)器的方式。這個(gè)過(guò)程在SQLServer2008安裝步驟中出現(xiàn)過(guò)，需要選擇Windows驗(yàn)證模式或（Windows驗(yàn)證和SQLServer驗(yàn)證）混合驗(yàn)證模式。Windows驗(yàn)證模式Windows驗(yàn)證模式是指使用用戶登錄Windows操作系統(tǒng)時(shí)的身份進(jìn)行驗(yàn)證，SQLServer2008使用和Windows操作系統(tǒng)一樣用戶名和密碼，即使用操作系統(tǒng)的身份驗(yàn)證方式，不在登錄SQLServer2008時(shí)再次驗(yàn)證這是默認(rèn)更安全的身份驗(yàn)證模式，應(yīng)盡可能的使用Windows驗(yàn)證模式。SQLServer驗(yàn)證模式當(dāng)使用SQLServer身份驗(yàn)證時(shí)，還必須提供一個(gè)用于連接的(SQLServer)登錄名和密碼，登錄名并不基于Windows用戶帳戶。登錄名和密碼均通過(guò)使用SQLServer創(chuàng)建并存儲(chǔ)在SQLServer中。通過(guò)SQLServer身份驗(yàn)證進(jìn)行連接的用戶每次連接時(shí)必須提供其憑據(jù)（登錄名和密碼）?；旌夏Ｊ绞窃试S用戶使用Windows身份驗(yàn)證或SQLServer身份驗(yàn)證進(jìn)行連接。混合模式是兩中驗(yàn)證模式都開(kāi)啟，這需要在安裝數(shù)據(jù)庫(kù)時(shí)進(jìn)行選擇。重新設(shè)置身份驗(yàn)證模式在對(duì)象資源管理咨詢中，連接到所需要的數(shù)據(jù)庫(kù)引擎；用鼠標(biāo)右擊該實(shí)例，選擇“屬性”；在彈出的“服務(wù)器屬性”對(duì)話框中選擇“安全性”選項(xiàng)，出現(xiàn)服務(wù)器身份驗(yàn)證的選擇界面。注意，當(dāng)更改安全模式是，如果sa密碼為空，則必須設(shè)置sa密碼。舉例：新建“登錄名”lsh,用于訪問(wèn)數(shù)據(jù)庫(kù)gongzi.（以下操作在Windows身份驗(yàn)證下完成）(1)新建“登錄名”(2)填寫“登錄名”和“密碼”，選擇“默認(rèn)數(shù)據(jù)庫(kù)”(3)勾選“用戶映射”的數(shù)據(jù)庫(kù)，即用戶可以訪問(wèn)的數(shù)據(jù)庫(kù)。(4)狀態(tài)：授予、啟用(5)右鍵服務(wù)器——屬性：身份驗(yàn)證為SQLServer和Windows身份驗(yàn)證模式。(6)斷開(kāi)連接，重新登錄系統(tǒng)數(shù)據(jù)庫(kù) 系統(tǒng)數(shù)據(jù)庫(kù)中保存的系統(tǒng)表用于系統(tǒng)的總體控制、系統(tǒng)運(yùn)行及對(duì)用戶數(shù)據(jù)的操作等基本信息。 這些系統(tǒng)數(shù)據(jù)庫(kù)的文件存儲(chǔ)在SQLServer的默認(rèn)安裝目錄的MMSQL子目錄的Data文件夾中。第2章數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)簡(jiǎn)介2.1使用SSMS創(chuàng)建和管理數(shù)據(jù)庫(kù)2.2使用T-SQL創(chuàng)建和管理數(shù)據(jù)庫(kù)2.3數(shù)據(jù)庫(kù)的分離與附加2.4

數(shù)據(jù)庫(kù)是SQLServer用以存放數(shù)據(jù)和數(shù)據(jù)庫(kù)對(duì)象的容器。

表是最重要的數(shù)據(jù)庫(kù)對(duì)象，它是數(shù)據(jù)存儲(chǔ)的地方，其結(jié)構(gòu)和電子表格類似，由行和列組成。 SQLServer2008的數(shù)據(jù)庫(kù)，可以從邏輯和物理兩個(gè)角度進(jìn)行討論2.1數(shù)據(jù)庫(kù)簡(jiǎn)介2.2.1數(shù)據(jù)庫(kù)的邏輯結(jié)構(gòu)數(shù)據(jù)庫(kù)中的對(duì)象主要包括：（1）表（2）視圖（3）主鍵（4）外鍵（5）索引（6）約束（7）默認(rèn)值（8）規(guī)則（9）存儲(chǔ)過(guò)程（10）觸發(fā)器2.1.2數(shù)據(jù)庫(kù)的物理結(jié)構(gòu)---------3種數(shù)據(jù)庫(kù)文件

(1)主數(shù)據(jù)文件（.mdf）*

(2)次要數(shù)據(jù)文件(.ndf) (3)日志文件(.ldf)*

物理結(jié)構(gòu)邏輯結(jié)構(gòu)

物理上每個(gè)數(shù)據(jù)庫(kù)文件是由數(shù)據(jù)文件和日志文件組成。數(shù)據(jù)文件是數(shù)據(jù)庫(kù)對(duì)象的物理存儲(chǔ)器，所有的數(shù)據(jù)庫(kù)數(shù)據(jù)物理上都是存儲(chǔ)在數(shù)據(jù)文件中。包括：主要數(shù)據(jù)文件、次要數(shù)據(jù)文件日記文件記錄了用戶對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作的信息。

安裝SQLServer2008時(shí)，系統(tǒng)自動(dòng)創(chuàng)建Master、Model、Msdb、Tempdb這4個(gè)系統(tǒng)數(shù)據(jù)庫(kù)。2.1.3系統(tǒng)數(shù)據(jù)庫(kù)和用戶數(shù)據(jù)庫(kù)1.系統(tǒng)數(shù)據(jù)庫(kù)

系統(tǒng)數(shù)據(jù)庫(kù)中保存的系統(tǒng)表用于系統(tǒng)的總體控制、系統(tǒng)運(yùn)行及對(duì)用戶數(shù)據(jù)的操作等基本信息。 這些系統(tǒng)數(shù)據(jù)庫(kù)的文件存儲(chǔ)在SQLServer的默認(rèn)安裝目錄的MSSQL子目錄的Data文件夾中。2.示例數(shù)據(jù)庫(kù) SQLServer2008的過(guò)程中，可以在安裝組件窗口中選擇安裝示例數(shù)據(jù)庫(kù)，默認(rèn)的示例數(shù)據(jù)庫(kù)有：AdventureWorks、AdventureWorksDW。3.用戶數(shù)據(jù)庫(kù)

如果用戶需要保存和管理自己的數(shù)據(jù)信息，則需要?jiǎng)?chuàng)建用戶數(shù)據(jù)庫(kù)。 本課程中，學(xué)生自己創(chuàng)建的數(shù)據(jù)庫(kù)都是用戶數(shù)據(jù)庫(kù)……2.2使用SSMS創(chuàng)建和管理數(shù)據(jù)庫(kù)2.2.1創(chuàng)建數(shù)據(jù)庫(kù)2.2.2修改數(shù)據(jù)庫(kù)2.2.3查看數(shù)據(jù)庫(kù)信息2.2.4刪除數(shù)據(jù)庫(kù)使用圖形化工具創(chuàng)建數(shù)據(jù)庫(kù)2.2.1創(chuàng)建數(shù)據(jù)庫(kù)

創(chuàng)建數(shù)據(jù)庫(kù)的界面創(chuàng)建用戶自定義文件界面2.2.2

修改數(shù)據(jù)庫(kù)修改數(shù)據(jù)庫(kù)名稱；增加或刪除數(shù)據(jù)文件；改變數(shù)據(jù)文件的大小和增長(zhǎng)方式；改變?nèi)罩疚募拇笮『驮鲩L(zhǎng)方式；增加或刪除日志文件；增加或刪除文件組。打開(kāi)SSMS，連接到數(shù)據(jù)庫(kù)服務(wù)器。展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，右鍵單擊所需的數(shù)據(jù)庫(kù)，在彈出的快捷菜單中選擇“重命名”修改數(shù)據(jù)庫(kù)名稱。2.2.3

查看數(shù)據(jù)庫(kù)信息打開(kāi)SSMS，連接到數(shù)據(jù)庫(kù)服務(wù)器。展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，右鍵單擊所需的數(shù)據(jù)庫(kù)，在彈出的快捷菜單中選擇“屬性”命令從屬性窗口的各個(gè)選項(xiàng)卡上可查看到數(shù)據(jù)庫(kù)的相關(guān)信息。2.2.4

刪除數(shù)據(jù)庫(kù)打開(kāi)SSMS，連接到數(shù)據(jù)庫(kù)服務(wù)器。展開(kāi)“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，右鍵單擊所需的數(shù)據(jù)庫(kù)，在彈出的快捷菜單中選擇“刪除”。2.3使用T-SQL創(chuàng)建和管理數(shù)據(jù)庫(kù)2.3.1創(chuàng)建數(shù)據(jù)庫(kù)2.3.2修改數(shù)據(jù)庫(kù)2.3.3查看數(shù)據(jù)庫(kù)信息2.3.4刪除數(shù)據(jù)庫(kù)2.3.1創(chuàng)建數(shù)據(jù)庫(kù)

在命令行方式下創(chuàng)建數(shù)據(jù)庫(kù)，需要使用CREATEDATABASE語(yǔ)句。CREATEDATABASE數(shù)據(jù)庫(kù)名數(shù)據(jù)庫(kù)相關(guān)參數(shù)設(shè)置：【例2.8】使用T-SQL的CREATEDATABASE命令創(chuàng)建一個(gè)“圖書借閱信息管理系統(tǒng)”數(shù)據(jù)庫(kù)。

數(shù)據(jù)文件名為“l(fā)ibrary.mdf”，存儲(chǔ)在“D:\data”下，初始大小為3MB，最大為10MB，文件增量以1MB增長(zhǎng)。

事務(wù)日志文件為“l(fā)ibrary_log.ldf”，存儲(chǔ)在“D:\data”下，初始大小為2MB，最大為5MB，文件增量以10%方式增長(zhǎng)。createdatabase圖書借閱信息管理系統(tǒng)onprimary(name=library,filename='D:\data\library.mdf',size=3MB,maxsize=10MB,filegrowth=1MB)logon(name=library_log,filename='D:\data\library_log.ldf',size=2MB,maxsize=5MB,filegrowth=10%)練習(xí)1：

創(chuàng)建名為“學(xué)生信息”的數(shù)據(jù)庫(kù)，主要數(shù)據(jù)文件初始大小為10MB、最大為60MB、文件增量為5MB，事務(wù)日志文件初始大小為5MB、最大為30MB。文件增量為5MB。CREATEDATABASE學(xué)生信息

ON(NAME=學(xué)生_dat,FILENAME='E:\數(shù)據(jù)庫(kù)\學(xué)生_data.mdf’,SIZE=10MB,MAXSIZE=60MB,FILEGROWTH=5MB)LOGON(NAME=學(xué)生_log,FILENAME='E:\數(shù)據(jù)庫(kù)\學(xué)生_log.ldf’,SIZE=5MB,MAXSIZE=30MB,FILEGROWTH=5MB)練習(xí)2創(chuàng)建名為“教務(wù)信息”的數(shù)據(jù)庫(kù)，含有3個(gè)數(shù)據(jù)文件和2個(gè)事務(wù)日志文件。CREATEDATABASE教務(wù)信息

ONPRIMARY(NAME=教務(wù)_1,FILENAME='E:\數(shù)據(jù)庫(kù)\教務(wù)_data1.mdf',SIZE=100MB,MAXSIZE=300MB,FILEGROWTH=20MB),(NAME=教務(wù)_2,FILENAME='E:\數(shù)據(jù)庫(kù)\教務(wù)_data2.ndf',SIZE=100MB,MAXSIZE=300MB,FILEGROWTH=20MB),

(NAME=教務(wù)_3,FILENAME='E:\數(shù)據(jù)庫(kù)\教務(wù)_data3.ndf',SIZE=100MB,MAXSIZE=300MB,FILEGROWTH=20MB)LOGON(NAME=教務(wù)_log1,FILENAME='E:\數(shù)據(jù)庫(kù)\教務(wù)_log1.ldf',SIZE=100MB,MAXSIZE=300MB,FILEGROWTH=10MB),(NAME=教務(wù)_log2,FILENAME='E:\數(shù)據(jù)庫(kù)\教務(wù)_log2.ldf',SIZE=100MB,MAXSIZE=300MB,FILEGROWTH=10MB)1、增加數(shù)據(jù)庫(kù)文件2.3.2修改數(shù)據(jù)庫(kù)【例2.10】向數(shù)據(jù)庫(kù)“hotel”中添加一個(gè)數(shù)據(jù)文件和一個(gè)日志文件，數(shù)據(jù)文件的名稱為“hotel2”，文件保存在“D:\data”下，初始大小為10MB，最大為30MB，文件增量以5MB增長(zhǎng)；日志文件的名稱為“hotel_log2”，文件保存在“D:\data”下，初始大小為5MB，最大為20MB，文件增量以2MB增長(zhǎng)。alterdatabasehoteladdfile(name=hotel2,filename='D:\data\hotel2.ndf',size=10MB,maxsize=30MB,filegrowth=5MB)【例2.10-1】向數(shù)據(jù)庫(kù)“hotel”中添加一個(gè)數(shù)據(jù)文件和一個(gè)日志文件，數(shù)據(jù)文件的名稱為“hotel2”，文件保存在“D:\data”下，初始大小為10MB，最大為30MB，文件增量以5MB增長(zhǎng)；alterdatabasehoteladdlogfile(name=hotel_log2,filename='D:\data\hotel_log2.ldf',size=5MB,maxsize=20MB,filegrowth=2MB)【例2.10-2】向數(shù)據(jù)庫(kù)“hotel”中添加一個(gè)數(shù)據(jù)文件和一個(gè)日志文件。日志文件的名稱為“hotel_log2”，文件保存在“D:\data”下，初始大小為5MB，最大為20MB，文件增量以2MB增長(zhǎng)?！揪毩?xí)】向數(shù)據(jù)庫(kù)“學(xué)生信息”中添加一個(gè)數(shù)據(jù)文件和一個(gè)日志文件。ALTERDATABASE學(xué)生信息

ADDFILE(NAME=學(xué)生_data,FILENAME=‘D:\MSSQL\學(xué)生_data.ndf', SIZE=100MB,MAXSIZE=300MB,FILEGROWTH=20MB)

GOALTERDATABASE學(xué)生信息

ADDLOGFILE(NAME=學(xué)生_log,FILENAME=‘D:\MSSQL\學(xué)生_log.ldf',SIZE=100MB,MAXSIZE=300MB,FILEGROWTH=10MB)2、刪除數(shù)據(jù)庫(kù)文件2.3.2修改數(shù)據(jù)庫(kù)【例2.11】將數(shù)據(jù)文件“hotel2”從“hotel”數(shù)據(jù)庫(kù)中刪除。alterdatabasehotelremovefilehotel2【練習(xí)】將數(shù)據(jù)文件“學(xué)生_data”從“學(xué)生”數(shù)據(jù)庫(kù)中刪除。 ALTERDATABASE學(xué)生信息

REMOVEFILE

學(xué)生_data3、增加文件組2.3.2修改數(shù)據(jù)庫(kù)【例2.12】為數(shù)據(jù)庫(kù)“hotel”添加文件組“FGroup2”alterdatabasehoteladdfilegroupFGroup24、刪除文件組2.3.2修改數(shù)據(jù)庫(kù)【例2.13】為數(shù)據(jù)庫(kù)“hotel”刪除文件組“FGroup2”alterdatabasehotelRemovefilegroupFGroup25、改變文件大小和增長(zhǎng)方式2.3.2修改數(shù)據(jù)庫(kù)【例2.14】將數(shù)據(jù)庫(kù)“hotel”中日志文件“hotel_log”的SIZE增加至4MB。alterdatabasehotelmodifyfile(name=hotel_log,size=4MB)6、重命名數(shù)據(jù)庫(kù)2.3.2修改數(shù)據(jù)庫(kù)【例2.15】將數(shù)據(jù)庫(kù)“hotel”重命名為“hotel2”。

方法一：alterdatabasehotelmodifyname=hotel2方法二：sp_renamedb'hotel','hotel2'1、查看數(shù)據(jù)庫(kù)定義信息2.3.3查看數(shù)據(jù)庫(kù)信息【例2.17】查看數(shù)據(jù)庫(kù)“圖書借閱管理信息系統(tǒng)”的信息。sp_helpdb圖書借閱信息管理系統(tǒng)2、查看數(shù)據(jù)庫(kù)文件信息2.3.3查看數(shù)據(jù)庫(kù)信息【例2.18】查看數(shù)據(jù)庫(kù)“圖書借閱管理信息系統(tǒng)”中“l(fā)ibrary”的信息。use圖書借閱信息管理系統(tǒng)gosp_helpfilelibrary3、查看數(shù)據(jù)庫(kù)文件組信息2.3.3查看數(shù)據(jù)庫(kù)信息【例2.19】查看數(shù)據(jù)庫(kù)“hotel”中“FGroup”文件組信息。usehotelgosp_helpfilegroupFGroup

在命令行方式下，可以使用系統(tǒng)存儲(chǔ)過(guò)程SP_DBOPTION顯示并修改數(shù)據(jù)庫(kù)選項(xiàng)。SP_DBOPTION的語(yǔ)法格式為：

2.3.4配置數(shù)據(jù)庫(kù)1、設(shè)置只讀模式2.3.4配置數(shù)據(jù)庫(kù)【例2.20】使用T-SQL命令方式將“hotel”數(shù)據(jù)庫(kù)設(shè)置為只讀。sp_dboption'hotel','readonly','true'只讀數(shù)據(jù)庫(kù)不能重命名SP_DBOPTION'NewSales','readonly','false'--去掉只讀屬性SP_RENAMEDB'NewSales','MySales'--將NewSales數(shù)據(jù)庫(kù)改名為MySales2、設(shè)置單用戶模式2.3.4配置數(shù)據(jù)庫(kù)【例2.20】使用T-SQL命令方式將“hotel”數(shù)據(jù)庫(kù)設(shè)置為單用戶方式。sp_dboption'hotel','singleuser','true'2.3.5刪除數(shù)據(jù)庫(kù)【例2.22】使用T-SQL命令方式刪除“hotel”數(shù)據(jù)庫(kù)。usemastergodropdatabasehotel不能刪除數(shù)據(jù)庫(kù)的情況系統(tǒng)數(shù)據(jù)庫(kù)（Msdb、Master、Model、Tempdb）是不能被刪除的。當(dāng)數(shù)據(jù)庫(kù)處于數(shù)據(jù)庫(kù)正在被使用、數(shù)據(jù)庫(kù)正在被恢復(fù)、數(shù)據(jù)庫(kù)中的部分表格是發(fā)布的表格狀態(tài)時(shí)，則數(shù)據(jù)庫(kù)不能被刪除。只讀數(shù)據(jù)庫(kù)不能刪除。

分離與附加數(shù)據(jù)庫(kù)適用于以下2種情況。（1）將數(shù)據(jù)庫(kù)從一臺(tái)計(jì)算機(jī)移到另一臺(tái)計(jì)算機(jī)。（2）將數(shù)據(jù)庫(kù)從一臺(tái)計(jì)算機(jī)的一個(gè)磁盤移到另一個(gè)磁盤。2.4數(shù)據(jù)庫(kù)的分離與附加1．使用圖形化工具分離數(shù)據(jù)庫(kù)

（1）打開(kāi)SSMS，連接到數(shù)據(jù)庫(kù)服務(wù)器。（2）展開(kāi)數(shù)據(jù)庫(kù)節(jié)點(diǎn)，右鍵單擊需重命名的數(shù)據(jù)庫(kù)，在彈出的菜單中選擇“任務(wù)”→“分離”命令。（3）單擊“確定”按鈕，完成數(shù)據(jù)庫(kù)的分離。2.4.1使用SSMS分離與附加數(shù)據(jù)庫(kù)的2．使用圖形化工具附加數(shù)據(jù)庫(kù)

（1）打開(kāi)SQLServerManagementStudio，連接到數(shù)據(jù)庫(kù)服務(wù)器。（2）右鍵單擊“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，在彈出的菜單中選擇“附加”命令。（3）單擊“添加”按鈕，選擇需附加數(shù)據(jù)庫(kù)的主數(shù)據(jù)文件library.mdf。（4）單擊“確定”按鈕，完成數(shù)據(jù)庫(kù)的附加。SQLServer2008默認(rèn)的數(shù)據(jù)庫(kù)文件路徑：

(C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data)2.4.1使用SSMS分離與附加數(shù)據(jù)庫(kù)的1．使用T-SQL分離數(shù)據(jù)庫(kù)【例2.25】使用T-SQL語(yǔ)句分離“圖書借閱信息管理系統(tǒng)”數(shù)據(jù)庫(kù)。

sp_detach_db'圖書借閱信息管理系統(tǒng)'2.4.2使用T-SQL分離與附加數(shù)據(jù)庫(kù)的2．使用T-SQL附加數(shù)據(jù)庫(kù)【例2.26】使用T-SQL語(yǔ)句將“圖書借閱信息管理系統(tǒng)”數(shù)據(jù)庫(kù)附加至“D:\data”路徑下。sp_attach_db'圖書借閱信息管理系統(tǒng)','D:\data\library.mdf'2.4.2使用T-SQL分離與附加數(shù)據(jù)庫(kù)的1．使用T-SQL附加數(shù)據(jù)庫(kù)

（1）打開(kāi)SQLServerManagementStudio，連接到數(shù)據(jù)庫(kù)服務(wù)器。（2）右鍵單擊“數(shù)據(jù)庫(kù)”節(jié)點(diǎn)，在彈出的菜單中選擇“附加”命令。（3）單擊“添加”按鈕，選擇需附加數(shù)據(jù)庫(kù)的主數(shù)據(jù)文件library.mdf。（4）單擊“確定”按鈕，完成數(shù)據(jù)庫(kù)的附加。SQLServer2008默認(rèn)的數(shù)據(jù)庫(kù)文件路徑：

(C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data)2.4.2使用T-SQL分離與附加數(shù)據(jù)庫(kù)的第3章數(shù)據(jù)類型與常用函數(shù)

系統(tǒng)數(shù)據(jù)類型3.1用戶自定義數(shù)據(jù)類型3.2系統(tǒng)內(nèi)置函數(shù)3.3認(rèn)識(shí)表：記錄：表中的每一行被稱為一條記錄，表 由一條和多條記錄組成。字段：表中每一列成為一個(gè)字段，一張表 中最多有1024個(gè)字段。4個(gè)字段3條記錄數(shù)據(jù)類型：

在創(chuàng)建表的時(shí)候，要為表中的每個(gè)字段定義一種數(shù)據(jù)類型，即每個(gè)字段數(shù)據(jù)的格式，因此要對(duì)字段中的數(shù)據(jù)類型進(jìn)行定義。3.1系統(tǒng)數(shù)據(jù)類型 1字符數(shù)據(jù)類型

2精確數(shù)字類型

3近似數(shù)字類型

4日期和時(shí)間數(shù)據(jù)類型

5Unicode字符串

6二進(jìn)制字符串

7其他數(shù)據(jù)類型

字符數(shù)據(jù)是由任意字母、符號(hào)和數(shù)字任意組合而成的數(shù)據(jù)，是現(xiàn)實(shí)工作中最常用的數(shù)據(jù)類型之一。 字符數(shù)據(jù)的類型包括Char、Varchar和Text。數(shù)據(jù)兩端需要用單引號(hào)。

3.1.1字符數(shù)據(jù)類型例如：‘helloSQLServer2008’char(n),varchar(n) char(n)固定分配n個(gè)字節(jié)的存儲(chǔ)單元

n<8000.(多退少補(bǔ))

varchar(n)可變長(zhǎng)，存儲(chǔ)單元視輸入的情況而定，最多為n個(gè)字節(jié)注意：

1.字符數(shù)據(jù)由字母、符號(hào)和數(shù)字的任意組合組成；

2.輸入時(shí)，字符數(shù)據(jù)必須由單引號(hào)’’括起來(lái)；

3.當(dāng)取空字符串時(shí)，只需寫NULL，而非‘NULL’

text舉例：字符數(shù)據(jù)“abcdABCD我們學(xué)習(xí)”共____個(gè)字符占___個(gè)字節(jié)。

（注:字符數(shù)據(jù)類型char(n),varchar(n)中一個(gè)中文占兩個(gè)字節(jié)，一個(gè)數(shù)字、英文各占一個(gè)字節(jié)）答案：12個(gè)字符，16個(gè)字節(jié) 請(qǐng)問(wèn)char(10)/char(20),varchar(20)用來(lái)存儲(chǔ)該數(shù)據(jù)，能存儲(chǔ)么？分析：

char(10):僅存儲(chǔ)“abcdABCD我”

char(20):存儲(chǔ)“abcdABCD我們學(xué)習(xí)”占20字節(jié)

varchar(20):存儲(chǔ)“abcdABCD我們學(xué)習(xí)”占16字節(jié)Unicode是“統(tǒng)一字符編碼標(biāo)準(zhǔn)”，用于支持國(guó)際上非英語(yǔ)種的字符數(shù)據(jù)的存儲(chǔ)和處理。Unicode字符串是為了在數(shù)據(jù)庫(kù)中容納多種語(yǔ)言存儲(chǔ)數(shù)據(jù)，而制定的數(shù)據(jù)類型。 其所占用的存儲(chǔ)大小是使用非Unicode數(shù)據(jù)類型所占用的存儲(chǔ)大小的2倍。包括Nchar（長(zhǎng)度固定）、Nvarchar（長(zhǎng)度可變）和Ntext。3.1.5Unicode字符串·nchar(n),nvarchar(n) nchar(n)存儲(chǔ)固定長(zhǎng)度的雙字節(jié)數(shù)據(jù)，其中n代表最大字符數(shù)，取值范圍為1～4000個(gè)字符。

nvarchar(n)數(shù)據(jù)類型用來(lái)存放可以變長(zhǎng)度的雙字節(jié)數(shù)據(jù)，取值范圍為1～4000，其存儲(chǔ)也類似于varchar舉例：字符型字符串“abcdABCD我們學(xué)習(xí)”作為統(tǒng)一字符型共__個(gè)字符,占__個(gè)字節(jié)。注：每個(gè)字符占2個(gè)字節(jié)。答案：12個(gè)字符，24個(gè)字節(jié) 請(qǐng)問(wèn)nchar(10)/nchar(20),nvarchar(20)用來(lái)存儲(chǔ)該數(shù)據(jù)，能存儲(chǔ)么？分析：

nchar(10)僅存儲(chǔ)“abcdABCD我們”

nchar(20)存儲(chǔ)“abcdABCD我們學(xué)習(xí)”占40字節(jié)

nvarchar(20)存儲(chǔ)“abcdABCD我們學(xué)習(xí)”占24字節(jié)數(shù)字類型精確數(shù)字類型整數(shù)精確小數(shù)貨幣類型位數(shù)據(jù)類型近似數(shù)字類型FloatReal3.1.2精確數(shù)字類型1.

整數(shù)數(shù)據(jù)類型·bigint 8字節(jié)，范圍－263～263－1·int 4字節(jié)，表示范圍－231～231－1·smallint2字節(jié)，范圍－215～215－1·tinyint 1字節(jié)，范圍0