目錄

• 1.基本查詢回顧
• 2.多表查詢 (重要)
• 3.自連接
• 4.子查詢
• 1)單行子查詢 (子查詢的結(jié)果是單行)
• (2)多行子查詢
• (3)多列子查詢
• 5.合并查詢
• 總結(jié)：

1.基本查詢回顧

準(zhǔn)備工作,創(chuàng)建一個(gè)雇員信息表:（來(lái)自oracle 9i的經(jīng)典測(cè)試表）

EMP員工表 DEPT部門表 SALGRADE工資等級(jí)表

案例1:查詢工資高于500或崗位為MANAGER的雇員,同時(shí)還要滿足雇員的姓名首字母為大寫的J

第一步:查詢工資高于500或者崗位為MANAGER的雇員

第二步:在上面篩選之后的條件下:還要滿足姓名首字母為大寫的J的雇員 ,此時(shí)需要利用到substring截取字符,判斷第一個(gè)字符是否是j

substring(ename,1,1) :從第1個(gè)字符開始往后截取,截取1個(gè)字符, 得到的就是姓名的首字母, (因?yàn)槟J(rèn)從1開始

案例2:按照部門號(hào)升序而雇員的工資降序排序

默認(rèn)的order by 排序就是升序的(asc), 如果想要降序：desc

先按部門號(hào)排序, 部門號(hào)相同的按照工資降序排序

案例3:使用年薪進(jìn)行降序排序

第一步:先算出每個(gè)人的年薪, 年薪=工資*12 + 獎(jiǎng)金, 但是我們可以發(fā)現(xiàn),有的人是沒有獎(jiǎng)金的,其獎(jiǎng)金為NULL

所以這里我們可以使用ifnull函數(shù)

• IFNULL() 函數(shù)用于判斷第一個(gè)表達(dá)式是否為 NULL,如果為 NULL 則返回第二個(gè)參數(shù)的值,如果不為 NULL 則返回第一個(gè)參數(shù)的值
• ifnull(獎(jiǎng)金,0) :如果獎(jiǎng)金選項(xiàng)不是空,就返回0, 否則返回獎(jiǎng)金

第二步:按照年薪降序排序 , 因?yàn)榇藭r(shí)是已經(jīng)拿到了數(shù)據(jù)之后才能進(jìn)行排序, 所以排序的地方可以使用別名

案例4:顯示工資最高的員工的名字和工作崗位

寫法1:先拿到公司最高工資, 可能多個(gè)人的工資都是最高工資, 然后按照這個(gè)最高工資進(jìn)行篩選人

寫法2:可以直接使用子查詢, select里面套select, 先執(zhí)行后面的子查詢,它的執(zhí)行結(jié)果作為下一個(gè)select的查詢條件

案例5:顯示工資高于平均工資的員工信息

方法1:先拿到平均工資,然后按照這個(gè)平均工資進(jìn)行篩選人

方法2:使用子查詢

案例6:顯示每個(gè)部門的平均工資和最高工資

做法:首先需要對(duì)每個(gè)部門做分組,然后求出每個(gè)部門的平均工資和最高工資

先從員工表emp當(dāng)中拿到數(shù)據(jù),然后按照部門編號(hào)deptno做分組, 然后針對(duì)每一組聚合求平均工資和最高工資

當(dāng)然了,如果我們想平均工資只顯示后面的2位小數(shù): 可以使用format聚合函數(shù)控制格式: 四舍五入

案例7:顯示平均工資低于2000的部門號(hào)和它的平均工資

含義就是:先把平均工資低于2000的部門,然后求出這個(gè)部門的平均工資

做法:先分組,再聚合求出每一組的平均工資, 然后再按條件:,注意:這里不能使用where,可以使用having

• 不能使用where的原因:按照平均工資進(jìn)行篩選的前提是:我們已經(jīng)把每一組的平均工資算出來(lái)了,也就是我們的聚合操作已經(jīng)完成了, 數(shù)據(jù)已經(jīng)被提取出來(lái)了, 而where是在篩選數(shù)據(jù)的階段幫我們對(duì)數(shù)據(jù)進(jìn)行篩選的,是在分組前進(jìn)行的, 我們這里已經(jīng)把數(shù)據(jù)篩選出來(lái)做了分組了
• 執(zhí)行順序：from -> where -> group by ->having -> select -> distinct -> order by -> limit

