QT: 电子商城系统-MYSQL数据库代码

QT: 第17章【统合实例】电子商城系统

MYSQL代码：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

/*
SQLyog Ultimate v12.3.2 (64 bit)
MySQL - 8.0.11 : Database - emarket
*********************************************************************
*/
 
 
/*!40101 SET NAMES utf8 */;
 
/*!40101 SET SQL_MODE=''*/;
 
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`emarket` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
 
USE `emarket`;
 
/*Table structure for table `category` */
 
DROP TABLE IF EXISTS `category`;
 
CREATE TABLE `category` (
  `CategoryID` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品类别编号，主键，自动递增',
  `Name` varchar(16) COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品类型名称',
  PRIMARY KEY (`CategoryID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
/*Data for the table `category` */
 
insert  into `category`(`CategoryID`,`Name`) values
 
(1,'服装'),
 
(2,'美食'),
 
(3,'手机');
 
/*Table structure for table `commodity` */
 
DROP TABLE IF EXISTS `commodity`;
 
CREATE TABLE `commodity` (
  `CommodityID` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品编号，主键，自动递增',
  `CategoryID` int(11) NOT NULL COMMENT '商品类型编号',
  `Name` varchar(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品名称',
  `Picture` blob COMMENT '商品图片',
  `InputPrice` float(6,2) NOT NULL COMMENT '商品购入价格（进价）',
  `OutputPrice` float(6,2) NOT NULL COMMENT '商品售出价格（单价）',
  `Amount` int(11) NOT NULL COMMENT '商品库存量',
  PRIMARY KEY (`CommodityID`),
  KEY `CategoryID_FK` (`CategoryID`),
  CONSTRAINT `CategoryID_FK` FOREIGN KEY (`CategoryID`) REFERENCES `category` (`categoryid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
/*Data for the table `commodity` */
 
insert  into `commodity`(`CommodityID`,`CategoryID`,`Name`,`Picture`,`InputPrice`,`OutputPrice`,`Amount`) values
 
(1,1,'潜水拉链长袖防晒分体泳衣',NULL,255.60,357.84,478),
 
(2,1,'空姐制服2018时尚气质工作服',NULL,158.00,305.00,398644),
 
(3,1,'职业套装女2018新款OL面试工装',NULL,688.00,788.98,12416),
 
(4,3,'Huawei/华为P20 4000万俫卡三摄',NULL,3388.00,3888.00,1235),
 
(5,2,'延禧攻略网红糕点饼干礼盒',NULL,168.00,188.00,796),
 
(6,2,'【三只松鼠】小吃货抖音美食大礼包',NULL,59.90,130.00,138081);
 
/*Table structure for table `member` */
 
DROP TABLE IF EXISTS `member`;
 
CREATE TABLE `member` (
  `MemberID` varchar(16) COLLATE utf8mb4_general_ci NOT NULL COMMENT '会员账号，主键',
  `PassWord` varchar(50) COLLATE utf8mb4_general_ci NOT NULL COMMENT '登录口令（以MD5加密存储）',
  `Name` varchar(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '会员名',
  `Sex` bit(1) NOT NULL DEFAULT b'1' COMMENT '性别：1表示男，0表示女，默认1',
  `Email` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Address` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Phone` varchar(16) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '联系电话',
  `RegisterDate` date NOT NULL COMMENT '注册日期',
  PRIMARY KEY (`MemberID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
/*Data for the table `member` */
 
insert  into `member`(`MemberID`,`PassWord`,`Name`,`Sex`,`Email`,`Address`,`Phone`,`RegisterDate`) values
 
('b02020622','','周何骏','',NULL,NULL,NULL,'2018-11-22');
 
/*Table structure for table `orderitems` */
 
DROP TABLE IF EXISTS `orderitems`;
 
CREATE TABLE `orderitems` (
  `OrderID` int(11) NOT NULL COMMENT '订单编号，主键',
  `CommodityID` int(11) NOT NULL COMMENT '商品编号，主键',
  `Count` int(11) NOT NULL COMMENT '数量',
  `Affirm` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否确认，0没有确认，1确认，默认0',
  `SendGoods` bit(1) DEFAULT b'0' COMMENT '是否发货，0没有发货，1发货，默认0',
  PRIMARY KEY (`OrderID`,`CommodityID`),
  KEY `CommodityID_FK` (`CommodityID`),
  CONSTRAINT `CommodityID_FK` FOREIGN KEY (`CommodityID`) REFERENCES `commodity` (`commodityid`),
  CONSTRAINT `OrderID` FOREIGN KEY (`OrderID`) REFERENCES `orders` (`orderid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
/*Data for the table `orderitems` */
 
/*Table structure for table `orders` */
 
DROP TABLE IF EXISTS `orders`;
 
CREATE TABLE `orders` (
  `OrderID` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号，主键，自动递增',
  `MemberID` varchar(16) COLLATE utf8mb4_general_ci NOT NULL COMMENT '会员账号',
  `PaySum` float(6,2) DEFAULT NULL COMMENT '付款总金额',
  `PayWay` varchar(32) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '付款方式',
  `OTime` datetime DEFAULT NULL COMMENT '下单日期时间',
  PRIMARY KEY (`OrderID`),
  KEY `MemberID_FK` (`MemberID`),
  CONSTRAINT `MemberID_FK` FOREIGN KEY (`MemberID`) REFERENCES `member` (`memberid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
 
/*Data for the table `orders` */
 
/*Table structure for table `commodity_inf` */
 
DROP TABLE IF EXISTS `commodity_inf`;
 
/*!50001 DROP VIEW IF EXISTS `commodity_inf` */;
/*!50001 DROP TABLE IF EXISTS `commodity_inf` */;
 
/*!50001 CREATE TABLE  `commodity_inf`(
 `商品名称` varchar(32) ,
 `进价(￥)` float(6,2) ,
 `售价(￥)` float(6,2) ,
 `库存` int(11) 
)*/;
 
/*View structure for view commodity_inf */
 
/*!50001 DROP TABLE IF EXISTS `commodity_inf` */;
/*!50001 DROP VIEW IF EXISTS `commodity_inf` */;
 
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `commodity_inf` AS select `commodity`.`Name` AS `商品名称`,`commodity`.`InputPrice` AS `进价(￥)`,`commodity`.`OutputPrice` AS `售价(￥)`,`commodity`.`Amount` AS `库存` from `commodity` */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
 
CREATE
    /*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]*/
    VIEW `emarket`.`commodity_inf` 
    AS
(SELECT commodity.`Name` AS `商品名称`,
commodity.`InputPrice` AS `进价(￥)`,
commodity.`OutputPrice` AS `售价(￥)`,
commodity.`Amount` AS `库存`
  FROM commodity);