[[437763]]

今天我們來(lái)講講SQL方面的知識(shí)點(diǎn)，特別是對(duì)于絕大多數(shù)的數(shù)據(jù)分析師而言，SQL也是經(jīng)常會(huì)被面試問(wèn)到的內(nèi)容。

目前我們有下面這4張表格

•  學(xué)生表
•  教師表
•  成績(jī)表
•  課程表

我們先在Navicat當(dāng)中創(chuàng)建表格并且插入數(shù)據(jù)，在Navicat當(dāng)中創(chuàng)建表格并不難，點(diǎn)擊新建表，然后輸入字段名，并且規(guī)定好相應(yīng)的數(shù)據(jù)類型即可，點(diǎn)擊保存

然后我們向表中插入數(shù)據(jù)，語(yǔ)法如下 

insert into 表名(字段名1, 字段名2, 字段名3,.....)   
values('val1', 'val2', 'val3', ......); 

例如我們?cè)趯W(xué)生表當(dāng)中插入以下的數(shù)據(jù) 

insert into student(學(xué)號(hào),姓名,出生日期,性別)   
values('0001' , '張三' , '1991-05-01' , '男');  
insert into student(學(xué)號(hào),姓名,出生日期,性別)   
values('0002' , '李四' , '1990-04-21' , '男'); 

output

將其他數(shù)據(jù)插入到其他的表格當(dāng)中也是一樣的道理，這里就不做贅述了

簡(jiǎn)單查詢

例如查找學(xué)生名字以“小”開(kāi)頭的名單，可以這么做 

select * from student where 姓名 like '小%' 

output 

學(xué)號(hào) 姓名 出生日期 性別  
0003 小紅 1994-10-21 女  
0004 小王 1996-07-20 男  
0005 小張 1999-04-15 男  
0006 小美 1997-04-05 女  
0007 小麗 1995-07-10 女 

這里的%表示任意字符串，例如'小%'則表示姓“小”的學(xué)生信息，而'%小'表示的是以“小”字結(jié)尾的學(xué)生姓名，而'%小%'代表的是學(xué)生姓名中帶有“小”這個(gè)關(guān)鍵字

但是假如我們只是想要返回前面幾行的數(shù)據(jù)的話，就可以使用limit關(guān)鍵字 

select * from student where 姓名 like '小%' limit 3; 

output 

學(xué)號(hào) 姓名 出生日期 性別  
0003 小紅 1994-10-21 女  
0004 小王 1996-07-20 男  
0005 小張 1999-04-15 男 

分組匯總查詢

我們想要看一下報(bào)名參加“英語(yǔ)”課的學(xué)生有幾人，我們知道“英語(yǔ)”課程對(duì)應(yīng)的課程號(hào)是0003，所以用count()函數(shù)方法來(lái)計(jì)算次數(shù)即可 

select count(*) as 學(xué)生人數(shù) from score where 課程號(hào) = '0003'; 

output 

學(xué)生人數(shù)  
      6 

但要是我們想要查詢一下每一門(mén)課程的最低、最高分以及平均分，可以通過(guò)group by關(guān)鍵字來(lái)執(zhí)行，按照“課程號(hào)”進(jìn)行分組匯總 

select 課程號(hào), min(成績(jī)) as 最低分, max(成績(jī)) as 最高分, avg(成績(jī)) as 平均分 from score group by 課程號(hào) 

output 

課程號(hào) 最低分 最高分 平均分  
0001   80   88     84.0000  
0002   66   90     80.2500  
0003   69   97     84.1667  
0004   75   78     76.5000  
0005   97   99     98.0000 

鑒于上面的結(jié)果，我們也可以使用order by關(guān)鍵字來(lái)進(jìn)行排序，根據(jù)“平均分”這一列 

select 課程號(hào), min(成績(jī)) as 最低分, max(成績(jī)) as 最高分, avg(成績(jī)) as 平均分 from score group by 課程號(hào) order by 平均分 

output 

課程號(hào) 最低分 最高分 平均分  
0004 75 78 76.5000  
0002 66 90 80.2500  
0001 80 88 84.0000  
0003 69 97 84.1667  
0005 97 99 98.0000 

默認(rèn)的排序方式是升序排序，另外我們也可以嘗試通過(guò)性別來(lái)進(jìn)行分組查詢 

select count(*) as 不同性別的學(xué)生數(shù)量 from student group by 性別; 

output 

不同性別的學(xué)生數(shù)量  
              4  
              3 

帶有條件的分組查詢

假設(shè)我們打算查詢平均分大于80分的學(xué)生，首先我們要計(jì)算每個(gè)學(xué)生的平均成績(jī)，然后再此的基礎(chǔ)之上挑出80分以上的部分，所以可以group by 學(xué)號(hào) 

