数据库对象-视图、序列、索引、同义词.ppt

第六节 数据库对象视图、序列、索引、同义词,通过本节学习，你需要掌握：视图(view)序列(sequence)索引(index)同义词(synonym)最后修改时间：,*,1,厦门大学计算机系工程硕士教材,数据库对象,*,2,厦门大学计算机系工程硕士教材,视图(View),可以基于表格或者视图中的字段或者字段的运算表达式建立视图。视图是一种逻辑结构，自身不存储任何记录，但可以反映数据源表格（称为基表）的值，并可以进行修改操作。视图在数据字典中被当作一个SELECT语句存储。,*,3,厦门大学计算机系工程硕士教材,*,4,厦门大学计算机系工程硕士教材,使用视图的原因？,数据保护，可以有选择地挑选需要的字段使查询语句简单化为应用程序提供独立数据为相同的数据提供不同的视角,*,5,厦门大学计算机系工程硕士教材,简单视图和复杂视图,*,6,厦门大学计算机系工程硕士教材,创建视图(1),CREATE OR REPLACE FORCE|NOFORCE VIEW view(alias,alias)AS subqueryWITH CHECK OPTIONWITH READ ONLY;OR REPLACE如果视图存在则更新FORCE即使数据源表格不存在也创建视图NOFORCE只有当源表格存在时才能创建，缺省值。view视图的名称alias视图中字段的名称，必须和子查询中的数量相同subquery一个完整的SELECT语句，可以对字段重命名WITH CHECK OPTION定义视图中的某些字段不可修改WITH READ ONLY规定视图中不允许DML操作,*,7,厦门大学计算机系工程硕士教材,创建视图(2),创建视图EMPVU10，包含10部门所有员工的资料。CREATE VIEWempvu10AS SELECTempno,ename,jobFROMempWHEREdeptno=10;使用DESCRIBE指令查询视图的结构DESC empvu10NameNull?Type-EMPNONOT NULLNUMBER(4)ENAMEVARCHAR2(10)JOBVARCHAR2(9),*,厦门大学计算机系工程硕士教材,8,创建视图(3),创建视图的查询语句中可以使用复杂的SELECT语句，包括连接(join)、分组(groups)、子查询(subqueries)、排序(order by)使用OR REPLACE选项可以在更新视图定义时，避免删除并重建的麻烦。,*,厦门大学计算机系工程硕士教材,9,创建视图(4),创建视图salvu30，包含30部门所有员工的员工号、姓名、工资，字段分别重命名为EMPLOYEE_NUMBER、NAME、SALARY。CREATE VIEW salvu30AS SELECTempno EMPLOYEE_NUMBER,ename NAME,sal SALARYFROMempWHEREdeptno=30;CREATE VIEW salvu30(EMPLOYEE_NUMBER,NAME,SALARY)AS SELECTempno,ename,salFROMempWHEREdeptno=30;,两种等价的写法,*,10,厦门大学计算机系工程硕士教材,从视图中查询数据(1),SELECT*FROM salvu30;查询视图的语句与查询表格的语句相同,*,11,厦门大学计算机系工程硕士教材,从视图中查询数据(2),SQL*PLUSSELECT*FROM empvu10;7839KING PRESIDENT7782CLARK MANAGER7934MILLER CLERK,EMP,USER_VIEWSEMPVU10SELECTempno,ename,jobFROMempWHEREdeptno=10;,*,12,厦门大学计算机系工程硕士教材,从视图中查询数据(3),当使用视图查询数据的时候，Oracle服务器执行以下操作：从USER_VIEWS中得到视图的定义验证用户是否有操作源表格的权限把针对视图的操作语句转换成针对基表的语句，从基表中得到数据或者更新数据。,*,厦门大学计算机系工程硕士教材,13,修改视图,使用OR REPLACE选项CREATE OR REPLACE VIEW empvu10(employee_number,employee_name,job_title)AS SELECTempno,ename,jobFROMempWHEREdeptno=10;定义字段别名的时候，顺序和数量必须和查询语句中的相同。,*,14,厦门大学计算机系工程硕士教材,创建复杂视图,创建视图dept_sum_vu，包含部门名称、部门的最低、最高、平均工资。CREATE VIEW dept_sum_vu(name,minsal,maxsal,avgsal)AS SELECTd.dname,MIN(e.sal),MAX(e.sal),AVG(e.sal)FROMemp e,dept dWHEREe.deptno=d.deptnoGROUP BYd.dname;,*,15,厦门大学计算机系工程硕士教材,当使用函数和表达式建立视图的时候，为每个字段建立别名是必要的。可以使用DESC语句查看视图结构，或者SELECT语句查看视图数据。SELECT*FROM dept_sum_vu,*,16,厦门大学计算机系工程硕士教材,在视图上执行DML操作的限制,可以在所有简单视图上执行DML操作。如果视图包含组函数（如SUM、AVG、MIN、MAX）、GROUP BY子句、DISTINCT，则不能删除记录。如果视图包含组函数、GROUP BY子句、DISTINCT、由表达式定义的字段（如salary*12）、ROWNUM伪字段，则不能修改记录。如果视图包含组函数、GROUP BY子句、DISTINCT、由表达式定义的字段（如salary*12）、ROWNUM伪字段、基表中存在某些NOT NULL约束的字段没有在视图中出现，则不能添加数据。,*,17,厦门大学计算机系工程硕士教材,WITH CHECK OPTION子句,定义视图中的某些字段如果修改后无法被SELECT语句选中，则修改操作将会失败。CREATE OR REPLACE VIEW empvu20AS SELECT*FROMempWHEREdeptno=20WITH CHECK OPTION;由于WITH CHECK OPTION约束条件，任何企图改变视图中deptno字段取值的操作都会失败。,*,18,厦门大学计算机系工程硕士教材,Denying DML Operations,使用WITH READ ONLY选项时，不允许对视图执行任何DML操作CREATE OR REPLACE VIEW empvu10(employee_number,employee_name,job_title)AS SELECTempno,ename,jobFROMempWHEREdeptno=10WITH READ ONLY;,*,19,厦门大学计算机系工程硕士教材,删除视图,DROP VIEW view;删除视图不会丢失任何数据，因为视图是逻辑上的概念，在数据库中是以SELECT语句形式存在的，自身不含任何数据。删除视图后，基于这个视图的数据库对象会失效(invalid)。只有视图的创建者或者有DROP ANY VIEW权限的用户才能删除视图。,*,20,厦门大学计算机系工程硕士教材,内置视图(Inline Views),内置视图是由FROM子句中的子查询语句产生的。SELECTa.ename,a.sal,a.deptno,b.maxsalFROMemp a,(SELECT deptno,max(sal)maxsalFROMempGROUP BYdeptno)bWHEREa.deptno=b.deptnoANDa.sal b.maxsal;,*,21,厦门大学计算机系工程硕士教材,有限排序分析(1),当需要提取有限多个最好的或者最差的记录时，需要用到有限排序分析。语法：SELECTcolumn_list,ROWNUMFROM(SELECT column_listFROMtableORDER BYTop-N_column)WHEREROWNUM=N;,ORDER BY子句确保内置视图中的记录按需要的顺序排列，从大到小时需要用DESC,ROWNUM伪字段对表格的第一行记录取值为1，第n行记录取值为n,比较符只能使用或=,厦门大学计算机系工程硕士教材,有限排序分析(2),输出EMP表中工资最高的3位员工的工资排位、姓名和工资。SELECTROWNUM as RANK,ename,salFROM(SELECT ename,sal FROM empORDER BY sal DESC)WHEREROWNUM=3;,*,23,厦门大学计算机系工程硕士教材,数据库对象,*,24,厦门大学计算机系工程硕士教材,序列(sequence),自动产生唯一的数字是一个可多个数据库用户共享的对象主要用于创建主键值由Oracle内部控制生成序列序列值的保存和生成与表格无关，因而相同的序列可以应用在多张表格中,*,25,厦门大学计算机系工程硕士教材,创建序列(1),CREATE SEQUENCE sequenceINCREMENT BY nSTART WITH nMAXVALUE n|NOMAXVALUEMINVALUE n|NOMINVALUECYCLE|NOCYCLECACHE n|NOCACHE;,*,26,厦门大学计算机系工程硕士教材,创建序列(1),sequence：序列的名称INCREMENT BY n：定义序列生成的数字的间隔为n。缺省情况下为1。START WITH n：定义序列生成的第一个数字是n。缺省情况下为1。MAXVALUE n：定义序列能生成的最大数字是n。NOMAXVALUE：定义对于升序序列，最大数字是1027，对于降序序列，最大数字是-1。这是缺省选项。MINVALUE n：定义序列能生成的最小数字是n。NOMINVALUE：定义对于升序序列，最小数字是1，对于降序序列，最小数字是-1026。这是缺省选项。CYCLE|NOCYCLE：定义当序列达到最大值或者最小值时是否继续生成数字(NOCYCLE是缺省选项)。CACHE n|NOCACHE：定义Oracle服务器预分配的序列数。缺省值为20。,*,厦门大学计算机系工程硕士教材,27,创建序列,创建序列DEPT_DEPTID_SEQ，用于生成DEPT表的主键，使用NOCYCLE选项。CREATE SEQUENCE dept_deptid_seqINCREMENT BY 10START WITH 120MAXVALUE 9999NOCACHENOCYCLE;Sequence created.,*,28,厦门大学计算机系工程硕士教材,使用序列的注意事项,不要使用CYCLE模式来生成主键值，除非有可靠的机制保证在生成下一轮数值时，旧的记录会被删除。序列独立存在，并非绑定在表格上。,*,29,厦门大学计算机系工程硕士教材,查询序列,使用数据字典USER_SEQUENCES查询序列SELECT sequence_name,min_value,max_value,increment_by,last_numberFROM user_sequences;,NOCACHE模式下，last_number返回序列下一个可以使用的数值,*,30,厦门大学计算机系工程硕士教材,NEXTVAL和CURRVAL伪字段,在创建序列后，就可以使用序列生成唯一值插入表格中的相关字段。序列中可以使用NEXTVAL和CURRVAL两个伪字段，调用方法为：sequence.NEXTVAL和sequence.CURRVAL。NEXTVAL返回下一个可以使用的序列数，即使多个用户调用序列的NEXTVAL值，也可以保证每个用户得到的值是互不相同的。CURRVAL获取当前的序列值。对于一个序列而言，在没有调用NEXTVAL之前，CURRVAL是没有值的。,*,31,厦门大学计算机系工程硕士教材,NEXTVAL和CURRVAL的使用规则,可以在以下情况中使用：SELECT语句中的SELECT列表，但不能是在子查询中INSERT语句子查询中的SELECT列表INSERT语句中的VALUE子句UPDATE语句中的SET子句不可以在以下情况中使用：视图中的SELECT列表包含DISTINCT关键字的SELECT语句包含GROUP BY、ORDER BY、HAVING子句的SELECT语句SELECT、DELETE、UPDATE语句中的子查询使用DEFAULT表达式的CREATE TABLE和ALTER TABLE语句,*,32,厦门大学计算机系工程硕士教材,使用序列,DEPT表中新增一个部门DEVELOPMENT，位于DETROITINSERT INTO dept(deptno,dname,loc)VALUES(dept_deptid_seq.NEXTVAL,DEVELOPMENT,DETROIT);观察当前的序列值SELECT dept_deptid_seq.CURRVALFROM dual;CURRVAL-120,*,33,厦门大学计算机系工程硕士教材,如果需要向上一条语句中新增的部门中添加一个新员工，可以考虑使用以下方法：INSERT INTO emp(empno,deptno,.)VALUES(employees_seq.NEXTVAL,dept_deptid_seq.CURRVAL,.);,*,34,厦门大学计算机系工程硕士教材,使用序列的注意事项,序列使用CACHE选项会加快其响应速度。序列生成的值可能会在以下情况时发生间断：ROLLBACK系统崩溃序列同时被其他表格使用如果序列使用NOCACHE选项，可以通过对USER_SEQUENCES表的访问，查看序列下一个生成的值,*,35,厦门大学计算机系工程硕士教材,修改序列,ALTER SEQUENCE dept_deptid_seqINCREMENT BY 20MAXVALUE 999999NOCACHENOCYCLE;Sequence altered.,*,36,厦门大学计算机系工程硕士教材,修改序列的语法格式,ALTER SEQUENCE sequenceINCREMENT BY nMAXVALUE n|NOMAXVALUEMINVALUE n|NOMINVALUECYCLE|NOCYCLECACHE n|NOCACHE;START WITH选项不能修改,*,37,厦门大学计算机系工程硕士教材,修改序列的注意事项(1),必须是序列的所有者，或者具有修改序列的系统权限。只影响修改后生成的序列值，对之前已经生成的值没有影响。ALTER SEQUENCE语句不能改变START WITH选项。如果需要重新从初始值开始使用序列，需要删除并重建序列。,*,38,厦门大学计算机系工程硕士教材,修改序列的注意事项(2),ALTER SEQUENCE语句必须有效ALTER SEQUENCE dept_deptid_seqINCREMENT BY 20MAXVALUE 90NOCACHENOCYCLE;ALTER SEQUENCE dept_deptid_seq*ERROR at line 1:ORA-04009:MAXVALUE cannot be made to be less than the current value,*,39,厦门大学计算机系工程硕士教材,删除序列,语法：DROP SEQUENCE sequence;使用DROP SEQUENCE语句从数据字典中删除序列。必须是序列的拥有者，或者具有删除序列的系统权限。DROP SEQUENCE dept_deptid_seq;Sequence dropped.,*,40,厦门大学计算机系工程硕士教材,数据库对象,*,41,厦门大学计算机系工程硕士教材,索引,什么是索引？索引是一个图表对象。索引可以显式建立，或者由服务器自动建立。索引加速ORACLE服务器定位数据，有效地降低I/O访问量。如果不使用索引，有可能需要全表扫描。一旦建立索引，索引的使用和维护都由服务器负责，用户不必考虑。索引与被索引的表格是相对独立的，因此增加、删除、修改索引不会影响表格数据和其他索引。当表格被删除时，相关索引自动被删除。,*,42,厦门大学计算机系工程硕士教材,索引的两种类型,自动建立：当创建表格时，如果有定义字段为PRIMARY KEY或者UNIQUE，相关的唯一索引会被自动创建。手工建立：用户可以创建非唯一的索引以加速数据定位。用户也可以创建唯一索引，不过建议采用定义字段为UNIQUE的方法。,*,43,厦门大学计算机系工程硕士教材,创建索引,语法：CREATE INDEX indexON table(column,column.);加速对EMP表ename字段的查询CREATE INDEX emp_ename_idxON emp(ename);Index created.,*,44,厦门大学计算机系工程硕士教材,索引的创建原则,More Is Not Always Better以下情况中应当建立索引字段的取值分布范围很广字段的取值包含了很多NULL值一个或多个字段经常被WHERE子句使用到表数据很大，但大多数查询语句只需要其中2%-4%左右的数据。注意如果需要定义唯一索引，应当在表格创建时就加上UNIQUE限制。,*,45,厦门大学计算机系工程硕士教材,不适宜创建索引的情况,小型表格字段不是经常在查询语句中作为判定条件出现大多数的查询语句返回的记录数超过表格总记录数的2%到4%表格经常被更新被索引字段用于表达式中,*,46,厦门大学计算机系工程硕士教材,验证索引,USER_INDEXES表中包含索引名和唯一性USER_IND_COLUMNS表中包含索引名、表格名和字段名SELECT ic.index_name,ic.column_name,ic.column_position col_pos,ix.uniquenessFROM user_indexes ix,user_ind_columns icWHERE ic.index_name=ix.index_nameAND ic.table_name=EMP;,基于函数的索引,CREATE INDEX idx_upper_dept_nameON dept(UPPER(dname);Index created.SELECT*FROM deptWHERE UPPER(dname)=SALES;,*,48,厦门大学计算机系工程硕士教材,NULL值对于函数索引的影响,SELECT*FROM deptORDER BY UPPER(dname);假设dname字段可能存在空值，那么以上语句很可能在执行的时候用到了全表扫描，即使UPPER(dname)已经被定义为索引。为了避免这种情况，使用如下语句：SELECT*FROM deptWHERE UPPER(dname)IS NOT NULLORDER BY UPPER(dname);,*,49,厦门大学计算机系工程硕士教材,删除索引,语法：DROP INDEX index;删除DEPT表中的idx_upper_dept_name索引DROP INDEX idx_upper_dept_name;Index dropped.必须是索引的拥有者或者具有删除索引的系统权限,*,50,厦门大学计算机系工程硕士教材,数据库对象,*,51,厦门大学计算机系工程硕士教材,同义词,语法：CREATE PUBLIC SYNONYM synonymFOR object;DROP PUBLIC SYNONYM synonym;创建同义词的目的：易于访问其他数据库用户创建的表格缩短数据库对象的名称,*,52,厦门大学计算机系工程硕士教材,使用同义词的注意事项(1),同义词指向的数据库对象必须已经存在，对象类型可以是表格、视图、序列、存储过程。封装在数据包(package)里的对象不能定义同义词普通用户只能创建私有的同义词，同义词的名字不能与该用户拥有的其他数据库对象重名。,*,53,厦门大学计算机系工程硕士教材,使用同义词的注意事项(2),只有数据库管理员能为所有用户创建公用的同义词CREATE PUBLIC SYNONYM departmentFOR alice.dept;Synonym created.只有数据库管理员能删除公用的同义词DROP PUBLIC SYNONYM department;Synonym dropped.,*,54,厦门大学计算机系工程硕士教材,小结,视图是由表格或其他视图派生的，使用视图具有如下好处：限定数据库访问简化查询语句提供数据独立性对相同数据提供不同观察视角删除视图不会丢失相关数据使用序列自动生成数值，会使用NEXTVAL和CURRVAL伪字段，使用USER_SEQUENCES表查询序列定义。创建索引加快查询速度，使用USER_INDEXES查询索引定义。使用同义词定义数据库对象别名。,*,55,厦门大学计算机系工程硕士教材,第六节 数据库对象视图、序列、索引、同义词 习题,列出公司里4位最年长的员工。对DEPT表中的主键创建一个序列。要求从200开始，每次增加10，最大值1000。对EMP表的DEPTNO外键创建非唯一索引。,*,56,厦门大学计算机系工程硕士教材,