MySQL多表關聯查詢實例分析

本篇內容介紹了“MySQL多表關聯查詢實例分析”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

數據庫設計范式

目前數據庫設計有五種范式 , 一般我們數據庫只需要滿足前三項即可

第一范式 : 確保每列保持原子性

什么是原子性? 意思就是不可再分的,例如下

聯系方式有 QQ,微信 , 電話等等 , 顯然此列不滿足原子性, 如果是單獨的QQ或者電話等,則只有一個, 滿足第一范式

第二范式 : 要有主鍵，要求其他字段都依賴于主鍵

為什么主鍵這么重要? 我們可以這樣理解, 如果把表當作一個隊伍, 那么主鍵就是這個隊伍的隊旗

• 沒有主鍵就沒有唯一性，沒有唯一性在集合中就定位不到這行記錄，所以要主鍵。

• 其他字段為什么要依賴于主鍵？因為不依賴于主鍵，就找不到他們。更重要的是,其他字段組成的這行記錄和主鍵表示的是同一個東西，而主鍵是唯一的,它們只需要依賴于主鍵,也就成了唯一的。

第三范式 : 第三范式就是要消除傳遞依賴，方便理解，可以看做是“消除冗余”

這個要怎樣理解呢? 看下述例子 

如果我們一張表設計成上面這樣, 大致看很正常, 但我們把這張表拆分開來

如果這樣做的話, 是不是條理清晰了很多, 我們直接通過商品編號來關聯這兩張表, 無論在哪方面,都比全部擠在一張表要優于很多

外鍵

 我們知道有主鍵 , 主鍵相當于表的標識, 那么外鍵呢 ? 

● 外鍵：引用另外一個數據表的某條記錄。

● 外鍵列類型與主鍵列類型保持一致 ,數據表之間的關聯/ 引用關系是依靠具體的主鍵（ primary key ）和外鍵（foreign key）建立起來的

語法:

添加外鍵約束
ALTER TABLE 表名 ADD [CONSTRAINT 約束名 ] FOREIGN KEY( 外鍵列 )
         REFERENCES 關聯表( 主鍵 );
刪除外檢鍵    ALTER TABLE 表名 DROP FOREIGN KEY 外鍵約束名

我們在上面第三范式的例子中說到, 消除冗余, 通過某一列來關聯兩個表 , 那么這一個連接起兩個表的列我們一般就會設置為外鍵

但是, 如果我們需要兩個表關聯查詢, 也是不一定去使用外鍵約束的

• 如果兩張表關聯查詢 , 我們并沒有去添加外鍵約束, 我們把這種稱為弱引用

• 如果添加了外鍵約束,那么它就是強引用

那么這兩種引用區別在哪呢?

我們知道 , 當我們使用外鍵后 , 外鍵所在的是從表 , 外鍵指向主表的主鍵 , 那么此時就在這兩張表之間建立起了約束 , 這時我們就不能隨意的去修改主表或者從表里關聯的值 , 這就是強引用

1、當主表中沒有對應的記錄時，不能將記錄添加到從表

2、不能更改主表中的值而導致從表中的記錄孤立

3、從表存在與主表對應的記錄，不能從主表中刪除該行

4、刪除主表前，先刪從表

弱引用我們則可以隨意修改關聯之間的值

-- 創建學生表
CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,
    num INT,
    NAME VARCHAR(20),
    sex CHAR(1),
    gradeId INT   -- 從表外鍵列
)
-- 創建年級表
CREATE TABLE grade(
    -- 主表主鍵列
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
)     
-- 添加外鍵約束
ALTER TABLE student ADD CONSTRAINT fk_grade 
      FOREIGN KEY(gradeId) REFERENCES grade(id)

主表創建并添加數據 :

從表創建并添加數據 : 

可以看到 , gradeId字段添加了外鍵約束 

這時我們試著去刪除主表的一列 : 

可以看到 , 是不能去隨意改變主表的, 如果一旦改變,就會使得從表中的數據孤立 

內連接 

● 把滿足了條件的兩張表中的交集數據查詢出來

語法：

Select 結果 from 表 1 ，表 2 where 表 1.column1 = 表 2.column2

內連接有等值連接, 非等值連接, 自連接, 這里我們主要討論自連接

笛卡爾乘積現象：表1有m行，表2有n行，結果=m*n

什么是自連接呢 , 就是自己關聯自己 , 自己和自己做笛卡爾積,  這么說可能不好理解, 舉例說明如下:

我們平時在淘寶網購填地址的時候, 都是采用選擇的方式, 先選擇省,然后是省下面的市, 接著是市下面的區(縣) ,它們都是在數據庫中存著, 如何去實現這個功能呢 ? 

有人可能會說, 建三張表相互關聯即可 , 但實際是 , 我們采用自連接的方式 , 一張表即可實現

CREATE TABLE demo(     -- 建立demo表
    id INT PRIMARY KEY,
    NAME VARCHAR(50),
    pid INT    
 )

往表中填入數據, pid為關聯上一級的id

-- 自連接 
-- 在多表關系中我們需要定義別名來區分
SELECT d1.name,d2.name,d3.name FROM demo d1 
            INNER JOIN demo d2 ON d1.id=d2.pid  --自連接條件
            INNER JOIN demo d3 ON d2.id=d3.pid  --自連接條件
  WHERE d3.id=6101011   -- 查詢條件

結果 : 

外連接

外連接又分為左外連接與右外連接

先看左外連接 : 

語法 

select 結果 from 表1 left join 表 2 on 表1.column1 = 表 2.column2 

左連接和內連接有什么不同呢?  通過兩幅圖我們就可以看出 ,內連接是取了兩張表的共同部分 , 而左連接是取了左邊表的全部(包括兩張表的共同部分)

也就是說, 不僅查詢兩張表的共同部分, 并且左邊表會被全部查詢出來

我們通過上面外鍵所建的表來演示 , 為了演示方便,我們為student表再添加一列數據

可以看到, 此時第五列并沒有去關聯grade表

-- 左外連接查詢
SELECT * FROM student s 
     LEFT JOIN grade g ON s.gradeId= g.id

查詢結果如下 : 

 那么說到這, 右外連接也就不難理解了 , 每次都會完整的查詢右邊的表

同樣我們再為grade添加一條無關聯的數據

語法 :

 select 結果 from 表1 right join 表2 on 表1.column1 = 表2.column2 

-- 右外連接查詢
SELECT * FROM student s 
     RIGHT JOIN grade g ON s.gradeId= g.id

查詢結果 : 

可以看到, 右表被完全查詢

“MySQL多表關聯查詢實例分析”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！