赢在面试之数据库篇（12）

àì夳堔傛蜴生んèń 2021-08-19 13:27 322阅读 0赞

**[返回目录列表：][Link 1][https://blog.csdn.net/liming1016/article/details/103232959][Link 1]**

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

基本表结构：

* student(sno,sname,sage,ssex)学生表  
        course(cno,cname,tno) 课程表  
        sc(sno,cno,score) 成绩表*

*        teacher(tno,tname) 教师表*

**111、把“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='王五'*

**112、查询和编号为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)*  
  
**113、删除学习“王五”老师课的sc表记录**  
*delete sc from course, teacher  
where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'*  
  
**114、向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)*  
  
**115、按平平均分从高到低显示所有学生的如下统计报表：  
\-- 学号,企业管理,马克思,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 sno  
ORDER by avg(score) DESC*  
  
**116、查询各科成绩最高分和最低分：**

**以如下形式显示：课程号，最高分，最低分**  
*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*  
  
**117、按各科平均成绩从低到高和及格率的百分数从高到低顺序**  
*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*  
  
**118、查询如下课程平均成绩和及格率的百分数(用"1行"显示):**

**企业管理（001），马克思（002），UML （003），数据库（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*  
  
**119、查询不同老师所教不同课程平均分, 从高到低显示**  
*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*  
  
**120、查询如下课程成绩均在第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.sno  
group by a.sno*

[Link 1]: https://blog.csdn.net/liming1016/article/details/103232959