第五章 数据库多表查询
1.简介
前面已经介绍了单表的查询操作,在数据库中有时为了提高数据的检索和存储效率,会将一些内容量比较大的表拆分成多个子表,并通过一些特殊的属性将这些子表关联起来。

2.表与表之间的关系
在数据库中,表之间需要通过某些相同的属性来建立连接关系,在两个表中至少应该有一列的数据属性是相同的,如图所示。
图片 1.png
在图中,两个表共有的列是stu-id,针对这一列的属性合并两个表的操作就是连接。数据库的表之间可以通过某种或多种方式关联,从而实现分散数据的组合和数据的交换、共享。表与表之间的关系包括一对一、一对多(多对一)、多对多。

3.一对一关系
数据表的一对一关系如图所示。
图片 2.png
一对一关系可以简单地理解为一张表的一条记录只能与另外一张表的一条记录进行对应,这与现实生活中的配偶关系相似,每个人只有一个或者没有配偶。下面将通过具体的案例演示数据表的一对一关系。创建订单表order_list,表结构如表所示。
图片 3.png
表中列出了order_list表的字段、字段类型、约束类型和说明,根据该表提供的信息创建order_list表,SQL语句如下所示。
图片 4.png
以上执行结果证明表order_list创建完成。接着创建备注表order_text,表结构如表所示。
图片 5.png
表中列出了order_text表的字段、字段类型、约束类型和说明,据此创建order_text表,SQL语句如下所示
图片 6.png
以上执行结果证明表order_text创建完成。直观地看两张表的关系,如图所示。
图片 7.png
从图中可以看出,order_list表与order_text表是一对一关系,这种关系经常用在数据库优化中。用户备注字段otext一般情况下会有较多的文字, 属于大文本字段,但这个字段又不是每次都会用到,如果将其存放在order_list 表中, 在查询订单信息时会影响表的查询效率,因此将otext字段单独拆分出来并放到从表中,当需要otext字段时,用户对两张表进行关联查询即可。
在实际的开发中,数据表的一对一关系主要应用于以下几个方面。
(1)分割列数较多的表。
(2)为了加强数据安全性而隐藏数据表中的一部分内容。
(3)保存临时数据,当不需要时可以直接删除从表,减少了操作步骤。

4.一对多关系
在一对多关系中,主键数据表中只允许有一条记录与其关系表中的一条或者多条记录相关联(也可以没有记录与之相关联)。另外,关系表中的一条记录只能对应主键数据表中的一条记录,如图所示。
图片 8.png
一对多关系可以简单地理解为一张表中的一条记录可以对应另外一张表中的一条或者多条记录,也可以没有记录与之关联。但是反过来,另外一张表的一条记录只能对应第一张表的一条记录。这与现实生活中父子的关系很像,主键所在的表可以称为父表,与其对应的关系表称为子表。为了使读者可以深入理解,接下来通过具体案例演示数据表的一对多关系。创建学生表student,表结构如表所示。
图片 9.png
表中列出了student表的字段、字段类型、约束类型和说明,接着创建student表,SQL语句如下所示。
图片 10.png
以上执行结果证明表studen 创建完成。下面创建成绩表score,表结构如表所示。
图片 11.png
表中列出了score表的字段、字段类型、约束类型和说明,接下来创建score表,SQL语句如下所示。
图片 12.png
以上执行结果证明成绩表score创建完成。直观展现这两张表的关系,如图所示。
图片 13.png
从图中可以看出,student表为父表,score表为子表。每个学生可能有多个成绩,但是一个成绩只能属于一个学生,这就是一对多关系。

5.多对一关系
多对一关系与一对多关系本质相同,只是从不同的角度来看问题。在图6.5中如果从score表的角度来看问题,多个成绩可以属于一个学生,但是一个成绩不能属于多个学生,这种关系就是多对一关系。

