ORACLE常用函数的使用方法
ORACLE常用函数的使用方法
1. 字符串函数
(1) length(); 获取字符长度
SELECT LENGTH('中国') FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM'
(2) lengthb(); 获取字节长度
SELECT LENGTHB('中国') FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM' (一个汉字等于两个字节)
(3) ltrim(); 除去左边出现的字符串
SELECT LTRIM(TABLE_NAME) FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM' //除去左边的空格
SELECT LTRIM(TABLE_NAME, 'XMB') FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM' //除去左边匹配到的字符XMB
(4) rtrim(); 除去右边出现的字符串
SELECT RTRIM(TABLE_NAME) FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM' //除去右边的空格
SELECT RTRIM(TABLE_NAME, 'XMB') FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM' //除去右边匹配到的字符XMB
(5) trim(); 一般都是用在删除字符串两边的空格
SELECT TRIM(TABLE_NAME) FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM' //除去字段两边出现的空格
SELECT TRIM(LEADING 'M' FROM TABLE_NAME) FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM' //删除左边出现的指定的字符M
SELECT TRIM(TRAILING 'M' FROM TABLE_NAME) FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM' //删除右边出现的指定的字符M
SELECT TRIM(BOTH 'M' FROM TABLE_NAME) FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM' //删除两边出现的指定字符M
(6) lpad(string,n,[pad_string]);在字符串左边填充特定的字符
String是原始字符串, n 要返回的字符串长度, 从字符串末尾向前数
当pad_String为null时, 会填充空格
(7) rpad(string,n,[pad_string]);在字符串右边填充特定的字符
使用与lpad一样
(8) substr(); 字符串截取
SELECT SUBSTR(TABLE_NAME, 1 , 4) FROM PLATFORM_METAINFO_TABLES WHERE XMBM = 'XMBM' //截取从索引1开始的4个字符,结合length()函数可实现从左边截取
SELECT SUBSTR(TABLE_NAME, length(TABLE_NAME)- 3 + 1, 3) FROM PLATFORM_METAINFO_TABLES WHERE TABLE_NAME = 'XMBM'
2. 时间函数
设置时间格式: ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy HH:mi:ss'
(1) sysdate //系统时间
(2) current_date //系统时间
(3) to_char(sysdate, 'yyyy-mm-dd HH24:mi:ss'); //将日期转为字符串
(4) to_date('2017-09-22 00:00:00', 'yyyy-MM-dd HH24:mi:ss'); //将字符串转为日期格式
(5) TO_TIMESTAMP();转为时间戳格式, 使用类似于to_date()
SELECT TO_TIMESTAMP(to_char(SYSDATE, 'yyyy-MM-dd HH24:mi:ss'), 'yyyy-MM-dd HH24:mi:ssxff') FROM RSGL_RYJBXXB WHERE XMPY = 'wjw_chengqiong'
时间计算:
>> 过去一小时/一分钟/一秒
SELECT TO_CHAR(to_date('2019/06/04 14:43:51', 'yyyy/MM/dd hh24:mi:ss') - 1/24, 'yyyy/MM/dd hh24:mi:ss') FROM RSGL_RYJBXXB WHERE XMPY = 'wjw_chengqiong'
>> 过去一天
SELECT TO_CHAR(SYSDATE - 1, 'yyyy/MM/dd hh24:mi:ss') FROM RSGL_RYJBXXB WHERE XMPY = 'wjw_chengqiong'
>> 过去一个月
SELECT add_months(SYSDATE, -1) FROM RSGL_RYJBXXB WHERE XMPY = 'wjw_chengqiong'
3.聚合函数
聚合函数不能作为WHERE里的查询条件出现, 因为聚合函数是对所有查询结果的运算
(1) SUM求和
SELECT SUM(MATTER_CNSX) FROM SP_MATTERS
(2) AVG求均值
SELECT AVG(MATTER_CNSX) FROM SP_MATTERS
(3) MAX和MIN
SELECT MIN(MATTER_CNSX) FROM SP_MATTERS
(4) NVL null值判断
SELECT NVL(MATTER_CNSX, '111111111') FROM SP_MATTERS
(5) ROUND四舍五入
SELECT ROUND (AVG(MATTER_CNSX)) FROM SP_MATTERS
SELECT round(AVG(MATTER_CNSX) * 100) / 100 FROM SP_MATTERS (保留小数点几位)
(6) count(*) 查询表行数
(7) count(column) 查询列行数 , 会忽略null值
4. 关于sql的优化法则
这些优化方式来源于网友的总结，自己在这里也记录一下
(1) 尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替。
原因:Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。
而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。
NOT EXISTS 比 NOT IN 效率稍高。但具体在选择IN或EXIST操作时，
要根据主子表数据量大小来具体考虑。
SELECT * FROM SP_MATTERS WHERE UUID IN (SELECT UUID FROM SP_MATTERS)
SELECT * FROM SP_MATTERS WHERE EXISTS (SELECT UUID FROM SP_MATTERS WHERE UUID = SP_MATTERS.UUID)
(2) 不用"<>"或者"!="操作符, 对于不等于操作符会造成全表扫描, 应该用">" OR "<" 代替
(3) is null 或者 is not null 条件会造成全表扫描, 解决方案可以为该列设置非空索引,就可以利用其它条件判断
(4) 在like后面出现通配符 "%" 或者 "_" 时, 索引会失效, 造成全表扫描
(5) 对于有连接的列"||",最后一个连接列索引无效, 尽量避免连接, 可以分开连接或者使用不作用在列上的函数替代
(6) 如果索引不是基于函数的,那么当在where子句中对索引列使用函数时, 索引不再起作用
(7) 对数据类型不同的列进行比较时，会使索引失效
(8) >= 比 >的执行效率高, 原因: >= 会通过索引快速定位等于项, 然后再向下扫描, 选出大于项, 而> 直接走全表扫描
(9) union和union all的区别
union会对结果进行筛选, 消除重复, 数据量大的情况下可能会引起磁盘排序, 如果不删除重复记录
应该使用union all
(10) oracle从下到上多个查询条件时, 应该将过滤量最大的条件放在where子句的末尾
(11) oracle 从右到左处理from后面的多个表时, 应该将数据量最少的表放在最后
(12) order by 语句中的非索引列会降低性能, 可以将排序列添加索引
(13) 当前sql连接多个表时, 使用表的别名, 并将之作为每列的前缀, 这样可以减少解析时间
