1. 前言

文章主要圍繞著以下三個(gè)問題:

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

2. 表的設(shè)計(jì)

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

2.1 一對(duì)一

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

2.2 一對(duì)多

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

2.3 多對(duì)多

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

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

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

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

示例:

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

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

4. 聚合查詢

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

4.1 聚合函數(shù)

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

示例:

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

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

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

4.2 GROUP BY

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

例如:

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

成績(jī)?yōu)?7的有一個(gè),69的有兩個(gè),82的有三個(gè)

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

4.3 HAVING

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

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

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

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

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

聯(lián)合查詢一般是在多表之間建立連接后查詢的過程.其實(shí)就是計(jì)算"笛卡爾積"的過程
但是當(dāng)表很大的時(shí)候,如果進(jìn)行聯(lián)合查詢,效率就會(huì)特別低.因?yàn)?quot;笛卡爾積"就是簡(jiǎ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 條件;

舉個(gè)例子:

學(xué)生表:

班級(jí)表:

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

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

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

2. 添加連接條件

注意這里的條件寫法,應(yīng)該是表名.字段名,因?yàn)樯婕暗蕉鄠€(gè)表,多個(gè)表中的字段名可能相同,因此需要使用 表名.字段 表明是哪個(gè)表中的字段.當(dāng)然如果這個(gè)列名是唯一的,也可以不加 表名.

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

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

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

2. 添加條件

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

5.2 外連接

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

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

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

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

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

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

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

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

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

5.3 自連接

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

例如這里有一張成績(jī)表

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

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

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

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

5.4 子查詢

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

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

查到"張三"的班級(jí)id這個(gè)想必大家都會(huì)

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

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

5.5 合并查詢

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

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

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

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

6. 總結(jié)

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

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

最新評(píng)論