SQL基礎(chǔ)知識(shí)整理

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

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

• where 查詢條件，如:[b.課程號(hào)='0003' and b.成績(jī)>80]

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

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

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

• limit?使用limt子句返回topN（對(duì)應(yīng)這個(gè)問(wèn)題返回的成績(jī)前兩名），如:[ limit? 2 ==>從0索引開(kāi)始讀取2個(gè)]limit==>從0索引開(kāi)始 [0,N-1]

select?*?from?table?limit?2,1;????????????????--?含義是跳過(guò)2條取出1條數(shù)據(jù)，limit后面是從第2條開(kāi)始讀，讀取1條信息，即讀取第3條數(shù)據(jù)select?*?from?table?limit?2?offset?1;?????--?含義是從第1條（不包括）數(shù)據(jù)開(kāi)始取出2條數(shù)據(jù)，limit后面跟的是2條數(shù)據(jù)，offset后面是從第1條開(kāi)始讀取，即讀取第2,3條

組函數(shù): 去重 distinct()??統(tǒng)計(jì)總數(shù)sum()? ?計(jì)算個(gè)數(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(無(wú)重復(fù)(過(guò)濾去重))和union all(有重復(fù)[不過(guò)濾去重])

• union 并集

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

oracle(SQL server)數(shù)據(jù)庫(kù)

• intersect 交集

• minus(except) 相減(差集)

oracle

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

1. 視圖: 存儲(chǔ)起來(lái)的 select 語(yǔ)句

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

可以對(duì)簡(jiǎn)單視圖進(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;

問(wèn)題：裂縫，原因：

• 當(dāng)多個(gè)表共用同一個(gè)序列時(shí)。

• rollback

• 發(fā)生異常

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

3. 索引：提高查詢效率

自動(dòng)創(chuàng)建：Oracle 會(huì)為具有唯一約束(唯一約束，主鍵約束)的列，自動(dòng)創(chuàng)建索引

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

手動(dòng)創(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ù)定義語(yǔ)言 create table .../ drop table ... / rename ... to..../ truncate table.../alter table ...

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

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 ... 用于過(guò)濾 組函數(shù)

• order by ... asc 升序， desc 降序

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

• union 并集

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

• intersect 交集

• minus 相減

DCL : 數(shù)據(jù)控制語(yǔ)言 ?commit : 提交 / rollback : 回滾 / 授權(quán)grant...to... ?/revoke如何保證緩存與數(shù)據(jù)庫(kù)的雙寫(xiě)一致性？

索引

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

一、

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

問(wèn)題：查詢工資大于149號(hào)員工工資的員工的信息

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

問(wèn)題：查詢與141號(hào)或174號(hào)員工的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_idfrom?employeeswhere?employee_id?in(141,?174) )?and?department_id?in?(select?department_idfrom?employeeswhere?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_idfrom?employeeswhere?employee_id?in?(141,?174) )?and?employee_id?not?in(141,?174);

from 子句中使用子查詢

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

• 問(wèn)題：返回比本部門(mén)平均工資高的員工的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?e2where?e1.department_id?=?e2.department_id )select?last_name,?e1.department_id,?salary,?avg_sal from?employees?e1,?(select?department_id,?avg(salary)?avg_salfrom?employeesgroup?by?department_id )?e2 where?e1.department_id?=?e2.department_id and?e1.salary?>?e2.avg_sal;

case...when ... then... when ... then ... else ... end

• 查詢：若部門(mén)為10 查看工資的 1.1 倍，部門(mén)號(hào)為 20 工資的1.2倍，其余 1.3 倍

SELECTemployee_id,last_name,salary, CASEdepartment_id?WHEN?10?THENsalary?*?1.1???????????????????????????????????????????????????????????WHEN?20?THENsalary?*?1.2??ELSE?salary?*?1.3???????????????????????????????????????????????????????????END?"new_salary"? FROMemployees; SELECTemployee_id,last_name,salary,decode(?department_id,?10,?salary?*?1.1,?20,?salary?*?1.2,??salary?*?1.3?)?"new_salary"? FROMemployees;

