数据库对象 同义词 索引 序列 视图
1. Oracle数据库对象
数据库对象是数据库的组成部分,常常用CREATE命令进行创建,可以使用ALTER命令修改,用DROP执行删除操作。前面已经接触过的数据库对象有表、用户等。
今天将学习更多的Oracle数据库对象:
同义词:就是给数据库对象一个别名。
序列:Oracle中实现增长的对象。
视图:预定义的查询,作为表一样的查询使用,是一张虚拟表。
索引:对数据库表中的某些列进行排序,便于提高查询效率。
2. 同义词
同义词(Synonym)是数据库对象的一个别名,Oracle可以为表、视图、序列、过程、函数、程序包等指定一个别名。同义词有两种类型:
私有同义词:拥有CREATE SYNONYM权限的用户(包括非管理员用户)即可创建私有同义词,创建的私有同义词只能由当前用户使用。
公有同义词:系统管理员可以创建公有同义词,公有同义词可以被所有用户访问。
创建同义词的语法是:
语法结构:同义词
CREATE [OR REPLACE] [PUBLIC] SYSNONYM [schema.]synonym_name
FOR [schema.]object_name
语法解析:
① CREATE [OR REPLACE:]表示在创建同义词时,如果该同义词已经存在,那么就用新创建的同义词代替旧同义词。
② PULBIC:创建公有同义词时使用的关键字,一般情况下不需要创建公有同义词。
③ Oracle中一个用户可以创建表、视图等多种数据库对象,一个用户和该用户下的所有数据库对象的集合称为Schema(中文称为模式或者方案),用户名就是Schema名。一个数据库对象的全称是:用户名.对象名,即schema.object_name。
如果一个用户有权限访问其他用户对象时,就可以使用全称来访问。比如:
代码演示:System用户访问Scott用户的Emp表
SQL> conn system/[email protected];
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as system
SQL> SELECT ENAME,JOB,SAL FROM SCOTT.EMP WHERE SAL>2000; ①
ENAME JOB SAL
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
6 rows selected
代码解析:
① 管理员用户可以访问任何用户的数据库对象,SYSTEM用户访问SCOTT用户的EMP表时,必须使用SCOTT.EMP。
案例1:创建一个用户XiaoMei,该用户拥有CONNECT角色和RESOURCE角色。为SCOTT用户的EMP表创建同义词,并通过同义词访问该EMP表。
代码演示:创建同义词并访问
SQL> CONN system/[email protected];
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as system
SQL> CREATE USER XiaoMei IDENTIFIED BY XiaoMei; ①
User created
SQL> GRANT CONNECT TO XiaoMei;
Grant succeeded
SQL> GRANT RESOURCE TO XiaoMei;
Grant succeeded
SQL> GRANT CREATE SYNONYM TO XiaoMei;
Grant succeeded
SQL> CONN XiaoMei/[email protected];
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as XiaoMei
SQL> CREATE SYNONYM MyEmp FOR SCOTT.EMP; ②
Synonym created
SQL> SELECT * FROM MYEMP; ③
SELECT * FROM MYEMP
ORA-00942: 表或视图不存在
SQL> CONNECT SCOTT/[email protected]
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as SCOTT
SQL> GRANT ALL ON EMP TO XiaoMei; ④
Grant succeeded
SQL> CONNECT XiaoMei/[email protected];
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as XiaoMei
SQL> SELECT ENAME,JOB,SAL FROM MyEmp WHERE SAL>2000; ⑤
ENAME JOB SAL
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
FORD ANALYST 3000
6 rows selected
代码解析:
① 在管理员用户下创建用户XiaoMei,对用户XiaoMei授予CONNECT和RESOURCE角色。为了XiaoMei能够创建序列,必须授予系统权限:CREATE SYNONYM。
② 在XiaoMei用户下,为SCOTT.EMP创建私有同义词MyEmp,同义词MyEmp只能在XiaoMei用户下使用。访问MyEmp就是访问SCOTT.EMP对象。
③ 访问MyEmp对象出错:对象不存在。因为XiaoMei如果访问MyEmp,就相当于访问SCOTT.EMP对象,那么SCOTT用户必须对XiaoMei授予相应的权限。
④ SCOTT用户下,把EMP表的所有权限(增删改查)授予XiaoMei。
⑤ 对MyEmp执行查询操作。MyEmp就可以像在本地的表一样使用。
删除同义词使用的语法是:
语法结构:删除同义词
DROP [PUBLIC] SYNONYM [schema.]sysnonym_name
语法解析:
① PUBLIC:删除公共同义词。
② 同义词的删除只能被拥有同义词对象的用户或者管理员删除。
③ 此命令只能删除同义词,不能删除同义词下的源对象。
3. 序列
序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列,序列中的可以升序生成,也可以降序生成。创建序列的语法是:
语法结构:创建序列
CREATE SEQUENCE sequence_name
[START WITH num]
[INCREMENT BY increment]
[MAXVALUE num|NOMAXVALUE]
[MINVALUE num|NOMINVALUE]
[CYCLE|NOCYCLE]
[CACHE num|NOCACHE]
语法解析:
① START WITH:从某一个整数开始,升序默认值是1,降序默认值是-1。
② INCREMENT BY:增长数。如果是正数则升序生成,如果是负数则降序生成。升序默认值是1,降序默认值是-1。
③ MAXVALUE:指最大值。
④ NOMAXVALUE:这是最大值的默认选项,升序的最大值是:1027,降序默认值是-1。
⑤ MINVALUE:指最小值。
⑥ NOMINVALUE:这是默认值选项,升序默认值是1,降序默认值是-1026。
⑦ CYCLE:表示如果升序达到最大值后,从最小值重新开始;如果是降序序列,达到最小值后,从最大值重新开始。
⑧ NOCYCLE:表示不重新开始,序列升序达到最大值、降序达到最小值后就报错。默认NOCYCLE。
⑨ CACHE:使用CACHE选项时,该序列会根据序列规则预生成一组***。保留在内存中,当使用下一个***时,可以更快的响应。当内存中的***用完时,系统再生成一组新的***,并保存在缓存中,这样可以提高生成***的效率。Oracle默认会生产20个***。
⑩ NOCACHE:不预先在内存中生成***。
案例2:创建一个从1开始,默认最大值,每次增长1的序列,要求NOCYCLE,缓存中有30个预先分配好的***。
代码演示:生成***
SQL> CREATE SEQUENCE MYSEQ
2 MINVALUE 1
3 START WITH 1
4 NOMAXVALUE
5 INCREMENT BY 1
6 NOCYCLE
7 CACHE 30
8 /
Sequence created
序列创建之后,可以通过序列对象的CURRVAL和NEXTVAL两个“伪列”分别访问该序列的当前值和下一个值。
代码演示:序列使用
SQL> SELECT MYSEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
1
SQL> SELECT MYSEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
2
SQL> SELECT MYSEQ.CURRVAL FROM DUAL;
CURRVAL
----------
2
使用ALTER SEQUENCE可以修改序列,在修改序列时有如下限制:
1. 不能修改序列的初始值。
2. 最小值不能大于当前值。
3. 最大值不能小于当前值。
使用DROP SEQUENCE命令可以删除一个序列对象。
代码演示:序列修改和删除
SQL> ALTER SEQUENCE MYSEQ
2 MAXVALUE 10000
3 MINVALUE -300
4 /
SEQUENCE ALTERED
SQL> DROP SEQUENCE MYSEQ;
SEQUENCE DROPPED
4. 视图
视图(View)实际上是一张或者多张表上的预定义查询,这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECT…FROM即可。
视图具有以下优点:
1. 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。
2. 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。
3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。
4. 从多张基表中按一定的业务逻辑抽出用户关心的部分,形成一张虚拟表。
语法结构:创建视图
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查询
[WITH READ ONLY CONSTRAINT]
语法解析:
1. OR REPLACE:如果视图已经存在,则替换旧视图。
2. FORCE:即使基表不存在,也可以创建该视图,但是该视图不能正常使用,当基表创建成功后,视图才能正常使用。
3. NOFORCE:如果基表不存在,无法创建视图,该项是默认选项。
4. WITH READ ONLY:默认可以通过视图对基表执行增删改操作,但是有很多在基表上的限制(比如:基表中某列不能为空,但是该列没有出现在视图中,则不能通过视图执行insert操作),WITH READ ONLY说明视图是只读视图,不能通过该视图进行增删改操作。现实开发中,基本上不通过视图对表中的数据进行增删改操作。
案例3:基于EMP表和DEPT表创建视图
代码演示:视图
SQL> CREATE OR REPLACE VIEW EMPDETAIL
2 AS
3 SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME
4 FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
5 WITH READ ONLY
6 /
VIEW CREATED
SQL> SELECT * FROM EMPDETAIL; ①
EMPNO ENAME JOB HIREDATE DEPTNO DNAME
7369 SMITH CLERK 17-12月-80 20 RESEARCH
7499 ALLEN SALESMAN 20-2月 -81 30 SALES
7521 WARD SALESMAN 22-2月 -81 30 SALES
7566 JONES MANAGER 02-4月 -81 20 RESEARCH
7654 MARTIN SALESMAN 28-9月 -81 30 SALES
7698 BLAKE MANAGER 01-5月 -81 30 SALES
7782 CLARK MANAGER 09-6月 -81 10 ACCOUNTING
7788 SCOTT ANALYST 19-4月 -87 20 RESEARCH
7839 KING PRESIDENT 17-11月-81 10 ACCOUNTING
7844 TURNER SALESMAN 08-9月 -81 30 SALES
7876 ADAMS CLERK 23-5月 -87 20 RESEARCH
7900 JAMES CLERK 03-12月-81 30 SALES
7902 FORD ANALYST 03-12月-81 20 RESEARCH
7934 MILLER CLERK 23-1月 -82 10 ACCOUNTING
14 ROWS SELECTED
代码解析:
① 对视图可以像表一样进行查询。该视图中隐藏了员工的工资。
删除视图可以使用“DROP VIEW 视图名称”,删除视图不会影响基表的数据。
5. 索引
当我们在某本书中查找特定的章节内容时,可以先从书的目录着手,找到该章节所在的页码,然后快速的定位到该页。这种做法的前提是页面编号是有序的。如果页码无序,就只能从第一页开始,一页页的查找了。
数据库中索引(Index)的概念与目录的概念非常类似。如果某列出现在查询的条件中,而该列的数据是无序的,查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序,生成独立的索引表。在某列上创建索引后,如果该列出现在查询条件中,Oracle会自动的引用该索引,先从索引表中查询出符合条件记录的ROWID,由于ROWID是记录的物理地址,因此可以根据ROWID快速的定位到具体的记录,表中的数据非常多时,引用索引带来的查询效率非常可观。
如果表中的某些字段经常被查询并作为查询的条件出现时,就应该考虑为该列创建索引。
当从很多行的表中查询少数行时,也要考虑创建索引。有一条基本的准则是:当任何单个查询要检索的行少于或者等于整个表行数的10%时,索引就非常有用。
Oracle数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效率,但是在数据增删改时需要更新索引,因此索引对增删改时会有负面影响。
语法结构:创建索引
CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])
语法解析:
1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。
2. index_name:指定索引名。
3. tabl_name:指定要为哪个表创建索引。
4. column_name:指定要对哪个列创建索引。我们也可以对多列创建索引;这种索引称为组合索引。
案例4:为EMP表的ENAME列创建创建唯一索引,为EMP表的工资列创建普通索引,把JOB列先变为小写再创建索引。
代码演示:创建索引
SQL> CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME); ①
Index created
SQL> CREATE INDEX IDX_SAL ON EMP(SAL); ②
Index created
SQL> CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB)); ③
Index created
代码解析:
① 为SCOTT.EMP表的ENAME列创建唯一索引。
② 为SCOTT.EMP表的SAL列创建索引。
③ 在查询中可能经常使用job的小写作为条件的表达式,因此创建索引时,可以先对JOB列中的所有值转换为小写后创建索引,而这时需要使用lower函数,这种索引称为基于函数的索引。
在select语句查询时,Oracle系统会自动为查询条件上的列应用索引。索引就是对某一列进行排序,因此在索引列上,重复值越少,索引的效果越明显。
Oracle可以为一些列值重复非常多且值有限的列(比如性别列)上创建位图索引。关于Oracle更多的索引类型(比如反向键索引等),请参考Oracle官方文档。
6. 表空间
在数据库系统中,存储空间是较为重要的资源,合理利用空间,不但能节省空间,还可以提高系统的效率和工作性能。Oracle可以存放海量数据,所有数据都在数据文件中存储。而数据文件大小受操作系统限制,并且过大的数据文件对数据的存取性能影响非常大。同时Oracle是跨平台的数据库,Oracle数据可以轻松的在不同平台上移植,那么如何才能提供统一存取格式的大容量呢?Oracle采用表空间来解决。
表空间只是一个逻辑概念,若干操作系统文件(文件可以不是很大)可以组成一个表空间。表空间统一管理空间中的数据文件,一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。如图所示:
图1 数据空间、表空间和数据文件
Oracle中所有的数据(包括系统数据),全部保存在表空间中,常见的表空间有:
系统表空间:存放系统数据,系统表空间在数据库创建时创建。表空间名称为SYSTEM。存放数据字典和视图以及数据库结构等重要系统数据信息,在运行时如果SYSTEM空间不足,对数据库影响会比较大,虽然在系统运行过程中可以通过命令扩充空间,但还是会影响数据库的性能,因此有必要在创建数据库时适当的把数据文件设置大一些。
TMEP表空间:临时表空间,安装数据库时创建,可以在运行时通过命令增大临时表空间。临时表空间的重要作用是数据排序。比如当用户执行了诸如Order by等命令后,服务器需要对所选取数据进行排序,如果数据很大,内存的排序区可能装不下太大数据,就需要把一些中间的排序结果写在硬盘的临时表空间中。
用户表自定义空间:用户可以通过CREATE TABLESPACE命令创建表空间。
创建表空间需要考虑数据库对分区(Extent,一个Oracle分区是数据库文件中一段连续的空间,Oracle分区是Oracle管理中最小的单位)的管理,比如当一个表创建后先申请一个分区,在Insert执行过程中,如果分区数据已满,需要重新申请另外的分区。如果一个数据库中的分区大小不一,创建表空间时需要考虑一系列问题。因此在Oracle8i之后,创建表空间都推荐使用“本地管理表空间”,这种表空间中的分区是一个固定大小的值,创建表空间的语法是:
语法结构:创建表空间
CREATE TABLESPACE 空间名称
DATAFILE '文件名1' SIZE 数字M
[,'文件名2' SIZE 数字….]
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 数字M
语法解析:
1. 文件名包括完整路径和文件名,每个数据文件定义了文件的初始大小,初始大小一般以“M”为单位。一个表空间中可以有多个数据文件。
2. EXTENT MANAGEMENT LOCAL指明表空间类型是:本地管理表空间。本地管理表空间要求Oracle中的数据分区(Extent)大小统一。
3. UNIFORM SIZE:指定每个分区的统一大小。
案例5:创建一个表空间,包含两个数据文件大小分别是10MB,5MB,要求extent的大小统一为1M。
代码演示:创建表空间
SQL> CREATE TABLESPACE MYSPACE
2 DATAFILE 'D:/A.ORA' SIZE 10M,
3 'D:/B.ORA' SIZE 5M
4 EXTENT MANAGEMENT LOCAL
5 UNIFORM SIZE 1M
6 /
Tablespace created
必须是管理员用户才能创建表空间,当表空间的空间不足时可以使用ALTER TABLESPACE命令向表空间中追加数据文件扩充表空间。
代码演示:扩充表空间
SQL> ALTER TABLESPACE MYSPACE
2 ADD DATAFILE 'D:/C.ORA' SIZE 10M
3 /
Tablespace altered
表空间可以在不使用时删除,使用DROP TABLESPACE命令。
数据库的所有数据全部在某一表空间中存放,在创建用户时,可以为用户指定某一表空间,那么该用户下的所有数据库对象(比如表)默认都存储在该空间中。
代码演示:为某一用户指定默认表空间
SQL> CREATE USER ACONG IDENTIFIED BY ACONG
2 DEFAULT TABLESPACE MYSPACE
3 /
User created
在创建表时,表中数据存放在用户的默认表空间中,也可以通过tablespace子句为表指定表中数据存放在其他表空间中。
代码演示:为表指定表空间
SQL> CREATE TABLE SCORES
2 (
3 ID NUMBER ,
4 TERM VARCHAR2(2),
5 STUID VARCHAR2(7) NOT NULL,
6 EXAMNO VARCHAR2(7) NOT NULL,
7 WRITTENSCORE NUMBER(4,1) NOT NULL,
8 LABSCORE NUMBER(4,1) NOT NULL
9 )
10 TABLESPACE MYSPACE
11 /
Table created
创建索引时也可以为索引指定表空间。
代码演示:为索引指定表空间
SQL> CREATE INDEX UQ_ID ON SCORES(ID)
2 TABLESPACE MYSPACE;
Index created
表和索引一旦创建,表空间无法修改。
转载于:https://blog.51cto.com/suexiukjung/1123820