簡單十步讓你全面理解SQL

tianya_2011發表於2015-02-03
SQL

很多程式設計師認為SQL是一頭難以馴服的野獸。它是為數不多的宣告性語言之一，也正因為這樣，其展示了完全不同於其他表現形式、命令式語言、面嚮物件語言甚至函數語言程式設計語言（雖然有些人覺得SQL還是有些類似功能）。

我每天都寫SQL，我的開源軟體JOOQ中也包含SQL。因此我覺得有必要為還在為此苦苦掙扎的你呈現SQL的優美！下面的教程面向於：

  • 已經使用過但沒有完全理解SQL的讀者；

  • 已經差不多瞭解SQL但從未真正考慮過它的語法的讀者；

  • 想要指導他人學習SQL的讀者；

本教程將重點介紹SELECT語句。其他DML語句將在另一個教程中在做介紹。


1、SQL是宣告性語言

首先你需要思考的是宣告性。你唯一需要做的只是宣告你想獲得結果的性質，而不需要考慮你的計算機怎麼算出這些結果的。

1
2
3
SELECT first_name, last_name
FROM employees
WHERE salary > 100000

這很容易理解，你無須關心員工的身份記錄從哪來，你只需要知道誰有著不錯的薪水。


從中我們學到了什麼呢？

那麼如果它是這樣的簡單，會出現什麼問題嗎？問題就是我們大多數人會直觀地認為這是指令式程式設計。如：“機器，做這，再做那，但在這之前，如果這和那都發生錯誤，那麼會執行一個檢測”。這包括在變數中儲存臨時的編寫迴圈、迭代、呼叫函式等等結果。

把那些都忘了吧，想想怎麼去宣告，而不是怎麼告訴機器去計算。


2、SQL語法不是“有序的”

常見的混亂源於一個簡單的事實，SQL語法元素並不會按照它們的執行方式排序。語法順序如下：

  • SELECT [DISTINCT]

  • FROM

  • WHERE

  • GROUP BY

  • HAVING

  • UNION

  • ORDER BY

為簡單起見，並沒有列出所有SQL語句。這個語法順序與邏輯順序從根本上就不同，即執行順序： 

  • FROM

  • WHERE

  • GROUP BY

  • HAVING

  • SELECT

  • DISTINCT

  • UNION

  • ORDER BY


這有三點需要注意：

  1、第一句是FROM，而不是SELECT。首先是將資料從磁碟載入到記憶體中，以便對這些資料進行操作。

  2、SELECT是在其他大多數語句後執行，最重要的是，在FROM和GROUP BY之後。重要的是要理解當你覺得你可以從WHERE語句中引用你定義在SELECT語句當中的時候。以下是不可行的：

1
2
3
4
5
SELECTA.x + A.y ASz
 
FROMA
 
WHER Ez = 10 -- z is not available here!

如果你想重用z，您有兩種選擇。要麼重複表示式: 

1
2
3
4
5
SELECTA.x + A.y ASz
 
FROMA
 
WHERE(A.x + A.y) = 10

或者你使用派生表、公用表表示式或檢視來避免程式碼重複。可參閱下文示例。

   3、在語法和邏輯順序裡，UNION都是放在ORDER BY之前，很多人認為每個UNION子查詢都可以進行排序，但根據SQL標準和大多數的SQL方言，並不是真的可行。雖然一些方言允許子查詢或派生表排序，但卻不能保證這種順序能在UNION操作後保留。

需要注意的是，並不是所有的資料庫都以相同的形式實現，例如規則2並不完全適用於MySQL、PostgreSQL和SQLite上。


 從中我們學到了什麼呢？

要時刻記住SQL語句的語法順序和邏輯順序來避免常見的錯誤。如果你能明白這其中的區別，就能明確知道為什麼有些可以執行有些則不能。

如果能設計一種在語法順序上實際又體現了邏輯順序的語言就更好了，因為它是在微軟的LINQ上實現的。


3、SQL是關於資料表引用的 

因為語法順序和邏輯順序的差異，大多數初學者可能會誤認為SQL中列的值是最重要的。其實並非如此，最重要的是資料表引用。

該SQL標準定義了FROM語句，如下：

1
::= FROM [ ... ]

FROM語句的"output"是所有表引用的結合程度組合表引用。讓我們慢慢地消化這些。 

1
FROM a, b

上述產生一個a+b度的組合表引用，如果a有3列和b有5列，那麼"輸出表"將有8（3+5）列。

