编辑代码

CREATE DATABASE ClothingStore;
USE ClothingStore;
CREATE TABLE Cloth (
    ItemID VARCHAR(20) PRIMARY KEY,
    ShelfID VARCHAR(20),
    SellerID VARCHAR(20),
    MaterialColor VARCHAR(50) NOT NULL,
    Size VARCHAR('L','M','S') NOT NULL,
    StockQuantity INT NOT NULL DEFAULT 0 CHECK (StockQuantity >= 0),
    PurchaseDate DATE NOT NULL,
    LogisticsStatus VARCHAR(20) NOT NULL DEFAULT '在库',
    Price DECIMAL(10, 2) NOT NULL CHECK (Price > 0),
    FOREIGN KEY (ShelfID) REFERENCES Shelf(ShelfID) ON DELETE SET NULL,
    FOREIGN KEY (SellerID) REFERENCES Seller(SellerID) ON DELETE SET NULL
);

CREATE TABLE Shelf (
    ShelfID VARCHAR(20) PRIMARY KEY,
    Location VARCHAR(50) COMMENT '货架位置'
);

CREATE TABLE Staff (
    StaffID VARCHAR(20) PRIMARY KEY,
    StaffName VARCHAR(50) NOT NULL,
    Gender ENUM('男', '女') NOT NULL,
    Position VARCHAR(20) NOT NULL,
    HireDate DATE NOT NULL,
    ShelfID VARCHAR(20),
    FOREIGN KEY (ShelfID) REFERENCES Shelf(ShelfID) ON DELETE SET NULL
);

CREATE TABLE Seller (
    SellerID VARCHAR(20) PRIMARY KEY,
    SellerName VARCHAR(50) NOT NULL,
    ContactPhone VARCHAR(20) UNIQUE,
    Address VARCHAR(100),
    CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sell (
    item_id VARCHAR(50),
    buyer_phone VARCHAR(20),
    quantity INT NOT NULL,
    PRIMARY KEY (item_id, buyer_phone),
    FOREIGN KEY (item_id) REFERENCES cloth(item_id),
    FOREIGN KEY (buyer_phone) REFERENCES buyer(buyer_phone)
);

CREATE TABLE Buyer (
    BuyerID VARCHAR(20) PRIMARY KEY,
    BuyerName VARCHAR(50) NOT NULL,
    ContactPhone VARCHAR(20) UNIQUE,
    ShippingAddress VARCHAR(100),
    CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO Cloth (ItemID, ShelfID, SellerID, MaterialColor, Size, StockQuantity, PurchaseDate, Price)VALUES ('C001', '01E', 'S001', '红色纯棉', 'M', 100, '2023-05-01', 99.99);
INSERT INTO Shelf (ShelfID, Location) VALUES ('01E', '一楼东区');
INSERT INTO Staff (StaffID, StaffName, Gender, Position, HireDate, ShelfID) VALUES ('S001', '李四', '男', '仓库管理员', '2023-01-01', '01E'); 
INSERT INTO Seller (SellerID, SellerName, ContactPhone, Address) VALUES ('0001', '广州服装厂', '18976480001', '广州市天河区天河小区A栋');
INSERT INTO SellRecord (ItemID, BuyerID, Quantity, TotalAmount) VALUES ('C001', 'B001', 5, 499.95);
INSERT INTO Buyer (BuyerID, BuyerName, ContactPhone, ShippingAddress) VALUES ('B001', '张三', '18976480002', '北京市朝阳区朝阳小区B栋303');
-- 数据查询示例
-- 查询所有服装及其货架信息
SELECT c.ItemID, c.MaterialColor, c.Size, c.StockQuantity, s.Location 
FROM Cloth c 
LEFT JOIN Shelf s ON c.ShelfID = s.ShelfID;

-- 查询某货架上的所有服装
SELECT * 
FROM Cloth WHERE ShelfID = 'A01';

-- 查询销售记录并计算总销售额
SELECT sr.RecordID, c.ItemID, c.MaterialColor, b.BuyerName, sr.Quantity, sr.TotalAmount
FROM SellRecord sr
JOIN Cloth c ON sr.ItemID = c.ItemID
JOIN Buyer b ON sr.BuyerID = b.BuyerID;

-- 查询某个时间段的销售统计
SELECT 
    DATE_FORMAT(sr.SellDate, '%Y-%m-%d') AS SellDate,
    SUM(sr.Quantity) AS TotalItemsSold,
    SUM(sr.TotalAmount) AS TotalRevenue
FROM SellRecord sr
WHERE sr.SellDate BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY DATE_FORMAT(sr.SellDate, '%Y-%m-%d')
ORDER BY SellDate DESC;

-- 修改表结构示例：添加员工联系方式字段
ALTER TABLE Staff ADD COLUMN ContactPhone VARCHAR(20) AFTER Position;

-- 删除表示例（谨慎操作）
-- DROP TABLE IF EXISTS SellRecord;