由于BC一條龍工作搭建需要，最近做了很多 BI 取數(shù)的工作，需要用到一些比較高級的 SQL 技巧，總結(jié)了一下工作中用到的一些比較騷的進階技巧，特此記錄一下，以方便自己以后搭建網(wǎng)站一條龍查閱，主要目錄如下：
SQL 的書寫規(guī)范
SQL 的一些進階使用技巧
SQL 的優(yōu)化方法
SQL 的書寫規(guī)范
在介紹一些技巧之前，有必要強調(diào)一下規(guī)范，這一點我發(fā)現(xiàn)工作中經(jīng)常被人忽略，其實遵循好的規(guī)范可讀性會好很多，應(yīng)該遵循哪些規(guī)范呢
1、 表明要有意義，且標(biāo)準(zhǔn) SQL 中規(guī)定表名的第一個字符應(yīng)該是字母。
2、注釋，有單行注釋和多行注釋，如下
多行注釋很多人不知道，這種寫法不僅可以用來添加真正的注釋，也可以用來注釋代碼，非常方便
3、縮進
就像寫 Java，Python 等編程語言一樣 ，SQL 也應(yīng)該有縮進，良好的縮進對提升代碼的可讀性幫助很大，以下分別是好的縮進與壞的縮進示例
4、空格
代碼中應(yīng)該適當(dāng)留有一些空格，如果一點不留，代碼都湊到一起， 邏輯單元不明確，閱讀的人也會產(chǎn)生額外的壓力，以下分別是是好的與壞的示例
5、大小寫
關(guān)鍵字使用大小寫，表明列名使用小寫，如下
花了這么多時間強調(diào)規(guī)范，有必要嗎，有！好的規(guī)范讓代碼的可讀性更好，更有利于團隊合作，之后的 SQL 示例都會遵循這些規(guī)范。
SQL 的一些進階使用技巧
1、巧用 CASE WHEN 進行統(tǒng)計
來看看如何巧用 CASE WHEN 進行定制化統(tǒng)計,假設(shè)我們有如下的需求，希望根據(jù)左邊各個市的人口統(tǒng)計每個省的人口
使用 CASE WHEN 如下
2、巧用 CASE WHEN 進行更新
現(xiàn)在某公司員工工資信息表如下:
現(xiàn)在公司出臺了一個奇葩的規(guī)定
對當(dāng)前工資為 1 萬以上的員工，降薪 10%。
對當(dāng)前工資低于 1 萬的員工，加薪 20%。
一些人不假思索可能寫出了以下的 SQL：
這么做其實是有問題的， 什么問題，對小明來說，他的工資是 10500，執(zhí)行第一個 SQL 后，工資變?yōu)?10500 0.9 = 9450, 緊接著又執(zhí)行條件 2， 工資變?yōu)榱?9450 1.2 = 11340，反而漲薪了！
如果用 CASE WHEN 可以解決此類問題，如下：
3、巧用 HAVING 子句
一般 HAVING 是與 GROUP BY 結(jié)合使用的，但其實它是可以獨立使用的， 假設(shè)有如下表，第一列 seq 叫連續(xù)編號，但其實有些編號是缺失的，怎么知道編號是否缺失呢，
用 HAVING 表示如下:
SELECT '存在缺失的編號' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);
1.
2.
3.
4、自連接
針對相同的表進行的連接被稱為“自連接”(self join)，這個技巧常常被人們忽視，其實是有挺多妙用的
1、刪除重復(fù)行
上圖中有三個句子，需要把這些重復(fù)的行給刪掉，用如下自連接可以解決：
2、排序
在 db 中，我們經(jīng)常需要按分?jǐn)?shù)，人數(shù)，銷售額等進行排名，有 Oracle, DB2 中可以使用 RANK 函數(shù)進行排名，不過在 MySQL 中 RANK 函數(shù)未實現(xiàn)，這種情況我們可以使用自連接來實現(xiàn),如對以下 Products 表按價格高低進行排名
使用自連接可以這么寫：
結(jié)果如下：
5、巧用 COALESCE 函數(shù)
此函數(shù)作用返回參數(shù)中的第一個非空表達式，假設(shè)有如下商品，我們重新格式化一樣，如果 city 為 null，代表商品不在此城市發(fā)行，但我們在展示結(jié)果的時候不想展示 null，而想展示 'N/A', 可以這么做:
SELECT
COALESCE(city, 'N/A')
FROM
customers;
1.
2.
3.
4.
SQL 性能優(yōu)化技巧
1、參數(shù)是子查詢時，使用 EXISTS 代替 IN
如果 IN 的參數(shù)是（1，2，3）這樣的值列表時，沒啥問題，但如果參數(shù)是子查詢時，就需要注意了。比如，現(xiàn)在有如下兩個表：
現(xiàn)在我們要查出同時存在于兩個表的員工，即田中和鈴木，則以下用 IN 和 EXISTS 返回的結(jié)果是一樣，但是用 EXISTS 的 SQL 會更快：
為啥使用 EXISTS 的 SQL 運行更快呢，有兩個原因
可以`用到索引，如果連接列 (id) 上建立了索引，那么查詢 Class_B 時不用查實際的表，只需查索引就可以了。
如果使用 EXISTS，那么只要查到一行數(shù)據(jù)滿足條件就會終止查詢， 不用像使用 IN 時一樣掃描全表。在這一點上 NOT EXISTS 也一樣
另外如果 IN 后面如果跟著的是子查詢，由于 SQL 會先執(zhí)行 IN 后面的子查詢，會將子查詢的結(jié)果保存在一張臨時的工作表里（內(nèi)聯(lián)視圖），然后掃描整個視圖，顯然掃描整個視圖這個工作很多時候是非常耗時的，而用 EXISTS 不會生成臨時表。
當(dāng)然了，如果 IN 的參數(shù)是子查詢時，也可以用連接來代替，如下：
-- 使用連接代替 IN SELECT A.id, A.name
FROM Class_A A INNER JOIN Class_B B ON A.id = B.id;
1.
2.
用到了 「id」列上的索引，而且由于沒有子查詢，也不會生成臨時表
2、避免排序
SQL 是聲明式語言，即對用戶來說，只關(guān)心它能做什么，不用關(guān)心它怎么做。這樣可能會產(chǎn)生潛在的性能問題：排序，會產(chǎn)生排序的代表性運算有下面這些
GROUP BY 子句
ORDER BY 子句
聚合函數(shù)(SUM、COUNT、AVG、MAX、MIN)
DISTINCT
集合運算符(UNION、INTERSECT、EXCEPT)
窗口函數(shù)(RANK、ROW_NUMBER 等)
如果在內(nèi)存中排序還好，但如果內(nèi)存不夠?qū)е滦枰谟脖P上排序上的話，性能就會急劇下降，所以我們需要減少不必要的排序。怎樣做可以減少排序呢。
1、 使用集合運算符的 ALL 可選項
SQL 中有 UNION，INTERSECT，EXCEPT 三個集合運算符，默認(rèn)情況下，這些運算符會為了避免重復(fù)數(shù)據(jù)而進行排序，對比一下使用 UNION 運算符加和不加 ALL 的情況。
聯(lián)系客服
