SQL Server 2005編程入門

第一章 SQL Server數(shù)據(jù)庫構(gòu)成

1.1數(shù)據(jù)庫中的對象

數(shù)據(jù)庫管理系統(tǒng)中包含許多對象。對于SQL Server，它常包含以下重要的數(shù)據(jù)庫對象：

數(shù)據(jù)庫索引事務(wù)日志程序集表報(bào)表文件集全文本目錄圖表用戶自定義數(shù)據(jù)類型視圖角色存儲過程用戶用戶自定義函數(shù)

1.2 SQL Server的4個(gè)系統(tǒng)數(shù)據(jù)庫

在給定的SQL Server中，數(shù)據(jù)庫實(shí)際上是最高層對象。在SQL Server中，大部分其他對象為數(shù)據(jù)庫對象的子對象。安裝好的SQL Server第一次啟動時(shí)包含4個(gè)系統(tǒng)數(shù)據(jù)庫：

• 主數(shù)據(jù)庫（master）——主數(shù)據(jù)庫保存一組特殊的表（系統(tǒng)表）以用于系統(tǒng)的總體控制。
• 模型數(shù)據(jù)庫（model）——模型數(shù)據(jù)庫是指可以基于該模型得到一個(gè)副本。模型數(shù)據(jù)庫構(gòu)成新建數(shù)據(jù)庫的模版。也就是說，如果想要改變新建數(shù)據(jù)庫的樣式，則可以根據(jù)需要更改模型數(shù)據(jù)庫。注意：由于模型數(shù)據(jù)庫作為其他任意數(shù)據(jù)庫的模版，因此系統(tǒng)中必須保留該數(shù)據(jù)庫，禁止刪除它。
• msdb——msdb數(shù)據(jù)庫是SQL代理進(jìn)程保存任意系統(tǒng)作業(yè)的場所，如計(jì)劃對一數(shù)據(jù)庫在每夜進(jìn)行備份和執(zhí)行一次計(jì)劃好的存儲過程。
• tempdb——tempdb數(shù)據(jù)庫是服務(wù)器主要工作區(qū)域之一。只要執(zhí)行一個(gè)復(fù)雜或者大型的查詢操作，則SQL Server需要建立一些中間表，而建立的中間表就是在tempdb數(shù)據(jù)庫中。只要建立臨時(shí)表，則這些表會建立在tempdb數(shù)據(jù)庫中，即使您是在當(dāng)前數(shù)據(jù)庫中建立的這些表。只要需要臨時(shí)保存數(shù)據(jù)，則很可能是將數(shù)據(jù)保存在tempdb數(shù)據(jù)庫中。tempdb數(shù)據(jù)庫與其他任意數(shù)據(jù)庫不同。不僅數(shù)據(jù)庫中的對象是臨時(shí)的，連數(shù)據(jù)庫本身也是臨時(shí)的。在每次SQL Server啟動時(shí)，tempdb數(shù)據(jù)庫會被完全重建。

1.3 最基本的對象：表

表由稱為域的數(shù)據(jù)（列）和實(shí)體數(shù)據(jù)（行）構(gòu)成。數(shù)據(jù)庫中實(shí)際的數(shù)據(jù)都存儲在表中。表的定義也包含了描述表中包含數(shù)據(jù)的類型，即元數(shù)據(jù)。每一列具有該列可存儲數(shù)據(jù)類型的一組規(guī)則。

1.4 索引

索引是僅在特定表或視圖架構(gòu)內(nèi)存在的對象。索引的功能非常類似百科全書中的目錄。索引中有以某一特定方式排序的查找值，使用索引可以快速查找數(shù)據(jù)庫中的實(shí)際信息。

索引分為兩類：

• 集群索引——每一個(gè)表只能有一個(gè)集群索引。如果是集群索引，其含義為：集群索引對應(yīng)的表按照其索引進(jìn)行物理排序。如果為百科全書做索引，則集群索引是書的頁碼；按頁碼順序保存百科全書中的信息。
• 非集群索引——每一個(gè)表可以有多個(gè)非集群索引。非集群索引的含義與普通"索引"的含義更接近。如百科全書，非集群索引指的是百科全書后面的關(guān)鍵字目錄。

1.5 觸發(fā)器

觸發(fā)器是存在于表框架內(nèi)的對象。觸發(fā)器是在表操作時(shí)（如進(jìn)行插入、更新或刪除等）自動執(zhí)行的一段邏輯代碼。觸發(fā)器有多種用途，但主要用于在插入時(shí)復(fù)制數(shù)據(jù)或更新時(shí)檢查數(shù)據(jù)，確保數(shù)據(jù)滿足相應(yīng)標(biāo)準(zhǔn)。

1.6 約束

約束是僅在表的限制中存在的另一對象。約束就是限制表中數(shù)據(jù)滿足的某種條件。約束在某種方式上類似觸發(fā)器，盡可能解決數(shù)據(jù)完整性問題，但他們有所不同，各自具有不同的優(yōu)點(diǎn)。

1.7 文件組

數(shù)據(jù)庫中所有的表及其他對象（日志除外）都存儲在文件中。這些文件組成了一些所謂的文件組。每個(gè)文件組中可以有超過32000個(gè)文件。一個(gè)數(shù)據(jù)庫僅能有一個(gè)主要文件組，可以有最多255個(gè)輔助文件組。

1.8 視圖

視圖是一種虛擬表。除了視圖本身不包含任意數(shù)據(jù)外，視圖的使用基本與表的使用類似。事實(shí)上視圖僅僅是存儲在表中的數(shù)據(jù)的映射和表示，它以查詢的形式存儲在數(shù)據(jù)庫中。應(yīng)用視圖的主要目的是控制用戶所要顯示的數(shù)據(jù)。這有兩方面的原因：安全和易于使用。

1.9 存儲過程

存儲過程是SQL Server編程功能的基礎(chǔ)。存儲過程通常是邏輯單元中的Transact-SQL語句的有序集合。存儲過程允許使用變量和參數(shù)，也可使用選擇和循環(huán)結(jié)構(gòu)。與單條語句相比，服務(wù)器中使用存儲過程有一下幾個(gè)優(yōu)點(diǎn)：

• 不使用長SQL語句字符串而使用短存儲過程名，可減少運(yùn)行存儲過程中的代碼所要的網(wǎng)絡(luò)傳輸。
• 預(yù)先優(yōu)化和編譯，節(jié)省存儲過程每次運(yùn)行的時(shí)間。
• 通常考慮安全原因，或僅僅是簡化數(shù)據(jù)庫的復(fù)雜性，可以將過程封裝。
• 可以調(diào)用其他的存儲過程，使得它們可以在有限的意義上重用。

但是要注意，存儲過程不是函數(shù)，它的返回值只能為整數(shù)。當(dāng)存儲過程成功執(zhí)行后會默認(rèn)返回0。完全可以忽略它的返回值，但如果需要根據(jù)返回值確定存儲過程是否成功執(zhí)行的話，需要在存儲過程的定義中指明返回值。從這點(diǎn)來說，存儲過程更像是一個(gè)可執(zhí)行程序，會根據(jù)執(zhí)行情況返回0或其他值。

1.10 用戶自定義函數(shù)

用戶自定義函數(shù)（UDF）更符合傳統(tǒng)意義上的函數(shù)的概念。它和存儲過程的不同處有以下幾點(diǎn)：

• 返回值的數(shù)據(jù)類型包括大部分SQL Server數(shù)據(jù)類型。不包括的返回值類型是：text、ntext、image、cursor及timestamp。
• 基本沒有"副作用"，即用戶自定義函數(shù)不能完成在其范圍之外的功能，比如更改表、發(fā)電子郵件或更改系統(tǒng)或數(shù)據(jù)庫參數(shù)。
• UDF類似于編程語言中使用的函數(shù)。函數(shù)可以有多個(gè)輸入變來那個(gè)，可以有一個(gè)返回值。UDF中，傳送到函數(shù)的所有變量都是按值傳遞。UDF還可以返回一種特殊類型的數(shù)據(jù)類型——表。

1.11 用戶和角色

用戶和角色相互并存。用戶（user）等價(jià)于登錄。簡言之，該對象表示登錄SQL Server的標(biāo)識符。登錄SQL Server的任何人都映射到一個(gè)用戶。用戶屬于一個(gè)或多個(gè)角色（role）。SQL Server中可以直接賦予用戶或角色執(zhí)行某操作的權(quán)限，一個(gè)或多個(gè)用戶可屬于同一角色。

1.12 規(guī)則

規(guī)則和約束（CHECK）都是限制插入到表中的數(shù)據(jù)類型的信息。與規(guī)則不同的是，約束本身并不是對象，而是描述特定表的多個(gè)元數(shù)據(jù)。規(guī)則是為了向下兼容的需要，在新的開發(fā)中應(yīng)該使用CHECK約束，避免使用規(guī)則。

1.13 默認(rèn)值

SQL Server中有兩種類型的默認(rèn)值。包括對象本身的默認(rèn)值，以及表中特定列的元數(shù)據(jù)的默認(rèn)值（而非真正對象）。與此非常類似，約束是針對對象，而規(guī)則是針對元數(shù)據(jù)。當(dāng)插入一條記錄時(shí)，如果沒有提供該列的值，且該列具有其默認(rèn)值，則自動插入默認(rèn)值。

1.14 用戶自定義的數(shù)據(jù)類型

用戶自定義的數(shù)據(jù)類型是系統(tǒng)定義數(shù)據(jù)類型的擴(kuò)展。自SQL Server 2005版本開始，用戶自定義數(shù)據(jù)類型幾乎可定義任意數(shù)據(jù)。

1.15 SQL Server 2005中的數(shù)據(jù)類型

數(shù)據(jù)類型名	類	長度（以字節(jié)為單位）	數(shù)據(jù)特點(diǎn)
Bit	整形	1	表中的第一個(gè)Bit數(shù)據(jù)類型占1個(gè)字節(jié)；其余7個(gè)位也用作Bit數(shù)據(jù)類型。允許空格使其占用一個(gè)額外的字節(jié)。
Bigint	整形	8	可處理日常用到的越來越大的數(shù)，其取值范圍為-263~263-1。
Int	整形	4	取值范圍為-2147483648~ 2147483647。
SmallInt	整形	2	取值范圍-32768~32768。
TinyInt	整形	1	取值范圍0~255。
Decimal/Numeric	數(shù)字型	可變	固定精度，取值范圍為-1038-1~1038-1。
Money	貨幣	8	貨幣單位，取值范圍為-263~263，精確到4個(gè)小數(shù)位。注意貨幣單位可以是任意貨幣，不限于美元。
SmallMoney	貨幣	4	貨幣單位，取值范圍為-214748.3648~214748.3647。
Float(Real)	近似小數(shù)	可變	由一參數(shù)（如Float(20)）決定其長度與精度。注意參數(shù)值表示位數(shù)，不是字節(jié)數(shù)。
DateTime	日期/時(shí)間	8	日期與時(shí)間，取值范圍為1753年1月1日~9999年12月31日，精確到0.03秒。
SmallDateTime	日期/時(shí)間	4	日期與時(shí)間，取值范圍為1900年1月1日~2079年6月6日，精確到分鐘。
Cursor	特殊小數(shù)	1	指向光標(biāo)的指針，只占用一個(gè)字節(jié)，記住組成實(shí)際光標(biāo)的結(jié)果集也占內(nèi)存，占用內(nèi)存的大小取決于結(jié)果集。
Timestamp/rowversion	特殊小數(shù)（二進(jìn)制）	8	給定數(shù)據(jù)庫的唯一特定值。即使UPDATE語句沒有timestamp列（時(shí)間標(biāo)記），但其值在插入或更新記錄的時(shí)間自動由數(shù)據(jù)庫設(shè)定（不允許直接更新timestamp對象）。
UniqueIdentifier	特殊小數(shù)（二進(jìn)制）	16	全球唯一標(biāo)識符（GUID），必須保證在內(nèi)存空間和時(shí)間內(nèi)唯一。
Char	字符	可變	定長字符數(shù)據(jù)。比設(shè)定長度短時(shí)使用空格填充，為非Unicode數(shù)據(jù)，最大長度為8000字符。
VarChar	字符	可變	長度可變的字符數(shù)據(jù)。按需存儲，為非Unicode數(shù)據(jù)。允許最大長度為8000字符，但使用max關(guān)鍵字（varchar(max)）時(shí)表示其長度可足夠大（231字節(jié)）。
Text	字符	可變	SQL Server 2005保持向后兼容的需要?？墒褂胿archar(max)代替。
NChar	Unicode字符	可變	定長Unicode字符數(shù)據(jù)。最大長度為4000字符，比設(shè)定長度短時(shí)使用空格填充。
NVarChar	Unicode字符	可變	長度可變的Unicode字符數(shù)據(jù)。按需存儲。允許最大長度為8000字符，但使用max關(guān)鍵字（nvarchar(max)）時(shí)表示其長度可足夠大（231字節(jié)）。
NText	Unicode字符	可變	SQL Server 2005保持向后兼容的需要?？墒褂胣varchar(max)代替。
Binary	二進(jìn)制	可變	定長二進(jìn)制數(shù)，最大長度為8000字節(jié)。
VarBinary	二進(jìn)制	可變	可變長度二進(jìn)制數(shù)，最大特定長度為8000字節(jié)，可使用max關(guān)鍵字（varbinary(max)）使其作為大對象字段（可達(dá)231字節(jié)）。
Image	二進(jìn)制	可變	SQL Server 2005保持向后兼容的需要?？墒褂胿arbinary(max)代替
Table	其他	-	主要用于結(jié)果集，通常作為用戶自定義函數(shù)返回。在表的定義中不作為可用的數(shù)據(jù)類型。
SQL_Variant	其他	-	用于保存SQL Server數(shù)據(jù)類型的容器。當(dāng)列或函數(shù)需要處理多種數(shù)據(jù)類型時(shí)可使用這種數(shù)據(jù)類型。
XML	字符	可變	定義一字符字段用作XML數(shù)據(jù)。提供不符合XML模式的數(shù)據(jù)而面向XML函數(shù)的使用的功能。

