1-1 集合查询
|
操作符 |
返回结果集 |
内容 |
|
UNION |
由每个查询选择的所有不重复的行 |
并集。不包含重复值，默认按第 1 个查询的第 1 列升序排列 |
|
UNION ALL |
由每个查询选择的所有的行，包括所有重复的行 |
包括所有重复的行，完全并集包含重复值，不排序 |
|
INTERSECT |
由每个查询选择的所有不重复的相交行 |
交集。不包含重复行，按第 1 个查询的第 1 列升序排列 |
|
MINUS |
在第一个查询中，不在后面查询中的行 |
不包含重复行，按第 1 个查询的第 1 列升序排列 |
除 UNION ALL 之外，系统会自动将重复的记录删除
系统将第一个查询的列名显示在输出中
除 UNION ALL 之外，系统自动按照第一个查询中的第一个列的升序排列
1-1-1 UNION 操作符
SELECT employee_id, job_id FROM employees UNION SELECT employee_id, job_id FROM job_history;
1-1-2 UNION ALL 操作符
UNION ALL 操作符返回两个查询的结果集的并集以及两个结果集的重复部分（不去重复值）
SELECT employee_id, job_id, department_id FROM employees UNION ALL SELECT employee_id, job_id, department_id FROM job_history
1-1-3 INTERSECT 操作符
INTERSECT 操作符返回两个结果集的交集
SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history;
1-1-4 MINUS 操作符
SELECT employee_id,job_id FROM employees MINUS SELECT employee_id,job_id FROM job_history;
使用集合的注意事项：
在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
括号可以改变执行的顺序
ORDER BY 子句:
只能在语句的最后出现
可以使用第一个查询中的列名, 别名或相对位置
1-2 分级查询
SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECT BY PRIOR condition(s)] ;
1. 其中level 关键字是可选的，表示等级。
2.From 之后可以是table,view 但是只能是一个table 。
3.Where 条件限制了查询返回的行，但是不影响层次关系，不满足条件的节点不返回，但是这个不满足条件的节点的下层child 不受影响。
4.Start with 是表示开始节点，对于一个真实的层次关系，必须要有这个子句，但是不是必须的，后面详细介绍。
5.connect by prior 是指定父子关系，其中prior 的位置不一定要在connect by 之后，对于一个真实的层次关系，这也是必须的。
1-2-1 树形结构
树结构的数据存放在表中，数据之间的层次关系即父子关系，通过表中的列与列间的关系来描述，如该表中的EMPLOYEE_ID 和MANAGER_ID 。 EMPLOYEE_ID 表示该雇员的编号， MANAGER_ID 表示领导该雇员的人的编号，即子节点的MANAGER_ID 值等于父节点的EMPLOYEE_ID 值。在表的每一行中都有一个表示父节点的MANAGER_ID （除根节点外），通过每个节点的父节点，就可以确定整个树结构。
1-2-2 遍历树
首先必须确定起始点，通过start with 子句，后面加条件，这个条件是任何合法的条件表达式。
Start with 确定将哪行作为root ，如果没有start with, 则每行都当作root ，然后查找其后代，这不是一个真实的查询。Start with 后面可以使用子查询，如果有where 条件，则会截断层次中的相关满足条件的节点，但是不影响整个层次结构。可以带多个条件。
运算符PRIOR 被放置于等号前后的位置，决定着查询时的检索顺序。
PRIOR 被置于CONNECT BY 子句中等号的前面时，则强制从根节点到叶节点的顺序检索，即由父节点向子节点方向通过树结构，我们称之为自顶向下的方式。
PIROR 运算符被置于CONNECT BY 子句中等号的后面时，则强制从叶节点到根节点的顺序检索，即由子节点向父节点方向通过树结构，我们称之为自底向上的方式。
CONNECT BY PRIOR column1 = column2
遍历树: 从底到顶
从底到顶查询树结构时 , 也要指定一个开始节点，以此开始向上查找其父节点，直至找到根节点，其结果将是结构树中的一枝数据
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR manager_id = employee_id ;
遍历树: 从顶到底
在自顶向下查询树结构时，不但可以从根节点开始，还可以定义任何节点为起始节点，以此开始向下查找。这样查找的结果就是以该节点为开始的结构树的一枝。
SELECT last_name||' reports to '|| PRIOR last_name "Walk Top Down" FROM employees START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id ;
1-2-3 使用 LEVEL 伪列标记层次
在查询中，可以使用伪列LEVEL 显示每行数据的有关层次。LEVEL 将返回树型结构中当前节点的层次，可以使用LEVEL 来控制对树型结构进行遍历的深度。
在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1 ，根节点的子节点为2 ， 依此类推 。
使用 LEVEL 和 LPAD 格式化分层查询
COLUMN org_chart FORMAT A12 SELECT level, LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,‘-') AS org_chart FROM employees START WITH last_name='King' CONNECT BY PRIOR employee_id=manager_id
LEVEL ORG_CHART
--- -----------
1 King
2 - Kochhar
3 --Whalen
3 --Higgins
4 --- Gietz
2 -De Haan
3 -- Hunold
4 --- Emst
4 ---Lorentz
2 - Mourgos
3 -- Rajs
3 --Davies
3 --Matos
3 --Vargas
2 - Zlotkey
3 --Abel
3 --Taylor
3 --Gant
2 - Hartstein
3 --Fay
1-2-4 修剪分支
where 子句会将节点删除，但是其后代不会受到影响
connect by 中加上条件会将满足条件的整个树枝包括后代都删除
1-3 GROUP BY 子句的扩展
使用 ROLLUP 操作分组
使用 CUBE 操作分组
使用 GROUPING 函数处理 ROLLUP 或 CUBE 操作所产生的空值
使用 GROUPING SETS 操作进行单独分组
1-3-1
带有
ROLLUP
和
CUBE
操作的
GROUP BY
子句
使用带有ROLLUP 和 CUBE 操作的GROUP BY 子句产生多种分组结果
ROLLUP 产生n + 1 种分组结果
CUBE 产生2 的n 次方种分组结果
ROLLUP 操作符
SELECT [column,] group_function(column). . . FROM table [WHERE condition] [GROUP BY [ROLLUP] group_by_expression] [HAVING having_expression]; [ORDER BY column];
ROLLUP 是对 GROUP BY 子句的扩展
ROLLUP 产生n + 1 种分组结果，顺序是从右向左
SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY ROLLUP(department_id, job_id);
CUBE 操作符
SELECT [column,] group_function(column)... FROM table [WHERE condition] [GROUP BY [CUBE] group_by_expression] [HAVING having_expression] [ORDER BY column];
CUBE 是对 GROUP BY 子句的扩展
CUBE 会产生类似于笛卡尔集的分组结果
SELECT department_id, job_id, SUM(salary) FROM employees WHERE department_id < 60 GROUP BY CUBE (department_id, job_id) ;
1-3-2 GROUPING 函数
SELECT [column,] group_function(column) . , GROUPING(expr) FROM table [WHERE condition] [GROUP BY [ROLLUP][CUBE] group_by_expression] [HAVING having_expression] [ORDER BY column];
GROUPING 函数可以和 CUBE 或 ROLLUP 结合使用
使用 GROUPING 函数，可以找到哪些列在该行中参加了分组
使用 GROUPING 函数, 可以区分空值产生的原因
GROUPING 函数返回 0 或 1
SELECT department_id DEPTID, job_id JOB, SUM(salary), GROUPING(department_id) GRP_DEPT, GROUPING(job_id) GRP_JOB FROM employees WHERE department_id < 50 GROUP BY ROLLUP(department_id, job_id);
1-3-3 GROUPING SETS
GROUPING SETS 是对GROUP BY 子句的进一步扩充
使用 GROUPING SETS 在同一个查询中定义多个分组集
Oracle 对 GROUPING SETS 子句指定的分组集进行分组后用 UNION ALL 操作将各分组结果结合起来
Grouping set 的优点:
只进行一次分组即可
不必书写复杂的 UNION 语句
SELECT department_id, job_id, manager_id,avg(salary) FROM employees GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));
1-3-4 复合列
复合列是被作为整体处理的一组列的集合
ROLLUP (a,b,c,d)
使用括号将若干列组成复合列在ROLLUP 或 CUBE 中作为整体进行操作
在ROLLUP 或 CUBE 中, 复合列可以避免产生不必要的分组结果
SELECT department_id, job_id, manager_id, SUM(salary) FROM employees GROUP BY ROLLUP( department_id,(job_id, manager_id));
1-3-5 连接分组集
连接分组集可以产生有用的对分组项的结合
将各分组集, ROLLUP 和 CUBE 用逗号连接 Oracle 自动在 GROUP BY 子句中将各分组集进行连接
连接的结果是对各分组生成笛卡尔集
SELECT department_id, job_id, manager_id, SUM(salary) FROM employees GROUP BY department_id, ROLLUP(job_id), CUBE(manager_id);