Oracle之constraint/index/view/sequence/grant

一，5大约束constraint

1，主键 primary key

2，非空 not null

3，唯一 unique

4，外键 foreign key

5，检查 check

例子：

create  table  t_type_info(

typeId    number(10)   primary  key,
type    varchar2(5)   not null,
typeDescZh    varchar2(50),
typeDescEn    varchar2(50)
);
create  table  t_basic_info  (
contentId   number(10)  primary  key,
name  varchar2(20)  unique,
source  varchar2(3)  not  null,
type  varchar2(5)    references  t_type_info(type),    --外键
sex  varchar2  default  '男'  check(sex  in('男','女')),    --check
srNo  number(10)  check(srNo  between  1  and  30) 
)；

create  table  t_basic_info    (

contentId    number(10),
name    varchar2(20),
source    varchar2(3)  not  null,
type    varchar2(5),        --外键
sex    varchar2    default  '男',
srNo    number(10),

-- 注意：not null约束只能在列级上定义
constraint  info_id  primary  key(contentId),
constraint  info_name  unique(name),
constraint  info_type_pk  foreign  key(type)    references    t_type_info(type),
constraint  info_sex  check(sex  in('男','女'))
)；

约束的基本操作：

1、增加约束

在建表之后可以用alter table来增加constraint，值得注意的是not null只能用modify来修改，其它用add来增加约束。

SQL:   alter table  t_type_info constraint  modify  typeDescZhnot null;

SQL:    alter  table  t_type_info  add  constraint  uk_Desczh  unique(typeDescZh);  

2、删除约束：

SQL:   alter table t_type_info drop constraint  uk_Desczh ;

特殊说明： 在删除主键或外键约束因为存在主从关系，  需要加上cascade选项。

SQL:  alter  table  t_type_info  drop  constraint  info_type_pk   cascade;         

SQL:  alter  table  t_type_info   drop constraint  primary key  cascade;

3、显示约束：

通过查询user_constraints来展示当前用户下的所有约束。

二，索引 index

1，什么时候建立索引呢？索引就相当于字典的目录。

在读取的要求很高的时候。索引虽然读取的效率很高，但是修改的效率却很低，一般不建议建索引。

如果为怎么提高查询速度？那第一个应该就是对关键查询字段建立索引，然后再是SQL的性能优化等等。

2，索引的创建和删除：

SQL:  create index   t_index_info  on  t_basic_info(name) ;

SQL:  drop index   t_index_info ;

三，视图view

1，视图view的作用：可以为外界提供数据，且能保护一些隐私的数据。

      视图可以更新数据，但是一般不用视图来更新数据的，一般只是获取所需要的一部分数据。

      视图上不用建立索引。

2，创建视图：

SQL:  

create  or  replace  view $_t_basic_type_info (
contentId,name,type,typeDescZh
)   as  
select  b.contentId, b.name, b.type, t.typeDescZh 

from  t_basic_info b, t_type_info t 
where  b.type = t.type  and  b.sex = '女' ;

3，视图view的定义原则：

3.1、视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询； 
3.2、在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用 ORDER BY 子句； 
3.3、如果没有为CHECK OPTION约束命名，系统会自动为之命名，形式为SYS_Cn; 
3.4、OR REPLACE选项可以不删除原视图便可更改其定义并重建，或重新授予对象权限。

4，视图的删除：

SQL: drop view  $_t_basic_type_info； 

四，序列Sequence

1，oracle所独有的东西，一般用来做主键用的。

2，创建序列：

SQL:  create  Sequence  seq；
3，序列的用法：

SQL:  select  seq.nextval  from  dual;

SQL:  insert  into t_type_info (seq.nextval,'1','aaa','bbb')
4,   删除序列：

SQL:  drop  Sequence  seq；

五，grant授权

alter user zhangsna account lock; -- 加锁

alter user zhangsan account unlock； -- 解锁
create user lisi identified by zhangsan； 创建用户
grant select on t_basic_info  to lisi； --授增删改查的权限
grant update on t_basic_info  to lisi；
grant insert on t_basic_info  to lisi；
grant delete on t_basic_info  to lisi；
grant execute on my_procedure_name to lisi； -- 授权存储过程

select * from dba_users;  --查询数据库中的所有用户

alter user TEST_SELECT account lock;  --锁住用户
alter user TEST_SELECT account unlock;  --给用户解锁
create user xujin identified by xujin;  --建立用户
grant create tablespace to xujin;  --授权
grant select on tabel1 to xujin;  --授权查询
grant update on table1 to xujin;
grant execute on procedure1 to xujin 授权存储过程
grant update on table1 to xujin with grant option;  --授权更新权限转移给xujin用户，许进用户可以继续授权；
收回权限
revoke select on table1 from xujin1;  --收回查询select表的权限；
revoke all on table1 from xujin;
/*grant connect to xujin;
revoke connect from xujin
grant select on xezf.cfg_alarm to xujin;
revoke select on xezf.cfg_alarm from xujin;*/
select table_name,privilege from dba_tab_privs where grantee='xujin' 查询一个用户拥有的对象权限
select * from dba_sys_privs where grantee='xujin' 查询一个用户拥有的系统权限
select * from session_privs 当钱会话有效的系统权限
角色
create role xujin1;建立xujin1角色
grant insert on xezf.cfg_alarm to xujin1;  --将插入表的信息
revoke insert on xezf.cfg_alarm from xujin1;  --收回xujin1角色的权限
grant xujin1 to xujin ;  --将角色的权限授权给xujin;
 create role xujin2;
grant xujin1 to xujin2;  --将角色xujin1授权给xujin2;
alter user xujin default xujin1,xujin2;  --修改用户默认角色
 DROP ROLE xujin1;删除角色1；
select * from role_sys_privs where role=xujin1;
查看许进1角色下有什么系统权限；
select granted_role,admin_option from role_role_privs where role='xujin2';
查看xujin1角色下面有什么角色权限
select * from role_sys_privs where role='xujin2';
select table_name,privilege from role_tab_privs where role='xujin1';
select * from dba_role_privs where grantee='xujin' 查看用户下面有多少个角色；

三，数据字典表

user_tables;

user_constraints;

user_indexes;
user_views;

例子：

SQL:  select  table_name  from  user_tables;
SQL:  select  view_name  from  user_views;
SQL:  select  constraint_name  from  user_constraints;

SQL:  select  index_name  from  user_indexes;

展示数据字典表的子表：
desc  dictionary；