岗位实习最终篇（汇总）——人力资源管理系统（包含DDL，DML，视图，简单/复杂查询，触发器语句和存储过程语句）

DDL

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    first_name VARCHAR(50) NOT NULL COMMENT '名',
    last_name VARCHAR(50) NOT NULL COMMENT '姓',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    phone VARCHAR(20) UNIQUE COMMENT '电话',
    hire_date DATE NOT NULL COMMENT '入职日期'
);

CREATE TABLE `zhiwei` (
  `zhiwei_id` int(15) NOT NULL AUTO_INCREMENT,
  `zhiwei` varchar(255) NOT NULL,
  `users_id` int(20) NOT NULL,
  PRIMARY KEY (`zhiwei_id`),
  KEY `zhiwei-users` (`users_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `bumen` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `bumen` varchar(255) NOT NULL,
  `users_id` int(11) NOT NULL,
  `zhiwei_id` int(15) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `users-bumen` (`users_id`),
  KEY `bumen-zhiwei` (`zhiwei_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `salary` (
  `salary_id` int(20) NOT NULL AUTO_INCREMENT,
  `salary` int(8) DEFAULT NULL,
  `users_id` int(20) NOT NULL,
  PRIMARY KEY (`salary_id`),
  KEY `users-salary` (`users_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `change_in_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `operation` varchar(15) NOT NULL COMMENT '操作类型',
  `operate_time` datetime NOT NULL COMMENT '操作时间',
  `operate_id` int(11) NOT NULL COMMENT '操作的ID',
  `operate_params` varchar(255) NOT NULL COMMENT '操作参数',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

视图

触发器语句

案例一（insert） 

delimiter $$  -- 分隔符
create trigger after_insert_users  -- 创建触发器名称
after insert on users  -- 员工表执行插入操作之后被触发的触发器
for each row  -- 每行数据都要进行触发
begin
insert into change_in_users (id,operation,operate_time,operate_id,operate_params)  values (null,'insert',now(),new.user_id,concat('插入数据内容为,','username=',new.username,'password=',new.`password`,'first_name=',new.first_name,'last_name=',new.last_name,'gender=',new.gender,'email=',new.email,'phone=',new.phone,'hire_date=',new.hire_date));   -- 触发器触发后要执行的代码块
end ;
$$
delimiter ;  -- 结束$$的有效性更换成;

案例二 （update）

delimiter $$  -- 分隔符
create trigger after_update_users  -- 创建触发器名称
after update on users  -- 员工表执行更新操作之后被触发的触发器
for each row   -- 每行数据都要进行触发
begin
insert into change_in_users (id,operation,operate_time,operate_id,operate_params)  values (null,'update',now(),new.user_id,concat('更新数据的变动为,','username:',old.username,'变为',new.username,'password:',old.`password`,'变为',new.`password`,'first_name:',old.first_name,'变为',new.first_name,'last_name:',old.last_name,'变为',new.last_name,'gender:',old.gender,'变为',new.gender,'email:',old.email,'变为',new.email,'phone:',old.phone,'变为',new.phone,'hire_date:',old.hire_date,'变为',new.hire_date));  -- 触发器触发后要执行的代码块
end ;
$$
delimiter ;  -- 结束$$的有效性更换成;

案例三 (delete)

delimiter $$  -- 分隔符
create trigger after_delete_users  -- 创建触发器名称
after delete on users  -- 员工表执行删除操作之后被触发的触发器
for each row  -- 每行数据都要被触发
begin
insert into change_in_users (id,operation,operate_time,operate_id,operate_params)  values (null,'delete',now(),old.user_id,concat('删之前的数据','user_id:',old.user_id,'username:',old.username,'password:',old.`password`,'first_time:',old.first_name,'last_name:',old.last_name,'gender:',old.gender,'email:',old.email,'phone:',old.phone));  -- 触发器触发后要执行的代码块
end ;
$$
delimiter ;  -- 结束$$的有效性更换成;

插入语句

INSERT INTO users (user_id, username, password, first_name, last_name, gender, email, phone, hire_date) VALUES
(1, '韦小宝', '123', '韦小宝', '假太监', '男', 'weixiaobao@example.com', '13800138001', '2020-01-01'),
(2, '孙悟空', '123', '孙悟空', '齐天大圣', '男', 'sunwukong@example.com', '13900139002', '2021-05-15'),
(3, '林黛玉', '123', '林黛玉', '潇湘妃子', '女', 'lingdaiyu@example.com', '13700137003', '2022-09-01'),
(4, '张三', '123', '张三', '张五', '男', '456123789@qq.com', '12345678912', '2020-06-06');

效果图：

users表：

change_in_users表：

更新语句

update users set username = '张飞',password = '520' where user_id = 4 ;

效果图：

users表：

change_in_users表：

删除语句：

delete from users where user_id = 4;

效果图：

users表：

change_in_users表：

查询所有触发器：

show triggers;

效果图：

对其他三个表插入数据：

职位表：

INSERT INTO `zhiwei` VALUES (1, '老板', 4);
INSERT INTO `zhiwei` VALUES (2, '招聘专员', 2);
INSERT INTO `zhiwei` VALUES (3, '开发工作者', 1);
INSERT INTO `zhiwei` VALUES (4, '推销专员', 3);

部门表：

INSERT INTO `bumen` VALUES (1, '人力资源部', 2, 2);
INSERT INTO `bumen` VALUES (2, '销售部', 3, 4);
INSERT INTO `bumen` VALUES (3, '技术开发部', 1, 3);
INSERT INTO `bumen` VALUES (4, '管理部门', 4, 1);

薪资表：

INSERT INTO `salary` VALUES (1, 40000, 4);
INSERT INTO `salary` VALUES (2, 7000, 2);
INSERT INTO `salary` VALUES (3, 8000, 1);
INSERT INTO `salary` VALUES (4, 7000, 3);

简单查询：

-- 查询林黛玉的电子邮箱和电话号码
select email , phone from users where username='林黛玉';

效果图：

-- 计算每种性别的员工数量
select gender , count(*) from users group by gender;

效果图：

-- 按照员工入职时间顺序进行查询
select * from users order by hire_date asc;

复杂查询：

员工和其对应职位信息：

-- 员工和其对应职位信息
select users.username,zhiwei.zhiwei from users
 left join zhiwei on users.user_id = zhiwei.users_id;

效果图：

职位所对应部门信息：

-- 职位所对应部门信息
select zhiwei.zhiwei,bumen.bumen from zhiwei
 right join bumen on bumen.zhiwei_id = zhiwei.zhiwei_id;

效果图：

子查询实例：

-- 通过子查询连接三个表
select * from users join bumen on users.user_id = bumen.users_id join (select * from zhiwei ) zh on users.user_id = zh .zhiwei_id;

效果图：

存储过程：

delimiter $$  -- 分隔符
create procedure p(in salary int, out result varchar(10))  -- 创建存储过程名称，明确输入和输出过程变量的参数
begin
	if salary >= 30000 then 
		set result := '管理层';
	elseif salary >= 12000 then
		set result := '优秀员工';
	elseif salary >= 8000 then
		set result := '正式员工';
	elseif salary >= 4000 then
		set result := '普通员工';
	else 
		set result := '实习生';
	end if;   -- 存储过程当中的代码块
end;
$$
delimiter ; -- 结束$$的有效性更换成;

call p(3000,@result);  -- 调用存储过程
select @result;  -- 对输出变量进行查询

效果图：