oracle常用函数及关键字笔记

--函数及关键字--
1.trim,ltrim,rtrim 去除字符，无指定默认去除空格
SELECT TRIM('a' FROM 'aafhfhaaaaaaaa'),
LTRIM('aafhfhaaaaaaaa','a'),
RTRIM('aafhfhaaaaaaaa','a')
FROM dual fhfh, fhfhaaaaaaaa,aafhfh
---以前一直以为只能去除空格，去除某字符都用replace函数替换

2.连接字符串函数：CONCAT函数和||

concat包含两个参数，多个字符串连接需套用
concat(1,2) 12

3.UPPER，LOWER，INITCAP函数:可以将字符串转换为全大写，全小写，以及首字母大写(单词之间用空格分开)

4.LENGTH函数:获取字符串的字符长度.

5.LPAD,RPAD函数:补位函数，当要显示的内容长度不足给定长度时，在左面或右面补充若干给定字符以达到长度。若实际显示的内容超过显示长度时，会截取实际显示内容。
select lpad('123',5,'0')|| rpad('54321',3,'0') from dual ---00123543

6.四舍五入，截取，
floor：取整 floor（1234.5678）--1234
round:四舍五入 round（1234.5678,-2）--1200 第二个参数为正向小数点后四舍五入，为负向前，0或没有取整四舍五入
ceil（1234.5678）--1235 整数返回自己，小数返回大于他自己的最小整数（取整加1）
trunc（1234.5678，-2）trunc(sysdate,'mm') --1200,20170201 对数字根据第二参数截取，正后负前，对时间按第二参数取时间，若是MM（月）取到月初第一天，其他同理
substr（‘12345678’，-5，3）--456 截取第一个参数字符串，从下标为第二个参数开始，以第三个参数为个数取，若第二个为负数则从最后一个开始向前数对应的个数，第二个为0和1都是
从第一个开始，第三个参数可省略
他的兄弟：substrb,substrc,substr2,substr4
7.获取下标：instr（a,b,c,d）从字符串a中第c位置开始找字符串b，出现的第d次获取这个下标
他的兄弟：
SELECT INSTR('无言独上西楼','上') FROM dual; --4
SELECT INSTRB('无言独上西楼','上') FROM dual; --7 汉字占位多
SELECT INSTRC('无言独上西楼','上') FROM dual; --4
SELECT INSTR2('无言独上西楼','上') FROM dual; --4
SELECT INSTR4('无言独上西楼','上') FROM dual; --4
8.常用类型转换函数：to_char(),to_date(),to_number（）
SELECT to_char(SYSDATE,'yyyymmdd') ,to_date('20150304','yyyymmdd'),to_number('00002001') FROM dual
--20160403 2015/3/4 2001
9.时间函数相关：
sysdate 系统当前时间
yyyy mm dd hh mi ss/yyyy mm dd hh24 mi ss 年月日时分秒
ssss 午夜后的秒
SELECT to_char(SYSDATE,'yyyy'),to_char(SYSDATE,'yyy'),to_char(SYSDATE,'yy'),to_char(SYSDATE,'y') FROM dual 2015 015 15 5

Q 季度
ww 一年中的第几周
w 一月中的第几周
ddd 一年中的第几天
dd 一月中的第几天
d 一周中的第几天
CC--世纪

last_day(A):获取时间A对应月份的最后一天，时分秒不变
next_day(A,3):A本周的下一周的第三天，周日为第一天
add_months(A,1):在时间A的基础上偏移一个月
months_between（A，B）：两个时间的月份差

10.最小值，最大值
min（A）：字段A中的最小值
max（A）：字段A中的最大值

LEAST（a,b,c....）:取参数中最小值
GREATEST（a,b,c....）:取参数中最大值

11.空值函数
空 null 和‘’这里有说null是空，‘’是长度为0的字符串（占位），其实我感觉数据库对于‘’的处理就是当null处理的，‘’中间是放字符串的，但一个字符串插入到date类型中就会报
错，如果是个空字符串（‘’）就能正常插入，而且符合 is null 条件，所以我感觉这里讨论他们的区别是没意义的，这里只针对oracle
非空 is not null ，length（A）>0 .......
注：length（A）=0 不能作为空的判断条件 length（null） 是空 除了处理空函数其他函数同理，数学运算空加减乘除任何数为空

NVL(a,b):如果a为空则取b，不为空取a
NVL2(a,b,c)：如果a为空取c,不为空取b
nullif（a,b）:a=b 返回空，不等返回a

12.数学函数
abs(a):a的绝对值
power（a，b）：a的b次方
sqrt(a):a的平方根
mode(a,b)a除b的余数
13.统计函数
count（A）计数
sum（a）求和
avg（A）平均数
sign（A）A小于0为-1，等于0为0，大于0为1

