Oracle -- 常用函数

不念不忘少年蓝@ 2022-05-17 11:29 628阅读 0赞

#### 一、字符函数 ####

**1. lower(char)　upper(char)**

lower(char) --将字符串转化为小写的格式
    upper(char) --将字符串转化为大写的格式

**2. length(char)**

length(char) --返回字符串的长度
    例: select * from emp where length(ename) = 5;

**3. replace(char, search\_string, replace\_string)**

replace(char, search_string, replace_string)     --替换字符串 
      char -等待替换的字符串  search_string -搜索需要替换的字符串  replace_string -替换字符串
    
    select replace(ename,'.net','Java') from emp;  --显示所有员工的姓名，用"Java"替换所有".net"

**4. substr(char, m, n)**

substr(char, m, n)    substr(某个列名里的String,开始位置,截取长度)
    				m代表第几个位置(从1开始),n代表取n个字符的意思    m为负数,则表示从右边开始数第几个位置		
    示例：
    select substr('hello world', 2) FROM DUAL;          --结果：ello world
    select substr('hello world', -2) FROM DUAL;         --结果：ld
    select substr('This is a test', 6, 2)               --结果：is
    select substr('This is a test', -3, 3)              --结果：est

**5. instr( string1, string2, start\_position,nth\_appearance )**  
　　`string1`：源字符串，要在此字符串中查找；  
　　`string2`：要在string1中查找的字符串；  
　　`start_position`：代表从string1的哪个位置开始查找，此参数可选，如果省略则默认为1。字符串索引从1开始，如果此参数为正，从左到右开始检索；如果此参数为负，则从右到左检索；  
　　`nth_appearance`：代表要查找第几次出现的string2，此参数可选，如果省略默认为1，不能为负。

instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）
    
    select instr('syranmo','s') from dual;        -- 返回 1, 位置索引从1开始
    select instr('syranmo','ra') from dual;       -- 返回 3 
    select instr('syran mo','at',1,2) from dual;  -- 返回 0, 没有找到返回值是0
    
    --由于ra只出现2次,而start_position为3,即结果返回0
    select instr('oracle traning', 'ra', 1, 3) from dual;

**6. substr 和 instr**

substr和instr结合使用来实现截取字符串中 特定字符前后的字符串
    
    --截取"hello,world"字符串中','分隔符之前的字符串 结果：hello
    select substr('hello,world', 1, instr('hello,world', ',')-1) from dual;
    
    --截取"hello,world"字符串中','分隔符之后的字符串 结果：world 
    select substr('hello,world', instr('hello,world', ',')+1) from dual;
    
    -- 截取"hello,world,HH"字符串中第1次出现的','字符和第2次出现的','字符之间的字符串 结果：world
    select substr('hello,world,HH', instr('hello,world,HH', ',',1)+1, instr('hello,world,HH', ',', 2)-1) from dual;

**7. Concat 和 '||'**

--concat只能连接两个字符串，连接多个需要嵌套调用不方便
    SQL> select concat('aa','bb') from dual;
     
    --||直接连接多个字符串
    SQL> select 'aa'||'bb'||'cc' from dual;

#### 二、数学函数 ####

**1. round(n, m) 保留几位小数(四舍五入)**  
　　如果省掉m，则四舍五入到整数；如果m是正数，则四舍五入到小数点的m位后；如果m是负数，则四舍五入到小数点的m位前。

select round(23.75123) from dual;       --返回24 
    select round(23.75123, -1) from dual;   --返回20 
    select round(23.75123, 1) from dual;    --返回23.8 
    select round(23.75123, 2) from dual;    --返回23.75

**2. trunc(n, m) --对数字和日期进行操作**

`trunc(number):`

*  截取数字(几位小数),无需四舍五入；
 *  如果省掉m，就截去小数部分；
 *  如果m是正数就截取到小数点的m位后；
 *  如果m是负数，则截取到小数点的前m位。

select trunc(23.75123) from dual;     --返回23 
    select trunc(23.75123, -1) from dual; --返回20 
    select trunc(23.75123, 1) from dual;  --返回23.7 
    select trunc(23.75123, 2) from dual;  --返回23.75

