Oracle 单行函数

Oracle

1单行函数

函数日期

• sysdate/current_date 以date类型返回当前的日期
• add_months(d,x)返回加上X月后的最后一天
• LAST_DAY(d)返回的所在月份的最后一天
• months_between(date1,date2)返回date1和date2之间月的数目
• next_day(sysdate,星期一)下周星期一

当前时间

select current_date from dual where 1=1;
select sysdate from dual where 1=1;

修改日期（天数）

select sysdate+2 from dual;

修改月份

--当前5个月后的时间
select add_months(sysdate,5) from dual;
--日期 两个月的时间
select ename,hiredate ,add_months(hiredate,2) after from emp; 

月份之差

---雇佣日期 距离现在在的月分数
select ename , months_between(sysdate,hiredate) from emp;

最后一天

--返回雇佣日期 当月的最后一天的时间
select ename ,last_day(hiredate) d from emp;

下一个星期的时间

---下一个星期二
select next_day(sysdate,'星期二') from dual;

转换函数（重点）

to_date(c,m) - ->字符串以指定格式转换为日期

to_char(d,m) - ->日期以指定格式转换为字符串

select to_date('1981-3-21 18:12:12','yyyy-mm-
dd hh24:mi:ss') time from dual;
select to_char(sysdate, 'yyyy-mm-dd') from
dual;
select to_char(sysdate, 'yyyy/mm/dd') from
dual;
select to_char(sysdate, 'yyyy\mm\dd') from
dual;

--注意中文的问题
--select to_char(sysdate,'yyyy年mm月dd日')
from dual;
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"')
from dual;

查询８２年的员工信息

1、select * from emp where hiredate like'%82%';

2、select * from emp where to_char(hiredate,'yyyy')='1982';

3、select * from emp where hiredate between to_date('1981-02-01','yyyy-mm-dd') and to_date('1982-12-31','yyyy-mm-dd');

其他函数（保障类型兼容）

--1)、nvl nvl(string1,string2) 如果string1为
null,则结果为string2的值
select ename, nvl(null,0) from emp;
select ename, nvl(to_char(comm),'hello') from
emp;

--2)、decode
decode(condition,case1,express1,case2 ,
express2,….casen , expressn, expressionm)
select ename,decode(deptno, 10,'十',20,'二十')
from emp;

--3)、case when then else end
--栗子

--给所有的员工 涨薪,10-->10% 20-->8% 30 -->15% 其
他-->20% --decode
select ename,sal,deptno,decode(deptno, 10,sal * 1.1,20,sal * 1.08,30,sal * 1.15,sal * 1.2)raisesal from emp;

--case when then else end
select ename,sal,deptno,(case deptno when 10 then sal * 1.1when 20 then sal * 1.08 when 30 then sal * 1.15 else sal * 1.2 end) from emp;

分析函数（了解）

select ename, sum(1) over(order by sal desc)
aa from emp;

select deptno,ename,sal,
   sum(sal) over(order by ename) 连续求和,
   sum(sal) over() 总和, -- 此处sum(sal)
over () 等同于sum(sal)
   100 * round(sal / sum(sal) over(), 4)
"份额(%)"
 from emp;


select deptno,ename,sal,
   sum(sal) over(partition by deptno order
by ename) 部门连续求和, --各部门的薪水"连续"求和
   sum(sal) over(partition by deptno) 部门
总和, -- 部门统计的总和，同一部门总和不变
   100 * round(sal / sum(sal)
over(partition by deptno), 4) "部门份额(%)",
   sum(sal) over(order by deptno, ename)
连续求和, --所有部门的薪水"连续"求和 sum(sal)
over() 总和, -- 此处sum(sal) over () 等同于
sum(sal)，所有员工的薪水总和
   100 * round(sal / sum(sal) over(), 4)
"总份额(%)"
 from emp;

2多行|聚合|组函数

count: 求和

--1、count统计所有的员工数
--1)、*
--2)、主键
--3)、推荐 
select ename,1 from emp;
select count(1) from emp where 1=1;

--2、null不参与运算 存在佣金的员工数 不推荐/不需要
select count(comm) from emp where comm is not
null; 
--推荐
select count(comm) from emp;

--统计 部门编号30的员工数
select count(1) from emp where deptno=30;

--统计数量过程中 ，可能处理重复
--统计 存在员工的 部门数量
select count(distinct(deptno)) 有人的部门 from
emp;

--统计10和20部门一共有多少人
select distinct(count(1)) from emp where
deptno in(10,20);

max min:最大值 最小值

--查询所有员工的 最高薪水 ，最低薪水，员工总数 -->组信息
select max(sal) maxSal , min(sal) minSal ,
count(1) from emp;


--查询 最高薪水的员工名称 及薪水
--组信息 与单条记录不能同时查询
select max(sal), ename, sal from emp; 错误
select ename, sal from emp where sal=(select
max(sal) from emp );

avg :平均

-- 查询工资低于平均工资的员工编号，姓名及工资
select empno, ename,sal from emp where
sal<(select avg(sal) from emp);


--查看 高于本部门平均薪水员工姓名
select * from emp e1 where sal>(select
avg(sal) from emp e2 where e1.deptno=e2.deptno
);

分组

group by:将符合条件的记录 进一步的分组

select 出现分组函数，就不能使用 非分组信息，可以使用group by 字段

group by 字段 不可以出现在select中，反之select除组函数外的，其他字段必须出现在 group by 中

过滤组

having：过滤组信息，表达式同where一致

where: 过滤行记录，不能使用组函数

having：过滤组 可以使用组函数

重点：行转列

--找出课程名(表头)
select distinct course from tb_student;
--数据(行记录) 分组（学生+行转列 decode）
select * from tb_student;


--1、行转列 decode
select name,
   decode(course, '语文', score) 语文,
   decode(course, '数学', score) 数学,
   decode(course, '英语', score) 英语
 from tb_student;
--2、分组
select name,
   min(decode(course, '语文', score)) 语文,
   min(decode(course, '数学', score)) 数学,
   min(decode(course, '英语', score)) 英语
 from tb_student
group by name;

nvl()

• nvl(如果值不为null那么就原赋给原值、假如为null那么就在逗号后面赋值，赋值)

--55将计算机系学生的成绩加5分。
select sno from s_student where sdept='计算机系'
update s_sc set grade=grade-5 where sno in(select sno from s_student where sdept='计算机系')
update s_sc set grade=nvl(grade+5,5) where sno in(select sno from s_student where sdept='计算机系')--优化