一、SQL函数概述
- SQL函数分类
单行函数
多行函数
- 单行函数语法
语法：
函数名[(参数1，参数2，….)]
- 注意：其中的参数可以是以下之一：
变量
列名
表达式
- 单行函数特征
- 单行函数对单行操作
- 每行返回一个结果
- 有可能返回值与原参数数据类型不一致
- 单行函数可以写在SELECT、WHERE、ORDER BY子句中
- 有些函数没有参数，有些函数包括一个或多个参数
- 函数可以嵌套
- 单行函数分类
- 字符函数
- 通用函数
- 转换函数
- 日期函数
- 数值函数
二、字符函数
主要指参数类型是字符型，不同函数返回值可能是字符型或数值型。
- 大小写转换函数
- LOWER（列名|表达式）：将大写或大小写混合的字符串转换成小写
LOWER('SQL Course') //结果为sql course
- UPPER（列名|表达式）：将小写或大小写混合的字符串转换成大写
UPPER('SQL Course) //结果为SQL COURSE
- INTICAP（列名|表达式）：将每一个单词的第一个字母转换成大写，其余的字母都转换成小写。
INITCAP('SQL Course') //结果为Sql Course
- 字符处理函数
CONCAT(参数1，参数2)
- 连接两个值，等同于||
- 例如：
CONCAT('Good' , 'String') //结果为GoodString
SUBSTR(参数1,n1,n2)
- 返参数1中，从第n1位开始，长度为n2的子串
- 如果n2省略，取第n1位开始的所有字符
- 如果n1是负值，表示从第一个参数的后面第abs(n1)位开始，向右取长度为n2的子串
- 例如：
SUBSTR('String' , 1 , 3) //结果为Str
LENGTH(参数)
- 取字符串的长度
- 例如：
LENGTH('String') //结果为6
INSTR(s1,s2,n1,n2)
- 返回s1中，子串s2从n1位置开始，第n2次出现的位置。n1和n2默认值都为1。
- 例如：
INSTR('String',r,) //结果为3
LPAD(s1,n1,s2)
- 返回s1被s2左填充到n1长度后的字符串
- 例如：
LPAD(sal,10,*) //结果为******5000
RPAD(s1,n1,s2)
- 返回s1被s2右填充到n1长度后的字符串
- 例如：
RPAD(sal,10,*) //结果为5000******
TRIM(参数1 From 参数2)
- 去掉字符串头部或者尾部（头尾）的字符
- 例如：
TRIM('S' From 'SSSHHHLLL') //结果为HHHLLL
REPLACE(s1,s2,s3)
- 把s1中的s2用s3替换。
- 例如：
RPPLACE('Sring' , 'rin','****') //结果为S****g
三、数值函数
DUAL：是一张虚表，不能保存任何数据，只有一个字段，一行记录。当我们不希望从任何表中读取数据，同时又想利用SQL引擎中的计算表达式的能力帮我们运算时，就可以使用DUAL表。
ROUND(参数，n)
- 将参数所表示的数值四舍五入到小数点后的第n位
- 例如：
ROUND(45.926) //结果为45.93
TRUNC(参数，n)
- 将参数所表示的数值截取到小数点后的第n位
- 例如：
TRUNC(45.926) //结果为54.92
MOD(m，n)
- 取m除以n后得到的余数
- 例如：
MOD(1600,300) //结果为100
四、日期的处理
Oracle是以一种内部的数值形式存储日期的，即：世纪、年、月、日、时、分、秒
默认的日期格式:DD-MON-RR
五、日期的运算
- 日期类型可以加减数字，功能是在该日期上加减相应的天数。
如：‘10-AUG-18’+15结果是‘25-AUG-18’
- 日期类型之间可以进行减操作，功能是计算两个日期之间相隔多少天。
如：‘25-AUG-18’- ‘10-AUG-18’结果四舍五入后是15天
- 如果需要对一个日期进行加减相应的小时操作，可以使用n/24来实现。
- SYSDATE：返回系统的当前时间。
六、日期函数
-
日期的RR格式和YY格式在给定的日期而实际代表的日期是多少呢？
2019-02-14_155321.png
- 常用的日期函数
- SYSDATE：返回系统日期
- MONTHS_BETWEEN：返回两个日期类型之间相隔的自然月数
- ADD_MONTHS：返回指定日期加上指定月数后的日期
- NEXT_DAY：返回某个日期指定的下一个日期
- LAST_DAY：返回指定日期当月最后一天的日期
- ROUND(date [ , ’fmt’ ])将date按照fmt指定的格式进行四舍五入，fmt为可选项，如果没有指定fmt。默认为DD，将date四舍五入最近的一天。
格式：世纪CC，年YY，月MM，日DD，小时HH24，分MI，秒SS- TRUNC(date [ , ’fmt’ ])将date按照fmt中指定的格式进行截断，fmt为可选项，如果没有指定fmt，默认为DD，将date截断为最近的一天。
- EXTRACT：返回日期数据类型中的年份，月份或者日。
七、转换函数
-
隐式数据类型转换规则
不同的数据类型之间关联，如果不使用显式转换函数，则他会根据以下规则对数据进行隐式转换：- 对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为字的数据类型
- 对于SELECT语句，oracle会把字段数据类型隐式转换为变量的数据类型
- 当比较一个数值型和一个字符型的值时，oracle会隐式的将字符型转换为数值型。
- 当比较字符型和日期型的数据时，oracle会自动将字符型转换为日期型
- 当链接操作符（||）时，oracle会将非字符型的数据转换成字符型
- 如果字符型的数据和非字符型的数据（如number、date、rowid等）作算术运算，则oracle会将字符型的数据转换为合适的数据类型，这些数据可能是number、date、rowid等。
显式数据类型转换
通常是在字符类型、日期类型、数值类型之间进行显示转换。主要有3个显示转换函数：
TO_CHAR
TO_NUMBER
TO_DATE
TO_CHAR用于日期型 TO_CHAR(date,'fmt')
日期格式模型：
- 必须用单引号引起来并且是大小写敏感的
- 可以包含任何有效的日期元素
- 使用逗号将日期型数据与日期型格式模型分开
2019-02-14_160446.png
- TO_CHAR用于数值型 TO_CHAR(number,'fmt')
通过TO_CHAR中使用以下形式可以把数值型数据转化成变长的字符串
2019-02-14_160724.png
注意：进行数字型到字符型转换时，格式中的宽度一定要超过实际列宽度，否则会显示我###
- TO_NUMBER和TO_DATE函数
- 使用TO_NUMBER将一个字符串转换成数值型数据
TO_NUMBER(char[,'fmt'])
注意：要转换的char类型的数据必须是由数字组成的字符串，格式码中相应的格式必须要和char中的格式匹配。- 使用TO_DATE将一个字符串转换成日期型数据
TO_DATE(char[,'fmt'])
注意：要转换的char类型的数据必须是可以转换成日期的，格式码中相应的格式必须要和char中的格式匹配。
八、通用函数
与空值（NULL）相关的一些函数，完成对空值（NULL）的一些操作。主要包括以下函数：
- NVL - NVL2 - NULL IF - COALESCE 条件处理函数： - CASE表达式 - DECODE
- NVL函数
语法：NVL(参数1，参数2)
如果参数1不是NULL，返回参数1，否则返回参数2
- NVL2函数
语法：NVL2(参数1，参数2，参数3)
如果参数1不是NULL，返回参数2，否则返回参数3
- NULL IF函数
语法：NULL IF(参数1，参数2)
比较两个参数（表达式），如果相等，返回NULL，否则，返回第一个参数（表达式）
- COALESCE函数
语法：COALESCE (表达式1，表达式2，….表达式n)
该函数是对NVL函数的扩展。COALESCE函数的功能是返回第一个不为空的参数，参数个数不受限制。
- CASE和DECODE函数
DECODE (字段|表达式，条件1，结果1[,条件2，结果2….] [,缺省值])
例子：SELECT ename, deptno, decode(deptno, 10,'销售部', 20,'技术部', 30 , '管理部', '无') deptname FROM emp; //备注：不常用，了解就行
九、函数的嵌套
单行函数可以嵌套于任何层
嵌套的函数是从最里层向最外层的顺序计算的
例如：
SELECT ename,
NVL(TO_CHAR(mgr),'No Manager')
FROM emp
WHERE mgr IS NULL;
