oracle学习笔记（二）---约束、授予系统权限、索引

（1）在oracle中，约束包括: not null、unique,primary key,foreign key,和check 五种.

■ not null(非空)
如果在列上定义了not null,那么当插入数据时，必须为列提供数据。
■ unique(唯一)
当定义了唯一约束后，该列值是不能重复的.但是可以为null。
■ primary key(主键)
用于唯一的标示表行的数据,当定义主键约束后，该列不但不能重复而且不能为null。
需要说明的是:一张表最多只能有一个主键,但是可以有多个unqiue约束。
■ foreign key(外键)
用于定义主表和从表之间的关系.外键约束要定义在从表上，主表则必须具有主键约束或是unique约束.,当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。
■ check
用于强制行数据必须满足的条件,假定在sal列上定义了check约束,并要求sal列值在1000～2000之间如果不再1000～2000之间就会提示出错。

（2）删除约束
当不再需要某个约束时，可以删除.
alter table 表名 drop constraint 约束名称;
在删除主键约束的时候，可能有错误,比如：
alter table 表名 drop primary key ;
这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上 cascade 选项。
alter table 表名 drop primary key cascade;

（3）列级定义
列级定义是在定义列的同时定义约束。
表级定义
表级定义是指在定义了所有列后，再定义约束.这里需要注意: not null约束只能在列级上定义。

（4）单列索引
单列索引是基于单个列所建立的索引,语法:

create index index_name on table(columnname);
复合索引
复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同,语法:

create index index_name on table(columnname,columnname…);

（5）管理权限和角色—系统权限

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

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

1创建两个用户，并指定密码.
2.给用户ken授权:
3.授予create session 和create table权限时 带with admin option
4. 授予create view 时不带with admin option
1. create user ken identified by ken
2. grant create session,create table to ken with admin option;
3.grandt create view to ken
注意：回收系统权限-----不是级联回收

grant create session,create table to tom; 

revoke create session from ken; 问 tom还能登录？---能

（6）管理权限和角色—对象权限

常用的有：
alter 修改 delete 删除 select 查询 insert 添加
update 修改 index 索引 references 引用 execute 执行
授予对象权限
dba,sys,system 可以将任何对象上的对象权限授予其它用户.授予对象权限是用grant命令来完成的。如果用其它的用户来操作，则需要用户具有相应的(with grant option )权限。

grant 对象权限 on 数据库对象 to 用户名[,角色名][,public] [with grant option]

几个案例:
1.monkey用户要操作scott.emp表,则必须授予相应的对象权限
①希望monkey可以查询scott.emp的表数据，怎样操作?
②希望monkey可以修改scott.emp的表数据，怎样操作?
③希望monkey可以删除scott.emp的表数据，怎样操作?
④有没有更加简单的方法,一次把所有权限赋给monkey?
对象权限可以授予用户，角色，和public.在授予权限时，如果带有with grant option 选项，则可以将该权限转授给其它用户。但时要注意 with grant option选项不能被授予角色，
①grant select on emp to monkey
②grant update on emp to monkey
③grant delete on emp to monkey
④grant all on emp to monkey
①希望monkey只可以修改scott.emp的表的sal字段,怎样操作?
②希望monkey只可查询scott.emp的表的ename,sal数据,怎样操作?
①grant update on emp(sal) to monkey
②grant select on emp(ename,sal) to monkey
注意：回收对象权限-----级联回收

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

（7）管理权限和角色—角色

预定义角色connect,resource,dba。

connect角色具有一般应用开发人员需要的大部分权限,当建立了一个用户后,多数情况下,只要给用户授予connect和resource角色就够了。

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

建立角色(不验证)
如果角色是公用的角色，可以采用不验证的方式建立角色.
create role 角色名 not identified;
㈡建立角色(数据库验证)
采用这样的方式时,角色名、口令存放在数据库中。当激活该角色时，必须提供口令.在建立这种角色时，需要为其提供口令.
create role 角色名 identified by creep
注意：给角色授权
给角色授予权限和给用户授权没有太多区别，但是要注意，系统权限的 unlimited tablespace 和对象权限的with grant option 选项是不能授予角色的。

grant 对象权限 on 数据库对象 to 自定义角色名 [with admin option]

①显示所有角色
select * from dba_roles;
②显示角色具有的系统权限
select privilege,admin_option from role_sys_privs where role=‘角色名';

***************示例

商店售货系统表设计案例
现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：
商品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 customer (customerId char(8) primary key,
name varchar2(20) not null,
address varchar2(30),
email varchar2(30) 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(10) check (nums between 1 and 30),
primary key (customerId,goodsId));

alter table命令为表增加约束.但是要注意: 增加not null约束时,需要使用modify选项,而增加其它四种约束使用add选项。

alter table goods modify goodsName not null;
alter table customer add constraint aaa unique(cardId);
alter table customer add constraint aaabb check (address in('aaa','bbb'));