oracle语法续

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

使用表达式
=====================================================================
.U/zWgQ"o)n)I w0
-K,xjM b2k2S0Select ename || '   是一位   ' || job As 雇员细节,51Testing软件测试网8If&n4`Hu8S'Q
        to_char(hiredate,'yyyy-mm-dd') As 雇佣时间,51Testing软件测试网gd?L2mn3F3P
        sal*1.2
7yMhc2K.j]0From emp;
6[-q]_m051Testing软件测试网%v){?v*|.NS
=====================================================================51Testing软件测试网Sg&|7YR;P e1b
取消重复行 distinct
\j sC%Nu0=====================================================================51Testing软件测试网;W2T/Qo5LbP

5a9|%b1L"K:P$LuO,| Y0Select Distinct deptno, job From emp;

Yv;R*tG0=====================================================================51Testing软件测试网X7M p/b`_7l
指定列排序 order by
=====================================================================51Testing软件测试网Nz0_1g6W
asc 升序，desc 降序51Testing软件测试网K9VP#UPu Y
--------------------------------------------
Select * From emp51Testing软件测试网%g%`9Jn0CX
where sal between 1500 and 3000
~RL5t]'d0Order By deptno Desc,ename;
t p8VQjp0
N#I*FHGX kv0--------------------------------------------
如果使用dsitinct,排序列必须是选择列
---------------------------
he;o$y"q#ynm&nfQ0select distinct depton, job
0M1]8j4B,N] H'C~$T0from emp51Testing软件测试网k2m}z$M'UB
order by job;
wKjm y@V0---------------------------------------------
order by 子句必须是最后一个子句
============================================================
:xe2aL F/Z7M S0     分组查询语句51Testing软件测试网1G1lB7e:yyL
============================================================
'iamMM ^%CB01。组处理函数不能出现在 where 子句中
"}L q4Ey:g02。选择列表中的列、表达式，必须出现在 group by 子句中51Testing软件测试网[7s+GQKf)R
3。组处理函数中可以指定 all 和 distinct51Testing软件测试网+H)i;b2OU
============================================================
分组函数51Testing软件测试网`DxsD
---------------------------------------51Testing软件测试网-M%~u@ T-^
select avg(sal)            as avg1,51Testing软件测试网 HU.\/E_I
       avg(distinct sal)   as avg2,
       max(sal)            as max,
,p _wBEV@0       min(sal)            as min,
fh TZP1YA0       sum(sal)            as sum,
       count(*)            as cnt1,
       count(sal)          as cnt2,
       count(distinct sal) as cnt3
from   emp
3OZ'\oh9e:n6x0where deptno = 30;
$X,WE.k^ IdU0
&j*~W"?y*h%mY9?0============================================================
A|y#or hX,Q0单列分组51Testing软件测试网6y? q%n c"]9V
---------------------------------------------
&W~n){7zo9\nh0select deptno, avg(sal), max(sal) from emp
SM6d0?v$k%|0group by deptno;
q|,z5V:x_8vF{051Testing软件测试网,h/wz|G
select deptno, avg(sal), max(sal) from emp
6T5^)h~O`Y0group by deptno51Testing软件测试网M![)|^ WY
order by avg(sal);51Testing软件测试网0bFo3KTG)O'P

9uu;U\8@$zos3R1\j0============================================================
多列分组
*Mn PP!v0---------------------------------------------
'H"^#J:L d+hz!fV0select deptno, job, avg(sal), max(sal), from emp
tD:P(R9lxV0group by deptno, job;

E1v4i*[ fNd(^)?0============================================================51Testing软件测试网;Cis$c$t"C5I
============================================================
Z,_Hbcx/[]0rollup 用于生成横向统计信息
o-bsvo;kgY$p0--------------------------51Testing软件测试网;[['n;W6VI(f
Select deptno, job, Avg(sal), Max(sal) From emp
)a b1vyeP+l8Mop-i0Group By rollup(deptno,job)
:`"a+sil_4})D0
--------------------------------------------------
|$A9M2d2nH6T"X0cube 用于生产纵向统计信息51Testing软件测试网#h8C$Jh2w
------------------------------51Testing软件测试网d b/Ip:F\z
Select deptno, job, Avg(sal), Max(sal) From emp51Testing软件测试网inq$i^ uD
Group By Cube(deptno,job)

V)m XniL{$Qr051Testing软件测试网;o0g^c@h0qD
============================================================51Testing软件测试网}e2OBOcc
     having 子句
