&&SQL函数概述
单行函数
单行函数语法
语法: 函数名[(参数1,参数2,…)]
其中的参数可以是以下之一:
-
-
- 变量
- 列名
- 表达式
-
单行函数特征
-
-
- 单行函数对单行操作
- 每行返回一个结果
- 有可能返回值与原参数数据类型不一致
- 单行函数可以写在SELECT、WHERE、ORDER BY子句中
- 有些函数没有参数,有些函数包括一个或多个参数
- 函数可以嵌套
-
##字符函数
字符函数:主要指参数类型是字符型,不同函数返回值可能是字符型或数值型。
大小写转换函数
- LOWER(列名|表达式):将大写或大小写混合的字符转换成小写
- UPPER(列名|表达式) :将小写或大小写混合的字符转换成大写
- INITCAP(列名|表达式) :将每个单词的第一个字母转换成大写,其余的字母都转换成小写
字符处理函数
- CONCAT(column1|expression1,column2|expression2) 连接两个值 ,等同于||
- SUBSTR (column|expression,n1[,n2]) 返回第一个参数中,从第n1位开始,长度为n2的子串。 如果n2省略,取第n1位开始的所有字符。 如果n1是负值,表示从第一个参数的后面第abs(n1)位开始向右取长度为n2的子串。
- LENGTH(column | expression) 取字符长度
- INSTR(s1,s2,[,n1],[n2]) 返回s1中,子串s2从n1开始,第n2次出现的位置。n1,n2默认值为1
- LPAD(s1,n1,s2) 返回s1被s2从左面填充到n1长度后的字符串。
- RPAD(s1,n1,s2) 返回s1被s2从右面填充到n1长度后的字符串。
- TRIM:去除字符串头部或尾部(头尾)的字符格式:TRIM(leading | trailing | both trim_character From trim_source)
- REPLACE(s1,s2,s3) 把s1中的s2用s3替换。
##数值函数
数值函数
- ROUND:将列或表达式所表示的数值四舍五入到小数点后的第n位。 格式: ROUND(column| expression, n) ROUND(45.926, 2) -> 45.93
DUAL:是一张虚表,不能保存任何数据,只有一个字段,一行记录。当我们不希望从任何表中读取数据,同时又想利用SQL引擎中的计算表达式的能力帮我们运算时,就可以使用DUAL表。
- TRUNC:将列或表达式所表示的数值截取到小数点后的第n位。 格式:TRUNC(column| expression, n) TRUNC(45.926, 2) -> 45.92
- MOD:取m除以n后得到的余数 格式:MOD(m,n) MOD(1600, 300) ->100
##日期函数
Oracle是以一种内部的数值形式存储日期的,即:世纪、年、月、日、小时、分、秒
默认的日期形式是: DD-MON-RR
-
-
- 日期类型可以加减数字,功能是在该日期上加减对应的天数。如:’10-AUG-06’+15结果是’25-AUG-06’
- 日期类型之间可以进行减操作,功能是计算两个日期之间间隔了多少天。如:’10-AUG-06’-‘4-AUG-06’结果四舍五入后是6天
- 如果需要对一个日期进行加减相应小时操作,可以使用n/24来实现
- SYSDATE:返回系统当前日期
-
常用日期函数
-
-
- 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_NUMBER // TO_DATE
##通用函数
通用函数 与空值(NULL)相关的一些函数,完成对空值(NULL)的一些操作。
主要包括以下函数:
-
-
- NVL
- NVL2
- NULLIF
- COALESCE
-
条件处理函数:
-
-
- CASE表达式
- DECODE
-
#NVL函数
语法:NVL (expr1,expr2) 如果expr1不是null,返回expr1,否则返回expr2
#NVL2函数
语法:NVL2(expr1,expr2,expr3) 如果expr1不是null,返回expr2,否则返回expr3
#NULLIF函数
语法: NULLIF(expr1,expr2) 比较两个表达式,如果相等,返回null,否则,返回第一个表达式
#COALESCE函数
COALESCE (表达式1, 表达式2, ... 表达式n)函数是对NVL函数的扩展。COALESCE函数的功能是返回第一个不为空的参数,参数个数不受限制。
##函数的嵌套
##本章总结
- 单行函数介绍
- 字符函数的使用
- 数值函数的使用
- 日期函数的使用
- 转换函数的使用
- 通用函数的使用
- 函数嵌套