仕事では 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
コメント