MySQL復(fù)合查詢(xún)操作實(shí)戰(zhàn)案例

更新時(shí)間：2023年08月11日 09:46:49 作者：終為nullptr

這篇文章主要給大家介紹了關(guān)于MySQL復(fù)合查詢(xún)操作的相關(guān)資料,MySQL復(fù)合查詢(xún)是指在一個(gè)SQL語(yǔ)句中使用多個(gè)查詢(xún)條件,以過(guò)濾和檢索數(shù)據(jù),需要的朋友可以參考下

前言

今天我們要介紹的內(nèi)容是復(fù)合查詢(xún)，在這之前介紹的都是對(duì)一張表的操作，但是在實(shí)際開(kāi)發(fā)中，對(duì)一張表的操作是滿足不了需求的，一般需要對(duì)多張表進(jìn)行操作。而復(fù)合查詢(xún)就為圍繞對(duì)對(duì)多張表的操作進(jìn)行介紹！

1.基本查詢(xún)回顧

在學(xué)習(xí)復(fù)合查詢(xún)之前，我們首先來(lái)對(duì)之前學(xué)習(xí)過(guò)的基本查詢(xún)做一個(gè)綜合性的回顧和練習(xí)，然后再來(lái)看看復(fù)合查詢(xún)的操作，下面我們就通過(guò)具體的案例進(jìn)行復(fù)習(xí)。

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

mysql> select ename,job,sal from emp where (sal > 500 or job='MANGER') and ename like 'J%'
    -> ;
+-------+---------+---------+
| ename | job     | sal     |
+-------+---------+---------+
| JONES | MANAGER | 2975.00 |
| JAMES | CLERK   |  950.00 |
+-------+---------+---------+
2 rows in set (0.00 sec)

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

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

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

mysql> select ename,sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;
+--------+----------+
| ename  | 年薪     |
+--------+----------+
| KING   | 60000.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| JONES  | 35700.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| ALLEN  | 19500.00 |
| TURNER | 18000.00 |
| MARTIN | 16400.00 |
| MILLER | 15600.00 |
| WARD   | 15500.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| SMITH  |  9600.00 |
+--------+----------+
14 rows in set (0.00 sec)

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

mysql> select ename,job from emp where sal = (select max(sal) from emp);
+-------+-----------+
| ename | job       |
+-------+-----------+
| KING  | PRESIDENT |
+-------+-----------+
1 row in set (0.00 sec)

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

mysql> select ename,sal from emp where sal > (select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

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

mysql> select deptno,format(avg(sal),2),max(sal) from emp group by deptno;
+--------+--------------------+----------+
| deptno | format(avg(sal),2) | max(sal) |
+--------+--------------------+----------+
|     10 | 2,916.67           |  5000.00 |
|     20 | 2,175.00           |  3000.00 |
|     30 | 1,566.67           |  2850.00 |
+--------+--------------------+----------+
3 rows in set (0.00 sec)

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

mysql> select deptno,avg(sal) 平均工資 from emp group by deptno having 平均工資 < 2000;
+--------+--------------+
| deptno | 平均工資     |
+--------+--------------+
|     30 |  1566.666667 |
+--------+--------------+
1 row in set (0.00 sec)

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

mysql> select job,count(*),avg(sal) 平均工資 from emp group by job;
+-----------+----------+--------------+
| job       | count(*) | 平均工資     |
+-----------+----------+--------------+
| ANALYST   |        2 |  3000.000000 |
| CLERK     |        4 |  1037.500000 |
| MANAGER   |        3 |  2758.333333 |
| PRESIDENT |        1 |  5000.000000 |
| SALESMAN  |        4 |  1400.000000 |
+-----------+----------+--------------+
5 rows in set (0.00 sec)

以上就是我們通過(guò)幾個(gè)具體的案例對(duì)之前學(xué)過(guò)的單表查詢(xún)操作做了簡(jiǎn)單回顧，下面我們就對(duì)多表查詢(xún)的操作進(jìn)行介紹：

2.多表查詢(xún)

實(shí)際開(kāi)發(fā)中往往數(shù)據(jù)來(lái)自不同的表，所以需要多表查詢(xún)。本節(jié)我們用一個(gè)簡(jiǎn)單的公司管理系統(tǒng)，有三張表EMP,DEPT,SALGRADE來(lái)演示如何進(jìn)行多表查詢(xún)。
案例：

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

因?yàn)樯厦娴臄?shù)據(jù)來(lái)自EMP和DEPT表，因此要聯(lián)合查詢(xún)

聯(lián)合查詢(xún)的理解：如下表所示

許多數(shù)據(jù)都是無(wú)效的，我們只需要emp表中的deptno = dept表中的deptno字段的記錄：

mysql> select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| JONES  | 2975.00 | RESEARCH   |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| CLARK  | 2450.00 | ACCOUNTING |
| SCOTT  | 3000.00 | RESEARCH   |
| KING   | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES      |
| ADAMS  | 1100.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| FORD   | 3000.00 | RESEARCH   |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
14 rows in set (0.00 sec)

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

mysql> select emp.ename,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno and dept.deptno = 10;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| CLARK  | 2450.00 | ACCOUNTING |
| KING   | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
3 rows in set (0.00 sec)

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

mysql> select ename,sal,grade from emp,salgrade where emp.sal between losal and hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

3.自連接

前面是我們是在不同的表中進(jìn)行查詢(xún)，而自連接是指在同一張表連接查詢(xún)(本質(zhì)是將多張表合并成一張表，然后在一張表中進(jìn)行查詢(xún))；

