Oracle 基本sql总结

Oracle SQL 总结
一、简单函数介绍
1、时间格式转换以及其他应用
to_char(sysdate,’d’)-----------本周中的第几天(星期几，外国计算方法，周日是第一天)
to_char(sysdate,’dd’)-----------本月中的第几天
to_char(sysdate,’ddd’)-----------本年中的第几天
to_char(sysdate,’yyyy-mm-dd hh:mi:ss’)
to_char(sysdate,’q’)--------------当前季度
to_char(sysdate,’w’)--------------当月第几周
to_char(sysdate,’ww’)--------------当年第几周
.............................................
last_day(sysdate)-------------------当月的最后一天

2、字符函数(可用于字面字符或数据库列)
substr('abcdef',1,3)----------------截取字符串
instr('abcdef',’c’)----------------查找字符串位置
rpad(lpad('123'||'abc',9,0),12,0)---字符串拼接
ltrim、rtrim、trim-----------------字符串去空格
trim(leading,’0’ from ‘ 000123’)--去前缀
trim(trailing,’0’ from ‘ 123000’)--去后缀
trim(‘0’ from ‘ 000123000’)--前缀后缀都去
length()---------------字段长度
lower()-----------------------------------大变小
upper()-----------------------------------小变大
initcap()----------------------------------首变大
................................................................
dbms_lob.substr（）----------clob转字符串
utl_raw.cast_to_varchar2（）---blob转字符串

3、字符串判断函数
nvl、nvl2、nullif、coalesce、decode
Nvl(字段1，字段2)
Nvl2(字段1，字段2，字段3)
Nullif(字段1，字段2)---------s1=s2-->null  s1!=s2-->1
Coalesce(s1，s2，s3，........)---------从左到右谁不为空取谁
Decode(s1,s2,s3,.........)--------------if  else if ....... else

二、SQL简单的优化
SQL Select语句完整的执行顺序： 
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、select 的字段；
8、使用order by对结果集进行排序。

注：group by的使用
rollup 按分组的第一个列进行统计和最后的小计
cube 按分组的所有列的进行统计和最后的小计

四、行列转换
1、行转列
Case when then end
2、列转行
Union all
3、一行转多行
with a as (select '/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ' id from dual)
select regexp_substr(id,'[^/]+',1,rownum) id from a
connect by rownum <= length(regexp_replace(id,'[^/]+'))
4、多行转一行
wm_concat()    注意配合group by使用
五、数据补全
常见的是时间：年   月   日
1、一年的12月份
select '2014年'||level||'月' ym,level monthes from dual connect by level<13
2、某月的每天
select '2014-01-'||lpad(level,2,0) monthes from dual connect by level<= 	to_char(last_day(to_date('2014-01','yyyy-mm')),'dd')

六、树形统计
SELECT root_id,SUM(sal)
  	FROM (
  	select CONNECT_BY_ROOT(empno) root_id,sal
  	from emp
 	WHERE CONNECT_BY_ISLEAF=1
  	CONNECT BY PRIOR empno = mgr
)
GROUP BY root_id;


SELECT empno,mgr,
  	(SELECT SUM(sal) 
 	FROM emp a
 	START WITH a.empno=b.empno
 	CONNECT BY PRIOR a.empno=a.mgr ) sum_sal
  	FROM emp b
七、分组排名
rank()与dense_rank()：非连续排名与连续排名(都是简单排名)
语法：
rank() over (order by 排序字段 顺序)
rank() over (partition by 分组字段 order by 排序字段 顺序)

select empno,e.ename,e.deptno,e.sal,
rank() over (order by e.sal desc) 排名 from emp e
where e.deptno=10

select empno,e.ename,e.deptno,e.sal,
dense_rank() over (order by e.sal desc) 排名 from emp e
where e.deptno=10

分组排名
select empno,e.ename,e.deptno,e.sal,
dense_rank() over (partition by e.deptno order by e.sal desc) 排名 from emp e