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. 为什么使用多张表
- 解决存储时数据冗余的问题
- 加载数据到内存时,数据少、速度快
- 维护简单
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和姓