数据库学习教程

    SQL 速查

    DDL：定义

    CREATE TABLE orders (
    id         BIGINT       PRIMARY KEY,
    user_id    BIGINT       NOT NULL,
    amount     DECIMAL(12,2) NOT NULL,
    status     VARCHAR(16)  NOT NULL DEFAULT 'created',
    created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_created (user_id, created_at)
);

ALTER TABLE orders ADD COLUMN channel VARCHAR(16) NULL;
ALTER TABLE orders MODIFY amount DECIMAL(14,2) NOT NULL;
ALTER TABLE orders DROP COLUMN channel;
DROP TABLE orders;
TRUNCATE TABLE orders;          -- 清空且不可回滚

    DML：增删改

    INSERT INTO orders (id, user_id, amount) VALUES (1, 7, 99.5);
INSERT INTO orders (id, user_id, amount)
VALUES (2, 8, 10.0), (3, 9, 20.0);           -- 多值批量

UPDATE orders SET status = 'paid' WHERE id = 1;
DELETE FROM orders WHERE status = 'cancelled';

-- UPSERT（MySQL）
INSERT INTO inventory (sku, qty) VALUES ('A1', 10)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);

-- UPSERT（PostgreSQL）
INSERT INTO inventory (sku, qty) VALUES ('A1', 10)
ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;

    查询

    SELECT [DISTINCT] col1, COUNT(*) AS cnt
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid' AND o.created_at >= '2026-01-01'
GROUP BY col1
HAVING COUNT(*) > 10
ORDER BY cnt DESC
LIMIT 20 OFFSET 0;

    逻辑执行顺序：

    FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

    连接

    类型保留
    INNER JOIN两表都匹配
    LEFT JOIN左表全部
    RIGHT JOIN右表全部
    FULL JOIN两表全部（MySQL 用 UNION 模拟）
    CROSS JOIN笛卡尔积
    SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

    聚合与条件

    函数说明
    COUNT(*) / COUNT(col)计数（后者忽略 NULL）
    SUM / AVG / MIN / MAX聚合
    GROUP_CONCAT(col)组内拼接（MySQL）
    STRING_AGG(col, ',')组内拼接（PostgreSQL）
    IFNULL(a,b) / COALESCE(...)空值兜底
    CASE WHEN ... THEN ... END条件分支
    IF(cond,a,b)（MySQL）三元

    窗口函数

    SELECT name, city, amount,
       ROW_NUMBER()  OVER (PARTITION BY city ORDER BY amount DESC) AS rn,
       RANK()        OVER (PARTITION BY city ORDER BY amount DESC) AS rk,
       SUM(amount)   OVER (PARTITION BY city)                      AS city_total,
       LAG(amount)   OVER (PARTITION BY city ORDER BY created_at)  AS prev_amount
FROM orders o JOIN users u ON u.id = o.user_id;

    子查询与 CTE

    SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

WITH paid AS (
    SELECT user_id, SUM(amount) AS gmv FROM orders
    WHERE status = 'paid' GROUP BY user_id
)
SELECT * FROM paid WHERE gmv > 1000;

    事务

    BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;     -- 或 ROLLBACK

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;   -- 当前读 + 排他锁

    索引

    CREATE INDEX idx_orders_status ON orders (status);
CREATE UNIQUE INDEX uk_users_email ON users (email);
CREATE INDEX idx_orders_cover ON orders (user_id, status) INCLUDE (amount);  -- PG
CREATE INDEX CONCURRENTLY idx_pg ON orders (user_id);                         -- PG 在线
ALTER TABLE orders DROP INDEX idx_orders_status;
SHOW INDEX FROM orders;   -- MySQL
    原则说明
    最左前缀联合索引从最左列连续匹配
    等值在前、范围在后高区分度等值列放左边
    覆盖索引查询列全在索引内，免回表
    失效函数/隐式转换、前导 %、OR 非索引列

    EXPLAIN 关键字段

    字段关注
    type（MySQL）ALL 全表 ⚠️，range/ref/const 好
    key实际使用索引
    rows预估扫描行数
    ExtraUsing filesort/temporary ⚠️，Using index 好
    PG cost / actual预估 vs 实测，差异大需 ANALYZE

    小结

    • 记忆求值顺序能解释大部分「别名不能用」的问题。
    • UPSERT 语法 MySQL/PG 不同；COUNT(col) 忽略 NULL。
    • 窗口函数不折叠行；ROW_NUMBER/RANK/SUM OVER 最常用。
    • 索引记住「最左前缀、等值在前、范围在后、覆盖免回表」。