20220604_MySQL_多表查询案例

show databases ;
create database multi_query_example;

drop table if exists employee;
drop table if exists job_position;
drop table if exists department;
drop table if exists salary_grade;
-- 按顺序建立部门表，职位表，员工表，工资等级表
create table department(
    id int primary key ,
    name varchar(50),
    location varchar(50)
);

create table job_position(
    id int primary key ,
    name varchar(20),
    description varchar(50)
);

create table employee(
    id int primary key ,
    name varchar(50),
    job_id int ,
    supervisor int,
    join_date date,
    salary decimal(7,1),
    bonus decimal(7,1),
    depart_id int,
    constraint emp_job_id_fk foreign key (job_id)
                     references job_position (id),
    constraint emp_depart_id_fk foreign key (depart_id)
                     references department (id)
);

create table salary_grade(
    grade int primary key ,
    low_salary int ,
    high_salary int
);
show tables;

建好表项之后ER图如此

-- 给每张表插入数据
insert into department(id,name,location) values
(10,'研发部','深圳'),
(20,'市场部','上海'),
(30,'财务部','北京'),
(40,'行政部','杭州');

insert into job_position(id,name,description) values
(1,'董事长','公司重大决策签字人'),
(2,'部门经理','统筹各部门日常工作'),
(3,'市场专员','负责产品的宣传营销渠道'),
(4,'文员','维持公司内部运转');

insert into employee(id,name,job_id,supervisor,join_date,salary,bonus,depart_id) values
(1001,'川建国',1,null,'2016-11-11','120000.0',null,20),
(1002,'伊万卡',2,1001,'2016-12-12','60000.0','20000.0',30),
(1003,'默克尔',2,1001,'2016-11-30','50000.0','14000.0',40),
(1004,'马斯克',2,1001,'2016-12-28','40000.0','10000.0',20),
(1005,'雷军',2,1001,'2017-01-15','30000.0','18000.0',10),
(1006,'罗翔',3,1004,'2017-01-20','20000.0','7000.0',20),
(1007,'林超',3,1004,'2017-02-12','18000.0','6000.0',20),
(1008,'董佳宁',3,1004,'2017-02-24','18000.0','5000.0',20),
(1009,'欣桐',4,1002,'2017-03-09','16000.0','4000.0',30),
(1010,'白骨精',4,1003,'2017-04-04','12000.0',null,40),
(1011,'蜘蛛精',4,1003,'2018-05-15','10000.0',null,40),
(1012,'鲁智深',3,1004,'2018-06-18','20000.0',null,20),
(1013,'孙二娘',4,1002,'2018-09-19','20000.0','5000.0',30),
(1014,'手工耿',4,1005,'2019-07-14','16000.0','14000.0',10);

insert into salary_grade(grade, low_salary, high_salary) values
(1,1,15000),
(2,15001,20000),
(3,20001,30000),
(4,30001,50000),
(5,50001,999999);

select *
from job_position;-- 更改此处表名查看各表内容

各表详情如下

-- 开始查询
-- 1.查询所有员工信息，员工编号，姓名，工资，职务名，职务描述
-- 隐式内连接，这里涉及员工表和职务表，是一对多的关系
select em.id, em.name, em.salary, jp.name, jp.description
from employee em,
     job_position jp
where em.job_id = jp.id;
-- 显式内连接
select em.id, em.name, em.salary, jp.name, jp.description
from employee em
         join job_position jp on em.job_id = jp.id;

-- 2.查询所有员工信息，员工编号，姓名，工资，职务名，职务描述，部门名，部门位置
-- 隐式内连接
select em.id, em.name, em.salary, jp.name, jp.description, dp.name, dp.location
from employee em,
     job_position jp,
     department dp
where em.job_id = jp.id
  and em.depart_id = dp.id;
-- 显式内连接
select em.id, em.name, em.salary, jp.name, jp.description, dp.name, dp.location
from employee em
         join job_position jp on jp.id = em.job_id
         join department dp on em.depart_id = dp.id;

-- 查询员工姓名，工资，工资等级，等级区间，注意正则*还可以这样这样用
-- 隐式
select name, salary, sg.*
from employee,
     salary_grade sg
where salary >= low_salary
  and salary <= high_salary;-- 本例种除了正则表达式，别处无需使用别名
-- 显式
select name, salary, sg.*
from employee
         join salary_grade sg on salary between low_salary and high_salary;

-- 4.查询所有员工信息，员工编号，姓名，工资，职务名，职务描述，部门名，部门位置，工资等级，工资区间
-- 隐式
select em.id,
       em.name,
       em.salary,
       jp.name,
       jp.description,
       dp.name,
       dp.location,
       sg.*
from employee em,
     job_position jp,
     department dp,
     salary_grade sg
where em.job_id = jp.id
  and em.depart_id = dp.id
  and salary >= low_salary
  and salary <= high_salary;
-- 显式
select em.id,
       em.name,
       em.salary,
       jp.name,
       jp.description,
       dp.name,
       dp.location,
       sg.*
from employee em
         join job_position jp on em.job_id = jp.id
         join department dp on dp.id = em.depart_id
         join salary_grade sg on salary between low_salary and high_salary;

-- 5.查询部门编号，名称，位置，人数
select depart_id, count(*) from employee group by depart_id;-- 这个查询构成子表，临时，虚拟表，提供所需人数统计
-- 隐式
select *
from department,
     (select depart_id, count(*) from employee group by depart_id) temp
where temp.depart_id = department.id;-- 此处临时表必须要别名，否则depart_id字段会引用员工表的，报错
-- 显式
select *
from department
         join (select depart_id, count(*) from employee group by depart_id) temp
              on temp.depart_id = department.id;