十步完全理解SQL

1、十步完全理解 SQL2013/07/08 · IT技術(shù), 書籍與教程 · 31 評論 · SQL分享到：964本文由 伯樂在線 - 水果泡騰片 翻譯。未經(jīng)許可，禁止轉(zhuǎn)載！英文出處： Lukas Eder 。歡迎加入翻譯組。很多程序員視 SQL 為洪水猛獸。SQL 是一種為數(shù)不多的聲明性語言，它的運行方式完全不同于我們所熟知的命令行語言、面向?qū)ο蟮某绦蛘Z言、甚至是函數(shù)語言（盡管有些人認為 SQL 語言也是一種函數(shù)式語言）。我們每天都在寫 SQL 并

2、且應(yīng)用在開源軟件 jOOQ 中。于是我想把 SQL 之美介紹給那些仍然對它頭疼不已的朋友，所以本文是為了以下讀者而特地編寫的：1、 在工作中會用到 SQL 但是對它并不完全了解的人。2、 能夠熟練使用 SQL 但是并不了解其語法邏輯的人。3、 想要教別人 SQL 的人。本文著重介紹 SELECT 句式，其他的 DML （Data Manipulation Language 數(shù)據(jù)操縱語言命令）將會在別的文章中進行介紹。10個簡單步驟，完全理解SQL1、 SQL 是一種聲明式語言首先要把這個概念記在腦中：“聲明”。 SQL 語言是為計算機聲明了一個你想從原始數(shù)據(jù)中獲得什么樣的結(jié)果的一個范例，而不是

3、告訴計算機如何能夠得到結(jié)果。這是不是很棒？（譯者注：簡單地說，SQL 語言聲明的是結(jié)果集的屬性，計算機會根據(jù) SQL 所聲明的內(nèi)容來從數(shù)據(jù)庫中挑選出符合聲明的數(shù)據(jù)，而不是像傳統(tǒng)編程思維去指示計算機如何操作。）MySQL1SELECT first_name, last_name FROM employees WHERE salary > 100000上面的例子很容易理解，我們不關(guān)心這些雇員記錄從哪里來，我們所需要的只是那些高薪者的數(shù)據(jù)（譯者注： salary>100000 ）。我們從哪兒學(xué)習(xí)到這些？如果 SQL 語言這么簡單，那么是什么讓人們“聞 SQL 色變”？主要的原因是：我們潛

4、意識中的是按照命令式編程的思維方式思考問題的。就好像這樣：“電腦，先執(zhí)行這一步，再執(zhí)行那一步，但是在那之前先檢查一下是否滿足條件 A 和條件 B ”。例如，用變量傳參、使用循環(huán)語句、迭代、調(diào)用函數(shù)等等，都是這種命令式編程的思維慣式。2、 SQL 的語法并不按照語法順序執(zhí)行SQL 語句有一個讓大部分人都感到困惑的特性，就是：SQL 語句的執(zhí)行順序跟其語句的語法順序并不一致。SQL 語句的語法順序是：· SELECTDISTINCT· FROM· WHERE· GROUP BY· HAVING· UNION· ORDER BY為

5、了方便理解，上面并沒有把所有的 SQL 語法結(jié)構(gòu)都列出來，但是已經(jīng)足以說明 SQL 語句的語法順序和其執(zhí)行順序完全不一樣，就以上述語句為例，其執(zhí)行順序為：· FROM· WHERE· GROUP BY· HAVING· SELECT· DISTINCT· UNION· ORDER BY關(guān)于 SQL 語句的執(zhí)行順序，有三個值得我們注意的地方：1、 FROM 才是 SQL 語句執(zhí)行的第一步，并非 SELECT 。數(shù)據(jù)庫在執(zhí)行 SQL 語句的第一步是將數(shù)據(jù)從硬盤加載到數(shù)據(jù)緩沖區(qū)中，以便對這些數(shù)據(jù)進行操作。（譯者注：原文為

6、“The first thing that happens is loading data from the disk into memory, in order to operate on such data.”，但是并非如此，以 Oracle 等常用數(shù)據(jù)庫為例，數(shù)據(jù)是從硬盤中抽取到數(shù)據(jù)緩沖區(qū)中進行操作。）2、 SELECT 是在大部分語句執(zhí)行了之后才執(zhí)行的，嚴格的說是在 FROM 和 GROUP BY 之后執(zhí)行的。理解這一點是非常重要的，這就是你不能在 WHERE 中使用在 SELECT 中設(shè)定別名的字段作為判斷條件的原因。123SELECT A.x + A.y AS zFROM AWHE

