Mysql 分组函数和查询

#分组函数

/*     

函数，类似java中的方法

 

叫什么？方法名或者函数名

干什么？函数功能

 

分组函数，又称为统计函数或聚合函数

 

length('john')  //传入一个值，返回一个值，一对一，像这种函数，就不是分组函数，叫做单行函数，分组函数表示这个函数给出一组值,最后返回一个值

max(salary) // 分组函数，给它一组值，比如说给它一列值

 

 

分组函数的分类

max():最大值

min():最小值

sum():和

avg():平均值

count():计算非空的个数

这些是通用的，sqlserver、oracle、mysql 都是一样的

 

 

特点：

1、sum、avg只支持数值型，count、max、min支持任何类型

2、这几个分组函数，都忽略null值

3、count()分组函数，支持以下写法

count(*) 建议使用

count(1)

表示
查询结果集的行数

*/

 

#一、查询分组函数

SELECT MAX(salary) 最大值 FROM
employees;

SELECT MIN(salary) 最小值 FROM
employees;

SELECT SUM(salary) 和 FROM
employees;

SELECT AVG(salary) 平均值 FROM
employees;

SELECT COUNT(salary) 个数 FROM
employees;

SELECT MAX(salary) 最大值,SUM(salary)
和 FROM employees;

 

#二、关于sum 和avg

/*

1.只支持数值型

2.sum和avg都忽略null值

*/

SELECT AVG(last_name) FROM employees;

SELECT SUM(last_name) FROM employees;

 

SELECT * FROM employees;

SELECT
SUM(commission_pct)/107,AVG(commission_pct) FROM employees;

 

 

#三、关于max和min

/*

1.支持任何类型

2.max和min都忽略null值

*/

SELECT MAX(last_name),MIN(last_name) FROM
employees;  //判断类型

SELECT
MAX(commission_pct),MIN(commission_pct) FROM employees;  //判断是否忽略null

 

 

#四、关于count

SELECT COUNT(commission_pct) FROM
employees;    //计算非空的值，忽略null、

//查询员工表占了几个部门

SELECT COUNT(DISTINCT department_id) FROM
employees;



 

#查询结果集中的行数

/*

count(1)的性能没有count（*）高

因为count(*)，sql服务器对其进行了优化，所以效率高

 

*/

SELECT COUNT(*) FROM employees;  //查看有多少条数据 

//计算满足条件的行数

SELECT COUNT(*) FROM employees WHERE
department_id>30;

SELECT COUNT(1) FROM employees;  //和count(*)的效果是一样的，但是效率不一样

 

 

#问题：查询员工表最大工资和员工号?

#答：不能！！！！

# 不允许和分组函数，一起执行任意字段，因为employee_id这个字段的值是多个，分组函数的值是一个，一和多无法形成规则的表格

SELECT MAX(salary),employee_id FROM
employees;

 

 

#分组查询

#和分组函数一起搭配使用的查询，我们叫做分组查询，换句话说，分组查询一般都会用到分组函数

 

#引入：查询各部门的最高工资

SELECT MAX(salary) FROM employees WHERE
department_id IN(20,90,30);

SELECT MAX(salary),department_id FROM
employees GROUP BY  department_id;

 

/*

 

语法：

select 分组函数 别名,分组后的字段

from 表

【where 分组前筛选】

group by 分组的字段

【having 分组后筛选】

【order by 排序的字段】

从上到下依次顺序，不能颠倒顺序

 

分组查询的特点：

1、按多个字段分组，字段间用逗号隔开，没有顺序要求

2、可以和分组函数一起查询的字段，只能是分组后的字段，不能是任意字段

3、分组筛选

                      针对的表           
位置                       使用的关键字

分组前筛选    原始表                
group by的前面       where

分组后筛选    分组后的结果集    group by的后面     having

顺序：where——group by——having        

4、having和group by支持别名

*/

#一、普通的分组查询

#01 案例：查询各部门的最高工资和部门号

SELECT MAX(salary),department_id FROM
employees GROUP BY department_id;

 

#二、按多个字段分组

#案例：查询每个工种、每个部门的平均工资

SELECT AVG(salary),job_id,department_id

FROM employees

GROUP BY job_id,department_id;

 

#三、分组查询+筛选having

/*

筛选条件分两类：

分组前筛选：筛选的条件针对的是原始表

分组后筛选：筛选的条件针对的是分组后的结果集

                  针对的表        位置                 使用的关键字

分组前筛选    原始表             group by的前面     where

分组后筛选    分组后的结果集    group by的后面  having

where——group by——having

 

*/

#案例1：查询有奖金的，每个部门的最高奖金率

SELECT MAX(commission_pct) 最高奖金率,department_id

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY department_id;

 

 

#案例2：查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号

SELECT department_id,MIN(salary) 最低工资

FROM employees

WHERE last_name LIKE '%a%'  

GROUP BY department_id

HAVING MIN(salary)>3000;

 

 

#案例3：查询电话以“110”开头的，工种号包含字符'T'的,每个工种的平均工资>5000的工种号和平均工资

SELECT job_id,AVG(salary)

FROM employees

WHERE phone_number LIKE '110%' AND job_id
LIKE '%T%'

GROUP BY job_id

HAVING AVG(salary)>5000;

 

 

#案例4：查询每个地区的部门个数，求个数大于2的部门个数和地区号

SELECT COUNT(*) 个数,location_id

FROM departments

GROUP BY location_id

HAVING COUNT(*)>2;

 

 

#四、having子句支持别名

#案例：查询每个地区的部门个数，求个数大于2的部门个数和地区号

SELECT COUNT(*) 个数,location_id


FROM departments

GROUP BY location_id

HAVING 个数>2;

 

 

#五、排序 order by 支持别名

#案例：查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号,按照最低工资降序排序

SELECT department_id,MIN(salary) 最低工资

FROM employees

WHERE last_name LIKE '%a%'  

GROUP BY department_id

HAVING MIN(salary)>3000

ORDER BY 最低工资 DESC;