MySQL | SQLite経験者向け、30日で習得するMySQL：実務応用 － Day29 総合課題

SQL MySQL
スポンサーリンク

Day29 後半のゴール

「“ちゃんと設計された小さなECバックエンド”をSQLレベルでイメージできるようにする」

前半で、ユーザー管理・商品管理・注文処理・売上集計の「役割」と「つながり」は描けました。
後半では、それをもう一歩進めて、実際のテーブル構成と代表的なクエリ、そして注文処理のトランザクションの流れまでを、初心者向けにかみ砕いて形にしていきます。

ここでのゴールは、次の状態です。
テーブル定義を見て「どのテーブルが何を表しているか」を説明できる。
注文処理の一連の流れを、トランザクションとして頭の中で再生できる。
売上集計の代表的なクエリを読んで、「何を集計しているか」が分かる。

コードは最小限にして、「設計とSQL」にフォーカスします。

全体のテーブル構成を一度“まとめて”見る

「users / products / orders / order_items を軸にする」

まずは、総合課題で使う中心テーブルを、ざっくりSQLで並べてみます。
細かい制約は省きつつ、「何が主キーで、何と何がつながるか」に注目してください。

users テーブル（ユーザー管理）

CREATE TABLE users (
  id             BIGINT AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(100) NOT NULL,
  email          VARCHAR(255) NOT NULL UNIQUE,
  password_hash  VARCHAR(255) NOT NULL,
  created_at     DATETIME NOT NULL,
  updated_at     DATETIME NOT NULL
) ENGINE=InnoDB;
SQL

ここは「誰が使っているか」の土台です。
id が全ての起点になります。

products テーブル（商品管理）

CREATE TABLE products (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(200) NOT NULL,
  description   TEXT NULL,
  price         INT NOT NULL,
  stock_quantity INT NOT NULL DEFAULT 0,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at    DATETIME NOT NULL,
  updated_at    DATETIME NOT NULL
) ENGINE=InnoDB;
SQL

price は「現在の販売価格」、stock_quantity は在庫数、is_active は「販売中かどうか」です。
ここは「何を売るか」のマスタです。

orders テーブル（注文のヘッダ）

