【DataBase】MySQL 12 SQL函数 聚合函数

视频参考自：P53 - P58

https://www.bilibili.com/video/BV1xW411u7ax

 

什么是分组函数？

 用来统计使用，其具体的实现都是基于对字段的值聚合再处理

又称为【聚合函数】或者【组函数】

 

聚合函数的种类：

-　1、sum 求和

-　2、avg 平均

-　3、max 最大

-　4、min 最小

-　5、count 计数记录数

 

单个使用

-- 演示使用
SELECT SUM(`salary`) FROM `employees`;
SELECT AVG(`salary`) FROM `employees`;
SELECT MAX(`salary`) FROM `employees`;
SELECT MIN(`salary`) FROM `employees`;
SELECT COUNT(`salary`) FROM `employees`;

 

组合使用

SELECT 
SUM(`salary`) 工资总和,
AVG(`salary`) 平均工资,
MAX(`salary`) 最高工资,
MIN(`salary`) 最低工资,
COUNT(`salary`) 人员个数
FROM `employees`;

 

参数可支持的类型？

-　1、sum & avg 用于处理数值型字段

-　2、max & min & count 可处理任何类型

-　3、所有函数都会忽略NULL值，如果不希望忽略，使用IFNULL对字段处理

-　4、搭配distinct可实现去重运算

-- 返回0，0字符不支持求和&平均
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`;
-- 时间本质是毫秒值，当然也可以求值【MAX表示最新时间，MIN表示最早时间】
SELECT MAX(`hiredate`),MIN(`hiredate`) FROM `employees`;

-- 计数记录数，要注意的是！不会记录NULL值
SELECT COUNT(`last_name`) FROM `employees`;

-- 要实现NULL值计数，我们要对字段进行IFNULL处理，设置缺省值
SELECT COUNT(IFNULL(`commission_pct`,0)) FROM `employees`;

 

去重运算

-- 去重运算
SELECT SUM(DISTINCT `salary`) 去重总工资,SUM(`salary`) 非去重总工资 FROM `employees`;
SELECT COUNT(DISTINCT `salary`) 不同工资的人员个数,COUNT(`salary`) 所有人员个数 FROM `employees`;

 

COUNT函数的具体介绍

-- COUNT函数的详细介绍
SELECT COUNT(`salary`) FROM `employees`; -- 对单个字段计数记录数
SELECT COUNT(*) FROM `employees`;    -- 对所有字段统计记录数[任意一个字段的记录为null，此函数都会忽略计数]
SELECT COUNT(1) FROM `employees`;    -- [在返回的查询表中添加了一列参数的字段，并统计记录数]

-- 效率问题
-- MyISAM 引擎 COUNT(*) 最高效
-- INNODB 引擎 COUNT(*) COUNT(1)的效率差不多 比COUNT(字段)效率高【因为字段需要判断NULL值】

-- 推荐使用 * 作为COUNT函数的参数计数

 

不要尝试分组函数和字段一并查询

-- 因为分组函数会限制字段的记录结果
SELECT AVG(`salary`),`last_name` FROM employees;

-- 8版本的MySQL不再允许这样的SQL语法，直接提示语法错误
/*
错误代码： 1140
In aggregated query without GROUP BY, 
expression #2 of SELECT list contains nonaggregated column 'myemployees.employees.last_name'; 
this is incompatible with sql_mode=only_full_group_by
*/

 

案例练习

-- 案例练习
-- 查询公司员工工资最大值最小值平均值求和
SELECT 
 MAX(`salary`) 最高工资,
 MIN(`salary`) 最低工资,
 AVG(`salary`) 平均工资,
 SUM(`salary`) 实发合计
FROM
 `employees`;
 
-- 员工表种的最大入职时间和最小入职时间的相差天数
SELECT 
MIN(`hiredate`) 最早入职时间,
MAX(`hiredate`) 最晚入职时间,
DATEDIFF(MAX(`hiredate`) , MIN(`hiredate`)) 相差天数 -- DATEDIFF()函数，求两个时间差
FROM
`employees`;

SELECT DATEDIFF(NOW(),'1997-09-06');

-- 查询部门编号为90的员工个数
-- 幻想可以直接这么写... SELECT COUNT(`department_id`=90) from `employees`;
SELECT COUNT(`department_id`)
FROM `employees`
WHERE `department_id` = 90;