obsidian/BACKEND/数据库基础.md

tags, create time

tags	create time
后端 数据库 SQL 基础	2026-04-24 18:41

数据库基础

概述

数据库是后端的核心。几乎每个应用都需要持久化数据——用户信息、订单记录、文章内容……关系型数据库（MySQL/PostgreSQL）是后端开发的基石。

思考题：为什么关系型数据库能统治这么多年？JSON 数据库（如 MongoDB）出现了，为什么我们仍然需要 SQL？

正文

1. 关系模型

关系数据库用"表"来组织数据，表与表之间通过"关系"（外键）连接。

erDiagram
    USERS ||--o{ POSTS : creates
    USERS {
        int id PK
        string username
        string email
        datetime created_at
    }
    POSTS {
        int id PK
        int user_id FK
        string title
        string content
        datetime published_at
    }

2. 基础 SQL

-- 创建表
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(50) NOT NULL UNIQUE,
    email       VARCHAR(100) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    created_at  TIMESTAMP DEFAULT NOW()
);

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

-- 查询数据
SELECT id, username, email FROM users WHERE id = 1;

-- 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;

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

3. 多表查询

-- JOIN 联表查询
SELECT u.username, p.title, p.content
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE u.id = 1;

-- 子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM posts WHERE created_at > '2026-01-01');

-- 聚合查询
SELECT user_id, COUNT(*) as post_count
FROM posts
GROUP BY user_id
HAVING post_count > 10;

4. 索引 — 数据库性能的命脉

-- 创建索引
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

-- 复合索引
CREATE INDEX idx_user_email ON users(email, created_at);

graph LR
    A[全表扫描 O(n)] -.->|慢| B[数据量增大]
    C[索引查找 O(log n)] -.->|快| D[数据量增大]

核心要点： 索引加速查询但减慢写入。每条写入（INSERT/UPDATE/DELETE）都需要更新索引。不是所有字段都需要索引，通常只给经常用于 WHERE 和 JOIN 的字段加索引。

提问： B+ 树索引为什么比哈希索引更适合范围查询？

5. 事务（Transaction）— 数据的最后一道防线

-- 事务保证要么全部成功，要么全部回滚
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 检查是否有错误
-- COMMIT;   → 全部提交
-- ROLLBACK; → 全部回滚

ACID 四个特性：

特性	说明	示例
Atomivity（原子性）	事务要么全做，要么全不做	转账时扣款和加款必须同时成功
Consistency（一致性）	事务前后数据保持一致	转账前后总金额不变
Isolation（隔离性）	并发事务互不干扰	两个用户同时转账不会出问题
Durability（持久性）	提交后数据永久保存	断电后数据不会丢失

思考： 如果转账过程中服务器在两条 UPDATE 之间崩溃，会发生什么？ACID 的原子性如何避免这个问题？

6. Go 操作数据库示例

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/lib/pq" // PostgreSQL 驱动
)

type User struct {
    ID       int
    Username string
    Email    string
}

func getUser(db *sql.DB, id int) (*User, error) {
    var user User
    err := db.QueryRow("SELECT id, username, email FROM users WHERE id = $1", id).
        Scan(&user.ID, &user.Username, &user.Email)
    if err != nil {
        return nil, err
    }
    return &user, nil
}

// 事务示例
func transfer(db *sql.DB, fromID, toID, amount int) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback() // 出错时自动回滚

    _, err = tx.Exec("UPDATE accounts SET balance = balance - $1 WHERE user_id = $2", amount, fromID)
    if err != nil {
        return err
    }

    _, err = tx.Exec("UPDATE accounts SET balance = balance + $1 WHERE user_id = $2", amount, toID)
    if err != nil {
        return err
    }

    return tx.Commit()
}

关联笔记

• API 设计 — API 需要操作数据库
• Go 后端基础 — Go 连接数据库的实践
• 30.areas/finance/Investment lessons/2024.Current trading lessons.md