Java面試題031：一文深入了解MySQL（3）

Java面試題029：一文深入了解MySQL（1）

Java面試題030：一文深入了解MySQL（2）

1、MySQL多表查詢

（1）內連接?inner join

????????返回兩個表中完全匹配的行，即只保留兩個表連接字段值相等的行。

（2）外連接

????????左外連接

????????LEFT JOIN 或 LEFT OUTER JOIN 左外連接返回左表中的所有行，以及右表中滿足連接條件的行（如果左表中的某行在右表中沒有匹配的行，那么結果集中該行的右表列將包含 NULL 值）。

????????右外連接

????????右外連接（RIGHT JOIN 或 RIGHT OUTER JOIN）：右外連接返回右表中的所有行，以及左表中滿足連接條件的行（如果右表中的某行在左表中沒有匹配的行，那么結果集中該行的左表列將包含 NULL 值）。

（3）UNION 與 UNION ALL

-- UNION 語法
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
-- 這里使用了 column1, column2將字段一一列出來，如果 table1和table2字段的順序一致，可以直接寫為 select * ，下方 UNION ALL 同理
select * from table1
UNION
SELECT * FROM table2;-- UNION ALL 語法
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;
或
select * from table1
UNION ALL
SELECT * FROM table2;

（1）列數一致：所有 SELECT 語句的列數必須相同；
（2）數據類型兼容：對應列的數據類型需兼容（如 VARCHAR 與 TEXT 兼容）；
（3）列名規則：最終結果集的列名以第一個 SELECT 的列名為準；

字段相同，順序相同

user1:

user2:

使用UNION查詢會自動去重，合并后的結果就是7條數據：

UNION ALL 不會自動去重，而是將兩張表的全部數據合并，一共十條數據：

字段相同，順序不同

user1：

user4：

????????此時不能直接使用?select?* ，否則不管是 UNION，還是UNION ALL，最終的表字段順序會以第一張表 user1 的字段順序為準，就會產生一個問題，?user4 表中，passwor 和 username 順序是反著的，會導致查詢結果中，user4 表用戶的密碼 password 被當作 username，用戶的 username 被當作 password；造成數據錯誤。

此時就需要將字段一一對應。

字段數量不同

user3：與user1對比，user3 少了一個 age 年齡字段

????????此時查詢的時候就需要"補列字段" ，可以使用 "NULL AS age"對查詢結果中 user3 的age字段進行填充，使其全部為 NULL（也可以使用具體數據）。

2、笛卡爾積

????????假設有兩個集合A和B。A的元素是{a1, a2, …}，B的元素是{b1, b2, …}。那么，A和B的笛卡爾積就是從A中取一個元素，和從B中取一個元素，形成一個有序對，這樣的所有有序對構成的集合就是笛卡爾積。數學上表示為：A × B = {(a1, b1), (a1, b2), …, (a2, b1), (a2, b2), …}。

????????在數據庫中，當你進行表連接操作時，如果沒有指定任何連接條件（如使用WHERE子句），就會產生兩個表的笛卡爾積。這意味著第一個表中的每一行都會與第二個表中的每一行配對，產生巨大數量的數據行。

（1）產生條件

? ? ? ? 兩表關聯查詢語句中沒有指定連接條件。

表Employees中有兩條數據：

表Departments中有兩條數據：?

?不加查詢條件進行查詢，會發現出現4條數據，而且兩個表的字段全部進行了展示

（2）避免笛卡爾積

????????為了避免笛卡爾積，我們應該使用適當的連接條件。

使用顯式的連接類型

INNER JOIN: 使用INNER JOIN并指定連接條件，可以確保只連接相關的行。
LEFT/RIGHT OUTER JOIN: 這些連接類型允許連接兩個表，并包括左表/右表中的所有行，即使它們在右表/左表中沒有匹配項。
FULL OUTER JOIN: 它結合了LEFT和RIGHT JOIN的特點，如果左表或右表中的行沒有匹配項，它也會被包含在結果中。

使用WHERE子句
????????添加過濾條件: 在WHERE子句中明確指定連接條件可以防止產生笛卡爾積，因為它會限制只返回滿足特定條件的行。

使用子查詢
????????子查詢作為連接條件: 在連接的ON子句或WHERE子句中使用子查詢，可以精確控制要返回的行。

