【MySQL】多表查询(JOINUNION)
作者:mmseoamin日期:2023-12-20

文章目录

    • 多表查询
      • 1. 笛卡尔积错误
      • 2. 等值连接 WHERE
      • 3. 非等值连接
      • 4. 自连接
      • 5. 内连接 INNER JOIN
      • 6. 外连接 LEFT JOIN
      • 7. 合并查询 UNION
      • 8. 注意
      • 9. 七种JOIN的实现
      • 10.练习

        多表查询

        • 多表查询,也称为关联查询,指两个或更多个表一起完成查询操作。
        • 前提条件:这些一起查询的表之间是有关系的(一对一、一对多),它们之间一定是有关联字段,这个关联字段可能建立了外键,也可能没有建立外键。

          1. 笛卡尔积错误

          • X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合,组合的个数即为两个集合中元素个数的乘积数。
          • 笛卡尔积也称为交叉连接 CROSS JOIN 。作用就是可以把任意表进行连接,即使这两张表不相关。
          • 直接进行多表查询,不添加连接条件会出现笛卡尔积错误:
            #查询员工姓名和所在部门名称
            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;
            # 结果会将last_name所有值和department_name所有值做排列组合
            
            • 在 WHERE子句中写入连接条件,可以避免出现笛卡尔积情况。
              SELECT table1.column, table2.column
              FROM table1, table2
              WHERE table1.column1 = table2.column2; #连接条件
              

              2. 等值连接 WHERE

              • 多个表中有相同列时,必须在列名之前加上表名前缀。
              • 使用别名可以简化查询,AS可省略。
              • 列名前使用表名前缀可以提高查询效率。
              • 如果使用了表的别名,在查询字段中、过滤条件中就只能使用别名,不能使用原有的表名,否则就会报错。

                - 阿里开发规范:【强制】对于数据库中表记录的查询和变更,只要涉及多个表,都需要在列名前加表的别名(或表名)进行限定。否则后续多表新增同名字段时,导致ambiguous模糊异常。

              • 等值连接 n个表,至少需要n-1个连接条件。
                # 表名前缀
                SELECT employees.employee_id, employees.last_name, employees.department_id, departments.location_id
                FROM employees, departments
                WHERE employees.department_id = departments.department_id;
                # 别名
                SELECT e.last_name, d.department_id, e.department_id
                FROM employees e, departments AS d 
                WHERE e.department_id = d.department_id;
                # 连接多个表,连接 n个表,至少需要n-1个连接条件。
                SELECT e.employee_id, e.last_name, e.department_id, d.department_name, d.location_id, l.location_id, l.city
                FROM employees e, departments d, locations l
                WHERE e.department_id = d.department_id AND d.location_id = l.location_id
                ORDER BY e.employee_id, l.location_id;
                

                3. 非等值连接

                # 非等值连接
                SELECT e.last_name, e.salary, j.grade_level
                FROM employees AS e, job_grades AS j
                WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal
                ORDER BY grade_level;
                

                4. 自连接

                • 当table1和table2本质上是同一张表,只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行连接查询。
                  # CONCAT() 可实现字段和字符串拼接
                  SELECT CONCAT (worker.last_name,' works for ', manager.last_name)
                  FROM employees worker, employees manager 
                  WHERE worker.manager_id = manager.manager_id;
                  

                  【MySQL】多表查询(JOINUNION),在这里插入图片描述,第1张

                  5. 内连接 INNER JOIN

                  • 内连接: 两表连接查询时,连接结果仅包含符合连接条件的行,参与连接的两个表都应该符合连接条件。不包含不匹配的行。
                  • 内连接通过 INNER JOIN 表名 ON 条件 实现,INNER 可省略。
                  • 语法:
                    # 内连接(INNER JOIN)的实现
                    SELECT 字段列表
                    FROM A表 
                    JOIN B表
                    ON 关联条件
                    WHERE 等其他子句;
                    
                    # 例一
                    SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id
                    FROM employees e 
                    JOIN departments d
                    ON e.department_id = d.department_id;
                    # 效果相同:
                    SELECT e.employee_id, e.last_name, e.department_id,d.department_id, d.location_id
                    FROM employees e, departments d 
                    WHERE e.department_id = d.department_id;
                    # 例二
                    SELECT employee_id, city, department_name
                    FROM employees e 
                    JOIN departments d 
                    ON d.department_id = e.department_id
                    JOIN locations l 
                    ON d.location_id = l.location_id;
                    # 效果相同
                    SELECT employee_id, city, department_name
                    FROM employees e, departments d, locations l 
                    WHERE e.department_id = d.department_id 
                    AND d.location_id = l.location_id; 
                    

                    6. 外连接 LEFT JOIN

                    • 外连接:两表连接查询时,连接结果不仅包含符合连接条件的行,同时也包含自身不符合条件的行。没有匹配的行时, 结果表中相应的列为空(NULL)。
                    • 外连接分为左外连接、右外连接,通过 LEFT / RIGHT OUTER JOIN 表名 ON 条件 实现,OUTER 可省略。
                    • 如果是左外连接,则连接条件中左边的表也称为主表,右边的表称为从表。
                    • 如果是右外连接,则连接条件中右边的表也称为主表,左边的表称为从表。
                    • 结果包括主表的所有行,从表不满足条件的行内容为NULL。
                    • MySQL不支持满外连接,可以通过UNION实现。
                      #左外连接
                      SELECT 字段列表
                      FROM A表 
                      LEFT JOIN B表
                      ON 关联条件
                      WHERE 等其他子句;
                      #右外连接
                      SELECT 字段列表
                      FROM A表 
                      RIGHT JOIN B表
                      ON 关联条件
                      WHERE 等其他子句;
                      
                      #左外连接
                      SELECT e.last_name, e.department_id, d.department_id, d.department_name
                      FROM employees e 
                      LEFT JOIN departments d 
                      ON e.department_id = d.department_id;
                      

                      结果包括employees表的全部行,employees表中的Grant没有department_id,departments表中无对应数值,仍显示在结果中(NULL)。

                      【MySQL】多表查询(JOINUNION),在这里插入图片描述,第2张

                      # 右外连接
                      SELECT e.last_name, e.department_id, d.department_id, d.department_name
                      FROM employees e 
                      RIGHT JOIN departments d 
                      ON e.department_id = d.department_id;
                      

                      结果包括departments表的全部行,employees表中无对应department_id,仍显示在结果中(NULL)。

                      【MySQL】多表查询(JOINUNION),在这里插入图片描述,第3张

                      7. 合并查询 UNION

                      • 利用UNION关键字,可以同时查询多条SELECT语句,并将它们的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同,并且相互对应。
                      • 各个SELECT语句之间使用 UNIONUNION ALL 关键字分隔。
                      • UNION :返回查询的结果集不包括重复记录。
                      • UNION ALL:返回查询的结果集包括重复记录。
                      • 执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效率。
                        SELECT column,... FROM table1
                        UNION
                        SELECT column,... FROM table2
                        SELECT column,... FROM table1
                        UNION ALL
                        SELECT column,... FROM table2
                        
                        • 查询部门编号>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';
                            

                            8. 注意

                            • 多表连接就相当于嵌套 for 循环一样,非常消耗资源,会让 SQL 查询性能下降得很严重,因此要控制连接表的数量,不连接不必要的表。
                            • 在许多 DBMS 中,也都会有最大连接表的限制。
                            • 阿里【强制】:超过三个表禁止 join。需要 join 的字段,数据类型保持绝对一致;多表关联查询时, 保证被关联的字段需要有索引。即使双表 join 也要注意表索引、SQL 性能。

                              9. 七种JOIN的实现

                              1、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第4张

                              # A ∩ B
                              SELECT e.last_name, e.department_id, d.department_name
                              FROM employees e
                              JOIN departments d 
                              ON e.department_id = d.department_id;
                              

                              2、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第5张

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

                              3、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第6张

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

                              4、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第7张

                              # A - A∩B
                              SELECT e.last_name, e.department_id, d.department_name
                              FROM employees e 
                              LEFT JOIN departments d 
                              ON e.department_id = d.department_id
                              WHERE d.department_id IS NULL;
                              

                              5、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第8张

                              # B-A∩B
                              SELECT e.last_name, e.department_id, d.department_name
                              FROM employees e 
                              RIGHT JOIN departments d 
                              ON e.department_id = d.department_id
                              WHERE e.department_id IS NULL;
                              

                              6、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第9张

                              # A∪B 
                              SELECT e.last_name, e.department_id, d.department_name
                              FROM employees e
                              LEFT JOIN departments d 
                              ON e.department_id = d.department_id
                              UNION 
                              SELECT e.last_name, e.department_id, d.department_name
                              FROM employees e
                              RIGHT JOIN departments d 
                              ON e.department_id = d.department_id
                              WHERE e.department_id IS NULL;
                              

                              7、

                              【MySQL】多表查询(JOINUNION),在这里插入图片描述,第10张

                              SELECT e.last_name, e.department_id, d.department_name
                              FROM employees e
                              LEFT JOIN departments d 
                              ON e.department_id = d.department_id
                              WHERE d.department_id IS NULL
                              UNION
                              SELECT e.last_name, e.department_id, d.department_name
                              FROM employees e
                              RIGHT JOIN departments d 
                              ON e.department_id = d.department_id
                              WHERE e.department_id IS NULL
                              

                              10.练习

                              # 1.显示所有员工的姓名,部门号和部门名称。
                              -- 1.1等值连接WHERE,只显示满足连接条件的结果
                              SELECT e.last_name, e.department_id, d.department_name
                              FROM employees e, departments d
                              WHERE e.department_id = d.department_id;
                              -- 1.2内连接join on,只显示满足连接条件的结果,1/2方法等效
                              SELECT last_name, e.department_id, d.department_name
                              FROM employees e 
                              JOIN departments d 
                              ON e.department_id = d.department_id;
                              -- 1.3外连接left join,显示主表左表的全部结果
                              -- 因为存在无主管的员工(deptid和deptname填null),结果比1/2更充分
                              SELECT last_name, e.department_id, d.department_name
                              FROM employees e 
                              LEFT JOIN departments d 
                              ON e.department_id = d.department_id;
                              # 2.查询90号部门员工的job_id和90号部门的location_id
                              SELECT e.department_id, e.job_id, d.location_id
                              FROM employees e, departments d
                              WHERE e.department_id = 90 
                              AND e.department_id = d.department_id;
                              -- 先通过join on 连接两表,再添加筛选条件
                              SELECT e.department_id, e.job_id, d.location_id
                              FROM employees e 
                              JOIN departments d 
                              ON e.department_id = d.department_id
                              WHERE e.department_id = 90;
                              # 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
                              SELECT last_name, d.department_name, d.location_id, city 
                              FROM employees e, departments d, locations l 
                              WHERE e.commission_pct IS NOT NULL 
                              AND e.department_id = d.department_id
                              AND d.location_id = l.location_id; 
                              SELECT last_name, d.department_name, d.location_id, city 
                              FROM employees e 
                              JOIN departments d 
                              ON e.department_id = d.department_id
                              JOIN locations l 
                              ON d.location_id = l.location_id
                              WHERE commission_pct IS NOT NULL;
                              # 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
                              SELECT e.last_name, e.job_id, e.department_id, d.department_name
                              FROM employees e, departments d, locations l 
                              WHERE e.department_id = d.department_id
                              AND d.location_id = l.location_id
                              AND l.city = 'Toronto';
                              SELECT e.last_name, e.job_id, e.department_id, d.department_name
                              FROM employees e 
                              JOIN departments d 
                              ON e.department_id = d.department_id
                              JOIN locations l 
                              ON d.location_id = l.location_id
                              WHERE l.city = 'Toronto';
                              #5.查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所在部门的部门名称为’Executive’
                              SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
                              FROM employees e, departments d, locations l 
                              WHERE e.department_id = d.department_id
                              AND d.location_id = l.location_id
                              AND d.department_name = 'Executive';
                              SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
                              FROM employees e 
                              JOIN departments d 
                              ON e.department_id = d.department_id
                              JOIN locations l 
                              ON d.location_id = l.location_id
                              WHERE d.department_name = 'Executive';
                              #6.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式employees Emp# manager Mgr#kochhar 101 king 100
                              SELECT e1.last_name, e1.employee_id, e1.manager_id, e2.last_name manager_name 
                              FROM employees e1, employees e2 
                              WHERE e1.manager_id = e2.employee_id;
                              SELECT e1.last_name, e1.employee_id, e1.manager_id, e2.last_name manager_name 
                              FROM employees e1 
                              JOIN employees e2 
                              ON e1.manager_id = e2.employee_id;
                              -- left join on 会包含employee没有manager的结果
                              SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,
                              mgr.employee_id "Mgr#"
                              FROM employees emp
                              LEFT OUTER JOIN employees mgr
                              ON emp.manager_id = mgr.employee_id;
                              # 7.查询哪些部门没有员工
                              SELECT d.department_id, d.department_name
                              FROM departments d 
                              LEFT JOIN employees e 
                              ON d.department_id = e.department_id
                              WHERE e.department_id IS NULL;
                              SELECT d.department_id, d.department_name
                              FROM departments d 
                              WHERE NOT EXISTS (
                              					SELECT * FROM employees e
                              					WHERE e.department_id = d.department_id);
                              # 8. 查询哪个城市没有部门
                              SELECT l.location_id, l.city 
                              FROM locations l
                              LEFT JOIN departments d 
                              ON l.location_id = d.location_id
                              WHERE d.location_id IS NULL;
                              # 9. 查询部门名为 Sales 或 IT 的员工信息
                              SELECT e.last_name, e.employee_id, e.department_id, d.department_name
                              FROM employees e, departments d 
                              WHERE e.department_id = d.department_id
                              AND d.department_name IN ('Sales', 'IT');