填充MySQL数据库中的字段 [英] Populating fields in MySQL Database

问题描述

我目前在MySQL数据库中总共创建了12个表。这些表中的11个共享相关的数据，并且彼此索引。我在填充表的每个字段中的值并确保它们正确索引时遇到一些困难。我想知道是否有一种更简单的方法可以在一条记录中填充所有表中的每个字段而无需手动进行操作，而不必担心 foreign_key 与另一个的正确匹配表（索引）。查询可能可以完成这项工作，但我不确定如何构建这样的查询。

I have currently created a total of 12 tables in MySQL database. 11 of those tables share correlated data and are indexing with each other. I am undergoing some difficulties populating the values in each individual field of the tables and making sure they index properly. I was wondering if there is an easier way that I can populate each field in all tables for one record without doing it manually and having to worry for the proper foreign_key matches with the other table(indexing). Possibly a query can do the job but I am not sure how I can build such query. Or any other suggestions specifically tying to this example.

下面是指向表设计和查询的链接，如果您想在数据库中创建表的精确副本。

Below are links to the desing of tables and a query if you will like to create an exact copy of tables in my database.

表结构/设计

创建所有表的查询

推荐答案

基于上一个问题的表格，您需要 INSERT 首先在独立表（或基表）上记录。其中一些表格是 event ， semester ， Major_Minor ，之所以称为独立表，是因为没有定义外键约束。

Based on your table from your previous question, you need to INSERT records first on the independent table (or the base tables). Some of these tables are event, semester, Major_Minor, etc. These are called independent tables because no foreign key constraints were defined.

要在独立表上插入的示例查询，

Sample Query to Insert on independent tables,

-- INSERTING records on table event
INSERT INTO event (ID, event_description, event_datetime) VALUES
  (1, 'hello', NOW()),
  (2, 'world', NOW()),
  (3, 'stack', NOW()),
  (4, 'overflow', NOW());

-- INSERTING records on table semester
INSERT INTO semester (ID, SEMESTER_NAme) VALUES
  (1, 'First Semester'), 
  (2, 'Second Semester'), 
  (3, 'Summer'); 

-- INSERTING records on table Major_Minor
INSERT INTO Major_Minor (ID, Major_Minor_Name) VALUES
  (1, 'Math'),
  (2, 'Science'),
  (3, 'English');

-- INSERTING records on table class
INSERT INTO class (ID, class_name) VALUES
  (1, 'Alpha'),
  (2, 'Beta'),
  (3, 'Gamma'),
  (4, 'Omega');

插入记录后，您现在可以插入在依赖表上。这些表称为 dependent 表，因为在它们上定义了外键约束。您不能在其他表上不存在的某些字段上添加值。从属表的示例是 Major_Class_br 表，

After records has been inserted, you can now INSERT on dependent tables. These are called dependent tables because foreign key constraints were defined on them. You can't add a value on certain fields it does not exist on the other table. Example of dependent table is Major_Class_br table,

-- INSERTING records on table Major_Class_br
INSERT INTO Major_Class_br (ID, Class_ID, Major_Minor_ID) VALUES
    (1,1,1),
    (2,1,2),
    (3,1,3),
    (4,2,1),
    (5,2,1),
    (6,4,2);

如您所见， Class_ID ，并且表上已经存在 Major_Minor_ID ： class 和 Major_Minor 因为表 Major_Class_br 依赖于它们。为了进一步说明，请尝试执行以下查询，其中 Class 的值尚不存在 Class 表，

As you can see, the values for Class_ID, and Major_Minor_ID already existed on tables: class and Major_Minor because table Major_Class_br is dependent on them. To illustrate more on that, try executing the query below wherein the value for Class_ID doesn't exist yet on the Class table,

INSERT INTO Major_Class_br (ID, Class_ID, Major_Minor_ID) VALUES (7,5,2);

，您将看到此错误

架构创建失败：无法添加或更新子行：外部
键约束失败（ databaseName 。 major_class_br ，约束
cc_fk1 外键（ Class_ID ）参考 class （ ID ））：

Schema Creation Failed: Cannot add or update a child row: a foreign key constraint fails (databaseName.major_class_br, CONSTRAINT cc_fk1 FOREIGN KEY (Class_ID) REFERENCES class (ID)):

• 请参见SQLFiddle演示



这篇关于填充MySQL数据库中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！