數(shù)據(jù)庫原理及應用教程（第5版） （微課版）課件 陳志泊 第6、7章　數(shù)據(jù)庫設(shè)計、SQL Server高級應用

北京林業(yè)大學數(shù)據(jù)庫原理與應用數(shù)據(jù)庫設(shè)計概述數(shù)據(jù)庫設(shè)計的任務數(shù)據(jù)庫設(shè)計的內(nèi)容數(shù)據(jù)庫設(shè)計的特點數(shù)據(jù)庫設(shè)計方法簡述本章目錄CONTENTS數(shù)據(jù)庫設(shè)計的步驟數(shù)據(jù)庫設(shè)計是指根據(jù)用戶需求研制數(shù)據(jù)庫結(jié)構(gòu)和行為的過程。對于一個給定的應用環(huán)境，構(gòu)造最優(yōu)的數(shù)據(jù)庫模式，建立數(shù)據(jù)庫及其應用系統(tǒng)；有效地存儲數(shù)據(jù)，滿足用戶的信息要求和處理要求。數(shù)據(jù)庫設(shè)計的任務數(shù)據(jù)庫設(shè)計的內(nèi)容概念設(shè)計、邏輯設(shè)計和物理設(shè)計用戶對數(shù)據(jù)庫的操作結(jié)構(gòu)設(shè)計行為設(shè)計結(jié)構(gòu)源于行為——強調(diào)結(jié)構(gòu)設(shè)計與行為設(shè)計相結(jié)合行為總是變化——“反復探尋，逐步求精”數(shù)據(jù)庫設(shè)計的特點數(shù)據(jù)庫設(shè)計的特點直觀設(shè)計法也稱為手工試湊法，它是最早使用的數(shù)據(jù)庫設(shè)計方法。這種方法依賴于設(shè)計者的經(jīng)驗和技巧，缺乏科學理論和工程原則的支持，設(shè)計的質(zhì)量很難保證。常常是數(shù)據(jù)庫運行一段時間后又發(fā)現(xiàn)各種問題，這樣再重新進行修改，增加了系統(tǒng)維護的代價。數(shù)據(jù)庫設(shè)計方法簡述直觀設(shè)計法數(shù)據(jù)庫設(shè)計方法簡述常用的規(guī)范設(shè)計法：用E-R圖構(gòu)造一個反映現(xiàn)實世界實體之間聯(lián)系的概念模式基于E-R模型的數(shù)據(jù)庫設(shè)計方法確定數(shù)據(jù)庫中的全部屬性和屬性間的依賴關(guān)系，分析不符合3NF的約束條件，將其進行投影分解，規(guī)范成若干個3NF關(guān)系模式的集合?；?NF的數(shù)據(jù)庫設(shè)計方法先從分析各個應用的數(shù)據(jù)著手，并為每個應用建立自己的視圖，然后再把這些視圖匯總起來合并成整個數(shù)據(jù)庫的概念模式。基于視圖的數(shù)據(jù)庫設(shè)計方法計算機輔助設(shè)計法是指在數(shù)據(jù)庫設(shè)計的某些過程中模擬某一規(guī)范化設(shè)計的方法，并以人的知識或經(jīng)驗為主導，通過人機交互方式實現(xiàn)設(shè)計中的某些部分。數(shù)據(jù)庫設(shè)計方法簡述計算機輔助設(shè)計法圍繞軟件工程的思想，通常以E-R圖設(shè)計為主體，輔以3NF設(shè)計和視圖設(shè)計實現(xiàn)模式的評價和優(yōu)化，吸收各種設(shè)計方法的優(yōu)勢。為提高設(shè)計的協(xié)同效率和規(guī)范化程度，現(xiàn)代數(shù)據(jù)庫設(shè)計過程還會通過計算機輔助設(shè)計工具獲得規(guī)范的數(shù)據(jù)庫設(shè)計結(jié)果。數(shù)據(jù)庫設(shè)計方法簡述現(xiàn)代數(shù)據(jù)庫設(shè)計方法數(shù)據(jù)庫設(shè)計分為六個階段數(shù)據(jù)庫設(shè)計的步驟1．系統(tǒng)需求分析——收集信息內(nèi)容和處理要求，進行分析2．概念結(jié)構(gòu)設(shè)計——表達用戶需求的概念模型3．邏輯結(jié)構(gòu)設(shè)計——由概念模型得出的數(shù)據(jù)模型4．物理結(jié)構(gòu)設(shè)計——存儲結(jié)構(gòu)和存取方法5．數(shù)據(jù)庫實施——數(shù)據(jù)入庫，編寫數(shù)據(jù)庫存取程序6．數(shù)據(jù)庫運行與維護——收集和記錄實際系統(tǒng)運行的數(shù)據(jù)系統(tǒng)需求分析系統(tǒng)需求分析是數(shù)據(jù)庫設(shè)計的起點，為以后的具體設(shè)計做準備。需求分析的結(jié)果是否準確地反映了用戶的實際要求，將直接影響到后面各個階段的設(shè)計，并影響到設(shè)計結(jié)果是否合理和實用。系統(tǒng)需求分析的不正確或誤解，直到系統(tǒng)測試階段才發(fā)現(xiàn)許多錯誤，糾正起來要付出很大代價。需求分析的任務需求分析的方法案例的需求分析本章目錄CONTENTS調(diào)查分析用戶活動，明確用戶的需求目標收集和分析需求數(shù)據(jù)，確定系統(tǒng)邊界需求分析的任務編寫需求分析報告，組織專家評審需求分析的方法（a）自頂向下的需求分析（b）自底向上的需求分析用命名的箭頭表示數(shù)據(jù)流用圓圈表示處理用不封閉的矩形或其他形狀表示存儲用封閉的矩形表示來源和輸出數(shù)據(jù)流圖需求分析的方法數(shù)據(jù)流數(shù)據(jù)流數(shù)據(jù)存儲數(shù)據(jù)來源處理數(shù)據(jù)輸出處理需求信息需求數(shù)據(jù)字典數(shù)據(jù)字典是對系統(tǒng)中數(shù)據(jù)的詳細描述，是各類數(shù)據(jù)結(jié)構(gòu)和屬性的清單。在需求分析階段，它通常包含以下五部分內(nèi)容：數(shù)據(jù)項數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)流數(shù)據(jù)存儲處理過程需求分析的方法最終形成的數(shù)據(jù)流圖和數(shù)據(jù)字典為系統(tǒng)分析報告的主要內(nèi)容，這是下一步進行概念結(jié)構(gòu)設(shè)計的基礎(chǔ)。案例描述案例將圍繞本科教學環(huán)節(jié)中的核心業(yè)務——任課和選課。案例的需求分析任課業(yè)務僅關(guān)心教師教授課程情況。選課業(yè)務主要記錄哪些學生選擇了哪些課程，以及這門課程的得分。案例的數(shù)據(jù)流圖——任課業(yè)務案例的需求分析案例的數(shù)據(jù)流圖——選課業(yè)務案例的需求分析學生自然情況信息：學生的學號、姓名、年齡和院系等。課程信息：課程的編號、名字和授課教師等。教師信息：教師的編號、名字、教師的性別、職稱和教授課程等。任課信息：課程名和授課教師名等。學生選課信息：學生名、課程名和教師名等。課程分數(shù)信息：學生名、課程名和分數(shù)等。案例的數(shù)據(jù)字典案例的需求分析除上述信息外，還需進一步分析該系統(tǒng)中是否還有隱含的數(shù)據(jù)結(jié)構(gòu)。實際調(diào)研結(jié)果表明高校的管理通常以系別為單位，如果不劃分系別，則各院系學生和教師的信息將混雜在一起，不便于開展各項業(yè)務。因此，還需要對系的數(shù)據(jù)項進行定義。系別：系的編號、名稱、系的老師和系的學生。案例的數(shù)據(jù)字典案例的需求分析概念結(jié)構(gòu)設(shè)計概念結(jié)構(gòu)設(shè)計的必要性概念結(jié)構(gòu)設(shè)計就是將需求分析得到的用戶需求抽象為信息結(jié)構(gòu)，即概念模型。從邏輯設(shè)計中分離出概念設(shè)計以后，各階段的任務相對單一化，設(shè)計復雜程度大大降低，便于組織管理。概念模型不受特定的DBMS的限制，也獨立于存儲安排和效率方面的考慮，因而比邏輯模型更為穩(wěn)定。概念模型不含具體的DBMS所附加的技術(shù)細節(jié)，更容易為用戶所理解，因而更有可能準確反映用戶的信息需求。概念模型特點語義表達能力豐富易于交流和理解概念模型的特點易于修改和擴充易于向數(shù)據(jù)模型轉(zhuǎn)換概念模型的E-R表示方法E-R圖的基本成分學生學號選修（a）實體型（b）屬性（c）聯(lián)系1:11:nm:n

