《SQL 必知必會》讀書筆記-有解無憂

第1課了解 SQL

這章主要介紹了資料庫，表，欄位型別，行，列，主鍵和SQL等基本概念，

資料庫：以某種形式存盤的資料集合，在計算機上的表現形式可能是一個檔案或者一組檔案，我們平時所說的資料庫，往往指MySQL或者Oracle這些資料庫管理系統，資料庫管理系統用來創建操作資料庫，
資料表：一個二維資料結構，
列和資料型別
資料行
主鍵：能夠唯一標識一條記錄的一列（或幾列）資料，主鍵具有下面的特點：
- 任意兩行都不具有相同的主鍵值；
- 每一行都必須具有一個主鍵值（主鍵列不允許NULL值）；
- 主鍵列中的值不允許修改或更新；
- 主鍵值不能重用（如果某行從表中洗掉，它的主鍵不能賦給以后的新行），

主鍵通常定義在表的一列上，但并不是必需這么做，也可以一起使用多個列作為主鍵，在使用多列作為主鍵時，上述條件必須應用到所有列，所有列值的組合必須是唯一的，

SQL語言：結構化查詢語言，是一種專門用來和資料庫互動的語言，標準SQL由ANSI標準委員會管理，從而稱為ANSI SQL，主流的資料庫管理系統，比如MySQL，Oracle等都支持標準的SQL，但是資料庫廠商都會對ANSL SQL進行擴展，如PL/SQL、Transact-SQL等，

第2課檢索資料（select）

關于SQL陳述句的一些說明：

有些資料庫管理系統需要在陳述句末尾加上;有的不需要加，當然，如果愿意可以總是加上分號，事實上，即使不一定需要，加上分號也肯定沒有壞處，

SQL陳述句和大小寫：SQL陳述句不區分大小寫，因此SELECT與select是相同的，同樣，寫成Select也沒有關系，許多SQL開發人員喜歡對SQL關鍵字使用大寫，而對列名和表名使用小寫，這樣做使代碼更易于閱讀和除錯，不過，一定要認識到雖然SQL是不區分大小寫的，但是表名、列名和值可能有所不同（這有賴于具體的DBMS及其如何實作），

在處理SQL陳述句時，其中所有空格都被忽略，SQL陳述句可以寫成長長的一行，也可以分寫在多行，

選擇多個資料列

SELECT prod_id, prod_name, prod_price
FROM Products;

查詢所有列

SELECT ＊ FROM Products;

一般而言，除非你確實需要表中的每一列，否則最好別使用＊通配符，雖然使用通配符能讓你自己省事，不用明確列出所需列，但檢索不需要的列通常會降低檢索和應用程式的性能，

檢索不同的值（distinct）

SELECT DISTINCT vend_id FROM Products;

DISTINCT關鍵字作用于所有的列，不僅僅是跟在其后的那一列，例如，你指定 SELECT DISTINCT vend_id, prod_price，因為指定的兩列不完全相同，所以所有的行都會被檢索出來，

限制查詢回傳結果數

在SQL Server和Access中使用SELECT時，可以使用TOP關鍵字來限制最多回傳多少行，

SELECT TOP 5 prod_name FROM Products;

如果你使用Oracle，需要基于ROWNUM（行計數器）來計算行

SELECT prod_name FROM Products
WHERE ROWNUM ＜=5;

如果你使用MySQL、MariaDB、PostgreSQL或者SQLite，需要使用LIMIT子句，像這樣：

SELECT prod_name FROM Products
LIMIT 5;

這些資料庫還支持offset關鍵字，用于指定從哪行資料開始回傳，比如：limit n offset m 表示從m+1條開始回傳，一共回傳n條，

MySQL、MariaDB和SQLite支持簡化版的LIMIT 4 OFFSET 3陳述句，即LIMIT 3,4，使用這個語法，逗號之前的值對應OFFSET，逗號之后的值對應LIMIT，

使用注釋

在sql陳述句中，使用“--”來進行陳述句注釋

第3課排序查詢資料

這一課講授如何使用SELECT陳述句的ORDER BY子句，根據需要排序檢索出的資料，

SELECT prod_name FROM Products
ORDER BY prod_name;

在指定一條ORDER BY子句時，應該保證它是SELECT陳述句中最后一條子句，如果它不是最后的子句，將會出現錯誤訊息，
通常，ORDER BY子句中使用的列將是為顯示而選擇的列，但是，實際上并不一定要這樣，用非檢索的列排序資料是完全合法的，

按照多個列排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

