SQL Server2012程式設計入門經典（第四版）（上） 讀書筆記

資料庫用了很久了，但好多東西很容易忘記，這次頭腦發熱想起來讀一遍書，做點筆記！

從第五章開始參考：《SQL Server 2005 程式設計入門經典》學習筆記

一、RDBMS基礎：SQL Server資料庫的構成

1.安裝好Sql Server一定包含以下4個系統資料庫：

• Master：此資料庫儲存一組特殊表(系統表),用于系統的總體控制。如：在伺服器建立一個資料庫，則會在Master庫中sysdatabases表中會記錄該資訊；任意的存儲過程(擴充的或系統的、同一或不同資料庫的)都存儲在Master資料庫中。總而言之，Master資料庫存儲的資訊是描述伺服器資訊的。不能删除它。
• model：其他資料庫的模闆。如果想要修改建立資料庫的樣式，可以改變model資料庫來實作。如：可以向資料庫中加入一組審計表或将使用者資訊複制到每一個建立資料庫中。但需要注意：建立資料庫的容量要比model容量大。建議不要修改此項。
• msdb：此資料庫是SQL Agent程序存儲任意系統任務的地方，如果計劃對一資料庫每天執行備份計劃，則msdb會記錄此資訊。SSIS程式包和基于政策的管理的定義就是使用msdb的程序執行個體。
• tempdb：此資料庫是伺服器的主要工作區之一。執行一個複雜或大型的操作時，伺服器可能需要建立一些中間表來完成，此項工作便是在tempdb庫中進行。隻要建立臨時表、存儲臨時資料，資訊都會儲存到tempdb資料庫中，但每次關閉伺服器時，這些資訊都會消亡。tempdb資料庫是系統中唯一完全重建的資料庫。

2.事務日志：任意資料庫的更改起初不會寫入資料庫本身，而是不斷地被寫入到事務日志，然後在某個點上，資料庫發出檢查點，在此時此刻日志中所有更改被寫到資料庫。

3.使用者和角色：使用者和角色關系密切。使用者(user)相當于登入名，是登入sql server的辨別符。登入到sql server的任何人都映射(直接或間接，取決于安全模型)到一個使用者。使用者一次屬于一個或多個角色(Role)。伺服器可以賦予使用者或角色權限，一個角色可以包含多個使用者。

4.資料類型

   常見的 int   DateTime   Date   Time   char   Varchar（可變長度字元）   NvarChar（長度可變的Unicode字元）

5.NULL

    表示未定義的或者是不能應用的值。不确定的值可設定為NULL。  不一定是0.

 NULL值不等于NULL值， NULL的真正含義是“我不知道”，而不是某種意義上的“空”。

二、SQL Server管理工具

       大部分開發人員每天使用的隻有SQL Server Management Studio工具。

三、T-SQL基本語句

主要的4條語句 SELECT    INSERT     UPDATE     DELETE  

 select語句

（1）、例子：         SELECT Name , SalesPersonID         FROM Sales.Store         WHERE  Name BETWEEN 'g' AND 'j'                        AND SalesPersonID>283         ORDER BY SalesPersonID, Name DESC 解釋，1、select語句檢索——僅僅表示讀取資料，後面跟的是列名，若為* 則表示讀取所有的列；             2、from指定要讀取的資料所在的一個表或幾個表的名稱；             3、where語句過濾——對查找的資料的一個限制。（隻要符合條件的幾行資料），這裡有一些運算符，大于号、小于号、and、or、not、betwwen、通配符like % 、in、all、any；            4、order by對結果排序（此語句一般在最後），可以按多列，逗号分隔，隻要是資料庫中的列就行，預設ASC升序，DESC降序。      5、命名規則： 關鍵字和指令 全部用大寫，而表名、列名、變量名使用大小寫混合形式（區分大小寫）。（但關鍵字是不區分大小寫的）   （2）、使用group by子句聚合資料——把同一列名下面相同的資料聚在一起。     聚合函數：（每一個聚合函數結果都産生一個新列）    1、AVG 平均值       SELECT　SalesPersonID，AVG(orderQty) AS avgorderQty    //将SalesPersonID相同的資料聚在一起，orderQty列 取平均值。as關鍵字是為了給平均值這一列取個别名    ------  GROUP BY SalesPersonID;    2、Min Max 最小最大值    3、COUNT(*) 計算查詢中傳回的行數，不忽略NULL值。。其他的聚合函數均忽略NULL值。    （3）使用HAVING子句給分組設定條件，聚合之後的過濾。   僅用于有group by子句的查詢中，放于group by之後。

