第20章SELECT高级查询.docx

第20章SELECT高级查询.docx

《第20章SELECT高级查询.docx》由会员分享，可在线阅读，更多相关《第20章SELECT高级查询.docx（12页珍藏版）》请在冰豆网上搜索。

第20章SELECT高级查询

第20章SELECT高级查询

在实际查询应用中，用户所需要的数据并不全部都在一个表或视图中，而在多个表中，这时就要使用多表查询。

多表查询用多个表中的数据来组合，再从中获取出所需要的数据信息。

多表查询实际上是通过各个表之间的共同列的相关性来查询数据的，是数据库查询最主要的特征。

多表查询首先要在这些表中建立连接，表之间的连接就是连接查询的结果集或结果表。

而实现连接的结果是在向数据库添加新类型的数据方面没有限制的，具有很大的灵活性。

通常总是通过连接创建一个新表，以包含不同表中的数据。

如果新表有合适的域，就可以将他连接到现有的表中。

20.1基本连接

在进行多表操作时，最简单的连接方式就是在SELECT语句列表中引用多个表的字段，其FROM子句中用半角逗号将不同的基本表隔开。

如果使用WHERE子句创建一个同等连接则能使查询结果集更加丰富，同等连接是指第一个基表中的一个或多个列值与第二个基表中对应的一个或多个列值相等的连接。

通常情况下，一般使用键码列建立连接，即一个基表中的主键码与第二个基表中的外键码保持一致，以保持整个数据库的参照完整性。

用户在进行基本连接操作时，可以遵循以下基本原则：

●SELECT子句列表中，每个目标列前都要加上基表名称

●FROM子句应包括所有使用的基表

●WHERE子句应定义一个同等连接

多表查询中同样可以使用WHERE子句的各个搜索条件，比如比较运算符、逻辑运算符、IN条件、BETWEEN条件、LIKE条件及ISNULL条件等，也可以规范化结果集。

例如“网店购物系统”数据库中“商品信息”表和“订单信息”表，都包含了“商品编号”列，根据该列在WHERE子句中建立同等连接，查询语句如下所示。

USE网店购物系统

SELECT商品信息.商品名称,商品信息.商品价格,订单信息.交易数量,订单信息.交易时间

FROM商品信息,订单信息

WHERE商品信息.商品编号=订单信息.商品编号

上面语句在SELECT语句中各个查询列前都指定了其所在的表，从而确定每个列的来源并限定列名。

WHERE子句中创建了一个同等连接，即通过两表的商品编号，它们都是所在表的主键。

执行语句后，得到的结果如图5-22所示。

图5-22使用WHERE子句建立同等连接

使用同等连接不仅仅可以连接两个列，如果表中结构允许，用户可以在WHERE子句中连接多个表，可以使用下面这样的语句：

USE网店购物系统

SELECT商品信息.商品名称,商品信息.商品价格,订单信息.交易数量,注册会员.会用地址

FROM商品信息,订单信息,注册会员

WHERE商品信息.商品编号=订单信息.商品编号

AND订单信息.会员编号=注册会员.会员编号

AND注册会员.会员地址LIKE'北京%'

上面语句在WHERE子句中使用AND连接了两个同等连接：

第一个连接是“商品信息”表中商品编号与“订单信息”表中商品编号，第二个连接是“订单信息”表中会员编号与“注册会员”表中的会员编号；最后又使用AND连接了另外一个限定条件。

执行上面语句后，得到的结果如图5-23所示。

图5-23使用多个同等连接

20.2JOIN关键字

使用JOIN关键字可以进行连接查询，它和基本连接查询一样都是用来连接多个表的操作。

使用JOIN关键字可以引导出多种连接方式，如内连接、外连接、交叉连接、自连接等。

其连接条件主要通过以下方法定义两个表在查询中的关系方式：

●指定每个表中要用于连接的目标列。

即在一个基表中指定外键，在另外一个基表中指定与其关联的键。

●指定比较各目标列的值要使用比较运算符，如=、<等。

使用JOIN关键字连接查询的语法格式为：

SELECTselect_list

FROMtablejoin_typeJOINtable2[ONjoin_conditions]

[WHEREsearch_conditions]

[ORDERBYorder_expression]

