mysql数据库 多表查询

多表查询，又称表联合查询，即一条sql语句涉及到的表有多张，数据通过特定的连接进行联合显示。

笛卡尔积

在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y。假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
在数据库中,如果直接查询俩张表,那么其查询结果就会产生笛卡尔积

#例如:
select * from s_emp,s_dept;

连接查询

为了在多表查询中避免笛卡尔积的产生,我们可以使用连接查询来解决这个问题。连接查询分为:

  1. 等值连接
  2. 不等值连接
  3. 外连接
  • 左外连接
  • 右外连接
  • 内连接
  1. 自连接

等值连接

利用一张表中某列的值和另一张表中某列的值相等的关系,把俩张表连接起来。

#例如:查询员工的名字、部门编号、部门名字
#为了表述的更加清楚,可以给每张表起别名
SELECT
	a.id,
	a.last_name,
	a.dept_id,
	b.name
FROM
	s_emp a,s_dept b -- 多张表使用逗号分隔
where a.dept_id = b.id -- 连接的关系： 2张表中存在字段表示相同的含义列名相同

外连接

场景：

#先分别在俩s_emp和s_dept表中插入新的数据
#特点:新员工tom不在任何部门,新增部门st下面没有任何员工
insert into s_emp(id,last_name) values(27,'tom');
insert into s_dept(id,name) values(60,'st');

等值连接查询

#例如:查询员工的名字、部门编号、部门名字
SELECT
	a.id,
	a.last_name,
	a.dept_id,
	b.name
FROM
	s_emp a,s_dept b 
where a.dept_id = b.id

观察查询结果是否正确显示数据？

答：这个时候再使用等值连接的话,查询出来的数据就会少,因为新增的员工tom和部门表中的数据连接不上,当然新增的部门st也和员工表中的数据连接不上.那么这俩条数据都是在等值连接中查询不出来。

概念：许多连接将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行 。连接包含了那些在相关表中没有关联行的行。这种类型的连接称为外连接。

  • 外连接分为:左外连接 右外连接 内连接。
左外连接

概念：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。

#查询所有员工 以及对应的部门的名字,没有部门的员工也要显示出来
SELECT
	a.last_name,
	a.dept_id,
	b.`name`
FROM
	s_emp a
LEFT OUTER JOIN s_dept b
ON a.dept_id = b.id;
#注意:outer可以省去不写
右外连接

概念：取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。

#查询所有员工 以及对应的部门的名字,没有任何员工的部门也要显示出来
SELECT
	a.last_name,
	a.dept_id,
	b.`name`
FROM
	s_emp a
RIGHT OUTER JOIN s_dept b 
ON a.dept_id = b.id;
#注意:outer可以省去不写
内连接

概念：用于取得两个表中存在连接匹配关系的记录。

#查询所有员工 以及对应的部门的名字,只显示有部门的员工信息及对应的部门名字
SELECT
	a.last_name,
	a.dept_id,
	b.`name`
FROM
	s_emp a
INNER  JOIN s_dept b 
ON a.dept_id = b.id;

自连接

概念：一张表,自己和自己连接。

#查询每个员工的名字以及员工对应的管理者的名字
SELECT
	a.last_name,
	b.last_name manager_name
FROM
	s_emp a,
	s_emp b
WHERE
	a.manager_id = b.id;