MySQL数据库------多表查询

一，数据准备

-- 数据准备
drop table if exists dept;-- 删除表结构
create table dept(
    id      int         auto_increment  comment 'ID' primary key,
    name    varchar(50) not null        comment '部门名称'
)comment '部门表';
insert into dept(id, name) values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');


drop table if exists emp;-- 删除表结构
create table emp(
    id          int    auto_increment               comment 'ID' primary key,
    name        varchar(50) not null                comment '姓名',
    age         tinyint unsigned                    comment '年龄',
    job         varchar(20)                         comment '职位',
    salary      int                                 comment '薪资',
    entrydate   date                                comment '入职时间',
    managerid   int                                 comment '直属领导ID',
    dept_id     int                                 comment '部门ID'
) comment '员工表';
insert into emp (id, name, age, job, salary, entrydate, managerid, dept_id)
values (1,'张三',12,'总裁',20000,'2001-01-01',null,3),
       (2,'李四',22,'开发',14000,'2008-01-01',4,4),
       (3,'王五',32,'开发',8000,'2012-01-01',4,3),
       (4,'赵六',42,'经理',12000,'2022-01-01',1,1),
       (5,'田七',52,'财务',7000,'2014-01-01',2,1),
       (7,'王八',62,'开发',14000,'2018-01-01',3,null),
       (6,'何九',72,'运维',14000,'2005-01-01',4,4);


drop table if exists salgrade;-- 删除表结构
create table salgrade(
    grade int comment '等级',
    losal int comment '最低',
    hisal int comment '最高'
)comment '薪资等级表';
insert into salgrade(grade, losal, hisal)
values (1,0,3000),
       (2,3001,5000),
       (3,5001,8000),
       (4,8001,10000),
       (5,10001,15000),
       (6,15001,20000),
       (7,20001,25000),
       (8,25001,30000);

二， 内连接

-- 多表查询 笛卡尔积
select * from emp,dept;

-- 消除无效的笛卡尔积
select * from emp,dept where emp.dept_id = dept.id;


-- 内连接

-- 隐式内连接

-- 1，查询每一个员工的姓名，及关联的部门的名称（隐式内连接）

-- 表结构：emp,dept
-- 连接条件：emp.dept_id=dept.id
select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;

-- 起别名
select e.name,d.name from emp e,dept d where e.dept_id=d.id;


-- 显式内连接

-- 1，查询每一个员工的姓名，及关联的部门的名称（显式内连接）------INNER JOIN ... ON...

-- 表结构：emp,dept
-- 连接条件：emp.dept_id=dept.id
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;

-- inner可省略
select e.name,d.name from emp e join dept d on e.dept_id = d.id;

三，外连接 

-- 左外连接  查询表1的所有数据 包含表1和表2的交集部分
-- 右外连接  查询表2的所有数据 包含表1和表2的交集部分


-- 1，查询emp表的所有数据，和对应的部门信息（左外连接）
-- 表结构：emp,dept
-- 连接条件：emp.dept_id=dept.id
select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;

-- outer可以省略
select e.*,d.name from emp e left join dept d on e.dept_id = d.id;


-- 2，查询dept表的所有数据，和对应的员工信息（右外连接）
select d.*,e.* from emp e right outer join dept d on e.dept_id = d.id;

-- 右外连转化左外连接（两者可互相转化）
select d.*,e.* from dept d left outer join emp e on e.dept_id = d.id;

 四，自连接

-- 自连接（必须对表起别名）
-- 1，查询员工 及其 所属领导的名字
-- 表结构 emp
select a.name,b.name from emp a left join emp b on a.managerid=b.id;


-- 2，查询所有员工 emp 及其领导的名字emp ,如果员工没有领导也需要查询出来呢
select a.name '员工',b.name  '领导' from emp a left join emp b on b.id = a.dept_id;

 五，联合查询

-- 联合查询
-- 1，将薪资低于10000的员工，和年龄大于50的员工全部查询出来
select * from emp where salary<10000
union all
select * from emp where age>50;

-- 去重
select * from emp where salary<10000
union
select * from emp where age>50;

-- 列数需要相同，这里是错误示范
select * from emp where salary<10000
union
select name from emp where age>50;

 六，子查询（嵌套查询）

-- 标量子查询

-- 1，查询’销售部‘的所有员工信息
-- a，查询‘销售部’的部门ID
select id from dept where name ='销售部';
-- b，根据’销售部‘的ID再查询员工信息
select * from emp where dept_id = (select id from dept where name ='销售部');


