一、DML 概述

DML（Data Manipulation Language，数据操作语言）用于对数据库中的数据进行增、删、改、查操作。DML 是日常开发中使用最频繁的 SQL 语句。

DML 的组成：

二、SELECT 查询语句

1. 基础查询

-- 最简单的查询
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

-- 使用列别名（AS 可省略）
SELECT 
    id AS user_id,
    username AS user_name,
    email AS email_address
FROM users;

-- 使用表达式
SELECT 
    id,
    username,
    age,
    age + 5 AS age_in_5_years,
    age * 2 AS double_age,
    age / 10 AS age_decade
FROM users;

-- 使用常量
SELECT 
    id,
    username,
    'active' AS status,
    CURRENT_DATE AS query_date
FROM users;

-- 去重查询
SELECT DISTINCT status FROM users;
SELECT DISTINCT department_id, role_id FROM employees;

-- 限制返回行数
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 分页，跳过20条

-- 排序
SELECT * FROM users ORDER BY created_at DESC;  -- 降序
SELECT * FROM users ORDER BY created_at ASC;   -- 升序（默认）
SELECT * FROM users ORDER BY age DESC, created_at ASC;  -- 多列排序

-- 使用 LIMIT 和 ORDER BY 获取最大值
SELECT * FROM orders ORDER BY total_amount DESC LIMIT 1;  -- 最大订单

2. WHERE 条件过滤

-- 比较运算符
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE age < 60;
SELECT * FROM users WHERE age <= 60;

-- 逻辑运算符
SELECT * FROM users 
WHERE age >= 18 AND age <= 60 AND status = 'active';

SELECT * FROM users 
WHERE status = 'active' OR status = 'pending';

SELECT * FROM users 
WHERE NOT status = 'deleted';

-- BETWEEN（范围包含边界）
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- IN（多值匹配）
SELECT * FROM users WHERE status IN ('active', 'pending', 'suspended');
SELECT * FROM users WHERE id IN (1, 2, 3, 5, 8, 13);

-- NOT IN
SELECT * FROM users WHERE status NOT IN ('deleted', 'banned');

-- LIKE 模糊匹配
SELECT * FROM users WHERE username LIKE 'a%';     -- 以 a 开头
SELECT * FROM users WHERE username LIKE '%son';   -- 以 son 结尾
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- 包含 @gmail.com
SELECT * FROM users WHERE username LIKE '_ohn';   -- 单字符通配符（John, Pohn）
SELECT * FROM users WHERE username LIKE '%\%%' ESCAPE '\';  -- 转义 % 字符

-- ILIKE（大小写不敏感）
SELECT * FROM users WHERE username ILIKE 'ALICE%';

-- SIMILAR TO（正则表达式风格）
SELECT * FROM users WHERE email SIMILAR TO '%@(gmail|yahoo|outlook)\.com';

-- POSIX 正则表达式
SELECT * FROM users WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- NULL 判断
SELECT * FROM users WHERE phone IS NULL;      -- 没有电话号码
SELECT * FROM users WHERE phone IS NOT NULL;  -- 有电话号码

-- 条件组合
SELECT * FROM users 
WHERE (status = 'active' OR status = 'pending')
  AND age BETWEEN 25 AND 40
  AND created_at >= '2024-01-01'
  AND email IS NOT NULL;

3. 聚合查询

-- 基础聚合函数
SELECT 
    COUNT(*) AS total_users,                    -- 总行数
    COUNT(email) AS email_count,                -- 非空邮箱数
    COUNT(DISTINCT status) AS unique_statuses,  -- 不同状态数
    AVG(age) AS avg_age,                        -- 平均年龄
    SUM(age) AS sum_age,                        -- 年龄总和
    MAX(age) AS max_age,                        -- 最大年龄
    MIN(age) AS min_age,                        -- 最小年龄
    STDDEV(age) AS age_stddev,                  -- 标准差
    VARIANCE(age) AS age_variance               -- 方差
FROM users;

-- 使用 FILTER 子句（条件聚合）
SELECT 
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'active') AS active_count,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending_count,
    AVG(age) FILTER (WHERE status = 'active') AS avg_active_age
FROM users;

-- GROUP BY 分组聚合
SELECT 
    status,
    COUNT(*) AS user_count,
    AVG(age) AS avg_age,
    MIN(created_at) AS first_created,
    MAX(created_at) AS last_created
FROM users
GROUP BY status;

-- 多列分组
SELECT 
    status,
    department_id,
    COUNT(*) AS count,
    AVG(age) AS avg_age
