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

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

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

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

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

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

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

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

下面創建數據表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)

• 外連接查詢將查詢多個表中相關聯的行。

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

①左連接：返回包括左表中的所有記錄和右表中連接字段相等的記錄。
②右連接：返回包括右表中的記錄和左表中連接字段相等的記錄。

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)

• 復合條件連接查詢是在連接查詢的過程中，通過添加過濾條件，限制查詢結果，使查詢的結果更加準確。

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