分享10個高級sql寫法
theme: channing-cyan
本文正在參加「金石計劃」
本文主要介紹博主在以往開發過程中,對於不同業務所對應的 sql 寫法進行歸納總結而來。進而分享給大家。 - 本文所講述 sql 語法都是基於 MySql 8.0+ - 博主github地址:http://github.com/wayn111 歡迎大家關注,點個star
一、ORDER BY FIELD() 自定義排序邏輯
MySql 中的排序 ORDER BY 除了可以用 ASC 和 DESC,還可以通過 ORDER BY FIELD(str,str1,...) 自定義字符串/數字來實現排序。這裏用 order_diy 表舉例,結構以及表數據展示:
ORDER BY FIELD(str,str1,...) 自定義排序sql如下:
sql
SELECT * from order_diy ORDER BY FIELD(title,'九陰真經',
'降龍十八掌','九陰白骨爪','雙手互博','桃花島主',
'全真內功心法','蛤蟆功','銷魂掌','靈白山少主');
查詢結果如下:
如上,我們設置自定義排序字段為 title 字段,然後將我們自定義的排序結果跟在 title 後面。
二、CASE 表達式
case when then else end表達式功能非常強大可以幫助我們解決 if elseif else
這種問題,這裏繼續用 order_diy 表舉例,假如我們想在 order_diy 表加一列 level 列,根據money 判斷大於60就是高級,大於30就是中級,其餘顯示低級,sql 如下:
sql
SELECT *,
case when money > 60 then '高級'
when money > 30 then '中級'
else '低級' END level
from order_diy;
查詢結果:
三、EXISTS 用法
我猜大家在日常開發中,應該都對關鍵詞 exists 用的比較少,估計使用 in 查詢偏多。這裏給大家介紹一下 exists 用法,引用官網文檔:
可知 exists 後面是跟着一個子查詢語句,它的作用是根據主查詢的數據,每一行都放到子查詢中做條件驗證,根據驗證結果(TRUE 或者 FALSE),TRUE的話該行數據就會保留,下面用 emp 表和 dept 表進行舉例,表結構以及數據展示:
假如我們現在想找到 emp 表中 dept_name 與 dept表中 dept_name 對應不上的員工數據,也就是 emp 表第二行記錄,sql 如下:
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
)
查詢結果:
我們通過 exists 語法將外層 emp 表全部數據 放到子查詢中與一一與 dept 表全部數據進行比較,只要有一行記錄返回true。畫個圖展示主查詢所有記錄與子查詢交互如下:
- 第一條記錄與子查詢比較時,全部返回 false,所以第一行不展示。
- 第二行記錄與子查詢比較時,發現
銷售部門
與 dept 表第二行 銷售部
對應不上,返回 true,所以主查詢該行記錄會返回。
- 第二行以後記錄執行結果同第一條。
四、GROUP_CONCAT(expr) 組連接函數
GROUP_CONCAT(expr) 組連接函數可以返回分組後指定字段的字符串連接形式,並且可以指定排序邏輯,以及連接字符串,默認為英文逗號連接。這裏繼續用 order_diy 表舉例:sql 如下:
sql
SELECT name, GROUP_CONCAT(title ORDER BY id desc SEPARATOR '-')
from order_diy GROUP BY name ORDER BY NULL;
查詢結果:
如上我們通過 GROUP_CONCAT(title ORDER BY id desc SEPARATOR '-') 語句,指定分組連接 title 字段並按照 id 排序,設置連接字符串為 -
。
五、自連接查詢
自連接查詢是 sql 語法裏常用的一種寫法,掌握了自連接的用法我們可以在 sql 層面輕鬆解決很多問題。這裏用 tree 表舉例,結構以及表數據展示:
tree 表中通過 pid 字段與 id 字段進行父子關聯,假如現在有一個需求,我們想按照父子層級將 tree 表數據轉換成
一級職位 二級職位 三級職位
三個列名進行展示,sql 如下:
sql
SELECT t1.job_name '一級職位',
t2.job_name '二級職位',
t3.job_name '三級職位'
from tree t1 join tree t2 on t1.id = t2.pid
left join tree t3 on t2.id = t3.pid
where t1.pid = 0;
結果如下:
我們通過 tree t1 join tree t2 on t1.id = t2.pid 自連接展示
一級職位 二級職位
,再用 left join tree t3 on t2.id = t3.pid 自連接展示 二級職位 三級職位
,最後通過where 條件 t1.pid = 0過濾掉非一級職位的展示,完成這個需求。
六、更新 emp 表和 dept 表關聯數據
這裏繼續使用上文提到的 emp 表和 dept 表,數據如下:
可以看到上述 emp 表中 jack 的部門名稱與 dept 表實際不符合,現在我們想將 jack 的部門名稱更新成 dept 表的正確數據,sql 如下:
sql
update emp, dept set emp.dept_name = dept.dept_name
where emp.dept_id = dept.dept_id;
查詢結果:
我們可以直接關聯 emp 表和 dept 表並設置關聯條件,然後更新 emp 表的 dept_name 為 dept 表的 dept_name。
七、ORDER BY 空值 NULL 排序
ORDER BY 字句中可以跟我們要排序的字段名稱,但是當字段中存在 null 值時,會對我們的排序結果造成影響。我們可以通過 ORDER BY IF(ISNULL(title), 1, 0) 語法將 null 值轉換成0或1,來達到將 null 值放到前面還是後面進行排序的效果。這裏繼續用 order_diy 表舉例,sql 如下:
sql
SELECT * FROM order_diy ORDER BY IF(ISNULL(title), 0, 1), money;
查詢結果:
八、with rollup 分組統計數據的基礎上再進行統計彙總
MySql 中可以使用 with rollup 在分組統計數據的基礎上再進行統計彙總,即用來得到 group by 的彙總信息。這裏繼續用order_diy 表舉例,sql 如下:
sql
SELECT name, SUM(money) as money
FROM order_diy GROUP BY name WITH ROLLUP;
查詢結果:
可以看到通過 GROUP BY name WITH ROLLUP 語句,查詢結果最後一列顯示了分組統計的彙總結果。但是 name 字段彙總後顯示為 null,我們可以通過 COALESCE(value,...)
比較函數,返回第一個非空參數。
sql
SELECT coalesce(name, '總金額') name, SUM(money) as money
FROM order_diy GROUP BY name WITH ROLLUP;
查詢結果:
九、with as 提取臨時表別名
with as 語法需要 MySql 8.0以上版本,它的作用主要是提取子查詢,方便後續共用,更多情況下會用在數據分析的場景上。
如果一整句查詢中多個子查詢都需要使用同一個子查詢的結果,那麼就可以用with as,將共用的子查詢提取出來,加個別名。後面查詢語句可以直接用,對於大量複雜的SQL語句起到了很好的優化作用。這裏繼續用 order_diy 表舉例,這裏使用with as給出sql 如下:
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 = '周伯通';
查詢結果:
這個 sql 查詢了 order_diy 表中 money 大於30且小於等於60之間並且 name 是周伯通的記錄。
10、存在就更新,不存在就插入
MySql 中通過on duplicate key update語法來實現存在就更新,不存在就插入的邏輯。插入或者更新時,它會根據表中主鍵索引或者唯一索引進行判斷,如果主鍵索引或者唯一索引有衝突,就會執行on duplicate key update後面的賦值語句。 這裏通過 news 表舉例,表結構和説數據展示,其中 news_code 字段有唯一索引:
添加sql:
sql
-- 第一次執行添加語句
INSERT INTO `news` (`news_title`, `news_auth`, `news_code`)
VALUES ('新聞3', '小花', 'wx-0003')
on duplicate key update news_title = '新聞3';
-- 第二次執行修改語句
INSERT INTO `news` (`news_title`, `news_auth`, `news_code`)
VALUES ('新聞4', '小花', 'wx-0003')
on duplicate key update news_title = '新聞4';
結果如下:
總結
到這裏,本文所分享的10個高級sql寫法就全部介紹完了,希望對大家日常開發 sql 編寫有所幫助,喜歡的朋友們可以點贊加關注😘。
- 分享10個高級sql寫法
- 2018至2023我的開源項目分享
- Mybatis返回集合類型到底是空集合還是null?源碼解讀
- Android實現RecyclerView嵌套流式佈局
- 十二條後端開發經驗分享,純乾貨
- ReactNative0.70版本發佈,默認Hermes引擎
- [源碼分析]Android View繪製流程--從同步屏障説起
- [源碼分析]為什麼Dialog不能使用Application作為Context進行初始化
- Android 過渡動畫啟動過程總結 -- 基於S版本
- wayne編譯支持k8s1.16
- Android 遊戲開發工具包熱門問題解答
- Android S 輸入法相關新特性整理
- Unity3D 發佈webgl卡 IL2CPP 的解決方案
- rsync
- 抓取網頁關鍵信息,填充word模板生成目標文檔
- oracle表空間不足相關問題解決辦法
- 打工人,你的假期餘額為0!
- ReactNative自定義車牌號輸入框及鍵盤實現
- ReactNative自定義車牌號輸入框及鍵盤實現
- 瘋狂吐槽postgresql,純灌水無意義