外键与多表关联查询

🎯 本文专栏：MySQL深入浅出
🚀 作者主页：小度爱学习

外键和多表关联

外键：指的是两张或者多张表之间关联关系的字段。

外键约束：是表的约束，是约束表在插入外键数据时能够正确的插入。

如何添加约束：

# 在创建表的同时，将外键约束添加上去
# 首先保证班级表创建成功
# 插入正确的数据
mysql> select * from grade;
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Java精品班       |
|  2 | python数据分析班 |
|  4 | 云原生高级班     |
|  3 | 网络安全班       |
+----+------------------+
4 rows in set (0.00 sec)create table student(id int primary key auto_increment,name varchar(50) unique,gender enum("F", "M"),age int default 18,adddress varchar(255),class_id int,--一定要提前创建gradeforeign key(class_id) references grade(id) #外键约束);
insert into student(name, class_id) values("张三", 1);
insert into student(name, class_id) values("张三2", 2);
insert into student(name, class_id) values("张三3", 1);
insert into student(name, class_id) values("张三4", 3);
insert into student(name, class_id) values("张三5", 1);
insert into student(name, class_id) values("张三6", 4);     mysql> insert into student(name, class_id) values("张三666", 5);  #做了外键约束以后插入错误的class_id就会报错
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db_test`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `grade` (`id`))
mysql> insert into student(name, class_id) values("张三666", null);
Query OK, 1 row affected (0.01 sec)

外键确保了相关的两个字段的两个关系：
子(从)表外键列的值必须在主表参照列值的范围内，或者为空（也可以加非空约束，强制不允许为空）。
当主表的记录被子表参照时，主表记录不允许被删除。
外键参照的只能是主表主键或者唯一键，保证子表记录可以准确定位到被参照的记录。

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 外表表名(主键字段名)[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]格式FOREIGN KEY (外键列名)REFERENCES 主表(参照列)

表与表之间的关联关系

当表与表之间存在了外键，这就意味着，这两张表之间存在某种关联关系。

一旦表存在了关联关系，则会进行外键设计，如果设计外键，将外键设计在哪张表中？

• 一对一 ：外键可以设计在任意一张表中
• 一对多 ：外键必须设计在多方
• 多对多 ：创建第三张表，来专门描述两张表的关联关系

多表关联查询

当两张或者多张表之间存在了关联关系，往往多表查询，如果查询。

• 交叉连接

• 内连接

• 外链接

  • 左外连接
  • 右外连接

• 自连接

• 全连接

• 自然连接 【不建议使用，知道就行】数据库自动使用相同字段名称完成外键关联

select *|字段 [,……] from 表名称 [,表名称] ……

-- 交叉连接(cross join)：
-- 在查询多表时，不指定表的关联关系，数据只能全部匹配
-- 引发笛卡尔积现象select * from student, grade;-- sql98的标准写法select * from student cross join grade;# 内连接：select * from student, grade where student.class_id  = grade.id;select * from student, grade where student.class_id  = grade.id and student.name = '张三';# 注意：sql98的内连接方式时，如果不指定关联条件，不管怎么写，都是交叉连接
select * from student inner join grade;
select * from student join grade;# 正确写法，必须写清楚关联条件
select * from student inner join grade on (student.class_id=grade.id);
select * from student join grade on (student.class_id=grade.id) where student.name = "张三3";
# 内连接只能查询出存在关联关系的数据，如果不存在关联关系，如目前没有班级的学生，目前没有学生的班级
select * from student cross join grade on (student.class_id=grade.id) where student.name = "张三3";# 如果要将这些没关联关系的数据查询出来，则需要使用外连接：
select * from student left join grade on (student.class_id=grade.id);
select * from student right join grade on (student.class_id=grade.id);# 注意：mysql不支持全连接查询  full  join
# 但是SQL存在联合查询  union 、union all# 注意：联合查询，必须保证查询的多条SQL返回的结果结构必须一致，所以联合查询常见于查询一张表
(select * from student where class_id = "1") union (select id, name from student where id > 3); #这条就会报错，因为查询字段不一致
(select * from student where class_id = "1") union all (select * from student where id > 3);# 自连接查询：
# 表的外键指向自身
create table board (id int primary key auto_increment,name varchar(50) unique not null,intro text,parent_id int,foreign key(parent_id) references board(id)
);
insert into board(name, parent_id) values("前端板块", null);
insert into board(name, parent_id) values("后端板块", null);
insert into board(name, parent_id) values("硬件板块", null);
insert into board(name, parent_id) values("html", 1);
insert into board(name, parent_id) values("css", 1);
insert into board(name, parent_id) values("java", 2);
insert into board(name, parent_id) values("python", 2);
insert into board(name, parent_id) values("嵌入式", 3);
insert into board(name, parent_id) values("python基础", 7);
insert into board(name, parent_id) values("django", 7);
insert into board(name, parent_id) values("python GUI开发", 7);
insert into board(name, parent_id) values("css2", 5);
insert into board(name, parent_id) values("css3", 5);mysql> select name from board where parent_id is null;
+----------+
| name     |
+----------+
| 前端板块 |
| 后端板块 |
| 硬件板块 |
+----------+
3 rows in set (0.00 sec)mysql> select name from board where parent_id = (select id from board where name="css");
+------+
| name |
+------+
| css2 |
| css3 |
+------+
2 rows in set (0.00 sec)mysql> select * from board as b, (select id from board where name = "前端板块") as t where b.parent_id = t.id;
+----+------+-------+-----------+----+
| id | name | intro | parent_id | id |
+----+------+-------+-----------+----+
|  4 | html | NULL  |         1 |  1 |
|  5 | css  | NULL  |         1 |  1 |
+----+------+-------+-----------+----+
2 rows in set (0.00 sec)mysql> select name from (select * from board where parent_id is not null) as t where t.parent_id in (1, 2, 3);
+--------+
| name   |
+--------+
| html   |
| css    |
| java   |
| python |
| 嵌入式 |
+--------+
5 rows in set (0.00 sec)