公司内部oracle培训小结1

1.格式：abs(number)   即   abs(数值）
返回数值的绝对值。
例：   abs(5)   返回  5
       abs(-5)   返回  5
 
2.格式：ceil(number)   即   ceil(数值）
根据输入值返回一个数值，输入参数可以是非整数，但返回结果则是大于等于输入参数的最小整数。
例：   ceil(5.1)   返回  6
       abs(-5.2)   返回  -5

3.floor(n)取小于等于数值n的最大整数
select floor(9.5) from dual;
FLOOR(9.5)
----------
         9

4.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3)  MOD(3,3)  MOD(2,3)
--------- --------- ---------
        1         0         2
5.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
         56          -55          55          -55
select round(123.456, 0) from dual;          回传 123
select round(123.456, 1) from dual;          回传 123.5
select round(123.456, 2) from dual;          回传 123.46
trunc(n,f)
select trunc(122.345,-2) from dual
100
select trunc(2.345,-1) from dual
0
select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18
select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.
select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天
select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日
select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天
select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天
select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41  
select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00  
TRUNC()函数没有秒的精确

6.CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23'  高乾竞电话 from dual;
高乾竞电话
----------------
010-88888888转23

7.小写函数：lower（）；
用法：比如将一个表的所有名称都小写：
select lower(t.ename) from scott.emp t
 
8.大写函数：upper（）；
用法：比如将一个表的所有名称都大写：
select upper（t.ename） from scott.emp t

9.lpad(String ,截取长度，添加的字符串)
select lpad('test',10) from dual;
将返回“    test”
select lpad('test',10,'ee') from dual;
结果将返回eeeeeetest。

10.ltrim(x,y) 函数是按照y中的字符一个一个截掉x中的字符，并且是从左边开始执行的，只要遇到y中有的字符, x中的字符都会被截掉, 直到在x的字符中遇到y中没有的字符为止函数命令才结束 .
LTRIM('109224323','109')
------------------------
224323
SQL> select ltrim('10900094323','109') from dual;
LTRIM('10900094323','109')
---------------------------
4323
 select LTRIM( 'Miss Liu', 'M is') result from dual;
RES
---
Liu

11.RTRIM
SELECT RTRIM('Mississippi','ip') test1,RTRIM('Rpadded ') test2 ,RTRIM

('Mississippi','sip') test3 FROM dual;
结果为：
test1 test3 test3
Mississ Rpadded M

12.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;
SUBSTR('
--------
08888888
substr('This is a test', 6, 2)     would return 'is'
substr('This is a test', 6)     would return 'is a test'
substr('TechOnTheNet', -3, 3)     would return 'Net'
substr('TechOnTheNet', -6, 3)     would return 'The'

13.REPLACE('string','s1','s2')
string   希望被替换的字符或变量
s1       被替换的字符串
s2       要替换的字符串
SQL> select replace('he love you','he','i') from dual;
REPLACE('H
----------
i love you

14.translate
select translate('123abc','2dc','4e') from dual;
因为from_string和to_string的位置是一一对应的，2对应4，d对应e，c没有对应的值，所以c应该会被删除。
结果是
143ab

15.instr( string1, string2 [, start_position [, nth_appearance ] ] )
参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引
从倒数第1个字符开始，搜索第1次出现子串的位置
SQL> select instr('oracleor','or', -1, 1) position from dual;
POSITION
----------
        7
从倒数第1个字符开始，搜索第2次出现子串的位置
SQL> select instr('oracleor','or', -1, 2) position from dual;
POSITION
----------
        1

16.add_months(time,months)
select add_months(sysdate,-6) from dual;
该查询的结果是当前时间半年前的时间
select add_months(sysdate,6) from dual;
该查询的结果是当前时间半年后的时间
SQL> select length('阿猪') from dual;
LENGTH('阿猪')
--------------
             2

17.length和lengthb
SQL> select lengthb('阿猪') from dual;
LENGTHB('阿猪')
---------------
              4
区别：length求得是字符长度，lengthb求得是字节长度。

18.last_day
select last_day(sysdate) from dual
返回：2013/7/31 15:15:31

19.to_char
select to_char(sysdate,'day') from dual
返回：星期日
select to_char(sysdate,'d') from dual
返回：1
select to_char(23332344.45632,'fml99,999,999.99') from dual
返回：￥23,332,344.46
select to_char(0.45632,'fm999.99') from dual
返回：.46
select to_char(0.45632,'fm9990.99') from dual
返回：0.46

20.decode
DECODE(条件，值1，翻译值1，值2，翻译值2，...值n，翻译值n，缺省值)
DECODE(字段，比较1，值1，比较2，值2，.....，比较n，值n缺省值) 

21.nvl、nvl2、nullif
NVL2(expr1,expr2,expr3)
　　功能：如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。

NVL( string1, replace_with)
　　功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

nullif(表达式1,表达式2) 比较两个表达式是否相等
如果相等，则返回null
如果不相等，则返回表达式1
限制条件:表达式1 不能是null

22.row_number() over ([partition by col1] order by col2) ) as 别名
表示根据col1分组，在分组内部根据 col2排序
而这个“别名”的值就表示每组内部排序后的顺序编号（组内连续的唯一的）,[partition by col1] 可省略。
查找各部门中薪水最高者
select * from
(select ename,job,deptno,sal,(row_number() over(partition by deptno order by sal desc))  rn from scott.emp) where rn=1;