1. 前言

文章主要圍繞著以下三個問題:

1. group by的作用
2. where與having的區(qū)別
3. 表的連接分為哪些,分別是什么作用

2. 表的設(shè)計

在創(chuàng)建數(shù)據(jù)表時,我們通常時根據(jù)需求找到"實體", 梳理"實體"之間的關(guān)系,從而進(jìn)行創(chuàng)建.
"實體"之間可能會有以下幾種關(guān)系:1.沒關(guān)系 2.一對一 3.一對多 4.多對多.
沒關(guān)系應(yīng)該是最好理解的,就是單獨的一張表,并不涉及到其它的表.

2.1 一對一

一對一的關(guān)系在生活中是很常見的,例如每個學(xué)生都有屬于自己的學(xué)號,每個學(xué)號就只對應(yīng)一個學(xué)生. 類似于這樣的情況,就是一對一的關(guān)系.
此時就可以創(chuàng)建兩張表,一個是學(xué)生表,另一個是學(xué)號表. 學(xué)生表里的學(xué)號就可以和學(xué)號表中的學(xué)號關(guān)聯(lián)起來.

2.2 一對多

學(xué)生在學(xué)校上課時,會有一個班級. 但是一個班級可以有多個學(xué)生. 這就是一對多的關(guān)系.

2.3 多對多

舉個例子,我們在學(xué)習(xí)課程時,可以選擇多門課程進(jìn)行學(xué)習(xí),而課程也可以被多個學(xué)生進(jìn)行選擇. 這就是多對多的關(guān)系.
多對多的關(guān)系,在創(chuàng)建表時,可以使用"關(guān)聯(lián)表" 將兩個實體聯(lián)系起來.

如果在設(shè)計數(shù)據(jù)庫表時,場景很復(fù)雜,可以使用ER圖幫助我們更好的創(chuàng)建數(shù)據(jù)庫表.

3.將查詢結(jié)果放到另一個表中

insert into 表2 select 字段 from 表1;
# 表2 是要存放查詢結(jié)果的表
# 表1 是要查詢的表
# 查詢結(jié)果的列要和表2的列相匹配!
# 也可以將查詢結(jié)果存放在表2的指定列中

示例:

下面這個"student1"這個表中,有三條數(shù)據(jù).

接下來我們把查詢結(jié)果放到一張新的 "student2"這個表中

4. 聚合查詢

聚合查詢可以進(jìn)行"行"與"行"之間的運算

4.1 聚合函數(shù)

• count函數(shù) 可以對行進(jìn)行計算,也可以對列進(jìn)行計算.

示例:

這里有一張學(xué)生表,里面有學(xué)生的id,姓名,語數(shù)英三科的成績.

• sum,avg,max和min 都是只對數(shù)字生效,如果不是數(shù)字則沒有意義

sum函數(shù)的用法和count函數(shù)類似. 不過只能計算"列",如果這一列存在"null",則不會參與運算
avg,max,min的用法 和 sum 類似,就不一一介紹了

4.2 GROUP BY

GROUP BY 語句用于結(jié)合合計函數(shù)，根據(jù)一個或多個列對結(jié)果集進(jìn)行分組。

例如:

在我的學(xué)生表中有這樣一些數(shù)據(jù).現(xiàn)在我要對語文成績進(jìn)行分組
使用group by之后,我們可以看到這已經(jīng)以語文成績進(jìn)行分組了

成績?yōu)?7的有一個,69的有兩個,82的有三個

需要注意的是:在進(jìn)行查詢分組的時候,只有分組的這一列,可以查詢,其它列必須搭配聚合函數(shù)來進(jìn)行查詢

4.3 HAVING

分組查詢還可以指定條件,這里的只當(dāng)條件可以分組之前指定還可以分組之后指定 分組前進(jìn)行篩選使用的是where 分組后進(jìn)行篩選使用的則是having SQL 中增加 HAVING 子句原因就是因為WHERE 關(guān)鍵字無法與合計函數(shù)一起使用。

去除id=1的語文成績后進(jìn)行分組

分組之后篩選出語文成績>60分的學(xué)生數(shù)量

where 關(guān)鍵字雖然無法與合計函數(shù)一起使用,但是可以和having一起使用

5. 聯(lián)合查詢(多表查詢)

聯(lián)合查詢一般是在多表之間建立連接后查詢的過程.其實就是計算"笛卡爾積"的過程
但是當(dāng)表很大的時候,如果進(jìn)行聯(lián)合查詢,效率就會特別低.因為"笛卡爾積"就是簡單的排列組合,有些數(shù)據(jù)是"合理"的,有些數(shù)據(jù)是"不合理"的.所以我們就要把"有效"的數(shù)據(jù)篩選出來.因此聯(lián)合查詢通常需要加連接條件和其它篩選條件

