员工管理数据库

按照要求实现员工管理数据库系统中的指定操作：

1、创建一个数据库 staff，并在数据库中创建以下四张表：

员工表 employee（员工编号id,姓名userName,出生日期birthDate,身份证号idCard,登录名称loginName,登录密码password,手机号mobile,电子邮件email,部门编号deptId,员工级别level,员工头像avatar,备注remark）

部门表 dept(部门编号id,部门名称deptName,部门经理编号managerId)

工资表payroll(工资编号id,员工编号empId,基本工资baseSalary,应发工资actualSalary,奖金bonus,缺勤扣钱deductMoney,薪资发放日期grantDate)

请假表ask_leave(请假编号id,员工编号empId,请假原因leaveReason,请假开始时间beginDate,请假结束时间endDate,提交时间submitDate,审核人编号auditId(该列有触发器维护),申请状态status,审核意见auditOpinion)

基础数据自己填写。

CREATE DATABASE staff;  

USE staff;  

CREATE TABLE employee (  

    id INT PRIMARY KEY,  

    userName VARCHAR(50),  

    birthDate DATE,  

    idCard VARCHAR(18),  

    loginName VARCHAR(50),  

    password VARCHAR(255),  

    mobile VARCHAR(20),  

    email VARCHAR(100),  

    deptId INT,  

    level VARCHAR(10),  

    avatar TEXT,  

    remark TEXT  

);  

CREATE TABLE dept (  

    id INT PRIMARY KEY,  

    deptName VARCHAR(50),  

    managerId INT  

);  

CREATE TABLE payroll (  

    id INT PRIMARY KEY,  

    empId INT,  

    baseSalary DECIMAL(10, 2),  

    actualSalary DECIMAL(10, 2),  

    bonus DECIMAL(10, 2),  

    deductMoney DECIMAL(10, 2),  

    grantDate DATE,  

    FOREIGN KEY (empId) REFERENCES employee(id)  

);  

CREATE TABLE ask_leave (  

    id INT PRIMARY KEY,  

    empId INT,  

    leaveReason TEXT,  

    beginDate DATE,  

    endDate DATE,  

    submitDate DATE,  

    auditId INT,  

    status VARCHAR(20),  

    auditOpinion TEXT,  

    FOREIGN KEY (empId) REFERENCES employee(id)  

);  

2、编写存储过程实现插入员工表：参数为：员工编号id,姓名userName,出生日期birthDate,身份证号idCard,登录名称loginName,登录密码password,手机号mobile,电子邮件email,部门编号deptId,员工级别level,员工头像avatar,备注remark。

存储过程名称为：insert_employee。

DELIMITER $$

DROP PROCEDURE IF EXISTS insert_employee$$

CREATE

    PROCEDURE `staff`.`insert_employee`(

    IN p_id INT,

    IN u_name VARCHAR(20),

    IN birth_date VARCHAR(20),

    IN id_card VARCHAR(20),

    IN login_name VARCHAR(20),

    IN pass_word VARCHAR(20),

    IN mobile_ VARCHAR(20),

    IN eamil_ VARCHAR(20),

    IN dept_id VARCHAR(20),

    IN level_ VARCHAR(20),

    IN avater_ VARCHAR(20),

    IN remark_ VARCHAR(20)

    )

    

    BEGIN

INSERT INTO employee(id,userName,birthDate,idCard,loginName,PASSWORD,mobile,email,deptid,LEVEL,avatar,remark)

VALUES(p_id,u_name,birth_date,id_card,login_name,pass_word,mobile_,eamil_,dept_id,level_,avater_,remark_);

    END$$

DELIMITER ;

3、利用存储过程在员工表中插入5条记录。

CALL insert_employee(1, 'John Doe', '1990-01-01', '123456789012345678', 'john_doe', 'password123', '1234567890', 'john@example.com', "1", 'junior', 'avatar_url', 'Employee remark');  

CALL insert_employee(2, 'John Doe', '1990-01-01', '123456789012345678', 'john_doe', 'password123', '1234567890', 'john@example.com', "1", 'junior', 'avatar_url', 'Employee remark');

CALL insert_employee(3, 'John Doe', '1990-01-01', '123456789012345678', 'john_doe', 'password123', '1234567890', 'john@example.com', "1", 'junior', 'avatar_url', 'Employee remark');

CALL insert_employee(4, 'John Doe', '1990-01-01', '123456789012345678', 'john_doe', 'password123', '1234567890', 'john@example.com', "1", 'junior', 'avatar_url', 'Employee remark');

CALL insert_employee(5, 'John Doe', '1990-01-01', '123456789012345678', 'john_doe', 'password123', '1234567890', 'john@example.com', "1", 'junior', 'avatar_url', 'Employee remark');

4、创建触发器，当插入或修改工资表payroll时，应发工资自动为“基本工资+奖金-缺勤扣钱”。

DELIMITER $$

CREATE

    TRIGGER `staff`.`trg_after_payroll_insert_` BEFORE INSERT

    ON staff.payroll

    FOR EACH ROW

    BEGIN

IF new.id IS NOT NULL THEN

 SET NEW.actualSalary = NEW.baseSalary + NEW.bonus - NEW.deductMoney;

END IF;

END$$

DELIMITER ;

DELIMITER $$

CREATE

    TRIGGER `staff`.`trg_after_payroll_UPDATE_` BEFORE INSERT

    ON staff.payroll

    FOR EACH ROW

    BEGIN

IF new.id IS NOT NULL THEN

 SET NEW.actualSalary = NEW.baseSalary + NEW.bonus - NEW.deductMoney;

END IF;

END$$

DELIMITER ;

5、在员工表中依据姓名userName建立索引。索引名为：index_userName。

CREATE INDEX index_userName ON employee(userName)

6、建立员工部门工资视图（包含员工名称，部门名称，基本工资,应发工资,奖金,缺勤扣钱）视图名称：v_employee_dept_payroll。

CREATE VIEW v_employee_dept_payroll AS

SELECT e.userName,d.deptName,p.baseSalary,p.actualSalary,p.bonus,p.deductMoney FROM dept d,employee e,payroll p

WHERE e.id=p.empId AND d.id = e.deptid;

7、利用触发器实现插入请假信息时，审核人编号自动填入请假人所在部门的部门经理编号。触发器名称为：insert_ask_leave。

DELIMITER $$

CREATE

    TRIGGER `staff`.`insert_ask_leave` BEFORE INSERT

    ON staff.ask_leave

    FOR EACH ROW

    BEGIN

DECLARE manage_id INT;

SELECT d.managerId INTO manage_id FROM dept d JOIN employee e ON e.deptid=d.id WHERE new.empId=e.id;

SET new.auditId = manage_id;

    END$$

DELIMITER ;
————————————————

                            版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
                        
原文链接：https://blog.csdn.net/genghw666/article/details/138872658