程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 幾種MySQL中的聯接查詢操作方法總結,mysql操作方法

幾種MySQL中的聯接查詢操作方法總結,mysql操作方法

編輯:MySQL綜合教程

幾種MySQL中的聯接查詢操作方法總結,mysql操作方法


前言

現在系統的各種業務是如此的復雜,數據都存在數據庫中的各種表中,這個主鍵啊,那個外鍵啊,而表與表之間就依靠著這些主鍵和外鍵聯系在一起。而我們進行業務操作時,就需要在多個表之間,使用sql語句建立起關系,然後再進行各種sql操作。那麼在使用sql寫出各種操作時,如何使用sql語句,將多個表關聯在一起,進行業務操作呢?而這篇文章,就對這個知識點進行總結。

聯接查詢是一種常見的數據庫操作,即在兩張表(多張表)中進行匹配的操作。MySQL數據庫支持如下的聯接查詢:

  •     CROSS JOIN(交叉聯接)
  •     INNER JOIN(內聯接)
  •     OUTER JOIN(外聯接)
  •     其它

在進行各種聯接操作時,一定要回憶一下在《SQL邏輯查詢語句執行順序》這篇文章中總結的SQL邏輯查詢語句執行的前三步:

  •     執行FROM語句(笛卡爾積)
  •     執行ON過濾
  •     添加外部行

每個聯接都只發生在兩個表之間,即使FROM子句中包含多個表也是如此。每次聯接操作也只進行邏輯查詢語句的前三步,每次產生一個虛擬表,這個虛擬表再依次與FROM子句的下一個表進行聯接,重復上述步驟,直到FROM子句中的表都被處理完為止。
前期准備

 1.新建一個測試數據庫TestDB;

      

create database TestDB;

    創建測試表table1和table2;

   CREATE TABLE table1
   (
     customer_id VARCHAR(10) NOT NULL,
     city VARCHAR(10) NOT NULL,
     PRIMARY KEY(customer_id)
   )ENGINE=INNODB DEFAULT CHARSET=UTF8;

   CREATE TABLE table2
   (
     order_id INT NOT NULL auto_increment,
     customer_id VARCHAR(10),
     PRIMARY KEY(order_id)
   )ENGINE=INNODB DEFAULT CHARSET=UTF8;

    插入測試數據;

   INSERT INTO table1(customer_id,city) VALUES('163','hangzhou');
   INSERT INTO table1(customer_id,city) VALUES('9you','shanghai');
   INSERT INTO table1(customer_id,city) VALUES('tx','hangzhou');
   INSERT INTO table1(customer_id,city) VALUES('baidu','hangzhou');

   INSERT INTO table2(customer_id) VALUES('163');
   INSERT INTO table2(customer_id) VALUES('163');
   INSERT INTO table2(customer_id) VALUES('9you');
   INSERT INTO table2(customer_id) VALUES('9you');
   INSERT INTO table2(customer_id) VALUES('9you');
   INSERT INTO table2(customer_id) VALUES('tx');

    准備工作做完以後,table1和table2看起來應該像下面這樣:

   mysql> select * from table1;
   +-------------+----------+
   | customer_id | city   |
   +-------------+----------+
   | 163     | hangzhou |
   | 9you    | shanghai |
   | baidu    | hangzhou |
   | tx     | hangzhou |
   +-------------+----------+
   4 rows in set (0.00 sec)

   mysql> select * from table2;
   +----------+-------------+
   | order_id | customer_id |
   +----------+-------------+
   |    1 | 163     |
   |    2 | 163     |
   |    3 | 9you    |
   |    4 | 9you    |
   |    5 | 9you    |
   |    6 | tx     |
   +----------+-------------+
   7 rows in set (0.00 sec)

准備工作做的差不多了,開始今天的總結吧。
CROSS JOIN聯接(交叉聯接)

