目录
介绍
常用字符函数
常用数值函数
常用日期函数
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的找了下，在官网没有搜索到，也许下寻找的姿势不对吧。如果由知道的，欢迎告知。
常用字符函数 函数名格式描述应用场景适用数据库举例代码upperupper(str)将str转换为大写当不确定记录中某个列值到底保存的是大写还是小写时，可以通过此函数将列值转成大写，进行明确比较SQL Server,Oracle查询员工位SCOTT的员工信息select * from emp where upper(ename)='SCOTT'lowerlower(str)将str转换为小写当不确定记录中某个列值到底保存的是大写还是小写时，可以通过此函数将列值转成小写，进行明确比较SQL Server,Oracle查询员工位SCOTT的员工信息select * from emp where lower(ename)='scott'lenlen(str)返回str长度计算某列保存的值的长度SQL Server查询员工姓名及姓名长度select ename,len(ename) from emplengthlength(str)返回str长度计算某列保存的值的长度Oracle查询员工姓名及姓名长度select ename,lengnth(ename) from emplengthblengthb(str)返回str字节长度计算某列保存的值的长度Oracle查询员工姓名及姓名长度select ename,lenghtb(ename) from empsubstringsubstring(str,start,[lenght])返回str从start位置开始，往后length位字符串，如果省略length参数，则会截取到最后一位对字符串进行截取SQL Server查询所有员工的员工姓名，并从员工姓名第2位开始截取4位长度的字符串select ename,substring(ename,2,4) from empsubstrsubstr(str,start,[lenght])返回str从start位置开始，往后length位字符串，如果省略length参数，则会截取到最后一位对字符串进行截取Oracle查询所有员工的员工姓名，并从员工姓名第2位开始截取5位长度的字符串select ename,substr(ename,2,4) from empreplacereplace(str1,str2,str3)用str3替换str1中所有的str2对字符串进行部分替换SQL Server,Oracle查询所有员工的员工姓名，并将姓名中的字符'A'用字符'$'替换select ename,replace(ename,'A','$') from empleftleft(str,length)返回str从左边开始length位字符串对字符串进行靠左截取SQL Server查询所有员工姓名，并从员工左边开始截取两位字符串select ename,left(ename,2) from emprightright(str,length)返回str从右边开始length位字符串对字符串进行靠右截取SQL Server查询所有员工姓名，并从员工右边开始截取两位字符串select ename,right(ename,2) from empltrimltrim(str)去除str左侧的空格去除字符串左边的空格SQL Server,Oracle查询所有员工姓名，并去掉员工姓名左边空格后的字符串select ename,ltrim(ename) from emprtrimrtrim(str)去除str右侧的空格去除字符串右边的空格SQL Server,Oracle查询所有员工姓名，并去掉员工姓名右边空格后的字符串select ename,rtrim(ename) from emptrimtrim(str)去除str两侧的空格去除字符串两边的空格Oracle查询所有员工姓名，并去掉员工姓名两边空格后的字符串select ename,trim(ename) from empreversereverse(str)反转str将字符串左右反转SQL Server,Oracle查询员工所有姓名，并将员工姓名字符串反转select ename,reverse(ename) from empcharindexcharindex(str1,str2[,start])从start开始查找str1在str2中出现的位置查询一个字符串在另一个字符串中出现的位置SQL Server查询所有员工姓名，并查询字符D在员工姓名中出现的位置select ename,charindex('D',ename) from empinstrinstr(str1,str2[,start[,number]])从start开始查找str2在str1中从start位开始第number次出现的位置查询一个字符串在另一个字符串中出现的位置Oracle查询所有员工姓名，并查询字符L在员工姓名中从第1个位置开始第2次出现的位置select ename,instr(ename,'L',1,2) from emppatindexpatindex('%pattern%',expression)返回pattern在express中第一次出现的位置返回指定表达式中某模式第一次出现的其实位置，此函数支持适用通配符搜索SQL Server查询所有员工姓名，并查询D在员工姓名中第1次出现的位置select ename,patindex('%D%',ename) from emp
常用数值函数 函数名格式描述应用场景适用数据库举例代码trunctrunc(number[,decimals])实现数值的截取。其中number是待截取的数值，decimals指明需要保留的小数点后的位数。默认为0，截取所有小数；为负数，表示往小数点左侧截取的位数，相应的整数用0代替。对截取 数字进行直接截断，不考虑四舍五入对数值进行直接截取Oracle5.77进行多次截取，观察区别select trunc(155.77) from dual;select trunc(155.77，1) from dual;select trunc(155.77，-1) from dual;modmod(number1,number2)实现取余操作。其中number1是被除数，number2是除数，结果返回余数对数值取余Oracle11对3取余select mod(11,3) from dualroundround(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;roundround(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;absabs(number)获取数值的绝对值计算差距SQL Server,Oracle查询月薪距离3000美元不超过100美元的员工信息select * from emp where abs(asl-3000)to_date('1987.01.01','yyyy.mm.dd')months_betweenmonths_between(date1,date2)计算date1距离date2的月数计算两个日期的间隔月数Oracle查询员工的姓名及入职日期select ename,months_between(sysdate,hiredate) from empadd_monthsadd_months(date,number)计算date之后number个月后的日期将日期参数加上若干个月份得到新的日期Oracle查询1个月后的日期select add_months(sysdate,1) from duallast_daylast_day(date)计算date所在月份的最后一天的日期计算月底日期Oracle计算本月月底的日期select last_day(sysdate) from dualnext_daynext_day(date,number)计算date之后的下一个星期几，number为1代表周日，以此类推计算下一个星期几Oracle计算下一个星期日select next_day(sysdate) from dualdateadddateadd(interval,number,date)在date的基础上增加number个interval，返回得到的日期返回已添加指定时间间隔的日期SQL Server计算明年的这个时候select dateadd(year,1,getdate())datediffdatediff(interval,date1,date2)计算date1和date2，相差了几个interval计算两个日期的间隔SQL Server查询所有员工的姓名及工龄select ename,datediff(year,hiredate,getdate()) from empyearyear(date)计算date所在年份计算日期年份SQL Server查询当前年份select year(getdate())monthmonth(date)计算date所在月份计算日期月份SQL Server查询当前月份select month(getdate())dayday(date)计算date是所在月份中的第几日计算日期是月份中的第几日SQL Server查询当前日期是当前月份中的第几日select day(getdate())isdateisdate(p)判断参数值是否是一个日期类型，是日期类型返回1，不是日期类型返回0判断参数是否为日期类型SQL Server判断getdate()和bsoft是否是日期类型select isdate(getdate()),isdate('bsoft')
null相关的常用函数 函数名格式描述应用场景适用数据库举例代码isnullisnull(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 empnvlnvl(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 empnvl2nvl2(expr1,expr2,expr3)nvl2是nvl的补充，如果exp1不为null则返回expr2，否则返回expr3根据条件是否为空，分别替换为不同的两个值Oracle获取员工的工号、姓名、年薪（薪资X12+佣金）select empno,ename,nvl2(comm,sal*12+comm,sal*12) from empnullifnullif(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 empcoalescecoalesce(expression[,…n])从左向右判断参数，返回抵押给不为空的参数。如果都为空。参数个数最少为两个，所有参数类型必须相同或者 能够隐式转型，是isnull的扩展返回第一个不为空的参数SQL Server,Oracle显示员工的工号、姓名、年薪（年薪由工资和提成组成）select empno,ename,coalesce(sal*12+comm,sal*12,comm) from emp
常用聚合函数 函数名格式描述应用场景适用数据库举例代码countcount(*)统计记录数函数分组查询中，查询每组的记录数SQL Server,Oracle查询员工总数select count(*) from empsumsum(column)求和函数分组查询中，查询每组某列或某个表达式的和SQL Server,Oracle查询每个部门的部门号及该部门所有员工的月薪之和select deptno,sum(sal) from emp group by deptnoavgavg(column)平均数函数分组查询中，查询每组某列或某个表达式的平均值SQL Server,Oracle查询每个部门的部门号及该部门所有员工的月薪之平均值select deptno,avg(sal) from emp group by deptnominmin(column)求最小值函数分组查询中，查询每组某列或某个表达式的最小值SQL Server,Oracle查询每个部门的部门号及该部门所有员工的月薪之最小值select deptno,min(sal) from emp group by deptnomaxmax(column)求最大值函数分组查询中，查询每组某列或某个表达式的最大值SQL Server,Oracle查询每个部门的部门号及该部门所有员工的月薪之最大值select deptno,max(sal) from emp group by deptno
