上海大學自動化系林小玲第7章數據庫開發技術3

7.3.1

SQLServer簡介

7.3.2數據庫的管理7.3.3表的管理7.3數據庫管理系統SQLServer7.3.4視圖7.3.5存儲過程

7.3.6觸發器7.3.1SQLServer簡介SQLServer的發展

SQLServer數據庫的體系結構

SQLServer的管理工具

SQLServer

特點SQLServer的體系結構

SQLServer系統數據庫

SQLServer最初是由Microsoft、Sybase和Ashton-Tate三家公司共同開發的。

1988年：微軟、Sybase和Ashton-Tate公司聯合開發并運行于OS/2操作系統上的SQLServer1.01989年：Ashton-Tate公司退出SQLServer的開發

1990年：SQLServer1.1產品面世

1991年：SQLServer1.11產品面世

1992年：SQLServer4.2產品面世

1994年：微軟和Sybase公司分道揚鑣

1995年：微軟發布了SQLServer6.0產品，隨后的SQLServer6.5產品取得了巨大的成功。SQLServer的發展

1998年：微軟發布了SQLServer7.0產品，開始進入企業級數據庫市場

2000年：微軟發布了SQLServer2000產品

2005年：微軟發布了SQLServer2005產品，系統引入了.NETFramework。引入后系統將允許構建.NETSQLServer專有對象，從而使SQLServer更具有靈活的功能。

2008年8月，微軟公司發布了MicrosoftSQLServer2008系統，其代碼名稱是Katmai，該系統在安全性、可用性、易管理性、可擴展性、商業智能等方面有了更多的改進和提高，對企業的數據存儲和應用需求提供了更強大的支持和便利。。SQLServer的發展（續）

對操作系統的要求低

SQLServer2000除了繼續支持Windows2000Server外，還新增了SQLServer2000桌面版（DesktopEdition），此版本可以安裝在Windows95/98/2000專業版上，用來建立數據庫，可以在任何大小的計算機上運行，包括膝上機、臺式機和高檔服務器。管理自動化數據庫調整和最佳化處理等可由系統自動完成，例如，系統會動態調整內存需求，自動縮減庫和增加數據庫所占文件的空間大小，自動更新統計數據等等。新的EnterpriseManager

它以MMC（MicrosoftManagementConsole）為基礎加以設計，其界面和其他微軟服務器產品一致。SQLServer的特點

簡化了SQLServer2000數據庫與Windows文件系統之間的關系，具有更好的可伸縮性，在SQLServer2000中，數據庫駐留在操作系統文件上，而不再位于SQLServer的邏輯設備中，即僅使用CREATEDATABASE語句即可創建數據庫及其所有文件，從而簡化了數據庫設計和管理的工作。SQLServer2000允許數據庫自動擴展，不再需要管理員發出額外的ALTER語句命令。支持XML(ExtensiveMarkupLanguage)擴展標記語言。提供數據庫挖掘功能在SQLServer2000中加入的OLAP（On-LineAnalyticalProcessing）服務，可以幫助公司建立決策分析的應用。。

SQLServer的特點（續）SQLServer的體系結構

SQLServer是一個基于C／S模式的關系數據庫管理系統，如圖所示。客戶端應用程序提交SQL語句，SQLServer查詢結果返回前臺客戶端服務器端。

SQLServer的體系結構（續）

SQLServer采用C／S體系結構把所有的工作負荷分解為服務器上的任務和客戶端任務。客戶機上的任務主要有：建立和斷開與服務器的連接。提交數據訪問請求。等待服務通告、接受請求結果或錯誤。處理數據庫訪問結果或錯誤、包括重發請求和終止請求。提供應用程序的友好用戶界面。數據輸入輸出及驗證。

SQLServer的體系結構（續）服務器上的任務主要有：為多用戶管理一個獨立的數據庫。管理和處理接收到的數據訪問請求，包括管理請求隊列、管理緩存、響應服務、管理結果和通知服務完成。管理用戶帳號、控制數據庫訪問權限和其他安全性。維護數據庫，包括數據庫備份和恢復。保證數據庫數據的完整或為客戶提供完整性控制手段。。

SQLServer的體系結構（續）

SQLServer的客戶/服務器體系結構可以采用靈活的部署方案。主要方案有：兩層結構，三層結構和桌面系統。圖所示為兩層結構示意圖在兩層結構中，SQLServer安裝在一個中心服務器上，數據庫存儲在該服務器上，該服務器稱為數據庫服務器，可以被多臺客戶機訪問。眾多的客戶機通過網絡直接訪問數據庫服務器。客戶機運行處理業務的程序和顯示處理結果的程序。兩層結構比較適合于用戶量較少的情況，當用戶量較大時，數據庫服務器的性能會顯著下降。

SQLServer的體系結構（續）

三層結構中引入了一層應用服務器。客戶機只運行向應用服務器發送請求和顯示請求結果的程序，客戶機沒有處理業務的程序，應用服務器運行處理業務的程序，多個客戶機可以同時訪問應用服務器，應用服務器負責訪問數據庫服務器，并取回處理結果，將結果返回給客戶機。三層結構適用于客戶量較大的情況。如圖所示

SQLServer的體系結構（續）

SQLServer提供了大量的管理工具，實現了對系統進行快速、高效的管理。在

SQLServer程序組中可以看到9個程序項，這些程序項為管理和使用SQLServer數據庫提供了各種方便手段。單擊每一個程序項，可以打開相應的管理工具。如圖所示。SQLServer的管理工具

1.企業管理器（EnterpriseManager）企業管理器是用戶管理SQLServer的主要工具和圖形界面，它的作用是用于配置系統的環境，完成數據庫管理與維護的大部分工作。如配置系統環境和管理SQLServer服務器，顯示、建立、管理所有的SQLServer對象，備份和恢復數據庫，復制數據庫，管理用戶帳戶等。主界面如圖所示

SQLServer的管理工具（續）

17

SQLServer的管理工具（續）

2.查詢分析器（QueryAnalyzer）查詢分析器是一個交互式的圖形工具，主要用于輸入和執行Transact-SQL語句，并且迅速查看這些語句的結果，以分析和處理數據庫中的數據。查詢分析器是一個非常實用的工具，對掌握SQL，深入理解SQLServer的管理工作有很大幫助。工具窗口如圖所示

