MySQL從零開始了解數(shù)據(jù)庫開發(fā)之復(fù)合查詢

更新時(shí)間：2025年11月04日 16:47:23 作者：叫我龍翔

本文介紹了MySQL數(shù)據(jù)庫開發(fā)中的復(fù)合查詢技術(shù),包括多表查詢、自連接和子查詢?nèi)N方法,示例演示了從員工表和部門表中聯(lián)合查詢員工姓名、工資及部門名稱, 本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧

1 多表查詢

多表查詢顧名思義是從多張表中查詢數(shù)據(jù)，而之前都是從單表讀取的。在實(shí)際開發(fā)過程中，同一模塊的數(shù)據(jù)會(huì)存放在很多中，查詢時(shí)進(jìn)行多表查詢時(shí)非常頻繁的操作

本節(jié)我們用一個(gè)簡(jiǎn)單的公司管理系統(tǒng)，有三張表EMP,DEPT,SALGRADE來演示如何進(jìn)行多表查詢。
表結(jié)構(gòu)：

現(xiàn)在我們要顯示雇員名、雇員工資以及所在部門的名字， 因?yàn)楣蛦T名，雇員工資和所在部門數(shù)據(jù)分別來自EMP和DEPT表，所以這里需要多表查詢。

執(zhí)行語句

select * from EMP , DEPT;

我們會(huì)得到一個(gè)大表：

這個(gè)表是由笛卡爾積得到的，EMP表中的每個(gè)數(shù)據(jù)都與DEPT的數(shù)據(jù)進(jìn)行一次匹配整合。

笛卡爾積得到的這張大表就是我們進(jìn)行多表查詢的基礎(chǔ)，這個(gè)操作將兩張表的數(shù)據(jù)整合到一張表里。接下來我們可以通過增加where條件限制，獲取有效的數(shù)據(jù)：

接下來就可以通過指定列查詢需要的數(shù)據(jù)了！

同樣下面幾個(gè)例子也就可以快速寫出來

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

select  EMP.ENAME,DEPT.DNAME , EMP.SAL from EMP , DEPT where EMP.DEPTNO = DEPT.DEPTNO and EMP.DEPTNO = 10;

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

mysql> select  *  from EMP , SALGRADE WHERE EMP.SAL between LOSAL and HISAL;

2 自連接

剛才我們是對(duì)兩張不同的表進(jìn)行笛卡爾積，然后通過大表的數(shù)據(jù)進(jìn)行讀取，那么對(duì)于同一張表也可以進(jìn)行笛卡爾積！
注意需要對(duì)這個(gè)表取一下別名，不然會(huì)認(rèn)為是同一張表語法而報(bào)錯(cuò)

這樣就得到了同一張表的笛卡爾積的結(jié)果，稱之為自連接。那么什么場(chǎng)景需要是需要自連接來解決的呢？我們來個(gè)場(chǎng)景：

顯示員工Ford的上級(jí)領(lǐng)導(dǎo)的編號(hào)和姓名（mgr代表員工領(lǐng)導(dǎo)的編號(hào)）

因?yàn)轭I(lǐng)導(dǎo)也是員工，所以都儲(chǔ)存在員工表中。先分析一下首先先選取匹配的領(lǐng)導(dǎo)，然后找到員工Ford；

mysql> select t1.ename , t2.ename '領(lǐng)導(dǎo)', t2.empno from emp t1 , emp t2 where t1.mgr = t2.empno and t1.ename = 'FORD';
+-------+--------+-------+
| ename | 領(lǐng)導(dǎo)   | empno |
+-------+--------+-------+
| FORD  | JONES  |  7566 |
+-------+--------+-------+
1 row in set (0.00 sec)

這就就成功篩選出來了；

3 子查詢

子查詢用于多表查詢，多表查詢問題的本質(zhì)就是想辦法將多表轉(zhuǎn)換為單表

3.1 單行子查詢

子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢。區(qū)別于上面連接構(gòu)建笛卡爾積的方法，子查詢是通過構(gòu)建子查詢來完成查詢?nèi)蝿?wù)。

子查詢的核心邏輯是：mysql萬物皆為表，查詢結(jié)果也是一種表。

比如下面幾個(gè)場(chǎng)景

查詢SMITH同一部門的員工

首先需要向找到SMITH是哪一個(gè)部門，然后根據(jù)這個(gè)部門去查詢員工

mysql> select * from emp where ename = 'SMITH';
+-------+-------+-------+------+------------+--------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal    | comm | deptno |
+-------+-------+-------+------+------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL |     20 |
+-------+-------+-------+------+------------+--------+------+--------+
1 row in set (0.00 sec)
mysql> select deptno  from emp where ename = 'SMITH';
+--------+
| deptno |
+--------+
|     20 |
+--------+
1 row in set (0.00 sec)
mysql> select * from emp where deptno = (select deptno  from emp where ename = 'SMITH');
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL |     20 |
|  7876 | ADAMS | CLERK   | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7902 | FORD  | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+---------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)