對于上述例子中的輸出，僅在多個行具有相同的prod_price值時才對產品按prod_name進行排序，如果prod_price列中所有的值都是唯一的，則不會按prod_name排序，

按照列位置排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

ORDER BY 2表示按SELECT清單中的第二個列prod_price進行排序，ORDER BY 2, 3表示先按prod_price，再按prod_name進行排序，

不建議使用這個排序方式

指定排序方向

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

DESC關鍵字只應用到直接位于其前面的列名，在上例中，只對prod_price列指定DESC，對prod_name列不指定，因此，prod_price列以降序排序，而prod_name列（在每個價格內）仍然按標準的升序排序，

默認升序排序（aesc）,降序使用desc

第4課過濾資料

這一課將講授如何使用SELECT陳述句的WHERE子句指定搜索條件，

WHERE子句運算子

上面列出的某些運算子是冗余的（如＜ >與！=相同，! ＜相當于>=），并非所有DBMS都支持這些運算子，想確定你的DBMS支持哪些運算子，請參閱相應的檔案，

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10

問題:between是否包括邊界值

上面也可以寫成

prod_price > 5 and prod_price < 10

不能寫成

5<prod_price<10

在sql陳述句中，字串用單引號包裹，而不是雙引號

第5課高級資料過濾

這一課講授如何組合WHERE子句以建立功能更強、更高級的搜索條件（and 和 or的使用），我們還將學習如何使用NOT和IN運算子

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;

SQL（像多數語言一樣）在處理OR運算子前，優先處理AND運算子(and 的優先級高于or)，當SQL看到上述WHERE子句時，它理解為：由供應商BRS01制造的價格為10美元以上的所有產品，以及由供應商DLL01制造的所有產品，而不管其價格如何，

此問題的解決方法是使用圓括號對運算子進行明確分組，

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

圓括號具有比AND或OR運算子更高的求值順序，所以DBMS首先過濾圓括號內的OR條件，這時，SQL陳述句變成了選擇由供應商DLL01或BRS01制造的且價格在10美元及以上的所有產品，這正是我們希望的結果，

任何時候使用具有AND和OR運算子的WHERE子句，都應該使用圓括號明確地分組運算子，不要過分依賴默認求值順序，即使它確實如你希望的那樣，使用圓括號沒有什么壞處，它能消除歧義，

in操作的優點

在有很多合法選項時，IN運算子的語法更清楚，更直觀，
在與其他AND和OR運算子組合使用IN時，求值順序更容易管理，
IN運算子一般比一組OR運算子執行得更快（在上面這個合法選項很少的例子中，你看不出性能差異），
IN的最大優點是可以包含其他SELECT陳述句，能夠更動態地建立WHERE子句，(子查詢)

NOT運算子

WHERE子句中的NOT運算子有且只有一個功能，那就是否定其后所跟的任何條件，

第6課用通配符進行過濾

like 操作讀符

利用like可以構建比較特殊的查詢條件，比如查詢包含某個關鍵字的行，

通配符搜索只能用于文本欄位（字串），非文本資料型別欄位不能使用通配符搜索，

百分號（%）通配符

%表示任何字符出現任意次數，%代表搜索模式中給定位置的0個、1個或多個字符，

根據DBMS的不同及其配置，搜索可以是區分大小寫的，如果區分大小寫，則’fish%’與Fish bean bag toy就不匹配，
通配符%看起來像是可以匹配任何東西，但有個例外，這就是NULL，子句WHERE prod_name LIKE '%’不會匹配產品名稱為NULL的行，

下劃線（_）通配符

下劃線的用途與%一樣，但它只匹配單個字符，而不是多個字符，

方括號（[ ]）通配符

方括號（[]）通配符用來指定一個字符集，它必須匹配指定位置（通配符的位置）的一個字符，

找出所有名字以J或M起頭的聯系人，可進行如下查詢：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'

此通配符可以用前綴字符^（脫字號）來否定，例如，下面的查詢匹配以J和M之外的任意字符起頭的任意聯系人名：

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'

當然，也可以使用NOT運算子得出類似的結果，

SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'

使用通配符的技巧

SQL的通配符很有用，但這種功能是有代價的，即通配符搜索一般比前面討論的其他搜索要耗費更長的處理時間，

這里給出一些使用通配符時要記住的技巧，

不要過度使用通配符，如果其他運算子能達到相同的目的，應該使用其他運算子，
在確實需要使用通配符時，也盡量不要把它們用在搜索模式的開始處，把通配符置于開始處，搜索起來是最慢的，
仔細注意通配符的位置，如果放錯地方，可能不會回傳想要的資料，

