Create table School   -- 院系表
(
    yxh char(4)  not null,
    mc varchar(50) not null,
    primary key (yxh)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create table Student
(
    xh char(8) not null,
    xm varchar(20) not null,
    yxh char(4) not null,
    mm char(40) not null,
    jd float,
    primary key (xh),
    foreign key (yxh) references School (yxh)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create table Teacher
(
    gh char(8) not null,
    xm varchar(20) not null,
    yxh char(4) not null,
    mm char(40) not null,
    primary key (gh),
    foreign key (yxh) references School(yxh)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create table CourseTeach -- 课程-老师表
(
    id int AUTO_INCREMENT primary key,
    kh char(8) not null,
    gh char(8) not null references Teacher(gh),
    time varchar(100) not null,
    bittime char(65) not null,
    room varchar(50) not null,
    maxnum int not null,
    enrollnum int not null default '0',
    lck boolean not null default '0',
    percent float not null,
    km varchar(20) not null,
    xf int not null,
    yxh char(4) not null references School(yxh)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create table grade
(
    xh char(8) not null references Student(xh),
    id int references CourseTeach(id),
    pscj int,
    kscj int,
    zpcj int,
    primary key (xh,id),
    check (pscj between 0 and 100),
    check (kscj between 0 and 100),
    check (zpcj between 0 and 100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create table history -- 删选记录
(
    i int AUTO_INCREMENT primary key,
    xh char(8) not null references Student(xh),
    id int references CourseTeach(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create table admin
(
    k varachar(20) primary key,
    v text not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- 存储过程 管理员 筛选学生
DELIMITER $$
DROP PROCEDURE IF EXISTS `ShaiXuan`$$
CREATE PROCEDURE ShaiXuan()
BEGIN
    DECLARE i_id INT;
    DECLARE cap INT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT id,maxnum FROM CourseTeach WHERE maxnum<=enrollnum;
    DECLARE EXIT HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    del: LOOP
        FETCH cur INTO i_id,cap;
        IF done THEN
            LEAVE del;
        END IF;
        BEGIN
            DELETE FROM grade WHERE grade.xh NOT IN ( SELECT tmp.x FROM (
                SELECT grade.xh AS x FROM grade,student WHERE student.xh=grade.xh AND grade.id=i_id
                ORDER BY substr(grade.xh,1,2) ASC,jd DESC LIMIT cap ) AS tmp
            );
            UPDATE CourseTeach SET lck=1,enrollnum=maxnum WHERE id=i_id;
        END;
    END LOOP;
    CLOSE cur;
END$$
DELIMITER ;

-- 存储过程 检查是否在选课时间内
DELIMITER $$
DROP PROCEDURE IF EXISTS `EnrollTime`$$
CREATE PROCEDURE EnrollTime(OUT buer INT)
BEGIN
    DECLARE xk1s CHAR(12);
    DECLARE xk1f CHAR(12);
    DECLARE xk2s CHAR(12);
    DECLARE xk2F CHAR(12);
    DECLARE curtime CHAR(12);
    SELECT substr(v,1,12),substr(v,14) into xk1s,xk1f FROM admin WHERE k='xk1';
    SELECT substr(v,1,12),substr(v,14) into xk2s,xk2f FROM admin WHERE k='xk2';
    SELECT date_format(now(),'%Y%m%d%H%i') into curtime;
    IF (curtime>xk1s AND curtime<xk1f) OR (curtime>xk2s AND curtime<xk2f) THEN
        SET buer = 1;
    ELSE
        SET buer = 0;
    END IF;
    SELECT buer;
END$$
DELIMITER ;

-- 触发器 筛选学生时加入删选记录表
DELIMITER $$
DROP TRIGGER IF EXISTS `CourseDeleted`$$
CREATE TRIGGER CourseDeleted AFTER DELETE ON grade FOR EACH ROW
BEGIN
	DECLARE xk1f CHAR(12);
	DECLARE xk2s CHAR(12);
	DECLARE xk2F CHAR(12);
	DECLARE curtime CHAR(12);
	SELECT substr(v,14,12) into xk1f FROM admin WHERE k='xk1';
	SELECT substr(v,1,12),substr(v,14,12) into xk2s,xk2f FROM admin WHERE k='xk2';
	SELECT date_format(now(),'%Y%m%d%H%i') into curtime;
	IF (curtime>xk1f AND curtime<xk2s) OR curtime>xk2f THEN
		INSERT INTO history(xh,id) values (OLD.xh,OLD.id);
	END IF;
END$$
DELIMITER ;