于是又到了梧桐絮飄滿校園的四月，又到了梧桐色相簿的季節(jié)，我們迎來了實(shí)習(xí)生招聘。

“范老濕從不寫代碼”，“范老濕從不會(huì)SQL”，說起來都是淚。去年就因?yàn)镾QL掛了面了一下午的X團(tuán)，然而今年仍然不會(huì)。近來又因?yàn)镃能力低下掛掉了N多OJ，滿屏AC的日子一去不復(fù)返。所幸現(xiàn)在拯救也許還不晚，那讓我們來愉快的治療它吧～

參考書籍是Ben Forta的《SQL必知必會(huì)》

一、SQL相關(guān)概念

• 數(shù)據(jù)庫(kù)(database)：保存有組織的數(shù)據(jù)的容器（通常是一個(gè)文件或一組文件）；
• 表(table)：某種特定類型數(shù)據(jù)的結(jié)構(gòu)化清單；
  每一個(gè)數(shù)據(jù)庫(kù)中的表都有一個(gè)唯一的表名
• 模式：關(guān)于數(shù)據(jù)庫(kù)和表的布局及特性的信息；
• 列(column)：表中的一個(gè)字段；
  所有表都是由一個(gè)或多個(gè)列組成的
  每一個(gè)表列都有一個(gè)數(shù)據(jù)類型
• 行(row)：表中的一個(gè)紀(jì)錄；
  有時(shí)稱紀(jì)錄(record)，行才是正確的術(shù)語
• 主鍵(primary key)：一（組）列，其值存在且唯一，故能標(biāo)識(shí)表中的每一行；
  主鍵列的值一般不允許修改與更新，且不能重用（即被刪除行的值刪除后不能用于以后的值）

范老濕總結(jié)：每一個(gè)database里面可能有很多table，既然是table那一定有column和row，起唯一標(biāo)識(shí)作用的column被稱為primary key。Primary key中的值一定符合以下性質(zhì)：存在性、唯一性、不可復(fù)用性。

以及：

• 關(guān)鍵字：即SQL中的保留字，不能用做表或列的名字。
  SQL支持以下三種注釋：

#, --, /* */

二、SELECT

2.1 SELECT的基本使用

SELECT, like its name, 就是從表中選擇數(shù)據(jù)嘛。那怎樣才是SELECT的正確姿勢(shì)呢？

SELECT prod_name FROM Products;

相信機(jī)智的你一眼就能看出，這是從Products這個(gè)表中選擇了prod_name這個(gè)column。

從上面那條SQL語句中，可以（或者不可以）看出以下幾點(diǎn)：

• SQL語句中，結(jié)束要加分號(hào)（半角的不用說了吧）；
• SQL語句不區(qū)分大小寫（看當(dāng)?shù)匾?guī)范了，這個(gè)例子就是關(guān)鍵字大寫，表名首字母大寫，其它小寫）；
• SQL語句中，不區(qū)分空格與回車（所以上面那四個(gè)詞語寫每行一個(gè)也沒關(guān)系，你開心就好）；
• SQL語句的輸出，并不一定遵循某些順序。

那這樣呢：

SELECT prod_id, prod_name, prod_price FROM Products;

當(dāng)然就是從Products這個(gè)表中同時(shí)選擇了prod_id, prod_name, prod_price這三列。
在語句中，列名之間以逗號(hào)分隔。

這樣呢：

SELECT * FROM Products;

這樣就是從Products里面選擇了所有的column。

有時(shí)候，我們需要去重：

SELECT DISTINCT vend_id FROM Products;

這樣，輸出的vend_id就實(shí)現(xiàn)了去重
需要注意的是，DISTINCT作用于所有的列，而并非跟在后面的那一列，比如:

SELECT DISTINCT vend_id, prod_price FROM Products;

我們也可以對(duì)輸出的內(nèi)容作出限制，比如我們想取prod_name中的TOP 5：

SELECT prod_name FROM Products LIMIT 5;

（不同SQL實(shí)現(xiàn)的本條語句，語法可能不同，本文以MySQL為準(zhǔn)，下同）
如果想看后面的呢？

SELECT prod_name FROM Products LIMIT 5 OFFSET 5;

這樣就返回了Products中prod_name這列從第5行起的5行數(shù)據(jù)。

2.2 排序：ORDER BY

可是，就像我們之前說的，SQL的輸出默認(rèn)是沒有順序的。如果你問我啊，我可以說一句“無可奉告”。但是看你們這么熱情啊，一句話不說也不好。所以說啊，我們來學(xué)習(xí)一個(gè)，如何才能讓SQL的輸出有順序：

SELECT prod_name FROM Products ORDER BY prod_name;

這樣的話，就是按照prod_name的字母順序升序排列了。
需要注意的是，ORDER BY子句應(yīng)被保證為SELECT語句中的最后一條子句。

不過這樣只能按一個(gè)列排序啊Orz如果我們想按多個(gè)列排序呢？

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

可是這樣寫語句是不是有點(diǎn)太長(zhǎng)了呢？其實(shí)這樣也可以的：

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

