目錄

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

1.基本查詢回顧

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

EMP員工表 DEPT部門表 SALGRADE工資等級表

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

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

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

substring(ename,1,1) :從第1個字符開始往后截取,截取1個字符, 得到的就是姓名的首字母, (因為默認從1開始

案例2:按照部門號升序而雇員的工資降序排序

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

先按部門號排序, 部門號相同的按照工資降序排序

案例3:使用年薪進行降序排序

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

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

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

第二步:按照年薪降序排序 , 因為此時是已經(jīng)拿到了數(shù)據(jù)之后才能進行排序, 所以排序的地方可以使用別名

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

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

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

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

方法1:先拿到平均工資,然后按照這個平均工資進行篩選人

方法2:使用子查詢

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

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

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

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

案例7:顯示平均工資低于2000的部門號和它的平均工資

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

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

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

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

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

**group by:**對select查詢出來的結(jié)果集按照某個字段或者表達式進行分組,獲得一組組的集合

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

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

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

2.多表查詢 (重要)

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

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

什么叫笛卡爾積:

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

• 笛卡爾積的列數(shù)就是兩個表的列數(shù)之和,行數(shù)則是兩個表的行數(shù)之積,我們在進行多表查詢的時候（計算笛卡爾積的過程）,如果兩個表數(shù)據(jù)很大,就會非常低效

如果是三個表的話,那么就是先將兩個表進行笛卡爾積運算,再用這個表與另外一個表進行笛卡爾積操作

因為畢竟笛卡爾積只是簡單的將他們進行排列組合（并沒有進行篩選有效信息,我們將有效信息這一篩選的過程稱為：連接條件 ,通常是存在 主外鍵約束 條件的多表建立的, 連接條件中兩個字段通過 = 建立等值關(guān)系, 例如上面的例子當中, 連接條件就是: emp.deptno = dept.deptno

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

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

如何看待多表查詢：

我們認為,在我心中永遠只有一張表,將來所有的多表查詢都可以認為是單表查詢, 我們認為select查詢出來的"記錄",都可以把它看作"表"

多表查詢步驟

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

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

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

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

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

案例2:顯示部門號為10的部門名,員工名和工資

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

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

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

案例3:顯示各個員工的姓名,工資,及工資級別

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

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

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

因為此處sal losal hisal都是笛卡爾積之后的新表當中唯一的列名,所以不需要帶表名區(qū)分

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

3.自連接

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

因為表名字不能相同,所以我們需要對表名取別名

案例1:顯示員工FORD的上級領(lǐng)導的編號和姓名

做法1:單表查詢: 先找到這個員工FORD的領(lǐng)導的編號,然后根據(jù)編號找到這個領(lǐng)導是誰

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

也是單表查詢

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

因為笛卡爾積之后的表太大了,建立使用limit查詢笛卡爾積之后的結(jié)果！, 這里因為自連接是兩個同名字的表進行笛卡爾積,因為表名字不能相同,區(qū)分不開,需要取別名 把其中一張表起名為員工表,另一種為領(lǐng)導表

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

領(lǐng)導也是員工！ (打工人) 這里的mgr就是員工對應的領(lǐng)導的編號, empno就是員工自己的編號

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

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

4.子查詢

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

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

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

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

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

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

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

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

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

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

(2)多行子查詢

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

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

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

第一步：先拿到10號部門的崗位,如果有重復的話,還可以去重

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

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

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

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

第一步:先把30號部門的員工的工資列出來,可能存在相同的,要進行去重

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

錯誤寫法:

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

正確寫法:使用all ,因為選出的是比30號部門所有人工資都要高的員工,所以最后的結(jié)果肯定沒有30號部門的人

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

其實可以直接使用>,是因為后面子查詢得到的只有一條記錄

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

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

第一步:先拿出30號部門的員工的工資,可能存在相同的,要進行去重

select distinct sal from emp where deptno=30

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

如果此時還要加上一個條件:要在20號部門當中選出呢?

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

所以30號部門的人也會被顯示上

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

(3)多列子查詢

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

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

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

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

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

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

在from子句中使用子查詢

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

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

做法:要拿自己員工表的工資和平均工資作比較, 首先需要分組求出每個部門的平均工資

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

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

因為笛卡爾積窮舉的時候,有多信息是無效的,需要進行過濾 部門號匹配的才是有效數(shù)據(jù)

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

我們只想要某些信息：

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

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

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

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

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

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

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

5.合并查詢

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

1)union 該操作符用于取得兩個結(jié)果集的并集,當使用該操作符時,會自動去掉結(jié)果集中的重復行,

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

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

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

寫法2:求兩個表的并集

2)union all 該操作符用于取得兩個結(jié)果集的并集,當使用該操作符時,不會去掉結(jié)果集中的重復行,

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

信息列必須一樣,否則會出問題

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

總結(jié)：

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

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