15000 字的 MySQL 速查手冊(cè)
牛逼！又發(fā)現(xiàn)了一款面試題庫，太全了??！
（點(diǎn)擊查看）
SQL基礎(chǔ)知識(shí)整理
select 查詢結(jié)果，如:? [學(xué)號(hào),平均成績：組函數(shù)avg(成績)]from 從哪張表中查找數(shù)據(jù)，如: [涉及到成績：成績表score]where 查詢條件，如: [b.課程號(hào)='0003' and b.成績>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é)果排序，如: [增序: 成績? ASC / 降序: 成績 DESC];limit?使用limt子句返回topN（對(duì)應(yīng)這個(gè)問題返回的成績前兩名），如: [ limit? 2 ==>從0索引開始讀取2個(gè)]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條
union 并集 union all(有重復(fù)) oracle(SQL server)數(shù)據(jù)庫
intersect 交集 minus(except) 相減(差集)
oracle
一、數(shù)據(jù)庫對(duì)象：表(table) ?視圖(view) ?序列(sequence) ?索引(index) ?同義詞(synonym)
1. 視圖: 存儲(chǔ)起來的 select 語句
create?view?emp_vw
as
select?employee_id,?last_name,?salary
from?employees
where?department_id?=?90;
select?*?from?emp_vw;
可以對(duì)簡單視圖進(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)多個(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ù)定義語言 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
索引
何時(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
問題：查詢工資大于149號(hào)員工工資的員工的信息
select?*?
from?employees
where?salary?>?(
??????select?salary
??????from?employees
??????where?employee_id?=?149
)
問題：查詢與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
問題：返回比本部門平均工資高的員工的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;
查詢：若部門為10 查看工資的 1.1 倍，部門號(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;
問題：顯式員工的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
)
問題：查詢公司管理者的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;
刪除 108 號(hào)員工所在部門中工資最低的那個(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;
常見的SQL面試題：經(jīng)典50題
學(xué)生表：student(學(xué)號(hào),學(xué)生姓名,出生年月,性別) 成績表：score(學(xué)號(hào),課程號(hào),成績) 課程表：course(課程號(hào),課程名稱,教師號(hào)) 教師表：teacher(教師號(hào),教師姓名)
ps：這些題考察SQL的編寫能力，對(duì)于這類型的題目，需要你先把4張表之間的關(guān)聯(lián)關(guān)系搞清楚了，最好的辦法是自己在草稿紙上畫出關(guān)聯(lián)圖，然后再編寫對(duì)應(yīng)的SQL語句就比較容易了。下圖是我畫的這4張表的關(guān)系圖，可以看出它們之間是通過哪些外鍵關(guān)聯(lián)起來的：
一、創(chuàng)建數(shù)據(jù)庫和表
為了演示題目的運(yùn)行過程，我們先按下面語句在客戶端navicat中創(chuàng)建數(shù)據(jù)庫和表。
如何你還不懂什么是數(shù)據(jù)庫，什么是客戶端navicat，可以先學(xué)習(xí)這個(gè)：
1.創(chuàng)建表
1）創(chuàng)建學(xué)生表（student）
按下圖在客戶端navicat里創(chuàng)建學(xué)生表。
2）創(chuàng)建成績表（score）
3）創(chuàng)建課程表（course）
4）教師表（teacher）
向表中添加數(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）成績表（score）
添加數(shù)據(jù)的sql
insert?into?score(學(xué)號(hào),課程號(hào),成績)?
values('0001'?,?'0001'?,?80);
?
insert?into?score(學(xué)號(hào),課程號(hào),成績)?
values('0001'?,?'0002'?,?90);
?
insert?into?score(學(xué)號(hào),課程號(hào),成績)?
values('0001'?,?'0003'?,?99);
?
insert?into?score(學(xué)號(hào),課程號(hào),成績)?
values('0002'?,?'0002'?,?60);
?
insert?into?score(學(xué)號(hào),課程號(hào),成績)?
values('0002'?,?'0003'?,?80);
?
insert?into?score(學(xué)號(hào),課程號(hào),成績)?
values('0003'?,?'0001'?,?80);
?
insert?into?score(學(xué)號(hào),課程號(hào),成績)?
values('0003'?,?'0002'?,?80);
?
insert?into?score(學(xué)號(hào),課程號(hào),成績)?
values('0003'?,?'0003'?,?80);
客戶端navicat里的操作
3）課程表
添加數(shù)據(jù)的sql
insert?into?course(課程號(hào),課程名稱,教師號(hào))
values('0001'?,?'語文'?,?'0002');
?
insert?into?course(課程號(hào),課程名稱,教師號(hào))
values('0002'?,?'數(shù)學(xué)'?,?'0001');
?
insert?into?course(課程號(hào),課程名稱,教師號(hào))
values('0003'?,?'英語'?,?'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)行了分類
查詢姓“猴”的學(xué)生名單
查詢姓“孟”老師的個(gè)數(shù)
select?count(教師號(hào))
from?teacher
where?教師姓名?like?'孟%';
2.匯總統(tǒng)計(jì)分組分析
面試題：查詢課程編號(hào)為“0002”的總成績
--分析思路
--select?查詢結(jié)果?[總成績:匯總函數(shù)sum]
--from?從哪張表中查找數(shù)據(jù)[成績表score]
--where?查詢條件?[課程號(hào)是0002]
select?sum(成績)
from?score
where?課程號(hào)?=?'0002';
查詢選了課程的學(xué)生人數(shù)
--這個(gè)題目翻譯成大白話就是：查詢有多少人選了課程
--select?學(xué)號(hào)，成績表里學(xué)號(hào)有重復(fù)值需要去掉
--from?從課程表查找score;
select?count(distinct?學(xué)號(hào))?as?學(xué)生人數(shù)?
from?score;
查詢各科成績最高和最低的分， 以如下的形式顯示：課程號(hào)，最高分，最低分
/*
分析思路
select 查詢結(jié)果?[課程ID：是課程號(hào)的別名,最高分：max(成績) ,最低分：min(成績)]
from?從哪張表中查找數(shù)據(jù)?[成績表score]
where?查詢條件?[沒有]
group by 分組?[各科成績：也就是每門課程的成績，需要按課程號(hào)分組];
*/
select?課程號(hào),max(成績)?as?最高分,min(成績)?as?最低分
from?score
group?by?課程號(hào);
查詢每門課程被選修的學(xué)生數(shù)
/*
分析思路
select 查詢結(jié)果?[課程號(hào)，選修該課程的學(xué)生數(shù)：匯總函數(shù)count]
from?從哪張表中查找數(shù)據(jù)?[成績表score]
where?查詢條件?[沒有]
group by 分組?[每門課程：按課程號(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?查詢條件?[沒有]
group by 分組?[男生、女生人數(shù)：按性別分組]
having?對(duì)分組結(jié)果指定條件?[沒有]
order?by?對(duì)查詢結(jié)果排序[沒有];
*/
select?性別,count(*)
from?student
group?by?性別;
查詢平均成績大于60分學(xué)生的學(xué)號(hào)和平均成績
/*?
題目翻譯成大白話：
平均成績：展開來說就是計(jì)算每個(gè)學(xué)生的平均成績
這里涉及到“每個(gè)”就是要分組了
平均成績大于60分，就是對(duì)分組結(jié)果指定條件
分析思路
select 查詢結(jié)果?[學(xué)號(hào)，平均成績：匯總函數(shù)avg(成績)]
from?從哪張表中查找數(shù)據(jù)?[成績?cè)诔煽儽碇校圆檎业氖浅煽儽韘core]
where?查詢條件?[沒有]
group by 分組?[平均成績：先按學(xué)號(hào)分組，再計(jì)算平均成績]
having?對(duì)分組結(jié)果指定條件?[平均成績大于60分]
*/
select?學(xué)號(hào),?avg(成績)
from?score
group?by?學(xué)號(hào)
having?avg(成績)>60;
查詢至少選修兩門課程的學(xué)生學(xué)號(hào)
/*?
翻譯成大白話：
第1步，需要先計(jì)算出每個(gè)學(xué)生選修的課程數(shù)據(jù)，需要按學(xué)號(hào)分組
第2步，至少選修兩門課程：也就是每個(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 查詢條件?[至少選修兩門課程：需要先計(jì)算出每個(gè)學(xué)生選修了多少門課，需要用分組，所以這里沒有where子句]
group by 分組?[每個(gè)學(xué)生選修課程數(shù)目：按課程號(hào)分組，然后用匯總函數(shù)count計(jì)算出選修了多少門課]
having 對(duì)分組結(jié)果指定條件?[至少選修兩門課程：每個(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ù)
/*?
翻譯成大白話，問題解析：
1）查找出姓名相同的學(xué)生有誰，每個(gè)姓名相同學(xué)生的人數(shù)
查詢結(jié)果：姓名,人數(shù)
條件：怎么算姓名相同？按姓名分組后人數(shù)大于等于2，因?yàn)橥娜藬?shù)大于等于2
分析思路
select 查詢結(jié)果?[姓名,人數(shù)：匯總函數(shù)count(*)]
from?從哪張表中查找數(shù)據(jù)?[學(xué)生表student]
where?查詢條件?[沒有]
group by 分組?[姓名相同：按姓名分組]
having 對(duì)分組結(jié)果指定條件?[姓名相同：count(*)>=2]
order?by?對(duì)查詢結(jié)果排序[沒有];
*/
?
select?姓名,count(*)?as?人數(shù)
from?student
group?by?姓名
having?count(*)>=2;
查詢不及格的課程并按課程號(hào)從大到小排列
/*?
分析思路
select?查詢結(jié)果?[課程號(hào)]
from?從哪張表中查找數(shù)據(jù)?[成績表score]
where 查詢條件?[不及格：成績?<60]
group?by?分組?[沒有]
having?對(duì)分組結(jié)果指定條件?[沒有]
order by 對(duì)查詢結(jié)果排序[課程號(hào)從大到小排列：降序desc];
*/
select?課程號(hào)
from?score?
where?成績<60
order?by?課程號(hào)?desc;
查詢每門課程的平均成績，結(jié)果按平均成績升序排序，平均成績相同時(shí)，按課程號(hào)降序排列
/*?
分析思路
select 查詢結(jié)果?[課程號(hào),平均成績：匯總函數(shù)avg(成績)]
from?從哪張表中查找數(shù)據(jù)?[成績表score]
where?查詢條件?[沒有]
group by 分組?[每門課程：按課程號(hào)分組]
having?對(duì)分組結(jié)果指定條件?[沒有]
order?by?對(duì)查詢結(jié)果排序[按平均成績升序排序:asc，平均成績相同時(shí)，按課程號(hào)降序排列:desc];
*/
select?課程號(hào),?avg(成績)?as?平均成績
from?score
group?by?課程號(hào)
order?by?平均成績?asc,課程號(hào)?desc;
檢索課程編號(hào)為“0004”且分?jǐn)?shù)小于60的學(xué)生學(xué)號(hào)，結(jié)果按按分?jǐn)?shù)降序排列
/*?
分析思路
select?查詢結(jié)果?[]
from?從哪張表中查找數(shù)據(jù)?[成績表score]
where?查詢條件?[課程編號(hào)為“04”且分?jǐn)?shù)小于60]
group?by?分組?[沒有]
having?對(duì)分組結(jié)果指定條件?[]
order?by?對(duì)查詢結(jié)果排序[查詢結(jié)果按按分?jǐn)?shù)降序排列];
*/
select?學(xué)號(hào)
from?score
where?課程號(hào)='04'?and?成績?<60
order?by?成績?desc;
/*?
分析思路
select?查詢結(jié)果?[要求輸出課程號(hào)和選修人數(shù)]
from?從哪張表中查找數(shù)據(jù)?[]
where?查詢條件?[]
group by 分組?[每門課程：按課程號(hào)分組]
having 對(duì)分組結(jié)果指定條件?[學(xué)生選修人數(shù)(超過2人的課程才統(tǒng)計(jì))：每門課程學(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;
查詢兩門以上不及格課程的同學(xué)的學(xué)號(hào)及其平均成績
/*
分析思路
先分解題目：
1）[兩門以上][不及格課程]限制條件
2）[同學(xué)的學(xué)號(hào)及其平均成績]，也就是每個(gè)學(xué)生的平均成績，顯示學(xué)號(hào)，平均成績
分析過程：
第1步：得到每個(gè)學(xué)生的平均成績，顯示學(xué)號(hào)，平均成績
第2步：再加上限制條件：
1）不及格課程
2）兩門以上[不及格課程]：課程數(shù)目>2
?
?
/*?
第1步：得到每個(gè)學(xué)生的平均成績，顯示學(xué)號(hào)，平均成績
select 查詢結(jié)果?[學(xué)號(hào),平均成績：匯總函數(shù)avg(成績)]
from 從哪張表中查找數(shù)據(jù)?[涉及到成績：成績表score]
where?查詢條件?[沒有]
group by 分組?[每個(gè)學(xué)生的平均：按學(xué)號(hào)分組]
having?對(duì)分組結(jié)果指定條件?[沒有]
order?by?對(duì)查詢結(jié)果排序[沒有];
*/
select?學(xué)號(hào),?avg(成績)?as?平均成績
from?score
group?by?學(xué)號(hào);
?
?
/*?
第2步：再加上限制條件：
1）不及格課程
2）兩門以上[不及格課程]
select 查詢結(jié)果?[學(xué)號(hào),平均成績：匯總函數(shù)avg(成績)]
from 從哪張表中查找數(shù)據(jù)?[涉及到成績：成績表score]
where 查詢條件?[限制條件：不及格課程，平均成績<60]
group by 分組?[每個(gè)學(xué)生的平均：按學(xué)號(hào)分組]
having 對(duì)分組結(jié)果指定條件?[限制條件：課程數(shù)目>2,匯總函數(shù)count(課程號(hào))>2]
order?by?對(duì)查詢結(jié)果排序[沒有];
*/
select?學(xué)號(hào),?avg(成績)?as?平均成績
from?score
where?成績?<60
group?by?學(xué)號(hào)
having?count(課程號(hào))>=2;
3.復(fù)雜查詢
1.翻譯成大白話
select 查詢結(jié)果[學(xué)號(hào)] from 從哪張表中查找數(shù)據(jù)[成績表：score] where 查詢條件[成績 < 60] group by 分組[沒有] having 對(duì)分組結(jié)果指定條件[沒有] order by 對(duì)查詢結(jié)果排序[沒有] limit 從查詢結(jié)果中取出指定行[沒有];
select?學(xué)號(hào)?
from?score
where?成績?60;
select 查詢結(jié)果[學(xué)號(hào),姓名] from 從哪張表中查找數(shù)據(jù)[學(xué)生表:student] where 查詢條件[用到運(yùn)算符in] group by 分組[沒有] having 對(duì)分組結(jié)果指定條件[沒有] order by 對(duì)查詢結(jié)果排序[沒有] limit 從查詢結(jié)果中取出指定行[沒有];
select?學(xué)號(hào),姓名
from?student
where??學(xué)號(hào)?in?(
select?學(xué)號(hào)?
from?score
where?成績?60
);
查詢沒有學(xué)全所有課的學(xué)生的學(xué)號(hào)、姓名
/*
查找出學(xué)號(hào)，條件：沒有學(xué)全所有課，也就是該學(xué)生選修的課程數(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)
);
查詢出只選修了兩門課程的全部學(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é)生表中出生日期列的類型是datetime
*/
select?學(xué)號(hào),姓名?
from?student?
where?year(出生日期)=1990;?
sql面試題：topN問題
如何找到每個(gè)類別下用戶最喜歡的產(chǎn)品是哪個(gè)？ 如果找到每個(gè)類別下用戶點(diǎn)擊最多的5個(gè)商品是什么？
分組取每組最大值
select?課程號(hào),max(成績)?as?最大成績
from?score?
group?by?課程號(hào);
我們可以使用關(guān)聯(lián)子查詢來實(shí)現(xiàn)：
select?*?
from?score?as?a?
where?成績?=?(
select?max(成績)?
from?score?as?b?
where?b.課程號(hào)?=?a.課程號(hào));
上面查詢結(jié)果課程號(hào)“0001”有2行數(shù)據(jù)，是因?yàn)樽畲蟪煽?0有2個(gè)
分組取每組最小值
案例：按課程號(hào)分組取成績最小值所在行的數(shù)據(jù)
同樣的使用關(guān)聯(lián)子查詢來實(shí)現(xiàn)
select?*?
from?score?as?a?
where?成績?=?(
select?min(成績)?
from?score?as?b?
where?b.課程號(hào)?=?a.課程號(hào));
每組最大的N條記錄
案例：查詢各科成績前兩名的記錄
第1步，查出有哪些組
我們可以按課程號(hào)分組，查詢出有哪些組，對(duì)應(yīng)這個(gè)問題里就是有哪些課程號(hào)
select?課程號(hào),max(成績)?as?最大成績
from?score?
group?by?課程號(hào);
--?課程號(hào)'0001'?這一組里成績前2名
select?*?
from?score?
where?課程號(hào)?=?'0001'?
order?by?成績??desc?
limit?2;
--?左右滑動(dòng)可以可拿到全部sql
(select?*?from?score?where?課程號(hào)?=?'0001'?order?by?成績??desc?limit?2)
union?all
(select?*?from?score?where?課程號(hào)?=?'0002'?order?by?成績??desc?limit?2)
union?all
(select?*?from?score?where?課程號(hào)?=?'0003'?order?by?成績??desc?limit?2);
4.多表查詢
select?a.學(xué)號(hào),a.姓名,count(b.課程號(hào))?as?選課數(shù),sum(b.成績)?as?總成績
from?student?as?a?left?join?score?as?b
on?a.學(xué)號(hào)?=?b.學(xué)號(hào)
group?by?a.學(xué)號(hào);
查詢平均成績大于85的所有學(xué)生的學(xué)號(hào)、姓名和平均成績
select?a.學(xué)號(hào),a.姓名,?avg(b.成績)?as?平均成績
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.成績)>85;
查詢學(xué)生的選課情況：學(xué)號(hào)，姓名，課程號(hào)，課程名稱
select?a.學(xué)號(hào),?a.姓名,?c.課程號(hào),c.課程名稱
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);
查詢出每門課程的及格人數(shù)和不及格人數(shù)
--?考察case表達(dá)式
select?課程號(hào),
sum(case?when?成績>=60?then?1?
??else?0?
????end)?as?及格人數(shù),
sum(case?when?成績?60?then?1?
??else?0?
????end)?as?不及格人數(shù)
from?score
group?by?課程號(hào);
--?考察case表達(dá)式
select?a.課程號(hào),b.課程名稱,
sum(case?when?成績?between?85?and?100?
??then?1?else?0?end)?as?'[100-85]',
sum(case?when?成績?>=70?and?成績<85?
??then?1?else?0?end)?as?'[85-70]',
sum(case?when?成績>=60?and?成績<70??
??then?1?else?0?end)?as?'[70-60]',
sum(case?when?成績<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.課程名稱;
查詢課程編號(hào)為0003且課程成績?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.成績>80;
下面是學(xué)生的成績表（表名score，列名：學(xué)號(hào)、課程號(hào)、成績）
使用sql實(shí)現(xiàn)將該表行轉(zhuǎn)列為下面的表結(jié)構(gòu)
【面試題類型總結(jié)】這類題目屬于行列如何互換，解題思路如下：
【面試題】下面是學(xué)生的成績表（表名score，列名：學(xué)號(hào)、課程號(hào)、成績）
使用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)的成績
select?學(xué)號(hào),
(case?課程號(hào)?when?'0001'?then?成績?else?0?end)?as?'課程號(hào)0001',
(case?課程號(hào)?when?'0002'?then?成績?else?0?end)?as??'課程號(hào)0002',
(case?課程號(hào)?when?'0003'?then?成績?else?0?end)?as?'課程號(hào)0003'
from?score;
select?學(xué)號(hào),
max(case?課程號(hào)?when?'0001'?then?成績?else?0?end)?as?'課程號(hào)0001',
max(case?課程號(hào)?when?'0002'?then?成績?else?0?end)?as?'課程號(hào)0002',
max(case?課程號(hào)?when?'0003'?then?成績?else?0?end)?as?'課程號(hào)0003'
from?score
group?by?學(xué)號(hào);
這樣我們就得到了目標(biāo)表（行列互換）
作者：sh_c_2450957609
blog.csdn.net/u010565545/article/details/100785261如有文章對(duì)你有幫助，
歡迎關(guān)注??、點(diǎn)贊??、轉(zhuǎn)發(fā)??！
推薦，?Java面試題庫，詳情點(diǎn)擊： 牛逼！又發(fā)現(xiàn)了一款牛逼的Java面試題庫，史上最強(qiáng)！ 點(diǎn)擊文末“閱讀原文”可直達(dá)
評(píng)論
圖片
表情