快捷導(dǎo)航

MySQL中連接查詢和子查詢的問(wèn)題

更新時(shí)間：2021年09月04日 08:39:16 作者：pure3417

這篇文章主要介紹了MySQL中連接查詢和子查詢的問(wèn)題,本文通過(guò)實(shí)例圖文相結(jié)合給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

多表連接的基本語(yǔ)法

多表連接，就是將幾張表拼接為一張表，然后進(jìn)行查詢

select 字段1, 字段2, ...
from 表1 {inner|lift|right} join 表2
on 連接條件;

有如下兩張表：部門(mén)表和員工表

交叉連接和笛卡爾積現(xiàn)象

交叉連接

交叉連接，又名無(wú)條件內(nèi)連接/笛卡爾連接

第一張表種的每一項(xiàng)會(huì)和另一張表的每一項(xiàng)依次組合

select * from employee,department;

上述結(jié)果肯定不是我們想曉得的，左表中每一個(gè)人都有4個(gè)部門(mén)，仔細(xì)觀察這4條記錄，正好是左表每一條記錄和右表一一匹配后的結(jié)果。

笛卡爾積現(xiàn)象

笛卡爾積現(xiàn)象產(chǎn)生的原因：兩張表沒(méi)有有效的連接條件。既然你沒(méi)有連接條件，本表中的第一行肯定是能和另外表中的所有行進(jìn)行一一匹配，同理，本表中的第二行肯定是能和另外表中的所有行進(jìn)行一一匹配，以此類推，本表中的最后一行m也可以和另外表中的所有行進(jìn)行一一匹配。若另外一張表有n行，那么最后顯示的行數(shù)，肯定就是m*n行了。

如果不想產(chǎn)生笛卡爾積現(xiàn)象，就需要添加有效的表連接條件。拿上述例子來(lái)說(shuō)，左表dep_id只有和右表id相等時(shí)，才代表他們的部門(mén)。

內(nèi)連接

內(nèi)連接(INNER JOIN)是找?guī)讖埍淼慕患锤鶕?jù)條件篩選出來(lái)正確的結(jié)果。

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp INNER JOIN department as dep
on emp.dep_id=dep.id;

由于部門(mén)表中沒(méi)有id=5的部門(mén)，所以員工表dep_id=5的這條記錄沒(méi)有返回；而由于行政部沒(méi)有員工，所以這條記錄也沒(méi)返回。

外連接

左外連接

左連接(left join)是以左表為準(zhǔn)，如果右表中沒(méi)有合適的記錄，用NULL補(bǔ)全；其本質(zhì)是在內(nèi)連接的基礎(chǔ)上增加左表有結(jié)果而右表沒(méi)有的記錄(內(nèi)連接時(shí)，這種情況的記錄會(huì)忽略)。

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp left join department as dep
on emp.dep_id=dep.id;

右外連接

跟左連接正好相反，右連接(right join)是以右表為準(zhǔn)，如果左表中某些字段沒(méi)有合適的結(jié)果，用NULL補(bǔ)全；其本質(zhì)是在內(nèi)連接的基礎(chǔ)上增加右表有結(jié)果而左表沒(méi)有的記錄(內(nèi)連接時(shí)，這種情況的記錄會(huì)忽略)。

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp right join department as dep
on emp.dep_id=dep.id;

全外連接

全外連接，在內(nèi)連接的基礎(chǔ)上，展示左右表的所有的記錄，而左右表中缺省記錄以NULL補(bǔ)全。

MySQL中并沒(méi)有全外連接的FULL JOIN語(yǔ)法，而是借助UNION/UNION ALL語(yǔ)句實(shí)現(xiàn)。

UNION和UNION ALL的區(qū)別，UNION具有去重功能。

select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp left join department as dep
on emp.dep_id=dep.id
union
select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.name
from employee as emp right join department as dep
on emp.dep_id=dep.id;

子查詢

子查詢是將一個(gè)查詢語(yǔ)句嵌套再另一個(gè)查詢語(yǔ)句中的查詢方式：
子查詢的內(nèi)層查詢結(jié)果，可以作為外層查詢語(yǔ)句提供查詢條件。
子查詢中可以包含IN、NOT IN、AND、ALL、EXISTS、NOT EXISTS等關(guān)鍵字。

子查詢中還可以包含比較運(yùn)算符，如=、!=、>、<等。

-- 查詢平均年齡在20以上的部門(mén)名稱
select name
from department
where id in (
select dep_id
from employee
group by dep_id
having avg(age) > 20);

-- 查詢財(cái)務(wù)部員工姓名
select name 
from employee
where dep_id in (
select id 
from department 
where name='財(cái)務(wù)部');


-- 查詢所有大于平均年齡的員工的年齡和姓名
select name,age 
from employee 
where age > (
select avg(age) from employee);