mysql联查显示有数据的那张表数据 mysql表连接查询的原理

转载

温柔一刀 2024-02-12 21:33:10

文章标签 mysql联查显示有数据的那张表数据 mysql 数据库 sql css 文章分类 MySQL 数据库

SQL最强大的功能之一就是能在数据检索查询的执行中连接(JOIN)表。连接是利用SQL的SELECT能执行的最重要的操作，很好地理解连接及其语法是学习SQL的一个极为重要的组成部分。

SQL查询的基本原理

单表查询：
根据WHERE条件过滤表中的记录，然后根据SELECT指定的列返回查询结果。
两表连接查询：
使用ON条件对两表进行连接形成一张虚拟结果集；然后根据WHERE条件过滤结果集中的记录，再根据SELECT指定的列返回查询结果。
多表连接查询：
先对第一个和第二个表按照两表连接查询，然后用用连接后的虚拟结果集和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一张虚拟结果集，然后根据WHERE条件过滤虚拟结果集中的记录，再根据SELECT指定的列返回查询结果。

多表连接的结果通过三个属性决定

方向性：在外连接中写在前边的表为左表、写在后边的表为右表。
主附关系：主表要出所有的数据范围，附表与主表无匹配项时标记为NULL，内连接时无主附表之分。
对应关系：关键字段中有重复值的表为多表，没有重复值的表为一表。

表对应关系

一对一关系

在一对一关系中，A 表中的一行最多只能匹配于 B 表中的一行，反之亦然。如果相关列都是主键或都具有唯一约束，则可以创建一对一关系。

这种关系并不常见，因为一般来说，按照这种方式相关的信息都在一个表中。可以利用一对一关系来：

分割具有多列的表。
由于安全原因而隔离表的一部分。
保存临时的数据，并且可以毫不费力地通过删除该表而删除这些数据。
保存只适用于主表的子集的信息。

mysql联查显示有数据的那张表数据 mysql表连接查询的原理_mysql

一对多关系

一对多关系是最普通的一种关系。在这种关系中，A 表中的一行可以匹配 B 表中的多行，但是 B 表中的一行只能匹配 A 表中的一行。例如，部门表和人员表之间具有一对多关系：每个部门有很多员工，但是每个员工只属于一个部门。

只有当一个相关列是一个主键或具有唯一约束时，才能创建一对多关系。

mysql联查显示有数据的那张表数据 mysql表连接查询的原理_sql_02

多对多关系

在多对多关系中，A 表中的一行可以匹配 B 表中的多行，反之亦然。要创建这种关系，需要定义第三个表，称为结合表，它的主键由 A 表和 B 表的外部键组成。

mysql联查显示有数据的那张表数据 mysql表连接查询的原理_sql_03

表连接方式

不同的数据分析工具	支持的表连接方式
Oracle/ sql server/ Tableau/ Python	内连接(inner join)、左连接(left join)、右连接(right join)、全连接(full join)
MySQL	内连接(inner join)、左连接(left join)、右连接(right join)
Power BI	内连接、左连接、右连接、全连接、左反连接、右反连接

示例表格

员工表 emp

empno	ename	job	mgr	hiretime	sal	comm	deptno
7369	SMITH	CLERK	7902	1980/12/17	800		20
7499	ALLEN	SALESMAN	7698	1981/2/20	1600	300	30
7521	WARD	SALESMAN	7698	1981/2/22	1250	500	30
7566	JONES	MANAGER	7839	1981/4/2	2975		20
7654	MARTIN	SALESMAN	7698	1981/9/28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981/5/1	2850		30
7782	CLARK	MANAGER	7839	1981/6/9	2450		10
7788	SCOTT	ANALYST	7566	1987/4/19	3000		20
7839	KING	PRESIDENT		1981/11/17	5000		10
7844	TURNER	SALESMAN	7698	1981/9/8	1500	0	30
7876	ADAMS	CLERK	7788	1987/5/23	1100		20
7900	JAMES	CLERK	7698	1981/12/3	950		30
7902	FORD	ANALYST	7566	1981/12/3	3000		20
7934	MILLER	CLERK	7782	1982/1/23	1300		10

