7.分组查询(GROUP BY)
7.1 什么是分组查询
在实际的应用中,可能有这样的需求,需要先进行分组,然后对每一组的数据进行操作。
这个时候我们需要使用分组查询,怎么进行分组查询呢?分组查询的语句:
select
…
from
…
group by
…注意:在一条select语句当中,如果有group by语句的话,select后面只能跟:参加分组的字段,以及分组函数。其它的一律不能跟。
7.2 为什么分组函数不能直接使用在where后面?
select
…
from
…
where
…
group by
…
order by
…
执行顺序为:1.from 2.where3.group by 4.select 5.order by
为什么分组函数不能直接使用在where后面?
因为分组函数在使用的时候必须先分组之后才能使用。where执行的时候,还没有分组。所以where后面不能出现分组函数。
7.3 使用案例
#找出每个工作岗位的工资和
select
sum(sal) '工资总和',job '工作'
from
emp
group by
job
order by
job desc;
#找出每个部门的最高薪资
select
max(sal) '最高薪资',deptno
from
emp
group by
deptno;
#找出每个部门,不同工作岗位的最高薪资
select
deptno,job,max(sal)
from
emp
group by
deptno,job
order by
deptno,job;
7.4 having语句
使用having可以对分完组之后的数据进一步过滤。having不能单独使用,having不能代替where,having必须和group by联合使用。
having在where不能使用的情况下使用
测试:
#找出每个部门最高薪资,要求显示最高薪资大于3000的?
#1.使用having语句,但是执行效率过低
select
deptno,max(sal)
from
emp
group by
deptno
having
max(sal) > 3000;
#2.先找出工资大于3000
select
deptno,max(sal)
from
emp
where
sal > 3000
group by
deptno;
7.5 单表查询的顺序
select
…
from
…
where
…
group by
…
having
…
order by
…
执行顺序?1.from 2.where 3.group by 4. having 5. select 6.order by
8.distinct 关键字 去除重复记录
distinct:把查询结果去除重复记录
注意:
- distinct只能出现在所有字段的最前方
- distinct出现在多个字段前,表示多个字段联合起来然后再去重
#查找所有的工作岗位
select distinct job from emp;
#这样写会报错,distinct只能出现在所有字段的最前方
#select ename,distinct job from emp;
#distinct出现在多个字段前,表示多个字段联合起来然后再去重
select distinct job,deptno from emp;
#统计工作岗位的数量
select count(distinct job) from emp;
9.连接查询
9.1 概述
什么是连接查询?
连接查询简单的来说就是,多张表联合起来查询数据。
语法格式:
select
…
from
…
join
…
on
…
连接查询的分类:
- 根据语法的年代分类:
- SQL92:1992年的时候出现的语法
- SQL99:1999年的时候出现的语法
- 根据表连接方式分类:
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全连接
- 内连接
注意:当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是两张表条数的乘积,这种现象被称为:笛卡尔积现象。
9.2 内连接
内连接的两张表或多张表没有主次关系。
内连接之等值连接
条件是一个等值关系
#查询每个员工所在部门的名称
#SQL92写法:表的连接条件和筛选条件都在where后面
select
e.ename,d.dname
from
emp e,dept d
where
e.DEPTNO = d.DEPTNO
order by
d.DNAME;
#SQL99写法:结构更清晰,如果需要筛选则继续添加where
select
e.ename,d.dname
from
emp e
#内连接的inner可以省略inner
join
dept d
on
e.DEPTNO = d.DEPTNO
order by
d.DNAME;
内连接之非等值连接
条件不是一个等值关系
#内连接之非等值连接(条件不是一个等值关系)
#找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
内连接之自连接
相当于一张表看作为两张表
#内连接之自连接
#查询员工的上级领导,要求显示员工名和对应的领导名
#也就是说对应mgr的编号和员工编号empno
#相当于一张表看作为两张表
select * from emp;
select
e.ename '员工',e2.ename '领导'
from
emp e
join
emp e2
on
e.mgr = e2.empno;
9.3 外连接
在外连接当中,两张表连接,产生了主次关系。
带有right的是右外连接,又叫做右连接。
带有left的是左外连接,又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。
外连接的查询结果条数一定是 >= 内连接的查询结果条数
右外连接
right代表:表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。
#查询员工对应的部门名称
#outer是可以省略的,带着可读性强。
select
e.ename,d.dname
from
emp e
right outer join
dept d
on e.deptno = d.deptno;
#15条数据,编号为40的部门没有对应员工,默认为null
左外连接
同理,left表示将join关键字左边的表看为主表
select
e.ename,d.dname
from
emp e
left outer join
dept d
on e.deptno = d.deptno;#14条数据
#查询每个员工的上级领导,要求显示所有员工的名字和领导名
#相比较内连接,多了king的领导为null 的数据
select
a.ename,b.ename
from
emp a
left join
emp b
on
a.mgr = b.empno;
多张表的连接查询
语法:
select
…
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件注意:一条SQL中内连接和外连接可以混合。都可以出现!
#找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级
select
e.ename '员工名',d.dname '部门名',e.sal '工资',s.grade '薪资等级'
from
emp e
left join
dept d
on
e.deptno = d.deptno
left join
salgrade s
on
e.sal between s.losal and s.hisal
order by
s.grade;
10.子查询
10.1 概述
子查询:select语句中嵌套select语句,被嵌套的select语句称为子查询。
可以出现在的地方:
select
…(select).
from
…(select).
where
…(select).
10.2 where子句中的子查询
#找出比最低工资高的员工姓名
#where子句的查询
select
ename,sal
from
emp
where
sal > (select min(sal) from emp);
10.3 from子句的子查询
#from子句的查询
#找出每个岗位的平均工资的薪资等级
select * from salgrade;
select * from dept;
select * from emp;
#将这个查询结果,当做一张;临时表
select job,avg(sal) from emp group by job;
select
s.grade,t.*
from
salgrade s
join
(select job,avg(sal