MySQL数据库从入门到精通—多表查询— 连接查询、子查询 ...

小莱昂纳德

连接查询
在关系型数据库中建立数据表时不必确定各个数据之间的关系，通常将每个实体的所有信息存放在一个表中。当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询，接下来将详细讲解连接查询的相关内容。
创建数据表和表结构的说明
在讲解查询之前，首先需要创建两个数据表（员工表emp和部门表dept）并插入数据，用于后面的例题演示。首先了解员工表emp的表结构，如表6.8所示。
表6.8 emp表

字段	字段类型	说明
empno	INT	员工编号
ename	VARCHAR(50)	员工姓名
job	VARCHAR(50)	员工工作
mgr	INT	领导编号
hiredate	DATE	入职日期
sal	DECIMAL(7,2)	月薪
comm	DECIMAL(7,2)	奖金
deptno	INT	部门编号

在表6.8中列出了员工表的字段、字段类型和说明。然后创建emp表。


在创建完成emp表后向该表中插入数据。


部门表dept的表结构如表6.9所示。
表6.9 dept表

字段	字段类型	说明
deptno	int	部门编码
dname	varchar(50)	部门名称
loc	varchar(50)	部门所在地点

在表6.9中列出了部门表的字段、字段类型和说明。然后创建部门表。


在创建完成部门表后向该表中插入数据。


至此两张表创建完成，本章后面的演示例题会用到这两张表。
笛卡儿积
笛卡儿积在SQL中的实现方式是交叉连接（Cross Join），所有连接方式都会先生成临时笛卡儿积表。笛卡儿积是关系代数里的一个概念，表示两个表中的每一行数据任意组合。接下来通过一个案例演示笛卡儿积问题，此处使用交叉查询来演示，其语法格式如下。


在以上语法格式中，CROSS JOIN用于连接两个需要查询的表，可以查询两个表中的所有数据组合。
在实际应用中，笛卡儿积本身大多没有什么实际用处，只有在两个表连接时加上限制条件才会有实际意义。


从以上执行结果可以看出，使用交叉查询并加入过滤条件成功地查询出需要的结果数据，总共查询出了14名员工及其对应部门的信息。

内连接
内连接的连接查询结果集中仅包含满足条件的行，在MySQL中默认的连接方式就是内连接。前面学习了交叉连接的语法，但该语法并不是SQL标准中的查询方式，可以理解为方言。SQL标准中的内连接语法格式如下。


在以上语法格式中，INNER JOIN用于连接两个表，其中INNER可以省略，因为MySQL默认的连接方式就是内连接，ON用来指定连接条件，类似于WHERE关键字。
使用SQL标准语法查询所有员工编号、员工姓名、部门编号、部门名称。


从以上执行结果可以看出，使用SQL标准语法进行内连接查询出了所有员工及对应部门的信息，其中,“emp e”表示为emp表起别名e，“dept d”表示为dept表起别名d，“e.字段名”或“d.字段名”代表对应表中字段名。
接着使用SQL标准语法查询姓名中包含字母A的员工编号和姓名，以及所在部门的编号和名称。



外连接
前面讲解了内连接的查询，返回的结果只包含符合查询条件和连接条件的数据，然而有时还需要包含没有关联的数据，返回的查询结果中不仅包含符合条件的数据，还包含左表或右表或两个表中的所有数据，此时就需要用到外连接查询。外连接查询包括左外连接和右外连接两种查询类型，接下来进行详细讲解。
1.左外连接
左外连接是以左表中的数据为基准，若左表中有数据且右表中没有数据，则显示左表中的数据，右表中的数据显示为空。左外连接的语法格式如下。


在以上语法格式中，LEFT JOIN表示返回左表中的所有记录以及右表中符合连接条件的记录，OUTER可以省略不写，ON后面是两张表的连接条件，在WHERE关键字后面可以添加查询条件。
2.右外连接
右外连接是以右表中的数据为基准，若右表中有数据且左表中没有数据，则显示右表中的数据，左表中的数据显示为空。右外连接的语法格式如下。


在以上语法格式中，RIGHT JOIN表示返回右表中的所有记录以及左表中符合连接条件的记录，OUTER可以省略不写，ON后面是两张表的连接条件，在WHERE关键字后面可以加查询条件。
多表连接
前面学习了内连接和外连接，它们都是两张表之间的连接查询。实际上随着业务的复杂，可能需要连接更多的表（3张、4张甚至更多），但表若连接过多会严重影响查询效率，因此连接查询一般不超出7张表的连接。多表连接的语法格式如下。