E-R模型是最著名、最實用的一種是概念模型。概念模型的E-R表示方法實體及其聯(lián)系圖的形式系主任領(lǐng)導系11學生屬于系n1學生選修課程nm成績（a）兩個實體型之間的聯(lián)系概念模型的E-R表示方法職工領(lǐng)導n1供應商供應項目零件mnn數(shù)量（b）多個實體型之間的聯(lián)系（c）同一實體集內(nèi)部的聯(lián)系實體及其聯(lián)系圖的形式概念模型的E-R表示方法舉例說明學生與課程聯(lián)系的完整的E-R圖m課程課程號課程名學分學生學號姓名性別年齡系別選修成績n自頂向下——逐步求細自底向上——由精細到整體逐步擴張——由核心逐漸向外擴充混合策略——自頂向下+自底向上概念結(jié)構(gòu)設(shè)計的方法與步驟概念結(jié)構(gòu)設(shè)計的方法概念結(jié)構(gòu)設(shè)計的方法與步驟需求分析概念結(jié)構(gòu)設(shè)計的步驟DFD，DD數(shù)據(jù)抽象，局部視圖設(shè)計局部E-R圖視圖集成全局E-R圖邏輯結(jié)構(gòu)設(shè)計征求用戶意見概念結(jié)構(gòu)設(shè)計的方法與步驟數(shù)據(jù)抽象分類聚集將一組具有共同特性和行為的對象抽象為一個實體將對象類型的組成成分抽象為實體的屬性①屬性必須是不可分的數(shù)據(jù)項，不能再由另一些屬性組成。②屬性不能與其他實體具有聯(lián)系，聯(lián)系只能發(fā)生在實體之間。局部E-R模型設(shè)計概念結(jié)構(gòu)設(shè)計的方法與步驟局部E-R模型設(shè)計概念結(jié)構(gòu)設(shè)計的方法與步驟局部E-R模型設(shè)計學生學號姓名性別年齡系別n系別學生人數(shù)教室人數(shù)辦公地點學生學號姓名性別年齡屬于1系主任系別作為一個屬性或?qū)嶓w概念結(jié)構(gòu)設(shè)計的方法與步驟局部E-R模型設(shè)計職稱作為一個屬性或?qū)嶓w教師學號性別職稱教師姓名性別n聘任1職稱分配11住房多元集成法，一次性將多個局部E-R圖合并。二元集成法，首先集成兩個重要的局部E-R圖，以后用累加的方法逐步集成新的E-R圖。視圖集成的方法：概念結(jié)構(gòu)設(shè)計的方法與步驟全局E-R模型設(shè)計概念結(jié)構(gòu)設(shè)計的方法與步驟局部E-R圖合并（消除沖突）優(yōu)化（消除不必要的冗余）基本E-R圖初步E-R圖分析規(guī)范化理論合并局部E-R圖，消除局部E-R圖之間的沖突，生成初步E-R圖概念結(jié)構(gòu)設(shè)計的方法與步驟合并沖突屬性沖突命名沖突結(jié)構(gòu)沖突值域沖突取值單位沖突同名異義異名同義同一對象在不同應用中有不同的抽象同一實體在不同應用中屬性組成不同同一聯(lián)系在不同應用中呈現(xiàn)不同類型消除不必要的冗余，生成基本E-R圖。概念結(jié)構(gòu)設(shè)計的方法與步驟優(yōu)化冗余指冗余的數(shù)據(jù)和實體之間冗余的聯(lián)系。冗余的數(shù)據(jù)是指可由基本的數(shù)據(jù)導出的數(shù)據(jù)。冗余的聯(lián)系是由其他的聯(lián)系導出的聯(lián)系。一個學生可選修多門課程，一門課程可為多個學生選修，因此，學生和課程是多對多的聯(lián)系。一個教師可講授多門課程，一門課程可為多個教師講授，因此，教師和課程也是多對多的聯(lián)系。案例的概念結(jié)構(gòu)設(shè)計案例的局部E-R模型設(shè)計一個系可有多個教師，一個教師只能屬于一個系，因此，系和教師是一對多的聯(lián)系，同樣系和學生也是一對多的聯(lián)系。案例的概念結(jié)構(gòu)設(shè)計課程課程號課程名學生系學號姓名性別年齡平均成績名稱m選修開課擁有成績11mmn教師號學生選課局部E-R圖案例的概念結(jié)構(gòu)設(shè)計教師任課局部E-R圖教師教師號姓名性別職稱m講授課程號屬于課程mn單位1單位編號單位名電話案例的概念結(jié)構(gòu)設(shè)計教師管理系統(tǒng)的初步E-R圖系系編號系名電話m屬于擁有m學生1平均成績姓名性別學號年齡1姓名性別教師號職稱開課選修1m教師講授課程教師號成績課程號課程名mnmn案例的概念結(jié)構(gòu)設(shè)計教師管理系統(tǒng)的基本E-R圖系系編號系名電話m屬于擁有m學生1姓名性別學號年齡1姓名性別教師號職稱選修m教師講授課程成績課程號課程名mnn邏輯結(jié)構(gòu)設(shè)計邏輯結(jié)構(gòu)設(shè)計的任務和步驟初始關(guān)系模式設(shè)計關(guān)系模式規(guī)范化模式評價是否改進模式改進是否實現(xiàn)設(shè)計階段E—R圖以DBMS語法描述網(wǎng)狀模型、層次模型和關(guān)系模型概念結(jié)構(gòu)設(shè)計邏輯結(jié)構(gòu)設(shè)計物理結(jié)構(gòu)初始關(guān)系模式設(shè)計E—R圖實體屬性聯(lián)系關(guān)系模式轉(zhuǎn)換原則初始關(guān)系模式設(shè)計轉(zhuǎn)換原則實體：一個實體轉(zhuǎn)換為一個關(guān)系模式，實體的屬性就是關(guān)系的屬性，實體的碼為關(guān)系的主碼。如果聯(lián)系為1:1，則每個實體的主碼都是關(guān)系的候選碼；如果聯(lián)系為1:n，則n端實體的主碼是關(guān)系的主碼；如果聯(lián)系為n:m，則每個實體的主碼的組合是關(guān)系的主碼。聯(lián)系的處理：一個聯(lián)系轉(zhuǎn)換為一個關(guān)系模式，有三種情況：初始關(guān)系模式設(shè)計具體做法（1）把每一個實體轉(zhuǎn)換為一個關(guān)系模式。（2）把每一個聯(lián)系轉(zhuǎn)換為關(guān)系模式。初始關(guān)系模式設(shè)計（1）把每一個實體轉(zhuǎn)換為一個關(guān)系模式。學生（學號，姓名，性別，年齡）系（系編號，系名，電話）課程（課程號，課程名）教師（教師號，姓名，性別，職稱）初始關(guān)系模式設(shè)計（2）把每一個聯(lián)系轉(zhuǎn)換為關(guān)系模式。屬于（教師號，系編號）講授（教師號，課程號）選修（學號，課程號，成績）擁有（系編號，學號） 初始關(guān)系模式設(shè)計具體做法（3）特殊情況的處理。

