CREATE DATABASE student;

USE student;
CREATE TABLE admin (
                       id INT PRIMARY KEY,
                       username VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                       password VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                       name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                       role varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

);
use student;

insert into admin values(1,'admin','admin','管理员','ADMIN');

CREATE TABLE course (
                        id INT PRIMARY KEY ,
                        name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        no VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        descr VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        times varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        teacher varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);
insert into course values(1,1,1,1,1,1);
# drop table course;
# drop table admin;
# drop table student;

CREATE TABLE student (
                        id INT PRIMARY KEY ,
                        username VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        password VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        phone VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        email varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        sex varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        birth varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        avatar varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        role varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

insert into student values(1,'stu','stu','学生','','','','','','STUDENT');


#学生选课
CREATE TABLE student_course (
                        id INT PRIMARY KEY ,
                        name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        no VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        student_id VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                        course_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

CREATE TABLE grade (
                                id INT PRIMARY KEY ,
                                student_id VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                                course_id varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                                score DECIMAL(20, 1),
                                comment varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
                                feedback varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);