CREATE DATABASE IF NOT EXISTS 商品管理系统;
USE 商品管理系统;
CREATE TABLE IF NOT EXISTS 供应商 (
供应商编号 INT AUTO_INCREMENT PRIMARY KEY,
供应商名称 VARCHAR(100) NOT NULL,
联系人 VARCHAR(50),
联系电话 VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS 商品 (
商品编号 INT AUTO_INCREMENT PRIMARY KEY,
商品名称 VARCHAR(100) NOT NULL,
商品编码 VARCHAR(50) UNIQUE,
供应商编号 INT,
进货价格 DECIMAL(10, 2),
销售价格 DECIMAL(10, 2) NOT NULL,
库存数量 INT DEFAULT 0,
最低库存量 INT DEFAULT 10,
状态 ENUM('在售', '停售') DEFAULT '在售',
FOREIGN KEY (供应商编号) REFERENCES 供应商(供应商编号) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS 库存记录 (
记录编号 INT AUTO_INCREMENT PRIMARY KEY,
商品编号 INT NOT NULL,
记录类型 ENUM('采购入库', '销售出库') NOT NULL,
变动数量 INT NOT NULL,
FOREIGN KEY (商品编号) REFERENCES 商品(商品编号) ON DELETE CASCADE
);
INSERT INTO 供应商 (供应商名称, 联系人, 联系电话)
VALUES
('apple', '孙经理', '13800138000'),
('华为旗舰店', '钟经理', '13900139000'),
('小米之家','林经理','14000140000');
INSERT INTO 商品 (商品名称, 商品编码, 供应商编号, 进货价格, 销售价格, 库存数量)
VALUES
('iPhone 80 PRO MAX', 'sjx', 1, 50000.00, 59999.00, 50),
('华为P10000', 'zmt', 2, 32000.00, 39999.00, 20),
('小米1200 Pro', 'lyq', 3, 35000.00, 42999.00, 100);
INSERT INTO 库存记录 (商品编号, 记录类型, 变动数量)
VALUES
(1, '采购入库', 50),
(2, '采购入库', 20),
(3, '采购入库', 100);
SELECT *FROM 商品
LEFT JOIN 供应商 ON 商品.供应商编号 = 供应商.供应商编号;
SELECT 商品名称, 库存数量, 最低库存量
FROM 商品
WHERE 库存数量 < 最低库存量;
SELECT 商品.商品名称,
SUM(CASE WHEN 库存记录.记录类型 = '销售出库' THEN 库存记录.变动数量 ELSE 0 END) AS 销售数量
FROM 商品
JOIN 库存记录 ON 商品.商品编号 = 库存记录.商品编号
GROUP BY 商品.商品名称;