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;