最新最需要的SQL面試題大匯總

最新最需要的SQL面試題大匯總

sql基礎(chǔ)知識整理:

  • select 查詢結(jié)果? ? 如: [學(xué)號,平均成績:組函數(shù)avg(成績)]

  • from 從哪張表中查找數(shù)據(jù)? ?如:[涉及到成績:成績表score]

  • where 查詢條件? ? 如:[b.課程號=’0003′ and b.成績>80]

  • group by 分組? ? 如:[每個學(xué)生的平均:按學(xué)號分組](oracle,SQL server中出現(xiàn)在select 子句后的非分組函數(shù),必須出現(xiàn)在group by子句后出現(xiàn)),mysql中可以不用

  • having 對分組結(jié)果指定條件? ? 如:[大于60分]

  • order by 對查詢結(jié)果排序? ? 如:[增序: 成績? ASC / 降序: 成績 DESC];

  • limit? ?使用limt子句返回topN(對應(yīng)這個問題返回的成績前兩名)如:[ limit? 2 ==>從0索引開始讀取2個]limit==>從0索引開始 [0,N-1]

①?select?*?from?table?limit?2,1;????????????????  //含義是跳過2條取出1條數(shù)據(jù),limit后面是從第2條開始讀,讀取1條信息,即讀取第3條數(shù)據(jù)  ②?select?*?from?table?limit?2?offset?1;?????  //含義是從第1條(不包括)數(shù)據(jù)開始取出2條數(shù)據(jù),limit后面跟的是2條數(shù)據(jù),offset后面是從第1條開始讀取,即讀取第2,3條

組函數(shù): 去重 distinct()??統(tǒng)計(jì)總數(shù)sum()? ?計(jì)算個數(shù)count()? 平均數(shù)avg()? 最大值max() 最小數(shù)min()?

多表連接: 內(nèi)連接(省略默認(rèn)inner) join?…on..左連接left join?tableName as b?on a.key ==b.key右連接right join? 連接union(無重復(fù)(過濾去重))和union all(有重復(fù)[不過濾去重])

union 并集union all(有重復(fù))oracle(SQL server)數(shù)據(jù)庫

  • intersect 交集?

  • minus(except) 相減(差集)

最新最需要的SQL面試題大匯總

oracle

一、數(shù)據(jù)庫對象: 表(table)? 視圖(view)? 序列(sequence)? 索引(index)? 同義詞(synonym)

1. 視圖: 存儲起來的 select 語句

create?view?emp_vw as select?employee_id,?last_name,?salary from?employees where?department_id?=?90;  select?*?from?emp_vw;

可以對簡單視圖進(jìn)行 DML 操作

update?emp_vw set?last_name?=?'HelloKitty' where?employee_id?=?100;  select?*?from?employees where?employee_id?=?100;

1). 復(fù)雜視圖

create?view?emp_vw2 as select?department_id,?avg(salary)?avg_sal from?employees group?by?department_id;  select?*?from?emp_vw2;

復(fù)雜視圖不能進(jìn)行 DML 操作

update?emp_vw2 set?avg_sal?=?10000 where?department_id?=?100;

2. 序列:用于生成一組有規(guī)律的數(shù)值。(通常用于為主鍵設(shè)置值)

create?sequence?emp_seq1 start?with?1 increment?by?1 maxvalue?10000 minvalue?1 cycle nocache;  select?emp_seq1.currval?from?dual;  select?emp_seq1.nextval?from?dual;

問題:裂縫 . ?原因:

  • 當(dāng)多個表共用同一個序列時。 ?

  • rollback ?

  • 發(fā)生異常

create?table?emp1( ???????id?number(10), ???????name?varchar2(30) );  insert?into?emp1 values(emp_seq1.nextval,?'張三');  select?*?from?emp1;

3. 索引:提高查詢效率

自動創(chuàng)建:Oracle 會為具有唯一約束(唯一約束,主鍵約束)的列,自動創(chuàng)建索引

create?table?emp2( ???????id?number(10)?primary?key, ???????name?varchar2(30) )

手動創(chuàng)建

create?index?emp_idx on?emp2(name);  create?index?emp_idx2 on?emp2(id,?name);

4. 同義詞

create?synonym?d1?for?departments; select?*?from?d1;

5. 表:

DDL :數(shù)據(jù)定義語言 create table …/ drop table … / rename … to…./ truncate table…/alter table …

DML : 數(shù)據(jù)操縱語言

insert?into?...?values?... update?...?set?...?where?... delete?from?...?where?...

