【寫在前面】
作為一個無推廣的小博主，之前的文章收到了很多讀者的熱愛，在此多謝大家的支持。最近發(fā)現國內剽竊現象很嚴重啊……如果您對我的文章有興趣，想要轉載請先經過我的同意，要不然打洗你！

最近面試了一些Data Engineer和Data Scientist的求職者，考了一些SQL的問題。（沒錯有些時候還是要用SQL的。）近兩年學術界和業(yè)界都很浮躁，很多人這tensor那reinforcement的不離嘴，卻把最基礎的SQL忘得干凈。所以本文總結了一下SQL的常見考點和常用函數。筆者在工作中SQL用得很少，多用Hive等NoSQL，但是其實都是萬變不離其宗。面試官考的還是普通的SQL。

本文僅供學習交流使用，不用于商業(yè)目的，轉載須注明出處。

有些公司（比如Facebook）會考又老又不好用的MySQL。所以本文就以最不好用的MySQL為例。個人覺得MySQL最討厭之處在于每一個derived table（中間產生的臨時表）都要有一個alias，還有就是沒有window function……

本文寫得是一般面試考SQL逃不出的關鍵點（標題部分有【高頻】為面試容易考到的知識點——根據北美面試與被面試的經驗），而不是基礎SQL教程。剛畢業(yè)的學生往往沒有接觸過SQL，應聘數據類的崗位往往會擔心會不會考SQL。我建議還是自己學習一下再去面試，一是因為在校生沒有工作經驗，簡歷和面試內容很單薄，二是SQL是面試中最容易又最逃不過的一個環(huán)節(jié)了。由此看來學習SQL投入少、產出高，何樂而不為。

如果你連subquery（子查詢）、join（表聯(lián)合）、having還不知道，那么建議先去看一些基礎的入門教程。以前我一般就推薦Teach Yourself SQL in 21 Days（《SQL21日自學通》）——這本書前8天的內容能涵蓋大部分的面試內容，看起來非?？?。但是后來我覺得這本書里寫到的知識點還是太少了，而且有些知識陳舊了。所以現在我一般推薦這些免費學習SQL的網站：
The SQL Tutorial to Data Analysis
SQL Course
Practical SQL Interview Questions and Answers
如果在國內打不開這些網站，或者閱讀英文吃力的話，還是建議看《SQL21日自學通》。

SQL 中的子句【必考】

子句和JOIN是必考的，但是說由于表的聯(lián)合相關的學習資料實在是太多了，都快說吐了，在此就不贅述了。一般面試中，SELECT之后應該是FROM，在每次輸入SELECT語句時FROM也應該輸入，之后按順序是：WHERE、GROUP BY、HAVING、ORDER BY。其余的子句包括UNION、UNION ALL、INTERSECT和MINUS。下面來講講WHERE、GROUP BY、HAVING、ORDER BY、UNION、UNION ALL、INTERSECT和MINUS的用法。

1）WHERE
使用WHERE子句是選定返回數據的條件，可以和表的聯(lián)合一起使用，除非你確實是想對表中的所有記錄進行聯(lián)合，否則一定不要忘記使用WHERE子句。

WHERE有時候和從屬運算IN或BETWEEN一起使用：
SELECT * FROM people WHERE state IN ('CA','NY');
SELECT * FROM inventory WHERE prince BETWEEN 50 AND 100;請注意BETWEEN操作將包括邊界值（50和100）

WHERE也可以和LIKE表達式一起用：
在LIKE表達式中，%是一種通配符，表示可能的模糊信息。如果想查找在某一確定的位置上有字符的數據時，可以使用另一個通配符——下劃線：
SELECT * FROM people WHERE firstname LIKE '_o%' 返回firstname中第二個字符為o的數據類似的還有STARTING WITH：STARTING WITH子句附加于WHERE子句上，它的作用與LIKE（%）相似。

2）ORDER BY
查詢輸出的結果按一定的排序規(guī)則來顯示，ORDER BY可以使用多個字段，在ORDER BY后邊的DESC表示用降序排列來代替默認的升序排列。如：SELECT * FROM customers ORDER BY consumption? # DESC
假如你已經知道了你想要進行排序的列是表中的第一列的話，那么你可以用ORDER BY 1 來代替輸入列的名字。

3）GROUP BY
SQL無法把正常的列和匯總函數結合在一起，這時就需要GROUP BY子句，它可以對 SELECT 的結果進行分組后在應用匯總函數。當要求分組結果返回多個數值時不能在SELECT子句中使用除分組列以外的列，這將會導致錯誤的返回值，但是你可以使用在SELECT中未列出的列進行分組。無論在什么情況下進行分組，SELECT語句中出現的字段只能是在GROUP BY中出現過的才可以。

4）HAVING
對需要進行分組的數據進行限制，匯總函數不能用在 WHERE 子句中，而是要用HAVING 子句 。HAVING子句允許你將匯總函數作為條件。

5）UNION與UNION ALL合并
UNION將返回兩個查詢的結果并去除其中的重復部分，UNION ALL與UNION一樣對表進行了合并，但是它不去掉重復的記錄。

6）INTERSECT相交
返回兩個表中共有的行。