7、RE z = 10 - z 在此處不可用，因為SELECT是最后執(zhí)行的語句！如果你想重用別名z，你有兩個選擇。要么就重新寫一遍 z 所代表的表達式：123SELECT A.x + A.y AS zFROM AWHERE (A.x + A.y) = 10或者求助于衍生表、通用數(shù)據(jù)表達式或者視圖，以避免別名重用。請看下文中的例子。3、 無論在語法上還是在執(zhí)行順序上， UNION 總是排在在 ORDER BY 之前。很多人認為每個 UNION 段都能使用 ORDER BY 排序，但是根據(jù) SQL 語言標準和各個數(shù)據(jù)庫 SQL 的執(zhí)行差異來看，這并不是真的。盡管某些數(shù)據(jù)庫允許 SQL 語句對子查詢（s

8、ubqueries）或者派生表（derived tables）進行排序，但是這并不說明這個排序在 UNION 操作過后仍保持排序后的順序。注意：并非所有的數(shù)據(jù)庫對 SQL 語句使用相同的解析方式。如 MySQL、PostgreSQL和 SQLite 中就不會按照上面第二點中所說的方式執(zhí)行。我們學(xué)到了什么？既然并不是所有的數(shù)據(jù)庫都按照上述方式執(zhí)行 SQL 預(yù)計，那我們的收獲是什么？我們的收獲是永遠要記得： SQL 語句的語法順序和其執(zhí)行順序并不一致，這樣我們就能避免一般性的錯誤。如果你能記住 SQL 語句語法順序和執(zhí)行順序的差異，你就能很容易的理解一些很常見的 SQL 問題。當(dāng)然，如果一種語言被

9、設(shè)計成語法順序直接反應(yīng)其語句的執(zhí)行順序，那么這種語言對程序員是十分友好的，這種編程語言層面的設(shè)計理念已經(jīng)被微軟應(yīng)用到了 LINQ 語言中。3、 SQL 語言的核心是對表的引用（table references）由于 SQL 語句語法順序和執(zhí)行順序的不同，很多同學(xué)會認為SELECT 中的字段信息是 SQL 語句的核心。其實真正的核心在于對表的引用。根據(jù) SQL 標準，F(xiàn)ROM 語句被定義為：1<from clause> := FROM <table reference> <comma> <table reference> . FROM 語句的“輸出

10、”是一張聯(lián)合表，來自于所有引用的表在某一維度上的聯(lián)合。我們們慢慢來分析：1FROM a, b上面這句 FROM 語句的輸出是一張聯(lián)合表，聯(lián)合了表 a 和表 b 。如果 a 表有三個字段， b 表有 5 個字段，那么這個“輸出表”就有 8 （ =5+3）個字段。這個聯(lián)合表里的數(shù)據(jù)是 a*b，即 a 和 b 的笛卡爾積。換句話說，也就是 a 表中的每一條數(shù)據(jù)都要跟 b 表中的每一條數(shù)據(jù)配對。如果 a 表有3 條數(shù)據(jù)， b 表有 5 條數(shù)據(jù)，那么聯(lián)合表就會有 15 （ =5*3）條數(shù)據(jù)。FROM 輸出的結(jié)果被 WHERE 語句篩選后要經(jīng)過 GROUP BY 語句處理，從而形成新的輸出結(jié)果。我們后面還

11、會再討論這方面問題。如果我們從集合論（關(guān)系代數(shù)）的角度來看，一張數(shù)據(jù)庫的表就是一組數(shù)據(jù)元的關(guān)系，而每個 SQL 語句會改變一種或數(shù)種關(guān)系，從而產(chǎn)生出新的數(shù)據(jù)元的關(guān)系（即產(chǎn)生新的表）。我們學(xué)到了什么？思考問題的時候從表的角度來思考問題提，這樣很容易理解數(shù)據(jù)如何在 SQL 語句的“流水線”上進行了什么樣的變動。4、 靈活引用表能使 SQL 語句變得更強大靈活引用表能使 SQL 語句變得更強大。一個簡單的例子就是 JOIN 的使用。嚴格的說 JOIN 語句并非是 SELECT 中的一部分，而是一種特殊的表引用語句。 SQL 語言標準中表的連接定義如下：1234<table reference&

12、gt; :=    <table name>  | <derived table>  | <joined table>就拿之前的例子來說：1FROM a, ba 可能輸如下表的連接：1a1 JOIN a2 ON a1.id = a2.id將它放到之前的例子中就變成了：1FROM a1 JOIN a2 ON a1.id = a2.id, b盡管將一個連接表用逗號跟另一張表聯(lián)合在一起并不是常用作法，但是你的確可以這么做。結(jié)果就是，最終輸出的表就有了 a1+a2+b 個字段了。（譯者注