多個實體之間的聯(lián)系供應商供應數(shù)量零件項目mnn三個或三個以上實體間的一個多元聯(lián)系，與該多元聯(lián)系相連的各實體的主碼及聯(lián)系本身的屬性均轉(zhuǎn)換成為關(guān)系的屬性，轉(zhuǎn)換后主碼為各實體碼的組合。供應（供應商號，項目號，零件號，數(shù)量）關(guān)系模式規(guī)范化確定范式級別實施規(guī)范化處理需求分析階段概念結(jié)構(gòu)設(shè)計階段邏輯結(jié)構(gòu)設(shè)計階段用函數(shù)依賴的概念分析和表示各個數(shù)據(jù)項之間的聯(lián)系以規(guī)范化理論為指導，確定關(guān)系主碼，消除初步E-R圖中冗余的聯(lián)系從E-R圖向數(shù)據(jù)模型轉(zhuǎn)換過程中，用模式合并與分解方法達到指定的數(shù)據(jù)庫規(guī)范化級別模式評價與改進模式評價對實際性能進行估計，包括邏輯記錄的存取數(shù)、傳送量以及物理結(jié)構(gòu)設(shè)計算法的模型等。性能評價（執(zhí)行效果）對照需求分析的結(jié)果，檢查規(guī)范化后的關(guān)系模式集合是否支持用戶所有的應用要求。功能評價（出現(xiàn)問題，回溯分析）模式評價與改進模式改進如果因為系統(tǒng)需求分析、概念結(jié)構(gòu)設(shè)計的疏漏導致某些應用不能得到支持，則應該增加新的關(guān)系模式或?qū)傩浴Ｈ绻驗樾阅芸紤]而要求改進，則可采用合并或分解的方法。（1）合并：

減少連接操作，提高查詢效率。（2）分解：提高數(shù)據(jù)操作的效率和存儲空間的利用率。案例的邏輯結(jié)構(gòu)設(shè)計依據(jù)轉(zhuǎn)換原則，將概念模型中得到的全局E-R模型中四個實體和四個聯(lián)系分別轉(zhuǎn)換成四個關(guān)系模式。案例的初始關(guān)系模式設(shè)計由于上述轉(zhuǎn)換基于的是全局E-R模型，因此，上述轉(zhuǎn)換得到的模式滿足3NF。案例關(guān)系模式的規(guī)范化案例的邏輯結(jié)構(gòu)設(shè)計對關(guān)系模式進行合并處理，合并具有相同主碼的關(guān)系模式。案例關(guān)系模式的評價和改進學生（學號，姓名，性別，年齡）系（系編號，系名，電話）課程（課程號，課程名）教師（教師號，姓名，性別，職稱）屬于（教師號，系編號）講授（教師號，課程號）選修（學號，課程號，成績）擁有（系編號，學號） 學生（學號，姓名，性別，年齡，系編號）系（系編號，系名，電話）課程（課程號，課程名）教師（教師號，姓名，性別，職稱，系編號）講授（教師號，課程號）選修（學號，課程號，成績）物理結(jié)構(gòu)設(shè)計物理結(jié)構(gòu)設(shè)計對于給定邏輯模型，選取一個最適合應用環(huán)境的物理結(jié)構(gòu)的過程。任務有效地實現(xiàn)邏輯模式，確定所采取的存取策略。物理結(jié)構(gòu)設(shè)計確定物理結(jié)構(gòu)在關(guān)系數(shù)據(jù)庫中主要指存取方法和存儲結(jié)構(gòu)。評價物理結(jié)構(gòu)評價的重點是時間和空間效率。確定物理結(jié)構(gòu)存取方法的設(shè)計聚集——針對重復的存儲和高頻的查詢。索引——保證數(shù)據(jù)的完整性，提高查詢效率，但注意維護成本。數(shù)據(jù)庫系統(tǒng)是多用戶共享的系統(tǒng)，對同一個關(guān)系要建立多條存取路徑才能滿足多用戶的多種應用要求。物理結(jié)構(gòu)設(shè)計的任務之一是根據(jù)數(shù)據(jù)庫管理系統(tǒng)支持的存取方法確定選擇哪些存取方法。