• 問(wèn)題：顯式員工的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_idfrom?departmentswhere?location_id?=?1800 )?then?'Canada'?else?'USA'?end?"location" from?employees;

• 問(wèn)題：查詢員工的employee_id,last_name,要求按照員工的department_name排序

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

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

• 問(wèn)題：查詢公司管理者的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_idfrom?employees )select?employee_id,?last_name,?job_id,?department_id from?employees?e1 where?exists?(select?'x'from?employees?e2where?e1.employee_id?=?e2.manager_id )?

• 問(wèn)題：查詢departments表中，不存在于employees表中的部門(mén)的department_id和department_name

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

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

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

• 刪除 108 號(hào)員工所在部門(mén)中工資最低的那個(gè)員工.

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

常見(jiàn)的SQL面試題：經(jīng)典50題

已知有如下4張表：

• 學(xué)生表：student(學(xué)號(hào),學(xué)生姓名,出生年月,性別)

• 成績(jī)表：score(學(xué)號(hào),課程號(hào),成績(jī))

• 課程表：course(課程號(hào),課程名稱(chēng),教師號(hào))

• 教師表：teacher(教師號(hào),教師姓名)

根據(jù)以上信息按照下面要求寫(xiě)出對(duì)應(yīng)的SQL語(yǔ)句。（搜索公眾號(hào)Java知音，回復(fù)“2021”，送你一份Java面試題寶典）

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

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

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

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

1.創(chuàng)建表

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

按下圖在客戶端navicat里創(chuàng)建學(xué)生表。推薦：250期面試題匯總

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

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

2）創(chuàng)建成績(jī)表（score）

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

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

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

4）教師表（teacher）

教師表的“教師號(hào)”列設(shè)置為主鍵約束，教師姓名這一列設(shè)置約束為“null”（紅框的地方不勾選），表示這一列允許包含空值（null）。推薦：250期面試題匯總

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

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

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

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

在客戶端navicat里的操作

2）成績(jī)表（score）

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

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

客戶端navicat里的操作

3）課程表

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

insert?into?course(課程號(hào),課程名稱(chēng),教師號(hào)) values('0001'?,?'語(yǔ)文'?,?'0002');insert?into?course(課程號(hào),課程名稱(chēng),教師號(hào)) values('0002'?,?'數(shù)學(xué)'?,?'0001');insert?into?course(課程號(hào),課程名稱(chēng),教師號(hào)) values('0003'?,?'英語(yǔ)'?,?'0003');

客戶端navicat里的操作

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

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

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

客戶端navicat里操作

添加結(jié)果

三、50道面試題

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

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

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

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

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

面試題：查詢課程編號(hào)為“0002”的總成績(jī)

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

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

--這個(gè)題目翻譯成大白話就是：查詢有多少人選了課程 --select?學(xué)號(hào)，成績(jī)表里學(xué)號(hào)有重復(fù)值需要去掉 --from?從課程表查找score; select?count(distinct?學(xué)號(hào))?as?學(xué)生人數(shù)? from?score;

查詢各科成績(jī)最高和最低的分， 以如下的形式顯示：課程號(hào)，最高分，最低分

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

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

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

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

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

查詢平均成績(jī)大于60分學(xué)生的學(xué)號(hào)和平均成績(jī)

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

查詢至少選修兩門(mén)課程的學(xué)生學(xué)號(hào)

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

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

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

查詢不及格的課程并按課程號(hào)從大到小排列

