最近项目中用到了很多ORACLE的查询语句,尤其是各种连接查询和子查询,现在就总结下这些语句的用法和部分效率相关内容.首先是连接查询.当我们所需要的数据存在于两张不同的表中时,这个时候就需要用到表连接查询.1. select d.deptno,d.dname,e.empno,e.ename,e.salfromdept d join emp e on d.deptno=e.deptno;--这是在做一个连接查询,连接的条件就是以表emp别名e和表dept别名d的deptno字段相等为条件做的一个表连接查询当emp表中的deptno字段与dept 中的deptno字段能一一对应的时候,就现在在结果集中2. select d.deptno,d.dname,e.empno,e.ename,e.salfrom dept d , emp ewhere d.deptno=e.deptno;--这个其实做得是和上面一样的一个连接查询,功能完全一样,如果前一个写法是现代写法,那么这个写法就算是古典写法吧3. select d.deptno,d.dnam e,e.empno,e.ename,e.salfromdept d left join emp e on d.deptno=e.deptno;--这是左连接查询,就是第一种查询里加了一个关键字left, 这种查询的结果于第一种不同,左边的表dept 中所有的记录都会显示在结果集中,无论emp表中是否有与之对应的对应left还有right,那么就是连接时,右边表的结果集全都显示在表中,叫做右连接4. ORACLE针对左右连接查询,还有特别的符号(+),并且ORALCE针对使用这个符号的连接查询做了特别的优化,select d.deptno,d.dname,e.empno,e.ename,e.salfrom dept d,emp ewhere d.deptno=e.deptno(+);--这个加号写在左边,其查询的结果和使用left的左连接查询效果相同select d.deptno,d.dname,e.empno,e.ename,e.salfrom dept d,emp ewhere d.deptno(+)=e.deptno;--这个加号写在左边,其查询的结果和使用right的右连接查询效果相同5. 最后还有一种全连接,使用full join 关键字select d.deptno,d.dname,e.empno,e.ename,e.salfrom dept d full join emp e on d.deptno=e.deptno;--这种查询效率不高,很少用到,左右表的数据全显示6.自连接,这个就是数据都在一张表中,但是需要根据条件筛选select yg.ename,sj.ename 上级from emp yg,emp sjwhere yg.mgr=sj.empno下面介绍下oracle特有的树形查询语句一般来说,如果数据库本身没有封装树形查询,那么就需要使用JAVA或者其他语言写相应的递归查询,写起来是比较复杂的,用起来效率肯定也没oracle封装后的效率高.先建立一张表,用于保存一个树形结构.create table type_tree(type_id number(5) primary key,type_name varchar2(10),parent_type number(5));也有很多项目使用的两张表或者多张表保存属性结构,这样也是可以保存的,但是这样的设计对于很复杂的树形结构是很不方便的,因为每一个节点都必须要有一张对应的表,当查询涉及多个节点的时候,就需要连接多个表,连接的表越多,效率越低.而且在以后扩展业务需求的时候,节点增加,表也增加.如果项目中不会涉及到很多的树形查询,那么我个人认为这种设计也是可以的接受的.7.使用自链接查询子节点,父节点select s.type_nam e as 节点,p.type_name as 父节点from type_tree s,type_tree pwhere s.parent_type=p.type_idorder by 2--这种查询也可以查处某种成都的树形结构,只不过其缺点是只能知道父节点和子节点两层,更多的需求还是需要使用递归查询.8.使用Oracle提供了递归查询(又叫树型查询)select t.*,level as 层次--level,oracle数据库中的关键字,表示层次from 表twhere 普通条件--也可以用于限制levelstart with 子ID=? --开始的节点connect by prior 子ID=父ID --子ID=父ID 往下查父ID＝子ID 往上查例如: 显示每个节点的信息和它所处的层次(level)select t.*,level as 层次from type_tree tstart with t.type_id=100connect by prior t.type_id=t.parent_type9.在网页上实现展示点击一个节点后,展示此节点下面的一层子节点,那么这时就需要用到where条件中过滤levelselect t.*,level as 层次from type_tree twhere level=2 --用到了level,2表示是以此节点为根节点的下一层节点start with t.type_id=100connect by prior t.type_id=t.parent_type10. 对level字段使用max可以获得从每个节点开始,向下(或向上)的层次数量select max(level) as maxlevelfrom type_tree tstart with t.type_id=107 --在此填写不同的开始节点,就可以获得此节点开始的节点数量connect by prior t.type_id=t.parent_type由此可以看出,使用oracle中内置的递归查询,可以很轻松的实现所有的树形查询了.下面介绍下子查询与效率的问题子查询分为相关子查询和非相关子查询非相关子查询是独立于外部的的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询.非相关子查询的子查询部分是可以单独执行的.相关子查询的执行以来于外部查询的数据返回一行,子查询就执行一次.由此也可以看出,相关子查询的效率是不高的,所以很少使用.示例：查询各部门的人数，格式：部门编号，部门名称，人数select deptno,dname,0 as 人数from dept; --计算列子查询,这个查询本身没什么实际意义,就是始终让一列为0select deptno,dname,(select count(*) from emp) as 人数from dept; --非相关子查询,这个查询本身也没什么实际意义,就是始终让一列为emp表中的记录总数select deptno,dname,(select count(*) from emp where emp.deptno=dept.deptno) as 人数from dept;--相关子查询,查询每个部门的人数,子查询中的所用到的where 条件后面的 dept,deptno依靠外部查询传入这样看起来起来,就像没一条dept的记录都会让子查询执行一次.DEPTNO DNAME 人数10 ACCOUNTING select count(*) from emp where emp.deptno=1020 RESEARCH select count(*) from emp where emp.deptno=2030 SALES select count(*) from emp where emp.deptno=3040 OPERATIONS select count(*) from emp where emp.deptno=40如果一张表中的记录条数达到了10000条,而它的每一行某个字段又作为了子查询查询另外一张表(这张表有20000条记录)的一个条件,那么这样计算下来,这条语句一共执行了10000+10000*20000次,效率是相当的低了.。
