在实际的开发场景中,我们会建立非常多的表,其中一对多和多对多关系表的建立和查询对与一些刚接触的人来说可能会有些疑惑,所以为了解决这些疑惑,有了这篇文章。
注意:本文的重点在于一对多、多对多关系表的建立和查询
一对多
现在你手里正拿着一部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;
赋值
查询
使用左连接查询
课程表 — 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';
评论回复