第7課創建計算欄位

這一課介紹什么是計算欄位，如何創建計算欄位，以及如何從應用程式中使用別名參考它們，

計算欄位

存盤在資料庫表中的資料一般不是應用程式所需要的格式，下面舉幾個例子，

需要顯示公司名，同時還需要顯示公司的地址，但這兩個資訊存盤在不同的表列中，
城市、州和郵政編碼存盤在不同的列中（應該這樣），但郵件標簽列印程式需要把它們作為一個有恰當格式的欄位檢索出來，
列資料是大小寫混合的，但報表程式需要把所有資料按大寫表示出來，
物品訂單表存盤物品的價格和數量，不存盤每個物品的總價格（用價格乘以數量即可），但為列印發票，需要物品的總價格，
需要根據表資料進行諸如總數、平均數的計算，

在上述每個例子中，存盤在表中的資料都不是應用程式所需要的，我們需要直接從資料庫中檢索出轉換、計算或格式化過的資料，而不是檢索出資料，然后再在客戶端應用程式中重新格式化，

這就是計算欄位可以派上用場的地方了，與前幾課介紹的列不同，計算欄位并不實際存在于資料庫表中，計算欄位是運行時在SELECT陳述句內創建的，

在SQL陳述句內可完成的許多轉換和格式化作業都可以直接在客戶端應用程式內完成，但一般來說，在資料庫服務器上完成這些操作比在客戶端中完成要快得多，

拼接欄位

關于字串拼接

Access和SQL Server使用+號，DB2、Oracle、PostgreSQL、SQLite和Open Office Base使用||，在MySQL和MariaDB中，必須使用特殊的函式，

但是基本上，所有的資料庫都有拼接函式，建議使用拼接函式來進行字串拼接，

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM Vendors

說明：TRlM函式
大多數DBMS都支持RTRIM()（正如剛才所見，它去掉字串右邊的空格）、LTRIM()（去掉字串左邊的空格）以及TRIM()（去掉字串左右兩邊的空格），

別名的使用

SELECT Concat(vend_name, ' (',vend_country, ')')
    AS vend_title
FROM Vendors
ORDER BY vend_name;

Oracle 中沒有 as 關鍵字，

執行算術計算

計算欄位的另一常見用途是對檢索出的資料進行算術計算，

SELECT prod_id,
    quantity,
    item_price,
    quantity＊item_price AS expanded_price FROM OrderItems
WHERE order_num = 2008;

支持加減乘除等運算子，

第8課使用函式處理資料

函式

SQL也可以用函式來處理資料，函式一般是在資料上執行的，為資料的轉換和處理提供了方便，

雖然使用函式可以給我們帶來方便，但是我們也應該要意識到函式給我們帶來的問題：每個資料庫都有自己的函式實作，只有很少幾個函式是可以在多數資料庫中通用的，也就是說你使用了一個特定的函式后，移植到其他資料庫就需要重新調整sql，

使用函式

大多數SQL實作支持以下型別的函式，

用于處理文本字串（如洗掉或填充值，轉換值為大寫或小寫）的文本函式，
用于在數值資料上進行算術操作（如回傳絕對值，進行代數運算）的數值函式，
用于處理日期和時間值并從這些值中提取特定成分（如回傳兩個日期之差，檢查日期有效性）的日期和時間函式，
回傳DBMS正使用的特殊資訊（如回傳用戶登錄資訊）的系統函式，

日期處理函式，每個資料庫的實作差異性都很大，

數值處理函式僅處理數值資料，這些函式一般主要用于代數、三角或幾何運算，因此不像字串或日期-時間處理函式使用那么頻繁，

具有諷刺意味的是，在主要DBMS的函式中，數值函式是最一致、最統一的函式下面列出一些常用的數值處理函式，

第9課匯總資料（聚合函式）重點

這一課介紹什么是SQL的聚集函式，如何利用它們匯總表的資料，

聚集函式（聚合函式）

我們經常需要匯總資料而不用把它們實際檢索出來，為此SQL提供了專門的函式，使用這些函式，SQL查詢可用于檢索資料，以便分析和報表生成，這種型別的檢索例子有：

確定表中行數（或者滿足某個條件或包含某個特定值的行數）；
獲得表中某些行的和；
找出表列（或所有行或某些特定的行）的最大值、最小值、平均值，

上面的列子中，需要原始資料參與計算，但是最后的結構又不需要這些原始資料，而是需要計算出來的匯總資訊，

