Oracle-5-2数据库对象(索引、视图、聚集、序列、同义词)

第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、索引组织基于主键访问数据。

1.创建表结构（员工医疗保险系统表）企业表：医院表：医保卡表：员工表：就诊表：消费表：医保表：2.查看表结构(1) 利用OEM查看员工医疗保险系统所有表的字段信息和约束信息。

(2) 利用SQL*Plus或iSQL*Plus从数据字典DBA_TAB _COLUMNS查看员工医疗保险系统所有表的字段信息。

(3) 利用SQL*Plus或iSQL*Plus从数据字典DBA_ CONSTRAINTS查看员工医疗保险系统所有表的约束信息。

SELECT * FROM DBA_TABLES WHERE TABLE_NAME= 'STAFF';3.修改表结构(1) 利用SQL*Plus或iSQL*Plus将表“staff_sql”重新命名为“staff_sql0”。

Alter table staff rename to staff_sql0;(2) 利用企业管理器为“staff_sql0”表添加“age INT”字段，利用DESC命令查看“staff_sql0”表的字段信息。

(3) 利用SQL*Plus或iSQL*Plus为“staff_sql0”表添加“salary NUMBER(5,2)”、“salary_add NUMBER(3,1)”两个字段，利用DESC命令查看“staff_sql0”表的字段信息。

(4) 利用SQL*Plus或iSQL*Plus向“staff_sql0”表添加“sname”字段惟一性约束，从数据字典DBA_CONSTRAINTS查看“staff_sql0”表的约束信息。

(5) 利用SQL*Plus或iSQL*Plus删除“staff_sql0”表上“sname”字段惟一性约束，从数据字典DBA_CONSTRAINTS查看“staff_sql0”表的约束信息。

(6) 利用企业管理器从“staff_sql0”表删除“age”字段，利用DESC命令查看“staff_sql0”表的字段信息。

同义词 synonymCREATE [PUBLIC]SYNONYM synonym For schema.object隐藏对象的名称和所有者:select count(*) from hr.employees;create synonym emp for hr.employees; --默认属于donny用户，是donny 的私有对象privateselect count(*) from emp;为分布式数据库的远程对象提供了位置透明性:访问其他数据库时，要首先建立数据库连结：CREATE DATABASE LINK test_link CONNECT TO username IDENTIFIED BY pass USING 'orabase';Select count(*) from hr.employees@test_link;create synonym link_emp for hr.employees@test_link;select count(*) from link_emp;提供对象的公共访问:create public synonym pub_emp for hr.employees;pub_emp属于public用户，数据库所有用户都可以访问。

同义词类型–私有 emp 实际上donny.emp–公用 pub_emp 所有用户都可以直接访问当公有对象和私有对象同名时(因为数据不同的用户，所以可以)，以私有对象优先。

(类似于局部变量)desc dba_synonyms/ user_synonyms/ all_synonyms 数据字典，复数tab公有同义词建立私有的tab表，查看效果。

删除同义词：drop synonym donny.emp;drop public synonym pub_emp;序列sequence：CREATE SEQUENCE donny.seq --也是属于某个用户的，以下参数均可省略，使用默认值。

Oracle基础必学知识点1. 数据库概念：Oracle是一种关系型数据库管理系统（RDBMS），用于存储和管理大量结构化数据。

它支持SQL语言，可以通过SQL语句进行数据查询、插入、更新和删除操作。

2. 数据库对象：Oracle数据库由多个对象组成，包括表、视图、索引、序列、存储过程等。

这些对象用于存储和处理数据，可以通过SQL语句进行操作。

3. 数据类型：Oracle支持多种数据类型，包括数字、字符、日期、大对象（LOB）等。

不同的数据类型用于存储不同类型的数据，可以根据需求选择合适的数据类型。

4. 表操作：在Oracle中，表用于存储数据。

可以使用CREATE TABLE语句创建表，使用INSERT语句插入数据，使用SELECT语句查询数据，使用UPDATE语句更新数据，使用DELETE语句删除数据。

5. 索引：索引是一种用于提高查询性能的数据结构。

