mysql一对多关联查询

mysql一对多关联查询

在数据库设计中，外键（Foreign Key）约束是一种重要的数据库完整性约束，用于在两个表之间建立逻辑链接。外键约束能够确保参照完整性，即确保一个表中的外键列只能包含另一个表（通常是主键表）中的有效值。一对多（One-to-Many）关系是使用外键约束最常实现的关系类型之一。

一对多关系概述

一对多关系是指一个表中的行可以与另一个表中的多行相关联，但反过来不成立。例如，在学校数据库中，一个教师可以教授多门课程，但每门课程只能由一位教师教授。这里，“教师”表与“课程”表之间就存在一对多关系。

外键约束实现一对多

在一对多关系中，通常将外键设置在“多”的那一方的表上，即指向“一”的表的主键。以下是具体的实现步骤：

1. 定义主键表：首先，定义包含主键的表（例如，“教师”表），主键用于唯一标识表中的每一行。
2. 在子表（多的一方）中创建外键：然后，在另一个表（例如，“课程”表）中，创建一个外键列，该列的值必须对应于主键表中的主键列的值。这确保了“课程”表中的每一条记录都能找到与之关联的“教师”表中的唯一一条记录。
3. 设置外键约束：在创建或修改“课程”表时，通过外键约束指定该表中的外键列参照“教师”表的主键列。这样，数据库就会强制实施参照完整性，防止在“课程”表中插入不存在于“教师”表中的教师ID。

案列

在MySQL数据库中创建和操作两个表：teacher（教师表）和student（学生表），以及在这两个表中插入一些示例数据。

1. 创建数据库：首先，使用create database school;命令创建了一个名为school的数据库。这是所有后续操作的上下文环境。

   create database school;
   

2. 选择数据库：接着，使用use school;命令选择刚刚创建的school数据库，以便后续操作都在这个数据库中进行。

   use school;
   

3. 创建教师表（主表）：

   • 通过create table teacher(...)命令创建了一个名为teacher的表，用于存储教师信息。
   • 该表有三个字段：id（教师的唯一标识符，主键，自动增长），name（教师姓名，字符串类型，最大长度50），age（教师年龄，整数类型）。

   create table teacher(
       id int primary key auto_increment,
       name varchar(50),
       age int
   );
   

   

4. 创建学生表（从表），并绑定外键：

   • 通过create table student(...)命令创建了一个名为student的表，用于存储学生信息。
   • 该表有四个字段：id（学生的唯一标识符，主键，自动增长），y_id（学生所属教师的ID，外键），name（学生姓名，字符串类型，最大长度50）。
   • 通过foreign key (y_id) references teacher(id)定义了y_id字段为外键，并指定它参照teacher表的id字段。这建立了教师表和学生表之间的一对多关系，即一个教师可以有多个学生，但一个学生只能属于一个教师。

   create table student(
       id int primary key auto_increment,
       y_id int,
       name varchar(50),
       foreign key (y_id) references teacher(id)
   );
   

   

5. 插入数据：

   • 向teacher表中插入了两条数据，分别表示两位教师（ID自动增长，名字分别为"001"和"002"，年龄分别为30和50）。注意，这里可能是一个小错误，因为通常教师姓名不会简单地是数字（除非有特殊含义），而且ID是自动增长的，不需要手动指定。
   • 向student表中插入了四条数据，表示四个学生，他们分别属于不同的教师（通过y_id字段指定）。但这里有一个问题：第四条数据尝试再次将"张三"插入为y_id为1的学生的名字，这在实际应用中可能会导致重复记录或需要根据业务逻辑进行去重处理。另外，这里假设y_id是已经存在的教师ID（即1和2），这与之前插入的教师ID相匹配。

   # 插入数据
   insert into teacher(name, age) values
                           ("001",30),
                           ("002",50);
   
   
   insert into student( y_id, name) values
                           (1,"张三"),
                           (1,"李四"),
                           (2,"王五"),
                           (1,"张三");