🗃️ SQL
リレーショナルデータベースの標準言語。SELECT・JOIN・集計関数・ウィンドウ関数・CTE を体系的に整理。
📌 基本 SELECT 構文
-- 基本形
SELECT col1, col2
FROM table_name
WHERE col1 = 'value'
ORDER BY col2 DESC
LIMIT 100;
-- DISTINCT: 重複排除
SELECT DISTINCT department FROM employees;
-- CASE 式
SELECT name,
CASE WHEN salary >= 500000 THEN '高'
WHEN salary >= 300000 THEN '中'
ELSE '低' END AS salary_rank
FROM employees;
📌 WHERE 条件
| 演算子 / 構文 | 意味・例 |
|---|
=, <>, <, >, <=, >= | 比較演算子 |
AND / OR / NOT | 論理演算子 |
BETWEEN a AND b | 範囲指定（a 以上 b 以下） |
IN (v1, v2, ...) | 値リストに含まれる |
LIKE 'A%' | パターンマッチ（% は任意文字列、_ は1文字） |
IS NULL / IS NOT NULL | NULL 判定 |
EXISTS (subquery) | サブクエリの結果が1行以上存在 |
📌 JOIN
-- INNER JOIN: 両テーブルに一致する行のみ
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;
-- LEFT JOIN: 左テーブルは全行、右テーブルは一致した行のみ
SELECT e.name, o.order_date
FROM employees e
LEFT JOIN orders o ON e.id = o.emp_id;
-- CROSS JOIN: 直積（全組み合わせ）
SELECT a.val, b.val FROM tbl_a a CROSS JOIN tbl_b b;
-- 自己結合: 同一テーブルを別名で JOIN
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
📌 集計関数 & GROUP BY
| 関数 | 内容 |
|---|
COUNT(*) | 行数（NULLを含む） |
COUNT(col) | NULLを除いた行数 |
SUM(col) | 合計 |
AVG(col) | 平均 |
MAX(col) / MIN(col) | 最大値 / 最小値 |
SELECT dept_id,
COUNT(*) AS headcount,
AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 400000
ORDER BY avg_sal DESC;
📌 ウィンドウ関数
-- ROW_NUMBER: グループ内の連番
SELECT name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees;
-- RANK / DENSE_RANK: 同順位考慮
RANK() OVER (ORDER BY salary DESC) -- 同順位後はスキップ
DENSE_RANK() OVER (ORDER BY salary DESC) -- スキップなし
-- LAG / LEAD: 前後の行の値を参照
LAG(salary, 1) OVER (ORDER BY hire_date) -- 1行前
LEAD(salary, 1) OVER (ORDER BY hire_date) -- 1行後
-- 累計合計
SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
📌 CTE（WITH 句）
-- 共通テーブル式: 読みやすいサブクエリ
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
),
high_earners AS (
SELECT e.name, e.salary, da.avg_sal
FROM employees e
JOIN dept_avg da ON e.dept_id = da.dept_id
WHERE e.salary > da.avg_sal
)
SELECT * FROM high_earners ORDER BY salary DESC;
📌 DML（データ操作）
-- INSERT
INSERT INTO employees (name, dept_id, salary)
VALUES ('田中', 3, 380000);
-- UPDATE
UPDATE employees
SET salary = salary * 1.05
WHERE dept_id = 3;
-- DELETE
DELETE FROM employees
WHERE hire_date < '2000-01-01';
-- UPSERT (PostgreSQL)
INSERT INTO kv (key, value)
VALUES ('x', 1)
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;