說明：SQL Server中沒有無符號整數(shù)類型。

1.16 Server對象標(biāo)識符

SQL Server中的所有對象都需要命名，即使在創(chuàng)建時(shí)沒有指定名稱（如表中的約束），SQL Server也會自動生成一個(gè)名稱。

SQL Server中的命名規(guī)則非常簡單，規(guī)則允許名字中內(nèi)嵌空格，甚至允許名字是關(guān)鍵字。主要的命名規(guī)則如下：

• 對象的名字必須以Unicode 2.0規(guī)范定義的任意字母開頭。大小寫是否敏感取決于服務(wù)器配置的方式。
• 正常對象的名字最多128個(gè)字符，臨時(shí)對象的名字最多116個(gè)字符。
• 與SQL Server關(guān)鍵字相同或包含內(nèi)嵌空格的名字必須使用雙引號（""）或方括號（[]）。哪一個(gè)視為關(guān)鍵字取決于設(shè)置數(shù)據(jù)庫的兼容水平。

注意：

只有在設(shè)置了SET QUOTED IDENTIFIERON，雙引號才可以作為列名中的分界符。特定的對象類型還存在其他命名規(guī)則。

第2章 T-SQL語言基礎(chǔ)

T-SQL是SQL Server的結(jié)構(gòu)化查詢語言的"方言"。T-SQL語言遵守公共語言運(yùn)行庫（CLR），簡言之，T-SQL為.NET語言。SQL Server 2005可以使用任何.NET語言來訪問數(shù)據(jù)庫，而T-SQL只保留了操作SQL Server的核心功能。

2.1 基本SELECT語句

SELECT語句的基本語法規(guī)則如下：

SELECT <column list>

[FROM <source tables>]

[WHERE <restrictive condition>]

[GROUP BY <column name or expression using a column in the SELECT list>]

[HAVING <restrictive condition based on the GROUP BY results>]

[ORDER BY <column list> [ASC|DESC]]

2.2 WHERE子句中的邏輯運(yùn)算符

運(yùn)算符	實(shí)例	功能
=, >, <, >=, <=, <>, !=, !>, !<	<Column Name> = <Other Column Name> <Column Name> = 'Bob'	標(biāo)準(zhǔn)的比較運(yùn)算符。要注意 1. "大于"、"小于"和"等于"可能因情況不同而改變。如比較字符串時(shí)是否區(qū)分大小寫。 2. !=和<>都表示"不等于"，而!<和!>分別表示"不小于"和"不大于"。
AND, OR, NOT	<Column1> = <Column2> AND <Column3> >= <Column4> <Column1> != "MyLiteral" OR <Column2> = "MyOtherLiteral"	標(biāo)準(zhǔn)的邏輯運(yùn)算符。運(yùn)算優(yōu)先級為NOT、AND、OR。
BETWEEN	<Column> BETWEEN 1 AND 5	第一個(gè)值在第二個(gè)與第三個(gè)值之間時(shí)其值為TRUE，其等價(jià)于A>=B AND A <= C。
LIKE	<Column> Like "ROM%"	可使用%和_作為通配符。%表示可以和任意長度的字符串匹配。_表示和任意的單個(gè)字符匹配。[]指定一個(gè)字符、字符串或范圍，匹配其中的任一個(gè)對象。[^]匹配指定字符串以外的任意字符。
運(yùn)算符	實(shí)例	功能
IN	<Column> IN　(List of Numbers)	IN左邊的表達(dá)式與IN右邊的列表中的任意值匹配時(shí)返回TRUE。
ALL, ANY, SOME	<Column|Expression> 比較運(yùn)算符 <ANY|SOME>(子查詢)	子查詢中的條件全部/任一滿足比較運(yùn)算符時(shí)返回TRUE。ALL指表達(dá)式要匹配結(jié)果集中的所有值。ANY和SOME相同，在表達(dá)式中匹配結(jié)果集中的任一值時(shí)返回TRUE。
EXISTS	EXISTS (子查詢)	子查詢返回至少一行記錄時(shí)為TRUE。

2.3 常見的統(tǒng)計(jì)函數(shù)

函數(shù)	說明
SUM()	求和
COUNT()	統(tǒng)計(jì)返回的行數(shù)（除非使用COUNT(*)，否則會忽略NULL值）
AVG()	計(jì)算平均值
MIN()	計(jì)算最小值
MAX()	計(jì)算最大值

2.4 DISTINCT和ALL謂詞

DISTINCT和ALL均放在SELECT的后面。DISTINCT表示去除重復(fù)的行，ALL表示保留重復(fù)的行。

SELECT語句默認(rèn)是保留重復(fù)行的，使用SELECT DISTINCT <columns...>將返回沒有重復(fù)的結(jié)果集（每行多個(gè)字段整體沒有重復(fù)，而不是單個(gè)字段沒有重復(fù)）。DISTINCT還可應(yīng)用與統(tǒng)計(jì)函數(shù)中，表示統(tǒng)計(jì)時(shí)首先去除重復(fù)的行，所以"COUNT(DISTINCT OrderID)"將比"COUNT(OrderID)"返回的行更少。但是在AVG函數(shù)中使用DISTINCT沒有任何意義。

ALL用于保留重復(fù)的行，這是SELECT語句的默認(rèn)設(shè)置。但在使用UNION語句時(shí)默認(rèn)會去除重復(fù)行，這是可以使用ALL指定保留（"SELECT... UNION ALL SELECT..."）。

2.5 使用INSERT語句添加數(shù)據(jù)

INSERT語句的語法如下：

INSERT [INTO] <table> [(column_list)]

VALUES (data_values)

注意：

• 在插入中可以使用DEFAULT關(guān)鍵字指定列使用默認(rèn)值，使用NULL關(guān)鍵字指定列為NULL值。
• 如果要插入和數(shù)據(jù)與表的每列一一對應(yīng)，插入語句可以忽略列名列表可選項(xiàng)。
• 插入數(shù)值類型數(shù)據(jù)不需要使用引號，而插入字符串或者日期型數(shù)據(jù)時(shí)需要使用引號。
• 常用的日期型數(shù)據(jù)格式為MM/DD/YYYY以及YYYY-MM-DD。

2.6 存儲過程sp_help

存儲過程sp_help的功能是給出任意數(shù)據(jù)庫對象、用戶定義的數(shù)據(jù)類型或SQL Server數(shù)據(jù)類型的信息。執(zhí)行存儲過程sp_help的語法結(jié)構(gòu)如下：

EXEC sp_help <name>

要查看sales表的屬性，只要輸入一下命令：

EXEC sp_help sales

2.7 INSERT INTO... SELECT語句

INSERT INTO... SELECT語句可完成一次插入一個(gè)數(shù)據(jù)塊的功能。其語法結(jié)構(gòu)為INSERT語句與SELECT語句語法結(jié)構(gòu)的組合，如下：

INSERT INTO <table_name>

[<column list>]

<SELECT statement>

由SELECT語句產(chǎn)生的結(jié)果集為INSERT語句中插入的數(shù)據(jù)。

2.8 用UPDATE語句更改數(shù)據(jù)

UPDATE語句的語法結(jié)構(gòu)如下：

UPDATE <table_name>

SET <column> = <value> [,<column> = <value>]

[FROM <source table(s)>]

[WHERE <restrictive condition>]

示例：

UPDATE stores

SET city = 'There'

WHERE stor_id = 'TEST'

此外，SET子句還可以使用表達(dá)式：

UPDATE titles

SET price = price * 1.1

WHERE title_id LIKE 'BU%'

2.9 DELETE語句

語法結(jié)構(gòu)如下：

DELETE <table_name>

[WHERE <search condition>]

SQL Server不允許刪除作為外鍵約束引用的行。如果一行使用外鍵約束引用另一行，則要先刪除被引用行后才能刪除引用行。

第3章 連接

3.1 幾種形式的JOIN子句

• 內(nèi)部連接（INNER JOIN）
• 外部連接（<LEFT|RIGHT> [OUTER] JOIN）
• 完全連接（FULL JOIN）
• 交叉連接（CROSS JOIN）

3.2 連接的語法結(jié)構(gòu)

SELECT <select list>

FROM <first_table> <join_type> <second_table>

[ON <join_condition>]

3.3 別名

使用AS關(guān)鍵字（可以省略）給列或者表取別名。同一個(gè)查詢中的多個(gè)表中，可以選擇哪些表使用別名，哪些表不使用別名，代碼中別名和表名可以混合使用，但是只要確定了使用表的別名，則這個(gè)表必須一直使用別名。

3.4 內(nèi)部連接（INNER JOIN）

內(nèi)部連接根據(jù)一個(gè)或幾個(gè)相同的字段將記錄匹配在一起，僅僅返回那些匹配的記錄。示例：

SELECT *

FROM Products p

INNER JOIN Suppliers s

ON p.SupplierID = s.SupplierID

3.5外部連接（OUTER JOIN）

外部連接語法結(jié)構(gòu)：

SELECT <select list>

FROM <left table>

<LEFT|RIGHT> [OUTER] JOIN <right table>

ON <join condition>

LEFT OUTER JOIN會使LEFT表中的所有記錄都包含到結(jié)果集中，即使在RIGHT表中相沒有匹配的記錄。而RIGHT OUTER JOIN會使RIGHT表中的所有記錄都包含到結(jié)果集中，即使在LEFT表中相沒有匹配的記錄。

注意NULL值無法連接NULL值。因?yàn)镹ULL值和NULL值是不相等的。

示例：

USE Northwind

SELECT c.CustomerID, CompanyName

FROM Customers c

LEFT JOIN Orders o

ON c.CustomerID = o.CustomerID

WHERE o.CustomerID IS NULL

3.6 完全連接（FULL JOIN）

完全連接用來將JOIN兩側(cè)的數(shù)據(jù)完全匹配，并返回所有的記錄，無論是記錄在JOIN的哪一側(cè)表中。完全連接的目的是返回沒有參考的記錄之間的所有關(guān)系，要返回的是連接兩側(cè)表的所有記錄，而且不丟棄任何記錄。

3.7 交叉連接（CROSS JOIN）

交叉連接不使用ON運(yùn)算符，并將CROSS JOIN的左側(cè)的所有記錄與右側(cè)的所有記錄連接。簡言之，返回的是JOIN兩側(cè)的笛卡爾積。

示例：

SELECT v.VendorName, a.Address

FROM Vendors v

CROSS JOIN Address a

3.8 聯(lián)合（UNION）

UNION用于將一個(gè)特殊的運(yùn)算符，用于將兩個(gè)或兩個(gè)以上的查詢產(chǎn)生一個(gè)結(jié)果集。使用UNION處理查詢時(shí)，要注意以下關(guān)鍵幾點(diǎn)：

• 所有UNION的查詢必須在SELECT列表中有相同的列數(shù)。即如果在第一個(gè)查詢中選擇了3列，則在第二個(gè)查詢中也要選擇3列。
• UNION返回的結(jié)果集的標(biāo)題僅從第一個(gè)查詢獲得，而忽略其他查詢的列標(biāo)題。
• 查詢中的對應(yīng)列的數(shù)據(jù)類型必須隱式一致。
• 與其他非UNION查詢不同，UNION查詢的默認(rèn)返回選項(xiàng)為DISTINCT，而不是ALL?？梢栽赨NION查詢中使用ALL關(guān)鍵字（UNION ALL），才能返回重復(fù)的行。

示例：

USE Northwind

SELECT CompanyName AS Name, Address, City, Region, PostalCode, Country

FROM Customers

UNION

SELECT CompanyName AS Name, Address, City, Region, PostalCode, Country

FROM Suppliers

UNION

SELECT FirstName + ' ' + LastName AS Name, Address, City, Region, PostalCode, Country

FROM Employees

第4章 創(chuàng)建和修改數(shù)據(jù)表

4.1 SQL Server中的對象名

SQL Server表有4層命名約定。完全限定命名如下所示：

[ServerName.[DatabaseName,[SchemaName.]]]ObjectName

4.1.1 模式名稱

如果使用模式，那么需要指定對象是在哪種模式下的。不同模式下可以有兩個(gè)同名的對象。如果想訪問不在默認(rèn)模式下的對象，那么需要特別指明對象的模式名稱（即廣為人知的所有權(quán)）。

