黑马程序员--Oracle学习_day05

 ----------  android培训 、java培训、期待与您交流！ ----------

1，维护数据的完整性

数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。

        

  · 约束，用于确保数据满足特定的商业规则。在oracle中，约束包括：not  null，

 unique，primary  key，foreign  key ，和check五种。

a, not  null(非空)

如果在列上定义了not  null，那么当插入数据时，必须为列提供数据。

b, unique(唯一)

当定义了唯一约束后，该列值是不能重复的，但是可以为null。

c, primary  key(主键)

用于唯一的标识表行的数据，当定义主键约束后，该列不但不能重复而且  

不能为null。需要说明的是：一张表最多只能有一个主键，但是可以有多

个uniqiue约束。

   d, foreign  key(外键)

  用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具 

有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主 表的主键列存在或是为null。

f, check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在1000~2000之间就会提示出错。

案例：商店售货系统表设计

现在一个商店的数据库，记录客户及其购物情况，由下面三个表组成：

商品goods(商品号goodsId,商品名goodsname,单价unitprice,商品类别 ctegory,供应商provider);

客户customer(客户号customerId,姓名name,地址address,电邮email,性 别,sex,身份证cardId);

购买purchase(客户号customerId,商品号goodsId,购买数据nums);

请用sql语言完成下列功能：           添加约束.txt

1）建表，在定义中要声明：

a) 每个表的主外键

b) 客户的姓名不能为空值

c) 单价必须大于0，购买数据必须在1到30之间

d) 电邮不能够重复

e) 客户的性别必须是男或女，默认是男

              2）商店售货系统表设计案例

如果在建表时忘记建立必要的约束，则可以在建表后使用alter  

table命令为表增加约束。但是要注意：增加not  null约束时，

需要使用modify选项，而增加其它四种约束使用add选项。

a, 每个表的主外码

b, 客户的姓名不能为空值   ——增加商品名也不能为空

c, 单价必须大于0，购买数据必须在1到30之间

d, 电邮不能够重复  ——增加身份证也不重复

e, 客户的性别必须是男或女，默认是男

f, 增加客户的住址只能是‘海淀’、‘朝阳’、‘东城’、‘西

  城’、‘通州’、‘崇文’。

·删除约束

当不再需要某个约束时，可以删除

  sql>alter  table  表名  drop  constraint  约束名称;

在删除主键约束的时候，可能有错误，比如：

alter  table  表名  drop  primary  key;

这是因为如果在刺绣表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项。如：

sql>alter  talble  表名  drop  primary  key 

 cascade;

 ·查询约束信息

1）显示约束信息，通过查询数据字典user_constraints,可以显示

当前用户的约束信息。

sql>select  constraint_name ,constraint_type,status,validated  

from  user_constraints  where  table_name=’表名’;

2）显示约束列

通过查询数据字典视图user_cons_columns,可以显示约束所对应的列信息。

sql>select  column_name,position  from  

user_cons_columns  where  constraint_name=’约束名’;

    3) 也可以通过pl sql  developer查看。

 

·列级定义，是在定义列的同时定义约束

如在department表定义主键约束

sql>create  table  department(dept_id  number(2)  constraint  

pk_department  primary  key, name  varchar2(12), loc  

 varchar2(12));

·表级定义，是指害定义了所有列后，再定义约束，这里需要注意：

not  null约束只能在列级上定义。

 以在建立employee表时定义主键约束和外键约束为例：

sql>create  table  employee(emp_id  number(4),name  

varchar2(15),dept_id  number(2),  

constraint  pk_employee primary  key (emp_id), 

constraint fk_department  foreign  key(dept_id) 

 references  department(dept_id));

 

 

2，管理索引

概述：索引是用于加速数据存取的数据对象，合理的使用索引可以大降低i/o次数，从而提高数据访问性能。索引有很多种我们主要介绍常用的几种：

为什么添加索引后，会加快速度呢？

·创建索引

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

sql>create  index   索引名  on  表名（列名）;

2) 复合索引，是基于两列或是多列的索引，在同一张表上可以有多个索引，但是

要求列的组合必须不同，比如：

sql>create  index  emp_index1  on   emp (ename,job);

sql>create  index  emp_index1  on  emp  (job,ename);

·使用原则

1）在大表建立索引才有意义

2）在where子句或是连接条件上经常引用的列上建立索引

3）索引的层次不要超过4层

·索引的缺点分析

索引有一些先天不足：

1，建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。

2，更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维

持数据和索引的一致性。

3，实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量

   的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

 

比如在如下字段建立索引是不恰当的：

1，很少或从不用的字段

2，逻辑型的字段，如男或女等。综上所述，提高查询效率是以消耗一定   的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否

  优秀的很重要的指标。

·其他索引

按照数据存储方式，可以分为B*树、反射索引、位图索引;

