单行函数
SQL函数：
sql函数.PNG
两种sql函数
两种sql函数.PNG
单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以转换数据类型
- 可以嵌套
- 参数可以是一列或一个值
单行函数.PNG
一、字符函数
字符函数.PNG
大小写控制函数：改变字符的大小写
大小写控制函数.PNG
--字符函数
select lower('Hello World') 转小写,upper('Hello World') 转大写,initcap('hello world') 首字母大写 from dual;
--substr(a,b) 从a中，第b位开始取
select substr('Hello World',3) 子串 from dual;
子串
---------
llo World
--substr(a,b,c) 从a中，第b位开始取,取c位
select substr('Hello World',3,4) 子串 from dual;
子串
----
llo
--instr(a,b) 在a字符串中查找b，找到了，返回下标（从1开始），没有找到，返回0
select instr('Hello World','ll') 位置 from dual;
位置
----------
3
--length 字符数 lengthb 字节数
英文：
select length('Hello World') 字符,lengthb('Hello World') 字节 from dual;
字符 字节
---------- ----------
11 11
中文：
select length('北京') 字符,lengthb('北京') 字节 from dual
字符 字节
---------- ----------
2 4
--lpad 左填充 rpad 右填充
-- abcd ---> 10 位
select lpad('abcd',10,'*') 左,rpad('abcd',10,'*') 右 from dual;
第一个参数：对谁进行填充
第二个参数：填到多少位
第三个参数：填什么
左 右
---------- ----------
******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
二、数字函数：
round：四舍五入(第二个参数表示保留两位小数)
round(45.926,2)--->45.93
trunc:截断
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
--截断
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中的日期型数据（date）实际含有两个值：日期和时间
默认的日期格式是：DD-MON-RR
--查询当前时间
SQL> select sysdate from dual;
SYSDATE
---------
04-MAY-17
to_char():把某个东西，按照某个格式转换成字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2017-05-04 09:19:58
日期的数学运算：
- 在日期上加上或减去一个数字结果仍为日期
- 两个日期相减返回日期之间相差的天数
- 可以用数字除24来向日期中加上或减去小时
例如：
--昨天 今天 明天
SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;
昨天 今天 明天
-------------- -------------- --------------
21-11月-15 22-11月-15 23-11月-15
--计算员工的工龄：天 星期 月 年
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
日期函数：
日期函数.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 个月后
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> --下一个星期日
SQL> select next_day(sysdate,'星期日') from dual;
NEXT_DAY(SYSDA
--------------
29-11月-15
SQL> --下一个星期一
SQL> select next_day(sysdate,'星期一') from dual;
NEXT_DAY(SYSDA
--------------
23-11月-15
next_day的应用：
每个星期一自动备份数据
1. 分布式数据库
2. 快照 触发器
日期函数：
日期函数2.PNG
select round(sysdate,'month'),round(sysdate,'year') from dual;
ROUND(SYSDATE, ROUND(SYSDATE,
-------------- --------------
01-12月-15 01-1月 -16
转换函数：
数据类型转换：
隐式
显示
隐式数据类型转换：（oracle自动完成）
隐式数据类型转换.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
显示数据类型转换：
显式数据类型转换.PNG
to_char函数对日期的转换：
to_char(date,'format_model')
格式：
必须包含在单引号中而且大小写敏感
可以包含任意的有效的日期格式
日期之间用逗号隔开
日期格式的元素：
日期格式的元素.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函数对数字的转换：
to_char函数中经常使用的几种格式
格式.PNG
--查询员工薪水：两位小数 千位符 货币代码
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
通用函数：
这些函数适用于任何数据类型，同时也适用于空值
nvl(expr1,expr2)
nvl(expr1,expr2,expr3)
nullif(expr1,expr2)
coalesce(expr1,expr2,...,exprn)
--nvl2(a,b,c) 当a=null的时候，返回c；否则返回b
select sal*12+nvl2(comm,comm,0) from emp;
--nullif(a,b) 当a=b的时候，返回null；否则返回a
select nullif('abc','abc') 值 from dual;
值
---
select nullif('abc','abcd') 值 from dual;
值
---
abc
--coalesce 从左到右找到第一个不为null的值
select comm,sal,coalesce(comm,sal) "第一个不为null的值" from emp;
COMM SAL 第一个不为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 第一个不为null的值
950 950
3000 3000
1300 1300
条件表达式：
在sql语句中使用if-then-else逻辑
使用两种方法：
1、case表达式：sql99的语法，类似basic，比较繁琐
2、decode函数：Oracle自己的语法，类似java，比较简洁
语法：
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;