oracle 常用语法笔记

字符串处理

1. substr(source,start,length)方法

该方法用于从某个字符串的截取一定长度的子字符串，subStr(source,start,length) 第一个参数是源字符串，第二个参数表示从哪个位置开始截取字符串，其中正负号代表方向，正好代表从左到右，负号代表从右到左，数值代表从第几个字符开始截取，length表示截取字符串的长度大小，当start为0时，效果与等于1相同（start只是确定截取的其实位置，截取方向永远是从左到右）

select substr('hello world!',1,5) from dual t   //展示结果hello 从左边第一个数开始截取5个字符
select substr('hello world!',1,5) from dual t   //展示结果hello 从左边第一个数开始截取5个字符
select substr('hello world!',-6,5) from dual t  //展示结果world 从右边第六个数开始截取5个字符

1. || 字符串连接符号

改方法主要用于字符串的连接

select 'hello'||' world' from dual t  //展示结果hello world 

1. not exists方法去最值

在平时取数据的时候一般会分组，但是分组对字段的选取是有局限性的。还有一种方法是先把最大值选出来，然后根据最大值去找那条记录。其实用not exists方法可以一步搞定

现在我要从这张用户表中找到最后创建的那个用户

这个sql的逻辑：不存在一个用户的创建时间比这个用户的创建时间晚，那么这个用户就是最后常见的用户

1.  oracle中数字的取整与四舍五入（ceil,floor,round,trunc）

ceil函数用于返回一个大于或者等于当前数字的整数

floor函数用于返回一个小于或者等于当前数字的整数

round函数用于对指定精度的数字进行四舍五入

trunc函数用于对数字，日期按照指定的精度进行截取

select ceil(10.125) from dual //返回结果11，返回一个大于或者等于当前数字的整数
select floor(-10.125) from dual //返回结果-11 返回一个小于或者等于当前数字的整数
select round(0.125,2) from dual //返回结果0.13
select round(0.124,2) from dual //返回结果0.12
select trunc(0.125,2) from dual //返回结果0.12 
select trunc(sysdate,'mm') from dual //返回结果2018/2/1

1.   to_char(source, format)  ,to_date(source,format) ,to_number(source) 用于类型之间的转换

时间转换成一定格式的字符串

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day') from dual //展示结果：2018-01-31 10:03:47 星期三
可以看见结果中的每一项都与format的格式保持一致，具体要这样的格式你可以根据自己的需要进行相应的调整
select to_char(0.125,'FM990.00')||'%' from dual //展示结果0.13%
在返回百分比的时候经常用到,'FM990.00'
其9代表：如果存在数字则显示数字，不存在则显示空格
其0代表：如果存在数字则显示数字，不存在则显示0，即占位符。
其FM代表：删除如果是因9带来的空格，则删除之

select to_date('2018-1-2 11:11:11','yyyy-mm-dd hh:mi:ss') from dual //展示结果：2018/1/2 11:11:11
format与source的精度要保持一致
select to_number('11.11') from dual //展示结果11.11

1.  over (PARTITION BY ... order by ...) 

这个函数可以对表起到分组的效果，然后通过具体的函数（求和，求平均等）在相应的记录上拼接相应的字段

这是一个和二班的成绩表，通过上面的函数可以在每个同学后面拼接自己班级的最高成绩和平局成绩

1.  条件判断 decode与case when..then

当返回的内容根据一个字段来决定的时候可以用decode或者case when ...then,

decode(条件,var1,值1,var2，值2,...,默认值)   

根据条件得出的结果逐个与var参数比较，一旦相等，就去相应的值返回，但是这个方法只能判断相等，不能判断大于或者小于，要判断大小可以用case表达式。由于decode表达式不能判断大小，一般会与sign函数搭配来解决部分大小判断问题。其中参数里面的条件，var1经过计算必须是数字或者字符串

case(条件，when 表达式1 then 值1 when 表达式2 then 值2 else 默认值 end)

1.  start with ... connect by prior 
   

这个函数可以根据根节点返回整个属性结果，可以用connect_by_root(var) 来保留根节点，用于统计