存取方法是快速獲取數(shù)據(jù)庫中數(shù)據(jù)的技術(shù)。確定物理結(jié)構(gòu)存取方法的設(shè)計聚集——針對重復的存儲和高頻的查詢。為了提高查詢速度，把在一個（一組）屬性上具有相同值的元組集中存放在一個物理塊中，這個（這組）屬性稱為聚集碼。對經(jīng)常在一起進行連接操作的關(guān)系可以建立聚集。如果一個關(guān)系的一組屬性經(jīng)常出現(xiàn)在相等比較條件中，則該單個關(guān)系可建立聚集。如果一個關(guān)系的一個（或一組）屬性上的值重復率很高，則此單個關(guān)系可建立聚集。即對應每個聚集碼值的平均元組數(shù)不能太少，太少則聚集的效果不明顯。確定物理結(jié)構(gòu)存取方法的設(shè)計索引——保證數(shù)據(jù)的完整性，提高查詢效率，但注意維護成本。如果一個（或一組）屬性經(jīng)常在查詢條件中出現(xiàn)，則考慮在這個（或這組）屬性上建立索引（或組合索引）。如果一個屬性經(jīng)常作為最大值和最小值等聚集函數(shù)的參數(shù)，則考慮在這個屬性上建立索引。如果一個（或一組）屬性經(jīng)常在連接操作的連接條件中出現(xiàn)，則考慮在這個（或這組）屬性上建立索引。確定物理結(jié)構(gòu)系統(tǒng)配置的設(shè)計DBMS產(chǎn)品一般都提供了一些系統(tǒng)配置變量、存儲分配參數(shù)，供設(shè)計人員和DBA對數(shù)據(jù)庫進行物理優(yōu)化。數(shù)據(jù)存放位置的設(shè)計為了提高系統(tǒng)性能，應該根據(jù)應用情況將數(shù)據(jù)的易變部分、穩(wěn)定部分、經(jīng)常存取部分和存取頻率較低部分分開存放。多磁盤下：將表和索引、日志和數(shù)據(jù)庫對象分開存儲。評價物理結(jié)構(gòu)評價重點是時間效率和空間效率。評價物理數(shù)據(jù)庫的方法完全依賴于所選用的數(shù)據(jù)庫管理系統(tǒng)，主要是從定量估算各種方案的存儲空間、存取時間入手，對估算結(jié)果進行權(quán)衡、比較，選擇出一個較優(yōu)的、合理的物理結(jié)構(gòu)。數(shù)據(jù)庫實施與維護數(shù)據(jù)庫實施數(shù)據(jù)庫實施指根據(jù)邏輯結(jié)構(gòu)設(shè)計和物理結(jié)構(gòu)設(shè)計的結(jié)果，在計算機上建立實際的數(shù)據(jù)庫結(jié)構(gòu)、裝入數(shù)據(jù)、進行測試和試運行的過程。建立實際數(shù)據(jù)庫結(jié)構(gòu)裝入數(shù)據(jù)應用程序編碼與調(diào)試數(shù)據(jù)庫試運行整理文檔建立實際數(shù)據(jù)庫結(jié)構(gòu)DBMS提供的數(shù)據(jù)定義語言（DDL）可以定義數(shù)據(jù)庫結(jié)構(gòu)。裝入數(shù)據(jù)裝入數(shù)據(jù)又稱為數(shù)據(jù)庫加載（Loading），是數(shù)據(jù)庫實施階段的主要工作。為了保證裝入數(shù)據(jù)庫中數(shù)據(jù)的正確無誤，必須高度重視數(shù)據(jù)的校驗工作。應用程序編碼與調(diào)試數(shù)據(jù)庫應用程序的設(shè)計屬于一般的程序設(shè)計范疇，但數(shù)據(jù)庫應用程序有自己的一些特點。集成開發(fā)環(huán)境。數(shù)據(jù)庫試運行（1）功能測試。實際運行應用程序，測試它們能否完成各種預定的功能。（2）性能測試。測試系統(tǒng)的性能指標，分析是否符合設(shè)計目標。技術(shù)說明書，使用說明書。完整的文件資料是應用系統(tǒng)的重要組成部分，但這一點常被忽視。必須強調(diào)這一工作的重要性，引起用戶與設(shè)計人員的充分注意。整理文檔數(shù)據(jù)庫實施建立實際數(shù)據(jù)庫結(jié)構(gòu)（DDL—Table&View）裝入數(shù)據(jù)-耗時（篩選、轉(zhuǎn)換、校驗）應用程序編碼與調(diào)試（借助開發(fā)工具）數(shù)據(jù)庫試運行（功能和性能的測試）整理文檔邏輯設(shè)計結(jié)果物理設(shè)計結(jié)果可在物理階段設(shè)計子系統(tǒng)部分測試原則注意備份工作編寫使用說明書編寫技術(shù)說明書數(shù)據(jù)庫運行和維護數(shù)據(jù)庫運行和維護階段的主要任務包括以下三項內(nèi)容：維護數(shù)據(jù)庫的安全性與完整性：監(jiān)管權(quán)限，調(diào)整轉(zhuǎn)儲計劃。監(jiān)測并改善數(shù)據(jù)庫性能：按照性能監(jiān)控調(diào)整功能，最小化的影響現(xiàn)有業(yè)務。重新組織和構(gòu)造數(shù)據(jù)庫：存儲位置，回收垃圾等北京林業(yè)大學數(shù)據(jù)庫原理與應用Transact-SQL的變量、注釋和運算符Transact-SQL概述變量（變?nèi)肿兞亢途植孔兞浚┳⑨屵\算符本章目錄CONTENTS小結(jié)Transact-SQL概述Transact-SQL概述Transact-SQL（T-SQL）是SQL在MicrosoftSQLServer上的增強版。T-SQL不僅提供標準SQL的DDL和DML功能，還提供了一些內(nèi)置函數(shù)和程序設(shè)計結(jié)構(gòu)(例如IF和WHILE)，可以讓SQLServer的程序設(shè)計具有更強的表達能力。Transact-SQL的變量變量:全局變量局部變量Transact-SQL的變量全局變量由系統(tǒng)定義和維護的，只能使用預先說明及定義。全局變量對用戶而言是只讀的，用戶無法對它們進行修改或管理。用戶定義的變量。用戶根據(jù)需要對它們進行修改或管理變量是可以對其賦值并參與運算的一個實體全局變量Transact-SQL的變量在SQLServer中，全部變量使用兩個@標記為前綴。可以使用簡單的SELECT查詢語句檢索任意全局變量。SELECT@@VERSIONASSQL_SERVER_VERSION全局變量Transact-SQL的變量在SQLServer中，一些常用的全局變量全部變量名含義@@connections服務器啟動以來已經(jīng)創(chuàng)建的連接數(shù)@@ERROR最后一個T-SQL錯誤的錯誤號@@IDENTITY最后一次插入的標識@@SERVERNAME本機服務器名稱@@ROWCOUNT上一個執(zhí)行的SQL語句影響行數(shù)變量聲明Transact-SQL的變量DECLARE@變量名變量類型

[，@變量名變量類型……]SELECT@變量名=變量值或SET@變量名=變量值變量賦值DECLARE@idchar(8)SET@id=‘10010001‘[例]聲明一個長度為8個字符的變量id，并賦值為'10010001'。DECLARE@snovarchar(10),@snvarchar(10)SELECT@sno=SNO,@sn=SNFROMSWHERESno=‘S7’.[例]從表S中查詢學號為‘S7’的學生的學號和姓名，并將查詢的學號和姓名分別復制給@sno和@sn。Transact-SQL的注釋注釋符Transact-SQL的注釋作用：使用注釋進行程序的解釋和說明；對暫時不需要執(zhí)行的語句進行屏蔽。在Transact-SQL中可以使用兩類注釋符：（1）ANSI標準的注釋符“--”用于單行注釋；（2）與C語言相同的程序注釋符，即“/*……*/”，“/*”用于注釋文字的開頭，“*/”用于注釋文字的結(jié)尾，可在程序中標識多行文字為注釋。例子：--這是一個注釋

SELECT*FROMSTUDENT/*注釋內(nèi)容*/Transact-SQL的運算符Transact-SQL的運算符運算符是一種符號，用來指定要在一個或多個表達式中執(zhí)行的操作。SQLServer提供了如下幾種運算符：運算符算術(shù)運算符比較運算符賦值運算符邏輯運算符一元運算符字符串連接運算符按位運算符Transact-SQL的運算符運算符含義+加-減*乘/除%求余數(shù)算術(shù)運算符算術(shù)運算符對兩個表達式執(zhí)行數(shù)學運算，參與運算的表達式必須是數(shù)值數(shù)據(jù)類型或能夠進行算術(shù)運算的其他數(shù)據(jù)類型。Transact-SQL的運算符DECLARE@MyCounterINTSET@MyCounter=1賦值運算符加號(+)是字符串連接運算符，可以用它將字符串連接起來。其他所有字符串操作都使用字符串函數(shù)進行處理。字符串連接運算符Transact-SQL的運算符比較運算符比較運算符用來比較兩個表達式值之間的大小關(guān)系，可以用于除了text，ntext和image數(shù)據(jù)類型之外的所有數(shù)據(jù)類型。運

