

干货总结 | 图解SQL面试题：数据分析人求职必考，建议收藏

CDA数据分析师

2021-11-17

导读：数据分析人求职必考~

作者：俊欣

来源：关于数据分析与可视化

好了，关于pandas的内容我们先告一段落，今天我们来讲讲SQL方面的知识点，特别是对于绝大多数的数据分析师而言，SQL也是经常会被面试问到的内容。

目前我们有下面这4张表格

学生表
教师表
成绩表
课程表

我们先在Navicat当中创建表格并且插入数据，在Navicat当中创建表格并不难，点击新建表，然后输入字段名，并且规定好相应的数据类型即可，点击保存

然后我们向表中插入数据，语法如下

insert into 表名(字段名1, 字段名2, 字段名3,.....) 
values('val1', 'val2', 'val3', ......);

例如我们在学生表当中插入以下的数据

insert into student(学号,姓名,出生日期,性别) 
values('0001' , '张三' , '1991-05-01' , '男');

insert into student(学号,姓名,出生日期,性别) 
values('0002' , '李四' , '1990-04-21' , '男');

output

将其他数据插入到其他的表格当中也是一样的道理，这里就不做赘述了

简单查询

例如查找学生名字以“小”开头的名单，可以这么做

select * from student where 姓名 like '小%'

output

学号 姓名 出生日期 性别
0003 小红 1994-10-21 女
0004 小王 1996-07-20 男
0005 小张 1999-04-15 男
0006 小美 1997-04-05 女
0007 小丽 1995-07-10 女

这里的%表示任意字符串，例如'小%'则表示姓“小”的学生信息，而'%小'表示的是以“小”字结尾的学生姓名，而'%小%'代表的是学生姓名中带有“小”这个关键字

但是假如我们只是想要返回前面几行的数据的话，就可以使用limit关键字

select * from student where 姓名 like '小%' limit 3;

output

学号 姓名 出生日期 性别
0003 小红 1994-10-21 女
0004 小王 1996-07-20 男
0005 小张 1999-04-15 男

分组汇总查询

我们想要看一下报名参加“英语”课的学生有几人，我们知道“英语”课程对应的课程号是0003，所以用count()函数方法来计算次数即可

select count(*) as 学生人数 from score where 课程号 = '0003';

output

学生人数
      6

但要是我们想要查询一下每一门课程的最低、最高分以及平均分，可以通过group by关键字来执行，按照“课程号”进行分组汇总

select 课程号, min(成绩) as 最低分, max(成绩) as 最高分, avg(成绩) as 平均分 from score group by 课程号

output

课程号 最低分 最高分 平均分
0001   80   88     84.0000
0002   66   90     80.2500
0003   69   97     84.1667
0004   75   78     76.5000
0005   97   99     98.0000

鉴于上面的结果，我们也可以使用order by关键字来进行排序，根据“平均分”这一列

select 课程号, min(成绩) as 最低分, max(成绩) as 最高分, avg(成绩) as 平均分 from score group by 课程号 order by 平均分

output

课程号 最低分 最高分 平均分
0004 75 78 76.5000
0002 66 90 80.2500
0001 80 88 84.0000
0003 69 97 84.1667
0005 97 99 98.0000

默认的排序方式是升序排序，另外我们也可以尝试通过性别来进行分组查询

select count(*) as 不同性别的学生数量 from student group by 性别;

output

不同性别的学生数量
              4
              3

带有条件的分组查询

假设我们打算查询平均分大于80分的学生，首先我们要计算每个学生的平均成绩，然后再此的基础之上挑出80分以上的部分，所以可以group by 学号

select 学号, avg(成绩) as 平均成绩 from score group by 学号 having avg(成绩) > 80;

output

学号 平均成绩
0001     91
0003 81.66666666666667
0004 81.33333333333333
0005     91.5
0006     91

同理，我们也可以根据“课程号”来进行分组，group by 课程号，然后挑选出例如平均分大于70分的部分

select 课程号, avg(成绩) as 平均成绩 from score group by 课程号 having avg(成绩) > 70;

output

课程号 平均成绩
0001     84
0002     80.25
0003 84.16666666666667
0004     76.5
0005     98

上面提到的order by默认排序的方式是升序，我们也可以设置成降序来排序