5.1 內(nèi)連接

內(nèi)連接得到的是兩張表中都存在的數(shù)據(jù)
兩種寫法:

select 字段 from 表1,表2;
select 字段 from 表1 join 表2 on 條件;

舉個例子:

學(xué)生表:

班級表:

要求查詢"王五"的班級的名字.
這就涉及到了兩張表,就需要使用聯(lián)合(多表)查詢.

1.首先進(jìn)行笛卡爾積

其實就是學(xué)生表和班級表進(jìn)行排列組合,里面有很多無效的數(shù)據(jù).

2. 添加連接條件

注意這里的條件寫法,應(yīng)該是表名.字段名,因為涉及到多個表,多個表中的字段名可能相同,因此需要使用 表名.字段 表明是哪個表中的字段.當(dāng)然如果這個列名是唯一的,也可以不加 表名.

剛才的結(jié)果還是太多了,我們也可以加上指定列進(jìn)行查詢,同樣需要使用表名.字段的形式進(jìn)行查詢

使用join on也可以實現(xiàn)相同的效果,同樣是剛才的例子
使用join on的方式來完成

1.首先進(jìn)行笛卡爾積

2. 添加條件

直接寫join或者inner join就是內(nèi)連接
join on不僅可以實現(xiàn)內(nèi)連接,還可以實現(xiàn)外連接

5.2 外連接

外連接分為左外連接(left join)和右外連接(right join)

還是剛才的學(xué)生表:

但班級表中多有兩條數(shù)據(jù)

進(jìn)行笛卡爾積后得到的結(jié)果

這是內(nèi)連接得到的結(jié)果:

這個是進(jìn)行右連接得到的結(jié)果

右連接會把右側(cè)表中的數(shù)據(jù)都獲取到,即使左邊的值是NULL,也會顯示出來
左連接也是類似,會把左側(cè)表中的數(shù)據(jù)都獲取到,即使右邊的值是NULL,也會顯示出來.

如果兩張表中的數(shù)據(jù),在對方表中都有,那么此時內(nèi)外連接是沒有區(qū)別的,如果兩張表中的數(shù)據(jù)只有一部分在對方的表中,內(nèi)連接就是獲取兩張表的"交集",如果是外連接,那么獲取到的值就是一側(cè)表的全部記錄.

還有一種連接是"全外連接",但是在MySQL中并不支持

5.3 自連接

自連接就是和自己進(jìn)行笛卡爾積
在條件查詢中,只是"列"和"列"之間的比較,但是有的地方需要用的 "行"和"行"之間的比較,就需要使用自連接,將"行"轉(zhuǎn)為"列"再進(jìn)行比較

例如這里有一張成績表

如果要查詢數(shù)學(xué)成績比語文成績高的同學(xué)的名字,就需要使用自連接.因為如果進(jìn)行比較,那么就是"行"和"行"進(jìn)行的比較

可以看到,如果是直接進(jìn)行連接,那么是會報錯的.
Not unique table/alias: 'grades': 這句話告訴我們不是唯一的表,但是可以起別名

通過起別名的方式,成功進(jìn)行自連接
加上連接條件,先篩選出一部分記錄,此時我們可以看到語文成績和數(shù)學(xué)成績就在兩列了

將條件補全,就可以得到我們想要的結(jié)果了

5.4 子查詢

子查詢本質(zhì)就是將多個查詢語組合成一個SQL語句,例如在查詢得到的臨時表上再次進(jìn)行查詢

例如:在班級表中,找到與"張三"班級相同的同學(xué)

查到"張三"的班級id這個想必大家都會

我們要將得到的這個結(jié)果繼續(xù)參與查詢:

此時就得到"李四"同學(xué)的這條記錄了,因為這里的班級id就只有一個,所以后面使用的是 = ,但是如果這個的臨時表數(shù)據(jù)有多條,就可以使用 in 來完成

5.5 合并查詢

合并查詢是把兩個查詢的結(jié)果集合合并到一起,使用的是union 和union all 這兩個關(guān)鍵字

union: 如果有重復(fù)的數(shù)據(jù),就會去重union all: 如果有重復(fù)的數(shù)據(jù),則不會去重

還是剛才的分?jǐn)?shù)表

如果我們要查詢數(shù)學(xué)成績>90和英語成績<60的人的姓名,就可以使用合并查詢

6. 總結(jié)

在SQL語句中,查詢數(shù)據(jù)的操作與其它操作語句相比還是有一些難度的,主要涉及到一些多表查詢等操作.對于里面涉及到的一些關(guān)鍵字,連接類型要熟練掌握

以上就是MySQL進(jìn)階查詢、聚合查詢和聯(lián)合查詢的詳細(xì)內(nèi)容，更多關(guān)于MySQL進(jìn)階、聚合、聯(lián)合查詢的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論