目錄

• 前言
• 一、案例準(zhǔn)備
• 二、基本查詢(xún)
• 三、多表查詢(xún)
• 四、子查詢(xún)
• 4.1 單行子查詢(xún)
• 4.2 多行子查詢(xún)
• 4.3 多列子查詢(xún)
• 4.4 FROM子句中的子查詢(xún)
• 4.5 合并查詢(xún)
• 4.5.1 UNION
• 4.5.2 UNION ALL
• 五、自連接
• 六、內(nèi)外連接
• 6.1 內(nèi)連接
• 6.2 外連接
• 6.2.1 左外連接
• 6.2.2 右外連接

前言

對(duì)MySQL表的基本查詢(xún)還遠(yuǎn)遠(yuǎn)達(dá)不到實(shí)際開(kāi)發(fā)過(guò)程中的需求，因此還需要掌握對(duì)數(shù)據(jù)庫(kù)表的復(fù)合查詢(xún)。本文介紹了多表查詢(xún)、子查詢(xún)、自連接、內(nèi)外連接等復(fù)合查詢(xún)的案例。

一、案例準(zhǔn)備

來(lái)自oracle 9i的經(jīng)典測(cè)試表：

emp員工表

mysql> select * from emp;+--------+--------+-----------+------+---------------------+---------+---------+--------+| empno  | ename  | job       | mgr  | hiredate    | sal     | comm    | deptno |+--------+--------+-----------+------+---------------------+---------+---------+--------+| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 || 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 || 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 || 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 || 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 || 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 || 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 || 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 || 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 || 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 || 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 || 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 || 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 || 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |+--------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in set (0.00 sec)

dept部門(mén)表

mysql> select * from dept;+--------+------------+----------+| deptno | dname      | loc      |+--------+------------+----------+|     10 | ACCOUNTING | NEW YORK ||     20 | RESEARCH   | DALLAS   ||     30 | SALES      | CHICAGO  ||     40 | OPERATIONS | BOSTON   |+--------+------------+----------+4 rows in set (0.00 sec)

salgrade工資等級(jí)表

mysql> select * from salgrade;+-------+-------+-------+| grade | losal | hisal |+-------+-------+-------+|     1 |   700 |  1200 ||     2 |  1201 |  1400 ||     3 |  1401 |  2000 ||     4 |  2001 |  3000 ||     5 |  3001 |  9999 |+-------+-------+-------+5 rows in set (0.00 sec)

二、基本查詢(xún)

MySQL表的基本查詢(xún)都是針對(duì)一張表進(jìn)行的查詢(xún)操作，在實(shí)際開(kāi)發(fā)過(guò)程中還遠(yuǎn)遠(yuǎn)不夠。以下是以下基本查詢(xún)的案例：

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

按照部門(mén)號(hào)升序而雇員的工資降序排序

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

注意：年薪 = 月薪 * 12 + 績(jī)效獎(jiǎng)，其中有的績(jī)效comm為NULL，在MySQL中有NULL參與運(yùn)算的結(jié)果都為NULL，因此要使用到ifnull函數(shù)。

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

注意：因?yàn)橐褂玫絤ax聚合函數(shù)，因此不能直接將聚會(huì)函數(shù)返回的結(jié)果作為where篩選的條件去找某一個(gè)具體的記錄。因此可以先找出最大的薪資，在根據(jù)薪資找到該條記錄。

但是這樣的話就要使用兩條SQL語(yǔ)句，因此可以使用子查詢(xún)：

內(nèi)部select查詢(xún)到的結(jié)果，作為外部where篩選的條件。

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

和上面的一樣，也需要用到子查詢(xún)。

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

顯示平均工資低于2000的部門(mén)號(hào)和它的平均工資

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

三、多表查詢(xún)

實(shí)際開(kāi)發(fā)中，數(shù)據(jù)往往來(lái)自不同的表，因此需要多表查詢(xún)。以下是使用emp、dept、salgrade三張表進(jìn)行多表查詢(xún)的案例：

顯示雇員名、雇員工資以及所在部門(mén)的名字

由于以上要查詢(xún)的數(shù)據(jù)分別來(lái)自于emp表和dept表，因此要聯(lián)合這兩張表進(jìn)行查詢(xún)：

