在线考试系统（二）——数据库设计

小鱼儿 2023-06-16 10:37 24阅读 0赞

### 在线考试系统（二）——数据库设计 ###

*   *   *  表结构
         *  创建数据库
         *  创建表
         *  执行sql文件
         *  后期修改

### 表结构 ###

原书上表结构，我在这张表的基础上进行了修改  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTQ2NjUw_size_16_color_FFFFFF_t_70]

### 创建数据库 ###

参考[vscode 下使用mysql][vscode _mysql]

create database online_exam;

### 创建表 ###

use online_exam;
    
    
    -- 管理员表
    CREATE TABLE IF NOT EXISTS `admin` (
      `aid` INT UNSIGNED NOT NULL comment '管理员账号',
      `pwd`  varchar(20) not null comment '管理员密码',
      PRIMARY KEY `pk_id`(`aid`)
    ) ENGINE = InnoDB;
    
    
    -- 学生班级表
    CREATE TABLE IF NOT EXISTS `class` (
      `classid` INT UNSIGNED NOT NULL auto_increment comment '班级编号',
      `term` int not null comment '入学年份',
      `institute` varchar(100) not null comment '学院',
      `major` varchar(100) not null comment '专业',
      `classno` int not null comment '具体班级',
      PRIMARY KEY `pk_classid`(`classid`)
    ) ENGINE = InnoDB;
    
    -- 学生信息表
    CREATE TABLE IF NOT EXISTS `student` (
      `studentId` INT UNSIGNED NOT NULL  comment '学号',
      `name` VARCHAR(50) not null comment '姓名',
      `pwd` varchar(50) not null comment '密码',
      `email` varchar(50) not null comment '邮箱',
      `phone` varchar(20) not null comment '联系电话',
      `sex` char(2) not null comment '性别',
      `dob` date not null comment '出生年月',
      `classid` int  not null comment '班级',
      PRIMARY KEY `pk_studentId`(`studentId`)                   
    ) ENGINE = InnoDB;
    
    
    -- 老师信息表
    CREATE TABLE IF NOT EXISTS `teacher` (
      `teacherid` INT UNSIGNED NOT NULL comment '职工编号',
      `name` VARCHAR(50) not null comment '姓名',
      `pwd` varchar(50) not null comment '密码',
      `email` varchar(50) not null comment '邮箱',
      `phone` varchar(20) not null comment '联系电话',
      `sex` char(2) not null comment '性别',
      `dob` date not null comment '出生年月',
      PRIMARY KEY `pk_teacherid`(`teacherid`)
    ) ENGINE = InnoDB;
    
    -- 老师课程表
    CREATE TABLE IF NOT EXISTS `course` (
      `cid` INT UNSIGNED NOT NULL AUTO_INCREMENT comment '课程编号',
      `name` VARCHAR(50) not null comment '名称',
      `teacherid` INT  NOT NULL comment '发布职工编号',
      `authcode` varchar(20) not null comment '课程创建码',
      PRIMARY KEY `pk_id`(`cid`)
    ) ENGINE = InnoDB;
    
    -- 学生选课表
    CREATE TABLE IF NOT EXISTS `scourse` (
      `cid` INT  NOT NULL comment '课程编号',
      `studentId` int not null comment '学生学号',
      constraint pk_stu_c primary key(`cid`,'studentId')
    ) ENGINE = InnoDB;
    
    -- 考试信息表
    CREATE TABLE IF NOT EXISTS `exam` (
      `eid` INT UNSIGNED NOT NULL AUTO_INCREMENT comment '考试编号',
      `cid` INT  NOT NULL comment '课程编号',
      `startdate` date not null comment '开始时间',
      `enddate` date not null comment '结束时间',
      `instruction` text comment '备注',
      PRIMARY KEY `pk_eid`(`eid`)
    ) ENGINE = InnoDB;
    
    
    -- 单选题
    CREATE TABLE IF NOT EXISTS `single` (
      `singleid` INT UNSIGNED NOT NULL AUTO_INCREMENT comment '单选题编号',
      `eid` INT  NOT NULL  comment '隶属考试编号',
      `option1` text comment '选项1',
      `option2` text comment '选项2',
      `option3` text comment '选项3',
      `option4` text comment '选项4',
      `answer` tinyint not null comment '答案',
      PRIMARY KEY `pk_singleid`(`singleid`)
    ) ENGINE = InnoDB;
    
    -- 多选题
    CREATE TABLE IF NOT EXISTS `mcq` (
      `mid` INT UNSIGNED NOT NULL AUTO_INCREMENT comment '多选题编号',
      `eid` INT  NOT NULL  comment '隶属考试编号',
      `option1` text comment '选项1',
      `option2` text comment '选项2',
      `option3` text comment '选项3',
      `option4` text comment '选项4',
      `answer` varchar(4) not null comment '答案',
      PRIMARY KEY `pk_mid`(`mid`)
    ) ENGINE = InnoDB;
    
    -- 判断题
    CREATE TABLE IF NOT EXISTS `judge` (
      `jid` INT UNSIGNED NOT NULL AUTO_INCREMENT comment '判断题编号',
      `eid` INT  NOT NULL  comment '隶属考试编号',
      `content` text not null comment '判断题内容',
      `answer` char(1) not null comment   '答案',
      PRIMARY KEY `pk_jid`(`jid`)
    ) ENGINE = InnoDB;
    
    
    -- 成绩表
    CREATE TABLE IF NOT EXISTS `score` (
      `scid` int unsigned not null auto_increment comment '提交编号',
      `eid` INT  NOT NULL comment '考试编号',
      `studentId` int not null comment '学生学号',
      `sco` tinyint not null comment '考试成绩' ,
      `time` date not null comment '提交时间',
      PRIMARY KEY `pk_scid`(`scid`)
    ) ENGINE = InnoDB;

### 执行sql文件 ###

*  参考在命令行执行

source sql文件绝对路径

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTQ2NjUw_size_16_color_FFFFFF_t_70 1]

### 后期修改 ###

alter table class rename stuClass;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTQ2NjUw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019112520545854.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTQ2NjUw,size_16,color_FFFFFF,t_70
[vscode _mysql]: https://blog.csdn.net/qq_41146650/article/details/103202832
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTQ2NjUw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191125230710867.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMTQ2NjUw,size_16,color_FFFFFF,t_70