PostgreSQL 基础学习

postgreSQL基础学习：

SQL四大操作

新增 INSERT

查询 SELECT

修改 UPDATE

删除 DELETE

总体结构：

PostgreSQL Server

|

|

Database

|

|

Schema（与mySQL的关键不同：）

|

|

Table

|

|

Row

作为AI全栈开发者最应该熟练的20个命令：

我建议背这些：

用途 命令

进入数据库 \c

退出 \q

查看数据库 \l

查看表 \dt

看表结构 \d

创建数据库 CREATE DATABASE

创建表 CREATE TABLE

修改表 ALTER TABLE

删除表 DROP TABLE

插入 INSERT

查询 SELECT

条件 WHERE

排序 ORDER BY

分页 LIMIT OFFSET

修改 UPDATE

删除 DELETE

关联查询 JOIN

统计 COUNT

事务 BEGIN/COMMIT

索引 CREATE INDEX

一、psql命令（数据库管理常用）

注意：

这些命令前面有：

\

它们不是SQL，而是 PostgreSQL 客户端命令。

1. 查看所有数据库

\l

2. 切换数据库

\c 数据库名

例如：

\c ai_knowledge

切换后：

ai_knowledge=#

3. 查看当前数据库

SELECT current_database();

4. 查看所有表

最常用：

\dt

例如：

users

documents

chat_history

5. 查看表结构

非常常用。

\d 表名

例如：

\d users

结果：

Column

-------------

id

username

email

password

相当于：

查看：

字段

类型

主键

默认值

6. 查看所有用户

\du

7. 退出 PostgreSQL

\q

二、数据库创建和删除

创建数据库

CREATE DATABASE 数据库名;

例如：

CREATE DATABASE ai_project;

删除数据库

DROP DATABASE 数据库名;

例如：

DROP DATABASE ai_project;

注意：

删除不可恢复。

三、表操作（开发最高频）

1. 创建表

例如：

用户表：

CREATE TABLE users(

id SERIAL PRIMARY KEY,

username VARCHAR(50),

email VARCHAR(100),

created_at TIMESTAMP

);

2. 删除表

DROP TABLE users;

3. 清空表数据

保留表结构：

TRUNCATE TABLE users;

区别：

DELETE：

逐条删除。

TRUNCATE：

直接清空。

开发测试非常常用。

4. 修改表结构 ALTER TABLE

实际开发经常需要。

例如：

增加字段：

ALTER TABLE users

ADD COLUMN age INTEGER;

现在：

users：

之前：

id

username

email

变成：

id

username

email

age

删除字段：

ALTER TABLE users

DROP COLUMN age;

修改字段类型：

ALTER TABLE users

ALTER COLUMN username TYPE VARCHAR(100);

四、INSERT 插入数据

插入一条

INSERT INTO users(username,email)

VALUES

('aki','aki@qq.com');

插入多条

高频：

INSERT INTO users(username,email)

VALUES

('aki','a@qq.com'),

('tom','t@qq.com'),

('jack','j@qq.com');

五、SELECT 查询（最重要）

查询所有：

SELECT *

FROM users;

查询指定字段：

SELECT username,email

FROM users;

条件查询：

SELECT *

FROM users

WHERE username='aki';

多个条件：

SELECT *

FROM users

WHERE age>18

AND username='aki';

模糊查询 LIKE

例如：

查找名字包含：

aki

SELECT *

FROM users

WHERE username LIKE '%aki%';

%表示任意字符。

例如：

aki

aki123

myaki

都能匹配。

六、排序 ORDER BY

例如：

按照创建时间排序：

SELECT *

FROM users

ORDER BY created_at DESC;

DESC：

降序。

ASC：

升序。

七、限制数量 LIMIT

非常常用。

例如：

取最新10条：

SELECT *

FROM chat_history

ORDER BY created_at DESC

LIMIT 10;

AI聊天记录查询大量使用。

八、分页查询 OFFSET

例如：

第一页：

LIMIT 10 OFFSET 0

第二页：

LIMIT 10 OFFSET 10

第三页：

LIMIT 10 OFFSET 20

后台管理系统大量使用。

九、UPDATE 修改数据

修改：

UPDATE users

SET email='new@qq.com'

WHERE id=1;

非常注意：

一定写：

WHERE

否则：

UPDATE users

SET email='xxx';

所有用户邮箱都会变。

十、DELETE 删除

删除：

DELETE FROM users

WHERE id=1;

危险：

DELETE FROM users;

会删除全部数据。

十一、JOIN（后端非常高频）

真实项目几乎每天用。

假设：

users:

id username

1 aki

documents:

id user_id title

1 1 FastAPI

查询：

用户名 + 文档：

SELECT

users.username,

documents.title

FROM users

JOIN documents

ON users.id = documents.user_id;

结果：

aki | FastAPI

十二、聚合查询

统计数据。

COUNT

统计数量：

SELECT COUNT(*)

FROM users;

结果：

100

GROUP BY

例如：

统计每个用户上传多少文档：

SELECT

user_id,

COUNT(*)

FROM documents

GROUP BY user_id;

结果：

user_id | count

1 | 5

2 | 3

AI知识库统计：

非常常见。

十三、索引（性能相关）

数据少：

没感觉。

100万数据：

区别巨大。

创建索引：

CREATE INDEX idx_username

ON users(username);

查询：

WHERE username='aki'

速度提升。

查看索引：

\d users

十四、事务 Transaction（企业必懂）

事务：

保证一组操作一起成功或者一起失败。

例如转账：

A减少：

UPDATE account

SET money=money-100;

B增加：

UPDATE account

SET money=money+100;

必须一起完成。

写法：

开始：

BEGIN;

提交：

COMMIT;

失败回滚：

ROLLBACK;

十五、PostgreSQL 特有高频能力（AI方向重点）

1. JSONB

AI项目非常常用。

例如：

保存Agent状态：

{

"step":3,

"tool":"search",

"memory":"xxx"

}

字段：

metadata JSONB

查询：

SELECT *

FROM agents

WHERE metadata->>'tool'='search';

2. UUID

企业项目常用：

id UUID PRIMARY KEY

代替：

SERIAL

原因：

避免暴露用户数量。

3. 时间字段

非常常见：

created_at TIMESTAMP DEFAULT NOW()

自动记录创建时间。

数据库误删事故发生后的标准处置顺序：

发现误删后，应遵循：

停止继续写入

↓

记录事故时间和命令

↓

保存当前现场

↓

检查备份、WAL、RDB、AOF、快照

↓

在隔离环境恢复

↓

核对业务数据

↓

导回生产或切换实例

↓

复盘和收紧权限

不要立即做这些事情：

不要反复重启数据库

不要继续执行大量写操作

不要直接编辑唯一一份 WAL/AOF

不要在生产数据目录上直接实验恢复

不要为了“修复”而继续删除文件

不要立即让主从发生重新同步

最终结论

数据库是否能回滚，可以记成一句话：

事务内靠 ROLLBACK，提交后靠备份和日志，rm 之后只能靠灾备体系。

真正可靠的防护不是要求运维人员“永远不犯错”，而是建立多层防线：

最小权限

+ 危险操作审批

+ 事务操作

+ PITR

+ RDB/AOF

+ 异地不可变备份

+ 定期恢复演练

只要公司提前配置了 PostgreSQL 基础备份与 WAL 归档，或者 Redis 保留了事故前的 RDB/AOF，即使发生严重误删，通常仍有较大机会恢复。没有提前配置灾备时，再熟练的数据库管理员也可能无能为力。