我不允许你还不会的——MySQL多表查询_查询"employees"表中名字为三个字母的员工的名字和部门

以t_employee(员工表)和t_dept(部门表)为例：

t_employee表中的记录如下，dept代表该员工所在的部门id

t_dept表中记录如下：

1. 多表查询

最简单的多表查询，交叉连接，不适用任何匹配条件，生成笛卡尔积

数学中的定义：假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

select * from t_employee,t_dept;
1

结果

实际运行环境下，应避免生成笛卡尔积

解决方案

使用where语句加入条件进行查询，这种也叫做隐式内连接（仅选出两张表中相互匹配的记录）

select e.id,e.empName,d.deptName from t_employee e, t_dept d where e.dept=d.id;
1

2. 内连接查询

是指所有查询出的结果都是能够在连接的表中有对应记录的。

上述表中人力资源部里没有员工（这里只是举例，可能与实际不符，但主要在于逻辑关系），而赵七没有对应的部门，现在想要查询出员工姓名以及其对应的部门名称：

此时，就要使用内连接查询，关键字（inner join）

在这里说一下关联查询sql编写的思路

1，先确定所连接的表，2，再确定所要查询的字段，3，确定连接条件以及连接方式

 select 
 e.empName,d.deptName
 from t_employee e
 INNER JOIN t_dept d
 ON e.dept = d.id;
1
2
3
4
5

查询结果：

没有部门的人员和部门没有员工的部门都没有被查询出来，这就是内连接的特点，只查询在连接的表中能够有对应的记录，其中e.dept = d.id是连接条件

3. 左外连接查询

是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null。例如：

查询所有员工姓名以及他所在的部门名称：在内连接中赵七没有被查出来，因为他没有对应的部门，现在想要把赵七也查出来，就要使用左外连接：

SELECT e.empName,d.deptName
from t_employee e
LEFT OUTER JOIN t_dept d 
on d.id = e.dept;
1
2
3
4

在这里，t_employee就是左表，也就是基准表，用基准表的数据去匹配右表的数据，所以左表的记录是全部会查询出来的，如果右表没有记录对应的话就显示null

查询结果：

关键字是left outer join，等效于left join，在关联查询中，做外连接查询就是左连接查询，两者是一个概念

4. 右外连接

和左外连接是同理的，只是基准表的位置变化了而已

比如：查询所有的部门和对应的员工：

SELECT e.empName,d.deptName
from t_employee e
RIGHT OUTER JOIN t_dept d 
on d.id = e.dept;
1
2
3
4

这里只是把left修改成了right，但是基准表变化了，是以右表的数据去匹配左表，所以左外连接能做到的查询，右外连接也能做到

查询结果：

5. 七种join理论

6. 全外连接

顾名思义，把两张表的字段都查出来，没有对应的值就显示null，但是注意：mysql是没有全外连接的(mysql中没有full outer join关键字)，想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接。例如：

select e.empName,d.deptName
     FROM t_employee e 
     left JOIN t_dept d
     ON e.dept = d.id
UNION
select e.empName,d.deptName
     FROM t_employee e 
     RIGHT JOIN t_dept d
     ON e.dept = d.id;
1
2
3
4
5
6
7
8
9

查询结果：

如果在oracle中，直接就使用full outer join关键字连接两表就行了

6.1 UNION与UNION ALL 的区别

SELECT * FROM A UNION SELECT * FROM B UNION ...
1

• 就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
• 如果使用UNION ALL，不会合并重复的记录行
• 效率 UNION 高于 UNION ALL

7. 自连接查询

自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名

例如：查询员工以及他的上司的名称，由于上司也是员工，所以这里虚拟化出一张上司表

select e.empName,a.empName as bossName 
from t_employee e 
LEFT JOIN t_employee a 
on e.bossId = a.id;
1
2
3
4

查询结果：

sql语句中，给表指定别名直接在表名后写上要用的别名即可，给字段取别名要在sql语句的字段后加上as 别名

在这里，b表是虚拟化出的表，我们可以通过查询了解b表的记录：

SELECT e.empName,b.empName,b.*
from t_employee e
LEFT JOIN t_employee b
ON e.bossId = b.id;
1
2
3
4

查询结果：

后面的四个字段就是虚拟化出的b表的所有记录，但看这四个字段其实就是记录所有是上司的员工的信息

所以，自连接查询一般用作表中的某个字段的值是引用另一个字段的值，比如权限表中，父权限也属于权限。

在连接查询中，还可以加入条件

比如，查询软件开发部的员工信息

select e.id,e.empName,d.deptName 
from t_employee e 
LEFT JOIN t_dept d 
on e.dept=d.id 
where d.deptName='软件开发部';
1
2
3
4
5

查询结果：

参考：

• Mysql中的关联查询(内连接，外连接，自连接)

如有不足之处，欢迎指正，谢谢！