MySQL資料查詢之連接查詢-mysql教程-PHP中文網

MySQL資料查詢之連接查詢

連結關係是關係資料庫模型的主要特徵。也是最主要的查詢，主要包括內連接、外連接等。
透過連接運算子可以實現多個表查詢。
在關聯式資料庫管理系統中，表格建立時各資料之間的關係不必確定，常把一個實體的所有資訊存放在一個表中。當查詢資料時，透過連線操作查詢出存放在多個表中不同實體的資訊。當兩個或多個表格中存在相同意義的欄位時，便可以透過這些欄位對不同的表格進行連接查詢。

以下介紹多表之間的三種連接方式：

(1)內連接查詢
(2)外連接查詢
(3)複合條件連線查詢

（免費學習推薦：mysql影片教學）

#(1)、內連線查詢(inner join)

內連接使用比較運算子進行表間某(某些)列資料的比較操作，並列出這些表中與連接條件向匹配的資料行，組成新紀錄，也就是說，在內連接查詢中，只有滿足條件的記錄才能出現在結果關係中。

下面建立資料表suppliers進行示範：

mysql> create table suppliers    -> (
    -> s_id int not null auto_increment,
    -> s_name char(50) not null,
    -> s_city char(50) null,
    -> s_zip char(10) null,
    -> s_call char(50) not null,
    -> primary key(s_id)
    -> );Query OK, 0 rows affected (0.17 sec)mysql> insert into suppliers(s_id,s_name,s_city,s_zip,s_call)
    -> values(101,'FastFruit Inc.','Tianjin','300000','48075'),
    -> (102,'LT Supplies','Chongqing','400000','44333'),
    -> (103,'ACME','Shanghai','200000','90046'),
    -> (104,'FNK Inc.','Zhongshan','528437','11111'),
    -> (105,'Good Set','Taiyuan','030000','22222'),
    -> (106,'Just Eat Ours','Beijing','010','45678'),
    -> (107,'DK Inc','Zhengzhou','450000','33332');Query OK, 7 rows affected (0.07 sec)Records: 7  Duplicates: 0  Warnings: 0

登入後複製

【範例1】在fruits表和suppliers表之間使用內部連接查詢。

mysql> desc fruits;+---------+--------------+------+-----+---------+-------+| Field   | Type         | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| f_id    | char(10)     | NO   | PRI | NULL    |       || s_id    | int(11)      | NO   |     | NULL    |       || f_name  | char(255)    | NO   |     | NULL    |       || f_price | decimal(8,2) | NO   |     | NULL    |       |+---------+--------------+------+-----+---------+-------+4 rows in set (0.06 sec)mysql> desc suppliers;+--------+----------+------+-----+---------+----------------+| Field  | Type     | Null | Key | Default | Extra          |+--------+----------+------+-----+---------+----------------+| s_id   | int(11)  | NO   | PRI | NULL    | auto_increment || s_name | char(50) | NO   |     | NULL    |                || s_city | char(50) | YES  |     | NULL    |                || s_zip  | char(10) | YES  |     | NULL    |                || s_call | char(50) | NO   |     | NULL    |                |+--------+----------+------+-----+---------+----------------+5 rows in set (0.00 sec)

登入後複製

可以看到fruits和suppliers表中都有相同資料類型的欄位s_id,兩個表透過s_id欄位建立聯繫。

接下來從fruits表中查詢f_name、f_price字段，從suppliers表中查詢s_id、s_name，SQL語句如下：

mysql> select suppliers.s_id,s_name,f_name,f_price    -> from fruits,suppliers    -> where fruits.s_id=suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name         | f_name     | f_price |+------+----------------+------------+---------+|  104 | FNK Inc.       | lemon      |    6.40 ||  101 | FastFruit Inc. | apple      |    5.20 ||  103 | ACME           | apricot    |    2.20 ||  101 | FastFruit Inc. | blackberry |   10.20 ||  104 | FNK Inc.       | berry      |    7.60 ||  107 | DK Inc         | xxxx       |    3.60 ||  102 | LT Supplies    | orange     |   11.20 ||  105 | Good Set       | melon      |    8.20 ||  101 | FastFruit Inc. | cherry     |    3.20 ||  106 | Just Eat Ours  | mango      |   15.70 ||  105 | Good Set       | xbabay     |    2.60 ||  105 | Good Set       | xxtt       |   11.60 ||  103 | ACME           | coconut    |    9.20 ||  102 | LT Supplies    | banana     |   10.30 ||  102 | LT Supplies    | grape      |    5.30 ||  107 | DK Inc         | xbabay     |    3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)

