Oracle 语法总结 (四)

mac2022-06-30 31

一，知识点

--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所在的部门工资上浮 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 )

浮生夢举世瞩目当有一天，有星光刺破黑洞的昏暗，那是我吞吐天地的余晖，代表着我已映照诸天。当有一天，有玄雷划过星空的浩瀚，那是我拳光的劲风，代表着我已回来。当星河列阵，宇宙星海星光齐绽，那是我在笑，我已复苏，我在归来，我已无敌！

最新回复(0)