数据库中的函数简介

目录

介绍

常用字符函数

常用数值函数

常用日期函数

null相关的常用函数

常用聚合函数

---

介绍

本文主要介绍SQL Server,Oracle数据库中常用的相关函数。如要了解有关SQL Server 的更多函数，可以访问https://docs.microsoft.com/zh-cn/sql/odbc/reference/appendixes/appendix-e-scalar-functions?view=sql-server-2017。Oracle的找了下，在官网没有搜索到，也许下寻找的姿势不对吧。如果由知道的，欢迎告知。

 

常用字符函数

函数名	格式	描述	应用场景	适用数据库	举例	代码
upper	upper(str)	将str转换为大写	当不确定记录中某个列值到底保存的是大写还是小写时，可以通过此函数将列值转成大写，进行明确比较	SQL Server,Oracle	查询员工位SCOTT的员工信息	select * from emp where upper(ename)='SCOTT'
lower	lower(str)	将str转换为小写	当不确定记录中某个列值到底保存的是大写还是小写时，可以通过此函数将列值转成小写，进行明确比较	SQL Server,Oracle	查询员工位SCOTT的员工信息	select * from emp where lower(ename)='scott'
len	len(str)	返回str长度	计算某列保存的值的长度	SQL Server	查询员工姓名及姓名长度	select ename,len(ename) from emp
length	length(str)	返回str长度	计算某列保存的值的长度	Oracle	查询员工姓名及姓名长度	select ename,lengnth(ename) from emp
lengthb	lengthb(str)	返回str字节长度	计算某列保存的值的长度	Oracle	查询员工姓名及姓名长度	select ename,lenghtb(ename) from emp
substring	substring(str,start,[lenght])	返回str从start位置开始，往后length位字符串，如果省略length参数，则会截取到最后一位	对字符串进行截取	SQL Server	查询所有员工的员工姓名，并从员工姓名第2位开始截取4位长度的字符串	select ename,substring(ename,2,4) from emp
substr	substr(str,start,[lenght])	返回str从start位置开始，往后length位字符串，如果省略length参数，则会截取到最后一位	对字符串进行截取	Oracle	查询所有员工的员工姓名，并从员工姓名第2位开始截取5位长度的字符串	select ename,substr(ename,2,4) from emp
replace	replace(str1,str2,str3)	用str3替换str1中所有的str2	对字符串进行部分替换	SQL Server,Oracle	查询所有员工的员工姓名，并将姓名中的字符'A'用字符'$'替换	select ename,replace(ename,'A','$') from emp
left	left(str,length)	返回str从左边开始length位字符串	对字符串进行靠左截取	SQL Server	查询所有员工姓名，并从员工左边开始截取两位字符串	select ename,left(ename,2) from emp
right	right(str,length)	返回str从右边开始length位字符串	对字符串进行靠右截取	SQL Server	查询所有员工姓名，并从员工右边开始截取两位字符串	select ename,right(ename,2) from emp
ltrim	ltrim(str)	去除str左侧的空格	去除字符串左边的空格	SQL Server,Oracle	查询所有员工姓名，并去掉员工姓名左边空格后的字符串	select ename,ltrim(ename) from emp
rtrim	rtrim(str)	去除str右侧的空格	去除字符串右边的空格	SQL Server,Oracle	查询所有员工姓名，并去掉员工姓名右边空格后的字符串	select ename,rtrim(ename) from emp
trim	trim(str)	去除str两侧的空格	去除字符串两边的空格	Oracle	查询所有员工姓名，并去掉员工姓名两边空格后的字符串	select ename,trim(ename) from emp
reverse	reverse(str)	反转str	将字符串左右反转	SQL Server,Oracle	查询员工所有姓名，并将员工姓名字符串反转	select ename,reverse(ename) from emp
charindex	charindex(str1,str2[,start])	从start开始查找str1在str2中出现的位置	查询一个字符串在另一个字符串中出现的位置	SQL Server	查询所有员工姓名，并查询字符D在员工姓名中出现的位置	select ename,charindex('D',ename) from emp
instr	instr(str1,str2[,start[,number]])	从start开始查找str2在str1中从start位开始第number次出现的位置	查询一个字符串在另一个字符串中出现的位置	Oracle	查询所有员工姓名，并查询字符L在员工姓名中从第1个位置开始第2次出现的位置	select ename,instr(ename,'L',1,2) from emp
patindex	patindex('%pattern%',expression)	返回pattern在express中第一次出现的位置	返回指定表达式中某模式第一次出现的其实位置，此函数支持适用通配符搜索	SQL Server	查询所有员工姓名，并查询D在员工姓名中第1次出现的位置	select ename,patindex('%D%',ename) from emp

 

