這篇文章主要梳理了 SQL 的基礎用法，會涉及到以下方面內容：
- SQL大小寫的規範
- 資料庫的型別以及適用場景
- SELECT 的執行過程
- WHERE 使用規範
- MySQL 中常見函式
- 子查詢分類
- 如何選擇合適的 EXISTS 和 IN 子查詢
瞭解 SQL
SQL 是我們用來最長和資料打交道的方式之一，如果按照功能劃分可分為如下 4 個部分：
- DDL，資料定義語言。定義資料庫物件，資料表，資料列。也就是，對資料庫和表結構進行增刪改操作。
- DML，資料操作語言。對資料表的增刪改。
- DCL，資料控制語言。定義訪問許可權和安全級別。
- DQL，資料查詢語言。用來查詢資料。
平時在編寫 SQL 時，可能發現許多 SQL 大小寫不統一，雖然不會影響 SQL 的執行結果，但保持統一的書寫規範，是提高效率的關鍵，通常遵循如下的原則：
- 表名，表別名，欄位名，欄位別名等用小寫。
- SQL 保留字，函式名，繫結變數等用大寫。
- 資料表，欄位名採用下劃線命名。
目前排名較前的 DBMS：
- 關係型資料庫：建立在關係模型上的資料庫，在建表時，通常先設計 ER 圖表示之間的關係。
- 鍵值型資料庫：以 key-value 的形式儲存資料，優點是查詢速度快，缺點是無法向關係型資料庫一樣使用如 WHERE 等的過濾條件。常見場景是作為內容快取。
- 文件型資料庫，在儲存時以文件作為處理資訊的基本單位。
- 搜尋引擎：針對全文檢索而設計。核心原理是 “倒排索引”。
- 列式資料庫：相對於如 MySQL 等行式儲存的資料庫，是以列將資料存在資料庫中，由於列具有相同的資料型別，所以可以更好的壓縮，從而減低系統的 I/O，適用於分散式檔案系統，但功能相對有限。
- 圖形資料庫，利用圖的資料結構儲存實體之間的關係。比如社交網路中人與人的關係，資料模型為節點和邊來實現。
認識 SELECT
SELECT 一般是在學習 SQL 接觸的第一個關鍵字，基礎的內容就是不提了，這裡整理常用的規範：
起別名
SELECT name AS n FROM student
查詢常數, 增加一列固定的常數列：
SELECT '學生資訊' as student_info, name FROM student
去重重複行
SELECT DISTINCT age FROM student
需要注意的是 DISTINCT 是對後面的所有列進行去重, 下面這種情況就會對 age 和 name 的組合進行去重。
SELECT DISTINCT age,name FROM student
排序資料,ASC 代表升序，DESC 代表降序
如先按照 name 排序，name 相等的情況下按照 age 排序。
SELECT DISTINCT age FROM student ORDERY BY name,age DESC
限制返回的數量
SELECT DISTINCT age FROM student ORDERY BY name DESC LIMIT 5
SELECT 的執行順序
瞭解了 SELECT 的執行順序，才能更好地寫出更有效率的 SQL。
對於 SELECT 順序有兩個原則：
-
關鍵字的順序不能顛倒：
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... -
SELECT 會按照如下順序執行：
FROM > WHERE > GROUP BY > HAVING > SELECT的欄位 > DISTINCT > ORDER BY > LIMIT
SELECT DISTINCT student_id, name, count(*) as num #順序5
FROM student JOIN class ON student.class_id = class.class_id #順序1
WHERE age > 18 #順序2
GROUP BY student.class_id #順序3
HAVING num > 2 #順序4
ORDER BY num DESC #順序6
LIMIT 2 #順序7
在逐一分析下這個過程前，我們需要知道在上面的每一個步驟中都會產生一個虛擬表，然後將這個虛擬表作為下一個步驟中作為輸入，但這一過程對我們來說是不可見的：
- 從 FROM 語句開始，對 student 和 class 表進行 CROSS JOIN 笛卡爾積運算，得到虛擬表 vt 1-1；
- 通過 ON 篩選，在 vt1-1 的基礎上進行過濾然後得到表 vt 1-2；
- 新增外部行。如使用左連線，右連線和全連線時，就會涉及到外部行，會在 vt1-2 的基礎上增加外部行，得到 vt1-3。
- 如果超過兩張表，就會重複上面的步驟。
- 在拿到最終的 vt1 的表資料後，會執行 WHERE 後面的過濾階段，得到表 vt2.
- 接著到 GROUP 階段，進行分組得到 vt3.
- 接著到 HAVING 階段，對分組的資料進行過濾，得到 vt4.
- 後面進入 SELECT 階段，提取需要的欄位，得到 vt5-1，接著通過 DISTINCT 階段，過濾到重複的行，得到 vt5-2.
- 然後對指定的欄位進行排序，進入 ORDER BY 階段，得到 vt6.
- 最後在 LIMIT 階段，取出指定的行，對應 vt7，也就是最後的結果。
如果涉及到函式的計算比如 sum() 等，會在 GROUP BY分組後，HAVING 分組前，進行聚集函式的計算。
涉及到表示式計算，如 age * 10 等，會在 HAVING 階段後，SELECT 階段前進行計算。
通過這裡，就可以總結出提高 SQL 效率的第一個方法：
- 使用 SELECT 時指定明確的列來代替 SELECT * . 從而減少網路的傳輸量。
使用 WHERE 進行過濾
使用 WHERE 篩選時，常有通過比較運算子，邏輯運算子，萬用字元三種方式。
對於比較運算子，常用的運算子如下表。
對於邏輯運算子來說，可以將多個比較執行符連線起來，進行多條件的篩選，常用的運算子如下：
需要注意的是，當 AND 和 OR 同時出現時，AND 的優先順序更高會先被執行。當如果存在 () 的話，則括號的優先順序最高。
使用萬用字元過濾：
like：（%）代表零個或多個字元，（_）只代表一個字元
函式
和程式語言中的定義的函式一樣，SQL 同樣定義了一些函式方便使用，比如求和，平均值，長度等。
常見的函式主要分為如下四類，分類的原則是根據定義列時的資料型別：
- 算術函式：
- 字串函式
需要注意的是，在使用字串比較日期時，要使用 DATE 函式比較。
- 日期函式
- 轉換函式：
CAST 函式在轉換資料型別時，不會四捨五入，如果原數值是小數，在轉換到整數時會報錯。
在轉換時可以使用 DECIMAL(a,b) 函式來規定小數的精度，比如 DECIMAL(8,2) 表示精度為 8 位 - 小數加整數最多 8 位。小數後面最多為 2 位。
然後通過 SELECT CAST(123.123 AS DECIMAL(8,2)) 來轉換。
聚集函式
通常情況下，我們會使用聚集函式來彙總表的資料，輸入為一組資料，輸出為單個值。
常用的聚集函式有 5 個：
其中 COUNT 函式需要額外注意，具體的內容可以參考這篇。
如何進行分組
在統計結果時，往往需要對資料按照一定條件進行分組，對應就是 GROUP BY 語句。
比如統計每個班級的學生人數：
SELECT class_id, COUNT(*) as student_count FROM student \
GROUP BY class_id;
GROUP BY 後也可接多個列名，進行分組，比如按照班級和性別分組：
SELECT class_id, sex, COUNT(*) as student_count FROM \
student GROUP BY class_id, sex;
HAVING 過濾和 WHERE 的區別
和 WHERE 一樣，可以對分組後的資料進行篩選。區別在於 WHERE 適用於資料行，HAVING 用於分組。
而且 WHERE 支援的操作，HAVING 也同樣支援。
比如可以篩選大於2人的班級：
SELECT class_id, COUNT(*) as student_count FROM student \
GROUP BY class_id \
HAVING student_count > 20;
子查詢
在一些更為複雜的情況中，往往會進行巢狀的查詢，比如在獲取結果後，該結果作為輸入，去獲取另外一組結果。
在 SQL 中，查詢可以分為關聯子查詢和非關聯子查詢。
假設有如下的表結構：
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '',
`age` int(3) NOT NULL,
`sex` varchar(10) NOT NULL DEFAULT '',
`class_id` int(11) NOT NULL COMMENT '班級ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of Student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '胡一', 13, '男', '1');
INSERT INTO `student` VALUES ('3', '王阿', 11, '女', '1');
INSERT INTO `student` VALUES ('5', '王琦', 12, '男', '1');
INSERT INTO `student` VALUES ('7', '劉偉', 11, '女', '1');
INSERT INTO `student` VALUES ('7', '王意識', 11, '女', '2');
-- ----------------------------
DROP TABLE IF EXISTS `student_activities`;
CREATE TABLE `student_activities` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '',
`stu_id` int(11) NOT NULL COMMENT '班級ID',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `student_activities` VALUES ('1', '博物館', 1);
INSERT INTO `student_activities` VALUES ('3, '春遊', 3);
非關聯子查詢
子查詢從資料表中查詢了資料結果，如果這個資料結果只執行一次，然後這個資料結果作為主查詢的條件接著執行。
這裡想要查詢和胡一相同班級的同學名稱：
SELECT name FROM student WHERE class_id = \
(SELECT class_id FROM student WHERE name='胡一')
這裡先查到胡一的班級，只有一次查詢，再根據該班級查詢學生就是非關聯子查詢。
關聯子查詢
如果子查詢需要執行多次，即採用迴圈的方式，先從外部查詢開始，每次都傳入子查詢進行查詢，然後再將結果反饋給外部
再舉個例子, 比如查詢比每個班級中比平均年齡大的學生姓名資訊：
SELECT name FROM student as s1 WHERE age >
(SELECT AVG(age) FROM student as s2 where s1.class_id = s2.class_id)
這裡根據每名同學的班級資訊，查詢出對應班級的平均年齡，然後做判斷。子查詢每次執行時，都需要根據外部的查詢然後進行計算。這樣的子查詢就是關聯子查詢。
EXISTS 子查詢
在關聯子查詢中，常會和 EXISTS 一起使用。用來判斷條件是否滿足，滿足的話為 True，不滿足為 False。
比如查詢參加過學校活動的學生名稱：
SELECT NAME FROM student as s where \
EXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)
同樣 NOT EXISTS 就是不存在的意思，滿足為 FALSE , 不滿足為 True.
比如查詢沒有參加過學校活動的學生名稱：
SELECT NAME FROM student as s where \
NOT EXISTS(SELECT stu_id FROM student_activities as sa where sa.stu_id=s.id)
集合比較子查詢
可以在子查詢中，使用集合操作符，來比較結果。
還是上面查詢參加學校活動的學生名字的子查詢, 同樣可以使用 IN：
SELECT name FROM student WHERE id IN (SELECT stu_id FROM student_activities)
EXISTS 和 IN 的區別
既然 EXISTS 和 IN 都能實現相同的功能，那麼他們之間的區別是什麼？
現在假設我們有表 A 和 表 B，其中 A，B 都有欄位 cc，並對 cc 建立了 b+ 索引，其中 A 表 n 條記錄，B 表 m 條索引。
將其模式抽象為：
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
對於 EXISTS 來說，會先對外表進行逐條迴圈，每次拿到外表的結果後，帶入子查詢的內表中，去判斷該值是否存在。
虛擬碼類似於下面：
for i in A
for j in B
if j.cc == i.cc:
return result
首先先看外表 A，每一條都需要遍歷到，所以需要 n 次。內表 B，在查詢時由於使用索引進而查詢效率變成 log(m) B+ 的樹高，而不是 m。
進而總效率：n * log(m)
所以對於 A 表的數量明顯小於 B 時，推薦使用 EXISTS 查詢。
再看 IN ，會先對內表 B 進行查詢，然後用外表 A 進行判斷，虛擬碼如下：
for i in B
for j in A
if j.cc == i.cc:
return result
由於需要首先將內表所有資料查出，所以需要的次數就是 m. 再看外表 A ，由於使用了 cc 索引，可將 n 簡化至 log(n), 也就是 m * log(n).
所以對於 A 表的資料明顯大於 B 表時，推薦使用 IN 查詢。
總結一下對於 IN 和 EXISTS時，採用小表驅動大表的原則。
這裡再擴充套件下 NOT EXISTS 和 NOT IN 的區別：
SELECT * FROM A WHERE cc NOT IN (SELECT cc FROM B)
SELECT * FROM A WHERE NOT EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
對於 NOT EXITS 來說，和 EXISTS 一樣，對於內表可以使用 cc 的索引。適用於 A 表小於 B 表的情況。
但對於 NOT IN 來說，和 IN 就有區別了，由於 cc 設定了索引 cc IN (1, 2, 3) 可以轉換成 WHERE cc=1 OR cc=2 OR cc=3 , 是可以正常走 cc 索引的。但對於 NOT IN 也就是轉化為 cc!=1 OR cc!=2 OR cc!=3 這時由於是不等號查詢，是無法走索引的，進而全表掃描。
也就是說，在設定索引的情況下 NOT EXISTS 比 NOT IN 的效率高。
但對於沒有索引的情況，IN 和 OR 是不同的：
一、操作不同
1、in：in是把父查詢表和子查詢表作hash連線。
2、or：or是對父查詢表作loop迴圈，每次loop迴圈再對子查詢表進行查詢。
二、適用場景不同
1、in：in適合用於子查詢表資料比父查詢表資料多的情況。
2、or：or適合用於子查詢表資料比父查詢表資料少的情況。
三、效率不同
1、in：在沒有索引的情況下，隨著in後面的資料量越多，in的執行效率不會有太大的下降。
2、or：在沒有索引的情況下，隨著or後面的資料量越多，or的執行效率會有明顯的下降。
總結
這篇文章中主要歸納了一些 SQL 的基礎知識：
在使用 SELECT 查詢時，通過顯式指定列名，來減少 IO 的傳輸，從而提高效率。
並且需要注意 SELECT 的查詢過程會從 FROM 後開始到 LIMIT 結束，理解了整體的流程，可以讓我們更好的組織 SQL.
之後詳細介紹了 WHERE 進行過濾的操作符和常用的函式，這裡要注意在比較時間時要使用 DATE 函式，以及如何對資料進行分組和過濾。
最後著重介紹了子查詢，IN 和 EXISTS 的適用場景。