[[437763]]

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

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

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

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

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

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

例如我們在學(xué)生表當(dāng)中插入以下的數(shù)據(jù) 

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

output

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

簡單查詢

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

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

output 

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

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

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

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

output 

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

分組匯總查詢

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

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

output 

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

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

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

output 

課程號 最低分 最高分 平均分  
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)鍵字來進行排序，根據(jù)“平均分”這一列 

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

output 

課程號 最低分 最高分 平均分  
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 

默認的排序方式是升序排序，另外我們也可以嘗試通過性別來進行分組查詢 

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

output 

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

帶有條件的分組查詢

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

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

output 

學(xué)號 平均成績  
0001     91  
0003 81.66666666666667  
0004 81.33333333333333  
0005     91.5  
0006     91 

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

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

output 

課程號 平均成績  
0001     84  
0002     80.25  
0003 84.16666666666667  
0004     76.5  
0005     98 

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

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

output 

課程號 平均成績  
0005      98  
0003 84.16666666666667  
0001     84  
0002     80.25  
0004     76.5 

這次我們再添加一個字段最高分，通過max()方法來進行統(tǒng)計，同時對平均分與最高分進行排序，當(dāng)平均分相同的時候，就以最高分來進行排序 

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

output 

課程號 平均成績 最高分  
0004 76.5       78  
0002 80.25       90  
0001 84         88  
0003 84.1666667 97  
0005 98         99 

我們在order by后面再放一個字段表示當(dāng)以字段1排序的時候碰到相同的情況下，就以字段2來進行排序。

我們再來換一個字段，看一下選課超過兩門的學(xué)生有哪些，我們首先是group by 學(xué)號，然后對“課程號”進行計數(shù)，挑選出滿足條件的部分 

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

output 

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

下面我們來處理一個復(fù)雜的查詢，找出分數(shù)都在80分以上至少兩門課程的學(xué)生，列出他們的學(xué)號以及平均分，我們先來篩選出分數(shù)都在80分以上的學(xué)生 

select 學(xué)號 from score where 成績 > 80 

然后我們計算出他們的平均分 

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

output 

學(xué)號 平均成績  
0001     91  
0003     85  
0004     97  
0005     91.5 
0006     91 

最后再加上限制條件，“至少是兩門課程” 

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

output 

學(xué)號 平均成績  
0001     91  
0005     91.5  
0006     91 

匯總排序

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

select 學(xué)號, sum(成績) from score group by 學(xué)號; 

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

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

output 

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

然后我們再添加一個限制條件，例如挑選出總分在200分以上的數(shù)據(jù)，可以通過having關(guān)鍵詞來執(zhí)行 

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

output 

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

嵌套式查詢

有時候我們需要寫多層的SQL查詢語句，一層的查詢有時候顯然不夠用，例如我們想要查詢出所有課程的成績都是高于80分的學(xué)生學(xué)號與姓名，我們一層一層來分析，首先我們篩選出所有課程都高于80分學(xué)生的學(xué)號與成績 

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

output 

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

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

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

output 

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

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

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

output 

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

當(dāng)然我們其實只要“學(xué)號”這一列，然后我們在此基礎(chǔ)之上再進行查詢 

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

output 

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

日期函數(shù)

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

select curdate(); 

output 

curdate()  
2021-11-16 

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

select now(); 

output 

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

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

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

output 

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

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

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

跨表查詢

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

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

output 

學(xué)號 姓名 出生日期 性別 學(xué)號(1) 課程號 成績  
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ǔ)之上，我們保留需要的這幾個字段 

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

output 

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

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

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

output 

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

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

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

output 

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

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

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

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

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

output 

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

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

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

output 

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

我們同時可以在后面添加一些篩選條件，例如我們想要找出語文的成績在85分以上的同學(xué)學(xué)號以及姓名，就可以這么做 

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

output 

學(xué)號 姓名 成績  
0001 張三 88 

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