PostgreSQL 快速入门

PostgreSQL 操作数据库命令语法

PostgreSQL 数据类型

约束 (Constraints)

什么是约束？ 约束是数据库的“规矩”，确保数据符合规则，防止乱七八糟的数据塞进来。

多表关联查询 (JOIN)

什么是关联查询？ 把多张表拼起来查，就像把几页名单拼成一份大表格。

别名 (Alias)

• 用 AS 或直接写简称给表或列起个“昵称”，让查询更简洁。
• 示例：SELECT u.name AS username FROM users u;
• 通俗理解：“给表和列取个小名，写起来不那么累！”

触发器 (Triggers)

什么是触发器？ 触发器是表的“自动反应”，某事发生时自动执行操作。

• 用途：记录日志、检查数据、同步表。
• 语法：

  CREATE TRIGGER trigger_name
  BEFORE | AFTER event (INSERT | UPDATE | DELETE)
  ON table_name
  FOR EACH ROW
  EXECUTE FUNCTION function_name();
  

• 通俗理解：“表的小助手，有人改数据时它就跳出来干活！”

示例：

CREATE FUNCTION log_update() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, changed_at) VALUES ('users', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_update_log
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_update();

• 效果：每次更新 users 表，audit_log 都会记一笔。

索引 (Indexes)

什么是索引？ 索引是表的“目录”，加速查找，减少翻遍全书的麻烦。

• 语法：CREATE INDEX index_name ON table_name(column_name);
• 通俗理解：“给表加个快查手册，找东西不用一行行翻！”

最新特性：

• 并行索引创建：CREATE INDEX ... WITH (PARALLEL_WORKERS = 4);，加速大表索引构建。
• 覆盖索引：CREATE INDEX idx_cover ON users(id) INCLUDE (name);，查询时不用访问表。

视图 (Views)

什么是视图？ 视图是表的“虚拟窗口”，基于查询结果，像个快捷方式。

• 语法：

  CREATE VIEW view_name AS SELECT ...;
  

• 通俗理解：“把复杂查询存下来，随时看，不用每次重写！”

示例：

CREATE VIEW active_users AS
SELECT id, name FROM users WHERE status = 'active';

• 效果：SELECT * FROM active_users; 直接看活跃用户。

事务(Transactions)

什么是事务？ 事务是一组操作，要么全成功，要么全取消，保证数据不乱。

• 语法：

  BEGIN;
  -- 操作1
  -- 操作2
  COMMIT;  -- 成功提交
  -- 或 ROLLBACK;  -- 失败回滚
  

• 通俗理解：“一篮子任务，要么全做完，要么全撤销，像转账不能只扣不加！”

示例：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

锁 (Locks)

什么是锁？ 锁是数据库的“门锁”，防止多人同时改数据出错。

• 通俗理解：“锁是交通信号灯，控制谁能动，谁得等！”

函数 (Functions)

什么是函数？ 函数是数据库的“自定义工具”，写一段代码反复用。

• 语法：

  CREATE FUNCTION function_name(params) RETURNS type AS $$
  BEGIN
      -- 逻辑
  END;
  $$ LANGUAGE plpgsql;
  

• 通俗理解：“自己造个小机器人，干重复活！”

示例：

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

• 使用：SELECT add_numbers(3, 5); 输出 8。

补充知识点

通俗总结

• 约束：表的规矩，防乱填。
• 关联查询：拼表找数据，别名是小助手。
• 触发器：表的小机器人，自动干活。
• 索引：表的快查目录，找得快。
• 视图：表的快捷窗口，省事。
• 事务：一篮子任务，全成或全废。
• 锁：表的门锁，防抢。
• 函数：自定义工具，偷懒神器。
• 补全：序列编号、分区拆表、搜索文本、权限管理，一个不漏！

什么是 SCHEMA？

在 PostgreSQL 中，SCHEMA 是一个逻辑上的“命名空间”或者“文件夹”，用来组织和管理数据库对象（比如表、视图、索引等）。一个数据库（Database）可以包含多个 SCHEMA，而每个 SCHEMA 里可以有自己的表、视图等东西。

• 比喻：想象一个大仓库（数据库），里面有很多货架（SCHEMA），每个货架上放不同的货物（表、视图等）。SCHEMA 就是帮你把东西分门别类放好。
• 官方定义：SCHEMA 是数据库中的一个逻辑分组，同一个数据库里的 SCHEMA 共享存储空间，但彼此独立命名。

SCHEMA 的作用

1. 组织数据 把相关的表归类到一个 SCHEMA 中，方便管理。比如，一个公司数据库可以有 sales（销售）、hr（人力资源）、inventory（库存）三个 SCHEMA。
2. 权限控制 可以给不同用户分配不同 SCHEMA 的访问权限。比如，销售团队只能看 sales 的表，HR 只能看 hr 的表。
3. 避免命名冲突 在同一个数据库里，不同 SCHEMA 可以有同名表。比如 sales.product 和 inventory.product，名字一样但内容不同，不会混淆。
4. 模块化开发 大型项目中，不同模块可以用不同 SCHEMA 分开，逻辑更清晰。

在什么情况下要用到 SCHEMA？

1. 多用户或多团队场景
   • 如果一个数据库被多个团队共用，每个团队可以用自己的 SCHEMA，避免互相干扰。
   • 示例：一个学校数据库，老师用 teacher SCHEMA，学生用 student SCHEMA。
2. 复杂项目需要分模块
   • 大型应用可能有多个子系统（比如电商的订单、用户、库存），用 SCHEMA 分开更清晰。
   • 示例：orders.order_table、users.user_table。
3. 权限管理需求
   • 需要精细控制谁能访问哪些表时，SCHEMA 是个好工具。
   • 示例：财务数据放 finance SCHEMA，只给财务人员权限。
4. 测试和生产环境共存
   • 在同一个数据库里，test SCHEMA 放测试数据，prod SCHEMA 放生产数据，方便切换。

什么时候不用 SCHEMA？ 如果你的项目很简单，只有一个团队、几个表，数据量也不大，默认的 public SCHEMA 就够用了。

SCHEMA 是否真实创建了一个数据库？

答案：不是。

• SCHEMA 不是一个独立的数据库，它只是数据库内部的一个逻辑分组。
• 数据库 vs SCHEMA：
  • 数据库（Database）：是物理隔离的存储单元，创建时会分配独立的空间。不同数据库之间完全独立，比如 mydb1 和 mydb2 的数据互不影响。
  • SCHEMA：是数据库里的“子文件夹”，所有 SCHEMA 共享同一个数据库的存储空间和连接。切换 SCHEMA 不需要重新连接数据库。
• 比喻：
  • 数据库是大房子，SCHEMA 是房子里的房间。建新房子（数据库）需要地基和材料，分房间（SCHEMA）只是画个隔断。
• 证明：
  • 创建 SCHEMA：CREATE SCHEMA myschema; —— 只是在当前数据库里加了个“文件夹”。
  • 创建数据库：CREATE DATABASE mydb; —— 这是建了个新“仓库”，完全独立。

通俗总结

• SCHEMA 是什么：数据库里的“文件夹”，帮你整理表和控制权限。
• 什么时候用：项目复杂、需要分团队或模块时用。简单项目用 public 就行。
• public 行不行：小项目行，大项目不够用。
• 是不是数据库：不是，只是数据库里的分组，不会独立占用空间。

举个例子： 你开个小店，只有一个货架（public），够用。但如果开了连锁超市，货架得按类别分（fruits、drinks），这就是 SCHEMA 的用武之地。