包含在這個組合表引用的記錄是交叉乘積/笛卡兒積的a×b。換句話說，每一條a記錄都會與每一條b記錄相對應。如果a有3個記錄和b有5條記錄,然後上面的組合表引用將產生15條記錄(3×5)。

在WHERE語句篩選後，GROUP BY語句中"output"是"fed"/"piped"，它已轉成新的"output"，我們會稍後再去處理。

如果我們從關係代數/集合論的角度來看待這些東西，一個SQL表是一個關係或一組元素組合。每個SQL語句將改變一個或幾個關係，來產生新的關係。

從中我們學到了什麼呢？

一直從資料表引用角度去思考，有助於理解資料怎樣透過你的sql語句流水作業的。


4、SQL資料表引用可以相當強大

表引用是相當強大的東西。舉個簡單的例子，JOIN關鍵字其實不是SELECT語句的一部分，但卻是"special"表引用的一部分。連線表，在SQL標準中有定義（簡化的）：

1
2
3
4
5
6
7
::=
 
 
|
 
|

如果我們又拿之前的例子來分析： 

1
FROMa, b

a可以作為一個連線表，如：

1
a1 JOINa2 ONa1.id = a2.id

這擴充套件到前一個表示式，我們會得到：

1
FROMa1 JOINa2 ONa1.id = a2.id, b

雖然結合了資料表引用語法與連線表語法的逗號分隔表讓人很無語，但你肯定還會這樣做的。結果，結合資料表引用將有a1+a2+b度。

派生表甚至比連線表更強大，我們接下來將會說到。


 從中我們學到了什麼呢？

要時時刻刻考慮表引用，重要的是這不僅讓你理解資料怎樣透過你的sql語句流水作業的，它還將幫助你瞭解複雜表引用是如何構造的。

而且，重要的是，瞭解JOIN是構造連線表的關鍵字。不是的SELECT語句的一部分。某些資料庫允許JOIN在插入、更新、刪除中使用。


5、應使用SQL JOIN的表，而不是以逗號分隔表 

前面，我們已經看到這語句： 

1
FROM a, b

高階SQL開發人員可能會告訴你，最好不要使用逗號分隔的列表，並且一直完整的表達你的JOINs。這將有助於改進你的SQL語句的可讀性從而防止錯誤出現。

一個非常常見的錯誤是忘記某處連線謂詞。思考以下內容：

1
2
3
4
5
6
7
8
9
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，並且永遠不在FROM語句中使用逗號分隔表引用。 


6、SQL的不同型別的連線操作

連線操作基本上有五種

  • EQUI JOIN

  • SEMI JOIN

  • ANTI JOIN

  • CROSS JOIN

  • DIVISION

這些術語通常用於關係代數。對上述概念，如果他們存在，SQL會使用不同的術語。讓我們仔細看看:


EQUI JOIN（同等連線）

這是最常見的JOIN操作。它有兩個子操作:

  • INNER JOIN(或者只是JOIN)

  • OUTER JOIN(可以再次拆分為LEFT、RIGHT、FULL OUTER JOIN)

例子是其中的區別最好的解釋:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 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 JOINbook ONauthor.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 LEFTOUTERJOINbook ONauthor.id = book.author_id


SEMI JOIN（半連線）

這種關係的概念在SQL中用兩種方式表達：使用IN謂詞或使用EXISTS謂語。"Semi"是指在拉丁語中的"half"。這種型別的連線用於連線只有"half"的表引用。再次考慮上述加入的作者和書。讓我們想象，我們想要作者/書的組合,但只是那些作者實際上也有書。然後我們可以這樣寫:

1
2
3
4
5
6
7
8
9
10
11
-- Using IN
 
FROM author
 
WHERE author.id IN(SELECTbook.author_id FROMbook)
 
-- Using EXISTS
 
FROM author
 
WHERE EXISTS (SELEC T1 FROM book WHERE book.author_id = author.id)

雖然不能肯定你到底是更加喜歡IN還是EXISTS，而且也沒有規則說明，但可以這樣說：

  • IN往往比EXISTS更具可讀性；

  • EXISTS往往比IN更富表現力（如它更容易表達複雜的半連線）；

  • 一般情況下效能上沒有太大的差異，但，在某些資料庫可能會有巨大的效能差異。

因為INNER JOIN有可能只產生有書的作者，因為很多初學者可能認為他們可以使用DISTINCT刪除重複項。他們認為他們可以表達一個像這樣的半聯接：

1
2
3
4
5
-- Find only those authors who also have books
 
SELECT DISTINCT first_name, last_name
 
FROM author