Table1与table2为基表；join_type指定连接类型，正是该连接类型指定了多种连接方式如内连接、外连接、交叉连接和自连接；join_conditions指定连接条件。

例如在“网店购物系统”数据库中“商品信息”和“订单信息”两个表中都包含“商品编号”列，用户可以使用JOIN关键字将两个表建立连接，可以使用下面的语句：

USE网店购物系统

SELECT商品信息.商品名称,商品信息.商品价格,订单信息.交易数量

FROM商品信息JOIN订单信息

ON商品信息.商品编号=订单信息.商品编号

WHERE商品信息.商品价格<350

在上面的语句中，首先在FROM子句中使用JOIN关键字将两个表建立连接，然后通过ON子句给出了连接的条件，最后，使用WHERE子句又给出了连接的限定条件。

执行上面语句后，得到如图5-24所示的结果。

图5-24使用JOIN建立连接

20.3内连接

内连接是比较常用的一种数据连接查询方式。

它使用比较运算符进行多个基表间数据的比较操作，并列出这些基表中与连接条件相匹配的所有的数据行。

一般用INNERJOIN或JOIN关键字来指定内连接，它是连接查询默认的连接方式。

内连接的语法格式为：

SELECTselect_list

FROMtableINNERJOINtable2[ONjoin_conditions]

[WHEREsearch_conditions]

[ORDERBYorder_expression]

细分起来，又可将内连接分为等值连接、非等值连接和自然连接三种。

1．等值连接

等值连接就是在连接条件中使用等于号（=）比较运算符来比较连接列的列值，其查询结果中列出被连接表中的所有列，并且包括重复列。

下面的语句对“商品信息”表和“订单信息”表进行了内部连接：

USE购物系统

SELECT商品信息.商品名称,商品信息.商品价格,订单信息.订单数量

FROM商品信息JOIN订单信息

ON商品信息.商品编号=订单信息.商品编号

ORDERBY商品信息.商品价格

上面的语句使用了ON子句指定连接条件，并使用了ORDERBY子句通过“商品信息”表中的商品价格进行升序排列。

执行该语句，得到的结果如图5-25所示。

图5-25内连接等值连接

2．非等值连接

非等值连接查询就是在连接条件中使用了除等于号之外的比较运算符以比较连接列的列值，比较连接符有>、<、>=、<=、<>，除了这比较运算符外，也可以使用范围运算符如BETWEEN。

例如在“商品信息”表和“订单信息”表中查询商品名称、商品价格、交易数量等，同时输出商品价格在250～350元之间的商品信息，可以使用下面的语句：

USE网店购物系统

SELECT商品信息.商品名称,商品信息.商品价格,订单信息.交易数量

FROM商品信息INNERJOIN订单信息

ON商品信息.商品编号=订单信息.商品编号

AND订单信息.商品价格BETWEEN250AND350

ORDERBY商品信息.商品价格

上面的语句中，除了通过ON子句指定了连接的条件外，并给出了BETWEEN非等值连接运算，执行该语句后，得到的结果如图5-26所示。

图5-26内连接非等值连接

3．自然连接

自然连接与等值连接相同，都是在连接条件中使用等于比较运算符，使用自然连接查询时，它会为具有相同名称的列自动进行记录匹配，并在返回的数据中删除连接表中的重复列。

例如，基于表“商品信息”和表“订单信息”创建一个自然连接查询，限定条件为两个基表中的商品编号，在结果集中显示商品名称、商品价格和交易数量。

可以使用下面的语句：

USE网店购物系统

SELECT商品信息.商品名称,商品信息.商品价格,订单信息.交易数量

FROM商品信息INNERJOIN订单信息

ON商品信息.商品编号=订单信息.商品编号

执行上面的语句后，得到的结果如图5-27所示。

图5-27内连接自然连接

20.4外连接

由于内连接可能产生信息的丢失，为避免这种情况的发生，用户可以使用外连接。

外连接与内连接不同，在查询时所用的基表有主从表之分。

使用外连接时，以主表中每行数据去匹配从表中的数据行，如果符合连接条件则返回到结果集中；如果没有找到匹配行，则主表的行仍然保留，并且返回到结果集中，相应的从表中的数据行被填上NULL值后也返回到结果集中。

