函數 - 天天看點

單行函數

大小寫函數

LOWER 轉小寫

UPPER 轉大寫

INITCAP 首字母大寫

字元函數

CONCAT

用來連接配接兩個字元串，隻能有兩個參數（不是兩個參數會報錯）

Oracle一般使用連接配接符 “||”來連接配接字元串，連接配接符不限字元串的個數

想要使用concat函數連接配接多個字元串，隻能嵌套使用

并且隻有兩個參數都為null時才傳回null

SUBSTR

截取字元串，兩種用法

substr(a,b) 從a中，第b位開始截取，直到最後

substr(a,b,c) 從a中，第b位開始，截取c位

oracle中下标都是從1開始，如果指定為0也是從1開始計算

LENGTH 字元長度

LENGTHB 位元組長度

INSTR(a,b) 在a中，查找b，傳回b第一次出現的位置

左填充

LPAD(原字元串，填充後的位數，填充符号)

右填充

RPAD(原字元串，填充後的位數，填充符号)

ASCII 将字元按ASCII碼轉換為數字

CHR 将數字按ASCII碼轉換為字元

LTRIM 去掉左邊空格

RTRIM 去掉右邊空格

TRIM

REPLACE 替換指定的字元或字元串

數字函數

ROUND 四舍五入

TRUNC　截取

MOD　　取餘

日期函數

查詢時間

select sysdate from dual;

運作結果：

SYSDATE
------------
08-7月 -19

按指定格式顯示時間

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

TO_CHAR(SYSDATE,'YY
-------------------
2019-07-08 10:35:20

對日期進行數學運算

在日期上加減

select sysdate+365 from dual;

SYSDATE+365
--------------
07-7月 -20

兩個日期相減傳回相差天數

select empno "員工号",ename "姓名",sysdate-hiredate "入職天數" from emp;

員工号 姓名 入職天數
---------- ---------- ----------
7369 SMITH 14082.458
7499 ALLEN 14017.458
7521 WARD 14015.458
7566 JONES 13976.458
7654 MARTIN 13797.458
7698 BLAKE 13947.458
7782 CLARK 13908.458
7788 SCOTT 11768.458
7839 KING 13747.458
7844 TURNER 13817.458
7876 ADAMS 11734.458
7900 JAMES 13731.458
7902 FORD 13731.458
7934 MILLER 13680.458

兩個日期相差月數 months_between

select empno "員工号",ename "姓名",months_between(sysdate,hiredate) "入職月數" from emp;

員工号 姓名 入職月數
---------- ---------- ----------
7369 SMITH 462.724512
7499 ALLEN 460.627738
7521 WARD 460.563222
7566 JONES 459.208383
7654 MARTIN 453.369673
7698 BLAKE 458.240641
7782 CLARK 456.982577
7788 SCOTT 386.659996
7839 KING 451.724512
7844 TURNER 454
7876 ADAMS 385.530964
7900 JAMES 451.176125
7902 FORD 451.176125
7934 MILLER 449.530964

指定日期，幾個月後的日期 add_months

select add_months(sysdate,5) from dual;

ADD_MONTHS(SYS
--------------
08-12月-19

指定日期，下一個星期幾的日期

next_day(x,y)

x 為日期，y為字元型時，取值從星期一到星期日

　　　　 y為數字時，取值從1~7，不過1表示星期日，2表示星期一，6表示星期五

如目前日期為2019年7月8号時

select next_day(sysdate,'星期一') from dual; --查詢目前日期之後，第一個星期一的日期
select next_day(sysdate,2) from dual;--同上

運作結果均為：

NEXT_DAY(SYSDA
--------------
15-7月 -19

select next_day(sysdate,'星期二') from dual;--查詢目前日期之後，第一個星期二的日期

運作結果為：

NEXT_DAY(SYSDA
--------------
09-7月 -19

select next_day(sysdate,2) from dual;--查詢目前日期之後，第一個星期一的日期

NEXT_DAY(SYSDA
--------------
15-7月 -19

指定日期，所在月份的最後一天 last_day

select last_day(sysdate) from dual;

LAST_DAY(SYSDA
--------------
31-7月 -19

按照規則對日期進行四舍五入 round

select round(sysdate,'year') from dual;

ROUND(SYSDATE,
--------------
01-1月 -20

select round(sysdate,'month') from dual;

ROUND(SYSDATE,
--------------
01-7月 -19

按照規則對日期進行截斷 trunc

select trunc(sysdate,'year') from dual;

TRUNC(SYSDATE,
--------------
01-1月 -19

select trunc(sysdate,'month') from dual;

TRUNC(SYSDATE,
--------------
01-7月 -19

擷取日期的年份或月份等 extract

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

EXTRACT(YEARFROMSYSDATE)
------------------------
                    2019

轉換函數

日期或數字按指定格式轉換成字元串 to_char

日期轉字元串：

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

TO_CHAR(SYSDATE,'YY
-------------------
2019-07-08 14:18:35

數字轉字元串：

格式中，'9' 代表一位數字，'0' 代表零，'$' 代表美元符，'L' 代表本地貨币符号， '.' 表示小數點， ',' 表示千位符号

前後0多了結果都會有0，前面9多了沒有0，後面9多了有0

結果前面會有空格，可以采用trim去掉空格

指定格式字元串轉換成日期 to_date

select to_date('1999-01-01','yyyy-mm-dd') from dual;

TO_DATE('1999-
--------------
01-1月 -99

字元串轉換成數字 to_number

select to_number('2')+to_number('3') from dual;

TO_NUMBER('2')+TO_NUMBER('3')
-----------------------------
                            5

通用函數

nvl(a,b) 濾空函數，當a為空時取b的值

nvl2(a,b,c) 濾空函數，當a為空時取c的值，否則取b的值

nullif(a,b) a與b相等時傳回null，否則傳回a

coalesce(a,b,c,.....) 傳回第一個不為空的值

多行函數

多行函數，也叫組函數或分組函數。作用于一組資料，并對一組資料傳回一個值

1.組函數會忽略空值，要想把空值也包含在内，可以使用濾空函數

2.分組函數去重

3.select清單中除了組函數外，其他列都必須包含在group by 子句中

4.分組之後進行條件查詢使用having，where後面不能有組函數