【MySQL新手到通关】第五章 多表查询

文章目录

• 1. 笛卡尔积
• • 1.1 避免笛卡尔积
  • 1.2 笛卡尔积（或交叉连接）的理解
  • 1.3 案例分析与问题解决笛卡尔积的错误会在下面条件下产生：
  • 2. 多表查询分类讲解
  • • 2.1 多表联查分类方式1：
    • 2.2 多表联查分类方式2：
    • 2.3 多表联���分类方式3：

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

      前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个

      关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进

      行关联。

      1. 笛卡尔积

      1.1 避免笛卡尔积

      #案例：查询员工的姓名及其部门名称SELECT last_name, department_nameFROM employees, departments;

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

      笛卡尔乘积是一个数学运算。假设我有两个集合 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.3 案例分析与问题解决笛卡尔积的错误会在下面条件下产生：

      • 笛卡尔积的错误会在下面条件下产生：
        • 省略多个表的连接条件（或关联条件）
        • 连接条件（或关联条件）无效
        • 所有表中的所有行互相连接
        • 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。
        • 加入连接条件后，查询语法：

          SELECT table1.column, table2.columnFROM table1, table2WHERE table1.column1 = table2.column2; #连接条件

          在 WHERE子句中写入连接条件。

          • 代码示例：

            #案例：查询员工的姓名及其部门名称SELECT last_name, department_nameFROM employees, departmentsWHERE employees.department_id = departments.department_id;

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

              2. 多表查询分类讲解

              2.1 多表联查分类方式1：

              等值连接

              SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_idFROM employees e , departments dWHERE e.department_id = d.department_id;

              非等值连接

              SELECT e.last_name, e.salary, j.grade_levelFROM employees e, job_grades jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

              2.2 多表联查分类方式2：

              自连接

              SELECT CONCAT(worker.last_name ,' works for ', manager.last_name)FROM employees worker, employees managerWHERE worker.manager_id = manager.employee_id ;

              非自连接

              略

              2.3 多表联查分类方式3：

              内连接: 合并多表具有同一列的行, 结果集中不包含多表中不匹配的行 。

              内连接

              SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id);

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

              除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

              如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。

              如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

              左外连接

              SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;

              右外连接

              SELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT OUTER JOIN departments dON (e.department_id = d.department_id) ;