ORACLE--函数1（字符串函数、数值型函数、日期函数、转换函数）

目的：为了辅助我们在执行某些sql时简化我们的操作，为了避免多次重复编写同样功能的代码，提高代码的复用性。
JAVA能够解决的问题，就不要在数据库中编写代码。

1 字符串函数

1.1 CONCAT()连接字符串

想要把两个字段的值作为一个字段去显示，直接把两个字段名传入。连接姓名和薪水用逗号分隔开，可以以别名命名，采用层层嵌套方法。

SELECT CONCAT (CONCAT (ename,','), sal ) 信息 FROM emp;
若连接多个字符串，直接使用||更为方便
SELECT ename||','||sal 信息 FROM emp ;

1.2 UPPER,LOWER,INITCAP

将字符串转换为全大写，全小写，以及首字母大写

--对某一列的字段值进行转换
SELECT UPPER(ename) FROM dual;
--对字符串 ’helloworld’ 的大小写转换
SELECT UPPER ('helloworld') ,LOWER ('HELLOWORLD') ,INITCAP ('helloworld')  FROM dual;

——对INITCAP而言，可以使用空格隔开多个单词，那么每个单词的首字母都大写,只保证首字母大写

SELECT UPPER ('hello world'), LOWER ( 'HELLO WORLD') ,INITCAP ('HELLO WORLD')  FROM  dual;

1.3 TRIM,LTRIM ,RTRIM去裁剪字符串