就可以達(dá)到和上面語句一樣的效果，其實(shí)就是從那三個(gè)查看的列里面選擇了第二個(gè)和第三個(gè)吧。

上面的都是升序，那如果我們想降序排列呢？

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

要降序排列的列要后面加上DESC，反過來說，就是DESC只作用于它前面的那一列：

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

這樣，prod_price列就是降序的，prod_name列則呈升序排列。

2.3 數(shù)據(jù)過濾：WHERE

但是這樣的話，似乎還是不夠方便啊。如果我們想查看某一個(gè)列的值符合特定規(guī)律的數(shù)據(jù)呢？這樣我們就用到WHERE：

SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49;

這樣就輸出了所有 prod_price值為3.49的prod_name和prod_price

WHERE字句都資瓷哪些操作符呢？

=, <>, !=, <, <=, !<, >, >=, !>, BETWEEN, IS NULL

SQL有個(gè)特點(diǎn)，就是非常直觀，這些符號(hào)的含義也就不用做過多解釋了。

SQL還資瓷很多花式的過濾方法！是的！就算過濾條件更復(fù)雜一些也沒關(guān)系！比如像這樣：

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

既然資瓷了AND，你問我資不資瓷OR？當(dāng)然資瓷！我就這么明確告訴你：

SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND vend_id = 'BRS01';

既然說到AND和OR，那么不可避免的就是出現(xiàn)邏輯運(yùn)算順序的問題了。SQL優(yōu)先處理AND運(yùn)算符。在需要指定順序時(shí)，也可以使用括號(hào)：

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

以及ORDED BY子句，一般是放在WHERE后面。

其實(shí)OR操作也可以這樣實(shí)現(xiàn)

SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01', 'BRS01') ORDER BY prod_name;

而且，IN的操作一般比OR更快哦～而且IN還可以用來實(shí)現(xiàn)更多更靈活的操作！

那么既然有了AND和OR，自然而然就有了NOT

SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;

2.4 更靈活的過濾：LIKE

這樣我們就學(xué)會(huì)了過濾，但是似乎好像資瓷的過濾方式還太少啊Orz連字符串匹配都不資瓷（就你事多（誤

好好好，這就說怎樣才是資瓷更高級(jí)過濾條件的正確姿勢(shì)：

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%';

就是過濾出prod_name里面以Fish開頭或者含有bean bag的數(shù)據(jù)。

也可以這樣：

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'F％y';

過濾出prod_name中以F開頭以y結(jié)尾的數(shù)據(jù)。

這樣存在一個(gè)問題：很多DBMS會(huì)在字符串后自動(dòng)添加空格，以達(dá)到和字符串類型定義時(shí)相等的長(zhǎng)度。

以及LIKE '%'不會(huì)匹配NULL。

當(dāng)然，都說了是“更靈活的”過濾姿勢(shì)，怎么能只有%一種？

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE ‘__ inch teddy bear';

_操作符相較于%更加嚴(yán)格？感覺這么說也不好，算是用法不同吧。%是匹配字符串，而一個(gè)_則是匹配單個(gè)字符，不許多也不許少。

有時(shí)候我們還會(huì)遇到要匹配的字符有多種可能性的情況，除了用OR，還有什么更優(yōu)雅的方案么？

SELECT cust_contact FROM Customers WHERE cust_contact LIKE ‘[JM]%' ORDER BY cust_contact;

這樣就匹配了cust_contact里面以J或M開頭的數(shù)據(jù)。同樣的，[]也只是對(duì)應(yīng)單個(gè)字符。

如果我們想否定，可以用NOT，當(dāng)然也可以用更優(yōu)雅的^

SELECT cust_contact FROM Customers WHERE cust_contact LIKE ‘[^JM]%' ORDER BY cust_contact;

主要的通配符就是%, _和[]。不幸的是通配符一般速度會(huì)比較慢，建議不要的過度使用，使用的時(shí)候也盡量放在搜索模式的后面，以減少檢索時(shí)間。

2.5 計(jì)算字段與格式化輸出

看來僅僅簡(jiǎn)單的把數(shù)據(jù)輸出還不行啊，有時(shí)候我們還需要將數(shù)據(jù)按照一定格式輸出，或者做一些數(shù)值計(jì)算。說白了就是字符串操作和數(shù)值計(jì)算嘛。比如這樣：

SELECT Concat(vend_name, ' (', vend_country, ')') FROM vendors ORDER BY vend_name;

即在vend_country字段兩側(cè)加了括號(hào)。Concat則是起連接作用的函數(shù)。

我們前面說過，有的DBMS（比如MySQL）會(huì)在字符串末尾補(bǔ)全空格。這時(shí)就需要用RTrim函數(shù)解決：

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') FROM vendors ORDER BY vend_name;

除了去除右邊空格的RTrim以外，還有去掉左邊空格的LTrim和左右空格的Trim。

有時(shí)候我們要把新生成的部分起一個(gè)名字，也就是別名：

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

