这个是对于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') d
where e.deptno=d.deptno
order by d.deptno;
[union:联合]
select ename,sal,comm from emp
union
select 'TOTAL',sum(sal),sum(comm) from emp order by sal

ENAME;SAL;;;COMM
---------- --------- ---------
SMITH;800
JAMES;950
ADAMS1100
SCOTT3000
KING;5000
TOTAL; 29025;;;2200
------------------------------
[intersect:相交]
select ename,sal,comm from emp where sal>1300
INTERSECT
select ename,sal,comm from emp where comm is not null
===select ename,sal,comm from emp where sal>1300 and comm is not null

ENAME;SAL;;;COMM
---------- --------- ---------
ALLEN1600;;;;300
TURNER;;;
;;;;1500 0
------------------------------
[minus]
select ename,sal comm from emp where sal>1300
minus
select 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.ename
Welcome>/

D_DNAME;;;;;E_ENAME;;D_DEPTNO; E_DEPTNO
-------------- ---------- --------- ---------
ACCOUNTING;;CLARK;;10;;;;;10
ACCOUNTING;;KING;;;10;;;;;10
ACCOUNTING;;MILLER;10;;;;;10
RESEARCH;;;;ADAMS;;20;;;;;20
RESEARCH;;;;FORD;;;20;;;;;20
RESEARCH;;;;JONES;;20;;;;;20
RESEARCH;;;;SCOTT;;20;;;;;20
RESEARCH;;;;SMITH;;20;;;;;20
SALES; ALLEN;;30;;;;;30
SALES; BLAKE;;30;;;;;30
SALES; JAMES;;30;;;;;30
SALES; MARTIN;30;;;;;30
SALES; TURNER;30;;;;;30
SALES; 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 d
where e.deptno(+)=d.deptno order by d.dname,e.ename

D_DNAME;
;;;;E_ENAME;;D_DEPTNO; E_DEPTNO
-------------- ---------- --------- ---------
ACCOUNTING;;CLARK;;10;;;;;10
ACCOUNTING;;KING;;;10;;;;;10
ACCOUNTING;;MILLER;10;;;;;10
OPERATIONS;;;;40
RESEARCH;;;;ADAMS;;20;;;;;20
RESEARCH;;;;FORD;;;20;;;;;20
RESEARCH;;;;JONES;;20;;;;;20
RESEARCH;;;;SCOTT;;20;;;;;20
RESEARCH;;;;SMITH;;20;;;;;20
SALES; ALLEN;;30;;;;;30
SALES; BLAKE;;30;;;;;30
SALES; JAMES;;30;;;;;30
SALES; MARTIN;30;;;;;30
SALES; TURNER;30;;;;;30
SALES; WARD;;;30;;;;;30
---------------------------------------------
[自连接：同一表表根据别名来访问]
select a.ename A_ename,b.ename B_ename,a.mgr A_mgr,b.empno B_empno
from emp a,emp b
where a.mgr=b.empno
order by b.ename,a.ename

A_ENAME;
B_ENAME;;;;;A_MGRB_EMPNO
---------- ---------- --------- ---------
ALLEN;;;BLAKE7698;;;7698
JAMES;;;BLAKE7698;;;7698
MARTIN;;BLAKE7698;;;7698
TURNER;;BLAKE7698;;;7698
WARD;;;;BLAKE7698;;;7698
MILLER;;CLARK7782;;;7782
SMITH;;;FORD;7902;;;7902
FORD;;;;JONES7566;;;7566
SCOTT;;;JONES7566;;;7566
BLAKE;;;KING;7839;;;7839
CLARK;;;KING;7839;;;7839
JONES;;;KING;7839;;;7839
ADAMS;;;SCOTT7788;;;7788
-----------------------------------------
select e.deptno,e.ename from emp e
where exists
(select 'x' from dept d where e.deptno=d.deptno
and d.loc='NEW YORK')
order by e.empno;

DEPTNO ENAME
--------- ----------
10 CLARK
10 KING
10 MILLER