PostgreSQL 完全指南：从安装配置到高级管理

 1# 添加 PostgreSQL 官方仓库
 2sudo apt-get install wget ca-certificates
 3sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
 4wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
 5
 6# 安装 PostgreSQL 16
 7sudo apt-get update
 8sudo apt-get install postgresql-16 postgresql-client-16
 9
10# 启动服务
11sudo systemctl start postgresql
12sudo systemctl enable postgresql
13
14# 验证安装
15psql --version

 1# 安装 PostgreSQL 仓库
 2sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-pgdg.rpm
 3
 4# 安装 PostgreSQL 16
 5sudo dnf install -y postgresql16-server postgresql16
 6
 7# 初始化数据库
 8sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
 9
10# 启动服务
11sudo systemctl start postgresql-16
12sudo systemctl enable postgresql-16
13
14# 验证安装
15/usr/pgsql-16/bin/psql --version

1# 使用 Homebrew 安装
2brew install postgresql@16
3
4# 启动服务
5brew services start postgresql@16
6
7# 验证安装
8psql --version

 1-- 使用 SQL 命令创建数据库
 2CREATE DATABASE mydb;
 3
 4-- 指定所有者
 5CREATE DATABASE mydb OWNER postgres;
 6
 7-- 设置编码和字符集
 8CREATE DATABASE mydb
 9    OWNER postgres
10    ENCODING 'UTF8'
11    LC_COLLATE 'en_US.UTF-8'
12    LC_CTYPE 'en_US.UTF-8'
13    TEMPLATE template0;
14
15-- 查看数据库列表
16\l
17
18-- 连接到指定数据库
19\c mydb

 1# 创建数据库
 2createdb mydb
 3
 4# 连接到数据库
 5psql mydb
 6
 7# 以特定用户身份连接
 8psql -U postgres -d mydb
 9
10# 执行 SQL 文件
11psql -U postgres -d mydb -f schema.sql
12
13# 执行单条 SQL 命令
14psql -U postgres -d mydb -c "SELECT version();"

 1-- 重命名数据库
 2ALTER DATABASE mydb RENAME TO newdb;
 3
 4-- 更改所有者
 5ALTER DATABASE newdb OWNER TO new_owner;
 6
 7-- 修改数据库属性
 8ALTER DATABASE newdb SET default_tablespace = new_tablespace;
 9ALTER DATABASE newdb RESET default_tablespace;
10
11-- 设置连接限制
12ALTER DATABASE newdb CONNECTION LIMIT 100;

 1-- 删除数据库
 2DROP DATABASE mydb;
 3
 4-- 删除数据库（如果存在）
 5DROP DATABASE IF EXISTS mydb;
 6
 7-- 强制断开所有连接后删除
 8SELECT pg_terminate_backend(pid)
 9FROM pg_stat_activity
10WHERE datname = 'mydb'
11AND pid <> pg_backend_pid();
12
13DROP DATABASE mydb;

 1# 备份单个数据库
 2pg_dump -U postgres mydb > mydb_backup.sql
 3
 4# 备份时压缩
 5pg_dump -U postgres -Fc -f mydb_backup.dump mydb
 6
 7# 只备份结构
 8pg_dump -U postgres -s mydb > schema.sql
 9
10# 只备份数据
11pg_dump -U postgres -a mydb > data.sql
12
13# 备份特定表
14pg_dump -U postgres -t users -t orders mydb > tables_backup.sql

 1# 从 SQL 文件恢复
 2psql -U postgres -d newdb < mydb_backup.sql
 3
 4# 从自定义格式恢复
 5pg_restore -U postgres -d newdb mydb_backup.dump
 6
 7# 恢复时只恢复结构
 8pg_restore -U postgres -s -d newdb mydb_backup.dump
 9
10# 恢复特定表
11pg_restore -U postgres -t users -t orders -d newdb mydb_backup.dump

 1-- 创建用户（带密码）
 2CREATE USER alice WITH PASSWORD 'SecurePassword123!';
 3
 4-- 创建角色（不带登录权限）
 5CREATE ROLE readonly;
 6
 7-- 创建超级用户
 8CREATE USER admin WITH PASSWORD 'AdminPass123!' SUPERUSER;
 9
