oracle经典sql语句整理.docx
- 文档编号:9668454
- 上传时间:2023-02-05
- 格式:DOCX
- 页数:63
- 大小:40.73KB
oracle经典sql语句整理.docx
《oracle经典sql语句整理.docx》由会员分享,可在线阅读,更多相关《oracle经典sql语句整理.docx(63页珍藏版)》请在冰豆网上搜索。
oracle经典sql语句整理
ORACLE常用经典SQL语句
常用的数据字典及SQL
1.oracle将多行变成一行
selectd.datagetsource_id,wmsys.wm_concat(d.catagory_id)fromdatagetsource2catagorydgroupbyd.datagetsource_id
2、oracle将表数据导入导出
C:
\DocumentsandSettings\Administrator>expirdp/irdpfile=e:
/数据库备份/irdp_bak_200911020927_forUpdate.dmpfull-y
导出数据库的语句,最后的是full-y,不是full=y,如果用后面也可以导出,但它会把库中的所有用户都导出来。
3、oracle将树形sql语句最后加上根节点id
CONNECT_BY_ROOT
selectmunity_id,
c2c.parent_community_id,
level,
CONNECT_BY_ROOTcommunity_idroot
fromcommunity2communityc2c
startwithmunity_idin
(selectcommunity_id
fromcommunitycy
wherewithdrawn='N')
connectbypriormunity_id=c2c.parent_community_id
4、查询数据库所有包含item_id字段的表的名称
selectt.TABLE_NAMEfromuser_tab_columnstwheret.COLUMN_NAMElike'%ITEM_ID%'groupbyt.TABLE_NAME;
5、查询数据库回收站中被删除的表
select*fromrecyclebinwhereoriginal_name='WEBPUBLISHAPPLY';
6、闪回数据库某张表
altertableWEBPUBLISHAPPLYenablerowmovement;
flashbacktableWEBPUBLISHAPPLYtotimestampto_timestamp('2010-08-0300:
00:
00','yyyy-mm-ddhh24:
mi:
ss');
altertableWEBPUBLISHAPPLYdisablerowmovement;
7、oracle-9i将一行记录中部分数据复制到另一行记录中
updateaddressbooka
set(a.a_sex,a.a_brithday,a.a_brithplace,a.a_address,a.a_phone)=
(selectd.a_sex,d.a_brithday,d.a_brithplace,d.a_address,d.a_phone
fromaddressbookd
whered.a_id='5')
wherea.a_id='3';
8、将表中a字段记录为1的行中的b字段全部改为2
updateaddressbookd
setd.a_brithplace=(selectr.a_brithplacefromaddressbookrwherer.a_id='9')
whered.a_address=(selectc.a_addressfromaddressbookcwherec.a_id='5')
9、删除表且删除表所占的空间,且不能回退
truncatetablebooks
10、oracle事务
1)savepointa保存事务点
2)rollbacktoa回退到事务点a,且取消a之后的操作
3)rollback取消全部操作
4)只读事务
settransactionreadonly;--1(会话1)
updateempsetsal=3000whereename='smith';--2(会话2)
selectsalfromempwhereename='smith';--3(会话1)
当在1处设置了只读事务后,紧接着2处在3之前修改了该数据,但是在3处查看的数据还是1处这个时间点的数据,也就是修改前的数据
提示:
在readonly事务中,是不允许进行增删改操作的
5)顺序事务
settransactionisolationlevelserializable;--1(会话1)
updateempsetsal=3000whereename='smith';--2(会话2)
selectsalfromempwhereename='smith';--3(会话1)
在3处的查询语句只会查询到2处修改前的数据
11、分组函数
selecti.item_type_id,i.submitter_id,count
(1)fromitemi
wherei.item_type_id='ebook'ori.item_type_id='rwsk'
groupbyrollup(i.item_type_id,i.submitter_id);
selecti.item_type_id,i.submitter_id,count
(1)fromitemi
wherei.item_type_id='ebook'ori.item_type_id='rwsk'
groupbycube(i.item_type_id,i.submitter_id);
12、同时利用两个或者多个分组函数
selectr.type,r.metaapplication_id,count
(1)fromresourceshowtyper
groupbygroupingsets(r.type,r.metaapplication_id);
13、自动执行内连接
selectc2i.collection_id,item_id,i.item_type_idfromcollection2itemc2i
naturaljoinitemi
14、函数
1)返回字符的ASCII码
selectascii('a')fromdual;
2)将ASCII码转换为字符
selectchr(65)fromdual;
3)链接两个字符
selectconcat('ab','cd')fromdual;
4)单词首字母大写
selectinitcap('helloworld')fromdual;
5)查看字符串a在字符串b中的位置instr(b,a,n,m),n为起始位置,初始为1,m为出现次数,初始为1
selectinstr('helloworld','l','1')fromdual;
6)返回字符串的长度
selectlength('helloworld')fromdual;
7)将条目id左边填充0直到字符长度达到10位
selecti.item_id,lpad(i.item_id,10,'0')fromitemi;
selecti.item_id,rpad(i.item_id,10,'0')fromitemi;
8)将字符串中左边连续出现的'浙江大学'的任意组合去掉
selectc.name,ltrim(c.name,'浙江大学')fromcommunityc;
9)将字符串变为首字母大写或者全小写或者全部大写
selectnls_initcap(n'helloworld')fromdual;
selectnls_lower(n'SQLServer')fromdual;
selectnls_upper(n'SQLServer')fromdual;
10)字符串替换
selectreplace('浙江大学出版社','浙江','湖北')fromdual;
11)截取子字符串
selectsubstr('浙江大学出版社',1,4)fromdual;
15、时间
1)在系统时间往后推100个月
selectadd_months(sysdate,100),sysdatefromdual;
selectadd_months(sysdate,-100),sysdatefromdual;
selectadd_months(sysdate,-1000000000000),sysdatefromdual;
2)返回当前时区对应日期格式的时间
selectcurrent_datefromdual;
3)返回当前日期格式的时间
selectcurrent_timestampfromdual;
4)从当前时间中抽取年月日,不能抽取时分秒
selectextract(yearfromsysdate)fromdual;
selectextract(monthfromsysdate)fromdual;
selectextract(dayfromsysdate)fromdual;
16、使用标量变量接收查询数据
declare
v_comtype_namecommunitytype.name%type;
begin
selectnameintov_comtype_name
fromcommunitytype
wherecommunity_type_id=&comtype_id;
dbms_output.put_line('资源库名称:
'||v_comtype_name);
end;
17、使用记录变量接受数据
--oracle自定义类型种类:
--1、子类型
--subtypecc_numisnumber(16,2);
--2、记录类型
--typeemp_record_typeisrecord
--(
--enamevarchar2(10),
--jobvarchar2(9)
--);
--emp_recordemp_record_type;
--3、rowtype类型变量
--emp_recordemp%rowtype;
declare
typeemp_record_typeisrecord(
idmunity_type_id%type,
namecommunitytype.name%type
);
emp_recordemp_record_type;
begin
selectmunity_type_id,ct.name
intoemp_record
fromcommunitytypect
wheremunity_type_id='ebook';
dbms_output.put_line('资源库ID:
'||emp_record.id);
dbms_output.put_line('资源库名称:
'||emp_record.name);
end;
18、操纵数据
declare
v_community_type_namecommunitytype.english_name%type:
='&englishname';
begin
updatecommunitytypet
sett.english_name=v_community_type_name
wheremunity_type_id='AcademicRes';
commit;
end;
19、数据库游标
1)sql%isopen游标是否已经打开
2)sql%foundsql语句是否已经执行成功
begin
savepointa1;
updatecommunitytypet
sett.english_name='333'
wheremunity_type_id='AcademicRes';
ifsql%found
then
commit;
dbms_output.put_line('成功');
else
rollbacktoa1;
dbms_output.put_line('失败');
endif;
end;
3)sql%notfound
4)sql%rowcount返回sql影响的行数
20、两表关联更新数据
updateaasetplace=(selectplacefrombbwherebb.name=aa.nameandrownum=1)
whereexists(select1frombbwherebb.name=aa.name);
21、小数点前补零
SELECTRTrim(To_Char(1/100,'FM99999999990.9999'),'.')FROMdual;
22、数据闪回
通过增加asoftimestamp的语法,就可以到undo表空间中查找到5分钟之前的记录前镜像,使用它我们就可以很轻易的并且迅速的将记录恢复:
insertintojss_tb1
select*fromjss_tb1asoftimestampsysdate-5/1440
SELECT*
FROMtablea
WHERELENGTH(id)!
=LENGTHB(id);
length求得是字符长度,lengthb求得是字节长度
23、创建分区表
createtablet
(t1date)
partitionbyrange(t1)
(partitionny_200611valueslessthan
(to_date('2006-12-01','yyyy-mm-dd')),
partitionny_200612valueslessthan
(to_date('2007-01-01','yyyy-mm-dd')),
partitionny_maxvalueslessthan(maxvalue)
)
/
24、创建临时表
CREATEGLOBALTEMPORARYTABLEadmin_work_area
(startdateDATE,
enddateDATE,
classCHAR(20))
ONCOMMITDELETEROWS;
25、查看表空间的名称及大小:
SQL>selectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_size
fromdba_tablespacest,dba_data_filesdwheret.tablespace_name=d.tablespace_namegroupbyt.tablespace_name;
26、查看表空间物理文件的名称及大小:
SQL>selecttablespace_name,file_id,file_name,round(bytes/(1024*1024),0)total_spacefromdba_data_filesorderbytablespace_name;
27、查看回滚段名称及大小:
SQL>selectsegment_name,tablespace_name,r.status,
(initial_extent/1024)InitialExtent,(next_extent/1024)NextExtent,
max_extents,v.curextCurExtentFromdba_rollback_segsr,v$rollstatv
Wherer.segment_id=v.usn(+)orderbysegment_name;
28、如何查看某个回滚段里面,跑的什么事物或者正在执行什么sql语句:
SQL>selectd.sql_text,a.name
fromv$rollnamea,v$transactionb,v$sessionc,v$sqltextd
wherea.usn=b.xidusnandb.addr=c.taddrandc.sql_address=
d.addressandc.sql_hash_value=d.hash_value
anda.usn=1;
(备注:
你要看哪个,就把usn=?
写成几就行了)
29、查看控制文件:
SQL>select*fromv$controlfile;
30、查看日志文件:
SQL>colmemberformata50
SQL>select*fromv$logfile;
31、如何查看当前SQL*PLUS用户的sid和serial#:
SQL>selectsid,serial#,statusfromv$sessionwhereaudsid=userenv('sessionid');
32、如何查看当前数据库的字符集:
SQL>selectuserenv('language')fromdual;
SQL>selectuserenv('lang')fromdual;
33、怎么判断当前正在使用何种SQL优化方式:
用explainplan产生EXPLAINPLAN,检查PLAN_TABLE中ID=0的POSITION列的值。
SQL>selectdecode(nvl(position,-1),-1,'RBO',1,'CBO')fromplan_tablewhereid=0;
34、如何查看系统当前最新的SCN号:
SQL>selectmax(ktuxescnw*power(2,32)+ktuxescnb)fromx$ktuxe;
35、在ORACLE中查找TRACE文件的脚本:
SQL>selectu_dump.value||'/'||instance.value||'_ora_'||
v$process.spid||nvl2(v$process.traceid,'_'||v$process.traceid,null)||'.trc'"TraceFile"fromv$parameteru_dumpcrossjoinv$parameterinstancecrossjoinv$processjoinv$sessiononv$process.addr=v$session.paddrwhereu_dump.name='user_dump_dest'and
instance.name='instance_name'andv$session.audsid=sys_context('userenv','sessionid');
SQL>selectd.value||'/ora_'||p.spid||'.trc'trace_file_name
from(selectp.spidfromsys.v_$mystatm,sys.v_$sessions,
sys.v_$processpwherem.statistic#=1and
s.sid=m.sidandp.addr=s.paddr)p,(selectvaluefromsys.v_$parameterwherename='user_dump_dest')d;
36、如何查看客户端登陆的IP地址:
SQL>selectsys_context('userenv','ip_address')fromdual;
如何在生产数据库中创建一个追踪客户端IP地址的触发器:
SQL>createorreplacetriggeron_logon_trigger
afterlogonondatabase
begin
dbms_application_info.set_client_info(sys_context('userenv','ip_address'));
end;
37、REM记录登陆信息的触发器
CREATEORREPLACETRIGGERLOGON_HISTORY
AFTERLOGONONDATABASE--WHEN(USER='WACOS')--ONLYFORUSER'WACOS'
BEGIN
insertintosession_history
selectusername,sid,serial#,AUDSID,OSUSER,ACTION,SYSDATE,null,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,machine,PROGRAM
fromv$sessionwhereaudsid=userenv('sessionid');
END;
38、查询当前日期:
SQL>selectto_char(sysdate,'yyyy-mm-dd,hh24:
mi:
ss')fromdual;
39、查看所有表空间对应的数据文件名:
SQL>selectdistinctfile_name,tablespace_name,AUTOEXTENSIBLEfromdba_data_files;
40、查看表空间的使用情况:
SQL>selectsum(bytes)/(1024*1024)asfree_space,tablespace_name
fromdba_free_spacegroupbytablespace_name;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 经典 sql 语句 整理
![提示](https://static.bdocx.com/images/bang_tan.gif)