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;