-- 1行挿入
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 30);

-- 複数行一括挿入
INSERT INTO users (name, email, age)
VALUES
    ('Bob',   'bob@example.com',   25),
    ('Carol', 'carol@example.com', 35),
    ('Dave',  'dave@example.com',  28);

-- SELECTの結果を挿入
INSERT INTO archived_users (id, name, email)
SELECT id, name, email
FROM users
WHERE deleted_at IS NOT NULL;

-- UPSERT: 存在すれば更新、なければ挿入
INSERT INTO users (email, name, updated_at)
VALUES ('alice@example.com', 'Alice', NOW())
ON CONFLICT (email) DO UPDATE
    SET name       = EXCLUDED.name,
        updated_at = EXCLUDED.updated_at;
-- MySQL: ON DUPLICATE KEY UPDATE name = VALUES(name);

-- UPDATE
UPDATE users
SET name = 'Alice Smith',
    updated_at = NOW()
WHERE id = 1;

-- 複数行を条件で一括更新
UPDATE users
SET role = 'premium'
WHERE id IN (
    SELECT user_id FROM orders
    GROUP BY user_id HAVING SUM(total) > 100000
);

-- 別テーブルの値で更新（PostgreSQL）
UPDATE users u
SET score = s.total_score
FROM scores s
WHERE u.id = s.user_id;

-- DELETE
DELETE FROM users WHERE id = 1;

-- 条件付き削除
DELETE FROM sessions WHERE expires_at < NOW();

-- 全行削除（トランザクション可能）
DELETE FROM logs;

-- TRUNCATE: 高速全行削除（ロールバック不可な場合あり）
TRUNCATE TABLE logs;
TRUNCATE TABLE orders CASCADE; -- 外部キー参照先も削除

-- Soft delete パターン（推奨）
UPDATE users SET deleted_at = NOW() WHERE id = 1;
SELECT * FROM users WHERE deleted_at IS NULL; -- 有効なユーザーのみ