關(guān)于where, group by having

**where：**數(shù)據(jù)庫(kù)中常用的是where關(guān)鍵字,用于在初始表中篩選查詢

**group by:**對(duì)select查詢出來(lái)的結(jié)果集按照某個(gè)字段或者表達(dá)式進(jìn)行分組,獲得一組組的集合

**having：**用于對(duì)where和group by查詢出來(lái)的分組進(jìn)行過濾,查出滿足條件的分組結(jié)果

案例8:顯示每種崗位的雇員總數(shù),平均工資

做法:先按照崗位進(jìn)行分組,然后對(duì)每一組數(shù)據(jù)進(jìn)行分組聚合

2.多表查詢 (重要)

實(shí)際開發(fā)中往往數(shù)據(jù)來(lái)自不同的表,所以需要多表查詢

例子:emp表和dept表進(jìn)行聯(lián)合查詢:

什么叫笛卡爾積:

簡(jiǎn)單來(lái)說(shuō)：就是排列組合, 把兩張表的記錄放在一起進(jìn)行排列組合的所有情況, 全排列！一般而言,我們所進(jìn)行的后續(xù)多表查詢,都應(yīng)該是笛卡爾積形成的新表的子集

• 笛卡爾積的列數(shù)就是兩個(gè)表的列數(shù)之和,行數(shù)則是兩個(gè)表的行數(shù)之積,我們?cè)谶M(jìn)行多表查詢的時(shí)候（計(jì)算笛卡爾積的過程）,如果兩個(gè)表數(shù)據(jù)很大,就會(huì)非常低效

如果是三個(gè)表的話,那么就是先將兩個(gè)表進(jìn)行笛卡爾積運(yùn)算,再用這個(gè)表與另外一個(gè)表進(jìn)行笛卡爾積操作

因?yàn)楫吘沟芽柗e只是簡(jiǎn)單的將他們進(jìn)行排列組合（并沒有進(jìn)行篩選有效信息,我們將有效信息這一篩選的過程稱為：連接條件 ,通常是存在 主外鍵約束 條件的多表建立的, 連接條件中兩個(gè)字段通過 = 建立等值關(guān)系, 例如上面的例子當(dāng)中, 連接條件就是: emp.deptno = dept.deptno

需要注意的是:笛卡爾積之后的新表,如果有相同的列名,就要通過表名.列名的方式區(qū)分,如果不用則會(huì)報(bào)錯(cuò)

即:當(dāng)多表查詢有重名的列時(shí),必須在列名前加上表名【一般用別名】作為前綴

如何看待多表查詢：

我們認(rèn)為,在我心中永遠(yuǎn)只有一張表,將來(lái)所有的多表查詢都可以認(rèn)為是單表查詢, 我們認(rèn)為select查詢出來(lái)的"記錄",都可以把它看作"表"

多表查詢步驟

• 先把多表查詢轉(zhuǎn)化為單表查詢
• 篩去排列組合產(chǎn)生的無(wú)意義數(shù)據(jù)
• 然后再根據(jù)要求進(jìn)一步篩選
• 選定好需要展示的字段

案例1:顯示雇員名,雇員工資以及所在部門的名字

雇員名,工資在emp表里面有,而部門的名字只在dept表里面有,上面的數(shù)據(jù)來(lái)自EMP和DEPT表,因此要進(jìn)行多表查詢

我們首先需要根據(jù)emp表的外鍵deptno和主表dept的key做級(jí)聯(lián) -> 過濾非法數(shù)據(jù),

需要注意的是:如果合并之后,列名在表結(jié)構(gòu)當(dāng)中唯一存在,就可以直接使用,如果不是唯一存在,就在前面加一個(gè)列名表示使用的是原來(lái)那一張表的 表名.列名

