oracle查询操作注意事项

oracle查询操作注意事项

1、模糊查询
 如果要将通配符%和_作为字符值使用，必须在escape后使用转义字符，举例如下：
--查找g3e_username字段以‘PT_’开头的记录
select * from G3E_DIALOGTAB where g3e_username like 'PTa_%' escape 'a'

2、order by 子句的使用
当在select语句中同时包含有多个子句（where，group by，having，order by）等时，order by必须是最后一条子句。

3、关于多表插入的问题
使用all操作符执行多表插入
insert all
when deptno = 10 then into dept10
when deptno = 20 then into dept20
when deptno = 30 then into dept30
when job = 'CLERK' then into CLERK
else into other
  select * from emp;

使用first多表插入
采用first多表插入，如果数据已经满足了先前的条件，且已经插入到了某表，那么改行数据在后续的插入中不会被再次使用。
insert first
when deptno = 10 then into dept10
when deptno = 20 then into dept20
when deptno = 30 then into dept30
when job = 'CLERK' then into CLERK
else into other
  select * from emp;


4、group by和having的使用
使用group by子句进行分组统计时，选择列表中的列必须出现在group by子句中；而且只能使用having来限制分组显示结果（不能使用where子句）。

5、在group by子句中使用rollup或cube
select operator,district,sum(cur_conduit_len) from N9_RPT_USERDATA group by rollup(operator,district);--对operator按district再进行一个总计
select operator,district,sum(cur_conduit_len) from N9_RPT_USERDATA group by cube(operator,district);--纵向统计 除了对operator进行总计，还对district进行一个总计

6、grouping函数的使用
 该函数用于确定统计结果是否用到了特定列，用到的则用1表示，反之为0.
select operator,district,sum(cur_conduit_len),grouping(operator),grouping(district) from N9_RPT_USERDATA group by rollup(operator,district);

7、grouping sets操作符的使用
 用于显示分组统计结果
select operator,district,sum(cur_conduit_len) from N9_RPT_USERDATA group by grouping sets(operator,district);

8、(+)操作符的使用
   1）(+)操作符只能出现在where子句中，且不能与outerjoin语法同时使用。
   2）当使用(+)操作符操作符执行外连接时，如果where子句中包含有多个条件，则必须在所有条件中都包含(+)操作符。
第二点的错误让我印象很深刻，之前写了个语句，因为在where子句中包含有多个条件，但是没有都指定(+)操作符，导致结果的错误。

select b.district 子区域,
       sum(decode(a.ownership, '自建', 1, 0)) 自建吊线段数量,
       sum(decode(a.ownership, '自建', 0, 1)) 其它吊线段数量,
       sum(decode(a.ownership, '自建', a.length, 0)) 自建吊线段长度,
       sum(decode(a.ownership, '自建', 0, a.length)) 其它吊线段长度
  from b$i_hangline_info a, b$gc_netelem b
 where a.g3e_fid(+) = b.g3e_fid
   and a.feature_state<> '拆除' --这个条件是后来加上的，正是这个条件的添加导致了统计结果的错误
   and b.district in ('宝山','长宁','崇明','奉贤','虹口','黄浦','嘉定','金山','静安','卢湾','普陀','浦东','青浦','松江','徐汇','杨浦','闸北','闵行')
 group by b.district;

以上语句查询出来的结果没有全，是因为采用外连接的情况下，a.feature_state<> '拆除'这个条件没有加外连接操作符，导致在统计中第一列的统计值为0的情况下就不显示了，尽管该列后面的结果不为0.只要修改如下就可以正常显示查询结果了：
select b.district 子区域,
       sum(decode(a.ownership, '自建', 1, 0)) 自建吊线段数量,
       sum(decode(a.ownership, '自建', 0, 1)) 其它吊线段数量,
       sum(decode(a.ownership, '自建', a.length, 0)) 自建吊线段长度,
       sum(decode(a.ownership, '自建', 0, a.length)) 其它吊线段长度
  from b$i_hangline_info a, b$gc_netelem b
 where a.g3e_fid(+) = b.g3e_fid
   and a.feature_state(+) <> '拆除'
   and b.district in ('宝山','长宁','崇明','奉贤','虹口','黄浦','嘉定','金山','静安','卢湾','普陀','浦东','青浦','松江','徐汇','杨浦','闸北','闵行')
 group by b.district;

3）(+)操作符只能适用于列，而不能用在表达式上。
4）(+)操作符不能与OR和IN操作符一起使用。
5）(+)操作符只能用于实现左外连接或右外连接，而不能用于实现完全外连接。

9、在多行子查询中使用all或any
单列单行或单列多行子查询
1）使用all操作符
all操作符必须与单行操作符结合使用，并且返回行必须要匹配于所有子查询结果。
select * from g3e_attribute where g3e_ano >all (select g3e_ano from g3e_tabattribute where g3e_dtno=247303381);

2)使用any操作符
all操作符必须与单行操作符结合使用，并且返回行只需匹配于子查询的任一个结果即可。
select * from g3e_attribute where g3e_ano >any (select g3e_ano from g3e_tabattribute where g3e_dtno=247303381);

多列多行子查询
成对比较
select * from i_exch_info where (gwm_fid,gwm_fno) in (select gwm_fid,gwm_fno from gc_netelem where gwm_fno=25057) ;

非成对比较：
select * from i_exch_info where gwm_fid in (select gwm_fid from gc_netelem where gwm_fno=25057) and gwm_fno in (select gwm_fno from gc_netelem where gwm_fno=25057);

10、在查询中注意使用minus和intersect
  minus可以替代not in 或not exists；intersect可以替代and