2017/12/27更新
工作中学到的新技巧，特来补充下：
trunc()函数不仅可以处理数字，还可以处理日期。表示截断到当前，之后的日期补充起始状态。
如下，我们需要的是2017/12/27 10：00：00，可以截断到小时
select trunc(sysdate,'HH24') from dual;结果如下：
TRUNC(SYSDATE,'HH24')
---------------------
2017/12/27 10:00:00同样的，我们可截断年月日分钟等。
select trunc(sysdate,'YYYY'),trunc(sysdate,'MM'),trunc(sysdate,'MI') from dual;TRUNC(SYSDATE,'YYYY') TRUNC(SYSDATE,'MM') TRUNC(SYSDATE,'MI')
--------------------- ------------------- -------------------
2017/1/1 2017/12/1 2017/12/27 10:23:00这里是正文
Oracle学习5：单行函数
注：这里的计算结果字段名均较长，但为了方法看出计算公式，故基本未使用别名进行重命名。
1.概述
SQL函数根据输出结果可以分为单行函数和多行函数。
- 单行函数，一个输入对应一个输出；
- 多行函数，多个输入对应一个输出。
如下图：
2.单行函数
单行函数主要包括：字符函数、数字函数、日期函数、转换函数、通用函数
1.字符函数
字符函数，顾名思义，是对字符串进行操作的函数，主要包括：
chr()&ASCII()
将数字转化为对应的ASCII码。与之相对应的是ASCII()函数
select chr('65'),chr(97),chr(15) from dual;CHR('65') CHR(97) CHR(15)
--------- ------- -------
A a ASCII()函数
select ASCII('A'),ASCII('a') from dual;ASCII('A') ASCII('A')
---------- ----------
65 97UPPER()
将英文字母全部转化为大写
select upper('sss'),upper('SSxx') from dual;rst：
UPPER('SSS') UPPER('SSXX')
------------ -------------
SSS SSXXLOWER()
将英文字母转化为小写
select lower('sss'),lower('SSxx'),lower('中国') from dual;Rst：
LOWER('SSS') LOWER('SSXX') LOWER('中国')
------------ ------------- -----------
sss ssxx 中国LENGTH()
计算字符串长度
select length('sss'),length('SSxx'),length('中国') from dual;Rst：
LENGTH('SSS') LENGTH('SSXX') LENGTH('中国')
------------- -------------- ------------
3 4 2REPLACE()
字符替换函数，传递三个（或两个）参数，将第二个参数替换为第三个参数，区分大小写。如果没有第三个参数，则默认为”。
select replace('Sys','s','r'),replace('SSxx','x') from dual; --不写默认第三个参数是空Rst：
REPLACE('SYS','S','R') REPLACE('SSXX','X')
---------------------- -------------------
Syr SSINITCAP()
首字母大写，其余字母转化为小写。
注：其余字母被转化为小写。
select initcap('sss'),initcap('SSxx'),initcap('中国') from dual;INITCAP('SSS') INITCAP('SSXX') INITCAP('中国')
-------------- --------------- -------------
Sss Ssxx 中国SUBSTR()
substr(col_name, a[, b])
从第a个到第a+b个。
select substr(t.employee,0),substr(t.employee,1) from test_ljb t; --0和1结果一样Rst：
SUBSTR(T.EMPLOYEE,0) SUBSTR(T.EMPLOYEE,1)
------------------------------ ------------------------------
Arvin Arvin
Java Java
Hive Hive
Oracle Oracle
Hadoop Hadoop
Oracle Oracle
Oracle Oracle
Ora%cle Ora%cle
8 rows selectedselect employee,substr(t.employee,-2),substr(t.employee,-2,0),substr(t.employee,-2,-1),substr(t.employee,-2,1),substr(t.employee,-2,10) from test_ljb t; EMPLOYEE SUBSTR(T.EMPLOYEE,-2) SUBSTR(T.EMPLOYEE,-2,0) SUBSTR(T.EMPLOYEE,-2,-1) SUBSTR(T.EMPLOYEE,-2,1) SUBSTR(T.EMPLOYEE,-2,10)
---------- --------------------- ----------------------- ------------------------ ----------------------- ------------------------
Arvin in i in
Java va v va
Hive ve v ve
Oracle le l le
Hadoop op o op
Oracle le l le
Oracle le l le
Ora%cle le l le
8 rows selected注：关于substr()函数，有几点一定要关注：
- 在Oracle中，
substr()函数从0或1开始结果一样，都是代表从第一个开始； - 里面的两个参数均可以设置位负数；
2.数字函数
round
四舍五入
select round(12.12345),round(12.12345,1),round(12.12345,-1) from dual; --不写则保留0位有效小数,保留一位有效小数,精确到十位ROUND(12.12345) ROUND(12.12345,1) ROUND(12.12345,-1)
--------------- ----------------- ------------------
12 12.1 10
TRUNC
数字&日期截断函数。
数字
保留指定位，没有四舍五入
select TRUNC(12.18345),TRUNC(12.18345,1),TRUNC(-12.18345,2),TRUNC(-12.18345,-1) from dual;TRUNC(12.18345) TRUNC(12.18345,1) TRUNC(-12.18345,2) TRUNC(-12.18345,-1)
--------------- ----------------- ------------------ -------------------
12 12.1 -12.18 -10日期
trunc()函数不仅可以处理数字，还可以处理日期。表示截断到当前，之后的日期补充起始状态。
如下，我们需要的是2017/12/27 10：00：00，可以截断到小时
select trunc(sysdate,'HH24') from dual;结果如下：
TRUNC(SYSDATE,'HH24')
---------------------
2017/12/27 10:00:00同样的，我们可截断年月日分钟等。
select trunc(sysdate,'YYYY'),trunc(sysdate,'MM'),trunc(sysdate,'MI') from dual;TRUNC(SYSDATE,'YYYY') TRUNC(SYSDATE,'MM') TRUNC(SYSDATE,'MI')
--------------------- ------------------- -------------------
2017/1/1 2017/12/1 2017/12/27 10:23:00MOD()
取模运算，注意负数
select mod(10,2),mod(10,3),mod(10,-3),mod(-10,3),mod(-10,-3) from dual; MOD(10,2) MOD(10,3) MOD(10,-3) MOD(-10,3) MOD(-10,-3)
---------- ---------- ---------- ---------- -----------
0 1 1 -1 -13.转换函数
Oracle数据库常见的数据类型：数字（NUMBER）、字符串（VARCHAR2）、日期（DATE），转换函数的主要用于三种类型的相互转换，共三种：
TO_CHAR()、TO_DATE()、TO_NUMBER()。
3.1 TO_CHAR()：将日期（数字）转为字符串
3.1.1日期转化为字符串
将当前时间转化为指定的格式：
select to_char(sysdate,'YYYY-MM-DD'),to_char(sysdate,'YYYYMMYY HH:MI:SS'),to_char(sysdate,'YYYYMMDD HH24:MI:SS') from dual;TO_CHAR(SYSDATE,'YYYY-MM-DD') TO_CHAR(SYSDATE,'YYYYMMYYHH:MI TO_CHAR(SYSDATE,'YYYYMMDDHH24:
----------------------------- ------------------------------ ------------------------------
2017-12-25 20171217 04:22:35 20171225 16:22:35也可以获得年月份小时等：
select to_char(sysdate,'YYYY'),to_char(sysdate,'MM'),to_char(sysdate,'DD'),to_char(sysdate,'HH24') from dual;TO_CHAR(SYSDATE,'YYYY') TO_CHAR(SYSDATE,'MM') TO_CHAR(SYSDATE,'DD') TO_CHAR(SYSDATE,'HH24')
----------------------- --------------------- --------------------- -----------------------
2017 12 25 16此外，还可以把08变为8的格式，只需要在格式之前加一个fm即可，如下：
select to_char(to_date('20170101','YYYYMMDD'),'MM'),to_char(to_date('20170101','YYYYMMDD'),'fmMM') from dual;TO_CHAR(TO_DATE('20170101','YY TO_CHAR(TO_DATE('20170101','YY
------------------------------ ------------------------------
01 13.1.2数字转化为字符串
L表示Local，即转化为本地货币。
9表示有则占位，没有则不占位；0表示无论有没有，都占位。
select to_char(6000,'L999,999.999'),to_char(6000,'$999,999.999'),to_char(6000,'$000,000.000') from dual;TO_CHAR(6000,'L999,999.999') TO_CHAR(6000,'$999,999.999') TO_CHAR(6000,'$000,000.000')
---------------------------- ---------------------------- ----------------------------
￥6,000.000 $6,000.000 $006,000.0003.2 TO_DATE()函数：字符转化为日期
将20171010转化为日期格式等；
注：只要可以匹配上，怎么写都可以。
select to_date('20171010','YYYYMMDD'),to_date('2017-1010105930','YYYY-MMDDHH24MISS') from dual;TO_DATE('20171010','YYYYMMDD') TO_DATE('2017-1010105930','YYY
------------------------------ ------------------------------
2017/10/10 2017/10/10 10:59:303.3 TO_NUMBER()函数：字符串转化为数字
与to_char相对应的，有时我们需要针对形如’$6,000.000’的结果进行数据计算，此时可以用to_number()；
select
to_number('￥6,000.000','L999,999.999'),
to_char(to_number('￥6,000.000','L999,999.999')*12,'L999,999.999') annual_salary
from dual;TO_NUMBER('￥6,000.000','L999, ANNUAL_SALARY
----------------------------- ----------------------
6000 ￥72,000.0004.日期函数
4.1当前时间
SELECT SYSDATE FROM dual;SYSDATE
-----------
2017/12/224.2日期运算
- 日期 ± 数字 = 日期，表示若干天、若干小时之后（之前）的日期
计算明天、昨天
select sysdate+1 tomorrow,sysdate-1 yesterday from dual;TOMORROW YESTERDAY
----------- -----------
2017/12/26 2017/12/24计算一个小时前，一个小时候
select to_char(sysdate-1/24,'YYYY-MM-DD HH24:MI:SS'),to_char(sysdate+1/24,'YYYY-MM-DD HH24:MI:SS') from dual;关于to_char函数见上文。
TO_CHAR(SYSDATE-1/24,'YYYY-MM- TO_CHAR(SYSDATE+1/24,'YYYY-MM-
------------------------------ ------------------------------
2017-12-25 14:45:50 2017-12-25 16:45:50- 日期 – 日期 = 数字，表示相差的天数
计算当前时间到20171220的相差天数。
注：关于to_date函数详解，见上文。
select sysdate-to_date('2017-12-20','YYYY-MM-DD') from dual;SYSDATE-TO_DATE('2017-12-20','
------------------------------
5.6591087962963
- LAST_DAY(日期)：求出指定日期的本月最后一天的当前时刻
计算本月最后一天的当前时刻：
select last_day(sysdate) from dual;LAST_DAY(SYSDATE)
-----------------
2017/12/31 15:54:- NEXT_DAY(日期，星期数)：求出下一个星期X的日期和当前时刻；
SELECT NEXT_DAY(SYSDATE,'星期一') FROM dual;NEXT_DAY(SYSDATE,'星期一')
-----------------------
2018/1/1 15:57:35
- 前（后）几个月的日期
SELECT ADD_MONTHS(SYSDATE,-5),ADD_MONTHS(SYSDATE,5) FROM dual;ADD_MONTHS(SYSDATE,-5) ADD_MONTHS(SYSDATE,5)
---------------------- ---------------------
2017/7/25 16:00:10 2018/5/25 16:00:10- MONTHS_BETWEEN(日期1，日期2)：求出两个日期之间的月份差（可以为小数）
select months_between(to_date('2017/11/11','YYYY/MM/DD'),to_date('2017/01/12','YYYY/MM/DD')) from dual;MONTHS_BETWEEN(TO_DATE('2017/1
------------------------------
9.967741935483875.通用函数
nvl()、nvl2()、encode()、greatest()、least()等。
5.1 nvl(a, b)
返回第一个不是null的值，如果全为null，则返回null。
注：只能传递两个参数
select nvl(1,2),nvl(null,3),nvl(null,null) from dual; NVL(1,2) NVL(NULL,3) NVL(NULL,NULL)
---------- ----------- --------------
1 3 该函数类似于coalesce()，不同的是coalesce()可以大于等于两个的参数：
select coalesce(100,null,1,2) from dual;COALESCE(100,NULL,1,2)
----------------------
1005.2 nvl2(a, b, c)
如果a为null，则返回c，如果a不为null，则返回c
select nvl2(1,10,20),nvl2(null,10,20) from dual;NVL2(1,10,20) NVL2(NULL,10,20)
------------- ----------------
10 205.3 decode()
DECODE(数值 | 列 ，判断值1，显示值1，判断值2，显示值2，判断值3，显示值3，…)
select employee,decode(employee,'Arvin','阿尔文','Java','jv','Hive','hv') from test_ljb;EMPLOYEE DECODE(EMPLOYEE,'ARVIN','阿尔?
---------- ----------------------------
Arvin 阿尔文
Java jv
Hive hv
Oracle
Hadoop
Oracle
Oracle
Ora%cle
8 rows selected5.4 greatest(a, …)
选取参数列表中的最大值，参数大于等于1个。
如果参数存在null，则结果为null。
select greatest(1,null,10,100),greatest(10,20,50) from dual;GREATEST(1,NULL,10,100) GREATEST(10,20,50)
----------------------- ------------------
505.5 least(a, …)
选取参数列表中的最小值，参数大于等于1个。
如果参数存在null，则结果为null。
select least(1),least(1,-10),least(1,null,100) from dual; LEAST(1) LEAST(1,-10) LEAST(1,NULL,100)
---------- ------------ -----------------
1 -10 多行函数
Oracle多行函数也叫做聚合函数。多行函数多条数据返回一条。
主要包括：
MAX()
MIN()
AVG()
SUM()
COUNT()需要注意的是包含null的聚合运算。
select MAX(NULL) from dual;
select MIN(NULL) from dual;
select AVG(NULL) from dual;
select SUM(NULL) from dual;
select COUNT(NULL) from dual;结果分别是：
NULL
NULL
NULL
NULL
0与Mysql的结果一致。
2324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