10-- 创建带属性的详细用户
11CREATE USER app_user WITH
12    PASSWORD 'AppPass123!'
13    CREATEDB
14    NOCREATEROLE
15    NOINHERIT
16    LOGIN
17    CONNECTION LIMIT 10;
18
19-- 创建角色并授予给其他角色
20CREATE GROUP developers WITH ROLE alice, bob;

 1-- 修改当前用户密码
 2ALTER USER CURRENT_USER WITH PASSWORD 'NewPassword123!';
 3
 4-- 修改指定用户密码
 5ALTER USER alice WITH PASSWORD 'NewSecurePassword!';
 6
 7-- 使用 psql 命令行修改
 8\password alice
 9-- 系统会提示输入新密码
10
11-- 设置密码过期
12ALTER USER alice VALID UNTIL '2026-12-31';

1# 密码加密方式（推荐 scram-sha-256）
2password_encryption = scram-sha-256
3
4# 可选值：
5# - md5：传统 MD5 加密
6# - scram-sha-256：更安全的加密方式（PostgreSQL 10+）

1sudo systemctl restart postgresql

 1-- 授予数据库连接权限
 2GRANT CONNECT ON DATABASE mydb TO alice;
 3
 4-- 授予模式使用权限
 5GRANT USAGE ON SCHEMA public TO alice;
 6
 7-- 授予表的所有权限
 8GRANT ALL PRIVILEGES ON TABLE users TO alice;
 9
10-- 授予表的特定权限
11GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE users TO alice;
12
13-- 授予序列权限
14GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO alice;
15
16-- 授予函数权限
17GRANT EXECUTE ON FUNCTION get_user_stats() TO alice;
18
19-- 授予模式下的所有表权限
20GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
21
22-- 授予未来创建的表权限（自动授权）
23ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

 1-- 撤销特定权限
 2REVOKE INSERT ON TABLE users FROM alice;
 3
 4-- 撤销所有权限
 5REVOKE ALL PRIVILEGES ON TABLE users FROM alice;
 6
 7-- 撤销角色权限
 8REVOKE readonly FROM alice;
 9
10-- 撤销默认权限
11ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM readonly;

 1-- 查看所有用户和角色
 2\du
 3
 4-- 查看当前用户权限
 5SELECT * FROM information_schema.role_table_grants
 6WHERE grantee = current_user;
 7
 8-- 查看表的权限
 9\dp table_name
10
11-- 查看数据库权限
12SELECT datname, datacl FROM pg_database WHERE datname = 'mydb';
13
14-- 查看角色成员
15SELECT * FROM pg_roles;

 1-- 创建组角色
 2CREATE ROLE developers NOLOGIN;
 3
 4-- 创建用户并加入组
 5CREATE USER bob WITH PASSWORD 'BobPass123!' IN ROLE developers;
 6CREATE USER charlie WITH PASSWORD 'CharliePass123!' IN ROLE developers;
 7
 8-- 将已有用户加入组
 9GRANT developers TO alice;
10
11-- 移除用户从组
12REVOKE developers FROM alice;
13
14-- 设置角色权限继承（NOINHERIT 需要显式启用）
15CREATE ROLE dba WITH NOINHERIT LOGIN;
16CREATE ROLE dev WITH INHERIT LOGIN;
17GRANT dba TO dev;
18
19-- 临时激活不继承的角色
20SET ROLE dba;

 1# 监听所有网络接口
 2listen_addresses = '*'
 3
 4# 监听特定 IP 地址
 5# listen_addresses = '192.168.1.100,127.0.0.1'
 6
 7# 修改默认端口（默认 5432）
 8port = 5432
 9
10# 最大连接数
11max_connections = 100

1sudo systemctl restart postgresql

 1# TYPE  DATABASE        USER            ADDRESS                 METHOD
 2
 3# 本地连接
 4local   all             postgres                                peer
 5local   all             all                                     md5
 6
 7# IPv4 本地回环
 8host    all             all             127.0.0.1/32            scram-sha-256
 9
10# IPv6 本地回环
11host    all             all             ::1/128                 scram-sha-256
12
13# 允许特定 IP 段访问（密码认证）
14host    all             all             192.168.1.0/24          scram-sha-256
15
16# 允许只读用户只读访问
17host    mydb            readonly        192.168.1.0/24          scram-sha-256
18
19# 允许 SSL 连接
20hostssl all             all             0.0.0.0/0               scram-sha-256
21
22# 拒绝特定 IP
23host    all             all             10.0.0.0/8              reject

