`
zhuqinglin
  • 浏览: 28315 次
  • 性别: Icon_minigender_1
  • 来自: 郑州
社区版块
存档分类
最新评论

oracle常用函数

 
阅读更多

 

一、集合运算符

例:集合运算符的使用:

intersect ,union, union all, minus

--表A与B的交集

select * from ucr_param.td_A_score intersect select * from ucr_param.td_B_score;

--在表A中且不在表B

select * from ucr_param.td_A_score minus select * from ucr_param.td_B_score;

--不包括重复的行

select * from ucr_param.td_A_score union  select * from ucr_param.td_B_score;

--包括重复的行

select * from ucr_param.td_A_score union all select * from ucr_param.td_B_score;

 

Union,对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;

Union All,对两个结果集进行并集操作,包括重复行不进行排序

Intersect,对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序;

Minus,对两个结果集进行差操作,不包括重复行,同时进行默认规则的排序。

 

可以在最后一个结果集中指定Order by子句改变排序方式。

二、ORACLE日期时间函数
1.日期和字符转换函数用法(to_date,to_char)        
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'as nowTime from dual;   

select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss'from dual;
2.某天是星期几     
 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day'from dual;     
     
3.两个日期间的天数     
 select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;

4.获取两个日期之间的具体时间间隔,extract函数是最好的选择

SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 2:38:40'from dual;

SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 2:38:40'from dual;

SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 2:38:40'from dual;

SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40'from dual;

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 2:38:40'from dual;

--获取两个日期之间的具体时间间隔,extract函数是最好的选择

select extract(day from dt2-dt1) day   

      ,extract(hour from dt2-dt1) hour   

      ,extract(minute from dt2-dt1) minute   

      ,extract(second from dt2-dt1) second   

from (    

     select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1    

           ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2    

     from dual)    
5.查找2002-02-28至2002-02-01间除星期一和七的天数    

select count(*)

  from (select rownum - 1 rnum

          from all_objects

         where rownum <= to_date('2014-03-15''yyyy-mm-dd') -

               to_date('2014-03-02''yyyy-mm-dd') + 1

)

  where to_char(to_date('2002-02-01''yyyy-mm-dd') + rnum - 1'D'    not i('1''7');
        

6.查找月份

select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) month_num FROM DUAL;     

select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) month_num FROM DUAL;  
   
7.Next_day的用法     

SELECT next_day(sysdate,'星期一'from dual;--或者

SELECT next_day(sysdate,2from dual;

三、字符函数(可用于字面字符或数据库列)

1.字符串截取        
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'as nowTime from dual;   

select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss'from dual;
2.查找子串位置     
 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day'from dual;     
     
3.字符串连接     
 select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;

4.去掉字符串中的空格

select ltrim(' abc') s1,rtrim('zhang ') s2,trim(' zhang ') s3 from dual;

5.去掉前导和后缀 

select trim(leading 9 from 9998767999) s1,

    trim(trailing 9 from 9998767999) s2,

trim(9 from 9998767999) s3 from dual;

876799999987678767

6.计算字符串长度

select length('abcdef'from dual;

7.nitcap(首字母变大写) ,lower(变小写),upper(变大写) 

select lower('ABC') s1, upper('def') s2,initcap('efg') s3 from dual;

8.Replace

SELECT REPLACE('AAAAbbbbbbbbb','A','a'from dual;

9.decode[实现if ..then 逻辑]  

注:第一个是表达式,最后一个是不满足任何一个条件的值
select deptno,decode(deptno,10,'1',20,'2',30,'3','其他'from dept;

例:
select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo//如果seed为111,则取1000;为200,取2000;其它取0
selectseed,account_name,decode(sign(seed-111),1,'bigseed',-1,'little seed','equal seed') from t_userInfo//如果seed>111,则显示大;为200,则显示小;其它则显示相等

 

10.case[实现switch ..case 逻辑]
SELECT CASE X-FIELD

         WHEN X-FIELD < 40 THEN 'X-FIELD 小于 40'

         WHEN X-FIELD < 50 THEN 'X-FIELD 小于 50'

         WHEN X-FIELD < 60 THEN 'X-FIELD 小于 60'

         ELSE 'UNBEKNOWN'

        END

 FROM DUAL;

四、数字函数

1.取整函数(ceil 向上取整,floor 向下取整)

 select ceil(66.6) N1,floor(66.6) N2 from dual;

2.取幂(power) 和 求平方根(sqrt)

select power(3,2) N1,sqrt(9) N2 from dual;

3.求余

select mod(9,5from dual;

4.返回固定小数位数 (round:四舍五入,trunc:直接截断)

select round(66.667,2) N1,trunc(66.667,2) N2 from dual;

5.返回值的符号(正数返回为1,负数为-1)

select sign(-32),sign(293from dual;

五、分组函数max min avg count sum


1.整个结果集是一个组
   1) 求部门30 的最高工资,最低工资,平均工资,总人数,有工作的人数,工种数量及工资总和
     select max(ename),max(sal),
     min(ename),min(sal),
     avg(sal),
     count(*) ,count(job),count(distinct(job)) ,
     sum(sal) from emp where deptno=30;


2带group by 和 having 的分组
   1)按部门分组求最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和


    select deptno, max(ename),max(sal),
    min(ename),min(sal),
    avg(sal),
    count(*) ,count(job),count(distinct(job)) ,
    sum(sal) from emp group by deptno;

  
   2)部门30的最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和

   
    select deptno, max(ename),max(sal),
    min(ename),min(sal),
    avg(sal),
    count(*) ,count(job),count(distinct(job)) ,
    sum(sal) from emp group by deptno having deptno=30;

六、Oracle trunc()函数的用法

/**************日期********************/

select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06

select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.

select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天

select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日

select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天

select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天

select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35

select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确

/***************数字********************/

/*

TRUNCnumber,num_digits

Number 需要截尾取整的数字。

Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0

TRUNC()函数截取时不进行四舍五入

*/

select trunc(123.458) from dual --123

select trunc(123.458,0) from dual --123

select trunc(123.458,1) from dual --123.4

select trunc(123.458,-1) from dual --120

select trunc(123.458,-4) from dual --0

select trunc(123.458,4) from dual --123.458

select trunc(123) from dual --123

select trunc(123,1) from dual --123

select trunc(123,-1) from dual --120

分享到:
评论

相关推荐

    110个oracle常用函数.pdf

    Oracle创建函数是通过PL/SQL自定义编写的,通过关键字function按照自己的需求把复杂的业务逻辑封装进PL/SQL函数中,函数提供一个返回值,返回给使用者。这样使用者就不需要去理解业务逻辑,把PL/SQL函数中的业务逻辑...

    Oracle常用函数大全

    Oracle常用函数大全 Oracle常用函数大全是一个非常重要的知识点,涵盖了许多实用的函数,例如日期函数、字符串函数、聚合函数等。这些函数可以帮助开发者更方便地进行数据库操作和数据分析。本文将详细介绍Oracle...

    oracle常用函数汇总中文最新版本

    不过,根据标题和描述,我们可以确定本文档是关于Oracle常用函数的汇总介绍,涵盖了字符串处理、数学计算、日期操作、数据转换、聚合统计、条件判断、通用工具以及特定数据类型处理等多个方面。对于Oracle数据库用户...

    Oracle常用函数

    在初学者的学习过程中,掌握Oracle常用函数是至关重要的,这将有助于提升数据库管理、查询和分析的效率。以下是对"Oracle常用函数"这一主题的详细阐述。 1. **字符串处理函数**: - `CONCAT()`:连接两个或更多...

    Oracle常用函数实战总结.xlsx、Oracle 11g数据库管理员指南.pdf、Oracle开发实战经典.pdf

    Oracle常用函数实战总结.xlsx: 1.内容:总结了Oracle数据库中常用的函数及其实战应用。这些函数可能包括字符串处理函数、数值计算函数、日期和时间函数、转换函数等。 2.用途:对于Oracle数据库的使用者来说,这...

    Oracle 常用函数110种详解

    ### Oracle 常用函数110种详解 在Oracle数据库中,掌握各种函数的使用方法对于进行数据处理和分析至关重要。以下是从给定的部分内容中提取并详细解释的几个重要函数。 #### 1. ASCII 该函数用于返回与指定字符相...

    oracle常用函数及语法

    多年积累Oracle常见的优化措施及自定义函数库

    ORACLE常用函数

    ### Oracle常用函数详解 #### 一、概述 Oracle数据库提供了丰富的内置函数,这些函数能够帮助开发者和数据库管理员高效地处理各种类型的数据。根据数据类型的不同,Oracle函数大致可以分为单行函数和多行(聚合)...

    hive和oracle常用函数对照表.xlsx

    hive和oracle常用函数对照,包含常用的函数分类 字符函数 数值函数 日期函数 聚合函数 转换函数 其他 增加的hive函数对比,只需要2个积分喔

    Oracle常用函数使用方法及常用查询

    ### Oracle常用函数使用方法及常用查询 在Oracle数据库中,SQL函数被广泛应用于各种数据处理需求,例如数据筛选、计算汇总、格式化等。本文将详细介绍Oracle中的常见函数及其应用场景,帮助用户更好地理解和掌握...

    Oracle常用函数Trunc及Trunc函数用法讲解

    Oracle数据库中的`TRUNC`函数是一个非常实用的函数,它在处理日期和数值时具有不同的功能。本篇文章将深入解析`TRUNC`函数在日期和数值处理中的具体用法。 ### 日期处理 在日期处理方面,`TRUNC`函数主要用于截取...

    Oracle常用函数集

    ### Oracle常用函数集详解 #### 一、ASCII函数 **功能描述:** ASCII函数用于返回与指定字符相对应的十进制数值。 **语法格式:** ```sql ASCII('字符') ``` **示例:** ```sql SELECT ASCII('A') A, ASCII('a') ...

    Oracle常用函数.pdf

    ### Oracle常用函数详解 #### 一、概述 Oracle 数据库提供了丰富的内置函数,极大地提高了 SQL 语句在处理数据时的灵活性与效率。这些函数主要分为两大类:单行函数和多行函数(也称为聚合函数)。单行函数通常...

    ORACLE 常用函数总结(80个)第1/2页

    以下是一些Oracle常用函数的详细说明: 1. ASCII函数:此函数返回给定字符的ASCII码,即该字符对应的十进制数字。例如,ASCII('A')返回65,ASCII(' ')返回32。 2. CHR函数:它接受一个整数并返回相应的字符。例如...

Global site tag (gtag.js) - Google Analytics