mysql 关系表上千万 关联查询 mysql关联表查询语句

5. 关联查询（多表查询）《重点掌握》

5.1 概念：

有的时候，我们的业务需求的数据不只是在一张表中，而是在两张或两张以上的表中，而这些表中通常都会存 
在着"有关系"的字段。那么此时的查询操作涉及到多表查询，我们称之为关联查询。 
案例：最简单的关联查询 
select * from emp,dept; 
select * from emp join dept;

5.2 写法分类

在进行多表关联查询时，通常有两种写法：
-第一种是在from子句中，直接写多个表名，表名之间使用逗号分隔开，如：
	select A.*,B.*,C.* from A,B,C where 关联条件
-第二种是在from子句中，多个表名之间使用join关键字连接，并在on关键字后面添加关联条件，此时where和on等价。
    select A.*,B.*,C.* from A join B on 关联条件
    select a.*,b.*,c.* from A join B on AB 关联条件 join C on AC 或 BC 关联条件
    select a.*,b.*,c.* from A join B join C on AB 的关联条件 and BC 或 AC 关联条件

5.3 笛卡尔积

-当做关联查询时，如果两张表中不存在关联字段，或者忘记写关联条件。那么会出现如下图所示的结果。
	-即表A中的每一条记录都回与表B中的所有记录进行匹配组合。
	例如表A中有m条记录。表B中有N条件。那么 匹配组合的记录数目为M*N，此值被称之为笛卡儿积。
	-通常没有意义。

5.4 等值查询

在做关联查询时，使用有关系的字段进行等值条件的限制操作。我们称之为等值查询。
select A.*,B.* from A,B where A.colName=B.colName;

5.5 join连接分类

join关联查询操作分为两大类：内连接和外连接，而外连接有细分为三种类型。参考下图

1.内连接： [inner] join
2.外连接(outer join)：(引出一个驱动表的概念：驱动表里的数据全部显示)
	-左外连接：left [outer] join,左表是驱动表
	-右外连接：right [outer] join,右表是驱动表
	-全外连接：full [outer] join, mysql不支持.两张表里的数据全部显示出来

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kgxFgTuU-1587780363559)(C:\Users\陈永豪\AppData\Roaming\Typora\typora-user-images\image-20200323230140138.png)]

内链接

语法：
from tableName1 [inner] join tableName2 on条件

注意：与等值查询的效果是一样的。

外连接

语法：
-左外连接
from tableNamel left join tableName2 on 条件
-右外连接
from tableNamel right join tableName2 on 条件
-全外连接
from tableNamel full [outer] join tableName2 on 条件

外连接的两张表分为驱动表和从动表。驱动表的数据都显示，从动表的数据只显示满足条件的数据。

• 左外连接左边的表就是主表。
• 右外连接右边的表是主表。

5.6 集合查询

union(去重)/union all(不去重)：

• 两个查询语句使用上述的关键字连接即可。
• 两个查询语句的字段名，字段个数，类型必须对应上，字段顺序尽可能一致，否则没有意义。

5.7 关联查询的说明

• 多个表用逗号分开，使用where进行条件关联
• 多个表用join连接，使用on进行条件关联
• 笛卡儿积一般无意义。
• 等值连接和内连接效果完全一致。
• 所谓自连接就是连接条件在自己表内。

6. 高级关联查询(子查询)《重点掌握》

6.1 简介

有的时候，**当一个查询语句A所需要的数据，不是直观在表中体现，而是由另外一个查询语句B查询出来的结果，那么查询语句A就是主查询语句，查询语句B就是子查询语句。**这种查询我们称之为高级关联查询，也叫做子查询。

子查询语句的返回数据形式：

• 返回单行单列
• 返回多行单列
• 返回单行多列
• 返回多行多列

子查询语句的位置可以在以下几个子句中

• 在where子句中: 子查询的结果可用作条件筛选时使用的值。
• 在from子句中: 子查询的结果可充当一张表或视图，需要使用表别名。
• 在having子句中: 子查询的结果可用作分组查询再次条件过滤时使用的值
• 在select子句中: 子查询的结果可充当一个字段。仅限子查询返回单行单列的情况。

6.2 在where子句中

# 需求：查询工资大于员工编号为7369这个员工的所有员工信息。
解析： 
第一步：目的是查询工资大于某一个数num的所有员工信息 
select * from emp where sal>num 
第二步：num的值7369员工的工资 
select sal from emp where empno = 7369; 
第三步：将主查询中的代词使用子查询语句替换 
select * from emp where sal>(select sal from emp where empno = 7369); 

# 需求：查询工资大于10号部门的平均工资的所有员工信息 
select * from emp where sal>(select avg(ifnull(sal,0)) from emp where deptno=10); 

# 需求：查询工资大于10号部门的平均工资的非10号部门的员工信息。 
select * from emp where sal>(select avg(ifnull(sal,0)) from emp where deptno=10) and deptno<>10; 

# 需求：查询与7369同部门的同事信息。 
select * from emp where deptno=(select deptno from emp where empno=7369) and empno<>7369;

6.3 在from子句中

# 需求：查询员工的姓名，工资，及其部门的平均工资。 
解析： 
第一步：先查询每个部门的平均工资 
select deptno,avg(ifnull(sal,0)) from emp group by deptno; 
第二步：将上一个查询语句的返回结果当成一张表，与员工表进行关联查询 
select A.ename,A.sal,B.avg_sal 
from emp A join (select deptno,avg(ifnull(sal,0)) avg_sal from emp group by 
deptno) B on A.deptno = B.deptno

6.4 在having子句中

# 需求：查询平均工资大于30号部门的平均工资的部门号，和平均工资 
select deptno,avg(ifnull(sal,0)) from emp group by deptno having 
avg(ifnull(sal,0))> 
(select avg(ifnull(sal,0)) from emp where deptno=30);

6.5 在select子句中

相当于外连接的另外一种写法 
# 查询每个员工的信息及其部门的平均工资，工资之和，部门人数 
select A.empno,A.ename,A.sal, 
(select avg(ifnull(sal,0)) from emp B where B.deptno=A.deptno) avg_sal, 
(select sum(sal) from emp C where C.deptno=A.deptno) sum_sal, 
(select count(*) from emp D where D.deptno=A.deptno) count_ 
from emp A;