【Oracle】函数的基本使用

文章目录

• • 
    
• ​​1. 多行函数​​
• ​​2. 单行函数​​
• 
  
• ​​2.1 数值函数​​
• ​​2.2 字符串函数​​
• ​​2.3 日期函数​​
• ​​2.4 转换函数​​
• ​​2.5 通用函数​​
• ​​2.6 条件表达式​​
• ​​2.7 分组表达式​​
• 
  
  • 
    

1. 多行函数

• • 
    
• 函数可以没有参数，但必须要有返回值
• 多行函数: 对某一列的所有行进行处理
  ​​max() min() count() sum() avg()​​，直接忽略空值
• 统计员工工资总和
  • 
    

select sum(sal) from emp;

• 统计员工奖金总和

select sum(comm) from emp;

• 统计员工人数

select count(1) from emp;

• 统计员工的平均奖金 ， 错误 2200/14 = 157

select avg(comm) from emp;

select sum(comm)/count(1) from emp;

select ceil(sum(comm)/count(1)) from emp;

2. 单行函数

• 单行函数: 对某一行中的某个值进行处理
  数值函数
  字符函数
  日期函数
  转换函数
  通用函数

2.1 数值函数

• • 
    
• 包括：​​round,trunc,mod,ceil,floor​​
• 数值函数 ​​ceil​​，该函数返回的最小整数值，但不能小于X
  • 
    

select ceil(45.926) from dual;

• ​​floor​​，返回小于等于n的最大整数

select floor(45.926) from dual;

• ​​round​​，四舍五入的方法，即传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果

SELECT ROUND( number, [ decimal_places ] ) FROM DUAL

参数 ​​number​​ 是指需要处理的数值，是必须填写的值。

参数 ​​decimal_places​​ 是指在进行四舍五入运算时 , 小数的应取的位数，该参数可以不填，不填的时候，系统默认小数位数取0。

select round(45.926, 0) from dual;

select round(45.926, 1) from dual;

select round(45.926, 2) from dual;

select round(45.926, -1) from dual;

select round(45.926, -2) from dual;

select round(65.926, -2) from dual;

• 截断，​​trunc​​

select trunc(45.926, 0) from dual;

select trunc(45.926, 1) from dual;

select trunc(45.926, 2) from dual;

select trunc(45.926, -1) from dual;

select trunc(45.926, -2) from dual;

• 求余，​​mod​​

select mod(9, 3) from dual;

select mod(9, 4) from dual;

2.2 字符串函数

• • 
    
• 字符函数，​​substr(str1,起始索引,长度)​​
• 注意: 起始索引不管写 ​​0​​​ 还是 ​​1​​ 都是从第一个字符开始截取
  • 
    

select substr('abcdefg',0,3) from dual;
select substr('abcdefg',1,3) from dual;

select substr('abcdefg',2,3) from dual;

• 获取字符串长度，​​length​​

select length('abcdefg') from dual;

• 去除字符两边的空格，​​trim​​

select trim(' hello ') from dual;

• 替换字符串，​​replace​​

select replace('hello', 'h', 'w') from dual;

2.3 日期函数

• 日期函数，查询今天的日期

select sysdate from dual;

• 查询3个月后的今天的日期

select add_months(sysdate,3) from dual;

• 查询3天后的日期

select sysdate + 3 from dual;

• 查询员工入职的天数

select ceil(sysdate - hiredate) from emp;

• 查询员工入职的周数

select (sysdate - hiredate)/7 from emp;

• 查询员工入职的月数

select months_between(sysdate,hiredate) from emp;

• 查询员工入职的年份

select months_between(sysdate,hiredate)/12 from emp;

2.4 转换函数

• • 
    
• 转换函数：数值转字符，字符转数值， 日期转字符
  
• 字符转数值：​​to_number(str)​​，默认已经转换了
  
  • 
    

select 100+'10'from dual;

• 标准写法

select 100 + to_number('10') from dual;

• 数值转字符，​​to_char(p1,'格式化字符串')​​

select to_char(sal,'$9,999.99') from emp;

select to_char(sal,'L9,999.99') from emp;

• 日期转字符 ​​to_char()​​

select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;

• 24小时制显示，在 hh 后面加上 24

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;

• 只显示年份

select to_char(sysdate, 'yyyy') from dual;

• 只显示日，dd：代表一个月中的第几天

select to_char(sysdate, 'dd') from dual;

• d：代表一个星期的第几天，因为西方是从星期天开始算的，今天周四，所以是第五天

select to_char(sysdate, 'd') from dual;

• ddd：代表一年中的第几天

select to_char(sysdate, 'ddd') from dual;

• dy：日期的简称，就是星期几

select to_char(sysdate, 'dy') from dual;

select to_char(sysdate, 'day') from dual;

• 字符转日期，​​to_date(字符,'格式化字符串')​​

select to_date('2021-12-30', 'yyyy-mm-dd') from dual;

• 查询1981年 – 1985年入职的员工信息

select * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');

2.5 通用函数

• ​​nvl(参数1,参数2)​​ ，如果 参数1 = null 就返回参数2

select nvl(null,1) from dual;

• ​​nvl2(参数1,参数2,参数3)​​ ，如果参数1 = null ,就返回参数3, 否则返回参数2

select nvl2(1,2,3) from dual;

• ​​nullif(参数1,参数2)​​ ，如果 参数1 = 参数2， 那么就返回 null , 否则返回参数1

select nullif(4,5) from dual;

• ​​coalesce​​: 返回第一个不为null的值

select coalesce(null,null,7,8,9) from dual;

2.6 条件表达式

• 通用语法，mysql和Oracle都可以使用

case 字段:
  when 值1  then 值
  when 值2  then 值
  else
    默认值
  end

select
       case ename
         when 'SMITH' then 'zhangsan'
         when 'ALLEN' then 'lisi'
         else
           'wangwu'
         end "中文别名"
from emp;

• Oracle专用的方式: ​​decode(字段,'if1','then1','if2','then2','else')​​

select decode(ename,'SMITH','zhangsan','ALLEN','lisi','wangwu') from emp;

2.7 分组表达式

select 分组的条件,分组之后的操作from 表名 group by 分组的条件 having 条件过滤

• 分组统计所有部门的平均工资，找出平均工资大于2000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

• 如果avg(sal) 使用别名，报错了

select deptno,avg(sal) sal from emp group by deptno having sal >2000;

• • 
    
• 原因如下：
• SQL的编写顺序：
  • 
    

select ... from ... where ... group by ... having ... order by

• SQL的执行顺序：

from ... where ... group by ... having ... select ... order by ...

• where 和 having 区别：
  where 后面不能接聚合函数，可以接单行函数
  having 是在 group by 之后执行的，可以接聚合函数