|-如下以employees表为例
①单行函数:
操作数据对象接受参数返回一个结果,只对一行进行变换,
每行返回一个结果,可以转换数据类型,可以嵌套,参数
可以是一列或一个值。
②大小写控制函数
select lower('ATGUIGUJAVA'),UPPER('AtGuiGu Java'),initcap('AtGuiGu java') from dual;
select * from employees where lower(last_name) = 'king';
select * from employees where upper(last_name) = 'KING'
③字符控制函数
select concat('hello','world'),substr('helloworld',2,4),length('helloworld')from dual;
select instr('hellowJava','l')from dual;
select employee_id,last_name,lpad(salary,10,'*')from employees;
select employee_id,last_name,lpad(salary,10,' '),rpad(salary,10,' ')from employees
select trim('h' from 'helloworld')from dual;--去除首尾
select replace('abcdab','b','m')from dual;
④数字函数
ROUND: 四舍五入
ROUND(45.926, 2)--------------->45.93
TRUNC: 截断
TRUNC(45.926, 2)--------------->45.92
MOD: 求余
MOD(1600, 300)--------------->100
select round(435.45,2),round(435.45),round(435.45,-2)from dual;
select trunc(435.45,1),trunc(435.45),trunc(435.45,-1)from dual;
select mod(1100,300)from dual;
⑤日期
在日期上加上或减去一个数字结果仍为日期。
两个日期相减返回日期之间相差的天数。
日期不允许做加法运算,无意义
可以用数字除24来向日期中加上或减去天数
select sysdate,sysdate+1,sysdate-3 from dual;
--多少天
select employee_id,last_name,sysdate-hire_date workde_days from employees;
select employee_id,last_name,trunc(sysdate-hire_date) workde_days from employees;
--多少月
select employee_id,last_name,(sysdate-hire_date)/30 workde_months from employees;
⑥日期函数
select employee_id,last_name,(sysdate-hire_date)/30,months_between(sysdate,hire_date) from employees;
select add_months(sysdate,2),add_months(sysdate,-3),next_day(sysdate,'星期四') from dual;
--hire_date是每个月倒数第二天有哪些
select last_name,hire_date from employees where hire_date = last_day(hire_date)-1;
select last_day(sysdate) from dual;
select round(sysdate,'month'),round(sysdate,'mm'),trunc(sysdate,'hh') from dual;
⑦转换函数
Oracle 自动完成下列转换:
隐式转换:
select '12'+2 from dual;
select sysdate + '2' from dual;
显式转换:
日期转换:
select employee_id,hire_date from employees where to_char(hire_date,'yyyy-mm-dd')= '1994-06-07';
select employee_id,hire_date from employees where to_date('1994-06-07','yyyy-mm-dd') = hire_date;
select to_char(1234567.89,'999,999,999.99') from dual;
select to_char(1234567.89,'000,000,999.99') from dual;
select to_char(1234567.89,'L000,000,999.99') from dual;
select to_number('¥001,234,567.89','L000,000,999.99') from dual;
select to_number('$001,234,567.89','$000,000,999.99') from dual;
⑧通用函数
这些函数适用于任何数据类型,同时也适用于空值:
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)
--年薪
select employee_id,last_name,salary*12*(1+nvl(commission_pct,0))"annual sal" from employees;
--输出last_name,department_id,当department_id为null时,显示‘没有部门’
select last_name,nvl(to_char(department_id,'999999'),'没有部门') from employees;
select last_name,nvl(to_char(department_id),'没有部门') from employees;
--查询员工的奖金率,若为空,返回0.01,若不为空,返回实际奖金率+0.015
select last_name,commission_pct,nvl2(commission_pct,commission_pct+0.015,0.01) from employees;
⑨条件表达式
–CASE 表达式:
--查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数
select employee_id,last_name,department_id,case department_id when 10 then salary * 1.1 when 20 then salary * 1.2 else salary * 1.3 end new_sal from employees where department_id in(10,20,30);
–DECODE 函数:
select employee_id,last_name,department_id,decode(department_id,10,salary*1.1,20,salary*1.2,salary)new_sal from employees where department_id in(10,20,30);