slambb_smartbowgame/SmartBowDatabase2/smart_bow（包含建库、建表和插入必要数据）.sql

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 80020
Source Host           : localhost:3306
Source Database       : smart_bow

Target Server Type    : MYSQL
Target Server Version : 80020
File Encoding         : 65001

Date: 2022-12-15 13:18:41
*/

CREATE DATABASE IF NOT EXISTS smart_bow default charset utf8 COLLATE utf8_general_ci;

use smart_bow;

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for admin_user
-- ----------------------------
DROP TABLE IF EXISTS `admin_user`;
CREATE TABLE `admin_user` (
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `token` varchar(32) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for game10_score
-- ----------------------------
DROP TABLE IF EXISTS `game10_score`;
CREATE TABLE `game10_score` (
  `userID` int NOT NULL,
  `score` int NOT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for game11_score
-- ----------------------------
DROP TABLE IF EXISTS `game11_score`;
CREATE TABLE `game11_score` (
  `userID` int NOT NULL,
  `score` int NOT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for game12_score
-- ----------------------------
DROP TABLE IF EXISTS `game12_score`;
CREATE TABLE `game12_score` (
  `userID` int NOT NULL,
  `score` int NOT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for game9_score
-- ----------------------------
DROP TABLE IF EXISTS `game9_score`;
CREATE TABLE `game9_score` (
  `userID` int NOT NULL,
  `score` int NOT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for game_channel_login
-- ----------------------------
DROP TABLE IF EXISTS `game_channel_login`;
CREATE TABLE `game_channel_login` (
  `gameID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `channelID` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `userID` int NOT NULL,
  `loginTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`gameID`,`channelID`,`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for mac_663axis
-- ----------------------------
DROP TABLE IF EXISTS `mac_663axis`;
CREATE TABLE `mac_663axis` (
  `mac` varchar(64) NOT NULL,
  `attitude` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `saveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '保存时间',
  PRIMARY KEY (`mac`),
  UNIQUE KEY `mac` (`mac`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for mac_9axis
-- ----------------------------
DROP TABLE IF EXISTS `mac_9axis`;
CREATE TABLE `mac_9axis` (
  `mac` varchar(64) NOT NULL,
  `attitude` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `saveTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '保存时间',
  PRIMARY KEY (`mac`),
  UNIQUE KEY `mac` (`mac`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for mac_calibrate
-- ----------------------------
DROP TABLE IF EXISTS `mac_calibrate`;
CREATE TABLE `mac_calibrate` (
  `mac` varchar(64) NOT NULL,
  `gyr` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `mag` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`mac`),
  UNIQUE KEY `mac` (`mac`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for room_game_record
-- ----------------------------
DROP TABLE IF EXISTS `room_game_record`;
CREATE TABLE `room_game_record` (
  `roomKey` varchar(32) NOT NULL,
  `gameType` int NOT NULL,
  `p1ID` int NOT NULL,
  `p2ID` int NOT NULL,
  `gameRes` int NOT NULL,
  `settleTime` bigint NOT NULL,
  `playerIDSeq` varchar(32) NOT NULL,
  PRIMARY KEY (`roomKey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_day_active
-- ----------------------------
DROP TABLE IF EXISTS `user_day_active`;
CREATE TABLE `user_day_active` (
  `id` int NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_friend
-- ----------------------------
DROP TABLE IF EXISTS `user_friend`;
CREATE TABLE `user_friend` (
  `myID` int NOT NULL,
  `friendID` int NOT NULL,
  PRIMARY KEY (`myID`,`friendID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_friend_request
-- ----------------------------
DROP TABLE IF EXISTS `user_friend_request`;
CREATE TABLE `user_friend_request` (
  `myID` int NOT NULL,
  `otherID` int NOT NULL,
  PRIMARY KEY (`myID`,`otherID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_game_record
-- ----------------------------
DROP TABLE IF EXISTS `user_game_record`;
CREATE TABLE `user_game_record` (
  `userID` int NOT NULL,
  `dateTime` datetime NOT NULL,
  `gameType` int NOT NULL,
  `duration` int NOT NULL,
  PRIMARY KEY (`userID`,`dateTime`),
  KEY `userID` (`userID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_info
-- ----------------------------
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `id` int NOT NULL,
  `avatarID` int NOT NULL,
  `nickname` varchar(16) NOT NULL,
  `gender` int NOT NULL,
  `phone` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `email` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `birthday` varchar(10) NOT NULL,
  `country` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `state` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `city` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `integral` int NOT NULL,
  `coin` int NOT NULL,
  `diamond` int NOT NULL,
  `mac` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `bagList` varchar(2048) NOT NULL,
  `deviceList` varchar(2048) NOT NULL,
  `timeLimitGameScores` varchar(255) NOT NULL,
  `challengeLevels` varchar(255) NOT NULL,
  `offlineTime` bigint NOT NULL DEFAULT '0',
  `guideRecord` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '占位表示对应的新手引导是否完成',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for user_login
-- ----------------------------
DROP TABLE IF EXISTS `user_login`;
CREATE TABLE `user_login` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `token` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `token_timeout` bigint NOT NULL,
  `serverIndex` int NOT NULL DEFAULT '0',
  `regTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`) USING BTREE,
  UNIQUE KEY `id` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

-- ----------------------------
-- 后台管理插入管理员
-- ----------------------------
INSERT INTO `smart_bow`.`admin_user` (`username`, `password`, `token`) VALUES ('admin', '123456', '86df6484ee6c425998b289faab27eee6');