Sql_Lab4 视图、序列、同义词和索引

第13讲使用视图、索引、序列和同义词对象1、使用视图（view）视图是基于其他表或其他视图的逻辑表。

视图的作用：（1）限制数据访问，访问视图时只能访问select语句所涉及到的列。

（2）简化复杂查询，如果经常需要在多个表之间执行复杂查询操作，可以基于该复杂查询语句建立视图。

视图分类：（1）简单视图，基于单个表所建视图，不包含任何函数、表达式及分组数据的视图。

（2）复杂视图，包含函数、表达式或者分组数据的视图。

（3）连接视图，基于多表所建立的视图。

（4）只读视图，至允许执行查询操作。

在视图上执行DML操作的原则：（1）DELETE操作原则：如果视图包含有group by子句、分组函数、distinct关键字和rownum 伪列，那么不能在该视图上执行delete操作。

（2）UPDATE操作原则：如果视图包含有group by子句、分组函数、distinct关键字和rownum伪列，以及使用表达式所定义的列，那么不能在该视图上执行update操作。

（3）INSERT操作原则：如果视图包含有group by子句、分组函数、distinct关键字和rownum 伪列，以及使用表达式所定义的列，或者在视图上没有包含视图基表的not null列，那么不能在该视图上执行insert操作。

使用system帐号授予scott账户create view的权限。

SQL> grant create view to scott;1.1 建立视图（1）建立简单的视图例：建立视图vu_emp，包含emp表的empno,ename,sal列create view vu_emp asselect empno,ename,sal from emp;查看视图列SQL> desc vu_emp;Name Type Nullable Default Comments----- ------------ -------- ------- --------EMPNO NUMBER(4)ENAME VARCHAR2(10) YSAL NUMBER(7,2) Y使用数据字典user_views查看用户视图SQL> select view_name from user_views;VIEW_NAME------------------------------VU_EMP查看视图文本：SQL> select text from user_views where view_name=upper('vu_emp');对简单视图的增、删、改操作SQL>insert into vu_emp (empno,ename,sal) values (1000,'郭永洪',1500);SQL> update vu_emp set sal=3000 where empno=1000;SQL> delete from vu_emp where empno=1000;视图本身没有任何数据，视图上的增、删、改、查操作都是针对视图基表来完成的。

常用的Oracle数据库对象一、数据库对象简介1、Oracle数据库对象又称模式对象2、数据库对象是逻辑结构的集合，最基本的数据库对象是表3、其他数据库对象包括：二、同义词1、同义词是现有对象的一个别名●简化SQL语句●隐藏对象的名称和所有者●提供对对象的公共访问2、同义词公用两种类型●公有同义词可被所有的数据库用户访问●私有同义词只能在其模式内访问，且不能与当前模式的对象同名●创建一个名为emp同义词，来自scott.emp表，查询的时候就可以直接用同义词，而不用加模式名（这个是私用同义词，只能针对当前用户）●创建一个公用的同义词，当前用户可以查询●其他用户也可以查询●替换和删除同义词（包括共有和私有）三、序列1、序列是用于生成唯一、连续序号的对象2、序列可以是升序的，也可以是降序的3、使用create sequence 语句创建序列4、序列的访问，通过序列的伪列来访问序列的值●Nextval返回序列的下一个值（用来实现排序序列名.nextval）●Currval返回序列的当前值（用来查看当前序列值序列名.currval）●向表中插入序列toys_seq.nextval●查看序列●查看序列的当前值●修改序列●查看修改后的序列，可以插入的值●删除序列四、视图1、视图以经过制定的方式显示来自一个或多个表的数据2、视图可以视为‘虚拟表’或‘存储的查询’3、创建视图所依据的表称为‘基表’4、视图的优点有：●提供了另外一种级别的表安全性●隐藏的数据的复杂性●简化的用户的SQL命令●隔离基表结构的改变●通过重命名列，从另一个角度提供数据5、创建视图●创建表，并插入数据●创建视图并查看视图●运用order by子句创建视图●创建带有错误的视图●在创建一个表，用于多表连接的视图6、在视图上也可以使用修改数据的DML语句，如：insert update和delete7、视图上的DML语句有如下限制：●只能修改一个底层的基表●如果修改违反了基表的约束条件，则无法更新视图●如果视图包含连接操作符、distinct关键字、集合操作符、聚合操作符或group by子句，则将无法更新视图●如果视图包含位列或表达式，则将无法更新视图8、键保留表●因为studno既是stud_details中的主键，也是连接结果的主键8、视图中的函数●视图中可以使用单行函数、分组函数和表达式●使用drop view语句删除视图五、索引1、索引是与表相关的一个可选结构2、用以提高SQL语句执行的性能3、减少磁盘I/O4、使用create index语句创建索引5、在逻辑上和物理上都独立于表的数据6、Oracle自动维护索引7、索引有各种类型，除了标准索引外，还有一些特殊烈性的索引：8、创建标准索引●创建、更改和删除索引9、唯一索引●唯一索引确保在定义索引的列中没有重复值●Oracle自动在表中的主键列上创建唯一索引●使用create unique index语句创建唯一索引10、组合索引●组合索引是在表的多个列上创建的索引●索引中列的顺序是任意的●如果SQL语句的where子句引用了组合索引的所有累或大多数列，则可以提高检索速度●组合emp2表中的empno和ename列创建索引11、反向键索引●反向键索引反转索引列键值的每个字节●通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上●创建索引时使用reverse关键字12、位图索引●位图索引适合创建在低基数列上●位图索引不直接存储rowID，而是存储字节位rowID的映射●减少响应时间●节省空间占用13、索引组织表●索引组织表的数据存储在与其关联的索引上●索引中存储的时行的实际数据，而不是rowID●基于主键访问数据●Create table命令与organization index子句一起用于创建索引组织表●普通表与索引表的比较14、基于函数的索引●基于一个或多个列上的函数或表达式创建的索引●表达式中不能出现聚合函数●不能在LOB类型的列上创建●创建必须具有query rewriter权限六、总结1、同义词时现有数据库对象的别名2、序列用于生成唯一、连续的序号3、视图时基于一个或多个表的虚拟表4、索引时与表相关的一个可选结构，用于提高SQL语句执行的性能5、索引类型有标准索引、唯一索引、反向键索引、位图索引和基于函数的索引6、索引组织基于主键访问数据。

