<窗口函数> OVER (
    [PARTITION BY 字段1, 字段2...] -- 【分组】：按什么归类（比如按用户、按班级）
    [ORDER BY 字段3 ASC/DESC]      -- 【排序】：组内按什么顺序算（比如按金额、按成绩）
    [ROWS/RANGE ...]               -- 【范围】：(进阶) 滑动窗口范围，通常用默认值
)

SELECT 
    u.username, 
    SUM(o.total_amount) as money
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.username
ORDER BY money DESC;

select 
    u.username, 
    SUM(o.total_amount) as money,
    RANK() OVER(ORDER BY SUM(o.total_amount) DESC) as ranklist
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.username
ORDER BY money DESC;

select 
    u.username, 
    SUM(o.total_amount) as money,
    RANK() OVER(ORDER BY SUM(o.total_amount) DESC) as rank
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.username
ORDER BY mone

SELECT 
    u.username, 
    SUM(o.total_amount) as money,
    -- 加了反引号 `rank`，告诉 MySQL 这是别名，不是关键字
    RANK() OVER(ORDER BY SUM(o.total_amount) DESC) as `rank`
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.username
ORDER BY money DESC;

SELECT 
    u.city, 
    u.username,  
    IFNULL(SUM(o.total_amount),0) as money,
    RANK() OVER(ORDER BY SUM(o.total_amount) DESC) as ranklist
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
WHERE u.city = "东莞市"
GROUP BY u.city, u.username
LIMIT 3;

SELECT 
    u.city, 
    IFNULL(SUM(o.total_amount),0) as money,
    RANK() OVER(ORDER BY SUM(o.total_amount) DESC) as ranklist
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.city, u.username
LIMIT 3;

SELECT * FROM(
SELECT 
    u.city, 
    u.username,  
    IFNULL(SUM(o.total_amount),0) as money,
    RANK() OVER(
    PARTITION BY u.city
    ORDER BY SUM(o.total_amount) DESC
    ) as ranklist
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.city, u.username
)AS t
WHERE t.ranklist <= 3 AND t.money > 0;

<窗口函数> OVER (
 PARTITION BY ...
 ORDER BY ...
 [ROWS|RANGE] BETWEEN <开始位置> AND <结束位置>
)

