??作為一枚Java后端開發(fā)者，數(shù)據(jù)庫知識必不可少，對數(shù)據(jù)庫的掌握熟悉度的考察也是對這個人是否有扎實基本功的考察。特別對于初級開發(fā)者，面試可能不會去問框架相關知識，但是絕對不會不去考察數(shù)據(jù)庫知識，這里收集一些常見類型的SQL語句，無論對于平常開發(fā)還是準備面試，都會有助益。
基本表結構：
student(sno,sname,sage,ssex)學生表
course(cno,cname,tno) 課程表
sc(sno,cno,score) 成績表
?teacher(tno,tname) 教師表
1、查詢課程1的成績比課程2的成績高的所有學生的學號
select a.sno from
(select sno,score from sc where cno=1) a,
(select sno,score from sc where cno=2) b
where a.score>b.score and a.sno=b.sno
2、查詢平均成績大于60分的同學的學號和平均成績select a.sno as "學號", avg(a.score) as "平均成績"
from
(select sno,score from sc) a
group by sno having avg(a.score)>60
3、查詢所有同學的學號、姓名、選課數(shù)、總成績select a.sno as?學號, b.sname as?姓名,
count(a.cno) as?
選課數(shù), sum(a.score) as?總成績
from sc a, student b
where a.sno = b.sno
group by a.sno, b.sname
或者：
selectstudent.sno as?學號, student.sname as?姓名,
count(sc.cno) as?
選課數(shù), sum(score) as?總成績
from student left Outer join sc on student.sno = sc.sno
group by student.sno, sname
4、查詢姓“張”的老師的個數(shù)
selectcount(distinct(tname)) from teacher where tname like '張%'
或者：
select tname as "姓名", count(distinct(tname)) as "人數(shù)"
from teacher
where tname like'張%'
group by tname
5、查詢沒學過“張三”老師課的同學的學號、姓名
select student.sno,student.sname from student
where sno not in (select distinct(sc.sno) from sc,course,teacher
where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='張三')
6、查詢同時學過課程1和課程2的同學的學號、姓名
select sno, sname from student
where sno in (select sno from sc where sc.cno = 1)
and sno in (select sno from sc where sc.cno = 2)
或者：
selectc.sno, c.sname from
(select sno from sc where sc.cno = 1) a,
(select sno from sc where sc.cno = 2) b,
student c
where a.sno = b.sno and a.sno = c.sno
或者：
select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno=1
and exists( select * from sc as sc_2 where sc_2.sno=sc.sno and sc_2.cno=2)
7、查詢學過“李四”老師所教所有課程的所有同學的學號、姓名select a.sno, a.sname from student a, sc b
where a.sno = b.sno and b.cno in
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四')
或者：
select a.sno, a.sname from student a, sc b,
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四') e
where a.sno = b.sno and b.cno = e.cno
8、查詢課程編號1的成績比課程編號2的成績高的所有同學的學號、姓名
select a.sno, a.sname from student a,
(select sno, score from sc where cno = 1) b,
(select sno, score from sc where cno = 2) c
where b.score > c.score and b.sno = c.sno and a.sno = b.sno
9、查詢所有課程成績小于60分的同學的學號、姓名
select sno,sname from student
where sno not in (select distinct sno from sc where score > 60)
10、查詢至少有一門課程與學號為1的同學所學課程相同的同學的學號和姓名
select distinct a.sno, a.sname
from student a, sc b
where a.sno <> 1 and a.sno=b.sno and
b.cno in (select cno from sc where sno = 1)
或者：
select s.sno,s.sname
from student s,
(select sc.sno
from sc
where sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and sc.sno<>1
group by sc.sno)r1
where r1.sno=s.sno
11、把“sc”表中“王五”所教課的成績都更改為此課程的平均成績
update sc set score = (select avg(sc_2.score) from sc sc_2 wheresc_2.cno=sc.cno)
from course,teacher where course.cno=sc.cno and course.tno=teacher.tno andteacher.tname='王五'
12、查詢和編號為2的同學學習的課程完全相同的其他同學學號和姓名
這一題分兩步查：
1，
select sno
from sc
where sno <> 2
group by sno
having sum(cno) = (select sum(cno) from sc where sno = 2)
2，
select b.sno, b.sname
from sc a, student b
where b.sno <> 2 and a.sno = b.sno
group by b.sno, b.sname
having sum(cno) = (select sum(cno) from sc where sno = 2)
13、刪除學習“王五”老師課的sc表記錄
delete sc from course, teacher
where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'
14、向sc表中插入一些記錄，這些記錄要求符合以下條件：
將沒有課程3成績同學的該成績補齊, 其成績取所有學生的課程2的平均成績
insert sc select sno, 3, (select avg(score) from sc where cno = 2)
from student
where sno not in (select sno from sc where cno = 3)
15、按平平均分從高到低顯示所有學生的如下統(tǒng)計報表：
-- 學號,企業(yè)管理,馬克思,UML,數(shù)據(jù)庫,物理,課程數(shù),平均分
select sno as 學號
,max(case when cno = 1 then score end) AS 企業(yè)管理
,max(case when cno = 2 then score end) AS 馬克思
,max(case when cno = 3 then score end) AS UML
,max(case when cno = 4 then score end) AS 數(shù)據(jù)庫
,max(case when cno = 5 then score end) AS 物理
,count(cno) AS 課程數(shù)
,avg(score) AS 平均分
FROM sc
GROUP by sno
ORDER by avg(score) DESC
16、查詢各科成績最高分和最低分：
以如下形式顯示：課程號，最高分，最低分
select cno as 課程號, max(score) as 最高分, min(score) 最低分
from sc group by cno
select? course.cno as '課程號'
,MAX(score) as '最高分'
,MIN(score) as '最低分'
from sc,course
where sc.cno=course.cno
group by course.cno
17、按各科平均成績從低到高和及格率的百分數(shù)從高到低順序
SELECT t.cno AS 課程號,
max(course.cname)AS 課程名,
isnull(AVG(score),0) AS 平均成績,
100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/count(1) AS 及格率
FROM sc t, course
where t.cno = course.cno
GROUP BY t.cno
ORDER BY 及格率 desc
18、查詢如下課程平均成績和及格率的百分數(shù)(用"1行"顯示):
企業(yè)管理（001），馬克思（002），UML （003），數(shù)據(jù)庫（004）
select
avg(case when cno = 1 then score end) as 平均分1,
avg(case when cno = 2 then score end) as 平均分2,
avg(case when cno = 3 then score end) as 平均分3,
avg(case when cno = 4 then score end) as 平均分4,
100 * sum(case when cno = 1 and score > 60 then 1 else 0 end) / sum(casewhen cno = 1 then 1 else 0 end) as 及格率1,
100 * sum(case when cno = 2 and score > 60 then 1 else 0 end) / sum(casewhen cno = 2 then 1 else 0 end) as 及格率2,
100 * sum(case when cno = 3 and score > 60 then 1 else 0 end) / sum(casewhen cno = 3 then 1 else 0 end) as 及格率3,
100 * sum(case when cno = 4 and score > 60 then 1 else 0 end) / sum(casewhen cno = 4 then 1 else 0 end) as 及格率4
from sc
19、查詢不同老師所教不同課程平均分, 從高到低顯示
select max(c.tname) as 教師, max(b.cname) 課程, avg(a.score) 平均分
from sc a, course b, teacher c
where a.cno = b.cno and b.tno = c.tno
group by a.cno
order by 平均分 desc
或者：
select r.tname as '教師',r.rname as '課程' , AVG(score) as '平均分'
from sc,
(select? t.tname,c.cno as rcso,c.cname as rname
from teacher t ,course c
where t.tno=c.tno)r
where sc.cno=r.rcso
group by sc.cno,r.tname,r.rname
order by AVG(score) desc
20、查詢如下課程成績均在第3名到第6名之間的學生的成績：
-- [學生ID],[學生姓名],企業(yè)管理,馬克思,UML,數(shù)據(jù)庫,平均成績
select top 6 max(a.sno) 學號, max(b.sname) 姓名,
max(case when cno = 1 then score end) as 企業(yè)管理,
max(case when cno = 2 then score end) as 馬克思,
max(case when cno = 3 then score end) as UML,
max(case when cno = 4 then score end) as 數(shù)據(jù)庫,
avg(score) as 平均分
from sc a, student b
where a.sno not in
(select top 2 sno from sc where cno = 1 order by score desc)
? and a.sno not in (select top 2 sno from sc where cno = 2 order by scoredesc)
? and a.sno not in (select top 2 sno from sc where cno = 3 order by scoredesc)
? and a.sno not in (select top 2 sno from sc where cno = 4 order by scoredesc)
? and a.sno = b.sno
group by a.sno
