Oracle学习笔记十六:常用函数

时间:2020-10-18 22:41:00 来源:互联网 作者: 神秘的大神 字体:

一、单行函数

1.1、字符串函数

字符串函数接受字符参数,这些参数可以是表中的列,也可以是一个字符串表达式。

1)常用的字符串函数:

2)上面各函数的例子:

1.2、数值函数

数值函数接受数字参数,这些参数可以是表中的列,也可以是一个数字表达式。

1.3、日期函数

1)SYSDATE

作用:返回的是当前系统的时间。

SELECT SYSDATE FROM dual;

2)SYSTIMESTAMP

作用:返回的是当前系统的时间戳。

SELECT SYSTIMESTAMP FROM dual;

3)MONTHS_BETWEEN (date1, date2)

作用:用于计算date1和date2之间有几个月。

  • 如果date1在日历中比date2晚,那么MONTHS_BETWEEN()就返回一个正数。
  • 如果date1在日历中比date2早,那么MONTHS_BETWEEN()就返回一个负数。
  • 如果date1和date2日期一样,那么MONTHS_BETWEEN()就返回一个0。
SELECT MONTHS_BETWEEN(TO_DATE('2020-10-18','yyyy-mm-dd'),TO_DATE('2020-06-06','yyyy-mm-dd')) MONTHS FROM dual;

4)ADD_MONTHS(d,n)

作用:在某一个日期 d 上,加上指定的月数 n,返回计算后的新日期。

SELECT SYSDATE,ADD_MONTHS(SYSDATE,3) FROM dual;

5)LAST_DAY(d)

作用:返回指定日期当月的最后一天。

SELECT SYSDATE,LAST_DAY(SYSDATE) FROM dual;

6)NEXT_DAY(date,char)

作用:查询出指定时间的下一个星期几(由char指定)所在的日期。

char可以用1~7替代,1表示星期日,2代表星期一,以此类推。

char可以是星期一、星期二...星期日,即 monday,thusday...(看是什么字符集的)。

SELECT NEXT_DAY(SYSDATE,'星期二') FROM dual;
SELECT NEXT_DAY(TO_DATE('2020-06-01','yyyy-mm-dd'),3) FROM dual;

7)ROUND(d[,fmt])

作用:返回一个以 fmt 为格式的四舍五入日期值, d 是日期, fmt 是格式模型。默认 fmt 为 DDD,即月中的某一天。

  • 如果 fmt 为"YEAR"则舍入到某年的 1 月 1 日,即前半年舍去,后半年作为下一年。
  • 如果 fmt 为"MONTH"则舍入到某月的 1 日,即前月舍去,后半月作为下一月。
  • 默认为"DDD",即月中的某一天,最靠近的天,前半天舍去,后半天作为第二天。
  • 如果 fmt 为"DAY"则舍入到最近的周的周日,即上半周舍去,下半周作为下一周周日。
SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;

8)EXTRACT(fmt FROM d)

作用:提取日期中的特定部分。

  • fmt 为:YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配,也可以与 TIMESTAMP 类型匹配,但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。
  • HOUR 匹配的结果中没有加上时区,因此在中国运行的结果少 8 小时。
SELECT SYSDATE,EXTRACT(YEAR FROM SYSDATE) YEAR,EXTRACT(MONTH FROM SYSDATE) MONTH,EXTRACT(DAY FROM SYSDATE) DAY,
    EXTRACT(HOUR FROM SYSTIMESTAMP) HOUR,EXTRACT(MINUTE FROM SYSTIMESTAMP ) MINUTE,EXTRACT(SECOND FROM SYSTIMESTAMP) SECOND 
FROM dual;

1.4、转换函数

1)TO_CHAR(d|n[,fmt])

作用:把日期和数字转换为制定格式的字符串,Fmt是格式化字符串。

代码演示:TO_CHAR对日期的处理

SELECT TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24:MI:SS') "date" FROM dual;

代码解析:

  • 在格式化字符串中,使用双引号对非格式化字符进行引用。
  • 年(yyyy)、月(mm)、日(dd)、时(hh、hh24)、分(mi)、秒(ss)

代码演示:TO_CHAR对数字的处理

SELECT TO_CHAR(-123123.45,'L9.9EEEEPR') "date" FROM dual;

针对数字的格式化,格式化字符有:

2)TO_DATE(X,[,fmt])

作用:把一个字符串以fmt格式转换成一个日期类型。

 SELECT TO_DATE('2020-10-18','yyyy-mm-dd') ONEDAY FROM dual;

3)TO_NUMBER(X,[,fmt])

作用:把一个字符串以fmt格式转换为一个数字。

SELECT TO_NUMBER('-$12,345.67','$99,999.99') "NUM" FROM dual;

1.5、其它单行函数

1)NVL(X,VALUE)

作用:如果X为空,返回VALUE,否则返回X。

--对工资是2000元以下的员工,如果没发奖金,每人奖金100元。
SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;

2)NVL2(X,VALUE1,VALUE2)

作用:如果X非空,返回VALUE1,否则返回VALUE2。

--对EMP表中工资为2000元以下的员工,如果没有奖金,则奖金为200元,如果有奖金,则在原来的奖金基础上加100元。
SELECT ENAME,JOB,SAL,NVL2(COMM,COMM+100,200) "COMM" FROM EMP WHERE SAL<2000;

3)DECODE(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

作用:

CASE 
    WHEN 条件=值1 THEN
        返回值1
    WHEN 条件=值2 THEN
        返回值2
        ......
    WHEN 条件=值n THEN
        返回值n
    ELSE
        缺省值
END
--年龄在30以上的显示">",30以下的显示"<",刚好是30的显示"="。
SELECT ID,NAME,AGE,DECODE(SIGN(AGE-30),1,'>',-1,'<',0,'=','未知') AS SYMBOL FROM STUDENT;

二、聚合函数

作用:聚合函数同时对一组数据进行操作,返回一行结果,比如计算一组数据的总和,平均值等。

--求本月所有员工的基本工资总和
SELECT SUM(SAL) FROM EMP;

--求不同部门的平均工资
SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;

 

参考自:

https://blog.csdn.net/u011955534/article/details/14168913