【MySQL】02_子查询与多表查询

子查询

指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。

SQL 中子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者 需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集 合）进行比较。

需求分析与问题解决

实际问题

image

#现有解决方式
#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
  SELECT salary
  FROM employees
  WHERE last_name = 'Abel'
  );

复制

子查询的基本使用

• 子查询的基本语法结构：

image

• 子查询（内查询）在主查询之前一次执行完成。
• 子查询的结果被主查询（外查询）使用 。
• 注意事项
  • 子查询要包含在括号内
  • 将子查询放在比较条件的右侧
  • 单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

分类方式1： 我们按内查询的结果返回一条还是多条记录，将子查询分为 单行子查询 、 多行子查询 。

• 单行子查询

image

• 多行子查询

image

分类方式2： 我们按内查询是否被执行多次，将子查询划分为 相关(或关联)/不相关(或非关联) 子查询

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条 件进行执行，那么这样的子查询叫做不相关子查询。

同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查 询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

单行子查询

---

单行比较操作符

操作符	含义
=	equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to
<>	not equal to

代码示例

#题目：查询工资大于149号员工工资的员工的信息
SELECT last_name
FROM employees
WHERE salary >(
    SELECT salary
    FROM employees
    WHERE employee_id = 149);
#题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id =(
    SELECT job_id
    FROM employees
    WHERE employees_id = 141)
AND salary >(
    SELECT salary
    FROM  employees
    WHERE employee_id = 143);
#题目：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(SELECT MIN(salary)FROM employees);
#题目：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id
#实现方式1：不成对比较SELECT employee_id, manager_id, department_id
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN(
    SELECT manager_id
    FROM employees
    WHERE employee_id IN (174,141))
AND department_id IN(
    SELECT department_id
    FROM employees
    WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
#实现方式2：成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN(
    SELECT manager_id, department_id
    FROM employees
    WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);

复制

HAVING 中的子查询

• 首先执行子查询。
• 向主查询中的HAVING 子句返回结果

# 题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >(
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50);

复制

CASE中的子查询

在CASE表达式中使用单列子查询：

# 题目：显式员工的employee_id,last_name和location。
# 其中，若员工department_id与location_id为1800 的department_id相同，则locatio为’Canada’，其余则为’USA’
SELECT employee_id, last_name,(
    CASE department_id
WHEN(
    SELECT department_id FROM departments
    WHERE location_id = 1800)
    THEN 'Canada' ELSE 'USA' END) location
FROM employees;

复制

子查询中的空值问题

no row selected 子查询不返回任何行

非法使用子查询

#例如
SELECT employee_id, last_name
FROM employees
WHERE salary =(
    SELECT MIN(salary)
    FROM employees
    GROUP BY department_id)
#错误代码：1242
#Subquery returns more	than	1 row

复制

多行子查询使用单行比较符

多行子查询

也称为集合比较子查询,内查询返回多行,使用多行比较操作符

多行比较操作符

操作符	含义
IN	等于列表中的任意一个
ANY	需要和单行比较操作符一起使用，和子查询放回的某一个值比较
ALL	需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常使用ANY

体会 ANY 和 ALL 的区别

#题目：查询平均工资最低的部门id
#方式1：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
    SELECT MIN(avg_sal)
FROM(
    SELECT AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
    )dept_avg_sal)
#方式2：
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
    SELECT AVG(salary) avg_sal
    FROM employees
    GROUP BY department_id
)

复制

空值问题

no row selected SELECT last_name FROM employees WHERE employee_id NOT IN ( SELECT manager_id FROM employees );

复制

相关子查询

执行流程 如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件 关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

image

说明：子查询中使用主查询中的列

代码示例

#题目：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
#方式一：相关子查询
SELECT last_name,salary,department_id
FROM employees outer
WHERE salary >(SELECT AVG(salary)
			   FROM employees
			   WHERE department_id=
			   		 outerdepartment_id);
#方式二：在 FROM 中使用子查询
SELECT last_name,salary,e1.department_id
FROM employees e1,
(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP
BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

from型的子查询：子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别 名， 把它当成一张“临时的虚拟的表”来使用。

在ORDER BY 中使用子查询：

#题目：查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
        SELECT department_name
        FROM departments d
        WHERE e.`department_id` = d.`department_id`
#题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2				输出这些相同id的员工的employee_id,last_name和其job_id
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
            FROM job_history
            WHERE employee_id = e.employee_id);