1. 關(guān)于模式的進(jìn)一步討論

在以前的發(fā)布中，所有權(quán)實(shí)際上很像它字面上的意思：即是通過完全限定名稱來識別是誰"擁有"這個(gè)對象。通常，所有這或者是創(chuàng)建該對象的用戶，或者是數(shù)據(jù)庫的所有者（通常指dbo）。一個(gè)所有者與特定的登錄相關(guān)，而模式可以在多個(gè)登錄之間共享，一個(gè)登錄也可以擁有多個(gè)模式。

在默認(rèn)情況下，只有當(dāng)用戶或者是sysadmin系統(tǒng)角色的成員，或者是db_owner或db_ddladmin數(shù)據(jù)庫角色時(shí)，才能在數(shù)據(jù)庫中創(chuàng)建對象。

用戶可以被授予一定權(quán)限來創(chuàng)建特定類型的數(shù)據(jù)庫及系統(tǒng)對象。如果這些個(gè)體用戶已經(jīng)創(chuàng)建了一個(gè)對象，那么在默認(rèn)情況下，這個(gè)對象被授予那個(gè)登錄下默認(rèn)的模式。

注意：

存在一個(gè)特征，并不是說就應(yīng)該使用這個(gè)特征。授予CREATE權(quán)限給普通用戶可能會出現(xiàn)不愉快的事情。簡單來說，將CREATE權(quán)限限制在sa賬戶或sysadmins成員或db_owner安全角色之內(nèi)。

2. 默認(rèn)模式：dbo

無論誰創(chuàng)建了數(shù)據(jù)庫，都被認(rèn)為是"數(shù)據(jù)庫所有者"，即dbo。在數(shù)據(jù)庫里面創(chuàng)建的任何對象都帶有dbo模式，而不是個(gè)體的用戶名。

另外，sa（或者sysadmin角色的成員）總是dbo的別名。即無論是誰實(shí)際上擁有數(shù)據(jù)庫，sa總擁有完全的權(quán)限，就好像是dbo一樣。而且sa登錄創(chuàng)建的任何對象都顯示所有權(quán)為dbo。

例如：假如某個(gè)數(shù)據(jù)庫的普通用戶MySchema，被賦予了CREATE TABLE權(quán)限。如果該用戶創(chuàng)建了一個(gè)名為MyTable的表，那么帶有所有者限定的對象名稱是MySchema.MyTable。注意，由于這時(shí)這個(gè)表有特定的所有者（MySchema），除了MySchema之外的其他用戶需要提供所有者限定名稱才能正確解析這個(gè)表的名稱（MySchema.MyTable）?，F(xiàn)在，假如還有一個(gè)用戶，登錄名為Fred。但是Fred是這個(gè)數(shù)據(jù)庫的所有者（不僅僅是db_owner的成員）。假如Fred使用與MySchema同樣的語句創(chuàng)建了名為MyTable的表，那么帶所有者限定名稱的表名稱是dbo.MyTable。還有，因?yàn)閐bo正好是默認(rèn)的所有者，所以任何用戶都可以用MyTable來引用該表。注意，db_owner數(shù)據(jù)庫角色的成員創(chuàng)建的對象的默認(rèn)模式不是dbo，這些對象將被賦予特定用戶所設(shè)定的默認(rèn)模式。

4.1.2 數(shù)據(jù)庫名稱

需要在當(dāng)前數(shù)據(jù)庫以外的數(shù)據(jù)庫檢索數(shù)據(jù)時(shí)，需要使用數(shù)據(jù)庫限定的命名。記住，當(dāng)前數(shù)據(jù)庫總是默認(rèn)的數(shù)據(jù)庫，所以，如果只需要當(dāng)前數(shù)據(jù)庫中的數(shù)據(jù)，那么不需要在完全限定的名稱中包括數(shù)據(jù)庫名稱。

4.2 CREATE語句

CREATE語句用來創(chuàng)建數(shù)據(jù)庫中的對象。CREATE的第一部分看起來總是這樣的：

CREATE <object type> <object name>

4.2.1 CREATE DATABASE

CREATE DATABASE <database name>

代碼示例：

CREATE DATABASE Accounting

ON

(

NAME = 'Accounting',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ AccountingData.mdf',

SIZE = 10MB,

MAXSIZE = 50MB,

FILEGROWTH = 5MB

)

LOG ON

(

NAME = 'AccountingLog',

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ AccountingLog.ldf',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB

)

各選項(xiàng)含義：

1. ON

ON用在兩個(gè)地方：一個(gè)定義存儲數(shù)據(jù)的文件的位置，二是定義存儲日志的文件的位置。

2. NAME

指定定義的文件的名稱，但是只是一個(gè)邏輯名稱——即SQL Server在內(nèi)部使用該名稱引用該文件。

3. FILENAME

指定文件的物理名稱。數(shù)據(jù)文件的推薦擴(kuò)展名為.mdf，日志文件的推薦擴(kuò)展名為.ldf，附屬文件的推薦擴(kuò)展名為.ndf。

4. SIZE

指定文件的初始大小。默認(rèn)情況下，大小的單位是MB（兆字節(jié)），還可以使用KB、GB或者TB。要記住，這個(gè)值至少與模型數(shù)據(jù)庫一樣大，而且必須是整數(shù)，否則將出錯(cuò)。默認(rèn)的值與模版數(shù)據(jù)庫一樣。

5. MAXSIZE

指定文件長度的最大值。默認(rèn)情況下，大小的單位是MB。這個(gè)選項(xiàng)沒有默認(rèn)值，如果沒有提供該選項(xiàng)，則表示不限制最大值。

6. FILEGROWTH

指定當(dāng)擴(kuò)張文件時(shí)每次的擴(kuò)張量，可以提供一個(gè)值來說明文件每次增加多少字節(jié)，或者提供一個(gè)百分比，指定文件每次增長的百分比。

7. LOG ON

指定日志文件。注意日志文件默認(rèn)的大小是數(shù)據(jù)文件大小的25%。其他方面，日志文件和數(shù)據(jù)庫文件的說明參數(shù)相同。

4.2.2 創(chuàng)建數(shù)據(jù)表

創(chuàng)建表的語法如下：

CREATE TABLE [database_name.[owner].]table_name

(

<column name> <data type>

[[DEFAULT <constant expression>]

| [IDENTITY [(seed, increment) [NOT FOR REPLICATION]]]]

[NULL | NOT NULL]

[<column constraints>]

| [<column name> AS <computed column expression>]

| [<table constraint>]

[,...n]

)

1. 表和列名稱

表和列的推薦命名規(guī)則：

• 名稱的每個(gè)單詞，首字母大寫，其他字母小寫。
• 名稱盡量短，但是要具有描述性。
• 限制使用縮寫，只使用大家都能理解的縮寫。例如"ID"表示標(biāo)識、"No"表示數(shù)字、"Org"表示組織。
• 當(dāng)基于其他表來構(gòu)建表時(shí)，需要在新的表名中包含其他父表的名稱。
• 當(dāng)名稱中有兩個(gè)單詞時(shí)，不要用任何分隔符。

2. 數(shù)據(jù)類型

注意沒有默認(rèn)的數(shù)據(jù)類型

3. DEFAULT

如果要使用默認(rèn)值，就必須緊跟在數(shù)據(jù)類型之后給定這個(gè)值。

4. IDENTITY

當(dāng)你設(shè)定一個(gè)列為標(biāo)識列時(shí)，SQL Server自動分配一個(gè)順序號給你插入的每個(gè)行。注意IDENTITY列和PRIMARY KEY列是完全不同的概念，既不會因?yàn)橛幸粋€(gè)IDENTITY列就說明這個(gè)值是唯一的（例如，可以重新設(shè)置種子，使用前面用過的值）。IDENTITY值通常用于PRIMARY KEY列，但并不是必須這樣使用。

5. NOT FOR REPLICATION

NOT FOR REPLICATION參數(shù)決定：當(dāng)列（通過復(fù)制）發(fā)布到另外一個(gè)數(shù)據(jù)庫時(shí)，是否為新的數(shù)據(jù)庫分配一個(gè)新的標(biāo)識值，還是保留已有的值。

6. NULL/NOT NULL

默認(rèn)的設(shè)置是列值是NOT NULL，除非指定允許為空。然而，有很多不同的設(shè)置可以改變這個(gè)設(shè)置，從而影響這個(gè)默認(rèn)值。

7. 列約束

列約束就是對單個(gè)列設(shè)置的關(guān)于該列可插入數(shù)據(jù)的限制和規(guī)則。

8. 計(jì)算列

該列值是由表中其他列動態(tài)生成的。具體的語法如下：

<column name> AS <computed column expression>

例如：

ExtendedPrice AS Price * Quantity

ListPrice AS Cost * 1.2

相關(guān)的限制條件：

• 不能使用子查詢，而且值不能來自其他不同的表。
• 在SQL Server 2000之前，不能使用計(jì)算列作為鍵的任何部分，也不能和默認(rèn)約束一起使用。在SQL Server 2005中，可以在約束中使用計(jì)算列。
• 以前版本的另外一個(gè)問題是在計(jì)算列中創(chuàng)建索引的能力?？梢栽谟?jì)算列上創(chuàng)建索引，但是必須采用特定的步驟。

9. 表約束

表約束和列約束很相似，但表約束可以基于多個(gè)列。表層次的約束包括PRIMARY KEY約束、FOREIGN KEY約束以及CHECK約束。

10. ON

表定義中的ON子句可以指定表位于哪個(gè)文件組。大多數(shù)時(shí)間，可以省略O(shè)N子句，那些表將位于默認(rèn)文件組中。

11. TEXTIMAGE_ON

該選擇將表的特定部分移動到不同的文件組中。這個(gè)子句只有在表的定義中有text、ntext和image列時(shí)才有效。當(dāng)使用TEXTIMAGE_ON子句時(shí)，只是將BLOB信息移動到分離的文件組中——表的其他部分還在默認(rèn)文件組或者ON子句選擇的文件組中。

12. 創(chuàng)建一個(gè)表

USE Accounting

CREATE TABLE Customers

(

CustomerNo INT IDENTITY NOT NULL,

CustomerName VARCHAR(30) NOT NULL,

Address1 VARCHAR(30) NOT NULL,

Address2 VARCHAR(30) NOT NULL,

City VARCHAR(20) NOT NULL,

State CHAR(2) NOT NULL,

Zip VARCHAR(10) NOT NULL,

Contact VARCHAR(25) NOT NULL,

Phone CHAR(15) NOT NULL,

FedIDNo VARCHAR(9) NOT NULL,

DateInSystem SMALLDATETIME NOT NULL

)

使用sp_help存儲過程查看表的信息：

EXEC sp_help Customers

4.3 ALTER語句

ALTER語句用來更改對象。ALTER語句總是有相同的開頭：

ALTER <object type> <object name>

4.3.1 ALTER DATEBASE

示例：

ALTER DATABASE Accounting

MODIFY FILE

(

NAME = Accounting,

SIZE = 100MB

)

4.3.2 ALTER TABLE

更經(jīng)常的情況是改變表的結(jié)構(gòu)。這個(gè)可以是增加、刪除一列或者改變一列的數(shù)據(jù)類型等。示例：

ALTER TABLE Employees

ADD

PreviousEmployer VARCHAR(30) NULL,

DataOfBirth DATETIME NULL,

LastRaiseDate DATETIME NOT NULL, DEFAULT '2005-01-01'

4.4 DROP語句

DROP語句用來刪除對象。

DROP <object type> <object name>[, ...n]

如果需要，可以同時(shí)刪除兩個(gè)表：

USE Accounting

DROP TABLE Customers, Employees

刪除整個(gè)數(shù)據(jù)庫：

DROP DATABASE Accounting

第5章 約束

確保數(shù)據(jù)的完整性不是使用數(shù)據(jù)的程序的責(zé)任，而是數(shù)據(jù)庫本身的責(zé)任。將數(shù)據(jù)完整性的責(zé)任移到數(shù)據(jù)庫本身是數(shù)據(jù)庫管理的一次革命。

3種不同類型的約束：

• 實(shí)體約束
• 域約束
• 參照完整性約束

具體的約束類型：

• PRIMARY KEY約束
• FOREIGN KEY約束
• UNIQUE約束
• CHECK約束
• DEFAULT約束

5.1 約束的類型

5.1.1 域約束

域約束處理一個(gè)或多個(gè)列，確保一個(gè)特定列或一組特定列滿足特定的標(biāo)準(zhǔn)。

5.1.2 實(shí)體約束

實(shí)體約束都是關(guān)于每個(gè)行的。這種形式的約束并不關(guān)心一個(gè)整體的列，只對特定的行感興趣，如PRIMARY KEY約束和UNIQUE約束。

5.1.3 參照完整性約束

參照完整性約束是在某列的值必須與其他列的值匹配時(shí)創(chuàng)建的，列可以在同一個(gè)表中，或者更通常的是在不同的表中，如FOREIGN KEY約束。

5.2 約束命名

常見的約束的推薦命名規(guī)則如下：

