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 by
select 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;