13、：原文這里用詞為 degree ，譯為維度。如果把一張表視圖化，我們可以想象每一張表都是由橫縱兩個維度組成的，橫向維度即我們所說的字段或者列，英文為columns；縱向維度即代表了每條數(shù)據(jù)，英文為 record ，根據(jù)上下文，作者這里所指的應(yīng)該是字段數(shù)。）在 SQL 語句中派生表的應(yīng)用甚至比表連接更加強大，下面我們就要講到表連接。我們學(xué)到了什么？思考問題時，要從表引用的角度出發(fā)，這樣就很容易理解數(shù)據(jù)是怎樣被 SQL 語句處理的，并且能夠幫助你理解那些復(fù)雜的表引用是做什么的。更重要的是，要理解 JOIN 是構(gòu)建連接表的關(guān)鍵詞，并不是 SELECT 語句的一部分。有一些數(shù)據(jù)庫允許在 INSERT

14、、 UPDATE 、 DELETE 中使用 JOIN 。5、 SQL 語句中推薦使用表連接我們先看看剛剛這句話：1FROM a, b高級 SQL 程序員也許學(xué)會給你忠告：盡量不要使用逗號來代替 JOIN 進行表的連接，這樣會提高你的 SQL 語句的可讀性，并且可以避免一些錯誤。利用逗號來簡化 SQL 語句有時候會造成思維上的混亂，想一下下面的語句：12345FROM a, b, c, d, e, f, g, hWHERE a.a1 = b.bxAND a.a2 = c.c1AND d.d1 = b.bc- etc.我們不難看出使用 JOIN 語句的好處在于：· 安全。 JOIN 和要

15、連接的表離得非常近，這樣就能避免錯誤。· 更多連接的方式，JOIN 語句能去區(qū)分出來外連接和內(nèi)連接等。我們學(xué)到了什么？記著要盡量使用 JOIN 進行表的連接，永遠不要在 FROM 后面使用逗號連接表。6、 SQL 語句中不同的連接操作SQL 語句中，表連接的方式從根本上分為五種：· EQUI JOIN· SEMI JOIN· ANTI JOIN· CROSS JOIN· DIVISIONEQUI JOIN這是一種最普通的 JOIN 操作，它包含兩種連接方式：· INNER JOIN（或者是 JOIN ）· OUTE

16、R JOIN（包括： LEFT 、 RIGHT、 FULL OUTER JOIN）用例子最容易說明其中區(qū)別：12345678910- This table reference contains authors and their books.- There is one record for each book and its author.- authors without books are NOT includedauthor JOIN book ON author.id = book.author_id - This table reference contains auth

17、ors 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_idSEMI JOIN這種連接關(guān)系在 SQL 中有兩種表現(xiàn)方式：使用 IN，或者使用 EXI

18、STS?！?SEMI ”在拉丁文中是“半”的意思。這種連接方式是只連接目標表的一部分。這是什么意思呢？再想一下上面關(guān)于作者和書名的連接。我們想象一下這樣的情況：我們不需要作者 / 書名這樣的組合，只是需要那些在書名表中的書的作者信息。那我們就能這么寫：1234567- Using INFROM authorWHERE author.id IN (SELECT book.author_id FROM book) - Using EXISTSFROM authorWHERE EXISTS (SELECT 1 FROM book WHERE book.author_id = author.

19、id)盡管沒有嚴格的規(guī)定說明你何時應(yīng)該使用 IN ，何時應(yīng)該使用 EXISTS ，但是這些事情你還是應(yīng)該知道的：· IN比 EXISTS 的可讀性更好· EXISTS 比IN 的表達性更好（更適合復(fù)雜的語句）· 二者之間性能沒有差異（但對于某些數(shù)據(jù)庫來說性能差異會非常大）因為使用 INNER JOIN 也能得到書名表中書所對應(yīng)的作者信息，所以很多初學(xué)者機會認為可以通過 DISTINCT 進行去重，然后將 SEMI JOIN 語句寫成這樣：1234- Find only those authors who also have booksSELECT DISTINCT