"qt?M7Oe'Wm:dG0============================================================
v0z j2c_)b q8xg0select deptno, avg(sal), max(sal) from emp
$`[@I,A^%wM7cc{G0group by deptno
4P~^fd,n6|F&w0having avg(sal) > 2000;51Testing软件测试网(JFuX1A vq#A

不等连接
----------------------------51Testing软件测试网;N9k Ha5g*[i
Select e.ename, e.sal, s.grade
J%u8}+R|$Q!ru'?[V0From emp e,salgrade s
1Yh\d!Dxm3nVB,E0Where e.sal Between s.losal And s.hisal 51Testing软件测试网,ou|.[`*~"@
And e.deptno = 30
DBN0}b0
1^M2p:f(u'A4g0===========================================================
)@8T8HS}E B0自连接
---------------------------------------
q.iu M*K u6l0Select e.ename As 雇员, p.ename As 管理员51Testing软件测试网)d'Buj*GB\3b
From emp e,emp p
pG2T&f+n9O@4r$f0Where e.mgr = p.empno
x:OE4A&p2N0And e.deptno = 30

===========================================================
2^0GN#t9A sjmz8I0       合并查询
1g!~m"j9n \HD0===========================================================
$R#Yo/d6]s0union --- 两个集合的并集，去掉重复行，按第一列结构排序
-------------------------
Select empno, ename, mgr From emp
!ioW1Drr&@%mh0Where deptno = 30
-ra E#I W!]*d1O7?e3l0Union
Zbz9wt"N q2L sMb0Select empno, ename, mgr From emp51Testing软件测试网-V}F;rA6G
Where job = 'MANAGER'
===========================================================
i`4^-_\0union all --- 两个集合并集，不去重复行，不排序51Testing软件测试网cT7? V3yJN
-------------------------
"j|])a t7a,u t0Select empno, ename, mgr From emp
Where deptno = 3051Testing软件测试网8R8hL9P u|
Union All
jE4NVDn:z0Select empno, ename, mgr From emp
D Y!]$JV0Where job = 'MANAGER'

bR0l1};OTt bE0===========================================================51Testing软件测试网#n7iz0on }0j
Intersect --- 只会显示同时存在两个集合中的数据
H*w1{C6y@ W!{c0-----------------------------------51Testing软件测试网6sbBL+M
Select empno, ename, mgr From emp51Testing软件测试网2o2aU6mL;JF}
Where deptno = 30
$e.s8A4a#v&W`I k0Intersect
$O^.O x Rd.q0Select empno, ename, mgr From emp
X5Z5q\3{lc0Where job = 'MANAGER'
*t gG X$hIWK CJB0
)|N7v0BC8R9d^0===========================================================
yf'A[)G+m&?A0minus --- 在一个集合存在，在第二个集合不存在的数据，按第一个排序51Testing软件测试网]&CnN k!I
-----------------------------------51Testing软件测试网*ty Sn8q$A2E
Select empno, ename, mgr From emp
"M"@K4[3f0Where deptno = 30
Minus
Select empno, ename, mgr From emp
Where job = 'MANAGER'
3RLUbR8||P0
&e}6XH.g&F0===========================================================
_i;w'V9N0合并查询中，只能有一个 order by 子句。在这个子句中使用列名或51Testing软件测试网&OuS!]GX
第一个查询的别名。51Testing软件测试网4V5@,Q-y0h
--------------------------------------
3g&km_G$C6L0Select empno, ename 雇员, mgr From emp
"x`&jJ bL0MyP0Where deptno = 30
$P@L%u~0q0Minus
G;j;z$ogA)?` d0Select empno, ename 雇员, mgr From emp51Testing软件测试网8u$Y5Q(Ho eE([
Where job = 'MANAGER' 51Testing软件测试网#xI)|pt`?
Order By 雇员
T&B/H'aB!k0

