oracle sql-白红宇

oracle sql

阅读量：2155 次

发布时间：2019-05-01

本文共 1886 字，大约阅读时间需要 6 分钟。

1. rownumoracle前10条select * from accounts t where rownum<11 分页 取 10 到20条 :select * from (select t.*,rownum r from accounts t) a  where  a.r>=10   and   a.r<=20; 分页:select * from (select row_.*,rownum rownum_ from (真正的SQL语句) row_ where rownum <=?) where rownum_ > ? 2. 随机取5条   order by dbms_random.value()  select * from (select t.id,t.user_type from accounts t order by dbms_random.value()) x where rownum <= 5 3. nulls sort3.1 first: 空值在最前select ename,sal,comm from emp order by comm nulls first 3.2 last: 空值在最后select ename,sal,comm from emp order by comm nulls last 4. to_date:SELECT TO_DATE(t.recent_access_date, 'YYYY-MM-DD HH24:MI:SS') as expiredate from accounts tSELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') as nowTime from dual 5. 求时间差:  sysdate 是今天的时间round(to_number(end-date-start_date))- 消逝的时间（以天为单位）round(to_number(end-date-start_date)*24)- 消逝的时间（以小时为单位）round(to_number(end-date-start_date)*1440)- 消逝的时间（以分钟为单位） select round(to_number(to_date('2006-11-30','YYYY-MM-DD')-to_date('2006-10-01','YYYY-MM-DD'))) from dual;select round(sysdate-to_date('2006-10-01','YYYY-MM-DD')) from dual; 6. coalesce(t.src_type,'0') 把空的用0代替select t.user_name,t.user_type, coalesce(t.src_type,'0') from accounts t 7. ADD_MONTHSselect to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual; select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual; 8. substrselect substr('1234567890', 9, 1) value from dual 9..替换函数select * from accounts  acc where replace(acc.pas,'-','') like '%057185663557%' 10. 获取上个月第1天上个月第1天: 这个月最后一天+1天(也就是下个月1号)，然后再减去2个月select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual; 上个月最后1天select to_char(add_months(last_day(sysdate),-1),'yyyy-mm-dd') LastDay from dual 11.DDD当年第几天select to_char(sysdate,'DDD') from dual; DD当月第几天select to_char(sysdate,'DD') from dual; D这周第几天select to_char(sysdate,'D') from dual;