【十，MySQL】基础篇--多表查询（内连接、外连接、自连接、联合查询、子查询）

一，内连接
inner可以省略

练习：

#===========================================隐式内连接==================================================
# 1，查询每一个员工的姓名，及关联部门的名称（隐式内连接实现）
select emp.name as 姓名, dept.name as 部门 from emp,dept where emp.dept_id = dept.id;

# 2，查询每一个员工的姓名，及关联部门的名称（显式内连接实现）
select a.name as 姓名 , b.name as 部门 from emp a join dept b on a.dept_id = b.id;

二，外连接

练习：

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

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

三，自连接（可以是内连接，也可以是外连接）


练习：

#===========================================自连接==================================================
# 要将一张表堪称两张表
# 1，查询员工，及其所属领导的名字(使用内连接)
select a.name as 姓名,b.name as 直系领导 from emp a
join emp b on a.managerid = b.id;

# 2，查询所有员工emp，及其领导的名字emp，如果员工没有领导，也要查询出来（使用外连接）
select a.name as 姓名 , b.name as 直系领导 from emp a
left join emp b on a.managerid = b.id;

四，联合查询

练习：

#===========================================联合查询==================================================
# 1，查询所有年龄大于20的员工，和所有薪资低于7000的员工
select * from emp where age > 20
union all
select * from emp where salary < 7000;
# 对查询的结果去重
select * from emp where age > 20
union
select * from emp where salary < 7000;

五，子查询

1，标量子查询

练习：

# 标量子查询
# 1，查询“研发部”的所有员工信息
# ①查询“研发部”的id
# ②根据研发部门id，查询员工信息
select * from emp where dept_id = (select id from dept  where name = '研发部');
# 2，查询在“井中月”入职之后的所有员工信息
# ①查询“井中月”的入职日期
select entrydate from emp where name = '井中月';
# ②查询查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '井中月');

2.列子查询

练习：

# 列子查询
# 1，查询“销售部”和“市场部”的所有员工信息
# ①查询“销售部”和“市场部”的部门id
# ②查询员工表的部门id在“销售部”和“市场部”
select * from emp where dept_id in (select id from dept where name in ('销售部','市场部'));

# 2，查询比财务部所有人工资都高的员工信息
# ①查询所有“财务部”的员工薪资
# ②比“财务部”所有人工资都高的员工信息
select * from emp where salary > all (select a.salary from emp a
left join dept b on a.dept_id = b.id
where b.name = '财务部');
# 实现方式不止一种
select * from emp where salary > (select max(a.salary) from emp a
left join dept b on a.dept_id = b.id
where b.name = '财务部');

# 3，查询比研发部其中任意一人高的员工信息（任意满足其一）
# ①查询所有“研发部”的员工薪资
# ②比研发部其中任意一人高的员工信息
select * from emp where salary > any (select a.salary from emp a
left join dept b on a.dept_id = b.id
where b.name = '研发部');

3，行子查询

练习：

# 行子查询
# 1，查询与”王语嫣“直系领导和薪资相同的员工信息
# ①查询”王语嫣“的直系领导和薪资
# ②与”王语嫣“直系领导和薪资相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '王语嫣');

4，表子查询

# 表子查询
# 1，查询与”周芷若“、”王五“职位和薪资相同的员工信息
# ①查询”周芷若“、”王五“的职位和薪资
# ②与”周芷若“、”王五“职位和薪资相同的员工信息
# 满足子表其一即可
select * from emp where (job,salary) in (select job,salary from emp where name in ('周芷若','王五'));

# 2，查询入职日期是”2019-01-01“之后的员工信息，及其部门信息
# ①查询入职日期是”2019-01-01“之后的员工信息  将这个子查询作为一张子表，再去联查部门表
# ②查询这部分员工对应的部门信息
select * from (select * from emp where entrydate > '2019-01-01') a
left join dept b on a.dept_id = b.id;