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

一、group by ，分組

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

1、我們先從最簡(jiǎn)單的開(kāi)始：

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

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

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

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

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

例：查詢(xún)每個(gè)學(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的時(shí)候，我就陷入了一個(gè)誤區(qū)，那就是group by不能和where一起使用，只能用having……

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

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

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

出現(xiàn)having其實(shí)我覺(jué)得就是為了解決一條語(yǔ)句出現(xiàn)兩個(gè)where的問(wèn)題，把它們區(qū)分開(kāi)來(lái)

例：

查詢(xún) 30100學(xué)院的每個(gè)專(zhuān)業(yè)的學(xué)生有多少人。

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

查詢(xún)每個(gè)學(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í)行順序：①先對(duì)*進(jìn)行篩選，②對(duì)篩選的結(jié)果進(jìn)行分組，③對(duì)分組的結(jié)果進(jìn)行篩選

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

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

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

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

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

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

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

2、連接類(lèi)型解讀

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

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

關(guān)鍵字：

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

語(yǔ)法：

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

3、連接的使用

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

例：查詢(xún)每個(gè)學(xué)院的學(xué)生有多少人：（學(xué)院的值是學(xué)院的id），在沒(méi)有連接的時(shí)候，學(xué)院人數(shù)為0的是顯示不出來(lái)的，因?yàn)楫?dāng)前表中就沒(méi)有這個(gè)學(xué)院的信息

那么我們?cè)谶@里做一下左連接（左外連接）：

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

我這里是一個(gè)完整的語(yǔ)句了。我在寫(xiě)出這條語(yǔ)句之前遇到了許多的磕磕碰碰。

解讀它：

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

因?yàn)槲覀円氖菍W(xué)院，所以學(xué)院作為主表，放left join的前面 c LEFT JOIN a ON ...

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

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

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

這時(shí)，查詢(xún)結(jié)果是這樣的

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

這時(shí)候，對(duì)這個(gè)結(jié)果集進(jìn)行分組：GROUP BY c.ID，并且查詢(xún)字段要做更改

在上邊那個(gè)結(jié)果集中，c.ID和a.COLLEGE是一一對(duì)應(yīng)的，此時(shí)，count(*)的數(shù)據(jù)是總行數(shù)，因?yàn)槲覀兊闹鞅硎菍W(xué)院表，所以這個(gè)數(shù)據(jù)和count(c.ID)的數(shù)據(jù)是一樣的。

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

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

4、經(jīng)過(guò)我測(cè)試了一下

左連接和右連接……

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

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

這兩個(gè)語(yǔ)句的結(jié)果相同，它兩并沒(méi)有發(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;

這兩個(gè)語(yǔ)句也沒(méi)有區(qū)別。

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

最新評(píng)論