一、数据库与模式管理

1. CREATE DATABASE — 创建数据库

-- 创建数据库，指定编码和属主
CREATE DATABASE mydb WITH ENCODING 'UTF8' OWNER = uxdb;
-- 使用不同的语言环境创建
CREATE DATABASE music2 LC_COLLATE 'sv_SE.iso885915' LC_CTYPE 'sv_SE.iso885915' 
    ENCODING LATIN9 TEMPLATE template0;

2. DROP DATABASE — 删除数据库

DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;  -- 存在则删除，避免报错

3. CREATE SCHEMA — 创建模式

CREATE SCHEMA myschema;
CREATE SCHEMA myschema AUTHORIZATION uxdb;  -- 指定属主

4. DROP SCHEMA — 删除模式

DROP SCHEMA myschema;
DROP SCHEMA myschema CASCADE;  -- 级联删除模式及其所有对象

5. SET SEARCH_PATH — 设置模式搜索路径

SET search_path TO myschema, public;

二、表与存储模型管理

6. CREATE TABLE — 创建标准表

CREATE TABLE products (
    product_no   integer,
    name         text,
    price        numeric,
    created_at   timestamp DEFAULT CURRENT_TIMESTAMP
);

7. DROP TABLE — 删除表

DROP TABLE products;
DROP TABLE IF EXISTS products;  -- 存在则删除
DROP TABLE products CASCADE;    -- 级联删除依赖对象

8. ALTER TABLE — 修改表结构

-- 添加列
ALTER TABLE products ADD COLUMN description text;
-- 删除列
ALTER TABLE products DROP COLUMN description;
-- 修改列数据类型
ALTER TABLE products ALTER COLUMN price TYPE decimal(10,2);
-- 重命名列
ALTER TABLE products RENAME COLUMN price TO unit_price;
-- 重命名表
ALTER TABLE products RENAME TO merchandise;
-- 设置/删除默认值
ALTER TABLE products ALTER COLUMN price SET DEFAULT 0.00;
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
-- 添加/删除非空约束
ALTER TABLE products ALTER COLUMN name SET NOT NULL;
ALTER TABLE products ALTER COLUMN name DROP NOT NULL;

9. TRUNCATE — 快速清空表

TRUNCATE TABLE products;
TRUNCATE TABLE products CASCADE;  -- 级联清空相关表

10. CREATE TABLE ... PARTITION BY — 创建分区表

-- 创建分区主表（按RANGE范围分区）
CREATE TABLE measurement (
    city_id     int NOT NULL,
    logdate     date NOT NULL,
    peaktemp    int,
    unitsales   int
) PARTITION BY RANGE (logdate);

-- 创建分区（按月）
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

-- 列表分区示例
CREATE TABLE log_par (
    id serial,
    user_id int4,
    create_time timestamp(0) without time zone
) PARTITION BY RANGE(create_time);

-- 创建分区并指定表空间
CREATE TABLE measurement_y2007m12 PARTITION OF measurement
    FOR VALUES FROM ('2007-12-01') TO ('2008-01-01')
    TABLESPACE fasttablespace;

11. CREATE GLOBAL TEMPORARY TABLE — 创建全局临时表

-- 全局临时表（数据在会话期间可见）
CREATE GLOBAL TEMPORARY TABLE temp_orders (
    order_id    NUMBER,
    order_date  DATE,
    amount      NUMBER(10,2)
) ON COMMIT PRESERVE ROWS;  -- 提交事务后保留数据

-- 会话级临时表（默认）
CREATE TEMP TABLE temp_data (
    id int,
    name text
);

12. CREATE TABLE ... WITH (orientation=column) — 创建列存表

-- 创建普通列存表
CREATE TABLE table_name (id int, name text, age int)
    WITH (orientation=column, appendonly=true);

-- 创建带约束的列存表
CREATE TABLE products (
    product_no int,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
) WITH (orientation=column, appendonly=true);

-- 创建带压缩的列存表
CREATE TABLE tbl_col_compressed (
    id int, name text, age int
) WITH (
    orientation=column,
    appendonly=true,
    compresstype=zlib,
    compresslevel=5
);
-- compresstype可选zlib或RLE_TYPE，compresslevel: zlib为1~9，RLE_TYPE为1~4

