MySQL多表關聯查詢實例分析

資料庫設計範式

目前資料庫設計有五種範式, 一般我們資料庫只需要滿足前三項即可

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

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

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

第二範式: 要有主鍵，要求其他欄位都依賴於主鍵

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

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

其他欄位為什麼需要依賴主鍵呢？因為如果不依賴主鍵，就無法確定它們的位置。更重要的是,其他字段組成的這行記錄和主鍵表示的是同一個東西，而主鍵是唯一的,它們只需要依賴於主鍵,也就成了唯一的。

第三範式: 第三範式就是要消除傳遞依賴，方便理解，可以看做是「消除冗餘」

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

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

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

外鍵

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

● 外鍵：引用另外一個資料表的某筆記錄。

● 外鍵列型別與主鍵列型別一致,資料表之間的關聯/ 引用關係是依靠特定的主鍵（ primary key ）與外鍵（foreign key）建立起來的

語法:

新增外鍵限制
ALTER TABLE 表名ADD [CONSTRAINT 約束名稱] 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多表關聯查詢實例分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！