mysql - 获取与输入数据难以关联的数据，SQL 查询

Question

首先，想问你(这样我的问题对你来说会更清楚)是否有一个在线数据库图表工具(？)，我可以在其中输入我的sql代码，它会绘制图表(带有表格及其关系)对我来说。

我找到了this question并尝试了一些工具，但大多数只能创建表和它们之间的关系，但不允许导入我的sql。

(2)我需要将所有类(class)与老师联系起来。有两种类型的联系:老师可能是类主任，或者老师可能教授这门课。如何获取与教师连接的所有类(class)。

(3) 还有 1 个附加问题:我的限制是否正确？

SQL 方案 (scheme on sqlfiddle.com):

CREATE TABLE `Subject` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
);

CREATE TABLE `Teacher` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `firstname` VARCHAR(50) NOT NULL,
    `midname` VARCHAR(50) NOT NULL,
    `lastname` VARCHAR(50) NOT NULL,
    `address` VARCHAR(100) NOT NULL,
    `birthdate` DATE NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE `Class` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `digit` INT(11) NOT NULL,
    `char` VARCHAR(50) NOT NULL,
    `teacher_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK_Class_Teacher` (`teacher_id`),
    CONSTRAINT `FK_Class_Teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`)
);

CREATE TABLE `ClassVsSubject` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `subject_id` INT(11) NOT NULL,
    `class_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `subject_id_class_id` (`subject_id`, `class_id`),
    INDEX `FK_ClassVsSubject_Class` (`class_id`),
    CONSTRAINT `FK_ClassVsSubject_Class` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`),
    CONSTRAINT `FK_ClassVsSubject_Subject` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`)
);
CREATE TABLE `TeacherVsSubject` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `teacher_id` INT(11) NOT NULL,
    `subject_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK_TeacherVsSubject_Teacher` (`teacher_id`),
    INDEX `FK_TeacherVsSubject_Subject` (`subject_id`),
    CONSTRAINT `FK_TeacherVsSubject_Teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`),
    CONSTRAINT `FK_TeacherVsSubject_Subject` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`)
);

Answer 1

对于第二个问题，请使用以下内容:

select *
  from Class
 where teacher_id = 1

union

select Class.*
  from Class
       join ClassVsSubject on Class.id = ClassVsSubject.class_id
       join TeacherVsSubject on ClassVsSubject.subject_id = TeacherVsSubject.subject_id
 where TeacherVsSubject.teacher_id = 1

由于您没有说明哪个老师是起点，所以我使用了 id 1 的老师。

请注意，union 会删除重复的条目，无需单独处理。

对于您的数据模型(问题 3)，我认为一个问题是您需要在 subject 表中为不同的类(class)和同一主题(例如“数学”或“英语”)提供不同的条目，否则，连接将不起作用:假设主题“English”的 id 为 1。现在，如果 id 为 1 的类(class)和 id 为 2 的类(class)都具有该主题，则 ClassVsSubject 中将包含以下数据:

class_id   subject_id
       1            1
       2            1

如何将 id 为 5 的老师在 1 类教英语，但不在 2 类教英语？你不能!如果您输入 subject_id 1 和 Teacher_id 5 的记录，则这将适用于所有类(class)。

为了解决这个问题，您可以从两个 ClassVsSubject 和 TeacherVsSubject 中创建一个表。这将是这样的:

CREATE TABLE `ClassVsSubjectVsTeacher` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `subject_id` INT(11) NOT NULL,
    `class_id` INT(11) NOT NULL,
    `teacher_id` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `subject_id_class_id` (`subject_id`, `class_id`, `teacher_id`),
    INDEX `FK_ClassVsSubjectVsTeacher_Class` (`class_id`),
    INDEX `FK_ClassVsSubjectVsTeacher_Teacher` (`teacher_id`),
    INDEX `FK_ClassVsSubjectVsTeacher_Subject` (`subject_id`),
    CONSTRAINT `FK_ClassVsSubjectVsTeacher_Class` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`),
    CONSTRAINT `FK_ClassVsSubjectVsTeacher_Teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`),
    CONSTRAINT `FK_ClassVsSubjectVsTeacher_Subject` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`)
);

对于数据模型，您不需要此处的 id 列，主键可以是所有三个外键的组合键。但是 - 根据客户端软件 - 拥有它可能会很有用。例如一些 OR 映射工具需要单列主键。