超詳細多表查詢詳解-多表關系-多表查詢-子查詢

多表關系

一對多關系：這是最常見的關系類型，它表示在兩個表之間，一個表中的記錄可以與另一個表中的多個記錄相關聯。例如，一個班級（父表）可以有多個學生（子表），但每個學生只能屬于一個班級。

多對多關系：表示一個表中的多個記錄可以與另一個表中的多個記錄相關聯。例如，一個學生可以選修多門課程，而一門課程也可以被多個學生選修。

一對一關系：關系: 一對一關系，多用于單表拆分，將一張表的基礎字段放在一張表中，其他詳情字段放在另一張表中，以提升操作效率.實現: 在任意一方加入外鍵，關聯另外一方的主鍵，并且設置外鍵為唯一的(UNIQUE)

多表查詢概述

多表查詢就是指從多張表中查詢數據。

當直接查詢兩個表時

select * from emp , dept ;

會出現笛卡爾積問題

多表查詢中的笛卡爾積問題通常指的是在沒有指定有效的連接條件時，兩個或多個表之間會產生所有可能的行組合。

?為了避免笛卡爾積的問題，可以采取以下措施：

明確連接條件：在進行多表查詢時，應該明確指定表與表之間的連接條件，確保只有相關的數據才會被組合在一起。

SELECT * FROM emp,dept where emp.dept_id =  dept.id;

使用內連接：使用內連接（INNER JOIN）來確保只有滿足連接條件的記錄才會出現在結果集中。

檢查關聯條件：確保所有的關聯條件都是有效的，避免因為錯誤的關聯條件導致查詢結果出現笛卡爾積。

使用外連接：如果需要包含某個表中的所有記錄，即使它們在另一個表中沒有匹配的記錄，可以使用外連接（LEFT OUTER JOIN 或 RIGHT OUTER JOIN）來避免笛卡爾積的問題。\

多表查詢分類

連接查詢

內連接：相當于查詢A、B交集部分數據
外連接
左外連接：查詢左表所有數據，以及兩張表交集部分數據
右外連接：查詢右表所有數據，以及兩張表交集部分數據
自連接：當前表與自身的連接查詢，自連接必須使用表別名

內連接

內連接的語法分為兩種: 隱式內連接、顯式內連接

隱式內連接

SELECT 字段列表 FROM 表1 , 表2 WHERE 條件 ... ;

顯式內連接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 連接條件 ... ;

有一個員工表（emp）和一個部門表（dept），并且想要查詢每個員工的姓名及其對應的部門名稱，可以使用以下查詢語句：?(顯式內連接實現) --- INNER JOIN ... ON ...

SELECT e.name, d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;

查詢每一個員工的姓名 , 及關聯的部門的名稱 (隱式內連接實現)

表結構: emp , dept

連接條件: emp.dept_id = dept.id

select e.name,d.name from emp e , dept d where e.dept_id = d.id;

一旦為表起了別名，就不能再使用表名來指定對應的字段了，此時只能夠使用別名來指定字段。

外連接

外連接分為兩種，分別是：左外連接和右外連接。具體的語法結構為

左外連接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 條件 ... ;

右外連接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 條件 ... ;

查詢emp表的所有數據, 和對應的部門信息

由于需求中提到，要查詢emp的所有數據，所以是不能內連接查詢的，需要考慮使用外連接查詢。

表結構: emp, dept

連接條件: emp.dept_id = dept.id

select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

查詢dept表的所有數據, 和對應的員工信息(右外連接)

select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

左外連接和右外連接可以相互替換，只需要調整先后順序就可以了。而我們在日常開發使用時，更偏向于左外連接。

自連接

自連接查詢

自連接是SQL中一種表與自身進行連接的操作，它常用于解決連續性問題或比較表中的數據.

自己連接自己,把一張表連接查詢多次

自連接可以看作是內連接的一種特殊情況，它允許我們使用同一張表的不同別名來執行連接操作。這樣做的目的是為了更好地比較表中的記錄或者查找具有特定關系的行。自連接在處理例如員工層級、朋友關系等具有層次或網絡結構的數據時尤為有用。以下是一些自連接的關鍵點：

別名的使用：在進行自連接時，需要為同一張表設置兩個不同的別名，以便在查詢中區分它們。這兩個別名通常被視為兩個獨立的表來處理。
連接條件：自連接的連接條件通常基于某些字段的相等性或其他邏輯關系。例如，如果我們想要找出年齡相同且籍貫也相同的學生信息，我們可以使用自連接來實現這一查詢。
結果優化：在使用自連接時，可能會出現重復的記錄或者不需要的字段。因此，需要在查詢中添加適當的條件來去除這些冗余信息，以得到更精確的結果。
性能考慮：由于自連接涉及到同一張表的多次引用，這可能會導致查詢性能的下降。因此，在設計查詢時，應考慮到性能的影響，并盡可能優化。

SELECT 字段列表 FROM 表A 別名A JOIN 表A 別名B ON 條件 ... ;

對于自連接查詢，可以是內連接查詢，也可以是外連接查詢

查詢員工及其所屬領導的名字

表結構: emp

select a.name , b.name from emp a , emp b where a.managerid = b.id;

?查詢所有員工 emp 及其領導的名字 emp , 如果員工沒有領導, 也需要查詢出來表結構: emp a , emp b

select a.name '員工', b.name '領導' from emp a left join emp b on a.managerid =b.id;

在自連接查詢中，必須要為表起別名，要不然我們不清楚所指定的條件、返回的字段，到底是哪一張表的字段。

聯合查詢

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

對于聯合查詢的多張表的列數必須保持一致，字段類型也需要保持一致。

union all 會將全部的數據直接合并在一起，union 會對合并之后的數據去重。