1sudo systemctl reload postgresql

 1# 进入数据目录
 2cd /var/lib/postgresql/16/main/
 3
 4# 生成服务器私钥
 5openssl genrsa -out server.key 2048
 6
 7# 设置私钥权限
 8chmod 600 server.key
 9chown postgres:postgres server.key
10
11# 生成证书签名请求（CSR）
12openssl req -new -key server.key -out server.csr
13
14# 生成自签名证书（有效期 365 天）
15openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt

 1# 启用 SSL
 2ssl = on
 3
 4# SSL 证书路径
 5ssl_cert_file = 'server.crt'
 6
 7# SSL 私钥路径
 8ssl_key_file = 'server.key'
 9
10# SSL 协议版本
11ssl_protocols = 'TLSv1.2,TLSv1.3'
12
13# SSL 加密套件
14ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'

1# 允许 PostgreSQL 端口（默认 5432）
2sudo ufw allow 5432/tcp
3
4# 限制特定 IP 访问
5sudo ufw allow from 192.168.1.0/24 to any port 5432
6
7# 查看防火墙状态
8sudo ufw status

 1# 添加 PostgreSQL 服务
 2sudo firewall-cmd --permanent --add-service=postgresql
 3
 4# 或者添加端口
 5sudo firewall-cmd --permanent --add-port=5432/tcp
 6
 7# 限制来源 IP
 8sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="5432" accept'
 9
10# 重载防火墙
11sudo firewall-cmd --reload
12
13# 查看规则
14sudo firewall-cmd --list-all

1# 从远程主机连接
2psql -h 192.168.1.100 -p 5432 -U alice -d mydb
3
4# 使用 SSL 连接
5psql -h 192.168.1.100 -p 5432 -U alice -d mydb sslmode=require
6
7# 查看连接信息
8\conninfo

 1-- 基本表创建
 2CREATE TABLE users (
 3    id SERIAL PRIMARY KEY,
 4    username VARCHAR(50) NOT NULL UNIQUE,
 5    email VARCHAR(100) NOT NULL,
 6    age INTEGER CHECK (age >= 0 AND age <= 150),
 7    is_active BOOLEAN DEFAULT true,
 8    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 9);
10
11-- 带外键约束的表
12CREATE TABLE orders (
13    id BIGSERIAL PRIMARY KEY,
14    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
15    order_no VARCHAR(32) NOT NULL UNIQUE,
16    total_amount DECIMAL(10, 2) NOT NULL,
17    status VARCHAR(20) DEFAULT 'pending',
18    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
19);
20
21-- 创建带注释的表
22CREATE TABLE articles (
23    id BIGSERIAL PRIMARY KEY,
24    title VARCHAR(200) NOT NULL,
25    content TEXT,
26    author_id INTEGER REFERENCES users(id),
27    views INTEGER DEFAULT 0,
28    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
29    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
30);
31
32COMMENT ON TABLE articles IS '文章表';
33COMMENT ON COLUMN articles.title IS '文章标题';
34COMMENT ON COLUMN articles.content IS '文章内容';

 1-- 添加列
 2ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 3ALTER TABLE users ADD COLUMN avatar BYTEA;
 4
 5-- 添加带默认值的列
 6ALTER TABLE users ADD COLUMN last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
 7
 8-- 删除列
 9ALTER TABLE users DROP COLUMN phone;
10
11-- 重命名列
12ALTER TABLE users RENAME COLUMN username TO user_name;
13
14-- 修改列类型
15ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(150);
16
17-- 添加约束
18ALTER TABLE users ADD CONSTRAINT check_email CHECK (email LIKE '%@%');
19
20-- 添加唯一约束
21ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
22
23-- 添加外键约束
24ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
25
26-- 删除约束
27ALTER TABLE users DROP CONSTRAINT check_email;
28
29-- 设置列非空
30ALTER TABLE users ALTER COLUMN email SET NOT NULL;
31
32-- 取消列非空
33ALTER TABLE users ALTER COLUMN email DROP NOT NULL;
34
35-- 设置默认值
36ALTER TABLE users ALTER COLUMN age SET DEFAULT 18;
37
38-- 取消默认值
39ALTER TABLE users ALTER COLUMN age DROP DEFAULT;

 1-- 删除表
 2DROP TABLE orders;
 3
 4-- 删除表（如果存在）
 5DROP TABLE IF EXISTS orders;
 6
 7-- 级联删除相关表
 8DROP TABLE orders CASCADE;
 9
