SQL(五)查询(三)连接查询、集合操作、嵌套子查询

一 连接查询：

(一)概述：

1.笛卡尔积：select * from A,B；A中的每个元组和B中的所有元组都要组合，其结果是非常大的关系，通常这样的笛卡尔积是无意义的。

2.where子句:限定笛卡尔积建立的组合。

*格式：select * from A,B where A.xx=B.xx。

*缺点：相同属性xx在结果中出现两次。

*解决方法：用自然连接或在select后指定要显示的列。

3.另一种形式的连接：select * from A join B on A.xx=B.xx。缺点：相同属性xx在结果出现两次。

(二)自然连接(natural join)：作用于两个关系，并产生一个关系作为结果。

1.自然连接：只考虑那些在两个关联模式中都出现的属性上取值相同的元素对(如where子句限定)；同时两个关系中都出现的属性只显示一次。

2.格式：

*select * from A nature join B；

*select * from A join B using(列名)：避免不必要的相等属性带来的危险，允许用户指定需要哪些列相等。

(三)外连接：通过在结果中创建包含控制元组的方式，保留了那些在连接中丢失的元组。

1.外连接的三种形式(mysql不支持全外连接)：

*左外连接(left outer join)：只保留出现在左外连接运算左边的关系中的元组。

*右外连接(right outer join)：只保留出现在左外连接运算右边的关系中的元组。

*全外连接(full outer join)：只保留出现在左外连接运算左边的关系中的元组。

2.常规连接称为：内连接。

(四)自身连接：需要使用别名加以区分。

(五)总结：连接类型和连接条件.

1.连接类型：

*inner join（默认，与join等价）

*left outer join

*right outer join

*full outer join

2.连接条件：

*natural：自然连接。

*on <谓语>

*using <列名...>：自然连接的另一种形式。

3.任意的连接类型可以和任意的连接条件进行组合。

二 集合操作：

(1)SQL上的集合运算union(并)，intersect(交)，except(差)。

(2)并运算(union)：连接两条select语句，并把输出组合成单个查询结果集。

*格式：select * from A where...union select * from A where...

*union必须由两条或两条以上的select语句组成，语句之间用关键字union分隔。

*union中的每个查询必须包含相同的列、表达式或聚集函数。

*列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐式转换的类型。

*union自动取消重复的行。这是默认行为，使用union all可以不去除重复的行。

(2)intersect和except(mysql暂不支持)。

三 嵌套子查询：

(1)概述：

*在SQL中，一个select-from-where语句称为一个查询块。子查询：嵌套在另一个select-from-where查询块中的查询块。

(2)带有IN谓词(或NOT IN)的子查询：

*格式：select * from lyr where name in(select name from test);

(3)带有比较运算符的子查询：适用于当用户知道内层查询块返回的是单值时。

*>,<,=,>=,<=,!=或<>等比较运算符。

(4)带有ANY(SOME)或ALL谓词的子查询：适用于子查询返回多值进行比较时。

*使用ANY或ALL谓词必须同时使用比较运算符。

(5)带有EXISTS谓词的子查询：

*exists：测试一个子查询的结果中是否包含元组。

*not exists：测试子查询结果中是否不存在元组。

*格式：select * from lyr where not exists(子查询)