oracle 函数

一，数值型函数

--round (x,y)将x保留y位小数（四舍五入）
select round(2.2222,3) from dual;
-- 返回x按精度y截取后的值
select trunc (2222.555,2) from dual;
-- mod（x，y）求余数
select mod(7,3) from dual;
-- ceil 向上取整/ floor向下取整
select ceil(1.9) from dual;

二，日期型函数

-- 返回系统当前时间
select sysdate from dual;
-- 添加月数
select add_months(sysdate,2)from dual; 
-- 返回两个时间相差的月数
select months_between(sysdate,add_months(sysdate,2)) from dual;
-- 需求:查询工作年限在30年以上
select e.*
from emp e
where months_between(sysdate,e.hiredate)/12>30
-- 返回date所在月份最后的一天
select last_day(add_months(sysdate,1)) from dual;
-- next_day(date1，week) 返回date1下周星期几的日期
select sysdate "当时日期",next_day(sysdate,'Monday') "下周星期一" from dual;
**两个日期之间的四则运算单位是“天”

三，函数转换

 转换函数就是把字符、日期、数值型数据进行相互转换。类型转换分两种：隐式类型转换和显式类型转换

（1）隐式转换：

 字符和数字/日期之间的隐式转换

-- 字符隐式转换成数值
select '100'-10 from dual;
-- 字符隐式转化为日期
-- DD-MON-RR 默认的日期格式
select * 
from dual
where sysdate>'12-May-17';

（2）显式转换：

to_char 之把数值转换成字符

--把数值格式化成字符串
select to_char(12345,'99999.99') from dual;
-- 不够位置用0
select to_char(12345,'00,000.000') from dual;
--把18612341234格式化成186-1234-1234
select replace(to_char(18612341234,'000,0000,0000'),',','-') from dual;

to_char之把日期转换成字符串

-- 把日期转化成字符
-- 按照默认格式DD-MON-RR
select to_char(sysdate) from dual;
-- 按指定格式
select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') from dual;

to_number/to_date

-- to_number
select to_number('$12,345','$99,999') from dual;
select to_number('$12,345.12','$99,999.99') from dual;

-- to_date
select to_date('14-May-19','DD-MON-RR') from dual;
select to_date('2004-09-19','YYYY-MM-DD') from dual;

综合案例：

-- 查询雇用期满6个月的下一个周一的日期
select e.ename,e.hiredate, next_day(add_months(e.hiredate,6),'Monday')
from emp e
where  months_between(sysdate,e.hiredate)>6;
-- 查询公司boss
select e.ename|| nvl(to_char(e.mgr),'is boss')
from emp e
where e.mgr is null;

四，decode/case when

decode(条件,值1,“返回值1”, 值2,“返回值2”,,,“默认值”)

 

-- 需求:查询员工所在的部门名称
select e.ename,e.deptno,
decode(e.deptno,10,'bumen1',20,'bumen2',30,'bumen3','weizhi')
from emp e;
-- case when
select e.ename,e.deptno,
case e.deptno
  when 10 then 'bumen1'
    when 20 then 'bumen2'
      when 30 then 'bumen3'
        else 'weizhi'
        end
  from emp e

 

需求：

-- 需求:对各个部门进行涨薪，10->1.1 20->1.2 30->1.3 其他->1.0
select e.ename,e.deptno,e.sal,
decode (e.deptno,10,e.sal*1.1,20,e.sal*1.2,30,e.sal*1.3,e.sal)
from emp e;

-- 需求:根据工资分布输出以下信息
/*
 <1000 真屌丝
 (1001,2000] 屌丝
 (2001,3000] 白领 
 (3001,5000] 高富帅
 (5001,10000] 土豪
*/
select e.ename,e.deptno,e.sal,
case
  when e.sal <1000 then '真屌丝'
    when e.sal between 1001 and 2000 then '屌丝'
      when e.sal between 2001 and 3000 then '白领'
        when e.sal between 3001 and 5000 then '高富帅'
          when e.sal between 5001 and 10000 then '土豪'
            else 'weizhi'
 end             
from emp e

总结：

decode 多用于等值匹配；case when可以用于等值，多用于条件分支

五，组函数

 组函数把多行数据经过运算后返回单个值。也称聚合函数

-- 求公司雇员的数量
select count(1)
from emp e;
-- avg:对多个记录的某个字段求平均值
-- 需求:求底薪的平均值
select avg(e.sal)
from emp e;
-- 需求:求雇员的最高薪资/最低薪资
select max(e.sal),min(e.sal),avg(e.sal)
from emp e;

 注意:

[1] 组函数或聚合函数是对一个数据集(表数据、查询出来的表、分组的表)进行聚合。

[2] 聚合函数对字段是null的值进行忽略。可用count(*)和count(e.coom)来对比测试

 聚合函数的结果可以作为其他查询条件

-- 最早入职的员工
select e.ename
from emp e
where e.hiredate=(select min(e.hiredate) from emp e);

六，分组（group by）

 在处理统计或聚合数据时，很多时候需要对数据进行分组 语法：

select field1,。。。
from tableName
group by field1[,field2,…]

 对数据进行分组后，select语句的字段值只能是分组字段或者聚合函数

-- 需求:求各个部门的人数
select count(e.deptno)
from emp e
group by e.deptno;
-- 需求:求各个部门的平均薪资
select e.deptno,avg(e.sal)
from emp e
group by e.deptno;
-- 需求:求各个部门的月收入平均值
select e.deptno,avg(e.sal+nvl(e.comm,0))
from emp e
group by e.deptno;

having

 如果需要对分组的数据进行条件过滤，必须使用having

-- group by having
-- 查询部门平均薪资大于2000的部门
select e.deptno
from emp e
group by e.deptno
having avg(e.sal)>2000;
-- 查询部门薪资大于2000的雇员按部门分组的平均薪资
select e.deptno,avg(e.sal)
from emp e
where e.sal>2000
group by e.deptno;

 注意:

[1] Where过滤行，having过滤分组。

[2] Having支持所有where操作符

六，排序（order by）

 当需要对数据集进行排序操作时，语法：

select field1, field2,。。。
from tablename
order by field1,field2

 对数据集进行排序，先按field1排序，如果field1排序相同，按照field2排序，依次类推。

-asc 升序,默认

-desc 降序

-- 需求：按雇员薪资排序
select e.ename,e.sal
from emp e
order by e.sal;
--薪资大于1200的雇员所在部门的平均薪资大于1500的部门，按照平均薪资升序排序 
select e.deptno,avg(e.sal)
from emp e
where e.sal>1200
group by e.deptno
having avg(e.sal)>1500
order by avg(e.sal);

select语言的执行顺序：

1，读取from子句中的基本表、视图的数据，[执行笛卡尔积操作]。

2，选取满足where子句中给出的条件表达式的元组

3，按group子句中指定列的值分组，同时提取满足Having子句中组条件表达式的那些组

4，按select子句中给出的列名或列表达式求值输出

5，Order by子句对输出的目标表进行排序

from -> where -> group by -> having -> select -> order by

七，多表关联

（1）卡迪尔积

-- 笛卡尔积
select *
from emp e,dept d;

（2）等值连接

-- 需求:查询雇员的部门名称
select *
from emp e,dept d
where e.deptno=d.deptno;

（3）不等值连接

-- 查询每个雇员的薪资等级
select e.ename,e.sal,sg.grade
from emp e,salgrade sg
where e.sal between sg.losal and sg.hisal;

（4）外连接

 左外连接:左边的表作为主表，右边表作为从表，主表数据都显示，从表数据没有，用null填充,用+号表示

 右外连接: 右边的表作为主表，左边表作为从表，主表数据都显示，从表数据没有，用null填充,用+号表示

-- 需求:查询所有部门的雇员
select *
from emp e,dept d
where e.deptno(+)=d.deptno;
select *
from dept d,emp e
where d.deptno=e.deptno(+);

（5）自连接

-- 查询每个雇员的上级领导
select e.ename,nvl(d.ename,'boss')
from emp e,emp d
where e.mgr=d.empno(+);

同理：多于两张表的查询

 先把t1xt2笛卡尔积得到一个大表T1，再把T1xt3笛卡尔积得到一个另外的大表T2，依次类推。

所有的多表查询最终都是两种表的查询