Oracle数据库学习笔记——单行函数

单行函数（五类）：字符，数值，日期，转换，通用。

 

一、字符函数：大小写控制，字符控制。

1、大小写控制：

lower   将字符转换成小写；

upper  将字符转换成大写；

initcap 将字符串中每个单词（空白分割）的第一个字母转换成大写，单词其他的转换成小写；

select lower('LUOjun'),upper('luoJun'),initcap('LuoJUn java')
from dual

select lower('LUOjun'),upper('luoJun'),initcap('LuoJUn java') 

from dual

 

------应用

select *

from employees

where upper(last_name)='KING' ---不清楚king的大小写，则统一转换

 

 

2、字符控制

concat    链接两个字符串；

substr   截取字符串的子集，第一个参数表示位置（第一个字符为的位置为1），     第二个参数表示个数；

length    返回字符串的长度；

instr   返回某个字符在字符串中的位置，如果没有则返回0；

trim   去掉字符串中首尾的出现的指定字符，首尾出现的连续字符都要出掉；

lpad/rpad  将字符串左补齐/右补齐；

replace   将字符串中指定的某个字符替换成其他字符，全部替换；

select concat('hello','world'),substr('study_java',2,6),length('123456') 

from dual

 

 

select instr('hellojava','l'),instr('hellojava','w') 

from dual

 

 

select last_name,lpad(salary,10,'*'),rpad(salary,10,'#')

from employees

 

 

select trim('h' from 'hhellowohhrldh'),replace('abcdab','b','m')

from dual

 

 

 

 

数字函数：round（四舍五入）、trunc（截断）、mod（求余）

select round(5452.926,2),round(5452.926,0),round(5452.926,-1)

from dual

 

 

select trunc(5452.926,2),trunc(5452.926,0),trunc(5452.926,-1),mod(12,10)

from dual

 

 

 

 

日期函数：

 

在日期上加上或减去一个数字结果还是日期。

两个日期相减返回日期之间相差的天数，两个日期之间不能做加法运算。

select sysdate,sysdate+1,sysdate-3 

from dual

 

 

select last_name,sysdate-hire_date as "工作天数",trunc(sysdate-hire_date) as "截断"

from employees

 

 

select last_name,(sysdate-hire_date)/30 as "大概天数",months_between(sysdate，hire_date) as "精确天数"

from employees

 

 

select add_months(sysdate,2),add_months(sysdate,-3),next_day(sysdate,'星期日')

from dual

--结果，next_day表示下个星期几的日期（今天是星期3）

ADD_MONTHS(SYSDATE,2) ADD_MONTHS(SYSDATE,-3) NEXT_DAY(SYSDATE,'星期日')

--------------------- ---------------------- ------------------------

2015/3/21 14:45:42    2014/10/21 14:45:42    2015/1/25 14:45:42

 

--来公司的员工中，hire_date是每个月倒数第二天来公司的有哪些？

select last_name,hire_date

from employees

where hire_date=last_day(hire_date)-1

 

select sysdate,round(sysdate,'MONTH'),round(sysdate,'MM'),trunc(sysdate,'HH')

from dual

--结果（按照月份四舍五入的话，日期过半则月份加1；所以1月21号做round运算，其结果为2月1号；按小时截断trunc的话其时钟和分钟都为0）

SYSDATE ROUND(SYSDATE,'MONTH') ROUND(SYSDATE,'MM') TRUNC(SYSDATE,'HH')

----------- ---------------------- ------------------- -----------

2015/1/21 1 2015/2/1               2015/2/1            2015/1/21 14:00:00

 

 

 

 

 

 

 

 

 

 

 

select '12'+2,sysdate+2 from dual

 

 

 

 

 

---选出1994年6月7号来公司的员工，打印出其id，不同的转换方式效果一样

select employee_id,hire_date

from employees

--where hire_date='7-6月-94' ---date型的格式要求很怪异，一般不用这种

--where to_date('1994-06-07','yyyy-mm-dd')=hire_date  ---char转换成date

where to_char(hire_date,'yyyy-mm-dd')='1994-06-07'  ---date转换成char

 

 

---中间要穿插其它符号的时候要用双引号包起来

select employee_id,to_char(hire_date,'yyyy"年"mm"月"dd"日"')

from employees

where to_char(hire_date,'yyyy"年"mm"月"dd"日"')='1994年06月07日'

 

---用99来替换该有的位，一个9就表示一个位。用0的话，它就会用0补齐不够的位

select to_char(123456.78,'999,999,999.999') ,to_char(123456.78,'999,999,999.9') 

from dual

 

 

select to_char(12.34,'999.99') ,to_char(12.34,'000.0')

from dual

 

 

---用$表示美元，用L来获取本地货币符号

select to_char(12.34,'$999.99') ,to_char(12.34,'L000.0')

from dual

 

 

 

 

 

 

 

 

 

 

这些函数适用于任何数据类型，同时也适用于空值：

•NVL (expr1, expr2) 如果exper1的值为空的时候，则用后面的exper2来代替；不为空的时 候就是它本身不变。

 

•NVL2 (expr1, expr2, expr3) 当exper1不为NULL的时候，返回exper2；为NULL的时候 返回exper3。NVL (expr1, expr2) 等价于NVL2 (expr1, expr1,  expr3)

 

•NULLIF (expr1, expr2) 相等返回NULL；不相等返回exper1。

 

•COALESCE (expr1, expr2, ..., exprn) 如果exper1为空，返回exper2；如果exper2为空，返 回exper3，... 类推 

---计算公司员工的年薪，需要加上奖金率，因为标准没有奖金的人其commission_pct这一项为空，为了便于计算需要将空的地方用0来替换

select employee_id,last_name,salary*12*(1+nvl(commission_pct,0)) as "年薪"

from employees

 

 

 

输出last_name,department_id，当department_id为null时，显示‘没有部门’。

---department_id本身是数字类型的，’没有部门’是字符类型的，而且是汉字，从而要使用显示转换

select last_name,nvl(to_char(department_id),'没有部门')

from employees

 

 

 

 

 

 

 

 

 

 

CASE expr WHEN comparison_expr1 THEN return_expr1

         [WHEN comparison_expr2 THEN return_expr2

          WHEN comparison_exprn THEN return_exprn

          ELSE else_expr]

END

 

--查询部门号为 10, 20, 30 的员工信息, 

--若部门号为 10, 则打印其工资的 1.1 倍, 

--20 号部门, 则打印其工资的 1.2 倍, 

--30 号部门打印其工资的 1.3 倍数

select last_name,department_id,salary,case department_id 

                                           when 10 then salary*1.1

                                           when 20 then salary*1.2

                                           else salary*1.3 

                                       end as "new_sal"

from employees

where department_id in (10,20,30)

 

 

--查询部门号为 10, 20, 30 的员工信息, 

--若部门号为 10, 则打印其工资的 1.1 倍, 

--20 号部门, 则打印其工资的 1.2 倍, 

--30 号部门打印其工资的 1.3 倍数----和以上的代码效果等价

select last_name,department_id,salary,decode(department_id,10, salary*1.1,

                                                           20, salary*1.2,

                                                          salary*1.3) as "new_sal"

from employees

where department_id in (10,20,30)

 

多表查询