MySql中的連接查詢問題

更新時(shí)間：2023年03月20日 09:42:21 作者：i知識(shí)搬運(yùn)工

這篇文章主要介紹了MySql中的連接查詢問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

連接查詢

當(dāng)進(jìn)行多表連接查詢時(shí) 需要指定字段所屬的表 , 可以提高查詢效率 , 如果不指定字段所屬的表 , 數(shù)據(jù)庫會(huì)從每個(gè)表中都找一下該字段

e . 字段名 : 表示取 emp 表的某個(gè)字段
emp as e : 表的別名機(jī)制 , 可以省略 as 直接寫成 emp e
連接查詢：也可以叫跨表查詢，需要關(guān)聯(lián)多張表聯(lián)合起來查詢數(shù)據(jù)
emp表和dept表聯(lián)合起來查詢數(shù)據(jù)，從emp表中取員工名字，從dept表中取部門名字

注意: 做連接查詢的時(shí)候一定要寫上關(guān)聯(lián)條件避免笛卡爾積現(xiàn)象

連接查詢的分類

根據(jù)語法的年代分類

SQL92：1992年的時(shí)候出現(xiàn)的語法

sql92的缺點(diǎn)：結(jié)構(gòu)不清晰，表的連接條件，和后期進(jìn)一步篩選的條件，都放到了where后面

SQL99：1999年的時(shí)候出現(xiàn)的語法(重點(diǎn)學(xué)習(xí))

sql99優(yōu)點(diǎn)：表的連接條件和查詢條件分離，連接之后如果還需要進(jìn)一步篩選，再往后繼續(xù)添加where , 多個(gè)表進(jìn)行連接的時(shí)候更清晰

根據(jù)表連接的方式分類

內(nèi)連接：等值連接 , 非等值連接 , 自連接 , inner 可以省略一般不加

表 A inner join 表 B on 關(guān)聯(lián)條件

外連接：左外連接（左連接）, 右外連接（右連接）, outer 可以省略一般不加

表 A left outer join 表 B on 關(guān)聯(lián)條件
表 B right outer join 表 A on 關(guān)聯(lián)條件
左連接以左面的表為準(zhǔn)和右邊的表比較，和左表相等的不相等都會(huì)顯示出來，而右表符合條件的顯示,不符合條件的不顯示 (右連接恰恰相反)
左連接能完成的功能右連接一定可以完成

全連接 , 連接的兩張表都是主表 , 都能查詢出來

笛卡爾積現(xiàn)象

當(dāng)兩張表進(jìn)行連接查詢，沒有指定連接條件的時(shí)候，最終查詢結(jié)果條數(shù)是兩張表?xiàng)l數(shù)的乘積，這種現(xiàn)象被稱為：笛卡爾積現(xiàn)象（笛卡爾發(fā)現(xiàn)的，這是一個(gè)數(shù)學(xué)現(xiàn)象）

避免笛卡爾積現(xiàn)象：連接多個(gè)表時(shí)加連接條件，將滿足這個(gè)條件的記錄被篩選出來

匹配的過程中匹配的次數(shù)沒有減少 , 但是最終查詢的結(jié)果條數(shù)是變少了 , 因?yàn)楦鶕?jù)連接條件進(jìn)行了篩選
通過笛卡爾積現(xiàn)象得出，表的連接次數(shù)越多效率越低，盡量避免表的連接次數(shù)

內(nèi)連接之等值連接

因?yàn)闂l件是等量關(guān)系，所以被稱為等值連接（連接條件相等的數(shù)據(jù)）

查詢每個(gè)員工所在部門名稱，顯示員工名和部門

emp e和dept d表進(jìn)行連接 , 連接條件是：e.deptno = d.deptno

	--sql92的缺點(diǎn)：結(jié)構(gòu)不清晰，表的連接條件，和后期進(jìn)一步篩選的條件，都放到了where后面

	select 
		e.ename,d.dname
	from
		emp e, dept d
	where
		e.deptno = d.deptno;
		
	--sql99優(yōu)點(diǎn)：表連接的條件是獨(dú)立的，連接之后，如果還需要進(jìn)一步篩選，再往后繼續(xù)添加where		
	select 
		e.ename,d.dname
	from
		emp e
	--inner可以省略（帶著inner可讀性更好）
	(inner) join
		dept d
	on
		e.deptno = d.deptno;

