oracle--單行函數和多行函數
- 2019 年 10 月 10 日
- 筆記
單行函數
1、字元函數
函 數 | 功 能 | 示 例 | 結 果 |
|---|---|---|---|
INITCAP (char) | 首字母大寫 | initcap ('hello') | Hello |
LOWER (char) | 轉換為小寫 | lower ('FUN') | fun |
UPPER (char) | 轉換為大寫 | upper ('sun') | SUN |
LTRIM (char, set) | 左剪裁 | ltrim ('xyzadams', 'xyz') | adams |
RTRIM (char, set) | 右剪裁 | rtrim ('xyzadams', 'ams') | xyzad |
TRANSLATE (char, from, to) | 按字元翻譯 | translate ('jack', 'abcd', '1234') | j13k |
REPLACE (char, search_str, replace_str) | 字元串替換 | replace ('jack and jue', 'j', 'bl') | black and blue |
INSTR (char, substr[, pos]) | 查找子串位置 | instr ('worldwide', 'd') | 5 |
SUBSTR (char, pos, len) | 取子字元串 | substr ('abcdefg',3,2) | cd |
CONCAT (char1, char2) | 連接字元串 | concat ('Hello', 'world') | Helloworld |
2、數值函數
函 數 | 功 能 | 示 例 | 結 果 |
|---|---|---|---|
ABS(n) | 取絕對值 | abs(-15) | 15 |
CEIL(n ) | 向上取整 | ceil(44.778) | 45 |
SIN(n) | 正弦 | sin(1.571) | .999999979 |
COS(n) | 餘弦 | cos(0) | 1 |
SIGN(n) | 取符號 | sign(-32) | -1 |
FLOOR(n) | 向下取整 | floor(100.2) | 100 |
POWER(m,n ) | m的n次冪 | power(4,2) | 16 |
MOD(m,n) | 取餘數 | mod(10,3) | 1 |
ROUND(m,n) | 四捨五入 | round(100.256,2) | 100.26 |
TRUNC(m,n) | 截斷 | trunk(100.256,2) | 100.25 |
SQRT(n) | 平方根 | sqrt(4) | 2 |
3、日期函數
函 數 | 功 能 | 示 例 | 結 果 |
|---|---|---|---|
MONTHS_BETWEEN | 返回兩個日期間的月份 | months_between ('04-11月-05','11-1月-01') | 57.7741935 |
ADD_MONTHS | 返回把月份數加到日期上的新日期 | add_months('06-2月-03',1) add_months('06-2月-03',-1) | 06-3月-03 06-1月-03 |
NEXT_DAY | 返回指定日期後的星期對應的新日期 | next_day('06-2月-03','星期一') | 10-2月-03 |
LAST_DAY | 返回指定日期所在的月的最後一天 | last_day('06-2月-03') | 28-2月-03 |
ROUND | 按指定格式對日期進行四捨五入 | round(to_date('13-2月-03'),'YEAR') round(to_date('13-2月-03'),'MONTH') round(to_date('13-2月-03'),'DAY') | 01-1月-03 01-2月-03 16-2月-03 |
TRUNC | 對日期按指定方式進行截斷 | trunc(to_date('06-2月-03'),'YEAR') trunc(to_date('06-2月-03'),'MONTH') trunc(to_date('06-2月-03'),'DAY') | 01-1月-03 01-2月-03 02-2月-03 |
4、轉換函數
日期格式元素 | 含義 |
|---|---|
YYYY、YY | 代表四位、兩位數字的年份 |
MM | 用數字表示的月份 |
MON | 月份的縮寫、對中文月份來說就是全稱 |
DD | 數字表示的日 |
DY | 星期的縮寫，對中文的星期來說就是全稱 |
HH24，HH12 | 12小時或者24小時進位下的時間 |
MI | 分鐘數 |
SS | 秒數 |
數字格式元素 | 含義 |
|---|---|
9 | 代表一位數字，如果該位沒有數字則不進行顯示，但對於小數點後面的部分仍會強制顯示 |
0 | 代表一位數字，如果該位沒有數字則強制顯示0 |
$ | 顯示美元符號 |
L | 顯示本地貨幣符號 |
. | 顯示小數點 |
, | 顯示千分位符號 |
其他函數
nvl() nvl2() decode()
函 數 | 功 能 |
|---|---|
NVL(EXP1, EXP2) | 如果exp1的值為null，則返回exp2的值，否則返回exp1的值 |
NVL2(EXP1, EXP2, EXP3) | 如果exp1的值為null，則返回exp2的值，否則返回exp3的值 |
DECODE(VALUE,IF1,THEN1, IF2,THEN2,……,ELSE) | 如果value的值為if1，則返回then1的值，如果value的值為if2，則返回then2的值，……，否則返回else值 |
多行函數
單行函數學習 --查詢工作為SALESMAN，MANAGER並且工資大於2500的員工資訊 --1、使用小括弧提升where篩選條件的執行優先順序別 --2、and的優先順序別高於or select * from emp select * from emp where job='SALESMAN' or job='MANAGER' and sal>2500 select * from emp where (job='SALESMAN' or job='MANAGER') and sal>2500 ----------------------------------------------------------------------------------------------------------- 使用函數 單行函數 多行函數 轉換函數 其他函數 --單行函數學習：不改變原始數據，只改變結果 ---1、字元函數 select * from emp; select INITCAP(ename) from emp;--initcap函數將首字母大寫 select lower(ename)from emp;--lower 字母小寫 select replace(ename,'S','M') from emp;--replace 替換 --2、數值函數--Math ----偽表 dual select * from dual select abs(-3) 絕對值，ceil(3.1415926)向上取整,floor(3.1415926)向下取整,power(2,3)冪,round(3.4)四捨五入 from dual --3、日期函數 select months_between('13-12月-2016','13-10月-2016') from dual--months_between兩個日期之間的月份數 多行函數學習 多行函數 ： max: max(欄位名) 返回此欄位的最大值 min：min(欄位名) 返回此欄位的最小值 avg：avg(欄位名) 返回平均值 sum：sum（欄位名） 返回欄位的和 count:count --count(*)，用來查詢表中有多少條記錄 --count（欄位名），用來查詢某個欄位有值的個數 --count(distinct 欄位名),可以先去除重複再計數。 注意： --多行函數不能和普通欄位直接出現在查詢語句中，除非group by --多行函數和單行函數不能直接出現在查詢語句中，除非group by 查看員工的最高工資 select max(sal),ename from emp--多行函數不能和普通欄位直接出現在查詢語句中，除非group by select max(sal)，lower(ename) from emp--多行函數和單行函數不能直接出現在查詢語句中，除非group by select ename from emp 查看員工的最低工資 select min(sal) from emp 查看員工的平均工資 select avg(sal) from emp 查看所有的員工工資之和 select sum(sal) from emp 查詢公司有多少員工 select * from emp select count(*) from emp--使用count(*)來查看一張表中有多少條記錄 查詢有津貼的員工人數 select count(comm) from emp--使用count(欄位名),查詢該欄位有值的記錄數 select count(ename) from emp 查詢公司有多少工作種類 select count(job) from emp select count(distinct job) from emp 轉換函數學習 轉換函數:在轉換的時候改變的是數據的類型，數據內容不會改變，可以指定格式。 1、to_number：將數字字元轉換為數字類型的數值，to_number(數字字元) 2、to_char：將數字轉換為字元類型，將日期轉換為字元類型 to_char(數字/日期) 3、to_date：將字元類型的日期轉換為日期類型： to_date(char) -------------------------------------------------------------------------------------- number-->char 轉換的時候使用的是默認格式， select to_char(123) from dual--將數字轉換為字元類型，使用to_char(number) select sal, to_char(sal) from emp number-->char 使用指定的格式 to_char(number,'格式')，格式： $代表美元符，9程式碼數字佔位。例如，L999,999,999表示三位一組的顯示方式L代表人民幣符號，0代表保留數字個數，不足使用0補充。 select to_char(sal),to_char(sal,'L999,999,999') from emp select to_char(sal),to_char(sal,'L0000.00') from emp char--->number to_number(數字字元) select to_number('123') from dual char--->date，轉換的字元必須是日期格式的字元串，默認格式 dd-mm-yyyy 注意：因為字元串有很多，所以在字元轉換為日期的時候，需要指定格式，因為日期是具備一定格式的字元組合。 字元轉換為日期的時候，指定的格式為字元的日期順序，無需指定間隔符。 yyyy：表示年 mm:表示月 dd表示日 select to_date('05-12月-2016') from dual; select to_date('2016-05-12','yyyy-mm-dd') from dual--使用指定的格式將指定的日期字元串轉換為日期 select to_date('12-05-2016','mm/dd/yyyy') from dual date--->char 注意：因為日期本身就具備一定的格式在不是指定格式的情況下會默認使用dd-mm-yyyy格式顯示數據 指定的格式會作為日期轉換為字元串類型的顯示格式存在。例如： yyyy-mm-dd' 『2016-12-05』 yyyy/mm/dd' '2016/12/05' yyyy"年"mm"月"dd"日"' 2016年12月05日 select hiredate,to_char(hiredate) from emp--日期轉換為字元的時候，不指定格式使用默認格式：dd-mm-yyyy select hiredate,to_char(hiredate,'yyyy-mm-dd') from emp;--使用指定格式將日期轉換為字元串類型 select hiredate,to_char(hiredate,'yyyy/mm/dd') from emp;--使用指定格式將日期轉換為字元串類型 select hiredate,to_char(hiredate,'yyyy"年"mm"月"dd"日"') from emp;--使用指定格式將日期轉換為字元串類型 查詢入職日期在81年10月20日後的員工資訊 第一種：自動轉型 select * from emp where hiredate>'20-10月-1981' 第二種：將日期轉換為字元串 select * from emp where to_char(hiredate,'yyyy-mm-dd')>'1981-10-20' 第三種： select * from emp where hiredate>to_date('1981-10-20','yyyy/mm/dd') 其他函數： 單行函數：lower 多行函數:min max sum avg count 轉換函數:to_number to_char to_date ----------------------------------------------------------------------------------------- 其他函數： 1、nvl():nvl(欄位名，執行)--相當於java中的if條件判斷 2、nvl2():nvl2(欄位名，值，值)--相當於java中的If(){}else{}判斷 3、decode():decode(欄位名，條件1，執行內容1，條件2，執行內容2，條件3，執行內容3，默認執行內容) 相當於java中if(){}else if(){}else if(){}...else{} 查詢所有員工的月薪及姓名和工作 select * from emp select ename,sal 基本工資,comm 績效,sal+comm 月薪 from emp select ename,sal 基本工資,comm 績效,sal+nvl(comm,0)月薪 from emp 查詢所有員工的月薪及姓名和工作 select ename,sal 基本工資,comm 績效,nvl2(comm,sal+comm,sal)月薪 from emp 顯示員工的職稱 select ename,job,decode(job,'MANAGER','經理','SALESMAN','銷售人員','普通員工') from emp單行函數分類 字元函數 數值函數 日期函數 轉換函數 通用函數 多行函數 sum() avg() 僅適用數值型 count() max() min() 適用任何類型數據