Oracle 数据库对象

准备数据：

赋予scott创建各种资源的权限。
grant resource to scott;

create table staff (id varchar2(2),name varchar2(10),sex varchar2(2),corpid varchar2(2));
insert into staff(id,name,sex,corpid) values('01','李成','男','01');
insert into staff(id,name,sex,corpid) values('02','赵兰','女','05');
insert into staff(id,name,sex,corpid) values('03','刘丰','男','03');
insert into staff(id,name,sex,corpid) values('04','钱峰山','男','04');
insert into staff(id,name,sex,corpid) values('05','张凯','男','02');
insert into staff(id,name,sex,corpid) values('07','李想','男','04');
insert into staff(id,name,sex,corpid) values('06','刘丽','女','02');
insert into staff(id,name,sex,corpid) values('09','周恒久','男','04');
insert into staff(id,name,sex) values('08','孙小萍','女');

create table corp (id varchar2(2),name varchar2(10));
insert into corp(id,name) values('01','办公室');
insert into corp(id,name) values('02','财务科');
insert into corp(id,name) values('03','质量科');
insert into corp(id,name) values('04','生产部');
insert into corp(id,name) values('05','销售部');
insert into corp(id,name) values('06','后勤科');

01、表
⑴定义表
create table student (
    id number(8) primary key,
    name varchar2(20) constraint student_name_nn not null,
    sex number(1) constraint staff_check_sex check(sex=1 or sex=0);
    age number(3),
    register_date date,
    grade number(2) default 1,
    class number(4),
    email varchar2(50)),
    constraint student_name_email_uni unique(email, name)
    constraint student_class_fk foreign key (class) references class(id)
);
 
create table class (
    id number(4) primary key,
    name warchar2(20) not null
);

⑵约束表
列约束，写在列名后，不能将多个约束联合起来，只有表级约束可以组合多个约束条件。
五种约束：非空，检查，唯一，主键，外键。外键约束中被引用的列必须是主键。

⑶修改表
alter table add(addr varchar2(100));
alter table student drop (addr);
alter table student modify (addr varchar2(150));
alter table student drop constraint student_pk;
alter table student constraint student_pk primary key(id);
alter table student drop constraint student_class_fk;
alter table student add constraint student_class_fk foreignkey (class) references class(id);

⑷删除表
drop table student;
删除后，检查列表：
select * from tab;
会发现有一些名为BIN$....==$0 的表，这是10g的新特性“闪回表”。
purge recyclebin;  --用来删除回收站中所有的表
purge table class; --用来删除class表
flashback table student to before drop; --用来闪回被删除的表
drop table student purge;  --彻底删除表

⑸系统对象
desc dictionary;
select table_name from dictionary where table_name like 'USER%';

desc dba_constraints;
desc all_constraints;
desc user_constraints;
select constraint_name,constraint_type from user_constraints where table_name='STAFF';

desc user_tables;
select table_name from user_tables;

02、索引
添加主键约束/唯一约束时自动建立索引
索引，高效读，低效写，慎重使用索引

create unique index staff_unique_index on staff(id);
create index idx_student_email on student(email);
drop index idx_student_email;

desc user_indexes;
select index_name from user_indexes;

03、视图
将常用子查询创建为视图，简化查询，保护私有数据；但也增加了维护成本。
create or replace view myview
as
    select empno,ename,job,mgr,sal,comm,deptno from emp where deptno>20;

scott 权限不足，执行本语句前，先赋权限：
grant create view to scott;

执行视图：
select * from myview;

create or replace view myview
as
    select * from abc where c>10 whith check option ;

带有check option，则插入数据时，如果c小于10，则不允许插入。


create or replace view scview(id,name,sex,corpid,company)
as
select s.id,s.name,s.sex,c.id,c.name from staff s,corp c where s.corpid=c.id;

在这里，视图中不能有重名的列，否则创建失败。
create or replace view scview
as
select s.id,s.name,s.sex,c.id as corpid,c.name as corp from staff s,corp c where s.corpid=c.id;

只读视图
create or replace view staff_ro_view
as
    select id,name from staff with read only ;

存储视图对象的系统表：
dba_views;
all_views;
user_views;

desc user_views;
select view_name from user_views;

查看视图文本，视图名必须用大写：
select text from user_views where view_name='STAFF_RO_VIEW' ;

04、同义词
一个用户访问另一个用户数据对象时，一定要加上用户名前缀：
select deptno,dname,loc from scott.dept;
为了使用非前缀形式，需要创建同义词。同义词分两类：
公共同义词：dba建立
私有同义词：当前用户建立(默认)

create synonym dept for scott.dept;
create public synonym t_staff for tom.t_staff;

sys 授权scott创建同义词
grant create any synonym to scott;

scott为tom用户创建同义词:
create synonym t_staff for tom.t_staff;

sys:创建公共同义词
drop sysnonym t_staff;
create public synonym t_staff for tom.t_staff;

存储同义词的系统表：
desc dba_synonyms
desc user_synonyms;
select synonym_name,table_name,table_owner from user_synonyms;


05、序列
用来产生唯一的不间断的数字序列，一般用来做主键

create sequence myseq
start with 1      --开始，默认为1
increment by 1 --递增，默认为1
order                --排序
nocycle;            --禁止循环

select myseq.nextval from dual;
select myseq.currval from dual;
drop sequence myseq;

使用了nextval之后，currval才会有效。

create table auto(a number,b varchar2(10));
insert into auto values(myseq.nextval,'test...');

alter sequence myseq increment by 3;
可以修改序列递增量，不能修改当前值。

desc dba_sequences; /all_sequences/user_sequences
select sequence_name,sequence_owner from dba_sequences where sequence_owner='TOM';


06、表空间
一个表空间对应多个数据文件。
conn / as sysdba;

create tablespace myts
datafile 'c:/oracle/product/10.2.0/oradata/orcl/myts.dbf' size 10M;
alter user tester default tablespace myts;

grant unlimited tablespace,dba to tester;
conn tester/test;

create table tre(t char(10),re number);
create table ty(t char(10),y varchar2(10)) tablespace users;

通过OEM管理：http://localhost:1158/em/
OEM－表空间，编辑myts，myts脱机，用于备份。
OEM—表－方案：TESTER，对象tre/ty，可以检查它们的表空间。
OEM－创建表空间－本地管理：该区每个数据文件保留一个位图，跟踪记录块的使用状态，空闲状态。每个位对象一个/组块。
字典管理用于老旧版本中，现在一般采用本地管理。