常見(jiàn)的SQL面試題：經(jīng)典50例
作者 | sh_c_2450957609
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;
自動(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ù)如果您正在學(xué)習(xí)Spring Boot，推薦一個(gè)連載多年還在繼續(xù)更新的免費(fèi)教程：http://blog.didispace.com/spring-boot-learning-2x/
union 并集 union all(有重復(fù)) intersect 交集 minus 相減
DCL : 數(shù)據(jù)控制語(yǔ)言 commit : 提交 / rollback : 回滾 / 授權(quán)grant...to... /revoke
索引
何時(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 salary
from employees
where 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_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);
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
問(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 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
查詢：若部門(mén)為10 查看工資的 1.1 倍，部門(mén)號(hào)為 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;
問(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_id
from departments
where 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_name
from departments d1
where 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_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
)
問(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 e1
where 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 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;
刪除 108 號(hào)員工所在部門(mén)中工資最低的那個(gè)員工.
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;
已知有如下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ǔ)句。
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é)生表。
學(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）。
向表中添加數(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)升序排序
如果您正在學(xué)習(xí)Spring Boot，推薦一個(gè)連載多年還在繼續(xù)更新的免費(fèi)教程：http://blog.didispace.com/spring-boot-learning-2x/
/*
分析思路
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）記錄。
如果您正在學(xué)習(xí)Spring Boot，推薦一個(gè)連載多年還在繼續(xù)更新的免費(fèi)教程：http://blog.didispace.com/spring-boot-learning-2x/
面對(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)表（行列互換）
--- EOF ---
往期推薦
關(guān)注我回復(fù)「加群」，加入Spring技術(shù)交流群