MySQL-- 多表查询，笛卡尔积错误

目录

多表查询

1,用分段式查找多表

2.尝试直接查找多表，发现笛卡尔积错误

3. 多表查询的正确方式：需要有连接条件

4. 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。

5. 可以给表起别名，在SELECT和WHERE中使用表的别名。

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

方法

---

多表查询

1,用分段式查找多表

熟悉常见的几个表

DESC employees;

DESC departments;

DESC locations;

#查询员工名为'Abel'的人在哪个城市工作？
SELECT * 
FROM employees
WHERE last_name = 'Abel';

SELECT *
FROM departments
WHERE department_id = 80;


SELECT *
FROM locations 
WHERE location_id = 2500;

不够高效

2.尝试直接查找多表，发现笛卡尔积错误

错误的原因：缺少了多表的连接条件

错误的实现方式：每个员工都与每个部门匹配了一遍。
 

SELECT employee_id,department_name
FROM employees,departments;  #查询出2889条记录

寻找原因

SELECT *
FROM employees;  #107条记录

SELECT 2889 / 107
FROM DUAL;

SELECT *
FROM departments; # 27条记录

我们发现，107*27=2889.相当于employees表上的所有人都与departments表中的部门匹配了一遍，缺少“筛选条件”，也就是连接条件。

3. 多表查询的正确方式：需要有连接条件

SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.department_id;

4. 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。

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

建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表。

5. 可以给表起别名，在SELECT和WHERE中使用表的别名。

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

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

比如下的操作是错误的：
 

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

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

 

#练习：查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;

方法

演绎式：提出问题1 ---> 解决问题1 ----> 提出问题2 ---> 解决问题2 ....

归纳式：总--分