12、oracle--查询/连接/层次查询

uzayvdjcxl

实例;http://www.1keydata.com/cn/sql/sql-truncate.php
会话隔离：

备份表
   create table table_name1 as select * from emp;

最常用的SELECT:
SELECT的基本构成
DUAL
查询（ query）和子查询（ subquery）
层次查询
集合
连接
WITH语句
理解SELECT语法图

执行顺序：
where最新执行
group by
order by

详细顺序：
from：对from子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1
on：对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2。
outer(join)：如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。
where：对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.
group by：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.
CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.
having：对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.
select：处理SELECT列表，产生VT8.
distinct：将重复的行从VT8中移除，产生VT9.
order by：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).
top：从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。



count(*)是计算有多少列
sum（salary）是计算salary列的总合

DML异常：
ORA-00979 不是 GROUP BY 表达式错误的解释  
err  ;select deptno,job,avg(sal) from emp  group by deptno;
right:select deptno,job,avg(sal) from emp group by deptno,job;
因为在select 列表像中出现了像deptno和job，而在group by中并没有出现的缘故。

子查询（ subquery）
一个查询嵌套在另外的SQL语句中，该查询称为子查询（ subquery）
子查询不仅仅会出现在SELECT中
select empno, ename from emp where mgr in (select empno from emp where job='MANAGER');

select * from emp where sal > 1.4*(select avg(sal) from emp);

insert into dept(deptno, dname, loc) select 50, 'TRAINING', 'PEKING' from dual;

exists是判断是否存在,和in类似,但效率要比in高
update emp set sal=sal*1.2 where exists (select 1 from dept where deptno=emp.deptno and loc='DALLAS');
update emp set sal=sal*1.2 where in (select deptno from dept  where loc='DALLAS');
注意exists的使用，一定要带表的别名

Set:
数学上的集合
集合的概念
一个集合就是将数个对象归类而分成为一个或数个形态各异的大小整体。
一般来讲，集合是具有某种特性的事物的整体，或是一些确认对象的汇集。
构成集合的事物或对象称作元素或是成员。
集合的特性
唯一性
无序性
确定性


计算机上的集合
列表：数据项的顺序是确定的，也可以存在多个相同的数据项。
队列、堆栈
集：数据项是无序的，也不允许存在相同数据项。
多重集：类似于集，其中数据项是无序的。但在多重集中，可以存在相同的数据项。
关联数组：和字典相似，为键输入提供一个值输出，即Key-Value
树：“根”节点与一定数量的数据项以亲-子关系联系起来，而其子数据项也与另外的数据项
以同样的方式联系。
图：每个数据项都可以与一个或多个其它数据项联系起来，其中每个节点都是平等的，类似于
无根节点、无亲-子关系的树。

Oracle的结果集（ ResultSet）
结果集的概念
一个查询（包括子查询）的结果，称为结果集。
结果集的特性
不唯一性
无序性
确定性


Oracle中的集合操作
并集
UNION：集
UNION ALL：集或多重集
交集
INTERSECT：集
差集（补集）
MINUS：集

操作符（ UNION [ALL], INTERSECT, MINUS）
拥有同样的优先级
括号可以改变运算顺序
query1 minus (query2 union query3)

contect:
连接定义
从两张或更多张表/视图中做的查询称为连接
只要FROM后面出现多个表/视图， Oracle总会去执行连接操作
如果某列的名称在多个表/视图中出现，那么为避免模棱两可必须在列前加上表/视图的名或别名

连接条件
通常多表连接都会至少有一个连接条件
无连接条件的连接结果集称为笛卡尔积（ Cartesian Products）
连接条件两端的字段必然来自不同的两张表/视图
多表连接时，除了连接条件，还可以有单表数据过滤条件

连接类型: 等值连接、自连接、笛卡尔积、内连接（简单连接/连接）、外连接、反连接、半连接
等值连接  [ = ]  (笛卡尔积受制于where条件)
自连接   from emp a, emp b  
内连接   from emp inner join salgrade on
反连接   not in
半连接   where exists 相当于 and （没有生成笛卡尔积，故快速）
          select * from emp a where exists (select 1 from dept b where loc='NEW YORK' and a.deptno=b.deptno);
外连接   （+）过时但可用，替代：left outer join, right outer join和full outer join
from dept b left join emp a on
from emp a right join dept b on
from emp a right outer join dept b on
左连接和左外连接的区别：
两者是一样的，只是写法不同，相同的概念。


层次查询Hierarchical Query
        select empno, ename, job, mgr, deptno, level from emp start with job='PRESIDENT'connect by prior empno= mgr;
         CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历
         select * from dual connect by rownum<5