• CHECK約束以CK開頭、主鍵約束以PK開頭、外鍵約束以FK開頭、唯一約束以UN開頭。
• 后接表名、列名。

如在Customers表上對PhoneNo列設(shè)置約束：CK_Customers_PhoneNo，Customers表上的主鍵約束：PK_Custoemrs_CustomerID。

5.3 鍵約束

常用的鍵類型：主鍵、外鍵、唯一約束。

5.3.1 主鍵約束

1. 在創(chuàng)建表的時(shí)候創(chuàng)建主鍵約束。

CREATE TABLE Customers

(

CustomerNo INT IDENTITY NOT NULL PRIMARY KEY,

......

)

2. 在已存在的表上創(chuàng)建主鍵約束。

USE Accounting

ALTER TABLE Employees

ADD CONSTRAINT PK_EmployeeID

PRIMARY KEY (EmployeeID)

5.3.2 外鍵約束

在CREATE語句中設(shè)置一列或幾列外鍵約束的語法如下所示：

<column name> <date type> <nullability>

FOREIGN KEY REPERENCES <table name>(<column name>)

[ON DELETE {CASCADE|NO ACTION|SET NULL|SET DEFAULT}]

[ON UPDATE {CASCADE|NO ACTION|SET NULL|SET DEFAULT}]

示例：

USE Accounting

CREATE TABLE Orders

(

OrderID INT IDENTITY NOT NULL

PRIMARY KEY,

CustomerNo INT NOT NULL

FOREIGN KEY REFERENCES Customers(CustomerNo),

OrderDate SMALLDATETIME NOT NULL,

EmpoyeeID INT NOT NULL

)

1. 在已存在的表中添加一個(gè)外鍵

ALTER TABLE Orders

ADD CONSTRAINT FK_EmployeeCreatesOrder

FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)

2. 使一個(gè)表自引用

在實(shí)際創(chuàng)建自引用約束之前，很關(guān)鍵的一點(diǎn)是在添加外鍵之前表中至少有一行。

ALTER TABLE Employees

ADD CONSTRAINT FK_EmployeeHasManager

FOREIGN KEY (ManagerEmpID) REFERENCES Employees(EmployeeID)

3. 級聯(lián)動作

外鍵是雙向的，即不僅是限制子表的值必須存在于父表中，還在每次對父表操作后檢查子行。SQL Server的默認(rèn)行為是在子表存在時(shí)"限制"父表不被刪除。然而，有時(shí)會自動刪除任何相關(guān)記錄，而不是防止刪除被引用的記錄。同樣，在更新記錄時(shí)，可能希望相關(guān)的記錄自動引用剛剛更新的記錄。這種進(jìn)行自動刪除和更新的過程稱為級聯(lián)。通過修改聲明外鍵的語法——添加ON子句，來定義級聯(lián)操作。

USE Accounting

CREATE TABLE OrderDetails

(

OrderID INT NOT NULL,

PartNo VARCHAR(10) NOT NULL,

Description VARCHAR(25) NOT NULL,

Qty INT NOT NULL,

CONSTRAINT PK_OrderDetails

PRIMARY KEY (OrderID, PartNo),

CONSTRAINT FK_OrderContainsDetails

FOREIGN KEY (OrderID)

REFERENCES Orders(OrderID)

ON UPDATE NO ACTION

ON DELETE CASCADE

)

如果對外鍵定義了CASCADE，則操作會從父表級聯(lián)到子表中。即，如果從父表刪除了某項(xiàng)，子表中依賴該項(xiàng)的項(xiàng)都會被刪除；如果從父表中更新了某項(xiàng)，則子表中依賴該項(xiàng)的字段也會被更新。

值得注意的是：CASCADE動作所能影響的深度沒有限制。

4. 其他操作

NO ACTION為默認(rèn)操作，即如果子表有依賴，則禁止對父表中的該字段進(jìn)行刪除和更新操作。

SET NULL操作會在父表中的該字段被刪除或者更新時(shí)，將子表中的依賴項(xiàng)設(shè)為NULL，前提是子表中的該項(xiàng)可為NULL值。

SET DEFAULT操作會在父表中的該字段被刪除或者更新時(shí)，將子表中的依賴項(xiàng)設(shè)為在子表中定義的默認(rèn)值，當(dāng)然前提是在子表中該字段有默認(rèn)值。

5.3.3 唯一約束

唯一約束不會自動防止您設(shè)置一個(gè)NULL值。是否允許NULL值取決于表中相應(yīng)列的NULL選項(xiàng)的設(shè)置。然而，要記住如果您確實(shí)允許NULL值，那么只能插入一個(gè)NULL。

在創(chuàng)建表時(shí)設(shè)置唯一約束：

CREATE TABLE Shippers

(

ShipperID INT IDENTITY NOT NULL PRIMARY KEY,

ShipperName VARCHAR(30) NOT NULL,

Address VARCHAR(30) NOT NULL,

City VARCHAR(25) NOT NULL,

State CHAR(2) NOT NULL,

Zip VARCHAR(10) NOT NULL,

PhoneNo VARCHAR(14) NOT NULL UNIQUE

)

在已存在的表中創(chuàng)建唯一約束：

ALTER TABLE Employees

ADD CONSTRAINT AK_EmployeeSSN

UNIQUE (SSN)

在約束名稱中的AK前綴代表"交替鍵（Alternate Key）"，也可以使用前綴UQ或者簡單的U，代表唯一約束。

5.4 CHECK約束

CHECK約束使用與WHERE字句一樣的規(guī)則來定義。CHECK約束標(biāo)準(zhǔn)的示例如下：

目標(biāo)	SQL
限制Month列為合適的數(shù)字	BETWEEN 1 AND 12
合適的SSN格式	LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
限制Shippers的一個(gè)特定列表	IN ('UPS', 'Fed Ex', 'USPS')
價(jià)格必須為正	UnitPrice >= 0
在同一行中引用另外一個(gè)列	ShipDate >= OrderDate

在已存在的表中添加CHECK約束：

ALTER TABLE Customers

ADD CONSTRAINT CK_CustomerDateInSystem

CHECK (DateInSystem <= GETDATE())

試著插入違反CHECK約束的記錄會得到錯(cuò)誤。

5.5 DEFAULT約束

DEFAULT約束定義了當(dāng)插入新行時(shí)，在您定義了默認(rèn)約束的列中沒有數(shù)據(jù)時(shí)填充的默認(rèn)值。要注意：

• 默認(rèn)值只在INSERT語句中使用——在UPDATE語句和DELETE語句中被忽略。
• 如果在INSERT語句中提供了任意的值（包括NULL值），那么就不使用默認(rèn)值。
• 如果沒有提供值，那么總是使用默認(rèn)值。

5.5.1 在CREATE TABLE語句中定義DEFAULT約束

示例：

CREATE TABLE Shippers

(

ShipperID INT IDENTITY NOT NULL

PRIMARY KEY,

ShipperName VARCHAR(30) NOT NULL,

DataInSystem SMALLDATETIME NOT NULL

DEFAULT GETDATE()

)

5.5.2 在已存在的表中添加DEFAULT約束

示例：

ALTER TABLE Customers

ADD CONSTRAINT DF_CustomerDefaultDateInSystem

DEFAULT GETDATE() FOR DateInSystem

5.6 使約束失效

5.6.1 在創(chuàng)建約束時(shí)忽略無效的數(shù)據(jù)

默認(rèn)情況下，除非已存在的數(shù)據(jù)滿足約束標(biāo)準(zhǔn)，否則SQL Server將不會創(chuàng)建約束。要想在創(chuàng)建約束時(shí)不檢查已經(jīng)在表中的數(shù)據(jù)是否滿足約束，可以在添加約束時(shí)添加WITH NOCHECK選項(xiàng)。示例：

ALTER TABLE Customers

WITH NOCHECK

ADD CONSTRAINT CK_CustomerPhoneNo

CHECK

(Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

5.6.2 臨時(shí)使已存在的約束失效

使用NOCHECK選項(xiàng)關(guān)閉約束，而不是刪除它。示例：

ALTER TABLE Customers

NOCHECK

CONSTRAINT CK_CustomerPhoneNo

當(dāng)準(zhǔn)備重新讓約束起效時(shí)，使用CHECK選項(xiàng)代替NOCHECK：

ALTER TABLE Customers

CHECK

CONSTRAINT CK_CustomerPhoneNo

第6章 在查詢中添加更多內(nèi)容

6.1 子查詢的概念

子查詢是嵌套在另外一個(gè)查詢中的正常的T-SQL查詢。在有一個(gè)SELECT語句作為部分?jǐn)?shù)據(jù)或者另外一個(gè)查詢的條件的基礎(chǔ)時(shí)，通過使用括號創(chuàng)建子查詢。

子查詢通常用于滿足下列需求之一：

• 將一個(gè)查詢分隔為一系列的邏輯步驟。
• 提供一個(gè)列表作為WHERE子句或者IN、EXISTS、ANL、SOME、ALL的目標(biāo)
• 為父查詢中的每個(gè)記錄提供一個(gè)查詢表。

6.2 嵌套的子查詢

嵌套的子查詢只在一個(gè)方向嵌套——返回在外部查詢中使用的單個(gè)值，或者在IN運(yùn)算符中使用的一個(gè)完整的值列表。

在最松散的意義上說，查詢語法看起來像下面的兩個(gè)語法模板：

SELECT <select list>

FROM <some table>

WHERE <some column> = (

SELECT <single column>

FROM <some table>

WHERE <condition that results in only one row returned>)

或者：

SELECT <select list>

FROM <some table>

WHERE <some column> IN (

SELECT <single column>

FROM <some table>

WHERE <where condition >)

6.2.1 使用單個(gè)值的SELECT語句的嵌套查詢

例如，假設(shè)希望知道每一天通過系統(tǒng)銷售的產(chǎn)品的每個(gè)條目的ProductID：

SELECT DISTINCK o.OrderDate, od.ProductID

FROM Orders o

INNER JOIN OrderDetails od

ON o.OrderID = od.OrderID

WHERE o.OrderDate = (SELECT MIN(OrderDate) FROM Orders)

6.2.2. 使用返回多個(gè)值的子查詢的嵌套查詢

例如，查看所有具有折扣記錄的商店列表：

USE Pubs

SELECT stor_id AS "Store ID", stor_name AS "Store Name"

FROM Stores

WHERE stor_id IN (SELECT stor_id FROM Discounts)

6.2.3. 使用嵌套的SELECT來發(fā)現(xiàn)孤立的記錄

這種嵌套的SELECT和前面示例幾乎相同，區(qū)別是添加了NOT運(yùn)算符。這個(gè)不同點(diǎn)時(shí)的在轉(zhuǎn)化連接語法時(shí)設(shè)置等于外部連接而不是內(nèi)部連接。例如，需要查詢所有在Pubs數(shù)據(jù)庫中沒有匹配的折扣記錄的商店：

SELECT stor_id AS "Store ID", stor_name AS "Store Name"

FROM Stores

WHERE stor_id NOT IN

(SELECT stor_id FROM Discounts WHERE stor_id IS NOT NULL)

6.3 相互關(guān)聯(lián)的子查詢

6.3.1 相互關(guān)聯(lián)的子查詢的工作原理

在相互關(guān)聯(lián)的子查詢中，內(nèi)部查詢在外部查詢提供的信息上運(yùn)行，反之亦然。有3個(gè)步驟的處理過程：

• 外部查詢獲得一個(gè)記錄，然后將該記錄傳遞到內(nèi)部查詢。
• 內(nèi)部查詢根據(jù)傳遞的值執(zhí)行。
• 內(nèi)部查詢?nèi)缓髮⒔Y(jié)果值傳回到外部查詢，外部查詢利用這些值完成處理過程。

6.3.2 在WHERE子句中的相互關(guān)聯(lián)的子查詢

例如，需要查詢系統(tǒng)中每個(gè)顧客第一個(gè)訂單的OrderID和OrderDate：

SELECT o1.CustomerID, o1.OrderID, o1.OrderDate

FROM Orders o1

WHERE o1.OrderDate = (

SELECT MIN(o2.OrderDate)

FROM Orders o2

WHERE o2.CustomerID = o1.CustomerID)

6.3.3 在SELECT列表中的相互關(guān)聯(lián)的子查詢

例如，現(xiàn)在需要查詢顧客的姓名和在哪天開始訂購商品：

SELECT cu.CompanyName,

(SELECT MIN(OrderDate)

FROM Orders o

WHERE o.CustomerID = cu.CustomerID) AS "Order Date"

FROM Customers cu

6.3.4 處理NULL數(shù)據(jù)——ISNULL函數(shù)

ISNULL()接受一個(gè)變量或者表達(dá)式來驗(yàn)證是否是一個(gè)空值。如果值確實(shí)是NULL，那么函數(shù)返回其他預(yù)指定的值。如果原來的值不是NULL，那么返回原來的值。語法如下：

ISNULL(<expression to test>, <replacement value if null>)

因此，示例如表所示：

ISNULL表達(dá)式	返回值
ISNULL(NULL, 5)	5
ISNULL(5, 15)	5
ISNULL(MyColumnName, 0) where MyColumnName IS NULL	0
ISNULL(MyColumnName, 0) where MyColumnName = 3	3
ISNULL(MyColumnName, 0) where MyColumnName = 'Fred Farmer'	Fred Farmer

