Oracle中的SQL函数（全）_心有—林夕_oracle sql

未知 02-07 4521

一.单行函数

注：dual 是一个虚表，不能保存任何数据，只有一个字段，一行记录

1.语义

单行函数 ?一条记录返回一个结果

2.字符函数 1.lower():转小写 ,upper()：转大写， initcap首字母大写 select lower(ename) from emp; 2.concat():连接两个值，等同|| select concat(ename,sal)from emp; 3.substr (‘’,n1,n2) n1指定位置，n2指定长度从1开始，省略n2，从n1开始截取到最后，n1为负值倒着数n个 select substr('hello',2,2) from dual; 4.length() 获取字符串长度 select length('hello')from dual; 5.instr(s1,s2,n1,n2) 返回s1中，字串S2从n1开始，第n2次出现的位置，n1,n2默认值为1 select instr('string','r')from dual; --结果为3 6.lpad(s1,n1,s2) s1被s2从左面填充n1个长度(总长度) select lpad('hi',3,'hello') from dual; --结果为 hhi 7.rpad(）同上只不过是从右面填充 8.trim() 去除字符串头部或尾部的字符 select trim('s' from 'sssis') from dual; --结果为 i 9.replace(s1,s2,s3) 把s1中的s2用s3替换 select replace('strrring','r','a')from dual; --结果为 staaaing 3.数值函数 1.round(n1,n2):四舍五入 n1数值 n2保留几位可取负值 select round(203,-1)from dual; --200 2.trunc(n1,n1):n1为数值n2为截取的长度可为负数 ? 不舍不入 select trunc(223,-2) from dual;--200 3.MOD(m,n) 取m/n的余数 select mod(223,2) from dual; --1 4.日期函数 1.Oracle中默认的日期格式为 RR格式

DD-MON-RR 1998年12月20日即‘20-12月--98’

当前年份（0-49），则取这世纪的（0-49），上世纪的（50，99）

2.sysdate系统当前日期 select sysdate from dual; 3.日期的加减 select (sysdate - hiredate)/365 年,(sysdate - hiredate) 天 from dual 4.months_between(d1,d2) 返回两日期相差的月数 select months_between(sysdate,hiredate) 月from emp; 5.add_months(d1,n1)d1为日期 ?返回d1过n1个月后的日期 select add_months(sysdate,1) from dual; 6.next_day(d,w) 返回d日期之后的星期w select next_day(sysdate,'星期五') from dual; 7.last_day(d) ? 返回指定日期月份的最后一天 select last_day(sysdate) from dual; 8.round(d,'m') 返回d日期四舍五入后的日期， m 可为month,year select round(sysdate,'month') from dual; 9.trunc(d,'m') ?返回d日期截取后的日期， m 可为month,year select trunc(sysdate,'month') from dual; 10.extract() ?返回指定日期中的年份，月份或者日 select sysdate, extract (year from sysdate) from dual; --2021 5.转换函数 1.to_char() 转换成字符 select to_char(hiredate,'day') from emp; --显示的是星期几 2.to_date(date,格式) ?转换成日期 select to_date('2020-10-10','yyyy-mm-dd') from dual; 3.to_number()转换为数值 select to_number('25') from dual; --只有这样的才能转为数字 6.通用函数 1.case 多条件比较 select ename,deptno, (case deptno when 10 then '销售部' when 20 then '研发部' when 30 then '管理部' else '无' end ) from emp; 2.nvl(par1,num) 将空值指成具体数值 select nvl(null,3) from dual; --3 3.coalesce(par1...) 返回第一个不为空的参数,参数不限 select coalesce(null,null,3) from dual; --3 二.多行函数??多个记录返回一个结果 1.五个聚合函数 1.max()计算最大值 select max(sal) from emp; 2.min():计算最小值 select min(hiredate) from emp; 3.avg():计算平均值 avg 将null值过滤了并且没把null值参与平均计算 select avg(comm) from emp;--550 select avg(nvl(comm,0))from emp;--157 4.sum():求和 select sum(hiredate) from emp; 5.count():计算总记录数不过滤null值，以上4个均过滤null值 select count(empno) from emp; 2.group by 分组函数 1.特点

写在where之后，order by之前

select 列表中所有普通字段（即没有聚合函数等的字段）都必须放在group by中

group by中出现的字段不一定出现在select中

处理多条数据返回一个结果也可以将表分成多个组可以将整个表作为一组

2.按照多列分组即group by 后面跟着多个字段 select deptno,job from emp where deptno=10 group by deptno,job; 3.如果条件是过滤聚合函数，不能用where进行过滤，只能通过having

having只能在分组的基础上进行的过滤的是分组函数

select deptno,max(sal) from emp group by deptno having max(sal)>500; 三.select 执行顺序 1.from 2.where 3.group by 4.having 5. select 6. orderby

注：函数之间可以相互嵌套