【MySQL学习15】MySQL多表操作之联合查询

本节介绍多表联合查询的概念及应用，包括交叉连接查询、内连接查询、外连接查询、子查询及自关联查询。

概述

多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候,需要显示的数据来自多张表。

多表查询有以下分类：

Ø交叉连接查询 [产生笛卡尔积，了解]

语法：select * from A,B;

Ø内连接查询(使用的关键字 inner join -- inner可以省略)

隐式内连接（SQL92标准）：select * from A,B where 条件;

显示内连接（SQL99标准）：select * from A inner join B on 条件;

内连接查询示意

Ø外连接查询(使用的关键字 outer join -- outer可以省略)

左外连接：left outer join

select * from A left outer join B on 条件;

左外连接示意

右外连接：right outer join

select * from A right outer join B on 条件;

右外连接示意

满外连接: full outer join

select * from A full outer join B on 条件;

满外连接示意

Ø子查询

select的嵌套

Ø表自关联：

将一张表当成多张表来用

数据准备

-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
 
-- 给emp3表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002'); 
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

交叉连接查询

交叉连接查询返回被连接的两个表所有数据行的笛卡尔积
笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配
假如A表有m行数据，B表有n行数据，则返回m*n行数据
笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

语法：

select * from 表1,表2,表3….;

示例：

-- 交叉连接查询
select * from dept3,emp3;

两个表的笛卡尔积

当左边的部门号和右边的员工所属部门号一致，即为所要的结果。

需要的正确结果

内连接查询

内连接查询求多张表的交集。

语法1：隐式内连接（SQL92标准）

select * from A,B where 条件;

语法2：显示内连接（SQL99标准）

select * from A inner join B on 条件;

示例：

-- 查询每个部门的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;

-- 查询研发部和销售部的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
 
-- 查询每个部门的员工数,并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
 
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;

外连接查询

外连接分为左外连接（left outer join）、右外连接(right outer join)，满外连接(full outer join)。

注意：oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用union来达到目的。

左外连接：left outer join

select * from A left outer join B on 条件;

右外连接：right outer join

select * from A right outer join B on 条件;

满外连接: full outer join

select * from A full outer join B on 条件;

外连接示意图

示例：

-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工
use mydb3;
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
 
-- 查询哪些员工有对应的部门，哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
 
 
-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union 
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

子查询

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。

子查询可以返回的数据类型一共分为四种：

1.单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；

2.单行多列：返回一行数据中多个列的内容；

3.多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；

4.多行多列：查询返回的结果是一张临时表

示例：

-- 子查询
-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select eid,ename,age from emp3 where age = (select max(age) from emp3);
-- 查询年研发部和销售部的员工信息，包含员工号、员工名字
select * from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;
-- 查询研发部20岁以下的员工信息,包括员工号、员工名字，部门名字、
-- 子查询
  -- 1.先在部门表中查询研发部的信息
select * from dept3 where name = '研发部';       # 这是第一个表
  -- 2.在员工表中查询年龄小于30岁的员工信息 
select * from emp3 where age<30;                 # 这是第二个表
  -- 3.关联查询，
select * from (select * from dept3 where name = '研发部') t1 inner join (select * from emp3 where age <30) t2 on t1.deptno=t2.dept_id;
# t1和t2是别名

在子查询中，有一些常用的逻辑关键字，这些关键字可以给我们提供更丰富的查询功能，主要关键字如下:

`ALL`关键字

 select … from … where c> all(查询结果)

等价于：

select ... from ... where c > result1 and c > result2 and c > result3

注意：

ALL: 与子查询返回的所有值比较为true 则返回true
ALL可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

示例：

-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');
#select age from emp3 where dept_id = '1003’  语句返回的就是1003组里的年龄
                                   
-- 查询不属于任何一个部门的员工信息 
select * from emp3 where dept_id != all(select deptno from dept3);

ALL表示和所有的查询结果作比较。

`ANY`关键字和`SOME`关键字

select …from …where c > any(查询语句)
--等价于：
select ...from ... where c > result1 or c > result2 or c > result3

特点：

ANY:与子查询返回的任何值比较为true 则返回true
ANY可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。
表示制定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
SOME和ANY的作用一样，SOME可以理解为ANY的别名

示例：

-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');

`IN`关键字

select …from …where c in(查询语句)
--等价于：
select ...from ... where c = result1 or c = result2 or c = result3

注意：

• IN关键字，用于判断某个记录的值，是否在指定的集合中

• 在IN关键字前边加上not可以将条件反过来

示例：

-- 查询研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;

`EXISTS`关键字

select … from … where exists(查询语句)

特点：

该子查询如果“有数据结果”(至少返回一行数据)，则该EXISTS() 的结果为“true”，外层查询执行
该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()的结果为“false”，外层查询不执行
EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立
注意，EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字

示例：

-- 关键字exists
-- 查询公司是否有大于60岁的员工，有则输出前面的select * from emp3
-- 输出的是全表
select * from emp3 where exists(select * from emp3 where age > 60);
-- 输出的是大于60岁的
select * from emp3 a where exists(select * from emp3 where a.age > 60);
select * from emp3 a where eid in (select eid from emp3 where a.age > 60);
-- 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

自关联查询

