每天学点Oracle——集合运算、伪列、模糊查询、字符函数、数字函数、日期函数、转换函数、分析函数_oracle 集合函数

目录

一、集合运算（即为并集）

1.1、UNION ALL --常用

1.2、UNION  --去重，就必须排序，所以性能差，不常用

1.3、练习

1.3.1、合并emp和dept表中的部门

1.3.2、行列转化练习1

二、伪列

三、模糊查询

四、字符函数

五、数字函数

六、日期函数

七、日期函数与TRUNC()搭配使用

八、数据类型转换函数

九、MERGE INTO

十、分析函数

 十一、空值排序方式

---

---

一、集合运算（即为并集）

1.1、UNION ALL --常用

1.2、UNION  --去重，就必须排序，所以性能差，不常用

注：对应字段的数据类型相同就可以合并。

1.3、练习

1.3.1、合并emp和dept表中的部门

SELECT DEPTNO FROM EMP
UNION
SELECT DEPTNO FROM DEPT

注：此结果为已去重。

SELECT DEPTNO FROM EMP
UNION ALL
SELECT DEPTNO FROM DEPT

注：此结果为未去重。

1.3.2、行列转化练习1

------------有一张表S，记录了某公司每个月的销售额，如下，要求转换为竖的表达。

  Y     Q1    Q2   Q3    Q4 
2015   100   110  130   100
2016   200   150  100   300
-------------------------------------
Y      Q      AMT
2015   1      100
2015   2      110
2015   3      130
2015   4      100
2016   1      200
2016   2      150
2016   3      100
2016   4      300
----

SELECT Y, 1 Q,Q1 AMT FROM S_1
UNION ALL
SELECT Y, 2 Q,Q2 AMT FROM S_1
UNION ALL
SELECT Y, 3 Q,Q3 AMT FROM S_1
UNION ALL
SELECT Y, 4 Q,Q4 AMT FROM S_1
ORDER BY Y,Q

注释：select  Y （查找Y列），1  Q（把常数1命名为Q），Q1 ATM   （把Q1的值命名为ATM）

二、伪列

ROWNUM--分页查询
ROWID--删除重复数据

SELECT ROWNUM,ROWID,EMP.EMPNO,
                    EMP.ENAME,
                    EMP.JOB,
                    EMP.MGR,
                    EMP.HIREDATE,
                    EMP.SAL,
                    EMP.COMM,
                    EMP.DEPTNO FROM  EMP 

练习1：rownum 查询指定行数的数据。

--查看EMP表中前10行
SELECT * FROM EMP WHERE ROWNUM<=10
--查看EMP表中后10行
SELECT * FROM EMP WHERE ROWNUM>4 --错误
--注释：ROWNUM 需要查找到1才会提取数据出来。
 
--子查询
SELECT * FROM 
(SELECT ROWNUM RR, EMP.EMPNO,
                  EMP.ENAME,
                  EMP.JOB,
                  EMP.MGR,
                  EMP.HIREDATE,
                  EMP.SAL,
                  EMP.COMM,
                  EMP.DEPTNO FROM EMP) A
WHERE A.RR>4
 
SELECT * FROM 
(SELECT ROWNUM RR, EMP.EMPNO,
                  EMP.ENAME,
                  EMP.JOB,
                  EMP.MGR,
                  EMP.HIREDATE,
                  EMP.SAL,
                  EMP.COMM,
                  EMP.DEPTNO FROM EMP) A
WHERE A.RR BETWEEN 2 AND 10

练习2：rowid 去重表数据，一般留下rowid最小的数据。

--rowid 去重表数据
DELETE FROM EMP_1 WHERE ROWID NOT IN
(SELECT MIN(ROWID) FROM EMP_1 GROUP BY EMPNO);
COMMIT;

三、模糊查询

_ 代表一个字符
% 代表任意个字符

SELECT * FROM EMP
--找出名字中第一个字符是'S'的员工信息
 
