oracle之DQL

一、单表查询

语法：select * from table where 条件 group by 分组 having 过滤分组 order by 排序

--查询平均工资低于2000的部门的最大工资和平均工资
select deptno, max(sal), avg(sal)
  from emp
 group by deptno
having avg(sal) < 2000
order by deptno asc;

 模糊查询，_表示占位一个字节，%填充剩下的位数

--查询员工姓名以A开头，并且第三个字母为L的员工信息
select * from emp where ename like 'A_L%';

分页查询

--查询员工表中第三条到第五条数据
select *
  from (select emp.*, rownum as rn from emp where rownum <= 5)
 where rn > 2;

二、嵌套子查询

单行子查询

子查询返回的值是单行单列的

--查询薪资高于平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp);

 多列子查询

多列子查询中的条件与查询结果必须依次对应，否则会报错

--查询与smith部门和岗位完全相同的员工
select *
  from emp
 where (deptno, job) = (select deptno, job from emp where ename = 'SMITH');

多行子查询

多行子查询就是嵌套在其他sql语句中返回多行数据，常用的条件是in,not in,any,all等，any和all必须和比较运算符一起使用

 >all = >max　|　>any = >min　|　<all = <min　|　<any = <max

--查询比部门30所有员工的工资都要高的员工信息
select * from emp where sal > all (select sal from emp where deptno = 30);

--查询比部门10中员工的工资高的员工信息
select * from emp where sal > any (select sal from emp where deptno = 10);

--查询部门10和部门20的员工信息
select * from emp where deptno in (10,20);

in和exists，在9i时代，exists适合用于字表查询量大的情况，in适用于父表查询量大的时候，在9i之后，ORACLE优化器有个查询转换器，很多SQL虽然写法不同，但是ORACLE优化器会根据既定规则进行查询重写，重写为优化器觉得效率最高的SQL，所以可能SQL写法不同，但是执行计划却是完全一样的。

相关子查询

相关子查询依赖于外部的条件，不能单独执行，而非相关子查询可以单独执行

--查询在dept表中存在的部门员工信息
select ename,sal,deptno from emp where exists (select 1 from dept 
where dept.deptno = emp.deptno) 

三、多表查询

内连接

内连接也叫连接，是最早的一种连接。还可以被称为普通连接或者自然连接，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

--内连接的两种写法，只返回符合条件的结果
select * from emp inner join dept on emp.deptno = dept.deptno;
select * from emp, dept where emp.deptno = dept.deptno;

内连接只要写上连接条件就不会产生笛卡尔积

外连接

左外连接、右外连接、满外连接

左外连接以左边的表的为基础，右表分别与左表中的数据进行匹配，右表中的数据可能有空值；

而右外连接正好相反，左表中可能有空值；

满外连接左右两边都可能有空值。

--左外连接的两种写法：
select * from emp, dept where emp.deptno = dept.deptno(+);
select * from emp left outer join dept on emp.deptno = dept.deptno;

 

--右外连接的两种写法
select * from emp ,dept where emp.deptno(+) = dept.deptno; 
select * from emp right join dept on emp.deptno = dept.deptno;

 

--满外连接的写法
select * from emp full outer join dept on emp.deptno = dept.deptno;

重点关注:WHERE子句中的连接顺序．

重点关注 ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

--列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
select a.ename, a.sal, dname, b.ename, grade
  from emp a, emp b, dept d, salgrade s
 where a.deptno = d.deptno(+)
   and a.mgr = b.empno
   and a.sal between losal and hisal
   and a.sal > (select avg(sal) from emp);