使用下面的场景来引入
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.编写存储过程实现插入员工表：参数为：
| 员工编号 | id | int |
|---|---|---|
| 姓名 | userName | varchar(225) |
| 出生日期 | birthDate | date |
| 身份证号 | idCard | varchar(225) |
| 登录名称 | loginName | varchar(225) |
| 登录密码 | password | varchar(225) |
| 手机号 | mobile | varchar(225) |
| 电子邮件 | varchar(225) | |
| 部门编号 | deptId | int |
| 员工级别 | level | int |
| 员工头像 | avatar | blob |
| 备注 | remark | text |
存储过程名称为：insert_employee
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_employee`(
IN `id` int,
IN `username` varchar(225),
IN `birthDate` date,
IN `idCard` varchar(225),
IN `loginName` varchar(225),
IN `password` varchar(225),
IN `mobile` varchar(225),
IN `email` varchar(225),
IN `deptId` int,
IN `level` int,
IN `avatar` blob,
IN `remark` text
)
BEGIN
DECLARE cnt INT;
SELECT COUNT(*) INTO cnt FROM employee WHERE employee.id = id;
IF cnt = 0 THEN
INSERT INTO employee(`id`,`username`,`birthDate`,`idCard`,`loginName`,`password`,`mobile`,`email`,`deptId`,`level`,`avatar`,`remark` )
VALUES(`id`,`username`,`birthDate`,`idCard`,`loginName`,`password`,`mobile`,`email`,`deptId`,`level`,`avatar`,`remark`);
END IF;
END
3.利用存储过程在员工表中插入记录.
call insert_employee(1,'小红','2002-03-14','411423200203141510','xiaohong','123','15238790678','1625376859@qq.com',3,1,NULL,'新员工');
call insert_employee(2,'小橙','2002-02-14','411423200203241511','xiaocheng','123','15238790677','1625376858@qq.com',2,2,NULL,'新员工');
4.创建触发器。
插入
CREATE TRIGGER `insert_payroll` BEFORE
INSERT ON `payroll`
FOR EACH ROW SET new.actualSalary = new.baseSalary + new.bonus - new.deductMoney;
更新
CREATE DEFINER = `root`@`localhost`
TRIGGER `update_payroll` BEFORE
UPDATE ON `payroll`
FOR EACH ROW SET new.actualSalary = new.baseSalary + new.bonus - new.deductMoney;
5.在员工表中依据姓名userName建立索引。
CREATE INDEX index_userName
ON employee(username);
6.建立员工部门工资视图（包含员工名称，部门名称，基本工资,应发工资,奖金,缺勤扣钱）
CREATE VIEW v_employee_dept_payroll AS
SELECT username AS 姓名,deptName AS 部门名称,baseSalary AS 基本工资,actualSalary AS 应发工资,bonus AS 奖金,deductMoney AS 缺勤扣钱
FROM employee,dept,payroll
WHERE employee.id = payroll.empId AND employee.deptId = dept.id;
7.利用触发器实现插入请假信息时，审核人编号自动填入请假人所在部门的部门经理编号。
CREATE DEFINER = `root`@`localhost`
TRIGGER `insert_ask_leave` BEFORE
INSERT ON `ask_leave`
FOR EACH ROW SET new.auditId = (
SELECT manageId
FROM employee,dept
WHERE employee.deptid = dept.id AND new.empid = employee.id
);