select 课程号, avg(成绩) as 平均成绩 from score group by 课程号 having avg(成绩) > 70 order by avg(成绩) desc;

output

课程号 平均成绩
0005      98
0003 84.16666666666667
0001     84
0002     80.25
0004     76.5

这次我们再添加一个字段最高分，通过max()方法来进行统计，同时对平均分与最高分进行排序，当平均分相同的时候，就以最高分来进行排序

select 课程号, avg(成绩) as 平均成绩, max(成绩) as 最高分 from score group by 课程号 having avg(成绩) > 70 order by avg(成绩), max(成绩);

output

课程号 平均成绩 最高分
0004 76.5       78
0002 80.25       90
0001 84         88
0003 84.1666667 97
0005 98         99

我们在order by后面再放一个字段表示当以字段1排序的时候碰到相同的情况下，就以字段2来进行排序。

我们再来换一个字段，看一下选课超过两门的学生有哪些，我们首先是group by 学号，然后对“课程号”进行计数，挑选出满足条件的部分

select 学号, count(课程号) as 选课的数量 from score group by 学号 having count(课程号) > 2;

output

学号 选课的数量
0001       3
0003       3
0004       3

下面我们来处理一个复杂的查询，找出分数都在80分以上至少两门课程的学生，列出他们的学号以及平均分，我们先来筛选出分数都在80分以上的学生

select 学号 from score where 成绩 > 80

然后我们计算出他们的平均分

select 学号, avg(成绩) as 平均成绩 from score where 成绩 > 80 group by 学号;

output

学号 平均成绩
0001     91
0003     85
0004     97
0005     91.5
0006     91

最后再加上限制条件，“至少是两门课程”

select 学号, avg(成绩) as 平均成绩 from score where 成绩 > 80 group by 学号 having count(课程号) >= 2;

output

学号 平均成绩
0001     91
0005     91.5
0006     91

汇总排序

我们来看一下，对每位学生的总成绩进行统计并且进行排序是怎么来做的，我们用sum()方法来统计每个学生的总成绩

select 学号, sum(成绩) from score group by 学号;

然后我们用order by关键字来进行排序

select 学号, sum(成绩) as 总分 from score group by 学号 order by sum(成绩);

output

然后我们再添加一个限制条件，例如挑选出总分在200分以上的数据，可以通过having关键词来执行

select 学号, sum(成绩) as 总分 from score group by 学号 having sum(成绩) > 200 order by sum(成绩);

output

嵌套式查询

有时候我们需要写多层的SQL查询语句，一层的查询有时候显然不够用，例如我们想要查询出所有课程的成绩都是高于80分的学生学号与姓名，我们一层一层来分析，首先我们筛选出所有课程都高于80分学生的学号与成绩

select 学号, min(成绩) as 最低分 from score group by 学号 having min(成绩) > 80;

output

再得到了学号以及成绩了之后，我们将学号这一列提取出来，去student这张表当中去寻找满足条件的

select 学号,姓名 from student where 学号 in (select 学号 from score group by 学号 having min(成绩) > 80);

output

学号 姓名
0001 张三
0005 小张
0006 小美

通常来说，嵌套式的查询是涉及到了多张表格的联合，例如我们想要查询出选课的数量小于3门课程的学生姓名与学号，首先我们先筛选出选课数量小于3门课程的学生学号

select 学号, count(课程号) as 选课数量 from score group by 学号 having count(课程号) <= 2;

output

当然我们其实只要“学号”这一列，然后我们在此基础之上再进行查询

select 学号,姓名 from student where 学号 in (select 学号 from score group by 学号 having count(课程号) <= 2);

output

学号 姓名
0002 李四
0005 小张
0006 小美
0007 小丽

日期函数

下面我们来演练一下日期函数的使用，例如我们想获取当前的日期，可以用curdate()方法

select curdate();

output

curdate()
2021-11-16

若是打算获取当前的时刻，则可以用now()方法

select now();

output

now()
2021-11-16 22:37:41

由于篇幅的限制，这里就不多说了，我们来看一下具体实践当中的操作，我们筛选出出生年份在1994年的学生有哪些，可以这么来操作

select * from student where year(出生日期) = 1994;

output

学号 姓名 出生日期 性别
0003 小红 1994-10-21 女

同理我们来筛选出当月过生日的同学

select * from student where month(出生日期) = month(now());