【重要】

  • select … 組函數(shù)(MIN()/MAX()/SUM()/AVG()/COUNT())

  • from …join … on … 左外連接:left join … on … 右外連接: right join … on …

  • where …?

  • group by … (oracle,SQL server中出現(xiàn)在select 子句后的非分組函數(shù),必須出現(xiàn)在 group by子句后)

  • having … 用于過濾 組函數(shù)

  • order by … asc 升序, desc 降序

  • limit (0,4) 限制N條數(shù)據(jù) 如: topN數(shù)據(jù)

  • union 并集

  • union all(有重復(fù))

  • intersect 交集?

  • minus 相減

DCL : 數(shù)據(jù)控制語言 ?commit : 提交 / rollback : 回滾 / 授權(quán)grant…to… ?/revoke?

最新最需要的SQL面試題大匯總

索引

最新最需要的SQL面試題大匯總

最新最需要的SQL面試題大匯總

最新最需要的SQL面試題大匯總

何時創(chuàng)建索引:

最新最需要的SQL面試題大匯總

最新最需要的SQL面試題大匯總

最新最需要的SQL面試題大匯總

最新最需要的SQL面試題大匯總

一、

select?employee_id,?last_name,?salary,?department_id from?employees where?department_id?in?(70,?80)?-->?70:1??80:34
  • union 并集

  • union all(有重復(fù)部分)

  • intersect 交集?

  • minus 相減

select?employee_id,?last_name,?salary,?department_id from?employees where?department_id?in?(80,?90)??-->?90:4??80:34

問題:查詢工資大于149號員工工資的員工的信息

select?*? from?employees where?salary?>?( ??????select?salary ??????from?employees ??????where?employee_id?=?149 )

問題:查詢與141號或174號員工的manager_id和department_id相同的其他員工的

employee_id,?manager_id,?department_id?? select?employee_id,?manager_id,?department_id from?employees where?manager_id?in?( ??????select?manager_id ??????from?employees ??????where?employee_id?in(141,?174) )?and?department_id?in?( ??????select?department_id ??????from?employees ??????where?employee_id?in(141,?174) )?and?employee_id?not?in?(141,?174);  select?employee_id,?manager_id,?department_id from?employees where?(manager_id,?department_id)?in?( ??????select?manager_id,?department_id ??????from?employees ??????where?employee_id?in?(141,?174) )?and?employee_id?not?in(141,?174);

1. from 子句中使用子查詢

select?max(avg(salary)) from?employees group?by?department_id;  select?max(avg_sal) from?( ??????select?avg(salary)?avg_sal ??????from?employees ??????group?by?department_id )?e

問題:返回比本部門平均工資高的員工的last_name, department_id, salary及平均工資

select?last_name,?department_id,?salary,?(select?avg(salary)?from?employees?where?department_id?=?e1.department_id) from?employees?e1 where?salary?>?( ??????select?avg(salary) ??????from?employees?e2 ??????where?e1.department_id?=?e2.department_id )  select?last_name,?e1.department_id,?salary,?avg_sal from?employees?e1,?( ?????select?department_id,?avg(salary)?avg_sal ?????from?employees ?????group?by?department_id )?e2 where?e1.department_id?=?e2.department_id and?e1.salary?>?e2.avg_sal;

case…when … then… when … then … else … end?

查詢:若部門為10 查看工資的 1.1 倍,部門號為 20 工資的1.2倍,其余 1.3 倍

select?employee_id,?last_name,?salary,?case?department_id?when?10?then?salary?*?1.1 ??????????????????????????????????????????????????????????when?20?then?salary?*?1.2 ??????????????????????????????????????????????????????????else?salary?*?1.3 ??????????????????????????????????????????????????????????end?"new_salary" from?employees;  select?employee_id,?last_name,?salary,?decode(department_id,?10,?salary?*?1.1, ?????????????????????????????????????????????????????????????20,?salary?*?1.2, ?????????????????????????????????????????????????????????????salary?*?1.3)?"new_salary" from?employees;

問題:顯式員工的employee_id,last_name和location。其中,若員工department_id與location_id為1800的department_id相同,則location為’Canada’,其余則為’USA’。

select?employee_id,?last_name,?case?department_id?when?( ????????????????????select?department_id ????????????????????from?departments ????????????????????where?location_id?=?1800 )?then?'Canada'?else?'USA'?end?"location" from?employees;

問題:查詢員工的employee_id,last_name,要求按照員工的department_name排序

select?employee_id,?last_name from?employees?e1 order?by?( ??????select?department_name ??????from?departments?d1 ??????where?e1.department_id?=?d1.department_id )

SQL 優(yōu)化:能使用 EXISTS 就不要使用 IN

