第三部分 用户、表、索引和视图

第三部分 用户、表、索引和视图 第三部分 用户、表、索引和视图 一、用户管理 1、用户管理 用户：为了使用Oracle，需要为使用者建立自己的用户帐户，建立并管理帐户是用户管理的任务，实现阻止非授权用户访问数据库中的信息。在实际应用中，应该避免使用sys、system、scott等帐户。 模式：数据库模式定义为数据库对象的集合，而模式名称就是拥有或控制这些数据库对象集合的用户名称。所有的数据库对象 包括表、索引、视图、触发器、存储过程、程序包、函数等都归Oracle数据库中的一个用户所有。甚至Oracle数据字典、系统目录也是sys模式的一部分。 （1）建立帐户 CREATE USER user_name IDENTIFIED BY password [DEFAULT TABLESPACE def_tablespace] [TEMPORARY TABLESPACE temp_tablespace] 例： CREATE USER oracle_admin IDENTIFIED BY oracle_admin CREATE USER mytest IDENTIFIED BY hello （2）改变用户密码 ALTER USER usre_name IDENTIFIED BY new_password 或password //仅用于修改用户自己的密码 ALTER USER mytest IDENTIFIED BY window （3）锁定及解除帐户 ALTER USER user_name ACCOUNT [lock|unlock] ALTER USER mytest ACCOUNT lock ALTER USER mytest ACCOUNT unlock （3）修改用户表空间 ALTER USER user_name DEFAULT TABLESPACE new_def_tablespace [TEMPORARY TABLESPACE new_def_tablespace] 从10g开始，用户的默认表空间由system修改为sysaux，但是建议在使用user表空间。 ALTER USER mytest DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp （4）修改表空间配额 ALTER USER user_name QUOTA nM ON tablespace ALTER USER mytest QUOTA 10M ON users 查看分配给用户的表空间份额可以使用dba_ts_quotas、user_ts_quotas两个视图。 （5）删除用户 DROP USER user_name [cascade] （6）查看用户信息 在dba_users数据字典中存有用户信息。Dba_users结构： USERNAME 用户名称 USER_ID 用户ID PASSWORD 口令（加密） ACCOUNT_STATUS 用户状态 LOCK_DATE 锁定日期 EXPIRY_DATE 到期日期 DEFAULT_TABLESPACE 缺省表空间 TEMPORARY_TABLESPACE 临时表空间 CREATED 创建日期 PROFILE 概要文件 INITIAL_RSRC_CONSUMER_GROUP 用户所在组 EXTERNAL_NAME 外部名 User_users数据字典中存有当前用户的信息。 2、系统权限管理 在Oracle数据库中，有两类权限：对象权限和系统权限。对象权限是由用户赋予的访问或操作数据库对象的权限。而系统权限是用来许可对各种特性的访问，或许可Oracle数据库中的特定任务。 每个用户的Oracle数据库的系统权限存储在dba_sys_privs数据库视图中，用户自己的系统权限在user_sys_privs视图中存储。 常见的系统权限见P197表8-1 向用户赋予系统权限的基本语法： GRANT system_privilege TO username [WITH ADMIN OPTION] 删除用户系统权限的基本语法： REVOKE system_privilege FROM username 例： GRANT create session TO mytest GRANT create table TO mytest REVOKE create session FROM mytest REVOKE create table FROM mytest 授权能力是可以传递的，当某个用户的系统权限被收回之后，从该用户转授出去的权限不能自动被收回，因此，撤销系统权限时不 要求 对教师党员的评价套管和固井爆破片与爆破装置仓库管理基本要求三甲医院都需要复审吗 是最初授予系统权限的用户。任何具有admin option系统权限的数据库用户都能够取消其他用户的系统权限。 3、对象权限管理 最常用的对象权限如下：SELECT、INSERT、UPDATE、DELETE、EXECUTE、INDEX、REFERENCES和ALTER。 授予、取消对象权限的语法格式： GRANT object_privilege ON object_name TO username [WITH GRANT OPTION] REVOKE object_privilege ON object_name FROM username grant select on scott.emp to mytest WITH GRANT OPTION revoke select on scott.emp from mytest 用户可以查询 user_tab_privs、all_tab_privs、dba_tab_privs数据库视图，检查对表的访问权限，用户对自己建立的对象拥有所有权限。 与系统权限不同的是只有授权者才能够撤销其为其他用户授予的权限，同时从该用户转授出去的权限能自动收回。 4、角色管理 数据库管理员为每个用户直接授予或取消明确的对象权限，同时公司又有大量的员工，流动性大，则管理员的工作将是非常巨大的。实际应用中，总是根据需要建立若干个数据库角色，角色就是对象权限和系统权限的命名集合。数据库管理员为不同的数据库角色授予不同的权限满足不同的需要，再将角色授予每个用户。角色的变化相对较小。而增减用户时只是给撤销用户或建立新用户并授予角色即可，因此管理的复杂度小，任务轻。 （1）创建角色 CREATE ROLE role_name [IDENTIFIED BY role_password] CREATE ROLE athos （2）为角色授权 为角色授权与为用户授权使用的grant语句大体相同，取消数据库角色的权限与revoke语句也大致相同。 GRANT select,insert , update on mytable to athos （3）将角色赋予用户、收回角色 GRANT role_name TO user_name [with admin option] GRANT role_name TO role_name[with admin option] GRANT athos TO mytest REVOKE athos FROM mytest 在默认情况下，当把某个角色授予一个用户时，该角色对用户开始起作用。当该用户登录系统后，就会自动拥有该角色的权限。但是可以限制用户自动拥有授予其的角色权限。 Alter user mytest default role all except XXX Connect mytest/hello Set role xxx identified by xxx （4）撤销角色 DROP ROLE role_name DROP ROLE athos （5）查看角色信息 可以通过user_role_privs、role_sys_privs、role_tab_privs等数据字典视图查看有关角色的信息 二、管理表 表是数据库中最重要、最基本的对象，是实际存储数据的地方。对数据库的许多管理和操纵，实际上就是对数据库中表的管理和操纵。 表由行和列组成，行也称为 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 ，列也称为字段或域。每一行都是这样实体的一个完整描述。行的顺序可以是任意的。 列的顺序也是任意的。每一个列都有一种数据类型，具有不可分割性，列名在一个表中具有唯一性。行在一个表中的唯一性一般由用户通过增加列的主键来强制实现。 在物理上，需要确定表存储在哪个表空间，Oracle怎样将表物理存储在磁盘上，Oracle如何将表中数据与内存进行映射，Oracle怎样控制表上特定操作的日志。 1、SQL数据类型 数值类型：number类型，可以规定数字长度和小数位数； 字符类型：包括char、nchar、varchar2、nvarchar2、raw等。Char和nchar为存储固定长度的字符串；Varchar2和nvarchar2为存储可变长度的字符串。 日期时间类型：date和timestamp类型，date只能存储到整数秒，timestamp可以为秒提供6位小数精度。 2、创建表和约束 创建表： CREATE TABLE [schema_name.]table_name ( Column_name datatype [DEFAULT defaultvalue][CONSTRAINT constraint_exp ] [,Column_name datatype [DEFAULT defaultvalue][CONSTRAINT constraint_exp ]] [,……] [constraint ……] ) 例： create table employee_history( employee_id number(6) not null, salary number(8,2), hire_date date default sysdate, termination_date date, termination_desc varchar2(200) ); Create table as select创建表 Create table emp_copy as select * from emp 注：创建一个表并带有数据，表结构（所包含的字段及类型）不变，但是不复制约束。 Oracle系统中，可以使用声明型完整性约束和参照完整性约束来确保表中数据的正确。常见约束：主键约束、外键约束、唯一性约束、检查约束等。 主键约束： Create中可以使用选项：constraint 主键约束名 primary key (主键字段集) ALTER TABLE [schema.]table_name Add [constraint 主键约束名 primary key (主键字段集)] Alter table employee_history add constraint emp_his_pk primary key (employee_id, hire_date) 外键约束： 构成表外键的列的取值要么是空值、要么来自参照表（父表）的取值。 CREATE TABLE ….. CONSTRAINT fk_name FOREIGN KEY (外键列表) REFERENCES parent_table (parent_table_columns ) ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (外键列表) REFERENCES parent_table (parent_table_columns ) 唯一性约束： 确保表中的行对于值为非null的给定列或列组都具有唯一值，可以使用唯一性约束，因为主键只能有一个。唯一性约束的原则：P247。 ALTER TABLE table_name ADD CONSTRAINT unique_key_name UNIQUE (column_names) 检查约束： 指定表中的某个列的取值范围。如学生成绩在0——100之间，性别为’男’或’女’等。 ALTER TABLE table_name ADD CONSTRAINT ck_name CHECK(ck_expression) 3、表类型 表类型包括：堆表、外部表、索引组织表、临时表、分区表、簇表、散列簇表等 （1）堆表：数据在磁盘上随机存储。关系表一般都建成堆表。 例：创建主表学科表（学科ID、学科名称、描述）、加主键约束（学科ID）、创建子表课程表（课程ID、课程名称、学科ID、课程学时等）、加外键约束（学科ID） （2）外部表：在数据库之外的文件系统中存储的只读表。 创建一个文本文件teachers.csv，数据用逗号分隔，存储在D:\目录中。 创建外部表（需要具有CREATE ANY DIRECTORY权限）： 首先创建目录：CREATE DIRECTORY ext_data_files AS ‘D:\’ 建立外部表定义（增加 ORGANIZATION EXTERNAL子句）： CREATE TABLE sale_ext( First_name varchar2(15), Last_name varchar2(15), Phone_number varchar2(15) ) ORGANIZATION EXTERNAL( TYPE oracle_loader DEFAULT DIRECTORY ext_data_files ACCESS PARAMETERS(fields terminated by ‘,’) LOCATION(‘extsales.txt’) ) REJECT LIMIT unlimited （3）临时表 只在事务处理和会话期间存在数据的表。建立方法与常规表相同，用户只需要建立一次临时表，以后就可以在需要时使用它。临时表分为会话临时表和事务临时表，会话临时表中的数据在一次会话结束后删除，事务临时表中的数据在事务提交或回滚之后删除。 创建会话临时表： CREATE GLOBAL TEMPORARY TABLE session_table ( ) ON COMMIT PRESERVE ROWS 创建事务临时表： CREATE GLOBAL TEMPORARY TABLE session_table ( ) ON COMMIT DELETE ROWS 4、表特性 Tablespace子句：表空间子句，用来指定存储表的表空间，缺省情况下，存储在用户的默认表空间中。 Select default_tablespace from user_users;//查默认表空间 CREATE TABLE table_name (……) TABLESPACE ts_name ALTER TABLE table_name TABLESPACE ts_name Storage子句：（存储属性）用来管理如何给对象分配磁盘空间。表空间的存储参数具有5个属性：initial、next、pctincrease、minextents和maxextents。创建表时如果不指定Storage子句，则表的存储属性取表空间的存储属性，否则使用创建表时指定的存储属性。 Initial：第一个盘区（分区Extent）的大小； Next：第一个盘区填满后，随后盘区分配的空间大小； Pctincrease：表示“不断增长比例”，通常设为0；从第三个盘区开始前一NEXT值*(1+PCTINCREASE/100) Minextents：为表分配盘区数量的下限； Maxextents：为表分配盘区数量的上限。 　　CREATE TABLE test(a number) 　　STORAGE( 　　INITIAL 100K 　　NEXT 100K 　　MINEXTENTS 2 　　MAXEXTENTS 100 　　PCTINCREASE 100); 　　 　　解释： 　　初始给test表分配两个Extent， 　　第一个Extent是100K，因INITIAL=100K 　　第二个Extent是100K，因NEXT=100K 　　假如因表内数据增长，需要分配第三个Extent，因PCTINCREASE是100，则 　　第三个Extent是200K=100K+100K 　　第四个Extent是400K=200K+200K 可通过数据字典表DBA_TABLES、ALL_TABLES、USER_TABLES查看参数设置情况，如： select table_name,initial_extent,next_extent,min_extents,max_extents,pct_increase from user_tables; 修改 5、修改表 增加列：ALTER TABLE table_name ADD ( column_name type [,column_name type…]) 更新列：ALTER TABLE table_name MODIFY ( column_name type [,column_name type…]) 删除列：ALTER TABLE table_name DROP column_name type 标注不用的列：ALTER TABLE table_name SET UNUSED COLUMN column_name ALTER TABLE table_name SET UNUSED COLUMNS 重命名表：ALTER TABLE table_name RENAME TO new_table_name 6、删除表 DROP TABLE table_name [CASCADE CONSTRAINTS] //删除主表时删除子表的外键约束 7、truncate table TRUNCATE TABLE [schema.]table_name [drop storage | reuse storage] 需要具有 drop table的权限、不产生回滚数据。 Drop storage：默认选项，恢复表的最初存储属性 REUSE STORAGE：保留表存储分配的盘区。 8、数据字典 User_tablespace、DBA_tablespace、User_tables、dba_tables、all_tables、user_tab_privs、dba_tab_privs、all_tab_privs、user_sys_privs、dba_sys_privs、user_constraints、dba_constraints、all_constraints; 三、索引 索引是一种树状结构，用于提高检索速度，特别是在大数据量情况下，效果更佳明显。 创建索引：CREATE INDEX index_name ON table_name(column_names) 删除索引：DROP INDEX index_name 例： CREATE TABLE indextest AS SELECT * FROM DBA_OBJECTS 执行查询： SET AUTOTRACE TRACE EXPLAIN //只显示优化器执行路径报告 SET AUTOTRACE OFF //关闭跟踪 Select owner,object_name from indextest where object_name=’DBA_INDEXES’ //观察cost开销的值 CREATE INDEX indextest_objname_idx on indextest(object_name) Select owner,object_name from indextest where object_name=’DBA_INDEXES’ //再观察cost开销的值 结论1：有索引时查询开销远远小于没有索引时的开销 CREATE INDEX indextest_owner_idx on indextest(owner) Select owner,object_name from indextest where owner=’SYS’ Select owner,object_name from indextest where owner=’SCOTT’ 观察两者的Operation和cost 结论2：当查询结果数据量占总数据量比例大时使用权标扫描，否则使用索引查找。 使用索引可以提高查询速度，但是索引的存在也使数据的更新、插入、删除增加了索引维护的开销。 主键、外键约束自动创建索引； 反转键索引：create index命令中增加reverse选项 基于函数的索引：索引可以是以字段为参数的函数。 位图索引：CREATE BITMAP INDEX ……，只存在于企业版中，是在低区分值列上创建的压缩对象。 四、视图 视图是一种逻辑表，是查看表的一种方式。可以定义单表视图也可定义多表连接视图。 视图分类：关系视图、内嵌视图、对象视图和物化视图。 关系视图： 关系视图：就是存储的查询语句，平常所说的视图就是关系视图。关系视图是虚拟表。关系视图可以出现在insert、update和delete语句中，就像在表上使用这些语句一样。 CREATE OR REPLACE [FORCE|NOFORCE] VIEW view_name [column1 [,column2]…] AS QUERYSTATEMENT [WITH CHECK OPTION [CONSTRAINT constraint_name]] [with readonly] 如： Grant create view to scott Create or replace view computesalary(employee_number,name,salary,salaryNew) As Select empno,ename,sal,sal*.1 From emp 使用视图： Desc computesalary Select * from computesalary 再如： CREATE OR REPLACE VIEW dept_sum_vw (name,minsal,maxsal,avgsal) AS SELECT d.dname,min(e.sal),max(e.sal),avg(e.sal) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.dname; 视图的定义原则： 1.视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询； 2.在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用ORDER BY 子句； 3.如果没有为CHECK OPTION约束命名，系统会自动为之命名，形式为SYS_Cn; 4.OR REPLACE选项可以不删除原视图便可更改其定义并重建，或重新授予对象权限。 DML操作应遵循的原则： 1.简单视图可以执行DML操作； 2.在视图包含GROUP 函数，GROUP BY子句，DISTINCT关键字时不能删除数据行； 3.在视图不出现下列情况时可通过视图修改基表数据或插入数据： a.视图中包含GROUP 函数，GROUP BY子句，DISTINCT关键字； b.使用表达式定义的列； c.ROWNUM伪列。 d.基表中未在视图中选择的其他列定义为非空且无默认值。 WITH CHECK OPTION 子句限定： 通过视图执行的INSERTS和UPDATES操作不能创建该视图检索不到的数据行，因为它会对插入或修改的数据行执行完整性约束和数据有效性检查。 视图的删除： DROP VIEW VIEW_NAME语句删除视图。 删除视图的定义不影响基表中的数据。 只有视图所有者和具备DROP VIEW权限的用户可以删除视图。 视图被删除后，基于被删除视图的其他视图或应用将无效。 内嵌视图： 嵌入到父查询中的查询语句。可以出现在insert into 、update、delete from中。如： Select dname, count(*),to_char(count(*)/total_emp.cnt*100,'99.99')||'%' pct From dept,emp, (select count(*) cnt from emp) total_emp Where dept.deptno=emp.deptno Group by dname,total_emp.cnt 再如： select ename,hiredate from (select ename,hiredate from emp order by hiredate) where rownum<6; 对象视图： 将关系表投射到特定数据类型的虚拟对象表中，视图的每行都是带有属性、方法和唯一标识(OID)的对象实例。 创建对象类型： CREATE TYPE emp_type is object(empid number,empname varchar2(10),emphiredate date) CREATE VIEW emp_view of emp_type with object oid(empid) As Select e.empno,e.ename ,e.hiredate from emp e 物化视图： 物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图查询速度更快。