ORALCE备忘

14人阅读 评论(0) 收藏 举报
分类:

COALESCE (expr1, expr2,...)返回第一个非空的值

TRANSLATE 替换

SELECT REPLACE('accd11','cd1','ef') from dual; 
SELECT TRANSLATE('accd11', 'cd1', 'ef') FROM DUAL;

NULLS FIRST 和 NULLS LAST ORACLE默认NULLS LAST排序

select * from TEST01 T ORDER BY DATA NULLS LAST

ORDER BY 和 CASE WHEN组合(ID4-6置前,再根据DATA排序)

select * from TEST01 T ORDER BY CASE WHEN ID>3 AND ID<7 THEN 1 ELSE 2 END,DATA

IN用法

select * from TEST01 WHERE (ID,DATA) IN (SELECT ID,DATA FROM TEST01 WHERE ID>4)

ESCAPE 转义 见《Oracle查询优化》:p13

select t.*, t.rowid from TEST01 T WHERE GUID LIKE '\_BC%' ESCAPE '\'

外连接条件(*)

SELECT T1.ID,T2.ID,T2.DATA from TEST01 T1 LEFT JOIN TEST02 T2 ON( T1.ID=T2.ID AND T2.DATA=10)
SELECT T1.ID,T2.ID,T2.DATA from TEST01 T1,TEST02 T2 WHERE T1.ID=T2.ID(+) AND  T2.DATA(+)=10

FULL JOIN 全连接,可以用于比较2张表的不同

SELECT T1.DATA,T2.DATA FROM TEST01 T1 FULL JOIN TEST02 T2 ON(T1.ID=T2.ID)
WHERE T1.DATA IS NULL OR T2.DATA IS NULL

约束

1.一般约束
ALTER TABLE TEST001 ADD CONSTRAINTS TEST001_CK_V1 CHECK(V1>0);
2.with check option约束,可以实现复杂约束
CREATE OR REPLACE VIEW VIEW_TEST001 AS
SELECT MAIN_ID,DTIME,V1,V2 FROM  TEST001 WHERE DTIME<=SYSDATE WITH CHECK OPTION;  
INSERT INTO VIEW_TEST001 ( MAIN_ID,DTIME,V1,V2)VALUES(17,SYSDATE+1,1,1);

Mrege详解 p68

删除重复数据(常用有3种) p72

month需建索引,/*+hash_sj*/为什么要加?oracle不是不建议强制索引了吗
DELETE FROM TEST01 A 
       WHERE EXISTS(
             SELECT /*+hash_sj*/ NULL FROM TEST01 B WHERE A.Month=B.MONTH AND B.ROWID>A.ROWID   
       )

q-quote界定符 []、{}、<>、()

SELECT q'('ss\ss)' from dual

REGEXP_COUT  统计字符次数(11g)

SELECT REGEXP_COUNT('ss,aa,dd',',') from dual

wm_concat,LISTAGG p88

1.SELECT MONTH,wm_concat(DATA) FROM TEST01 GROUP BY MONTH 
2.按data排序10g
SELECT MONTH,MAX(DATAS) FROM 
       (SELECT MONTH,wm_concat(DATA) OVER(PARTITION BY MONTH ORDER BY DATA) DATAS FROM TEST01)
GROUP BY MONTH
3.按data排序11g
SELECT MONTH,LISTAGG(DATA,',') WITHIN GROUP (ORDER BY DATA) FROM TEST01 GROUP BY MONTH
SELECT LISTAGG(C) WITHIN GROUP(ORDER BY C) FROM (
     SELECT SUBSTR('ADAMIN',LEVEL,1) C FROM DUAL CONNECT BY LEVEL <=LENGTH('ADAMIN') 
)
REGEXP_LIKE,REGEXP_INSTR,REGEXP_SUBSTR,REGEXP_REPLACE  第5章p74 正则表达式 
取名字的首字母
SELECT REGEXP_REPLACE('Michael Hartstein','([[:upper:]])(.*)([[:upper:]])(.*)','\1.\3') from DUAL
取1,2逗号间的字符窜
SELECT REGEXP_SUBSTR('ss,aa,bb,cc','[^,]+',1,2) FROM DUAL
CREATE OR REPLACE VIEW OP_V001 AS SELECT  'ss,aa,bb,CC' AS A1 FROM DUAL 
SELECT REGEXP_SUBSTR(A1,'[^,]+',1,LEVEL) FROM OP_V001 CONNECT BY LEVEL<=REGEXP_COUNT(A1,',')+1  --11g
SELECT REGEXP_SUBSTR(A1,'[^,]+',1,LEVEL) FROM OP_V001 CONNECT BY LEVEL<=LENGTH(TRANSLATE(A1,','||A1,','))+1  --10g
SELECT GUID,REGEXP_REPLACE(GUID,'[^0-9]','') FROM TEST01 WHERE REGEXP_LIKE(GUID,'[0-9]+')

