前面我們講解的mysql表的查詢(xún)都是對(duì)一張表進(jìn)行查詢(xún)，即數(shù)據(jù)的查詢(xún)都是在某一時(shí)刻對(duì)一個(gè)表進(jìn)行操作的。而在實(shí)際開(kāi)發(fā)中，我們往往還需要對(duì)多個(gè)表同時(shí)進(jìn)行查詢(xún)。

我們這里使用的測(cè)試表，為雇員信息表(來(lái)自O(shè)racle 9i的經(jīng)典測(cè)試表)：EMP員工表，DEPT部門(mén)表，SALGRADE工資等級(jí)表。

EMP員工表：

DEPT部門(mén)表：

SALGRADE工資等級(jí)表：

一、多表查詢(xún)

之前我們都是從一張表拿數(shù)據(jù)，但是實(shí)際開(kāi)發(fā)中，我們需要的數(shù)據(jù)往往來(lái)自不同的表，所以需要進(jìn)行多表查詢(xún)。

~ 顯示雇員名、雇員工資以及所在部門(mén)的名字

笛卡兒積

首先，我們需要介紹的就是笛卡爾積。

根據(jù)上面的需求，我們需要的數(shù)據(jù)是雇員名，雇員工資和雇員所在部門(mén)的名字，顯而易見(jiàn)，雇員名和雇員工資均在emp表中，而雇員所在的部門(mén)的名字是在dept表中，這就明確要求我們需要去兩張表中查詢(xún)數(shù)據(jù)。

那么我就簡(jiǎn)單地去同時(shí)查詢(xún)兩張表：

我們發(fā)現(xiàn)，如果我們直接對(duì)兩張表進(jìn)行整合的話(huà)，其整合方式如下：

在進(jìn)行多表查詢(xún)時(shí)，只需要將多張表的表名依次放到from子句之后，用逗號(hào)隔開(kāi)即可，這時(shí)MySQL將會(huì)對(duì)給定的這多張表取笛卡爾積，作為多表查詢(xún)的初始數(shù)據(jù)。

對(duì)多張表取笛卡爾積，就是得到這多張表的記錄的所有可能有序?qū)M成的集合。即，拿一張表的一條記錄與另一張表的所有記錄進(jìn)行組合，得到新的記錄。所以，我們上面 select * from emp,dept 最終得到的結(jié)果便是員工表emp和部門(mén)表dept的笛卡爾積。

但是，對(duì)員工表和部門(mén)表取笛卡爾積時(shí)，員工表中的每一個(gè)員工信息都會(huì)和部門(mén)表中的每一個(gè)部門(mén)信息進(jìn)行組合，而實(shí)際一個(gè)員工只有和自己所在的部門(mén)信息進(jìn)行組合才是有意義的，因此需要從笛卡爾積中篩選出員工的部門(mén)號(hào)和部門(mén)的編號(hào)相等的記錄。

mysql> select * from emp,dept where emp.deptno=dept.deptno;

所以說(shuō)，最終我們可以這樣解決這個(gè)需求：

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

~ 顯示部門(mén)號(hào)為10的部門(mén)名，員工名和工資 

首先，我們需要明確，部門(mén)名在dept表中，而員工名和工資在emp表中，所以我們需要去兩張表中查詢(xún)數(shù)據(jù)。

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

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

首先，我們需要明確，工資級(jí)別在salgrade表中，而員工的姓名和工資在emp表中，所以我們需要去兩張表中查詢(xún)數(shù)據(jù)。

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

二、自連接

自連接是指在同一張表進(jìn)行數(shù)據(jù)查詢(xún)，也就是說(shuō)我們不僅可以取不同表的笛卡爾積，也可以對(duì)同一張表取笛卡爾積。

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

為了解決這個(gè)需求，我們需要兩步。第一步，在emp表中找到員工Ford上級(jí)領(lǐng)導(dǎo)的編號(hào)mgr；第二步，查找到的mgr就是emp表中某一個(gè)員工的empno，根據(jù) mgr == empno 的條件，就可以找到Ford上級(jí)領(lǐng)導(dǎo)的編號(hào)和姓名。

而這兩次查詢(xún)均是在emp表中進(jìn)行的，所以我們可以對(duì)同一張表進(jìn)行笛卡爾積來(lái)進(jìn)行查詢(xún)。

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

