期望目标： ①掌握维护oracle数据完整性的技巧 ②理解索引概念，会建立索引【优化数据库的第一步就是看有没有建立索引】 ③管理oracle的权限和角色
维护数据的完整行 - 介绍 数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。 在oracle中，数据完整性可以使用约束、触发器、应用程序(过程、函数) 三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能 ，所以作为维护数据完整性的首选。 【约束：数据不合理我就不让你放进来】
【约束】 约束用于确保数据库满足特定的商业规则。 在oracle中，约束包括： ①not null【非空】, ②unique【唯一】, ③primary key【主键】, ④foreign key 【外键】, ⑤check 【检查，如年龄大于5岁小于100岁】
-----------------------------------------------------------------
【not null】非空 如果在列上定义了not null, 那么插入数据时，必须为列提供数据。
【unique】唯一 当定义了唯一约束后，该列值是不能重复的。但是可以为null
【primary key】主键 用于唯一的表示行的数据，当定义主键约束后，该列不但不能重复而且 不能为null。 需要说明的是：一张表最多只能有一个主键，但是可以有多个unique约束。
【foreign key】外键 用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须 具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须 在主表的主键列存在或是为null。
【check】检查 用于强智行数据必须满足的条件，假定在sal列上定义了check约束，并要求 sal列值在1000~2000之间如果不在1000~2000之间就会提示出错。
-----------------------------------------------------------------
商店售货系统表设计案例【项目要成功数据库是很重要的】 现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成： 商品goods(商品号goodsId，商品名goodsName，单价unitprice， 商品类别category，供应商provider)； 客户customer(客户号customerId，姓名name，住址address，电邮 email，性别sex，身份证cardId); 购买purchase(客户号customerId，商品号goodsId，购买数量nums); 请用SQL语言完成下列功能： 1 建表，在定义中要求声明： ① 每个表的主外键； ② 客户的姓名不能为空值； ③ 单价必须大于0，购买数量必须在1到30之间； ④电邮不能够重复； ⑤客户的性别必须是男或者女，默认是男
【不知道这个为什么会有问题？匪夷所思】 create table customer( customerId char(8) primary key, --主键 name varchar2(30) not null, --不为空 address varchar2(50), email varchar2(50) unique, --不能重复 sex char(2) defaut '男' check(sex in ('男','女')), --1个char只能存半个汉字 cardId char(18) --第2代身份证就18位 );
【下面这个和上面明明一样，为什么呢？真是活见鬼】 create table customer( customerId char(8) primary key, --主键 name varchar2(50) not null, --不为空 address varchar2(50), email varchar2(50) unique, --不能重复 sex char(2) default '男' check (sex in ('男','女')), --1个char只能存半个汉字 cardId char(18) --第2代身份证就18位 );
create table purchase ( customerId char(8) references customer(customerId), goodsId char(8) references goods(goodsId), nums number(10) check (nums between 1 and 30) );
【商品售货系统表设计案例2】 如果在建表时忘记建立必要的约束，则可以在建表后使用 alter table 命令为表增加约束。但是要注意：增加not null约束时，需要使用 modify选项，而增加其他四种约束使用add选项。 ①每个表的主外码； ②客户的姓名不能为空值；--增加商品名也不能为空 在plsql中进行可视化操作时最简洁的~ alter table goods modify goodsName not null; ③单价必须大于0，购买数量必须在1到30之间； ④电邮不能够重复；--增加身份证也不能重复 alter table customer add constraint cardunique unique(cardId); ⑤客户的性别必须是 男 或者 女，默认是男 ⑥ 增加客户的住址只能是‘海淀’、‘朝阳’、‘东城’、‘西城’... alter table customer add constraint addresscheck check (address in ('东城','西城'));
【删除约束】 但不再需要某个约束时，可以删除。 alter table 表名 drop constraint 约束名称; 【特别说明】 在删除主键约束的时候，可能有错误(某些表的外键可能对被删除的主键有所依赖)，比如： alter table 表名 drop primary key; 这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项，如像 alter table 表名 drop primary key cascade;
【显示约束信息】 ①显示约束信息 通过查询数据字典视图uesr_constraints，可以显示当前用户所有的约束的信息。 select constraint_name,constraint_type,status,validated from user_constraints where table_name='GOODS'; ②显示约束列 通过查询数据字典视图user_cons_columns，可以显示约束所对应的表列信息。 select column_name,position from user_cons_columns where constraint_name='ADDRESSCHECK'; ③当然也有更容易的方法，直接用pl/sql developer查看即可。
【表级定义和列级定义】 【列级定义】 列级定义是在定义列的同时定义约束。 如在department 表定义主键约束 create table department4( dept_id number(2) constraint pk_department primary key, name varchar2(12), loc varchar2(12) ); 【表级定义】 表级定义是指在定义了所有列后，再定义约束。这里需要注意： not null 约束只能在列级上定义。 以在建立employee2表时定义主键约束和外键约束为例： create table employee2( 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) reference department4(dept_id) );
【管理索引】 索引适用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而 提高数据访问性能。索引有很多种，我们主要介绍常用的几种：
为什么添加了索引后，会加快查询速度呢？ 略，见图
【单列索引】 单列索引是基于单个列所建立的索引，比如： create index 索引名 on 表名(列名)
【复合索引】 复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的 组合必须不同，比如：【筛选范围大的放在后面，高效】 create index emp_idx1 on emp (ename,job); create index emp_idx1 on emp (job,ename); 【索引缺点分析】 索引有一些先天不足： ①建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引； ②更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持 数据和索引的一致性。 实践表明，不恰当的索引不但于是无补，反而会降低系统性能。因为大量索引 在进行插入、修改和删除操作时比没有索引花费更多的系统时间。
比如在如下字段建立索引应该是不恰当的： ①很少或从不引用的字段； ②逻辑型的字段，如男或女（是或否）等。综上所述，提高查询效率是 以消耗一定的系统资源为代价的，索引不能盲目建立，这是考验一个DBA 是否优秀的很重要的指标。
【其他索引】 按照数据存储方式，可以分为B*树、反向索引、位图索引； 按照索引列的个数分类，可以分为单列索引、复合索引； 按照索引列值的唯一性，可以分为唯一索引和非唯一索引。 此外还有函数索引，全局索引，分区索引...
对于所引还有： 在不同的情况我们会在不同的列上建立索引，甚至建立不同种类的索引， 技术是死的，人是活的。比如： B*树索引建立在重复值很少的列上，而位图索引则建立在重复值很多、 不同值相对固定的列上。
【显示表的索引】 在同一张表上可以有多个索引，通过查询数据字典视图 dba_indexs 和 user_indexs，可以显示索引信息。其中dba_indexs用于显示数据库 所有的索引信息，而user_indexs用于显示当前用户的索引信息： select indexs_name,index_type from user_indexs where table_name='表名';
【显示索引列】 通过查询数据字段视图 user_ind_columns，可以显示索引对应的列的信息 select table_name,column_name from user_ind_columns where index_name='IND_ENAME'; 也可以通过pl/sql developer工具查看索引信息。
【管理权限和角色 - 介绍】 这一部分我们主要看看oracle中如何管理权限和角色，权限和角色的区别在哪里 当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定 的数据库操作，则必须为其授予系统的权限；如果用户要访问其他方案的对象，则必须 为其授予对象的全乡。为了简化权限的管理，可以使用角色。
-----------------------------------------------------------------------------
【系统权限介绍】 系统权限是指执行特定类型sql命令的权利。他用于控制用户可以执行的一个或是一组 数据库操作。必如当用户具有create table 权限时，可以在其方案中建表，当用户具有 create any table 权限时，可以在任何方案中建表。oracle提供了100多种系统权限。 常用的有： create session 连接数据库 create table 建表 create view 建视图 create public synonym 建同义词 create procedure 建过程、函数、包 create trigger 建触发器 create cluster 建簇
【显示系统权限】 oracle提供了100多种系统权限，而且oracle的版本越高，提供的系统权限就越多， 我们可以查询数据字典视图system_privilege_map，可以显示所有系统权限。 select * from system_privilege_map order by name;
【授予系统权限】 一般情况，授予系统权限是由dba完成的，如果用其他用户来授予系统权限， 则要求该用户必须具有grant any privilege 的系统权限在授予系统权限时， 可以带有with admin option选项，这样，被授予权限的用户或是角色还可以 将该系统权限授予其他的用户或是角色。 1.创建两个用户ken,tom。初始阶段他们没有任何权限，如果登录就会给出 错误的信息： create user ken identified by ken; 2.给用户 ken 授权 grant create session,create table to ken with admin option; grant create view to ken 3.给用户 tom 授权 我们可以通过ken给tom授权，因为with admin option是加上的。 当然也可以通过dba给tom授权，我们就用ken给tom授权： ①grant create session,create table to tom; ok! ②grant create view to tom; 不OK!
【回收系统权限 -- 不是级联回收】 一般情况下，回收系统权限时dba来完成的，如果其他的用户来回收系统权限，要求 该用户必须具有相应系统权限及传授系统权限的选项(with admin option)。回收 系统权限使用revoke来完成。 当回收了系统权限后，用户就不能执行相应的操作了， 但是请注意，系统权限级联回收问题？ system -----------------> ken ---------------> tom (create session) (create session) (create session) 用system执行如下操作： revoke create session from ken; 请思考，tom 还能登陆吗？能！！
【对象权限】 指访问其他方案对象的权利，用户可以直接访问自己方案的对象， 但是如果要访问别的方案的对象，则必须具有对象的权限，比如 smith用户要访问scott。emp表(scott：方案，emp：表) 则必须自爱scott.emp 表上具有对象的权限 常用的有： alter 修改 delete 删除 select 查询 insert 添加 upate 修改 index 索引 references 引用 execute 执行
【显示对象权限】 通过数据字段视图可以显示用户或是角色所具有的对象权限。 视图为dba_tab_privs conn system/manager select distinct privilege from dba_tab_privs; select grantor,owner,table_name,privilege from dba_tab_privs where grantee='BLAKE';
【授予对象权限】 在oracle9i前，授予对象权限是由对象的所有者来完成的，如果用其他的用户 来操作，则需要用户具有相应的(with grant option)权限，从头oracle9i 开始，dba用户(sys,sytem)可以将任何对象上的对象权限授予其他用户。 授予对象权限时用grant命令来完成的。 对象权限可以授予用户，角色，和public。在授予权限时，如果带有 with grant option 选项，则可以将该权限传授给其他用户。 但是要注意 with grant option 选项不能被授予角色， 1.monkey用户要操作scott.emp表，则必须授予相应的对象权限 ① 希望monkey可以查询scott.emp 的表数据，怎样操作？ grant select on scott.emp to monkey; ② 希望monkey可以修改scott.emp 的表数据，怎样操作？ grant update on scott.emp to monkey; ③ 希望monkey可以删除scott.emp的表数据，怎样操作？ grant delete on scott.emp to monkey; ④ 有没有跟家简单的方法，一次吧所有权限赋给monkey？ grant all on scott.emp to monkey;
能否对monkey访问权限更加精细的控制(授予列权限) ①希望monkey只可以修改scott.emp表的sal字段，怎样操作？ grant update on emp(sal) to monkey; ②希望monkey只可查询scott.emp表的ename，sal数据，怎样操作？ grant select on emp(ename,sal) to monkey;
3.授予alter权限 如果black用户要修改scott.emp表的结构，则必须授予 alter 对象权限 conn scott/tiger grant alter on emp to black; 当然也可以用system，sys来完成这件事
4.授予execute权限 如果用户想要执行其他方案的包/过程/函数，则须有execute 权限。 比如为了让ken可以执行包 dbms_transaction,可以授予execute权限 conn system/manager grant execute on dbms_transaction to ken;
5.授予index权限 如果想在别的方案的表上建立索引，则必须具有index对象权限， 如为了让black可以在scott。emp上建立索引，就给其index的对象权限 conn scott/tiger grant index on scott.emp to black
6.使用with grant option 选项 该选项用于传授对象权限。但是该选项只能被授予用户，而不能授予角色。 conn scott/tiger; grant select on emp to black with grant option; conn black/shunping; grant select on scott.emp to jones;
【回收对象权限】 在oracle9i中，收回对象的权限可以由对象的所有者来完成， 也可以用dba用户(sys, system)来完成 这里要说明的是：收回对象权限后，用户就不能执行相应的sql命令， 但是要注意的是对象的权限是否会被级联回收？ 如： scott -------------> black ---------------> jones (select on emp) (select on emp) (select on emp)
conn scott/tiger@accp revoke select on emp from black; 请大家思考，jones能否查询 scott.emp 表数据？不能(和系统权限相反)！
-------------------------------------------------------------------
【管理权限和角色-角色】 角色就是相关权限的命令集合，使用角色的主要目的就是为了简化权限的而管理。 假定有用户a,b,c为了让他们都拥有权限 ①连接数据库 ②在scott.emp表上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
①connect角色 connect角色具有一般应用开发人员需要的大部分权限，当建立了一个用户后， 多数情况下，只要给用户授予connect和resource角色就够了，那么connect角色 具有哪些系统权限呢？ alter session create cluster create database link create session create table create view create sequence
②resource角色 resource角色具有应用开发人员所需要的其他权限，比如建立存储过程、触发器等。 这里需要注意的是resource角色隐含了unlimitied tablespace 系统权限。 resource角色包含以下系统权限： create cluster create indextype create table create sequence create type create procedure create trigger
③dba角色 dba 角色具有所有的系统权限，及 with admin option 选项。默认的 dba 用户为 sys 和 system。他们可以将任何系统权限授予其他用户。但是要注意的是 dba 角色不具备 sysdba 和 sysoper 的特权(启动和关闭数据库)
【自定义角色】 顾名思义就是自己定义的角色，根据自己的需要来定义。一般是dba来建立，如果用的 别的用户来建立，则需要具有create role的系统权限。在建立角色时可以指定验证方式 (不验证，数据库验证等) ①建立角色(不验证) 如果角色是公用的角色，可以采用不验证的方式建立角色。 create role 角色名 not identified; ②建立角色(数据库验证) 采用这样的方式时，角色名、口令存放在数据库中。当激活该角色时，必须提供口令。 在建立这种角色时，需要为其提供口令 create role 角色名 identified by bruce;
【角色授权】 当建立角色时，角色没有任何权限，为了使得角色完成特定任务，必须为其授予相应的 系统权限和对象权限。 ①给角色授权 给角色授予权限和给用户授权没有太多区别，但是要注意，系统权限的unlimited tablespace 和对象权限的with grant option 选项是不能授予角色的。 conn system/manager; grant create session to 角色名 with admin option; conn scott/tiger@myora1; grant select on scott.emp to 角色名 grant insert,update,delete on scott.emp to 角色名 通过上面的步骤，就给角色授权了。
②分配角色给某个用户 一般分配角色是由dba来完成的，如果要以其他用户身份分配角色，则要求 用户必须具有grant any role 的系统权限。 conn system/manger grant 角色名 to black with admin option; 因为我给了with admin option 选项 所以，black可以吧 system 分配个他的角色分配给别的用户。
--------------------------------------------------------- SQL> show user; User is "SYS" SQL> create role myrole1 not identified; Role created SQL> grant create session to myrole1 with admin option; Grant succeeded SQL> conn scott/m123; Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 Connected as scott SQL> grant select on emp to myrole1; Grant succeeded SQL> grant update on emp to myrole1; Grant succeeded SQL> conn system/876521963 Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 Connected as system SQL> grant myrole1 to y3w; Grant succeeded
SQL> conn y3w/m123; Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 Connected as y3w SQL> select * from scott.emp; 成功取得scott.emp表的数据。
------------------------------------------------------------------
【删除角色】 使用drop role，一般是dba来执行，如用其他用户则要求该用户具有drop any role 系统权限 conn system/manager drop role 角色名
如果角色被删除，那么被赋予该角色的用户是否还可以继 续使用这种角色带来的特权？否【皮之不存，毛将焉附】 ①删除myrole1角色【一般是system来做的】 drop role myrole1;【角色已丢弃】
【显示角色的信息】 ①显示所有角色 select * from dba_roles; ②显示角色具有的系统权限【貌似有点儿问题】 select privilege,admin_option from role_sys_privs where role='CONNECT'; ③显示角色具有的对象权限 通过查询数据字典视图dba_tab_privs可以查看角色具有的对象权限或是列 的权限。 ④显示用户具有的角色，及默认角色 当以用户的身份连接到数据库时，oracle会自动激活默认的角色，通过查询数据 字典视图dba_role_privs 可以显示某个用户具有的所有角色及当前默认的角色 【如查看 SCOTT 具有怎样的角色】 select granted_role,default_role from dba_role_privs where grantee='SCOTT';
【精细访问控制】 是指用户可以使用函数、策略实现更加细微的安全访问控制。如果使用精细访问控制， 则当在客户端发出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;时，根据实际情况 可以返回不同的结果
