数据库assignment，就差最后添加2个构造函数了，怎么弄都报错。能不能救一下
drop database wxshop;
create database WXShop;
use WXShop;
-- 创建用户表user
CREATE TABLE User (
UserID INT PRIMARY KEY AUTO_INCREMENT, -- 自增主键
UserName VARCHAR(255) NOT NULL,
Password VARCHAR(255) NOT NULL,
Phone VARCHAR(20),
wx_name varchar(30),
wx_number varchar(60),
UNIQUE (UserName)
);
-- 创建客服表
CREATE TABLE customer_service (
customer_service_id INT NOT NULL AUTO_INCREMENT,
customer_service_name VARCHAR(255) NOT NULL,
customer_service_phone VARCHAR(255) NOT NULL,
PRIMARY KEY (customer_service_id)
);
-- 创建商家表
CREATE TABLE merchant (
merchant_id INT NOT NULL AUTO_INCREMENT,
merchant_name VARCHAR(255) NOT NULL,
merchant_address VARCHAR(255) NOT NULL,
merchant_phone VARCHAR (255) NOT NULL,
PRIMARY KEY (merchant_id)
);
-- 创建订单表
CREATE TABLE `Order` (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
UserID INT NOT NULL,
Order_customer_service_id INT NOT NULL COMMENT '客服id',
Order_ProductName VARCHAR(255) NOT NULL COMMENT '商品名称',
OrderTime TIMESTAMP DEFAULT NOW(),
TotalPrice DECIMAL(10,2) NOT NULL,
Status VARCHAR(20) NOT NULL,
FOREIGN KEY (UserID) REFERENCES User(UserID),
FOREIGN KEY (Order_customer_service_id) REFERENCES customer_service (customer_service_id)
);
DELIMITER ;
-- 创建支付表
CREATE TABLE payment (
paymentID INT NOT NULL AUTO_INCREMENT,
payment_order_id INT NOT NULL COMMENT '订单ID',
payment_method VARCHAR(255) NOT NULL COMMENT '支付方式',
payment_time DATETIME NOT NULL COMMENT '支付时间',
payment_amount DECIMAL(10, 2) NOT NULL COMMENT '支付金额',
PRIMARY KEY (paymentID),
KEY fk_payment_order_id (payment_order_id),
CONSTRAINT fk_payment_order_id FOREIGN KEY (payment_order_id) REFERENCES `Order` (OrderID)
);
-- 创建商品表
CREATE TABLE Product (
ProductID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
product_merchant_id INT NOT NULL COMMENT '商家id',
ProductName VARCHAR(255) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
Inventory INT NOT NULL,
Category VARCHAR(50),
FOREIGN KEY (product_merchant_id) REFERENCES `Order` (OrderID)
);
-- 创建售后表
CREATE TABLE aftersales (
aftersales_id INT NOT NULL AUTO_INCREMENT,
aftersales_order_id INT NOT NULL COMMENT '订单ID，外键',
aftersales_product_id INT NOT NULL COMMENT '商品ID，外键',
aftersales_type INT NOT NULL COMMENT '售后类型，1表示退货，2表示换货，3表示物流',
aftersales_status INT NOT NULL COMMENT '售后状态，1表示待处理，2表示进行中，3表示已完成',
aftersales_reason TEXT NOT NULL COMMENT '售后原因',
aftersales_remark TEXT COMMENT '售后备注',
aftersales_create_time DATETIME NOT NULL COMMENT '创建时间',
aftersales_finish_time DATETIME NOT NULL COMMENT '完成时间',
PRIMARY KEY (aftersales_id),
KEY fk_aftersales_order_id (aftersales_order_id),
KEY fk_aftersales_product_id (aftersales_product_id),
CONSTRAINT fk_aftersales_order_id FOREIGN KEY (aftersales_order_id) REFERENCES `order` (OrderID),
CONSTRAINT fk_aftersales_product_id FOREIGN KEY (aftersales_product_id) REFERENCES product (ProductID)
);
-- 插入用户数据
INSERT INTO User(UserName, Password, Phone,wx_name,wx_number)VALUES
('john', '123456', '1234567890','join','joinnum01'),
('jane', 'abcdef', '2345678901','jane','janenum02'),
('june', '567890', '3456789012','june','junenum03');
-- 插入客服数据
INSERT INTO customer_service (customer_service_name, customer_service_phone) VALUES
('Jone', '88888888'),
('Lucy', '77777777'),
('David', '66666666');
-- 插入商家数据
INSERT INTO merchant (merchant_name, merchant_address, merchant_phone) VALUES
('Apple Store', 'Cupertino, CA, USA', '408-996-1010'),
('Nike Store', 'Beaverton, OR, USA', '800-344-6453'),
('Adidas Store', 'Herzogenaurach, Germany', '49 9132 84-0');
-- 插入订单数据
INSERT INTO `Order` (UserID, Order_customer_service_id,Order_ProductName,TotalPrice, Status) VALUES
(1, 1, 'iPhone 12',6999.00, 'Processing'),
(2, 2, 'Nike Air Max',799.00, 'Delivered'),
(3, 1,'Adidas Superstar',599.00, 'Shipped');
-- 插入商品数据
INSERT INTO Product (product_merchant_id, ProductName, Price, Inventory, Category) VALUES
(1, 'iPhone 12', 6999.00, 100, 'Electronics'),
(1, 'MacBook Pro', 12999.00, 75, 'Electronics'),
(1, 'AirPods Pro', 1999.00, 50, 'Electronics'),
(2, 'Nike Air Max', 799.00, 200, 'Shoes'),
(3, 'Adidas Superstar', 599.00, 150, 'Shoes'),
(2, 'Converse Chuck Taylor', 399.00, 300, 'Shoes'),
(2, 'Levi Jeans', 599.00, 100, 'Clothing'),
(3, 'PUMA RS-Fast Sneakers', 899.00, 80, 'Shoes');
-- 插入支付数据
INSERT INTO payment (payment_order_id, payment_method, payment_time, payment_amount) VALUES
(1, 'Alipay', '2023-06-14 17:20:01', 6999.00),
(2, 'WeChat Pay', '2023-06-11 09:30:55', 799.00),
(3, 'Credit Card', '2023-06-12 21:15:12', 599.00);
-- 插入售后数据
INSERT INTO aftersales (aftersales_order_id, aftersales_product_id, aftersales_type, aftersales_status,
aftersales_reason, aftersales_remark, aftersales_create_time, aftersales_finish_time) VALUES
(1, 1, 1, 1, 'Defective product', 'Replace with new one', '2023-06-15 14:30:00', '2023-06-16 12:00:00'),
(2, 3, 2, 3, 'Size not fit', 'Refund requested', '2023-06-11 10:00:00', '2023-06-12 18:15:00'),
(3, 4, 3, 1, 'Delivery status check', 'quick', '2023-06-12 08:45:00', '2023-06-13 14:30:00');
-- 索引的创建
-- 索引1
create index index_ProductName
on Product(ProductName);
-- 索引2
create index index_ProductPrice
on Product(Price);
-- 视图的创建
-- 客户订单信息视图
CREATE VIEW v_customer_order_info AS
SELECT
o.OrderID,
o.TotalPrice,
o.Status,
u.UserName,
u.Phone,
u.wx_name,
p.ProductID,
p.ProductName,
p.Price,
p.Inventory
FROM `Order` o,
user u,
Product p
where u.UserID = o.UserID
and p.product_merchant_id = o.OrderID;
-- 商家销售情况视图
CREATE VIEW v_merchant_sales_info AS
SELECT
m.merchant_name,
m.merchant_address,
m.merchant_phone,
p.ProductID,
p.ProductName,
SUM(o.TotalPrice) AS sales_amount
FROM merchant m,
Product p,
`Order` o,
payment pay
where p.product_merchant_id = m.merchant_id
and o.OrderID = p.ProductID
and pay.payment_order_id = o.OrderID;
-- 存储过程的创建
-- 存储过程1：注册新用户信息
DELIMITER $$
CREATE PROCEDURE sp_InsertUser (
IN p_UserName VARCHAR(255),
IN p_Password VARCHAR(255),
IN p_Phone VARCHAR(20),
IN p_wx_name varchar(30),
IN p_wx_number varchar(60)
)
BEGIN
INSERT INTO User (UserName, Password, Phone, wx_name, wx_number)
VALUES (p_UserName, p_Password, p_Phone, p_wx_name, p_wx_number);
END$$
DELIMITER ;
-- 存储过程2：更新User表中指定用户的手机号
DELIMITER $$
CREATE PROCEDURE sp_UpdateUserPhone (
IN p_UserID INT,
IN p_Phone VARCHAR(20)
)
BEGIN
UPDATE User
SET Phone = p_Phone
WHERE UserID = p_UserID;
END$$
DELIMITER ;
-- 存储过程3：输入商品名称查看商品价格
DELIMITER $$
CREATE PROCEDURE GetProductPrice (IN productName VARCHAR(255))
BEGIN
SELECT Price
FROM Product
WHERE ProductName = productName;
END$$
DELIMITER ;
-- 创建时间记录表
create table timelog
(timelogID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
savetime TIMESTAMP DEFAULT NOW(),
timelog_ProductName VARCHAR(255) NOT NULL,
timelog_UserName VARCHAR(255) NOT NULL
);
-- 触发器1：在用户表中查看商品时，保存查看时的时间，将时间、用户名称、商品名称记录在表timelog中
DELIMITER $$
CREATE TRIGGER user_insert
AFTER UPDATE
ON timelog FOR EACH ROW
BEGIN
INSERT INTO timelog(timelog_ProductName, timelog_UserName, savetime)
VALUES(
NEW.timelog_ProductName,
NEW.timelog_UserName,
NOW()
);
END$$
DELIMITER ;
-- 触发器2：创建触发器实现用户在user表中更改用户名或密码，
-- 并将更新好的用户名或密码覆盖之前的用户名或密码
DELIMITER $$
CREATE TRIGGER user_update_username_password
BEFORE UPDATE
ON User FOR EACH ROW
BEGIN
IF old.UserName != new.UserName THEN
SET NEW.UserName = new.UserName;
END IF;
IF old.Password != new.Password THEN
SET NEW.Password = new.Password;
END IF;
END$$
DELIMITER ;
-- 创建构造函数：插入订单记录
DROP FUNCTION IF EXISTS insert_order_record;
DELIMITER //
CREATE FUNCTION insert_order_record(user_id INT, customer_service_id INT, product_name VARCHAR(255), total_price DECIMAL(10,2), status VARCHAR(20))
RETURNS INT
BEGIN
DECLARE new_order_id INT;
INSERT INTO `Order` (UserID, Order_customer_service_id, Order_ProductName, TotalPrice, Status)
VALUES (user_id, customer_service_id, product_name, total_price, status);
SET new_order_id = LAST_INSERT_ID();
RETURN new_order_id;
END //
DELIMITER ;
-- 插入订单记录
SELECT insert_order_record(1, 1, 'Product A', 100.00, 'Pending'); -- 传入用户ID、客服ID、商品名称、总价
你好,根据你的描述,你现在要添加两个构造函数,我理解如下:
Order (UserID, Order_customer_service_id, Order_ProductName, TotalPrice, Status)