创建
1.表空间
create tablespace spaceName
datafile 'C:\spaceName.dbf'
size 20M
autoextend on next 5M
maxsize 50M;
alter tablespace spaceName
add datafile 'C:\spaceName2.dbf'
size 10M;
2.表1
create table tableName(
id number(4) [primary key],
name varchar(4) [not null],
sex char(2) [check (sex in('男','女'))],
age number(3) [default '18'],
tableId number(4) references tableName2(table2Id)
)[tablespace spaceName]
create table tableName2(
table2Id number(4) [primary key]
)[tablespace spaceName]
2.1 添加新列(已经创建表字段想加新的字段)
alter table tableName add city varchar(10);
3. 添加约束 不给约束起名字oracle会自动起一个名字 建议手动起名字
alter table tableName
add [constraint pk_id] primary key(id[,name[,...]]);
alter table tableName
add [constraint ck_sex] check (sex in('男','女'));
alter table tableName modify name not null;
alter table tableName add unique(name);
alter table tableName add [constraint fk_tableId] foreign key(tableId) references tableName2(table2Id);
4.创建索引
create index idx_name on tableName(name) tablespace spaceName;
create bitmap index idx_sex on tableName(sex) tablespace spaceName;
5.创建视图
create [or replace] view view_name as select name,sex,age from tableName;
修改
1.表空间
alter database datafile 'C:\spaceName.dbf' autoextend on next 5M maxsize 100M;
alter tablespace old_tablespaceName rename to new_tablespaceName;
alter database datafile 'C:\spaceName.dbf' resize 100M;
alter tablespace new_tablespaceName offline/ online/ read only/ read write;
alter database datafile 'C:\spaceName.dbf' online | offline | offline drop;
2.表
alter table tableName rename column old_col_name to new_col_name;
alter table tableName modify city varchar2;
alter table tableName rename to new_col_name;
rename tableName to new_col_name;
alter table tableName move tablespace newSpace;
select table_name,tablespace_name from user_tables where table_name = 'tableName';
3. 索引
alter index idx_name rename to new_idx_name;
alter index new_idx_name coalesce [ deallocate unused];
alter index new_idx_name rebuild;
4. 更新视图
删除
1.表空间
alter tablespace spaceName drop datafile 'C:\spaceName2.dbf';
drop tablespace spaceName |[including contents]|[including contents and datafiles];
2.表
drop table tableName [cascade constraint][purge] ;
alter table tableName drop column city;
alter table tableName drop(city,city1,city2,...)
3.约束
drop constraint constraintName;
alter table tableName drop constraint pk_id;
alter table tableName drop constraint ck_sex;
alter table tableName modify name null;
alter table tableName drop unique(name);
alter table tableName drop constraint fk_tableId;
drop index indexName ;
4.索引
drop index 索引名;
5.视图
drop view viewName ;