my,＄,没有,命运,蚂蚁,名誉,my

一.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

sql

>2

sql

sql

sql

sql

>3

>4

sql

sql

>5

sql

sql

>6

sql

7.allocating index space

sql

>8

>9

SQL

>10

SQL

11、创建序列

select * from user_sequences;

create sequence SEQ_NAME start with 1000

maxvalue 1000 increment by 1;

alter sequence SEQ_NAME minvalue 50 maxvalue 100;

12、删除重复行

update a set aa=null where aa is not null;

delete from a where rowid!=

(select max(rowid) from a b where a.aa=b.aa);

13、删除同其他表相同的行

delete from a where exits

(select ‘X’ from b where b.no=a.no);

或

delete from a where no in (select no from b);

14、查询从多少行到多少行的记录(可以用在web开发中的分页显示)

select * from ( select rownum row_id,b.* from (select a.* from sys_oper a) b )

where row_id between 15 and 20

15、对公共授予访问权

grant select on 表名 to public;

create public synonym 同义词名 for 表名;

16、填加注释

comment on table 表名 is ‘注释’;

comment on column 表名.列名 is ‘注释’;

17、分布式数据库,创建数据库链路

create [public] database link LINKNAME

[connect to USERNAME identified by PASSWORD]

[using 'CONNECT_STRING']

可以在服务器端，也可以在客户端建立，但必须注意，两台服务器之间

数据库必须可以互访，必须各有各自的别名数据库

18、查看数据库链路

select * from all_db_links;

select * from user_db_links;

查询 select * from TABLENAME@DBLNKNAME;

创建远程数据库同义词

create synonym for TABLENAME@DBLNKNAME;

操纵远程数据库记录

insert into TABLENAME@DBLNKNAME (a,b) values (va,vb);

update TABLENAME@DBLNKNAME set a=’this’;

delete from TABLENAME@DBLNKNAME;

怎样执行远程的内嵌过程

begin

otherdbpro@to_html(参数);

end;

19、数据库链路用户密码有特殊字符的时候，可以用双引号把密码引起来

create public database link dblink1 connect to db1 identified by “123*456″ using ‘db11′

20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。

>1

select region_code,count(*) from aicbs.acc_woff_notify

group by rollup(region_code);

>2

select region_code,write_status,count(*) from aicbs.acc_woff_notify

group by rollup(region_code,write_status);

———————-

570 0 3

570 1 2

570 5 –此处小计了570的记录

571 0 10

571 1 2

571 12 –此处小计了571的记录

…..

100 –此处有总计

>3

select region_code,write_status,count(*) from aicbs.acc_woff_notify

group by rollup(region_code,write_status);

>4

select region_code,write_status,count(*) from aicbs.acc_woff_notify

group by cube(region_code,write_status);

———————-

100 –此处有总计

0 60 –对write_status＝0的小计

1 39 –对write_status＝1的小计

3 1 –对write_status＝3的小计

570 5 –此处小计了570的记录

570 0 3

570 1 2

571 12 –此处小计了571的记录

571 0 10

571 1 2

….

>3

select region_code,write_status,count(*) from aicbs.acc_woff_notify

group by cube(region_code,write_status);

>4

select region_code,write_status,count(*) from aicbs.acc_woff_notify

group by region_code,rollup(write_status);