C
データベース 一覧
前へ3 / 6次へ
データベース/SQL 基礎/Lesson 03

SQL 実習 — テーブル作成からデータ挿入・照会まで

1時間·theory
このチャプター
2/2

SQL 実習 — テーブル作成からデータ挿入・照会まで

🎯 このレッスンを読み終えたら

このレッスンを最後まで読めば、以下の3つを自信を持ってできるようになります。

  • ▸✅ CREATE TABLE + FK + 制約で 実際のスキーマ を設計する
  • ▸✅ INSERT · UPDATE · DELETE の WHERE 抜け漏れの落とし穴を理解する
  • ▸✅ サブクエリの IN vs EXISTS + GROUP BY + HAVING の組み合わせを使いこなす

学習目標を チェックリストとして手元に置き、すべてに答えられるようになったらレッスンを閉じてください。

テーブルの作成 — CREATE TABLE + 制約

DDL の出発点

sql
CREATE TABLE users (
    id          BIGINT       AUTO_INCREMENT,
    email       VARCHAR(255) NOT NULL UNIQUE,
    name        VARCHAR(50)  NOT NULL,
    age         INT          CHECK (age >= 0),
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

制約の5種類

  • ▸PRIMARY KEY — 一意 + NOT NULL。行の識別子。テーブルに1つだけ。
  • ▸NOT NULL — 空にできない。
  • ▸UNIQUE — 重複不可。PK と違い、複数設定できる。
  • ▸CHECK — 値の範囲を検証。CHECK (age >= 0)。
  • ▸FOREIGN KEY — 他のテーブルを参照。参照整合性を保証する。

外部キー — リレーションの作成

sql
CREATE TABLE orders (
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id  BIGINT NOT NULL,
    amount   INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

ON DELETE CASCADE — 親行を削除すると 子行も自動的に削除される。RESTRICT（デフォルト）の場合、子が存在すると親の削除が拒否される。

INSERT · UPDATE · DELETE

sql
-- 追加
INSERT INTO users (email, name, age) VALUES ('[email protected]', 'Alice', 30);

-- 複数行を一括挿入
INSERT INTO users (email, name, age) VALUES
    ('[email protected]', 'Bob', 25),
    ('[email protected]', 'Carol', 28);

-- 更新
UPDATE users SET age = 31 WHERE email = '[email protected]';

-- 削除
DELETE FROM users WHERE age < 20;

WHERE のない UPDATE/DELETE は全行が対象。誤って1行省略すると テーブルごと消える。必ずトランザクション内でテストしましょう。

JOIN — INNER と LEFT の違い

テストデータ

sql
users           orders
+----+-------+  +----+---------+--------+
| id | name  |  | id | user_id | amount |
+----+-------+  +----+---------+--------+
|  1 | A     |  |  1 |       1 |  10000 |
|  2 | B     |  |  2 |       1 |   5000 |
|  3 | C     |  |  3 |       3 |   8000 |
+----+-------+  +----+---------+--------+

INNER JOIN — 両方に存在する行のみ

sql
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 結果: A/10000, A/5000, C/8000  (B は注文なしのため除外)

LEFT JOIN — 左テーブルの行はすべて含まれる

sql
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 結果: A/10000, A/5000, B/NULL, C/8000

B のように 対応する行がないユーザーも NULL として含まれる。「注文のないユーザーを探す」 場面でよく使う:

sql
SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
-- 結果: B

RIGHT JOIN — ほとんど使わない

テーブルの順序を入れ替えれば LEFT JOIN で表現できる。統一性のために LEFT だけ 使うチームが多い。

CROSS JOIN — すべての組み合わせ

sql
SELECT a.name, b.name FROM users a CROSS JOIN users b;
-- 9行 (3 × 3)

直積（デカルト積）。実務ではほぼ使わない — 誤って ON を省略すると発生する 大惨事。

サブクエリ · GROUP BY · HAVING

サブクエリの3種類

IN — リストに含まれているか

sql
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 5000);

EXISTS — 1行でも存在するか（パフォーマンス優秀）

sql
SELECT name FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 5000
);

EXISTS は最初の行が見つかった時点で終了する → 大きなテーブルでは IN より速いことが多い。

スカラーサブクエリ — 1つの値 のみを返す

sql
SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

SELECT 句で 各行ごとに1回実行される — 小さなテーブル以外では JOIN に書き換えた方が 速い。

GROUP BY — 集計関数とセットで使う

sql
-- ユーザーごとの合計注文金額
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

GROUP BY に含まれていないカラム を SELECT に書くと エラー（または非標準の動作）になる。集計関数は5種類:

  • ▸COUNT(*) — 行数
  • ▸SUM(col) — 合計
  • ▸AVG(col) — 平均
  • ▸MAX(col) / MIN(col) — 最大 / 最小

HAVING vs WHERE

WHERE はグループ化前のフィルタ、HAVING はグループ化後のフィルタ。

sql
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE amount > 1000               -- 1000超の注文のみ
GROUP BY user_id
HAVING SUM(amount) > 10000;       -- 合計が1万超のユーザーのみ

HAVING で集計関数が使える のが本質的な違い。WHERE では SUM() は使えない。

ORDER BY · LIMIT

sql
SELECT name FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

3ページ目のデータ（1ページあたり10件） を取得する。ページネーションの基本パターン。

実践でまとめる — 上位ユーザー TOP 5

sql
SELECT u.name, SUM(o.amount) AS total
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING SUM(o.amount) > 0
ORDER BY total DESC
LIMIT 5;

JOIN + GROUP BY + HAVING + ORDER BY + LIMIT — 実務のクエリの90%がこの組み合わせです。

🤖 AI にこう質問してみましょう

このレッスンの概念を身につければ、AI に 具体的に 指示できるようになります。漠然とした「直して」ではなく、語彙を持ったリクエスト — それがトークン節約の出発点です。

  • ▸「users · orders · products の3テーブルを設計し、FK と INDEX まで作成して」
  • ▸「直近7日間に登録したユーザー数を日別に集計するクエリを書いて」
  • ▸「このクエリに EXPLAIN を付けて実行計画を解説して」

なぜこれがトークンを減らすのか

概念を知らないと、AI の回答を受け取っても 「それは何ですか?」 ともう一度聞かなければなりません。その「再質問」がトークンを消費します。概念を一度身につければ 会話が一回で終わります。

先に読むとよい概念: SQL 基礎 — RDBMS・SELECT・WHERE・GROUP BY・JOIN
次のおすすめ: トランザクション + インデックス — ACID・分離レベル・MVCC・B+Tree
前へ一覧次へ