-- 2，查询在李四入职之后的信息
-- a，查新李四的入职日期
select entrydate from emp where name = '李四';
-- b，查询指定入职日期之后 入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '李四');



-- 列子查询
-- 常用操作符   IN,NOT IN,ANY,SOME,ALL

-- 1，查询’销售部‘和’市场部‘的所有员工信息
-- a，查询销售部和市场部的部门ID
select id from dept where name = '销售部' or name = '市场部';
-- b，根据部门ID，查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');


-- 2，查新比财务部工资都高的员工信息
-- a，查询所有财务部人员工资
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');
-- b，比财务部所有人工资都高的员工信息
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '财务部'));


-- 3，查询比研发部其中任意一人工资高的员工信息
-- a，查询研发部所有人的工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
-- b，比研发部其中任意一人工资高的员工
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));



-- 行自查询
-- 常用操作符 =,<>,IN,NOT,IN

-- 1，查询与李四的薪资及直属领导相同的员工信息
-- a，查询李四的薪资及领导
select salary,managerid from emp where name = '李四';
-- b，查询与李四的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '李四');



-- 表子查询
-- 常用操作符 IN

-- 1，查询与李四，王五的职位和薪资相同的员工信息
-- a，查询李四，田七的职位和薪资
select job,salary from emp where name='李四' or name='田七';
-- b，查询与李四，王五的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name='李四' or name='田七');


-- 2，查询入职日期是 2006-01-01 之后的员工信息，及其部门信息
-- a，查询入职日期是 2006-01-01 之后的员工信息
select * from emp where entrydate > '2006-01-01';
-- b，查询这部分员工对应的部门信息
select e.*,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id=d.id;

 七，课后习题

-- -----------------------------课后习题----------------------------- --


-- 1，查询员工的姓名，年龄，职位，部门信息（隐式内连接）
-- 表：emp,dept
-- 连接条件emp.dept_id = dept.id
select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id=d.id;


-- 2，查询年龄小于30岁的员工姓名，年龄，职位，部门信息（显式内连接）
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id = d.id where e.age<30;


-- 3，查询拥有员工的部门ID，部门名称
select d.id,d.name from emp e,dept d where e.dept_id=d.id;
-- 去重distinct
select distinct d.id,d.name from emp e,dept d where e.dept_id=d.id;


-- 4，查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示
select e.*,d.name from emp e left join dept d on d.id = e.dept_id where e.age>40;


-- 5,查询所有员工的薪资等级
-- 表：emp,salgarder
-- 连接条件：emp.salary >= salgarder.losal and emp.salary <= salgarder.hisal
select e.name,s.grade from emp e, salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
-- 或者
select e.name,s.grade from emp e, salgrade s where e.salary between s.losal and  s.hisal;


-- 6，查询研发部所有员工的信息及工资等级
-- 表：emp,dept,salgrade
-- 连接条件：emp.salary between salgarder.losal and salgarder.hisal，emp.dept_id = dept.id
-- 查询条件：dept.name='研发部’
select e.*, s.grade
from emp e,
     dept d,
     salgrade s
where e.dept_id = d.id
  and (e.salary between s.losal and s.hisal)
  and d.name = '研发部';


-- 7，查询研发部员工的平均工资
select avg(e.salary) from emp e,dept d where e.dept_id=d.id and d.name='研发部';


-- 8，查询工资比李四高的员工信息
-- a，查询李四的薪资
select salary from emp where name='李四';
-- b，查询比他工资高的员工信息
select * from emp where salary > (select salary from emp where name='李四');


-- 9，查询比平均薪资高的员工信息
-- a，查询员工的平均薪资
select avg(salary) from emp;
-- b，查询比平均薪资高的员工信息
select * from emp where salary > (select avg(salary) from emp);


-- 10，查询低于本部门平均薪资的员工信息
-- a，查询指定部门平均薪资  部门1
select avg(e1.salary) from emp e1 where e1.dept_id=1;
-- b，查询低于本部门平均薪资的员工信息
select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id=e2.dept_id);


-- 11，查询所有部门信息，并统计部门的员工人数
-- a，统计部门的员工人数
select count(*) from emp where dept_id=1;
-- b，查询所有部门信息，并统计部门的员工人数
select d.id,d.name,(select count(*) from emp e where e.dept_id=d.id) '人数' from dept d;