SQL函数分为单行函数和多行函数
单行函数
(1)字符函数
大小写转换函数:
①小写转换函数
LOWER({列名 | 变量 | 表达式})
②大写转换函数
UPPER({列名 | 变量 | 表达式})
③首字母大写函数
INITCAP({列名 | 变量 | 表达式})
④dual是一行N列的伪表
（2）字符处理函数
①连接两个值，等同于 | |
CONCAT(列名1 | 表达式1, 列名2 | 表达式2)
②截取字符串
SUBSTR(列名 | 表达式, n1[, n2])
作用：返回第一个参数中，从第n1位开始，长度为n2的子串
注意：
Ⅰ.如果n2被省略，则取第n1到最后的所有字符
Ⅱ.如果n1是负数，表示从第一个参数的后面第(-n1)位开始向右取长度为n2的子串
③取字符长度
LENGTH(列名 | 表达式)
④获取子串在字符串中出现的位置的下标
INSTR(参数1, 参数2 [,参数3,参数4])
其中：
参数1：待查找的目标字符串
参数2：要查找的子串
参数3：开始查找的位置，默认从1开始
参数4：要查找第N次出现的子串
⑤左补齐
LPAD(参数1, 参数2, 参数3)
其中：
参数1：带补齐的目标字符串
参数2：想要得到的字符串长度
参数3：不足长度的向左补齐的字符串
⑥右补齐
RPAD(参数1, 参数2, 参数3)
其中：
参数1：带补齐的目标字符串
参数2：想要得到的字符串长度
参数3：不足长度的向右补齐的字符串
⑦去除字符串头部或尾部或头尾的字符
TRIM( [LEADING | TRAILING | BOTH 字符 FROM] 字符串)
其中：
LEADING为头部，TRAILING为尾部，BOTH为头部和尾部
⑧替换字符串
REPLACE(参数1, 参数2, 参数3)
其中：
参数1：源字符串
参数2：源字符串中要被替换的字符串
参数3：要替换参数2的字符串
（3）数值函数
①四舍五入
ROUND(列名 | 表达式 [, n])
注意：
Ⅰ.当有两个参数的时候，表示结果保留小数点后第n位；
Ⅱ.当只有一个参数的时候，表示结果保留整数
Ⅲ.当n为负数时，表示整数第(-n)位开始四舍五入，大于等于5向前进1；小于5，取0;
②截取小数点第n位
TRUNC(列名 | 表达式 [, n])
注意：
Ⅰ.当有两个参数的时候，表示结果截取到小数点第n位；
Ⅱ.当只有一个参数的时候，表示结果保留整数
Ⅲ.当n为负数时，表示整数第(-n)位到最后一位都取0
③取m除于n得到的余数
MOD(m,n)
（4）日期函数
①返回系统当前日期
SYSDATE
②RR日期格式：用来判定按照DD-MON-RR格式给定的日期实际代表的日期是多少
大大 不变
大小 世纪+1
小小 不变
小大 世纪-1
③返回两个日期类型数据之间间隔的自然月份
MONTHS_BETWEEN(日期类型数据1, 日期类型数据2)
④返回指定日期加上相应的月份后的日期
ADD_MONTHS(日期类型数据, 月份)
⑤返回某一日期的下一个指定星期
NEXT_DAY(日期类型数据, 星期几)
⑥返回某一日期所在月份的最后一天
LAST_DAY(日期类型数据)
⑦四舍五入日期
ROUND(日期类型数据[, 'YEAR' | 'MONTH' | 'DAY'])
⑧截断日期
TRUNC(日期类型数据[, 'YEAR' | 'MONTH' | 'DAY'])
⑨返回日期类型数据中的年份、月份或者日
EXTRACT([YEAR | MONTH | DAY] FROM 日期类型数据)
（5）转换函数
①TO_CHAR(数据, ['fmt'])
Ⅰ.当数据为日期型数据时，功能为：将日期类型数据转换为[规定日期格式的]字符串
日期模型的元素：
| YYYY | 完整的年份 |
| YEAR | 年份的英文 |
| MM | 两位数字的月份 |
| MONTH | 月份的全名 |
| DD | 日期 |
| DAY | 星期几 |
| DY | 用3个英文字符来表示星期几 |
| HH | 小时(其中12HH表示12小时制，24HH表示24小时制) |
| MI | 分钟 |
| SS | 秒 |
| AM | 上午 |
| PM | 下午 |
Ⅱ.当数据为数值型数据时，功能为：将数值类型数据转换为[规定日期格式的]字符串
数值模型的元素：
| 9 | 一位数字 |
| 0 | 显示前导0 |
| $ | 显示美元符号 |
| L | 显示本地货币符号 |
| . | 显示小数点 |
| , | 显示千位符 |
注意：进行数字类型到字符型转换时，格式中的宽度一定要超过实际列宽度，否则会显示为###
②将字符串转换为数值
TO_NUMBER(字符串)
③将字符串转换为日期
TO_DATE(字符串, ['fmt'])
注意：当字符串为日期的默认格式的日期类型数据即'日-月-年'时，才可以省略'fmt'
（6）通用函数
①判断参数一是否为空，如果为null，取参数二的值；反之，取参数一本身的值
NVL(参数一, 参数二)
②判断参数一是否为空，不为空取参数二的值，为空取参数三的值
NVL2(参数一, 参数二, 参数三)
③判断参数一与参数二是否相等，相等返回null，不等返回参数一
NULLIF(参数一, 参数二)
④判断参数一是否为空，不为空取参数一的值；为空则判断参数二是否为空，参数二不为空取参数二的值，为空判断参数三是否为空...
COALESCE(参数一, 参数二, 参数三, ..., 参数N)
⑤判断表达式，如果满足表达式1，返回值1；如果满足表达式2，返回值2；...；如果都不满足返回else值
CASE 表达式
WHEN 表达式1 THEN 值1
WHEN 表达式1 THEN 值2
WHEN 表达式1 THEN 值3
...
WHEN 表达式N THEN 值N
ELSE else值
END
⑥如果表达式的值等于表达式1，返回值1；如果表达式的值等于表达式2，返回值2；如果表达式的表达式等于值3，返回值3；...；都不相等返回缺省值
DECODE(表达式, 表达式1, 值1, 表达式2, 值2, 表达式3, 值3, ... , 缺省值)
（7）函数嵌套
单行函数可以嵌套N层
（8）抽取日期的三种
①EXTRACT()
②SUBSTR()
③TO_CHAR()
(9)练习
--1.字符函数
--（1）大小写转换函数
SELECT ename,LOWER(ename),job FROM emp;--转小写
SELECT 'SQL source',LOWER('SQL source') FROM dual;--dual伪表
SELECT 'SQL source',UPPER('SQL source') FROM dual;--转大写
SELECT 'sql source',INITCAP('sql source') FROM dual;--首字母大写
--（2）字符处理函数
SELECT ename,sal FROM emp;
SELECT CONCAT(ename,sal) FROM emp;--拼接两个列的值
SELECT CONCAT(CONCAT(ename,'的薪水为：'),sal) FROM emp;--函数的嵌套
SELECT 'lqh@qq.com',SUBSTR('lqh@qq.com',5) FROM dual;--从下标为5开始截取到最后
SELECT 'lqh@qq.com',SUBSTR('lqh@qq.com',5,2) FROM dual;--从下标为5开始截取，截取长度为2的子串
SELECT 'lqh@qq.com',SUBSTR('lqh@qq.com',-6,2) FROM dual;--从后面第6位开始截取，截取长度为2的子串
SELECT 'lqh@qq.com', LENGTH('lqh@qq.com') FROM dual;--获取字符串的长度
SELECT ename,LENGTH(ename) FROM emp;--获取所有员工姓名的长度
SELECT 'lqh@qq.com', INSTR('lqh@qq.com','lqh') FROM dual;--查找'lqh'第一次出现的位置
SELECT 'lqh@qq.com', INSTR('lqh@qq.com','lqh',2) FROM dual;--从2开始查找'lqh'第一次出现的位置
SELECT 'lqh@qq.com', INSTR('lqh@qq.com','q',2,2) FROM dual;--从2开始查找'q'第二次出现的位置
SELECT ename, '￥' || LPAD(sal, 10 , '-') AS 薪水 FROM emp;--薪水不足10位的向左补'-'
SELECT ename, RPAD(ename, 10,'_') AS 员工姓名 FROM emp;--员工姓名不足10位的向右补'_'
SELECT ' lqh@qq.com ', TRIM(' lqh@qq.com ') FROM dual;--默认除去首尾空格
SELECT '_lqh@qq.com_', TRIM(LEADING '_' FROM '_lqh@qq.com_') AS 去除头部字符 FROM dual;--去除头部字符'_'
SELECT '_lqh@qq.com_', TRIM(TRAILING '_' FROM '_lqh@qq.com_') AS 去除尾部字符 FROM dual;--去除尾部字符'_'
SELECT '_lqh@qq.com_', TRIM(BOTH '_' FROM '_lqh@qq.com_') AS 去除头尾字符 FROM dual;--去除头尾字符'_'
SELECT 'lqh@qq.com',REPLACE('lqh@qq.com', 'qq', 'wechat') AS 替换后的字符串 FROM dual;--将字符串中的'qq'替换成'wechat'
SELECT 'lqh@qq.com',REPLACE('lqh@qq.com', 'q', '*') AS 替换后的字符串 FROM dual;--将字符串中所有'q'替换成'*'
--(3)数值函数
SELECT ROUND(520.131396), ROUND(520.131396, 4) FROM dual;--取整， 保留小数点后四位小数
SELECT ROUND(519.131396, -1), ROUND(520.131396, -2) FROM dual;--整数第一位四舍五入，整数第二位四舍五入
SELECT TRUNC(520.131396), TRUNC(520.131396, 4) FROM dual;--截取整数部分，截取到小数点第四位
SELECT TRUNC(519.131396, -1), TRUNC(520.131396, -2) FROM dual;--将整数第一位以及之后的数变为0，将整数第二位以及之后的数变为0
SELECT MOD(520,3) FROM dual;--取520/3的余数
--(4)日期函数
SELECT SYSDATE FROM dual;--获取系统当前时间
--查询员工的入职日期，转正日期（6个月试用期），入职天数，入职星期数等
SELECT hiredate 入职日期,
hiredate + (6 * 30) 转正日期,
(SYSDATE - hiredate) 入职天数,
(SYSDATE - hiredate) / 7 入职星期数
FROM emp;
-------------------
当前年份 指定的日期 RR格式
1995 27-12月-95 1995
1995 27-12月-17 2017
2001 27-12月-17 2017
2001 27-12月-95 1995
-------------------
SELECT MONTHS_BETWEEN('1-12月-12', '1-5月-12') FROM dual;--返回两个日期相差的月份
SELECT ADD_MONTHS('1-12月-12', 2) FROM dual;--加上两个月
SELECT NEXT_DAY(SYSDATE, '星期一') FROM dual;--返回系统时间的下一个星期一
SELECT SYSDATE, ROUND(SYSDATE) FROM dual;
SELECT SYSDATE,
ROUND(SYSDATE, 'YEAR'), --根据月份进位，1-6不变，7-12进1
ROUND(SYSDATE, 'MONTH'), --根据天数进位，1-15不变，16-30进1
ROUND(SYSDATE, 'DAY') --根据星期进位，星期日-三不变，星期四-六进1
FROM dual;
SELECT SYSDATE,
TRUNC(SYSDATE, 'YEAR'), --截取年份，月和日都为1
TRUNC(SYSDATE, 'MONTH'), --截取月份，日为1
TRUNC(SYSDATE, 'DAY') --截取日
FROM dual;
SELECT SYSDATE,
EXTRACT(YEAR FROM SYSDATE), --抽取年
EXTRACT(MONTH FROM SYSDATE), --抽取月
EXTRACT(DAY FROM SYSDATE) --抽取日
FROM dual;
--(5)转换函数
SELECT SYSDATE, TO_CHAR(SYSDATE) FROM dual;--将当前系统时间转换为字符串
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'YYYY') YYYY,--四位的年分
TO_CHAR(SYSDATE, 'YEAR') "YEAR",--英文年份
TO_CHAR(SYSDATE, 'MM') MM,--两位的月份
TO_CHAR(SYSDATE, 'MONTH') "MONTH",--全名的月份
TO_CHAR(SYSDATE, 'DD') DY,--日期
TO_CHAR(SYSDATE, 'DAY') "DAY",--星期
TO_CHAR(SYSDATE, 'HH') HH,--小时
TO_CHAR(SYSDATE, 'MI') MI,--分钟
TO_CHAR(SYSDATE, 'SS') "SS",--秒
TO_CHAR(SYSDATE, 'YYYY-MM-DD PM HH:MI:SS')--转换为规定的日期格式的字符串
FROM dual;
SELECT TO_CHAR(5201314, 'L0999,999,999.999') FROM dual;--位数不够，补零
SELECT TO_CHAR(5201314, 'L999.999') FROM dual;--位数超过了，显示##################
SELECT TO_NUMBER('520.1313')+0.0001 FROM dual;--将字符串转为数值
SELECT TO_DATE('1-12月-01') FROM dual;--将字符串转为日期
SELECT TO_DATE('2005-12-01') FROM dual;--报错
SELECT TO_DATE('2005-12-01', 'YYYY-MM-DD') FROM dual;--正确
--(6)通用函数
SELECT comm, NVL(comm, 0), deptno, NVL(deptno, 0) FROM emp;--如果comm和deptno为空
SELECT comm, NVL2(comm ,'不为空', '空') FROM emp;--如果comm为空输出“空”，不为空输出“不为空”
SELECT NULLIF(520,1314) FROM dual;--如果两个数相等，返回null，否则返回第一个数
SELECT comm, COALESCE(TO_CHAR(comm), null, null, 'comm为空') FROM emp;--如果comm为空，返回函数参数中不为空的值；不为空，则返回其自身的值
SELECT deptno,
(CASE deptno
WHEN 10 THEN
'销售部'
WHEN 20 THEN
'员工部'
WHEN 30 THEN
'经理部'
ELSE
'打杂部'
END)
FROM emp; --如果部门为10，返回“销售部”；如果部门为20，返回“员工部”；如果部门为30，返回“经理部”；否则返回“打杂部”
--效果跟上面一样
SELECT deptno,
(CASE
WHEN deptno = 10 THEN
'销售部'
WHEN deptno = 20 THEN
'员工部'
WHEN deptno = 30 THEN
'经理部'
ELSE
'打杂部'
END)
FROM emp;
--效果跟上面一样
SELECT deptno, DECODE(deptno, 10, '销售部', 20, '员工部', 30, '经理部', '打杂部') FROM emp;
--(7)函数嵌套
SELECT mgr, NVL(mgr, '为空') FROM emp;--错误，NVL中仅允许存放同类型数据
SELECT mgr, NVL(TO_CHAR(mgr), '为空') FROM emp;--正确，NVL函数里面嵌套TO_CHAR函数
--抽取日期
SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE), SUBSTR(SYSDATE,-2), TO_CHAR(SYSDATE, 'YYYY') FROM dual;--抽取年份的三种方法