数据库基础与关系模型：从 SQL 到事务

-- 创建一个用户表
CREATE TABLE users (
    user_id    INTEGER PRIMARY KEY,
    username   VARCHAR(50) NOT NULL,
    email      VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入一行数据
INSERT INTO users (user_id, username, email)
VALUES (1, 'alice', 'alice@example.com');

-- 查询
SELECT * FROM users;

-- 单列主键
CREATE TABLE orders (
    order_id   INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date  DATE NOT NULL,
    total       DECIMAL(10,2)
);

-- 复合主键（多列联合）
CREATE TABLE order_items (
    order_id   INTEGER,
    product_id INTEGER,
    quantity   INTEGER NOT NULL,
    price      DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

-- 使用序列/自增主键（PostgreSQL）
CREATE TABLE products_postgres (
    product_id SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL
);

-- 使用 AUTO_INCREMENT（MySQL）
CREATE TABLE products_mysql (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL
);

-- 创建带外键的表
CREATE TABLE employees (
    emp_id      INTEGER PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    dept_id     INTEGER,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 外键约束行为：ON DELETE CASCADE
CREATE TABLE tasks (
    task_id   INTEGER PRIMARY KEY,
    emp_id    INTEGER,
    title     VARCHAR(200),
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
        ON DELETE CASCADE
);

-- 外键约束行为：ON DELETE SET NULL
CREATE TABLE comments (
    comment_id INTEGER PRIMARY KEY,
    post_id    INTEGER,
    content    TEXT,
    FOREIGN KEY (post_id) REFERENCES posts(post_id)
        ON DELETE SET NULL
);

-- 单列索引
CREATE INDEX idx_users_email ON users(email);

-- 复合索引（多列）
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

-- 唯一索引（自动由 UNIQUE 约束创建）
CREATE UNIQUE INDEX idx_products_sku
ON products(sku);

-- 部分索引（PostgreSQL）
CREATE INDEX idx_active_users
ON users(email)
WHERE status = 'active';

-- 删除索引
DROP INDEX IF EXISTS idx_users_email;

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    name    VARCHAR(100)
);

CREATE TABLE user_profiles (
    profile_id INTEGER PRIMARY KEY,
    user_id    INTEGER UNIQUE,
    bio        TEXT,
    avatar_url VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE departments (
    dept_id   INTEGER PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id   INTEGER PRIMARY KEY,
    name     VARCHAR(100),
    dept_id  INTEGER REFERENCES departments(dept_id)
);

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name       VARCHAR(100)
);

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    title     VARCHAR(100)
);

-- 中间表
CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(student_id),
    course_id  INTEGER REFERENCES courses(course_id),
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, course_id)
);

-- 查询某学生选修的所有课程
SELECT s.name, c.title, e.enrolled_at
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE s.student_id = 1;

-- CREATE：创建数据库、表、视图
CREATE DATABASE shop;

CREATE TABLE categories (
    cat_id   INTEGER PRIMARY KEY,
    cat_name VARCHAR(100) NOT NULL
);

-- ALTER：修改表结构
ALTER TABLE categories ADD COLUMN description TEXT;
ALTER TABLE categories ALTER COLUMN cat_name TYPE VARCHAR(200);
ALTER TABLE categories DROP COLUMN description;
ALTER TABLE categories RENAME TO product_categories;

-- DROP：删除对象
DROP TABLE IF EXISTS temp_data;
DROP DATABASE IF EXISTS old_shop;

-- 创建示例表
CREATE TABLE employees (
    id        INTEGER PRIMARY KEY,
    name      VARCHAR(50),
    dept      VARCHAR(30),
    salary    DECIMAL(10,2),
    hire_date DATE
);

-- 插入数据
INSERT INTO employees VALUES
(1, '张三', '技术部', 15000.00, '2020-03-15'),
(2, '李四', '市场部', 12000.00, '2021-06-01'),
(3, '王五', '技术部', 18000.00, '2019-01-10'),
(4, '赵六', '人事部', 10000.00, '2022-09-20'),
(5, '孙七', '市场部', 13000.00, '2020-11-05'),
(6, '周八', '技术部', 22000.00, '2018-05-12'),
(7, '吴九', '财务部', 11000.00, '2023-02-28');