問題:查詢公司管理者的employee_id,last_name,job_id,department_id信息

select?employee_id,?last_name,?job_id,?department_id from?employees where?employee_id?in?( ??????select?manager_id ??????from?employees )   select?employee_id,?last_name,?job_id,?department_id from?employees?e1 where?exists?( ??????select?'x' ??????from?employees?e2 ??????where?e1.employee_id?=?e2.manager_id )

問題:查詢departments表中,不存在于employees表中的部門的department_id和department_name

select?department_id,?department_name from?departments?d1 where?not?exists?( ??????select?'x' ??????from?employees?e1 ??????where?e1.department_id?=?d1.department_id )

更改 108 員工的信息: 使其工資變?yōu)樗诓块T中的最高工資, job 變?yōu)楣局衅骄べY最低的 job? ??

update?employees?e1 set?salary?=?( ????select?max(salary) ????from?employees?e2 ????where?e1.department_id?=?e2.department_id ),?job_id?=?( ???select?job_id ???from?employees ???group?by?job_id ???having?avg(salary)?=?( ?????????select?min(avg(salary)) ?????????from?employees ?????????group?by?job_id ???) ) where?employee_id?=?108;

56. 刪除 108 號員工所在部門中工資最低的那個員工.

delete?from?employees?e1 where?salary?=?( ??????select?min(salary) ??????from?employees ??????where?department_id?=?( ????????????select?department_id ????????????from?employees ????????????where?employee_id?=?108 ??????) )  select?*?from?employees?where?employee_id?=?108; select?*?from?employees?where?department_id?=?100 order?by?salary;  rollback;

常見的SQL面試題:經(jīng)典50題

已知有如下4張表:

學(xué)生表:student(學(xué)號,學(xué)生姓名,出生年月,性別)

成績表:score(學(xué)號,課程號,成績)

課程表:course(課程號,課程名稱,教師號)

教師表:teacher(教師號,教師姓名)

根據(jù)以上信息按照下面要求寫出對應(yīng)的SQL語句。

ps:這些題考察SQL的編寫能力,對于這類型的題目,需要你先把4張表之間的關(guān)聯(lián)關(guān)系搞清楚了,最好的辦法是自己在草稿紙上畫出關(guān)聯(lián)圖,然后再編寫對應(yīng)的SQL語句就比較容易了。下圖是我畫的這4張表的關(guān)系圖,可以看出它們之間是通過哪些外鍵關(guān)聯(lián)起來的:

最新最需要的SQL面試題大匯總

一、創(chuàng)建數(shù)據(jù)庫和表

為了演示題目的運(yùn)行過程,我們先按下面語句在客戶端navicat中創(chuàng)建數(shù)據(jù)庫和表。

(如何你還不懂什么是數(shù)據(jù)庫,什么是客戶端navicat,可以先學(xué)習(xí)這個:

最新最需要的SQL面試題大匯總

1.創(chuàng)建表

1)創(chuàng)建學(xué)生表(student)

按下圖在客戶端navicat里創(chuàng)建學(xué)生表

最新最需要的SQL面試題大匯總

學(xué)生表的“學(xué)號”列設(shè)置為主鍵約束,下圖是每一列設(shè)置的數(shù)據(jù)類型和約束

最新最需要的SQL面試題大匯總

創(chuàng)建完表,點(diǎn)擊“保存”

最新最需要的SQL面試題大匯總

2)創(chuàng)建成績表(score)

同樣的步驟,創(chuàng)建”成績表“。“課程表的“學(xué)號”和“課程號”一起設(shè)置為主鍵約束(聯(lián)合主鍵),“成績”這一列設(shè)置為數(shù)值類型Float,浮點(diǎn)數(shù)值)

最新最需要的SQL面試題大匯總

3)創(chuàng)建課程表(course)

課程表的“課程號”設(shè)置為主鍵約束

最新最需要的SQL面試題大匯總

4)教師表(teacher)

教師表的“教師號”列設(shè)置為主鍵約束,

教師姓名這一列設(shè)置約束為“NULL”(紅框的地方不勾選),表示這一列允許包含空值(null)

最新最需要的SQL面試題大匯總

2.向表中添加數(shù)據(jù)

最新最需要的SQL面試題大匯總

1)向?qū)W生表里添加數(shù)據(jù)

添加數(shù)據(jù)的sql

