Scribd
Upload
5 views3 pages

MySQL User and Activity Tables

The document outlines the SQL schema for a user management system, including tables for user accounts, messages, mistakes history, practice history, practice details, system settings, hot questions, user awards, and check-in records. Each table includes various fields with specific data types and constraints, such as foreign keys linking to the user account table. The schema is designed to support functionalities like user notifications, tracking practice sessions, and managing user settings.

Uploaded by

youbaosansuicgh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
5 views3 pages

MySQL User and Activity Tables

The document outlines the SQL schema for a user management system, including tables for user accounts, messages, mistakes history, practice history, practice details, system settings, hot questions, user awards, and check-in records. Each table includes various fields with specific data types and constraints, such as foreign keys linking to the user account table. The schema is designed to support functionalities like user notifications, tracking practice sessions, and managing user settings.

Uploaded by

youbaosansuicgh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

use kousuan;

-- 用户表
CREATE TABLE `account` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) NOT NULL COMMENT '用户名',
`password` varchar(255) NOT NULL COMMENT '密码',
`email` varchar(100) DEFAULT NULL COMMENT '邮箱',
`headShot` varchar(255) DEFAULT NULL COMMENT '头像地址',
`class` varchar(50) DEFAULT NULL COMMENT '班级',
`phoneNumber` varchar(20) DEFAULT NULL COMMENT '手机号',
`isHidden` tinyint(1) DEFAULT '0' COMMENT '是否隐藏',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 用户消息表
CREATE TABLE `account_news` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`userId` int NOT NULL COMMENT '外键',
`title` varchar(50) NOT NULL COMMENT '标题',
`content` text COMMENT '内容',
`sendTime` datetime NOT NULL COMMENT '发送时间',
`isRead` tinyint(1) DEFAULT '0' COMMENT '是否已读',
`type` varchar(20) NOT NULL COMMENT '类型',
`isHidden` tinyint(1) DEFAULT '0' COMMENT '是否隐藏',
PRIMARY KEY (`id`),
KEY `userId` (`userId`),
CONSTRAINT `account_news_ibfk_1` FOREIGN KEY
(`userId`) REFERENCES `account` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户消息';

-- 错题本历史表
CREATE TABLE `mistakes_history` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`userId` int NOT NULL COMMENT '外键',
`question` varchar(100) NOT NULL COMMENT '题目',
`userAnswer` varchar(50) COMMENT '用户回答',
`successAnswer` varchar(50) NOT NULL COMMENT '正
确答案',
`dateTime` datetime NOT NULL COMMENT '时间',
`analyse` text COMMENT '分析',
`type` varchar(20) DEFAULT NULL COMMENT '类型',
`errorNums` int DEFAULT '1' COMMENT '错误次数',
PRIMARY KEY (`id`),
KEY `userId` (`userId`),
CONSTRAINT `mistakes_history_ibfk_1` FOREIGN
KEY (`userId`) REFERENCES `account` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='错题本历史';

-- 练习历史表
CREATE TABLE `practice_history` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`userId` int NOT NULL COMMENT '外键',
`label` varchar(100) NOT NULL COMMENT '练习标题',
`dateTime` datetime NOT NULL COMMENT '时间',
`timeSpent` int DEFAULT NULL COMMENT '用时(秒)',
`questionNums` int NOT NULL COMMENT '题目数量',
`correctNums` int NOT NULL COMMENT '正确数量',
`type` varchar(20) DEFAULT NULL COMMENT '练习类型',
`icon` varchar(50) DEFAULT NULL COMMENT '图标',
`difficulty` varchar(20) DEFAULT NULL COMMENT
'难度',
PRIMARY KEY (`id`),
KEY `userId` (`userId`),
CONSTRAINT `practice_history_ibfk_1` FOREIGN
KEY (`userId`) REFERENCES `account` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='练习历史';

-- 练习内容详情表
CREATE TABLE `practice_detail` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`historyId` int NOT NULL COMMENT '外键',
`question` varchar(100) NOT NULL COMMENT '题目',
`userAnswer` varchar(50) COMMENT '用户回答',
`successAnswer` varchar(50) NOT NULL COMMENT '正确
答案',
`answerStatus` tinyint(1) NOT NULL COMMENT '是否正
确',
`type` varchar(20) DEFAULT NULL COMMENT '类型',
PRIMARY KEY (`id`),
KEY `historyId` (`historyId`),
CONSTRAINT `practice_detail_ibfk_1` FOREIGN KEY
(`historyId`) REFERENCES `practice_history` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='练习内容详情';

-- 系统设置表
CREATE TABLE `system_settings` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`userId` int NOT NULL COMMENT '外键',
`soundSwitch` tinyint(1) DEFAULT '1' COMMENT '声音
效果开关',
`shockSwitch` tinyint(1) DEFAULT '1' COMMENT '震动
反馈开关',
`warnSwitch` tinyint(1) DEFAULT '1' COMMENT '每日提
醒开关',
`answerTime` int DEFAULT NULL COMMENT '答题时间',
`questionNums` int DEFAULT NULL COMMENT '每组题目数量',
`level` varchar(20) DEFAULT NULL COMMENT '默认难度等
级',
PRIMARY KEY (`id`),
KEY `userId` (`userId`),
CONSTRAINT `system_settings_ibfk_1` FOREIGN KEY
(`userId`) REFERENCES `account` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统设置表';

-- 热门问题表
CREATE TABLE `hot_questions` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`content` text NOT NULL COMMENT '内容',
`viewCount` int DEFAULT '0' COMMENT '浏览次数',
`createTime` datetime NOT NULL COMMENT '问题添加时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='热门问题表';

-- 用户奖励表
CREATE TABLE `account_award` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`userId` int NOT NULL COMMENT '外键',
`progress` int DEFAULT '0' COMMENT '闯关进度',
`coin` int DEFAULT '0' COMMENT '金币',
`star` int DEFAULT '0' COMMENT '星',
PRIMARY KEY (`id`),
KEY `userId` (`userId`),
CONSTRAINT `account_award_ibfk_1` FOREIGN KEY
(`userId`) REFERENCES `account` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户奖励表';

-- 打卡表
CREATE TABLE `checked_days` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
`userId` int NOT NULL COMMENT '外键',
`month` varchar(7) NOT NULL COMMENT '年月',
`checkedDays` varchar(255) DEFAULT NULL COMMENT '打卡
日期字符串',
PRIMARY KEY (`id`),
KEY `userId` (`userId`),
CONSTRAINT `checked_days_ibfk_1` FOREIGN KEY
(`userId`) REFERENCES `account` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='打卡表';

You might also like