使用多对多SQL关系和中间表

在复杂的数据库中工作时,可以使用中间表在两个表之间创建多对多(M:N)关系。在这样的模型中,表a中的每条记录都与表B中的零、一或多个条目实例相关。同样,对于表B中的每条记录,在表a中都存在零、一条或多个相关记录。

mysql

从这个角度来看。下面是一些多对多关系的例子。

在招聘平台数据库中,一个承包商可以为许多公司工作,每个不同的公司可以有许多承包商为他们工作。

在大学数据库中,一个学生可能注册多个课程,许多学生可能选择一个课程。

在多办公室/多商店购物车数据库中,一个产品可能在许多不同的办公室中出售,而一个办公室可能销售许多不同的产品。

在电影数据库中,一个明星可以出演多部电影,而许多演员可以出演一部电影。

在房地产数据库中,许多不同的租户可以占用一个公寓,而一个租户可以租用许多不同的公寓。

为了得到一个优化的数据库模式来为上述场景建模,我们必须使用一个中间表。在本文中,我们将为一个在线购物车创建一个示例数据库,并创建一个多对多关系。在这个示例数据库中,我们将建模一个场景,其中一个产品可能在不同的销售点出售,而一个办公室也可能销售不同的产品。

创建一个示例数据库

首先,SSH到我们的服务器并以root用户登录MySQL。

$ sudo mysql -u root -p

当出现提示时,输入MySQL服务器的root密码并按ENTER键继续。接下来,运行下面的命令来设置一个示例数据库。

mysql> CREATE DATABASE sample;

切换到sample数据库。

mysql> USE sample;

数据库就绪后,现在将继续为购物车创建基表。

创建并填充基表

在这一步中,我们将创建office和products表。“办公室”表将包含我们的业务所在的所有办公室的列表,而“产品”表将列出不同办公室中可供销售的所有项目。

请注意,并非所有产品都会在不同的办事处出售。在一个真实的例子中,我们的商店可能在不同的司法管辖区运营,在这些地方销售某些产品可能是不被允许的。另一个可能会迫使我们在某些商店禁用产品可用性的场景是物流问题,例如高昂的运输成本或缺少客户。

首先,创建offices表。在本文的后面,我们将看到如何使用中间表是管理不同办公室产品可用性的最佳选择。

mysql> CREATE TABLE offices       (           office_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,           office_name VARCHAR(50)       ) ENGINE = InnoDB;

在本文中,假设我们的公司有三个办公室。用以下记录填充表。

mysql> INSERT INTO offices(office_name) VALUES ('NEW YORK');mysql> INSERT INTO offices(office_name) VALUES ('LOS ANGELES');mysql> INSERT INTO offices(office_name) VALUES ('CHICAGO');

查询office表以确保数据已成功插入。

mysql> SELECT       office_id,       office_name       FROM offices;

确保我们得到了下面的列表

+-----------+-------------+| office_id | office_name |+-----------+-------------+|         1 | NEW YORK    ||         2 | LOS ANGELES ||         3 | CHICAGO     |+-----------+-------------+3 rows in set (0.00 sec)

接下来,创建products表。

mysql> CREATE TABLE products       (           product_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,           product_name VARCHAR(50),           retail_price DOUBLE       ) ENGINE = InnoDB;

用一些记录填充products表

mysql> INSERT INTO products(product_name, retail_price) VALUES ('WINTER COAT', '59.55');mysql> INSERT INTO products(product_name, retail_price) VALUES ('SMART WATCH', '199.45');mysql> INSERT INTO products(product_name, retail_price) VALUES ('UNIVERSAL REMOTE CONTROL', '9.95');mysql> INSERT INTO products(product_name, retail_price) VALUES ('METAL CASE FLASK', '12.75');

查询products表以确保所有项目都已成功插入。

mysql> SELECT       product_id,       product_name,       retail_price       FROM products;

确保所有的产品显示如下所示。

+------------+--------------------------+--------------+| product_id | product_name             | retail_price |+------------+--------------------------+--------------+|          1 | WINTER COAT              |        59.55 ||          2 | SMART WATCH              |       199.45 ||          3 | UNIVERSAL REMOTE CONTROL |         9.95 ||          4 | METAL CASE FLASK         |        12.75 |+------------+--------------------------+--------------+4 rows in set (0.00 sec)

定义和填充基表之后,下一步是设置一个中间表来链接它们。

创建并填充一个中间表

在这一步中,我们将创建一个中间表,以在办公室和产品表之间建立多对多关系。

在创建中间表时,一般的经验法则是将需要链接的两个表的名称连接起来,并用介词to和下划线字符(_)分隔,从而创建一个名称。

