PostgreSQL 基础操作

官方文档：http://www.postgres.cn/docs/14/

一、什么是 psql？

psql 是 PostgreSQL 提供的交互式终端工具，是与 PostgreSQL 数据库交互的核心工具。它允许用户：

• 执行 SQL 查询：运行各种 SQL 语句

• 管理数据库对象：创建/修改表、视图、索引等

• 查看数据库状态：检查性能指标和配置

• 执行脚本文件：批量运行 SQL 命令

• 数据导入导出：处理数据迁移任务

在 psql 中有两种命令类型：

1. SQL 标准语句（如 SELECT, INSERT, UPDATE 等）

2. psql 内部命令（以反斜杠 \ 开头）

1. 数据库管理

命令	功能
\l 或 \list	列出所有数据库
\c 或 \connect	切换数据库
CREATE DATABASE	创建新数据库
DROP DATABASE	删除数据库

2. 对象查看与管理

命令	功能
\dt	查看当前数据库的所有表
\dt schema.*	查看特定模式下的表
\d table_name	查看表结构
\dv	查看视图
\di	查看索引
\ds	查看序列
\dn	查看schema
\df	查看函数和存储过程
\du	查看用户列表

3. 实用内部命令

命令	功能
\conninfo	显示当前连接信息
\timing	切换SQL执行时间显示
\x	切换扩展显示模式
\s	查看命令历史
\e	在编辑器中编辑查询
\?	查看所有内部命令帮助
\q	退出psql
\h <command>	查看 SQL 命令的语法帮助⭐⭐⭐⭐⭐⭐⭐

二、 连接与基础信息

连接数据库

# 通过命令行连接（psql）
psql -h <主机名> -p <端口号> -U <用户名> -d <数据库名>

# 示例：连接本地默认端口(5432)的 mydb 数据库
psql -U postgres -d mydb

# 连接后，如果数据库需要密码，会提示你输入。

三、 用户与权限管理

1. 创建用户/角色

```sql
-- 创建具有登录权限的用户
CREATE USER dev_user WITH PASSWORD 'secure_password';
-- 或者（USER 和 ROLE 的区别：USER 默认有 LOGIN 权限）
CREATE ROLE read_only_user WITH LOGIN PASSWORD 'readonly_pass';

-- 创建角色（通常用于组权限）
CREATE ROLE developers;
```

2. 修改用户

-- 修改密码
ALTER USER dev_user WITH PASSWORD 'new_secure_password';

-- 重命名用户
ALTER USER dev_user RENAME TO new_username;

-- 设置用户有效期
ALTER USER dev_user VALID UNTIL '2024-12-31';

3. 删除用户

DROP USER dev_user;

4. 权限管理

4.1 数据库权限：

-- 授予连接数据库权限
GRANT CONNECT ON DATABASE mydb TO dev_user;

-- 授予创建表权限
GRANT CREATE ON DATABASE mydb TO dev_user;

4.2 模式权限：

-- 授予模式使用权限
GRANT USAGE ON SCHEMA public TO dev_user;

-- 授予模式中所有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

-- 授予未来创建表的权限（重要！）
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO read_only_user;

4.3 表权限：

-- 授予特定表的权限
GRANT SELECT, INSERT, UPDATE ON users TO dev_user;
GRANT ALL PRIVILEGES ON posts TO developers;

-- 授予列级权限
GRANT UPDATE (email, phone) ON users TO dev_user;

-- 撤销权限
REVOKE DELETE ON users FROM dev_user;

4.4 序列权限（自增字段）：

GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO dev_user;

5. 角色成员关系

-- 将用户添加到角色组
GRANT developers TO dev_user;

-- 从角色组移除用户
REVOKE developers FROM dev_user;

6. 查看权限信息

-- 查看当前用户权限
SELECT * FROM information_schema.table_privileges 
WHERE grantee = CURRENT_USER;

-- 查看特定表的权限
\dp users

四、 数据库操作

1. 创建数据库

CREATE DATABASE mydb 
WITH OWNER = postgres  -- 指定所有者
     ENCODING = 'UTF8'
     CONNECTION LIMIT = 100;  -- 限制连接数