insert?into?student(學(xué)號,姓名,出生日期,性別)? values('0001'?,?'猴子'?,?'1989-01-01'?,?'男');  insert?into?student(學(xué)號,姓名,出生日期,性別)? values('0002'?,?'猴子'?,?'1990-12-21'?,?'女');  insert?into?student(學(xué)號,姓名,出生日期,性別)? values('0003'?,?'馬云'?,?'1991-12-21'?,?'男');  insert?into?student(學(xué)號,姓名,出生日期,性別)? values('0004'?,?'王思聰'?,?'1990-05-20'?,?'男');

在客戶端navicat里的操作

最新最需要的SQL面試題大匯總

最新最需要的SQL面試題大匯總

最新最需要的SQL面試題大匯總

2)成績表(score)

添加數(shù)據(jù)的sql

insert?into?score(學(xué)號,課程號,成績)? values('0001'?,?'0001'?,?80);  insert?into?score(學(xué)號,課程號,成績)? values('0001'?,?'0002'?,?90);  insert?into?score(學(xué)號,課程號,成績)? values('0001'?,?'0003'?,?99);  insert?into?score(學(xué)號,課程號,成績)? values('0002'?,?'0002'?,?60);  insert?into?score(學(xué)號,課程號,成績)? values('0002'?,?'0003'?,?80);  insert?into?score(學(xué)號,課程號,成績)? values('0003'?,?'0001'?,?80);  insert?into?score(學(xué)號,課程號,成績)? values('0003'?,?'0002'?,?80);  insert?into?score(學(xué)號,課程號,成績)? values('0003'?,?'0003'?,?80);

客戶端navicat里的操作

最新最需要的SQL面試題大匯總

3)課程表

添加數(shù)據(jù)的sql

insert?into?course(課程號,課程名稱,教師號) values('0001'?,?'語文'?,?'0002');  insert?into?course(課程號,課程名稱,教師號) values('0002'?,?'數(shù)學(xué)'?,?'0001');  insert?into?course(課程號,課程名稱,教師號) values('0003'?,?'英語'?,?'0003');

客戶端navicat里的操作

最新最需要的SQL面試題大匯總

4)教師表里添加數(shù)據(jù)

添加數(shù)據(jù)的sql

--?教師表:添加數(shù)據(jù) insert?into?teacher(教師號,教師姓名)? values('0001'?,?'孟扎扎');  insert?into?teacher(教師號,教師姓名)? values('0002'?,?'馬化騰');  --?這里的教師姓名是空值(null) insert?into?teacher(教師號,教師姓名)? values('0003'?,?null);  --?這里的教師姓名是空字符串('') insert?into?teacher(教師號,教師姓名)? values('0004'?,?'');

客戶端navicat里操作

最新最需要的SQL面試題大匯總

添加結(jié)果

最新最需要的SQL面試題大匯總

三、50道面試題

為了方便學(xué)習(xí),我將50道面試題進(jìn)行了分類

最新最需要的SQL面試題大匯總

查詢姓“猴”的學(xué)生名單

最新最需要的SQL面試題大匯總

查詢姓“孟”老師的個數(shù)

select?count(教師號) from?teacher where?教師姓名?like?'孟%';

2.匯總統(tǒng)計(jì)分組分析

最新最需要的SQL面試題大匯總

面試題:查詢課程編號為“0002”的總成績

/* 分析思路 select?查詢結(jié)果?[總成績:匯總函數(shù)sum] from?從哪張表中查找數(shù)據(jù)[成績表score] where?查詢條件?[課程號是0002] */ select?sum(成績) from?score where?課程號?=?'0002';

查詢選了課程的學(xué)生人數(shù)

/* 這個題目翻譯成大白話就是:查詢有多少人選了課程 select?學(xué)號,成績表里學(xué)號有重復(fù)值需要去掉 from?從課程表查找score; */ select?count(distinct?學(xué)號)?as?學(xué)生人數(shù)? from?score;

最新最需要的SQL面試題大匯總

查詢各科成績最高和最低的分, 以如下的形式顯示:課程號,最高分,最低分

/* 分析思路 select?查詢結(jié)果?[課程ID:是課程號的別名,最高分:max(成績)?,最低分:min(成績)] from?從哪張表中查找數(shù)據(jù)?[成績表score] where?查詢條件?[沒有] group?by?分組?[各科成績:也就是每門課程的成績,需要按課程號分組]; */ select?課程號,max(成績)?as?最高分,min(成績)?as?最低分 from?score group?by?課程號;

查詢每門課程被選修的學(xué)生數(shù)

/* 分析思路 select?查詢結(jié)果?[課程號,選修該課程的學(xué)生數(shù):匯總函數(shù)count] from?從哪張表中查找數(shù)據(jù)?[成績表score] where?查詢條件?[沒有] group?by?分組?[每門課程:按課程號分組]; */ select?課程號,?count(學(xué)號) from?score group?by?課程號;