（4）distinct 和all 謂詞 distinct用在select後面，去除結果中的重複行。 all與之相反，包括所有行（是select語句的預設值），但有union（聯合）子句的select語句除外，union結果自動去除重複行。

insert語句

（1）插入一行/幾行資料 insert into <table>  [(列清單)] values (第一個資料值 eg 'test','1234',---),(第二個資料值)---- 省略列清單的話，則是對所有列插入值，而且要對應。 插入數值時不用引号，插入字元資料和date資料時要用引号 （2）插入一塊資料 用insert into ----select insert into 表名1   select 表名1的列   from 另一個表/另一個資料庫的表  where ----

update語句 用來更新已有的資料

update 表名 set  列名1='新的值' ,列名2='新的值'  where ID=4; SET語句可靈活多變，可為表達式。最好不要更新主鍵。。

delete語句  删除整行或表

delete 表名 [where 限制條件] 

四、連接配接join  （操作多個表）

(1)内部連接配接（排除不比對的字段）

   僅僅傳回那些在兩個表中存在字段比對的記錄。（排他特性）類似于where子句 文法結構：SELECT　查詢的列名                 FROM 第一個表（左側）              [inner] join 第二個表（右側）         on  連接配接條件（表1的某列和表2的某列相等）

說明：inner為默 認的，可以不寫。。

（2）外部連接配接 （包含特性）（要比對資料，傳回僅一側）

left/right [outer]  左連接配接的結果來自左側的表，右連接配接來自右側的表。

（3）完全連接配接 （要比對資料，傳回兩側）

full join 傳回兩側資料表的所有資料，對方沒有比對的顯示null。 此種連接配接很少用

（4）交叉連接配接 

傳回兩側資料表的笛卡爾積，相乘。。使用CROSS關鍵字，而不使用ON SELECT v.VendorName, a.AddressName FROM Vendors v CROSS JOIN Address a   (注：v和a分别是表Vendors和 Address 的别名) （高等數學中有許多笛卡爾積的函數，，交叉連接配接常用來建立測試資料和科學領域。）

（5）聯合（UNION）

用于使兩個或兩個以上的查詢産生一個結果集。 其并不是真正的連接配接，其作用更像是将一個查詢傳回的資料附加到另一個查詢結果的末尾。。 join将資訊水準連接配接（添加更多列），而union将資料垂直連接配接（添加更多行）。 

五、 建立和修改資料表

一、 SQL Server中的對象名

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

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

模式名稱（SchemaName）

如果使用模式，那麼需要指定對象是在哪種模式下的。不同模式下可以有兩個同名的對象。如果想通路不在預設模式下的對象，那麼需要特别指明對象的模式名稱

1. 預設模式：dbo

無論誰建立了資料庫，都被認為是"資料庫所有者"，即dbo。在資料庫裡面建立的任何對象都帶有dbo模式，而不是個體的使用者名。

另外，sa（或者sysadmin角色的成員）總是dbo的别名。即無論是誰實際上擁有資料庫，sa總擁有完全的權限，就好像是dbo一樣。而且sa登入建立的任何對象都顯示所有權為dbo。

二、 CREATE語句

CREATE語句用來建立資料庫中的對象。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

)

各選項含義：

1. ON

ON用在兩個地方：一個定義存儲資料的檔案的位置，二是定義存儲日志的檔案的位置。

2. NAME

指定定義的檔案的名稱，但是隻是一個邏輯名稱——即SQL Server在内部使用該名稱引用該檔案。

3. FILENAME

指定檔案的實體名稱。資料檔案的推薦擴充名為.mdf，日志檔案的推薦擴充名為.ldf，附屬檔案的推薦擴充名為.ndf。

4. SIZE

指定檔案的初始大小。預設情況下，大小的機關是MB（兆位元組），還可以使用KB、GB或者TB。要記住，這個值至少與模型資料庫一樣大，而且必須是整數，否則将出錯。預設的值與模版資料庫一樣。

5. MAXSIZE

指定檔案長度的最大值。預設情況下，大小的機關是MB。這個選項沒有預設值，如果沒有提供該選項，則表示不限制最大值。

6. FILEGROWTH

指定當擴張檔案時每次的擴張量，可以提供一個值來說明檔案每次增加多少位元組，或者提供一個百分比，指定檔案每次增長的百分比。

7. LOG ON

指定日志檔案。注意日志檔案預設的大小是資料檔案大小的25%。其他方面，日志檔案和資料庫檔案的說明參數相同。

4.2.2 建立資料表

