Oracle进阶（三）常用函数

目录

1、聚合函数

2、转换函数

3、字符函数

4、数值函数

5、日期函数

6、判断函数

7、分析函数

附表

1、聚合函数

常用的SQL 聚合函数有5个：最大(MAX) 最小(MIN) 平均(AVG) 总和(SUM) 求数(COUNT) 

--一、聚合函数 基本语法

--MAX|MIN|AVG|SUM|COUNT (DISTINCT|ALL)

--其它聚合函数 类似,但中位数 DISTINCT会提示错误：DISTINCT 选项在此函数中禁用。
SELECT MAX(DISTINCT SAL) FROM EMP;
SELECT MAX(SAL) FROM EMP

--二、聚合函数 常见需求：

--1、查询EMP表里所有部门的名称和最高工资

SELECT E.DEPTNO,D.DNAME,MAX(E.SAL) 
FROM EMP E 
LEFT JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO,D.DNAME;

--2、查询EMP表里所有部门的名称和最低工资

SELECT E.DEPTNO,D.DNAME,MAX(E.SAL),MIN(E.SAL) 
FROM EMP E 
LEFT JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO,D.DNAME;

--3、计算EMP表里超过1人的每个部门

SELECT E.DEPTNO,COUNT(*) 
FROM EMP E
GROUP BY DEPTNO
HAVING COUNT(*) > 1;

--4、 查询EMP表里每个部门的汇总工资

SELECT E.DEPTNO,SUM(E.SAL) AS 工资总和 
FROM EMP E
GROUP BY DEPTNO
HAVING COUNT(*) > 1;

--5、查询EMP表里每个部门的平均薪酬

SELECT E.DEPTNO,AVG(E.SAL) AS 平均工资
FROM EMP E
GROUP BY DEPTNO
HAVING COUNT(*) > 1;

--其它：标准差(STDDEV) 协方差(VARIANCE) 中位数(MEDIAN) 

2、转换函数

常用的SQL 转换函数有3个：

TO_CHAR()：将DATE或者NUMBER转换为字符串
TO_DATE()：将NUMBER、CHAR转换为DATE
TO_NUMBER()：将CHAR转换为NUMBER

--1、TO_CHAR()：将DATE或者NUMBER转换为字符串

 SELECT TRUNC(TO_DATE(SYSDATE),'Y') FROM DUAL;
 SELECT TO_CHAR(DATE'2021-04-25','YYYY') FROM DUAL;  -- 年
 SELECT TO_CHAR(DATE'2021-04-25','YYYYMM') FROM DUAL; -- 年月
 SELECT TO_CHAR(DATE'2021-04-25','WW') FROM DUAL;     -- 第17周
 SELECT TO_CHAR(DATE'2021-04-25','IW') FROM DUAL;     -- 第16周（自然周）
 SELECT TO_CHAR(DATE'2021-04-25','Q') FROM DUAL;     -- 第2季度
 SELECT TO_CHAR(DATE'2021-04-25','MM') FROM DUAL;    -- 月
 SELECT TO_CHAR(DATE'2021-04-25','DD') FROM DUAL;    -- 日
 SELECT TO_CHAR(DATE'2021-04-25','D') FROM DUAL;       --本周第几天（星期天为第一天）

--2、 TO_DATE()：将NUMBER、CHAR转换为DATE

--注：Date 强制转换,要有'-'才能转换，数字只会出现错误
--注：字符或数字中的年月日必须是日期范围内的值
--注：字符日期中有-或/转化为日期类型必须加-或/   

  SELECT DATE'2021-01-01' FROM DUAL;  -- 强制转换为日期格式
  SELECT TO_DATE('2021-01-01','YYYY/MM/DD') FROM DUAL;
  SELECT TO_DATE('2021-01-01','YYYY-MM-DD') FROM DUAL;
  SELECT TO_DATE('2021-01-01','YYYYMMDD') FROM DUAL;  -- 错误:无效的月份
  SELECT TO_DATE('2021-13-08','YYYY-MM-DD'),TO_DATE(20210732,'YYYY-MM-DD') FROM DUAL;-- 错误:无效的月份
  SELECT TO_DATE('2021-07-08','YYYYMMDD') FROM DUAL;-- 错误:无效的月份
  SELECT TO_DATE(20210708,'YYYY-MM-DD')  FROM DUAL;

