sql复杂查询语句的使用

&as&V;f/@hy]*f1R0############################复杂查询语句的使用#####################################51Testing软件测试网&O1I K^)V#{:h
1.查询语句的使用
W]Z-}m,E0使用 select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据.
4jw$HFM}/Z0 51Testing软件测试网^4y0`~Nwe+B
1.1相关子查询
0He*s3ja-S-}G'[ `0可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询称为子查询
YV h3t;c0  .where中可以包含一个select语句的子查询51Testing软件测试网 M Iv2J;]P7y
  .where中可以包含in,exists语句51Testing软件测试网 muo(NBb0sK/L!y B/B7Q
  .最多可以嵌套16层51Testing软件测试网E5T]&N*H_!\ L
  .层次过多会影响性能51Testing软件测试网2RD,Af
w @
R
  [例]简单子查询实例51Testing软件测试网,J&`F}q6V6G
  查询是否有的专家既以研究所的名义来申请基金项目,又以大学系为单位申请项目
"N!H0]3f@k8~0  (按规定只能以一个单位来申请)
CU6tf[,M$Y0  SQL> create table univ_subject
r3tb'B/V*m+z0  2    (
-xvV9Ku0  3       name                 varchar2(12) not null,51Testing软件测试网8@%H%q7s fG;ic
  4       per_id                number     not null,51Testing软件测试网j$w2{P,ft
  5      dept_name       varchar2(20)            
L` bD'RzY'z[0  6    );
{EIr-j0  SQL> insert into univ_subject  values('gaoqianjing',1001,'信息工程系');
U%v#d+t%}?Y0  SQL> insert into univ_subject  values('wangbing',1002,'物理系');51Testing软件测试网f/v)n4rY1|LABP
  SQL> insert into univ_subject  values('liming',1003,'化学系');51Testing软件测试网6Xt#c;Q0~Q co
  ===============
-vQCV*P"H0   SQL> create table  colle_subject
f^Yf w7Y j$\
E0  2     (51Testing软件测试网3d#aWhv#mS Rx
  3              colle_name    varchar2(20),51Testing软件测试网#BiU+Q:SlA:n
  4              per_id              number51Testing软件测试网_Y$_+w;t
  5     );51Testing软件测试网HY.VM?\ p%F-V
  SQL> insert into colle_subject values('电子研究所',1001);
M @0^2yhzZ0  SQL>  insert into colle_subject values('物理研究所',1005);51Testing软件测试网]R2ebu ]/Ns
  ================