Oracle数据库——索引、视图、序列和同义词的创建⼀、涉及内容 1.理解索引的概念和类型。

2.掌握创建索引的命令。

3.理解视图的概念和优点。

4.理解可更新视图应具备的特点。

5.掌握创建⼀般视图和可更新视图的命令。

6.理解序列和同义词的概念和作⽤。

7.掌握序列的创建与应⽤。

8.掌握同义词的创建与应⽤。

⼆、具体操作（实验）1.在数据库中创建Student表，包括学号Id、姓名Name、性别Sex、班级编号Class_id。

利⽤该表创建如下索引：（1）在Id字段上创建唯⼀的B树索引。

（2）在Name字段上创建普通的B树索引。

（3）在Sex 字段上创建位图索引。

语句：--建表：create table Student(Id char(6),Name varchar2(8),Sex char(2),Class_id char(4));create unique index index_id on Student(Id)；create index index_name on Student(Name);create bitmap index index_sex on Student(Sex);截图：2.利⽤scott.emp 表创建视图并完成以下操作：（1）创建简单视图。

语句：conn scott/tiger;grant insert,update,delete on emp to system;conn system/orcl1234;create or replace VIEW v_empasselect empno,ename,job,hiredate,deptnofrom scott.emp;截图：（2）查看视图的结构。

语句：SQL> desc v_emp;截图：（3）从视图中查询数据。

语句：SQL> select * from v_emp where deptno=10;截图：（4）查看视图中各字段的可更新性。

实验七索引-视图-序列-同义词实验目的：本次实验旨在使学生正确理解数据库模式对象：索引、视图、序列、同义词的定义、作用和管理的操作。

实验要求:创建序列并掌握序列的正确使用方法。

为表创建索引并与没有创建索引的表的查询效率做比较。

创建一个基于单表和一个基于两个表的视图，并比较两个视图在做DML操作时的区别。

创建同义词并理解在编程中使用同义词的优点。

实验环境：Oracle 10g实验步骤：首先使用scott帐户登录数据库：创建一个序列，开始值是1，增量值是1，最大值是99999，达到最大值之后可以重复，使用10个序列预分配。

创建一个没有索引的表，并做insert的操作，该操作要使用序列创建一个基于emp的视图v_emp，该视图只包含empno和ename属性，然后对v_emp进行DML的操作。

创建一个基于emp和dept表的视图，该视图包含empno、ename和dname(员工所在部门名称)，并对该视图进行DML的操作。

创建一个同义词并使用它。

Create SEQUENCE seq_teststart with 1increment by 1maxvalue 99999cyclecache 10;-- 使用序列要使用的语句是：seq_test.nextval-- 检测序列的当前值得语句是：seq_test.currval创建一个没有索引的表：create table noindex(id number(5),name char(13) default 'software dept',day date default (sysdate) )使用循环插入100000记录DECLAREseq_start NUMBER :=1;BEGINWHILE seq_start < 100000LOOPinsert into noindex(id) values(seq_test.nextval);seq_start := seq_start + 1;END LOOP;END;/创建一个有b树索引的表：create table bindex(id number(5) primary key,name char(13) default 'software dept',day date default (sysdate) )使用循环插入100000记录DECLAREseq_start NUMBER :=1;BEGINWHILE seq_start < 100000LOOPinsert into bindex(id) values(seq_test.nextval);seq_start := seq_start + 1;END LOOP;END;/执行计划查询:对无索引的表进行查询：explain plan forselect * from noindex where id = 10000查看执行计划select * from table(dbms_xplan.display())主要是查看cpu开销和执行时间对有索引的表进行同样的查询，然后再查看执行计划，比较它们之间的cpu开销和执行时间create view v_empasselect empno, ename from emp;对视图v_emp进行DML操作：insert into v_emp values (2000,'cns');select * from v_emp where empno = 2000;update v_emp set ename = 'chen' where empno = 2000;create view v_emp_deptasselect e.empno,e.ename,d.dname from emp einner join dept don e.deptno = d.deptno;对视图v_emp_dept进行DML操作，有什么错误发生？Create synonym syn_deptfor dept;select * from syn_dept;。