oracle创建和管理表、完整性约束、创建视图、序列、索引、同义词

表分为系统表和用户表

数据库对象
表（基本存储对象，由行和列组成）、视图（从一个或多个表取来的逻辑子集数据）、序列（自动产生唯一序列号）、索引（提高查询性能）、同义词（简化对于数据对象的访问）

标准的命名规则
表名和列名：
第一个符号必须是字母
1-30个字符
只能包含A-Z，a-z,0-9,_$#
同一个用户不能有重名的表名
ORACLE的保留字不能用来做表明和列名

非标准的命名可以使用特殊字符，用""引起来，如create table "a-1" (int_id int);

唯一定位到数据对象,对象所有者.对象
select * from informix.users;

查询数据字典

用户拥有的user_,系统管理员可以看到的dba_

数据类型
varchar2、char、number、date、long、clob、raw、long raw、blob、bfile、rowid

使用子查询创建表

create table test
as select * from users;

修改表结构

增加一列
修改一列(数据类型，大小，缺省值)
删除一列
为新的列定义缺省值
alter table users
add (job varchar2(10)) default 'unknow';

alter table users
modify （name varchar2(20)）;

alter table users
drop column job;

集中删除列，先设置成unused
alter table test
set unused job;

alter table test
drop unused columns;

删除表
表结构和记录都删除
drop table test;

对象更名

rename test to t1;

清空表
不写日志，DDL语句,只清空记录，表结构还存在
truncate table t1;

注释表

comment on table users is 'theses are user's table';

通过数据字典查看注释信息

all_col_comments
user_col_comments
all_tab_comments
user_tab_comments

约束
维护数据完整性

行、列、参照

列
not null
行
unique
primary key
参照
foreign key 
check

create table test1
(id int not null,
name varchar2(20),
age number,
constraint uk_test unique(id,name))

其中not null是系统命名，uk_test是用户命名

primary key能唯一定义一条记录
alter table test
add constraint pk_test primary key(id);

create table test2
(rid int,
name varchar2(10),
constraint fk_test foreign key(rid) references test(id));

check约束
列的取值范围

alter table test
add constraint ck_test check(name like 'L%');

alter table test
drop constraint ck_test;

alter table test
drop constraint pk_test cascade;

临时把约束disable
alter table test
disable constraint pk_test;

alter table test
enable constraint pk_test;

alter table test
drop column id cascade constraints;

获取constraint信息
select constraint_name,constraint_type from user_constraints;
select * from user_cons_columns;

创建视图

是一个逻辑对象，本身不包括任何数据，是一个命了名的select语句

create view testview
as
select id,name,age from users;

视图的用处

限制数据的存取
使复杂的查询简单化
提供数据独立性
不同的用户可以从不同的角度观察数据

aaa表不存在，强制建立视图
create force view testview2
as 
select * from aaa

视图不能直接用alter修改，必须用create or replace修改

通过视图update，注意改变的是基表的数据
update testview set name='abc';

create or replace view testview2
as select * from users where age>20;

update testview2 set age=18 where id=212;

更新数据后，id为212的数据不在视图testview2中了。为了不让修改，可以用with check option
create or replace view testview2
as select * from users where age>20 with check option;

只读的with read only
create or replace view testview2
as select * from users where age>20
with read only;

删除
drop view testview2;

TOP-N分析

select * from (select id,age from users);

select age,rownum from (select * from users order by age desc ) where rownum<5;

其他数据对象

序列
索引
同义词

创建序列
create sequence testse1
increment by 1
start with 1;

建立序列后其实序号不能修改，其他值可以改

当前值和下一值currval,nextval
select testse1.nextval from dual;

可以用序列值填充到表里
create table testtable1
(id int);

insert into testtable1
values(testse1.currval)

删除序列

drop sequence testse1;

获取序列信息
select * from user_sequences;

索引

一些指针和路径，指向表里的数据。
创建主键和唯一约束时能自动创建索引，其他可手工创建
create index testindex1
on users(name);

不需要创建索引
表很小
不经常用在where条件，表数据经常改变

删除索引
drop index testindex1;

同义词

不是我名下的表，通过创建同义词简化访问
create public synonym t1 for user1.test;

select * from t2;等于
select * from user1.test;

drop public synonym t1;

------------------------------------练习---------------------------------------------

/*创建,删除同义词*/
select * from emp;
create synonym emp_ny for emp;
create public synonym pub_emp_ny for employees;--创建公有同义词
DROP SYNONYM EMP_NY;--删除同义词
DROP PUBLIC SYNONYM PUB_EMP_NY;--删除公有同义词
/*创建,删除序列*/
CREATE SEQUENCE SEQID
INCREMENT BY 1
START WITH 1001
MAXVALUE 1200
MINVALUE 1
CYCLE
NOCACHE ;

DROP SEQUENCE SEQID;--删除序列
/*创建，删除视图*/
CREATE VIEW EMP_VE AS SELECT * FROM EMP;
CREATE OR REPLACE VIEW EMP_VE AS SELECT * FROM EMP WHERE ADDRESS LIKE '%USA%' --有条件创建视图
CREATE FORCE VIEW EMPLOYEE_VE AS SELECT * FROM ABC;--创建带错误视图，意为即使表不存在也创建
DROP VIEW EMP_VE;--删除视图
/*创建、删除索引*/
CREATE INDEX employees_empid ON EMPLOYEES(DATEOFJOINING);--标准索引
CREATE INDEX employees_empname ON EMPLOYEES(ENAME,JOB);--组合索引
CREATE INDEX emp_ename ON EMP (ENAME) REVERSE;--反向键索引
CREATE BITMAP INDEX emp_bit_address ON EMP(ADDRESS);--位图索引
CREATE INDEX abc_name ON ABC(LOWER(aname));
DROP INDEX employees_empid;--删除索引
/*创建索引组织表*/
CREATE TABLE place
(
place_id NUMBER(5)PRIMARY KEY,
pName VARCHAR2(20),
Address VARCHAR2(20),
Station VARCHAR2(10)
)ORGANIZATION INDEX;