使用示例：

SELECT cu.CompanyName,

ISNULL(CAST((SELECT MIN(o.OrderDate)

FROM Orders o

WHERE o.CustomerID = cu.CustomerID) AS VARCHAR), 'NEVER ORDERED')

AS "Order Date"

FROM Customers cu

6.3.5 派生表

例如，現(xiàn)在需要查詢既訂購了Chocolade又訂購了Vegie-spread的所有公司名稱。查詢代碼如下所示：

SELECT DISTINCT c.CompanyName

FROM Customers c

INNER JOIN (

SELECT CustomerID

FROM Orders o

INNER JOIN OrderDetails od

ON o.OrderID = od.OrderID

INNER JOIN Products p

ON od.ProductID = p.ProductID

WHERE p.ProductName = 'Chocolade') AS spen

ON c.CustomerID = spen.CustomerID

INNER JOIN (

SELECT CustomerID

FROM Orders o

INNER JOIN OrderDetails od

ON o.OrderID = od.OrderID

INNER JOIN Products p

ON od.ProductID = p.ProductID

WHERE p.ProductName = 'Vegie-spread') AS spap

ON c.CustomerID = spap.CustomerID

6.4 EXISTS運(yùn)算符

使用EXISTS時(shí)，根據(jù)是否存在數(shù)據(jù)滿足查詢中EXISTS語句所建立的標(biāo)準(zhǔn)，返回一個(gè)簡單的TRUE和FALSE。例如：

SELECT CustomerID, CompanyName

FROM Customers cu

WHERE EXISTS (

SELECT OrderID

FROM Orders o

WHERE o.CustomerID = cu.CustomerID)

當(dāng)使用EXISTS關(guān)鍵字時(shí)，SQL Server不需要執(zhí)行一行一行的連接，而是尋找記錄，知道找到第一個(gè)匹配的記錄，停止在那里。只要有一個(gè)匹配，EXISTS就為真，不需要繼續(xù)查找。

如果需要查詢沒有訂購任何產(chǎn)品的客戶，可以使用NOT EXISTS：

SELECT CustomerID, CompanyName

FROM Customers cu

WHERE NOT EXISTS (

SELECT OrderID

FROM Orders o

WHERE o.CustomerID = cu.CustomerID)

6.5 數(shù)據(jù)類型轉(zhuǎn)換：CAST和CONVERT

CAST和CONVERT都可以執(zhí)行數(shù)據(jù)類型轉(zhuǎn)換。在大部分情況下，兩者執(zhí)行相同的功能，不同的是CONVERT還提供一些日期格式轉(zhuǎn)換，而CAST沒有這個(gè)功能。

注意，CAST是ANSI兼容的，而CONVERT不是。

各自的語法如下：

CAST (expression AS data type)

CONVERT (data type, expression[, style])

CAST和CONVERT可以進(jìn)行很多數(shù)據(jù)類型轉(zhuǎn)換，在SQL Server不進(jìn)行隱式轉(zhuǎn)換時(shí)，需要這種轉(zhuǎn)換。例如：

SELECT 'The Customer has an Order numbered ' + CAST(OrderID AS VARCHAR)

FROM Orders

WHERE CustomerID = 'ALFKI'

例如，需要將timestamp列轉(zhuǎn)換為正常數(shù)字。一個(gè)timestamp是個(gè)二進(jìn)制數(shù)字，因此需要轉(zhuǎn)換：

SELECT CloTS AS "Uncoverted", CAST(ColTS AS INT) AS "Converted"

FROM ConvertTest

還可以轉(zhuǎn)換日期：

SELECT OrderDate, CAST(OrderDate AS VARCHAR) AS "Converted"

FROM Orders

WHERE OrderID = 11050

CONVERT還可以控制日期格式：

SELECT OrderDate, CONVERT(VARCHAR, OrderDate, 111) AS "Converted"

FROM Orders

WHERE OrderID = 11050

CONVERT函數(shù)最后一個(gè)代碼說明需要的格式。注意，任何以超過100表示的是4位的年份；小于100的是兩位數(shù)字的年份，不過有很少的一些例外，并且小于100表示的格式加上100后即為對應(yīng)的4位的年份表示的格式。

第7章 視圖

視圖的核心實(shí)際上僅僅是一個(gè)存儲的查詢。重要的是可以將來自于基本表（或者其他視圖）的數(shù)據(jù)混合以及匹配，以創(chuàng)建在大多數(shù)方面上像另一個(gè)基本表那樣起作用的對象。

7.1 簡單的視圖

視圖語法的基本形式：

CREATE VIEW <view name>

[WITH ENCRYPTION]

AS

<select statement>

WITH CHECK OPTION

示例——?jiǎng)?chuàng)建簡單的視圖：

USE Accounting

GO

CREATE VIEW CustomerPhoneList_vw

AS

SELECT CustomerName, Contact, Phone

FROM Customers

對Employees表創(chuàng)建視圖，隱藏隱私信息：

USE Accounting

GO

CREATE VIEW Employees_vw

AS

SELECT EmployeeID,

FirstName,

MiddleInitial,

LastName,

Title,

HireDate,

ManagerEmpID,

Department

FROM Employees

7.2 作為過濾器的視圖

在創(chuàng)建視圖時(shí)使用WHERE字句來過濾查詢中的結(jié)果。

示例：

CREATE VIEW CurrentEmployees_vw

AS

SELECT EmployeeID,

FirstName,

MiddleInitial,

LastName,

Title,

HireDate,

TerminationDate,

ManagerEmpID,

Department

FROM Employees

WHERE TerminationDate IS NULL

7.3 更加復(fù)雜的視圖

在簡單視圖的基礎(chǔ)上添加連接。示例——查詢訂單、零件和消費(fèi)者信息：

USE Northwind

GO

CREATE VIEW CustomerOrders_vw

AS

SELECT cu.CompanyName,

o.OrderID,

o.OrderDate,

od.ProductID,

p.ProductName,

od.Quantity,

od.UnitPrice,

od.Quantity * od.UnitPrice AS ExtendedPrice

FROM Customers AS cu

INNER JOIN Orders AS o

ON cu.CustomerID = o.CustomerID

INNER JOIN OrderDetails AS od

ON o.OrderID = od.OrderID

INNER JOIN Products AS p

ON od.ProductID = p.ProductID

數(shù)據(jù)庫的用戶可以方便地查詢到消費(fèi)者的訂單信息，而不需要關(guān)心四個(gè)表的連接：

SELECT CompanyName, ExtendedPrice

FROM CustomerOrders_vw

WHERE OrderDate = '1996-9-3'

7.4 通過視圖改變數(shù)據(jù)

從使用的觀點(diǎn)來看，視圖非常像表那樣地工作。但是，現(xiàn)在來看看一些不同之處。

你可以成功地對視圖執(zhí)行INSERT、UPDATE以及DELETE語句。但是，當(dāng)通過視圖改變數(shù)據(jù)的時(shí)候，有一些內(nèi)容需要注意：

• 如果視圖包含連接，在大多數(shù)情況下，除非使用INSTEAD OF觸發(fā)器，否則不能對數(shù)據(jù)執(zhí)行INSERT或者DELETE操作。在一些情況下，UPDATE可以不使用INSTEAD OF觸發(fā)器來工作，但是僅限于個(gè)別情況。
• 如果視圖僅僅引用單個(gè)的表，那么在表中所有需要的字段都在視圖中或者有默認(rèn)值得情況下，可以直接執(zhí)行INSERT操作。否則，需要使用INSTEAD OF觸發(fā)器。
• 在一個(gè)有限的范圍內(nèi)，可以限制在視圖中可以插入和更新的內(nèi)容以及不可以插入和更新的內(nèi)容。

1. 以連接的數(shù)據(jù)方式處理視圖的變化

如果視圖包含連接，在大多數(shù)情況下，除非使用INSTEAD OF觸發(fā)器，否則不能對數(shù)據(jù)執(zhí)行INSERT或者DELETE操作。

2. 必要的字段必須在視圖中出現(xiàn)或者具有默認(rèn)值

3. 約束插入到視圖中的內(nèi)容——WITH CHECK OPTION

WITH CHECK OPTION的規(guī)則很簡單——為了通過使用視圖更新或者插入數(shù)據(jù)，結(jié)果行必須符合要求以出現(xiàn)在視圖結(jié)果中。即如果通過視圖插入的值不能在視圖中顯示出來，則禁止插入該行記錄。

7.5編輯視圖

ALTER語句會完全替換現(xiàn)有的視圖。使用ALTER VIEW語句和先刪除后新建視圖的區(qū)別在于：

• ALTER VIEW期望找到一個(gè)已存在的視圖，而CREATE則相反。
• ALTER VIEW保留了視圖上任何已經(jīng)建立的權(quán)限信息。
• ALTER VIEW保留了任何依賴信息。

7.6 刪除視圖

DROP VIEW <view name> [, <view name> [...n]]

7.7 保護(hù)代碼：加密視圖

使用WITH ENCRYPTION選項(xiàng)加密視圖，注意和WITH CHECK OPTION出現(xiàn)的位置不同：

ALTER VIEW CustomerOrders_vw

WITH ENCRYPTION

AS

SELECT cu.COmpanyName,

o.OrderDate,

od.ProductID,

p.ProductName,

od.Quantity,

od.UnitPrice,

od.Quantity * od.UnitPrice AS ExtendedPrice

FROM Customers AS cu

INNER JOIN Orders AS o

ON cu.CustomerID = o.CustomerID

INNER JOIN OrderDetails AS od

ON o.OrderID = od.OrderID

INNER JOIN Products AS p

ON od.ProductID = p.ProductID

注意，一旦源代碼被加密了，就沒有辦法恢復(fù)。

第8章 腳本與批處理

8.1 腳本基礎(chǔ)

腳本示例：

USE Northwind

DECLARE @Ident INT

INSERT INTO Orders

(CustomerID, OrderDate)

VALUES

('ALFKI', DATEADD(day, -1, GETDATE()))

SELECT @Ident = @@IDENTITY

INSERT INTO OrderDetails

(OrderID, ProductID, UnitPrice, Quantity)

VALUES

(@Ident, 1, 50, 25)

SELECT 'The OrderID of the INSERTed row is ' + CONVERT(VARCHAR(8), @Ident)

8.1.1 USE語句

USE語句用于設(shè)置當(dāng)前數(shù)據(jù)庫。USE語句會影響在完全限定對象名的數(shù)據(jù)庫部分使用默認(rèn)值的任何地方。

8.1.2 聲明變量

DECLARE語句具有相當(dāng)簡單的語法：

DECLARE @<variable name> <variable type> [, …]

可以一次僅僅聲明一個(gè)變量，也可以一次聲明幾個(gè)變量。變量開始的值將總是為NULL，直到顯示地將變量設(shè)置為一些其他的值。

1. 為變量設(shè)置值

目前在變量中設(shè)置值的方法有兩種?？梢允褂肧ELECT語句或者SET語句。從功能上看，它們的作用幾乎是相同的，不同的是SELECT語句具有使得源值來自SELECT語句中的某一列的能力。

使用SET設(shè)置變量

SET通常用于以更加程序化的語言中所使用的方式來設(shè)置變量。經(jīng)典的使用示例是：

SET @TotalCost = 10

SET @TotalCost = @UnitCost * 1.1

使用SET，不能將查詢得到的值賦給變量——必須將查詢和SET分開。例如：

USE Northwind

DECLARE @Test MONEY

SET @Test = (SELECT MAX(UnitPrice) FROM OrderDetails)

SELECT @Test

注意：

盡管這個(gè)語法可以起作用，但習(xí)慣上，從來不采用這種方法實(shí)現(xiàn)代碼。

使用SELECT設(shè)置變量

當(dāng)變量中存儲的信息來源于查詢的時(shí)候，經(jīng)常用SELECT給變量賦值。例如，上面最后的示例中使用SELECT是更加常用的做法：

USE Northwind

DECLARE @Test MONEY

SELECT @Test = MAX(UnitPrice) FROM OrderDetails

SELECT @Test

2. 系統(tǒng)函數(shù)概述

注意這些系統(tǒng)函數(shù)常被人們認(rèn)為是"系統(tǒng)常量"，但在SQL Server中更規(guī)范的名稱為"系統(tǒng)函數(shù)"。其中最值得關(guān)心的如下所示：

