Oracle -- 常用函数

最新推荐文章于 2022-09-09 10:41:55 发布

lairikeqi

最新推荐文章于 2022-09-09 10:41:55 发布

阅读量1.5k

点赞数 1

分类专栏： Database 文章标签： Oracle函数

本文链接：https://blog.csdn.net/lairikeqi/article/details/81566962

版权

Database 专栏收录该内容

9 篇文章 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：

lairikeqi

关注

1
点赞
踩
13

收藏

觉得还不错? 一键收藏
2
评论
Oracle -- 常用函数

一、字符函数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...
复制链接

扫一扫