（1）TRIM无参数时去除前后的空格，中间的空格无法去除
SELECT TRIM (’ AA AA ') FROM dual;
–结果：AA AA
（2）TRIM(c2 FROM c1)：从c1里面把两端的c2去掉，去除当前字符串两边的指定字符。c2是一个长字符串，c1是截取集只能有一个字符。TRIM 作用就是只有在长字符串(c2)两边有该字符（c1）才能删除（c2两边有几个c1就可以删除几个）。

SELECT  TRIM  ('a'  FROM  'aaabachabcaa' )  FROM  dual;
—结果：bchabc
SELECT  TRIM  ('a'  FROM  'abchabce' )  FROM  dual;
—结果：bchabce
SELECT  TRIM  ('b'  FROM  'abchbcbe' )  FROM  dual;
—结果：abchbcbe

（3）LTRIM去除左边，RTRIM去除右边，左或右两边可以同时删除多个字符（这里“多个”有两个含义：
1.去重字符串可以是多个
2.可以删除原字符串多个字母
格式：LTRIM(参数1，参数2)
RTRIM(参数1，参数2)
从字符串参数1左侧(右侧)开始去除与参数2字符集中其中单个字符能匹配的字符，就可以去除。直到在参数1中找不到与参数2其中任意一个字符，就停止剔除（tichu）动作。

SELECT  LTRIM ('eeeeliteeeee','e') FROM dual; 
—结果：从左边开始，碰到t结束，liteeeee
SELECT  RTRIM ('eeeliteeeee','e')FROM dual; 
—结果：从右边开始，碰到t结束eeelit，
SELECT  LTRIM ('esreserserserelitesesesees','esr')FROM dual;
—结果：从左边开始，凡是碰到esr其中一个字符，就剔出去，直至到某个字符不能删去，就停止。结果：litesesesees

1.4 LENGTH() 字符串的长度

求字符串的长度，空格也算一个长度

--查询名字字符串的长度
SELECT ename,LENGTH(ename) FROM emp;
--字符串长度空格也算
SELECT LENGTH('lgx wlk') FROM dual;--结果为7

1.5 REPLACE(参数1，参数2，参数3): 替换

参数1：被替换的字符串
参数2：要替换哪个字符串
参数3：要替换成哪个

SELECT REPLACE ('九点是下课时间','九点','十点半') FROM dual;
—结果：十点半是下课时间

1.6 SUBSTR 截取字符串

字符串下标从1开始

SELECT SUBSTR ('thinking in java',13,4)FROM dual;
——结果：java
--显示所有员工的姓名的第一个字 
  select substr(ename,0,1) from emp; 

–如果第三个参数不指定截取长度，就是截取到末尾，如果指定的长度超过实际可以截取的内容 那么也是截取到末尾

SELECT SUBSTR ('thinking in java',10)FROM dual;

–截取的位置可以是负数，表示从倒数第几个字符串开始截取

SELECT SUBSTR ('thinking in java',-4,4)FROM dual;
--结果java

1.7 INSTR(参数1，参数2,n,m)

查找第二个字符串在第一个字符串出现的位置.n从第几个字符开始搜索，m为第几次出现，如果找不到则返回0
–如果n，m不写，那么默认1
INSTR(参数1，参数2) ：查找第二个字符串在第一个字符串出现的位置

--显示emp表中姓名没有'L'字的员工的详细信息或含有'SM'字的员工信息
SELECT * FROM emp WHERE INSTR(ename,'L')=0 OR INSTR(ename,'SM')>0;
SELECT  INSTR ('thinking in java','in',4,2) FROM dual;
—结果：10

2 数值型函数

2.1 ABS() 取绝对值

SELECT ABS(-2.5) FROM dual; --结果：2.5

2.2 ROUND(n,m) 四舍五入

m代表保留位（
正数代表保留小数位，0代表个位
负数代表保留整数位-1代表十位，-2代表百位
）

SELECT ROUND(46.678,2)FROM dual	;	——46.68
SELECT ROUND(46.678,0)FROM dual;	——47
SELECT ROUND(45.678,-1)FROM dual;	——50
SELECT ROUND(45.678,-2)FROM dual;	——0
SELECT ROUND(55.678,-2)FROM dual;	——100

2.3 TRUNC(n,m) 截取数字

不遵循四舍五入，m超过了本身的长度，则保留原来的数据

SELECT TRUNC(46.678,2)FROM dual;	——46.67
SELECT TRUNC(46.678,0)FROM dual;	——46
SELECT TRUNC(46.678,-1)FROM dual;	——40
SELECT TRUNC(46.678,-2)FROM dual;	——0

2.4 MOD(n,m) 求余数

如果除数为0那么结果就是被除数。取余数运算：a mod b = c表示整数a除以整数b所得余数为c，如：7÷3 = 2 ······1。

SELECT ename,sal,MOD(sal,1000)FROM emp;
SELECT MOD(1,2)FROM dual;		——1

2.5 向上取整CEIL和向下取整FLOOR

CEIL(n)：大于n这个数的最小整数(向上取整)
FLOOR(n):小于这个数的最大整数（向下取整）

SELECT CEIL(45.678) FROM dual;			——46
SELECT FLOOR(45.678) FROM dual;		——45

2.6 POWER(n,m)幂的运算

SELECT POWER(2,3) FROM dual;		——8	

3 日期函数

DATE 时间类型，最多精确到秒
TIMESTAMP ：时间戳，可以精确到秒以下的单位

（1）SYSDATE :	当前日期的DATE类型的时间
SELECT SYSDATE FROM dual;
（2）SYSTIMESTAMP：返回的一个表示当前系统时间的TIMESTAMP类型的值
SELECT SYSTIMESTAMP FROM dual;

3.1 TO_DATE 字符串转成日期格式

SELECT TO_DATE ('2019-01-21 20:38:00','YYYY-MM-DD HH24:MI:SS')  FROM dual;

字符串和日期格式需要一一相对应，模板日期中只能够包含英文数字或者符号，如果需要有其他的，比如中文，那么这些符号必须要
使用双引号扩上。

SELECT TO_DATE ('2019年01月21日 20:38:00','YYYY"年"MM"月"DD"日" HH24:MI:SS')FROM dual;

3.2 日期加减

——日期可以与一个数值进行加减法，这相当于加减指定的天数。
——两个日期之间可以进行减法，差代表相差多少天

--查询emp表每个员工入职多少天？
SELECT ename,TRUNC(SYSDATE-hiredate) FROM emp;
--查看自己活了多少天（SYSDATE是日期格式，要将后面的字符串通过TO_DATE转为日期格式）
SELECT SYSDATE -TO _DATE ('2001 -08 -18 ',' YYYY -MM -DD ') FROM dual;
结果：

3.3 TO_CHAR:日期格式转成字符串

SELECT TO_CHAR (SYSDATE, 'YYYY -MM -DD HH12 :MI :SS' ) FROM dual;

从data转成字符串，YY和RR没有区别
从字符串转成日期：
RR会自动决定是否是本世纪，由系统时间和用户时间来决定。无论是RR还是RRRR，不存在格式前后不对用补0现象。

SELECT TO_DATE('1994-08-18','RRRR-MM-DD') FROM dual;--1994/8/18
SELECT TO_DATE('1994-08-18','RR-MM-DD') FROM dual;--1994/8/18
SELECT TO_DATE('94-08-18','RRRR-MM-DD') FROM dual;--1994/8/18
SELECT TO_DATE('94-08-18','RR-MM-DD') FROM dual;--1994/8/18

YY就是默认本世纪,YYYY要看对应字符串，若日期格式与字符串之间一一对应就是本世纪，格式前后要不对应就补0。

SELECT TO_DATE('1994-08-18','YYYY-MM-DD') FROM dual;--1994/8/18
SELECT TO_DATE('1994-08-18','YY-MM-DD') FROM dual;--1994/8/18
SELECT TO_DATE('01-08-18','YYYY-MM-DD') FROM dual;--0001/8/18
SELECT TO_DATE('94-08-18','YY-MM-DD') FROM dual;--2094/8/18

3.4 LAST_DAY(date)月底日期

返回给定日期所在月的月底时间

--找出每个月倒数第三天受雇的员工（如：2009-5-29） 
  select * from emp where last_day(hiredate)-2=hiredate; 

3.5 ADD_MONTHS(date,i)累加月份

如果i是正数，那么就是加上指定的月。
如果i是负数，那么就是减去指定的月。

SELECT ADD_MONTHS(SYSDATE,3)FROM dual;
--2019/1/31

3.6 MONTHS_BETWEEN(date1,date2)返回月差

计算两个日期之间相差几个月，date1-date2

--查看emp表的员工到至今入职了多少月
SELECT ename ,MONTHS _ BETWEEN ( SYSDATE, hiredate ) FROM emp;

SYSDATE和 hiredate本身就是日期格式可以直接相减，如果得到的是字符串就要进行TO_DATE进行转换。

SELECT MONTHS_BETWEEN (TO_DATE ('2018-01-01','YYYY-MM-DD'),TO_DATE('2017-01-01','YYYY-MM-DD')) FROM dual;
--结果：12

3.7 NEXT_DAY(date,i) 紧邻的某一天

表示给定日期的明天开始的一周之内的指定星期几的日期，i只能取值1-7。同java一样 1：星期天 7：星期六

SELECT NEXT_DAY(SYSDATE,4)FROM dual; 
--2019/1/27      

3.8 LEAST和GREATEST日期比大小

LEAST(a1，a2，a3…)：求小值
GREATEST(a1，a2，a3…)：最大值
日期越晚越大，越早就越小

SELECT LEAST(SYSDATE,TO_DATE('2008-8-8','YYYY-MM-DD')) FROM dual;-
-2008-8-8
SELECT GREATEST (SYSDATE,TO_DATE('2008-8-8','YYYY-MM -DD' ))  FROM dual; 
--2019/1/21   

3.9 EXTRACT(YEAR/MONTH/DAY FROM date)提取时间分量

提取给定日期中指定时间分量的值，使用EXTRACT只能获得DATE类型里面的年月日，若想获取时分秒，要使用时间戳类型。

--从当前时间获取年
SELECT EXTRACT(DAY  FROM  SYSDATE) FROM dual;--21
--查询emp表哪些员工是1981年入职的
SELECT * FROM emp WHERE EXTRACT(YEAR FROM hiredate) =1980;
--另一种写法
SELECT * FROM emp WHERE  TO_CHAR(hiredate,'YYYY')=1980;

4 空值函数NULL

涉及NULL值的运算操作，那么NULL和任何数字运算结果都还为NULL，NULL与字符串进行拼接等于什么事情都没干。
数字运算表达式
–查看emp表中每个员工的总收入（奖金+工资）
SELECT ename,sal,comm,sal+comm FROM emp;
上述查询会出现问题，当某个员工奖金comm为NULL时，所得结果sal+comm结果也为NULL。
解决方法：

4.1 NVL(arg1，arg2) 空值转换函数

将null值替换成一个非null的值。
当arg1不为null时，则返回arg1本身
当arg1为null时，函数返回arg2的值

--查询emp查看每个员工的收入
SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp;

NVL2(arg1,arg2,arg3):根据一个值是否为null 来返回两个不同的结果。
当arg1不为null，则函数返回agr2
当agr1为null，则函数返回arg3

--查询emp表中每个人的奖金情况，如果有奖金，显示“有奖金”,如果没有奖金，显示“没有奖金”
SELECT ename,sal,comm,NVL2(comm,'有奖金','没有奖金')FROM emp ;
--NVL2相当于NVL的2.0版本，NVL2可以实现NVL写法
SELECT ename,sal,comm,NVL2(comm,sal+comm,sal) FROM emp;
SELECT ename,sal,comm,sal+NVL2(comm,comm,0)FROM emp;

4.2 DISTINCT 去重

对结果集中指定字段值重复的记录进行去重复行

--查看emp表中员工职位有哪几种
SELECT DISTINCT job FROM emp;

–下面这条查询语句有问题，因为ename有多条数据，而job去重只有六条数据，前后不对应，结果没有办法出来。
SELECT ename,DISTINCT job FROM emp;

——对多个字段进行去重，是对这些字段值的组合进行去重
SELECT DISTINCT job,deptno FROM emp;

5 转换函数

5.1 TO_CHAR数值格式转换

（1）TO_CHAR,（999）转换成指定的格式，补全小数位

SELECT TO_CHAR(11.11,'99.999999') FROM DUAL;	
--11.110000
SELECT TO_CHAR(11.11,'999.9999') FROM DUAL;		
--11.1100

（2）TO_CHAR,（000）格式转换成指定的格式，补足前后位数

SELECT TO_CHAR(11.11,'0000.00000') FROM DUAL;
--0011.11000

（3）总结：格式是0的话无论是整数还是小数都会补齐位数，格式是9的话整数位不会补齐位数，只补小数部分位

SELECT TO_CHAR(11.11,'999.00000') FROM DUAL;	
--11.11000
SELECT TO_CHAR(11.11,'000.9999') FROM DUAL;		
--011.1100

（4）当指定值小于格式值位数，会进行四舍五入，此时格式值是0还是9都一样。

SELECT TO_CHAR(11.99,'99.9') FROM DUAL;		
--12.0
SELECT TO_CHAR(11.99,'00.0') FROM DUAL;		
--12.0
SELECT TO_CHAR(11.948,'99.9') FROM DUAL;		
--11.9

5.2 TO_DATE将字符串转成指定的时间格式

SELECT TO_DATE('2018-05-06','YY-MM-DD') FROM DUAL;
结果：2018/5/6 星期日

8.3 TO_NUMBER,将字符串转换为数字

参数1和参数2的位数必须要一一对应。

SELECT TO_NUMBER('1212.1212','0000.0000') FROM DUAL;
结果：1212.1212.

没有转换之前用单引号括起来的是字符串，经过TO_NUMBER就可以转换为数字格式。

5.4 NVL(comm,0)空值转换函数

SELECT sal+NVL(comm,0) 总工资 FROM emp;