oracle学习笔记三——查询之单行函数
三、单行函数
--什么是单行函数？
单行函数:function_name [(arg1, arg2,...)]
操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值
说明：这里会尽量给出每个函数的运行结果，以防止没说明白，可以对照命令和结果理解清楚
1.字符函数
（1）大小写控制函数（lower, upper, initcap)
SQL> select lower('HeLLO World')转小写,upper('HeLLO World')转大写,initcap('hello world') 首字母大写 from dual;
-->结果：
大小写控制函数
（2）字符控制函数（CONCAT，SUBSTR，LENGTH/LENGTHB，INSTR，LPAD|RPAD，TRIM，REPLACE）
--concat(a,b),拼接字符串a和b，在前面讲连接符“||”的时候说过了
SQL> select concat('Hello', ' World') from dual;
--substr(a,b,c) 从a中，第b位开始取,取c位
SQL> select substr('hello world',3,4) from dual;
-->结果：
从第3位开始截取4位（最后有个空值）
--substr(a,b) 从a中，第b位开始取，取右边的所有字符（默认指定了第三个参数到最后）
SQL> select substr('hello world',3) from dual;
-->结果：
从第3位开始取右边的所有字符
--length 字符数 lengthb 字节数
SQL> select length('Hello World') 字符数,lengthb('Hello World') 字节数 from dual;
-->结果：（英文的话，是一样的）
英文，字符数=字节数
SQL> select length('中国') 字符数,lengthb('中国') 字节数 from dual;
-->结果：（中文的话，字节说大于字符数，至于大多少，要看编码）
中文， 字符数<字节数
--instr: 在母串中，查找子串；如果找到，返回下标（从1开始）；否则返回0
SQL> select instr('Hello World','ll') from dual;
-->结果:
注意，只是返回起始下标(从1开始)，没有返回0
--lpad 左填充 rpad 右填充
SQL> select lpad('abcd',10,'*') 左填充,rpad('abcd',10,'*') 右填充 from dual;
-->结果：
左填充，右填充
--trim 去掉前后指定的字符（注意语法，参数里要用from）
SQL> select trim('H' from 'Hello WorldH') from dual;
-->结果：
注意，trim参数里要用from
--replace 替换
SQL> select replace('Hello World','l','*') from dual;
-->结果：（注意第二个参数，如果是lo，则结果是“Hel* World”）
replace 替换字符
2.数字函数
--ROUND：四舍五入（不仅可以对数字，也可以对日期，后面会说）
SQL> select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二,ROUND(45.926, 0) 三, ROUND(45.926, -1) 四,ROUND(45.926, -2) 五 from dual;
-->结果：
ROUND，可以自己琢磨参数，正负的含义
--TRUNC：截断（不仅可以对数字，也可以对日期，后面会说）
SQL> select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二,TRUNC(45.926, 0) 三,
TRUNC(45.926, -1) 四,TRUNC(45.926, -2) 五 from dual;
-->结果：
TRUNC，可以自己琢磨参数，正负的含义
--MOD：求余
SQL> select MOD(1600, 300) from dual;
-->结果：100
3.日期函数
--先来查一下系统的时间
SQL> select sysdate from dual;
-->结果：（这个格式之前说过了，也说了怎么改，参见一，基本查询）
格式：DD-MON-RR
注：日期的数学运算
1.在日期上加上或减去一个数字结果仍为日期。
2.两个日期相减返回日期之间相差的天数。
3.可以用数字除24来向日期中加上或减去小时。
--昨天 今天 明天
SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;
-->结果：（运行日期：2018-08-04）
对日期进行加减数值
注：日期的加法，只能是数字，你加上个另一个日期是个啥子？
减法可以是两个日期相减，返回天数
--计算员工的工龄: 天 星期 月 年
SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月, (sysdate-hiredate)/365 年 from emp;
-->结果：（这只是算个大概，运行日期：2018-08-04）
计算员工的工龄，大概计算
--MONTHS_BETWEEN ：计算两个日期之间的月份数，会自动考虑（28/29,30/31）
SQL> select ename,hiredate,(sysdate-hiredate)/30 方式一,MONTHS_BETWEEN(sysdate,hiredate) 方式二 from emp;
-->结果：（工龄：月份数，运行日期：2018-08-04）
MONTHS_BETWEEN ，大值在前返回正数，小值在前返回负数
--ADD_MONTHS：计算指定多少个月前(负数)/后(正数)是什么日期
SQL> select ADD_MONTHS(sysdate,-119) "119个月前", ADD_MONTHS(sysdate,119) "119个月后" from dual;
-->结果：（运行日期：2018-08-04）
ADD_MONTHS，负值向前计算，正值向后计算
--LAST_DAY：当前月的最后一天
SQL> select LAST_DAY(sysdate) from dual;
-->结果：
LAST_DAY，当前月的最后一天
--next_day：返回指定标识的下一个日期
SQL> select next_day(sysdate,'星期日') from dual;
-->结果：（运行日期：2018-08-04）
next_day，指定标识的下一个日期
注：
1.这里的第二个参数，中文状态只能是：星期一~星期日（不能是周一，礼拜一等）。英文状态，只能是monday~sunday
2.SQL> select * from v$nls_parameters可以查询当前Oracle数据库的参数（之前说过了）：
NLS_LANGUAGE --> SIMPLIFIED CHINESE 表示简体中文
--对日期进行四舍五入
SQL> select round(sysdate,'month'), round(sysdate,'year') from dual;
-->结果：（运行日期：2018-08-04）
round，也可以对日期使用
说明：
现在是2018-08-04，月未过半，故对month四舍五入是2018-08-01，而年已过半，故对year四舍五入是2019-01-01。
--对日期进行截断
SQL> select trunc(sysdate,'month'), trunc(sysdate,'year') from dual;
-->结果：（运行日期：2018-08-04）
trunc，也可以对日期使用
4.转换函数
--to_char：将字段，转换为指定格式的字符串，不限于日期
--格式化日期，变成字符串
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
-->结果：
to_char，可以格式化日期
--能不能直接将日期格式化为：2018-08-04 23:50:10 今天是 星期六，可以的
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;
-->结果：
to_char，在格式化字符的时候，可以添加其他字符
说明：
在格式化日期的时候，可以使用双引号向日期中添加字符
DD "of" MONTH --> 12 of OCTOBER
注：
1.格式:
1）必须包含在单引号中而且大小写敏感。
2）可以包含任意的有效的日期格式。
3）日期之间用逗号隔开。
2.日期格式的元素
常用的日期格式化元素
--格式化数字，变成字符串
--查询员工的薪水: 货币代码 两位小数 千位符
SQL> select to_char(sal,'L9,999.99') from emp;
-->结果：
to_char，也可以格式化数值
注：
在TO_CHAR 函数中经常对数值使用的几种格式:
常用的数值格式化元素
--TO_NUMBER(char[, 'format_model'])：将字符串转化为数值
SQL> select to_number('10000') from dual;
-->结果：10000
SQL> select to_number('$7,356.87', '$9,999.99') from dual;
-->结果：（ '$9,999.99' --> '$99,999.99'也可以，自己琢磨）
把格式匹配正确即可
--TO_DATE(char[, 'format_model'])：将字符转换成日期
SQL> select to_date('20180805 00:09:53', 'yyyymmdd hh24:mi:ss') from dual;
-->结果：
需要正确匹配日期格式
SQL> select to_date('05-8月-18') from dual;
-->结果：
省略，日期format的情况
说明：
如果想省略to_date的第二个参数，传入的字符串必须和Oracle数据库默认的格式（DD-MON-RR）一致
补充：关于隐式转换（不推荐，尽量显示转换）
使用上面的函数进行的转换都是显示转换，还有一种转换是隐式转换
要想完成隐式转换，则传入的值必须符合格式要求。
举例：
SQL> select * from emp where deptno='10';
-->结果：（Oracle会自动将’10‘转化为，数字10，因为deptno的类型是number）
隐式转换，‘10’-->10
5.通用函数（适用于任何数据类型，同时也适用于空值）
--滤空函数nvl,nvl2
--nvl(a,b)当a=null时，返回b; 否则返回自身
--nvl2(a,b,c) 当a=null时，返回c; 否则返回b
SQL> select ename, sal*12+nvl2(comm,comm,0) from emp;
-->结果：
使用滤空函数nvl2
如果不加滤空函数会怎样？
SQL> select ename, sal*12+comm from emp;
-->结果：（这显然不是我们想要的，回顾我们说的null的第一个说明，包含null的表达式都是空值）
未使用滤空函数
说明：
nvl2是nvl的增强，在Oracle中后面带2的都是不带2的增强，常见的还有如数据类型varchar2和varchar
--nullif(a,b) 当a=b时，返回null，否则返回a
SQL> select nullif('abc','abc') from dual;
-->结果：
nullif(a,b) 当a=b时，返回null
SQL> select nullif('abc','adbc') from dual;
-->结果：
nullif(a,b) 当a！=b时，返回a
--COALESCE (expr1, expr2, ..., exprn)： 返回第一个不为null的值
SQL> select comm,sal,COALESCE(comm,sal) from emp;
-->结果：
COALESCE (expr1, expr2, ..., exprn)： 返回第一个不为null的值
注：
1.COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。
2.如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
3.即：找第一个不为空的值。
--CASE 表达式（条件表达式，适用所有对SQL99标准实现的数据库）：即IF-THEN-ELSE 逻辑
--报表：涨后的工资（总裁1000 经理800 其他400）
SQL> select ename,job,sal 涨前薪水, case job when 'PRESIDENT' then sal+1000
when 'MANAGER' then sal+800
else sal+400
end 涨后薪水
from emp;
-->结果：(SQL的书写规范，和其他语言一样，都很重要)
使用case-when-else-end实现涨薪
注：
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
--decode函数：Oracle提供的一个简化的case-when-else-end实现，只在Oracle中能用
--报表：涨后的工资（总裁1000 经理800 其他400）
SQL> select ename,job,sal 涨前薪水, decode(job,'PRESIDENT',sal+1000,
'MANAGER',sal+800,
sal+400) 涨后薪水
from emp;
-->结果：和case-when-else-end逻辑实现的一样
注：
decode函数的参数说明：
DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
