MySQL

最新推荐文章于 2024-09-13 21:40:38 发布

zhuangbiqifei

最新推荐文章于 2024-09-13 21:40:38 发布

阅读量257

点赞数 3

文章标签：数据库 sql java

本文链接：https://blog.csdn.net/zhuangbiqifei/article/details/139776743

版权

CREATE TABLE `consumer` (
  `ConsumerId` int(11) NOT NULL AUTO_INCREMENT,
  `ConsumerName` varchar(50) NOT NULL,
  `Password` varchar(255) NOT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `Phone` varchar(20) DEFAULT NULL,
  `CreateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ConsumerId`),
  UNIQUE KEY `ConsumerName` (`ConsumerName`),
  UNIQUE KEY `Email` (`Email`),
  UNIQUE KEY `Phone` (`Phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `consumerwallet` (
  `WalletId` int(11) NOT NULL AUTO_INCREMENT,
  `ConsumerId` int(11) NOT NULL,
  `Balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `CreatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`WalletId`),
  KEY `ConsumerId` (`ConsumerId`),
  CONSTRAINT `consumerwallet_ibfk_1` FOREIGN KEY (`ConsumerId`) REFERENCES `consumer` (`ConsumerId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `consumerwalletlog` (
  `LogId` int(11) NOT NULL AUTO_INCREMENT,
  `ConsumerId` int(11) NOT NULL,
  `TransactionType` varchar(50) NOT NULL,
  `Amount` decimal(10,2) NOT NULL,
  `TransactionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`LogId`),
  KEY `ConsumerId` (`ConsumerId`),
  CONSTRAINT `consumerwalletlog_ibfk_1` FOREIGN KEY (`ConsumerId`) REFERENCES `consumer` (`ConsumerId`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `order` (
  `OrderId` int(11) NOT NULL AUTO_INCREMENT,
  `ConsumerId` int(11) NOT NULL,
  `OrderStatus` varchar(50) NOT NULL DEFAULT '待支付',
  `OrderTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `PaymentStatus` varchar(50) NOT NULL DEFAULT '未支付',
  `PaymentTime` timestamp NULL DEFAULT NULL,
  `TotalPrice` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`OrderId`),
  KEY `ConsumerId` (`ConsumerId`),
  CONSTRAINT `order_ibfk_1` FOREIGN KEY (`ConsumerId`) REFERENCES `consumer` (`ConsumerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `orderinfo` (
  `OrderInfoId` int(11) NOT NULL AUTO_INCREMENT,
  `OrderId` int(11) NOT NULL,
  `ProductId` int(11) NOT NULL,
  `Quantity` int(11) NOT NULL,
  `UnitPrice` decimal(10,2) NOT NULL,
  PRIMARY KEY (`OrderInfoId`),
  KEY `OrderId` (`OrderId`),
  KEY `ProductId` (`ProductId`),
  CONSTRAINT `orderinfo_ibfk_1` FOREIGN KEY (`OrderId`) REFERENCES `order` (`OrderId`) ON DELETE CASCADE,
  CONSTRAINT `orderinfo_ibfk_2` FOREIGN KEY (`ProductId`) REFERENCES `product` (`ProductId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `product` (
  `ProductId` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(255) NOT NULL,
  `Price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `Stock` int(11) NOT NULL DEFAULT '0',
  `TypeId` int(11) NOT NULL,
  `CreatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ProductId`),
  KEY `TypeId` (`TypeId`),
  CONSTRAINT `product_ibfk_1` FOREIGN KEY (`TypeId`) REFERENCES `producttype` (`TypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `producttype` (
  `TypeId` int(11) NOT NULL AUTO_INCREMENT,
  `TypeName` varchar(100) NOT NULL,
  `ParentId` int(11) DEFAULT NULL,
  `Description` text,
  `CreatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`TypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;