MySQL学习---多表查询相关概念和操作

MySQL学习—多表查询相关概念和操作

多表查询，也被称为关联查询，指两个或更多的表一起完成查询操作

前提条件：这些一起查询的表之间是有关系的（一对一，一对多）。他们之间有一定的关联字段，这个关联字段可能建立了外键，也可能没有建立外键

原始的多表查询

SELECT * 
FROM employees
where last_name='Abel';

SELECT *
FROM departments
where department_id=80;

SELECT *
FROM locations
WHERE location_id=2500;

多表查询具体实现

• 笛卡尔积错误（交叉连接）
  • 笛卡尔积是一个数学运算。假设存在两个集合X和Y，则X和Y的笛卡尔积就是X和Y的所有可能组合，也就是第一个对象来自于X，第二个对象来自与Y的所有可能，组合的个数即为两个集合中元素个数的乘积数
  • 错误的实现方式，每个员工都与每个部门匹配了一遍

SELECT employee_id,department_name
FROM employees,departments;

• 多表查询的正确方式，需要有连接条件

SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

• 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
• 建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表

SELECT employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;

• 可以给表取别名，在SELECT和WHERE中使用表的别名
  • 如果给表起了别名，一旦在SELECT或WHERE中使用表名的话，则必须使用表的别名，而不能再使用表的原名

SELECT employee_id,department_name,t1.department_id
FROM employees t1,departments t2
WHERE t1.department_id=t2.department_id;

• 如果有n个表实现多表的查询，则需要至少n-1个连接条件

查询员工的employee_id,first_name,last_name,department_name,city

SELECT employee_id,first_name,last_name,department_name,city
FROM employees,departments,locations
WHERE employees.department_id=departments.department_id 
AND departments.location_id=locations.location_id;

多表查询分类

• 等值连接vs非等值连接
• 自连接vs非自连接
• 内连接vs外连接

等值连接vs非等值连接

等值连接->上面描述的都为等值连接

非等值连接

SELECT last_name,salary,grade_level
FROM employees,job_grades
WHERE employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;

SELECT last_name,salary,grade_level
FROM employees,job_grades
WHERE employees.salary >= job_grades.lowest_sal AND employees.salary <= job_grades.highest_sal;

自连接 VS 非自连接

• 非自连接：上面描述的都为自连接
• 自连接：从自己当前的表从新选取

SELECT emp.employee_id,emp.last_name,mgr.employee_id magr_id,mgr.last_name mgr_name
FROM employees emp,employees mgr
WHERE emp.manager_id=mgr.employee_id;

内连接和外连接

内连接：上面写的都是内连接

• 合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行

SQL92语法实现内连接

SELECT emp.employee_id, dep.department_name
FROM employee emp, department dep
WHERE emp.`department_id` = dep.`department_id`;

SQL99语法实现内连接

SELECT emp.employee_id, dep.department_name
FROM employees emp JOIN departments dep
ON emp.department_id= dep.department_id;


SELECT emp.employee_id, dep.department_name,city
FROM employees emp INNER JOIN departments dep
ON emp.department_id= dep.department_id
INNER JOIN locations l
ON dep.location_id=l.location_id;

外连接：

• 合并并包含同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行
• 外连接的分类
  • 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的 行 ，这种连接称为左外连接
    • 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
  • 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的 行 ，这种连接称为右外连接
  • 满外连接：同时满足左右连接

SQL92语法实现外连接

​ 使用+ ---- MySQL不支持SQL92语法中外连接的写法

SELECT emp.employee_id,emp.last_name,mgr.employee_id magr_id,mgr.last_name mgr_name
FROM employees emp,employees mgr
WHERE emp.manager_id(+)=mgr.employee_id;

SQL99语法实现外查询

SQL99语法使用JOIN … ON方式实现多表查询。这种方式能够解决外连接的问题。MySQL是支持此种方式

• 左外连接

SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id=d.department_id;

SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id;

• 右外连接

SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id;

SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id;

• 满外连接–MySQL不支持FULL OUTER JOIN，Oracle支持

SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id=d.department_id;

UNION使用

合并查询结果

利用UNION关键字，可以给出多条SELECT语句，并将他们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间可以使用UNION或UNION ALL关键字分隔。

• UNION 操作符返回两个查询的结果集的并集，去除重复记录
• UNION ALL 操作符返回两个查询的结果集的并集，对于两个结果集的重复部分，不去重

注意：在执行UNION ALL语句所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或则不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率

7种JOIN的实现

中图：内连接

SELECT last_name,department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id;

左上图：左外连接

SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id;

右上图：右外连接

SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id;

左中图

SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id IS NULL;

右中图

SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL;

左下图：满外连接

• 方式一：左上图 UNION ALL 右中图

SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id
UNION ALL
SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL;

• 方式二：左中图 UNION ALL 右上图

SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id;

右下图

SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id IS NULL;

SQL99语法新特性

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING 连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用。比如：

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN…USING 可以简化 JOIN ON 的等值连接