Oracle实战笔记（第五天）

导读

　　今天的主要内容：维护数据的完整性、索引、管理Oracle的权限和角色。

一、维护数据库的数据的完整性

　　数据完整性用于确保数据库数据遵从一定的商业规则和逻辑规则。在Oracle中，数据完整性可以使用约束、触发器、应用程序（过程和函数）三种方法来实现。在这三种实现方法中约束最易于维护，且性能最好，所以一般使用约束来作为数据维护的是首选。下面内容也主要是对约束的讲解。

　　1、约束（Constraint）

　　约束用于确保数据库满足特定的商业规则。Oracle中约束包括五种：not null（非空）、unique（唯一）、primary key（主键）、foreign key（外键）、check（检查）。

• not null：非空约束，当该列定义了not null约束是，那么该列数据不允许为空。
• unique：数据唯一约束，当定义了unique约束，该列值不能重复，但允许为空。
• primary key：主键约束，用于唯一标识表行的数据，该列值不能为空且不能重复。ps：一张表只能有一个主键。
• foreign key：外键约束，用于定义主表和从表字啊进的关系，外键约束要定义在从表上，而主表必须具有主键约束或unique约束。要求外键列数据必须存在于主表主键列或unique列或为null。
• check ：用于强制行数据必须满足的条件。比如sal列定义了check约束，并要求sal列在1000～2000之间，如果插入数据时不满足会报错。

　　2、商店销售系统表设计案例

　　现有一个商店的数据库，记录客户机器购物情况，由三个表组成：

• 商品表goods（商品号goodsId，商品名 goodsName，单价 unitprice，商品类别category，供应商provider）；
• 客户表customer（客户号customerId，姓名name，住在address，电邮email，性别sex，身份证cardId）；
• 购买表purchase（客户号customerId，商品号goodsId，购买数量nums）；

　　 请用sql语句完成下列需求：

1. 建表，在定义中要求声明： (1)每个表的主外键； (2) 客户的姓名不能为空值； (3) 单价必须大于0，购买数量必须在1到30之间； (4) 电邮不能够重复； (5).客户的性别必须是男或者女，默认是男。
   

   --商品表
   create table goods(goodsId char(8) primary key ,--商品号，主键
       goodsName varchar2(30) ,--商品名
       unitprice number(8,2) check(unitprice > 0),--单价（0,999999.99]
       categry varchar2(10) ,--商品类别
       provider varchar2(30) --供应商
   );
   --客户表
   create table customer(customerId char(8) primary key ,--客户号，主键
       name varchar2(20) not null,--姓名(非空)
       adress varchar(50),--住址
       email varchar(20) unique,--邮箱（唯一）
       sex char(2) default '男' check(sex in('男','女')) ,--性别(默认男)
       cardId char(18)--身份证
   );
   --购买表
   create table purchase(
       customerId char(8) references customer(customerId),--客户号,外键
       goodsId char(8) references goods(goodsId),--商品号，外键
       nums number(2) check(nums between 1 and 30)--购买数量,[1，30]
   );

2. 有时候我们在建表时会忘记一些约束，那么我们可以在建表后使用alter table 命令来为表增加约束。ps：在增加not null约束时需要使用modify，而其他四个增加约束要使用add。下面我们继续完善应有的约束：
   1）购买表的主键设置为customerId和goodsId；
   

   --增加联合主键约束的语法：
   alter table tablename add constraint unionkeyname primary key (column1,column2);
   --购买表的主键设置为customerId和goodsId
   alter table purchase  add constraint unionkeyname primary key (customerId,goodsId);

   2）增加商品名not null约束；
   

   alter table goods modify goodsName not null;

   3）增加身份证唯一约束；
   

   --constraint是约束的意思，cardUnique是自定义约束的名字
   alter table customer add constraint cardUnique unique(cardId);

   4）增加约束让客户的住址只能是‘海淀’、‘朝阳’、‘东城’、‘西城’、‘通州’、‘昌平’。
   

   alter table customer add constraint addressCheck check(adress in('海淀','朝阳','东城','西城','通州','昌平'));

　　3、删除约束

　   当不需要某个约束时，使用drop constraint命令来进行删除。

• alter table 表名 drop constraint 约束名;
  --比如删除上面的地址约束
  alter table customer drop constraint addressCheck;

　　注意：在删除约束时，可能会发生逻辑上的错误导致无法删除，比如：alter table 表名 drop primary key;如果这个时候该主键作为主表主键而从表的的外键还存在的情况下不允许删除，这是可以带上cascade进行级联删除，表示将从表的外键也一起删除。

　　4、显示约束信息

1. 通过使用数据字典视图user_constraints可以显示当前用户的所有约束信息。
   

   --查询表goods的一些约束信息
   select constraint_name,constraint_type,status,validated from user_constraints where table_name='goods';