聚合函数会忽略空值,对avg、count有影响,根据情况需要转空值

SELECT COUNT(DATA),COUNT(1),SUM(DATA),SUM(DATA)/COUNT(1),AVG(COALESCE(DATA,0)) FROM TEST01


执行计划 详解

执行完一条语句后查看
sqlplus>SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(NULL,0,'ALL -NOTE -ALIAS')); -前有空格
--查询某用户的sql
SELECT sql_id, child_number,SQL_TEXT FROM V$SQL A WHERE A.PARSING_SCHEMA_NAME='IC_EMOS2' AND LAST_ACTIVE_TIME>=SYSDATE-1
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor('a00jka5vnc2zh')) --a00jka5vnc2zh为sql_id
ROW_NUMBER,RANK,DENSE_RANK
SELECT MONTH,
       ROW_NUMBER() OVER (PARTITION BY MONTH ORDER BY DATA) AS ROW_NUMBER,
       RANK() OVER  (PARTITION BY MONTH ORDER BY DATA) AS RANK,
       DENSE_RANK() OVER  (PARTITION BY MONTH ORDER BY DATA) AS DENSE_RANK
FROM TEST01



查看评论

Oracle高级管理

通过本课程,掌握Oracle备份恢复以及Oracle高级管理知识。
  • 2016年12月12日 14:57

好用桌面日历备忘软件

  • 2010年07月16日 13:25
  • 956KB
  • 下载

ubuntu16.04LTS备忘录

1.设置root密码 -- sudo passwd 2.安装谷歌拼音输入法 -- Terminal >> 输入sudo apt-get install fcitx-googlepinyi...
  • chenbbxuan
  • chenbbxuan
  • 2017-09-08 22:27:39
  • 123

oralce汇总函数备忘

select nvl(schememoncode,小计),sum(providesum),rank() over (order by sum(providesum) desc) fare_rankfr...
  • pangpangde
  • pangpangde
  • 2006-07-24 18:00:00
  • 803

关于笔记与备忘

永远不要有“系统”的企图,这样的动机会让你强迫不止,继而让你迷失“记录”的原意;实际上我们需要的,仅仅是留下来身边一些有价值的内容,这样的东西其实不会有很多。 最好的记录,是你的大脑,用进废退;任何...
  • l791444672
  • l791444672
  • 2017-11-29 14:46:59
  • 31

oralce 学习笔记

  • 2010年09月26日 21:22
  • 187KB
  • 下载

动态规划之备忘录法

动态规划与分治方法相似,都是通过组合子问题的解来求解原文题。分治方法将问题划分为互不相交的子问题,递归地求解子问题,再将它们的解组合起来,求出原问题的解。与之相反,动态规划应用于子问题重叠的情况,即不...
  • Thare_Lam
  • Thare_Lam
  • 2015-11-04 21:04:58
  • 922

带登录注册功能的material design 云端日历备忘

项目简介:(地址:【https://github.com/panyunyi97/New-Event】)这个小程序本来是因为老是有次组会提过这个需求,所以我就想着把它做出来做好。 后来也感谢很多同学给...
  • CUFE_panda
  • CUFE_panda
  • 2017-03-04 02:22:53
  • 231

备忘提醒小助手V1.0

  • 2010年01月13日 14:04
  • 722KB
  • 下载

oralce的归档oralce的归档

  • 2010年05月11日 00:11
  • 107KB
  • 下载
    个人资料
    等级:
    访问量: 2076
    积分: 152
    排名: 110万+