算

符含

義=等于>

大于<

小于>=大于或等于<=小于或等于<>

不等于!=不等于（非SQL-92標準）!<不小于（非SQL-92標準）!>不大于（非SQL-92標準）Transact-SQL的運算符邏輯運算符邏輯運算符用來對多個條件進行運算，其結(jié)果通常為True或者為False。運

算

符含

義ALL如果一組比較中都為TRUE，運算結(jié)果就為TRUEAND如果兩個表達式都為TRUE，運算結(jié)果就為TRUEANY如果一組的比較中任何一個為TRUE，運算結(jié)果就為TRUEBETWEEN如果操作數(shù)在某個范圍之內(nèi)，運算結(jié)果就為TRUEEXISTS如果子查詢包含一些行，運算結(jié)果就為TRUEIN如果操作數(shù)等于表達式列表中的一個，運算結(jié)果就為TRUELIKE如果操作數(shù)與一種模式相匹配，運算結(jié)果就為TRUENOT對邏輯值取反，即如果操作數(shù)的值為TRUE，運算結(jié)果為FALSE，否則為TRUEOR如果兩個布爾表達式中的一個為TRUE，運算結(jié)果就為TRUESOME如果一系列操作數(shù)中，有些值為TRUE，運算結(jié)果為TRUETransact-SQL的運算符按位運算符對兩個表達式進行二進制位操作，這兩個表達式必須是整型或者整數(shù)兼容數(shù)據(jù)類型。按位運算符運算符含義運算規(guī)則&按位與兩個數(shù)對應的二進制位上都為1時，該位上的運算結(jié)果為1，否則為0|按位或兩個數(shù)對應的二進制位上有一個為1時，該位上的運算結(jié)果為1，否則為0^按位異或兩個數(shù)對應的二進制位上不同時，該位上的運算結(jié)果為1，否則為0

00000111&00000100=0000100Transact-SQL的運算符運算符含

義+正號，數(shù)值為正-負號，數(shù)值為負~按位取反，對操作數(shù)進行按二進制位取反運算，即二進制位上原來為1，運算結(jié)果為0，否則為1一元運算符一元運算符只對一個表達式進行運算。Transact-SQL的運算符運算符優(yōu)先級和結(jié)合性優(yōu)先級（從高到低）運算符說明1()小括號2+、-、~正、負、按位取反3*、/、%乘、除、求余數(shù)4

+、-、+加、減、字符串連接5=、>、<、>=、<=、<>、!=、!>、!<各種比較運算符6^、&、|位運算符7NOT邏輯非8AND邏輯與9ALL、ANY、BETWEEN、IN、LIKE、OR、SOME邏輯運算符10=賦值運算符小結(jié)Transact-SQL的變量、注釋和運算符小結(jié)變量局部變量全局變量注釋運算符賦值運算符字符串連接運算符比較運算符…Transact-SQL的批處理和流程控制批處理流程控制語句小結(jié)本節(jié)內(nèi)容CONTENTS批處理批處理是包含一個或多個T-SQL語句的組。批處理的所有語句被整合成一個執(zhí)行計劃。批處理作為單個執(zhí)行計劃執(zhí)行組成一批SQL語句SQL語句SQL語句SQL語句SQL語句SQL語句批處理是使用GO語句標識批處理的技術(shù)。多個批處理可以用多個GO分開，其中每兩個GO之間的SQL語句就是一個批處理單元。批處理每個批處理被單獨地處理，所以一個批處理中的錯誤不會阻止另一個批處理的運行。USESTUDENTSELECT*FROMSTUDENTUPDATESTUDENTSETAGE=23WHERESN=‘1001’GO流程控制語句T-SQL使用的流程控制語句與常見的程序設(shè)計語言類似，使其能夠產(chǎn)生控制程序執(zhí)行及流程分支的作用。主要有以下幾種控制語句。流程控制語句BEGIN…END語句IF…ELSE語句CASE語句WHILE語句WAITFOR語句GOTO語句RETURN語句使用BEGIN…END語句創(chuàng)建一個由一條語句或多條語句構(gòu)成的程序塊BEGIN…END語句經(jīng)常在條件語句和循環(huán)語句中使用。可以在BEGIN…END中嵌套另一個程序塊。BEGIN…END語句BEGIN…END的語法格式如下：BEGIN<命令行或程序塊>END使用IF…ELSE語句創(chuàng)建條件語句IF…ELSE語句IF…ELSE語句的語法格式如下：IF<條件表達式><命令行或程序塊>[ELSE<命令行或程序塊>]條件表達式是各種表達式的組合。ELSE子句是可選擇的。在T-SQL中IF…ELSE可以嵌套，最多嵌套32級?！纠繌臄?shù)據(jù)庫Teach中的SC數(shù)據(jù)表中求出學號S1同學的平均成績，如果此成績或等于60分，則輸出’pass’信息，否則，輸出’fail’信息IF…ELSE語句例子USETeachGOIF(SELECTAVG（SCORE）FROMSCWHERESNO=‘S1’)>=60PRINT‘pass’ELSEPRINT‘fail’GOEXISTS后面的查詢語句結(jié)果不為空，則執(zhí)行其后的程序塊，否則執(zhí)行ELSE后面的程序塊。當采用NOT關(guān)鍵字的時候，與上面的功能相反。IF[NOT]EXISTS語句IF[NOT]EXISTS語句的語法格式如下：IF[NOT]EXISTS(SELECT子查詢)<命令行或程序塊>[ELSE<命令行或程序塊>]【例】從數(shù)據(jù)庫Teach中的S表中讀取學號S1同學記錄，如果存在，則輸出’存在記錄’，否則，輸出’不存在記錄’。IF[NOT]EXISTS例子USETeachGODECLARE@messageVARCHAR(255)IFEXISTS(SELECT*FROMSWHERESNO=‘S1’)SET@message=‘存在記錄’ELSESET@message=‘不存在記錄’PRINT@messageGO當存在多種條件判斷時，可以使用CASE語句，通過判斷CASE后面表達式的取值，找到對應的WHEN，然后執(zhí)行THEN后的表達式，執(zhí)行后跳出CASE。如果沒有ELSE子句，則所有比較失敗后，反饋NULL，如果存在ELSE子句，ELSE是沒有任何匹配的WHEN時，執(zhí)行的內(nèi)容。CASE語句CASE<表達式>WHEN<表達式>THEN<表達式>…WHEN<表達式>THEN<表達式>[ELSE<表達式>]END【例】從數(shù)據(jù)庫Teach中的S表中選取SNO和Sex，如果Sex字段為’男’，則輸出’M’；如果為’女’，則輸出’F’。CASE語句例子USETeachGOSELECTSNO,Sex=CASEsexWHEN‘男’THEN‘M’WHEN‘女’THEN‘F’ENDFROMSGOCASE語句可以嵌套在SQL語句中。當需要循環(huán)結(jié)構(gòu)時，可以使用WHILE語句，通過WHILE后條件表達式情況，判讀循環(huán)是否終止。如果循環(huán)未結(jié)束，則執(zhí)行BEGIN和END標注的內(nèi)容。WHILE…CONTINUE…BREAK語句WHILE<條件表達式>BEGIN<命令行或程序塊>[BREAK][CONTINUE][命令行或程序塊]END【例】計算輸出1-100之間能夠被3整除的數(shù)的總和及個數(shù)。WHILE語句例子DECLARE@sSMALLINT,@iSMALLINT,@numsSMALLINTSET@s=0SET@i=1SET@nums=0WHILE(@i<=100)BEGINIF(@i%3=0)BEGINSET@s=@s+@iSET@nums=@nums+1ENDSET@i=@i+1ENDPRINT@sPRINT@nums當程序需要阻塞一段時間的時候，可以使用WAITFOR實現(xiàn)。WAITFOR語句WAITFOR{DELAY<'時間'>|TIME<'時間'>|ERROREXIT|PROCESSEXIT|MIRROREXIT}WAITFOR語句（1）DELAY：用來設(shè)定等待的時間，最多可達24小時。（2）TIME：用來設(shè)定等待結(jié)束的時間點。（3）ERROREXIT：直到處理非正常中斷。（4）PROCESSEXIT：直到處理正?；蚍钦Ｖ袛?。（5）MIRROREXIT：直到鏡像設(shè)備失敗?！纠康却?小時2分零3秒后才執(zhí)行SELECT語句WAITFOR語句例子WAITFORDELAY'01:02:03'SELECT*FROMS【例】指定在11:24:00時間點時開始執(zhí)行SELECT語句WAITFORTIME'11:24:00'SELECT*FROMS將執(zhí)行流程改變到由標簽指定的位置。系統(tǒng)跳過GOTO后邊的語句。GOTO語句DECLARE@sSMALLINT,@iSMALLINTSET@i=1SET@s=0BEG:IF(@i<=10)BEGINSET@s=@s+@iSET@i=@i+1GOTOBEG/*使程序跳轉(zhuǎn)到標號為BEG的地方執(zhí)行*/ENDPRINT@sGOTO標識符可以在任意位置使用RETURN退出，系統(tǒng)將不會執(zhí)行RETURN后的語句。RETURN語句RETURN（[整數(shù)值]）RETURN語句不能返回NULL值。SQLServer保留?1～?99之間的返回值作為系統(tǒng)使用。0

