mysql中的多表關聯查詢

超人李發表於2020-11-20

在使用資料庫查詢語句時,單表的查詢有時候不能滿足專案的業務需求,在專案開發過程中,有很多需求都是要涉及到多表的連線查詢,總結一下mysql中的多表關聯查詢

一,內連線查詢

是指所有查詢出的結果都是能夠在連線的表中有對應記錄的。

以t_employee(員工表)和t_dept(部門表)為例:

t_employee表中的記錄如下:dept代表該員工所在的部門

 

t_dept表中記錄如下:

 

可以發現,其中人力資源部裡沒有員工(這裡只是舉例,可能與實際不符,但主要在於邏輯關係),而趙七沒有對應的部門,現在想要查詢出員工姓名以及其對應的部門名稱:

此時,就要使用內連線查詢,關鍵字(inner join)

在這裡說一下關聯查詢sql編寫的思路,1,先確定所連線的表,2,再確定所要查詢的欄位,3,確定連線條件以及連線方式

1 select 
2 e.empName,d.deptName
3 from t_employee e
4 INNER JOIN t_dept d
5 ON e.dept = d.id;

查詢的結果如下:

其中,沒有部門的人員和部門沒有員工的部門都沒有被查詢出來,這就是內連線的特點,只查詢在連線的表中能夠有對應的記錄,其中e.dept = d.id是連線條件

二,左外連線查詢

是指以左邊的表的資料為基準,去匹配右邊的表的資料,如果匹配到就顯示,匹配不到就顯示為null。例如:

查詢所有員工姓名以及他所在的部門名稱:在內連線中趙七沒有被查出來,因為他沒有對應的部門,現在想要把趙七也查出來,就要使用左外連線:

1 SELECT e.empName,d.deptName
2 from t_employee e
3 LEFT OUTER JOIN t_dept d 
4 on d.id = e.dept;

在這裡,t_employee就是左表,也就是基準表,用基準表的資料去匹配右表的資料,所以左表的記錄是全部會查詢出來的,如果右表沒有記錄對應的話就顯示null

查詢結果:

關鍵字是left outer join,等效於left join,在關聯查詢中,做外連線查詢就是左連線查詢,兩者是一個概念

三,右外連線是同理的,只是基準表的位置變化了而已

比如:查詢所有的部門和對應的員工:

1 SELECT e.empName,d.deptName
2 from t_employee e
3 RIGHT OUTER JOIN t_dept d 
4 on d.id = e.dept;

這裡只是把left修改成了right,但是基準表變化了,是以右表的資料去匹配左表,所以左外連線能做到的查詢,右外連線也能做到
查詢結果:

四,全外連線

顧名思義,把兩張表的欄位都查出來,沒有對應的值就顯示null,但是注意:mysql是沒有全外連線的(mysql中沒有full outer join關鍵字),想要達到全外連線的效果,可以使用union關鍵字連線左外連線和右外連線。例如:

複製程式碼

1 select e.empName,d.deptName
2      FROM t_employee e 
3      left JOIN t_dept d
4      ON e.dept = d.id
5 UNION
6 select e.empName,d.deptName
7      FROM t_employee e 
8      RIGHT JOIN t_dept d
9      ON e.dept = d.id;

複製程式碼

查詢結果:

如果在oracle中,直接就使用full outer join關鍵字連線兩表就行了

五,自連線查詢

自連線查詢就是當前表與自身的連線查詢,關鍵點在於虛擬化出一張表給一個別名

例如:查詢員工以及他的上司的名稱,由於上司也是員工,所以這裡虛擬化出一張上司表

1 SELECT e.empName,b.empName
2      from t_employee e
3      LEFT JOIN t_employee b
4      ON e.bossId = b.id;

查詢結果:

在這裡,b表是虛擬化出的表,我們可以通過查詢瞭解b表的記錄:

1 SELECT e.empName,b.empName,b.*
2      from t_employee e
3      LEFT JOIN t_employee b
4      ON e.bossId = b.id;

查詢結果:

後面的四個欄位就是虛擬化出的b表的所有記錄,但看這四個欄位其實就是記錄所有是上司的員工的資訊

所以,自連線查詢一般用作表中的某個欄位的值是引用另一個欄位的值,比如許可權表中,父許可權也屬於許可權。

相關文章