pg_dump -U postgres -d change-this-db-name > /var/lib/postgresql/data/change-this-db-name.sql

psql -U postgres -d change-this-db-name -f /var/lib/postgresql/data/pgdata/change-this-db-name.sql

apt update
apt install postgresql-17-pgvector

-- 普通索引
CREATE INDEX idx_users_email ON users(email);
-- 唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- 复合索引
CREATE INDEX idx_users_name_email ON users(name, email);
-- 部分索引
CREATE INDEX idx_active_users ON users(email) WHERE created_at > '2025-01-01';
-- 查看索引
SELECT * FROM pg_indexes WHERE tablename = 'users';
-- 删除索引
DROP INDEX idx_users_email;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入单条
INSERT INTO users (name, email) VALUES ('张三', 'zhang@example.com');
-- 插入多条
INSERT INTO users (name, email) VALUES ('李四', 'li@example.com'),
    ('王五', 'wang@example.com');
-- 返回插入的数据
INSERT INTO users (name, email) VALUES ('赵六', 'zhao@example.com') RETURNING *;

-- 删除指定数据
DELETE FROM users WHERE id = 1;
-- 条件删除
DELETE FROM users WHERE created_at < '2025-01-01';
-- 删除全部（慎用）
DELETE FROM users;

-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;
-- 更新多个字段
UPDATE users SET name = '张三丰', email = 'zhang@new.com' WHERE id = 1;
-- 返回更新的数据
UPDATE users SET name = '李四光' WHERE id = 2 RETURNING *;

-- 查询全部
SELECT * FROM users;
-- 条件查询
SELECT * FROM users WHERE name = '张三';
-- 排序和限制
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- 模糊查询
SELECT * FROM users WHERE email LIKE '%@example.com';

-- 分页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
-- 聚合
SELECT COUNT(*) FROM users;
SELECT COUNT(*), MAX(created_at) FROM users;
-- 分组
SELECT name, COUNT(*) FROM users GROUP BY name;
-- JOIN
SELECT u.name, o.order_id 
FROM users u 
JOIN orders o ON u.id = o.user_id;