SELECT * FROM EMP WHERE ENAME LIKE'S%'
--找出名字红有字符'S'的员工信息
SELECT * FROM EMP WHERE ENAME LIKE'%S%'
--找出第二个字母是'M'的员工信息
SELECT * FROM EMP WHERE ENAME LIKE'_M%'

四、字符函数

1. REPLACE(X,OLD,NEW) 替换
2. LTRIM(X[,FMT])/RTRIM(X[,FMT]) 从左边/右边截去某个字符  FMT可以省略  省略的话默认截去的是空格
3. TRIM(X FORM STRING)
4. LENGTH(X) 查询X占多个字节
5. SUBSTR(X,START,N)  字符截取函数  把字符X从START开始截取N位，如果N省掉,默认截取到最后一位
6. INSTR(X,Y,START,N) 在字符X中从第START个字符开始查找字符Y第N次出现的位置 N省略默认是第一次出现
7. CONCAT(X,Y) 把字符X和Y 拼接到一起
8. WM_CONCAT

--REPLACE(X,OLD,NEW) 替换
I LOVE YOU --> I HATE YOU 
 
SELECT REPLACE('I LOVE YOU','LOVE','HATE') FROM DUAL;
--
SELECT REPLACE(ENAME,'S','----') FROM EMP;

--LTRIM(X[,FMT])/RTRIM(X[,FMT]) 从左边/右边截去某个字符  FMT可以省略  省略的话默认截去的是空格
 
 
SELECT LTRIM('AAADSDFS','A') FROM DUAL;
SELECT LTRIM('AAADSDAFS','AA') FROM DUAL;
SELECT LTRIM('AAADSDFS','ADF') FROM DUAL;
 
SELECT LTRIM('      D') FROM DUAL;

--TRIM(X FORM STRING)
SELECT TRIM('A' FROM 'ADFSDFA') FROM DUAL;
 
SELECT TRIM(' ' FROM '     A      ') FROM DUAL;

--LENGTH(X) 查询X占多个字节
 
SELECT LENGTH('DFA') FROM DUAL

--SUBSTR(X,START,N)  字符截取函数  把字符X从START开始截取N位，如果N省掉,默认截取到最后一位
SELECT SUBSTR('SDFASFD',2,1) FROM DUAL;
SELECT SUBSTR('SDFASFD',2) FROM DUAL;
SELECT SUBSTR('SDFASFD',-2,1) FROM DUAL;

--INSTR(X,Y,START,N) 在字符X中从第START个字符开始查找字符Y第N次出现的位置 N省略默认是第一次出现
 
SELECT INSTR('DFASFDSF','F',1,1) FROM DUAL;
SELECT INSTR('DFASFDSF','F',-1,3) FROM DUAL;

--CONCAT(X,Y) 把字符X和Y 拼接到一起
-- A B 
SELECT CONCAT('A','B') FROM DUAL
--  A B C 
SELECT CONCAT(CONCAT('A','B'),'C') FROM DUAL
 
SELECT 'A'||'B' FROM DUAL
SELECT 'A'||'B'||'C' FROM DUAL

---WM_CONCAT 把字符以逗号为间隔拼接在一起
SELECT * FROM EMP;
 
DEPENO                 ENAME
10                 CLARK,KING,MILLER
20                 SCOTT,ADAMS...
 
SELECT DEPTNO,WM_CONCAT(ENAME) FROM EMP GROUP BY DEPTNO

练习题1：把EMP中的每个员工的职位显示为
SMITH的工作是CLERK

SELECT ENAME||'的工作是'||JOB FROM EMP;

练习题2：查询EMP表中员工姓名最后一个字符是S的员工信息（至少两种方法）

SELECT  * FROM EMP WHERE ENAME LIKE'%S';
SELECT  * FROM EMP WHERE SUBSTR(ENAME,-1,1)='S';
SELECT  * FROM EMP WHERE LENGTH(ENAME)=INSTR(ENAME,'S',-1,1);

练习题3：'DS#FDHS#AFJ#KDSAHF#DSAKJ#LF' 不知道有多少个#号 截取最后一个#后面的字符 

