SQL太難？你離完全理解SQL就差這10步（下）
點(diǎn)擊關(guān)注上方“SQL數(shù)據(jù)庫開發(fā)”，
設(shè)為“置頂或星標(biāo)”，第一時(shí)間送達(dá)干貨
很多程序員視 SQL 為洪水猛獸。SQL 是一種為數(shù)不多的聲明性語言，它的運(yùn)行方式完全不同于我們所熟知的命令行語言、面向?qū)ο蟮某绦蛘Z言、甚至是函數(shù)語言（盡管有些人認(rèn)為 SQL 語言也是一種函數(shù)式語言）。
我們每天都在寫 SQL 并且應(yīng)用在開源軟件 jOOQ 中。于是我想把 SQL 之美介紹給那些仍然對(duì)它頭疼不已的朋友，所以本文是為了以下讀者而特地編寫的：
在工作中會(huì)用到 SQL 但是對(duì)它并不完全了解的人。 能夠熟練使用 SQL 但是并不了解其語法邏輯的人。 想要教別人 SQL 的人。
本文著重介紹 SELECT 句式，其他的 DML （Data Manipulation Language 數(shù)據(jù)操縱語言命令）將會(huì)在別的文章中進(jìn)行介紹。
6、 SQL 語句中不同的連接操作
SQL 語句中，表連接的方式從根本上分為五種：
EQUI JOIN SEMI JOIN ANTI JOIN CROSS JOIN DIVISION
EQUI JOIN
這是一種最普通的 JOIN 操作，它包含兩種連接方式：
INNER JOIN（或者是 JOIN ） OUTER JOIN（包括：LEFT 、 RIGHT、 FULL OUTER JOIN）
用例子最容易說明其中區(qū)別：
--?This?table?reference?contains?authors?and?their?books.
--?There?is?one?record?for?each?book?and?its?author.
--?authors?without?books?are?NOT?included
author?JOIN?book?ON?author.id?=?book.author_id
--?This?table?reference?contains?authors?and?their?books
--?There?is?one?record?for?each?book?and?its?author.
--?...?OR?there?is?an?"empty"?record?for?authors?without?books
--?("empty"?meaning?that?all?book?columns?are?NULL)
author?LEFT?OUTER?JOIN?book?ON?author.id?=?book.author_id
這種連接關(guān)系在 SQL 中有兩種表現(xiàn)方式：使用 IN，或者使用 EXISTS。“ SEMI ”在拉丁文中是“半”的意思。這種連接方式是只連接目標(biāo)表的一部分。這是什么意思呢？再想一下上面關(guān)于作者和書名的連接。我們想象一下這樣的情況：我們不需要作者 / 書名這樣的組合，只是需要那些在書名表中的書的作者信息。那我們就能這么寫：
--?Using?IN
FROM?author
WHERE?author.id?IN?(SELECT?book.author_id?FROM?book)
--?Using?EXISTS
FROM?author
WHERE?EXISTS?(SELECT?1?FROM?book?WHERE?book.author_id?=?author.id)
盡管沒有嚴(yán)格的規(guī)定說明你何時(shí)應(yīng)該使用 IN ，何時(shí)應(yīng)該使用 EXISTS ，但是這些事情你還是應(yīng)該知道的：
IN比 EXISTS 的可讀性更好
EXISTS 比IN 的表達(dá)性更好（更適合復(fù)雜的語句）
二者之間性能沒有差異（但對(duì)于某些數(shù)據(jù)庫來說性能差異會(huì)非常大）
因?yàn)槭褂?INNER JOIN 也能得到書名表中書所對(duì)應(yīng)的作者信息，所以很多初學(xué)者機(jī)會(huì)認(rèn)為可以通過 DISTINCT 進(jìn)行去重，然后將 SEMI JOIN 語句寫成這樣：
--?Find?only?those?authors?who?also?have?booksSELECT?DISTINCT?first_name,?last_nameFROM?authorJOIN?book?ON?author.id?=?book.author_id
這是一種很糟糕的寫法，原因如下：
SQL 語句性能低下：因?yàn)槿ブ夭僮鳎?DISTINCT ）需要數(shù)據(jù)庫重復(fù)從硬盤中讀取數(shù)據(jù)到內(nèi)存中。（譯者注：DISTINCT 的確是一種很耗費(fèi)資源的操作，但是每種數(shù)據(jù)庫對(duì)于 DISTINCT 的操作方式可能不同）。
這么寫并非完全正確：盡管也許現(xiàn)在這么寫不會(huì)出現(xiàn)問題，但是隨著 SQL 語句變得越來越復(fù)雜，你想要去重得到正確的結(jié)果就變得十分困難。
ANTI JOIN
這種連接的關(guān)系跟 SEMI JOIN 剛好相反。在 IN 或者 EXISTS 前加一個(gè) NOT 關(guān)鍵字就能使用這種連接。舉個(gè)例子來說，我們列出書名表里沒有書的作者：
--?Using?IN
FROM?author
WHERE?author.id?NOT?IN?(SELECT?book.author_id?FROM?book)
--?Using?EXISTS
FROM?author
WHERE?NOT?EXISTS?(SELECT?1?FROM?book?WHERE?book.author_id?=?author.id)
關(guān)于性能、可讀性、表達(dá)性等特性也完全可以參考 SEMI JOIN。
這篇博文介紹了在使用 NOT IN 時(shí)遇到 NULL 應(yīng)該怎么辦，因?yàn)橛幸稽c(diǎn)背離本篇主題，就不詳細(xì)介紹，有興趣的同學(xué)可以讀一下
CROSS JOIN
這個(gè)連接過程就是兩個(gè)連接的表的乘積：即將第一張表的每一條數(shù)據(jù)分別對(duì)應(yīng)第二張表的每條數(shù)據(jù)。我們之前見過，這就是逗號(hào)在 FROM 語句中的用法。在實(shí)際的應(yīng)用中，很少有地方能用到 CROSS JOIN，但是一旦用上了，你就可以用這樣的 SQL語句表達(dá)：
--?Combine?every?author?with?every?book
author?CROSS?JOIN?book
DIVISION
DIVISION 的確是一個(gè)怪胎。簡(jiǎn)而言之，如果 JOIN 是一個(gè)乘法運(yùn)算，那么 DIVISION 就是 JOIN 的逆過程。DIVISION 的關(guān)系很難用 SQL 表達(dá)出來，介于這是一個(gè)新手指南，解釋 DIVISION 已經(jīng)超出了我們的目的。
我們學(xué)到了什么？
學(xué)到了很多！讓我們?cè)谀X海中再回想一下。SQL 是對(duì)表的引用， JOIN 則是一種引用表的復(fù)雜方式。但是 SQL 語言的表達(dá)方式和實(shí)際我們所需要的邏輯關(guān)系之間是有區(qū)別的，并非所有的邏輯關(guān)系都能找到對(duì)應(yīng)的 JOIN 操作，所以這就要我們?cè)谄綍r(shí)多積累和學(xué)習(xí)關(guān)系邏輯，這樣你就能在以后編寫 SQL 語句中選擇適當(dāng)?shù)?JOIN 操作了。
7、 SQL 中如同變量的派生表
在這之前，我們學(xué)習(xí)到過 SQL 是一種聲明性的語言，并且 SQL 語句中不能包含變量。但是你能寫出類似于變量的語句，這些就叫做派生表：
說白了，所謂的派生表就是在括號(hào)之中的子查詢：
--?A?derived?table
FROM?(SELECT?*?FROM?author)
需要注意的是有些時(shí)候我們可以給派生表定義一個(gè)相關(guān)名（即我們所說的別名）。
--?A?derived?table?with?an?alias
FROM?(SELECT?*?FROM?author)?a
派生表可以有效的避免由于 SQL 邏輯而產(chǎn)生的問題。舉例來說：如果你想重用一個(gè)用 SELECT 和 WHERE 語句查詢出的結(jié)果，這樣寫就可以（以 Oracle 為例）：
--?Get?authors'?first?and?last?names,?and?their?age?in?days
SELECT?first_name,?last_name,?age
FROM?(
??SELECT?first_name,?last_name,?current_date?-?date_of_birth?age
??FROM?author
)
--?If?the?age?is?greater?than?10000?days
WHERE?age?>?10000
需要我們注意的是：在有些數(shù)據(jù)庫，以及 SQL ：1990 標(biāo)準(zhǔn)中，派生表被歸為下一級(jí)——通用表語句（ common table experssion）。這就允許你在一個(gè) SELECT 語句中對(duì)派生表多次重用。上面的例子就（幾乎）等價(jià)于下面的語句：
WITH?a?AS?(
??SELECT?first_name,?last_name,?current_date?-?date_of_birth?age
??FROM?author
)
SELECT?*
FROM?a
WHERE?age?>?10000
當(dāng)然了，你也可以給“ a ”創(chuàng)建一個(gè)單獨(dú)的視圖，這樣你就可以在更廣泛的范圍內(nèi)重用這個(gè)派生表了。
我們學(xué)到了什么？
我們反復(fù)強(qiáng)調(diào)，大體上來說 SQL 語句就是對(duì)表的引用，而并非對(duì)字段的引用。要好好利用這一點(diǎn)，不要害怕使用派生表或者其他更復(fù)雜的語句。
8、 SQL 語句中 GROUP BY 是對(duì)表的引用進(jìn)行的操作
讓我們?cè)倩叵胍幌轮暗?FROM 語句：
FROM?a,?b
現(xiàn)在，我們將 GROUP BY 應(yīng)用到上面的語句中：
GROUP?BY?A.x,?A.y,?B.z
上面語句的結(jié)果就是產(chǎn)生出了一個(gè)包含三個(gè)字段的新的表的引用。我們來仔細(xì)理解一下這句話：當(dāng)你應(yīng)用 GROUP BY 的時(shí)候， SELECT 后沒有使用聚合函數(shù)的列，都要出現(xiàn)在 GROUP BY 后面。（譯者注：原文大意為“當(dāng)你是用 GROUP BY 的時(shí)候，你能夠?qū)ζ溥M(jìn)行下一級(jí)邏輯操作的列會(huì)減少，包括在 SELECT 中的列”）。需要注意的是：其他字段能夠使用聚合函數(shù)：
SELECT?A.x,?A.y,?SUM(A.z)FROM?AGROUP?BY?A.x,?A.y
還有一點(diǎn)值得留意的是：MySQL 并不堅(jiān)持這個(gè)標(biāo)準(zhǔn)，這的確是令人很困惑的地方。（譯者注：這并不是說 MySQL 沒有 GROUP BY 的功能）但是不要被 MySQL 所迷惑。GROUP BY 改變了對(duì)表引用的方式。你可以像這樣既在 SELECT 中引用某一字段，也在 GROUP BY 中對(duì)其進(jìn)行分組。我們學(xué)到了什么？
GROUP BY，再次強(qiáng)調(diào)一次，是在表的引用上進(jìn)行了操作，將其轉(zhuǎn)換為一種新的引用方式。
9、 SQL 語句中的 SELECT 實(shí)質(zhì)上是對(duì)關(guān)系的映射
我個(gè)人比較喜歡“映射”這個(gè)詞，尤其是把它用在關(guān)系代數(shù)上。（譯者注：原文用詞為 projection ，該詞有兩層含義，第一種含義是預(yù)測(cè)、規(guī)劃、設(shè)計(jì)，第二種意思是投射、映射，經(jīng)過反復(fù)推敲，我覺得這里用映射能夠更直觀的表達(dá)出 SELECT 的作用）。一旦你建立起來了表的引用，經(jīng)過修改、變形，你能夠一步一步的將其映射到另一個(gè)模型中。SELECT 語句就像一個(gè)“投影儀”，我們可以將其理解成一個(gè)將源表中的數(shù)據(jù)按照一定的邏輯轉(zhuǎn)換成目標(biāo)表數(shù)據(jù)的函數(shù)。
通過 SELECT語句，你能對(duì)每一個(gè)字段進(jìn)行操作，通過復(fù)雜的表達(dá)式生成所需要的數(shù)據(jù)。
SELECT 語句有很多特殊的規(guī)則，至少你應(yīng)該熟悉以下幾條：
你僅能夠使用那些能通過表引用而得來的字段； 如果你有 GROUP BY 語句，你只能夠使用 GROUP BY 語句后面的字段或者聚合函數(shù)； 當(dāng)你的語句中沒有 GROUP BY 的時(shí)候，可以使用開窗函數(shù)代替聚合函數(shù)； 當(dāng)你的語句中沒有 GROUP BY 的時(shí)候，你不能同時(shí)使用聚合函數(shù)和其它函數(shù)； 有一些方法可以將普通函數(shù)封裝在聚合函數(shù)中； ……
一些更復(fù)雜的規(guī)則多到足夠?qū)懗隽硪黄恼铝恕１热纾簽楹文悴荒茉谝粋€(gè)沒有 GROUP BY 的 SELECT 語句中同時(shí)使用普通函數(shù)和聚合函數(shù)？（上面的第 4 條）
原因如下：
憑直覺，這種做法從邏輯上就講不通。
如果直覺不能夠說服你，那么語法肯定能。SQL : 1999 標(biāo)準(zhǔn)引入了 GROUPING SETS，SQL：2003 標(biāo)準(zhǔn)引入了 group sets : GROUP BY() 。無論什么時(shí)候，只要你的語句中出現(xiàn)了聚合函數(shù)，而且并沒有明確的 GROUP BY 語句，這時(shí)一個(gè)不明確的、空的 GROUPING SET 就會(huì)被應(yīng)用到這段 SQL 中。因此，原始的邏輯順序的規(guī)則就被打破了，映射（即 SELECT ）關(guān)系首先會(huì)影響到邏輯關(guān)系，其次就是語法關(guān)系。（譯者注：這段話原文就比較艱澀，可以簡(jiǎn)單理解如下：在既有聚合函數(shù)又有普通函數(shù)的 SQL 語句中，如果沒有 GROUP BY 進(jìn)行分組，SQL 語句默認(rèn)視整張表為一個(gè)分組，當(dāng)聚合函數(shù)對(duì)某一字段進(jìn)行聚合統(tǒng)計(jì)的時(shí)候，引用的表中的每一條 record 就失去了意義，全部的數(shù)據(jù)都聚合為一個(gè)統(tǒng)計(jì)值，你此時(shí)對(duì)每一條 record 使用其它函數(shù)是沒有意義的）。
糊涂了？是的，我也是。我們?cè)倩剡^頭來看點(diǎn)淺顯的東西吧。
我們學(xué)到了什么？
SELECT 語句可能是 SQL 語句中最難的部分了，盡管他看上去很簡(jiǎn)單。其他語句的作用其實(shí)就是對(duì)表的不同形式的引用。而 SELECT 語句則把這些引用整合在了一起，通過邏輯規(guī)則將源表映射到目標(biāo)表，而且這個(gè)過程是可逆的，我們可以清楚的知道目標(biāo)表的數(shù)據(jù)是怎么來的。
想要學(xué)習(xí)好 SQL 語言，就要在使用 SELECT 語句之前弄懂其他的語句，雖然 SELECT 是語法結(jié)構(gòu)中的第一個(gè)關(guān)鍵詞，但它應(yīng)該是我們最后一個(gè)掌握的。
10、 SQL 語句中的幾個(gè)簡(jiǎn)單的關(guān)鍵詞：DISTINCT ， UNION ， ORDER BY 和 OFFSET
在學(xué)習(xí)完復(fù)雜的 SELECT 豫劇之后，我們?cè)賮砜袋c(diǎn)簡(jiǎn)單的東西：
集合運(yùn)算（ DISTINCT 和 UNION ） 排序運(yùn)算（ ORDER BY，OFFSET…FETCH） 集合運(yùn)算（ set operation）：
集合運(yùn)算主要操作在于集合上，事實(shí)上指的就是對(duì)表的一種操作。從概念上來說，他們很好理解：
DISTINCT 在映射之后對(duì)數(shù)據(jù)進(jìn)行去重 UNION 將兩個(gè)子查詢拼接起來并去重 UNION ALL 將兩個(gè)子查詢拼接起來但不去重 EXCEPT 將第二個(gè)字查詢中的結(jié)果從第一個(gè)子查詢中去掉 INTERSECT 保留兩個(gè)子查詢中都有的結(jié)果并去重 排序運(yùn)算（ ordering operation）：
排序運(yùn)算跟邏輯關(guān)系無關(guān)。這是一個(gè) SQL 特有的功能。排序運(yùn)算不僅在 SQL 語句的最后，而且在 SQL 語句運(yùn)行的過程中也是最后執(zhí)行的。使用 ORDER BY 和 OFFSET…FETCH 是保證數(shù)據(jù)能夠按照順序排列的最有效的方式。其他所有的排序方式都有一定隨機(jī)性，盡管它們得到的排序結(jié)果是可重現(xiàn)的。
OFFSET…SET是一個(gè)沒有統(tǒng)一確定語法的語句，不同的數(shù)據(jù)庫有不同的表達(dá)方式，如 MySQL 和 PostgreSQL 的 LIMIT…OFFSET、SQL Server 和 Sybase 的 TOP…START AT 等。
讓我們?cè)诠ぷ髦斜M情的使用 SQL！
正如其他語言一樣，想要學(xué)好 SQL 語言就要大量的練習(xí)。上面的 10 個(gè)簡(jiǎn)單的步驟能夠幫助你對(duì)你每天所寫的 SQL 語句有更好的理解。另一方面來講，從平時(shí)常見的錯(cuò)誤中也能積累到很多經(jīng)驗(yàn)。
鏈接：http://blog.jobbole.com/55086/
我是岳哥，最后給大家分享我寫的SQL兩件套：《SQL基礎(chǔ)知識(shí)第二版》和《SQL高級(jí)知識(shí)第二版》的PDF電子版。里面有各個(gè)語法的解釋、大量的實(shí)例講解和批注等等，非常通俗易懂，方便大家跟著一起來實(shí)操。
有需要的讀者可以下載學(xué)習(xí)，在下面的公眾號(hào)「數(shù)據(jù)前線」(非本號(hào))后臺(tái)回復(fù)關(guān)鍵字：SQL，就行
數(shù)據(jù)前線 ——End——
后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。
推薦閱讀