--3、 TO_NUMBER()：将CHAR转换为NUMBER

  SELECT TO_NUMBER('1 2') FROM DUAL;--错误：无效数字
  SELECT TO_NUMBER(' 12') FROM DUAL;
  SELECT TO_NUMBER('12 ') FROM DUAL;
  SELECT TO_NUMBER(' 12 ') FROM DUAL;
  SELECT TO_NUMBER('12D') FROM DUAL;--错误：无效数字

  SELECT  TO_NUMBER(TO_DATE(20210425,'YYYYMMDD')) FROM DUAL;  --错误：无效数字
  SELECT  TO_NUMBER(TO_CHAR(TO_DATE(20210425,'YYYYMMDD'),'YYYYMMDD')) FROM DUAL; 

3、字符函数

--字符函数

--1 ASCII 返回参数的十进制数字表示
SELECT ASCII('A') FROM DUAL; --65

--2 CHR 返回参数的代码所指定的字符
SELECT CHR(65) FROM DUAL;  --A

--3 CONCAT 返回arg1与arg2的拼接结果
SELECT CONCAT('ABC','DEF') FROM DUAL; --ABCDEF

--4 INITCAP 将参数arg1每个单词的首字母大写
SELECT INITCAP('yi er shan ren') FROM DUAL; --Yi Er Shan Ren

--5 INSTR(arg1,子字符串,开始检索的位置,搜索第几次出现子字符串)
--从左面第1个字符开始搜索,第一次出现ABC的位置
SELECT INSTR('HABCHABC','ABC',1,1) FROM DUAL; --2

--6 LENGTH 返回参数arg1的长度
SELECT LENGTH('abcdefg') FROM DUAL; --7

--7 lower 返回参数arg1的小写形式
SELECT LOWER('AbCdEfG') FROM DUAL; --abcdefg

--8 LPAD(arg1,n,arg3) 返回在arg1左边填充若干个arg3组成长度为n新字符串
SELECT LPAD('ABC',10,'Ha') FROM DUAL; --HaHaHaHABC

--9 LTRIM(arg1,arg2)在arg1左面删除连续出现的arg2.不指定arg2则默认删除空格
SELECT LTRIM('AAAASA','A') FROM DUAL; --SA

--10 RPAD(arg1,n,arg3) 在字符串arg1右边连续重复填充arg3直到填充后的字符串总长度到达n为止
SELECT RPAD('thr',5,'ee') FROM DUAL; --three

--11 RTRIM(arg1,arg2) 从arg1中删除右侧连续出现的arg2.不指定arg2则默认删除空格.
SELECT RTRIM('abcdabcabc','abc') FROM DUAL; --abcd

--12 REGEXP_INSTR(源字符串,正则表达式,[开始搜索的位置],[描述执行多少次替换],[描述匹配字符串的位置的整数],[允许修改正则表达式引擎的默认行为的字符串])
SELECT REGEXP_INSTR('ABC123ABC','[0-9]A') FROM DUAL; --6

--13 REGEXP_REPLACE(源字符串,正则表达式,当正则表达式匹配时所使用的替换字符串,[开始搜索的位置的整数],[描述执行多少次替换的数据],[允许修改正则表达式引擎的默认行为的字符串])
SELECT REGEXP_REPLACE('abc123abc123','[0-9]','#') FROM DUAL; --abc###abc###

--14 EGEXP_SUBSTR(源字符串,正则表达式,[开始搜索的位置],[描述执行多少次替换的数据],[允许修改正则表达式引擎的默认行为的字符串])
SELECT REGEXP_substr('abc133abc456','([0-9]{3})abc([0-9]{3})') FROM DUAL; --133abc456

--15 REPLACE(arg1,search,replace) 该函数用replace参数替换出现的所有search参数
SELECT REPLACE('abcdabce','abc','x') FROM DUAL; --xdxe

--16 SUBSTR(arg1,pos,len) 
SELECT SUBSTR('1234567890',3,5) FROM DUAL; --34567

--17 TRANSLATE(arg1,match,replace)
SELECT TRANSLATE('PI*R*2','*R','#@') FROM DUAL;  --PI#@#2

--18 TRIM([LEADING][