SQLServer關(guān)系數(shù)據(jù)庫管理系統(tǒng)

2023/2/4數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用機械工業(yè)出版社

6.1SQLServer2005模式結(jié)構(gòu)6.2SQLServer2005的管理組件及管理工具6.3SQLServer2005數(shù)據(jù)庫操作工具6.4Transact-SQL功能及實例

第6章

SQLServer2005關(guān)系數(shù)據(jù)庫管理系統(tǒng)6.1.1客戶/服務(wù)器（C/S）模式應(yīng)用系統(tǒng)對于一般的數(shù)據(jù)庫應(yīng)用系統(tǒng)，除了數(shù)據(jù)庫管理系統(tǒng)外，需要設(shè)計適合普通人員操作數(shù)據(jù)庫的界面。目前，流行的開發(fā)數(shù)據(jù)庫界面的工具主要包括VisualBASIC、VisualC++、VisualFoxPro、Delphi、PowerBuilder等。數(shù)據(jù)庫應(yīng)用程序與數(shù)據(jù)庫、數(shù)據(jù)庫管理系統(tǒng)之間的關(guān)系如圖6-1所示。圖6-1數(shù)據(jù)庫應(yīng)用程序與數(shù)據(jù)庫、數(shù)據(jù)庫管理系統(tǒng)之間的關(guān)系客戶/服務(wù)器（C/S）模式應(yīng)用系統(tǒng)C/S模式可以運行在單機和網(wǎng)絡(luò)的兩種方式。網(wǎng)絡(luò)方式:數(shù)據(jù)庫管理系統(tǒng)在網(wǎng)絡(luò)上的一臺主機上運行，應(yīng)用程序可以在網(wǎng)絡(luò)上的多臺主機上運行，即一對多的方式。例如，用VisualBASIC開發(fā)的客戶/服務(wù)器（C/S）模式的學(xué)生成績管理系統(tǒng)學(xué)生信息輸入界面如圖1.13所示。圖1.13C/S模式的學(xué)生成績管理系統(tǒng)界面6.1.2三層客戶/服務(wù)器（B/S）模式應(yīng)用系統(tǒng)基于Web的數(shù)據(jù)庫應(yīng)用采用三層客戶/服務(wù)器模式，也稱B/S結(jié)構(gòu)。第一層為瀏覽器，第二層為Web服務(wù)器，第三層為數(shù)據(jù)庫服務(wù)器。瀏覽器是用戶輸入數(shù)據(jù)和顯示結(jié)果的交互界面，用戶在瀏覽器表單中輸入數(shù)據(jù)，然后將表單中的數(shù)據(jù)提交并發(fā)送到Web服務(wù)器；Web服務(wù)器應(yīng)用程序接受并處理用戶的數(shù)據(jù)，通過數(shù)據(jù)庫服務(wù)器，從數(shù)據(jù)庫中查詢需要的數(shù)據(jù)（或把數(shù)據(jù)錄入數(shù)據(jù)庫）返回給Web服務(wù)器；Web服務(wù)器再把返回的結(jié)果插入HTML頁面，傳送到客戶端，在瀏覽器中顯示出來。如圖6-2所示。圖6-2三層客戶/服務(wù)器結(jié)構(gòu)6.1.2三層客戶/服務(wù)器（B/S）模式應(yīng)用系統(tǒng)例如，用ASP.NET開發(fā)的三層客戶/服務(wù)器（B/S）模式的學(xué)生成績管理系統(tǒng)學(xué)生信息更新頁面如圖6-3所示。圖6-3B/S模式的學(xué)生成績管理系統(tǒng)頁面6.2.1SQLServer2005服務(wù)器組件（1）DatabaseEngine。數(shù)據(jù)庫引擎是SQLServer2005用于存儲、處理和保護數(shù)據(jù)的核心服務(wù)。SQLServer2005支持在同一臺計算機上同時運行多個SQLServer數(shù)據(jù)庫引擎實例。6.2SQLServer2005的管理組件及管理工具6.2.1SQLServer2005服務(wù)器組件（2）ReportingServices。SQLServerReportingServices（報表服務(wù)，簡稱SSRS）是基于服務(wù)器的報表平臺，可以用來創(chuàng)建和管理包含關(guān)系數(shù)據(jù)源和多維數(shù)據(jù)源中的數(shù)據(jù)的表格、矩陣、圖形和自由格式的報表。（3）AnalysisServices。SQLServerAnalysisServices（分析服務(wù)，簡稱SSAS）為商業(yè)智能應(yīng)用程序提供聯(lián)機分析處理（OLAP）和數(shù)據(jù)挖掘功能。（4）IntegrationServices。SQLServerIntegrationServices（集成服務(wù)，簡稱SSIS）主要用于清理、聚合、合并、復(fù)制數(shù)據(jù)的轉(zhuǎn)換以及管理SSIS包。除此之外，它還提供包括生產(chǎn)并調(diào)試SSIS包的圖形向?qū)Чぞ摺⒂糜趫?zhí)行FTP操作、電子郵件消息傳遞等工作流功能的任務(wù)。（5）NotificationServices。SQLServerNotificationServices（通知服務(wù)，簡稱SSNS）是用于開發(fā)和部署那些生成并發(fā)送通知的應(yīng)用程序的環(huán)境，使用它可以生成個性化消息，并發(fā)送給其他人或設(shè)備。6.2.2SQLServer2005管理和開發(fā)工具1．SQLServer2005管理工具MicrosoftSQLServer2005安裝后，可在“開始”菜單中查看安裝了哪些工具。另外，還可以使用這些圖形化工具和命令實用工具進一步配置

