SET names utf8;
SET FOREIGN_KEY_CHECKS=0;

DROP DATABASE IF EXISTS attendance;
CREATE DATABASE attendance CHARSET utf8;
USE attendance;
 
-- ----------------------------
-- Table structure for staff_info
-- ----------------------------
DROP TABLE IF EXISTS `staff_info`;
CREATE TABLE `staff_info` (
  `jobnum` int(255) NOT NULL COMMENT '学号',
  `name` varchar(255) default NULL COMMENT '姓名',
  `dept` varchar(255) default NULL COMMENT '院系',
  `depthead` varchar(255) default NULL COMMENT '班级'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Table structure for attend_record
-- ----------------------------
DROP TABLE IF EXISTS `attend_record`;
CREATE TABLE `attend_record` (
  `jobnum` int(255) NOT NULL COMMENT '学号',
  `number` int(255) NOT NULL COMMENT '考勤序号',
  `state` varchar(255) default NULL COMMENT '签到为通过否则为不通过'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for attend_score
-- ----------------------------
DROP TABLE IF EXISTS `attend_score`;
CREATE TABLE `attend_score` (
  `jobnum` int(255) NOT NULL COMMENT '学号',
  `sum_fail` int(255) NOT NULL COMMENT '累计签到次数',
  `score` int(255) NOT NULL COMMENT '考勤成绩'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for var
-- ----------------------------
DROP TABLE IF EXISTS `var`;
CREATE TABLE `var` (
  `sum_all` int(255) NOT NULL COMMENT '考勤次数'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `var` (`sum_all`) VALUES (0);

-- ----------------------------
-- Records of staff_info
-- ----------------------------
INSERT INTO `staff_info` (`jobnum`, `name`, `dept`, `depthead`) VALUES
(438, '陈越', '数据学院', '1'),
(1, '学生1', '学院1', '1'),
(2, '学生2', '学院2', '2'),
(3, '学生3', '学院3', '3');

-- ----------------------------
-- Records of attend_score
-- ----------------------------
INSERT INTO `attend_score` (`jobnum`) VALUES
(438),
(1),
(2),
(3);

-- ---------------------------------
-- Indexes for table `staff_info`
-- ---------------------------------
ALTER TABLE `staff_info`ADD PRIMARY KEY (`jobnum`);

-- ---------------------------------
-- Indexes for table `attend_score`
-- ---------------------------------
ALTER TABLE `attend_score`ADD PRIMARY KEY (`jobnum`);

-- ----------------------------
-- 在表attend_record上创建触发器change_score
-- 签到通过，score+1，
-- 累计考勤不通过数
-- ----------------------------
DELIMITER $$
DROP TRIGGER IF EXISTS change_score$$
CREATE TRIGGER change_score AFTER INSERT
   ON attend_record FOR EACH ROW
   BEGIN
      --  考勤通过者，score+1
      update attend_score 
      set score=score+'1' 
      where  jobnum=NEW.jobnum and NEW.state = '通过';
      -- 累计考勤不通过数
      update attend_score 
      set sum_fail=sum_fail+'1'
      where  jobnum=NEW.jobnum and NEW.state = '不通过';
   END
   $$
DELIMITER ;