MySQL --- 多表查询 - 笛卡尔积和正确的多表查询、等值连接和不等值连接、内连接和外连接

多表查询

前面我们使用的查询，只是对单表进行查询，但是在具体的应用中，经常需要实现在一个查询语句中显示多张数据表的数据，因为基本上数据都是分表的，需要根据当前表在其他表中查询数据，把多张表结合起来进行查询数据的方式叫做多表联合查询。

在具体实现连接操作时，首先将两个或两个以上的表按照某个条件连接起来，然后再查询到所要求的数据记录。

连接查询分为 交叉连接、内连接、外连接 查询三种方式。

MySQL --- 数据库的基本操作

基本的信息都存储在员工信息表 emp 中，最后一项信息：部门编号只有一个数字，怎么知道是什么部门？部门在哪个地方？这个时候就需要转到部门表 dept  里面

部门表 dept 中存储了部门编号所对应的部门详细信息

薪资等级表 salgrade，例如薪资为 800，在 700 到 1200 之间为一等，1201 到 1400 之间为二等

-- 查看smith员工的信息
SELECT * FROM emp WHERE ename='smith';

-- 根据部门编号查看smith所在的部门信息
SELECT * FROM dept WHERE deptno=20;

虽然我们确实可以找到某一位员工的信息以及它所在的部门信息，但是我们可以发现这两个查询语句是独立的，我们并不能直观地看到所有的数据，如果想要直观地看到所有的数据，就需要使用多表查询

-- 查询所有员工的信息和所在的部门信息

SELECT *
FROM emp,dept;

为什么会有56条记录呢？接下来看一下员工记录条数与部门记录条数 

-- 查询员工记录条数共14条
SELECT COUNT(*) AS '员工记录条数' FROM emp;
-- 查询部门记录条数共4条
SELECT COUNT(*) FROM dept;

一个是 4 条，一个是 14 条，每个员工只有一个部门，不能同时在多个部门，查询应该显示 14 条，为什么会显示 56 条呢？

仔细观察数据可以发现，每一位员工都查询出来 4 条数据，显示重复，虽然Smith的部门编号一直都是 20，但是所有的部门都和Smith进行了一个匹配，其中有 3 条数据是无效的

交叉连接

交叉连接不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积

• 查询员工及员工所在的部门信息

SELECT * FROM emp,dept;　
SELECT * FROM emp CROSS JOIN dept;　

接下来分析为什么会显示 56 条呢？

首先，如果不加条件直接进行查询，则数据条数是两个表记录条数的乘积，这种结果我们称之为笛卡尔乘积。

笛卡尔乘积公式：A表中数据条数 * B表中数据条数 = 笛卡尔乘积

A={A，B}，B={A，B，C}  A * B={AA，AB，AC，BA，BB，BC}

如果两张表的数据量都很大，那么这种庞大是非常可怕的，所以现在必须想办法消除掉笛卡尔积的无效记录。

想要消除笛卡尔积的无效记录，需要使用关联字段(也就是加上限定条件，员工表中的编号要等于部门表中的编号)。

范例：利用等值条件来处理笛卡尔积，对笛卡尔积的数据进行筛选，只需要满足条件的数据

为了避免二义性，需要指定表名称

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

如果表名非常长，如果每次都需要指定表名称就变得非常复杂，可以对表取别名。但是如果指定了别名，原来的表名在当前这个 sql 语句就不能使用了

-- 查询所有员工的信息和所在的部门信息
SELECT * FROM emp AS a,dept b WHERE a.deptno=b.deptno;

内连接

在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按匹配的条件可以分成等值连接和不等值连接。

有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。（所谓的链接表就是数据库在做查询形成的中间表）。

• 隐式内连接

SELECT * FROM 表1，表2 WHERE 条件;

• 显示内连接（使用关键字INNER JOIN）

SELECT * FROM 表1 [INNER] JOIN 表2 ON 条件; 

等值连接

在连接条件中使用等于号(=)运算符比较被连接列的列值。

• 查询员工及员工部门信息

#隐式连接
SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;
#显示连接
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;

• 等值连接可以使用USING来自动关联两表中相同的列

SELECT * FROM emp INNER JOIN dept USING(deptno);

非等值连接

在连接条件使用除等于运算符以外的其它比较运算符比较被连接的 列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>等。

• 查询每个员工的薪资等级，需要根据员工的薪资判断一下这个员工在薪资等级表中的哪一个区间

#隐式连接
SELECT * FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal;
#显示连接
SELECT * FROM emp e INNER JOIN salgrade s ON  e.sal BETWEEN s.losal AND s.hisal;

-- 非等值连接
-- 查询每个员工的薪资等级
SELECT *
FROM emp AS e,salgrade AS s
-- 员工的薪资在某个区间 不等值在某个范围之内
WHERE e.sal BETWEEN s.losal AND s.hisal;