FROM users
GROUP BY status, department_id
ORDER BY status, department_id;

-- GROUP BY 表达式
SELECT 
    EXTRACT(YEAR FROM created_at) AS year,
    COUNT(*) AS signups
FROM users
GROUP BY EXTRACT(YEAR FROM created_at);

-- HAVING 过滤分组（WHERE 过滤行，HAVING 过滤分组）
SELECT 
    status,
    COUNT(*) AS user_count,
    AVG(age) AS avg_age
FROM users
GROUP BY status
HAVING COUNT(*) > 10          -- 用户数大于10的状态
   AND AVG(age) >= 18;        -- 平均年龄大于等于18

-- GROUPING SETS（多维度聚合）
SELECT 
    status,
    department_id,
    COUNT(*)
FROM users
GROUP BY GROUPING SETS (
    (status, department_id),  -- 详细分组
    (status),                  -- 仅状态
    (department_id),          -- 仅部门
    ()                        -- 总计
);

-- ROLLUP（层次聚合）
SELECT 
    COALESCE(department_id::TEXT, 'ALL') AS department,
    COALESCE(status, 'ALL') AS status,
    COUNT(*)
FROM users
GROUP BY ROLLUP (department_id, status);

-- CUBE（所有组合聚合）
SELECT 
    department_id,
    status,
    COUNT(*)
FROM users
GROUP BY CUBE (department_id, status);

4. 字符串函数

-- 字符串连接
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT first_name || ' ' || last_name AS full_name FROM users;
SELECT CONCAT_WS(' ', first_name, middle_name, last_name) FROM users;

-- 字符串长度
SELECT LENGTH(username) AS name_length FROM users;
SELECT CHAR_LENGTH(username) FROM users;  -- 字符数
SELECT OCTET_LENGTH(username) FROM users; -- 字节数

-- 大小写转换
SELECT UPPER(username), LOWER(username), INITCAP(username) FROM users;

-- 字符串截取
SELECT LEFT(username, 3) AS prefix FROM users;
SELECT RIGHT(username, 3) AS suffix FROM users;
SELECT SUBSTRING(username FROM 2 FOR 3) FROM users;
SELECT SUBSTRING(email FROM '@(.*)$') AS domain FROM users;

-- 字符串替换
SELECT REPLACE(email, 'gmail.com', 'outlook.com') FROM users;
SELECT TRANSLATE('hello world', 'l', 'X');  -- heXXo worXd
SELECT REGEXP_REPLACE(email, '@.*$', '@example.com') FROM users;

-- 字符串查找
SELECT POSITION('@' IN email) FROM users;
SELECT STRPOS(email, '@') FROM users;

-- 去除空格
SELECT TRIM('  hello  ') = 'hello';
SELECT LTRIM('  hello') = 'hello';
SELECT RTRIM('hello  ') = 'hello';
SELECT TRIM(BOTH 'x' FROM 'xxhelloxx') = 'hello';
SELECT TRIM(LEADING 'x' FROM 'xxhelloxx') = 'helloxx';
SELECT TRIM(TRAILING 'x' FROM 'xxhelloxx') = 'xxhello';

-- 填充字符串
SELECT LPAD('123', 5, '0') = '00123';
SELECT RPAD('123', 5, '0') = '12300';

-- 字符串反转
SELECT REVERSE('hello') = 'olleh';

-- 分割和拼接
SELECT STRING_AGG(username, ', ') FROM users;  -- 拼接
SELECT STRING_TO_ARRAY('a,b,c', ',') = '{a,b,c}';
SELECT UNNEST(STRING_TO_ARRAY('a,b,c', ','));

5. 日期时间函数

-- 获取当前时间
SELECT CURRENT_DATE;        -- 2024-01-15
SELECT CURRENT_TIME;        -- 14:30:25.123456
SELECT CURRENT_TIMESTAMP;   -- 2024-01-15 14:30:25.123456+08
SELECT NOW();               -- 同 CURRENT_TIMESTAMP
SELECT LOCALTIMESTAMP;      -- 不带时区
SELECT CURRENT_TIME(3);     -- 指定精度

-- 提取日期部分
SELECT 
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(HOUR FROM created_at) AS hour,
    EXTRACT(MINUTE FROM created_at) AS minute,
    EXTRACT(SECOND FROM created_at) AS second,
    EXTRACT(DOW FROM created_at) AS day_of_week,      -- 0=周日
    EXTRACT(DOY FROM created_at) AS day_of_year,
    EXTRACT(WEEK FROM created_at) AS week_number,
    EXTRACT(QUARTER FROM created_at) AS quarter