建立表的文法如下：

1. 表和列名稱

表和列的推薦命名規則：

• 名稱的每個單詞，首字母大寫，其他字母小寫。
• 名稱盡量短，但是要具有描述性。限制使用縮寫，隻使用大家都能了解的縮寫。例如"ID"表示辨別、"No"表示數字、"Org"表示組織。
• 當名稱中有兩個單詞時，不要用任何分隔符。

2. 資料類型

注意沒有預設的資料類型

3. DEFAULT

如果要使用預設值，就必須緊跟在資料類型之後給定這個值。

4. IDENTITY

當你設定一個列為辨別列時，SQL Server自動配置設定一個順序号給你插入的每個行。注意IDENTITY列和PRIMARY KEY列是完全不同的概念，既不會因為有一個IDENTITY列就說明這個值是唯一的（例如，可以重新設定種子，使用前面用過的值）。IDENTITY值通常用于PRIMARY KEY列，但并不是必須這樣使用。

6. NULL/NOT NULL

預設的設定是列值是NOT NULL，除非指定允許為空。然而，有很多不同的設定可以改變這個設定，進而影響這個預設值。

7. 列限制

列限制就是對單個列設定的關于該列可插入資料的限制和規則。

9. 表限制

表限制和列限制很相似，但表限制可以基于多個列。表層次的限制包括PRIMARY KEY限制、FOREIGN KEY限制以及CHECK限制。

12. 建立一個表

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

更經常的情況是改變表的結構。這個可以是增加、删除一列或者改變一列的資料類型等。示例：

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]

如果需要， 可以同時删除兩個表：

USE Accounting

DROP TABLE Customers, Employees

删除整個資料庫：

DROP DATABASE Accounting

4.5 使用GUI工具

SQL Server Management Studio  不用寫sql語句，圖形化操縱資料庫。

第5章 鍵和限制

   確定資料的完整性不是使用資料的程式的責任，而是資料庫本身的責任。将資料完整性的責任移到資料庫本身是資料庫管理的一次革命。

較高層次上的3種不同類型的限制：

• 實體限制
• 域限制
• 參照完整性限制

具體的限制類型：

• PRIMARY KEY限制
• FOREIGN KEY限制
• UNIQUE限制（唯一限制）
• CHECK限制
• DEFAULT限制

5.1 限制的類型

5.1.1 域限制

域限制處理一個或多個列，確定一個特定列或一組特定列滿足特定的标準。

5.1.2 實體限制

實體限制都是關于每個行的。這種形式的限制并不關心一個整體的列，隻對特定的行感興趣，如PRIMARY KEY限制和UNIQUE限制。

5.1.3 參照完整性限制

參照完整性限制是在某列的值必須與其他列的值比對時建立的，列可以在同一個表中，或者更通常的是在不同的表中，如FOREIGN KEY限制。

5.2 限制命名

常見的限制的推薦命名規則如下：

• CHECK限制以CK開頭、主鍵限制以PK開頭、外鍵限制以FK開頭、唯一限制以UN開頭。
• 後接表名、列名。

如在Customers表上對PhoneNo列設定限制：CK_Customers_PhoneNo，Customers表上的主鍵限制：PK_Custoemrs_CustomerID。

5.3 鍵限制

常用的鍵類型：主鍵、外鍵、唯一限制。

5.3.1 主鍵限制

1. 在建立表的時候建立主鍵限制。主鍵：每行的唯一辨別符，必須包含唯一的值（是以不能為null）。

一個表中最多可以有一個主鍵。

CREATE TABLE Customers

(

    CustomerNo INT IDENTITY NOT NULL PRIMARY KEY,

    ......

)

2. 在已存在的表上建立主鍵限制。

USE Accounting

ALTER TABLE Employees

    ADD CONSTRAINT PK_EmployeeID

        PRIMARY KEY (EmployeeID)

5.3.2 外鍵限制

    外鍵既能確定資料完整性，也能表現表之間的關系。一個表中可以有多個（0-253）外鍵，但一個給定的列隻能引用一個外鍵，，一個外鍵可以涉及多列。

在CREATE語句中設定一列或幾列外鍵限制的文法如下所示：

<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}]

示例： （建立了由四列組成的Orders表）

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

)

上面的聲明：将表Orders的CustomerNo列聲明為依賴于外部列（Customers.CustomerNo）

1. 在已存在的表中添加一個外鍵

ALTER TABLE Orders

    ADD CONSTRAINT FK_EmployeeCreatesOrder

        FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)

2. 使一個表自引用