10-- 清空表数据（保留结构）
11TRUNCATE TABLE orders;
12
13-- 清空表并重置序列
14TRUNCATE TABLE orders RESTART IDENTITY CASCADE;

 1-- 查看所有表
 2\dt
 3
 4-- 查看指定表结构
 5\d users
 6
 7-- 查看表详细定义
 8\d+ users
 9
10-- 查看表约束
11\d+ orders
12
13-- 查询表信息
14SELECT
15    table_name,
16    column_name,
17    data_type,
18    is_nullable,
19    column_default
20FROM information_schema.columns
21WHERE table_name = 'users'
22ORDER BY ordinal_position;
23
24-- 查看表大小
25SELECT
26    pg_size_pretty(pg_total_relation_size('users')) AS total_size,
27    pg_size_pretty(pg_relation_size('users')) AS table_size,
28    pg_size_pretty(pg_indexes_size('users')) AS index_size;

1-- 重命名表
2ALTER TABLE old_table_name RENAME TO new_table_name;
3
4-- 重命名表（带模式）
5ALTER TABLE public.users RENAME TO user_profiles;

 1-- SMALLINT + 自增（范围：1 到 32767）
 2CREATE TABLE small_data (
 3    id SMALLSERIAL PRIMARY KEY,
 4    name VARCHAR(50)
 5);
 6
 7-- INTEGER + 自增（范围：1 到 2147483647）
 8CREATE TABLE users (
 9    id SERIAL PRIMARY KEY,
10    username VARCHAR(50)
11);
12
13-- BIGINT + 自增（范围：1 到 9223372036854775807）
14CREATE TABLE large_data (
15    id BIGSERIAL PRIMARY KEY,
16    content TEXT
17);

 1-- GENERATED ALWAYS AS IDENTITY（推荐）
 2CREATE TABLE products (
 3    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 4    name VARCHAR(100),
 5    price DECIMAL(10, 2)
 6);
 7
 8-- GENERATED BY DEFAULT AS IDENTITY（允许手动插入）
 9CREATE TABLE orders (
10    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
11    order_no VARCHAR(32),
12    total_amount DECIMAL(10, 2)
13);
14
15-- 带序列选项的 IDENTITY
16CREATE TABLE logs (
17    id BIGINT GENERATED ALWAYS AS IDENTITY (
18        START WITH 1000
19        INCREMENT BY 1
20        NO MINVALUE
21        NO MAXVALUE
22        CACHE 20
23    ) PRIMARY KEY,
24    message TEXT,
25    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
26);

 1-- 创建自定义序列
 2CREATE SEQUENCE user_id_seq
 3    START WITH 1
 4    INCREMENT BY 1
 5    NO MINVALUE
 6    NO MAXVALUE
 7    CACHE 1;
 8
 9-- 在表中使用序列
10CREATE TABLE users (
11    id INTEGER PRIMARY KEY DEFAULT nextval('user_id_seq'),
12    username VARCHAR(50)
13);
14
15-- 获取下一个序列值
16SELECT nextval('user_id_seq');
17
18-- 获取当前序列值
19SELECT currval('user_id_seq');
20
21-- 设置序列值
22SELECT setval('user_id_seq', 1000);
23
24-- 重置序列
25ALTER SEQUENCE user_id_seq RESTART WITH 1;
26
27-- 修改序列属性
28ALTER SEQUENCE user_id_seq INCREMENT BY 5;
29ALTER SEQUENCE user_id_seq CACHE 50;
30
31-- 查看序列
32\ds
33
34-- 删除序列
35DROP SEQUENCE IF EXISTS user_id_seq;

 1-- 方法1：使用 RESTART
 2ALTER SEQUENCE users_id_seq RESTART WITH 1;
 3
 4-- 方法2：使用 setval（设置为最大 ID + 1）
 5SELECT setval('users_id_seq', COALESCE(MAX(id), 0) + 1) FROM users;
 6
 7-- 方法3：重置所有序列为当前最大值
 8DO $$
 9DECLARE
