|
很多程序員認(rèn)為SQL是一頭難以馴服的野獸。它是為數(shù)不多的聲明性語言之一，也因?yàn)檫@樣，其展示了完全不同于其他的表現(xiàn)形式、命令式語言、 面向?qū)ο笳Z言甚至函數(shù)式編程語言（雖然有些人覺得SQL 還是有些類似功能）。 我每天都寫SQL，我的開源軟件JOOQ中也包含SQL。因此我覺得有必要為還在為此苦苦掙扎的你呈現(xiàn)SQL的優(yōu)美！下面的教程面向于：
本教程將重點(diǎn)介紹SELECT 語句。其他 DML 語句將在另一個(gè)教程中在做介紹。接下來就是… 1、SQL是聲明性語言 首先你需要思考的是，聲明性。你唯一需要做的只是聲明你想獲得結(jié)果的性質(zhì)，而不需要考慮你的計(jì)算機(jī)怎么算出這些結(jié)果的。 SELECT first_name, last_name FROM employees WHERE salary > 100000 這很容易理解，你無須關(guān)心員工的身份記錄從哪來，你只需要知道誰有著不錯(cuò)的薪水。 從中我們學(xué)到了什么呢？ 那么如果它是這樣的簡(jiǎn)單，會(huì)出現(xiàn)什么問題嗎？問題就是我們大多數(shù)人會(huì)直觀地認(rèn)為這是命令式編程。如：“機(jī)器，做這，再做那，但在這之前，如果這和那都發(fā)生錯(cuò)誤，那么會(huì)運(yùn)行一個(gè)檢測(cè)”。這包括在變量中存儲(chǔ)臨時(shí)的編寫循環(huán)、迭代、調(diào)用函數(shù)，等等結(jié)果。 把那些都忘了吧，想想怎么去聲明，而不是怎么告訴機(jī)器去計(jì)算。 2、SQL語法不是“有序的” 常見的混亂源于一個(gè)簡(jiǎn)單的事實(shí)，SQL語法元素并不會(huì)按照它們的執(zhí)行方式排序。語法順序如下：
為簡(jiǎn)單起見，并沒有列出所有SQL語句。這個(gè)語法順序與邏輯順序基本上不同，執(zhí)行順序如下：
這有三點(diǎn)需要注意： 1、第一句是FROM，而不是SELECT。首先是將數(shù)據(jù)從磁盤加載到內(nèi)存中，以便對(duì)這些數(shù)據(jù)進(jìn)行操作。 2、SELECT是在其他大多數(shù)語句后執(zhí)行，最重要的是，在FROM和GROUP BY之后。重要的是要理解當(dāng)你覺得你可以從WHERE語句中引用你定義在SELECT語句當(dāng)中的時(shí)候，。以下是不可行的： SELECT A.x + A.y AS z FROM A WHERE z = 10 -- z is not available here! 如果你想重用z,您有兩種選擇。要么重復(fù)表達(dá)式: SELECT A.x + A.y AS z FROM A WHERE (A.x + A.y) = 10 或者你使用派生表、公用表表達(dá)式或視圖來避免代碼重復(fù)。請(qǐng)參閱示例進(jìn)一步的分析： 3、在語法和邏輯順序里，UNION都是放在ORDER BY之前，很多人認(rèn)為每個(gè)UNION子查詢都可以進(jìn)行排序，但根據(jù)SQL標(biāo)準(zhǔn)和大多數(shù)的SQL方言，并不是真的可行。雖然一些方言允許子查詢或派生表排序，但卻不能保證這種順序能在UNION操作后保留。 需要注意的是，并不是所有的數(shù)據(jù)庫都以相同的形式實(shí)現(xiàn)，例如規(guī)則2并不完全適用于MySQL,PostgreSQL,和SQLite上 從中我們學(xué)到了什么呢？ 要時(shí)刻記住SQL語句的語法順序和邏輯順序來避免常見的錯(cuò)誤。如果你能明白這其中的區(qū)別，就能明確知道為什么有些可以執(zhí)行有些則不能。 如果能設(shè)計(jì)一種在語法順序上實(shí)際又體現(xiàn)了邏輯順序的語言就更好了，因?yàn)樗窃谖④浀腖INQ上實(shí)現(xiàn)的。 3、SQL是關(guān)于數(shù)據(jù)表引用的 因?yàn)檎Z法順序和邏輯順序的差異，大多數(shù)初學(xué)者可能會(huì)誤認(rèn)為SQL中列的值是第一重要的。其實(shí)并非如此，最重要的是數(shù)據(jù)表引用。 該SQL標(biāo)準(zhǔn)定義了FROM語句，如下： <from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ] ROM語句的"output"是所有表引用的結(jié)合程度組合表引用。讓我們慢慢地消化這些。 FROM a, b上述產(chǎn)生一個(gè)a+b度的組合表引用，如果a有3列和b有5列，那么"輸出表"將有8（3+5）列。 包含在這個(gè)組合表引用的記錄是交叉乘積/笛卡兒積的axb。換句話說，每一條a記錄都會(huì)與每一條b記錄相對(duì)應(yīng)。如果a有3個(gè)記錄和b有5條記錄,然后上面的組合表引用將產(chǎn)生15條記錄(3×5)。 在WHERE語句篩選后，GROUP BY語句中"output"是"fed"/"piped"，它已轉(zhuǎn)成新的"output"，我們會(huì)稍后再去處理。 如果我們從關(guān)系代數(shù)/集合論的角度來看待這些東西，一個(gè)SQL表是一個(gè)關(guān)系或一組元素組合。每個(gè)SQL語句將改變一個(gè)或幾個(gè)關(guān)系，來產(chǎn)生新的關(guān)系。 從中我們學(xué)到了什么呢？ 一直從數(shù)據(jù)表引用角度去思考，有助于理解數(shù)據(jù)怎樣通過你的sql語句流水作業(yè)的 4、SQL數(shù)據(jù)表引用可以相當(dāng)強(qiáng)大 表引用是相當(dāng)強(qiáng)大的東西。舉個(gè)簡(jiǎn)單的例子，JOIN關(guān)鍵字其實(shí)不是SELECT語句的一部分，但卻是"special"表引用的一部分。連接表，在SQL標(biāo)準(zhǔn)中有定義（簡(jiǎn)化的）：  <table reference> ::= <table name> | <derived table> | <joined table> 如果我們又拿之前的例子來分析： FROM a, ba可以作為一個(gè)連接表，如： a1 JOIN a2 ON a1.id = a2.id 這擴(kuò)展到前一個(gè)表達(dá)式，我們會(huì)得到： FROM a1 JOIN a2 ON a1.id = a2.id, b 雖然結(jié)合了數(shù)據(jù)表引用語法與連接表語法的逗號(hào)分隔表讓人很無語，但你肯定還會(huì)這樣做的。結(jié)果，結(jié)合數(shù)據(jù)表引用將有a1+a2+b度。 派生表甚至比連接表更強(qiáng)大，我們接下來將會(huì)說到。 從中我們學(xué)到了什么呢？ 要時(shí)時(shí)刻刻考慮表引用，重要的是這不僅讓你理解數(shù)據(jù)怎樣通過你的sql語句流水作業(yè)的，它還將幫助你了解復(fù)雜表引用是如何構(gòu)造的。 而且，重要的是，了解JOIN是構(gòu)造連接表的關(guān)鍵字。不是的SELECT語句的一部分。某些數(shù)據(jù)庫允許JOIN在插入、更新、刪除中使用。 5、應(yīng)使用SQL JOIN的表，而不是以逗號(hào)分隔表 前面，我們已經(jīng)看到這語句： FROM a, b 高級(jí)SQL開發(fā)人員可能會(huì)告訴你，最好不要使用逗號(hào)分隔的列表，并且一直完整的表達(dá)你的JOINs。這將有助于改進(jìn)你的SQL語句的可讀性從而防止錯(cuò)誤出現(xiàn)。 一個(gè)非常常見的錯(cuò)誤是忘記某處連接謂詞。思考以下內(nèi)容： FROM a, b, c, d, e, f, g, h WHERE a.a1 = b.bx AND a.a2 = c.c1 AND d.d1 = b.bc -- etc... 使用join來查詢表的語法
從中我們學(xué)到了什么呢？ 使用JOIN，并且永遠(yuǎn)不在FROM語句中使用逗號(hào)分隔表引用。 6、SQL的不同類型的連接操作 連接操作基本上有五種
這些術(shù)語通常用于關(guān)系代數(shù)。對(duì)上述概念，如果他們存在，SQL會(huì)使用不同的術(shù)語。讓我們仔細(xì)看看: EQUI JOIN（同等連接） 這是最常見的JOIN操作。它有兩個(gè)子操作:
例子是其中的區(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 SEMI JOIN（半連接） 這種關(guān)系的概念在SQL中用兩種方式表達(dá)：使用IN謂詞或使用EXISTS謂語。"Semi"是指在拉丁語中的"half"。這種類型的連接用于連接只有"half"的表引用。再次考慮上述加入的作者和書。讓我們想象，我們想要作者/書的組合,但只是那些作者實(shí)際上也有書。然后我們可以這樣寫: -- 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) 雖然不能肯定你到底是更加喜歡IN還是EXISTS，而且也沒有規(guī)則說明，但可以這樣說：
因?yàn)镮NNER JOIN有可能只產(chǎn)生有書的作者，因?yàn)楹芏喑鯇W(xué)者可能認(rèn)為他們可以使用DISTINCT刪除重復(fù)項(xiàng)。他們認(rèn)為他們可以表達(dá)一個(gè)像這樣的半聯(lián)接： -- Find only those authors who also have books SELECT DISTINCT first_name, last_name FROM author 這是非常不好的做法，原因有二：
更多的關(guān)于DISTINCT濫用的問題，可以訪問這里的博客。 ANTI JOIN（反連接） 這個(gè)關(guān)系的概念跟半連接剛好相反。您可以簡(jiǎn)單地通過將 NOT 關(guān)鍵字添加到IN 或 EXISTS中生成它。在下例中，我們選擇那些沒有任何書籍的作者： -- 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ī)則對(duì)性能、可讀性、表現(xiàn)力都適用。然而，當(dāng)使用NOT IN時(shí)對(duì)NULLs會(huì)有一個(gè)小警告，這個(gè)問題有點(diǎn)超出本教程范圍。 CROSS JOIN（交叉連接） 結(jié)合第一個(gè)表中的內(nèi)容和第二個(gè)表中的內(nèi)容，引用兩個(gè)join表交叉生成一個(gè)新的東西。我們之前已經(jīng)看到，這可以在FROM語句中通過逗號(hào)分隔表引用來實(shí)現(xiàn)。在你確實(shí)需要的情況下，可以在SQL語句中明確地寫一個(gè)CROSS JOIN。 -- Combine every author with every book author CROSS JOIN book DIVISION（除法） 關(guān)系分割就是一只真正的由自己親自喂養(yǎng)的野獸。簡(jiǎn)而言之，如果JOIN是乘法，那么除法就是JOIN的反義詞。在SQL中，除法關(guān)系難以表達(dá)清楚。由于這是一個(gè)初學(xué)者的教程，解釋這個(gè)問題超出了我們的教程范圍。當(dāng)然如果你求知欲爆棚，那么就看這里，這里還有這里。 從中我們學(xué)到了什么呢？ 讓我們把前面講到的內(nèi)容再次牢記于心。SQL是表引用。連接表是相當(dāng)復(fù)雜的表引用。但關(guān)系表述和SQL表述還是有點(diǎn)區(qū)別的，并不是所有的關(guān)系連接操作都是正規(guī)的SQL連接操作。對(duì)關(guān)系理論有一點(diǎn)實(shí)踐與認(rèn)識(shí)，你就可以選擇JOIN正確的關(guān)系類型并能將其轉(zhuǎn)化為正確的SQL。 7、SQL的派生表就像表的變量 前文，我們已經(jīng)了解到SQL是一種聲明性語言，因此不會(huì)有變量。（雖然在一些SQL語句中可能會(huì)存在）但你可以寫這樣的變量。那些野獸一般的表被稱為派生表。 派生表只不過是包含在括號(hào)里的子查詢。 -- A derived table FROM (SELECT * FROM author) 需要注意的是，一些SQL方言要求派生表有一個(gè)關(guān)聯(lián)的名字（也被稱為別名）。 -- A derived table with an alias FROM (SELECT * FROM author) a 當(dāng)你想規(guī)避由SQL子句邏輯排序造成的問題時(shí)，你會(huì)發(fā)現(xiàn)派生表可以用帥呆了來形容。例如，如果你想在SELECT和WHERE子句中重用一個(gè)列表達(dá)式，只寫（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:1999標(biāo)準(zhǔn)里已將派生表帶到下一級(jí)別，,引入公共表表達(dá)式。這將允許你在單一的SQL SELECT中重復(fù)使用相同的派生表。上面的查詢將轉(zhuǎn)化為類似這樣的： WITH a AS ( SELECT first_name, last_name, current_date - date_of_birth age FROM author ) SELECT * FROM a WHERE age > 10000 很明顯，對(duì)廣泛重用的常見SQL子查詢，你也可以灌輸具體"a"到一個(gè)獨(dú)立視圖中。想要了解更多就看這里。 從中我們學(xué)到了什么呢？ 再溫習(xí)一遍，SQL主要是關(guān)于表引用，而不是列。好好利用這些表引用。不要害怕寫派生表或其他復(fù)雜的表引用。 8、SQL GROUP BY轉(zhuǎn)換之前的表引用 讓我們重新考慮我們之前的FROM語句： FROM a, b現(xiàn)在,讓我們來應(yīng)用一個(gè)GROUP BY語句到上述組合表引用 GROUP BY A.x, A.y, B.z 這會(huì)產(chǎn)生一個(gè)只有其余三個(gè)列(!)的新的表引用。讓我們?cè)傧槐?。如果你用了GROUP BY，那么你在所有后續(xù)邏輯條款-包括選擇中減少可用列的數(shù)量。這就是為什么你只可以從SELECT語句中的GROUP BY語句引用列語法的原因。
SELECT A.x, A.y, SUM(A.z) FROM A GROUP BY A.x, A.y
從中我們學(xué)到了什么呢？ GROUP BY，在表引用上操作，將它們轉(zhuǎn)換成一個(gè)新表。 9、SQL SELECT在關(guān)系代數(shù)中被稱為投影 當(dāng)它在關(guān)系代數(shù)中使用時(shí)，我個(gè)人比較喜歡用"投影"一詞中。一旦你生成你的表引用，過濾，轉(zhuǎn)換它，你可以一步將它投影到另一個(gè)表中。SELECT語句就像一個(gè)投影機(jī)。表函數(shù)利用行值表達(dá)式將之前構(gòu)造的表引用的每個(gè)記錄轉(zhuǎn)化為最終結(jié)果。 在SELECT語句中，你終于可以在列上操作，創(chuàng)建復(fù)雜的列表達(dá)式作為記錄/行的一部分。 有很多關(guān)于可用的表達(dá)式，函數(shù)性質(zhì)等的特殊規(guī)則。最重要的是，你應(yīng)該記住這些： 1、你只能使用從“output”表引用產(chǎn)生的列引用 2、如果你有GROUP BY語句，你只可能從該語句或聚合函數(shù)引用列 3、當(dāng)你沒有GROUP BY語句時(shí)，你可以用窗口函數(shù)替代聚合函數(shù) 4、如果你沒有GROUP BY語句，你就不能將聚合函數(shù)與非聚合函數(shù)結(jié)合起來 5、這有一些關(guān)于在聚合函數(shù)包裝常規(guī)函數(shù)的規(guī)則，反之亦然 6、還有… 嗯，這有很多復(fù)雜的規(guī)則。他們可以填補(bǔ)另一個(gè)教程。例如，之所以你不能將聚合函數(shù)與非聚合函數(shù)結(jié)合起來投影到?jīng)]有GROUP BY的SELECT語句中是因?yàn)椋?/p> 1、憑直覺，沒有任何意義。 2、對(duì)一個(gè)SQL初學(xué)者來說，直覺還是毫無幫助的，語法規(guī)則則可以。SQL:1999年引入了分組集，SQL：2003引入了空分組集GROUP BY()。每當(dāng)存在沒有顯式GROUP BY語句的聚合函數(shù)時(shí)就會(huì)應(yīng)用隱式的空分組集（規(guī)則2）。因此，最初關(guān)于邏輯順序的那個(gè)規(guī)則就不完全正確了，SELECT的投影也會(huì)影響前面的邏輯結(jié)果，但語法語句GROUP BY卻不受影響。 是不是有點(diǎn)迷糊？其實(shí)我也是，讓我們看一些簡(jiǎn)單的吧。 從中我們學(xué)到了什么呢？ 在SQL語句中，SELECT語句可能是最復(fù)雜的語句之一，即使它看起來是那么的簡(jiǎn)單。所有其他語句只不過是從這個(gè)到另一個(gè)表引用的的輸送管道。通過將它們完全轉(zhuǎn)化，后期地對(duì)它們應(yīng)用一些規(guī)則，SELECT語句完完全全地?cái)噥y了這些表引用的美。 為了了解SQL，重要的是要理解其他的一切，都要嘗試在SELECT之前解決。即便SELECT在語法順序中排第一的語句，也應(yīng)該將它放到最后。 10.相對(duì)簡(jiǎn)單一點(diǎn)的SQL DISTINCT,UNION,ORDER BY,和OFFSET 看完復(fù)雜的SELECT之后，我們看回一些簡(jiǎn)單的東西。
集合運(yùn)算 集合運(yùn)算在除了表其實(shí)沒有其他東西的“集”上操作。嗯，差不多是這樣，從概念上講，它們還是很容易理解的
所有這些刪除重復(fù)項(xiàng)通常是沒有意義的，很多時(shí)候，當(dāng)你想要連接子查詢時(shí)，你應(yīng)該使用UNION ALL。 排序操作 排序不是一個(gè)關(guān)系特征，它是SQL僅有的特征。在你的SQL語句中，它被應(yīng)用在語法排序和邏輯排序之后。保證可以通過索引訪問記錄的唯一可靠方法是使用ORDER BY a和OFFSET..FETCH。所有其他的排序總是任意的或隨機(jī)的，即使它看起來像是可再現(xiàn)的。 OFFSET..FETCH是唯一的語法變體。其他變體包括MySQL'和PostgreSQL的LIMIT..OFFSET，或者SQL Server和Sybase的TOP..START AT（這里）。 讓我們開始應(yīng)用吧 跟其他每個(gè)語言一樣，要掌握SQL語言需要大量的實(shí)踐。上述10個(gè)簡(jiǎn)單的步驟將讓你每天編寫SQL時(shí)更有意義。另一方面，你也可以從常見的錯(cuò)誤中學(xué)習(xí)到更多。下面的兩篇文章列出許多Java（和其他）開發(fā)者寫SQL時(shí)常見的錯(cuò)誤： · 10 Common Mistakes Java Developers Make when Writing SQL · 10 More Common Mistakes Java Developers Make when Writing SQL |
|
|
