Oralce中的其他对象.doc

Oralce 中的其他对象：l 视图：n 概念：视图是从一个或几个实体表（或视图）导出的表，它与实体表不同，视图本身是一个不包含任何真实的数据的虚拟表，数据库中只存放视图的定义，而不存放视图对应的数据，这些数据任然存放在原来的实体表中，从这个意义上讲，视图就像一个窗口，通过它可以看到数据库中自己感兴趣的数据及其变化：u 案例：1：创建一张基本工资大于2000的雇员信息的视图：SQL create or replace view v_emp 2 as 3 select empno,ename,sal,hiredate from t_emp 4 where 5 sal 2000;视图已创建。Create or replace 表示如果有这张视图则替换，没有则创建：2：就可通过视图来直接查询结果：视图就相当于给某个查询结果定义一个别名：SQL select *from v_emp; EMPNO ENAME SAL HIREDATE- - - - 7566 JONES 2975 02-4月 -81 7698 BLAKE 2850 01-5月 -81 7782 CLARK 2450 09-6月 -81 7788 SCOTT 3000 19-4月 -87 7839 KING 5000 17-11月-81 7902 FORD 3000 03-12月-81已选择6行。n 但是在Oraclette10g Ra这个版本之后，默认情况下scott用户没有创建视图的权限：所以要进行如下操作：Conn sys/change_no_install as sysdba;Grant create view to c#scott;n 现在只要查询视图就可以实现与被封装的sql语句同样的功能：n 在Oracle中，针对视图也提供一个数据字典：user_views;n 视图也相当于一张表：u 案例：2：创建一张只包含20部门雇员信息的视图：SQL create or replace view v_emp 2 as 3 select empno,ename,sal,deptno from t_emp 4 where 5 deptno = 20;视图已创建。然后再进行查询：SQL select *from v_emp; EMPNO ENAME SAL DEPTNO- - - - 7369 SMITH 800 20 7566 JONES 2975 20 7788 SCOTT 3000 20 7876 ADAMS 1100 20 7902 FORD 3000 20n 当然视图也可以进行增，删，改，查，但不推荐进行对视图数据的更改：n 通过视图实现了查询的方便：创建视图的时候注意使用别名：create or replace view v_empasselectd.deptno部门编号,d.dname部门名称,d.loc部门位置,count(e.ename)总人数,avg(e.sal)平均工资,max(e.sal)最高工资,min(e.sal)最低工资from t_dept d,t_emp ewheree.deptno(+) = d.deptnogroup by d.deptno,d.dname,d.loc;进行查询：SQL select *from v_emp; 部门编号 部门名称 部门位置 总人数 平均工资 最高工资- - - - - - 最低工资- 20 RESEARCH DALLAS 5 2175 3000 800 40 OPERATIONS BOSTON 0 10 ACCOUNTING NEW YORK 3 2916.66667 5000 1300 部门编号 部门名称 部门位置 总人数 平均工资 最高工资- - - - - - 最低工资- 30 SALES CHICAGO 6 1566.66667 2850 950n 还有要注意的是：u 无法通过视图修改多个基表：u 也无法更改经过合计后的数据：u 只能更改基本数据：因此不推荐：u With check option 字句：在创建视图的时候需要使用一些where 字句做一些条件的的限制，但是默认情况下的视图创建完成之后，是可以通过视图去修改在where字句之中的字段内容的，而在此时就需要通过with check option字句来保证视图的创建条件不被更新SQL create or replace view v_emp 2 as 3 select *from t_emp where deptno = 20 4 with check option constraint v_emp_vk;SQL update v_emp set deptno =40 where deptno = 20;update v_emp set deptno =40 where deptno = 20第 1 行出现错误:ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规 需要注意的是，虽然使用了with check option 后在where后的条件不能改变了，但是其他字段仍然可以改变：但是视图不是真实数据，它属于映射数据，应该在基表之中更新才合适：u With read only：u 让视图中所有字段不可更新，则可以通过with read only 字句控制：u 案例：create or replace view v_empasselect *from t_emp where deptno = 30with read only;SQL update v_emp set deptno = 20 where deptno = 30;update v_emp set deptno = 20 where deptno = 30 *第 1 行出现错误:ORA-01733: 此处不允许虚拟列u 删除视图：l Drop view 视图名称：l 序列：（sequence）n 概念：在许多数据库之中都会为用户提供一个钟自动增长列的操作，也就是说序列可以自动的按照既定的规则实现数据的编号操作：n 完整序列的创建语法：Create sequence 序列名称： increment by 步长 start with 开始值 maxvalue 最大值| no maxvalue cycle | nocycle cache 缓存大小 | nocachen 默认序列：u 最小值 0 最大值是 无限大u Nextval:获取下一个值，（next value）u Currval:获取当前一个值（current value）u Cache保存的是一个缓存的个数，而不是一个值：u 创建时得注意：CACHE 值必须小于 CYCLE 值u CACHE默认的缓存个数是20 ；cycle表示 表示循环的种子数的个数，例如从1开始，步长为2，最大值是9，则cycle表示的值为5（1,3,5,7,9）因此cache create sequence mysequcnce4 2 start with 1 3 increment by 5 4 maxvalue 21 5 cycle 6 cache 4;序列已创建。从上面的序列可以看出cycle为5（1,6,11,16,21），cache为4：满足cache select empno,ename,sal, rownum from t_emp; EMPNO ENAME SAL ROWNUM- - - - 7369 SMITH 800 1 7499 ALLEN 1600 2 7521 WARD 1250 3 7566 JONES 2975 4 7654 MARTIN 1250 5 7698 BLAKE 2850 6 7782 CLARK 2450 7 7788 SCOTT 3000 8 7839 KING 5000 9 7844 TURNER 1500 10 7876 ADAMS 1100 11 EMPNO ENAME SAL ROWNUM- - - - 7900 JAMES 950 12 7902 FORD 3000 13 7934 MILLER 1300 142：rowid的使用：SQL select empno,ename,sal,rowid from t_emp; EMPNO ENAME SAL ROWID- - - - 7369 SMITH 800 AAAM3jAAEAAAAGsAAA 7499 ALLEN 1600 AAAM3jAAEAAAAGsAAB 7521 WARD 1250 AAAM3jAAEAAAAGsAAC 7566 JONES 2975 AAAM3jAAEAAAAGsAAD 7654 MARTIN 1250 AAAM3jAAEAAAAGsAAE 7698 BLAKE 2850 AAAM3jAAEAAAAGsAAF 7782 CLARK 2450 AAAM3jAAEAAAAGsAAG 7788 SCOTT 3000 AAAM3jAAEAAAAGsAAH 7839 KING 5000 AAAM3jAAEAAAAGsAAI 7844 TURNER 1500 AAAM3jAAEAAAAGsAAJ 7876 ADAMS 1100 AAAM3jAAEAAAAGsAAK EMPNO ENAME SAL ROWID- - - - 7900 JAMES 950 AAAM3jAAEAAAAGsAAL 7902 FORD 3000 AAAM3jAAEAAAAGsAAM 7934 MILLER 1300 AAAM3jAAEAAAAGsAAN已选择14行。n 案例：删除添加在t_dept中的重复数据，只保留不重复数据：u 那么就要找到min(rowed)在t_dept中原数据为：添加后为：在现在的表中找min(rowed)这也就是最早的数据：删除重复的数据：u Min(rowed) ：查询是按什么字段分组就能得到不重复的字段，比如 deptno重复，但是查询时是按dname分组，如果dname 不重复就不会被删除，根据min(rowed)只可以删除重复在按什么字段分组的情况：l 案例1：在上面的情况中，deptno是重复的，但是查min（rowid）是根据dname查询的，那么只要dname没有重复，就会被显示出来，删除的时候也是一样，只要dname没有重复，尽管deptno有重复，但是查询min（rowid）没有按照deptno字段分组查询，也是不会被删除掉的：l 案例2：删除上面重复的行：分析：由于是因为deptno重复，删除时只能根据deptno分组：l 案例3：删除上面重复的字段：l 总之：可以根据min(rowed) 删除各字段上重复的字段：u Rownum(重点)：关系到分页查询：分页查询案例：第一次显示前5条，第二次显示5到第十条：2：也可以把显示伪列的当成是一张表，然后机型多表查询ul 索引：在数据库中索引是一种专门用于数据库查询操作性能的一种手段，在Oracle之中为了维护这种查询特性，需要对某一类数据进行指定结构的排列。但是在Oracle之中，针对于不同的情况会有不同的索引使用：n B树索引，降序索引，位图索引，函数索引：n 1：B树索引：（又为:B*Tree）是最为基本的索引姐都，在Oracle之中默认尽力的索引就是此类型的所以。一般B树在检索高基数数列（该列上的重复内容较少或是没有）的时候可以提供高性能的检索操作：u 案例1：查询工资大于1500的全部雇员：u 跟踪机制，也就是说要找到这个满足sal1500的雇员信息需要进行逐行扫描，如果信心多了就会影响程序的性能：Set autotrace on;(打开跟踪机制)B数索引：SQL create index emp_sal on t_emp(sal);索引已创建。-| 0 | SELECT ST