MySQL学习笔记（第四部分）

5.1、多表查询的分类

• 等值连接 VS 非等值连接

  • 等值连接的例子就是上面这一种，这块列举一个非等值连接的
  • 我想查看一下员工的姓名、工资、还有工资评定等级，写法：

  #这块的解题思路在于表中相对应的连接字段并不是可以直接和另一个表中的字段直接匹配的，它更像是一种范围，这就是非等值连接
  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;
  #这两种写法作用是一致的，都具有边界的作用
  WHERE E.salary >= J.lowest_sal AND E.salary<= J.highest_sal;
  

• 自连接 VS 非自连接

  • 前面我们写的这些案例都是非自连接，是我们手动进行表与表之间的关联

  • 自连接的练习：查询员工id、员工姓名及其管理者的id和姓名，写法如下：

    #题解思路：表中员工的姓名和id，对应管理者的姓名和id，虽然每个员工都有对应领导，但是领导本身也是公司的一份子，所以在进行自连接的时候，要把四个本来相同的字段分成管理者和员工的字段并以命名，再以别名去说明字段分别是哪张表里的，再进行连接，即员工表的领导id对应的是领导本身在公司里的id，这就是自连接
    SELECT EMP.employee_id,EMP.last_name,MRG.employee_id,MRG.last_name
    FROM employees EMP, employees MRG
    WHERE EMP.manager_id = MRG.employee_id;
    

• 内连接 VS 外连接

  • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
  • 这里指两个个表，一个为表A，一个为表B，即内连接只是把A表和B表当中满足连接条件的数据给查询出来了，其他的数据并没有被查出来，这就是内连接，举例：查询员工的姓名及其部门名称，写法如下：

  SELECT D.department_name,E.department_id,E.last_name,E.employee_id
  FROM employees E,departments D
  WHERE E.department_id = D.department_id;
  

  • 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行，之外还查询到了左表或右表中不匹配的行，这个就是外连接；
    • 外连接的分类：左外连接、右外连接、满外连接
    • 左外连接和右外连接即左表和右表之间的非公共部分的数据
    • 满外连接即左右两表所有的数据，也包括了公共部分和左外连接和右外连接
    • 以下是外连接的案例练习题：查询所有的员工信息，写法如下：

# 查询所有的员工信息
SELECT E.employee_id,D.department_name
FROM employees E ,departments D
WHERE E.department_id = D.department_id
#但我们通过此查询只查询出了106条数据，明显与107条数据不符，所以推断此连接是内连接的写法，因为并没有有查询到公共部分

• SQL92语法实现外连接的写法：使用 + ------>但MySQL并不支持SQL92的外连接写法
• 所以我们使用SQL99语法中，使用JOIN ...ON的方式实现多表的查询，这种方式也能解决外连接的问题，MySQL也是支持这种方式的
• SQL99语法如何实现多表查询；
• SQL99语法实现内连接：
• 查询所有员工的信息

SELECT E.last_name,D.department_name
FROM employees E JOIN departments D
ON E.department_id = D.department_id #其中表和表之间用JOIN进行表的连接，并给表起别名，再把过滤条件加入到ON关键字后即可完成连接操作，这是两边表之间的内连接，那么三张表怎么去体现呢？

• 查询所有员工的信息以及他们所在的城市

#根据题意涉及到了三张表的字段，并需要体现在一张表内，即表与表之间的关联，写法如下
SELECT E.last_name,D.department_name,city
FROM employees E JOIN departments D
ON E.department_id = D.department_id
JOIN locations L
ON L.location_id = D.location_id; #写法中涉及到三张表，即在后面使用关键字JOIN来连接需要连接的表，连接条件则写在ON中

• 以上是使用SQL99语法实现了内连接，那么如何实现外连接呢？
• 外连接又分为左外连接和右外连接和满外连接
• 我们也还是使用以上的案例来做说明：查询所有员工的姓名、职位名，写法如下：

SELECT E.last_name,D.department_name
FROM employees E LEFT JOIN departments D
ON E.department_id = D.department_id   #JION ON关键字是内连接，那外连接又分为左外连接和右外连接，题中是左外连接

SELECT last_name,department_name
FROM employees E RIGHT JOIN departments D
ON E.department_id = D.department_id;  #只有外连接才需要加上OUTER，然后根据需求改变左右连接即可，这是右外连接

5.2、UNION关键字的使用

合并结果查询

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

SELECT column,  ... FROM table1
UNION [ALL]
SELECT column,  ... FROM table2

5.3、UNION和UNION ALL的使用

• UNION：会执行去重操作
• UNION ALL：不会执行去重操作
• 结论：如果明确直到合并数据后的数据结果不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高效率

5.4、七种SQL JOINS的实现

• 面试会问，笔试会考，开发要用的！！！！重点！重点！重点！

• 以上以及说明了内连接、左外连接、右外连接，那么还有四种情况没有说明到，首先要引入一个关键字才可继续学习；即：UNION

• 完整实现步骤：中图：即内连接的写法

SELECT E.employee_id,D.department_name
FROM employees E JOIN departments D 
ON E.department_id = D.department_id;   #这就是内连接，即两表之间的公共部分

• 左上图和右上图的写法：即左外连接和右外连接

SELECT E.employee_id,D.department_name
FROM employees E LEFT JOIN departments D
ON E.department_id = D.department_id; #这就是左外连接，即两表之间的公共部分以及左表的部分

SELECT E.employee_id,D.department_name
FROM employees E RIGHT JOIN departments D
ON E.department_id = D.department_id;  #这就是右外连接，即两表之间的公共部分以及右表的部分

• 左中图的写法：可以理解为在左上图的基础上用WHERE语句把公共部分抹去

SELECT E.employee_id,D.department_name
FROM employees E LEFT JOIN departments D
ON E.department_id = D.department_id
WHERE D.department_id IS NULL;    #使用WHERE语句抹去了右表和左表中的公共的部分即可显示

• 右中图的写法：可以理解为在右上图的基础上用WHERE语句把公共部分抹去

SELECT E.employee_id,D.department_name
FROM employees E RIGHT JOIN departments D
ON E.department_id = D.department_id
WHERE E.department_id IS NULL;  #使用WHERE语句抹去了左表和右表中的公共部分即可显示

• 左下图的写法：可以理解为左上图和右中图进行拼接，这块可以使用UNION ALL关键字，因为没有重复的部分，效率会更高

SELECT E.employee_id,D.department_name
FROM employees E LEFT JOIN departments D
ON E.department_id = D.department_id
UNION ALL
SELECT E.employee_id,D.department_name
FROM employees E RIGHT JOIN departments D
ON E.employee_id = D.department_id
WHERE E.department_id IS NULL; #这也可以理解为满汉查询，使用UNION ALL关键字进行拼接

• 右下图的写法：可以理解为左中图和右中图进行拼接，这块也可以使用UNION ALL关键字，没有重复的部分，效率会更高

SELECT E.employee_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 ALL
SELECT E.employee_id,D.department_name
FROM employees E RIGHT JOIN departments D
ON E.department_id = D.department_id
WHERE E.department_id IS NULL;  #在开发中如果发现UNION和UNION ALL字段能同时使用的，优先使用UNION ALL，效率更高