內(nèi)連接之自連接

因?yàn)橹挥幸粡埍磉B接，具體的查詢方法是把一張表看作兩張表自己連接自己 , 所以成為自連接

查詢員工的上級(jí)領(lǐng)導(dǎo)，要求顯示員工名和對(duì)應(yīng)的領(lǐng)導(dǎo)名

一張表看成兩張表 , emp e 代表了員工表，emp m 也代表了領(lǐng)導(dǎo)表

連接條件: e.mgr = m.empno 員工的領(lǐng)導(dǎo)編號(hào) = 領(lǐng)導(dǎo)的員工編號(hào)

--SQL92
select e.ename, m.ename from emp e, emp m where e.mgr=m.empno;

--SQL99
select 
	a.ename as '員工名', b.ename as '領(lǐng)導(dǎo)名'
from
	emp e
join
	emp m
on
	e.mgr = m.empno;

內(nèi)連接之非等值連接

因?yàn)檫B接條件不是一個(gè)等量關(guān)系，所以稱為非等值連接

顯示薪水大于 2000 的員工信息，并顯示所屬的部門名稱

--采用 SQL92 語法
select 
	e.ename, e.sal, d.dname 
from 
	emp e, dept d 
where 
	e.deptno=d.deptno and e.sal > 2000;
	
--采用 SQL99 語法
select 
	e.ename, e.sal, d.dname 
from 
	emp e 
(inner) join 
	dept d 
on 
	e.deptno=d.deptno 
where 
	e.sal>2000;

找出每個(gè)員工的薪資等級(jí)，要求顯示員工名、薪資、薪資等級(jí)

select 
	e.ename, e.sal, s.grade
from
	emp e
(inner) join
	salgrade s
on
	e.sal between s.losal and s.hisal;

外連接

外連接: 在外連接當(dāng)中，兩張表連接產(chǎn)生了主次關(guān)系 , 主要查詢的是主表的數(shù)據(jù) , 捎帶著關(guān)聯(lián)查詢次表 , 即如果對(duì)方?jīng)]有記錄和我主表匹配 , 那么默認(rèn)對(duì)方是null , outer關(guān)鍵字可以省略

右外連接: 帶有 right 的是右外連接，表示將join關(guān)鍵字右邊的這張表看成主表，主要是為了將這張表的數(shù)據(jù)全部查詢出來，捎帶著關(guān)聯(lián)查詢左邊的表
左外連接: 帶有 lef t的是左外連接 , 表示將join關(guān)鍵字左邊的這張表看成主表，主要是為了將這張表的數(shù)據(jù)全部查詢出來，捎帶著關(guān)聯(lián)查詢右邊的表

**注意: 任何一個(gè)右連接都有左連接的寫法 , 任何一個(gè)左連接都有右連接的寫法 **

查詢每個(gè)員工的上級(jí)領(lǐng)導(dǎo)，要求顯示所有員工的名字和領(lǐng)導(dǎo)名 , 如果某個(gè)員工沒有領(lǐng)導(dǎo) , 那么該員工也必須顯示出來

--左連接
select 
	a.ename as '員工名', b.ename as '領(lǐng)導(dǎo)名'
from
	emp a
left (outer) join
	emp b
on
	a.mgr = b.empno; 

--右連接
select 
	a.ename as '員工名', b.ename as '領(lǐng)導(dǎo)名'
from
	emp b
right (outer) join
	emp a
on
	a.mgr = b.empno;

內(nèi)連接和外連接的特點(diǎn)

內(nèi)連接：A表和B表連接，A和B兩張表沒有主次關(guān)系是平等的 , 查詢時(shí)只是將能夠匹配上連接條件的數(shù)據(jù)查詢出來 , 即如果沒有匹配的就查詢不出來 , inner關(guān)鍵字可以省略

注意: 區(qū)分內(nèi)外連接的辦法是通過 right 和 left 關(guān)鍵字 , 不是通過 inner 和 outer 因?yàn)樗鼈兌伎梢允÷?, 并且外連接的查詢結(jié)果條數(shù)一定是大于等于內(nèi)連接的查詢結(jié)果條數(shù)

顯示員工信息，并顯示所屬的部門名稱

select 
	e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;

顯示員工信息，并顯示所屬的部門名稱，如果某一個(gè)部門沒有員工，那么該部門也必須顯示出來

