前面我們講解的mysql表的查詢都是對一張表進行查詢，在實際開發(fā)中這遠遠不夠，未來可能還有多表查詢，子查詢。

1.基本查詢回顧

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

下面有兩種寫法，都沒問題

select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';

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

按照部門號升序而雇員的工資降序排序

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

使用年薪進行降序排序

我們看到這個表獎金這一列有的是有的，有的是沒有的為null，年薪=月薪*12+獎金，但是null不參與運算，我們前面剛學(xué)的ifnull函數(shù)這個時候就可以用上了

ifnull(val1，val2)，如果val1為null，返回val2，否則返回val1的值

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

顯示工資最高的員工的名字和工作崗位最高工資我們可以使用聚合函數(shù)做統(tǒng)計，但是聚合函數(shù)只是對一列的相同數(shù)據(jù)做聚合，今天還要有名字，所以單純的做聚合是無法滿足需求的，它只能最高工資是多少。名字和工作崗位顯示不出來。

因此我們先找最高工資是多少。然后拿著這個最高工資去把這個人和工作崗位找到。很顯然這是兩句select。

但是這樣寫太挫了。怎么辦呢，想辦法把兩個select查詢語句合在一塊寫。

sql允許在一條sql內(nèi)部在執(zhí)行select查詢，這稱為子查詢，先執(zhí)行內(nèi)部的sql然后在執(zhí)行外部的sql

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

顯示工資高于平均工資的員工信息

還是要先使用聚合函數(shù)把平均工資找到，然后在找到工資高于平均工資的員工。因此還可以使用子查詢。

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

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

首先是對部門分組，分組就是分表，表分好之后一個表內(nèi)部門號是相同的，然后就是在每張表中找每個部門的平均工資和最高工資。其實在子表和在一個整表內(nèi)找是一樣的。在我看來不管是整表還是子表其實都是在表內(nèi)查詢。

select deptno,max(sal) 最高,avg(sal) 平均 from emp group by deptno;

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

這里要的是平均工資低于2000的部門號，因此要按照部門號分組，統(tǒng)計出每個部分的平均工資，然后在篩選出低于2000的部門號和平均工資

select deptno,avg(sal) myavg from emp group by deptno having myavg<2000;

顯示每種崗位的雇員總數(shù)，平均工資

這個不就是對崗位分組，然后在統(tǒng)計每個崗位的人數(shù)和平均工資

select job,count(*),avg(sal) from emp group by job;

2.多表查詢

以前我們都是從一張表拿數(shù)據(jù)，但是實際開發(fā)中往往數(shù)據(jù)來自不同的表，所以需要多表查詢。

顯示雇員名、雇員工資以及所在部門的名字

我們發(fā)現(xiàn)上面emp表中是沒有部門名稱的，換句話說要的數(shù)據(jù)是從兩張表來的。

員工名和員工工資來自于emp表，部門名稱來來自于dept表，因此注定了我們要將兩個表做整合然后在查詢。

select * from emp,dept;

可以看到形成了一張大表，仔細觀察一下，將兩張表信息做整合的時候，就光SMITH這一條消息就和整個dept表做組合形成了更多的記錄，發(fā)現(xiàn)下面都是這樣的。

我們可以看到如果今天對兩張表的信息做組合，新形成表本質(zhì)是將兩張表中數(shù)據(jù)進行窮舉組合的結(jié)果。我們把它稱之為笛卡爾積。

在我們看來這不就是把兩張表變成了一張表嗎。所以未來在做數(shù)據(jù)的查找的時候，不就還是相當(dāng)于單表的查找嗎！然后就可以按照條件篩選出想要的信息。注意窮舉是把所有組合結(jié)果都放在一起了，但是有些信息是有無意義的，因此可以先去除無意義的信息(不過還是看具體情況在決定是否保留)，然后在按條件查找。

去除無效信息

在按條件篩選

在我們看來mysql一切皆表，換句話說這里做笛卡爾積之后，它形成的組合結(jié)果也是表結(jié)構(gòu)，然后按照條件篩選

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

select ename,sal,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno;

注意，如果指定的字段是這個表中是唯一的字段，不用指定表名直接顯示即可，如果不是這個表中唯一的字段就需要指定表名顯示，用來區(qū)分要顯示是那個表的字段。