字符串的計(jì)算就是上面那樣，那么如何進(jìn)行數(shù)學(xué)計(jì)算呢？

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;

這樣就計(jì)算了數(shù)量和單價(jià)的乘積，并將其別名設(shè)為expanded_price。

2.6 函數(shù)運(yùn)算

是的，既然資瓷一般的字符串運(yùn)算，那么更應(yīng)該資瓷一下更復(fù)雜的函數(shù)運(yùn)算，上一節(jié)我們提到了Trim等函數(shù)這一屆里面我們簡(jiǎn)要的把函數(shù)分為文本處理函數(shù)、日子時(shí)間處理函數(shù)和數(shù)值處理函數(shù)。

首先說文本處理函數(shù)

其中SOUNDEX的含義需要解釋一下，簡(jiǎn)單來說就是讀音相似的，比如：

SELECT cust_name, cust_contact FROM Customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green')

cust_contact里面如果有'Michelle Green'這樣讀音相似的，就會(huì)返回。

接下來是日期時(shí)間處理函數(shù)：

例子：

SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

即匹配的日期在2005年9月1日到2005年9月30日。需要注意的是，order_date是一個(gè)datetime類型，對(duì)它進(jìn)行date部分的匹配時(shí)要用Date函數(shù)截取其date部分。

最后是數(shù)值處理函數(shù)：

這個(gè)不用多說了吧

2.7 聚集函數(shù)

上面說的是對(duì)于一些數(shù)值的計(jì)算。有時(shí)候我們做數(shù)據(jù)分析時(shí)需要關(guān)注整個(gè)Dataset中某個(gè)特征的宏觀特點(diǎn)。（說人話：比如平均值、最大值最小值等）

下面分別舉例說明：

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

即得出vend_id為1003的產(chǎn)品的平均價(jià)格。

需要注意的是，Avg函數(shù)將自動(dòng)忽略值為NULL的行。

Count函數(shù)也是類似

SELECT COUNT(cust_email) AS num_cust FROM Customers;

也將返回cust_email不為NULL的行數(shù)。Min,Max,Sum同理。

我們前面提到過用DISTINCT去重，這里也是一樣：

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

得到的是每個(gè)商品的單價(jià)去重之后的平均價(jià)格。

聚集函數(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;

2.8 分組

在前面的例子中，我們知道，如果想得到某一個(gè)vend_id的num_prods數(shù)據(jù)，我們可以這樣：

SELECT COUNT(*) AS num_prods FROM products WHERE vend_id = 1003;

如果我們想同時(shí)查看所有vend_id的num_prods，就需要分組處理：

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

輸出為兩列，一列是排序后的vend_id，一個(gè)是每個(gè)vend_id對(duì)應(yīng)的num_prods數(shù)。

GROUP BY子句中需要注意的是：

• GROUP BY子句可以包含任意數(shù)目的列，因而可以對(duì)分組進(jìn)行嵌套。這種情況下數(shù)據(jù)將在最后指定的分組上進(jìn)行匯總；
• GROUP BY子句中的每一列都必須是檢索列或有效的表達(dá)式；
• 大多數(shù)SQL實(shí)現(xiàn)不允許GROUP BY列帶有長(zhǎng)度可變的數(shù)據(jù)類型；
• 除聚集計(jì)算語句外，SELECT語句中的每一列都必須在GROUP BY子句中給出；
• 如果分組列中包含具有NULL值的行，則NULL將作為一個(gè)單獨(dú)的分組；
• GROUP BY分組必須出現(xiàn)在WHERE子句之后，ORDER BY子句之前。

除了用GROUP BY分組以外，SQL還支持過濾分組。這時(shí)候我們要用到HAVING子句：

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

這樣就過濾出了訂單數(shù)>=2的分組。

• HAVING和WHERE的區(qū)別：可以簡(jiǎn)要的理解為HAVING在數(shù)據(jù)分組前進(jìn)行過濾，WHERE在數(shù)據(jù)分組后進(jìn)行過濾。

有些時(shí)候，HAVING和WHERE也可以同時(shí)使用：

SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2

WHERE子句先是過濾出了prod_price至少為4的行，HAVING子句又過濾出計(jì)數(shù)為2或2以上的分組。

既然分組支持過濾，那么一定支持排序：

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3;

如上過濾出了訂單數(shù)>=3的order_num。

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

加上一個(gè)ORDER BY子句，即按items、order_num從小到大排序。

到現(xiàn)在，我們總結(jié)一下SELECT子句使用的順序：

2.9 子查詢

SQL還允許子查詢，即查詢中嵌套的查詢。

例如如下兩個(gè)SQL查詢：

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

有兩個(gè)表，OrderItems和Orders。OrderItems中保存的是order_num和prod_id信息，而Orders中保存的是cust_id和order_num的信息。如果我們想根據(jù)prod_id查找cust_id的相關(guān)信息，除了使用兩次查找，還可以使用如下方式：

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

除此之外，還可以作為計(jì)算字段使用子查詢。

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