SeconHand
/
seconhandDB.sql

/*
 Navicat Premium Data Transfer

 Source Server         : MyBates
 Source Server Type    : MySQL
 Source Server Version : 80016
 Source Host           : localhost:3306
 Source Schema         : seconhand

 Target Server Type    : MySQL
 Target Server Version : 80016
 File Encoding         : 65001

 Date: 07/05/2020 17:05:40
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for comment
-- ----------------------------
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment`  (
  `cid` int(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '评论id（主键）自动分配',
  `uid` int(8) UNSIGNED ZEROFILL NOT NULL COMMENT '用户id（外键）',
  `iid` int(8) UNSIGNED ZEROFILL NOT NULL COMMENT '帖id（外键）',
  `ccontent` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '评论内容',
  `cidp` int(8) UNSIGNED ZEROFILL NULL DEFAULT 00000000 COMMENT '回复评论编号（评论他人的评论，默认为null）(外键)',
  `ctime` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '评论时间',
  `cread` int(2) NOT NULL COMMENT '被读状态（已读1，未读0（默认））',
  PRIMARY KEY (`cid`) USING BTREE,
  INDEX `C_I`(`iid`) USING BTREE,
  INDEX `C_U`(`uid`) USING BTREE,
  INDEX `C_C`(`cidp`) USING BTREE,
  CONSTRAINT `C_C` FOREIGN KEY (`cidp`) REFERENCES `comment` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `C_I` FOREIGN KEY (`iid`) REFERENCES `item` (`iid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `C_U` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for great
-- ----------------------------
DROP TABLE IF EXISTS `great`;
CREATE TABLE `great`  (
  `gid` int(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '通知编号（主键）自动分配',
  `iid` int(8) UNSIGNED ZEROFILL NOT NULL COMMENT '收件用户编号',
  `cid` int(8) UNSIGNED ZEROFILL NULL DEFAULT NULL COMMENT '被点赞评论编号（默认为0）',
  `uid` int(8) UNSIGNED ZEROFILL NOT NULL COMMENT '点赞用户',
  `gtime` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '通知时间（自动分配）',
  `gread` int(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '点赞时间被读状态（已读1，未读0（默认））',
  PRIMARY KEY (`gid`) USING BTREE,
  INDEX `G_I`(`iid`) USING BTREE,
  INDEX `G_C`(`cid`) USING BTREE,
  INDEX `G_U`(`uid`) USING BTREE,
  CONSTRAINT `G_C` FOREIGN KEY (`cid`) REFERENCES `comment` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `G_I` FOREIGN KEY (`iid`) REFERENCES `item` (`iid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `G_U` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of great
-- ----------------------------
INSERT INTO `great` VALUES (00000001, 00000001, NULL, 00000001, '2020-04-24 15:36:20', 0);
INSERT INTO `great` VALUES (00000002, 00000001, NULL, 00000002, '2020-04-24 15:37:40', 0);
INSERT INTO `great` VALUES (00000003, 00000001, NULL, 00000003, '2020-04-24 15:37:51', 0);

-- ----------------------------
-- Table structure for handsel
-- ----------------------------
DROP TABLE IF EXISTS `handsel`;
CREATE TABLE `handsel`  (
  `hid` int(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '转增编号（自动生成）',
  `iid` int(8) UNSIGNED ZEROFILL NOT NULL COMMENT '帖编号（外键）',
  `htime` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '激活转赠时间（默认自动分配）',
  PRIMARY KEY (`hid`) USING BTREE,
  INDEX `H_I`(`iid`) USING BTREE,
  CONSTRAINT `H_I` FOREIGN KEY (`iid`) REFERENCES `item` (`iid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for item
-- ----------------------------
DROP TABLE IF EXISTS `item`;
CREATE TABLE `item`  (
  `iid` int(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '帖编号（主键）自动分配',
  `uid` int(8) UNSIGNED ZEROFILL NOT NULL COMMENT '发帖用户编号（外键）',
  `iname` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '帖标题，长度限制在20字以内',
  `iimage` int(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '帖图片,是否有图片，默认为0',
  `iintro` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '帖介绍',
  `itype` int(2) UNSIGNED NOT NULL DEFAULT 2 COMMENT '帖类别（分公告1、图文2、二手3、已结束的二手4）默认为‘2’',
  `itime` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '发布商品的时间',
  `ibrowse` int(5) UNSIGNED NOT NULL DEFAULT 0 COMMENT '浏览量（点击页面及增加，默认为0）',
  PRIMARY KEY (`iid`) USING BTREE,
  INDEX `U_I`(`uid`) USING BTREE,
  CONSTRAINT `U_I` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of item
-- ----------------------------
INSERT INTO `item` VALUES (00000001, 00000001, 'testItem', 0, 'null', 2, '2020-03-30 14:51:32', 0);
INSERT INTO `item` VALUES (00000004, 00000003, 'abc', 0, 'abc', 2, '2020-03-30 14:55:40', 0);
INSERT INTO `item` VALUES (00000005, 00000001, '这是公告', 0, '这是公告', 1, '2020-04-23 17:39:44', 10);
INSERT INTO `item` VALUES (00000006, 00000001, '公告2', 0, '2222', 1, '2020-04-23 17:40:52', 30);
INSERT INTO `item` VALUES (00000007, 00000001, '置顶普通帖', 0, '1111', 2, '2020-04-23 17:41:48', 0);

-- ----------------------------
-- Table structure for key
-- ----------------------------
DROP TABLE IF EXISTS `key`;
CREATE TABLE `key`  (
  `kid` int(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '关键词编号（主键）自动分配',
  `kword` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '关键词',
  `uid` int(8) UNSIGNED ZEROFILL NOT NULL COMMENT '关注者编号(外键)',
  PRIMARY KEY (`kid`) USING BTREE,
  INDEX `K_U`(`uid`) USING BTREE,
  CONSTRAINT `K_U` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for top
-- ----------------------------
DROP TABLE IF EXISTS `top`;
CREATE TABLE `top`  (
  `tid` int(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '置顶位置/编号（主键）自动分配',
  `iid` int(8) UNSIGNED ZEROFILL NOT NULL COMMENT '帖编号（外键）',
  PRIMARY KEY (`tid`) USING BTREE,
  INDEX `T_I`(`iid`) USING BTREE,
  CONSTRAINT `T_I` FOREIGN KEY (`iid`) REFERENCES `item` (`iid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of top
-- ----------------------------
INSERT INTO `top` VALUES (00000003, 00000001);
INSERT INTO `top` VALUES (00000002, 00000006);
INSERT INTO `top` VALUES (00000001, 00000007);

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `uid` int(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT COMMENT '用户编号（主键），自动分配',
  `uname` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名，不允许重复',
  `uno` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户账号，不允许重复，用户登陆用,长度限制在18位以内，仅限英文和数字',
  `upass` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '123456' COMMENT '密码，长度在6位以上18位以内，仅限英文和数字',
  `usex` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '保密' COMMENT '性别【男、女、保密（默认）】',
  `utel` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电话11位以内',
  `uother` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '备注，默认为“这个咸鱼太懒了什么都没写”',
  `uauth` int(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '权限【1，0（默认）】1为管理员，0为普通用户',
  PRIMARY KEY (`uid`) USING BTREE,
  UNIQUE INDEX `uni`(`uname`, `uno`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (00000001, 'Haoo', 'admin', '111111', '男', NULL, '默认的咸鱼管理员\"非常非常咸鱼的那种。不得不说，太咸鱼了。', 1);

-- ----------------------------
-- View structure for topitem
-- ----------------------------
DROP VIEW IF EXISTS `topitem`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `topitem` AS select `item`.`iid` AS `iid`,`item`.`uid` AS `uid`,`item`.`iname` AS `iname`,`item`.`iimage` AS `iimage`,`item`.`iintro` AS `iintro`,`item`.`itype` AS `itype`,`item`.`itime` AS `itime`,`item`.`ibrowse` AS `ibrowse` from (`top` join `item` on((`top`.`iid` = `item`.`iid`))) order by `top`.`tid`;

SET FOREIGN_KEY_CHECKS = 1;