顯示各個員工的姓名，工資，及工資級別emp表中有員工的姓名和工資，工資級別在工資表里的，因此也還要將兩個表笛卡爾積。

select ename,sal,grade from emp t1,salgrade t2 where t1.sal between t2.losal and t2.hisal;

上面我們是將兩個不同的表做笛卡爾積，那可不可以把同一張表做笛卡爾積呢？

3.自連接

自連接是指同一張表做笛卡爾積。

我們發(fā)現(xiàn)直接把同一張表做笛卡爾積是不行的。主要原因這是同一張表這樣不太好，字段名有重復(fù)不知道用的是那個表的字段名。

因此我們可以給兩個表做重命名。

重命名也可以對表進行重命名，一旦對表進行重命名之后幾乎可以在這條sql語句任何地方出現(xiàn)。因為sql語句執(zhí)行一定是先告訴是從那個表拿數(shù)據(jù)。

我們看到同一個表也是拿著前面的表每一條記錄去和后面的表中所有記錄做組合。所以哪怕是同一張表也可以做笛卡爾積，只不過是對表名重新命名一下即可。

顯示員工FORD的上級領(lǐng)導(dǎo)的編號和姓名（mgr是員工領(lǐng)導(dǎo)的編號–empno）

我們首先會想到把這個人的領(lǐng)導(dǎo)編號拿到，然后根據(jù)這個編號在去找這個人拿到他的信息。兩條select查詢語句，但是我們可以把兩個整和成一條sql語句

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

還有一種做法，我們發(fā)現(xiàn) 先找到FORD的領(lǐng)導(dǎo)編是要從 EMP 表中找，然后根據(jù)領(lǐng)導(dǎo)編號找領(lǐng)導(dǎo)信息也是要從 EMP 表中找。 因此我們可以把同一張表自連接然后再找。

select t2.empno,t2.ename from emp t1,emp t2 where t1.mgr=t2.empno and t1.ename='FORD';

4.子查詢

在之前編寫的時候，子查詢我們也寫了一些。現(xiàn)在我們正式來說一下子查詢的概念。除了剛才的笛卡爾積是一種整合表的做法，子查詢也是多表查詢或者一張表中復(fù)雜查詢時常用的做法。

子查詢是指嵌入在其他sql語句中的select語句，也叫嵌套查詢一般我們在子查詢時依賴的永遠都是子查詢查出來的結(jié)果，根據(jù)結(jié)果我們可以把子查詢劃分為單列單行子查詢、單例多行子查詢、多列單行子查詢、多列多行子查詢。

4.1單列子查詢

單例單行子查詢

顯示SMITH同一部門的員工

首先會執(zhí)行子查詢將SMITH的部門號查出來，然后充當(dāng)外面select的篩選條件。

select * from emp where deptno=(select deptno from emp where ename='SMITH');

子查詢篩選出來的是單列單行的信息。

單列多行子查詢

子查詢篩選出來的是單例多行的信息。

in關(guān)鍵字 判斷一個列值是否在集合中。查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含10自己的

select ename,job,sal,deptno from emp where job in(select job from emp where deptno) and deptno<>10;

查詢和10號部門的工作崗位相同的雇員的名字，崗位，工資，部門號，但是不包含10自己的，并且知道對應(yīng)的員工屬于哪一個部門的名字

還想要名字怎么辦呢，但是emp里面并沒有部門名字，只有dept表里面有。結(jié)合剛才所學(xué)，我們可以進行多表查詢。首先確定一定要用的是dept表，還有一張表用誰呢？剛才我們不是已經(jīng)得到一張表了。所以就把dept表和剛才的表做笛卡爾積。

子查詢不僅可以在where充當(dāng)篩選條件，還可以在from中，先用下面在解釋。

然后去除不對的信息，就得到我們要的數(shù)據(jù)了

select ename,job,sal,tmp.deptno,dname from (select ename,job,sal,deptno from emp where job in(select job from emp where deptno=10) and deptno<>10) as tmp,dept where tmp.deptno=dept.deptno;

