MySQL多表查询

一、多表查询简介

二、笛卡尔乘积

当一个连接条件无效或被遗漏时，其结果是一个笛卡尔乘积 (Cartesian product)，其中所有行的组合都被显示。第一个表中的所有行连接到第二个表中的所有行。一个笛卡尔乘积会产生大量的 行，其结果没有什么用。你应该在 WHERE 子句中始终包含一个有效的连接条件，除非你有特殊的需求，需要从所有表中组合所有的行。

三、多表查询分类 

• sql92标准：内连接(等值连接 、非等值连接 、 自连接)。
• sql99标准：内连接、外连接(左外、右外、全外(MySQL不支持全外连接))、交叉连接。

四、多表查询细解

4.1等值连接

等值连接也被称为简单连接 (simple joins) 或内连接 (inner joins)。

等值连接特点：

• 多表等值连接的结果为多表的交集部分；
• n表连接，至少需要n-1个连接条件；
• 多表不分主次，没有顺序要求； 
• 一般为表起别名，提高阅读性和性能；
• 可以搭配排序、分组、筛选….等子句使用；

①增加搜索条件

可以使用AND操作符增加搜索条件

 除连接之外，可能还要求用 WHERE 子句在连接中限制一个或多个表中的行。

②限定表中的列

在多表查询中使用表前缀限制修饰列名

表名.列名

需要在 WHERE 子句中用表的名字限制列的名字以避免含糊不清。没有表前缀，DEPARTMENT_ID 列可能来自 DEPARTMENTS 表，也可能来自 EMPLOYEES 表，这种情况下需要添加表前缀来执行查询。 如果列名在两个表之间不相同，就不需要限定列。但是，使用表前缀可以改善性能，因为MySQL服 务器可以根据表前缀找到对应的列。 必须限定不明确的列名也适用于在其它子句中可能引起混淆的那些列，例如 SELECT子句或 ORDER BY 子句。

③使用表别名

表别名定义原则

• 表别名不易过长，短一些更好。
• 表别名应该是有意义的。
• 表别名只对当前的 SELECT 语句有效。

4.2 非等值连接 

一个非等值连接是一种不同于等值操作的连接条件。 EMPLOYEES 表 和JOB_GRADES A 表之间的关系有一个非等值连接例子。在两个 表之间的关系是EMPLOYEES 表中的 SALARY 列必须是 JOB_GRADES 表的 LOWEST_SALARY 和HIGHEST_SALARY 列之间的值。使用不同于等于 (=) 的操作符获得关系。

4.3 自连接

连接一个表到它自己。有时需要连接一个表到它自己。为了找到每个雇员的经理的名字，则需要连接EMPLOYEES 表到它自己，或执 行一个自连接。 

这里有点难以理解，再此解释一下，从始至终，此案例只有一张表，这张表有三个字段，员工编号，姓名，对应的经理编号，我们要清楚，经理也是员工。我们用员工对应的经理编号=经理自己的编号。

4.4 交叉连接(CROSS JOIN)

4.5 自然连接(NATURAL JOIN)

连接只能发生在两个表中有相同名字且数据类型相同的列上。如果列有相同的名字，但数据类型不同，NATURAL JOIN 语法会引起错误。

4.6 内连接(INNER JOIN)

语法：

SELECT 查询列表 

FROM 表1

INNER JOIN 连接表(INNER关键字可省略)

ON 连接条件;

用ON子句指定更多的连接条件：

4.7 外连接(OUTER JOIN)

孤儿数据：(Orphan Data)

 孤儿数据是指被连接的列的值为空的数据。

①左外连接

左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的行，该查询也会取回 EMPLOYEES 表中所有的行 

②右外连接

右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的行，该查询也会取回 DEPARTMENTS 表中所有的行。

③全外连接(FULL OUTER JOIN)

MySQL 中不支持 FULL OUTER JOIN 连接

五、分页查询

数据的索引是从0开始的，所有说limit 0,3；意思是从第1条记录开始，查询3条记录。limit子句和limit offset子句放在SQL语句句尾。

①LIMIT子句

LIMIT 开始位置，查询数量

②LIMIT OFFSET子句

LIMIT 查询数量 OFFSET 开始位置