目錄

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

前言

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

一、案例準備

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

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

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

使用年薪進行降序排序

注意：年薪 = 月薪 * 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三張表進行多表查詢的案例：

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

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

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

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

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

四、子查詢

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

4.1 單行子查詢

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

顯示SMITH同一部門的員工

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

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

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

4.2 多行子查詢

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

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

查詢案例：

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

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

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

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

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

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

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

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

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

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

4.3 多列子查詢

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

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

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

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

最后去掉SMITH的相關信息

4.4 FROM子句中的子查詢

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

查詢案例：

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

首先對部門進行分組，獲取其部門號即平均工資

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

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

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

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

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

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

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

方法一：使用子查詢

首先對部門進行分組，查找每個部門對應的人數(shù)

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

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

方法二：使用多表

4.5 合并查詢

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

4.5.1 UNION

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

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

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

4.5.2 UNION ALL

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

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

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

五、自連接

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

顯示員工FORD的上級領導的編號和姓名

方法一：子查詢

首先從emp表中找出FORD領導的編號，然后將其作為篩選條件查找出FORD的領導信息。

方法二：自連接

首先將兩張emp表分別作為leader表和worker表，查找出所有領導與員工之間的關系表

然后從關系表中，查找出員工為FORD的領導信息

六、內(nèi)外連接

6.1 內(nèi)連接

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

內(nèi)連接語法：

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

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

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

方法二：使用標準內(nèi)連接查詢

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

從以上關系中篩選出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)); -- 學生表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);

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

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

6.2.2 右外連接

語法：

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

案例：

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

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

dept表左外連接emp表：

emp表右外連接dept表：

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

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