? 前面我們學(xué)習(xí)了MySQL簡單的單表查詢。但是我們發(fā)現(xiàn)，在很多情況下單表查詢并不能很好的滿足我們的查詢需求。本篇文章會重點(diǎn)講解MySQL中的多表查詢、子查詢和一些復(fù)雜查詢。希望本篇文章會對你有所幫助。

文章目錄

一、基本查詢回顧

二、多表查詢

2、1 笛卡爾積

2、2 多表查詢練習(xí)

三、自連接

四、子查詢

4、1 單行子查詢

4、2 多行子查詢

4、3 多列子查詢

4、4?在from子句中使用子查詢

五、合并查詢

---

🙋?♂??作者：@Ggggggtm?🙋?♂?

👀?專欄：MySQL?👀

💥?標(biāo)題：MySQL復(fù)合查詢💥

????寄語：與其忙著訴苦，不如低頭趕路，奮路前行，終將遇到一番好風(fēng)景???

? 在對本篇文章學(xué)習(xí)之前，首先說明一下本篇文章所用到表的結(jié)構(gòu)和內(nèi)容。具體如下：

• 員工表emp：
• 部門表dept：
• 薪水表salgrade:

一、基本查詢回顧

查詢工資高于500或崗位為MANAGER的雇員，同時(shí)還要滿足他們的姓名首字母為大寫的J?

? 首先確定，上述所需篩選的信息都在一行表中。其次，分析出 工資 > 500 or job = MANAGER。我們先來查詢出滿足?工資 > 500 or job = MANAGER 的員工。具體如下：

? 同時(shí)，我們還需要滿足所查詢到的員工的姓名首字母為大寫的J，很明顯是模糊查詢。具體如下圖：

按照部門號升序而雇員的工資降序排序

? 這個(gè)需求就是簡單的排序即可。注意所需排序的先后順序。具體如下圖：

使用年薪進(jìn)行降序排序

? 首先我們需要計(jì)算出來年薪。年薪 = 月薪（sal）*12 + 年終獎（comm）。那么我們直接就對其進(jìn)行排序即可。但是需要注意的是：NULL并不能參與計(jì)算，這時(shí)候需要內(nèi)置函數(shù)ifnull來進(jìn)行判斷其是否為NULL，如果為NULL直接加0即可。?具體如下：

顯示工資最高的員工的名字和工作崗位?

? 我們可以很容易的查找到最高工資是多少，然后再根據(jù)最高工資去找對應(yīng)的員工的名字和工作崗位。具體如下圖：

? 上述用了兩條SQL語句確實(shí)能夠查詢出我們想要的結(jié)果。但是好像不太優(yōu)雅。能不能用一條語句將所需結(jié)果查詢出來呢？答案是可以的。我們可以用子查詢。什么是子查詢呢？在 MySQL 中，子查詢是指在一個(gè)查詢語句中嵌套另一個(gè)查詢語句。子查詢可以用于過濾結(jié)果集、作為計(jì)算字段的數(shù)據(jù)源、與外部查詢進(jìn)行比較等多種情況。下面我們用子查詢來解決這個(gè)需求。具體如下：

顯示工資高于平均工資的員工信息?

? 這個(gè)題目的需求與上一個(gè)題目的需求很相似。我們可以先獲取平均工資，在查詢比平均工資高的員工，一樣是用子查詢。具體如下：

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

? 我們看到需求是每個(gè)部門，那么首先肯定要按部門號進(jìn)行分組。其次我們再查詢每個(gè)部門的平均工資和最高工資。具體如下圖：

顯示平均工資低于2000的部門號和它的平均工資

? 首先我們很容易可以找到各個(gè)部門的平均工資，然后只需要再增加一個(gè)條件判斷即可。具體如下：

顯示每種崗位的雇員總數(shù)，平均工資??

? 注意是每種崗位，所以需要根據(jù)job進(jìn)行分組查詢。具體如下圖：

二、多表查詢

2、1 笛卡爾積

? 在MySQL中，多表查詢的笛卡爾積（Cartesian Product）是指在沒有使用任何條件或連接的情況下，將兩個(gè)或多個(gè)表中的所有行進(jìn)行組合的結(jié)果集。這種情況通常是在沒有明確指定連接條件或者WHERE子句的情況下進(jìn)行的查詢，但在實(shí)際應(yīng)用中，很少需要或者希望獲得笛卡爾積結(jié)果。

? 以下是一個(gè)簡單的說明以及一個(gè)示例來解釋笛卡爾積：

1. 笛卡爾積的性質(zhì)： 笛卡爾積將參與查詢的每個(gè)表的所有可能組合都返回，即第一個(gè)表的每一行都會與第二個(gè)表的每一行進(jìn)行組合，生成的結(jié)果集的行數(shù)為各個(gè)表行數(shù)的乘積。