跨表查询

有时候我们在进行数据查询的时候需要用到多张表格，将多张表格联结起来进行操作，例如列出所有学生的姓名、选课的数量以及总分成绩，我们就需要用到多张表格了，我们可以先尝试着连接student和score这两张表

select * from student left join score on student.`学号` = score.`学号`;

output

学号 姓名 出生日期 性别 学号(1) 课程号 成绩
0001 张三 1991-05-01 男 0001 0001 88
0001 张三 1991-05-01 男 0001 0002 90
0001 张三 1991-05-01 男 0001 0003 95
0002 李四 1990-04-21 男 0002 0002 66
0002 李四 1990-04-21 男 0002 0003 75
0003 小红 1994-10-21 女 0003 0001 80
0003 小红 1994-10-21 女 0003 0002 80
0003 小红 1994-10-21 女 0003 0003 85
0004 小王 1996-07-20 男 0004 0003 69
0004 小王 1996-07-20 男 0004 0004 78
0004 小王 1996-07-20 男 0004 0005 97
.....

然后再此基础之上，我们保留需要的这几个字段

select 姓名, count(课程号) as 选课的数量, sum(成绩) as 总成绩 from student left join score on student.`学号` = score.`学号` group by student.`姓名`;

output

姓名 选课的数量 总成绩
张三         3   273
李四         2   141
小红         3   245
小王         3   244
小张         2   183
小美         2   182
小丽         1   75

我们也可以将总成绩替换成平均成绩，然后做一个排序，取平均分最高的前三名

select 姓名, avg(成绩) as 平均分 from student left join score on student.`学号` = score.`学号` group by student.`姓名` limit 3;

output

姓名 平均分
张三 91.0000
李四 70.5000
小红 81.6667

最后通过limit关键字来控制输出，上面的例子是两张表格的连接，我们也可以尝试三张表格的连接，

select * from student inner join score on student.`学号` = score.`学号` inner join course on score.`课程号` = course.`课程号`;

output

学号 姓名 出生日期 性别 学号(1) 课程号 成绩 课程号(1) 课程名称 教师号
0001 张三 1991-05-01 男 0001 0001 88 0001 语文 0002
0001 张三 1991-05-01 男 0001 0002 90 0002 数学 0001
0001 张三 1991-05-01 男 0001 0003 95 0003 英语 0003
0002 李四 1990-04-21 男 0002 0002 66 0002 数学 0001
0002 李四 1990-04-21 男 0002 0003 75 0003 英语 0003
......

当然我们也可以四张表格来连接

select * from student inner join score on student.`学号` = score.`学号` inner join course on score.`课程号` = course.`课程号` inner join teacher on course.`教师号` = teacher.`教师号`;

要是我们想查询学生姓名、选课的课程名称以及授课的老师，可以这么来做

select 姓名, 课程名称, 教师姓名 from student inner join score on student.`学号` = score.`学号` inner join course on score.`课程号` = course.`课程号` inner join teacher on course.`教师号` = teacher.`教师号`;

output

姓名 课程名称 教师姓名
张三 语文 马老师
张三 数学 王老师
张三 英语 
李四 数学 王老师
李四 英语 
小红 语文 马老师
小红 数学 王老师
小红 英语 
小王 英语 
小王 物理 张老师
........

当然我们也可以将“成绩”这一关键字也给加上

select 姓名, 课程名称, 成绩, 教师姓名 from student inner join score on student.`学号` = score.`学号` inner join course on score.`课程号` = course.`课程号` inner join teacher on course.`教师号` = teacher.`教师号`;

output

姓名 课程名称 成绩 教师姓名
张三 语文     88   马老师
张三 数学     90   王老师
张三 英语     95 
李四 数学     66   王老师
李四 英语     75 
小红 语文     80   马老师
.......

我们同时可以在后面添加一些筛选条件，例如我们想要找出语文的成绩在85分以上的同学学号以及姓名，就可以这么做

select student.`学号`, 姓名, 成绩 from student inner join score on student.`学号` = score.`学号` inner join course on score.`课程号` = course.`课程号` inner join teacher on course.`教师号` = teacher.`教师号` where 课程名称 = '语文' and 成绩 > 85;

output

学号 姓名 成绩
0001 张三 88

点这里👇关注我，记得标星哦~