MySQL中的多表联合查询功能操作

【字号：大中小】日期：2023-02-05 11:01:20浏览：3作者：猪猪

一.介绍

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

交叉连接查询[产生笛卡尔积，了解]
内连接查询(使用的关键字inner join -- inner可以省略)
外连接查询(使用的关键字outer join -louter可以省略)
子查询
表自关联

数据准备

-- 创建部门表
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");

交叉连接查询

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

格式

实现

结果

内连接查询

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

格式

操作

inner可以省略

操作

-- 查询研发部门的所属员工
-- 隐式内连接
select * from emp3 e ,dept3 d where e.dept_id =d.deptno and name ="研发部";
-- 显式内连接
select * from dept3 d join emp3 e on d.deptno =e.dept_id and name ="研发部"; 
-- 查询研发部和销售部的所属员工
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = "研发部" or name = "销售部") ; 
select * from dept3 a join emp3 b on a.deptno = b.dept_id and name in("研发部" ,"销售部") ; 
 
-- 查询每个部门的员工数,并升序排序
 
select 
	a.name,a.deptno,count(1) 
from dept3 a 
   join emp3 b on a.deptno = b.dept_id 
group by 
  a.deptno,name;
 
 
-- 查询人数大于等于3的部门，并按照人数降序排序
 
select
  a.deptno,
  a.name,
  count(1) as total_cnt
from dept3 a
	join emp3 b on a.deptno = b.dept_id
group by 
  a.deptno,a.name
having 
  total_cnt >= 3
order by 
  total_cnt desc;

外连接

外连接分为左外连接（left outer join)、右外连接(right outer join)，满外连接(full outer join)。注意: oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用unjion来达到目的。

格式

左外连接: 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 条件;

操作

直接用fulljion会报错

-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工
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嵌套的查询。

特点

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

单行单列:返回的是一个具体列的内容，可以理解为一个单值数据;单行多列:返回一行数据中多个列的内容;多行单列:返回多行记录之中同一列的内容，相当于给出了一个操作范围;多行多列:查询返回的结果是一张临时表

操作

-- 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄
select * from emp3 where age=(select max(age) from emp3 );
 
select eid,ename ,age from emp3 where age = (select max (age) from emp3);
-- 查询年研发部和销售部的员工信息,包含员工号、员工名字
select  * from emp3 where emp3.dept_id in (select deptno from dept3 d where d.name="研发部" or d.name="销售部");
 
select eid,ename , t.name from emp3 where dept_id in (select deptno, name from dept3where name ="研发部"or name ="销售部");
-- 查询研发部20岁以下的员工信息，包括员工号、员工名字，部门名字
 
-- 方式一：关联查询
select * from dept3 d join emp3 e on d.deptno =e.dept_id and (d.name="研发部" and e.age<20); 
-- 方式二：子查询
select eid,age ,ename , name from (select * from dept3 where name = "研发部")t1 , (select * from emp3 where age <20) t2 where  t1.deptno =t2.dept_id ;
 
select eid,age ,ename , name from (select * from dept3 where name = "研发部")t1 join  (select * from emp3 where age <20) t2 on t1.deptno =t2.dept_id ;

子查询关键字

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

1.ALL关键字
2.ANY关键字
3.SOME关键字
4.IN关键字
5.EXISTS关键字

all关键字

格式

特点

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

操作

any关键字和some关键字

格式

特点

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

操作

in关键字

格式

特点

IN关键字，用于判断某个记录的值,是否在指定的集合中
在IN关键字前边加上not可以将条件反过来

操作

exists关键字

格式

特点

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

操作

自关联查询

概念

MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时表必须给表起别名。

格式

操作

数据准备

-- 创建表，并建立自关联约束
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) ,
                             (2,"刘备",1),
                             (3,"关羽",2),
                            (4,"张飞",2),
                             (5,"曹操",1),
                            (6,"许褚",5),
                             (7,"典韦",5),
                             (8,"孙权",1) ,
                             (9,"周瑜",8),
                            (10,"鲁肃",8) ;

-- 进行关联查询
-- 1.查询每个三国人物及他的上级信息，如:关羽刘备
select * from t_sanguo ts ,t_sanguo ts2 where ts .manager_id  =ts2.eid  ;						
select ts.ename ,ts2.ename  from t_sanguo ts ,t_sanguo ts2 where ts .manager_id  =ts2.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;