SQLServer。表1.7列舉了用來管理

SQLServer2005實例的工具。管理工具說

明SQLServerManagementStudio用于編輯和執(zhí)行查詢，并用于啟動標(biāo)準(zhǔn)向?qū)蝿?wù)SQLServerProfiler提供用于監(jiān)視SQLServer數(shù)據(jù)庫引擎實例或AnalysisServices實例的圖形用戶界面數(shù)據(jù)庫引擎優(yōu)化顧問可以協(xié)助創(chuàng)建索引、索引視圖和分區(qū)的最佳組合SQLServerBusinessIntelligenceDevelopmentStudio用于AnalysisServices和IntegrationServices解決方案的集成開發(fā)環(huán)境NotificationServices命令提示從命令提示符管理SQLServer對象SQLServerConfigurationManagerSQLServer配置管理器，管理服務(wù)器和客戶端網(wǎng)絡(luò)配置設(shè)置SQLServer外圍應(yīng)用配置器包括服務(wù)和連接的外圍應(yīng)用配置器和功能的外圍應(yīng)用配置器。使用SQLServer外圍應(yīng)用配置器，可以啟用、禁用、開始或停止SQLServer2005安裝的一些功能、服務(wù)和遠程連接?？梢栽诒镜睾瓦h程服務(wù)器中使用SQLServer外圍應(yīng)用配置器ImportandExportData提供一套用于移動、復(fù)制及轉(zhuǎn)換數(shù)據(jù)的圖形化工具和可編程對象SQLServer安裝程序安裝、升級到或更改

SQLServer2005實例中的組件表6.1SQLServer管理工具6.2.2SQLServer2005管理和開發(fā)工具單擊“開始”→“所有程序”→“MicrosoftSQLServer2005”→“配置工具”→“SQLServerConfigurationManager”，在彈出窗口的左邊菜單欄中選擇“SQLServer2005服務(wù)”即可在出現(xiàn)的服務(wù)列表中對各個服務(wù)進行操作，如圖6-4所示。圖6-4SQLServer配置管理器6.2.2SQLServer2005管理和開發(fā)工具使用SQLServer配置管理器可以完成下列服務(wù)任務(wù)：（1）啟動、停止和暫停服務(wù)，雙擊圖1.26服務(wù)列表的某個服務(wù)即可進行操作。（2）將服務(wù)配置為自動啟動或手動啟動，禁用服務(wù)或者更改其他服務(wù)設(shè)置。（3）更改SQLServer服務(wù)所使用的賬戶的密碼。（4）查看服務(wù)的屬性。（5）啟用或禁用SQLServer網(wǎng)絡(luò)協(xié)議。（6）配置SQLServer網(wǎng)絡(luò)協(xié)議。SQLServer2005新實例的默認配置禁用某些功能和組件，以減少此產(chǎn)品易受攻擊的外圍應(yīng)用。默認情況下，禁用下列組件和功能：IntegrationServices（SSIS）SQLServerAgent（代理）SQLServerAgent是一種Windows服務(wù)，主要用于執(zhí)行作業(yè)、監(jiān)視SQLServer、激發(fā)警報以及允許自動執(zhí)行某些管理任務(wù)。SQLServerBrower（瀏覽器）此服務(wù)將命名管道和TCP端口信息返回給客戶端應(yīng)用程序。在用戶希望遠程連接SQLServer2005時，如果用戶是通過使用實例名稱來運行SQLServer2005，并且在連接字符串中沒有使用特定的TCP/IP端口號，則必須啟用SQLServerBrowser服務(wù)以允許遠程連接。FullTextSearch（全文搜索）用于快速構(gòu)建結(jié)構(gòu)化或半結(jié)構(gòu)化數(shù)據(jù)的內(nèi)容和屬性的全文索引，以允許對數(shù)據(jù)進行快速的語言搜索。6.2.2SQLServer2005管理和開發(fā)工具、2．SQLServerManagementStudio環(huán)境SQLServer2005使用的圖形界面管理工具是“SQLServerManagementStudio”。除了Express版本不具有該工具之外，其他所有版本的SQLServer2005都附帶這個工具。這是一個集成的統(tǒng)一的管理工具組。這個工具組將包括一些新的功能，以開發(fā)、配置SQLServer數(shù)據(jù)庫，發(fā)現(xiàn)并解決其中的故障。在“SQLServerManagementStudio”中主要有兩個工具：“圖形化的管理工具（對象資源管理器）”和“TransactSQL編輯器（查詢分析器）”。此外還擁有“解決方案資源管理器”窗口、“模板資源管理器”窗口和“注冊服務(wù)器”等窗口。6.2.2SQLServer2005管理和開發(fā)工具（1）“對象資源管理器”與“查詢分析器”。圖1.27SQLServerManagementStudio6.2.2SQLServer2005管理和開發(fā)工具打開“SQLServerManagementStudio”的方法如下：圖1.28服務(wù)器連接對話框6.2.2SQLServer2005管理和開發(fā)工具（2）“模板資源管理器”。使用腳本編制數(shù)據(jù)庫對象與使用圖形化向?qū)Ь幹茢?shù)據(jù)庫對象相比，最大的優(yōu)點是使用腳本化的方式具有圖形化向?qū)У姆绞剿鶡o法比擬的靈活性。但是，高度的靈活性，也就意味著使用它的時候有著比圖形化向?qū)У姆绞礁叩碾y度。為了降低難度，“SQLServerManagementStudio”提供了“模板資源管理器”來降低編寫腳本的難度。（3）“已注冊的服務(wù)器”?！癝QLServerManagementStudio”界面有一個單獨可以同時處理多臺服務(wù)器的“已注冊的服務(wù)器”窗口?？梢杂肐P地址進行注冊數(shù)據(jù)庫服務(wù)器，也可以用比較容易分辨的名稱為服務(wù)器命名，甚至還可以為服務(wù)器添加描述。名稱和描述會在“已注冊的服務(wù)器”窗口顯示。6.2.2SQLServer2005管理和開發(fā)工具（4）“解決方案資源管理器”。是用來管理項目方案資源的有效工具。項目可以將一組文件結(jié)合在一起作為組進行訪問。創(chuàng)建新項目的步驟如下：