SQLServer的管理工具（續）

查詢分析器主要功能：編寫、編輯和執行Transcat-SQL語句，并可以查看執行結果。不同的結果分別顯示在不同的窗格中。指定顯示結果的格式。能以不同的顏色和字體顯示代碼中不同的組成部分。能檢查所編寫的代碼語法是否正確。可以使用調試工具調試存儲過程。可以將編寫的Transcat-SQL語句存儲成文件，在需要時打開編輯或執行。能夠利用模板快速創建數據庫對象。可以快速地添加、修改和刪除表中的數據記錄。通過“對象瀏覽器”可以完成許多快速功能。

SQLServer的管理工具（續）

3．服務管理器（ServiceManager）

SQLServer的服務管理器用來啟動、停止和暫停SQLServer過程。必須在進行任何數據庫操作前啟動SQLServer。服務管理器可以從SQLServer的程序組中進行啟動。啟動后的服務管理器如圖所示4.導入和導出數據導入數據是從SQLServer的外部數據源（如ASCII文本文件）中檢索數據，并將數據插入到SQLServer表的過程。導出數據是將SQLServer表中的數據轉換為其他指定格式的數據的過程，例如將SQLServer表的內容復制到

Access數據庫中。5.服務器網絡實用工具：配置服務器端網絡連接和設置相關參數等。6.客戶端網絡實用工具：配置客戶端的網絡連接、管理和測定客戶端的網絡庫等。7.事件探查器：是一個圖形化的管理工具，用于監督、記錄和檢查SQLServer數據庫的使用情況。系統管理員可以利用它來實時捕獲用戶的活動情況。8．在IIS中配置SQLXML支持：用于實現對SQLServer的IIS虛擬目錄的管理。

SQLServer的管理工具（續）

SQLServer數據庫的體系結構

SQLServer數據庫的體系結構可以分為邏輯體系結構和物理體系結構，本教材只介紹其邏輯體系結構。SQLServer能夠支持多種數據庫，結構如圖所示。在一個SQLServer數據庫中，除了包含基本表以外，還包含了與這些基本表相關的多種對象，如視圖、索引、存儲過程和觸發器。目的是為執行與數據有關的活動提供支持。結構如圖7所示。SQLServer數據庫的體系結構（續）

SQLServer系統數據庫

1.master

是系統最重要的數據庫，它記錄了SQLServer系統的所有系統信息。這些系統信息包括所有的登錄信息、系統設置信息、SQLServer的初始化信息和其它系統及用戶數據庫的相關信息。建議不要在master數據庫中創建任何用戶對象。如表、視圖、存儲過程或觸發器等。2.model

是所有用戶數據庫和tempdb數據庫的模板數據庫，它含有master數據庫所有系統表的子集，這些系統表是每個用戶定義數據庫需要的。3.msdb

是代理服務數據庫，用于為警報、任務調度和記錄操作員的操作提供存儲空間。4.tempdb

用于為所有的臨時表、臨時存儲過程提供存儲空間，還用于任何其它的臨時存儲要求。例如存儲SQLServer生成的工作表。tempdb數據庫是全局資源，所有連接到系統的用戶的臨時表和存儲過程都存儲在該數據庫中。在SQLServer每次啟動時都重新創建，因此該數據庫在系統啟動時總是干凈的，臨時表和存儲過程在連接斷開時自動除去。5.pubs數據庫和northwind數據庫是兩個實例數據庫。pubs數據庫:以一個圖書出版公司為模型，用于演示數據庫中可用的許多選項。該數據庫及其中的表經常在幫助文檔所介紹的示例中使用。

SQLServer系統數據庫（續）

7.3.2數據庫的管理數據庫的存儲結構恢復數據庫

備份數據庫

創建數據庫修改數據庫

分離數據庫與附加數據庫

數據庫的存儲結構SQLServer中每個數據庫由一組操作系統文件組成。數據庫中的所有數據、對象和數據庫操作日志都存儲在這些文件中。數據庫文件可以分為三類：主數據庫文件，次數據庫文件和事務日志文件主數據庫文件(PrimaryDatabasefile)每個數據庫有且僅有一個主數據庫文件，用來存儲數據庫的啟動信息和部分或全部數據。一個數據庫可以有1到多個數據庫文件，其中只有一個文件為主數據庫文件。主數據庫文件的文件擴展名為mdf。次數據庫文件(SecondaryDatabaseFile)用于存儲主數據庫文件中未存儲的剩余數據和數據庫對象。一個數據庫可以沒有次數據庫文件，也可以有多個次數據庫文件。次數據庫文件的文事務日志文件(TranscationLogFile)

用于存儲數據庫的更新情況等事務日志信息。當數據庫損壞時，可以使用事務日志文件恢復數據庫。一個數據庫可以有1到多個事務日志文件。事務日志文件的擴展名為ldf。所有的數據庫文件又有邏輯文件名與物理文件名之分邏輯文件名邏輯文件名是在所有Transact-SQL語句中引用文件時所使用的名稱。邏輯文件名必須遵守SQLServer標識符的命名規則，且對數據庫必須是唯一的。物理文件名數據庫文件在物理磁盤上的存儲路徑及文件名稱構成數據庫文件的物理名稱，物理文件名必須遵從操作系統文件名的命名規則。數據庫的存儲結構（續）此外，為了便于分配和管理，SQLServer允許將多個文件歸納為同一組，并賦予此組一個名稱，這就是文件組。文件組也分為三類：主文件組，次文件組，默認文件組數據庫的存儲結構（續）創建數據庫就是確定數據庫的名稱、設計數據庫所占用的存儲空間和文件的存放位置。每個新創建的數據庫都包含以下數據庫對象：關系圖、表、視圖、存儲過程、用戶、角色、規則、默認、用戶定義的數據類型和用戶定義的函數。創建數據庫的途徑:

企業管理器

Transact-SQL語句

創建數據庫企業管理器創建數據庫選擇“新建數據庫”選項。或使用工具欄中的新建數據庫，進行創建，如圖所示

創建數據庫（續）應用數據庫文件頁面，確定數據庫文件及日志文件位置，數據庫文件的名稱并指定文件的初始大小，同時確定數據庫文件大小是否自動增長和增長的方式等，如圖所示。