SELECT SUBSTR('DS#FDHS#AFJ#KDSAHF#DSAKJ#LF',INSTR('DS#FDHS#AFJ#KDSAHF#DSAKJ#LF','#',-1,1)+1) FROM DUAL;
 
--截取10M-100M的10M
SELECT SUBSTR('10M-100M',0,INSTR('10M-100M','-',-1,1)-1) FROM DUAL;
 
--截取10M-100M的100M
SELECT SUBSTR('10M-100M',INSTR('10M-100M','-',-1,1)+1) FROM DUAL;

练习题4：把EMP表中的员工姓名显示为E***E      只留两个的字母  中间的用***替换

SELECT REPLACE(ENAME,SUBSTR(ENAME,2,LENGTH(ENAME)-2),'***') FROM EMP;

五、数字函数

ABS() 求绝对值

MOD(X,Y) 求X除以Y的余数

ROUND(X[,FMT])  四舍五入  FMT 不写默认到整数位

TRUNC(X[,FMT])  直接截断 

--ABS() 求绝对值
SELECT ABS(-23) FROM DUAL;
--MOD(X,Y) 求X除以Y的余数
 
SELECT MOD(9,10) FROM DUAL
SELECT MOD(3,2) FROM DUAL
--ROUND(X[,FMT])  四舍五入  FMT 不写默认到整数位
SELECT ROUND(3.156) FROM DUAL;--3
SELECT ROUND(3.156,2) FROM DUAL;--3.16
--TRUNC(X[,FMT])  直接截断 
SELECT TRUNC(3.67) FROM DUAL;--3
SELECT TRUNC(3.67,1) FROM DUAL;--3.6

练习1：随便给定一个三位数 写一句SQL  分别取出去个位数 十位数 百位数

123
 
3 2 1
 
SELECT  MOD(123,10),TRUNC(MOD(123,100)/10),TRUNC(123/100) FROM DUAL

六、日期函数

LAST_DAY(D) 求本月的最后一天

ADD_MONTHS(D,N) 在当年月份加N个月 N可以为负数

--LAST_DAY(D) 求本月的最后一天
SELECT LAST_DAY(SYSDATE) FROM DUAL;
--ADD_MONTHS(D,N) 在当年月份加N个月 N可以为负数
SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE,-2) FROM DUAL;
 
SELECT ADD_MONTHS(DATE'2020-04-30',1) FROM DUAL;
SELECT ADD_MONTHS(DATE'2020-05-31',1) FROM DUAL;

七、日期函数与TRUNC()搭配使用

SELECT SYSDATE,
       TRUNC(SYSDATE),--2020/8/12
       TRUNC(SYSDATE,'YYYY'),--截取到当年的第一天 2020/1/1
       TRUNC(SYSDATE,'MM'),--截取到当月的第一天 2020/8/1
       TRUNC(SYSDATE,'Q'),--截取到本季度的第一天 2020/7/1
       TRUNC(SYSDATE,'IW')--截取到本周的第一天
FROM DUAL;

八、数据类型转换函数

TO_NUMBER()

TO_CHAR()

TO_DATE()

--TO_NUMBER()
SELECT TO_NUMBER('    13213    ') FROM DUAL;
SELECT TO_NUMBER(' 13  213 ') FROM DUAL;--错误
 
--日期不可以直接转数字，必须先转字符再转数字
 
SELECT TO_NUMBER(SYSDATE) FROM DUAL--错误
SELECT TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) FROM DUAL

--TO_CHAR()
-- 日期转字符
 
SELECT TO_CHAR(SYSDATE),
       TO_CHAR(SYSDATE,'YYYY'),--2020
       TO_CHAR(SYSDATE,'Q'),--3
       TO_CHAR(SYSDATE,'MM'),--08
       TO_CHAR(SYSDATE,'IW'),--33 当年的第几周
       TO_CHAR(SYSDATE,'W'),--2 当月的第几周
       TO_CHAR(SYSDATE,'DDD'),--225 当年的第几天
       TO_CHAR(SYSDATE,'DD'),--12 当月的第几天
       TO_CHAR(SYSDATE,'D')--4  当周的第几天