登入後複製

#注意：因為fruits表和suppliers表中有相同的字段s_id，因此在比較的時候，需要完全限定表名，格式為"表名.列名"，如果只給出s_id，MySQL將不知道指的是哪一個，並返回錯誤信息。

【範例2】在fruits表和suppliers表之間，使用inner join語法進行內連接查詢，SQL語句如下：

mysql> select suppliers.s_id,s_name,f_name,f_price    -> from fruits inner join suppliers    -> on fruits.s_id = suppliers.s_id;+------+----------------+------------+---------+| s_id | s_name         | f_name     | f_price |+------+----------------+------------+---------+|  104 | FNK Inc.       | lemon      |    6.40 ||  101 | FastFruit Inc. | apple      |    5.20 ||  103 | ACME           | apricot    |    2.20 ||  101 | FastFruit Inc. | blackberry |   10.20 ||  104 | FNK Inc.       | berry      |    7.60 ||  107 | DK Inc         | xxxx       |    3.60 ||  102 | LT Supplies    | orange     |   11.20 ||  105 | Good Set       | melon      |    8.20 ||  101 | FastFruit Inc. | cherry     |    3.20 ||  106 | Just Eat Ours  | mango      |   15.70 ||  105 | Good Set       | xbabay     |    2.60 ||  105 | Good Set       | xxtt       |   11.60 ||  103 | ACME           | coconut    |    9.20 ||  102 | LT Supplies    | banana     |   10.30 ||  102 | LT Supplies    | grape      |    5.30 ||  107 | DK Inc         | xbabay     |    3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)

登入後複製

使用where子句定義連接條件比較簡單明了，而inner join語法是ANSI SQL 的標準規範，使用inner join連接語法能夠確保不會忘記連接條件，而且，where子句在某些時候回影響查詢的效能。

如果在一個連線查詢中涉及的兩個表都是同一個表，name這種查詢稱為自連接查詢。自連接是一種特殊的內連接，是指相互連接的表在物理上為同一張表，但可以在邏輯上分為兩張表。

【範例3】查詢供應f_id='a1'的水果供應商提供的水果種類，SQL語句如下：

mysql> select f1.f_id,f1.f_name    -> from fruits as f1,fruits as f2    -> where f1.s_id =f2.s_id and f2.f_id ='a1';+------+------------+| f_id | f_name     |+------+------------+| a1   | apple      || b1   | blackberry || c0   | cherry     |+------+------------+3 rows in set (0.00 sec)

登入後複製

(2)、外連接查詢

外連接查詢將查詢多個表中相關聯的行。
內連線時，傳回查詢結果集合中的僅是符合查詢條件和連線條件的行。但有時需要包含沒有關聯的行中數據，即返回查詢結果集合中的不僅包含符合連接條件的行，而且還包括左表(左外連接或左連接)、右表(右外連接或右連接)或兩個邊接表(全外連接)中的所有資料行。外連接分為左外連接或左連接和右外連接或右連接：

①左連接：傳回包括左表中的所有記錄和右表中連接欄位相等的記錄。
②右連接：傳回包含右表中的記錄和左表中連接欄位相等的記錄。

1.左連接left join

首先建立表格orders，SQL語句如下;

mysql> create table orders    -> (
    -> o_num int not null auto_increment,
    -> o_date datetime not null,
    -> c_id int not null,
    -> primary key (o_num)
    -> );Query OK, 0 rows affected (0.11 sec)mysql> insert into orders(o_num,o_date,c_id)
    -> values(30001,'2008-09-01',10001),
    -> (30002,'2008-09-12',10003),
    -> (30003,'2008-09-30',10004),
    -> (30004,'2008-10-03',10005),
    -> (30005,'2008-10-08',10001);Query OK, 5 rows affected (0.06 sec)Records: 5  Duplicates: 0  Warnings: 0

登入後複製

【範例】在customers表和orders表中，查詢所有客戶，包括沒有訂單的客戶，SQL語句如下：

