分组查询

select * from 表
where 条件
group by 条件
order by  desc、asc

        #查询每个工种最高工资
            SELECT
            MAX(salary),job_id
            FROM
            employees
            GROUP BY
            job_id
            #查询每个部门的人数
            SELECT
            COUNT(1),location_id
            FROM departments
            GROUP BY location_id

#分组查询加where,因为涉及的字段都是原始表可以直接使用
            select avg(salary) as 平均工资,job_id 部门 FROM employees
            WHERE email LIKE ‘%a%‘
          GROUP BY job_id

    #按照lasatName长度分组查询每一组的人数，求出大于5的
            select count(*) as 个数,LENGTH(last_name) cd FROM
            employees GROUP BY cd HAVING 个数 > 5

            #求部门员工的平均工资，总工资 最小工资 和 最大工资
            select job_id,sum(salary) 工资求和,min(salary) 工资最小值,avg(salary) 平均值,max(salary) 工资最大值
            FROM employees
            GROUP BY job_id


    #求某领导手下员工的最少工资，并且求出工资至少等于6000     并且该部门不能为null
                select MIN(salary) FROM employees
                WHERE manager_id is NOT null
                GROUP BY manager_id
                HAVING MIN(salary) >= 6000