微人事(HR)系统数据库表设计及查询

微人事(HR)系统数据库表设计及查询

● 初始化
● 分析表设计
● 高级查询应用
● 视图(View)的设计
● 事务(Transaction)的应用

数据初始化操作

命令行登陆mysql，然后执行source d:/hr_mysql.sql

关键表设计分析

当如上sql脚本执行完之后，会默认创建一个hr数据库，并且在数据库中会创建如下多张表：

● 区域表(Regions)
● 国家表(Countries)
● 地址表(Locations)
● 部门表(Departments)
● 雇员表(Employees)
● 岗位表(Jobs)
● 岗位变更历史表(job_history)

关键表关系分析

● 一个区域(Regions)可以有多个国家(one2many)
● 一个国家可以有多个locations(one2many)
● 一个地址(locations)中可以有多个部门(one2many)
● 一个部门中可以有多个雇员(one2many)
● 一个岗位下可以有多个雇员(one2many)
● 一个雇员可以有多个岗位变动(one2many)

FAQ？一对多个关系，关系维护方在哪里？(多的一方)

表中关键字段分析

区域表(Regions)

CREATE TABLE regions
(
region_id int auto_increment comment '编号',
region_name VARCHAR(25) comment '区域名称',
primary key (region_id),
unique key (region_name)
) engine=innodb default character set utf8;

国家表(Countries)

CREATE TABLE countries
(
country_id CHAR(2) primary key, 
country_name VARCHAR(40) comment '国家名称',
region_id int
)engine=innodb default character set utf8;

ALTER TABLE countries
ADD (
CONSTRAINT countr_reg_fk
FOREIGN KEY (region_id)
REFERENCES regions(region_id)
);

地址表(Locations)

CREATE TABLE locations
( location_id int(4) primary key auto_increment,
  street_address VARCHAR(40),
  postal_code VARCHAR(12), 
  city VARCHAR(30) not null, 
  state_province VARCHAR(25),
  country_id CHAR(2)
) engine=innodb default character set utf8;

ALTER TABLE locations
ADD (
CONSTRAINT loc_c_id_fk
FOREIGN KEY (country_id)
REFERENCES countries(country_id)
);

部门表(Departments)

CREATE TABLE departments
( department_id int(4) primary key auto_increment, 
  department_name VARCHAR(30) NOT NULL,
  manager_id int(6), 
  location_id int(4)
) engine=innodb default character set utf8;

ALTER TABLE departments
ADD (
CONSTRAINT dept_loc_fk
FOREIGN KEY (location_id)
REFERENCES locations (location_id)
) ;

岗位表(Jobs)

CREATE TABLE jobs
( job_id VARCHAR(10) primary key, 
  job_title VARCHAR(35) NOT NULL, 
  min_salary numeric(6), 
  max_salary numeric(6)
) engine=innodb default character set utf8;

雇员表

CREATE TABLE employees
( employee_id int(6