mysql - 通过循环遍历 MySQL 中特定表中的 ROWS 创建多个表

Question

如何在 MySQL 中使用循环遍历 Subject 表并使用 Math、Econs 为每个主题创建一个表，如下所示，化学为例。引入 php 仅作为最后的手段。

表:主题

|主题 |单位|工作人员 |

|数学 | 3 |詹姆斯先生|
|经济学 | 1 |史密斯博士|
|化学| 2 |艾莎夫人 |

表格:数学

学生 |测试1 |测试2 |考试 |

101 | 10 | 10 20 | 30|105 | 105 11 | 11 09| 45 |

表:经济

学生 |测试1 |测试2 |考试 |

101 | 10 | 10 20 | 30|105 | 105 11 | 11 09| 45 |

表格:CHEM

学生 |测试1 |测试2 |考试 |

101 | 10 | 10 20 | 30|105 | 105 11 | 11 09| 45 |

Answer 1

设计数据库时，您首先需要确定支持您的目标所需的所有实体。就您而言，您需要跟踪学生以及他们在各种类(class)/科目中的成绩。

据此我们可以识别以下实体:

1. 数学、化学等科目
2. 参加这些科目测试和考试的学生。
3. 负责教授科目和准备考试/测试的教职员工。
4. 不同类型的测试，例如类测试、考试等。
5. 学生在特定科目的特定类型测试中的成绩。

