單行函數(shù)
SQL函數(shù)：
sql函數(shù).PNG
兩種sql函數(shù)
兩種sql函數(shù).PNG
單行函數(shù)
- 操作數(shù)據(jù)對(duì)象
- 接受參數(shù)返回一個(gè)結(jié)果
- 只對(duì)一行進(jìn)行變換
- 每行返回一個(gè)結(jié)果
- 可以轉(zhuǎn)換數(shù)據(jù)類型
- 可以嵌套
- 參數(shù)可以是一列或一個(gè)值
單行函數(shù).PNG
一、字符函數(shù)
字符函數(shù).PNG
大小寫控制函數(shù)：改變字符的大小寫
大小寫控制函數(shù).PNG
--字符函數(shù)
select lower('Hello World') 轉(zhuǎn)小寫,upper('Hello World') 轉(zhuǎn)大寫,initcap('hello world') 首字母大寫 from dual;
--substr(a,b) 從a中，第b位開(kāi)始取
select substr('Hello World',3) 子串 from dual;
子串
---------
llo World
--substr(a,b,c) 從a中，第b位開(kāi)始取,取c位
select substr('Hello World',3,4) 子串 from dual;
子串
----
llo
--instr(a,b) 在a字符串中查找b，找到了，返回下標(biāo)（從1開(kāi)始），沒(méi)有找到，返回0
select instr('Hello World','ll') 位置 from dual;
位置
----------
3
--length 字符數(shù) lengthb 字節(jié)數(shù)
英文：
select length('Hello World') 字符,lengthb('Hello World') 字節(jié) from dual;
字符 字節(jié)
---------- ----------
11 11
中文：
select length('北京') 字符,lengthb('北京') 字節(jié) from dual
字符 字節(jié)
---------- ----------
2 4
--lpad 左填充 rpad 右填充
-- abcd ---> 10 位
select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;
第一個(gè)參數(shù)：對(duì)誰(shuí)進(jìn)行填充
第二個(gè)參數(shù)：填到多少位
第三個(gè)參數(shù)：填什么
左 右
---------- ----------
******abcd abcd******
--trim 去掉前后指定的字符
select trim('H' from 'Hello WorldH') from dual;
TRIM('H'FR
----------
ello World
--replace
select replace('Hello World','l','*') from dual;
REPLACE('HE
-----------
He**o Wor*d
二、數(shù)字函數(shù)：
round：四舍五入(第二個(gè)參數(shù)表示保留兩位小數(shù))
round(45.926,2)--->45.93
trunc:截?cái)?trunc(45.926,2)--->45.92
mod：求余
mod(1600,300)--->100
select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三,
2 round(45.926,-1) 四,round(45.926,-2) 五
3 from dual;
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.93 45.9 46 50 0
--截?cái)?select trunc(45.926,2) 一,trunc(45.926,1) 二,trunc(45.926,0) 三,
2 trunc(45.926,-1) 四,trunc(45.926,-2) 五
3* from dual
SQL> /
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.92 45.9 45 40 0
三、日期：
Oracle中的日期型數(shù)據(jù)（date）實(shí)際含有兩個(gè)值：日期和時(shí)間
默認(rèn)的日期格式是：DD-MON-RR
--查詢當(dāng)前時(shí)間
SQL> select sysdate from dual;
SYSDATE
---------
04-MAY-17
to_char():把某個(gè)東西，按照某個(gè)格式轉(zhuǎn)換成字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2017-05-04 09:19:58
日期的數(shù)學(xué)運(yùn)算：
- 在日期上加上或減去一個(gè)數(shù)字結(jié)果仍為日期
- 兩個(gè)日期相減返回日期之間相差的天數(shù)
- 可以用數(shù)字除24來(lái)向日期中加上或減去小時(shí)
例如：
--昨天 今天 明天
SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;
昨天 今天 明天
-------------- -------------- --------------
21-11月-15 22-11月-15 23-11月-15
--計(jì)算員工的工齡：天 星期 月 年
SQL> select ename,hiredate, (sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,
2 (sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年
3 from emp;
ENAME HIREDATE 天 星期 月 年
---------- -------------- ---------- ---------- ---------- ----------
SMITH 17-12月-80 12758.6314 1822.66163 425.287713 34.9551545
ALLEN 20-2月 -81 12693.6314 1813.37591 423.121046 34.7770723
WARD 22-2月 -81 12691.6314 1813.0902 423.05438 34.7715928
JONES 02-4月 -81 12652.6314 1807.51877 421.75438 34.6647435
MARTIN 28-9月 -81 12473.6314 1781.94734 415.787713 34.1743326
BLAKE 01-5月 -81 12623.6314 1803.37591 420.787713 34.5852915
CLARK 09-6月 -81 12584.6314 1797.80448 419.487713 34.4784422
SCOTT 19-4月 -87 10444.6314 1492.0902 348.15438 28.6154285
KING 17-11月-81 12423.6314 1774.80448 414.121046 34.0373463
TURNER 08-9月 -81 12493.6314 1784.80448 416.45438 34.2291271
ADAMS 23-5月 -87 10410.6314 1487.23306 347.021046 28.5222778
ENAME HIREDATE 天 星期 月 年
---------- -------------- ---------- ---------- ---------- ----------
JAMES 03-12月-81 12407.6314 1772.51877 413.587713 33.9935107
FORD 03-12月-81 12407.6314 1772.51877 413.587713 33.9935107
MILLER 23-1月 -82 12356.6314 1765.23306 411.887713 33.8537846
日期函數(shù)：
日期函數(shù).PNG
moths_between:
select ename,hiredate,(sysdate-hiredate)/30 一,months_between(sysdate,hiredate) 二 from emp;
ENAME HIREDATE 一 二
---------- -------------- ---------- ----------
SMITH 17-12月-80 425.287784 419.181726
ALLEN 20-2月 -81 423.121117 417.084952
WARD 22-2月 -81 423.054451 417
JONES 02-4月 -81 421.754451 415.665597
MARTIN 28-9月 -81 415.787784 409.826888
BLAKE 01-5月 -81 420.787784 414.697855
CLARK 09-6月 -81 419.487784 413.439791
SCOTT 19-4月 -87 348.154451 343.11721
KING 17-11月-81 414.121117 408.181726
TURNER 08-9月 -81 416.454451 410.472049
ADAMS 23-5月 -87 347.021117 341.988178
ENAME HIREDATE 一 二
---------- -------------- ---------- ----------
JAMES 03-12月-81 413.587784 407.633339
FORD 03-12月-81 413.587784 407.633339
MILLER 23-1月 -82 411.887784 405.988178
--56 個(gè)月后
SQL> select add_months(sysdate,56) from dual;
ADD_MONTHS(SYS
--------------
22-7月 -20
select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
30-11月-15
--next_day
SQL> --下一個(gè)星期日
SQL> select next_day(sysdate,'星期日') from dual;
NEXT_DAY(SYSDA
--------------
29-11月-15
SQL> --下一個(gè)星期一
SQL> select next_day(sysdate,'星期一') from dual;
NEXT_DAY(SYSDA
--------------
23-11月-15
next_day的應(yīng)用：
每個(gè)星期一自動(dòng)備份數(shù)據(jù)
1. 分布式數(shù)據(jù)庫(kù)
2. 快照 觸發(fā)器
日期函數(shù)：
日期函數(shù)2.PNG
select round(sysdate,'month'),round(sysdate,'year') from dual;
ROUND(SYSDATE, ROUND(SYSDATE,
-------------- --------------
01-12月-15 01-1月 -16
轉(zhuǎn)換函數(shù)：
數(shù)據(jù)類型轉(zhuǎn)換：
隱式
顯示
隱式數(shù)據(jù)類型轉(zhuǎn)換：（oracle自動(dòng)完成）
隱式數(shù)據(jù)類型轉(zhuǎn)換.PNG
select * from emp where hiredate='17-11月-81';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------- --------- ---------- -------------- ----- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
顯示數(shù)據(jù)類型轉(zhuǎn)換：
顯式數(shù)據(jù)類型轉(zhuǎn)換.PNG
to_char函數(shù)對(duì)日期的轉(zhuǎn)換：
to_char(date,'format_model')
格式：
必須包含在單引號(hào)中而且大小寫敏感
可以包含任意的有效的日期格式
日期之間用逗號(hào)隔開(kāi)
日期格式的元素：
日期格式的元素.PNG
--2015-11-22 15:32:12今天是星期日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI
----------------------------------
2015-11-22 15:33:55今天是星期日
to_char函數(shù)對(duì)數(shù)字的轉(zhuǎn)換：
to_char函數(shù)中經(jīng)常使用的幾種格式
格式.PNG
--查詢員工薪水：兩位小數(shù) 千位符 貨幣代碼
select to_char(sal,'L9,999.99') from emp;
TO_CHAR(SAL,'L9,999
-------------------
￥800.00
￥1,600.00
￥1,250.00
￥2,975.00
￥1,250.00
￥2,850.00
￥2,450.00
￥3,000.00
￥5,000.00
￥1,500.00
￥1,100.00
TO_CHAR(SAL,'L9,999
-------------------
￥950.00
￥3,000.00
￥1,300.00
通用函數(shù)：
這些函數(shù)適用于任何數(shù)據(jù)類型，同時(shí)也適用于空值
nvl(expr1,expr2)
nvl(expr1,expr2,expr3)
nullif(expr1,expr2)
coalesce(expr1,expr2,...,exprn)
--nvl2(a,b,c) 當(dāng)a=null的時(shí)候，返回c；否則返回b
select sal*12+nvl2(comm,comm,0) from emp;
--nullif(a,b) 當(dāng)a=b的時(shí)候，返回null；否則返回a
select nullif('abc','abc') 值 from dual;
值
---
select nullif('abc','abcd') 值 from dual;
值
---
abc
--coalesce 從左到右找到第一個(gè)不為null的值
select comm,sal,coalesce(comm,sal) "第一個(gè)不為null的值" from emp;
COMM SAL 第一個(gè)不為null的值
800 800
300 1600 300
500 1250 500
2975 2975
1400 1250 1400
2850 2850
2450 2450
3000 3000
5000 5000
0 1500 0
1100 1100
COMM SAL 第一個(gè)不為null的值
950 950
3000 3000
1300 1300
條件表達(dá)式：
在sql語(yǔ)句中使用if-then-else邏輯
使用兩種方法：
1、case表達(dá)式：sql99的語(yǔ)法，類似basic，比較繁瑣
2、decode函數(shù)：Oracle自己的語(yǔ)法，類似java，比較簡(jiǎn)潔
語(yǔ)法：
case：
case expr when comparison_expr1 then return_expr1
[when comparison_expr2 then return_expr2
when comparison_exprn then return_exprn
else else_expr]
end
SQL> select ename,job,sal 漲前,
2 case job when 'PRESIDENT' then sal+1000
3 when 'MANAGER' then sal+800
4 else sal+400
5 end 漲后
6 from emp;
或者：
case when sal<3000 then ****
when sal>=3000 and sal<6000 then ****
else ****
end
decode:
decode(col|expression,search1,result1
[,search2,result2,...,]
[,default])
SQL> select ename,job,sal 漲前,
2 decode(job,'PRESIDENT',sal+1000,
3 'MANAGER',sal+800,
4 sal+400) 漲后
5 from emp;