將薪資低于 5000 的員工 , 和年齡大于 50 歲的員工全部查詢出來.

當前對于這個需求，我們可以直接使用多條件查詢，使用邏輯運算符 or 連接即可。那這里呢，我們也可以通過union/union all來聯合查詢.

select * from emp where salary < 5000
union all
select * from emp where age > 50;

union all查詢出來的結果，僅僅進行簡單的合并，并未去重。

union 聯合查詢，會對查詢出來的結果進行去重處理。

子查詢

SQL語句中嵌套SELECT語句，稱為嵌套查詢，又稱子查詢。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

?子查詢外部的語句可以是INSERT / UPDATE / DELETE / SELECT 的任何一個。

根據子查詢結果不同

A. 標量子查詢（子查詢結果為單個值）

標量子查詢是一種特殊的子查詢，它返回單一值，通常用在WHERE子句中與比較運算符結合使用。

定義和用法：標量子查詢的結果是一個單一的值，即一行一列的數據。這種查詢可以用作另一個查詢的一部分，通常是在WHERE子句中，以篩選滿足特定條件的記錄。例如，想要找出分數高于平均分的學生學號和成績，可以使用標量子查詢來計算平均分數并將其作為比較的條件。
語法結構：基本的語法結構是將子查詢放置在括號內，并在外部查詢的WHERE子句中使用比較運算符。例如：SELECT 學號，分數 FROM score WHERE 分數 > (SELECT AVG(分數) FROM score);。這里的子查詢?(SELECT AVG(分數) FROM score)?返回一個值，即score表中的平均分數。
性能考慮：雖然標量子查詢在功能上非常強大，但它們可能會影響查詢的性能。因為子查詢需要先執行并取得結果，然后外部查詢才能使用這個結果。如果主查詢返回大量唯一的連接列值，那么子查詢可能需要執行多次，這可能會導致性能問題。優化標量子查詢的一種方法是重寫為JOIN操作，或者在某些情況下使用索引來改善性能。

常用的操作符：= <> > >= < <=

查詢 "銷售部" 的所有員工信息

 select * from emp where dept_id = (select id from dept where name = '銷售部');

B. 列子查詢(子查詢結果為一列)

列子查詢是子查詢的一種類型，它返回單列多行的結果集，通常與主查詢結合使用來篩選數據。

列子查詢的特點是它返回一個字段的多個值，這些值通常用于WHERE子句中與IN、ANY、ALL或EXISTS等操作符結合使用，以便根據子查詢的結果來過濾主查詢的數據。

以下是列子查詢的一些典型用法：

與IN操作符結合使用：當需要根據一組值來篩選數據時，可以使用IN操作符。例如，如果想要選取所有年齡為18歲的學生的成績信息，可以使用列子查詢來獲取這些學生的學號，然后根據這些學號來查詢成績。
與ANY和ALL操作符結合使用：ANY和ALL操作符可以用于比較主查詢中的值與子查詢返回的一組值。ANY用于比較是否滿足子查詢結果中的任意一個條件，而ALL用于比較是否滿足子查詢結果中的所有條件。
與EXISTS操作符結合使用：EXISTS操作符用于檢查子查詢是否返回了結果。如果子查詢返回至少一行數據，EXISTS操作符返回真，否則返回假。這在檢查是否存在相關記錄時非常有用。

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

操作符	描述
IN	在指定的集合范圍之內，多選一
NOT IN	不在指定的集合范圍之內
ANY	子查詢返回列表中，有任意一個滿足即可
SOME	與ANY等同，使用SOME的地方都可以使用ANY
ALL	子查詢返回列表的所有值都必須滿足

查詢所有財務部人員工資

select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '財務部') );

C. 行子查詢(子查詢結果為一行)

行子查詢返回的結果集包含一行多列

返回值：行子查詢可以返回一個或多個字段的值，這些值可以是單一的記錄，也可以是由多條記錄組成的集合。
使用場景：當需要根據一組相關的數據來篩選主查詢結果時，可以使用行子查詢。例如，如果想要找出工資最高的員工信息，可能需要根據員工的工資和員工編號來進行查詢。
比較運算符：在單行子查詢中，通常使用單行比較運算符（如=, >, <, >=, <=, <>），而在多行子查詢中，則使用多行運算符（如IN, ANY, ALL）。

常用的操作符：= 、<> 、IN 、NOT IN

查詢與 "小白" 的薪資及直屬領導相同的員工信息 ;

select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '小白');

D. 表子查詢(子查詢結果為多行多列)

表子查詢是子查詢的一種類型，它返回的結果集包含多行多列，并且需要在查詢中為其指定別名。

別名的使用：由于表子查詢返回的是多行多列的結果集，因此在使用時必須為其指定別名，以便在外部查詢中引用這些字段。
語法結構：表子查詢通常放在圓括號中，并且在外部查詢的FROM子句后面，通過別名來引用。例如，如果要找出參加了“01”課程的學生信息及課程分數，可以使用表子查詢來實現更復雜的查詢條件。
性能考慮：表子查詢可能會影響查詢的性能，特別是當子查詢返回大量數據時。因此，在設計查詢時，應當考慮效率，并在必要時對查詢進行優化。
與外部查詢的關系：表子查詢的結果不被直接顯示，而是傳遞給外部查詢，作為外部查詢的條件使用。外部查詢根據子查詢的結果來過濾數據，并最終顯示結果。

常用的操作符：IN

查詢入職日期是 "2000-6-6" 之后的員工信息 , 及其部門信息

select e.*, d.* from (select * from emp where entrydate > '2000-6-6') e left join dept d on e.dept_id = d.id ;

根據子查詢位置

A. WHERE之后

B. FROM之后

C. SELECT之后