案例2:顯示部門號(hào)為10的部門名,員工名和工資

員工名和工資在員工表里面有, 部門名只在部門表有,所以需要進(jìn)行多表查詢

做法:把兩個(gè)表進(jìn)行笛卡爾積,把數(shù)據(jù)窮舉到一起, 然后根據(jù)連接條件:員工表的部門編號(hào)=部門表的編號(hào), 把合法數(shù)據(jù)篩選出來(lái), 然后根據(jù)條件篩選數(shù)據(jù)

注意:笛卡爾積之后的表,deptno列名不唯一,所以需要指定表名訪問

案例3:顯示各個(gè)員工的姓名,工資,及工資級(jí)別

工資級(jí)別 :在工資表, 員工的姓名和工資:在員工表 所以這里是多表查詢

問：此時(shí)什么是非法的數(shù)據(jù)? 工資不在對(duì)應(yīng)的等級(jí)范圍！

做法:先根據(jù)工資判斷其是否在[losal,hisal]范圍內(nèi),如果在,說(shuō)明就是合法數(shù)據(jù),否則是非法數(shù)據(jù),

因?yàn)榇颂巗al losal hisal都是笛卡爾積之后的新表當(dāng)中唯一的列名,所以不需要帶表名區(qū)分

我們可以發(fā)現(xiàn):上面多表查詢做題的精髓是: 先確定要的數(shù)據(jù)在哪些表,然后把這些表進(jìn)行笛卡爾積,整合在一起, 多表就變成了一張表, 然后再根據(jù)連接條件對(duì)數(shù)據(jù)做清洗,過濾掉非法的數(shù)據(jù), 然后再按條件進(jìn)行篩選

3.自連接

自連接是指在同一張表連接查詢,一張表可以和別人笛卡爾積,當(dāng)然也可以和自己笛卡爾,自連接時(shí)要對(duì)表名進(jìn)行重命名,否則會(huì)出現(xiàn)名字沖突的問題.

因?yàn)楸砻植荒芟嗤?所以我們需要對(duì)表名取別名

案例1:顯示員工FORD的上級(jí)領(lǐng)導(dǎo)的編號(hào)和姓名

做法1:單表查詢: 先找到這個(gè)員工FORD的領(lǐng)導(dǎo)的編號(hào),然后根據(jù)編號(hào)找到這個(gè)領(lǐng)導(dǎo)是誰(shuí)

做法2:改成子查詢 :先找到員工FORD的領(lǐng)導(dǎo)編號(hào),然后用這個(gè)查出來(lái)的員工號(hào),在員工表里面找這個(gè)編號(hào)

也是單表查詢

做法3:多表查詢,自連接

因?yàn)榈芽柗e之后的表太大了,建立使用limit查詢笛卡爾積之后的結(jié)果！, 這里因?yàn)樽赃B接是兩個(gè)同名字的表進(jìn)行笛卡爾積,因?yàn)楸砻植荒芟嗤?區(qū)分不開,需要取別名 把其中一張表起名為員工表,另一種為領(lǐng)導(dǎo)表

這里的連接條件是什么? 即:以什么條件過濾非法數(shù)據(jù) 員工表中自己的領(lǐng)導(dǎo)編號(hào) = 領(lǐng)導(dǎo)表中自己的員工編號(hào)！

領(lǐng)導(dǎo)也是員工！ (打工人) 這里的mgr就是員工對(duì)應(yīng)的領(lǐng)導(dǎo)的編號(hào), empno就是員工自己的編號(hào)

然后再根據(jù)條件篩選數(shù)據(jù): 員工名字為’FORD’

我們只要我們需要的數(shù)據(jù):

4.子查詢

子查詢是指嵌入在其他sql語(yǔ)句中的select語(yǔ)句,也叫嵌套查詢

1)單行子查詢 (子查詢的結(jié)果是單行)

單行子查詢是指子查詢只返回單列,單行數(shù)據(jù)

案例1:顯示SMITH同一部門的員工

做法1：先拿到SMITH的部門號(hào),然后再在EMP表里面篩選在SMITH所屬部門的員工

