第8章 连接查询与集合查询.ppt

第8章连接查询与集合查询,在对数据库的查询过程中，有些时候检索一张表中的数据记录往往不能满足开发人员或者客户的需要。例如，查询学生的选课成绩信息。而学生选课信息和课程成绩信息分别在两个不同的数据表中。其中，在课程信息表（T_curriculum）中包括课程的编号、课程的名字、课程的学分、课时以及授课的教师等学生选课信息，而学生的编号、选课的课程编号以及课程成绩等信息在成绩信息表（T_result）中，此时为了在查询的结果中显示学生选课信息和所选课程的相关信息，就需要同时检索课程信息表（T_curriculum）和成绩信息表（T_result）。这就需要进行连接查询的操作。,第8章连接查询与集合查询,连接查询主要包括内连接、交叉连接、自连接、以及外连接查询四种类型。本章主要介绍这四种连接查询的使用方法，在本章的最后还将介绍三种主要的集合查询。等值连接和非等值连接查询的使用方法使用ON子句建立相等连接使用USING子句建立相等连接交叉连接的使用方法自连接查询的使用方法外连接查询的使用方法集合查询的使用方法,8.1内连接查询,很多时候，需要将几个表连接起来并查询满足条件的记录，这个时候就需要使用内连接。内连接包括等值连接和非等值连接。除了使用WHERE子句中的等号运算符（=）建立等值连接外，还可以使用ON子句和USING子句建立建立等值连接。这一节将介绍几种内连接的查询方法。,8.1.1等值连接,等值连接是指将指定的连接条件通过使用等号运算符（=）连接起来，并返回符合连接条件的数据行。其语法格式如下：SELECT表名1.字段,表名2.字段.FROM表名1,表名2WHERE表名1.字段1=表名2.字段2其中，SELECT语句中表名1.字段和表名2.字段表示指定数据表1和数据表2中要查询的列；FROM语句中表名1和表名2表示指定连接的数据表的名字；WHERE子句中表名1.字段1=表名2.字段2表示用于指定连接条件的列。这里的字段1中的列和字段2中的列必须是两个表之间相互关联的列。,8.1.2非等值连接,非等值连接是指使用除等号运算符（=）以外的其他运算符将指定条件连接起来而执行的查询操作。其他运算符包括、=（大于等于）、（大于）、（小于）、!=（不等于）等，还可以使用BETWEENAND运算符。,8.1.3使用ON子句建立相等连接,在SQL语句中除了WHERE子句中使用等号运算符（=）实现等值连接的操作之外，还可以使用ON子句建立相等连接条件。其语法规则如下：SELECT表名1.字段,表名2.字段.FROM表名1JOIN表名2ON表名1.字段1=表名2.字段其中关键字JOIN表示将表1和表2连接起来，ON子句用来指定连接条件的列。这里的字段1中的列和字段2中的列必须是两个表之间相互关联的列。,8.1.4使用USING子句建立相等连接,在进行连接操作时，有时只希望将两张表中相互关联的列建立一个等值连接。此时，可以使用USING子句建立相等连接来简化使用等号运算符（=）建立的等值连接操作。其语法规范如下：SELECT表名1.字段,表名2.字段.FROM表名1JOIN表名2USING(字段1)其中关键字JOIN表示将表1和表2连接起来，USING子句中使用括号将字段1括起来，字段1就是两个表中建立等值连接相互关联的列。,8.2交叉连接,交叉连接返回的结果是一个笛卡尔积。所谓笛卡尔积实际就是两个集合相乘的结果。假设集合A中有n个元素，集合B中有m个元素，如果最后返回的结果是n*m，那么这个结果就是集合A和集合B的笛卡尔积。,8.3自连接查询,前面讲到的连接都是在表与表之间进行的。连接查询除了可以在不同的表之间进行，也可以对同一张表实现连接操作，这种连接查询的方式称为自连接。所谓自连接，就是指一个数据表与其自身进行连接。其语法规则如下：SELECTA.字段,A.字段.FROM表名1A,表名1BWHEREA.字段=B.字段由于连接的是同一张表，所以在FROM语句中需要为表定义不同的别名，这里为表1分别定义了表的别名为A和B。SELECT语句中可以使用A.字段的形式也可以使用B.字段的形式查询需要的记录。这里的SELECT语句中使用的是A.字段的形式。,8.3自连接查询,例如，在课程信息表中选择学分数比操作系统的学分数多的课程信息。SELECTC2.curID,C2.curName,C2.creditFROMT_curriculumC1,T_curriculumC2WHEREC1.curName=操作系统ANDC1.creditC2.credit,8.4外连接查询,在前面讲述的连接操作中，返回的结果都是满足连接条件的记录。有些时候，开发人员或者用户对于不满足连接条件的部分记录也感兴趣，这个时候就需要使用外连接查询。外连接查询不仅可以返回满足连接条件的记录，对于一个数据表中在另一个数据表中不匹配的记录也可以返回。外连接查询主要包括三种：左外连接、右外连接和全外连接。这一节将介绍这三种连接在不同数据库中的使用方法。,8.4.1左外连接,左外连接中查询的结果中不仅将显示满足连接条件的记录，而且还包括左侧表中不满足查询条件的记录。下面分别介绍在Oracle数据库、MySQL数据库和MicrosoftSQLServer数据库中左外连接的使用方法。1Oracle数据库2MySQL和MicrosoftSQLServer数据库,8.4.1左外连接,1Oracle数据库在Oracle数据库中，可以使用加号运算符（+）来表示左外连接。当该加号运算符（+）出现在连接条件的左边时，就称之为左外连接。其语法格式如下：SELECT表名1.字段,表名2.字段.FROM表名1,表名2WHERE表名1.字段1(+)=表名2.字段2其中，SELECT语句中表名1.字段和表名2.字段表示指定数据表1和数据表2中要查询的列；FROM语句中表名1和表名2表示指定连接的数据表的名字；WHERE子句中表名1.字段1(+)=表名2.字段2表示左外连接。此时，表名1.字段1所在的列的值将会被全部查询出来。,8.4.1左外连接,2.MySQL和MicrosoftSQLServer数据库在MySQL数据库和MicrosoftSQLServer数据库中可以使用LEFTOUTERJOIN关键字实现，其中OUTER关键字是可选的。使用LEFTOUTERJOIN关键字实现左外连接的语法规则如下：SELECT表名1.字段,表名2.字段.FROM表名1LEFTJOIN表名2ON表名1.字段1=表名2.字段2这里使用LEFTJOIN关键字代替SQL语句中FROM语句里的逗号，使用ON子句代替标准SQL语句中的WHERE子句，并将SQL语句中表示左外连接的加号运算符（+）去除。,8.4.2右外连接,右外连接中查询的结果中不仅将显示满足连接条件的记录，而且还包括右侧表中不满足查询条件的记录。下面分别介绍在Oracle数据库、MySQL数据库和MicrosoftSQLServer数据库中右外连接的使用方法。1Oracle数据库2MySQL和MicrosoftSQLServer数据库,8.4.2右外连接,1.Oracle数据库在Oracle数据库中，当该加号运算符（+）出现在连接条件的右边时，就称之为右外连接。其语法格式如下：SELECT表名1.字段,表名2.字段.FROM表名1,表名2WHERE表名1.字段1=表名2.字段2(+)其中，SELECT语句中表名1.字段和表名2.字段表示指定数据表1和数据表2中要查询的列；FROM语句中表名1和表名2表示指定连接的数据表的名字；WHERE子句中表名1.字段1=表名2.字段2(+)表示左外连接。此时，表名2.字段2所在的列的值将会被全部查询出来。,8.4.2右外连接,2.MySQL和MicrosoftSQLServer数据库在MySQL和MicrosoftSQLServer数据库中可以使用RIGHTOUTERJOIN关键字实现，其中OUTER关键字是可选的。使用RIGHTOUTERJOIN关键字实现左外连接的语法规则如下：SELECT表名1.字段,表名2.字段.FROM表名1RIGHTJOIN表名2ON表名1.字段1=表名2.字段2这里使用RIGHTJOIN关键字代替SQL语句中FROM语句里的逗号，使用ON子句代替SQL语句中的WHERE子句，并将标准SQL语句中表示右外连接的加号运算符（+）去除。,8.4.2全外连接,全外连接中查询的结果中不仅将显示左侧表中不满足连接条件的记录，而且还会显示右侧表中不满足查询条件的记录。全外连接可以认为是左外连接与右外连接的合集（不包括重复行）。全外连接可以使用FULLOUTERJOIN关键字实现，其中OUTER关键字是可选的。使用FULLOUTERJOIN关键字实现左外连接的语法规则如下：SELECT表名1.字段,表名2.字段.FROM表名1FULLJOIN表名2ON表名1.字段1=表名2.字段2这里使用FULLJOIN关键字代替SQL语句中FROM语句里的逗号，使用ON子句代替SQL语句中的WHERE子句,8.5集合查询,在SQL的连接查询语句中，还有一种查询方式就是结合查询。集合查询主要包括三种：并操作、交操作和差操作。其中交操作和差操作并不是对目前主流的所有的数据库的适用。这一节中将分别介绍这三种集合的操作方法。,8.5.1并操作（UNION）,执行并操作使用的关键字是UNION。并操作返回的结果集是包括了两个查询语句中查询出来的所有不同的行，不包含重复行。其语法格式如下：SELECT语句1UNIONSELECT语句2其中语句1和语句2表示的是两个用于查询的SELECT语句。UNION关键字表示对这两个查询语句查询出来的结果进行并操作。这里需要保证SELECT语句1和SELECT语句2中查询出的列数必须相同，而且对应的列的数据类型必须一致。,8.5.2交操作（INTERSECT）,执行交操作使用的关键字是INTERSECT。交操作返回的结果集包括了连接查询结果的公共行。交操作中不会出现重复行。其语法格式如下：SELECT语句1INTERSECTSELECT语句2其中语句1和语句2表示的是两个用于查询的SELECT语句。INTERSECT关键字表示对这两个查询语句查询出来的结果进行交操作。这里需要保证SELECT语句1和SELECT语句2中查询出的列数必须相同，而且对应的列的数据类型必须一致。,8.5.2交操作（INTERSECT）,例如，有两个表A和表B，表A中有三个列，分别是A1,A2和A3，表B中也包含三个列，分别是B1,B2和B3。这里假设字段A1和字段B1有相同的数据类型，字段A2和字段B2有相同的数据类型，字段A3和字段B3有相同的数据类型。现在对这两张表执行交操作。,8.5.2交操作（INTERSECT）,对这两张表中的记录进行交操作。这里表A1查询的列为列A1，A2和A3。这表B1查询的列为列B1，B2和B3。使用INTERSECT关键字对查询结果进行交操作。根据交操作的运算规则，执行A交B的运算之后，其执行的结果如下图所示。,8.5.3差操作（MINUS）,执执行交操作使用的关键字是MINUS。差操作返回的记录结果集是只在第一个SELECT语句中出现存在，但不存在于第二个SELECT语句的查询结果中。在执行差操作时，首先会找出第一个SELECT语句中产生的结果集,然后再将这些结果集和第二个SELECT语句中查询的结果集进行比较，如果结果集中的记录存在于第二个SELECT语句中查询的结果集，则将这些记录舍弃，最后的结果集中只会出现在第一个SELECT语句查询出的结果集中有而不在第二个SELECT语句查询的结果集的记录。在其语法格式如下：SELECT语句1MINUSSELECT语句2,8.5.3差操