设计表时如何实现一对一，一对多和多对多关系？

作者: 狗头军师
发布时间: 2024-12-21 02:59:29 (1月前)

2 条回复

1#
回复此人
v-star*위위 | 2020-08-06 15-26

一对一：使用外键访问被引用的表： ``` student: student_id, first_name, last_name, address_id address: address_id, address, city, zipcode, student_id # you can have a# "link back" if you need ``` 您还必须在外键列（addess.student_id）上设置唯一约束，以防止子表（address）中的多行与引用表（student）中的同一行相关。一对多：在关系的许多方面使用外键链接回“一侧”： ``` teachers: teacher_id, first_name, last_name # the "one" side classes: class_id, class_name, teacher_id # the "many" side ``` 多对多：使用联结表（例如）： ``` student: student_id, first_name, last_name classes: class_id, name, teacher_id student_classes: class_id, student_id # the junction table ``` 查询示例： ``` -- Getting all students for a class: SELECT s.student_id, last_name FROM student_classes sc INNER JOIN students s ON s.student_id = sc.student_id WHERE sc.class_id = X -- Getting all classes for a student: SELECT c.class_id, name FROM student_classes sc INNER JOIN classes c ON c.class_id = sc.class_id WHERE sc.student_id = Y ```

编辑

登录后才能参与评论