程序執(zhí)行成功-1

找不到對象-2

數(shù)據(jù)類型錯誤-3

死鎖錯誤-4

違反權(quán)限原則-5

語法錯誤-6

用戶造成的一般錯誤-7

資源錯誤-8

非致命的內(nèi)部錯誤-9達到系統(tǒng)配置參數(shù)極限返回值含義小結(jié)BEGIN-ENDIF、CASEWHILETransact-SQL的批處理和流程控制流程控制批處理過程GOTORETURN小結(jié)Transact-SQL中常用命令和函數(shù)常用命令函數(shù)小結(jié)CONTENTS本節(jié)內(nèi)容常用命令用于將數(shù)據(jù)庫內(nèi)容或其事務處理日志備份到存儲介質(zhì)上（軟盤、硬盤、磁帶等）。BACKUP常用命令用于驗證數(shù)據(jù)庫完整性、查找錯誤、分析系統(tǒng)使用情況等。DBCC常用命令DECLARE的語法格式如下：

DECLARE{{@local_variabledata_type} |{@cursor_variable_nameCURSOR} |{table_type_definition} }[,...n]DECLARE常用命令EXECUTE或EXEC命令用來執(zhí)行存儲過程。EXECUTEKILLKILL命令用于終止某一過程的執(zhí)行。常用命令PRINT的語法格式如下：

PRINT'anyASCIItext'|@local_variable|@@FUNCTION|string_expressionPRINT命令向客戶端返回一個用戶自定義的信息，即顯示一個字符串、局部變量或全局變量。PRINT常用命令用于在SQLServer系統(tǒng)返回錯誤信息時，同時返回用戶指定的信息。RAISERROR常用命令SELECT命令可用于給變量賦值，其語法格式如下：SELECT{@local_variable=expression}[，...n]SELECT命令可以一次給多個變量賦值。SELECT常用命令命令有兩種用法。用于給局部變量賦值。用于用戶執(zhí)行SQL命令時，SQLServer處理選項的設(shè)定。SET：選項ON；SET：選項OFF；SET：選項值。SET常用命令SHUTDOWN[WITHNOWAIT]SHUTDOWN命令用于停止SQLServer的執(zhí)行。SHUTDOWN常用命令USE{database}USE命令用于改變當前使用的數(shù)據(jù)庫為指定的數(shù)據(jù)庫。USE常用命令函數(shù)統(tǒng)計函數(shù)算術(shù)函數(shù)字符串函數(shù)日期函數(shù)自定義函數(shù)函數(shù)是能夠完成特定功能并返回處理結(jié)果的一組T-SQL語句，處理結(jié)果稱為“返回值”，處理過程稱為“函數(shù)體”。函數(shù)可以用來構(gòu)造表達式，可以出現(xiàn)在SELECT語句的選擇列表中，也可以出現(xiàn)在WHERE子句的條件中。SQLServer的內(nèi)置函數(shù)包括：統(tǒng)計函數(shù)STDEV函數(shù)STDEV函數(shù)返回表達式中所有數(shù)據(jù)的標準差。STDEVP函數(shù)STDEVP函數(shù)返回表達式中所有數(shù)據(jù)的總體標準差。可以在SELECT語句的SELECT和WHERE子句以及表達式中使用。算術(shù)函數(shù)函數(shù)功能三角函數(shù)SINCOSTANCOT返回以弧度表示的角的正弦返回以弧度表示的角的余弦返回以弧度表示的角的正切返回以弧度表示的角的余切角度弧度轉(zhuǎn)換DEGREESRADIANS把弧度轉(zhuǎn)換為角度把角度轉(zhuǎn)換為弧度算術(shù)函數(shù)指數(shù)函數(shù)EXP(表達式)返回以e為底、以表達式為指數(shù)的冪值對數(shù)函數(shù)LOG(表達式)LOG10(表達式)返回表達式的以e為底的自然對數(shù)值返回表達式的以10為底的對數(shù)值平方根函數(shù)SQRT(表達式)返回表達式的平方根取近似值函數(shù)CEILING(表達式)FLOOR(表達式)ROUND(表達式,n)返回大于等于表達式的最小整數(shù)返回小于等于表達式的最大整數(shù)將表達式四舍五入為指定的精度n符號函數(shù)ABS(表達式)SIGN(表達式)返回表達式的絕對值測試表達式的正負號，返0、1或－1其他函數(shù)PI()RAND()返回值為