FROM users;

-- 快捷提取函数
SELECT 
    DATE_PART('year', created_at) AS year,
    DATE_PART('month', created_at) AS month,
    DATE_TRUNC('month', created_at) AS month_start,   -- 截断到月初
    DATE_TRUNC('day', created_at) AS day_start,
    DATE_TRUNC('hour', created_at) AS hour_start
FROM users;

-- 日期运算
SELECT 
    created_at + INTERVAL '1 day' AS tomorrow,
    created_at - INTERVAL '1 month' AS last_month,
    created_at + INTERVAL '2 hours 30 minutes' AS plus_time,
    AGE(created_at) AS age_from_now,                   -- 距离现在多久
    AGE('2024-12-31', created_at) AS age_to_date,
    (CURRENT_DATE - created_at::DATE) AS days_ago
FROM users;

-- 日期比较
SELECT * FROM users 
WHERE created_at > CURRENT_DATE - INTERVAL '30 days';

-- 生成日期序列
SELECT generate_series(
    '2024-01-01'::DATE, 
    '2024-12-31'::DATE, 
    '1 month'::INTERVAL
);

-- 计算两个日期之间的天数
SELECT 
    start_date,
    end_date,
    end_date - start_date AS days_diff,
    DATE_PART('day', end_date - start_date) AS exact_days
FROM date_ranges;

6. 条件表达式

-- CASE 表达式（两种语法）
-- 语法1：简单 CASE
SELECT 
    username,
    status,
    CASE status
        WHEN 'active' THEN '活跃用户'
        WHEN 'pending' THEN '待审核'
        WHEN 'suspended' THEN '已暂停'
        WHEN 'deleted' THEN '已删除'
        ELSE '未知状态'
    END AS status_cn
FROM users;

-- 语法2：搜索 CASE
SELECT 
    username,
    age,
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age BETWEEN 18 AND 30 THEN '青年'
        WHEN age BETWEEN 31 AND 60 THEN '中年'
        WHEN age > 60 THEN '老年'
        ELSE '年龄未知'
    END AS age_group
FROM users;

-- 在聚合中使用 CASE
SELECT 
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_count,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_sum
FROM users;

-- COALESCE（返回第一个非空值）
SELECT 
    username,
    COALESCE(phone, email, '无联系方式') AS contact
FROM users;

-- NULLIF（如果两个值相等则返回 NULL）
SELECT NULLIF(0, 0) = NULL;
SELECT NULLIF('admin', 'admin') = NULL;
-- 防止除零错误
SELECT amount / NULLIF(count, 0) AS avg_per_unit FROM orders;

-- GREATEST/LEAST（返回最大/最小值）
SELECT GREATEST(10, 20, 30) = 30;
SELECT LEAST(10, 20, 30) = 10;
SELECT 
    order_id,
    GREATEST(actual_price, discount_price, member_price) AS max_price,
    LEAST(actual_price, discount_price, member_price) AS min_price
FROM products;

三、INSERT 插入语句

1. 基础插入

-- 插入完整行（必须提供所有列）
INSERT INTO users VALUES (1, 'alice', 'alice@example.com', 25, 'active', NOW());

-- 指定列插入（推荐）
INSERT INTO users (username, email, age, status) 
VALUES ('bob', 'bob@example.com', 30, 'active');

-- 插入多行
INSERT INTO users (username, email, age, status) VALUES
    ('charlie', 'charlie@example.com', 28, 'active'),
    ('david', 'david@example.com', 35, 'pending'),
    ('eve', 'eve@example.com', 22, 'active');

-- 插入并返回插入的数据
INSERT INTO users (username, email, age) 
VALUES ('frank', 'frank@example.com', 29)
RETURNING id, username, created_at;

-- 返回所有插入的数据
INSERT INTO users (username, email, age) 
VALUES ('grace', 'grace@example.com', 31)
RETURNING *;

2. 从查询插入

-- 从另一个表插入数据
INSERT INTO inactive_users (user_id, username, email, deleted_at)
SELECT id, username, email, CURRENT_TIMESTAMP
FROM users
WHERE status = 'deleted';

-- 使用 CTE 插入
WITH new_users AS (
    SELECT * FROM (VALUES 
        ('john', 'john@example.com', 25),
        ('jane', 'jane@example.com', 28)
    ) AS t(username, email, age)
)
INSERT INTO users (username, email, age)
SELECT username, email, age FROM new_users
RETURNING *;