根据返回行的主从表形式不同，外连接可以分为三种类型：

左外连接、右外连接和全外连接。

各种外连接方式有独特的关键字，下面对这三种连接方式进行详细介绍。

1．左外连接

左外连接是指返回所有的匹配行并从关键字JOIN左边的表中返回所有不匹配行。

由此可知，即使不匹配，JOIN关键字左边的表中数据也将被保留，所以在左外连接中JOIN关键字左边的表为主表，右边的表为从表。

使用左边连接的一般语法结构为：

SELECTselect_list

FROMtableLEFTOUTERJOINtable2[ONjoin_conditions]

[WHEREsearch_conditions]

[ORDERBYorder_expression]

上面语句结构中OUTERJOIN表示外连接，而LEFT表示左外连接的关键字，因此table1为主表，table2为从表。

例如，在“订单信息”表中一个商品编号，对应一个具体的商品；而在“商品信息”表中保存了数据库中所有的商品，相对应“商品信息”表中所有商品并不是都要被购买，因此可以使用这两个表作为左外连接，可以使用下面语句：

USE网店购物系统

SELECT商品信息.商品名称,商品信息.商品价格,订单信息.交易数量

FROM商品信息LEFTOUTERJOIN订单信息

ON商品信息.商品编号=订单信息.商品编号

上面语句返回结果显示商品名称、商品价格、交易数量，主表为商品信息，从表为订单信息，执行语句后，结果如图5-28所示。

图5-28使用左外连接

从上图可以看到，由于在“订单信息”表存在不匹配的行，因此不匹配结果的交易数量为NULL值。

2．右外连接

与左外连接相反，右外连接返回所有的匹配行并从关键字JOIN右边的表中返回所有不匹配的行。

因此，在右外连接中JOIN关键字右边的为主表，而关键字左边的为从表，右外连接返回结果与左外连接相同，即不满足匹配的结果集在相应列中添加NULL值。

使用右外连接的语句结构为：

SELECTselect_list

FROMtable1RIGHTOUTERJOINtable2[ONjoin_conditions]

[WHEREsearch_conditions]

[ORDERBYorder_expression]

OUTERJOIN表示外连接，RIGHT为右外连接的关键字。

例如，同样在“商品信息”表和“订单信息”表中使用右外连接，返回商品名称、商品价格、交易数量、交易时间，可以使用下面的语句：

USE网店购物系统

SELECT商品信息.商品名称,商品信息.商品价格,订单信息.交易数量,订单信息.交易时间

FROM订单信息RIGHTOUTERJOIN商品信息

ON商品信息.商品编号=订单信息.商品编号

语句中“商品信息”为主表，而“订单信息”为从表，返回结果中不满足匹配条件的，即没有被订购的商品，会在交易数量和交易时间列中显示NULL值。

如图5-29所示。

图5-29使用右外连接

3．全外连接

全外连接又称完全外连接，该连接查询方式返回连接表中所有行的数据。

与左外连接相同，JOIN关键字左边为主表，而JOIN关键字右边为从表。

根据匹配条件，如果满足匹配条件时，则返回数据；如果不满足匹配条件时，同样返回数据只不过在相应列中填入NULL值。

在整个全外连接返回结果中，包含了两个完全连接表的所有数据。

使用全外连接一般语法结构为：

SELECTselect_list

FROMtable1FULLOUTERJOINtable2[ONjoin_conditions]

[WHEREsearch_conditions]

[ORDERBYorder_expression]

例如在“商品信息”表和“订单信息“表中使用全外连接，可以使用下面语句：

USE网店购物系统

SELECT商品信息.商品名称,商品信息.商品价格,订单信息.交易数量,订单信息.交易时间

FROM订单信息FULLOUTERJOIN商品信息

ON商品信息.商品编号=订单信息.商品编号

从上面语句中可以看到，“订单信息”为主表，而“商品信息”为从表，返回结果一定是按照“订单信息”表中的数据进行匹配，返回结果如图5-30所示。

图5-30使用全外连接

从返回结果中可以看出，按照“订单信息”表进行匹配，前面11行显示了相应商品信息，而最后的两行不匹配“订单信息”表中的行，因此在交易数量、交易时间列填入NULL值。

