Mysql：如何在具有外键的表中插入值

I have these two tables just for example:

我有这两个表，例如：

TAB_TEACHER
- id_teacher   // primary key, autoincrement
- name_teacher // a varchar

TAB_STUDENT
- id_student    // primary key, autoincrement
- name_student  // a varchar
- id_teacher_fk // foreign key reference to a teacher (TAB_TEACHER)

I want to know how to insert in these two cases:

我想知道如何插入这两种情况：

CASE 1- INSERT a new Student with an pre-existin TEACHER, so I have to get the foreign key with a teacher name

案例1-插入一个具有预先存在的教师的新学生，所以我必须获得具有教师姓名的外键

CASE 2- INSERT a new Student with a new TEACHER (the teacher I'm creating in the same time I'm creating the student)

案例2-用新教师插入新学生（我在创建学生的同时创建的老师）

3 个解决方案

#1

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

For case1:

对于case1：

INSERT INTO TAB_STUDENT(name_student, id_teacher_fk)
SELECT 'Joe The Student', id_teacher
  FROM TAB_TEACHER
 WHERE name_teacher = 'Professor Jack'
 LIMIT 1

For case2 you just have to do 2 separate insert statements

对于case2，您只需要执行2个单独的插入语句

#2

Case 1: Insert Row and Query Foreign Key

Here is an alternate syntax I use:

这是我使用的替代语法：

INSERT INTO tab_student 
   SET name_student = 'Bobby Tables',
       id_teacher_fk = (
       SELECT id_teacher
         FROM tab_teacher
        WHERE name_teacher = 'Dr. Smith')

I'm doing this in Excel to import a pivot table to a dimension table and a fact table in SQL so you can import to bost department and expenses tables from the following:

我在Excel中执行此操作以将数据透视表导入到SQL中的维度表和事实表，以便您可以从以下内容导入到bost department和expenses表：

Case 2: Insert Row and Then Insert Dependant Row

Luckily, MySQL supports LAST_INSERT_ID() exactly for this purpose.

幸运的是，MySQL完全支持LAST_INSERT_ID（）用于此目的。

INSERT INTO tab_teacher
   SET name_teacher = 'Dr. Smith';
INSERT INTO tab_student 
   SET name_student = 'Bobby Tables',
       id_teacher_fk = LAST_INSERT_ID()

#3

Case 1

INSERT INTO tab_student (name_student, id_teacher_fk)
    VALUES ('dan red', 
           (SELECT id_teacher FROM tab_teacher WHERE name_teacher ='jason bourne')

it is advisable to store your values in lowercase to make retrieval easier and less error prone

建议以小写形式存储您的值，以使检索更容易，更不容易出错

Case 2

mysql docs

mysql文档

INSERT INTO tab_teacher (name_teacher) 
    VALUES ('tom stills')
INSERT INTO tab_student (name_student, id_teacher_fk)
    VALUES ('rich man', LAST_INSERT_ID())