Oracle笔记04——Oracle单行函数

SQL函数分为单行函数多行函数


单行函数

 

(1)字符函数

大小写转换函数:

①小写转换函数

LOWER({列名 | 变量 | 表达式})

②大写转换函数

UPPER({列名 | 变量 | 表达式})

③首字母大写函数

INITCAP({列名 | 变量 | 表达式})

④dual是一行N列的伪表

 

(2)字符处理函数

①连接两个值,等同于 | |

CONCAT(列名1 | 表达式1, 列名2 | 表达式2)

②截取字符串

SUBSTR(列名 | 表达式, n1[, n2])
作用:返回第一个参数中,从第n1位开始,长度为n2的子串
注意
Ⅰ.如果n2被省略,则取第n1到最后的所有字符
Ⅱ.如果n1是负数,表示从第一个参数的后面第(-n1)位开始向右取长度为n2的子串

③取字符长度

LENGTH(列名 | 表达式)

④获取子串在字符串中出现的位置的下标

INSTR(参数1, 参数2 [,参数3,参数4])
其中:
参数1:待查找的目标字符串
参数2:要查找的子串
参数3:开始查找的位置,默认从1开始
参数4:要查找第N次出现的子串

⑤左补齐

LPAD(参数1, 参数2, 参数3)
其中:
参数1:带补齐的目标字符串
参数2:想要得到的字符串长度
参数3:不足长度的向左补齐的字符串

⑥右补齐

RPAD(参数1, 参数2, 参数3)
其中:
参数1:带补齐的目标字符串
参数2:想要得到的字符串长度
参数3:不足长度的向右补齐的字符串

⑦去除字符串头部或尾部或头尾的字符

TRIM( [LEADING | TRAILING | BOTH 字符 FROM] 字符串)
其中:
LEADING为头部,TRAILING为尾部,BOTH为头部和尾部

⑧替换字符串

REPLACE(参数1, 参数2, 参数3)
其中:
参数1:源字符串
参数2:源字符串中要被替换的字符串
参数3:要替换参数2的字符串


(3)数值函数

①四舍五入

ROUND(列名 | 表达式 [, n])
注意:
Ⅰ.当有两个参数的时候,表示结果保留小数点后第n位;
Ⅱ.当只有一个参数的时候,表示结果保留整数
Ⅲ.当n为负数时,表示整数第(-n)位开始四舍五入,大于等于5向前进1;小于5,取0;

②截取小数点第n位

TRUNC(列名 | 表达式 [, n])
注意:
Ⅰ.当有两个参数的时候,表示结果截取到小数点第n位;
Ⅱ.当只有一个参数的时候,表示结果保留整数
Ⅲ.当n为负数时,表示整数第(-n)位到最后一位都取0
③取m除于n得到的余数
MOD(m,n)


(4)日期函数

①返回系统当前日期

SYSDATE

②RR日期格式:用来判定按照DD-MON-RR格式给定的日期实际代表的日期是多少

Oracle笔记04——Oracle单行函数

大大 不变
大小 世纪+1
小小 不变
小大 世纪-1

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

MONTHS_BETWEEN(日期类型数据1, 日期类型数据2)

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

ADD_MONTHS(日期类型数据, 月份)

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

NEXT_DAY(日期类型数据, 星期几)

⑥返回某一日期所在月份的最后一天

LAST_DAY(日期类型数据)

⑦四舍五入日期

ROUND(日期类型数据[, 'YEAR' | 'MONTH' | 'DAY'])

⑧截断日期

TRUNC(日期类型数据[, 'YEAR' | 'MONTH' | 'DAY'])

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

EXTRACT([YEAR | MONTH | DAY] FROM 日期类型数据)

(5)转换函数

①TO_CHAR(数据, ['fmt'])

Ⅰ.当数据为日期型数据时,功能为:将日期类型数据转换为[规定日期格式的]字符串
日期模型的元素:

