Oracle学习笔记大全

一：sql语句1.增加主键altertableTABLE_NAMEaddconstraintKEY_NAMEprimarykey(TABLE_COLUMN);指定表空间altertableTABLE_NAMEaddconstraintKEY_NAMEprimarykey(TABLE_COLUMN)usingindextablespaceTABLE_SPACE_NAME;2.增加外键altertableTABLE_NAMEaddconstraintFK_NAMEforeignkey(TABLE_COLUMN)referencesKEY_TABLE_NAME;3.使主键或外键失效、生效altertableTABLE_NAMEdisable(enable)constraintKEY_NAME;4、查看各种约束selectconstraint_name,table_name,constraint_type,statusfromuser_constraints;selectconstraint_name,constraint_type,search_condition,r_constraint_namefromuser_constraintswheretable_name=upper('&table_name')selectc.constraint_name,c.constraint_type,cc.column_namefromuser_constraintsc,user_cons_columnsccwherec.owner=upper('&table_owner')andc.table_name=upper('&table_name')andc.owner=cc.ownerandc.constraint_name=cc.constraint_nameorderbycc.position;5、删除主键或外键altertableTABLE_NAMEdropconstraintKEY_NAME;6、建外键单字段时：createtable表名(col1char(8),cnochar(4)REFERENCEcourse);多个字段时,在最后加上ForeignKey(字段名)REFERENCE表名(字段)IXDBA.NET社区论坛连带删除选项(ondeletecascade当指定时，如果父表中的记录被删除，则依赖于父表的记录也被删除REFERENCE表名（）ondeletecascade;7、删除带约束的表Droptable表名cascadeconstraints;8：索引管理1.creatingfunction-basedindexessqlcreateindexsummit.item_quantityonsummit.item(quantity-quantity_shipped);2.createaB-treeindexsqlcreate[unique]indexindex_nameontable_name(column,..asc/desc)tablespacesqltablespace_name[pctfreeinteger][initransinteger][maxtransinteger]sql[logging|nologging][nosort]storage(initial200knext200kpctincrease0sqlmaxextents50);3.pctfree(index)=(maximumnumberofrows-initialnumberofrows)*100/maximumnumberofrows4.creatingreversekeyindexessqlcreateuniqueindexxay_idonxay(a)reversepctfree30storage(initial200ksqlnext200kpctincrease0maxextents50)tablespaceindx;5.createbitmapindexsqlcreatebitmapindexxay_idonxay(a)pctfree30storage(initial200knext200ksqlpctincrease0maxextents50)tablespaceindx;6.changestorageparameterofindexsqlalterindexxay_idstorage(next400kmaxextents100);7.allocatingindexspacesqlalterindexxay_idallocateextent(size200kdatafile'c:/oracle/index.dbf');8.alterindexxay_iddeallocateunused;9、查看索引SQLselectindex_name,index_type,table_namefromuser_indexesorderbytable_name;10、查看索引被索引的字段SQLselect*fromuser_ind_columnswhereindex_name=upper('&index_name');11、创建序列select*fromuser_sequences;createsequenceSEQ_NAMEstartwith1000maxvalue1000incrementby1;altersequenceSEQ_NAMEminvalue50maxvalue100;12、删除重复行updateasetaa=nullwhereaaisnotnull;deletefromawhererowid!=(selectmax(rowid)fromabwherea.aa=b.aa);13、删除同其他表相同的行deletefromawhereexits(select'X'frombwhereb.no=a.no);或deletefromawherenoin(selectnofromb);14、查询从多少行到多少行的记录(可以用在web开发中的分页显示)select*from(selectrownumrow_id,b.*from(selecta.*fromsys_opera)b)whererow_idbetween15and2015、对公共授予访问权grantselecton表名topublic;createpublicsynonym同义词名for表名;16、填加注释commentontable表名is'注释';commentoncolumn表名.列名is'注释';17、分布式数据库,创建数据库链路create[public]databaselinkLINKNAME[connecttoUSERNAMEidentifiedbyPASSWORD][using'CONNECT_STRING']可以在服务器端，也可以在客户端建立，但必须注意，两台服务器之间数据库必须可以互访，必须各有各自的别名数据库18、查看数据库链路select*fromall_db_links;select*fromuser_db_links;查询select*fromTABLENAME@DBLNKNAME;创建远程数据库同义词createsynonymforTABLENAME@DBLNKNAME;操纵远程数据库记录insertintoTABLENAME@DBLNKNAME(a,b)values(va,vb);updateTABLENAME@DBLNKNAMEseta='this';deletefromTABLENAME@DBLNKNAME;怎样执行远程的内嵌过程beginotherdbpro@to_html(参数);end;19、数据库链路用户密码有特殊字符的时候，可以用双引号把密码引起来createpublicdatabaselinkdblink1connecttodb1identifiedby123*456using'db11'20.oracle8中扩充了groupbyrollup和cube的操作。有时候省了你好多功夫的。1下面的语句可以进行总计selectregion_code,count(*)fromaicbs.acc_woff_notifygroupbyrollup(region_code);2对第1个字段小计，最后合计selectregion_code,write_status,count(*)fromaicbs.acc_woff_notifygroupbyrollup(region_code,write_status);----------------------57003570125705--此处小计了570的记录5710105711257112--此处小计了571的记录.....100--此处有总计3复合rollup表达式,只做总计selectregion_code,write_status,count(*)fromaicbs.acc_woff_notifygroupbyrollup(region_code,write_status);4对第1个字段小计，再对第2个字段小计，最后合计selectregion_code,write_status,count(*)fromaicbs.acc_woff_notifygroupbycube(region_code,write_status);----------------------100--此处有总计060--对write_status＝0的小计139--对write_status＝1的小计31--对write_status＝3的小计5705--此处小计了570的记录570035701257112--此处小计了571的记录57101057112....3复合cube表达式,只做总计selectregion_code,write_status,count(*)fromaicbs.acc_woff_notifygroupbycube(region_code,write_status);4下面的语句可以按照rollup不同的字段进行小计selectregion_code,write_status,count(*)fromaicbs.acc_woff_notifygroupbyregion_code,rollup(write_status);21.查询view的创建语句sqlsetlong1000sqlselect*fromuser_viewswhereview_name='MY_VIEW_NAME';orsqlselect*fromall_viewswhereview_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中执行sqlselect'UserId=1233111'||chr(10)||'AccId=13431'||chr(9)||'AccId2=11111'fromdual;24、树形查询createtablezj(bmnumber(8),bmmcvarchar2(20),sjbmnumber(8))insertintozjvalues(1,'aaa',0)insertintozjvalues(11,'aaa1',1)insertintozjvalues(12,'aaa2',1)insertintozjvalues(