SQL> select MONTHS_BETWEEN (to_date('2011-11-1','yyyy-mm-dd'),to_date('2011-1-1','yyyy-mm-dd')) from dual;
MONTHS_BETWEEN(TO_DATE('2011-11-1','YYYY-MM-DD'),TO_DATE('2011-1-1','YYYY-MM-DD'
--------------------------------------------------------------------------------
10
==========================================
SQL> select trunc(months_between(sysdate,to_date('2011-11-1','yyyy-mm-dd'))) from dual;
TRUNC(MONTHS_BETWEEN(SYSDATE,TO_DATE('2011-11-1','YYYY-MM-DD')))
----------------------------------------------------------------
7
SQL> select trunc(months_between(sysdate,to_date('2011-11-1','yyyy-mm-dd')),1) from dual;
TRUNC(MONTHS_BETWEEN(SYSDATE,TO_DATE('2011-11-1','YYYY-MM-DD')),1)
------------------------------------------------------------------
7.5
==========================================
SQL> select to_char(sysdate,'yyyy-MM-dd') from dual;
==========================================
SQL> select ename,nvl(comm,0) from fuxi_emp；
SQL> select ename,nvl(job,'--') from fuxi_emp ;
ENAME NVL(JOB,'
---------- ---------
SMITH --
ALLEN SALESMAN
SQL> select ename,nvl2(comm,comm,0) from fuxi_emp;
ENAME NVL2(COMM,COMM,0)
---------- -----------------
SMITH 0
ALLEN 300
WARD 500
已选择14行。
SQL> select ename,nvl2(comm,1,0) from fuxi_emp;
ENAME NVL2(COMM,1,0)
---------- --------------
SMITH 0
ALLEN 1
WARD 1
JONES 0
-----相等则返回空 否则返回第一个表达式
SQL> select ename,nullif(comm,0) from fuxi_emp;
ENAME NULLIF(COMM,0)
---------- --------------
SMITH
ALLEN 300
WARD 500
JONES
MARTIN 1400
BLAKE
CLARK
SCOTT
KING
COALESCE(expr1,expr2,...,exprn)
返回表达式列表里的第一个非空表达式
SQL> select case 300 when 300 then '300-' when 100 then '100--' end from dual;
CASE
----
300-
DECODE函数类似于一系列CASE 或 IF-THEN-ELSE 语句
DECODE(col/expression, search1, result1
[, search2, result2,...,]
[, default])
SQL> select to_char(last_day(to_date('2011-11-11','yyyy-MM-dd')),'yyyy-MM"月"dd') from dual;
SQL> select round(sysdate,'month') from dual;
ROUND(SYSDATE,
--------------
01-7月 -12
SQL> select round(add_months(sysdate,-2),'month') from dual;
ROUND(ADD_MONT
--------------
01-5月 -12
SQL> select to_char(trunc(sysdate,'month'),'yyyy') from dual;
TO_C
----
2012
SQL> select to_char(trunc(sysdate,'month'),'month') from dual;
TO_CHA
------
6月
SQL> select ename, decode(job,upper('clerk'),'业务员',upper('manager'),'经理') from fuxi_emp;
SQL> select round(2133.234,2) from dual;
SQL> select trunc(2133.235,2) from dual;
TRUNC(2133.235,2)
-----------------
2133.23
SQL> select trunc(2133.235,-2) from dual;
TRUNC(2133.235,-2)
------------------
2100
SQL> select round(2133.232,-2) from dual;
ROUND(2133.232,-2)
------------------
2100
SQL> select round(2153.232,-2) from dual;
ROUND(2153.232,-2)
------------------
2200
SQL> select mod(21,2) from dual;
MOD(21,2)
----------
1
select concat(ename,sal) from fuxi_emp;
SQL> select substr(ename,1,2) from fuxi_emp;
SQL> select ename,length(ename) from fuxi_emp;
SQL> select ename, instr(ename,upper('l')) from fuxi_emp;
SQL> select ename,lpad(ename,10,'*') from fuxi_emp;
ENAME LPAD(ENAME,10,'*')
---------- --------------------
SMITH *****SMITH
ALLEN *****ALLEN
WARD ******WARD
JONES *****JONES
MARTIN ****MARTIN
BLAKE *****BLAKE
CLARK *****CLARK
SCOTT *****SCOTT
KING ******KING
TURNER ****TURNER
ADAMS *****ADAMS
JAMES *****JAMES
FORD ******FORD
MILLER ****MILLER
select INITCAP('mKKKoo') FROM DUAL;
select UPPER('KKKKoo') FROM DUAL;
select lower('KKKKADS') FROM DUAL;
select user from dual;
select sysdata from dual;
--登陆
sqlplus scott/tiger
sqlplus /nolog
sqlplus “sys/oracle as sysdba”
sqlplus scott/tiger@abc
--Sql语句分类
DML语句（数据操作语言） Insert / Update / Delete / Merge
DDL语句（数据定义语言） Create / Alter / Drop / Truncate
DCL语句（数据控制语言） Grant / Revoke
事务控制语句 Commit / Rollback
set linesize 180;
set autocommit on 默认为off
set arraysize 20;