在Oracle中，可以使用CREATE INDEX语句创建索引，通过索引可以快速定位到需要查询的数据，提高查询效率。

6. 数据约束：数据约束是用于保证数据的完整性和有效性的规则。

在Oracle中，可以使用约束来限制数据的取值范围、保证数据的唯一性等。

常见的约束类型包括主键约束、外键约束、唯一约束、非空约束等。

7. 视图：视图是一种虚拟表，它是从一个或多个表中获取数据的查询结果。

在Oracle中，可以使用CREATE VIEW语句创建视图，通过视图可以简化复杂的查询操作，提高数据的安全性。

8. 存储过程：存储过程是一组预先编译的SQL语句，存储在数据库中，并可以通过调用来执行。

在Oracle中，可以使用CREATE PROCEDURE语句创建存储过程，通过存储过程可以实现复杂的数据处理逻辑。

9. 事务控制：事务是一组逻辑操作，要么全部执行成功，要么全部回滚。

在Oracle中，可以使用BEGIN/END语句或者显式的事务语句（如COMMIT和ROLLBACK）来控制事务的提交或回滚。

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）查看视图中各字段的可更新性。

Oracle数据库基本操作（⼆）——视图、序列、索引、同义词⼀、视图（Views）与同义词 1、视图:实际上是对查询结果集的封装,视图本⾝不存储任何数据,所有的数据都存放在原来的表中; 在逻辑上可以把视图看作是⼀张表 2、作⽤:封装查询语句,简化复杂的查询需求屏蔽表中的细节 3、语法:create[or replace]view视图的名称as查询语句[with read only] 4、视图和同义词:屏蔽了查询过程步骤屏蔽了真实的表名增加了代码被破解的难度 5、⽰例：select*from emp;-- 封装成⼀个视图: 获取10号部门的员⼯信息create or replace view view_test1 as select*from emp where deptno=10;select*from view_test1;-- 简化查询语句CREATE VIEW view_test2 AS SELECTSUM(cc) total,SUM(CASE yy WHEN'1980'THEN cc END) "1980",SUM(CASE yy WHEN'1981'THEN cc END) "1981",SUM(CASE yy WHEN'1982'THEN cc END) "1982",SUM(CASE yy WHEN'1987'THEN cc END) "1987"FROM(SELECTTO_CHAR(HIREDATE, 'YYYY') YY,COUNT(*) CCFROMEMPGROUP BYTO_CHAR(HIREDATE, 'YYYY')) TT ;select*from view_test2;-- 屏蔽表中的细节create or replace view view_test3 as select ename,job,mgr,hiredate from emp;-- 通过视图修改数据update view_test3 set ename='SMITH'where ename='SMI%TH';-- 创建只读视图create view view_test4 as select ename,job,mgr,hiredate from emp with read only;-- 报错 ORA-01733: virtual column not allowed hereupdate view_test4 set ename='SMITH2'where ename='SMITH';-- 同义词: 相当于是取了⼀个别名create synonym yuangong for view_test3;select*from yuangong;⼆、序列 序列: 1,2,3,4,5,6,7.... 作⽤: 模拟类似mysql中auto_increment⾃动增长的编号 1、语法：create sequence 序列名称start with从⼏开始increment by每次递增多少minvalue | nominvaluemaxvalue | nomaxvaluecycle | nocyclecache 缓存⼏个数31,2,34,5,6-- currval:序列当前取到哪个值,必须是调⽤了⼀次nextval之后才能正常使⽤-- nextval:序列中的下⼀个值-- 注意:序列中的值,⼀旦被取过,⽆论回滚/发⽣异常,序列都是永不回头向下递增的 2、使⽤⽰例-- 1,2,3,4,5,6,1,2,3,4,5,6create sequence seq_test01start with1minvalue 1maxvalue 6cyclecache 3;-- 需先执⾏nextval语句后才有效select seq_test01.currval from dual;select seq_test01.nextval from dual;-- 开发过程,通常写法如下:create sequence seq_test02;-- 建表create table louceng(lou number primary key);-- 执⾏两次insert into louceng values(seq_test02.nextval);-- 1 2select seq_test02.currval from dual;select*from louceng;三、索引3.1 索引概述 索引:索引是⼀种已经排好序的帮助数据库快速查找数据的数据结构,主要是⽤来帮助数据库快速的找到数据相当于⼀本书的⽬录。

