暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Oracle SQL语句学习（3）--单行函数的使用

Oracle SQL语句学习（3）--单行函数的使用

原创 张均 云和恩墨 2022-09-10
468

一. 概述

上一篇文章里，了解了过滤查询语句和排序查询子句的应用，其主要是对比较操作符的使用，在经过排序，就能准确筛选出自己想要的的到信息。本篇文章主要介绍了SQL语句中单行函数的使用。

二. 单行函数

2.1 函数

SQL函数与平时接触的数学函数一样，其本质就是将参数输入函数体，再由函数体输出结果的过程。根据输入行数的多少，可以分为单行函数和多行函数：
image.png

2.2 单行函数

  • 格式：function_name [(ARG1,ARG2,…)]

操作数据对象
接受函数返回一个结果
只对一行进行变换
每行返回一个结果
可以转换数据类型，计算，格式化
可以嵌套
参数可以是列，值，表达式

  • dual表
    dual表是一个虚拟表，并不真实存在，其只包含一个列DUMMY，数据类型为varchar2(1)。DUAL表的存在只是为了保证【select…from…】语法的完整性
    例如我们想要计算【1+1】的时候，在其他数据库中可以执行SELECT 1+1;，但是在oracle中就必须补充完整的语法【from dual】才能正确执行：
SCOTT@oradb> SELECT 1+1;
SELECT 1+1
         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


SCOTT@oradb> SELECT 1+1 FROM DUAL;

       1+1
----------
         2

2.3 字符函数

2.3.1 大小写转换函数

这一类函数主要是以下三个：

函数 含义 结果
LOWER(‘SQL Course’) 将字符串全部变为小写 sql course
LOWER(‘SQL Course’) 将字符串全部变为大写 sql course
LOWER(‘SQL Course’) 将字符串变为首字母大写 sql course 
SCOTT@oradb> select ename,lower(ename),upper(ename),initcap(ename) from emp;

