玩转Oracle 实战教程.ppt

* *画图说明 * * * * * * * * * * * * 可以建表演示一下. * * 可以建表演示一下. * 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 sqlconn system/manger sqlselect distinct privilege from dba_tab_privs; sqlselect grantor,owner,table_name,privilege from dba_tab_privs wh