CROSS JOIN對兩個表執行FROM語句(笛卡爾積)操作,返回兩個表中所有列的組合。如果左表有m行數據,右表有n行數據,則執行CROSS JOIN將返回m*n行數據。CROSS JOIN只執行SQL邏輯查詢語句執行的前三步中的第一步。

CROSS JOIN可以干什麼?由於CROSS JOIN只執行笛卡爾積操作,並不會進行過濾,所以,我們在實際中,可以使用CROSS JOIN生成大量的測試數據。

對上述測試數據,使用以下查詢:

select * from table1 cross join table2;

就會得到以下結果:

+-------------+----------+----------+-------------+
| customer_id | city   | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163     | hangzhou |    1 | 163     |
| 9you    | shanghai |    1 | 163     |
| baidu    | hangzhou |    1 | 163     |
| tx     | hangzhou |    1 | 163     |
| 163     | hangzhou |    2 | 163     |
| 9you    | shanghai |    2 | 163     |
| baidu    | hangzhou |    2 | 163     |
| tx     | hangzhou |    2 | 163     |
| 163     | hangzhou |    3 | 9you    |
| 9you    | shanghai |    3 | 9you    |
| baidu    | hangzhou |    3 | 9you    |
| tx     | hangzhou |    3 | 9you    |
| 163     | hangzhou |    4 | 9you    |
| 9you    | shanghai |    4 | 9you    |
| baidu    | hangzhou |    4 | 9you    |
| tx     | hangzhou |    4 | 9you    |
| 163     | hangzhou |    5 | 9you    |
| 9you    | shanghai |    5 | 9you    |
| baidu    | hangzhou |    5 | 9you    |
| tx     | hangzhou |    5 | 9you    |
| 163     | hangzhou |    6 | tx     |
| 9you    | shanghai |    6 | tx     |
| baidu    | hangzhou |    6 | tx     |
| tx     | hangzhou |    6 | tx     |
+-------------+----------+----------+-------------+

INNER JOIN聯接(內聯接)

INNER JOIN比CROSS JOIN強大的一點在於,INNER JOIN可以根據一些過濾條件來匹配表之間的數據。在SQL邏輯查詢語句執行的前三步中,INNER JOIN會執行第一步和第二步;即沒有第三步,不添加外部行,這是INNER JOIN和接下來要說的OUTER JOIN的最大區別之一。

現在來看看使用INNER JOIN來查詢一下:

select * 
from table1 
inner join table2 
on table1.customer_id=table2.customer_id;

就會得到以下結果:

+-------------+----------+----------+-------------+
| customer_id | city   | order_id | customer_id |
+-------------+----------+----------+-------------+
| 163     | hangzhou |    1 | 163     |
| 163     | hangzhou |    2 | 163     |
| 9you    | shanghai |    3 | 9you    |
| 9you    | shanghai |    4 | 9you    |
| 9you    | shanghai |    5 | 9you    |
| tx     | hangzhou |    6 | tx     |
+-------------+----------+----------+-------------+

對於INNER JOIN來說,如果沒有使用ON條件的過濾,INNER JOIN和CROSS JOIN的效果是一樣的。當在ON中設置的過濾條件列具有相同的名稱,我們可以使用USING關鍵字來簡寫ON的過濾條件,這樣可以簡化sql語句,例如:

select * from table1 inner join table2 using(customer_id);

在實際編寫sql語句時,我們都可以省略掉INNER關鍵字,例如:

select * 
from table1 
join table2 
on table1.customer_id=table2.customer_id;

但是,請記住,這還是INNER JOIN。
OUTER JOIN聯接(外聯接)

哦,記得有一次參加面試,還問我這個問題來著,那在這裡再好好的總結一下。通過OUTER JOIN,我們可以按照一些過濾條件來匹配表之間的數據。OUTER JOIN的結果集等於INNER JOIN的結果集加上外部行;也就是說,在使用OUTER JOIN時,SQL邏輯查詢語句執行的前三步,都會執行一遍。關於如何添加外部行,請參考《SQL邏輯查詢語句執行順序》這篇文章中的添加外部行部分內容。

