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

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

MYSQL代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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);

  问题1:MYSQL8 导入出来的数据,导入时自增字段怎么处理呢?

问题2:MYSQL 8 字段是BIT类型,导出来是一个不可识别的字符串,导入时会自动识别吗?

 

posted @   samrv  阅读(34)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示