SQL> select * from test111;

        ID NAME
---------- --------------------
       111 aaa
       222 bbb
       333 ccc

SQL> select * from test222;

        ID NAME
---------- --------------------
       222 bbb
       444 ddd

SQL> select * from test333;

        ID NAME
---------- --------------------
       333 ccc
       555 eee

SQL> select t1.id,t1.name
  2  from test111 t1 left outer join test222 t2
  3  on t1.id=t2.id
  4  left outer join test333 t3
  5  on t1.id=t3.id;                           //t1先跟t2左连接，结果集再跟t3左连接

        ID NAME
---------- --------------------
       333 ccc
       222 bbb
       111 aaa

SQL> select t1.id,t1.name
  2  from test111 t1,test222 t2,test333 t3
  3  where t1.id=t2.id(+)
  4  and t1.id=t3.id(+);                      //(+)在右边表示左连接，在左边表示右连接

        ID NAME
---------- --------------------
       333 ccc
       222 bbb
       111 aaa

SQL> insert into test333 values (222,'bbb');

1 row created.

SQL> commit;

Commit complete.

SQL> select t1.id,t1.name
  2  from test111 t1 inner join test222 t2
  3  on t1.id=t2.id and t1.name=t2.name
  4  inner join test333 t3
  5  on t1.id=t3.id and t1.name=t3.name;     //on 关键字可以跟and实现多字段关联

        ID NAME
---------- --------------------
       222 bbb

SQL> select t1.id,t1.name
  2  from test111 t1,test222 t2,test333 t3
  3  where t1.id=t2.id
  4  and t1.id=t3.id;

        ID NAME
---------- --------------------
       222 bbb

SQL> delete from test222 where (id,name) in (select id,name from test333); //可以多字段关联删除,但是in后面关联的数据必须来源于查询语句。

1 row deleted.

SQL> select * from test222;

        ID NAME
---------- --------------------
       444 ddd

SQL> update test222 set (id,name)=(select 555,'eee' from dual);        //可以多字段更新，但是=号后面的数据必须来源于查询语句。

1 row updated.

SQL> select * from test222;

        ID NAME
---------- --------------------
       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 NAME
---------- --------------------
           fff
       111 aaa

SQL> delete from test333
  2  where rowid
  3  in
  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语句的关联更新：
SQL> select * from a;

        ID NAME                        AGE     COURSE        ID2
---------- -------------------- ---------- ---------- ----------
       111 jack                        21
       222 mary                         23
       333 lucy                         19

SQL> select * from b;

        ID     COURSE
---------- ----------
       111         98
       222         76
       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)
  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        ID2
---------- -------------------- ---------- ---------- ----------
       111 jack                        21         98        211
       222 mary                         23         76        322
       333 lucy                         19         12        433

SQL> select * from a;

        ID NAME                        AGE     COURSE        ID2
---------- -------------------- ---------- ---------- ----------
       111 jack                         21
       222 mary                         23
       333 blue                         18

SQL> select * from b;

        ID     COURSE
---------- ----------
       111         79
       222         87
       333         89

SQL> update a set (a.course,a.id2)=
  2  (select course ,id+100 from b where b.id=a.id);

3 rows updated.

SQL> select * from a;

        ID NAME                        AGE     COURSE        ID2
---------- -------------------- ---------- ---------- ----------
       111 jack                         21         79        211
       222 mary                         23         87        322
       333 blue                         18         89        433

UPDATE关联更新需要注意的：

SQL> select * from test111;

        ID NAME              AGE
---------- ---------- ----------
       111 jack
       222 mary
       333 tom                99

SQL> select * from test222;

        ID        AGE
---------- ----------
       111         23
       222         34
       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              AGE
---------- ---------- ----------
       111 jack               23
       222 mary               34
       333 tom                               //将没有匹配成功的值全部update为null,这将覆盖原有的值，不符合逻辑，所以需要加上where条件。

SQL> update test111 t1 set t1.age=(select t2.age from test222 t2 where t1.id=t2.id) where id in (
  2  select t2.id from test222 t2 where t1.id=t2.id);

2 rows updated.

SQL> select * from test111;

        ID NAME              AGE
---------- ---------- ----------
       111 jack               23
       222 mary               34
       333 tom                99

--慢慢补充--