按照索引列的个数分类，可以分为单列索引、复合索引；

按照索引列值的唯一性，可以分为唯一索引和非唯一索引，此外还有函数索引、

     全局索引、分区索引。。。

对于索引：在不同情况我们会在不同的列上建立索引，甚至建立不同种类的索引，请记住，技术是死的，人是活的。 比如：

B*树索引建立在重复值很少的列，而位图索引则建立在重复值多、不同值相对固定的列。

·显示索引信息

1）显示表的所有索引

在同一张表上可以有多个索引，通过查询数据字典视图dba_indexs和user_indexs,可以显示索引信息，其中dba_indexs用于显示数据库所有的索引信息，而user_indexs用于显示当前用户的索引信息。

sql>select  index_name,index_type  from  user_indexes  where  

 table_name=’表名’;

2）显示索引列

通过查询数据字典视图user_index_columns,可以显示索引对应的列的信息。

select  table_name,column_name  from  user_ind_columns  where  index_name=’IND_ENAME’;

    3) 也可以通过pl/sql developer工具查看索引信息。

 

3，管理权限和角色

当刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定的数据库操作，则必须为其授予系统的权限；如果用户要访问其它方案的对象，则必须为其授予对象的权限，为了简化权限的管理，可以使用角色。

权限是指执行特定类型sql命令或是访问其它方案对象的权利，包括系统权限和对象权限两种。

1）系统权限，是指执行特定类型sql命令的权利，它用于控制用户可以执行的一

     个或是一组数据库操作，比如当用户具有create  table权限时，可以在其方案中

  建表，当用户具有create  any  table权限时，可以在任何方案中建表，oracle   提供了100多种系统权限。

常用的有：

create  session  连接数据库

create  vies  建视图

create  procedure  建过程、函数、包

create  cluster  建簇

create  table   建表

create  public  synonym  建同义词

create  trigger  建触发器

2）显示系统权限

通过查询数据字典视图system_privilege_map,可以显示所有系统权限。

sql>select  * from  system_privilege_map  order  by name;

  3) 授予系统权限

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

例如：

1，创建两个用户ken,tom初始阶段他们没有任何权限，如果登录就

   会给出错误的信息。

   sql>create   user  ken  identified  by m123;

sql>create   user  tom  identified  by  m123;

2，给用户ken授权

a,grant  create  session,create  table  to ken  with  admin  

  option;

b, grant  create  view  to ken;

 

3,给用户tom授权

·回收系统权限 

一般情况下，回收系统是dba来完成的，如果其它的用户来回收系统权限，要求该用户必须具有相应系统权限及转授系统权限的选项（with  admin  option）。回收系统权限使用revoke来完成。

当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限级联回收问题?  答：不是级联回收。

stsytem-------------------->ken------------------------->tom

用system执行如下操作：

sql>revoke  create  session  from  ken;  

 

·授予对象权限

在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其它的用户

来操作，则需要用户具有相应的（with  grant  option）权限，从oracle9i开

始，dba用户（sys,system）可以将任何对象上的对象权限授予其它用户，授予

对象权限是用grant命令来完成的。

对象权限可以授予用户，角色，和public。在授予权限时，如果带有withgrant 

option选项，则可以将该权限转授给其它用户。但是要注意with  grant  option选

项不被授予角色。

1，monkey用户要操作scott.emp的表数据，怎样操作？

a,，希望monkey可以查询scott.emp的表数据，怎样操作？

b,，希望monkey可以修改scott.emp的表数据，怎样操作？

c，希望monkey可以删除scott.emp的表数据，怎样操作？

d，有没有更加简单的方法，一次把所有权限授予monkey?

     2，能否对monkey访问权限更加精细控制。（授予列权限）

a,希望monkey只可以修改scott.emp表的sal字段，怎样操作？

sql>grant  update  on  emp(sal)  to monkey;

b,希望monkey只可查询scott.emp表的eanme，sal数据，怎样操作？

sql>grant  select  on  emp(eanme,sal)  to  monkey;

3，授予alter权限

如果black用户要修改scott.emp表的结构，则必须授予alter对象权限。

sql>grant  alter  on emp  to  black;

4，授予execute权限

如果用户想要执行其它方案的包/过程/函数，则须有execute权限。比如：为了让ken可以执行包dbms_transaction, 可以授execute权限。

sql>grant  execute  on  dbms_transaction  to ken;

5，授予index权限

如果想在别的方案的表上建立索引，则必须具有index对象权限。

如为了让black可以在scott.emp上建立索引，就给其index的对象权限。

sql>grant  index  on  scott.emp  to  black;

6，使用with  grant  option  选项

该选项用于转授对象权限，但该选项只能被授予用户，而不能授予角色。

sql>grant  select  on  emp  to  black  with  grant  option;

sql>grant  select   on  scott.emp  to jones;

