数据库SQL经典面试题详解

SQL面试题不外乎增删改查，对于这种类型的题目，我的经验是先把每个表的关联关系搞清楚，它们之间是通过哪些字段关联的，可以在纸上画出关联图，再去编写SQL就不会那么头大了。

下面是四张表的关联关系图：

1创建表

1.1学生表(Student)

字段: SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

create table Student
(
Sid varchar(8) not null primary key, /*学生编号，括号里是允许输入的长度，加主键*/
Sname varchar(10) not null, /*学生姓名，加not null 表示不允许为空*/
Sage int, /*学生年龄*/
Ssex varchar(10) /*学生性别*/
)

1.2课程表(Course)

字段：CId 课程编号,Cname 课程名称,Tid教课老师编号

create table Course
(
Cid varchar(8) not null primary key, /*课程编号*/
Cname varchar(10) not null, /*课程名称*/
Tid varchar(8) /*教课老师编号*/
)

1.3教师表(Teacher)

字段：TId 教师编号,Tname 教师姓名

create table Teacher
(
Tid varchar(8) not null primary key, /*教师编号*/
Tname varchar(10) not null /*教师姓名*/
)

1.4成绩表（SC）

字段：SId 学生编号,CId 课程编号,score 分数

create table SC
(
Sid varchar(8) not null, /*学生编号*/
Cid varchar(8) not null, /*课程编号*/
score int /*成绩*/
)

2在表中插入数据

2.1学生表

insert into Student(Sid,Sname,Sage,Ssex) values('1','张敏','20','女');
insert into Student(Sid,Sname,Sage,Ssex) values('2','吴晓磊','21','男');
insert into Student(Sid,Sname,Sage,Ssex) values('3','钱娟娟','19','女');
insert into Student(Sid,Sname,Sage,Ssex) values('4','李少臣','21','男');
insert into Student(Sid,Sname,Sage,Ssex) values('5','师诗','21','女');
insert into Student(Sid,Sname,Sage,Ssex) values('6','朱萌','21','女');
insert into Student(Sid,Sname,Sage,Ssex) values('7','张雷','22','男');
insert into Student(Sid,Sname,Sage,Ssex) values('8','陈宇轩','21','男');

;

2.2课程表

insert into Course(Cid,Cname,Tid) values('1','语文','1');
insert into Course(Cid,Cname,Tid) values('2','数学','3');
insert into Course(Cid,Cname,Tid) values('3','英语','2');

2.3教师表

insert into Teacher(Tid,Tname) values('1','吕薇');
insert into Teacher(Tid,Tname) values('2','张柏龙');
insert into Teacher(Tid,Tname) values('3','张峰');

2.4成绩表

insert into SC(Sid,Cid,score) values('1','1','80');
insert into SC(Sid,Cid,score) values('1','2','85');
insert into SC(Sid,Cid,score) values('1','3','89');
insert into SC(Sid,Cid,score) values('2','1','67');
insert into SC(Sid,Cid,score) values('2','2','72');
insert into SC(Sid,Cid,score) values('2','3','78');
insert into SC(Sid,Cid,score) values('3','1','90');
insert into SC(Sid,Cid,score) values('3','2','89');
insert into SC(Sid,Cid,score) values('3','3','98');
insert into SC(Sid,Cid,score) values('4','1','68');
insert into SC(Sid,Cid,score) values('4','2','58');
insert into SC(Sid,Cid,score) values('4','3','77');
insert into SC(Sid,Cid,score) values('5','1','96');
insert into SC(Sid,Cid,score) values('5','2','92');
insert into SC(Sid,Cid,score) values('5','3','88');
insert into SC(Sid,Cid,score) values('6','1','76');
insert into SC(Sid,Cid,score) values('6','2','77');
insert into SC(Sid,Cid,score) values('6','3','83');
insert into SC(Sid,Cid,score) values('7','1','90');
insert into SC(Sid,Cid,score) values('7','2','92');
insert into SC(Sid,Cid,score) values('7','3','99');
insert into SC(Sid,Cid,score) values('8','1','80');
insert into SC(Sid,Cid,score) values('8','2','82');
insert into SC(Sid,Cid,score) values('8','3','79');

