SQL> select * from test111;
ID NAMEITPUB个人空间&U&fpI8\a"c+\/r9N
---------- --------------------ITPUB个人空间 I(E ] G s1x
111 aaa
1b}.\F L `4|X0 222 bbbITPUB个人空间4Drhh6X ` NRf
333 ccc
SQL> select * from test222;
ID NAME
6_ ni j I T0---------- --------------------ITPUB个人空间6p,G
^!}6[(aF_'v
222 bbb
X1L3\ a+{G$Y0 444 ddd
SQL> select * from test333;
ID NAMEITPUB个人空间 }lWNGA
---------- --------------------
)m;va$@ E0 333 cccITPUB个人空间)SD en]!] C
555 eee
SQL> select t1.id,t1.nameITPUB个人空间 |*`4I`&Z+K
2 from test111 t1 left outer join test222 t2ITPUB个人空间 T(v
@mvC
yI
3 on t1.id=t2.id
N*q4X!Q|],S/\0g0 4 left outer join test333 t3
Iq+\cL1k
O^s0 5 on t1.id=t3.id; //t1先跟t2左连接，结果集再跟t3左连接
ID NAME
(`&sR!b p/v u)G s0---------- --------------------ITPUB个人空间 ^\} r
a9Cpi
333 ccc
#@#|$X\lm$b0 222 bbb
;K3E]zG(i0 111 aaa
SQL> select t1.id,t1.name
.D,Z'O
L'y W0 2 from test111 t1,test222 t2,test333 t3
U0A3@ne0 3 where t1.id=t2.id(+)
K7?n8}$? c0 4 and t1.id=t3.id(+); //(+)在右边表示左连接，在左边表示右连接
ID NAMEITPUB个人空间0Yb3MFsF"}M~
---------- --------------------ITPUB个人空间SZF V?
JF5Z
333 cccITPUB个人空间-D8Z%q Y%w z/R5k
222 bbbITPUB个人空间{coR&a ~
111 aaa
SQL> insert into test333 values (222,'bbb');
1 row created.
SQL> commit;
Commit complete.
SQL> select t1.id,t1.nameITPUB个人空间 x V sn1yU4Q.k
2 from test111 t1 inner join test222 t2ITPUB个人空间7[9co^C0i UN9b!T
3 on t1.id=t2.id and t1.name=t2.nameITPUB个人空间{HYt+l}:}
4 inner join test333 t3
/Wk7tU un4w'L D.H0 5 on t1.id=t3.id and t1.name=t3.name; //on 关键字可以跟and实现多字段关联
ID NAMEITPUB个人空间d9j;U e8^ls8S
---------- --------------------ITPUB个人空间X H6n4S.AZ!VZ X H
222 bbb
SQL> select t1.id,t1.name
M Ck8L-`N~(`0 2 from test111 t1,test222 t2,test333 t3ITPUB个人空间&ZW~c_!W\
3 where t1.id=t2.idITPUB个人空间 ])AN3Tv2TD
4 and t1.id=t3.id;
ID NAMEITPUB个人空间~A`P)F6j9mD
---------- --------------------ITPUB个人空间
q0W O v qx6J(@
222 bbb
SQL> delete from test222 where (id,name) in (select id,name from test333);//可以多字段关联删除,但是in后面关联的数据必须来源于查询语句。
1 row deleted.
SQL> select * from test222;
ID NAMEITPUB个人空间2K:@&Iu*F m-w9V5a]
---------- --------------------ITPUB个人空间,udWe Ba [
444 ddd
SQL> update test222 set (id,name)=(select 555,'eee' from dual); //可以多字段更新，但是=号后面的数据必须来源于查询语句。
1 row updated.
SQL> select * from test222;
ID NAME
W
K;@3NK s0---------- --------------------
,P ^
`op0 555 eee
SQL> update test222 set (id,name)=(select 666,'fff' from dual) where id=555;//update后面可以正常使用where子句
1 row updated.
SQL> insert into test222 values (777,'aaa');
1 row created.
SQL> update test222 set id =(select avg(id) from test111 where test111.name=test222.name);//update后面不能直接跟上from子句，所以需要加上括号在内层和外层关联实现直接在update后面跟from的效果。
2 rows updated.
SQL> select * from test222;
ID NAMEITPUB个人空间e
[S s2Jo$k
---------- --------------------
\v&^N @X'L N0 fff
q;B `GH A0 111 aaa
SQL> delete from test333ITPUB个人空间F(YB4@4JJ6G T
2 where rowidITPUB个人空间7a[7l W{
3 in
;C#}f;en0 4 (select rr.rd from(select t3.rowid rd from test111 t1,test333 t3 where t1.id=t3.id) rr);
2 rows deleted. //多字段的关联删除还可以通过查询出rowid来实现。delete也不能直接和其他表进行关联执行delete,例如delete from a from b where a.id=b.id xxx;
UPDATE语句的关联更新：
r y ~ YY&c)[0SQL> select * from a;
ID NAME AGE COURSE ID2
s V0Gw R^^9i%L0---------- -------------------- ---------- ---------- ----------ITPUB个人空间9Yi)o4H e+r
111 jack 21ITPUB个人空间 h,H }0P \5tm6W8R
222 mary 23
'z%q(Y1Z(D;c3d/M-s5f,i.D }0 333 lucy 19
SQL> select * from b;
ID COURSE
~ D3at*S7J#u K0---------- ----------
s Kv D9r3dt0 111 98ITPUB个人空间 ?L \ w2W(F#F!Q8t2p
222 76ITPUB个人空间Ov:qHl }
333 12
SQL> update a set a.course=(select b.course from b where a.id=b.id);
3 rows updated.
SQL> update a set a.course=(select b.course from b where a.id=b.id)ITPUB个人空间 y%s)a QS bj
2 ,a.id2=(select b.id+100 from b where a.id=b.id);
3 rows updated.
SQL> select * from a;
ID NAME AGE COURSE ID2ITPUB个人空间8rMj#I%|'q,k.@
---------- -------------------- ---------- ---------- ----------ITPUB个人空间&| m^)qsfm0I$q
111 jack 21 98 211ITPUB个人空间nv
K \(I.{
222 mary 23 76 322ITPUB个人空间 Mf)CKP| @V6~
333 lucy 19 12 433
SQL> select * from a;
ID NAME AGE COURSE ID2
+o:KS^!a0---------- -------------------- ---------- ---------- ----------ITPUB个人空间%I FS-X0^.Q
J
111 jack 21
:F;X hO;\ l3Up{a1lS0 222 mary 23ITPUB个人空间~OKA3qS)a^+D
333 blue 18
SQL> select * from b;
ID COURSEITPUB个人空间-Rf E&w%n f
---------- ----------
\
G%Q"cP`e_0 111 79
6uBor#@,H |M-cj O0 222 87ITPUB个人空间LUM[M4b*f
333 89
SQL> update a set (a.course,a.id2)=ITPUB个人空间P&b7n
H.J RF~uv
2 (select course ,id+100 from b where b.id=a.id);
3 rows updated.
SQL> select * from a;
ID NAME AGE COURSE ID2
)Jf J-? Q }0Z0---------- -------------------- ---------- ---------- ----------
;m[ O @ K+A!QL+K0 111 jack 21 79 211
P9r1rL8Nt x/m0 222 mary 23 87 322
;J,]9j,cB\0 333 blue 18 89 433
UPDATE关联更新需要注意的：ITPUB个人空间 ~ J!| { p%K]n*z.U_
ITPUB个人空间w5],U1@Z0W%[
SQL> select * from test111;
ID NAME AGE
)J"YM~ G.Xqj0---------- ---------- ----------ITPUB个人空间/t C n#d2U
QZ
111 jack
Bc'['P
^`8vd `-V0 222 maryITPUB个人空间?3I2G;rR-`,y7D5Fr6h
333 tom 99
SQL> select * from test222;
ID AGE
;J*Hw[_.S%C8B0---------- ----------ITPUB个人空间W
Z8?dz)U
111 23ITPUB个人空间Kb8I2]s V
222 34
z K3L+g6y*|'b)q {`0 444 62
SQL> update test111 t1 set t1.age=(select t2.age from test222 t2 where t1.id=t2.id);
3 rows updated.
SQL> select * from test111;
ID NAME AGEITPUB个人空间yZ0Nyxu;~Qh$g
---------- ---------- ----------ITPUB个人空间CZStw(G EcXQ
111 jack 23
g!ZTlP4s-J0 222 mary 34
I'OS'Y6J-L0 333 tom //将没有匹配成功的值全部update为null,这将覆盖原有的值，不符合逻辑，所以需要加上where条件。
2_(Fs$qlJM0ITPUB个人空间"_BrWdv
SQL> update test111 t1 set t1.age=(select t2.age from test222 t2 where t1.id=t2.id) where id in (ITPUB个人空间 F8m!@8lw
2 select t2.id from test222 t2 where t1.id=t2.id);
2 rows updated.
SQL> select * from test111;
ID NAME AGEITPUB个人空间.qd&Z B%c/bG
---------- ---------- ----------
^(a0x$k'zC
p0 111 jack 23
ZP ml6QMZ/f1Q0 222 mary 34ITPUB个人空间f R|5l[4C
333 tom 99ITPUB个人空间 b:CK1Hb ESU
--慢慢补充--