第1步

單擊菜單欄中“文件”→在彈出的子菜單中選擇“新建”→單擊“項目”，選擇所要創(chuàng)建的項目的類型。第2步

為該項目創(chuàng)建一個或多個數(shù)據(jù)庫連接或者添加已經(jīng)存在的項目文件，如圖1.30所示，只需要在“解決方案資源管理器”內(nèi)的“SQLServer腳本2”上右擊鼠標(biāo)，在彈出的快捷菜單中選擇要添加的項目即可。圖1.30“解決方案資源管理器”窗口6.2.2SQLServer2005管理和開發(fā)工具SQLServerManagementStudio功能集成的豐富的管理界面提高日常管理工具的效率利用SMO可擴展的管理架構(gòu)總結(jié)：1、SQLSERVER2005特性2、SQLSERVER2005企業(yè)應(yīng)用框架3、核心組件和后臺服務(wù)組件(1)表和視圖：表是在數(shù)據(jù)庫中存放的實際關(guān)系。視圖是為了用戶查詢方便或根據(jù)數(shù)據(jù)安全的需要而建立的虛表。(2)角色：由一個或多個用戶組成的單元，也稱職能組。一個用戶可以成為多個角色中的成員。(3)索引：來加速數(shù)據(jù)訪問和保證表的實體完整性的數(shù)據(jù)庫對象。的索引有群聚和非群聚索引兩種。群聚索引會使表的物理順序與索引順序一致，一個表只能有一個群聚索引；非群聚索引與表的物理順序無關(guān)，一個表可以建立多個非群聚索引。(4)存儲過程：通過Transact-SQL編寫的程序。包括系統(tǒng)存儲過程和用戶存儲過程：系統(tǒng)存儲過程是由SQLServer2000提供的，其過程名均以SP開頭；用戶過程是由用戶編寫的，它可以自動執(zhí)行過程中安排的任務(wù)。(5)觸發(fā)器：一種特殊類型的存儲過程，當(dāng)表中發(fā)生特殊事件時執(zhí)行。觸發(fā)器主要用于保證數(shù)據(jù)的完整性。(6)約束：約束規(guī)則用于加強數(shù)據(jù)完整性。6.3SQLServer2005數(shù)據(jù)庫操作工具

6.3.1數(shù)據(jù)庫對象6.3.2數(shù)據(jù)庫結(jié)構(gòu)(1)SQLServer2000的三種物理文件

1)基本數(shù)據(jù)文件：基本數(shù)據(jù)文件用于容納數(shù)據(jù)庫對象，它使用.mdf作為文件擴展名。

