SQL チートシート
SQL構文のクイックリファレンス。DDL、DML、SELECTクエリ、JOIN、集約関数、サブクエリ、ウィンドウ関数、インデックスを網羅
69 件のコマンド
CREATE TABLEテーブルを作成
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255));DROP TABLEテーブルを削除
DROP TABLE IF EXISTS users;ALTER TABLE ADDテーブルにカラムを追加
ALTER TABLE users ADD COLUMN age INT;ALTER TABLE DROPテーブルからカラムを削除
ALTER TABLE users DROP COLUMN age;ALTER TABLE MODIFYカラムの型を変更
ALTER TABLE users MODIFY COLUMN name VARCHAR(200);ALTER TABLE RENAMEテーブル名を変更
ALTER TABLE users RENAME TO members;TRUNCATE TABLEテーブルの全データを高速削除
TRUNCATE TABLE logs;CREATE DATABASEデータベースを作成
CREATE DATABASE myapp;DROP DATABASEデータベースを削除
DROP DATABASE IF EXISTS myapp;CREATE VIEWビューを作成
CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';INSERT INTOデータを挿入
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');INSERT INTO ... SELECTSELECTの結果を挿入
INSERT INTO archive SELECT * FROM logs WHERE created_at < '2024-01-01';UPDATEデータを更新
UPDATE users SET name = 'Bob' WHERE id = 1;DELETEデータを削除
DELETE FROM users WHERE status = 'inactive';UPSERT (MySQL)存在すれば更新、なければ挿入（MySQL）
INSERT INTO users (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE name = 'Alice';UPSERT (PostgreSQL)存在すれば更新、なければ挿入（PostgreSQL）
INSERT INTO users (id, name) VALUES (1, 'Alice') ON CONFLICT (id) DO UPDATE SET name = 'Alice';MERGEマージ（UPSERT的操作）
MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...;SELECTデータを取得
SELECT name, email FROM users;SELECT DISTINCT重複を除外して取得
SELECT DISTINCT country FROM users;WHERE条件でフィルタリング
SELECT * FROM users WHERE age >= 18;AND / OR複数条件の組み合わせ
SELECT * FROM users WHERE age >= 18 AND status = 'active';IN値のリストに含まれるか判定
SELECT * FROM users WHERE country IN ('US', 'JP', 'UK');BETWEEN範囲内の値を取得
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';LIKEパターンマッチング
SELECT * FROM users WHERE name LIKE 'A%';IS NULL / IS NOT NULLNULLチェック
SELECT * FROM users WHERE email IS NOT NULL;ORDER BY結果をソート
SELECT * FROM users ORDER BY created_at DESC;LIMIT / OFFSET取得行数を制限
SELECT * FROM users LIMIT 10 OFFSET 20;CASE WHEN条件分岐
SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS category FROM users;COALESCE最初のNULLでない値を返す
SELECT COALESCE(nickname, name, 'Unknown') FROM users;INNER JOIN内部結合（両テーブルに一致する行のみ）
SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;LEFT JOIN左外部結合（左テーブルの全行を保持）
SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id;RIGHT JOIN右外部結合（右テーブルの全行を保持）
SELECT u.name, o.total FROM users u RIGHT JOIN orders o ON u.id = o.user_id;FULL OUTER JOIN完全外部結合（両テーブルの全行を保持）
SELECT u.name, o.total FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id;CROSS JOIN交差結合（全組み合わせ）
SELECT * FROM colors CROSS JOIN sizes;SELF JOIN自己結合（同一テーブルを結合）
SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;NATURAL JOIN自然結合（同名カラムで自動結合）
SELECT * FROM orders NATURAL JOIN customers;COUNT行数をカウント
SELECT COUNT(*) FROM users;SUM合計値を計算
SELECT SUM(amount) FROM orders;AVG平均値を計算
SELECT AVG(price) FROM products;MAX最大値を取得
SELECT MAX(salary) FROM employees;MIN最小値を取得
SELECT MIN(price) FROM products;GROUP BYグループ化して集計
SELECT country, COUNT(*) FROM users GROUP BY country;HAVINGグループ化後のフィルタリング
SELECT country, COUNT(*) AS cnt FROM users GROUP BY country HAVING cnt > 10;GROUP_CONCAT (MySQL)グループ内の値を連結
SELECT department, GROUP_CONCAT(name) FROM employees GROUP BY department;STRING_AGG (PostgreSQL)グループ内の値を連結
SELECT department, STRING_AGG(name, ', ') FROM employees GROUP BY department;WHERE ... IN (SELECT)サブクエリの結果でフィルタ
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);EXISTSサブクエリの結果が存在するか判定
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);スカラーサブクエリ単一値を返すサブクエリ
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u;WITH (CTE)共通テーブル式を定義
WITH active AS (SELECT * FROM users WHERE status = 'active') SELECT * FROM active WHERE age > 25;WITH RECURSIVE再帰CTEを定義
WITH RECURSIVE tree AS (SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id FROM categories c JOIN tree t ON c.parent_id = t.id) SELECT * FROM tree;UNION複数SELECTの結果を結合（重複除去）
SELECT name FROM users UNION SELECT name FROM admins;UNION ALL複数SELECTの結果を結合（重複含む）
SELECT name FROM users UNION ALL SELECT name FROM admins;ROW_NUMBER()行番号を付与
SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees;RANK()順位を付与（同値は同順位、次はスキップ）
SELECT name, RANK() OVER (ORDER BY score DESC) AS rank FROM students;DENSE_RANK()順位を付与（同値は同順位、次はスキップなし）
SELECT name, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM students;LAG()前の行の値を取得
SELECT date, amount, LAG(amount) OVER (ORDER BY date) AS prev_amount FROM sales;LEAD()次の行の値を取得
SELECT date, amount, LEAD(amount) OVER (ORDER BY date) AS next_amount FROM sales;SUM() OVER累積合計を計算
SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales;PARTITION BYパーティション（グループ）内でウィンドウ関数を適用
SELECT dept, name, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) FROM employees;NTILE()行をN個のグループに均等分割
SELECT name, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM students;CREATE INDEXインデックスを作成
CREATE INDEX idx_users_email ON users (email);CREATE UNIQUE INDEXユニークインデックスを作成
CREATE UNIQUE INDEX idx_users_email ON users (email);DROP INDEXインデックスを削除
DROP INDEX idx_users_email;PRIMARY KEY主キー制約を設定
ALTER TABLE users ADD PRIMARY KEY (id);FOREIGN KEY外部キー制約を設定
ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);UNIQUEユニーク制約を設定
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);CHECKチェック制約を設定
ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);NOT NULLNOT NULL制約を設定
ALTER TABLE users MODIFY COLUMN name VARCHAR(100) NOT NULL;DEFAULTデフォルト値を設定
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';