PostgreSQL 基础查询 #

┌─────────────────────────────────────────────────────────────┐
│                    SELECT 语句结构                           │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│   SELECT [DISTINCT] columns                                 │
│   FROM table_name                                           │
│   [ WHERE conditions ]                                      │
│   [ GROUP BY columns ]                                      │
│   [ HAVING conditions ]                                     │
│   [ ORDER BY columns [ASC|DESC] ]                           │
│   [ LIMIT count [ OFFSET skip ] ];                          │
│                                                             │
│   执行顺序：                                                 │
│   1. FROM        确定数据源                                 │
│   2. WHERE       过滤行                                     │
│   3. GROUP BY    分组                                       │
│   4. HAVING      过滤分组                                   │
│   5. SELECT      选择列                                     │
│   6. DISTINCT    去重                                       │
│   7. ORDER BY    排序                                       │
│   8. LIMIT/OFFSET 分页                                      │
│                                                             │
└─────────────────────────────────────────────────────────────┘

-- 创建示例表
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE
);

INSERT INTO employees (name, department, salary, hire_date, is_active) VALUES
    ('Alice', 'Engineering', 80000.00, '2020-01-15', TRUE),
    ('Bob', 'Sales', 60000.00, '2021-03-20', TRUE),
    ('Charlie', 'Engineering', 90000.00, '2019-06-10', TRUE),
    ('Diana', 'Marketing', 70000.00, '2022-02-28', TRUE),
    ('Eve', 'Sales', 65000.00, '2021-08-05', FALSE),
    ('Frank', 'Engineering', 85000.00, '2020-11-01', TRUE);

-- 查询所有列
SELECT * FROM employees;

-- 输出：
--  id |  name   | department  |  salary  | hire_date  | is_active
-- ----+---------+-------------+----------+------------+-----------
--   1 | Alice   | Engineering | 80000.00 | 2020-01-15 | t
--   2 | Bob     | Sales       | 60000.00 | 2021-03-20 | t
--   3 | Charlie | Engineering | 90000.00 | 2019-06-10 | t
--   4 | Diana   | Marketing   | 70000.00 | 2022-02-28 | t
--   5 | Eve     | Sales       | 65000.00 | 2021-08-05 | f
--   6 | Frank   | Engineering | 85000.00 | 2020-11-01 | t

-- 查询指定列
SELECT name, department, salary FROM employees;

-- 使用列别名
SELECT 
    name AS employee_name,
    department AS dept,
    salary AS annual_salary
FROM employees;

-- 别名可以省略 AS
SELECT name employee_name, department dept FROM employees;

-- 使用表达式
SELECT 
    name,
    salary,
    salary * 1.1 AS salary_with_bonus,
    ROUND(salary / 12, 2) AS monthly_salary
FROM employees;

-- 查询不重复的部门
SELECT DISTINCT department FROM employees;

-- 输出：
--  department
-- -------------
--  Engineering
--  Sales
--  Marketing

-- 多列去重
SELECT DISTINCT department, is_active FROM employees;

-- 使用 DISTINCT ON（PostgreSQL 特有）
-- 返回每个部门薪资最高的员工
SELECT DISTINCT ON (department) 
    department,
    name,
    salary
FROM employees
ORDER BY department, salary DESC;

-- 输出：
--  department  |  name   |  salary
-- -------------+---------+----------
--  Engineering | Charlie | 90000.00
--  Marketing   | Diana   | 70000.00
--  Sales       | Eve     | 65000.00

-- 等于
SELECT * FROM employees WHERE department = 'Engineering';

-- 不等于
SELECT * FROM employees WHERE department != 'Sales';
SELECT * FROM employees WHERE department <> 'Sales';

-- 大于、小于
SELECT * FROM employees WHERE salary > 70000;
SELECT * FROM employees WHERE salary >= 70000;
SELECT * FROM employees WHERE salary < 80000;
SELECT * FROM employees WHERE salary <= 80000;

-- AND
SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 80000;

-- OR
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'Marketing';

-- NOT
SELECT * FROM employees WHERE NOT is_active;

-- 组合使用（注意优先级）
SELECT * FROM employees 
WHERE department = 'Engineering' 
  AND (salary > 80000 OR is_active = FALSE);

