啦啦啦啦拉拉

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,
subminDate 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 bitrhDate DATE,
            IN idCard VARCHAR(255),
            IN loginName VARCHAR(255),
            IN PASSWORD VARCHAR(255),
            IN mobile VARCHAR(255),
            IN email VARCHAR(255),
            IN deptId INT,
            IN 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','410523200423320167','avc','12345','1234567890','123',1,1,NULL,NULL);
/*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 ;

/*5*/
CREATE INDEX index_userName ON staff.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`staff``payroll`

/*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 ;