SQL 実務チートシート — 毎日使うコマンド総まとめ

-- 基本検索
SELECT * FROM users WHERE age >= 20 ORDER BY created_at DESC LIMIT 10;

-- 特定カラムのみ
SELECT id, name, email FROM users WHERE status = 'active';

-- エイリアス（別名）
SELECT
    u.name AS user_name,
    COUNT(o.id) AS order_count,
    SUM(o.amount) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- DISTINCT（重複排除）
SELECT DISTINCT department FROM employees;

-- BETWEEN, IN, LIKE
SELECT * FROM products
WHERE price BETWEEN 10000 AND 50000
  AND category IN ('electronics', 'books')
  AND name LIKE '%Galaxy%';

-- NULL 処理
SELECT name, COALESCE(phone, '未登録') AS phone
FROM users
WHERE email IS NOT NULL;

-- 単件挿入
INSERT INTO users (name, email, age) VALUES ('Kim Youngju', 'yj@example.com', 30);

-- 複数件挿入
INSERT INTO users (name, email, age) VALUES
    ('Hong Gildong', 'hong@example.com', 25),
    ('Lee Sunsin', 'lee@example.com', 35),
    ('King Sejong', 'sejong@example.com', 45);

-- SELECT結果をINSERT（テーブルコピー）
INSERT INTO users_backup (name, email, age)
SELECT name, email, age FROM users WHERE status = 'active';

-- UPSERT（あればUPDATE、なければINSERT）
-- PostgreSQL
INSERT INTO users (email, name, login_count)
VALUES ('yj@example.com', 'Kim Youngju', 1)
ON CONFLICT (email)
DO UPDATE SET
    login_count = users.login_count + 1,
    last_login = NOW();

-- MySQL
INSERT INTO users (email, name, login_count)
VALUES ('yj@example.com', 'Kim Youngju', 1)
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = NOW();

-- 基本更新
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';

-- 複数カラムの同時更新
UPDATE products
SET price = price * 1.1,           -- 10% 値上げ
    updated_at = NOW()
WHERE category = 'electronics';

-- JOIN UPDATE（他テーブルを参照して更新）
-- PostgreSQL
UPDATE orders o
SET status = 'cancelled'
FROM users u
WHERE o.user_id = u.id
  AND u.status = 'banned';

-- MySQL
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.status = 'cancelled'
WHERE u.status = 'banned';

-- CASEを使った条件付きUPDATE
UPDATE employees
SET salary = CASE
    WHEN department = 'engineering' THEN salary * 1.15
    WHEN department = 'sales' THEN salary * 1.10
    ELSE salary * 1.05
END
WHERE hire_date < '2024-01-01';

-- 注意: WHEREなしのUPDATEは全行更新！
-- 必ず先にSELECTで確認！
SELECT * FROM users WHERE last_login < '2025-01-01';  -- まず確認
UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01';

-- 基本削除
DELETE FROM sessions WHERE expired_at < NOW();

-- JOIN DELETE
-- PostgreSQL
DELETE FROM orders
USING users
WHERE orders.user_id = users.id AND users.status = 'deleted';

-- MySQL
DELETE o FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'deleted';

-- TRUNCATE（全件削除、高速、AUTO_INCREMENTリセット）
TRUNCATE TABLE logs;

-- ソフトデリートパターン（推奨）
UPDATE users SET deleted_at = NOW() WHERE id = 123;
-- 検索時:
SELECT * FROM users WHERE deleted_at IS NULL;

-- INNER JOIN（両方に存在するもののみ）
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（左テーブル全部 + 右テーブルのマッチ）
SELECT u.name, COALESCE(COUNT(o.id), 0) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- → 注文のないユーザーも含まれる（order_count = 0）

-- RIGHT JOIN（右テーブル全部 + 左テーブルのマッチ）
-- あまり使わない、LEFT JOINを反転させた方が可読性が良い

-- FULL OUTER JOIN（両方全部）
SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN（全組み合わせ、デカルト積）
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;
-- 3 sizes × 4 colors = 12 combinations

-- SELF JOIN（自己結合）
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

JOINダイアグラム:

INNER JOIN:    A ∩ B（積集合のみ）
LEFT JOIN:     A + (A ∩ B)
RIGHT JOIN:    (A ∩ B) + B
FULL OUTER:    A ∪ B（和集合）

-- 基本集計
SELECT
    department,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000000  -- 平均給与5千万以上の部署のみ
ORDER BY avg_salary DESC;