-- 基本查询
SELECT * FROM employees;

-- 投影：选择特定列
SELECT name, salary FROM employees;

-- 别名
SELECT name AS 姓名, salary AS 薪资 FROM employees;

-- 条件过滤
SELECT * FROM employees
WHERE dept = '技术部' AND salary > 15000;

-- 排序
SELECT * FROM employees
ORDER BY salary DESC;

-- 分页（PostgreSQL / MySQL）
SELECT * FROM employees
ORDER BY id
LIMIT 3 OFFSET 0;  -- 第1页

SELECT * FROM employees
ORDER BY id
LIMIT 3 OFFSET 3;  -- 第2页

-- 单行插入
INSERT INTO employees (id, name, dept, salary, hire_date)
VALUES (8, '陈十', '技术部', 16000.00, '2024-01-15');

-- 多行插入
INSERT INTO employees VALUES
(9, '林十一', '财务部', 11500.00, '2024-03-01'),
(10, '黄十二', '市场部', 12500.00, '2024-04-10');

-- 从查询结果插入
CREATE TABLE high_salary AS
SELECT * FROM employees WHERE salary > 15000;

-- 更新单行
UPDATE employees
SET salary = 16000.00
WHERE id = 4;

-- 更新多行
UPDATE employees
SET salary = salary * 1.10
WHERE dept = '技术部';

-- 使用子查询更新
UPDATE employees
SET salary = (
    SELECT AVG(salary) FROM employees
)
WHERE id = 1;

-- 删除特定行
DELETE FROM employees
WHERE id = 10;

-- 删除所有行
DELETE FROM employees;

-- TRUNCATE（速度更快，不可回滚）
TRUNCATE TABLE employees;

-- 创建用户（PostgreSQL）
CREATE USER app_user WITH PASSWORD 'secure_password';
CREATE USER analyst WITH PASSWORD 'read_only_pass';

-- GRANT：授予权限（PostgreSQL）
GRANT SELECT, INSERT, UPDATE ON employees TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
GRANT USAGE ON SCHEMA public TO app_user;

-- GRANT（MySQL）
GRANT SELECT, INSERT ON shop.* TO 'app_user'@'localhost';
GRANT ALL PRIVILEGES ON shop.* TO 'admin'@'localhost';

-- REVOKE：撤销权限
REVOKE DELETE ON employees FROM app_user;
REVOKE ALL PRIVILEGES ON shop.* FROM 'app_user'@'localhost';

-- 刷新权限（MySQL）
FLUSH PRIVILEGES;

-- 准备数据
CREATE TABLE customers (
    cid INTEGER PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    oid     INTEGER PRIMARY KEY,
    cid     INTEGER,
    product VARCHAR(50),
    amount  DECIMAL(10,2)
);

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO orders VALUES
(101, 1, 'Laptop', 5000),
(102, 1, 'Mouse', 100),
(103, 2, 'Keyboard', 300),
(104, NULL, 'Monitor', 1500);  -- 无客户的订单

-- 只返回有订单的客户
SELECT c.name, o.product, o.amount
FROM customers c
INNER JOIN orders o ON c.cid = o.cid;

-- 结果：
-- Alice | Laptop   | 5000
-- Alice | Mouse    | 100
-- Bob   | Keyboard | 300

-- 返回所有客户及其订单（Charlie 无订单也出现）
SELECT c.name, o.product, o.amount
FROM customers c
LEFT JOIN orders o ON c.cid = o.cid;

-- 结果：
-- Alice   | Laptop   | 5000
-- Alice   | Mouse    | 100
-- Bob     | Keyboard | 300
-- Charlie | NULL     | NULL

-- 返回所有订单，无客户的订单也出现
SELECT c.name, o.product, o.amount
FROM customers c
RIGHT JOIN orders o ON c.cid = o.cid;