2. 修改数据库所有者

ALTER DATABASE mydb OWNER TO new_owner;

3. 删除数据库

-- 先确保没有活跃连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'mydb';

DROP DATABASE mydb;

注意：不能在与该数据库连接的状态下删除它。请先连接到另一个数据库（如 postgres）再执行删除。

五、 表操作

1. 创建表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,        -- 自增主键
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100),
    age INT CHECK (age > 0),      -- 检查约束
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id), -- 外键约束
    title VARCHAR(255) NOT NULL,
    content TEXT,
    is_published BOOLEAN DEFAULT FALSE
);

2. 修改表

-- 添加列
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);

-- 修改列数据类型
ALTER TABLE users ALTER COLUMN full_name TYPE TEXT;

-- 删除列
ALTER TABLE users DROP COLUMN full_name;

-- 添加约束
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

3. 删除表

DROP TABLE posts;

六、 CRUD 操作（核心）

1. 插入数据

-- 插入单条数据
INSERT INTO users (username, email, age)
VALUES ('alice', 'alice@example.com', 25);

-- 插入多条数据
INSERT INTO users (username, email, age)
VALUES
    ('bob', 'bob@example.com', 30),
    ('charlie', 'charlie@example.com', 28);

-- 插入时使用 RETURNING 子句获取生成的值（如自增ID）
INSERT INTO users (username, email) VALUES ('david', 'david@example.com') RETURNING id;

2. 查询数据

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

-- 选择特定字段
SELECT id, first_name, last_name FROM employees;

-- 使用别名
SELECT 
    id AS employee_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    salary * 1.1 AS new_salary
FROM employees;

-- 基本条件筛选
SELECT * FROM orders WHERE amount > 1000 AND status = 'completed';

-- 日期范围查询
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

-- 模糊查询 (LIKE, ILIKE 不区分大小写)
SELECT * FROM users WHERE username LIKE 'a%'; -- 以 'a' 开头
SELECT * FROM users WHERE email ILIKE '%EXAMPLE.COM';

-- 排序 (ORDER BY)
SELECT * FROM users ORDER BY age DESC; -- 降序
SELECT * FROM users ORDER BY created_at ASC; -- 升序
SELECT * FROM employees ORDER BY department ASC, salary DESC;

-- 标准分页
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 10 OFFSET 20;  --跳过 20 条记录，从第 21 条开始，最多返回 10 条

-- 使用更高效的分页（PostgreSQL 12+）
SELECT * FROM orders
ORDER BY id
OFFSET 20 FETCH NEXT 10 ROWS ONLY;

-- 基本聚合
SELECT 
    COUNT(*) AS total_orders,  --计数
    SUM(amount) AS total_revenue, -- 求和
    AVG(amount) AS average_order  --平均值
FROM orders;

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

-- 分组后过滤
SELECT 
    product_category,
    SUM(quantity) AS total_sold
FROM sales
GROUP BY product_category
HAVING SUM(quantity) > 1000;

3. 更新数据

-- 更新所有行（慎用！）
UPDATE posts SET is_published = TRUE;

-- 更新指定行
UPDATE users SET age = 26 WHERE username = 'alice';

-- 更新多个列
UPDATE users SET email = 'new_email@example.com', age = 27 WHERE id = 1;

4. 删除数据

-- 删除所有行（慎用！）
DELETE FROM posts;

-- 删除指定行
DELETE FROM users WHERE id = 5;

-- 清空表（更高效，但无法回滚）
TRUNCATE TABLE posts;

七、 连接查询

-- 内连接
SELECT 
    o.order_id,
    c.customer_name,
    o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- 左连接（包含无订单的客户）
SELECT 
    c.customer_name,
    o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- 多表连接
SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    p.project_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id;

-- 自连接
SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
       w2.city, w2.temp_lo AS low, w2.temp_hi AS high
    FROM weather w1 JOIN weather w2
        ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;
        
     city      | low | high |     city      | low | high