在介绍中突出显示的不同示例中,有一些很棒的名称,在创建各自的中间表时可以使用它们。

  • 招聘平台数据库:contractors_to_companies表
  • 学院数据库:students_to_courses表
  • 多商店购物车:products_to_stores表
  • 电影数据库:actors_to_movies表
  • 房地产数据库:tenants_to_apartments表。

由于在本文中使用的是购物车示例,所以请创建一个products_to_offices中间表。

mysql> CREATE TABLE products_to_offices       (           reference_id  BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,           product_id BIGINT NOT NULL,           office_id INT NOT NULL,           INDEX (product_id),           INDEX (office_id)       ) ENGINE = InnoDB;

接下来,填充products_to_offices表。在执行此操作之前,请重新查看产品目录并查看全球可用的待售商品。

  1. WINTER COAT
  2. SMART WATCH
  3. UNIVERSAL REMOTE CONTROL
  4. METAL CASE FLASK

然后,我们有三个办公室。

  1. NEW YORK
  2. LOS ANGELES
  3. CHICAGO

假设winter coat (product_id no 1)将在所有三个办事处出售。

mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('1', '1');mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('1', '2');mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('1', '3');

接下来,只通过运行下面的命令,在new york(office_id no 1)和chicago(office_id no 3)办公室使用samrt watch(product_id no 2)。

mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('2', '1');mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('2', '3');

然后,将UNIVERSAL REMOTE CONTROL (product_id no 3)仅与chicago办事处(office_id no 3)联系起来:

mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('3', '3');

然后将METAL CASE FLASK(product_id no 4)提供给new york (office_id no 1)和chicago (office_id no 3)办事处:

mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('4', '1');mysql> INSERT INTO products_to_offices(product_id, office_id) VALUES ('4', '3');

在运行上面的INSERT语句之后,我们现在已经成功地在数据库中建立了多对多关系。

查询products_to_offices表,查看如何保存关系。

mysql> SELECT       reference_id       product_id,       office_id       FROM products_to_offices;

输出

+------------+-----------+| product_id | office_id |+------------+-----------+|          1 |         1 ||          4 |         1 ||          7 |         1 ||          2 |         2 ||          3 |         3 ||          5 |         3 ||          6 |         3 ||          8 |         3 |+------------+-----------+8 rows in set (0.00 sec)

要查看每个办公室的产品可用性,请运行下面的命令。

new york(office_id = '1')

mysql> SELECT       products.product_id,       products.product_name,       retail_price       FROM products_to_offices       LEFT JOIN products       ON products_to_offices.product_id = products.product_id       WHERE products_to_offices.office_id = '1';

输出

+------------+------------------+--------------+| product_id | product_name     | retail_price |+------------+------------------+--------------+|          1 | WINTER COAT      |        59.55 ||          2 | SMART WATCH      |       199.45 ||          4 | METAL CASE FLASK |        12.75 |+------------+------------------+--------------+3 rows in set (0.00 sec)

LOS ANGELES (office_id = '2')

mysql> SELECT       products.product_id,       products.product_name,       retail_price       FROM products_to_offices       LEFT JOIN products       ON products_to_offices.product_id = products.product_id       WHERE products_to_offices.office_id = '2';

输出

+------------+--------------+--------------+

| product_id | product_name | retail_price |

+------------+--------------+--------------+

| 1 | WINTER COAT |        55 |

+------------+--------------+--------------+

1 row in set (0.00 sec)

CHICAGO (office_id = '3')

mysql> SELECT       products.product_id,       products.product_name,       retail_price       FROM products_to_offices       LEFT JOIN products       ON products_to_offices.product_id = products.product_id       WHERE products_to_offices.office_id = '3';

输出

+------------+--------------------------+--------------+| product_id | product_name             | retail_price |+------------+--------------------------+--------------+|          1 | WINTER COAT              |        59.55 ||          2 | SMART WATCH              |       199.45 ||          3 | UNIVERSAL REMOTE CONTROL |         9.95 ||          4 | METAL CASE FLASK         |        12.75 |+------------+--------------------------+--------------+4 rows in set (0.00 sec)

以上输出确认我们的多对多关系按预期工作。

在本文中,我们已经学习了如何在MySQL上使用中间表创建多对多关系。当设计一个需要M:N关系的数据库模式时,请始终使用本教程中的逻辑。

<<<<<<便宜稳定美国VPS推荐>>>>>>搬瓦工VPS年付49.99美元起

未经允许不得转载:便宜VPS网 » 使用多对多SQL关系和中间表