SQL 基础：执行顺序、增删改查到窗口函数

CREATE TABLE users (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  username    VARCHAR(64)     NOT NULL COMMENT '用户名',
  email       VARCHAR(128)    NOT NULL DEFAULT '' COMMENT '邮箱',
  city        VARCHAR(32)              COMMENT '所在城市（可空，用于演示 NULL 语义）',
  age         SMALLINT        NOT NULL DEFAULT 0 COMMENT '年龄',
  referrer_id BIGINT UNSIGNED          COMMENT '邀请人ID（可空）',
  created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '用户表';

CREATE TABLE products (
  id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name     VARCHAR(128)    NOT NULL COMMENT '商品名',
  category VARCHAR(32)     NOT NULL DEFAULT '' COMMENT '分类',
  price    DECIMAL(10, 2)  NOT NULL DEFAULT 0.00 COMMENT '售价',
  stock    INT             NOT NULL DEFAULT 0 COMMENT '库存',
  PRIMARY KEY (id),
  UNIQUE KEY uk_name (name)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '商品表';

CREATE TABLE orders (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no   VARCHAR(32)     NOT NULL COMMENT '订单号',
  user_id    BIGINT UNSIGNED NOT NULL,
  status     TINYINT         NOT NULL DEFAULT 0 COMMENT '0待付款 1已付款 2已发货 3完成 4退款',
  amount     DECIMAL(10, 2)  NOT NULL DEFAULT 0.00 COMMENT '订单金额',
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_time (user_id, created_at)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '订单表';

CREATE TABLE order_items (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id   BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity   SMALLINT        NOT NULL DEFAULT 1,
  price      DECIMAL(10, 2)  NOT NULL DEFAULT 0.00 COMMENT '成交单价',
  PRIMARY KEY (id),
  KEY idx_order (order_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT = '订单明细表';

FROM / JOIN ... ON   -- 先确定数据来源和连接
WHERE                -- 在原始行上过滤
GROUP BY             -- 分组
HAVING               -- 在分组结果上过滤
SELECT（含表达式、聚合函数）  -- 这时才计算投影和别名
DISTINCT
ORDER BY             -- 排序时别名已可见
LIMIT                -- 最后截取

-- WHERE 在 SELECT 之前求值，别名还不存在，报错：
SELECT city AS c FROM users WHERE c = '杭州';
-- ERROR 1054: Unknown column 'c' in 'where clause'

-- GROUP BY / HAVING / ORDER BY 在 SELECT 之后（或同时）求值，MySQL 允许引用别名：
SELECT city AS c, COUNT(*) AS cnt
FROM users
GROUP BY c
HAVING cnt > 10
ORDER BY cnt DESC;

CREATE DATABASE IF NOT EXISTS shop
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

DROP DATABASE IF EXISTS shop_test;  -- 库名不给引号，删库前先 USE + SHOW TABLES 确认

-- 加列（8.0 起追加到表尾通常是 INSTANT，秒级完成）
ALTER TABLE orders ADD COLUMN remark VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注';

-- 改列：注意 MODIFY 是"整列定义重写"
ALTER TABLE orders MODIFY COLUMN amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '订单金额';

-- 索引与表选项（改列名用 RENAME COLUMN，改类型见下方注意事项）
ALTER TABLE orders ADD KEY idx_status (status), DROP KEY idx_user_time;
ALTER TABLE users RENAME TO user_info;

-- 单行：列清单显式写全，别依赖列顺序
INSERT INTO orders (order_no, user_id, status, amount)
VALUES ('A20261020001', 101, 0, 199.00);

-- 多行：一条语句批量插入，比循环单条快得多（网络往返、事务开销都省）
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
  (1001, 7, 2, 59.00),
  (1001, 9, 1, 81.00),
  (1002, 7, 1, 59.00);

-- INSERT ... SELECT：跨表搬运
INSERT INTO order_items_archive (order_id, product_id, quantity, price)
SELECT order_id, product_id, quantity, price FROM order_items WHERE order_id < 900000;

-- 未列出的列取 DEFAULT；无默认值且 NOT NULL 则报错（严格模式）

-- 老写法（VALUES 函数，8.0.20 起弃用）：
INSERT INTO products (name, category, price, stock) VALUES ('键盘K80', '外设', 199.00, 50)
ON DUPLICATE KEY UPDATE stock = stock + VALUES(stock);

-- 8.0.19 起的推荐写法：给待插入行起别名，用别名引用新值
INSERT INTO products (name, category, price, stock) VALUES ('键盘K80', '外设', 199.00, 50) AS new
ON DUPLICATE KEY UPDATE stock = stock + new.stock, price = new.price;

INSERT IGNORE INTO users (id, username) VALUES (5, 'bob');
REPLACE INTO users (id, username, email) VALUES (5, 'bobby', 'b@x.com');

UPDATE orders SET status = 1, amount = amount - 10.00
WHERE id = 1001 AND status = 0;   -- 带上 status=0 做乐观校验，防止重复支付标记

DELETE FROM order_items WHERE order_id = 1002 AND quantity < 0;

-- 分批处理：UPDATE/DELETE 支持 ORDER BY + LIMIT（按主键推进，避免一次锁几十万行）
UPDATE orders SET status = 4
WHERE status = 0 AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY id LIMIT 1000;   -- 循环执行直到 affected rows 为 0

-- WHERE 常用谓词
SELECT * FROM orders
WHERE status IN (1, 2)                      -- 枚举
  AND amount BETWEEN 100 AND 500            -- 闭区间
  AND created_at >= '2026-10-01'             -- 时间左闭右开更防歧义
  AND city IS NULL                           -- 判空只能用 IS NULL（见 NULL 一节）
ORDER BY amount DESC, id ASC                 -- 排序列加 id 兜底，保证结果确定
LIMIT 10 OFFSET 20;                          -- 第 3 页：每页 10 条，偏移从 0 计

SELECT city, COUNT(*) AS user_cnt, AVG(age) AS avg_age,
       MAX(created_at) AS last_join
FROM users
GROUP BY city;

Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'shop.users.email' which is not functionally
dependent on columns in the GROUP BY clause

SELECT order_id,
       GROUP_CONCAT(product_id ORDER BY product_id SEPARATOR ',') AS skus
FROM order_items GROUP BY order_id;

-- INNER JOIN：两边都能匹配上的才留下
SELECT o.order_no, u.username
FROM orders o INNER JOIN users u ON u.id = o.user_id;

-- LEFT JOIN：左表全保留，右表配不上的补 NULL
SELECT u.username, COUNT(o.id) AS order_cnt
FROM users u LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username;            -- COUNT(o.id) 数的是右表非 NULL，即 0 单用户得 0

-- RIGHT JOIN：和 LEFT 镜像，习惯上把左表换成另一张重写，少用
-- CROSS JOIN：笛卡尔积，报表补零/枚举全组合时才有正当用途
SELECT c.city, p.name
FROM (SELECT DISTINCT city FROM users) c
CROSS JOIN products p;

-- 自连接：同一张表扮两个角色
SELECT a.username AS u1, b.username AS u2, a.city
FROM users a JOIN users b ON a.city = b.city AND a.id < b.id;
-- a.id < b.id 去掉自配对和 (b,a) 重复对

-- 需求：所有用户 + 他"已发货(status=2)"的订单
SELECT u.id, u.username, o.order_no
FROM users u LEFT JOIN orders o
  ON o.user_id = u.id AND o.status = 2;
-- 没下过发货单的用户仍在结果里，order_no 为 NULL

SELECT u.id, u.username, o.order_no
FROM users u LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 2;
-- 未匹配行的 o.status 是 NULL，NULL = 2 不成立被 WHERE 滤掉，
-- 结果只剩"有发货单的用户"——等价 INNER JOIN，且往往不是想要的

-- 标量子查询：返回一行一列，能出现在任何单值位置
SELECT username, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_cnt
FROM users u;

-- 列子查询 + IN
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- EXISTS：相关子查询，语义"外层这行是否存在配得上内层的行"
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);

-- 派生表：FROM 里的子查询必须有别名（MySQL 硬性要求）
SELECT t.city, t.cnt FROM (
  SELECT city, COUNT(*) AS cnt FROM users GROUP BY city
) t WHERE t.cnt > 10;

SELECT id, username AS name, 'user' AS src FROM users WHERE city = '杭州'
UNION ALL
SELECT id, name AS name, 'product' AS src FROM products WHERE category = '外设';

SELECT DATE(created_at) AS d,
       SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END)               AS pending_cnt,
       SUM(CASE WHEN status = 1 THEN amount ELSE 0 END)          AS paid_amount,
       SUM(CASE WHEN status = 4 THEN amount ELSE 0 END)          AS refund_amount
FROM orders
GROUP BY d;

SELECT IF(status = 1, '已付', '未付') AS s,          -- 三元
       IFNULL(email, '-')              AS e,          -- a 为 NULL 取 b
       NULLIF(stock, 0)                AS s2,         -- a=b 时返回 NULL，防除零利器
       COALESCE(remark, memo, '无')     AS note       -- 从左到右第一个非 NULL
FROM orders;

SELECT CONCAT('u_', id)                 AS k,   -- 任一参数为 NULL 结果即 NULL（传染）
       CONCAT_WS(',', city, username)   AS tag, -- 分隔符版：自动跳过 NULL，拼 LIKE 模糊也用这风格
       LOCATE('key', name)              AS p,   -- 子串位置（1 起始），参数顺序是 (子串, 串)，和多数语言反着
       SUBSTRING(name, 1, 5), LEFT(name, 5), RIGHT(name, 3),  -- 按字符计，中文安全；LENGTH 才是字节
       TRIM(BOTH ' ' FROM name),        -- 还有 LTRIM/RTRIM；去指定字符 TRIM(BOTH 'x' FROM ...)
       REPLACE(name, '旧', '新');

SELECT NOW(), CURDATE(), SYSDATE();                  -- NOW=语句开始时刻，SYSDATE=调用瞬间（区别在长语句）
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s'); -- %i 才是分钟，%m 是月份——写混了是高频事故
SELECT STR_TO_DATE('2026/10/20 13:05', '%Y/%m/%d %H:%i');
SELECT DATE_ADD(NOW(), INTERVAL 1 MONTH),            -- 还有 DATE_SUB、加 (6,'2026-02-01') 形式的 INTERVAL
       TIMESTAMPDIFF(HOUR, created_at, NOW()),        -- 注意方向：第二个减第一个，返回 end-start
       DATEDIFF(CURDATE(), created_at);               -- 天数差：DATEDIFF(早的放后? 不是——是 expr1 - expr2，以天为单位截断)

SELECT CAST('123' AS UNSIGNED) + 1,          -- 124
       CONVERT(0.565, DECIMAL(10, 2));       -- 0.57：浮点转 DECIMAL 四舍六入五看偶数策略，敏感计算实测
SELECT CONVERT(name USING latin1);           -- 另一种 CONVERT：字符集转换

SELECT * FROM users WHERE city = NULL;      -- 永远 0 行，判空必须 IS NULL / IS NOT NULL
SELECT * FROM users WHERE city <> '杭州';    -- 也会漏掉 city 为 NULL 的人！
SELECT * FROM users WHERE city <> '杭州' OR city IS NULL;  -- 才完整

SELECT * FROM users
WHERE referrer_id NOT IN (SELECT id FROM users WHERE city = '北京');
-- 若子查询结果含 NULL：对任意行，"x NOT IN (..., NULL)" 里对 NULL 的比较是 unknown，
-- AND 链里出现 unknown 整体不为 TRUE → 所有行被过滤，返回空集，还以为是数据问题

SELECT COUNT(*), COUNT(1), COUNT(city), COUNT(DISTINCT city) FROM users;

函数() OVER (
  [PARTITION BY 分组列]      -- 按值切窗口
  [ORDER BY 排序列]          -- 窗口内排序
  [帧：ROWS / RANGE ...]     -- 取数范围，可省
)

-- 每个用户金额 Top3 的订单（每组 TopN 标准模板）
WITH ranked AS (
  SELECT user_id, id AS order_id, order_no, amount,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC, id) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

SELECT user_id, created_at, amount,
       SUM(amount)  OVER (PARTITION BY user_id)                          AS user_total,   -- 不写 ORDER BY：整分区一个值
       SUM(amount)  OVER (PARTITION BY user_id ORDER BY created_at)      AS running_sum,  -- 累计
       AVG(amount)  OVER (PARTITION BY user_id ORDER BY created_at
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)     AS avg_recent3   -- 滚动窗口
FROM orders;

-- 按月销售额，环比与同比
WITH monthly AS (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS ym, SUM(amount) AS sales
  FROM orders
  GROUP BY ym
)
SELECT ym, sales,
       LAG(sales, 1) OVER (ORDER BY ym)  AS prev_month,
       ROUND((sales - LAG(sales, 1)  OVER (ORDER BY ym))
             / NULLIF(LAG(sales, 1)  OVER (ORDER BY ym), 0) * 100, 2) AS mom_pct,
       LAG(sales, 12) OVER (ORDER BY ym) AS yoy_base
FROM monthly
ORDER BY ym;

WITH d AS (   -- 登录日志换成订单去重到天
  SELECT DISTINCT user_id, DATE(created_at) AS login_date FROM orders
),
g AS (
  SELECT user_id, login_date,
         DATE_SUB(login_date,
           INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM d
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end,
       COUNT(*) AS days
FROM g
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

WITH big_users AS (
  SELECT user_id FROM orders WHERE amount > 5000 GROUP BY user_id
),
big_buyer_detail AS (
  SELECT u.id, u.username FROM big_users b JOIN users u ON u.id = b.user_id
)
SELECT * FROM big_buyer_detail;   -- CTE 可互相引用（后者用前者），也可传参引用多次

-- 表 departments(id INT 主键, name VARCHAR(64), parent_id INT NOT NULL DEFAULT 0，根节点 parent_id=0)
WITH RECURSIVE tree AS (
  -- 锚点：根节点
  SELECT id, name, parent_id, 1 AS depth,
         CAST(name AS CHAR(512)) AS path
  FROM departments WHERE parent_id = 0
  UNION ALL
  -- 递归体：用上一轮 tree 找孩子
  SELECT d.id, d.name, d.parent_id, t.depth + 1,
         CONCAT(t.path, ' > ', d.name)
  FROM departments d JOIN tree t ON d.parent_id = t.id
)
SELECT * FROM tree ORDER BY path;
-- depth 输出层级，path 输出"总公司 > 华东 > 上海一组"式面包屑

CREATE USER 'app_rw'@'10.20.%' IDENTIFIED BY 'Fv9#tR2x';     -- host 决定哪些来源可登，% 通配
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_rw'@'10.20.%';
REVOKE DELETE ON shop.* FROM 'app_rw'@'10.20.%';
SHOW GRANTS FOR 'app_rw'@'10.20.%';
DROP USER 'app_ro'@'localhost';

PREPARE stmt FROM 'SELECT id, username FROM users WHERE city = ? AND age > ?';
SET @c = '杭州', @a = 18;
EXECUTE stmt USING @c, @a;
DEALLOCATE PREPARE stmt;

rows, err := db.Query(
    "SELECT id, username FROM users WHERE city = ? AND age > ?",
    city, minAge,
)
// LIKE 模糊匹配把 % 放进参数或用 CONCAT，不要把用户输入拼进语句：
db.Query("SELECT id FROM users WHERE username LIKE CONCAT('%', ?, '%')", keyword)

SELECT stock FROM products WHERE id = 7 FOR UPDATE;        -- 加行级排他锁，扣库存前锁定
SELECT * FROM orders WHERE id = 1001 LOCK IN SHARE MODE;   -- 等价 FOR SHARE，共享读锁
SELECT ... FOR UPDATE SKIP LOCKED;                         -- 8.0：跳过被锁行，适合任务队列领取
SELECT ... FOR UPDATE NOWAIT;                              -- 碰锁立刻报错而不是等