mysql 表连接和视图查询性能对比 mysql连表查询效率

多表联合查询主要分为四种:

1.交叉联合查询

2.内连接查询

3.外连接查询

4.子查询

5.自关联查询

一:交叉联合查询

1>实质:求多表的总集,也是最基础的查询

2>介绍:交叉联合查询能够汇总多张表的数值,如有A,B两张表,那么则会从A表的每一行,与B表的每一行数据进行联合查询,最后得到一个笛卡尔集,同时的,也会产生大量无用的数据

3>操作:

-- 关键词:select * from A,B,C.....;

select * from bumen1,emp1;

二:内连接查询

1>实质:求各个表之间的交集

2>介绍:

   1.内连接查询是在交叉联合查询作为一个基础之上,通过使用某些条件对其进行限定筛选数据,从交叉联合查询的笛卡尔集当中寻求交集,除掉那些冗余的数据,得到有用的答案

   2.内连接查询操作也分为两种方式,一种是隐式内查询(SQ92)版本的,一种是显式内查询(SQ99)版本的,两者在查询效率上并没有太大的差异

-- 隐式查询(SQL92) SELECT * from A,B where .....;
-- 显式查询(SQL98) SELECT * from A [inner] join B on .....;

3>操作

-- 一:查询各个部门所有的人员信息
-- 显式
SELECT * from emp1 inner join bumen1
on detpno = dept_id;


-- 隐式查询:
SELECT * from emp1,bumen1 where detpno = dept_id;
-- 二:查询研发部以及销售部所属员工
SELECT * from emp1 a join bumen1 b
on detpno=dept_id && (b.name='研发部' || b.name ='销售部'); 

-- 查询每个部门当中大于三的部门人数,并且按照升序进行排列
select bumen1.name,COUNT(bumen1.name) '人数'
from bumen1 
join emp1 
on detpno=dept_id 
group by detpno
HAVING count(bumen1.name)>3
order by count(bumen1.NAME)
;

-- 查询每个部门的员工数量,并且按照升序进行排列
SELECT b.name,count(1) 
from emp1 a
join bumen1 b
on  detpno=dept_id
GROUP BY detpno 
order by count(1);

-- 切记! group by 之后要跟having!

TIPS:使用过group by进行分组之后的条件限定记得使用HAVING!

三:外连接查询

1>种类:外连接查询主要分为三种形式

        1.左连接查询: left [outer] join

        2.右连接查询:right [outer] join

        3.满连接查询: full [outer] join

2>实质:

1.以左连接查询为例子:

        有A,B两张表,总的集合为C,那么左连接查询会得到A表的所有数据,如果A,B表有交集同样会显示出来,但是不显示除了交集之外的B表的数据,如果右表没有匹配的值,查询结果处显示左边A表的所有信息,但对于B表的查询如果没有结果则显示为NULL,右连接查询与此相似

2.满连接查询:

        而对于满连查询来说,是查询了所有表的所有数据
        

        注意:oracle里面有满连接,但是在MYSQL当中对于满连接的支持并不好,因此我们可以使用union来达到目的!

3>操作:

-- 查询那些部门有员工,那些部门没有员工
use mydb3;

select * from bumen1 left outer join emp1 on detpno = dept_id;
-- 左外连接,左边没有空值,右外连接右边没有空值
-- 查询那些员工有部门,那些员工没有部门
select * from bumen1 right join emp1 on detpno=dept_id;

-- 通过使用满外连接来展现表中的数据---对于union的运用
select * from bumen1 left outer join emp1 on detpno=dept_id
UNION
SELECT * from bumen1 right outer join emp1 on detpno=dept_id;

四:子查询:

1>实质:子查询实际上就是对于查询数据的嵌套再嵌套(select的嵌套)

2>种类:根据子查询类型进行分类,主要包括有三大类

        1.将子查询的结果当作为一个值:对应:一行一列

        2.将子查询的结果当作多个值:对应:一列多行

        3.将子查询结果当作一张表:对应:

                1.一行多列

                2.多行多列

3.1>操作:

-- 一:查询研发部和销售部门的员工信息以及人数
方法一:内连接查询:
select * from bumen1 join emp1 on deptno=dept_id && (bumen1.name='研发部' || bumen1.name='销售部'); 
方法二:分步查询:
1.先查询研发部门或者是销售部门的编号
select deptno from bumen1 where name in ('销售部','研发部');
2.将查询过后两个部门的ID再在员工当中进行相应的匹配
TIPS:需要注意的是:在这里有两个结果.所以可以使用in的形式来接结果
select * from emp1 where dept_id in (select deptno from bumen1 where name in ('销售部','研发部'));