部门表 dept

deptno	dname	loc
10	accounting	new york
20	research	dalls
30	sales	chicago
40	operations	boston

工资等级表 salgrade

grade	losal	hisal
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

内连接 (`inner join`)

按照连接条件，返回两张表中满足条件的记录。

mysql联查显示有数据的那张表数据 mysql表连接查询的原理_mysql联查显示有数据的那张表数据_04

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分以下几种：

等值连接： 在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
自然连接： 在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重列。
自连接： 自连接通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。
笛卡尔积连接： 两张表中的每一条记录进行笛卡尔积组合，然后根据WHERE条件过滤虚拟结果集中的记录。

SELECT <字段名1>[,<字段名2>...,<字段名n>] 
FROM <表名1> 
[INNER] JOIN <表名2> 
ON <连接条件>；

1、等值连接

查询每位员工的 `ename, job, hiretime, sal, dname`

SELECT ename,job,hiretime,sal,dname
FROM emp 
INNER JOIN dept
ON emp.deptno=dept.deptno;

结果

ename	job	hiretime	sal	dname
CLARK	MANAGER	1981-06-09	2450	accounting
KING	PRESIDENT	1981-11-17	5000	accounting
MILLER	CLERK	1982-01-23	1300	accounting
SMITH	CLERK	1980-12-17	800	research
JONES	MANAGER	1981-04-02	2975	research
SCOTT	ANALYST	1987-04-19	3000	research
ADAMS	CLERK	1987-05-23	1100	research
FORD	ANALYST	1981-12-03	3000	research
ALLEN	SALESMAN	1981-02-20	1600	sales
WARD	SALESMAN	1981-02-22	1250	sales
MARTIN	SALESMAN	1981-09-28	1250	sales
BLAKE	MANAGER	1981-05-01	2850	sales
TURNER	SALESMAN	1981-09-08	1500	sales
JAMES	CLERK	1981-12-03	950	sales

2、不等连接

查询在工资等级范围内的每位员工的 `ename, job, hiretime, sal`

SELECT ename,job,hiretime,sal
FROM emp 
INNER JOIN salgrade
ON sal BETWEEN losal AND hisal;

结果

ename	job	hiretime	sal
SMITH	CLERK	1980-12-17	800
ALLEN	SALESMAN	1981-02-20	1600
WARD	SALESMAN	1981-02-22	1250
JONES	MANAGER	1981-04-02	2975
MARTIN	SALESMAN	1981-09-28	1250
BLAKE	MANAGER	1981-05-01	2850
CLARK	MANAGER	1981-06-09	2450
SCOTT	ANALYST	1987-04-19	3000
KING	PRESIDENT	1981-11-17	5000
TURNER	SALESMAN	1981-09-08	1500
ADAMS	CLERK	1987-05-23	1100
JAMES	CLERK	1981-12-03	950
FORD	ANALYST	1981-12-03	3000
MILLER	CLERK	1982-01-23	1300

3、自然连接

无论何时对表进行连接，应该至少有一个列出现在不止一个表中（被连接的列）。标准的连接（前一章中介绍的内部连接）返回所有数据，甚至相同的列多次出现。自然连接排除多次出现，使每个列只返回一次。

以下例子将会自动排除相同的列'deptno'

SELECT emp.*, dept.dname
FROM emp
JOIN dept
WHERE ename = 'FORD';

结果

empno	ename	job	mgr	hiretime	sal	comm	deptno	dname
7902	FORD	ANALYST	7566	1981-12-03	3000		20	accounting
7902	FORD	ANALYST	7566	1981-12-03	3000		20	research
7902	FORD	ANALYST	7566	1981-12-03	3000		20	sales
7902	FORD	ANALYST	7566	1981-12-03	3000		20	operations

4、自连接

通过设置表别名，将一张表虚拟成多张表。返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。

