SQL 总结

1. select 使用正则表达式
正则表达式的模式串, 与linux基本相同, oracle提供以下4个函数来支持正则表达式:
REGEXP_LIKE: 比较一个字符串是否与正则表达式匹配(看来是返回true, false) (srcstr, pattern)
select * from test where regexp_like(column_name, ‘^[0-9]+$‘);
REGEXP_REPLACE: 搜索并替换匹配的正则表达式(srcstr, pattern[,replacestr[,position[,occurrence]]])
REGEXP_INSTR: 在字符串中查找正则表达式, 并返回匹配的位置 (srcstr, pattern[,position[,occurrence[return_option]]])
REGEXP_SUTSTR: 返回与正则表达式匹配的子字符串(srcstr, pattern[,position[,occurrence]])

2. date +-1 天, date +- 1/24, date - date 差别天数

3. 比较重要函数, substr, concat, length, lpad|rpad, trim, replace, round, trunc, mod, nvl, nvl2

4. case, decode 注意: decode 中可以有 select 语句
用法:
简单case
select
case sex --列名字
when ‘1‘ then ‘男‘
when ‘2‘ then ‘女‘
else ‘其他‘
end
搜索case
select
case -- 等同于 (case true)
when sex = ‘1‘ then ‘男‘
when sex = ‘2‘ then ‘女‘
else ‘其他‘
end

case 语句不同位置:
在 where 条件中~
select t2.*, t1.*
from t1, t2
where (case
when t2.compare_type = ‘a‘ and t1.some_type like ‘nothing%‘ then 1
when t2.compare_type !=‘a‘ and t1.some_type not like ‘nothing%‘ then 1
else 0
end) = 1
case还可以出现在group by语句中来作为分组的条件
5. timestamp, timestamp with local time zone, timestamp with timezone 等 类型的使用场合

6. 各种 object 的创建方法, db link, constraint, view, sequence, index, synonym 等等

7. 高级 group 函数分组 (rollup, cube, grouping)

8. 子查询相关 (嵌套子查询, 相关子查询, exists)

9. 树型结构查询语句(start with connecty by prior condition)

10.insert 多行语句

11.blog for plsql 的总结

12.book: plsql programming

13.with语句
   

with home
as
(select value home
from v$diag_info
where name = ‘ADR Home‘
)
select name,
case when value <> home.home
then replace(value, home.home, ‘$home$‘)
else value
end value
from v$diag_info, home
/