案例：

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

-- 使用到表的別名
--from emp leader, emp worker，給自己的表起別名，因?yàn)橐茸龅芽柗e，所以別名可以先識(shí)別
mysql> select leader.empno,leader.ename from emp leader,emp worker where leader.empno=worker.mgr and worker.ename='FORD';
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)

4.子查詢(xún)

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

4.1單行子查詢(xún)

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

mysql> select* from emp where deptno = (select deptno from emp where ename='SMITH');
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
| 007876 | ADAMS | CLERK   | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL |     20 |
| 007902 | FORD  | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+

4.2多行子查詢(xún)

返回多行記錄的子查詢(xún)；

案例：

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

mysql> select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno = 10) and deptno <> 10;
+-------+---------+---------+--------+
| ename | job     | sal     | deptno |
+-------+---------+---------+--------+
| JONES | MANAGER | 2975.00 |     20 |
| BLAKE | MANAGER | 2850.00 |     30 |
| SMITH | CLERK   |  800.00 |     20 |
| ADAMS | CLERK   | 1100.00 |     20 |
| JAMES | CLERK   |  950.00 |     30 |
+-------+---------+---------+--------+
5 rows in set (0.00 sec)

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

mysql> select ename,sal,deptno from emp where sal > all (select 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 |
+-------+---------+--------+

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

mysql> select ename,sal,deptno from emp where sal > any(select 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)

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本人

mysql> select ename from emp where (deptno,job) = (select deptno,job from emp where ename = 'SMITH') and ename <> 'SMITH';
+-------+
| ename |
+-------+
| ADAMS |
+-------+
1 row in set (0.00 sec)

4.4在from子句中使用子查詢(xún)

子查詢(xún)語(yǔ)句出現(xiàn)在from子句中。這里要用到數(shù)據(jù)查詢(xún)的技巧，把一個(gè)子查詢(xún)當(dāng)做一個(gè)臨時(shí)表使用。

案例：

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

mysql> select ename,deptno,sal,平均工資 from emp,(select avg(sal) 平均工資,deptno dt from emp group by deptno) tmp where emp.sal > tmp.平均工資 an
nd emp.deptno=tmp.dt;
+-------+--------+---------+--------------+
| ename | deptno | sal     | 平均工資     |
+-------+--------+---------+--------------+
| KING  |     10 | 5000.00 |  2916.666667 |
| JONES |     20 | 2975.00 |  2175.000000 |
| SCOTT |     20 | 3000.00 |  2175.000000 |
| FORD  |     20 | 3000.00 |  2175.000000 |
| ALLEN |     30 | 1600.00 |  1566.666667 |
| BLAKE |     30 | 2850.00 |  1566.666667 |
+-------+--------+---------+--------------+

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

mysql> select emp.ename,emp.sal,emp.deptno,最高工資 from emp,(select max(sal) 最高工資 ,deptno from emp group by deptno) tmp where emp.deptno=tmp..deptno and emp.sal=tmp.最高工資;
+-------+---------+--------+--------------+
| ename | sal     | deptno | 最高工資     |
+-------+---------+--------+--------------+
| BLAKE | 2850.00 |     30 |      2850.00 |
| SCOTT | 3000.00 |     20 |      3000.00 |
| KING  | 5000.00 |     10 |      5000.00 |
| FORD  | 3000.00 |     20 |      3000.00 |
+-------+---------+--------+--------------+
4 rows in set (0.00 sec)

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

方法1 ：使用多表

mysql> select dept.dname,dept.deptno,dept.loc,count(*) '部門(mén)人數(shù)' from emp,dept where emp.deptno=dept.deptno
    -> group by dept.dname,dept.deptno,dept.loc;
+------------+--------+----------+--------------+
| dname      | deptno | loc      | 部門(mén)人數(shù)     |
+------------+--------+----------+--------------+
| ACCOUNTING |     10 | NEW YORK |            3 |
| RESEARCH   |     20 | DALLAS   |            5 |
| SALES      |     30 | CHICAGO  |            6 |
+------------+--------+----------+--------------+
3 rows in set (0.00 sec)

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

mysql> select dept.deptno,dname,loc,部門(mén)人數(shù) from dept,(select count(*) 部門(mén)人數(shù),deptno from emp group by deptno) tmp where dept.deptno=tmp.deptno
o;
+--------+------------+----------+--------------+
| deptno | dname      | loc      | 部門(mén)人數(shù)     |
+--------+------------+----------+--------------+
|     10 | ACCOUNTING | NEW YORK |            3 |
|     20 | RESEARCH   | DALLAS   |            5 |
|     30 | SALES      | CHICAGO  |            6 |
+--------+------------+----------+--------------+
3 rows in set (0.00 sec)

5.合并查詢(xún)

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

1.union

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

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

mysql> select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where ename='MANAGER';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
+-------+---------+-----------+
5 rows in set (0.00 sec)

2.union all

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

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

mysql> select ename, sal, job from emp where sal>2500 union all select ename, sal, job from emp where job='MANAGER';
+-------+---------+-----------+
| ename | sal     | job       |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| SCOTT | 3000.00 | ANALYST   |
| KING  | 5000.00 | PRESIDENT |
| FORD  | 3000.00 | ANALYST   |
| JONES | 2975.00 | MANAGER   |
| BLAKE | 2850.00 | MANAGER   |
| CLARK | 2450.00 | MANAGER   |
+-------+---------+-----------+
8 rows in set (0.00 sec)