一张表自己和自己关联，一张表当成多张表来用。注意自关联时表必须给表起别名。

语法：

select 字段列表 from 表1 a , 表1 b where 条件;
或者 
select 字段列表 from 表1 a [left] join 表1 b on 条件;

操作：

-- 创建表,并建立自关联约束
create table t_sanguo(
    eid int primary key ,
    ename varchar(20),
    manager_id int,
    foreign key (manager_id) references t_sanguo (eid)  -- 添加自关联约束
);

本节代码

use mydb3;

-- 创建部门表
create table if not exists dept3(
  deptno varchar(20) primary key ,  -- 部门号
  name varchar(20) -- 部门名字
);
 
-- 创建员工表
create table if not exists emp3(
  eid varchar(20) primary key , -- 员工编号
  ename varchar(20), -- 员工名字
  age int,  -- 员工年龄
  dept_id varchar(20)  -- 员工所属部门
);

-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
 
-- 给emp3表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002'); 
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

-- 交叉连接查询
select * from dept3,emp3;

-- 内连接
select * from dept3 inner join emp3 on deptno = dept_id;
-- 要是两个表的这一列的名字相同，则需要  表名.列名  这样的格式
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
-- 要是表的名太长，也可以给表写别名
select * from dept3 a inner join emp3  b on a.deptno = b.dept_id;
-- 查询研发部和销售部的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
-- 查询每个部门的员工数,并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
-- 查询人数大于等于3的部门，并按照人数降序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;


-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工
use mydb3;
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id; 
# 左外连接，以左表为主，右表没有的就是NULL

-- 查询哪些员工有对应的部门，哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
# 右外连接，以右表为主，左表没有的就是NULL

-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union 
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
# 满外连接
# union 会去重
# union all 不会去充，直接硬生生拼接


-- 子查询
-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select eid,ename,age from emp3 where age = (select max(age) from emp3);
-- 查询年研发部和销售部的员工信息，包含员工号、员工名字
select * from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;
-- 查询研发部20岁以下的员工信息,包括员工号、员工名字，部门名字、
-- 子查询
  -- 1.先在部门表中查询研发部的信息
select * from dept3 where name = '研发部';       # 这是第一个表
  -- 2.在员工表中查询年龄小于30岁的员工信息 
select * from emp3 where age<30;                 # 这是第二个表
  -- 3.关联查询，
select * from (select * from dept3 where name = '研发部') t1 inner join (select * from emp3 where age <30) t2 on t1.deptno=t2.dept_id;
# t1和t2是别名

 -- 关键字ALL
-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');
#select age from emp3 where dept_id = '1003’  语句返回的就是1003组里的年龄                                   
-- 查询不属于任何一个部门的员工信息 
select * from emp3 where dept_id != all(select deptno from dept3); 

-- 关键字ANY和SOME
--     ANY和SOME的作用是一样的
-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > any(select age from emp3 where dept_id = '1003') and dept_id!='1003';

-- 关键字in
-- 查询研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;

-- 关键字exists
-- 查询公司是否有大于60岁的员工，有则输出前面的select * from emp3
-- 输出的是全表
select * from emp3 where exists(select * from emp3 where age > 60);
-- 输出的是大于60岁的
select * from emp3 a where exists(select * from emp3 where a.age > 60);
select * from emp3 a where eid in (select eid from emp3 where a.age > 60);
-- 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);


-- 自关联查询
-- 创建表,并建立自关联约束
create table t_sanguo(
    eid int primary key ,
    ename varchar(20),
    manager_id int,   -- 上级领导的编号
    foreign key (manager_id) references t_sanguo (eid)  -- 添加自关联约束
);
-- 添加数据 
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);
-- 进行关联查询
-- 1.查询每个三国人物及他的上级信息，如:  关羽  刘备 
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;
select a.ename,b.ename from t_sanguo a, t_sanguo b where a.manager_id = b.eid;
-- 没有刘协，是老大，没有上级
-- 2.查询所有人物及上级，包括刘协
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
-- 3.查询所有人物的上级、上上级
select 
   a.ename,b.ename,c.ename    
from t_sanguo a 
   left join t_sanguo b on a.manager_id = b.eid
   left join t_sanguo c on b.manager_id = c.eid;

声明：内容来源于B站视频《2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程》，博客内容仅作学习参考使用。

【MySQL学习15】MySQL多表操作之联合查询

概述

数据准备

交叉连接查询

内连接查询

外连接查询

子查询

`ALL`关键字

`ANY`关键字和`SOME`关键字

`IN`关键字

`EXISTS`关键字

自关联查询

本节代码

※随机文章推荐※

※热评文章推荐※

※相关文章推荐※

发表评论取消回复
填写正确的邮箱地址，有利于及时收到回复哦~

概述

数据准备

交叉连接查询

内连接查询

外连接查询

子查询

ALL关键字

ANY关键字和SOME关键字

IN关键字

EXISTS关键字

自关联查询

本节代码

※随机文章推荐※

※热评文章推荐※

※相关文章推荐※

发表评论 取消回复 填写正确的邮箱地址，有利于及时收到回复哦~

`ALL`关键字

`ANY`关键字和`SOME`关键字

`IN`关键字

`EXISTS`关键字

发表评论取消回复
填写正确的邮箱地址，有利于及时收到回复哦~