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')))---------------------------------------------------------------- 7SQL> 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 SALESMANSQL> select ename,nvl2(comm,comm,0) from fuxi_emp;ENAME NVL2(COMM,COMM,0)---------- -----------------SMITH 0ALLEN 300WARD 500已选择14行。SQL> select ename,nvl2(comm,1,0) from fuxi_emp;ENAME NVL2(COMM,1,0)---------- --------------SMITH 0ALLEN 1WARD 1JONES 0-----相等则返回空 否则返回第一个表达式SQL> select ename,nullif(comm,0) from fuxi_emp;ENAME NULLIF(COMM,0)---------- --------------SMITHALLEN 300WARD 500JONESMARTIN 1400BLAKECLARKSCOTTKING
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月 -12SQL> 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----2012SQL> 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.23SQL> select trunc(2133.235,-2) from dual;TRUNC(2133.235,-2)------------------ 2100SQL> select round(2133.232,-2) from dual;ROUND(2133.232,-2)------------------ 2100SQL> select round(2153.232,-2) from dual;ROUND(2153.232,-2)------------------ 2200SQL> 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 *****SMITHALLEN *****ALLENWARD ******WARDJONES *****JONESMARTIN ****MARTINBLAKE *****BLAKECLARK *****CLARKSCOTT *****SCOTTKING ******KINGTURNER ****TURNERADAMS *****ADAMSJAMES *****JAMESFORD ******FORDMILLER ****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/tigersqlplus /nologsqlplus “sys/oracle as sysdba”sqlplus scott/tiger@abc
--Sql语句分类DML语句（数据操作语言） Insert / Update / Delete / MergeDDL语句（数据定义语言） Create / Alter / Drop / TruncateDCL语句（数据控制语言） Grant / Revoke事务控制语句 Commit / Rollback
set linesize 180;
set autocommit on 默认为off set arraysize 20;