本文共 6355 字，大约阅读时间需要 21 分钟。

作为一枚Java后端开发者，数据库知识必不可少，对数据库的掌握熟悉度的考察也是对这个人是否有扎 实基本功的考察。特别对于初级开发者，面试可能不会去问框架相关知识，但是绝对不会不去考察数据 库知识，这里收集一些常见类型的SQL语句，无论对于平常开发还是准备面试，都会有助益。 

基础表结构

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) agroup by sno having avg(a.score)>60

3、查询所有同学的学号、姓名、选课数、总成绩

select a.sno as 学号, b.sname as 姓名, count(a.cno) as 选课数, sum(a.score) as 总成绩 from sc a, student bwhere a.sno = b.sno group by a.sno, b.sname 或者select student.sno as 学号, student.sname as 姓名, count(sc.cno) as 选课数, sum(score) as 总成绩from student left outer join sc on student.sno = sc.sno group by student.sno, sname

4、查询姓“张”的老师的个数

select count(tno)from teacherwhere tname like '张%'

5、查询没学过“张三”老师课的同学的学号、姓名

select student.sno,student.sname from studentwhere 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 studentwhere 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 cwhere 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 sno-- 子查询结果如果要当临时表来使用的话需要起个别名(比如这里面的t)from (	-- 学过该老师课程的同学学号(包含只学一门)	select sno,count(cno) num	from sc	where cno in (		-- 该老师教了哪些课程		select cno		from course		where tno in (			select tno 			from teacher			where tname = '李四'		)	) group by sno) twhere t.num = (	-- 统计该老师总课程数	select count(cno)	from course	where tno = (		select tno		from teacher		where tname = '李四'	))

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) cwhere b.score > c.score and b.sno = c.sno and a.sno = b.sno

9、查询所有课程成绩小于60分的同学的学号、姓名

select sno,sname from studentwhere sno not in (select distinct sno from sc where score > 60)

10、查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名

select distinct a.sno, a.sname from student a, sc bwhere a.sno <> 1 and a.sno=b.sno and b.cno in (select cno from sc where sno = 1)或者select s.sno,s.snamefrom 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) r1where r1.sno=s.sno

11、 把“sc”表中“王五”所教课的成绩都更改为此课程的平均成绩

update sc set score = (select avg(sc_2.score) from sc sc_2  where sc_2.cno = sc.cno)where cno in (select c.cno from course c left join teacher t on t.tno = c.tnowhere t.tname = '王五');

12、查询和编号为1002的同学学习的课程完全相同的其他同学学号和姓名

select sc_1.snofrom (select cno from sc where sno='1002')aleft join sc sc_1 on a.cno = sc_1.cnowhere sc_1.sno<>'1002' group by sc_1.sno having count(sc_1.cno) = (select count(cno) from sc where sno='1002');select a.sno,s.sname from (select sno,GROUP_CONCAT(cno order by cno separator ',') as cid_str from sc where sno='1002')b,(select sno,GROUP_CONCAT(cno order by cno separator ',') as cid_str from sc group by sno)aleft join student s on a.sno = s.snowhere a.cid_str = b.cid_str and a.sno<>'1002';

13、删除学习“王五”老师课的sc表记录

delete sc from course, teacherwhere 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 studentwhere sno not in (select sno from sc where cno = 3)

15、按平平均分从高到低显示所有学生的如下统计报表：学号，企业管理，马克思，UML，数据库，物理，课程数，平均分

select sno as 学号,max(case when cno = 1 then score end) AS 企业管理,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 数据库,max(case when cno = 5 then score end) AS 物理,count(cno) AS 课程数,avg(score) AS 平均分FROM sc GROUP by snoORDER by avg(score) DESC

16、查询各科成绩最高分和最低分：以如下形式显示：课程号，最高分，最低分

select cno as 课程号, max(score) as 最高分, min(score) 最低分from sc group by cnoselect	course.cno as '课程号',MAX(score) as '最高分',MIN(score) as '最低分' from sc,coursewhere sc.cno=course.cno group by course.cno

17、按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT t.cno as 课程号,c.cname as 课程名,COALESCE(avg(score),0) as 平均成绩,100*sum(case when COALESCE(score,0)>=60 then 1 else 0 END)/count(*) as 及格百分数from sc tleft join course c on t.cno = c.cnogroup by t.cnoorder by 100*sum(case when COALESCE(score,0)>=60 then 1 else 0 END)/count(*);

18、查询如下课程平均成绩和及格率的百分数(用"1行"显示)：企业管理（001），马克思（002），UML（003），数据库（004）

selectavg(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 及格率4from sc

19、查询不同老师所教不同课程平均分, 从高到低显示

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 cwhere t.tno=c.tno)r where sc.cno=r.rcsogroup by sc.cno,r.tname,r.rname order by AVG(score) desc

20、查询如下课程成绩均在第3名到第6名之间的学生的成绩：学生ID，学生姓名，企业管理，马克思，UML，数据库，平均成绩

select top 6 max(a.sno) 学号, max(b.sname) 姓名, max(case when cno = 1 then score end) as 企业管理, 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 数据库, 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.snogroup by a.sno

转载地址：https://lux-sun.blog.csdn.net/article/details/117302100 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！