oracle笔记 以及oracle的自增长问题解决

oracle笔记  以及oracle的自增长问题解决

 

oracle的自增长

1. 建立序列

 create sequence aaa increment by 1 start with 1;

2. 建立一张表测试

 create table users (id number(4) primary key , name varchar2(30));

3. 如何使用

 insert into users values(aaa.nextval,'顺平');

 

 

第一部分: 维护数据的完整性

 数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在oracle中，数据完整性可以使用约束、触

发器、应用程序(过程、函数)三种方法来实先，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作

为维护数据完整性的首选.

 

 这部分的目标

 1)了解约束的分类，作用，及实现方法
 2）了解约束的各个状态、作用、及改变约束状态的方法
 3）了解如何处理违反约束规则的数据

1.1约束的介绍

 约束用于确保数据库数据满足特定的商业规则。在oracle中，约束包括: not null、unique,primary

key,foreign key,和check 五种.

 1.1.1not null

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

 1.1.2unique(唯一)

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

 1.1.3primary key(主键)

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

 这里需要说明的是:

 一张表最多只能有一个主键,但是可以有多个unqiue约束.

 1.1.4foreign key(外键)

 用于定义主表和从表之间的关系.外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当

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

 如emp 和 dept

 在emp表的deptno的外键约束 －－－》 dept 的 deptno

 insert into emp (empno,ename,deptno) values(1234,'土拨鼠',50);

 insert into dept values(50,'间谍部','埃及');
 我们看看有什么效果..

 1.1.5check

 用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000~2000之间

 如果不再1000~2000之间就会提示出错

 1.2维护约束

 一般情况下,维护约束由表的所有者来完成的，如果用其它的用户来维护约束，则要求该用户具有alter any

table权限或是在该表上具有alter 对象的权限。在使用create table 建表的同时可以定义约束，当然也可在建表后

，使用alter table命令来增加、修改、删除约束.

 1.2.1定义约束

 1)列级定义

 列级定义是在定义列的同时定义约束.

 

 如在department 表定义主键约束

 
create table deparment (dept_id number(2) primary key identify(1,1),
    name varchar2(40),
    address varchar2(40))
   

 insert into deparment values(1,'爬虫部','地洞');
 insert into deparment values(2,'飞禽部','天空');

 insert into deparment  values(3,'鲨鱼部','大海');

 insert into deparment  values(4,'走兽部','陆地');

 insert into deparment  values(5,'走兽部2','地狱');
 1.2.2增加约束

 如果再建表时忘记建立必要的约束，则可以再建表后使用alter table命令为表增加约束.但是要注意: 增加

not null约束时,需要使用modify选项,而增加其它四种约束使用add选项.

 
 1)增加not null约束

 比如部门必须有名字,否则数据就没有任何意义,

 //modify->修改
 //name 就是字段名
 alter table deparment modify name not null;

 2)增加unique约束

 同一单位部门名称不应该相同,为了限制部门名的唯一性,应该再name列上定义唯一约束

 //constraint :是关键字，不能修改
 alter table deparment add constraint cccddd unique(name); 

 
 3)增加check约束

 假定所有员工只有四个地点 （'地洞',天空,大海，陆地),为了防止加入错误,应该在address列上定义check

约束.

alter table deparment add constraint aaa3 check
(address in ('地洞','天空','大海','陆地'));

 

 1.24删除约束

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

 alter table deparment drop constraint 指定的约束名

 1.3禁止和激活约束

 禁止约束就是使约束失效,激活约束就是使约束重新生效.

 1.3.1禁止约束

 alter table 表名  disable novalidate constraint 指定的约束名

 1.3.2激活约束

 alter table 表名 enable novalidate constraint 指定的约束名

 1.4显示约束信息

 1.4.1显示约束信息
 通过查询数据字典视图user_constraints,可以显示当前用户所有的约束的信息

 select CONSTRAINT_NAME,constraint_type,SEARCH_CONDITION from user_constraints where

table_name='DEPARMENT';

 1.4.2显示约束列

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

 select column_name,position from user_cons_columns where constraint_name='AAA3'

 小结:

 1)掌握五中约束的作用 
 2）如何在建表时定义索引，并了解列级定义和表级定义索引的区别
 3）在见表后如何增加，修改，删除约束

 4)如何禁止和激活约束，显示约束的相关信息

 