做法2:直接寫成子查詢:

案例2:顯示工資最高的員工的名字和工作崗位

做法:最高工資的可能有一個(gè)或者多個(gè), 先找出emp表中最高的工資,然后在查找時(shí),找工資為最高工資的員工

案例3:顯示工資高于平均工資的員工信息

做法:先求出emp表中的平均工資,然后在查找時(shí)找工資高于平均工資的員工

(2)多行子查詢

多行子查詢是指子查詢的結(jié)果返回單列多行數(shù)據(jù).

in關(guān)鍵字 ：只要在多行單列的結(jié)果中,則條件滿足.

案例:查詢和10號(hào)部門的工作崗位相同的雇員的名字,崗位,工資,部門號(hào),但是不包含10號(hào)部門自己的員工

第一步：先拿到10號(hào)部門的崗位,如果有重復(fù)的話,還可以去重

第二步:使用in關(guān)鍵字,在員工表當(dāng)中找到在上面的這些崗位的人的信息

第三步：再根據(jù)條件篩選:不包含10號(hào)部門自己的員工

all關(guān)鍵字 :需要滿足多行單列結(jié)果當(dāng)中的所有,條件才滿足

案例：顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號(hào)

第一步:先把30號(hào)部門的員工的工資列出來(lái),可能存在相同的,要進(jìn)行去重

第二步:根據(jù)條件在員工表篩選: 比部門30的所有員工的工資高的員工

錯(cuò)誤寫法:

原因：后面的select子查詢得到的是多條的記錄

正確寫法:使用all ,因?yàn)檫x出的是比30號(hào)部門所有人工資都要高的員工,所以最后的結(jié)果肯定沒有30號(hào)部門的人

寫法2:題目的本質(zhì)其實(shí)就是找到工資>30號(hào)部門的最高工資的員工

其實(shí)可以直接使用>,是因?yàn)楹竺孀硬樵兊玫降闹挥幸粭l記錄

any 關(guān)鍵字 :只要滿足多行單列結(jié)果當(dāng)中的任一一個(gè),則條件滿足

案例:顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號(hào)（包含自己部門的員工）

第一步:先拿出30號(hào)部門的員工的工資,可能存在相同的,要進(jìn)行去重

select distinct sal from emp where deptno=30

第二步：找出比30號(hào)部門任意一個(gè)員工工資都要高的人, 此時(shí)需要使用any關(guān)鍵字

如果此時(shí)還要加上一個(gè)條件:要在20號(hào)部門當(dāng)中選出呢?

寫法2:題目的本質(zhì)其實(shí)就是找到工資>30號(hào)部門的最低工資的員工

所以30號(hào)部門的人也會(huì)被顯示上

in:我是否屬于你們的一員 all:我比你們都怎么樣 any：我比你們?nèi)我庖粋€(gè)人怎么樣

(3)多列子查詢

多行子查詢是指返回單列多行數(shù)據(jù),都是針對(duì)單列而言的,而多列子查詢則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語(yǔ)句

案例:查詢和SMITH這個(gè)員工的部門和崗位完全相同的所有雇員,不含SMITH本人

第一步:先拿到SMITH的部門和崗位,

我們需要同時(shí)找到deptno和job兩列數(shù)據(jù),上面的多行子查詢都只是包含一列數(shù)據(jù), 此時(shí)得到的是單行多列的數(shù)據(jù)

第二步:進(jìn)行篩選:,前面的得到的就是和SMITH在同一個(gè)部門同一個(gè)崗位的人, 然后用and條件再把SMITH篩選走

可以認(rèn)為,()就是表示MySQL內(nèi)部實(shí)現(xiàn)的集合

在from子句中使用子查詢

子查詢語(yǔ)句出現(xiàn)在from子句中,這里要用到數(shù)據(jù)查詢的技巧,把一個(gè)子查詢當(dāng)做一個(gè)臨時(shí)表使用

案例1:顯示每個(gè)高于自己部門平均工資的員工的姓名、部門、工資、平均工資

