mysql多表查询
为什么需要多表查询?
放在一张表里字段太多,一次加载的更少,效率低,其次还会造成数据的冗余。
查询员工名为’Abel’的人在哪个城市工作?
多表查询如何实现?
笛卡尔积错误
错误的实现方式:每个员工都与每个部门匹配了一遍
SELECT employee_id,department_name FROM employees,departments;
查询出2889条记录
SELECT * FROM employees;
107条记录
SELECT 2889/107 FROM DUAL
SELECT * FROM departments;
27条记录
多表查询的正确方式:需要有连接条件:
SELECT employee_id,department_name FROM employees,departments WHERE employees.`department_id` = departments.`department_id`;
department_id出现在两个表中不指明就会报错,需要指定字段在哪个表中
SELECT employee_id,department_name,department_id FROM employees,departments WHERE employees.`department_id` = departments.`department_id`;
正确写法:
SELECT employee_id,department_name,employees.department_id FROM employees,departments WHERE employees.`department_id` = departments.`department_id`;
建议:从sql优化的角度建议多表查询时,每个字段前都指明其所在的表。
多表查询分类
等值连接和非等值连接
之前where后面带=都是等值连接
非等值连接的例子:
SELECT e.last_name,e.salary,j.grade_level FROM employees e,job_grades j WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
自连接和非自连接
自连接例子:
查询员工id,员工姓名以及其管理者的id和姓名
SELECT emp.`employee_id`,emp.`last_name`,mgr.`employee_id`,mgr.`last_name` FROM employees emp,employees mgr WHERE emp.`manager_id` = mgr.`employee_id`;
内连接和外连接
内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表和另一个表不匹配的行。
外连接:合并具有同一列的两个以上的表的行,结果集中除了包含一个表和另一个表匹配的行之外,还查询到左表或右表中不匹配的行。
分类:左外连接,右外连接,满外连接
内连接
SELECT last_name,department_name FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
外连接:
查询所有的员工的last_name,department_id信息:
左外连接
SELECT last_name,department_name FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
右外连接:
SELECT last_name,department_name FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
满外连接:
mysql不支持
SELECT last_name,department_name FROM employees e FULL JOIN departments d
ON e.`department_id` = d.`department_id`;
UNION操作符:
返回两个查询的结果集并集,去除重复记录。
UNION ALL操作符:
返回两个查询的结果集并集,不去重。
尽量使用UNION ALL 语句以提高数据查询的效率。
自然连接:帮你自动查询两张连接表中所有相同的字段然后进行等值连接。
SELECT employee_id,last_name,department_name FROM employees e NATURAL JOIN departments d;
USING括号内填入要指定的同名字段。
SELECT employee_id,last_name,department_name FROM employees e JOIN departments d USING (department_id);
多表查询练习