编辑代码

CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Barcode VARCHAR(50) UNIQUE,
    Category VARCHAR(50),
    SupplierID INT,
    PurchasePrice DECIMAL(10,2) NOT NULL,
    SellingPrice DECIMAL(10,2) NOT NULL,
    StockQuantity INT DEFAULT 0,
    ReorderLevel INT DEFAULT 0,
    Description TEXT,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID)
);
CREATE TABLE Suppliers (
    SupplierID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    ContactPerson VARCHAR(50),
    Phone VARCHAR(20) NOT NULL,
    Email VARCHAR(100),
    Address TEXT,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(50),
    Phone VARCHAR(20) UNIQUE,
    Email VARCHAR(100),
    Points INT DEFAULT 0,
    MembershipLevel ENUM('普通会员', '银卡会员', '金卡会员', '钻石会员') DEFAULT '普通会员',
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE SalesOrders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT,
    EmployeeID INT,
    OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    TotalAmount DECIMAL(10,2) NOT NULL,
    Discount DECIMAL(5,2) DEFAULT 0,
    FinalAmount DECIMAL(10,2) NOT NULL,
    PaymentMethod ENUM('现金', '银行卡', '微信', '支付宝', '其他') DEFAULT '现金',
    Status ENUM('待支付', '已支付', '已取消') DEFAULT '已支付',
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
CREATE TABLE OrderItems (
    ItemID INT PRIMARY KEY AUTO_INCREMENT,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    Subtotal DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES SalesOrders(OrderID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
CREATE TABLE InventoryRecords (
    RecordID INT PRIMARY KEY AUTO_INCREMENT,
    ProductID INT NOT NULL,
    QuantityChange INT NOT NULL,
    CurrentQuantity INT NOT NULL,
    OperationType ENUM('入库', '出库', '调整') NOT NULL,
    Operator VARCHAR(50) NOT NULL,
    OperationTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    Remark TEXT,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
-- 创建销售订单
INSERT INTO SalesOrders (CustomerID, EmployeeID, TotalAmount, Discount, FinalAmount, PaymentMethod)
VALUES (1, 2, 100.00, 5.00, 95.00, '微信');

-- 添加销售明细
INSERT INTO OrderItems (OrderID, ProductID, Quantity, UnitPrice, Subtotal)
VALUES (1, 1, 2, 3.50, 7.00), (1, 2, 1, 7.50, 7.50);

-- 查询销售订单
SELECT * FROM SalesOrders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';

-- 更新订单状态
UPDATE SalesOrders SET Status = '已取消' WHERE OrderID = 1;
-- 记录入库操作
INSERT INTO InventoryRecords (ProductID, QuantityChange, CurrentQuantity, OperationType, Operator, Remark)
VALUES (1, 50, 150, '入库', '李四', '采购入库');

-- 查询库存记录
SELECT * FROM InventoryRecords WHERE ProductID = 1 ORDER BY OperationTime DESC;

-- 查询低库存货品
SELECT * FROM Products WHERE StockQuantity <= ReorderLevel;
-- 添加供应商
INSERT INTO Suppliers (Name, ContactPerson, Phone, Email, Address)
VALUES ('光明食品有限公司', '张三', '13900139001', 'zhangsan@example.com', '上海市浦东新区');

-- 添加客户
INSERT INTO Customers (Name, Phone, Points, MembershipLevel)
VALUES ('李四', '13800138001', 200, '银卡会员');