开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> mysql的多表操作（详细） -> 正文阅读

[大数据]mysql的多表操作（详细）

mysql的多表操作

作者：贤大蛋
目标：彻底学懂数据库的基本操作，接下来就是自己造项目写接口

PS：需要先准备至少两张表以及对应的数据

创建外键

外键可以将不同的表之间联系起来（这里的表是products和brand）将products中的brand_id关联到brand中的id：

如果是创建表添加外键约束，我们需要在创建表的()最后添加如下语句

FOREIGN KEY (brand_id) REFERENCES brand(id)

如果是表已经创建好的，需要额外添加外键

ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id)；

外键存在时更新和删除数据

如果products表中引用的外键被修改或者被删除，这个时候我们进行更新操作会报错，这个时候就需要给更新或者删除时候设置几个值。

**RESTRICT（默认属性）：**当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话会报错的，不允许更新或删除；
**NO ACTION：**和RESTRICT是一致的，是在SQL标准中定义的；
**CASCADE：**当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话：
- 更新：那么会更新对应的记录；
- 删除：那么关联的记录会被一起删除掉；
**SET NULL：**当更新或删除某个记录时，会检查该记录是否有关联的外键记录，有的话，将对应的值设置为 NULL

如何修改外键的更新时的动作呢

查看表结构
```
#查看表结构
SHOW CREATE TABLE `products`;
```
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5k9ZVN27-1653035556606)(E:\语雀文档\mysql的多表操作.assets\image-20220519114800433.png)]
根据名称删除外键

# 2. 根据名称将外键删除掉
ALTER TABLE `products` DROP FOREIGN KEY products_ibfk_1;

重新添加外键，并设置新的action

# 3.重新添加外键约束
ALTER TABLE `products` ADD FOREIGN KEY (brand_id) REFERENCES brand(id)
ON UPDATE CASCADE
ON DELETE RESTRICT;

多表查询

如果我们希望查询到产品的同时，显示对应的品牌相关的信息，因为数据是存放在两张表中，所以这个时候就需要进行多表查询。也就是说第一张表中每一个条数据，都会和第二张表中的每一条数据结合一次，默认结果我们称之为 笛卡尔乘积，也称之为直积，表示为 X*Y。

多表之前的连接

事实上我们想要的效果并不是这样的，而且表中的某些特定的数据，这个时候我们可以使用 SQL JOIN 操作：

左连接：我们希望获取到的是左边所有的数据（以左表为主）：
- 这个时候就表示无论左边的表是否有对应的brand_id的值对应右边表的id，左边的数据都会被查询出来；
- 这个也是开发中使用最多的情况，它的完整写法是LEFT [OUTER] JOIN，但是OUTER可以省略的；

# 4.左连接 
# 查询所有的手机（包括没有品牌信息的手机）以及对应的品牌 null--以产品表为主
SELECT * FROM `products` LEFT OUTER JOIN `brand` ON products.brand_id = brand.id;
# 查询没有对应品牌数据的手机
SELECT * FROM `products` LEFT JOIN `brand` ON products.brand_id = brand.id WHERE brand.id IS NULL;

右连接：我们希望获取到的是右边所有的数据（以由表为主）：
- 这个时候就表示无论左边的表中的brand_id是否有和右边表中的id对应，右边的数据都会被查询出来；
- 右连接在开发中没有左连接常用，它的完整写法是RIGHT [OUTER] JOIN，但是OUTER可以省略的；

#5. 右连接
# 查询所有品牌（没有对应的手机数据，品牌也显示）以及对应的手机数据--以品牌表为主
SELECT *FROM `products` RIGHT JOIN `brand` ON products.brand_id = brand.id;
# 查询没有对应手机的品牌信息
SELECT * FROM `products` RIGHT JOIN `brand` ON products.brand_id = brand.id WHERE products.brand_id IS NULL;

内连接：事实上内连接是表示左边的表和右边的表都有对应的数据关联：
- 内连接有其他的写法：CROSS JOIN或者 JOIN都可以；
- 但是他们代表的含义并不相同：
  - SQL语句一：内连接，代表的是在两张表连接时就会约束数据之间的关系，来决定之后查询的结果
  - SQL语句二：where条件，代表的是先计算出笛卡尔乘积，在笛卡尔乘积的数据基础之上进行where条件的帅选

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RG4jWAJf-1653035556621)(E:\语雀文档\mysql的多表操作.assets\image-20220519155339742.png)]

