Oracle之select查询语句

一、子查询

一个查询的条件包含另一个查询的结果

子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。 

子查询按照返回数据的类型可以分为单行子查询、多行子查询和多列子查询。 

1.如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符

查询比SCOTT工资高的雇员名字和工资

SELECT ename, sal FROM employee  

WHERE sal>(SELECT sal FROM employee WHERE empno=7788);  

2.如果子查询未返回任何行，则主查询也不会返回任何结果

(空值)select * from emp where sal > (select sal from emp where empno = 8888); //empno的值不存在8888所以返回空值

3.如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符(>,<,=等)，可以使用下面的常用方法解决。

(多值)select * from emp where sal > (select avg(sal) from emp group by deptno);//非法

查询雇员表中排在第6～9位置上的雇员

SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM employee WHERE rownum<=9 )  

WHERE num>=6; 

说明：子查询出现在FROM从句中，检索出行号小于等于9的雇员，并生成num编号列。在主查询中检索行号大于等于6的雇员。 

注意：以下用法不会有查询结果 

SELECT ename,sal FROM employee WHERE rownum>=6 AND rownum<=9 //因为rownum从1开始

子查询中常用方法（用于多行子查询跟多列子查询）

1。any即任何一个。如果在where条件中加入>any，意思是大于任何一个，也就是大于最小的；<any为小于最大的；=any无意义一般不写。

select * from emp twhere t.sal >any(select sal from hhgy.emp where deptno=30)

 

2。some即一些。和any的用法基本相同。用any的地方都可以用some代替。不过some大多用在=操作中。表示等于所选集合中的任何一个。当然any也可以用于=操作中，效果和some相同。

select * from emp twhere t.sal =some(select sal from hhgy.emp where deptno=30)

 

3。all即所有。如果在where条件中加入>all，意思是大于每一个，也就是大于最大的；<all为小于最小的；=all无意义一般不写。

select * from emp twhere t.sal >all(select sal from hhgy.emp where deptno=30)

 

4。In意思是在某些数据之中

select * from emp twhere t.deptno in(30,40);-----deptno在30跟40之中等同于30 or 40

4.1 使用in作多列子查询

select ename, job, sal from emp where (sal,job) in (select max(sal), job from emp group by job);——多列子查询

 

5。exists主查询存在exists后面的条件。将主查询的数据放到子查询中做条件验证，根据验证结果(true或false)来决定主查询数据结果是否保留(true保留)

select * from hhgy.emp where exists(select * from hhgy.emp where deptno=30);

5.1not exists主查询不存在not exists后面的条件。结果与exists相反

select * from hhgy.emp where not exists(select * from hhgy.emp where deptno=30);

 

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。 其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 另外IN是不对NULL进行处理

如：

select 1 from dual where null in (0,1,2,null)为空

 

In和exists的区别

性能上的比较

比如Select * from T1 where x in ( select y from T2 )

执行的过程相当于:

select * from t1, ( select distinct y from t2 ) t2where t1.x = t2.y;

相对的

select * from t1 where exists ( select null from t2 where y = x )

执行的过程相当于:

for x in ( select * from t1 )

   loop

      if ( exists ( select null from t2 where y = x.x )

      then 

         OUTPUT THE RECORD

      end if

end loop

表 T1 不可避免的要被完全扫描一遍

in 是把外表和内表作hash join，而exists是对外表作loop，每次loop再对内表进行查询。

 

例如：表A（小表），表B（大表）

1：select * from A where cc in (select cc from B)

效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)

效率高，用到了B表上cc列的索引。

相反的

2：select * from B where cc in (select cc from A)

效率高，用到了B表上cc列的索引；

select * from B where exists(select cc from A where cc=B.cc)

效率低，用到了A表上cc列的索引。

带in的关联子查询是多余的，因为in子句和子查询中相关的操作的功能是一样的。如：

select staff_name from staff_member where staff_id in

(select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id);

为非关联子查询指定exists子句是不适当的，因为这样会产生笛卡乘积。如： 

