MySQL SELECT 查询（二）：复杂查询的实现


阅读数: 次   

1. 多表查询

1.1 常见错误：笛卡尔积与属性归属

在多表查询中，笛卡尔积是一个常见的错误，它发生在查询时没有明确指定连接条件，导致每行数据都与其他表中的每行数据进行匹配。这种错误会导致结果集过大，且难以理解。

原因分析：

  • 缺少连接条件：在执行多表查询时，如果没有指定连接条件，MySQL 会默认执行笛卡尔积。

示例代码：

1
2
3
4
5
6
-- 错误示例：缺少连接条件
SELECT employee_id, department_id 
FROM employees, departments;
-- 等价于
SELECT employee_id, department_id 
FROM employees CROSS JOIN departments;

为了防止这种情况，必须在 WHERE 子句中指定连接条件。

属性归属：

在多表查询中，如果列名在多个表中都存在，必须明确指定该列属于哪个表，否则会导致查询错误。

示例代码：

1
2
3
4
-- 错误示例：未指明属性所属表
SELECT employee_id, department_id  
FROM employees e, departments d
WHERE e.department_id = d.department_id ;

1.2 连接条件与规范

相同属性连接：

当两个表中存在相同的属性时，可以通过比较这些属性来连接表。

示例代码：

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

表别名：

使用别名可以简化查询语句，并避免列名冲突。

示例代码：

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

多个连接条件：

当连接多个表时，至少需要 n-1 个连接条件，其中 n 是表的个数。

示例代码：

1
2
3
4
SELECT employee_id, department_name, l.city, e.department_id, l.location_id 
FROM employees e, departments d, locations l 
WHERE e.department_id = d.department_id 
AND d.location_id = l.location_id ;

1.3 连接类型

等值连接与非等值连接：

  • 等值连接：使用 = 操作符连接两个表，返回两个表中匹配的行。
  • 非等值连接：使用不等号（例如 <>、>、<）连接两个表，返回不满足等值条件的行。

示例代码：

1
2
3
4
-- 非等值连接
SELECT e.employee_id, e.last_name, jg.grade_level  
FROM employees e, job_grades jg 
WHERE e.salary BETWEEN jg.lowest_sal AND jg.highest_sal ;

自连接与非自连接：

  • 自连接：一个表与自身进行连接，用于查找同一表中的相关记录。
  • 非自连接：多个表之间进行连接。

示例代码：

1
2
3
4
-- 自连接
SELECT e.employee_id, e.last_name, e2.employee_id, e2.last_name 
FROM employees e, employees e2 
WHERE e.manager_id = e2.employee_id ;

内连接与外连接：

  • 内连接：只返回两个表中匹配的行。
  • 外连接：包括内连接的结果，并添加外层表中不匹配的行。

示例代码：

1
2
3
4
5
6
7
8
9
-- 左外连接
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 ;

1.4 SQL99 连接特性

自然连接：

自然连接会自动连接所有具有相同列名和类型的列，简化了连接条件。

示例代码：

1
2
SELECT e.employee_id, e.last_name, d.department_name 
FROM employees e NATURAL JOIN departments d ;

USING 连接：

USING 连接指定连接时使用的列，简化了连接条件的书写。

示例代码：

1
2
3
SELECT e.last_name, d.department_id 
FROM employees e JOIN departments d
USING(department_id);

2. SQL JOIN 操作详解

2.1 联接的概念

在关系型数据库中，JOIN 操作用于将两个或多个表中的行结合起来，基于它们之间的某种关联或关系。MySQL 支持多种 JOIN 类型，每种类型都有其特定的用途和语法。

