MySQL資料庫學習足跡記錄13–聯結表–INNER JOIN…ON

MySQL學習足跡記錄13–聯結表–INNER JOIN…ON

 

  學資料庫聯結表這一節內容幾乎吐血,看書上的介紹和百度到的內容,總感到一臉茫然…..也許是我理解能力太差瞭吧。但我是一個不會輕易放棄的人,研究瞭半小時,終於搞明白瞭聯結的原理,自我總結如下:關鍵在於“select語句,如果沒有WHERE子句限定,將返回兩個表的笛卡爾積.”

 

Example:
    
表一: Kemu
+-----+--------+
| kno | kname  |
+-----+--------+
| b   | yuwen  |
| b1  | shuxue |
+-----+--------+



表二: Chenji;
+------+------+---------+
| sno  | kno  | chengji |
+------+------+---------+
|    1 | a    |      10 |
|    2 | a1   |      20 |
+------+------+---------+
2 rows in set (0.00 sec)




*重點:select語句,如果沒有WHERE子句限定,將返回兩個表的笛卡爾積.
mysql> SELECT * FROM Kemu,Chenji;
+-----+--------+------+------+---------+
| kno | kname  | sno  | kno  | chengji |
+-----+--------+------+------+---------+
| b   | yuwen  |    1 | a    |      10 |
| b1  | shuxue |    1 | a    |      10 |
| b   | yuwen  |    2 | a1   |      20 |
| b1  | shuxue |    2 | a1   |      20 |
+-----+--------+------+------+---------+
4 rows in set (0.00 sec)

 

 

 Tips:SELECT 語句後面的所有表將會合成一個大表,所以,SELECT後所跟的各種子句就相當於在一個表中篩選數據瞭。

 

 

1.外鍵:某個表的一列,它包含另一個表的主鍵值,設定瞭兩個表之間的關系

 

2. 聯結不是物理實體,它在實際的資料庫表中不存在,它存在與查詢執行當中

 

3.創建聯結

  Example:
  mysql> SELECT vend_name,prod_name,prod_price
            -> FROM vendors,products
           -> WHERE vendors.vend_id = products.vend_id        #WHERE子句指示MySQL匹配vendors表的  
           -> ORDER BY vend_name,prod_name;                     #vend_id和products表中的vend_id.

+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.01 sec)

 

   

TIPS:

   *WHERE聯結兩個表時,實際是將第一個表中的每一行和第二個表中的每一行配對.並過濾不符合條件的行.

 

4.內部聯結INNER JOIN …. ON

  Example:
  mysql> SELECT vend_name,prod_name,prod_price
           -> FROM vendors INNER JOIN products                     #使用明確的聯結語法能狗確保不會忘記聯結條件
           -> ON vendors.vend_id = products.vend_id                #聯結條件用特定的ON子句,
           -> ORDER BY vend_name,prod_name;                        #傳遞的實際條件與傳遞給WHERE的相同
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)

 

5.聯結多個表

   *SQL對一條SELECT語句中可以聯結的表的數目沒有限制。

   Example:
    mysql> SELECT prod_name,vend_name,prod_price,quantity
            -> FROM orderitems,products,vendors
            -> WHERE products.vend_id=vendors.vend_id
           -> AND orderitems.prod_id=products.prod_id
           -> AND order_num=20005;
+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us |       5.99 |       10 |
| 1 ton anvil    | Anvils R Us |       9.99 |        3 |
| TNT (5 sticks) | ACME        |      10.00 |        5 |
| Bird seed      | ACME        |      10.00 |        1 |
+----------------+-------------+------------+----------+
4 rows in set (0.00 sec)

 

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。