Oracle第二课--函数笔记
Oracle函数
Oracle函数大集合。另外还有练习题。
--日期函数
-- 1、sysdate服务器时间====date类型
select sysdate from dual;
-- 2、systimestamp 服务器时间===timestamp类型
--2018-10-18 14:27:14:753000 +08:00
select systimestamp from dual;
-- 受时区影响的服务器时间
select current_date from dual;
-- 2018-10-18 14:29:07:504000 +08:00
-- 2018-10-18 15:33:23:491000 ASIA/TOKYO
select current_timestamp from dual;
-- 修改当前会话时区--东八区 Asia/Shanghai
alter session set time_zone='Asia/Tokyo';
-- 一起查询对比四个时间的区别
select sysdate,systimestamp,current_date,
current_timestamp from dual;
-- 3、to_char(日期,格式)将日期转换成字符串
/*
d 一周当中的第几天 1-7
dd 月份中的某一天 1-31
day 一周中的某一天，和语言设置有关
ddd 一年中第一天 1-365
dl 长时间格式
ds 短时间格式
yyyy 四位数的年
mm 月
mi 分
ss 秒
ff 毫秒
iw 一年中的第几周 1-53
hh24 24小时制
*/
select to_char
(current_timestamp,'yyyy-mm-dd hh24:mi:ss')
from dual;
-- day获取的星期受语言环境影响
select to_char
(current_timestamp,'yyyy-mm-dd day')
from dual;
--ddd 获取一年中的第几天
select to_char
(current_timestamp,'yyyy-mm-dd ddd')
from dual;
--iw 获取一年中第几周
select to_char
(current_timestamp,'yyyy-mm-dd iw')
from dual;
-- dl 年月日 星期---Thursday, October 18, 2018
select to_char(current_timestamp,'dl') FROM dual;
-- ds 年月日 10/18/2018
select to_char(current_timestamp,'ds') FROM dual;
-- to_date(字符，格式) -- day of month must be between 1 and last day of
month
select to_date('2018-09-30','yyyy-mm-dd')
from dual;
-- 6、增加月份
select add_months(sysdate,3) from dual;
select add_months
(to_date('2018-3-22','yyyy-mm-dd hh24:mi:ss'),2)
from dual;
-- 7、last_day 返回日期中指定月的最后一天
select last_day(sysdate) from dual;
-- 8、months_between计算两个日期之间的月份差
select months_between
(sysdate,
to_date('2018-3-22','yyyy-mm-dd hh24:mi:ss'))
from dual;
--
select * from emp;
-- 获取员工入职的月份
select ename,
months_between(sysdate,hiredate) as "dd"
from emp order by "dd" desc;
-- 星期一是一周的第二天
select to_char(sysdate,'d') from dual;
-- next_day(date,x)
-- 周日=1 星期一=2 以此类推
-- 从指定日开始，返回下一个星期X是哪一天
select next_day(sysdate,2) from dual;
-- extract 返回日期/时间单独部分
-- year、month\day\hour\minute\second
select extract(year from sysdate) from dual;
-- 二、数学函数 Math
-- 1、abs 绝对值
select abs(-17) from dual;
-- 2、ceil(x) 返回大于等于x的最小整数值
select ceil(5.7) from dual;
-- floor(x) 返回小于等于x的最大整数
select floor(5.7) from dual;
-- power(x,y) 求x的y次幂
select power(10,3) from dual;
-- mod(x,y) 求x除以y的余数
select mod(10,3) from dual;
-- round(x,[y]) 返回四舍五入的值
select round(4.5) from dual;
-- sqrt(x) 返回x的平方根
select sqrt(9) from dual;
-- trunc(x,[y]) 返回x按照精度y截取后的值，
--y为正，表示保留的小数位数，
--y为负，表示左边y位变成0
select trunc(12.987,2) from dual;-- 12.98
select trunc(12.987,-2) from dual;-- 0
select trunc(112.987,-2) from dual;-- 100
select trunc(4312.987,-2) from dual;-- 4300
-- y为0，或者不写，取整
select trunc(4312.987,0) from dual;-- 4312
-- 字符函数
-- 1、chr(x) 将数字以AscII码的方式转成字符
select chr(97) from dual;
--2、ascii 将字符转成数字
select ascii('喻') from dual;
-- contact 连接字符串 Oracle中，字符串相连用'||'
select concat('fuck','you') from dual;
-- 4、ltrim |我爱 Oracle | 去掉左边的空格
select concat('|',ltrim(' 我爱 Oracle '))
from dual;
-- 5、trim 删除两边的空格
-- 6、upper 转换成大写
-- 7、lower 转换成小写
-- 8、replace(string,origstr,[replacestr])
-- 你不是很喜欢程序么
select replace('你爱程序么','爱','不是很喜欢')
from dual;
select replace('你爱程序么','程序')
from dual;
-- aabbccdd 全部替换
select replace('aa2bbc2cd2d','2') from dual;
-- translate 替换，功能比replace更强大
-- @#c##@@ef b被#代替 a被@代替，d对应的是空值，移除
select translate('abcbbaadef','bad','#@')
from dual;
select translate('我爱你','我你','你我') FROM
dual;
-- rpad(string,x,[str2])
-- 截取/填充x个长度，不足的话右边用指定的字符来填充
select rpad('abcd',5,'m') FROM dual;-- abcdm
select rpad('abcdefg',5,'m') FROM dual;
-- 汉字占两个字节
select rpad('单行单列',10,'猪') from dual;
-- ‘11猪 ’
select rpad('11',9,'猪') from dual;
-- lpad(str,x,[str2])
-- 截取/填充x个长度，不足的话【左】边用指定的字符来填充
-- substr(str,start,[length])
-- 截取子串，从start开始，截取中包括start的位置
-- 字符索引从1开始
SELECT substr('ABCDEFG',2,4) from dual;--BCDE
SELECT substr('ABCDEFG',2) from dual;--BCDEFG
-- 索引超出，不会异常，直接给null
SELECT substr('ABCDEFG',8,1) from dual;
-- length 求字符串的长度
SELECT length('YF1705') from dual;
-- 中文以字符数量返回长度=4
select length('中科韬睿') from dual;
-- 转换函数
-- 1、decode(条件,值1,翻译1,值2,翻译2……)
select ename,job from emp;
select ename,job,
decode(job,
'SALESMAN','销售员',
'MANAGER','经理',
'ANALYST','分析师') from emp;
-- case
/*
语法一,和decode函数一样，做等值替换
case 表达式
when 值1 then 返回值1
when 值2 then 返回值2
else 返回值3
end
*/
select ename,job,
case job
when 'SALESMAN' then '销售员'
when 'MANAGER' then '经理'
when 'ANALYST' then '分析师'
else '其他'
end from emp;
-- 不写else，用null值替代
select ename,job,
case job
when 'SALESMAN' then '销售员'
when 'MANAGER' then '经理'
when 'ANALYST' then '分析师'
end from emp;
/*
语法二（更灵活）
case
when 条件1 then 返回值1
when 条件2 then 返回值2
else 返回值3
end
*/
select ename,sal from emp;
select ename,sal,
case
when sal<1000 then '屌丝'
when sal between 1000 and 5000 then '温饱'
when sal between 5001 and 10000 then '小康'
else '壕'
end from emp;
-- 空值函数
-- 1、nvl(e1,e2) e1为null，返回e2。e1不为空，返回自己
-- 两个参数类型要一致
select ename,comm from emp;
select ename,comm,nvl(comm,0) from emp;
-- 2、nvl2(e1,e2,e3)
-- e1不为空，则返回e2，为空则返回e3。
--如果e2和e3的类型不同，会将e3转成e2的类型
select ename,comm,nvl2(comm,'牛',0)
from emp;
-- 3、nullif(e1,e2)
-- e1和e2相等则返回null，不等则返回e1
select nullif(2,1) from dual; --2
select nullif(2,2) from dual; -- NULL
练习题（以下是Oracle函数的练习题）
-- 在Oracle中，请以scott登陆，完成下列的查询：
--A、请统计出每一个月入职的人数
SELECT ename,hiredate from emp;
-- 第一步
select ename,hiredate,
extract(month from hiredate) as "hm" from emp;
--
select extract(month from hiredate) as hm,
count(*) from emp
group by extract(month from hiredate)
order by hm;
-- B、请统计出每一个职务的平均工资（sal + comm）
select job,avg(sal+nvl(comm,0))
from emp group by job;
-- C、请查询出所有的员工，其中员工编号
-- 必须显示10位，不足部分使用0填充
select empno,ename from emp;
select rpad(empno,10,0),empno,ename
from emp;
-- D、请统计出每一个部门的人数
-- E、请找出最高级别的领导的所有下属
-- 中工资最高的员工信息
select empno,ename,job,mgr from emp;
-- 错误写法
select empno,ename,job,max(sal) from emp;
-- 第一步 找到员工工资最高的用户
select * from emp where sal=(select max(sal) from emp)
-- 第二步 查找权限最高的mrg=null的用户的子级信息
/* 这里利用了connect BY prior递归查询，因为emp中的等级很多。
start with 子句：遍历起始条件
connect by 子句：连接条件。
关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；
prior跟子结点列subid放在一起，则往叶子结点方向遍历，
order by 子句：排序
*/
select level as "等级", empno, ename, mgr,(sal+nvl(comm,0)) newsal
FROM emp
where level>1 --
start with mgr is null
connect BY prior empno = mgr
order by newsal desc;
-- 根据如上结果，可以获取工资最高的用户信息
select max(sal+nvl(comm,0))
FROM emp
where level>1
start with mgr is null
connect BY prior empno = mgr
order by level;
-- 第三步 找到最高工资属于权限最高的mgr=NULL的用户的子级的信息
-- 最高级别的子级中工资最高的记录和emp，联合查询，条件是工资相等，然后得出员
工信息
select e2.* from emp e2 join (select max(sal+nvl(comm,0)) sal
FROM emp
where level>1
start with mgr is null
connect BY prior empno = mgr
order by level) e1 on e2.sal=e1.sal;
请先 后发表评论～