Mysql8公用表表達(dá)式CTE詳解
一、概述
- 公用表表達(dá)式(或通用表表達(dá)式)簡稱為CTE(Common Table Expressions)。
- CTE是一個(gè)命名的臨時(shí)結(jié)果集,作用范圍是當(dāng)前語句。
- CTE可以理解成一個(gè)可以復(fù)用的子查詢,當(dāng)然跟子查詢還是有點(diǎn)區(qū)別的,CTE可以引用其他CTE,但子查詢不能引用其他子查詢。所以,可以考慮代替子查詢。
- 依據(jù)語法結(jié)構(gòu)和執(zhí)行方式的不同,公用表表達(dá)式分為普通公用表表達(dá)式和遞歸公用表表達(dá)式 2 種。
二、普通公用表表達(dá)式
1、語法結(jié)構(gòu)
普通公用表表達(dá)式的語法結(jié)構(gòu)是:
WITH CTE名稱 AS (子查詢) SELECT|DELETE|UPDATE 語句;
普通公用表表達(dá)式類似于子查詢,不過,跟子查詢不同的是,它可以被多次引用,而且可以被其他的普通公用表表達(dá)式所引用。
2、案例
舉例:查詢員工所在的部門的詳細(xì)信息。
mysql> SELECT * FROM departments -> WHERE department_id IN ( -> SELECT DISTINCT department_id -> FROM employees -> ); +---------------+------------------+------------+-------------+ | department_id | department_name | manager_id | location_id | +---------------+------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | | 110 | Accounting | 205 | 1700 | +---------------+------------------+------------+-------------+ 11 rows in set (0.00 sec)
這個(gè)查詢也可以用普通公用表表達(dá)式的方式完成:
mysql> WITH emp_dept_id -> AS (SELECT DISTINCT department_id FROM employees) -> SELECT * -> FROM departments d JOIN emp_dept_id e -> ON d.department_id = e.department_id; +---------------+------------------+------------+-------------+---------------+ | department_id | department_name | manager_id | location_id | department_id | +---------------+------------------+------------+-------------+---------------+ | 90 | Executive | 100 | 1700 | 90 | | 60 | IT | 103 | 1400 | 60 | | 100 | Finance | 108 | 1700 | 100 | | 30 | Purchasing | 114 | 1700 | 30 | | 50 | Shipping | 121 | 1500 | 50 | | 80 | Sales | 145 | 2500 | 80 | | 10 | Administration | 200 | 1700 | 10 | | 20 | Marketing | 201 | 1800 | 20 | | 40 | Human Resources | 203 | 2400 | 40 | | 70 | Public Relations | 204 | 2700 | 70 | | 110 | Accounting | 205 | 1700 | 110 | +---------------+------------------+------------+-------------+---------------+ 11 rows in set (0.00 sec)
- 例子說明,公用表表達(dá)式可以起到子查詢的作用。
- 以后如果遇到需要使用子查詢的場景,你可以在查詢之前,先定義公用表表達(dá)式,然后在查詢中用它來代替子查詢。
- 而且,跟子查詢相比,公用表表達(dá)式有一個(gè)優(yōu)點(diǎn),就是定義過公用表表達(dá)式之后的查詢,可以像一個(gè)表一樣多次引用公用表表達(dá)式,而子查詢則不能。
三、遞歸公用表表達(dá)式
1、語法結(jié)構(gòu)
遞歸公用表表達(dá)式也是一種公用表表達(dá)式,只不過,除了普通公用表表達(dá)式的特點(diǎn)以外,它還有自己的特點(diǎn),就是可以調(diào)用自己。
它的語法結(jié)構(gòu)是:
WITH RECURSIVE CTE名稱 AS (子查詢) SELECT|DELETE|UPDATE 語句;
- 遞歸公用表表達(dá)式由 2 部分組成,分別是種子查詢和遞歸查詢,中間通過關(guān)鍵字 UNION [ALL]進(jìn)行連接。
- 這里的種子查詢,意思就是獲得遞歸的初始值。這個(gè)查詢只會(huì)運(yùn)行一次,以創(chuàng)建初始數(shù)據(jù)集,之后遞歸查詢會(huì)一直執(zhí)行,直到?jīng)]有任何新的查詢數(shù)據(jù)產(chǎn)生,遞歸返回。
2、案例
案例:針對(duì)于我們常用的employees表,包含employee_id,last_name和manager_id三個(gè)字段。如果a是b的管理者,那么,我們可以把b叫做a的下屬,如果同時(shí)b又是c的管理者,那么c就是b的下屬,是a的下下屬。 下面我們嘗試用查詢語句列出所有具有下下屬身份的人員信息。
如果用我們之前學(xué)過的知識(shí)來解決,會(huì)比較復(fù)雜,至少要進(jìn)行 4 次查詢才能搞定:
- 第一步,先找出初代管理者,就是不以任何別人為管理者的人,把結(jié)果存入臨時(shí)表;
- 第二步,找出所有以初代管理者為管理者的人,得到一個(gè)下屬集,把結(jié)果存入臨時(shí)表;
- 第三步,找出所有以下屬為管理者的人,得到一個(gè)下下屬集,把結(jié)果存入臨時(shí)表。
- 第四步,找出所有以下下屬為管理者的人,得到一個(gè)結(jié)果集。
如果第四步的結(jié)果集為空,則計(jì)算結(jié)束,第三步的結(jié)果集就是我們需要的下下屬集了,否則就必須繼續(xù)進(jìn)行第四步,一直到結(jié)果集為空為止。比如上面的這個(gè)數(shù)據(jù)表,就需要到第五步,才能得到空結(jié)果集。而且,最后還要進(jìn)行第六步:把第三步和第四步的結(jié)果集合并,這樣才能最終獲得我們需要的結(jié)果集。
如果用遞歸公用表表達(dá)式,就非常簡單了。我介紹下具體的思路。
- 用遞歸公用表表達(dá)式中的種子查詢,找出初代管理者。字段 n 表示代次,初始值為 1,表示是第一代管理者。
- 用遞歸公用表表達(dá)式中的遞歸查詢,查出以這個(gè)遞歸公用表表達(dá)式中的人為管理者的人,并且代次的值加 1。直到?jīng)]有人以這個(gè)遞歸公用表表達(dá)式中的人為管理者了,遞歸返回。
- 在最后的查詢中,選出所有代次大于等于 3 的人,他們肯定是第三代及以上代次的下屬了,也就是下下屬了。這樣就得到了我們需要的結(jié)果集。
這里看似也是 3 步,實(shí)際上是一個(gè)查詢的 3 個(gè)部分,只需要執(zhí)行一次就可以了。而且也不需要用臨時(shí)表保存中間結(jié)果,比剛剛的方法簡單多了。
代碼實(shí)現(xiàn):
WITH RECURSIVE cte AS ( SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100 -- 種子查詢,找到第一代領(lǐng)導(dǎo) UNION ALL SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte ON (a.manager_id = cte.employee_id) -- 遞歸查詢,找出以遞歸公用表表達(dá)式的人為領(lǐng)導(dǎo)的人 ) SELECT employee_id,last_name FROM cte WHERE n >= 3;
總之,遞歸公用表表達(dá)式對(duì)于查詢一個(gè)有共同的根節(jié)點(diǎn)的樹形結(jié)構(gòu)數(shù)據(jù),非常有用。它可以不受層級(jí)的限制,輕松查出所有節(jié)點(diǎn)的數(shù)據(jù)。如果用其他的查詢方式,就比較復(fù)雜了。
四、注意事項(xiàng)
公用表表達(dá)式的作用是可以替代子查詢,而且可以被多次引用。遞歸公用表表達(dá)式對(duì)查詢有一個(gè)共同根節(jié)點(diǎn)的樹形結(jié)構(gòu)數(shù)據(jù)非常高效,可以輕松搞定其他查詢方式難以處理的查詢。但在使用時(shí)需要時(shí)刻注意以下事項(xiàng):
- 版本要求: 在開始使用公用表表達(dá)式之前,確保您使用的是 MySQL 8 或更高版本。公用表表達(dá)式是 MySQL 8 中引入的新功能,因此在舊版本的 MySQL 中無法使用。如果您的數(shù)據(jù)庫還在舊版本上運(yùn)行,請考慮升級(jí)到 MySQL 8。
- 語法規(guī)則: 公用表表達(dá)式的語法類似于子查詢,但使用 WITH 關(guān)鍵字進(jìn)行定義。了解并熟悉公用表表達(dá)式的語法結(jié)構(gòu),包括 WITH 子句、CTE 名稱、列名和實(shí)際查詢部分。確保您正確地編寫公用表表達(dá)式,并在查詢中使用它們。
- CTE 的作用范圍: 公用表表達(dá)式的作用范圍僅限于定義它們的查詢。這意味著在同一查詢中,您可以在多個(gè)地方引用相同的 CTE,但在其他查詢中無法使用相同的 CTE。在編寫查詢時(shí),請確保將 CTE 的使用限制在合適的范圍內(nèi),以避免引起錯(cuò)誤。
- 遞歸查詢的使用: 公用表表達(dá)式還可以用于執(zhí)行遞歸查詢,即查詢中的表達(dá)式引用自身的情況。遞歸查詢在處理層次結(jié)構(gòu)的數(shù)據(jù)時(shí)非常有用,例如組織結(jié)構(gòu)或評(píng)論回復(fù)。然而,遞歸查詢需要謹(jǐn)慎使用,因?yàn)殄e(cuò)誤的查詢可能導(dǎo)致無限循環(huán)。確保您了解遞歸查詢的工作原理,并遵循最佳實(shí)踐,例如設(shè)置遞歸終止條件和使用適當(dāng)?shù)乃饕?/li>
- 性能優(yōu)化: 雖然公用表表達(dá)式提供了更簡潔的查詢語法,但在處理大型數(shù)據(jù)集時(shí),性能可能成為一個(gè)關(guān)鍵問題。CTE 的執(zhí)行可能涉及到臨時(shí)表的創(chuàng)建和數(shù)據(jù)復(fù)制,這可能導(dǎo)致額外的開銷。在編寫復(fù)雜的查詢時(shí),請?jiān)u估性能影響并進(jìn)行必要的優(yōu)化,例如適當(dāng)?shù)乃饕筒樵冎貙憽J褂?EXPLAIN 語句來分析查詢計(jì)劃,并確保查詢的執(zhí)行效率得到優(yōu)化。
- 數(shù)據(jù)庫兼容性: 請注意,公用表表達(dá)式是 MySQL 8 特有的功能,并不一定在其他數(shù)據(jù)庫管理系統(tǒng)(DBMS)中可用。如果您計(jì)劃將查詢遷移到其他 DBMS,您可能需要調(diào)整查詢語句以適應(yīng)該系統(tǒng)的特定語法和功能。在跨數(shù)據(jù)庫平臺(tái)的項(xiàng)目中,請確保了解目標(biāo) DBMS 支持的功能和語法。
總結(jié):
MySQL 8 的公用表表達(dá)式(CTE)是一個(gè)強(qiáng)大的功能,可以提高查詢的可讀性和編寫復(fù)雜查詢的靈活性。
在使用公用表表達(dá)式時(shí),請注意版本要求、語法規(guī)則、作用范圍、遞歸查詢的使用、性能優(yōu)化以及數(shù)據(jù)庫兼容性等重要事項(xiàng)。
通過充分了解這些注意事項(xiàng),您將能夠更好地應(yīng)用 MySQL 8 的公用表表達(dá)式功能,并獲得更好的查詢性能和結(jié)果。
到此這篇關(guān)于Mysql8公用表表達(dá)式CTE詳解的文章就介紹到這了,更多相關(guān)Mysql8公用表表達(dá)式內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
SQL中l(wèi)imit函數(shù)語法與用法(MYSQL獲取限制某行數(shù)據(jù))
limit是MySql的內(nèi)置函數(shù),一般用于查詢表中記錄的條數(shù),作用是用于限制查詢條數(shù),下面這篇文章主要給大家介紹了關(guān)于SQL中l(wèi)imit函數(shù)語法與用法的相關(guān)資料,詳細(xì)講了MYSQL獲取限制某行數(shù)據(jù)的方法,需要的朋友可以參考下2022-08-08Mysql中常用函數(shù)之分組,連接查詢功能實(shí)現(xiàn)
在MySQL中,函數(shù)可以進(jìn)行各種數(shù)據(jù)操作,如字符處理、數(shù)學(xué)計(jì)算和日期格式化等,單行函數(shù)處理單條數(shù)據(jù)記錄,而分組函數(shù)則處理多條數(shù)據(jù)記錄,本文給大家介紹Mysql中常用函數(shù)之分組,連接查詢功能實(shí)現(xiàn),感興趣的朋友一起看看吧2024-10-10mysql實(shí)現(xiàn)合并同一ID對(duì)應(yīng)多條數(shù)據(jù)的方法
這篇文章主要介紹了mysql實(shí)現(xiàn)合并同一ID對(duì)應(yīng)多條數(shù)據(jù)的方法,涉及mysql GROUP_CONCAT函數(shù)的使用技巧,具有一定參考借鑒價(jià)值,需要的朋友可以參考下2016-06-06