多表連接的基本語法
多表連接，就是將幾張表拼接為一張表，然后進行查詢
select 欄位1, 欄位2, ...
from 表1 {inner|lift|right} join 表2
on 連接條件;
有如下兩張表：部門表和員工表
交叉連接和笛卡爾積現象
交叉連接
交叉連接，又名無條件內連接/笛卡爾連接
第一張表種的每一項會和另一張表的每一項依次組合
select * from employee,department;
上述結果肯定不是我們想曉得的，左表中每一個人都有4個部門，仔細觀察這4條記錄，正好是左表每一條記錄和右表一一匹配后的結果，
笛卡爾積現象
笛卡爾積現象產生的原因：兩張表沒有有效的連接條件，既然你沒有連接條件，本表中的第一行肯定是能和另外表中的所有行進行一一匹配，同理，本表中的第二行肯定是能和另外表中的所有行進行一一匹配，以此類推，本表中的最后一行m也可以和另外表中的所有行進行一一匹配，若另外一張表有n行，那么最后顯示的行數，肯定就是m*n行了，
如果不想產生笛卡爾積現象，就需要添加有效的表連接條件，拿上述例子來說，左表dep_id只有和右表id相等時，才代表他們的部門，
內連接
內連接(INNER JOIN)是找幾張表的交集，即根據條件篩選出來正確的結果，
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;
由于部門表中沒有id=5的部門，所以員工表dep_id=5的這條記錄沒有回傳；而由于行政部沒有員工，所以這條記錄也沒回傳，
外連接
左外連接
左連接(left join)是以左表為準，如果右表中沒有合適的記錄，用NULL補全；其本質是在內連接的基礎上增加左表有結果而右表沒有的記錄(內連接時，這種情況的記錄會忽略)，
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)是以右表為準，如果左表中某些欄位沒有合適的結果，用NULL補全；其本質是在內連接的基礎上增加右表有結果而左表沒有的記錄(內連接時，這種情況的記錄會忽略)，
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;
全外連接
全外連接，在內連接的基礎上，展示左右表的所有的記錄，而左右表中預設記錄以NULL補全，
MySQL中并沒有全外連接的FULL JOIN語法，而是借助UNION/UNION ALL陳述句實作，
UNION和UNION ALL的區別，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;
子查詢
子查詢是將一個查詢陳述句嵌套再另一個查詢陳述句中的查詢方式：
- 子查詢的內層查詢結果，可以作為外層查詢陳述句提供查詢條件，
- 子查詢中可以包含
IN、NOT IN、AND、ALL、EXISTS、NOT EXISTS等關鍵字， - 子查詢中還可以包含比較運算子，如
=、!=、>、<等，
-- 查詢平均年齡在20以上的部門名稱
select name
from department
where id in (
select dep_id
from employee
group by dep_id
having avg(age) > 20);
-- 查詢財務部員工姓名
select name
from employee
where dep_id in (
select id
from department
where name='財務部');
-- 查詢所有大于平均年齡的員工的年齡和姓名
select name,age
from employee
where age > (
select avg(age) from employee);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/297270.html
標籤：MySQL
下一篇：MySQL之連接查詢和子查詢