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

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

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

第6章

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

SQLServer。表1.7列舉了用來管理

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

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

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

第1步

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

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

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

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

2)輔助數(shù)據(jù)文件：當數(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個相鄰頁的構成。(3)文件組：多個文件可以歸納成為一個文件組。

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

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

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

Model數(shù)據(jù)庫的主文件是model.mdf，日志文件為model.ldf。Model數(shù)據(jù)庫是SQLServer2000為用戶數(shù)據(jù)庫提供的樣板，新的用戶數(shù)據(jù)庫都以model數(shù)據(jù)庫為基礎。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)定義表間關聯(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ù)恢復操作。

例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〈參照表〉(〈對應字段〉)]：定義被參照表及字段。

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

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

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

(2)記錄約束

CONSTRAINT〈約束名〉〈約束式〉

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

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

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

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

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

姓名char(8)unique,

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

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

出生時間smalldatetime

notnull,

總學分intnull,

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

1)修改字段的定義。

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

ALTERTABLE〈表名〉ADD{〈列定義〉|[〈表約束定義〉]}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〈缺省名組〉

定義默認對象：設置默認值為男CREATEDEFAULTsex_mrAS

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

execsp_bindefault

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

‘學生.性別’刪除默認對象sex_mr

DROPDEFAULTsex_mr

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

CREATERULE〈規(guī)則名〉AS〈規(guī)則表達式〉

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

(2)綁定規(guī)則

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

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

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

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

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

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

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

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

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

[WITHRECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION]

AS〈SQL語句組〉

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

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

4)ENCYPTION為加密選項。

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

過程的調用語句為：

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

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

===============================execproc_7_1execproc_7_2‘061101’例3：帶有參數(shù)的存儲過程，實現(xiàn)傳遞兩個數(shù)，將計算的和返回調用處的接收變量。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ù)學生宿舍統(tǒng)計表dormitory，將每個宿舍人數(shù)統(tǒng)計結果插入到學生公寓人數(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.學號,姓名,性別,xskc.課程號,課程名,kc.學分,成績Fromxs,sc,kc

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

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

dropprocproc_7_1

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

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

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

1)WITHENCRYPTION為加密。

2)DELETE觸發(fā)器是當對表執(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項說明當一個復制過程在修改一個觸發(fā)器表時，與該表相關聯(lián)的觸發(fā)器不能被執(zhí)行。例1：對Xs表創(chuàng)建delete、update和insert觸發(fā)器。當觸發(fā)該操作，自動執(zhí)行T-SQL語句。CREATETRIGGERdelete_student1onXSFORDELETE,UPDATE,INSERTASprint‘數(shù)據(jù)操作成功'GO測試：deletefromXSwhere學號=‘1202701‘….例2：當試圖刪除SC表中的一條記錄時，若成績?yōu)榭?則撤消事務。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ā)器應用實例：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的值賦值給對應的變量。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ā)器，要求當讀者已經借過5本書時不能繼續(xù)借書。Createtrigger借書限制on借閱afterinsertAsif(selectcount（*）frominserted,借閱whereinserted.讀者證號=借閱.讀者書證號)>5Beginrollbacktransactionprint‘借書已超過限額’end【例6-21】設有member表（成員表）、loan表（借書表）和reservation表（預定書表）。通過觸發(fā)器定義未還圖書的成員不能從成員表中刪除，當刪除成員時，該成員在的預定書表（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

學號,成績，'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為僅顯示結果集中的惟一行。該項不選時，ALL是缺省值。

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

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

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

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