6.多对多关系
在多对多关系中,两个数据表中的数据需要通过“中间人”实现数据的连接,每条记录都可以和另一个数据表里任意数量的记录相关联。这种关系与现实生活中老师与班级的关系很像,一个老师可以负责多个班级,一个班级也可以有多个老师。多对多关系如图所示。
图片 14.png
为了让读者加深理解,接下来通过具体案例演示多对多关系。创建教师表teacher,表结构如表所示。
图片 15.png
表中列出了teacher表的字段、字段类型、约束类型和说明,接着创建teacher表,SQL语句如下所示。
图片 16.png
以上执行结果证明表teacher创建完成。下面创建班级表class,表结构如表所示。
图片 17.png
表中列出了class表的字段、字段类型、约束类型和说明,接着创建class表,SQL语句如下所示。
图片 19.png
以上执行结果证明班级表class创建完成。为了维护实体关系,还需要创建一张关系表tea_class,用于映射多对多的关系,表结构如表所示。
图片 18.png
表中列出了tea_class表的字段、字段类型、约束类型和说明,接着创建tea_class表,SQL语句如下所示。
图片 20.png
以上执行结果证明关系表tea_class创建完成。直观地看三张表的关系,如图所示。
图片 21.png
从图中可以看到,teacher表与class表都与关系表tea_class关联,且都是一对多的关系,因此teacher表与class表是多对多的关系, 即一个老师可以负责多个班级,一个班级也可以有多个老师。

7.合并结果集
在进行多表查询时,常会遇到需要将多个表的查询结果合并的情况。MySQL中提供UNION关键字和UNION ALL关键字用于将查询结果合并,下面对这两种查询方法进行讲解。
1.使用UNION关键字合并
创建测试表test1和测试表test2,然后分别向其中插入数据,再使用查询语句将两个表中的数据集合并。测试表test1的表结构如表所示。
图片 22.png
测试表test2的表结构如表所示。
图片 23.png
根据表提供的数据结构分别创建测试表test1和测试表test2,具体的操作代码如下所示。
图片 24.png
从以上代码的执行结果可以看出测试表创建成功。下面向其中插入相关数据,具体代码如下所示。
图片 25.png
从上方代码的执行结果可以看出数据插入成功。尝试使用UNION关键字对两张表进行合并结果集查询。
图片 26.png
从以上语句的执行结果可以看出,UNION语句将test1表和test2表中的数据进行了合并。需要注意的是,使用UNION关键字合并数据时会去除重复的数据。因为test1表中和test2表中存在相同的数据,所以查询结果只显示一条数据。
2.使用UNION ALL关键字合并
UNION ALL关键字与UNION关键字的属性类似,它也可以实现查询结果的合并,但不同的是UNION ALL关键字不会去除掉合并结果中重复的数据。使用UNION ALL关键字将test1表和test2表中的数据合并,具体的语句如下所示。
图片 27.png
从上方代码的执行结果可以看出,使用UNION ALL关键字将test1表和test2表中的数据进行了合并,并且两张表中的相同的数据“a”并没有被去除。

