注：這一系列的文章是《SQL必知必會》第四版的讀書筆記藻丢。

7.創(chuàng)建計算字段

什么是計算字段，怎么創(chuàng)建計算字段摄乒，以及如何從應(yīng)用程序中使用別名引用它們悠反。

7.1 計算字段

字段：基本上與列的意思相同，經(jīng)斥捎樱互換使用斋否。術(shù)語字段通常與計算字段一起使用。

計算字段是運行時在SELECT語句內(nèi)創(chuàng)建的拭荤，并不實際存在于數(shù)據(jù)庫表中茵臭。

只有數(shù)據(jù)庫直到SELECT語句中哪些列時實際列，哪些列時計算字段舅世。從客戶端來看旦委，實際列和計算字段返回的方式相同。

7.2 拼接字段

拼接：將值聯(lián)結(jié)到一起構(gòu)成單個值雏亚。

輸入：

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

FROM Vendors

ORDER BY vend_name;

上面輸出的列沒有名字缨硝，不能用于客戶端應(yīng)用中。為了解決這個問題评凝，SQL支持列別名追葡。

別名：一個字段或值得替換名。別名使用AS關(guān)鍵字賦予奕短。

輸入：

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

AS vend_title

FROM Vendors

ORDER BY vend_name;

7.3 執(zhí)行算術(shù)計算

計算字段另一常見用途是對檢索出的數(shù)據(jù)進(jìn)行算術(shù)計算宜肉。

輸入：

SELECT prod_id,

quantity,

item_price,

quantity*item_price AS expanded_price

FROM OrderItems

WHERE order_num=20008;

8.使用數(shù)據(jù)處理函數(shù)

介紹什么是函數(shù)，DBMS支持何種函數(shù)翎碑，如何使用這些函數(shù)谬返，以及SQL函數(shù)的使用可能帶來的問題。

8.1 函數(shù)

每一個DBMS都有特定的函數(shù)日杈，即使功能一樣的函數(shù)遣铝，但在不同的DBMS中名稱和語法可能極其不同。

SQL函數(shù)不是可移植的莉擒。

8.2 使用函數(shù)

8.2.1 文本處理函數(shù)

UPPER()函數(shù)的使用酿炸。

輸入：

SELECT vend_name,UPPER(vend_name) AS vend_name_upcase

FROM Vendors

ORDER BY vend_name;

SOUNDEX()函數(shù)，將任何文本串轉(zhuǎn)換為其語音表示的字母數(shù)字模式的算法涨冀。對字符串進(jìn)行發(fā)音比較而不是字母比較填硕。

輸入：

SELECT cust_name,cust_contact

FROM Customers

WHERE SOUNDEX(cust_contact)=SOUNDEX('Michael Green');

8.2.2 日期和時間處理函數(shù)

以MySQL中的YEAR()函數(shù)為例。

輸入：

SELECT order_num

FROM Orders

WHERE YEAR(order_date)=2012;

8.2.3 數(shù)值處理函數(shù)

在主要的DBMS中鹿鳖，數(shù)值處理函數(shù)時最一致扁眯、最統(tǒng)一的函數(shù)。

常用的數(shù)值處理函數(shù)：

ABS()翅帜，COS()姻檀，EXP()，PI()涝滴，SIN()绣版，SQRT()，TAN()

9.匯總數(shù)據(jù)

介紹什么是SQL的聚集函數(shù)歼疮，利用它們匯總表的數(shù)據(jù)僵娃。

9.1 聚集函數(shù)

聚集函數(shù)：對某些行運行的函數(shù)，計算并返回一個值腋妙。

SQL聚集函數(shù)：AVG()默怨，COUNT()，MAX()骤素，MIN()匙睹，SUM()

9.1.1 AVG()函數(shù)

AVG()函數(shù)通過對表中行數(shù)計數(shù)并計算其列值之和，求得該列的平均值济竹。

輸入：

SELECT AVG(prod_price) AS avg_price

FROM Products;

輸入：

SELECT AVG(prod_price) AS avg_price

FROM Products

WHERE vend_id='DLL01';

注意：AVG()只用于單個列痕檬。AVG()函數(shù)忽略值為NULL的行。

9.1.2 COUNT()函數(shù)

