分组查询GROUP BY

SELECT MAX(employees.salary),employees.job_id FROM employees GROUP BY employees.job_id;

SELECT COUNT(*),departments.location_id FROM departments GROUP BY location_id;

SELECT salary,department_id FROM employees WHERE email LIKE '%a%';

SELECT AVG(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

SELECT COUNT(*),employees.department_id FROM employees GROUP BY employees.department_id;

SELECT COUNT(*),employees.department_id FROM employees GROUP BY employees.department_id HAVING COUNT(*) > 2;

SELECT MAX(employees.salary),job_id FROM employees WHERE employees.commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(employees.salary) > 12000;

SELECT MIN(employees.salary),manager_id FROM employees WHERE manager_id>120 GROUP BY manager_id HAVING MIN(employees.salary)>5000;

函数

sum 求和

max 最大值

min 最小值

avg 平均值

count 计数

特点：

1、以上五个分组函数都忽略null值，除了count(*)

2、sum和avg一般用于处理数值型

    max、min、count可以处理任何数据类型

3、都可以搭配distinct使用，用于统计去重后的结果

4、count的参数可以支持：

    字段、*、常量值，一般放1

    建议使用 count(*)