资料
根据【MySQL数据库教程天花板,mysql安装到mysql高级,强!硬!】 整理
资料链接:
百度网盘:
链接:https://pan.baidu.com/s/1KboU_3EZJxrezMWZ2klP6g
提取码:1234
阿里云盘
1 为什么需要多表查询
多表查询,也称为关联查询,指两个或更多个表一起完成查询操作。
前提条件:这些一起查询的表之间是有关系的(一对一、一对多、多对多),它们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键。比如:员工表和部门表,这两个表依靠“部门编号”进行关联。
引入案例:查询员工名为’Abel’的人在哪个城市工作
# 先查询 Abel 所在的部门的部门编号 SELECT department_id FROM employees WHERE last_name='Abel'; # 部门编号 80 # 根据 Abel 所在部门的部门编号查询部门所在城市的城市编号 SELECT location_id FROM departments WHERE department_id=80; # 城市编号 2500 # 根据部门所在城市的城市编号查询城市名 SELECT city FROM locations WHERE location_id=2500; # Oxford
完成该查询,需要三条SQL语句进行三次查询,于是有了多表查询 — 将多张表进行联合查询,将多张表合并为一张表进行查询。
2 笛卡尔积(或交叉连接)的理解
SELECT last_name, department_name, city FROM employees, departments, locations WHERE last_name='Abel';
直接对三张表进行合并然后查询,会发现查询出来的数据不对,结果的条数远大于预期。(这是由于出现了笛卡尔积的错误,错误的原因:缺少了每个表之间的连接条件)
员工表中姓名为 Abel 的记录与每个部门和每个城市都匹配了一遍。
笛卡尔积是一个数学运算。
假设我有两个集合 X 和 Y,两个集合中的每个元素为一条记录,那么 X 和 Y 的笛卡尔积就是 X 和 Y 中的每条记录(每个元素)所有可能的组合组成的结果,即两个集合中的每条记录(每个元素)进行两两组合。组合的个数即为两个集合中记录条数(元素个数)的乘积数。
SQL92中,笛卡尔积也称为 交叉连接 ,英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。
它的作用就是可以把任意表进行笛卡尔积连接,即使这两张表不相关。
在MySQL中如下情况会出现笛卡尔积:
SELECT * FROM employees, departments; # 或 SELECT * FROM employees CROSS JOIN departments;
2.1 笛卡尔积分析与问题解决
- 笛卡尔积的错误会在下面条件下产生:
- 1.省略多个表的连接条件(或关联条件)
- 2.连接条件(或关联条件)无效
- 3.所有表中的所有行互相连接
为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件。
三张表的关联关系:
可以据此增加筛选条件,WHERE子句中写入连接条件,去除不需要的数据,避免笛卡尔积的产生。
在不同的表中有相同的列名时,在列名之前加上表名前缀避免报错:
SELECT last_name, department_name, city FROM employees, departments, locations WHERE last_name='Abel' AND employees.department_id=departments.department_id AND locations.location_id = departments.location_id;
- 建议:从sql优化的角度,建议多表查询时,每个字段前都指明其所在的表。
- 因为如果不增加字段所在的表,在SQL查询时数据库还要查询该字段在那个表中,会影响查询的效率,如果指定了字段所在的表,则数据库可以直接找字段所在的表,增加查询效率。
3 表的别名
- 可以给表起别名,在 SELECT 和 WHERE 中使用表的别名。
- 在执行查询语句时,执行顺序为
FROM => SELECT => WHERE => ...
,所以表的别名可以在 SELECT 和 WHERE 中使用。 - 使用别名可以简化查询。
- 列名前使用表名前缀可以提高查询效率。
SELECT e.last_name, d.department_name, l.city FROM employees e, departments d, locations l WHERE e.last_name='Abel' AND e.department_id=d.department_id AND l.location_id = d.location_id;
如果给表起了别名,一旦在 SELECT 或 WHERE 中使用表名的话,则必须使用表的别名,而不能再使用表的原名。
4 连接多个表
如果有n个表实现多表的查询,则需要至少n-1个连接条件
连接 n个表,至少需要n-1个连接条件。比如,连接三个表,至少需要两个连接条件。
如图:
员工表和部门表通过部门编号进行关联,部门表与城市表通过城市编号进行关联。
查询员工的employee_id,last_name,department_name,city:
# 查询员工的employee_id,last_name,department_name,city SELECT e.employee_id, e.last_name, d.department_name, l.city FROM employees e, departments d, locations l WHERE e.employee_id=d.department_id AND d.location_id=l.location_id
5 多表查询的分类
- 等值连接 vs 非等值连接
- 自连接 vs 非自连接
- 内连接 vs 外连接
5.1 等值连接 vs 非等值连接
5.1.1 等值连接
表的连接条件中,选取的是两张表中指定字段相等的记录
# 查询员工的employee_id,last_name,department_name,city SELECT e.employee_id, e.last_name, d.department_name, l.city FROM employees e, departments d, locations l WHERE e.employee_id=d.department_id AND d.location_id=l.location_id;
5.1.2 非等值连接
表的连接条件中,选取的不是两张表中指定字段相等的记录
工资等级表:
查询员工的工资等级:
SELECT employees.last_name, employees.salary, job_grades.grade_level FROM employees, job_grades WHERE employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;