:|^DUK%l0  SQL> select name,per_id,dept_name from univ_subject where per_id in
H#gN`5Lz
{ Y0  2    (select per_id from colle_subject);

%?$L+a(Q,l0  NAME            PER_ID   DEPT_NAME51Testing软件测试网&a@Z4?&G7lpJ7X
  ------------          ---------     --------------------51Testing软件测试网8W;Sq0F#@M
  gaoqianjing  1001      信息工程系51Testing软件测试网nUjv1`4i

1.2外连接51Testing软件测试网2A/IL*{/^7P
 [例]外连接实例
D|-\X:ZOP/D$f1{0 招生中所有学生的信息放在students表中,而部分有特长的学生在另一个表中stuent_skill中同样有该学生51Testing软件测试网y2Xg7`(q
 的信息。现在要全部列出所有学生,如果某个学生在表student_skill中就有其特长信息,并显示特长信息,如果51Testing软件测试网 ]*PE(Y XJ8o
 某个学生没有特长就显示特长问空.
1NB-A/H4oF'tK0 SQL>  create table students51Testing软件测试网Gw&AcP0E4[AV"n5m
  2    (
$l|w%pQ!J6am:ar0  3       st_id    varchar2(20),
*T%hdv'?4^7I(i)O`0  4       name  varchar2(10),
&Gm7T1lzK)dU2y0  5       age      number(2),51Testing软件测试网%X3i [6},L)tO
  6       tol_score   number(3)
*O0@9HRt!]0  7    ) ;51Testing软件测试网U sN#W8G7}&J9zi$i
SQL>   insert into students values('973231','wangbindu',22,501);51Testing软件测试网U5R;vJ BQ2k&Z
SQL>   insert into students values('973232','zhuzhijing',21,538);51Testing软件测试网IN6y3n6^
SQL>  insert into students values('973233','gaojing',21,576);51Testing软件测试网3fW[0BG:J
===================

7t)H+j{XOp G&i0SQL>  create table student_skill51Testing软件测试网Zo!o%I+R ~
  2   (
1~+m-f+ehCVI0  3      st_id  varchar2(20),
3c5A;Y1E&|LnM0  4      skill    varchar2(20)
so.gP1J0  5  );
f3q{(ma)Y4W1eS0SQL>  insert into student_skill values('973231','篮球');
[Z"~%U6@4S_.bI9kT;V/P0SQL>  insert into student_skill(st_id) values('973232');51Testing软件测试网 gN-y0p~h%g
SQL>  insert into student_skill values('973233','足球');
.n[vT(F(Hqo1O0===================

h8aP9}6EE0a0~0SQL>   select a.* , b.skill from students a,student_skill b where a.st_id=b.st_id(+)
w9QF4y^ Wr3Y0order by a.st_id;

ST_ID                NAME             AGE TOL_SCORE SKILL
q{9}4x
Rp"Cs(B;Q5L0-------------------- ---------- --------- --------- ------------------  --51Testing软件测试网l*Ce)K*m
973231               wangbindu         22       501        篮球
1A2c#M!jPk2TP
v0973232               zhuzhijing           21       53851Testing软件测试网7?(} }%Md#u&j
973233               gaojing                21       576        足球

1.3自我连接
7i(x |#J5P:I0自我连接是在同一个表或视图内进行条件连接.
Ei'|xv[^)Z0[例]自我连接实例
"eExf4t0查询每个雇员的名字和该雇员的经理的名字:51Testing软件测试网/J#r9L7cT z"K8g7j9x
SQL> select e1.ename||'   work for   '||e2.ename "Employees and their Managers"
8jL&KX4|
I&_0  2  from  scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;51Testing软件测试网q @
E`P*i

lRE"q3q
L:kZSg0Employees and their Managers
&`Z6h)CL$R0-------------------------------------------------51Testing软件测试网3|_-HZ!X"s
SMITH      work for   FORD51Testing软件测试网zE7VGH.tp
ALLEN     work for   BLAKE
`5k g4z/Zk0WARD      work for   BLAKE51Testing软件测试网'|F h;rW8~ R
JONES     work for   KING51Testing软件测试网(T+N1^-}(]Zm p-F!k.A
MARTIN   work for   BLAKE
(\ vl$Kle0BLAKE     work for   KING
#h#L3VrKd}8E
[fG0CLARK     work for   KING51Testing软件测试网W:i(j8tL9Wb{9B
SCOTT      work for   JONES51Testing软件测试网(v `pEu?sQ9}
TURNER  work for   BLAKE
i
F8nn"w9F0ADAMS     work for   SCOTT
Gk.D*NW.as0JAMES      work for   BLAKE
8n$E.]g3G8Qu0FORD        work for   JONES51Testing软件测试网]t
]U__/y4U
MILLER     work for   CLARK51Testing软件测试网h Lw7A5c#o y?9|vJ

1.4UNION , INTERSECT及 MINUS51Testing软件测试网o;ol y }R#F&X/J*m
UNION:            可以将两个以上的表的相类似的查询结果放在一起 (union all则表示返回所有的行)
@@a%R kFx&H0具体语法:51Testing软件测试网#]$xthxl,v jh;C \y
select ...
(@bg'w1OtUq0union[all]51Testing软件测试网|qa lm-L%M#Ffi
select...51Testing软件测试网fc7l+iu"q
==========

2Z'p{kT2}2W0INTERSECT:  返回两个表中相同的信息
.]#poWaWU0具体语法:51Testing软件测试网a \ L bO$X/L8w
select ...
6An%AZG
j R5^-q
Q0intersect
2h7_v8T6L2w0select...
f:sO`1rs|4DJ0==========51Testing软件测试网#N&}2[5e [ VU-{

MINUS          :  返回一个表中出现的信息
,MR,u:x}y^:l8Xk0具体语法:
1\ J"O3o4{!`0Z?Uj#T1_0select ...51Testing软件测试网*tb)b(l3hc
minus
+N(E
`)S(O7Z0o7S0select...51Testing软件测试网M q|.a+YDeLf
[例1]UNION操作实例
*~U['Ja8?([0SQL> select  st_id  from students51Testing软件测试网3d+h!^MT7Ms6v;ZE]
  2  union
c?lf1E/s5y"r:{T[0  3  select  st_id  from student_skill;

9hQ$D t PGFG%L0ST_ID51Testing软件测试网"f%f(b)F;l B"`)g
--------------------51Testing软件测试网!S%S`CZ+JZ
973231
R1{v"pV9o0973232
r;x"K$n$dq/i-Y097323351Testing软件测试网9wMyZ0{,jZ"\rz&w

3]QQy5Fg)N0[例2]INTERSECT操作实例
,}A+B fP Pr,f0列出有特长的学生的学号51Testing软件测试网,_5j*G.ku s+x+Q1EO
SQL> select st_id from students51Testing软件测试网&A&S,} x4nQ7@c
  2  intersect51Testing软件测试网a _"m(Mu!S-PO
  3  select st_id from student_skill;51Testing软件测试网6Ugjk[m
ST_ID51Testing软件测试网rzG4~ MU_IAG
--------------------51Testing软件测试网*[b"w3t~*UICo
97323151Testing软件测试网%v9h&c;r:QD(Ku7r
973233

lWO0gTS0[例3]MINUS操作实例51Testing软件测试网&x$vSU}7wUSWd `
列出没有特长学生的学号51Testing软件测试网k(}5u'O"lC
select st_id from students51Testing软件测试网fH%V1E,X!Or$V#bP
minus
I,d?3s gl3Qv:dT0select st_id from student_skill;
5Fu'I{7u"s0ST_ID51Testing软件测试网$x ~"LkwC4K
--------------------51Testing软件测试网/V sh.~ct&|
97323251Testing软件测试网
gSh/z1n

