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是一款优秀的关系型数据库 , 在多表查询中,通过表与表之间的关系, 我们就可以连接起各种数据 , 通过外键约束等,我们可以在表与表之间建立起联系 , 等等

到此这篇关于MySQL详细讲解多表关联查询的文章就介绍到这了,更多相关MySQL多表关联查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！