3解题思路

单表查询可以参考下面的思路：

select 要展示的结果

from 从哪张表中查数据

where 查询条件

group by 分组

having 对分组结果指定条件

order by 对查询结果排序

limit 从查询结果中取出指定行;

多表关联查询可以参考下面的思路：

select 要展示的结果

from  表1

inner join 表2

​​on 关联条件

inner join 表3

on 关联条件

group by 分组

order by 对查询结果排序

…

4试题

4.1查询名字为赵敏的学生表数据

select * from student where Sname = '张敏'

4.2修改编号2的学生名字为赵莹莹

update student set Sname = '赵莹莹'
where Sid = '2'

4.3修改语文老师的名字为吴晓敏

update student set Tname = '吴晓敏'
where Tid = '2'

4.4删除名字为朱萌的学生

delete from student where Sname = '朱萌

4.5查询语文老师的名字

select teacher.Tname from teacher
inner join course
where teacher.Tid = course.Tid and course.Tid = '1'

备注：这里on和where的区别:

on先对左表进行筛选再生成关联表，即优先级高于join；

where先关联生成临时表后,再对临时表进行筛选，即优先级低于join

4.6统计语文成绩大于70的学生信息

select a.Sname,a.Sage,a.Ssex,b.score from Student a, SC b
where a.Sid=b.Sid and b.Cid=1 and b.score >70

4.7统计各科分数大于80的人

select a.Sid,a.Sname
from student a,
(select Sid from sc
group by Sid
having min(score) > 80) as b
where b.Sid = a.Sid;

4.8查询所有学生的数学成绩，降序排列

解析：三表联查（学生表、课程表、成绩表）

select a.Cname, b.Sname, c.score
from course as a inner join student as b
inner join sc as c
where a.cid = c.cid and a.cid = 2 and b.Sid = c.Sid
order by c.score desc

4.9查询每门课及格和不及格的人数

解析：group by意思是根据一定的规则进行分组。它的作用是将一个数据集划分成若干个小的区域，然后对这些小区域进行数据处理

select b.Cname,
sum(case when a.score >60 then 1 else 0 end) as 及格人数,
sum(case when a.score <60 then 1 else 0 end) as 不及格人数
from sc a
inner join course b
where a.Cid = b.Cid
group by a.Cid

4.10查询每个学生的总成绩

select a.Sname,sum(b.score) Total
from student a
inner join sc b
where a.Sid = b.Sid
group by b.Sid

4.11查询语文成绩最高分数

select a.Cname,max(b.score) Max
from course a
inner join sc b
on a.Cid = b.Cid
group by a.Cid

4.12查询出英语及格的全部男生

select a.Sname
from student a
inner join sc b
on b.score>= 60 and a.Ssex = '男' and b.Cid = 3 and a.Sid = b.Sid

4.13查询出所有女生的成绩

select a.Sname,b.score,c.Cname
from student a
inner join sc b
on a.Ssex = '女' and a.Sid = b.Sid
inner join course c
on b.Cid = c.Cid
order by b.Sid

4.14查询学生平均成绩及其名次

解析：先将学生按照平均成绩排名，然后使用窗口函数row_number增加“名次”一列

select Sid ,avg(score) 平均分,
row_number () over( order by avg(score) desc) 名次
from sc
group by Sid

 

作为一个软件测试的过来人，我想尽自己最大的努力，帮助每一个伙伴都能顺利找到工作。所以我整理了下面这份资源，现在免费分享给大家，有需要的小伙伴可以关注【公众号：开心螺蛳粉】自提！

软件测试面试文档

我们学习必然是为了找到高薪的工作，下面这些面试题是来自阿里、腾讯、字节等一线互联网大厂最新的面试资料，并且有字节大佬给出了权威的解答，刷完这一套面试资料相信大家都能找到满意的工作。

行动吧，在路上总比一直观望的要好，未来的你肯定会感谢现在拼搏的自己！如果想学习提升找不到资料，没人答疑解惑时，请及时加入群：1150305204，里面有各种测试开发资料和技术可以一起交流哦。