2)輔助數(shù)據(jù)文件：當(dāng)數(shù)據(jù)庫中的數(shù)據(jù)較多時需要建立輔助數(shù)據(jù)文件。一個數(shù)據(jù)庫中可以沒有、也可以有一個或多個輔助數(shù)據(jù)文件。輔助數(shù)據(jù)文件的擴展名為.ndf。

3)日志文件：用于存放數(shù)據(jù)庫日志信息的文件。一個數(shù)據(jù)庫可以有一個或多個日志文件。日志文件的擴展名為.ldf。(2)數(shù)據(jù)庫文件的兩種組件

1)頁：使用的最小數(shù)據(jù)單元，一頁可以容納8k的數(shù)據(jù)。共有8種頁：數(shù)據(jù)頁、索引頁、文本/圖像頁、全局分配映射表頁、頁空閑空間、索引分配映射表頁、大容量更改映射表頁和差異更改映射表頁。

2)擴展盤區(qū)：擴建表和索引的基本單位，一個擴展盤區(qū)由8個相鄰頁的構(gòu)成。(3)文件組：多個文件可以歸納成為一個文件組。

6.3.3SQLServer2000的系統(tǒng)數(shù)據(jù)庫1.Master數(shù)據(jù)庫

Master數(shù)據(jù)庫的主文件名為Master.mdf，日志文件為Masterlog.ldf。Master中內(nèi)含許多系統(tǒng)表，用來跟蹤和記錄SQLServer相關(guān)信息。2.Msdb數(shù)據(jù)庫

Msdb的主文件名為Msdb.dbf，日志文件名為Msdb.ldf。Msdb由SQLServer企業(yè)管理器和代理服務(wù)器使用。Msdb中記錄著任務(wù)計劃信息、事件處理信息、數(shù)據(jù)備份及恢復(fù)信息和警告及異常信息。3.Model數(shù)據(jù)庫

Model數(shù)據(jù)庫的主文件是model.mdf，日志文件為model.ldf。Model數(shù)據(jù)庫是SQLServer2000為用戶數(shù)據(jù)庫提供的樣板，新的用戶數(shù)據(jù)庫都以model數(shù)據(jù)庫為基礎(chǔ)。4.tempdb數(shù)據(jù)庫

tempdb的主文件名和日志文件名分別為tempdb.dbf和tempdb.ldf。tempdb是一個共享的工作空間，SQLServer2000中的所有數(shù)據(jù)庫都可以使用它，它為臨時表和其他臨時工作提供了一個存儲區(qū)。6.3.4界面方式創(chuàng)建數(shù)據(jù)庫及其他對象（演示）1、創(chuàng)建數(shù)據(jù)庫2、創(chuàng)建表2.1.定義表的完整性約束和索引(1)定義索引和鍵(2)定義表間關(guān)聯(lián)選擇“索引/鍵”頁面，選擇表頁面

6.4Transact-SQL語言

6.4.1數(shù)據(jù)定義語言1.創(chuàng)建和管理數(shù)據(jù)庫

CREATEDATABASE〈數(shù)據(jù)庫名〉

[ON[PRIMARY][(NAME=〈邏輯數(shù)據(jù)文件名〉，]

FILENAME='〈操作數(shù)據(jù)文件路徑和文件名〉'

[，SIZE=〈文件長度〉]

[，MAXSIZE=〈最大長度〉]

[，F(xiàn)ILEROWTH=〈文件增長率〉])[，…n]]

[LOGON([NAME=〈邏輯日志文件名〉，]

FILENAME='〈操作日志文件路徑和文件名〉'

[，SIZE=〈文件長度〉])[，…n]]

[FORRESTORE]子句中：PRIMARY指明主文件名；SIZE說明文件的大小，數(shù)據(jù)庫文件最小為1MB，默認值為3MB；FILEROWTH說明文件的增長率，默認值為10%。FORRESTORE子句說明重建一個數(shù)據(jù)庫，該重建的數(shù)據(jù)庫用于數(shù)據(jù)恢復(fù)操作。

例2.9創(chuàng)建一個名為XSCJ,主文件初始大小為5MB，最大大小為50MB，增長方式為按10%增長；日志文件大小為2MB，最大大小為5MB，每次增長1MB。分析:一個主數(shù)據(jù)文件和一個日志文件logon(name=xscj_log,filename='e:\xscj_log.ldf',size=2MB,Maxsize=5MB,filegrowth=1MB)createdatabaseXSCJon(name=xscj_dat,filename='e:\xscj_dat.mdf',size=5MB,Maxsize=50MB,filegrowth=10%)2.定義表：CREATETABLE〈表名〉(〈列名〉〈類型〉|AS〈表達式〉[〈字段約束〉][，……][〈記錄約束〉])(1)字段約束

1)[NOTNULL|NULL]：不允許或允許字段值為空。

