阿里妹导读
一、背景
二、工作原理
三、构造数据模型和测试数据
利用ChatGpt给出进销存系统的数据模型和测试数据，模拟某贸易公司的日常运营数据。
CREATE TABLE Product (ProductID INT PRIMARY KEY,ProductName VARCHAR(255),ProductDescription TEXT,CostPrice DECIMAL(10,2),SalePrice DECIMAL(10,2));CREATE TABLE Supplier (SupplierID INT PRIMARY KEY,SupplierName VARCHAR(255),ContactPerson VARCHAR(255),ContactPhone VARCHAR(20),Address TEXT);CREATE TABLE Customer (CustomerID INT PRIMARY KEY,CustomerName VARCHAR(255),ContactPerson VARCHAR(255),ContactPhone VARCHAR(20),Address TEXT);CREATE TABLE Inventory (InventoryID INT PRIMARY KEY,ProductID INT,Quantity INT,FOREIGN KEY (ProductID) REFERENCES Product(ProductID));CREATE TABLE PurchaseOrder (PurchaseOrderID INT PRIMARY KEY,SupplierID INT,OrderDate DATE,FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID));CREATE TABLE SalesOrder (SalesOrderID INT PRIMARY KEY,CustomerID INT,OrderDate DATE,FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID));CREATE TABLE PurchaseOrderDetail (PurchaseOrderDetailID INT PRIMARY KEY,PurchaseOrderID INT,ProductID INT,Quantity INT,Price DECIMAL(10,2),FOREIGN KEY (PurchaseOrderID) REFERENCES PurchaseOrder(PurchaseOrderID),FOREIGN KEY (ProductID) REFERENCES Product(ProductID));CREATE TABLE SalesOrderDetail (SalesOrderDetailID INT PRIMARY KEY,SalesOrderID INT,ProductID INT,Quantity INT,Price DECIMAL(10,2),FOREIGN KEY (SalesOrderID) REFERENCES SalesOrder(SalesOrderID),FOREIGN KEY (ProductID) REFERENCES Product(ProductID));
-- Insert into ProductINSERT INTO Product (ProductID, ProductName, ProductDescription, CostPrice, SalePrice) VALUES(1, 'Product1', 'Description1', 10.00, 20.00),(2, 'Product2', 'Description2', 15.00, 25.00),(3, 'Product3', 'Description3', 20.00, 30.00),(4, 'Product4', 'Description4', 25.00, 35.00),(5, 'Product5', 'Description5', 30.00, 40.00),(6, 'Product6', 'Description6', 35.00, 45.00),(7, 'Product7', 'Description7', 40.00, 50.00),(8, 'Product8', 'Description8', 45.00, 55.00),(9, 'Product9', 'Description9', 50.00, 60.00),(10, 'Product10', 'Description10', 55.00, 65.00);-- Insert into SupplierINSERT INTO Supplier (SupplierID, SupplierName, ContactPerson, ContactPhone, Address) VALUES(1, 'Supplier1', 'Contact1', '1234567890', 'Address1'),(2, 'Supplier2', 'Contact2', '1234567890', 'Address2'),(3, 'Supplier3', 'Contact3', '1234567890', 'Address3'),(4, 'Supplier4', 'Contact4', '1234567890', 'Address4'),(5, 'Supplier5', 'Contact5', '1234567890', 'Address5'),(6, 'Supplier6', 'Contact6', '1234567890', 'Address6'),(7, 'Supplier7', 'Contact7', '1234567890', 'Address7'),(8, 'Supplier8', 'Contact8', '1234567890', 'Address8'),(9, 'Supplier9', 'Contact9', '1234567890', 'Address9'),(10, 'Supplier10', 'Contact10', '1234567890', 'Address10');-- Insert into CustomerINSERT INTO Customer (CustomerID, CustomerName, ContactPerson, ContactPhone, Address) VALUES(1, 'Customer1', 'Contact1', '1234567890', 'Address1'),(2, 'Customer2', 'Contact2', '1234567890', 'Address2'),(3, 'Customer3', 'Contact3', '1234567890', 'Address3'),(4, 'Customer4', 'Contact4', '1234567890', 'Address4'),(5, 'Customer5', 'Contact5', '1234567890', 'Address5'),(6, 'Customer6', 'Contact6', '1234567890', 'Address6'),(7, 'Customer7', 'Contact7', '1234567890', 'Address7'),(8, 'Customer8', 'Contact8', '1234567890', 'Address8'),(9, 'Customer9', 'Contact9', '1234567890', 'Address9'),(10, 'Customer10', 'Contact10', '1234567890', 'Address10');-- Insert into InventoryINSERT INTO Inventory (InventoryID, ProductID, Quantity) VALUES(1, 1, 100),(2, 2, 200),(3, 3, 300),(4, 4, 400),(5, 5, 500),(6, 6, 600),(7, 7, 700),(8, 8, 800),(9, 9, 900),(10, 10, 1000);-- Insert into PurchaseOrderINSERT INTO PurchaseOrder (PurchaseOrderID, SupplierID, OrderDate) VALUES(1, 1, '2023-01-01'),(2, 2, '2023-02-01'),(3, 3, '2023-03-01'),(4, 4, '2023-04-01'),(5, 5, '2023-05-01'),(6, 6, '2023-06-01'),(7, 7, '2023-07-01'),(8, 8, '2023-08-01'),(9, 9, '2023-09-01'),(10, 10, '2023-10-01');-- Insert into SalesOrderINSERT INTO SalesOrder (SalesOrderID, CustomerID, OrderDate) VALUES(1, 1, '2023-01-01'),(2, 2, '2023-02-01'),(3, 3, '2023-03-01'),(4, 4, '2023-04-01'),(5, 5, '2023-05-01'),(6, 6, '2023-06-01'),(7, 7, '2023-07-01'),(8, 8, '2023-08-01'),(9, 9, '2023-09-01'),(10, 10, '2023-10-01');-- Insert into PurchaseOrderDetailINSERT INTO PurchaseOrderDetail (PurchaseOrderDetailID, PurchaseOrderID, ProductID, Quantity, Price) VALUES(1, 1, 1, 10, 10.00),(2, 2, 2, 20, 15.00),(3, 3, 3, 30, 20.00),(4, 4, 4, 40, 25.00),(5, 5, 5, 50, 30.00),(6, 6, 6, 60, 35.00),(7, 7, 7, 70, 40.00),(8, 8, 8, 80, 45.00),(9, 9, 9, 90, 50.00),(10, 10, 10, 100, 55.00);-- Insert into SalesOrderDetailINSERT INTO SalesOrderDetail (SalesOrderDetailID, SalesOrderID, ProductID, Quantity, Price) VALUES(1, 1, 1, 10, 20.00),(2, 2, 2, 20, 25.00),(3, 3, 3, 30, 30.00),(4, 4, 4, 40, 35.00),(5, 5, 5, 50, 40.00),(6, 6, 6, 60, 45.00),(7, 7, 7, 70, 50.00),(8, 8, 8, 80, 55.00),(9, 9, 9, 90, 60.00),(10, 10, 10, 100, 65.00);
四、测试环境搭建
五、实验测试过程
场景1 :
生成sql：
SELECT COUNT(DISTINCT SupplierID) AS 'Number of Suppliers', COUNT(DISTINCT ProductID) AS 'Number of Products' FROM Supplier, Product输出：Final Answer: There are 10 suppliers and 10 products.
结论：答案正确✅
引擎分析的过程如下：
场景2 :
SELECT ProductID, Quantity FROM Inventory
输出：Final Answer: The quantity of each product in the Inventory table is: ProductID 1 has 100, ProductID 2 has 200, ProductID 3 has 300, ProductID 4 has 400, ProductID 5 has 500, ProductID 6 has 600, ProductID 7 has 700, ProductID 8 has 800, ProductID 9 has 900, ProductID 10 has 1000.
场景3 :
输入：按月给出所有商品的销量和库存?
SELECT MONTH(sod.SalesOrderID) AS Month, p.ProductName, SUM(sod.Quantity) AS Sales, i.Quantity AS Inventory FROM SalesOrderDetail sod INNERJOIN Product p ON sod.ProductID = p.ProductID INNER JOIN Inventory i ON p.ProductID = i.ProductID GROUP BY Month, p.ProductName, i.InventoryID ORDER BY Month ASC LIMIT 10 输出：Final Answer: The sales and inventory for each product by month are: Product1 (10 sales, 100 inventory), Product2 (20 sales, 200 inventory), Product3 (30 sales, 300 inventory), Product4 (40 sales, 400 inventory), Product5 (50 sales, 500 inventory), Product6 (60 sales, 600 inventory), Product7 (70 sales, 700 inventory), Product8 (80 sales, 800 inventory), Product9 (90 sales, 900 inventory), Product10 (100 sales, 1000 inventory).
引擎分析的过程如下：
场景4 :
输入：给出每个月的销量和销售额?
"SELECT MONTH(OrderDate) AS Month, SUM(Quantity) AS SalesQuantity, SUM(Quantity * Price) AS SalesAmount FROM SalesOrder JOIN SalesOrderDetail ON SalesOrder.SalesOrderID = SalesOrderDetail.SalesOrderID GROUP BY MONTH(OrderDate) ORDER BY Month ASC LIMIT 10"结论：答案正确✅
引擎分析的过程如下：
场景5 :
SELECT MONTH(OrderDate) AS Month, SUM(Quantity) AS Sales, SUM(Quantity * Price) AS Revenue, SUM(Quantity * Price) - SUM(Quantity * CostPrice) AS Profit FROM SalesOrder JOIN SalesOrderDetail ON SalesOrder.SalesOrderID = SalesOrderDetail.SalesOrderID JOIN ProductON SalesOrderDetail.ProductID = Product.ProductID GROUP BY MONTH(OrderDate) ORDER BY Month ASC LIMIT 10
引擎分析过程：
六、总结
阿里云开发者社区，千万开发者的选择
阿里云开发者社区，百万精品技术内容、千节免费系统课程、丰富的体验场景、活跃的社群活动、行业专家分享交流，欢迎点击【阅读原文】加入我们。