這是非常不好的做法，原因有二：

  • 它非常慢，因為該資料庫有很多資料載入到記憶體中，只是要再刪除重複項；

  • 它不完全正確，即使在這個簡單的示例中它產生了正確的結果。但是，一旦你JOIN更多的表引用，你將很難從你的結果中正確刪除重複項。

更多的關於DISTINCT濫用的問題，可以訪問這裡的部落格。


ANTI JOIN（反連線）

這個關係的概念跟半連線剛好相反。您可以簡單地透過將NOT關鍵字新增到IN或EXISTS中生成它。在下例中，我們選擇那些沒有任何書籍的作者：

1
2
3
4
5
6
7
8
9
10
11
-- Using IN
 
FROM author
 
WHERE author.id NOTIN(SELECT book.author_id FROM book)
 
-- Using EXISTS
 
FROM author
 
WHERENOTEXISTS (SELECT1 FROMbook WHEREbook.author_id = author.id)

同樣的規則對效能、可讀性、表現力都適用。然而，當使用NOT IN時對NULLs會有一個小警告，這個問題有點超出本教程範圍。


CROSS JOIN（交叉連線）

結合第一個表中的內容和第二個表中的內容，引用兩個join表交叉生成一個新的東西。我們之前已經看到，這可以在FROM語句中透過逗號分隔表引用來實現。在你確實需要的情況下，可以在SQL語句中明確地寫一個CROSS JOIN。

1
2
3
-- Combine every author with every book
 
author CROSS JOIN book


DIVISION（除法）

關係分割就是一隻真正的由自己親自餵養的野獸。簡而言之，如果JOIN是乘法，那麼除法就是JOIN的反義詞。在SQL中，除法關係難以表達清楚。由於這是一個初學者的教程，解釋這個問題超出了我們的教程範圍。當然如果你求知慾爆棚，那麼就看這裡，還有。


從中我們學到了什麼呢？

讓我們把前面講到的內容再次牢記於心。SQL是表引用，連線表是相當複雜的表引用。但關係表述和SQL表述還是有點區別的，並不是所有的關係連線操作都是正規的SQL連線操作。對關係理論有一點實踐與認識，你就可以選擇JOIN正確的關係型別並能將其轉化為正確的SQL。


7、SQL的派生表就像表的變數

前文，我們已經瞭解到SQL是一種宣告性語言，因此不會有變數。（雖然在一些SQL語句中可能會存在）但你可以寫這樣的變數，那些野獸一般的表被稱為派生表。

派生表只不過是包含在括號裡的子查詢。

1
2
3
-- A derived table
 
FROM(SELECT* FROM author)

需要注意的是，一些SQL方言要求派生表有一個關聯的名字（也被稱為別名）。

1
2
3
-- A derived table with an alias
 
FROM(SELECT* FROMauthor) a

當你想規避由SQL子句邏輯排序造成的問題時，你會發現派生表可以用帥呆了來形容。例如，如果你想在SELECT和WHERE子句中重用一個列表示式，只寫（Oracle方言）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 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

注意，一些資料庫和SQL:1999標準裡已將派生錶帶到下一級別，引入公共表表示式。這將允許你在單一的SQL SELECT中重複使用相同的派生表。上面的查詢將轉化為類似這樣的：

1
2
3
4
5
6
7
8
9
10
11
12
13
WITHa AS(
 
SELECT first_name, last_name, current_date- date_of_birth age
 
FROM author
 
)
 
SELECT*
 
FROM a
 
WHERE age > 10000

很明顯，對廣泛重用的常見SQL子查詢，你也可以灌輸具體"a"到一個獨立檢視中。想要了解更多就看。


 從中我們學到了什麼呢？

再溫習一遍，SQL主要是關於表引用，而不是列。好好利用這些表引用。不要害怕寫派生表或其他複雜的表引用。


8、SQL GROUP BY轉換之前的表引用

讓我們重新考慮我們之前的FROM語句：

1
FROM a, b

現在,讓我們來應用一個GROUP BY語句到上述組合表引用

1
GROUP BY A.x, A.y, B.z

這會產生一個只有其餘三個列(!)的新的表引用。讓我們再消化一遍，如果你用了GROUP BY，那麼你在所有後續邏輯條款——包括選擇中減少可用列的數量。這就是為什麼你只可以從SELECT語句中的GROUP BY語句引用列語法的原因。

  • 請注意，其他列仍然可能可作為聚合函式的引數：
1
2
3
SELECT A.x, A.y, SUM(A.z)
FROM A
GROUP BY A.x, A.y
  • 值得注意並很不幸的是，MySQL不遵守這一標準，只會造成混亂。不要陷入MySQL的把戲。GROUP BY轉換表引用，因此你可以只引用列也引用GROUP BY語句。


 從中我們學到了什麼呢？

