oracle高级sql的参考资料

1.集合操作
学习oracle中集合操作的有关语句，掌握union,union all,minus,interest的使用,能够描述结合运算，并且能够将多个查询组合到一个查询中去，能够控制行返回的顺序。
包含集合运算的查询称为复合查询。见表格1-1
表1-1
Operator         Returns         content
UNION         由每个查询选择的所有不同的行          并集不包含重复值
UNION ALL         由每个查询选择的所有的行，包括所有重复的行         完全并集包含重复值
INTERSECT         由两个查询选择的所有不同的行          交集
MINUS         由第一个查询选择的所有不同的行          差集

所有的集合运算与等号的优先级相同，如果SQL语句包含多个集合运算并且没有圆括号明确地指定另一个顺序，Oracle服务器将以从左到右的顺序计算。你应该使用圆括号来明确地指定带另外的集合运算的INTERSECT (相交) 运算查询中的赋值顺序。
Union all 效率一般比union高。
1.1.union和union all
UNION(联合)运算
UNION运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行，但除去任何重复的行。
原则 :

􀂃被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。
􀂃联合运算在所有被选择的列上进行。
􀂃在做重复检查的时候不忽略空(NULL)值。
􀂃IN运算有比UNION运算高的优先级。
􀂃在默认情况下，输出以SELECT子句的第一列的升序排序。

全联合(UNION ALL)运算
用全联合运算从多个查询中返回所有行。
原则

􀂃和联合不同，重复的行不被过滤，并且默认情况下输出不排序。
􀂃不能使用DISTINCT关键字。
使用：
Select statement union | union all Select statement;

1.2.intersect交集操作
相交运算
用相交运算返回多个查询中所有的公共行。 无重复行。
原则

􀂃在查询中被 SELECT 语句选择的列数和数据类型必须与在查询中所使用的所有的 SELTCT 语句中的一样，但列的名字不必一样。
􀂃相交的表的倒序排序不改变结果。
􀂃相交不忽略空值。
使用：
Select statement intersect all Select statement;

1.3. minus差集操作
相减运算
用相减运算返回由第一个查询返回的行，那些行不出现在第二个查询中 (第一个SELECT语句减第二个SELECT语句)。
原则

􀂃在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样，但列的名字不必一样。
􀂃对于MINUS运算，在WHERE子句中所有的列都必须在SELECT子句中。


集合运算的原则
•在两个SELECT列表中的表达式必须在数目上和数据类型上相匹配
•可以用圆括号改变执行的顺序
•ORDER BY子句：–只能出现在语句的最后–从第一个SELECT语句接收列名、别名，或者位置记号

注：•除了UNION ALL，重复行自动被清除
•在结果中的列名是第一个查询中出现的列名
•除了UNION ALL，默认情况下按升序顺序输出
2.exists和not exists的使用
2.1. exists的使用
Exists用于只能用于子查询，可以替代in，若匹配到结果，则退出内部查询，并将条件标志为true，传回全部结果资料,in不管匹配到匹配不到都全部匹配完毕，使用exists可以将子查询结果定为常量，不影响查询效果，而且效率高。如查询所有销售部门员工的姓名，对比如下：
IN is often better if the results of the subquery are very small
When you write a query using the IN clause, you're telling the rule-based optimizer that you want the inner query to drive the outer query.
When you write EXISTS in a where clause, you're telling the optimizer that you want the outer query to be run first, using each value to fetch a value from the inner query.
In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.


In和exists对比：
若子查询结果集比较小，优先使用in，若外层查询比子查询小，优先使用exists。因为若用in，则oracle会优先查询子查询，然后匹配外层查询，若使用exists，则oracle会优先查询外层表，然后再与内层表匹配。最优化匹配原则，拿最小记录匹配大记录。
使用in
select last_name, title
        from s_emp
        where dept_id in
                (select id
                from s_dept
                where name='Sales');        

使用exists
select last_name,title
       from s_emp e
       where  exists
       (select 'x' --把查询结果定为constant，提高效率
        from s_dept s where s.id=e.dept_id and s.name='Sales');
2.2 not exists的使用
        与exists 含义相反，也在子查询中使用，用于替代not in。其他一样。如查询不在销售部的员工姓名
select last_name,title
       from s_emp e
       where  not exists
       (select 'x' --把查询结果定为constant，提高效率
        from s_dept s where s.id=e.dept_id and s.name='Sales');
