SQL 入門：SELECT 不只是查資料
從關聯式資料庫到 JOIN 的完整思維

-- 建立 users 表
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,          -- 自動遞增 ID
    name       VARCHAR(100) NOT NULL,       -- 最多 100 字元，不可為空
    email      VARCHAR(255) UNIQUE NOT NULL, -- 唯一且不可為空
    created_at TIMESTAMP DEFAULT NOW()      -- 預設為當下時間
);

-- 建立 orders 表（有外鍵關聯）
CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    product    VARCHAR(200) NOT NULL,
    amount     DECIMAL(10, 2) NOT NULL CHECK (amount > 0),
    status     VARCHAR(20) DEFAULT 'pending'
               CHECK (status IN ('pending', 'shipped', 'delivered')),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 常用的 DDL 操作
ALTER TABLE users ADD COLUMN phone VARCHAR(20);  -- 新增欄位
ALTER TABLE users DROP COLUMN phone;              -- 刪除欄位
DROP TABLE orders;                                -- 刪除整張表（謹慎！）

-- ─────────────── CREATE（新增）───────────────
INSERT INTO users (name, email)
VALUES ('Joseph', 'joseph@example.com');

-- 一次插入多筆
INSERT INTO orders (user_id, product, amount)
VALUES (1, 'MacBook Pro', 60000),
       (1, 'AirPods', 5000);

-- ─────────────── READ（查詢）────────────────
SELECT * FROM users;                    -- 所有欄位（避免在 production 用 *）
SELECT id, name, email FROM users;     -- 指定欄位

-- WHERE 條件
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE amount > 10000 AND status = 'shipped';

-- ORDER BY / LIMIT / OFFSET（分頁）
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;   -- 第一頁，每頁 10 筆

-- ─────────────── UPDATE（修改）───────────────
UPDATE orders
SET status = 'shipped'
WHERE id = 101;

-- ⚠️ 千萬記得加 WHERE，不然會更新所有列！
-- UPDATE orders SET status = 'shipped';   -- 災難！

-- ─────────────── DELETE（刪除）───────────────
DELETE FROM orders WHERE id = 101;

-- 軟刪除（推薦做法）
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;
UPDATE users SET deleted_at = NOW() WHERE id = 1;
-- 查詢時過濾掉已刪除的
SELECT * FROM users WHERE deleted_at IS NULL;

-- LIKE：模糊搜尋（% 匹配任意字元，_ 匹配單一字元）
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE name LIKE 'J%';   -- 名字以 J 開頭

-- IN：多值比對（比多個 OR 更清晰）
SELECT * FROM orders WHERE status IN ('pending', 'shipped');
-- 等同於 WHERE status = 'pending' OR status = 'shipped'

-- BETWEEN：範圍查詢（含兩端）
SELECT * FROM orders WHERE amount BETWEEN 1000 AND 50000;

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE deleted_at IS NULL;

-- 子查詢（Subquery）：在 WHERE 中嵌套另一個 SELECT
SELECT * FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE email LIKE '%@company.com'
);

-- 聚合函數：COUNT / SUM / AVG / MAX / MIN
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT SUM(amount) FROM orders WHERE user_id = 1;
SELECT AVG(amount) FROM orders;

-- GROUP BY + HAVING（分組後再篩選）
SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 10000;   -- HAVING 是對分組後的結果篩選（不是 WHERE）

-- INNER JOIN：只要有訂單的使用者
SELECT u.name, o.product, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN：所有使用者，沒訂單的也要出現
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
-- 沒有訂單的使用者：order_count = 0（因為 COUNT(NULL) = 0）

-- 多表 JOIN：users + orders + products（假設有 products 表）
SELECT u.name, o.amount, p.category
FROM orders o
JOIN users    u ON o.user_id    = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'shipped';

-- SELF JOIN：在同一張表查關聯（例如員工與主管都在 employees 表）
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;