YYYY 完整的年份
YEAR 年份的英文
MM 两位数字的月份
MONTH 月份的全名
DD 日期
DAY 星期几
DY 用3个英文字符来表示星期几
HH 小时(其中12HH表示12小时制,24HH表示24小时制)
MI 分钟
SS
AM 上午
PM 下午


Ⅱ.当数据为数值型数据时,功能为:将数值类型数据转换为[规定日期格式的]字符串
数值模型的元素:

9 一位数字
0 显示前导0
$ 显示美元符号
L 显示本地货币符号
. 显示小数点
, 显示千位符


注意:进行数字类型到字符型转换时,格式中的宽度一定要超过实际列宽度,否则会显示为###

②将字符串转换为数值

TO_NUMBER(字符串)

③将字符串转换为日期

TO_DATE(字符串, ['fmt'])
注意:当字符串为日期的默认格式的日期类型数据即'日-月-年'时,才可以省略'fmt'


(6)通用函数

①判断参数一是否为空,如果为null,取参数二的值;反之,取参数一本身的值

NVL(参数一, 参数二)

②判断参数一是否为空,不为空取参数二的值,为空取参数三的值

NVL2(参数一, 参数二, 参数三)

③判断参数一与参数二是否相等,相等返回null,不等返回参数一

NULLIF(参数一, 参数二)

④判断参数一是否为空,不为空取参数一的值;为空则判断参数二是否为空,参数二不为空取参数二的值,为空判断参数三是否为空...

COALESCE(参数一, 参数二, 参数三, ..., 参数N)

⑤判断表达式,如果满足表达式1,返回值1;如果满足表达式2,返回值2;...;如果都不满足返回else值

CASE 表达式
WHEN 表达式1 THEN 值1
WHEN 表达式1 THEN 值2
WHEN 表达式1 THEN 值3
...
WHEN 表达式N THEN 值N
ELSE else值
END

⑥如果表达式的值等于表达式1,返回值1;如果表达式的值等于表达式2,返回值2;如果表达式的表达式等于值3,返回值3;...;都不相等返回缺省值

DECODE(表达式, 表达式1, 值1, 表达式2, 值2, 表达式3, 值3, ... , 缺省值)

 

(7)函数嵌套

单行函数可以嵌套N层


(8)抽取日期的三种

①EXTRACT()
②SUBSTR()
③TO_CHAR()

 

(9)练习

--1.字符函数
--(1)大小写转换函数
SELECT ename,LOWER(ename),job FROM emp;--转小写
SELECT 'SQL source',LOWER('SQL source') FROM dual;--dual伪表
SELECT 'SQL source',UPPER('SQL source') FROM dual;--转大写
SELECT 'sql source',INITCAP('sql source') FROM dual;--首字母大写
--(2)字符处理函数
SELECT ename,sal FROM emp;
SELECT CONCAT(ename,sal) FROM emp;--拼接两个列的值
SELECT CONCAT(CONCAT(ename,'的薪水为:'),sal) FROM emp;--函数的嵌套

SELECT 'lqh@qq.com',SUBSTR('lqh@qq.com',5) FROM dual;--从下标为5开始截取到最后
SELECT 'lqh@qq.com',SUBSTR('lqh@qq.com',5,2) FROM dual;--从下标为5开始截取,截取长度为2的子串
SELECT 'lqh@qq.com',SUBSTR('lqh@qq.com',-6,2) FROM dual;--从后面第6位开始截取,截取长度为2的子串

SELECT 'lqh@qq.com', LENGTH('lqh@qq.com') FROM dual;--获取字符串的长度
SELECT ename,LENGTH(ename) FROM emp;--获取所有员工姓名的长度