常用数值函数

函数名	格式	描述	应用场景	适用数据库	举例	代码
trunc	trunc(number[,decimals])	实现数值的截取。其中number是待截取的数值，decimals指明需要保留的小数点后的位数。默认为0，截取所有小数；为负数，表示往小数点左侧截取的位数，相应的整数用0代替。对截取 数字进行直接截断，不考虑四舍五入	对数值进行直接截取	Oracle	5.77进行多次截取，观察区别	select trunc(155.77) from dual;select trunc(155.77，1) from dual;select trunc(155.77，-1) from dual;
mod	mod(number1,number2)	实现取余操作。其中number1是被除数，number2是除数，结果返回余数	对数值取余	Oracle	11对3取余	select mod(11,3) from dual
round	round(number[,decimals])	实现数值的截取。其中number是待截取的数值，decimals指明需要保留的小数点后的位数。默认为0，截取所有小数；为负数，表示往小数点左侧截取的位数，相应的整数用0代替。对截取 数字进行四舍五入	对数值进行四舍五入截取	Oracle	对155.77进行多次截取，观察区别	select round(155.77) from dual;select round(155.77，1) from dual;select round(155.77，-1) from dual;
round	round(n1,n2[,n3])	SQL Server数据库中的round不同于Oracle数据库中的round。SQL Serve中的round具有Oracle中的round和trunc两个函数的功能，n1是要截取的数字，n2是要保留的小数位数，n3指定要不要四舍五入，n3为0要四舍五入，n2不为0则直接截取	对数值进行截取	SQL Server	对155.77进行多次截取，观察区别	select round(155.77,1,0) from dual;select round(155.77,1,1) from dual;
abs	abs(number)	获取数值的绝对值	计算差距	SQL Server,Oracle	查询月薪距离3000美元不超过100美元的员工信息	select * from emp where abs(asl-3000)<100
ceil	ceil(number)	取大于等于数值number的最小整数	向上取整	Oracle	对1000.1进行向上取整	select ceil(1000.1) from dual
ceiling	ceiling(number)	取大于等于数值number的最小整数	向上取整	SQL Server,Oracle	对1000.1进行向上取整	select ceiling(1000.1) from dual
floor	floor(number)	取小于等于数值number的最大整数	向下取整	SQL Server,Oracle	对1000.1进行向下取整	select floor(1000.1) from dual
power	power(number)	进行幂运算，其中number为底数，power是指数	幂运算	SQL Server,Oracle	计算2的10次方	select power(2,10) from dual

 

常用日期函数

函数名	格式	描述	应用场景	适用数据库	举例	代码
to_date	to_date(str,format)	to_date函数属于Oracle中使用非常频繁的函数，它将字符串转为日期类型	把字符串类型转为日期类型	Oracle	查询1987年元旦之后入职的员工	select * from emp where hiredate>to_date('1987.01.01','yyyy.mm.dd')
months_between	months_between(date1,date2)	计算date1距离date2的月数	计算两个日期的间隔月数	Oracle	查询员工的姓名及入职日期	select ename,months_between(sysdate,hiredate) from emp
add_months	add_months(date,number)	计算date之后number个月后的日期	将日期参数加上若干个月份得到新的日期	Oracle	查询1个月后的日期	select add_months(sysdate,1) from dual
last_day	last_day(date)	计算date所在月份的最后一天的日期	计算月底日期	Oracle	计算本月月底的日期	select last_day(sysdate) from dual
next_day	next_day(date,number)	计算date之后的下一个星期几，number为1代表周日，以此类推	计算下一个星期几	Oracle	计算下一个星期日	select next_day(sysdate) from dual
dateadd	dateadd(interval,number,date)	在date的基础上增加number个interval，返回得到的日期	返回已添加指定时间间隔的日期	SQL Server	计算明年的这个时候	select dateadd(year,1,getdate())
datediff	datediff(interval,date1,date2)	计算date1和date2，相差了几个interval	计算两个日期的间隔	SQL Server	查询所有员工的姓名及工龄	select ename,datediff(year,hiredate,getdate()) from emp
year	year(date)	计算date所在年份	计算日期年份	SQL Server	查询当前年份	select year(getdate())
month	month(date)	计算date所在月份	计算日期月份	SQL Server	查询当前月份	select month(getdate())
day	day(date)	计算date是所在月份中的第几日	计算日期是月份中的第几日	SQL Server	查询当前日期是当前月份中的第几日	select day(getdate())
isdate	isdate(p)	判断参数值是否是一个日期类型，是日期类型返回1，不是日期类型返回0	判断参数是否为日期类型	SQL Server	判断getdate()和bsoft是否是日期类型	select isdate(getdate()),isdate('bsoft')

 