H;q*rL2Q|Ct02.创建复杂的视图51Testing软件测试网B:G ZI7M@Q6p
许多应用系统有统计等功能,建议最好把这些复杂语句写成视图.下面是几个常用的视图.
/l%jHJ(q qe"kP02.1分组视图
}H/sE.jH#BPb0[例1]简单的分组视图51Testing软件测试网
`mv2T3W
SQL> create or replace view dept_tot as51Testing软件测试网a6tE%Hk0YIV5?
  2  select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b
+Vuj~tU0  3  where a.deptno=b.deptno group by a.dname;

查看已建立。51Testing软件测试网bz*R!Q3P:hD
SQL> select * from dept_tot;51Testing软件测试网J!L7|8SaX

Z6b*h:~ ]0DEPT                           TOTAL_SAL51Testing软件测试网o~fuzy
--------------                     ---------
D.}1b u#[j.F0ACCOUNTING          875051Testing软件测试网6I@mi}
RESEARCH              10875
\b;^1{:{1D0SALES                        9400

Q7Xhv;Q7l,y0[例2]带复杂函数视图51Testing软件测试网LAW)I:^XMe*PX
SQL> create or replace view itemtot as
DK/Z2WJ&j0  2  select persion,sum(amount) itemtot from ledger51Testing软件测试网Ar(s s'{Wq*Q
  3  where actiondate between51Testing软件测试网M2lCa1qfaGN
  4  to_date('01-MAR-1901','dd-mon-yyyy') and
!O3a1g/sZm*dO0  5  to_date('31-MAR-1901','dd-mon-yyyy')51Testing软件测试网y6B+R%IF
  6  and action in('bought','raid') group by persion;51Testing软件测试网 mzJsf}`&l'}

2.2合计视图
'] X"~na9`3L2Q0[例]合计函数视图实例51Testing软件测试网:wV5kxq'Z
SQL> create or replace view emp_no1  as51Testing软件测试网T7p/vX!J ?Z%~k$u
  2  select deptno,sum(sal) 工资和,sum(comm) 总和51Testing软件测试网
R:W%O`P,n
  3  from scott.emp group by deptno;
?bSQ1di2j+z0SQL> select * from emp_no1;51Testing软件测试网 h[#h%\X9K*c;Iq
DEPTNO    工资和      总和51Testing软件测试网-OwsUTg3`
--------- --------- ---------51Testing软件测试网k"E;lp,pb"}g \
       10      875051Testing软件测试网0a6Yq j!x/Lm(}5_ {J
       20     10875
b&x|r;qDj|0       30      9400      2200

xaI H/cl4l{02.3组合视图
F:K*pbI6}h:V0[例]带组合函数的视图51Testing软件测试网&l7t)fZ6Q
j
SQL> create or replace view byitem as
8ALk^+F9j"jXVe0  2  select l.persion persion.item, amount, 100*amount/item bypersion,100*amount/total bytotal51Testing软件测试网lIJ1M q4L
  3  from ledgent l,itemtotal i,total where l.persion=i.persion where l.persion=i.persion
'Uq+xOQ#ti#M0  4  and actiondate between51Testing软件测试网@wqWuz
  5  to_date('01-MAR-1901','dd-mon-yyyy') and51Testing软件测试网#izb7?wJ{,q%O5nZ
  6  to_date('31-MAR-1901','dd-mon-yyyy')51Testing软件测试网&P-IU7T-p&ac
  7   and action in('bought','raid') ;

