sql单行函数语言《Oracle数据库》

–大小写控制函数
-----函数 ---------------------结果 ---------- 备注
lower(‘SQL Course’) sql course 转变小写
upper(‘SQL Course’) SQL COURSE 转变大大写
initcap(‘SQL Course’) Sql Course 首字母大写

–lower(‘Higgins’):把参数转为小写再根据条件查询，或者把参数和条件都转为小写再查询

select employee_id, last_name,lower(last_name)   转化结果, department_id
from   employees
where lower(last_name)='higgins';/*第一种*/
where  lower(last_name) = lower('higGins');/*第二种*/

–upper(‘Higgins’):把参数转为大写再根据条件查询，或者把参数和条件都转为大写再查询

select employee_id, last_name, department_id，upper(last_name) 转化结果
from   employees
where upper(last_name)='HIGGINS';/*第一种*/
where  upper(last_name) = upper('higgins');/*第二种*/

–initcap(’’):把单词的首字母转为大写查询再根据条件查询

select last_name  本姓名
,lower(last_name) 首字母转小写结果
,initcap(lower(last_name)) 首字母转大写结果
from   employees
where initcap(lower(last_name))='Higgins';

–字符控制函数
–函数 ---------------------------- 结果 --------- 备注
concat(‘Hello’,‘World’) HelloWorld 字符串拼接
substr(‘HelloWorld’,1,5) Hello 字符串截取
length(‘HelloWorld’) 10 获取字符串长度
instr(‘HelloWorld’,‘W’) 6 获取特定字符的位置
lpad(‘salary’,10,’’) *****24000 左对齐
rpad(‘salary’,10,’’) 24000*** 右对齐
trim(‘H’ from ‘HelloWorld’) elloWorld 删除特定的字符
replace(‘abcd’,‘b’,‘m’) amcd 替换特定的字符**

select concat('Hello','World') 拼接
,substr('HelloWorld',1,5)      截取
,length('HelloWorld')          字符串数量
,instr('HelloWorld','W')       获取位置
,lpad('salary',10,'*')         左对齐
,rpad('salary',10,'*')         右对齐
,trim('H' from 'HelloWorld')   去除
,replace('abcd','b','m')       替换
from employees;

–数字函数
–函数 ----备注 ---------------------- 列子
round 四舍五入 round(1314.525,2)=>1314.53
trunc 截断 trunc(1314.525,2)=>1314.52
mod 求余 mod(1600,300)=>100

select round(1314.525,2) 四舍五入
,round(1315.525,-1)      四舍五入
,trunc(1314.525,2)       截断
,mod(1600,300)           求余
from employees;

–日期的数学运算
–在日期上加上或减去一个数字结果仍为日期。
–两个日期相减返回日期之间相差的天数。
–日期不允许做加法运算，无意义。
–可以用数字除24来向日期中加上或减去天数。

–日期函数

SELECT last_name,department_id ,(SYSDATE-hire_date)/7 AS WEEKS
FROM   employees
WHERE  department_id = 90;

函数 ---------------------------描述 ---------------------- 格式
months_between 两个日期相差的月数 （现在日期,过去日期）
add_months 向指定日期中加上若干月数 （日期,要加上的月数）
next_day 指定日期的下一个星期*对应的日期 （日期,下个星期几）
last_day 本月的最后一天 （日期）
round 日期四舍五入
trunc 日期截断

–两个日期相差的月数months_between(现在日期,过去日期)

select months_between('01-9月-95','11-1月-94') as 相差月数 from employees;

–向指定日期中加上若干月数add_months（日期,要加上的月数）

select add_months('01-9月-95',12) as 十二月后 from employees;

–指定日期的下一个星期*对应的日期next_day（日期,下个星期几）

select next_day('03-3月-19','星期五') from employees;

–本月的最后一天last_day（日期）

select last_day('03-3月-19') from employees;

–月/年份的四舍五入round(日期,‘month/year’)

select sysdate,round(sysdate,'year') from employees;--年的四舍五入
select sysdate,round(sysdate,'month') from employees;--月的四舍五入

----月/年份的截断trunc(日期,‘month/year’)

select sysdate,trunc(sysdate,'year') from employees;--年的截断
select sysdate,trunc(sysdate,'month') from employees;--月的截断

–to_char 函数对日期的转换
格式：
1、必须包含在单引号中而且大小写敏感
2、可以包含任意的有效的日期格式
3、日期之间用逗号隔开

–显示服务器系统当前时间，格式为2007-10-12 17:11:11(提示：使用 to_char函数)

select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') from employees;

–查询部门编号，员工姓名，入职时间为’1987-09-17’的员工。

select employee_id,last_name,hire_date
from employees
where to_char(hire_date,'yyyy-mm-dd') = '1987-09-17'

–to_date函数将字符转换成数字

select to_date('2019年03月04日 14:56:21','yyyy"年"mm"月"dd"日" hh24:mi:ss')
from employees;

–to_char 函数对数字的转换

select to_char(salary,'$999,999.00') 工资
from employees;

–to_number函数将字符转换成日期

select to_unmber('￥1,234,567,890.00','L999,999,999,999.99')
from employees;

–nvl函数将空值转换成一个已知的值

SELECT last_name, salary, NVL(commission_pct, 0),
   (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

–NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

—查询员工的奖金率，若为空，返回0.01,若不为空，返回实际奖金率+0.015

select commission_pct,nvl2(commission_pct,0.015,0.01)
from employees;

–unllif (expr1, expr2) : 相等返回NULL，不等返回expr1

SELECT first_name, length(first_name) "expr1", 
       last_name,  length(last_name)  "expr2",
       NULLIF(length(first_name), length(last_name)) result
FROM   employees;

—casa表达式
–查询部门号为 10, 20, 30 的员工信息,
–若部门号为 10, 则打印其工资的 1.1 倍,
–20 号部门, 则打印其工资的 1.2 倍,
–30 号部门打印其工资的 1.3 倍数。

select employee_id,salary,department_id,
case department_id 
   when 10 then salary*1.1
     when 20 then salary*1.2
       when 30 then salary*1.3     
         else salary
end
from employees
where department_id in('10','20','30')

–查询经理id为空时，添加’No Manager’
–嵌套函数格式如下：

select last_name,
       nvl(to_char(manager_id), 'No Manager')
from   employees
where  manager_id is null;