10    table_name TEXT;
11    column_name TEXT;
12    sequence_name TEXT;
13BEGIN
14    FOR table_name, column_name, sequence_name IN
15        SELECT
16            t.table_name,
17            c.column_name,
18            s.sequence_name
19        FROM
20            information_schema.tables t
21            JOIN information_schema.columns c
22                ON t.table_name = c.table_name
23            JOIN information_schema.sequences s
24                ON c.column_default LIKE '%' || s.sequence_name || '%'
25        WHERE
26            t.table_schema = 'public'
27    LOOP
28        EXECUTE format('SELECT setval(%L, COALESCE(MAX(%I), 0) + 1) FROM %I',
29            sequence_name, column_name, table_name);
30    END LOOP;
31END $$;

 1-- 创建共享序列
 2CREATE SEQUENCE global_id_seq;
 3
 4-- 多个表使用同一序列
 5CREATE TABLE orders (
 6    id INTEGER PRIMARY KEY DEFAULT nextval('global_id_seq'),
 7    order_no VARCHAR(32)
 8);
 9
10CREATE TABLE invoices (
11    id INTEGER PRIMARY KEY DEFAULT nextval('global_id_seq'),
12    invoice_no VARCHAR(32)
13);
14
15-- 现在 orders 和 invoices 的 ID 不会冲突

 1-- 创建表空间（需要超级用户权限）
 2CREATE TABLESPACE fast_storage LOCATION '/mnt/fast_storage';
 3
 4-- 创建表空间并指定所有者
 5CREATE TABLESPACE slow_storage
 6    OWNER postgres
 7    LOCATION '/mnt/slow_storage';
 8
 9-- 查看表空间
10\db

1# 创建目录
2sudo mkdir -p /mnt/fast_storage
3
4# 设置权限（所有者是 postgres 用户）
5sudo chown postgres:postgres /mnt/fast_storage
6sudo chmod 700 /mnt/fast_storage

 1-- 在指定表空间创建表
 2CREATE TABLE large_data (
 3    id BIGSERIAL PRIMARY KEY,
 4    content TEXT,
 5    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 6) TABLESPACE slow_storage;
 7
 8-- 将表移动到表空间
 9ALTER TABLE large_data SET TABLESPACE fast_storage;
10
11-- 在指定表空间创建索引
12CREATE INDEX idx_large_data_created ON large_data(created_at)
13    TABLESPACE fast_storage;
14
15-- 将索引移动到表空间
16ALTER INDEX idx_large_data_created SET TABLESPACE slow_storage;

 1-- 重命名表空间
 2ALTER TABLESPACE fast_storage RENAME TO ssd_storage;
 3
 4-- 修改表空间所有者
 5ALTER TABLESPACE slow_storage OWNER TO admin;
 6
 7-- 查看表空间使用情况
 8SELECT
 9    spcname AS tablespace_name,
10    pg_size_pretty(pg_tablespace_size(spcname)) AS size
11FROM pg_tablespace
12WHERE spcname NOT LIKE 'pg_%'
13ORDER BY pg_tablespace_size(spcname) DESC;
14
15-- 查看表空间中的对象
16SELECT
17    schemaname,
18    tablename,
19    tablespace
20FROM pg_tables
21WHERE tablespace IS NOT NULL;

1-- 删除表空间（需要先移除所有对象）
2DROP TABLESPACE IF EXISTS slow_storage;

 1-- 场景1：分离热数据和冷数据
 2-- 热数据（频繁访问）放在 SSD
 3CREATE TABLE active_users (
 4    id SERIAL PRIMARY KEY,
 5    data TEXT
 6) TABLESPACE ssd_storage;
 7
 8-- 冷数据（归档数据）放在 HDD
 9CREATE TABLE archived_data (
10    id SERIAL PRIMARY KEY,
11    archive_content TEXT
12) TABLESPACE slow_storage;
13
14-- 场景2：分离索引和数据
15CREATE TABLE products (
16    id SERIAL PRIMARY KEY,
17    name VARCHAR(100),
18    description TEXT
19) TABLESPACE hdd_storage;
20
21-- 索引放在 SSD 提升查询性能
22CREATE INDEX idx_products_name ON products(name)
23    TABLESPACE ssd_storage;
24
25-- 场景3：不同用户数据分离
26CREATE TABLE user_a_data (
27    id SERIAL PRIMARY KEY,
28    data TEXT
29) TABLESPACE user_a_storage;
30
31CREATE TABLE user_b_data (
32    id SERIAL PRIMARY KEY,
33    data TEXT
34) TABLESPACE user_b_storage;

 1-- 单条插入
 2INSERT INTO users (username, email, age) VALUES ('alice', 'alice@example.com', 25);
 3
 4-- 多条插入
 5INSERT INTO users (username, email, age) VALUES
 6    ('bob', 'bob@example.com', 30),
 7    ('charlie', 'charlie@example.com', 28),
 8    ('diana', 'diana@example.com', 32);
 9