3.with子句
1.使用with子句可以重复使用相同的子查询块,通过select调用，一般在子查询用到多次情况下。
2.with子句的返回结果存到用户的临时表空间中
3.with子句可以提高查询效率
4.有多个with的时候，用逗号隔开
5.最后一个with子句与下面的查询之间不能有逗号，只通过右括号分割,查询必须用括号括起来
目的是为了重用查询。
语法：
With alias_name as (select1),
With alias_name2 as (select2),
…
With alias_namen as (select n)
Select ….
如查询销售部门员工的姓名
  --with clause
with a as
     (select id from s_dept where name='Sales' order by id)
  select last_name,title
         from s_emp where dept_id in (select * from a);--使用select查询别名
4.merge into合并资料
语法：(其中as可以省略)
MERGE INTO table_name AS table_alias
USING (table|view|sub_query) AS alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)—多个列以逗号分割                      //可以不指定列
VALUES (column_values);

作用：将源数据(来源于实际的表,视图,子查询)更新或插入到指定的表中(必须实际存在),依赖于on条件,好处是避免了多个insert和update操作。Merge是一个目标性明确的操作符,不允许在一个merge语句中对相同的行insert或update操作。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。例子如下：

drop table t;
CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

drop table t1;
CREATE TABLE T1 AS
SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE
FROM DBA_TABLES;


select *  from dba_objects;
select *  from dba_tables;

MERGE INTO T1 USING T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);--insert后面不写表示插入全部列


MERGE INTO T1 USING T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);--常见错误，连接条件不能获得稳定的行，可以使用下面的用子查询


MERGE INTO T1
USING (SELECT OWNER, OBJECT_NAME, MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
  WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);


SELECT ID, OWNER, OBJECT_NAME, OBJECT_TYPE FROM T
MINUS
SELECT * FROM T1;

drop table subs;
create table subs(msid number(9),
                    ms_type char(1),
                  areacode number(3)
                    );
                    
     drop table acct;               
                     create table acct(msid number(9),
                     bill_month number(6),
                    areacode   number(3),
                    fee        number(8,2) default 0.00);
                    
insert into subs values(905310001,0,531);
insert into subs values(905320001,1,532);
insert into subs values(905330001,2,533);                 
commit;

merge into acct a --操作的表
      using subs b on (a.msid=b.msid)--使用原始数据来源的表，并且制定条件，条件必须有括号
     when matched then
          update set a.areacode=b.areacode--当匹配的时候，执行update操作，和直接update的语法不一样，不需要制定表名
     when not matched then--当不匹配的时候，执行insert操作，也不需要制定表名，若指定字段插入，则在insert后用括号标明，不指定是全部插入
          insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);
         
   
   
另外，MERGE语句的UPDATE不能修改用于连接的列，否则会报错
select *  from acct;
select * from subs;
--10g新特性，单个操作
merge into acct a
      using subs b on(a.msid=b.msid)
    when not matched then--只有单个not matched的时候，只做插入，不做更新，只有单个matched的时候，只做更新操作
         insert(a.msid,a.bill_month,a.areacode) values(b.msid,'200702',b.areacode);
         
update acct set areacode=800 where msid=905320001;

delete from acct where areacode=533 or areacode=531;

insert into acct values(905320001,'200702',800,0.00);


--删除重复行
delete from subs b where b.rowid<(
select max(a.rowid) from subs a where a.msid=b.msid and a.ms_type=b.ms_type and a.areacode=b.areacode);

--10g新特性，merge操作之后，只有匹配的update操作才可以，用delete where子句删除目标表中满足条件的行。
  merge into acct a
     using subs b on (a.msid=b.msid)
   when MATCHED then
        update set a.areacode=b.areacode        
        delete where (b.ms_type!=0)
        when NOT MATCHED then
        insert(msid,bill_month,areacode)
        values(b.msid,'200702',b.areacode)
        where b.ms_type=0;
  --10g新特性，满足条件的插入和更新         
merge into acct a
     using subs b on (a.msid=b.msid)     
   when MATCHED then
        update set a.areacode=b.areacode
        where b.ms_type=0
   when NOT MATCHED then
        insert(msid,bill_month,areacode)
        values(b.msid,'200702',b.areacode)
        where b.ms_type=0;

select *  from subs where ms_type=0;