本文主要介紹博主在以往開發(fā)過(guò)程中，對(duì)于不同業(yè)務(wù)所對(duì)應(yīng)的 sql 寫法進(jìn)行歸納總結(jié)而來(lái)。進(jìn)而分享給大家。

• 本文所講述 sql 語(yǔ)法都是基于 MySql 8.0

• 博主github地址：http://github.com/wayn111? 歡迎大家關(guān)注，點(diǎn)個(gè)star

一、ORDER BY FIELD() 自定義排序邏輯

MySql 中的排序 ORDER BY 除了可以用 ASC 和 DESC，還可以通過(guò) 「ORDER BY FIELD(str,str1,...)」 自定義字符串/數(shù)字來(lái)實(shí)現(xiàn)排序。這里用 order_diy 表舉例，結(jié)構(gòu)以及表數(shù)據(jù)展示：

ORDER BY FIELD(str,str1,...) 自定義排序sql如下：

SELECT?*?from?order_diy?ORDER?BY?FIELD(title,'九陰真經(jīng)',?
'降龍十八掌','九陰白骨爪','雙手互博','桃花島主',
'全真內(nèi)功心法','蛤蟆功','銷魂掌','靈白山少主');


查詢結(jié)果如下：

如上，我們?cè)O(shè)置自定義排序字段為 title 字段，然后將我們自定義的排序結(jié)果跟在 title 后面。

二、CASE 表達(dá)式

「case when then else end」表達(dá)式功能非常強(qiáng)大可以幫助我們解決 if elseif else 這種問(wèn)題，這里繼續(xù)用 order_diy 表舉例，假如我們想在 order_diy 表加一列 level 列，根據(jù)money 判斷大于60就是高級(jí)，大于30就是中級(jí)，其余顯示低級(jí)，sql 如下：

SELECT?*,?
case?when?money?>?60?then?'高級(jí)'?
when?money?>?30?then?'中級(jí)'?
else?'低級(jí)'?END?level?
from?order_diy;


查詢結(jié)果：

三、EXISTS 用法

我猜大家在日常開發(fā)中，應(yīng)該都對(duì)關(guān)鍵詞 exists 用的比較少，估計(jì)使用 in 查詢偏多。這里給大家介紹一下 exists 用法，引用官網(wǎng)文檔:

可知 exists 后面是跟著一個(gè)子查詢語(yǔ)句，它的作用是「根據(jù)主查詢的數(shù)據(jù)，每一行都放到子查詢中做條件驗(yàn)證，根據(jù)驗(yàn)證結(jié)果（TRUE 或者 FALSE），TRUE的話該行數(shù)據(jù)就會(huì)保留」，下面用 emp 表和 dept 表進(jìn)行舉例，表結(jié)構(gòu)以及數(shù)據(jù)展示：

計(jì)入我們現(xiàn)在想找到 emp 表中 dept_name 與 dept表 中 dept_name 對(duì)應(yīng)不上員工數(shù)據(jù)，sql 如下：

SELECT?*?from?emp?e?where?exists?(
SELECT?*?from?dept?p?where?e.dept_id?=?p.dept_id?
and?e.dept_name?!=?p.dept_name
)


查詢結(jié)果：

我們通過(guò) exists 語(yǔ)法將外層 emp 表全部數(shù)據(jù) 放到子查詢中與一一與 dept 表全部數(shù)據(jù)進(jìn)行比較，只要有一行記錄返回true。畫個(gè)圖展示主查詢所有記錄與子查詢交互如下：

• 第一條記錄與子查詢比較時(shí)，全部返回 false，所以第一行不展示。

• 第二行記錄與子查詢比較時(shí)，發(fā)現(xiàn) 銷售部門 與 dept 表第二行 銷售部 對(duì)應(yīng)不上，返回 true，所以主查詢?cè)撔杏涗洉?huì)返回。

• 第二行以后記錄執(zhí)行結(jié)果同第一條。

四、GROUP_CONCAT(expr) 組連接函數(shù)

「GROUP_CONCAT(expr)」 組連接函數(shù)可以返回分組后指定字段的字符串連接形式，并且可以指定排序邏輯，以及連接字符串，默認(rèn)為英文逗號(hào)連接。這里繼續(xù)用 order_diy 表舉例：sql 如下：

SELECT?name,?GROUP_CONCAT(title?ORDER?BY?id?desc??SEPARATOR?'-')?
from?order_diy?GROUP?BY?name?ORDER?BY?NULL;


查詢結(jié)果：

如上我們通過(guò) 「GROUP_CONCAT(title ORDER BY id desc SEPARATOR '-')」 語(yǔ)句，指定分組連接 title 字段并按照 id 排序，設(shè)置連接字符串為 -。

五、自連接查詢

自連接查詢是 sql 語(yǔ)法里常用的一種寫法，掌握了自連接的用法我們可以在 sql 層面輕松解決很多問(wèn)題。這里用 tree 表舉例，結(jié)構(gòu)以及表數(shù)據(jù)展示：

tree 表中通過(guò) pid 字段與 id 字段進(jìn)行父子關(guān)聯(lián)，假如現(xiàn)在有一個(gè)需求，我們想按照父子層級(jí)將 tree 表數(shù)據(jù)轉(zhuǎn)換成 一級(jí)職位 二級(jí)職位 三級(jí)職位 三個(gè)列名進(jìn)行展示，sql 如下：

SELECT?t1.job_name?'一級(jí)職位',?t2.job_name?'二級(jí)職位',?t3.job_name?'三級(jí)職位'?
from?tree?t1?join?tree?t2?on?t1.id?=?t2.pid?left?join?tree?t3?on?t2.id?=?t3.pid?
where?t1.pid?=?0;


