如何使用mysql构建简单的电商购物车系统

CREATE TABLE users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
<p>CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT NOT NULL DEFAULT 0,
description TEXT
);</p><p>CREATE TABLE cart_items (
cart_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (product_id) REFERENCES products(product_id),
UNIQUE KEY unique_user_product (user_id, product_id)
);</p>

INSERT INTO cart_items (user_id, product_id, quantity)
VALUES (1, 101, 2)
ON DUPLICATE KEY UPDATE quantity = quantity + VALUES(quantity);

SELECT 
  p.name,
  p.price,
  c.quantity,
  (p.price * c.quantity) AS subtotal
FROM cart_items c
JOIN products p ON c.product_id = p.product_id
WHERE c.user_id = 1;

UPDATE cart_items
SET quantity = 3
WHERE user_id = 1 AND product_id = 101;

DELETE FROM cart_items
WHERE user_id = 1 AND product_id = 101;

DELETE FROM cart_items WHERE user_id = 1;

-- 先查询库存
SELECT stock FROM products WHERE product_id = 101;
<p>-- 应用层判断库存足够后再执行添加</p>