欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle之DQL

程序员文章站 2022-03-31 22:57:43
一、单表查询 语法:select * from table where 条件 group by 分组 having 过滤分组 order by 排序 模糊查询,_表示占位一个字节,%填充剩下的位数 --查询员工姓名以A开头,并且第三个字母为L的员工信息 select * from emp where ......

一、单表查询

语法: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;

 oracle之DQL

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

 oracle之DQL

--满外连接的写法
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);