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
Oracle 基本sql总结
最新推荐文章于 2024-08-16 18:39:05 发布