微人事(HR)系统数据库表设计及查询
课程安排
- 初始化
- 分析表设计
- 高级查询应用
- 视图(View)的设计
- 事务(Transaction)的应用
数据初始化操作
命令行登陆mysql,然后执行source d:/hr_mysql.sql
关键表设计分析
- 区域表(Regions)
- 国家表(Countries)
- 地址表(Locations)
- 部门表(Departments)
- 雇员表(Employees)
- 岗位表(Jobs)
- 岗位变更历史表(job_history)
关键表关系分析
- 一个区域可以有多个国家(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) primary key auto_increment,
first_name VARCHAR(20),
last_name VARCHAR(25) NOT NULL,
email VARCHAR(25) NOT NULL,
phone_number VARCHAR(20),
hire_date DATE NOT NULL,
job_id VARCHAR(10) NOT NULL,
salary numeric(8,2),
commission_pct numeric(2,2),
manager_id int(6),
department_id int(4),
CONSTRAINT emp_salary_min CHECK (salary > 0),
CONSTRAINT emp_email_uk UNIQUE (email)
) engine=innodb default character set utf8;
ALTER TABLE employees
ADD (
CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
,
CONSTRAINT emp_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs (job_id)
,
CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees(employee_id)
) ;
ALTER TABLE departments
ADD (
CONSTRAINT dept_mgr_fk
FOREIGN KEY (manager_id)
REFERENCES employees (employee_id)
) ;
岗位历史变更表
CREATE TABLE job_history
( employee_id int(6) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
job_id VARCHAR(10) NOT NULL,
department_id int(4),
CONSTRAINT jhist_date_interval
CHECK (end_date > start_date),
CONSTRAINT jhist_emp_id_st_date_pk PRIMARY KEY (employee_id, start_date)
) engine=innodb default character set utf8;
ALTER TABLE job_history
ADD (
CONSTRAINT jhist_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs(job_id)
,
CONSTRAINT jhist_emp_fk
FOREIGN KEY (employee_id)
REFERENCES employees(employee_id)
,
CONSTRAINT jhist_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
) ;
常用查询分析及实践
1)求雇员编号206的经理人的名字和薪水?
请问雇员的经理人是雇员吗?是 那经理人的id是不是需要在雇员id中有相同值?需要
方案1:嵌套查询
select first_name,salary
from employees
where employee_id=(
select manager_id
from employees
where employee_id=206
)
方案2:自关联(可以将自身这张表看成是多张表)
select m.first_name,m.salary
from employees e join employees m
on e.manager_id=m.employee_id
where e.employee_id=206
拓展:求雇员编号206的雇员名,部门名称,经理人的名字和薪水?
select e.employee_id,e.first_name, d.department_name,m.first_name,m.salary
from employees e join employees m on e.manager_id=m.employee_id
join departments d on e.department_id = d.department_id
where e.employee_id=206;
2)查询雇员206所在部门的部门名称以及这个部门所在的城市? -- 雇员表中有部门名称吗?没有,部门表(departments)中有 -- 雇员表中有城市名称名称?没有,地址表(locations)中有
select e.employee_id,d.department_name,l.city
from employees e join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id
where e.employee_id=206;
3)统计每个岗位的雇员数,并按人数进行降序排序?
select job_id,count(*) total
from employees
group by job_id
order by total desc;
4)统计公司中有佣金提成的员工人数有多少?
方案1
select count(*)
from employees
where commission_pct is not null;
方案2
select count(1)
from employees
where commission_pct is not null;
方案3(count函数内部是列名,表示统计列值不为null的记录)
select count(commission_pct)
from employees;
5)统计年入职的人数有多少?(查看系统函数 help 'functions')
select year(hire_date),count(*)
from employees
group by year(hire_date);
6)查询每个部门的平均薪资,部门id为null的除外,并按平均薪资降序排序
select department_id,avg(salary)
from employees
where department_id is not null
group by department_id
order by avg(salary) desc;
7)查询每个部门的平均薪资,只显示平均薪资大于10000的,并按降序排序
select department_id,avg(salary)
from employees
group by department_id
having avg(salary)>10000
order by 2 desc;
8)查询每个雇员的薪资,并给出薪资等级(例如>=10000高;>=8000and<10000 中等;<8000偏低)
考核知识点:case when 表达式的应用
select first_name,salary,
(case when salary>=10000 then '偏高'
when salary>=8000 then '中等'
else '偏低' end) level
from employees
9)统计薪资大于等于10000的人数,小于10000的人数。
select sum(case when salary>=10000 then 1 else 0 end ) '大于等于10000',
sum(case when salary<10000 then 1 else 0 end ) '小于10000'
from employees
10)查询雇员表中第二页的数据(每页最多显示10条记录-页面大小)?
方案1:(数据量比较小的情况下-这种查询是全表扫描,数据量大时查询会比较慢)
select *
from employees
limit 10,10
方案2:(数据量比较大时-例如500万数据)
select e2.*
from (
select employee_id
from employees
order by employee_id
limit 10,10) e1 join employees e2
on e1.employee_id=e2.employee_id
11)说说如下查询SQL语句的执行顺序
select(7)
from (1)
join (3)
on (2)
where(4)
group by(5)
having(6)
order by (8)
limit(9)
12)说说如下查询返回的结果数是多少?
select count(*)
from employees,deparements;
返回的结果数为两张表数量的乘积.(这个查询为笛卡尔积)
*13)删除区域表(Regions)表中区域名字重复的记录
准备工作,向表中写入几个区域名重复的记录。
insert into regions(region_Name) values ('Asia');
insert into regions(region_Name) values ('Asia');
insert into regions(region_Name) values ('Asia');
方案1:MariaDB (MySQL5.7不成立)
delete
from regions
where region_id not in(
select min(region_id) min_id
from regions
group by region_name);
方案2:MySQL5.7
delete
from regions
where region_id not in(
select min_id
from (
select min(region_id) min_id
from regions
group by region_name)t );
方案3:通过表关联的方式执行删除(MySQL5.7)
delete r1.*
from regions r1 join regions r2
on r1.region_name=r2.region_name and r1.region_id>r2.region_id
视图(View)的应用
- 什么是视图?
数据库中的一个对象,可以将其看成是一张虚拟的表。视图基于表创建,只有结构但不存储数据。我们 可以通过视图查询到表中的数据。
- 为什么要使用视图?
简化程序中对SQL语句的编写,更好保证数据的安全。
- 如何创建视图?
create view emp_view as
select e.first_name,d.department_name,l.city
from employees e join departments d on e.department_id = d.department_id
join locations l on d.location_id = l.location_id;
- 如何基于视图查询数据
select first_name,department_name,city from emp_view;
- 如何删除视图?
drop view if exists emp_view;
- 可以基于视图更新表中数据吗?
简单视图(基于单张表并且没有数据统计等函数应用)可以,但是不推荐。
事务(Transaction)逻辑应用
- 什么是事务?
是一个逻辑工作单元,这个工作单元中的所有操作,要么都成功,要么都失败。
- 为什么要事务?
用于保证数据的正确性(完整性,一致性)
- 事务是如何保证数据的正确性的?
通过事务的四大特性(ACID):原子性,一致性,隔离性,持久性。
- 事务的隔离级别有哪些?
四种(这里的隔离级别从小到大依次为read uncommitted<read committed<repeatable read<Serializable) ,隔离级别越高,并发性能越差,数据的安全性越好。
- 如何查看当前会话使用的事务隔离级别?
select @@tx_isolation;
- 如何修改当前会话的事务隔离级别?
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
set tx_isolation='read-uncommitted';
set tx_isolation='read-committed';
set tx_isolation='repeatable-read';
set tx_isolation='serializable';
- 如何修改全局的的事务隔离级别?
set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;
set global transaction isolation level serializable;
set global tx_isolation='read-uncommitted';
set global tx_isolation='read-committed';
set global tx_isolation='repeatable-read';
set global tx_isolation='serializable';
- 多个事务并发执行时,可能会带来什么问题?
- 脏读(一个事务读取了其它事务未提交的数据,然后这个数据后面又北回滚了)
- 不可重复读(一个事务对同样查询条件的数据进行多次查询时,得到的结果不一致)
- 幻读(一个读取到的数据可能是表中不存在的数据)
- 如何解决多个事务并发执行时可能会带来的一些问题呢?
可以通过修改事务的隔离级别进行实现,但是要注意隔离级别越高,并发性可能会越差。
- 通过案例演示多个事务并发时导致的脏读问题?
事务1 | 事务2 |
---|---|
set tx_isolation='read-uncommitted' | set tx_isolation='read-uncommitted' |
set autocommit=0 | set autocommit=0 |
begin | |
select * from regions; | begin |
.... | insert into regions(region_name) values ('sanjiang'); |
select * from regions; | |
... | rollback; |
- 通过案例演示多个事务并发时导致的不可重复读问题?
事务1 | 事务2 |
---|---|
set tx_isolation='read-committed' | set tx_isolation='read-committed' |
set autocommit=0 | set autocommit=0 |
begin | |
select * from regions; | begin |
... | update regions set region_name='shoudu' where region_id=12; |
... | commit; |
select * from regions; |
- 通过案例演示多个事务并发时导致的幻读问题?
事务1 | 事务2 |
---|---|
set tx_isolation='repeatable-read' | set tx_isolation='repeatable-read' |
set autocommit=0 | set autocommit=0 |
begin | |
select count(*) from regions; | begin |
... | delete from regions where region_id=12; |
... | commit; |
select count(*) from regions; |