三、子查詢(xún)

子查詢(xún)是指嵌入在其他sql語(yǔ)句中的select語(yǔ)句，也叫嵌套查詢(xún)。

單行子查詢(xún)（返回一行記錄的子查詢(xún)）

~ 顯示SMITH同一部門(mén)的員工 

首先，我們分析一下需求，需要找到與Smith在同一個(gè)部門(mén)的員工。那么首先我們就需要找到Smith在哪個(gè)部門(mén)。

mysql> select ename,deptno from emp where ename='SMITH';

 然后我們就可以根據(jù)Smith所在的部門(mén)進(jìn)行查詢(xún)，進(jìn)而找到與Smith在同一部門(mén)的員工。

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

查找出來(lái)的記錄的deptno和Smith一樣，都是20。 

多行子查詢(xún)（返回多行記錄的子查詢(xún)） 

~ 查詢(xún)和10號(hào)部門(mén)的工作崗位相同的雇員的名字，崗位，工資，部門(mén)號(hào)，但是不包含10自己的（in關(guān)鍵字） 

首先，分析一下需求，我們需要先找到10號(hào)部門(mén)有哪些工作崗位。

mysql> select job from emp where deptno=10;

也就是說(shuō)，我們要查詢(xún)的雇員信息數(shù)據(jù)是滿(mǎn)足雇員的崗位屬于 MANAGER，PRESIDENT，CLERK這三個(gè)中的一種的。

所以，我們可以將上述查詢(xún)作為子查詢(xún)，在查詢(xún)員工表時(shí)在where子句中使用in關(guān)鍵字，判斷員工的工作崗位是否是子查詢(xún)得到的若干崗位中的一個(gè)。

select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10);

由于要求篩選出來(lái)的員工需要不包含10號(hào)部門(mén)的，因此還需要在where子句中指明篩選條件為部門(mén)號(hào)不等于10。所以，最終的查詢(xún)語(yǔ)句如下：

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

~ 顯示工資比部門(mén)30的所有員工的工資高的員工的姓名、工資和部門(mén)號(hào)（all關(guān)鍵字）

首先，分析一下需求，我們需要先找到30號(hào)部門(mén)所有員工的工資，在查詢(xún)時(shí)最好對(duì)結(jié)果進(jìn)行去重，因?yàn)?0號(hào)部門(mén)的某些員工的工資可能是相同的，而我們可以不需要重復(fù)的結(jié)果。

mysql> select distinct sal from emp where deptno=30;

也就是說(shuō)，我們要查詢(xún)的雇員信息數(shù)據(jù)是滿(mǎn)足雇員的工資大于上圖中的最大工資的，也就是大于上圖中所有的工資。

所以說(shuō)，我們最終的查詢(xún)語(yǔ)句如下：

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

~ 顯示工資比部門(mén)30的任意員工的工資高的員工的姓名、工資和部門(mén)號(hào)（包含自己部門(mén)的員工，any關(guān)鍵字）

上面我們已經(jīng)找到了30號(hào)部門(mén)所有員工的工資，而我們要查詢(xún)的雇員信息數(shù)據(jù)是滿(mǎn)足雇員的工資大于30號(hào)部門(mén)任意一個(gè)員工的工資的雇員。

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

多列子查詢(xún) 

單行子查詢(xún)是指子查詢(xún)只返回單列，單行數(shù)據(jù)。多行子查詢(xún)是指返回單列多行數(shù)據(jù)，都是針對(duì)單列而言的，而多列子查詢(xún)則是指查詢(xún)返回多個(gè)列數(shù)據(jù)的子查詢(xún)語(yǔ)句。

~ 查詢(xún)和SMITH的部門(mén)和崗位完全相同的所有雇員，不含SMITH本人

首先，分析一下需求，我們需要先找到Smith的部門(mén)和崗位。

select ename,deptno,job from emp where ename='SMITH';

然后將上述查詢(xún)作為子查詢(xún)，在查詢(xún)員工表時(shí)在where子句中，指明篩選條件為部門(mén)號(hào)和崗位等于子查詢(xún)得到的部門(mén)號(hào)和崗位，并且員工的姓名不為SMITH即可。

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

在from子句中使用子查詢(xún)

