数据库的复合查询

多表查询，表间连接方式

多表连接的方式

• 实际开发中往往数据来自不同的表，所以需要多表查询。

• ON关键字在 SQL 中用于指定两个表之间连接的条件。

1. INNER JOIN（内连接）

INNER JOIN 将对两种表形成的笛卡儿积进行筛选，返回两个表中 匹配 的记录。

• 例如：

  SELECT 
      A.column1, B.column2
  FROM 
      TableA A
  INNER JOIN 
      TableB B
  ON 
      A.common_field = B.common_field;

笛卡儿积是什么？

“INNER JOIN” 的隐式写法

SELECT 
A.column1, B.column2
FROM 
TableA A ，TableB B
WHERE
 A.common_field = B.common_field;

效果和INNER JOIN相同。

LEFT JOIN 和 RIGHT JOIN都是外连接

2. LEFT JOIN (或 LEFT OUTER JOIN)

LEFT JOIN 返回左表的所有记录，即使右表中没有匹配的记录。对于右表中没有匹配的记录，将返回 NULL。

例如：

SELECT 
    A.column1, B.column2
FROM 
    TableA A
LEFT JOIN 
    TableB B
ON 
    A.common_field = B.common_field;

• 以上SQL 语句会从 TableA 表和 TableB 表中提取数据，并返回一个结果集，其中包括了 TableA 表中的 column1 列和 TableB 表中的 column2 列。即使 TableB 中没有与 TableA 对应的记录，TableA 中的记录仍然会被显示出来，TableB 中没有匹配的列将会显示为 NULL。

3. RIGHT JOIN (或 RIGHT OUTER JOIN)

RIGHT JOIN 与 LEFT JOIN 类似，但它返回右表的所有记录，即使左表中没有匹配的记录。

SELECT 
    A.column1, B.column2
FROM 
    TableA A
RIGHT JOIN 
    TableB B
ON 
    A.common_field = B.common_field;

4. FULL JOIN

MySQL 不直接支持 FULL JOIN，但可以通过结合 LEFT JOIN 和 RIGHT JOIN 来实现，或者通过 UNION 来组合两个查询结果。

SELECT 
    A.column1, B.column2
FROM 
    TableA A
LEFT JOIN 
    TableB B
ON 
    A.common_field = B.common_field

UNION

SELECT 
    A.column1, B.column2
FROM 
    TableA A
RIGHT JOIN 
    TableB B
ON 
    A.common_field = B.common_field;

5. CROSS JOIN

CROSS JOIN 会返回两个表的笛卡尔积（即所有可能的组合）。通常用于在不考虑任何条件的情况下组合两个表。

SELECT 
    A.column1, B.column2
FROM 
    TableA A
CROSS JOIN 
    TableB B;

查询方式

子查询（Subquery）

• 是一个嵌套在另一个 SQL 查询语句中的查询。
• 子查询语句出现在from子句中。即是数据查询的一种技巧，把一个子查询当做一个临时表使用，并且可以给临时表命名。

例如：

SELECT ename, deptno, sal, FORMAT(asal, 2)
FROM EMP,
    (SELECT AVG(sal) asal, deptno dt FROM EMP GROUP BY deptno) tmp
WHERE EMP.sal > tmp.asal
  AND EMP.deptno = tmp.dt;

1. 子查询部分

(SELECT AVG(sal) asal, deptno dt FROM EMP GROUP BY deptno) tmp

• 这个子查询从 EMP 表中选取了每个部门的平均薪水（AVG(sal)），并按部门编号（deptno）进行了分组。
• 子查询的结果会返回两列：asal（每个部门的平均薪水）和 dt（部门编号）。
• 结果集被命名为 tmp，作为一个临时表的名称。在主查询中使用。

2. 主查询部分

SELECT ename, deptno, sal, FORMAT(asal, 2)
FROM EMP,
    tmp
WHERE EMP.sal > tmp.asal
  AND EMP.deptno = tmp.dt;

• FROM EMP, tmp:

  • 主查询从 EMP 表和 tmp 临时表（子查询的结果）中选择数据。
  • 通过 EMP.deptno = tmp.dt 将 EMP 表和子查询结果进行连接。即匹配员工所在的部门与临时表中对应的部门。