2.2 7种 JOIN 类型

  1. INNER JOIN（内连接）：

    • 选择两个或多个表中有匹配的记录的行。
    • 如果在任一表中没有匹配，结果集中就不会有对应的行。

    示例：

    1
    2
    3
    SELECT e.last_name, d.department_name
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id;

  2. LEFT JOIN（左连接）：

    • 返回左表的所有记录，即使右表中没有匹配的记录。
    • 如果右表中没有匹配，结果集中右表的部分将显示为 NULL。

    示例：

    1
    2
    3
    SELECT e.last_name, d.department_name
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id;

  3. RIGHT JOIN（右连接）：

    • 返回右表的所有记录，即使左表中没有匹配的记录。
    • 如果左表中没有匹配，结果集中左表的部分将显示为 NULL。

    示例：

    1
    2
    3
    SELECT e.last_name, d.department_name
    FROM employees e
    RIGHT JOIN departments d ON e.department_id = d.department_id;

  4. FULL JOIN（全连接）：

    • 返回左表和右表中的所有记录。
    • 如果任一表中没有匹配，则在结果集中相应的部分显示为 NULL。
    • MySQL 不直接支持 FULL JOIN，但可以通过 UNION 操作符结合 LEFT JOIN 和 RIGHT JOIN 来实现。

    示例：

    1
    2
    3
    4
    5
    6
    7
    SELECT e.last_name, d.department_name
    FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
    UNION
    SELECT e.last_name, d.department_name
    FROM employees e
    RIGHT JOIN departments d ON e.department_id = d.department_id;

  5. CROSS JOIN（交叉连接）：

    • 返回两个表的笛卡尔积，即所有可能的组合。
    • 这种类型的 JOIN 不常用，因为它会产生大量的结果行。

    示例：

    1
    2
    3
    SELECT e.last_name, d.department_name
    FROM employees e
    CROSS JOIN departments d;

  6. NATURAL JOIN（自然连接）：

    • 根据表中相同的列名自动连接两个或多个表。
    • 它类似于 INNER JOIN，但更加简洁。

    示例：

    1
    2
    3
    SELECT e.last_name, d.department_name
    FROM employees e
    NATURAL JOIN departments d;

  7. USING JOIN：

    • 与 NATURAL JOIN 类似，但它指定连接时使用的列名。

    示例：

    1
    2
    3
    SELECT e.last_name, d.department_name
    FROM employees e
    JOIN departments d USING(department_id);

2.3 UNION 与 UNION ALL

  • UNION：

    • 用于合并两个或多个 SELECT 语句的结果集。
    • 自动去除重复的行。
    • 如果任一 SELECT 语句返回 NULL，则结果集中也包含 NULL。

    示例：

    1
    2
    3
    4
    5
    SELECT last_name, department_id
    FROM employees
    UNION
    SELECT last_name, department_id
    FROM departments;

  • UNION ALL：

    • 与 UNION 类似，但它不会去除重复的行。
    • 如果任一 SELECT 语句返回 NULL，则结果集中也包含 NULL。

    示例：

    1
    2
    3
    4
    5
    SELECT last_name, department_id
    FROM employees
    UNION ALL
    SELECT last_name, department_id
    FROM departments;

3. 流程控制与高级功能

3.1 流程控制函数

if() 函数：

if() 函数用于根据条件返回不同的值。

语法：

1
IF(val, val1, val2)
  • val：条件表达式，如果为 TRUE，则返回 val1。
  • val1：当 val 为 TRUE 时返回的值。
  • val2：当 val 为 FALSE 时返回的值。

示例：

1
2
3
SELECT last_name, salary, 
    IF(salary >= 15000, 'High Salary', 'Low Salary') AS salary_grade
FROM employees;

ifnull() 函数：

ifnull() 函数用于检查表达式是否为 NULL，如果是，则返回指定的替代值。

语法：

1
IFNULL(val1, val2)
  • val1：要检查的表达式。
  • val2：如果 val1 为 NULL，则返回的值。

示例：

1
2
3
4
SELECT last_name, department_id, 
    IFNULL(department_name, 'No Department') AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

3.2 case when 语句

case when 语句用于根据多个条件返回不同的值。

语法：

1
2
3
4
5
CASE when condition1 then result1
     when condition2 then result2
     ...
     else resultN
END
  • condition1, condition2, ...：条件表达式，如果为 TRUE，则执行相应的 result。
  • result1, result2, ...：当条件为 TRUE 时返回的值。
  • resultN：如果所有条件都为 FALSE，则返回的值。

示例：

1
2
3
4
5
6
7
SELECT last_name, salary, 
    CASE
        WHEN salary >= 15000 THEN 'High Salary'
        WHEN salary >= 10000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS salary_grade
