oracle语法续

上一篇 / 下一篇 2014-05-27 17:29:17 / 个人分类：甲骨文

s%LPH4T@1z%n#h:| W/P0使用表达式51Testing软件测试网Q(_][ F?
=====================================================================
51Testing软件测试网CoI1r}};u
Select ename || '   是一位   ' || job As 雇员细节,51Testing软件测试网Xk5oh3S/ZO
        to_char(hiredate,'yyyy-mm-dd') As 雇佣时间,
        sal*1.2
From emp;
o$Gm2z&t3B$yjt0
=====================================================================
aYi8U:d*Y?|0 取消重复行 distinct51Testing软件测试网}[1U;TA+^$r
=====================================================================51Testing软件测试网g*B#d8Nte
51Testing软件测试网8e?R&Y+n.gT
Select Distinct deptno, job From emp;51Testing软件测试网S$|O!uzK,|

=====================================================================
指定列排序 order by
=====================================================================51Testing软件测试网olm6LUH
asc 升序，desc 降序51Testing软件测试网*@%I\.F'\"m M
--------------------------------------------51Testing软件测试网,C6Vs%p$[XV
Select * From emp
0R]N]Q,N0where sal between 1500 and 3000
,ci{nqyY$K0Order By deptno Desc,ename;
9r)}-Y\8w7w _5OJ0uW4\0
9g+i7p|@^9_a2VC$x0--------------------------------------------
I3[8c1c!V8TtwL0如果使用dsitinct,排序列必须是选择列51Testing软件测试网7L%sBQ f `,m2P
---------------------------
@8Hi \|Y!RH0select distinct depton, job
~!f-C|\5b@Le0from emp51Testing软件测试网8y5eq+})yS3AQ
order by job;
_bn[u?c-p'K0---------------------------------------------
order by 子句必须是最后一个子句
!r!]`*u[`}9e%d0============================================================51Testing软件测试网"[VO*a~mu.A
    分组查询语句
;|~8AW#V4P/xZ3IA d~0============================================================
%ZPNj$dO;D01。组处理函数不能出现在 where 子句中
0_}3J,{mXqA02。选择列表中的列、表达式，必须出现在 group by 子句中
3。组处理函数中可以指定 all 和 distinct51Testing软件测试网 Bs@b$_{)I
============================================================
'P5]kQ4|Y0分组函数
-L|3m&KB*|:b(x0---------------------------------------
py,vzrF0select avg(sal)            as avg1,51Testing软件测试网1CL$bSPCv
       avg(distinct sal)   as avg2,
       max(sal)            as max,
       min(sal)            as min,
       sum(sal)            as sum,
       count(*)            as cnt1,
(q[OI:U Q!s:B%A|5W0       count(sal)          as cnt2,51Testing软件测试网2pA*l~tE x
       count(distinct sal) as cnt351Testing软件测试网e7H~KT
from   emp
|erS(z!|0where deptno = 30;
51Testing软件测试网ZH7\#LQh:{5o
============================================================
A!cX E$U0单列分组
---------------------------------------------51Testing软件测试网?~4FeQU
select deptno, avg(sal), max(sal) from emp
group by deptno;51Testing软件测试网Bwo?;pO

select deptno, avg(sal), max(sal) from emp
group by deptno
order by avg(sal);

Bc4G]w.tq0============================================================
,}zhWh2o0多列分组
&VjA/y9yAz/A-d$h0---------------------------------------------51Testing软件测试网1d1d ~zI_&|
select deptno, job, avg(sal), max(sal), from emp
q!ULjZ9}!c0group by deptno, job;
WG;{*r6^0
*YA$?S(DJ8w_0============================================================
;Q3c5l ^6v5ja e0============================================================51Testing软件测试网 b!`d{.M0[*gO
rollup 用于生成横向统计信息
0x-p#H~*y:?M ~0--------------------------
#j[bzC#h O4R-Is3H0Select deptno, job, Avg(sal), Max(sal) From emp
2p;CfNy4X/v0Group By rollup(deptno,job)51Testing软件测试网&Rw&x{#h il

RAr7h^T0--------------------------------------------------51Testing软件测试网B2`MLw8a!?
cube 用于生产纵向统计信息
------------------------------
(v'N~ [M G]u0Select deptno, job, Avg(sal), Max(sal) From emp
]4_?BRF0Group By Cube(deptno,job)

&q0Css5Q-sM1@051Testing软件测试网3t f,qp~x"[)r
============================================================
Bh;H;_2u]~0      having 子句
============================================================
#k&_B`$Kv0H%e0select deptno, avg(sal), max(sal) from emp51Testing软件测试网np@ J/W t[
group by deptno51Testing软件测试网V6xu9A%W
having avg(sal) > 2000;51Testing软件测试网] n%|[Ra r~

:U)t3?p;Z?\gaV\,_0不等连接
----------------------------51Testing软件测试网?d;h.TM{&z
Select e.ename, e.sal, s.grade
,T&Gx`A l6H2ae0From emp e,salgrade s
4K)e#}A%F4y0Where e.sal Between s.losal And s.hisal 51Testing软件测试网8\]OV.d2l
And e.deptno = 30
^i9T7L%O0
b L[0?(KB-P&Iw0===========================================================51Testing软件测试网9sWA].iw
自连接
---------------------------------------
5a)CS&s#}fw_7r0Select e.ename As 雇员, p.ename As 管理员51Testing软件测试网1{i2{${y3G
From emp e,emp p
Where e.mgr = p.empno51Testing软件测试网W%z,rW.dT
And e.deptno = 30
k$y2?uz8FQ}2}051Testing软件测试网y-C`C:[cg
===========================================================
R-V2dl&j(MZ X g0       合并查询
6I5`5_+A"x_8A?0===========================================================51Testing软件测试网g"V x(c w"EpY
union --- 两个集合的并集，去掉重复行，按第一列结构排序51Testing软件测试网;L'v;\G&^
-------------------------51Testing软件测试网@6a8I/GB&Qz4K
Select empno, ename, mgr From emp
Where deptno = 30
H+W9`ff oW:ME*k7zz$[-V0Union
Select empno, ename, mgr From emp51Testing软件测试网)Yw,`(f5aC
Where job = 'MANAGER'
u0g X/N}"|0===========================================================51Testing软件测试网kpd7[]4i
union all --- 两个集合并集，不去重复行，不排序
nOKM7~q@"_d0-------------------------51Testing软件测试网-d.A4ph Bvt
Select empno, ename, mgr From emp51Testing软件测试网 nt,Q&y"@YM
Where deptno = 30
Union All
Select empno, ename, mgr From emp51Testing软件测试网d](SJE)B
Where job = 'MANAGER'
0Ry%py2j0
b,{q-i/I7ap6gn0===========================================================
MbV0S/I0Intersect --- 只会显示同时存在两个集合中的数据
}*De8Tg%oX0-----------------------------------
$a#]N,D%cZy0Select empno, ename, mgr From emp
4ItA2V^M0Where deptno = 30
G)vQ0B7LQbM{mQ%y0Intersect
b1lr;j d*a-R0Select empno, ename, mgr From emp51Testing软件测试网OQ9qK*O5xl
Where job = 'MANAGER'
L9N _[ t s0
Ejsb*R8[-IN7}:}0===========================================================
FAU\t^V0minus --- 在一个集合存在，在第二个集合不存在的数据，按第一个排序
i1y7V0Cs0-----------------------------------
Sp*[7g&_;MHl(wS0Select empno, ename, mgr From emp
Where deptno = 3051Testing软件测试网+|AJ%D!rC!|
Minus
Wv'h m'I6y0Select empno, ename, mgr From emp
Where job = 'MANAGER' 51Testing软件测试网I?HEGR8R
51Testing软件测试网)q9e"E;hc#p
===========================================================
zvphG @s0合并查询中，只能有一个 order by 子句。在这个子句中使用列名或51Testing软件测试网3H:`*jw"]q n
第一个查询的别名。51Testing软件测试网b%a%Yv~/L
--------------------------------------
Jt/[IC:O0Select empno, ename 雇员, mgr From emp
Where deptno = 30
z3u/b*Rl2D0Minus
;yu$?9u2j8t0g#V'EJ\0Select empno, ename 雇员, mgr From emp51Testing软件测试网Y0G;o1Zch
Where job = 'MANAGER'
)eoE8\_'H l(LwK0Order By 雇员51Testing软件测试网X[$w0f@*n

单行子查询
|6jwxw6U+l8k)J0------------------------------51Testing软件测试网k.B]gh x0er
Select ename, deptno, sal From emp
g3W yAj0Where sal = (Select Max(sal) From emp);51Testing软件测试网V bJ\+? po

k _7Q.o|6w0============================================================
多行子查询 --- where子句中使用多行子查询，必须使用多行运算符，
（in,notin,exists,not exists,all,any）
/y?7hbkY TDw[0---------------------------------------------------
*rmwW$~ r%J0Select ename, deptno, sal, job From emp
o{FjC0Where job In ( Select Distinct job From emp Where deptno = 20 )
X T vFuZ~`0
Acd5C6s+~Mu\B0------------------------------------------------------------51Testing软件测试网$VRX b#NA
Select e.ename, e.job, e.sal
%P;r z3Zyd+u(u0From emp e
:b*CZ9k Un ]S1Haq0Where sal > All ( Select sal From emp Where emp.deptno = 20)

------------------------------------------------------------
r1lFO!@I0Select e.ename, e.job, e.sal51Testing软件测试网s[ar}#_
From emp e
hS"lbsu,~b0Where sal > Any ( Select sal From emp Where emp.deptno = 20)
O'D:o-Bs:p+_h5s$Sq051Testing软件测试网 ni/A]@7XX
============================================================
umES3im(e&o`or0相关子查询
fF8S;z6c*x0------------------------------
Select deptno,
9b'Vw`dy0( Select Max(sal) From emp b Where b.deptno = a.deptno ) maxsal
r#D$un@#pB8b0From emp a
Order By deptno
O/bQEn0uq0------------------------------51Testing软件测试网]|n!q%[
Select ename, deptno, sal, job From emp
"k*o5_OHf D'c8b5]p0Where Exists
2^5n_}j1VQ H0(
~#tx$mt"c0 Select 'x' From dept
SJ'S;W*qS3{R j0 Where dept.deptno = emp.deptno And dept.loc = 'NEW YORK'51Testing软件测试网?x4GM](J
)
;EBJ_C)@[051Testing软件测试网i1B!p(v5}t$w}
============================================================51Testing软件测试网)Ej Zd\)qU
标量子查询 --- 显示每个部门的最高工资员工信息
C A)j WF0-----------------------
k.x&`HJXafDcGI0select distinct deptno,51Testing软件测试网#AH#T-tF
(select max(sal) from emp b where b.deptno = a.deptno) maxsal
w|N [1G\3c|X0from emp a
order by deptno;51Testing软件测试网&V.g\G'B

============================================================
多列子查询 --- 显示与smith部门和岗位完全相同的所有雇员信息。
-----------------------
Select ename, deptno, sal, job From emp
Where (deptno, job) =
&u)k-c#t fv0(Select deptno, job From emp Where ename = 'SMITH')
6jx2YM+?f&uRP0-----------------------51Testing软件测试网8rQYG6Zj'U
显示岗位或者管理员匹配于部门编号为20的所有雇员信息.51Testing软件测试网8v$d0z^*yu3d
-----------------------
Select ename, deptno, sal, job, mgr From emp51Testing软件测试网[-^vz,bu&o
Where job In ( Select job From emp Where deptno = 20 )
%wK9JoX~ |}O0Or mgr In ( Select mgr From emp Where deptno = 20 )
Order By deptno
51Testing软件测试网0o!tP \8X