系統(tǒng)函數(shù)	用途	注釋
@@DATEFIRST	返回當(dāng)前設(shè)置的每個(gè)星期的第一天（比如星期日或者星期一）	是一個(gè)系統(tǒng)范圍的設(shè)置——如果有人改變了這個(gè)設(shè)置，就不能得到所期望的結(jié)果。
@@ERROR	返回在當(dāng)前連接上的最近的T-SQL語句錯(cuò)誤的數(shù)目。如果沒有錯(cuò)誤，返回0	在每個(gè)新的語句下重新設(shè)置。如果需要保存這個(gè)值，應(yīng)該立刻把這個(gè)值移動到一個(gè)局部變量中。
@@IDENTITY	返回插入的最近的標(biāo)識值，作為最近的INSERT或者SELECT INTO語句的結(jié)果	如果沒有標(biāo)識值產(chǎn)生，那么設(shè)置為NULL。即使缺少標(biāo)識值是由于一個(gè)運(yùn)行的語句的失敗，也是如此。如果通過一個(gè)語句執(zhí)行多個(gè)插入，那么只返回最后的標(biāo)識值。
@@REMSERVER	僅僅在存儲過程中使用。返回稱為存儲過程的服務(wù)器的數(shù)值	在希望sproc根據(jù)遠(yuǎn)程服務(wù)器不同表現(xiàn)出不同的行為時(shí)，這個(gè)選項(xiàng)是很方便的。
@@ROWCOUNT	一個(gè)最經(jīng)常使用的系統(tǒng)函數(shù)。返回最近的語句所影響的行的數(shù)目。	一般在非運(yùn)行時(shí)錯(cuò)誤檢查時(shí)使用。例如，如果嘗試通過使用一個(gè)WHERE字句刪除一行，并且沒有行被影響，那么那將意味著一些不期望的事情發(fā)生了。

8.1.3 使用@@IDENTITY

@@IDENTITY是所有的系統(tǒng)函數(shù)中最重要的一個(gè)。標(biāo)識列是這樣的列，在那里沒有提供一個(gè)值，而是SQL Server自動地插入一個(gè)值。任何的INSERT或者INSERT INTO語句都會更新這個(gè)函數(shù)的返回值。如果沒有新的標(biāo)識列被插入，將返回NULL。如果插入了多個(gè)行，生成了多個(gè)標(biāo)識值，則@@IDENTITY將返回最后生成的標(biāo)識值。如果語句觸發(fā)了一個(gè)或多個(gè)觸發(fā)器，該觸發(fā)器又執(zhí)行了生成標(biāo)識值的插入操作，那么，在語句執(zhí)行后立即調(diào)用@@IDENTITY將返回觸發(fā)器生成的最后一個(gè)標(biāo)識值。如果對包含標(biāo)識列的表執(zhí)行插入操作后觸發(fā)了觸發(fā)器，并且觸發(fā)器對另一個(gè)沒有標(biāo)識列的表執(zhí)行了插入操作，則@@IDENTITY將返回第一次插入的標(biāo)識值。出現(xiàn)INSERT或SELECT INTO語句失敗或大容量復(fù)制失敗，或者事務(wù)被回滾的情況時(shí)，@@IDENTITY值不會恢復(fù)為以前的設(shè)置。

8.1.4 使用@@ROWCOUNT

@@ROWCOUNT說明上一個(gè)SQL語句（SELECT、UPDATE、INSERT和DELETE等）影響了多少行。示例：

USE Northwind

GO

DECLARE @RowCount INT

SELECT * FROM Categories

SELECT @RowCount = @@ROWCOUNT

PRINT 'The value of @@ROWCOUNT was ' + CAST(@RowCount AS VARCHAR(5))

則最后一行顯示：

The value of @@ROWCOUNT was 8

8.2 批處理

批處理是進(jìn)入一個(gè)邏輯單元的T-SQL語句組。一個(gè)批處理中的所有語句被組合為一個(gè)執(zhí)行計(jì)劃，因此對所有語句一起進(jìn)行語法分析，并且必須通過語法驗(yàn)證，否則將沒有一個(gè)語句會執(zhí)行。但是，這并不能防止運(yùn)行時(shí)錯(cuò)誤的發(fā)生。如果發(fā)生運(yùn)行時(shí)錯(cuò)誤，那么任何在發(fā)生運(yùn)行時(shí)錯(cuò)誤之前執(zhí)行的語句將仍然是有效的。簡言之，如果一個(gè)語句不能通過語法分析，那么不會運(yùn)行任何語句。如果一個(gè)語句在運(yùn)行時(shí)失敗，那么產(chǎn)生錯(cuò)誤語句之前的所有語句都已經(jīng)運(yùn)行了。

可以將一個(gè)腳本分開為多個(gè)批處理，方法是使用GO語句。GO語句：

• 必須自成一行（只有注釋可以在相同的行上）。
• 使得從腳本或者上一個(gè)GO語句開始的所有語句編譯成一個(gè)執(zhí)行計(jì)劃并發(fā)送到服務(wù)器，與任何其他批處理無關(guān)。
• 不是T-SQL命令，而是由各種SQL Server命令實(shí)用程序識別的命令。

代碼示例：

USE AdventureWorks

DECLARE @MyVarchar VARCHAR(50) – This DECLARE only lasts for this batch!

SELECT @MyVarchar = 'Honey, I''m home…'

PRINT 'Done with first batch…'

GO

PRINT @MyVarchar – This generates an error since @MyVarchar isn't declared in this batch

PRINT 'Done with second batch'

GO

PRINT 'Done with third batch' – Notice that this still gets executed even after the error

GO

結(jié)果如下所示：

Done with first batch…

Msg 137, Level 15, State 2, Line 2

Must declare the scalar variable "@MyVarchar"

Done with third batch

8.2.1 批處理中的錯(cuò)誤

批處理中的錯(cuò)誤分成兩類：

• 語法錯(cuò)誤
• 運(yùn)行時(shí)錯(cuò)誤

如果查詢分析器發(fā)現(xiàn)一個(gè)語法錯(cuò)誤，那么批處理的處理過程會立即取消。因?yàn)檎Z法檢查發(fā)生在批處理編譯或者執(zhí)行之前，所以在語法檢查期間的失敗意味著還沒有批處理被執(zhí)行。

運(yùn)行時(shí)錯(cuò)誤的工作方式則不同。因?yàn)槿魏卧谟龅竭\(yùn)行時(shí)錯(cuò)誤之前執(zhí)行的語句已經(jīng)完成了，所以除非是未提交的事務(wù)的一部分，否則這些語句所做的任何事情的影響將保留下來。一般而言，運(yùn)行時(shí)錯(cuò)誤將終止從錯(cuò)誤發(fā)生地方到批處理末端的批處理的執(zhí)行。

8.2.2 什么時(shí)候使用批處理

批處理有幾個(gè)目的，但是所有的批處理具有一個(gè)共同點(diǎn)——在腳本中當(dāng)一些事情必須發(fā)生在另外一件事之前或者分開發(fā)生時(shí)，使用批處理。

1. 要求有自己的批處理的語句

有一些命令必須完全是它們自己的批處理的一部分。這些命令包括：

• CREATE DEFAULT
• CREATE PROCEDUER
• CREATE RULE
• CREATE TRIGGER
• CREATE VIEW

如果你想在一個(gè)腳本中將這些語句中的任意一些和其他的語句進(jìn)行組合，那么需要通過使用GO語句將它們分開為各自的批處理。

注意：

注意，如果DROP一個(gè)對象，那么應(yīng)該將DROP語句放在它自己的批處理中或者至少和其他DROP語句在一個(gè)批處理中。

2. 使用批處理建立優(yōu)先權(quán)

使用批處理語句的最可能如果在下一個(gè)任務(wù)開始之前，需要全部完成上一個(gè)任務(wù)。例如，在嘗試使用新數(shù)據(jù)庫時(shí)，需要先完成CREATE DATABASE語句：

CREATE DATABASE Test

GO

USE Test

CREATE TABLE TestTable

(

col1 INT,

col2 INT

)

另外，當(dāng)使用ALTER TABLE語句顯著地修改一個(gè)列的類型或者添加列時(shí)，直到執(zhí)行修改任務(wù)的批處理已經(jīng)完成時(shí)，才能利用這些變化。

USE Test

ALTER TABLE TestTable

ADD col3 INT

GO

INSERT INTO TestTable(col1, col2, col3)

VALUES (1, 1, 1)

第9章 存儲過程和流控制語句

存儲過程（stored procedure）有時(shí)也稱為sproc。存儲過程存儲于數(shù)據(jù)庫中而不是在單獨(dú)的文件中，有輸入?yún)?shù)、輸出參數(shù)以及返回值等。

9.1 創(chuàng)建存儲過程：基本語法

在數(shù)據(jù)庫中，創(chuàng)建存儲過程和創(chuàng)建其他對象的過程一樣，除了它使用的AS關(guān)鍵字外。存儲過程的基本語法如下：

CREATE PROCDUER|PROC <sproc name>

[<parameter_name>[schema.]<data_type> [VARYING][=<default_value>][OUT [PUT]][,

[<parameter_name>[schema.]<data_type> [VARYING][=<default_value>][OUT [PUT]][,

...]]

AS

<code>

示例：

USE Northwind

GO

CRREATE PROC spShippers

AS

SELECT * FROM Shippers

執(zhí)行這個(gè)存儲過程：

EXEC spShippers

9.2 使用ALTER改變存儲過程

當(dāng)使用T-SQL編輯存儲過程的時(shí)候，需要記住的是它完全取代了現(xiàn)存的存儲過程。使用ALTER PROC和CREATE PROC的區(qū)別在于：

• ALTER PROC期望找到現(xiàn)存的存儲過程，而CREATE則不是。
• ALTER PROC保留了已經(jīng)建立的存儲過程的任何權(quán)限。它在系統(tǒng)對象中保留了相同的對象ID并允許保留依賴關(guān)系。
• ALTER PROC在其他對象上保留了任何依賴關(guān)系的信息，這些對象可以調(diào)用修改的存儲過程。

注意：

如果執(zhí)行DROP，然后使用CREATE，這和使用ALTER PROC語句一樣，幾乎都能得到相同的效果，除了一個(gè)很重要的區(qū)別——如果使用DROP和CREATE，則需要完全重新建立權(quán)限，權(quán)限規(guī)定了可以使用以及不能使用存儲過程的用戶。

9.3 刪除存儲過程

這個(gè)過程非常簡單：

DROP PROC|PROCEDURE <sproc name>

9.4 參數(shù)化（Parameterization）

9.4.1聲明參數(shù)

聲明參數(shù)需要以下2到4部分信息：

• 名稱
• 數(shù)據(jù)類型
• 默認(rèn)值
• 方向

語法如下：

@parameter_name [AS] datatype[= default|NULL] [VARYING] [OUTPUT|OUT]

名稱有一個(gè)簡單的規(guī)則集合。首先，它必須以@開始。此外，命名規(guī)則除了不能有嵌套的空格外，它和SQL的命令規(guī)則是相同的。

數(shù)據(jù)類型可以使用SQL Server內(nèi)置的或用戶自定義的類型。

注意：

• 聲明CURSOR類型參數(shù)的時(shí)候，必須也使用VARYING和OUTPUT選項(xiàng)。
• OUTPUT可以簡寫為OUT。

示例：

USE Northwind

GO

CREATE PROC spInsertShipper

@CompanyName NVARCHAR(40),

@Phone NVARCHAR(24)

AS

INSERT INTO Shippers

VALUES

(@CompanyName, @Phone)

可以使用這個(gè)新的存儲過程來插入新的數(shù)據(jù)：

EXEC spInstertShipper 'Speedy Shippers, Inc.', '(503)555-5566'

因?yàn)椴]有為任何參數(shù)提供默認(rèn)值，所以需要提供兩個(gè)參數(shù)。這意味著為了成功運(yùn)行該存儲工程，則必須提供兩個(gè)參數(shù)。

1. 提供默認(rèn)值

示例：

USE Northwind

GO

CREATE PROC spInsertShipperOptionalPhone

@CompanyName NVARCHAR(40),

@Phone NVARCHAR(24) = NULL

AS

INSERT INTO Shippers

VALUES (@CompanyName, @Phone)

重新發(fā)出命令，但是使用新的存儲過程：

EXEC spInsertShipperOptionalPhone 'Speedy Shippers, Inc'

這次一切順利，成功插入了新的紀(jì)錄。

2. 創(chuàng)建輸出參數(shù)

示例：

USE Northwind

GO

CREATE PROC spInsertOrder

@CustomerID NVARCHAR(5),

@EmployeeID INT,

@OrderDate DATETIME = NULL,

@RequiredDate DATETIME = NULL,

@ShippedDate DATETIME = NULL,

@ShipVia INT,

@Freight MONEY,

@ShipName NVARCHAR(40) = NULL,

@ShipAddress NVARCHAR(60) = NULL,

@ShipCity NVARCHAR(15) = NULL,

@ShipRegion NVARCHAR(15) = NULL,

@ShipPostalCode NVARCHAR(10) = NULL,

@ShipCountry NVARCHAR(15) = NULL,

@OrderID INT OUTPUT

AS

INSERT INTO Orders

VALUES

(

@CustomerID,

@EmployeeID,

@OrderDate,

@RequiredDate,

@ShippedDate,

@ShipVia,

@Freight,

@ShipName,

@ShipAddress,

@ShipCity,

@ShipRegion,

@ShipPostalCode,

@ShipCountry

)

SELECT @OrderID = @@IDENTITY

執(zhí)行該存儲過程的代碼如下：

USE Northwind

GO

DECLARE @MyIdent INT

EXEC spInsertOrder

@CustomerID = 'ALFKI',

@EmployeeID = 5,

@OrderDate = '5/1/1999'

@ShipVia = 3,

@Freight = 5.00,

@OrderID = @MyIdenty OUTPUT