使用聚合函數和GROUP BY
????????分組和聚合: 當需要根據某個字段進行分組時，使用GROUP BY子句可以避免笛卡爾積，尤其是在進行統計計算時。

使用DISTINCT關鍵字
????????消除重復行: 如果查詢產生了重復行（這在某些類型的笛卡爾積中可能發生），使用DISTINCT關鍵字可以移除重復的結果集。

使用LIMIT子句
????????限制返回行數: 在進行初步測試和調試時，使用LIMIT子句可以限制查詢結果的行數，從而避免大量的輸出，尤其是在處理可能產生笛卡爾積的復雜查詢時。

3、SQL 查詢語句的執行順序

（8）Select
（9）distinct 字段名1,字段名2，
（6）[fun(字段名)]  
（1）from 表1
（3）<join類型>join 表2 
（2）on <join條件> 
（4）where <where條件> 
（5）group by <字段> 
（7）having <having條件> 
（10）order by <排序字段> 
（11）limit <起始偏移量,行數>

1. FROM：對 FROM 子句中的表1和表2執行笛卡兒積，產生虛擬表VT1

2. ON：對虛擬表 VT1 應用 ON 篩選，只有那些符合join條件的行才被插入虛擬表 VT2 中

3. JOIN：如果指定了 OUTER JOIN（如 LEFT JOIN、RIGHT JOIN），那么保留表中未匹配的行

作為外部行添加到虛擬表 VT2 中，產生虛擬表 VT3。如果 FROM 子句包含兩個以上表，則對上一個連接生成的結果表 VT3 和下一個表重復執行步驟 1～步驟 3，直到處理完所有的表為止

4. WHERE：對虛擬表 VT3 應用 WHERE 過濾條件，只有符合條件的記錄才被插入虛擬表 VT4 中

5. GROUP BY：根據 GROUP BY 子句中的列，對 VT4 中的記錄進行分組操作，產生 VT5

6. 聚合函數：對表 VT5 進行 CUBE 或 ROLLUP 操作，產生表 VT6

7. HAVING：對虛擬表 VT6 應用 HAVING 過濾?，只有符合條件的記錄才被插入虛擬表 VT7中。

8. SELECT：第二次執行 SELECT 操作，選擇指定的列，插入到虛擬表 VT8 中

9. DISTINCT：去除重復數據，產生虛擬表 VT9

10. ORDER BY：將虛擬表 VT9 中的記錄按照排序字段進行排序操作，產生虛擬表 VT10。

11. LIMIT：取出指定行的記錄，產生虛擬表 VT11，并返回給查詢?用戶

舉例：

用戶表user ：

訂單表orders ：

目標：查詢來自北京，并且訂單數少于2的客戶。

SELECT a.user_id,COUNT(b.order_id) as total_orders
FROM  user as a
LEFT JOIN orders as b
ON a.user_id = b.user_id
WHERE a.city = 'beijing'
GROUP BY a.user_id
HAVING COUNT(b.order_id) < 2
ORDER BY total_orders desc

（1）FROM語句對兩個表執行笛卡爾積，會得到一個虛擬表，VT1（vitual table 1）

（2）執行ON過濾

根據ON中指定的條件，去掉那些不符合條件的數據,得到VT2如下：

select * from user as a inner JOIN orders as b ON a.user_id = b.user_id;

（3）執行left join子句：user表作為保留表，未匹配的記錄添加到VT2中形成VT3

（4）執行where條件過濾：對添加了外部行的數據進行where條件過濾，執行WHERE a.city = 'beijing'??得到VT4如下：

（5）執行group by分組語句：執行GROUP BY a.user_id?得到VT5如下：

（6）執行having：HAVING子句主要和GROUP BY子句配合使用，對分組得到VT5的數據進行條件過濾，執行?HAVING COUNT(b.order_id) < 2，得到VT6如下：

（7）select列表：執行測試語句中的SELECT a.user_id,user_name,COUNT(b.order_id) as total_orders，從VT6中選擇出我們需要的內容，得到VT7如下：

（8）執行distinct去重復數據：如果在查詢中指定了DISTINCT子句，則會創建一張內存臨時表（如果內存放不下，就需要存放在硬盤了）。這張臨時表的表結構和上一步產生的虛擬表是一樣的，不同的是對進行DISTINCT操作的列增加了一個唯一索引，以此來除重復數據。

