oracle表的基本操作
创建表:
Create table username.table_name(/*username 可以没有*/
Sid number(2) primary key, /*定义主键 可以没有*/
Nane varchar2(20) not null,
Age number(3),
Constraint sid primary key(sid) /*或者添加约束,定义主键*/
) tablespace tablespace_name; /*表空间可以不指定*/
修改表:
添加列:
Alter table table_name add column column_name column_type(n);
A;ter table table_name add (column_name column_type(n),column_name column_type(n));
删除列:
Alter table table_name drop column column_name;
Alter table table_name drop (column_name,column_name);
更改列属性:
Alter table table_name modify column_name new_column_type(n);
更改表名:
Alter table table_name rename to new_table_name;
修改表所在的表空间:
Alter table table_name move new_tablespace;
删除表:
Drop table table_name;
表约束:
创建表时:
Create table table_name(
Sid number(3) primary key,
cid number(3) unique,
aid number(3) not null,
fid number(3) references table_name(priamry key));
Not null:
Alter table table_name modify aid null;
Alter table table_name modify aid not null;
Unique:
Alter table table_name add unique(cid);
Alter table table_name drop unique(cid);
Alter table table_name add constraint constraint_name unique(cid);
Alter table talbe_name drop constraint constraint_name
Primary key:
Alter table table_name add constraint constraint_name primary key(sid,cid);
Alter table talbe_name drop constraint constraint_name;
Foreign key:
Alter table talbe_name add constraint constraint_name foreign key(cid) references table_name(primary key)
Alter table table_name drop constraint constraint_name;
Check:
Alter table table_name add constraint constraint_name check(aid>3);
Alter tabel table_name drop constraint constraint_name;
Constraint:
Alter table table_name disable constraint constraint_name;
Alter table table_name enable constraint constraint_name;
Alter table table_name modify constraint constraint_name enable;
Alter table talbe_name modify constraint constraint_name disable;