今天我们来讲讲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
- 学号 总分
- 0007 75
- 0002 141
- 0006 182
- 0005 183
- 0004 244
- 0003 245
- 0001 273
然后我们再添加一个限制条件,例如挑选出总分在200分以上的数据,可以通过having关键词来执行
- select 学号, sum(成绩) as 总分 from score group by 学号 having sum(成绩) > 200 order by sum(成绩);
output
- 学号 总分
- 0004 244
- 0003 245
- 0001 273
嵌套式查询
有时候我们需要写多层的SQL查询语句,一层的查询有时候显然不够用,例如我们想要查询出所有课程的成绩都是高于80分的学生学号与姓名,我们一层一层来分析,首先我们筛选出所有课程都高于80分学生的学号与成绩
- select 学号, min(成绩) as 最低分 from score group by 学号 having min(成绩) > 80;
output
- 学号 最低分
- 0001 88
- 0005 84
- 0006 85
再得到了学号以及成绩了之后,我们将学号这一列提取出来,去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
- 学号 选课数量
- 0002 2
- 0005 2
- 0006 2
- 0007 1
当然我们其实只要“学号”这一列,然后我们在此基础之上再进行查询
- 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
关于sql当中表格数据的连接,尤其是多张表格的连接。