第二部分 管理权限和角色

 这一部分我们主要看看oracle种如何管理权限和角色，权限和角色的区别在那里

 当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定的数据库操作，则必须

为其授予系统的权限；如果用户要访问其它方案的对象，则必须为其授予对象的权限.为了简化权限的管理，可以使

用角色。这里我们会详细的介绍

 

 2.1权限和角色

 2.1.1系统权限

 系统权限是指执行特定类型sql命令的权利.它用于控制用户可以执行的一个或是一组数据库操作.比如当用

户具有create table权限时，可以在其方案中建表,当用户具有create any table权限时,可以在任何方案中建

表.oracle提供了100多中系统权限

 常用的有：

 
 create session  连接数据库
 create table    建表
 create view     建视图
 create procedure  建立 过程，包，函数
 create trigger  建立触发器

 

 

 2.1.2对象权限

 是指访问其它方案对象的权利,用于控制用户对其它方案对象的访问.用户可以直接访问自己方案的对象，但

是如果要访问别的方案的对象，则必须具有对象的权限. 比如smith用户要访问scott.emp表(scott:方案,emp :表)则

必须在scott.emp不表上具有对象的权限

 常涉及到的对象权限有:
 
 alter
 delete
 insert
 select
 update
 index
 execute     (用于执行过程，触发器..)
 
 2.1.3角色
 
 角色就是相关权限的命令集合,使用角色的主要目的就是为了简化权限的管理.假定有用户a,b,c为了让他们

都拥有权限

 1连接数据库
 2在scott.emp表上select,insert,update

 如果采用直接授权操作，则需要进行12次授权,

 我们如果采用角色就可以简化：

 

 
 可以作出一个示意图给大家看看

 

 

 

 2.1.4用户组public//?????

 权限、角色可以授予各个单独的用户，也可以授予用户组public ,这样会使得所有的用户都可以执行与该权

限或是角色相关的数据库操作..

 oracle的所有用户都可以查询数据字典视图all_xxx,user_xxx,原因就是在这些数据字典的select权限被授

予了public

 

 

 

 2.2管理系统权限

 系统权限是执行特定的sql命令的权利,oracle提供了100多种系统权限

 

 

 2.2.1授予系统权限

 一般情况，授予系统权限是有dba完成的，如果用其它用户来授予系统权限，则要求该用户必须具有grant

any privilege
的系同权限

 create user zhanglang identified by m123;

 //给 用户 蟑螂 连接数据库和建表 的权限
 grant create session,create table to zhanglang;

 在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统

权限授予其它的用户或是角色,

 我们建立两个用户 blake , jones .初始阶段他们没有任何权限，如果登陆就会给出错误的信息

 

 

 2.2.2显示系统权限

 1)显示所有的系统权限
 oracel提供了100多系统权限，而且oracle的版本越高，提供的系统权限就越多,我们可以查询数据字典视图

system_privilege_map，可以显示所有系统权限(

 
 2)显示用户具有的系统权限

 通过查询数据字典视图dba_sys_privs,可以显示用户或是角色所具有的系统权限.

 
 3)显示当前用户具有的系统权限，及该权限是否能够转让.

 

 2.2.3回收系统权限

 一般情况下，回收系统权限是dba来完成的，如果要以其它的用户来回收系统权限，要求该用户必须具有相

应系统权限及转授系统权限的选项(with admin option）。回收系统权限使用revoke来完成

 

 2.3管理对象权限

 
 是指访问其它方案对象的权利,用于控制用户对其它方案对象的访问.用户可以直接访问自己方案的对象，但

是如果要访问别的方案的对象，则必须具有对象的权限. 比如smith用户要访问scott.emp表(scott:方案,emp :表)则

必须在scott.emp不表上具有对象的权限

 常涉及到的对象权限有:
 
 八种。
 

 2.3.1授予对象权限

 

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

 

 

 2)授予execute权限

 

 3)授予index权限

 如果想在别的方案的表上建立索引,则必须具有index对象权限,如为了让black可以在 scott.emp上建立索引