查詢男生、女生人數(shù)

/* 分析思路 select?查詢結(jié)果?[性別,對應(yīng)性別的人數(shù):匯總函數(shù)count] from?從哪張表中查找數(shù)據(jù)?[性別在學(xué)生表中,所以查找的是學(xué)生表student] where?查詢條件?[沒有] group?by?分組?[男生、女生人數(shù):按性別分組] having?對分組結(jié)果指定條件?[沒有] order?by?對查詢結(jié)果排序[沒有]; */ select?性別,count(*) from?student group?by?性別;

最新最需要的SQL面試題大匯總

查詢平均成績大于60分學(xué)生的學(xué)號和平均成績

/*? 題目翻譯成大白話: 平均成績:展開來說就是計(jì)算每個學(xué)生的平均成績 這里涉及到“每個”就是要分組了 平均成績大于60分,就是對分組結(jié)果指定條件  分析思路 select?查詢結(jié)果?[學(xué)號,平均成績:匯總函數(shù)avg(成績)] from?從哪張表中查找數(shù)據(jù)?[成績在成績表中,所以查找的是成績表score] where?查詢條件?[沒有] group?by?分組?[平均成績:先按學(xué)號分組,再計(jì)算平均成績] having?對分組結(jié)果指定條件?[平均成績大于60分] */ select?學(xué)號,?avg(成績) from?score group?by?學(xué)號 having?avg(成績)>60;

查詢至少選修兩門課程的學(xué)生學(xué)號

/*? 翻譯成大白話: 第1步,需要先計(jì)算出每個學(xué)生選修的課程數(shù)據(jù),需要按學(xué)號分組 第2步,至少選修兩門課程:也就是每個學(xué)生選修課程數(shù)目>=2,對分組結(jié)果指定條件  分析思路 select?查詢結(jié)果?[學(xué)號,每個學(xué)生選修課程數(shù)目:匯總函數(shù)count] from?從哪張表中查找數(shù)據(jù)?[課程的學(xué)生學(xué)號:課程表score] where?查詢條件?[至少選修兩門課程:需要先計(jì)算出每個學(xué)生選修了多少門課,需要用分組,所以這里沒有where子句] group?by?分組?[每個學(xué)生選修課程數(shù)目:按課程號分組,然后用匯總函數(shù)count計(jì)算出選修了多少門課] having?對分組結(jié)果指定條件?[至少選修兩門課程:每個學(xué)生選修課程數(shù)目>=2] */ select?學(xué)號,?count(課程號)?as?選修課程數(shù)目 from?score group?by?學(xué)號 having?count(課程號)>=2;

查詢同名同性學(xué)生名單并統(tǒng)計(jì)同名人數(shù)

/*? 翻譯成大白話,問題解析: 1)查找出姓名相同的學(xué)生有誰,每個姓名相同學(xué)生的人數(shù) 查詢結(jié)果:姓名,人數(shù) 條件:怎么算姓名相同?按姓名分組后人數(shù)大于等于2,因?yàn)橥娜藬?shù)大于等于2 分析思路 select?查詢結(jié)果?[姓名,人數(shù):匯總函數(shù)count(*)] from?從哪張表中查找數(shù)據(jù)?[學(xué)生表student] where?查詢條件?[沒有] group?by?分組?[姓名相同:按姓名分組] having?對分組結(jié)果指定條件?[姓名相同:count(*)>=2] order?by?對查詢結(jié)果排序[沒有]; */  select?姓名,count(*)?as?人數(shù) from?student group?by?姓名 having?count(*)>=2;

查詢不及格的課程并按課程號從大到小排列

/*? 分析思路 select?查詢結(jié)果?[課程號] from?從哪張表中查找數(shù)據(jù)?[成績表score] where?查詢條件?[不及格:成績?<p>查詢每門課程的平均成績,結(jié)果按平均成績升序排序,平均成績相同時,按課程號降序排列</p><pre class="brush:sql;toolbar:false;">/*? 分析思路 select?查詢結(jié)果?[課程號,平均成績:匯總函數(shù)avg(成績)] from?從哪張表中查找數(shù)據(jù)?[成績表score] where?查詢條件?[沒有] group?by?分組?[每門課程:按課程號分組] having?對分組結(jié)果指定條件?[沒有] order?by?對查詢結(jié)果排序[按平均成績升序排序:asc,平均成績相同時,按課程號降序排列:desc]; */ select?課程號,?avg(成績)?as?平均成績 from?score group?by?課程號 order?by?平均成績?asc,課程號?desc;