COUNT()函數(shù)進(jìn)行計數(shù)送浊，有兩種使用方式：

（1）COUNT(*)：對表中行的數(shù)目進(jìn)行計數(shù)梦谜，不管包含的是NULL值還是非NULL值。

（2）COUNT(column)：對特定列中具有值得行進(jìn)行計數(shù)，忽略NULL值唁桩。

輸入：

SELECT COUNT(*) AS num_cust

FROM Customers;

輸入：

SELECT COUNT(cust_email) AS num_cust

FROM Customers;

9.1.3 MAX()函數(shù)

MAX()函數(shù)要求指定列名闭树，并且忽略列值為NULL的行。

輸入：

SELECT MAX(prod_price) AS max_price

FROM Products;

9.1.4 MIN()函數(shù)

與MAX()函數(shù)類似荒澡。

9.1.5 SUM()函數(shù)

SUM()函數(shù)用來返回指定列值的和报辱。忽略值為NULL的行。

輸入：

SELECT SUM(quantity) AS item_ordered

FROM OrderItems

WHERE order_num=20005;

SUM()函數(shù)也能用來合計計算值单山。

輸入：

SELECT SUM(item_price*quantity) AS total_price

FROM OrderItems

WHERE order_num=20005;

9.2 聚集不同值

以上五個聚集函數(shù)可以如下使用：

（1）對所有行執(zhí)行計算碍现，指定ALL參數(shù)或不指定參數(shù)（因為默認(rèn)是ALL參數(shù)）。

（2）只包含不同的值米奸，指定DISTINCT參數(shù)昼接。

輸入：

SELECT AVG(DISTINCT prod_price) AS avg_price

FROM Products

WHERE vend_id='DLL01';

DISTINCT只能用于COUNT()，不能用于COUNT(*)悴晰，必須使用列名慢睡，不能用于計算或表達(dá)式。

9.3 組合聚集函數(shù)

聚集函數(shù)能夠組合起來使用膨疏。

輸入：

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.分組數(shù)據(jù)

介紹如何分組數(shù)據(jù)一睁，以便匯總表內(nèi)容的子集。涉及的新SELECT子句：GROUP BY子句佃却，HAVING子句者吁。

10.1 數(shù)據(jù)分組

使用分組可以將數(shù)據(jù)分為多個邏輯組，對每個組進(jìn)行聚集計算饲帅。

10.2 創(chuàng)建分組

分組是使用GROUP BY子句建立的复凳。

輸入：

SELECT vend_id,COUNT(*) AS num_prods

FROM Products

GROUP BY vend_id;

使用GROUP BY子句需要知道的一些重要規(guī)定：

（1）GROUP BY子句可以包含任意數(shù)目的列，因而可以對分組進(jìn)行嵌套灶泵，更加細(xì)致的進(jìn)行數(shù)據(jù)分組育八。

（2）如果GROUP BY子句嵌套了分組，數(shù)據(jù)將在最后指定的分組上進(jìn)行匯總赦邻。即髓棋，在建立分組時，指定的所有列都一起計算惶洲。

（3）GROUP BY子句中列出的每一列都必須是檢索列或有效的表達(dá)式（不能是聚集函數(shù)）按声。如果在SELECT子句中使用表達(dá)式，則必須在GROUP BY子句中指定相同的表達(dá)式恬吕。不能使用別名签则。

（4）大多數(shù)SQL實現(xiàn)不允許GROUP BY子句列帶有長度可變的數(shù)據(jù)類型。

（5）除聚集計算語句外铐料，SELECT語句中的每一列都必須在GROUP BY子句中給出渐裂。

（6）如果分組列中包含具有NULL值的行豺旬，則NULL將作為一個分組返回。如果列中有多個行是NULL值柒凉，它們將分為一組族阅。

（7）GROUP BY子句必須出現(xiàn)在WHERE子句之后，ORDER BY子句之前扛拨。

10.3 過濾分組

WHERE子句：過濾指定的是行耘分。WHERE子句沒有分組的概念举塔。

HAVING子句：過濾的是分組绑警。

輸入：

SELECT cust_id,COUNT(*) AS orders

FROM Orders

GROUP BY cust_id

HAVING COUNT(*)>=2;