2. 示例：我們現(xiàn)在將員工表和部門表進(jìn)行笛卡爾積。具體如下：其實(shí)我們也不難看出，規(guī)律就是如下圖：

? 但是往往我們用笛卡爾積所獲取的表有很多的數(shù)據(jù)冗余。因?yàn)樗鼤a(chǎn)生大量的冗余數(shù)據(jù)并且效率低下。為了避免得到笛卡爾積，我們需要正確地使用連接條件（例如使用where條件來篩選掉無用信息）來明確指定表之間的關(guān)聯(lián)關(guān)系。例如，在對上述的員工表和部門表進(jìn)行笛卡爾積時(shí)，一個(gè)員工不可能會有多個(gè)部門號，所以只有部門號相同的才算是有效的信息。最終有效結(jié)果如下圖：

2、2 多表查詢練習(xí)

顯示部門號為 10 的部門名，員工名和工資

我們發(fā)現(xiàn)員工表中并沒有我們想要的部門名，所以我們需要進(jìn)行多表查詢。需要將員工表和部門表進(jìn)行合并查詢。然后在查詢部門號為10的部門名、員工名和工資。具體如下：

這里再說明一下：上述 SQL語句中 from 后 的 t1 和 t2 是對 emp 和 dept 表進(jìn)行了重命名，后續(xù)都可以 用我們重命名的名字去代替表名字。其次是當(dāng)我們將兩張表拼接到一塊后，表中會有 兩個(gè)deptno，所以我們在使用deptno時(shí)，需要指定是那個(gè)表的。

顯示各個(gè)員工的姓名，工資，及工資級別

? ?我們發(fā)現(xiàn)工資等級只有在薪資表中有，所以我們需要進(jìn)行多表查詢。當(dāng)我們將員工表與薪水表進(jìn)行笛卡爾積后，發(fā)現(xiàn)很多數(shù)據(jù)是冗余的。只有薪資符合它所在的等級區(qū)間才是有效的。所以我們的查尋結(jié)果如下：

三、自連接

? 我們上述講解的是兩張不同的表進(jìn)行連接。那么可以自己與自己的表進(jìn)行連接嗎？答案是可以的！MySQL中的自連接是指在同一張表中進(jìn)行連接操作。這種連接通常用于將表中的數(shù)據(jù)與自身進(jìn)行比較或者組合。自連接可以通過將表與自身進(jìn)行別名來實(shí)現(xiàn)，從而使得查詢可以使用表中的不同行進(jìn)行比較和操作。?我們看如下例子：

? 通過上圖我們發(fā)現(xiàn)，當(dāng)進(jìn)行自連接時(shí)，如果不對表進(jìn)行取別名，那么將不能夠進(jìn)行自連接。必須對表進(jìn)行取別名。自連接的使用場景是什么呢？我們看如下例子。

?顯示員工FORD的上級領(lǐng)導(dǎo)的編號和姓名（mgr是員工領(lǐng)導(dǎo)的編號--empno）

? 員工是在emp表中，上級領(lǐng)導(dǎo)也是員工，也在emp表中。我們可能首先會想到用子查詢來解決，相對簡單。具體如下：

? 但是我們也不難發(fā)現(xiàn)，要查詢的兩個(gè)條件都是在emp表中，那么我們就可以對emp表進(jìn)行自連接。我們現(xiàn)在把兩張表想象成一張表是員工表，另一張表是領(lǐng)導(dǎo)表。我們現(xiàn)在需要的有效信息是：員工表中的mgr = 領(lǐng)導(dǎo)表中的empno即可。篩選出有效信息后在選擇員工表中的員工為FORD。具體如下：

四、子查詢

? 子查詢的概念在上文中已經(jīng)解釋過，這里就不再解釋。在子查詢的子句中，子句查詢出的結(jié)果可能不止是一行記錄，也有可能是多行記錄，還有就是多列的情況。下面我們一一來分析一下。

4、1 單行子查詢

顯示 SMITH 同一部門的員工

? 首先將SMITH的部門號查出，然后再將該部門的所有員工篩選出即可。具體如下：

4、2 多行子查詢

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

? 我們可以先查詢出10號部門的工作崗位，具體如下：

? 然后我們再進(jìn)行篩選與上圖中崗位相同的雇員的信息。當(dāng)我們想用子查詢時(shí)，發(fā)現(xiàn)上圖的崗位并不是一個(gè)，那該怎么辦呢？這時(shí)候可以用到 in關(guān)鍵字。in關(guān)鍵字用于檢查某個(gè)值是否在一組值中。剛好符合我們的需求。具體如下：

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

