当前位置：主页 > 查看内容

Oracle进阶（三）常用函数

发布时间：2021-10-13 00:00| 有位朋友查看

简介：目录 1、聚合函数 2、转换函数 3、字符函数 4、数值函数 5、日期函数 6、判断函数 7、分析函数附表 1、聚合函数常用的SQL 聚合函数有5个最大(MAX) 最小(MIN) 平均(AVG) 总和(SUM) 求数(COUNT)? --一、聚合函数基本语法--MAX|MIN|AVG|SUM|COUNT (DISTINCT|……

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][TRAILING][BOTH] char FROM source)
--截断串首连续出现的字符1
SELECT TRIM(LEADING '1' FROM '1122332211') FROM DUAL;--22332211
SELECT TRIM(BOTH ' ' from ' ! ') as BOTH,TRIM(' ! ') as source FROM DUAL; --!  --!

4、数值函数

--数值函数

--1 ABS 绝对值 
SELECT  ABS(-2) FROM DUAL;  -- 2

--2 ROUND 四舍五入
SELECT ROUND(2.281,1) FROM DUAL; -- 2.3

--3 MOD 求余
SELECT MOD(10,3) FROM DUAL; --- 1

--4 POWER 平方 求导
SELECT POWER(2,3) AS 平方,POWER(2,-1) AS 求导 FROM DUAL;--8 --.5

--5 TRUNC 直接截取，不四舍五入
SELECT TRUNC(4.1231,2) FROM DUAL; --4.12

5、日期函数

--日期函数

--日期格式：
/*
YYYY：四位表示的年份 
YYY，YY，Y：年份的最后三位、两位或一位，缺省为当前世纪 
IYYY:ISO标准的四位年份
MM：01~12的月份编号 
MON：缩写字符集表示 
MONTH：全拼字符集表示的月份，右边用空格填补
Q：季度
W：当月第几周
WW：当年第几周 
IW:ISO标准的年中的第几周
D：当周第几天 
DD：当月第几天 
DDD：当年第几天 
DY：缩写字符集表示
DAY：全拼字符集表示的天 如（星期六）
HH，HH12：一天中的第几个小时，12进制表示法 
HH24：一天中的第几个小时，取值为00~23 
MI：一小时中的分钟 
SS：一分钟中的秒 
SSSS：从午夜开始过去的秒数 
*/
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, 'DAY') 天,
       TO_CHAR(SYSDATE, 'Q') 第几季度,
       TO_CHAR(SYSDATE, 'W') 当月第几周,
       TO_CHAR(SYSDATE, 'WW') 当年第几周,
       TO_CHAR(SYSDATE, 'D') 当周第几天,
       TO_CHAR(SYSDATE, 'DDD') 当年第几天    
  FROM DUAL;
	
--注：日期可以加减数字,表示加减多少天;日期减去日期表示相隔多少天;日期不能相加
--注：MONTHS_BETWEEN的第一个日期要大于第二个日期，否则返回负数。

  SELECT DATE'2021-04-25'+2,DATE'2021-04-25'-2,DATE'2021-02-25'-DATE'2021-04-25' FROM DUAL;--2021-4-27 --2021-4-23  --59
			
--1 ADD_MONTHS(日期,月数)（n可以为负值） 增减月份
  SELECT ADD_MONTHS(SYSDATE, 1),ADD_MONTHS(DATE'2021-04-25',-2) FROM DUAL;--2021-5-25 16:17:38 --2021-2-25
    
--2 LAST_DAY(日期),返回指定日期当月的最后一天
  SELECT LAST_DAY(SYSDATE),LAST_DAY(DATE'2021-04-25') FROM DUAL;--2021-4-30 16:19:46 --2021-4-30

--3 MONTHS_BETWEEN (date1, date2),用于计算date1和date2之间有几个月
  SELECT MONTHS_BETWEEN(TO_DATE('2021-4-25', 'yyyy-mm-dd'), TO_DATE('2021-1-1', 'yyyy-mm-dd')) MON_DIFF FROM DUAL;--3.77419354838709677419354838709677419355
  
--4 FLOOR：计算两个日期间的天数
  SELECT FLOOR(TO_DATE('2021-05-03 20:00:00','yyyy-mm-dd hh24:mi:ss')-TO_DATE('2021-05-01 12:00:00','yyyy-mm-dd hh24:mi:ss')) A FROM DUAL;--2

