SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` VARCHAR(15) NOT NULL,
  `nickname` VARCHAR(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `password` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES ('201992060','山君彩','wohenaidajia1');
INSERT INTO `user` VALUES ('201992061','汪心理','shwiince');
INSERT INTO `user` VALUES ('201992062','砰好燃','xjwbxjdxks');
INSERT INTO `user` VALUES ('201992063','须加量','wohenzswxedx');
INSERT INTO `user` VALUES ('201992064','章蒽择','wse2zdwE');
INSERT INTO `user` VALUES ('201992065','告的含','dzj3nJCewzw');
INSERT INTO `user` VALUES ('201992066','辽月路','2xnjkwdjxoizq');
INSERT INTO `user` VALUES ('201992067','为与选','a2i1oxnx2');
INSERT INTO `user` VALUES ('201992068','墙赋为','ijjxdjwooo');
INSERT INTO `user` VALUES ('201992069','山赠琳','ooskkqoo');
INSERT INTO `user` VALUES ('201992070','网以通','qiikjnenkaoox');
INSERT INTO `user` VALUES ('201992071','科南','ooakkmqmk');
INSERT INTO `user` VALUES ('201992072','吕聿熙','ooppplllaaa');
INSERT INTO `user` VALUES ('201992073','山与解','qwsderf');
INSERT INTO `user` VALUES ('201992074','深奥','oksneudhcbx');
INSERT INTO `user` VALUES ('201992075','了一行','qazwsx3edc');
INSERT INTO `user` VALUES ('201992076','吕不韦','123qweasd');
INSERT INTO `user` VALUES ('201992077','里奥埃','monster123');
INSERT INTO `user` VALUES ('201992078','加里奥','903jssnee');
INSERT INTO `user` VALUES ('201992079','山林','osjkelanel');


DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`  (
  `c_id` INT NOT NULL AUTO_INCREMENT,
  `c_name` VARCHAR(15) NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `category` VALUES (1,'电子产品');
INSERT INTO `category` VALUES (2,'游戏相关产品');
INSERT INTO `category` VALUES (3,'居家产品');
INSERT INTO `category` VALUES (4,'宠物相关');
INSERT INTO `category` VALUES (5,'美食');
INSERT INTO `category` VALUES (6,'学习用品及玩具');
INSERT INTO `category` VALUES (7,'运动器械');


DROP TABLE IF EXISTS `good`;
CREATE TABLE `good`  (
  `good_id` INT NOT NULL AUTO_INCREMENT,
  `c_id` INT NOT NULL,
  `name` TEXT NOT NULL,
  `publisher_id` VARCHAR(15) NOT NULL,
  `price` FLOAT(11,2) NOT NULL,
  `picture` VARCHAR(100),
  `intro` TEXT,
  `sold` BOOLEAN NOT NULL,
  PRIMARY KEY (`good_id`) USING BTREE,
  FOREIGN KEY (`c_id`) REFERENCES `category` (`c_id`),
  FOREIGN KEY (`publisher_id`) REFERENCES `user` (`user_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 48 DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `good` VALUES (1,1,'散热被夹','201992060',99.5,'1.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (2,1,'USB摄像头','201992062',20.0,'2.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (3,2,'三国杀神荀彧初始号','201992064',1000,'3.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (4,2,'原神钟离号','201992066',100,'4.jpg',NULL,TRUE);
INSERT INTO `good` VALUES (5,1,'二手荣耀手机','201992068',1000,'5.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (6,3,'台灯','201992070',22.5,'6.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (7,5,'黄桃罐头','201992079',6.5,'7.jpeg','上午刚买的黄桃罐头，突然有事，不想吃了，出，急',TRUE);
INSERT INTO `good` VALUES (8,1,'联想充电器','201992067',78.9,'8.jpeg','之前修理电脑，多买了一条，贱卖',TRUE);
INSERT INTO `good` VALUES (9,7,'沙包','201992060',0,'9.jpeg','训练自用，但是最近想换个新的，因此想转手给有缘人，给钱就出',TRUE);
INSERT INTO `good` VALUES (10,1,'ipad','201992070',5000,'10.jpeg','99新，想买了换钱',TRUE);
INSERT INTO `good` VALUES (11,5,'包子','201992063',0,'11.jpeg','新出笼的包子，想分享给邻居们，不要钱，来一楼大厅拿就可以，有韭菜的，土豆的，白菜的',TRUE);
INSERT INTO `good` VALUES (12,4,'剃须刀','201992064',50,'12.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (13,5,'煎饼果子','201992067',5.5,'13.jpeg','外卖多点了一份，有邻居想吃吗，等十分钟没人回我就放冰箱了',TRUE);
INSERT INTO `good` VALUES (14,5,'驱蚊器','201992069',3.6,'14.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (15,7,'滑板','201992060',200,'15.jpeg','铝合金制品，保质保量',TRUE);
INSERT INTO `good` VALUES (16,7,'轮滑鞋','201992067',200,'16.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (17,6,'小学期设计作业','201992068',50,'17.jpeg','保证运行，不能运行我能帮你跑',TRUE);
INSERT INTO `good` VALUES (18,4,'猫爬架','201992064',99.5,'18.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (19,3,'大镜子','201992064',299,'19.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (20,6,'记事本','201992063',3,'20.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (21,1,'鼠标','201992064',70.9,'21.jpeg','99新',TRUE);
INSERT INTO `good` VALUES (22,1,'键盘','201992063',170,'22.jpeg','99新',TRUE);
INSERT INTO `good` VALUES (23,2,'电影票','201992069',28,'23.jpeg','上午买的电影票，杨戬，下午三点的，去不了了',TRUE);
INSERT INTO `good` VALUES (24,4,'奶茶杯','201992072',15.5,'24.jpeg',NULL,TRUE);
INSERT INTO `good` VALUES (25,4,'苏泊尔三明治机','201992061',90,'25.jpg','全新全新，因月底离开北京，现出一批闲置锅具',FALSE);
INSERT INTO `good` VALUES (26,1,'电子表','201992062',10,'26.jpg','手贱多买的生活防水，送电池和说明书',FALSE);
INSERT INTO `good` VALUES (27,1,'苹果s6','201992063',1199,'27.jpg','外观磨损严重，不介意的来，价格可以谈，保修没过',FALSE);
INSERT INTO `good` VALUES (28,6,'布欧圆环','201992064',800,'28.jpg','布欧圆环，800元送一个500系列的软胶',FALSE);
INSERT INTO `good` VALUES (29,4,'法斗','201992065',224,'29.jpg','自家养殖纯种法斗幼犬，已经两个月了，公母都有，驱虫疫苗已经做了',FALSE);
INSERT INTO `good` VALUES (30,3,'儿童玩具收纳架','201992066',46,'30.jpg','厂家促销活动，全新正品未拆封，最后30套',TRUE);
INSERT INTO `good` VALUES (31,6,'夹子高达','201992067',9.90,'31.jpg','夹子高达，艺术品摆件高达',TRUE);
INSERT INTO `good` VALUES (32,6,'2008年北京福娃纪念品','201992068',200,'32.jpg','旋转便笺本一套五本',TRUE);
INSERT INTO `good` VALUES (33,1,'星之卡比耳机','201992069',659,'33.jpg','全新正版，日本直邮回国，不议价，确认收货，不退不换',TRUE);
INSERT INTO `good` VALUES (34,3,'奥克斯空调扇','201992070',480,'34.jpg','奥克斯空调扇，妹妹工作室用了几天，几乎全新',TRUE);
INSERT INTO `good` VALUES (35,3,'水冷机箱','201992071',95,'35.jpg','鲅鱼圈可以自取减十块钱',TRUE);
INSERT INTO `good` VALUES (36,3,'无火气氛融蜡灯','201992072',300,'36.jpg',NULL,TRUE);
INSERT INTO `good` VALUES (37,6,'东北大象棋','201992073',98,'37.jpg','新收的，有些年份，字体挺好，什么木头不认识',FALSE);
INSERT INTO `good` VALUES (38,6,'软式飞镖靶','201992074',800,'38.jpg','蓝牙智能连接，专业家用电子飞镖盘',FALSE);
INSERT INTO `good` VALUES (39,3,'小地毯','201992075',20,'39.jpg','宽50厘米，长110厘米',FALSE);
INSERT INTO `good` VALUES (40,3,'纯手绘丙烯画','201992076',35,'40.jpg','尺寸大小可以定制',FALSE);
INSERT INTO `good` VALUES (41,3,'宝宝锁','201992077',289,'41.jpg','和田玉青玉山水宝宝锁，简单好看',FALSE);
INSERT INTO `good` VALUES (42,6,'钓竿','201992078',130,'42.jpg','个人转让：汉鼎一号三代5.7米',FALSE);
INSERT INTO `good` VALUES (43,1,'爱普生投影仪','201992079',400,'43.jpg','出租，全国包邮',FALSE);
INSERT INTO `good` VALUES (44,6,'尤克里里','201992060',19.9,'44.jpg','正版吉他，高颜值乐器',FALSE);
INSERT INTO `good` VALUES (45,4,'猫玩具','201992064',19.8,'45.jpg','猫玩具低价出售',FALSE);
INSERT INTO `good` VALUES (46,6,'泥人','201992063',30,'46.jpg','正版散货DC蝙蝠侠鲨鱼王贝恩鳄鱼泥人',FALSE);
INSERT INTO `good` VALUES (47,3,'实木双人床','201992069',148,'47.jpg',NULL,FALSE);

DROP TABLE IF EXISTS `message`;
CREATE TABLE `message`  (
  `sender_id` VARCHAR(15) NOT NULL,
  `receiver_id` VARCHAR(15) NOT NULL,
  `time` TIMESTAMP(6) NOT NULL,
  `content` VARCHAR(100),
  PRIMARY KEY (`sender_id`, `receiver_id`, `time`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `message` VALUES ('201992079','201992060','220823','我想买你的散热背夹');
INSERT INTO `message` VALUES ('201992061','201992062','220822','我想买你的USB摄像头');
INSERT INTO `message` VALUES ('201992063','201992064','220820','我想买的三国杀神荀彧初始号，500块钱可以吗？');
INSERT INTO `message` VALUES ('201992065','201992066','220821','我想买你的原神钟离号，您给个价格好吗？');
INSERT INTO `message` VALUES ('201992067','201992068','220819','我想买你的二手荣耀手机，1000块可以吗？');
INSERT INTO `message` VALUES ('201992079','201992070','220823','我想买你的台灯');
INSERT INTO `message` VALUES ('201992078','201992079','220823','我想买你的黄桃罐头');
INSERT INTO `message` VALUES ('201992075','201992067','220823','我想买你的联想充电器');
INSERT INTO `message` VALUES ('201992076','201992060','220823','我想买你的沙包');
INSERT INTO `message` VALUES ('201992071','201992070','220823','我想买你的ipad');
INSERT INTO `message` VALUES ('201992072','201992063','220823','我想买你的二手包子，韭菜馅的');
INSERT INTO `message` VALUES ('201992067','201992064','220823','我想买你的二手剃须刀');
INSERT INTO `message` VALUES ('201992068','201992067','220123','我想买你的二手煎饼果子，早饭还没吃');
INSERT INTO `message` VALUES ('201992079','201992069','220223','我想买你的驱蚊器');
INSERT INTO `message` VALUES ('201992079','201992060','220323','我想买你的滑板');
INSERT INTO `message` VALUES ('201992066','201992067','220423','我想买你的轮滑鞋');
INSERT INTO `message` VALUES ('201992079','201992068','220523','我想买你的小学期设计作业');
INSERT INTO `message` VALUES ('201992074','201992064','220623','我想买你的猫爬架');
INSERT INTO `message` VALUES ('201992075','201992064','220723','我想买你的大镜子');
INSERT INTO `message` VALUES ('201992076','201992063','220823','我想买你的记事本');
INSERT INTO `message` VALUES ('201992076','201992064','220822','我想买你的鼠标');
INSERT INTO `message` VALUES ('201992075','201992063','220822','我想买你的键盘');
INSERT INTO `message` VALUES ('201992079','201992069','220821','我想买你的电影票');
INSERT INTO `message` VALUES ('201992079','201992072','220821','我想买你的奶茶杯');

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `order_id` INT NOT NULL AUTO_INCREMENT,
  `buyer_id` VARCHAR(15) NOT NULL,
  `seller_id` VARCHAR(15) NOT NULL,
  `good_id` INT NOT NULL,
  `price` FLOAT(11,2) NOT NULL,
  `status` INT NOT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 37 DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `order` VALUES (1,'201992079','201992060',1,99.5,1);
INSERT INTO `order` VALUES (2,'201992061','201992062',2,20.0,1);
INSERT INTO `order` VALUES (3,'201992063','201992064',3,1000,1);
INSERT INTO `order` VALUES (4,'201992065','201992066',4,100,1);
INSERT INTO `order` VALUES (5,'201992067','201992068',5,1000,1);
INSERT INTO `order` VALUES (6,'201992079','201992070',6,22.5,2);
INSERT INTO `order` VALUES (7,'201992078','201992079',7,6.5,2);
INSERT INTO `order` VALUES (8,'201992075','201992067',8,78.9,2);
INSERT INTO `order` VALUES (9,'201992076','201992060',9,0,3);
INSERT INTO `order` VALUES (10,'201992071','201992070',10,3000,2);
INSERT INTO `order` VALUES (11,'201992072','201992063',11,0,3);
INSERT INTO `order` VALUES (12,'201992067','201992064',12,35,2);
INSERT INTO `order` VALUES (13,'201992068','201992067',13,5.5,3);
INSERT INTO `order` VALUES (14,'201992079','201992069',14,3.6,3);
INSERT INTO `order` VALUES (15,'201992079','201992060',15,200,2);
INSERT INTO `order` VALUES (16,'201992066','201992067',16,200,2);
INSERT INTO `order` VALUES (17,'201992079','201992068',17,50,2);
INSERT INTO `order` VALUES (18,'201992074','201992064',18,89,2);
INSERT INTO `order` VALUES (19,'201992075','201992064',19,200,3);
INSERT INTO `order` VALUES (20,'201992076','201992063',20,3,3);
INSERT INTO `order` VALUES (21,'201992076','201992064',21,60,2);
INSERT INTO `order` VALUES (22,'201992075','201992063',22,150,3);
INSERT INTO `order` VALUES (23,'201992079','201992069',23,20,3);
INSERT INTO `order` VALUES (24,'201992079','201992072',24,10,3);
INSERT INTO `order` VALUES (25,'201992079','201992061',25,90,1);
INSERT INTO `order` VALUES (26,'201992061','201992062',26,10,1);
INSERT INTO `order` VALUES (27,'201992064','201992063',27,1199,1);
INSERT INTO `order` VALUES (28,'201992065','201992064',28,800,1);
INSERT INTO `order` VALUES (29,'201992067','201992065',29,224,1);
INSERT INTO `order` VALUES (30,'201992079','201992066',30,46,2);
INSERT INTO `order` VALUES (31,'201992078','201992067',31,9.9,2);
INSERT INTO `order` VALUES (32,'201992075','201992068',32,200,2);
INSERT INTO `order` VALUES (33,'201992076','201992069',33,659,3);
INSERT INTO `order` VALUES (34,'201992071','201992070',34,480,2);
INSERT INTO `order` VALUES (35,'201992072','201992071',35,95,3);
INSERT INTO `order` VALUES (36,'201992067','201992072',36,300,2);

DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment`  (
  `order_id` INT NOT NULL,
  `buyer_id` VARCHAR(15) NOT NULL,
  `flag` INT NOT NULL,
  `content` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `comment` VALUES (9,'201992076',1,'卖家没要钱，知道我是散打爱好者直接送我了，很开心');
INSERT INTO `comment` VALUES (11,'201992072',1,'大娘的包子太好吃啦！');
INSERT INTO `comment` VALUES (13,'201992068',1,'用户未评价，系统默认好评');
INSERT INTO `comment` VALUES (14,'201992079',1,'用户未评价，系统默认好评');
INSERT INTO `comment` VALUES (19,'201992075',1,'用户未评价，系统默认好评');
INSERT INTO `comment` VALUES (20,'201992076',1,'用户未评价，系统默认好评');
INSERT INTO `comment` VALUES (22,'201992075',3,'卖东西也不擦干净，胶粘');
INSERT INTO `comment` VALUES (23,'201992079',1,'用户未评价，系统默认好评');
INSERT INTO `comment` VALUES (24,'201992079',1,'用户未评价，系统默认好评');
INSERT INTO `comment` VALUES (33,'201992076',1,'用户未评价，系统默认好评');
INSERT INTO `comment` VALUES (35,'201992072',2,'还行吧，就是太脏了');

DROP PROCEDURE IF EXISTS `checkPwd`;
CREATE PROCEDURE checkPwd(IN id VARCHAR(15), IN pwd VARCHAR(20))
SELECT count(*) FROM user WHERE user_id = id AND password = pwd