SELECT @MyIdent AS IdentityValue

SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipName

FROM Orders

WHERE OrderID = @MyIdent

需要注意以下幾點(diǎn)：

• 在存儲過程聲明中，輸出參數(shù)需要使用OUTPUT關(guān)鍵字。
• 調(diào)用存儲過程的時(shí)候也必須使用OUTPUT關(guān)鍵字，才能保證參數(shù)被正確的輸出。注意如果沒有使用OUTPUT關(guān)鍵字，不會產(chǎn)生任何錯(cuò)誤，但是此時(shí)輸出參數(shù)的值將是無法保證的。
• 賦給輸出變量的變量不需要和存儲過程中的內(nèi)部參數(shù)擁有相同的名稱。例如在本例中，內(nèi)部參數(shù)叫做@OrderID，而傳給值的變量叫做@MyIdent。
• 需要使用EXEC（或EXECUTE）關(guān)鍵字來調(diào)用存儲過程。

9.5 流控制語句

T-SQL提供了大多數(shù)流控制語句的典型的選擇，同樣也有CASE語句，但是它沒有像其他語言中預(yù)期的那種流控制級的能力。

9.5.1 IF...ELSE語句

IF...ELSE語句的實(shí)現(xiàn)方式和C語言是接近相同的?；镜恼Z法如下：

IF <Boolean Expression>

<SQL statement> | BEGIN <code series> END

[ELSE

<SQL statement> | BEGIN <code series> END]

其中的表達(dá)式可以是取布爾值的任意表達(dá)式。

提示：

不恰當(dāng)?shù)氖褂肗ULL值是個(gè)常見的陷阱。例如經(jīng)常會有如下錯(cuò)誤出現(xiàn)：

IF @MyVar = NULL

在大多數(shù)系統(tǒng)上（遵循ANSI標(biāo)準(zhǔn)）這樣的表達(dá)式永遠(yuǎn)都不會為真，并且為繞過所有的NULL值結(jié)束。想要判斷一個(gè)值是否為空應(yīng)該這樣來寫：

IF @MyVar IS NULL

不要忘記了NULL不等于任何值——甚至是NULL。不要使用"="而要使用"IS"。

1. ELSE子句

注意：

結(jié)果返回值為NULL的表達(dá)式會被當(dāng)作FALSE從而進(jìn)入ELSE子句。也就是說，如果IF子句中的語句返回值為FALSE或者NULL，則執(zhí)行ELSE子句中的語句。

示例：

USE Northwind

GO

ALTER PROC spInsertOrder

@CustomerID NVARCHAR(5),

@EmployeeID INT,

@OrderDate DATETIME = NULL,

@RequiredDate DATETIME = NULL,

@ShippedDate DATETIME = NULL,

@ShipVia INT,

@Freight MONEY,

@ShipName NVARCHAR(40) = NULL,

@ShipAddress NVARCHAR(60) = NULL,

@ShipCity NVARCHAR(15) = NULL,

@ShipRegion NVARCHAR(15) = NULL,

@ShipPostalCode NVARCHAR(10) = NULL,

@ShipCountry NVARCHAR(15) = NULL,

@OrderID INT OUTPUT

AS

DECLARE @InsertedOrderDate SMALLDATETIME

IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7

SELECT @InsertedOrderDate = NULL

ELSE

SELECT @InsertedOrderDate =

CONVERT(DATETIME, CONVERT(VARCHAR, @OrderDate, 112))

INSERT INTO Orders

VALUES

(

@CustomerID,

@EmployeeID,

@OrderDate,

@RequiredDate,

@ShippedDate,

@ShipVia,

@Freight,

@ShipName,

@ShipAddress,

@ShipCity,

@ShipRegion,

@ShipPostalCode,

@ShipCountry

)

SELECT @OrderID = @@IDENTITY

2. 把代碼分組為塊

SQL Server提供了把代碼分組為塊的方法，可以認(rèn)為這個(gè)塊是屬于一起的。這個(gè)塊以BEGIN語句開始，然后直到END語句結(jié)束。

現(xiàn)在可以修改訂單插入的存儲過程如下：

USE Northwind

GO

ALTER PROC spInsertOrder

@CustomerID NVARCHAR(5),

@EmployeeID INT,

@OrderDate DATETIME = NULL,

@RequiredDate DATETIME = NULL,

@ShippedDate DATETIME = NULL,

@ShipVia INT,

@Freight MONEY,

@ShipName NVARCHAR(40) = NULL,

@ShipAddress NVARCHAR(60) = NULL,

@ShipCity NVARCHAR(15) = NULL,

@ShipRegion NVARCHAR(15) = NULL,

@ShipPostalCode NVARCHAR(10) = NULL,

@ShipCountry NVARCHAR(15) = NULL,

@OrderID INT OUTPUT

AS

DECLARE @InsertedOrderDate SMALLDATETIME

IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7

BEGIN

SELECT @InsertedOrderDate = NULL

PRINT 'Invalid Order Date'

PRINT 'Supplied Order Date was greater than 7 days old.'

PRINT 'The value has been reset to NULL'

END

ELSE

BEGIN

SELECT @InsertedOrderDate =

CONVERT(DATETIME, CONVERT(VARCHAR, @OrderDate, 112))

PRINT 'The time of Day in Order Date was truncated'

END

INSERT INTO Orders

VALUES

(

@CustomerID,

@EmployeeID,

@OrderDate,

@RequiredDate,

@ShippedDate,

@ShipVia,

@Freight,

@ShipName,

@ShipAddress,

@ShipCity,

@ShipRegion,

@ShipPostalCode,

@ShipCountry

)

SELECT @OrderID = @@IDENTITY

9.5.2 CASE語句

CASE語句在某種程度上與一些編程語言中的一些不同語句是等價(jià)的。例如：

• C、C++、Delphi中的switch
• Visual Basic中的select case
• COBOL中的evaluate

在T-SQL中使用CASE語句的一個(gè)很大的缺點(diǎn)是：在很多方面，它更像替換運(yùn)算符而非流控制語句。

編寫CASE語句的方式不只一種——可以使用輸入表達(dá)式或者布爾表達(dá)式。第一種方法是使用一個(gè)輸入表達(dá)式來與每個(gè)WHEN子句中用到的值進(jìn)行比較。SQL Server文檔把這種方法稱為簡單CASE：

CASE <input expression>

WHEN <when expression> THEN <result expression>

[...n]

[ELSE <result expression>]

END

第二種方法將提供一個(gè)表達(dá)式，其中每個(gè)WHEN子句的值將為TRUE或者FALSE。相關(guān)文檔把它稱為搜索CASE：

CASE

WHEN <Boolean expression> THEN <result expression>

[...n]

[ELSE <result expression>]

END

可以使用CASE語句最好的方式是把它與SELECT語句放一起使用。

1. 簡單CASE

簡單CASE使用結(jié)果等于布爾值的表達(dá)式。示例：

USE Northwind

GO

SELECT TOP 10 OrderID, OrderID % 10 AS 'Last Digit', Position =

CASE OrderID % 10

WHEN 1 THEN 'First'

WHEN 2 THEN 'Second'

WHEN 3 THEN 'Third'

WHEN 4 THEN 'Fourth'

ELSE 'Something Else'

END

FROM Orders

2. 搜索CASE

搜索CASE語句和簡單CASE語句非常相同，它只有兩個(gè)很細(xì)微的不同點(diǎn)：

• 沒有輸入表達(dá)式。
• WHEN表達(dá)式必須為布爾值。

示例：

USE Northwind

GO

SELECT TOP 10 OrderID % 10 AS "Last Digit", ProductID, "How Close?" =

CASE

WHEN (OrderID % 10) < 3 THEN 'Ends with less than three'

WHEN ProductID = 6 THEN 'ProductID is 6'

WHEN ABS(OrderID % 10 - ProductID) <= 1 THEN 'Within 1'

ELSE 'More than one apart'

END

FROM OrderDetails

WHERE ProductID < 10

ORDER BY OrderID DESC

注意SQL Server求值的工作方式：

• 即使兩個(gè)條件都為真，但只使用第一個(gè)條件。
• 不需要使用"break"語句，在一個(gè)條件滿足后自動終止。
• 可以在條件表達(dá)式中混合和匹配正在使用的字段。
• 只要最后等于布爾值的結(jié)果，則可以執(zhí)行任何表達(dá)式。

9.5.3 使用WHILE語句循環(huán)

語法如下：

WHILE <boolean expression>

<sql statement> |

[BEGIN

<statement block>

[BREAK]

<sql statement>|<statement block>

[CONTINUE[

END]

在WHILE語句中必須跟上BEGIN...END，其中包含整個(gè)語句塊。

9.6 通過返回值確認(rèn)成功或失敗

返回值指示了存儲過程的成功或者失敗，甚至是成功或失敗的范圍或?qū)傩浴?/p>

RETURN的工作方式

不管是否提供返回值，程序都會收到一個(gè)返回值。SQL Server默認(rèn)地會在完成存儲過程時(shí)自動返回0。

為了從存儲過程向調(diào)用代碼返回值，只需要使用RETURN語句：

RETURN [<integer value to return>]

注意：

• 返回值必須是整數(shù)。
• RETURN語句是無條件地從存儲過程中退出的。

示例：

USE Northwind

GO

CREATE PROC spTestReturns

AS

DECLARE @MyMessage VARCHAR(50)

DECLARE @MyOtherMessage VARCHAR(50)

SELECT @MyMessage = 'Hi, it''s that line before the RETURN'

PRINT @MyMessage

RETURN

SELECT @MyOtherMessage = 'Sorry, but we won''t get this far'

PRINT @MyOtherMessage

RETURN

為了能獲取RETURN語句的值，需要在EXEC語句中把值賦給變量。

DECLARE @Return INT

EXEC @Return = spTestReturns

SELECT @Return

直接RETURN會默認(rèn)返回0，需要返回其他整數(shù)可以直接寫RETURN <integer>。

第10章 用戶自定義函數(shù)

用戶自定義函數(shù)和存儲過程非常相似，但它們也有一些行為和能力的區(qū)別。

10.1用戶自定義函數(shù)的定義

用戶自定義函數(shù)（UDF）是有序的T-SQL語句集合，該語句集合能夠預(yù)先優(yōu)化和編譯，并且可以作為一個(gè)單元來調(diào)用。它和存儲過程的主要區(qū)別在于返回結(jié)果的方式。為了能支持多種不同的返回值，UDF比存儲過程有更多地限制。

可以在使用存儲過程的時(shí)候傳入?yún)?shù)，也可以以參數(shù)的形式得到返回值。存儲過程可以返回值，不過該值是為了指示成功或失敗的，而非返回?cái)?shù)據(jù)。

然而，可以在使用UDF的時(shí)候傳入?yún)?shù)，但是可以不傳出任何值。UDF還可以返回標(biāo)量（scalar）值，這個(gè)值可以是大部分SQL Server的數(shù)據(jù)類型。UDF還可以返回表。

按照返回值的類型，UDF有兩種類型：

• 返回標(biāo)量的UDF
• 返回表的UDF

10.2 返回標(biāo)量值的UDF

這種類型的UDF和大多數(shù)SQL Server內(nèi)建的函數(shù)一樣，會向調(diào)用腳本或存儲過程返回標(biāo)量值，例如GETDATE()和USER()函數(shù)就會返回標(biāo)量值。

UDF可以返回除了BLOB、CURSOR和TIMESTAMP以外的任何SQL Server中有效的數(shù)據(jù)類型（包含用戶自定義類型）。如果想返回整數(shù)，UDF也和存儲過程不同的是：

• UDF返回值的目的是提供有意義的數(shù)據(jù)，而不是說明成功或失敗。
• 在查詢中可以內(nèi)聯(lián)地執(zhí)行函數(shù)，而使用存儲過程則不行。

示例——返回去掉時(shí)分秒的日期：

CREATE FUNCTION DayOnly(@Date DATETIME)

RETURNS VARCHAR(12)

AS

BEGIN

RETURN CONVERT(VARCHAR(12), @Date, 101)

END

函數(shù)的使用方法如下：

SELECT *

FROM Orders

WHERE DayOnly(OrderDate) = DayOnly(GETDATE())

在一個(gè)UDF中調(diào)用另一個(gè)UDF：

CREATE FUNCTION AveragePrice()

RETURNS MONEY

WITH SCHEMABINDING

AS

BEGIN

RETURN (SELECT AVG(Price) FROM Titles)

END

GO

CREATE FUNCTION PriceDifference(@Price MONEY)

RETURN MONEY

AS

BEGIN

RETURN @Price – AveragePrice()

END

使用UDF可以大大增加查詢語句的可讀性，并實(shí)現(xiàn)了代碼重用：

USE pubs

SELECT Title,

Price,

AveragePrice() AS Average,

PriceDifference(Price) AS Difference

FROM Titles

WHERE Type = 'popular_comp'

10.3 返回表的UDF

可以對UDF返回的表執(zhí)行JOIN，甚至對結(jié)果應(yīng)用WHERE條件。相對簡單的函數(shù)示例如下：

USE pubs

GO

CREATE FUNCTION fnAuthorList()

