MySQL学习六-＜基础篇＞-多表查询

本篇文章使用到的表结构

emp表

dept 表

 

 

内连接 

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

select e.name,d.name from emp e,dept d where e.dept_id = d.id;

显式内连接

SELECT 字段列表 FROM 表1 [ INNER] JOIN 表2 ON 连接条件...;

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

外连接

左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件...;

select e.name,e.dept_id,d.name from emp e left join dept d on e.dept_id = d.id;

右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件...;

select e.name,e.dept_id,d.name from emp e right join dept d on d.id = e.dept_id;

自连接

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

# 自连接
select  e.name,m.name from emp e join emp m on e.manager_id = m.id;
# 没有也要显示出来
select e.name,d.name from emp e left join emp d on e.manager_id = d.id;

联合查询-union , union all

SELECT字段列表FROM表...,

AUNION [ ALL ]

SELECT字段列表 FROM表B...,

#未去重
select * from emp where salary < 20000
union all
select * from emp where age > 50;
#去重
select * from emp where salary < 20000
union
select * from emp where age > 50;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);

根据子查询位置，分为:WHERE之后、FROM之后、SELECT 之后。

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。
常用的操作符: = <> > >= < <=

# 查看销售部的所有员工信息
select * from emp where dept_id = (select id from dept d where d.name = '销售部');

# 查看前台入职日期之后的入职人员
select *
from emp where entry_date > (select entry_date from emp where job = '前台');

列子查询

子查询返回的结果是一列（可以是多行)，这种子查询称为列子查询。

常用的操作符:IN 、NOT IN 、ANY . SOME、ALL

#查询市场部和销售部的所有员工信息
select * from emp where dept_id in (select id from dept where dept.name = '销售部' or dept.name = '市场部');
# 查询比财务部所有人工资都高的员工信息
select * from emp where salary > all (
    select salary from emp where dept_id in (
        select id from dept where dept.name = '财务部'
        )
);

# 查询比研发部任意一个人工资高的员工信息
select * from emp where salary > any (
    select  salary from  emp where dept_id in (
        select id from dept where dept.name = '研发部'
        )
);

行子查询

子查询返回的结果是一行（可以是多列)，这种子查询称为行子查询。

常用的操作符:= . <>、IN 、 NOT IN

# 查询与"lolita" 的薪资及直属领导相同的员工信息
select * from emp where (salary,manager_id) = (
    select salary,manager_id from emp where name = 'lolita'
    );

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符:IN

# 查询与”l“和”k“的薪紫相同且部门相同
select  * from emp where (salary,dept_id) in (
    select salary,dept_id from emp where name = 'l' or name = 'k'
    );
# 查询2022-01-30之后入职的员工及其部门信息
select * from (
              select  * from emp where entry_date > '2022-01-30'
                  ) e left join dept d on e.dept_id = d.id;