Oracle SQL寫法

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

--慢慢補充--