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

復(fù)合查詢

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

1. 回顧查詢基本操作

下面將通過幾個(gè)具體情況來進(jìn)行回顧

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

分析查詢目標(biāo)：

工資高于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'

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

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

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

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

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

其中任何值和NULL運(yùn)算都會(huì)變成NULL（此處該人的comm為NULL）

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

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

在加上名稱和月薪，獎(jiǎng)金，年薪，這樣跟好看

在進(jìn)行排序得到年薪的降序（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é)果按照指定的列進(jìn)行分組，通常與聚合函數(shù)一起使用。

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

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

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

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

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

having

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

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

平均工資（同上）平均工資低于2000的部門號(hào)（分組）

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

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

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

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

因?yàn)樯厦娴臄?shù)據(jù)（雇員名、雇員工資以及所在部門和部門號(hào)為10的）需要來自EMP和DEPT兩張表，因此要聯(lián)合查詢
EMP（需要ename、sal）

DEPT（需要dname）：

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

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

再對(duì)該表進(jìn)行篩選，選出正確的數(shù)據(jù)（因?yàn)橹苯痈F舉的話，他們的數(shù)據(jù)是不正確的，我們需要將對(duì)應(yīng)部門的數(shù)據(jù)進(jìn)行整合，這個(gè)部門編號(hào)就相當(dāng)于一個(gè)外鍵的連接作用）

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

回到題目：

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

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

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

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

需要的新表salgrade：

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

需要員工的姓名，工資，及工資級(jí)別

其中因?yàn)槭歉F舉的，所以說表是用問題的，而我們找的是正確的工資等級(jí)所以結(jié)合sal 、losal、hisal 通過between and來進(jìn)行分級(jí)：

總結(jié)：

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

自連接

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

同一張表進(jìn)行笛卡爾積：

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

那什么情況下會(huì)使用自連接呢？

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

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

找到FORD的領(lǐng)導(dǎo)編號(hào)----empselect mgr from emp where ename='FORD';在從emp表中使用領(lǐng)導(dǎo)編號(hà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');

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

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

子查詢：

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

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

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

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

10號(hào)部門崗位（distinct去重）：

in 查看是否包含：

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

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

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

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

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

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

多列子查詢

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

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

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

子查詢與from

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

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

找到每個(gè)部門的平均工資：

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

那么就變成了單標(biāo)查詢

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

再次結(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;

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

顯示每個(gè)部門的信息（部門名，編號(hào)，地址）和人員數(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)成單標(biāo)問題！

合并查詢

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

union

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

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

union all

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

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

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

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

最新評(píng)論