oracle11g学习笔记(七)

Oracle的复杂查询
在实际应用中经常需要执行复杂的数据统计,经常需要现实多张表的数据
所以经常要用到数据分组函数如 max(), min(),avg(),sum(),count()等
一、分组函数
?如何显示所有员工中最高工资和最低工资
select max(sal),min(sal) from emp;
?如何查询最高,最低工资的人是谁
select ename,sal from emp where sal=
(select max(sal) from emp);
select ename,sal from emp where sal=
(select min(sal) from emp);
这里利用了子查询
?请显示工资最高的员工的名字,工作岗位
select ename,job from emp where sal=
(select max(sal) from emp);
?请显示工资高于平均工资的员工信息
select * from emp where sal>
(select avg(sal) from emp)
select avg(sal) from emp ;
如果这里不是*号而是ename,sal等字段与分组函数那么在语句的最后要加上
group by ename,sal..(与select后的字段一致)

二、group by 和having 子句
group by 用于对查询结果分组统计
having子句用于限制分组显示结果
？如何显示每个部门的平均工资和最高工资
select avg(sal),max(sal),deptno from emp group by deptno;
分组字段依据必须出现在查询结果中，否则结果可读性太差
？如何显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),job,deptno from emp group by job,deptno;
? 显示平均工资低于2000的部门和它的平均工资
select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;

三、对分组函数的总结
1.分组函数只能出现在选择列表,having,group by,order by子句中
2.如果在select语句中同时包含有group by,having, order by,那么顺序为group by,having,order by
3.在选择列中如果有列,表达式,和分组函数,那么这些列表达式必须有一个出现在group by 子句中,否则出错
如:select deptno,avg(sal),max(sal),max(sal) from emp group by deptno having avg(sal)>2000;
这里的deptno就一定要出现在group by中

四、多表查询
基于两个或两个以上的表或是视图的查询
查单表满足不了要求如部门和员工的关系
?显示雇员名,雇员工资及所在部门的名称
select a.ename,a.sal,b.dname
 from emp a,dept b
 where a.deptno=b.deptno;
如果不加where子句,就会产生笛卡尔集
所谓笛卡尔集,就是不加筛选,将所有的都查询出来
两张表关联,至少需要一个条件排除笛卡尔集
三张表关联,至少需要二个条件排除笛卡尔集
以此类推:多表查询中,判断条件至少是表的个数-1
?如何显示部门号为10的部门名,员工名和工资
select b.dname,a.ename,a.sal
 from emp a,dept b
 where a.deptno=b.deptno
   and b.deptno=10;
?显示各个员工的姓名,工资及工资的级别
select a.ename,a.sal,b.grade
 from emp a,salgrade b
 where a.sal between losal and hisal;
这里用到了between ... and 子句.
表示在losal和hisal之间(很好理解)
?显示雇员名,雇员工资及所在部门的名字,并部门排序
 select a.ename,a.sal,b.dname,b.deptno
   from emp a,dept b
  where a.deptno=b.deptno
  order by b.deptno;

五、自连接
自连接:指在同一张表内的连接查询
?显示某个员工的上级领导的姓名
select worker.ename,boss.ename
  from emp worker,emp boss
where worker.mgr=boss.empno
   and worker.ename='FORD';
根据FORD的名字找到ford的mgr编号再根据这个编号找到boss的empno,最后显示出来

六、子查询
子查询:指嵌入在其他sql语句中的select语句,也叫做嵌套查询
(1)单行子查询:是指只返回一行数据的子查询语句
?如何显示与SMITH同一部门的所有员工
分两步:
1.查出SMITH所在部门
2.根据部门查出所有员工
1.select deptno from emp where ename='SMITH';
2.select ename from emp where deptno=(select deptno from emp where ename='SMITH');
(2)多行子查询:返回多行数据的子查询
?如何查询和部门10的工作相同的雇员的名字,岗位,工资,部门号
1.首先查出部门10的工作种类
 select job from emp where deptno=10;
 发现有重复结果.所以在job前加上distinct
 select distinct job from emp where deptno=10;
2.根据工作的种类查询
 select ename,job,sal,deptno
   from emp 
 where job in
  (select distinct job from emp where deptno=10);
注意这里job之后用的是"in"而非"=".

(3)all操作符
?如何显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号
select ename,sal,deptno from emp where sal>all
(select sal from emp where deptno=30);
可以使用max方法
select ename,sal,deptno from emp where sal>
(select max(sal) from emp where deptno=30);
max方法的效率较高,原因是使用all操作符时,要和子查询所得结果逐一比较
而使用max只需和sal中的最大值比较,减少了比较的次数,缩短了时间.
数据量较大是会比较明显,数据量较小基本看不出来

(4)any操作符
?如何显示工资比部门30的任意一个员工的工资高的员工的姓名,工资和部门号
select ename,sal,deptno from emp where sal>any
(select sal from emp where deptno=30);
可以使用min方法
select ename,sal,deptno from emp where sal>
(select min(sal) from emp where deptno=30);
原理同上
(5)多列子查询
多列子查询:指子查询返回多列数据
?如何查询与SMITH部门和岗位完全相同的所有雇员
1.查询出SMITH的部门号,岗位
select deptno,job from emp where ename='SMITH';
2.显示结果
select * from emp
 where (deptno,job)=
  (select deptno,job
     from emp
    where ename='SMITH'
  );

(6)在from子句中使用子查询
?如何显示高于自己部门的平均工资员工信息
1.查询各个部门的平均工资和部门号
select avg(sal) avgsal,deptno from emp group by deptno;
这里group by deptno是必须要有的.用来对分组结果进行统计
2.把上面的查询看作是一张子表
select a.ename,a.sal,a.deptno,b.avgsal
  from emp a,
  (select avg(sal) avgsal,deptno from emp group by deptno) b
 where a.deptno=b.deptno and a.sal>b.avgsal;
这个比较难想到,将这两个表做关联查询
当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌视图
挡在from子句中使用子查询时,必须为子查询指定别名