select staff_name from staff_member where staff_id 

exists (select staff_id from staff_func);

not in 和not exists

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；

而not extsts 的子查询依然能用到表上的索引。

所以无论哪个表大，用not exists都比not in要快。

尽量不要使用not in子句。使用minus 子句都比not in 子句快，虽然使用minus子句要进行两次查询： 

select staff_name from staff_member where staff_id in (select staff_id from staff_member minus select staff_id from staff_func where func_id like '81%');

in 与 "=" 的区别

select name from student where name in ('zhang','wang','li','zhao');

与

select name from student where name='zhang' or name='li' or name='wang' or name='zhao'

的结果是相同的。

一/2.分页查询

SELECT*FROM (SELECT A.*, ROWNUM RNFROM (SELECT*FROM TABLE_NAME) A WHERE ROWNUM<=40 )WHERE RN>=21

//分页查询加上按照id倒序排列

SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME ORDER BY "ID" DESC) A WHERE ROWNUM <= 40 ) WHERE rn >= 21 

说明：例子中先在内层通过（ROWNUM <= 40）来控制最大值查询出页码最大范围内所有的结果，然后在外层控制最小值（ROWNUM >= 21）查询出大于最小值的结果

SELECT*FROM (SELECT A.*, ROWNUM RNFROM (SELECT*FROM TABLE_NAME) A ) WHERE RNBETWEEN 21AND40

注：在大多数情况前面一个分页查询效率较第二个高，Oracle分页查询使用伪列rownum完成，相当于使用了子查询

一/3、统计查询

通常需要对数据进行统计，汇总出数据库的统计信息。

比如，我们可能想了解公司的总人数和总工资额，或各个部门的人数和工资额，这个功能可以由统计查询完成。 

Oracle提供了一些函数来完成统计工作，这些函数称为组函数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDER BY子句中。组函数也可以称为统计函数。 

       

组函数: 

AVG:求平均值 

COUNT:求数量，返回非空行数，*表示返回所有行 

MAX:求最大值 

MIN:求最小值 

SUM:求和 

STDDEV:求标准偏差，是根据差的平方根得到的 

VARIANCE:求统计方差 

                  

组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。 

使用GROUP BY从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。 

如果不使用分组，将对整个表或满足条件的记录应用组函数。 

在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。 

         

求雇员表中不同职务的个数

SELECT COUNT(DISTINCT job) FROM employee

按职务统计工资总和

SELECT job,SUM(sal) FROM employee GROUP BY job

按部门和职务分组统计工资总和

SELECT deptno, job, sum(sal) FROM employee   

GROUP BY deptno, job;  

统计各部门的最高工资，排除最高工资小于3000的部门。

SELECT deptno, max(sal) FROM employee  

GROUP BY deptno HAVING max(sal)>=3000;  

按职务统计工资总和并排序

SELECT job 职务, SUM(sal) 工资总和 

FROM employee  

GROUP BY job  

ORDER BY SUM(sal);  

求各部门平均工资的最高值

SELECT max(avg(sal)) FROM employee GROUP BY deptno

二、多表连接查询

两个表连接有四种连接方式： 

* 内连接

* 外连接

* 交叉连接

* 自连接(自关联)

         

1．内连接 

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种： 

1.1等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。

显示雇员名称和所在部门的编号和名称。 

执行以下查询： 

SELECT a.ename, b.id, b.dname FROM employee a,dept b   

WHERE a.id=b.id

说明：相等连接语句的格式要求是，在FROM从句中依次列出两个表的名称，在表的每个列前需要添加表名，用“.”分隔，表示列属于不同的表。在WHERE条件中要指明进行相等连接的列。 

1.2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。

SELECT ename, b.id, dname FROM employee a,dept b   

WHERE a.id > b.id

1.3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

SELECT a.*,p.pub_id,p.pub_name,p.country 
   FROM authors AS a INNER JOIN publishers AS p 
   ON a.city=p.city

    