GROUP BY A ：按A值分组
HAVING count（1）>2 :进行分组统计后的条件筛选，比如按家庭分组，每家养猪超过2只 的展示出来

13.查询交并差集
并集
union 和union all 前面踢重，后面全展示，不踢重
select a from t1
union
select b from t2
交集
intersect
写法同上
差集 ：Minus 第一个查询中不同于第二个查询结果的数据
写法同上

14.模糊查询 ：like
%：字符串，长度任意包括0
_:一个字符

如：like ‘%aa%bb__cc%’ 按顺序包含aabbcc且bb和cc间必有2个字符的字符

15.decode(a,b,c,d,e....,g,h,i):如果a等于b取c，否则如果等于d取e..以此类推..
如果最后一个和倒数第二个是因果关系（否则如果等于h取i）则除了括号中列举外的就取空，decode（1,2,3）=null
如果倒数第二个和倒数第三个是因果关系则除了前面指定的外取最后一个，decode（1,2,3,4）=4

16.字符与十进制数转换函数

SELECT ASCII('A'),CHR(65) FROM dual --65 A

17.替换 replace
create or replace view v_test 创建一个名叫v_test的视图，如果已存在将之替换
函数：select replace(a,b,c) 将字符串a中包含字符串b的部分替换为字符串c

18.TRANSLATE(a,b,c)：通过b将a中字符替换为c中的字符

将字符串b中字符与字符串c中字符下标一一对应的字符做对应，如果c中没与之对应的则取空字符，
如果有b中字符有重复，则取第一个对应值，然后再将a字符串中字符与b字符串中字符相同的值替换为c中与b对应值的值

select TRANSLATE('不吃葡萄吐出葡萄皮一颗牙齿一个葡萄皮','葡萄的葡萄牙齿','苹果汁里有虫') from dual;
--不吃苹果吐出苹果皮一颗虫一个苹果皮

葡——苹
萄——果
的——汁
葡——苹（原本对应‘里’ ，但是上面已经对应了‘苹’，可以理解为他在两个地方发布了征婚，但现在已经和第一个确认婚姻关系了，不能再把这个也拉去当老婆了，但是他的征婚位置还是有效的要收钱的，所以后面的还得跟在他后面发布征婚）
萄——果 （与上同理）
牙——虫
齿——
----根据以上情况他们几个字符的关系就是下面这种了，最后替换也就根据下面这种对应来替换a中的字符
葡——苹
萄——果
的——汁
牙——虫
齿——

 

20.将一条记录多条展示
如 有表t_test_dept
deptno name
001 张三，李四，王五
拆分后t_test_dept
deptno name
001 张三
001 李四
001 王五

WITH temp AS
(SELECT dept_no,uname||'，' AS uname,(LENGTH(uname)-LENGTH(replace(uname,'，')))+1 AS run_times
FROM t_test_dept)
SELECT a.dept_no,regexp_substr(a.uname,'[^，]+',1,b.column_value) VALUE
FROM temp a,
TABLE(CAST(MULTISET (SELECT ROWNUM rn
FROM dual
CONNECT BY Rownum<=a.run_times) AS sys.odcivarchar2list)) b

----------------------
WITH temp AS:相当于创建个临时表再将数据进行分析处理
regexp_substr：通过匹配的正则动态截取字符串
table:这里将集合当做表
cast:把结果集处理为一个集合
multiset:有了它查询结果返回为多行，否则为一行
connect by:指定层级父子行关系
as sys.odcivarchar2list: 相当于当做这种结果集

21.将多条记录合并展示 wm_concat ；listagg(aaa) within group(order by bbb)
如
deptno name
001 张三
001 李四
001 王五
合并后
deptno name
001 张三;李四;王五

SELECT a.dept_no,REPLACE(replace(wm_concat(a.uname),',','；'),'，','；')
FROM T_TEST_DEPT a
GROUP BY a.dept_no

------

SELECT dept_no,replace(listagg(a.uname,'；') WITHIN GROUP(ORDER BY NULL),'，','；') FROM t_test_dept a
GROUP BY a.dept_no

 22.转义  escape,将escape前需要处理的字符串中包含其后字符的字符后的一个字符转义，通常用\

select * from dba_source A
where upper(text) like upper('I\_D%') escape '\'   ---在I\_D中找到 \ ，对 \ 后面的下划线进行转义，sql使用like 会把下划线当做一个任意字符模糊匹配，转义后即是下划线这个字符

select * from dba_source A
where upper(text) like upper('Ie_D%') escape 'E'  --以上同理，escape 后面指定的只能是长度为1的字符