oracle相关小知识及通用函数

最新推荐文章于 2021-06-24 19:32:37 发布
kruppwang1
最新推荐文章于 2021-06-24 19:32:37 发布
阅读量399
点赞数
分类专栏：数据库文章标签： oracle 函数 oracle函数
本文链接：https://blog.youkuaiyun.com/kruppwang1/article/details/84679985
版权
数据库专栏收录该内容
3 篇文章
订阅专栏
                    
                        
                    
                    sql中null值问题 
  包含null的表达式都为null
null永远不等于null
如果集合中含有null不能使用not in 但是可以使用in （null的不能用等于，得用is，同理不等于也不能用得用is not null）
组函数（多行函数）自动虑空的功能；可以嵌套虑空函数来屏蔽他的虑空功能（nvl函数）；
 
数据的完整性是指我们建立在表中的约束；
查询表中的数据通过主键查最快，因为它是unique，而unique本身是一个索引；
distinct为去除重复放在要查询的字段前面，如果后面跟两个不同的字段的话，查询出来的是两个组合起来不重复的结果。概括起来就是distinct作用于后面所有的列
oracle里面的dual伪表，它的存在是为了让我们操作方便，因为select语句都需要特定的对象来实现，如果我们不需要从特定的表中得到数据单纯的就是要得到我们想要的一些信息就可以利用伪表；
oracle的语句别名的起发有三种形式
oracle语句中的字符串用单引号包起来，并且大小写敏感；同时对日期格式敏感默认格式’DD-MON月-YY‘
oracle语句中的between and 表示两个数之间的数据，包括边界值，并且小值要放在前面，大值放在后面
在oracle中声明转义字符的用法：例如查找名字中含有下划线的员工
oracle语句：order by 后面可以跟列，表达式，别名，序号；同时order by 作用于后面所有的列，但是如果是降序的话只作用于离他最近的列，此时如果想作用于所有列那么每一列后面都需要加desc关键字
语句：按照奖金降序排列，如果有null值所有null值排在最后：
oracle 中的字符函数 
  upper 转大写 lower 转小写 initcap首字母大写
substr（a，b）从a中第b位开始取（a，b，c）从a中第b位开始取，取c位
length 返回字符数 lengthb 返回字节数
instr（a，b） 在a中查找b；找到了返回下标，否则返回0
ipad 左填充 rpad 右填充ipad（‘abcd’，10，‘*’）左填充星号填充后共十位
trim去掉前后指定的字符select trim(‘H’ from ‘Hello World’) from dual;
replace替换功能
round四舍五入 round(45.235,X)x表示四舍五入后保留几位小数；trunc截断不进行四舍五入直接截断x表示的位数
 
日期函数 
  查询系统当前时间：select sysdate from dual;格式化显示时间select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;
日期和日期之间是可以相减的，表示两个日期之间的天数，但是日期和日期之间不能相加
months_between 返回两个日期之间相差的月数，是准确值；
add_months 向指定日期中加上若干个月数
last_day 日期所在月份的最后一天
next_day 返回指定日期的下一个星期几； 
    例子：把北京的数据库每个星期一自动备份数据都上海的数据库（分布式数据库，快照）
 
round(sysdate,‘month’)日期之间的四舍五入看天；truct；截断
 
转换函数 
  显示：2015-07-22 06:58:21今天是星期三 select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss"今天是"day’) from dual;
 
通用函数 
  nvl2(a,b,c) 当a为null时，返回c，否则返回b
nullif（a，b） 当a=b时，返回null。否则返回a；
coalesce 从左到右找到第一个不为null的值
case表达式：case …when …then …end
DECODE函数：decode(value,if1,then1,if2,then2,if3,then3,…else)表示如果value的值等于if1，decode返回then1…如果不等于任何一个值返回else 
    sign()函数：根据括号里面的值是正数，负数，0；分别返回1，-1,0
 
 
分组函数 
  group by ：注意：在select列表中所有未包含在组函数中的列表都应该包含在group by子句中；
rollup（a,b,c）：是个统计函数表示从右向左进行数据的汇总统计，并生成一行
 
多表查询 
  外连接：对于某些不成立的记录，任然希望包含在最后的结果中
左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中 写法： where e.deptno=d.deptno(+)
右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含在最后的结果中 写法: where e.deptno(+)=d.deptno
 
层次查询
子查询 
  注意的问题：
SQL> 1. 括号
SQL> 2. 合理的书写风格
SQL> 3. 可以在主查询的where select having from后面都可以放置子查询
SQL> 4. 不可以在group by后面放置子查询
SQL> 5. 强调from后面的子查询
SQL> 6. 主查询和子查询可以不是同一张表，只要子查询返回的结果 主查询可以使用即可
SQL> 7. 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
SQL> 8. 一般先执行子查询 再执行主查询；但相关子查询例外
SQL> 9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
SQL> 10. 子查询中的null
 
