前言

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

一、案例準(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部門表

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)

二、基本查詢

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

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

按照部門號(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ǔ)句，因此可以使用子查詢：

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

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

和上面的一樣，也需要用到子查詢。

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

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

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

三、多表查詢

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

顯示雇員名、雇員工資以及所在部門的名字

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

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

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

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

四、子查詢

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

4.1 單行子查詢

單行子查詢指的是返回一行記錄的子查詢，例如：

顯示SMITH同一部門的員工

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

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

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

4.2 多行子查詢

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

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

查詢案例：

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

首先查詢出10號(hào)部門所有的崗位

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

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

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

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

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

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

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

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

4.3 多列子查詢

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

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

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

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

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

4.4 FROM子句中的子查詢

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

查詢案例：

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

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

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

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

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

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

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

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

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

方法一：使用子查詢

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

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

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

方法二：使用多表

4.5 合并查詢

在實(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 查詢出來(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ì)。

五、自連接

所謂的自連接是指在同一張表連接查詢。
查詢案例：

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

方法一：子查詢

首先從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ù)合查詢操作都屬于內(nèi)連接，同時(shí)內(nèi)連接也是實(shí)際開(kāi)發(fā)過(guò)程中使用最多的連接查詢。

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

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

案例：顯示SMITH的名字和部門名稱

方法一：使用前面的查詢方式

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

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

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

6.2 外連接

外連接分為左外連接和右外連接。如果聯(liá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);

查詢所有學(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)合查詢，把所有的成績(jī)都顯示出來(lái)，即使這個(gè)成績(jī)沒(méi)有學(xué)生與它對(duì)應(yīng)，也要顯示出來(lái)

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

dept表左外連接emp表：

emp表右外連接dept表：

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

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

最新評(píng)論