Oracle零基础学习函数笔记

—函数

聚合函数 SUM(SAL)
NVL(COMM,0)
TO_DATE(‘20220221’,‘YYYYMMDD’)
TO_CHAR
函数名称(参数)
–DUAL 是一个虚拟表，只有一行数据

–数字函数
参数，参数是写在括号里的
–绝对值 ABS
SELECT ABS(-1) FROM DUAL;
SELECT ABS(-1) FROM EMP;

–MOD(X,Y) x除以y的余数
SELECT MOD(7,2) FROM DUAL;

—幂 运算
SELECT POWER(2,3) FROM DUAL; --2的3次方

—TRUNC 截断 对数字的截断
TRUNC(X,Y)
在 X 的第 Y 位截断
截断的意思就是所从截断的那个位置开始，后面的所有数据都变成 0

SELECT TRUNC(123.456,2) FROM DUAL; —在小数点之后第二位截断
SELECT TRUNC(123.456,-2) FROM DUAL; —在小数点之前第二位截断
SELECT TRUNC(123.456,-3) FROM DUAL; —在小数点之前第二位截断
SELECT TRUNC(123456,3) FROM DUAL; —在小数点之前第二位截断
SELECT TRUNC(123.456) FROM DUAL; —默认 保留整数

—ROUND(X,Y)
–在 X 的第 Y 位四舍五入
SELECT ROUND(123.456,2)
FROM DUAL;

SELECT ROUND(123.453,2)
FROM DUAL;

–CEIL 向上取整
SELECT CEIL(4.23) FROM DUAL;
SELECT CEIL(4) FROM DUAL;

–FLOOR 向下取整
SELECT FLOOR(4.23) FROM DUAL;
SELECT FLOOR(4) FROM DUAL;

—将 数字 634.47 处理为 630
SELECT TRUNC(634.47,-1) FROM DUAL
—将 数字 634.47 处理为 634.4
SELECT TRUNC(634.47,1) FROM DUAL

–TO_NUMBER 将字符串类型的数据转换为 NUMBER 类型
SELECT ‘123’
,TO_NUMBER(‘123’)
,123
FROM DUAL;

—字符串 ‘12’ 比 字符串 ‘9’ 要小
‘12’
‘9’

—数字 12 比 数字 9 要大
12
9

SELECT SYSDATE FROM DUAL;
如果看到的日期类型的数据没有时分秒，那么就是 0 点 0 分 0秒

–日期函数
对 DATE 类型的数据做处理
–TRUNC 也可以对日期做截断
—返回这一年的 1月一号
SELECT TRUNC(SYSDATE,‘YYYY’) FROM DUAL;

----返回时间所在月的 一号
SELECT TRUNC(SYSDATE,‘MM’) FROM DUAL;

—返回到当天的日期,不带时分秒
SELECT SYSDATE,TRUNC(SYSDATE,‘DD’) FROM DUAL;

—当前所在星期的第一天
SELECT SYSDATE,TRUNC(SYSDATE,‘D’) FROM DUAL;

—返回当前时间所在季度的第一天
SELECT SYSDATE,TRUNC(SYSDATE,‘Q’) FROM DUAL;

SELECT TRUNC(TO_DATE(20211201,‘YYYYMMDD’),‘Q’) FROM DUAL;

SELECT TO_CHAR(SYSDATE,‘YYYYMMDD’)
,TO_CHAR(SYSDATE,‘YYYY-MM-DD’)
,TO_CHAR(SYSDATE,‘YYYY’)
,TO_CHAR(SYSDATE,‘MM’)
,TO_NUMBER(TO_CHAR(SYSDATE,‘MM’))
,TO_CHAR(SYSDATE,‘DD’)
,TO_CHAR(SYSDATE,‘Q’)–第几个季度
FROM DUAL;

-----WEEK
SELECT TO_CHAR(SYSDATE,‘W’) 本月第几周
,TO_CHAR(SYSDATE,‘D’) 本周第几天
,TO_CHAR(SYSDATE,‘WW’) 本年第几周
FROM DUAL;

–TO_DATE
SELECT TO_DATE(‘20220221’,‘YYYYMMDD’)
,TO_DATE(‘20220221101535’,‘YYYYMMDDHH24MISS’)
FROM DUAL;