另一種理解方法：WHERE子句在數(shù)據(jù)分組前過濾，HAVING子句在數(shù)據(jù)分組后進(jìn)行過濾央渣。

同時使用WHERE子句和HAVING子句：

輸入：

SELECT vend_id,COUNT(*) AS num_prods

FROM Products

WHERE prod_price>=4

GROUP BY vend_id

HAVING COUNT(*)>=2;

10.4 分組和排序

GROUP BY：對行分組计盒，但輸出可能不是分組的順序；只可能使用選擇列或表達(dá)式列芽丹，而且必須使用每個選擇列表達(dá)式北启；如果與聚集函數(shù)一起使用列（或表達(dá)式），則必須使用拔第。

ORDER BY：對產(chǎn)生的輸出排序咕村；任意列都可以使用；不一定需要蚊俺。

輸入：

SELECT order_num,COUNT(*) AS items

FROM OrderItems

GROUP BY order_num

HAVING COUNT(*)>=3

ORDER BY items,order_num;

10.5 SELECT子句的順序

SELECT：必須使用

FROM：僅在從表選擇數(shù)據(jù)時使用

WHERE：非必須

GROUP BY：僅在按組計算聚集時使用

HAVING：非必須

ORDER BY：非必須

11.使用子查詢

介紹什么是子查詢懈涛，使用子查詢。子查詢常用于WHERE子句的IN操作符中泳猬，以及用來填充計算列批钠。

11.1 子查詢

子查詢：嵌套在其他查詢中的查詢。

MySQL對子查詢是從4.1版本開始的得封。

11.2 利用子查詢進(jìn)行過濾

子查詢總是從內(nèi)向外處理埋心。對能嵌套的子查詢的數(shù)目沒有限制，但在實際使用時忙上，出于性能的限制拷呆，不能嵌套太多的子查詢。

作為子查詢的SELECT語句只能查詢單個列疫粥。

列出訂購物品RGAN01的所有顧客茬斧。

輸入：

SELECT cust_name,cust_contact

FROM Customers

WHERE cust_id IN (SELECT cust_id

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?FROM Orders

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?WHERE order_num IN (SELECT order_num

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? FROM OrderItems

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? WHERE prod_id='RGAN01'));

11.3 作為計算字段使用子查詢

使用子查詢的另一方法是創(chuàng)建計算字段。

需要顯示Customers表中每個顧客的訂單總數(shù)手形。

輸入：

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.聯(lián)結(jié)表

介紹什么是聯(lián)結(jié)，為什么使用聯(lián)結(jié)库糠，如何編寫使用聯(lián)結(jié)的SELECT語句伙狐。

12.1 聯(lián)結(jié)

SQL最強大的功能之一就是能在數(shù)據(jù)查詢的執(zhí)行中聯(lián)結(jié)表涮毫。

12.1.1 關(guān)系表

關(guān)系表的設(shè)計就是將信息分解成多個表，一類數(shù)據(jù)一個表贷屎。各表通過某些共同的值互相關(guān)聯(lián)罢防，這樣可以有效的存儲，方便的管理唉侄。關(guān)系數(shù)據(jù)庫的可伸縮性遠(yuǎn)比非關(guān)系數(shù)據(jù)庫要好咒吐。

12.1.2 為什么使用聯(lián)結(jié)

聯(lián)結(jié)是一種機制，用來在一條SELECT語句中關(guān)聯(lián)表属划。使用特殊的語法恬叹，可以聯(lián)結(jié)多個表返回一組輸出。

12.2 創(chuàng)建聯(lián)結(jié)

創(chuàng)建聯(lián)結(jié)非常簡單同眯，指定要聯(lián)結(jié)的所有表和關(guān)聯(lián)它們的方式即可绽昼。

用WHERE子句聯(lián)結(jié)。

輸入：

SELECT vend_name,prod_name,prod_price

FROM Vendors,Products

WHERE Vendors.vend_id=Products.vend_id;

12.2.1 WHERE子句的重要性

聯(lián)結(jié)兩個表時须蜗，實際要做的是將第一個表的每一行與第二個表的每一行配對硅确。WHERE子句作為過濾條件，只包含滿足給定條件的行明肮。沒有WHERE子句的話菱农，兩個表聯(lián)結(jié)的結(jié)果將是一個笛卡爾積。