檢索課程編號為“0004”且分?jǐn)?shù)小于60的學(xué)生學(xué)號,結(jié)果按按分?jǐn)?shù)降序排列

/*? 分析思路 select?查詢結(jié)果?[] from?從哪張表中查找數(shù)據(jù)?[成績表score] where?查詢條件?[課程編號為“04”且分?jǐn)?shù)小于60] group?by?分組?[沒有] having?對分組結(jié)果指定條件?[] order?by?對查詢結(jié)果排序[查詢結(jié)果按按分?jǐn)?shù)降序排列]; */ select?學(xué)號 from?score where?課程號='04'?and?成績?<p>統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)(超過2人的課程才統(tǒng)計(jì))</p><p>要求輸出課程號和選修人數(shù),查詢結(jié)果按人數(shù)降序排序,若人數(shù)相同,按課程號升序排序</p><pre class="brush:sql;toolbar:false;">/*? 分析思路 select?查詢結(jié)果?[要求輸出課程號和選修人數(shù)] from?從哪張表中查找數(shù)據(jù)?[] where?查詢條件?[] group?by?分組?[每門課程:按課程號分組] having?對分組結(jié)果指定條件?[學(xué)生選修人數(shù)(超過2人的課程才統(tǒng)計(jì)):每門課程學(xué)生人數(shù)&gt;2] order?by?對查詢結(jié)果排序[查詢結(jié)果按人數(shù)降序排序,若人數(shù)相同,按課程號升序排序]; */ select?課程號,?count(學(xué)號)?as?'選修人數(shù)' from?score group?by?課程號 having?count(學(xué)號)&gt;2 order?by?count(學(xué)號)?desc,課程號?asc;

查詢兩門以上不及格課程的同學(xué)的學(xué)號及其平均成績

/* 分析思路 先分解題目: 1)[兩門以上][不及格課程]限制條件 2)[同學(xué)的學(xué)號及其平均成績],也就是每個學(xué)生的平均成績,顯示學(xué)號,平均成績 分析過程: 第1步:得到每個學(xué)生的平均成績,顯示學(xué)號,平均成績 第2步:再加上限制條件: 1)不及格課程 2)兩門以上[不及格課程]:課程數(shù)目&gt;2 */  /*? 第1步:得到每個學(xué)生的平均成績,顯示學(xué)號,平均成績 select?查詢結(jié)果?[學(xué)號,平均成績:匯總函數(shù)avg(成績)] from?從哪張表中查找數(shù)據(jù)?[涉及到成績:成績表score] where?查詢條件?[沒有] group?by?分組?[每個學(xué)生的平均:按學(xué)號分組] having?對分組結(jié)果指定條件?[沒有] order?by?對查詢結(jié)果排序[沒有]; */ select?學(xué)號,?avg(成績)?as?平均成績 from?score group?by?學(xué)號;   /*? 第2步:再加上限制條件: 1)不及格課程 2)兩門以上[不及格課程] select?查詢結(jié)果?[學(xué)號,平均成績:匯總函數(shù)avg(成績)] from?從哪張表中查找數(shù)據(jù)?[涉及到成績:成績表score] where?查詢條件?[限制條件:不及格課程,平均成績2,匯總函數(shù)count(課程號)&gt;2] order?by?對查詢結(jié)果排序[沒有]; */ select?學(xué)號,?avg(成績)?as?平均成績 from?score where?成績?=2;

如果上面題目不會做,可以復(fù)習(xí)這部分涉及到的sql知識:

3.復(fù)雜查詢

查詢所有課程成績小于60分學(xué)生的學(xué)號、姓名

【知識點(diǎn)】子查詢

1.翻譯成大白話

1)查詢結(jié)果:學(xué)生學(xué)號,姓名

2)查詢條件:所有課程成績

