在MySQL查询中创建和桥接表 [英] Creating and Bridge tables in MySQL query

问题描述

我正在使用MySQL数据库的基本表设计.这个数据库项目是作为一个想法给出的，主要是出于教育目的.共有11个表，其中2个用于桥接.我试图正确设置主键和外键.我不确定如何编写查询来创建11个表并一次桥接它们.这里我已经附上了这些表格的直观图.

I am working with a basic table design for a MySQL database. This database project was given as an idea, mainly aimed for an educational purpose. There are a total of 11 tables which 2 are used for bridging purposes. I tried to properly set primary and foreign keys. I am not sure how to write the query that will create the 11 tables and bridge them all at once. HERE I have attached a visual diagram of these tables.

通过基础学习，我可以创建一个表，但不确定如何从此处进行扩展:

Through basic learning I can create one table but not sure how to advance from here:

CREATE TABLE Course (
          Course_ID INT,
          Course_Abbreviation VARCHAR(5),
      Course_Number INT,
      Section_Number INT,
      Professor_ID INT,
      Status VARCHAR(10)        
        ) TYPE=innodb;

推荐答案

您快要准备好了，您必须为表中的属性定义PRIMARY和FOREIGN键，例如，Students，Course和Course_Br_Students表的示例是:

You're almost there, you have to define PRIMARY and FOREIGN keys for the attributes within the tables, an example with the Students, Course, and Course_Br_Students table is:

CREATE TABLE Students(
PRIMARY KEY Student_ID SERIAL INTEGER,
Username VARCHAR(255),
First_name VARCHAR(255),
Email VARCHAR(255),
Phone number INTEGER,
Beginning_Date TIME,
Ending_Date TIME,
Max_hours INTEGER,
)

CREATE TABLE Student_Br_Course(
FOREIGN KEY Student_ID REFERENCES Students(Student_ID),
FOREIGN KEY Course_ID REFERENCES Courses(Course_ID),
Role VARCHAR(255),
Status BOOLEAN,
)

CREATE TABLE Courses(
PRIMARY KEY Course_ID INTEGER,
Course_Abbreviation VARCHAR(255),
Course_Number INTEGER,
Section_number INTEGER,
Professor_ID INTEGER,
)

这里是用于了解如何使用PRIMARY和FOREIGN键创建表的参考:

Here's a reference for understanding how to create tables with PRIMARY and FOREIGN keys:

http://www.w3schools.com/sql/sql_foreignkey.asp

这篇关于在MySQL查询中创建和桥接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！