oracle语法续

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

使用表达式51Testing软件测试网K!qzN ?;@\
=====================================================================51Testing软件测试网V[#T#B3hD

}[@0\u9uf0Select ename || '   是一位   ' || job As 雇员细节,51Testing软件测试网,ZRh NY2Xi
        to_char(hiredate,'yyyy-mm-dd') As 雇佣时间,51Testing软件测试网$S9T+\*o:F)bW U
        sal*1.251Testing软件测试网gpz)I"I3v
From emp;51Testing软件测试网l?-qt5Y$IY

=====================================================================
取消重复行 distinct
7R,x]jF5t0=====================================================================
JS8P1i)Y;kr,Hq4F&aYw0
J0X,o5w)H&^0Select Distinct deptno, job From emp;51Testing软件测试网!HJhO4p
51Testing软件测试网fa m!]~z
=====================================================================
k_@o1ysq`D3U!E;@0 指定列排序 order by51Testing软件测试网"G2k4j Vu,I
=====================================================================51Testing软件测试网Y%O.Q1@{A#JA
asc 升序，desc 降序51Testing软件测试网T R_U D
--------------------------------------------
"@:X:K2g*H0Select * From emp
where sal between 1500 and 3000
G&X7p"m;|8Y+F0Order By deptno Desc,ename;

--------------------------------------------
aHObX@dP4c0如果使用dsitinct,排序列必须是选择列51Testing软件测试网e3yS,x+];spr
---------------------------
4|4siY*z0select distinct depton, job
from emp
order by job;
Q8h Q.]^IJ|oGRm0---------------------------------------------
)iNy1V-E0order by 子句必须是最后一个子句51Testing软件测试网TY S/i;DS?
============================================================51Testing软件测试网ZQX7T Zg!w
    分组查询语句51Testing软件测试网Ny8Il+V4e,|k
============================================================
9nw(s7i-m*QxL01。组处理函数不能出现在 where 子句中
l,u?!lP02。选择列表中的列、表达式，必须出现在 group by 子句中
${Z9{cT03。组处理函数中可以指定 all 和 distinct
z tD9h3l T0============================================================51Testing软件测试网6u}}:dV-F `
分组函数
#pef.W)xla$cbS#N0---------------------------------------
select avg(sal)            as avg1,
(?ds\w4~0       avg(distinct sal)   as avg2,51Testing软件测试网 P!Y7o3v#coR
       max(sal)            as max,
\I$ge4q)LG$l`0       min(sal)            as min,
vp@(Q.KXMb7e"c0       sum(sal)            as sum,
       count(*)            as cnt1,
       count(sal)          as cnt2,
P&[u"K J6Uik5l0       count(distinct sal) as cnt3
%H I_L)A&O{9w0from   emp
CTIEbvN"A0where deptno = 30;
8W^hnh,[kH6h7?0
============================================================
_Z.bOA#Q%o+d|6o0单列分组
~+fu A]0---------------------------------------------51Testing软件测试网"cw b4n/P!zE
select deptno, avg(sal), max(sal) from emp51Testing软件测试网lI/yg1S\ m$Y
group by deptno;
[A4an;n0
yX)d ]&uci0select deptno, avg(sal), max(sal) from emp
{+Su_"e0group by deptno
order by avg(sal);51Testing软件测试网 ]dF!nY-Ov\

{!o,^A0h0============================================================51Testing软件测试网XG\-N$p
多列分组
,N:k5M'|'}fos W(G0---------------------------------------------
select deptno, job, avg(sal), max(sal), from emp
group by deptno, job;

qWbBzchD0============================================================
/tLBNf'j+r0============================================================
rollup 用于生成横向统计信息51Testing软件测试网!W)h hVX?k
--------------------------
BV2fJ8V0Select deptno, job, Avg(sal), Max(sal) From emp
)lN y;d)w0Vt;p0Group By rollup(deptno,job)51Testing软件测试网|T{.v7fl?8V

--------------------------------------------------
cube 用于生产纵向统计信息51Testing软件测试网 M"qBtd [1k
------------------------------
G8}+uX5s2N;C6F0Select deptno, job, Avg(sal), Max(sal) From emp
;t;s+P3P`} e0Group By Cube(deptno,job)
&m'x-ga:G7Y0
aH(G.xS;B,j3D3p0
T"s|#s:oen0============================================================
'`!d2D/F\'^#H0      having 子句51Testing软件测试网|H X-fy{qU
============================================================
&D"` lV5jUr(Zrc0select deptno, avg(sal), max(sal) from emp51Testing软件测试网J`.i;MqZ
group by deptno51Testing软件测试网&P;N M/V)}
having avg(sal) > 2000;
;PLE8w$Rr te!q051Testing软件测试网SVGW6@ lg5Z
不等连接
----------------------------51Testing软件测试网/U.ZJjcN
Select e.ename, e.sal, s.grade51Testing软件测试网%H"^VA&N5a ak
From emp e,salgrade s
5P*x)^rrinF0Where e.sal Between s.losal And s.hisal
K)sZ7AG-W8a0And e.deptno = 30