7）MINUS相減
返回的記錄是存在于第一個表中但不存在于第二個表中的記錄。如果解釋器不支持一般用table_name1 t2 LEFT OUTER JOIN table_name2 t2 WHERE t2.col_name IS NULL;

返回前幾行【高頻】

MySQL的LIMIT+數字和TOP子句是等價的，并非所有的數據庫系統(tǒng)都支持 TOP 子句。所以我們來簡單看一下limit+offset的用法。以下這兩句都合乎語法，但是有區(qū)別：

select * from table_name limit 3,1;? ? ? ? ? ? ? ? # 跳過前3條數據，從數據庫中第4條開始查詢，取一條數據，即第4條數據
select * from table_name limit 3 offset 1;? ? ? # 從數據庫中的第2條數據開始查詢3條數據，即第2條到第4條

記住這兩句，再加上order by column_name (desc) 就能應付“消費第二多的客戶”、“點擊量第5到20名”之類的問題的。

條件語句【中頻】

條件語句考得并不多，但是如果很久不用MySQL就容易忘記。MySQL里常用的條件語句是Case。Case語句分為兩種：簡單Case函數和Case搜索函數。

- 簡單Case函數：
CASE gender WHEN '0' THEN 'male' WHEN '1' THEN 'female' ELSE 'others' END
- Case搜索函數：
CASE WHEN? age < 18 THEN '未成年人' WHEN age < 60 THEN '成年人' ELSE '老年人' END

Case語句只返回第一個符合條件的結果，剩下的條件會被自動忽略，比如上例中一個數據的age為16，那么它就在第一個case中被返回，不會進入第二個when中進行判斷，因此返回'未成年人'而不是'成年人'。

關于空值【高頻】

SQL中的空值是NULL，空值是不能用等號來比較的，而是要用IS NULL或者IS NOT NULL來判斷值是否為空值。

面試的時候往往需要在輸出結果中對空值進行處理，這時候最好用的就是IFNULL函數和ISNULL函數了。IFNULL是當SQL查詢某個字段為空的時候，查詢結果中設置其值為默認值。ISNULL使用指定的替換值替換 NULL：
ISNULL (檢查的對象, 如果為空值替換的值)

可惜，MySQL中ISNULL只是用來判斷是否為空，不能實現替換功能，所以用IFNULL代替，語法和上面的ISNULL一樣。

關于Window Function【中頻】

面試的時候通常是用的MySQL，MySQL本身是不支持Window Function的（一般翻譯為統(tǒng)計分析函數）。如果面試官不讓你用window function卻要寫出cumulative sum（累積和）的話，那就是純心惡心人了，這樣的公司不去也罷?，F在大部分的數據庫語言都支持window function，盡管用便是。

除了COUNT/SUM/...+ (col_name) OVER (PARTITION BY col_name2 ORDER BY col_name3)之外，常見的window function還有RANK等。在這里著重講一下RANK()、DENSE_RANK()、ROW_NUMBER()三者排序的不同：

RANK()返回的是不持續(xù)的編號，例如100, 101, 101, 102返回的編號將是1,2,2,4；
DENSE_RANK()返回的是持續(xù)的編號，例如100, 101, 101, 102返回的編號是1,2,2,3；
ROW_NUMBER()返回的是持續(xù)不重復的編號，例如100, 101, 101, 102返回的編號將是1,2,3,4；

可見RANK()和DENSE_RANK()排序的差異就是序列存在并列的情況下。

相關子查詢【中頻】

相關子查詢可以接受外部的引用從而得到一些令人驚奇的結果。
Select * From orders o
Where '三輪車' = (Select type From inventory i Where i.SeriesNum = o.SeriesNum)
個人覺得這樣的join很神奇。

WHERE與IN有些類似。IN只相當于多個等號的作用，而ANY、SOME、ALL則可以使用其他的比較運算符如大于或小于。ALL關鍵字的作用在于子查詢中的所有結果均滿足條件時它才會返回TRUE，因此常與不等號連用。如SELECT * FROM table_name1 WHERE name != ALL (SELECT name FROM table_name2 WHERE name = 'XXX')

隨機抽樣【低頻】

有時候面試會考到如何在SQL表中隨機抽樣，這時候就要用到RAND()函數。

通常被面試者的第一反應是：SELECT * FROM table ORDER BY RAND() LIMIT 10000，這樣來抽取一萬個樣本。但是如果數據量很大，上述的做法太慢了：它對于每一行都用了RAND()函數，這一步復雜度是O(n)；然后再排序，這一步的復雜度O(nlogn)。

所以可以改用這個方法：SELECT * FROM table WHERE RAND() <= .3，這樣可以抽出約30%的數據，然后再用TOP或者LIMIT子句。

操作數據【低頻】

在下文的數據操作命令之后，數據會出現在表中，但是在使用COMMIT語句之前它并不會真正生效。事務處理工作可以由COMMIT確認或只是簡單地放棄。如果在使用INSERT或者UPDATE語句之后發(fā)現了錯誤，可以使用ROLLBACK語句來取消更新操作。

