SQL语句函数
6.4 Functions
6.4.1 Numeric
abs/mod 绝对值/求模sqrt/power/exp 求幂ceil/floor/round 取整trunc 截取⼩数点
sin/cos/ln/log 数学函数6.4.2 Charactor
chr/ascii ascii码
length/lengthb 字符/字节长度initcap/upper/lower 字母⼤⼩写
substr/translate/replace/instr ⼦串/替换ltrim/rtrim/lpad/rpad 修剪/填充6.4.3 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;
6.4.4 Nullable
nvl/nvl2/nullif6.4.5 Miscellaneousdecode/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]
6.4.6 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;
6.4.7 Analytic Function
聚合函数统计的是⼀个分组的信息，聚合之后每个分组只能得到⼀条信息，但并不能够获取分组内成员的具体信息。⽽分析函数可以详细显⽰分组内部的统计信息，它显⽰的信息更全⾯，语法也更复杂。⼤致来说，分析函数的使⽤语法是这样的:
分析函数() 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 by
select 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;
