MySQL復(fù)合查詢操作實(shí)戰(zhàn)案例
更新時(shí)間：2023年08月11日 09:46:49 作者：終為nullptr
這篇文章主要給大家介紹了關(guān)于MySQL復(fù)合查詢操作的相關(guān)資料,MySQL復(fù)合查詢是指在一個(gè)SQL語(yǔ)句中使用多個(gè)查詢條件,以過(guò)濾和檢索數(shù)據(jù),需要的朋友可以參考下
前言
今天我們要介紹的內(nèi)容是復(fù)合查詢，在這之前介紹的都是對(duì)一張表的操作，但是在實(shí)際開(kāi)發(fā)中，對(duì)一張表的操作是滿足不了需求的，一般需要對(duì)多張表進(jìn)行操作。而復(fù)合查詢就為圍繞對(duì)對(duì)多張表的操作進(jìn)行介紹！
1.基本查詢回顧
在學(xué)習(xí)復(fù)合查詢之前，我們首先來(lái)對(duì)之前學(xué)習(xí)過(guò)的基本查詢做一個(gè)綜合性的回顧和練習(xí)，然后再來(lái)看看復(fù)合查詢的操作，下面我們就通過(guò)具體的案例進(jìn)行復(fù)習(xí)。
a.查詢工資高于500或崗位為MANAGER的雇員，同時(shí)還要滿足他們的姓名首字母為大寫的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.按照部門號(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è)部門的平均工資和最高工資
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的部門號(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ò)的單表查詢操作做了簡(jiǎn)單回顧，下面我們就對(duì)多表查詢的操作進(jìn)行介紹：
2.多表查詢
實(shí)際開(kāi)發(fā)中往往數(shù)據(jù)來(lái)自不同的表，所以需要多表查詢。本節(jié)我們用一個(gè)簡(jiǎn)單的公司管理系統(tǒng)，有三張表EMP,DEPT,SALGRADE來(lái)演示如何進(jìn)行多表查詢。
案例：
a.顯示雇員名、雇員工資以及所在部門的名字
因?yàn)樯厦娴臄?shù)據(jù)來(lái)自EMP和DEPT表，因此要聯(lián)合查詢
聯(liá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.顯示部門號(hào)為10的部門名，員工名和工資
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)行查詢，而自連接是指在同一張表連接查詢(本質(zhì)是將多張表合并成一張表，然后在一張表中進(jì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.子查詢
子查詢是指嵌入在其他sql語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢
4.1單行子查詢
顯示SMITH同一部門的員工
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多行子查詢
返回多行記錄的子查詢；
案例：
使用in關(guān)鍵字；查詢和10號(hào)部門的工作崗位相同的雇員的名字，崗位，工資，部門號(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)鍵字；顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號(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)鍵字；顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號(hào)（包含自己部門
的員工）
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多列子查詢
單行子查詢是指子查詢只返回單列，單行數(shù)據(jù)；多行子查詢是指返回單列多行數(shù)據(jù)，都是針對(duì)單列而言的，而多列子查詢則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語(yǔ)句
案例：
查詢和SMITH的部門和崗位完全相同的所有雇員，不含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子句中使用子查詢
子查詢語(yǔ)句出現(xiàn)在from子句中。這里要用到數(shù)據(jù)查詢的技巧，把一個(gè)子查詢當(dāng)做一個(gè)臨時(shí)表使用。
案例：
顯示每個(gè)高于自己部門平均工資的員工的姓名、部門、工資、平均工資
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è)部門工資最高的人的姓名、工資、部門、最高工資
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è)部門的信息（部門名，編號(hào)，地址）和人員數(shù)量
方法1 ：使用多表
mysql> select dept.dname,dept.deptno,dept.loc,count(*) '部門人數(shù)' from emp,dept where emp.deptno=dept.deptno
-> group by dept.dname,dept.deptno,dept.loc;
+------------+--------+----------+--------------+
| dname | deptno | loc | 部門人數(shù) |
+------------+--------+----------+--------------+
| ACCOUNTING | 10 | NEW YORK | 3 |
| RESEARCH | 20 | DALLAS | 5 |
| SALES | 30 | CHICAGO | 6 |
+------------+--------+----------+--------------+
3 rows in set (0.00 sec)方法2 ： 使用子查詢
mysql> select dept.deptno,dname,loc,部門人數(shù) from dept,(select count(*) 部門人數(shù),deptno from emp group by deptno) tmp where dept.deptno=tmp.deptno o; +--------+------------+----------+--------------+ | deptno | dname | loc | 部門人數(shù) | +--------+------------+----------+--------------+ | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | +--------+------------+----------+--------------+ 3 rows in set (0.00 sec)
5.合并查詢
在實(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)
總結(jié)
本篇文章為大家介紹了多表查詢的幾種方式，掌握了這些多表查詢的方式之后，可以滿足更多實(shí)際開(kāi)發(fā)中的需求！
到此這篇關(guān)于MySQL復(fù)合查詢操作的文章就介紹到這了,更多相關(guān)MySQL復(fù)合查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要為大家分享了Win7下安裝MySQL5.7.16過(guò)程的筆記，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
重置MySQL中表中自增列的初始值的實(shí)現(xiàn)方法
這篇文章主要介紹了重置MySQL中表中自增列的初始值的實(shí)現(xiàn)方法的相關(guān)資料,需要的朋友可以參考下2017-03-03
如何修改MYSQL5.7.17數(shù)據(jù)庫(kù)存儲(chǔ)文件的路徑
在搭建華為云服務(wù)器的時(shí)候遇到點(diǎn)問(wèn)題，查看了網(wǎng)上好多的帖子都沒(méi)能解決，不知道有沒(méi)有跟我遇到一樣問(wèn)題的老鐵，我就把我的解決辦法分享給大家，希望能夠幫助各位老鐵2023-05-05
本文主要介紹了講解Mysql OnlineDDL的算法,文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2025-09-09
在實(shí)際的生產(chǎn)環(huán)境中,由單臺(tái)Mysql作為獨(dú)立的數(shù)據(jù)庫(kù)是完全不能滿足實(shí)際需求的,一般都是通過(guò)主從復(fù)制的方式來(lái)同步數(shù)據(jù),再通過(guò)讀寫分離（來(lái)提升數(shù)據(jù)庫(kù)的并發(fā)負(fù)載能力,這篇文章主要給大家介紹了關(guān)于MySQL主從復(fù)制與讀寫分離的相關(guān)資料,需要的朋友可以參考下2021-11-11
mysql查線上數(shù)據(jù)注意數(shù)據(jù)庫(kù)的隔離級(jí)別
數(shù)據(jù)庫(kù)的隔離級(jí)別關(guān)乎事務(wù)對(duì)其他并發(fā)事務(wù)的可見(jiàn)性及其對(duì)數(shù)據(jù)庫(kù)的影響,隔離級(jí)別的選擇決定了并發(fā)性能和數(shù)據(jù)一致性的平衡,SQL標(biāo)準(zhǔn)定義了四種隔離級(jí)別,每種級(jí)別都有不同的應(yīng)用場(chǎng)景和防止并發(fā)問(wèn)題的能力,感興趣的可以了解一下2024-10-10
MySQL是當(dāng)前最為常用的關(guān)系型數(shù)據(jù)庫(kù)之一,它的出色性能和可靠性使得它成為了許多互聯(lián)網(wǎng)企業(yè)的首選,但是,如何讓MySQL支撐億級(jí)流量的挑戰(zhàn),成為了很多技術(shù)人員需要面對(duì)和解決的問(wèn)題,需要的朋友可以參考下2023-10-10
Navicat Premium操作MySQL數(shù)據(jù)庫(kù)(執(zhí)行sql語(yǔ)句)
這篇文章主要介紹了Navicat Premium操作MySQL數(shù)據(jù)庫(kù)(執(zhí)行sql語(yǔ)句),文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
mysql8如何設(shè)置不區(qū)分大小寫ubuntu20
這篇文章主要介紹了mysql8如何設(shè)置不區(qū)分大小寫ubuntu20問(wèn)題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-05-05
MySQL實(shí)現(xiàn)字符到DATE和TIMESTAMP的相互轉(zhuǎn)換的方法
本文詳細(xì)介紹了MySQL中日期、時(shí)間戳和字符串之間的相互轉(zhuǎn)換方法,包括使用STR_TO_DATE()、DATE_FORMAT()、DATE()、UNIX_TIMESTAMP()、FROM_UNIXTIME()、CAST、CONVERT以及CONVERT_TZ等函數(shù)進(jìn)行轉(zhuǎn)換,需要的朋友可以參考下2026-03-03