[分享]多表查询_01

/*
SELECT ------
FROM------
WHERE ------
ORDER BY-----
LIMIT ------
*/

# 熟悉常见的几个表
DESC employees;

DESC departments;

DESC locations;

SELECT * from employees;


#2. 出现笛卡尔积的错误 

#错误原因：缺少了多表的连接条件
#查询员工名为'Abel'的人在哪个城市工作？
SELECT * FROM employees 
WHERE last_name = 'Abel';

SELECT * FROM departments
WHERE department_id = 80;

SELECT * FROM locations
WHERE location_id = 2500;

#思考：三张表合成一张表可不可以

# 锁定问题

#错误原因：缺少了多表的连接条件
# 错误的查询,每个员工与每个部门都匹配了一遍
SELECT employee_id,department_name
FROM departments,employees; # 2889条记录

# SQL92和SQL99是两个不同的SQL标准版本

SELECT employee_id,department_name
FROM employees CROSS JOIN departments;

SELECT * from employees; #107条记录

SELECT 2889/107

SELECT 2889/107 FROM DUAL;
#MySQL的DUAL是一个特殊的表,它只有一列和一行,用于执行一些无需实际表数据的操作。

SELECT * from departments; #27条记录

SELECT 27*107

/*

(a,b,c)
(x,y)

笛卡尔坐标系

几何 代数

y=x^2

埃瓦里斯特·伽罗瓦

*/

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

SELECT employee_id,department_name
FROM departments,employees

# 两个表的连接条件
WHERE employees.department_id=departments.department_id;

SELECT employee_id,department_name,department_id
FROM departments,employees
WHERE employees.department_id=departments.department_id;
# ambiguous 不明确的

#4. 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
SELECT employee_id,department_name,employees.department_id
FROM departments,employees
WHERE employees.department_id=departments.department_id;

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

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

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

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

# 如果给表起了别名，一旦在在SELECT和where中使用表的别名，则必须使用表的别名，而不能再使用表的原名
SELECT emp.employee_id,departments.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id = dept.department_id;


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

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