我的oracle笔记一（sql语句方面）

--================================================
--==================================== 一.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'''');

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> 对第1个字段小计，最后合计
 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> 复合rollup表达式,只做总计
 select region_code,write_status,count(*) from aicbs.acc_woff_notify
 group by rollup(region_code,write_status);
   
 <4> 对第1个字段小计，再对第2个字段小计，最后合计
 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> 复合cube表达式,只做总计
 select region_code,write_status,count(*) from aicbs.acc_woff_notify
 group by cube(region_code,write_status);
   
 
 <4>下面的语句可以按照rollup不同的字段进行小计
 select region_code,write_status,count(*) from aicbs.acc_woff_notify
 group by region_code,rollup(write_status);
21.查询view的创建语句
  sql>set long 1000
  sql>select * from user_views where view_name=''''MY_VIEW_NAME'''';
   or
 sql>select * from all_views where view_name=''''MY_VIEW_NAME'''';

22、去除数据库中特殊字符
 <1>.字符串字段中含有"''''",如果用来组合sql语句，会造成语句不准确。
     比如：replace(f1,'''''''''''''''','''''''')
 <2>.字符串字段中含有"\t \n",如果用来在c或者c＋＋程序中输出到文件，格式无法保证。
  比如：replace(f2,''''\t'''','''''''')
 <3>.清除换行和回车
  比如: replace(f2,chr(13)||chr(10),'''''''')
23、如何在字符串里加回车或者tab键
  在sqlplus中执行
   sql>select ''''UserId=1233111''''||chr(10)||''''AccId=13431''''||chr(9)||''''AccId2=11111'''' from dual;

24、树形查询
create table zj(
bm     number(8),
bmmc   varchar2(20),
sjbm   number(8)
)

insert into zj values(1,''''aaa'''',0)
insert into zj values(11,''''aaa1'''',1)
insert into zj values(12,''''aaa2'''',1)
insert into zj values(111,''''aaa11'''',11)
insert into zj values(112,''''aaa12'''',11)
insert into zj values(113,''''aaa13'''',11)
insert into zj values(121,''''aaa21'''',12)
i

[1] [2] [3] [4]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18