2)[PRIMARYKEYCLUSTERED|NONCLUSTERED：字段為主碼并建立聚集或非聚集索引。

3)[REFERENCE〈參照表〉(〈對應(yīng)字段〉)]：定義被參照表及字段。

4)[DEFAULT〈缺省值〉]：定義字段的缺省值。

5)[CHECK(〈條件〉)]：定義字段應(yīng)滿足的條件表達式。

6)[IDENTITY(〈初始值〉，〈步長〉)]：定義字段為數(shù)值型數(shù)據(jù)，并指出它的初始值和逐步增加的步長值。

(2)記錄約束

CONSTRAINT〈約束名〉〈約束式〉

1)[PRIMARYKEY[CLUSTERED|NONCLUSTERED](〈列名組〉)]

2)[FOREIGNKEY(〈外碼〉)REFERENCES〈參照表〉(〈對應(yīng)列〉)]：

3)[CHECK(〈條件表達式〉)]：定義記錄應(yīng)滿足的條件。

4)[UNIQUE(〈列組〉)]：定義不允許重復(fù)值的字段組。

字段約束CREATETABLEXS(學(xué)號char(6)primarykey,

姓名char(8)unique,

專業(yè)名char(10)notnull,

性別char(2)constraintckxbcheck(性別in(’男’，’女’))政治面貌char(6)default‘團員’

出生時間smalldatetime

notnull,

總學(xué)分intnull,

備注Varchar(500)null)Createtablesc(學(xué)號char(6),課程號char(3),成績tinyint,學(xué)分tinyint,Primarykey(學(xué)號,課程號),constraintfk_xhforeignkey(學(xué)號)ReferencesXS(學(xué)號)，constraintfk_kchforeignkey(課程號)ReferencesKC(課程號))3.基本表的維護(1)修改基本表

1)修改字段的定義。

ALTERTABLE〈表名〉A(chǔ)LTERCOLUMN〈列名〉〈新類型〉[NULL|NOTNULL]〈約束定義〉A(chǔ)LTERTABLExs3ALTERCOLUMNxhchar(8)2)增加字段和表約束規(guī)則。

ALTERTABLE〈表名〉A(chǔ)DD{〈列定義〉|[〈表約束定義〉]}ALTERTABLEXS3ADDCONSTRIANTkkCHECK(政治面貌=‘團員’or

政治面貌=‘群眾’or政治面貌=‘黨員’3)刪除字段或約束規(guī)則。

ALTERTABLE〈表名〉DROP{[CONSTRAINT]〈約束名〉|COLUMN〈列名〉}ALTERTABLEXSDROPKK

4)使約束有效或無效。

ALTERTABLE〈表名〉{CHECK|NOCHECK}CONSTRAINT{ALL|〈約束名組〉}(2)刪除基本表:DROPTABLE〈表名〉

4.創(chuàng)建和管理索引

(1)創(chuàng)建索引

CTEATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX〈索引名〉ON〈表名〉(〈索引列組〉)

(2)刪除索引

DROPINDEX〈表名〉.〈索引名〉

5.創(chuàng)建和管理視圖

(1)創(chuàng)建視圖

創(chuàng)建視圖的語法為：

CREATEVIEW〈視圖名〉[(〈列名組〉)]

[WITHENCRYPTIOPN]

AS〈子查詢〉

[WITHCHECKOPTION]

(2)刪除視圖

DROPVIEW〈視圖名組〉

6.創(chuàng)建和管理缺省(1)創(chuàng)建缺省

CREATEDEFAULT〈缺省名〉

(2)綁定缺省

EXECsp_bindfault

‘〈缺省名〉’，‘〈表名〉.〈列名〉’

系統(tǒng)存儲過程執(zhí)行時的格式為：

EXEC〈存儲過程名〉〈參數(shù)組〉

如果參數(shù)是常量，則要加定界符

‘

’

。

(3)解除缺省

EXECsp_unbinddefault‘〈缺省名〉’，‘〈表名〉.〈列名〉’

(4)刪除缺省

DROPDEFAULT〈缺省名組〉

定義默認對象：設(shè)置默認值為男CREATEDEFAULTsex_mrAS

‘男’將默認對象sex_mr綁定到學(xué)生表的性別。

execsp_bindefault

‘sex_mr',‘學(xué)生.性別’刪除學(xué)生表性別列和默認對象sex_mr的綁定。execsp_unbinddefault

‘學(xué)生.性別’刪除默認對象sex_mr

DROPDEFAULTsex_mr

7.創(chuàng)建和管理規(guī)則(1)創(chuàng)建規(guī)則

CREATERULE〈規(guī)則名〉A(chǔ)S〈規(guī)則表達式〉

規(guī)則表達式是WHERE子句中的有效表達式。

(2)綁定規(guī)則

