玩转Oracle第4讲

 

 

 

 

 

 

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 goods modify goodsName not null;

alter table customer add constraint aaa unique(cardId);

alter table customer add constraint aaabb check (address in('aaa','bbb'));

 

 

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'));

 

如在department 表定义主键约束

create table department4

(dept_id number(2) constraint pk_department primary key,

name varchar2(12),loc varchar2(12));

--表级定义

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)

references department4(dept_id));

 

 

 

create sequence my_seq             ---创建序列名

 start with 1                       ---从1开始

 increment by 1                     ---每次增长1

 maxvalue 999999999 //NOMAXVALUE(不设置最多值)  ---最大值

 minvalue 1                         ---最小值

 cycle  //NOCYCLE -- 一直累加，不循环     ---循环

 nocache          ---缓存

 从1开始,每次增长1,最大值为999999999,之后又循环从1开始．

 

 

 

 

 

create index 索引名 on 表名(列名)

//emp 的 ename

create index in_ename on emp(ename);

create index emp_idx1 on emp (ename,job);

create index emp_idx1 on emp (job,ename);

 

 

 

 

 

 

 

 

1. create user ken identified by ken

2. ① grant create session,create table to ken with admin option;② grandt create view to ken

 

 

 

 

■显示对象权限

通过数据字段视图可以显示用户或是角色所具有的对象权限.

视图为 dba_tab_privs

sql>conn system/manger

sql>select distinct privilege from dba_tab_privs;

sql>select grantor,owner,table_name,privilege from  dba_tab_privs where grantee='BLAKE';

 

对象权限可以授予用户，角色，和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

 

 

①grant update on emp(sal) to monkey

②grant select on emp(ename,sal) to monkey

授予alter权限

sql>conn scott/tiger

sql>grant alter on emp to blake;

当然也可以用system,sys来完成这件事

授予execute权限

sql>conn system/manager

sql>grant execute on dbms_transaction to ken;

 

 

 

sql>conn scott/tiger@accp

sql>revoke select on emp from blake

请大家思考,jones能否查询scott.emp表数据.

 

 

 

 

 

 

 

sql>conn system/manger

sql>grant create session to 角色名 with admin option

sql>conn scott/tiger@myora1

sql>grant select on scott.emp to 角色名 [with grant option 不可带]

sql>grant insert,update,delete on scott.emp to 角色名

通过上面的步骤，就给角色授权了.