MySQL中連接查詢和子查詢的問題
多表連接的基本語法
多表連接,就是將幾張表拼接為一張表,然後進行查詢
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);
到此這篇關於MySQL中連接查詢和子查詢的問題的文章就介紹到這瞭,更多相關MySQL連接查詢和子查詢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!