JAVA開發(fā)必備的SQL能力
第1篇：SQL為什么慢
作為一個(gè)地地道道的java開發(fā)，不管是校招還是社招，只要涉及到筆試，這個(gè)SQL筆試是必須的。另外，在面試的時(shí)候，也有可能面試官給出一個(gè)業(yè)務(wù)表，然后叫你寫個(gè)SQL，另外，我們?cè)诠ぷ髦校瑢慡QL也是常事，尤其是涉及到拉取數(shù)據(jù)、報(bào)表等都會(huì)用到SQL。
所以，于情于理，我們是很有必要掌握好基本SQL的編寫能力。
成功都是留給有準(zhǔn)備的人，你準(zhǔn)備過嗎？
準(zhǔn)備
數(shù)據(jù)庫(kù)表創(chuàng)建：
學(xué)生表Student
課程表Course
老師表Teacher
成績(jī)表sc
建表語句如下：
create?table?Student(
????sid?varchar(10),
????sname?varchar(10),
????sage?datetime,
????ssex?nvarchar(10)
);
create?table?Course(
????cid?varchar(10),
????cname?varchar(10),
????tid?varchar(10)
);
create?table?Teacher(
????tid?varchar(10),
????tname?varchar(10)
);
create?table?SC(
????sid?varchar(10),
????cid?varchar(10),
????score?decimal(18,1)
);
然后對(duì)表進(jìn)行初始化數(shù)據(jù)：
--?初始化?學(xué)生表
insert?into?Student?values('01'?,?'趙雷'?,?'1990-01-01'?,?'男');
insert?into?Student?values('02'?,?'錢電'?,?'1990-12-21'?,?'男');
insert?into?Student?values('03'?,?'孫風(fēng)'?,?'1990-05-20'?,?'男');
insert?into?Student?values('04'?,?'李云'?,?'1990-08-06'?,?'男');
insert?into?Student?values('05'?,?'周梅'?,?'1991-12-01'?,?'女');
insert?into?Student?values('06'?,?'吳蘭'?,?'1992-03-01'?,?'女');
insert?into?Student?values('07'?,?'鄭竹'?,?'1989-07-01'?,?'女');
insert?into?Student?values('08'?,?'王菊'?,?'1990-01-20'?,?'女');
--?初始化?課程表
insert?into?Course?values('01'?,?'語文'?,?'02');
insert?into?Course?values('02'?,?'數(shù)學(xué)'?,?'01');
insert?into?Course?values('03'?,?'英語'?,?'03');
--?初始化?老師表
insert?into?Teacher?values('01'?,?'張三');
insert?into?Teacher?values('02'?,?'李四');
insert?into?Teacher?values('03'?,?'王五');
--?初始化?成績(jī)表
insert?into?SC?values('01'?,?'01'?,?80);
insert?into?SC?values('01'?,?'02'?,?90);
insert?into?SC?values('01'?,?'03'?,?99);
insert?into?SC?values('02'?,?'01'?,?70);
insert?into?SC?values('02'?,?'02'?,?60);
insert?into?SC?values('02'?,?'03'?,?80);
insert?into?SC?values('03'?,?'01'?,?80);
insert?into?SC?values('03'?,?'02'?,?80);
insert?into?SC?values('03'?,?'03'?,?80);
insert?into?SC?values('04'?,?'01'?,?50);
insert?into?SC?values('04'?,?'02'?,?30);
insert?into?SC?values('04'?,?'03'?,?20);
insert?into?SC?values('05'?,?'01'?,?76);
insert?into?SC?values('05'?,?'02'?,?87);
insert?into?SC?values('06'?,?'01'?,?31);
insert?into?SC?values('06'?,?'03'?,?34);
insert?into?SC?values('07'?,?'02'?,?89);
insert?into?SC?values('07'?,?'03'?,?98);
準(zhǔn)備好基礎(chǔ)數(shù)據(jù)后，我們來看看具體需要解答的SQL題目。
SQL題目
1、查詢“01”課程比“02”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)；
2、查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；
3、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)
4、查詢姓“李”的老師的個(gè)數(shù)；
5、查詢沒學(xué)過“張三”老師課的同學(xué)的學(xué)號(hào)、姓名；
6、查詢學(xué)過編號(hào)“01”并且也學(xué)過編號(hào)“02”課程的同學(xué)的學(xué)號(hào)、姓名；
7、查詢學(xué)過“張三”老師所教的課的同學(xué)的學(xué)號(hào)、姓名；
8、查詢課程編號(hào)“01”的成績(jī)比課程編號(hào)“02”課程低的所有同學(xué)的學(xué)號(hào)、姓名；
9、查詢所有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名；
10、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名；
11、查詢至少有一門課與學(xué)號(hào)為“01”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名；
12、查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的學(xué)號(hào)和姓名
13、把“SC”表中“張三”老師教的課的成績(jī)都更改為此課程的平均成績(jī)；
14、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
15、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)
16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息
17、按平均成績(jī)從高到低顯示所有學(xué)生的平均成績(jī)
18、查詢各科成績(jī)最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率
19、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序
20、查詢學(xué)生的總成績(jī)并進(jìn)行排名
21、查詢不同老師所教不同課程平均分從高到低顯示
22、查詢所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)
23、統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比
24、查詢學(xué)生平均成績(jī)及其名次
25、查詢各科成績(jī)前三名的記錄
26、查詢每門課程被選修的學(xué)生數(shù)
27、查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名
28、查詢男生、女生人數(shù)
29、查詢名字中含有"風(fēng)"字的學(xué)生信息
30、查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
31、查詢1990年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime)
32、查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列
37、查詢不及格的課程，并按課程號(hào)從大到小排列
38、查詢課程編號(hào)為"01"且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；
40、查詢選修“張三”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)
42、查詢每門功課成績(jī)最好的前兩名
43、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過5人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列
44、檢索至少選修兩門課程的學(xué)生學(xué)號(hào)
45、查詢選修了全部課程的學(xué)生信息
46、查詢各學(xué)生的年齡
47、查詢本周過生日的學(xué)生
48、查詢下周過生日的學(xué)生
49、查詢本月過生日的學(xué)生
50、查詢下月過生日的學(xué)生
一共50道題，大家可以先自己試試，看看能不能全部做對(duì)，如果能全部做對(duì)，那不管是面試還是工作，你都能輕松應(yīng)對(duì)了。
如果只是寫了一部分，那就請(qǐng)跟著我往下面看。
題解
1、查詢“01”課程比“02”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)；
select?distinct?t1.sid?as?sid
from?
????(select?*?from?sc?where?cid='01')t1
left?join?
????(select?*?from?sc?where?cid='02')t2
on?t1.sid=t2.sid
where?t1.score>t2.score
2、查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；
select?
????sid
????,avg(score)
from?sc
group?by?sid
having?avg(score)>60
3、查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)
select
????student.sid?as?sid
????,sname
????,count(distinct?cid)?course_cnt
????,sum(score)?as?total_score
from?student
left?join?sc?
on?student.sid=sc.sid
group?by?student.sid,sname
4、查詢姓“李”的老師的個(gè)數(shù)；
select
????count(distinct?tid)?as?teacher_cnt
from?teacher
where?tname?like?'李%'
5、查詢沒學(xué)過“張三”老師課的同學(xué)的學(xué)號(hào)、姓名；
select
????sid,sname
from?student
where?sid?not?in?
????(
????????select
????????????sc.sid
????????from?teacher
????????left?join?course
????????????on?teacher.tid=course.tid
????????left?join?sc
????????????on?course.cid=sc.cid
????????where?teacher.tname='張三'
????)
6、查詢學(xué)過“01”并且也學(xué)過編號(hào)“02”課程的同學(xué)的學(xué)號(hào)、姓名；
select
????t.sid?as?sid
????,sname
from?
????(
????????select
????????????sid
????????????,count(if(cid='01',score,null))?as?count1
????????????,count(if(cid='02',score,null))?as?count2
????????from?sc
????????group?by?sid
????????having?count(if(cid='01',score,null))>0?and?count(if(cid='02',score,null))>0
????)t
left?join?student
????on?t.sid=student.sid
7、查詢學(xué)過“張三”老師所教的課的同學(xué)的學(xué)號(hào)、姓名；
select
????student.sid
????,sname
from?
????(
????????select
????????????distinct?cid?
????????from?course
????????left?join?teacher?
????????on?course.tid=teacher.tid
????????where?teacher.tname='張三'
????)course
left?join?sc?
????on?course.cid=sc.cid
left?join?student
????on?sc.sid=student.sid
group?by?student.sid,sname
8、查詢課程編號(hào)“01”的成績(jī)比課程編號(hào)“02”課程低的所有同學(xué)的學(xué)號(hào)、姓名；
select
????t1.sid,sname
from?
????(
????????select?distinct?t1.sid?as?sid
????????from?
????????????(select?*?from?sc?where?cid='01')t1
????????left?join?
????????????(select?*?from?sc?where?cid='02')t2
????????on?t1.sid=t2.sid
????????where?t1.score?<?t2.score
????)t1
left?join?student
????on?t1.sid=student.sid
9、查詢所有課程成績(jī)小于60分的同學(xué)的學(xué)號(hào)、姓名；
select
????t1.sid,sname
from?
????(
????????select
????????????sid,max(score)
????????from?sc
????????group?by?sid
????????having?max(score)<60
????)t1
left?join?student
????on?t1.sid=student.sid
10、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名；
select
????t1.sid,sname
from?
????(
????????select
????????????count(cid),sid
????????from?sc
????????group?by?sid
????????having?count(cid)?<?(select?count(distinct?cid)?from?course)
????)t1
left?join?student
????on?t1.sid=student.sid
11、查詢至少有一門課與學(xué)號(hào)為“01”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名；
select
????distinct?sc.sid
from?
????(
????????select
????????????cid
????????from?sc
????????where?sid='01'
????)t1
left?join?sc
????on?t1.cid=sc.cid
12、查詢和"01"號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的學(xué)號(hào)和姓名
#注意是和'01'號(hào)同學(xué)課程完全相同但非學(xué)習(xí)課程數(shù)相同的,這里我用左連接解決這個(gè)問題
select
????t1.sid,sname
from
????(
????????select
????????????sc.sid
????????????,count(distinct?sc.cid)
????????from?
????????????(
????????????????select
????????????????????cid
????????????????from?sc
????????????????where?sid='01'
????????????)t1?#選出01的同學(xué)所學(xué)的課程
????????left?join?sc
????????????on?t1.cid=sc.cid
????????group?by?sc.sid
????????having?count(distinct?sc.cid)=?(select?count(distinct?cid)?from?sc?where?sid?=?'01')
????)t1
left?join?student
????on?t1.sid=student.sid
where?t1.sid!='01'
13、把“SC”表中“張三”老師教的課的成績(jī)都更改為此課程的平均成績(jī)；
#暫跳過update題目
14、查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
select?
????sname
from?student
where?sid?not?in
????(
????????select
????????????distinct?sid
????????from?sc
????????left?join?course
????????????on?sc.cid=course.cid
????????left?join?teacher
????????????on?course.tid=teacher.tid?
????????where?tname='張三'
????)
15、查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績(jī)
select
????t1.sid,sname,avg_score
from?
????(
????????select
????????????sid,count(if(score<60,cid,null)),avg(score)?as?avg_score
????????from?sc
????????group?by?sid
????????having?count(if(score<60,cid,null))?>=2
????)t1
left?join?student
????on?t1.sid=student.sid
16、檢索"01"課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的學(xué)生信息
select?*
from?sc
where?cid?=?'01'?and?score?<?60
order?by?score?desc
17、按平均成績(jī)從高到低顯示所有學(xué)生的平均成績(jī)
select?sid,avg(score)
from?sc
group?by?sid
order?by?avg(score)?desc
18、查詢各科成績(jī)最高分、最低分和平均分：以如下形式顯示：課程ID，課程name，最高分，最低分，平均分，及格率
select
????sc.cid
????,cname
????,max(score)?as?max_score
????,min(score)?as?min_score
????,avg(score)?as?avg_score
????,count(if(score>=60,sid,null))/count(sid)?as?pass_rate
from?sc?
left?join?course
????on?sc.cid=course.cid
group?by?sc.cid
19、按各科平均成績(jī)從低到高和及格率的百分?jǐn)?shù)從高到低順序
#這里先按照平均成績(jī)排序，再按照及格百分?jǐn)?shù)排序，題目有點(diǎn)奇怪
select?
????cid
????,avg(score)?as?avg_score
????,count(if(score>=60,sid,null))/count(sid)?as?pass_rate
from?sc
group?by?cid
order?by?avg_score,pass_rate?desc
20、查詢學(xué)生的總成績(jī)并進(jìn)行排名
select
????sid
????,sum(score)?as?sum_score
from?sc
group?by?sid
order?by?sum_score?desc
21、查詢不同老師所教不同課程平均分從高到低顯示
select
????tid
????,avg(score)?as?avg_score
from?course
left?join?sc
????on?course.cid=sc.cid
group?by?tid
order?by?avg_score?desc
22、查詢所有課程的成績(jī)第2名到第3名的學(xué)生信息及該課程成績(jī)
select
????sid,rank_num,score,cid
from
????(
????????select
????????????rank()?over(partition?by?cid?order?by?score?desc)?as?rank_num
????????????,sid
????????????,score
????????????,cid
????????from?sc
????)t
where?rank_num?in?(2,3)
23、統(tǒng)計(jì)各科成績(jī)各分?jǐn)?shù)段人數(shù)：課程編號(hào),課程名稱,[100-85],[85-70],[70-60],[0-60]及所占百分比
select
????sc.cid
????,cname
????,count(if(score?between?85?and?100,sid,null))/count(sid)
????,count(if(score?between?70?and?85,sid,null))/count(sid)
????,count(if(score?between?60?and?70,sid,null))/count(sid)
????,count(if(score?between?0?and?60,sid,null))/count(sid)
from?sc
left?join?course
????on?sc.cid=course.cid
group?by?sc.cid,cname
24、查詢學(xué)生平均成績(jī)及其名次
select
????sid
????,avg_score
????,rank()?over?(order?by?avg_score?desc)
from?
????(
????????select
????????????sid
????????????,avg(score)?as?avg_score
????????from?sc
????????group?by?sid
????)t
25、查詢各科成績(jī)前三名的記錄
select
????sid,cid,rank1
from?
????(
????????select
????????????cid
????????????,sid
????????????,rank()?over(partition?by?cid?order?by?score?desc)?as?rank1
????????from?sc
????)t
where?rank1<=3
到這里，我們已經(jīng)完成一半的SQL題目了。有何感想？
中場(chǎng)休息一會(huì)！
還沒有關(guān)注我的趕緊關(guān)注一波唄！
26、查詢每門課程被選修的學(xué)生數(shù)
select
????count(sid)
????,cid
from?sc
group?by?cid
27、查詢出只選修了一門課程的全部學(xué)生的學(xué)號(hào)和姓名
#只查出來sid即可，后面懶得交student表
select
????sid
from?sc
group?by?sid
having?count(cid)?=1
28、查詢男生、女生人數(shù)
select
????ssex
????,count(distinct?sid)
from?student
group?by?ssex
29、查詢名字中含有"風(fēng)"字的學(xué)生信息
select
????sid,sname
from?student
where?sname?like?'%風(fēng)%'
30、查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
#題目有歧義，這套題的質(zhì)量感覺有點(diǎn)差
select
????ssex
????,sname
????,count(sid)
from?student
group?by?ssex,sname
having?count(sid)>=2
31、查詢1990年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime)
select
????sid,sname,sage
from?student
where?year(sage)=1990
32、查詢每門課程的平均成績(jī)，結(jié)果按平均成績(jī)升序排列，平均成績(jī)相同時(shí)，按課程號(hào)降序排列
select
????cid,avg(score)?as?avg_score
from?sc
group?by?cid
order?by?avg_score,cid?desc
37、查詢不及格的課程，并按課程號(hào)從大到小排列
#有問題的題目
select
????cid,sid,score
from?sc
where?score<60
order?by?cid?desc,sid
38、查詢課程編號(hào)為"01"且課程成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)和姓名；
select
????sid,cid,score
from?sc
where?cid='01'?and?score>60
40、查詢選修“張三”老師所授課程的學(xué)生中，成績(jī)最高的學(xué)生姓名及其成績(jī)
select
????sc.sid,sname,cname,score
from?sc
left?join?course
????on?sc.cid=course.cid
left?join?teacher
????on?course.tid=teacher.tid
left?join?student
????on?sc.sid=student.sid
where?tname='張三'
order?by?score?desc
limit?1;
42、查詢每門功課成績(jī)最好的前兩名
##感覺題目重復(fù)了
select
????cid,sid,rank1
from?
????(
????????select
????????????cid
????????????,sid
????????????,rank()?over(partition?by?cid?order?by?score?desc)?as?rank1
????????from?sc?
????)t
where?rank1?<=2
43、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過5人的課程才統(tǒng)計(jì)）。
要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列
select
????cid
????,count(sid)?as?cnt
from?sc
group?by?cid
having?cnt>=5
order?by?count(sid)?desc,cid
44、檢索至少選修兩門課程的學(xué)生學(xué)號(hào)
select
????sid
????,count(cid)
from?sc
group?by?sid
having?count(cid)>=2
45、查詢選修了全部課程的學(xué)生信息
#不太嚴(yán)謹(jǐn)，但實(shí)務(wù)中應(yīng)該沒問題，如需嚴(yán)謹(jǐn)見12題思路
select
????sid
????,count(cid)
from?sc
group?by?sid
having?count(cid)=(select?count(distinct?cid)?from?sc)
46、查詢各學(xué)生的年齡
select
????sid,sname,year(curdate())-year(sage)?as?sage
from?student
47、查詢本周過生日的學(xué)生
select
????sid,sname,sage
from?student
where?weekofyear(sage)=weekofyear(curdate())
48、查詢下周過生日的學(xué)生
select?
????sid,sname,sage
from?student
where?weekofyear(sage)?=?weekofyear(date_add(curdate(),interval?1?week))
49、查詢本月過生日的學(xué)生
select
????sid,sname,sage
from?student
where?month(sage)?=?month(curdate())
50、查詢下月過生日的學(xué)生
select
????sid,sname,sage
from?student
where?month(date_sub(sage,interval?1?month))?=?month(curdate())
好了，50道題我們就這樣搞定了，如果對(duì)于上面題解有疑問的，可以關(guān)注我，我們私下探討。