20、 first_name, last_nameFROM authorJOIN book ON author.id = book.author_id這是一種很糟糕的寫法，原因如下：· SQL 語句性能低下：因為去重操作（ DISTINCT ）需要數(shù)據(jù)庫重復(fù)從硬盤中讀取數(shù)據(jù)到內(nèi)存中。（譯者注： DISTINCT 的確是一種很耗費資源的操作，但是每種數(shù)據(jù)庫對于 DISTINCT 的操作方式可能不同）。· 這么寫并非完全正確：盡管也許現(xiàn)在這么寫不會出現(xiàn)問題，但是隨著 SQL 語句變得越來越復(fù)雜，你想要去重得到正確的結(jié)果就變得十分困難。更多的關(guān)于濫用 DISTINCT 的危害可以參考這

21、篇博文（/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/）。ANTI JOIN這種連接的關(guān)系跟 SEMI JOIN 剛好相反。在 IN 或者 EXISTS 前加一個 NOT 關(guān)鍵字就能使用這種連接。舉個例子來說，我們列出書名表里沒有書的作者：1234567- Using INFROM authorWHERE author.id NOT IN (SELECT book.author_id FROM book) - Using EXISTSFROM author

22、WHERE NOT EXISTS (SELECT 1 FROM book WHERE book.author_id = author.id)關(guān)于性能、可讀性、表達性等特性也完全可以參考 SEMI JOIN。這篇博文介紹了在使用 NOT IN 時遇到 NULL 應(yīng)該怎么辦，因為有一點背離本篇主題，就不詳細介紹，有興趣的同學(xué)可以讀一下（/2012/01/27/sql-incompatibilities-not-in-and-null-values/）。CROSS JOIN這個連接過程就是兩個連接的表的乘積：即將第一張表的每一條數(shù)據(jù)分別對應(yīng)第二張表的每條數(shù)據(jù)。我

23、們之前見過，這就是逗號在 FROM 語句中的用法。在實際的應(yīng)用中，很少有地方能用到 CROSS JOIN，但是一旦用上了，你就可以用這樣的 SQL語句表達：12- Combine every author with every bookauthor CROSS JOIN bookDIVISIONDIVISION 的確是一個怪胎。簡而言之，如果 JOIN 是一個乘法運算，那么 DIVISION 就是 JOIN 的逆過程。DIVISION 的關(guān)系很難用 SQL 表達出來，介于這是一個新手指南，解釋 DIVISION 已經(jīng)超出了我們的目的。但是有興趣的同學(xué)還是可以來看看這三篇文章（http:/blo

24、/2012/03/30/advanced-sql-relational-division-in-jooq/）（/wiki/Relational_algebra#Division）（https:/www.simple-推薦閱讀 _ 畫圖解釋SQL聯(lián)合語句我們學(xué)到了什么？學(xué)到了很多！讓我們在腦海中再回想一下。 SQL 是對表的引用， JOIN 則是一種引用表的復(fù)雜方式。但是 SQL 語言的表達方式和實際我們所需要的邏輯關(guān)系之間是有區(qū)別的，并非所有的邏輯關(guān)系都能找到對應(yīng)的 JOIN 操作，所以這就要我們在平時多積累和學(xué)習(xí)關(guān)系邏

25、輯，這樣你就能在以后編寫 SQL 語句中選擇適當(dāng)?shù)?JOIN 操作了。7、 SQL 中如同變量的派生表在這之前，我們學(xué)習(xí)到過 SQL 是一種聲明性的語言，并且 SQL 語句中不能包含變量。但是你能寫出類似于變量的語句，這些就叫做派生表：說白了，所謂的派生表就是在括號之中的子查詢：12- A derived tableFROM (SELECT * FROM author)需要注意的是有些時候我們可以給派生表定義一個相關(guān)名（即我們所說的別名）。12- A derived table with an aliasFROM (SELECT * FROM author) a派生表可以有效的避免由于 SQL

26、 邏輯而產(chǎn)生的問題。舉例來說：如果你想重用一個用 SELECT 和 WHERE 語句查詢出的結(jié)果，這樣寫就可以（以 Oracle 為例）：12345678- Get authors' first and last names, and their age in daysSELECT first_name, last_name, ageFROM (  SELECT first_name, last_name, current_date - date_of_birth age  FROM author)- If the age is greater

27、than 10000 daysWHERE age > 10000需要我們注意的是：在有些數(shù)據(jù)庫，以及 SQL ： 1990 標準中，派生表被歸為下一級通用表語句（ common table experssion）。這就允許你在一個 SELECT 語句中對派生表多次重用。上面的例子就（幾乎）等價于下面的語句：1234567WITH a AS (  SELECT first_name, last_name, current_date - date_of_birth age  FROM author)SELECT *FROM aWHERE age >