SELECT 'lqh@qq.com', INSTR('lqh@qq.com','lqh') FROM dual;--查找'lqh'第一次出现的位置
SELECT 'lqh@qq.com', INSTR('lqh@qq.com','lqh',2) FROM dual;--从2开始查找'lqh'第一次出现的位置
SELECT 'lqh@qq.com', INSTR('lqh@qq.com','q',2,2) FROM dual;--从2开始查找'q'第二次出现的位置

SELECT ename, '¥' || LPAD(sal, 10 , '-') AS 薪水 FROM emp;--薪水不足10位的向左补'-'
SELECT ename, RPAD(ename, 10,'_') AS 员工姓名 FROM emp;--员工姓名不足10位的向右补'_'

SELECT ' lqh@qq.com ', TRIM(' lqh@qq.com ') FROM dual;--默认除去首尾空格
SELECT '_lqh@qq.com_', TRIM(LEADING '_' FROM '_lqh@qq.com_') AS 去除头部字符 FROM dual;--去除头部字符'_'
SELECT '_lqh@qq.com_', TRIM(TRAILING '_' FROM '_lqh@qq.com_') AS 去除尾部字符 FROM dual;--去除尾部字符'_'
SELECT '_lqh@qq.com_', TRIM(BOTH '_' FROM '_lqh@qq.com_') AS 去除头尾字符 FROM dual;--去除头尾字符'_'

SELECT 'lqh@qq.com',REPLACE('lqh@qq.com', 'qq', 'wechat') AS 替换后的字符串  FROM dual;--将字符串中的'qq'替换成'wechat'
SELECT 'lqh@qq.com',REPLACE('lqh@qq.com', 'q', '*') AS 替换后的字符串  FROM dual;--将字符串中所有'q'替换成'*'

--(3)数值函数
SELECT ROUND(520.131396), ROUND(520.131396, 4) FROM dual;--取整, 保留小数点后四位小数
SELECT ROUND(519.131396, -1), ROUND(520.131396, -2) FROM dual;--整数第一位四舍五入,整数第二位四舍五入

SELECT TRUNC(520.131396), TRUNC(520.131396, 4) FROM dual;--截取整数部分,截取到小数点第四位
SELECT TRUNC(519.131396, -1), TRUNC(520.131396, -2) FROM dual;--将整数第一位以及之后的数变为0,将整数第二位以及之后的数变为0

SELECT MOD(520,3) FROM dual;--取520/3的余数

--(4)日期函数
SELECT SYSDATE FROM dual;--获取系统当前时间

--查询员工的入职日期,转正日期(6个月试用期),入职天数,入职星期数等
SELECT hiredate 入职日期,
       hiredate + (6 * 30) 转正日期,
       (SYSDATE - hiredate) 入职天数,
       (SYSDATE - hiredate) / 7 入职星期数
  FROM emp;

-------------------
当前年份        指定的日期         RR格式
1995            27-12月-95         1995
1995            27-12月-17         2017
2001            27-12月-17         2017
2001            27-12月-95         1995             
-------------------

SELECT MONTHS_BETWEEN('1-12月-12', '1-5月-12') FROM dual;--返回两个日期相差的月份

SELECT ADD_MONTHS('1-12月-12', 2) FROM dual;--加上两个月

SELECT NEXT_DAY(SYSDATE, '星期一') FROM dual;--返回系统时间的下一个星期一

SELECT SYSDATE, ROUND(SYSDATE) FROM dual;

SELECT SYSDATE,
       ROUND(SYSDATE, 'YEAR'),      --根据月份进位,1-6不变,7-12进1
       ROUND(SYSDATE, 'MONTH'),     --根据天数进位,1-15不变,16-30进1
       ROUND(SYSDATE, 'DAY')        --根据星期进位,星期日-三不变,星期四-六进1
  FROM dual;

SELECT SYSDATE,
       TRUNC(SYSDATE, 'YEAR'),      --截取年份,月和日都为1
       TRUNC(SYSDATE, 'MONTH'),     --截取月份,日为1
       TRUNC(SYSDATE, 'DAY')        --截取日
  FROM dual;

