多表连接查询与子查询

多表连接查询和子查询

一、连接查询

1.1使用无连接规则连接两表

所谓无连接规则连接，就是指两个表的SELECT语句中不设置任何连接条件，这样的到的链接结果是第一个表的每一行都会和第二个表的所有行进行连接，即得到一个笛卡尔积。

SELECT *

FROM STU_INFO,SCORE

1.2 使用有连接规则连接两表

有连接规则连接，其实就是在无连接规则上，加上WHERE子句指定连接规则的连接方法。

SELECT*

FROMSTU_INFO,SCORE

WHERESTU_INFO.SNO=SCORE.SNO

1.3 使用多表连接查询数据

SELECTSTU_INFO.SNO,STU_INFO.SNAME,STU_INFO.DEPART,SCORE.ENGLISH,TEACHER.TNAME

FROM STU_INFO,SCORE,TEACHER

WHERE STU_INFO.SNO=SCORE.SNO

ANDSTU_INFO.SNAME='张三'

1.4 使用INNER JOIN 连接查询

在WHERE子句中设置连接规则，有时会是整个条件表达式变得非常臃肿，而且不容易让人理解。因此ANSI SQL规范中建议使用INNER JOIN进行多表连接。这样一来WHERE 子句中就不在放置连接规则了，而只放置查询条件就可以了。

INNER JOIN 的语法格式如下：

SELECT *

FROM 表名1

INNER JOIN 表名2

ON 连接规则1

INNER JOIN 表名3

ON 连接规则 2

……

INNER JOIN 表名n

ON 连接规则 n

例子：

SELECT ST.SNO,ST.SNAME,S.ENGLISH

FROMSTU_INFO AS ST

INNERJOIN SCORE AS S

ONST.SNO=S.SNO

WHERE ST.SNAME='张三'

二、高级连接查询

2.1自连接查询

在多表连接查询中，比较有意思的查询是自连接查询，即表自身与自身连接。

例子：

SELECT DEPART

FROM STU_INFO

WHERE SNAME=’张三’

2.2内连接查询

前面介绍过的有连接规则的连接都属于内连接。内连接包括等值连接、自然连接和不等值连接三种。内连接最大的特点是只返回两个表中相会匹配的记录。而那些不能匹配的记录就被自动去除了。所以使用内连接时，应考虑到查询结果中可能丢掉了某些数据的问题。

1.等值连接

使用‘=’连接，如：STU_INFO.SNO=SCORE.SNO

2.自然连接

在等值连接的基础上稍加改动即可得到自然连接，等值连接将两个表中所有字段全部显示，而自然连接则不将相同的字段显示两次，即在SELECT 子句中列出需要显示的字段列表

3.不等值连接

不等值连接的连接规则由等于号以外的运算符组成，例如：

>、>=、<、<=、<>等。

2.3左外连接查询

左外连接运算符（LEFTOUTER JOIN）

左外连接将左侧表的所有记录都包含进来，而只将右边匹配的记录包含进来。

例子：

SELECT *

FROM STU_INFO

LEFT OUTER JOIN SCORE

ON STU_INFO.SNO=SCORE.SNO

2.4右外连接

右外连接运算符（RIGHT OUTER JOIN）

右外连接将右侧表的所有记录都包含进来，而只将左边匹配的记录包含进来。

例子：

SELECT *

FROM STU_INFO

RIGHT OUTER JOIN SCORE

ON STU_INFO.SNO=SCORE.SNO

2.5全外连接

这种连接规则是将两个表的所有记录都包含到结果集中。

运算符（FULL OUTER JOIN）

SELECT *

FROM STU_INFO

FULL OUTER JOIN SCORE

ON STU_INFO.SNO=SCORE.SNO

2.6交叉连接查询

交叉连接其实就是前面介绍的无连接规则连接，实际上这种连接规则有两种表示方法。

1.用逗号隔开表名

SELECT *

FROM STU_INFO,SCORE

2.用CROSS JOIN 关键字连接表名

SELECT *

FROM STU_INFO CROSS JOIN SCORE

2.7连接查询中使用聚合函数

例如：

SELECT COUNT(*) AS 没有考试总人数

FROM STU_INFO

LEFT OUTER JOIN SCORE

ON STU_INFO.SNO=SCORE.SNO

WHERE SCORE.SNO IS NULL

三、组合查询

除连接查询外，SQL中还有一种组合查询，这种查询使用UNION关键字将多个SELECT语句组合起来，将多个SELECT语句的查询结果显示到一个结果集中。组合查询与连接查询不同的是，前者将多个表的查询结果，竖着组合，而后者是将查询结果和这连接。

3.1使用组合查询

有时，需要将多个查询结果放到一起，以一个查询结果及的形式显示出来。这时候就可以实用组合查询，组合查询是使用UNION关键字将多个SELECT

查询语句结合起来查询的一种查询方法。

语法格式：

SELECT 语句1

UNION

SELECT 语句2

UNION

SELECT 语句3

……

UNION

SELECT 语句n

例子：

SELECT *

FROM STU_INFO

WHERE DEPART=’外语系’

UNION

SELECT *

FROM STU_INFO

WHERE YEAR>21

3.2使用UNION的规则

1.每个查询语句应该有相同数量的字段

在使用UNION查询语句时，一定要注意每个单独的SELECT查询子句内的字段个数一定要相同，如果不同则会出错。

3.每个查询语句中相应的字段的类型必须相互兼容

在每个查询语句字段个数相同的情况下，相应的字段的类型应当相互兼容，否则会出错。

3.3使用UNION得到复杂的统计总样式

到目前为止，UNION好像是多余的，因为他可能被OR代替，其实不然。联合UNION、GROUP BY和聚合函数三者会得到具有很棒的统计汇总样式的查询结果，这就是OR所不能替代的一个例子。

例子：

SELECT ENGLISH

FROM SCORE

UNION

SELECT SUM(ENGLISH)

四、子查询

嵌入到另一个SELECT语句中的SELECT语句被称为子查询。

4.1使用返回单值的子查询

如果子查询返回单值，则可以使用关系运算符，例如，等于（=）、

不等于（<>）等，将其与主查询结合起来。

实例：

SELECT *

FROM STU_INFO

WHERE SNO=(SELECT SNO

FROMSCORE

WHEREENGLISH=78)

4.2子查询与聚合函数的配合使用

例子：

SELECT *

FROM STU_INFO

WHERE YEAR=(SELECT MIN(YEAR)

FROMSTU_INFO)