ORACLE的子查询学习（主要是FROM \HAVING\WHERE）

-- 多表查询性能差，子查询性能好
复杂查询=限定查询+多表查询+统计查询+子查询 ，也是在笔试中出现较多的部分
有些公司面试逻辑分析能力。

查询公司之中工资最低的雇员的完整信息。

 

首先知道最低工资是多少？
　　select min(sal) from emp

 

这样就是子查询：
　　select * from emp where sal =(select min(sal) from emp)

 

子查询可以返回的数据类型，一共分为四种：
单行多列，单行单列，多行多列，多行单列

老师会告诉你，哪块要用它。

一个查询语句内部可以定义多个子查询

 

WHERE 子句之中，可以返回单行单列、单行多列等

查询基本工资高于公司平均工资的信息：（简单）

select * from emp where sal>(
select avg(sal) from emp)

select * from emp
where 
job=(select job from emp where ename = 'ALLEN') 
and 
sal>(select avg(sal) from emp)

select * from emp 
where ( job ,sal)=(select job,sal from emp where ename ='ALLEN');


select * from emp
where ( job ,sal)=(select job,sal from emp where empno='7566');

 

查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息

 

单行多列的where：

select * from emp where
(job,to_char(hiredate,'yyyy'))=(
select job, to_char(hiredate,'yyyy')from emp where ename='ALLEN' )

 

返回多行单列数据（比较麻烦）
--int、any、all

查询出与每个部门中最低工资相同的全部雇员信息
首先，统计出部门最低工资
select * from emp
where sal in (
select min(sal) from emp group by deptno)

查询出不与每个部门中最低工资相同的全部雇员信息
select * from emp
where sal not in (
select min(sal) from emp group by deptno)

说到这，in 与not in 的操作，有一点注意，关于空的问题，如果返回有空，那么不会影响，如果在not in之中子查询
返回不会用空。

select * from emp 
where empno not in (
select empno from emp 
)

select * from emp
where sal <>any (
select min(sal) from emp group by deptno)

select * from emp
where sal <any (
select min(sal) from emp group by deptno)

 

在oracle之中，some的功能和any是相同的，这个后来添加的。

select * from emp
where sal <some (
select min(sal) from emp group by deptno)

ALL操作符

select * from emp
where sal <>all (
select min(sal) from emp group by deptno)

select * from emp
where sal =all (
select min(sal) from emp group by deptno)
这是没有数据返回的。

 

select * from emp
where sal <all (
select min(sal) from emp group by deptno)

select * from emp
where sal >all (
select min(sal) from emp group by deptno)

全部显示出来了，这是为什么？

 

总结：
IN 操作符 in ， not in
ANY
=ANY 等同与in
>ANY 表示子查询之中返回的最小值要大
<ANY 表示子查询之中返回最大的值要小
ALL
>ALL 比子查询之中返回的最大值要大
<ALL 比子查询之中返回的最小值要小

where中除了这几种判断之外

select * from emp
where exists(
select * from emp
)
一旦有结果返回，就全部回来了。

exists求反，如下：

select * from emp
where not exists(
select * from emp where empno=9999
)

having 子句中使用子查询
这个一定是结合group by 一起使用，分组后数据再次过滤，而且与where子句不同。

第一范例：
select deptno ,count(empno),avg(sal) avg
from emp
group by deptno
having avg(sal)>(
select avg(sal) from emp
)

 

查询出每个部门平均工资最高的部门名称及平均工资
第一步，求部门最高的平均工资
select max(avg(sal))
from emp
group by deptno

 

第二部，多表查询，用having筛选出部门最高的平均工资

select d.dname, round(avg(e.sal))
from emp e, dept d
where e.deptno=d.deptno
group by d.dname
having avg(sal)=(
select max(avg(sal))
from emp
group by deptno
)

 

FROM 子查询（子查询的精髓所在）：
基本的实例 ：第一点查询出每个部门的编号、名称、位置、部门人数、平均工资