SELECT SYSDATE,
       EXTRACT(YEAR FROM SYSDATE),      --抽取年
       EXTRACT(MONTH FROM SYSDATE),     --抽取月
       EXTRACT(DAY FROM SYSDATE)        --抽取日
  FROM dual;

--(5)转换函数
SELECT SYSDATE, TO_CHAR(SYSDATE) FROM dual;--将当前系统时间转换为字符串
SELECT SYSDATE,
       TO_CHAR(SYSDATE, 'YYYY') YYYY,--四位的年分
       TO_CHAR(SYSDATE, 'YEAR') "YEAR",--英文年份
       TO_CHAR(SYSDATE, 'MM') MM,--两位的月份
       TO_CHAR(SYSDATE, 'MONTH') "MONTH",--全名的月份
       TO_CHAR(SYSDATE, 'DD') DY,--日期
       TO_CHAR(SYSDATE, 'DAY') "DAY",--星期
       TO_CHAR(SYSDATE, 'HH') HH,--小时
       TO_CHAR(SYSDATE, 'MI') MI,--分钟
       TO_CHAR(SYSDATE, 'SS') "SS",--秒
       TO_CHAR(SYSDATE, 'YYYY-MM-DD PM HH:MI:SS')--转换为规定的日期格式的字符串
  FROM dual;

SELECT TO_CHAR(5201314, 'L0999,999,999.999') FROM dual;--位数不够,补零
SELECT TO_CHAR(5201314, 'L999.999') FROM dual;--位数超过了,显示##################

SELECT TO_NUMBER('520.1313')+0.0001 FROM dual;--将字符串转为数值

SELECT TO_DATE('1-12月-01') FROM dual;--将字符串转为日期
SELECT TO_DATE('2005-12-01') FROM dual;--报错
SELECT TO_DATE('2005-12-01', 'YYYY-MM-DD') FROM dual;--正确

--(6)通用函数
SELECT comm, NVL(comm, 0), deptno, NVL(deptno, 0) FROM emp;--如果comm和deptno为空

SELECT comm, NVL2(comm ,'不为空', '空') FROM emp;--如果comm为空输出“空”,不为空输出“不为空”

SELECT NULLIF(520,1314) FROM dual;--如果两个数相等,返回null,否则返回第一个数

SELECT comm, COALESCE(TO_CHAR(comm), null, null, 'comm为空') FROM emp;--如果comm为空,返回函数参数中不为空的值;不为空,则返回其自身的值

SELECT deptno,
       (CASE deptno
         WHEN 10 THEN
          '销售部'
         WHEN 20 THEN
          '员工部'
         WHEN 30 THEN
          '经理部'
         ELSE
          '打杂部'
       END)
  FROM emp; --如果部门为10,返回“销售部”;如果部门为20,返回“员工部”;如果部门为30,返回“经理部”;否则返回“打杂部”

--效果跟上面一样
SELECT deptno,
       (CASE
         WHEN deptno = 10 THEN
          '销售部'
         WHEN deptno = 20 THEN
          '员工部'
         WHEN deptno = 30 THEN
          '经理部'
         ELSE
          '打杂部'
       END)
  FROM emp;

--效果跟上面一样
SELECT deptno, DECODE(deptno, 10, '销售部', 20, '员工部', 30, '经理部', '打杂部') FROM emp;

--(7)函数嵌套
SELECT mgr, NVL(mgr, '为空') FROM emp;--错误,NVL中仅允许存放同类型数据
SELECT mgr, NVL(TO_CHAR(mgr), '为空') FROM emp;--正确,NVL函数里面嵌套TO_CHAR函数

--抽取日期
SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE), SUBSTR(SYSDATE,-2), TO_CHAR(SYSDATE, 'YYYY') FROM dual;--抽取年份的三种方法

 

上一篇:MySQL函数


下一篇:sea.js 入门