sql复杂查询语句的使用

############################复杂查询语句的使用#####################################
n,B9d| Vd@$]r0 1.查询语句的使用
_%e+D-Nk J$W4L0 使用 select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据.
0Cqi"W\�~�^ ?6rul,|0  51Testing软件测试网~:X T.j+^o_
1.1相关子查询
6wO[E~O^0 可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询称为子查询
%s~2`!U)E2D0   .where中可以包含一个select语句的子查询
2Xn#s!L0w0   .where中可以包含in,exists语句
:[3\#a%W#hvBt9{_7l0   .最多可以嵌套16层
Gad9s5M7e/v v:{0   .层次过多会影响性能51Testing软件测试网 fmZ\c-K ?t
  [例]简单子查询实例51Testing软件测试网P%g4AF,?Q9P
  查询是否有的专家既以研究所的名义来申请基金项目,又以大学系为单位申请项目
#k'|y]%PP0   (按规定只能以一个单位来申请)
f1gY(b7Gz*lY}R0   SQL> create table univ_subject51Testing软件测试网)~O,n5r N?BX
  2    (51Testing软件测试网eA/K2D)fo%aR:J+F
  3       name                 varchar2(12) not null,
*] k-Xn@h0   4       per_id                number     not null,51Testing软件测试网+E!u5{Al2K
  5      dept_name       varchar2(20)            51Testing软件测试网U4Wf:j'HWQ)?
  6    );
[3Uf�\mgN0   SQL> insert into univ_subject  values('gaoqianjing',1001,'信息工程系');
2}Y-V)@5T6d'u0   SQL> insert into univ_subject  values('wangbing',1002,'物理系');
8Y_c.W�?EW`0   SQL> insert into univ_subject  values('liming',1003,'化学系');51Testing软件测试网4kXHj+w3I { s
  ===============51Testing软件测试网a*m'jv m9k
   SQL> create table  colle_subject
v'AaT(y2`3x;W0   2     (
NpG#LR!D:v0   3              colle_name    varchar2(20),
(D WQ2T*^A7_Wq0   4              per_id              number51Testing软件测试网?O8g#?L u3CD;km+u
  5     );51Testing软件测试网E Hv[#v
  SQL> insert into colle_subject values('电子研究所',1001);
o-e4M+k aL Y*Bml0   SQL>  insert into colle_subject values('物理研究所',1005);51Testing软件测试网7yzz;U~ d-L
  ================
yQ,JTv|)l0   SQL> select name,per_id,dept_name from univ_subject where per_id in51Testing软件测试网|+X8zQ"N6A*U
  2    (select per_id from colle_subject); 51Testing软件测试网;W'z$A2mj4V

51Testing软件测试网0Z4y z:sQ/x^@:X?

  NAME            PER_ID   DEPT_NAME
8a @/lj({ E0   ------------          ---------     --------------------51Testing软件测试网,\Z$gRR5A_
  gaoqianjing  1001      信息工程系 51Testing软件测试网s gk2}K1q

%M#O]H)p;~ y0 1.2外连接51Testing软件测试网)Si)c.~mId
 [例]外连接实例51Testing软件测试网*l,P|va(e
 招生中所有学生的信息放在students表中,而部分有特长的学生在另一个表中stuent_skill中同样有该学生51Testing软件测试网'` A,o:h K
 的信息。现在要全部列出所有学生,如果某个学生在表student_skill中就有其特长信息,并显示特长信息,如果51Testing软件测试网#t@.A&|&mw
 某个学生没有特长就显示特长问空.51Testing软件测试网-dO6K~](x
 SQL>  create table students
/iPix Z+dw$\0   2    (
"akM.CV3H+M0   3       st_id    varchar2(20),51Testing软件测试网z8`7K:Vez`
  4       name  varchar2(10),51Testing软件测试网-v$qr?!wX2ni,o
  5       age      number(2),51Testing软件测试网!Q%j\BGn
  6       tol_score   number(3)
|h]Os9n�f0   7    ) ;
4u#w9OC*GI C:T;\eo4?0 SQL>   insert into students values('973231','wangbindu',22,501);
%lI%|U;D{oUI2E5K0 SQL>   insert into students values('973232','zhuzhijing',21,538);51Testing软件测试网;lN;h!J'jq1F(j-s
SQL>  insert into students values('973233','gaojing',21,576);51Testing软件测试网A9[8ZJ;E@
=================== 51Testing软件测试网4r:}"^3OqF!V@

1WQ h)X#Wx0 SQL>  create table student_skill
j\]p)QHL0   2   (
A){O5gC0   3      st_id  varchar2(20),51Testing软件测试网bzp_lP%z
  4      skill    varchar2(20)
jf7c] V6FCEwxt0   5  );51Testing软件测试网$b!VLY4x{
SQL>  insert into student_skill values('973231','篮球');
4@PP)eg6K6t)J3Xo8|0 SQL>  insert into student_skill(st_id) values('973232');
Gr+bN{|ZATV0 SQL>  insert into student_skill values('973233','足球');51Testing软件测试网9Hz;H)h#p${
=================== 51Testing软件测试网#E5O(KLq6U{

