mysql数据库中一对一、一对多、多对多的实现

一对一关系

什么是一对一的关系

一个数据表的每一行只能跟另一张表中的一行关联

如何实现一对一的关系

实现一对一需要使用外键约束，在employee表中的主键设为info表中的外键，也就是给info表设置一个外键，这个外键指向employee表中的主键，通过外键关系将两个表进行连接

优点

1. 结构简单
2. 数据的完整性高

缺点

1. 数据冗余
2. 查询效率低

| employee | CREATE TABLE `employee` (
  `employee_id` int NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

| info  | CREATE TABLE `info` (
  `employee_id` int NOT NULL,
  `name` varchar(11) DEFAULT NULL,
  `phone` varchar(12) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  CONSTRAINT `fk_emp_info` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |    

多对多

什么是多对多的关系

1. 多对多就是双向的一对多，即A表中的一行数据对应B表中的多行数据，相同的B表中的一行数据也对应A表中的多行数据。
2. 例如：学生和老师：老师表和学生表：
   1. 一个老师有多个学生，相同一个学生也有多个老师
   2. 实现这个多对多就需要班级连接起来学生和老师，找到班级表就找到了学生表和老师表，
   3. 通过班级表就可以实现一个老师给多个学生上课，一个学生上多个老师的课
3. 实现多对多需要一个中间表

如何实现多对多关系

多对多关系的实现需要一个中间表，即为这个中间表设置两个外键约束，一个指向学生表的唯一标识、另一个指向老师表的唯一标识，通过中间表class将老师和学生连接

优点

1. 减少表中数据的冗余
2. 保证数据的唯一性
3. 提高查询效率

通常在多对多关系中，在中间表中会建立一个联合主键

联合主键的优点

1. 数据完整性
2. 索引性能优化
3. 保证数据的唯一性

mysql> CREATE TABLE user(user_id int primary key,             
    -> username VARCHAR(11),                                  
    -> password VARCHAR(20)                                   
    -> );                                                     
Query OK, 0 rows affected (0.15 sec)                          
                                                              
mysql> CREATE TABLE role(role_id int PRIMARY KEY,             
    -> role_username VARCHAR(11)                              
    -> );                                                     
Query OK, 0 rows affected (2.02 sec)                          
                                                              
mysql> CREATE TABLE user_role(user_id INT,                    
    -> role_id INT,                                           
    -> PRIMARY KEY(user_id,role_id),                          
    -> FOREIGN KEY(user_id) REFERENCES user(user_id),         
    -> FOREIGN KEY(role_id) REFERENCES role(role_id)          
    -> );                                                     
Query OK, 0 rows affected (2.02 sec)                          

什么是一对多

一对多就是指主表中的一行数据可以同时对应从表中的多条数据，反过来，从表的多条数据指向主表的一条数据

优点

1. 保证数据的唯一性
2. 简化了数据的维护

缺点

1. 数据冗余
2. 数据的一致性维护较难
3. 在数据量大的时候，查询操作耗时

mysql> CREATE TABLE class(class_id int PRIMARY KEY AUTO_INCREMENT,
    -> class_name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.69 sec)

mysql> CREATE TABLE students(student_id INT PRIMARY KEY AUTO_INCREMENT,
    -> st_name VARCHAR(12),
    -> st_sex CHAR(2),
    -> st_phone VARCHAR(12),
    -> class_id INT,
    -> FOREIGN KEY(class_id) REFERENCES class(class_id)
    -> );
Query OK, 0 rows affected (0.17 sec)

,
-> class_id INT,
-> FOREIGN KEY(class_id) REFERENCES class(class_id)
-> );
Query OK, 0 rows affected (0.17 sec)