单行函数

• 本次文章主要学习
• • 单行函数语法
  • 单行函数特征
  • 单行函数分类
  • • 字符函数
    • • 大小写函数
      • • LOWER(列名|表达式)
        • UPPER(列名|表达式)
        • INITCAP(列名|表达式)
      • 字符处理函数
      • • CONCAT(column1|expression1,column2|expression2)
        • SUBSTR (column|expression,n1[,n2])
        • LENGTH(column | expression)
        • INSTR(s1,s2,[,n1],[n2])
        • LPAD(s1,n1,s2)
        • RPAD(s1,n1,s2)
        • TRIM
        • REPLACE(s1,s2,s3)
    • 数值函数
    • • • ROUND
        • TRUNC
        • MOD
    • 常用日期函数
    • • • SYSDATE
        • MONTHS_BETWEEN
        • ADD_MONTHS
        • NEXT_DAY
        • LAST_DAY
        • ROUND(date[,‘fmt’])
        • TRUNC(date[,‘fmt’])
        • EXTRACT
    • 转换函数
    • • • 隐式数据类型转换规则
        • 显式数据类型转换
        • TO_CHAR 用于日期型
        • TO_CHAR 用于数值型
        • TO_NUMBER 函数
        • TO_DATE 函数
    • 通用函数
    • • • NVL
        • NVL2
        • NULLIF
        • COALESCE
      • 条件处理函数：
      • • CASE表达式
        • DECODE
  • 收工，谢谢

本次文章主要学习

-理解函数的作用
-掌握单行函数的五大分类函数
-其中，会一直用到到的虚表
DUAL：是一张虚表，不能保存任何数据，只有一个字段，一行记录。当我们不希望从任何表中读取数据，同时又想利用SQL引擎中的计算表达式的能力帮我们运算时，就可以使用DUAL表

SQL函数概述

SQL函数分类主要分为两种
-单行函数
-多行函数

单行函数语法

语法:
函数名[(参数1，参数2,…)]

其中的参数可以是以下之一：

1. 变量
2. 列名
3. 表达式

单行函数特征

1. 单行函数特征
2. 单行函数对单行操作
3. 每行返回一个结果
4. 有可能返回值与原参数数据类型不一致
5. 单行函数可以写在SELECT、WHERE、ORDER BY子句中
6. 有些函数没有参数，有些函数包括一个或多个参数
7. 函数可以嵌套

单行函数分类

分为5大类

• 字符函数
• 数值函数
• 日期函数
• 转换函数
• 通用函数

字符函数

字符函数
- 字符函数：主要指参数类型是字符型，不同函数返回值可能是字符型或数值型。

大小写函数

LOWER(列名|表达式)

将大写或大小写混合的字符转换成小写

select lower('ABCDE')
from dual;

UPPER(列名|表达式)

将小写或大小写混合的字符转换成大写

select upper('abcde')
from dual;

INITCAP(列名|表达式)

将每个单词的第一个字母转换成大写，其余的字母都转换成小写

select initcap('oycp oyyyyy')
from dual;

字符处理函数

CONCAT(column1|expression1,column2|expression2)

连接两个值 ,等同于||

select concat(ename,sal)
from emp;

SUBSTR (column|expression,n1[,n2])

返回第一个参数中，从第n1位开始，长度为n2的子串。
如果n2省略,取第n1位开始的所有字符。
如果n1是负值，表示从第一个参数的后面第abs(n1)位开始向右取长度为n2的子串。

select substr('abcdefg',1,3)
from dual;

LENGTH(column | expression)

取字符长度

select length('abcdefg')
from dual;

INSTR(s1,s2,[,n1],[n2])

返回s1中，子串s2从n1开始，第n2次出现的位置。n1,n2默认值为1

select instr('abcdefg','c')---3
from dual;

LPAD(s1,n1,s2)

返回s1被s2从左面填充到n1长度后的字符串。

select lpad('abc',11,'*')
from dual;

RPAD(s1,n1,s2)

返回s1被s2从右面填充到n1长度后的字符串。

select rpad('abc',11,'*')
from dual;

TRIM

去除字符串头部或尾部（头尾）的字符格式:TRIM(leading | trailing | both trim_character From trim_source)

select trim('c' from 'abcdefccccccccc')
from dual;