null相关的常用函数

函数名	格式	描述	应用场景	适用数据库	举例	代码
isnull	isnull(check_expression,replacement_value)	如果check_expression不为null则返回check_expression，否则返回replacement_value，check_expression和replacement_value可以为任意数据类型，而且check_expression的类型可以不同于replacement_value的类型	要获取明确的值，但是要获取的值中有可能存在不确定数值时，往往需要将不确定的值替换为指定的值，这个场景就需要用到isnull函数	SQL Server	获取员工的工号、姓名、工资。当工资为空时，用0代替	select empno,ename,isnull(sal,0) from emp
nvl	nvl(check_expression,replacement_value)	如果check_expression不为null则返回check_expression，否则返回replacement_value，check_expression和replacement_value可以为任意数据类型，而且check_expression的类型可以不同于replacement_value的类型	要获取明确的值，但是要获取的值中有可能存在不确定数值时，往往需要将不确定的值替换为指定的值，这个场景就需要用到nvl函数	Oracle	获取员工的工号、姓名、工资。当工资为空时，用1代替	select empno,ename,nvl(sal,1) from emp
nvl2	nvl2(expr1,expr2,expr3)	nvl2是nvl的补充，如果exp1不为null则返回expr2，否则返回expr3	根据条件是否为空，分别替换为不同的两个值	Oracle	获取员工的工号、姓名、年薪（薪资X12+佣金）	select empno,ename,nvl2(comm,sal*12+comm,sal*12) from emp
nullif	nullif(expr1,expr2)	如果expr1和expr2相等则返回null，否则返回expr1，其中expr1不能为null。Isnull是汉族null条件时，用只当值替换null，而nullif刚好相反，他是满足一定条件后，用null值替换非null值。整数因为这层含义，所以nullif的第一个参数不能为null	当满足某个条件时，用null值替换非null值	SQL Server,Oracle	显示员工信息时，隐藏大老板的姓名	select empno,ename,nullif(ename,'KING') from emp
coalesce	coalesce(expression[,…n])	从左向右判断参数，返回抵押给不为空的参数。如果都为空。参数个数最少为两个，所有参数类型必须相同或者 能够隐式转型，是isnull的扩展	返回第一个不为空的参数	SQL Server,Oracle	显示员工的工号、姓名、年薪（年薪由工资和提成组成）	select empno,ename,coalesce(sal*12+comm,sal*12,comm) from emp

 

常用聚合函数

函数名	格式	描述	应用场景	适用数据库	举例	代码
count	count(*)	统计记录数函数	分组查询中，查询每组的记录数	SQL Server,Oracle	查询员工总数	select count(*) from emp
sum	sum(column)	求和函数	分组查询中，查询每组某列或某个表达式的和	SQL Server,Oracle	查询每个部门的部门号及该部门所有员工的月薪之和	select deptno,sum(sal) from emp group by deptno
avg	avg(column)	平均数函数	分组查询中，查询每组某列或某个表达式的平均值	SQL Server,Oracle	查询每个部门的部门号及该部门所有员工的月薪之平均值	select deptno,avg(sal) from emp group by deptno
min	min(column)	求最小值函数	分组查询中，查询每组某列或某个表达式的最小值	SQL Server,Oracle	查询每个部门的部门号及该部门所有员工的月薪之最小值	select deptno,min(sal) from emp group by deptno
max	max(column)	求最大值函数	分组查询中，查询每组某列或某个表达式的最大值	SQL Server,Oracle	查询每个部门的部门号及该部门所有员工的月薪之最大值	select deptno,max(sal) from emp group by deptno