-- 批量插入（使用 unnest）
INSERT INTO users (username, email, age)
SELECT * FROM unnest(
    ARRAY['user1', 'user2', 'user3'],
    ARRAY['email1@ex.com', 'email2@ex.com', 'email3@ex.com'],
    ARRAY[20, 25, 30]
);

3. UPSERT（ON CONFLICT）

-- 基础 UPSERT：如果冲突则更新
INSERT INTO users (id, username, email, age)
VALUES (1, 'alice_new', 'alice_new@example.com', 26)
ON CONFLICT (id) 
DO UPDATE SET 
    username = EXCLUDED.username,
    email = EXCLUDED.email,
    age = EXCLUDED.age,
    updated_at = CURRENT_TIMESTAMP;

-- 冲突时不做任何操作
INSERT INTO users (id, username, email)
VALUES (1, 'alice', 'alice@example.com')
ON CONFLICT (id) DO NOTHING;

-- 复合唯一约束冲突处理
INSERT INTO user_roles (user_id, role_id, assigned_by)
VALUES (1, 3, 'admin')
ON CONFLICT (user_id, role_id) 
DO UPDATE SET 
    assigned_by = EXCLUDED.assigned_by,
    assigned_at = CURRENT_TIMESTAMP;

-- 使用 WHERE 子句的条件更新
INSERT INTO inventory (product_id, quantity)
VALUES (100, 50)
ON CONFLICT (product_id) 
DO UPDATE SET 
    quantity = inventory.quantity + EXCLUDED.quantity
WHERE inventory.quantity < 100;

-- 返回操作结果
INSERT INTO users (id, username, email)
VALUES (1, 'alice', 'alice@example.com')
ON CONFLICT (id) DO UPDATE 
SET username = EXCLUDED.username
RETURNING id, username, 
    CASE WHEN xmax = 0 THEN 'INSERTED' ELSE 'UPDATED' END AS operation;

四、UPDATE 更新语句

1. 基础更新

-- 更新单个列
UPDATE users SET status = 'suspended' WHERE id = 5;

-- 更新多个列
UPDATE users 
SET status = 'active', 
    updated_at = CURRENT_TIMESTAMP 
WHERE id = 10;

-- 使用表达式更新
UPDATE users SET age = age + 1 WHERE id = 1;
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';

-- 使用子查询更新
UPDATE orders 
SET status = 'completed'
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

-- 使用 FROM 子句更新（关联更新）
UPDATE orders 
SET status = u.status
FROM users u
WHERE orders.user_id = u.id 
  AND u.status = 'banned';

-- 更新并返回
UPDATE users 
SET status = 'active', updated_at = NOW()
WHERE id = 1
RETURNING id, username, status, updated_at;

-- 更新所有行（谨慎使用）
UPDATE users SET last_login = NULL;

2. 高级更新技巧

-- 使用 CTE 更新
WITH updated_users AS (
    SELECT id, status 
    FROM users 
    WHERE last_login < CURRENT_DATE - INTERVAL '90 days'
)
UPDATE users 
SET status = 'inactive'
FROM updated_users
WHERE users.id = updated_users.id
RETURNING users.id, users.status;

-- 使用 JSON 数据更新
UPDATE users 
SET settings = jsonb_set(settings, '{theme}', '"dark"')
WHERE id = 1;

-- 数组操作更新
UPDATE users 
SET tags = array_append(tags, 'vip')
WHERE id = 1;

UPDATE users 
SET tags = array_remove(tags, 'trial')
WHERE id = 2;

-- 条件更新使用 CASE
UPDATE products 
SET price = CASE 
    WHEN category = 'premium' THEN price * 1.2
    WHEN category = 'standard' THEN price * 1.1
    ELSE price * 1.05
END
WHERE active = true;

-- 使用 DEFAULT 恢复默认值
UPDATE users SET status = DEFAULT WHERE id = 999;

五、DELETE 删除语句

1. 基础删除

-- 删除特定行
DELETE FROM users WHERE id = 999;

-- 多条件删除
DELETE FROM logs 
WHERE created_at < CURRENT_DATE - INTERVAL '90 days'
  AND severity = 'debug';

-- 使用子查询删除
DELETE FROM orders 
WHERE user_id IN (
    SELECT id FROM users WHERE status = 'deleted'
);

-- 使用 USING 子句删除
DELETE FROM orders
USING users
WHERE orders.user_id = users.id 
  AND users.status = 'deleted';