ENAME      LOWER(ENAM UPPER(ENAM INITCAP(EN
---------- ---------- ---------- ----------
SMITH      smith      SMITH      Smith
ALLEN      allen      ALLEN      Allen
WARD       ward       WARD       Ward
JONES      jones      JONES      Jones
MARTIN     martin     MARTIN     Martin
BLAKE      blake      BLAKE      Blake
CLARK      clark      CLARK      Clark
SCOTT      scott      SCOTT      Scott
KING       king       KING       King
TURNER     turner     TURNER     Turner
ADAMS      adams      ADAMS      Adams
JAMES      james      JAMES      James
FORD       ford       FORD       Ford
MILLER     miller     MILLER     Miller
  • 注意的是，在initcap使用的时候会出现分隔符的情况，分割符的作用就是分割单词
SCOTT@oradb> select initcap('beijing,enmoedu_ocp%ocm') from dual;

INITCAP('BEIJING,ENMOED
-----------------------
Beijing,Enmoedu_Ocp%Ocm

空格、下划线、%、！、$等符号都是有效的单词分隔符（特殊符号 标点符号）

2.3.2 字符操作函数

这一类函数主要有以下8个：

函数 含义 结果
CONCAT(‘HELLO’,‘WORLD’) 拼接 HELLOWORLD
SUBSTR(‘HELLOWORLD’,1,5) 截取字符串 HELLO
LENGTH(‘HELLOWORLD’) 获取字符串长度（字符数） 10
INSTR(‘ORACLE’,‘A’) 后面子串在前面字符串第一次出现的位置 3
LPAD(‘123’,10,’*’) 用*左补全该列的值 *******123
RPAD(‘123’,10,’*’) 用*右补全该列的值 123*******
TRIM(‘H’ FROM ‘HELLOWORLD’) 去掉空格或自定义去掉的字符(只针对首尾的字符） ELLOWORLD
REPLACE(‘JACK AND JUE’,‘J’,‘BL’) 替换 BLACK AND BLUE

2.4 使用字符操作函数

2.4.1 CONCAT

  • 查询emp表中的enmae和job并使用is拼接。

在之前的学习中，使用拼接符可以完成类似的查询：
SCOTT@oradb> select ename,job,ename||’ is a '||job from emp;

使用CONCAT函数需要嵌套才能完成查询：

SCOTT@oradb> SELECT CONCAT(CONCAT(ENAME,' IS '),JOB) FROM EMP;

CONCAT(CONCAT(ENAME,'IS
-----------------------
SMITH IS CLERK
ALLEN IS SALESMAN
WARD IS SALESMAN
JONES IS MANAGER
MARTIN IS SALESMAN
BLAKE IS MANAGER
CLARK IS MANAGER
SCOTT IS ANALYST
KING IS PRESIDENT
TURNER IS SALESMAN
ADAMS IS CLERK
JAMES IS CLERK
FORD IS ANALYST
MILLER IS CLERK

2.4.2 SUBSTR

截取字符串函数可以分为两种格式：

  • substr(STRING,a,b)

-STRING为需要截取的字符串
-a表示截取字符串的开始位置：a=0或1的时候都是从第一个字符开始截取
-a为负时表示倒数
-b表示需要截取的字符串长度

SCOTT@oradb> select substr('oracle',1,3) from dual;

SUB
---
ora
# 截取'oracle'字符串从第一个字符o开始，截取长度为3
SCOTT@oradb> select substr('oracle',-2,2) from dual;

SU
--
le
#截取截取'oracle'字符串从倒数第二个字符l开始，截取长度为2
  • substr(STRING,a)

STRING表示需要被截取的字符串
a可以理解为从第a个字符开始截取后面所有的字符

SCOTT@oradb> select substr('oracle',4) from dual;

SUB
---
cle
# 截取oracle从第四个字符c开始到最后的字符串
SCOTT@oradb> select substr('oracle',-5) from dual;

SUBST
-----
racle
# 截取oracle从倒数第五个字符r开始到后面所有的字符

2.4.3 LENGTH

获取字符串长度（字符数）

SCOTT@oradb> select length('oracle') from dual;

LENGTH('ORACLE')
----------------
               6

2.4.4 INSTR

INSTR函数也有两种语法：

  • instr(STRING1,STRING2)

表示子串STRING2在STRING1字符串中第一次出现的位置

SCOTT@oradb> select instr('oracle','cle') from dual;

INSTR('ORACLE','CLE')
---------------------
                    4

=0 表示没有。
instr 经常用来做判断，判断一个字符串是否在另外一个字符串中。

  • instr(STRING1,STRING2,a,b)

a为正时，表示查询从第a个字符开始，STRING2在STRING1第b次出现的位置
a为负时，表示查询从倒数第|a|个字符开始，向前查询，STRING2在STRING1第b次出现的位置

SCOTT@oradb> select instr('1*2*3*4*5*','*',3,4) from dual;

INSTR('1*2*3*4*5*','*',3,4)
---------------------------
                         10
# 查询从第 3 个字符开始，*第 4 次出现的位置
SCOTT@oradb> select instr('1*2*3*4*5*','*',-3,2) from dual;

INSTR('1*2*3*4*5*','*',-3,2)
----------------------------
                           6
#查询从倒数第 3 个字符开始向前，*第 2 次出现的位置,此时字符1的位置为10

2.4.5 LPAD/RPAD

  • LPAD(STRING1/COLUMN,a,STRING2)

string1和column为被补全的对象，a表示补全后的字符总数，string2表示用该子串对string1的左边进行补全
右补全函数作用类似，只是补全方向从原字符串右边开始

#作用于emp表中的字段
SCOTT@oradb> select sal,lpad(sal,4,0),rpad(sal,4,0) from emp;

       SAL LPAD(SAL RPAD(SAL
---------- -------- --------
       800 0800     8000
      1600 1600     1600
      1250 1250     1250
      2975 2975     2975
      1250 1250     1250
      2850 2850     2850
      2450 2450     2450
      3000 3000     3000
      5000 5000     5000
      1500 1500     1500
      1100 1100     1100
       950 0950     9500
      3000 3000     3000
      1300 1300     1300

# 补全的字段也可以是一个字符串，补全的时候重复该子串
SCOTT@oradb> select lpad(123,10,'abcd') from dual;

LPAD(123,1
----------
abcdabc123

2.4.6 TRIM

TRIM也有两种使用方法：

  • TRIM(string)

这里的作用是去掉字符串首位的空格

SCOTT@oradb> SELECT TRIM('    HELLO WORLD   ') FROM DUAL;

TRIM('HELLO
-----------
HELLO WORLD
  • TRIM(STRING1 FROM STRING2)

-STRING1只能为单个字符，例如’A’,‘B’,’*’
-这种使用方法的作用是去掉string2首尾含string1的部分
-trim（string）相当于trim（’ ’ FROM string）

SCOTT@oradb> SELECT TRIM('*' FROM '*    HELLO WORLD   *') FROM DUAL;

TRIM('*'FROM'*HELL
------------------
    HELLO WORLD
  • 注意TRIM函数只针对首尾的连续字符进行去除
SCOTT@oradb> SELECT TRIM('*' FROM '***HELLO WORLD***') FROM DUAL;

TRIM('*'FRO
-----------
HELLO WORLD
#去除首尾连续的字符'*'
SCOTT@oradb> SELECT TRIM('*' FROM '*  *  HELLO WORLD *  *') FROM DUAL;

TRIM('*'FROM'**HELLO
--------------------
  *  HELLO WORLD *
#由于第二个*与第一个不连续，所以中间的*不会被去除

2.4.7 REPLACE

  • replace(string1/column，string2，string3）

-STRING1是需要被修改的字符串或表的字段，string2表示string1中需要被替换的部分子串，string3就是替换的子串
-string3可以不写，那么该函数就相当于replace(STRING1/COLUMN,STRING2,’’)，删除string1中的string2部分

SCOTT@oradb> select replace('oracle','ora','ORA-') from dual;

REPLACE
-------
ORA-cle

SCOTT@oradb> select replace(hiredate,'-') from emp;

REPLACE(HIRE
------------
17DEC80
20FEB81
22FEB81
02APR81
28SEP81
# HIREDATE列原来的格式为'17-DEC-80'

2.5 数字函数

函数 含义 结果
ROUND(45.926,2) 保留小数2位，四舍五入 45.93
TRUNC(45.926,2) 截断到指定位数，后位赋0，不四舍五入 45.92
MOD(1600,300) 1600/300的余数 100

2.5.1 ROUND

  • ROUND(NUMBER,a)

四舍五入NUMBER到指定的第a十进制位，需要四舍五入
对于a的取值可以参考下图,a=0时可以不写入函数
image.png

SCOTT@oradb> select round(45.923,2), round(45.923,0),round(45.923,-1) from dual;

ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
          45.92              46               50

2.5.2 TRUNC

  • TRUNC(NUMBER,a）

截断number到指定的第a十进制位，不进行四舍五入。可理解为第a位以后的各位值都为0

SCOTT@oradb> select trunc(45.923,2), trunc(45.923),trunc(45.923,-1) from dual;

TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1)
--------------- ------------- ----------------
          45.92            45               40

2.5.3 MOD

  • MOD(NUMBER1,NUMBER2)

该函数结果相当于NUMBER1除以NUMBER2得到的余数

SCOTT@oradb> select mod(1600,300) from dual;

MOD(1600,300)
-------------
          100
  • 可以利用trunc函数来表示商：
    TRUNC(NUMBER/NUMBER,0)
# 求1600和300的商和余数
SCOTT@oradb> select trunc(1600/300,0),mod(1600,300) from dual;

TRUNC(1600/300,0) MOD(1600,300)
----------------- -------------
                5           100

2.6 日期函数

函数 结果
MONTHS_BETWEEN 两个日期相差的月数
ADD_MONTHS 向指定日期中加上若干月数
NEXT_DAY 指定日期的下一个日期
LAST_DAY 本月的最后一天
ROUND 日期四舍五入
TRUNC 日期截断

2.6.1 sysdate

返回 Oracle 数据库服务器所在操作系统的当前日期和时间

SCOTT@oradb> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
10-SEP-22

SYSDATE默认不显示时间，只显示日期，可以通过修改nls_date_format来修改

SCOTT@oradb> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss day';

Session altered.
SCOTT@oradb> select sysdate from dual;

SYSDATE
-----------------------------
2022-09-10 21:05:05 saturday

2.6.2 日期的计算

  • select sysdate+1 from dual; 1天以后
  • select sysdate,sysdate+1/24 from dual; 1小时后
  • select sysdate,sysdate+1/24/60 from dual; 1分钟后
SCOTT@oradb> SELECT SYSDATE+1 FROM DUAL;

SYSDATE+1
-----------------------------
2022-09-11 21:10:22 sunday

SCOTT@oradb> SELECT SYSDATE+1/24 FROM DUAL;

SYSDATE+1/24
-----------------------------
2022-09-10 22:10:34 saturday

SCOTT@oradb> SELECT SYSDATE+1/24/60 FROM DUAL;

SYSDATE+1/24/60
-----------------------------
2022-09-10 21:11:40 saturday

2.6.3 MONTHS_BETWEEN

  • 计算两个日期相差的月数
select empno,ename,sal,months_between(sysdate,hiredate) months from emp;

     EMPNO ENAME             SAL     MONTHS
---------- ---------- ---------- ----------
      7369 SMITH             800 500.802802
      7499 ALLEN            1600 498.706027
      7521 WARD             1250 498.641511
      7566 JONES            2975 497.286673
      7654 MARTIN           1250 491.447963
      7698 BLAKE            2850 496.318931
      7782 CLARK            2450 495.060866
      7788 SCOTT            3000 424.738286
      7839 KING             5000 489.802802
      7844 TURNER           1500 492.093124
      7876 ADAMS            1100 423.609253
      7900 JAMES             950 489.254415
      7902 FORD             3000 489.254415
      7934 MILLER           1300 487.609253

2.6.4 ADD_MONTHS

  • 为日期增加月份

select sysdate from dual;
select sysdate,add_months(sysdate,1) from dual; 一个月后的现在
select sysdate,add_months(sysdate,12) from dual; 一年后
select sysdate,add_months(sysdate,-12) from dual; 一年前

SCOTT@oradb> SELECT ADD_MONTHS(SYSDATE,1),ADD_MONTHS(SYSDATE,12),ADD_MONTHS(SYSDATE,-12) FROM DUAL;

ADD_MONTHS(SYSDATE,1)         ADD_MONTHS(SYSDATE,12)        ADD_MONTHS(SYSDATE,-12)
----------------------------- ----------------------------- -----------------------------
2022-10-10 21:22:18 monday    2023-09-10 21:22:18 sunday    2021-09-10 21:22:18 friday

2.6.5 NEXT_DAY

  • 指定某日期的下一天(可指定天数/星期)

select next_day(sysdate,‘MONDAY’) from dual; 下周一
select next_day(sysdate,n) from dual; 下n天,其中n只能在[1-7]的整数中取值

SCOTT@oradb> select next_day(sysdate,'FRIDAY') from dual;

NEXT_DAY(SYSDATE,'FRIDAY')
-----------------------------
2022-09-16 21:27:05 friday

2.6.6 LAST_DAY

  • 特指日期当月最后一天的现在
SCOTT@oradb> SELECT LAST_DAY(SYSDATE) FROM DUAL;

LAST_DAY(SYSDATE)
-----------------------------
2022-09-30 21:39:07 friday

2.6.7 ROUND

  • 对日期进行四舍五入,可以选择天,月,年,小时,分钟
SCOTT@oradb> select round(sysdate) from dual;

ROUND(SYSDATE)
-----------------------------
2022-09-11 00:00:00 sunday

SCOTT@oradb> select round(sysdate,'hh') from dual;

ROUND(SYSDATE,'HH')
-----------------------------
2022-09-10 21:00:00 saturday

SCOTT@oradb> select round(sysdate,'mm') from dual;

ROUND(SYSDATE,'MM')
-----------------------------
2022-09-01 00:00:00 thursday

SCOTT@oradb> select round(sysdate,'mi') from dual;

ROUND(SYSDATE,'MI')
-----------------------------
2022-09-10 21:29:00 saturday

2.6.8 TRUNC

  • 对日期取整
SCOTT@oradb> select trunc(sysdate) from dual;

TRUNC(SYSDATE)
-----------------------------
2022-09-10 00:00:00 saturday

SCOTT@oradb> select sysdate,trunc(sysdate,'hh') from dual;

SYSDATE                       TRUNC(SYSDATE,'HH')
----------------------------- -----------------------------
2022-09-10 21:31:23 saturday  2022-09-10 21:00:00 saturday

2.7 函数的嵌套

  • 单行函数可以嵌套到任意层级
  • 嵌套函数的执行顺序时由内到位
    image.png
SCOTT@oradb> select ename,concat(lower(substr(ename, 1, 3)), '_US') from emp where deptno = 10;

ENAME      CONCAT(LO
---------- ---------
CLARK      cla_US
KING       kin_US
MILLER     mil_US

三. 总结

单行函数.png

墨力计划oracleoracle 函数
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Oracle SQL学习笔记（共9篇）
oracle萌新的sql学习笔记

评论