编辑代码

-- .创建orders表,字段包含:订单ID(主键)、用户ID、商品ID、数量、订单日期(DATE类型)
-- .编写SQL语句实现:
-- 1.插入5条测试数据(需包含不同用户和商品)
-- 2.‌事务操作‌:将用户ID为1001的订单数量全部增加1,若任意一条更新失败则回滚 
-- 3.查询2024年1月至今下单次数超过3次的用户ID
CREATE DATABASE IF NOT EXISTS order_management;
USE order_management;


CREATE TABLE IF NOT EXISTS orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    order_date DATE NOT NULL
)ENGINE=InnoDB;


INSERT INTO orders(user_id, product_id, quantity, order_date) VALUES
(1001, 101, 1, '2025-01-05'),
(1001, 101, 1, '2025-01-05'),
(1001, 101, 1, '2025-01-05'),
(1001, 102, 1, '2025-01-05'),
(1001, 102, 1, '2025-01-05'),
(1001, 102, 1, '2025-01-05'),
(1001, 103, 1, '2025-01-05'),
(1001, 101, 1, '2025-01-05');

SET autocommit = 0;
START TRANSACTION;

UPDATE orders
SET quantity = quantity + 1
WHERE user_id = 1001;

SELECT COUNT(*) INTO @invalid_orders
FROM orders
WHERE user_id = 1001 AND quantity <= 0;

SELECT IF(@invalid_orders = 0, 'COMMIT', 'ROLLBACK') INTO @action;

PREPARE stmt FROM @action;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT IF(@invalid_orders = 0,
    '事务提交成功:1001订单数量已增加',
    '失败,一会滚'
) AS RESULT;

SET autocommit = 1;

SELECT user_id
FROM orders
WHERE quantity >= 3 AND order_date > '2024-01'

SELECT * FROM orders;