7，回收对象权限

在oracle9i中，收回对象的权限可以由对象的所有者来未完成，也可以用dba用户（sys,system）来完成。

这里要说明的是：收回对象权限后，用户就不执行相应的sql命令，但是要注意的是对象的权限是否会被级联收回？  答：会联系回收。

如：

scott------------------>black----------------------->jones

select  on emp    select  on  emp      select  on  emp

 

sql>revoke  select   on  emp  form  black;

 

·角色

角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的管理，角色分为预定义和自定义角色两类。

假定有用户a,b,c为了让他们都拥有权限a,连接数据库 b,在scott表上

select ,insert,update。

如果采用采用直接授权操作，则需要进行12次授权。怎么解决呢？

答：首先将create  session ,select   on  scott.emp,insert on  scott.emp,update  

on  scott.emp授予角色，然后将该角色授予a,b,c用户，这样就可能三次授权

搞定。

·预定义角色，是指oracle所提供的角色，每种角色都用于执行一些特定的管理任务，   下面我们介绍常用的预定义角色connect，resource，dba。

1）connect 角色

conn角色具有一般应用开发人员需要的大部分权限，当建立一个用户后，多数情况下，只要给用户授予connect和resource角色就够了，那么connect角色具有哪些系统权限呢？

alter  session

create  cluster

create  database  link

create  session

create  table

create  view

create  sequence

 2)resource角色

resource角色具有应用开必人员需要的其它权限，比如建立存储过程、触发器 

等。这里需要注意的是resource角色隐含了unlimited  tablespace系统权限。

resource角色包含了以下系统权限：

create  cluster

create  indextype

create  table

create  sequence

create  type

create  procedure

create  trigger

3)dba角色

dba角色具有所有的系统权限，及with  admin  option 选项，默认的dba用户为sys和system他们可以将任何系统权限授予其它用户。但是要注意的是dba角色不具备sysdba和sysoper的特权（启动和关闭数据库）。

     ·自定义角色

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

1）建立角色（不验证）

如果角色是公用的角色，可以采用不验证的方式建立角色。

sql>create  role  角色名  not  identified;

2) 建立角色（数据库验证）

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

sql>create  role  角色名 identified  by  xiaoxiang;

    ·角色授权

  当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其

  授予相应的系统权限和对象权限。

  1）给角色授权

 给角色授权和给用户授权没有太多区别，但是要注意，系统权限的unlimited  tablespace 和对象权限的with  grant  option  选项是不能授予角色的。

sql>conn  system/Systadmin2;

sql>grant  create  session  to  角色名  with  admin  option;

sql>conn  scott/tiger;

sql>grant  select  on  scott.emp  to 角色名;

sql>grant  insert ,update ,delete  on  scott.emp  to 角色名;

  

2）分配角色给某个用户

一般分配角色是由dba来完成的，如果要以其它用户身份分配角色，则要求用户必须具有grant  any  role的系统权限。

sql>conn  system/Systadmin2;

sql>grant  角色名  to  along  with  admin  option;

因为这里给了with  admin  option 选项，所以，black可以把system分 配给它的角色分配给别的用户。

3）删除角色

使用drop  role，一般是dba来执行，如用其它用户则要求该用户具有

drop  any  role系统权限。

sql>conn  system/Systadmin2;

sql>drop  role  角色名;

 

      思考：如果角色被删除，那么along（along用角色授权的）是否还可以登录？

答：不能登录，用角色授权的用户，如果角色删除，那么该用户将失

去所有角色包含的权限。

  ·显示角色的信息

a,显示所有角色

sql>select  *  from  dba_roles;

b,显示角色具有的系统权限

sql>select  privilege,admin_option  from  role_sys_privs  where  

    role=’角色名’;

c,显示角色具有的对象权限

sql>select  * from  dba_tab_privs  where  grantee=’DBA’;

d,显示用户具有的角色，及默认角色

   当以用户的身份连接到数据库时，oracle会自动的激活默认的角色，

通过查询数据字典视图dba_role_privs可以显示某个用户具有的所有角色

及当前默认的角色。

sql>select  granted_role,default_role  from  dba_role_privs  where  

    grantee=’用户名’;

·精细访问控制

是指用户可以使用函数、策略实现更加细微的安全访问控制。如果使用精细访问控制，则当在客户端发出sql语句（select ,insert,update,delete）时，oracle全制动在sql语句后追加谓词（where子句），并执行新的sql语句。通过这样的控制，可以使得不同的数据库用户在访问相同表时，返回不同的数据信息，如图：

用户：     scott    black     jones

策略：     emp_access

数据库表emp

如上图所示：通过策略emp_access,用户scott,black,jones在执行相同的sql语句时，可以返回不同的结果，例如，当执行select  ename  from  emp;时，根据实际情况可以返回不同的结果。