/*? 分析思路 select?查詢結(jié)果?[課程號(hào)] from?從哪張表中查找數(shù)據(jù)?[成績(jī)表score] where 查詢條件?[不及格：成績(jī)?<60] group?by?分組?[沒(méi)有] having?對(duì)分組結(jié)果指定條件?[沒(méi)有] order by 對(duì)查詢結(jié)果排序[課程號(hào)從大到小排列：降序desc]; */ select?課程號(hào) from?score? where?成績(jī)<60 order?by?課程號(hào)?desc;

查詢每門(mén)課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排序，平均成績(jī)相同時(shí)，按課程號(hào)降序排列

/*? 分析思路 select 查詢結(jié)果?[課程號(hào),平均成績(jī)：匯總函數(shù)avg(成績(jī))] from?從哪張表中查找數(shù)據(jù)?[成績(jī)表score] where?查詢條件?[沒(méi)有] group by 分組?[每門(mén)課程：按課程號(hào)分組] having?對(duì)分組結(jié)果指定條件?[沒(méi)有] order?by?對(duì)查詢結(jié)果排序[按平均成績(jī)升序排序:asc，平均成績(jī)相同時(shí)，按課程號(hào)降序排列:desc]; */ select?課程號(hào),?avg(成績(jī))?as?平均成績(jī) from?score group?by?課程號(hào) order?by?平均成績(jī)?asc,課程號(hào)?desc;

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

/*? 分析思路 select?查詢結(jié)果?[] from?從哪張表中查找數(shù)據(jù)?[成績(jī)表score] where?查詢條件?[課程編號(hào)為“04”且分?jǐn)?shù)小于60] group?by?分組?[沒(méi)有] having?對(duì)分組結(jié)果指定條件?[] order?by?對(duì)查詢結(jié)果排序[查詢結(jié)果按按分?jǐn)?shù)降序排列]; */ select?學(xué)號(hào) from?score where?課程號(hào)='04'?and?成績(jī)?<60 order?by?成績(jī)?desc;

統(tǒng)計(jì)每門(mén)課程的學(xué)生選修人數(shù)(超過(guò)2人的課程才統(tǒng)計(jì))

要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排序，若人數(shù)相同，按課程號(hào)升序排序

/*? 分析思路 select?查詢結(jié)果?[要求輸出課程號(hào)和選修人數(shù)] from?從哪張表中查找數(shù)據(jù)?[] where?查詢條件?[] group by 分組?[每門(mén)課程：按課程號(hào)分組] having 對(duì)分組結(jié)果指定條件?[學(xué)生選修人數(shù)(超過(guò)2人的課程才統(tǒng)計(jì))：每門(mén)課程學(xué)生人數(shù)>2] order?by?對(duì)查詢結(jié)果排序[查詢結(jié)果按人數(shù)降序排序，若人數(shù)相同，按課程號(hào)升序排序]; */ select?課程號(hào),?count(學(xué)號(hào))?as?'選修人數(shù)' from?score group?by?課程號(hào) having?count(學(xué)號(hào))>2 order?by?count(學(xué)號(hào))?desc,課程號(hào)?asc;

查詢兩門(mén)以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績(jī)

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

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

3.復(fù)雜查詢

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

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

1.翻譯成大白話

1）查詢結(jié)果：學(xué)生學(xué)號(hào)，姓名 2）查詢條件：所有課程成績(jī) < 60 的學(xué)生，需要從成績(jī)表里查找，用到子查詢

第1步，寫(xiě)子查詢（所有課程成績(jī) < 60 的學(xué)生）

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

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

• where 查詢條件[成績(jī) < 60]

• group by 分組[沒(méi)有]

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

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

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

select?學(xué)號(hào)? from?score where?成績(jī)?<?60;

第2步，查詢結(jié)果：學(xué)生學(xué)號(hào)，姓名，條件是前面1步查到的學(xué)號(hào)

• select 查詢結(jié)果[學(xué)號(hào),姓名]

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

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

• group by 分組[沒(méi)有]

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

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

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

select?學(xué)號(hào),姓名 from?student where??學(xué)號(hào)?in?( select?學(xué)號(hào)? from?score where?成績(jī)?<?60 );

