![2e0958ed99a2c0128284487f3e0ca835.png](https://img-blog.csdnimg.cn/img_convert/2e0958ed99a2c0128284487f3e0ca835.png)
今天学习汇总分析,一共六个知识点
![6bfbc92db9824af28df280b7498a6d94.png](https://img-blog.csdnimg.cn/img_convert/6bfbc92db9824af28df280b7498a6d94.png)
一,汇总函数
函数就是一个功能,输入函数参数,它会向你返回值
- count函数 求某列的行数
例:查询教师表中教师姓名的行数
select count(教师姓名) from teacher;
![27168596ebed47eb99bf1e1a195ee386.png](https://img-blog.csdnimg.cn/img_convert/27168596ebed47eb99bf1e1a195ee386.png)
注意,这里计算的是红色区域的
![5d1640dc3342997939285c1bbce2277b.png](https://img-blog.csdnimg.cn/img_convert/5d1640dc3342997939285c1bbce2277b.png)
如果select的是*号,则会计算所有的行,计算结果会变成4
2. sum函数 对某列数据求和 (只对数值类型的列进行计算)
查询成绩表中的成绩总和
select sum(成绩) from score;
![825e6f78c149b06227877d8baabac915.png](https://img-blog.csdnimg.cn/img_convert/825e6f78c149b06227877d8baabac915.png)
3. avg函数 求某列数据的平均值 (只对数值类型的列进行计算)
查询成绩表的学生平均成绩
select avg(成绩) from score;
![61e2960a0cd3449d34cea44e10b2a2c8.png](https://img-blog.csdnimg.cn/img_convert/61e2960a0cd3449d34cea44e10b2a2c8.png)
4. max函数 求某列数据的最大值, min函数 求某列数据的最小值
查询成绩表的学生最大成绩以及最小成绩
select max(成绩),min(成绩) from score;
![38860f32015f76913701acc0e5aeeeeb.png](https://img-blog.csdnimg.cn/img_convert/38860f32015f76913701acc0e5aeeeeb.png)
tips: 学生表中学生姓名有重复,想要查询有多少个学生 这个时候就要用到distinct
select count(distinct姓名) from student;
![11234cea166c5a82efe647a5981aa533.png](https://img-blog.csdnimg.cn/img_convert/11234cea166c5a82efe647a5981aa533.png)
练习:
- 查询课程编号为‘0002’的总成绩
select sum(成绩) from score
where 课程号 = '0002';
![ca0d707c6ad4e963f8eb1dc1c563918c.png](https://img-blog.csdnimg.cn/img_convert/ca0d707c6ad4e963f8eb1dc1c563918c.png)
2. 查询选了课程的学生人数
/*这里用count计算有多少个学生号即可,但是一个学生可能会选择多个课程,
就会出现重复的学生号,所以要用到distinct 去除重复 */
select count(distinct 学号) as 学生人数 from score;
![8587ba995d652b3a54552284e91bb3b1.png](https://img-blog.csdnimg.cn/img_convert/8587ba995d652b3a54552284e91bb3b1.png)
二,分组
在sql中使用 group by对数据按某个条件进行分组,经常与上面的汇总 条件进行搭配使用
比如 查询男生就多少人,女生有多少人 就对男生女生进行分组
select 性别, count(*) as 学生人数 from student group by 性别;
![683b99594cc51c25273babc9f1e72515.png](https://img-blog.csdnimg.cn/img_convert/683b99594cc51c25273babc9f1e72515.png)
运行顺序:
![158300e49845729484170987a1cbb658.png](https://img-blog.csdnimg.cn/img_convert/158300e49845729484170987a1cbb658.png)
练习:
- 查询各科成绩最高分和最低分
select max(成绩) as 最高分, min(成绩) as 最低分 from score
group by 课程号;
-- 锁定成绩表之后,找到最高分最低分,再根据课程号分组
![dee73bc3bdc866e8a0ccebfae8878a53.png](https://img-blog.csdnimg.cn/img_convert/dee73bc3bdc866e8a0ccebfae8878a53.png)
2. 查询每门课程被选修的学生数
select 课程号,count(学生号) as 学生人数from score
group by 课程号;
-- 锁定成绩表之后,找到学号,再根据课程号分组
![4b734b1dea125fb4cbe99a6feb507b2a.png](https://img-blog.csdnimg.cn/img_convert/4b734b1dea125fb4cbe99a6feb507b2a.png)
3.查询男生女生人数
select 性别, count(*) as 学生人数 from student group by 性别;
![e110993176298781d591cc48de682745.png](https://img-blog.csdnimg.cn/img_convert/e110993176298781d591cc48de682745.png)
三,对分组结果指定条件
用having指定分组以后的条件
例如,男生女生组中哪一个组是一个人以上
select 性别, count(*) as 学生人数 from student
group by 性别 having count(*) > 1;
![6f50192386df106ddfb2ae89b9dace24.png](https://img-blog.csdnimg.cn/img_convert/6f50192386df106ddfb2ae89b9dace24.png)
面试题练习:
- 查询平均成绩大于60份学生的学号和平均成绩
select 学号,avg(成绩) as 平均成绩 from score
group by 学号
having avg(成绩) > 60;
-- 从成绩标准找到学号以及成绩,将其按学号分类号后,应用函数 抽取大于60分的学生
![c2e392e6946c47eb55bad98615d231ca.png](https://img-blog.csdnimg.cn/img_convert/c2e392e6946c47eb55bad98615d231ca.png)
2. 查询至少选修两门课程的学生学号
select 学号, count(课程号) as 选修课 from score
group by 学号
having count(课程号) >= 2;
-- 从成绩标准找到学号以及课程号,将其按学号分类号后,应用函数 抽取课程数量大于2的学生
![b99b40218b2c3c3ea7ba6e6dcb0a469a.png](https://img-blog.csdnimg.cn/img_convert/b99b40218b2c3c3ea7ba6e6dcb0a469a.png)
3. 查询同名同姓学生名单并统计同名人数
select 姓名, count(*) as 人数 from student
group by 姓名
having count(*) >= 2;
/* 解题思路首先是 确定表student,然后根据题目所说的统计同名人数,那么就是按照姓名分组,
其次统计运用的就是count函数,同名的人数一定是两个及以上,
所以最后 用having提取计数两个以上的数据 */
![7b9a78b1daab34585123c5601f15c6d4.png](https://img-blog.csdnimg.cn/img_convert/7b9a78b1daab34585123c5601f15c6d4.png)
四, 用SQL解决业务问题
要解决问题,先把要求翻译成大白话,再想解决问题的思路是什么,最后写sql语句
例如:如何计算各科平均成绩
先翻译成大白话,1. 各科成绩就是每门成绩,那么就是按照科目分组 group by 课程号
2. 平均成绩,就是需要函数avg
然后将查询语句的各个关键字写出来
select [查询结果] from [哪张表]
where [查询条件]
group by [分组]
having [对分组结果指定条件]
再与之对应
那么对应下来的结果就是:
表:score
查询条件:没有
分组 :课程号
对分组指定条件:没有
查询结果:课程号,avg(成绩)
select 课程,avg(成绩) from score
group by 课程号;
练习题:计算每门课程的平均成绩并且平均成绩大于80分
解题思路:
表:score
查询条件:没有
分组 :课程号
对分组指定条件:平均成绩大于80分
查询结果:课程号,avg(成绩)
select 课程号,avg(成绩) from score
group by 课程号
having avg(成绩) >= 80;
![bdc776f431525dcfb8448e1deae66b19.png](https://img-blog.csdnimg.cn/img_convert/bdc776f431525dcfb8448e1deae66b19.png)
五,对查询结果排序
order by是对查询结果排序
升序 ASC
降序 DESC
这个是放在最后的,所以在之前的公式后面再加一个order by 具体如下
select [查询结果]
from [哪张表]
where [查询条件]
group by [分组]
having [对分组结果指定条件]
order by[对查询结果进行排序]
练习题1:计算每门课程的平均成绩并且平均成绩大于80分 平均成绩相同时结果按降序排列
首先按照公式一步一步分析
表:score
查询条件:没有
分组 :课程号
对分组指定条件:平均成绩大于80分
结果排序 :降序 desc
select 课程号,avg(成绩) as 平均成绩 from score
group by 课程号
having avg(成绩) >= 80
order by 平均成绩 desc;;
![383b10de38608105ada62a45bec7b034.png](https://img-blog.csdnimg.cn/img_convert/383b10de38608105ada62a45bec7b034.png)
运行顺序:
![6a2d227176b26b185ee75170ce7f687d.png](https://img-blog.csdnimg.cn/img_convert/6a2d227176b26b185ee75170ce7f687d.png)
指定多个排序列名
例子2:要求升级升序,课程号降序
select * from score
order by 成绩 asc, 课程号 desc;
![c608439230ed2888b603616e4b7c5ee0.png](https://img-blog.csdnimg.cn/img_convert/c608439230ed2888b603616e4b7c5ee0.png)
空值的排序 可用于查看空值
如查找教师表的空值
select * from teacher order by 教师姓名;
如图所示,空值会在最前面显示
![8be7ee4fc26d147ae9c0b02f3e3836a2.png](https://img-blog.csdnimg.cn/img_convert/8be7ee4fc26d147ae9c0b02f3e3836a2.png)
取出指定行 limit
去几行就写limit几
例如:查询成绩表中前两行的信息
select * from score
limit 2;
![7b73e1dd1abf65fd153686261644993d.png](https://img-blog.csdnimg.cn/img_convert/7b73e1dd1abf65fd153686261644993d.png)
运行顺序:
![ae978feb55d5e3327ccf4807b7115b79.png](https://img-blog.csdnimg.cn/img_convert/ae978feb55d5e3327ccf4807b7115b79.png)
练习题2:查询不及格的课程并按课程号从大到小排列
select 课程号 from score
where 成绩 < 60
order by 课程号 desc;
![286607f787f61ecf0a3d024f167890d8.png](https://img-blog.csdnimg.cn/img_convert/286607f787f61ecf0a3d024f167890d8.png)
六,如何看懂报错信息
常见错误:在where中使用聚合函数
正确写法:
select 性别, count(*) as 学生人数 from student
group by 性别 having count(*) > 1;
错误写法:
select 性别, count(*) as 学生人数
from student
where count(*) > 1
group by 性别 ;
![7a76d04948ddffd0055522a3d0334307.png](https://img-blog.csdnimg.cn/img_convert/7a76d04948ddffd0055522a3d0334307.png)
如图所示,红色部分Err 表示报错,黄色部分显示的错误的信息,可根据英文知道发生了什么错误,加以改正
tips:数值类型与字符串类型的排序顺序以及算最大值时 是不一样的,所以一定要注意确认修改数据类型。