SQL函数概述
SQL函数分类
SQL函数分为单行函数和多行函数单行函数语法
函数名[(参数1,参数2,...)]其中的参数可以是变量、列名、表达式单行函数特征
单行函数对单行操作每行返回一个结果有可能返回值与原参数数据类型不一致单行函数可以写在SELECT、WHERE、ORDER BY子句中有些函数没有参数，有些函数包括一个或多个参数函数可以嵌套单行函数分类
单行函数分为字符函数、数值函数、日期函数、转换函数、通用函数字符函数
主要指参数类型是字符型，不同函数返回值可能是字符型或数值型大小写转换函数
LOWER(列名|表达式)：将大写或大小写混合的字符转换成小写，多用于WHERE子句中UPPER(列名|表达式)：将小写或大小写混合的字符转换成大写，多用于WHERE子句中INITCAP(列名|表达式)：将每个单词的第一个字母转换成大写，其余的字母都转换成小写
| 函数 | 结果 |
|---|
| LOWER('SQL Course') | sql course |
| UPPER('SQL Course') | SQL COURSE |
| INITCAP('SQL Course') | Sql Course |
字符处理函数
ASCII(c)：返回字符c的ASCII码CHR(i)：返回给定的ASCII码i所对应的字符CONCAT(s1,s2)：连接两个值，相当于“||”如果s1为NULL，则返回s2如果s2为NULL，则返回s1如果s1和s2都为NULL，则返回NULLSUBSTR(s,n1[,n2])：返回字符串s从第n1位开始，长度为n2的子串如果n2省略，取第n1位开始的所有字符如果n1是负值，表示从第一个参数的后面第ABS(n1)位开始，向右取长度为n2的子串LENGTH(s)：取字符串s长度，如果s为NULL，则返回值为NULLINSTR(s1,s2[,n1[,n2]])：返回字符s2在字符串s1中第n2次出现时的位置，搜索从字符串s1的第n1个字符开始当没有发现要查找的字符时，返回值为0如果n1为负数，那么搜索将从右到左进行，但函数的返回位置还是按从左到右计算n1和n2可以省略，即均取默认值1LPAD(s1,n1,s2)：返回s1被s2从左面填充到n1长度后的字符串RPAD(s1,n1,s2)：返回s1被s2从右面填充到n1长度后的字符串LTRIM(s1,s2)|RTRIM(s1,s2)|TRIM(s1,s2)：去除字符串s1左边|右边|左右两端字符s2，如果不指定s2，则表示去除相应方位的空格REPLACE(s1,s2[,s3])：把s1中的s2用s3替换,s3的默认值为空字符串| 函数 | 结果 |
|---|
| ASCII('Z') | 90 |
| CHR(72) | H |
| CONCAT('Good','Bye‘) | GoodBye |
| SUBSTR('String',1,3) | Str |
| LENGTH('String') | 6 |
| INSTR('String','r') | 3 |
| LPAD(sal,10,'*') | ******5000 |
| RPAD(sal,10,'*') | 5000****** |
| TRIM('SSMITHS','S') | MITH |
| REPLACE('abc','b','d') | adc |
数值函数
ROUND函数
将列或表达式所表示的数值四舍五入到小数点后的第n位格式：ROUND(列名|表达式,n)举例：ROUND(45.926,2) → 45.93TRUNC函数
将列或表达式所表示的数值截取到小数点后的第n位格式：TRUNC(列名|表达式,n)举例：TRUNC(45.926,2) → 45.92MOD函数
取m除以n后得到的余数格式：MOD(m,n)举例：MOD(1600,300) → 100其他数值函数
| 函数 | 说明 |
|---|
| ABS(n) | 返回n的绝对值 |
| CEIL(n) | 返回大于等于n的最小整数 |
| COS(n) | 返回n的余弦值，n为弧度 |
| EXP(n) | 返回e的n次幂，e=2.71828183 |
| FLORR(n) | 返回小于等于n的最大整数 |
| LOG(n1,n2) | 返回以n1为底n2的对数 |
| POWER(n1,n2) | 返回n1的n2次方 |
| SIGN(n) | 若n为负数，则返回-1；若n为正数，则返回1；若n为0，则返回0 |
| SIN(n) | 返回n的正弦值，n为弧度 |
| SQRT(n) | 返回n的平方根 |
日期函数
日期的处理
Oracle是以一种内部的数值形式存储日期的，即：世纪、年、月、日、小时、分、秒默认的日期形式是：DD-MON-RR(DD表示两位字符的“日”，MON表示三位字符的“月”，RR表示两位字符的“年”)日期的数学运算
日期类型可以通过加减数字实现在该日期上加减对应的天数，如('10-AUG-16'+15)结果是'25-AUG-16'日期类型之间进行减操作是计算两个日期之间间隔了多少天，如('10-AUG-16'-'4-AUG-16')结果四舍五入后是6天如果需要对一个日期进行加减响应小时操作，可以使用n/24来实现RR日期格式
用来判定按照DD-MON-RR格式给定的日期实际代表的日期是多少
常用日期函数
SYSDATE：返回系统当前的日期MONTHS_BETWEEN：返回两个日期类型数据之间间隔的自然月数-- 查询所有员工服务的月数SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate) monthsFROM empADD_MONTHS：返回指定日期加上相应的月数后的日期-- 查询所有员工转正日期，试用期按三个月考虑SELECT ename,ADD_MONTHS(hiredate,3) new_date FROM empNEXT_DAY：返回某一日期的下一个指定日期-- 返回在02-2月-16之后的下一个周一是什么日期SELECT NEXT_DAY('02-2月-16','星期一') next_dayFROM DUALLAST_DAY：返回指定日期当月最后一天的日期-- 返回02-2月-16年所在月份的最后一天SELECT LAST_DAY('02-2月-16') "LAST DAY"FROM DUALROUND(date[,’fmt‘])：将date按照fmt指定的格式进行四舍五入fmt：可选项，默认为DD，即将date四舍五入为最近的天-- 查询81年入职的员工姓名、入职日期、按月四舍五入后的入职日期SELECT ename,hiredate,ROUND(hiredate,'MONTH')FROM empWHERE SUBSTR(hiredate,-2,2)='81'TRUNC(date[,’fmt‘])：将date按照fmt指定的格式进行截取fmt：可选项，默认为DD，即将date截取为最近的天-- 查询81年入职的员工姓名、入职日期、按月截断后的入职日期SELECT ename,hiredate,TRUNC(hiredate,'MONTH')FROM empWHERE SUBSTR(hiredate,-2,2)='81'EXTRACT([YEAR]|[MONTH]|[DAY] FROM [日期类型表达式])：返回日期类型数据中的年份、月份或者日-- 查询部门编号是10的部门中所有员工入职月份SELECT ename,hiredate,EXTRACT(MONTH FROM HIREDATE) monthFROM empWHERE deptno=10NEW_TIME(date,t1,t2)：当时区t1中的日期和时间是date时，返回时区t2中的日期和时间转换函数
隐式数据类型转换规则
对于INSERT和UPDATE操作，Oracle会把插入值或者更新隐式转换为字段的数据类型对于SELECT语句，Oracle会把字段的数据类型隐式转换为变量的数据类型当比较一个字符型和数值型的值时，Oracle会把字符型的值隐式转换为数值型当比较字符型和日期型的数据时，Oracle会把字符型转换为日期型用连接操作符||时，Oracle会把非字符类型的数据转换为字符类型如果字符类型的数据和非字符类型的数据作算术运算，Oracle会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等显式数据类型转换
TO_CHAR函数用于日期型：TO_CHAR(date,'fmt')SELECT ename,TO_CHAR(hiredate,'MM/YY') month_hiredFROM emp日期格式模型fmt必须用单引号引起来并且是大小写敏感的fmt可以包含任何有效的日期元素
| YYYY | 完整的年份数字表示 |
| YEAR | 年份的英文表示 |
| MM | 用两位数字来表示月份 |
| MONTH | 月份的全名 |
| DAY | 星期几 |
| DY | 用3个英文字符缩写来表示星期几 |
| HH24:MI:SS AM | 15:45:32 PM |
| DD "of" MONTH | 12 of OCTOBER |
用于数值型：TO_CHAR(number,'fmt')SELECT TO_CHAR(sal,'$99,999') salaryFROM empWHERE ename='SCOTT'进行数字类型到字符型转换时，格式中的宽度一定要超过实际列宽度，否则会显示为###数值格式模型fmt包含的数值元素
| 9 | 一位数字 |
| 0 | 显示前导零 |
| $ | 显示美元符号 |
| L | 显示本地货币符号 |
| . | 显示小数点 |
| , | 显示千位符 |
TO_NUMBER(char[,'fmt'])将字符串转换成数值型数据要转换的char类型数据必须是由数字组成的字符串格式码中相应的格式必须要和char中的格式匹配TO_DATE(char[,'fmt'])将字符串转换成日期型数据要转换的char类型数据必须是可以转换成日期的字符格式码的格式必须要和char中的格式匹配通用函数
NVL函数
语法：NVL(表达式1,表达式2)如果表达式1不是NULL，返回表达式1，否则返回表达式2SELECT ename,sal,comm,(sal*12)+NVL(comm,0)FROM empNVL2函数
语法：NVL2(表达式1,表达式2,表达式3)如果表达式1不是NULL，返回表达式2，否则返回表达式3NULLIF函数
语法：NULLIF(表达式1,表达式2)比较两个表达式，如果相等，返回NULL，否则，返回表达式1COALESCE函数
语法：COALESCE(表达式1,表达式2,...,表达式n)返回第一个不为空的表达式，参数表达式个数不限，是对NVL函数的扩展SELECT ename,COALESCE(comm,0) comm,deptnoFROM empCASE函数
CASE函数语法-- CASE函数语法：CASE 字段|表达式 WHEN 条件1 THEN 结果1 [WHEN 条件2 THEN 结果2 ...... ELSE 缺省值]ENDCASE函数示例-- CASE函数示例：SELECT ename,deptno, (CASE deptno WHEN 10 THEN '销售部' WHEN 20 THEN '技术部' WHEN 30 THEN '管理部' ELSE '无' END) deptnameFROM empDECODE函数
语法：DECODE(字段|表达式,条件1,结果1[,条件2,结果2,...][,缺省值])DECODE函数示例-- DECODE函数示例：SELECT ename,deptno, DECODE(deptno, 10,'销售部', 20,'技术部', 30,'管理部', '无') deptnameFROM emp函数嵌套
单行函数可以嵌套于任何层嵌套的函数时从最里层向最外层的顺序计算的SELECT ename, NVL(TO_CHAR(mgr),'No Manager')FROM empWHERE mgr IS NULL
