Oracle易忘知识点记录
1、SQL Select语句完整的执行顺序:
CREATE INDEX index_nm ON table_nm(col_nm) TABLESPACE tablespace_nm ONLINE;
3、其他
在sql脚本中创建存储过程时,存储过程部分代码结束后,在其下一行加一个反斜杠 / ,表示存储过程结束,防止oracle将下面的其他sql语句当成存储过程的一部分。
在某个字段后面追加内容:
UPDATE table_nm T SET T.IP=T.IP||',127.0.0.1' WHERE 筛选条件;
4、oracle中对于''和null的判断必须使用 IS NULL才能正确得到返回值,使用< = >都是false;使用like进行模糊查询时,'%%'也无法查出null的记录。
ALTER TABLE MKMTJNL ADD (COMT_DIF_AMT NUMBER(9,2)); declare I INT; V_COUNT INT; V_LOOP INT; BEGIN SELECT COUNT(*) INTO V_COUNT FROM MKMTJNL; SELECT ceil(V_COUNT / 100000) INTO V_LOOP FROM dual; I := 1; WHILE I <= V_LOOP LOOP UPDATE MKMTJNL SET COMT_DIF_AMT = 0.00 WHERE ROWNUM <= 100000; COMMIT; I := I + 1; END LOOP; END; ALTER TABLE MKMTJNL MODIFY (COMT_DIF_AMT DEFAULT 0.00);
WITH stat AS(SELECT * FROM table_nm)select * from stat;
11、LPAD(左侧填充)与RPAD(右侧填充)函数
LPAD(原字符串str,长度/正整数x,[用于填充的字符串]y):①如果没有第三个参数y(或者长度x比字符串本身长度小),则表示从左侧截取长度x个字符 ②使用y填充str到直到长度达到x。
SELECT lpad('abcde',3) FROM dual; --abc SELECT lpad('abcde',8,'x') FROM dual; --xxxabcde
SELECT SUBSTR('abcdef',3) FROM dual; --cdef SELECT SUBSTR('abcdefefgh',3,5) FROM dual; --cdefe
REGEXP_SUBSTR(String, pattern, position, occurrence, [modifier]) 五个参数分别表示:①要处理的字符串 ②进行匹配的正则表达式 ③匹配的开始位置,默认为1 ④字符串处理后取第几个匹配组,默认为1 ⑤模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'。)
SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, 1) FROM dual; --111 SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, 2) FROM dual; --222
[^,]+ 表示匹配以非,开始,非,结束的规则。(^用在方括号[]里面时表示非,否则表示字符串开始位置)。
常用应用:
SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, LEVEL) spli,LEVEL FROM dual CONNECT BY LEVEL <= REGEXP_COUNT('111,222,333,444', '[,]', 1) + 1; --仅限单条记录的表 SELECT REGEXP_SUBSTR('111,222,333,444', '[^,]+', 1, ROWNUM) spli,ROWNUM FROM dual CONNECT BY ROWNUM <= REGEXP_COUNT('111,222,333,444', '[,]', 1) + 1; --仅限单条记录的表
当连接条件(connect by条件)没有限制记录之间的关系(即 connect by里没有类似 id=prior pid的条件,而是 connect by rownum<xxx 或connect by level<xxx )时,每一条记录都会作为自己或者其他记录的子节点,也就说,每一条记录的子节点就是表上所有的记录。而树的层数就是rownum(或是level)值。
13、REPLACE函数
REPLACE(原字符串str,被替换的字符串x,[替换字符串]y}):①如果第三个参数为空,则将原字符串str中所有的x都删除(换成空字符串)②使用y替换str中出现的所有x。
SELECT REPLACE('abcabcabc','b') FROM dual; --acacac SELECT REPLACE('abcabcabc','b',' ') FROM dual; --a ca ca c
14、WM_CONCAT函数
WM_CONCAT(列名):把列值用逗号分隔并显示成一行(列转行)
SELECT WM_CONCAT(COL) FROM (SELECT 1 COL FROM DUAL UNION ALL SELECT 2 COL FROM DUAL); --1,2
15、CAST函数
CAST(expr AS type_name) :将一种类型转换成另一种类型。而不仅仅是限于使用用to_number、to_char()以及to_date()类型。
使用to_char()将日期转换成字符串时,注意分钟使用mi,而不是mm(mm表示月份)。
16、DECODE与SIGN函数
SIGN(val):根据val的值是0、正数还是负数,分别返回0、1、-1
decode(条件/字段,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值):根据条件与各个值比对,如果相等,则取对应的返回值;如果没有匹配的值,则返回缺省值。
SELECT DECODE('x','y','值y','z','值z','缺省') FROM dual; --缺省 SELECT DECODE('x','x','值x','z','值z','缺省') FROM dual; --值x
sign与decode联合使用
SELECT DECODE(SIGN(4-3),0,'表达式等于0',1,'表达式大于0',-1,'表达式小于0') FROM dual; --表达式大于0
使用decode进行自定义排序
SELECT *FROM (SELECT 'x' COL FROM DUAL UNION ALL SELECT 'y' COL FROM DUAL UNION ALL SELECT 'z' COL FROM DUAL) ORDER BY DECODE(COL, 'x', 3, 'y', 2, 'z', 1);
结果:
SELECT length('这是6个字符') FROM dual; --6 SELECT lengthb('这是12个字节') FROM dual; --12
18、OVER(partition by 按哪个字段划分组,order by 按哪个字段排序)分析函数
说明:聚合函数(如sum()、max()、COUNT()等)可以计算基于组的某种聚合值,但是聚合函数对于某个组只能返回一行记录。若想对于某组返回多行记录,则需要使用分析函数。
COUNT() OVER():应用在查询明细的同时查询总记录数,可使用partition by分组
SELECT COL,COUNT(*)OVER(PARTITION BY COL) COUNT_NUM FROM (SELECT 'x' COL FROM DUAL UNION ALL SELECT 'x' COL FROM DUAL UNION ALL SELECT 'y' COL FROM DUAL UNION ALL SELECT 'y' COL FROM DUAL);
SUM()OVER() :累加,统计总和,使用方法与COUNT()OVER()相同
偏移分析函数LAG() LEAD()
lag与lead函数是跟偏移量相关的两个分析函数,通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤。这种操作可以代替表的自联接,并且LAG和LEAD有更高的效率。偏移分析函数必须使用order by进行排序,否则报错。
SELECT COL 本行COL, LAG(COL,1,NULL)OVER(ORDER BY col) 上一行COL, LEAD(COL,1,NULL)OVER(ORDER BY col) 下一行COL FROM (SELECT 'a' COL FROM DUAL UNION ALL SELECT 'b' COL FROM DUAL UNION ALL SELECT 'c' COL FROM DUAL UNION ALL SELECT 'd' COL FROM DUAL);