我們不僅僅用子查詢把要的結(jié)果篩選出來，我想說的是，一個SQL整體的查詢結(jié)果本身就是表結(jié)構(gòu)，mysql一切解表，所以不要認為只有物理上真實存在的表才可以做笛卡爾積，我們可以將一個查出來的表結(jié)構(gòu)也可以和其他表或者其他查詢結(jié)果做笛卡爾積。其次，子查詢不僅能出現(xiàn)在where后面充當(dāng)判斷條件，而且也能出現(xiàn)在from后面充當(dāng)?shù)芽柗e。 在from哪里解釋。

顯示工資比部門30的所有員工的工資高的員工的姓名、工資和部門號可能會想到先把30號部門最高工資先拿到，然后在去篩選比30號部門最高工資還高的信息。

下面這種寫法我們也是先查找30部分最高工資然后充當(dāng)篩選條件，在篩選

select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);

除了這樣的寫法之外，我們還有一種做法，先把30號部門工資篩選出來，然后使用
all關(guān)鍵字 表示全部

select ename,sal,deptno from emp where sal>all(select distinct sal from emp where deptno=30);

顯示工資比部門30的任意員工的工資高的員工的姓名、工資和部門號（包含自己部門的員工）只要比30號部分任意一個人工資高就可以了
any關(guān)鍵字 表示任意

select ename,sal,deptno from emp where sal>any(select distinct sal from emp where deptno=30);

4.2多列子查詢

單行子查詢是指子查詢只返回單列，單行數(shù)據(jù)；多行子查詢是指返回單列多行數(shù)據(jù)，都是針對單列而言的，而多列子查詢則是指查詢返回多個列數(shù)據(jù)的子查詢語句。

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

select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH') and ename<>'SMITH';

子查詢查出來的多列單行，沒關(guān)系mysql支持( ，)

select * from emp where (deptno,job) in (select deptno,job from emp where ename='SMITH') and ename<>'SMITH';

我們發(fā)現(xiàn)這里也支持子查詢多列多行

目前全部的子查詢，全部都在where子句中，充當(dāng)判斷條件！但是任何時刻，查詢出來的臨時結(jié)構(gòu)，本質(zhì)在邏輯上也是表結(jié)構(gòu)！

5.在from子句中使用子查詢

子查詢不僅可以出現(xiàn)where中充當(dāng)判斷條件，也可以出現(xiàn)在from中，from是在sql中告訴數(shù)據(jù)庫去那個表里拿數(shù)據(jù)。在這里說一下任意查出來的表結(jié)構(gòu)在我看來全都是表結(jié)構(gòu)。子查詢語句出現(xiàn)在from子句中，把一個子查詢結(jié)果當(dāng)做一個臨時表使用，可以解決很多問題。

顯示每個高于自己部門平均工資的員工的姓名、部門、工資、平均工資首先我們要把每個部門的平均工資拎出來，如果找呢？那就需要分組然后聚合統(tǒng)計了。然后找出高于自己部門平均工資的姓名、部門、工資、平均工資。這個怎么找呢？如果你想的子查詢找出自己部門的平均工資然后在做篩選找出這個部門中高于平均工資的，你會發(fā)現(xiàn)你做不下去的，因為今天這里并不是一個部門。首先我們先把每個部門平均工資拿出來走一步看一步。

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

按照之前這就是多列多行子查詢，然后在子查詢?nèi)here中篩選可能就比較麻煩了

可以看我們目前有什么，目前我們有部門號和部門平均工資，在我看來這就是一張表，然后我手里還有一張員工表，所以是不是可以把兩張表做笛卡爾積，做完之后每個員工它所在那個部門已經(jīng)所在部門的平均工資，在新的表中不就有了嗎

子查詢做表必須要給一個別名

select * from emp,(select deptno,avg(sal) myavg from emp group by deptno) tmp;

這個笛卡爾積將兩張表的信息做了窮舉，可是做完笛卡爾積有些信息是有意義的，有些一看就無意義的的，就如一個員工所屬部門它目前并不需要和它不是同一個部門號的平均工資做對比，如果將來需要再說。因此可以過濾掉。

