多表查询

12.18

1.多表查询

#查询员工名为'Abel'的人在哪里工作
SELECT *
FROM employees
WHERE last_name ='Abel';
#得到结果department_id为80
#在departments表中查询department_id为80的工作地点
SELECT *
FROM departments
WHERE department_id=80;
#得到结果location_id为2500
#在locations表中查询location_id为2500
SELECT *
FROM locations
WHERE location_id=2500;
#得到city为Oxford
#综上员工名为'Abel'的人在Oxford工作
#进行了三次查询！

  • Q：可以将多张表写在一张表上吗？为什不那么做？

  • A：可以！防止数据的冗余。用上述例子来举例，一个员工有一个部门号department_id，部门表中有部门名字，那么如果写在一张表中，相同部门号的员工就会有大段文字重复。或者有的员工没有部门，则会有null值的存在，又或者有些部门还有员工，则前方会有null值。

    last_name department_id department_name
    zhangsan 1000 Finance
    lisi 1000 Finance
    wangwu null null
    null 1001 Corporate Tax

1.1 多表查询SQL92

#查询员工的姓名及其部分名称
SELECT employee_id,department_id
FROM employees,departments
#如上是错误的！每一个employee_id对应了每一个department_id，107*27=2889条数据
#上述错误被称为笛卡尔积的错误。

#查询员工的姓名及其部分名称
SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;
#过滤条件为employees表中department_id与departments表中department_id相等
1.1.1怎样会出现笛卡尔积的错误

  • 省略多表的查询条件（或关联条件）

  • 连接条件（或关联条件）无效

  • 所有表中所有行的互相连接

1.1.2 Ambiguous问题

对表中共有的字段进行查找时，必须指明该字段出自哪一个表

从SQL优化的角度，建议在每个字段前都指明其出自的表！

SELECT employee_id,department_name,department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;
#如上是错误的，Column 'department_id' in field list is ambiguous
#department_id在是不明确的
SELECT employees.employee_id,departments.department_name,departments.department_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;
1.1.3 表的别名
  1. 由于要给每个字段加上字段的出处，使得SQL语句复杂化，因此建议在SQL语句中给表加上别名
  2. WHERE中不能使用列的别名，但是可以使用表的别名
  3. 一旦使用表的别名，在WHERE和SELECT则必须使用别名
SELECT t1.employee_id,t2.department_name,t2.department_id
FROM employees t1,departments t2
WHERE t1.department_id=t2.department_id;

1.2结论

结论：如果有n个表实现多表查询，则至少要有n-1个条件 。

1.3多表查询的分类

1.3.1等值连接和非等值链接
  • 如名字就可以知道意思
SELECT employee_id,salary,grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;
#根据employees表中salary的数值比较job_grades表中的区间来得到job_grades表中的grade_level然后输出
1.3.2自连接和非自连接
  • 自己链接自己就是自连接，多个表中的连接就是非自连接。
#练习：查询员工id，员工姓名，及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,man.employee_id,man.last_name
FROM employees emp,employees man
WHERE emp.manager_id=man.employee_id;
#完成自连接
1.3.3自连接和外连接

  • 合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行，则称为内连接

  • 如上所写的内容都是内连接

  • 合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行，则称为外连接。

SELECT emp.employee_id,emp.last_name,man.employee_id,man.last_name
FROM employees emp,employees man
WHERE emp.manager_id=man.employee_id+;
#在MYSQL中不认可SQL92的外连接写法，即上述写法会报错！但是Oracle可以。

在此之前需要先学习SQL99中的多表查询。

1.4多表查询SQL99

1.4.1内连接
  • 语法其实很简单，将上述描述FROM中的“，”改为JOIN；将WHERE改为ON。
  • JOIN+表名+ON+连接条件！
  • 其实内连接用的是INNER JOIN，INNER可以省略！
SELECT t1.employee_id,t2.department_name,t2.department_id
FROM employees t1 JOIN departments t2
ON t1.department_id=t2.department_id;
  • 多个条件时，则写多个JOIN...ON
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
1.4.2 外连接
  • 将INNER JOIN改为OUTER JOIN
  • 左外连接为LEFT OUTER JOIN
  • 右外连接为RIGHT OUTER JOIN
  • 其中有LEFT或者RIGHT使，OUTER可以省略
#查询所有员工的last_name,department_name信息
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
1.4.3全外连接
  • MySQL不支持FULL OUTER JOIN。
1.4.4UNION和UNION ALL

  • UNION和UNION ALL是将两个结果集合并为一个

  • UNION会对返回的结果集进行去重

  • 如果明确知道返回的结果不存在重复数据或者不需要去重的，则尽量使用UNION ALL语句，以提高数据效率

假设A为员工表，B为部门表

  • 中图——内连接

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

  • 左1——左外连接

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

  • 右1——右外连接

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

  • 左2

    SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

  • 右2

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

  • 左3

    #左1图UNION ALL右2图
#不要习惯性的加分号！
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

  • 右3

    #左2 UNION ALL 右二
SELECT employee_id,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 employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
posted @ 2021-12-18 22:10  Boerk  阅读(61)  评论(0)    收藏  举报