===========================================================
I?S)ioW0自连接
;N.Tg*{ZlFJ0---------------------------------------
$k|0`u/do3a0Select e.ename As 雇员, p.ename As 管理员
From emp e,emp p51Testing软件测试网K+[1Z:V%y z:L
Where e.mgr = p.empno
jW&ChQx0And e.deptno = 30
Z^0ah!BVX0
s[~/uJh7S&{ CS0===========================================================
      合并查询
F.dcwp0===========================================================
union --- 两个集合的并集，去掉重复行，按第一列结构排序
OII;z-[s s)R0-------------------------
"ule6i,y^a0Select empno, ename, mgr From emp51Testing软件测试网"NbiyKU
Where deptno = 3051Testing软件测试网 i z&Hf"x
Union
ez*?T,Vq.Cbj%i0Select empno, ename, mgr From emp
d{Y:[}1k){i7je0Where job = 'MANAGER'
zpJ _~9Hy0===========================================================
^Mw|:SA-[-H0union all --- 两个集合并集，不去重复行，不排序51Testing软件测试网6Pz6aln$VA
-------------------------
wg5M"\6E0Select empno, ename, mgr From emp
3D]8Nan?0Where deptno = 3051Testing软件测试网b[8|K)j\Z
Union All
FW!gQJ0Select empno, ename, mgr From emp
S*A u r-A2yfOw$Z0Where job = 'MANAGER'
+TTGF;yl!\;r(k(\4Y0
/W)ko,Fk;Z*z"Nl0===========================================================
f!B+l ?%E1\N+X0Intersect --- 只会显示同时存在两个集合中的数据
B9@A&y I`-E0-----------------------------------51Testing软件测试网atm#`AJal
Select empno, ename, mgr From emp
Where deptno = 30
bp1sPn~O{0Intersect
/W"X3A U&y;D0Select empno, ename, mgr From emp
!|W)l,Wly+Ke.V0Where job = 'MANAGER'

FO.y&ft#AV6k0===========================================================
:_~#h9ff1\9B0minus --- 在一个集合存在，在第二个集合不存在的数据，按第一个排序
M eSV"[0-----------------------------------
] B9UP({'Y5A b%Z@0Select empno, ename, mgr From emp51Testing软件测试网 qr8W7QLu9P
Where deptno = 3051Testing软件测试网&z9?Qp)|1p!n
Minus
Select empno, ename, mgr From emp51Testing软件测试网To Zx/|`r
Where job = 'MANAGER' 51Testing软件测试网!@9r5T(?2a8al
51Testing软件测试网nh-v-apY
===========================================================
{(BVJr5r ^0合并查询中，只能有一个 order by 子句。在这个子句中使用列名或
1xW*FU-d2s'BC0第一个查询的别名。
v2t"W;U1rHL5I k{/C0--------------------------------------
9Insci G0Select empno, ename 雇员, mgr From emp51Testing软件测试网7J9g-f:US a-F
Where deptno = 30
zPOu!hrHn0Minus51Testing软件测试网)_Xd8I ?Y%B
Select empno, ename 雇员, mgr From emp51Testing软件测试网VeX7C%t1Ex
Where job = 'MANAGER' 51Testing软件测试网7y/VuER!|H
Order By 雇员

单行子查询51Testing软件测试网8P eHWZ3n
------------------------------
m;dG T&l^0Select ename, deptno, sal From emp
(c6p:AF\cZJ4V0Where sal = (Select Max(sal) From emp);51Testing软件测试网 H*d0r/p_3`

/r5`1vCI$QKB0============================================================51Testing软件测试网#e,D[HY6@O
多行子查询 --- where子句中使用多行子查询，必须使用多行运算符，51Testing软件测试网:?'b8{\0j
（in,notin,exists,not exists,all,any）
[;g6U%r)C?0---------------------------------------------------51Testing软件测试网5XH$lE-@7eN
Select ename, deptno, sal, job From emp51Testing软件测试网 x]%N'ax]
Where job In ( Select Distinct job From emp Where deptno = 20 )
Rrn#I:~9GFc q!`y0
| o"_B&Uc_0------------------------------------------------------------
Select e.ename, e.job, e.sal
From emp e51Testing软件测试网5H4ZzC)P4I
Where sal > All ( Select sal From emp Where emp.deptno = 20)
)E fM{ h,{0
$?p*H!ohC(O0------------------------------------------------------------51Testing软件测试网*Ef7cV3X7zK
Select e.ename, e.job, e.sal
VGe-nhBU0From emp e
Where sal > Any ( Select sal From emp Where emp.deptno = 20)
8gfR?"yKn`0
============================================================
相关子查询51Testing软件测试网{2K!D^p"b
------------------------------51Testing软件测试网"O7T2CFI b.Vl
Select deptno,51Testing软件测试网 |4ejV@n9QI
( Select Max(sal) From emp b Where b.deptno = a.deptno ) maxsal
From emp a
Da|vX$R8P.h0Order By deptno51Testing软件测试网y5@9E+q U$}Z
------------------------------
gmyuH0Wav0Select ename, deptno, sal, job From emp
v(u4A'\2_0Where Exists
(
#YAyx I#K`)Z'j0 Select 'x' From dept51Testing软件测试网9@'_*njP1m
Where dept.deptno = emp.deptno And dept.loc = 'NEW YORK'
)
H'F5kt:[a051Testing软件测试网F+|tK3Q#X*y Q R
============================================================
4t iI*m%HZ0u0标量子查询 --- 显示每个部门的最高工资员工信息51Testing软件测试网ONT+t/B|
-----------------------
select distinct deptno,51Testing软件测试网.t[^So}
(select max(sal) from emp b where b.deptno = a.deptno) maxsal51Testing软件测试网'A ~H0\%@{{;}
from emp a
lYC^ q/xH7j0order by deptno;
n@p;i}}4V,B,}[+_ E051Testing软件测试网)^Rq!orA'A
============================================================
)J;qeDS(}t9O Ly;b0多列子查询 --- 显示与smith部门和岗位完全相同的所有雇员信息。
H$d+V$de%V3A,_0-----------------------
0e-Vd]0{y!c d2B0Select ename, deptno, sal, job From emp51Testing软件测试网?-\*o(s(c
Where (deptno, job) =
(Select deptno, job From emp Where ename = 'SMITH')
-----------------------
f9~rMi} a R0 显示岗位或者管理员匹配于部门编号为20的所有雇员信息.
Tf@W+O8ida8d0-----------------------
*I4s](W(M9e S4kq5w~0Select ename, deptno, sal, job, mgr From emp
Where job In ( Select job From emp Where deptno = 20 )
Or mgr In ( Select mgr From emp Where deptno = 20 )
@$Z(fdk"@*`C)Oy8AG"@0Order By deptno51Testing软件测试网;rH(gfV/lo

51Testing软件测试网7b)p W@uN