![在这里插入图片描述](https://www.icode9.com/i/ll/?i=20201230190643897.png)

REPLACE(s1,s2,s3)

把s1中的s2用s3替换。

select replace('abcdef','c','p')----abpdef
from dual;

数值函数

ROUND

将列或表达式所表示的数值四舍五入到小数点后的第n位。

格式: ROUND(column| expression, n)
ROUND(45.926, 2) -> 45.93

select round(12.4846,2)
from dual;

TRUNC

将列或表达式所表示的数值截取到小数点后的第n位。

格式：TRUNC(column| expression, n)
TRUNC(45.926, 2) -> 45.92

select trunc(12.4846,2)
from dual;

MOD

取m除以n后得到的余数

格式：MOD(m,n)
MOD(1600, 300) ->100

select mod(1600,300)
from dual;

常用日期函数

SYSDATE

返回系统日期

select sysdate
from dual;

MONTHS_BETWEEN

返回两个日期类型数据之间间隔的自然月数

SELECT  ename, sal, MONTHS_BETWEEN(SYSDATE,hiredate) months
FROM 	emp
ORDER BY months;

ADD_MONTHS

返回指定日期加上相应的月数后的日期

SELECT  ename, sal, hiredate, ADD_MONTHS(hiredate,3) new_date
FROM	  emp
WHERE  hiredate>'01-1月-82';

NEXT_DAY

返回某一日期的下一个指定日期

SELECT  NEXT_DAY('02-2月-06','星期一') NEXT_DAY
FROM 	DUAL;

LAST_DAY

返回指定日期当月最后一天的日期

SELECT LAST_DAY('02-2月-06') "LAST DAY"
FROM	DUAL;

ROUND(date[,‘fmt’])

将date按照fmt指定的格式进行四舍五入，fmt为可选项，如果没有指定fmt，则默认为DD，将date四舍五入为最近的天。
格式码：世纪CC,年YY，月MM,日DD，小时HH24，分MI，秒SS

SELECT  empno, hiredate, 
        ROUND(hiredate, 'MONTH')
FROM	  emp
WHERE  SUBSTR(hiredate,-2,2)='81'

TRUNC(date[,‘fmt’])

将date按照fmt指定的格式进行截断，fmt为可选项，如果没有指定fmt，则默认为‘DD’，将date截取为最近的天。

SELECT	empno, hiredate, TRUNC(hiredate, 'MONTH')
FROM		emp
WHERE	SUBSTR(hiredate,-2,2)='81';

EXTRACT

返回日期类型数据中的年份、月份或者日

SELECT  ename, hiredate, 
	      EXTRACT (MONTH  FROM  HIREDATE) MONTH
FROM   emp
WHERE  deptno= 10;

转换函数

分为两种

• 隐式数据类型转换
• 显式数据类型转换

隐式数据类型转换规则

不同的数据类型之间关联，如果不使用显式转换函数，则它会根据以下规则对数据进行隐式转换：

• 对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为字段的数据类型
• 对于SELECT语句，oracle会把字段的数据类型隐式转换为变量的数据类型
  当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型
• 当比较字符型和日期型的数据时，oracle会把字符型转换为日期型
• 用连接操作符(||)时，oracle会把非字符类型的数据转换为字符类型
• 如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算，则oracle会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等

显式数据类型转换

通常是在字符类型、日期类型、数值类型之间进行显式转换。主要有3个显式转换函数：

• TO_CHAR
• TO_NUMBER
• TO_DATE

TO_CHAR 用于日期型

日期格式模型：

• 必须用单引号引起来并且是大小写敏感的
• 可以包含任何有效的日期元素
• 使用逗号将日期型数据与日期型格式模型分隔开

SELECT ename,TO_CHAR(hiredate,'MM/YY') month_hired 
FROM emp;

日期格式模型的元素

TO_CHAR 用于数值型

通过在TO_CHAR中使用以下形式可以把数值型数据转化成变长的字符串：

 SELECT	TO_CHAR(sal,'$99,999') SALARY
   FROM	emp
   WHERE	ename = 'SCOTT';

TO_NUMBER 函数

使用TO_NUMBER将一个字符串转换成数值型数据

TO_NUMBER(char[, 'fmt'])

注意：要转换的char类型数据必须是由数字组成的字符串，格式码中相应的格式必须要和char中的格式匹配。

TO_DATE 函数

select to_date('2020/12/30','YYYY/MM/DD')
from dual

通用函数

与空值（NULL）相关的一些函数，完成对空值（NULL）的一些操作。主要包括以下函数：

NVL

语法:NVL (expr1,expr2)
如果expr1不是null,返回expr1,否则返回expr2

NVL2

语法:NVL2(expr1,expr2,expr3)
如果expr1不是null,返回expr2,否则返回expr3

NULLIF

语法: NULLIF(expr1,expr2)
比较两个表达式,如果相等，返回null,否则，返回第一个表达式

COALESCE

COALESCE (表达式1, 表达式2, … 表达式n)函数是对NVL函数的扩展。COALESCE函数的功能是返回第一个不为空的参数，参数个数不受限制。

SELECT  ename, COALESCE(comm, 0)  comm , deptno
FROM   emp;

条件处理函数：

CASE表达式

SELECT ename, deptno,
       (CASE deptno     
	          WHEN 10  THEN '销售部'
                        WHEN 20  THEN '技术部'
                        WHEN 30  THEN '管理部'
                          ELSE '无'    END)   
                            deptname   
FROM emp;

DECODE

SELECT ename, deptno,
  decode(deptno,
    10,'销售部',
    20,'技术部',
    30 , '管理部',
    '无')   deptname
FROM emp;

收工，谢谢