【2017/5/2】oracle之sql(四)
来源:互联网 发布:麟龙选股软件 编辑:程序博客网 时间:2024/06/14 11:53
转换函数
隐式转换:
SQL> set linesize 200
SQL> select * from emp where empno='7900'; #字符串--》数字
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
SQL> select * from emp where hiredate='17-DEC-80'; #字符串--》日期
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
显式转换:
SQL> select to_char(hiredate,'yyyy-mm-dd') from emp where deptno=10;
TO_CHAR(HI
----------
1981-06-09
1981-11-17
1982-01-23
SQL>
SQL> select to_char(hiredate,'fmyyyy-mm-dd') from emp where deptno=10; #fm可以去除开头的0
TO_CHAR(HI
----------
1981-6-9
1981-11-17
1982-1-23
SQL>
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2017-04-10 19:34:13
SQL>
SQL> select ename,sal from emp;
ENAME SAL
---------- ----------
SMITH 800
ALLEN 1600
SQL> select ename,to_char(sal,'9,999.00')sal from emp; #.=d,9代表所有数字,gG=,
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
SQL> select ename,to_char(sal,'9g999d00')sal from emp;
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
SQL> select ename,to_char(sal,'9G999d00')sal from emp;
ENAME SAL
---------- ---------
SMITH 800.00
ALLEN 1,600.00
SQL> select to_date('2017,12,10','yyyy-mm-dd') from dual;
TO_DATE('
---------
10-DEC-17
SQL> select to_date('20171210','yyyy-mm-dd') from dual;
TO_DATE('
---------
10-DEC-17
NVL
NVL(expr1,expr2) 第一个有值返回第一个,否则返回第二个SQL> select ename,nvl(to_char(comm),' have no comm') comm from emp;
ENAME COMM
---------- ----------------------------------------
SMITH have no comm
ALLEN 300
WARD 500
JONES have no comm
MARTIN 1400
BLAKE have no comm
CLARK have no comm
SCOTT have no comm
KING have no comm
TURNER 0
ADAMS have no comm
ENAME COMM
---------- ----------------------------------------
JAMES have no comm
FORD have no comm
MILLER have no comm
14 rows selected.
SQL>
NVL2
NVL2(expr1,expr2,expr3) 第一个有值返回第三个,否则返回第二个SQL> select ename,comm,sal,nvl2(comm,sal+comm,sal) "comm+sal" from emp;
ENAME COMM SAL comm+sal
---------- ---------- ---------- ----------
SMITH 800 800
ALLEN 300 1600 1900
WARD 500 1250 1750
JONES 2975 2975
MARTIN 1400 1250 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
TURNER 0 1500 1500
ADAMS 1100 1100
ENAME COMM SAL comm+sal
---------- ---------- ---------- ----------
JAMES 950 950
FORD 3000 3000
MILLER 1300 1300
14 rows selected.
SQL>
NULLIF
NULLIF(expr1,expr2) 相同返回空值,不同返回第一个SQL> select ename,comm from emp where ename='ALLEN';
ENAME COMM
---------- ----------
ALLEN 300
SQL> select ename,comm,NULLIF(comm,300) from emp where ename='ALLEN';
ENAME COMM NULLIF(COMM,300)
---------- ---------- ----------------
ALLEN 300
SQL> select ename,comm,NULLIF(comm,0) from emp where ename='ALLEN';
ENAME COMM NULLIF(COMM,0)
---------- ---------- --------------
ALLEN 300 300
SQL>
COALESCE
COALESCE(expr1,expr2,expr3,...exprn) 找非空,找到返回不再找了SQL> select coalesce(null,88,10) from dual;
COALESCE(NULL,88,10)
--------------------
88
SQL> select coalesce(null,null,null) from dual;
C
-
case when
SQL> select ename,deptno,sal,case deptno
2 when 10 then sal+100
3 when 20 then sal+200
4 else sal
5 end
6 newsal from emp;
ENAME DEPTNO SAL NEWSAL
---------- ---------- ---------- ----------
SMITH 20 800 1000
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3175
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2550
SCOTT 20 3000 3200
KING 10 5000 5100
TURNER 30 1500 1500
ADAMS 20 1100 1300
ENAME DEPTNO SAL NEWSAL
---------- ---------- ---------- ----------
JAMES 30 950 950
FORD 20 3000 3200
MILLER 10 1300 1400
14 rows selected.
SQL>
这样也行
SQL> select ename,deptno,sal,case when deptno=10 then sal+100
2 when deptno=20 then sal+200
3 else sal
4 end
5 newsal from emp;
ENAME DEPTNO SAL NEWSAL
---------- ---------- ---------- ----------
SMITH 20 800 1000
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3175
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2550
SCOTT 20 3000 3200
KING 10 5000 5100
TURNER 30 1500 1500
ADAMS 20 1100 1300
ENAME DEPTNO SAL NEWSAL
---------- ---------- ---------- ----------
JAMES 30 950 950
FORD 20 3000 3200
MILLER 10 1300 1400
14 rows selected.
SQL>
DECODE
SQL> select ename,deptno,sal,decode(deptno,10,sal+100,20,sal+200) newsal from emp;ENAME DEPTNO SAL NEWSAL
---------- ---------- ---------- ----------
SMITH 20 800 1000
ALLEN 30 1600
WARD 30 1250
JONES 20 2975 3175
MARTIN 30 1250
BLAKE 30 2850
CLARK 10 2450 2550
SCOTT 20 3000 3200
KING 10 5000 5100
TURNER 30 1500
ADAMS 20 1100 1300
ENAME DEPTNO SAL NEWSAL
---------- ---------- ---------- ----------
JAMES 30 950
FORD 20 3000 3200
MILLER 10 1300 1400
14 rows selected.
SQL>
0 0
- 【2017/5/2】oracle之sql(四)
- ORACLE SQL性能优化之四
- ORACLE PL/SQL编程详解之四
- oracle SQL简介(四)
- ORACLE SQL性能优化系列(四)
- ORACLE SQL性能优化系列(四)
- ORACLE SQL性能优化系列(四)
- ORACLE SQL性能优化(四)
- ORACLE SQL性能优化(四)
- (转)oracle sql%四种用法
- Oracle PL/SQL 学习笔记(四)
- oracle sql优化 四(转载)
- Oracle 笔记(四)、SQL 几个要点
- Oracle基础(四)pl/sql
- Oracle sql函数精解(四)
- oracle sql学习四
- oracle学习之路(四) ---------PL/SQL 表,二维数组(TABLE)
- ORACLE PL/SQL编程之四:把游标说透
- 【Linux】双系统:从ubuntu 下访问windows的磁盘
- APUE Exercises 10.5 pp381
- 调优之CPU调优
- Makefile
- Plugin with id 'android-apt' not found.
- 【2017/5/2】oracle之sql(四)
- HashMap源码阅读与解析
- 图像识别及处理相关数据集介绍
- 2.8.5_虚线的绘制
- Action、Category、Data、Extras知识详解
- 考研之路-始
- mysql在linux下的安装
- 【Python】Python学习笔记(一)——urllib类库基础
- JAVA单例模式