MySQL?with語句講解
更新時(shí)間：2022年11月12日 10:16:43 作者：只是甲
這篇文章主要介紹了MySQL?with語句小結(jié),對于邏輯復(fù)雜的sql，with可以大大減少臨時(shí)表的數(shù)量，提升代碼的可讀性、可維護(hù)性，對mysql?with語句相關(guān)知識感興趣的朋友一起看看吧
備注:測試數(shù)據(jù)庫版本為MySQL 8.0
今天我們來聊聊MySQL的with語句
對于邏輯復(fù)雜的sql，with可以大大減少臨時(shí)表的數(shù)量，提升代碼的可讀性、可維護(hù)性
MySQL 8.0終于開始支持with語句了，對于復(fù)雜查詢，可以不用寫那么多的臨時(shí)表了。
如需要scott用戶下建表及錄入數(shù)據(jù)語句，可參考:
scott建表及錄入數(shù)據(jù)sql腳本
語句結(jié)構(gòu):
with subquery_name1 as (subquery_body1),
subquery_name2 as (subquery_body2)
...
select * from subquery_name1 a, subquery_name2 b
where a.col = b.col
...優(yōu)勢
- – 代碼模塊化
- – 代碼可讀性增強(qiáng)
- – 相同查詢唯一化
一.提升代碼的可讀性和可維護(hù)性
需求:求每個(gè)部門的平均工資,以及剔除薪資低于1000的實(shí)習(xí)人員之后的平均工資
-- 求每個(gè)部門的平均工資,以及剔除薪資低于1000的實(shí)習(xí)人員之后的平均工資
-- 主查詢的from后面跟了2個(gè)臨時(shí)表，程序可讀性不佳
select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
from dept d
left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
from emp e1
group by e1.deptno) tmp1
on d.deptno = tmp1.deptno
left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
from emp e1
where e1.sal > 1000
group by e1.deptno) tmp2
on d.deptno = tmp2.deptno;
-- 求每個(gè)部門的平均工資,以及剔除薪資低于1000的實(shí)習(xí)人員之后的平均工資
-- 2個(gè)臨時(shí)表的定時(shí)語句通過with封裝成子查詢了，程序可讀性增強(qiáng)
with tmp1 as
(select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
from emp e1
group by e1.deptno),
tmp2 as
(select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
from emp e1
where e1.sal > 1000
group by e1.deptno)
select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
from dept d
left join tmp1
on d.deptno = tmp1.deptno
left join tmp2
on d.deptno = tmp2.deptno;mysql> -- 求每個(gè)部門的平均工資,以及剔除薪資低于1000的實(shí)習(xí)人員之后的平均工資
mysql> -- 主查詢的from后面跟了2個(gè)臨時(shí)表，程序可讀性不佳
mysql> select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
-> from dept d
-> left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
-> from emp e1
-> group by e1.deptno) tmp1
-> on d.deptno = tmp1.deptno
-> left join (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
-> from emp e1
-> where e1.sal > 1000
-> group by e1.deptno) tmp2
-> on d.deptno = tmp2.deptno;
+--------+----------+----------+
| deptno | avg_sal1 | avg_sal2 |
+--------+----------+----------+
| 10 | 2916.67 | 2916.67 |
| 20 | 2175.00 | 2518.75 |
| 30 | 1566.67 | 1690.00 |
| 40 | NULL | NULL |
+--------+----------+----------+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> -- 求每個(gè)部門的平均工資,以及剔除薪資低于1000的實(shí)習(xí)人員之后的平均工資
mysql> -- 2個(gè)臨時(shí)表的定時(shí)語句通過with封裝成子查詢了，程序可讀性增強(qiáng)
mysql> with tmp1 as
-> (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
-> from emp e1
-> group by e1.deptno),
-> tmp2 as
-> (select e1.deptno, round(avg(ifnull(e1.sal, 0)), 2) avg_sal
-> from emp e1
-> where e1.sal > 1000
-> group by e1.deptno)
-> select d.deptno, tmp1.avg_sal avg_sal1, tmp2.avg_sal avg_sal2
-> from dept d
-> left join tmp1
-> on d.deptno = tmp1.deptno
-> left join tmp2
-> on d.deptno = tmp2.deptno;
+--------+----------+----------+
| deptno | avg_sal1 | avg_sal2 |
+--------+----------+----------+
| 10 | 2916.67 | 2916.67 |
| 20 | 2175.00 | 2518.75 |
| 30 | 1566.67 | 1690.00 |
| 40 | NULL | NULL |
+--------+----------+----------+
4 rows in set (0.00 sec)
mysql>
二.with遞歸
用with遞歸構(gòu)造數(shù)列
-- 用with遞歸構(gòu)造1-10的數(shù)據(jù) with RECURSIVE c(n) as (select 1 union all select n + 1 from c where n < 10) select n from c;
-- 用with遞歸構(gòu)造1-10的數(shù)據(jù)
mysql> with RECURSIVE c(n) as
-> (select 1 union all select n + 1 from c where n < 10)
-> select n from c;
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec)用with遞歸構(gòu)造級聯(lián)關(guān)系
with RECURSIVE emp2(ename,empno,mgr,lvl)
as
(select ename, empno, mgr, 1 lvl from emp where mgr is null
union all
select emp.ename, emp.empno, emp.mgr, e2.lvl+1
from emp, emp2 e2
where emp.mgr = e2.empno
)
select lvl,
concat(repeat('**',lvl),ename) nm
from emp2
order by lvl,ename
;mysql> with RECURSIVE emp2(ename,empno,mgr,lvl)
-> as
-> (select ename, empno, mgr, 1 lvl from emp where mgr is null
-> union all
-> select emp.ename, emp.empno, emp.mgr, e2.lvl+1
-> from emp, emp2 e2
-> where emp.mgr = e2.empno
-> )
-> select lvl,
-> concat(repeat('**',lvl),ename) nm
-> from emp2
-> order by lvl,ename
-> ;
+------+---------------+
| lvl | nm |
+------+---------------+
| 1 | **KING |
| 2 | ****BLAKE |
| 2 | ****CLARK |
| 2 | ****JONES |
| 3 | ******ALLEN |
| 3 | ******FORD |
| 3 | ******JAMES |
| 3 | ******MARTIN |
| 3 | ******MILLER |
| 3 | ******SCOTT |
| 3 | ******TURNER |
| 3 | ******WARD |
| 4 | ********ADAMS |
| 4 | ********SMITH |
+------+---------------+
14 rows in set (0.00 sec)到此這篇關(guān)于MySQL with語句小結(jié)的文章就介紹到這了,更多相關(guān)mysql with語句內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了Mysql 8.0安裝及重置密碼問題,非常不錯(cuò)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2018-11-11
Mysql Workbench查詢mysql數(shù)據(jù)庫方法
在本篇文章里小編給大家分享了個(gè)關(guān)于Mysql Workbench查詢mysql數(shù)據(jù)庫方法和步驟，有需要的朋友們學(xué)習(xí)下。2019-03-03
這篇文章主要介紹了MySQL查詢性能優(yōu)化索引下推，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價(jià)值，需要的小伙伴可以參考一下，希望對你的學(xué)習(xí)有所幫助2022-08-08
這篇文章主要介紹的使MySQL空間數(shù)據(jù)存儲及函數(shù)，MySQL提供了數(shù)據(jù)類型geometry用來存儲坐標(biāo)信息，MySQL為空間數(shù)據(jù)存儲及處理提供了專用的類型geometry，下面就和小編一起學(xué)習(xí)下文吧2021-09-09
完美解決mysql in條件語句只讀取一條信息問題的2種方案
使用mysql多表查詢時(shí)一個(gè)表中的某個(gè)字段作為另一表的in查詢條件，只能讀取一條信息，而直接用數(shù)字的話可以正常讀取2018-04-04
MySQL使用觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)自動更新的應(yīng)用實(shí)例
觸發(fā)器是非常常見的自動化數(shù)據(jù)庫操作方式,無論是在數(shù)據(jù)更新、刪除還是需要自動添加一些內(nèi)容到數(shù)據(jù)表上,觸發(fā)器都可以發(fā)揮作用,熟悉 SQL 的基本語法和一些常見的用例,可以幫助你合理地設(shè)置自己的數(shù)據(jù)庫操作流程,2024-01-01
在網(wǎng)上也搜看了一下再mysql query browser中解決中文亂碼的問題，答案各不相同2010-09-09
這篇文章主要介紹了MyISAM和InnoDB存儲引擎的區(qū)別,存儲引擎是MySQL中特有的一個(gè)術(shù)語，其它數(shù)據(jù)庫中沒有,實(shí)際上存儲引擎是一個(gè)表存儲/組織數(shù)據(jù)的方式,今天就跟小編來看看MyISAM和InnoDB存儲引擎的區(qū)別,需要的朋友可以參考下2023-04-04
這篇文章主要介紹了mysql 時(shí)間戳的用法，文中講解非常細(xì)致，代碼幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-08-08
在使用寶塔linux面板一鍵安裝LNMP的是,數(shù)據(jù)庫mysql始終無法啟動,查查找找用了幾個(gè)小時(shí),問題終于解決了,下面這篇文章主要給大家介紹了關(guān)于寶塔面板mysql無法啟動問題的分析和解決方法,需要的朋友可以參考下2023-04-04