MySQL基础回顾03（对于01外键相关的补充）

多表

        在实际开发过程中，要完成一个项目需要很多张表才能完成，这时候单表就很难满足需求。

而且单表可能还存在一些数据冗余的情况。为了解决上述问题，这时就可以采用多表来实现需求。

多表关系分析

        拿上表为例，

        1. 员工表中有一个字段dep_id与部门表中的主键对应字段，这个字段就叫做关系属性
        2. 拥有关系属性的员工表被称为 从表（多）, 与关系属性对应的主键所在的表叫做 主表（一）

多表设计上的问题

        多表在使用过程中也会存在一些问题，比如当我们在添加数据时添加了主表主键中不存在的值，数据依然可以添加，这显然是不合理的。为了解决这一问题，可以使用外键约束。

外键约束

                外键指的是在从表中与主表的主键对应的那个字段

        外键约束

                使用外键约束可以让两张表之间产生一个对应关系，从而保证主从表数据的正确性、完整性

如何创建外键约束

        语法格式

                1.新建表时添加外键约束     

constraint 外键约束名 foreign key（从表外键字段名） references 主表名（主键字段）;

                2.为已经创建好的表添加外键约束            

alter table 从表名 add constraint 外键约束名 foreign key（从表外键字段）
 references 主表名（主键字段）;

如何删除外键约束

        语法格式

alter table 从表名 drop foreign key 外键约束名；

        注意事项

                在添加了外键约束后，进行操作时要符合以下要求：        

                操作从表

                1.向从表（子表）中添加数据时，要添加的外键值必须要在关联的主表主键值中存在

                2.修改从表（子表）中的外键值时，修改后的外键值必须要在关联的主表主键值中存在

                操作主表

                1.删除主表中的数据时，从表中有关联的外键值，要先将从表中的外键值设置为null或者直接将关联数据删除

                2.变更主表中的数据时，从表中有关联的外键值，要先将从表中的外键值设置为null、直接将关联数据删除或者将从表中关联的外键值同步为新的主表中的主键值

                注意

                1.创建表时，要先创建主表，然后再创建从表        2.删除表时，要先删除从表然后再删除主表

外键约束设置

        删除时

                1.RESTRICT（默认）和 No action 作用类似，删除主表中的数据时，都会检查外键约束

                2.setnull：删除主表中的数据时，将关联的从表中的外键值设置为null

                3.cascade：删除主表中的数据时，将关联的从表中的数据一同删除

        变更时

                1.RESTRICT（默认）和 No action 作用类似，变更主表中的数据时，都会检查外键约束 

                2.setnull：变更主表中的数据时，将关联的从表中的外键值设置为null

                3.cascade：变更主表中的数据时，将关联的从表中的外键值同步为变更后主表的主键值
 

---

                             

多表关系设计

表与表之间的3种关系	举例
一对多关系(最常见)	员工表和部门表 学生表和班级表
多对多关系	学生表和课程表 用户表和角色表
一对一关系(使用较少)	一对一的关系可以设计成一张表

一对多关系建表原则

        在从表（多）创建一个字段，该字段作为外键指向主表中的主键

多对多关系建表原则

        多对多关系不能直接处理，需要创建第三张表（中间表），中间表中至少有两个字段，这两个字段分别作为外键指向各自一方的主键，本质上是将多对多拆解为两个一对多。        

一对一关系建表原则

        在实际开发的过程中应用的场景不多，因为一对一的关系完全可以设计为一张表来操作

---

多表查询

        有时候我们需要针对两张表或多张表进行操作，这时候就会用到多表查询

笛卡尔积

        为了方便理解多表连接查询的原理，这里来引入一个新概念——笛卡尔积。笛卡尔积表示的是两个表中的每一行数据任意组合的结果。如果对两个或多个表来进行笛卡尔积操作，这样的操作就可以称为交叉连接（CROSS JOIN）。

        要注意，笛卡尔积不管两个表中的数据是否匹配，都会进行连接，也就是进行两两组合的操作。笛卡尔积在实际方面没有意义，只是为了方便理解连接查询。

        语法格式：

                select 字段名 from 表1 cross join 表2；

多表查询的分类

        内连接查询

                通过指定的条件去匹配两张表中的数据，匹配上就显示，匹配不上就不会显示

                                                

        SQL92隐式内连接

                from子句后面直接写多个表名，多个表名之间用逗号隔开，使用where指定连接条件

        语法格式：

                select 字段名，字段名，... from 表1， 表2 where 连接条件；

        SQL99显式内连接

                        语法格式：

                                select 字段名，字段名，...  from 表1 join 表2 on 连接条件；

内连接的实现

        1.不同的表可以进行内连接

        2.同一张表（自连接）可以进行内连接

        3.判断条件为等值判断，可以进行等值内连接

                SQL92：select 字段名，字段名，.... from 表1， 表2 where 字段1 = 字段2；

                SQL99：select 字段名，字段名，... from 表1 （inner可以省略） join 表2 on 字段1 = 字段2；

        4.判断条件为非等值判断，可以进行非等值内连接    

                SQL92：select 字段名，字段名，.... from 表1， 表2 where 只要进行的不是等值判断都可以；

                SQL99：select 字段名，字段名，... from 表1 （inner可以省略） join 表2 on 只要进行的不是等值判断都可以；

外连接查询

        通过指定的条件去匹配两张表中的数据，匹配上就显示，匹配不上也可以显示

外连接的分类

        左外连接

                以左表为主，左表中的数据全部显示，右表中没有匹配的数据用null填充

        语法格式：select 字段名，字段名，... from 表1 left （outer可以省略） join 表2 on 条件；

                                                

        左外连接的实现

                1.不同的表可以进行左外连接

                2.同一张表（自连接）可以进行左外连接

                3.判断条件为等值判断，可以进行左外连接

                        SQL99：select 字段,... from 表1 left join 表2 on 字段1=字段2;

                4.判断条件为非等值判断，可以进行左外连接

                        SQL99：select 字段,... from 表1 left join 表2 on 字段1 between 字段2 and 字段3;    

        右外连接

                以右表为主，右表中的数据全部显示，左表中没有匹配的数据用null填充

        语法格式：select 字段名，字段名，... from 表1 right（outer可以省略） join 表2 on 条件；

                        ​​​​​​​        ​​​​​​​        ​​​​​​​        

        左外连接的实现

                1.不同的表可以进行左外连接

                2.同一张表（自连接）可以进行右外连接

                3.判断条件为等值判断，可以进行右外连接

                        SQL99：select 字段,... from 表1 right  join 表2 on 字段1=字段2;

                4.判断条件为非等值判断，可以进行右外连接

                        SQL99：select 字段,... from 表1 right  join 表2 on 字段1 between 字段2 and 字段3;

        全外连接

                MySQL不支持全外连接，但是可以利用左外连接和右外连接返回的结果进行合并去重来达到全外连接的目的

        ​​​​​​​        ​​​​​​​        ​​​​​​​        ​​​​​​​        ​​​​​​​     

        语法格式：左外连接

                        union

                        右外连接；

                        返回的结果中左右表中数据都展示，没有匹配填充null