前言

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

一、案例準(zhǔn)備

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

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工資等級表

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表的基本查詢都是針對一張表進(jìn)行的查詢操作，在實際開發(fā)過程中還遠(yuǎn)遠(yuǎn)不夠。以下是以下基本查詢的案例：

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

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

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

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

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

注意：因為要使用到max聚合函數(shù)，因此不能直接將聚會函數(shù)返回的結(jié)果作為where篩選的條件去找某一個具體的記錄。因此可以先找出最大的薪資，在根據(jù)薪資找到該條記錄。

但是這樣的話就要使用兩條SQL語句，因此可以使用子查詢：

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

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

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

顯示每個部門的平均工資和最高工資

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

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

三、多表查詢

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

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

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

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

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

顯示各個員工的姓名，工資，及工資級別

四、子查詢

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

4.1 單行子查詢

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

顯示SMITH同一部門的員工

首先從emp表中找出SMITH所在部門的部門號：

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

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

4.2 多行子查詢

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

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

查詢案例：

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

首先查詢出10號部門所有的崗位

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

最后去掉10號部門的員工信息

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

首先查找出30號部門所有的員工工資

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

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

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

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

4.3 多列子查詢

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

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

首先查找出SMITH的部門號和崗位信息

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

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

4.4 FROM子句中的子查詢

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

查詢案例：

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

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

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

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

查找每個部門工資最高的人的姓名、工資、部門、最高工資

首先分組查詢獲取每個部門的部門號和最高工資

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

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

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

方法一：使用子查詢

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

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

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

方法二：使用多表

4.5 合并查詢

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

4.5.1 UNION

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

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

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

4.5.2 UNION ALL

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

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

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

五、自連接

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

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

方法一：子查詢

首先從emp表中找出FORD領(lǐng)導(dǎ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)連接實際上就是利用WHERE子句對兩張表形成的笛卡爾積進(jìn)行篩選，因此前面所有的復(fù)合查詢操作都屬于內(nèi)連接，同時內(nèi)連接也是實際開發(fā)過程中使用最多的連接查詢。

內(nèi)連接語法：

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

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

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

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

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

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

6.2 外連接

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

6.2.1 左外連接

語法：

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); -- 成績表
insert into exam values(1, 56),(2,76),(11, 8);

查詢所有學(xué)生的成績，如果這個學(xué)生沒有成績，也要將學(xué)生的個人信息顯示出來

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

6.2.2 右外連接

語法：

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

案例：

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

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

dept表左外連接emp表：

emp表右外連接dept表：

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

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

最新評論