---------------+-----+------+---------------+-----+------
 San Francisco |  43 |   57 | San Francisco |  46 |   50
 Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)

八、 高级查询技巧

1. 子查询应用

-- WHERE子句中的子查询
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- FROM子句中的子查询（派生表）
SELECT 
    dept_stats.department,
    dept_stats.avg_salary
FROM (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats
WHERE dept_stats.avg_salary > 70000;

-- CTE（公用表表达式）
WITH regional_sales AS (
    SELECT 
        region,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
)
SELECT 
    region,
    total_sales
FROM regional_sales
WHERE total_sales > 100000;

2. 窗口函数高级应用

-- 排名与分区
SELECT 
    department,
    first_name,
    last_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 移动平均
SELECT 
    order_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS weekly_avg
FROM daily_sales;

九、 事务管理

事务确保一系列操作要么全部成功，要么全部失败。

BEGIN; -- 开始事务

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 如果此时检查无误，则提交
COMMIT;

-- 如果发现错误，则回滚
-- ROLLBACK;

十、 索引（优化查询速度）

1. 创建索引

-- 在经常用于查询条件的列上创建索引
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_posts_user_id ON posts (user_id);

-- 复合索引
CREATE INDEX idx_users_name_age ON users (username, age);

2. 查看索引

\d users -- 在表结构中会显示索引

十一、 常用函数与技巧

1. 字符串函数

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
SELECT UPPER(username), LOWER(email) FROM users;
SELECT LENGTH(title) FROM posts;

2. 日期/时间函数

SELECT NOW(); -- 当前时间戳
SELECT CURRENT_DATE; -- 当前日期
SELECT EXTRACT(YEAR FROM created_at) AS year FROM posts; -- 提取年份
SELECT created_at::DATE FROM posts; -- 类型转换，只取日期部分

3. 条件表达式

-- CASE WHEN
SELECT username,
       CASE WHEN age < 20 THEN 'Teen'
            WHEN age < 30 THEN 'Twenties'
            ELSE 'Adult'
       END AS age_group
FROM users;

-- COALESCE (返回第一个非NULL值)
SELECT COALESCE(full_name, username) AS display_name FROM users;

---

十二、 安全最佳实践

1. 遵循最小权限原则

-- 只授予必要的权限
GRANT SELECT ON sensitive_table TO reporting_user;
REVOKE DELETE ON important_table FROM app_user;

2. 使用视图保护敏感数据

CREATE VIEW user_public_info AS
SELECT id, username, created_at FROM users;

GRANT SELECT ON user_public_info TO public_user;

3. 定期审计权限

-- 查看所有用户权限
SELECT * FROM information_schema.role_table_grants;

-- 查看数据库权限
SELECT datname, datacl FROM pg_database;

4. 密码策略

-- 设置密码有效期
ALTER USER app_user VALID UNTIL '2024-12-31';

-- 强制密码更改
ALTER USER app_user WITH PASSWORD 'new_pass';

十三、 常用维护命令

1. 查看活跃连接

SELECT * FROM pg_stat_activity;

2. 终止连接

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'mydb';

3. 查看锁信息

SELECT * FROM pg_locks;

---

权限管理要点总结

1. 权限层次：集群 → 数据库 → 模式 → 表/视图/序列 → 列

2. 关键命令：GRANT / REVOKE / ALTER DEFAULT PRIVILEGES

3. 权限类型：

   • SELECT / INSERT / UPDATE / DELETE
   • CREATE / CONNECT / USAGE / TEMPORARY
   • EXECUTE (函数) / USAGE (序列)

4. 最佳实践：

   • 使用角色组管理权限
   • 及时撤销不必要的权限
   • 定期审计用户权限
   • 使用视图限制数据访问
   • 遵循最小权限原则

总结

• 核心四步：CREATE -> INSERT -> SELECT -> UPDATE/DELETE。
• 查询是灵魂：熟练掌握 WHERE, JOIN, GROUP BY, ORDER BY, LIMIT。
• 安全第一：使用 WHERE 条件时务必小心，避免误删或误更新。善用事务。
• 性能优化：在合适的列上创建索引。