Sql函数

SQL分为单行函数和多行函数。

单行函数的特征：1.对单行操作 2.每行返回一个结果 3.有可能返回类型与原参数类型不一致 4.单行函数可写在SELECT,FORM,WHERE,ORDER BY子句中 5.有些函数没有参数有些函数有参数，可以嵌套。

单行函数分类如下：

INITCAP(列名/表达式)函数是将每个单词首字母转为大写其余字母小写

LOWER(列名/表达式) WHERE ename=LOWER('JPB')    同理 UPPER是转为大写

CONCAT：同 ‘’||‘’ 用来连接两个值 SELECT ename || eno FROM emp   同 SELECT concat(ename,eno) FROM emp

SUBSTR(colum/expression,n1,n2 ):从n1位置截取长度为n2的子串。 I.若n2不写，则从n1位置开始截取所有的子串。II.若n1为负数则从右向左数abs(n1)的位置开始向右截取n2长度

LENGTH(colum/expression):取字符串长度。

例：用首字母大写，其他字母小写显示雇员的名字和长度，名字以A,J,M开头的

select initcap(ename),length(ename) from emp where substr(ename,1,1) in ('A','J','M')  

instr('String','r')，结果为3.即返回子串r在String中第一次出现的位置

lpad(sal,10,'*'):结果为******5000,即从左边开始填充*，填充完后总长度为10.

rpad 同上

replace('abc','b','d'):结果为adc,即把abc中的子串b用d来替换掉。

trim('s' from 'ssdbcs'):结果为dbc，即去除头部和尾部中与s子串相同的部分，注意这里的头部其实执行了两次，把前面的ss都去掉了。

例：查询名字包含‘a'的员工姓名，不区分大小写

select ename from emp where instr(upper(ename),'A')>0  若不包含则inser=0    或       select ename from emp where upper(ename) like ('%A%')

单行函数之数值函数

round(123.426,2)，结果为123.43，以四色五入的方式保留小数点后两位。

trunc(123.426,2),结果为123.42，截取小数点后两位。 trunc默认为截取整数，若第二个参数为负数，例trunc('123.426',-1)结果为120

mod(9,2),结果为1，即取余数

日期函数

Oracle中默认的日期格式是DD-MM-RR     对于RR格式，是否设置的是上个世纪，其实数据库很智能的，它会自动判断是本世纪还是上个世纪。

日期可与数字进行加减运算，如'10-AUG-18'+15结果是‘25-AUG-18’

日期与日期进行加减运算，如'10-AUG-18' - '4-AUG-18'结果是四舍五入后的6天。

SYSDATE：返回系统当前时间，例 (SYSDATE-hiredate)/7  可以得到星期数

常用的日期函数

MONTHS_BETWEEN(SYSDATE,hiredate)注意这里是SYSDATE-hiredate，顺序是前者减去后者的月数

ADD_MONTHS(hiredate,3),代表着实习三个月后转正，即入职月数加3

 NEXT_DAy('02-2月-18'，‘星期一’)返回18年2月2日之后的第一个星期一，亦可以是第一个周日。

LAST_DAY（‘02-2月-18’）返回当前月的最后一天  SELECT LAST_DAY(‘02-2月-18’) FROM DUAL(伪表)

ROUND(hiredate,'MONTH')按照月份进行四舍五入操作如图中对日期进行的操作。

TRUNC(hiredate,'MONTH')这里的月数没有进行四舍五入。

 

EXTRACT(month from hiredate)把月份截取出来。

转换函数

转换函数分为显示转换和隐式转换，这里我只写显示转换，隐式转换需按照Oracle规则

显示转换通常在，日期、数值、字符之间相互转换。

转换规则如图所示

TO_CHAR用于日期型

TO_CHAR(date,'fmt'):TO_CHAR（hiredate，‘MM/YY’）注意：日期格式模型必须用单引号括起来且对大小敏感，可以包含任何有效的日期元素，使用逗号将日期与日期模型分开

       TO_CHAR(SYSDATE,'DD/MM/YY HH24/MI/SS')可以求出当前时间的日期和时分秒。

TO_CHAR用于数值型

TO_CHAR(sal,'$99,999') 逗号代表显示千位符，结果是$3,000

TO_NUMBER('$123,234,345','$999,999,999') 注意要转化成number型的char必须是由数字组成的。

TO_DATE('2018-07-28 16/16','YYYY-MM-DD HH24/MI')注CHAR类型的数据必须是可以转为日期格式的数据。

通用函数

NVL(exp1,exp2):若exp1为null则返回exp2否则返回exp1。

NVL2(exp1,exp2,exp3)若exp1不是null,返回exp2否则返回exp3（有点像三目运算符）。

NULLIF(exp1,exp2)比较两个表达式，若想等返回NULL否则返回第一个

COALESCE(表达式1，表达式2·······)返回第一个不是null的参数

CASE和DECODE函数

将满足条件的部门改为指定名称：