SQL基本コマンド集

-- 全カラムに値を指定
INSERT INTO users VALUES (1, 'Tanaka', 'tanaka@example.com', 25, NOW());

-- 特定のカラムに値を指定（推奨）
INSERT INTO users (name, email, age)
VALUES ('Tanaka', 'tanaka@example.com', 25);

-- 一部のカラムのみ指定（他はNULLまたはデフォルト値）
INSERT INTO users (name, email)
VALUES ('Suzuki', 'suzuki@example.com');

-- 複数行を同時に挿入
INSERT INTO users (name, email, age) VALUES
  ('Tanaka', 'tanaka@example.com', 25),
  ('Suzuki', 'suzuki@example.com', 30),
  ('Sato', 'sato@example.com', 28);

-- パフォーマンスが良い（個別にINSERTするより高速）

-- 別テーブルからデータをコピー
INSERT INTO users_backup (name, email, age)
SELECT name, email, age FROM users WHERE age >= 20;

-- 集計結果を挿入
INSERT INTO category_stats (category, product_count, avg_price)
SELECT category, COUNT(*), AVG(price)
FROM products
GROUP BY category;

-- 重複していたら更新（MySQL）
INSERT INTO users (id, name, email, age)
VALUES (1, 'Tanaka', 'tanaka@example.com', 26)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  age = VALUES(age);

-- 重複していたら無視
INSERT IGNORE INTO users (id, name, email)
VALUES (1, 'Tanaka', 'tanaka@example.com');

-- PostgreSQLの場合
INSERT INTO users (id, name, email, age)
VALUES (1, 'Tanaka', 'tanaka@example.com', 26)
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
    age = EXCLUDED.age;

-- 重複時は何もしない
INSERT INTO users (id, name, email)
VALUES (1, 'Tanaka', 'tanaka@example.com')
ON CONFLICT (id) DO NOTHING;

-- 1つのカラムを更新
UPDATE users
SET age = 26
WHERE id = 1;

-- 複数のカラムを更新
UPDATE users
SET age = 26, email = 'newemail@example.com'
WHERE id = 1;

-- 警告: WHERE句を省略すると全行が更新される！
UPDATE users SET age = 0;  -- 全ユーザーのageが0に！

-- 複数条件
UPDATE products
SET price = price * 0.9
WHERE category = 'electronics' AND stock > 10;

-- 計算結果を使う
UPDATE products
SET price = price * 1.1
WHERE category = 'books';

-- 現在値を使った更新
UPDATE products
SET stock = stock - 1
WHERE id = 100;

-- 条件によって異なる値を設定
UPDATE products
SET price = CASE
  WHEN category = 'electronics' THEN price * 1.1
  WHEN category = 'books' THEN price * 1.05
  ELSE price
END
WHERE stock > 0;

-- 他のテーブルの値を使って更新
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.status = 'vip_processing'
WHERE u.membership = 'premium';

-- PostgreSQLの構文
UPDATE orders
SET status = 'vip_processing'
FROM users
WHERE orders.user_id = users.id
  AND users.membership = 'premium';

-- 条件に一致する行を削除
DELETE FROM users WHERE id = 1;

-- 複数条件
DELETE FROM products
WHERE stock = 0 AND created_at < '2025-01-01';

-- 警告: WHERE句を省略すると全行が削除される！
DELETE FROM users;  -- 全ユーザーが削除される！

-- 注文がないユーザーを削除
DELETE FROM users
WHERE id NOT IN (
  SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL
);

-- 1年以上前のデータを削除
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 他のテーブルを参照して削除
DELETE o
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'deleted';

-- テーブルの全データを削除（高速）
TRUNCATE TABLE logs;

-- DELETEとの違い:
-- - TRUNCATEは高速（ロールバック不可の場合あり）
-- - WHERE句が使えない
-- - AUTO_INCREMENTはリセットされる
-- - トリガーが発火しない

-- DELETE: 条件指定可能、ロールバック可能、遅い
DELETE FROM users WHERE status = 'inactive';

-- TRUNCATE: 全削除のみ、高速、AUTO_INCREMENTリセット
TRUNCATE TABLE users;

-- トランザクション開始
START TRANSACTION;
-- または
BEGIN;

-- 複数の操作を実行
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- 全て成功したらコミット
COMMIT;

-- エラーがあったらロールバック
ROLLBACK;

-- 銀行口座間の送金
START TRANSACTION;

-- 送金元から引き落とし
UPDATE accounts 
SET balance = balance - 10000 
WHERE id = 1 AND balance >= 10000;

-- 引き落としに失敗したかチェック
SELECT ROW_COUNT() INTO @affected;

IF @affected = 0 THEN
  ROLLBACK;
  SELECT '残高不足' AS error;
ELSE
  -- 送金先に加算
  UPDATE accounts 
  SET balance = balance + 10000 
  WHERE id = 2;
  
  COMMIT;
  SELECT '送金完了' AS message;
END IF;

-- トランザクションの一部だけをロールバック
START TRANSACTION;

INSERT INTO users (name, email) VALUES ('User1', 'user1@example.com');

-- セーブポイントを作成
SAVEPOINT sp1;

INSERT INTO users (name, email) VALUES ('User2', 'user2@example.com');

-- エラーがあったらsp1までロールバック
ROLLBACK TO SAVEPOINT sp1;

-- User1の挿入は有効、User2はキャンセルされる
COMMIT;

-- 分離レベルの設定（MySQL）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- MySQLデフォルト
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- レベルの特徴:
-- READ UNCOMMITTED: ダーティリード発生（最も低い分離）
-- READ COMMITTED: コミット済みデータのみ読む
-- REPEATABLE READ: 同じトランザクション内で一貫した読み取り
-- SERIALIZABLE: 最も高い分離（最も遅い）