RETURN TABLE

AS

RETURN (

SELECT au_id,

au_lname + ', ' + au_fname AS au_name

address AS address1,

city + ', ' + state + ', ' + zip AS address2

FROM authors

)

GO

這樣的話，使用這個(gè)函數(shù)就像使用表一樣：

SELECT *

FROM fnAuthorList()

使用返回表的UDF比使用視圖的好處在于可以在UDF中將條件參數(shù)化，而視圖不得不包含不想要的數(shù)據(jù)，然后再通過WHERE子句過濾。例如：

USE pubs

GO

CREATE FUNCTION fnSalesCount(@SalesQty BIGINT)

RETURNS TABLE

AS

RETURN (

SELECT au.au_id,

au.aulname + ', ' + au.au_fname AS au_name,

au.address AS address1,

city + ', ' + state + ', ' + zip AS address2,

SUM(s.qty) AS SalesCount

FROM authors au

INNER JOIN titleauthor ta

ON au.au_id = ta.au_id

INNER JOIN sales s

ON ta.title_id = s.title_id

GROUP BY au.au_id,

au.au_lname + ', ' + au.au_fname,

au.address,

au.city + ', ' + au.state + ', ' + zip

HAVING SUM(qty) > @SalesQty

)

為了執(zhí)行該函數(shù)，只需要調(diào)用它并提供參數(shù)：

SELECT *

FROM fnSalesCount(25)

再進(jìn)一步，如果需要查詢每一個(gè)銷售超過25本書以上的作者和出版社的信息，這需要連接UDF返回的表：

SELECT DISTINCT p.pub_name, a.au_name

FROM dbo.fnSalesCount(25) AS a

INNER JOIN titleauthor AS ta

ON a.au_id = ta.au_id

INNER JOIN titles AS t

ON ta.title_id = t.title_id

INNER JOIN publishers AS p

ON t.pub_id = p.pub_id

這里對函數(shù)進(jìn)行了連接，就好像它是表或視圖一樣。唯一的區(qū)別在于可以對它進(jìn)行參數(shù)化。

再進(jìn)一步，UDF也可以遞歸調(diào)用，并同樣存在最深32層的限制。

第11章 事務(wù)和鎖

11.1 事務(wù)

事務(wù)是關(guān)于原子性（atomicity）的。原子性的概念是指可以把一些東西當(dāng)作一個(gè)單元來看待。

事務(wù)要有非常明確的開始和結(jié)束點(diǎn)。事實(shí)上，在SQL Server中發(fā)出的每一個(gè)SELECT、INSERT、UPDATE和DELETE語句都是隱式事務(wù)的一部分。即使只發(fā)出一條語句，也會把這條語句當(dāng)作一個(gè)事務(wù)——要么執(zhí)行語句中的所有內(nèi)容，要么什么都不執(zhí)行。確實(shí)，這一條語句默認(rèn)地將作為事務(wù)的長度。

關(guān)于事務(wù)的操作有：

• BEGIN事務(wù)：設(shè)置起始點(diǎn)。
• COMMIT事務(wù)：使得事務(wù)成為數(shù)據(jù)庫中永久的、不可撤回的一部分。
• ROLLBACK事務(wù)：本質(zhì)上說想要忘記它曾經(jīng)發(fā)生過。
• SAVE事務(wù)：創(chuàng)建一個(gè)特有的標(biāo)記符，從而可以做部分的回滾工作。

11.1.1 BEGIN TRAN

語法如下：

BEGIN TRAN|TRANSACTION [<transaction name>|<@transaction variable>]

11.1.2 COMMIT TRAN

事務(wù)的提交是完成事務(wù)的終點(diǎn)。COMMIT的語法類似于BEGIN：

COMMIT TRAN|TRANSACTION [<transaction name>|<@transaction variable>]

11.1.3 ROLLBACK TRAN

ROLLBACK可以回到開始的地方或者其中的任何一個(gè)保存點(diǎn)。ROLLBACK的語法如下：

ROLLBACK TRAN|TRANSACTION [<transaction name> | <save point name> | <@transaction variable> | <@savepoint variable>]

11.1.4 SAVE TRAN

保存事務(wù)從本質(zhì)上說是創(chuàng)建書簽。在建立"書簽"之后，可以在回滾中引用它。它的好處是可以回滾到代碼中想要的點(diǎn)上。SAVE的語法如下：

SAVE TRAN|TRANSACTION [<save point name>|<@savepoint variable>]

關(guān)于保存點(diǎn)需要記住的是ROLLBACK會清除它們——執(zhí)行ROLLBACK后之前保存過的保存點(diǎn)都會消失。

11.2 SQL Server記錄日志的工作方式

在數(shù)據(jù)庫的正常操作中，大多數(shù)執(zhí)行的活動都是"記錄"在事務(wù)日志上，而非直接寫入數(shù)據(jù)庫中。檢查點(diǎn)是指強(qiáng)制地把數(shù)據(jù)庫現(xiàn)在所使用的臟頁寫入磁盤的周期性操作。臟頁是指日志或數(shù)據(jù)頁，它們在讀入到緩存后已經(jīng)被修改，但是所進(jìn)行的修改還沒有寫入到磁盤。

11.2.1 失敗和恢復(fù)

恢復(fù)發(fā)生在SQL Server每次啟動的時(shí)候。SQL Server獲得數(shù)據(jù)庫文件，并且在最后的檢查點(diǎn)以后應(yīng)用日志中的任何提交的改變。日志中任何沒有對應(yīng)提交的改變都會回滾。

第12章 觸發(fā)器

一些常見的使用觸發(fā)器的情況包括：

• 實(shí)施參照完整性，例如數(shù)據(jù)庫或服務(wù)器中的參照完整性以及許多復(fù)雜的關(guān)系類型。
• 創(chuàng)建審計(jì)跟蹤。
• 與CHECK約束的功能相似，但是用于表、數(shù)據(jù)庫、甚至是服務(wù)器之間。
• 用自己的語句代替用戶的操作語句（通常用于允許復(fù)雜語句中的插入操作）。

12.1 觸發(fā)器的概念

觸發(fā)器是一種特殊類型的存儲過程，響應(yīng)特定的事件。有兩種類型的觸發(fā)器：數(shù)據(jù)定義語言（DDL）觸發(fā)器和數(shù)據(jù)操作語言（DML）觸發(fā)器。

DDL觸發(fā)器激活了人們以某些方式（CREATE、ALTER、DROP等）對數(shù)據(jù)庫結(jié)構(gòu)進(jìn)行修改的響應(yīng)。DML觸發(fā)器是一些加在特殊表或試圖上的代碼片段。只要加在觸發(fā)器上的事件在表中發(fā)生，觸發(fā)器中的代碼就會自動地運(yùn)行。不能顯式地調(diào)用觸發(fā)器——唯一的做法是執(zhí)行指派給表所需的操作。觸發(fā)器也沒有參數(shù)和返回值，因?yàn)槎疾恍枰?/p>

在SQL Server中可以使用3種類型的觸發(fā)器，并可以相互混合和匹配：

• INSERT觸發(fā)器
• DELETE觸發(fā)器
• UPDATE觸發(fā)器

注意，有些語句不會激活觸發(fā)器，比如TRUNCATE TABLE有與DELETE語句相似的刪除行的效果，但是不會觸發(fā)任何DELETE觸發(fā)器。

除了觸發(fā)器需要加在一個(gè)表上外，創(chuàng)建觸發(fā)器的語法類似于其他CREATE語法：

CREATE TRIGGER <trigger name>

ON [<schema name>.]<table or view name>

[WITH ENCRYPTION]

{{{FOR | ALTER} [DELETE] [,] [INSERT} [,] [UPDATE]}}

AS

<sql statements>

12.1.1 ON子句

對創(chuàng)建觸發(fā)器的對象進(jìn)行命名。注意如果觸發(fā)器的類型是AFTER（或FOR）觸發(fā)器，那么ON字句的目標(biāo)就必須是一個(gè)表（而不能是視圖），視圖只接受INSTEAD OF觸發(fā)器。

12.1.2 WITH ENCRYPTION子句

加密觸發(fā)器代碼。注意ALTER語句不會自動加密，如需加密需要再次指明WITH ENCRYPTION選項(xiàng)。

12.1 3 FOR|AFTER子句

還需要對激活觸發(fā)器的定時(shí)時(shí)間做出選擇。雖然可以使用長期接觸的FOR觸發(fā)器（也可以使用關(guān)鍵字ATFER來替換），而且這也是人們經(jīng)常考慮的一種觸發(fā)器，但是也可以使用INSTEAD OF觸發(fā)器。對這兩種觸發(fā)器的選擇將影響到是在修改數(shù)據(jù)之前還是之后來輸入觸發(fā)器。

SQL Server會將兩張表放在一起——其中的INSERTED表保存插入記錄的副本，另一張DELETED表保存刪除的任何記錄的副本。使用INSTEAD OF觸發(fā)器，創(chuàng)建這兩張工作表是發(fā)生在檢查任何約束之前，而使用FOR觸發(fā)器，這些表的創(chuàng)建是發(fā)生在檢查約束之后。使用INSTEAD OF觸發(fā)器的重點(diǎn)在于可以在視圖中清除任何不確定的插入問題。這也意味著在檢查約束之前可以采取運(yùn)動清除違反約束的情況。

使用FOR或ATFER聲明的觸發(fā)器，與INSTEAD OF觸發(fā)器最大的區(qū)別在于它們是在檢查完約束之后建立工作表的。

FOR（AFTER）子句指明了想要在哪種動作下激活觸發(fā)器。例如：

FOR INSERT, DELETE

注意之前提到過，使用FOR或AFTER子句聲明的觸發(fā)器只能加在表上，而不允許加在視圖上。

1. INSERT觸發(fā)器

每當(dāng)有人向表中插入全新的數(shù)據(jù)行的時(shí)候，都會執(zhí)行在代碼中通過FOR INSERT標(biāo)記聲明的觸發(fā)器的代碼。對于插入的每一行來說，SQL Server會創(chuàng)建該新行的副本并把它插入到稱為INSERTED的表中，該表只在觸發(fā)器的作用域內(nèi)存在。

2. DELETE觸發(fā)器

每個(gè)刪除的額記錄的副本將插入到成為DELETED表中，該表同樣只在觸發(fā)器的作用域內(nèi)存在。

3. UPDATE觸發(fā)器

SQL Server會把每一行當(dāng)作先刪除了現(xiàn)有的記錄，并插入了全新的行，所以INSERTED和DELETED表均存在。當(dāng)然，這兩個(gè)表會有完全相同數(shù)量的數(shù)據(jù)行。而DELETED表中的為改變前的數(shù)據(jù)，INSERTED表中為改變后的數(shù)據(jù)。

12.2 為了數(shù)據(jù)完整性規(guī)則使用觸發(fā)器

觸發(fā)器可以完成CHECK約束和DEFAULT約束一樣的功能，但可以使用CHECK約束和DEFAULT約束完成的功能不應(yīng)該再設(shè)置觸發(fā)器。但觸發(fā)器還是可以完成更多的功能：

• 業(yè)務(wù)規(guī)則需要引用單個(gè)表中的數(shù)據(jù)。
• 業(yè)務(wù)規(guī)則需要檢查更新的增量（更新前后的區(qū)別）。
• 需要一個(gè)定制的錯(cuò)誤信息。

12.2.1 處理來自于其他表的需求

例如，客戶支持部門的人員不斷發(fā)出已經(jīng)停止供應(yīng)的產(chǎn)品的訂單，應(yīng)該在訂單進(jìn)入系統(tǒng)之前拒絕這些訂單的錄入。

CREATE TRIGGER OrderDetailNotDiscontinued

ON OrderDetails

FOR INSERT, UPDATE

AS

IF EXISTS (

SELECT 'TRUE'

FROM Inserted i

INNER JOIN Products p

ON i.ProductID = p.ProductID

WHERE p.Discontinued = 1)

BEGIN

PAISERROR('Order Item is discontinued. Transaction Failed.', 16, 1)

ROLLBACK TRAN

END

12.2.2 使用觸發(fā)器來檢查更新的增量

例如，Northwind的存貨部門要求不能發(fā)出任何銷售某個(gè)產(chǎn)品超過其一般庫存單位的訂單。

CREATE TRIGGER ProductIsRationed

ON Products

FOR UPDATE

AS

IF EXISTS (

SELECT 'TRUE'

FROM Inserted i

INNER JOIN Deleted d

ON i.ProductID = d.ProductID

WHERE (d.UnitsInStock – i.UnitsInStock) > d.UnitsInStock / 2

AND d.UnitsInStock – i.UnitsInStock > 0

)

BEGIN

RAISERROR('Cannot reduce stock by more than 50%% at onece.', 16, 1)

ROLLBACK TRAN

END

12.3 可以關(guān)閉觸發(fā)器而不刪除它

可以使用ALTER TABLE語句來打開或關(guān)閉觸發(fā)器，語法如下：

ALTER TABLE <table name>

{ENABLE|DISABLE} TRIGGER {ALL|<trigger name>}

12.4 刪除觸發(fā)器

和刪除其他對象一樣：

DROP TRIGGER <trigger name>