Oracle 常用函数
来源:互联网 发布:西北大学现代学院网络 编辑:程序博客网 时间:2024/05/29 04:36
Functions
Numeric
- abs/mod 绝对值/求模
- sqrt/power/exp 求幂
- ceil/floor/round 取整
- trunc 截取小数点
- sin/cos/ln/log 数学函数
Charactor
- chr/ascii ascii码
- length/lengthb 字符/字节长度
- initcap/upper/lower 字母大小写
- substr/translate/replace/instr 子串/替换
- ltrim/rtrim/lpad/rpad 修剪/填充
Datetime
select sysdate, systimestamp, current_date from dual;-- x 天之后的日期select sysdate + 365 from dual;-- x 个月后的日期select add_months(sysdate, -5) from dual;-- 相差几个月select months_between(to_date('20180501'), sysdate) from dual;-- 下周 x 的日期select next_day(sysdate, '星期一') from dual;select next_day(sysdate, 2) from dual; -- 1 代表星期天, 0 代表星期一, 类推-- 一个月的最后一天/第一天select last_day(sysdate) from dual;select trunc(sysdate,'MONTH') from dual;select trunc(sysdate,'YEAR') from dual;-- 获取日期的单个字段select extract(day from sysdate) from dual;-- 对日期进行截取select round(to_date('20170916'),'MONTH') from dual; -- 四舍五入select trunc(to_date('20170916'),'MONTH') from dual;
Nullable
- nvl/nvl2/nullif
Miscellaneous
decode/case when:
-- 生成数据create table sss (name varchar2(20), gender int);insert into sss values ('樱桃小丸子', 2);insert into sss values ('路飞', 1);insert into sss values ('大熊', 3);insert into sss values ('光头强', 1);commit;-- 查询,将 gender 的数字转化为男女-- 1. decode 函数的方式select name, decode(gender, 1, '雄性', 2, '雌性', '未知性别') "性别" from sss;-- 2. case when 的方式 select name, case gender when 1 then '雄性' when 2 then '雌性' else '未知性别' end "性别" from sss;-- 3. case when 的另一种方式 select name, case when gender <= 1 then '雄性' when gender >= 2 then '雌性' else '未知性别' end "性别" from sss;-- 给 emp 中的人加工资:---- 1000 元以下的,加 50%---- 2000 元以下的,加 30%---- 3000 元以下的,加 20%---- 其他人 5%create table emp3 as select * from emp;-- 如果要将所有的工资翻倍,这么写:update emp3 set sal = sal*2;-- 如果不同的人加不同的工资,需要用到 case when:update emp3 set sal = (case when sal <= 1000 then sal*1.5 when sal <= 2000 then sal*1.3 when sal <= 3000 then sal*1.2 else sal*1.05 end);-- 如果 1981/5/1 之前来公司的人按照上面调薪,其他人只调 1% 呢?-- [TODO]
Aggregate Function
聚合函数是用来统计每个分组的统计信息,它们要跟 group by 一起使用,用来将每个分组所有数据 聚合 成一条统计数据。
包括 max/min/count/avg/sum 等。
-- 按照部门进行分组统计的语句select deptno, -- 因为按照 deptno 分组,select 中只能有 deptno 字段 count(*), -- 每个分组多少条数据 max(sal), -- 每个分组工资的最大值 min(sal), -- 每个分组工资的最小值 avg(sal), -- 每个分组工资的均值 sum(nvl(comm, 0)) -- 每个分组奖金总和,因为奖金可能为 null,所以需要使用 nvl 进行去空 from emp group by deptno; -- 分组依据-- 可以用 having 对结果进行过滤-- 整个 select 语句执行顺序大致是: where -> group by -> having -> order byselect deptno, count(*), max(sal), min(sal), avg(sal), sum(comm) from emp group by deptno having avg(sal) > 2000;-- having 等价于嵌套的 where,即上面语句跟下面这条等效。select * from ( select deptno, count(*), max(sal), min(sal), avg(sal) asal, sum(comm) from emp group by deptno ) where asal > 2000;
Analytic Function
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
聚合函数统计的是一个分组的信息,聚合之后每个分组只能得到一条信息,但并不能够获取分组内成员的具体信息。
而分析函数可以详细显示分组内部的统计信息,它显示的信息更全面,语法也更复杂。
大致来说,分析函数的使用语法是这样的:
分析函数() over ([partition by xxx] order by yyy [rows/range ...])
解释:
- 一个分析函数,需要跟着一个窗口函数。
- 以 over 开始的部分叫 窗口函数, 它描述了要对哪些数据、按照什么方式进行分析的行为。
- 窗口函数内部分为三部分,其中 order by 是必须的,其它可选
partition by
指定了数据按照什么方式 分组/分区order by
指定了在分区之内,数据的排序方式rows/range
可以对分区的数据进行限定- 分析函数有很多,比如 row_number/rank/dense_rank/max/count 等,row_number 等有如下区别
ROW_NUMBER 返回连续的排位,不论值是否相等RANK 具有相等值的行排位相同,序数随后跳跃DENSE_RANK 具有相等值的行排位相同,序号是连续的
- 需要查询组本身的信息,用聚合函数 group by; 需要查询组成员的统计信息,需要用分析函数。
-- 最基本,窗口描述内只有 order byselect e.*, sum(sal) over (order by sal) from emp e;select e.*, row_number() over (order by sal) from emp e;-- 以组为单位,进行排序select e.*, sum(sal) over (partition by deptno order by sal) from emp e;select e.*, row_number() over (partition by deptno order by sal) from emp e;-- 比如,要查询每个组的最高工资,可以用聚合函数select deptno, max(sal) from emp group by deptno;-- 但如果要查看工资最高的那个人,聚合函数无能为力,需要用到分析函数---- 1. 首先按组排序select e.*, row_number() over (partition by deptno order by sal desc) from emp e;---- 2. 过滤,只取排名第一的那个人,okselect * from (select e.*, row_number() over (partition by deptno order by sal desc) rn from emp e) where rn = 1;
阅读全文
1 0
- 常用oracle之常用函数
- Oracle常用函数
- Oracle 常用函数
- Oracle 常用函数
- oracle常用函数问答
- Oracle 常用SQL函数
- Oracle 常用SQL函数
- Oracle常用函数
- Oracle 常用SQL函数
- oracle常用函数
- (转载)Oracle 常用函数
- Oracle 常用SQL函数
- Oracle 常用函数
- Oracle 常用函数
- oracle常用函数
- 常用oracle函数
- Oracle常用函数
- oracle常用函数
- mycat join
- Effective 34 Note which algorithms expect sorted ranges
- HDU1027 Ignatius and the Princess II
- 欢迎来辩到底要不要用黄金分割?
- Java中ArrayList和LinkedList区别
- Oracle 常用函数
- HDU1031 Design T-Shirt
- Python 优雅的操作字典
- HDU1034 Candy Sharing Game
- 关于el表达式的整理
- 连接查询
- HDU1035 Robot Motion
- UVA10003[Cutting Sticks] 区间动态规划模型
- 《ImageNet Classification with Deep Convolutional Neural Networks》