19. 数据库原理基础

-- DDL：数据定义
CREATE TABLE users (
    id       BIGINT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(64)  NOT NULL,
    email    VARCHAR(128) UNIQUE,
    age      INT          CHECK (age >= 0),
    dept_id  BIGINT,
    FOREIGN KEY (dept_id) REFERENCES dept(id)
);

-- DML：数据操作
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@x.com', 23);
UPDATE users SET age = 24 WHERE name = 'Alice';
DELETE FROM users WHERE id = 1;

-- DQL：查询
SELECT d.name, COUNT(u.id) AS cnt
FROM dept d
LEFT JOIN users u ON u.dept_id = d.id
WHERE u.age >= 20
GROUP BY d.id
HAVING COUNT(u.id) > 0
ORDER BY cnt DESC
LIMIT 10;

# B+ 树插入引发节点分裂的示意（m=3 阶）
def b_plus_insert(node, key):
    if node.is_leaf:
        node.keys.insert_sorted(key)
        if len(node.keys) > node.max_keys:      # 上溢出
            left, right = split_leaf(node)      # 从中间切分
            return left, right                  # 中间键上升为父路由键
    else:
        child = node.find_child(key)
        res = b_plus_insert(child, key)
        if res:                                 # 子节点分裂
            node.keys.insert(res.mid_key)
            if len(node.keys) > node.max_keys:  # 内部节点也分裂
                return split_internal(node)
    return None

# LSM 读路径：布隆过滤器 + 二分，逐层查找
def lsm_get(leveled_ssts, memtable, key, bloom):
    if key in memtable:
        return memtable[key]
    for sst in leveled_ssts:                 # 从 L0 到 Ln 逐层
        if key in bloom[sst]:                # 布隆过滤器说"可能存在"
            val = sst.binary_search(key)     # SSTable 内部有序，二分
            if val is not None:
                return val
    return None

-- 经典转账事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 若任一步失败则 ROLLBACK，保证余额守恒
COMMIT;

-- 加锁语句示例
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;          -- X 锁（写意图）
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;  -- S 锁

# WAL 恢复示意
def crash_recovery(redo_log, checkpoint_lsn):
    for record in redo_log.after(checkpoint_lsn):   # 从检查点后重放
        if record.txn_committed:
            apply_redo(record)                      # 把修改重新应用到数据页
    return 0  # 恢复完成，已提交数据不丢失

EXPLAIN SELECT d.name, COUNT(u.id)
FROM dept d LEFT JOIN users u ON u.dept_id = d.id
WHERE d.location = 'Beijing'
GROUP BY d.id;

-- 输出关键列
-- type: ALL | index | range | ref | eq_ref | const   （访问方式，从上到下性能递增）
-- key:  实际使用的索引
-- rows: 预估扫描行数
-- Extra: Using index（覆盖索引）/ Using filesort / Using temporary

-- 违反 2NF：订单明细表存在部分依赖
CREATE TABLE order_items (
    order_id  INT,
    product_id INT,
    product_name VARCHAR(100),   -- 只依赖 product_id，与 order_id 无关
    qty       INT,
    PRIMARY KEY (order_id, product_id)
);
-- 修正：拆出 product 表，订单明细只存 product_id