「前言」文章內(nèi)容大致是對(duì)MySQL復(fù)合查詢的學(xué)習(xí)。
「歸屬專欄」MySQL
「主頁(yè)鏈接」個(gè)人主頁(yè)
「筆者」楓葉先生(fy)
一、基本查詢回顧
前面篇章講解的mysql表的查詢都是對(duì)一張表進(jìn)行查詢，在實(shí)際開(kāi)發(fā)中這遠(yuǎn)遠(yuǎn)不夠，下面將講解復(fù)合查詢，首先回顧一下基本的查詢。
使用的數(shù)據(jù)庫(kù)是之前篇章的雇員信息表，員工表（emp）、部門表（dept）和工資等級(jí)表（salgrade）
查詢工資高于500或崗位為MANAGER的雇員，同時(shí)還要滿足他們的姓名首字母為大寫的J
mysql> select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';
按照部門號(hào)升序而雇員的工資降序排序
mysql> select * from emp order by deptno asc, sal desc;
使用年薪進(jìn)行降序排序
mysql> select ename, sal*12+ifnull(comm, 0) as 年薪 from emp order by 年薪 desc;
注：
- 由于NULL與任何值做計(jì)算得到的結(jié)果都是NULL，因此在計(jì)算年薪時(shí)不能直接用月薪的12倍加上每個(gè)員工的獎(jiǎng)金，這樣可能導(dǎo)致得到的年薪為NULL值。
- 在計(jì)算每個(gè)員工的年薪時(shí)，應(yīng)該通過(guò)ifnull函數(shù)判斷員工的獎(jiǎng)金是否為NULL，如果不為NULL則ifnull函數(shù)返回員工的獎(jiǎng)金，如果為NULL則ifnull函數(shù)返回0，避免讓NULL值參與計(jì)算
顯示工資最高的員工的名字和工作崗位
解決該問(wèn)題需要進(jìn)行兩次查詢
此外，這種問(wèn)題還可以使用子查詢，將兩句查詢語(yǔ)句合并起來(lái)，需要將第一次查詢的SQL語(yǔ)句用括號(hào)括起來(lái)。
mysql> select ename, job from emp where sal = (select max(sal) from emp);
顯示工資高于平均工資的員工信息
也是使用子查詢解決
mysql> select * from emp where sal > (select avg(sal) from emp);
顯示每個(gè)部門的平均工資和最高工資
在group by子句中指明按照部門號(hào)進(jìn)行分組，在select語(yǔ)句中使用avg函數(shù)和max函數(shù)，分別查詢每個(gè)部門的平均工資和最高工資
mysql> select deptno, format(avg(sal), 2) 平均, max(sal) 最高 from emp group by deptno;
顯示平均工資低于2000的部門號(hào)和它的平均工資
在group by子句中指明按照部門號(hào)進(jìn)行分組，在select語(yǔ)句中使用avg函數(shù)查詢每個(gè)部門的平均工資，在having子句中指明篩選條件為平均工資小于2000
mysql> select deptno, avg(sal) 平均工資 from emp group by deptno having 平均工資 < 2000;
顯示每種崗位的雇員總數(shù)，平均工資
mysql> select job, count(*) 人數(shù), format(avg(sal), 2) 平均工資 from emp group by job;
二、多表查詢
上面的基礎(chǔ)查詢都是在一張表的基礎(chǔ)上進(jìn)行的查詢，實(shí)際開(kāi)發(fā)中往往數(shù)據(jù)來(lái)自不同的表，所以需要多表查詢。
- 在進(jìn)行多表查詢時(shí)，只需要將多張表的表名依次放到from子句之后，用逗號(hào)隔開(kāi)即可，這時(shí)MySQL將會(huì)對(duì)給定的這多張表取笛卡爾積，作為多表查詢的初始數(shù)據(jù)源
- 多表查詢的本質(zhì)，就是對(duì)給定的多張表取笛卡爾積，然后在產(chǎn)生的新表進(jìn)行查詢
笛卡爾積是指給定兩個(gè)集合A和B，其中A中的每個(gè)元素和B中的每個(gè)元素都可以組成一個(gè)有序?qū)?，這些有序?qū)Φ募暇褪茿和B的笛卡爾積。
例如，員工表和部門表進(jìn)行笛卡爾積
員工表：
部門表：
兩張表進(jìn)行笛卡爾積
mysql> select * from emp, dept;
員工表和部門表的笛卡爾積由兩部分組成，前半部分是員工表的列信息，后半部分是部門表的列信息
對(duì)員工表和部門表取笛卡爾積時(shí)，會(huì)先從員工表中選出一條記錄與部門表中的所有記錄進(jìn)行組合，然后再?gòu)膯T工表中選出一條記錄與部門表中的所有記錄進(jìn)行組合，以此類推，最終得到一張新表
對(duì)多張表取笛卡爾積后得到的數(shù)據(jù)并不都是有意義的。
比如對(duì)員工表和部門表取笛卡爾積時(shí)，員工表中的每一個(gè)員工信息都會(huì)和部門表中的每一個(gè)部門信息進(jìn)行組合，而實(shí)際一個(gè)員工只有和自己所在的部門信息進(jìn)行組合才是有意義的，因此需要從笛卡爾積產(chǎn)生的新表篩選出員工的部門號(hào)和部門的編號(hào)相等記錄。
注意：進(jìn)行笛卡爾積的多張表中可能會(huì)存在相同的列名，這時(shí)在選中列名時(shí)需要通過(guò)表名.列名的方式進(jìn)行指明，如果有重復(fù)的不指明確切一列，就會(huì)報(bào)錯(cuò)。
顯示雇員名、雇員工資以及所在部門的名字
從題意可以看出，部門名只有dept表中才有，其他數(shù)據(jù)來(lái)源于emp表，即數(shù)據(jù)來(lái)自EMP和DEPT表，因此要聯(lián)合查詢，即多表查詢
mysql> select emp.ename, emp.sal, dept.deptno from emp, dept where emp.deptno = dept.deptno;
顯示部門號(hào)為10的部門名，員工名和工資
部門名只有部門表中才有，員工名和員工工資只有員工表中才有，因此需要同時(shí)使用員工表和部門表進(jìn)行多表查詢，在where子句中指明篩選條件為員工的部門號(hào)等于部門編號(hào)（篩選符合條件的信息）
mysql> select ename, sal, emp.deptno, dname from emp, dept where emp.deptno = dept.deptno and dept.deptno = 10;
注意：在篩選部門號(hào)等于10的部門時(shí)，可以使用員工表中的部門號(hào)，也可以使用部門表中的部門編號(hào)，因?yàn)閮闪卸际且粯拥摹?/p>
顯示各個(gè)員工的姓名，工資，及工資級(jí)別
員工名和工資只有員工表中才有，而工資級(jí)別只有工資等級(jí)表中才有，因此需要同時(shí)使用員工表和工資等級(jí)表進(jìn)行多表查詢，在where子句中指明篩選條件為員工的工資在losal和hisal之間的記錄
mysql> select ename, sal, grade from emp, salgrade where sal between losal and hisal;
三、自連接
自連接是指在同一張表進(jìn)行連接查詢，也就是說(shuō)我們不僅可以對(duì)不同表進(jìn)行取笛卡爾積，也可以對(duì)同一張表取笛卡爾積。
顯示員工FORD的上級(jí)領(lǐng)導(dǎo)的編號(hào)和姓名
可以使用子查詢，先對(duì)員工表進(jìn)行查詢得到FORD的領(lǐng)導(dǎo)的編號(hào)，然后再根據(jù)領(lǐng)導(dǎo)的編號(hào)對(duì)員工表進(jìn)行查詢得到FORD領(lǐng)導(dǎo)的姓名
mysql> select empno, ename from emp where empno = (select mgr from emp where ename = 'FORD');
也可以使用多表查詢（自查詢），因?yàn)閱T工表中的mgr字段能夠?qū)⒈碇袉T工的信息和員工領(lǐng)導(dǎo)的信息關(guān)聯(lián)起來(lái)。
mysql> select leader.empno, leader.ename from emp leader, emp worder where leader.empno = worder.mgr and worder.ename = 'FORD';
注 由于自連接是對(duì)同一張表取笛卡爾積，因此在自連接時(shí)至少需要給一張表取別名，否則無(wú)法區(qū)分這兩張表中的列。
四、子查詢
- 子查詢是指嵌入在其他SQL語(yǔ)句中的查詢語(yǔ)句，也叫嵌套查詢
- 子查詢可分為單行子查詢、多行子查詢、多列子查詢，以及在from子句中使用的子查詢
4.1 單行子查詢
單行子查詢，是指返回單行單列數(shù)據(jù)的子查詢
顯示SMITH同一部門的員工
在子查詢中查詢SMITH所在的部門號(hào)，在where子句中指明篩選條件為員工部門號(hào)等于子查詢返回的部門號(hào)
mysql> select * from emp where deptno = (select deptno from emp where ename = 'SMITH');
此外，解決該問(wèn)題也可以使用自連接
4.2 多行子查詢
多行子查詢，是指返回多行單列數(shù)據(jù)的子查詢
使用
in關(guān)鍵字；查詢和10號(hào)部門的工作崗位相同的雇員的名字，崗位，工資，部門號(hào)，但是不包含10自己的
先查詢10號(hào)部門有哪些工作崗位，在查詢時(shí)要對(duì)結(jié)果進(jìn)行去重，因?yàn)?0號(hào)部門的某些員工的工作崗位可能是相同的
然后將上述查詢作為子查詢，在查詢員工表時(shí)在where子句中使用in關(guān)鍵字，in關(guān)鍵字用于判斷員工的工作崗位是子查詢得到的若干崗位中的一個(gè)
mysql> select ename, job, deptno from emp
-> where job in (select distinct job from emp where deptno=10) and deptno<>10;
實(shí)用
all關(guān)鍵字；顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號(hào)
先查詢30號(hào)部門員工的工資，進(jìn)行去重
將上述查詢作為子查詢，在查詢員工表時(shí)在where子句中使用all關(guān)鍵字，all關(guān)鍵字用于判斷員工的工資是否高于子查詢得到的所有工資
mysql> select ename, sal, deptno from emp where sal > all(select distinct sal from emp where deptno=20);
使用
any關(guān)鍵字；顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號(hào)（包含自己部門的員工）
先查詢30號(hào)部門員工的工資，然后在查詢員工表時(shí)在where子句中使用any關(guān)鍵字，判斷員工的工資是否高于子查詢的得到的工資中的某一個(gè)
mysql> select ename, sal, deptno from emp where sal > any(select distinct sal from emp where deptno=30);
4.3 多列子查詢
單行子查詢是指子查詢只返回單列，單行數(shù)據(jù)；多行子查詢是指返回單列多行數(shù)據(jù)，都是針對(duì)單列而言的，而多列子查詢則是指查詢返回多個(gè)列數(shù)據(jù)的子查詢語(yǔ)句
查詢和SMITH的部門和崗位完全相同的所有雇員，不含SMITH本人
先查詢SMITH所在部門的部門號(hào)和他的崗位，然后將上述查詢作為子查詢
mysql> select * from emp where (deptno,job) = (select deptno, job from emp where ename = 'SMITH') and ename <> 'SMITH';
注：
- 多列子查詢得到的結(jié)果是多列數(shù)據(jù)，在比較多列數(shù)據(jù)時(shí)需要將待比較的多個(gè)列用圓括號(hào)括起來(lái)
- 多列子查詢返回的如果是多行數(shù)據(jù)，在篩選數(shù)據(jù)時(shí)也可以使用
in、all和any關(guān)鍵字
4.4 在from子句中使用子查詢
- 子查詢語(yǔ)句不僅可以出現(xiàn)在where子句中，也可以出現(xiàn)在from子句中
- 子查詢語(yǔ)句出現(xiàn)from子句中，其查詢結(jié)果將會(huì)被當(dāng)作一個(gè)臨時(shí)表使用
顯示每個(gè)高于自己部門平均工資的員工的姓名、部門、工資、平均工資
先查詢每個(gè)部門的平均工資，這張表當(dāng)做臨時(shí)表使用
然后對(duì)員工表和上述的查詢結(jié)果進(jìn)行多表查詢，在where子句中指明篩選條件為員工的部門號(hào)等于臨時(shí)表中的部門號(hào)，并且員工的工資大于臨時(shí)表中的平均工資
mysql> select ename, emp.deptno, sal, 平均工資 from emp, (select deptno, avg(sal) 平均工資 from emp group by deptno) tmp
-> where emp.deptno=tmp.deptno and sal > 平均工資;
注意：在from子句中使用子查詢時(shí)，必須給子查詢得到的臨時(shí)表取一個(gè)別名，否則查詢將會(huì)出錯(cuò)
查找每個(gè)部門工資最高的人的姓名、工資、部門、最高工資
先查詢每個(gè)部門的最高工資
然后對(duì)員工表和上述的查詢結(jié)果進(jìn)行取笛卡爾積，在where子句中指明篩選條件為員工的部門號(hào)等于臨時(shí)表中的部門號(hào)，并且員工的工資等于臨時(shí)表中的最高工資
mysql> select ename, sal, emp.deptno, 最高工資 from emp, (select max(sal) 最高工資, deptno from emp group by deptno) tmp
-> where emp.deptno=tmp.deptno and sal=最高工資;
顯示每個(gè)部門的信息（部門名，編號(hào)，地址）和人員數(shù)量
按照部門號(hào)進(jìn)行分組，分別查詢每個(gè)部門的人員數(shù)量
述查詢作為子查詢放在from子句中，然后對(duì)員工表和臨時(shí)表取笛卡爾積，在where子句中指明篩選條件為員工的部門號(hào)等于臨時(shí)表中的部門號(hào)即可
mysql> select dname, dept.deptno, loc, 部門人數(shù) from dept, (select deptno, count(*) 部門人數(shù) from emp group by deptno)
-> tmp where dept.deptno = tmp.deptno;
上述也可以只使用多表查詢解決
mysql> select dname, dept.deptno, loc, count(*) 人數(shù) from emp, dept
-> where emp.deptno = dept.deptno
-> group by dept.deptno, dname, loc;
五、合并查詢
合并查詢，是指將多個(gè)查詢結(jié)果進(jìn)行合并，關(guān)鍵字union和union all
- union用于取得兩個(gè)查詢結(jié)果的并集，union會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行
- union all也用于取得兩個(gè)查詢結(jié)果的并集，但union all不會(huì)去掉結(jié)果集中的重復(fù)行
將工資大于2500或職位是MANAGER的人找出來(lái)
查詢工資大于2500的員工，查詢職位是MANAGER的員工
可以使用or操作符將where子句中的兩個(gè)條件關(guān)聯(lián)起來(lái)
也可以使用union將上述的兩條查詢SQL連接起來(lái)，這時(shí)將會(huì)得到兩次查詢結(jié)果的并集，并且會(huì)對(duì)合并后的結(jié)果進(jìn)行去重文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-673319.html
mysql> select ename, job, sal from emp where sal > 2500 union
-> select ename, job, sal from emp where sal > 2500 or job = 'MANAGER';
可以使用union all，結(jié)果是不去重
注意：待合并的兩個(gè)查詢結(jié)果的列的數(shù)量必須一致，否則無(wú)法合并
--------------------- END ----------------------文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-673319.html
「 作者 」 楓葉先生
「 更新 」 2023.8.25
「 聲明 」 余之才疏學(xué)淺，故所撰文疏漏難免，
或有謬誤或不準(zhǔn)確之處，敬請(qǐng)讀者批評(píng)指正。
到了這里，關(guān)于【MySQL系列】MySQL復(fù)合查詢的學(xué)習(xí) _ 多表查詢 | 自連接 | 子查詢 | 合并查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
