緒論?：
本章是MySQL篇中，非常實用性的篇章，相信在實際工作中對于表的查詢，很多時候會涉及多表的查詢，在多表查詢的時候光是前面的篇章可能無法完成，所以本章來了，本章將主要結(jié)合：子查詢 + 笛卡爾積 的方式來解決多表查詢問題，下一章將更新MySQL索引敬請期待~
————————
早關(guān)注不迷路，話不多說安全帶系好，發(fā)車啦（建議電腦觀看）。

復(fù)合查詢

前面我們講解的mysql表的查詢都是對一張表進行查詢，在實際開發(fā)中這遠遠不夠，所以復(fù)合查詢就是同時查詢多個表中的內(nèi)容。

1. 回顧查詢基本操作

下面將通過幾個具體情況來進行回顧

查詢工資高于500或崗位為MANAGER的雇員，同時還要滿足他們的姓名首字母為大寫的J（where、or/and 、like）

分析查詢目標：

工資高于500 / 崗位為MANAGER的雇員（查詢）select * from emp where sal > 500 or job = 'MANAGER'姓名首字母為大寫的... and ename lik 'J%'; / and substring(ename,1,1) = 'J'
以下表數(shù)據(jù)來操作：

select * from emp where sal > 500 or job = 'MANAGER' and ename lik 'J%';  / and substring(ename,1,1) = 'J'

按照部門號升序而雇員的工資降序排序（order by asc/desc）

比較簡單就不分析了，其中要注意的就是對于要進行排序的字段來說：那個在前面那個排序 優(yōu)先級就較高

select * from emp order by deptno asc,sal desc;

使用年薪進行降序排序（select 自定義添加新列、ifnull、order by）

年薪 = 月薪sal * 12 + 獎金comm
獲取某個人并創(chuàng)建新列（在select后面直接創(chuàng)建要求并可以創(chuàng)建別名）

其中任何值和NULL運算都會變成NULL（此處該人的comm為NULL）

此時就要將這種情況避免（使用ifnull）

select sal*12+ifnull(comm,0) 年薪 from emp;

在加上名稱和月薪，獎金，年薪，這樣跟好看

在進行排序得到年薪的降序（order by desc）

顯示工資最高的員工的名字和工作崗位（select 內(nèi)部允許使用 嵌套select、max函數(shù)） 顯示工資最高 select max(sal) from emp; 該情況是在表中不存在的所以需要提前篩選出來！員工的名字和工作崗位 select ename job where sal=..

select ename,job where sal=(select max(sal) from emp);`

顯示工資高于平均工資的員工信息（select嵌套 + avg函數(shù)） 平均工資select avg(sal) 平均工資 from emp ;
方法類似同上：

select * from emp where sal > (select avg(sal) from emp);`

group by 分組

GROUP BY： 子句用于將查詢結(jié)果按照指定的列進行分組，通常與聚合函數(shù)一起使用。

顯示每個部門的平均工資和最高工資（format）

平均工資、最高工資select max(sal) ,avg(sal) from emp；

每個部門（對應(yīng)著需要分組）goup by deptno

select deptno, max(sal) ,avg(sal) from emp group by deptno;

在使用format設(shè)置一下小數(shù)點：

having

HAVING 子句：用于對分組后的結(jié)果進行條件過濾，類似于 WHERE，但專門用于分組后的篩選。

顯示平均工資低于2000的部門號和它的平均工資

平均工資（同上）平均工資低于2000的部門號（分組）

就需要對分組之后的數(shù)據(jù)再做篩選（having）出小于2000的部門號：

顯示每種崗位的雇員總數(shù)，平均工資 每種崗位（group分組）雇員總數(shù)，平均工資（篩選內(nèi)容）
2 多表查詢（多表笛卡爾積）

結(jié)合實例，邊練習(xí)邊了解邊快速上手學(xué)習(xí)

1. 顯示部門號為10的部門名，員工名和工資

因為上面的數(shù)據(jù)（雇員名、雇員工資以及所在部門和部門號為10的）需要來自EMP和DEPT兩張表，因此要聯(lián)合查詢
EMP（需要ename、sal）

DEPT（需要dname）：

將他們直接使用select結(jié)合：

對兩張表直接進行整合，他的情況是：將兩表中的數(shù)據(jù)進行窮舉組合（笛卡爾積）、任何一種組合都包括了，那么此時得到的就是一張新的表了對該表進行操作，就是單表=操作了

再對該表進行篩選，選出正確的數(shù)據(jù)（因為直接窮舉的話，他們的數(shù)據(jù)是不正確的，我們需要將對應(yīng)部門的數(shù)據(jù)進行整合，這個部門編號就相當于一個外鍵的連接作用）

然后就得到了正確的兩表結(jié)合的數(shù)據(jù)（如上圖）

回到題目：

顯示雇員名、雇員工資所在部門的名字和部門號為10（在表結(jié)合后面再添加部門篩選條件）

select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno = 10;

其中需要注意的是deptno的有兩個，所以需要篩選一下

顯示各個員工的姓名，工資，及工資級別

需要的新表salgrade：

結(jié)合emp得到新表：

需要員工的姓名，工資，及工資級別

其中因為是窮舉的，所以說表是用問題的，而我們找的是正確的工資等級所以結(jié)合sal 、losal、hisal 通過between and來進行分級：

