将数据从一个MySQL表传输到另一个w /参数

I have a table named SUBJECT with fields SUBJ_ID and SUBJ_NAME. Another table is called COURSES with the fields COURSE_NAME, SUBJ_NAME, and SUBJ_ID. My goal is to automatically update the SUBJ_ID in the COURSE table with the SUBJ_ID from the SUBJECT table when the SUBJ_NAME is entered into the COURSE table.

我有一个名为SUBJECT的表,其中包含字段SUBJ_ID和SUBJ_NAME。另一个表名为COURSES,其字段为COURSE_NAME,SUBJ_NAME和SUBJ_ID。我的目标是当SUBJ_NAME进入COURSE表时,使用SUBJECT表中的SUBJ_ID自动更新COURSE表中的SUBJ_ID。

For example, the SUBJECT table has the following data:

例如,SUBJECT表具有以下数据:

+-----------+-------------+
| course_ID | course_name |
+-----------+-------------+
|     1     | math        |
|     2     | physics     |
+-----------+-------------+

If I enter into the COURSES table:

如果我进入COURSES表:

INSERT INTO `courses` VALUES('Algebra 101', 'Math');

How can I have it automatically update SUBJ_ID to equal 1?

如何让它自动将SUBJ_ID更新为等于1?

1 个解决方案

#1

Change your table schema to something like below:

将表架构更改为如下所示:

Course Table

+-----------+---------+------------+
| course_id | subbj_id|course_name |
+-----------+---------+------------+
|     1     | 1       |  math      |
|-----------+---------+----------- +

Subject Table

+-----------+-----------+
| subj_id   | subj_name |
+-----------+---------+-+
|     1     |  math     | 
|-----------+-----------+

Get rid of the subj_name in the courses table (because its redundant and might lead to data corruption like in your case). This will normalize your data, and you will be able to get the information via joins.

摆脱课程表中的subj_name(因为它冗余并可能导致数据损坏,就像你的情况一样)。这将规范化您的数据,您将能够通过联接获取信息。

If you have a subject table with subj_id and subj_name, then the subj_id should be your primary key (unique identifier). Your second table, courses, should have course_id, course_name and subj_id. The course_id should be your primary key (unique identifier). You will then have a one to many foreign key between subj_id in the subject table to subj_id in your course table.

如果您有一个包含subj_id和subj_name的主题表,那么subj_id应该是您的主键(唯一标识符)。你的第二个表,课程,应该有course_id,course_name和subj_id。 course_id应该是您的主键(唯一标识符)。然后,您将在主题表中的subj_id与课程表中的subj_id之间具有一对多的外键。

Once this is set up, you will use this query:

设置完成后,您将使用此查询:

select c.course_name, s.subj_name from courses AS c inner join subject AS s on c.subj_id = s.subj_id;

This will pull the course and subject that it belongs to.

这将拉动它所属的课程和主题。

When ever you need to update the subject name, you now only have to change it in one place, subject.subj_name, and it will propagate over due to the relationship.

当您需要更新主题名称时,您现在只需在一个地方subject.subj_name更改它,并且由于该关系它将传播。

If all this is too much, read up on normalizing data and setting up relationships properly.

如果所有这些都太多,请阅读规范化数据并正确设置关系。

Best of luck!

祝你好运!

#1