N&XD*q2nr2hu�o&h1O'e0 SQL>   select a.* , b.skill from students a,student_skill b where a.st_id=b.st_id(+)
3\'~U/r.@3GQ'@0 order by a.st_id;

8Z,K]`qmH-U0

;z1l9j%LY,d;cd&}0 ST_ID                NAME             AGE TOL_SCORE SKILL
AB[*z-H ^+K2C M r0 -------------------- ---------- --------- --------- ------------------  --
Y*iS0I5f"Z0 973231               wangbindu         22       501        篮球
'v,pd8P-O$O gG/`0 973232               zhuzhijing           21       538
_'[B1m#}9QI0 973233               gaojing                21       576        足球

j8X�mF7maQ2A}T0

IGb.l�xP U0 1.3自我连接51Testing软件测试网4Y2a%w�Af&}$sX5v
自我连接是在同一个表或视图内进行条件连接.
&h-LMPW8kUzo+O}D0 [例]自我连接实例51Testing软件测试网5SV ^i&iz3a
查询每个雇员的名字和该雇员的经理的名字:51Testing软件测试网u!I q8Ql
SQL> select e1.ename||'   work for   '||e2.ename "Employees and their Managers"
9|6a(Z#j `A_/tR0   2  from  scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;

&J1{3G4h8\G7H0 51Testing软件测试网m[*w'_EhY

Employees and their Managers51Testing软件测试网$G fz)r|:Cm"k#V:KE
-------------------------------------------------51Testing软件测试网%P-` o4Zg{sC
SMITH      work for   FORD51Testing软件测试网,ja2|[ J s
ALLEN     work for   BLAKE
__d|+?z0 WARD      work for   BLAKE51Testing软件测试网@'YT;@fy"g ~
JONES     work for   KING51Testing软件测试网^/YgUQ(rq?
MARTIN   work for   BLAKE
\;V4Wbqt i CS0 BLAKE     work for   KING
"nt&f,a%q0C%|0 CLARK     work for   KING51Testing软件测试网 guz{*Lxf[h t
SCOTT      work for   JONES51Testing软件测试网xb;s6MZR:gf
TURNER  work for   BLAKE
tT!C;aS0 ADAMS     work for   SCOTT51Testing软件测试网"K7R3`e]4vV2H"[
JAMES      work for   BLAKE51Testing软件测试网2v7o U,b7u;_8v$v
FORD        work for   JONES51Testing软件测试网6i7[h�f)n)p Tmi
MILLER     work for   CLARK

x2p|cZoC'X5EV%M q0 51Testing软件测试网4J8ehh.Jl

