mysql打卡学习5.1分组函数

心已赠人 2023-03-12 09:22 18阅读 0赞

## 二，分组函数 ##

/*
    功能：作统计使用，又称为聚合函数或者统计函数或者组函数
    分类
    sum,avg,max,min,count
    特点
    	1.sum avg:数值型
    	2.max,min,count 可以处理任何类型
    	3.以上分组函数都忽略null值，可以和distinct搭配进行去重
    	4.count函数
    		count(*):统计行数
    	5.和分组函数一同查询的字段要求是group by后的字段
    */

#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),ROUND(AVG(salary),2),MIN(salary) FROM employees;

#2.参数支持哪些类型
    
    SELECT MAX(last_name),MIN(last_name) FROM employees;
    SELECT MAX(hiredate),MIN(hiredate) FROM employees;
    
    SELECT COUNT(commission_pct) FROM employees;

#3.忽略null
    SELECT COUNT(commission_pct)  FROM employees;
    SELECT SUM(commission_pct),AVG(commission_pct) ,SUM(commission_pct)/35 FROM employees;
    SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;

#4.和distinct搭配
    SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
    SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

#5.count函数的详细介绍
    SELECT COUNT(salary) FROM emplyees;
    #统计行数
    SELECT COUNT(*) FROM employees;
    SELECT COUNT(1) FROM employees;
    #效率
    #myisam存储引擎下,count(*)效率最高
    #innodb存储引擎下,count(1)==count(*)

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

### 练习 ###

#1.公司员工工资的最大最小平均值总和
    SELECT MAX(salary),MIN(salary),AVG(salary),ROUND(COUNT(salary),2) FROM employees;
    
    #2.最大入职时间和最小入职时间的相差天数
    SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;
    
    #3.编号90的员工个数
    SELECT COUNT(*) FROM employees
    WHERE department_id=90;