【MySQL学习记录】第六章：多表查询（内连接、外连接、等值查询、非等值查询）

1. 熟悉几张表

DESC employees;
DESC departments;
DESC locations;

查询员工名为 "Abel"的工作城市

SELECT * 
FROM employees
WHERE last_name = 'Abel';

SELECT *
FROM departments
WHERE department_id = 80;

SELECT city
FROM locations
WHERE location_id = 2500;

2. 为什么使用多张表

1. 解决存储时数据冗余的问题
2. 加载数据到内存时，数据少、速度快
3. 维护简单

3. 多表查询实现

笛卡尔积错误

查询employee_id，department_name

SELECT employee_id, department_name
FROM employees, departments;	# 共2889条记录

/*
出错原因：每个员工都与每个部门匹配了一遍，出现笛卡尔积错误

产生原因：
缺少连接条件
*/

笛卡尔积（交叉连接）

正确方式

查询employee_id，department_name

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

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

查询employee_id，department_name，department_id

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

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

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

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

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

如果给表起了别名，就必须使用别名，不可以再用表的原名

练习：查询员工的employee_id, last_name, department_name, city

SELECT emp.employee_id, emp.last_name, dept.department_id, loc.city
FROM employees emp, departments dept, locations loc
WHERE emp.`department_id` = dept.`department_id` AND dept.`location_id` = `loc.location_id`;

若有n个表，则至少需要n-1个连接条件

4. 多表的分类

等值连接 VS 非等值连接

非等值连接举例：

查询员工last_name, salary, grade_level

SELECT last_name, salary, grade_level
FROM employee e, job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

自连接 VS 非自连接

自连接

查询员工姓名，员工id，及其管理者的id和姓名