Oracle SQL用法

這個是對于Oracle數據庫的sql基本語句，SQL plus執行通過的------------------------------------------------------------------select empno, to_char(sal,'999,999.99') sal from emp;select distinct deptno from emp; select empno,ename,sal*0.5 from emp where deptno=10;select empno''ename,nvl(sal,0)+nvl(comm,0) from emp;select empno,ename,job,sal from emp where empno=&empno;select sysdate,user,uid,rowid,rownum from emp;[sysdate,user,uid,rowid,rownum為偽列]select empno,ename,comm from emp where comm=null;[comm is null];select empno,ename,nvl(comm,'0') from emp where comm is null;select deptno,dname from dept where deptno in(30,40);select deptno,dname,loc from dept where loc not in('NEW YORK','CHICAGO');select deptno,ename,sal from emp where deptno=10 or deptno=20 and sal>3000;[列別名]select e.ename EMPLOYEE,e.sal*1.15 NEW_SAl from emp e where e.deptno=10;[多表連接]select d.dname,e.ename,e.sal,e.comm from emp e,dept d where d.deptno=e.deptno order by d.deptno;[使用子查詢]select ename from emp where deptno=(select deptno from dept where dname='SALES');[查詢別名]select e.ename,d.dname,e.deptno'=='d.deptno from emp e,(select deptno,dname from dept where loc='NEW YORK') dwhere e.deptno=d.deptnoorder by d.deptno;[union:聯合]select ename,sal,comm from empunionselect 'TOTAL',sum(sal),sum(comm) from emp order by salENAME;SAL;;;COMM---------- --------- ---------SMITH;800JAMES;950ADAMS1100SCOTT3000KING;5000TOTAL; 29025;;;2200------------------------------[intersect:相交]select ename,sal,comm from emp where sal>1300INTERSECTselect ename,sal,comm from emp where comm is not null===select ename,sal,comm from emp where sal>1300 and comm is not nullENAME;SAL;;;COMM---------- --------- ---------ALLEN1600;;;;300TURNER;;; ;;;;1500 0------------------------------[minus]select ename,sal comm from emp where sal>1300minusselect ename,sal comm from emp where sal>1500;===select ename,sal,comm from emp where sal>1300 and not(sal>1500)ENAMECOMM---------- ---------TURNER; 1500--------------------select to_char(sysdate,'yyyy/mm/dd hh24:mi') sys_date from dual;select to_date('2002/08/13','yyyy/mm/dd') from dual;select to_number('12345',99999) from dual;select empno,ename from emp where months_between(sysdate,hiredate)>=12; add_months(date,number) last_day(date) months_between(date1,date2) next_dat(date,day) round(date,format) trunc(date,format)---------------------數值函數 abs(number) ceil(number) cos(number) ln(number) mod(n,m) round(number,decimal_digits) sign(number) sqrt(number) sin(number)-------------------字符函數; ascii(character) chr(number) concat(string1,string2) # initcap(string) length(string) lower(string)upper(string) substr(string,start[,length]) replace(string,search_string,replace_string)-------------------other greatest(list of values) least(list of values) nvl(eXPression,replacement_value) AVG(expression) COUNT(expression) MAX(expression) MIN(expression) SUM(expression)Welcome>select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;COUNT(*); SUM(SAL); AVG(SAL); MAX(SAL); MIN(SAL)--------- --------- --------- --------- --------- 14;;29025 2073.2143;;;5000;;;;800---------------------------------------------------------------------------[右連接：如下圖，假如出現條件不符和的，以左邊為主/e.deptno/,右邊的/d.deptno/應該以空行還填補左邊顯示的內容]select d.dname,e.ename from emp e,dept d where e.deptno=d.deptno(+) order by d.dname,e.ename； 1; select d.dname D_Dname,e.ename E_Ename,d.deptno D_Deptno,e.deptno E_Deptno from emp e,dept d 2* where e.deptno=d.deptno(+) order by d.dname,e.enameWelcome>/D_DNAME;;;;;E_ENAME;;D_DEPTNO; E_DEPTNO-------------- ---------- --------- ---------ACCOUNTING;;CLARK;;10;;;;;10ACCOUNTING;;KING;;;10;;;;;10ACCOUNTING;;MILLER;10;;;;;10RESEARCH;;;;ADAMS;;20;;;;;20RESEARCH;;;;FORD;;;20;;;;;20RESEARCH;;;;JONES;;20;;;;;20RESEARCH;;;;SCOTT;;20;;;;;20RESEARCH;;;;SMITH;;20;;;;;20SALES; ALLEN;;30;;;;;30SALES; BLAKE;;30;;;;;30SALES; JAMES;;30;;;;;30SALES; MARTIN;30;;;;;30SALES; TURNER;30;;;;;30SALES; WARD;;;30;;;;;30---------------------------------------------[左連接：如下圖，假如出現條件不符和的，以右邊為主/d.deptno/,左邊的/e.deptno/應該以空行還填補右邊顯示的內容]select d.dname D_Dname,e.ename E_Ename,d.deptno D_Deptno,e.deptno E_Deptno from emp e,dept dwhere e.deptno(+)=d.deptno order by d.dname,e.enameD_DNAME; ;;;;E_ENAME;;D_DEPTNO; E_DEPTNO-------------- ---------- --------- ---------ACCOUNTING;;CLARK;;10;;;;;10ACCOUNTING;;KING;;;10;;;;;10ACCOUNTING;;MILLER;10;;;;;10OPERATIONS;;;;40RESEARCH;;;;ADAMS;;20;;;;;20RESEARCH;;;;FORD;;;20;;;;;20RESEARCH;;;;JONES;;20;;;;;20RESEARCH;;;;SCOTT;;20;;;;;20RESEARCH;;;;SMITH;;20;;;;;20SALES; ALLEN;;30;;;;;30SALES; BLAKE;;30;;;;;30SALES; JAMES;;30;;;;;30SALES; MARTIN;30;;;;;30SALES; TURNER;30;;;;;30SALES; WARD;;;30;;;;;30---------------------------------------------[自連接：同一表表根據別名來訪問]select a.ename A_ename,b.ename B_ename,a.mgr A_mgr,b.empno B_empnofrom emp a,emp bwhere a.mgr=b.empnoorder by b.ename,a.enameA_ENAME; B_ENAME;;;;;A_MGRB_EMPNO---------- ---------- --------- ---------ALLEN;;;BLAKE7698;;;7698JAMES;;;BLAKE7698;;;7698MARTIN;;BLAKE7698;;;7698TURNER;;BLAKE7698;;;7698WARD;;;;BLAKE7698;;;7698MILLER;;CLARK7782;;;7782SMITH;;;FORD;7902;;;7902FORD;;;;JONES7566;;;7566SCOTT;;;JONES7566;;;7566BLAKE;;;KING;7839;;;7839CLARK;;;KING;7839;;;7839JONES;;;KING;7839;;;7839ADAMS;;;SCOTT7788;;;7788-----------------------------------------select e.deptno,e.ename from emp e where exists(select 'x' from dept d where e.deptno=d.deptnoand d.loc='NEW YORK')order by e.empno; DEPTNO ENAME--------- ---------- 10 CLARK 10 KING 10 MILLER