编辑代码

-- 创建数据库
CREATE DATABASE ClothingStore;
USE ClothingStore;

-- 1. 创建货架表
CREATE TABLE Shelf (
    ShelfID VARCHAR(20) PRIMARY KEY,
    Location VARCHAR(50) COMMENT '货架位置'
);

-- 2. 创建卖家表
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
);

-- 3. 创建买家表
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
);

-- 4. 创建员工表
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
);

-- 5. 创建服装表
CREATE TABLE Cloth (
    ItemID VARCHAR(20) PRIMARY KEY,
    ShelfID VARCHAR(20),
    SellerID VARCHAR(20),
    MaterialColor VARCHAR(50) NOT NULL,
    Size VARCHAR(10) 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
);

-- 6. 创建出售记录表
CREATE TABLE SellRecord (
    RecordID INT AUTO_INCREMENT PRIMARY KEY,
    ItemID VARCHAR(20) NOT NULL,
    BuyerID VARCHAR(20) NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    SellDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    TotalAmount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (ItemID) REFERENCES Cloth(ItemID) ON DELETE CASCADE,
    FOREIGN KEY (BuyerID) REFERENCES Buyer(BuyerID) ON DELETE CASCADE,
    UNIQUE (ItemID, BuyerID, SellDate)
);

-- 7. 创建进货记录表
CREATE TABLE PurchaseRecord (
    RecordID INT AUTO_INCREMENT PRIMARY KEY,
    ItemID VARCHAR(20) NOT NULL,
    SellerID VARCHAR(20) NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    PurchaseDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    UnitPrice DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (ItemID) REFERENCES Cloth(ItemID) ON DELETE CASCADE,
    FOREIGN KEY (SellerID) REFERENCES Seller(SellerID) ON DELETE CASCADE
);


-- 示例数据插入
INSERT INTO Shelf (ShelfID, Location) VALUES ('A01', '一楼东区');
INSERT INTO Seller (SellerID, SellerName, ContactPhone, Address) VALUES ('S001', '广州服装厂', '18976403509', '广州市天河区');
INSERT INTO Buyer (BuyerID, BuyerName, ContactPhone, ShippingAddress) VALUES ('B001', '张三', '18976483506', '海口市美兰区');
INSERT INTO Buyer (BuyerID, BuyerName, ContactPhone, ShippingAddress) VALUES ('B002', '张四', '18976483508', '西安市灞桥区');
INSERT INTO Staff (StaffID, StaffName, Gender, Position, HireDate, ShelfID) VALUES ('E001', '李四', '男', '库管', '2023-01-01', 'A01');
INSERT INTO Cloth (ItemID, ShelfID, SellerID, MaterialColor, Size, StockQuantity, PurchaseDate, Price) 
VALUES ('C001', 'A01', 'S001', '红色纯棉', 'M', 100, '2023-05-01', 99.99);
INSERT INTO SellRecord (ItemID, BuyerID, Quantity, TotalAmount) VALUES ('C001', 'B001', 5, 499.95);

-- 数据查询示例
-- 查询所有员工信息
SELECT *
FROM Staff ;

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

SELECT *
FROM Staff;

UPDATE Staff
SET ContactPhone='18976484199'
WHERE StaffID='E001';

SELECT *
FROM Staff;

SELECT *
FROM Buyer;

DELETE FROM Buyer
WHERE  ContactPhone='18976483508';

SELECT *
FROM Buyer;

SELECT *
FROM SellRecord;

DROP TABLE IF EXISTS SellRecord; 

SELECT *
FROM SellRecord;