MySQL8：連線查詢

五月的倉頡發表於2016-05-30
MySql

連線查詢

連線是關係型資料庫模型的主要特點。

連線查詢是關係型資料庫中最主要的查詢，主要包括內連線、外連線等通過聯結運算子可以實現多個表查詢。

在關係型資料庫管理系統中，表建立時各種資料之間的關係不必確定，常把一個實體的所有資訊存放在一個表中，當查詢資料時通過連線操作查詢出存放在多個表中的不同實體資訊，當兩個或多個表中存在相同意義的欄位時，便可以通過這些欄位對不同的表進行連線查詢。

本文將介紹多表之間的內連線查詢、外連線查詢。

 

建立測試資料

為了後面可以演示內連線、外連線中的左外連線和右外連線，下面建立一些測試資料，首先建立一張base_worker表，用於存放工人基本資訊：

create table base_worker
(
  s_id        int           auto_increment,
  s_name      varchar(20),
  s_age       int,
  primary key(s_id)
)engine=innodb, charset=utf8;

insert into base_worker values(null, "aaa", 20);
insert into base_worker values(null, "bbb", 21);
insert into base_worker values(null, "ccc", 22);
commit;

然後建立一張extra_worker表，用於存放工人額外資訊：

create table extra_worker
(
   e_id        int        auto_increment,
   s_id        int,
   s_nation    varchar(20),
   s_phone     varchar(30),
   primary key(e_id)
)engine=innodb, charset=utf8;

insert into extra_worker values(null, 1, "中國", "00000000");
insert into extra_worker values(null, 2, "美國", "11111111");
insert into extra_worker values(null, 5, "英國", "22222222");
commit;

兩張表之間通過s_id相關聯，當然這裡沒有設定外來鍵，因為我不太喜歡使用外來鍵，一個是語法太麻煩了，另一個是外來鍵的關聯關係太死了，外來鍵不存在插入會報錯，還得重新定位問題。

 

內連線inner join

內連線（inner join）使用比較運算子進行表間某（些）列資料的比較操作，並列出這些表中與連線條件相匹配的資料行，組合成新的記錄。換句話說，在內連線查詢中，只有滿足條件的記錄才能出現在結果關係中。

對base_worker和extra_worker使用內連線：

select b.s_id, b.s_name, b.s_age, e.s_nation, e.s_phone from 
base_worker b, extra_worker e where b.s_id = e.s_id;

看一下查詢結果：

看到base_worker和extra_worker中分別有三條記錄，但是最終查詢出來只有兩條記錄，因為只有這兩條記錄可以通過s_id相匹配上。

另外一個細節點是，s_id這種在兩張表中都存在的欄位，必須指明讀取的是哪張表中的s_id，否則SQL將報錯，但是s_name這種只在base_worker表中存在的欄位則沒有這個限制。

最後，上面的SQL是內連線最常用的SQL寫法，內連線還有另外一種SQL寫法，結果也是一樣的：

select b.s_id, b.s_name, b.s_age, e.s_nation, e.s_phone from 
base_worker b inner join extra_worker e on b.s_id = e.s_id;

可以自己驗證一下，執行效率上也沒有什麼差別。

 

左外連線left join

連線查詢將查詢多個表中相關聯的行，內連線時返回查詢結果集合中的僅僅是符合查詢條件和連線條件的行。但有時候需要包含沒有關聯的行中的資料，即返回查詢結果集合中的不僅僅包含符合的連線條件的行，而且還包含左表或右表中的所有資料行。外連線分為左外連線和右外連線，這裡先看一下左外連線。

左外連線，返回的是左表中的所有記錄以及由表中連線欄位相等的記錄。

看一下SQL：

select b.s_id, b.s_name, b.s_age, e.s_nation, e.s_phone from
base_worker b left outer join extra_worker e on b.s_id = e.s_id;

看一下查詢結果：

顯示了三條紀錄，s_id為3的記錄在extra_worker表中並沒有s_nation與s_phone，所以這兩個值為null，但因為base_worker為左表，因此base_worker中的所有資料都會被查出來。

 

右外連線right join

右外連線是左外連線的反向連線，將返回右表中的所有行，如果右表中的某行在左表中沒有匹配的行，左表將返回空值。

看一下SQL：

select e.s_id, b.s_name, b.s_age, e.s_nation, e.s_phone from
base_worker b right outer join extra_worker e on b.s_id = e.s_id;

注意一下，這裡的s_id是extra_worker的而不是base_worker的。

看一下查詢結果：

同樣的，看到顯示了三條紀錄，s_id為5的記錄在base_worker中並沒有s_name與s_age，所以這兩個值為null，但因為extra_worker表為右表，因此extra_worker表中的所有資料都會被查出來。

 

相關文章