PostgreSQL 查詢語法基礎教學

開發環境準備

• PostgreSQL 16.1
• pgAdmin 4
• DBeaver Community
  • 跨平台資料庫工具
  • 支援多種資料庫
  • 免費開源

參考資源

• 官方文件：https://www.postgresql.org/docs/current/
• SQL 語法速查：https://www.postgresqltutorial.com/
• 影片教學：https://www.youtube.com/watch?v=qw--VYLpxG4

基礎查詢語法

SELECT 陳述式基礎

1. 查詢所有欄位

   SELECT * FROM employees;
   

2. 查詢特定欄位

   SELECT first_name, last_name, salary FROM employees;
   

3. 使用別名

   SELECT first_name AS name, last_name AS surname FROM employees;
   

WHERE 條件篩選

1. 基本比較運算子

   SELECT * FROM employees WHERE salary > 5000;
   SELECT * FROM products WHERE price BETWEEN 10 AND 20;
   SELECT * FROM customers WHERE country IN ('USA', 'Canada', 'UK');
   

2. 模糊比對

   SELECT * FROM employees WHERE last_name LIKE 'S%';
   SELECT * FROM products WHERE description LIKE '%organic%';
   

3. NULL 值處理

   SELECT * FROM employees WHERE manager_id IS NULL;
   SELECT * FROM orders WHERE shipping_date IS NOT NULL;
   

排序與限制結果

1. ORDER BY 陳述式

   SELECT * FROM employees ORDER BY salary DESC;
   SELECT * FROM products ORDER BY category ASC, price DESC;
   

2. LIMIT 與 OFFSET

   SELECT * FROM employees ORDER BY salary DESC LIMIT 10;
   SELECT * FROM employees ORDER BY hire_date LIMIT 5 OFFSET 10;
   

進階查詢技巧

聚合函式

1. 常用聚合函式

   SELECT COUNT(*) FROM employees;
   SELECT AVG(salary) FROM employees;
   SELECT MAX(price), MIN(price) FROM products;
   SELECT SUM(amount) FROM orders;
   

2. GROUP BY 分組

   SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
   SELECT category, AVG(price) AS avg_price FROM products GROUP BY category;
   

3. HAVING 篩選分組

   SELECT department_id, AVG(salary) FROM employees 
   GROUP BY department_id HAVING AVG(salary) > 5000;
   

多資料表查詢

1. 資料表聯結

   SELECT e.first_name, e.last_name, d.name AS department
   FROM employees e
   JOIN departments d ON e.department_id = d.id;
   

2. 不同類型的聯結

   -- 内连接(INNER JOIN)
   SELECT c.name, o.order_date
   FROM customers c
   INNER JOIN orders o ON c.id = o.customer_id;
   
   -- 左连接(LEFT JOIN)
   SELECT c.name, o.order_date
   FROM customers c
   LEFT JOIN orders o ON c.id = o.customer_id;
   
   -- 右连接(RIGHT JOIN)
   SELECT e.first_name, d.name
   FROM employees e
   RIGHT JOIN departments d ON e.department_id = d.id;
   

PostgreSQL 特有功能

資料型別與運算子

1. JSON 操作

   SELECT data->>'name' AS customer_name 
   FROM orders WHERE data->>'status' = 'delivered';
   

2. 陣列操作

   SELECT * FROM products WHERE tags @> ARRAY['organic'];
   SELECT ARRAY_LENGTH(phone_numbers, 1) FROM contacts;
   

3. 文字搜尋

   SELECT * FROM articles
   WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & tutorial');
   

常用函式

1. 字串函式

   SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
   SELECT UPPER(name) FROM products;
   SELECT LENGTH(description) FROM products;
   

2. 日期函式

   SELECT NOW();
   SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*) 
   FROM orders GROUP BY month;
   SELECT AGE(NOW(), hire_date) FROM employees;
   

實用技巧

交易控制

1. 基本交易

   BEGIN;
   UPDATE accounts SET balance = balance - 100 WHERE id = 1;
   UPDATE accounts SET balance = balance + 100 WHERE id = 2;
   COMMIT;
   

2. 儲存點

   BEGIN;
   INSERT INTO orders (customer_id, amount) VALUES (1, 100);
   SAVEPOINT after_order;
   INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 2);
   ROLLBACK TO after_order;
   COMMIT;
   

效能最佳化

1. 索引使用

   CREATE INDEX idx_employees_last_name ON employees (last_name);
   EXPLAIN ANALYZE SELECT * FROM employees WHERE last_name = 'Smith';
   

2. 查詢計畫分析

   EXPLAIN ANALYZE
   SELECT c.name, COUNT(o.id) 
   FROM customers c
   JOIN orders o ON c.id = o.customer_id
   GROUP BY c.name;