MySQL多表查询

MySQL多表查询

多表查询查询的结果是多张表中的字段信息

多表查询的方式：

• 交叉连接查询
• 等值连接查询
• 内连接查询
• 左外连接查询
• 右外连接查询
• 全连接查询
• 自连接查询
• 子连接查询

（重点以子连接查询为主，子连接的查询效率较高）

如何选择是单表查询还是多表查询？

（1）根据查询结果来看，如果查询的结果是多张表中的数据，则是多表联查

（2）根据查询条件来判断，如果查询条件是当前查询结果中的非直接字段，则为多表联查

创建员工表和部门表并插入大量数据

-- 创建部门表并插入数据
create table dept(
	dno int primary key auto_increment,
	dname varchar(10) not null,
	loc varchar(20)	
)

insert into dept values
		(10,'营销部','武汉'),
		(20,'教学部','武汉'),
		(30,'研发部','郑州');
		
		
-- 创建员工表并插入数据
create table emp(
	empno int primary key auto_increment,
	ename varchar(20) not null,
	job varchar(10),
	mgr int,     -- 当前员工的上级领导编号
	hiredate date,
	sal double,
	comm double,
	deptno int,
	foreign key(deptno) REFERENCES dept(dno)
)

insert into emp(empno,ename,job,mgr,hiredate,sal,Comm,deptno) values
 (1001,'甘宁','文员','1013','2020-12-17',8000.00,null,20),
 (null,'黛琦丝','销售员','1006','2001-02-20',16000.00,3000.00,30),
 (null,'殷天正','销售员','1006','2001-02-22',12500.00,5000.00,30),
 (null,'刘备','经理','1009','2001-04-02',29750.00,null,20),
 (null,'谢逊','销售员','1006','2001-09-28',12500.00,14000.00,30),
 (null,'关羽','经理','1009','2001-05-01',28500.00,null,30),
 (null,'张飞','经理','1009','2001-09-01',24500.00,null,10),
 (null,'诸葛亮','分析师','1004','2007-04-19',30000.00,null,20),
 (null,'曾阿牛','董事长',null,'2001-11-17',50000.00,null,10),
 (null,'韦一笑','销售员','1006','2001-09-08',15000.00,0.00,30),
 (null,'周泰','文员','1008','2007-05-23',11000.00,null,20),
 (null,'程普','文员','1006','2001-12-03',9500.00,null,30),
 (null,'庞统','分析师','1004','2001-12-03',30000.00,null,20),
 (null,'黄盖','文员','1007','2002-01-23',13000.00,null,10);

1.交叉连接查询

交叉连接又称为笛卡尔积，就是把两张表中的数据全部查询出来，简单的拼接在一起

格式：select … from 表1，表2

select * from dept,emp;

最终得到的结果是表1的数据个数×表2的数据个数

笛卡尔积没有实际意义

2.等值连接

在等值连接中进行条件筛选，把主外键有关系的数据保留下来

格式：select … from 表1,表2 where 表1.外键=表2.外键

-- 查询员工信息及其所属的部门信息
select * from emp,dept where emp.deptno=dept.dno

-- 注意事项：
-- 1.如果主外键字段不相同，则可以省略 表名.
select * from emp,dept where deptno=dno
-- 2.查询的结果会按照表名的先后顺序，在查询结果中展示
select * from dept,emp where deptno=dno
-- 3.可以对select后的值进行改变（字段、distinct、四则运算、函数等）

-- 查询员工的姓名，工资及所属的部门名称
select emp.ename,emp.sal,dept.dname from emp,dept where deptno=dno 

select ename,sal,dname from emp,dept where deptno=dno

-- 查询员工的信息及所属的部门名称
select emp.*,dept.dname from emp,dept where deptno=dno

-- 查询所有的工资高于10000的员工信息及其所属的部门信息
select * from emp,dept where deptno=dno and sal>10000

-- 查询工资高于10000的经理的员工信息及部门信息
select * from emp,dept where deptno=dno and sal>10000 and job='经理'

-- 查询工资高于10000的经理的员工信息及部门信息，工资按照升序排序
select * from emp,dept where deptno=dno and sal>10000 and job='经理' order by sal asc 

3.内连接

内连接是join连接查询的一种，查询结果与等值连接查询的结果一摸一样

格式：select … from 表1 inner join 表2 on 表1.外键=表2.主键

注意：inner 可以省略

-- 查询所有的工资在20000以上的员工信息及所属部门信息
select * from emp INNER JOIN dept on dept.dno=emp.deptno and emp.sal>20000

-- 查询10号部门的经理和30号部门的高于15000以上销售人员的信息及所属的部门信息
select*from emp inner join dept on deptno=dno
and((deptno=10 and job='经理')or(deptno=30 and job='销售员'and sal<15000))

外连接包括左外连接和右外连接

4.左外连接

格式：select … from 表1 left outer join 表2 on 表1.外键=表2.主键

注意：

1，outer可以省略
2，左外连接是把左边的表当成主表，右边的表为从表，主表中的数据全部展示，从表中如果有数据对应则展示，如果没有数据对应则以null填充

例子：

插入一条没有部门编号的数据，然后查询员工信息及其所属的部门信息
select * from emp,dept where emp.deptno=dept.dno
(查询后发现少了一条员工信息，即没有部门编号的那个员工)

问：该怎么查询出所有员工信息（包括没有部门编号的那个员工）

答：使用左外连接，把emp表当成主表，即可显示其中所有的数据

​

5.右外连接

格式：select … from 表2 right outer join 表2 on 表1.外键=表2.主键

注意：
1，outer可以省略
2，右外连接是把右边的表当成主表，左边的表为从表，主表中的数据全部展示，从表中如果有数据对应则展示，如果没有数据对应则以null填充

-- 查询所有的员工信息及其所属的的部门信息，包含那些没有部门的员工
-- 左外连接，emp在左边，所以emp是主表
select * from emp left outer join dept on deptno=dno
-- 右外连接,emp在右边，所以emp是主表
select * from dept right join emp on deptno=dno

-- 查询所有的部门信息及该部门下的所有员工，包含没有员工的部门
-- 左外连接，dept在左边，dept是主表
select * from dept left join emp on deptno=dno
-- 右外连接，dept在右边,dept是主表
select * from emp right join dept on deptno=dno

6.全连接

全连接可以把左右两张表中的数据全部展示出来。

格式：select … from 表1 full join 表2 on 表1.外键=表2.主键

注意：mysql默认不支持全连接查询，oracle和DB2支持

解决：

（1）并集查询 union

并集查询就是两条sql语句查询的结果实现并集合并【相同则去重展示，不同也要展示】

注意：如果使用union，则两条查询语句的结果字段顺序和个数必须一模一样

（2）非并集查询 union all

非并集查询就是两条sql语句查询的结果实现合并【相同的数据也不去重】

-- 查询所有员工的信息和部门信息，包含没有员工的部门和没有部门的员工
select * from emp full join dept on deptno=dno

-- 并集查询（去重）,将两句sql查询的结果合并即可得到想要的结果
select * from emp left join dept on deptno=dno-- 这条可以查出没有部门的员工
union
select * from emp right join dept on deptno=dno-- 这条可以查出没有员工的部门

-- 非并集查询（不去重）
select * from emp left join dept on deptno=dno
union all
select * from emp right join dept on deptno=dno

7.自连接

自连接就是把一张表看成多张表来使用。

相同的表取不同的别名

-- 查询员工的姓名及其对应的上级的姓名
select * from emp as yg,emp as sj-- 同一张表当成两张，一张当成员工表一张当成上级表
where yg.mgr=sj.empno

-- 查询员工的姓名及其对应的上级的姓名
select yg.ename,sj.ename from emp as yg join emp as sj on yg.mgr=sj.empno

-- 查询员工的信息及对应的上级信息，如果没有上级也要展示
select * from emp as yg left join emp as sj
on yg.mgr=sj.empno 

-- 查询入职时间早于其上级的员工信息和上级信息
select * from emp as yg,emp as sj
where yg.mgr=sj.empno and yg.hiredate<sj.hiredate

8.子连接

子连接就是把一条查询语句封装到另一条查询语句中

方式：

（1）把一条查询语句的结果当成另一条查询语句的条件

（2）把一条查询语句的结果当成一张虚拟的表，然后在此表中继续查询

使用：

单行单列 > >= < <= = != <>

单列多行 in ，not in ，any(任意值)，all（所有值）

any，all的用法：

‘>’ any （sql语句） 大于sql语句查询的数据的任意值

‘>’ all （sql语句） 大于sql语句查询的数据的所有值

-- 方式一---------------------
-- 查询教学部的员工信息
-- 步骤
-- 1.先查询教学部的编号
select dno from dept where dname='教学部'
-- 2.将1中的编号作为条件来查询员工信息
select * from emp where deptno=(select dno from dept where dname='教学部')

-- 查询工资高于刘备的员工姓名和工资
-- 1.先查询刘备的工资
select sal from emp where ename='刘备'
-- 2.把1中的工资作为条件来查询
select ename,sal from emp where sal>(select sal from emp where ename='刘备')

-- 查询教学部和研发部的员工信息
select * from emp where deptno in(20,30)

-- 1.先查询教学部和研发部的编号
select dno from dept where dname in('教学部','研发部')
-- 2.根据1的条件来查询员工信息
select * from emp where deptno in(select dno from dept where dname in('教学部','研发部'))

-- 查询工资高于部门30中的（所有的员工的工资）的员工信息
-- 1.查询部门30中的所有人的工资
select sal from emp where deptno=30
-- 2,大于1中查询到的工资即可
select * from emp where sal>all(select sal from emp where deptno=30)

-- 查询工资高于部门30中员工工资的员工信息（换句话说就是查询工资高于部门30中最低工资的员工信息）
-- 1，查询部门30中的工资
select sal from emp where deptno=30
-- 2，查询只要高于1中的工资的任意数据都可以显示
select * from emp where sal > any(select sal from emp where deptno=30)

-- 查询工资高于部门30中最低工资的员工信息
select * from emp where sal >(select min(sal) from emp where deptno=30 and deptno!=30)

-- 方式二-------------

-- 查询部门名称，部门地址，部门人数
-- 1 查询各个部门个有多少人
select deptno,count(1) from emp group by deptno
-- 2 把1查询后的结果当成虚拟表，与dept表联合查询即可
select * from dept,(select deptno,count(1) from emp group by deptno) as aa
where dept.dno=aa.deptno

-- 查询部门名称，部门地址，部门人数，部门的最高工资，总工资
-- 1 查询各个部门总人数，最高工资，总工资
select deptno,count(1) as co,max(sal) as ma,sum(sal) as su from emp group by deptno
-- 2 把1中查询的结果看成虚拟表与dept表联合查询
select dept.dname,dept.loc,aa.co,aa.ma,aa.su from dept,
(select deptno,count(1) as co,max(sal) as ma,sum(sal) as su from emp group by deptno) aa
where dept.dno=aa.deptno

-- 查询员工工资高于其所属部门的平均工资的员工信息和部门信息
-- 1 查询部门的编号及部门的平均工资
select deptno ,avg(sal) as ag from emp group by deptno
-- 2把1的结果看成虚拟表，与员工表和部门表联合查询
select * from emp,dept,
(select deptno ,avg(sal) as ag from emp group by deptno)as aa
where emp.deptno=aa.deptno and dept.dno=emp.deptno
and emp.sal>aa.ag

on和where 的区别：

（1）在内连接中

注意：内连接中除了表与表之间的关系连接条件之外的其他条件可以放置在where 后面；

多个条件放在on后面和放在where 后面，效果是一样的

-- 查询工资在20000以上的员工信息及所属部门信息
select * from emp inner join dept on deptno=dno and sal>200000
select * from emp inner join dept on deptno=dno where sal>200000

建议：on后面只放关系条件，剩余条件全部放在where后面

（2）在外连接中（左右外连接一样）

左外连接查询时，如果有普通的主表中的字段作为条件来查询时

（1）条件放在on之后，则主表中的不论是否满足此条件的数据都要展示，如果不满足条件的对应表中的以null填充

（2）条件放在where之后，则是先查询多表中的对应数据，然后再进行筛选，所以只展示满足条件的数据。

示例：

-- 查询工资高于200000员工信息及部门信息，使用左外连接查询
select * from emp left join dept on deptno=dno and sal>20000-- 这样写得不到想要的结果
select * from emp left join dept on deptno=dno where sal>20000-- 这样写才是正确的

结果展示如下：

结论：

建议所有的表与表之间的关系条件放在on之后，非关系条件全部放在where之后