实验22：建立和使用视图
- 【View视图】
使用视图为了我们的方便，增加了数据库的负担，视图下降数据库的性能，视图是查询语句的别名，视图的定义存在于数据字典中
- 【User_Views 查看视图的定义】
视图是好东西，用好了提高开发的效率和安全性，反之数据库的性能极大的下降，数据库是给明白人用的。物有所长，必有所短，万物一理，视图也不例外。Oracle 的所有字典都是视图。极大的提高了管理性，极大的提高了可使用性，我们学习 oracle,就要跟 oracle 学习,他怎么玩，我们就怎么玩，肯定可以成为数据库大师
- 【使用视图的目的】
1、限制对数据库的访问
2、将复杂的查询包起来，化繁为简
3、提供给用户独立的数据
4、在同一个表上建立不同的视图，减少基表的个数。
5、普通得视图下降数据库的性能，不能起到优化的目的，所以在使用视图关联查询的 时候一定要注意，可能使我们的系统性能隐含着巨大的隐患。我见到一个应用，表 面就 3 行的语句，客户说运行慢，一看执行计划，1 米多长，为什么，使用了视 图。表面简单，实际是个很复杂的查询。
4、 【建立、使用视图】
如果没有权限，请先授权。在 10G 以前 Scott 用户是有 Create view 的权限的，10g 以后就没有了，因为默认的角色的权限发生了变化。
SQL > Create view v1 as select deptno, min( sal ) salary
from emp group by deptno;
SQL > Desc v1;
Name Null? Type
deptno NUMBER(2)
salary NUMBER
其中 salary 是 min(sal)的别名，因为函数不能作为列的名称，列的别名的本质用法，将非法的合法化。
SQL > Select * from v1;
deptno salary
30 950
20 800
10 1300
视图 v1 只是一个定义，没有独立的数据，数据还是存放在 emp 表中。
SQL > Select VIEW_NAME, text from user_views 查看视图的定义
View_name text
V1 select deptno,min(sal) salary from emp group by deptno
- 【视图的使用】
1、你使用视图的时候可以把视图当做表。
2、数据库在解释查询语句的时候会查找视图的定义。
3、每次普通视图查询的时候都要进行基表数据的查找。
4、视图中不存放数据，除了物化视图，物化视图是快照，真正的存放数据，需要刷新。
6、【视图的执行过程】
SQL > Select * from v1;
查找 user_views 取出 v1 视图的定义。
select deptno, min( sal ) salary from emp group by deptno;
【点评】：数据库执行查找到的定义
所以说视图下降数据库的性能，尤其在复杂的视图相互关联查询的时候，你觉得语法很简单，其实视图内调用了大量的表，容易使我们麻痹大意
- 【修改视图】
SQL > Create or replace view v1 as select * from dept;
【点评】：就是将视图的定义替换。
- 【删除视图】
SQL > Drop view v1;
【点评】：在数据字典中将视图的定义清除，不能影响基本表中的数据
- 【视图上运行DML】
简单的视图可以运行 dml ，等于直接操作基本表的数据，但受到一些限制。
9.1、【Delete的限制】
1、有组函数
2、有 group by 子句
3、用了 distinct 关键字
4、有 rownum 列
Delete v1 where deptno=10;
ORA-01732: 此视图的数据操纵操作非法
9.2、【Update的限制】
1、有组函数
2、有 group by 子句
3、用了 distinct 关键字
4、有 rownum 列
5、有表达式的列
9.3、【Insert的限制】
1、有组函数
2、有 group by 子句
3、用了 distinct 关键字
4、有 rownum 列
5、有表达式的列
6、基本表中有 not null 的列，但该列没有出现在视图的定义里
10、【With Check Option选项】
SQL > Create or replace view empvu20 as select * from emp
where deptno = 20 with check option constraint empvu20_ck
建立视图的时候带有检测约束，约束就是 where 的条件。确保在 update 视图的时候，视图所选择的行不会发生变化，是对视图中数据的一种保障。
Update empvu20 set deptno=10;
【ORA-01402】：视图 with check option where 子句违规
- 【Read Only 选项】
SQL > Create or replace view empvu10 (employee_number, employee_name, job_title) As select empno, ename, job from emp where deptno = 10 with read only;
禁止 dml 操作视图
- 【Inline内嵌式视图】：将 from 子句中的子查询起别名
SQL > select ename, sal from emp,
(select deptno, avg( sal ) salary from emp group by deptno) a
where emp.deptno = a.deptno and emp.sal > a.salary;
【解释】：a 就是内嵌式视图，当前语句内起作用，一次性的，在语句外不可引用。
13、【Top-n 查询】：查询工资前三名
SQL > Select rownum as renk, ename, sal deom
(select ename, sal from emp order by sal desc) where rownum <= 3
【解释】：From 子句后为视图，内
- 【rownum 伪列】
按照取出数据的顺序显示,我们使用了 rownum 伪列。表 EMP 中并没有 ROWNUM 列，这是数据库计算出来的，按照数据库取出的顺序来决定顺序是几，所以一定要使用小于等于，而不能使用大于等于
【【 知识点总结 】】
1、掌握视图的原理
2、建立简单的视图
3、在视图上进行 dml
4、内嵌式视图
5、查看视图的定义
6、删除视图
实验23：查询结果的集合操作
【【 集合操作包括 】】
1、并集 union /union all
2、交集 intersect
3、补集 minus
1、【建立实验表】
drop table t2 purge;
drop table t1 purge;
Create table t1 as select deptno, ename, sal from emp
Where deptno in ( 10, 20 ) order by deptno;
Create table t2 as select deptno, ename, sal from emp
Where deptno in ( 20, 30 ) order by deptno;
SQL > select * from t1;
Deptno ename sal
10 CLARK 2450
10 KING 5000
10 MILLER 1300
20 FORD 3000
20 JONES 2975
20 SMITH 800
SQL > select * from t1;
Deptno ename sal
20 FORD 3000
20 JONES 2975
20 SMITH 800
30 TURNER 1500
30 JAMES 950
30 BLAKE 2850
30 WARD 1250
30 ALLEN 1600
30 MARTIN 1250
这两张表中既有相同部分又有不同部分
2、【Union All】
SQL > select * from t1 union all select * from t2;
Deptno ename sal
10 CLARK 2450
10 KING 5000
10 MILLER 1300
20 FORD 3000
20 JONES 2975
20 SMITH 800
20 FORD 3000
20 JONES 2975
20 SMITH 800
30 TURNER 1500
30 JAMES 950
30 BLAKE 2850
30 WARD 1250
30 ALLEN 1600
30 MARTIN 1250
注释：将 t1 和 t2 的结果一起显示。Union All不排序操作也不去掉重复的行
- 【Union】
SQL > select * from t1 union select * from t2;
Deptno ename sal
10 CLARK 2450
10 KING 5000
10 MILLER 1300
20 FORD 3000
20 JONES 2975
20 SMITH 800
30 ALLEN 1600
30 BLAKE 2850
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
30 WARD 1250
注释：将 t1 和 t2 的结果一起显示。Union含有排序操作也去掉重复的行。
- 【Intersect】
SQL > select * from t1 intersect select * from t2;
Deptno ename sal
20 FORD 3000
20 JONES 2975
20 SMITH 800
注释：将 t1 和 t2 的结果的共有部分显示。Intersect含有排序操作也去掉重复的行
- 【Minus】
SQL > select * from t1 minus select * from t2;
Deptno ename sal
10 CLARK 2450
10 KING 5000
10 MILLER 1300
注释：t1 表有，而且 t2 表没有的行显示出来，去掉重复的行
【【 总结 】】
在 10g 以前，去掉重复行的操作是使用排序，10g 以后使用 hash 算法，避免了排序。提高了性能，所以结果集是无序的，如果需要有序，使用 order by 语句。10g 以前结果是有序的。
实验24：高级分组Rollup，Cube的用法
【解释】：比普通分组在 Union 要高效，一次扫描干多样的事情，搂草打兔子，一次全部完成了，
- 【Rollup】分组
按部门分组 SQL > select deptno, sum ( sal ) from emp group by deptno;
Deptno sum(sal)
30 9400
20 6775
10 8750
按部门分组求和 SQL > select deptno, sum(sal) from emp group by rollup(deptno);
Deptno sum(sal)
30 9400
20 6775
10 8750
24925
Rollup 分组，一次全表扫描
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
select deptno, sum ( sal ) from emp group by rollup ( deptno ) ;
上面这条SQL可以分解为：
SQL > Select deptno , sum ( sal ) from emp group by deptno union all
Select null , sum ( sal ) from emp order by 1 ;
但是两次扫描表，效率很低
【【 总结 】】
Group by Rollup ( a, b, c, d.......)
的结果集为，共 n+1 个集
Group by a, b, c, d Union all
Group by a, b, c Union all
Group by a, b Union all
Group by a Union all
Group by null;
【【 根据总结举例子 】】
SQL > select deptno, job, sum ( sal ) from emp group by rollup ( deptno, job );
Deptno job sum(sal)
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 800
20 ANALYST 3000
20 MANAGER 2975
20 6775
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
24925
结果为：
SQL > Select deptno, job, sum ( sal ) from emp group by deptno, job
union all
select deptno, null, sum ( sal ) from emp group by deptno
union all
Select null, null, sum ( sal ) from emp;
- 【Grouping】
Grouping(列名称)的使用，为了表达该列是否参加了分组活动。 0 为该列参加了分组，1 为该列未参加分组操作
SQL > Select deptno , job , grouping ( deptno ), grouping ( job ) ,sum ( sal )
from emp group by rollup ( deptno , job ) ;
Deptno job grouping(deptno) grouping(job) sun(sal)
10 CLERK 0 0 1300
10 MANAGER 0 0 2450
10 PRESIDENT 0 0 5000
10 0 1 8750
20 CLERK 0 0 800
20 ANALYST 0 0 3000
20 MANAGER 0 0 2975
20 0 1 6775
30 CLERK 0 0 950
30 MANAGER 0 0 2850
30 SALESMAN 0 0 5600
30 0 1 9400
1 1 24925
- 【Cube分组】
SQL > Select deptno ,job ,grouping ( deptno ),
Grouping ( job ) , sum ( sal ) from emp group by cube ( deptno, job );
结果集拆分为 2**n 个结果集
SQL > select deptno ,job ,sum ( sal ) from emp group by deptno ,job
union all
select deptno ,null ,sum ( sal ) from emp group by deptno
union all
select null ,job ,sum ( sal ) from emp group by job
union all
Select null , null , sum ( sal ) from emp;
实验25：树结构的查询start with 语句
【注释】：实现高级的查询需要，减少了编程序！ 层次结构的数据查询
SQL> select empno, ename, mgr from emp start with ( ename = 'SMITH' )
Connect by prior mgr = empno;
empno ename mgr
7369 SMITH 7902
7902 FORD 7566
7566 JONES 7839
7839 KING
- 【伪列level】
SQL > select level, empno, ename, mgr from emp start with (ename = 'SMITH')
Connect by prior mgr = empno;
level empno ename mgr
1 7369 SMITH 7902
2 7902 FORD 7566
3 7566 JONES 7839
4 7839 KING
- 【从树根开始查询】
SQL > select level, empno, ename, mgr from emp start with (ename = 'KING')
Connect by prior empno = mgr;
level empno ename mgr
1 7839 KING
2 7566 JONES 7839
3 7902 FORD 7566
4 7369 SMITH 7902
2 7698 BLAKE 7839
3 7499 ALLEN 7698
3 7521 WARD 7698
3 7654 MARTIN 7698
3 7844 TURNER 7698
3 7900 JAMES 7698
2 7782 CLARK 7839
- 【美化层次的关系】
SQL> select lpad ('-', level, '-') || ename from emp start with ( ename = 'KING' )
Connect by prior empno = mgr;
lpad ('-', level, '-') || ename
-KING
--JONES
---FORD
----SMITH
--BLAKE
---ALLEN
---WARD
---MARTIN
---TURNER
---JAMES
--CLARK
---MILLER
- 【删除节点、下级保留】
SQL> select level, empno, ename, mgr from emp where ename <> 'BLAKE'
start with ( ename = 'KING' ) connect by prior empno = mgr;
BLAKE 一个人删除，不影响他的下属
- 【删除枝干】
SQL> select level, empno, ename, mgr from emp start with ( ename = 'KING')
connect by prior empno = mgr and ename < > 'BLAKE' ;
BLAKE 和他的整个部门
实验26：高级DML操作 → insert的进一步学习
1、【建立实验表】
SQL> Drop table e1 purge;
Drop table e2 purge;
create table e1 as select ename, sal, hiredate from emp where 9 = 0;
create table e2 as select ename, sal, hiredate from emp where 9 = 0;
2、【Insert All】
SQL> insert all
into e1 values ( ename, sal, hiredate )
into e2 values (ename, deptno, mgr)
selec t ename, sal, hiredate, deptno, mgr from emp where deptno = 10;
select * from e1 ; --查询e1表 select * from e2 ; --查询e2表
【注释】：All 的含义为：emp 表中的一行将插入到 e1,e2 中
- 【Insert First】
SQL> insert first
when sal > 3000 then into e1 values ( ename, sal, hiredate )
when sal > 2000 then into e2 values ( ename, deptno, mgr )
Select ename, sal, hiredate, deptno, mgr from emp ;
【注释】：First 的含义为：一行只能给一张表，即使两个表的条件都符合
实验27：高级子查询
普通的子查询是先运行子查询，再运行外面的查询。有的时候我们需要把外部的查询结果先传入到内部，再运行子查询，子查询的结果再来判断外部的查询。
*-*-*-*-*-*-*-*-*-*-* 举个例子 *-*-*-*-*-*-*-*-*
查询哪些员工的工资高于本部门的平均工资
1、【第一种】：
SQL> select a.deptno, ename, sal from emp a, (select deptno , avg(sal) asal
from emp group by deptno ) b
where a.deptno = b.deptno and a.sal > b.asal order by 1;
【执行结果】：
deptno ename sal
10 KING 5000
20 JONES 2975
20 FORD 3000
30 ALLEN 1600
30 BLAKE 2850
【解释SQL】：
其中 b 为内嵌式视图，asal 为表达式的别名，因为表达式不能做列的名称。别名的含义在于非法的列名合法化。
- 【第二种】：
SQL> select deptno, ename, sal from emp a
where a.sal > (select avg (sal) from emp where deptno = a.deptno)
order by 1;
【执行结果】：
deptno ename sal
10 KING 5000
20 JONES 2975
20 FORD 3000
30 ALLEN 1600
30 BLAKE 2850
【解释SQL】：
以上两种SQL，第一个为内嵌式的视图，第二个为互动的子查询，两句话的结果完全相同，但内部运行的模式不同