数据库学习2024/3/16

多表查询

1.多表查询如何实现

错误的实现：每个员工和每个部门都匹配了一遍（出现笛卡尔积的错误）

缺少了多表的连接条件

SELECT employee_id,department_name #FROM employees CROSS JOIN departments; FROM employees,departments;* #查询记录太多了*

2.多表查询的正确查询条件：需要有连接条件

SELECT employee_id,department_name

FROM employees,departments

#两个表的连接条件

WHERE employees.department_id =

departments.department_id;#``可加可不加

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

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

SELECT employees.employee_id,departments.department_name,employees.department_id

FROM employees,departments

WHERE employees.department_id = departments.department_id;

4.可以给表起别名，在FROM中给表起别名

如果起了别名，在SELECT和WHERE中使用表名就一定要使用别名，不能使用表的原名

SELECT emp.employee_id,dept.department_name,emp.department_id

FROM employees emp,departments dept

WHERE emp.department_id = dept.department_id;

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

案例：查询员工的员工id，姓名，部门名称，城市

SELECT e.employee_id,e.last_name,d.department_name,l.city

FROM employees e,departments d,locations l

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id;

6.多表查询的分类

角度一：等值连接 vs 非等值连接

角度二：自连接 vs 非自连接

角度三：内连接 vs 非内连接

6.1等值连接 vs 非等值连接

SELECT e.last_name,e.salary,j.grade_level

FROM employees e,job_grades j

WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;

6.2自连接 vs 非自连接

自连接

案例：查询员工id，员工姓名，管理者id，管理者姓名

SELECT emp.employee_id,emp.last_name,mar.employee_id,mar.last_name

FROM employees emp,employees mar

WHERE emp.manager_id = mar.employee_id;

6.3内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行

SELECT employee_id,department_name

FROM employees e,departments d

WHERE e.department_id = d.department_id;

6.4外连接:合并具有同一列的两个以上的表的行,结果集中除了包含一个表与另一个表匹配的行

 还查询到了左表 或 右表中不匹配的行
外连接的分类：左外连接、右外连接、满外连接

左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，
这种连接称为左外连接。

右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，
这种连接称为右外连接。

案例：查询所有员工的last_name,department_name信息（因为有所有，所以是用外连接）

SELECT employee_id,department_name

FROM employees e,departments d

WHERE e.department_id = d.department_id;

SQL92语法实现内连接：见上

SQL92语法实现外连接：使用（+）-----------MySQL不支持SQL92语法中的外连接的写法

SELECT employee_id,department_name

FROM employees e,departments d

WHERE e.department_id = d.department_id(+);#在orcale可以使用

SQL99语法实现外连接：使用JOIN...ON的方式实现多表查询。这种方式也能解决外连接的问题， MySQL是支持这种方式的

6.5 SQL99语法如何实现多表的查询

SQL99语法实现内连接：

#两个表 SELECT employee_id,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; #多个表，直接多个JOIN 表名 ON 连接条件 SELECT employee_id,department_name,city FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id;

#左外连接 SELECT employee_id,department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;

#同理：右外连接 SELECT employee_id,department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;

#满外连接：mysql不支持FULL JOIN SELECT employee_id,department_name FROM employees e FULL JOIN departments d ON e.department_id = d.department_id;

7.UNION 和UNION ALL的用法

UNION会执行除重

UNION ALL 不会会执行除重

结论：如果明确知道合并后的数据不存在重复数据或者要去除重复的数据，则尽量使用UNION ALL不使用UNION

中图：内连接

SELECT employee_id,department_name

FROM employees e JOIN departments d

ON e.department_id = d.department_id;

左上图：左外连接

SELECT employee_id,department_name

FROM employees e LEFT JOIN departments d

ON e.department_id = d.department_id;

右上图：右外连接

SELECT employee_id,department_name

FROM employees e RIGHT JOIN departments d

ON e.department_id = d.department_id;

左中图：

SELECT employee_id,department_name

FROM employees e LEFT JOIN departments d

ON e.department_id = d.department_id

WHERE d.department_name IS NULL;

右中图：

SELECT employee_id,department_name

FROM employees e RIGHT JOIN departments d

ON e.department_id = d.department_id

WHERE e.department_id IS NULL;

左下图：满连接

SELECT employee_id,department_name

FROM employees e LEFT JOIN departments d

ON e.department_id = d.department_id

WHERE d.department_name IS NULL

UNION ALL

SELECT employee_id,department_name

FROM employees e RIGHT JOIN departments d

ON e.department_id = d.department_id;

右下图：

SELECT employee_id,department_name

FROM employees e RIGHT JOIN departments d

ON e.department_id = d.department_id

WHERE e.department_id IS NULL

UNION ALL

SELECT employee_id,department_name

FROM employees e LEFT JOIN departments d

ON e.department_id = d.department_id

WHERE d.department_name IS NULL;

数据库学习笔记