創建數據庫（續）使用CREATEDATABASE語句創建數據庫,格式：CREATEDATABASE數據庫名稱[ON[<文件說明>[,...n]][,<文件組>[,...n]]][LOGON{<文件說明>[,...n]}]

創建數據庫（續）【例】設已經在e盤建立了兩個文件夾：e:\sql_data和e:\sql_log，分別用來存放數據庫文件和事務日志文件，使用CREATEDATABASE語句創建一個company數據庫:

CREATEDATABASEcompany--創建數據庫ONPRIMARY--定義在主文件組上的文件(NAME=company_data,--邏輯名稱FILENAME='e:\sql_data\company.mdf',--物理名稱SIZE=10,--初始大小為10MBMAXSIZE=unlimited,--最大限制為無限大FILEGROWTH=10%)--增長速度為10%LOGON--定義事務日志文件(NAME=company_log,--邏輯名稱FILENAME='e:\sql_log\company.ldf',--物理名稱SIZE=1,--初始大小為1MBMAXSIZE=5,--最大限制為5MBFILEGROWTH=1)--增長速度為1MB

創建數據庫（續）在企業管理器中利用數據庫屬性對話框直接修改創建時的某些設置，或修改創建時無法設置的屬性，也可使用Transcat-SQL語言的ALTERDATABASE語句修改數據庫。使用企業管理器修改數據庫應用企業管理器中“屬性”命令來查看或修改數據庫文件及其他屬性。

修改數據庫