–ADD_MONTHS
–月份加减
SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL; --加一个月
SELECT ADD_MONTHS(SYSDATE,-1) FROM DUAL;—减去一个月

SELECT ADD_MONTHS(TO_DATE(‘20220228’,‘YYYYMMDD’),1) FROM DUAL;
SELECT ADD_MONTHS(TO_DATE(‘20220329’,‘YYYYMMDD’),-1) FROM DUAL;

SELECT TRUNC(ADD_MONTHS(SYSDATE,1),‘DD’)
FROM DUAL;

–系统时间加多少天
SELECT SYSDATE + 1 FROM DUAL;
SELECT SYSDATE - 1 FROM DUAL;

—计算两个日期的天数差
SELECT TRUNC(SYSDATE,‘DD’) - TO_DATE(20220301,‘YYYYMMDD’) FROM DUAL;
SELECT TO_DATE(20220201,‘YYYYMMDD’) - TRUNC(SYSDATE,‘DD’) FROM DUAL;

–LAST_DAY(d)，返回指定日期当月的最后一天
SELECT LAST_DAY(SYSDATE)FROM DUAL;

SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1)) FROM DUAL;

—ROUND
—如果为“YEAR”则舍入到某年的1月1日，即前半年舍去，后半年作为下一年
SELECT ROUND(SYSDATE,‘YEAR’) FROM DUAL;
SELECT ROUND(TO_DATE(20220801,‘YYYYMMDD’),‘YEAR’) FROM DUAL;

—如果为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一月。
SELECT ROUND(SYSDATE,‘MONTH’) FROM DUAL;

SELECT ROUND(TO_DATE(20220315000000,‘YYYYMMDDHH24MISS’),‘MONTH’) FROM DUAL;
SELECT ROUND(TO_DATE(20220215000000,‘YYYYMMDDHH24MISS’),‘MONTH’) FROM DUAL;

—默认为“DDD”，即某一天，最靠近的天，前半天舍去，后半天作为第二天
SELECT ROUND(SYSDATE,‘DDD’) FROM DUAL;

----如果为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日
SELECT ROUND(SYSDATE,‘DAY’) FROM DUAL;

SELECT ROUND(TO_DATE(20220113,‘YYYYMMDD’),‘DAY’) FROM DUAL;

–MONTHS_BETWEEN
—两个日期相差的月数
SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE(20220201,‘YYYYMMDD’)) FROM DUAL;
SELECT MONTHS_BETWEEN(TRUNC(SYSDATE,‘DD’),TO_DATE(20220201,‘YYYYMMDD’)) FROM DUAL;

—根据 日期 20211020 得到 2021年11月1号
SELECT LAST_DAY(TO_DATE(20211020,‘YYYYMMDD’))
,LAST_DAY(TO_DATE(20211020,‘YYYYMMDD’)) +1
FROM DUAL ;

SELECT TRUNC(TO_DATE(20211020,‘YYYYMMDD’),‘MM’)
,ADD_MONTHS(TRUNC(TO_DATE(20211020,‘YYYYMMDD’),‘MM’),1)
FROM DUAL;

—根据 日期 20201020 得到 2021年1月1号
SELECT TRUNC(TO_DATE(20201020,‘YYYYMMDD’),‘MM’)
,ADD_MONTHS(TRUNC(TO_DATE(20201020,‘YYYYMMDD’),‘MM’),3)
FROM DUAL;

—计算 emp 表中每个年份的每个季度有有多少个员工入职
SELECT TO_CHAR(T.HIREDATE,‘YYYY’)
,TO_CHAR(T.HIREDATE,‘Q’)
,COUNT(T.EMPNO)
FROM EMP T
GROUP BY TO_CHAR(T.HIREDATE,‘YYYY’),TO_CHAR(T.HIREDATE,‘Q’)

select trunc(sysdate, ‘D’)+1 from dual;

---

–字符串