MySQL數據庫支持LEFT OUTER JOIN和RIGHT OUTER JOIN,與INNER關鍵字一樣,我們可以省略OUTER關鍵字。對於OUTER JOIN,同樣的也可以使用USING來簡化ON子句。所以,對於以下sql語句:

select * 
from table1 
left outer join table2 
on table1.customer_id=table2.customer_id;

我們可以簡寫成這樣:

select * 
from table1 
left join table2 
using(customer_id);

但是,與INNER JOIN還有一點區別是,對於OUTER JOIN,必須指定ON(或者using)子句,否則MySQL數據庫會拋出異常。
NATURAL JOIN聯接(自然連接)

NATURAL JOIN等同於INNER(OUTER) JOIN與USING的組合,它隱含的作用是將兩個表中具有相同名稱的列進行匹配。同樣的,NATURAL LEFT(RIGHT) JOIN等同於LEFT(RIGHT) JOIN與USING的組合。比如:

select * 
from table1 
join table2 
using(customer_id);

select * 
from table1 
natural join table2;

等價。

在比如:

select * 
from table1 
left join table2 
using(customer_id);

select * 
from table1 
natural left join table2;

等價。
STRAIGHT_JOIN聯接

STRAIGHT_JOIN並不是一個新的聯接類型,而是用戶對sql優化器的控制,其等同於JOIN。通過STRAIGHT_JOIN,MySQL數據庫會強制先讀取左邊的表。舉個例子來說,比如以下sql語句:

explain select * 
from table1 join table2 
on table1.customer_id=table2.customer_id;

它的主要輸出部分如下:

+----+-------------+--------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+---------------+
| 1 | SIMPLE   | table2 | ALL | NULL     |
| 1 | SIMPLE   | table1 | ALL | PRIMARY    |
+----+-------------+--------+------+---------------+

我們可以很清楚的看到,MySQL是先選擇的table2表,然後再進行的匹配。如果我們指定STRAIGHT_JOIN方式,例如:

explain select * 
from table1 straight_join table2 
on table1.customer_id=table2.customer_id;

上述語句的主要輸出部分如下:

+----+-------------+--------+------+---------------+
| id | select_type | table | type | possible_keys |
+----+-------------+--------+------+---------------+
| 1 | SIMPLE   | table1 | ALL | PRIMARY    |
| 1 | SIMPLE   | table2 | ALL | NULL     |
+----+-------------+--------+------+---------------+

可以看到,當指定STRAIGHT_JOIN方式以後,MySQL就會先選擇table1表,然後再進行的匹配。

那麼就有讀者問了,這有啥好處呢?性能,還是性能。由於我這裡測試數據比較少,大進行大量數據的訪問時,我們指定STRAIGHT_JOIN讓MySQL先讀取左邊的表,讓MySQL按照我們的意願來完成聯接操作。在進行性能優化時,我們可以考慮使用STRAIGHT_JOIN。
多表聯接

在上面的所有例子中,我都是使用的兩個表之間的聯接,而更多時候,我們在工作中,可能不止要聯接兩張表,可能要涉及到三張或者更多張表的聯接查詢操作。

對於INNER JOIN的多表聯接查詢,可以隨意安排表的順序,而不會影響查詢的結果。這是因為優化器會自動根據成本評估出訪問表的順序。如果你想指定聯接順序,可以使用上面總結的STRAIGHT_JOIN。

而對於OUTER JOIN的多表聯接查詢,表的位置不同,涉及到添加外部行的問題,就可能會影響最終的結果。
總結

這是MySQL中聯接操作的全部內容了,內容雖多,但是都還比較簡單,結合文章中的例子,再自己實際操作一遍,完全可以搞定的。這一篇文章就這樣了。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved