oracle中的函数

一、单行函数

     1.定义：作用于一行，返回值为一个

         注：以下所有的操作的表是Oracle数据库中自带的用户scott下的表

     2.字符函数

--小写变大写
select upper('yes') from dual;--YES
select lower('YES') from dual;--yes

      3.数值函数

select round(26.18,1) from dual;--四舍五入,后面的参数为保留几位小数，26.2
select trunc(26.18,1) from dual;--直接截取，不在看后面面的位数，26.1
select mod(10,3) from dual;--求余，1

      4.日期函数

         系统时间：sysdate

--查询emp表中所有员工入职距离现在几天
select sysdate-e.hiredate from emp e; 
--算出明天此刻
select sysdate+1 from dual;
--查询emp表中所有员工入职距离现在几个月
select months_between(sysdate,e.hiredate) from emp e;
--查询emp表中所有员工入职距离现在几个年
select months_between(sysdate,e.hiredate)/12 from emp e;

--查询emp表中所有员工入职距离现在几周
select round((sysdate-e.hiredate)/7) from emp e;

--转换函数
--日期转字符串
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;
--字符串传日期
select to_date('2020-4-3 14:9:37','fm yyyy-mm-dd hh24:mi:ss') from dual;

        5.通用函数 

--计算员工没年的工资：基础工资（sal）+ 奖金（comm），奖金中存在null值
--如果null值和任意数字做算数运算，结果都是null;
--nvl(e.comm,0)此函数表示e.comm不为空就用本身值,如果为空就用零
 select e.sal*12+nvl(e.comm,0) from emp e;

       6.条件表达式

--条件表达式
--给emp表中起一个中文名称
select e.ename,
       case e.ename
         when 'SMITH' then '老杜'
           when 'ALLEN' then '老李'
             when 'JONES' then '老张'
               else '无名'
                 end

from emp e;
--判断emp表中员工工资，如果>3000显示高收入，>3000 and <1500显示中等，其余低收入
 select e.sal,
       case 
         when e.sal>3000 then '高收入'
           when e.sal>1500 then '中等'
               else '低收入'
                 end

from emp e;
--oracle专用条件表达式
--Oracle中除了起别名用双引号，其他都用单引号
select e.ename,
       decode( e.ename,
          'SMITH' , '老杜',
            'ALLEN',  '老李',
              'JONES',  '老张',
                '无名') "中文名"
                 
from emp e;

二、多行函数[集合函数]

          定义：作用域多行，返回一个值。         

select count(1) from emp;  --查询总数量，count(1)和count(*)一样
select sum(sal) from emp; --工资总和
select max(sal) from emp;  --最大工资
select min(sal) from emp;   --最小工资
select avg(sal) from emp;   --工资平均

三、分组查询

--查询每个部门的平均工资
--分组查询中，出现在group by后面的原始列，才能出现在select后，其他的要加上聚合函数
select e.deptno,avg(e.sal)
from emp e
group by e.deptno;
--查询出平均工资高于2000部门信息
--所有条件不能使用别名
select e.deptno,avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal)>2000;
--查询每个部门工资高于800的员工的平均工资
--where 是过滤分组之前的数据，having是过滤分组之后数据进行判断
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno;
--查询每个部门工资高于800的员工的平均工资，再查询出平均工资高于2000的部门
select e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;

四、连接

       1.等值连接           

select *
from emp e,dept d
where e.deptno=d.deptno;

        2.外连接     

--查询出所有部门，以及部门下的员工的信息【右外连接】
select *
from emp e right join dept d
on e.deptno=d.deptno;
--查询出所有员工的信息，以及员工所属的部门【左外连接】
select *
from emp e left join dept d
on e.deptno=d.deptno;
--oracle中的专业的外连接
select * 
from emp e,dept d
where e.deptno(+)=d.deptno;

        3.自连接

--查询员工姓名和员工领导姓名
select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno;
--查询员工姓名，员工部门名称，和员工领导姓名，员工领导部门名称
select e1.ename,d1.dname,d2.dname,e2.ename
from emp e1,emp e2,dept d1,dept d2
where e1.mgr=e2.empno and e1.deptno=d1.deptno and e2.deptno=d2.deptno;

五、子查询 

--子查询
--查询出工资和SCOTT一样的员工信息
select * from emp where sal in
(select sal from emp where ename='SCOTT');
--查询出工资和10号部门一样的员工信息
select * from emp where sal in(
select sal from emp where deptno=10);
--查询每个部门最低工资，和最低工资的员工姓名，和该员工所在部门名称
--先查询每个部门最低工资
select e.deptno,min(sal) msal
from emp e
group by e.deptno 


--最终结果
select t.deptno,t.msal,e.ename,d.dname
from (select e.deptno,min(sal) msal
from emp e
group by e.deptno) t,emp e,dept d
where t.deptno=e.deptno and t.msal=e.sal
and e.deptno=d.deptno;

六、分页查询

       1.oracle中的分页

          - - rownum行号：当我们做select操作时，没查询处一行记录就会在该行加上一个行号。

          - - 行号从1开始，依次递增，不能跳着走。

          - - 排序操作会影响rownum的顺序，如果要排序，还要使用rownum的话，我们可以使用嵌套查询。

--根据rownum排序
select rownum, t.* from(
select * from emp e order by e.sal desc
) t
--emp表工资倒叙排序后，每页五条记录，查询第二页
select * from 
(select rownum rn,t.* from(
select * from emp e order by e.sal desc
) t where rownum<11
) where rn>5 

 七、视图

        1.将其他用户的表拉到当前用户下        

create table emp as select * from scott.emp;

         2.视图作用

            1）可以屏蔽掉敏感字段

            2）保证总部和分部数据的统一    

--创建视图（必须有dba权限）
create view v_emp as
select ename ,job from emp;

--修改视图
update v_emp set job='CLERK' where ename='ALLEN';
commit;
--创建只读视图
create view v_emp as select ename ,job from emp  with read only;

八、索引

       1.概念：索引就是在表上构建一个二叉树，达到大幅度提高查询效率的目的，但是索引会影响增删改的效率

       2.单例索引       

--创建单列索引
create index idx_ename on emp(ename);
--单列索引触发规则，必须是索引列的原始值
--单行函数，模糊查询都会影响索引的触发
select * from emp where ename='SCOTT';

        3.复合索引

--复合索引触发规则,复合索引第一列位有限检索列
--必须包含优先检索列中的值，才会触发
select *from emp where ename='SCOTT' and job='xx';--触发复合索引
select *from emp where ename='SCOTT' or job='xx';--不触发