EXECsp_bindrule‘〈規(guī)則名〉’，‘〈對象名〉’(3)解除規(guī)則

EXECsp_unbindrule‘〈規(guī)則名〉’，‘〈對象名〉’(4)刪除規(guī)則

DROPRULE〈規(guī)則名組〉定義規(guī)則對象：設(shè)置成績在0分和100分之間。createrulecj_rl

AS@cj>=0and@cj<=100將規(guī)則對象cj_rl綁定到成績表的成績列。execsp_bindrule

‘cj_rl’，‘sc.成績’刪除學(xué)生表性別列和規(guī)則對象cj_rl的綁定。execsp_unbindrule

‘成績.成績’刪除規(guī)則對象cj_rl。droprulecj_rl

8.創(chuàng)建和管理存儲過程存儲過程是一系列預(yù)先編譯好的、能實現(xiàn)特定數(shù)據(jù)操作功能的SQL代碼集，用戶可以像使用函數(shù)一樣重復(fù)調(diào)用這些存儲過程，實現(xiàn)它所定義的操作。將執(zhí)行計劃存儲在數(shù)據(jù)庫的服務(wù)器中。它的運行速度比獨立運行同樣的程序要快。

(1)創(chuàng)建存儲過程和調(diào)用存儲過程

CREATEPROCEDURE〈過程名〉[;〈版本號〉][@〈參數(shù)名〉〈參數(shù)類型〉[=〈缺省值〉][OUTPUT]…]

[WITHRECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION]

AS〈SQL語句組〉

1)版本號是整數(shù)，它用于將有相同名字的存儲過程編為不同的組。

2)OUTPUT用于給調(diào)用者返回值。3)RECOMPILE為重編譯。

4)ENCYPTION為加密選項。

5)參數(shù)前加“@”為局部變量，加“@@”則說明為全局變量。

過程的調(diào)用語句為：