-- 结果：
-- Alice | Laptop   | 5000
-- Alice | Mouse    | 100
-- Bob   | Keyboard | 300
-- NULL  | Monitor  | 1500

SELECT c.name, o.product, o.amount
FROM customers c
FULL JOIN orders o ON c.cid = o.cid;

-- 结果：
-- Alice   | Laptop   | 5000
-- Alice   | Mouse    | 100
-- Bob     | Keyboard | 300
-- Charlie | NULL     | NULL
-- NULL    | Monitor  | 1500

SELECT c.name, o.product
FROM customers c
CROSS JOIN orders o;

-- 3 个客户 × 4 个订单 = 12 行结果

-- WHERE 子句中的子查询
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

-- FROM 子句中的子查询（派生表）
SELECT dept, avg_salary
FROM (
    SELECT dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept
) AS dept_stats
WHERE avg_salary > 13000;

-- 关联子查询
SELECT e1.name, e1.salary, e1.dept
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept = e1.dept
);

-- ❌ 违反 1NF：phone 字段包含多个值
CREATE TABLE students_bad (
    id    INTEGER PRIMARY KEY,
    name  VARCHAR(50),
    phone VARCHAR(100)  -- '13800138000, 13900139000'
);

-- ✅ 符合 1NF：将电话拆分为多行或独立表
CREATE TABLE students_good (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE student_phones (
    student_id INTEGER REFERENCES students_good(id),
    phone      VARCHAR(20),
    PRIMARY KEY (student_id, phone)
);

-- 或是在同一行中用多列（有限情况）
CREATE TABLE students_alt (
    id         INTEGER PRIMARY KEY,
    name       VARCHAR(50),
    phone_pri  VARCHAR(20),  -- 主要电话
    phone_sec  VARCHAR(20)   -- 备用电话
);

-- ❌ 违反 3NF：dept_location 依赖于 dept_name，而 dept_name 依赖于 emp_id
-- 存在传递依赖：emp_id → dept_name → dept_location
CREATE TABLE employees_bad (
    emp_id       INTEGER PRIMARY KEY,
    name         VARCHAR(50),
    dept_name    VARCHAR(50),
    dept_location VARCHAR(100)   -- ❌ 传递依赖
);

-- ✅ 符合 3NF：拆分部门信息
CREATE TABLE employees (
    emp_id    INTEGER PRIMARY KEY,
    name      VARCHAR(50),
    dept_id   INTEGER REFERENCES departments(dept_id)
);

CREATE TABLE departments (
    dept_id     INTEGER PRIMARY KEY,
    dept_name   VARCHAR(50),
    location    VARCHAR(100)
);

-- 反范式化示例：订单表冗余存储客户名
-- 范式化设计需要 JOIN：
SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- 反范式化：orders 表直接存储 customer_name
CREATE TABLE orders_denorm (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER,
    customer_name VARCHAR(100),  -- 🚀 冗余字段，避免 JOIN
    total         DECIMAL(10,2)
);
-- 代价：customer 改名时需要同步更新 orders 表

-- 事务示例：银行转账
BEGIN;

-- 从 A 账户扣款
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 'A';

-- 向 B 账户加款
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 'B';

-- 如果其中任意一步失败：
ROLLBACK;  -- 撤销所有更改

-- 如果全部成功：
COMMIT;    -- 永久保存更改

-- 使用保存点（SAVEPOINT）实现部分回滚
BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE account_id = 'A';

SAVEPOINT after_deduction;

UPDATE accounts SET balance = balance + 1000 WHERE account_id = 'B';

-- 检测到目标账户异常（如被冻结）
ROLLBACK TO SAVEPOINT after_deduction;
-- A 的扣款被撤销，回到事务开始前的状态

-- 修复后重试...
COMMIT;

-- 设置隔离级别（PostgreSQL）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置隔离级别（MySQL）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 事务 A（隔离级别：READ UNCOMMITTED）|  事务 B
BEGIN;                                     |
UPDATE accounts SET balance = 5000         |
WHERE id = 1;  -- 未提交                    |
                                           | BEGIN;
                                           | SELECT balance FROM accounts WHERE id = 1;
                                           | -- 读到 5000（脏读！）
ROLLBACK; -- A 回滚，balance 回到 1000     |

-- 事务 A（隔离级别：READ COMMITTED）       |  事务 B
BEGIN;                                     |
UPDATE accounts SET balance = 5000         |
WHERE id = 1;                              |
                                           | BEGIN;
                                           | SELECT balance FROM accounts WHERE id = 1;
                                           | -- 读到 1000（未提交的数据不可见）
COMMIT;                                    |
                                           | SELECT balance FROM accounts WHERE id = 1;
                                           | -- 读到 5000（不可重复读！同一事务中两次读不同）

-- 事务 A（隔离级别：REPEATABLE READ）      |  事务 B
BEGIN;                                     |
SELECT * FROM products WHERE id = 1;       |
-- 读到 price = 100                        |
                                           | BEGIN;
                                           | UPDATE products SET price = 200 WHERE id = 1;
                                           | COMMIT;
SELECT * FROM products WHERE id = 1;       |
-- 仍然读到 price = 100（可重复读）          |
                                           | -- 但可能出现幻读：
                                           | INSERT INTO products (id, price) VALUES (10, 300);
                                           | COMMIT;
SELECT * FROM products WHERE price > 250;  |
-- 可能读到新插入的行（幻读）               |

-- 设置最高隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 或 PostgreSQL 的 SSI（可序列化快照隔离）
BEGIN ISOLATION LEVEL SERIALIZABLE;

UPDATE inventory SET qty = qty - 1 WHERE product_id = 1;
UPDATE orders SET status = 'confirmed' WHERE order_id = 100;

COMMIT;
-- 如果并发冲突，PostgreSQL 会抛出：
-- ERROR: could not serialize access due to read/write dependencies

时间线：
T1: BEGIN
T1: UPDATE product SET price = 0 WHERE id = 1  (未提交)
T2: BEGIN
T2: SELECT price FROM product WHERE id = 1 → 读到 0 (脏读)
T1: ROLLBACK  (实际 price 应还是 100)
T2: 基于 price=0 做了错误决策...

时间线：
T1: BEGIN
T1: SELECT balance FROM account WHERE id = 1 → 1000
T2: UPDATE account SET balance = 2000 WHERE id = 1; COMMIT
T1: SELECT balance FROM account WHERE id = 1 → 2000 (不一样了!)
T1: COMMIT

时间线：
T1: BEGIN
T1: SELECT * FROM orders WHERE amount > 100 → 返回 2 行
T2: INSERT INTO orders (id, amount) VALUES (3, 200); COMMIT
T1: SELECT * FROM orders WHERE amount > 100 → 返回 3 行 (多了一行!)
T1: COMMIT

-- MVCC 的每行数据在内部包含两个系统列（以 PostgreSQL 为例）：
-- xmin：创建该行版本的事务 ID
-- xmax：删除/更新该行版本的事务 ID

-- 创建表并插入数据
CREATE TABLE items (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price NUMERIC
);

INSERT INTO items VALUES (1, 'Widget', 100);

-- 事务 T1 执行更新
BEGIN;  -- T1 的 xid = 100
UPDATE items SET price = 120 WHERE id = 1;
-- 物理操作：
--   1. 将原行标记为已删除（xmax = 100）
--   2. 插入新行（xmin = 100），price = 120
COMMIT;

-- 事务 T2 并发读取（T2 的 xid = 101）
BEGIN;
SELECT * FROM items WHERE id = 1;
-- 根据快照可见性规则：
--   如果 T2 启动时 T1 尚未提交 → 看到旧版本（price = 100）
--   如果 T2 启动时 T1 已提交   → 看到新版本（price = 120）
COMMIT;

-- PostgreSQL 查看事务和死元组信息
SELECT
    schemaname,
    tablename,
    n_dead_tup,
    n_live_tup,
    last_autovacuum
FROM pg_stat_user_tables
WHERE tablename = 'items';

-- 手动清理死元组
VACUUM items;
VACUUM FULL items;  -- 更彻底但会锁表

-- PostgreSQL 查看执行计划
EXPLAIN SELECT * FROM employees WHERE dept = '技术部';

-- 显示实际执行时间和行数
EXPLAIN ANALYZE SELECT * FROM employees WHERE dept = '技术部';

-- MySQL
EXPLAIN SELECT * FROM employees WHERE dept = '技术部'\G

-- 示例：比较有索引和无索引的执行计划

-- 无索引时
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 15000;
-- Seq Scan on employees (cost=0.00..35.50 rows=3 width=120)
-- 实际执行时间: 0.523 ms

-- 创建索引后
CREATE INDEX idx_salary ON employees(salary);

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 15000;
-- Index Scan using idx_salary on employees (cost=0.28..8.29 rows=3 width=120)
-- 实际执行时间: 0.089 ms ✅

B+树结构示意图：
                 [50, 80]
               /    |    \
         [10,30,40] [60,70] [90,100]
         /    |    \   |   \   |    \
      数据  数据  数据 数据 数据 数据 数据

内部节点只存键值，不存数据 → 树更矮 → IO更少
叶子节点通过链表相连 → 范围查询高效

-- B+树索引示例
CREATE INDEX idx_employees_salary ON employees(salary);

-- 范围查询利用 B+树的有序性
SELECT * FROM employees
WHERE salary BETWEEN 10000 AND 20000
ORDER BY salary;

-- MySQL 支持显式指定哈希索引（Memory 引擎）
CREATE TABLE lookup (
    id INTEGER PRIMARY KEY,
    code VARCHAR(10)
) ENGINE=MEMORY;

CREATE INDEX idx_code USING HASH ON lookup(code);

-- PostgreSQL 的哈希索引
CREATE INDEX idx_employees_id_hash ON employees USING HASH (id);

-- MySQL 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;      -- 超过 1 秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- PostgreSQL 开启慢查询
-- 在 postgresql.conf 中配置：
-- log_min_duration_statement = 1000  -- 超过 1000ms 的查询

-- ❌ 慢：读取所有列，可能回表
SELECT * FROM employees;
-- ✅ 快：只取需要的列，可能只用索引
SELECT id, name, salary FROM employees;

-- ❌ 函数在列上，无法使用索引
SELECT * FROM employees
WHERE LOWER(email) = 'alice@example.com';
-- ✅ 改为等值比较
SELECT * FROM employees
WHERE email = 'Alice@Example.com';
-- 或使用表达式索引（PostgreSQL）
CREATE INDEX idx_email_lower ON employees(LOWER(email));

-- 小表驱动大表：先将数据量小的结果集作为驱动表
-- 假设 departments 有 10 行，employees 有 100 万行
-- ✅ 高效：先筛选 departments，再 JOIN employees
SELECT e.name, d.dept_name
FROM departments d
JOIN employees e ON e.dept_id = d.dept_id
WHERE d.dept_name = '技术部';

-- ❌ 列是 INTEGER，传入字符串 → 隐式转换，无法使用索引
SELECT * FROM employees WHERE id = '100';  -- 隐式转换
-- ✅ 正确匹配类型
SELECT * FROM employees WHERE id = 100;

-- ❌ 传统分页：OFFSET 越大越慢
SELECT * FROM employees
ORDER BY id
LIMIT 20 OFFSET 100000;  -- 需要扫描 100020 行

-- ✅ 游标分页 / 键集分页
SELECT * FROM employees
WHERE id > 100000  -- 记录上一页最后一条的 id
ORDER BY id
LIMIT 20;

-- PostgreSQL 查看当前正在运行的慢查询
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC
LIMIT 10;

-- MySQL 查看正在运行的线程
SHOW FULL PROCESSLIST;