總結(jié)：

在進行多表查詢的時候，將兩張表合并的方式是笛卡爾積式的窮舉結(jié)合，這樣可能會導(dǎo)致數(shù)據(jù)出現(xiàn)問題，所以我們需要進行再次的篩選，得到符合目的的新表，再對這個表進行正常的單表處理即可

自連接

自連接是指在同一張表連接查詢

同一張表進行笛卡爾積：

發(fā)現(xiàn)：
同一張表并不能直接的進行笛卡爾積合并，但將這張表重命名為兩個名字，就能進行合并了，也就是自連接

那什么情況下會使用自連接呢？

顯示員工FORD的上級領(lǐng)導(dǎo)的編號和姓名

mgr是員工領(lǐng)導(dǎo)的編號–empno
此處為什么要使用自連接呢？
因為：員工的領(lǐng)導(dǎo)本質(zhì)也是員工，本題每個員工的領(lǐng)導(dǎo)只是通過編號來指定的，所以說無法直接找到領(lǐng)導(dǎo)的信息
若想找到某個員工的領(lǐng)導(dǎo)姓名的話，就需要員工信息中的領(lǐng)導(dǎo)的編號和表中的員工編號進行比對篩選才能找到領(lǐng)導(dǎo)的信息

找到FORD的領(lǐng)導(dǎo)編號----empselect mgr from emp where ename='FORD';在從emp表中使用領(lǐng)導(dǎo)編號找領(lǐng)導(dǎo)信息—empselect ename,emobo from emp where ename(...);
子查詢：

select empno,ename from emp where emp.empno=(select mgr from emp where ename='FORD');

第二種方式（多表查詢，自查詢）：

將相同的表重命名為兩張表，再進行笛卡爾積合并從兩表結(jié)合的新表中找到FORD在從這兩張表中獲取 判斷 表1中的領(lǐng)導(dǎo)編號 = 表二中的員工編號的 信息

子查詢：

子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢

單行子查詢 顯示SMITH同一部門的員工

select * from EMP WHERE deptno = (select deptno from EMP where ename='smith');

多行子查詢 查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含10自己的
分析題目：

10號部門崗位（distinct去重）：

in 查看是否包含：

篩選出job崗位包含10號部門崗位的相同的雇員的名字，崗位，工資，部門號：

其中還不要10號部門的（那么再次篩選 deptno<> 10 、<>就是不等與）

進一步拓展（結(jié)合前面的理解下）：本質(zhì)就是將上面的結(jié)果在重命名為一個張表在和其他表進行合并得到領(lǐng)導(dǎo)名稱
其中select子查詢還能當成一張表出現(xiàn)在from后面

all：獲取所有信息 顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號 工資比部門30的所有員工（找到30部門的最高工資進行比較）的員工的姓名、工資和部門號（通過前面的最高工資再在表中進行遍歷所有比較）

這種本質(zhì)也可以，但若想更加的具體且通俗易懂
使用all函數(shù)，比較所有情況，不需要提前獲取最大的，而是直接比較所有

any關(guān)鍵字； 顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（包含自己部門的員工）
很好理解就不過訴了：

多列子查詢

單行子查詢是指子查詢只返回單列，單行數(shù)據(jù)；多行子查詢是指返回單列多行數(shù)據(jù)，都是針對單列而言的，而多列子查詢則是指查詢返回多個列數(shù)據(jù)的子查詢語句
可能有點不太好理解，具體見下面實例：

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

注意：
任何時刻，查詢出來的臨時結(jié)構(gòu)，本質(zhì)在邏輯上也是表結(jié)構(gòu)

子查詢與from

子查詢語句出現(xiàn)在from子句中。這里要用到數(shù)據(jù)查詢的技巧，把一個子查詢當做一個臨時表使用。

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

找到每個部門的平均工資：

將原本的表和該表進行笛卡爾積，生成新表（就得到了每個員工和平均工資）

那么就變成了單標查詢

若還需要辦公地址，就再需要表：

再次結(jié)合，并且去掉沒用的值

再篩選出需要的字段：

select ename, deptno, sal, format(asal,2) from EMP, (select avg(sal) asal, deptno dt from EMP group by deptno) tmp where EMP.sal > tmp.asal and EMP.deptno=tmp.dt;

查找每個部門工資最高的人的姓名、工資、部門、最高工資

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

select DEPT.deptno, dname, mycnt, loc from DEPT, (select count(*) mycnt, deptno from EMP group by deptno) tmp where DEPT.deptno=tmp.deptno;

mysql一切皆表
解決多表問題的本質(zhì)：想辦法將多表轉(zhuǎn)化為單表，所以mysql中，所有select的問題全部都可以轉(zhuǎn)成單標問題！

合并查詢

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

union

該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，會自動去掉結(jié)果集中的重復(fù)行。

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

union all

該操作符用于取得兩個結(jié)果集的并集。當使用該操作符時，不會去掉結(jié)果集中的重復(fù)行。

其中注意的話使用union進行拼接的前提是列相同：

本章完。預(yù)知后事如何，暫聽下回分解。

到此這篇關(guān)于MySQL中實現(xiàn)多表查詢的操作方法(配sql+實操圖+案例鞏固 通俗易懂版)的文章就介紹到這了,更多相關(guān)mysql多表查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論