SELECT 语句用于从 PostgreSQL 数据库中查询数据，是 SQL 中最常用的查询语句。PostgreSQL 提供了强大的 SELECT 功能，包括基础查询、条件过滤、排序、分组、连接、多表查询、子查询等。

---

1. SELECT 语法

1.1 基本语法

SELECT column1, column2, ... 
FROM table_name;

• column1, column2, ...：要查询的列。
• table_name：查询的数据表。

如果要查询表中的所有列，可以使用 *：

SELECT * FROM table_name;

---

2. 基础查询示例

假设有一个 employees 表：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

插入示例数据：

INSERT INTO employees (name, age, department, salary) VALUES
('Alice', 30, 'HR', 5000.00),
('Bob', 25, 'IT', 6000.00),
('Charlie', 35, 'Finance', 7000.00);

执行查询：

SELECT * FROM employees;

结果：

 id |  name   | age | department | salary
----+---------+-----+------------+--------
  1 | Alice   |  30 | HR         | 5000.00
  2 | Bob     |  25 | IT         | 6000.00
  3 | Charlie |  35 | Finance    | 7000.00

---

3. 条件查询 (WHERE 子句)

WHERE 语句用于过滤数据：

SELECT * FROM employees WHERE age > 30;

查询 age > 30 的员工：

 id |  name   | age | department | salary
----+---------+-----+------------+--------
  3 | Charlie |  35 | Finance    | 7000.00

3.1 结合 AND 和 OR

SELECT * FROM employees WHERE age > 25 AND department = 'IT';

---

4. 排序查询 (ORDER BY)

ORDER BY 用于对结果进行排序：

SELECT * FROM employees ORDER BY salary DESC;

按 salary 降序排序：

 id |  name   | age | department | salary
----+---------+-----+------------+--------
  3 | Charlie |  35 | Finance    | 7000.00
  2 | Bob     |  25 | IT         | 6000.00
  1 | Alice   |  30 | HR         | 5000.00

---

5. 限制查询结果 (LIMIT 和 OFFSET)

5.1 限制返回的行数

SELECT * FROM employees LIMIT 2;

返回前 2 行数据。

5.2 跳过前几行

SELECT * FROM employees ORDER BY salary DESC LIMIT 2 OFFSET 1;

跳过 1 行，返回 2 行数据。

---

6. 计算总数 (COUNT)

查询员工总数：

SELECT COUNT(*) FROM employees;

 count
-------
     3

---

7. 分组查询 (GROUP BY 和 HAVING)

GROUP BY 用于按某列分组，HAVING 过滤分组后的结果。

统计每个部门的平均工资：

SELECT department, AVG(salary) FROM employees GROUP BY department;

 department |  avg  
------------+-------
 HR         | 5000.00
 IT         | 6000.00
 Finance    | 7000.00

查询平均工资大于 5500 的部门：

SELECT department, AVG(salary) AS avg_salary
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 5500;

---

8. 连接查询 (JOIN)

JOIN 用于连接多个表。假设有一个 departments 表：

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);
INSERT INTO departments (name) VALUES ('HR'), ('IT'), ('Finance');

employees 表新增 department_id：

ALTER TABLE employees ADD COLUMN department_id INT;
UPDATE employees SET department_id = 1 WHERE department = 'HR';
UPDATE employees SET department_id = 2 WHERE department = 'IT';
UPDATE employees SET department_id = 3 WHERE department = 'Finance';

8.1 内连接 (INNER JOIN)

查询员工和部门信息：

SELECT employees.name, employees.age, departments.name AS department 
FROM employees 
INNER JOIN departments ON employees.department_id = departments.id;

---

9. 子查询

查询比所有 HR 员工工资高的员工：

SELECT * FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department = 'HR');

---

10. DISTINCT 去重

查询不同的部门：

SELECT DISTINCT department FROM employees;

---

11. CASE 语句

为员工分类：

SELECT name, age, 
    CASE 
        WHEN age < 30 THEN 'Young'
        WHEN age BETWEEN 30 AND 40 THEN 'Middle-aged'
        ELSE 'Senior'
    END AS age_group
FROM employees;

---

12. SELECT INTO 复制表

创建 backup_employees 并复制数据：

SELECT * INTO backup_employees FROM employees;

---

13. WITH 公用表表达式 (CTE)

查询薪资高于平均工资的员工：

WITH high_salary_employees AS (
    SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)
)
SELECT * FROM high_salary_employees;

---

14. UNION 合并多个查询结果

查询所有员工的 name 和 department：

SELECT name, department FROM employees
UNION
SELECT 'John Doe', 'Admin';

---

15. 总结

SQL 语法	说明
SELECT * FROM table;	查询所有数据
SELECT column1, column2 FROM table;	查询指定列
SELECT * FROM table WHERE condition;	条件查询
SELECT * FROM table ORDER BY column ASC/DESC;	排序
SELECT * FROM table LIMIT n OFFSET m;	分页
SELECT COUNT(*) FROM table;	统计总数
SELECT column, AVG(value) FROM table GROUP BY column;	分组聚合
SELECT * FROM table1 INNER JOIN table2 ON condition;	连接查询
SELECT * FROM table WHERE column > (SELECT MAX(column) FROM table2);	子查询
SELECT DISTINCT column FROM table;	去重查询
WITH cte AS (...) SELECT * FROM cte;	公用表表达式

PostgreSQL SELECT 语句功能强大，适用于多种查询需求。更多详细内容请关注其他相关文章！