2. 显示约束列：通过使用数据字典视图user_cons_columns可以显示约束所对应的列表信息。
   

   --查询约束名为addressCheck的一些列信息
   select column_name,position from user_cons_columns where constraint_name='addressCheck';

3. 使用软件工具pl/sql就能简单地查看约束信息。

　　5、列级定义&表级定义

　　列级定义是指定义列的同时定义约束。比如要求一的做法：创建表的同时定义约束。

　   表级定义是指再定义所有列后再定义约束。比如要求二的做法：在创建表后再增加约束。

　   不同点：列级约束在定义时不用主动给约束命名，Oracle会自动命名；表级定义则需要自定义约束名称，这样也方便管理。

二、索引（index）

　　索引是对数据库表中一列或多列的值进行排序的一种结构。索引用于加速数据存储的数据对象，合理的使用索引可以大大降低I/O次数，从而提高数据访问性能。通过建立索引可以极大地提高在数据库中获取所需信息的速度，同时还能提高服务器处理相关搜索请求的效率，同时维护索引也需要较多的时间和精力，所以索引的建立应当选择在经常查询的列。

　　1、常用索引

• 单列索引
  单例索引是基于单个列所建立的索引，比如：
  

  --语法
  create index 索引名 on 表名(列名) 
  --创建ename列的索引
  create index enameIndex on emp(ename);

• 复合索引
  复合索引是基于两列或多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：
  

  --这也是不同组合的索引，这两个索引区别在于前者先查询ename，后者先查询job
  create index emp_index1 on emp(ename,job)；
  create index emp_index2 on emp(job,ename)；

　　2、索引建立原则

1. 在大表上建立才有意义；
2. 在where字句或是连接条件上经常引用的列上建立索引；
3. 在一个表中建立索引的层次不要超过4层。

　　3、索引缺点分析

　　索引有一些先天不足：

1. 占空间：建立索引，系统要占用大约是表1.2倍的硬盘和内存空间来保存索引。
2. 维护麻烦：更新数据时，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。实践表明，不恰当的索引不但于事无补反而降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引要花费更多的系统时间。

　　4、显示索引信息

• 显示表的所有索引：通过使用数据字典视图dba_indexes 和 user_indexes，可以分别显示数据库的所有索引信息和用户的所有索引信息。
  

  --查询emp表的索引名和索引类型 
  select index_name,index_type from user_indexes where table_name='emp';

• 显示索引列：通过使用数据字典视图user_ind_columns可以显示索引对应的列信息。
  

  --查询索引名为enameIndex对应列的表名和字段名
  select table_name,column_name from user_ind_columns where index_name='enameIndex';

• 使用ql/sql developer工具查看。

三、管理Oracle的权限和角色

 　　在前几天的内容中已经说过，当用户刚刚创建时是没有任何权限的，不能执行任何操作。所以我们需要授予用户相应的权限或者角色，才能执行相应的操作。其中Oracle中的权限可以分为系统权限和对象权限。

　　1、系统权限

　　系统权限是指执行特定类型sql命令的权利，它用于控制用户可以执行的一个或是一组数据库操作。比如当用户具有create table权限时，可以在其方案中建表，当用户具有create any table权限时，可以在任何方案中建表。目前oracle提供了两百多种系统权限，常用的有：

• create session： 连接数据库。
• create view ：建视图。
• create procedure： 建过程、函数、包。
• create cluster ：建簇。
• create table ：建表。
• create public synonym ：建同义词。
• create trigger ：建触发器。

　　2、显示系统权限

　　目前Oracle提供了两百多种系统权限（随版本发行一般都会新增权限），我们可以通过使用数据字典视图system_privilege_map，来显示所有系统权限。

• select * from system_privilege_map order by name;

　　3、授予系统权限

　　一般情况，授予系统权限是有dba完成的，如果用其它用户来授予系统权限，则要求用户必需具有grant any privilege的系统权限。在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。举个例子：

1. 创建两个用户ken,tom（初始阶段他们没有任何权限，如果登陆就会出错误的信息）
   

   create user ken identified by ken;
   create user tom identified by tom;

2. 给用户ken和tom授权（grant to）
   

   --带有with admin option表示ken能赋予该权限给其他用户
   grant create session to ken with admin option;
   grant create table to ken with admin option;
   --tom不带
   grant create session to tom;
   grant create table to tom;

　　4、回收系统权限

　　一般情况下，回收系统权限也是dba来完成的，如果其它的用户来回收系统权限，要求该用户必需具有相应系统权限及转授系统权限的选项(with admin option)。回收系统权限使用revoke from来完成，当回收了系统权限后，用户就不能执行相应的操作了，另外请注意：系统权限不会级联回收权限，但对象权限会级联收回。

• --回收ken建表的系统权限（如果ken给其他用户授权了建表权限，那么不会被级联收回）
  revoke create session from ken

　　5、对象权限

