MySQL復合查詢操作實戰(zhàn)案例
更新時間：2023年08月11日 09:46:49 作者：終為nullptr
這篇文章主要給大家介紹了關于MySQL復合查詢操作的相關資料,MySQL復合查詢是指在一個SQL語句中使用多個查詢條件,以過濾和檢索數(shù)據(jù),需要的朋友可以參考下
前言
今天我們要介紹的內容是復合查詢，在這之前介紹的都是對一張表的操作，但是在實際開發(fā)中，對一張表的操作是滿足不了需求的，一般需要對多張表進行操作。而復合查詢就為圍繞對對多張表的操作進行介紹！
1.基本查詢回顧
在學習復合查詢之前，我們首先來對之前學習過的基本查詢做一個綜合性的回顧和練習，然后再來看看復合查詢的操作，下面我們就通過具體的案例進行復習。
a.查詢工資高于500或崗位為MANAGER的雇員，同時還要滿足他們的姓名首字母為大寫的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.按照部門號升序而雇員的工資降序排序
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.使用年薪進行降序排序
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.顯示每個部門的平均工資和最高工資
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的部門號和它的平均工資
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)
以上就是我們通過幾個具體的案例對之前學過的單表查詢操作做了簡單回顧，下面我們就對多表查詢的操作進行介紹：
2.多表查詢
實際開發(fā)中往往數(shù)據(jù)來自不同的表，所以需要多表查詢。本節(jié)我們用一個簡單的公司管理系統(tǒng)，有三張表EMP,DEPT,SALGRADE來演示如何進行多表查詢。
案例：
a.顯示雇員名、雇員工資以及所在部門的名字
因為上面的數(shù)據(jù)來自EMP和DEPT表，因此要聯(lián)合查詢
聯(lián)合查詢的理解：如下表所示
許多數(shù)據(jù)都是無效的，我們只需要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.顯示部門號為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.顯示各個員工的姓名，工資，及工資級別
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.自連接
前面是我們是在不同的表中進行查詢，而自連接是指在同一張表連接查詢(本質是將多張表合并成一張表，然后在一張表中進行查詢)；
案例：
顯示員工FORD的上級領導的編號和姓名（mgr是員工領導的編號--empno）
-- 使用到表的別名 --from emp leader, emp worker，給自己的表起別名，因為要先做笛卡爾積，所以別名可以先識別 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語句中的select語句，也叫嵌套查詢
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關鍵字；查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含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關鍵字；顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號
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關鍵字；顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（包含自己部門
的員工）
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ù)，都是針對單列而言的，而多列子查詢則是指查詢返回多個列數(shù)據(jù)的子查詢語句
案例：
查詢和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子句中使用子查詢
子查詢語句出現(xiàn)在from子句中。這里要用到數(shù)據(jù)查詢的技巧，把一個子查詢當做一個臨時表使用。
案例：
顯示每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資
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 | +-------+--------+---------+--------------+
查找每個部門工資最高的人的姓名、工資、部門、最高工資
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)
顯示每個部門的信息（部門名，編號，地址）和人員數(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.合并查詢
在實際應用中，為了合并多個select的執(zhí)行結果，可以使用集合操作符 union，union all
1.union
該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中的重復行。
案例：將工資大于2500或職位是MANAGER的人找出來
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
該操作符用于取得兩個結果集的并集。當使用該操作符時，不會去掉結果集中的重復行。
案例：將工資大于25000或職位是MANAGER的人找出來
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)
總結
本篇文章為大家介紹了多表查詢的幾種方式，掌握了這些多表查詢的方式之后，可以滿足更多實際開發(fā)中的需求！
到此這篇關于MySQL復合查詢操作的文章就介紹到這了,更多相關MySQL復合查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
Mysql中自定義函數(shù)的創(chuàng)建和執(zhí)行方式
這篇文章主要介紹了Mysql中自定義函數(shù)的創(chuàng)建和執(zhí)行方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-03-03
limit在mysql語句中使用的頻率非常高，一般分頁查詢都會使用到limit語句，本文章向碼農們介紹mysql limit 分頁的用法與注意事項，需要的朋友可以參考下2016-12-12
這篇文章主要介紹了MySql設置指定用戶數(shù)據(jù)庫查看查詢權限,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-10-10
這篇文章主要為大家詳細介紹了MySQL索引的作用，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下，希望能夠給你帶來幫助2022-03-03
這篇文章主要介紹了分析MySQL中索引引引發(fā)的CPU負載飆升的問題,文中提到了獨立索引所帶來的巨大CPU負擔,以提醒在MySQL中使用索引要注意CPU負載的問題,需要的朋友可以參考下2015-05-05
雙主可以在一定程度上保證主庫的高可用,在一臺主庫down掉之后,可以在極短的時間內切換到另一臺主庫上,減少了主從同步給線上主庫帶來的壓力,這篇文章主要給大家介紹了關于Mysql雙主配置的詳細步驟,需要的朋友可以參考下2022-06-06
SELECT語句是SQL中最常用的語句。它的用途是從一個或多個表中檢索信息。為了使用SELECT檢索表數(shù)據(jù)，必須至少給出兩條信息：想選擇什么、從什么地方選擇2023-02-02
這篇文章主要介紹了mysql 觸發(fā)器用法實例詳解的相關資料,需要的朋友可以參考下2017-06-06
本文主要介紹了mysql的虛擬表(DUAL)的介紹及使用場景,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-04-04
這篇文章主要介紹了MySQL創(chuàng)建索引需要了解的知識，幫助大家更正確的使用MySQL的索引，感興趣的朋友可以了解下2021-04-04