• Oracle数据库从入门到精通 单行函数问题


    视频课程:李兴华 Oracle从入门到精通视频课程

    学习者:阳光罗诺

    视频来源:51CTO学院

    Oracle数据库从入门到精通-单行函数

    在数据库中,为了方便用户的数据开发,往往会提供一系列的支持函数,利用这些函数可以针对于数据处理。

    例如:在进行根据姓名查询的时候,如果说姓名本身是大写字母,而查询的是小写字母,此时就不会由任何的数据结果返回。所以针对于此类情况,往往数据保存的时候或者是查询的时候对数据进行一些处理,而这些处理每一个数据库都有自己本身的函数库,利用函数可以实现特定的功能。

    在Oracle中,对于函数的基本使用结构如下:

    1     返回值 函数名称(列|数据)

    而根据函数的特点,单行函数可以分为以下几种:字符串函数、数值函数、日期函数、转换函数以及通用函数。

    一、字符串函数

    字符串函数可以针对于字符串数据进行处理,在Oracle之中对于此类函数定义有如下变化:UPPER()、LOWER()、INITCAP()、REPLACE()、LENGTH()、SUBSTR()。

    1.大小写转换函数

          转大写函数:字符串 UPPER(列|字符串)

          转小写函数:字符串 LOWER(列|字符串)

    如果要在Oracle数据库中验证字符串函数,那么就必须保证编写的是完整的SQL语句。所以为了可以方便地进行函数验证,往往会使用一张虚拟表:dual表

    范例:验证函数

    语法格式:

    1 SELECT LOWER('SOLer He'),UPPER('SOLer He') FROM  dual;

    几乎所有的数据库里面都会提供这两个函数。

    如果说现在要求用户自己输入一个雇员姓名,而后进行雇员信息的查找。

    语法格式:

    1 SELECT * FROM emp WHERE ename='&inputename';

    结果如下:

    用户在进行数据输入的时候几乎不会去考虑大小写,所以为了保证数据可以正常的查询出来,往往需要对输入数据进行处理。由于在数据表中所有的数据都是大写操作,那么就可以接收完输入数据之后将会自动变为大写字母。

     

    范例:改善输入操作。

    语法格式:

    1 SELECT * FROM emp WHERE ename=UPPER('&inputename');

    所以在一些要求严格的操作环境下,对于不区分大小写的操作的时候,基本上就会有两种做法:

     

    1. 在数据保存的时候将所有的数据统一变为大写或者是小写,这样子在查询的时候就可以直接利用特定的函数进行处理。
    2. 在数据保存的时候依然是按照原始的方式进行保存,而后在查询的时候将每一个数据中的字母变为大写形式进行处理。

    在所有不区分大小写的操作的项目之中,保存数据时就必须对数据进行提前的处理。

     

     

    2.首字母大写

    语法格式:

    1 字符串 INITCAP(列 | 数据)

     

    范例:观察首字母大写

     

    代码格式:

    1 SELECT INITCAP ('HeLLoWorld') FROM dual;

    查询输出结果:

     

    除了首字母大写之外,其他的都是小写。

     

    范例:将每一个雇员的姓名首字母变为大写。

     

    代码结构:

    1 SELECT INITCAP (ename) FROM emp;

    查询结果:

     

    3.计算字符串长度

    语法:

    1 数字 LENGTH (列 | 字符串数据)

    范例:查询出每个雇员姓名以及雇员姓名的长度。

     

    代码格式:

    1 SELECT ename,LENGTH(ename) FROM emp;

    查询结果:

     

    那么所有的单行函数可以在SQL语句的任意位置上出现。

     

    范例:查询雇员姓名长度为5的全部雇员信息。

     

             分析:需要针对于所选的数据行进行筛选,那么就一定要在WHERE子句之中进行。

     

    代码格式:

    1 SELECT * FROM emp WHERE LENGTH(ename)=5;

    结果如图:

     

    4.字符串的替换操作

    可以使用指定的内容替换原始字符串中的数据。

     

    语法格式:

    1 字符串  REPLACE (列 | 数据,要查找的内容,新的内容)

    范例:将所有雇员姓名之中的字母替换为“_”。

     

    代码示例:

    1 SELECT REPLACE (ename,'A','_') FROM emp;

    查询结果:

     

    实际上可以利用REPLACE()函数可以消除字符串中的全部空格数据。

     

    范例:消除空格数据。

     

    代码示例:

    1 SELECT REPLACE('Hello World This is my oracle',' ','') FROM dual;

    查询结果:

     

     

    5.字符串截取

             语法一:字符串 SUBSTR(列 | 数据,开始点),从指定的开始点一直截取到结尾

             语法二:字符串 SUBSTR(列 | 数据,开始点,长度):截取指定范围的子字符串。

     

    范例:子字符串截取操作。

     

    代码示例: 

    1 SELECT SUBSTR('helloworldnihao',11) FROM dual;

    查询截图:

    此种方式就是从指定位置截取到结尾。

     

    范例:截取部分内容

    代码示例:

    1 SELECT SUBSTR('helloworldnihao',6,5) FROM dual;

    结果:

     

    但是对于SUBSTR()函数千万要记住一点,它的下标是从1开始的,也就是在进行截取的时候,字符串从1开始作为索引下标,但是即使设置的值是0,也是按照1来处理。

    例如:代码示例:

    1 SELECT SUBSTR('helloworldnihao',0,5) FROM dual;
    2 
    3 SELECT SUBSTR('helloworldnihao',1,5) FROM dual;

    结果:

     

     

    范例:要求截取每一位雇员姓名的前三位字符

     

    代码示例:

    1 SELECT  ename,SUBSTR(ename,1,3) FROM emp;

    结果如下:

     

    范例:要求截取姓名的后三个字母。此范例可以采用两种做法:

            

             第一种(传统做法):如果进行截取,那么首先一定要确认出截取的开始点,所以对于开始点,由于每一个姓名的长度都是不一样的,所以开始点也是不一样的。所以采用最好的办法就是进行计算。就可以使用LENGTH来计算长度。

     

    代码示例:

    1 SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp;

    查询结果:

     

     

    SUBSTR()的支持,可以设置负数索引。

    代码示例:

    1 SELECT ename,SUBSTR(ename,-3) FROM emp;

    实际上只有Oracle数据库才会支持这种负数的索引设计,其他的任何语言都是不支持。

     

    面试题:请问Oracle中的SUBSTR()函数截取字符串的索引是从1开始还是从0开始?

     

    答案:Oracle数据库中的字符串的索引都是从1开始的,即使设置的值是0,也会将其自动变为1开始执行。

     

     

    数值函数

    数值函数主要是针对于数字进行处理的,有三个主要的函数:ROUND()、TRUNC()、MOD()。

     

    1.四舍五入操作

    语法:

    1 数字 ROUND(列| 数字,[保留小数位])如果不设置小数位就表示不保留。

     

    范例:测试四舍五入

    代码示例及解析:

     1 SELECT
     2 
     3          ROUND(78915.678932654),           78916,小数点之后的内容直接进行四舍五入
     4 
     5          ROUND(78915.678932654,2),       78915068       保留两位小数
     6 
     7          ROUND(78915.678932654,-2),      78900,把不足5的数字全部取消了。
     8 
     9          ROUND(78985.678932654,-2),      79000,如果超过了5则进行进位
    10 
    11          ROUND(-15.32)                                 -15
    12 
    13 FROM dual;

    结果如下:

    2.截取小数,所有的小数都不进位。

    语法:

    1 数字 TRUNC(列 | 数字[,小数位]

    代码示例:

     1 SELECT
     2 
     3          TRUNC(78915.678932654),                    78916
     4 
     5          TRUNC(78915.678932654,2),                          78915.68
     6 
     7          TRUNC(78915.678932654,-2),               78900
     8 
     9          TRUNC(78985.678932654,-2),               79000
    10 
    11          TRUNC(-15.32)                                           -15
    12 
    13 FROM dual;
    14 
    15  

    结果如下:

     

    3.求模(求余数)

    语法:

    1 数字 MOD(列1 | 数字1,列2 | 数字2)

    范例:求模操作

    代码示例:

    1 SELECT MOD(10,3) FROM dual;

     

    日期函数(Oracle自己的特色)

    日期处理函数主要是进行日期处理,但是整个日期处理过程中会存在一个关键词的问题。如何可以取得当前的日期时间。在Oracle中会提供一个数据伪列。指的是一个列,但是不存在于表中,可是却可以像列一样进行数据的查询。那么这个伪列就是SYSDATE。

     

    代码示例:

    1 SELECT ename, hiredate, SYSDATE FROM emp;

    如果只是单纯地想要取得日期,可以使用简单一些,直接使用dual虚拟表就可以。

    代码示例:

    1 SELECT SYSDATE FROM dual;

     

    如果是用具体地时间戳,那么就可以直接加SYSTIMESTAMP。

    代码示例:

    1 SELECT SYSDATE,SYSTIMESTAMP FROM dual;

    实际上对于日期时间提供有三种计算模式:

                      

    1 日期 + 数字 = 日期(若干天后地日期)
    2 
    3 日期 – 数字 = 日期(若干之前地日期)
    4 
    5 日期 – 日期 = 数字 (两个日期间的天数)
    6 
    7                      

    测试:若干天后的日期

     

    代码示例1:

    1 SELECT SYSDATE+10 FROM dual;

     

    代码示例2:

    1 SELECT SYSDATE+10FROM dual;

    在进行日期与数字的计算之中,得到的结果都是比较容易理解的。

     

    范例:计算每一位雇员到今天为止的雇佣天数。

    代码示例:

    1 SELECT ename,hiredate,SYSDATE-hiredate FROM emp;

     

    通过以上的分析发现,如果现在只是依靠天数实际上是很难得到一个准确的年或者是月,所以为了可以精确的进行计算,在Oracle里面才提供有日期处理函数,利用这些函数可以避免掉那些闰年或者是闰月的问题。

     

    计算两个日期间所经历的月数总和。

    语法:

    1 数字 MONTHS_BETWEEN(日期1,日期2)

    范例:计算每一位雇员到今天为止的雇佣总月数。

    代码示例:

    1 SELECT ename,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;

    实际上,现在已经存在有月的数据了,那么就表示可以准确计算年。

     

    范例:计算每一个雇员到今天为止所雇佣的年限。

    代码示例:

    1 SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) FROM emp;

    增加若干月之后的日期。

    语法:

    1 日期 ADD_MONTHS(日期,月数)

    范例:测试ADD_MONTHS()函数。

     

    代码示例:

    1 SELECT ADD_MONTHS(SYSDATE,4) FROM dual;

    利用这种方式增加的月可以避免闰年、闰月这两个问题。

     

    范例:计算所有还差1年满34年雇佣日期的全部雇员。

    代码示例:

    1 SELECT * FROM emp WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate)/12)=34;
    1. 计算指定日期所在月的最后一天。

          语法:

    1              日期 LAST_DAY(日期)      

     

    范例1:计算当前日期所在月的最后一天。

    代码示例:

    1 SELECT LAST_DAY(SYSDATE) FROM dual;

    范例2:查询出所有在雇佣所在月倒数第二天被雇佣的雇员信息

             每一个雇员的雇佣日期都是不一样的,所以在每一个雇佣日期所在月的倒数第二天也不一样的。

             ·首先应该知道每一个雇员雇佣月的最后一天,而后利用“日期 + 数字 = 日期”,计算倒数第二天。

     

    代码示例:

    1 SELECT ename,hiredate, LAST_DAY(hiredate),LAST_DAY(hiredate)-2
    2 
    3 FROM emp
    4 
    5 WHERE LAST_DAY(hiredate)-2=hiredate;

    1. 计算下一个指定的日期

        语法:日期 next_day(日期,一周时间数)

     

    范例:计算下一个周二

    代码示例:

    1 SELECT NEXT_DAY(SYSDATE,'星期二') FROM dual;

    【了解】综合分析:要求查询雇员的编号、姓名、雇佣日期,以及每一位雇员到今天为止所雇佣的年数、月数、天数。

             假设现在的日期是:2018-07-06

     

    现在WARD他的雇佣日期为:“1981-02-22”,所以它到今天为止的雇佣日期已经被雇佣了:35年、0月,15天。

    对于该查询而言,由于日期的跨度较长。所以要想准确的计算出结果。

     

    代码示例:

     

    1 SELECT empno,aname,hiredate
    2 
    3          TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year
    4 
    5 FROM emp;

    第二步:计算月

    在进行计算年的时候就包含余数,余数实际上就是除以12的结果,余数就是月数。利用MOD()函数求出余数。

     

    加上TRUNC之后的结果如下:

     

    第三步:计算天数

             计算天数的操作只有一个公式:“日期1 – 日期2 = 数字(天数)”,现在就出现了日期的问题上:

                      日期1:一定是当前日期,肯定是使用SYSDATE伪例。

                      日期:实际上可以使用MONTHS_BETWEEN()函数求出两个日期之间的月数。

     

    观察可以发现,天数里面计算结果也会有小数点,所以我们加上TRUNC之后,就会只有整数形式的结果。

     

     

    转换函数(重点)

    就目前而言,在Oracle中的三种数据类型:字符串、数字、日期。所以所谓的转换函数的实现字符串与日期、数字之间的转换。

    转换函数一共提供有三种:TO_CHAR()、TO_DATE()、TO_NUMBER()。针对于转换函数而言,重点的是TO_CHAR()。

     

    1.转字符串函数,数字或者是日期可以转换为字符串。
             语法:字符串 TO_CHAR(列 | 日期 | 数字,转换格式)

             对于转换格式而言,主要有两类格式:

    -        日期转换为字符串:年(yyyy)、月(mm)、日(hh、hh24)、分(mi)、秒(ss)。

    -        数字转换为字符串:任意一位数字(9)、货币(L,本地货币)。

     

    范例:格式化日期。

    代码格式:

    1 SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy-mm-dd hh24-mi-ss')FROM dual;

    在这里提供了思想:日期要想该改变日期,最终的数据类型就是字符串。如果要这样子转换实际上会破坏程序的一致性。

     

    实际上现在可以进一步探索TO_CAHR()好处,它可以实现年月份、月、日的拆分。

     

    范例:查询出,每个雇员的编号、姓名、雇佣年份。

    代码示例:

    1 SELECT * empno,ename,TO_CHAR(hiredate,'yyyy') year FROM emp;

    范例:查询出所有在2月雇佣的雇员信息。

    代码示例:

     

    Oracle中实际上提供有数据类型的自动转换,如果发现比较的类型不统一,在一定的范围内是可以转换的。

     

    TO_CHAR()函数除了可以进行日期的转换之外,也是支持数字转换的。所谓的数字转换往往是针对于数字的可读性进行一些格式化的操作。

     

    范例:转换数字

    代码示例:

    1 SELECT TO_CHAR(8899,'L999,999,999,999') FROM dual;

    2.转日期函数

    如果说现在某一个字符按照“日 - 月 - 年”的格式去编写。那么可以自动转换为日期类型,但是也可以依靠TO_DATE()函数来完成。

     

    语法格式:

    1 TO_DATE(字符串,转换格式)

    -        年(yyyy)、月(mm)、日(hh、hh24)、分(mi)、秒(ss)。

     

    范例:实现字符串转换为日期。

    代码示例:

    1 SELECT TO_DATE('1995-05-02','yyyy-mm-dd')  FROM dual;

    3.转数字函数

             可以将字符串(由字符串所组成),变为数字。

     

             语法格式:

    1 数字 TO_NUMBER(字符串)。

    范例:验证转数字函数

    代码示例:

    1 SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM dual;

    通用函数(oracle自己的特色)

    在Oracle中提供了两个简单的数据处理函数:NVL()、DECODE()。并且随着版本的提升,此两个函数也衍生出了许多的子函数。

     

    1.处理null

    计算出每一个雇员的年薪,包括基本工资和佣金。

     

    代码示例:

    1 SELECT empno,ename,job,(sal+comm)*12 income FROM emp;

    现在发现,所有没有佣金的雇员,现在进行年收入计算的时候,最终的计算结果都是Null ,因为null在进行任何数学计算的时候,结果永远都是null。而实际上在计算之中,如果发现内容为null,如果是数字则应该使用0来替代,那么就需要利用我们的NVL()函数来解决此类问题。

             语法格式:

    1 NVL(列 | null,为空的默认值)如果在列上的内容不是null则使用列的数据,如果为null,则使用默认值。

             代码示例:

    1 SELECT empno,ename,job,sal,NVL(comm,0),(sal+NVL(comm,0))*12 income FROM emp;

    2.多数值判断

             所谓的多数值判断,指的是根据不同的结果可以在输出的时候进行严格数据的转换,假设每一个雇员都有自己的职位。职位现在使用的是英文描述,决定使用更换为中文描述。

    代码示例:

    1 SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售','暂无此信息') FROM emp;
    2 
    3  
    4 
    5 SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售') FROM emp;

     

     

  • 相关阅读:
    codevs 3641 上帝选人
    codevs 1966 乘法游戏
    codevs 1345 饥饿的奶牛
    codevs 1962 马棚问题--序列型DP
    codevs 1959 拔河比赛--判断背包内刚好装满n/2个物品
    codevs 1297 硬币
    [转载]矩阵取数游戏
    101.金明的预算方案(分组背包)
    105.(并查集结合绝对值最小的01背包)选学霸
    POJ 2528 Mayor's posters(线段树)
  • 原文地址:https://www.cnblogs.com/launolife/p/9279342.html
Copyright © 2020-2023  润新知