10-- 使用默认值插入
11INSERT INTO users (username, email) VALUES ('eve', 'eve@example.com');
12
13-- 从查询结果插入
14INSERT INTO users (username, email)
15SELECT username, email FROM temp_users WHERE status = 'active';
16
17-- 插入并返回 ID
18INSERT INTO users (username, email) VALUES ('frank', 'frank@example.com')
19RETURNING id;
20
21-- 插入或更新（UPSERT）
22INSERT INTO users (username, email, age) VALUES ('alice', 'new_email@example.com', 26)
23ON CONFLICT (username) DO UPDATE SET
24    email = EXCLUDED.email,
25    age = EXCLUDED.age
26RETURNING id;

 1-- 基本查询
 2SELECT * FROM users;
 3
 4-- 查询指定列
 5SELECT id, username, email FROM users;
 6
 7-- 条件查询
 8SELECT * FROM users WHERE age > 25;
 9
10-- 多条件查询
11SELECT * FROM users WHERE age >= 25 AND age <= 35;
12
13-- 范围查询
14SELECT * FROM users WHERE age BETWEEN 25 AND 35;
15
16-- IN 查询
17SELECT * FROM users WHERE username IN ('alice', 'bob', 'charlie');
18
19-- 模糊查询
20SELECT * FROM users WHERE username LIKE 'a%';
21SELECT * FROM users WHERE email LIKE '%@example.com';
22
23-- 排序
24SELECT * FROM users ORDER BY age ASC;
25SELECT * FROM users ORDER BY age DESC, username ASC;
26
27-- 限制结果数量
28SELECT * FROM users LIMIT 5;
29SELECT * FROM users LIMIT 5 OFFSET 2;
30
31-- 去重
32SELECT DISTINCT age FROM users;
33
34-- 聚合查询
35SELECT COUNT(*) FROM users;
36SELECT AVG(age) FROM users;
37SELECT MAX(age), MIN(age) FROM users;
38SELECT SUM(views) FROM articles;
39
40-- 分组查询
41SELECT age, COUNT(*) as count FROM users GROUP BY age HAVING count > 1;

 1-- 内连接
 2SELECT u.username, o.order_no, o.total_amount
 3FROM users u
 4INNER JOIN orders o ON u.id = o.user_id;
 5
 6-- 左连接
 7SELECT u.username, o.order_no
 8FROM users u
 9LEFT JOIN orders o ON u.id = o.user_id;
10
11-- 右连接
12SELECT u.username, o.order_no
13FROM users u
14RIGHT JOIN orders o ON u.id = o.user_id;
15
16-- 全连接
17SELECT u.username, o.order_no
18FROM users u
19FULL OUTER JOIN orders o ON u.id = o.user_id;
20
21-- 多表连接
22SELECT u.username, o.order_no, oi.quantity, p.name as product_name
23FROM users u
24JOIN orders o ON u.id = o.user_id
25JOIN order_items oi ON o.id = oi.order_id
26JOIN products p ON oi.product_id = p.id;

 1-- 更新单条记录
 2UPDATE users SET email = 'new_alice@example.com' WHERE username = 'alice';
 3
 4-- 更新多条记录
 5UPDATE users SET age = age + 1 WHERE age < 30;
 6
 7-- 批量更新
 8UPDATE articles SET views = views + 1 WHERE id = 1;
 9