oracle同义词概念及其作用同义词在Oracle数据库中起到了很重要的作用。

它能够提供更加灵活的数据访问方式，方便了数据库的管理和查询。

本文将详细介绍Oracle同义词的概念以及其作用。

一、同义词概念同义词是指在数据库中为一个或多个对象（如表、视图、序列等）起一个别名，也就是给它们取一个更加直观、易记的名字。

同义词是数据库对象的另一种名字，通过使用同义词，我们可以在数据库中以不同的名字来访问相同的对象。

在Oracle数据库中创建同义词非常简单，只需要使用CREATE SYNONYM语句并指定别名即可。

例如，我们可以通过以下语句创建一个名为"EMPLOYEE"的同义词，指向真正的"EMPLOYEE_INFO"表：CREATE SYNONYM EMPLOYEE FOR EMPLOYEE_INFO;二、同义词的作用1.简化表名同义词的一个主要作用是简化表名的访问。

在复杂的数据库结构中，表名可能会非常长，记忆和输入起来都比较麻烦。

通过使用同义词，我们可以将复杂的表名简化成一个易于记忆和书写的名字，提高数据库管理的效率。

2.提高安全性同义词可以起到一定的安全作用。

通过设置权限，我们可以控制用户对同义词的访问权限，而不是直接对数据库中的对象进行授权。

这样一来，就可以更好地保护敏感数据，提高数据库的安全性。

3.简化SQL语句使用同义词可以简化SQL查询语句。

通过给对象起一个简短的同义词，我们可以减少SQL语句的长度，提高查询效率。

此外，当数据库结构发生变化时，只需要修改同义词的指向，而不需要修改所有引用到该对象的SQL语句，从而减少了维护工作量。

4.实现跨用户的数据访问在Oracle数据库中，不同的用户可能对同一张表感兴趣。

使用同义词可以实现跨用户的数据访问，即一个用户可以通过同义词来访问另一个用户的表，而无需直接访问其他用户的对象。

这既能简化用户的操作，又能提高数据的安全性。

Oracle同义词从字面上理解就是别名的意思，和视图的功能类似。

就是一种映射关系。

同义词语法：CREATE [PUBLIC] SYNONYM synonym FOR object;1.创建同义词语句：create public synonym table_name for user.table_name;其中第一个user_table和第二个user_table可以不一样。

此外如果要创建一个远程的数据库上的某张表的同义词，需要先创建一个Database Link(数据库连接)来扩展访问，然后在使用如下语句创建数据库同义词：create synonym table_name for table_name@DB_Link;当然，你可能需要在user用户中给当前用户(user2)授权：grant select/delete/update on user22.删除同义词：drop public synonym table_name;3.查看所有同义词：select * from dba_synonyms同义词拥有如下好处：节省大量的数据库空间，对不同用户的操作同一张表没有多少差别;扩展的数据库的使用范围，能够在不同的数据库用户之间实现无缝交互;同义词可以创建在不同一个数据库服务器上，通过网络实现连接。

知识扩展：数据库对象表、视图、序列、过程、函数、程序包，甚至其它同义词都可以创建同义词。

1、同义词：私有同义词、公有同义词。

私有同义词只能被当前模式的用户访问。

私有同义词名称不可与当前模式的对象名称相同。

要在自身的模式创建私有同义词，用户必须拥有Create Synonym系统权限。

要在其它用户模式创建私有同义词，用户必须拥有Create Any Synonym系统权限。

公有同义词可被所有的数据库用户访问。

要创建公有同义词，用户必须拥有Create Public Synonym系统权限。

创建私有同义词语法：Create [OR REPLACE] SYNONYM [schema.]synonym_name FOR [schema.]object_name;其中：OR REPLACE表示在同义词存在的情况下替换该同义词。