SQL 重点速查（以 PostgreSQL 为例）

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  role VARCHAR(20) NOT NULL DEFAULT 'user'
    CHECK (role IN ('user', 'admin')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE tasks (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  title VARCHAR(200) NOT NULL,
  completed BOOLEAN NOT NULL DEFAULT FALSE,
  due_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO tasks (user_id, title) VALUES (1, '学习 JOIN')
RETURNING *;

SELECT id, title, completed
FROM tasks
WHERE user_id = 1 AND completed = FALSE
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

UPDATE tasks SET completed = TRUE WHERE id = 1 AND user_id = 1
RETURNING *;

DELETE FROM tasks WHERE id = 1 AND user_id = 1
RETURNING id;

SELECT u.id, u.email, COUNT(t.id) AS task_count
FROM users AS u
LEFT JOIN tasks AS t ON t.user_id = u.id
GROUP BY u.id, u.email
HAVING COUNT(t.id) >= 3
ORDER BY task_count DESC;

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 任何一步失败时执行 ROLLBACK；应用代码也必须正确捕获错误。

// 正确：数据和值分开传递
await pool.query(
  'SELECT id, email FROM users WHERE email = $1',
  [email]
);

// 错误：用户输入被拼入 SQL，存在注入风险
// await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

CREATE INDEX idx_tasks_user_created
ON tasks (user_id, created_at DESC);

EXPLAIN ANALYZE
SELECT * FROM tasks
WHERE user_id = 1
ORDER BY created_at DESC
LIMIT 20;