（9）執行order by字句：對虛擬表VT7中的內容按照指定的列進行排序，然后返回一個新的虛擬表

（10）執行limit字句：

LIMIT子句從上一步得到的虛擬表中選出從指定位置開始的指定行數據，常用來做分頁；

MySQL數據庫的LIMIT支持如下形式的選擇：limit n,m

表示從第n條記錄開始選擇m條記錄。對于小數據，使用LIMIT子句沒有任何問題，當數據量非常大的時候，使用LIMIT n, m是非常低效的。因為LIMIT的機制是每次都是從頭開始掃描，如果需要從第60萬行開始，讀取3條數據，就需要先掃描定位到60萬行，然后再進行讀取，而掃描的過程是一個非常低效的過程。

4、count(1)、**count(*) 與 count(列名) 的區別**

（1）count(1) and count(*)

????????當表的數據量大些時，對表作分析之后，使用count(1)還要比使用count()用時多了！
????????從執行計劃來看，count(1)和count()的效果是一樣的。但是在表做過分析之后，count(1)會比count(*)的用時少些（1w以內數據量），不過差不了多少。

????????如果count(1)是聚索引，id，那肯定是count(1)快，但是差的很小的。
????????因為count()，自動會優化指定到那一個字段。所以沒必要去count(1)，用count()，sql會幫你完成優化的，因此：count(1)和count(*)基本沒有差別！

（2）count(1) and count(字段)

????????count(1) 會統計表中的所有的記錄數，包含字段為null 的記錄。
????????count(字段) 會統計該字段在表中出現的次數，忽略字段為null 的情況。即不統計字段為null 的記錄。

（3）執行效率：

列名為主鍵，count(列名)會比count(1)快。
列名不為主鍵，count(1)會比count(列名)快。
如果有主鍵，則 select count（主鍵）的執行效率是最優的。

5、?in 和 exists 的區別

（1）exists

????????exists對外表用loop逐條查詢，每次查詢都會查看exists的條件語句，當exists里的條件語句能夠返回記錄行時（無論記錄行是的多少，只要能返回），條件就為真，返回當前loop到的這條記錄；反之，如果exists里的條件語句不能返回記錄行，則當前loop到的這條記錄被丟棄，exists的條件就像一個bool條件，當能返回結果集則為true，不能返回結果集則為false。

select * from user where exists (select 1);

????????對user表的記錄逐條取出，由于子條件中的select 1永遠能返回記錄行，那么user表的所有記錄都將被加入結果集，所以與select * from user;是一樣的。

select * from user where exists (select * from user where user_id = 0);

????????user表進行loop時，檢查條件語句(select * from user where user_id = 0),由于user_id永遠不為0，所以條件語句永遠返回空集，條件永遠為false，那么user表的所有記錄都將被丟棄。

????????如果A表有n條記錄，那么exists查詢就是將這n條記錄逐條取出，然后判斷n遍exists條件。

（2）in

????????in查詢就是先將子查詢條件的記錄全都查出來，假設結果集為B，共有m條記錄，然后再將子查詢條件的結果集分解成m個，再進行m次查詢。

????????in查詢相當于多個or條件的疊加。

select * from user where user_id in (1, 2, 3);等效于select * from user where user_id = 1 or user_id = 2 or user_id = 3;

（3）性能對比

IN查詢在內部表和外部表上都可以使用到索引。
Exists查詢僅在內部表上可以使用到索引。
當子查詢結果集很大，而外部表較小的時候，Exists的Block Nested Loop(Block 嵌套循環)的作用開始顯現，并彌補外部表無法用到索引的缺陷，查詢效率會優于IN。
當子查詢結果集較小，而外部表很大的時候，Exists的Block嵌套循環優化效果不明顯，IN 的外表索引優勢占主要作用，此時IN的查詢效率會優于Exists。
表的規模不是看內部表和外部表，而是外部表和子查詢結果集。

歡迎大家關注我的專欄，該專欄會持續更新，從原理角度覆蓋Java知識體系的方方面面。

一文吃透JAVA知識體系（面試題）https://blog.csdn.net/wuxinyan123/category_7521898.html?fromshare=blogcolumn&sharetype=blogcolumn&sharerId=7521898&sharerefer=PC&sharesource=wuxinyan123&sharefrom=from_link