第1步,寫子查詢(所有課程成績

  • select 查詢結(jié)果[學(xué)號]

  • from 從哪張表中查找數(shù)據(jù)[成績表:score]

  • where 查詢條件[成績

  • group by 分組[沒有]

  • having 對分組結(jié)果指定條件[沒有]

  • order by 對查詢結(jié)果排序[沒有]

  • limit 從查詢結(jié)果中取出指定行[沒有];

select?學(xué)號? from?score where?成績?<p>第2步,查詢結(jié)果:學(xué)生學(xué)號,姓名,條件是前面1步查到的學(xué)號</p>
  • select 查詢結(jié)果[學(xué)號,姓名]

  • from 從哪張表中查找數(shù)據(jù)[學(xué)生表:student]

  • where 查詢條件[用到運(yùn)算符in]

  • group by 分組[沒有]

  • having 對分組結(jié)果指定條件[沒有]

  • order by 對查詢結(jié)果排序[沒有]

  • limit 從查詢結(jié)果中取出指定行[沒有];

select?學(xué)號,姓名 from?student where??學(xué)號?in?( select?學(xué)號? from?score where?成績?<p>查詢沒有學(xué)全所有課的學(xué)生的學(xué)號、姓名|</p><pre class="brush:sql;toolbar:false;">/* 查找出學(xué)號,條件:沒有學(xué)全所有課,也就是該學(xué)生選修的課程數(shù)?<p>查詢出只選修了兩門課程的全部學(xué)生的學(xué)號和姓名|</p><pre class="brush:sql;toolbar:false;">select?學(xué)號,姓名 from?student where?學(xué)號?in( select?學(xué)號 from?score group?by?學(xué)號 having?count(課程號)=2 );

1990年出生的學(xué)生名單

最新最需要的SQL面試題大匯總

/* 查找1990年出生的學(xué)生名單 學(xué)生表中出生日期列的類型是datetime */ select?學(xué)號,姓名? from?student? where?year(出生日期)=1990;

查詢各科成績前兩名的記錄

這類問題其實(shí)就是常見的:分組取每組最大值、最小值,每組最大的N條(top N)記錄。

sql面試題:topN問題

工作中會經(jīng)常遇到這樣的業(yè)務(wù)問題:

  • 如何找到每個類別下用戶最喜歡的產(chǎn)品是哪個?

  • 如果找到每個類別下用戶點(diǎn)擊最多的5個商品是什么?

這類問題其實(shí)就是常見的:分組取每組最大值、最小值,每組最大的N條(top N)記錄。

面對該類問題,如何解決呢?

下面我們通過成績表的例子來給出答案。

成績表是學(xué)生的成績,里面有學(xué)號(學(xué)生的學(xué)號),課程號(學(xué)生選修課程的課程號),成績(學(xué)生選修該課程取得的成績)

最新最需要的SQL面試題大匯總

分組取每組最大值

案例:按課程號分組取成績最大值所在行的數(shù)據(jù)

我們可以使用分組(group by)和匯總函數(shù)得到每個組里的一個值(最大值,最小值,平均值等)。但是無法得到成績最大值所在行的數(shù)據(jù)。

select?課程號,max(成績)?as?最大成績 from?score? group?by?課程號;

最新最需要的SQL面試題大匯總

我們可以使用關(guān)聯(lián)子查詢來實(shí)現(xiàn):

select?*? from?score?as?a? where?成績?=?( select?max(成績)? from?score?as?b? where?b.課程號?=?a.課程號);

最新最需要的SQL面試題大匯總

上面查詢結(jié)果課程號“0001”有2行數(shù)據(jù),是因?yàn)樽畲蟪煽?0有2個

分組取每組最小值

案例:按課程號分組取成績最小值所在行的數(shù)據(jù)

同樣的使用關(guān)聯(lián)子查詢來實(shí)現(xiàn)

select?*? from?score?as?a? where?成績?=?( select?min(成績)? from?score?as?b? where?b.課程號?=?a.課程號);

最新最需要的SQL面試題大匯總

每組最大的N條記錄

案例:查詢各科成績前兩名的記錄

第1步,查出有哪些組

我們可以按課程號分組,查詢出有哪些組,對應(yīng)這個問題里就是有哪些課程號

select?課程號,max(成績)?as?最大成績 from?score? group?by?課程號;

最新最需要的SQL面試題大匯總

第2步:先使用order by子句按成績降序排序(desc),然后使用limt子句返回topN(對應(yīng)這個問題返回的成績前兩名)

--?課程號'0001'?這一組里成績前2名 select?*? from?score? where?課程號?=?'0001'? order?by?成績??desc? limit?2;

同樣的,可以寫出其他組的(其他課程號)取出成績前2名的sql

第3步,使用union all 將每組選出的數(shù)據(jù)合并到一起

--?左右滑動可以可拿到全部sql (select?*?from?score?where?課程號?=?'0001'?order?by?成績??desc?limit?2) union?all (select?*?from?score?where?課程號?=?'0002'?order?by?成績??desc?limit?2) union?all (select?*?from?score?where?課程號?=?'0003'?order?by?成績??desc?limit?2);

最新最需要的SQL面試題大匯總

前面我們使用order by子句按某個列降序排序(desc)得到的是每組最大的N個記錄。如果想要達(dá)到每組最小的N個記錄,將order by子句按某個列升序排序(asc)即可。

求topN的問題還可以使用自定義變量來實(shí)現(xiàn),這個在后續(xù)再介紹。

如果對多表合并還不了解的,可以看下我講過的《從零學(xué)會SQL》的“多表查詢”。

總結(jié)

常見面試題:分組取每組最大值、最小值,每組最大的N條(top N)記錄。

4.多表查詢

最新最需要的SQL面試題大匯總

查詢所有學(xué)生的學(xué)號、姓名、選課數(shù)、總成績

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

查詢平均成績大于85的所有學(xué)生的學(xué)號、姓名和平均成績

select?a.學(xué)號,a.姓名,?avg(b.成績)?as?平均成績 from?student?as?a?left?join?score?as?b on?a.學(xué)號?=?b.學(xué)號 group?by?a.學(xué)號 having?avg(b.成績)&gt;85;

查詢學(xué)生的選課情況:學(xué)號,姓名,課程號,課程名稱

select?a.學(xué)號,?a.姓名,?c.課程號,c.課程名稱 from?student?a?inner?join?score?b?on?a.學(xué)號=b.學(xué)號 inner?join?course?c?on?b.課程號=c.課程號;

查詢出每門課程的及格人數(shù)和不及格人數(shù)

--?考察case表達(dá)式 select?課程號, sum(case?when?成績&gt;=60?then?1? 	?else?0? ????end)?as?及格人數(shù), sum(case?when?成績?<p>使用分段[100-85],[85-70],[70-60],[</p><pre class="brush:sql;toolbar:false;">--?考察case表達(dá)式 select?a.課程號,b.課程名稱, sum(case?when?成績?between?85?and?100? 	?then?1?else?0?end)?as?'[100-85]', sum(case?when?成績?&gt;=70?and?成績=60?and?成績<p>查詢課程編號為0003且課程成績在80分以上的學(xué)生的學(xué)號和姓名|</p><pre class="brush:sql;toolbar:false;">select?a.學(xué)號,a.姓名 from?student??as?a?inner?join?score?as?b?on?a.學(xué)號=b.學(xué)號 where?b.課程號='0003'?and?b.成績&gt;80;

下面是學(xué)生的成績表(表名score,列名:學(xué)號、課程號、成績)

最新最需要的SQL面試題大匯總

使用sql實(shí)現(xiàn)將該表行轉(zhuǎn)列為下面的表結(jié)構(gòu)

最新最需要的SQL面試題大匯總

【面試題類型總結(jié)】這類題目屬于行列如何互換,解題思路如下:

【面試題】下面是學(xué)生的成績表(表名score,列名:學(xué)號、課程號、成績)

最新最需要的SQL面試題大匯總

使用sql實(shí)現(xiàn)將該表行轉(zhuǎn)列為下面的表結(jié)構(gòu)

最新最需要的SQL面試題大匯總

【解答】

第1步,使用常量列輸出目標(biāo)表的結(jié)構(gòu)

可以看到查詢結(jié)果已經(jīng)和目標(biāo)表非常接近了

select?學(xué)號,'課程號0001','課程號0002','課程號0003' from?score;

最新最需要的SQL面試題大匯總

第2步,使用case表達(dá)式,替換常量列為對應(yīng)的成績

select?學(xué)號, (case?課程號?when?'0001'?then?成績?else?0?end)?as?'課程號0001', (case?課程號?when?'0002'?then?成績?else?0?end)?as??'課程號0002', (case?課程號?when?'0003'?then?成績?else?0?end)?as?'課程號0003' from?score;

最新最需要的SQL面試題大匯總

在這個查詢結(jié)果中,每一行表示了某個學(xué)生某一門課程的成績。比如第一行是’學(xué)號0001’選修’課程號00001’的成績,而其他兩列的’課程號0002’和’課程號0003’成績?yōu)?。

每個學(xué)生選修某門課程的成績在下圖的每個方塊內(nèi)。我們可以通過分組,取出每門課程的成績。

最新最需要的SQL面試題大匯總

第3關(guān),分組

分組,并使用最大值函數(shù)max取出上圖每個方塊里的最大值

select?學(xué)號, max(case?課程號?when?'0001'?then?成績?else?0?end)?as?'課程號0001', max(case?課程號?when?'0002'?then?成績?else?0?end)?as?'課程號0002', max(case?課程號?when?'0003'?then?成績?else?0?end)?as?'課程號0003' from?score group?by?學(xué)號;

這樣我們就得到了目標(biāo)表(行列互換)

最新最需要的SQL面試題大匯總

相關(guān)推薦:《mysql教程

? 版權(quán)聲明
THE END
喜歡就支持一下吧
點(diǎn)贊15 分享