MYSQL聚合函数与分组查询

目录

插入查询

 聚合查询

1.count()：统计所有的行

2.sum(列名)：求和

3.avg(列名)：平均值运算

 4.max(列名)，min(列名)

分组查询

group by方法

having方法

在说聚合函数和联合函数前，先补充 插入查询 部分的知识。

插入查询

语法：

insert into 表名 [列，列] select from [列名] from 表名......；

 前一个表名是 目标表，即要把数据插入到这张表中，后面的是要从旧表中查询出来的列。

当然，后面还可以添加一些限定条件，像是where、order by等。

例如，下面有一张 stu表，创建、插入以及查询过程如下：

 create table stu(
    id bigint primary key auto_increment,
    -name varchar(50),
    -sn bigint
    );

insert into stu values (1,'张三',10001),(2,'李四',10002),(3,'王五',10003),(4,'赵六',5);

select * from stu;

现在有一个stu1的空表想要插入stu表中的id和name列，

create table stu1(
    id bigint primary key auto_increment,
    name varchar(50)
    );

 select * from stu1;

那么这时候就可以用到插入查询。 

insert into stu1(id,name) select id,name from stu;

select * from stu1;

 还有其他方式可以把stu中的数据复制到stu1中

• 一条一条的重新插入一遍
• 把原来的数据导出来，再把表名改一下，再改入到目标表中

 聚合查询

聚合查询是MYSQL中内置的一些函数。

之前所学到的表达式查询，是对一行记录中的列和列之间进行运算，有需要的可以看：MYSQL 表的增删改查（上）_mysql中表的修改记录这么查-CSDN博客

聚合查询本质上是针对数据表中的行和行进行过运算的。

有以下内容：

1.count()：统计所有的行

接下来所有的关于聚合查询部分的内容都是以这张表为例。

如果需要统计有多少个人，那么就可以用到count方法。

select count(*) from exam;

select count(id) from exam;

 其中 * 表示统计表中的行数，也可以选择指定某一个列，例如id。

如果被统计的行中 有NULL存在，NULL会被统计进去嘛？正好上面的english列中有NULL。

select count(english) from exam;

能看到和上面id列查询的列的内容比较来说，少了一行。

所以可得结论：如果说列中有NULL值，则不会被统计在内。 

2.sum(列名)：求和

把查询结果中所有行中的指定列进行相加，列的数据类型必须是数值型，不能是其他类型。

还是以上面exam表为例，如果我想要计算所有学生的数学成绩的和，那么便可以通过sum方法来进行。

select sum(math) from exam;

还是关于NULL问题，如果列中有NULL，sum方法得出的数据是什么样的？

关于NULL，我们知道：NULL与任何值运行结果都是NULL。我们可以尝试用sum方法运行一下。

select sum(english) from exam;

从最终的结果能看到，得到的结果并不是NULL，而是一个数值，不难想到，在sum求和中，NULL值不参与运算。

如果此时我们用name（非数值类型）进行计算，会发生什么？

select sum(name) from exam where id < 4;

show warnings;

为什么只有两行警告，因为id < 4 的学生只有两位。

3.avg(列名)：平均值运算

对所有行的指定列进行平均值运算。

例如，需要求出数学成绩的平均值，就能用到avg方法。

select avg(math) from exam;

 或者，用来计算三门学科总分的平均分。

select avg(chinese + math + english) from exam;

select avg(chinese + math + english) 总分 from exam;

直接写或者使用别名进行都是可以的。

 4.max(列名)，min(列名)

找到所有行指定列的最大值，与最小值。

例如，找到语文成绩的最大值和数学成绩最小值。

select max(chinese),min(math) from exam;

select max(chinese) 语文最高分,min(math) 数学最低分 from exam;

分组查询

group by方法

在select中使用group by 子句可以对指定列进行分组查询。

接下来会使用 emp表 来对分组查询进行说明。

 关于计算不同角色（role）的平均工资可以用到分组查询。

其中要分的列是role列，并计算其中的平均工资。

select role,avg(salary) from emp group by role;

如果想要输出的结果是升序排列的，group by后面也可以跟order by方法。

select role,avg(salary) from emp group by role order by avg(salary) asc;

 如果我们想要对分组之后的结果进行过滤，比如找出平均工资小于1000的角色，这时候改怎么做呢？

having方法

group by方法进行分组后，需要对分组条件再进行条件过滤时，不能使用where方法，而需要用having方法。

注意：

• where 是对表中每一行的真实数据进行过滤的
• having 是对 group by之后，计算出来的结果进行过滤

换句话说，where是对我们设计出来的表的数据进行过滤，而having是对临时表进行过滤的。

这样我们就可以解决上面这个问题了：找到平均工资小于1000的角色

select role,avg(salary) 平均工资 from emp group by role having 平均工资 < 1000;

 如果需要查询到每个角色的最高工资、最低工资和平均工资，我们可以通过下面的语句来实现。

select role,max(salary) 最高工资,min(salary) 最低工资,avg(salary) 平均工资 
from  emp group by role;

今天的分享就到这里，感谢支持。