干貨總結！太全面了，圖解SQL面試題：經典30題！

作者：佚名 2021-12-01 09:25:58

數據庫

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

[[437763]]

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

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

學生表
教師表
成績表
課程表

我們先在Navicat當中創建表格并且插入數據，在Navicat當中創建表格并不難，點擊新建表，然后輸入字段名，并且規定好相應的數據類型即可，點擊保存

然后我們向表中插入數據，語法如下

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

例如我們在學生表當中插入以下的數據

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

output

將其他數據插入到其他的表格當中也是一樣的道理，這里就不做贅述了

簡單查詢

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

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

output

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

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

但是假如我們只是想要返回前面幾行的數據的話，就可以使用limit關鍵字

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

output

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

分組匯總查詢

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

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

output

學生人數  
      6

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

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

鑒于上面的結果，我們也可以使用order by關鍵字來進行排序，根據“平均分”這一列

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 不同性別的學生數量 from student group by 性別;

output

不同性別的學生數量  
              4  
              3

帶有條件的分組查詢

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

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

output

學號 平均成績  
0001     91  
0003 81.66666666666667  
0004 81.33333333333333  
0005     91.5  
0006     91

同理，我們也可以根據“課程號”來進行分組，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默認排序的方式是升序，我們也可以設置成降序來排序

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()方法來進行統計，同時對平均分與最高分進行排序，當平均分相同的時候，就以最高分來進行排序

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后面再放一個字段表示當以字段1排序的時候碰到相同的情況下，就以字段2來進行排序。

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

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

output

學號 選課的數量  
0001       3  
0003       3  
0004       3

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

select 學號 from score where 成績 > 80

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

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

output

學號 平均成績  
0001     91  
0003     85  
0004     97  
0005     91.5 
0006     91

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

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

output

學號 平均成績  
0001     91  
0005     91.5  
0006     91

匯總排序

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

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

然后我們用order by關鍵字來進行排序

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

output

然后我們再添加一個限制條件，例如挑選出總分在200分以上的數據，可以通過having關鍵詞來執行

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

output

嵌套式查詢

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

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

output

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

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

output

學號 姓名  
0001 張三  
0005 小張  
0006 小美

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

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

output

當然我們其實只要“學號”這一列，然后我們在此基礎之上再進行查詢

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

output

學號 姓名  
0002 李四  
0005 小張  
0006 小美  
0007 小麗

日期函數

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

select curdate();

output

curdate()  
2021-11-16

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

select now();

output

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

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

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

output

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

同理我們來篩選出當月過生日的同學

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

跨表查詢

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

select * from student left join score on student.`學號` = score.`學號`;

output

學號 姓名 出生日期 性別 學號(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  
.....

然后再此基礎之上，我們保留需要的這幾個字段

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

output

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

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

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

output

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

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

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

output

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

當然我們也可以四張表格來連接

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

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

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

output

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

當然我們也可以將“成績”這一關鍵字也給加上

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

output

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

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

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

output

學號 姓名 成績  
0001 張三 88

關于sql當中表格數據的連接，尤其是多張表格的連接。

責任編輯：龐桂玉來源：菜鳥學Python

SQL 數據庫數據庫基礎