oracle复习

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;