1.4UNION , INTERSECT及 MINUS
'{x(H[(ve0 UNION:            可以将两个以上的表的相类似的查询结果放在一起 (union all则表示返回所有的行)51Testing软件测试网YI|2r)F)NjF+uu3k
具体语法:51Testing软件测试网ZQ|8F ue9y:u
select ...
lY3bl:j-`0 union[all]51Testing软件测试网0E'T(WktP&RcO
select...51Testing软件测试网:yxK e4I'c.C
========== 51Testing软件测试网(M x?&u};^5}|V Jn

x^ GIg{0 INTERSECT:  返回两个表中相同的信息
`,OP~�M,N,D$qm0 具体语法:51Testing软件测试网HQ[|)Us9y3N\
select ...51Testing软件测试网A}~OA4w'B
intersect51Testing软件测试网lyS&@0c1H-U:S
select...
rBd dK6X2`7@ubE0 ========== 51Testing软件测试网z;vhx|3x;~1P

51Testing软件测试网UY�}3@ \9bYHfy*FG

MINUS          :  返回一个表中出现的信息51Testing软件测试网3v Ws0pQ
具体语法:51Testing软件测试网#a:Y s3r)V#|!z
select ...
0`u9{h�y'x7i%z6M0 minus51Testing软件测试网$bPpY*n.HwE
select...
,t-\7p&gP]&?D}0 [例1]UNION操作实例
iyT^'m r0 SQL> select  st_id  from students
1{$^O"W L)p&|q0   2  union51Testing软件测试网C(PT�C(TXc:t6D x
  3  select  st_id  from student_skill;

1u/Bq3o$TcHmq0

,E/by xE6J0 ST_ID51Testing软件测试网m0X8VI3rvF[:B
--------------------51Testing软件测试网6T+}V9HhJ*r3?-X ]
97323151Testing软件测试网(xWH2ACG7GL4u.b
973232
.cm&Y+YW9n C0 973233

G0YD�w$jp2v_1A0 51Testing软件测试网iQ/vy,KU4d

[例2]INTERSECT操作实例
.L0Z_ES6N0 列出有特长的学生的学号51Testing软件测试网tdIG3tZ OA7AZ)q
SQL> select st_id from students51Testing软件测试网7Op_3[m!cFw
  2  intersect
RG/Hd7^n-b.Y0   3  select st_id from student_skill;51Testing软件测试网hO}G?;{KvH
ST_ID
+h_C5[(s n;p(J![0 --------------------
g#uY4K\\0 97323151Testing软件测试网8vH V.@.pu,w~
973233

{B"~?@1L a0

7}L7\ mrC1r#o0 [例3]MINUS操作实例
^:@9zCI~u(]0 列出没有特长学生的学号51Testing软件测试网I5t9b^;^Y'B6[+t
select st_id from students51Testing软件测试网%N xXt!A6gl
minus
oT bJhW,zVW0 select st_id from student_skill;51Testing软件测试网\�UU:DL(M:e�w&b9u
ST_ID
4W$BDZz3c7|y\0 --------------------
nlJ%i{Q0 973232 51Testing软件测试网ach*|}7KUi

51Testing软件测试网.x["[*b/Yr4q

T1J n+gZ0M]R;~1o0 2.创建复杂的视图51Testing软件测试网5g}6z.c @I%|P&e D
许多应用系统有统计等功能,建议最好把这些复杂语句写成视图.下面是几个常用的视图.
M8f&z/~*Mso�y0 2.1分组视图
1B&n5jeXv.q0 [例1]简单的分组视图51Testing软件测试网-PQ%|0dH^pz
SQL> create or replace view dept_tot as51Testing软件测试网4{bU,e6c
  2  select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b
1H4I&noC0   3  where a.deptno=b.deptno group by a.dname; 51Testing软件测试网p,TH9P)aMi+f

51Testing软件测试网/u9h_:u)^HQ][

查看已建立。
&]8Pqr[2\W/[0 SQL> select * from dept_tot;

S?|3uf5e C0 51Testing软件测试网0o-zam#R.G

DEPT                           TOTAL_SAL51Testing软件测试网i}'Xq.GEJs
--------------                     ---------51Testing软件测试网sus*F\ c,It[
ACCOUNTING          8750
vY0ZX&_t']3h0 RESEARCH              1087551Testing软件测试网rMT7g:iXy
SALES                        9400 51Testing软件测试网 m%s|}h"W

51Testing软件测试网;ocU\7l6Al

[例2]带复杂函数视图
z9^ T7o5d/aL0 SQL> create or replace view itemtot as
wKvn,z_A0   2  select persion,sum(amount) itemtot from ledger51Testing软件测试网0ZO0r8rC`^
  3  where actiondate between51Testing软件测试网8H"`kL(Tl5@6pt�}I
  4  to_date('01-MAR-1901','dd-mon-yyyy') and
7Qf;U B�M0   5  to_date('31-MAR-1901','dd-mon-yyyy')51Testing软件测试网W1B&I#F*m!m7P;Nt
  6  and action in('bought','raid') group by persion;

1Pky'u \?I�Vf0 51Testing软件测试网 ]+O2t7a/m&?+}R"Kd

2.2合计视图
5\�k_{] n"jc"fb0 [例]合计函数视图实例51Testing软件测试网[%f\(kdiz
SQL> create or replace view emp_no1  as
og8xG$m U'B x4E W ?0   2  select deptno,sum(sal) 工资和,sum(comm) 总和
[ B(Uv|xM0   3  from scott.emp group by deptno;
xZ]z Z0 SQL> select * from emp_no1;
S*M5\8S*W)S,z U0 DEPTNO    工资和      总和51Testing软件测试网"Zh)[\-b};h+gF Zp
--------- --------- ---------
&p p[faF2l0        10      8750
f3}tkD.y;X0Pn3g0        20     1087551Testing软件测试网?4Kk T*Gg/E2n(Pw
       30      9400      2200 51Testing软件测试网,\G&XZ.NV/Ic

51Testing软件测试网C[u4J2D/lF

2.3组合视图51Testing软件测试网.Pr-xJt2H Z
[例]带组合函数的视图51Testing软件测试网6w;Hx\r{kQ
SQL> create or replace view byitem as
d vfU$THn?"[0   2  select l.persion persion.item, amount, 100*amount/item bypersion,100*amount/total bytotal51Testing软件测试网klXxN-i8fP
  3  from ledgent l,itemtotal i,total where l.persion=i.persion where l.persion=i.persion51Testing软件测试网~.@,]-zB�O,A
  4  and actiondate between51Testing软件测试网"e e8k.\f:{6O
  5  to_date('01-MAR-1901','dd-mon-yyyy') and51Testing软件测试网-oC%GY#O1t0`(mr;QV
  6  to_date('31-MAR-1901','dd-mon-yyyy')
.lIF CMD0   7   and action in('bought','raid') ;

J MV"S*T$N1?Mc@3`-j|0

:u,y?sU)qcR8w0 3.家族树51Testing软件测试网YA0k#w K,Y'Tu
语法:
3Y5z Ge/d5{g0 select column from table_name start with column=value51Testing软件测试网b)i2[ h|#m8z
connect by prior 父主键=子主键 51Testing软件测试网%FY$C2j]T.\zY

$` fJ1PU)_;z0 3.1排除单一性和分枝51Testing软件测试网%SySW~6E~O
以ORACLE中的EMP表为例51Testing软件测试网 c\oP)c h/cE5w-]
[例]从顶到底列出各雇员的信息
S RzL;Vn;h*[(t0 SQL> select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null
}j|'W/BQ1z5pM0   2  connect by prior empno=mgr; 51Testing软件测试网L:r#lI(v;S

qC"Z'V/q{0~9]J0 NAME                                 EMPNO       MGR51Testing软件测试网^ l;o7x*iYGe
---------                                  ---------           ---------
T6~;B/g8X0K#_0 KING                                   7839           
G ?(BBWDC?0     JONES                           7566            783951Testing软件测试网 bcS}NuO n�e
          SCOTT                     7788            7566
J!Mz(MIuW0                 ADAMS              7876            7788

U6RC Y^&?"~0 51Testing软件测试网+Uu Us(DijG

3.2遍历至根
]N:\IRe6b9cA!P4F0 [例1]现在要从某个雇员开始向他的上级列出该雇员的层次结构
N�r3j#]`(h:i,Xg0 SQL> col ename for a30;51Testing软件测试网O X8YS _.}O
SQL> select lpad(' ',4*(level-1))||ename ename,mgr,empno from scott.emp
:L Ll+N2H }b9y0   2  start with mgr=7788 connect by prior mgr=empno;
Gt6nu8xA4^_0 ENAME                                MGR     EMPNO51Testing软件测试网6[J5j#U ^:Cj.}
------------------------------         ---------    ---------51Testing软件测试网ssA%]#}%X&r
ADAMS                               7788      787651Testing软件测试网(B~5mQ+@]%w
    SCOTT                           7566       7788
(R8C$K;NA:n#n7B0         JONES                       7839       7566
wO+r!E-P.x0             KING                                        7839 51Testing软件测试网 l9CY|6fw4h�l

#_]!zl h3pPD0 [例2]列出所有雇员的层次结构
X#Cnb)n[_YC0 SQL> select lpad(' ',4*(level-1))||ename ename,empno,mgr from scott.emp
_*uosQ9M&C#EXZ0   2  start with mgr is not null
lgVTJ'h-O%Gq0   3  connect by empno=prior mgr; 51Testing软件测试网RibQb,n_,Y-FF"{

51Testing软件测试网#T0rI_5[B%M)M

ENAME                              EMPNO       MGR
0q R-I m;@0|t0 ------------------------------       ---------           ---------
0A4M9qZzm0 SMITH                               7369          790251Testing软件测试网GWPTS/oU
    FORD                            7902         756651Testing软件测试网"R@2`"])O%P)sIa
        JONES                       7566        783951Testing软件测试网�B/q8\8V3Y*Wv
            KING                    783951Testing软件测试网kHIMS
ALLEN                               7499        7698
/S�eO/Lz;Gz1j]0     BLAKE                           7698        783951Testing软件测试网eQ zNLIN
        KING                        783951Testing软件测试网5F?E1[]rR`E
WARD                                7521        769851Testing软件测试网4Q�a)~g[$l
    BLAKE                           7698        783951Testing软件测试网(pn V9e*L#kl
        KING                        7839
2}^-@j ?IWIg:@ r0 JONES                               7566        783951Testing软件测试网V1yun&W:o"i*^#Ta&e
    KING                            783951Testing软件测试网QqZD/`o
MARTIN                              7654        769851Testing软件测试网/o�htEi8} _
    BLAKE                           7698         7839
\~i/qP @YC%V0         KING                        783951Testing软件测试网#OPf[6U
BLAKE                               7698        7839
b2iG:X[C0     KING                            783951Testing软件测试网)P0s VJbd!F�G@
CLARK                               7782         783951Testing软件测试网{1JQT'E(O4Bm
    KING                            7839
:K:n%cw-j2~6yy0 SCOTT                               7788         7566
y}T]I0C0     JONES                           7566        7839