詳解MySQL分組鏈接的使用技巧

MYSQL中的分組和鏈接是在操作數據庫和數據交互時最常用的兩個在功能，把這兩項處理好了，MYSQL的執行效率會非常高速。

一、group by ，分組

顧名思義，把數據按什么來分組，每一組都有什么特點。

1、我們先從最簡單的開始：

select count(*) from tb1 group by tb1.sex;

查詢所有數據的條數，按性別來分組。這樣查詢到的結果集只有一列count(*)。

2、然后我們來分析一下，這個分組，我們能在select 和 from 之間放一些什么呢？

當數據分組之后，數據的大部分字段都將失去它存在的意義，大家想想，多條數據的同一列，只顯示一個值，那到底顯示誰的，這個值有用嗎？

通過思考，不難發現，只有by的那些列可以放進去，然后就是sql的函數操作了,比如count()，sum()……（包含在by后面作為分組的依據，包含在聚合函數中作為結果）

例：查詢每個學院的學生有多少人：（學院的值是學院的id）

SELECT a.COLLEGE AS 學院,COUNT(*) AS 學生人數 FROM base_alumni a GROUP BY a.COLLEGE;

3、where，having，和group by聯合使用

在最初學習group by的時候，我就陷入了一個誤區，那就是group by不能和where一起使用，只能用having……

看書不認真啊，其實它們都是可以一起使用的，只不過是where只能在group by 的前面，having只能在group by 的后面。

where，過濾條件的關鍵字，但是它只能對group by之前的數據進行過濾篩選；

having，也是過濾條件的關鍵字作用和where是一樣的，但是它過濾的是分組后的數據，就是對分組后得到的結果集進行過濾篩選。

出現having其實我覺得就是為了解決一條語句出現兩個where的問題，把它們區分開來

例：

查詢 30100學院的每個專業的學生有多少人。

SELECT a.MAJOR AS 專業, COUNT(*) AS 學生人數 FROM base_alumni a WHERE a.COLLEGE = 30100 GROUP BY a.MAJOR;

查詢每個學院的學生有多少人，并且只要學生人數大于3的。

SELECT a.COLLEGE AS 學院,COUNT(*) AS 學生人數 FROM base_alumni a GROUP BY a.COLLEGE HAVING COUNT(*)>3;

濾清執行順序：①先對*進行篩選，②對篩選的結果進行分組，③對分組的結果進行篩選

4、Group By All 的使用，哈哈哈哈，經常網上的查閱，我決定淘汰這個語法~

其實就是前面where之后，想要分組的結果顯示不符合where的數據，當然，不做運算，運算結果用0或null表示，感覺這語法沒啥用，想不出應用場景~

二、深入學習 連接

連接分4種，內連接，全連接，左外連接，右外連接

1、連接出現的地方

①from和where之間，做表和表的連接

②where和having之間，having是對group by的結果集進行篩選，就是把group by的結果集作為一張表，然后可以再和別的表做連接，再進一步篩選

2、連接類型解讀

把表看成是一個集合，連接看成是映射，那么它們的結果

內連接：一一映射；全連接：笛卡爾乘積；左外連接：一一映射+左表對應右表的null；右外連接：一一映射+右表對應左表的null。

關鍵字：

內連接：inner join；全連接：cross join；左外連接：left join；右外連接：right join 。

語法：

表a left join 表b on a.列1 = b.列2

3、連接的使用

之前學習group by的例子中，結果集是存在bug的。

例：查詢每個學院的學生有多少人：（學院的值是學院的id），在沒有連接的時候，學院人數為0的是顯示不出來的，因為當前表中就沒有這個學院的信息

那么我們在這里做一下左連接（左外連接）：

SELECT c.ID, a.COLLEGE, COUNT(a.COLLEGE) FROM (SELECT ID FROM dic_college) c LEFT JOIN ( SELECT COLLEGE FROM base_alumni ) a ON c.ID = a.COLLEGE GROUP BY c.ID

我這里是一個完整的語句了。我在寫出這條語句之前遇到了許多的磕磕碰碰。

解讀它：

我們先把學院表和校友信息表（學生表）做左連接

因為我們要的是學院，所以學院作為主表，放left join的前面 c LEFT JOIN a ON ...

然后我們發現有很多字段，于是我們去掉多余的字段，這樣既方便我們觀察，也提高了sql的執行效率

①把學院表變成只有一個字段(SELECT ID FROM dic_college) c

②把學生表變成只有一個字段( SELECT COLLEGE FROM base_alumni ) a

這時，查詢結果是這樣的

SELECT * FROM (SELECT ID FROM dic_college) c LEFT JOIN ( SELECT COLLEGE FROM base_alumni ) a ON c.ID = a.COLLEGE

這時候，對這個結果集進行分組：GROUP BY c.ID，并且查詢字段要做更改

在上邊那個結果集中，c.ID和a.COLLEGE是一一對應的，此時，count(*)的數據是總行數，因為我們的主表是學院表，所以這個數據和count(c.ID)的數據是一樣的。

但是a.COLLEGE為空的行的數據中值都是1，這不是我們想要的，所以我們把count(*)改成count(a.COLLEGE)，這樣數據就出來了。

這才是查詢所有學院中每個學院的學生人數的正確答案！當然，上邊的截圖只是數據的前幾行，后面還有數據的

4、經過我測試了一下

左連接和右連接……

SELECT * FROM a LEFT JOIN b ON b.ID = a.FK_ID;

SELECT * FROM b RIGHT JOIN a ON b.ID = a.FK_ID;

這兩個語句的結果相同，它兩并沒有發現別的區別。

全連接就是交叉連接，和不使用連接……

SELECT * FROM c,a WHERE c.ID = a.FK_ID;

SELECT * FROM c CROSS JOIN a ON c.ID = a.FK_ID;

這兩個語句也沒有區別。

以上就是本篇關于MYSQL分組和鏈接深入詳解的全部內容，如果大家還有任何不明白的地方可以在下方留言區討論。