mysql一对多、多对多关系表的建立与查询

夏梦，只写最简洁最有效的教程—欢迎关注

在实际的开发场景中，我们会建立非常多的表，其中一对多和多对多关系表的建立和查询对与一些刚接触的人来说可能会有些疑惑，所以为了解决这些疑惑，有了这篇文章。

注意：本文的重点在于一对多、多对多关系表的建立和查询

一对多

现在你手里正拿着一部iPhone手机，除了这部之外你家里还有一部华为手机和三星手机，一个人有多部手机，这就是典型的一对多关系。

此时，我们就知道需要建立两个表，一个是"人"表，一个是"手机"表。

建表

于是，先建立一个people表，包含了以下字段

再建立一个手机表，包含了以下字段

建表语句

DROP TABLE IF EXISTS `people`;
CREATE TABLE `people`  (
  `people_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`people_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `phone`  (
  `phone_id` int(11) NOT NULL AUTO_INCREMENT,
  `brand` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `model` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `people_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`phone_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

赋值

现在向两个表中手动填值
people表

phone表

查询

使用左连接查询

查询语句

SELECT * FROM people LEFT JOIN phone on people.people_id = phone.people_id;

多对多

假如你是一个老师，需要上很多门课程。
现在学校有一个需求：要知道老师上哪些课，要知道课是被哪些老师上，这就是典型的多对多关系。
此时，我们就知道需要建立三个表，一个是"老师"表，一个是"课程"表，一个是老师和课程的联系表。

建表

老师表— teach

课程表 — class

老师课程关系表 — teach_class

建表语句

CREATE TABLE `class`  (
  `class_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '课程名字',
  PRIMARY KEY (`class_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `teach`  (
  `teach_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `teach_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '老师名字',
  PRIMARY KEY (`teach_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `teach_class`  (
  `class_id` bigint(20) NOT NULL,
  `teach_id` bigint(20) NOT NULL,
  PRIMARY KEY (`class_id`, `teach_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

插值

老师表— teach

课程表 — class

老师课程关系表 — teach_class

查询

左连接查询

查询单个课程被哪些老师上

查询老师上哪些课程

查询语句

SELECT * FROM
	teach t
LEFT JOIN 
	teach_class 
on 
	t.teach_id = teach_class.teach_id
LEFT JOIN 
	class on teach_class.class_id = class.class_id
 WHERE t.teach_id = '1';
-- WHERE class.class_id = '1';

---

教程结束