remaker01 / stugrademanagement Goto Github PK

create table student
(
    id      int auto_increment
        primary key,
    sname   varchar(255)               not null,
    gender  varchar(255)               not null,
    address varchar(255)               null,
    age     tinyint unsigned           not null,
    qq      varchar(10) charset latin1 null,
    phone   varchar(11) charset latin1 null
) AUTO_INCREMENT = 2000 CHARACTER SET = utf8mb4;

create table users
(
    id         int auto_increment primary key,
    username   varchar(255) not null,
    `password` varchar(100) not null,
    isadmin    tinyint(1) unsigned default 0 null,
    last_err_time bigint null,
    err_count     int default 0 null
) AUTO_INCREMENT = 1000 CHARACTER SET = utf8mb4;

CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `cname` varchar(10) CHARACTER SET utf8mb4 NOT NULL,
  `ctype` varchar(10) CHARACTER SET utf8mb4 NOT NULL COMMENT '课程类型：公共课、专业基础课、专业课、选修课',
  `teacher` int(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  CONSTRAINT `TEA_ID` FOREIGN KEY (`teacher`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE
) CHARACTER SET = utf8mb4;

create table grade
(
   stuid    int              not null
      primary key,
   courseid int              not null,
   score    tinyint unsigned null comment '分数为空代表考试异常',
   constraint COURSE_ID
      foreign key (courseid) references course (id)
         on update cascade,
   constraint STU_ID
      foreign key (stuid) references student (id)
         on update cascade
);