Mysql多表間關系以及連接配接查詢

多表間關系

一對多關系

概念

一對多的關系是指: 主表的一行資料可以同時對應從表的多行資料，反過來就是從表的多行資料指向主表的同一行資料

應用場景

分類表和商品表、班級表和學生表、使用者表和訂單表等等

建表原則

将一的一方作為主表，多的一方作為從表，在從表中指定一個字段作為外鍵，指向主表的主鍵

多對多關系

概念

兩張表都是多的一方，A表的一行資料可以同時對應B表的多行資料，反之B表的一行資料也可以同時對應A表的多行資料

應用場景

訂單表和商品表、學生表和課程表等等

建表原則

因為兩張表都是多的一方，是以在兩張表中都無法建立外鍵，是以需要新建立一張中間表，在中間表中定義兩個字段，這倆字段分别作為外鍵指向兩張表各自的主鍵

可以在架構設計器中展示一對多關系

一對一關系

第一種一對一關系

我們之前學習過一對多關系，在一對多關系中主表的一行資料可以對應從表的多行資料，反之從表的一行資料則隻能對應主表的一行資料。這種一行資料對應一行資料的關系，我們可以将其看作一對一關系

第二種一對一關系

A表中的一行資料對應B表中的一行資料，反之B表中的一行資料也對應A表中的一行資料，此時我們可以将A表當做主表B表當做從表，或者是将B表當做主表A表當做從表

建表原則

在從表中指定一個字段建立外鍵并指向主表的主鍵，然後給從表的外鍵字段添加唯一限制

多表關聯查詢

多表關聯查詢是使用一條SQL語句，将關聯的多張表的資料查詢出來

交叉查詢

交叉查詢其實就是将多張表的資料沒有條件地連接配接在一起進行展示（根據笛卡爾積）

文法

select a.列,a.列,b.列,b.列 from a,b ;  

select a.*,b.* from a,b ;  
--或者 
select * from a,b;
           

通過查詢結果我們可以看到，交叉查詢其實是一種錯誤的做法，在查詢到的結果集中有大量的錯誤資料，我們稱交叉查詢到的結果集是笛卡爾積

笛卡爾積

假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以擴充到多個集合的情況。

内連接配接查詢

交叉查詢産生這樣的結果并不是我們想要的，那麼怎麼去除錯誤的、不想要的記錄呢，當然是通過條件過濾。通常要查詢的多個表之間都存在關聯關系，那麼就通過關聯關系(主外鍵關系)去除笛卡爾積。這種通過條件過濾去除笛卡爾積的查詢，我們稱之為連接配接查詢。連接配接查詢又可以分為内連接配接查詢和外連接配接查詢

隐式内連接配接查詢

隐式内連接配接查詢裡面是沒有inner join關鍵字

哪個表在前就先顯示哪個

顯式内連接配接查詢

顯式内連接配接查詢裡面是有inner join關鍵字

内連接配接查詢練習

查詢所有類别下的商品資訊,如果該類别下沒有商品則不展示

-- 2.1 隐式内連接配接方式 
select *from t_category  c, t_product  p WHERE c.cid = p.cno;

-- 查詢手機數位這個分類下的所有商品的資訊以及分類資訊
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid WHERE tc.cname = '手機數位';

-- 2.2 顯示内連接配接方式  
SELECT * from t_category c INNER JOIN t_product p ON c.cid = p.cno
           

内連接配接查詢的特點

主表和從表中的資料都是滿足連接配接條件則能夠查詢出來，不滿足連接配接條件則不會查詢出來

外連接配接查詢

我們發現内連接配接查詢出來的是滿足連接配接條件的公共部分， 如果要保證查詢出某張表的全部資料情況下進行連接配接查詢. 那麼就要使用外連接配接查詢了. 外連接配接分為左外連接配接和右外連接配接

左外連接配接查詢

概念

以join左邊的表為主表,展示主表的所有資料,根據條件查詢連接配接右邊表的資料,若滿足條件則展示,若不滿足則以null顯示。可以了解為：在内連接配接的基礎上保證左邊表的資料全部顯示

文法

select 字段 from a left [outer] join b on 條件
           

右外連接配接查詢

概念

以join右邊的表為主表,展示右邊表的所有資料,根據條件查詢join左邊表的資料,若滿足則展示,若不滿足則以null顯示。可以了解為：在内連接配接的基礎上保證右邊表的資料全部顯示

文法

select 字段 from a right [outer] join b on 條件
           

union聯合查詢實作全外連接配接查詢

首先要明确，聯合查詢不是多表連接配接查詢的一種方式。聯合查詢是将多條查詢語句的查詢結果合并成一個結果并去掉重複資料。

全外連接配接查詢的意思就是将左表和右表的資料都查詢出來，然後按照連接配接條件連接配接

union的文法

自連接配接查詢

自連接配接查詢是一種特殊的多表連接配接查詢，因為兩個關聯查詢的表是同一張表，通過取别名的方式來虛拟成兩張表，然後進行兩張表的連接配接查詢

子查詢

如果一個查詢語句嵌套在另一個查詢語句裡面，那麼這個查詢語句就稱之為子查詢，根據位置不同，分為：where型，from型，exists型。注意：不管子查詢在哪裡，子查詢必須使用()括起來。

where型

①子查詢是單值結果，那麼可以對其使用（=，>等比較運算符）

# 查詢價格最高的商品資訊
select * from t_product where price = (select max(price) from t_product)
           

②子查詢是多值結果，那麼可對其使用（【not】in(子查詢結果)，或 >all(子查詢結果)，或>=all(子查詢結果)，<all(子查詢結果)，<=all（子查詢結果)，或 >any(子查詢結果)，或>=any(子查詢結果)，<any(子查詢結果)，<=any（子查詢結果)）

# 查詢價格最高的商品資訊
SELECT * FROM t_product WHERE price >=ALL(SELECT price FROM t_product)
           

from型

子查詢的結果是多行多列的結果，類似于一張表格。

必須給子查詢取别名，即臨時表名，表的别名不要加“”和空格。

-- 思路一: 使用連接配接查詢
-- 使用外連接配接，查詢出分類表的所有資料
SELECT tc.cname,COUNT(tp.pid) FROM t_category tc LEFT JOIN t_product tp ON tp.cno = tc.cid GROUP BY tc.cname

-- 思路二: 使用子查詢
-- 第一步:對t_product根據cno進行分組查詢，統計每個分類的商品數量
SELECT cno,COUNT(pid) FROM t_product GROUP BY cno
-- 第二步: 用t_category表去連接配接第一步查詢出來的結果，進行連接配接查詢,此時要求查詢出所有的分類
SELECT tc.cname,IFNULL(tn.total,0) '總數量' FROM t_category tc LEFT JOIN (SELECT cno,COUNT(pid) total FROM t_product GROUP BY cno) tn ON tn.cno=tc.cid
           

exists型

# 查詢那些有商品的分類
SELECT cid,cname FROM t_category tc WHERE EXISTS (SELECT * FROM t_product tp WHERE tp.cno = tc.cid);