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

查询结果 : 

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