FROM DUAL

--TO_DATE()
SELECT TO_DATE('20200801','YYYYMMDD') FROM DUAL;

九、MERGE INTO

MERGE INTO..  用于把原表数据插入到目标表中，可以用作数据增量

MERGE INTO 目标表
USING  源表/查询的结果集
ON (目标表.字段=源表.字段)
WHEN MATCHED THEN                 --当上一句on条件的数据相同时，更新数据，不写相同的条件，默认相同不更新数据；
  UPDATE SET 目标表.字段=源表.字段
WHEN NOT MATCHED THEN INSERT      --当上一句on条件的数据不相同时，插入数据；
 (目标表的字段) VALUES(源表的字段)

MERGE INTO DICTB B
USING  DICTA A
ON (B.ID1=A.ID1)
WHEN MATCHED THEN 
  UPDATE SET
  B.DESC1=A.DESC1
WHEN NOT MATCHED THEN 
  INSERT (B.ID1, B.DESC1) VALUES(A.ID1, A.DESC1)

十、分析函数

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，
每一组的每一行都有返回值

• 语法格式：分析函数语法

  FUNCTION_NAME(<参数>,…) OVER (<PARTITION BY 表达式,…> <ORDER BY 表达式 <ASC DESC> )

1、跟聚合函数一起用 （sum over、avg over、min over、max over、count over）

2、跟排序函数一起用  （row_number、rank、dense_rank）

3、跟位移函数一起用--一般用来求同比环比（lag、lead）

注释：

分析函数和聚合函数的不同之处是什么？

普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

--1.跟聚合函数一起用 
MAX/MIN/SUM/AVG/COUNT  --有ORDER BY  不仅仅是排序 --->累计求和
 
SELECT E.*,AVG(SAL)OVER(PARTITION BY DEPTNO) 平均工资  FROM EMP E
SELECT E.*,AVG(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL)   FROM EMP E
--每个部门的总工资
SELECT E.*,SUM(SAL)OVER(PARTITION BY DEPTNO ) FROM EMP E
--每个部门的累计工资
SELECT E.*,SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL ) FROM EMP E

--2.跟排序函数一起用  
ROW_NUMBER()   1 2 3 4..
DENSE_RANK()   1 2 2 3 4...
RANK()         1 2 2 4 5...
 
SELECT E.ENAME,E.SAL,ROW_NUMBER()OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL)  FROM EMP E;
SELECT E.ENAME,E.SAL,DENSE_RANK()OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL)  FROM EMP E;
SELECT E.ENAME,E.SAL,RANK()OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL)  FROM EMP E

3.跟位移函数一起用--一般用来求同比环比
LEAD()/LAG()
LAG()与LEAD()：求之前或之后的第N行 
 
LAG和LEAD函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和LEAD有更高的效率。
LAG(ARG1,ARG2,ARG3)第一个参数是列名，
第二个参数是偏移的offset，
第三个参数是超出记录窗口时的默认值。
 
SELECT E.ENAME,E.HIREDATE,LEAD(E.HIREDATE,1)OVER(ORDER BY E.HIREDATE)  FROM EMP E
SELECT E.ENAME,E.HIREDATE,LEAD(E.HIREDATE,1,DATE'1980-12-17')OVER(ORDER BY E.HIREDATE)  FROM EMP E
 
SELECT E.ENAME,E.HIREDATE,LEAD(E.HIREDATE,1)OVER(PARTITION BY E.DEPTNO ORDER BY E.HIREDATE)  FROM EMP E

 十一、空值排序方式

SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO);--总计
 
SELECT  ENAME,COMM FROM EMP ORDER BY COMM NULLS FIRST;  --使列的空值排序在前面
SELECT  ENAME,COMM FROM EMP ORDER BY COMM DESC NULLS LAST; --使列的空值排序在后边