分组查询 group by

  • 将某个字段的相同值分为一组,对其他字段的数据进行聚合函数的统计,称为分组查询
  • 单字段分组查询
  • 1.查询每个部门的平均工资
    select dept_id,avg(sal) from emp group by dept_id;
  • 2.查询每个职位的最高工资
    select job, max(sal) from emp group by job;
  • 3.查询每个部门的人数
    select dept_id,count(*) from emp group by dept_id;
  • 4.查询每个职位 中工资大于1000的人数
    select job,count(*) from emp where sal>1000 group by job ;
  • 5.查询每个领导手下的人数
    select mgr,count(*) from emp where mgr is not null and mgr <>0 group by mgr;
  • 练习
select job,avg(sal) from emp where avg(sal)>2000 group by job;

select job,avg(sal)  from emp group by job having avg(sal) >2000;

select job, count(*) c from emp group by job having c > 1;

select dept_id,sum(sal) s from where mgr is not null emp group by dept_id having s>5000;

select dept_id,avg(sal) where sal between 1000 and 3000 group by dept_id having>=2000;

select * from emp where sal>(select avg(sal) from emp where dept_id=2);
  • 多字段分组查询
  • 1.查询每个部门每个主管手下的人数;
    select job,,count(*) where mgr is null group by dept_id,
  • having
  • 后跟聚合函数的条件;
  • 不能单独使用,常和group by组队出现,跟在其后面
    select job count(*) c from emp group by job having c>1000;

字段查询顺序

  • select 查询字段信息 from 表名 where 普通字段条件 group by
    分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数;

子查询(嵌套查询)

  1. 查询工资大于2号部门平均工资的员工信息
  2. 查询员工高于程序员最高工资的员工信息
select  * from emp where sal>(select  max(sal) from emp where job='程序员');
  1. 查询工资最高的员工信息
select  name,max(sal) from emp group by name;
  1. 查询和孙悟空相同工作的员工信息
select job from emp where name='孙悟空';

select  * from emp where job=(select job from emp where name='孙悟空') and name<>'孙悟空';
  1. 查询那最低工资员工的同事们的信息,(同事指同一部门)
select min(sal) from emp ;

select  dept_id from emp where sal=(select min(sal) from emp)

select * from where dept_id=(select dept_id from sal=(select min(sal) from emp));

关联查询之等值连接

  • 格式: select * from A,B where 关联关系
  • 1.查询每个员工 姓名和对应的部门名字
select e.name,d.name from emp e,dept d where e.dept_id=d.id and sal  > 2000;

关联查询之内连接

  • 格式: select * from A join B on 关联关系
  • 1.查询工资高于2000的员工姓名和对应的部门名
select e.name,d.name from emp e join dept d on e.dept_id =d.id where sal>2000;

 select e.name ,d.name from emp e join dept d on  e.dept_id=d.id where sal >2000;
  1. 查询有领导并且和销售有关的员工姓名,工作,部门名,部门地点

关联查询之外连接

  • 等值连接和内连接查询到的都是两张表的交集数据;
  • 外连接查询的是一张表 全部和另一张表的交集数据
  • 格式 select * from A left join B on 关联关系
1.查询所有员工姓名和对应的部门名

select e.name,d.name  from emp e left join dept d on e.dept_id=d.id;

select e.name,d.name from emp  emp e left join dept d on e.dept_id=id;

select e.name,d.name from emp e  left  join  dept d on e.dept_id=d.id;

2.查询所有部门的名称/地点和对应的员工姓名和工资

select d.name,d.loc,e.name,e.sal from emp e left join dept d on e.dept_id =d.id;