select d.deptno,d.dname,d.loc,avg(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno,d.dname,d.loc

select deptno dno,count(empno) count,round(avg(sal)) sal
from emp
group by deptno

 

结果出来：

select d.deptno,d.dname,d.loc,dno,count,sal
from dept d,(
select deptno dno,count(empno) count,round(avg(sal)) sal
from emp
group by deptno
) teap
where d.deptno=teap.dno(+)

 

但是现在发现一个问题，两种操作可以实现同样的结果，那么使用那一种呢？
数据扩大100倍，
实现一：多字段分组实现
当dept和emp表关联的时候，一定会出现笛卡儿积
实现二：子查询

使用子查询实际上是解决多表查询的带来的性能问题。

查询出所有部门 “sales”（销售部）工作的员工的编号，姓名，基本工资，奖金，职位，雇佣日期，部门的最高和最低工资
确定所需要的表格：
dept表：销售部，最终是根据销售本门的统计
emp表：编号，姓名，基本工资，奖金，职位，雇佣日期
emp表：统计最高和最低工资

select deptno from dept where dname='sales'

select e.empno,e.ename,e.sal,e.comm,e.hiredate,max,min
from emp e,(
select deptno dno ,max(sal) max,min(sal) min
from emp
group by deptno 
) teap
where deptno=(
select deptno from dept where dname='SALES'
) and
e.deptno=dno

 

子查询是一个分析的过程。答案只是参考的。

 

查询出所有薪金高于公司平均薪金的员工编号，姓名，基本工资，职位，雇员日期，所在部门名称，位置，上级领导名称，工资的等级，部门人数，平均工资，平均服务年限

确定所需要的表：
　　emp表：员工编号、姓名、基本工资、职位、雇员日期
　　dept表：部门名称、位置　　

　　emp表：上级领导名称
　　salgrade表：工资等级
　　emp表：统计出部门人数，平均工资，平均服务年限

确定已知的关联字段：
　　雇员和部门：
　　雇员和领导：
　　雇员和工资等级：

步骤一：统计平均工资
select avg(sal)
from emp

步骤二：高于次平均工资的员工编号
select e.empno,
from emp e
where e.sal>(
select avg(sal)
from emp
)

步骤三：
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc
from emp e ,dept d
where e.sal>(
select avg(sal)
from emp
)
and e.deptno=d.deptno

步骤四：
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,m.ename
from emp e ,dept d,emp m
where e.sal>(
select avg(sal)
from emp
)
and e.deptno=d.deptno
and e.empno=m.empno(+)

步骤五：
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,m.ename,s.grade
from emp e ,dept d,emp m,salgrade s
where e.sal>(
select avg(sal)
from emp
)
and e.deptno=d.deptno
and e.empno=m.empno(+)
and e.sal between s.losal and s.hisal

步骤六：
select e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,m.ename,s.grade
from emp e ,dept d,emp m, salgrade s,(
select deptno dno , count(empno) count, 
round(avg(sal),2) avg,
round(avg(months_between(sysdate,hiredate)/12),2) avgyear
from emp
group by deptno
) teap
where e.sal>(
select avg(sal)
from emp
)
and e.deptno=d.deptno
and e.empno=m.empno(+)
and e.sal between s.losal and s.hisal
and e.deptno=dno

 

 

查出薪金比ALLEN 或CLARK多的所有员工的编号姓名、基本工资、部门名称、领导姓名、部门人数
取得所需要的数据表
emp表：员工编号、姓名，基本工资
dept表：部门名称
emp表：领导姓名
emp表：数量统计

步骤一：
找到ALLEN或CHECK工资
select sal
from emp
where ename in ('ALLEN','CHECK')

--ename='ALLEN' or ename='CHECK'
步骤二：
select e.empno,e.ename,e.sal
from emp e
where sal>any(
select sal
from emp
where ename in ('ALLEN','CHECK')
)and e.ename not in ('ALLEN','CHECK')

步骤三、四：
select e.empno,e.ename,e.sal,d.dname,m.ename
from emp e ,dept d,emp m
where e.sal>any(
select sal
from emp
where ename in ('ALLEN','CHECK')
)
and e.ename not in ('ALLEN','CHECK')
and e.deptno=d.deptno
and e.mgr=m.empno(+);

步骤五：
select e.empno,e.ename,e.sal,d.dname,m.ename,count
from emp e ,dept d,emp m,(
select deptno dno,count(empno) count from emp 
group by deptno
) teap
where e.sal>any(
select sal
from emp
where ename in ('ALLEN','CHECK')
)
and e.ename not in ('ALLEN','CHECK')
and e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.deptno=dno;

 

最后有一个操作范例：
列出公司各个部门的 经理姓名、薪金、部门名称、部门人数、部门的平均工资
确定所需要的数据表
emp 表：经理姓名、薪金
dept 表：部门名称、
emp表：部门的人数、平均工资
关联字段：
雇员和部门：

步骤一，找到每个部门姓名，薪金
select ename,sal
from emp where job='MANAGER'

步骤二，添加部门名称
select ename,sal,d.dname
from emp e,dept d
where job='MANAGER'
and e.deptno=d.deptno

步骤三，部门人数，平均工资
select ename,sal,d.dname,count,avg
from emp e,dept d,(
select deptno dno, count(deptno) count ,round(avg(sal),2) avg
from emp 
group by deptno
)
where job='MANAGER'
and e.deptno=d.deptno
and e.deptno=dno

 

select 子句查询
这个东西出现的意义不是特别大，简单说一下：
在实际项目WHERE \HAVING\FROM 使用的比较多。

查询出公司每个部门的编号、名称、人数统计，平均薪金

select d.deptno,d.dname,d.loc,
(
select count(empno)
from emp
where deptno=d.deptno
group by deptno
) count ,
(
select avg(empno)
from emp
where deptno=d.deptno
group by deptno
) avg
from dept d

 

这样的查询可以用其他方法给实现。只要懂这个东西就可以了。

与子查询有关的WITH，在行列转制上有用的。

WITH是比较有用的东西

临时表实际上就是上一个结果，
这个with的使用，

查询每个部门的编号、部门名称、统计部门、平均薪金

with e as(
select deptno dno,round(avg(sal),2) avg, count(sal) count
from emp
group by deptno
)
select d.deptno,d.loc,count,avg
from e ,dept d
where e.dno(+)=d.deptno

 

查询每个部门工资最高的雇员编号、姓名、职位、雇佣日期、工资、部门编号、部门名称、显示的结果按照部门的编号来排序

with e as(
select deptno dno, max(sal) max
from emp
group by deptno
)
select em.empno,em.ename,em.hiredate ,em.sal,d.deptno,d.dname
from e, emp em ,dept d
where e.dno=em.deptno and e.max=em.sal and e.dno=em.deptno
order by em.deptno

 

用了一个with创造了一个临时表，可供查询使用

下一节继续写。