MYSQL-多表查询

一、概述

1、定义

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

2、前提条件

这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

3、多表查询分类关系

分类：  隐式内连接内连接（查交集）显示内连接左外连接（差左表+包括交集的部分）
连接查询   外连接  右外连接（查右表+包括交集部分）自连接：当前表与自身的连接查询标量子查询（查询结果为单个值，比如数字、字符串、日期等）列子查询（查询的结果只有一列）子查询    行子查询（查询的结果只有一行）表子查询（查询的结果是一个表，为多行或列）

4、准备数据

dept表和emp表：

dept部门表：

create table dept (id int auto_increment primary key comment 'id',name varchar(50) not null comment '部门名称'
) comment '部门表';insert into dept (id, name)
values (1, '研发部'),(2, '市场部'),(3, '财务部'),(4, '销售部'),(5, '总经办'),(6, '人事部');

emp员工表：

create table emp(id int auto_increment primary key ,name varchar(50) not null ,age int,job varchar(20) comment '职位',salary int ,entrydate date comment '入职时间',managerid int comment '直属领导id',dept_id int comment '所在部门id'
) comment '员工表';insert into emp
values ( 1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5 ),( 2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1 ),( 3, '杨晓', 33, '开发', 8400, '2000-11-03', 2, 1 ),( 4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1 ),( 5, '陈玉存', 43, '开发', 10500, '2004-09-07', 3, 1 ),( 6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1 ),( 7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3 ),( 8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3 ),( 9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3 ),( 10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2 ),( 11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2 ),( 12, '何碧文', 19, '职员', 3750, '2007-05-09', 10, 2 ),( 13, '东方白', 19, '职员', 5500, '2009-02-12', 10, 2 ),( 14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4 ),( 15, '鱼梁洲', 38, '销售', 4600, '2004-10-12', 14, 4 ),( 16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4 ),( 17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null );

二、分类

1、连接查询

（1）、内连接

隐式内连接：
select    字段列表    from   表1,表2    where    条件;
显示内连接：
select    字段列表    from 表1 [inner]    join 表2 on    连接条件;

-- 查询每一个员工的姓名及关联的部门的名称
-- 隐式内连接
select emp.name, dept.name from emp, dept where emp.dept_id=dept.id;
-- 显示内连接
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;

查询结果：

（2）、外连接

左外连接：select   字段列表   from   表1   left [outer]   join 表2   on 条件; 

右外连接： select   字段列表  from 表1    right [outer]  join 表2   on 条件;

 -- 查询emp表的所有数据，和应于的部门信息(左查询)
select emp.*, dept.* from emp left outer join dept on emp.dept_id = dept.id;
-- 查询dept表的所有数据，和对于的员工信息(右查询)
select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;

查询结果：

左查询：

右查询：

（3）、自连接

select    字段列表    from 表a 别名a   join  表a 别名b   on   条件;

-- 查询员工及其所属领导的名字
select a.name, b.name from emp a join emp b on a.managerid=b.id;

 查询结果：

 

2、联合查询----union、union all

select 字段列表 from 表a
union [all]
select 字段列表 from 表b

对于联合查询就是把多次查询的结果合并起来，形成一个新的查询结果。

注意：

对于联合查询的多张表的列数必须保持一致，字段类型也要保持一致。
union all会将全部的数据合并在一起，union会对合并之后的数据去重。

-- 将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union all
select * from emp where age>50;-- 没有all重复满足条件的只出现一次
--  将薪资低于5000的员工和年龄大于50的员工查询出来
select * from emp where salary>5000
union
select * from emp where age>50;

查询结果：

union会进行去重：

3、子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

select * from 表1 where 字段=(select 字段 from 表2);

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

（1）、标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询
常用符号：=、<>、>、>=、<、<=

-- 查询销售部所有员工信息
select id from dept where name='销售部'; -- 先查询销售部门的id，结果为4
select * from emp where dept_id=4;-- 再查询销售部门中员工的信息
-- 合并为一个查询
select * from emp where dept_id=(select dept.id from dept where dept.name='销售部' );

查询结果：

（2）、列子查询

子查询的结果为一列(可以是多行)的，这种子查询为列子查询。

常用操作符：in     not in     any   some    alll

-- 列子查询
-- 查询销售部和市场部的所有员工信息
select id from dept where name='销售部' or name='市场部';--  查询销售部和市场部的id  结果为id为2 4
select * from emp where dept_id in (2,4); -- 查询两个部门的所有员工
-- 合并
select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');

查询结果：

（3）、行子查询

子查询返回的结果是一行(可以是多列)，这种子查询为行子查询
常用操作符：=、<>、in、not in

-- 行子查询-- 查询与张无忌的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid)=(select salary, managerid from emp where name='张无忌');

查询结果：

（4）、表子查询

子查询的结果是多行多列这种查询为表子查询
常用操作符：in

-- 表子查询
-- 查询与鹿杖客和宋远桥的职位和薪资相同的员工信息
select * from emp where (job, salary) in ( select job, salary from emp where name in ('鹿杖客', '宋远桥'));

查询结果：