`trunc(Date):`

*  `trunc(sysdate，'yyyy'); --返回当前年的第一天`；
 *  `trunc(sysdate, 'mm');//返回当前月的第一天`；
 *  `trunc(sysdate, 'dd');//返回当前时间的年月日`；
 *  `trunc(sysdate, 'd');//返回当前星期的第一天`；
 *  `trunc(sysdate, 'hh');//返回当前小时`。

select trunc(sysdate) from dual --2018-12-22 今天的日期为2018-12-22
    select trunc(sysdate,'mm') from dual --2018-12-01 返回当月第一天.
    select trunc(sysdate,'d') from dual --2018-12-16 (星期天)返回当前星期的第一天

**3. 取余、取商、取整、绝对值、与0判断函数**

取余:
    	select mod(10,2) from dual;    –0 
      	select mod(10,3) from dual;    –1    
    
    取商:
    	select trunc(20/3) from dual;   -6
    
    取整:
    	floor(n) / trunc(n)    无需四舍五入有	
    	ceil(n) 四舍五入
    
    绝对值:
    	abs(n) 返回数字n的绝对值
    	
    sign
        语法: sign(表达式);
        若表达式>0则返回1,若表达式<0则返回-1,=0返回0

#### 三、转换函数 ####

转换函数用于将数据类型从一种转为另外一种

**1. to\_char( )函数**

用作日期转换: select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
    
        yyyy：四位数字的年份 2004年 
        mm：两位数字的月份 8 月–>08 
        dd：两位数字的天 30 号–>30 
        hh24： 8点–>20 
        hh12：8点–>08 
        mi、ss–>显示分钟\秒 
    
    处理数字(常见)
      to_char(number,'格式');
      select to_char(888) from dual;   --'888'
      select to_char(123,'09999')  from dual;   --'00123'
      select to_char(1314.73, '9999.9') from dual;	 --'1314.7'
      select to_char(1314.73, '9,999.99') from dual;   --'1,314.73'
      select to_char(1314.73, '$9,999.00') from dual;  --'$1,314.73'

**2. to\_date( )函数**

--将字符串转换成date类型的数据
    select to_date('2018-08-13 09:25:30','yyyy-mm-dd hh24:mi:ss') from dual;

**3. to\_number( )**

将char或varchar2类型的string转换为一个number类型的数值，
    
    	select to_number('123') from dual;              --结果:123
    	select to_number('00123') from dual;           --结果:123
    	to_number和to_char恰好是两个相反的函数:
    	select to_char(123) from dual;     --结果:'123'
    
    可以用来实现进制转换；16进制转换为10进制：
    	select to_number('19f','xxx') from dual;                       --415
    	select to_number('f','xx') from dual;                            --15

#### 四、日期和时间函数 ####

在Oracle中，系统提供了许多用于处理日期和时间的函数，通过这些函数可以实现计算需要的特定日期和时间，常用的日期和时间函数如下：

**1. sysdate --返回系统当前日期**

select sysdate as nowtime from dual;

**2. next\_day()**

--n为1到7的数字，分別对应周日到周六 查找下一个星期几对应的日期
    select next_day(sysdate,n) from dual;

**3. months\_between(d1, d2)**  
　　返回d1和d2之间的数目，若d1和d2的日期都相同，或者都是该月的最后一天，则返回一个整数，否则返回的结果将包含一个小数

#### 五、聚合函数 ####

**1. avg() - 平均值运算**

--查询平均成绩大于80的学生姓名和平均成绩 
    select A.sname,avg(B.num) from student A,score B,course C
      where 1=1
    	and A.sid = B.student_id
    	and B.course_id =C.cid
    	and C.cname in('生物','物理','体育','美术')
      group by A.sname
      having avg(B.num) > 80
    
    用avg函数进行平均运算时会忽略空值(即原始数据中如果存在空值,最终计算的平均值不对)
    avg(nvl(comm,0))或者sum(comm)/count(*)

**2. sum() - 某列求和**

