第六章 多表查询

第六章 多表查询

多表查询，也称为关联查询，指两个或更多表一起完成查询操作

前提条件，这些一起查询的表之间是有关系的（一对一，一对多），他们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

比如:员工表和部门表依靠部门编号进行关联

DESCRIBE employees;

DESCRIBE departments;

DESCRIBE locations;

查询员工名为'Abel'的人在那个城市工作?

SELECT * FROM employees WHERE last_name='Abel';

SELECT * FROM departments WHERE department_id=80;

SELECT * FROM locations WHERE location_id=2500;

2.出现了笛卡尔积的错误，缺少了多表的连接条件

查询结果是每个员工都和每个部门匹配了一遍

SELECT employee_id,department_name FROM employees,departments;

2.1理解笛卡尔

笛卡尔积是一个数学运算，假设我有两个集合X和Y，那么X和Y的笛卡尔积就是X和Y的所有可能组合，也就是第一个对象来自X

第二个对象来自Y的所有可能。组合的个数即为两个集合中元素个数的乘积数

SQL中，笛卡尔积也称为交叉连接，因为CROSS JOIN .在SQL99中也是使用CROSS JOIN表示交叉连接

他的作用就是可以把任意表进行连接，即使两张表不相关。在MySQL中如下情况会出现笛卡尔积

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

给表取一个别名

SELECT emp.department_id,dept.department_name FROM employees emp,departments dept WHERE emp.department_id=dept.department_id;

3.1 笛卡尔积的错误会在下面条件下产生

省略多个表的连接条件

连接条件无效

所有表中的所有行互连

为了避免笛卡尔积，可以在WHERE加入有效的连接条件

加入连接条件后，查询语法

SELECT table1.column,table2.COLUMN

FROM table1,TABLE2

WHERE table1.column=table2.COLUMN

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

可以给表一个别名，在select和where中使用表的别名

如果有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;

---------------------------------------------------------------------------------------------------------------------------------------

4.多表查询的分类

角度1 ：等值连接  VS   非等值连接

角度2 ：自连接    VS   非自连接

角度3 : 内连接    VS   外连接

4.1 等值连接  VS   非等值连接

DESCRIBE job_grades;

SELECT * FROM job_grades;

SELECT last_name,salary,grade_level

FROM employees e,job_grades j

-- WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

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

4.2 自连接 VS 非自连接

SELECT * FROM employees;

查询员工的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 ;

4.3 内连接 VS 外连接

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

SELECT emp.department_id,dept.department_name FROM employees emp,departments dept WHERE emp.department_id=dept.department_id;

外连接:合并具有同一列的两个以上的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行。

外连接分类:左外连接 、右外连接 、满外连接

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

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

练习：查询所有的员工的last_name,department_name信息

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

---------------------------------------------------------------------------------------------------------------------------------------

常用的SQL标准

SQL主要有两个标准，分别是SQL92和SQL99.92和99代表了标准的提出时间。

实际上最重要的标准就是SQL92和SQL99，一般来说SQL92的形式更简单，但是写得SQL语句会比较长，可读性比较差。而SQL99，语法更复杂，但可读性更高。

SQL92有500页 SQL99有1000多页。实际上SQL99以后，很少有人掌握所有内容，因为确实太多了，就好比我们用windows和Linux很少掌握

全部的内容一样，我们只需要掌握一些核心的功能。

SQL92和SQL99是经典的SQL标准，也分别叫做SQL-2和SQL-3标准

SQL92语法实现内连接

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

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

MySQL是支持此种方式的

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

SQL语法实现内连接

SELECT last_name,department_name FROM employees e JOIN departments d ON e.department_id=d.department_id;

SELECT last_name,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 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;

5.满外连接（FULL OUTER JOIN）

满外连接的结果=左右数据表匹配的数据+左表没有匹配的数据+右表没有匹配的数据

SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现

满外连接

mysql 不支持 full OUTER JOIN

-- SELECT last_name,department_name FROM employees e FULL JOIN departments d ON e.department_id=d.department_id;

UNION关键字的使用

合并查询结果

利用union关键字，可以给出多条select语句，并将他们的结果组合成单个结果集，合并时，两个表对应的列数和数据类型必须相同，并且互相对应。

各个select语句之间使用UNION或UNION ALL 关键字分割

语法格式:

SELECT column FROM TABLE1

UNION

SELECT column FROM TABLE2

UNION操作符返回的是两个查询结果集的并集，去除重复记录

UNION ALL 操作符返回两个查询的结果集的并集，对于两个结果集的重复部门，不去重。

注意：执行union all 语句所需要的资源比union语句少，如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL 语句，以提高数据查询效率。

8.UNION 和UNION ALL 的使用

UNION :会去重操作

UNION ALL :不会去重

多表查询练习题:

1.显示所有员工的姓名，部门号和部门名称

SELECT emp.last_name,emp.department_id,dept.department_name

FROM employees emp LEFT OUTER JOIN departments dept

ON emp.department_id=dept.department_id;

2.查询90号部门员工的job_id和90号部门的location_id

SELECT  emp.job_id,dept.location_id

FROM employees emp,departments dept

WHERE emp.department_id=90 AND dept.department_id=90;

SELECT emp.job_id,dept.location_id

FROM employees emp JOIN departments dept

ON emp.department_id=dept.department_id

WHERE emp.department_id=90;

3.选择所有有奖金的员工的last_name,department_name,location_id,city

SELECT * FROM employees WHERE commission_pct IS NOT NULL;

SELECT emp.last_name,dept.department_name,dept.location_id,loc.city

FROM employees emp,departments dept,locations loc

WHERE emp.commission_pct IS NOT NULL AND emp.department_id=dept.department_id AND loc.location_id=dept.location_id;

SELECT emp.last_name,dept.department_name,dept.location_id,loc.city

FROM employees emp LEFT OUTER JOIN departments dept ON emp.department_id=dept.department_id LEFT JOIN locations loc ON dept.location_id=loc.location_id WHERE emp.commission_pct IS NOT NULL;

4.选择city在Toronto工作的员工的last_name,job_id,department_id,department_name,l.city

SELECT e.last_name,e.job_id,d.department_name,d.department_id ,l.city

FROM employees  e JOIN departments d  

ON e.department_id=d.department_id  JOIN  

locations l ON l.location_id=d.location_id

WHERE l.city='Toronto';

5.查询员工所在的部门名称，部门地址，姓名，工作，工资，其中员工所在的部门的部门名称为 'Executive'

SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary,d.department_name

FROM  locations l

JOIN  departments d   ON d.location_id=l.location_id

JOIN  employees e ON e.department_id=d.department_id  

WHERE d.department_name='Executive';

6.选择指定员工的姓名，员工号。以及他的管理者的姓名和员工号

SELECT e1.last_name,e1.job_id,e2.last_name,e2.job_id  

FROM  employees e1 JOIN employees e2  

ON e1.manager_id=e2.employee_id;

7.查询那些部门没有员工

SELECT d.department_id

FROM employees e RIGHT JOIN departments d ON e.department_id=d.department_id  

WHERE e.department_id IS NULL;

本题可以使用子查询

8.查询那个城市没有部门

SELECT l.location_id,l.city FROM

locations l left JOIN departments d  

ON l.location_id=d.location_id  

WHERE d.location_id IS NULL;

9.查询部门名为Sales和IT的员工信息