FROM employees;

3.3 case … when … 语句

case … when … 语句类似于 case when 语句，但可以用于更复杂的逻辑。

语法：

1
2
3
4
5
6
7
8
SELECT column1, column2, ...
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END AS alias
FROM table_name;
  • column1, column2, ...：要选择的列。
  • condition1, condition2, ...：条件表达式，如果为 TRUE，则执行相应的 result。
  • result1, result2, ...：当条件为 TRUE 时返回的值。
  • alias：结果的别名。

示例：

1
2
3
4
5
6
7
8
SELECT last_name, department_id, salary, 
    CASE department_id
        WHEN 10 THEN salary * 1.1
        WHEN 20 THEN salary * 1.2
        WHEN 30 THEN salary * 1.3
        ELSE salary
    END AS new_salary
FROM employees;

3.4 加密与解密

MySQL 提供了几个函数用于加密和解密数据。
在这里插入图片描述
在这里插入图片描述

加密函数：

  • ENCRYPT(value, key): 使用指定的密钥对值进行加密。
  • AES_ENCRYPT(value, key): 使用 AES 算法对值进行加密。

解密函数：

  • DECRYPT(value, key): 使用指定的密钥对加密的值进行解密。
  • AES_DECRYPT(value, key): 使用 AES 算法对加密的值进行解密。

示例：

1
2
3
4
5
-- 加密
SELECT ENCRYPT('my secret', 'my key');

-- 解密
SELECT DECRYPT(ENCRYPT('my secret', 'my key'), 'my key');

4. SQL 查询执行原理与子查询深入

4.1 SQL 查询执行过程

SQL 查询的执行过程是一个复杂的过程，涉及多个阶段，以下是 MySQL 执行 SQL 查询的基本步骤：

1. FROM 子句解析：

  • MySQL 首先解析 FROM 子句，确定查询涉及的表。

2. JOIN 子句解析：

  • 如果查询中包含 JOIN 操作，MySQL 会解析 JOIN 子句，确定连接类型（如 INNER JOIN、LEFT JOIN 等）和连接条件。

3. ON 子句解析：

  • ON 子句定义了连接条件，MySQL 会根据这些条件将相关行进行匹配。

4. (LEFT|RIGHT) JOIN 解析：

  • 对于 LEFT JOIN 或 RIGHT JOIN，MySQL 会确定哪些行应该包含在结果集中，即使它们在另一个表中没有匹配的行。

5. WHERE 子句解析：

  • WHERE 子句用于过滤行，只保留满足条件的行。

6. GROUP BY 子句解析：

  • GROUP BY 子句用于对结果集进行分组，通常与聚合函数一起使用。

7. HAVING 子句解析：

  • HAVING 子句用于过滤分组后的结果，类似于 WHERE 子句，但它应用于分组后的数据。

8. SELECT 子句解析：

  • SELECT 子句指定了要从表中选择的列。

9. DISTINCT 子句解析：

  • DISTINCT 子句用于去除结果集中的重复行。

10. ORDER BY 子句解析：

  • ORDER BY 子句用于对结果集进行排序。

11. LIMIT 子句解析：

  • LIMIT 子句用于限制结果集的大小。

12. 执行查询：

  • MySQL 根据上述解析步骤执行查询，生成最终的结果集。

4.2 自连接与子查询

自连接：

自连接是指一个表与其自身进行连接，这在处理具有层次结构的数据时非常有用。

示例：

1
2
3
4
-- 查询所有管理者的姓名和他们的直接下属的姓名
SELECT m.last_name AS Manager, e.last_name AS Employee
FROM employees m
JOIN employees e ON e.manager_id = m.employee_id;

子查询：

子查询是一种嵌套查询，它可以在 SELECT、FROM 或 WHERE 子句中使用。

单行子查询：

单行子查询返回单个值。

示例：

1
2
3
4
-- 查询所有工资高于部门平均工资的员工
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

多行子查询：

多行子查询返回多个值。

示例：