13. COMMENT — 添加注释

COMMENT ON TABLE products IS '产品信息表';
COMMENT ON COLUMN products.price IS '产品单价，单位：元';
COMMENT ON INDEX idx_products_price IS '按价格查询的索引';

三、完整性约束

14. PRIMARY KEY — 主键约束

-- 建表时定义
CREATE TABLE products (product_no integer PRIMARY KEY, name text);
-- 单独添加
ALTER TABLE products ADD PRIMARY KEY (product_no);

15. FOREIGN KEY — 外键约束

-- 建表时定义
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products(product_no)
);
-- 单独添加（含级联删除）
ALTER TABLE orders ADD CONSTRAINT fk_product
    FOREIGN KEY (product_no) REFERENCES products(product_no) ON DELETE CASCADE;

16. UNIQUE — 唯一约束

ALTER TABLE products ADD CONSTRAINT unique_name UNIQUE (name);

17. CHECK — 检查约束

ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);

18. DEFAULT — 默认值约束

CREATE TABLE users (
    id serial,
    status text DEFAULT 'active',
    created_at timestamp DEFAULT CURRENT_TIMESTAMP
);

19. ALTER TABLE ... DROP CONSTRAINT — 删除约束

ALTER TABLE products DROP CONSTRAINT unique_name;
ALTER TABLE products DROP CONSTRAINT price_positive;

四、序列与自增列

20. CREATE SEQUENCE — 创建序列

CREATE SEQUENCE product_seq START 1000 INCREMENT 1;
CREATE SEQUENCE order_seq START 1 INCREMENT 1 MINVALUE 1 MAXVALUE 99999;

21. ALTER SEQUENCE / setval() — 修改/设置序列值

ALTER SEQUENCE product_seq RESTART WITH 2000;
SELECT setval('product_seq', 2000);        -- 设置当前值
SELECT setval('product_seq', 2000, true);  -- 设置当前值并立即生效

22. DROP SEQUENCE — 删除序列

DROP SEQUENCE product_seq;
DROP SEQUENCE IF EXISTS product_seq;

五、索引高级管理

23. CREATE INDEX — 创建普通索引

CREATE INDEX idx_products_price ON products (price);

24. CREATE UNIQUE INDEX — 创建唯一索引

CREATE UNIQUE INDEX idx_products_no ON products (product_no);

25. CREATE INDEX CONCURRENTLY — 在线创建索引

-- 不阻塞读写操作，适用于生产环境
CREATE INDEX CONCURRENTLY idx_products_price ON products (price);

26. CREATE INDEX ... ON (function) — 创建函数索引

-- 基于表达式/函数的索引
CREATE INDEX idx_lower_name ON products (LOWER(name));
-- 部分索引（只索引满足条件的行）
CREATE INDEX idx_active_products ON products (price) WHERE status = 'active';

27. DROP INDEX — 删除索引

DROP INDEX idx_products_price;
DROP INDEX IF EXISTS idx_products_price;

28. REINDEX — 重建索引

REINDEX INDEX idx_products_price;   -- 重建单个索引
REINDEX TABLE products;             -- 重建表上所有索引
REINDEX DATABASE mydb;              -- 重建整个数据库的索引

六、视图与物化视图

29. CREATE VIEW — 创建视图

CREATE VIEW cheap_products AS
SELECT product_no, name, price FROM products WHERE price < 10;

30. CREATE OR REPLACE VIEW — 创建或替换视图

CREATE OR REPLACE VIEW cheap_products AS
SELECT product_no, name, price, category FROM products WHERE price < 10;

31. CREATE MATERIALIZED VIEW — 创建物化视图

CREATE MATERIALIZED VIEW product_summary AS
SELECT category, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM products GROUP BY category;

32. REFRESH MATERIALIZED VIEW — 刷新物化视图

REFRESH MATERIALIZED VIEW product_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY product_summary;  -- 不阻塞查询

33. DROP VIEW — 删除视图

DROP VIEW cheap_products;
DROP VIEW IF EXISTS cheap_products;
DROP MATERIALIZED VIEW product_summary;

七、外部数据集成（异构数据库互联）

34. CREATE SERVER — 创建外部数据服务器

-- 创建连接远程Oracle的外部服务器
CREATE SERVER orcl_server FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (host '192.168.1.100', port '1521', dbname 'ORCL');

