-- 字符串函数
字符串函数主要处理字符串数据(字符串数据可以是列上的内容,也可以是指定的常量)。
常用的字符串函数有以下几种:
函数名称 返回值(结果)类型 说明
LOWER(列\字符串常量); 字符串 将传入的字符转为小写
UPPER(列\字符串常量); 字符串 将传入的字符转为大写
INITCAP(列\字符串常量); 字符串 将传入的字符转为除了首字符大写其他字母小写
LENGTH(列\字符串常量); 数值 返回传入的字符串的字符长度
SUBSTR(列\字符串常量,开始索引,截取长度); 字符串 将字符串从开始索引的位置截取指定长度
SUBSTR(列\字符串常量,开始索引); 字符串 将字符串从开始索引的位置截取到末尾
REPLACE(列\字符串常量,旧字符串,新字符串); 字符串 将指定字符串中的旧字符串替换成新字符串
--字节个数
SELECT LENGTHB('ab哈') FROM dual;
注:字符串下标从1开始
LOWER(列|常量)
针对列
-- 将 emp 表的 ENAME 列的数据转为小写。
SELECT LOWER(ename) FROM emp;
针对常量
-- 将 HELLOWORLD 转为小写。
SELECT LOWER('HELLOWORLD') FROM emp;
注意:
所有函数想要进行验证使用,都必须编写SQL 语法。不能单独使用函数。
而直接查询 emp 表的话满足条件的结果都将输出来。
有时候只是想测试一下常量,而结果也会输出多条记录。
提示:
Oracle 专门提供了一张虚拟表 dual,测试常量的时候,只需要把查询的表 换成 dual即可。
-- UPPER(列|常量)
SELECT UPPER('abc') FROM dual;
-- INITCAP(列|常量)
SELECT INITCAP('abc') FROM dual;
-- LENGTH(列|常量)
SELECT LENGTH('abc') FROM dual;
-- SUBSTR(列|常量,开始索引,截取长度)
SELECT SUBSTR('哈哈一二三',2,4) FROM dual;
-- SUBSTR(列|常量,开始索引)
SELECT SUBSTR('哈哈一二三',2) FROM dual;
-- REPLACE(列|常量,旧字符串,新字符串)
SELECT REPLACE('哈哈哈1111','1111','2222') FROM dual;
-- 数值函数
字符串函数主要处理字符串数据(字符串数据可以是列上的内容,也可以是指定的常量)。
常用的字符串函数有以下几种:
函数名称 返回值(结果)类型 说明
ROUND(列\数值常量 [,小数点位数]); 数值 实现数据的四舍五入,可以设置保留小数位
TRUNC(列\数值常量 [,小数点位数]); 数值 实现数据的截取,即:不进位
MOD(列\数值常量(除数) , 列\ 数值常量(被除数)); 数值 取模(计算余数)
-- ROUND(列|数值常量 [,小数点位数]);
-- 对 768.6178 进行四舍五入,无指定小数位
SELECT ROUND(768.6178) FROM dual;
-- 对 768.6178 进行四舍五入,指定保留2位小数
SELECT ROUND(768.6178,2) FROM dual;
-- 对 768.6178 进行四舍五入,指定保留到 十位。(使用比较少,了解即可...)
SELECT ROUND(768.6178,-1) FROM dual;
-- TRUNC(列|数值常量 [,小数点位数]);
将数字截尾取整:TRUNC() 函数 和 ROUND() 函数的使用方式一样,效果差不多,只不过 TRUNC() 函数不进行四舍五入。
-- 对 768.6718 进行 数字截尾取整,无指定小数位
SELECT TRUNC(768.6718) FROM dual;
-- 对 768.67 进行 数字截尾取整,指定保留2位小数
SELECT TRUNC(768.6718,2) FROM dual;
-- 对 768.67 进行 数字截尾取整,指定保留到 十位。(使用比较少,了解即可...)
SELECT TRUNC(768.6718,-1) FROM dual;
-- MOD(列|数值常量(除数) , 列|数值常量(被除数));
-- 10 对 3取余。
SELECT MOD(10,3) FROM dual;
-- 测试 emp 表 EMPNO列数据 对 DEPTNO列数据 取余。
SELECT MOD(empno,deptno) FROM emp;
还有一些函数使用频率较少,如:
CEIL(列|数值常量) 向前取整:
如:CEIL(1.2) 结果为2;
FLOOR(列|数值常量) 向后取整:
如:CEIL(1.2) 结果为1;
SQRT()(列|数值常量) 平方根:
如:SQRT(9) 结果为3;
-- 日期函数
如果想要处理日期函数,就必须知道系统当前时间是多少。Oracle为专门提供了两个 虚拟(伪)列 SYSDATE和SYSTIMESTAMP 用来查询当前日期时间。两个虚拟伪劣都包含日期和时间内容,但 SYSDATE列 只显示日期,而SYSTIMESTAMP 显示日期和时间。用的较多的是SYSDATE。
伪列:在查询所有数据表的时候都可以访问该列。
如:查询 emp 表的 雇员编号,雇员姓名,当前时间。那么就可以把 sysdate 当做一列进行查询
SELECT empno,ename,SYSDATE FROM emp;
清楚了获取当前时间的方式后,还需要以下三个日期的计算公式:
日期 + 数字(天数) = 日期 ——表示若干天后的日期
日期 - 数字(天数) = 日期 ——表示若干天前的日期
SELECT SYSDATE,SYSDATE+7,SYSDATE-7 FROM dual;
日期 - 日期 = 数字(天数) ——表示两个日期的天数差
-- 查询 emp 表员工姓名、雇佣日期、当前日期、雇佣的天数
SELECT ename,hiredate,SYSDATE,SYSDATE-hiredate FROM emp;
注意:通过 日期 - 日期 公式得到是天数,我们很难从天数算出经过多少年,多少月份等,因为我们每一年的天数是不一样的,以及月份的天数也是不一样的。所以,除了要知道三个简单的日期公式之外,还需要学会以下四个常用日期函数。
日期函数:
函数名称 返回值(结果)类型 说明
ADD_MONTHS(列\日期常量 ,月数); 日期 在指定日期增加若干月份后的日期
MONTHS_BETWEEN(列\日期常量,列\日期常量); 数值 返回两个日期所经历过的月份数
LAST_DAY(列\日期常量); 日期 返回指定日期所在月份的最后一天的日期
NEXT_DAY(列\日期常量, '星期X' ); 日期 下一个星期X的日期
-- ADD_MONTHS(列|日期常量 ,月数);
-- 查看4个月后的日期。
SELECT SYSDATE AS 当前日期, ADD_MONTHS(SYSDATE,4) AS "4个月后的日期" FROM dual;
-- 查看 姓名为 SCOTT 的雇员 雇佣日期前一个月的日期。
SELECT hiredate 雇佣日期,ADD_MONTHS(hiredate,-1) 前一个月的日期 FROM emp WHERE ename='SCOTT';
MONTHS_BETWEEN(列|日期常量,列|日期常量);
-- 查看 所有雇员到今天为止 所雇佣的月份数。
SELECT hiredate 雇日期, SYSDATE 当前日期,MONTHS_BETWEEN(SYSDATE,hiredate) 所雇佣的月份数 FROM emp;
-- LAST_DAY(列|日期常量);
-- 查看当前时间日期所在月份的最后一天的日期。
SELECT LAST_DAY(SYSDATE) FROM dual;
-- 查看 姓名为 BLAKE 的雇员 雇佣日期所在月份的最后一天的日期。
SELECT ename,hiredate,LAST_DAY(hiredate) FROM emp WHERE ename='BLAKE';
-- 查看 所有雇佣日期在雇佣日期所在月末10天内的雇员信息。(即在月末-10 至 月末)
-- SELECT * FROM emp WHERE LAST_DAY(hiredate)-hiredate BETWEEN 0 AND 10;
SELECT * FROM emp WHERE hiredate <= LAST_DAY(hiredate) AND hiredate>= LAST_DAY(hiredate)-9;
-- NEXT_DAY(列|日期常量, 星期X );
-- 查看下一个星期一的日期。(当前日期为2017年4月10日 星期一)
SELECT NEXT_DAY('10-4月-17','星期一') FROM dual;
-- 查看下一个星期三的日期。(当前日期为2017年4月10日 星期一)
SELECT NEXT_DAY(SYSDATE,'星期三') FROM dual;
-- 转换函数
我们已经学过了 字符串函数、数值函数、日期函数 ,那么这三类数据之间也是可以相互转换的。Oracle提供了以下三个常用转换函数。
函数名称 返回值(结果)类型 说明
TO_NUMBER(列、字符串); 数值 将字符串转换为数值
TO_CHAR(列、数值、日期,转换格式); 字符串 将数值类型或者日期类型转换为字符串类型
TO_DATE(列、字符串,转换格式); 日期 将指定字符串通过转换格式转换为日期
-- TO_NUMBER(列|字符串);
-- 将 '13' 字符串数值内容和 '22' 字符串数值内容 相加。
SELECT TO_NUMBER('13')+TO_NUMBER('22') FROM dual;
注意:Oracle 带有自动转换的功能,上面的Sql 语法可以简写成
SELECT '13' +'22' FROM dual;
所以说: TO_NUMBER()函数 意义不大。了解即可
-- TO_CHAR(列|数值|日期,转换格式);(转换函数的重点)
常用的日期时间格式化符号:年(yyyy)、月(mm)、日(dd) 、时(hh/hh24)、分(mi)、秒(ss)、星期几(dy)
-- 将当前日期转为字符串一。
SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') FROM dual;
-- 将当前日期转为字符串二。(实现日期时间的拆分)
SELECT TO_CHAR(SYSDATE,'yyyy') 年,TO_CHAR(SYSDATE,'mm') 月,TO_CHAR(SYSDATE,'dd') 日,TO_CHAR(SYSDATE,'hh24') 时,TO_CHAR(SYSDATE,'mi') 分,TO_CHAR(SYSDATE,'ss') 秒,TO_CHAR(SYSDATE,'dy') 星期 FROM dual;
-- 常用的数值格式化符号:任意数字(9)、本地货币符号(L)。
SELECT TO_CHAR(12345678,'L999,999,999') FROM dual;
-- TO_DATE(列|字符串,转换格式);(与java类似)
-- 将 '2020-02-02' 字符串转为日期格式。
SELECT TO_DATE('2020-02-02','yyyy-mm-dd') FROM dual;
-- 现在有一个时间'2018-08-08'的字符串。计算出该时间与当前时间相隔的天数。
SELECT TO_DATE('2018-08-08','yyyy-mm-dd')-SYSDATE FROM dual;
-- 通用函数
通用函数一般指Oracle特色函数,在Oracle的函数发展中,有部分函数内部的实现也是通用函数演变过来的。那么Oracle主要有两个通用函数:
函数名称 返回值(结果)类型 说明
NVL(列(数值),默认数值); 数值 针对指定数值列做非空处理,如果数值为NUll,则返回默认值
DECODE(列、数值、字符串,比较内容1,结果内容1,比较内容2,结果内容2……[,默认值]); 数据类型 类似于 java的 Switch,判断等值情况,将传入的数值或字符串,与每一个比较内容比较,比较成功则返回对应的结果内容。如果都比较失败,则返回NULL,如果有默认值,则返回默认值。
-- NVL(列(数值),默认数值);
-- 查看 emp 的雇员编号、姓名、基本工资、佣金、月总收入(薪资+提层)
SELECT empno,ename,sal,comm,sal+NVL(comm,0) 总收入 FROM emp;
-- DECODE(列|数值|字符串,比较内容1,结果内容1,比较内容2,结果内容2……[,默认值]);
-- 查询 雇佣编号、姓名、职位(中文显示)。
SELECT empno,ename,DECODE(job,'MANAGER','经理','CLERK','办事员','SALESMAN','销售','其它')
FROM emp;
本文中使用的数据表可在 《SQL 基本语句》文章中查看