Sakilaデータベースで使える調査系SQLまとめとJOIN準備の基本
Sakilaデータベースを使って、テーブル構造の調査やJOIN設計に役立つSQLをまとめておく。
JOINを書く前にどのテーブルがどこに接続されているかを確認することで、ミスを減らして設計をスムーズに進められる。
✅ よく使う調査系SQLコマンド一覧
1. テーブル一覧を確認する
SHOW TABLES;
2. テーブルのカラム構造を確認する
DESCRIBE テーブル名;
-- 例：
DESCRIBE actor;
主キー、データ型、NULL制約などを確認できる。
3. CREATE TABLE の定義を確認する
SHOW CREATE TABLE テーブル名;
-- 例：
SHOW CREATE TABLE payment;
外部キー制約やインデックス、デフォルト値なども含めてすべての定義を確認できる。
4. 外部キーの一覧を確認する（JOIN設計の地図）
SELECT
TABLE_NAME,
COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = 'sakila'
AND REFERENCED_TABLE_NAME IS NOT NULL;
どのテーブルがどこを参照しているかを一覧で確認できる。
5. 全テーブル・全カラムの一覧を確認する
SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'sakila';
カラム名のあいまいな記憶を補うのに便利。
6. インデックスを確認する（パフォーマンス対策）
SHOW INDEX FROM テーブル名;
または：
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'sakila';
7. テーブルのレコード数を確認する
SELECT COUNT(*) FROM テーブル名;
テーブルの規模感を知っておくことでJOINのパフォーマンス見積もりにも使える。
🔁 JOINを書く前のチェックリスト
SHOW TABLES; で全体像を把握する
DESCRIBE テーブル; で主キーの確認
SHOW CREATE TABLE で外部キー・制約を確認
KEY_COLUMN_USAGE でテーブル間のリレーションを一覧で見る
必要なJOINを書き始める
🧩 CREATE文の中に出てくる構文の意味
CONSTRAINT（制約）
CONSTRAINT `fk_payment_customer`
制約の名前。外部キーやユニーク制約などにつける。
後で削除・変更する際に指定できる。
FOREIGN KEY（外部キー）
FOREIGN KEY (`customer_id`)
このカラムが外部キーであることを宣言する。
REFERENCES（参照先）
REFERENCES `customer` (`customer_id`)
外部キーがどのテーブル・カラムを参照しているかを示す。
組み合わせた構文の例
CONSTRAINT `fk_payment_customer`
FOREIGN KEY (`customer_id`)
REFERENCES `customer` (`customer_id`)
payment.customer_id は外部キー
参照先は customer.customer_id
この関係を fk_payment_customer という名前の制約として登録している
🧭 まとめ
JOINを正確に書くためには、リレーションの把握が必須。
特にSakilaのようなリレーションが多いサンプルデータベースでは、外部キーの一覧やCREATE文を確認しながら設計していくのが重要。
JOINに迷ったときは information_schema を使えば、ER図がなくても十分戦える。