，即3.1415926535897936返回0～1之間的隨機浮點數(shù)字符串轉(zhuǎn)換函數(shù)字符串函數(shù)ASCII（character_expression）返回字符表達式最左端字符的ASCII碼值CHAR（integer_expression）CHAR函數(shù)用于將ASCII碼轉(zhuǎn)換為字符LOWER（character_expression）LOWER函數(shù)用于把字符串全部轉(zhuǎn)換為小寫字符串轉(zhuǎn)換函數(shù)字符串函數(shù)UPPER（character_expression）UPPER函數(shù)用于把字符串全部轉(zhuǎn)換為大寫STR（float_expression[，length[，<decimal>]]）STR函數(shù)用于把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)去空格函數(shù)字符串函數(shù)LTRIM（character_expression）LTRIM函數(shù)用于把字符串頭部的空格去掉。RTRIM（character_expression）RTRIM函數(shù)用于把字符串尾部的空格去掉。取子串函數(shù)字符串函數(shù)LEFT（character_expression,integer_expression）LEFT函數(shù)返回的子串是從字符串最左邊起到第integer_expression個字符的部分。RIGHT（character_expression,integer_expression）RIGHT函數(shù)返回的子串是從字符串右邊第integer_expression個字符起到最后一個字符的部分。字符串比較函數(shù)字符串函數(shù)CHARINDEX（substring_expression，expression）CHARINDEX函數(shù)返回字符串中某個指定的子串出現(xiàn)的開始位置。日期函數(shù)DAY（<date_expression>）DAY函數(shù)返回date_expression中的日期值。MONTH（<date_expression>）MONTH函數(shù)返回date_expression中的月份值。日期函數(shù)YEAR（<date_expression>）YEAR函數(shù)返回date_expression中的年份值。DATEADD（<datepart><number><date>）DATEADD函數(shù)返回指定日期date加上指定的額外日期間隔number產(chǎn)生的新日期。日期函數(shù)DATEDIFF（<datepart>，<date1>，<date2>）DATEDIFF函數(shù)返回兩個指定日期在datepart方面的不同之處，即date2超過date1的差距值，其結(jié)果值是一個帶有正負號的整數(shù)值。DATENAME（<datepart>，<date>）DATENAME函數(shù)以字符串的形式返回日期的指定部分，此部分由datepart來指定。（1）創(chuàng)建標量值函數(shù)（2）創(chuàng)建內(nèi)聯(lián)表值函數(shù)用戶自定義函數(shù)（3）多語句表值函數(shù)數(shù)值函數(shù)返回結(jié)果為單個數(shù)據(jù)值；表值函數(shù)返回結(jié)果集（table數(shù)據(jù)類型）（1）創(chuàng)建標量值函數(shù)（1）創(chuàng)建標量值函數(shù)CREATEFUNCTIONfunction_name([{@parameter_name[As]parameter_data_type[=default][READONLY]}[,...n]])RETURNSreturn_data_type[WITHENCRYPTION][AS]BEGINfunction_bodyReturnscalar_expressionEND標量值函數(shù)的函數(shù)體由一條或多條T-SQL語句組成，這些語句以Begin開始，以End結(jié)束自定義一個標量函數(shù)Fun1，判斷一個整數(shù)是否為素數(shù)，如果為素數(shù)，則函數(shù)返回1，否則返回0，待判斷的數(shù)通過參數(shù)傳給函數(shù)?！纠縿?chuàng)建標量值函數(shù)的例子創(chuàng)建標量值函數(shù)的例子調(diào)用：SELECTdbo.Fun1(13)CREATEFUNCTIONdbo.Fun1(@nASINT)RETURNSINTASBEGINDECLARE@iINTDECLARE@signINTSET@sign=1SET@i=2WHILE@i<=SQRT(@n)BEGINIF@n%@i=0BEGINSET@sign=0BREAKENDSET@i=@i+1ENDRETURN@signEND（2）創(chuàng)建內(nèi)聯(lián)表值函數(shù)（2）創(chuàng)建內(nèi)聯(lián)表值函數(shù)CreateFunctionfunction_name([{@parameter_name[As]parameter_data_type[=default][Readonly]}[,...n]])Returns

Table[WithEncryption][As]Return（select_statement）（2）創(chuàng)建內(nèi)聯(lián)表值函數(shù)內(nèi)聯(lián)表值函數(shù)沒有函數(shù)體CREATEFUNCTIONdbo.Fun2()RETURNSTABLEASRETURNSELECTSNo,SNFROMS調(diào)用：SELECT*FROMScore_Table('S2')用戶自定義函數(shù)的步驟CreateFunctionfunction_name([{@parameter_name[As]parameter_data_type[=default][Readonly]}[,...n]])Returns@return_variableTable<table_type_definition>[WithEncryption][As]Begin

function_bodyReturnEnd與內(nèi)聯(lián)表值函數(shù)不同的是，多語句表值函數(shù)在返回語句之前還有其他的Transact-SQL語句（3）多語句表值函數(shù)CREATEFUNCTIONScore_Table(@student_idCHAR(6))RETURNS@T_scoreTABLE(CnameVARCHAR(20),GradeINT)ASBEGININSERTINTO@T_scoreSELECTCN,ScoreFROMSC,CWHERESC.CNo=C.CNoandSC.SNo=@student_idandScore<60RETURNEND調(diào)用：SELECT*FROMScore_Table('S2')小結(jié)小結(jié)常用命令函數(shù)常用函數(shù)自定義函數(shù)存儲過程存儲過程的概念、優(yōu)點及分類創(chuàng)建、查看、重命名、刪除、執(zhí)行、修改存儲過程本節(jié)目錄CONTENTS存儲過程的概念、優(yōu)點及分類存儲過程是一組為了完成特定功能的SQL語句集。存儲過程的概念不使用存儲過程執(zhí)行語句未知按順序執(zhí)行效率很低存儲過程的優(yōu)點模塊化的程序設(shè)計，獨立修改。高效率的執(zhí)行，一次編譯。減少網(wǎng)絡流量?？梢宰鳛榘踩珯C制使用。存儲過程的優(yōu)點：存儲過程的分類系統(tǒng)存儲過程，master數(shù)據(jù)庫，sp前綴。用戶自定義存儲過程。擴展存儲過程，xp前綴。存儲過程的分類：創(chuàng)建、查看、重命名、刪除、執(zhí)行、修改存儲過程當創(chuàng)建存儲過程時，需要確定存儲過程的三個組成部分：創(chuàng)建存儲過程所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其他存儲過程的語句。返回給調(diào)用者的狀態(tài)值以指明調(diào)用是成功還是失敗。用CREATEPROCEDURE命令創(chuàng)建存儲過程創(chuàng)建存儲過程CREATEPROCEDUREprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[,...n]創(chuàng)建存儲過程【例】在Teach數(shù)據(jù)庫中，創(chuàng)建一個名稱為MyProc的不帶參數(shù)的存儲過程，該存儲過程的功能是從數(shù)據(jù)表S中查詢所有男同學的信息。USETeachGOCREATEPROCEDUREMyProcASSELECT*FROMSWHERESex='男'創(chuàng)建存儲過程【例】定義具有參數(shù)的存儲過程。在Teach數(shù)據(jù)庫中，創(chuàng)建一個名稱為InsertRecord的存儲過程，該存儲過程的功能是向S數(shù)據(jù)表中插入一條記錄，新記錄的值由參數(shù)提供。USETeachGOCREATEPROCEDUREInsertRecord(@snoVARCHAR(6),@snNVARCHAR(10),@sexNCHAR(1),@ageINT,@deptNVARCHAR(20))ASINSERTINTOSVALUES(@sno,@sn,@sex,@age,@dept)創(chuàng)建存儲過程【例】定義具有參數(shù)默認值的存儲過程。在Teach數(shù)據(jù)庫中，創(chuàng)建一個名稱為InsertRecordDefa的存儲過程，該存儲過程的功能是向S數(shù)據(jù)表中插入一條記錄，新記錄的值由參數(shù)提供，如果未提供系別Dept的值時，由參數(shù)的默認值代替。創(chuàng)建存儲過程USETeachGOCREATEPROCEDUREInsertRecordDefa(@snoVARCHAR(6),@snNVARCHAR(10),@sexNCHAR(1),@ageINT,@deptNVARCHAR(20)='無')ASINSERTINTOSVALUES(@sno,@sn,@sex,@age,@dept)創(chuàng)建存儲過程【例】定義能夠返回值的存儲過程。在Teach數(shù)據(jù)庫中，創(chuàng)建一個名稱為QueryTeach的存儲過程。該存儲過程的功能是從數(shù)據(jù)表S中根據(jù)學號查詢某一同學的姓名和系別，查詢的結(jié)果由參數(shù)@sn和@dept返回。USETeachGOCREATEPROCEDUREQueryTeach(@snoVARCHAR(6),@snNVARCHAR(10)OUTPUT,@deptNVARCHAR(20)OUTPUT)ASSELECT@sn=SN,@dept=DeptFROMSWHERESNo=@sno利用對象資源管理器創(chuàng)建存儲過程創(chuàng)建存儲過程在選定的數(shù)據(jù)庫下打開“可編程性”節(jié)點。找到“存儲過程”節(jié)點，單擊鼠標右鍵，在彈出的快捷菜單中選擇“新建存儲過程”。在新建的查詢窗口中可以看到關(guān)于創(chuàng)建存儲過程的語句模板，在其中添上相應的內(nèi)容，單擊工具欄上的“執(zhí)行”按鈕即可。查看存儲過程

