用sql serve写出一个商店外卖系统并给出代码,主要内容有商品信息、商品分类、顾客信息、地址信息、订单信息,购物车信息管理,主要业务:下单、退货,销售情况共计查询
时间: 2024-01-12 16:04:47 浏览: 20
以下是一个基本的商店外卖系统的数据库设计和代码:
数据库设计:
1. 商品信息表
CREATE TABLE ProductInfo (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(50),
ProductPrice FLOAT,
ProductDescription VARCHAR(200),
CategoryID INT
);
2. 商品分类表
CREATE TABLE CategoryInfo (
CategoryID INT PRIMARY KEY,
CategoryName VARCHAR(50)
);
3. 顾客信息表
CREATE TABLE CustomerInfo (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
CustomerPhone VARCHAR(20),
CustomerEmail VARCHAR(50)
);
4. 地址信息表
CREATE TABLE AddressInfo (
AddressID INT PRIMARY KEY,
CustomerID INT,
AddressDetail VARCHAR(200),
FOREIGN KEY (CustomerID) REFERENCES CustomerInfo(CustomerID)
);
5. 订单信息表
CREATE TABLE OrderInfo (
OrderID INT PRIMARY KEY,
CustomerID INT,
AddressID INT,
OrderTime DATETIME,
TotalPrice FLOAT,
OrderStatus VARCHAR(20),
FOREIGN KEY (CustomerID) REFERENCES CustomerInfo(CustomerID),
FOREIGN KEY (AddressID) REFERENCES AddressInfo(AddressID)
);
6. 购物车信息表
CREATE TABLE CartInfo (
CartID INT PRIMARY KEY,
CustomerID INT,
ProductID INT,
Quantity INT,
FOREIGN KEY (CustomerID) REFERENCES CustomerInfo(CustomerID),
FOREIGN KEY (ProductID) REFERENCES ProductInfo(ProductID)
);
7. 销售情况查询视图
CREATE VIEW SalesInfo AS
SELECT
ProductInfo.ProductName,
SUM(OrderProduct.Quantity) AS TotalQuantity,
SUM(OrderProduct.Quantity * ProductInfo.ProductPrice) AS TotalSales
FROM
ProductInfo
INNER JOIN
OrderProduct ON ProductInfo.ProductID = OrderProduct.ProductID
GROUP BY
ProductInfo.ProductName;
代码实现:
1. 添加商品信息
INSERT INTO ProductInfo VALUES (1, '商品1', 10.0, '商品1的描述', 1);
2. 添加商品分类
INSERT INTO CategoryInfo VALUES (1, '分类1');
3. 添加顾客信息
INSERT INTO CustomerInfo VALUES (1, '顾客1', '12345678900', 'customer1@example.com');
4. 添加地址信息
INSERT INTO AddressInfo VALUES (1, 1, '地址1');
5. 添加订单信息
INSERT INTO OrderInfo VALUES (1, 1, 1, '2022-01-01 12:00:00', 30.0, '已完成');
6. 添加购物车信息
INSERT INTO CartInfo VALUES (1, 1, 1, 2);
7. 查询销售情况
SELECT * FROM SalesInfo;
8. 下单
INSERT INTO OrderInfo (CustomerID, AddressID, OrderTime, TotalPrice, OrderStatus)
VALUES (1, 1, '2022-01-01 12:00:00', 30.0, '已完成');
9. 退货
UPDATE OrderInfo SET OrderStatus = '已退货' WHERE OrderID = 1;
以上是一个基本的商店外卖系统的数据库设计和代码实现,具体的实现需要根据实际需求进行调整。