在以上语法格式中，为了方便书写，通常会给表起别名，当然也可以不起别名。多个表通过JOIN关键字连接，ON关键字后面是表与表之间的关系字段。
首先创建student表。


以上执行结果证明student表创建完成。之后向student表中添加数据。


接着创建subject表。


以上执行结果证明subject表创建完成。然后向subject表中添加数据。


最后创建score表。


以上执行结果证明score表创建完成。然后向score表中添加数据。


查询出学生的编号和姓名以及所学科目和对应分数。


从以上执行结果可以看出，通过连接student、score和subject几张表查询出了学生的编号和姓名，以及所学科目和对应分数，这是多表连接查询的基本应用。

自然连接
前面学习了表的连接查询，需要指定表与表之间的连接字段。在SQL标准中还有一种自然连接，不需要指定连接字段，表与表之间列名和数据类型相同的字段会被自动匹配。自然连接默认按内连接的方式进行查询，语法格式如下。


在以上语法格式中，通过NATURAL关键字使两张表进行自然连接，默认按内连接的方式进行查询。
自然连接也可以指定使用左连接或右连接的方式进行查询，语法格式如下。


在以上语法格式中，若需要指定左连接或右连接，只需要添加LEFT或RIGHT关键字即可。
自连接
前面讲解了多表连接查询，在MySQL中还有一种很特殊的连接查询——自连接。在自连接时连接的两张表是同一张表，通过起别名进行区分，其语法格式如下。


在以上语法格式中，通过给表名起多个别名实现自连接查询。
查询员工编号为7369的员工姓名以及对应经理的编号和姓名。


从以上执行结果可以看出，通过自连接查询出了员工编号为7369的员工姓名以及对应经理的编号和姓名，但自连接在实际应用中并不多见。

子查询
子查询就是嵌套查询，即在SELECT中包含SELECT。子查询可以在WHERE关键字后面作为查询条件，也可以在FROM关键字后面作为表来使用，接下来详细讲解子查询的相关内容。
子查询作为查询条件
在复杂查询中，子查询往往作为条件来使用。它可以嵌套在一个SELECT语句中，SELECT语句放在WHERE关键字的后面。当执行查询语句时，首先会执行子查询中的语句，然后将返回结果作为外层查询的过滤条件。
查询所有工资高于JONES的员工信息。


从以上执行结果可以看出，有3名员工的工资高于JONES。在SQL语句中首先使用子查询查出JONES的工资，然后将结果作为查询条件查出高于JONES工资的员工的信息。
接着查询与SCOTT在同一个部门的所有员工信息。


从以上执行结果可以看出，有5名员工与SCOTT在同一个部门。在SQL语句中首先使用子查询查出SCOTT所在部门的编号，然后将结果作为查询条件查出该部门的所有员工的信息。
接着查询工资高于30号部门的所有人的员工信息。


从以上执行结果可以看出，有4名员工的工资高于30号部门的所有人。在SQL语句中首先使用子查询查出30号部门的所有人的工资，然后将结果作为查询条件进行比较。
接着查询工作和工资与MARTIN完全相同的员工信息。


从以上执行结果可以看出，有两名员工工作和工资与MARTIN完全相同。在SQL语句中首先使用子查询查出MARTIN的工作与工资，然后将结果作为查询条件进行比较。

子查询作为表
前面讲解了将子查询作为查询条件来使用，子查询还可以作为表来使用，即把SELECT子句放在FROM关键字的后面。在执行查询语句时，首先会执行子查询中的语句，然后将返回结果作为外层查询的数据源使用。
查询编号为7788的员工姓名和工资以及所在部门的名称和部门地址。


从以上执行结果可以看出，编号为7788的员工是SCOTT，查询语句查询出了其姓名和工资以及所在部门的名称和部门地址。在SQL语句中首先使用子查询查询出了所有部门的名称、地址和编号，然后将返回结果作为外层查询的数据源使用。
小结


https://www.zhihu.com/video/1565993701180153856
本文首先介绍了表与表之间的关系，讲解了一对一、一对多和多对多的概念，接着介绍了合并结果集的用法与连接查询的用法，最后讲解了子查询。对于本文内容，大家需要多练习，不需要死记硬背。

小犊马

是爷们的娘们的都帮顶！大力支持