-- IN
SELECT * FROM employees 
WHERE department IN ('Engineering', 'Sales');

-- NOT IN
SELECT * FROM employees 
WHERE department NOT IN ('Engineering', 'Sales');

-- 子查询
SELECT * FROM employees 
WHERE department IN (
    SELECT DISTINCT department 
    FROM employees 
    WHERE salary > 75000
);

-- 范围查询（包含边界）
SELECT * FROM employees 
WHERE salary BETWEEN 60000 AND 80000;

-- 日期范围
SELECT * FROM employees 
WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';

-- NOT BETWEEN
SELECT * FROM employees 
WHERE salary NOT BETWEEN 60000 AND 80000;

-- % 匹配任意字符
SELECT * FROM employees WHERE name LIKE 'A%';     -- 以 A 开头
SELECT * FROM employees WHERE name LIKE '%e';     -- 以 e 结尾
SELECT * FROM employees WHERE name LIKE '%a%';    -- 包含 a

-- _ 匹配单个字符
SELECT * FROM employees WHERE name LIKE '_ob';    -- 第二个字符是 ob

-- ILIKE 不区分大小写
SELECT * FROM employees WHERE name ILIKE 'alice';

-- NOT LIKE
SELECT * FROM employees WHERE name NOT LIKE 'A%';

-- 使用正则表达式
SELECT * FROM employees WHERE name ~ '^A';        -- 以 A 开头
SELECT * FROM employees WHERE name ~* '^a';       -- 以 a 开头（不区分大小写）
SELECT * FROM employees WHERE name !~ '^A';       -- 不以 A 开头

-- IS NULL
SELECT * FROM employees WHERE department IS NULL;

-- IS NOT NULL
SELECT * FROM employees WHERE department IS NOT NULL;

-- NULL 比较的特殊性
SELECT NULL = NULL;      -- 结果是 NULL，不是 TRUE
SELECT NULL IS NULL;     -- 结果是 TRUE

-- COALESCE 处理 NULL
SELECT 
    name,
    COALESCE(department, 'Unassigned') AS dept,
    salary
FROM employees;

-- NULLIF 返回 NULL 如果相等
SELECT NULLIF(salary, 0);  -- 如果 salary 是 0，返回 NULL

-- 升序（默认）
SELECT * FROM employees ORDER BY salary;
SELECT * FROM employees ORDER BY salary ASC;

-- 降序
SELECT * FROM employees ORDER BY salary DESC;

-- 多列排序
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

-- 使用列位置
SELECT name, salary FROM employees 
ORDER BY 2 DESC;  -- 按第二列排序

-- NULL 值默认排在最后（升序）或最前（降序）
-- 显式指定 NULL 排序位置
SELECT * FROM employees 
ORDER BY salary DESC NULLS FIRST;

SELECT * FROM employees 
ORDER BY salary ASC NULLS LAST;

-- 按表达式结果排序
SELECT name, salary, salary * 1.1 AS bonus_salary
FROM employees
ORDER BY salary * 1.1 DESC;

-- 使用别名排序
SELECT name, salary * 1.1 AS bonus_salary
FROM employees
ORDER BY bonus_salary DESC;

-- 使用 CASE 排序
SELECT * FROM employees
ORDER BY 
    CASE department
        WHEN 'Engineering' THEN 1
        WHEN 'Sales' THEN 2
        ELSE 3
    END;

-- 限制返回行数
SELECT * FROM employees LIMIT 3;

-- 获取前 N 名
SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 3;

-- LIMIT 0 返回空结果（用于检查语法）
SELECT * FROM employees LIMIT 0;

-- 跳过前 2 行
SELECT * FROM employees 
ORDER BY id 
LIMIT 3 OFFSET 2;

-- 分页查询
-- 第 1 页（每页 10 条）
SELECT * FROM employees 
ORDER BY id 
LIMIT 10 OFFSET 0;

-- 第 2 页
SELECT * FROM employees 
ORDER BY id 
LIMIT 10 OFFSET 10;

-- 第 N 页
-- OFFSET = (page - 1) * page_size
SELECT * FROM employees 
ORDER BY id 
LIMIT 10 OFFSET 20;  -- 第 3 页

