玩转Oracle（6）

||-------  维护数据完整性  -------||

【约束】

//约束
not null //非空
unique  //唯一 不能重复，但可以为空
primary key //主键
foreign key //外键
check  //满足条件

//商店售货系统表设计案例（1）

//goods 商品表
goodsid  //商品号
goodsname //商品名
unitprice //单价
category //商品类别
provider //供应商

SQL> create table goods(
  2  goodsid char(8) primary key,--主键
  3  goodsname varchar2(30) ,
  4  unitprice number(10,2) check (unitprice>0),--单价必须>0
  5  category varchar2(8),
  6  provider varchar2(30));

//customer 客户表
customerid //客户号
name  //姓名
address  //住址
email  //电邮
sex  //性别
cardid  //身份证

SQL> create table sustomer(
  2  customerid char(8) primary key,--主键
  3  name varchar2(30) not null,--不为空
  4  address varchar2(50),
  5  email varchar2(50) unique,--不能重复
  6  sex char(2) default '男' check(sex in ('男','女')),--默认为男，性别为男或女
  7  cardid char(18));

//purchase 购买表
customerid //客户号
goodsid  //商品号
nums  //购买数量

SQL> create table purchase(
  2  custmoerid char(8) references customer(customerid),
  3  goodsid char(8) references goods(goodsid),
  4  nums number(5) check(nums between 1 and 30));

//要求如下：
1.每个表的主外键；
2.客户的姓名不能为空值；
3.单价必须大于0，购买数量必须1-30之间；
4.电邮不能够重复；
5.客户的性别是男or女，默认为男；

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

//增加not null 约束需要使用modify
//增加其他四种约束使用add

1.增加商品名也不能为空
alter table goods modify goodsname not null;

2.增加身份证也不重复
alter table customer add constraint cardunique unique(cardid);

3.增加客户的地址只能是 '海淀','朝阳','东城','西城','通州','崇文'
alter table customer add constraint addresscheck check(address in ('海淀','朝阳','东城','西城','通州','崇文'));

//删除约束
alter table 表名 drop constraint 约束名

//删除主键约束
alter table 表名 drop primary key cascade;

//列级定义
定义列的同时定义约束

//表级定义
定义了列之后，再定义约束

【索引】

是用于加速数据存取的数据对象

//单列索引
单列索引是基于单个列所建立的索引
create index 索引名 on 表名（列名）
create index customer_index on customer(name);

//复合索引
复合索引是基于两列或者多列的索引，在同一张表上可以有多个索引，但要求列的组合必须不同
create index customer_index on customer(name,address);

//使用索引原则
1.在大表上建立索引
2.在where子句或是连接条件上经常饮用的列建立索引
3.索引的层次不要超过4层

//索引的缺点
1.建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引
2.更新数据的时候，系统必须有额外的时间来同时对索引进行更新，以维持数据和索引的一致性

//显示表的所有索引
select index_name,index_type from user_indexs where table_name='emp';

【权限】

1.创建两个用户king，tom
create user king identified by 123;
create user tom identified by 123;

2.给用户king授权
1)grant create session,create table to king with admin option;
2)grant create view to king; //不允许继续派发

3.给用户tom授权
1)grant create session,create table to tom with admin option;

4.撤销授权
revoke create session from king; //但是tom用户仍然可以登录，确定权限回收不是级联回收

//查询该用户所拥有的所有表
select table_name from user_tables;

//查看该用户能够访问的表
select table_name from all_tables;

//查看所有方案所有表（需要拥有dba权限）
select table_name from dba_tables;

//通过dba_sys_privs可以显示用户所具有的系统权限

//通过dba_tab_privs可以显示用户具有的对象权限

//通过dba_col_privs可以显示用户具有的列权限

//通过dba_role_privs可以显示用户所具有的角色
select * from dba_role_privs where grantee='SCOTT';

//如何查询一个角色包括的权限？（系统权限，角色权限）
select * from system_privilege_map order by name;

【角色】

//预定义角色

dba  //具有所有的系统权限，默认的dba用户为sys,system
connect  //一般应用开发人员需要的大部分权限
 1.alter session
 2.create cluster
 3.create database link
 4.create session
 5.create table
 6.create view
 7.create sequence

resource
 1.create cluster
 2.create indextype
 3.create table
 4.create type
 5.create procedure
 6.create trigger

//自定义角色

//建立角色（不验证）
create role 角色名 not identified;

//建立角色（数据库验证）
create role 角色名 identified by valen;

//给角色授权
sysdba：grant create session to role1 with admin option;
scott：grant select,insert,update,delete on scott.emp to role1;

//分配角色
grant 角色名 to 用户名 with admin option

//删除角色（需要dba权限）
drop role 角色名

//oracle究竟有多少种角色？
select * from dba_roles;

//查询oracle中所有的对象权限，一般是dba
select distinct privilege from dba_tab_privs;

//查询数据库的表空间
select tablespace_name from dba_tablespaces;