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;--不触发

 

   

oracle中的函数

上一篇:QS Network(最小生成树)


下一篇:一分钟 Get 时序数据库 InfluxDB 的技能