，就必须给其index的对象权限

 

 4)授予列权限

 

 5)使用with grant option选项

 

 2.3.2显示对象权限

 1)显示对象权限
 
 2）显示列权限

 
 

 2.3.3收回对象的权限

 

 2.4管理角色

 角色就是相关权限的命令集合,使用角色的主要目的就是为了简化权限的管理.假定有用户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用户，这样就可以三次授权搞定.

 角色分为预定义和自定义角色两类.

 2.4.1预定义角色

 

 1)connect角色

 
 2)resource角色
 

 
 

 3)dba角色

 

 4)还有些其它的角色

 exp_full_datebase:
 imp_full_database;
 ....大家可以参考其它书籍

 

 

 

 2.4.2自定义角色

 

 1)建立角色(不验证)
 
 如果角色是公用的角色，可以采用不验证的方式建立用户.

 create role public_role not identified;

 2)建立角色(数据库验证)

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

要为其提供口令

 create role private_role identified by shunping

 2.4.3角色授权

 当建立角色时，角色没有任何权限,为了使得角色完成特定任务，必须为其授予相应的系统权限和对象权限.

 1)给角色授权

 给角色授予权限和用户授权没有太多区别，但是要注意，系统权限的 unlimited tablespace 和对象权限的

with grant option 选项是不能授予角色的.

 sql>conn system/manger
 sql>grant create session to public_role with admin option

 sql>conn scott/tiger@ebook//考虑
 sql>grant select on scott.emp to public_role

 sql>grant insert,update,delete on scott.emp to private_role;

 
 2）分配角色给某个用户

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

系统权限或是角色with admin option的选项

 sql>conn system/manager
 sql>grant public_role ,private_role to blake with admin option

 因为我给了with admin option

 所以

 blake可以将这两个角色授予其它用户

 sql>conn black/shunping
 sql>grant public_role,private_role to jones;

 2.4.4激活和禁止角色(不用演示，说说即可 ）

 激活角色指使角色具有的权限生效,禁止角色是指使角色具有的权限失效.

 1)禁止角色
 
 sql>set role none;

 这是当前会话用户所具有的角色全部失效

 2)激活角色(不验证）如果在建立角色时没有使用任何验证方式,则可以直接激活角色.

 sql>set role public_role

 3)激活角色(验证)如果在建立角色时使用任何验证方式,则在激活角色时，需要提供口令

 sql>set role private_role identified by shunping

 

 2.4.5修改角色

 修改角色时用alter role完成的，一般是dba来执行，如用其它用户则要求该用户具有alter any role系统

权限

 1)验证角色---->不验证角色

 sql>conn system/manager
 sql>alter role private_role not identified;

 2)不验证角色---->验证角色

 sql>conn system/manager

 sql>alter role public_role identified by shunping

 这样public_role就是验证角色

 2.4.6删除角色

 使用drop role,一般是dba来执行，如用其它用户则要求该用户具有drop any role系统权限

 sql>conn system/manger
 sql>DROP ROLE  private_role

 2.4.7显示角色信息

 1)显示所有角色

 通过查询数据字典视图dba_roles可以显示数据库所包含的所有角色（预定义和自定义)

 sql>select * from dba_roles;

 2)显示角色具有的系统权限

 通过查询数据字段dba_sys_privs或是role_sys_privs，可以显示角色具有的系统权限

 sql>select privilege,admin_option,from role_sys_privs where role='PUBLIC_ROLE';

 3)显示角色具有的对象权限

 通过查询数据字典视图role_sys_privs可以查看角色具有的对象权限或是列的权限

 4）显示用户具有的角色，及默认角色

 当以用户的身份连接到数据库时，oracle会自动的激活默认的角色,通过查询数据字典视图dba_role_privs

可以显示某个用户具有的所有角色及当前默认的角色

 sql>select granted_role,default_role from dba_role_privs where grantee='BLAKE';

 5)显示当前会话激活的角色

 这里我们要明确一个概念，尽管用户可以具有多个角色，但是只能执行被激活角色所对应的权限操作。我们

可以通过数据字段session_roles，显示当前会话所激活的角色.

 sql>conn blake/shunping
 sql>select * from session_roles;

 sql>select owner,table_name,privilege from role_tab_privs where role='PUBLIC_ROLE';