1.postgresql 安装

https://www.postgresql.org/download/windows/

安装教程
第一步：启动安装程序
直接点 Next 下一步。

第二步：选组件
会弹出以下四个组件，建议全选。

PostgreSQL Server
pgAdmin 4
Stack Bulder
Command Line Tools

第三步：选安装路径
默认安装路径为： C:\Program Files\PostgreSQL\16

你也可以选择其他路径，但是你要记的安装在哪了，记得记路径！

第四步：配置数据库存储目录
默认路径为 C:\Program Files\PostgreSQL\16\data，C 盘不够不建议装 C，装到其他盘里！空间要留大一点，因为是存数据的，内存自然会一直增长，空间留大！空间留大！！空间留大！重要的事情说三遍！

第五步：设置密码
然后会让你设置密码：不要忘记了！！！

第六步：配置端口号
设置端口号：默认端口 5432（若该端口被占用，可修改为其他未占用端口，如 5433）
好了之后点击 “Next”。

最后一步：确认安装配置
点击“Next”开始安装，等待 5-10 分钟（根据电脑配置而定）。

安装完成后，取消 “Stack Builder” 勾选（无需额外组件），点击 “Finish” 。

至此，安装完毕！

我们来查看安装的pg数据库

点击左下角开始，然后找到对应的文件夹，双击Pd Admin 4,输入密码即可连接。

这个自带的图形化界面不太好用，推荐使用navicat或者sqlark等工具。

具体页面如下：

mac和linux系统安装教学暂时省略，这个也比较简单，大家自行百度安装即可。

2.postgresql使用

其实他的操作大多数和mysql是差不多的，都是关系型数据库。

2.1建表

-- 创建一张表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

2.2.操作列

增加

-- 给 users 表增加一个 phone 字段
ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

-- 增加带默认值和非空约束的列
ALTER TABLE users
ADD COLUMN status VARCHAR(10) DEFAULT 'active' NOT NULL;

删除

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

修改列名和属性

--修改列名
ALTER TABLE users
RENAME COLUMN email TO user_email;



-- 添加 NOT NULL（要求现有数据不能有 NULL）
ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;

2.3执行crud操作

-- 插入数据
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- 查询数据
SELECT * FROM users;

-- 更新数据
UPDATE users SET email = 'alice_new@example.com' WHERE name = 'Alice';

-- 删除数据
DELETE FROM users WHERE id = 1;

-- 提交事务（psql 默认开启自动提交，但也可手动控制）
BEGIN;
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
ROLLBACK;  -- 回滚，刚才的插入不会生效

-- 再试一次并提交
BEGIN;
INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com');
COMMIT;

2.4查询

-- 创建带 JSONB 字段的表
CREATE TABLE products (
    id SERIAL,
    name TEXT,
    metadata JSONB  -- 注意是 JSONB，不是 JSON
);
-- 插入数据
INSERT INTO products (name, metadata) VALUES 
('Laptop', '{"brand": "Dell", "specs": {"ram": "16GB", "ssd": "512GB"}}');

-- 查询：找出所有 RAM 是 16GB 的产品
SELECT name FROM products 
WHERE metadata->'specs'->>'ram' = '16GB';

3.pgsql的优点（相比mysql）

3.1. 强大的 JSON/JSONB 支持（远超 MySQL）

PostgreSQL 原生支持结构化 JSON 查询，且 JSONB 类型可建索引、高效查询。

-- 创建带 JSONB 字段的表
CREATE TABLE products (
    id SERIAL,
    name TEXT,
    metadata JSONB  -- 注意是 JSONB，不是 JSON
);

-- 插入数据
INSERT INTO products (name, metadata) VALUES 
('Laptop', '{"brand": "Dell", "specs": {"ram": "16GB", "ssd": "512GB"}}');

-- 查询：找出所有 RAM 是 16GB 的产品
SELECT name FROM products 
WHERE metadata->'specs'->>'ram' = '16GB';

