exam
/
ac_exam.sql

-- phpMyAdmin SQL Dump
-- version 4.9.0.1
-- https://www.phpmyadmin.net/
--
-- 主机： 172.16.1.5
-- 生成日期： 2021-08-05 07:39:46
-- 服务器版本： 5.7.33-log
-- PHP 版本： 7.3.27

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- 数据库： `ac_exam`
--
CREATE DATABASE IF NOT EXISTS `ac_exam` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `ac_exam`;

-- --------------------------------------------------------

--
-- 表的结构 `account_departments`
--

CREATE TABLE `account_departments` (
  `id` int(10) UNSIGNED NOT NULL,
  `pid` int(10) UNSIGNED NOT NULL,
  `first` int(10) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL COMMENT '部门名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

-- --------------------------------------------------------

--
-- 表的结构 `account_dutys`
--

CREATE TABLE `account_dutys` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(100) NOT NULL COMMENT '职务名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='职务表';

-- --------------------------------------------------------

--
-- 表的结构 `account_mark_examination_papers`
--

CREATE TABLE `account_mark_examination_papers` (
  `id` int(10) UNSIGNED NOT NULL,
  `account` int(10) UNSIGNED NOT NULL,
  `type` varchar(10) NOT NULL COMMENT 'department -- 部门  user -- 用户',
  `binding_id` int(10) UNSIGNED NOT NULL COMMENT '部门或者用户id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- 表的结构 `account_users`
--

CREATE TABLE `account_users` (
  `id` int(10) UNSIGNED NOT NULL,
  `uid` varchar(20) NOT NULL COMMENT '用户编码',
  `openid` varchar(100) NOT NULL DEFAULT '',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `password` varchar(40) NOT NULL COMMENT '密码',
  `gender` tinyint(3) NOT NULL COMMENT '性别',
  `birthday` int(10) UNSIGNED NOT NULL COMMENT '生日',
  `avatar` varchar(500) NOT NULL COMMENT '头像',
  `department` int(10) UNSIGNED NOT NULL COMMENT '部门',
  `duty` int(10) UNSIGNED NOT NULL COMMENT '职务',
  `company` varchar(150) NOT NULL,
  `phone` varchar(11) NOT NULL COMMENT '手机号',
  `card_type` varchar(50) NOT NULL COMMENT '证件类型',
  `card_num` varchar(100) NOT NULL COMMENT '证件号',
  `role` varchar(10) NOT NULL COMMENT 'root -- 超管 manager -- 管理用户  user -- 普通用户',
  `status` varchar(10) NOT NULL COMMENT 'normal -- 正常  disabled  -- 禁用',
  `founder` int(10) UNSIGNED NOT NULL COMMENT '创建人账号',
  `ip` varchar(100) NOT NULL,
  `exam_type` varchar(200) NOT NULL COMMENT '评卷类型',
  `privileges` text NOT NULL COMMENT '角色菜单',
  `time_created` int(10) NOT NULL,
  `salt` char(8) NOT NULL COMMENT '加密言'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

--
-- 转存表中的数据 `account_users`
--

INSERT INTO `account_users` (`id`, `uid`, `openid`, `name`, `password`, `gender`, `birthday`, `avatar`, `department`, `duty`, `company`, `phone`, `card_type`, `card_num`, `role`, `status`, `founder`, `ip`, `exam_type`, `privileges`, `time_created`, `salt`) VALUES
(1, 'root', '', 'root', 'b37b7ea2f44d21e15c4ba851a56af958018ac3ef', 1, 0, 'image/a3/2976395abda643c4615b4846bd81ef.png', 0, 0, '', '', '', '', 'root', 'normal', 0, '', '', '', 0, 'tY6yh1');

-- --------------------------------------------------------

--
-- 表的结构 `ac_attachments`
--

CREATE TABLE `ac_attachments` (
  `id` int(11) UNSIGNED NOT NULL,
  `resource` varchar(500) NOT NULL COMMENT '资源地址',
  `title` varchar(100) NOT NULL,
  `time_created` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='附件/媒体';

-- --------------------------------------------------------

--
-- 表的结构 `ac_config`
--

CREATE TABLE `ac_config` (
  `key` varchar(50) NOT NULL,
  `value` varchar(2000) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- 表的结构 `book_chapter`
--

CREATE TABLE `book_chapter` (
  `id` int(10) UNSIGNED NOT NULL,
  `subject_id` int(10) UNSIGNED NOT NULL COMMENT '科目id',
  `volume_id` int(10) UNSIGNED NOT NULL COMMENT '章id',
  `caption` varchar(200) NOT NULL COMMENT '标题',
  `content` longtext NOT NULL,
  `source` varchar(2000) NOT NULL COMMENT '附件资源',
  `view` int(10) UNSIGNED NOT NULL COMMENT '点击量',
  `founder` varchar(20) NOT NULL COMMENT '发布人',
  `time_created` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='电子书节表';

-- --------------------------------------------------------

--
-- 表的结构 `book_volume`
--

CREATE TABLE `book_volume` (
  `id` int(10) UNSIGNED NOT NULL,
  `subject_id` int(10) UNSIGNED NOT NULL COMMENT '科目id',
  `title` varchar(200) NOT NULL COMMENT '电子书章名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='电子书章表';

-- --------------------------------------------------------

--
-- 表的结构 `exam_knowledge`
--

CREATE TABLE `exam_knowledge` (
  `id` int(10) UNSIGNED NOT NULL,
  `subject_id` int(10) UNSIGNED NOT NULL COMMENT '科目id',
  `title` varchar(200) NOT NULL COMMENT '知识点标题'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='知识点表';

-- --------------------------------------------------------

--
-- 表的结构 `exam_papers`
--

CREATE TABLE `exam_papers` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(50) NOT NULL COMMENT '试卷名称',
  `type` varchar(30) NOT NULL COMMENT '试卷类型 competition -- 竞赛级 promote -- 提升级  beginner -- 入门级  simulate  --  模拟考',
  `mode` varchar(20) NOT NULL COMMENT '组卷方式 random -- 随机组卷  hand -- 手工组卷',
  `way` varchar(20) NOT NULL COMMENT '出题方式 fixed -- 题序固定  random -- 题序随机  question -- 试题随机',
  `display` tinyint(2) UNSIGNED NOT NULL COMMENT '1 -- 整卷 2 -- 逐题',
  `time` int(10) UNSIGNED NOT NULL COMMENT '答题时间',
  `total_points` decimal(10,2) UNSIGNED NOT NULL COMMENT '总分',
  `through_points` decimal(10,2) UNSIGNED NOT NULL COMMENT '通过分数',
  `definition` tinyint(2) UNSIGNED NOT NULL COMMENT '分数定义  1 --  使用试卷定义分值  2 -- 使用题库定义分值',
  `multiple` tinyint(2) UNSIGNED NOT NULL COMMENT '允许多次考试  0 -- 不允许  1 -- 允许',
  `result` tinyint(2) UNSIGNED NOT NULL COMMENT '允许查看结果  0 -- 不允许  1 -- 允许',
  `completion` tinyint(2) UNSIGNED NOT NULL COMMENT '允许填空类自动判卷  0 -- 不允许  1 -- 允许 ',
  `save` int(10) UNSIGNED NOT NULL COMMENT '自动保存答卷时长',
  `strategies` longtext NOT NULL COMMENT '策略',
  `question_type` longtext NOT NULL COMMENT '题型设置',
  `question` longtext NOT NULL,
  `time_created` int(10) UNSIGNED NOT NULL,
  `time_start` int(10) UNSIGNED NOT NULL,
  `time_end` int(10) UNSIGNED NOT NULL,
  `founder` int(10) UNSIGNED NOT NULL,
  `watch` varchar(2000) NOT NULL COMMENT '浏览账号',
  `mark` varchar(1000) NOT NULL COMMENT '批卷账号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='试卷表';

-- --------------------------------------------------------

--
-- 表的结构 `exam_paper_mark`
--

CREATE TABLE `exam_paper_mark` (
  `id` int(10) UNSIGNED NOT NULL,
  `paper_id` int(10) UNSIGNED NOT NULL,
  `type` varchar(20) NOT NULL,
  `binding_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='试卷评卷人员';

-- --------------------------------------------------------

--
-- 表的结构 `exam_paper_records`
--

CREATE TABLE `exam_paper_records` (
  `id` int(10) UNSIGNED NOT NULL,
  `paper_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `question` text NOT NULL,
  `status` varchar(10) NOT NULL COMMENT 'start -- 开始答题 end -- 答题结束',
  `total_points` decimal(10,2) NOT NULL COMMENT '总分',
  `score` decimal(10,2) NOT NULL COMMENT '得分',
  `result` longtext NOT NULL COMMENT '试卷结果',
  `mark_id` int(10) UNSIGNED NOT NULL COMMENT '评卷人',
  `mark_status` varchar(10) NOT NULL COMMENT '评卷状态',
  `exam_time` int(10) UNSIGNED NOT NULL COMMENT '答题时长',
  `time_beat` int(10) UNSIGNED NOT NULL,
  `time_end` int(10) UNSIGNED NOT NULL COMMENT '答题完成时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- 表的结构 `exam_paper_watch`
--

CREATE TABLE `exam_paper_watch` (
  `id` int(10) UNSIGNED NOT NULL,
  `paper_id` int(10) UNSIGNED NOT NULL,
  `type` varchar(20) NOT NULL,
  `binding_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='试卷查看人员';

-- --------------------------------------------------------

--
-- 表的结构 `exam_questions`
--

CREATE TABLE `exam_questions` (
  `id` int(11) NOT NULL,
  `name` varchar(500) NOT NULL COMMENT '题目',
  `options` varchar(500) NOT NULL COMMENT '选项',
  `answer` text NOT NULL COMMENT '参考答案',
  `type` varchar(50) NOT NULL COMMENT '类型',
  `score` decimal(6,2) UNSIGNED DEFAULT NULL COMMENT '分值',
  `founder` int(10) UNSIGNED NOT NULL COMMENT '出题人',
  `difficulty_level` varchar(20) NOT NULL COMMENT '难度',
  `knowledge` int(10) UNSIGNED NOT NULL COMMENT '知识点',
  `subject` int(10) UNSIGNED NOT NULL COMMENT '科目',
  `analysis` varchar(500) DEFAULT NULL COMMENT '解析',
  `created_time` int(10) UNSIGNED NOT NULL COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- 表的结构 `exam_subjects`
--

CREATE TABLE `exam_subjects` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(100) NOT NULL COMMENT '科目名称',
  `watch` varchar(2000) NOT NULL COMMENT '浏览账号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='科目表';

-- --------------------------------------------------------

--
-- 表的结构 `exam_subject_watch`
--

CREATE TABLE `exam_subject_watch` (
  `id` int(10) UNSIGNED NOT NULL,
  `subject_id` int(10) UNSIGNED NOT NULL,
  `type` varchar(10) NOT NULL COMMENT 'department -- 部门  user -- 用户',
  `binding_id` int(10) UNSIGNED NOT NULL COMMENT '部门或者用户id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- 表的结构 `news`
--

CREATE TABLE `news` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL COMMENT '标题',
  `content` longtext NOT NULL COMMENT '内容',
  `view` int(10) UNSIGNED NOT NULL COMMENT '点击量',
  `founder` varchar(20) NOT NULL COMMENT '发布人',
  `time_created` int(10) UNSIGNED NOT NULL,
  `watch` varchar(2000) NOT NULL COMMENT '查看账号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='新闻表';

-- --------------------------------------------------------

--
-- 表的结构 `news_watch`
--

CREATE TABLE `news_watch` (
  `id` int(10) UNSIGNED NOT NULL,
  `news_id` int(10) UNSIGNED NOT NULL,
  `type` varchar(10) NOT NULL COMMENT 'department -- 部门  user  -- 人员',
  `binding_id` int(10) UNSIGNED NOT NULL COMMENT '关联id  部门id或者人员id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='新闻可查看表';

--
-- 转储表的索引
--

--
-- 表的索引 `account_departments`
--
ALTER TABLE `account_departments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `title` (`pid`,`title`),
  ADD KEY `pid` (`pid`),
  ADD KEY `first` (`first`);

--
-- 表的索引 `account_dutys`
--
ALTER TABLE `account_dutys`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `title` (`title`);

--
-- 表的索引 `account_mark_examination_papers`
--
ALTER TABLE `account_mark_examination_papers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `account` (`account`),
  ADD KEY `type` (`type`);

--
-- 表的索引 `account_users`
--
ALTER TABLE `account_users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uid` (`uid`),
  ADD KEY `name` (`name`),
  ADD KEY `gender` (`gender`),
  ADD KEY `department` (`department`),
  ADD KEY `duty` (`duty`),
  ADD KEY `phone` (`phone`),
  ADD KEY `role` (`role`),
  ADD KEY `status` (`status`),
  ADD KEY `founder` (`founder`),
  ADD KEY `timecreated` (`time_created`);

--
-- 表的索引 `ac_attachments`
--
ALTER TABLE `ac_attachments`
  ADD PRIMARY KEY (`id`);

--
-- 表的索引 `ac_config`
--
ALTER TABLE `ac_config`
  ADD PRIMARY KEY (`key`);

--
-- 表的索引 `book_chapter`
--
ALTER TABLE `book_chapter`
  ADD PRIMARY KEY (`id`),
  ADD KEY `subject` (`subject_id`),
  ADD KEY `volume` (`volume_id`);

--
-- 表的索引 `book_volume`
--
ALTER TABLE `book_volume`
  ADD PRIMARY KEY (`id`),
  ADD KEY `subject` (`subject_id`);

--
-- 表的索引 `exam_knowledge`
--
ALTER TABLE `exam_knowledge`
  ADD PRIMARY KEY (`id`),
  ADD KEY `subject` (`subject_id`),
  ADD KEY `title` (`title`);

--
-- 表的索引 `exam_papers`
--
ALTER TABLE `exam_papers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `type` (`type`),
  ADD KEY `mode` (`mode`),
  ADD KEY `way` (`way`),
  ADD KEY `time` (`time`),
  ADD KEY `definition` (`definition`),
  ADD KEY `multiple` (`multiple`),
  ADD KEY `result` (`result`),
  ADD KEY `completion` (`completion`),
  ADD KEY `save` (`save`),
  ADD KEY `time_created` (`time_created`),
  ADD KEY `time_end` (`time_end`),
  ADD KEY `time_start` (`time_start`),
  ADD KEY `display` (`display`),
  ADD KEY `founder` (`founder`),
  ADD KEY `question` (`question`(768));

--
-- 表的索引 `exam_paper_mark`
--
ALTER TABLE `exam_paper_mark`
  ADD PRIMARY KEY (`id`),
  ADD KEY `paper_id` (`paper_id`),
  ADD KEY `type` (`type`),
  ADD KEY `binding_id` (`binding_id`);

--
-- 表的索引 `exam_paper_records`
--
ALTER TABLE `exam_paper_records`
  ADD PRIMARY KEY (`id`),
  ADD KEY `paper_id` (`paper_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `status` (`status`),
  ADD KEY `mark_id` (`mark_id`),
  ADD KEY `mark_status` (`mark_status`),
  ADD KEY `question` (`question`(768));

--
-- 表的索引 `exam_paper_watch`
--
ALTER TABLE `exam_paper_watch`
  ADD PRIMARY KEY (`id`),
  ADD KEY `paper_id` (`paper_id`),
  ADD KEY `type` (`type`),
  ADD KEY `binding_id` (`binding_id`);

--
-- 表的索引 `exam_questions`
--
ALTER TABLE `exam_questions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `difficulty_level` (`difficulty_level`);

--
-- 表的索引 `exam_subjects`
--
ALTER TABLE `exam_subjects`
  ADD PRIMARY KEY (`id`),
  ADD KEY `title` (`title`);
ALTER TABLE `exam_subjects` ADD FULLTEXT KEY `watch` (`watch`);

--
-- 表的索引 `exam_subject_watch`
--
ALTER TABLE `exam_subject_watch`
  ADD PRIMARY KEY (`id`),
  ADD KEY `subject_id` (`subject_id`),
  ADD KEY `type` (`type`);

--
-- 表的索引 `news`
--
ALTER TABLE `news`
  ADD PRIMARY KEY (`id`),
  ADD KEY `title` (`title`);
ALTER TABLE `news` ADD FULLTEXT KEY `watch` (`watch`);

--
-- 表的索引 `news_watch`
--
ALTER TABLE `news_watch`
  ADD PRIMARY KEY (`id`),
  ADD KEY `news_id` (`news_id`),
  ADD KEY `type` (`type`),
  ADD KEY `binding_id` (`binding_id`);

--
-- 在导出的表使用AUTO_INCREMENT
--

--
-- 使用表AUTO_INCREMENT `account_departments`
--
ALTER TABLE `account_departments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `account_dutys`
--
ALTER TABLE `account_dutys`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `account_mark_examination_papers`
--
ALTER TABLE `account_mark_examination_papers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `account_users`
--
ALTER TABLE `account_users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- 使用表AUTO_INCREMENT `ac_attachments`
--
ALTER TABLE `ac_attachments`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `book_chapter`
--
ALTER TABLE `book_chapter`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `book_volume`
--
ALTER TABLE `book_volume`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `exam_knowledge`
--
ALTER TABLE `exam_knowledge`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `exam_papers`
--
ALTER TABLE `exam_papers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `exam_paper_mark`
--
ALTER TABLE `exam_paper_mark`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `exam_paper_records`
--
ALTER TABLE `exam_paper_records`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `exam_paper_watch`
--
ALTER TABLE `exam_paper_watch`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `exam_questions`
--
ALTER TABLE `exam_questions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `exam_subjects`
--
ALTER TABLE `exam_subjects`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `exam_subject_watch`
--
ALTER TABLE `exam_subject_watch`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `news`
--
ALTER TABLE `news`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- 使用表AUTO_INCREMENT `news_watch`
--
ALTER TABLE `news_watch`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;