為方便這種型別的檢索，SQL給出了5個聚集函式，這些函式能進行上述檢索，與前一章介紹的資料處理函式不同，SQL的聚集函式在各種主要SQL實作中得到了相當一致的支持，

AVG()函式

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

AVG()函式忽略列值為NULL的行，

COUNT()函式
COUNT()函式進行計數，可利用COUNT()確定表中行的數目或符合特定條件的行的數目，

COUNT()函式有兩種使用方式：

使用COUNT(＊)對表中行的數目進行計數，不管表列中包含的是空值（NULL）還是非空值，
使用COUNT(column)對特定列中具有值的行進行計數，忽略NULL值，

MAX()函式

MAX()回傳指定列中的最大值，MAX()要求指定列名，

雖然MAX()一般用來找出最大的數值或日期值，但許多（并非所有）DBMS允許將它用來回傳任意列中的最大值，包括回傳文本列中的最大值，在用于文本資料時，MAX()回傳按該列排序后的最后一行，
MAX()函式忽略列值為NULL的行，

min函式*
用法和max函式類似

SUM()函式
SUM()用來回傳指定列值的和（總計），

SELECT SUM(item_price＊quantity) AS total_price
FROM OrderItems
WHERE order_num = [20005](http://tel:20005/);

如本例所示，利用標準的算術運算子，所有聚集函式都可用來執行多個列上的計算，
SUM()函式忽略列值為NULL的行，

PS:我們寫sql陳述句時也要有判斷空值的意識，想著我們選擇的欄位要是null，程式會不會報例外，會不會影響我們的業務邏輯

聚集不同

以上5個聚集函式都可以如下使用，

對所有行執行計算，指定ALL引數或不指定引數（因為ALL是默認行為），
只包含不同的值，指定DISTINCT引數，

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

加了distinct引數后，如果有多個prod_price的值一樣，只取其中一個進行計算，

DISTINCT不能用于COUNT(＊)，類似地，DISTINCT必須使用列名，不能用于計算或運算式，

組合聚集函式

目前為止的所有聚集函式例子都只涉及單個函式，但實際上，SELECT陳述句可根據需要包含多個聚集函式，

SELECT COUNT(＊) AS num_items,
    MIN(prod_price) AS price_min,
    MAX(prod_price) AS price_max,
    AVG(prod_price) AS price_avg
FROM Products;

第10課分組資料

這一課介紹如何分組資料，以便匯總表內容的子集，這涉及兩個新SELECT陳述句子句：GROUP BY子句和HAVING子句，

資料分組

所謂資料分組，就是先對選出來的資料分組，再對這些分組資料進行聚合計算，

group by 一般和聚合函式配合使用，

創建分組

SELECT vend_id, COUNT(＊) AS num_prods
FROM Products GROUP BY vend_id;

使用GROUP BY子句前，需要知道一些重要的規定，

GROUP BY子句可以包含任意數目的列，因而可以對分組進行嵌套，更細致地進行資料分組，
如果在GROUP BY子句中嵌套了分組，資料將在最后指定的分組上進行匯總，換句話說，在建立分組時，指定的所有列都一起計算（所以不能從個別的列取回資料），
GROUP BY子句中列出的每一列都必須是檢索列或有效的運算式（但不能是聚集函式），如果在SELECT中使用運算式，則必須在GROUP BY子句中指定相同的運算式，不能使用別名，
大多數SQL實作不允許GROUP BY列帶有長度可變的資料型別（如文本或備注型欄位），
除聚集計算陳述句外，SELECT陳述句中的每一列都必須在GROUP BY子句中給出，
如果分組列中包含具有NULL值的行，則NULL將作為一個分組回傳，如果列中有多行NULL值，它們將分為一組，
GROUP BY子句必須出現在WHERE子句之后，ORDER BY子句之前，

過濾分組

過濾分組的作用:根據分組以后得到的統計資料，進一步對資料進行過濾，

比如:你可能想要列出至少有兩個訂單的所有顧客，

我們已經看到了WHERE子句的作用，但是，在這個例子中WHERE不能完成任務，因為WHERE過濾指定的是行而不分組，事實上，WHERE沒有分組的概念，

那么，不使用WHERE使用什么呢？SQL為此提供了另一個子句，就是HAVING子句，HAVING非常類似于WHERE，事實上，目前為止所學過的所有型別的WHERE子句都可以用HAVING來替代，唯一的差別是，WHERE過濾行，而HAVING過濾分組，

WHERE子句的條件（包括通配符條件和帶多個運算子的子句），這些有關WHERE的所有技術和選項都適用于HAVING，它們的句法是相同的，只是關鍵字有差別，

SELECT cust_id, COUNT(＊) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(＊) >= 2;

HAVING和WHERE的差別
WHERE在資料分組前進行過濾，HAVING在資料分組后進行過濾，這是一個重要的區別，WHERE排除的行不包括在分組中，這可能會改變計算值，從而影響HAVING子句中基于這些值過濾掉的分組，

分組和排序

GROUP BY和ORDER BY經常完成相同的作業，但它們非常不同，理解這一點很重要，

我們經常發現，用GROUP BY分組的資料確實是以分組順序輸出的，但并不總是這樣，這不是SQL規范所要求的，

一般在使用GROUP BY子句時，應該也給出ORDER BY子句，這是保證資料正確排序的唯一方法，千萬不要僅依賴GROUP BY排序資料，

SELECT子句順序

第11課使用子查詢

子查詢

即嵌套在其他查詢中的查詢，

利用子查詢進行過濾

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
           FROM OrderItems
           WHERE prod_id = 'RGAN01');

