MySQL基础-多表查询

目录

多表查询

多表查询的概念

笛卡尔积

多表查询分类

等值连接（内部连接）

非等值连接

自连接

内连接

外连接

SQL99**语法实现多表查询**

JOIN...ON解决外连接

内连接(INNER JOIN)的实现

sql99语法实现外连接：

UNION**的使用**

7种SQL JOINS 的实现

SQL99**语法新特性

---

多表查询

多表查询的概念

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进

行关联。

说明一下外键和主键的概念：

主键：一列（或一组列），其值能唯一区分表中每个行。

外键：外键为某个表中的一列，它包含另一个表中的主键值，定义了两个表中的关系。

笛卡尔积

由没有连接条件的表关系返回的结果为笛卡尔积。检索出的行数目为第一个表的行数目和第二个表中的行数目的乘积。

多表查询分类

等值连接（内部连接）

直接举例：

SELECT employees.employee_id,employees.last_name,
employees.department_id,departments.department_id,
departments.location_id
FROM employees,departments
WHERE employees.department_id=departments.department_id;

在employees表中department_id是外键，与departments表的主键department_id，相关联

在WHERE子句中可以有多个连接条件，用AND连接。

在多个表中由相同的列必须在列名前加上前缀，避免二义性。

可以使用表的别名代替表名，需要注意的是，如果我们使用了表别

名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使

用原有的表名，否则就会报错。

非等值连接

直接举例

employees 表中员工工资在job_grades表中是一个范围。

 SELECT *
 FROM job_grades;
 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`
 ORDER BY j.`grade_level`ASC;

自连接

 

可以看到除了老板外，每个员工都有自己的管理者，同时作为管理者的同时也作为普通员工拥有管理者。

举例：

查询员工 id，员工姓名，及其管理者id，管理者姓名

 SELECT emp.`employee_id`,emp.`last_name`,mgr.`employee_id`,mgr.`last_name`
 FROM employees emp,employees mgr
 WHERE emp.`manager_id`=mgr.`employee_id`;

  

emp和mgr本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。

然后两个表再进行内连接，外连接等查询。

内连接

合并具有同一列的两个以上的表的行，结果集不包含一个表与另一个表不匹配的行。

例如：查询员工所在的部门名字，需要将员工表和部门表联合起来，两个表具有相同的列 部门id。

 SELECT employee_id,department_name
 FROM employees  AS e,departments AS d
 WHERE e.`department_id`=d.`department_id`;

查询的结果少一行，因为有一个员工没有部门。

外连接

合并具有同一列的两个以上的表的行,除了包含一个表与另一个表匹配的行之外，还查询到了 左表 或 右表中 不匹配的行 左外连接:还返回 左表中不满足条件的表，左边的表也称为 主表 ，右边的表称为 从表 。 右外连接：还返回右表中不满足条件的表，右边*的表*也称为 主表 ，左边的表称为 从表 。

在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。MySQL不支持SQL92中外连接的写法

 SELECT employeed_id,department_name
 FROM employees e,departments d
 WHERE e.`department_id`=d.`department_id`(+);

SQL99**语法实现多表查询**

JOIN...ON解决外连接

基本语法：

SELECT table1.column,table2.column,table3.column FROM table1
  JOIN table2 ON table1和table2的连接条件
    JOIN table3 ON table2和table3的连接条件

说明：

可以使用ON子句指定额外的连接条件。

这个连接条件是与其它条件分开的。

ON子句使语句具有更高的易读性。

关键字JOIN、INNER JOIN、CROSS JOIN的含义是一样的，都表示内连接

内连接(INNER JOIN)的实现

语法：

SELECT字段列表
FROM A表 INNER JOIN B表 ON关联条件
WHERE等其他子句;

举例：查询员工所在的部门名字

 SELECT last_name,department_name
 FROM employees e INNER JOIN departments d
 ON e.`department_id`=d.`department_id`;

这里少一个员工的记录。因为他没有部门。

sql99语法实现外连接：

查询所员工的last_name和department_name 左外连接(LEFT OUTER JOIN)*：**

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

这里查询到的结果是完全的。

右外连接(RIGHT OUTER JOIN)：

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

这里多出来的数据是因为有的部门还没有员工。

可以回溯到前文外连接的定义。

满外连接*(FULL OUTER JOIN)：

满外连接的结果=左右表匹配的数据+左表没有匹配到的数据+右表没有匹配到的数据。

SQL99是支持满外连接的。使用FULLJOIN或FULLOUTERJOIN来实现。

需要注意的是，MySQL不支持FULL JOIN，但是可以用LEFT JOIN UNION RIGHT JOIN代替。

UNION**的使用**

语法：

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

UNION操作符 返回两个查询的结果集的并集，去除重复记录

UNION ALL返回两个查询的结果集的并集,对于重复部分不去除

 

7种SQL JOINS 的实现

代码实现：

#中图：内连接A∩B
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id`=d.`department_id`;
​
#左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
​
#右上图：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`;
​
#右中图：B-A∩B
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;
​
#左中图：A- A∩B
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;
​
#左下图：满外连接
#1：左上图 union all 右中图
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;
#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
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`;
​
#右下图：右中图 union all 左中图 A∪B- A∩B或者(A- A∩B)∪（B- A∩B）
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
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;

SQL99**语法新特性

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值连接

USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

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

WHERE：适用于所有关联查询

ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。

USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等