SQL Guide初心者向けSQL文・キーワード集

練習用テーブル：servers

以降は、サーバー台帳を想定した次の列を使います。1行が1台のサーバー、列がその属性です。owner_id は担当者を表す番号で、結合（JOIN）で使う owners テーブルの主キー（その表の中で1行を見分けるための、重複しない列）に対応します。

この列に、以下のような架空のサンプルデータが入っているものとして、このページの例と演習を進めます。

1. データを読む・絞る・並べる

SELECT hostname, role, cpu_usage
FROM servers
WHERE environment = 'prod' AND cpu_usage >= 80
ORDER BY cpu_usage DESC
LIMIT 10;

このデータに対する実行結果（#sampleのサンプルデータで再現した例。架空のデータであり、実機で実行した記録ではありません）：

読み解き方：上のSQLを「本番環境（prod）でCPU使用率が80%以上のサーバーを、使用率が高い順に最大10台表示する」と日本語へ戻せれば理解できています。面接では「条件で絞り、並べ替え、件数を制限する流れを説明できる」と言えます。

2. 数える・まとめる

かんたんに言うとここでは、1行ずつ見るのをやめて「同じ仲間ごとにまとめて数える」方法を学びます。まとめる単位を決めるのが GROUP BY、まとめたあとの結果を絞り込むのが HAVING です。

SELECT environment, COUNT(*) AS server_count, AVG(cpu_usage) AS avg_cpu
FROM servers
GROUP BY environment
HAVING AVG(cpu_usage) >= 60
ORDER BY avg_cpu DESC;

このデータに対する実行結果（#sampleのサンプルデータで再現した架空の例）：

devは平均CPU使用率が13.6と60未満のため、HAVINGで除外されて結果に含まれません。

3. 複数のテーブルをつなぐ

servers.owner_id と owners.owner_id のように、両方の表に共通する列をキー（つなぎ合わせるための目印）として使います。こうして、別々の表に分けて保存したデータを1つの結果にまとめます。担当者を管理する owners テーブルは、次の列を持つものとします。

SELECT s.hostname, s.environment, o.owner_name
FROM servers AS s
LEFT JOIN owners AS o ON s.owner_id = o.owner_id
WHERE s.environment = 'prod';

このデータに対する実行結果（#sample・上記ownersのサンプルデータで再現した架空の例）：

app-02はowner_idがNULLのため、LEFT JOINにより行は残りつつowner_nameがNULLになります。これが演習3で探す「担当者未設定」の状態です。

4. データを追加・更新・削除する

INSERT INTO servers (server_id, hostname, role, environment, cpu_usage)
VALUES (104, 'web-02', 'web', 'dev', 15.2);

UPDATE servers
SET last_checked_at = CURRENT_TIMESTAMP
WHERE server_id = 104;

DELETE FROM servers
WHERE server_id = 104 AND environment = 'dev';

5. テーブルと制約を定義する

かんたんに言うとデータを入れる前に、表の形と「入れてよい値の決まり」を先に決めます。この決まりを制約と呼びます。制約を付けておけば、間違ったデータがそもそも入らなくなります。

CREATE TABLE owners (
    owner_id INTEGER PRIMARY KEY,
    owner_name VARCHAR(50) NOT NULL
);

CREATE TABLE servers (
    server_id INTEGER PRIMARY KEY,
    hostname VARCHAR(100) NOT NULL UNIQUE,
    role VARCHAR(30) NOT NULL,
    environment VARCHAR(10) NOT NULL CHECK (environment IN ('dev', 'stg', 'prod')),
    owner_id INTEGER,
    cpu_usage DECIMAL(5,2) DEFAULT 0,
    last_checked_at TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES owners (owner_id)
);

6. トランザクションで変更をひとまとまりにする

複数の変更を「すべて成功」または「すべて取り消し」にする仕組みです。途中だけ反映される不整合を防ぎます。

BEGIN;

UPDATE servers SET environment = 'prod' WHERE server_id = 104;
SELECT server_id, hostname, environment FROM servers WHERE server_id = 104;

-- 問題なければ確定
COMMIT;
-- 問題があれば COMMIT の代わりに ROLLBACK;

7. 権限と安全なSQL

まず、SQLインジェクション（利用者が入力した文字を悪用して、想定外のSQLを実行させる攻撃）という典型的な事故の悪い例を見ます。利用者が入力した文字を、そのままSQL文へつなぎ合わせてはいけません。入力の内容しだいで、作った人が想定していないSQLが実行されてしまいます。

-- 悪い例：入力値をそのまま文字列連結している（アプリ側の疑似コード、実行しないでください）
sql = "SELECT * FROM servers WHERE hostname = '" + input + "'";

-- 利用者が hostname 欄に  ' OR '1'='1  と入力すると、
-- 実際に実行される文は次のようになってしまう
SELECT * FROM servers WHERE hostname = '' OR '1'='1';
-- WHERE条件が常に真になり、絞り込みたかった一部の行ではなく全行が返る

GRANT SELECT ON servers TO monitoring_user;
REVOKE UPDATE, DELETE ON servers FROM monitoring_user;

-- アプリ側の考え方（記法はライブラリで異なる）
SELECT hostname FROM servers WHERE server_id = ?;

8. インデックス・ビュー・実行計画

かんたんに言うと「なぜ遅いのか」を調べて速くする章です。インデックスは本の索引にあたる検索の近道、ビューはよく使うSQLに付けた名前、実行計画はデータベースがどう処理するつもりかを見せてくれる説明書です。

CREATE INDEX idx_servers_environment_role
ON servers (environment, role);

EXPLAIN
SELECT hostname FROM servers
WHERE environment = 'prod' AND role = 'web';

実行計画の出力例（製品によって列名や表示は異なります。架空の例であり、実機で実行した記録ではありません）：

typeがrefでkeyには、実際に使われたインデックスの名前が入ります。ここに作成したインデックス名が表示され、推定行数（rows）が表全体の行数より少なければ、インデックスが使われたと判断できます。面接では「実行計画を見て、インデックスが使われたことを確認した」と説明できます。typeがALLでkeyが空の場合は全件走査（フルスキャン）です。

1. 遅いSQL、実行時間、頻度、対象時間帯をログで特定する。
2. EXPLAIN で実行計画と推定件数を確認する。
3. 不要な列・行、関数、並び替え、結合条件を見直す。
4. 必要なら候補インデックスを検証環境で比較する。
5. 変更前後の時間、CPU、I/O、ロック、書き込み性能を記録する。

9. SQLキーワード早見表

10. サーバー運用でSQLエラーが起きたとき

1. 影響を確認：誰が、いつから、どの画面・処理で失敗しているかを記録する。
2. 接続を確認：データベースのサービスが動いているか、待受ポート（接続を受け付ける番号）、DNS（ホスト名からIPアドレスを調べる仕組み）、ファイアウォール（決めた条件で通信を許可・拒否する関所）、接続先の設定、TLS（通信の暗号化）、認証の順に確認する。
3. エラーを保存：画面の表示だけで判断しない。発生時刻、SQLSTATE（SQLの標準エラーコード）、データベースのログ、アプリのログを採取する。パスワードなどの秘密情報は伏せる。
4. 切り分け：構文、権限、ロック、容量、接続数、タイムアウト、実行計画、直前変更を順に確認する。
5. 変更を管理：本番で思いつきのSQLを実行せず、承認、対象確認、バックアップ、切り戻し、作業記録をそろえる。
6. 復旧後を確認：エラーが消えたことだけでなく、件数、整合性、監視、再発有無を確認し、手順へ反映する。