2．外连接 

内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

    

2.1 左外连接：(也叫左外连接，给出两个表的配匹行，并且以左边的表为准，如果左边表有而右边表没有的行，则在右边表的相应行选择的列显示为NULL，允许左边的基准表对应右边表多条满足条件的记录)左连接就是返回左边的匹配行，不考虑右边的表是否有相应的行

SELECT ename, b.id, dname FROMemployee a 

LEFT JOINdept b ON a.id=b.id

或者可以简写成

SELECT ename, b.id, dname FROMemployee a, dept b where a.id=b.id(+);

2.2 右外连接：(也叫右外连接，给出两个表的配匹行，并且以右边的表为准，如果右边表有而左边表没有的行，则在右边表的相应行选择的列显示为NULL，允许右边的基准表对应左边表多条满足条件的记录)

SELECT ename, b.id, dname FROM employee a 

right JOIN dept b ON a.id=b.id

或者可以简写成

SELECT ename, b.id, dname FROM employee a, dept bwhere a.id(+)=b.id;

2.3 全外连接：左表和右表都不做限制都不作为基准表，所有的记录都显示，两表不足的地方均为NULL。

SELECT ename, b.id, dname FROM employee a 

full JOIN dept b ON a.id=b.id

3．交叉连接 

交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

　例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等于6*8=48行。 

SELECT type,pub_name FROM titles

   CROSS JOIN publishers ORDER BY type

4、自连接(一般用在树形权限结构中)

自连接就是一个表，同本身进行连接。对于自连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别两个相同的表。 

SELECT worker.ename||' 的经理是 '||manager.ename AS 雇员经理   

FROM employee worker, employee manager  

WHERE worker.mgr = manager.empno;

        

------------

执行结果为： 

1.SMITH 的经理是 FORD  

2.ALLEN 的经理是 BLAKE  

3.WARD 的经理是 BLAKE 

   

注：在操作多表联合查询时，若出现以下情况，将形成笛卡尔积

– 联接条件被省略

– 联接条件无效

– 第一个表中的所有行被联接到第二个表中的所有行上

为了避免笛卡尔积，请始终包括有效的联接条件

三、联合查询

联合查询是合并两个或多个数据表中的列作为结果集

多个查询语句的结果可以做集合运算，结果集的字段类型、数量和顺序应该一样。 

Oracle共有4个集合操作 

     

UNION:并集，合并两个操作的结果，去掉重复的部分 

UNION ALL:并集，合并两个操作的结果，保留重复的部分 

MINUS:差集，从前面的操作结果中去掉与后面操作结果相同的部分 

INTERSECT:交集，取两个操作结果中相同的部分 

    

查询部门10和部门20的所有职务。

SELECT  job FROM employee WHERE deptno=10  

UNION  

SELECT  job FROM emp WHERE deptno=20;  

查询部门10和20中是否有相同的职务和工资。 

SELECT  job,sal FROM employee WHERE deptno=10  

INTERSECT  

SELECT  job,sal FROM employee WHERE deptno=20

查询只在部门表中出现，但没有在雇员表中出现的部门编号

SELECT  deptno FROM dept  

MINUS  

SELECT  deptno FROM employee

四、递归查询

Oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，一个表中两个字段:id,parentid那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。

1、其基本语法是：

select * from tablename start with 条件一

connect by 条件二

where 条件三;

条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。

条件2 是连接条件，其中用PRIOR表示上一条记录，

比如 CONNECT BY PRIOR id = parent_id就是说上一条记录的id 是本条记录的parent_id，即本记录的父亲是上一条记录。

条件3 是过滤条件，用于对返回的所有记录进行过滤。

2、运算符 PRIOR 被放置于等号前后的位置，决定着查询时的检索顺序

通过子节点向根节点追朔，start with后面条件指定子节点，prior后面条件子节点paredeptid在前

select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid

通过根节点遍历子节点，start with后面条件指定根节点，prior后面条件根节点deptid在前

select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid