忍者ブログ

VB.NET-TIPS などプログラミングについて

VB.NETのTIPS(小技集)を中心に、Javascript、PHP その他のプログラミングについて少し役に立つ情報を発信します。いわゆる個人的な忘備録ですが、みなさんのお役に立てれば幸いです。

  • 2025/01/18
[PR]
×

[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。

  • CM
  • 2024/01/29
MySQL マスタテーブルの作成と参照に関する考察について(CREATE TABLE、AUTOINCREMENT、JOIN、LEFT JOIN)

仕事では MySQL を使用したシステムに携わることが多く、Web系の仕事の場合にはほぼ100％に近い感じです。

MySQL は Oracle社 が開発したもので現在もメンテナンスがなされている様です。 また、Yahoo や Google などのシステムにも採用されていることからも、安心感もあります。

MySQL について20本ぐらいの記事を書いてきましたが、関数やSELECTなどについて個別の事例に付いてが多かったですが、 今回は少し毛色を変えて、初心者の方がテーブルの考え方で少参考になりそうな内容を記してみます。

以下の内容で、順を追って説明します。


■テストテーブル（商品マスタ、売上データ）の作成について

いろんなWeb上のシステムがありますが、データ処理を行う時に、意味合いがまとまった情報を1個のデータ集合として考えます。 例えば、「個人情報」「会社情報」「商品情報」「売上データ」「売上統計データ」などが在ると思います。

それぞれを別のテーブルとして情報を保存しておきますが、 このなかで「個人情報」「会社情報」「商品情報」など1回データを作成すればあまり変化がない情報をマスタデータとして それぞれ別々のテーブルとして持つことが一般的です。 （あまり変化がないと言いましたが、それぞれの情報は更新できる必要があります。この作業をマスタメンテナンスと言います。）

ここでは、「商品情報」と「売上データ」に関してのみを例にして説明したいと思います。
「商品情報」（商品マスタと言い換えます）は以下の様な項目が必要だと思います。

  • 商品コード（商品の型番？）
  • 商品名
  • 単価


「売上データ」は以下の様な項目が必要だと思います。 ここでは、誰が買ったかなどの情報は割愛して考えますので、商品と売上のみの情報の項目のみとしています。 （後、消費税についても割愛します。）

  • 売上NO
  • 商品コード
  • 日付
  • 数量
  • 単価（単価の変更があるため）
  • 金額

上記の項目でテーブル作成SQLを考えますが、商品マスタの場合は「商品コード」を主キーにしてもいいのですが、 AUTO_INCREMENT な「id」を追加してこちらを主キーとします。
「商品コード」とマスタの識別子となる「id」を分けた方が、後々なにかと便利になるといいますか、 MySQL の作法的に私は「id」を付加しています。 （テーブルカラムの指定で AUTO_INCREMENT があるので気軽に連番のデータが付加できるとも言えますが...）

なお、「商品マスタ」「売上データ」ともに削除フラグ（デフォルトで「0」設定）を持たせました。 それぞれのデータの削除処理の時にフラグを「1」に更新する様に考えています。 こうすれば廃品種となったマスタのデータを残しておくことができます。

作成の SQL は以下の様になります。 （「OR REPLACE」の指定は置き換えを行います）

-- 商品マスタ
CREATE OR REPLACE TABLE tm_prod (
	id    INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    code  VARCHAR(32),	-- 商品コード
    name  VARCHAR(32),	-- 商品名
    price INT,          -- 単価
	del	  INT DEFAULT 0 -- 削除フラグ:0(存在),1:(削除)
);

-- 売上データ
CREATE OR REPLACE TABLE tt_sales (
    no        INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 売上NO
    id_prd    INT,      -- 商品マスタ.id
    salesdate DATE,	    -- 日付
    volume    INT,      -- 数量
    price     INT,      -- 単価
    amount    INT,      -- 金額
	del	  INT DEFAULT 0 -- 削除フラグ:0(存在),1:(削除)
);

それでは、上記のテーブルに以下の SQL でテストデータを登録します。

-- 商品テーブル
INSERT INTO tm_prod(code, name, price) VALUES
    ('pen01', '鉛筆HB', 80), 
    ('pen02', '鉛筆B' , 100),
    ('pen03', '鉛筆B2' , 100),
    ('cre01', 'くれよん12色', 1000),
    ('cre02', 'くれよん24色', 2000),
    ('pen10', '蛍光ペン', 120),
    ('pen11', '蛍光ペン2', 130),
    ('era01', '消しゴム', 150),
    ('era02', '消しゴム2', 200);
-- 売上件数テーブル
INSERT INTO tt_sales(id_prd, salesdate, volume, price, amount) VALUES
    (1, '2024-01-04', 10,   80,  800), 
    (4, '2024-01-04',  1, 1000, 1000), 
    (6, '2024-01-04',  2,  120,  240), 
    (7, '2024-01-05',  3,  130,  390), 
    (1, '2024-01-07', 15,   80, 1200), 
    (2, '2024-01-07', 10,  100, 1000), 
    (5, '2024-01-07',  2, 2000, 4000), 
    (1, '2024-01-05', 20,   80, 1600), 
    (3, '2024-01-05',  1,  100,  100), 
    (4, '2024-01-10',  2, 1000, 2000), 
    (2, '2024-01-15',  2,  100,  200), 
    (6, '2024-01-05',  5,  120,  600), 
    (7, '2024-01-14',  3,  130,  390), 
    (8, '2024-01-20',  1,  150,  150), 
    (5, '2024-01-20',  4, 2000, 8000), 
    (8, '2024-01-22', 10,  150, 1500), 
    (9, '2024-01-22', 10,  200, 2000);

テストデータを確認します。

MariaDB [test]> select * from tm_prod;
+----+-------+--------------+-------+------+
| id | code  | name         | price | del  |
+----+-------+--------------+-------+------+
| 19 | pen01 | 鉛筆HB       |    80 |    0 |
| 20 | pen02 | 鉛筆B        |   100 |    0 |
| 21 | pen03 | 鉛筆B2       |   100 |    0 |
| 22 | cre01 | くれよん12色 |  1000 |    0 |
| 23 | cre02 | くれよん24色 |  2000 |    0 |
| 24 | pen10 | 蛍光ペン     |   120 |    0 |
| 25 | pen11 | 蛍光ペン2    |   130 |    0 |
| 26 | era01 | 消しゴム     |   150 |    0 |
| 27 | era02 | 消しゴム2    |   200 |    0 |
+----+-------+--------------+-------+------+
9 rows in set (0.00 sec)

MariaDB [test]> select * from tt_sales;
+----+--------+------------+--------+-------+--------+------+
| no | id_prd | salesdate  | volume | price | amount | del  |
+----+--------+------------+--------+-------+--------+------+
|  1 |      1 | 2024-01-04 |     10 |    80 |    800 |    0 |
|  2 |      4 | 2024-01-04 |      1 |  1000 |   1000 |    0 |
|  3 |      6 | 2024-01-04 |      2 |   120 |    240 |    0 |
|  4 |      7 | 2024-01-05 |      3 |   130 |    390 |    0 |
|  5 |      1 | 2024-01-07 |     15 |    80 |   1200 |    0 |
|  6 |      2 | 2024-01-07 |     10 |   100 |   1000 |    0 |
|  7 |      5 | 2024-01-07 |      2 |  2000 |   4000 |    0 |
|  8 |      1 | 2024-01-05 |     20 |    80 |   1600 |    0 |
|  9 |      3 | 2024-01-05 |      1 |   100 |    100 |    0 |
| 10 |      4 | 2024-01-10 |      2 |  1000 |   2000 |    0 |
| 11 |      2 | 2024-01-15 |      2 |   100 |    200 |    0 |
| 12 |      6 | 2024-01-05 |      5 |   120 |    600 |    0 |
| 13 |      7 | 2024-01-14 |      3 |   130 |    390 |    0 |
| 14 |      8 | 2024-01-20 |      1 |   150 |    150 |    0 |
| 15 |      5 | 2024-01-20 |      4 |  2000 |   8000 |    0 |
| 16 |      8 | 2024-01-22 |     10 |   150 |   1500 |    0 |
| 17 |      9 | 2024-01-22 |     10 |   200 |   2000 |    0 |
+----+--------+------------+--------+-------+--------+------+
17 rows in set (0.00 sec)



■売上データの一覧リストの取得について

それでは商品マスタから「名称」「商品コード」を取得する様にした、売上データの一覧をリストする SQL を示します。
「売上データ」の「商品id」と「商品マスタ」の「商品id」を INNER JOIN にて結合しています。
さらに、結合で「商品マスタ」の「削除フラグ」が「0:未削除」の条件付けを行っています。

SELECT 
    tt_sales.no AS '売上no',
    tt_sales.id_prd AS '商品id',
    tm_prod.code AS '商品コード',
    tm_prod.name AS '商品名',
    tt_sales.volume AS '数量',
    tt_sales.price AS '単価',
    tt_sales.amount AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
ORDER BY tt_sales.no;

この SQL の結果は以下の通りです。

+--------+--------+------------+--------------+------+------+------+
| 売上no | 商品id | 商品コード | 商品名       | 数量 | 単価 | 金額 |
+--------+--------+------------+--------------+------+------+------+
|      1 |      1 | pen01      | 鉛筆HB       |   10 |   80 |  800 |
|      2 |      4 | cre01      | くれよん12色 |    1 | 1000 | 1000 |
|      3 |      6 | pen10      | 蛍光ペン     |    2 |  120 |  240 |
|      4 |      7 | pen11      | 蛍光ペン2    |    3 |  130 |  390 |
|      5 |      1 | pen01      | 鉛筆HB       |   15 |   80 | 1200 |
|      6 |      2 | pen02      | 鉛筆B        |   10 |  100 | 1000 |
|      7 |      5 | cre02      | くれよん24色 |    2 | 2000 | 4000 |
|      8 |      1 | pen01      | 鉛筆HB       |   20 |   80 | 1600 |
|      9 |      3 | pen03      | 鉛筆B2       |    1 |  100 |  100 |
|     10 |      4 | cre01      | くれよん12色 |    2 | 1000 | 2000 |
|     11 |      2 | pen02      | 鉛筆B        |    2 |  100 |  200 |
|     12 |      6 | pen10      | 蛍光ペン     |    5 |  120 |  600 |
|     13 |      7 | pen11      | 蛍光ペン2    |    3 |  130 |  390 |
|     14 |      8 | era01      | 消しゴム     |    1 |  150 |  150 |
|     15 |      5 | cre02      | くれよん24色 |    4 | 2000 | 8000 |
|     16 |      8 | era01      | 消しゴム     |   10 |  150 | 1500 |
|     17 |      9 | era02      | 消しゴム2    |   10 |  200 | 2000 |
+--------+--------+------------+--------------+------+------+------+
17 rows in set (0.00 sec)


「商品マスタ」との結合ですが、「削除フラグ:0」のデータのみを取得する SELECT文 をサブクエリのようにすれば、 以下の SQL でも同様のことができます。

SELECT 
    tt_sales.no AS '売上no',
    tt_sales.id_prd AS '商品id',
    TX.code AS '商品コード',
    TX.name AS '商品名',
    tt_sales.volume AS '数量',
    tt_sales.price AS '単価',
    tt_sales.amount AS '金額'
FROM tt_sales
    INNER JOIN (
        SELECT * 
        FROM tm_prod
        WHERE tm_prod.del = 0
    ) TX
    ON  TX.id = tt_sales.id_prd
ORDER BY tt_sales.no;


■商品マスタが削除済みの場合の売上データの一覧リストの取得について

削除済みの商品マスタを設定し、売上データの一覧リストでどの様に処理するのか説明します。

最初に、商品マスタ内に削除済みデータを更新するため以下の SQL を実行します。

UPDATE tm_prod SET del = 1
WHERE id IN(4, 7);

「id」が「4,7」のデータの削除フラグが「1」になることを確認します。

MariaDB [test]> select * from tm_prod;
+----+-------+--------------+-------+------+
| id | code  | name         | price | del  |
+----+-------+--------------+-------+------+
|  1 | pen01 | 鉛筆HB       |    80 |    0 |
|  2 | pen02 | 鉛筆B        |   100 |    0 |
|  3 | pen03 | 鉛筆B2       |   100 |    0 |
|  4 | cre01 | くれよん12色 |  1000 |    1 |
|  5 | cre02 | くれよん24色 |  2000 |    0 |
|  6 | pen10 | 蛍光ペン     |   120 |    0 |
|  7 | pen11 | 蛍光ペン2    |   130 |    1 |
|  8 | era01 | 消しゴム     |   150 |    0 |
|  9 | era02 | 消しゴム2    |   200 |    0 |
+----+-------+--------------+-------+------+
9 rows in set (0.00 sec)

この状況で、先ほどのどちらかの SQL で売上一覧を実行してみます。

MariaDB [test]> SELECT
    ->     tt_sales.no AS '売上no',
    ->     tt_sales.id_prd AS '商品id',
    ->     TX.code AS '商品コード',
    ->     TX.name AS '商品名',
    ->     tt_sales.volume AS '数量',
    ->     tt_sales.price AS '単価',
    ->     tt_sales.amount AS '金額'
    -> FROM tt_sales
    ->     INNER JOIN (
    ->         SELECT *
    ->         FROM tm_prod
    ->         WHERE tm_prod.del = 0
    ->     ) TX
    ->     ON  TX.id = tt_sales.id_prd
    -> ORDER BY tt_sales.no;
+--------+--------+------------+--------------+------+------+------+
| 売上no | 商品id | 商品コード | 商品名       | 数量 | 単価 | 金額 |
+--------+--------+------------+--------------+------+------+------+
|      1 |      1 | pen01      | 鉛筆HB       |   10 |   80 |  800 |
|      3 |      6 | pen10      | 蛍光ペン     |    2 |  120 |  240 |
|      5 |      1 | pen01      | 鉛筆HB       |   15 |   80 | 1200 |
|      6 |      2 | pen02      | 鉛筆B        |   10 |  100 | 1000 |
|      7 |      5 | cre02      | くれよん24色 |    2 | 2000 | 4000 |
|      8 |      1 | pen01      | 鉛筆HB       |   20 |   80 | 1600 |
|      9 |      3 | pen03      | 鉛筆B2       |    1 |  100 |  100 |
|     11 |      2 | pen02      | 鉛筆B        |    2 |  100 |  200 |
|     12 |      6 | pen10      | 蛍光ペン     |    5 |  120 |  600 |
|     14 |      8 | era01      | 消しゴム     |    1 |  150 |  150 |
|     15 |      5 | cre02      | くれよん24色 |    4 | 2000 | 8000 |
|     16 |      8 | era01      | 消しゴム     |   10 |  150 | 1500 |
|     17 |      9 | era02      | 消しゴム2    |   10 |  200 | 2000 |
+--------+--------+------------+--------------+------+------+------+
13 rows in set (0.00 sec)

確かに、商品マスタの「削除フラグ:1」のデータは一覧に表示されません。
一覧としてはこれで正解のですが、削除済みのものも見たいという要求があれば INNER JOIN を LEFT JOIN に変更し「売上データ」側には「商品id」が存在するが 「商品マスタ」側にはデータが削除されているものも表示する様にします。

MariaDB [test]> SELECT
    ->     tt_sales.no AS '売上no',
    ->     tt_sales.id_prd AS '商品id',
    ->     TX.code AS '商品コード',
    ->     TX.name AS '商品名',
    ->     tt_sales.volume AS '数量',
    ->     tt_sales.price AS '単価',
    ->     tt_sales.amount AS '金額'
    -> FROM tt_sales
    ->     LEFT JOIN (
    ->         SELECT *
    ->         FROM tm_prod
    ->         WHERE tm_prod.del = 0
    ->     ) TX
    ->     ON  TX.id = tt_sales.id_prd
    -> ORDER BY tt_sales.no;
+--------+--------+------------+--------------+------+------+------+
| 売上no | 商品id | 商品コード | 商品名       | 数量 | 単価 | 金額 |
+--------+--------+------------+--------------+------+------+------+
|      1 |      1 | pen01      | 鉛筆HB       |   10 |   80 |  800 |
|      2 |      4 | NULL       | NULL         |    1 | 1000 | 1000 |
|      3 |      6 | pen10      | 蛍光ペン     |    2 |  120 |  240 |
|      4 |      7 | NULL       | NULL         |    3 |  130 |  390 |
|      5 |      1 | pen01      | 鉛筆HB       |   15 |   80 | 1200 |
|      6 |      2 | pen02      | 鉛筆B        |   10 |  100 | 1000 |
|      7 |      5 | cre02      | くれよん24色 |    2 | 2000 | 4000 |
|      8 |      1 | pen01      | 鉛筆HB       |   20 |   80 | 1600 |
|      9 |      3 | pen03      | 鉛筆B2       |    1 |  100 |  100 |
|     10 |      4 | NULL       | NULL         |    2 | 1000 | 2000 |
|     11 |      2 | pen02      | 鉛筆B        |    2 |  100 |  200 |
|     12 |      6 | pen10      | 蛍光ペン     |    5 |  120 |  600 |
|     13 |      7 | NULL       | NULL         |    3 |  130 |  390 |
|     14 |      8 | era01      | 消しゴム     |    1 |  150 |  150 |
|     15 |      5 | cre02      | くれよん24色 |    4 | 2000 | 8000 |
|     16 |      8 | era01      | 消しゴム     |   10 |  150 | 1500 |
|     17 |      9 | era02      | 消しゴム2    |   10 |  200 | 2000 |
+--------+--------+------------+--------------+------+------+------+
17 rows in set (0.00 sec)

削除された「商品マスタ」の商品コード、商品名は NULL で表示されます。
NULL が気に入らないのであれば、以下の様にカラム表示の中を変えてやります。

MariaDB [test]> SELECT
    ->     tt_sales.no AS '売上no',
    ->     tt_sales.id_prd AS '商品id',
    ->     IFNULL(TX.code, '-----') AS '商品コード',
    ->     IFNULL(TX.name, '*削除済*') AS '商品名',
    ->     tt_sales.volume AS '数量',
    ->     tt_sales.price AS '単価',
    ->     tt_sales.amount AS '金額'
    -> FROM tt_sales
    ->     LEFT JOIN (
    ->         SELECT *
    ->         FROM tm_prod
    ->         WHERE tm_prod.del = 0
    ->     ) TX
    ->     ON  TX.id = tt_sales.id_prd
    -> ORDER BY tt_sales.no;
+--------+--------+------------+--------------+------+------+------+
| 売上no | 商品id | 商品コード | 商品名       | 数量 | 単価 | 金額 |
+--------+--------+------------+--------------+------+------+------+
|      1 |      1 | pen01      | 鉛筆HB       |   10 |   80 |  800 |
|      2 |      4 | -----      | *削除済*     |    1 | 1000 | 1000 |
|      3 |      6 | pen10      | 蛍光ペン     |    2 |  120 |  240 |
|      4 |      7 | -----      | *削除済*     |    3 |  130 |  390 |
|      5 |      1 | pen01      | 鉛筆HB       |   15 |   80 | 1200 |
|      6 |      2 | pen02      | 鉛筆B        |   10 |  100 | 1000 |
|      7 |      5 | cre02      | くれよん24色 |    2 | 2000 | 4000 |
|      8 |      1 | pen01      | 鉛筆HB       |   20 |   80 | 1600 |
|      9 |      3 | pen03      | 鉛筆B2       |    1 |  100 |  100 |
|     10 |      4 | -----      | *削除済*     |    2 | 1000 | 2000 |
|     11 |      2 | pen02      | 鉛筆B        |    2 |  100 |  200 |
|     12 |      6 | pen10      | 蛍光ペン     |    5 |  120 |  600 |
|     13 |      7 | -----      | *削除済*     |    3 |  130 |  390 |
|     14 |      8 | era01      | 消しゴム     |    1 |  150 |  150 |
|     15 |      5 | cre02      | くれよん24色 |    4 | 2000 | 8000 |
|     16 |      8 | era01      | 消しゴム     |   10 |  150 | 1500 |
|     17 |      9 | era02      | 消しゴム2    |   10 |  200 | 2000 |
+--------+--------+------------+--------------+------+------+------+
17 rows in set (0.00 sec)











PR
  • MySQL
  • 0CM

コメント

コメントを書く