查詢沒(méi)有學(xué)全所有課的學(xué)生的學(xué)號(hào)、姓名

/* 查找出學(xué)號(hào)，條件：沒(méi)有學(xué)全所有課，也就是該學(xué)生選修的課程數(shù)?<?總的課程數(shù) 【考察知識(shí)點(diǎn)】in，子查詢 */ 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))?<?(select?count(課程號(hào))?from?course) );

查詢出只選修了兩門(mén)課程的全部學(xué)生的學(xué)號(hào)和姓名

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 );

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

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

查詢各科成績(jī)前兩名的記錄

這類(lèi)問(wèn)題其實(shí)就是常見(jiàn)的：分組取每組最大值、最小值，每組最大的N條（top N）記錄。

sql面試題：topN問(wèn)題

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

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

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

這類(lèi)問(wèn)題其實(shí)就是常見(jiàn)的：分組取每組最大值、最小值，每組最大的N條（top N）記錄。

面對(duì)該類(lèi)問(wèn)題，如何解決呢？

下面我們通過(guò)成績(jī)表的例子來(lái)給出答案。

成績(jī)表是學(xué)生的成績(jī)，里面有學(xué)號(hào)（學(xué)生的學(xué)號(hào)），課程號(hào)（學(xué)生選修課程的課程號(hào)），成績(jī)（學(xué)生選修該課程取得的成績(jī)）

分組取每組最大值

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

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

select?課程號(hào),max(成績(jī))?as?最大成績(jī) from?score? group?by?課程號(hào);

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

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

上面查詢結(jié)果課程號(hào)“0001”有2行數(shù)據(jù)，是因?yàn)樽畲蟪煽?jī)80有2個(gè)

分組取每組最小值

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

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

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

每組最大的N條記錄

案例：查詢各科成績(jī)前兩名的記錄

第1步，查出有哪些組

我們可以按課程號(hào)分組，查詢出有哪些組，對(duì)應(yīng)這個(gè)問(wèn)題里就是有哪些課程號(hào)

select?課程號(hào),max(成績(jī))?as?最大成績(jī) from?score? group?by?課程號(hào);

第2步：先使用order by子句按成績(jī)降序排序（desc），然后使用limt子句返回topN（對(duì)應(yīng)這個(gè)問(wèn)題返回的成績(jī)前兩名）

--?課程號(hào)'0001'?這一組里成績(jī)前2名 select?*? from?score? where?課程號(hào)?=?'0001'? order?by?成績(jī)??desc? limit?2;

同樣的，可以寫(xiě)出其他組的（其他課程號(hào)）取出成績(jī)前2名的sql

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

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

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

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

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

總結(jié)

常見(jiàn)面試題：分組取每組最大值、最小值，每組最大的N條（top N）記錄。

4.多表查詢

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

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

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

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

查詢學(xué)生的選課情況：學(xué)號(hào)，姓名，課程號(hào)，課程名稱(chēng)

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

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

--?考察case表達(dá)式 select?課程號(hào), sum(case?when?成績(jī)>=60?then?1?else?0?end)?as?及格人數(shù), sum(case?when?成績(jī)?<??60?then?1?else?0?end)?as?不及格人數(shù) from?score group?by?課程號(hào);

使用分段[100-85],[85-70],[70-60],[<60]來(lái)統(tǒng)計(jì)各科成績(jī)，分別統(tǒng)計(jì)：各分?jǐn)?shù)段人數(shù)，課程號(hào)和課程名稱(chēng)

--?考察case表達(dá)式 select?a.課程號(hào),b.課程名稱(chēng), sum(case?when?成績(jī)?between?85?and?100?then?1?else?0?end)?as?'[100-85]', sum(case?when?成績(jī)?>=70?and?成績(jī)<85?then?1?else?0?end)?as?'[85-70]', sum(case?when?成績(jī)>=60?and?成績(jī)<70??then?1?else?0?end)?as?'[70-60]', sum(case?when?成績(jī)<60?then?1?else?0?end)?as?'[<60]' from?score?as?a?right?join?course?as?b? on?a.課程號(hào)=b.課程號(hào) group?by?a.課程號(hào),b.課程名稱(chēng);