這樣我們就通過對(duì)子查詢查詢出來的部門去找到了目標(biāo)員工。

查詢與10號(hào)部門的工作崗位相同的雇員的名字，崗位，工資，部門名稱，但是不包含10自己的

我們來拆解一下查詢內(nèi)容：

首先先找到10號(hào)部門的工作崗位
然后根據(jù)工作崗位去查詢雇員信息
查詢的雇員信息與部門表做笛卡爾積找到部門名稱

這樣一步一步就可以：

mysql> select job from emp where deptno = 10;
+-----------+
| job       |
+-----------+
| MANAGER   |
| PRESIDENT |
| CLERK     |
+-----------+
3 rows in set (0.00 sec)
mysql> select * from emp where job in (select distinct job from emp where deptno = 10);
+-------+--------+-----------+------+------------+---------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm | deptno |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 | NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
8 rows in set (0.01 sec)
mysql> select * from emp where job in (select distinct job from emp where deptno = 10) and deptno != 10;
+-------+-------+---------+------+------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+---------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL |     30 |
|  7876 | ADAMS | CLERK   | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7900 | JAMES | CLERK   | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
+-------+-------+---------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)
mysql> select ename , job , sal , dname from (select ename , job , sal , deptno  from emp where job in (select distinct job from emp where deptno = 10) and deptno != 10) as tmp , deept where tmp.deptno = dept.deptno;
+-------+---------+---------+----------+
| ename | job     | sal     | dname    |
+-------+---------+---------+----------+
| SMITH | CLERK   |  800.00 | RESEARCH |
| JONES | MANAGER | 2975.00 | RESEARCH |
| BLAKE | MANAGER | 2850.00 | SALES    |
| ADAMS | CLERK   | 1100.00 | RESEARCH |
| JAMES | CLERK   |  950.00 | SALES    |
+-------+---------+---------+----------+
5 rows in set (0.00 sec)

所以查詢出來的結(jié)果也是可以做笛卡爾積的，并且可以作為查詢的數(shù)據(jù)表

3.2 多行子查詢

顯示工資比部門30的所有員工的工資高的員工的姓名，工資與部門號(hào)

首先找到部門30所有員工的最高工資
根據(jù)tmp的結(jié)果作為篩選條件去查詢員工信息

mysql> select sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+
6 rows in set (0.00 sec)
mysql> select ename , sal , deptno from emp where sal > (select max(sal) from emp where deptno = 30);
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.00 sec)

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

首先找到部門30所有員工的最低工資
根據(jù)tmp的結(jié)果作為篩選條件去查詢員工信息

