mysql分组函数、组函数、聚合函数、统计函数
直接po代码,代码中有注释
#二、分组函数
/*
功能:用作统计使用,又称为聚合函数或统计函数或组函数
分类:
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点:
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段
*/
#
#1、分组函数的简单 的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary), COUNT(*), COUNT('aaa'), COUNT("bbbb"), COUNT(666) 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;
#2、参数支持哪些类型
#使用SUM()函数和AVG()函数去处理字符型和日期型,尽管语法不会报错,但是没什么意义
SELECT SUM(last_name), AVG(last_name) FROM employees;
SELECT SUM(hiredate), AVG(hiredate) FROM employees;
#max、min、count可以处理任何类型
SELECT MAX(last_name), MIN(last_name) FROM employees;
SELECT MAX(hiredate), MIN(hiredate) FROM employees;
SELECT MAX(salary), MIN(salary) FROM employees;
SELECT COUNT(salary), COUNT(last_name), COUNT(*), COUNT(hiredate) FROM employees;
#COUNT()函数不会把null算进去,即忽略null
SELECT COUNT(salary), COUNT(commission_pct), COUNT(last_name), COUNT(*), COUNT(hiredate) FROM employees;
SELECT * FROM employees WHERE commission_pct IS NOT NULL;
SELECT * FROM employees WHERE commission_pct IS NULL;
#3、是否忽略null (记住:分组函数都忽略null值,即不会把null算进去)
SELECT
COUNT(commission_pct),
SUM(commission_pct),
AVG(commission_pct),
SUM(commission_pct) / 35,
SUM(commission_pct) / 107
FROM
employees ;
#
SELECT MAX(commission_pct), MIN(commission_pct) FROM employees;
#
SELECT COUNT(commission_pct) FROM employees;
SELECT commission_pct FROM employees;
#
#4、和distinct搭配使用
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees;
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;
SELECT SUM(DISTINCT commission_pct), SUM(commission_pct) FROM employees;
SELECT COUNT(DISTINCT commission_pct), COUNT(commission_pct) FROM employees;
#
SELECT
MAX(DISTINCT commission_pct) AS 结果,
MAX(commission_pct) AS 结果,
MIN(commission_pct) AS 结果,
MIN(DISTINCT commission_pct) AS 结果,
AVG(commission_pct) AS 结果,
AVG(DISTINCT commission_pct) AS 结果,
SUM(DISTINCT commission_pct) / COUNT(DISTINCT commission_pct) AS 结果
FROM
employees ;
#
SELECT
MAX(DISTINCT salary),
MAX(salary),
MIN(salary),
MIN(DISTINCT salary),
AVG(salary),
AVG(DISTINCT salary),
SUM(DISTINCT salary) / COUNT(DISTINCT salary) AS 结果
FROM
employees ;
#别名可以取一样的,语法不会报错,但是不建议取一样的别名,容易混淆
SELECT
salary AS a,
hiredate AS a
FROM
employees
/*
下面这条sql语句,语法报错,原因是,取了一样的别名,在使用别名排序时,搞不清该别名到
底指的是谁(即模棱两可;含糊的,不明确的,引起歧义的,有两种或多种意思的)
*/
#select salary as a,hiredate as a from employees order by a;
#5、count函数的详细介绍
#统计个数,推荐使用COUNT(*),不推荐使用COUNT(字段名)
SELECT
COUNT(salary),
COUNT(commission_pct),
#统计个数最好用*星号,不要用具体的某个字段,因为某字段中可能有null
COUNT(*) AS 个数,
COUNT(NULL),
COUNT('aaa'),
COUNT(6666),
COUNT("bbb")
FROM
employees ;
/*
效率:
MYISAM存储引擎下 ,COUNT(*)的效率高
INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些
*/
#
SELECT COUNT(salary), COUNT(commission_pct) FROM employees;
#
SELECT COUNT(*) FROM employees;
#
SELECT COUNT(1), COUNT(66), COUNT("bbb"), COUNT('aaa') FROM employees;
#
SELECT * FROM employees;
#6、和分组函数一同查询的字段有限制
#如下这条sql语句语法不会报错,但是没有任何意义
SELECT AVG(salary), employee_id, last_name FROM employees;
#测试下ROUND()函数
SELECT
ROUND(689.257, 2),
ROUND('689.257', 2),
ROUND('hello', 2),
ROUND(
'江西省赣州市于都县',
4
) ;
#1.查询公司员工工资的最大值,最小值,平均值,总和
SELECT
MAX(salary),
MIN(salary),
AVG(salary),
ROUND(AVG(salary), 2),
SUM(salary)
FROM
employees ;
#DATEDIFF()函数
SELECT DATEDIFF('2018-12-18', '2018-12-01'), DATEDIFF('2018-12-18', '2018-12-2');
SELECT DATEDIFF('18-3-8', '2018-3-01'), DATEDIFF('2018-2-12', '18-2-02');
SELECT DATEDIFF('2018-3-01', '18-3-8'), DATEDIFF('18-2-02', '2018-2-12');
#算一下自己活了多少天了
SELECT DATEDIFF(NOW(), '1995-08-27');
#2.查询员工表中的最大入职时间和最小入职时间的相差天数 (DIFFRENCE),使用DATEDIFF()函数
SELECT
MIN(hiredate) 最早入职,
MAX(hiredate) 最晚入职,
DATEDIFF(MAX(hiredate), MIN(hiredate)) AS 相差天数
FROM
employees ;
#查看下employees表结构,查看下employees表中的hiredate字段的类型
DESC employees;
#也可以使用如下方式
SELECT
MIN(hiredate) 最早入职,
MAX(hiredate) 最晚入职,
(UNIX_TIMESTAMP(MAX(hiredate)) - UNIX_TIMESTAMP(MIN(hiredate)))/(60*60*24) 相差天数,
(UNIX_TIMESTAMP(MAX(hiredate)) - UNIX_TIMESTAMP(MIN(hiredate)))/(60*60*24*365) 相差年
FROM
employees ;
#3.查询部门编号为90的员工个数
#
SELECT
COUNT(*)
FROM
employees
WHERE department_id = 90 ;
#用单引号把90包起来,效果一样
SELECT
COUNT(*)
FROM
employees
WHERE department_id = '90' ;
#用双引号把90包起来,效果一样
SELECT
COUNT(*)
FROM
employees
WHERE department_id = "90" ;