28、; 10000當(dāng)然了，你也可以給“ a ”創(chuàng)建一個單獨的視圖，這樣你就可以在更廣泛的范圍內(nèi)重用這個派生表了。更多信息可以閱讀下面的文章（/wiki/View_%28SQL%29）。我們學(xué)到了什么？我們反復(fù)強調(diào)，大體上來說 SQL 語句就是對表的引用，而并非對字段的引用。要好好利用這一點，不要害怕使用派生表或者其他更復(fù)雜的語句。8、 SQL 語句中 GROUP BY 是對表的引用進行的操作讓我們再回想一下之前的 FROM 語句：1FROM a, b現(xiàn)在，我們將 GROUP BY 應(yīng)用到上面的語句中：1GROUP BY A.x, A.y, B.z上面語句

29、的結(jié)果就是產(chǎn)生出了一個包含三個字段的新的表的引用。我們來仔細理解一下這句話：當(dāng)你應(yīng)用 GROUP BY 的時候， SELECT 后沒有使用聚合函數(shù)的列，都要出現(xiàn)在 GROUP BY 后面。（譯者注：原文大意為“當(dāng)你是用 GROUP BY 的時候，你能夠?qū)ζ溥M行下一級邏輯操作的列會減少，包括在 SELECT 中的列”）。· 需要注意的是：其他字段能夠使用聚合函數(shù)：123SELECT A.x, A.y, SUM(A.z)FROM AGROUP BY A.x, A.y· 還有一點值得留意的是： MySQL 并不堅持這個標準，這的確是令人很困惑的地方。（譯者注：這并不是說 MySQ

30、L 沒有 GROUP BY 的功能）但是不要被 MySQL 所迷惑。 GROUP BY 改變了對表引用的方式。你可以像這樣既在 SELECT 中引用某一字段，也在 GROUP BY 中對其進行分組。我們學(xué)到了什么？GROUP BY，再次強調(diào)一次，是在表的引用上進行了操作，將其轉(zhuǎn)換為一種新的引用方式。9、 SQL 語句中的 SELECT 實質(zhì)上是對關(guān)系的映射我個人比較喜歡“映射”這個詞，尤其是把它用在關(guān)系代數(shù)上。（譯者注：原文用詞為 projection ，該詞有兩層含義，第一種含義是預(yù)測、規(guī)劃、設(shè)計，第二種意思是投射、映射，經(jīng)過反復(fù)推敲，我覺得這里用映射能夠更直觀的表達出 SELECT 的作用

31、）。一旦你建立起來了表的引用，經(jīng)過修改、變形，你能夠一步一步的將其映射到另一個模型中。 SELECT 語句就像一個“投影儀”，我們可以將其理解成一個將源表中的數(shù)據(jù)按照一定的邏輯轉(zhuǎn)換成目標表數(shù)據(jù)的函數(shù)。通過 SELECT語句，你能對每一個字段進行操作，通過復(fù)雜的表達式生成所需要的數(shù)據(jù)。SELECT 語句有很多特殊的規(guī)則，至少你應(yīng)該熟悉以下幾條：1. 你僅能夠使用那些能通過表引用而得來的字段；2. 如果你有 GROUP BY 語句，你只能夠使用 GROUP BY 語句后面的字段或者聚合函數(shù)；3. 當(dāng)你的語句中沒有 GROUP BY 的時候，可以使用開窗函數(shù)代替聚合函數(shù)；4. 當(dāng)你的語句中沒有 GR

32、OUP BY 的時候，你不能同時使用聚合函數(shù)和其它函數(shù)；5. 有一些方法可以將普通函數(shù)封裝在聚合函數(shù)中；6. 一些更復(fù)雜的規(guī)則多到足夠?qū)懗隽硪黄恼铝恕１热纾簽楹文悴荒茉谝粋€沒有 GROUP BY 的 SELECT 語句中同時使用普通函數(shù)和聚合函數(shù)？（上面的第 4 條）原因如下：1. 憑直覺，這種做法從邏輯上就講不通。2. 如果直覺不能夠說服你，那么語法肯定能。 SQL : 1999 標準引入了 GROUPING SETS，SQL： 2003 標準引入了 group sets : GROUP BY() 。無論什么時候，只要你的語句中出現(xiàn)了聚合函數(shù)，而且并沒有明確的 GROUP BY 語句，這時一個不明確的、空的 GROUPING SET 就會被應(yīng)用到這段 SQL 中。因此，原始的邏輯順序的規(guī)則就被打破了，映射（即 SELECT ）關(guān)系首先會影響到邏輯關(guān)系，其次就是語法關(guān)系。（譯者注：這段話原文就比較艱澀，可以簡單理解如下：在