-- 创建 GIN 索引加速查询（MySQL 无法做到同等性能）
CREATE INDEX idx_metadata ON products USING GIN (metadata);

 -> 返回 JSON 对象，->> 返回文本。
MySQL 虽然也有 JSON，但功能弱、索引支持差、语法繁琐。

3.2. 窗口函数（Window Functions）—— 更强大 & 标准

虽然 MySQL 8.0+ 也支持窗口函数，但 PostgreSQL 支持更早、更完整、性能更好。

-- 计算每个用户订单金额的排名（按总金额降序）
SELECT 
    user_id,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS rank_all,
    RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank_per_user
FROM orders;

PostgreSQL 还支持：

• LAG(), LEAD()（取前后行）
• FIRST_VALUE(), LAST_VALUE()
• 自定义窗口帧（ROWS / RANGE）

3.3. CTE（公用表表达式） + 递归查询

PostgreSQL 对 CTE 的支持非常成熟，尤其擅长处理树形/层级数据（如组织架构、评论回复链）。

-- 递归查询：找出某部门的所有子部门
WITH RECURSIVE subdepartments AS (
    -- 锚点：起始部门
    SELECT id, name, parent_id FROM departments WHERE name = 'Engineering'
    
    UNION ALL
    
    -- 递归：查找子部门
    SELECT d.id, d.name, d.parent_id
    FROM departments d
    INNER JOIN subdepartments s ON d.parent_id = s.id
)
SELECT * FROM subdepartments;

MySQL 8.0+ 才支持递归 CTE，且早期版本完全不支持。

---

3.4. 全文搜索（Full-Text Search）内置且强大

无需 Elasticsearch，PostgreSQL 自带高性能全文检索。

-- 创建 tsvector 列并建索引
ALTER TABLE articles ADD COLUMN tsv TSVECTOR;
UPDATE articles SET tsv = to_tsvector('english', title || ' ' || content);
CREATE INDEX idx_tsv ON articles USING GIN(tsv);

-- 搜索包含 "database" 或 "performance" 的文章
SELECT title FROM articles 
WHERE tsv @@ to_tsquery('english', 'database | performance');

MySQL 的全文搜索功能较弱（仅 MyISAM/InnoDB 有限支持），且不支持中文分词（需额外插件）。

---

3.5. 自定义聚合函数 & 扩展性

PostgreSQL 允许你用 SQL、C、Python 等编写自定义函数、聚合、操作符。

例如：计算几何平均数（MySQL 没有内置）：

CREATE AGGREGATE geomean(double precision) (
    SFUNC = float8mul,
    STYPE = double precision,
    INITCOND = 1.0
);

---

3.6. 更严格的类型系统 & 自定义类型

• 支持 ARRAY 类型：sql

  CREATE TABLE tags (id INT, labels TEXT[]);
  INSERT INTO tags VALUES (1, ARRAY['tech', 'sql']);
  SELECT * FROM tags WHERE 'sql' = ANY(labels);

• 支持 RANGE 类型（如时间区间、数值区间）：sql

  SELECT int4range(10, 20) && int4range(15, 25); -- 返回 true（有重叠）

MySQL 没有原生数组或范围类型。

3.7. 更好的事务隔离与并发控制

• 默认使用 MVCC（多版本并发控制），读不阻塞写，写不阻塞读。
• 支持 Serializable Snapshot Isolation (SSI)，真正防止幻读，而 MySQL 的“可重复读”其实不是标准的。

总结如下：

方面	结论
基础查询	和 MySQL 几乎一样，无缝切换
高级功能	PostgreSQL 远超 MySQL，尤其在 JSON、递归、全文搜索、类型系统等方面
开发体验	PostgreSQL 更“程序员友好”，适合复杂业务逻辑
建议	先用熟悉的 MySQL 式写法，再逐步尝试 PostgreSQL 特色功能