亚洲AV成人无码精品直播在线,大香蕉一级片,日本中文字幕在线,日批视频免费,午夜做爱视频,五月天在线高清无码,欧美久久伊人,人妻夜夜爽天天爽4区

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

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

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

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

然后我們向表中插入數(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ù)方法來計(jì)算次數(shù)即可

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

output

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

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

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ì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

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

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

output

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

帶有條件的分組查詢

假設(shè)我們打算查詢平均分大于80分的學(xué)生，首先我們要計(jì)算每個學(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ù)“課程號”來進(jìn)行分組，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默認(rèn)排序的方式是升序，我們也可以設(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()方法來進(jìn)行統(tǒng)計(jì)，同時對平均分與最高分進(jìn)行排序，當(dāng)平均分相同的時候，就以最高分來進(jìn)行排序

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來進(jìn)行排序。

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

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

output

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

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

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

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

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é)生的總成績進(jìn)行統(tǒng)計(jì)并且進(jìn)行排序是怎么來做的，我們用sum()方法來統(tǒng)計(jì)每個學(xué)生的總成績

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

然后我們用order by關(guān)鍵字來進(jì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)然我們其實(shí)只要“學(xué)號”這一列，然后我們在此基礎(chǔ)之上再進(jìn)行查詢

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

由于篇幅的限制，這里就不多說了，我們來看一下具體實(shí)踐當(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());

跨表查詢

有時候我們在進(jìn)行數(shù)據(jù)查詢的時候需要用到多張表格，將多張表格聯(lián)結(jié)起來進(jìn)行操作，例如列出所有學(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ù)的連接，尤其是多張表格的連接，后面會專門寫一篇文章來進(jìn)行說明的，今天的分享就到這里，后臺回復(fù)【sql面試】便可獲取源代碼





推薦閱讀:
入門:?最全的零基礎(chǔ)學(xué)Python的問題? |?零基礎(chǔ)學(xué)了8個月的Python??|?實(shí)戰(zhàn)項(xiàng)目?|學(xué)Python就是這條捷徑

干貨:爬取豆瓣短評，電影《后來的我們》?|?38年NBA最佳球員分析?|? ?從萬眾期待到口碑撲街！唐探3令人失望? |?笑看新倚天屠龍記?|?燈謎答題王?｜用Python做個海量小姐姐素描圖?｜碟中諜這么火，我用機(jī)器學(xué)習(xí)做個迷你推薦系統(tǒng)電影

趣味:彈球游戲? |?九宮格? |?漂亮的花?|?兩百行Python《天天酷跑》游戲!

AI:?會做詩的機(jī)器人?|?給圖片上色?|?預(yù)測收入?|?碟中諜這么火，我用機(jī)器學(xué)習(xí)做個迷你推薦系統(tǒng)電影

小工具:?Pdf轉(zhuǎn)Word，輕松搞定表格和水印！?|?一鍵把html網(wǎng)頁保存為pdf！|??再見PDF提取收費(fèi)！?|?用90行代碼打造最強(qiáng)PDF轉(zhuǎn)換器，word、PPT、excel、markdown、html一鍵轉(zhuǎn)換?|?制作一款釘釘?shù)蛢r機(jī)票提示器！?｜60行代碼做了一個語音壁紙切換器天天看小姐姐！｜

年度爆款文案

1).臥槽！Pdf轉(zhuǎn)Word用Python輕松搞定！
2).學(xué)Python真香！我用100行代碼做了個網(wǎng)站，幫人PS旅行圖片，賺個雞腿吃
3).首播過億，火爆全網(wǎng)，我分析了《乘風(fēng)破浪的姐姐》，發(fā)現(xiàn)了這些秘密?
4).80行代碼！用Python做一個哆來A夢分身?
5).你必須掌握的20個python代碼，短小精悍，用處無窮?
6).30個Python奇淫技巧集?
7).我總結(jié)的80頁《菜鳥學(xué)Python精選干貨.pdf》,都是干貨?
8).再見Python！我要學(xué)Go了！2500字深度分析！
9).發(fā)現(xiàn)一個舔狗福利！這個Python爬蟲神器太爽了，自動下載妹子圖片

干貨總結(jié)！太全面了，圖解SQL面試題：經(jīng)典30題！

簡單查詢

分組匯總查詢

帶有條件的分組查詢

匯總排序

嵌套式查詢

日期函數(shù)

跨表查詢

干貨總結(jié)！太全面了，圖解SQL面試題：經(jīng)典30題！