美文网首页
oracle学习笔记三——查询之单行函数

oracle学习笔记三——查询之单行函数

作者: 涨财司 | 来源:发表于2018-08-06 21:06 被阅读0次

    三、单行函数

    --什么是单行函数?

    单行函数:function_name [(arg1, arg2,...)]

            操作数据对象

            接受参数返回一个结果

            只对一行进行变换

            每行返回一个结果

            可以转换数据类型

            可以嵌套

           参数可以是一列或一个值

    说明:这里会尽量给出每个函数的运行结果,以防止没说明白,可以对照命令和结果理解清楚


    1.字符函数

    (1)大小写控制函数(lower,  upper,  initcap)

    SQL> select lower('HeLLO World')转小写,upper('HeLLO World')转大写,initcap('hello world') 首字母大写 from dual;

    -->结果:

    大小写控制函数

    (2)字符控制函数(CONCAT,SUBSTR,LENGTH/LENGTHB,INSTR,LPAD|RPAD,TRIM,REPLACE)

    --concat(a,b),拼接字符串a和b,在前面讲连接符“||”的时候说过了

    SQL> select concat('Hello', ' World') from dual;

    --substr(a,b,c) 从a中,第b位开始取,取c位

    SQL> select substr('hello world',3,4) from dual;

    -->结果:

    从第3位开始截取4位(最后有个空值)

    --substr(a,b) 从a中,第b位开始取,取右边的所有字符(默认指定了第三个参数到最后)

    SQL> select substr('hello world',3) from dual;

    -->结果:

    从第3位开始取右边的所有字符

    --length 字符数  lengthb 字节数

    SQL> select length('Hello World') 字符数,lengthb('Hello World') 字节数 from dual;

    -->结果:(英文的话,是一样的)

    英文,字符数=字节数

    SQL> select length('中国') 字符数,lengthb('中国') 字节数 from dual;

    -->结果:(中文的话,字节说大于字符数,至于大多少,要看编码)

    中文, 字符数<字节数

    --instr: 在母串中,查找子串;如果找到,返回下标(从1开始);否则返回0

    SQL> select instr('Hello World','ll') from dual;

    -->结果:

    注意,只是返回起始下标(从1开始),没有返回0

    --lpad 左填充 rpad 右填充

    SQL> select lpad('abcd',10,'*') 左填充,rpad('abcd',10,'*') 右填充 from dual;

    -->结果:

    左填充,右填充

    --trim 去掉前后指定的字符(注意语法,参数里要用from)

    SQL> select trim('H' from 'Hello WorldH') from dual;

    -->结果:

    注意,trim参数里要用from

    --replace 替换

    SQL> select replace('Hello World','l','*') from dual;

    -->结果:(注意第二个参数,如果是lo,则结果是“Hel* World”)

    replace 替换字符

    2.数字函数

    --ROUND:四舍五入(不仅可以对数字,也可以对日期,后面会说)

    SQL> select ROUND(45.926, 2) 一,ROUND(45.926, 1) 二,ROUND(45.926, 0) 三,        ROUND(45.926, -1) 四,ROUND(45.926, -2) 五 from dual;

    -->结果:

    ROUND,可以自己琢磨参数,正负的含义

    --TRUNC:截断(不仅可以对数字,也可以对日期,后面会说)

    SQL> select TRUNC(45.926, 2) 一,TRUNC(45.926, 1) 二,TRUNC(45.926, 0) 三,

    TRUNC(45.926, -1) 四,TRUNC(45.926, -2) 五 from dual;

    -->结果:

    TRUNC,可以自己琢磨参数,正负的含义

    --MOD:求余

    SQL> select MOD(1600, 300) from dual;

    -->结果:100

    3.日期函数

    --先来查一下系统的时间

    SQL> select sysdate from dual;

    -->结果:(这个格式之前说过了,也说了怎么改,参见一,基本查询)

    格式:DD-MON-RR

    注:日期的数学运算

            1.在日期上加上或减去一个数字结果仍为日期。

            2.两个日期相减返回日期之间相差的天数

            3.可以用数字除24来向日期中加上或减去小时。

    --昨天 今天 明天

    SQL> select (sysdate-1) 昨天,sysdate 今天,(sysdate+1) 明天 from dual;

    -->结果:(运行日期:2018-08-04)

    对日期进行加减数值

    注:日期的加法,只能是数字,你加上个另一个日期是个啥子?

            减法可以是两个日期相减,返回天数

    --计算员工的工龄: 天 星期 月 年

    SQL> select ename,hiredate,(sysdate-hiredate) 天,(sysdate-hiredate)/7 星期,(sysdate-hiredate)/30 月, (sysdate-hiredate)/365 年 from emp;

    -->结果:(这只是算个大概,运行日期:2018-08-04)

    计算员工的工龄,大概计算

    --MONTHS_BETWEEN :计算两个日期之间的月份数,会自动考虑(28/29,30/31)

    SQL> select ename,hiredate,(sysdate-hiredate)/30 方式一,MONTHS_BETWEEN(sysdate,hiredate) 方式二 from emp;

    -->结果:(工龄:月份数,运行日期:2018-08-04)

    MONTHS_BETWEEN ,大值在前返回正数,小值在前返回负数

    --ADD_MONTHS:计算指定多少个月前(负数)/后(正数)是什么日期

    SQL> select ADD_MONTHS(sysdate,-119) "119个月前", ADD_MONTHS(sysdate,119) "119个月后" from dual;

    -->结果:(运行日期:2018-08-04)

    ADD_MONTHS,负值向前计算,正值向后计算

    --LAST_DAY:当前月的最后一天

    SQL> select LAST_DAY(sysdate) from dual;

    -->结果:

    LAST_DAY,当前月的最后一天

    --next_day:返回指定标识的下一个日期

    SQL> select next_day(sysdate,'星期日') from dual;

    -->结果:(运行日期:2018-08-04)

    next_day,指定标识的下一个日期

    注:

            1.这里的第二个参数,中文状态只能是:星期一~星期日(不能是周一,礼拜一等)。英文状态,只能是monday~sunday

            2.SQL> select * from v$nls_parameters可以查询当前Oracle数据库的参数(之前说过了):

               NLS_LANGUAGE  -->  SIMPLIFIED CHINESE 表示简体中文

    --对日期进行四舍五入

    SQL> select round(sysdate,'month'), round(sysdate,'year') from dual;

    -->结果:(运行日期:2018-08-04)

    round,也可以对日期使用

    说明:

            现在是2018-08-04,月未过半,故对month四舍五入是2018-08-01,而年已过半,故对year四舍五入是2019-01-01。

    --对日期进行截断

    SQL> select trunc(sysdate,'month'), trunc(sysdate,'year') from dual;

    -->结果:(运行日期:2018-08-04)

    trunc,也可以对日期使用

    4.转换函数

    --to_char:将字段,转换为指定格式的字符串,不限于日期

    --格式化日期,变成字符串

    SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

    -->结果:

    to_char,可以格式化日期

    --能不能直接将日期格式化为:2018-08-04 23:50:10 今天是 星期六,可以的

    SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;

    -->结果:

    to_char,在格式化字符的时候,可以添加其他字符

    说明:

            在格式化日期的时候,可以使用双引号向日期中添加字符

            DD "of" MONTH --> 12 of OCTOBER

    注:

            1.格式:

                1)必须包含在单引号中而且大小写敏感。

                2)可以包含任意的有效的日期格式。

                3)日期之间用逗号隔开。

            2.日期格式的元素

    常用的日期格式化元素

    --格式化数字,变成字符串

    --查询员工的薪水: 货币代码  两位小数 千位符

    SQL> select to_char(sal,'L9,999.99') from emp;

    -->结果:

    to_char,也可以格式化数值

    注:

        在TO_CHAR 函数中经常对数值使用的几种格式:

    常用的数值格式化元素

    --TO_NUMBER(char[, 'format_model']):将字符串转化为数值

    SQL> select to_number('10000') from dual;

    -->结果:10000

    SQL> select to_number('$7,356.87', '$9,999.99') from dual;

    -->结果:( '$9,999.99' --> '$99,999.99'也可以,自己琢磨)

    把格式匹配正确即可

    --TO_DATE(char[, 'format_model']):将字符转换成日期

    SQL> select to_date('20180805 00:09:53', 'yyyymmdd hh24:mi:ss') from dual;

    -->结果:

    需要正确匹配日期格式

    SQL> select to_date('05-8月-18') from dual;

    -->结果:

    省略,日期format的情况

    说明:

            如果想省略to_date的第二个参数,传入的字符串必须和Oracle数据库默认的格式(DD-MON-RR)一致

    补充:关于隐式转换(不推荐,尽量显示转换)

               使用上面的函数进行的转换都是显示转换,还有一种转换是隐式转换

               要想完成隐式转换,则传入的值必须符合格式要求。

               举例:

                       SQL> select * from emp where deptno='10';

                       -->结果:(Oracle会自动将’10‘转化为,数字10,因为deptno的类型是number)

    隐式转换,‘10’-->10


    5.通用函数(适用于任何数据类型,同时也适用于空值)

    --滤空函数nvl,nvl2

    --nvl(a,b)当a=null时,返回b; 否则返回自身

    --nvl2(a,b,c) 当a=null时,返回c; 否则返回b

    SQL> select ename, sal*12+nvl2(comm,comm,0) from emp;

    -->结果:

    使用滤空函数nvl2

    如果不加滤空函数会怎样?

    SQL> select ename, sal*12+comm from emp;

    -->结果:(这显然不是我们想要的,回顾我们说的null的第一个说明,包含null的表达式都是空值)

    未使用滤空函数

    说明:

            nvl2是nvl的增强,在Oracle中后面带2的都是不带2的增强,常见的还有如数据类型varchar2和varchar

    --nullif(a,b) 当a=b时,返回null,否则返回a

    SQL> select nullif('abc','abc') from dual;

    -->结果:

    nullif(a,b) 当a=b时,返回null

    SQL> select nullif('abc','adbc') from dual;

    -->结果:

    nullif(a,b) 当a!=b时,返回a    

    --COALESCE (expr1, expr2, ..., exprn): 返回第一个不为null的值

    SQL> select comm,sal,COALESCE(comm,sal) from emp;

    -->结果:

    COALESCE (expr1, expr2, ..., exprn): 返回第一个不为null的值

    注:

            1.COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。

            2.如果第一个表达式为空,则返回下一个表达式,对其他的参数进行COALESCE 。

            3.即:找第一个不为空的值。

    --CASE 表达式(条件表达式,适用所有对SQL99标准实现的数据库):即IF-THEN-ELSE 逻辑

    --报表:涨后的工资(总裁1000 经理800 其他400)

    SQL> select ename,job,sal 涨前薪水, case job when 'PRESIDENT' then sal+1000

                                                                              when 'MANAGER' then sal+800

                                                                              else sal+400

                                                                              end 涨后薪水

              from emp;

    -->结果:(SQL的书写规范,和其他语言一样,都很重要)

    使用case-when-else-end实现涨薪

    注:

            case表达式语法:

                CASE expr WHEN comparison_expr1 THEN return_expr1

                     [WHEN comparison_expr2 THEN return_expr2

                     WHEN comparison_exprn THEN return_exprn

                      ELSE else_expr]

                END

    --decode函数:Oracle提供的一个简化的case-when-else-end实现,只在Oracle中能用

    --报表:涨后的工资(总裁1000 经理800 其他400)

    SQL> select ename,job,sal 涨前薪水, decode(job,'PRESIDENT',sal+1000,

                                                                                    'MANAGER',sal+800,

                                                                                                         sal+400) 涨后薪水

              from emp;

    -->结果:和case-when-else-end逻辑实现的一样

    注:

            decode函数的参数说明:

                DECODE(col|expression, search1, result1

                   [, search2, result2,...,]

                   [, default])

    相关文章

      网友评论

          本文标题:oracle学习笔记三——查询之单行函数

          本文链接:https://www.haomeiwen.com/subject/bblbvftx.html