用的表：
employees
departments
locations

查询员工名为’Abel’的人在哪个城市工作

  1. SELECT department_id 
  2. FROM employees
  3. WHERE last_name = 'Abel';
  5. SELECT location_id
  6. FROM departments
  7. WHERE department_id = 80
  9. SELECT * 
  10. FROM locations
  11. WHERE location_id = 2500

笛卡尔积错误

  • 错误实现方式：每个员工都与每个部门匹配了一边
  • 错误原因：缺少了多表的连接条件 ```sql

错误的实现方式 SELECT employee_id,drpartment_name FROM employees,departments; # 查询出2889条记录

SELECT * FROM employees; #107条记录

SELECT 2889/107 # 27

SELECT * FROM departments; # 27条记录

  1. <a name="umTi4"></a>
  2. # 多表查询的正确方式
  3. ```sql
  4. SELECT employee_id,department_name
  5. FROM employees,departments
  6. #两个表的连接条件
  7. WHERE employees.department_id = departments.department_id;
  • 如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
  • 建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表 ```sql

    department_id两个表中都有

    SELECT employee_id,department_name,department_id FROM employees,departments

    两个表的连接条件

    WHERE employees.department_id = departments.department_id;

SELECT employee_id,department_name,employees.department_id FROM employees,departments

两个表的连接条件

WHERE employees.department_id = departments.department_id;

  2. - 可以给表起别名，在SELECT 和 WHERE中使用表的别名
  3. ```sql
  4. SELECT emp.employee_id,dept.department_name,emp.department_id
  5. FROM employees emp,departments dept
  6. #两个表的连接条件
  7. WHERE emp.department_id = dept.department_id;

  • 如果给表起了别名，一旦在SELECT 或 WHERE中使用表名的话，则必须使用表的别名，而不能使用表的原名

  • 如果有n个表实现多表的查询，则需要至少使用n-1个连接条件


多表查询的分类

等值连接 vs 非等值连接

  • 非等值连接的例子 ```sql SELECT * FROM job_grades;

SELECT last_name,salary,grade_level FROM employee 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;

  2. <a name="mLn25"></a>
  3. ## 自连接 vs 非自连接
  5. - 自连接的例子
  7. 查询员工id，员工姓名及管理者的id和姓名
  8. ```sql
  9. SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
  10. FROM employees emp,employees mgr
  11. WHERE emp.manager_id = mgr.employee_id

内连接 vs 外连接

  • 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行 ```sql SELECT employee_id,department_name FROM employees e,departments d WHERE e.department_id = d.department_id # 只有106条记录

内连接就是只查询WHERE相等对应的记录 外连接是除了查到对应的记录，还查询到了其他不匹配的记录

  2. - 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表 或 右表中不匹配的行
  4. - 外连接的分类：左外连接，右外连接，满外连接
  5. - 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行
  6. - 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行
  8. - 查询  **所有的，并且来自不同的表**  的信息，则为外连接
  10. 查询**所有的**员工的last_name，department_name信息
  11. ```sql
  12. SELECT employee_id,department_name
  13. FROM employees e,departments d
  14. WHERE e.department_id = d.department_id  # 需要使用左外连接

  • SQL99语法中使用JOIN …ON的方式实现多表的查询。这种方式也能解决外连接的问题

  • SQL99语法实现内连接 ```sql SELECT last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id

SELECT last_name,department_name,city FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN locations l ON d.location_id = l.location_id;

  2. - SQL99语法实现外连接
  4. 查询**所有的**员工的last_name，department_name信息
  5. ```sql
  6. # 左外连接
  7. SELECT last_name,department_name
  8. FROM employees e LEFT JOIN departments d
  9. ON e.department_id = d.department_id
  1. # 右外连接
  2. SELECT last_name,department_name
  3. FROM employees e RIGHT JOIN departments d
  4. ON e.department_id = d.department_id
  1. # 满外连接  mysql不支持full outer join 写法
  2. SELECT last_name,department_name
  3. FROM employees e FULL JOIN departments d
  4. ON e.department_id = d.department_id

使用SQL99实现JOIN操作