1）INSERT VALUES語句
INSERT INTO table_name (col1, col2, ...) VALUES (value1, value2, ...)
如果要在表中插入唯一值：
IF NOT EXISTS (SELECT * FROM table_name WHERE col1 = value1) INSERT INTO table_name VALUES (value1, value2, ...);此處視解釋器不同語法可能有不同

2）INSERT SELECT語句
INSERT INTO table_name (col1, col2, ...) SELECT col1, col2, ... FROM table_name WHERE search_condition
它是將一個SELECT語句的輸出結果再輸入到另一個表格中去。

3）UPDATE語句
UPDATE table_name SET col1 = value1 [, col2 = value2] ...
WHERE search_condition
例如UPDATE people SET age = 90, gender = 'female' WHERE type= '老奶奶';
下面的拓展對于當給定表需要更新的內容源自于其它多個表的時候（不是標準語法，視數據庫支持）：
UPDATE table_name SET col1 = value1 [, col2 = value2] ...
FROM table_list WHERE search_condition

4）DELETE語句
DELETE FROM table_name WHERE search_condition
DELETE語句只會刪除記錄，不會刪除表，如果要刪除表需使用DROP TABLE命令。

創(chuàng)建和操作表【低頻】

這個部分是最不容易被面到的內容了，但是聽朋友說面試中國的公司時還是會考這個知識點：

1）CREATE DATABASE語句
CREATE DATABASE database_name

2）CREATE TABLE語句
CREATE TABLE table_name (col_name1 datatype [NOT NULL], col_name2 datatype [NOT NULL], ...)

3）ALTER TABLE語句
ALTER TABLE語句可以在表創(chuàng)建以后修改它的結構：
ALTER TABLE table_name <ADD col_name1 datatype; | MODIFY col_name2 datatype;>

SQL函數

1）匯總函數（與GROUP BY連用）：COUNT、SUM、AVG、MAX、MIN、STDDEV、VARIANCE
COUNT、MAX、MIN既可以處理數值也可以處理字符（字符中最大的是小寫的z）。因為大寫的字母代碼比小寫的小，所以大寫的字符總是排在小寫字符的前面。

2）日期與時間函數
ADD_MONTHS：將給定的日期增加一個月
LAST_DAY：指定月份的最后一天
MONTHS_BETWEEN：計算在給定的兩個日期中有多少個月
NEW_TIME(datetime1, c1, c2)：給出時間datetime1在c1時區(qū)對應c2時區(qū)的日期和時間
NEXT_DAY：求指定日期的下一個日期
SYSDATE：系統(tǒng)的日期和時間，也可以用CURRENT_TIMESTAMP()或CURRENT_DATE()

3）數學函數
ABS：給定數字的絕對值
CEIL：與給定參數相等或比給定參數大的最小整數
FLOOR：與給定參數相等或比給定參數小的最大整數
COS、COSH、SIN、SINH、TAN、TANH、EXP、LN、LOG三角函數和指數計算
MOD(A, B)：取模運算，A與B相除后的余數，同A%B，將返回一個除法的余數部分，如：SELECT NUMERATOR%DENOMINATOR REMAINDER FROM table_name。
POWER：冪
SQRT：平方根
SIGN：求符函數，如果參數的值為負數，那么 SIGN 返回-1；如果參數的值為正數，那么 SIGN 返回 1；如果參數為零，那么 SIGN 也返回零。

4）字符函數
CHR：與所給數值參數等當的字符，返回的字符取決于數據庫所依賴的字符集
CONCAT同||：字符串連接符號，比如CONCAT(firstname, lastname)或者 firstname || lastname
INITCAP：將第一個字母變?yōu)榇髮懀送馄渌淖帜竸t轉換成小寫
LOWER：轉換為全部小寫字母
UPPER：全部轉換成大寫字母
LPAD與RPAD：左填充與右填充，第一個參數是需要處理的字符串，第二個參數是需要將字符串擴充的寬度，第三個參數表示加寬部分用什么字符來做填補
LTRIM與RTRIM：左剪除與右剪除，剪除所指定的字符
REPLACE：替換字符
SUBSTR：將目標字符串的一部份輸出
TRANSLATE(目標字符串，源字符串，目的字符串)：在目標字符串與源字符串中均出現的字符將會被替換成對應的目的字符串的字符
INSTR：對某個字符串判斷其是否含有指定的字符，其語法為：INSTR(sourceString, destString, start, appearPosition)，需要知道在一個字符串中滿足特定的內容的位置
LENGTH：返回指定字符串的長度

5）轉換函數
TO_CHAR：將一個數字轉換為字符型
TO_NUMBER：將字符串型數字轉換為數值型

6）其它函數
GREATEST與LEAST：返回幾個表達式中最大的和最小的
USER：返回當前使用數據庫的用戶的名字

以上就是我總結的一些SQL常見的考點，現在面試中除了SQL的技術部分，也注重business sense，比如根據數據讓你算出conversion rate（轉換率）、click-through rate（點擊率）、ROI（投資回報率）等等。

這些抽象的知識點要結合具體的例子一起學習才更有效，但是我已經敲不動了……所以我們下次再見！ヾ(￣￣)Bye~Bye~