一、多表查询讲解
1.主要笔记
-
多表查询,也称为关联查询,指两个或更多个表一起完成查询操作。
前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键。比如:员工表和部门表,这两个表依靠“部门编号”进行关联。
-
多表查询存在笛卡尔积错误的问题
- 笛卡尔积的错误会在下面条件下产生,为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件。
- 省略多个表的连接条件(或关联条件)
- 连接条件(或关联条件)无效
- 所有表中的所有行互相连接
- 笛卡尔积的错误会在下面条件下产生,为了避免笛卡尔积, 可以在 WHERE 加入有效的连接条件。
-
加入连接条件后,查询语法:
-
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2; #连接条件
-
在 WHERE子句中写入连接条件。
-
-
正确写法:
-
#案例:查询员工的姓名及其部门名称 SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id;
-
-
代码示例:(包含:多表查找初始举例、笛卡尔积错误举例、表的别名、不明确的查询举例等见注释)
# 多表查询
#员工表、部门表、部门地点等
DESC employees;
DESC departments;
DESC locations;
# 查询员工名为’abel‘的人在那个城市工作
SELECT *
FROM employees
WHERE last_name='abel';
SELECT *
FROM departments
WHERE department_id=80;
SELECT *
FROM locations
WHERE location_id=2500;
# 多表查询实现
# 1. 错误的示范:出现笛卡尔积错误(每个员工都与每个部门匹配了一遍)
SELECT employee_id,department_name
FROM employees,departments#2889条数据
# from employees cross join departments #(和上面一样的效果)
# 错误原因:缺少了多表的连接条件
# 2.正确的方式:需要有连接条件
# 两个表的连接条件
WHERE employees.department_id=departments.department_id;
# 3.不明确的查询示范(下列查询会报错)
SELECT employee_id,department_name,department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;
# 原因:department_id在两个表里都存在,查询不明确报错ambiguous
# 改正确则改为:某表.department_id()
# 结论:如果查询语句中出现了多个表中都存在的字段,则必须指明此字段所在的表
# 建议:从sql优化的角度,建议多表查询时,每个字段前面都指明其所在的表
SELECT employees.employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;
# 4.可以给表起别名,在select和where中使用表的别名
# 注意:如果给表起了别名,一旦在select和where中使用表名的话必须使用别名,不能再使用原名
SELECT emp.employee_id,department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id=dept.department_id;
2.例题
- 练习:查询员工的employee_id,last_name,department_name,city
# 练习:查询员工的employee_id,last_name,department_name,city
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;
- 注意:如果有n个表实现多表的查询,则至少需要n-1个连接条件
- 原则上select每一个数据都指明是哪一个表的数据
- 如果给表起了别名,一旦在select和where中使用表名的话必须使用别名,不能再使用原名
3.笛卡尔积(或交叉连接)的理解
- 笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y,那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能 组合,也就是第一个对象来自于 X,第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素 个数的乘积数。
-
SQL92中,笛卡尔积也称为 交叉连接 ,英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交 叉连接。它的作用就是可以把任意表进行连接,即使这两张表不相关。在MySQL中如下情况会出现笛卡 尔积:
#查询员工姓名和所在部门名称 SELECT last_name,department_name FROM employees,departments; SELECT last_name,department_name FROM employees CROSS JOIN departments; SELECT last_name,department_name FROM employees INNER JOIN departments; SELECT last_name,department_name FROM employees JOIN departments;
二、多表查询的分类
角度1:等值连接和非等值连接
上面的查询都是等值连接,非等值连接指条件语句不是等于
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;
WHERE e.salary>=j.lowest_sal AND e.salary<=j.highest_sal;
角度2:自连接和非自连接
背景:比如某表有一列条件为它的上级id,但是它的上级也存在于该表,这时候存在自连接
# 角度2:自连接和非自连接
SELECT * FROM employees;
# 练习:查询员工id,员工姓名及其管理者的id和姓名
SELECT 员工.employee_id,员工.last_name,上级.employee_id,上级.last_name
FROM employees 员工,employees 上级
WHERE 员工.manager_id=上级.employee_id;
角度3:内连接和外连接(重点)
除了查询满足条件的记录以外,外连接还可以查询某一方不满足条件的记录。
-
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行 (上面写的都是内连接)
-
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的
-
外连接分类
- 左外连接
- 右外连接
- 满外连接
例题:查询所有员工的last_name,department_name
图一:
图二:
但是参考图一和图二可知,有一位员工是没有上级的,外连接查询则是把这位员工信息也显示出来
在sql92语法如下
# 练习:查询所有员工的last_name,department_name(一般题目出现‘所有’问的多半是外连接)
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id =d.department_id(+); # 需要使用左外连接
# sql92语法实现外连接:使用 + (但是MySQL不支持sql92语法中外连接的写法!)
解释:该字段在MySQL中不适用,在SQL92 中采用(+)代表从表所在的位置。即左或右外连接中,(+) 表示哪个是从表。
Oracle 对 SQL92 支持较好,而 MySQL 则不支持 SQL92 的外连接:Oracle 中如下
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;
在sql99语法中
- sql99语法:使用join …on的方式实现多表查询,这种方式也能解决外连接的问题,MySQL是支持该方式的
⭕️:SQL99语法实现内连接
# SQL99语法实现内连接
# 1.0
SELECT last_name,department_name
FROM employees e JOIN departments d
ON e.department_id =d.department_id;
# 2.0
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
如图:
⭕️:SQL99语法实现外连接
首先原则上使用99语法内连接如下(但是inner是可以省略的)
# 查询所有员工的last_name,department_name(一般题目出现‘所有’问的多半是外连接)
SELECT last_name,department_name
FROM employees e INNER JOIN departments d
ON e.department_id =d.department_id;
所以外连接(用outer表示外连接,left为左,right为右)(并且语句中的outer是可以省略的,因为left和right已经确定了为外连接)
# 查询所有员工的last_name,department_name(一般题目出现‘所有’问的多半是外连接)
#左外连接
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id =d.department_id;
# 查询所有员工的last_name,department_name(一般题目出现‘所有’问的多半是外连接)
# 右外连接
SELECT last_name,department_name
FROM employees e RIGTH OUTER JOIN departments d
ON e.department_id =d.department_id;
⭕️:SQL99语法实现满外连接(FULL,但是MySQL不支持)
Oracle 中支持使用FULL,满外连接如下,但是mysql却不支持
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id =d.department_id;
关于mysql见如下
三、使用SQL99实现7种JOIN操作
UNION和UNION ALL的使用
- 合并查询结果利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并 时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
语法格式:
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
UNION操作符:UNION 操作符返回两个查询的结果集的并集,去除重复记录
UNION ALL操作符:UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重
注意:两者都能用的时候推荐使用union all ,因为union存在去重操作效率低
注意:执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据 不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效率。
- UNION:会执行去重操作
- UNION ALL:不会执行去重操作
7种操作的实现
分别取名为左上图、左中图、左下图,右边以此类推,中间的叫中图,语句如下:
#中图:内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#左上图:左外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图:右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图:A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
#右中图:B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
#左下图:满外连接
# 左中图 + 右上图 A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作,效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ (B - A∩B)
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
SQL99语法新特性
附录:常用的 SQL 标准有哪些
- SQL 有两个主要的标准,分别是 SQL92 和 SQL99 。92 和 99 代表了标准提出的时间,SQL92 就是 92 年 提出的标准规范。当然除了 SQL92 和 SQL99 以外,还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、 SQL:2011 和 SQL:2016 等其他的标准。
- 这么多标准,到底该学习哪个呢?实际上最重要的 SQL 标准就是 SQL92 和 SQL99。一般来说 SQL92 的 形式更简单,但是写的 SQL 语句会比较长,可读性较差。而 SQL99 相比于 SQL92 来说,语法更加复杂, 但可读性更强。我们从这两个标准发布的页数也能看出,SQL92 的标准有 500 页,而 SQL99 标准超过了 1000 页。实际上从 SQL99 之后,很少有人能掌握所有内容,因为确实太多了。就好比我们使用 Windows、Linux 和 Office 的时候,很少有人能掌握全部内容一样。我们只需要掌握一些核心的功能,满 足日常工作的需求即可。
L92 和 SQL99 以外,还存在 SQL-86、SQL-89、SQL:2003、SQL:2008、 SQL:2011 和 SQL:2016 等其他的标准。 - 这么多标准,到底该学习哪个呢?实际上最重要的 SQL 标准就是 SQL92 和 SQL99。一般来说 SQL92 的 形式更简单,但是写的 SQL 语句会比较长,可读性较差。而 SQL99 相比于 SQL92 来说,语法更加复杂, 但可读性更强。我们从这两个标准发布的页数也能看出,SQL92 的标准有 500 页,而 SQL99 标准超过了 1000 页。实际上从 SQL99 之后,很少有人能掌握所有内容,因为确实太多了。就好比我们使用 Windows、Linux 和 Office 的时候,很少有人能掌握全部内容一样。我们只需要掌握一些核心的功能,满 足日常工作的需求即可。
- SQL92 和 SQL99 是经典的 SQL 标准,也分别叫做 SQL-2 和 SQL-3 标准。也正是在这两个标准发布之 后,SQL 影响力越来越大,甚至超越了数据库领域。现如今 SQL 已经不仅仅是数据库领域的主流语言, 还是信息领域中信息处理的主流语言。在图形检索、图像检索以及语音检索中都能看到 SQL 语言的使 用。