--求每日的销量总和和每日的销量均值
    select to_char(sysdate,'yyyy/mm/dd'),sum(销量),avg(销量) from table group by 日期;

**3. count() - 统计结果集的行数**

--count(*),count(1),count(列名)效率其实没多大区别，区别在于统计null 
    count(*)/count(1)将返回表格中所有存在的行的总数包括值为null的行
    count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）

**4. max() / min()**

-- 查询当天最大的价格
    select nowday,max(price) from goods group by nowday;
    
    -- 查询每个人员最小离开时间和最大离开时间
    select personid,max(leavetime),min(leavetime) from person_leave group by personid;

#### 六、其他函数 ####

**1. decode函数**

使用decode判断字符串是否一样，`decide()`函数，它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
    decode (expression, search_1, result_1, default)
    decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)
    比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；default可以不定义，则返回空值。
    
    select id,name,decode(lever,1,'教授',2,'副教授',3,'导师','讲师') position from some_tables where t.lever in (1,2,3);
    
    逻辑如下:
        if(lever==1) then return '教授'(翻译值1)
        else if(lever==2) then return '副教授'(翻译值2)
        else if(lever==3) then return '导师'
        else return '讲师'(默认值)
    
    使用decode实现表或试图的行转列:
    select name,
    	sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese",
    	sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics",
    	sum(decode(subject, 'English', nvl(score, 0), 0)) "English"
    from student_score
    group by name;

**2. case when then else end**

case具有两种格式，简单case函数和case搜索函数。
    
    --简单case函数,使用表达式确定返回值.
    　　case search_expression                       --case sex
    　　when expression1 then result1                --when '1' then '男'
    　　when expression2 then result2                --when '2' then '女' 
    　　...
    　　else default_result                          --else '其他' end
    　　end
    
     --case搜索函数,使用条件确定返回值.
    　　case
    　　when condition1 then result1                --case when sex = '1' then '男'
    　　when condistion2 then result2               --when sex = '2' then '女'
    　　...
    　　else default_result                         --else '其他' end
    　　end
                
    这两种方式都能实现相同的功能,简单case函数的写法相对比较简洁,但是和case索函数相比功能方面会有些限制

**3. nvl() 和 nvl2()**

*  `nvl(value1,value2)`：如果value1的值为null，那么函数返回value2，如果value1不为null，函数返回value1的值；
 *  `nvl2(value1,value2,value3)`：如果value1的值为null，函数返回value3，否则函数返回value2。

**4. union / union all**

union/union all 都要求两个sql查询列要相同
    
    union: 对两个结果集进行并集操作(不包括重复行),同时进行默认规则的排序；
    union All: 对两个结果集进行并集操作(包括重复行),不进行排序
    	
    从效率上说,union all要比union快很多,如果可以确认合并的两个结果集中不包含重复的数据,尽量使用union all,因为union需要进行排序去除重复记录,效率低

**5. + 号 / left join… on…**

+ 左右外连接,哪边有 + 号哪边就可以为空
    select * from a,b where a.id=b.id(+);	--(+)写在where后面,不能与or/in连用,b表是附属表
    
    left join on:
    select * from a left join b on a.id=b.id;	--左连接,写在 from 与where之间
    
    效率上没区别,不过left join可读性高、功能更全面、通用性强、而且是新标准,建议使用left join

**6. group by / order by**

group by 分组,用来统计某些字段的信息
    order by 排序,根据升降序显示结果集 asc升序   desc降序 
    
    当group by和order by 一起使用时,先执行group by 在执行 order by
    
    group by 是分组,首先用group by的前提是你的select里边用了聚合函数如sum(),avg(),min(),max(),没有聚合函数用不了group by不然会报错;
    其次出现在select里边的除聚合函数以外的所有字段,都必须作为group by的条件,也就是说select中的字段必须出现在group by中,group by中的分组条件不一定出现在select中

#### Remark： ####

*  [参考case when then 的两种写法][case when then]
 *  [Oracle中保留两位小数][Oracle]

[case when then]: https://blog.csdn.net/alias_fa/article/details/71158851
[Oracle]: https://blog.csdn.net/u012934325/article/details/72840786