EXE[UTE]〈過程名〉[[@〈參數(shù)名〉=]〈參數(shù)〉…[〈版本號〉](2)刪除存儲過程

DROPPROCEDURE〈存儲過程名組〉例1：創(chuàng)建存儲過程，實現(xiàn)查詢所有學(xué)生信息的功能。Createprocproc_7_1AsSelect*Fromxswhere學(xué)號='121103'例2：創(chuàng)建存儲過程proc_7_2，要求實現(xiàn)根據(jù)學(xué)生學(xué)號，產(chǎn)生不同結(jié)果，如果該學(xué)生信息不存在，則顯示“無此學(xué)號的學(xué)生！”，否則返回該學(xué)生的基本信息。Createprocproc_7_2@snochar(8)AsIfexists(Select*Fromxswhere學(xué)號=@sno)select*Fromxswhere學(xué)號=@snoElseprint'無此學(xué)號的學(xué)生！‘

===============================execproc_7_1execproc_7_2‘061101’例3：帶有參數(shù)的存儲過程，實現(xiàn)傳遞兩個數(shù)，將計算的和返回調(diào)用處的接收變量。CREATEPROCEDURE

spAdd@Value1INT,@Value2INT,@ResultValue

INT

OUTPUTASSELECT@ResultValue=@Value1+@Value2ReturnDECLARE@v1INTDECLARE@v2INTDECLARE@valueTotal

INTSET@v1=125SET@v2=3SET@valueTotal=34EXECspAdd@v1,@v2,@valueTotal

OUTPUTPRINTCONVERT(CHAR(5),@v1)+'與'+CONVERT(CHAR(5),@v2)+'的和等于:'+

CONVERT(CHAR(5),@valueTotal)GO例4：建立存儲過程，根據(jù)學(xué)生宿舍統(tǒng)計表dormitory，將每個宿舍人數(shù)統(tǒng)計結(jié)果插入到學(xué)生公寓人數(shù)管理信息表apartmentcreateprocgytj@gychar(5)asdeclare@summ

intbeginselect@summ=count(*)fromdormitorywhereroomid=@gyinsertintoapartmentvalues(@gy,@summ)endexecgytj'201'execgytj'202'execgytj'203'execgytj'204'execgytj'205'execgytj'510'修改存儲過程ALTERPROCEDURE

存儲過程名稱參數(shù)定義ASSQL語句例5：修改proc_7_t1存儲過程的定義。alterprocproc_7_t1asSelectxs.學(xué)號,姓名,性別,xskc.課程號,課程名,kc.學(xué)分,成績Fromxs,sc,kc

Wherexs.學(xué)號=sc.學(xué)號andsc.課程號=kc.課程號and專業(yè)名='計算機教育‘刪除存儲過程：DROPPROC[EDURE]

存儲過程名稱例6.8：刪除存儲過程proc_7_1。

dropprocproc_7_1

觸發(fā)器是一種特殊類型的存儲過程，當(dāng)表中數(shù)據(jù)被修改時，SQLServer自動執(zhí)行觸發(fā)器中定義的T-SQL語句。使用觸發(fā)器可以實施更為復(fù)雜的數(shù)據(jù)完整性約束。工作原理：

觸發(fā)器被觸發(fā)時，內(nèi)存中產(chǎn)生兩個臨時表：INSERTED和DELETEDinserted表和deleted表的結(jié)構(gòu)總是與被該觸發(fā)器作用的表的結(jié)構(gòu)相同，而且只能由創(chuàng)建它們的觸發(fā)器引用。它們是臨時的邏輯表，由系統(tǒng)來維護，不允許用戶直接對它們進行修改。它們存放于內(nèi)存中，并不存放在數(shù)據(jù)庫中。觸發(fā)器工作完成后，與該觸發(fā)器相關(guān)的這兩個表也會被刪除。9.創(chuàng)建和管理觸發(fā)器1.INSERT觸發(fā)器的工作原理當(dāng)一個記錄插入到表中時，INSERT觸發(fā)器自動觸發(fā)執(zhí)行，相應(yīng)的插入觸發(fā)器創(chuàng)建一個inserted表，新的記錄被增加到該觸發(fā)器表和inserted表中。它允許用戶參考初始的INSERT語句中的數(shù)據(jù)，觸發(fā)器可以檢查inserted表，以確定該觸發(fā)器里的操作是否應(yīng)該執(zhí)行和如何執(zhí)行。2.DELETE觸發(fā)器的工作原理當(dāng)從表中刪除一條記錄時，DELETE觸發(fā)器自動觸發(fā)執(zhí)行，相應(yīng)的刪除觸發(fā)器創(chuàng)建一個deleted表，deleted表是個邏輯表，用于保存已經(jīng)從表中刪除的記錄，該deleted表允許用戶參考原來的DELETE語句刪除的已經(jīng)記錄在日志中的數(shù)據(jù)。應(yīng)該注意：當(dāng)被刪除的記錄放在deleted表中的時候，該記錄就不會存在于數(shù)據(jù)庫的表中了。因此，deleted表和數(shù)據(jù)庫表之間沒有共同的記錄。3.UPDATE觸發(fā)器的工作原理修改一條記錄就等于插入一條新記錄，刪除一條舊記錄。進行數(shù)據(jù)更新也可以看成由刪除一條舊記錄的DELETE語句和插入一條新記錄的INSERT語句組成。當(dāng)在某一個觸發(fā)器表的上面修改一條記錄時，UPDATE觸發(fā)器自動觸發(fā)執(zhí)行，相應(yīng)的更新觸發(fā)器創(chuàng)建一個deleted表和inserted表，表中原來的記錄移動到deleted表中，修改過的記錄插入到了inserted表中。

CREATETRIGGER〈觸發(fā)器名〉ON〈表名〉[WITHENCRYPTION]FOR{[DELETE][，][INSERT][，][UPDATE]}[WITHAPPEND][NOTFORREPLICATION]AS〈SQL語句組〉

1)WITHENCRYPTION為加密。

2)DELETE觸發(fā)器是當(dāng)對表執(zhí)行DELETE操作時刪除元組，將刪除的元組放入deleted表中。檢查delete表中的數(shù)據(jù)，確定該如何處理。

3)INSERT觸發(fā)器在對執(zhí)行插入數(shù)據(jù)操作時,將插入表中的數(shù)據(jù)拷貝并送入inserted表中，根據(jù)inserted表中的值決定如何處理。

4)UPDATE觸發(fā)器僅在更新數(shù)據(jù)操作時將要被更新的原數(shù)據(jù)移入deleted表中，將更新后的數(shù)據(jù)備份送入inserted表中，對deleted和inserted表進行檢查，并決定如何處理。

5)NOTFORREPLICATION項說明當(dāng)一個復(fù)制過程在修改一個觸發(fā)器表時，與該表相關(guān)聯(lián)的觸發(fā)器不能被執(zhí)行。例1：對Xs表創(chuàng)建delete、update和insert觸發(fā)器。當(dāng)觸發(fā)該操作，自動執(zhí)行T-SQL語句。CREATETRIGGERdelete_student1onXSFORDELETE,UPDATE,INSERTASprint‘?dāng)?shù)據(jù)操作成功'GO測試：deletefromXSwhere學(xué)號=‘1202701‘….例2：當(dāng)試圖刪除SC表中的一條記錄時，若成績?yōu)榭?則撤消事務(wù)。CREATETRIGGERt_xskc

ONSCAFTERDELETE ASDECLARE@scoreINT SELECT@score=成績FROMDELETEDIF(@scoreISNULL)BEGIN

RAISERROR('不允許刪除這條記錄，因為該生的成績必須給出后方可刪除',16,1)END例3：教師錄入課程成績時，若成績輸入小于0或者大于100都提示出錯。CREATETRIGGERtrigger1 ONcjbAFTERinsert ASDECLARE@scoreINT SELECT@score=cj

