Oracle函數，當任意一個參數為空字元串或者null時，結果傳回null
聚合函數
count
使用方式有5種
select count(*) from student;--統計總行數
select count(0) from student;--統計總行數,括号裡面的0表示什麼意思？
select count(id) from student;--統計列id不為null的總行數
select count(rowid) from student;--統計總行數
select count(distinct id) from student;--統計id不為null并且去掉id重複的總行數
ps:前面四種count的方式，在沒有索引的情況是一樣的，都會全表掃描。選中SQL按F5會打開解釋計劃視窗，可以看到前四種count的方式執行過程都是一樣。如果有索引，一般情況下，count(*),count(0),count(rowid)會走索引，count(id)走索引掃描還是走全表掃描要看字段id是否有索引。
sum
select sum(idno) from student;--任意行的idno不能為非數字，可以為null
avg
select avg(idno) from student;--計算之前會把null值排除在外
max
select max(idno) from student;--
列中的資料可以是數值、字元串或是日期時間資料類型，會忽略null值，但是如果所有的值都為null，則會傳回null，字元串是怎麼比較大小的？
min
select min(idno) from student;--同上
字元函數
大小寫
select lower(name) from dual; --lower()轉小寫
select upper(name) from dual; --upper()轉大寫
select initcap(name) from dual; --initcap()首字母大寫
非英文字母不會進行轉換，但任然會查詢出來，null值也會查詢出來
資料内容合并
select concat(id,name) from student;--concat函數隻能是兩個參數，多行資料内容合并成一行可以使用||
字元串截取
substr
隻針對字元類型
select substr('HelloWorld',0,3) value from dual;--傳回結果：Hel，截取從“H”開始3個字元
select substr('HelloWorld',1,3) value from dual;--傳回結果：Hel，截取從“H”開始3個字元
select substr('HelloWorld',2,3) value from dual;--傳回結果：ell，截取從“e”開始3個字元
select substr('HelloWorld',0,100) value from dual;--傳回結果：HelloWorld，100雖然超出預處理的字元串最長度，但不會影響傳回結果，系統按預處理字元串最大數量傳回。
select substr('HelloWorld',5,3) value from dual;--傳回結果：oWo
select substr('Hello World',5,3) value from dual;--傳回結果：o W (中間的空格也算一個字元串，結果是：o空格W)
select substr('HelloWorld',-1,3) value from dual;--傳回結果：d （從後面倒數第一位開始往後取1個字元，而不是3個。）
select substr('HelloWorld',-2,3) value from dual;--傳回結果：ld （從後面倒數第二位開始往後取2個字元，而不是3個。）
select substr('HelloWorld',-3,3) value from dual;--傳回結果：rld （從後面倒數第三位開始往後取3個字元）
select substr('HelloWorld',-4,3) value from dual;--傳回結果：orl （從後面倒數第四位開始往後取3個字元）
select substr('HelloWorld',0) value from dual;--傳回結果：HelloWorld，截取所有字元
select substr('HelloWorld',1) value from dual;--傳回結果：HelloWorld，截取所有字元
select substr('HelloWorld',2) value from dual;--傳回結果：elloWorld，截取從“e”開始之後所有字元
select substr('HelloWorld',3) value from dual;--傳回結果：lloWorld，截取從“l”開始之後所有字元
select substr('HelloWorld',-1) value from dual;--傳回結果：d，從倒數一個“d”開始,截取所有字元
select substr('HelloWorld',-2) value from dual;--傳回結果：ld，從倒數第二個“l”開始,截取所有字元
select substr('HelloWorld',-3) value from dual;--傳回結果：rld，從倒數第三個“r”開始,截取所有字元
trunc
trunc可以對日期進行格式化，有關日期的格式參數，參考https://blog.csdn.net/qq_26565861/article/details/83110968
trunc可以對數字進行截取，與round的差別是不進行四舍五入
字元串長度計算
--length計算字元數，lengthb計算位元組數，編碼方式為UTF8/GBK時，一個中文占3/2個位元組長度
select length('hello你好') from dual;
select lengthb('hello你好') from dual;
查找字元的位置
select instr('helloworld', 'o') from dual;--instr從左向右找第一次出現的位置，從1開始
select instr('helloworld', 'z') from dual;--找不到傳回0
填充
select lpad('hello',10,'#')from dual;--lpad如果位數不夠，從左邊開始補位
select lpad('hello',10)from dual;--第三個參數不傳時，用空格
select lpad('甲骨文',10,'$') from dual;--注意字元集編碼為UTF8時中文也是占用兩個長度
select rpad('hello',3,'#')from dual;--rpad如果位數不夠，從右邊邊開始補位
去除空格
--基本用法，注意全角空格去不掉
SELECT trim(' aaa bbb ccc ') trim FROM dual;
SELECT ltrim(' aaa bbb ccc ') trim FROM dual;
SELECT rtrim(' aaa bbb ccc ') trim FROM dual;
--進階用法，去除特定字元 trim( leading | trailing | both string1 FROM string2) 注意string1隻能是單個字元，如果沒有設定string1參數，預設去除空字元串
SELECT trim(leading '$' from '$aaa bbb ccc$') FROM dual;
SELECT trim(trailing '$' from '$aaa bbb ccc$') FROM dual;
SELECT trim(both '$' from '$aaa bbb ccc$') FROM dual;
SELECT trim(both from ' aaa bbb ccc ') FROM dual;
字元串替換
select replace('heLLo','LL','ll') from dual;
select replace('heLLo','LL') from dual;--未設定第三個參數時表示用空字元串替換
數字函數
四舍五入
--第二個參數為正整數表示小數點後多少位，0表示取整，負數表示小數點前n+1位
select round(1234.5678,4) from dual;
select round(1234.5678,3) from dual;
select round(1234.5678,0) from dual;
select round(1234.5678,-1) from dual;
--對日期處理
select round(to_date('2003-10-15','yyyy-mm-dd'),'month')from dual;
select round(to_date('2003-10-16','yyyy-mm-dd'),'month')from dual;
select round(to_date('2003-6','yyyy-mm'),'year')from dual;
select round(to_date('2003-7','yyyy-mm'),'year')from dual;
轉換函數
to_char
日期類型轉字元串TO_CHAR(DATETIME)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
關于日期格式不同符号代表什麼意思，可以參考https://blog.csdn.net/qq_26565861/article/details/83110968
數字格式化TO_CHAR(NUMBER)
select to_char(1234,'9,9,9,9') from dual;
select to_char(1234.34,'9,9,9,9.99') from dual;
select to_char(1234.34,'9,9,9,9.$99') from dual;
更多數字格式化的參數，參考https://blog.csdn.net/jinlong5200/article/details/3135949
把NCLOB,CLOB,NCHAR轉換為VARCHAR2 TO_CHAR(CHARACTER)
to_date
參考https://blog.csdn.net/qq_26565861/article/details/83110968
to_number
可以将char和varchar2類型轉換成number類型
select to_number('$234234.4350','$999999.0000') from dual;
| 格式值 | 含義 |
|---|---|
| 9 | 代表一個數字 |
| 強迫0顯示 | |
| $ | 顯示美元符号 |
| L | 強制顯示一個當地的貨币符号 |
| . | 顯示一個小數點 |
| , | 顯示一個千位分隔符号 |
cast
cast可以進行資料類型轉換
文法：cast( 列名/值 as 資料類型 )
select cast(1 as varchar2(10)) from dual;
select cast('1' as number) from dual;
判斷函數
decode
用來判斷值是否相等，文法格式為：decode(列或值,值1,傳回值1,值2,傳回值2,…值n,傳回值n,預設值)
具體含義為：如果列或值等于第一個值，那麼傳回第一個傳回值，如果等于第二個值，那麼傳回第二個傳回值，否則傳回預設值
select decode('小米','華為','android','三星','android','蘋果','ios','其他') from dual;
select decode('小米','華為','android','三星','android','蘋果','ios') from dual;--如果沒有設定預設值參數,傳回null
nvl
文法：NVL(參數1,參數2 )
含義：如果參數1不為null，則傳回參數1，否則傳回參數2
select nvl('測試','預設值') from dual;
nvl2
文法：nvl2(參數1，參數2，參數3)
含義：如果參數1不為null，傳回參數2，否則傳回參數3
select nvl2('','參數2','參數3') from dual;
select nvl2('參數1','參數2','參數3') from dual;
nullif
文法：NULLIF (參數1，參數2)
含義：若兩個參數相等，傳回NULL；不相等，等傳回參數1
其他函數
ROW_NUMBER()OVER
一對多取多的一方最新的一條資料，分類（PARTITION BY）排序（ORDER BY ）
SELECT * FROM(
SELECT u.*
,ROW_NUMBER()OVER(PARTITION BY p.user_id ORDER BY p.flush_time desc)RN
FROM STS_LPATROL_APPUSER_T u
left JOIN STS_LPATROL_PHONEDEV_T p ON u.user_id=p.user_id)T where RN=1
pivot
行轉列：将多行資料轉換成一行資料的多個列
create table tb_student_grade(name varchar2(50),id varchar2(32),course varchar2(50),grade number);
insert into tb_student_grade(name,id,course,grade) values('cc','1','math',100);
insert into tb_student_grade(name,id,course,grade) values('cc','1','chinese',110);
insert into tb_student_grade(name,id,course,grade) values('cc','1','english',120);
insert into tb_student_grade(name,id,course,grade) values('cc','2','math',100);
insert into tb_student_grade(name,id,course,grade) values('cc','2','chinese',110);
insert into tb_student_grade(name,id,course,grade) values('cc','2','english',120);
insert into tb_student_grade(name,id,course,grade) values('lp','3','math',50);
insert into tb_student_grade(name,id,course,grade) values('lp','3','chinese',60);
insert into tb_student_grade(name,id,course,grade) values('lp','3','english',70);
commit;
select * from tb_student_grade pivot(max(grade) for course in('math' as 數學,'chinese' as 國文,'english' as 英語));
unpivot
列轉行：将一行資料的多個列轉換成多行資料
select * from tb_student_grade pivot(max(grade) for course in('math' as 數學,'chinese' as 國文,'english' as 英語)) unpivot(grade for course in(數學,國文,英語));
合并多行資料
wm_concat
用來将分組後的多行資料合并成一行
select t.deptno,wm_concat(t.ename) from emp t group by t.deptno;
ename拼接的順序不是按照ename來的
listagg
select t.deptno,listagg(t.ename,':') within group (order by t.ename) from emp t group by t.deptno;
ename拼接的順序可以按照某個字段來，拼接的字元也可以指定。是11g新增的函數
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)