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_emp
as 
select empno,ename,job,hiredate,deptno 
from scott.emp;

  截图：

 

 （2）查看视图的结构。

  语句：SQL> desc v_emp;

   截图：

 

 （3）从视图中查询数据。

  语句：SQL>  select * from v_emp where deptno=10;

  截图：

 

 （4）查看视图中各字段的可更新性。

  语句：select * from USER_UPDATABLE_COLUMNS  where table_name='V_EMP';

  截图：

 

 

 （5）向视图中增加新数据。

  语句：insert into v_emp values(1234,'JACK','CLERK','29-4月-1963',10);

  截图：

 

 （6）利用视图修改数据。

  语句：update v_emp set ename='Mark' where empno=1234;

  截图：

 

 （7）利用视图删除数据。

 语句：delete from v_emp where empno=1234;

 截图：

 

  3.利用Scott.emp 创建只读视图。

语句：

create or replace VIEW v_emp_readonly
 as
 select empno,ename,job,hiredate,deptno 
 from scott.emp
 with read only;

截图：

 

对视图执行删除操作，测试视图的只读性：

语句：delete from v_emp_readonly where empno=7782;

截图：

 

  4.利用Scott.emp创建具有With check option 选项的检查视图。

语句：

create or replace VIEW v_emp_check
as 
select empno,ename,job,hiredate,deptno
from scott.emp
where deptno=10
with check option;

截图：

 

创建检查视图，通过视图对数据源表执行插入和修改操作。

语句：

insert into v_emp_check
values(1235,'JACK','CLERK','29-4月-1963',20);

截图：

 

部门编号20 不符合Where 条件，插入失败。

语句：update  v_emp_check set deptno=20 where deptno=10;

截图：

 

修改后的新数据不符合where 条件，修改失败。

 

5.利用Scott方案下的emp表和Dept表创建连接视图，查询部门编号为10和30的部门及雇员信息。

创建视图：

语句：

create or replace VIEW v_dept_emp
as
select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal
from scott.dept a,scott.emp b 
where a.deptno=b.deptno and a.deptno in (10,30);

截图：

 

利用视图查询表中的数据：

 

查看视图中个字段的可更新性：

语句：select * from USER_UPDATABLE_COLUMNS where table_name='V_DEPT_EMP';

截图：

 

 

（习题）

 1.简述索引有哪些类型，并说明什么情况下适合建立反向键索引，什么情况下适合建立位图索引。

答：常见的索引类型包括B树索引﹑位图索引﹑反向键索引﹑基于函数的索引﹑全局索引和局部索引等。 反向键索引是一种特殊类型的B树索引，特别适合基于有序数列建立的索引。

当列的值是可以枚举（也就是说列的值差异性较小）的时候适合建立位图索引。

 

 2.简述视图的概念以及利用视图操作数据的优点。

答：

视图的概念：视图是一个虚拟表，其内容由查询定义。 同真实的表一样，视图包含一系列带有名称的列和行数据，用户可以像使用普通表一样对视图执行各种DML操作，如SELECT、insert、update、delete。但是，视图并不在数据库中真正存储有数据，它的数据来自于定义视图的查询所引用的表，而且这些数据是在使用视图时动态生成的。因此视图在数据库中只对应着一个SELECT语句的定义，可以从一个表或多个表中查询。对视图的各种操作实际上是对SELECT语句中数据源表的操作，当数据源中的数据发生变化时，视图的查询结果也会发生变化。

利用视图操作数据的优点：

（1）简化数据操作

包含的数据较少

将对多个表的操作简化为对一个视图的操作

（2）增强数据的安全性

可以将安全性控制到任意的数据子集

（3）定制数据

不同的级别的用户可以看到不同用途的数据集

（4）合并与分割数据

（5）利用视图修改源表

 

 3.简述可更新视图应具有哪些特点。

答：

（1）创建视图时不能选择WITH READ ONLY选项。

（2）视图中的非计算列或非聚合运算，即数据源表中的原始字段，才可以被更新。

（3）视图的定义中SELECT语句不能包含DISTINCT关键字。

（4）视图的定义中SELECT语句不能包含集合操作如，UNION、INTERSECT等。

（5）视图的定义中SELECT语句不能包含GROUP BY子句和HAVING子句。

（6）用户必须对视图的数据源表具有显示的操作权限才可以。

（7）只有在视图中可见的行和列才可能被修改或删除。

 

 4.操作题：

  （1）建立一个表myEMP ，表结构和表中数据与scott.emp相同。

语句：

create table myEMP
 as 
 select * from scott.emp;

截图：

 

  （2）在myEMP表中建立基于字段empno的唯一索引。

语句：create unique index index_empno on myEMP(empno);

截图：

 

  （3）建立一个视图myV_emp，视图包括myEMP表中的empno、ename、sal，并按sal从大到小排列。

语句：

Create or replace VIEW myV_emp
As
Select empno,ename,sal
From myEMP
Order by sal desc;、

截图：

 

 

（实验）

1.使用新建序列的值，为新表填充字段值。

（1）创建一张新表。

 

（2）创建序列id_seq。

 

（3）将序列的值插入到新表中。

 

 

2.以scott用户登录数据库，并在数据库中创建emp表的同义词s_e。

（1）以system 用户登录后授予scott用户创建同义词的权限。

 

 

（2）以scott用户登录，创建公有同义词。

 

（3）以System用户登录，利用同义词来操作原表。

 

 

 

（P199 10.4 习题）

1.简述同义词和序列的概念。

答：

同义词的概念：

同义词是表、索引、视图等方案对象的一个别名，不占据任何实际的存储空间，只在数据字典中保存其定义。

 

序列的概念：

序列可在当前方案下产生一系列唯一数字，可以用这些数字产生表的主键值，也可以参与其他运算。序列也可以在多用户并发环境中使用，为所有用户生成不重复的顺序数字，而且不需要任何额外的I/O开销。

 

2.简述使用同义词的好处。

答：同义词可以简化原数据库对象的名称，方面用户对数据库对象的引用。

 

3.序列常用的两个运算符是什么？各代表什么意义？

答：序列常用的两个运算符是NEXTVAL和CURRVAL。NEXTVAL将返回序列生成的下一个值，而CURRVAL将返回序列的当前值。第一次应用序列时，需要使用NEXTVAL，返回的是初始值。而以后再使用NEXTVAL运算符时，会使序列自动增加INCREMENT BY后面定义的值。

 

 

create or replace VIEW v_emp_check

as

select empno,ename,job,hiredate,deptno

from scott.emp

where deptno=10

with check option;