分组查询 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 跳过条数,请求条数;
子查询(嵌套查询)
- 查询工资大于2号部门平均工资的员工信息
- 查询员工高于程序员最高工资的员工信息
select * from emp where sal>(select max(sal) from emp where job='程序员');
- 查询工资最高的员工信息
select name,max(sal) from emp group by name;
- 查询和孙悟空相同工作的员工信息
select job from emp where name='孙悟空';
select * from emp where job=(select job from emp where name='孙悟空') and name<>'孙悟空';
- 查询那最低工资员工的同事们的信息,(同事指同一部门)
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;
- 查询有领导并且和销售有关的员工姓名,工作,部门名,部门地点
关联查询之外连接
- 等值连接和内连接查询到的都是两张表的交集数据;
- 外连接查询的是一张表 全部和另一张表的交集数据
- 格式 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;