如何从两个不同的表中将值插入到表中？

一尘不染

如何从两个不同的表中将值插入到表中？

mysql

我有三张表

students table 
------------------------------------  
id(PK, A_I)  |  student_name | nationality

teachers table
------------------------------------
id(PK, A_I)  |  teacher_name |  email

classroom table
----------------------
id(PK, A_I)   | date   | teacher_id(FK to teachers.id)  |  student_id(FK to students.id)

如果给我老师的姓名（david例如）和 student_id（7例如）并要求根据表中的将teacher_id插入表中，我会这样做：classroom``id``teachers

insert into classroom (date, teacher_id, student_id)
select '2014-07-08', id, 7
from teachers
where teacher_name = 'david';

现在，如果我没有直接给出学生的 id 并且只给出了学生的姓名怎么办？假设给我老师的名字“大卫”和学生的名字“山姆”。如何teacher_id从teachers表和表中student_id获取并根据各自的名称students将两者插入表中？classroom

阅读 60

2022-10-17

共1个答案

一尘不染

你会这样写查询

insert into classroom (date, teacher_id, student_id)
select '2014-07-08', t.id, s.id
from teachers t,students s
where t.teacher_name = 'david'
and s.student_name = 'sam';

当心。这是笛卡尔积。另一种解决方法是

select teacher_id into @tid from teachers where teacher_name = 'david';
select student_id into @sid from students where student_name = 'sam';
insert into classroom (date, teacher_id, student_id) values ('2014-07-08',@tid,@sid);

2022-10-17