mysql> select * from customers;+-------+----------+---------------------+---------+--------+-----------+-------------------+| c_id  | c_name   | c_address           | c_city  | c_zip  | c_contact | c_email           |+-------+----------+---------------------+---------+--------+-----------+-------------------+| 10001 | redhool  | 200 Street          | Tianjin | 300000 | LiMing    | LMing@163.com     || 10002 | Stars    | 333 Fromage Lane    | Dalian  | 116000 | Zhangbo   | Jerry@hotmail.com || 10003 | Netbhood | 1 Sunny Place       | Qingdao | 266000 | LuoCong   | NULL              || 10004 | JOTO     | 829 Riverside Drive | Haikou  | 570000 | YangShan  | sam@hotmail.com   |+-------+----------+---------------------+---------+--------+-----------+-------------------+4 rows in set (0.00 sec)mysql> select * from orders;+-------+---------------------+-------+| o_num | o_date              | c_id  |+-------+---------------------+-------+| 30001 | 2008-09-01 00:00:00 | 10001 || 30002 | 2008-09-12 00:00:00 | 10003 || 30003 | 2008-09-30 00:00:00 | 10004 || 30004 | 2008-10-03 00:00:00 | 10005 || 30005 | 2008-10-08 00:00:00 | 10001 |+-------+---------------------+-------+5 rows in set (0.00 sec)mysql>  select customers.c_id , orders.o_num    -> from customers left outer join orders    -> on customers.c_id = orders.c_id;+-------+-------+| c_id  | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || 10001 | 30005 || 10002 |  NULL |+-------+-------+5 rows in set (0.00 sec)

登入後複製

2.右連接right join

右連接是左連接的反向連接，將返回右表的所有行。如果右表的某行在左表中沒有符合行，則左表會傳回空值。

【範例】在customers表和orders表中，查詢所有訂單，包括沒有客戶的訂單，SQL語句如下：

mysql> select customers.c_id, orders.o_num    -> from customers right outer join orders    -> on customers.c_id = orders.c_id;+-------+-------+| c_id  | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 ||  NULL | 30004 || 10001 | 30005 |+-------+-------+5 rows in set (0.00 sec)

登入後複製

(3)、複合條件連接查詢

複合條件連接查詢是在連接查詢的過程中，透過新增篩選條件，限制查詢結果，使查詢的結果更加準確。

【例1】在customers表和orders表中，使用inner join語法查詢customers表匯總ID為10001的客戶的訂單訊息，SQL語句如下：

mysql> select customers.c_id,orders.o_num    -> from customers inner join orders    -> on customers.c_id = orders.c_id and customers.c_id = 10001;+-------+-------+| c_id  | o_num |+-------+-------+| 10001 | 30001 || 10001 | 30005 |+-------+-------+2 rows in set (0.00 sec)

登入後複製

【例2】在fruits表和suppliers表之間，使用inner join語法進行內連接查詢，並對查詢結果排序，SQL語句如下：

mysql> select suppliers.s_id,s_name,f_name,f_price    -> from fruits inner join suppliers    -> on fruits.s_id = suppliers.s_id    -> order by fruits.s_id;+------+----------------+------------+---------+| s_id | s_name         | f_name     | f_price |+------+----------------+------------+---------+|  101 | FastFruit Inc. | apple      |    5.20 ||  101 | FastFruit Inc. | blackberry |   10.20 ||  101 | FastFruit Inc. | cherry     |    3.20 ||  102 | LT Supplies    | orange     |   11.20 ||  102 | LT Supplies    | banana     |   10.30 ||  102 | LT Supplies    | grape      |    5.30 ||  103 | ACME           | apricot    |    2.20 ||  103 | ACME           | coconut    |    9.20 ||  104 | FNK Inc.       | lemon      |    6.40 ||  104 | FNK Inc.       | berry      |    7.60 ||  105 | Good Set       | melon      |    8.20 ||  105 | Good Set       | xbabay     |    2.60 ||  105 | Good Set       | xxtt       |   11.60 ||  106 | Just Eat Ours  | mango      |   15.70 ||  107 | DK Inc         | xxxx       |    3.60 ||  107 | DK Inc         | xbabay     |    3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)

登入後複製

更多相關免費學習推薦：mysql教學(影片)

以上是MySQL資料查詢之連接查詢的詳細內容。更多資訊請關注PHP中文網其他相關文章！