在實際建立自引用限制之前，很關鍵的一點是在添加外鍵之前表中至少有一行。

ALTER TABLE Employees

    ADD CONSTRAINT FK_EmployeeHasManager

        FOREIGN KEY (ManagerEmpID) REFERENCES Employees(EmployeeID)

 注：SQL Server不允許删除一個被其他表引用的表（被引用表）。必須先在引用的表中删除外鍵，才可以删除被引         用表。

3. 級關聯作

外鍵是雙向的，即不僅是限制子表的值必須存在于父表中，還在每次對父表操作後檢查子行。SQL Server的預設行為

是在子表存在時"限制"父表不被删除。然而，有時會自動删除任何相關記錄，而不是防止删除被引用的記錄。同樣，

在更新記錄時，可能希望相關的記錄自動引用剛剛更新的記錄。這種進行自動删除和更新的過程稱為級聯。通過修改

聲明外鍵的文法——添加ON子句，來定義級聯操作。

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（級聯），則操作會從父表級聯到子表中。即，如果從父表删除了某項，子表中依賴該項的項都會被删除；如果從父表中更新了某項，則子表中依賴該項的字段也會被更新。

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

4. 其他操作

NO ACTION為預設操作，即如果子表有依賴，則禁止對父表中的該字段進行删除和更新操作。

SET NULL操作會在父表中的該字段被删除或者更新時，将子表中的依賴項設為NULL，前提是子表中的該項可為NULL值。

SET DEFAULT操作會在父表中的該字段被删除或者更新時，将子表中的依賴項設為在子表中定義的預設值，當然前提是在子表中該字段有預設值。

5.3.3 唯一限制

要求指定的列上有一個唯一值，表中可以有多個唯一限制（而主鍵隻能有一個）

唯一限制不會自動防止您設定一個NULL值。是否允許NULL值取決于表中相應列的NULL選項的設定。然而，要記住如果您确實允許NULL值，那麼隻能插入一個NULL。

在建立表時設定唯一限制：

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

)

在已存在的表中建立唯一限制：

ALTER TABLE Employees

    ADD CONSTRAINT AK_EmployeeSSN

        UNIQUE (SSN)

在限制名稱中的AK字首代表"交替鍵（Alternate Key）"，也可以使用字首UQ或者簡單的U，代表唯一限制。

5.4 CHECK限制

CHECK限制使用與WHERE字句一樣的規則來定義。CHECK限制标準的示例如下：

目标	SQL
限制Month列為合适的數字	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的一個特定清單	IN ('UPS', 'Fed Ex', 'USPS')
價格必須為正	UnitPrice >= 0
在同一行中引用另外一個列	ShipDate >= OrderDate

在已存在的表中添加CHECK限制：

ALTER TABLE Customers

    ADD CONSTRAINT CK_CustomerDateInSystem

        CHECK (DateInSystem <= GETDATE())

試着插入違反CHECK限制的記錄會得到錯誤。

5.5 DEFAULT限制

DEFAULT限制定義了當插入新行時，在您定義了預設限制的列中沒有資料時填充的預設值。要注意：

• 預設值隻在INSERT語句中使用——在UPDATE語句和DELETE語句中被忽略。
• 如果在INSERT語句中提供了任意的值（包括NULL值），那麼就不使用預設值。
• 如果沒有提供值，那麼總是使用預設值。

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 在建立限制時忽略無效的資料

預設情況下，除非已存在的資料滿足限制标準，否則SQL Server将不會建立限制。要想在建立限制時，不檢查已經在表中的資料是否滿足限制，可以在添加限制時添加WITH NOCHECK選項。示例：

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 臨時使已存在的限制失效

使用NOCHECK選項關閉限制，而不是删除它。示例：

ALTER TABLE Customers

    NOCHECK

    CONSTRAINT CK_CustomerPhoneNo

當準備重新讓限制起效時，使用CHECK選項代替NOCHECK：

ALTER TABLE Customers

    CHECK

    CONSTRAINT CK_CustomerPhoneNo

第6章 更複雜的查詢

6.1 子查詢的概念

子查詢是嵌套在另外一個查詢中的正常的T-SQL查詢。在有一個SELECT語句作為部分資料或者另外一個查詢的條件

的基礎時，通過使用括号建立子查詢。

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

• 将一個查詢分隔為一系列的邏輯步驟。
• 提供一個清單作為WHERE子句或者IN、EXISTS、ANL、SOME、ALL的目标
• 為父查詢中的每個記錄提供一個查詢表。

注意：大多數的子查詢可用連接配接JOIN來寫，且應先考慮連接配接。