做法:要拿自己?jiǎn)T工表的工資和平均工資作比較, 首先需要分組求出每個(gè)部門的平均工資

可以把上面查到的內(nèi)容當(dāng)成一張表,它里面放著就是部門和它的平均工資,然后把這張表和員工表做笛卡爾積

然后再過濾出非法的數(shù)據(jù), 必須保證:員工的部門編號(hào)=平均工資表的部門編號(hào)才有意義, 子查詢是先被執(zhí)行的,先有的avg_tb表,然后才進(jìn)行非法數(shù)據(jù)過濾,所以可以用別名

因?yàn)榈芽柗e窮舉的時(shí)候,有多信息是無(wú)效的,需要進(jìn)行過濾 部門號(hào)匹配的才是有效數(shù)據(jù)

然后再根據(jù)條件篩選:員工的工資要比它所在部門的工資高 就是拿員工表的工資和平均工資表的平均工資比較,篩選出工資要高于自己部門平均工資的員工

我們只想要某些信息：

在上面的基礎(chǔ)上.如果我們想把部門也顯示出來(lái)呢?

把上面的表和部門表dept做笛卡爾積！然后再根據(jù)部門號(hào)要相等進(jìn)行非法數(shù)據(jù)過濾

案例2:查找每個(gè)部門工資最高的人的姓名、工資、部門、最高工資

先根據(jù)部門號(hào)分組,求出每個(gè)部門的最高工資,然后形成的這張表和員工表進(jìn)行笛卡爾積, 根據(jù) 員工表的部門編號(hào)=最高工資表的部門編號(hào)進(jìn)行過濾非法數(shù)據(jù), 然后找到每個(gè)部門工資最高的人,可能有1個(gè)或者多個(gè) (只要員工的工資=部門表的最高工資,該員工就是它部門的最高工資的人)

案例3:顯示每個(gè)部門的信息（部門名,編號(hào),地址）和人員數(shù)量

第一步:先根據(jù)部門分組,統(tǒng)計(jì)每個(gè)部門的人數(shù)->需要使用count函數(shù),然后得到的內(nèi)容作為新表 和部門表做笛卡爾積, 根據(jù): 新表的部門編號(hào)=部門表的部門編號(hào)進(jìn)行過濾非法數(shù)據(jù), 然后需要什么信息就顯示什么信息

做法2：直接把員工表和部門表做笛卡爾積, 然后根據(jù)部門編號(hào)要相同過濾非法數(shù)據(jù), 然后按照部門進(jìn)行分組,需要什么就顯示什么

5.合并查詢

在實(shí)際應(yīng)用中,為了合并多個(gè)select的執(zhí)行結(jié)果,可以使用集合操作符 union,union all

1)union 該操作符用于取得兩個(gè)結(jié)果集的并集,當(dāng)使用該操作符時(shí),會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行,

案例：將工資大于2500或者職位是MANAGER的人找出來(lái)

工資和職位的信息都早員工表里面有,所以就是單表查詢

寫法1:直接根據(jù)條件在員工表進(jìn)行篩選

寫法2:求兩個(gè)表的并集

2)union all 該操作符用于取得兩個(gè)結(jié)果集的并集,當(dāng)使用該操作符時(shí),不會(huì)去掉結(jié)果集中的重復(fù)行,

案例: 將工資大于2500或者職位是MANAGER的人找出來(lái)

信息列必須一樣,否則會(huì)出問題

關(guān)鍵字解釋union取并集,將多個(gè) select 結(jié)果合并到一起,自動(dòng)去掉重復(fù)行union all取并集,將多個(gè) select 結(jié)果合并到一起,但不去重

總結(jié)：

• 子查詢可以出現(xiàn)在兩個(gè)地方(常規(guī),重要)
• 1. where字句中,作為篩選條件使用
• 2. from字句中,用來(lái)和特定的表做笛卡爾積

到此這篇關(guān)于MySQL數(shù)據(jù)庫(kù)復(fù)合查詢操作實(shí)戰(zhàn)的文章就介紹到這了,更多相關(guān)MySQL復(fù)合查詢內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！