? 題目的要求：找出比30號部門所有員工工資都好的員工信息。也就是比30號部門最高工資還要高的部門。我們首先找出30號部門的員工最高工資，再篩選出薪資比它大的即可。具體如下：

? 我們也可以使用all關(guān)鍵字。all關(guān)鍵字用于比較外部查詢和子查詢返回的所有值。當(dāng)使用?all關(guān)鍵字時(shí)，外部查詢的值必須滿足子查詢返回的所有值的條件才會被選中。具體如下：

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

注意：題目中的任意員工，是指的只要有比部門30中的員工工資高的即滿足條件。通俗理解：找出比 部門號30的員工中最低工資 高的員工。這時(shí)可以用any關(guān)鍵字。 any關(guān)鍵字用于比較外部查詢和子查詢返回的任意一個(gè)值。 當(dāng)使用 any? 時(shí)，外部查詢的值只需要滿足子查詢返回的任意一個(gè)值的條件即可被選中。具體如下：

4、3 多列子查詢

? 單行子查詢是指子查詢只返回單列，單行數(shù)據(jù)；多行子查詢是指返回單列多行數(shù)據(jù)，都是針對單列而言的，而多列子查詢則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語句。下面我們來看一個(gè)例子。

查詢和 SMITH 的部門和崗位完全相同的所有雇員，不含 SMITH 本人

? 我們可以先查詢出來SMITH的部門和崗位。如下圖：

? 我們發(fā)現(xiàn)，要和SMITH的部門和崗位完全相同，是多列的情況。這該怎么辦呢？我們看如下：

? 但是題目中還要求了不能包含SMITH本人。所以再把SMITH本人去掉即可。結(jié)果如下：

4、4?在from子句中使用子查詢

? 我們之前學(xué)到的from后都是跟的表的名字。在from子句中使用子查詢怎么理解呢？使用子查詢無非就是一個(gè)查詢語句中嵌套了一個(gè)語句。我們就稱之為子句。那么子句查詢出來的結(jié)果我們也可看成一張表，可與其他物理上實(shí)力存在的表進(jìn)行連接。這就是在from子句中使用子查詢的意思。下面我們結(jié)合實(shí)際例子來理解一下。

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

? 我們可以很容易得到每個(gè)部門的平均工資，具體如下：

? 我們可以把上述所查詢出來的結(jié)果當(dāng)作一個(gè)表，再與emp表進(jìn)行連接即可。具體如下：

? 對我們來說，有用的信息就是emp.deptno = tmp.deptno。那么查詢出來的結(jié)果如下：

? 現(xiàn)在我們只需要emp.sal > tmp.平均工資（ avg（sal））?即可，就是題目所要求的答案，具體如下：

顯示每個(gè)部門的信息（部門名，編號，地址）和人員數(shù)量

? 我們發(fā)現(xiàn)，部門名和地址都在部門表中，而我們想要統(tǒng)計(jì)每個(gè)部門的人員數(shù)量還需要在emp表中統(tǒng)計(jì)。我們先來統(tǒng)計(jì)每個(gè)部門的人員數(shù)量，具體如下：

? 我們再將上述查詢的結(jié)果與部門dept表進(jìn)行連接，得到有用信息如下圖：

? 此時(shí)，我們在獲取題目中的所需要的信息就相當(dāng)容易了。具體如下圖：

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

? 首先，我們可以很容易的得到每個(gè)部門的最高工資，如下圖：

? 但是怎么獲取工資最高的人的信息呢？這時(shí)候可以將我們查詢的結(jié)果與emp表連接，再獲取該人的信息就可以了。具體如下：

五、合并查詢

? 在MySQL中，合并查詢指的是將多個(gè)查詢結(jié)果合并成一個(gè)結(jié)果集的操作。這可以通過使用union、union?all等操作符來實(shí)現(xiàn)。以下是對每種操作符的詳細(xì)解釋：

1. union：union操作符用于將兩個(gè)或多個(gè)select語句的結(jié)果合并為一個(gè)結(jié)果集，并自動去重。

2. union?all：與union類似，但不會自動去重。

? 下面我們來看幾個(gè)實(shí)際例子來理解一下。

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

? 這個(gè)例子我們前面已經(jīng)做過類似的，不再過多解釋，直接看下圖：

? 我們也可以先將工資大于2500的人找出來，如下：

? 再找出來職位是MANAGER的。如下圖：

? 最后用union將他們兩個(gè)合并即可。具體如下：

? 我們再來用union all 將他們合并試試。具體如下圖：

? 從上述的對比中，我們也能看出來union是合并并且去重，union all就只是合并。注意：兩個(gè)select合并的前提是必須所查詢出來的列數(shù)是相同的。實(shí)際中，union并不常用，我們只是了解一下即可。