mysql - 退款 MySQL 查询

Question

我的数据库中目前有 5 个表

科, 客户, 订单线, 订单, 产品

我希望能够通过它的orderid退款含义来退款订单：将订单总价设置回0.00，每个订单行的总价设置为0.00，将订单行中的数量添加回产品，以及将订单orderstatus设置为RFND

继承人我的数据库到目前为止

DROP DATABASE IF EXISTS MMWally;

CREATE DATABASE IF NOT EXISTS MMWally;

USE MMWally;

--
-- Definition of table `categories`
--

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`
(
    `ProductID` int NOT NULL,
    `ProductName` varchar(50) NOT NULL, 
    `wPrice` double(13, 2) NOT NULL,
    `Stock` int NOT NULL,
    `BranchID` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `CustomerID` int NOT NULL,
  `FirstName` tinytext NOT NULL,
  `LastName` tinytext NOT NULL,
  `Phone` VARCHAR(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `branch`;
CREATE TABLE `branch`
(
    `BranchID` int NOT NULL,
    `BranchName` tinytext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`
(
    `OrderID` int NOT NULL,
    `CustomerID` int NOT NULL,
    `BranchID` int NOT NULL,
    `OrderDate` date NOT NULL,
    `sPrice` double(13, 2) DEFAULT NULL,
    `Status` tinytext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `orderline`;
CREATE TABLE `orderline`
(
    `OrderID` int NOT NULL,
    `OrderLineID` int NOT NULL,
    `ProductID` int NOT NULL,
    `ItemQuantity` int NOT NULL,
    `TotalPrice` double(13, 2) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE orders MODIFY OrderID INT AUTO_INCREMENT PRIMARY KEY; 
ALTER TABLE orderline MODIFY OrderLineID INT AUTO_INCREMENT PRIMARY KEY; 
ALTER TABLE branch MODIFY BranchID INT AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE customers MODIFY CustomerID INT AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE products MODIFY ProductID INT AUTO_INCREMENT PRIMARY KEY;


/* FOREIGN KEYS */

ALTER TABLE orders ADD CONSTRAINT FOREIGN KEY (CustomerID) REFERENCES customers (CustomerID);
ALTER TABLE orders ADD CONSTRAINT FOREIGN KEY (BranchID) REFERENCES branch (BranchID);
ALTER TABLE products ADD CONSTRAINT FOREIGN KEY (BranchID) REFERENCES branch (BranchID);
ALTER TABLE orderline ADD CONSTRAINT FOREIGN KEY (OrderID) REFERENCES orders (OrderID);
ALTER TABLE orderline ADD CONSTRAINT FOREIGN KEY (ProductID) REFERENCES products (ProductID);

ALTER TABLE orders ADD CONSTRAINT CHECK (`Status` = 'PAID' OR `Status` = 'RFND');

/*!40000 ALTER TABLE `branch` DISABLE KEYS */;
INSERT INTO `branch` (`BranchID`, `BranchName`) VALUES 
 (1, 'Sports World'),
 (2, 'Waterloo'),
 (3, 'Cambridge Mall');
/*!40000 ALTER TABLE `branch` ENABLE KEYS */;

/*!40000 ALTER TABLE `customers` DISABLE KEYS */;
INSERT INTO `customers` (`CustomerID`, `FirstName`, `LastName`, `Phone`) VALUES 
 (1, 'Carlo', 'Sgro', 5195550000),
 (2, 'Norbert', 'Mika', 4165551111),
 (3, 'Russell', 'Foubert', 5195552222),
 (4, 'Sean', 'Clarke', 5195553333),
 (5, 'Sean', 'Clarke', 5192395285);
/*!40000 ALTER TABLE `customers` ENABLE KEYS */;

/*!40000 ALTER TABLE `products` DISABLE KEYS */;
INSERT INTO `products` (`ProductID`,`ProductName`, `wPrice`, `Stock`, `BranchID`) VALUES 
 (1, 'Disco Queen Wallpaper (roll)', 12.95, 56, 1),
 (2, 'Countryside Wallpaper (roll)', 11.95, 24, 1),
 (3, 'Drywall Tape (roll)', 3.95, 120, 2),
 (4, 'Drywall Tape (pkg 10)', 36.95, 30, 2),
 (5, 'Drywall Repair Compound (tube)', 6.95, 90, 3),
 (6, 'Victorian Lace Wallpaper (roll)', 14.95, 44, 3);
/*!40000 ALTER TABLE `products` ENABLE KEYS */;


/*!40000 ALTER TABLE `orders` DISABLE KEYS */;
INSERT INTO orders(CustomerID, BranchID, OrderDate, sPrice, Status) VALUES 
(1, 1, '2019-07-20', 20.93, 'PAID'),
(2, 1, '2019-07-20', 9.73, 'PAID'),
(3, 1, '2019-07-20', 5.53, 'PAID'),
(4, 3, '2019-10-06', 16.73, 'PAID'),
(4, 2, '2019-11-02', 18.13, 'PAID'),
(3, 2, '2019-11-02', 5.53, 'PAID'),
(2, 2, '2019-11-02', 18.13, 'RFND'),
(1, 2, '2019-11-02', 5.53, 'RFND');
/*!40000 ALTER TABLE `orders` ENABLE KEYS */;

标签： mysqldatabase