select 學(xué)號(hào), avg(成績(jī)) as 平均成績(jī) from score group by 學(xué)號(hào) having avg(成績(jī)) > 80; 

output 

學(xué)號(hào) 平均成績(jī)  
0001     91  
0003 81.66666666666667  
0004 81.33333333333333  
0005     91.5  
0006     91 

同理，我們也可以根據(jù)“課程號(hào)”來(lái)進(jìn)行分組，group by 課程號(hào)，然后挑選出例如平均分大于70分的部分 

select 課程號(hào), avg(成績(jī)) as 平均成績(jī) from score group by 課程號(hào) having avg(成績(jī)) > 70; 

output 

課程號(hào) 平均成績(jī)  
0001     84  
0002     80.25  
0003 84.16666666666667  
0004     76.5  
0005     98 

上面提到的order by默認(rèn)排序的方式是升序，我們也可以設(shè)置成降序來(lái)排序 

select 課程號(hào), avg(成績(jī)) as 平均成績(jī) from score group by 課程號(hào) having avg(成績(jī)) > 70 order by avg(成績(jī)) desc; 

output 

課程號(hào) 平均成績(jī)  
0005      98  
0003 84.16666666666667  
0001     84  
0002     80.25  
0004     76.5 

這次我們?cè)偬砑右粋€(gè)字段最高分，通過(guò)max()方法來(lái)進(jìn)行統(tǒng)計(jì)，同時(shí)對(duì)平均分與最高分進(jìn)行排序，當(dāng)平均分相同的時(shí)候，就以最高分來(lái)進(jìn)行排序 

select 課程號(hào), avg(成績(jī)) as 平均成績(jī), max(成績(jī)) as 最高分 from score group by 課程號(hào) having avg(成績(jī)) > 70 order by avg(成績(jī)), max(成績(jī)); 

output 

課程號(hào) 平均成績(jī) 最高分  
0004 76.5       78  
0002 80.25       90  
0001 84         88  
0003 84.1666667 97  
0005 98         99 

我們?cè)趏rder by后面再放一個(gè)字段表示當(dāng)以字段1排序的時(shí)候碰到相同的情況下，就以字段2來(lái)進(jìn)行排序。

我們?cè)賮?lái)?yè)Q一個(gè)字段，看一下選課超過(guò)兩門(mén)的學(xué)生有哪些，我們首先是group by 學(xué)號(hào)，然后對(duì)“課程號(hào)”進(jìn)行計(jì)數(shù)，挑選出滿足條件的部分 

select 學(xué)號(hào), count(課程號(hào)) as 選課的數(shù)量 from score group by 學(xué)號(hào) having count(課程號(hào)) > 2; 

output 

學(xué)號(hào) 選課的數(shù)量  
0001       3  
0003       3  
0004       3 

下面我們來(lái)處理一個(gè)復(fù)雜的查詢，找出分?jǐn)?shù)都在80分以上至少兩門(mén)課程的學(xué)生，列出他們的學(xué)號(hào)以及平均分，我們先來(lái)篩選出分?jǐn)?shù)都在80分以上的學(xué)生 

select 學(xué)號(hào) from score where 成績(jī) > 80 

然后我們計(jì)算出他們的平均分 

select 學(xué)號(hào), avg(成績(jī)) as 平均成績(jī) from score where 成績(jī) > 80 group by 學(xué)號(hào); 

output 

學(xué)號(hào) 平均成績(jī)  
0001     91  
0003     85  
0004     97  
0005     91.5 
0006     91 

最后再加上限制條件，“至少是兩門(mén)課程” 

select 學(xué)號(hào), avg(成績(jī)) as 平均成績(jī) from score where 成績(jī) > 80 group by 學(xué)號(hào) having count(課程號(hào)) >= 2;  

output 

學(xué)號(hào) 平均成績(jī)  
0001     91  
0005     91.5  
0006     91 

匯總排序

我們來(lái)看一下，對(duì)每位學(xué)生的總成績(jī)進(jìn)行統(tǒng)計(jì)并且進(jìn)行排序是怎么來(lái)做的，我們用sum()方法來(lái)統(tǒng)計(jì)每個(gè)學(xué)生的總成績(jī) 

select 學(xué)號(hào), sum(成績(jī)) from score group by 學(xué)號(hào); 

然后我們用order by關(guān)鍵字來(lái)進(jìn)行排序 

select 學(xué)號(hào), sum(成績(jī)) as 總分 from score group by 學(xué)號(hào) order by sum(成績(jī)); 

output 

學(xué)號(hào) 總分  
0007 75  
0002 141  
0006 182  
0005 183  
0004 244  
0003 245  
0001 273 

然后我們?cè)偬砑右粋€(gè)限制條件，例如挑選出總分在200分以上的數(shù)據(jù)，可以通過(guò)having關(guān)鍵詞來(lái)執(zhí)行 