--5 NEXT_DAY(d,string)：（准确来说一个礼拜是从星期日开始到星期六结束的）
  SELECT NEXT_DAY(SYSDATE,'星期五') NEXT_DAY FROM DUAL; --2021-4-30 16:34:32

--6 EXTRACT(fmt FROM d)：提取日期中的特定部分
SELECT SYSDATE ,
       EXTRACT(YEAR FROM SYSDATE) "YEAR",
       EXTRACT(MONTH FROM SYSDATE) "MONTH",
       EXTRACT(DAY FROM SYSDATE) "DAY",
       EXTRACT(HOUR FROM SYSTIMESTAMP) "HOUR",
       EXTRACT(MINUTE FROM SYSTIMESTAMP) "MINUTE",
       EXTRACT(SECOND FROM SYSTIMESTAMP) "SECOND"
  FROM DUAL;

--7 TO_TIMESTAMP(char[fmt[,'nls_param’]])：应注意char、fmt、nls_param之间的对应关系。
SELECT TO_TIMESTAMP('25-4月-21 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') FROM DUAL;--25-4月 -21 07.46.41.000000000 上午
SELECT CAST(SYSDATE AS TIMESTAMP) DATE_TO_TIMESTAMP from dual;--25-4月 -21 04.36.04.000000 下午

6、判断函数

6.1 NVL(expr1,expr2)：若expr1参数为空则显示expr2参数的值，反之则显示expr1参数本来的值

--1 NVL(expr1,expr2)：若expr1参数为空则显示expr2参数的值，反之则显示expr1参数本来的值
 SELECT NVL(NULL,0) FROM DUAL;     -- 0
 SELECT NVL(NULL,100) FROM DUAL;   -- 100
 SELECT NVL('', 99) FROM DUAL;   -- 99
 SELECT NVL(10, 100) FROM DUAL;   --10
 SELECT NVL('AB',25) FROM DUAL;     -- AB

6.2?NVL2(expr1,expr2, expr3)：若expr1参数为空则显示expr2的值，反之则显示expr3参数的值

--2 NVL2(expr1,expr2, expr3)：若expr1参数为空则显示expr2的值，反之则显示expr3参数的值
 SELECT NVL2(NULL, 0, 1) FROM DUAL;      -- 1
 SELECT NVL2('', 99, 2) FROM DUAL;       -- 2
 SELECT NVL2(10, 3, 100) FROM DUAL;      -- 3
 SELECT NVL2('AD', 'AB', 25) FROM DUAL;  -- AB

6.3?DECODE(value,if1,then1,if2,then2,if3,then3,...,else):若value与第一个值相等，则与第一个值的匹配值，以此类推，最后都没有的，就返回默认值

--3 DECODE(value,if1,then1,if2,then2,if3,then3,...,else):若value与第一个值相等，则与第一个值的匹配值，以此类推，最后都没有的，就返回默认值。
 SELECT DECODE(12, -- value
           1, 2, --if1,then1
          12, 10 --if2,then2
					) FROM DUAL;  -- 10				
 SELECT DECODE(12, 1, 2, 13, 10) FROM DUAL;-- 空			
 SELECT DECODE(12, 1, 2, 13, 10, 
           7 --默认值
					 ) FROM DUAL; --7				 
 SELECT DECODE(12, 1, 2, 12, 10, 12, 88,7) FROM DUAL; --10

6.4?CASE WHEN 多条件判断

 --4 CASE WHEN 多条件判断

7、分析函数

附表

--附表1：SCOTT表中的EMP表：员工表

SELECT * FROM EMP
--DROP TABLE EMP

-- CREATE TABLE
CREATE TABLE EMP
(
  EMPNO NUMBER(4),--员工编号，EMP表主键
	ENAME VARCHAR2(10), --员工姓名
	JOB VARCHAR2(9), --员工工作
	MGR NUMBER(4),  --员工的领导编号，引用EMPNO
	HIREDATE DATE , --入职日期 
	SAL NUMBER(7,2),  --员工工资 
	COMM NUMBER(7,2),  --员工奖金
	DEPTNO NUMBER(2)  --员工部门编号，是表DEPT的外键。
);
-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE EMP
  IS '员工表';
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN EMP.EMPNO
  IS '员工编号，EMP表主键';
COMMENT ON COLUMN EMP.ENAME
  IS '员工姓名';
COMMENT ON COLUMN EMP.JOB
  IS '员工工作';
COMMENT ON COLUMN EMP.MGR
  IS '员工的领导编号，引用EMPNO ';
COMMENT ON COLUMN EMP.HIREDATE
  IS '入职日期 ';
COMMENT ON COLUMN EMP.SAL
  IS '员工工资';
COMMENT ON COLUMN EMP.COMM
  IS '员工奖金';
COMMENT ON COLUMN EMP.DEPTNO
  IS '员工部门编号，是表DEPT的外键。';
	
--附表2：SCOTT表中的DEPT表：部门表

SELECT * FROM DEPT
--DROP TABLE DEPT

-- CREATE TABLE
CREATE TABLE DEPT
(
  DEPTNO NUMBER(2), --部门编号，主键
	DNAME VARCHAR2(14), --部门名称
	LOC VARCHAR2(13) --部门位置
);
-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE DEPT
  IS '部门表';
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN DEPT.DEPTNO
  IS '部门编号，主键';
COMMENT ON COLUMN DEPT.DNAME
  IS '部门名称';
COMMENT ON COLUMN DEPT.LOC
  IS '部门位置';
	
--附表3：SCOTT表中的SALGRADE表：工资等级表

SELECT * FROM SALGRADE

-- CREATE TABLE
CREATE TABLE SALGRADE
(
  GRADE NUMBER, --等级
	LOSAL NUMBER, --此等级下最低工资
	HISAL NUMBER --此等级下最高工资
);
-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE SALGRADE
  IS '工资等级表';
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN SALGRADE.GRADE
  IS '等级';
COMMENT ON COLUMN SALGRADE.LOSAL
  IS '此等级下最低工资';
COMMENT ON COLUMN SALGRADE.HISAL
  IS '此等级下最高工资';

；原文链接：https://blog.csdn.net/qq_31652795/article/details/116126431
本站部分内容转载于网络，版权归原作者所有，转载之目的在于传播更多优秀技术内容，如有侵权请联系QQ/微信：153890879删除，谢谢！

上一篇：补鞋匠都能看明了的sql高阶语句（数据函数库（聚合函数、字符串 下一篇：没有了

随机推荐

课程总结第六周

收获尼姆博奕看了好多博客开始还是比较容易理解深层面的较难看懂尤其是尼姆博...
四张图带你搞懂 Kubernetes Pod 如何获取

在学习 Kubernetes 网络模型的过程中，了解各种网络组件的作用以及如何交互非常...
为什么 Windows2003 的 IIS6.0 不能上传

Windows2003的IIS6.0对asp的上传文件大小为200k限制，aspx的上传程序没有影响。...
PHP用swoole+websocket和redis实现web一

Redis 实现每个连接websocket的服务都唯一绑定一个用户。通过用户账号 = websoc...
vue使用echarts画组织结构图

昨天，写了一篇关于圆环进度条的博客（请移步： Vue圆环进度条），已经烦不胜烦...
在laravel中使用with实现动态添加where条

关键点：闭包模型： public function getCollect() { return $this-belongsTo('...
jsp中实现带滚动条的table表格实例代码

如下所示： div style="width:700px; height:225px; overflow:auto;" table bord...
Chrome最新4.0版本支持GreaseMonkey脚本

GreaseMokey (国人称之为油猴脚本)之前只是Firefox的一个插件，可以用来添加一些...
曲速未来发布：新基建下，区块链将急速

? 区块链安全咨询公司曲速未来?表示作为一种底层与后端技术区块链自诞生起就与...
windows关闭445、3389端口

文章目录一、检验端口是否开启二、关闭445端口 2.1 445端口tcp 2.2 445端口udp...

Oracle进阶（三）常用函数

1、聚合函数

2、转换函数

3、字符函数

4、数值函数

5、日期函数

6、判断函数

7、分析函数

附表

推荐图文

UNIX的名字是我起的——对话 UNIX 开发者：Brian W.

javascript实现随机抽奖功能

DNS自述：我是如何为域名找到家的

Yii框架的redis命令使用方法简单示例

js去掉首位空格的正则表达式

laravel实现查询最后执行的一条sql语句的方法

随机推荐

课程总结第六周

四张图带你搞懂 Kubernetes Pod 如何获取

为什么 Windows2003 的 IIS6.0 不能上传

PHP用swoole+websocket和redis实现web一

vue使用echarts画组织结构图

在laravel中使用with实现动态添加where条

jsp中实现带滚动条的table表格实例代码

Chrome最新4.0版本支持GreaseMonkey脚本

曲速未来发布：新基建下，区块链将急速

windows关闭445、3389端口

关于我们