oracle 部分语法

以下内容结合了很多人的博客，在此表示感谢

1.   with as 语法

    当查询中多次用到某一部分时，可以用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中，避免了重复解析，所以执行效率会提高不少。临时表在一次查询结束自动清除。

语法格式：

with 

    tempTable1   as    (select  * from table1),

     tempTable2   as    (select  * from table2)

     ......

select * from  tempTable2;

用法：

1）创建一个临时表

with 

  temp1 as (select  1 from dual)

select * from temp1;

2）创建多个临时表

with 

  temp1 as (select  11  from dual),

   temp2 as (select  22  from dual)

select * from temp2;

with as与union all 配合默契

    因为union all的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用with as短语，则只要执行一遍即可

with 

  temp1 as (select  11  from dual),

  temp2 as (select  22  from dual)

select * from temp1 union all

select * from temp2;

with as优点

    增加了sql的易读性，如果构造了多个子查询，结构会更清晰；

更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标

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

2. case when 语法

语法格式：

1)简单Case函数  

CASE type  

WHEN 1 THEN 'cat'  

WHEN 2 THEN 'dog'  

ELSE 'other' END  

2)Case搜索函数  

CASE

WHEN type = 1 THEN 'cat'  

WHEN type = 2 THEN 'dog'  

ELSE 'other' END 

case when 在语句中不同位置的用法

create table ani (

id number(2),

type number(2),

name varchar2(5)

)

select * from ani;

1) select case when 用法

select id,name,

(case when type = 1 then 'cat' 

 when type = 2 then 'dog'  

 else 'other'

end) "类别"--别名可用双引号

from ani;

2) where case when 用法

select id,name from ani 

where (

case when type = 1 then 'cat' 

 when type = 2 then 'dog'  

 else 'other'

end) = 'dog'

3) group by case when 用法（目前未发现有什么用）

select (case when type = 1 then 'cat' 

 when type = 2 then 'dog'  

 else 'other'

end) a

from ani group by (case when type = 1 then 'cat' 

 when type = 2 then 'dog'  

 else 'other'

end)

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

3. exists ,not exists  语法

exists ：抓取满足条件的数据

not exists ：抓取不满足条件的数据

create table a (

id number(2),

name varchar2(5)

)

create table b (

id number(2),

aid number(2),

name varchar2(5)

)

select * from a;

select * from b;  

映射关系：a.id = b.aid

in 与  exists  

select a.id aid,a.name aname from a where a.id in (select aid from b) ; --在其中

select a.id aid,a.name aname from a where exists (select * from b where b.aid = a.id) ; --存在

not in 与 not  exists  

select a.id aid,a.name aname from a where a.id not in (select aid from b);

select a.id aid,a.name aname from a where not exists (select * from b where b.aid = a.id) ;

效率问题

exists和in

1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

    T1数据量小而T2数据量非常大时，T1<<T2 时，1) 的查询效率高。

2) select * from T1 where T1.a in (select T2.a from T2) ;

     T1数据量非常大而T2数据量小时，T1>>T2 时，2) 的查询效率高。

not in 和not exists  

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

而not extsts 的子查询依然能用到表上的索引。 所以无论那个表大，用not exists都比not in要快。