FROMinsertedIF(@score>100or@score<0)BEGIN

RAISERROR(‘不允許插入這條記錄，因為該生的成績無效，重新輸入?。?！',16,1)

ROLLBACKTRANSACTION

END觸發(fā)器應(yīng)用實例：createtablestudent( employeeidchar(6)notnullprimarykeyclustered, namechar(6)notnulluniquenonclustered, sexchar(2)notnull, birthdaydatetimenotnull, nationchar(10)notnull, departmentchar(6)notnull, classchar(8)notnull, politicalchar(4)notnull)createtableclasses( classchar(8)notnullprimarykey, boyintnotnull, girlintnotnull,

peoplesum

intnotnull)實現(xiàn)在student表中插入數(shù)據(jù)時和classes表數(shù)據(jù)的一致性createtriggerstudent_cfqonstudentafterinsertasbegindeclare@bj1char(8),@xb1char(2)—@bj1:班級變量,@xb1：性別變量declare@boyint,@girl

int--@boy：男生變量，@girl：女生變量set@boy=0--男生變量賦值set@girl=0--女生變量賦值select@bj1=class,@xb1=sexfrominserted--插入的記錄先放在inserted表中，再將該表中班級class和性別sex的值賦值給對應(yīng)的變量。select@boy=1frominsertedwhere@xb1='男'select@girl=1frominsertedwhere@xb1='女'updateclassessetboy=boy+@boywhereclass=@bj1updateclassessetgirl=girl+@girlwhereclass=@bj1updateclassessetpeoplesum=peoplesum+1endselect@boy=1frominsertedwhere@xb1='男'select@girl=1frominsertedwhere@xb1='女'updateclassessetboy=boy+@boywhereclass=@bj1updateclassessetgirl=girl+@girlwhereclass=@bj1updateclassessetpeoplesum=peoplesum+1以上改成if

else語句。例6-32：定義借閱表的插入觸發(fā)器，要求當(dāng)讀者已經(jīng)借過5本書時不能繼續(xù)借書。Createtrigger借書限制on借閱afterinsertAsif(selectcount（*）frominserted,借閱whereinserted.讀者證號=借閱.讀者書證號)>5Beginrollbacktransactionprint‘借書已超過限額’end【例6-21】設(shè)有member表（成員表）、loan表（借書表）和reservation表（預(yù)定書表）。通過觸發(fā)器定義未還圖書的成員不能從成員表中刪除，當(dāng)刪除成員時，該成員在的預(yù)定書表（reservation表）中的記錄也將全部被刪除。

CREATETRIGGERmember_deleteONmember

FORDELETE

ASIF(SELECTCOUNT(*)

FROMloan，deleted

WHEREloan.member_no=deleted.member_no)>0

ROLLBACKTRANSACTION

ELSE

DELETEreservation

FROMreservation，deleted

WHEREreservation.member_no=deleted.member_no

例：實現(xiàn)按成績分為四個等級:優(yōu)、良、中、合格、差select

學(xué)號,成績，'scorelevel'=

case

when

成績>=90and成績<=100then'優(yōu)'

when成績>=80and成績<90100then'良'

when成績>=70and成績<80then'中'

when成績>=60and成績<=70then'合格'

else'差'

endfromsc分析：書203頁：例6-346.4.2數(shù)據(jù)操縱語言

1.數(shù)據(jù)檢索語句的語句格式

SELECT〈查詢列〉

[INTO〈新表名〉]

[FROM〈數(shù)據(jù)源〉]

[WHERE〈元組條件表達式〉]

[GROUPBY〈分組條件〉][HAVING〈組選擇條件〉]

[ORDERBY〈排序條件〉]

[COMPUTER〈統(tǒng)計列組〉][BY〈表達式〉]

（1）SELECT子句SELECT[ALL|DISTINCT][TOP〈數(shù)值〉[PERCENT]]〈查詢列組〉

查詢列為：〈查詢列〉::=*|〈表或視圖〉.*|〈列名或表達式〉[AS]〈列別名〉

|〈列別名〉=〈表達式〉

1)ALL|DISTINCT：ALL為返回所有行,DISTINCT為僅顯示結(jié)果集中的惟一行。該項不選時，ALL是缺省值。

2)TOP〈數(shù)值〉：僅返回結(jié)果集中的前〈數(shù)值〉行。如果有[PENCENT]，則返回結(jié)果集中的百分之〈數(shù)值〉行記錄。

3)“*”：指明返回表和視圖的全部列。

4)〈表或視圖〉.*：指明返回指定表或視圖的全部列。

5)〈列別名〉：用來代替出現(xiàn)在結(jié)果集中的列名或表達式，別名可以在ORDERBY子句中出