12.2.2 內(nèi)聯(lián)結(jié)

等值聯(lián)結(jié)：基于兩個表之間的相等測試柿估，也稱為內(nèi)聯(lián)結(jié)循未。

關(guān)鍵字：INNER JOIN，ON

輸入：

SELECT vend_name,prod_name,prod_price

FROM Vendors INNER JOIN Products

?ON Vendors.vend_id=Products.vend_id;

12.2.3 聯(lián)結(jié)多個表

顯示訂單20007中的物品官份。

輸入：

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;

13.創(chuàng)建高級聯(lián)結(jié)

講解另一些聯(lián)結(jié)只厘，介紹如何使用表別名，如何對被聯(lián)結(jié)的表使用聚集函數(shù)舅巷。

13.1 使用表別名

使用表別名的兩個理由：

（1）縮短SQL語句

（2）允許在一條SELECT語句中多次使用相同的表

輸入：

SELECT cust_name,cust_contact

FROM Customers AS C,Orders AS O,OrderItems AS OI

WHERE C.cust_id=O.cust_id

?AND OI.order_num=O.order_num

?AND prod_id='RGAN01';

13.2 使用不同類型的聯(lián)結(jié)

其他的三種聯(lián)結(jié)：自聯(lián)結(jié)羔味，自然聯(lián)結(jié)，外聯(lián)結(jié)钠右。

13.2.1 自聯(lián)結(jié)

要給與Jim Jones同一公司的所有顧客發(fā)送一封郵件赋元。

輸入：

（1）使用子查詢

SELECT cust_id,cust_name,cust_contact

FROM Customers

WHERE cust_name=(SELECT cust_name

FROM Customers

WHERE cust_contact='Jim Jones');

（2）使用自聯(lián)結(jié)

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';

用自聯(lián)結(jié)而不用子查詢：許多DBMS處理聯(lián)結(jié)遠(yuǎn)比處理子查詢快的多，應(yīng)該試一下兩種不同的方法飒房，以確定哪一種性能更好搁凸。

13.2.2 自然聯(lián)結(jié)

自然聯(lián)結(jié)排除多次出現(xiàn)，使每一列只返回一次狠毯。系統(tǒng)不完成這項工作护糖，由自己完成。自然聯(lián)結(jié)要求只能選擇那些唯一的列嚼松，一般通過對一個表使用通配符（SELECT *）嫡良，而對其他表的列使用明確的子集來完成锰扶。

輸入：

SELECT C.*,O.order_num,O.order_date,OI.prod_id,OI.quantity,OI.item_price

FROM Customers AS C,Orders AS O,OrderItems AS OI

WHERE C.cust_id=O.cust_id

?AND OI.order_num=O.order_num

?AND prod_id='RGAN01';

13.2.3 外聯(lián)結(jié)

外聯(lián)結(jié)：包含了那些在相關(guān)表中沒有關(guān)聯(lián)的行。

關(guān)鍵字：OUTER JOIN寝受，ON坷牛。使用RIGHT或LEFT關(guān)鍵字指定包括其所有行的表。

輸入：

SELECT Customers.cust_id,Orders.order_num

FROM Customers LEFT OUTER JOIN Orders

ON Customers.cust_id=Orders.cust_id;

注意：MySQL不支持FULL OUTER JOIN 的語法很澄。

13.3 使用帶聚集函數(shù)的聯(lián)結(jié)

檢索所有顧客及每個顧客所下的訂單數(shù)京闰。

輸入：

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;

13.4 使用聯(lián)結(jié)和聯(lián)結(jié)條件

（1）注意所使用的聯(lián)結(jié)類型。

（2）關(guān)于確切的聯(lián)結(jié)語法甩苛，應(yīng)當(dāng)查看具體的文檔蹂楣。

（3）保證使用正確的聯(lián)結(jié)條件。

（4）應(yīng)該總是提供聯(lián)結(jié)條件浪藻。

（5）在一個聯(lián)結(jié)中可以包含多個表捐迫，甚至對每個聯(lián)結(jié)都可以采用不同的聯(lián)結(jié)類型乾翔。在一起測試它們之前爱葵，先分別測試每個聯(lián)結(jié)。