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

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

一、group by ，分組

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

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

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

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

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

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

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

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

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

3、where，having，和group by聯(lián)合使用

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

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

where，過濾條件的關(guān)鍵字，但是它只能對group by之前的數(shù)據(jù)進行過濾篩選；

having，也是過濾條件的關(guān)鍵字作用和where是一樣的，但是它過濾的是分組后的數(shù)據(jù)，就是對分組后得到的結(jié)果集進行過濾篩選。

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

例：

查詢 30100學(xué)院的每個專業(yè)的學(xué)生有多少人。

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

查詢每個學(xué)院的學(xué)生有多少人，并且只要學(xué)生人數(shù)大于3的。

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

濾清執(zhí)行順序：①先對*進行篩選，②對篩選的結(jié)果進行分組，③對分組的結(jié)果進行篩選

4、Group By All 的使用，哈哈哈哈，經(jīng)常網(wǎng)上的查閱，我決定淘汰這個語法~

其實就是前面where之后，想要分組的結(jié)果顯示不符合where的數(shù)據(jù)，當(dāng)然，不做運算，運算結(jié)果用0或null表示，感覺這語法沒啥用，想不出應(yīng)用場景~

二、深入學(xué)習(xí) 連接

連接分4種，內(nèi)連接，全連接，左外連接，右外連接

1、連接出現(xiàn)的地方

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

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

2、連接類型解讀

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

內(nèi)連接：一一映射；全連接：笛卡爾乘積；左外連接：一一映射+左表對應(yīng)右表的null；右外連接：一一映射+右表對應(yīng)左表的null。

關(guān)鍵字：

內(nèi)連接：inner join；全連接：cross join；左外連接：left join；右外連接：right join 。

語法：

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

3、連接的使用

之前學(xué)習(xí)group by的例子中，結(jié)果集是存在bug的。

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

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

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

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

解讀它：

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

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

然后我們發(fā)現(xiàn)有很多字段，于是我們?nèi)サ舳嘤嗟淖侄?，這樣既方便我們觀察，也提高了sql的執(zhí)行效率

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

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

這時，查詢結(jié)果是這樣的

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

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

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

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

這才是查詢所有學(xué)院中每個學(xué)院的學(xué)生人數(shù)的正確答案！當(dāng)然，上邊的截圖只是數(shù)據(jù)的前幾行，后面還有數(shù)據(jù)的

4、經(jīng)過我測試了一下

左連接和右連接……

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

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

這兩個語句的結(jié)果相同，它兩并沒有發(fā)現(xiàn)別的區(qū)別。

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

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

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

這兩個語句也沒有區(qū)別。

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