結(jié)果如下：

我們通過(guò) 「tree t1 join tree t2 on t1.id = t2.pid」 自連接展示 一級(jí)職位 二級(jí)職位，再用 「left join tree t3 on t2.id = t3.pid」 自連接展示 二級(jí)職位 三級(jí)職位，最后通過(guò)「where 條件 t1.pid = 0」過(guò)濾掉非一級(jí)職位的展示，完成這個(gè)需求。

六、更新 emp 表和 dept 表關(guān)聯(lián)數(shù)據(jù)

這里繼續(xù)使用上文提到的 emp 表和 dept 表，數(shù)據(jù)如下：

可以看到上述 emp 表中 jack 的部門名稱與 dept 表實(shí)際不符合，現(xiàn)在我們想將 jack 的部門名稱更新成 dept 表的正確數(shù)據(jù)，sql 如下：

update?emp,?dept?set?emp.dept_name?=?dept.dept_name
where?emp.dept_id?=?dept.dept_id;


查詢結(jié)果：

我們可以直接關(guān)聯(lián) emp 表和 dept 表并設(shè)置關(guān)聯(lián)條件，然后更新 emp 表的 dept_name 為 dept 表的 dept_name。

七、ORDER BY 空值 NULL 排序

ORDER BY 字句中可以跟我們要排序的字段名稱，但是當(dāng)字段中存在 null 值時(shí)，會(huì)對(duì)我們的排序結(jié)果造成影響。我們可以通過(guò) 「ORDER BY IF(ISNULL(title), 1, 0)」 語(yǔ)法將 null 值轉(zhuǎn)換成0或1，來(lái)達(dá)到將 null 值放到前面還是后面進(jìn)行排序的效果。這里繼續(xù)用 order_diy 表舉例，sql 如下：

SELECT?*?FROM?order_diy?ORDER?BY??IF(ISNULL(title),?0,?1),?money;


查詢結(jié)果：

八、with rollup 分組統(tǒng)計(jì)數(shù)據(jù)的基礎(chǔ)上再進(jìn)行統(tǒng)計(jì)匯總

MySql 中可以使用 with rollup 在分組統(tǒng)計(jì)數(shù)據(jù)的基礎(chǔ)上再進(jìn)行統(tǒng)計(jì)匯總，即用來(lái)得到 group by 的匯總信息。這里繼續(xù)用order_diy 表舉例，sql 如下：

SELECT?name,?SUM(money)?as?money?
FROM?order_diy?GROUP?BY?name?WITH?ROLLUP;


查詢結(jié)果：

可以看到通過(guò) 「GROUP BY name WITH ROLLUP」 語(yǔ)句，查詢結(jié)果最后一列顯示了分組統(tǒng)計(jì)的匯總結(jié)果。但是 name 字段最后顯示為 null，我們可以通過(guò) coalesce() 比較函數(shù)，返回第一個(gè)非空參數(shù)。

SELECT?coalesce(name,?'總金額')?name,?SUM(money)?as?money?
FROM?order_diy?GROUP?BY?name?WITH?ROLLUP;


查詢結(jié)果：

九、with as 提取臨時(shí)表別名

with as 語(yǔ)法需要 MySql 8.0以上版本，它的作用主要是提取子查詢，方便后續(xù)共用，更多情況下會(huì)用在數(shù)據(jù)分析的場(chǎng)景上。

如果一整句查詢中「多個(gè)子查詢都需要使用同一個(gè)子查詢」的結(jié)果，那么就可以用with as，將共用的子查詢提取出來(lái)，加個(gè)別名。后面查詢語(yǔ)句可以直接用，對(duì)于大量復(fù)雜的SQL語(yǔ)句起到了很好的優(yōu)化作用。這里繼續(xù)用 order_diy 表舉例，這里使用with as給出sql 如下：

--?使用?with?as
with?t1?as?(SELECT?*?from?order_diy?where?money?>?30),
t2?as?(SELECT?*?from?order_diy?where?money?>?60)
SELECT?*?from?t1?
where?t1.id?not?in?(SELECT?id?from??t2)?and?t1.name?=?'周伯通';


查詢結(jié)果：

這個(gè) sql 查詢了 order_diy 表中 money 大于30且小于等于60之間并且 name 是周伯通的記錄。

10、存在就更新，不存在就插入

MySql 中通過(guò)「on duplicate key update」語(yǔ)法來(lái)實(shí)現(xiàn)存在就更新，不存在就插入的邏輯。插入或者更新時(shí)，它會(huì)根據(jù)表中主鍵索引或者唯一索引進(jìn)行判斷，如果主鍵索引或者唯一索引有沖突，就會(huì)執(zhí)行「on duplicate key update」后面的賦值語(yǔ)句。 這里通過(guò) news 表舉例，表結(jié)構(gòu)和說(shuō)數(shù)據(jù)展示，其中 news_code 字段有唯一索引：

添加sql：

--?第一次執(zhí)行添加語(yǔ)句
INSERT?INTO?`news`?(`news_title`,?`news_auth`,?`news_code`)?
VALUES?('新聞3',?'小花',?'wx-0003')?
on?duplicate?key?update?news_title?=?'新聞3';
--?第二次執(zhí)行修改語(yǔ)句
INSERT?INTO?`news`?(`news_title`,?`news_auth`,?`news_code`)?
VALUES?('新聞4',?'小花',?'wx-0003')?
on?duplicate?key?update?news_title?=?'新聞4';


結(jié)果如下：

總結(jié)

到這里，本文所分享的10個(gè)高級(jí)sql寫法就全部介紹完了，希望對(duì)大家日常開發(fā) sql 編寫有所幫助，喜歡的朋友們可以點(diǎn)贊加關(guān)注??。