员工管理数据库系统的创建及操作

图1-1 

1.

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 ;