CREATE TABLE orders (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       BIGINT NOT NULL,
  total_amount  INT NOT NULL,
  status        VARCHAR(20) NOT NULL,  -- 'new', 'paid', 'canceled' など
  created_at    DATETIME NOT NULL,
  updated_at    DATETIME NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;
SQL

ここは「1回の注文そのもの」です。
誰が、いつ、いくらの注文をしたか、そして今の状態（新規／支払い済み／キャンセルなど）を持ちます。

order_items テーブル（注文の明細）

CREATE TABLE order_items (
  id           BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id     BIGINT NOT NULL,
  product_id   BIGINT NOT NULL,
  unit_price   INT NOT NULL,
  quantity     INT NOT NULL,
  CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_order_items_product
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;
SQL

ここが「どの商品を、いくらで、何個買ったか」を表す部分です。
unit_price に「注文時点の価格」をコピーするのが重要ポイントです。

この4テーブルが、総合課題の“背骨”になります。

ユーザー管理の代表的な処理をイメージする

「登録・ログイン・一覧の3つを押さえる」

ユーザー管理で最低限やりたいのは、登録、ログイン、一覧表示です。
ここでは、SQLレベルで「何をしているか」をイメージします。

新規登録では、users に1行 INSERT します。
パスワードは必ずハッシュ化してから入れます。

INSERT INTO users (name, email, password_hash, created_at, updated_at)
VALUES ('Taro', 'taro@example.com', 'hashed_password_here', NOW(), NOW());
SQL

ログイン処理は、SQL的には「メールアドレスでユーザーを引き、ハッシュを比較する」です。

SELECT id, name, email, password_hash
FROM users
WHERE email = 'taro@example.com';
SQL

ここで取れた password_hash と、入力されたパスワードをアプリ側で照合します。
MySQLは「認証の結果」を知らないので、ここはアプリの責任です。

一覧表示はシンプルです。

SELECT id, name, email, created_at
FROM users
ORDER BY id DESC
LIMIT 50;
SQL

総合課題としては、「users は他の機能の土台なので、ここはシンプルかつ堅く作る」という意識が大事です。

商品管理の代表的な処理をイメージする

「登録・更新・一覧・非公開化」

商品管理では、商品登録、情報更新、一覧表示、非公開化（販売停止）が基本です。

商品登録は、products に1行 INSERT します。

INSERT INTO products (name, description, price, stock_quantity, is_active, created_at, updated_at)
VALUES ('サンプル商品A', '説明文', 1200, 100, 1, NOW(), NOW());
SQL

価格や在庫を更新するときは、UPDATE を使います。

UPDATE products
SET price = 1500,
    updated_at = NOW()
WHERE id = 1;
SQL

一覧表示では、「販売中の商品だけ」を出したいことが多いので、is_active を使います。

SELECT id, name, price, stock_quantity
FROM products
WHERE is_active = 1
ORDER BY id DESC
LIMIT 50;
SQL

販売停止にしたいときは、削除ではなく is_active を 0 にします。

UPDATE products
SET is_active = 0,
    updated_at = NOW()
WHERE id = 1;
SQL

これにより、過去の注文の order_items.product_id はそのまま生き続け、
「昔買った商品が今は非公開」という状態も自然に表現できます。

注文処理を“トランザクション”として組み立てる

「orders と order_items と在庫更新を“ひとまとまり”で考える」

ここが総合課題の一番の山場です。
「1回の注文」を、トランザクションとしてどう扱うかをイメージしましょう。

例として、「ユーザーID=1 が、商品ID=10を2個、商品ID=11を1個買う」ケースを考えます。
合計金額は、products から価格を取って計算します。

流れを日本語で書くと、こうなります。

トランザクションを開始する。
カート内の各商品について、現在の価格と在庫数を products から取得する。
在庫が足りない商品があれば、ロールバックしてエラーにする。
全て問題なければ、orders に1行 INSERT する。
order_items に、商品ごとに1行ずつ INSERT する（unit_price にそのときの価格を入れる）。
products の在庫数を減らす。
トランザクションをコミットする。

これを、SQLイメージで見てみます。

START TRANSACTION;

SELECT price, stock_quantity
FROM products
WHERE id = 10
FOR UPDATE;

SELECT price, stock_quantity
FROM products
WHERE id = 11
FOR UPDATE;
SQL

FOR UPDATE を付けることで、「この商品行をロックして、他のトランザクションが同時に在庫をいじれないようにする」ことができます。
在庫チェックはアプリ側で行い、「stock_quantity >= 必要数量」であることを確認します。

在庫が足りていると判断したら、orders を作ります。

INSERT INTO orders (user_id, total_amount, status, created_at, updated_at)
VALUES (1, 1200 * 2 + 800 * 1, 'new', NOW(), NOW());
SQL

ここで得られた orders.id（例えば 100）を使って、order_items を作ります。

INSERT INTO order_items (order_id, product_id, unit_price, quantity)
VALUES
  (100, 10, 1200, 2),
  (100, 11, 800, 1);
SQL

最後に、在庫を減らします。

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE id = 10;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 11;
SQL

全て成功したら、コミットします。

COMMIT;
SQL

どこかでエラーが起きたら、ロールバックします。

ROLLBACK;
SQL

ここでの超重要ポイントは、「orders・order_items・在庫更新を、必ず同じトランザクションに入れる」ことです。
そうしないと、「注文だけ作られたけど在庫が減っていない」「在庫だけ減ったけど注文がない」といった不整合が起きます。

売上集計の代表的なクエリを見てみる

「日別・商品別・ユーザー別をそれぞれ一つずつ」

売上集計は、新しいテーブルを作らなくても、orders と order_items から取り出せます。
代表的な3パターンを見てみましょう。

日別売上（1日ごとの合計金額と件数）

SELECT
  DATE(created_at) AS sale_date,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_sales
FROM orders
WHERE status = 'paid'
GROUP BY DATE(created_at)
ORDER BY sale_date;
SQL

ここでは、「支払い済みの注文だけ」を対象にしています。
status の扱いは設計次第ですが、「キャンセルを含めるかどうか」は必ず決めておきましょう。

商品別売上（商品ごとの売上金額と販売数量）

SELECT
  oi.product_id,
  p.name,
  SUM(oi.quantity) AS total_quantity,
  SUM(oi.unit_price * oi.quantity) AS total_sales
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 'paid'
GROUP BY oi.product_id, p.name
ORDER BY total_sales DESC;
SQL

ここでは、order_items と orders と products をJOINしています。
unit_price × quantity を SUM することで、「注文時点の価格での売上」を集計できます。

ユーザー別売上（ユーザーごとの購入金額と件数）

SELECT
  o.user_id,
  u.name,
  COUNT(*) AS order_count,
  SUM(o.total_amount) AS total_sales
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY o.user_id, u.name
ORDER BY total_sales DESC;
SQL

ここでは、orders と users をJOINしています。
「どのユーザーがどれくらい買っているか」を見ることができます。

これらのクエリを読んで、「何を軸にグループ化しているか」「どのテーブルからどの情報を取っているか」を説明できれば、売上集計の基本は押さえられています。

総合課題を“仕上げる”ときに意識してほしいこと

「“動く”だけでなく、“壊れにくく・振る舞いが一貫しているか”を見る」

Day29 の総合課題を自分で実装するとき、最後に次のような観点で見直してみてください。

ユーザー登録から注文、売上集計まで、一人のユーザーのストーリーが通っているか。
注文時点の価格が order_items.unit_price にちゃんとコピーされているか。
在庫更新と注文作成が、同じトランザクションで処理されているか。
売上集計で、「キャンセルされた注文」をどう扱うかが一貫しているか。

これらは、どれも「正解が一つ」ではありません。
大事なのは、「自分で選んだルールが、全体を通してブレていないか」です。

SQLite のシンプルな世界から、MySQL の“ちゃんとした設計”の世界に来たあなたなら、
もう「とりあえず動けばいい」から一歩進んだ視点を持てています。
Day29 は、その成長を自分で実感するタイミングでもあります。

Day29 後半のまとめ

総合課題の実装では、users（誰が）、products（何を）、orders（1回の注文のヘッダ）、order_items（注文の明細）という4つのテーブルを軸に、ユーザー登録や商品登録の基本的なINSERT／UPDATE、商品非公開のための is_active フラグ、注文処理では START TRANSACTION の中で価格取得と在庫チェック、orders へのINSERT、order_items へのINSERT、在庫のUPDATEをひとまとまりで実行して整合性を保ち、売上集計では orders と order_items を時間・商品・ユーザーの軸で GROUP BY して「日別売上」「商品別売上」「ユーザー別売上」を取り出す、という流れを組み立てていく。
そのうえで、「注文時点の価格を unit_price にコピーする」「在庫と注文を同じトランザクションに入れる」「集計対象の注文ステータス（例：status='paid'）を明確に決める」といった“壊れにくく、振る舞いが一貫した設計”を意識することで、小さくても現実世界に耐えうるECバックエンドの骨格を、自分の手で描けるようになる。

前へ
12
スポンサーリンク
タイトルとURLをコピーしました