oracle笔记一(sql语句方面).docVIP

1.增加主键?? alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN);?? 指定表空间?? alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN) using index? tablespace TABLE_SPACE_NAME;2.增加外键?? alter table TABLE_NAME add constraint FK_NAME? foreign key (TABLE_COLUMN) references KEY_TABLE_NAME;3.使主键或外键失效、生效?? alter table TABLE_NAME? disable(enable) constraint KEY_NAME;4、查看各种约束? select constraint_name,table_name,constraint_type,status from user_constraints;? select constraint_name, constraint_type,search_condition, r_constraint_name? from user_constraints where table_name = upper(table_name) ? select c.constraint_name,c.constraint_type,cc.column_name? ???? from user_constraints c,user_cons_columns cc???? where c.owner = upper(table_owner) and c.table_name = upper(table_name)???? and c.owner = cc.owner and c.constraint_name = cc.constraint_name???? order by cc.position; 5、删除主键或外键? alter table TABLE_NAME? drop constraint KEY_NAME;6、建外键??? 单字段时：create table 表名 (col1? char(8),???????????????????????????????? cno?? char(4)? REFERENCE course);??? 多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段)??? 连带删除选项 (on delete cascade?????? 当指定时，如果父表中的记录被删除，则依赖于父表的记录也被删除???????????? REFERENCE 表名（） on delete cascade;7、删除带约束的表??? Drop table 表名 cascade? constraints; 8：索引管理1.creating function-based indexes sql create index summit.item_quantity on summit.item(quantity-quantity_shipped); 2.create a B-tree index sql create [unique] index index_name on table_name(column,.. asc/desc) tablespace sql tablespace_name [pctfree integer] [initrans integer] [maxtrans integer] sql [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0 sql maxextents 50); 3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows 4.creating reverse key indexes sql create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k sql next 200k pctincrease 0 maxextents 50) tablespace indx; 5.create b