MySQL資料庫——多表查詢（子查詢、外連接配接、内連接配接、交叉連接配接）

多表查詢

• 建立兩張表并插入資料
• 子查詢
• 外連接配接(outer join）
• • 左連接配接(left join)
  • 右連接配接(right join)
  • 左連接配接和右連接配接可以互相轉換
  • 全連接配接(full [outer] join)
• 内連接配接(inner join)
• 交叉連接配接(cross join)

多個表關聯查詢需要依據多表之間列關系将其連接配接起來，這種連接配接方式分為三種：内連接配接(inner join)、外連接配接(outer join)及交叉連接配接(cross join)

建立兩張表并插入資料

user_infor表

create table user_info(
      id int(2) primary key,
      user_name varchar(12) unique,
      password varchar(15) not null,
      real_name varchar(8) not null,
      age int(3)
); 
           

address表

create table address(
      id int(2) primary key,
      user_id int(2) not null,
      real_name varchar(8),
      mobile char(11),
      address varchar(150)
); 
           

插入資料

insert into user_info values(1,'淺唱灬幸福','[email protected]','王曉明',12);
insert into address values(1,1,'王小明','15516472282','山西太原');
insert into address values(2,1,'王鑫','18404905139','山西大同');
insert into address values(3,1,'任建','15333021730','山西晉城');

insert into user_info values(2,'ぅ浮生若夢〤','56701wz','王楠',36);
insert into address values(4,2,'王楠','15010303314','北京海澱');
insert into address values(5,2,'趙婕','18435224278','山西長治');

insert into user_info values(3,'街角の風鈴','27w4921','李曉飛',9);

insert into address values(6,6,'劉倩','13159775555','吉林長春');
           

子查詢

當需要查詢兩張表中的資料的時候

• 首先需要在user_info表中把’王曉明’對應的id查找出來

#查詢user_info表中的王曉明對應的資料
select id from user_info where real_name = '王曉明';
           

• 然後在address表中查找id為1的資料

select * from address where user_id=1;
           

結果

• 可以看出來這兩句SQL語句是分開的，需要執行兩次，是以這裡可以使用子查詢，把這兩句SQL語句寫在一句SQL語句中

select * from address where user_id = (select id from user_info where real_name = '王曉明');
           

結果和上面的結果相同

外連接配接(outer join）

如果依據多表之間列關系進行外連接配接，查詢結果集不僅僅包括滿足on連接配接條件的資料行,還包括不滿足on連接配接條件的資料行。

左連接配接(left join)

傳回的結果集中不僅包含表之間滿足on連接配接條件的全部資料行，還包含左表（“left [outer] join”關鍵字左邊的表）不滿足on連接配接條件的資料行

#這裡為了友善閱讀我們把SQL語句分成多行書寫
select ui.*,addr.*
from user_info ui
left join address addr
on addr.user_id =  ui.id;
           

結果

這裡可以看出來如果在address表中左連接配接一個user_info表則可以查出來user_info表中的所有資料，即使address表中沒有對應的資料，也會用null顯示。但是address表中沒有對應user_info表中的資料沒有顯示出來。是以左連接配接的表就是以left join 關鍵字左邊的表為主要的表，即從表中沒有對應的資料依然會把所有的資料查出來，但是從表中如果沒有主表中的資料則不顯示。

右連接配接(right join)

右連接配接和左連接配接相似，但是右連接配接是right join關鍵字右邊的表為主表，左邊的為從表。同樣的查詢結果會把所有的主表中的資料顯示出來，從表中的資料如果不符合條件則不顯示。

比如

select ui.*,addr.* 
from user_info ui
right join address addr
on addr.user_id = ui.id;
           

結果

左連接配接和右連接配接可以互相轉換

可以在不改變主表和從表的關系為前提的條件下left和right關鍵字可以互換，隻需要對應的表的位置也進行互換即可。

全連接配接(full [outer] join)

傳回的結果集中不僅包含表之間滿足on連接配接條件的全部資料行，還包含左表（“full [outer] join”關鍵字左邊的表）和右表（“full [outer join”關鍵字右邊的表）中不滿足on連接配接條件的資料行

select ui.*,addr.* from user_info ui full address addr on ui.id = addr.user_id;
           

• 注意：MySQL資料不支援全連接配接，在MySQL資料庫這句SQL語句會報錯

内連接配接(inner join)

如果依據多個表之間列關系進行内連接配接，查詢結果集僅包括滿足連接配接條件的資料行。内連接配接分為等值連接配接、不等值連接配接以及自然連接配接，其中等值連接配接最為常見。

主要介紹一下等值連接配接

等值連接配接：在連接配接條件中使用等号(=)運算符比較被連接配接列的列值是否相等，分顯式等值連接配接和隐式等值連接配接：

• 顯式等值連接配接

select ui.*,addr.*
from user_info ui
inner join address addr
on addr.user_id = ui.id;
           

結果

• 隐式等值連接配接

select ui.*,addr.*
from address addr,user_info ui
where addr.user_id = ui.id;
           

結果

這裡可以看出來這兩種方法的結果相同。但是個人還是建議使用顯示等值連接配接，這樣SQL語句更便于閱讀

交叉連接配接(cross join)

中的每一行與右表（“cross join”關鍵字右邊的表）中的所有行組合，交叉聯接的結果是一個笛卡爾積。

select ui.*,addr.*
from user_info ui
cross join address addr;
           

這裡一同是18條資料，user_info中3條 * address表中6條=18