Watch Star Fork

XiaoSK / DB2PHPGPL-2.0

加入码云
与超过 200 万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
DB2 是 2013-2014 学年春季学期数据库实验。
克隆/下载
一键复制 编辑 原始数据 按行查看 历史
table.sql 4.04 KB XiaoSK 提交于 2014-05-28 03:18 . DB2::version = Milestone;
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 ;

评论 ( 0 )

你可以在登录后,发表评论

8_float_left_people 8_float_left_close