PostgreSQL 完全入门指南（Mac 版）

# 第 1 步：安装 PostgreSQL 16
brew install postgresql@16
 
# 第 2 步：启动 PostgreSQL 服务（开机自动启动）
brew services start postgresql@16
 
# 第 3 步：把 pg 命令加入 PATH（否则终端找不到 psql 命令）
echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc
 
# 第 4 步：验证安装成功
psql --version
# 应该输出类似：psql (PostgreSQL) 16.x

# 拉取并启动 PostgreSQL 容器
docker run -d \
  --name bookmark-pg \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  postgres:16-alpine
 
# 验证容器在运行
docker ps
# 应该能看到 bookmark-pg 容器

# 创建一个叫 bookmark_dev 的数据库
createdb bookmark_dev
 
# 验证：连接进去看看
psql bookmark_dev

# 在容器里创建数据库
docker exec -it bookmark-pg createdb -U postgres bookmark_dev
 
# 验证：连接进去
docker exec -it bookmark-pg psql -U postgres bookmark_dev

-- 创建一张 users 表
CREATE TABLE users (
  id          SERIAL PRIMARY KEY,   -- 自增主键
  email       VARCHAR(255) UNIQUE NOT NULL,  -- 邮箱，唯一且不能为空
  nickname    VARCHAR(50),          -- 昵称
  created_at  TIMESTAMP DEFAULT NOW()  -- 创建时间，默认当前时间
);

-- 插入一条用户记录
INSERT INTO users (email, nickname) VALUES ('xiaoyi@example.com', '小易');
 
-- 插入多条
INSERT INTO users (email, nickname) VALUES
  ('xiaoming@example.com', '小明'),
  ('xiaohong@example.com', '小红');

-- 查询所有用户
SELECT * FROM users;
 
-- 结果：
--  id |        email          | nickname |       created_at
-- ----+-----------------------+----------+------------------------
--   1 | xiaoyi@example.com    | 小易     | 2026-04-09 10:30:00
--   2 | xiaoming@example.com  | 小明     | 2026-04-09 10:30:05
--   3 | xiaohong@example.com  | 小红     | 2026-04-09 10:30:05
 
-- 只查邮箱和昵称
SELECT email, nickname FROM users;
 
-- 条件查询：查找小易
SELECT * FROM users WHERE nickname = '小易';
 
-- 模糊查询：查找昵称包含"小"的用户
SELECT * FROM users WHERE nickname LIKE '%小%';
 
-- 排序：按创建时间倒序
SELECT * FROM users ORDER BY created_at DESC;
 
-- 限制数量：只取前 10 条
SELECT * FROM users LIMIT 10;

-- 修改小易的昵称
UPDATE users SET nickname = '小易易' WHERE id = 1;
 
-- 千万别忘了 WHERE！不加 WHERE 会修改所有记录：
-- UPDATE users SET nickname = '全改了';  -- 灾难！所有人的昵称都变了

-- 删除 id 为 3 的用户
DELETE FROM users WHERE id = 3;
 
-- 同样，千万别忘了 WHERE！
-- DELETE FROM users;  -- 灾难！所有用户都删了

CREATE TABLE books (
  id        SERIAL PRIMARY KEY,
  user_id   INTEGER REFERENCES users(id),  -- 外键，指向 users 表
  title     VARCHAR(500) NOT NULL,
  format    VARCHAR(10) DEFAULT 'epub'
);
 
-- 给小易添加两本书
INSERT INTO books (user_id, title, format) VALUES
  (1, '深入理解 TypeScript', 'epub'),
  (1, 'React 设计模式', 'pdf');
 
-- 给小明添加一本书
INSERT INTO books (user_id, title, format) VALUES
  (2, '数据库系统概念', 'epub');

-- 查询所有书以及它们的主人是谁
SELECT books.title, books.format, users.nickname
FROM books
JOIN users ON books.user_id = users.id;
 
-- 结果：
--        title          | format | nickname
-- ----------------------+--------+----------
--  深入理解 TypeScript   | epub   | 小易
--  React 设计模式        | pdf    | 小易
--  数据库系统概念        | epub   | 小明
 
-- 只查小易的书
SELECT books.title, books.format
FROM books
JOIN users ON books.user_id = users.id
WHERE users.nickname = '小易';

-- 创建高亮表，position 用 JSONB 类型
CREATE TABLE highlights (
  id        SERIAL PRIMARY KEY,
  book_id   INTEGER REFERENCES books(id),
  text      TEXT NOT NULL,
  color     VARCHAR(20) DEFAULT 'yellow',
  position  JSONB NOT NULL  -- 灵活存储定位信息
);
 
-- EPUB 的高亮（用 CFI 定位）
INSERT INTO highlights (book_id, text, color, position) VALUES
  (1, '类型是值的集合', 'yellow',
   '{"type": "epub", "cfi": "epubcfi(/6/4!/4/2/1:0,/6/4!/4/2/1:7)"}');
 