1
2
3
4
-- 查询所有部门 ID 和部门名称，这些部门包含工资高于平均工资的员工
SELECT department_id, department_name
FROM departments
WHERE department_id IN (SELECT department_id FROM employees WHERE salary > (SELECT AVG(salary) FROM employees));

相关子查询：

相关子查询是指子查询依赖于外层查询中的值。

示例：

1
2
3
4
5
6
-- 查询所有部门的平均工资，但只显示那些平均工资高于公司平均工资的部门
SELECT department_id, department_name, AVG(salary) AS average_salary
FROM departments d
JOIN employees e ON d.department_id = e.department_id
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

5. 子查询的广泛应用与常见问题解析

5.1 子查询在 HAVING 子句中的应用

HAVING 子句通常与 GROUP BY 子句结合使用，用于对分组后的结果进行过滤。子查询在 HAVING 子句中可以提供更复杂的过滤条件。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查询所有部门 ID 和部门名称，这些部门的最低工资高于部门 50 的最低工资
SELECT department_id, department_name
FROM departments
WHERE department_id IN (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    HAVING MIN(salary) > (
        SELECT MIN(salary)
        FROM employees
        WHERE department_id = 50
    )
);

5.2 子查询在 CASE WHEN 语句中的应用

CASE WHEN 语句可以与子查询结合，用于根据不同的条件返回不同的值。

示例：

1
2
3
4
5
6
7
8
9
10
11
-- 查询员工信息，并根据部门 ID 判断是否位于加拿大
SELECT employee_id, last_name, department_id,
    CASE
        WHEN department_id = (
            SELECT department_id
            FROM departments
            WHERE location_id = 1800
        ) THEN 'Canada'
        ELSE 'USA'
    END AS location
FROM employees;

5.3 常见问题解析

  • NULL 值：

    NULL 值会导致 NOT IN 永远为真，因为 NOT IN 检查的是是否存在不等于的值，而 NULL 与任何值比较都为假。

    示例：

    1
    2
    SELECT * FROM employees WHERE department_id NOT IN (NULL);
    -- 结果为空，因为 NULL 不等于任何值

  • 多行子查询与 = 的使用：

    = 不能用于多行子查询，因为 = 要求子查询返回单个值，而多行子查询会返回多行。

    示例：

    1
    2
    SELECT * FROM employees WHERE salary = (SELECT salary FROM employees WHERE employee_id = 100);
    -- 错误，因为子查询返回多行

5.4 多行子查询

多行子查询返回多行结果，可以使用以下操作符：

  • IN：用于检查值是否在子查询的结果集中。
  • ALL：用于检查外层查询的值是否小于子查询结果集中的所有值。
  • ANY（或 SOME）：用于检查外层查询的值是否小于子查询结果集中的任意一个值。

示例：

1
2
3
4
5
6
7
8
9
-- 查询所有部门的平均工资，但只显示那些平均工资低于所有部门的最低平均工资的部门
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) < ALL (
    SELECT AVG(salary)
    FROM employees
    GROUP BY department_id
);

5.5 关联子查询

关联子查询是指子查询的执行依赖于外层查询的结果。

示例：

1
2
3
4
5
6
7
8
-- 查询所有工资高于其部门平均工资的员工
SELECT last_name, salary, department_id
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

5.6 子查询作为临时表

有时候，可以将子查询的结果视为一个临时表，这可以通过在子查询前加上括号来实现。

示例：

1
2
3
4
5
6
7
8
9
-- 使用子查询作为临时表
SELECT last_name, salary
FROM employees e, (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg
WHERE e.department_id = dept_avg.department_id
AND e.salary > dept_avg.avg_salary;

5.7 使用 EXISTS 和 NOT EXISTS

EXISTS 和 NOT EXISTS 是用来判断子查询是否返回至少一行结果的逻辑操作符。

  • EXISTS：如果子查询返回至少一行结果，则结果为真。
  • NOT EXISTS：如果子查询返回至少一行结果，则结果为假。

示例：

1
2
3
4
5
6
7
8
-- 查询没有员工的部门
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT *
    FROM employees e
    WHERE e.department_id = d.department_id
);
赏

谢谢你请我吃糖果

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

我是Yang,这是我的个人博客，欢迎关注我的博客