#_dU1h/_&X-Utf0单行子查询
------------------------------
H-C:j9V#?NBE0Select ename, deptno, sal From emp51Testing软件测试网7Q4ET#F;P
Where sal = (Select Max(sal) From emp);
Y,g_NT]'b*B0
============================================================
多行子查询 --- where子句中使用多行子查询，必须使用多行运算符，51Testing软件测试网4x(T.S9Vx-h
（in,notin,exists,not exists,all,any）
---------------------------------------------------
-K6p:u9D)i(qF`0Select ename, deptno, sal, job From emp51Testing软件测试网:YAfZA
Where job In ( Select Distinct job From emp Where deptno = 20 )
/m`lh`:W@;o^051Testing软件测试网&X!T|y9_{3z
------------------------------------------------------------
Select e.ename, e.job, e.sal
F1~?a;IN%^oR0From emp e
u;z]3`oy.H}l.@0Where sal > All ( Select sal From emp Where emp.deptno = 20)
bN:~O#eSg-U9Z:Z0
oes7A v}3{G5i0------------------------------------------------------------51Testing软件测试网:||]gf)E
Select e.ename, e.job, e.sal51Testing软件测试网`d%E-Ktc6jT
From emp e
Where sal > Any ( Select sal From emp Where emp.deptno = 20)
H#ZvL C6Z)iC0
v8A[f g"mw0============================================================
相关子查询
d"k!Dy ^'y0------------------------------
2|[E~SG7L*O0Select deptno,
K@mt&ZWA:sj0( Select Max(sal) From emp b Where b.deptno = a.deptno ) maxsal
%{`i0`)KOD9srU0From emp a51Testing软件测试网_3|qmD Q:x
Order By deptno51Testing软件测试网ks T}l&[T
------------------------------
Xh.A| E(o6W6YG_V0Select ename, deptno, sal, job From emp51Testing软件测试网(P9EmI A7wa
Where Exists
(
Select 'x' From dept
s)}7Qs qdb0 Where dept.deptno = emp.deptno And dept.loc = 'NEW YORK'
_'U4I2dBT\/K0)

============================================================
B6JP"X%UB0标量子查询 --- 显示每个部门的最高工资员工信息51Testing软件测试网'qdA!t2r']1O
-----------------------
gI6KFYu;\u0select distinct deptno,
z,h^;{+{X0(select max(sal) from emp b where b.deptno = a.deptno) maxsal51Testing软件测试网(B(L(pZ+`:JT4X
from emp a51Testing软件测试网^({#~XN
order by deptno;
51Testing软件测试网!C)hM;JRDN
============================================================
)D-{3h0I T1p"}#qnX0多列子查询 --- 显示与smith部门和岗位完全相同的所有雇员信息。
xwe6vmyP0-----------------------
Select ename, deptno, sal, job From emp
Where (deptno, job) =
-L/@tJC rJ*K{2k0(Select deptno, job From emp Where ename = 'SMITH')
RiA;Gc1Gu}0-----------------------51Testing软件测试网xGB RE5e
显示岗位或者管理员匹配于部门编号为20的所有雇员信息.
-----------------------
u(E&y3^.B0Select ename, deptno, sal, job, mgr From emp
0d-Y0k{)Aw3o4g0Where job In ( Select job From emp Where deptno = 20 )
]:T estxl8}0Or mgr In ( Select mgr From emp Where deptno = 20 )
Order By deptno