GROUP BY，在表引用上操作，將它們轉換成一個新表。


9、SQL SELECT在關係代數中被稱為投影

當它在關係代數中使用時，我個人比較喜歡用“投影”一詞中。一旦你生成你的表引用、過濾、轉換它，你可以一步將它投影到另一個表中。SELECT語句就像一個投影機。表函式利用行值表示式將之前構造的表引用的每個記錄轉化為最終結果。

在SELECT語句中，你終於可以在列上操作，建立複雜的列表示式作為記錄/行的一部分。

有很多關於可用的表示式，函式性質等的特殊規則。最重要的是，你應該記住這些：

1、你只能使用從“output”表引用產生的列引用；

2、如果你有GROUP BY語句，你只可能從該語句或聚合函式引用列；

3、當你沒有GROUP BY語句時，你可以用視窗函式替代聚合函式；

4、如果你沒有GROUP BY語句，你就不能將聚合函式與非聚合函式結合起來；

5、這有一些關於在聚合函式包裝常規函式的規則，反之亦然；

6、還有……

嗯，這有很多複雜的規則。他們可以填補另一個教程。例如，之所以你不能將聚合函式與非聚合函式結合起來投影到沒有GROUP BY的SELECT語句中是因為：

1、憑直覺，沒有任何意義。

2、對一個SQL初學者來說，直覺還是毫無幫助的，語法規則則可以。SQL:1999年引入了分組集，SQL：2003引入了空分組集GROUP BY()。每當存在沒有顯式GROUP BY語句的聚合函式時就會應用隱式的空分組集（規則2）。因此，最初關於邏輯順序的那個規則就不完全正確了，SELECT的投影也會影響前面的邏輯結果，但語法語句GROUP BY卻不受影響。

是不是有點迷糊？其實我也是，讓我們看一些簡單的吧。


 從中我們學到了什麼呢？

在SQL語句中，SELECT語句可能是最複雜的語句之一，即使它看起來是那麼的簡單。所有其他語句只不過是從這個到另一個表引用的的輸送管道。透過將它們完全轉化，後期地對它們應用一些規則，SELECT語句完完全全地攪亂了這些表引用的美。

為了瞭解SQL，重要的是要理解其他的一切，都要嘗試在SELECT之前解決。即便SELECT在語法順序中排第一的語句，也應該將它放到最後。


10.相對簡單一點的SQL DISTINCT,UNION,ORDER BY,和OFFSET

看完複雜的SELECT之後，我們看回一些簡單的東西。

  • 集合運算（DISTINCT和UNION）

  • 排序操作（ORDER BY,OFFSET..FETCH）

 
 集合運算

集合運算在除了表其實沒有其他東西的“集”上操作。嗯，差不多是這樣，從概念上講，它們還是很容易理解的：

  • DISTINCT投影后刪除重複項。

  • UNION求並集，刪除重複項。

  • UNION ALL求並集，保留重複項。

  • EXCEPT求差集（在第一個子查詢結果中刪除第二個子查詢中也含有的記錄刪除），刪除重複項。

  • INTERSECT求交集（保留所有子查詢都含有的記錄），刪除重複項。

所有這些刪除重複項通常是沒有意義的，很多時候，當你想要連線子查詢時，你應該使用UNION ALL。


 排序操作

排序不是一個關係特徵，它是SQL僅有的特徵。在你的SQL語句中，它被應用在語法排序和邏輯排序之後。保證可以透過索引訪問記錄的唯一可靠方法是使用ORDER BY a和OFFSET..FETCH。所有其他的排序總是任意的或隨機的，即使它看起來像是可再現的。

OFFSET..FETCH是唯一的語法變體。其他變體包括MySQL'和PostgreSQL的LIMIT..OFFSET，或者SQL Server和Sybase的TOP..START AT（）。


 讓我們開始應用吧

跟其他每個語言一樣，要掌握SQL語言需要大量的實踐。上述10個簡單的步驟將讓你每天編寫SQL時更有意義。另一方面，你也可以從常見的錯誤中學習到更多。下面的兩篇文章列出許多Java（和其他）開發者寫SQL時常見的錯誤：

· 10 Common Mistakes Java Developers Make when Writing SQL

· 10 More Common Mistakes Java Developers Make when Writing SQL

來源宣告：本文來自Tech.Pro的博文《》，由IDF實驗室陳民慧翻譯。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25373498/viewspace-1424682/，如需轉載，請註明出處，否則將追究法律責任。

相關文章