6.2 嵌套的子查詢

嵌套的子查詢隻在一個方向嵌套——傳回在外部查詢中使用的單個值，或者在IN運算符中使用的一個完整的值清單。

在最松散的意義上說，查詢文法看起來像下面的兩個文法模闆：

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 使用單個值的SELECT語句的嵌套查詢

例如，假設希望知道每一天通過系統銷售的産品的每個條目的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. 使用傳回多個值的子查詢的嵌套查詢

例如，檢視所有具有折扣記錄的商店清單：

USE Pubs

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

FROM Stores

WHERE stor_id IN (SELECT stor_id FROM Discounts)

注：出于性能方面的考慮，應使用連接配接方法作為預設解決方案，除非特别理由要使用嵌套的SELECT。

6.2.3. 使用嵌套的SELECT來發現孤立的記錄

這種嵌套的SELECT和前面示例幾乎相同，差別是添加了NOT運算符。這個不同點時的在轉化連接配接文法時設定等于外部連接配接而不是内部連接配接。例如，需要查詢所有在Pubs資料庫中沒有比對的折扣記錄的商店：

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 關聯的子查詢

6.3.1 關聯的子查詢的工作原理

在互相關聯的子查詢中，内部查詢在外部查詢提供的資訊上運作，反之亦然。資訊傳遞是雙向的。而嵌套查詢是單向的，内部查詢再外部查詢。 關聯查詢有3個步驟的處理過程：

• 外部查詢獲得一個記錄，然後将該記錄傳遞到内部查詢。
• 内部查詢根據傳遞的值執行。
• 内部查詢然後将結果值傳回到外部查詢，外部查詢利用這些值完成處理過程。

6.3.2 在WHERE子句中的互相關聯的子查詢

例如，需要查詢系統中每個顧客第一個訂單的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清單中的互相關聯的子查詢

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

SELECT cu.CompanyName,

    (SELECT MIN(OrderDate)

    FROM Orders o

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

FROM Customers cu

6.3.4 處理NULL資料——ISNULL函數

ISNULL()接受一個變量或者表達式來驗證是否是一個空值。如果值确實是NULL，那麼函數傳回其他預指定的值。如果原來的值不是NULL，那麼傳回原來的值。文法如下：

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

是以，示例如表所示：

ISNULL表達式	傳回值
ISNULL(NULL, 5)	5
ISNULL(5, 15)	5
ISNULL(MyColumnName, 0) where MyColumnName IS NULL
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 派生表

派生表，它是虛表，在資料庫中不存在的，是我們建構的，目的是為了縮小資料的查找範圍。      

例如，現在需要查詢既訂購了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運算符

與IN關鍵字很相似。

使用EXISTS時，根據是否存在資料滿足查詢中EXISTS語句所建立的标準，傳回一個簡單的TRUE和FALSE。并不真正傳回資料。例如：

SELECT CustomerID, CompanyName

FROM Customers cu

WHERE EXISTS (

    SELECT OrderID

    FROM Orders o

    WHERE o.CustomerID = cu.CustomerID)

相比連接配接來說，性能更高。當使用EXISTS關鍵字時，SQL Server不需要執行一行一行的連接配接，而是尋找記錄，直到找到第一個比對的記錄，停止在那裡。隻要有一個比對，EXISTS就為真，不需要繼續查找。

如果需要查詢沒有訂購任何産品的客戶，可以使用NOT EXISTS：

SELECT CustomerID, CompanyName

FROM Customers cu

WHERE NOT EXISTS (

    SELECT OrderID

    FROM Orders o

    WHERE o.CustomerID = cu.CustomerID)

6.5 資料類型轉換：CAST和CONVERT

CAST和CONVERT都可以執行資料類型轉換。在大部分情況下，兩者執行相同的功能，不同的是CONVERT還提供一些日期格式轉換，而CAST沒有這個功能。

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

各自的文法如下：

CAST (expression AS data type)

CONVERT (data type, expression[, style])

CAST和CONVERT可以進行很多資料類型轉換，在SQL Server不進行隐式轉換時，需要這種轉換。例如：

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

FROM Orders

WHERE CustomerID = 'ALFKI'

例如，需要将timestamp列轉換為正常數字。一個timestamp是個二進制數字，是以需要轉換：

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

FROM ConvertTest

還可以轉換日期：

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函數最後一個代碼說明需要的格式。注意，任何以超過100表示的是4位的年份；小于100的是兩位數字的年份，不過有很少的一些例外，并且小于100表示的格式加上100後即為對應的4位的年份表示的格式。