-- 删除并返回
DELETE FROM temp_data 
WHERE processed = true
RETURNING id, data, processed_at;

-- 清空表（保留结构）
DELETE FROM logs;  -- 慢，可回滚
TRUNCATE TABLE logs;  -- 快，不可回滚（事务内可回滚）

2. 级联删除

-- 使用外键的 ON DELETE CASCADE
-- 删除用户时会自动删除其订单
DELETE FROM users WHERE id = 1;

-- 手动级联删除（没有外键时）
BEGIN;
DELETE FROM order_items WHERE order_id IN (
    SELECT id FROM orders WHERE user_id = 1
);
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;
COMMIT;

-- 使用 USING 删除关联数据
WITH deleted_users AS (
    DELETE FROM users 
    WHERE status = 'deleted'
    RETURNING id
)
DELETE FROM orders 
USING deleted_users
WHERE orders.user_id = deleted_users.id;

六、MERGE（PostgreSQL 15+）

-- MERGE 语法（插入或更新或删除）
MERGE INTO products AS target
USING product_updates AS source
ON target.id = source.id
WHEN MATCHED AND target.stock = 0 THEN
    DELETE  -- 无库存则删除
WHEN MATCHED THEN
    UPDATE SET 
        name = source.name,
        price = source.price,
        stock = target.stock + source.stock,
        updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
    INSERT (id, name, price, stock, created_at)
    VALUES (source.id, source.name, source.price, source.stock, CURRENT_TIMESTAMP)
RETURNING target.id, target.name, target.stock;

-- 使用 VALUES 子句的 MERGE
MERGE INTO accounts a
USING (VALUES (1, 100), (2, 200)) AS v(id, amount)
ON a.id = v.id
WHEN MATCHED THEN
    UPDATE SET balance = a.balance + v.amount
WHEN NOT MATCHED THEN
    INSERT (id, balance) VALUES (v.id, v.amount);

七、高级查询技巧

1. 子查询

-- 标量子查询（返回单值）
SELECT 
    username,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- 行子查询（返回一行）
SELECT * FROM users 
WHERE (age, status) = (SELECT 25, 'active');

-- 表子查询（返回多行多列）
SELECT u.username, o.order_count
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

-- EXISTS 子查询（存在性检查）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.total_amount > 1000
);

-- NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- IN 子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total_amount > 100);

-- ANY/SOME 子查询
SELECT * FROM users 
WHERE age > ANY (SELECT age FROM users WHERE status = 'vip');

-- ALL 子查询
SELECT * FROM users 
WHERE age > ALL (SELECT age FROM users WHERE status = 'vip');

2. 联合查询（UNION）

-- UNION（去重）
SELECT id, username, email FROM active_users
UNION
SELECT id, username, email FROM pending_users
ORDER BY id;

-- UNION ALL（不去重，性能更好）
SELECT id, username FROM users_2023
UNION ALL
SELECT id, username FROM users_2024;

-- INTERSECT（交集）
SELECT user_id FROM orders_online
INTERSECT
SELECT user_id FROM orders_offline;

-- EXCEPT（差集）
SELECT user_id FROM all_users
EXCEPT
SELECT user_id FROM banned_users;

3. 窗口函数

-- 排名函数
SELECT 
    username,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,      -- 唯一序号
    RANK() OVER (ORDER BY score DESC) AS rank,                -- 排名（有并列）
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank,    -- 密集排名
    NTILE(4) OVER (ORDER BY score DESC) AS quartile           -- 分桶
FROM scores;

-- 分区窗口
SELECT 
    department_id,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LAG/LEAD（前后行访问）
SELECT 
    date,
    sales,
    LAG(sales, 1) OVER (ORDER BY date) AS prev_day_sales,
    LAG(sales, 7) OVER (ORDER BY date) AS prev_week_sales,
    LEAD(sales, 1) OVER (ORDER BY date) AS next_day_sales,
    sales - LAG(sales, 1) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