使用表别名的主要原因之一是能在单条 SELECT 语句中不止一次引用相同的表。

当两张表格中没有相同的列时，则为笛卡尔积连接。

查询所有管理者姓名及其下属员工姓名

SELECT 员工表.ename AS 员工姓名, 领导表.ename AS 领导姓名
FROM emp AS 员工表
INNER JOIN emp AS 领导表
ON 员工表.mgr=领导表.empno;

表限定符.：如果表1和表2中的字段名相同，则必须使用表限定符.指定引用的是哪个表中的字段。

结果

员工姓名	领导姓名
SMITH	FORD
ALLEN	BLAKE
WARD	BLAKE
JONES	KING
MARTIN	BLAKE
BLAKE	KING
CLARK	KING
SCOTT	JONES
TURNER	BLAKE
ADAMS	SCOTT
JAMES	BLAKE
FORD	JONES
MILLER	CLARK

5、笛卡尔积连接

笛卡儿积（cartesianproduct）由没有联结条件的表关系返回的结果为笛卡儿积。
检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

SELECT <字段名1>[,<字段名2>...,<字段名n>] 
FROM <表名1>,<表名2> 
WHERE <筛选条件>;

查询所有管理者姓名及其下属员工姓名

SELECT 领导表.ename AS 领导姓名, 员工表.ename AS 员工姓名
FROM emp AS 领导表, emp AS 员工表 
WHERE 领导表.empno = 员工表.mgr;

结果

领导姓名	员工姓名
FORD	SMITH
BLAKE	ALLEN
BLAKE	WARD
KING	JONES
BLAKE	MARTIN
KING	BLAKE
KING	CLARK
JONES	SCOTT
BLAKE	TURNER
SCOTT	ADAMS
BLAKE	JAMES
JONES	FORD
CLARK	MILLER

左连接

内连接时，返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。

左连接是按照连接条件，返回两张表中满足条件的记录，以及左表中的所有记录，右表匹配不到显示为NULL。

mysql联查显示有数据的那张表数据 mysql表连接查询的原理_css_05

SELECT <字段名1>[,<字段名2>...,<字段名n>] 
FROM <表名1> 
LEFT JOIN <表名2> 
ON <连接条件>；

查询每位员工的 ename,dname,sal

SELECT ename,dname,sal
FROM emp 
LEFT JOIN dept
ON emp.deptno=dept.deptno;

结果

ename	dname	sal
ALLEN	sales	1600
WARD	sales	1250
JONES	research	2975
MARTIN	sales	1250
BLAKE	sales	2850
CLARK	accounting	2450
SCOTT	research	3000
KING	accounting	5000
TURNER	sales	1500
ADAMS	research	1100
JAMES	sales	950
FORD	research	3000
MILLER	accounting	1300

右连接

按照连接条件，返回两张表中满足条件的记录，以及右表中的所有记录，左表匹配不到显示为NULL

mysql联查显示有数据的那张表数据 mysql表连接查询的原理_sql_06

SELECT <字段名1>[,<字段名2>...,<字段名n>] 
FROM <表名1> 
RIGHT JOIN <表名2> 
ON <连接条件>；

查询每个部门的员工数（没有员工的部门，员工数统计为0）

SELECT dept.deptno,dname,COUNT(empno) AS empnum
FROM emp
RIGHT JOIN dept
ON dept.deptno=emp.deptno
GROUP BY dept.deptno;

结果

deptno	dname	empnum
10	accounting	3
20	research	5
30	sales	6
40	operations	0

WHERE 子句的重要性
在一条SELECT 语句中联结几个表时，相应的关系是在运行中构造的。在联结两个表时，实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE 子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE 子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

一表作为主表可以保证维度的完整性，多表作为主表可以保证度量的准确性。在没有明确表示需要保证维度完整性的情况下，优先保证度量的准确性，所以将度量值所在的表作为主表。度量字段通常存在于多表中，因此通常情况下可以将多表作为主表进行外连接。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。