----拼接
–CONCAT(x,y) 连接字符串x和y。
–CONCAT(参数1,参数2)
SELECT CONCAT(T.EMPNO,T.ENAME) AS EMPNO_ENAME
,CONCAT(T.EMPNO,’ ‘)
,CONCAT(CONCAT(T.EMPNO,’ '),T.ENAME)
FROM EMP T;
7369

SELECT CONCAT('HELLO ',‘ORACLE’) FROM DUAL;

—管道拼接符 ||
SELECT T.EMPNO||’ ‘||T.ENAME||’ '||T.SAL AS EMPNO_ENAME
,T.*
FROM EMP T;

WM_CONCAT,跟 GROUP BY 一起使用，将那一组的多行的某个字段的数据拼在在一行里（纵向拼接），
—DISTINCT 可以对拼接出来的数据做去重
SELECT * FROM EMP;

SELECT T.DEPTNO
–,T.JOB
,WM_CONCAT(DISTINCT T.JOB)
,AVG(SAL)
FROM EMP T
GROUP BY T.DEPTNO;

统计 EMP 表中部门的个数 --3 个部门
SELECT DISTINCT T.DEPTNO
FROM EMP T

SELECT COUNT(DISTINCT T.DEPTNO)
FROM EMP T;

—字符串长度
LENGTH(参数)
SELECT LENGTH(‘ASDF’) FROM DUAL;
SELECT LENGTH(‘长度’) FROM DUAL;

SELECT *
FROM EMP T
WHERE LENGTH(T.ENAME) = 4

—字节长度
SELECT LENGTHB(‘ASDF’) FROM DUAL;
SELECT LENGTHB(‘长度’) FROM DUAL;—两个中文占 4个字节
SELECT LENGTHB(‘ASD中国F’) FROM DUAL;–4个字母占了4个字节，2个中文占了4个字节，加起来就是8

拼接 练习;
–1 将员工的编号、姓名、工作拼接起来
用 CONCAT 和 || 这两种方法实现
SELECT CONCAT(T.EMPNO,T.ENAME)
,CONCAT(CONCAT(T.EMPNO,T.ENAME),T.JOB)
FROM EMP T;

SELECT T.EMPNO ||’ ‘||T.ENAME||’ '||T.JOB
FROM EMP T;

–2 用 WM_CONCAT 将每一个岗位的所有员工的名字拼接到一起
SELECT T.JOB
,WM_CONCAT(T.ENAME)
FROM EMP T
GROUP BY T.JOB;

–替换
SELECT REPLACE(字符串,旧的值,新的值) FROM DUAL;
SELECT REPLACE(‘12AA34’,‘A’,‘B’) FROM DUAL;

–左填充 LPAD
LPAD(要填充的那个字符串,填充之后的总长度,以什么字符填充)
SELECT LPAD(‘BB’,5,‘A’)
FROM DUAL;

–右填充 RPAD
SELECT RPAD(‘BB’,5,‘A’)
FROM DUAL;

–ASCII(x) 返回字符x的ASCII码。
SELECT ASCII(‘A’) FROM DUAL;
SELECT ASCII(‘a’) FROM DUAL;
SELECT ASCII(‘0’) FROM DUAL;

—返回的是第一个字母对应的 ASCII 编码
SELECT ASCII(‘AB’) FROM DUAL;
SELECT ASCII(‘CBA’) FROM DUAL;
SELECT ASCII(8) FROM DUAL;

–通过 ASCII 编码返回对应的字符
CHR(ASCII编码)
SELECT CHR(65) FROM DUAL;

----查找位置
INSTR(参数1, 参数2 ,参数3,参数4) 返回的是字符在字符串出现的位置
参数1 在哪个字符串中查找
参数2 查找的是哪一个字符
参数3 从哪一个位置开始找
参数4 第几次出现

字符串 ‘DGSJISA’，从第一个位置开始找，找到字母S 第一次出现的位置
INSTR(在哪个字符串中找,找到的那个字符,从什么位置开始找,第几次出现的位置)
–返回的位置是从左边第一个位置开始数的
–第三个参数是正数的时候，就是从左往右找
–第三个参数是负数的时候，就是从右往左找
SELECT INSTR(‘12178121’,‘1’,1,2) FROM DUAL;
SELECT INSTR(‘DGSJSIA’,‘S’,1,2) FROM DUAL; --5
SELECT INSTR(‘DGSJSIA’,‘S’,4,2) FROM DUAL;–0 找不到就是0
SELECT INSTR(‘Helloworldor’，‘or’,1,1) FROM DUAL;
–从右边往左边找
SELECT INSTR(‘DGSJSIAS’,‘S’,-1,2) FROM DUAL;–5 —从右边往左边找

–大小写转换
LOWER(x) x转换为小写。
UPPER(x) x转换为大写。

SELECT LOWER(‘ASD’) FROM DUAL;
SELECT LOWER(ENAME) FROM EMP; --将员工的名字转化为小写
SELECT UPPER(‘eiua’) FROM DUAL;
SELECT UPPER(‘EIUA’) FROM DUAL;

‘AAASDF’
—截去
–LTRIM(x,trim_str) 把x的左边截去trim_str字符串，缺省截去空格。
–RTRIM(x,trim_str) 把x的右边截去trim_str字符串，缺省截去空格。
–TRIM(trim_str FROM x) 把x的两边截去trim_str字符串，缺省截去空格。

LTRIM(参数1,参数2) 把参数1 的左边截去 参数2 字符串，参数2 不写的时候默认截去空格。
–参数2 是一个字符集，从左边或者右边截掉的字符中包括字符集中的任意一个字符
—从左边开始，截取掉包含参数2的任意一个字符，只要是在参数2的范围内并且是连续的
SELECT LTRIM(‘ABCDEFG’,‘ABC’) FROM DUAL; —截去 ABCDEFG 的左边的 ABC
—截去 ABCDEFG 的左边的 ABC
SELECT LTRIM(‘ABCDEFG’,‘BAC’) FROM DUAL; —截去 ABCDEFG 的左边的 ABC
SELECT LTRIM(‘ACCABBCDEFG’,‘BAC’) FROM DUAL; —截去 ABCDEFG 的左边的 ABC
SELECT LTRIM(‘ACFACABBCDEFG’,‘BAC’) FROM DUAL; —截去 ACACABBCDEFG 的左边的 ABC

SELECT ’ ABCDEFG’
,LTRIM(’ ABCDEFG’) —不写参数2
FROM DUAL; --默认截去空格

RTRIM(x,trim_str)把x的右边截去trim_str字符串，缺省截去空格。
SELECT RTRIM(‘ABCDEFG’,‘GFED’),RTRIM(‘ABCDEFG’,‘DEFG’) FROM DUAL;
SELECT RTRIM(‘ABCDEFG’,‘GFDE’),RTRIM(‘ABCDEFG’,‘DFGE’) FROM DUAL;
SELECT RTRIM(‘ABCDEFG’,‘GFTE’),RTRIM(‘ABCDEFG’,‘LFGE’) FROM DUAL;

—TRIM 从两边截去
TRIM(trim_str FROM x) 把x的两边截去trim_str字符，缺省截去空格。
—截取集只能填一个字符
SELECT TRIM(‘S’ FROM ‘SASFFDGAS’) FROM DUAL;
SELECT TRIM(‘S’ FROM T.ENAME),T.ENAME FROM EMP T;

—SUBSTR
—截取出来

SUBSTR(参数1，参数2，参数3)
注意：截取都是从左往右截取
参数1 从哪个字符串中截
参数2 从这个字符串的哪个位置截取,如果参数2为负数，就是从倒数第几个位置开始截取
参数3 截取的长度，如果参数3 为空，就默认截到末尾

SELECT SUBSTR(‘DDSIADF’,1,4)—从第一个位置开始截取，截取四个长度
FROM DUAL;

SELECT SUBSTR(‘DASIADF’,3,4)–从第三个位置开始截取，截取4个长度
FROM DUAL;

SELECT SUBSTR(‘DASIADF’,-3,2)–从倒数第三个位置开始截取，截取2个长度
FROM DUAL;

练习1

用 RTRIM 或者 SUBSTR 这两个函数实现 从 ‘15min’ 这个字符串 得到 15
SELECT RTRIM(‘15min’,‘min’) FROM DUAL;
SELECT SUBSTR(‘15min’,1,2) FROM DUAL;
SELECT SUBSTR(‘15min’
,1
,LENGTH(‘15min’)-3)
FROM DUAL;

–在 EMP中找出名字的首字母是 A 的员工
SELECT T.ENAME
FROM EMP T
WHERE SUBSTR(T.ENAME,1,1)= ‘A’;

–将字符串’###abcdefg***h’ 截取成’h’;
SELECT SUBSTR(’###abcdefg***h’,-1,1) FROM DUAL;

–将字符串 ‘SDNDJDAAE’ 截取成’AAE’;
SELECT SUBSTR(‘SDNDJDAAE’,-3,3) FROM DUAL;