mysql> select ename , sal , deptno from emp where sal > (select min(sal) from emp where deptno = 30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
12 rows in set (0.00 sec)

總結(jié)一下，子查詢的結(jié)果是可以作為查詢范圍的。并且可以使用以下關(guān)鍵字進(jìn)行優(yōu)化：

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

select ename, sal, deptno from EMP where sal > all(select sal from EMP wheredeptno=30);

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

select ename, sal, deptno from EMP where sal > any(select sal from EMP wheredeptno=30);

3.3 多列子查詢

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

來看這個(gè)場(chǎng)景：

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

首先查詢SMITH的部門與崗位
將查詢出來的部門崗位作為篩選條件，去除SMITH本人

mysql> select deptno , job from emp where ename = 'SMITH';
+--------+-------+
| deptno | job   |
+--------+-------+
|     20 | CLERK |
+--------+-------+
1 row in set (0.00 sec)
mysql> select * from emp where (deptno , job) = (select deptno , job from emp where ename = 'SMITH');
+-------+-------+-------+------+------------+---------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-------+------+------------+---------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
+-------+-------+-------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp where (deptno , job) = (select deptno , job from emp where ename = 'SMITH') and ename != 'SMITH';
+-------+-------+-------+------+------------+---------+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal     | comm | deptno |
+-------+-------+-------+------+------------+---------+------+--------+
|  7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
+-------+-------+-------+------+------------+---------+------+--------+
1 row in set (0.00 sec)

3.4 from中使用子查詢

上面我們說過子查詢的結(jié)果本質(zhì)也是一種表，也可以進(jìn)行笛卡爾積！

來看下面幾個(gè)場(chǎng)景：

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

首先先計(jì)算出部門平均工資作為臨時(shí)表
然后臨時(shí)表與員工表做笛卡爾積篩選出來平均工資高于部門平均工資的員工
最后與部門表進(jìn)行笛卡爾積，找到對(duì)應(yīng)的部門名稱

mysql> select deptno , AVG(sal) from emp group by deptno;
+--------+-------------+
| deptno | AVG(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)
mysql> select ename , t1.deptno , sal , t1.avg_sal from (select deptno , AVG(sal) avg_sal from emp group by deptno) t1, emp t2 where t1.deptno = t2.deptno and t2.sal > t1.avg_sal;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avg_sal     |
+-------+--------+---------+-------------+
| ALLEN |     30 | 1600.00 | 1566.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| KING  |     10 | 5000.00 | 2916.666667 |
| FORD  |     20 | 3000.00 | 2175.000000 |
+-------+--------+---------+-------------+
6 rows in set (0.00 sec)
mysql> select ename , dname , sal , avg_sal from (select ename , t1.deptno deptno, sal , avg_sal from (select deptno , AVG(sal) avg_sal from emp group by deptno) t1, emp t2 where t11.deptno = t2.deptno and t2.sal > t1.avg_sal) t1 , dept t2 where t1.deptno = t2.deptno;
+-------+------------+---------+-------------+
| ename | dname      | sal     | avg_sal     |
+-------+------------+---------+-------------+
| KING  | ACCOUNTING | 5000.00 | 2916.666667 |
| JONES | RESEARCH   | 2975.00 | 2175.000000 |
| SCOTT | RESEARCH   | 3000.00 | 2175.000000 |
| FORD  | RESEARCH   | 3000.00 | 2175.000000 |
| ALLEN | SALES      | 1600.00 | 1566.666667 |
| BLAKE | SALES      | 2850.00 | 1566.666667 |
+-------+------------+---------+-------------+
6 rows in set (0.00 sec)

這樣就成功篩選出來了！

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

對(duì)EMP表進(jìn)行人員統(tǒng)計(jì)
將上面的表看作臨時(shí)表

mysql> select count(*), deptno from emp group by deptno;
+----------+--------+
| count(*) | deptno |
+----------+--------+
|        3 |     10 |
|        5 |     20 |
|        6 |     30 |
+----------+--------+
3 rows in set (0.01 sec)
mysql> select dept.deptno, dname, mycnt, loc from dept,
    -> (select count(*) mycnt, deptno from emp group by deptno) tmp
    -> where dept.deptno=tmp.deptno;
+--------+------------+-------+----------+
| deptno | dname      | mycnt | loc      |
+--------+------------+-------+----------+
|     10 | ACCOUNTING |     3 | NEW YORK |
|     20 | RESEARCH   |     5 | DALLAS   |
|     30 | SALES      |     6 | CHICAGO  |
+--------+------------+-------+----------+
3 rows in set (0.00 sec)

3.5 合并查詢

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

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

來看一個(gè)場(chǎng)景：

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

mysql> select ename, sal, job from EMP where sal>2500 union
	-> select ename, sal, job from EMP where job='MANAGER';--去掉了重復(fù)記錄
+-------+---------+-----------+
| ename | sal 	  | job 	|
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+

4 內(nèi)外連接

數(shù)據(jù)表的連接分為內(nèi)連接與外連接

4.1 內(nèi)連接

內(nèi)連接實(shí)際上就是利用where子句對(duì)兩種表形成的笛卡兒積進(jìn)行篩選，我們前面學(xué)習(xí)的查詢都是內(nèi)連接，也是在開發(fā)過程中使用的最多的連接查詢。

語法：

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

對(duì)于前面的場(chǎng)景可以使用內(nèi)連接進(jìn)行書寫：顯示SMITH的名字和部門名稱

-- 用前面的寫法
select ename, dname from EMP, DEPT where EMP.deptno=DEPT.deptno and ename='SMITH';
-- 用標(biāo)準(zhǔn)的內(nèi)連接寫法
select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and ename='SMITH';

4.2 外連接

外連接分為左外連接和右外連接

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

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

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

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

我們建立一個(gè)場(chǎng)景，就可以了解外連接做了一件什么是事情了：

-- 建兩張表
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é)生沒有成績(jī)，也要將學(xué)生的個(gè)人信息顯示出來

mysql> select * from stu left join exam on stu.id = exam.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
|    3 | kity | NULL |  NULL |
|    4 | nono | NULL |  NULL |
+------+------+------+-------+
4 rows in set (0.00 sec)

可以看到對(duì)應(yīng)沒有成績(jī)的，通過左外連接也查詢到了。

查詢所有成績(jī)，如果這個(gè)成績(jī)沒有對(duì)應(yīng)學(xué)生，也要將成績(jī)顯示出來

mysql> select * from stu right join exam on stu.id = exam.id;
+------+------+------+-------+
| id   | name | id   | grade |
+------+------+------+-------+
|    1 | jack |    1 |    56 |
|    2 | tom  |    2 |    76 |
| NULL | NULL |   11 |     8 |
+------+------+------+-------+
3 rows in set (0.01 sec)

這樣就能看到左外連接與右外連接做了一項(xiàng)什么工作了！

到此這篇關(guān)于MySQL從零開始了解數(shù)據(jù)庫開發(fā)之復(fù)合查詢的文章就介紹到這了,更多相關(guān)mysql復(fù)合查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: