多表联合查询主要分为四种:
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;
感谢观看🙇!