--外連接（右外連接）
select 
	e.ename,d.dname
from
	emp e 
--outer是可以省略的，帶著可讀性強(qiáng)
--right代表什么：表示將join關(guān)鍵字右邊的這張表看成主表，主要是為了將這張表的數(shù)據(jù)全部查詢出來，捎帶著關(guān)聯(lián)查詢左邊的表
right (outer) join 
	dept d
on
	e.deptno = d.deptno;


--外連接（左外連接）
select 
	e.ename,d.dname
from
	dept d 
--outer是可以省略的，帶著可讀性強(qiáng)
left (outer) join 
	emp e
on
	e.deptno = d.deptno;

多表連接

一條SQL中內(nèi)連接和外連接可以混合 , 都可以出現(xiàn)

--表示一: a 和 b 進(jìn)行內(nèi)連接 , a 和 c 進(jìn)行內(nèi)連接 , a 和 d 進(jìn)行右外連接(推薦)

--表示二: a 和 b 的內(nèi)連接結(jié)果去內(nèi)連接 c 的結(jié)果再去右外連接 d
select 
	...
from
	a
join
	b
on
	a和b的連接條件
join
	c
on
	a和c的連接條件
right join
	d
on
	a和d的連接條件

找出每個(gè)員工的部門名稱以及工資等級(jí)，要求顯示員工名、部門名、薪資、薪資等級(jí)

	select 
		e.ename,e.sal,d.dname,s.grade
	from
		emp e
	join
		dept d
	on 
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal;

查詢結(jié)果

	+--------+---------+------------+-------+
	| ename  | sal     | dname      | grade |
	+--------+---------+------------+-------+
	| SMITH  |  800.00 | RESEARCH   |     1 |
	| ALLEN  | 1600.00 | SALES      |     3 |
	| WARD   | 1250.00 | SALES      |     2 |
	| JONES  | 2975.00 | RESEARCH   |     4 |
	| MARTIN | 1250.00 | SALES      |     2 |
	| BLAKE  | 2850.00 | SALES      |     4 |
	| CLARK  | 2450.00 | ACCOUNTING |     4 |
	| SCOTT  | 3000.00 | RESEARCH   |     4 |
	| KING   | 5000.00 | ACCOUNTING |     5 |
	| TURNER | 1500.00 | SALES      |     3 |
	| ADAMS  | 1100.00 | RESEARCH   |     1 |
	| JAMES  |  950.00 | SALES      |     1 |
	| FORD   | 3000.00 | RESEARCH   |     4 |
	| MILLER | 1300.00 | ACCOUNTING |     2 |
	+--------+---------+------------+-------+

找出每個(gè)員工的部門名稱以及工資等級(jí)，還有上級(jí)領(lǐng)導(dǎo)，要求顯示員工名、領(lǐng)導(dǎo)名、部門名、薪資、薪資等級(jí)

	select 
		e.ename,e.sal,d.dname,s.grade,l.ename
	from
		emp e
	join
		dept d
	on 
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal
	left join
		emp l
	on
		e.mgr = l.empno;

查詢結(jié)果

	+--------+---------+------------+-------+-------+
	| ename  | sal     | dname      | grade | ename |
	+--------+---------+------------+-------+-------+
	| SMITH  |  800.00 | RESEARCH   |     1 | FORD  |
	| ALLEN  | 1600.00 | SALES      |     3 | BLAKE |
	| WARD   | 1250.00 | SALES      |     2 | BLAKE |
	| JONES  | 2975.00 | RESEARCH   |     4 | KING  |
	| MARTIN | 1250.00 | SALES      |     2 | BLAKE |
	| BLAKE  | 2850.00 | SALES      |     4 | KING  |
	| CLARK  | 2450.00 | ACCOUNTING |     4 | KING  |
	| SCOTT  | 3000.00 | RESEARCH   |     4 | JONES |
	| KING   | 5000.00 | ACCOUNTING |     5 | NULL  |
	| TURNER | 1500.00 | SALES      |     3 | BLAKE |
	| ADAMS  | 1100.00 | RESEARCH   |     1 | SCOTT |
	| JAMES  |  950.00 | SALES      |     1 | BLAKE |
	| FORD   | 3000.00 | RESEARCH   |     4 | JONES |
	| MILLER | 1300.00 | ACCOUNTING |     2 | CLARK |
	+--------+---------+------------+-------+-------+