Oracle 语法总结 (四)

原创

day_day_up_cium 2021-07-07 13:57:19 ©著作权

文章标签 oracle 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者day_day_up_cium的原创作品，请联系作者获取转载授权，否则将追究法律责任

一，知识点

--1
子查询
是一条查询语句，他是嵌套在其他语
句中的，目的为外层SQL语句提供数
据DDL,DML,DQL都能使用
谁的工资高于CLARK?
SELECT ename,sal FROM emp_liwc WHERE sal>(SELECT sal FROM emp_liwc WHERE ename = 'CLARK')
和CLARK同部门的有谁?
SELECT ename,deptno FROM emp_liwc WHERE deptno = (SELECT deptno FROM emp_liwc WHERE ename = 'CLARK')
那些员工的工资是高于部门平均工资的
SELECT ename,sal FROM emp_liwc WHERE sal > (SELECT AVG(sal) FROM emp_liwc)
--
DDL中使用子查询，可以将子查询的结果集当做一张表快速创建出来
CREATE TABLE employee_liwc AS
SELECT e.empno,e.ename,e.sal,e.job,e.deptno,d.dname,d.loc
FROM emp_liwc e JOIN dept_liwc d ON e.deptno = d.deptno
--
DML子查询
将CLARK所在的部门工资上浮%10
UPDATE emp_liwc SET sal = sal*1.1 WHERE deptno = (SELECT deptno FROM emp_liwc WHERE ename = 'CLARK')
--
子查询根据结果分为：
单行单列子查询，多行单列子查询，多行多列子查询，
单列子查询常用在过滤条件中，多列子查询当做表使用
多行单列子查询过滤判断根据 IN,ANY,ALL
与职位是SALESMAN同部门员工有哪些
SELECT ename,job FROM emp_liwc WHERE deptno IN(SELECT deptno FROM emp_liwc WHERE job = 'SALESMAN')
查看比CLERK和SALESMAN工资都高的员工
SELECT ename,sal FROM emp_liwc WHERE sal > ALL(SELECT sal FROM emp_liwc WHERE job IN('CLERK','SALESMAN'))
--2
EXISTS关键字
用在过滤条件中，后面跟一个子查询，只要该子查询可以查询出一条记录，就满足条件
SELECT deptno,dname FROM dept_liwc d WHERE EXISTS (SELECT * FROM emp_liwc e WHERE e.deptno = d.deptno)
谁是别人的上司
SELECT m.ename FROM emp_liwc m
WHERE EXISTS (SELECT * FROM emp_liwc e WHERE e.mgr = m.empno )
查看部门最低工资前提是高于30号部门的
SELECT MIN(sal) FROM emp_liwc GROUP BY deptno HAVING MIN(sal)>(SELECT MIN(sal) FROM emp_liwc WHERE deptno = 30)----------
查看那些员工的工资高于其在部门的平均工资
SELECT e.ename,e.sal,e.deptno
FROM emp_liwc e,(SELECT AVG(sal) avg_sal,deptno FROM emp_liwc GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal>t.avg_sal
查看每个部门最高工资是谁，列出名字，职位工资，部门号
SELECT e.ename,e.job,e.sal,e.deptno
FROM emp_liwc e,(SELECT MIN(sal) avg_sal,deptno FROM emp_liwc GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal=t.avg_sal

--
SELECT e.ename,e.sal,(SELECT d.dname FROM dept_liwc d WHERE e.deptno=d.deptno) dname FROM emp_liwc e
--3
分页查询
就是将一个查询语句查询的数据分段查询出来
好处在于，当查询结果非常大的时候，有效减少数据传输量，
提高速度降低开销
ROWNUM
是一个伪列，实际上不存在任何表中，但是每张表都可以查询该字段，值为对应的行号
是伴随查询过程中动态产生的，只要查询出一条记录该记录就是其行号，从1开始递增
SELECT ROWNUM,ename,sal,job,deptno FROM emp_liwc
--
查看6-10行的记录
使用ROWNUM时，不要使用ROWNUM>1以上作为过滤天没见否则会得不到任何值
SELECT ROWNUM,ename,sal,job,deptno FROM emp_liwc WHERE ROWNUM >10
--
SELECT *
FROM(SELECT ROWNUM rm,ename,sal,job,deptno FROM emp_liwc)
WHERE rm BETWEEN 6 AND 10
工资排名6-10
SELECT * FROM(SELECT ROWNUM rm,t.*
FROM (SELECT sal,ename FROM emp_liwc ORDER BY sal DESC) t)
WHERE rm BETWEEN 6 AND 10
优化
SELECT * FROM(SELECT ROWNUM rm,t.*
FROM (SELECT sal,ename FROM emp_liwc ORDER BY sal DESC) t
WHERE ROWNUM<=10
)
WHERE rm>=6
--
PageSize:每页显示的条目
Page：要显示的页数
start：（PageSize-1）*PageSize+1
end:PageSize*Page
--4
DECODE 函数
SELECT ename,job,sal, DECODE(JOB,'MANAGER',sal*1.2,'ANALYST',sal*1.1,'SALESMAN',sal*1.05,sal)bonus FROM emp_liwc
统计公司人数，所有ANALYST和MANAGER看成一组其他看成一组
SELECT COUNT(*),DECODE(JOB,'ANALYST','VIP','MANAGER','VIP','OTHER')FROM emp_liwc
GROUP BY DECODE(JOB,'ANALYST','VIP','MANAGER','VIP','OTHER')
CSAE语法
SELECT ename,job,sal,CASE job WHEN 'MANAGER' THEN sal*1.2
WHEN 'ANALYST' THEN sal*1.1 WHEN 'SALESMAN' THEN sal*1.05 ELSE sal END bonus FROM emp_liwc
--排序中的应用
SELECT deptno,dname,loc FROM dept_liwc
ORDER BY DECODE(dname,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3)
--5
排序函数
排序函数可以将结果集按照指定的字段分组，组内再按照指定的字段排序
然后生成组内编号
ROW_NUMBER:生成组内连续且唯一的数字
RANK:生成组内不连续不唯一的数字
DENSE_RANK:生成组内连续不唯一的数字
查看每个部门的工资排名
SELECT ename,deptno,sal,DENSE_RANK() OVER (PARTITION BY deptno
ORDER BY sal DESC)rank
FROM emp_liwc

CREATE TABLE sales_tab(
year_id NUMBER NOT NULL,
month_id NUMBER NOT NULL,
day_id NUMBER NOT NULL,
sales_value NUMBER(10,2)NOT NULL
);
INSERT INTO sales_tab
SELECT TRUNC(DBMS_RANDOM.value(2010,2012))AS year_id,
TRUNC(DBMS_RANDOM.value(1,13))AS month_id,
TRUNC(DBMS_RANDOM.VALUE(1,32))AS day_id,
ROUND(DBMS_RANDOM.VALUE(1,100),2)AS SALES_value
FROM dual
CONNECT BY level<=1000;
COMMIT;
SELECT * from sales_tab

集合操作（字段一样多）
并集
SELECT ename,job,sal FROM emp_liwc WHERE job = 'MANAGER'
UNION --重复显示一次(OR)
SELECT ename,job,sal FROM emp_liwc WHERE sal>2500

SELECT ename,job,sal FROM emp_liwc WHERE job = 'MANAGER'
UNION ALL --重复显示两次
SELECT ename,job,sal FROM emp_liwc WHERE sal>2500
交集
SELECT ename,job,sal FROM emp_liwc WHERE job = 'MANAGER'
INTERSECT --同时满足（AND）
SELECT ename,job,sal FROM emp_liwc WHERE sal>2500
差集
SELECT ename,job,sal FROM emp_liwc WHERE job = 'MANAGER'
MINUS --上面有下面没有的
SELECT ename,job,sal FROM emp_liwc WHERE sal>2500
--7
高级分组函数
--
ROLLUP函数
GROUP BY ROLLUP (A,B,C)
等价于
GROUP BY A,B,C
UNION ALL
GROUP BY A,B
UNION ALL
GROUP BY A
全表
查看每天，每月，每年的，总共的营业额
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY ROLLUP (year_id,month_id,day_id)
--
CUBE(A,B,C)所有情况都比较
A,B
A,C
B,C
A
B
C
ABC
全表
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab GROUP BY CUBE (year_id,month_id,day_id)
--
GROUPING SETS
自定义分组方式，每个参数为一种分组方式，将分组结果并在一个结果集上
查看每天每月的营业额
SELECT year_id,month_id,day_id,SUM(sales_value)FROM sales_tab GROUPING SETS ((year_id,month_id,day_id),(year_id,month_id))
ORDER BY year_id,month_id,day_id

SELECT * FROM sales_tab

二，例子

1:查看与CLARK相同职位的员工
SELECT ename,job FROM emp_liwc WHERE job = (SELECT job FROM emp_liwc WHERE ename = 'CLARK')
2:查看低于公司平均工资的员工
SELECT ename,sal FROM emp_liwc WHERE sal<(SELECT AVG(sal) FROM emp_liwc)
3:查看与ALLEN同部门的员工
SELECT ename,deptno FROM emp_liwc WHERE deptno = (SELECT deptno FROM emp_liwc WHERE ename = 'ALLEN')
4:查看平均工资低于20号部门平均工资的部门平均工资
SELECT AVG(sal) FROM emp_liwc GROUP BY deptno HAVING AVG(sal)<(SELECT AVG(sal) FROM emp_liwc WHERE deptno=20 )
5:查看低于自己所在部门平均工资的员工
SELECT e.ename,e.sal FROM emp_liwc e,(SELECT AVG(sal) xb,deptno FROM emp_liwc GROUP BY deptno)t WHERE e.deptno = t.deptno AND e.sal>t.xb
6:查看公司工资排名的第1-5名
SELECT * FROM(SELECT ROWNUM,t.* FROM (SELECT sal,ename FROM emp_liwc ORDER BY sal DESC)t) WHERE ROWNUM<=5
7:查看CLERK职位的人数和其他职位的总人数各多少?
SELECT COUNT(*),DECODE(JOB,'CLERK','VIP','OTHER')FROM emp_liwc
GROUP BY DECODE(JOB,'CLERK','VIP','OTHER')
8:查看每个职位的工资排名
SELECT ename,job,sal,ROW_NUMBER() OVER (PARTITION BY job
ORDER BY sal DESC)rank
FROM emp_liwc
9:查看每个职位的工资排名，若工资一致，排名一致
SELECT ename,job,sal, RANK() OVER (PARTITION BY job
ORDER BY sal DESC)rank
FROM emp_liwc
10:查看每个职位的工资排名，若工资一致，排名一致，不跳名次。
SELECT ename,job,sal,DENSE_RANK() OVER (PARTITION BY job
ORDER BY sal DESC)rank
FROM emp_liwc
11:分别查看:同部门同职位，同职位，以及所有员工的工资总和
SELECT deptno,job,SUM(sal)
FROM emp_liwc
GROUP BY ROLLUP(deptno,job)
ORDER BY deptno,job
12:分别查看:同部门同职位，同职位，同部门以及所有员工的工资总和
SELECT deptno,job,SUM(sal)
FROM emp_liwc
GROUP BY CUBE(deptno,job)
ORDER BY deptno,job
13:分别查看同部门同职位和同职位的员工的工资总和
SELECT deptno,job,SUM(sal)
FROM emp_liwc
GROUP BY GROUPING SETS(
(deptno,job),(job))
ORDER BY deptno,job
14:查看公司最高工资的员工的名字以及所在部门名称
SELECT e.ename,d.dname FROM emp_liwc e,dept_liwc d WHERE sal = (SELECT MAX(sal) FROM emp_liwc) AND d.deptno = e.deptno
15:查看每个部门的最高工资的员工名字
SELECT e.ename,e.sal FROM emp_liwc e,(SELECT MAX(sal) avg_sal,deptno FROM emp_liwc GROUP BY deptno) t
WHERE e.deptno=t.deptno AND e.sal=t.avg_sal
16:查看有下属的员工信息
SELECT m.* FROM emp_liwc m
WHERE EXISTS (SELECT * FROM emp_liwc e WHERE e.mgr = m.empno )
SELECT m.ename FROM emp_liwc m
WHERE (SELECT * FROM emp_liwc e WHERE e.mgr = m.empno )