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);