10-- 使用 FROM 子句更新
11UPDATE users SET email = new_emails.new_email
12FROM (VALUES ('alice', 'alice_new@example.com')) AS new_emails(username, new_email)
13WHERE users.username = new_emails.username;
14
15-- 返回更新的行
16UPDATE users SET age = age + 1 WHERE username = 'bob' RETURNING *;

 1-- 删除单条记录
 2DELETE FROM users WHERE username = 'eve';
 3
 4-- 删除多条记录
 5DELETE FROM users WHERE age < 25;
 6
 7-- 使用子查询删除
 8DELETE FROM orders WHERE user_id IN (SELECT id FROM users WHERE is_active = false);
 9
10-- 返回删除的行
11DELETE FROM users WHERE username = 'bob' RETURNING *;
12
13-- 清空表数据（保留结构）
14TRUNCATE TABLE users;
15
16-- 清空表并重置序列
17TRUNCATE TABLE users RESTART IDENTITY CASCADE;

 1-- 开始事务
 2BEGIN;
 3
 4-- 执行多个操作
 5INSERT INTO users (username, email) VALUES ('test', 'test@example.com');
 6UPDATE users SET age = age + 1 WHERE username = 'alice';
 7DELETE FROM users WHERE username = 'eve';
 8
 9-- 提交事务
10COMMIT;
11
12-- 或者回滚事务
13-- ROLLBACK;
14
15-- 保存点
16BEGIN;
17INSERT INTO users (username, email) VALUES ('test1', 'test1@example.com');
18SAVEPOINT my_savepoint;
19INSERT INTO users (username, email) VALUES ('test2', 'test2@example.com');
20-- 回滚到保存点
21ROLLBACK TO my_savepoint;
22COMMIT;

 1-- B-tree 索引（默认）
 2CREATE INDEX idx_users_username ON users(username);
 3
 4-- 唯一索引
 5CREATE UNIQUE INDEX idx_users_email ON users(email);
 6
 7-- 复合索引
 8CREATE INDEX idx_users_age_email ON users(age, email);
 9
10-- GIN 索引（用于数组、JSON、全文搜索）
11CREATE INDEX idx_articles_content ON articles USING GIN(to_tsvector('english', content));
12
13-- GiST 索引（用于几何数据、范围）
14CREATE INDEX idx_geo_location ON locations USING GiST(coordinates);
15
16-- 部分索引
17CREATE INDEX idx_active_users ON users(username) WHERE is_active = true;
18
19-- 表达式索引
20CREATE INDEX idx_users_lower_email ON users(LOWER(email));
21
22-- 查看索引
23\di
24
25-- 查看表的索引
26\di users*
27
28-- 分析索引使用
29SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';
30
31-- 删除索引
32DROP INDEX IF EXISTS idx_users_username;
33
34-- 并发创建索引（不锁表）
35CREATE INDEX CONCURRENTLY idx_users_age ON users(age);

 1-- 创建视图
 2CREATE VIEW user_orders AS
 3SELECT
 4    u.id as user_id,
 5    u.username,
 6    u.email,
 7    COUNT(o.id) as order_count,
 8    SUM(o.total_amount) as total_spent
 9FROM users u
10LEFT JOIN orders o ON u.id = o.user_id
11GROUP BY u.id, u.username, u.email;
12
13-- 查询视图
14SELECT * FROM user_orders WHERE total_spent > 1000;
15
16-- 创建可更新视图
17CREATE VIEW active_users AS
18SELECT id, username, email, age
19FROM users
20WHERE is_active = true;
21
22-- 更新可更新视图
23UPDATE active_users SET age = age + 1 WHERE id = 1;
24
25-- 删除视图
26DROP VIEW IF EXISTS user_orders;

 1-- 创建物化视图
 2CREATE MATERIALIZED VIEW user_stats AS
 3SELECT
 4    u.id,
 5    u.username,
 6    COUNT(DISTINCT o.id) as order_count,
 7    SUM(o.total_amount) as total_amount
 8FROM users u
 9LEFT JOIN orders o ON u.id = o.user_id
10GROUP BY u.id, u.username;
11
12-- 刷新物化视图
13REFRESH MATERIALIZED VIEW user_stats;
14
15-- 并发刷新（不影响查询）
16REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats;
17
18-- 删除物化视图
19DROP MATERIALIZED VIEW IF EXISTS user_stats;