-- 二:查询研发部门20以下的员工信息,包括员工号,员工姓名,以及部门的名字
方法一:分布查询:
1.先查询研发部门的编号是多少
select deptno from bumen1 where name ='研发部';
2.再在表中进行查询年龄为限制为20岁以下的
select * from emp1 where age<20;
3.将两个表当作是两个不同的表再使用内显性查询即可
select * from (select deptno from bumen1 where name ='研发部') a
join 
(select * from emp1 where age<20 ) b
on a.deptno = b.dept_id
;

3.2:子查询的一些关键字:

-- 子查询小技巧
子查询当中的关键子:
1.ALL关键子  (包括查询结果的所有数值)
1.ANY关键子  (表示满足查询结果:其中的任意一个即可)
1.SOME关键子  (可以当作是ANY的一个别名,用途跟ANY是一样的)
1.IN关键子  (可以是查询结果也可以不是,是其中的任何一个就行)
1.EXISTS关键子 (判断查询的最后有没有结果)




ALL的使用:
-- 一:查询不属于任何一个部门的员工信息
select * from emp1 where dept_id!=all(SELECT deptno from bumen1);


ANY的使用:
-- 二:查询年龄大于'1003'部门任意一个员工年龄的员工信息
select * from emp1 where age>any(select age from emp1 where 
dept_id='1003');

IN的使用:
-- 特点1:IN关键子,可以用来判断某个记录的值,是否再指定的集合当中
-- 特点2:在in的关键字之前也可以加上not将条件反过来
-- 三:查询研发部以及销售部的所有员工信息,包含员工号,以及员工名字
select * from emp1 where dept_id in(select deptno from bumen1 where NAME in('研发部','销售部'));
 
 
 exists的用法,作用其实跟IN是差不多的
 EXISTS的使用:
 特点:
 1.如果在子查询当中有结果数据,至少有一行结果输出,那么该exists输出的结果就是TRUE,外层的查询能够继续的往下进行执行,相反,没有的话就执行为FALSE,外层的查询结果不成立
 2.EXISTS后面的查询结果不返回任何的值,仅仅只返回是TRUE还是FALSE,并且仅仅只有为TRUE的时候条件成立
 -- 操作:
 -- 查询公司是否有大于20岁的员工,有则输出
 select * from emp1 a where EXISTS(SELECT * from emp1 where a.age>20);
 
-- 查询所有有相关部门的员工信心
select * from emp1 a where exists(select * from bumen1 b where a.dept_id=b.deptno);

五:自连接查询:

1>实质:有时候需要对一张表的某些行,或者列的数据进行一定的操作,让他们之间建立起来联系,也就是一张表自己跟自己关联

2>特点:对于子关联查询来说,必须,使用,别名!

3>操作:

-- 关键词语:FOREIGN key 表[列名] REFERENCES 表[列名];
-- 实质上跟之前的外键约束差不多

create database mydb4;

use mydb4;

create table if not EXISTS sanguo(
eid int primary key,
ename varchar(20),
manager_id int,
foreign key (manager_id) REFERENCES sanguo(eid)
);

insert into sanguo VALUES(1,'刘协',NULL);
insert into sanguo VALUES(2,'刘备',1);
insert into sanguo VALUES(3,'关羽',2);
insert into sanguo VALUES(4,'张飞',2);
insert into sanguo VALUES(5,'曹操',1);
insert into sanguo VALUES(6,'典韦',5);
insert into sanguo VALUES(7,'孙权',1);
insert into sanguo VALUES(8,'周瑜',8);
insert into sanguo VALUES(9,'鲁肃',8);

-- 在一张表当中表示出来各个三国人物和他们的上司
select a.ename,b.ename from sanguo a join sanguo b on  a.manager_id=b.eid; 

-- 在一张表当中表示出来所有的人物,并展现出来他们各自的上司;
SELECT a.ename,b.ename from sanguo a left join sanguo b on a.manager_id=b.eid;
-- 在这里使用了外连接查询的左连接查询

-- 以三列的形式显示出上下级关系:比如:张飞 刘备 刘协
select a.ename,b.ename,c.ename from sanguo a 
 left join sanguo b on a.manager_id=b.eid
 left  join sanguo c
on b.manager_id=c.eid;

                                                                         感谢观看🙇!