oracle语法续

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

]"b|;g;w8P h _J h0使用表达式
Hi` [D#^ ravm({0=====================================================================

/w$Tp;K1sL^(h0Select ename || '   是一位   ' || job As 雇员细节,
uG)R+F[0        to_char(hiredate,'yyyy-mm-dd') As 雇佣时间,
        sal*1.2
}h M#~(^8C:WU!J0From emp;
g;A9?FG0
=====================================================================51Testing软件测试网.Q5KSC.B{
取消重复行 distinct51Testing软件测试网'B NEM)W#]MW
=====================================================================
x&S_5A9F2E~051Testing软件测试网i |V:z X0A
Select Distinct deptno, job From emp;
8_.Rlp3Zl$Ij-p0
\B:[ y3M2X0=====================================================================
3A,G-d3b)ELo*z$C({)i0 指定列排序 order by
Y0RF([*U.R0=====================================================================
*N\/i vp`6EB4j)}0asc 升序，desc 降序
BI%v$zUQ0--------------------------------------------51Testing软件测试网 pB5j,\%o!PU
Select * From emp51Testing软件测试网Y4\vn1Cf
where sal between 1500 and 300051Testing软件测试网s'm0_ZM2}
Order By deptno Desc,ename;
-q'C @PE^051Testing软件测试网+N(M6a(V4J
--------------------------------------------51Testing软件测试网q#F2sO"\
如果使用dsitinct,排序列必须是选择列
9o!Z-h,]0_0---------------------------
"d3yQ&|rY0select distinct depton, job 51Testing软件测试网l1LR#CN*r
from emp
:HYh~JS0{}%^0order by job;
!z(M'c'{{k{kjZ0---------------------------------------------
W/YZ1mmL3\gH0order by 子句必须是最后一个子句
============================================================51Testing软件测试网 p+w6] oeR
    分组查询语句
============================================================
1。组处理函数不能出现在 where 子句中
;q:OE AOC02。选择列表中的列、表达式，必须出现在 group by 子句中
2sZ"a3yYHm03。组处理函数中可以指定 all 和 distinct
============================================================
!LyC;Gc|!w,`4s0分组函数51Testing软件测试网`d$~ R:fr%m
---------------------------------------
GKV$xF:M3[#O.p7~j'[0select avg(sal)            as avg1,51Testing软件测试网,c_t}2z:t
       avg(distinct sal)   as avg2,
IN2b~M%jb,g$s%[E0       max(sal)            as max,
n_pjD&m-@`0b0       min(sal)            as min,51Testing软件测试网`U5]hZ2A[
       sum(sal)            as sum,
R$w'i8z9mA0       count(*)            as cnt1,
DO4v!V@K;k0       count(sal)          as cnt2,
1P9[\z1C J0       count(distinct sal) as cnt3
"o0N4V*@&C'DhtX0from   emp
d^0\\O4[`0where deptno = 30;
+}I'[ C5Vt-X/I_e0
]payFyoQv${0============================================================51Testing软件测试网|_i2k$d!g
单列分组
k0g!D.J(V^5s*Wg A0---------------------------------------------
select deptno, avg(sal), max(sal) from emp51Testing软件测试网+zP*G([!N^6CQ
group by deptno;

select deptno, avg(sal), max(sal) from emp
g%D8_;n*xm/Z'DL,P0group by deptno
X0gJ/H8K E0order by avg(sal);51Testing软件测试网6a.t"^,b#F:a

RMzQ!qd)j{0============================================================
多列分组
;Q_U6[7OnG0---------------------------------------------51Testing软件测试网SK#w/O%kq%Z
select deptno, job, avg(sal), max(sal), from emp
group by deptno, job;51Testing软件测试网,b3ag#]a7?;q

v:x ]4n&q7~k0============================================================
============================================================51Testing软件测试网@A,OC Y/y!n
rollup 用于生成横向统计信息
--------------------------51Testing软件测试网u_&\hS\"`W
Select deptno, job, Avg(sal), Max(sal) From emp
-\ZZ:EnPsO0Group By rollup(deptno,job)

s0{~ WN6V0--------------------------------------------------
vBV~XG,F0cube 用于生产纵向统计信息
------------------------------
&g^:mxB"\$z0Select deptno, job, Avg(sal), Max(sal) From emp
0F:t4R2CDC\q0Group By Cube(deptno,job)
~4H1bX)dW0
5D+D/_L a0
7tp_m6Z"_)ad0============================================================
     having 子句
d(p"t ]"v]0============================================================
z4U#V^#@:s0select deptno, avg(sal), max(sal) from emp
[8[3pP8X7R"o0group by deptno
+q1WtV-|7aUPwUL0having avg(sal) > 2000;
51Testing软件测试网(F2g*nFh
不等连接
5?a#^F;P~8k0----------------------------
Select e.ename, e.sal, s.grade51Testing软件测试网T+O'JVb
From emp e,salgrade s
!^t0z.qT8\gJ0Where e.sal Between s.losal And s.hisal
F:gF Y)@h6o0And e.deptno = 30
5yZL|G.B`[%T051Testing软件测试网d? h&k4v.A
===========================================================
:N/W!Z'}8Tg`PnM0自连接
b(Di2l#@i0---------------------------------------
x+b!TSXO?3xD0Select e.ename As 雇员, p.ename As 管理员
F8l!wT)m+UpL0From emp e,emp p51Testing软件测试网4CQ[_o#O"M
Where e.mgr = p.empno
:YyLZ e OD%L0And e.deptno = 30
bjc @5|$U*y/j%KwH0
7?#?pd7~6b@0===========================================================
      合并查询51Testing软件测试网)Y7|7{ d%F3}
===========================================================
LdOu"L,J'}0union --- 两个集合的并集，去掉重复行，按第一列结构排序51Testing软件测试网\Swm-m
-------------------------51Testing软件测试网$\JJ7mrE
Select empno, ename, mgr From emp
a5MK*~\O(i.S!B0Where deptno = 3051Testing软件测试网t C-@Bc'N$uz
Union
Select empno, ename, mgr From emp51Testing软件测试网AN.r zS$X
Where job = 'MANAGER'
9{spBG2lDf Z0===========================================================
o(~`{D"O`o0union all --- 两个集合并集，不去重复行，不排序
byLP'P9j^0-------------------------
Y;V7?.\@M-k4H0Select empno, ename, mgr From emp
v3iErr ?G)pQm0Where deptno = 30
f So2wHi$j}0Union All51Testing软件测试网iK {7k,m
Select empno, ename, mgr From emp
*H M.n_;Uy0Where job = 'MANAGER'
i#{k"x0g7L0
===========================================================
;LC f8g Q;xXG0Intersect --- 只会显示同时存在两个集合中的数据
-----------------------------------
Uc;UbC)Sj)@ t0Select empno, ename, mgr From emp
Mznk4tm0Where deptno = 3051Testing软件测试网iD.IB(`5J
Intersect
q^8W%r4zw2x+q pb0Select empno, ename, mgr From emp
#}X g|^.R-[0Where job = 'MANAGER'
zT O\.g,@7sdA$@Ou0
===========================================================
minus --- 在一个集合存在，在第二个集合不存在的数据，按第一个排序
-----------------------------------51Testing软件测试网B0T1q#Q&|9k-s
Select empno, ename, mgr From emp
Where deptno = 30
-ssd,L2G H&D\]S0Minus51Testing软件测试网)[n1T{$@
Select empno, ename, mgr From emp
j8pm8}qxM6R?0Where job = 'MANAGER'
n*y,s;OB/e%muZ"U0
===========================================================51Testing软件测试网%g0^+HYrM1A
合并查询中，只能有一个 order by 子句。在这个子句中使用列名或
第一个查询的别名。
A3SKY-Y6d)Uy0d0--------------------------------------51Testing软件测试网\x3w},^|
Select empno, ename 雇员, mgr From emp
;h/u6R&[:Z9R$cY`%M]'F0Where deptno = 30
Minus51Testing软件测试网.y'Br0f {
Select empno, ename 雇员, mgr From emp
oVx-N9k!Yp*F{0Where job = 'MANAGER' 51Testing软件测试网,^fs|v~g
Order By 雇员51Testing软件测试网W/VsF]+Q;U

bD-MS!?9m~D$H4b0单行子查询
------------------------------51Testing软件测试网(v$w*X}:X]N
Select ename, deptno, sal From emp51Testing软件测试网"fxR Mt+o sV
Where sal = (Select Max(sal) From emp);

============================================================51Testing软件测试网W.c;C6WY7U
多行子查询 --- where子句中使用多行子查询，必须使用多行运算符，
c^$p:wA:Qx0 （in,notin,exists,not exists,all,any）
|a%FK'R0U%n1E'l0---------------------------------------------------
:CGv*Ew-K%^0Select ename, deptno, sal, job From emp
*Q0OtVL0Where job In ( Select Distinct job From emp Where deptno = 20 )
51Testing软件测试网U2f6r^%r:z#F
------------------------------------------------------------
6^FJ E.lX2z0Select e.ename, e.job, e.sal
From emp e
([2adnMg.@bL0Where sal > All ( Select sal From emp Where emp.deptno = 20)
R:V(MBz)o^p2C0
------------------------------------------------------------
+l(x Y.`Yw0Select e.ename, e.job, e.sal
:m6Q2F#zJ0From emp e
.rh['j'px2W0Where sal > Any ( Select sal From emp Where emp.deptno = 20)51Testing软件测试网Q4a"RA_1T

8x:BfY+a)}]!F.gy0============================================================
4B}!Xf.g$cVU]7M0相关子查询51Testing软件测试网,kZ3L] ww
------------------------------
"i6~)\z\$}0Select deptno,
( Select Max(sal) From emp b Where b.deptno = a.deptno ) maxsal
From emp a
"q _,YW3n#l)q)Ik'W0Order By deptno51Testing软件测试网5HL3ZR:|Y
------------------------------
Select ename, deptno, sal, job From emp
Where Exists51Testing软件测试网-ThG5O j)z
(
\ rw|i@0 Select 'x' From dept
~m8E,|0|D0 Where dept.deptno = emp.deptno And dept.loc = 'NEW YORK'51Testing软件测试网a&s&jI;S#~
)
+V3M:?e/w#O0
============================================================
标量子查询 --- 显示每个部门的最高工资员工信息
@_.vI9Sj ^9KQ0-----------------------51Testing软件测试网{w%r/\Y
select distinct deptno,
:y mTt3yEZ |Y0(select max(sal) from emp b where b.deptno = a.deptno) maxsal
M d0SV+B ^0from emp a
!}o{2q"y0order by deptno;51Testing软件测试网(W0u e@%EG/I&B

3dt;_@-k&Rk!sG0============================================================
多列子查询 --- 显示与smith部门和岗位完全相同的所有雇员信息。
,kW.f{H*D0-----------------------
Select ename, deptno, sal, job From emp
N;c6x8[ZImY$Y0Where (deptno, job) =
s-L#[#xd oF2q0(Select deptno, job From emp Where ename = 'SMITH')
:F\-g0]r4O0-----------------------
2L H'p2S.py_0i0 显示岗位或者管理员匹配于部门编号为20的所有雇员信息.
'R)J)S5e J+mjs ^0-----------------------
kne0Ea8L0Select ename, deptno, sal, job, mgr From emp
4u(H0ZQS/g#G(T0Where job In ( Select job From emp Where deptno = 20 )
Or mgr In ( Select mgr From emp Where deptno = 20 )51Testing软件测试网 `o+O,w4t?+Fo
Order By deptno
gv_.yG\ G0f$Z_,@k0
zZ6xjv0