Oracle 语法总结 (四)

你的名字 2021-09-20 18:16 374阅读 0赞

### 一，知识点 ###

\--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 )