Oracle学习笔记大全.doc

一：sql语句 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 ccwhere c.owner = upper(table_owner) and c.table_name = upper(table_name)and c.owner = cc.owner and c.constraint_name = cc.constraint_nameorder 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 表名(字段)IXDBA.NET社区论坛 (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 bitmap index sql create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k s