1、将字段名转换为小写,查询员工表姓名 lower
select lower(ename) from emp;
2、将字段名转换为大写,查询job为manager的员工 upper
select * from emp where job = upper('manager');
3、substr 方法参数('被截取的字符串','从哪一位开始截取','截取的位数');
查询姓名以M开头的所有员工 substr
select * from emp where substr(ename,1,1)='M';
4、获取字段的长度 length
select length(ename) from emp;
5、去除首尾空格,但是不能去除中间空格 trim
select * from emp where job = trim(' MANAGER ');
6、数据库格式匹配与字符串转换成date类型 to_date
select * from emp where HIREDATE = '20-2月-81';
select * from emp where hiredate = to_date('1981-02-20 00:00:00','YYYY-MM-DD HH24:MI:SS');
7、获取oracle系统当前日期
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') nowTime from dual;
select sysdate from dual;
8、格式化to_char
查询1981-02-20以后入职的员工,将入职日期格式化为yyyy-mm-dd hh:mm:ss
select empno,ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp where hiredate>to_char('1981-02-20 00:00:00','YYYY-MM-DD HH24:MI:SS');
查询员工薪水 加入千分位
select empno,ename,to_char(sal,'$999,999') from emp;
查询员工薪水 加入千分位 保留两位小数
select empno,ename,to_char(sal,'$999,999.00') from emp;
9、将字符串转换为数值
select * from emp where sal>to_number('1500','999,999');
10、nvl相当于mysql中的ifnull
select empno,ename,sal,comm,sal+nvl(comm,0) from emp;
11、case...when...then...when...then...end与decode
如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%
select empno, ename, job, sal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end) as newsal from emp;
select empno, ename, job, sal, decode(job, 'MANAGER', SAL*1.1, 'SALESMAN', sal*1.5) as newsal from emp;
12、round 四舍五入
select round(1234567.4567, 2) from dual;
dual是oracle提供的,主要是方便使用。