Java后台-2.创建数据库和表

创建用户表

CREATE TABLE `beautiful_box_system`.`tb_user`  (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `nick_name` varchar(32) NOT NULL COMMENT '昵称',
  `password` varchar(64) NOT NULL COMMENT '密码',
  `signature` varchar(64) NOT NULL COMMENT '签名',
  `avatar` varchar(1024) NOT NULL COMMENT '头像',
  `mobile_number` int(16) NOT NULL COMMENT '手机号码',
  `email` varchar(64) NOT NULL COMMENT '邮箱',
  `is_forbidden` int(1) NOT NULL COMMENT '是否被禁用 1：正常 0：禁用',
  `reg_ip` varchar(32) NOT NULL COMMENT '注册ip',
  `login_ip` varchar(32) NOT NULL COMMENT '登录ip',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `last_login_time` datetime(0) NOT NULL COMMENT '最后登录时间',
  `birthday` date NOT NULL COMMENT '生日',
  `gender` int(1) NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建角色表

CREATE TABLE `beautiful_box_system`.`tb_role`  (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `role_name` varchar(64) NOT NULL COMMENT '角色名称',
  `role_desc` varchar(64) NOT NULL COMMENT '角色说明',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建权限表

CREATE TABLE `beautiful_box_system`.`tb_permission`  (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `perm_name` varchar(64) NOT NULL COMMENT '权限名称',
  `perm_desc` varchar(64) NOT NULL COMMENT '权限说明',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建用户角色关系表

CREATE TABLE `beautiful_box_system`.`tb_user_role`  (
  `uid` bigint(32) NOT NULL COMMENT '用户id',
  `rid` bigint(32) NOT NULL COMMENT '角色id'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建角色权限关系表

CREATE TABLE `beautiful_box_system`.`tb_role_permission`  (
  `rid` bigint(32) NOT NULL COMMENT '角色id',
  `pid` bigint(32) NOT NULL COMMENT '权限id'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建refreshToken表

CREATE TABLE `beautiful_box_system`.`tb_refresh_token`  (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `refresh_token` text NOT NULL COMMENT '时效token值',
  `token_key` varchar(32) NOT NULL COMMENT '时效token的键',
  `user_id` bigint(32) NOT NULL COMMENT '用户id',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_user_token_on_user_id` FOREIGN KEY (`user_id`) REFERENCES `beautiful_box_system`.`tb_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建商品类型表

CREATE TABLE `beautiful_box_system`.`tb_categories`  (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `name` varchar(64) NOT NULL COMMENT '类型名称',
  `desc` text NOT NULL COMMENT '说明',
  `is_forbidden` int(1) NOT NULL COMMENT '是否被禁用',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建商品表

CREATE TABLE `beautiful_box_system`.`tb_goods`  (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `summary` text NOT NULL COMMENT '简要说明',
  `user_id` bigint(32) NOT NULL COMMENT '用户id',
  `is_sell_out` int(1) NOT NULL COMMENT '是否售空',
  `price` double(32, 2) NOT NULL COMMENT '价格',
  `content` mediumtext NOT NULL COMMENT '内容',
  `sell_count` bigint(255) NOT NULL COMMENT '销售量',
  `category_id` bigint(32) NOT NULL COMMENT '类型id',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_user_goods_on_user_id` FOREIGN KEY (`user_id`) REFERENCES `beautiful_box_system`.`tb_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_categories_goods_on_category_id` FOREIGN KEY (`category_id`) REFERENCES `beautiful_box_system`.`tb_categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建标签表

CREATE TABLE `beautiful_box_system`.`tb_labels`  (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `name` varchar(32) NOT NULL COMMENT '名称',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建评论表

CREATE TABLE `beautiful_box_system`.`tb_comment`  (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `goods_id` bigint(32) NOT NULL COMMENT '商品id',
  `content` text NOT NULL COMMENT '评论内容',
  `user_id` bigint(32) NOT NULL COMMENT '用户id',
  `user_nick_name` varchar(32) NOT NULL COMMENT '用户昵称',
  `user_avatar` varchar(1024) NOT NULL COMMENT '用户头像',
  `is_delete` int(1) NOT NULL COMMENT '是否删除 1：删除 0：未删除',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_goods_comment_on_goods_id` FOREIGN KEY (`goods_id`) REFERENCES `beautiful_box_system`.`tb_goods` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_comment_on_user_id` FOREIGN KEY (`user_id`) REFERENCES `beautiful_box_system`.`tb_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建广告轮播图表

CREATE TABLE `beautiful_box_system`.`tb_looper`  (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `title` varchar(128) NOT NULL COMMENT '标题',
  `target_url` varchar(1024) NOT NULL COMMENT '跳转url',
  `image_url` varchar(1024) NOT NULL COMMENT '显示图片url',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  `off_the_shelf` int(1) NOT NULL COMMENT '是否下架 1：下架 0：正常使用',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

创建设置表

CREATE TABLE `beautiful_box_system`.`tb_settings`  (
  `id` bigint(32) NOT NULL COMMENT 'ID',
  `key` varchar(32) NOT NULL COMMENT '设置项的key',
  `value` varchar(512) NOT NULL COMMENT '设置项的值',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `update_time` datetime(0) NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;