select * from emp,(select deptno,avg(sal) myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno;

篩選之后，現(xiàn)在這不就是一張符合條件的單表查詢了嗎

然后在這張表里，在篩選出需要的信息就可以了

select ename,emp.deptno,sal,myavg from emp,(select deptno,avg(sal) myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and sal>myavg;

如果再加要求呢，要求這些人的辦公地點在哪里？很簡單我們可以把上面篩選出來的表結(jié)構(gòu)充當(dāng)一個表然后再和物理存在的dept表做笛卡爾積。

然后不又得到一張表了嗎

然后只把有意義的消息篩選出來，

select * from dept,(select ename,emp.deptno,sal,myavg from emp,(select deptno,avg(sal) myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and sal>myavg) tmp where dept.deptno=tmp.deptno;

在把誰在哪里上班，部門號是多少消息拿出來不就好了嗎

select tmp.ename,dept.loc,tmp.deptno from dept,(select ename,emp.deptno,sal,myavg from emp,(select deptno,avg(sal) myavg from emp group by deptno) tmp where emp.deptnmp.deptno=tmp.deptno and sal>myavg) tmp where dept.deptno=tmp.deptno;

只要你想做還可以在笛卡爾積。所以我們面對非常復(fù)雜的查詢本質(zhì)上都是在任務(wù)分解，復(fù)雜問題是由簡單問題構(gòu)成的。

查找每個部門工資最高的人的姓名、工資、部門、最高工資首先也是要分組聚合統(tǒng)計找每個部門的最高工資，只不過只能統(tǒng)計到部門號和部門工資，這個人其他信息是沒有辦法在group by找到的。然后我們把這個臨時表結(jié)構(gòu)和emp做笛卡爾積。 最后在篩選出來部門號相同的，這個時候不有我們想要的信息的一張表了嗎，然后在篩選自己想要的信息。

然后篩選出部門號相同的信息，最后找出自己要的數(shù)據(jù)就可以了

select ename,sal,emp.deptno,mymax from emp,(select deptno,max(sal) mymax from emp group by deptno) tmp where emp.deptno=tmp.deptno and sal=mymax;

記住mysql一切皆表，所謂的一切皆表就意味著可以把查詢出來的臨時結(jié)果在from后面也充當(dāng)表。

顯示每個部門的信息（部門名，編號，地址）和人員數(shù)量首先人員數(shù)量一定是聚合出來的員工表里面是沒有這個信息的。其次部門名、編號、地址是在dept表里面的。因此我們還可以像上面的做法一樣解決。

select t1.deptno,dname,loc,mycnt from dept t1,(select deptno,count(*) mycnt from emp group by deptno) t2 where t1.deptno=t2.deptno;

其實還有一種方法就是單純使用多表，不涉及子查詢，不過這樣太簡單粗暴了，不夠優(yōu)雅！直接對emp表和dept表做笛卡爾積，然后篩選出部門和相同的一張表，然后對這張表做分組，分組之后在做聚合統(tǒng)計。

select t2.deptno,t2.dname,t2.loc,count(*) mycnt from emp t1,dept t2 where t1.deptno=t2.deptno group by t2.deptno,t2.dname,t2.loc;

要的信息除了部門號和人員數(shù)量，還有編號和地址，但是根據(jù)我們前面所學(xué)知識知道分組聚合統(tǒng)計只有跟在group by后面的列和聚合函數(shù)才可以聚合情況下出現(xiàn)，因此還要對編號和地址分組。

這種方法并不推薦，推薦使用子查詢多表這種方法。

總結(jié)一下：mysql在我的心里是沒有多表結(jié)構(gòu)的，永遠就是一張表。group by在我看來也是一張表，分組就是分表。只要解決一個問題其他都是解決。 多張表我可以在where中充當(dāng)判斷條件，在from中也做一個表然后和其他表做笛卡爾積。所以根本就沒有多表問題。

解決多表問題的本質(zhì)：想辦法將多表轉(zhuǎn)化成為單表，所以mysql中，所有select的問題全部都可以轉(zhuǎn)成單表問題！這就是我們多表查詢的指導(dǎo)思想！

6.合并查詢

在實際應(yīng)用中，為了合并多個select的執(zhí)行結(jié)果，可以使用集合操作符 union，union all，合并并不是笛卡爾積，笛卡爾積是將兩個表的信息窮舉。合并就是單純的合起來。

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

將工資大于2500或職位是MANAGER的人找出來其實我們可以直接篩選出來的，但是我們用一下union

我們可以看到這兩條sql查詢出來的信息是有重復(fù)的。

這里我們可以使用union把兩條sql合并起來并且去掉重復(fù)的

不想去重使用union all，就會把所有信息保留

注意合并時，兩個表結(jié)構(gòu)列必須是一樣的才能把兩個表合并起來

7.表的內(nèi)連和外連

表的連接分為內(nèi)連和外連

7.1內(nèi)連接

內(nèi)連接實際上就是利用where子句對兩種表形成的笛卡兒積進行篩選，我們前面學(xué)習(xí)的查詢都是內(nèi)連接，也是在開發(fā)過程中使用的最多的連接查詢。

除了上面學(xué)習(xí)的from 逗號 連接兩個表然后用where篩選有效信息，還可以用下面inner join連接兩個表，然后用on并且可以用and級聯(lián)多個篩選條件 對笛卡爾積篩選出有效信息。之前學(xué)到的其實就是內(nèi)連接的一種。

語法：

select 字段 from 表1 inner join 表2 on 連接條件 and 其他條件；

顯示SMITH的名字和部門名稱

這是之前的寫法

select ename,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno and ename='SMITH';

標(biāo)準內(nèi)連接寫法

select ename,emp.deptno,dname from emp inner join dept on emp.deptno=dept.deptno where ename='SMITH';

兩種寫法都可以得到同樣的數(shù)據(jù)，換句話說這種標(biāo)準寫法可以讓我們的sql邏輯更清楚 ，哪一個部分是要形成笛卡爾積的，那一部分是進一步做條件篩選的。當(dāng)然也直接用and連接，不過還是建議用where，邏輯更清楚。

7.2外連接

外連接分為左外連接和右外連接

7.2.1左外連接

如果多表查詢，我們想讓左側(cè)的表完全顯示不要過任何過濾篩選，如果和右側(cè)的表配不上，讓右側(cè)的都為空也可以。必須保持左側(cè)表的全貌。我們就叫做左外連接。

語法：

select 字段名 from 表名1 left join 表名2 on 連接條件

我們發(fā)現(xiàn)目前這兩張表id是對不上的。

查詢所有學(xué)生的成績，如果這個學(xué)生沒有成績，也要將學(xué)生的個人信息顯示出來

如果要用id做內(nèi)連接的話，那只有1，2號學(xué)生符合條件，而我們要保留左側(cè)表結(jié)構(gòu)要完全顯示，那內(nèi)連接就不能滿足條件了。因此我們使用左外連接

select * from stu left join exam on stu.id=exam.id;

左側(cè)表完全保留，右側(cè)表按條件拼接，條件滿足直接拼上，條件不滿足拼null

7.2.2右外連接

如果聯(lián)合查詢，要求右側(cè)的表完全顯示我們就說是右外連接。
其實只有左外連接也可以，把順序換一下就可以，不過mysql這里也給我提供了右外連接。

語法：

select 字段 from 表名1 right join 表名2 on 連接條件；

對stu表和exam表聯(lián)合查詢，把所有的成績都顯示出來，即使這個成績沒有學(xué)生與它對應(yīng)，也要顯示出來

select * from stu right join exam on stu.id=exam.id;

右側(cè)表完全保留，左側(cè)表按條件拼接，條件滿足直接拼上，條件不滿足拼null

列出部門名稱和這些部門的員工信息，同時列出沒有員工的部門首先是要將dept表和emp表做連接，要求的是必須把部門全部顯示出來即使沒有員工，這不是要求以dep表為主嗎。

這里使用左外連接和右外連接都可以，不過就是換一下位置

select dept.dname,emp.* from dept left join emp on dept.deptno=emp.deptno order by dept.deptno asc;

select dept.dname,emp.* from emp right join dept on dept.deptno=emp.deptno order by dept.deptno asc;

總結(jié)

到此這篇關(guān)于MySQL復(fù)合查詢和表的內(nèi)外連接的文章就介紹到這了,更多相關(guān)MySQL復(fù)合查詢和表內(nèi)外連接內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論