MySQL數據查詢之連接查詢的案例

134次閱讀

沒有評論

共計 8572 個字符，預計需要花費 22 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

丸趣 TV 小編給大家分享一下 MySQL 數據查詢之連接查詢的案例，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

連接關系是關系數據庫模型的主要特點。也是最主要的查詢，主要包括內連接、外連接等。

通過連接運算符可以實現多個表查詢。

在關系數據庫管理系統中，表建立時各數據之間的關系不必確定，常把一個實體的所有信息存放在一個表中。當查詢數據時，通過連接操作查詢出存放在多個表中不同實體的信息。當兩個或多個表中存在相同意義的字段時，便可以通過這些字段對不同的表進行連接查詢。

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

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

(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 數據查詢之連接查詢的案例”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！

向 AI 問一下細節

正文完