• WHERE EMP.sal > tmp.asal:

  • 这个条件限制了查询结果，只保留那些薪水大于其所在部门平均薪水的员工。

• SELECT ename, deptno, sal, FORMAT(asal, 2):

  • 选择的字段包括员工姓名（ename）、部门编号（deptno）、员工薪水（sal），以及格式化后的部门平均薪水（asal）。
  • FORMAT(asal, 2) 将部门的平均薪水格式化为两位小数。

合并查询

• 指的是通过 UNION 运算符将多个 SELECT 语句的结果组合在一起形成一个结果集。合并查询允许你在一个查询中从不同的表或同一个表的不同查询中获得结果，并将它们组合成一个整体输出。

UNION 运算符

• **UNION**：合并两个或多个 SELECT 语句的结果集，并去除重复的行。
• **UNION ALL**：与 UNION 类似，但不去除重复的行，会保留所有结果集中的数据。

用法示例

假设有两个表 TableA 和 TableB，结构相同，包含 id 和 name 两个字段。我们想要从这两个表中获取所有的 id 和 name。

1. 使用 UNION 去重合并查询

SELECT id, name
FROM TableA
UNION
SELECT id, name
FROM TableB;

• 这个查询会返回 TableA 和 TableB 中所有的 id 和 name，但会去除重复的记录。
• 如果 TableA 和 TableB 中有相同的行，结果集中只会保留一行。

2. 使用 UNION ALL 保留重复记录的合并查询

SELECT id, name
FROM TableA
UNION ALL
SELECT id, name
FROM TableB;

• 这个查询同样会返回 TableA 和 TableB 中所有的 id 和 name，但会保留重复的记录。
• 如果 TableA 和 TableB 中有相同的行，结果集中会保留所有这些行。

可能用到的关键字

IN、ALL 和 ANY 关键字用于在子查询或条件表达式中进行比较。它们各自有不同的用途和行为，以下是它们的用法说明及示例：

• IN：用于判断值是否在指定的列表或子查询结果集中。
• ALL：用于判断值是否满足与子查询结果集中的所有值的比较条件。
• ANY：用于判断值是否满足与子查询结果集中的任一值的比较条件。

1. IN 关键字

IN 用于检查一个值是否存在于一个指定的列表或子查询结果集中。

用法

• 检查列表中的值：

  SELECT ename, sal
  FROM emp
  WHERE deptno IN (10, 20, 30);

  这条语句会返回部门编号为 10、20 或 30 的员工。

• 与子查询结合：

  SELECT ename, sal
  FROM emp
  WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'NEW YORK');

  这条语句会返回在子查询中找到的部门编号对应的员工。子查询返回的是位于 “NEW YORK” 的部门编号。

2. ALL 关键字

ALL 用于比较一个值是否满足与子查询结果集中 所有 值的比较条件。通常与比较操作符（如 =, !=, >, <, >=, <=）一起使用。

用法

• 大于子查询结果集中的所有值：

  SELECT ename, sal
  FROM emp
  WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 20);

  这条语句会返回薪水高于部门 20 所有员工的那些员工。也就是说，只有当某个员工的薪水大于部门 20 中最高的薪水时，才会返回该员工。

• 等于子查询结果集中的所有值：

  SELECT ename, sal
  FROM emp
  WHERE sal = ALL (SELECT sal FROM emp WHERE deptno = 20);

  这条语句会返回薪水等于部门 20 所有员工的员工（一般情况下会返回空集，因为员工的薪水通常不会全部相同）。

3. ANY 关键字

ANY 用于比较一个值是否满足与子查询结果集中 任一 值的比较条件。也通常与比较操作符一起使用。

用法

• 大于子查询结果集中的任一值：

  SELECT ename, sal
  FROM emp
  WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 20);

  这条语句会返回薪水大于部门 20 中任一员工的那些员工。实际上，这意味着这些员工的薪水只要高于部门 20 中某个员工的薪水就会被返回。

• 小于子查询结果集中的任一值：

  SELECT ename, sal
  FROM emp
  WHERE sal < ANY (SELECT sal FROM emp WHERE deptno = 20);

  这条语句会返回薪水低于部门 20 中任一员工的那些员工。