35. CREATE FOREIGN TABLE — 创建外部表

-- 映射远程Oracle数据库中的表
CREATE FOREIGN TABLE remote_products (
    product_no integer,
    name       text,
    price      numeric
) SERVER orcl_server OPTIONS (table_name 'PRODUCTS');
-- 外部表支持SELECT、INSERT、UPDATE、DELETE等操作

36. CREATE DATABASE LINK — 创建数据库链接

-- 创建数据库链接（Oracle兼容语法）
CREATE DATABASE LINK dblink1
    CONNECT TO uxdb IDENTIFIED BY 'password'
    USING 'host=192.168.1.101 port=5432 dbname=otherdb';

-- 通过dblink查询远程表
SELECT * FROM remote_table@dblink1;

-- 通过dblink进行DML操作
INSERT INTO remote_table@dblink1 (id, name) VALUES (1, 'test');
UPDATE remote_table@dblink1 SET name = 'updated' WHERE id = 1;

37. DROP SERVER / FOREIGN TABLE — 删除外部对象

DROP FOREIGN TABLE remote_products;
DROP SERVER orcl_server;
DROP DATABASE LINK dblink1;

八、数据操纵语言 DML

38. INSERT — 插入数据

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
-- 多行插入
INSERT INTO products (product_no, name, price) VALUES
    (2, 'Milk', 2.50), (3, 'Bread', 3.20);

39. UPDATE — 更新数据

UPDATE products SET price = 10.99 WHERE product_no = 1;
UPDATE products SET price = price * 1.1 WHERE price < 5;

40. DELETE — 删除数据

DELETE FROM products WHERE product_no = 1;
DELETE FROM products WHERE price < 1;

41. INSERT ... ON CONFLICT — 有则更新无则插入

INSERT INTO products (product_no, name, price)
VALUES (1, 'Cheese', 11.99)
ON CONFLICT (product_no) DO UPDATE SET price = EXCLUDED.price;

42. RETURNING — DML操作后返回数据

INSERT INTO products (product_no, name, price) VALUES (4, 'Butter', 4.50)
    RETURNING *;
UPDATE products SET price = price * 1.1 WHERE price < 5 RETURNING *;
DELETE FROM products WHERE price < 1 RETURNING *;

九、数据查询语言 DQL

43. SELECT — 基础查询

SELECT * FROM products;
SELECT name, price FROM products;
SELECT 1 + 1 AS result;

44. WHERE — 条件过滤

SELECT * FROM products WHERE price > 5;
SELECT * FROM products WHERE price BETWEEN 5 AND 10 AND name LIKE 'C%';

45. ORDER BY — 排序

SELECT * FROM products ORDER BY price DESC;
SELECT * FROM products ORDER BY price DESC, name ASC;

46. SELECT DISTINCT — 去重查询

SELECT DISTINCT name FROM products;
SELECT DISTINCT name, price FROM products;

47. LIMIT / OFFSET — 分页查询

SELECT * FROM products LIMIT 10;                 -- 第一页
SELECT * FROM products OFFSET 5 LIMIT 10;       -- 第二页

48. GROUP BY — 分组聚合

SELECT price, COUNT(*) FROM products GROUP BY price;
SELECT AVG(price), MAX(price), MIN(price) FROM products;

49. HAVING — 分组后条件过滤

SELECT price, COUNT(*) FROM products
GROUP BY price HAVING COUNT(*) > 1;

50. JOIN — 多表连接

-- 内连接
SELECT o.id, c.name FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- 左连接
SELECT c.name, o.id FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- 自连接
SELECT a.name AS employee, b.name AS manager
FROM employees a LEFT JOIN employees b ON a.manager_id = b.id;

51. 子查询

-- WHERE子查询
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
-- EXISTS子查询
SELECT * FROM products p
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.product_no = p.product_no);
-- IN子查询
SELECT * FROM products WHERE product_no IN (SELECT product_no FROM orders);

52. UNION / INTERSECT / EXCEPT — 集合操作

SELECT name FROM products UNION SELECT name FROM old_products;        -- 合并去重
SELECT name FROM products UNION ALL SELECT name FROM old_products;    -- 保留重复
SELECT product_no FROM products INTERSECT SELECT product_no FROM orders;  -- 交集
SELECT product_no FROM products EXCEPT SELECT product_no FROM discontinued; -- 差集