使用上面的查詢(xún)方法查詢(xún)出來(lái)的包含許多錯(cuò)誤的結(jié)果，因此需要使用emp.deptno = dept.deptno條件來(lái)進(jìn)行查詢(xún)：

顯示部門(mén)號(hào)為10的部門(mén)名，員工名和工資

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

四、子查詢(xún)

子查詢(xún)是指嵌入到其他SQL語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢(xún)。

4.1 單行子查詢(xún)

單行子查詢(xún)指的是返回一行記錄的子查詢(xún)，例如：

顯示SMITH同一部門(mén)的員工

首先從emp表中找出SMITH所在部門(mén)的部門(mén)號(hào)：

然后將該部門(mén)號(hào)作為篩選的條件，篩選出與該部門(mén)號(hào)相同的員工信息，并且不包含SMITH：

由此可見(jiàn)，子查詢(xún)就是將第一次select查詢(xún)的結(jié)果，作為第二次select查詢(xún)的篩選條件。

4.2 多行子查詢(xún)

多行子查詢(xún)就是返回多行記錄的子查詢(xún)，此時(shí)一般會(huì)用于IN、ALL、ANY 這些關(guān)鍵字：

IN：表示存在，即需滿足存在條件ALL：表示所有，即需滿足所有條件ANY：表示任一，即需滿足任一條件

查詢(xún)案例：

IN關(guān)鍵字：查詢(xún)和10號(hào)部門(mén)的工作崗位相同的雇員的名字，崗位，工資，部門(mén)號(hào)，但是不包含10號(hào)部門(mén)自己的

首先查詢(xún)出10號(hào)部門(mén)所有的崗位

然后將這些崗位信息作為下一次查詢(xún)的篩選條件進(jìn)行查詢(xún)

最后去掉10號(hào)部門(mén)的員工信息

ALL關(guān)鍵字：顯示工資比部門(mén)30的所有員工的工資高的員工的姓名、工資和部門(mén)號(hào)

首先查找出30號(hào)部門(mén)所有的員工工資

然后將其作為篩選條件查找出比30號(hào)部門(mén)的所有員工工資都高的員工信息

ANY關(guān)鍵字：顯示工資比部門(mén)30的任意員工的工資高的員工的姓名、工資和部門(mén)號(hào)（包含自己部門(mén)的員工）

該案例的前面部分和上面的案例一樣，也是首先找出30號(hào)部門(mén)所有員工的工資，然后再使用ANY關(guān)鍵字找出比部門(mén)30的任意員工的工資高的員工信息：

任一當(dāng)然也包含了30號(hào)部門(mén)的內(nèi)部員工，因此只需大于30號(hào)部門(mén)最低的員工工資的員工都會(huì)被篩選出來(lái)。

4.3 多列子查詢(xún)

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

案例：查詢(xún)和SMITH的部門(mén)和崗位完全相同的所有雇員，不含SMITH本人

首先查找出SMITH的部門(mén)號(hào)和崗位信息

然后以SMITH的部門(mén)號(hào)和崗位信息作為篩選條件進(jìn)行篩選

最后去掉SMITH的相關(guān)信息

4.4 FROM子句中的子查詢(xún)

FROM子句中的子查詢(xún)就是指子查詢(xún)語(yǔ)句出現(xiàn)在FROM后面，其實(shí)就是把子查詢(xún)的結(jié)果當(dāng)成一張臨時(shí)表使用。

查詢(xún)案例：

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

首先對(duì)部門(mén)進(jìn)行分組，獲取其部門(mén)號(hào)即平均工資

將查詢(xún)結(jié)果作為一張臨時(shí)表，獲取其與emp表的笛卡爾積

最后在笛卡爾積表當(dāng)中篩選出每個(gè)高于自己部門(mén)平均工資的員工的姓名、部門(mén)、工資、平均工資

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

首先分組查詢(xún)獲取每個(gè)部門(mén)的部門(mén)號(hào)和最高工資

然后將查詢(xún)結(jié)果作為臨時(shí)表，并獲取其與emp表的笛卡爾積

從獲取的笛卡爾積中篩選出每個(gè)部門(mén)工資最高的人的姓名、工資、部門(mén)、最高工資

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

方法一：使用子查詢(xún)

