1.数据库(分组查询）

1.select max(salary) mx_sal, min(salary) mi_sal,round(avg(salary),2) ag_sal,sum(salary) sm_sal from employees;

查询最大值，最小值，平均值（保留2位），总和；

2.select  datediff(max(hiredate),min(hiredate)) diffrence from employees;

查询两个时间的时间之差，然后取个别名；

3.select count(*) 个数 from employees where department_id = 90;

查看部门员工部门编号是90的员工个数；

4.分组查询；

分组数据：group by子句语法；

可以使用group by子句将表中的数据分成若干组，where一定要放在from后面；

分组前的查询：

select 分组函数，列（要求出现在group by的后面）from 表【where 筛选条件】group by 分组的列表【order by 子句】

注意：查询列表必须特殊，要求是分组函数和group by后出现的字段

1.查询每个工种的最高工资：

select max(salary),job_id from employees group by job_id;

2.查询每个位置上的部门个数；

select count(*) department_id from departments group by department_id;

3.查询邮箱中包含a字符的，每个部门的平均工资；

select avg(salary) 平均工资 ,department_id from employees where email like '%a%' group by department_id 

4.查询有奖金的每个领导手下员工的最高工资；

select max(salary) , manager_id from employees where commision_pct is not null 

group by  manager_id;

5.查询哪个部门的员工个数>2:

分组后的查询：

先查询部门所有的人数；

select count(*) ,department_id from eployees group by department_id;

再去查人数>2的；

select count(*) ,department_id from eployees group by department_id having count(*)>2;

6.查询每个工种有奖金的员工的最高工资>12000的的工种编号和最高工资；

首先查最高工资

select max(salary),job_id from employees where commission_pct is not no null  group by job_id having max(salary)>12000;

7.select min(salary) ,manager_id from employees where manager_id >102 group by manager_id having min(salary)>5000

8.select count(*) c,length(last_name) len_name from employees group by len_name  having c>5;

总结：

分组前筛选： 数据源   位置 group by 子句的前面  where

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

分组函数做条件，肯定是放在having语句中；

group by和having后面都支持别名，但是where不支持放别名；

9.多个字段分组：

select avg(salary) departmen_id ,job_id from employees group by departmen_id ,job_id;

10.

select avg(salary) departmen_id ,job_id from employees where departmmen_id is not null group by departmen_id ,job_id having avg(salary)>10000 order by avg(salary) desc;

select max(salary)