-- 多表查询性能差,子查询性能好
复杂查询=限定查询+多表查询+统计查询+子查询 ,也是在笔试中出现较多的部分
有些公司面试逻辑分析能力。
查询公司之中工资最低的雇员的完整信息。
首先知道最低工资是多少?
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创造了一个临时表,可供查询使用
下一节继续写。