单行函数，集合，数据类型

数据类型

数字：number、number(n)、number(n,p) 字符：char(n)--存储定长字符串，最多2000个字节；varchar2(n)--存储变长字符串，最多4000个字节 日期：date--精确到秒；timestamp--精确到毫秒

单行函数

单行函数：数据的加工机，接收1个或多个数据，输出一个新的数据

数字函数：

abs(n)：返回n的绝对值

select abs(1.2),abs(-3),abs(0) from dual;

ceil(n)：

向上取整，返回大于等于n的最小整数

floor(n)：

向下取整，返回小于等于n的最大整数

select ceil(1.2), ceil(3), ceil(-32.44), floor(1.2), floor(3), floor(-32.44) from dual

round(n,p)：

四舍五入，如果p>0小数点保留p位，p=0精确到整数位，p<0小数点前至少有p个0，如果p缺省相当于p=0 select round(8327368.3762937) a, round(8327368.3762937, 0) b, round(8327368.3762937, 2) c, round(8327368.3762937, 5) d, round(8327368.3762937, -1) e, round(8327368.3762937, -3) f from dual

trunc(n,p)：

直接截断，如果p>0小数点保留p位，p=0精确到整数位，p<0小数点前至少有p个0，如果p缺省相当于p=0 select trunc(8327368.3762937) a, trunc(8327368.3762937, 0) b, trunc(8327368.3762937, 2) c, trunc(8327368.3762937, 5) d, trunc(8327368.3762937, -1) e, trunc(8327368.3762937, -3) f from dual

mod(m,n)：返回m/n的余数

select mod(13,2),mod(12,2) from dual

ASCII码

select ascii('a'),ascii('A') from dual; select chr(97),chr(65) from dual

a=97,A=65, 回车=10

字符函数：

 

大小写

lower(c)：全部转成小写 upper(c)：全部转成大写 initcap(c)：首字母大写其他小写，以单词为单位 select lower('sDSDSssd'), upper('sDSDSssd'), initcap('sDSDSssd'), initcap('sDSDSssd dds') from dual

长度：

length(c)：返回c的字符个数 lengthb(c)：返回c的字节个数，GBK编码一个汉字两个字节，UTF-8编码一个汉字三个字节

select length('ssd123你好#,'), lengthb('ssd123你好#,') from dual

去前后空格：

ltrim(c)：去除头空格 rtrim(c)：去除尾空格 trim(c)：去除头尾空格

select ltrim(' dsds s '), rtrim(' dsds s '), trim(' dsds s ') from dual

replace(c,c1,c2)：

查找替换，用c2替换c中的c1

select replace(' sdds ds ', ' ', ''), replace(' sdds ds ', ' '), replace(' sdds ds ', ' ', '$') from dual

字符串的拼接：

• concat(c1,c2)--将c1和c2拼接到一起，只能有两个参数，多个参数的拼接只能嵌套

  select 'a'||'b'||'c',concat(concat('a','b'),'c') from dual

格式化字符串：

以指定的长度输出，不满足去补特定的字符 lpad(c,l,c1)：将c格式化输出l位，不足用c1在前面填充 rpad(c,l,c1)：将c格式化输出l位，不足用c1在后面填充 select lpad('1', 3, '0'), lpad('11', 3, '0'), lpad('111', 3, '0'), lpad('1234', 3, '0'), rpad('1', 3, '0'), rpad('11', 3, '0'), rpad('111', 3, '0'), rpad('1234', 3, '0') from dual

截取字符串：

oracle中字符串的位置是从1开始的 substr(c,p,l)：从c中截取字符串，从p位开始截取（包含p位），往后截取l位的长度，如果p>0从前往后找到p的位置，如果p小于0从后往前找到p的位置；如果l缺省，则截取剩下所有的字符 select substr('1234567890', 1, 1), substr('1234567890', 1), substr('1234567890', 3, 2), substr('1234567890', 3, 5), substr('1234567890', -1, 1), substr('1234567890', -1), substr('1234567890', -5, 2) from dual

**查找子字符串

查找子字符串在目标字符串中出现的位置，找到返回其实际位置，找不到返回0** instr(c,c1,p,l)：从p位（含）开始找c中的c1第l次出现的位置，如果p大于0，从前往后找到p的位置，从p位开始往后找c1,；如果p小于0从后往前找P位，从p位开始往前找c1 如果后面的参数缺省，都相当于是1 select instr('addsaadsda', 'a'), instr('addsaadsda', 'a', 2), instr('addsaadsda', 'a', 2, 1), instr('addsaadsda', 'a', 2, 3), instr('addsaadsda', 'a', 2, 4), instr('addsaadsda', 'a', -2, 1), instr('addsaadsda', 'a', -5, 3), instr('addsaadsda', 'a', -2, 4) from dual

 

查找例题