使用ALTERDATABASE語句修改數據庫格式：ALTERDATABASE數據庫名稱{ADDFILE<文件說明>[,...n][TOFILEGROUP文件組名稱]|ADDLOGFILE<文件說明>[,...n]|REMOVEFILE邏輯文件名|ADDFILEGROUP文件組名稱|REMOVEFILEGROUP文件組名稱|MODIFYFILE<文件說明>|MODIFYNAME=新數據庫名|MODIFYFILEGROUP文件組名稱{文件組屬性|NAME=新文件組名稱}

修改數據庫（續）

備份數據庫數據庫的備份的方式有完全數據庫備份，差異數據庫備份，事務日志備份及數據庫文件和文件組備份4種。完全數據庫備份：備份數據庫的所有表的數據及模式，以及對應的文件結構，包括對事務日志中的事務進行備份。差異數據庫備份：只記錄自上次完全數據庫備份后發生更改的數據。事務日志備份：事務日志是自上次備份事務日志后對數據庫執行的所有事務的一系列記錄。據庫文件和文件組備份：只備份特定的文件或文件組，常用于超大型數據庫的備份。

備份數據庫（續）1.創建備份設備備份設備是用來存儲數據庫、事務日志或文件和文件組備份的存儲介質。包括磁盤和磁帶設備。可以在本地服務器的磁盤上或遠程共享磁盤上定義磁盤備份設備。磁帶設備只能物理連接到運行SQLServer實例的計算機上。SQLServer不支持備份到遠程磁帶設備上。

SQLServer使用物理設備名稱或邏輯設備名稱來標識備份設備。物理設備名稱：是操作系統用來標識備份設備的名稱，如“D:\MyBackups\Student.bak”。邏輯設備名稱：是用來標識物理備份設備的別名或公用名稱，用以簡化物理設備名稱。例如，邏輯設備名稱可以是“STDBackup”，而物理設備名稱則是“D:\MyBackups\Student.bak”。（1）使用企業管理器創建設備在企業管理器中應用“新建備份設備”命令，并在圖形對話框中輸入邏輯備份設備名稱和物理備份設備名稱即可創建備份設備，如圖所示

備份數據庫（續）

備份數據庫（續）（2）使用系統存儲過程創建備份設備格式:

sp_addumpdevice[@devtype=]'設備類型',[@logicalname=]'邏輯備份設備名',[@physicalname=]'物理備份設備名'【例】創建磁盤一個備份設備,邏輯名稱為“copy1”,物理名稱為“d:\Mybackup\company.bak”EXECsp_addumpdevice@devtype='disk',@logicalname='copy1',@physicalname='d:\Mybackup\company.bak'也可以簡化成：

EXECsp_addumpdevice

'disk','copy1','d:\Mybackup\company.bak'

備份數據庫（續）2.執行備份使用企業管理器或系統存儲過程執行數據庫的備份。（1）使用企業管理器執行備份選擇“備份數據庫”命令，在圖形化界面中選擇數據庫名稱，備份類型及備份的目的路徑等參數，如圖所示。

備份數據庫（續）

備份數據庫（續）（2）使用BACKUPDATABASE語句備份數據庫完全數據庫備份格式：

BACKUPDATABASE數據庫名稱

TO<備份設備>[,...n][WITH[NAME=備份集名稱][[,]DESCRIPTION='備份描述文本'][[,]{INIT|NOINIT}]]【例】將“學生管理”數據庫備份到d盤的mybackup文件夾下的“學生管理.bak”文件中。

--首先先創建一個備份設備

sp_addumpdevice'disk','mycopy1','d:\mybackup\學生管理.bak'

--用BACKUPDATABASE備份學生管理數據庫

BACKUPDATABASE學生管理

TOmycopy1WITHNAME='學生管理備份',DESCRIPTION='完全備份'

備份數據庫（續）

備份數據庫（續）差異數據庫備份即對最近一次完全數據庫備份結束以來發生改變的數據進行備份。當數據庫從上次備份以來只修改了很少的數據時，適合使用差異備份。格式：

BACKUPDATABASE數據庫名稱

TO<備份設備>[,...n]WITHDIFFERENTIAL[[,]NAME=備份集名稱][[,]DESCRIPTION='備份描述文本'][[,]{INIT|NOINIT}]

備份數據庫（續）文件或文件組備份當一個數據庫很大時，對整個數據庫進行備份可能會花費很多時間，這時可以采用文件或文件組備份，即對數據庫中的部分文件或文件組進行備份。格式：

BACKUPDATABASE數據庫名稱

<文件或文件組>[,...n]TO<備份設備>[,...n][WITHDIFFERENTIAL[[,]NAME=備份集名稱][[,]DESCRIPTION='備份描述文本'][[,]{INIT|NOINIT}]]<文件或文件組>::={FILE=邏輯文件名|FILEGROUP=邏輯文件組名}恢復數據庫恢復內容有：恢復整個數據庫，恢復部分數據庫，恢復數據庫文件或文件組，恢復數據庫事務日志等。（1）使用企業管理器執行恢復選擇“還原數據庫”命令，在圖形化界面中選擇數據庫名稱及需要還原的第一個備份，還原數據庫目的路徑等參數，如圖所示

恢復數據庫（續）（2）使用RESTOREDATABASE語句恢復數據庫格式：

RESTOREDATABASE數據庫名稱

[FROM<備份設備>[,...n]][WITH[[,]FILE=文件號][[,]MOVE'邏輯文件名'TO'物理文件名'][,...n][[,]{NORECOVERY|RECOVERY}][[,]REPLACE]]分離數據庫和附加數據庫使用分離數據庫和附加數據庫的方法能快速將數據庫從一臺服務器轉移到另一臺服務器上。分離數據庫將使數據庫文件（.MDF、.NDF和.LDF文件）與SQLServer脫離關系，這時無法在當前服務器上使用數據庫，但數據庫文件仍然存儲在磁盤上，可以將其復制到另一臺服務器上，然后在另一臺服務器上用附加數據庫的方法將數據庫文件附加到SQLServer上，這樣就可以在另一臺服務器上管理和使用該數據庫了。應用“所有任務”中的“分離數據庫”命令，再應用“分離數據庫”對話框中，如圖所示。實現數據庫的分離操作。7.3.3表的管理表的創建、修改、刪除

表的更新表的查詢

1.表的創建SQLServer系統中，可以使用CREATETABLE語句或企業管理器來創建表。（1）利用企業管理器創建新表在企業管理器中選擇“表”項，在彈出菜單中選擇“新建表”，打開表設計器。如圖所示表的創建、修改、刪除表的創建、修改、刪除（續）（2）使用CREATETABLE語句創建表CREATETABLE表名(

列名1數據類型和長度1列屬性1,

列名2數據類型和長度2列屬性2,......

列名n數據類型和長度n列屬性n,)【例】使用CREATETABLE語句在數據庫“學生管理”中創建“學生”表。

CREATETABLE學生

(

記錄編號intPRIMARYKEYIDENTITY(1,1),學號INTNOTNULL,姓名varchar(50)NOTNULL,

性別bitDEFAULT(0),

班級varchar(50)NOTNULL,

入學成績floatDEFAULT(0),

所屬院系intNOTNULL）表的創建、修改、刪除（續）表的創建、修改、刪除（續）2.修改表在SQLServer中可以使用企業管理器修改表也可以使用ALTERTABLE語句修改表。使用ALTERTABLE語句修改表定義向表中添加列

ALTERTABLE表名ADD列名數據類型和長度列屬性例:ALTERTABLE學生

ADD新增列varchar(50)NULL修改列屬性

ALTERTABLE表名

ALTERCOLUMN列名新數據類型和長度新列屬性例:ALTERTABLE學生

ALTERCOLUMN新增列char(30)NOTNULL刪除列

ALTERTABLE表名DROPCOLUMN列名例：ALTERTABLE學生DROPCOLUMN新增列表的創建、修改、刪除（續）3.刪除表刪除表就是將表中數據和表的結構從數據庫中永久性去除。表被刪除之后，就不能再恢復該表的定義。刪除表可以使用DROPTABLE語句來完成，該語句的語法形式如下：DROPTABLEtable_name例如：使用DROPTABLE語句刪除“學生”表。語句如下：

DROPTABLE學生注意使用DROPTABLE語句刪除表時，不需要用戶確認，因此在使用時要慎重。4.表約束（1）主鍵（PRIMARYKEY）約束主鍵是表中的一列或一組的列，它們的值可以惟一地標識表中的每一行。在創建和修改表時，可以定義主鍵約束。主鍵列的值不允許為空。可應用表設計器“索引/鍵”的“屬性”對話框查看、創建、修改和刪除主鍵，如右圖所示。表的創建、修改、刪除（續）（2）惟一性（UNIQUE）約束惟一性約束可以保證除主鍵外的其他一個或多個列的數據惟一性，以防止在列中輸入重復的值。可以在表設計器“屬性”對話框中選“創建UNIQUE”復選框，可以創建唯一性約束，如圖右所示。表的創建、修改、刪除（續）（3）外部鍵（FOREIGNKEY）約束外部鍵約束是用于建立兩個表之間的一列或多列之間的聯系。通過將當前表中的某一列或某幾列關聯到另一個表的主鍵列，可創建兩個表之間的連接。當前表中的列就成為外部鍵。外部鍵約束可以確保添加到外部鍵表中的任何行的外部鍵值在主表中都存在相應主鍵值，以保證數據的參照完整性。表的創建、修改、刪除（續）使用企業管理器創建外部鍵約束例如針對學生管理數據庫中“學生”表與“院系”表中的外部鍵約束的創建①在“學生管理”數據庫中，將“院系”表的“記錄編號”列設置為主鍵。②應用企業管理器打開“學生”表設計器③應用“屬性”對話框創建外部鍵約束，如圖所示。表的創建、修改、刪除（續）使用T-SQL語句創建外部鍵約束語法：

CONSTRAINT約束名

FOREIGNKEY(列名1[,列名2,...,列名n])REFERENCES關聯表(關聯列名1[,關聯列名2,...,關聯列名n])同上例：

ALTERTABLE學生

ADDCONSTRAINTFK_學生

FOREIGNKEY(所屬院系)REFERENCES院系(記錄編號)表的創建、修改、刪除（續）表的更新表的更新包括數據插入，數據刪除和數據修改。1.插入數據向表中插入數據，使用INSERT語句。該語句包括了兩個子句，即INSERT子句和VALUES子句。INSERT子句指定要插入數據的表名或視圖名稱，它可以包含表或視圖中列的列表。VALUES子句指定將要插入的數據。語法：

INSERTINTO表名[(列名1,列名2,...,列名n)]VALUES(值1,值2,...,值n)表的更新（續）【例】使用INSERT語句在“院系”表中添加一行數據，“院系名稱”列的值為“計算機學院”，“上級編號”列的值為0。

USE學生管理

INSERTINTO院系(院系名稱,上級編號)VALUES('計算機學院',0)

執行結果：（所影響的行數為1行）查詢結果如圖所示。表的更新（續）2.刪除數據使用DELETE語句刪除數據。DELETE語句可以從一個表中刪除一行或多行數據。語法1：刪除只涉及一個表

DELETEFROM表名WHERE刪除條件語法2：刪除涉及多個表如果要使用其他表的信息刪除當前表的行，可以使用以下格式的DELETE語句。

DELETE當前表名FROM其他表名WHERE條件其中，FROM子句用于指定刪除時用到的額外的表或視圖及聯接條件，該語句不從FROM子句指定的表中刪除行，而只從“當前表名”指定的表中刪除行。表的更新（續）【例7-17】使用DELETE語句從“學生”表中刪除所有“入學成績”小于30的數據。

USE學生管理

DELETEFROM學生WHERE入學成績<30【例7-20】刪除涉及多張表刪除院系名稱為“計算機”的學生信息，則需要用到兩張表

DELETE學生

FROM院系

WHERE學生.所屬院系=院系.記錄編號

AND院系.院系名稱='計算機'表的更新（續）3.更新數據使用UPDATE語句更新表中已經存在的數據。UPDATE語句既可以一次更新一行數據，也可以一次更新許多行，甚至可以一次更新表中的全部數據行。在UPDATE語句中，使用WHERE子句指定要更新的數據行滿足的基本條件，使用SET子句給出新的數據。新數據既可以是常量，也可以是指定的表達式。語法：

UPDATE表名

SET列名1=值1[,列名2=值2,...,列名n=值n][WHERE更新條件]【例7-21】使用UPDATE語句將“學生”表的所有記錄的入學成績加10。

USE學生管理

UPDATE學生SET入學成績=入學成績+10【例7-22】使用UPDATE語句將“學生”表中所有班級為“一班”的記錄的入學成績加10。

USE學生管理

UPDATE學生SET入學成績=入學成績+10WHERE班級='一班'表的更新（續）表的查詢在SQLServer平臺上同樣主要應用SELECT查詢語句進行表的查詢，此節介紹在SQLServer平臺上常用查詢實現語句及常用的函數的應用。（1）最基本的SELECT語句及應用格式：SELECT[all|distinct]<目標列表達式>]…

[TOPn[PERCENT]]FROM<表名或視圖名>[,<表名或視圖名>]…DISTINCT的作用是顯示惟一行，TOPn[PERCENT]的作用是顯示頭n行例：如果只需要顯示“學生”表中的前3行記錄，可以使用如下語句：

USE學生管理

SELECTTOP3*FROM學生表的查詢（續）（2）使用CASE函數CASE函數可以根據指定表達式的值返回多個可能的結果之一，格式：

CASE條件表達式

WHEN值1THEN結果1......ELSE其他結果

END例：對“學生”表中“性別”列的值進行處理，當值等于0時返回“女”，當值等于1時返回“男”。具體語句如下：

USE學生管理

SELECT姓名,CASE性別

WHEN0THEN'女'ELSE'男'ENDAS性別

FROM學生表的查詢（續）（3）使用聚合函數SELECT語句不僅可以顯示表或視圖中的列，還可以對列應用聚合函數，實現對表中指定數據的統計，如求總和、計數、求平均值等。（A）COUNT()函數：COUNT()函數可以用于統計記錄個數。【例7-28】統計“學生”表的記錄個數。

USE學生管理

SELECTCOUNT(*)AS記錄數量FROM學生（B）AVG()函數：AVG()函數可以統計指定表達式的平均值。【例7-29】統計“學生”表中所有學生的平均入學成績。

USE學生管理

SELECTAVG(入學成績)AS平均成績

FROM學生（C）SUM()函數：SUM()函數可以統計指定表達式的和。表的查詢（續）【例7-30】統計“學生”表中所有學生的入學成績之和。

USE學生管理

SELECTSUM(入學成績)AS成績之和

FROM學生（D）聚合函數與WHERE子句結合使用【例7-31】統計“學生”表中所有一班學生的平均入學成績。

USE學生管理

SELECTAVG(入學成績)AS平均成績

FROM學生WHERE班級='一班'表的查詢（續）（4）對查詢結果分組當SELECT子句中包含聚合函數時，可以使用GROUPBY子句對查詢結果進行分組統計，計算每組記錄的匯總值。【例7-32】在“學生”表中按性別統計所有學生的最高成績。

USE學生管理

SELECTCASE性別

WHEN0THEN'女'ELSE'男'ENDAS性別,MAX(入學成績)AS最高成績

FROM學生

GROUPBY性別表的查詢（續）（5）連接查詢當需要從多個表中提取數據，組合成一個結果集的查詢稱為連接查詢。例如，在“學生”表中“所屬院系”列的值是數字類型，不方便用戶查看，而該列的值對應于“院系”表中的“記錄編號”列。可以使用連接查詢，從表“學生”和表“院系”中同時獲取數據，從而可以顯示學生所在院系的名稱。連接查詢包括內連接、外連接和交叉連接等。內連接內連接使用比較運算符（最常使用的是等號，即等值連接），根據每個表共有列的值匹配兩個表中的行。只有每個表中都存在相匹配列值的記錄才出現在結果集中。在內連接中，所有表是平等的，沒有主次之分。表的查詢（續）【例7-36】使用內連接從“學生”表和“院系”表中同時獲取數據，查詢所有學生的姓名及其所在院系的名稱。

USE學生管理

SELECTt1.院系名稱,t2.姓名

FROM院系t1,學生t2WHEREt1.記錄編號=t2.所屬院系還可以使用INNERJOIN關鍵字來定義內部連接。例如，上面的SELECT語句也可以使用下面的語句來代替：

USE學生管理

SELECTt1.院系名稱,t2.姓名

FROM院系t1INNERJOIN學生t2ONt1.記錄編號=t2.所屬院系表的查詢（續）外連接與內連接相對，參與外連接的表有主次之分。以主表的每一行數據去匹配從表中的數據列，符合連接條件的數據將直接返回到結果集中，對那些不符合連接條件的列，將被填上NULL值后再返回到結果集中。外連接可以分為左向外連接、右向外連接和完整外部連接3種情況。①左向外連接左向外連接以連接（JOIN）子句左側的表為主表，主表中所有記錄都將出現在結果集中。如果主表中的記錄在右表中沒有匹配的數據，則結果集中右表的列值為NULL。可以使用*=符號定義左連接。表的查詢（續）【例7-37】使用左向外連接從“院系”表和“學生”表中同時獲取數據，查詢所有學生的姓名和所在院系名稱，其中表“院系”為主表。

USE學生管理

SELECTt1.院系名稱,t2.姓名

FROM院系t1,學生t2WHEREt1.記錄編號*=t2.所屬院系還可以使用LEFTOUTERJOIN或LEFTJOIN關鍵字定義左向外連接。例如，上例可以寫為：

USE學生管理

SELECTt1.院系名稱,t2.姓名

FROM院系t1LEFTJOIN學生t2ONt1.記錄編號=t2.所屬院系表的查詢（續）②右向外連接右向外連接以連接（JOIN）子句右側的表為主表，主表中所有記錄都將出現在結果集中。如果主表中的記錄在左表中沒有匹配的數據，則結果集中左表的列值為NULL。可以使用=*符號定義右向外連接。【例7-38】使用右向外連接從“院系”表和“學生”表中同時獲取數據，查詢所有學生的姓名和所在院系名稱，其中“院系”表為主表。

USE學生管理

SELECTt1.院系名稱,t2.姓名

FROM院系t1,學生t2WHEREt2.所屬院系=*t1.記錄編號表的查詢（續）③完整外部連接完整外部連接包括連接表中的所有行，無論它們是否匹配。在SQLServer中，還可以使用FULLOUTERJOIN或FULLJOIN關鍵字定義完整外部連接【例7-39】使用完整外部連接從“院系”表和“學生”表中同時獲取數據，查詢所有學生的姓名和所在院系名稱。

USE學生管理

SELECTt1.院系名稱,t2.姓名

FROM學生t2FULLJOIN院系t1ONt1.記錄編號=t2.所屬院系④使用ISNULL和ISNOTNULL在使用外連接時，結果集中會出現一些值為NULL的記錄。在查詢條件中使用ISNULL或ISNOTNULL關鍵字，可以查詢某字段值等于或不等于NULL的記錄。【例7-40】使用外連接查詢沒有學生的院系。

USE學生管理

SELECTt1.院系名稱,t2.姓名

FROM院系t1LEFTJOIN學生t2ONt1.記錄編號=t2.所屬院系

WHEREt2.姓名ISNULL表的查詢（續）（6）子查詢子查詢就是在一個SELECT語句中又嵌套了另一個SELECT語句。在WHERE子句和HAVING子句中都可以嵌套SELECT語句。（A）使用IN關鍵字連接子查詢【例7-41】查詢和小趙在同一個班的所有學生姓名，可以使用以下命令：

SELECT姓名

FROM學生

WHERE班級IN(SELECT班級

FROM學生

WHERE姓名='小趙')表的查詢（續）（B）使用等號連接子查詢【例7-42】要顯示軟件系的所有學生信息，可以使用以下命令：

USE學生管理

SELECT*FROM學生

WHERE所屬院系=(SELECT記錄編號

FROM院系

WHERE院系名稱='軟件系')表的查詢（續）表的查詢（續）（C）使用EXISTS關鍵字連接子查詢【例7-43】要顯示軟件系的所有學生信息，可以使用以下命令：

USE學生管理

SELECT*FROM學生

WHEREEXISTS(SELECT*FROM院系

WHERE記錄編號=學生.所屬院系

AND院系名稱='軟件系')表的查詢（續）（D）EXISTS關鍵字用于檢測子句的結果集是否為空。在HAVING子句中使用子查詢【例7-44】要顯示院系平均入學成績大于所有學生平均成績的記錄，可以使用以下命令：

USE學生管理

SELECT院系.院系名稱,AVG(學生.入學成績)FROM學生,院系

WHERE學生.所屬院系=院系.記錄編號

GROUPBY院系.院系名稱

HAVINGAVG(學生.入學成績)>(SELECTAVG(入學成績)FROM學生)表的查詢（續）（7）合并查詢合并查詢是將兩個或更多查詢的結果組合為單個結果集，該結果集包含聯合查詢中的所有查詢的全部行。合并查詢應用UNION運算符來實現。使用UNION運算符組合兩個查詢的結果集的兩個前提是：所有查詢結果中的列數和列的順序必須相同；對應列的數據類型必須兼容。合并查詢經常用來返回明細和統計信息。因為明細和統計信息需要分別統計，所以可以使用合并查詢將它們統一到一個結果集中。7.3.4視圖

創建視圖

修改視圖刪除視圖

視圖是關系數據庫系統提供給用戶以多種角度觀察數據庫中數據的重要機制。視圖是從一個或幾個基本表（或視圖）導出的表，它與基本表不同，是一個虛表。數據庫中只存放視圖的定義，而不存放視圖對應的數據，這些數據仍存放在原來的基本表中。所以基本表中的數據發生變化，從視圖中查詢出的數據也就隨之改變了。視圖兼有表和查詢的特點：與查詢相類似的是，視圖可以用來從一個或多個相關聯的表或視圖中提取有用信息；與表相類似的是，視圖可以用來更新其中的信息，并將更新結果永久保存在磁盤上。我們可以用視圖使數據暫時從數據庫中分離成為游離數據，以便在主系統之外收集和修改數據。概述創建視圖1.使用企業管理器創建視圖在SQLServer的企業管理器環境中，應用“視圖”目錄，選擇“新建視圖”，可創建視圖，如圖所示。2.使用CREATEVIEW語句創建視圖CREATEVIEW視圖名[(列名[,...n])][WITHENCRYPTION]ASSELECT語句說明：WITHENCRYPTION：對包含在系統表syscomments內的CREATEVIEW語句文本進行加密。SELECT語句：用于創建視圖的SELECT語句，利用SELECT語句可以從表或視圖中選擇列構成新視圖的列。

創建視圖（續）

創建視圖（續）【例7-49】使用CREATEVIEW命令創建加密視圖“軟件系學生簡表2”。

CREATEVIEW軟件系學生簡表2WITHENCRYPTIONASSELECT學生.姓名,學生.性別,學生.班級,院系.院系名稱

FROM學生INNERJOIN院系ON學生.所屬院系=院系.記錄編號

WHERE院系.記錄編號=修改視圖1.使用企業管理器修改視圖對于已經存在的視圖，可以在“視圖設計器”中對其進行修改。選擇“設計視圖”命令，就可以打開“視圖設計器”進行修改。2.使用ALTERVIEW語句修改視圖ALTERVIEW視圖名[(列名[,...n])][WITHENCRYPTION]ASSELECT語句刪除視圖在企業管理器中，選擇“刪除”命令，打開“除去對象”對話框，單擊“全部除去”按鈕，將視圖刪除。若使用DROPVIEW語句刪除視圖，基本語法如下：

DROPVIEW視圖名【例7-51】使用DROPVIEW語句刪除視圖“軟件系學生簡表”。

DROPVIEW軟件系學生簡表刪除視圖后，表和視圖所基于的數據并不受到影響。91

創建帶參數的存儲過程7.3.5存儲過程創建存儲過程

存儲過程的分類

執行存儲過程概述存儲過程是存儲在服務器上的預先編譯好的SQL語句和流程控制語句，用以實現某種任務（如查詢）。它可以在服務器上的SQLServer環境下運行。任何一組Transact-SQL語句構成的代碼塊，都可以作為存儲過程保存起來。它是在服務器端對數據庫中的數據進行處理，并將結果返回到客戶端。以此來避免從客戶端上多次連接并訪問數據庫的操作，減少了網絡上的傳輸量，同時也提高了客戶端的工作效率。SQLServer系統中，可以使用Transact-SQL語言編寫存儲過程，也可以使用CLR方式編寫存儲過程。使用CLR編寫存儲過程是SQLServer系統與.NET框架緊密集成的一種表現形式。存儲過程可以返回值，修改值，將系統欲請求的信息與用戶提供的值進行比較。它能識別數據庫，而且可以利用SQLServer優化器在運行時獲得最佳性能。概述

概述（續）使用存儲過程，只需創建存儲過程一次并將其存儲在數據庫中，客戶端程序可多次調用該存儲過程。存儲過程可獨立于程序源代碼而單獨修改。使用存儲過程可以減少網絡流量，還可以簡化數據庫管理及可作為安全機制使用。存儲過程優點：（1）性能存儲過程信息物理地存儲在同一系統中并運行于服務器上，執行時間短。具有對數據庫即時訪問效果。（2）客戶／服務器開發任務分離將客戶端和服務器端的開發任務分離，減少完成項目需要的時間。用戶可獨立開發服務器端組件而不涉及客戶端，但可在客戶方應用程序間重復使用服務器端組件。（3）安全性如同視圖，可使用存儲過程作為一種工具來加強安全性。可以創建存儲過程來完成所有增加、刪除和查詢操作，并可通過編程方式控制上述操作中對信息的訪問。（4）面向數據規則的服務器端措施使用智能數據庫引擎的最重要原因之一，存儲過程可利用規則和其他邏輯控制輸入系統的信息。在創建用戶系統時要切記客戶用服務器模型。數據管理工作由服務器負責，因為報表和查詢所需的數據表述和顯示的操作在理想模型中應駐留在客戶方。從而優化了用戶處理應用程序的過程。

概述（續）存儲過程分類系統存儲過程：SQLServer內置的存儲過程，存儲在master庫中，主要用于執行SQLServer的某些管理功能、顯示有關數據庫和用戶的信息。系統存儲過程名以SP_開頭，可以在任何數據庫中執行系統存儲過程。用戶存儲過程：用戶自行創建并存儲在用戶數據庫中的存儲過程。臨時存儲過程：分為局部臨時存儲過程和全局臨時存儲過程。局部臨時存儲過程名稱以#開頭，存放在tempdb數據庫中，只由創建并連接的用戶使用，當該用戶斷開連接時將自動刪除局部臨時存儲過程。全局臨時存儲過程名稱以##開頭，存放在tempdb數據庫中，允許所有連接的用戶使用，在所有用戶斷開連接時自動被刪除。遠程存儲過程：位于遠程服務器上的存儲過程。擴展存儲過程：利用外部語言（如C）編寫的存儲過程，以彌補SQLServer的不足之處，擴展新的功能創建存儲過程存儲過程的語法格式如下：CREATEPROCEDUREprocedure_name[:number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…n][WITH{RECOMPILE│ENCRYPTION│RECOMPILE,ENCRYPTION}][FORREPLICATION]AS

Sql_statement[,…n]說明：

procedure_name（存儲過程名）和Sql_statement（包含在存儲過程中的任何合法的SQL語句）兩個參數必須傳遞給CREATEPROCEDURE語句。number（編號）：可選整數，用來對同名的存儲過程分組，以便用一條DROPPROCEDURE語句即可將同組的存儲過程一起刪除。@parameterdata_type表示存儲過程中定義的局部變量parameter，類型為data_type。OUTPUT表示允許用戶將數據直接返回到在其他處理過程中要用到的變量中。返回值是當存儲過程執行完成時參數的當前值。[WITH{RECOMPILE│ENCRYPTION│RECOMPILE,ENCRYPTION}][FORREPLICATION]允許用戶選擇任何存儲過程和執行過程。RECOMPILE：表明不保存該存儲過程的執行計劃，該存儲過程將在運行時重新編譯。ENCRYPTION：指定SQLServer對syscomments表中包含本CREATEPROCEDURE語句文本的條目進行加密AS：用于指定該存儲過程要執行的操作。SQL語句：存儲過程中要包含的Transact-SQL語句。

創建存儲過程（續）

創建存儲過程（續）【例7-52】創建存儲過程“增加成績”，將表“學生”中所有學生的入學成績增加10%。

USE學生管理

GOCREATEPROCEDURE增加成績

ASUPDATE學生SET入學成績=入學成績+10GO執行存儲過程使用EXECUTE語句執行存儲過程。EXECUTE語句也可以簡寫為EXEC。執行存儲過程的格式如下：[[EXEC[UTE]]{[@返回狀態=]{存儲過程名|@存儲過程名變量}}[[@參數名稱=]{值|@變量[OUTPUT]|[DEFAULT]}][,...n][WITHRECOMPILE]說明：返回狀態：是一個可選的整型變量，保存存儲過程的返回狀態。這個變量在用于EXECUTE語句前，必須在批處理、存儲過程或函數中聲明過。存儲過程名：要調用的存儲過程的名稱。

@存儲過程名變量：局部變量名，代表存儲過程的名稱。

@參數名稱：存儲過程的參數，在CREATEPROCEDURE語句中定義。參數名稱前必須加上符號@。在使用格式“@參數=值”時，參數名稱和常量不一定按照CREATEPROCEDURE語句中定義的順序出現。但是，如果有一個參數使用“@參數=值”格式，則其他所有參數都必須使用這種格式。值：過程中參數的值。如果沒有指定參數名稱，參數值必須以CREATEPROCEDURE語句中定義的順序給出。

執行存儲過程（續）

執行存儲過程（續）@變量：是用來保存參數或者返回參數的變量。OUTPUT：指定存儲過程必須返回一個參數。該存儲過程的匹配參數也必須由關鍵字OUTPUT創建。使用游標變量作參數時使用該關鍵字。DEFAULT：根據存儲過程的定義，提供參數的默認值。當過程需要的參數值沒有事先定義好的默認值，或缺少參數，或指定了DEFAULT關鍵字時，就會出錯。WITHRECOMPILE：強制編譯新的計劃。如果所提供的參數為非典型參數或者數據有很大的改變，使用該選項。在以后的程序執行中使用更改過的計劃。該選項不能用于擴展存儲過程。創建帶參數的存儲過程存儲過程的參數分為輸入參數和輸出參數，輸入參數用于向存儲過程中帶入數據，而輸出參數則能將存儲過程中的數據返回到調用程序。定義存儲過程時,可以同時指定參數,格式如下：

@參數名數據類型[=默認值][OUTPUT][,...n]如果參數后面使用OUTPUT關鍵字，則表明它是輸出參數。【例7-54】創建存儲過程add_proc，用于計算兩個參數之和并將其輸出。

CREATEPROCEDUREadd_proc@num1INT=0,@num2INT=0ASDECLARE@num3INTSET@num3=@num1+@num2PRINT@num3【例7-56】創建存儲過程AvgScore，用于根據給定的院系和班級名稱計算平均成績，并使用輸出參數返回結果。

CREATEPROCEDUREAvgScore@orgvarchar(100),--院系名稱，輸入參數

@classvarchar(50),--班級名稱，輸入參數

@scorefloatOUTPUT--成績，輸出參數

ASDECLARE@orgid

intSET@orgid=0--根據參數中指定的院系名稱org,獲取院系編號

創建帶參數的存儲過程（續）SELECT@orgid=記錄編號

FROM院系WHERE院系名稱=@orgIF@orgid=0BEGINSET@score=0PRINT'指定的院系記錄不存在'ENDELSEBEGINSELECT@score=AVG(入學成績)FROM學生

WHERE所屬院系=@orgidAND班級=@classGROUPBY所屬院系,班級

END

創建帶參數的存儲過程（續）105

7.3.6觸發器

觸發器工作原理

創建觸發器觸發器概述觸發器概述觸發器是一種特殊類型的存儲過程，它包括了大量的Transact-SQL語句。但觸發器又與存儲過程不同，存儲過程可以由用戶直接調用執行，而觸發器不能被直接調用執行，它只能自動執行。SQLServer有效地管理信息的能力來源于它在系統中控制數據的能力。存儲過程的建立，使用戶能夠在服務器上執行邏輯，通過規則和默認值去幫助數據庫更進一步地管理信息。SQLServer在信息被寫入數據庫之前確認規則和默認值。這對于信息是一種“預過濾器”，并且能基于數據項控制數據庫活動的作用阻止數據項的活動。觸發器是在數據更新后執行的“后置過濾器”，并且SQLServer已經確認了這些規則、默認值等。觸發器是SQLServer執行的特殊類型的存儲過程，它發生在對于一個給定表的插入、修改或刪除操作執行后。觸發器最普遍的應用是實施數據庫中的商務規則，在維持引用完整性方面，外鍵要比觸發器更快，但觸發器能夠維持那些外鍵所不能處理的復雜關系。觸發器不會明顯影響服務器的性能。經常被用于增強那些在其他的表和行上進行很多級聯操作的應用程序的功能。觸發器可以限制向表中插入無效的數據，與CHECK約束的功能相似。但在CHECK約束中不能使用到其他表中的字段，而在觸發器中則沒有此限制。例如，可以在表“學生”中定義觸發器，限制插入的記錄其“院系編號”字段值必須在表“院系”中存在對應的記錄。觸發器可以對數據庫中的相關表進行級聯更改。例如，可以在表“院系”中定義觸發器，當用戶刪除表“院系”中的記錄時，觸發器將刪除表“學生”中對應院系的記錄。

觸發器概述（續）觸發器與存儲過程的區別：（1）觸發器是自動執行的，而存儲過程需要顯示調用才能執行。（2）觸發器是建立在表或視圖之上的，而存儲過程是建立在數據庫之上的。刪除表就刪除了該表之上的所有觸發器，與該表有關的存儲過程仍然存在，只有刪除數據庫才能刪除該數據庫上的所有存儲過程。

觸發器概述（續）觸發器的主要優點如下：（1）觸發器可通過數據庫中的相關表實現級聯更改。（2）觸發器可以防止惡意或錯誤的INSERT、UPDATE以及DELETE操作，并強制執行比CHECK約束定義的限制更為復雜的其他限制。與CHECK約束不同，觸發器可以引用其他表中的列。（3）觸發器可以評估數據修改前后表的狀態，并根據該差異采取措施。

觸發器概述（續）觸發器工作原理

觸發器在數據庫中發生數據操作語言(DML)事件時將啟用。DML事件包括在指定表或視圖中修改數據的INSERT語句、UPDATE語句或DELETE語句當創建一個DML觸發器時必須指定如下選項：①名稱；②在其上定義觸發器的表；③觸發器將何時激發；④激活觸發器的數據修改語句，有效選項為INSERT、UPDATE或DELETE，多個數據修改語句可激活同一個觸發器；⑤執行觸發操作的編程語句。觸發器使用deleted和inserted邏輯表:①Deleted表用于存儲delete，update語句所影響的行的副本。在執行delete或update語句時，行從觸發器表中刪除，并傳輸到deleted表中。②Inserted表用于存儲Insert或update語句所影響的行的副本，在一個插入或更新事務處理中，新建的行被同時添加到Inserted表和觸發器表中。Inserted表中的行是觸發器表中新行的副本。

觸發器工作原理（續）觸發器的類型：①insert觸發器：在表或視圖執行插入記錄操作時觸發。