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

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

EMP員工表：

DEPT部門表：

SALGRADE工資等級表：

一、多表查詢

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

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

笛卡兒積

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

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

那么我就簡單地去同時查詢兩張表：

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

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

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

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

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

所以說，最終我們可以這樣解決這個需求：

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

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

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

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

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

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

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

二、自連接

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

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

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

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

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

三、子查詢

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

單行子查詢（返回一行記錄的子查詢）

~ 顯示SMITH同一部門的員工 

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

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

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

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

查找出來的記錄的deptno和Smith一樣，都是20。 

多行子查詢（返回多行記錄的子查詢） 

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

首先，分析一下需求，我們需要先找到10號部門有哪些工作崗位。

mysql> select job from emp where deptno=10;

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

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

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

由于要求篩選出來的員工需要不包含10號部門的，因此還需要在where子句中指明篩選條件為部門號不等于10。所以，最終的查詢語句如下：

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

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

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

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

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

所以說，我們最終的查詢語句如下：

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

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

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

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

多列子查詢 

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

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

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

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

然后將上述查詢作為子查詢，在查詢員工表時在where子句中，指明篩選條件為部門號和崗位等于子查詢得到的部門號和崗位，并且員工的姓名不為SMITH即可。

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

在from子句中使用子查詢

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

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

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

首先，分析一下需求，我們需要先查詢每個部門的平均工資。

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

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

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

首先，我們需要知道每個部門的最高工資。

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

然后，將上述查詢作為子查詢放在from子句中，然后對emp員工表和臨時表取笛卡爾積，進(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;

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

部門名，編號均來自emp表，地址則是來自dept表。很顯然，這需要從兩張表中進(jì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子句中使用子查詢時，必須給子查詢得到的臨時表取一個別名，否則查詢將會出錯。

四、合并查詢

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

union：union的作用是取得兩個查詢結(jié)果的并集，union會自動去掉結(jié)果集中的重復(fù)行。 

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

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

查詢工資大于2500的員工：

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

查詢職位是MANAGER的員工：

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

查詢工資大于2500或職位是MANAGER的員工，可以使用union將上述的兩條查詢SQL語句連接起來。

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

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

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

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

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

1、內(nèi)連接

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

語法：

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

~ 顯示SMITH的名字和部門名稱 

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

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

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

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

2、外連接

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

左外連接

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

語法：

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

右外連接 

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

語法：

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

總結(jié) 

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

最新評論