ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [转]sql语句-001

[转]sql语句-001

原创 Linux操作系统 作者:31597359 时间:2019-07-20 14:48:02 0 删除 编辑
01

一.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 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 bitmap index
sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k
sql> pctincrease 0 maxextents 50) tablespace indx;

<6>.change storage parameter of index
sql> alter index xay_id storage (next 400k maxextents 100);

7.allocating index space
sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');

<8>.alter index xay_id deallocate unused;

<9>、查看索引
SQL>select index_name,index_type,table_name from user_indexes order by table_name;
<10>、查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name');

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8000572/viewspace-62275/,如需转载,请注明出处,否则将追究法律责任。

下一篇: [转]sql语句-002
请登录后发表评论 登录
全部评论

注册时间:2006-10-07

  • 博文量
    120
  • 访问量
    77510