在SELECT陳述句中，子查詢總是從內向外處理，

注意：只能是單列
作為子查詢的SELECT陳述句只能查詢單個列，企圖檢索多個列將回傳錯誤，
子查詢的性能:如果子查詢嵌套的層次太多，性能會出問題，而且又是多層次的子查詢并不是解決問題的最好方法，

作為計算欄位使用子查詢

前面介紹的功能是作為in操作的條件，

SELECT cust_name,
    cust_state,
    (SELECT COUNT(＊)
    FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

第12課聯結表

聯結

將兩張表通過聯系欄位拼接起來，

創建聯結

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

笛卡兒積（cartesian product）
由沒有聯結條件的表關系回傳的結果為笛卡兒積，檢索出的行的數目將是第一個表中的行數乘以第二個表中的行數，

行內結
上面使用的聯結稱為等值聯結（equijoin），它基于兩個表之間的相等測驗，這種聯結也稱為行內結（inner join），其實，可以對這種聯結使用稍微不同的語法，明確指定聯結的型別，下面的SELECT陳述句回傳與前面例子完全相同的資料：

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
 ON Vendors.vend_id = Products.vend_id;

ANSI SQL規范首選INNER JOIN語法，之前使用的是簡單的等值語法，其實，SQL語言純正論者是用鄙視的眼光看待簡單語法的，

聯結多個表
SQL不限制一條SELECT陳述句中可以聯結的表的數目，創建聯結的基本規則也相同，首先列出所有表，然后定義表之間的關系，

SELECT prod_name, vend_name, prod_price, quantity FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
 AND OrderItems.prod_id = Products.prod_id
 AND order_num = [20007](http://tel:20007/);

注意：性能考慮
DBMS在運行時關聯指定的每個表，以處理聯結，這種處理可能非常耗費資源，因此應該注意，不要聯結不必要的表，聯結的表越多，性能下降越厲害，
注意：聯結中表的最大數目
雖然SQL本身不限制每個聯結約束中表的數目，但實際上許多DBMS都有限制，

第13課創建高級聯結

使用表別名

SQL除了可以對列名和計算欄位使用別名，還允許給表名起別名，

注意：Oracle中沒有AS
Oracle不支持AS關鍵字，要在Oracle中使用別名，可以不用AS，簡單地指定列名即可（因此，應該是Customers C，而不是Customers AS C），

使用不同型別的聯結

迄今為止，我們使用的只是行內結或等值聯結的簡單聯結，現在來看三種其他聯結：自聯結（self-join）、自然聯結（natural join）和外聯結（outer join），

自聯結
假如要給與Jim Jones同一公司的所有顧客發送一封信件，

常規做法:子查詢

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
          FROM Customers
          WHERE cust_contact = 'Jim Jones');

自鏈接的做法:

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
 AND c2.cust_contact = 'Jim Jones';

提示：用自聯結而不用子查詢
自聯結通常作為外部陳述句，用來替代從相同表中檢索資料的使用子查詢陳述句，雖然最終的結果是相同的，但許多DBMS處理聯結遠比處理子查詢快得多，應該試一下兩種方法，以確定哪一種的性能更好，

自然聯結
可以不用重點關注

外聯結
許多聯結將一個表中的行與另一個表中的行相關聯，但有時候需要包含沒有關聯行的那些行，例如，可能需要使用聯結完成以下作業：

對每個顧客下的訂單進行計數，包括那些至今尚未下訂單的顧客；

在上述例子中，聯結包含了那些在相關表中沒有關聯行的行，這種聯結稱為外聯結，

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

與行內結關聯兩個表中的行不同的是，外聯結還包括沒有關聯行的行，在使用OUTER JOIN語法時，必須使用RIGHT或LEFT關鍵字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右邊的表，而LEFT指出的是OUTER JOIN左邊的表），

使用帶聚集函式的聯結

聚合函式一般用來對表中資料進行匯總統計，或者對分組資料進行分組統計，

這些函式也可以與聯結一起使用，

SELECT Customers.cust_id,
    COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
 ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

第14課組合查詢

本課講述如何利用UNION運算子將多條SELECT陳述句組合成一個結果集，

組合查詢

SQL允許執行多個查詢（多條SELECT陳述句），并將結果作為一個查詢結果集回傳，這些組合查詢通常稱為并（union）或復合查詢（compound query），
主要有兩種情況需要使用組合查詢：

在一個查詢中從不同的表回傳結構資料；
對一個表執行多個查詢，按一個查詢回傳資料，

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

上面的列子，也可以使用下面的sql實作，

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
 OR cust_name = 'Fun4All';

在這個簡單的例子中，使用UNION可能比使用WHERE子句更為復雜，但對于較復雜的過濾條件，或者從多個表（而不是一個表）中檢索資料的情形，使用UNION可能會使處理更簡單，

提示：UNION的限制
使用UNION組合SELECT陳述句的數目，SQL沒有標準限制，但是，最好是參考一下具體的DBMS檔案，了解它是否對UNION能組合的最大陳述句數目有限制，

理論上講，從性能上看使用多條WHERE子句條件還是UNION應該沒有實際的差別，不過我說的是理論上，實踐中多數查詢優化程式并不能達到理想狀態，所以最好測驗一下這兩種方法，看哪種作業得更好，

進行組合時需要注意幾條規則，

UNION必須由兩潭訓兩條以上的SELECT陳述句組成，陳述句之間用關鍵字UNION分隔（因此，如果組合四條SELECT陳述句，將要使用三個UNION關鍵字），
UNION中的每個查詢必須包含相同的列、運算式或聚集函式（不過，各個列不需要以相同的次序列出），
列資料型別必須兼容：型別不必完全相同，但必須是DBMS可以隱含轉換的型別（例如，不同的數值型別或不同的日期型別），

如果查詢出來的資料完全相同，union 會默認只回傳其中一條，如果想讓資料全部回傳，可以使用 union all

提示：UNION與WHERE
這一課一開始我們說過，UNION幾乎總是完成與多個WHERE條件相同的作業，UNION ALL為UNION的一種形式，它完成WHERE子句完成不了的作業，如果確實需要每個條件的匹配行全部出現（包括重復行），就必須使用UNION ALL，而不是WHERE，

在用UNION組合查詢時，只能使用一條ORDER BY子句，它必須位于最后一條SELECT陳述句之后，但是它是對整個回傳結果集的排序，

第15課插入資料

顧名思義，INSERT用來將行插入（或添加）到資料庫表，插入有幾種方式：

插入完整的行；
插入行的一部分；
插入某些查詢的結果，

INSERT INTO Customers(cust_id,
            cust_name,cust_address,
            cust_city,
            cust_state,
            cust_zip,
            cust_country,
            cust_contact,
            cust_email)
VALUES('[1000000006](http://tel:1000000006/)',
'Toy Land',
    '123 Any Street',
    'New York',
    'NY',
    '1111',
    'USA',
    NULL,
    NULL);

VALUES中的第一個值對應于第一個指定列名，第二個值對應于第二個列名，如此等等，
因為提供了列名，VALUES必須以其指定的次序匹配指定的列名，不一定按各列出現在表中的實際次序，其優點是，即使表的結構改變，這條INSERT陳述句仍然能正確作業，

注意：省略列
如果表的定義允許，則可以在INSERT操作中省略某些列，省略的列必須滿足以下某個條件，

該列定義為允許NULL值（無值或空值），
在表定義中給出默認值，這表示如果不給出值，將使用默認值，

插入檢索出的資料

INSERT INTO Customers(cust_id,
            cust_contact,
            cust_email,
            cust_name,
            cust_address,
            cust_city,
            cust_state,
            cust_zip,
            cust_country)
SELECT cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
FROM CustNew;

從一個表復制到另一個表

要將一個表的內容復制到一個全新的表（運行中創建的表），可以使用SELECT INTO陳述句，

SELECT ＊
INTO CustCopy
FROM Customers;

MariaDB、MySQL、Oracle、PostgreSQL和SQLite使用的語法稍有不同：

CREATE TABLE CustCopy AS
SELECT ＊ FROM Customers;

第16課更新和洗掉資料

這一課介紹如何利用UPDATE和DELETE陳述句進一步操作表資料，

注意：不要省略WHERE子句
在使用UPDATE時一定要細心，因為稍不注意，就會更新表中的所有行，

UPDATE Customers
SET cust_email = '[[email protected]](http://mailto:[email protected]/)'
WHERE cust_id = '[1000000005](http://tel:1000000005/)';

在更新多個列時，只需要使用一條SET命令，每個“列=值”對之間用逗號分隔（最后一列之后不用逗號），

資料的洗掉更簡單，

注意：不要省略WHERE子句
在使用DELETE時一定要細心，因為稍不注意，就會錯誤地洗掉表中所有行，
提示：更快的洗掉
如果想從表中洗掉所有行，不要使用DELETE，可使用TRUNCATE TABLE陳述句，它完成相同的作業，而速度更快（因為不記錄資料的變動），先洗掉表再建立表結構

第17課創建和操縱表

創建表

一般有兩種創建表的方法：

多數DBMS都具有互動式創建和管理資料庫表的工具；
表也可以直接用SQL陳述句操縱，

CREATE TABLE Products
(
  prod_id    CHAR(10)      NOT NULL,
  vend_id    CHAR(10)NOT NULL,
  prod_name   CHAR(254)     NOT NULL,
  prod_price   DECIMAL(8,2)    NOT NULL,
  prod_desc   VARCHAR(1000)   NULL
);

更新表

使用alter table關鍵字

洗掉表

drop table xxx

重命名表

每個資料庫不太一樣

第18課使用視圖

視圖

視圖是虛擬的表，與包含資料的表不一樣，視圖只包含使用時動態檢索資料的查詢，

為什么使用視圖

重用SQL陳述句，
簡化復雜的SQL操作，在撰寫查詢后，可以方便地重用它而不必知道其基本查詢細節，
使用表的一部分而不是整個表，
保護資料，可以授予用戶訪問表的特定部分的權限，而不是整個表的訪問權限，
更改資料格式和表示，視圖可回傳與底層表的表示和格式不同的資料，

創建視圖之后，可以用與表基本相同的方式使用它們，可以對視圖執行SELECT操作，過濾和排序資料，將視圖聯結到其他視圖或表，甚至添加和更新資料（添加和更新資料存在某些限制），

下面是關于視圖創建和使用的一些最常見的規則和限制，

與表一樣，視圖必須唯一命名（不能給視圖取與別的視圖或表相同的名字），
對于可以創建的視圖數目沒有限制，
創建視圖，必須具有足夠的訪問權限，這些權限通常由資料庫管理人員授予，
視圖可以嵌套，即可以利用從其他視圖中檢索資料的查詢來構造視圖，所允許的嵌套層數在不同的DBMS中有所不同（嵌套視圖可能會嚴重降低查詢的性能，因此在產品環境中使用之前，應該對其進行全面測驗），
許多DBMS禁止在視圖查詢中使用ORDER BY子句，
有些DBMS要求對回傳的所有列進行命名，如果列是計算欄位，則需要使用別名（關于列別名的更多資訊，請參閱第7課），
視圖不能索引，也不能有關聯的觸發器或默認值，
有些DBMS把視圖作為只讀的查詢，這表示可以從視圖檢索資料，但不能將資料寫回呼層表，詳情請參閱具體的DBMS檔案，
有些DBMS允許創建這樣的視圖，它不能進行導致行不再屬于視圖的插入或更新，例如有一個視圖，只檢索帶有電子郵件地址的顧客，如果更新某個顧客，洗掉他的電子郵件地址，將使該顧客不再屬于視圖，這是默認行為，而且是允許的，但有的DBMS可能會防止這種情況發生，

建議:能不用，就不用視圖

創建視圖

視圖用CREATE VIEW陳述句來創建，與CREATE TABLE一樣，CREATE VIEW 只能用于創建不存在的視圖，

說明：視圖重命名
洗掉視圖，可以使用DROP陳述句，其語法為DROP VIEW viewname;，覆寫（或更新）視圖，必須先洗掉它，然后再重新創建，

利用視圖簡化復雜的聯結

一個最常見的視圖應用是隱藏復雜的SQL，這通常涉及聯結，

CREATE VIEW ProductCustomers ASSELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND OrderItems.order_num = Orders.order_num;

視圖極大地簡化了復雜SQL陳述句的使用，利用視圖，可一次性撰寫基礎的SQL，然后根據需要多次使用，

用視圖重新格式化檢索出的資料
視圖的另一常見用途是重新格式化檢索出的資料，

用視圖過濾不想要的資料

使用視圖與計算欄位

第19課使用存盤程序

什么是存盤程序

一潭訓者多條sql陳述句的集合，可以將其看成是一個sql函式，

為什么要使用存盤程序

通過把處理封裝在一個易用的單元中，可以簡化復雜的操作（如前面例子所述），
由于不要求反復建立一系列處理步驟，因而保證了資料的一致性，如果所有開發人員和應用程式都使用同一存盤程序，則所使用的代碼都是相同的，
這一點的延伸就是防止錯誤，需要執行的步驟越多，出錯的可能性就越大，防止錯誤保證了資料的一致性，
簡化對變動的管理，如果表名、列名或業務邏輯（或別的內容）有變化，那么只需要更改存盤程序的代碼，使用它的人員甚至不需要知道這些變化，這一點的延伸就是安全性，通過存盤程序限制對基礎資料的訪問，減少了資料訛誤（無意識的或別的原因所導致的資料訛誤）的機會，
因為存盤程序通常以編譯過的形式存盤，所以DBMS處理命令所需的作業量少，提高了性能，
存在一些只能用在單個請求中的SQL元素和特性，存盤程序可以使用它們來撰寫功能更強更靈活的代碼，換句話說，使用存盤程序有三個主要的好處，即簡單、安全、高性能，顯然，它們都很重要，

不過，在將SQL代碼轉換為存盤程序前，也必須知道它的一些缺陷，

不同DBMS中的存盤程序語法有所不同，事實上，撰寫真正的可移植存盤程序幾乎是不可能的，不過，存盤程序的自我呼叫（名字以及資料如何傳遞）可以相對保持可移植，因此，如果需要移植到別的DBMS，至少客戶端應用代碼不需要變動，
一般來說，撰寫存盤程序比撰寫基本SQL陳述句復雜，需要更高的技能，更豐富的經驗，因此，許多資料庫管理員把限制存盤程序的創建作為安全措施（主要受上一條缺陷的影響），
盡管有這些缺陷，存盤程序還是非常有用的，并且應該使用，事實上，多數DBMS都帶有用于管理資料庫和表的各種存盤程序，更多資訊請參閱具體的DBMS檔案，

存盤程序執行

參考具體的資料庫

存盤程序創建

參考具體的資料庫

事務管理

提示：可以回退哪些陳述句？
事務處理用來管理INSERT、UPDATE和DELETE陳述句，不能回退SELECT陳述句（回退SELECT陳述句也沒有必要），也不能回退CREATE或DROP操作，事務處理中可以使用這些陳述句，但進行回退時，這些操作也不撤銷，

每個資料庫的事務實作可能不太一樣，針對具體的資料庫學習，效果比較好，

第21課使用游標

游標（cursor）是一個存盤在DBMS服務器上的資料庫查詢，它不是一條SELECT陳述句，而是被該陳述句檢索出來的結果集，在存盤了游標之后，應用程式可以根據要滾動或瀏覽其中的資料，

第22課高級sql特性

約束

主鍵約束
外鍵約束
唯一約束
檢查約束

索引

索參考來排序資料以加快搜索和排序操作的速度，

在開始創建索引前，應該記住以下內容，

索引改善檢索操作的性能，但降低了資料插入、修改和洗掉的性能，在執行這些操作時，DBMS必須動態地更新索引，
索引資料可能要占用大量的存盤空間，
并非所有資料都適合做索引，取值不多的資料（如州）不如具有更多可能值的資料（如卸訓名），能通過索引得到那么多的好處，
索參考于資料過濾和資料排序，如果你經常以某種特定的順序排序資料，則該資料可能適合做索引，
可以在索引中定義多個列（例如，州加上城市），這樣的索引僅在以州加城市的順序排序時有用，如果想按城市排序，則這種索引沒有用處，
沒有嚴格的規則要求什么應該索引，何時索引，大多數DBMS提供了可用來確定索引效率的實用程式，應該經常使用這些實用程式，

觸發器

附件

附件A

https://forta.com/books/0672336073

從上面的地址獲取建表陳述句和資料，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/192875.html

標籤：其他

上一篇：《SQL 必知必會》讀書筆記

下一篇：一個MySQL行轉列的動態存盤程序

《SQL 必知必會》讀書筆記

第1課 了解 SQL

第2課 檢索資料（select）