Oracle 函数

全部函数及详细用法查看chm文档

SQL 函数

▪函数一般是在数据上执行的，它给数据的转换和处理提供了方便。只是将取出的数据进行处理，并会改变数据库中的值。

▪Sql函数可以分为组函数和单行函数。

组函数又称为聚合函数
-输入多个值，最终只会返回一个值
-组函数仅可用于选择列表或查询的having子句

单行函数

-输入一个值，输出一个值

单行函数: 字符函数：

--字符函数
--concat：表示字符串的连接  等同于||
select concat('my name is ', ename) from emp;
--将字符串的首字母大写
select initcap(ename) from emp;
--将字符串全部转换为大写
select upper(ename) from emp;
--将字符串全部转换为小写
select lower(ename) from emp;
--填充字符串 
lpad左边补全
rpad右边补全
当前字符串长度为10，用*补全
select lpad(ename,10,'*') from emp;
select rpad(ename,10,'*') from emp;
--去除空格
select trim(ename) from emp;
select ltrim(ename) from emp;
select rtrim(ename) from emp;
--查找指定字符串的位置
select instr('ABABCDEF','A') from emp;
--查看字符串的长度
select length(ename) from emp;
--截取字符串的操作
select substr(ename,0,2) from emp;
--替换操作
将原字符串中的ab替换成hehe
select replace('ababefg','ab','hehe') from emp;

单行函数: 数字函数：

--数值函数
--给小数进行四舍五入操作，可以指定小数部分的位数
select round(123.123,2) from dual;
select round(123.128,2) from dual;
select round(-123.128,2) from dual;

--截断数据,按照位数去进行截取，但是不会进行四舍五入的操作
select trunc(123.128,2) from dual;
--取模操作
select mod(10,4) from dual;
select mod(-10,4) from dual;
--向上取整
select ceil(12.12) from dual;
--向下取整
select floor(13.99) from dual;
--取绝对值
select abs(-100) from dual;
--获取正负值
select sign(-100) from dual;
--x的y次幂
select power(2,3) from dual;

日期函数

--返回系统当前日期
select sysdate from dual;

--返回日期时间戳
select current_timestamp from dual;

转换函数

-- 日期转字符串
select to_char(sysdate,'YYYY-MM-dd HH24:MI:SS') from dual;

-- 数字转字符串
select to_char(123.32141,'9999') from dual;
select to_char(123.3451,'0000.00') from dual;
select to_char(1322.231,'L0000.00') from dual;

--16进制转二进制
select HEXTORAW('A123')  from dual;

单行函数嵌套

练习：
显示没有上级管理的公司首脑

表：

分析：
找到MGR为空的条目 并将 MGR 显示为 no manager
select ename,nvl(to_char(mgr),‘no manager’) from emp where MGR is null;

NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。

练习2：
显示员工雇佣期满6个月后下一个星期五的日期
select hiredate,next_day(add_months(hiredate,6),‘星期五’) from emp;

其他函数

–条件函数
–decode,case when

–给不同部门的人员涨薪，10部门涨10%，20部门涨20%，30部门涨30%
select ename,sal,deptno,decode(deptno,10,sal1.1,20,sal1.2,30,sal*1.3) from emp;
select ename,
sal,
deptno,
case deptno
when 10 then
sal * 1.1
when 20 then
sal * 1.2
when 30 then
sal * 1.3
end
from emp;

练习：
表：

需求：
将表显示为
姓名 性别 年龄
张三 男 50

select min(decode(type, 1, value)) 姓名,
min(decode(type, 2, value)) 性别,
min(decode(type, 3, value)) 年龄
from test group by t_id;

min 用来过滤空值，因为null不能进行求最小值

练习

▪1、查询82年以后入职的员工
select * from emp where to_char(hiredate,'YYYYMMDD')>='19820101';

▪2、查询37年工龄的人员
select * from emp where '2019' - to_char(hiredate,'YYYY') = 37;

▪3、显示员工雇佣期 6 个月后下一个星期一的日期
select ename,next_day(add_months(hiredate,6),'星期一') from emp;

▪4、找没有上级的员工，把mgr的字段信息输出为 "boss"
select ename,nvl(to_char(mgr),'boss') from emp where MGR is null;

▪5、为所有人长工资，标准是：10部门长10%；20部门长15%；30部门长20%其他部门长18%
select ename,sal,deptno,decode(deptno,10,sal*1.1,20,sal*1.15,30,sal*1.2,sal*1.18) from emp;

组函数

组函数,一般情况下，组函数都要和groupby组合使用
组函数一般用于选择列表或者having条件判断

常用的组函数有5个
avg() 平均值,只用于数值类型的数据
min() 最小值，适用于任何类型
max() 最大值，适用于任何类型
count() 记录数,处理的时候会跳过空值而处理非空值
count一般用来获取表中的记录条数，获取条数的时候可以使用*或者某一个具体的列
甚至可以使用纯数字来代替，但是从运行效率的角度考虑，建议使用数字或者某一个具体的列
而不要使用*

sum() 求和，只适合数值类型的数据

–group by,按照某些相同的值去进行分组操作
–group进行分组操作的时候，可以指定一个列或者多个列，但是当使用了groupby 之后，
–选择列表中只能包含组函数的值或者group by 的普通字段

在分组函数中使用NVL函数：
▪组函数不能处理null
▪select avg(comm) from emp;
▪NVL函数迫使分组函数包括空值
▪select avg(nvl(comm,0)) from emp;

--求每个部门的平均薪水
select avg(sal) from emp group by deptno;

--求平均薪水大于2000的部门
select avg(sal) from emp group by deptno having avg(sal)>2000;

--求各部门下雇员工资大于2000的人数
select deptno,count(1) from emp where sal > 2000 group by deptno.

--部门薪水最高
select deptno,max(sal) from emp group by deptno;

--各部门里面 工龄最小和最大的人找出来,知道姓名
select deptno,min(hiredate),max(hiredate) from emp group by deptno;

----部门里面 工龄最小和最大的人找出来,知道姓名
select ename, deptno
  from emp
 where hiredate in (select min(hiredate) from emp group by deptno)
    or hiredate in (select max(hiredate) from emp group by deptno)