mysql加強(4)~多表查詢
- 2022 年 1 月 27 日
- 筆記
- 笛卡爾積、消除笛卡爾積操作(等值、非等值連接)，內連接（隱式連接、顯示連接)、外連接、自連接
mysql加強（4）~多表查詢：笛卡爾積、消除笛卡爾積操作(等值、非等值連接)，內連接（隱式連接、顯示連接)、外連接、自連接
一、笛卡爾積
1、什麼是笛卡爾積：
數學上，有兩個集合A={a,b},B={1,2,3},則兩個集合的笛卡爾積={{a,1}, {a,2}, {a,3}, {b,1}, {b,2}, {b,3}} 列出所有情況,一共是2*3=6條記錄
在資料庫中，笛卡爾積是多表查詢沒有連接條件時返回的表結果。
2、實際開發中應該避免全笛卡爾積 —-在 where 加入有效的連接條件【等值連接】
連接n張表，至少需要n-1個連接條件。
■ 所謂的多表查詢—「都是兩張表的查詢」， 假如有A、B、C、D、E 五張表，先是A、B查詢得到AB表，然後C再和AB表查詢得到ABC表，然後…
3、外鍵約束：foreign key FK， 用來限定B表的某一列數據是來自A表的主鍵列，不允許亂寫。
【開發中往往是通過java程式碼檢查數據的合法性，不使用外鍵！】
■ 外鍵作用：
- 不允許亂寫，要參考另外一張表的主鍵列，確定是合法數據
- 和查詢沒有關係，沒有外鍵不影響查詢，僅僅是插入數據時候會做數據檢查。
- 在開發中要支援外鍵和事務，存儲引擎必須是InnoDB
- 使用外鍵性能較低，
開發中，往往刪除外鍵約束，檢查數據是否合法是通過java程式碼（業務程式碼） - 外鍵–在「多的一方」，舉例，多個員工屬於都是屬於銷售部的（同一個部門）
- 開發中添加外鍵，往往是通過下拉框從「多的一方」選出「一方」。
4、消除笛卡爾積（等值連接和非等值連接）
-
等值連接：最常見的連接操作，通常是存在
主外鍵約束條件的多表建立的，連接條件中兩個欄位通過 = 建立等值關係(連接條件是等號)。 -
非等值連接：多表連接，連接條件為 不是用等號，用大於、小於、大於等於、小於等於
#等值連接例子（其中表emp中的部門編號就是參照表dept的部門編號）--- 主外鍵約束 【不過外鍵咱一般不加哈哈哈，外鍵檢查作用交給java業務邏輯程式碼啦】 select * from emp e, dept d where e.deptno = d.deptno; #非等值連接例子 #查詢員工的姓名、工資、所在部門的名稱和工資的等級（表salgrade給出的是工資範圍【等級號、最低工資、最高工資】） select e.ename,e.sal,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal;
二、多表連接查詢（所謂’連接’—通過一個列名去尋找對應的另外一個列名）
1、多表查詢分類：
內連接(隱式/顯式連接)、外連接(左外連接、右外連接、全外連接)、自連接
2、多表查詢圖示：
3、當多表查詢有重名的列時，必須在列名前加上表名【一般用別名】作為前綴。
# 當多表中有重名列時，必須在列名前加上表名【一般用別名】作為前綴。
select * from emp, dept where emp.deptno = dept.deptno;
select * from emp e, dept d where e.deptno = d.deptno;
4、內連接查詢
（1）隱式連接語法（沒有join）：
select [欄位1[,欄位2….]]
from 表A, 表B
where 查詢過濾條件 and 消除笛卡爾積的連接條件
[order by 排序欄位1 asc|desc [,排序欄位2[asc|desc],…]]
(2) 顯示連接語法（有join）—常用：
select [欄位1[,欄位2….]]
from 表A [inner] join 表B on 消除笛卡爾積的連接條件 [join 表C on 消除笛卡爾積條件…]
where 查詢過濾條件
[order by 排序欄位1 asc|desc [,排序欄位2[asc|desc],…]]
#查詢員工編號，員工名稱，員工所屬部門編號和名稱。
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;
# 顯示查詢的等值連接，當列名是相同時，可以使用using
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d using (deptno);
#查詢員工編號，員工名稱，員工所屬部門編號和名稱以及薪資等級
select e.empno, e.ename, d.deptno, 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;
- 自然連接：是表之間的同名列都進行等值連接。
■ 內連接【等值連接】—匹配條件才顯示。
5、外連接
■ 內連接最大問題：必須匹配條件才能查詢出來。
例如新人沒有分配部門，但是多表查詢的時候，查詢員工編號、姓名、所屬於部門時，新人的資訊就不會顯示出來。
（1）左連接/外連接 【以誰為準，誰顯示全部，以哪張表為準，顯示該表的全部，連接那張不滿足補null】
- 左(外)連接：查詢出join左邊表的所有數據，若是右邊表不匹配使用null填充。
- 右(外)連接：查詢出join右邊表的所有數據，若是左邊表不匹配使用null填充。
（2）全外連接查詢
-
mysql 暫時不支援，但是可以通過 union+左右連接完成。
select e.ename, d.dname from emp e left join dept d on e.deptno = d.deptno union select e.ename, d.dname from emp e right join dept d on e.deptno = d.deptno;
6、自連接（自己和自己連接，把一張表看成兩張表使用，通過別名區分）—常用：
-
在查詢語句中，一張表可以重複使用多次，完成多次連接的需要。
#查詢員工名稱和其對應的主管名稱 emp 表有員工名稱的列（主管也屬於員工），也有主管的編號---通過主管的編號去找對應的員工名稱【連接】 select e1.ename, e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno;