select 學(xué)號(hào), sum(成績(jī)) as 總分 from score group by 學(xué)號(hào) having sum(成績(jī)) > 200 order by sum(成績(jī)); 

output 

學(xué)號(hào) 總分  
0004 244  
0003 245  
0001 273 

嵌套式查詢

有時(shí)候我們需要寫(xiě)多層的SQL查詢語(yǔ)句，一層的查詢有時(shí)候顯然不夠用，例如我們想要查詢出所有課程的成績(jī)都是高于80分的學(xué)生學(xué)號(hào)與姓名，我們一層一層來(lái)分析，首先我們篩選出所有課程都高于80分學(xué)生的學(xué)號(hào)與成績(jī) 

select 學(xué)號(hào), min(成績(jī)) as 最低分 from score group by 學(xué)號(hào) having min(成績(jī)) > 80; 

output 

學(xué)號(hào) 最低分  
0001   88  
0005   84  
0006   85 

再得到了學(xué)號(hào)以及成績(jī)了之后，我們將學(xué)號(hào)這一列提取出來(lái)，去student這張表當(dāng)中去尋找滿足條件的 

select 學(xué)號(hào),姓名 from student where 學(xué)號(hào) in (select 學(xué)號(hào) from score group by 學(xué)號(hào) having min(成績(jī)) > 80); 

output 

學(xué)號(hào) 姓名  
0001 張三  
0005 小張  
0006 小美 

通常來(lái)說(shuō)，嵌套式的查詢是涉及到了多張表格的聯(lián)合，例如我們想要查詢出選課的數(shù)量小于3門(mén)課程的學(xué)生姓名與學(xué)號(hào)，首先我們先篩選出選課數(shù)量小于3門(mén)課程的學(xué)生學(xué)號(hào) 

select 學(xué)號(hào), count(課程號(hào)) as 選課數(shù)量 from score group by 學(xué)號(hào) having count(課程號(hào)) <= 2; 

output 

學(xué)號(hào) 選課數(shù)量  
0002     2  
0005     2  
0006     2  
0007     1 

當(dāng)然我們其實(shí)只要“學(xué)號(hào)”這一列，然后我們?cè)诖嘶A(chǔ)之上再進(jìn)行查詢 

select 學(xué)號(hào),姓名 from student where 學(xué)號(hào) in (select 學(xué)號(hào) from score group by 學(xué)號(hào) having count(課程號(hào)) <= 2); 

output 

學(xué)號(hào) 姓名  
0002 李四  
0005 小張  
0006 小美  
0007 小麗 

日期函數(shù)

下面我們來(lái)演練一下日期函數(shù)的使用，例如我們想獲取當(dāng)前的日期，可以用curdate()方法

select curdate(); 

output 

curdate()  
2021-11-16 

若是打算獲取當(dāng)前的時(shí)刻，則可以用now()方法

select now(); 

output 

now()  
2021-11-16 22:37:41 

由于篇幅的限制，這里就不多說(shuō)了，我們來(lái)看一下具體實(shí)踐當(dāng)中的操作，我們篩選出出生年份在1994年的學(xué)生有哪些，可以這么來(lái)操作 

select * from student where year(出生日期) = 1994; 

output 

學(xué)號(hào) 姓名 出生日期 性別  
0003 小紅 1994-10-21 女 

同理我們來(lái)篩選出當(dāng)月過(guò)生日的同學(xué) 

select * from student where month(出生日期) = month(now()); 

跨表查詢

有時(shí)候我們?cè)谶M(jìn)行數(shù)據(jù)查詢的時(shí)候需要用到多張表格，將多張表格聯(lián)結(jié)起來(lái)進(jìn)行操作，例如列出所有學(xué)生的姓名、選課的數(shù)量以及總分成績(jī)，我們就需要用到多張表格了，我們可以先嘗試著連接student和score這兩張表 

select * from student left join score on student.`學(xué)號(hào)` = score.`學(xué)號(hào)`; 

output 

學(xué)號(hào) 姓名 出生日期 性別 學(xué)號(hào)(1) 課程號(hào) 成績(jī)  
0001 張三 1991-05-01 男 0001 0001 88  
0001 張三 1991-05-01 男 0001 0002 90  
0001 張三 1991-05-01 男 0001 0003 95  
0002 李四 1990-04-21 男 0002 0002 66  
0002 李四 1990-04-21 男 0002 0003 75  
0003 小紅 1994-10-21 女 0003 0001 80  
0003 小紅 1994-10-21 女 0003 0002 80  
0003 小紅 1994-10-21 女 0003 0003 85  
0004 小王 1996-07-20 男 0004 0003 69  
0004 小王 1996-07-20 男 0004 0004 78  
0004 小王 1996-07-20 男 0004 0005 97  
..... 

