mysql多表查询

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);

多表查询练习