　　对象权限指访问其它方案对象的权利，用户可以直接访问自己方案的对象，但是如果要访问别的方案的对象，则必需具有对象的权限，比如smith用户要访问scott.emp表。则必需在scott.emp表上具有对象的权限。常用的权限有：

• alter 修改
• delete 删除
• select 查询
• insert 添加
• update 修改
• index 索引
• references 引用

　　6、显示对象权限

　　通过使用数据字典视图dba_tab_privs可以显示用户或角色具有的对象权限。

　　7、授予对象权限

　　在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它的用户来操作，则需要用户具有相应的(with grant option)权限，从oracle9i开始，sys,system可以将任何对象上的对象权限授予其它用户，授予对象权限是用grant to命令来完成的。

• --语法（with grant option不能赋予给角色）
  grant 对象权限 on 数据库对象 to 用户名 [with grant option] [,角色名]

　　比如：

• --使用dba将查询scott.emp表的权限授予给monkey用户
  grant select on scott.emp to monkey;
  
  --使用dba将所有数据操作scott.emp表的权限授予给monkey用户
  grant all on scott.emp to monkey;
  
  --如果black用户修改scott.emp表的结构，则必需授予alter对象权限
  grant alter on scott.emp to black;
  
  --如果用户想要执行其它方案的包/过程/函数，则需有execute权限。
  grant execute on dbms_transaction to ken;
  
  --如果想在别的方案的表上建立索引，则必需具有index对象权限
  grant index on dbms_transaction to ken;

　　8、回收对象权限

　　在oracle9i中，收回对象的权限可以由对象的所有者来完成，也可以用dba用户(sys,system)来完成。注意：系统权限不会级联回收权限，但对象权限会级联收回。

• revoke 对象权限 on 数据库对象 from 用户名[,角色名];

　　9、角色

　　角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理。角色分为预定义角色和自定义角色。预定义角色是指oracle所提供的角色，每种角色都用于执行一些特定的管理任务，下面我们介绍常用的预定义角色CONNECT 、RESOURCE、DBA。

• connect角色具有一般应用开发人员需要的大部分权限。
• resource角色具有应用开发人员所需要的其它权限，比如建立存储过程、触发器等。这里需要注意的是resource角色隐含了unlimited tablespace系统权限。
• dba角色具有所有的系统权限，及with admin option选项，默认的dba用户为sys和system他们可以将任何系统权限授予其它用户，但是要注意的是dba角色不具备sysdba和sysoper的特权(启动和关闭数据库。
  

  --语法(角色名一般为大写)
  grant 角色1,角色2... to 用户名;

　　10、自定义角色

　　顾名思义就是自己定义的角色，根据自己的需要来定义，一般是dba或具有create role的系统权限的用户来定义。在建立角色时可以指定验证方式(不验证或数据库验证)。

1. 不验证的方式建立角色
   如果角色是公用的，那么一般采用不验证的方式建立。
   

   create role 角色名 not identified;

2. 验证数据库的方式建立角色
   采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必需提供口令。在建立这种角色时，需要为其提供口令。
   

   create role 角色名 identified by 口令;

3. 角色授权
   当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必需为其授予相应的系统权限和对象权限。
   给角色授予权限和给用户授权没有太多区别，但是要注意，系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的。
   

   grant 对象权限 on 数据库对象 to 自定义角色名;

4. 案例
   

   --1、创建自定义角色
   conn system/manager9527;--连接数据库
   create role crud_scott not identified;--创建自定义角色
   
   --2、给自定义角色crud_scott授权
   grant create session to crud_scott;
   grant select on scott.emp to crud_scott;
   grant insert on scott.emp to crud_scott;
   grant update on scott.emp to crud_scott;
   
   3、通过将授权过的角色crud_scott给用户a进行授权
   grant crud_scott to a;

　　11、显示角色信息

　　　通过使用数据字典视图：

• dba_roles（数据库的所有角色信息）
  

  select * from dba_roles;

• role_sys_privs（角色系统权限信息）
  

  select privilege,admin_option from role_sys_privs where role='角色名';

• role_tab_privs（角色对象权限信息）
  

  select * from dba_tab_privs where grantee='角色名';

• user_role_privs（用户具有的角色信息）
  

  select granted_role,default_role from user_role_privs;

　　12、删除自定义角色

• drop role 角色名;

　　13、精细访问控制（简单了解）

　　是指用户可以使用函数、策略实现更加细微的案例访问控制。如果使用精细访问控制，则当在客户端发出sql语句(select,insert,update,delete)时，oracle会自动在sql语句后追加谓语(where子句)，并执行新的sql语句。通过这样的控制，可以使得不同的数据库用户在访问相同的表时，返回不同的数据信息。使用函数或策略是为了更好的保护数据安全性，为不同权限的用户提供不同的安全级别，可有效的保障信息安全。