-- SQL 标准语法
SELECT * FROM employees 
ORDER BY salary DESC 
FETCH FIRST 3 ROWS ONLY;

-- 带 OFFSET
SELECT * FROM employees 
ORDER BY id 
OFFSET 2 ROWS 
FETCH FIRST 3 ROWS ONLY;

-- COUNT 计数
SELECT COUNT(*) FROM employees;                    -- 总行数
SELECT COUNT(department) FROM employees;           -- 非 NULL 行数
SELECT COUNT(DISTINCT department) FROM employees;  -- 不重复值数量

-- SUM 求和
SELECT SUM(salary) FROM employees;

-- AVG 平均值
SELECT AVG(salary) FROM employees;
SELECT ROUND(AVG(salary), 2) FROM employees;  -- 保留两位小数

-- MAX/MIN 最大/最小值
SELECT MAX(salary), MIN(salary) FROM employees;

-- 组合使用
SELECT 
    COUNT(*) AS total_count,
    COUNT(DISTINCT department) AS dept_count,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 2) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees;

-- 按部门分组统计
SELECT 
    department,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department;

-- 输出：
--  department  | employee_count | total_salary | avg_salary
-- -------------+----------------+--------------+------------
--  Engineering |              3 |    255000.00 |   85000.00
--  Sales       |              2 |    125000.00 |   62500.00
--  Marketing   |              1 |     70000.00 |   70000.00

-- 多列分组
SELECT 
    department,
    is_active,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department, is_active
ORDER BY department, is_active;

-- HAVING 过滤分组结果（WHERE 过滤行）
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

-- 平均薪资大于 70000 的部门
SELECT 
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

-- WHERE 和 HAVING 结合
SELECT 
    department,
    COUNT(*) AS count,
    AVG(salary) AS avg_salary
FROM employees
WHERE is_active = TRUE
GROUP BY department
HAVING COUNT(*) > 1
ORDER BY avg_salary DESC;

-- 聚合函数忽略 NULL 值
SELECT 
    COUNT(*) AS total,           -- 所有行
    COUNT(department) AS non_null, -- 非 NULL 行
    AVG(salary) AS avg           -- 忽略 NULL
FROM employees;

-- 使用 COALESCE 处理 NULL
SELECT AVG(COALESCE(salary, 0)) FROM employees;

SELECT 
    name,
    salary,
    salary + 10000 AS salary_plus_bonus,
    salary * 1.1 AS salary_with_increase,
    salary / 12 AS monthly_salary,
    salary % 1000 AS remainder
FROM employees;

SELECT 
    name,
    UPPER(name) AS upper_name,
    LOWER(name) AS lower_name,
    LENGTH(name) AS name_length,
    CONCAT(name, ' - ', department) AS full_info,
    name || ' (' || department || ')' AS formatted_name,
    SUBSTRING(name, 1, 3) AS short_name,
    LEFT(name, 3) AS first_three,
    RIGHT(name, 3) AS last_three,
    REPLACE(name, 'a', 'A') AS replaced_name,
    INITCAP(name) AS capitalized
FROM employees;

SELECT 
    name,
    hire_date,
    CURRENT_DATE AS today,
    CURRENT_TIMESTAMP AS now,
    AGE(hire_date) AS employment_duration,
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    EXTRACT(MONTH FROM hire_date) AS hire_month,
    DATE_TRUNC('year', hire_date) AS year_start,
    hire_date + INTERVAL '1 year' AS one_year_later,
    hire_date - INTERVAL '1 month' AS one_month_ago
FROM employees;

-- CASE WHEN
SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 80000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employees;

-- CASE 简单形式
SELECT 
    name,
    department,
    CASE department
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Sales' THEN 'Business'
        ELSE 'Other'
    END AS dept_type
FROM employees;

-- COALESCE 返回第一个非 NULL 值
SELECT 
    name,
    COALESCE(department, 'Unassigned') AS dept
FROM employees;

-- NULLIF 如果相等返回 NULL
SELECT NULLIF(salary, 0);

基础阶段
├── 数据库操作
├── 表操作
├── 数据插入
├── 数据更新
├── 数据删除
└── 基础查询（本文）

进阶阶段
├── 高级查询
└── 多表查询