EXISTS 与 NOT EXISTS关键字

• 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
• 如果在子查询中不存在满足条件的行：
  • 条件返回 FALSE
  • 继续在子查询中查找
• 如果在子查询中存在满足条件的行：
  • 不在子查询中继续查找
  • 条件返回 TRUE
• NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

#题目：查询公司管理者的employee_id，last_name，job_id，department_id信息
#方式一：
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS ( SELECT *
              FROM employees e2
              WHERE e2.manager_id =
                    e1.employee_id);
#方式二：自连接
SELECT DISTINCT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
                    SELECT DISTINCT manager_id
                    FROM employees
#题目：查询departments表中
# 		不存在于employees表中的部门的department_id和department_name
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                  FROM employees
                  WHERE department_id = d.department_id);
#相关更新/相关删除
UPDATE table1 alias1
SET column = (SELECT expression
              FROM table2 alias2
              WHERE alias1.column = alias2.column);
#使用相关子查询依据一个表中的数据更新另一个表的数据。
#题目：在employees中增加一个department_name字段，数据为员工对应的部门名称
ALTER TABLE employees
ADD(department_name VARCHAR2(14));
UPDATE employees e
SET department_name = (SELECT department_name
                      FROM departments d
                      WHERE e.department_id = d.department_id);
#相关删除
DELETE FROM table1 alias1
WHERE column operator (SELECT expression
                        FROM table2 alias2
                        WHERE alias1.column = alias2.column);
#使用相关子查询依据一个表中的数据删除另一个表的数据。
#题目：删除表employees中，其与emp_history表皆有的数据
DELETE FROM employees e
WHERE employee_id in
          (SELECT employee_id
          FROM emp_history
          WHERE employee_id = e.employee_id);

自连接与子连接的好坏之分

自连接好！一般情况建议你使用自连接，因为在许多 DBMS 的处理过 程中，对于自连接的处理速度要比子查询快得多。

可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表 进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

多表查询

简介

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

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个 关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

为什么要用多表查询？

1. 避免数据冗余
2. 避免内存空间浪费
3. 减少IO时间花费
4. 提高并发性 多表查询的实现方式：

#错误的实现方式：因为表一与表二元素匹配了一遍
#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments;  #查询出两表列数乘积条记录
SELECT last_name,department_name
FROM employees CROSS JOIN departments;
#错误原因：缺少了多表的连接条件,这种错误简称笛卡尔积错误

笛卡尔积（或交叉连接）的理解

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

image

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;

笛卡尔积的错误的产生条件：

• 省略多个表的连接条件（或关联条件）
• 连接条件（或关联条件）无效
• 所有表中的所有行互相连接

为了避免笛卡尔积， 可以在 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;

在表中有相同列时，在列名之前加上表名前缀。

多表查询分类

角度1：等值连接 vs 非等值链接

等值连接

#示例
SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id, departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

拓展1：多个连接条件与 AND 操作符 拓展2：区分重复的列名

• 多个表中有相同列时，必须在列名之前加上表名前缀。
• 在不同表中具有相同列名的列可以用 表名 加以区分。

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

拓展3：表的别名

• 使用别名可以简化查询。
• 列名前使用表名前缀可以提高查询效率。

注意：如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替， 不能使用原有的表名，否则就会报错。

拓展4：连接多个表

• 如果有n个表实现多表查询，则需要n-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;

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

自连接

#练习：查询员工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`;

非自连接 ：上面写的都属于非自连接

角度3：内链接 vs 外链接

内连接

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

外连接 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

外连接分类

#左外连接，实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句
#右外连接，实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

注意：LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在， 只能用 (+) 表示。

满外连接

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT JOIN 代替。

UNION 和 UNION ALL的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并 时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

#语法格式：
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

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

image

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

image

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

#举例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
#举例：查询中国用户中男性的信息以及美国用户中年男性的用户信息
SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

7种SQL JOINS的实现

image

#代码实现
#中图：内连接 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

#语法小结
#实现A - A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;
#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;
#实现A∪B - A∩B 或 (A - A∩B) ∪ （B - A∩B）
#使用左外的 (A - A∩B) union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

SQL99 语法新特性

自然连接 SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把 自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接 。

#在SQL92标准中：
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
#在 SQL99 中你可以写成：
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING连接 当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配 合JOIN一起使用。

#比如
SELECT employee_id,last_name,department_name