首先對(duì)部門(mén)進(jìn)行分組，查找每個(gè)部門(mén)對(duì)應(yīng)的人數(shù)

將查詢(xún)的結(jié)果作為臨時(shí)表，獲取其與dept表的笛卡爾積

從笛卡爾積表中篩選出每個(gè)部門(mén)的信息及其部門(mén)人數(shù)

方法二：使用多表

4.5 合并查詢(xún)

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

4.5.1 UNION

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

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

可以發(fā)現(xiàn)，使用 OR 和 UNION 查詢(xún)出來(lái)的結(jié)果相同。

4.5.2 UNION ALL

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

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

此時(shí)可以發(fā)現(xiàn)UNION和UNION ALL的唯一區(qū)別就是前者會(huì)對(duì)查找結(jié)果進(jìn)行去重，而后者不會(huì)。

五、自連接

所謂的自連接是指在同一張表連接查詢(xún)。
查詢(xún)案例：

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

方法一：子查詢(xún)

首先從emp表中找出FORD領(lǐng)導(dǎo)的編號(hào)，然后將其作為篩選條件查找出FORD的領(lǐng)導(dǎo)信息。

方法二：自連接

首先將兩張emp表分別作為leader表和worker表，查找出所有領(lǐng)導(dǎo)與員工之間的關(guān)系表

然后從關(guān)系表中，查找出員工為FORD的領(lǐng)導(dǎo)信息

六、內(nèi)外連接

6.1 內(nèi)連接

內(nèi)連接實(shí)際上就是利用WHERE子句對(duì)兩張表形成的笛卡爾積進(jìn)行篩選，因此前面所有的復(fù)合查詢(xún)操作都屬于內(nèi)連接，同時(shí)內(nèi)連接也是實(shí)際開(kāi)發(fā)過(guò)程中使用最多的連接查詢(xún)。

內(nèi)連接語(yǔ)法：

select 字段 from 表1 inner join 表2 on 連接條件 and 其他條件;

案例：顯示SMITH的名字和部門(mén)名稱(chēng)

方法一：使用前面的查詢(xún)方式

方法二：使用標(biāo)準(zhǔn)內(nèi)連接查詢(xún)

首先通過(guò)內(nèi)連接查詢(xún)出所有員工與其所在部門(mén)名之間的關(guān)系

從以上關(guān)系中篩選出SMITH與其部門(mén)名

6.2 外連接

外連接分為左外連接和右外連接。如果聯(lián)合查詢(xún)，左側(cè)的表完全顯示我們就說(shuō)是左外連接，右側(cè)的表完全顯示就是右外連接。

6.2.1 左外連接

語(yǔ)法：

select 字段 from 表1 inner join 表2 on 連接條件 and 其他條件;

案例：

-- 建兩張表create table stu (id int, name varchar(30)); -- 學(xué)生表insert into stu values(1,"jack"),(2,"tom"),(3,"kity"),(4,"nono");create table exam (id int, grade int); -- 成績(jī)表insert into exam values(1, 56),(2,76),(11, 8);

查詢(xún)所有學(xué)生的成績(jī)，如果這個(gè)學(xué)生沒(méi)有成績(jī)，也要將學(xué)生的個(gè)人信息顯示出來(lái)

可以發(fā)現(xiàn)，左外連接以左表的內(nèi)容為準(zhǔn)，顯示其全部?jī)?nèi)容，如果右邊沒(méi)有對(duì)應(yīng)信息，則顯示為NULL。

6.2.2 右外連接

語(yǔ)法：

select 字段 from 表名1 right join 表名2 on 連接條件；

案例：

對(duì)stu表和exam表聯(lián)合查詢(xún)，把所有的成績(jī)都顯示出來(lái)，即使這個(gè)成績(jī)沒(méi)有學(xué)生與它對(duì)應(yīng)，也要顯示出來(lái)

對(duì)dept表和emp表聯(lián)合查詢(xún)，列出部門(mén)名稱(chēng)和這些部門(mén)的員工信息，同時(shí)列出沒(méi)有員工的部門(mén)

dept表左外連接emp表：

emp表右外連接dept表：

由此可見(jiàn)左外連接和右外連接可以相互轉(zhuǎn)換。

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