📚 课程目录第2章工程环境搭建

2. 工程环境搭建

SQL 工程项目与“在客户端里随手写几条查询”最大的区别在于：项目需要一套可复现、可协作、可版本化的运行环境。环境搭建的目标不是把数据库装上能连上就结束，而是让团队任何一名成员拿到仓库后，都能按同一套步骤得到一致的数据库实例、一致的连接方式、一致的目录结构和一致的初始数据。本节围绕四个交付物展开：数据库实例、连接工具配置、版本管理约定、项目目录与初始化脚本。

一、数据库实例：选型与安装

教学与中小型项目常用 PostgreSQL 或 MySQL。以 PostgreSQL 16 为例，Windows 可用官方 EDB 安装包，macOS 用 Homebrew，Linux 用 apt/yum；容器方式则统一用 Docker，便于团队对齐版本。下面给出容器方式的示例配置，端口、密码、数据卷均为示例参数，实际项目应替换为团队约定值。

# docker-compose.yml（示例）
services:
  pg:
    image: postgres:16.3
    container_name: sqlproj-pg
    environment:
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: app_pwd_2024
      POSTGRES_DB: shop_dev
      TZ: Asia/Shanghai
    ports:
      - "5432:5432"
    volumes:
      - ./pgdata:/var/lib/postgresql/data
      - ./sql/init:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app_user -d shop_dev"]
      interval: 5s
      retries: 10

启动命令为 docker compose up -d，随后用 docker compose ps 观察状态是否为 healthy。这里有两个关键点：一是把 ./sql/init 挂载到 /docker-entrypoint-initdb.d，容器首次初始化时会按文件名字典序自动执行其中的 .sql 脚本，这正是“初始化脚本”落地的地方；二是数据卷 ./pgdata 保证重启后数据不丢，但要注意：初始化脚本只在数据目录为空时执行一次，若想重跑，需要先 docker compose down 再删除 pgdata 目录。

二、连接工具：命令行与图形客户端

命令行工具 psql 是排查问题的第一选择，连接命令为：

psql -h 127.0.0.1 -p 5432 -U app_user -d shop_dev
# 进入后常用元命令
\l        -- 列出数据库
\dt       -- 列出当前库的表
\d orders -- 查看 orders 表结构
\i sql/init/02_seed.sql  -- 执行外部脚本

图形客户端可选 DBeaver、DataGrip 或 pgAdmin。配置连接时建议把主机、端口、库名、用户写入连接配置并保存为团队共享模板，密码通过环境变量或本地凭据库保存，不要写进仓库。连接成功后，先在 SQL 编辑器执行 SELECT version(); 和 SELECT current_database(), current_user;，确认版本与身份符合预期，再开始后续操作。

三、版本管理：仓库里放什么、不放什么

数据库项目的版本管理核心是“结构变更可追溯、敏感信息不入库、数据可重建”。推荐约定：DDL 与初始化脚本、迁移脚本、查询脚本、文档全部入库；数据卷目录、日志、本地连接配置、密码文件全部忽略。示例 .gitignore：

pgdata/
*.log
.env
.idea/
.vscode/settings.json

迁移脚本命名建议采用“序号_动作_对象.sql”，例如 001_create_customers.sql、002_create_orders.sql、003_add_order_status_index.sql。序号保证执行顺序，动作与对象保证可读性。每次结构变更新增一个文件，不修改已提交的历史脚本，这样任何环境都能按序号重放出同一结构。

四、项目目录结构与初始化脚本

一个可直接使用的目录结构如下：

sql-project/
├── docker-compose.yml
├── .gitignore
├── README.md
├── sql/
│   ├── init/          # 容器首次启动自动执行
│   │   ├── 01_schema.sql
│   │   └── 02_seed.sql
│   ├── migrations/    # 增量结构变更
│   │   ├── 001_create_customers.sql
│   │   └── 002_create_orders.sql
│   └── queries/       # 业务查询与分析脚本
│       ├── daily_sales.sql
│       └── top_customers.sql
└── docs/
    └── er-diagram.md

初始化脚本分两层：01_schema.sql 建表与约束，02_seed.sql 灌入基础数据。示例建表片段：

-- 01_schema.sql（示例）
CREATE TABLE customers (
  id          BIGSERIAL PRIMARY KEY,
  name        VARCHAR(64)  NOT NULL,
  phone       VARCHAR(20)  UNIQUE,
  created_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  customer_id  BIGINT NOT NULL REFERENCES customers(id),
  amount       NUMERIC(12,2) NOT NULL CHECK (amount >= 0),
  status       VARCHAR(16) NOT NULL DEFAULT 'pending',
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_created  ON orders(created_at);

种子数据示例：

-- 02_seed.sql（示例）
INSERT INTO customers (name, phone) VALUES
  ('张伟', '13800000001'),
  ('李娜', '13800000002'),
  ('王强', '13800000003');

INSERT INTO orders (customer_id, amount, status) VALUES
  (1, 199.00, 'paid'),
  (1,  89.50, 'pending'),
  (2, 320.00, 'paid');

执行完成后，用三条查询验证环境是否真正可用：SELECT count(*) FROM customers; 应返回 3；SELECT count(*) FROM orders; 应返回 3；SELECT c.name, sum(o.amount) FROM customers c JOIN orders o ON o.customer_id=c.id GROUP BY c.name ORDER BY 2 DESC; 应得到张伟 288.50、李娜 320.00 的聚合结果。若数量或金额不符，优先检查初始化脚本是否被重复执行或未执行。

五、常见问题与判断依据

环境搭建阶段最容易踩的坑有三类。第一类是端口冲突：本机已有 PostgreSQL 占用 5432，容器启动后 docker compose ps 显示 unhealthy，此时把宿主机端口改为 5433 并同步修改连接串即可。第二类是初始化脚本未执行：如果 ./pgdata 目录已存在，容器会跳过 docker-entrypoint-initdb.d，表现为表不存在；处理方式是 docker compose down 后删除 pgdata 再启动。第三类是权限与编码问题：种子数据含中文时，确认数据库编码为 UTF8，客户端 \encoding 输出为 UTF8，否则会出现乱码。

建议把“启动容器 → 等待 healthy → 执行验证查询”写成 README 中的三步检查清单，新成员按清单操作，能在五分钟内判断环境是否可用，而不是靠感觉“好像连上了”。

完成本节后，你应当拥有一个由 docker-compose 定义、由 sql/init 初始化、由 psql 或图形客户端连接、由 Git 管理变更的数据库工程。后续所有建表、查询、优化与迁移，都在这套环境上进行，保证结果可复现、问题可定位、协作可对齐。