目錄

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

1.基本查詢回顧

準備工作,創建一個雇員信息表:（來自oracle 9i的經典測試表）

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 + 獎金, 但是我們可以發現,有的人是沒有獎金的,其獎金為NULL

所以這里我們可以使用ifnull函數

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

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

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

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

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

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

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

方法2:使用子查詢

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

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

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

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

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

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

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

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

關于where, group by having

**where：**數據庫中常用的是where關鍵字,用于在初始表中篩選查詢

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

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

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

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

2.多表查詢 (重要)

實際開發中往往數據來自不同的表,所以需要多表查詢

例子:emp表和dept表進行聯合查詢:

什么叫笛卡爾積:

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

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

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

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

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

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

如何看待多表查詢：

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

多表查詢步驟

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

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

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

我們首先需要根據emp表的外鍵deptno和主表dept的key做級聯 -> 過濾非法數據,

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

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

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

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

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

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

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

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

做法:先根據工資判斷其是否在[losal,hisal]范圍內,如果在,說明就是合法數據,否則是非法數據,

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

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

3.自連接

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

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

案例1:顯示員工FORD的上級領導的編號和姓名

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

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

也是單表查詢

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

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

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

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

然后再根據條件篩選數據: 員工名字為’FORD’

我們只要我們需要的數據:

4.子查詢

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

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

單行子查詢是指子查詢只返回單列,單行數據

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

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

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

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

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

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

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

(2)多行子查詢

多行子查詢是指子查詢的結果返回單列多行數據.

in關鍵字 ：只要在多行單列的結果中,則條件滿足.

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

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

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

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

all關鍵字 :需要滿足多行單列結果當中的所有,條件才滿足

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

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

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

錯誤寫法:

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

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

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

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

any 關鍵字 :只要滿足多行單列結果當中的任一一個,則條件滿足

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

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

select distinct sal from emp where deptno=30

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

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

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

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

in:我是否屬于你們的一員 all:我比你們都怎么樣 any：我比你們任意一個人怎么樣

(3)多列子查詢

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

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

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

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

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

可以認為,()就是表示MySQL內部實現的集合

在from子句中使用子查詢

子查詢語句出現在from子句中,這里要用到數據查詢的技巧,把一個子查詢當做一個臨時表使用

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

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

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

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

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

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

我們只想要某些信息：

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

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

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

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

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

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

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

5.合并查詢

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

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

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

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

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

寫法2:求兩個表的并集

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

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

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

總結：

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

到此這篇關于MySQL數據庫復合查詢操作實戰的文章就介紹到這了,更多相關MySQL復合查詢內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！