-- PDF 的高亮（用页码+坐标定位）
INSERT INTO highlights (book_id, text, color, position) VALUES
  (2, '组合优于继承', 'green',
   '{"type": "pdf", "page": 42, "x": 100, "y": 200, "width": 300, "height": 20}');
 
-- 查询所有 EPUB 格式的高亮（JSONB 可以直接查里面的字段！）
SELECT text, color, position->>'cfi' as cfi
FROM highlights
WHERE position->>'type' = 'epub';

-- 1. 连接数据库
-- psql bookmark_dev
 
-- 2. 创建完整的表结构
CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  email       VARCHAR(255) UNIQUE NOT NULL,
  nickname    VARCHAR(50),
  created_at  TIMESTAMP DEFAULT NOW()
);
 
CREATE TABLE books (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER REFERENCES users(id),
  title       VARCHAR(500) NOT NULL,
  author      VARCHAR(255),
  format      VARCHAR(10) DEFAULT 'epub',
  created_at  TIMESTAMP DEFAULT NOW()
);
 
CREATE TABLE notes (
  id          SERIAL PRIMARY KEY,
  book_id     INTEGER REFERENCES books(id),
  content     TEXT NOT NULL,
  created_at  TIMESTAMP DEFAULT NOW(),
  updated_at  TIMESTAMP DEFAULT NOW()
);
 
CREATE TABLE highlights (
  id          SERIAL PRIMARY KEY,
  book_id     INTEGER REFERENCES books(id),
  text        TEXT NOT NULL,
  color       VARCHAR(20) DEFAULT 'yellow',
  position    JSONB NOT NULL,
  created_at  TIMESTAMP DEFAULT NOW()
);
 
-- 3. 插入测试数据
INSERT INTO users (email, nickname) VALUES ('xiaoyi@test.com', '小易');
 
INSERT INTO books (user_id, title, author, format) VALUES
  (1, '深入理解 TypeScript', 'basarat', 'epub'),
  (1, 'React 设计模式', 'Krasimir Tsonev', 'pdf');
 
INSERT INTO notes (book_id, content) VALUES
  (1, '第三章的泛型讲得很清楚，需要再看一遍'),
  (1, 'extends 关键字在类型编程中有两个完全不同的含义');
 
INSERT INTO highlights (book_id, text, color, position) VALUES
  (1, '类型是值的集合', 'yellow',
   '{"type": "epub", "cfi": "epubcfi(/6/4!/4/2/1:0,/6/4!/4/2/1:7)"}'),
  (2, '组合优于继承', 'green',
   '{"type": "pdf", "page": 42, "rect": {"x": 100, "y": 200}}');
 
-- 4. 常见查询练习
 
-- 查询小易的所有书
SELECT b.title, b.author, b.format
FROM books b
JOIN users u ON b.user_id = u.id
WHERE u.nickname = '小易';
 
-- 查询某本书的所有笔记和高亮数量
SELECT
  b.title,
  (SELECT COUNT(*) FROM notes n WHERE n.book_id = b.id) as note_count,
  (SELECT COUNT(*) FROM highlights h WHERE h.book_id = b.id) as highlight_count
FROM books b
WHERE b.user_id = 1;
 
-- 搜索笔记内容包含"泛型"的记录
SELECT n.content, b.title
FROM notes n
JOIN books b ON n.book_id = b.id
WHERE n.content LIKE '%泛型%';
 
-- 查询所有 PDF 格式书籍的高亮
SELECT h.text, h.color, h.position->>'page' as page
FROM highlights h
JOIN books b ON h.book_id = b.id
WHERE b.format = 'pdf';
 
-- 5. 清理（练习完可以删掉这些表）
-- DROP TABLE highlights;
-- DROP TABLE notes;
-- DROP TABLE books;
-- DROP TABLE users;

# Homebrew 安装的
brew services start postgresql@16    # 启动
brew services stop postgresql@16     # 停止
brew services restart postgresql@16  # 重启
brew services list                   # 查看所有服务状态
 
# Docker 安装的
docker start bookmark-pg    # 启动
docker stop bookmark-pg     # 停止
docker restart bookmark-pg  # 重启
docker ps                   # 查看运行中的容器

# Homebrew 安装的，数据目录
/opt/homebrew/var/postgresql@16
 
# Docker 安装的，在容器内部
# 如果需要持久化，启动时加 -v 参数挂载到本地目录

dropdb bookmark_dev            # Homebrew
docker exec -it bookmark-pg dropdb -U postgres bookmark_dev  # Docker

# Homebrew
brew services stop postgresql@16
brew uninstall postgresql@16
rm -rf /opt/homebrew/var/postgresql@16  # 删除数据（谨慎！）
 
# Docker
docker stop bookmark-pg
docker rm bookmark-pg