作者簡介
多肉，餓了麼資深python工程師。曾在17年擔任餓了麼即時配送眾包系統的研發經理，這篇文章最早的版本就誕生於那段時間，目前負責配送相關業務系統的整體穩定性建設。個人比較喜歡c和python，最近有點迷rust，同時還是個archlinux的日常使用者，有類似愛好的歡迎交流
Preface
為什麼以《寫給運營同學和初學者的Sql入門教程》為題？
這原本是給一位關係要好的運營同學定製的Sql教程。在餓了麼，總部運營的同學在排查、跟蹤線上問題和做運營決策的時候，除了通過運營管理系統查詢資訊和依賴資料分析師給出的分析資料，常常也需要直接從資料庫管理臺通過寫Sql的方式獲取更細緻、更實時的業務資料，並基於這些資料進行一些及時的分析，從而更快的給出運營方案。在這樣的背景下，Sql已經越來越成為我們運營同學的一項必備技能。網上有很多Sql教程(e.g. w3school)，我也翻閱過一些運營同學桌上的Sql紙質書，這些教程都很好，但普遍側重介紹語法，很多很多的語法，配以簡短的demo。作為老司機的reference book很贊，可是對於剛入門甚至還沒有入門的學習者，就未免困難了一點。再回顧運營同學的使用場景，大多數情況下是根據一些已有條件做一些簡單的查詢，偶爾會有一些相對複雜的查詢，比如對查詢結果做一些聚合、分組、排序，或是同時查詢兩三張資料表，除此以外，建表、建索引、修改表欄位、修改欄位值等等這些操作，在運營同學的日常工作中基本是不會遇到的。
基於以上種種原因，寫了這篇教程，初衷是能夠幫助這位好朋友以更高的ROI入門Sql。下面是寫這篇教程時的一些考量：
- 從資料庫、資料表的基礎概念以及最簡單的Sql語法開始，做一些必要的鋪墊，後續的每一章再逐步增加難度；
- 只介紹查詢語法(更準確的說，是最常用的查詢語法)，將它們的用法和套路解釋清楚，不涉及日常工作暫時還用不到的建表、修改表結構等等操作，避免鋪天蓋地的語法一個接一個反而干擾了學習的節奏；
- 通過穿插一些小測驗、小溫習，及時檢驗對知識點的理解和複習已經學習過的知識點；
- 結合一些業務場景的demo幫助理解；
- 結尾提供一章快速複習，既是複習，也可以自測還有哪些知識點沒有掌握到位；
建議所有閱讀教程的同學，都嘗試搭建一套自己的資料庫服務(建議安裝MySQL)，對教程中的demo多做一些練習，不論是示例、小測驗還是小溫習裡面的Sql語句，都不妨親自執行一下，這也是一種很好的幫助你熟悉語法的方式。當然搭建自己的資料庫會是一個不小的挑戰，寫作這篇教程的時候，我在自己的VPS上安裝了MySQL(MariaDB)並提供了一個連線指令碼(隱藏了連線MySQL的過程)給朋友使用，但是這種方式並不適合推廣到所有人。具體的安裝和使用方式，不在本教程的敘述範圍內，所以…運營妹子們可以求助下熟悉的研發同學，漢子們嘛..
- 資料建立指令碼-通過該指令碼匯入demo資料到MySQL中
- 連結VPS上MySQL的指令碼-基本原理是建立了一個ssh tunnel，在tunnel裡和遠端的MySQL通訊，但實際上本地建MySQL服務更方便，僅供參考
可以從這裡sql_tutorial下載通過pandoc+latex匯出的pdf，獲得更好的閱讀體驗。
B.T.W
由餓了麼技術社群主辦的首屆物流技術開放日終於來啦！
時間：2018年12月30日
地點：餓了麼上海總部：普陀區近鐵城市廣場北座5樓榴蓮酥
此次活動邀請到了物流團隊的6位重量級嘉賓。不僅會有前後端大佬分享最新的架構、演算法在物流團隊的落地實戰經驗，更有 P10 大佬教你如何在業務開發中獲得技術成長。當然，也會有各種技術書籍，紀念品拿到手軟，最後最重要的一點，完全免費！還等什麼，趕快點選 etech.ele.me/salon.html?… 瞭解更多細節並報名吧！
?我也會在這次開放日活動中分享Gunicorn有關的話題，歡迎大家報名參加。放出一張分享內容的Outline：
by 多肉
Introduction
其實Sql並沒有那麼難。Sql是幫助你和關係型資料庫互動的一套語法，主要支援的操作有4類：
- DQL：其實就是資料查詢操作，通過特定的語法查詢到資料庫裡你想要的資料結果，並且展示出來；
- DML：資料修改操作，包括更新某個資料欄位、向某張表裡面插入一條新的資料等等；
- DDL：資料定義操作，比如建立一張新的表或是建立一個新的索引(索引是啥？我們後面專門聊一聊它)；
- DCL：資料授權操作，比如授權特定的人可以查詢某張特定的表；
聽起來挺嚇人的對吧，但實際上DML、DDL、DCL這3類操作在日常的運營工作中幾乎都不會用到，經常會使用到的吶其實是第一種，也就是資料查詢操作(DQL)。Sql基本的查詢語法也比較簡單，那麼難在哪裡呢？我猜想難在學習了基本語法之後，不知道怎麼應用到實際的Case上。在接下來的內容裡，我將以一些十分接近現實的眾包運營Case為例，逐一解釋最基本的Sql查詢語法並且分析如何將它應用到具體的場景上。
1 預備知識
好的吧，吹了一波牛還是逃不過需要介紹一些最基礎的東西，但是我保證這是整篇教程中最枯燥的部分，後面就會有趣很多。
1.1 資料庫和資料表
為了更簡單的理解這兩個概念以及他們之間的關係，可以這麼類比：
- 資料表：就是一張表格，想象一張你經常在使用的Excel表，有行有列，每一行就是一條資料，每一列對應了這條資料的某一個具體的欄位，當然這張表還有一個表名。資料表也是如此，只是欄位名、表名不像Excel表格那樣好理解，比如Excel表格裡面某一列的欄位名可能叫
騎手id，而對應到資料表裡面可能就叫做rider_id，Excel的表名可能叫騎手基本資訊表，而對應到資料表的表名則可能叫tb_rider； - 資料庫：就是集中管理一批相關的表格的地方。你可以把它理解成是一個資料夾。平時你可能會建立一個名叫
眾包業務的資料夾，然後將眾包運營相關的Excel表格都放在這個資料夾裡面。資料庫也是如此，比如我們會有一個庫名叫做crowd的資料庫，裡面可能存放了tb_rider、tb_order等和騎手、運單相關的多張資料表；
所以，“關係型資料庫”的概念很嚇唬人，但其實道理很簡單，就是列和列之間有一定的聯絡，整合在一起就是一條有意義的資料，將這些資料歸納起來就構成了一張表，而將一批有關聯的表一同管理起來就得到了一個資料庫。
1.2 最基本的Sql查詢語法
最基本的Sql查詢語法其實就一個：
SELECT 列名(或者*，表示所有列) FROM 表名 WHERE 篩選條件;
複製程式碼B.T.W 注意
SELECT...FROM...WHERE...;語句結尾的這個分號，在標準Sql語法中這個分號是必要的
讓我們按照FROM、WHERE、SELECT的順序理解一下這個語法：
FROM 表名：顧名思義，就是從表名指定的這張表格中；WHERE 篩選條件：意思是“當滿足篩選條件”的時候；SELECT 列名：意思是選擇出這些記錄，並且展示指定的列名；
串聯起來便是，從FROM後面指定的資料表中，篩選出滿足WHERE後面指定條件的資料，並且展示SELECT後指定的這幾列欄位。是不是很簡單吶？不過好像抽象了一點。所以我們來看幾個具體的超簡單的例子。假設我們有一張學生數學期末考試成績表，資料表長下面這樣，表名叫作tb_stu_math_score。
| id(自增主鍵) | name(學生姓名) | number(學號) | grade(年級) | class(班級) | score(得分) |
|---|---|---|---|---|---|
| 1 | 柯南 | 010201 | 1 | 2 | 100 |
| 2 | 小哀 | 010202 | 1 | 2 | 100 |
| 3 | 光彥 | 010203 | 1 | 2 | 98 |
| 4 | 步美 | 010204 | 1 | 2 | 95 |
| 5 | 元太 | 010205 | 1 | 2 | 59 |
讓我們試著理解一下下面幾個查詢語句：
[1] SELECT name FROM tb_stu_math_score WHERE score >
= 95;
從tb_stu_math_score表中挑選出得分大於95分的學生姓名，得到的結果顯而易見：
| name |
|---|
| 柯南 |
| 小哀 |
| 光彥 |
| 步美 |
[2] SELECT name, number FROM tb_stu_math_score WHERE score <
60;
從tb_stu_math_score表中挑選出得分小於60分的學生姓名，得到的結果是：
| name | number |
|---|---|
| 元太 | 010205 |
[3] SELECT * FROM tb_stu_math_score WHERE score = 100;
從tb_stu_math_score表中挑選出得分為100分學生的所有資訊(注意SELECT後面的*符號，表示所有欄位)，得到的結果是：
| id | name | number | grade | class | score |
|---|---|---|---|---|---|
| 1 | 柯南 | 010201 | 1 | 2 | 100 |
| 2 | 小哀 | 010202 | 1 | 2 | 100 |
小測驗
看看下面這些Sql查詢語句你是不是知道是什麼含義並且知道查詢結果是什麼了呢？
1. SELECT name, grade, class, score FROM tb_stu_math_score WHERE number = "010201";
2. SELECT * FROM tb_stu_math_score WHERE name = "小哀";
3. SELECT id, score FROM tb_stu_math_score WHERE number = "010202";
複製程式碼2 更進一步
剛剛我們學習了Sql查詢的最最最最基礎的語法，但是相信我，所有的Sql查詢幾乎都長這個樣子，所以理解了這個最基礎的語法結構，後面學習起來就輕鬆多了。接下來讓我通過一些例子，擴充套件這個基礎語法，教你一些更加高階的Sql查詢操作。不過首先，我們還是要看一下接下來我們的範例資料表長啥樣。
假設我們有一張騎手資料表，表名叫作tb_rider，還有一張運單資料表，表名叫作tb_order，這兩張表分別長下面這個樣子。
[1] 騎手資料表：tb_rider
| id | name | real_name_certify_state | level | level_city | is_deleted | created_at | updated_at |
|---|---|---|---|---|---|---|---|
| 1 | Stark | 2 | 3 | 1 | 0 | 2017-01-01 22:00:19 | 2018-01-01 06:40:01 |
| 2 | Banner | 2 | 3 | 9 | 0 | 2017-04-28 12:01:19 | 2018-01-01 06:40:01 |
| 3 | Rogers | 2 | 2 | 1 | 0 | 2017-04-10 17:24:01 | 2018-01-01 06:40:01 |
| 4 | Thor | 1 | 0 | 1 | 0 | 2017-12-31 23:10:39 | 2018-01-01 06:40:01 |
| 5 | Natasha | 2 | 1 | 1 | 0 | 2017-02-11 15:03:13 | 2018-01-01 06:40:01 |
| 6 | Barton | 2 | 1 | 9 | 0 | 2017-02-11 15:04:19 | 2018-01-01 06:40:01 |
| 7 | Coulson | 2 | 3 | 9 | 0 | 2017-01-03 23:00:22 | 2018-01-01 06:40:01 |
| 8 | Coulson | 1 | 0 | 2 | 0 | 2017-01-05 10:10:23 | 2018-01-01 06:40:01 |
欄位含義：
id：自增主鍵。又是一個聽起來很嚇人的名字，但實際含義很簡單。“自增”的意思是，每次在這張資料表中建立一條新記錄的時候，資料庫都會在上一個id值的基礎上自動加上一個固定的步長(預設就是+1)作為新記錄的id值。而所謂“主鍵”，就是能夠在一張資料表中唯一標識一條記錄的欄位，因為每條記錄的id都不一樣，所以它是主鍵。這個欄位可以是為了單純的標識資料的唯一性，也可以具有一些業務含義，比如這裡的id就同時也是騎手的賬號id；name: 騎手姓名；real_name_certify_state: 實名認證狀態：1-認證中，2-認證通過，3-認證失敗；level：騎手等級，3-金牌，2-銀牌，1-銅牌，0-普通；level_city：等級城市;is_deleted：這條資料是否有效，在大多數產線相關的資料表中，都會有這樣的標記欄位。0-未刪除(表示有效), 1-已刪除(表示無效)；created_at：這條資料的建立時間，也是所有產線資料表中都必須有的欄位；updated_at：這條資料最新一次被更新的時間，同樣是所有產線資料表中都必須有的欄位；
[2] 運單資料表：tb_order
| id | order_id | order_state | rider_id | rider_name | grabbed_time | created_at | updated_at |
|---|---|---|---|---|---|---|---|
| 1 | 300000201712300001 | 40 | 1 | Stark | 2017-12-30 12:34:55 | 2017-12-30 12:34:17 | 2017-12-30 12:39:30 |
| 2 | 300000201712300002 | 40 | 1 | Stark | 2017-12-30 12:34:56 | 2017-12-30 12:34:18 | 2017-12-30 12:44:27 |
| 3 | 300000201712300003 | 40 | 2 | Banner | 2017-12-30 13:23:12 | 2017-12-30 13:20:02 | 2017-12-30 13:54:09 |
| 4 | 300000201712300004 | 40 | 5 | Natasha | 2017-12-30 13:35:03 | 2017-12-30 13:34:19 | 2017-12-30 14:03:17 |
| 5 | 300000201712300005 | 40 | 1 | Stark | 2017-12-30 16:01:22 | 2017-12-30 16:01:03 | 2017-12-30 16:08:21 |
| 6 | 300000201712300006 | 40 | 3 | Rogers | 2017-12-30 16:10:45 | 2017-12-30 16:08:57 | 2017-12-30 16:34:27 |
| 7 | 300000201712310001 | 20 | 6 | Barton | 2017-12-31 09:12:57 | 2017-12-31 09:12:07 | 2017-12-31 09:20:35 |
| 8 | 300000201712310002 | 80 | 7 | Coulson | 2017-12-31 09:15:01 | 2017-12-31 09:10:33 | 2017-12-31 09:20:17 |
| 9 | 300000201712310003 | 80 | 2 | Banner | 2017-12-31 09:20:17 | 2017-12-31 09:18:10 | 2017-12-31 09:22:24 |
| 10 | 300000201712310004 | 20 | 3 | Rogers | 2017-12-31 10:37:33 | 2017-12-31 10:34:01 | 2017-12-31 10:38:09 |
| 11 | 300000201712310005 | 10 | 0 | 1970-01-01 00:00:00 | 2017-12-31 19:29:02 | 2017-12-31 19:29:02 | |
| 12 | 300000201712310006 | 10 | 0 | 1970-01-01 00:00:00 | 2017-12-31 19:29:27 | 2017-12-31 19:29:27 | |
| 13 | 300000201712310007 | 10 | 0 | 1970-01-01 00:00:00 | 2017-12-31 19:30:01 | 2017-12-31 19:30:01 |
欄位含義：
id：自增主鍵。吶，這裡的id就是單純的主鍵作用，沒有其他的業務含義；order_id：運單號，業務層面上運單的唯一標識；order_state：運單當前的狀態。10-待搶單，20-待到店，80-待取餐，40-已送達；rider_id：搶單的騎手id，還未被搶的運單這個欄位是預設值0；rider_name：搶單的騎手姓名，還未被搶的運單這個欄位是預設值空字元；grabbed_time：搶單時間，還未被搶的運單這個欄位是預設的”1970-01-01 00:00:00″(這是一個特殊的時間，有興趣的話可以搜尋關鍵詞：時間戳)；created_at：這條資料的建立時間，也是所有產線資料表中都必須有的欄位；updated_at：這條資料最新一次被更新的時間，同樣是所有產線資料表中都必須有的欄位；
小溫習
試著理解看看下面這幾條Sql的含義以及返回的資料結果吧？
1. SELECT name, real_name_certify_state FROM tb_rider WHERE level = 3;
2. SELECT * FROM tb_order WHERE rider_id = 1;
3. SELECT rider_id, rider_name, order_id, grabbed_time FROM tb_order WHERE order_state = 40;
複製程式碼2.1 IN 操作
場景： 線下反饋了一批騎手說自己理應是上海的金牌，但是牌級是普通或者展示的是金牌卻沒有享受到上海的金牌活動，你已經知道了這幾個分別是id=(2, 4, 7)的騎手，想排查一下他們的等級更新情況。
這時你可以選擇像這樣一條一條的查詢，像之前我們介紹的那樣：
1. SELECT name, real_name_certify_state, level, level_city FROM tb_rider WHERE id=2;
2. SELECT name, real_name_certify_state, level, level_city FROM tb_rider WHERE id=4;
3. SELECT name, real_name_certify_state, level, level_city FROM tb_rider WHERE id=7;
複製程式碼這樣當然可以達到目的，但是隻有兩三個騎手的時候還勉強可以操作，如果有幾十個騎手這樣查起來就太費勁了。這時候我們可以使用IN這個語法。
SELECT name, real_name_certify_state, level, level_city FROM tb_rider WHERE id IN(2, 4, 7);
複製程式碼很簡單的對吧？但我們還是來簡單理解一下，WHERE id IN(2, 4, 7)的意思就是篩選id欄位的值在2，4，7這幾個值當中的記錄，執行這條Sql語句你就會得到下面這樣的結果。
| name | real_name_certify_state | level | level_city |
|---|---|---|---|
| Banner | 2 | 3 | 9 |
| Thor | 1 | 0 | 1 |
| Coulson | 2 | 3 | 9 |
於是你會發現，Thor這個騎手因為他沒有通過實名認證所以肯定評不上金牌，Banner和Coulson兩位騎手雖然都是金牌騎手，但是等級城市卻是福州，所以享受不到上海金牌的活動。
那如果不知道騎手id，只知道騎手的名字怎麼辦？也可以使用IN查詢，只是這時候篩選的條件變成了name，取值範圍也變成了”Banner”, “Thor”, “Coulson”。就像這樣。
SELECT name, real_name_certify_state, level, level_city FROM tb_riderWHERE name IN("Banner", "Thor", "Coulson");
複製程式碼於是你順利的得到了以下的結果。
| name | real_name_certify_state | level | level_city |
|---|---|---|---|
| Banner | 2 | 3 | 9 |
| Thor | 1 | 0 | 1 |
| Coulson | 2 | 3 | 9 |
| Coulson | 1 | 0 | 2 |
Oops! 居然有兩個Coulson！
這就是在實際應用中要特別注意的地方了：
當你使用類似騎手id這種被設計為唯一值的欄位作為查詢依據時，返回的結果也是唯一的，而當你使用類似騎手姓名這類欄位作為查詢依據時，就有可能出現上面這種情況。這時候你就需要依賴更多的資訊來判斷，哪一條才是你真正想要的。所以能夠用明確的欄位作為查詢依據時就要儘可能的使用。
2.2 關係運算子：AND 和 OR
最常用的關係運算子有兩個AND和OR，用來連線多個篩選條件。顧名思義，AND就是**“並且”的意思，也就是同時滿足AND前後兩個篩選條件；OR就是“或者”**的意思，也就是滿足OR前後任何一個篩選條件。有點抽象了對不對，我們看一個具體的例子。
場景： 假設你想要看看2017-02-01(包括2017-02-01當天)到2017-06-01(不包括2017-06-01當天)期間註冊的騎手所有資訊。
註冊時間對應到資料上就是騎手資訊的建立時間(created_at)，換句話說，就是查詢tb_rider``表中建立時間處於2017-02-01到2017-06-01之間的資料。那這樣的Sql應該怎麼寫呢，這時我們就可以用到AND“`。
SELECT * FROM tb_rider WHERE created_at >
= "2017-02-01 00:00:00"AND created_at <
"2017-06-01 00:00:00";
複製程式碼B.T.W 注意因為包括2017-02-01當天，而不包括2017-06-01當天，所以前者是
>，而後者是
=<。
讓我們再來推廣一下。假設現在的場景變成：想看一看2017-02-01(包括當天)之前，或者2017-06-01(包括當天)之後註冊的騎手所有資訊。我們應該怎麼寫這個Sql呢？既然是或的關係，我們就應該使用OR了。
SELECT * FROM tb_rider WHERE created_at <
= "2017-02-01 00:00:00"OR created_at >
= "2017-06-01 00:00:00";
複製程式碼B.T.W 注意這裡既包括了2017-02-01當天，又包括了2017-06-01當天，所以前者是
<，後者是
=>。
=
當然啦，AND和OR這樣的關係運算子，不僅僅能夠連線前後兩個篩選條件，也可以通過使用若干個AND和OR連線多個不同的篩選條件。比如：想要看看2017-02-01(包括2017-02-01當天)到2017-06-01(不包括2017-06-01當天)期間註冊的且當前是金牌等級的騎手所有資訊，那麼我們可以這麼寫。
SELECT * FROM tb_riderWHERE created_at >
= "2017-02-01 00:00:00"AND created_at <
"2017-06-01 00:00:00"AND level = 3;
複製程式碼2.3 排序：ORDER BY
讓我們先小小的複習一下上面學到的知識點，有一個這樣的場景：
我們打算看一下Stark這位騎手，在2017-12-30當天搶單且當前狀態為已完成的運單號和運單的建立時間。
如何寫這個Sql呢？先思考3s…1…2…3，看看是否和你想的一樣。
SELECT order_id, created_at FROM tb_orderWHERE rider_id = 1AND grabbed_time >
= "2017-12-30 00:00:00"AND grabbed_time <
"2017-12-31 00:00:00"AND order_state = 40;
複製程式碼如果你沒有寫對，沒關係，讓我們來分析一下：
- Stark這位騎手的騎手id是1，所以我們的第一個篩選條件為
rider_id = 1； - 因為我們要看2017-12-30當天搶單的運單id，確定了我們的第二個篩選條件是搶單時間，對應的是
grabbed_time這個欄位，而2017-12-30當天，實際上指的就是2017-12-30 00:00:00(包括)到2017-12-31 00:00:00(不包括)這段時間； - 最後是已完成這個條件，
order_state欄位標識了運單狀態，因此我們的篩選條件是order_state = 40；
執行這個語句，我們得到了下面這樣的結果。
| order_id | created_at |
|---|---|
| 300000201712300001 | 2017-12-30 12:34:17 |
| 300000201712300002 | 2017-12-30 12:34:18 |
| 300000201712300005 | 2017-12-30 16:01:03 |
有點美中不足，我想按照運單的建立時間倒序排序把最近建立的運單排在最前面，這時候就可以使用ORDER BY語法了。
SELECT order_id, created_at FROM tb_orderWHERE rider_id = 1AND grabbed_time >
= "2017-12-30 00:00:00"AND grabbed_time <
"2017-12-31 00:00:00"AND order_state = 40ORDER BY created_at DESC;
複製程式碼讓我們再來理解一下，DESC是**“遞減”**的意思，與之對應的是ASC遞增。ORDER BY created_at DESC的含義是，按照(BY)created_at欄位值遞減(DESC)的順序對查詢結果排序(ORDER)。於是我們得到如下的結果。
| order_id | created_at |
|---|---|
| 300000201712300005 | 2017-12-30 16:01:03 |
| 300000201712300002 | 2017-12-30 12:34:18 |
| 300000201712300001 | 2017-12-30 12:34:17 |
B.T.W 在現實場景中有時候查詢結果的集合會很大(例如幾百行、幾千行)，但是我們只想看其中前10行的資料，這時候我們可以使用LIMIT語法。例如這裡我們可以使用LIMIT語法僅僅展示前兩行查詢結果：SELECT order_id, created_at FROM tb_orderWHERE rider_id = 1 AND grabbed_time >
= “2017-12-30 00:00:00″AND grabbed_time <
“2017-12-31 00:00:00” AND order_state = 40ORDER BY created_at DESCLIMIT 2;
我們再來看一個更加複雜的場景：假設想要查詢2017-12-30和2017-12-31兩天所有運單的所有資訊，並先按照騎手id遞增，再按運單狀態遞減的順序排序展示。還是先思考一會兒。
這時的Sql類似長這樣。
SELECT * FROM tb_orderWHERE created_at >
= "2017-12-30 00:00:00"AND created_at <
"2018-01-01 00:00:00"ORDER BY rider_id ASC, order_state DESC;
複製程式碼如果前面的每個知識點都理解了，這裡應該就只對**“先按照騎手id遞增，再按運單狀態遞減的順序排序展示”**有所疑惑。實際上我們不僅可以對一個欄位排序，還可以把多個欄位作為排序的依據，而且不同欄位上的排序規則(遞增/遞減)可以不同。但排序是有優先順序的，比如這裡，只有當rider_id欄位的值都相同無法區分順序時，才會對相同rider_id的這幾條資料再按照order_state欄位的值進行排序。舉例來說，rider_id = 2且order_state = 80的資料，也依然不可能排在rider_id = 1且order_state = 40的資料前面。
執行這條Sql語句，將得到的結果如下。
| id | order_id | order_state | rider_id | rider_name | grabbed_time | created_at | updated_at |
|---|---|---|---|---|---|---|---|
| 11 | 300000201712310005 | 10 | 0 | 1970-01-01 00:00:00 | 2017-12-31 19:29:02 | 2017-12-31 19:29:02 | |
| 12 | 300000201712310006 | 10 | 0 | 1970-01-01 00:00:00 | 2017-12-31 19:29:27 | 2017-12-31 19:29:27 | |
| 13 | 300000201712310007 | 10 | 0 | 1970-01-01 00:00:00 | 2017-12-31 19:30:01 | 2017-12-31 19:30:01 | |
| 1 | 300000201712300001 | 40 | 1 | Stark | 2017-12-30 12:34:55 | 2017-12-30 12:34:17 | 2017-12-30 12:39:30 |
| 2 | 300000201712300002 | 40 | 1 | Stark | 2017-12-30 12:34:56 | 2017-12-30 12:34:18 | 2017-12-30 12:44:27 |
| 5 | 300000201712300005 | 40 | 1 | Stark | 2017-12-30 16:01:22 | 2017-12-30 16:01:03 | 2017-12-30 16:08:21 |
| 9 | 300000201712310003 | 80 | 2 | Banner | 2017-12-31 09:20:17 | 2017-12-31 09:18:10 | 2017-12-31 09:22:24 |
| 3 | 300000201712300003 | 40 | 2 | Banner | 2017-12-30 13:23:12 | 2017-12-30 13:20:02 | 2017-12-30 13:54:09 |
| 6 | 300000201712300006 | 40 | 3 | Rogers | 2017-12-30 16:10:45 | 2017-12-30 16:08:57 | 2017-12-30 16:34:27 |
| 10 | 300000201712310004 | 20 | 3 | Rogers | 2017-12-31 10:37:33 | 2017-12-31 10:34:01 | 2017-12-31 10:38:09 |
| 4 | 300000201712300004 | 40 | 5 | Natasha | 2017-12-30 13:35:03 | 2017-12-30 13:34:19 | 2017-12-30 14:03:17 |
| 7 | 300000201712310001 | 20 | 6 | Barton | 2017-12-31 09:12:57 | 2017-12-31 09:12:07 | 2017-12-31 09:20:35 |
| 8 | 300000201712310002 | 80 | 7 | Coulson | 2017-12-31 09:15:01 | 2017-12-31 09:10:33 | 2017-12-31 09:20:17 |
這個部分相對有一點難，可以多對比著例子理解一下。
3 高階一點的話題
進入到這個部分，說明之前的內容你基本都已經掌握了，在日常運營的操作中有30%左右的場景都可以使用前面講述的這些知識點解決(當然會有個熟能生巧的過程)。這個部分，我將繼續介紹幾個更加高階、當然也更加有難度的Sql技能，當你結束這一部分的學習並且熟練掌握這些技能的時候，你會發現絕大部分需要通過查資料來確認的場景你都可以勝任。因為這個章節的內容本身難度又大了些，如果再對著一張複雜的表就更加難以關注重點，因此我們精簡一下表結構，只保留一些必要的欄位。新的tb_order表如下。
| id | order_id | order_state | rider_id | rider_name | merchant_customer_distance | created_at |
|---|---|---|---|---|---|---|
| 1 | 300000201712300001 | 40 | 1 | Stark | 2.5 | 2017-12-30 12:34:17 |
| 2 | 300000201712300002 | 40 | 1 | Stark | 1.8 | 2017-12-30 12:34:18 |
| 3 | 300000201712300003 | 40 | 2 | Banner | 1.8 | 2017-12-30 13:20:02 |
| 4 | 300000201712300004 | 40 | 5 | Natasha | 2.7 | 2017-12-30 13:34:19 |
| 5 | 300000201712300005 | 40 | 1 | Stark | 1.2 | 2017-12-30 16:01:03 |
| 6 | 300000201712300006 | 40 | 3 | Rogers | 0.5 | 2017-12-30 16:08:57 |
| 7 | 300000201712310001 | 20 | 6 | Barton | 1.3 | 2017-12-31 09:12:07 |
| 8 | 300000201712310002 | 80 | 7 | Coulson | 2.9 | 2017-12-31 09:10:33 |
| 9 | 300000201712310003 | 80 | 2 | Banner | 0.7 | 2017-12-31 09:18:10 |
| 10 | 300000201712310004 | 20 | 3 | Rogers | 2.2 | 2017-12-31 10:34:01 |
| 11 | 300000201712310005 | 10 | 0 | 0.3 | 2017-12-31 19:29:02 | |
| 12 | 300000201712310006 | 10 | 0 | 1.3 | 2017-12-31 19:29:27 | |
| 13 | 300000201712310007 | 10 | 0 | 3.0 | 2017-12-31 19:30:01 |
新增的列： merchant_customer_distance：配送距離(商家到使用者的直線距離)，單位是千米(km)。
3.1 聚合函式：COUNT，SUM, AVG
千萬別被聚合函式這個名字嚇唬到，可以簡單的理解為對資料進行一些加工處理，讓我們先來分別看一下這幾個聚合函式的基本定義。
COUNT：對查詢結果集合中特定的列進行計數；SUM：對查詢結果的某個欄位進行求和；AVG：就是average的意思，對查詢結果的某個欄位計算平均值；
讓我們分別來看幾個具體的例子。
[1] 場景：查詢2017-12-30這一天，騎手Stark的所有完成單(狀態為40)總量
你可以這樣來寫這個Sql。
SELECT COUNT(id) FROM tb_order WHERE rider_id = 1AND order_state = 40 AND created_at >
= "2017-12-30 00:00:00"AND created_at <
"2017-12-31 00:00:00";
複製程式碼到這裡你應該已經能夠很好的理解WHERE...AND...AND...這部分的含義，我們就不再過多的討論這個部分(對自己要有信心！試著理解先自己理解一下)。
讓我們重點來看一下COUNT(id)這部分的含義。其實很簡單，就是對id這一列進行計數。連起來看這段Sql，意思就是：從tb_order這張表中(FROM tb_order)篩選(WHERE)騎手id為1(rider_id = 1)且運單狀態為已完成(order_state = 40)且建立時間大於等於2017年12月30日(created_at >)且建立時間小於2017年12月31日(
= "2017-12-30 00:00:00created_at <)的資料，並且按照
"2017-12-31 00:00:00id這列對返回的結果集合進行計數。
我們看到tb_order這張表中，2017-12-30當天由騎手Stark配送且狀態是已完成的運單分別是300000201712300001、300000201712300002、300000201712300005這幾個運單號的運單，對應的自增id分別是id=[1, 2, 5]，所以對id這一列進行計數得到的結果是3。所以我們得到的查詢結果如下表。
| COUNT(id) |
|---|
| 3 |
有時候你僅僅是想查一下滿足某個條件的記錄的總行數，而並非想對某個特定的列進行計數，這時就可以使用COUNT(*)語法。比如上面的這個Sql也可以寫成下面這個樣子。
SELECT COUNT(*) FROM tb_order WHERE rider_id = 1AND order_state = 40 AND created_at >
= "2017-12-30 00:00:00"AND created_at <
"2017-12-31 00:00:00";
複製程式碼因為返回的結果有三行，所以我們會得到下表的結果。
| COUNT(*) |
|---|
| 3 |
看起來COUNT(列)和COUNT(*)是完全等價的？有些特定的場景下的確如此，這裡需要補充一下COUNT的兩個小脾氣。
COUNT不會自動去重；COUNT在某一條查詢結果中，用來計數的那一列的值為**“空”**時，這條記錄不進行計數；
B.T.W 注意這裡的“空”指的是
<，而不是某一列沒有展示出任何值就是空，這是一個相對技術的概念，當前不理解可以先跳過
null>
有一點暈是嗎？不著急，我們來看兩個例子。假設有兩張表，很簡單的表，長下面這樣。
示例表1：tb_sample_1
| id | name |
|---|---|
| 1 | Stark |
| 2 | Stark |
| 3 | Coulson |
| 4 | Natasha |
| 5 | Stark |
示例表2：tb_sample_2
| id | name |
|---|---|
| 1 | Stark |
| 2 | Stark |
| 3 | < null> |
| 4 | < null> |
| 5 | Natasha |
| 6 | Coulson |
我們下猜一猜下面幾條Sql的執行結果分別是什麼？
1. SELECT COUNT(id) FROM tb_sample_1;
2. SELECT COUNT(*) FROM tb_sample_1;
3. SELECT COUNT(name) FROM tb_sample_1;
4. SELECT COUNT(name) FROM tb_sample_2;
複製程式碼B.T.W 當
SELECT...FROM...WHERE...語句中的WHERE...部分被省略時，表示查詢表中的所有資料(不對資料進行篩選)。
讓我們逐一分析一下。
1. SELECT COUNT(id) FROM tb_sample_1;
複製程式碼這條Sql沒有太多可以分析的，因為tb_sample_1表中id欄位的取值範圍是id=[1, 2, 3, 4, 5]，共5個，所以我們得到的結果如下。
| COUNT(id) |
|---|
| 5 |
2. SELECT COUNT(*) FROM tb_sample_1;
複製程式碼這條Sql也沒有太多需要分析的，因為COUNT(*)的含義是計算查詢結果的總行數，tb_sample_1共5行資料，所以我們得到的結果如下。
| COUNT(*) |
|---|
| 5 |
3. SELECT COUNT(name) FROM tb_sample_1;
複製程式碼這條Sql裡面我們對name這一列進行計數，tb_sample_1表中包含3個Stark，1個Coulson和1個Natasha，因為COUNT不進行自動去重，因此結果是5=3(Stark)+1(Coulson)+1(Natasha)，如下表。
| COUNT(name) |
|---|
| 5 |
4. SELECT COUNT(name) FROM tb_sample_2;
複製程式碼這條Sql語句我們還是對name這一列進行計數，tb_sample_2表中包含2個Stark，1個Coulson，1個Natasha以及2個<，由於COUNT不去重因此2個Stark都會被計數，但COUNT不會對值為**“空”**的結果進行計數，因此兩個
null><都會被忽略。所以最終的結果為
null>4=2(Stark)+1(Coulson)+1(Natasha)，如下表。
| COUNT(name) |
|---|
| 4 |
[2] 場景：查詢Stark這名騎手的累計配送里程
讓我們先定義一下累計配送里程：騎手所有配送完成單的配送距離(商家到使用者的直線距離)之和。
這裡的關鍵詞是求和，所以我們要用到SUM這個聚合函式。對欄位求和的意思是把返回的結果集合中該欄位的值累加起來。讓我們看下這個場景的Sql怎麼寫。
SELECT SUM(merchant_customer_distance) FROM tb_orderWHERE rider_id = 1 AND order_state = 40;
複製程式碼讓我們來分析一下這條語句，FROM tb_order WHERE rider_id = 1 AND order_state = 40已經比較好理解了，就是從tb_order表中篩選出騎手id為1且配送狀態為40的記錄。而這裡的SUM(merchant_customer_distance)的含義，就是對前面的條件篩選出的資料結果中的merchant_customer_distance列的值進行求和。根據騎手id和配送狀態篩選出的記錄分別為id=(1, 2, 5)，對應的merchant_customer_distance的值分別為merchant_customer_distance=(2.5, 1.8, 1.2)，求和結果為5.5=2.5+1.8+1.2，如下表。
| SUM(merchant_customer_distance) |
|---|
| 5.5 |
[3] 場景：查詢Stark這名騎手的平均配送里程
同樣的，讓我們先來定義一下平均配送里程：騎手所有完成單的配送距離(商家到使用者的直線距離)之和除以總的完成單量。
基於SUM的經驗和前面的“預告”，不難想到這次我們會用到AVG這個聚合函式。對欄位求平均值的意思是，把結果集合中該欄位的值累加起來再除以結果總行數。AVG幫我們自動完成了“做除法”的動作，所以Sql的長相和上一個場景的SUM是如出一轍的。
SELECT AVG(merchant_customer_distance) FROM tb_orderWHERE rider_id = 1 AND order_state = 40;
複製程式碼根據騎手id和配送狀態篩選出的記錄分別為id=(1, 2, 5)，對應的merchant_customer_distance的值分別為merchant_customer_distance=(2.5, 1.8, 1.2)，求平均值的結果為1.83=(2.5+1.8+1.2) / 3，如下表。
| AVG(merchant_customer_distance) |
|---|
| 1.83 |
寫在3.1節的最後：
對著這幾個場景學習下來，不知道你感覺怎麼樣吖？是否覺得這幾個聚合函式本身還蠻簡單的，或者也有可能會覺得一下子灌輸了很多知識點有點費勁呢？其實聚合函式有它複雜的一面，我們上面看的這些Case都是比較簡單的使用方式。但是千萬不要擔心，一方面是因為運營工作中遇到的絕大多數場景都不會比這些示例Case更復雜，另一方面是不鼓勵過於複雜的使用這些聚合函式，因為查詢的邏輯越是複雜就越是難以“預測”查詢的結果，Sql並不是一個適合表達“邏輯”的語言，如果對資料的再加工邏輯很多，就應該考慮像分析師提需求或者學習更加利於表達邏輯的其他程式語言。
其次要說的就是多給自己些信心，同時也要多一點耐心。Sql雖然不同於Python、Java這樣的通用編成語言，除了語法還雜糅著一套體系化的程式設計概念、設計哲學，但是初次上手的時候還是會感覺到有些吃力的。但是隻要多去理解幾遍示例、多自己寫一寫，特別是在之後遇到實際工作中真實場景的時候自己思考如何轉化為Sql、多實踐、多回顧分析，很快就會在潛移默化中掌握它，要相信熟能生巧。
接下來的3.2、3.3節，我會繼續介紹兩個實用的Sql語法，以及如何將它們和聚合函式結合使用，會更有難度一些。
3.2 對查詢結果去重：DISTINCT 語法
DISTINCT語法顧名思義就是對某一列的值進行去重，讓我們首先來回顧一下3.1節中COUNT的其中一個例子。
這個例子使用的是tb_sample_1這張表，這張表很簡單，讓我再把它貼出來。
| id | name |
|---|---|
| 1 | Stark |
| 2 | Stark |
| 3 | Coulson |
| 4 | Natasha |
| 5 | Stark |
對應的，我們想要回顧的這條Sql語句也很簡單。
SELECT COUNT(name) FROM tb_sample_1;
複製程式碼前面我們已經分析過這條Sql：對name這列進行計數，有3個Stark，1個Coulson，1個Natasha，所以得到最終的結果如下表。
| COUNT(name) |
|---|
| 5 |
可是有的時候，我們不想對相同的名字進行重複計數，當有多個相同的名字時只計數一次。這時候就可以使用到DISTINCT語法。
SELECT COUNT(DISTINCT name) FROM tb_sample_1;
複製程式碼對比上一條Sql只是增加了一個DISTINCT關鍵字，其實理解起來呢也不用把它想的太複雜啦：COUNT(DISTINCT name)就是對去重後的name進行計數。tb_sample_1中有3個Stark,但是3個Stark是重複的，使用DISTINCT語法後只會被計算一次，另外還有1個Coulson和一個Natasha，所以得到的結果如下表。
| COUNT(DISTINCT name) |
|---|
| 3 |
DISTINCT語法可以單獨使用，這時就是它本身的意思，對某列的值進行去重。但是相比之下，更常見的是像上面的例子一樣和COUNT這個聚合函式一起使用，這樣就可以對去重後的結果進行計數。
3.3 將查詢資料分組：GROUP BY 語法
前面我們基於tb_order這張表講解了很多Sql的語法知識，讓我們再來回憶一下這張表的容顏。
| id | order_id | order_state | rider_id | rider_name | merchant_customer_distance | created_at |
|---|---|---|---|---|---|---|
| 1 | 300000201712300001 | 40 | 1 | Stark | 2.5 | 2017-12-30 12:34:17 |
| 2 | 300000201712300002 | 40 | 1 | Stark | 1.8 | 2017-12-30 12:34:18 |
| 3 | 300000201712300003 | 40 | 2 | Banner | 1.8 | 2017-12-30 13:20:02 |
| 4 | 300000201712300004 | 40 | 5 | Natasha | 2.7 | 2017-12-30 13:34:19 |
| 5 | 300000201712300005 | 40 | 1 | Stark | 1.2 | 2017-12-30 16:01:03 |
| 6 | 300000201712300006 | 40 | 3 | Rogers | 0.5 | 2017-12-30 16:08:57 |
| 7 | 300000201712310001 | 20 | 6 | Barton | 1.3 | 2017-12-31 09:12:07 |
| 8 | 300000201712310002 | 80 | 7 | Coulson | 2.9 | 2017-12-31 09:10:33 |
| 9 | 300000201712310003 | 80 | 2 | Banner | 0.7 | 2017-12-31 09:18:10 |
| 10 | 300000201712310004 | 20 | 3 | Rogers | 2.2 | 2017-12-31 10:34:01 |
| 11 | 300000201712310005 | 10 | 0 | 0.3 | 2017-12-31 19:29:02 | |
| 12 | 300000201712310006 | 10 | 0 | 1.3 | 2017-12-31 19:29:27 | |
| 13 | 300000201712310007 | 10 | 0 | 3.0 | 2017-12-31 19:30:01 |
溫故而知新！先來出幾道題目複習一下前面所學的Sql知識。
複習題1： 試著寫出以下幾個場景對應的Sql語句
- 查詢2017-12-30當天建立的運單，狀態為已完成且配送距離大於等於2公里的總單量；
- 查詢2017-12-30當天建立且狀態為已完成的所有運單的平均配送距離；
- 查詢2017-12-30當天完成過配送任務(至少配送完成1單)的騎手總人數；
複習題2： 試著理解以下幾條Sql的含義並且寫出查詢的結果
1. SELECT COUNT(order_id) FROM tb_order WHERE order_state = 40 AND merchant_customer_distance >
= 2.0 AND created_at >
= "2017-12-30 00:00:00" AND created_at <
"2017-12-31 00:00:00";
2. SELECT AVG(merchant_customer_distance) FROM tb_order WHERE order_state = 40 AND created_at >
= "2017-12-30 00:00:00" AND created_at <
"2017-12-31 00:00:00";
3. SELECT COUNT(DISTINCT rider_id) FROM tb_order WHERE order_state = 40 AND created_at >
= "2017-12-30 00:00:00" AND created_at <
"2017-12-31 00:00:00";
複製程式碼聰明的你是否發現複習題2就是複習題1的答案呢？如果還沒有發現，沒關係，再回過頭來多分析幾遍，Practice Makes Perfect 絕對是真理。不過複習這幾個例子可不僅僅是為了複習哦，讓我們在1、2兩個場景的基礎下擴充套件一下，講解新的知識點。思考下面這兩個場景。
- 查詢2017-12-30當天每個參與跑單的騎手各自的完成單總量；
- 查詢2017-12-30當天每個參與跑單騎手的完成單平均配送距離；
首先分析一下這裡的場景1。“2017-12-30當天”這個條件不難轉化為created_at >，“完成單”不難轉化為
= '2017-12-30 00:00:00' AND created_at <
'2017-12-31 00:00:00'order_state = 40，由於要計算運單的“總量”我們也不難想到可以對order_id進行COUNT操作。那麼如何分組到每個騎手身上呢？這時候就要用到GROUP BY了。
SELECT COUNT(order_id) FROM tb_order WHERE order_state = 40AND created_at >
= "2017-12-30 00:00:00" AND created_at <
"2017-12-31 00:00:00"GROUP BY rider_id;
複製程式碼注意這裡執行順序是先按照WHERE條件進行篩選，然後根據騎手id進行分組(GROUP BY)，最後再對每個分組按照運單號進行計數。因此我們可以得到下表的結果。
| COUNT(order_id) |
|---|
| 3 |
| 1 |
| 1 |
| 1 |
好像有哪裡不對？結果中看不到對應的騎手吖！不著急，我們稍微修改下剛才的Sql，將騎手id、騎手姓名這2列展示出來就可以了。
SELECT rider_id, rider_name, COUNT(order_id)FROM tb_order WHERE order_state = 40AND created_at >
= "2017-12-30 00:00:00"AND created_at <
"2017-12-31 00:00:00"GROUP BY rider_id;
複製程式碼我們得到如下表的結果。
| rider_id | rider_name | COUNT(order_id) |
|---|---|---|
| 1 | Stark | 3 |
| 2 | Banner | 1 |
| 5 | Natasha | 1 |
| 3 | Rogers | 1 |
這樣是不是就清晰多了。
再來分析場景2。有了前面的例子，“2017-12-30當天”、“完成單”這兩個條件應該是已經得心應手、信手拈來了，“平均配送距離”問題也不大，可以轉化為AVG(merchant_customer_distance)。那麼如何分組到每個騎手身上呢？還是通過GROUP BY語法。我們的Sql長成下面這個樣子。
SELECT rider_id, rider_name, AVG(merchant_customer_distance)FROM tb_order WHERE order_state = 40AND created_at >
= "2017-12-30 00:00:00"AND created_at <
"2017-12-31 00:00:00"GROUP BY rider_id;
複製程式碼得到如下表的結果。
| rider_id | rider_name | AVG(merchant_customer_distance) |
|---|---|---|
| 1 | Stark | 1.83 |
| 2 | Banner | 1.8 |
| 5 | Natasha | 2.7 |
| 3 | Rogers | 0.5 |
還是需要特別提一下這裡的執行順序，首先執行的是WHERE條件篩選，然後對篩選出的資料結果根據騎手id進行分組，最後再對每個分組中的資料進行merchant_customer_distance列的求平均值。
3.4 聚合函式的好搭檔：HAVING 語法
HAVING語法的含義類似於WHERE，當我們使用HAVING的時候一般遵循HAVING 篩選條件的語法結構。你可能會問啦，既然和WHERE語法含義差不多、使用方式又很類似，那幹嘛還要憑空多個HAVING語法出來呢？原因就在於聚合函式。WHERE語法是不能和聚合函式一起使用的，但有些時候我們卻需要依賴聚合函式的計算結果作為篩選條件。讓我們看一下3.3節中場景2這個例子。
場景2：查詢2017-12-30當天每個參與跑單騎手的完成單平均配送距離。
通過前面我們的分析，得到這樣的Sql。
SELECT rider_id, rider_name, AVG(merchant_customer_distance)FROM tb_order WHERE order_state = 40AND created_at >
= "2017-12-30 00:00:00"AND created_at <
"2017-12-31 00:00:00"GROUP BY rider_id;
複製程式碼我們在場景2的基礎上再擴充套件一下。
擴充套件的場景2：查詢2017-12-30當天每個參與跑單騎手的完成單平均配送距離，並篩選出其中平均配送距離超過1.5km的資料。
我們得到這樣的Sql結果。
SELECT rider_id, rider_name, AVG(merchant_customer_distance)FROM tb_order WHERE order_state = 40AND created_at >
= "2017-12-30 00:00:00"AND created_at <
"2017-12-31 00:00:00"GROUP BY rider_idHAVING AVG(merchant_customer_distance) >
1.5;
複製程式碼比較一下不難發現，變化僅僅是末尾多了HAVING AVG(merchant_customer_distance) >這條子句。讓我們分析看看。
1.5SELECT ... FROM ... WHERE ...和之前的用法並沒有變化，GROUP BY rider_id將SELECT的結果根據rider_id進行分組，分組完成後HAVING AVG(merchant_customer_distance) >語句對每一組的
1.5merchant_customer_distance欄位值求取平均數，並且將平均數大於1.5的結果篩選出來，作為返回結果。
執行這條Sql我們得到結果。
| rider_id | rider_name | AVG(merchant_customer_distance) |
|---|---|---|
| 1 | Stark | 1.83 |
| 2 | Banner | 1.8 |
| 5 | Natasha | 2.7 |
Rogers這位騎手(騎手id=3)因為平均配送距離為0.5，不滿足HAVING語句指定的“平均配送距離大於1.5km”的篩選條件，所以沒有在我們的查詢結果中。
4 有點超綱的話題
4.1 欄位型別
型別這個詞此刻你聽起來可能還是很陌生的，但其實在電腦科學領域，型別是一個非常基礎而且廣泛存在的概念，幾乎每一種程式語言都有自己的型別系統。
B.T.W 在二進位制中每一個0或者1被稱作一個位元位，所以32位是指一段二進位制資料中0和1的個數加在一起共有32個，例如
00000000000000000000000000000001表示一個32位二進位制數，0000000000000001表示一個16位二進位制數。
[1] 為什麼要定義型別的概念？
關於為什麼要有型別這個概念，我吶有一個“不成熟”的理解：程式語言作為人和機器互動的一種工具，人類對資料有人類邏輯上的理解，當我們看到2903的時候我們會認為這是個整數，當我們看到1031.2903的時候我們會認為這是個小數。而機器在處理資料或者存取資料的時候，是無差別的按照位元位進行二進位制運算或者讀寫的。人類很難做到直接用二進位制輸入計算機，當然也不能接受計算機直接以二進位制的形式輸出結果。設想一下，如果某天我們們想用一下電腦上的計算器，計算個1+1=2，但是我們沒有型別，我們需要理解機器是如何處理二進位制的，那麼就可能需要輸入00000000000000000000000000000001 + 00000000000000000000000000000001，而得到的結果也是二進位制00000000000000000000000000000010，這得多累人吶。有了型別就輕鬆多了，通過定義資料的型別，根據型別的約定，計算機就知道如何將這個1轉化為二進位制(包括：應該轉化為16位、32位還是64位的二進位制，對這段二進位制資料進行操作的時候，應該把它看作整數還是浮點數等等)，而返回結果的時候也就知道如何將二進位制的00000000000000000000000000000010轉化為我們能夠理解的整數2
程式語言的型別其實就是人與機器約定好的，去理解和運算元據的一套規則。
總而言之，在機器的眼裡，無論是對資料進行何種操作，它看到的都是一串一串由0和1構成的東西，稱呼這種東西有專門的術語，叫作**“位元組流”或者“二進位制流“**。
讓我們再一起看一個例子。假設要處理這樣的一段二進位制流：00000000100111011000001111010111，這段二進位制流可以表示很多東西，要明確它的含義，就需要明確它的型別，比如下面這兩種不同的型別，這段流表示的內容就完全不同。
- 如果我們把這段二進位制流看作是32位整型，那麼它代表的是10322903這個整數；
- 如果我們把這段二進位制流看作是2個16位整型(前16位
0000000010011101表示一個整型，後16位1000001111010111表示一個整型)，那麼它分別代表的是157和33751這兩個整數；
我知道你此刻對為何轉換為32位整型是10322903？為何看作2個16位整型轉換後是157和33751？還有著很多疑惑。但是關於二進位制和十進位制的轉換方法呢，在這裡就不做展開了，如果你很感興趣、很想知道可以再單獨給你講這個方法。講上面的這些，最主要的還是希望你明白，定義“型別”的概念，根本上是在人機互動的過程中提供了一種機制，賦予無差別的二進位制流一定的語義。
還是太抽象了對不對？沒關係，我們再來舉個例子。
前面我們在預備知識這一章中使用到了tb_stu_math_score這張表，為了不讓你辛苦的再翻回去，我們再貼一下這張表的內容啦。
| id(自增主鍵) | name(學生姓名) | number(學號) | grade(年級) | class(班級) | score(得分) |
|---|---|---|---|---|---|
| 1 | 柯南 | 010201 | 1 | 2 | 100 |
| 2 | 小哀 | 010202 | 1 | 2 | 100 |
| 3 | 光彥 | 010203 | 1 | 2 | 98 |
| 4 | 步美 | 010204 | 1 | 2 | 95 |
| 5 | 元太 | 010205 | 1 | 2 | 59 |
也寫過類似下面這條Sql語句。
SELECT score FROM tb_stu_math_score WHERE id=1;
複製程式碼這條Sql語句非常非常的簡單，現在我們已經知道它會返回第一行資料score這一列的值，結果長下面這樣。
| score |
|---|
| 100 |
讓我們分析一下獲取這個結果的整個流程，幫助你理解一下，型別是如何發揮作用的。
- 這條Sql語句會被執行，根據主鍵id找到對應的行，進而獲取到這一行、
score這列的值； - 但是我們上面說到，計算機的儲存是基於二進位制的，所以獲取到的
score值是類似於00000000000000000000000001100100這樣的二進位制流； - 這時候根據
tb_stu_math_score表的定義，score這一列被定義為整型，於是將二進位制流轉化為一個整型數，經過進位制轉換得到00000000000000000000000001100100對應的整型值為100，於是我們看到的結果就是100。
實際上反過來也非常類似，當我們向這張表中寫入資料時，例如寫入的score列的值為100。因為儲存基於二進位制，根據表的定義，score列的型別為整型，於是將值100按照整型轉換為對應的二進位制流00000000000000000000000001100100，並且寫入到庫中。
[2] Sql的主要資料型別有哪些？
Sql中常常接觸的資料型別主要包括幾類。
1 整型
tinyint：用來表示很小很小的整數，比如常常用它作為is_deleted、is_valid這些欄位的欄位型別，因為這兩個欄位表示該條記錄是否有效，只存在兩個值分別是0和1；smallint：比tinyint稍微大一點點的整型，可以表示更大一點的整數，比如200、404、401這樣的整數值；int：常用的整型，可以用來表示比較大的整數，比如10322(事實上int可以表示的整數範圍遠遠比這個大)；bigint：用來表示非常大的整數，比如大多數表的自增id就會使用這個型別，可以表示類似10322903這樣非常大的整數(事實上bigint可以表示的整數範圍遠遠比這個要大)；
2 浮點型
decimal：可以表示非常準確的小數，比如經緯度；
3 字串型別
char：固定長度的字串；varchar：可變長度的字串；
這裡固定長度和可變長度指的是資料庫中的儲存形式，因為這部分的內容其實有些超出了這個教程的範圍，我們不過多的解釋這裡的區別。一般在我們實際的應用中varchar用的更多一些。它們都表示類似於"very glad to meet u, Huohuo!"這樣的一串字元，當然也可以是中文"敲開心認識你，火火！"。
4 日期型別
date：表示一個日期，只包含日期部分，不包含時間，比如當前日期”2018-01-23″；datetime：表示一個日期，同時包含日期部分和時間部分，比如當前日期”2018-01-23 03:01:43″；
我們在這裡只是簡單的介紹了幾種Sql中常見的欄位型別，並沒有很深入的去解釋它們的原理、差異以及一些其他的資料型別，我們們不著急去學習那些“高大上”的內容，先理解這些型別的含義。
[3] 怎麼知道一張表中每一列的型別是什麼？
第1種方式是使用DESC 表名命令，例如我們想看一下之前提到的tb_rider表的每一列欄位型別，就可以執行命令DESC tb_rider，得到下面的結果。
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | int(11) | NO | PRI | < null> |
auto_increment |
| name | varchar(32) | NO | |||
| real_name_certify_state | int(11) | NO | 0 | ||
| is_deleted | tinyint(4) | NO | 0 | ||
| created_at | datetime | NO | MUL | CURRENT_TIMESTAMP | |
| updated_at | datetime | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| level | tinyint(4) | NO | 0 | ||
| level_city | varchar(32) | NO |
注意這裡的第一列表示欄位名稱，第二列Type則表示對應欄位的欄位型別。比如id欄位，是一個int型別。
第二種方式是使用SHOW CREATE TABLE 表名命令，例如SHOW CREATE TABLE tb_rider，得到下面的結果。
CREATE TABLE `tb_rider` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名', `real_name_certify_state` int(11) NOT NULL DEFAULT '0' COMMENT '身份證認證狀態', `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '該使用者是否還存在. 0: 不存在, 1: 存在', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間', `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間', `level` tinyint(4) NOT NULL DEFAULT '0' COMMENT '騎手等級：0普通 1銅牌 2銀牌 3金牌', `level_city` varchar(32) NOT NULL DEFAULT '' COMMENT '配送員等級城市', PRIMARY KEY (`id`), KEY `ix_created_at` (`created_at`), KEY `ix_updated_at` (`updated_at`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='配送員資訊';
複製程式碼我們以
`name` varchar(32) NOT NULL DEFAULT '' COMMENT '姓名'複製程式碼來解釋一下這裡的語句。
name是欄位名(列名)；varchar表示欄位型別為字串；NOT NULL表示這個欄位不能為空, 為空的意思是沒有指定任何值給這個欄位(注意不等價於空字串)；- “`DEFAULT ”`表示如果沒有指定這個欄位的值則使用空字串作為預設值；
COMMENT '姓名'是對這個欄位的備註，表示這個欄位的業務含義，只用做展示；
4.2 索引
索引絕對算得上是關係型資料庫中最關鍵同時也是最有難度的話題。即便是經驗豐富的研發同學，也經常會踩到索引的坑。不過我們這裡介紹索引，只是為了更好的服務於查詢，我會盡可能避免牽扯進一些複雜的概念和底層原理。
[1] 什麼是索引？
那麼到底什麼是索引呢？你可以把資料庫理解為一本很厚的書(假設有10萬頁)，書中的內容就是資料庫裡的資料，那麼索引就是書的目錄。 假設你從來沒有閱讀過這本書，此刻你想要閱讀書的第7章第2小節。如果沒有目錄，你可能需要翻閱整本書找到你要閱讀的內容。但是在有目錄的情況下，你就只需要先查一下目錄找到對應的頁碼，然後直接翻到那一頁就能看到你想看的內容了。索引也是類似的，首先查詢索引找到目標資料的位置，再從特定的位置讀取出資料的內容。
如何設計索引，是設計資料庫表的時候考慮的關鍵點之一。索引一般由表中的某一列或者某幾列構成，一旦設定某一列為索引，那麼之後每次在往表中寫入資料的時候，都會更新這一列到索引中去。事實上，索引在技術層面是比較複雜的，涉及到磁碟I/O、B樹、優化器(Optimizer)等很多技術概念，不過我們先不去深究這些。
[2] 為什麼索引很重要，它有什麼用？
索引之所以重要，最主要的原因是能夠大大提高查詢的速度。上面我們舉了書的例子，當這本書的頁數足夠大的時候(假設有2000萬頁)，如果沒有目錄，想要查閱其中的某一章節的內容，那幾乎就是天方夜譚了。資料庫也是如此，當表中的資料只有幾行或者幾十行、幾百行的時候，有沒有索引其實差別不大，但是當表中的資料非常非常多的時候(比如眾包的運單表，2000萬+ 行)，如果沒有索引，要找到某一條目標資料，查詢的速度就會非常非常非常的慢。
[3] 如何使用索引？
要使用索引非常簡單，只需要在WHERE條件中使用到索引列作為查詢條件，讓我們舉個例子。
| id | order_id | order_state | rider_id | rider_name | merchant_customer_distance | created_at |
|---|---|---|---|---|---|---|
| 1 | 300000201712300001 | 40 | 1 | Stark | 2.5 | 2017-12-30 12:34:17 |
| 2 | 300000201712300002 | 40 | 1 | Stark | 1.8 | 2017-12-30 12:34:18 |
| 3 | 300000201712300003 | 40 | 2 | Banner | 1.8 | 2017-12-30 13:20:02 |
| 4 | 300000201712300004 | 40 | 5 | Natasha | 2.7 | 2017-12-30 13:34:19 |
| 5 | 300000201712300005 | 40 | 1 | Stark | 1.2 | 2017-12-30 16:01:03 |
| 6 | 300000201712300006 | 40 | 3 | Rogers | 0.5 | 2017-12-30 16:08:57 |
| 7 | 300000201712310001 | 20 | 6 | Barton | 1.3 | 2017-12-31 09:12:07 |
| 8 | 300000201712310002 | 80 | 7 | Coulson | 2.9 | 2017-12-31 09:10:33 |
| 9 | 300000201712310003 | 80 | 2 | Banner | 0.7 | 2017-12-31 09:18:10 |
| 10 | 300000201712310004 | 20 | 3 | Rogers | 2.2 | 2017-12-31 10:34:01 |
| 11 | 300000201712310005 | 10 | 0 | 0.3 | 2017-12-31 19:29:02 | |
| 12 | 300000201712310006 | 10 | 0 | 1.3 | 2017-12-31 19:29:27 | |
| 13 | 300000201712310007 | 10 | 0 | 3.0 | 2017-12-31 19:30:01 |
還是這張tb_order表，假設這張資料表中order_id是索引列，那麼當我們以order_id作為查詢條件時，我們就利用了索引，比如下面這條Sql。
SELECT * FROM tb_order WHERE order_id = 300000201712310007;
複製程式碼當然啦，類似的使用order_id作為查詢條件的Sql也都會利用到索引，看看你是否都理解下面兩條Sql語句的含義。
1. SELECT * FROM tb_order WHERE order_id IN (300000201712310007, 300000201712310006) AND order_state = 40;
2. SELECT order_id, order_state FROM tb_order WHERE order_id >
= 300000201712300001 AND order_id <
= 300000201712300006 AND order_state = 40;
複製程式碼那麼如果一張表裡面不止一列是索引，而在查詢的Sql中這些索引列都作為了WHERE語句的查詢條件，會使用哪個列作為索引還是都使用？假設tb_order表中order_id和rider_id兩列都是索引列，那麼下面這條Sql語句會使用哪個作為索引呢？
SELECT * FROM tb_orderWHERE order_id >
= 300000201712310001AND order_id <
= 300000201712310007AND rider_id >
0;
複製程式碼答案是不確定的。使用哪個索引，甚至是否使用索引，從根本上來說是由優化器(Optimizer)決定的，它會分析多個索引的優劣，以及使用索引和不使用索引的優劣，然後選擇最優的方式執行查詢。這部分話題就太過複雜了，這裡不做展開。儘管有優化器(Optimizer)的存在，但是對於我們的查詢來說，能夠使用明確的索引欄位作為查詢條件的，就應該儘可能使用索引欄位。
[4] 索引的型別、如何確定表中的哪些列是索引列？
還記得欄位型別一節中提到的DESC 表名和SHOW CREATE TABLE 表名語法嗎？前面我們將這兩個語法用在了tb_rider表上，這一節讓我們看一看tb_order表。
首先是DESC tb_order，我們會得到下面的結果。
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | bigint(20) | NO | PRI | < null> |
auto_increment |
| order_id | bigint(20) | NO | UNI | 0 | |
| rider_id | int(11) | NO | 0 | ||
| rider_name | varchar(100) | NO | |||
| order_state | tinyint(4) | NO | 0 | ||
| is_deleted | tinyint(4) | NO | 0 | ||
| grabbed_time | timestamp | NO | CURRENT_TIMESTAMP | ||
| merchant_customer_distance | decimal(10,2) | NO | 0.00 | ||
| created_at | datetime | NO | MUL | CURRENT_TIMESTAMP | |
| updated_at | datetime | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
之前我們關注的是Type這一項，這裡讓我們關注Key這一項。我們看到有些列對應的Key是空的，這就表示這一列(或者叫這個欄位)不是索引列(或者叫索引欄位)。但id、order_id、created_at和updated_at這幾列對應的Key均是有值的，這說明這幾列都是索引列。但這幾列Key的值又各不相同，這是為啥吶？這是以內索引也分為不同的型別，讓我們逐個來解釋一下。
PRI：是primary的縮寫，標記這一列為主鍵，主鍵的概念我們在一開始的時候有介紹過，就是用來唯一標識表中每一行資料的索引；UNI: 是unique的縮寫，顧名思義就是唯一的意思, 被設定為UNI KEY的列，不允許出現重複的值，如果嘗試向表中插入這一列的值完全相同的兩行資料，則會引發報錯。我猜你肯定會覺得疑惑，那UNI KEY和PRI KEY有啥區別？首先是這兩種型別的索引在實現上是有區別的(這一點我們們不深究，涉及到了資料庫底層對索引的實現)，其次PRI KEY更多的是資料庫層面的語義，僅僅是描述資料的唯一性，而UNI KEY則更多是業務層面的語義，比如說這裡的order_id欄位，因為業務上不能存在兩個運單號完全相同的運單，所以需要把order_id這一列設定為UNI KEY；MUL：是multiple的縮寫，表示這一列是被設定為一個普通索引。之所以叫做multiple，是因為此時可能這一列單獨作為索引，也可能這一列和其他標記為MUL的列共同構成了一個索引(這種由多列共同構成的索引被叫作複合索引)；
現在我們還處在Sql以及資料庫知識(是的，除了Sql，我還偷偷介紹了一些資料庫原理)學習的初級階段，所以讓我們知道這寫差異，但是不著急去把這些搞得一清二楚，它們都是索引，只要合理使用，都可以幫助我們加快Sql查詢的效率。
另一種識別表中索引列的方法就是通過SHOW CREATE TABLE 表名命令，比如SHOW CREATE TABLE tb_order，我們得到下面的結果。
CREATE TABLE `tb_order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '對外不提供，內部使用', `order_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '運單的跟蹤號（可以對外提供）', `rider_id` int(11) NOT NULL DEFAULT '0' COMMENT '配送員id', `rider_name` varchar(100) NOT NULL DEFAULT '' COMMENT '配送員名字', `order_state` tinyint(4) NOT NULL DEFAULT '0' COMMENT '配送狀態', `is_deleted` tinyint(4) NOT NULL DEFAULT '0', `grabbed_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '搶單時間', `merchant_customer_distance` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '商鋪到顧客步行距離', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_order_id` (`order_id`), KEY `ix_created_at` (`created_at`), KEY `ix_updated_at` (`updated_at`)) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='配送單';
複製程式碼看到末尾幾行的PRIMARY KEY、UNIQUE KEY和KEY了嗎，它們就對應於DESC tb_order結果中的PRI、UNI和MUL，分別標識主鍵索引、唯一索引和普通索引。每一行括號內的欄位就表示對應的索引列。
4.3 JOIN語法家族
我嘗試了好幾種解釋清楚JOIN語法的方法(JOIN語法的確有些複雜)，始終不能讓我自己滿意，最終決定還是從一個例子開始。讓我們首先看一張新的表，建表語句長下面這樣。
CREATE TABLE `tb_grab_order_limit` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', `rider_id` BIGINT(20) NOT NULL DEFAULT 0 COMMENT '騎手id', `order_grab_limit` INT(11) NOT NULL DEFAULT '0' COMMENT '接單上限', `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT '該記錄是否被刪除', `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間', `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間', PRIMARY KEY(`id`), KEY `ix_rider_id` (`rider_id`), KEY `ix_created_at` (`created_at`), KEY `ix_updated_at` (`updated_at`)) ENGINE = InnoDB DEFAULT CHARSET=utf8 comment="自定義騎手接單上限表";
複製程式碼小溫習
參考上面的建表語句嘗試回答下面這幾個問題。
- 這張表的表名是什麼？
order_grab_limit這個欄位的含義是什麼？- 這張表的主鍵索引是什麼？有幾個唯一索引、幾個普通索引？
沒錯！這就是自定義騎手接單上限表。描述了某一個騎手(rider_id)對應的他的接單上限(order_grab_limit)。表中的資料如下。
| id | rider_id | order_grab_limit | is_deleted | created_at | updated_at |
|---|---|---|---|---|---|
| 1 | 1 | 11 | 0 | 2018-02-25 17:22:03 | 2018-02-25 17:22:03 |
| 2 | 2 | 9 | 0 | 2018-02-25 17:22:21 | 2018-02-25 17:22:21 |
| 3 | 4 | 9 | 0 | 2018-02-25 17:22:31 | 2018-02-25 17:22:31 |
| 4 | 6 | 7 | 0 | 2018-02-25 17:22:39 | 2018-02-25 17:22:39 |
| 5 | 10 | 8 | 0 | 2018-02-25 17:22:46 | 2018-02-25 17:22:46 |
再讓我們回顧一下前面反覆用到的tb_rider表。
| id | name | real_name_certify_state | level | level_city | is_deleted | created_at | updated_at |
|---|---|---|---|---|---|---|---|
| 1 | Stark | 2 | 3 | 1 | 0 | 2017-01-01 22:00:19 | 2018-01-01 06:40:01 |
| 2 | Banner | 2 | 3 | 9 | 0 | 2017-04-28 12:01:19 | 2018-01-01 06:40:01 |
| 3 | Rogers | 2 | 2 | 1 | 0 | 2017-04-10 17:24:01 | 2018-01-01 06:40:01 |
| 4 | Thor | 1 | 0 | 1 | 0 | 2017-12-31 23:10:39 | 2018-01-01 06:40:01 |
| 5 | Natasha | 2 | 1 | 1 | 0 | 2017-02-11 15:03:13 | 2018-01-01 06:40:01 |
| 6 | Barton | 2 | 1 | 9 | 0 | 2017-02-11 15:04:19 | 2018-01-01 06:40:01 |
| 7 | Coulson | 2 | 3 | 9 | 0 | 2017-01-03 23:00:22 | 2018-01-01 06:40:01 |
| 8 | Coulson | 1 | 0 | 2 | 0 | 2017-01-05 10:10:23 | 2018-01-01 06:40:01 |
(終於鋪墊完啦！)
[1] 從LEFT JOIN開始
以這兩張表為基礎，設想一個場景：假設要查詢tb_rider表中所有騎手對應的自定義接單上限。我們的Sql應該怎麼寫呢？
**思路1：**先查出tb_rider表中所有騎手id，再根據這些騎手id作為查詢條件，通過前面學習過的IN語法從tb_grab_order_limit表中查詢出所對應的自定義接單上限的記錄。
SELECT id FROM tb_rider;
複製程式碼和
SELECT rider_id, order_grab_limit FROM tb_grab_order_limitWHERE rider_id IN (1, 2, 3, 4, 5, 6, 7, 8);
複製程式碼思路1顯然是個Bad idea。但是思路1詮釋瞭解決這個查詢問題的基本要點。
- 我們最終想要的資料是需要結合
tb_rider和tb_grab_order_limit兩張表共同得出的； - 關聯這兩張資料表的條件是騎手id；
- 因為查詢的要求是：
tb_rider表中所有騎手，因此應該以tb_rider表中的騎手id作為查詢參考集合； - 不是所有
tb_rider表中的騎手都配置了自定義接單上限，思路1的查詢方案存在一個缺點，就是我們需要根據查詢結果，在邏輯上做一個轉換得知哪些騎手沒有配置自定義接單上限( 不在返回結果中的騎手)；
**思路2：**基於這幾個要點我們可以使用LEFT JOIN語法，下面是對應的Sql語句。
SELECT tb_rider.id, tb_grab_order_limit.order_grab_limitFROM tb_rider LEFT JOIN tb_grab_order_limitON tb_rider.id = tb_grab_order_limit.rider_id;
複製程式碼這裡先介紹一下JOIN語法的基本結構：表1 (INNER/LEFT/RIGHT/FULL) JOIN 表2 ON 表1.列1 = 表2.列2。JOIN關鍵字前後連線的是兩張需要關聯查詢的資料表，ON關鍵字後面跟著關聯的條件。一共有四種型別的JOIN，他們分別是INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。以例子中的LEFT JOIN為例，表1 LEFT JOIN 表2 ON 表1.列1 = 表2.列2的含義是，遍歷表1中的列1的值，如果表2中列2的值有和它相等的則展示對應的記錄，如果沒有表2.列2和表1.列1相等，則展示為null。
思路2的例子中，tb_rider LEFT JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_id的含義是，遍歷tb_rider表中id這一列(tb_rider表的id欄位業務含義就是騎手id)的值，尋找tb_grab_order_limit表中rider_id列的值和它相等的記錄，如果不存在則是null。
我們還看到SELECT語句的內容和我們之前使用的很類似，但又稍微有點不一樣，都是表名.列名的書寫形式。其實這主要是指明瞭欄位所屬的表，因為JOIN的兩張資料表中可能存在的相同名稱的列，例如tb_rider表和tb_grab_order_limit表都有id欄位，但含義截然不同，這樣寫更加明確。
最終思路2的結果如下。
| id | order_grab_limit |
|---|---|
| 1 | 11 |
| 2 | 9 |
| 4 | 9 |
| 6 | 7 |
| 7 | < null> |
| 8 | < null> |
| 5 | < null> |
| 3 | < null> |
我們看到騎手id=(7, 8, 5, 3)的幾個騎手沒有配置自定義的接單上限，但因為是LEFT JOIN，他們仍然會展示在查詢結果中，不過因為沒有接單上限的記錄，order_grab_limit的結果為null。
讓我們再回頭看一下表名.列名這個寫法。如果思路2中的Sql改成下面這樣，返回結果會變成什麼呢？
SELECT tb_grab_order_limit.rider_id, tb_grab_order_limit.order_grab_limitFROM tb_rider LEFT JOIN tb_grab_order_limitON tb_rider.id = tb_grab_order_limit.rider_id;
複製程式碼讓我們來分析一下。我們知道LEFT JOIN的返回結果集合是以它左側連線的資料表決定的，所以結果集仍然包含8條記錄，但是騎手id=(7, 8, 5, 3)這個騎手沒有對應的接單上限的配置，因此當我們展示這幾個騎手的tb_grab_order_limit.rider_id列的值的時候，類似於tb_grab_order_limit.order_grab_limit，也是null。因此結果是下面這樣。
| rider_id | order_grab_limit |
|---|---|
| 1 | 11 |
| 2 | 9 |
| 4 | 9 |
| 6 | 7 |
| < null> |
< null> |
| < null> |
< null> |
| < null> |
< null> |
| < null> |
< null> |
如果你還是不太明白，然我們在SELECT的時候，加上tb_rider.id，或許有助於理解。
SELECT tb_rider.id, tb_grab_order_limit.rider_id, tb_grab_order_limit.order_grab_limitFROM tb_rider LEFT JOIN tb_grab_order_limitON tb_rider.id = tb_grab_order_limit.rider_id;
複製程式碼結果是。
| id | rider_id | order_grab_limit |
|---|---|---|
| 1 | 1 | 11 |
| 2 | 2 | 9 |
| 4 | 4 | 9 |
| 6 | 6 | 7 |
| 7 | < null> |
< null> |
| 8 | < null> |
< null> |
| 5 | < null> |
< null> |
| 3 | < null> |
< null> |
[2] LEFT JOIN的姊妹篇：RIGHT JOIN
前面我們知道LEFT JOIN是以連線的左側表作為查詢的結果集的依據，RIGHT JOIN則是以連線的右側表作為依據。讓我們考慮另一個場景：假設想要查詢所有設定了自定義接單上限的騎手姓名。應該如何寫這個Sql呢？
先在聰明的大腦裡思考幾分鐘。此時你需要類比LEFT JOIN，需要理解上一段內容講述的LEFT JOIN知識點，可能需要回到上一段再看一看示例Sql語句以及對應的結果。沒關係，一開始學習的時候慢慢來。
答案是這樣的。
SELECT tb_grab_order_limit.rider_id, tb_rider.nameFROM tb_rider RIGHT JOIN tb_grab_order_limitON tb_rider.id = tb_grab_order_limit.rider_id;
複製程式碼對應的查詢結果則是。
| rider_id | name |
|---|---|
| 1 | Stark |
| 2 | Banner |
| 4 | Thor |
| 6 | Barton |
| 10 | < null> |
如果這個結果和你腦海中思考的結果不一樣，不要著急，讓我們再來解釋一下。RIGHT JOIN是以連線的右側表為依據，而tb_grab_order_limit中的騎手id=(1, 2, 4, 6, 10)，其中騎手id為10的騎手在tb_rider表中是沒有的，所以name為null。
小測驗
嘗試下將上面的這條Sql語句改寫成LEFT JOIN吧(要求得到相同的查詢結果)？
[3] 一絲不苟的INNER JOIN
之所以叫“一絲不苟”的INNER JOIN，是因為INNER JOIN是非常嚴格的關聯查詢，換句話說，必須是根據JOIN條件兩張表中存在匹配記錄的才作為結果集返回。讓我們回顧下[1]中LEFT JOIN的Sql。
SELECT tb_rider.id, tb_grab_order_limit.order_grab_limitFROM tb_rider LEFT JOIN tb_grab_order_limitON tb_rider.id = tb_grab_order_limit.rider_id;
複製程式碼它的返回結果是。
| id | order_grab_limit |
|---|---|
| 1 | 11 |
| 2 | 9 |
| 4 | 9 |
| 6 | 7 |
| 7 | < null> |
| 8 | < null> |
| 5 | < null> |
| 3 | < null> |
如果我們將LEFT JOIN改為INNER JOIN吶？修改後的Sql像這樣。
SELECT tb_rider.id, tb_grab_order_limit.order_grab_limitFROM tb_rider INNER JOIN tb_grab_order_limitON tb_rider.id = tb_grab_order_limit.rider_id;
複製程式碼這時返回的查詢結果變成了。
| id | order_grab_limit |
|---|---|
| 1 | 11 |
| 2 | 9 |
| 4 | 9 |
| 6 | 7 |
這是因為INNER JOIN會遍歷連線一側的表，根據ON後的連線條件，和連線另一側的表進行比較，只有兩張表中存在匹配的記錄才會作為結果集返回。例如這裡，它會遍歷tb_rider表中id欄位的值，並且去tb_grab_order_limit表中尋找rider_id與之匹配的記錄，如果找到則作為結果返回。
B.T.W INNER JOIN 和 JOIN是等價的，換句話說，
表1 INNER JOIN 表2 ON...和表1 JOIN 表2 ON...是完全等價的。
小測驗
猜測一下下面的這條Sql語句的返回結果是什麼？
SELECT tb_rider.id, tb_grab_order_limit.order_grab_limitFROM tb_grab_order_limit INNER JOIN tb_riderON tb_grab_order_limit.rider_id = tb_rider.id;
複製程式碼提示：這裡交換了一下INNER JOIN連線的兩張表的位置，根據INNER JOIN的特性，查詢結果會有影響嘛？
[4] 心大的FULL JOIN
FULL JOIN其實並不在乎匹配與否，而是將連線的兩張表中所有的行都返回，如果有匹配的則返回匹配的結果，如果沒有匹配則哪張表中缺失則對應的將當前這條記錄標記為null。看一個例子就明白啦！
SELECT tb_rider.id, tb_rider.name, tb_grab_order_limit.rider_id, tb_grab_order_limit.order_grab_limitFROM tb_rider FULL JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_id;
複製程式碼這條Sql語句的查詢結果是這樣的。
| id | name | rider_id | order_grab_limit |
|---|---|---|---|
| 1 | Stark | 1 | 11 |
| 2 | Banner | 2 | 9 |
| 4 | Thor | 4 | 9 |
| 6 | Barton | 6 | 7 |
| 3 | Rogers | < null> |
< null> |
| 5 | Natasha | < null> |
< null> |
| 7 | Coulson | < null> |
< null> |
| 8 | Coulson | < null> |
< null> |
| < null> |
< null> |
10 | 10 |
可以看到tb_rider表中騎手id=(3, 5, 7, 8)的騎手在tb_grab_order_limit表中沒有匹配的記錄，而tb_grab_order_limit表中騎手id=(10)的騎手在tb_rider表中沒有匹配記錄，但是它們都作為結果集返回了。只不過缺失tb_grab_order_limit記錄的，rider_id和order_grab_limit欄位值為null，而缺失tb_rider記錄的，id和name欄位的值為null。
事實上，絕大多數情況下，FULL JOIN都不會被用到。而且在一些資料庫管理系統中，例如MySql(我們的線上環境主要使用的就是MySql)，是不支援FULL JOIN語法的。對於上面的查詢語句，需要使用一些技巧通過LEFT JOIN、RIGHT JOIN以及UNION(這篇教程中我們不討論UNION語法哦)語法的組合來實現同樣效果的查詢。
SELECT tb_rider.id, tb_rider.name, tb_grab_order_limit.rider_id, tb_grab_order_limit.order_grab_limitFROM tb_rider LEFT JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_idUNIONSELECT tb_rider.id, tb_rider.name, tb_grab_order_limit.rider_id, tb_grab_order_limit.rider_idFROM tb_rider RIGHT JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_idWHERE tb_rider.id IS null;
複製程式碼這已經超出了這篇教程的討論範圍啦！如果想要挑戰一下自己，以下是一些提示。
- UNION連線兩條SELECT語句，作用是將兩個SELECT語句的查詢結果取交集；
- 第2條SELECT語句中的
WHERE tb_rider.id IS null是為了對存在匹配的資料記錄去重(否則UNION之後會有重複的結果)； - WHERE語句是在RIGHT JOIN之後，UNION之前執行的；
試著在這兩條提示下理解一下這條Sql語句，如果能夠弄明白這條語句是如何等價於FULL JOIN的，那麼說明你對JOIN家族的語法已經基本掌握啦。如果暫時還不能弄得非常明白也沒關係，多看一看例子，多寫一寫實踐一下，慢慢就會明白啦。
題外話
從上面的講解我們瞭解到JOIN的四種用法，總結一下。
- INNER JOIN關鍵字在兩張表中都有匹配的值的時候返回匹配的行；
- LEFT JOIN關鍵字從左表返回所有的行，即使在右表中沒有匹配的行；
- RIGHT JOIN關鍵字從右表返回所有的行，即使在左表中沒有匹配的行；
- FULL JOIN關鍵字從左表和右表那裡返回所有行，即使右表的行在左表中沒有匹配或者左表的行在右表中沒有匹配，這些行也會返回；
不過這些都是刻板的文字總結，讓我們換個視角總結一下這集中JOIN語法。
離散數學中在討論集合論的時候介紹過**“韋恩圖”**的概念，它清楚的描述了資料集合之間的關係。而JOIN的這4種操作也正好對應了4種集合運算，下面的這張圖(Figure 1)很清楚的描述了這種關係。
4.4 巢狀的SELECT語法
再來看一下講述LEFT JOIN的開始，我們提到的那個例子：查詢tb_rider表中所有騎手對應的自定義接單上限。當時我們首先提出了思路1，是分為2個步驟的。
SELECT id FROM tb_rider;
複製程式碼和
SELECT rider_id, order_grab_limit FROM tb_grab_order_limitWHERE rider_id IN (1, 2, 3, 4, 5, 6, 7, 8);
複製程式碼我們說這個思路不好，這是顯然的，因為在現實場景中往往資料集合都很大(例如這裡的rider_id在現實中可能是成百上千甚至成千上萬個)，思路本身沒有問題但無法操作執行。所以在4.3節我們選擇通過JOIN語法來實現同樣的查詢。那是不是思路1就真的只能是個紙上談兵的思路了呢？當然不是啦！我們還可以使用巢狀的SELECT語句，就像這樣。
SELECT rider_id, order_grab_limit FROM tb_grab_order_limitWHERE rider_id IN (SELECT id FROM tb_rider);
複製程式碼這個寫法非常好理解，WHERE rider_id IN (SELECT id FROM tb_rider)首先執行括號中的語句SELECT id FROM tb_rider，然後執行IN篩選，就是我們的思路1描述的那樣。於是得到下面的結果。
| rider_id | order_grab_limit |
|---|---|
| 1 | 11 |
| 2 | 9 |
| 4 | 9 |
| 6 | 7 |
複習題
回想一下上面的結果和以下哪條Sql語句的執行結果是一致的呢？為什麼是一致的，為什麼和其他的不一致？
1. SELECT tb_rider.id, tb_grab_order_limit.order_grab_limit FROM tb_rider LEFT JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_id;
2. SELECT tb_grab_order_limit.rider_id, tb_rider.name FROM tb_rider RIGHT JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_id;
3. SELECT tb_rider.id, tb_grab_order_limit.order_grab_limit FROM tb_rider INNER JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_id;
4. SELECT tb_rider.id, tb_grab_order_limit.order_grab_limit FROM tb_rider FULL JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_id;
複製程式碼小測驗
思考一下以下這個場景，看看能否寫出它對應的Sql語句？
場景：篩選出所有通過實名認證(real_name_certify_state=2)的金牌(level=3)騎手(tb_rider表)，在2017-12-30當天(created_at >)所跑運單(
= xxx AND created_at <
yyytb_order表)的運單號(order_id)。
想一想有幾種寫法呢？
5 闖關答題：快速複習
前面的幾個段落我們學習了Sql查詢中最常用，而且特別好用的語法知識，讓我們簡單總結一下。
- 資料庫、資料表的概念；
- 最基本的Sql查詢結構；
- IN查詢和邏輯操作語法(AND/OR)；
- 對查詢結果進行排序和LIMIT語法；
- 聚合函式(COUNT/AVG/SUM)和DISTINCT語法；
- 對查詢結果分組(GROUP BY)；
- 對聚合函式的結果進行篩選的HAVING語法；
- 欄位型別和索引的概念和作用；
- JOIN語法的一家子(LEFT JOIN/RIGHT JOIN/INNER JOIN/FULL JOIN)；
- 巢狀的SELECT語法；
學習了這麼多知識點，實在是太膩害了！給自己點贊！
但是(凡事都有個但是)…
想要把這些知識點融會貫通，靈活應用到現實工作中更多變、更復雜的查詢場景，僅僅是“學會”是不夠的，還需要更多的“練習”和“回味”。
這個部分我設計了一個“闖關答題”專案，通過思考和回答這些闖關題，幫助你更好的掌握上面提到的知識點。
先來看一下答題將要用到的資料表。
[1] 商品資料表：tb_product
| id | product_id | name | price |
|---|---|---|---|
| 1 | 1001 | iPad Pro 10.5 64G WLAN | 4888 |
| 2 | 1002 | Macbook Pro 2017 13.3 i5/8G/256GB | 13888 |
| 3 | 1003 | iPhone X 64G | 8388 |
建表語句：
CREATE TABLE `tb_product` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', `product_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商品id', `name` varchar(100) NOT NULL DEFAULT '' COMMENT '商品名稱', `price` int(11) NOT NULL DEFAULT '0' COMMENT '商品價格', PRIMARY KEY (`id`), UNIQUE KEY `uk_product_id` (`product_id`)) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='商品資訊表';
複製程式碼欄位含義：
id：自增主鍵；product_id：商品id；name：商品名稱；price：商品單價，單位是元；
[2] 使用者資料表：tb_customer
| id | customer_id | name | gender | balance |
|---|---|---|---|---|
| 1 | NO100001 | 火火 | 女 | 18888 |
| 2 | NO100002 | 撥潑抹 | 女 | 9000 |
| 3 | NO100003 | 艾橋 | 男 | 7990 |
| 4 | NO100004 | 水娃 | 女 | 8388 |
建表語句：
CREATE TABLE `tb_customer` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', `customer_id` varchar(100) NOT NULL DEFAULT '' COMMENT '使用者id', `name` varchar(100) NOT NULL DEFAULT '' COMMENT '使用者姓名', `gender` varchar(30) NOT NULL DEFAULT '' COMMENT '使用者性別', `balance` int(11) NOT NULL DEFAULT '0' COMMENT '賬戶餘額', PRIMARY KEY (`id`), UNIQUE KEY `uk_customer_id` (`customer_id`)) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='使用者資訊表';
複製程式碼欄位含義：
id：自增主鍵；customer_id：使用者id；name：使用者姓名；gender：使用者的性別；balance：使用者當前的可用賬戶餘額，單位是元；
[3] 訂單資料表：tb_order
| id | order_id | customer_id | product_id | quantity |
|---|---|---|---|---|
| 1 | NUM1000301 | NO100001 | 1001 | 1 |
| 2 | NUM1000302 | NO100001 | 1002 | 2 |
| 3 | NUM1000303 | NO100002 | 1002 | 2 |
| 4 | NUM1000304 | NO100003 | 1002 | 1 |
| 5 | NUM1000305 | NO100001 | 1003 | 1 |
建表語句：
CREATE TABLE `tb_order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', `order_id` varchar(100) NOT NULL DEFAULT '' COMMENT '訂單id', `customer_id` varchar(100) NOT NULL DEFAULT '0' COMMENT '使用者id', `product_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商品id', `quantity` int(11) NOT NULL DEFAULT '0' COMMENT '商品價格', PRIMARY KEY (`id`), UNIQUE KEY `uk_order_id` (`order_id`)) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='訂單資料表';
複製程式碼欄位含義：
id：自增主鍵；order_id：訂單號；customer_id：下單使用者id；product_id：購買的商品id；quantity：購買的數量；
瞭解完需要用到表結構，我們就要開始答題啦！
第一關：查詢賬戶餘額大於1萬元的使用者id和姓名？
Answer:
SELECT customer_id, name FROM tb_customer WHERE balance >
10000;
複製程式碼| customer_id | name |
|---|---|
| NO100001 | 火火 |
第二關：查詢賬戶餘額小於1萬元且性別為女生的使用者姓名？
Answer:
SELECT name FROM tb_customer WHERE balance <
10000 AND gender="女";
複製程式碼| name |
|---|
| 撥潑抹 |
| 水娃 |
第三關：查詢使用者id為NO100001和NO100002的使用者，所有購買記錄的訂單號？
Hint：IN
Answer:
SELECT order_id FROM tb_order WHERE customer_id IN ("NO100001", "NO100002");
複製程式碼| order_id |
|---|
| NUM1000301 |
| NUM1000302 |
| NUM1000303 |
| NUM1000305 |
第四關：查詢使用者id為NO100001、NO100002兩位使用者所有的購買記錄(所有欄位)，要求按照優先以商品id遞增、其次以訂單號遞減的規則展示資料？
Hint：IN、ORDER BY
Answer:
SELECT * FROM tb_order WHERE customer_id IN ("NO100001", "NO100002")ORDER BY product_id ASC, order_id DESC;
複製程式碼| id | order_id | customer_id | product_id | quantity |
|---|---|---|---|---|
| 1 | NUM1000301 | NO100001 | 1001 | 1 |
| 3 | NUM1000303 | NO100002 | 1002 | 2 |
| 2 | NUM1000302 | NO100001 | 1002 | 2 |
| 5 | NUM1000305 | NO100001 | 1003 | 1 |
第五關：查詢性別為女生的使用者總數？
Hint：COUNT
Answer:
SELECT COUNT(customer_id) FROM tb_customer WHERE gender="女";
複製程式碼| COUNT(customer_id) |
|---|
| 3 |
第六關：查詢NO100001、NO100002、NO100003三位使用者各自購買商品的總數(不區分商品型別)，輸出購買商品件數大於等於2件的使用者id以及他們對應購買的商品總數？
Warning：“購買商品的總數”和上一關“女生使用者的總數”，這兩個**“總數”**一樣嗎？
Hint：IN、SUM、HAVING
Answer:
SELECT customer_id, SUM(quantity) FROM tb_orderWHERE customer_id IN ("NO100001", "NO100002", "NO100003")GROUP BY customer_idHAVING SUM(quantity) >
= 2;
複製程式碼| customer_id | SUM(quantity) |
|---|---|
| NO100001 | 4 |
| NO100002 | 2 |
第七關：查詢NO100001、NO100002、NO100003三位使用者各自購買商品的總數(不區分商品型別)，輸出購買總數前兩名的使用者id以及他們對應購買的商品總數？
Hint：IN、SUM、ORDER BY、LIMIT
Answer:
SELECT customer_id, SUM(quantity) FROM tb_orderWHERE customer_id IN ("NO100001", "NO100002", "NO100003")GROUP BY customer_idORDER BY SUM(quantity) DESCLIMIT 2;
複製程式碼| customer_id | SUM(quantity) |
|---|---|
| NO100001 | 4 |
| NO100002 | 2 |
第八關：查詢所有使用者各自購買商品的總數(不區分商品型別)，輸出購買商品件數大於等於2件的使用者id以及他們對應購買的商品總數？要求給出至少兩種寫法。
Warning：注意是“所有使用者”，不是所有的使用者都購買了商品
Hint：關聯查詢有哪些方法？
Answer:
寫法一：巢狀的SELECT
SELECT customer_id, SUM(quantity) FROM tb_orderWHERE customer_id IN (SELECT customer_id FROM tb_customer)GROUP BY customer_idHAVING SUM(quantity) >
= 2;
複製程式碼| customer_id | SUM(quantity) |
|---|---|
| NO100001 | 4 |
| NO100002 | 2 |
寫法二：使用LEFT JOIN語法
SELECT tb_customer.customer_id, SUM(tb_order.quantity) FROM tb_customerLEFT JOIN tb_order ON tb_customer.customer_id = tb_order.customer_idGROUP BY tb_customer.customer_idHAVING SUM(tb_order.quantity) >
= 2;
複製程式碼| customer_id | SUM(tb_order.quantity) |
|---|---|
| NO100001 | 4 |
| NO100002 | 2 |
第九關：查詢所有使用者各自購買商品的總數(不區分商品型別)，輸出購買總數前兩名的使用者id以及他們對應購買的商品總數？要求給出至少兩種寫法。
Hint：關聯查詢有哪些方法？
Answer:
寫法一：巢狀的SELECT
SELECT customer_id, SUM(quantity) FROM tb_orderWHERE customer_id IN (SELECT customer_id FROM tb_customer)GROUP BY customer_idORDER BY SUM(quantity) DESCLIMIT 2;
複製程式碼| customer_id | SUM(quantity) |
|---|---|
| NO100001 | 4 |
| NO100002 | 2 |
寫法二：使用LEFT JOIN語法
SELECT tb_customer.customer_id, SUM(tb_order.quantity) FROM tb_customerLEFT JOIN tb_order ON tb_customer.customer_id = tb_order.customer_idGROUP BY tb_customer.customer_idORDER BY SUM(tb_order.quantity) DESCLIMIT 2;
複製程式碼| customer_id | SUM(tb_order.quantity) |
|---|---|
| NO100001 | 4 |
| NO100002 | 2 |
第十關：以下哪幾條Sql語句使用到了索引？分別是哪些欄位上的索引？是什麼型別的索引？
1. SELECT name FROM tb_customer WHERE customer_id = 1001;
2. SELECT product_id, name FROM tb_product WHERE price >
5000;
3. SELECT order_id, customer_id, product_id FROM tb_order WHERE order_id = "NUM1000302" AND customer_id = "NO100001" AND product_id = "1002";
4. SELECT order_id FROM tb_order WHERE id >
2;
複製程式碼Hint：索引
Answer:
| sql序號 | 是否使用到索引 | 索引所在欄位 | 索引型別 |
|---|---|---|---|
| 1 | 是 | customer_id | UNIQUE KEY |
| 2 | 否 | – | – |
| 3 | 是 | order_id | UNIQUE KEY |
| 4 | 是 | id | PRIMARY KEY |
閱讀部落格還不過癮？
歡迎大家掃二維碼通過新增群助手，加入交流群，討論和部落格有關的技術問題，還可以和博主有更多互動
部落格轉載、線下活動及合作等問題請郵件至 shadowfly_zyl@hotmail.com 進行溝通