【详细】Oracle单行函数和多行函数

加油，新时代打工人！

1. 【详细】 Oracle Database 19c 安装步骤

2. Oracle基础入门语句

没有环境的可以看以上文章。

一、单行函数

--scott用户 密码 tiger
--解锁 scott用户 
--scott 用户 给初学者 学习使用 里面有四张表 
alter user scott account unlock;
--解锁重置密码，此句可以用来重置密码
alter user scott identified by tiger;

--单行函数，作用与一行，返回一个值
---字符函数
select upper('yes') from dual;--YES --upper 大写字符
select lower('yes') from dual;--YES --lower 小写字符
---数值函数
select round(56.16,-2) from dual;--round 四舍五入 “，”后面参数是保留几位小数  -1，是小数点前四舍五入   
select trunc(56.16,-1) from dual; --trunc 直接截取，不再看后面的数字   
select mod(10,3) from dual;--mod 求余
---日期函数
---查询出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('2021-12-14 14:59:25','fm yyyy-mm--dd hh24:mi:ss') from dual;
---通用函数
---算出emp表中所有员工的年薪
---奖金里面有null值，如果null值和任意数字做算术运算，结果都是null
select e.sal*12+nvl(e.comm,0) from emp e; --nvl() 如果有null,是本身 如果不是null,加上奖金
--排序查询
select * from emp e order by e.sal desc;
--条件表达式 
/*语法
select 字段
  case 条件
     when 条件字段 then 别名
        when 条件字段 then 别名
          else
            end
from 表名*/
--给emp表中员工起中文名
select e.ename,
      case e.ename 
        when 'SMITH'  then '小明'
          when 'ALLEN' then '小华'
            when 'WARD' then '小亮'
           --   else '无名' 注释掉 为空的不查询，null值
                end
 
from emp e;
--给emp表中工资记录 大于3000高收入 大于5000中等收入 其他小收入
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' , '小华',
            'WARD',  '小亮',
             '无名') "中文名"
from emp e;

二、多行函数【聚合函数】

--多行函数【聚合函数】：作用于多行，返回一个值
select count(1)from emp;--总行数 count(*)底层也是count(1)1表示主键
select max(sal) from emp;--最高工资
select min(sal) from emp;--最低工资
select sum(sal) from emp;--工资总和
select avg(sal) from emp;--平均工资
--分组查询 
---查询每个部门的平均工资
---分组查询中，出现group by 后面的原始列，才能出现在select后面
---没有出现在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;
---所有条件都不能使用别名来判断，原因 条件大于select
---查询出每个部门工资大于1000的员工
select e.deptno,avg(e.sal)
from emp e
where e.sal>1000
group by e.deptno;
---where 是过滤分组前的数据，havaing 是过滤分组后的数据
---表现形式：where 必须在group by前，having 在group by后面
---查询出每个部门工资大于1000的员工
---然后在查询平均工资高于2000的工资
select e.deptno,avg(e.sal)
from emp e
where e.sal>1000
group by e.deptno
having avg(e.sal)>2000;

---多表查询 
---笛卡尔积 两个表总记录相乘, 假设集合A={a, b}，集合B={0, 1, 2}，
--则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
select *
from  emp e, dept d;
--等值连接
select * 
from emp e, dept d
where e.deptno=d.deptno;
--查询出所有部门的信息，以及部门下的员工信息【外连接】分为左外连接 右外连接
--语法
select 字段
from 表名A right join 表名B
on A.外键=B.主键;
--右外连接
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; 

--查询员工姓名 领导姓名
--自连接 自连接就是站在不同的角度把一张表看成多张表
select e1.ename ,e2.ename 
from emp e1,emp e2
where e1.mgr=e2.empno; 

--查询员工姓名 员工部门名称 领导姓名 领导部门名称
select e1.ename ,d1.dname,e2.ename,d2.dname
from emp e1,emp e2,dept d1,dept d2
where e1.mgr=e2.empno 
and e1.deptno=d1.deptno
and e2.deptno=d2.deptno;
--查询员工编号 员工姓名 员工的工资等级 部门名称 领导编号 领导姓名 领导的工资等级 
select e1.empno,e1.ename,
    decode(s1.grade，
    1,'一等级'，
    2,'二等级'，
    3,'三等级'，
    4,'四等级'，
    5,'五等级'
    )grade,
    d.dname,
    e2.empno,
    e2.ename,
     decode(s2.grade，
    1,'一等级'，
    2,'二等级'，
    3,'三等级'，
    4,'四等级'，
    5,'五等级'
    )grade
from emp e1,emp e2,dept d,salgrade s1,salgrade s2
where e1.mgr=e2.empno 
and e1.deptno=d.deptno
and e1.sal between s1.losal  and s1.hisal
and e2.sal between s2.losal  and s2.hisal;


---子查询
---子查询返回一个值
---查询出工资和MARTIN一样的员工信息 如果ename 是主键可以用 sal =
select * from emp where sal in (
select sal from emp where ename='MARTIN');
---子查询返回一个集合
---查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in(
select sal from emp where deptno = 10);
---子查询返回一张表
---查询出每个部门最低工资 和最低工资员工姓名 和该员工所在部门名称
---1.先查询出部门最低工资
select deptno, min(sal) msal
from  emp 
group by deptno; 

---2.三表联查
select e.ename,e.sal, d.dname
from (select deptno, min(sal) msal
from  emp 
group by deptno) t, emp e, dept d
where t.deptno=e.deptno
and t.msal=e.sal
and e.deptno=d.deptno;

---oracle分页
---rownum行号，当我们做select操作时候
--每查询出一行记录，就会在该行上加上一个行号
--行号从1开始，依次递增，不能跳着走

---排序操作会影响rownum的顺序
select rownum, e.* from emp e order by e.sal desc;
---如果涉及到排序，但是还有使用rownum的话，我们可以再次嵌套查询
select rownum, t.* 
from 
(select rownum, e.* from emp e order by e.sal desc) t;

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

三、分页查询运行结果