因此，基于此(相当简化的场景，我们只需要 5 个表。第二件事是确定实体之间的关系。让我们回顾一下它们。

1. 一个科目只能由一名工作人员演示，但一名工作人员可以演示多个科目。例如，数学仅由琼斯先生教授，但琼斯先生教授数学和科学。这就是我们所说的一对多关系。
2. 一个学生可以在多个学科上取得多个成绩，一个学科可以有多个学生取得多个成绩。例如，学生 1 参加了一次数学考试，但有两个不同的结果。数学科目可能有 100 名学生，每个学生都记录了一个或多个结果。这是多对多的关系。
3. 学生的每项成绩都属于某种类型的考试或测试。因此这里存在一对多的关系。只有一种考试类型，但数学、科学、地理和历史都使用这种考试类型。

因此，基于所有这些创建了一个小型演示数据库。我会带你经历它。

这是结构的直观表示:

这是数据库的代码。

CREATE SCHEMA IF NOT EXISTS `coursedb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `coursedb` ;

-- -----------------------------------------------------
-- Table `coursedb`.`staff`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `coursedb`.`staff` (
 `staff_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `staff_name` VARCHAR(64) NOT NULL,
 `staff_surname` VARCHAR(64) NOT NULL,
 `staff_title` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`staff_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `coursedb`.`subject`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `coursedb`.`subject` (
`subject_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`subject_name` VARCHAR(64) NULL,
`staff_ID` INT UNSIGNED NOT NULL,
PRIMARY KEY (`subject_ID`),
INDEX `fk_subject_staff_idx` (`staff_ID` ASC),
CONSTRAINT `fk_subject_staff`
  FOREIGN KEY (`staff_ID`)
  REFERENCES `coursedb`.`staff` (`staff_ID`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `coursedb`.`student`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `coursedb`.`student` (
  `student_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_name` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`student_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `coursedb`.`subject_result_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `coursedb`.`subject_result_type` (
  `subject_result_type_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `subject_result_type_name` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`subject_result_type_ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `coursedb`.`subject_student_result`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `coursedb`.`subject_student_result` (
  `subject_ID` INT UNSIGNED NOT NULL,
  `student_ID` INT UNSIGNED NOT NULL,
  `subject_student_result_date` DATE NOT NULL,
  `subject_student_result_score` INT NOT NULL,
  `subject_result_type_ID` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`subject_ID`, `student_ID`, `subject_student_result_date`),
  INDEX `fk_subject_student_result_subject1_idx` (`subject_ID` ASC),
  INDEX `fk_subject_student_result_student1_idx` (`student_ID` ASC),
  INDEX `fk_subject_student_result_subject_result_type1_idx`  (`subject_result_type_ID` ASC),
  CONSTRAINT `fk_subject_student_result_subject1`
    FOREIGN KEY (`subject_ID`)
    REFERENCES `coursedb`.`subject` (`subject_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_subject_student_result_student1`
    FOREIGN KEY (`student_ID`)
    REFERENCES `coursedb`.`student` (`student_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_subject_student_result_subject_result_type1`
    FOREIGN KEY (`subject_result_type_ID`)
    REFERENCES `coursedb`.`subject_result_type` (`subject_result_type_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
     CREATE TABLE IF NOT EXISTS `coursedb`.`subject` (
       `subject_ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
       `subject_name` VARCHAR(64) NULL,
       `staff_ID` INT UNSIGNED NOT NULL,
       PRIMARY KEY (`subject_ID`),
       INDEX `fk_subject_staff_idx` (`staff_ID` ASC),
       CONSTRAINT `fk_subject_staff`
         FOREIGN KEY (`staff_ID`)
         REFERENCES `coursedb`.`staff` (`staff_ID`)
         ON DELETE NO ACTION
         ON UPDATE NO ACTION)
     ENGINE = InnoDB;

让我们开始添加一些数据，以便您可以了解这一切是如何组合在一起的。首先让我们添加两名工作人员琼斯先生和脾气暴躁小姐。

执行以下代码:

INSERT INTO `coursedb`.`staff`
(
  `staff_name`,
  `staff_surname`,
  `staff_title` 
)
SELECT 'James', 'Jones','Mr'
UNION 
SELECT 'Pot','Grumpy','Miss';

这将插入两名工作人员。如果您通过执行 SELECT 语句查看新插入的数据，您将发现以下数据:

 staff_ID staff_name  staff_surname  staff_title
 '1',     'James',    'Jones',       'Mr'
 '2',     'Pot',      'Grumpy',      'Miss'

请注意数据库是如何为这些员工分配 ID 号的？这是员工的主键，我们用它来标识员工表中的行。我暂时将其保留为自动编号，但这里有几个选项。

我们现在需要创建这两名员工教授的类(class)。琼斯先生教数学和科学，格鲁普小姐教艺术。让我们创建主题并将它们链接到工作人员。这可以通过以下代码来完成:

INSERT INTO `coursedb`.`subject`
(
    `subject_name`,
    `staff_ID`
)
VALUES
(
    'Math',
     1
);


INSERT INTO `coursedb`.`subject`
(
    `subject_name`,
    `staff_ID`
)
VALUES
(
     'Science',
      1
);


INSERT INTO `coursedb`.`subject`
(
    `subject_name`,
    `staff_ID`
)
VALUES
(
  'Art',
   2
);

您现在可以通过执行以下查询来查看琼斯先生教授哪些科目:

SELECT *
FROM   staff stf
INNER JOIN `subject` sub
      ON stf.staff_ID = sub.staff_ID
WHERE stf.staff_ID =1;

因此，在我们能够跟踪学生在类(class)中的表现之前，我们缺少两条信息。结果的类型，即考试或测试，当然还有一些学生。所以让我们添加它们。

INSERT INTO `coursedb`.`student`
(
   `student_name`
)
VALUES
(
    'Student 1'
);


INSERT INTO `coursedb`.`subject_result_type`
(
  `subject_result_type_name`
)
SELECT 'Test'
UNION 
SELECT 'Exam'

有了这个，让我们记录一下学生 1 的一些结果。学生 1 参加了 2 项考试，其中一项是科学，一项是数学，学生 1 还做了一项艺术测试。以下 SQL 将插入此数据:

 INSERT INTO `coursedb`.`subject_student_result`
 (`subject_ID`,
  `student_ID`,
  `subject_student_result_date`,
  `subject_student_result_score`,
  `subject_result_type_ID`
  )
  VALUES
  (
    1,
    1,
    CURDATE(),
    80,
    2
 );

 INSERT INTO `coursedb`.`subject_student_result`
 ( `subject_ID`,
   `student_ID`,
   `subject_student_result_date`,
   `subject_student_result_score`,
   `subject_result_type_ID`
 )
 VALUES
 (
    2,
    1,
    CURDATE(),
    60,
    2
 );
INSERT INTO `coursedb`.`subject_student_result`
(  `subject_ID`,
   `student_ID`,
   `subject_student_result_date`,
   `subject_student_result_score`,
   `subject_result_type_ID`
 )
 VALUES
 (
    3,
    1,
    CURDATE(),
    80,
    1
  );

您现在可以通过执行以下查询来绘制有关学生及其结果的报告:

 SELECT *
 FROM subject_student_result ssr
 INNER JOIN student std
       ON ssr.student_ID = std.student_ID
 INNER JOIN `subject` sub
       ON ssr.subject_ID = sub.subject_ID
 INNER JOIN subject_result_type srt
       ON ssr.subject_result_type_ID = srt.subject_result_type_ID
 INNER JOIN staff stf
       ON sub.staff_ID = stf.staff_ID

我建议您研究一下这个模型，并真正理解我向您展示的内容。它将使您的设计更简单、更清晰，并且维护更少。