-- ROLLUP（小計 + 総計）
SELECT
    COALESCE(department, '=== 合計 ===') AS department,
    COALESCE(position, '--- 小計 ---') AS position,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY ROLLUP(department, position);

-- WHERE サブクエリ
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders
    WHERE amount > 1000000
);

-- FROM サブクエリ（インラインビュー）
SELECT dept_name, avg_salary
FROM (
    SELECT department AS dept_name, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) sub
WHERE avg_salary > 60000000;

-- EXISTS（存在確認、大量データではINより高速）
SELECT u.name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.status = 'completed'
);

-- スカラーサブクエリ（SELECT句内）
SELECT
    name,
    salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- 基本CTE
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active' AND last_login > NOW() - INTERVAL '30 days'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT
    au.name,
    au.email,
    COALESCE(uo.order_count, 0) AS orders,
    COALESCE(uo.total, 0) AS total_spent
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id
ORDER BY total_spent DESC;

-- 再帰CTE（組織図、カテゴリツリー）
WITH RECURSIVE org_tree AS (
    -- ベースケース: CEO（manager_idがNULL）
    SELECT id, name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- 再帰ケース: 部下を探索
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT REPEAT('  ', level - 1) || name AS org_chart, level
FROM org_tree
ORDER BY level, name;

-- ROW_NUMBER（連番）
SELECT
    name, department, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_all,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_dept
FROM employees;

-- RANK vs DENSE_RANK
-- RANK: 1, 2, 2, 4（同順位2位の後は4位）
-- DENSE_RANK: 1, 2, 2, 3（同順位2位の後は3位）

-- LAG / LEAD（前/次の行を参照）
SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY date))
        / LAG(revenue) OVER (ORDER BY date) * 100, 1
    ) AS change_pct
FROM daily_sales;

-- 累積合計（Running Total）
SELECT
    date, amount,
    SUM(amount) OVER (ORDER BY date) AS running_total,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

-- NTILE（分位に分割）
SELECT
    name, salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
    -- 1=上位25%, 2=25~50%, 3=50~75%, 4=下位25%
FROM employees;

-- インデックス作成
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 複合インデックスの順序が重要！
-- idx(a, b, c) の場合:
--   WHERE a = 1                    使用される
--   WHERE a = 1 AND b = 2         使用される
--   WHERE a = 1 AND b = 2 AND c = 3 使用される
--   WHERE b = 2                    使用されない！（先頭カラムなし）
--   WHERE a = 1 AND c = 3         aのみ使用（bスキップ）

-- 部分インデックス（PostgreSQL）
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- カバリングインデックス（テーブルアクセスなしでインデックスのみで検索）
CREATE INDEX idx_covering ON orders(user_id, status, amount);
SELECT status, SUM(amount) FROM orders WHERE user_id = 123 GROUP BY status;
-- → インデックスのみ読み取り！（テーブルI/Oなし）

-- PostgreSQL
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.name;

-- 読み方:
-- Seq Scan: フルテーブルスキャン（インデックスが必要？）
-- Index Scan: インデックス使用
-- Index Only Scan: カバリングインデックス
-- Nested Loop: 少量データのJOIN
-- Hash Join: 大量データのJOIN
-- Sort: ORDER BY（メモリ超過時はディスク使用）
-- Bitmap Heap Scan: 複数インデックスの組み合わせ

-- MySQL
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';

-- OFFSET方式（シンプルだが大量データでは遅い）
SELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 40;

-- カーソルベース（大量データ推奨！）
SELECT * FROM posts
WHERE id < 12345  -- 最後に見たid
ORDER BY id DESC
LIMIT 20;

-- 重複行の検出
SELECT email, COUNT(*) as cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- 重複のうち最古のもののみ残して削除
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY email
);

-- またはCTEで
WITH ranked AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- 今日/今月/今年
SELECT * FROM orders WHERE created_at::date = CURRENT_DATE;
SELECT * FROM orders WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', NOW());

-- 直近7日間の日別統計
SELECT
    DATE(created_at) AS date,
    COUNT(*) AS orders,
    SUM(amount) AS revenue
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
GROUP BY DATE(created_at)
ORDER BY date;

-- 時間帯別分布
SELECT
    EXTRACT(HOUR FROM created_at) AS hour,
    COUNT(*) AS count
FROM orders
GROUP BY hour
ORDER BY hour;

-- SELECT FOR UPDATE（悲観的ロック）
BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;  -- 他のトランザクションは待機
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- 楽観的ロック（version カラム）
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- versionが一致する場合のみ更新
-- 影響行数 = 0 の場合 → 他の人が先に更新済み！