來源:Linary
https://www.cnblogs.com/lina-zhang/p/3761409.html
多年前收藏在筆記中的一篇文章,今天偶然翻出,重讀了一遍,依然大有收獲。分享出來,大家一起探討。
以本文是為了以下讀者而特地編寫的:
1. 在工作中會用到 SQL 但是對它并不完全了解的人。
2. 能夠熟練使用 SQL 但是并不了解其語法邏輯的人。
3. 想要教別人 SQL 的人。
本文著重介紹 SELECT 句式。
1、SQL 是一種聲明式語言
首先要把這個概念記在腦中:“聲明”。SQL 語言是為計算機(jī)聲明了一個你想從原始數(shù)據(jù)中獲得什么樣的結(jié)果的一個范例,而不是告訴計算機(jī)如何能夠得到結(jié)果。sql的執(zhí)行引擎會根據(jù)你聲明的數(shù)據(jù)結(jié)果去獲取對應(yīng)的數(shù)據(jù)。
SELECT first_name, last_name FROM employees WHERE salary 》 100000
上面的例子很容易理解,我們不關(guān)心這些雇員記錄從哪里來,我們所需要的只是工資大于10W的員工。
我們從哪兒學(xué)習(xí)到這些?
如果 SQL 語言這么簡單,那么是什么讓人們“聞 SQL 色變”?
主要的原因是:我們潛意識中的是按照命令式編程的思維方式思考問題的。就好像這樣:“電腦,先執(zhí)行這一步,再執(zhí)行那一步,但是在那之前先檢查一下是否滿足條件 A 和條件 B ”。例如,用變量傳參、使用循環(huán)語句、迭代、調(diào)用函數(shù)等等,都是這種命令式編程的思維慣式。
2、SQL 的語法并不按照語法順序執(zhí)行
SQL 語句有一個讓大部分人都感到困惑的特性,就是:SQL 語句的執(zhí)行順序跟其語句的語法順序并不一致。SQL 語句的語法順序是:
SELECT [DISTINCT] FROM WHERE GROUP BY HAVING UNION ORDER BY
為了方便理解,上面并沒有把所有的 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ù)進(jìn)行操作。
2. SELECT 是在大部分語句執(zhí)行了之后才執(zhí)行的,嚴(yán)格的說是在 FROM 和 GROUP BY 之后執(zhí)行的。理解這一點(diǎn)是非常重要的,這就是你不能在 WHERE 中使用在 SELECT 中設(shè)定別名的字段作為判斷條件的原因。
SELECT A.x + A.y AS z FROM A WHERE z = 10 -- z 在此處不可用,因為SELECT是最后執(zhí)行的語句!
如果你想重用別名z,你有兩個選擇。要么就重新寫一遍 z 所代表的表達(dá)式:
SELECT A.x + A.y AS z FROM A WHERE (A.x + A.y) = 10 或者求助于衍生表、通用數(shù)據(jù)表達(dá)式或者視圖,以避免別名重用。
3. 無論在語法上還是在執(zhí)行順序上, UNION 總是排在在 ORDER BY 之前。很多人認(rèn)為每個 UNION 段都能使用 ORDER BY 排序,但是根據(jù) SQL 語言標(biāo)準(zhǔn)和各個數(shù)據(jù)庫 SQL的執(zhí)行差異來看,這并不是真的。
4. 盡管某些數(shù)據(jù)庫允許 SQL 語句對子查詢(subqueries)或者派生表(derived tables)進(jìn)行排序,但是這并不說明這個排序在 UNION 操作過后仍保持排序后的順序。注意:并非所有的數(shù)據(jù)庫對 SQL 語句使用相同的解析方式。如 MySQL、PostgreSQL和 SQLite 中就不會按照上面第二點(diǎn)中所說的方式執(zhí)行。
我們學(xué)到了什么?
既然并不是所有的數(shù)據(jù)庫都按照上述方式執(zhí)行 SQL,那我們的收獲是什么?
我們的收獲是永遠(yuǎn)要記得:SQL 語句的語法順序和其執(zhí)行順序并不一致,這樣我們就能避免一般性的錯誤。如果你能記住 SQL 語句語法順序和執(zhí)行順序的差異,你就能很容易的理解一些很常見的 SQL 問題。
當(dāng)然,如果一種語言被設(shè)計成語法順序直接反應(yīng)其語句的執(zhí)行順序,那么這種語言對程序員是十分友好的,這種編程語言層面的設(shè)計理念已經(jīng)被微軟應(yīng)用到了 LINQ 語言中。
3、SQL 語言的核心是對表的引用(table references)
由于 SQL 語句語法順序和執(zhí)行順序的不同,很多同學(xué)會認(rèn)為SELECT 中的字段信息是 SQL 語句的核心。其實真正的核心在于對表的引用。
根據(jù) SQL 標(biāo)準(zhǔn),F(xiàn)ROM 語句被定義為:
《from clause》 ::= FROM 《table reference》 [ { 《comma》 《table reference》 }。。。 ]
FROM語句的“輸出”是一張聯(lián)合表,來自于所有引用的表在某一維度上的聯(lián)合。我們們慢慢來分析:
FROM 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é)果。
如果我們從集合論(關(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 語句的“流水線”上進(jìn)行了什么樣的變動。
4、靈活引用表能使 SQL 語句變得更強(qiáng)大
靈活引用表能使 SQL 語句變得更強(qiáng)大。一個簡單的例子就是 JOIN 的使用。
嚴(yán)格的說 JOIN 語句并非是 SELECT 中的一部分,而是一種特殊的表引用語句。
SQL 語言標(biāo)準(zhǔn)中表的連接定義如下:
《table reference》 ::= 《table name》 | 《derived table》 | 《joined table》
就拿之前的例子來說:
FROM a, b
a 可能輸如下表的連接:
a1 JOIN a2 ON a1.id = a2.id
將它放到之前的例子中就變成了:
盡管將一個連接表用逗號跟另一張表聯(lián)合在一起并不是常用作法,但是你的確可以這么做。結(jié)果就是,最終輸出的表就有了 a1+a2+b 個字段了。
在 SQL 語句中派生表的應(yīng)用甚至比表連接更加強(qiáng)大,下面我們就要講到表連接。
我們學(xué)到了什么?
思考問題時,要從表引用的角度出發(fā),這樣就很容易理解數(shù)據(jù)是怎樣被 SQL 語句處理的,并且能夠幫助你理解那些復(fù)雜的表引用是做什么的。
更重要的是,要理解 JOIN 是構(gòu)建連接表的關(guān)鍵詞,并不是 SELECT 語句的一部分。有一些數(shù)據(jù)庫允許在 INSERT 、 UPDATE 、 DELETE 中使用 JOIN 。
5、SQL 語句中推薦使用表連接
我們先看看剛剛這句話:
FROM a, b
高級 SQL 程序員也許給你忠告:盡量不要使用逗號來代替 JOIN 進(jìn)行表的連接,這樣會提高你的 SQL 語句的可讀性,并且可以避免一些錯誤。利用逗號來簡化 SQL 語句有時候會造成思維上的混亂,想一下下面的語句:
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 語句的好處在于:安全。JOIN 和要連接的表離得非常近,這樣就能避免錯誤。
更多連接的方式,JOIN 語句能去區(qū)分出來外連接和內(nèi)連接等。
我們學(xué)到了什么?
記著要盡量使用 JOIN 進(jìn)行表的連接,永遠(yuǎn)不要在 FROM 后面使用逗號連接表。
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 SEMI JOIN
這種連接關(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ī)定說明你何時應(yīng)該使用 IN ,何時應(yīng)該使用 EXISTS ,但是這些事情你還是應(yīng)該知道的:
IN比 EXISTS 的可讀性更好
EXISTS 比IN 的表達(dá)性更好(更適合復(fù)雜的語句)
二者之間性能沒有差異(但對于某些數(shù)據(jù)庫來說性能差異會非常大) 因為使用 INNER JOIN 也能得到書名表中書所對應(yīng)的作者信息,所以很多初學(xué)者機(jī)會認(rèn)為可以通過 DISTINCT 進(jìn)行去重,然后將 SEMI JOIN 語句寫成這樣:
-- Find only those authors who also have books SELECT DISTINCT first_name, last_name FROM author JOIN book ON author.id = book.author_id
這是一種很糟糕的寫法,原因如下:
SQL 語句性能低下:因為去重操作( DISTINCT )需要數(shù)據(jù)庫重復(fù)從硬盤中讀取數(shù)據(jù)到內(nèi)存中。
這么寫并非完全正確:盡管也許現(xiàn)在這么寫不會出現(xiàn)問題,但是隨著 SQL 語句變得越來越復(fù)雜,你想要去重得到正確的結(jié)果就變得十分困難。
ANTI JOIN
這種連接的關(guān)系跟 SEMI JOIN 剛好相反。在 IN 或者 EXISTS 前加一個 NOT 關(guān)鍵字就能使用這種連接。舉個例子來說,我們列出書名表里沒有書的作者:
-- 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 WHEREbook.author_id = author.id)
關(guān)于性能、可讀性、表達(dá)性等特性也完全可以參考 SEMI JOIN。
CROSS JOIN
這個連接過程就是兩個連接的表的乘積:即將第一張表的每一條數(shù)據(jù)分別對應(yīng)第二張表的每條數(shù)據(jù)。我們之前見過,這就是逗號在 FROM 語句中的用法。在實際的應(yīng)用中,很少有地方能用到 CROSS JOIN,但是一旦用上了,你就可以用這樣的 SQL語句表達(dá):
-- Combine every author with every bookauthor CROSS JOIN book
DIVISION DIVISION 的確是一個怪胎。簡而言之,如果 JOIN 是一個乘法運(yùn)算,那么 DIVISION 就是 JOIN 的逆過程。DIVISION 的關(guān)系很難用 SQL 表達(dá)出來,介于這是一個新手指南,解釋 DIVISION 已經(jīng)超出了我們的目的。
我們學(xué)到了什么?
學(xué)到了很多!讓我們在腦海中再回想一下。SQL 是對表的引用, JOIN 則是一種引用表的復(fù)雜方式。但是 SQL 語言的表達(dá)方式和實際我們所需要的邏輯關(guān)系之間是有區(qū)別的,并非所有的邏輯關(guān)系都能找到對應(yīng)的 JOIN 操作,所以這就要我們在平時多積累和學(xué)習(xí)關(guān)系邏輯,這樣你就能在以后編寫 SQL 語句中選擇適當(dāng)?shù)?JOIN 操作了。
7、SQL 中如同變量的派生表
在這之前,我們學(xué)習(xí)到過 SQL 是一種聲明性的語言,并且 SQL 語句中不能包含變量。但是你能寫出類似于變量的語句,這些就叫做派生表:
說白了,所謂的派生表就是在括號之中的子查詢:
-- A derived table FROM (SELECT * FROM author)
需要注意的是有些時候我們可以給派生表定義一個相關(guān)名(即我們所說的別名)。
-- A derived table with an alias FROM(SELECT * FROM author) a
派生表可以有效的避免由于 SQL 邏輯而產(chǎn)生的問題。
舉例來說:如果你想重用一個用 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 WHEREage 》 10000
需要我們注意的是:在有些數(shù)據(jù)庫,以及 SQL :1990 標(biāo)準(zhǔn)中,派生表被歸為下一級——通用表語句( common table experssion)。這就允許你在一個 SELECT 語句中對派生表多次重用。
上面的例子就(幾乎)等價于下面的語句:
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)建一個單獨(dú)的視圖,這樣你就可以在更廣泛的范圍內(nèi)重用這個派生表了。
我們學(xué)到了什么?
我們反復(fù)強(qiáng)調(diào),大體上來說 SQL 語句就是對表的引用,而并非對字段的引用。要好好利用這一點(diǎn),不要害怕使用派生表或者其他更復(fù)雜的語句。
8、SQL 語句中 GROUP BY 是對表的引用進(jìn)行的操作
讓我們再回想一下之前的 FROM 語句:
FROM a, b
現(xiàn)在,我們將 GROUP BY 應(yīng)用到上面的語句中:
GROUP BY A.x, A.y, B.z
上面語句的結(jié)果就是產(chǎn)生出了一個包含三個字段的新的表的引用。我們來仔細(xì)理解一下這句話:當(dāng)你應(yīng)用 GROUP BY 的時候, SELECT 后沒有使用聚合函數(shù)的列,都要出現(xiàn)在 GROUP BY 后面。(譯者注:原文大意為“當(dāng)你是用 GROUP BY 的時候,你能夠?qū)ζ溥M(jìn)行下一級邏輯操作的列會減少,包括在 SELECT 中的列”)。需要注意的是:其他字段能夠使用聚合函數(shù):
SELECT A.x, A.y, SUM(A.z) FROM A GROUP BY A.x, A.y
還有一點(diǎn)值得留意的是:MySQL 并不堅持這個標(biāo)準(zhǔn),這的確是令人很困惑的地方。(譯者注:這并不是說 MySQL 沒有 GROUP BY 的功能)但是不要被 MySQL 所迷惑。GROUP BY 改變了對表引用的方式。你可以像這樣既在 SELECT 中引用某一字段,也在 GROUP BY 中對其進(jìn)行分組。
我們學(xué)到了什么?
GROUP BY,再次強(qiáng)調(diào)一次,是在表的引用上進(jìn)行了操作,將其轉(zhuǎn)換為一種新的引用方式。
9、SQL 語句中的 SELECT 實質(zhì)上是對關(guān)系的映射
我個人比較喜歡“映射”這個詞,尤其是把它用在關(guān)系代數(shù)上。(譯者注:原文用詞為 projection ,該詞有兩層含義,第一種含義是預(yù)測、規(guī)劃、設(shè)計,第二種意思是投射、映射,經(jīng)過反復(fù)推敲,我覺得這里用映射能夠更直觀的表達(dá)出 SELECT 的作用)。一旦你建立起來了表的引用,經(jīng)過修改、變形,你能夠一步一步的將其映射到另一個模型中。
SELECT 語句就像一個“投影儀”,我們可以將其理解成一個將源表中的數(shù)據(jù)按照一定的邏輯轉(zhuǎn)換成目標(biāo)表數(shù)據(jù)的函數(shù)。
通過SELECT語句,你能對每一個字段進(jìn)行操作,通過復(fù)雜的表達(dá)式生成所需要的數(shù)據(jù)。
SELECT 語句有很多特殊的規(guī)則,至少你應(yīng)該熟悉以下幾條:
你僅能夠使用那些能通過表引用而得來的字段;
如果你有 GROUP BY 語句,你只能夠使用 GROUP BY 語句后面的字段或者聚合函數(shù);
當(dāng)你的語句中沒有 GROUP BY 的時候,可以使用開窗函數(shù)代替聚合函數(shù);
當(dāng)你的語句中沒有 GROUP BY 的時候,你不能同時使用聚合函數(shù)和其它函數(shù);
有一些方法可以將普通函數(shù)封裝在聚合函數(shù)中;
……一些更復(fù)雜的規(guī)則多到足夠?qū)懗隽硪黄恼铝?。比如:為何你不能在一個沒有 GROUP BY 的 SELECT 語句中同時使用普通函數(shù)和聚合函數(shù)?(上面的第 4 條)
原因如下:
憑直覺,這種做法從邏輯上就講不通。如果直覺不能夠說服你,那么語法肯定能。SQL : 1999 標(biāo)準(zhǔn)引入了 GROUPING SETS,SQL:2003 標(biāo)準(zhǔn)引入了 group sets : GROUP BY() 。無論什么時候,只要你的語句中出現(xiàn)了聚合函數(shù),而且并沒有明確的 GROUP BY 語句,這時一個不明確的、空的 GROUPING SET 就會被應(yīng)用到這段 SQL 中。因此,原始的邏輯順序的規(guī)則就被打破了,映射(即 SELECT )關(guān)系首先會影響到邏輯關(guān)系,其次就是語法關(guān)系。(譯者注:這段話原文就比較艱澀,可以簡單理解如下:在既有聚合函數(shù)又有普通函數(shù)的 SQL 語句中,如果沒有 GROUP BY 進(jìn)行分組,SQL 語句默認(rèn)視整張表為一個分組,當(dāng)聚合函數(shù)對某一字段進(jìn)行聚合統(tǒng)計的時候,引用的表中的每一條 record 就失去了意義,全部的數(shù)據(jù)都聚合為一個統(tǒng)計值,你此時對每一條 record 使用其它函數(shù)是沒有意義的)。糊涂了?是的,我也是。我們再回過頭來看點(diǎn)淺顯的東西吧。
我們學(xué)到了什么?
SELECT 語句可能是 SQL 語句中最難的部分了,盡管他看上去很簡單。其他語句的作用其實就是對表的不同形式的引用。而 SELECT 語句則把這些引用整合在了一起,通過邏輯規(guī)則將源表映射到目標(biāo)表,而且這個過程是可逆的,我們可以清楚的知道目標(biāo)表的數(shù)據(jù)是怎么來的。
想要學(xué)習(xí)好 SQL 語言,就要在使用 SELECT 語句之前弄懂其他的語句,雖然 SELECT 是語法結(jié)構(gòu)中的第一個關(guān)鍵詞,但它應(yīng)該是我們最后一個掌握的。
10、SQL 語句中的幾個簡單的關(guān)鍵詞:DISTINCT , UNION , ORDER BY 和 OFFSET
在學(xué)習(xí)完復(fù)雜的 SELECT 之后,我們再來看點(diǎn)簡單的東西:
集合運(yùn)算( set operation):集合運(yùn)算主要操作在于集合上,事實上指的就是對表的一種操作。從概念上來說,他們很好理解:
DISTINCT 在映射之后對數(shù)據(jù)進(jìn)行去重
UNION 將兩個子查詢拼接起來并去重
UNION ALL 將兩個子查詢拼接起來但不去重
EXCEPT 將第二個字查詢中的結(jié)果從第一個子查詢中去掉
INTERSECT 保留兩個子查詢中都有的結(jié)果并去重
排序運(yùn)算( ordering operation):
排序運(yùn)算跟邏輯關(guān)系無關(guān)。這是一個 SQL 特有的功能。排序運(yùn)算不僅在 SQL 語句的最后,而且在 SQL 語句運(yùn)行的過程中也是最后執(zhí)行的。使用 ORDER BY 和 OFFSET…FETCH 是保證數(shù)據(jù)能夠按照順序排列的最有效的方式。其他所有的排序方式都有一定隨機(jī)性,盡管它們得到的排序結(jié)果是可重現(xiàn)的。OFFSET…SET是一個沒有統(tǒng)一確定語法的語句,不同的數(shù)據(jù)庫有不同的表達(dá)方式,如 MySQL 和 PostgreSQL 的 LIMIT…OFFSET、SQL Server 和 Sybase 的 TOP…START AT 等。
責(zé)任編輯:PSY
原文標(biāo)題:10 個簡單步驟,帶你完全理解 SQL
文章出處:【微信公眾號:數(shù)據(jù)分析與開發(fā)】歡迎添加關(guān)注!文章轉(zhuǎn)載請注明出處。
-
編程
+關(guān)注
關(guān)注
88文章
3521瀏覽量
93276 -
SQL
+關(guān)注
關(guān)注
1文章
750瀏覽量
43900 -
數(shù)據(jù)庫
+關(guān)注
關(guān)注
7文章
3712瀏覽量
64028 -
select
+關(guān)注
關(guān)注
0文章
28瀏覽量
3891
原文標(biāo)題:10 個簡單步驟,帶你完全理解 SQL
文章出處:【微信號:DBDevs,微信公眾號:數(shù)據(jù)分析與開發(fā)】歡迎添加關(guān)注!文章轉(zhuǎn)載請注明出處。
發(fā)布評論請先 登錄
相關(guān)推薦
評論