sp_helptext存儲過程名稱【例】查看數(shù)據(jù)庫Teach中存儲過程MyProc的源代碼。USETeachGOEXECsp_helptextMyProc查看存儲過程在選定的數(shù)據(jù)庫下打開“可編程性”節(jié)點。找到“存儲過程”節(jié)點，展開。重新命名存儲過程通過對象資源管理器在選定的數(shù)據(jù)庫下打開“可編程性”節(jié)點。找到“存儲過程”節(jié)點，展開。右鍵單擊要重命名的存儲過程名稱，在彈出的快捷菜單中選擇“重命名”。刪除存儲過程

DROPPROCEDURE{procedure}[,…n]在選定的數(shù)據(jù)庫下打開“可編程性”節(jié)點。找到“存儲過程”節(jié)點，展開。右鍵單擊要刪除的存儲過程名稱，在彈出的快捷菜單中選擇“刪除”。利用對象資源管理器刪除存儲過程執(zhí)行存儲過程EXECMyProcUSETeachGOEXECMyProc【例】執(zhí)行數(shù)據(jù)庫Teach中已定義的不帶參數(shù)的存儲過程MyProc。修改存儲過程ALTERPROCEDUREprocedure_name[;number]在選定的數(shù)據(jù)庫下打開“可編程性”節(jié)點。找到“存儲過程”節(jié)點，展開。右鍵單擊要修改的存儲過程名稱，在彈出的快捷菜單中選擇“修改”。對存儲過程代碼進行修改，單擊工具欄“執(zhí)行”按鈕，即可完成。利用對象資源管理器修改存儲過程觸發(fā)器觸發(fā)器概述觸發(fā)器是一種特殊類型的存儲過程。觸發(fā)器主要有以下優(yōu)點：觸發(fā)器是在某個事件發(fā)生時自動激活而執(zhí)行的。觸發(fā)器可以實現(xiàn)比約束更為復雜的完整性要求。觸發(fā)器可以根據(jù)表數(shù)據(jù)修改前后的狀態(tài)采取相應的措施。觸發(fā)器可以防止惡意的或錯誤的INSERT、UPDATE和DELETE操作。觸發(fā)器的種類DML觸發(fā)器DML觸發(fā)器是在執(zhí)行數(shù)據(jù)操縱語言（DML）事件時被激活而自動執(zhí)行的觸發(fā)器。DDL觸發(fā)器DDL觸發(fā)器是在執(zhí)行數(shù)據(jù)定義語言（DDL）事件時被激活而自動執(zhí)行的觸發(fā)器。登錄觸發(fā)器登錄觸發(fā)器是由登錄（LOGON）事件而激活的觸發(fā)器。SQLServer在工作時為每個觸發(fā)器在服務器的內(nèi)存上建立兩個特殊的表：插入表和刪除表。觸發(fā)器的工作原理對表的操作Inserted表Deleted表增加記錄（INSERT）存放增加的記錄無刪除記錄（DELETE）無存放被刪除的記錄修改記錄（UPDATE）存放更新后的記錄存放更新前的記錄觸發(fā)器的工作原理當對表進行INSERT操作時，INSERT觸發(fā)器被激發(fā)，新的數(shù)據(jù)行被添加到創(chuàng)建觸發(fā)器的表和Inserted表。（1）INSERT觸發(fā)器的工作原理觸發(fā)器的工作原理對表進行DELETE操作時，DELETE觸發(fā)器被激發(fā)，系統(tǒng)從被影響的表中將刪除的行放入Deleted表中。（2）DELETE觸發(fā)器的工作原理觸發(fā)器的工作原理當執(zhí)行UPDATE操作時，UPDATE觸發(fā)器被激活。觸發(fā)器將原始行移入Deleted表中，把更新行插入到Inserted表中。（3）UPDATE觸發(fā)器的工作原理創(chuàng)建觸發(fā)器創(chuàng)建DML觸發(fā)器使用CREATETRIGGER創(chuàng)建DML觸發(fā)器的語法格式為：CREATETRIGGERtrigger_nameON{table|view}[WithEncryption]{For|After|InsteadOf}{[INSERT][,][UPDATE][,][DELETE]}ASsql_statement[;]創(chuàng)建觸發(fā)器創(chuàng)建DML觸發(fā)器【例】設(shè)計一個觸發(fā)器，在學生表S中刪除某一個學生時，在選課表SC中該學生的選課記錄也全部被刪除。USETeachGOCREATETRIGGERdel_SONSAFTERDELETEAS

DELETEFROMSC

WHERESC.SNo

IN(SELECTSNoFROMDELETED)GO創(chuàng)建觸發(fā)器創(chuàng)建DDL觸發(fā)器創(chuàng)建DDL觸發(fā)器的CREATETRIGGER語句的語法格式為：CREATETRIGGERtrigger_nameOn{AllServer|Database}[WithEncryption]{FOR|AFTER}{event_type|event_group}[,...n]ASsql_statement[;]創(chuàng)建觸發(fā)器創(chuàng)建DDL觸發(fā)器【例】創(chuàng)建一個DDL觸發(fā)器safety，禁止修改和刪除當前數(shù)據(jù)庫中的任何表。USETeachGOCREATETRIGGERsafetyONDATABASEFORDROP_TABLE,ALTER_TABLEASPRINT'不能刪除或修改數(shù)據(jù)庫表！'ROLLBACKGO查看觸發(fā)器執(zhí)行系統(tǒng)存儲過程sp_helptr