Categories: データベースプログラム、インフラ

Mysql 多対多のテーブルの操作方法　中間テーブルを使う

本日は、MySQLにおいて、2つのテーブルが多対多の関係の場合にどのようにデータをSelectするかについて解説します。
多対多の関係とういうのは、1つのテーブルの中に存在する各行が、もう1つのテーブルの中の複数の行に対応することを意味します。

例えば、顧客テーブルと商品テーブルがあった場合、ある顧客が複数の商品を購入することができ、
同じ商品は、複数の顧客によって購入されることもあります。
このような場合、顧客と商品のテーブルは多対多の関係にあります。

多対多の関係を解決するために、中間テーブルというテーブルを作成するのが一般的です。
中間テーブルとはいわゆる俗名で名前は何でも構いません。
今回名前を付けるなら売り上げ履歴テーブルがよいかと思います。

この中間テーブルには、顧客と商品のテーブルのIDが含まれます。
このように、中間テーブルの行には、顧客IDと商品IDを記録されます。
売り上げの場合だとある顧客(顧客ID)が購入した商品のIDが含まれます。

多対多　中間テーブルのテーブル構造
実践編　テーブルを作ってみる
ダミーデータの挿入
多対多　中間テーブルのSelect文
多対多　Gorup byのSelect文
まとめ

多対多　中間テーブルのテーブル構造

実際にテーブルを作る場合は下記のようになります。

顧客テーブル: customer

customer_id (主キー)
customer_name
…

商品テーブル: product

product_id (主キー)
product_name
…

中間テーブル: customer_product

customer_id (外部キー)
product_id (外部キー)
purchase_date
…

このようにすることで、顧客と商品の多対多の関係を効率的に管理することができます。
また、中間テーブルには、購入日などの他の情報を含めることができます。

実践編　テーブルを作ってみる

MySQLを使い実際にテーブルを作って、データを入れてSelect分をいくつか実行してみます。

最初にテーブルを作成します。
作成するのは、商品マスタテーブル、顧客マスタテーブル、売上履歴テーブル（中間テーブル）です。

下記を実行します。まず、商品テーブルを作成します。

