使用下面的場景來引入
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
)
BEGINDECLARE 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
);