UNION的使用

  • 合并查询结果
  • UNION：返回两个查询的结果集的并集，去除重复记录
  • UNION ALL：返回两个查询的结果集的并集，不去重

  • 结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复数据，则尽量使用UNION ALL语句，以提高数据查询的效率

    七种JOIN的实现

    image.png

  • 中图：内连接

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

  • 左上图：左外连接

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

  • 右上图：右外连接

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

  • 左中图：

    1. SELECT employee_id,department_name
    2. FROM employees e LEFT JOIN departments d
    3. ON e.department_id = d.department_id;
    4. WHERE d.department_id IS NULL; //去除中间的部分:中间的部分不是null，要是NULL的

  • 右中图：

    1. SELECT employee_id,department_name
    2. FROM employees e RIGHT JOIN departments d
    3. ON e.department_id = d.department_id;
    4. WHERE e.department_id IS NULL

  • 左下图：满外连接

    • 方式一：左上图 UNION ALL 右中图

      1. SELECT employee_id,department_name
      2. FROM employees e LEFT JOIN departments d
      3. ON e.department_id = d.department_id
      4. UNION ALL
      5. SELECT employee_id,department_name
      6. FROM employees e RIGHT JOIN departments d
      7. ON e.department_id = d.department_id
      8. WHERE e.department_id IS NULL;

    • 方式二：左中图 UNION ALL 右上图

      1. SELECT employee_id,department_name
      2. FROM employees e LEFT JOIN departments d
      3. ON e.department_id = d.department_id
      4. WHERE d.drpartment_id IS NULL
      5. UNION ALL
      6. SELECT employee_id,department_name
      7. FROM employees e RIGHT JOIN departments d
      8. ON e.department_id = d.department_id

  • 右下图：左中图 UNION ALL 右中图

    1. SELECT employee_id,department_name
    2. FROM employees e LEFT JOIN departments d
    3. ON e.department_id = d.department_id
    4. WHERE d.department_id IS NULL
    5. UNION ALL
    6. SELECT employee_id,department_name
    7. FROM employees e RIGHT JOIN departments d
    8. ON e.department_id = d.department_id
    9. WHERE e.department_id IS NULL

SQL99语法新特性

自然连接

  • NATURAL JOIN：会帮你自动查询两张连接表中 所有相同的字段，然后进行 等值连接 ```sql SELECT employee_id,last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id AND e.manager_id = d.manager_id

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

  1. <a name="bh9lm"></a>
  2. ## USING
  4. - USING：需要在USING的括号中填入要指定的同名字段
  5. ```sql
  6. SELECT employee_id,last_name,department_name
  7. FROM employees e JIN departments d
  8. ON e.department_id = d.department_id;
  11. SELECT employee_id,last_name,department_name
  12. FROM employees e JOIN departments d
  13. USING (department_id);

小结

  • 表连接的约束条件可以有三种方式：WHERE，ON，USING

  • WHERE：适用于所有关联查询

  • ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开可读性更好
  • USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，且只能表示关联字段值相等
  • 拓展：可以这样写，不推荐。最好还是拆开写
    1. SELECT last_name,job_title,department_name
    2. FROM employees INNER JOIN departments INNER JOIN jobs
    3. ON employees.department_id = departments.department_id
    4. AND employees.job_id = jobs.job_id;

练习

  • 查询员工的employee_id,.last_name,department_name,city

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

  • 显示 所有员工的姓名，部门号和部门名称

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

  • 查询90号部门员工的job_id和90号部门的location_id

    1. SELECT e.job_id,d.location_id
    2. FROM employees e JOIN departments d
    3. ON e.department_id = d.department_id
    4. WHERE d.department_id = 90

  • 查询所有有奖金的员工的last_name，department_name，location_id，city ```sql SELECT e.last_name,d.department_name,d.location_id,l.city,e.commission_pct FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id LEFT JOIN locations l ON d.location_id = l.location_id WHERE e.commission_pct IS NOT NULL # 也应该是35条记录

SELECT * FROM employees WHERE commission_pct IS NOT NULL; 35条记录

  2. - 选择city在Toronto工作的员工的 last_name，job_id，department_id，department_name
  3. ```sql
  4. SELECT last_name,job_id,department_id,department_name
  5. FROM employees e JOIN departments d
  6. ON e.deaprtment_id = d.department_id
  7. JOIN locations l
  8. ON d.location_id = l.location_id
  9. WHERE l.city = 'Toronto';

  • 查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在的部门名称为’Executive’

    1. SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary
    2. FROM employees e LEFT JOIN departments d  # 不同的城市内可能有相同的部门
    3. ON e.department_id = d.department_id
    4. LEFT JOIN locations l
    5. ON d.location_id = l.location_id
    6. WHERE d.department_name = 'Executive';

  • 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式 ```sql employees Emp# manager Mgr# kochhar 101 king 100

SELECT emp.last_name “employees”,emp.employee_id “Emp#”,mgr.last_name “manager”,mgr.employee_id “Mgr#” FROM employees emp LEFT JOIN employees mgr ON emp.manager_id = mgr.employee_id

指定员工是要查询所有员工的情况，但是公司的boss没有管理者(null)，需要让其显示出来，因此使用左外连接

  2. - 查询哪些部门没有员工
  3. ```sql
  4. SELECT department_id
  5. FROM departments d LEFT JOIN employees e
  6. ON d.department_id = e.department_id
  7. WHERE e.department_id IS NULL;
  10. # 也可以使用子查询实现
  11. SELECT department_id
  12. FROM departments d
  13. WHERE NOT EXISTS(
  14.         SELECT * 
  15.       FROM employees e
  16.          WHERE e.department_id = d.department_id
  17.     )

  • 查询哪个城市没有部门

    1. SELECT l.location_id,l.city
    2. FROM locations l LEFT JOIN departments d
    3. ON l.location_id = d.location_id
    4. WHERE d.location_id IS NULL

  • 查询部门名为Sales 或 IT的员工信息

    1. SELECT e.employee_id,e.last_name,e.department_id
    2. FROM employees e JOIN departments d
    3. ON e.department_id = d.department_id
    4. WHERE d.department_name IN ('Sales','IT');