CREATE TABLE product_master (
id int(11) NOT NULL AUTO_INCREMENT, -- 商品ID
name varchar(255) NOT NULL, -- 商品名
price int(11) NOT NULL, -- 商品価格
stock_quantity int(11) NOT NULL, -- 在庫数
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

次に顧客テーブルです。

CREATE TABLE customer_master (
id int(11) NOT NULL AUTO_INCREMENT, -- 顧客ID
name varchar(255) NOT NULL, -- 顧客名
email varchar(255) NOT NULL, -- メールアドレス
phone varchar(255) NOT NULL, -- 電話番号
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

最後に　売上履歴テーブル（中間テーブル）です。

CREATE TABLE sales_history (
id int(11) NOT NULL AUTO_INCREMENT, -- 売上ID
product_id int(11) NOT NULL, -- 商品ID
customer_id int(11) NOT NULL, -- 顧客ID
quantity int(11) NOT NULL, -- 売上数量
price int(11) NOT NULL, -- 売上価格
created_at datetime NOT NULL, -- 売上日時
PRIMARY KEY (id),
FOREIGN KEY (product_id) REFERENCES product_master (id) ON DELETE CASCADE,
FOREIGN KEY (customer_id) REFERENCES customer_master (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

商品マスタテーブルでは、商品ID、商品名、商品価格、在庫数のカラムを持ち、商品IDを主キーとします。

顧客マスタテーブルでは、顧客ID、顧客名、メールアドレス、電話番号のカラムを持ち、顧客IDを主キーとします。

売上履歴テーブルでは、売上ID、商品ID、顧客ID、売上数量、売上価格、売上日時のカラムを持ち、売上IDを主キーとします。
また、商品IDと顧客IDのカラムには、それぞれ商品マスタテーブルと顧客マスタテーブルのIDカラムを外部キーとして設定し、参照整合性を保ちます。

ダミーデータの挿入

商品マスタテーブルへのダミーデータのインサートします。

INSERT INTO product_master (name, price, stock_quantity)
VALUES
('商品A', 1000, 50),
('商品B', 2000, 30),
('商品C', 3000, 20),
('商品D', 4000, 10),
('商品E', 5000, 5),
('商品F', 6000, 0);

顧客マスタテーブルへのダミーデータのインサート

INSERT INTO customer_master (name, email, phone)
VALUES
('顧客A', 'customer_a@example.com', '000-111-222'),
('顧客B', 'customer_b@example.com', '000-222-333'),
('顧客C', 'customer_c@example.com', '000-333-444'),
('顧客D', 'customer_d@example.com', '000-444-555'),
('顧客E', 'customer_e@example.com', '000-555-666'),
('顧客F', 'customer_f@example.com', '000-666-777');

売上履歴テーブルへのダミーデータのインサート

INSERT INTO sales_history (customer_id, product_id, quantity, total_price, sales_date)
VALUES
(1, 1, 3, 3000, '2022-01-01'),
(2, 2, 2, 4000, '2022-01-02'),
(3, 3, 1, 3000, '2022-01-03'),
(4, 4, 5, 20000, '2022-01-04'),
(5, 5, 1, 5000, '2022-01-05'),
(6, 6, 0, 0, '2022-01-06'),
(1, 2, 2, 14000, '2022-01-07'),
(2, 2, 3, 24000, '2022-01-08'),
(3, 1, 1, 9000, '2022-01-09'),
(5, 1, 4, 40000, '2022-01-10'),
(5, 3, 3, 33000, '2022-01-11'),
(6, 1, 0, 0, '2022-01-12'),
(6, 6, 2, 26000, '2022-01-13'),
(1, 6, 1, 14000, '2022-01-14'),
(2, 4, 4, 60000, '2022-01-15'),
(2, 5, 0, 0, '2022-01-16'),
(3, 4, 3, 51000, '2022-01-17'),
(4, 4, 2, 36000, '2022-01-18'),
(4, 3, 1, 19000, '2022-01-19'),
(4, 5, 5, 100000, '2022-01-20');

多対多　中間テーブルのSelect文

売り上げテーブルに顧客テーブルと商品テーブルを結合し、売上履歴テーブルの全件をSELECTし、顧客名と商品名を表示するSQL文です。
中間テーブルだけselectした場合は、当然顧客コードと商品コードしかわからないため
各コードの名前が何になるかをコードから結合したテーブルを参照し、名前を表示します。


SELECT
    c.`name` AS `customer_name`,
    p.`name` AS `product_name`,
    s.`quantity`,
    s.`total_price`,
    s.`sales_date`
FROM
    `sales_history` s
JOIN `customers` c ON s.`customer_id` = c.`id`
JOIN `products` p ON s.`product_id` = p.`id`;

このSQL文では、sales_historyテーブル、customersテーブル、productsテーブルの3つのテーブルを結合しています。

最初のJOIN句は、sales_historyテーブルとcustomersテーブルを結合しています。
ON句によって結合条件が指定されており、sales_historyテーブルのcustomer_id列とcustomersテーブルのid列が等しい場合に結合されます。

次のJOIN句は、先ほどの結果にproductsテーブルを追加しています。
同様に、ON句によって結合条件が指定されており、sales_historyテーブルのproduct_id列とproductsテーブルのid列が等しい場合に結合されます。

最後に、SELECT句によって、各テーブルから取得したい列を指定しています。AS句を使用して列名を別名に変更しています。
これによって、3つのテーブルを結合して、sales_historyテーブルの全件を取得しています。
JOINを使用することで、関連する複数のテーブルから必要な情報を取得することができます。

多対多　Gorup byのSelect文

このSQL文では、sales_historyテーブルとproductsテーブルを結合し、1月の売り上げ全体を商品別に出力します。Group byという構文を使います。

WHERE句によって、売り上げ日が1月である条件を指定しています。MONTH()関数を使用して、sales_date列から月を取得しています。
GROUP BY句によって、商品IDごとに集計するように指定しています。
SUM()関数を使用して、売り上げ数量と売り上げ金額をそれぞれ合計しています。
AS句を使用して、列名を別名に変更しています。

SELECT
    p.`name` AS `product_name`,
    SUM(s.`quantity`) AS `total_quantity`,
    SUM(s.`total_price`) AS `total_sales`
FROM
    `sales_history` s
JOIN `products` p ON s.`product_id` = p.`id`
WHERE
    MONTH(s.`sales_date`) = 1
GROUP BY
    s.`product_id`;