查詢課程編號(hào)為0003且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名|

select?a.學(xué)號(hào),a.姓名 from?student??as?a?inner?join?score?as?b?on?a.學(xué)號(hào)=b.學(xué)號(hào) where?b.課程號(hào)='0003'?and?b.成績(jī)>80;

下面是學(xué)生的成績(jī)表（表名score，列名：學(xué)號(hào)、課程號(hào)、成績(jī)）

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

【面試題類(lèi)型總結(jié)】這類(lèi)題目屬于行列如何互換，解題思路如下：

【面試題】下面是學(xué)生的成績(jī)表（表名score，列名：學(xué)號(hào)、課程號(hào)、成績(jī)）

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

【解答】

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

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

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

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

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

在這個(gè)查詢結(jié)果中，每一行表示了某個(gè)學(xué)生某一門(mén)課程的成績(jī)。比如第一行是'學(xué)號(hào)0001'選修'課程號(hào)00001'的成績(jī)，而其他兩列的'課程號(hào)0002'和'課程號(hào)0003'成績(jī)?yōu)?。

每個(gè)學(xué)生選修某門(mén)課程的成績(jī)?cè)谙聢D的每個(gè)方塊內(nèi)。我們可以通過(guò)分組，取出每門(mén)課程的成績(jī)。

第3關(guān)，分組

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

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

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

BAT等大廠Java面試經(jīng)驗(yàn)總結(jié) 想獲取 Java大廠面試題學(xué)習(xí)資料掃下方二維碼回復(fù)「BAT」就好了回復(fù)?【加群】獲取github掘金交流群回復(fù)?【電子書(shū)】獲取2020電子書(shū)教程回復(fù)?【C】獲取全套C語(yǔ)言學(xué)習(xí)知識(shí)手冊(cè)回復(fù)?【Java】獲取java相關(guān)的視頻教程和資料回復(fù)?【爬蟲(chóng)】獲取SpringCloud相關(guān)多的學(xué)習(xí)資料回復(fù)?【Python】即可獲得Python基礎(chǔ)到進(jìn)階的學(xué)習(xí)教程回復(fù)?【idea破解】即可獲得intellij idea相關(guān)的破解教程關(guān)注我gitHub掘金，每天發(fā)掘一篇好項(xiàng)目，學(xué)習(xí)技術(shù)不迷路！回復(fù)?【idea激活】即可獲得idea的激活方式 回復(fù)?【Java】獲取java相關(guān)的視頻教程和資料 回復(fù)?【SpringCloud】獲取SpringCloud相關(guān)多的學(xué)習(xí)資料 回復(fù)?【python】獲取全套0基礎(chǔ)Python知識(shí)手冊(cè) 回復(fù)?【2020】獲取2020java相關(guān)面試題教程 回復(fù)?【加群】即可加入終端研發(fā)部相關(guān)的技術(shù)交流群為什么HTTPS是安全的 因?yàn)锽itMap，白白搭進(jìn)去8臺(tái)服務(wù)器... 《某廠內(nèi)部SQL大全 》.PDF 字節(jié)跳動(dòng)一面：i++ 是線程安全的嗎？ 大家好，歡迎加我微信，很高興認(rèn)識(shí)你！ 在華為鴻蒙 OS 上嘗鮮，我的第一個(gè)“hello world”，起飛！相信自己，沒(méi)有做不到的，只有想不到的在這里獲得的不僅僅是技術(shù)！就給個(gè)“在看”

總結(jié)

以上是生活随笔為你收集整理的SQL面试题：经典50例的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。