3.家族树
;p!^(W;y"GH"Z3_0语法:51Testing软件测试网 e!v4EfF3hs"@
select column from table_name start with column=value
.F[wP"v,k SGz8g(W0connect by prior 父主键=子主键51Testing软件测试网S5\3Y1A@

3.1排除单一性和分枝
6z$X;^JS,nGh |0以ORACLE中的EMP表为例51Testing软件测试网(SeS
Bn"lX` s
[例]从顶到底列出各雇员的信息51Testing软件测试网|y(f@j1D
N/x
SQL> select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null
G|)rJ?V(J0  2  connect by prior empno=mgr;

NAME                                 EMPNO       MGR
vZbo5d
JP`0---------                                  ---------           ---------51Testing软件测试网T;y K7Pi.@nw
KING                                   7839           51Testing软件测试网%\ VH[!^r"a
    JONES                           7566            7839
*bZq"@pD0          SCOTT                     7788            7566
ci1wM3am9?0                ADAMS              7876            7788

'?!|D e:J03.2遍历至根51Testing软件测试网EmpY3gW
[例1]现在要从某个雇员开始向他的上级列出该雇员的层次结构
fbAsU},?w N0SQL> col ename for a30;
w*K"US-E.k/nd+e0SQL> select lpad(' ',4*(level-1))||ename ename,mgr,empno from scott.emp51Testing软件测试网$s&j3L-@t
  2  start with mgr=7788 connect by prior mgr=empno;51Testing软件测试网 [:j4TC|N:iipD
ENAME                                MGR     EMPNO
"vX$~W}_q~0------------------------------         ---------    ---------
oQ
wBJjmh0ADAMS                               7788      787651Testing软件测试网Mtn%^C6u3h
    SCOTT                           7566       7788
#|a"y&@*}W,C,K0        JONES                       7839       7566
$}`E!W wA]0            KING                                        783951Testing软件测试网4A
pyVA4jM,S

W$WY#z2FH3?6h [^0[例2]列出所有雇员的层次结构51Testing软件测试网^)D-X6T&D/~d&{9v
SQL> select lpad(' ',4*(level-1))||ename ename,empno,mgr from scott.emp
RPc6r#o-r\4x0  2  start with mgr is not null51Testing软件测试网VC
D m%t%oL3`f/U'|,r
  3  connect by empno=prior mgr;

~-rME2{!cu:b5dZ\0ENAME                              EMPNO       MGR51Testing软件测试网#^D {0|WJ9~%v
------------------------------       ---------           ---------51Testing软件测试网;QK-G7@Q@
SMITH                               7369          7902
qf;QC}0    FORD                            7902         756651Testing软件测试网B5W;s/S%R
        JONES                       7566        7839
EZ3\4mC@)o2P3{0            KING                    7839
6oF%i0B_'nZd
y)\0ALLEN                               7499        7698
[N[3zFR0    BLAKE                           7698        783951Testing软件测试网kS.@/~9Idv3H
        KING                        7839
Ytdw'`_2T0WARD                                7521        7698
&j-Xd_P.W0    BLAKE                           7698        783951Testing软件测试网p:XOJ0\O
        KING                        7839
v,Fq*XGn.S'J0JONES                               7566        7839
6Y_4AQ[ O @;y1X0    KING                            7839

h8VFVx~D0MARTIN                              7654        769851Testing软件测试网/D5ThD.Z(` F:_X]
    BLAKE                           7698         7839
b,Nr \a,r0        KING                        783951Testing软件测试网$tH8\Q2Q x6w.y4OW
BLAKE                               7698        783951Testing软件测试网3NZW-lV@$a$gg^"\
    KING                            783951Testing软件测试网xe
H&n"sV&f
w
CLARK                               7782         7839

`5J4DJT0    KING                            783951Testing软件测试网@#[mDQ ]+[T%C
SCOTT                               7788         756651Testing软件测试网2L
Q1WQ.b5vf:t&n|
    JONES                           7566        783951Testing软件测试网i#_vw Y8{

 51Testing软件测试网 t(?e
e/^zs|

/Hh8K
]%`I.?:] i0      
%}y4O}Q0        51Testing软件测试网`L&K*s7Ei)u

-M TiS V/Ou0           51Testing软件测试网)Rtm8z2?s0v/O
          

z?wS2c0j0g+@0 51Testing软件测试网+{;v,bD*i$R*R