-- 非等值连接
-- 查询每个员工的薪资等级
SELECT *
FROM emp AS e,salgrade AS s
-- 员工的薪资在某个区间 不等值在某个范围之内
WHERE e.sal>=s.losal AND e.sal<=s.hisal;

查询所有员工emp的信息以及部门dept的信息和薪资等级salgrade的信息

可以发现按照下面这种查询方式有 70 条数据，笛卡尔积为 14 × 5 = 70

-- 查询所有员工emp的信息以及部门dept的信息和薪资等级salgrade的信息
SELECT *
FROM emp AS e,dept d, salgrade s
WHERE e.deptno=d.deptno;

接下来对薪资等级进行限定，用 AND 进行连接

-- 查询所有员工emp的信息以及部门dept的信息和薪资等级salgrade的信息 
SELECT *
FROM emp AS e,dept d, salgrade s
WHERE e.deptno=d.deptno
-- 对薪资等级进行限定
AND e.sal BETWEEN s.losal AND s.hisal;

由于等级是按数字显示，具体内容不太明显，可以考虑使用 CASE 函数

-- 查询所有员工emp的信息以及部门dept的信息和薪资等级salgrade的信息 
SELECT *,
CASE s.grade 
	WHEN 1 THEN 'E'
	WHEN 2 THEN 'D'		
	WHEN 3 THEN 'C'
	WHEN 4 THEN 'B'
	WHEN 5 THEN 'A' 
END '薪资等级'
FROM emp AS e,dept d, salgrade s
WHERE e.deptno=d.deptno
-- 对薪资等级进行限定
AND e.sal BETWEEN s.losal AND s.hisal;

外连接

在表关系的笛卡尔积中，不仅保留表关系中所有匹配的数据记录，而且还保留部分不匹配的记录。按照保留不匹配条件数据记录来源可以分为左外连接(LEFT OUTER JOIN)、右外连接(RIGHT OUTER JOIN)和全外连接(FULL OUTER JOIN)。

外连接也是一种查表的方式，只是查询的结果有所不太一样。

外连接使用语法如下：

SELECT * FROM 表1 LEFT|RIGHT|FULL [OUTER] JOIN 表2 ON 条件; 

左外连接

在表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联左边表中不匹配的数据记录。

首先在 emp 表中插入一条NULL的记录，在进行等值连接的时候，在连接的时候是根据某一个字段把两个表先生成笛卡尔积，然后根据条件进行筛选，去掉一些不相关的。当连接方式条件有一方为空的时候，这个条件在左边匹配不到右边、右边匹配不到左边，应该如何显示？

由于原来的部门表中都有部门，所以我们先插入一条没有部门的记录

插入一条部门编号为NULL的数据(关于插入后面会提及)

INSERT INTO 
emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) 
VALUES(6666,'idex','teacher',7369,CURDATE(),800,100,NULL);

 

有 15 个人，查询到 14 条记录，由于连接条件 deptno 为空，不显示 Teacher，也就是左表中的数据不显示，属于隐式内连接

-- 内连接、等值连接 连接条件不成立的时候 条件为空或匹配不到数据的时候不显示
SELECT * FROM 
emp,dept WHERE emp.deptno=dept.deptno;

接下来看一下显式内连接，把表中的 '，' 变成关键字

-- 显式内连接
SELECT * FROM 
emp INNER JOIN dept
ON emp.deptno=dept.deptno;		

查询员工及对应的部门信息（没有部门的员工也显示出来，没有员工的部门不显示）

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

使用左外连接，显示 15 条数据

-- 外连接   连接条件不成立的时候 表中的数据照样显示
-- 左外连接 左表中的数据显示
SELECT * FROM 
emp LEFT JOIN dept
ON emp.deptno=dept.deptno;		

右外连接

在表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联右边表中不匹配的数据记录。

查询员工及对应的部门信息（没有部门的员工不显示，没有员工的部门显示）

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

使用右外连接，虽然没有部门编号是 40 的数据，但是也可以显示部门编号 40，前面的员工信息则显示为空

-- 右外连接  右表中的数据显示
SELECT * FROM 
emp RIGHT JOIN dept
ON emp.deptno=dept.deptno;

全连接

在表关系的笛卡尔积中，出了选择相匹配的数据记录，还包含关联 左右两边表 中不匹配的数据记录。

查询员工及对应的部门信息（没有部门的员工 显示，没有员工的部门 显示）

#mysql不支持全外连接但是别的数据库支持所以高亮显示 可以通过集合运算来实现
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

-- 全外连接 两个表中的数据都显示 mysql中不支持全外连接
SELECT * FROM
emp FULL JOIN dept
ON emp.deptno=dept.deptno;

自连接

自连接就是指表与其自身进行连接         

查询每个员工对应的领导姓名                

SELECT e.ename,e.mgr,me.ename 领导 FROM emp e,emp me WHERE e.mgr=me.empno;