--查找S开头的员工信息 select * from emp where instr(ename,'S',1,1)=1 --查找T结尾的员工信息 select * from emp where instr(ename,'T',-1,1)=length(ename) --查找第三个字符是L的员工信息 select * from emp where instr(ename,'S',3,1)=3 --查找含有S的员工信息 select * from emp where instr(ename,'S',1,1)>0 --查找不含有S的员工信息 select * from emp where instr(ename,'S',1,1)=0

 

日期函数

sysdate--

返回当前系统的日期，到秒 日期+一个数字 返回日期后N天（如果为负则是前N天）的日期 d1+n=d2 select sysdate,sysdate+10,sysdate-20,sysdate+1/24 from dual d1-d2=n，两个日期相减返回的是日期相差的天数

日期常量：

date'yyyy-mm-dd'

last_day(d)：

返回d所在月的最后一天的日期 select last_day(date '2021-04-26'), last_day(sysdate) from dual

add_months(d,n)：

返回d后n个月（n小于0前n个月）的日期

select add_months(sysdate, 1), add_months(sysdate, -24), add_months(sysdate, 48) from dual

months_between(d1,d2)：

返回d1与d2相差的月数 select months_between(sysdate, add_months(sysdate, -24)) from dual

trunc(d,fmt)：

按照指定精度进行截断，fmt可以有yyyy、mm、dd、hh24、mi、ss、q（季度）、d（周），从这个精度后都忽略

select sysdate, trunc(sysdate, 'yyyy'),--精确到年的起始时间 trunc(sysdate, 'mm'),--精确到当前月的起始时间 trunc(sysdate, 'dd'),--精确到当天的起始时间 trunc(sysdate, 'hh24'),--的起始时间 trunc(sysdate, 'mi'),--的起始时间 trunc(sysdate, 'q'),--的起始时间 trunc(sysdate, 'd')--的起始时间 from dual;

转换函数

1、字符和数字：

数字可以转成字符，数字类型的字符才可以转成数字 to_char(n)：把数字类型的列转成字符类型 to_number(c)：把字符类型的列转成数字类型，要求一定是数字类型的字符串

2、字符和日期：

日期可以转成任意日期格式的字符串，而日期格式字符才可以按照指定格式转换成日期 to_char(d,fmt)：fmt可以是yyyy、mm、dd、hh24、mi、ss、q（第几个季度）、d（这周的第几天）、day(星期几），中间可以加任意的分隔符

例子： to_char(日期，'mm')得到月份

select sysdate, to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'), to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'), to_char(sysdate, 'yyyy-mm-dd'), to_char(sysdate, 'yyyy'), to_char(sysdate, 'mm'), to_char(sysdate, 'dd'), to_char(sysdate, 'q'), to_char(sysdate, 'd'), to_char(sysdate, 'day') from dual to_date(c,fmt)：将日期格式的字符按照指定的格式转换成日期 select to_date('2021', 'yyyy'), to_date('2021-04', 'yyyy-mm'), to_date('2021-0405', 'yyyy-mmdd'), to_date('2021-04-05 12:12:12', 'yyyy-mm-dd hh24:mi:ss') from dual

其他函数

a、null值相关的

nvl(p1,p2):

• 如果p1为null返回p2，否则返回p1 select nvl(1, 2), nvl(null, 2) from dual

nvl2(p1,p2,p3)：

如果p1为null返回p3,否则返回p2 select nvl2(1, 2, 3), nvl2(null, 2, 3) from dual

• coalesce(p1,p2,p3……)：返回第一个不为null的参数 select coalesce(1, 2, 3, 4), coalesce(null, 2, 3, 4), coalesce(null, null, 3, 4), coalesce(null, null, null, 4) from dual

b、等值的case when 语句

• select decode(1, 2, 3), decode(1, 1, 2) from dual // null 2 --decode(p1,p2,p3)： case when p1=p2 then p3 end

• select decode(1, 2, 3,4), decode(1, 1, 2,4) from dual //4 2 --decode(p1,p2,p3,p4)：case when p1=p2 then p3 else p4 end

• select decode(1, 2, 3, 4, 5), decode(1, 2, 2, 1, 5) from dual // null 5 --decode(p1,p2,p3,p4,p5)：case when p1=p2 then p3 when p1=p4 then p5 end

• select decode(1, 2, 3, 4, 5,6), decode(1, 2, 2, 1, 5,6) from dual // 6 5 --decode(p1,p2,p3,p4,p5,p6)：case when p1=p2 then p3 when p1=p4 then p5 else p6 end

集合

并集：将多个查询结果集的结果合并到一起，要求每个查询结果集具有相同的列数，且每列的数据类型一致

union all:

第一个结果集拼上第二个结果集拼第三个……

union ：

对union all后的结果进行去重，排序 select distinct t.* from (select ename, empno, sal from emp union all select ename, empno, sal from emp) t order by 1, 2, 3

rollup :对列求和

select nvl(to_char(deptno),'合计'),count(*),sum(sal) from emp group by rollup(deptno)

minus：差集

显示出第一个集合中，第二个集合没有的列

select * from emp where deptno=10 minus select * from emp where deptno=20

intersect并集

select * from emp where deptno=10 intersect select * from emp