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

/*
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);