-- FIRST_VALUE/LAST_VALUE
SELECT 
    department_id,
    employee_name,
    salary,
    FIRST_VALUE(employee_name) OVER (
        PARTITION BY department_id ORDER BY salary DESC
    ) AS highest_paid,
    LAST_VALUE(employee_name) OVER (
        PARTITION BY department_id ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_paid
FROM employees;

-- 聚合窗口函数
SELECT 
    department_id,
    employee_name,
    salary,
    SUM(salary) OVER (PARTITION BY department_id) AS dept_total,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
    salary * 100.0 / SUM(salary) OVER (PARTITION BY department_id) AS dept_percentage
FROM employees;

4. CTE（公共表表达式）

-- 普通 CTE
WITH active_users AS (
    SELECT id, username, email 
    FROM users 
    WHERE status = 'active'
),
user_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT 
    au.username,
    au.email,
    COALESCE(uo.order_count, 0) AS order_count
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id;

-- 递归 CTE（树形结构查询）
WITH RECURSIVE employee_tree AS (
    -- 基础查询：顶级节点
    SELECT 
        id, 
        name, 
        manager_id, 
        1 AS level,
        ARRAY[id] AS path
    FROM employees
    WHERE manager_id IS NULL
  
    UNION ALL
  
    -- 递归查询
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        et.level + 1,
        et.path || e.id
    FROM employees e
    JOIN employee_tree et ON e.manager_id = et.id
)
SELECT 
    id,
    name,
    level,
    repeat('  ', level - 1) || name AS indented_name,
    path
FROM employee_tree
ORDER BY path;

-- 递归查询：生成日期序列
WITH RECURSIVE date_series AS (
    SELECT '2024-01-01'::DATE AS date
    UNION ALL
    SELECT date + 1
    FROM date_series
    WHERE date < '2024-12-31'
)
SELECT * FROM date_series;

5. 横向连接（LATERAL）

-- LATERAL 子查询可以引用前面的列
SELECT 
    u.id,
    u.username,
    recent_orders.*
FROM users u
LEFT JOIN LATERAL (
    SELECT order_id, total_amount, created_at
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) recent_orders ON true
WHERE u.status = 'active';

-- 使用 LATERAL 进行行展开
SELECT 
    username,
    email_domain
FROM users,
LATERAL (SELECT SPLIT_PART(email, '@', 2)) AS domain(email_domain);

八、事务控制

-- 开始事务
BEGIN;
-- 或
START TRANSACTION;

-- 执行 DML 操作
INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 设置保存点
SAVEPOINT before_transfer;

-- 如果出错可以回滚到保存点
ROLLBACK TO SAVEPOINT before_transfer;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置事务隔离级别
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 执行操作
COMMIT;

-- 只读事务
BEGIN TRANSACTION READ ONLY;
SELECT * FROM users;
COMMIT;

-- 延迟事务（推迟约束检查）
BEGIN TRANSACTION DEFERRABLE;
-- 约束会在事务提交时检查
COMMIT;

九、DML 最佳实践

1. 批量操作优化

-- 批量插入使用多行 VALUES
INSERT INTO users (username, email) VALUES 
    ('user1', 'user1@ex.com'),
    ('user2', 'user2@ex.com'),
    ...  -- 最多 1000 行为宜

-- 使用 COPY 批量导入（最快）
COPY users (username, email, age) 
FROM '/path/to/users.csv' 
DELIMITER ',' CSV HEADER;

-- 批量更新使用 CTE
WITH updates AS (
    SELECT * FROM (VALUES 
        (1, 'new_email1@ex.com'),
        (2, 'new_email2@ex.com')
    ) AS t(id, email)
)
UPDATE users SET email = updates.email
FROM updates
WHERE users.id = updates.id;

2. 避免常见陷阱

-- 错误：在 WHERE 中使用列别名（不支持）
-- SELECT username, age * 2 AS double_age FROM users WHERE double_age > 40;  -- 错误

-- 正确：重复表达式
SELECT username, age * 2 AS double_age FROM users WHERE age * 2 > 40;

-- 或使用子查询
SELECT * FROM (
    SELECT username, age * 2 AS double_age FROM users
) t WHERE double_age > 40;

-- 错误：NULL 比较
SELECT * FROM users WHERE age = NULL;  -- 永远不会返回结果

-- 正确：使用 IS NULL
SELECT * FROM users WHERE age IS NULL;

-- 错误：除零错误
SELECT id, amount / count FROM statistics WHERE count = 0;  -- 错误

-- 正确：使用 NULLIF
SELECT id, amount / NULLIF(count, 0) FROM statistics;

3. 性能优化建议

-- 使用 EXPLAIN 分析查询
EXPLAIN (ANALYZE, BUFFERS, TIMING) 
SELECT * FROM users WHERE email = 'alice@example.com';

-- 避免 SELECT *
-- 错误
SELECT * FROM large_table WHERE id = 1;

-- 正确
SELECT id, name, status FROM large_table WHERE id = 1;

-- 使用 EXISTS 替代 IN（对于子查询）
-- 优化前
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- 优化后
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);