# 6.内连接
# 查询所有有品牌信息的手机
SELECT *FROM `products`JOIN `brand` ON products.brand_id= brand.id;
# 查询有品牌信息并且价格是8699的手机
SELECT * FROM `products` JOIN `brand` ON products.brand_id = brand.id WHERE price = 8699;

全连接：SQL规范中全连接是使用FULL JOIN，但是MySQL中并没有对它的支持，我们需要使用 UNION 来实现。

# 7.全连接
# (左)UNION(右）左图
(SELECT * FROM `products` LEFT OUTER JOIN `brand` ON products.brand_id = brand.id)
UNION
(SELECT *FROM `products` RIGHT JOIN `brand` ON products.brand_id = brand.id);

多对多数据初始化

PS：这里提供多对多的关系表和数据，按需下载：

多对多关系表设计数据

创建关系表：

# 创建关系表
CREATE TABLE IF NOT EXISTS `students_select_courses`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	student_id INT NOT NULL,
	course_id INT NOT NULL,
	FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE,
	FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE
);

# 3.学生选课
# elva选择了英文、数学、历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 1);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (1, 4);

# lilei选择了语文和历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (3, 4);

# lily选择了语文、数学和历史
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (5, 2);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (5, 3);
INSERT INTO `students_select_courses` (student_id, course_id) VALUES (5, 4);

完成数据初始化

查询多对多数据

查询多条数据

需求：所有的学生的所有的课程

解决：要展示所有学生选择的所有课程，可以采用内连接

# 查询所有的学生选择的所有课程
SELECT stu.id id,stu.`name` stuName,stu.age stuAge,cs.`name` csName,cs.price csPrice
FROM `students` stu
JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
JOIN `courses` cs ON ssc.course_id = cs.id;

需求：查询所有的学生选课情况

解决：查询所有学生，就是以学生表为主，采用左连接

# 查询所有的学生选课情况
SELECT stu.id stuId,stu.`name` stuName,cs.id csId,cs.`name` csName,cs.price csPrice
FROM `students` stu
LEFT JOIN `students_select_courses`ssc
ON stu.id = ssc.student_id
LEFT JOIN `courses` cs
ON ssc.course_id = cs.id;

查询单个学生课程

需求：查询elva同学选择了哪些课程

解决：先要查询所有学生选择的所有课程，可以采用内连接，然后根据elva同学的id筛选出她选了什么课程

#查询elva同学选择了哪些课程，elva的id为1
SELECT 
stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
JOIN `courses` cs 
ON ssc.course_id = cs.id
WHERE stu.id = 1;

查询哪些学生没有选择和哪些课程没有被选择

需求：哪些学生是没有选课的

解决：学生没有选课，即用学生表为主，采用左连接

# 哪些学生是没有选课的
SELECT 
stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
LEFT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
LEFT JOIN `courses` cs
ON ssc.course_id = cs.id
WHERE cs.id IS NULL;

需求：查询哪些课程没有学生选择

解决：课程没有被学生选择，即以课程表为主，采用右连接

# 查询哪些课程没有被学生选择
SELECT 
stu.id studentId, stu.name studentName, cs.id courseId, cs.name courseName, cs.price coursePrice
FROM `students` stu
RIGHT JOIN `students_select_courses` ssc
ON stu.id = ssc.student_id
RIGHT JOIN `courses` cs
ON ssc.course_id = cs.id
WHERE stu.id IS NULL;

将联合查询的数据转成对象

Ps:这里用products和brand两张表做例子

在真实开发中，查询到的数据有一部分是需要放入到一个对象中，那么我们可以使用下面的查询方式：

这个时候我们要用 JSON_OBJECT。

# 将联合查询到的数据转成对象（一对多）
SELECT 
	products.id id, products.title title, products.price price,
	JSON_OBJECT('id', brand.id, 'name', brand.name, 'website', brand.website) brand
FROM `products`
LEFT JOIN `brand` ON products.brand_id = brand.id;

多对多转成数组

在多对多的关系中，我们希望查询到的是一个数组：这个时候就要用到 JSON_ARRAYAGG和JSON_OBJECT结合来使用；

# 将查询到的多条数据，组织成对象，放入到一个数组中(多对多)
SELECT 
	stu.id, stu.name, stu.age,
	JSON_ARRAYAGG(JSON_OBJECT('id', cs.id, 'name', cs.name, 'price', cs.price))
FROM `students` stu
JOIN `students_select_courses` ssc ON stu.id = ssc.student_id
JOIN `courses` cs ON ssc.course_id = cs.id
GROUP BY stu.id;