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)