53. COALESCE / NULLIF — 空值处理

SELECT name, COALESCE(price, 0) AS price FROM products;  -- NULL替换为0
SELECT NULLIF(price, 0) FROM products;  -- price为0时返回NULL

54. LIKE / ILIKE — 模糊匹配

SELECT * FROM products WHERE name LIKE 'Che%';     -- 以Che开头，区分大小写
SELECT * FROM products WHERE name ILIKE 'che%';    -- 以che开头，不区分大小写
SELECT * FROM products WHERE name LIKE '%es%';     -- 包含es

55. 正则表达式

SELECT * FROM products WHERE regexp_like(name, '^[A-Z]');    -- 正则匹配
SELECT regexp_substr(name, '[A-Z][a-z]+') FROM products;     -- 正则提取
SELECT regexp_replace(description, 'old', 'new') FROM products; -- 正则替换

56. CASE — 条件表达式

SELECT name, price,
    CASE
        WHEN price < 5 THEN 'Cheap'
        WHEN price < 20 THEN 'Medium'
        ELSE 'Expensive'
    END AS category
FROM products;

十、高级SQL分析与层次查询

57. 窗口函数

-- 排名函数
SELECT name, price,
    RANK() OVER (ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num
FROM products;
-- 分区内排名
SELECT name, category, price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products;
-- 累计聚合
SELECT name, price,
    SUM(price) OVER (ORDER BY product_no) AS cumulative_sum
FROM products;

58. WITH (CTE) — 公共表表达式

WITH expensive_products AS (
    SELECT * FROM products WHERE price > 100
)
SELECT * FROM expensive_products;

59. WITH RECURSIVE — 递归CTE

-- 递归查询：获取ID=7及其所有父节点
WITH RECURSIVE r AS (
    SELECT * FROM address WHERE id = 7
    UNION ALL
    SELECT address.* FROM address, r WHERE address.id = r.fatherid
)
SELECT * FROM r ORDER BY id;

60. CREATE RECURSIVE VIEW — 创建递归视图

-- 生成1到100的数字序列
CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
    VALUES (1)
    UNION ALL
    SELECT n+1 FROM nums_1_100 WHERE n < 100;

61. CONNECT BY — Oracle风格层次查询

-- UXDB兼容Oracle的CONNECT BY语法
SELECT id, name, fatherid, LEVEL
FROM address
START WITH fatherid = 0
CONNECT BY PRIOR id = fatherid;

62. CAST — 数据类型转换

SELECT CAST(price AS varchar) FROM products;
SELECT price::varchar FROM products;
SELECT '2024-01-01'::date;

63. EXPLAIN — 查看SQL执行计划

EXPLAIN SELECT * FROM products WHERE price > 10;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE price > 10;

64. ANALYZE — 手动更新表统计信息

ANALYZE products;  -- 更新指定表统计信息
ANALYZE;           -- 更新当前数据库所有表统计信息

十一、全文检索

65. CREATE TEXT SEARCH CONFIGURATION — 创建全文检索配置

-- 安装中文分词扩展
CREATE EXTENSION zhparser;
-- 创建中文检索配置
CREATE TEXT SEARCH CONFIGURATION testzhcfg (PARSER = zhparser);
-- 增加标记映射
ALTER TEXT SEARCH CONFIGURATION testzhcfg
    ADD MAPPING FOR n,v,a,i,e,l WITH simple;

66. CREATE INDEX ... USING GIN — 创建GIN索引

-- 创建分词向量列
ALTER TABLE articles ADD COLUMN tsv_column tsvector;
UPDATE articles SET tsv_column = to_tsvector('testzhcfg', content);
-- 创建GIN索引加速全文检索
CREATE INDEX idx_tsv ON articles USING GIN (tsv_column);

67. @@ (tsquery) — 全文检索匹配

-- 全文检索查询
SELECT * FROM articles t
JOIN articles_ts s ON t.id = s.id
WHERE s.tsv_column @@ to_tsquery('testzhcfg', '搜索关键词');

-- 按相关度排序
SELECT content,
    ts_rank_cd(to_tsvector('testzhcfg', content),
               to_tsquery('testzhcfg', '关键词')) AS rank
FROM articles
ORDER BY rank DESC LIMIT 5;

十二、事务、并发与预备语句

68. BEGIN / COMMIT / ROLLBACK — 事务控制

BEGIN;
UPDATE products SET price = price * 1.1;
COMMIT;   -- 提交事务
-- 或 ROLLBACK;  -- 回滚事务

69. SAVEPOINT — 设置与回滚到保存点

BEGIN;
UPDATE products SET price = 10 WHERE product_no = 1;
SAVEPOINT sp1;
UPDATE products SET price = 20 WHERE product_no = 2;
ROLLBACK TO SAVEPOINT sp1;  -- 回滚到保存点，保留之前的更改
COMMIT;

70. SET TRANSACTION ISOLATION LEVEL — 设置隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

71. LOCK — 显式锁表

LOCK TABLE products IN ACCESS EXCLUSIVE MODE;
LOCK TABLE products IN ROW SHARE MODE;

72. PREPARE / EXECUTE / DEALLOCATE — 预备语句

PREPARE get_product(int) AS SELECT * FROM products WHERE product_no = $1;
EXECUTE get_product(1);
DEALLOCATE get_product;
DEALLOCATE ALL;  -- 释放所有预备语句

十三、PL/SQL 服务器端编程

73. CREATE FUNCTION — 创建自定义函数

-- 基本函数
CREATE FUNCTION add(a integer, b integer) RETURNS integer AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plsql;

-- 返回表的函数
CREATE FUNCTION get_products_by_price(min_price numeric)
RETURNS TABLE(product_no integer, name text, price numeric) AS $$
BEGIN
    RETURN QUERY SELECT p.product_no, p.name, p.price
                 FROM products p WHERE p.price >= min_price;
END;
$$ LANGUAGE plsql;

74. CREATE PROCEDURE — 创建存储过程

CREATE PROCEDURE update_prices(percentage numeric) AS $$
BEGIN
    UPDATE products SET price = price * (1 + percentage / 100);
    COMMIT;
END;
$$ LANGUAGE plsql;

75. CREATE TRIGGER — 创建触发器

-- 先创建触发器函数
CREATE FUNCTION log_price_change() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO price_log (product_no, old_price, new_price, change_time)
    VALUES (OLD.product_no, OLD.price, NEW.price, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plsql;

-- 创建触发器
CREATE TRIGGER trg_price_change
    AFTER UPDATE OF price ON products
    FOR EACH ROW
    EXECUTE FUNCTION log_price_change();

76. DROP FUNCTION / PROCEDURE / TRIGGER — 删除对应对象

DROP FUNCTION add(integer, integer);
DROP PROCEDURE update_prices(numeric);
DROP TRIGGER trg_price_change ON products;

77. CALL — 调用存储过程

CALL update_prices(10.0);

78. RETURNS TABLE — 函数返回结果集

CREATE FUNCTION get_active_products()
RETURNS TABLE(id integer, name text, price numeric) AS $$
BEGIN
    RETURN QUERY SELECT product_no, name, price
                 FROM products WHERE status = 'active';
END;
$$ LANGUAGE plsql;

79. OUT / INOUT 参数 — 定义函数输出参数

CREATE FUNCTION get_product_info(
    IN p_id integer,
    OUT p_name text,
    OUT p_price numeric
) AS $$
BEGIN
    SELECT name, price INTO p_name, p_price
    FROM products WHERE product_no = p_id;
END;
$$ LANGUAGE plsql;

十四、游标

80. DECLARE CURSOR — 声明游标

DECLARE product_cursor CURSOR FOR SELECT * FROM products WHERE price > 10;
-- 带WITH HOLD：事务提交后游标仍可使用
DECLARE product_cursor CURSOR WITH HOLD FOR SELECT * FROM products;

81. FETCH / MOVE — 提取/移动游标数据

FETCH NEXT FROM product_cursor;
FETCH PRIOR FROM product_cursor;
FETCH FIRST 10 FROM product_cursor;
MOVE NEXT FROM product_cursor;  -- 移动游标但不返回数据

82. CLOSE — 关闭游标

CLOSE product_cursor;

十五、安全权限与行级访问控制

83. CREATE ROLE / USER — 创建角色/用户

CREATE USER user1 WITH PASSWORD 'password123';
CREATE ROLE admin WITH LOGIN PASSWORD 'adminpass';

84. ALTER ROLE / USER — 修改用户属性

ALTER USER user1 WITH PASSWORD 'newpassword';
ALTER USER user1 VALID UNTIL '2025-12-31';

85. DROP ROLE / USER — 删除角色/用户

DROP USER user1;
DROP ROLE admin;

86. GRANT — 授予对象权限

GRANT SELECT ON products TO user1;
GRANT INSERT, UPDATE ON products TO user1;
GRANT ALL PRIVILEGES ON products TO user1;
GRANT USAGE ON SCHEMA myschema TO user1;

87. REVOKE — 撤销权限

REVOKE SELECT ON products FROM user1;
REVOKE ALL PRIVILEGES ON products FROM user1;

88. ALTER DEFAULT PRIVILEGES — 修改默认权限

-- 预设：用户user1未来创建的表，自动授予user2查询权限
ALTER DEFAULT PRIVILEGES FOR USER user1 IN SCHEMA public
    GRANT SELECT ON TABLES TO user2;

89. CREATE POLICY — 创建行级安全策略

-- 启用行级安全
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
-- 创建策略：用户只能看到自己部门的产品
CREATE POLICY user_products ON products
    USING (department = current_user);

90. ENABLE ROW LEVEL SECURITY — 启用/禁用行级安全

ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE products DISABLE ROW LEVEL SECURITY;

十六、数据批量导入导出

91. COPY — 服务端文件导入/导出

-- 从CSV导入
COPY products FROM '/path/to/products.csv' DELIMITER ',' CSV HEADER;
-- 导出到CSV
COPY products TO '/path/to/products.csv' DELIMITER ',' CSV HEADER;
-- 导出查询结果
COPY (SELECT * FROM products WHERE price > 10) TO '/path/to/expensive.csv' CSV;

92. \copy — 客户端本地文件导入/导出

-- psql客户端命令，文件在客户端本地
\copy products TO '/local/path/products.csv' CSV HEADER;
\copy products FROM '/local/path/products.csv' CSV HEADER;

十七、数据库运维与监控

93. VACUUM — 清理死元组

VACUUM products;              -- 清理指定表的死元组
VACUUM ANALYZE products;      -- 清理并更新统计信息
VACUUM FULL products;         -- 完全清理，回收更多空间（会锁表）

94. CHECKPOINT — 强制触发检查点

CHECKPOINT;  -- 将脏页刷入磁盘

95. CLUSTER — 按指定索引重排物理存储

CLUSTER products USING idx_products_price;  -- 按价格索引重排物理存储
CLUSTER products;  -- 使用之前已聚簇过的索引

96. 系统目录查询

-- 查看当前活动连接
SELECT pid, usename, application_name, client_addr, state, query
FROM pg_stat_activity;

-- 查看活跃查询
SELECT pid, usename, query, state, query_start
FROM pg_stat_activity WHERE state = 'active';

-- 查看当前锁
SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks WHERE NOT granted;

-- 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('products'));

97. 终止会话

-- 终止指定连接
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'user1';
-- 取消指定查询
SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid = 12345;

十八、高可用与逻辑复制

98. CREATE PUBLICATION — 创建发布节点

-- 发布指定表
CREATE PUBLICATION my_publication FOR TABLE products, orders;
-- 发布所有表
CREATE PUBLICATION all_tables_publication FOR ALL TABLES;
-- 发布指定表的指定列
CREATE PUBLICATION partial_publication FOR TABLE products (product_no, name);

99. CREATE SUBSCRIPTION — 创建订阅节点

-- 订阅远程发布
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=192.168.1.100 port=5432 dbname=mydb user=replication'
    PUBLICATION my_publication;

十九、系统级动态配置

100. ALTER SYSTEM — 修改全局系统配置

-- 修改共享缓冲区大小（部分参数需重启生效）
ALTER SYSTEM SET shared_buffers = '2GB';
-- 修改最大连接数
ALTER SYSTEM SET max_connections = 500;
-- 重新加载配置文件（使部分参数生效）
SELECT pg_reload_conf();
-- 查看当前配置
SHOW shared_buffers;