r(char[,'format_model']);
SQL> select to_number('f','xxx') from dual;
TO_NUMBER('F','XXX')
--------------------
15
================================================
注意以下两个区别，列的对齐方式
SQL> select ename,sal from emp where deptno=10;
ENAME SAL
---------- ----------
CLARK 2450
KING 5000
MILLER 1300
SQL> c/sal/to_char(sal) sal/
1* select ename,to_char(sal) sal from emp where deptno=10
SQL> l
1* select ename,to_char(sal) sal from emp where deptno=10
SQL> /
ENAME SAL
---------- ----------------------------------------
CLARK 2450
KING 5000
MILLER 1300
==================================================================
to_date(char[,'format_model']);
SQL> select to_date('19811111','yyyy/mm/dd') from dual;
TO_DATE('
---------
11-NOV-81
空值函数
nvl(expr1,expr2)
SQL> select ename,sal,comm,sal+nvl(comm,0) from emp;
ENAME SAL COMM SAL+NVL(COMM,0)
---------- ---------- ---------- ---------------
SMITH 800 800
ALLEN 1600 300 1900
WARD 1250 500 1750
JONES 2975 2975
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
nvl2(expr1,expr2,expr3)
SQL> select nvl2(2,1,0) from dual;
NVL2(2,1,0)
-----------
1
SQL> c/2/null
1* select nvl2(null1,0) from dual
SQL> /
SQL> select nvl2(null,1,0) from dual;
NVL2(NULL,1,0)
--------------
0
nullif(expr1,expr2)
coalesce(expr1,expr2,...,exprn)
不停地查找，返回不为空的值
=隐式转化 oracle服务器自动完成
字符串转number
字符串转日期
条件表达式(分支函数)
case expr when ... then ...
else ...
end
SQL> select ename,deptno,sal,case deptno when 10 then sal*1.1
2 when 20 then sal*1.3
3 else sal
4 end isal from emp;
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 1040
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3867.5
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SQL> l
1 select ename,deptno,sal, case when deptno=10 then sal*1.1
2 when deptno=20 then sal*1.2
3 else sal
4* end isal from emp
SQL> /
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 960
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3570
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SCOTT 20 3000 3600
decode 可读性没有case好
SQL> select ename,deptno,sal,decode(deptno,10,sal*1.1,20,sal*1.2,sal) isal from emp;
ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------
SMITH 20 800 960
ALLEN 30 1600 1600
WARD 30 1250 1250
JONES 20 2975 3570
MARTIN 30 1250 1250
BLAKE 30 2850 2850
CLARK 10 2450 2695
SCOTT 20 3000 3600
SQL> select to_char(hiredate,'yyyy'),count(*) from emp group by to_char(hiredate,'yyyy');
TO_C COUNT(*)
---- ----------
1987 2
1980 1
1982 1
1981 10
行转列
SQL> select sum(decode(to_char(hiredate,'yyyy'),'1980',1)) "1980",sum(decode(to_char(hiredate,'yyyy'),'1981',1)) "1981" from emp;
1980 1981
---------- ----------
1 10
多行函数：每个行组集返回一个结果
分组函数 group by having限制分组个数
统计函数不统计空值
SQL> select count(comm) from emp;
COUNT(COMM)
-----------
4
SQL> select count(nvl(comm,0)) from emp; //效率低下
COUNT(NVL(COMM,0))
------------------
14
avg(),min(),max(),sum()
SQL> select job,min(sal) from emp group by job;
JOB MIN(SAL)
--------- ----------
CLERK 800
SALESMAN 1250
PRESIDENT 5000
MANAGER 2450
ANALYST 3000
SQL> select job,min(sal) from emp group by job having min(sal)>2000;
JOB MIN(SAL)
--------- ----------
PRESIDENT 5000
MANAGER 2450
ANALYST 3000
多表查询语句(实践必须掌握优化技巧)
等值连接与不等值连接
A.外连 |