[學習筆記] Oracle字元串函數、日期函數、數值函數、轉換函數、聚合函數

函數

• 單行函數：對一行數據進行操作的函數，如字元串函數、數值函數、轉換函數、日期函數等。
• 聚合函數：同時對多行數據進行操作，如求和函數等。

字元串函數

日期函數

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select systimestamp from dual;
select dbtimezone from dual;

-- 結果為 2018-11-30，2018-10-31
select 
    to_char(add_months(to_date('2018-10-31','yyyy-mm-dd'),1),'yyyy-mm-dd'), 
    to_char(add_months(to_date('2018-09-30','yyyy-mm-dd'),1),'yyyy-mm-dd')
from dual;

select last_day(sysdate) from dual;

select next_day(to_date('2018-11-12','yyyy-mm-dd'),'星期四') from dual;

select extract(year from timestamp '2018-11-12 15:36:01') as year,
       extract(month from timestamp '2018-11-12 15:36:01') as month,
       extract(day from timestamp '2018-11-12 15:36:01') as day,  
       extract(minute from timestamp '2018-11-12 15:36:01') as minute,
       extract(second from timestamp '2018-11-12 15:36:01') as second
from dual;

select months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
                      to_date('2017-11-12', 'yyyy-mm-dd')) as zs, --整數
       months_between(to_date('2018-11-12', 'yyyy-mm-dd'),
                      to_date('2017-10-11', 'yyyy-mm-dd')) as xs, --小數
       months_between(to_date('2017-11-12', 'yyyy-mm-dd'),
                      to_date('2018-10-12', 'yyyy-mm-dd')) as fs --負數
from dual;

select sysdate, --當前時間
       round(sysdate, 'yyyy') as year, --按年
       round(sysdate, 'mm') as month, --按月
       round(sysdate, 'dd') as day, --按天
       round(sysdate) as mr_day, --預設不填按天
       round(sysdate, 'hh24') as hour --按小時
from dual;

select sysdate, --當前時間
       trunc(sysdate, 'yyyy') as year, --按年
       trunc(sysdate, 'mm') as month, --按月
       trunc(sysdate, 'dd') as day, --按天
       trunc(sysdate) as mr_day, --預設不填按天
       trunc(sysdate, 'hh24') as hour --按小時
from dual;

數值函數

轉換函數

其中數值的格式f可以參考下表：

聚合函數

select classno, avg(t.age) from stuinfo t where t.age < 30 group by t.classno;

select classno, sum(age), count(*), sum(age) / count(*), avg(age) from stuinfo t where t.age < 30 group by t.classno;