MySQL数据库基础：分组函数

【MySQL数据库基础：4.分组函数】 

作者：zhenyi

专栏：mysql数据库1

简介：MySQL基础专栏文章，适合MySQL基础阶段的学习与阅读。

文章所用文件，可与作者联系获取！如果对您有帮助，点个关注吧，持续更新中，谢谢支持。如果有问题或错误，欢迎指出私信与我联系，谢谢。

目录

分组函数

1.简单的使用

2.参数支持哪些类型

3.是否忽略null

4.和distinct（去重）搭配

5.count函数的介绍

6.和分组函数统一查询的字段有限制

---

分组函数

用作统计使用，又称为聚合函数或统计函数或组函数

分类：

sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数

特点：

1.sum，avg-一般用于处理数值型

max，min，count可以处理任何类型

2.以上分组函数都忽略null

3.可以和distinct搭配实现去重的运算

4.count函数的单独介绍

1.简单的使用

1.1：SELECT SUM(salary) FROM employees;

        SELECT AVG(salary) FROM employees;

        SELECT MIN(salary) FROM employees;

        SELECT MAX(salary) FROM employees;

        SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) FROM employees;

SELECT AVG(salary) FROM employees;

SELECT MIN(salary) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT COUNT(salary) FROM employees;

查询结果如下：

 

1.2：SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary)个数

FROM employees;

SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary)个数

FROM employees;

查询结果如下：

 

2.参数支持哪些类型

2.1：SELECT SUM(last_name) ,AVG(last_name) FROM employees;

        SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;

        SELECT MAX(last_name),MIN(last_name) FROM employees;

        SELECT MAX(hiredate),MIN(hiredate)FROM employees;

3.是否忽略null

SELECT     SUM(comission_put),AVG(comission_put),SUM(comission_put)/35,SUM(comission_put)/107 FROM employees;

SELECT SUM(comission_put) ,AVG(comission_put) ,
SUM(comission_put)/35,SUM(comission_put)/107 
FROM employees;

查询结果如下：

 

4.和distinct（去重）搭配

SELECT SUM(DISABLE salary),SUM(salary) FROM employees;

SELECT COUNT(DISABLE salary),COUNT(salary) FROM employees;

SELECT	*	FROM	employees;
SELECT SUM(DISABLE salary),SUM(salary) FROM employees;

查询结果如下：

 

5.count函数的介绍

SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;

SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;

查询结果如下：

 

效率：

myisam存储引擎下，count(*)的效率高

innodb存储引擎下，count(*）和count（1）的效率差不多，比count（字段）要高一些

6.和分组函数统一查询的字段有限制

SELECT AVG(salary),employees_id FROM employees;

SELECT	*	FROM	employees;
SELECT AVG(salary),employees_id FROM employees;

查询结果如下：

 

分组函数所有代码：

SELECT	*	FROM	employees;

SELECT SUM(salary) FROM employees;

SELECT AVG(salary) FROM employees;

SELECT MIN(salary) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT COUNT(salary) FROM employees;


SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary)个数

FROM employees;

SELECT  SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary)个数

FROM employees;


SELECT SUM(last_name) ,AVG(last_name) FROM employees;

        SELECT SUM(hiredate) ,AVG(hiredate) FROM employees;

        SELECT MAX(last_name),MIN(last_name) FROM employees;

        SELECT MAX(hiredate),MIN(hiredate)FROM employees;


SELECT SUM(comission_put) ,AVG(comission_put) ,SUM(comission_put)/35,SUM(comission_put)/107 FROM employees;

SELECT	*	FROM	employees;
SELECT SUM(DISABLE salary),SUM(salary) FROM employees;

SELECT COUNT(DISABLE salary),COUNT(salary) FROM employees;



SELECT COUNT(salary) FROM employees;

SELECT COUNT(*) FROM employees;
SELECT	*	FROM	employees;
SELECT AVG(salary),employees_id FROM employees;

如果对您有帮助，点个关注吧，持续更新中，谢谢支持。如果有问题或错误，欢迎指出与我联系，谢谢。