1 视图
1.1 视图简介
视图是由一个或者多个表组成的虚拟表;那些用于产生视图的表叫做该视图的基表。视图不占用物理空间,这个也是相对概念,因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候只是重新执行SQL。一个视图也可以从另一个视图中产生。视图没有存储真正的数据,真正的数据还是存储在基表中。一般出于对基本的安全性和常用的查询语句会建立视图;并一般情况下不对视图进行新增、更新操作。
【语法】
--创建视图 CREATE [OR REPLACE] VIEW <view_name> AS <SELECT 语句>;
--删除视图 DROP VIEW <view_name> ; |
1.2 视图操作
-- 授予wxz用户 创建视图 的权限 grant createview to wxz;
-- 登录wxz,创建视图 create orreplace view v_emp as select empno,enamefrom emp;
--通过视图查询数据 select *from v_emp;
--通过视图添加数据,需要保证基表的其它数据项可以为空 insert into v_emp(empno,ename)values(3333,'wxz');
--通过视图修改数据 update v_emp set ename='wxz3'where empno=3333;
--通过视图删除数据 delete from v_emp where empno=3333;
--基于多个基表的视图,不建议使用视图进行增删改操作 create orreplace view v_dept_emp as select dept.deptno,dept.dname,enamefrom emp inner join dept on emp.deptno=dept.deptno;
--查询多个基表的视图 select *from v_dept_emp;
--创建基于视图的视图 create orreplace view vv_emp as select ename from v_emp;
--查询基于视图的视图 select *from vv_emp;
--删除视图 drop view v_emp; drop view v_dept_emp; drop view vv_emp;
|
2 同义词
同义词是数据库模式对象的一个别名,经常用于简化对象访问和提高对象访问的安全性。在使用同义词时,Oracle数据库将它翻译成对应模式对象的名字。与视图类似,同义词并不占用实际存储空间,只有在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象,如表、视图、同义词、序列、存储过程等,数据库管理员都可以根据实际情况为他们定义同义词。隐藏对象名称和所有者。
2.1 私有同义词
私有Oracle同义词由创建它的用户所有;创建的用户需要具有CREATE SYNONYM权限。
【语法】 CREATE SYNONYM <synonym_name> for <tablename/viewname...>
【示例】 --管理员 授权用户wxz创建同义词的权限 grant createsynonym to wxz;
--创建私有同义词 create synonym syn_empfor emp; create synonym syn_v_empfor v_emp;--为视图v_emp创建私有同义词(别名)
--使用私有同义词 select empno,enamefrom syn_emp; update syn_emp set ename='wxz4'where empno='1234';
--删除同义词 drop synonym syn_emp;
|
2.2 公有同义词
公有Oracle同义词由一个特殊的用户组Public所拥有。顾名思义,数据库中所有的用户都可以使用公有同义词。公有同义词往往用来标示一些比较普通的数据库对象,这些对象常需要引用。公有同义词一般由管理员用户创建及删除,普通用户需要创建及删除需要create public synonym和drop public synonym权限。
【语法】 CREATE PUBLIC SYNONYM <synonym_name> for <tablename/viewname...>
--登陆sys管理员用户,授权用户wxz创建、删除(公有的删除权限需要特别给定)公有同义词权限 grant createpublic synonym,droppublic synonym to wxz; --revoke create public synonym,drop public synonym from wxz;
--登陆wxz用户创建公有同义词 conn wxz/wxz; create publicsynonym syn_public_emp for emp;
--使用公有同义词 select *from syn_public_emp;
-- 登录system管理员 conn system/orcl; 创建wxz2并授权 --create user wxz2 identified by wxz2 default tablespace wxz_ts; --grant connect,resource to wxz2;
--为其它用户wxz2授权使用公有同义词(需要给予使用表的权限) grant select,updateon wxz.emp to wxz2; --revoke select,update on wxz.emp from wxz2;
--登陆wxz2用户下使用公有同义词syn_public_emp select *from syn_public_emp; update syn_public_emp set ename='wxz5'where empno=5555;
--删除同义词 --登陆wxz,删除公有同义词 drop publicsynonym syn_public_emp;
|
3 索引
索引是建立在数据库表中的某些列的上面,是与表关联的,可提供快速访问数据方式,但会影响增删改的效率;常用类型(按逻辑分类):单列索引和组合索引、唯一索引和非唯一索引。
什么时候要创建索引
(1)在经常需要搜索、主键、连接的列上
(2)表很大,记录内容分布范围很广
(3)在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的
(4)在经常使用在WHERE子句中的列上面创建索引
什么时候不要创建索引
(1)表经常进行 INSERT/UPDATE/DELETE 操作
(2)表很小(记录超少)
(3)列名不经常作为连接条件或出现在 WHERE 子句中
(4)对于那些定义为text, image和bit数据类型的列不应该增加索引
3.1 创建索引
【语法】 CREATE [UNIQUE] INDEX <index_name> ON <table_name>(字段 [ASC|DESC]);
【说明】 UNIQUE --确保所有的索引列中的值都是可以区分的。 [ASC|DESC] --在列上按指定排序创建索引。
(创建索引的准则: 1.如果表里有几百行记录则可以对其创建索引(表里的记录行数越多索引的效果就越明显)。 2.不要试图对表创建两个或三个以上的索引。 3.为频繁使用的行创建索引。)
【示例】 --创建单列唯一索引,表中的列值将不允许重复 create uniqueindex index_emp_empno on emp(empno);
--创建单列非唯一索引 create index index_emp_enameon emp(ename);
--创建组合列、唯一索引 create uniqueindex index_emp_ename_job on emp(ename,job);
--创建组合列、非唯一索引 create index index_emp_job_salon emp(job,sal);
|
3.2 删除索引
【语法】 DROP INDEX <index_name>;
【示例】 --删除索引 drop index index_emp_empno; drop index index_emp_ename; drop index index_emp_ename_job; drop index index_emp_job_sal;
|
4 序列
序列是oracle提供的一个产生唯一数值型值的机制。通常用于表的主健值,序列只能保证唯一,不能保证连续。
4.1 创建序列
【语法】 CREATE SEQUENCE <sequencen_name> [INCREMENT BY n] [START WITH n] [MAXVALUE n][MINVALUE n] [CYCLE|NOCYCLE] [CACHE n|NOCACHE];
INCREMENT BY n --表示序列每次增长的幅度;默认值为1. START WITH n --表示序列开始时的序列号。默认值为1. MAXVALUE n --表示序列可以生成的最大值(升序). MINVALUE n --表示序列可以生成的最小值(降序). CYCLE --表示序列到达最大值后,在重新开始生成序列.默认值为 NOCYCLE。 CACHE n--允许更快的生成序列.预先生成n个序列值到内存(如果没有使用完,那下次序列的值从内存最大值之后开始;所以n不应该设置太大)
【示例】 --创建递增序列 create sequence seq_test increment by 1 start with1 maxvalue 1000 nocycle;
--创建递减序列 create sequence seq_test2 increment by -1 start with5 maxvalue 5 minvalue 1 nocycle;
|
4.2 序列使用
1、NEXTVAL 返回序列下一个值;第一次访问时,返回序列的初始值,后继每次调用时,按步长增加的值返回 【语法】 select <sequence_name>.nextval from dual;
【示例】 select seq_test.nextval from dual;
2、CURRVAL 返回序列的当前值.注意在刚建立序列后,序列的CURRVAL值为NULL,所以不能直接使用。使用过NEXTVAL访问序列后才能使用 【语法】查看序列的当前值 select <sequence_name>.currval from dual;
【示例】 select seq_test.nextval from dual; select seq_test.currval from dual;
|
运用序列
-- 创建序列 create sequence seq_emp_empno start with1000 increment by 1 maxvalue 9000 minvalue 1000 nocycle;
-- 使用序列作为主键插入emp表的empno列 insert into emp(empno,ename) values(seq_emp_empno.nextval,'wxz1'); insert into emp(empno,ename) values(seq_emp_empno.nextval,'wxz2');
-- 查看emp表数据 select empno,enamefrom emp;
-- 查看当前序列的值 select seq_emp_empno.currval from dual;
--修改序列 alter sequence seq_emp_empno maxvalue 9999 cycle;
|
4.3 删除序列
【语法】 DROP SEQUENCE <sequence_name>
【示例】 drop sequence seq_test; |
4.4 序列与sys_guid
sys_guid和序列都可以作为主键值。
--使用SYS_GUID函数,32位,由时间戳和机器标识符生成,保证唯一 select sys_guid()from dual;
|
5 分区表
5.1 分区表用途
分区表通过对分区列的判断,把分区列不同的记录,放到不同的分区中。分区完全对应用透明。Oracle的分区表可以包括多个分区,每个分区都是一个独立的段(SEGMENT),可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据,也可以通过在查询时直接指定分区的方法来进行查询。
分区表的优点:
(1)由于将数据分散到各个分区中,减少了数据损坏的可能性;
(2)可以对单独的分区进行备份和恢复;
(3)可以将分区映射到不同的物理磁盘上,来分散IO;
(4)提高可管理性、可用性和性能。
数据量大的表,一般大于2GB;数据有明显的界限划分;对于Long和Long Raw类型列不能使用分区。
5.2 分区表类型
一般包括范围分区,散列分区,列表分区、复合分区(范围-散列分区,范围-列表分区)、间隔分区和系统分区等。
5.2.1 范围分区
范围分区根据数据库表中某一字段的值的范围来划分分区。
【语法】 在Create Table语句后增加 PARTITION BY RANGE(column_name) ( PARTITION part1 VALUES LESS THAN (range1) [TABLESPACE tbs1], PARTITION part2 VALUES LESS THAN (range2) [TABLESPACE tbs2], .... PARTITION partN VALUES LESS THAN (MAXVALUE) [TABLESPACE tbsN] );
【说明】 MAXVALUE:当分区列值都不在设置的范围内时,新增数据将到这个分区中
【示例】 -- 创建表,并设置分区 create table myemp ( empno number(4)primary key, ename varchar2(10), hiredate date, sal number(7,2), deptno number(2) ) partition by range(sal) ( partition p1 values less than(1000), partition p2 values less than(2000), partition p3 values less than(maxvalue) );
-- 插入数据 insert into myemp(empno,ename,hiredate,sal,deptno) select empno,ename,hiredate,sal,deptnofrom emp;
-- 查看工资1000-2000的数据 select *from myemp partition(p2);
-- 删除工资小于1000的数据 delete from myemppartition(p1);
-- 查看数据 select *from myemp;
|
5.2.2 列表分区
列表分区明确指定了根据某字段的某个具体值进行分区,而不是像范围分区那样根据字段的值范围来划分的。
【语法】 在Create Table语句后增加 PARTITION BY LIST(column_name) ( PARTITION part1 VALUES (values_list1), PARTITION part2 VALUES (values_list2), .... PARTITION partN VALUES (DEFAULT) ); 其中:column_name是以其为基础创建列表分区的列。 part1...partN是分区的名称。 values_list是对应分区的分区键值的列表。 DEFAULT关键字允许存储前面的分区不能存储的记录。
【示例】 -- 创建表,并设置分区 create table myemp2 ( empno number(4)primary key, ename varchar2(10), hiredate date, sal number(7,2), deptno number(2) ) partition by list(deptno) ( partition dept10 values(10), partition dept20 values(20), partition dept30 values(30), partition deptx values(default) );
-- 插入数据 insert into myemp2(empno,ename,hiredate,sal,deptno) select empno,ename,hiredate,sal,deptnofrom emp;
-- 查看部门20的数据 select *from myemp2 partition(dept20);
-- 删除部门30的数据 delete from myemp2partition(dept30);
-- 查看数据 select *from myemp2;
|