集合运算 
  注意的问题 
     
      参与运算的各个集合必须列数相同 且类型一致（如果集合中的列数不一致的话通过添加空值来保证一致，如果空值为字符串类型可以用to_char(null)来表示，如果空值为数字类型to_number(null)…）
 
SQL> 2. 采用第一个集合作为最后的表头
SQL> 3. order by永远在最后
SQL> 4. 括号
 
并集 用union ；交集用intersect；差集用minus
 
oracle语句中的字符串用单引号包起来，并且大小写敏感；同时对日期格式敏感默认格式’DD-MON月-YY‘
oracle语句中的between and 表示两个数之间的数据，包括边界值，并且小值要放在前面，大值放在后面
在oracle中声明转义字符的用法：例如查找名字中含有下划线的员工
oracle语句：order by 后面可以跟列，表达式，别名，序号；同时order by 作用于后面所有的列，但是如果是降序的话只作用于离他最近的列，此时如果想作用于所有列那么每一列后面都需要加desc关键字
语句：按照奖金降序排列，如果有null值所有null值排在最后：
oracle 中的字符函数 
  upper 转大写 lower 转小写 initcap首字母大写
substr（a，b）从a中第b位开始取（a，b，c）从a中第b位开始取，取c位
length 返回字符数 lengthb 返回字节数
instr（a，b） 在a中查找b；找到了返回下标，否则返回0
ipad 左填充 rpad 右填充ipad（‘abcd’，10，‘*’）左填充星号填充后共十位
trim去掉前后指定的字符select trim(‘H’ from ‘Hello World’) from dual;
replace替换功能
round四舍五入 round(45.235,X)x表示四舍五入后保留几位小数；trunc截断不进行四舍五入直接截断x表示的位数
 
日期函数 
  查询系统当前时间：select sysdate from dual;格式化显示时间select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;
日期和日期之间是可以相减的，表示两个日期之间的天数，但是日期和日期之间不能相加
months_between 返回两个日期之间相差的月数，是准确值；
add_months 向指定日期中加上若干个月数
last_day 日期所在月份的最后一天
next_day 返回指定日期的下一个星期几； 
    例子：把北京的数据库每个星期一自动备份数据都上海的数据库（分布式数据库，快照）
 
round(sysdate,‘month’)日期之间的四舍五入看天；truct；截断
 
转换函数 
  显示：2015-07-22 06:58:21今天是星期三 select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss"今天是"day’) from dual;
 
通用函数 
  nvl2(a,b,c) 当a为null时，返回c，否则返回b
nullif（a，b） 当a=b时，返回null。否则返回a；
coalesce 从左到右找到第一个不为null的值
case表达式：case …when …then …end
DECODE函数：decode(value,if1,then1,if2,then2,if3,then3,…else)表示如果value的值等于if1，decode返回then1…如果不等于任何一个值返回else 
    sign()函数：根据括号里面的值是正数，负数，0；分别返回1，-1,0
 
 
分组函数 
  group by ：注意：在select列表中所有未包含在组函数中的列表都应该包含在group by子句中；
rollup（a,b,c）：是个统计函数表示从右向左进行数据的汇总统计，并生成一行
 
多表查询 
  外连接：对于某些不成立的记录，任然希望包含在最后的结果中
左外连接：当where e.deptno=d.deptno不成立的时候，等号左边的表任然被包含在最后的结果中 写法： where e.deptno=d.deptno(+)
右外连接：当where e.deptno=d.deptno不成立的时候，等号右边的表任然被包含在最后的结果中 写法: where e.deptno(+)=d.deptno
 
层次查询
子查询 
  注意的问题：
SQL> 1. 括号
SQL> 2. 合理的书写风格
SQL> 3. 可以在主查询的where select having from后面都可以放置子查询
SQL> 4. 不可以在group by后面放置子查询
SQL> 5. 强调from后面的子查询
SQL> 6. 主查询和子查询可以不是同一张表，只要子查询返回的结果 主查询可以使用即可
SQL> 7. 一般不在子查询中排序；但在top-n分析问题中，必须对子查询排序
SQL> 8. 一般先执行子查询 再执行主查询；但相关子查询例外
SQL> 9. 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符
SQL> 10. 子查询中的null
 
集合运算 
  注意的问题 
     
      参与运算的各个集合必须列数相同 且类型一致（如果集合中的列数不一致的话通过添加空值来保证一致，如果空值为字符串类型可以用to_char(null)来表示，如果空值为数字类型to_number(null)…）
 
SQL> 2. 采用第一个集合作为最后的表头
SQL> 3. order by永远在最后
SQL> 4. 括号
 
并集 用union ；交集用intersect；差集用minus
 

                