子查詢(xún)語(yǔ)句不僅可以出現(xiàn)在where子句中，也可以出現(xiàn)在from子句中。

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

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

首先，分析一下需求，我們需要先查詢(xún)每個(gè)部門(mén)的平均工資。

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

上圖所顯示的數(shù)據(jù)中包含部門(mén)的平均工資，而且只有上表有平均工資的數(shù)據(jù)，現(xiàn)成的表emp，dept和salgrade都沒(méi)有平均工資的數(shù)據(jù)。所以我們需要同時(shí)使用emp員工表和上述的查詢(xún)結(jié)果進(jìn)行多表查詢(xún)，這時(shí)可以將上述查詢(xún)作為子查詢(xún)放在from子句中，然后對(duì)emp員工表和臨時(shí)表取笛卡爾積，然后進(jìn)行篩選。 

select t1.deptno,ename,t1.sal,t2.mysal from emp t1,(select deptno,avg(sal) mysal from emp group by deptno) t2 where t1.deptno=t2.deptnoptno and t1.sal>t2.mysal;

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

首先，我們需要知道每個(gè)部門(mén)的最高工資。

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

然后，將上述查詢(xún)作為子查詢(xún)放在from子句中，然后對(duì)emp員工表和臨時(shí)表取笛卡爾積，進(jìn)而進(jìn)行篩選。

select ename,sal,t1.deptno,maxsal from emp t1,(select deptno,max(sal) maxsal from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal=t2.maxsal;

~ 顯示每個(gè)部門(mén)的信息（部門(mén)名，編號(hào)，地址）和人員數(shù)量

部門(mén)名，編號(hào)均來(lái)自emp表，地址則是來(lái)自dept表。很顯然，這需要從兩張表中進(jìn)行查詢(xún)。

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

注：在from子句中使用子查詢(xún)時(shí)，必須給子查詢(xún)得到的臨時(shí)表取一個(gè)別名，否則查詢(xún)將會(huì)出錯(cuò)。

四、合并查詢(xún)

合并查詢(xún)，是指將多個(gè)查詢(xún)結(jié)果進(jìn)行合并。

union：union的作用是取得兩個(gè)查詢(xún)結(jié)果的并集，union會(huì)自動(dòng)去掉結(jié)果集中的重復(fù)行。 

union all ：union all的作用是取得兩個(gè)查詢(xún)結(jié)果的并集，但union all不會(huì)去掉結(jié)果集中的重復(fù)行。 

~ 顯示工資大于2500或職位是MANAGER的員工 

查詢(xún)工資大于2500的員工：

mysql> select ename,job,sal from emp where sal>2500;

查詢(xún)職位是MANAGER的員工：

mysql> select ename,job,sal from emp where job='MANAGER';

查詢(xún)工資大于2500或職位是MANAGER的員工，可以使用union將上述的兩條查詢(xún)SQL語(yǔ)句連接起來(lái)。

select ename,job,sal from emp where sal>2500 union select ename,job,sal from emp where job='MANAGER';

當(dāng)然，我們也可以使用union all將上述的兩條查詢(xún)SQL語(yǔ)句連接起來(lái)，但不會(huì)對(duì)合并后的結(jié)果進(jìn)行去重。

select ename,job,sal from emp where sal>2500 union all select ename,job,sal from emp where job='MANAGER';

注：待合并的兩個(gè)查詢(xún)結(jié)果的列的數(shù)量必須一致，否則無(wú)法合并。 

五、表的內(nèi)連接和外連接

1、內(nèi)連接

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

語(yǔ)法：

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

~ 顯示SMITH的名字和部門(mén)名稱(chēng) 

我們之前的寫(xiě)法是直接使用笛卡爾積進(jìn)行查詢(xún)。

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

如果我們使用標(biāo)準(zhǔn)的內(nèi)連接寫(xiě)法去寫(xiě)的話(huà)，就是如下的寫(xiě)法：

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

2、外連接

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

左外連接

如果聯(lián)合查詢(xún)，左側(cè)的表完全顯示我們就說(shuō)是左外連接。 

語(yǔ)法：

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

右外連接 

如果聯(lián)合查詢(xún)，右側(cè)的表完全顯示我們就說(shuō)是右外連接。 

語(yǔ)法：

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

總結(jié) 

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

最新評(píng)論