8.关于笛卡儿积
笛卡儿积可以理解为两个集合中所有数组的排列组合。在数据库中,使用这个概念表示两个表中的每一行数据的所有组合,如图所示。
图片 28.png
在图中,表A中存在数据a和b,使用集合形式表示为A={a,b}。表B中存在数据1、2、3,使用集合形式表示为B={1,2,3}。则两个表的笛卡儿积为{(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}。MySQL中使用交叉查询可以实现两个表之间所有数据的组合,语句的基本格式如下所示。
图片 29.png
交叉查询语法格式中,CROSS JOIN关键字用于连接两个要查询的表,从而实现查询两个表中的所有数据组合。接下来通过具体案例演示交叉查询语句的用法。创建员工表emp和部门表dept,emp表结构如表所示
图片 30.png
dept表结构如表所示。
图片 31.png
根据相关的表结构分别创建员工表emp和部门表dept,具体的SQL语句与执行过程如下所示。
图片 32.png
员工表emp和部门表dept创建完成后,分别向两张表中插入数据,具体的SQL语句和执行过程如下所示。
图片 33.png
从上方代码的执行结果可以看出,已经成功地向emp表和dept表中插入数据。接下来使用SELECT命令查看两张表的详细内容,具体的SQL语句如下所示。
图片 34.png
最后使用交叉查询的方式查询emp表和dept表中所有数据的组合,具体的SQL语句如下所示。
图片 35.png
上方语句中一共查询出20条员工表emp与部门表dept的数据组合。需要注意,这里分别为emp表和dept表定义了别名(emp表别名为e,dept表别名为d)。在实际应用中,通过笛卡儿积得到的数据并不能提供有效的信息,对两张表进行连接查询时,使用交叉查询并加入限制条件,所得到的数据才会有实际意义。查询每个员工及其对应部门的信息时加入过滤条件,将不需要的数据过滤掉,具体的SQL语句如下所示。
图片 36.png
从以上执行结果可看出,使用交叉查询并加入过滤条件,成功查询出所有员工及其对应的部门信息。

9.内连接
MySQL的INNER JOIN子句可以将一个表中的行与其他表中的行进行匹配,并允许从两个表查询包含列的行记录,效果与CROSS JOIN语句类似,其具体的语法格式如下所示。
图片 37.png
以上语法格式中,INNER JOIN用于连接两个表,因为MySQL默认的连接方式就是内连接,所以语法格式中的INNER可以省略。ON用来指定连接条件,类似于WHERE。简单地说,INNER JOIN子句就是将两个表中的记录组合,并返回关联字段相符的记录,也就是返回两个表的交集(阴影)部分。INNER JOIN子句是SELECT语句的可选部分,它出现在FROM子句之后。在使用INNER JOIN子句时,需要注意以下问题。
图片 38.png
(1)需要在FROM子句中指定主表。
(2)理论上讲,INNER JOIN子句可以连接多个其他表,但是,为了获得更好的性能,建议连接表的数量不要超过3个。
(3)连接条件需要出现在INNER JOIN子句的ON关键字之后,连接条件是将主表中的行与其他表中的行进行匹配的规则。为了使读者更加清楚地了解INNER JOIN语句的使用方法,下面将用具体的实例进行演示。使用INNER JOIN子句查询每个员工及其对应部门的信息,具体的SQL语句如下所示。
图片 39.png
从上方代码的执行结果可以看出,使用INNER JOIN子句查询出了每个员工对应的部门信息,读者可以与前面使用的CROSS JOIN语句进行对比。 另外,INNER JOIN子句后也可以加入查询条件。例如,查询编号为0001的员工的详细信息的SQL语句如下。
图片 40.png
从上方代码的执行结果可以看出,成功查询出了0001号员工的详细信息

10.外连接
外连接查询与内连接查询不同的是,内连接查询的返回结果只包含符合查询条件和连接条件的数据,而外连接查询可以返回没有关联的数据,返回结果不仅包含符合条件的数据,而且包含左表或右表或两个表中的所有数据。外连接查询主要包括左外连接和右外连接,接下来进行详细讲解。
1.左外连接
左外连接以左表为基准,查询结果中不仅显示左表满足条件的数据,还显示不满足条件的数据(左表的数据全部显示),而右表只保留满足条件的数据,不满足条件的显示为空。左外连接的工作原理如图所示
图片 41.png
图中的阴影部分表示左外连接的查询结果。左外连接查询的具体语法格式如下所示。
图片 42.png
以上语法格式中,LEFT JOIN表示返回左表中的所有记录以及右表中符合连接条件的记录,OUTER是可以省略的,ON后面是两张表的连接条件,WHERE后面可以加查询条件。接下来通过具体案例演示左外连接的使用。新建a_table表和b_table表,并向其中插入数据,两张表的详细内容如下。
图片 43.png
使用左外连接查询a_table表和b_table表的所有员工信息,其中a_table为左表,具体的SQL语句如下所示。
图片 44.png
从以上执行结果可看出,a_table表中的所有员工信息都显示了出来,而b_tableb表中员工编号为5和6的两位员工的信息并没有显示。
2.右外连接
右外连接以右表为基准,右表的数据行全部保留,左表保留符合连接条件的行。右外连接的工作原理如图所示。
图片 45.png
图中的阴影部分表示右外连接的查询结果。右外连接查询的具体语法格式如下所示。
图片 46.png
右外连接与左外连接一样,语法格式中的ON后面是两张表的连接条件。使用右外连接对a_table表和b_table表进行查询,并以b_table表为右表的SQL语句如下所示。
图片 47.png
从以上代码的执行结果可看出,b_table表的数据全部显示了出来,而a_table表中员工编号为1和4的员工信息并没有显示出来。

11.自然链接
前面学习的表连接查询需要指定表与表之间的连接字段,MySQL中还有一种自然连接,不需要指定连接字段,表与表之间列名和数据类型相同的字段会被自动匹配。自然连接默认按内连接的方式进行查询,其语法格式如下所示。
图片 48.png
以上语法格式中,通过NATURAL关键字使两张表进行自然连接,默认按内连接的方式进行查询。使用自然连接查询emp表和dept表的员工信息的SQL语句如下所示。
图片 49.png
从以上执行结果可看出,通过自然连接,不需要指定连接字段,就会查询出正确的结果,不会出现重复数据,这是自然连接默认的连接查询方式。自然连接也可以指定使用左连接或右连接的方式进行查询,语法格式如下所示。
图片 50.png
以上语法格式中,若需要指定左连接或右连接,添加LEFT关键字或RIGHT关键字即可。关于自然连接的左连接查询和右连接查询此处不再赘述,读者可以自行操作。

12.嵌套查询
嵌套查询又被称为子查询,其语句特点是在SELECT语句中包含SELECT语句。子查询可以在WHERE关键字后作为查询条件或在FROM关键字后作为表来使用,接下来详细讲解子查询的相关内容。
1.子查询作为查询条件
在复杂查询中,子查询往往作为查询条件来使用,它可以嵌套在一个SELECT语句中,SELECT语句放在WHERE关键字后。执行查询语句时,首先会执行子查询中的语句,然后将返回结果作为外层查询的过滤条件。接下来通过具体实例演示子查询作为查询条件的使用。
图片 51.png
从以上执行结果可看出,有3个员工的工资高于JONES。SQL语句中使用子查询查出了JONES的工资,将结果作为查询条件查出了工资高于JONES的员工的信息。查询与SCOTT同一个部门的所有员工的信息。
图片 52.png
从以上执行结果可看出,有5个员工与SCOTT在同一个部门。SQL语句中使用子查询查出了SCOTT的部门编号,将结果作为查询条件查出了该部门的所有员工信息。查询工资高于30号部门所有人的员工的信息。
图片 53.png
从以上执行结果可看出,有4个员工工资高于30号部门所有人。SQL语句中使用子查询查出了30号部门所有人的工资,将结果作为查询条件进行比较。
2.子查询作为表
前面讲解了子查询作为查询条件来使用,子查询还可以作为表来使用。SELECT子句放在FROM关键字后,执行查询语句时,首先会执行子查询中的语句,然后将返回结果作为外层查询的数据源使用。接下来通过具体实例演示子查询作为表的使用。
图片 54.png
从以上执行结果可看出,编号为7788的员工是SCOTT,查询语句查询出了他的名字、工资、部门名称和部门地址。SQL语句中使用子查询查出了所有的部门编号、部门名称和部门地址,然后将返回结果作为外层查询的数据源使用。

上一篇 下一篇