SELECT 
    total_amount,
    AVG(total_amount) OVER (
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg
FROM orders;

SELECT 
    order_no,
    total_amount,
    SUM(total_amount) OVER (
        ORDER BY created_at
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as context_sum
FROM orders;

SUM(amount) OVER (
  PARTITION BY user_id
  ORDER BY create_time
  RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
)

SELECT
  id,
  score,
  SUM(score) OVER (
    ORDER BY score
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS sum_rows
FROM score;

SELECT
  id,
  score,
  SUM(score) OVER (
    ORDER BY score
    RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS sum_rows
FROM score;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    details JSON  -- 定义 JSON 类型
);

INSERT INTO products (name, details) VALUES 
('T-Shirt', '{"color": "white", "size": "L", "tags": ["summer", "casual"]}'),
('iPhone 15', '{"color": "black", "storage": "256GB", "screen": "6.1"}');

select name,details->>'$.color' as color from products;

select * from products where details->>'$.storage' is not null;

select * from products where json_contains(details,'"summer"',"$.tags");

update products set details = json_set(details,"$.color","blue","$.NB","true");

update products set details = json_remove(details,"$.NB");

ALTER TABLE users ADD COLUMN v_age INT 
GENERATED ALWAYS AS (profile->>'$.age') VIRTUAL;

CREATE INDEX idx_age ON users(v_age);

SELECT * FROM users WHERE v_age > 20; 
-- 或者
SELECT * FROM users WHERE profile->>'$.age' > 20;

WITH cte_name (列名1, 列名2, ...) AS (
    -- 这里写你的子查询
    SELECT ...
)
-- 紧接着写主查询，可以使用上面的 cte_name
SELECT * FROM cte_name;

SELECT * FROM(
SELECT 
    u.city, 
    u.username,  
    IFNULL(SUM(o.total_amount),0) as money,
    RANK() OVER(
    PARTITION BY u.city
    ORDER BY SUM(o.total_amount) DESC
    ) as ranklist
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id
GROUP BY u.city, u.username
)AS t
WHERE t.ranklist <= 3 AND t.money > 0;

-- 1. 先定义“带排名的用户列表”
WITH UserRanks AS (
    SELECT 
        u.city, 
        u.username,  
        IFNULL(SUM(o.total_amount),0) as money,
        RANK() OVER(PARTITION BY u.city ORDER BY SUM(o.total_amount) DESC) as ranklist
    FROM users AS u
    LEFT JOIN orders AS o ON u.id = o.user_id
    GROUP BY u.city, u.username
)
-- 2. 再对这个列表进行筛选
SELECT * 
FROM UserRanks 
WHERE ranklist <= 3 AND money > 0;

select 
    user_id,
    SUM(total_amount) AS total_spent
FROM orders GROUP BY user_id;

WITH UserSpent AS (
    select 
        user_id,
        SUM(total_amount) AS total_spent
    FROM orders GROUP BY user_id
)

SELECT 
    u.username,
    u.city,
    u.phone,
    us.total_spent
FROM users AS u
INNER JOIN UserSpent AS us ON u.id = us.user_id
WHERE us.total_spent > 5000
ORDER BY us.total_spent DESC;

WITH UserSpent AS (
    select 
        user_id,
        SUM(total_amount) AS total_spent
    FROM orders GROUP BY user_id
)

SELECT 
    u.username,
    u.city,
    u.phone,
    us.total_spent,
    RANK() OVER(ORDER BY us.total_spent DESC) as `rank`
FROM users AS u
INNER JOIN UserSpent AS us ON u.id = us.user_id
WHERE us.total_spent > 5000
ORDER BY us.total_spent DESC;

WITH DigitalProductSales AS (
    -- 1. 先把数码类的所有销售明细算出来
    SELECT 
        p.name AS product_name,
        SUM(oi.quantity * oi.price) AS prod_total
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE p.category = '数码'
    GROUP BY p.name
),
TotalDigitalSales AS (
    -- 2. 复用上面的 CTE！计算数码类的总销售额
    -- 这里直接查询了定义好的 DigitalProductSales，不用再去 join 原始表了
    SELECT SUM(prod_total) AS cat_total 
    FROM DigitalProductSales
)
-- 3. 主查询：计算占比
SELECT 
    dps.product_name,
    dps.prod_total,
    tds.cat_total,
    CONCAT(ROUND((dps.prod_total / tds.cat_total) * 100, 2), '%') AS percent
FROM DigitalProductSales dps
CROSS JOIN TotalDigitalSales tds
ORDER BY dps.prod_total DESC;

SELECT 
    p.name AS product_name,
    SUM(oi.quantity * oi.price) AS prod_total,
    SUM(SUM(oi.quantity * oi.price)) OVER() AS cat_total,
    CONCAT(ROUND(SUM(oi.quantity * oi.price) / SUM(SUM(oi.quantity * oi.price)) OVER() * 100, 2), '%') AS percent
FROM order_items oi
JOIN products p ON oi.product_id = p.id
WHERE p.category = '数码'
GROUP BY p.name
ORDER BY prod_total DESC;

SELECT 
    DATE_FORMAT(created_at, '%Y-%m') AS sale_month,
    SUM(total_amount) AS total_money
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

WITH MonthlyStats AS (
    SELECT 
        DATE_FORMAT(created_at, '%Y-%m') AS sale_month,
        SUM(total_amount) AS total_money
    FROM orders
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
),
GrowthStats AS (
    SELECT 
        sale_month,
        total_money,
        LAG(total_money, 1) OVER (ORDER BY sale_month) AS last_month_money
    FROM MonthlyStats
)

WITH MonthlyStats AS (
    SELECT 
        DATE_FORMAT(created_at, '%Y-%m') AS sale_month,
        SUM(total_amount) AS total_money
    FROM orders
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
),
GrowthStats AS (
    SELECT 
        sale_month,
        total_money,
        LAG(total_money, 1) OVER (ORDER BY sale_month) AS last_month_money
    FROM MonthlyStats
)
SELECT 
    sale_month,
    total_money,
    last_month_money,
    CONCAT(ROUND((total_money - last_month_money) / last_month_money * 100, 2), '%') AS growth_rate
FROM GrowthStats
ORDER BY sale_month;

SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS sale_month,
    SUM(total_amount) AS total_money,
    LAG(SUM(total_amount), 1) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m')) AS last_month_money,
    CONCAT(ROUND((SUM(total_amount)  - LAG(SUM(total_amount), 1) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m')) ) / LAG(SUM(total_amount), 1) OVER (ORDER BY DATE_FORMAT(created_at, '%Y-%m'))  * 100, 2), '%') AS growth_rate
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

WITH RECURSIVE NumSeries AS (
    SELECT 1 AS n

    UNION ALL

    SELECT n + 1 FROM NumSeries WHERE n < 5
)
SELECT * FROM NumSeries;

SELECT n + 1 FROM NumSeries WHERE n < 5

WITH RECURSIVE cte_name AS (
    -- 1. 初始查询（种子）：你想从谁开始找？
    SELECT ...

    UNION ALL

    -- 2. 递归查询（套娃）：逻辑通常是 JOIN cte_name
    SELECT ...
    FROM table
    JOIN cte_name ON table.parent_id = cte_name.id -- 或者是 cte.id = table.parent_id
)
SELECT * FROM cte_name;

WITH RECURSIVE Subordinates AS (
    -- 启动：先找到那个“根”节点（技术总监）
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE id = 2  -- 从 ID=2 开始往下找

    UNION ALL

    -- 循环：找上一轮找到的人的下属,s 是上一轮找到的结果集
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN Subordinates s ON e.manager_id = s.id 
    -- 员工的 manager_id 等于 上一轮找到的人的 id
)
SELECT * FROM Subordinates;

CREATE TRIGGER 触发器名
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON 表名
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

DELIMITER $$

CREATE TRIGGER koukucun
BEFORE INSERT
ON order_items
FOR EACH ROW
BEGIN
	UPDATE products SET products.stock = products.stock - NEW.quantity 
	WHERE NEW.product_id = products.id;
END $$

DELIMITER ;

DELIMITER $$

CREATE TRIGGER koukucun
BEFORE INSERT 
ON order_items
FOR EACH ROW
BEGIN
    -- 1. 定义一个变量存当前库存
    DECLARE current_stock INT;
    
    -- 2. 查出当前商品的库存
    SELECT stock INTO current_stock 
    FROM products 
    WHERE id = NEW.product_id; -- 明确指定 id 是产品的 id
    
    -- 3. 判断库存够不够
    IF current_stock < NEW.quantity THEN
        -- 4. 如果不够，抛出异常！这会中断 INSERT 操作，订单创建失败
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '库存不足，无法下单';
    ELSE
        -- 5. 如果够，执行扣减
        UPDATE products 
        SET stock = stock - NEW.quantity 
        WHERE id = NEW.product_id;
    END IF;

END $$

DELIMITER ;

insert into order_items (product_id,quantity) values (1,500);

MySQL  localhost:3306 ssl  mall_db_simple  SQL > select * from products limit 1;
+----+-----------+----------+--------+-------+--------+
| id | name      | category | price  | stock | status |
+----+-----------+----------+--------+-------+--------+
|  1 | 零食-世界 | 零食     | 535.00 |   499 |      1 |
+----+-----------+----------+--------+-------+--------+
1 row in set (0.0012 sec)

MySQL  localhost:3306 ssl  mall_db_simple  SQL > insert into order_items (product_id,quantity) values (1,900);
ERROR: 1644 (45000): 库存不足，无法下单
MySQL  localhost:3306 ssl  mall_db_simple  SQL > select * from products limit 1;
+----+-----------+----------+--------+-------+--------+
| id | name      | category | price  | stock | status |
+----+-----------+----------+--------+-------+--------+
|  1 | 零食-世界 | 零食     | 535.00 |   499 |      1 |
+----+-----------+----------+--------+-------+--------+
1 row in set (0.0004 sec)

MySQL  localhost:3306 ssl  mall_db_simple  SQL > show TRIGGERS;
+----------+--------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger  | Event  | Table       | Statement                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | Timing | Created                | sql_mode                                                                                                              | Definer        | character_set_client | collation_connection | Database Collation |
+----------+--------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| koukucun | INSERT | order_items | BEGIN
    -- 1. 定义一个变量存当前库存
    DECLARE current_stock INT;

    -- 2. 查出当前商品的库存
    SELECT stock INTO current_stock
    FROM products
    WHERE products.id = NEW.product_id;

    -- 3. 判断库存够不够
    IF current_stock < NEW.quantity THEN
        -- 4. 如果不够，抛出异常！这会中断 INSERT 操作，订单创建失败
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '库存不足，无法下单';
    ELSE
        -- 5. 如果够，执行扣减
        UPDATE products
        SET stock = stock - NEW.quantity
        WHERE id = NEW.product_id;
    END IF;

END | BEFORE | 2026-01-06 18:07:03.53 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+----------+--------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-----------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.0015 sec)

DELIMITER $$

CREATE PROCEDURE recharge_and_bonus(IN p_user_id INT, IN p_amount DECIMAL(10,2))
BEGIN
    -- 1. 充钱
    UPDATE users SET balance = balance + p_amount WHERE id = p_user_id;
    
    -- 2. 送钱
    IF p_amount >= 100 THEN
        UPDATE users SET balance = balance + 5 WHERE id = p_user_id; -- 再送5元！
    END IF; 
    
    -- 3. 返回最新余额
    SELECT balance, points FROM users WHERE id = p_user_id;
END $$

DELIMITER ;

CALL recharge_and_bonus(1, 200);