干货总结!太全面了,图解SQL面试题:经典30题!

数据库
今天我们来讲讲SQL方面的知识点,特别是对于绝大多数的数据分析师而言,SQL也是经常会被面试问到的内容。

 [[437763]]

今天我们来讲讲SQL方面的知识点,特别是对于绝大多数的数据分析师而言,SQL也是经常会被面试问到的内容。

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

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

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

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

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

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

  1. insert into student(学号,姓名,出生日期,性别)   
  2. values('0001' , '张三' , '1991-05-01' , '男');  
  3. insert into student(学号,姓名,出生日期,性别)   
  4. values('0002' , '李四' , '1990-04-21' , '男'); 

output

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

简单查询

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

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

output 

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

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

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

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

output 

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

分组汇总查询

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

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

output 

  1. 学生人数  
  2.       6 

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

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

output 

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

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

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

output 

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

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

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

output 

  1. 不同性别的学生数量  
  2.               4  
  3.               3 

带有条件的分组查询

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

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

output 

  1. 学号 平均成绩  
  2. 0001     91  
  3. 0003 81.66666666666667  
  4. 0004 81.33333333333333  
  5. 0005     91.5  
  6. 0006     91 

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

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

output 

  1. 课程号 平均成绩  
  2. 0001     84  
  3. 0002     80.25  
  4. 0003 84.16666666666667  
  5. 0004     76.5  
  6. 0005     98 

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

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

output 

  1. 课程号 平均成绩  
  2. 0005      98  
  3. 0003 84.16666666666667  
  4. 0001     84  
  5. 0002     80.25  
  6. 0004     76.5 

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

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

output 

  1. 课程号 平均成绩 最高分  
  2. 0004 76.5       78  
  3. 0002 80.25       90  
  4. 0001 84         88  
  5. 0003 84.1666667 97  
  6. 0005 98         99 

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

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

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

output 

  1. 学号 选课的数量  
  2. 0001       3  
  3. 0003       3  
  4. 0004       3 

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

  1. select 学号 from score where 成绩 > 80 

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

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

output 

  1. 学号 平均成绩  
  2. 0001     91  
  3. 0003     85  
  4. 0004     97  
  5. 0005     91.5 
  6. 0006     91 

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

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

output 

  1. 学号 平均成绩  
  2. 0001     91  
  3. 0005     91.5  
  4. 0006     91 

汇总排序

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

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

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

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

output 

  1. 学号 总分  
  2. 0007 75  
  3. 0002 141  
  4. 0006 182  
  5. 0005 183  
  6. 0004 244  
  7. 0003 245  
  8. 0001 273 

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

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

output 

  1. 学号 总分  
  2. 0004 244  
  3. 0003 245  
  4. 0001 273 

嵌套式查询

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

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

output 

  1. 学号 最低分  
  2. 0001   88  
  3. 0005   84  
  4. 0006   85 

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

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

output 

  1. 学号 姓名  
  2. 0001 张三  
  3. 0005 小张  
  4. 0006 小美 

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

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

output 

  1. 学号 选课数量  
  2. 0002     2  
  3. 0005     2  
  4. 0006     2  
  5. 0007     1 

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

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

output 

  1. 学号 姓名  
  2. 0002 李四  
  3. 0005 小张  
  4. 0006 小美  
  5. 0007 小丽 

日期函数

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

  1. select curdate(); 

output 

  1. curdate()  
  2. 2021-11-16 

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

  1. select now(); 

output 

  1. now()  
  2. 2021-11-16 22:37:41 

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

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

output 

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

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

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

跨表查询

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

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

output 

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

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

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

output 

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

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

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

output 

  1. 姓名 平均分  
  2. 张三 91.0000  
  3. 李四 70.5000  
  4. 小红 81.6667 

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

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

output 

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

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

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

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

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

output 

  1. 姓名 课程名称 教师姓名  
  2. 张三 语文 马老师  
  3. 张三 数学 王老师  
  4. 张三 英语   
  5. 李四 数学 王老师  
  6. 李四 英语   
  7. 小红 语文 马老师  
  8. 小红 数学 王老师  
  9. 小红 英语   
  10. 小王 英语   
  11. 小王 物理 张老师  
  12. ........ 

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

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

output 

  1. 姓名 课程名称 成绩 教师姓名  
  2. 张三 语文     88   马老师  
  3. 张三 数学     90   王老师  
  4. 张三 英语     95   
  5. 李四 数学     66   王老师  
  6. 李四 英语     75   
  7. 小红 语文     80   马老师  
  8. ....... 

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

  1. 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 

  1. 学号 姓名 成绩  
  2. 0001 张三 88 

关于sql当中表格数据的连接,尤其是多张表格的连接。

 

责任编辑:庞桂玉 来源: 菜鸟学Python
相关推荐

2016-03-03 10:07:39

ios内存管理面试总结

2011-03-24 13:27:37

SQL

2014-07-28 14:00:40

linux面试题

2021-02-23 12:43:39

Redis面试题缓存

2020-09-16 11:50:18

MySQL数据库面试

2021-10-26 11:45:22

Vue面试前端

2020-08-06 10:45:30

JavaSpring面试题

2016-05-05 17:45:43

Spring面试题答案

2023-09-21 14:55:24

Web 开发TypeScript

2020-08-31 12:20:07

Python面试题代码

2020-06-24 09:55:17

Web面试前端

2015-08-19 09:35:49

Java main面试题

2010-04-15 11:54:55

面试

2021-06-27 22:48:28

Redis数据库内存

2020-06-04 14:40:40

面试题Vue前端

2013-11-01 09:27:48

Twitter技术面试

2020-11-16 07:22:32

腾讯多线程

2009-08-28 09:29:02

2023-11-13 07:37:36

JS面试题线程

2011-07-18 15:08:19

SQL存储过程
点赞
收藏

51CTO技术栈公众号