目录
6.LAST_DAY(时间)，返回指定日期 当月 的最后一天
9.CHR(ASCII编码)返回的是第一个字母对应的 ASCII 编码
一、数学函数
1.ABS(X)取绝对值
SELECT ABS(-1) a
FROM dual;2.ROUND(X,Y) 四舍五入
--保留y位小数
SELECT trunc(31.1456, -2) a1, ROUND(3.124) a2
FROM dual;3.TRUNC(X,Y)截断
--在 X 的第 Y 位截断
--截断的意思就是：从截断的那个位置开始，后面的所有数据都变成 0
--如果第二个参数是正数，那么就是从小数点之后截断，如果是负数，那么就是从小数点之前截断
SELECT TRUNC(3567.1263, 2) a1, TRUNC(3567.1243, -2) a2, TRUNC(3567.1243) a3
FROM dual;
4.FLOOR向下取整(<)
SELECT FLOOR(56.34) a1 --56
, FLOOR(-56.34) a2 -- -57
FROM dual;5.CEIL向上取整(>)
SELECT CEIL(56.34) a1, -- 57
CEIL(-56.34) a2 -- -56
FROM dual;6.MOD取余数与MOD(X,Y) x除以y的余数
SELECT MOD(4, 2) a1, -- 0
MOD(5, 3) a2 -- 2
FROM dual;7.TRUNC取商(相当于取整)
SELECT TRUNC(5 / 3) a1 FROM dual;8.POWER取幂
SELECT POWER(2, 1) a1, POWER(2, -1) a2, POWER(2, 1 / 2) a3
FROM dual;
二、日期函数
SELECT TO_DATE('20220610133050', 'YYYYMMDDHH24MISS') a,
TO_DATE('20220610123050', 'YYYYMMDDHHMISS') b
FROM DUAL;
1.日期 + 整数 得到的是 日期
示例：获取明天的日期
SELECT SYSDATE+1 FROM dual;示例：当前时间往前推1小时
SELECT SYSDATE-1/24 FROM dual;日期相减计算：
SELECT TO_DATE('20220610', 'YYYYMMDD') - TO_DATE('20220710', 'YYYYMMDD')
FROM DUAL; -- 302.日期 - 日期 得到的是 数字 单位是 天
示例：获取2024年天数
SELECT to_date('20241231', 'yyyymmdd') -
to_date('20240101', 'yyyymmdd') + 1 a
FROM dual;3.TRUNC 也可以对日期做截断
示例：截取当年第一天
SELECT TRUNC(SYSDATE, 'yyyy') FROM dual; -- 2025-01-01示例：截取当月第一天
下面两种方式都可以
SELECT TRUNC(SYSDATE, 'MM') FROM dual;
SELECT TO_DATE('202407', 'YYYYMM') FROM DUAL;示例：获取当前的年月日
下面两种方式都可以
SELECT TRUNC(SYSDATE, 'DD') FROM dual;
SELECT TRUNC(SYSDATE) FROM dual;示例：截取当月第一天
SELECT TRUNC(SYSDATE, 'MM') FROM dual;示例：截断到小时
SELECT TRUNC(SYSDATE, 'hh') FROM dual;
示例：截断到分
SELECT TRUNC(SYSDATE, 'mi') FROM dual;
示例：当前所在星期的第一天
SELECT TRUNC(SYSDATE, 'd') FROM dual;
示例：返回当前时间所在季度的第一天 -- QUARTER
SELECT TRUNC(SYSDATE, 'Q') FROM dual;
4.使用TO_CHAR判断日期
SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') a1
, TO_CHAR(SYSDATE, 'YYYY-MM-DD') a2
, TO_CHAR(SYSDATE, 'YYYY') 第几年
, TO_CHAR(SYSDATE, 'MM') 本年第几月
, TO_CHAR(SYSDATE, 'DD') 本月第几天
, TO_CHAR(SYSDATE, 'Q') 第几个季度
, TO_CHAR(SYSDATE, 'W') 本月第几周 --MONTH OF WEEK
, TO_CHAR(SYSDATE, 'D') 本周第几天 -- DAY OF WEEK
, TO_CHAR(SYSDATE, 'WW') 本年第几周 --YEAR OF WEEK
FROM DUAL;
5.月份加减ADD_MONTHS(时间,数字)
示例：返回上个月的当天
select trunc(add_months(sysdate, -1), 'DD') from dual;
示例：往前推10年
select add_months(trunc(sysdate), -120) from dual;示例：获取今年5月最后一天
select trunc(add_months(sysdate, 1), 'MM') - 1 from dual;
SELECT LAST_DAY(TO_DATE('2025-05', 'YYYY-MM')) AS last_day
FROM DUAL;
-- 提取当前年份 EXTRACT(YEAR FROM SYSDATE)
SELECT LAST_DAY(TO_DATE(EXTRACT(YEAR FROM SYSDATE) || '-05', 'YYYY-MM')) AS last_day_of_may
FROM DUAL;6.LAST_DAY(时间)，返回指定日期 当月 的最后一天
示例：获取当月最后一天
select last_Day(trunc(sysdate)) from dual;示例：获取下个月第一天
select trunc(add_months(sysdate, 1), 'MM') from dual;
select last_day(trunc(sysdate, 'MM')) + 1 from dual;
SELECT last_day(TRUNC(SYSDATE)) + 1 a FROM dual;
SELECT TRUNC(SYSDATE, 'mm') a1, add_months(TRUNC(SYSDATE, 'mm'), 1) a2
FROM dual;7.MONTHS_BETWEEN(大的日期,小的日期)
得到的结果是个数字, 可以是负数
示例：计算今年过去几个月，取整数
select months_between(trunc(sysdate), trunc(sysdate, 'yyyy')) a,
ceil(months_between(trunc(sysdate), trunc(sysdate, 'yyyy'))) b, -- 向上取整
trunc(months_between(trunc(sysdate), trunc(sysdate, 'yyyy'))) c -- 截取整数
from dual;1 查询每个员工的工号，入职日期，入职日期是入职年份的第几季度，入职日期所在季度的第一天是哪那一天
select empno,
hiredate,
to_char(hiredate, 'Q') 入职季度,
trunc(hiredate, 'Q') 入职季度第一天
from emp;
2.查出 2022年3月2号所在季度的第一天
select trunc(to_date('20220302', 'YYYYMMDD'), 'Q') a from dual;
3.查出 2022年3月2号所在月的最后一天
select last_Day(to_date('20220302', 'YYYYMMDD')) a from dual;
select add_months(trunc(to_date('20220302', 'YYYYMMDD'),'MM'), 1) - 1 a from dual;4.查出2022年3月2号是所在年份的第几个季度
select to_char(to_date('20220302', 'YYYYMMDD'), 'YYYY') 所在年份,
to_char(to_date('20220302', 'YYYYMMDD'), 'Q') 所在季度
from dual;
5.由2022年3月2号得到 2023年的1月1号
-- 第二年所在季度的第一天
select trunc(add_months(to_date('20220302', 'YYYYMMDD'), 12), 'Q') a
from dual;6.输出 EMP表中 入职时间 的 上个季度 的 第一天的 月末
select hiredate,
to_char(hiredate, 'Q') 入职季度,
TRUNC(hiredate, 'Q') 入职季度第一天,
ADD_MONTHS(TRUNC(hiredate, 'Q'), -3) 上个季度第一天,
last_day(ADD_MONTHS(TRUNC(hiredate, 'Q'), -3)) 上个季度的第一天的月末
from emp;
7.计算当前日期跟 2020-01-01 相差多少个月
select ceil(months_between(trunc(sysdate),
to_date('2020-01-01', 'YYYY-MM-DD'))) 相差月份
from dual; -- 65三、字符串函数
1.两两拼接CONCAT(参数1,参数2)
select ename,
empno,
concat(concat(ename, job), sal) a,
ename || empno || sal
from emp;2.管道拼接符 ||
SELECT ename || '-' || empno || '-' || job a, 1 || 2 || 3 b FROM emp;
3.wm_concat(x)+GROUP BY
wm_concat(x)跟 GROUP BY 一起使用，将那一组的多行的某个字段的数据拼在一行里
SELECT DEPTNO, wm_concat(job) a FROM emp GROUP BY DEPTNO;
SELECT deptno, wm_concat(DISTINCT job) a FROM emp GROUP BY deptno;
每个部门有多少不同的工作种类
select count(job), count(distinct job) from emp group by deptno;4.字符串长度LENGTH(参数)
SELECT
LENGTH('ABC') AS char_count, -- 返回 3（3个字符）
LENGTH('你好') AS char_count, -- 返回 2（2个字符）
LENGTH('A😊B') AS char_count -- 返回 3（1个字母 + 1个表情符号 + 1个字母）
FROM DUAL;5.字节长度LENGTHB(参数)
SELECT
LENGTHB('ABC') AS byte_count, -- 返回 3（每个字符占1字节）
LENGTHB('你好') AS byte_count, -- 返回 6（每个中文占3字节）
LENGTHB('A😊B') AS byte_count -- 返回 8（表情符号在UTF-8中占4字节）
FROM DUAL;6.转换为大写UPPER()
select upper('hello') from dual;7.转换为小写LOWER()
select lower('HELLO') from dual;8.ASCII(x)返回字符x的ASCII码
select ASCII('a') a, -- 97
ASCII('Aa') b, -- 65
ASCII(',') c, -- 44
ASCII('好') d, -- 47811
ASCII('''') a, -- 单引号的ASCII码值是39
ASCII(6) -- 54
from dual;
-- 查询单引号
SELECT '''' a FROM dual;
9.CHR(ASCII编码)返回的是第一个字母对应的 ASCII 编码
通过 ASCII 编码返回对应的字符
SELECT CHR(97) a1, CHR(50403) || CHR(47811) a2 FROM dual;
10.左填充 LPAD left pad
LPAD(要填充的那个字符串,填充之后的总长度,以什么字符填充)
select lpad('1234', 7, 'a') from dual; -- aaa1234
select lpad('1234', 7, 'ab') from dual; -- aba1234
select lpad('01234', 3, 'ab') from dual; -- 01211.右填充 RPAD right pad
SELECT RPAD('1234', 7, 'a') a1, -- 1234aaa
RPAD('1234', 7, 'ab') a2, -- 1234aba
RPAD('01234', 3, 'ab') a3 -- 012
FROM dual;1.将员工的编号、姓名、工作拼接起来
select empno || ename || job from emp;2.将每一个岗位的所有员工的名字拼接到一起
select job, wm_concat(ename) from emp group by job;3.查找名字长度是 4个字符的名字
select ename from emp where length(ename) = 4;12.字符串去除空格
TRIM (字符串)--去除字符串两边空格
LTRIM(字符串) ---去除字符串左边空格 LEFT
RTRIM (字符串)---去除字符串右边空格 RIGHT
REPLACE也可以去除字符串内部的空格
SELECT TRIM(' 12 3 45 ') a,
LTRIM(' 12 3 45 ') a2,
RTRIM(' 12 3 45 ') a3,
REPLACE('asdfg dfhdhf', ' ') a4 -- asdfgdfhdhf
FROM dual;13.匹配截取LTRIM(字符串,字符集)
LTRIM(字符串,字符集) ---从字符串的左边开始与字符集相匹配，匹配的上就消掉，
---直到字符不在字符集里面则停止匹配，并截取
找到一部分也会截取
SELECT LTRIM('asdasfgZXZc', 'as') a1,-- dasfgZXZc
LTRIM('asdfgZXZc', 'asg') a2 -- dasfgZXZc
FROM dual;14.查找字符串位置INSTR
INSTR(参数1, 参数2 ,参数3,参数4) 返回的是字符在字符串出现的位置
参数1 在哪个字符串中查找
参数2 查找的是哪一个字符
参数3 从哪一个位置开始找
参数4 第几次出现
--返回的位置是从左边第一个位置开始数的
--第三个参数是正数的时候，就是从左往右找
--第三个参数是负数的时候，就是从右往左找
SELECT INSTR('aasdaf23', 'a', 1, 3) a1, -- 5
INSTR('aasdf23', '3') a2, -- 7
INSTR('aasdf23', 'a', 4, 1) a3, -- 0
INSTR('aasdf23', 'a', -1, 2) a4 -- 1
FROM dual;15.截取字符串SUBSTR
SUBSTR(参数1，参数2，参数3)
注意：截取都是从左往右截取
参数1 从哪个字符串中截
参数2 从这个字符串的哪个位置截取，如果参数2为负数，就是从倒数第几个位置开始截取
参数3 截取的长度，如果参数3 为空，就默认截到末尾
SELECT SUBSTR('1234567', 1, 1) a1, -- 1
SUBSTR('1234567', 1, 4) a2, -- 1234
SUBSTR('1234567', -1, 1) a3, -- 7
SUBSTR('12345678', -2, 4) a4, -- 78
SUBSTR('1234567', -3, 4) a5, -- 567
SUBSTR('1234567', -3) a6 -- 567
FROM dual;16.替换REPLACE(字符串,旧的值,新的值)
SELECT REPLACE('asdfgdh', 'd', 'x') a, -- asxfgxh
REPLACE('asdfgdh', 'd', 'x1') a, -- asx1fgx1h
REPLACE('asdfgdfhdhf', 'df', 'x') a, -- asxgxhdhf
REPLACE('asdfg dfhdhf', ' ') a -- asdfgdfhdhf
FROM dual;练习：
1.在 EMP中找出名字的首字母是 A 的员工的名字
select ENAME from emp where substr(ename, 1, 1) = 'A';
SELECT ename FROM EMP WHERE INSTR(ename, 'A') = 1;2.在 EMP 表中找出名字的最后一个字符是 S 的员工的名字
select ENAME from emp where substr(ename, -1, 1) = 'S';
SELECT ename FROM EMP WHERE SUBSTR(ename, LENGTH(ename), 1) = 'S';3.保证EMP表的员工姓名是6位长度，如果不够左边填充X
select lpad(ename, 6, 'X') from emp;
4.将 '2020-1-3'转换成'2020-01-03'
select to_date('2020-1-3', 'YYYY-MM-DD') A,
TO_CHAR(to_date('2020-1-3', 'YYYY-MM-DD'), 'YYYY-MM-DD') B
from dual;
select replace('2020-1-3','-','-0') from dual;