Oracle进阶总结（2）

Oracle知识点总结

1.分组函数

---

1） MIN 求最小值
2） MAX 求最大值
3）SUM 求和
4）AVG 求平均值
5）COUNT 求满足条件的记录数

2.GROUP BY

---

通过GROUP BY 子句可将表中满足WHERE条件
的记录按照指定的列划分成若干个小组
注意 （1）.SELECT deptno, COUNT(ename) FROM emp;
错误: [Err] ORA-00937: not a single-group group function


3.ORDER BY 子句

---

根据列名排序 默认降序（DESC）,升序(ASC)

4.使用HAVING 子句限制组

---

– 记录已经分组.
–使用过组函数.
– 与HAVING 子句匹配的结果才输出

SELECT deptno, max(sal)
 FROM emp
 GROUP BY deptno
 HAVING max(sal)>2900;
 结果:
 DEPTNO MAX(SAL)
--------- ---------
 10 5000
 20 3000

5.SELECT语句执行过程

---

–1.通过FROM子句中找到需要查询的表；
– 2.通过WHERE子句进行非分组函数筛选判断；
– 3.通过GROUP BY子句完成分组操作；
– 4.通过HAVING子句完成组函数筛选判断；
– 5.通过SELECT子句选择显示的列或表达式及组函数；
–6.通过ORDER BY子句进行排序操作。

6.子查询

---

1)单行子查询

• 子查询只返回一行一列
• 使用单行运算符
实例:
显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作

SELECT ename, job
 FROM emp
 WHERE job = CLERK
 (SELECT job
 FROM emp
 WHERE empno =7369)
AND  sal >
   (SELECT sal
   FROM emp
   WHERE empno = 7876);

2)多行子查询

子查询返回记录的条数 可以是一条或多条。多行操作符包括：
• IN 判断是否与子查询的任意一个返回值相同
• ANY 表示和子查询的任意一行结果进行比较，有一个满足条件即可。
• ALL 表示和子查询的所有行结果进行比较，每一行必须都满足条件。

3)嵌套子查询

在通常的子查询中，子查询是以嵌套的方式写在父查询的WHERE、HAVING、FROM子句中
写法

SELECT empno,ename,sal
FROM emp e ,(SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno) d
WHERE e.deptno =d.deptno
AND e.sal >d.avgsal;

4）相关子查询

当子查询中引用了父查询表中的一个列时，Oracle服务器执行相关子查询。
•在WHERE子句中使用相关子查询；
•在SELECT子句中使用相关子查询；

例：查询至少调过2次岗位的员工编号，姓名，岗位
相关子查询 SELECT e.empno, ename, e.job FROM emp e WHERE 2 <= (SELECT COUNT(*) FROM emp_jobhistory WHERE empno = e.empno);

相关子查询还可使用EXISTS和 NOT EXISTS操作符来进行操作

例：查询哪些人是经理？
SELECT ename , job, sal, deptno FROM emp e WHERE EXISTS (SELECT '1' FROM emp WHERE mgr= e.empno);

7.层次查询

SELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)]

– LEVEL：节点的层次，伪列，由查询的起点开始算起为1，依次类推。
– START WITH：指定层次的根行 (起点)。这个子句对于一个正确的分级查询是必须的。
– CONNECT BY PRIOR：指定存在父与子行的关系列。对于分级查询该子句是
必须的。

从顶向下遍历，用EMP 表
... CONNECT BY PRIOR empno = mgr

用LEVEL 伪列将行分等级

SELECT LEVEL,EMPNO,ENAME,JOB,MGR FROM EMP START WITH ENAME='KING' CONNECT BY PRIOR EMPNO=MGR
1   7839    KING    PRESIDENT   
2   7566    JONES   MANAGER 7839
3   7788    SCOTT   ANALYST 7566
4   7876    ADAMS   CLERK   7788
3   7902    FORD    ANALYST 7566
4   7369    SMITH   CLERK   7902
2   7698    BLAKE   MANAGER 7839
3   7499    ALLEN   SALESMAN    7698
3   7521    WARD    SALESMAN    7698
3   7654    MARTIN  SALESMAN    7698

8.分页

---

SELECT b.*
FROM (SELECT ROWNUM rn,[列名1,列名2,....列名n]
FROM 表名1,[表名2,...表名n]
[WHERE 条件表达式]) b
WHERE rn <=目标页数*每页记录数and rn > (目标页数-1)*每页记录数

9.集合运算

---

1）联合运算UNION

返回由任一查询结果集包含的行，并且去除重复行,并且按照查询结果集的第一列升序排序

2）完全联合运算 UNION ALL

返回由任一查询结果集包含的行，并且包含重复行，默认情况下不对结果集进行排

3）相交 INTERSECT

返回多个查询结果集的公有行。

4）相减 MINUS

返回第一个查询中存在而第二个查询中不存在的行记录。