20.5交叉连接

使用交叉查询，如果不带WHERE子句时，则返回的结果是被连接的两个表的笛卡尔积；如果交叉连接带有WHERE子句时，则返回结果为连接两个表的笛卡尔积减去WHERE子句所限定而省略的行数。

交叉连接的语法格式为：

SELECTselect_list

FROMtable1CROSSJOINtable2

[WHEREsearch_conditions]

[ORDERBYorder_expression]

CROSS为交叉连接的关键字。

例如在“商品信息”表和“订单信息”表中使用交叉连接，可以使用如下语句：

USE网店购物系统

SELECT商品信息.商品名称,商品信息.商品价格,订单信息.交易数量,订单信息.交易时间

FROM订单信息CROSSJOIN商品信息

WHERE商品信息.商品编号=订单信息.商品编号

AND订单信息.商品价格BETWEEN200AND300

ORDERBY订单信息.交易时间

执行上面的语句，可以得到的结果如图5-31所示。

从交叉连接的语句及其返回结果中可以看出，实际上交叉连接和使用逗号的基本连接操作非常相似，唯一不同之处在于交叉连接使用CROSSJOIN关键字，而基本连接使用逗号操作符。

图5-31使用交叉连接

20.6自连接

前面介绍了多种方式实现两个或多个表之间连接查询，但对同一个表同样也可以进行连接查询，这种连接查询方式就称为自连接。

对一个表使用自连接方式时，需要为该表定义一个别名，其他内容与两个表的连接操作完全相似，只是在每次列出这个表时便为它命名一个别名。

在自连接中可以使用内连接或外连接等连接方式，例如对“商品信息”表实现自连接，并在连接时使用内连接，可以使用下面语句：

USE网店购物系统

SELECTA.商品名称,B.商品编号,A.商品价格,B.库存数量

FROM商品信息ASAINNERJOIN商品信息ASB

ONA.商品编号=B.商品编号

WHEREA.商品价格BETWEEN200AND300

ORDERBYA.商品价格

可以看到，在上面的语句中为该表创建了两个别名A和B，接下来将A和B作为两个不同的表进行查询，执行语句后，得到的结果如图5-32所示。

图5-32在自连接中使用内连接

20.7联合查询

对于不同的查询操作会生成不同的查询结果集，但在实际应用中会希望这些查询结果集连接到一起，从而组成符合实际需要的数据，此时就可以使用联合查询。

使用联合查询可以将两个或更多的结果集组合到一个结果集中，新结果集则包含了所有查询结果集中的全部数据。

使用联合查询的一般语法结构为：

SELECTsearch_list

FROMtable_source

[WHEREsearch_conditions]

{UNION[ALL]

SELECTselect_list

FROMtable_source

[WHEREsearch_conditions]}

[ORDERBYorder_expression]

大括号中联合查询通过UNION子句实现，其中ALL关键字为可选的：

如果在UNION子句中使用该关键字，则返回全部满足匹配的结果；如果不使该关键字，则返回结果中删除满足匹配的重复行。

在进行联合查询时，查询结果的列标题为第一个查询语句的列标题。

因此，必须在第一个查询语句中定义列标题。

例如在“制造商信息”表和“供货商信息”表中，创建联合查询。

可以使用下面的语句：

USE网店购物系统

SELECT制造商名称AS'制造商及地址',制作商电话AS'传真及电话'

FROM制造商信息

UNION

SELECT供货商地址,供货商电话

FROM供应商信息

上面语句中，在第一个SELECT语句中定义了返回结果新列名，分别是“制造商及地址”和“传真及电话”，使用UNION子句连接两个查，执行语句后，得到的结果如图5-33所示。

图5-33使用联合查询

在所有联合查询时，一定要注意以下几点：

●所有UNION查询必须在SELECT列表中有相同的列数。

即如果第一个SELECT语句有3列，那么第二个SELECT语句中也必须有3列。

●UNION返回结果集的列名仅从第一个查询获得。

如果第一个SELECT语句中定义了列名，那么不管其他SELECT语句中是否为列定义了别名，UNION子句返回的结果集都是在第一个SELECT语句中定义的内容。