目录
1.2、UNION --去重，就必须排序，所以性能差，不常用
一、集合运算（即为并集）
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%'
四、字符函数
- REPLACE(X,OLD,NEW) 替换
- LTRIM(X[,FMT])/RTRIM(X[,FMT]) 从左边/右边截去某个字符 FMT可以省略 省略的话默认截去的是空格
- TRIM(X FORM STRING)
- LENGTH(X) 查询X占多个字节
- SUBSTR(X,START,N) 字符截取函数 把字符X从START开始截取N位，如果N省掉,默认截取到最后一位
- INSTR(X,Y,START,N) 在字符X中从第START个字符开始查找字符Y第N次出现的位置 N省略默认是第一次出现
- CONCAT(X,Y) 把字符X和Y 拼接到一起
- 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 E3.跟位移函数一起用--一般用来求同比环比
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; --使列的空值排序在后边
扫一扫
289
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
