很可惜 T 。T 您現(xiàn)在還不是作者身份，不能自主發(fā)稿哦～
如有投稿需求，請(qǐng)把文章發(fā)送到郵箱tougao@appcpx.com，一經(jīng)錄用會(huì)有專人和您聯(lián)系
咨詢?nèi)绾纬蔀榇河鹱髡哒?qǐng)聯(lián)系：鳥哥筆記小羽毛（ngbjxym）
Preface
為什么以《寫給運(yùn)營同學(xué)和初學(xué)者的Sql入門教程》為題？
這原本是給一位關(guān)系要好的運(yùn)營同學(xué)定制的Sql教程。在餓了么，總部運(yùn)營的同學(xué)在排查、跟蹤線上問題和做運(yùn)營決策的時(shí)候，除了通過運(yùn)營管理系統(tǒng)查詢信息和依賴數(shù)據(jù)分析師給出的分析數(shù)據(jù)，常常也需要直接從數(shù)據(jù)庫管理臺(tái)通過寫Sql的方式獲取更細(xì)致、更實(shí)時(shí)的業(yè)務(wù)數(shù)據(jù)，并基于這些數(shù)據(jù)進(jìn)行一些及時(shí)的分析，從而更快的給出運(yùn)營方案。在這樣的背景下，Sql已經(jīng)越來越成為我們運(yùn)營同學(xué)的一項(xiàng)必備技能。網(wǎng)上有很多Sql教程(e.g. w3school)，我也翻閱過一些運(yùn)營同學(xué)桌上的Sql紙質(zhì)書，這些教程都很好，但普遍側(cè)重介紹語法，很多很多的語法，配以簡短的demo。作為老司機(jī)的reference book很贊，可是對(duì)于剛?cè)腴T甚至還沒有入門的學(xué)習(xí)者，就未免困難了一點(diǎn)。再回顧運(yùn)營同學(xué)的使用場(chǎng)景，大多數(shù)情況下是根據(jù)一些已有條件做一些簡單的查詢，偶爾會(huì)有一些相對(duì)復(fù)雜的查詢，比如對(duì)查詢結(jié)果做一些聚合、分組、排序，或是同時(shí)查詢兩三張數(shù)據(jù)表，除此以外，建表、建索引、修改表字段、修改字段值等等這些操作，在運(yùn)營同學(xué)的日常工作中基本是不會(huì)遇到的。
基于以上種種原因，寫了這篇教程，初衷是能夠幫助這位好朋友以更高的ROI入門Sql。下面是寫這篇教程時(shí)的一些考量：
建議所有閱讀教程的同學(xué)，都嘗試搭建一套自己的數(shù)據(jù)庫服務(wù)(建議安裝MySQL)，對(duì)教程中的demo多做一些練習(xí)，不論是示例、小測(cè)驗(yàn)還是小溫習(xí)里面的Sql語句，都不妨親自執(zhí)行一下，這也是一種很好的幫助你熟悉語法的方式。當(dāng)然搭建自己的數(shù)據(jù)庫會(huì)是一個(gè)不小的挑戰(zhàn)，寫作這篇教程的時(shí)候，我在自己的VPS上安裝了MySQL(MariaDB)并提供了一個(gè)連接腳本(隱藏了連接MySQL的過程)給朋友使用，但是這種方式并不適合推廣到所有人。具體的安裝和使用方式，不在本教程的敘述范圍內(nèi)，所以...運(yùn)營妹子們可以求助下熟悉的研發(fā)同學(xué)，漢子們嘛..
可以從這里sql_tutorial下載通過pandoc+latex導(dǎo)出的pdf，獲得更好的閱讀體驗(yàn)。
我也會(huì)在這次開放日活動(dòng)中分享Gunicorn有關(guān)的話題，歡迎大家報(bào)名參加。放出一張分享內(nèi)容的Outline：
by 多肉
Introduction
其實(shí)Sql并沒有那么難。Sql是幫助你和關(guān)系型數(shù)據(jù)庫交互的一套語法，主要支持的操作有4類：
聽起來挺嚇人的對(duì)吧，但實(shí)際上DML、DDL、DCL這3類操作在日常的運(yùn)營工作中幾乎都不會(huì)用到，經(jīng)常會(huì)使用到的吶其實(shí)是第一種，也就是數(shù)據(jù)查詢操作(DQL)。Sql基本的查詢語法也比較簡單，那么難在哪里呢？我猜想難在學(xué)習(xí)了基本語法之后，不知道怎么應(yīng)用到實(shí)際的Case上。在接下來的內(nèi)容里，我將以一些十分接近現(xiàn)實(shí)的眾包運(yùn)營Case為例，逐一解釋最基本的Sql查詢語法并且分析如何將它應(yīng)用到具體的場(chǎng)景上。
1 預(yù)備知識(shí)
好的吧，吹了一波牛還是逃不過需要介紹一些最基礎(chǔ)的東西，但是我保證這是整篇教程中最枯燥的部分，后面就會(huì)有趣很多。
1.1 數(shù)據(jù)庫和數(shù)據(jù)表
為了更簡單的理解這兩個(gè)概念以及他們之間的關(guān)系，可以這么類比：
所以，“關(guān)系型數(shù)據(jù)庫”的概念很嚇唬人，但其實(shí)道理很簡單，就是列和列之間有一定的聯(lián)系，整合在一起就是一條有意義的數(shù)據(jù)，將這些數(shù)據(jù)歸納起來就構(gòu)成了一張表，而將一批有關(guān)聯(lián)的表一同管理起來就得到了一個(gè)數(shù)據(jù)庫。
1.2 最基本的Sql查詢語法
最基本的Sql查詢語法其實(shí)就一個(gè)：
SELECT 列名(或者*，表示所有列) FROM 表名 WHERE 篩選條件;
讓我們按照 FROM、 WHERE、 SELECT的順序理解一下這個(gè)語法：
串聯(lián)起來便是，從FROM后面指定的數(shù)據(jù)表中，篩選出滿足WHERE后面指定條件的數(shù)據(jù)，并且展示SELECT后指定的這幾列字段。是不是很簡單吶？不過好像抽象了一點(diǎn)。所以我們來看幾個(gè)具體的超簡單的例子。假設(shè)我們有一張學(xué)生數(shù)學(xué)期末考試成績表，數(shù)據(jù)表長下面這樣，表名叫作 tb_stu_math_score。
id(自增主鍵)name(學(xué)生姓名)number(學(xué)號(hào))grade(年級(jí))class(班級(jí))score(得分)1柯南010201121002小哀010202121003光彥01020312984步美01020412955元太0102051259
讓我們?cè)囍斫庖幌孪旅鎺讉€(gè)查詢語句：
[1] SELECT name FROM tb_stu_math_score WHERE score>=95;
從 tb_stu_math_score表中挑選出得分大于95分的學(xué)生姓名，得到的結(jié)果顯而易見：
| name | | ---- | | 柯南 | | 小哀 | | 光彥 | | 步美 |
[2] SELECT name,number FROM tb_stu_math_score WHERE score<60;
從 tb_stu_math_score表中挑選出得分小于60分的學(xué)生姓名，得到的結(jié)果是：
namenumber元太010205
[3] SELECT*FROM tb_stu_math_score WHERE score=100;
從 tb_stu_math_score表中挑選出得分為100分學(xué)生的所有信息(注意SELECT后面的*符號(hào)，表示所有字段)，得到的結(jié)果是：
idnamenumbergradeclassscore1柯南010201121002小哀01020212100
小測(cè)驗(yàn)
看看下面這些Sql查詢語句你是不是知道是什么含義并且知道查詢結(jié)果是什么了呢？
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 更進(jìn)一步
剛剛我們學(xué)習(xí)了Sql查詢的最最最最基礎(chǔ)的語法，但是相信我，所有的Sql查詢幾乎都長這個(gè)樣子，所以理解了這個(gè)最基礎(chǔ)的語法結(jié)構(gòu)，后面學(xué)習(xí)起來就輕松多了。接下來讓我通過一些例子，擴(kuò)展這個(gè)基礎(chǔ)語法，教你一些更加高級(jí)的Sql查詢操作。不過首先，我們還是要看一下接下來我們的范例數(shù)據(jù)表長啥樣。
假設(shè)我們有一張騎手?jǐn)?shù)據(jù)表，表名叫作 tb_rider，還有一張運(yùn)單數(shù)據(jù)表，表名叫作 tb_order，這兩張表分別長下面這個(gè)樣子。
[1] 騎手?jǐn)?shù)據(jù)表： tb_rider
字段含義：
[2] 運(yùn)單數(shù)據(jù)表： tb_order
字段含義：
小溫習(xí)
試著理解看看下面這幾條Sql的含義以及返回的數(shù)據(jù)結(jié)果吧？
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 操作
場(chǎng)景： 線下反饋了一批騎手說自己理應(yīng)是上海的金牌，但是牌級(jí)是普通或者展示的是金牌卻沒有享受到上海的金牌活動(dòng)，你已經(jīng)知道了這幾個(gè)分別是id=(2, 4, 7)的騎手，想排查一下他們的等級(jí)更新情況。
這時(shí)你可以選擇像這樣一條一條的查詢，像之前我們介紹的那樣：
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;
這樣當(dāng)然可以達(dá)到目的，但是只有兩三個(gè)騎手的時(shí)候還勉強(qiáng)可以操作，如果有幾十個(gè)騎手這樣查起來就太費(fèi)勁了。這時(shí)候我們可以使用 IN這個(gè)語法。
SELECT name, real_name_certify_state, level, level_city FROM tb_rider WHERE id IN(2, 4, 7);
很簡單的對(duì)吧？但我們還是來簡單理解一下， WHERE id IN(2,4,7)的意思就是篩選id字段的值在2，4，7這幾個(gè)值當(dāng)中的記錄，執(zhí)行這條Sql語句你就會(huì)得到下面這樣的結(jié)果。
namereal_name_certify_statelevellevel_cityBanner239Thor101Coulson239
于是你會(huì)發(fā)現(xiàn)，Thor這個(gè)騎手因?yàn)樗麤]有通過實(shí)名認(rèn)證所以肯定評(píng)不上金牌，Banner和Coulson兩位騎手雖然都是金牌騎手，但是等級(jí)城市卻是福州，所以享受不到上海金牌的活動(dòng)。
那如果不知道騎手id，只知道騎手的名字怎么辦？也可以使用 IN查詢，只是這時(shí)候篩選的條件變成了 name，取值范圍也變成了"Banner", "Thor", "Coulson"。就像這樣。
SELECT name, real_name_certify_state, level, level_city FROM tb_rider
WHERE name IN("Banner", "Thor", "Coulson");
于是你順利的得到了以下的結(jié)果。
namereal_name_certify_statelevellevel_cityBanner239Thor101Coulson239Coulson102
Oops! 居然有兩個(gè)Coulson！
這就是在實(shí)際應(yīng)用中要特別注意的地方了：
2.2 關(guān)系運(yùn)算符：AND 和 OR
最常用的關(guān)系運(yùn)算符有兩個(gè) AND和 OR，用來連接多個(gè)篩選條件。顧名思義， AND就是“并且”的意思，也就是同時(shí)滿足 AND前后兩個(gè)篩選條件； OR就是“或者”的意思，也就是滿足 OR前后任何一個(gè)篩選條件。有點(diǎn)抽象了對(duì)不對(duì)，我們看一個(gè)具體的例子。
場(chǎng)景： 假設(shè)你想要看看2017-02-01(包括2017-02-01當(dāng)天)到2017-06-01(不包括2017-06-01當(dāng)天)期間注冊(cè)的騎手所有信息。
注冊(cè)時(shí)間對(duì)應(yīng)到數(shù)據(jù)上就是騎手信息的創(chuàng)建時(shí)間( created_at)，換句話說，就是查詢 tb_rider``表中創(chuàng)建時(shí)間處于2017-02-01到2017-06-01之間的數(shù)據(jù)。那這樣的Sql應(yīng)該怎么寫呢，這時(shí)我們就可以用到AND```。
SELECT * FROM tb_rider WHERE created_at >= "2017-02-01 00:00:00" AND created_at < "2017-06-01 00:00:00";
讓我們?cè)賮硗茝V一下。假設(shè)現(xiàn)在的場(chǎng)景變成：想看一看2017-02-01(包括當(dāng)天)之前，或者2017-06-01(包括當(dāng)天)之后注冊(cè)的騎手所有信息。我們應(yīng)該怎么寫這個(gè)Sql呢？既然是或的關(guān)系，我們就應(yīng)該使用 OR了。
SELECT * FROM tb_rider WHERE created_at <= "2017-02-01 00:00:00" OR created_at >= "2017-06-01 00:00:00";
當(dāng)然啦， AND和 OR這樣的關(guān)系運(yùn)算符，不僅僅能夠連接前后兩個(gè)篩選條件，也可以通過使用若干個(gè) AND和 OR連接多個(gè)不同的篩選條件。比如：想要看看2017-02-01(包括2017-02-01當(dāng)天)到2017-06-01(不包括2017-06-01當(dāng)天)期間注冊(cè)的且當(dāng)前是金牌等級(jí)的騎手所有信息，那么我們可以這么寫。
SELECT * FROM tb_rider WHERE created_at >= "2017-02-01 00:00:00" AND created_at < "2017-06-01 00:00:00" AND level = 3;
2.3 排序：ORDER BY
讓我們先小小的復(fù)習(xí)一下上面學(xué)到的知識(shí)點(diǎn)，有一個(gè)這樣的場(chǎng)景：
如何寫這個(gè)Sql呢？先思考3s...1...2...3，看看是否和你想的一樣。
SELECT order_id, created_at FROM tb_order WHERE rider_id = 1 AND grabbed_time >= "2017-12-30 00:00:00" AND grabbed_time < "2017-12-31 00:00:00" AND order_state = 40;
如果你沒有寫對(duì)，沒關(guān)系，讓我們來分析一下：
執(zhí)行這個(gè)語句，我們得到了下面這樣的結(jié)果。
order_idcreated_at3000002017123000012017-12-30 12:34:173000002017123000022017-12-30 12:34:183000002017123000052017-12-30 16:01:03
有點(diǎn)美中不足，我想按照運(yùn)單的創(chuàng)建時(shí)間倒序排序把最近創(chuàng)建的運(yùn)單排在最前面，這時(shí)候就可以使用 ORDER BY語法了。
SELECT order_id, created_at FROM tb_order WHERE rider_id = 1 AND grabbed_time >= "2017-12-30 00:00:00" AND grabbed_time < "2017-12-31 00:00:00" AND order_state = 40 ORDER BY created_at DESC;
讓我們?cè)賮砝斫庖幌拢?DESC是“遞減"的意思，與之對(duì)應(yīng)的是 ASC遞增。 ORDER BY created_at DESC的含義是，按照(BY) created_at字段值遞減(DESC)的順序?qū)Σ樵兘Y(jié)果排序(ORDER)。于是我們得到如下的結(jié)果。
order_idcreated_at3000002017123000052017-12-30 16:01:033000002017123000022017-12-30 12:34:183000002017123000012017-12-30 12:34:17
我們?cè)賮砜匆粋€(gè)更加復(fù)雜的場(chǎng)景：假設(shè)想要查詢2017-12-30和2017-12-31兩天所有運(yùn)單的所有信息，并先按照騎手id遞增，再按運(yùn)單狀態(tài)遞減的順序排序展示。還是先思考一會(huì)兒。
這時(shí)的Sql類似長這樣。
SELECT * FROM tb_order WHERE 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;
如果前面的每個(gè)知識(shí)點(diǎn)都理解了，這里應(yīng)該就只對(duì)“先按照騎手id遞增，再按運(yùn)單狀態(tài)遞減的順序排序展示”有所疑惑。實(shí)際上我們不僅可以對(duì)一個(gè)字段排序，還可以把多個(gè)字段作為排序的依據(jù)，而且不同字段上的排序規(guī)則(遞增/遞減)可以不同。但排序是有優(yōu)先級(jí)的，比如這里，只有當(dāng) rider_id字段的值都相同無法區(qū)分順序時(shí)，才會(huì)對(duì)相同 rider_id的這幾條數(shù)據(jù)再按照 order_state字段的值進(jìn)行排序。舉例來說， rider_id=2且 order_state=80的數(shù)據(jù)，也依然不可能排在 rider_id=1且 order_state=40的數(shù)據(jù)前面。
執(zhí)行這條Sql語句，將得到的結(jié)果如下。
這個(gè)部分相對(duì)有一點(diǎn)難，可以多對(duì)比著例子理解一下。
3 高級(jí)一點(diǎn)的話題
進(jìn)入到這個(gè)部分，說明之前的內(nèi)容你基本都已經(jīng)掌握了，在日常運(yùn)營的操作中有30%左右的場(chǎng)景都可以使用前面講述的這些知識(shí)點(diǎn)解決(當(dāng)然會(huì)有個(gè)熟能生巧的過程)。這個(gè)部分，我將繼續(xù)介紹幾個(gè)更加高級(jí)、當(dāng)然也更加有難度的Sql技能，當(dāng)你結(jié)束這一部分的學(xué)習(xí)并且熟練掌握這些技能的時(shí)候，你會(huì)發(fā)現(xiàn)絕大部分需要通過查數(shù)據(jù)來確認(rèn)的場(chǎng)景你都可以勝任。因?yàn)檫@個(gè)章節(jié)的內(nèi)容本身難度又大了些，如果再對(duì)著一張復(fù)雜的表就更加難以關(guān)注重點(diǎn)，因此我們精簡一下表結(jié)構(gòu)，只保留一些必要的字段。新的 tb_order表如下。
新增的列：
merchant_customer_distance：配送距離(商家到用戶的直線距離)，單位是千米(km)。
3.1 聚合函數(shù)：COUNT，SUM, AVG
千萬別被聚合函數(shù)這個(gè)名字嚇唬到，可以簡單的理解為對(duì)數(shù)據(jù)進(jìn)行一些加工處理，讓我們先來分別看一下這幾個(gè)聚合函數(shù)的基本定義。
讓我們分別來看幾個(gè)具體的例子。
[1] 場(chǎng)景：查詢2017-12-30這一天，騎手Stark的所有完成單(狀態(tài)為40)總量
你可以這樣來寫這個(gè)Sql。
SELECT COUNT(id) FROM tb_order WHERE rider_id = 1 AND order_state = 40 AND created_at >= "2017-12-30 00:00:00" AND created_at < "2017-12-31 00:00:00";
到這里你應(yīng)該已經(jīng)能夠很好的理解 WHERE...AND...AND...這部分的含義，我們就不再過多的討論這個(gè)部分(對(duì)自己要有信心！試著理解先自己理解一下)。
讓我們重點(diǎn)來看一下 COUNT(id)這部分的含義。其實(shí)很簡單，就是對(duì) id這一列進(jìn)行計(jì)數(shù)。連起來看這段Sql，意思就是：從 tb_order這張表中( FROM tb_order)篩選( WHERE)騎手id為1( rider_id=1)且運(yùn)單狀態(tài)為已完成( order_state=40)且創(chuàng)建時(shí)間大于等于2017年12月30日( created_at>="2017-12-30 00:00:00)且創(chuàng)建時(shí)間小于2017年12月31日( created_at<"2017-12-31 00:00:00)的數(shù)據(jù)，并且按照 id這列對(duì)返回的結(jié)果集合進(jìn)行計(jì)數(shù)。
我們看到 tb_order這張表中，2017-12-30當(dāng)天由騎手Stark配送且狀態(tài)是已完成的運(yùn)單分別是300000201712300001、300000201712300002、300000201712300005這幾個(gè)運(yùn)單號(hào)的運(yùn)單，對(duì)應(yīng)的自增id分別是id=[1, 2, 5]，所以對(duì) id這一列進(jìn)行計(jì)數(shù)得到的結(jié)果是3。所以我們得到的查詢結(jié)果如下表。
| COUNT(id) | | --------- | | 3 |
有時(shí)候你僅僅是想查一下滿足某個(gè)條件的記錄的總行數(shù)，而并非想對(duì)某個(gè)特定的列進(jìn)行計(jì)數(shù)，這時(shí)就可以使用 COUNT(*)語法。比如上面的這個(gè)Sql也可以寫成下面這個(gè)樣子。
SELECT COUNT(*) FROM tb_order WHERE rider_id = 1 AND order_state = 40 AND created_at >= "2017-12-30 00:00:00" AND created_at < "2017-12-31 00:00:00";
因?yàn)榉祷氐慕Y(jié)果有三行，所以我們會(huì)得到下表的結(jié)果。
| COUNT(*) | | -------- | | 3 |
看起來 COUNT(列)和 COUNT(*)是完全等價(jià)的？有些特定的場(chǎng)景下的確如此，這里需要補(bǔ)充一下COUNT的兩個(gè)小脾氣。
有一點(diǎn)暈是嗎？不著急，我們來看兩個(gè)例子。假設(shè)有兩張表，很簡單的表，長下面這樣。
示例表1： tb_sample_1
idname1Stark2Stark3Coulson4Natasha5Stark
示例表2： tb_sample_2
idname1Stark2Stark3\4\5Natasha6Coulson
我們下猜一猜下面幾條Sql的執(zhí)行結(jié)果分別是什么？
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;
讓我們逐一分析一下。
1. SELECT COUNT(id) FROM tb_sample_1;
這條Sql沒有太多可以分析的，因?yàn)?tb_sample_1表中 id字段的取值范圍是id=[1, 2, 3, 4, 5]，共5個(gè)，所以我們得到的結(jié)果如下。
| COUNT(id) | | --------- | | 5 |
2. SELECT COUNT(*) FROM tb_sample_1;
這條Sql也沒有太多需要分析的，因?yàn)?COUNT(*)的含義是計(jì)算查詢結(jié)果的總行數(shù)， tb_sample_1共5行數(shù)據(jù)，所以我們得到的結(jié)果如下。
| COUNT(*) | | -------- | | 5 |
3. SELECT COUNT(name) FROM tb_sample_1;
這條Sql里面我們對(duì) name這一列進(jìn)行計(jì)數(shù)， tb_sample_1表中包含3個(gè)Stark，1個(gè)Coulson和1個(gè)Natasha，因?yàn)镃OUNT不進(jìn)行自動(dòng)去重，因此結(jié)果是 5=3(Stark)+1(Coulson)+1(Natasha)，如下表。
| COUNT(name) | | ----------- | | 5 |
4. SELECT COUNT(name) FROM tb_sample_2;
這條Sql語句我們還是對(duì) name這一列進(jìn)行計(jì)數(shù)， tb_sample_2表中包含2個(gè)Stark，1個(gè)Coulson，1個(gè)Natasha以及2個(gè)
| COUNT(name) | | ----------- | | 4 |
[2] 場(chǎng)景：查詢Stark這名騎手的累計(jì)配送里程
讓我們先定義一下累計(jì)配送里程：騎手所有配送完成單的配送距離(商家到用戶的直線距離)之和。
這里的關(guān)鍵詞是求和，所以我們要用到 SUM這個(gè)聚合函數(shù)。對(duì)字段求和的意思是把返回的結(jié)果集合中該字段的值累加起來。讓我們看下這個(gè)場(chǎng)景的Sql怎么寫。
SELECT SUM(merchant_customer_distance) FROM tb_order WHERE rider_id = 1 AND order_state = 40;
讓我們來分析一下這條語句， FROM tb_order WHERE rider_id=1AND order_state=40已經(jīng)比較好理解了，就是從 tb_order表中篩選出騎手id為1且配送狀態(tài)為40的記錄。而這里的 SUM(
merchant_customer_distance)的含義，就是對(duì)前面的條件篩選出的數(shù)據(jù)結(jié)果中的
merchant_customer_distance列的值進(jìn)行求和。根據(jù)騎手id和配送狀態(tài)篩選出的記錄分別為id=(1, 2, 5)，對(duì)應(yīng)的
merchant_customer_distance的值分別為merchantcustomerdistance=(2.5, 1.8, 1.2)，求和結(jié)果為5.5=2.5+1.8+1.2，如下表。
| SUM(merchant_customer_distance) | | --------------------------------- | | 5.5 |
[3] 場(chǎng)景：查詢Stark這名騎手的平均配送里程
同樣的，讓我們先來定義一下平均配送里程：騎手所有完成單的配送距離(商家到用戶的直線距離)之和除以總的完成單量。
基于 SUM的經(jīng)驗(yàn)和前面的“預(yù)告”，不難想到這次我們會(huì)用到 AVG這個(gè)聚合函數(shù)。對(duì)字段求平均值的意思是，把結(jié)果集合中該字段的值累加起來再除以結(jié)果總行數(shù)。 AVG幫我們自動(dòng)完成了“做除法”的動(dòng)作，所以Sql的長相和上一個(gè)場(chǎng)景的 SUM是如出一轍的。
SELECT AVG(merchant_customer_distance) FROM tb_order WHERE rider_id = 1 AND order_state = 40;
根據(jù)騎手id和配送狀態(tài)篩選出的記錄分別為id=(1, 2, 5)，對(duì)應(yīng)的
merchant_customer_distance的值分別為merchantcustomerdistance=(2.5, 1.8, 1.2)，求平均值的結(jié)果為1.83=(2.5+1.8+1.2) / 3，如下表。
| AVG(merchant_customer_distance) | | --------------------------------- | | 1.83 |
寫在3.1節(jié)的最后：
對(duì)著這幾個(gè)場(chǎng)景學(xué)習(xí)下來，不知道你感覺怎么樣吖？是否覺得這幾個(gè)聚合函數(shù)本身還蠻簡單的，或者也有可能會(huì)覺得一下子灌輸了很多知識(shí)點(diǎn)有點(diǎn)費(fèi)勁呢？其實(shí)聚合函數(shù)有它復(fù)雜的一面，我們上面看的這些Case都是比較簡單的使用方式。但是千萬不要擔(dān)心，一方面是因?yàn)檫\(yùn)營工作中遇到的絕大多數(shù)場(chǎng)景都不會(huì)比這些示例Case更復(fù)雜，另一方面是不鼓勵(lì)過于復(fù)雜的使用這些聚合函數(shù)，因?yàn)椴樵兊倪壿嬙绞菑?fù)雜就越是難以“預(yù)測(cè)”查詢的結(jié)果，Sql并不是一個(gè)適合表達(dá)“邏輯”的語言，如果對(duì)數(shù)據(jù)的再加工邏輯很多，就應(yīng)該考慮像分析師提需求或者學(xué)習(xí)更加利于表達(dá)邏輯的其他編程語言。
其次要說的就是多給自己些信心，同時(shí)也要多一點(diǎn)耐心。Sql雖然不同于Python、Java這樣的通用編成語言，除了語法還雜糅著一套體系化的編程概念、設(shè)計(jì)哲學(xué)，但是初次上手的時(shí)候還是會(huì)感覺到有些吃力的。但是只要多去理解幾遍示例、多自己寫一寫，特別是在之后遇到實(shí)際工作中真實(shí)場(chǎng)景的時(shí)候自己思考如何轉(zhuǎn)化為Sql、多實(shí)踐、多回顧分析，很快就會(huì)在潛移默化中掌握它，要相信熟能生巧。
接下來的3.2、3.3節(jié)，我會(huì)繼續(xù)介紹兩個(gè)實(shí)用的Sql語法，以及如何將它們和聚合函數(shù)結(jié)合使用，會(huì)更有難度一些。
3.2 對(duì)查詢結(jié)果去重：DISTINCT 語法
DISTINCT語法顧名思義就是對(duì)某一列的值進(jìn)行去重，讓我們首先來回顧一下3.1節(jié)中COUNT的其中一個(gè)例子。
這個(gè)例子使用的是 tb_sample_1這張表，這張表很簡單，讓我再把它貼出來。
idname1Stark2Stark3Coulson4Natasha5Stark
對(duì)應(yīng)的，我們想要回顧的這條Sql語句也很簡單。
SELECT COUNT(name) FROM tb_sample_1;
前面我們已經(jīng)分析過這條Sql：對(duì) name這列進(jìn)行計(jì)數(shù)，有3個(gè)Stark，1個(gè)Coulson，1個(gè)Natasha，所以得到最終的結(jié)果如下表。
| COUNT(name) | | ----------- | | 5 |
可是有的時(shí)候，我們不想對(duì)相同的名字進(jìn)行重復(fù)計(jì)數(shù)，當(dāng)有多個(gè)相同的名字時(shí)只計(jì)數(shù)一次。這時(shí)候就可以使用到DISTINCT語法。
SELECT COUNT(DISTINCT name) FROM tb_sample_1;
對(duì)比上一條Sql只是增加了一個(gè)DISTINCT關(guān)鍵字，其實(shí)理解起來呢也不用把它想的太復(fù)雜啦： COUNT(DISTINCT name)就是對(duì)去重后的 name進(jìn)行計(jì)數(shù)。 tb_sample_1中有3個(gè)Stark,但是3個(gè)Stark是重復(fù)的，使用DISTINCT語法后只會(huì)被計(jì)算一次，另外還有1個(gè)Coulson和一個(gè)Natasha，所以得到的結(jié)果如下表。
| COUNT(DISTINCT name) | | -------------------- | | 3 |
DISTINCT語法可以單獨(dú)使用，這時(shí)就是它本身的意思，對(duì)某列的值進(jìn)行去重。但是相比之下，更常見的是像上面的例子一樣和COUNT這個(gè)聚合函數(shù)一起使用，這樣就可以對(duì)去重后的結(jié)果進(jìn)行計(jì)數(shù)。
3.3 將查詢數(shù)據(jù)分組：GROUP BY 語法
前面我們基于 tb_order這張表講解了很多Sql的語法知識(shí)，讓我們?cè)賮砘貞浺幌逻@張表的容顏。
溫故而知新！先來出幾道題目復(fù)習(xí)一下前面所學(xué)的Sql知識(shí)。
復(fù)習(xí)題1： 試著寫出以下幾個(gè)場(chǎng)景對(duì)應(yīng)的Sql語句
復(fù)習(xí)題2： 試著理解以下幾條Sql的含義并且寫出查詢的結(jié)果
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";
聰明的你是否發(fā)現(xiàn)復(fù)習(xí)題2就是復(fù)習(xí)題1的答案呢？如果還沒有發(fā)現(xiàn)，沒關(guān)系，再回過頭來多分析幾遍，Practice Makes Perfect 絕對(duì)是真理。不過復(fù)習(xí)這幾個(gè)例子可不僅僅是為了復(fù)習(xí)哦，讓我們?cè)?、2兩個(gè)場(chǎng)景的基礎(chǔ)下擴(kuò)展一下，講解新的知識(shí)點(diǎn)。思考下面這兩個(gè)場(chǎng)景。
首先分析一下這里的場(chǎng)景1。“2017-12-30當(dāng)天”這個(gè)條件不難轉(zhuǎn)化為 created_at>='2017-12-30 00:00:00'AND created_at<'2017-12-31 00:00:00'，“完成單”不難轉(zhuǎn)化為 order_state=40，由于要計(jì)算運(yùn)單的“總量”我們也不難想到可以對(duì) order_id進(jìn)行COUNT操作。那么如何分組到每個(gè)騎手身上呢？這時(shí)候就要用到GROUP BY了。
SELECT COUNT(order_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" GROUP BY rider_id;
注意這里執(zhí)行順序是先按照WHERE條件進(jìn)行篩選，然后根據(jù)騎手id進(jìn)行分組(GROUP BY)，最后再對(duì)每個(gè)分組按照運(yùn)單號(hào)進(jìn)行計(jì)數(shù)。因此我們可以得到下表的結(jié)果。
| COUNT(order_id) | | ------------------- | | 3 | | 1 | | 1 | | 1 |
好像有哪里不對(duì)？結(jié)果中看不到對(duì)應(yīng)的騎手吖！不著急，我們稍微修改下剛才的Sql，將騎手id、騎手姓名這2列展示出來就可以了。
SELECT rider_id, rider_name, COUNT(order_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" GROUP BY rider_id;
我們得到如下表的結(jié)果。
rider_idrider_nameCOUNT(order_id)1Stark32Banner15Natasha13Rogers1
這樣是不是就清晰多了。
再來分析場(chǎng)景2。有了前面的例子，“2017-12-30當(dāng)天”、“完成單”這兩個(gè)條件應(yīng)該是已經(jīng)得心應(yīng)手、信手拈來了，“平均配送距離”問題也不大，可以轉(zhuǎn)化為 AVG(
merchant_customer_distance)。那么如何分組到每個(gè)騎手身上呢？還是通過 GROUP BY語法。我們的Sql長成下面這個(gè)樣子。
SELECT rider_id, rider_name, 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" GROUP BY rider_id;
得到如下表的結(jié)果。
rider_idrider_nameAVG(merchant_customer_distance)1Stark1.832Banner1.85Natasha2.73Rogers0.5
還是需要特別提一下這里的執(zhí)行順序，首先執(zhí)行的是 WHERE條件篩選，然后對(duì)篩選出的數(shù)據(jù)結(jié)果根據(jù)騎手id進(jìn)行分組，最后再對(duì)每個(gè)分組中的數(shù)據(jù)進(jìn)行
merchant_customer_distance列的求平均值。
3.4 聚合函數(shù)的好搭檔：HAVING 語法
HAVING語法的含義類似于WHERE，當(dāng)我們使用HAVING的時(shí)候一般遵循 HAVING篩選條件的語法結(jié)構(gòu)。你可能會(huì)問啦，既然和WHERE語法含義差不多、使用方式又很類似，那干嘛還要憑空多個(gè)HAVING語法出來呢？原因就在于聚合函數(shù)。WHERE語法是不能和聚合函數(shù)一起使用的，但有些時(shí)候我們卻需要依賴聚合函數(shù)的計(jì)算結(jié)果作為篩選條件。讓我們看一下3.3節(jié)中場(chǎng)景2這個(gè)例子。
場(chǎng)景2：查詢2017-12-30當(dāng)天每個(gè)參與跑單騎手的完成單平均配送距離。
通過前面我們的分析，得到這樣的Sql。
SELECT rider_id, rider_name, 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" GROUP BY rider_id;
我們?cè)趫?chǎng)景2的基礎(chǔ)上再擴(kuò)展一下。
擴(kuò)展的場(chǎng)景2：查詢2017-12-30當(dāng)天每個(gè)參與跑單騎手的完成單平均配送距離，并篩選出其中平均配送距離超過1.5km的數(shù)據(jù)。
我們得到這樣的Sql結(jié)果。
SELECT rider_id, rider_name, 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" GROUP BY rider_id HAVING AVG(merchant_customer_distance) > 1.5;
比較一下不難發(fā)現(xiàn)，變化僅僅是末尾多了 HAVING AVG(
merchant_customer_distance)>1.5這條子句。讓我們分析看看。 SELECT...FROM...WHERE...和之前的用法并沒有變化， GROUP BY rider_id將SELECT的結(jié)果根據(jù) rider_id進(jìn)行分組，分組完成后 HAVING AVG(
merchant_customer_distance)>1.5語句對(duì)每一組的
merchant_customer_distance字段值求取平均數(shù)，并且將平均數(shù)大于1.5的結(jié)果篩選出來，作為返回結(jié)果。
執(zhí)行這條Sql我們得到結(jié)果。
rider_idrider_nameAVG(merchant_customer_distance)1Stark1.832Banner1.85Natasha2.7
Rogers這位騎手(騎手id=3)因?yàn)槠骄渌途嚯x為0.5，不滿足HAVING語句指定的“平均配送距離大于1.5km”的篩選條件，所以沒有在我們的查詢結(jié)果中。
4 有點(diǎn)超綱的話題
4.1 字段類型
類型這個(gè)詞此刻你聽起來可能還是很陌生的，但其實(shí)在計(jì)算機(jī)科學(xué)領(lǐng)域，類型是一個(gè)非?；A(chǔ)而且廣泛存在的概念，幾乎每一種編程語言都有自己的類型系統(tǒng)。
[1] 為什么要定義類型的概念？
關(guān)于為什么要有類型這個(gè)概念，我吶有一個(gè)“不成熟”的理解：編程語言作為人和機(jī)器交互的一種工具，人類對(duì)數(shù)據(jù)有人類邏輯上的理解，當(dāng)我們看到2903的時(shí)候我們會(huì)認(rèn)為這是個(gè)整數(shù)，當(dāng)我們看到1031.2903的時(shí)候我們會(huì)認(rèn)為這是個(gè)小數(shù)。而機(jī)器在處理數(shù)據(jù)或者存取數(shù)據(jù)的時(shí)候，是無差別的按照比特位進(jìn)行二進(jìn)制運(yùn)算或者讀寫的。人類很難做到直接用二進(jìn)制輸入計(jì)算機(jī)，當(dāng)然也不能接受計(jì)算機(jī)直接以二進(jìn)制的形式輸出結(jié)果。設(shè)想一下，如果某天咱們想用一下電腦上的計(jì)算器，計(jì)算個(gè) 1+1=2，但是我們沒有類型，我們需要理解機(jī)器是如何處理二進(jìn)制的，那么就可能需要輸入
00000000000000000000000000000001+
00000000000000000000000000000001，而得到的結(jié)果也是二進(jìn)制
00000000000000000000000000000010，這得多累人吶。有了類型就輕松多了，通過定義數(shù)據(jù)的類型，根據(jù)類型的約定，計(jì)算機(jī)就知道如何將這個(gè)1轉(zhuǎn)化為二進(jìn)制(包括：應(yīng)該轉(zhuǎn)化為16位、32位還是64位的二進(jìn)制，對(duì)這段二進(jìn)制數(shù)據(jù)進(jìn)行操作的時(shí)候，應(yīng)該把它看作整數(shù)還是浮點(diǎn)數(shù)等等)，而返回結(jié)果的時(shí)候也就知道如何將二進(jìn)制的
00000000000000000000000000000010轉(zhuǎn)化為我們能夠理解的整數(shù)2
編程語言的類型其實(shí)就是人與機(jī)器約定好的，去理解和操作數(shù)據(jù)的一套規(guī)則。
總而言之，在機(jī)器的眼里，無論是對(duì)數(shù)據(jù)進(jìn)行何種操作，它看到的都是一串一串由0和1構(gòu)成的東西，稱呼這種東西有專門的術(shù)語，叫作“字節(jié)流”或者“二進(jìn)制流“。
讓我們?cè)僖黄鹂匆粋€(gè)例子。假設(shè)要處理這樣的一段二進(jìn)制流：
00000000100111011000001111010111，這段二進(jìn)制流可以表示很多東西，要明確它的含義，就需要明確它的類型，比如下面這兩種不同的類型，這段流表示的內(nèi)容就完全不同。
我知道你此刻對(duì)為何轉(zhuǎn)換為32位整型是10322903？為何看作2個(gè)16位整型轉(zhuǎn)換后是157和33751？還有著很多疑惑。但是關(guān)于二進(jìn)制和十進(jìn)制的轉(zhuǎn)換方法呢，在這里就不做展開了，如果你很感興趣、很想知道可以再單獨(dú)給你講這個(gè)方法。講上面的這些，最主要的還是希望你明白，定義“類型”的概念，根本上是在人機(jī)交互的過程中提供了一種機(jī)制，賦予無差別的二進(jìn)制流一定的語義。
還是太抽象了對(duì)不對(duì)？沒關(guān)系，我們?cè)賮砼e個(gè)栗子。
前面我們?cè)?strong>預(yù)備知識(shí)這一章中使用到了 tb_stu_math_score這張表，為了不讓你辛苦的再翻回去，我們?cè)儋N一下這張表的內(nèi)容啦。
id(自增主鍵)name(學(xué)生姓名)number(學(xué)號(hào))grade(年級(jí))class(班級(jí))score(得分)1柯南010201121002小哀010202121003光彥01020312984步美01020412955元太0102051259
也寫過類似下面這條Sql語句。
SELECT score FROM tb_stu_math_score WHERE id=1;
這條Sql語句非常非常的簡單，現(xiàn)在我們已經(jīng)知道它會(huì)返回第一行數(shù)據(jù) score這一列的值，結(jié)果長下面這樣。
| score | | ----- | | 100 |
讓我們分析一下獲取這個(gè)結(jié)果的整個(gè)流程，幫助你理解一下，類型是如何發(fā)揮作用的。
實(shí)際上反過來也非常類似，當(dāng)我們向這張表中寫入數(shù)據(jù)時(shí)，例如寫入的 score列的值為100。因?yàn)榇鎯?chǔ)基于二進(jìn)制，根據(jù)表的定義， score列的類型為整型，于是將值100按照整型轉(zhuǎn)換為對(duì)應(yīng)的二進(jìn)制流
00000000000000000000000001100100，并且寫入到庫中。
[2] Sql的主要數(shù)據(jù)類型有哪些？
Sql中常常接觸的數(shù)據(jù)類型主要包括幾類。
1 整型
2 浮點(diǎn)型
3 字符串類型
這里固定長度和可變長度指的是數(shù)據(jù)庫中的存儲(chǔ)形式，因?yàn)檫@部分的內(nèi)容其實(shí)有些超出了這個(gè)教程的范圍，我們不過多的解釋這里的區(qū)別。一般在我們實(shí)際的應(yīng)用中 varchar用的更多一些。它們都表示類似于 "very glad to meet u, Huohuo!"這樣的一串字符，當(dāng)然也可以是中文 "敲開心認(rèn)識(shí)你，火火！"。
4 日期類型
我們?cè)谶@里只是簡單的介紹了幾種Sql中常見的字段類型，并沒有很深入的去解釋它們的原理、差異以及一些其他的數(shù)據(jù)類型，咱們不著急去學(xué)習(xí)那些“高大上”的內(nèi)容，先理解這些類型的含義。
[3] 怎么知道一張表中每一列的類型是什么？
第1種方式是使用 DESC表名命令，例如我們想看一下之前提到的 tb_rider表的每一列字段類型，就可以執(zhí)行命令 DESC tb_rider，得到下面的結(jié)果。
注意這里的第一列表示字段名稱，第二列 Type則表示對(duì)應(yīng)字段的字段類型。比如 id字段，是一個(gè) int類型。
第二種方式是使用 SHOW CREATE TABLE表名命令，例如 SHOW CREATE TABLE tb_rider，得到下面的結(jié)果。
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 '身份證認(rèn)證狀態(tài)', `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '該用戶是否還存在. 0: 不存在, 1: 存在', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間', `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間', `level` tinyint(4) NOT NULL DEFAULT '0' COMMENT '騎手等級(jí)：0普通 1銅牌 2銀牌 3金牌', `level_city` varchar(32) NOT NULL DEFAULT '' COMMENT '配送員等級(jí)城市', 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 '姓名'
來解釋一下這里的語句。
4.2 索引
索引絕對(duì)算得上是關(guān)系型數(shù)據(jù)庫中最關(guān)鍵同時(shí)也是最有難度的話題。即便是經(jīng)驗(yàn)豐富的研發(fā)同學(xué)，也經(jīng)常會(huì)踩到索引的坑。不過我們這里介紹索引，只是為了更好的服務(wù)于查詢，我會(huì)盡可能避免牽扯進(jìn)一些復(fù)雜的概念和底層原理。
[1] 什么是索引？
那么到底什么是索引呢？你可以把數(shù)據(jù)庫理解為一本很厚的書(假設(shè)有10萬頁)，書中的內(nèi)容就是數(shù)據(jù)庫里的數(shù)據(jù)，那么索引就是書的目錄。 假設(shè)你從來沒有閱讀過這本書，此刻你想要閱讀書的第7章第2小節(jié)。如果沒有目錄，你可能需要翻閱整本書找到你要閱讀的內(nèi)容。但是在有目錄的情況下，你就只需要先查一下目錄找到對(duì)應(yīng)的頁碼，然后直接翻到那一頁就能看到你想看的內(nèi)容了。索引也是類似的，首先查詢索引找到目標(biāo)數(shù)據(jù)的位置，再從特定的位置讀取出數(shù)據(jù)的內(nèi)容。
如何設(shè)計(jì)索引，是設(shè)計(jì)數(shù)據(jù)庫表的時(shí)候考慮的關(guān)鍵點(diǎn)之一。索引一般由表中的某一列或者某幾列構(gòu)成，一旦設(shè)置某一列為索引，那么之后每次在往表中寫入數(shù)據(jù)的時(shí)候，都會(huì)更新這一列到索引中去。事實(shí)上，索引在技術(shù)層面是比較復(fù)雜的，涉及到磁盤I/O、B樹、優(yōu)化器(Optimizer)等很多技術(shù)概念，不過我們先不去深究這些。
[2] 為什么索引很重要，它有什么用？
索引之所以重要，最主要的原因是能夠大大提高查詢的速度。上面我們舉了書的例子，當(dāng)這本書的頁數(shù)足夠大的時(shí)候(假設(shè)有2000萬頁)，如果沒有目錄，想要查閱其中的某一章節(jié)的內(nèi)容，那幾乎就是天方夜譚了。數(shù)據(jù)庫也是如此，當(dāng)表中的數(shù)據(jù)只有幾行或者幾十行、幾百行的時(shí)候，有沒有索引其實(shí)差別不大，但是當(dāng)表中的數(shù)據(jù)非常非常多的時(shí)候(比如眾包的運(yùn)單表，2000萬+ 行)，如果沒有索引，要找到某一條目標(biāo)數(shù)據(jù)，查詢的速度就會(huì)非常非常非常的慢。
[3] 如何使用索引？
要使用索引非常簡單，只需要在 WHERE條件中使用到索引列作為查詢條件，讓我們舉個(gè)例子。
還是這張 tb_order表，假設(shè)這張數(shù)據(jù)表中 order_id是索引列，那么當(dāng)我們以 order_id作為查詢條件時(shí)，我們就利用了索引，比如下面這條Sql。
SELECT * FROM tb_order WHERE order_id = 300000201712310007;
當(dāng)然啦，類似的使用 order_id作為查詢條件的Sql也都會(huì)利用到索引，看看你是否都理解下面兩條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語句的查詢條件，會(huì)使用哪個(gè)列作為索引還是都使用？假設(shè) tb_order表中 order_id和 rider_id兩列都是索引列，那么下面這條Sql語句會(huì)使用哪個(gè)作為索引呢？
SELECT * FROM tb_order WHERE order_id >= 300000201712310001 AND order_id <= 300000201712310007 AND rider_id > 0;
答案是不確定的。使用哪個(gè)索引，甚至是否使用索引，從根本上來說是由優(yōu)化器(Optimizer)決定的，它會(huì)分析多個(gè)索引的優(yōu)劣，以及使用索引和不使用索引的優(yōu)劣，然后選擇最優(yōu)的方式執(zhí)行查詢。這部分話題就太過復(fù)雜了，這里不做展開。盡管有優(yōu)化器(Optimizer)的存在，但是對(duì)于我們的查詢來說，能夠使用明確的索引字段作為查詢條件的，就應(yīng)該盡可能使用索引字段。
[4] 索引的類型、如何確定表中的哪些列是索引列？
還記得字段類型一節(jié)中提到的 DESC表名和 SHOW CREATE TABLE表名語法嗎？前面我們將這兩個(gè)語法用在了 tb_rider表上，這一節(jié)讓我們看一看 tb_order表。
首先是 DESC tb_order，我們會(huì)得到下面的結(jié)果。
之前我們關(guān)注的是 Type這一項(xiàng)，這里讓我們關(guān)注 Key這一項(xiàng)。我們看到有些列對(duì)應(yīng)的 Key是空的，這就表示這一列(或者叫這個(gè)字段)不是索引列(或者叫索引字段)。但 id、 order_id、 created_at和 updated_at這幾列對(duì)應(yīng)的 Key均是有值的，這說明這幾列都是索引列。但這幾列 Key的值又各不相同，這是為啥吶？這是以內(nèi)索引也分為不同的類型，讓我們逐個(gè)來解釋一下。
現(xiàn)在我們還處在Sql以及數(shù)據(jù)庫知識(shí)(是的，除了Sql，我還偷偷介紹了一些數(shù)據(jù)庫原理)學(xué)習(xí)的初級(jí)階段，所以讓我們知道這寫差異，但是不著急去把這些搞得一清二楚，它們都是索引，只要合理使用，都可以幫助我們加快Sql查詢的效率。
另一種識(shí)別表中索引列的方法就是通過 SHOW CREATE TABLE表名命令，比如 SHOW CREATE TABLE tb_order，我們得到下面的結(jié)果。
CREATE TABLE `tb_order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '對(duì)外不提供，內(nèi)部使用', `order_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '運(yùn)單的跟蹤號(hào)（可以對(duì)外提供）', `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 '配送狀態(tài)', `is_deleted` tinyint(4) NOT NULL DEFAULT '0', `grabbed_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '搶單時(shí)間', `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了嗎，它們就對(duì)應(yīng)于 DESC tb_order結(jié)果中的 PRI、 UNI和 MUL，分別標(biāo)識(shí)主鍵索引、唯一索引和普通索引。每一行括號(hào)內(nèi)的字段就表示對(duì)應(yīng)的索引列。
4.3 JOIN語法家族
我嘗試了好幾種解釋清楚JOIN語法的方法(JOIN語法的確有些復(fù)雜)，始終不能讓我自己滿意，最終決定還是從一個(gè)例子開始。讓我們首先看一張新的表，建表語句長下面這樣。
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 '創(chuàng)建時(shí)間', `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間', 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="自定義騎手接單上限表";
小溫習(xí)
參考上面的建表語句嘗試回答下面這幾個(gè)問題。
沒錯(cuò)！這就是自定義騎手接單上限表。描述了某一個(gè)騎手( rider_id)對(duì)應(yīng)的他的接單上限( order_grab_limit)。表中的數(shù)據(jù)如下。
idrider_idorder_grab_limitis_deletedcreated_atupdated_at111102018-02-25 17:22:032018-02-25 17:22:0322902018-02-25 17:22:212018-02-25 17:22:2134902018-02-25 17:22:312018-02-25 17:22:3146702018-02-25 17:22:392018-02-25 17:22:39510802018-02-25 17:22:462018-02-25 17:22:46
再讓我們回顧一下前面反復(fù)用到的 tb_rider表。
(終于鋪墊完啦！)
[1] 從LEFT JOIN開始
以這兩張表為基礎(chǔ)，設(shè)想一個(gè)場(chǎng)景：假設(shè)要查詢 tb_rider表中所有騎手對(duì)應(yīng)的自定義接單上限。我們的Sql應(yīng)該怎么寫呢？
思路1：先查出 tb_rider表中所有騎手id，再根據(jù)這些騎手id作為查詢條件，通過前面學(xué)習(xí)過的IN語法從 tb_grab_order_limit表中查詢出所對(duì)應(yīng)的自定義接單上限的記錄。
SELECT id FROM tb_rider;
和
SELECT rider_id, order_grab_limit FROM tb_grab_order_limit WHERE rider_id IN (1, 2, 3, 4, 5, 6, 7, 8);
思路1顯然是個(gè)Bad idea。但是思路1詮釋了解決這個(gè)查詢問題的基本要點(diǎn)。
思路2：基于這幾個(gè)要點(diǎn)我們可以使用LEFT JOIN語法，下面是對(duì)應(yīng)的Sql語句。
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;
這里先介紹一下JOIN語法的基本結(jié)構(gòu)： 表1(INNER/LEFT/RIGHT/FULL)JOIN表2ON表1.列1=表2.列2。JOIN關(guān)鍵字前后連接的是兩張需要關(guān)聯(lián)查詢的數(shù)據(jù)表，ON關(guān)鍵字后面跟著關(guān)聯(lián)的條件。一共有四種類型的JOIN，他們分別是INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。以例子中的LEFT JOIN為例， 表1LEFT JOIN表2ON表1.列1=表2.列2的含義是，遍歷表1中的列1的值，如果表2中列2的值有和它相等的則展示對(duì)應(yīng)的記錄，如果沒有表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字段業(yè)務(wù)含義就是騎手id)的值，尋找 tb_grab_order_limit表中 rider_id列的值和它相等的記錄，如果不存在則是null。
我們還看到SELECT語句的內(nèi)容和我們之前使用的很類似，但又稍微有點(diǎn)不一樣，都是表名.列名的書寫形式。其實(shí)這主要是指明了字段所屬的表，因?yàn)镴OIN的兩張數(shù)據(jù)表中可能存在的相同名稱的列，例如 tb_rider表和 tb_grab_order_limit表都有 id字段，但含義截然不同，這樣寫更加明確。
最終思路2的結(jié)果如下。
idorder_grab_limit1112949677\8\5\3\
我們看到騎手id=(7, 8, 5, 3)的幾個(gè)騎手沒有配置自定義的接單上限，但因?yàn)槭荓EFT JOIN，他們?nèi)匀粫?huì)展示在查詢結(jié)果中，不過因?yàn)闆]有接單上限的記錄， order_grab_limit的結(jié)果為null。
讓我們?cè)倩仡^看一下表名.列名這個(gè)寫法。如果思路2中的Sql改成下面這樣，返回結(jié)果會(huì)變成什么呢？
SELECT tb_grab_order_limit.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;
讓我們來分析一下。我們知道LEFT JOIN的返回結(jié)果集合是以它左側(cè)連接的數(shù)據(jù)表決定的，所以結(jié)果集仍然包含8條記錄，但是騎手id=(7, 8, 5, 3)這個(gè)騎手沒有對(duì)應(yīng)的接單上限的配置，因此當(dāng)我們展示這幾個(gè)騎手的
tb_grab_order_limit.rider_id列的值的時(shí)候，類似于
tb_grab_order_limit.order_grab_limit，也是null。因此結(jié)果是下面這樣。
rider_idorder_grab_limit111294967\\\\\\\\
如果你還是不太明白，然我們?cè)赟ELECT的時(shí)候，加上 tb_rider.id，或許有助于理解。
SELECT tb_rider.id, tb_grab_order_limit.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;
結(jié)果是。
idrider_idorder_grab_limit11112294496677\\8\\5\\3\\
[2] LEFT JOIN的姊妹篇：RIGHT JOIN
前面我們知道LEFT JOIN是以連接的左側(cè)表作為查詢的結(jié)果集的依據(jù)，RIGHT JOIN則是以連接的右側(cè)表作為依據(jù)。讓我們考慮另一個(gè)場(chǎng)景：假設(shè)想要查詢所有設(shè)置了自定義接單上限的騎手姓名。應(yīng)該如何寫這個(gè)Sql呢？
先在聰明的大腦里思考幾分鐘。此時(shí)你需要類比LEFT JOIN，需要理解上一段內(nèi)容講述的LEFT JOIN知識(shí)點(diǎn)，可能需要回到上一段再看一看示例Sql語句以及對(duì)應(yīng)的結(jié)果。沒關(guān)系，一開始學(xué)習(xí)的時(shí)候慢慢來。
答案是這樣的。
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;
對(duì)應(yīng)的查詢結(jié)果則是。
rider_idname1Stark2Banner4Thor6Barton10\
如果這個(gè)結(jié)果和你腦海中思考的結(jié)果不一樣，不要著急，讓我們?cè)賮斫忉屢幌?。RIGHT JOIN是以連接的右側(cè)表為依據(jù)，而 tb_grab_order_limit中的騎手id=(1, 2, 4, 6, 10)，其中騎手id為10的騎手在 tb_rider表中是沒有的，所以 name為null。
小測(cè)驗(yàn)
嘗試下將上面的這條Sql語句改寫成LEFT JOIN吧(要求得到相同的查詢結(jié)果)？
[3] 一絲不茍的INNER JOIN
之所以叫“一絲不茍”的INNER JOIN，是因?yàn)镮NNER JOIN是非常嚴(yán)格的關(guān)聯(lián)查詢，換句話說，必須是根據(jù)JOIN條件兩張表中存在匹配記錄的才作為結(jié)果集返回。讓我們回顧下[1]中LEFT JOIN的Sql。
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;
它的返回結(jié)果是。
idorder_grab_limit1112949677\8\5\3\
如果我們將LEFT JOIN改為INNER JOIN吶？修改后的Sql像這樣。
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;
這時(shí)返回的查詢結(jié)果變成了。
idorder_grab_limit111294967
這是因?yàn)镮NNER JOIN會(huì)遍歷連接一側(cè)的表，根據(jù)ON后的連接條件，和連接另一側(cè)的表進(jìn)行比較，只有兩張表中存在匹配的記錄才會(huì)作為結(jié)果集返回。例如這里，它會(huì)遍歷 tb_rider表中 id字段的值，并且去 tb_grab_order_limit表中尋找 rider_id與之匹配的記錄，如果找到則作為結(jié)果返回。
小測(cè)驗(yàn)
猜測(cè)一下下面的這條Sql語句的返回結(jié)果是什么？
SELECT tb_rider.id, tb_grab_order_limit.order_grab_limit FROM tb_grab_order_limit INNER JOIN tb_rider ON tb_grab_order_limit.rider_id = tb_rider.id;
提示：這里交換了一下INNER JOIN連接的兩張表的位置，根據(jù)INNER JOIN的特性，查詢結(jié)果會(huì)有影響嘛？
[4] 心大的FULL JOIN
FULL JOIN其實(shí)并不在乎匹配與否，而是將連接的兩張表中所有的行都返回，如果有匹配的則返回匹配的結(jié)果，如果沒有匹配則哪張表中缺失則對(duì)應(yīng)的將當(dāng)前這條記錄標(biāo)記為null?？匆粋€(gè)例子就明白啦！
SELECT tb_rider.id, tb_rider.name, tb_grab_order_limit.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語句的查詢結(jié)果是這樣的。
idnamerider_idorder_grab_limit1Stark1112Banner294Thor496Barton673Rogers\\5Natasha\\7Coulson\\8Coulson\\\\1010
可以看到 tb_rider表中騎手id=(3, 5, 7, 8)的騎手在 tb_grab_order_limit表中沒有匹配的記錄，而 tb_grab_order_limit表中騎手id=(10)的騎手在 tb_rider表中沒有匹配記錄，但是它們都作為結(jié)果集返回了。只不過缺失 tb_grab_order_limit記錄的， rider_id和 order_grab_limit字段值為null，而缺失 tb_rider記錄的， id和 name字段的值為null。
事實(shí)上，絕大多數(shù)情況下，F(xiàn)ULL JOIN都不會(huì)被用到。而且在一些數(shù)據(jù)庫管理系統(tǒng)中，例如MySql(我們的線上環(huán)境主要使用的就是MySql)，是不支持FULL JOIN語法的。對(duì)于上面的查詢語句，需要使用一些技巧通過LEFT JOIN、RIGHT JOIN以及UNION(這篇教程中我們不討論UNION語法哦)語法的組合來實(shí)現(xiàn)同樣效果的查詢。
SELECT tb_rider.id, tb_rider.name, tb_grab_order_limit.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 UNION SELECT tb_rider.id, tb_rider.name, tb_grab_order_limit.rider_id, tb_grab_order_limit.rider_id FROM tb_rider RIGHT JOIN tb_grab_order_limit ON tb_rider.id = tb_grab_order_limit.rider_id WHERE tb_rider.id IS null;
這已經(jīng)超出了這篇教程的討論范圍啦！如果想要挑戰(zhàn)一下自己，以下是一些提示。
試著在這兩條提示下理解一下這條Sql語句，如果能夠弄明白這條語句是如何等價(jià)于FULL JOIN的，那么說明你對(duì)JOIN家族的語法已經(jīng)基本掌握啦。如果暫時(shí)還不能弄得非常明白也沒關(guān)系，多看一看例子，多寫一寫實(shí)踐一下，慢慢就會(huì)明白啦。
題外話
從上面的講解我們了解到JOIN的四種用法，總結(jié)一下。
不過這些都是刻板的文字總結(jié)，讓我們換個(gè)視角總結(jié)一下這集中JOIN語法。
離散數(shù)學(xué)中在討論集合論的時(shí)候介紹過“韋恩圖”的概念，它清楚的描述了數(shù)據(jù)集合之間的關(guān)系。而JOIN的這4種操作也正好對(duì)應(yīng)了4種集合運(yùn)算，下面的這張圖(Figure 1)很清楚的描述了這種關(guān)系。
4.4 嵌套的SELECT語法
再來看一下講述LEFT JOIN的開始，我們提到的那個(gè)例子：查詢 tb_rider表中所有騎手對(duì)應(yīng)的自定義接單上限。當(dāng)時(shí)我們首先提出了思路1，是分為2個(gè)步驟的。
SELECT id FROM tb_rider;
和
SELECT rider_id, order_grab_limit FROM tb_grab_order_limit WHERE rider_id IN (1, 2, 3, 4, 5, 6, 7, 8);
我們說這個(gè)思路不好，這是顯然的，因?yàn)樵诂F(xiàn)實(shí)場(chǎng)景中往往數(shù)據(jù)集合都很大(例如這里的 rider_id在現(xiàn)實(shí)中可能是成百上千甚至成千上萬個(gè))，思路本身沒有問題但無法操作執(zhí)行。所以在4.3節(jié)我們選擇通過JOIN語法來實(shí)現(xiàn)同樣的查詢。那是不是思路1就真的只能是個(gè)紙上談兵的思路了呢？當(dāng)然不是啦！我們還可以使用嵌套的SELECT語句，就像這樣。
SELECT rider_id, order_grab_limit FROM tb_grab_order_limit WHERE rider_id IN (SELECT id FROM tb_rider);
這個(gè)寫法非常好理解， WHERE rider_id IN(SELECT id FROM tb_rider)首先執(zhí)行括號(hào)中的語句 SELECT id FROM tb_rider，然后執(zhí)行IN篩選，就是我們的思路1描述的那樣。于是得到下面的結(jié)果。
rider_idorder_grab_limit111294967
復(fù)習(xí)題
回想一下上面的結(jié)果和以下哪條Sql語句的執(zhí)行結(jié)果是一致的呢？為什么是一致的，為什么和其他的不一致？
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;
小測(cè)驗(yàn)
思考一下以下這個(gè)場(chǎng)景，看看能否寫出它對(duì)應(yīng)的Sql語句？
場(chǎng)景：篩選出所有通過實(shí)名認(rèn)證( real_name_certify_state=2)的金牌( level=3)騎手( tb_rider表)，在2017-12-30當(dāng)天( created_at>=xxx AND created_at 想一想有幾種寫法呢？ 5 闖關(guān)答題：快速復(fù)習(xí) 前面的幾個(gè)段落我們學(xué)習(xí)了Sql查詢中最常用，而且特別好用的語法知識(shí)，讓我們簡單總結(jié)一下。 學(xué)習(xí)了這么多知識(shí)點(diǎn)，實(shí)在是太膩害了！給自己點(diǎn)贊！ 但是(凡事都有個(gè)但是)... 想要把這些知識(shí)點(diǎn)融會(huì)貫通，靈活應(yīng)用到現(xiàn)實(shí)工作中更多變、更復(fù)雜的查詢場(chǎng)景，僅僅是“學(xué)會(huì)”是不夠的，還需要更多的“練習(xí)”和“回味”。 這個(gè)部分我設(shè)計(jì)了一個(gè)“闖關(guān)答題”項(xiàng)目，通過思考和回答這些闖關(guān)題，幫助你更好的掌握上面提到的知識(shí)點(diǎn)。 先來看一下答題將要用到的數(shù)據(jù)表。 [1] 商品數(shù)據(jù)表： tb_product idproduct_idnameprice11001iPad Pro 10.5 64G WLAN488821002Macbook Pro 2017 13.3 i5/8G/256GB1388831003iPhone X 64G8388 建表語句： 字段含義： [2] 用戶數(shù)據(jù)表： tb_customer 建表語句： 字段含義： [3] 訂單數(shù)據(jù)表： tb_order idorder_idcustomer_idproduct_idquantity1NUM1000301NO100001100112NUM1000302NO100001100223NUM1000303NO100002100224NUM1000304NO100003100215NUM1000305NO10000110031 建表語句： 字段含義： 了解完需要用到表結(jié)構(gòu)，我們就要開始答題啦！ 第一關(guān)：查詢賬戶余額大于1萬元的用戶id和姓名？ Answer: customer_idnameNO100001火火 第二關(guān)：查詢賬戶余額小于1萬元且性別為女生的用戶姓名？ Answer: | name | |--------| | 撥潑抹 | | 水娃 | 第三關(guān)：查詢用戶id為NO100001和NO100002的用戶，所有購買記錄的訂單號(hào)？ Hint：IN Answer: | order_id | |------------| | NUM1000301 | | NUM1000302 | | NUM1000303 | | NUM1000305 | 第四關(guān)：查詢用戶id為NO100001、NO100002兩位用戶所有的購買記錄(所有字段)，要求按照優(yōu)先以商品id遞增、其次以訂單號(hào)遞減的規(guī)則展示數(shù)據(jù)？ Hint：IN、ORDER BY Answer: idorder_idcustomer_idproduct_idquantity1NUM1000301NO100001100113NUM1000303NO100002100222NUM1000302NO100001100225NUM1000305NO10000110031 第五關(guān)：查詢性別為女生的用戶總數(shù)？ Hint：COUNT Answer: | COUNT(customer_id) | |---------------------| | 3 | 第六關(guān)：查詢NO100001、NO100002、NO100003三位用戶各自購買商品的總數(shù)(不區(qū)分商品類型)，輸出購買商品件數(shù)大于等于2件的用戶id以及他們對(duì)應(yīng)購買的商品總數(shù)？ Warning：“購買商品的總數(shù)”和上一關(guān)“女生用戶的總數(shù)”，這兩個(gè)“總數(shù)”一樣嗎？ Hint：IN、SUM、HAVING Answer: customer_idSUM(quantity)NO1000014NO1000022 第七關(guān)：查詢NO100001、NO100002、NO100003三位用戶各自購買商品的總數(shù)(不區(qū)分商品類型)，輸出購買總數(shù)前兩名的用戶id以及他們對(duì)應(yīng)購買的商品總數(shù)？ Hint：IN、SUM、ORDER BY、LIMIT Answer: customer_idSUM(quantity)NO1000014NO1000022 第八關(guān)：查詢所有用戶各自購買商品的總數(shù)(不區(qū)分商品類型)，輸出購買商品件數(shù)大于等于2件的用戶id以及他們對(duì)應(yīng)購買的商品總數(shù)？要求給出至少兩種寫法。 Warning：注意是“所有用戶”，不是所有的用戶都購買了商品 Hint：關(guān)聯(lián)查詢有哪些方法？ Answer: 寫法一：嵌套的SELECT customer_idSUM(quantity)NO1000014NO1000022 寫法二：使用LEFT JOIN語法 customer_idSUM(tb_order.quantity)NO1000014NO1000022 第九關(guān)：查詢所有用戶各自購買商品的總數(shù)(不區(qū)分商品類型)，輸出購買總數(shù)前兩名的用戶id以及他們對(duì)應(yīng)購買的商品總數(shù)？要求給出至少兩種寫法。 Hint：關(guān)聯(lián)查詢有哪些方法？ Answer: 寫法一：嵌套的SELECT customer_idSUM(quantity)NO1000014NO1000022 寫法二：使用LEFT JOIN語法 customer_idSUM(tb_order.quantity)NO1000014NO1000022 第十關(guān)：以下哪幾條Sql語句使用到了索引？分別是哪些字段上的索引？是什么類型的索引？ Hint：索引 Answer: sql序號(hào)是否使用到索引索引所在字段索引類型1是customer_idUNIQUE KEY2否--3是order_idUNIQUE KEY4是idPRIMARY KEY 閱讀博客還不過癮？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 '商品價(jià)格',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_product_id` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='商品信息表';
idcustomer_idnamegenderbalance1NO100001火火女188882NO100002撥潑抹女90003NO100003艾橋男79904NO100004水娃女8388CREATE 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='用戶信息表';
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 '商品價(jià)格',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_order_id` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='訂單數(shù)據(jù)表';
SELECT customer_id, name FROM tb_customer WHERE balance > 10000;
SELECT name FROM tb_customer WHERE balance < 10000 AND gender="女";
SELECT order_id FROM tb_order WHERE customer_id IN ("NO100001", "NO100002");
SELECT * FROM tb_order WHERE customer_id IN ("NO100001", "NO100002")
ORDER BY product_id ASC, order_id DESC;
SELECT COUNT(customer_id) FROM tb_customer WHERE gender="女";
SELECT customer_id, SUM(quantity) FROM tb_order
WHERE customer_id IN ("NO100001", "NO100002", "NO100003")
GROUP BY customer_id
HAVING SUM(quantity) >= 2;
SELECT customer_id, SUM(quantity) FROM tb_order
WHERE customer_id IN ("NO100001", "NO100002", "NO100003")
GROUP BY customer_id
ORDER BY SUM(quantity) DESC
LIMIT 2;
SELECT customer_id, SUM(quantity) FROM tb_order
WHERE customer_id IN (SELECT customer_id FROM tb_customer)
GROUP BY customer_id
HAVING SUM(quantity) >= 2;
SELECT tb_customer.customer_id, SUM(tb_order.quantity) FROM tb_customer
LEFT JOIN tb_order ON tb_customer.customer_id = tb_order.customer_id
GROUP BY tb_customer.customer_id
HAVING SUM(tb_order.quantity) >= 2;
SELECT customer_id, SUM(quantity) FROM tb_order
WHERE customer_id IN (SELECT customer_id FROM tb_customer)
GROUP BY customer_id
ORDER BY SUM(quantity) DESC
LIMIT 2;
SELECT tb_customer.customer_id, SUM(tb_order.quantity) FROM tb_customer
LEFT JOIN tb_order ON tb_customer.customer_id = tb_order.customer_id
GROUP BY tb_customer.customer_id
ORDER BY SUM(tb_order.quantity) DESC
LIMIT 2;
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;
本文為作者獨(dú)立觀點(diǎn)，不代表鳥哥筆記立場(chǎng)，未經(jīng)允許不得轉(zhuǎn)載。
《鳥哥筆記版權(quán)及免責(zé)申明》 如對(duì)文章、圖片、字體等版權(quán)有疑問，請(qǐng)點(diǎn)擊 反饋舉報(bào)
上一篇
我們致力于提供一個(gè)高質(zhì)量內(nèi)容的交流平臺(tái)。為落實(shí)國家互聯(lián)網(wǎng)信息辦公室“依法管網(wǎng)、依法辦網(wǎng)、依法上網(wǎng)”的要求，為完善跟帖評(píng)論自律管理，為了保護(hù)用戶創(chuàng)造的內(nèi)容、維護(hù)開放、真實(shí)、專業(yè)的平臺(tái)氛圍，我們團(tuán)隊(duì)將依據(jù)本公約中的條款對(duì)注冊(cè)用戶和發(fā)布在本平臺(tái)的內(nèi)容進(jìn)行管理。平臺(tái)鼓勵(lì)用戶創(chuàng)作、發(fā)布優(yōu)質(zhì)內(nèi)容，同時(shí)也將采取必要措施管理違法、侵權(quán)或有其他不良影響的網(wǎng)絡(luò)信息。
一、根據(jù)《網(wǎng)絡(luò)信息內(nèi)容生態(tài)治理規(guī)定》《中華人民共和國未成年人保護(hù)法》等法律法規(guī)，對(duì)以下違法、不良信息或存在危害的行為進(jìn)行處理。
1. 違反法律法規(guī)的信息，主要表現(xiàn)為：
1)反對(duì)憲法所確定的基本原則；
2)危害國家安全，泄露國家秘密，顛覆國家政權(quán)，破壞國家統(tǒng)一，損害國家榮譽(yù)和利益；
3)侮辱、濫用英烈形象，歪曲、丑化、褻瀆、否定英雄烈士事跡和精神，以侮辱、誹謗或者其他方式侵害英雄烈士的姓名、肖像、名譽(yù)、榮譽(yù)；
4)宣揚(yáng)恐怖主義、極端主義或者煽動(dòng)實(shí)施恐怖活動(dòng)、極端主義活動(dòng)；
5)煽動(dòng)民族仇恨、民族歧視，破壞民族團(tuán)結(jié)；
6)破壞國家宗教政策，宣揚(yáng)邪教和封建迷信；
7)散布謠言，擾亂社會(huì)秩序，破壞社會(huì)穩(wěn)定；
8)宣揚(yáng)淫穢、色情、賭博、暴力、兇殺、恐怖或者教唆犯罪；
9)煽動(dòng)非法集會(huì)、結(jié)社、游行、示威、聚眾擾亂社會(huì)秩序；
10)侮辱或者誹謗他人，侵害他人名譽(yù)、隱私和其他合法權(quán)益；
11)通過網(wǎng)絡(luò)以文字、圖片、音視頻等形式，對(duì)未成年人實(shí)施侮辱、誹謗、威脅或者惡意損害未成年人形象進(jìn)行網(wǎng)絡(luò)欺凌的；
12)危害未成年人身心健康的；
13)含有法律、行政法規(guī)禁止的其他內(nèi)容；
2. 不友善：不尊重用戶及其所貢獻(xiàn)內(nèi)容的信息或行為。主要表現(xiàn)為：
1)輕蔑：貶低、輕視他人及其勞動(dòng)成果；
2)誹謗：捏造、散布虛假事實(shí)，損害他人名譽(yù)；
3)嘲諷：以比喻、夸張、侮辱性的手法對(duì)他人或其行為進(jìn)行揭露或描述，以此來激怒他人；
4)挑釁：以不友好的方式激怒他人，意圖使對(duì)方對(duì)自己的言論作出回應(yīng)，蓄意制造事端；
5)羞辱：貶低他人的能力、行為、生理或身份特征，讓對(duì)方難堪；
6)謾罵：以不文明的語言對(duì)他人進(jìn)行負(fù)面評(píng)價(jià)；
7)歧視：煽動(dòng)人群歧視、地域歧視等，針對(duì)他人的民族、種族、宗教、性取向、性別、年齡、地域、生理特征等身份或者歸類的攻擊；
8)威脅：許諾以不良的后果來迫使他人服從自己的意志；
3. 發(fā)布垃圾廣告信息：以推廣曝光為目的，發(fā)布影響用戶體驗(yàn)、擾亂本網(wǎng)站秩序的內(nèi)容，或進(jìn)行相關(guān)行為。主要表現(xiàn)為：
1)多次發(fā)布包含售賣產(chǎn)品、提供服務(wù)、宣傳推廣內(nèi)容的垃圾廣告。包括但不限于以下幾種形式：
2)單個(gè)帳號(hào)多次發(fā)布包含垃圾廣告的內(nèi)容；
3)多個(gè)廣告帳號(hào)互相配合發(fā)布、傳播包含垃圾廣告的內(nèi)容；
4)多次發(fā)布包含欺騙性外鏈的內(nèi)容，如未注明的淘寶客鏈接、跳轉(zhuǎn)網(wǎng)站等，誘騙用戶點(diǎn)擊鏈接
5)發(fā)布大量包含推廣鏈接、產(chǎn)品、品牌等內(nèi)容獲取搜索引擎中的不正當(dāng)曝光；
6)購買或出售帳號(hào)之間虛假地互動(dòng)，發(fā)布干擾網(wǎng)站秩序的推廣內(nèi)容及相關(guān)交易。
7)發(fā)布包含欺騙性的惡意營銷內(nèi)容，如通過偽造經(jīng)歷、冒充他人等方式進(jìn)行惡意營銷；
8)使用特殊符號(hào)、圖片等方式規(guī)避垃圾廣告內(nèi)容審核的廣告內(nèi)容。
4. 色情低俗信息，主要表現(xiàn)為：
1)包含自己或他人性經(jīng)驗(yàn)的細(xì)節(jié)描述或露骨的感受描述；
2)涉及色情段子、兩性笑話的低俗內(nèi)容；
3)配圖、頭圖中包含庸俗或挑逗性圖片的內(nèi)容；
4)帶有性暗示、性挑逗等易使人產(chǎn)生性聯(lián)想；
5)展現(xiàn)血腥、驚悚、殘忍等致人身心不適；
6)炒作緋聞、丑聞、劣跡等；
7)宣揚(yáng)低俗、庸俗、媚俗內(nèi)容。
5. 不實(shí)信息，主要表現(xiàn)為：
1)可能存在事實(shí)性錯(cuò)誤或者造謠等內(nèi)容；
2)存在事實(shí)夸大、偽造虛假經(jīng)歷等誤導(dǎo)他人的內(nèi)容；
3)偽造身份、冒充他人，通過頭像、用戶名等個(gè)人信息暗示自己具有特定身份，或與特定機(jī)構(gòu)或個(gè)人存在關(guān)聯(lián)。
6. 傳播封建迷信，主要表現(xiàn)為：
1)找人算命、測(cè)字、占卜、解夢(mèng)、化解厄運(yùn)、使用迷信方式治?。?br /> 2)求推薦算命看相大師；
3)針對(duì)具體風(fēng)水等問題進(jìn)行求助或咨詢；
4)問自己或他人的八字、六爻、星盤、手相、面相、五行缺失，包括通過占卜方法問婚姻、前程、運(yùn)勢(shì)，東西寵物丟了能不能找回、取名改名等；
7. 文章標(biāo)題黨，主要表現(xiàn)為：
1)以各種夸張、獵奇、不合常理的表現(xiàn)手法等行為來誘導(dǎo)用戶；
2)內(nèi)容與標(biāo)題之間存在嚴(yán)重不實(shí)或者原意扭曲；
3)使用夸張標(biāo)題，內(nèi)容與標(biāo)題嚴(yán)重不符的。
8.「飯圈」亂象行為，主要表現(xiàn)為：
1)誘導(dǎo)未成年人應(yīng)援集資、高額消費(fèi)、投票打榜
2)粉絲互撕謾罵、拉踩引戰(zhàn)、造謠攻擊、人肉搜索、侵犯隱私
3)鼓動(dòng)「飯圈」粉絲攀比炫富、奢靡享樂等行為
4)以號(hào)召粉絲、雇用網(wǎng)絡(luò)水軍、「養(yǎng)號(hào)」形式刷量控評(píng)等行為
5)通過「蹭熱點(diǎn)」、制造話題等形式干擾輿論，影響傳播秩序
9. 其他危害行為或內(nèi)容，主要表現(xiàn)為：
1)可能引發(fā)未成年人模仿不安全行為和違反社會(huì)公德行為、誘導(dǎo)未成年人不良嗜好影響未成年人身心健康的；
2)不當(dāng)評(píng)述自然災(zāi)害、重大事故等災(zāi)難的；
3)美化、粉飾侵略戰(zhàn)爭行為的；
4)法律、行政法規(guī)禁止，或可能對(duì)網(wǎng)絡(luò)生態(tài)造成不良影響的其他內(nèi)容。
二、違規(guī)處罰
本網(wǎng)站通過主動(dòng)發(fā)現(xiàn)和接受用戶舉報(bào)兩種方式收集違規(guī)行為信息。所有有意的降低內(nèi)容質(zhì)量、傷害平臺(tái)氛圍及欺凌未成年人或危害未成年人身心健康的行為都是不能容忍的。
當(dāng)一個(gè)用戶發(fā)布違規(guī)內(nèi)容時(shí)，本網(wǎng)站將依據(jù)相關(guān)用戶違規(guī)情節(jié)嚴(yán)重程度，對(duì)帳號(hào)進(jìn)行禁言 1 天、7 天、15 天直至永久禁言或封停賬號(hào)的處罰。當(dāng)涉及欺凌未成年人、危害未成年人身心健康、通過作弊手段注冊(cè)、使用帳號(hào)，或者濫用多個(gè)帳號(hào)發(fā)布違規(guī)內(nèi)容時(shí)，本網(wǎng)站將加重處罰。
三、申訴
隨著平臺(tái)管理經(jīng)驗(yàn)的不斷豐富，本網(wǎng)站出于維護(hù)本網(wǎng)站氛圍和秩序的目的，將不斷完善本公約。
如果本網(wǎng)站用戶對(duì)本網(wǎng)站基于本公約規(guī)定做出的處理有異議，可以通過「建議反饋」功能向本網(wǎng)站進(jìn)行反饋。
(規(guī)則的最終解釋權(quán)歸屬本網(wǎng)站所有)