在 PostgreSQL 中，主要使用 `SELECT` 指令取得資料，搭配 `WHERE` 進行篩選。 ## 1. 取得整張資料表 ```sql SELECT * FROM users; ``` `*` 表示選取所有欄位。 建議在正式查詢中明確指定欄位： ```sql SELECT id, name, email FROM users; ``` --- ## 2. 使用 `WHERE` 篩選資料 ### 等於 ```sql SELECT * FROM users WHERE status = 'active'; ``` ### 數值比較 ```sql SELECT * FROM products WHERE price > 1000; ``` 常見運算子： ```sql = -- 等於 <> -- 不等於 != -- 不等於 > -- 大於 < -- 小於 >= -- 大於等於 <= -- 小於等於 ``` --- ## 3. 多個條件 使用 `AND`： ```sql SELECT * FROM users WHERE status = 'active' AND age >= 18; ``` 使用 `OR`： ```sql SELECT * FROM users WHERE city = 'Taipei' OR city = 'Kaohsiung'; ``` 使用括號控制條件優先順序： ```sql SELECT * FROM products WHERE category = 'book' AND (price < 500 OR stock > 0); ``` --- ## 4. 範圍與清單篩選 ### `BETWEEN` ```sql SELECT * FROM products WHERE price BETWEEN 100 AND 500; ``` `BETWEEN` 包含兩端的值。 ### `IN` ```sql SELECT * FROM users WHERE city IN ('Taipei', 'Taichung', 'Kaohsiung'); ``` 相當於： ```sql WHERE city = 'Taipei' OR city = 'Taichung' OR city = 'Kaohsiung' ``` --- ## 5. 文字模糊搜尋 ### `LIKE` ```sql SELECT * FROM users WHERE name LIKE '王%'; ``` 常見萬用字元： - `%`：任意數量的字元 - `_`：單一字元 例如： ```sql -- 名稱中包含「明」 SELECT * FROM users WHERE name LIKE '%明%'; ``` ### PostgreSQL 的不分大小寫搜尋：`ILIKE` ```sql SELECT * FROM users WHERE email ILIKE '%example.com'; ``` --- ## 6. 判斷 `NULL` 不能使用： ```sql WHERE phone = NULL ``` 應使用 `IS NULL` 或 `IS NOT NULL`： ```sql SELECT * FROM users WHERE phone IS NULL; ``` ```sql SELECT * FROM users WHERE phone IS NOT NULL; ``` --- ## 7. 排序資料 使用 `ORDER BY`： ```sql SELECT id, name, created_at FROM users ORDER BY created_at DESC; ``` - `ASC`：升冪，預設值 - `DESC`：降冪 多欄位排序： ```sql SELECT * FROM products ORDER BY category ASC, price DESC; ``` --- ## 8. 限制筆數與分頁 ### `LIMIT` ```sql SELECT * FROM users LIMIT 10; ``` ### `OFFSET` ```sql SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; ``` 表示跳過前 20 筆，再取得 10 筆。 --- ## 9. 去除重複值 ```sql SELECT DISTINCT city FROM users; ``` 多個欄位也可以： ```sql SELECT DISTINCT city, status FROM users; ``` --- ## 10. 計算與彙總 ### 計算筆數 ```sql SELECT COUNT(*) AS total_users FROM users; ``` ### 平均、總和、最大值、最小值 ```sql SELECT AVG(price) AS average_price, SUM(stock) AS total_stock, MAX(price) AS highest_price, MIN(price) AS lowest_price FROM products; ``` ### 分組統計：`GROUP BY` ```sql SELECT city, COUNT(*) AS user_count FROM users GROUP BY city; ``` ### 篩選分組結果：`HAVING` ```sql SELECT city, COUNT(*) AS user_count FROM users GROUP BY city HAVING COUNT(*) >= 10; ``` `WHERE` 是分組前篩選，`HAVING` 是分組後篩選。 --- ## 11. 連接多個資料表 假設： - `orders.user_id` 對應 `users.id` ```sql SELECT users.name, orders.id AS order_id, orders.amount FROM users JOIN orders ON orders.user_id = users.id; ``` 常見連接類型： ```sql INNER JOIN -- 只保留兩邊都有對應的資料 LEFT JOIN -- 保留左表全部資料 RIGHT JOIN -- 保留右表全部資料 FULL JOIN -- 保留兩邊全部資料 ``` 例如取得所有使用者，即使沒有訂單： ```sql SELECT users.name, orders.id AS order_id FROM users LEFT JOIN orders ON orders.user_id = users.id; ``` --- ## 12. 一個完整查詢範例 ```sql SELECT u.id, u.name, u.email, COUNT(o.id) AS order_count FROM users AS u LEFT JOIN orders AS o ON o.user_id = u.id WHERE u.status = 'active' AND u.created_at >= DATE '2024-01-01' GROUP BY u.id, u.name, u.email HAVING COUNT(o.id) > 0 ORDER BY order_count DESC LIMIT 20; ``` 這段查詢會： 1. 取得啟用中的使用者 2. 只篩選 2024 年後建立的使用者 3. 統計每位使用者的訂單數 4. 排除沒有訂單的使用者 5. 按訂單數由多到少排序 6. 只取前 20 筆 --- ## 13. 常見查詢順序 SQL 通常依照以下順序撰寫： ```sql SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY LIMIT OFFSET ``` 例如： ```sql SELECT 欄位 FROM 資料表 WHERE 條件 GROUP BY 分組欄位 HAVING 分組條件 ORDER BY 排序欄位 LIMIT 筆數; ``` --- ## 14. 使用參數避免 SQL Injection 應避免直接把使用者輸入字串拼接到 SQL 中： ```sql -- 不建議 'SELECT * FROM users WHERE name = ''' || user_input || '''' ``` 應使用參數化查詢，例如 PostgreSQL 預備語句： ```sql PREPARE find_user(text) AS SELECT id, name, email FROM users WHERE name = $1; EXECUTE find_user('王小明'); ``` 在應用程式中，也應使用對應資料庫函式庫提供的參數綁定功能。