CREATE DATABASE staff;
USE staff
CREATE TABLE employee(
id INT NOT NULL AUTO_INCREMENT,
userName VARCHAR(255),
birthDate DATE,
idCard VARCHAR(255),
loginName VARCHAR(255),
PASSWORD VARCHAR(255),
mobile VARCHAR(255),
email VARCHAR(255),
deptId INT,
LEVEL INT,
avatar BLOB,
remark TEXT,
PRIMARY KEY(id)
);
CREATE TABLE dept(
id INT NOT NULL AUTO_INCREMENT,
deptName VARCHAR(255),
manageId INT,
remark VARCHAR(255),
PRIMARY KEY(id)
);
CREATE TABLE payroll(
id INT NOT NULL AUTO_INCREMENT,
empId INT,
baseSalary DOUBLE,
actualSalary DOUBLE,
bonus DOUBLE,
deductMoney DOUBLE,
grantDate DATE,
PRIMARY KEY(id)
);
CREATE TABLE ask_leave(
id INT NOT NULL AUTO_INCREMENT,
empId INT,
leaveReason TEXT,
beginDate DATE,
endDate DATE,
submitDate DATE,
auditId INT,
STATUS INT,
auditOpinion TEXT,
PRIMARY KEY(id)
);
2、
DELIMITER $$
CREATE
PROCEDURE `staff`.`insert_employee`( IN id INT, IN userName VARCHAR(255),
IN birthDate DATE,
IN idCard VARCHAR(255),
IN loginName VARCHAR(255),
IN PASSWORD VARCHAR(255),
IN mobile VARCHAR(255),
IN email VARCHAR(255),
IN deptId INT,
LEVEL INT,
IN avatar BLOB,IN remark TEXT)
BEGIN
INSERT INTO `employee` VALUES(NULL,userName,birthDate,idCard,
loginName,PASSWORD,mobile,email,deptId,LEVEL,avatar,remark);
END$$
DELIMITER ;
3、CALL `insert_employee`(NULL,'aa','2001-1-1','41032222222222222','abc','1234','123445555','123@qq.com',1,1,NULL,NULL);写5条即可
4、
DELIMITER $$
CREATE
TRIGGER `staff`.`beforeinsert_payloyee` BEFORE INSERT
ON `staff`.`payroll`
FOR EACH ROW BEGIN
set new.`actualSalary`=new.`baseSalary`+new.`bonus`-new.`deductMoney`;
END$$
DELIMITER ;
DELIMITER $$
CREATE
TRIGGER `staff`.`before_update_payloyee` BEFORE UPDATE
ON `staff`.`payroll`
FOR EACH ROW BEGIN
SET new.`actualSalary`=new.`baseSalary`+new.`bonus`-new.`deductMoney`;
END$$
DELIMITER ;
5、
CREATE INDEX index_userName ON `employee`(`userName`);
6、
CREATE VIEW v_employee_dept_payroll
AS
SELECT `userName`,`deptName`,`baseSalary`,`actualSalary`,`bonus`,`deductMoney`
FROM`dept`,`employee`,`payroll`
WHERE `dept`.`id`=`employee`.`deptId` AND `employee`.`id`=`payroll`.`empId`
7、
DELIMITER $$
CREATE
TRIGGER `staff`.`insert_ask_leave` BEFORE INSERT
ON `staff`.`ask_leave`
FOR EACH ROW BEGIN
DECLARE manangeID1 INT;
SELECT `manageId` INTO manangeID1 FROM `dept` WHERE `id`=(
SELECT `deptId` FROM `employee` WHERE `id`=new.`empId`);
SET new.`auditId`=manangeID1;
END$$
DELIMITER ;