然后再此基礎(chǔ)之上，我們保留需要的這幾個(gè)字段 

select 姓名, count(課程號(hào)) as 選課的數(shù)量, sum(成績(jī)) as 總成績(jī) from student left join score on student.`學(xué)號(hào)` = score.`學(xué)號(hào)` group by student.`姓名`; 

output 

姓名 選課的數(shù)量 總成績(jī)  
張三         3   273  
李四         2   141  
小紅         3   245  
小王         3   244  
小張         2   183  
小美         2   182  
小麗         1   75 

我們也可以將總成績(jī)替換成平均成績(jī)，然后做一個(gè)排序，取平均分最高的前三名 

select 姓名, avg(成績(jī)) as 平均分 from student left join score on student.`學(xué)號(hào)` = score.`學(xué)號(hào)` group by student.`姓名` limit 3; 

output 

姓名 平均分  
張三 91.0000  
李四 70.5000  
小紅 81.6667 

最后通過(guò)limit關(guān)鍵字來(lái)控制輸出，上面的例子是兩張表格的連接，我們也可以嘗試三張表格的連接， 

select * from student inner join score on student.`學(xué)號(hào)` = score.`學(xué)號(hào)` inner join course on score.`課程號(hào)` = course.`課程號(hào)`; 

output 

學(xué)號(hào) 姓名 出生日期 性別 學(xué)號(hào)(1) 課程號(hào) 成績(jī) 課程號(hào)(1) 課程名稱 教師號(hào)  
0001 張三 1991-05-01 男 0001 0001 88 0001 語(yǔ)文 0002  
0001 張三 1991-05-01 男 0001 0002 90 0002 數(shù)學(xué) 0001  
0001 張三 1991-05-01 男 0001 0003 95 0003 英語(yǔ) 0003  
0002 李四 1990-04-21 男 0002 0002 66 0002 數(shù)學(xué) 0001  
0002 李四 1990-04-21 男 0002 0003 75 0003 英語(yǔ) 0003  
...... 

當(dāng)然我們也可以四張表格來(lái)連接 

select * from student inner join score on student.`學(xué)號(hào)` = score.`學(xué)號(hào)` inner join course on score.`課程號(hào)` = course.`課程號(hào)` inner join teacher on course.`教師號(hào)` = teacher.`教師號(hào)`; 

要是我們想查詢學(xué)生姓名、選課的課程名稱以及授課的老師，可以這么來(lái)做 

select 姓名, 課程名稱, 教師姓名 from student inner join score on student.`學(xué)號(hào)` = score.`學(xué)號(hào)` inner join course on score.`課程號(hào)` = course.`課程號(hào)` inner join teacher on course.`教師號(hào)` = teacher.`教師號(hào)`; 

output 

姓名 課程名稱 教師姓名  
張三 語(yǔ)文 馬老師  
張三 數(shù)學(xué) 王老師  
張三 英語(yǔ)   
李四 數(shù)學(xué) 王老師  
李四 英語(yǔ)   
小紅 語(yǔ)文 馬老師  
小紅 數(shù)學(xué) 王老師  
小紅 英語(yǔ)   
小王 英語(yǔ)   
小王 物理 張老師  
........ 

當(dāng)然我們也可以將“成績(jī)”這一關(guān)鍵字也給加上 

select 姓名, 課程名稱, 成績(jī), 教師姓名 from student inner join score on student.`學(xué)號(hào)` = score.`學(xué)號(hào)` inner join course on score.`課程號(hào)` = course.`課程號(hào)` inner join teacher on course.`教師號(hào)` = teacher.`教師號(hào)`; 

output 

姓名 課程名稱 成績(jī) 教師姓名  
張三 語(yǔ)文     88   馬老師  
張三 數(shù)學(xué)     90   王老師  
張三 英語(yǔ)     95   
李四 數(shù)學(xué)     66   王老師  
李四 英語(yǔ)     75   
小紅 語(yǔ)文     80   馬老師  
....... 

我們同時(shí)可以在后面添加一些篩選條件，例如我們想要找出語(yǔ)文的成績(jī)?cè)?5分以上的同學(xué)學(xué)號(hào)以及姓名，就可以這么做 

select student.`學(xué)號(hào)`, 姓名, 成績(jī) from student inner join score on student.`學(xué)號(hào)` = score.`學(xué)號(hào)` inner join course on score.`課程號(hào)` = course.`課程號(hào)` inner join teacher on course.`教師號(hào)` = teacher.`教師號(hào)` where 課程名稱 = '語(yǔ)文' and 成績(jī) > 85; 

output 

學(xué)號(hào) 姓名 成績(jī)  
0001 張三 88 

關(guān)于sql當(dāng)中表格數(shù)據(jù)的連接，尤其是多張表格的連接。