MSSQL之五 连接查询与子查询
|
在一个高级的数据库中，被浏览的数据可以被存储在多个表中。当你需要从相关的表中浏览数据的时候，你可以通过将公用的属性连接表查询数据。你可以使用子查询，这里一个查询的结果被用作另一个查询的条件的输入。 本章讨论如何通过应用各种类型的连接，例如内连接，外连接，交叉连接，等值连接或自连接，来从夺标中查询数据。进一步，它解释如何使用子查询
Ø 使用连接查询数据 Ø 使用子查询查询数据
Ø 连接查询的几种方式 Ø 子查询的使用方法
|
|
数据表之间的联系是通过表的字段值来体现的，这种字段称为连接字段。连接操作的目的就是通过加在连接字段的条件将多个表连接起来，以便从多个表中查询数据。前面的查询都是针对一个表进行的，当查询同时涉及两个以上的表时，称为连接查询。连接查询主要分为如下几种格式：
1. 内连接
2. 外连接
3. 交叉连接
4. 自身连接
5. 等值连接与非等值连接
Ø 内连接
内连接在公共的列上使用比较操作符从多表中抽取数据。当内连接被使用的时候，仅满足公共列中的连接条件的值的列被显示。两个表中不满足连接条件的行不显示。
语法：
select 表名.列名[….., 表名.列名] from 表名1 join 表名2 on表名1.ref_表名2.列名 比较操作符 表名1.ref_表名2.列名
例如[5-1]
Select studentInfo.SName, studentInfo.SSex,grade.gradefrom studentInfo [inner] join grade on studentInfo.SID=grade.SID
内连接输出结果：
Ø 外连接
外连接显示包含来自一个表中所有行和来自另一个表中匹配行的结果集。
外连接有三种类型：
1.左连接
2.右连接
3.完全连接
左连接返回 left outer join 关键字左侧指定表的所有行和右侧指定的表的匹配的行。对于来自左侧的表中的行，在右侧指定的表中没有发现匹配的行，那么在来自右侧指定的表中获得数据的列中将显示null值。右连接也是如此。
完全连接是左连接和右连接的组合。这个连接返回来自两个表的所有匹配和非匹配行。但是，匹配记录仅被显示一次。在非匹配行的情况下，对于数据不可用的列将显示null值。
语法：
select 表名.列名, 表名.列名[,…. 表名.列名] from表名1[ left |right |full ] out join表名2 on表名1.引用列名 连接操作符 表名2.引用列名
例如： selectkecheng.cmame AS 课程,grade.grade as 成绩from kecheng left outer join grade on grade.CID=kecheng.CID
外键连接输出：Ø 交叉连接
交叉连接，也被称为笛卡尔积，在两个表中将一个表中的每一行与另一个表中的每一行连接，结果集中行的数量是第一个表中行的数量与第二个表中行的数量的的乘积。这表示如果表A中有10行，表B中有5行，那么表A中的10行与表B中的5行连接。结果集将包含50行。
语法： selectA.列名,B.列名 from Across join B
例如:在查询窗口写入：
selectW.tid,W.tname,P.CID,P.cmame as '课程名',P.cperiod
FROM TEACHERS W cross joinkecheng P
Ø 自身连接
当一个表与其自已进行连接操作时，称为表的自身连接。要查询的内容是在一个表中的一行与同一个表中的另一行，为了区别同一表的两个实例可以将表分别取两个别名，一个是X，一个是Y。将X， Y中满足查询条件的行连接起来。这实际上是同一表的自身连接。
语法：select X.列名，X.列名[as 别名],Y.列名，Y.列名[as 别名] from 表名 X，表名 Y[where x.列名=y.列名]
注释：“[]“内的内容是可选的
例如：查询studentInfo 表中学生的ID、姓名、QQ、电话
select x.SID,x.SName as 姓名,y.SQQ,y.SPhone as 电话from studentxinxi x,studentxinxi y
Ø 等值连接与非等值连接
语法：
[<表名1>.] <列名1><比较运算符> [<表名2>.]<列名2>
其中，比较运算符主要有：＝、＞、＜、＞＝、＜＝、！＝。当比较运算符为“＝”时，称为等值连接，其他情况为非等值连接。
注释：等值连接和外连接一样，使用外键连接表。但是，被用于显示两个或多个表的所有列。所有连接的表的公共列被显示出来。
例如：
select *from studentInfo a join grade b on a.SID=b.SID join kecheng k on k.CID=b.CID在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。
重点：
1. 返回一个值的子查询
2. 返回一个组的子查询
Ø 返回一个值的子查询
当子查询的返回值只有一个值时，可以使用比较运算符（=, >, <, >=, <=, !=）将父查询和子查询连接起来。
例如：在查询窗口写入以下代码：
select SID,SName,SAddress from studentxinxi where
SID=(selectSID from studentxinxi where SName='李连杰')
Ø 返回一个组的子查询
如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY或ALL。
1. 使用IN
2. 使用EXISTS
3. 使用ALL
4. 使用ANY
使用IN
语法：
select 列名[….列名] from 表名 where 列名 [not] in
(select 列名 from 表名 [where 条件表达式])
说明：可以使用IN代替“=ANY”。
例如在查询窗口写入以下代码：
select CID,grade from grade where SID in
(select SID from studentxinxi where SID='niit0806')
输出结果如下：使用EXISTS
EXISTS表示存在量词，带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值“真”或“假”。当子查询的的查询结果集合为非空时，外层的WHERE子句返回真值，否则返回假值。
语法：
select 列名[….列名] from 表名 whereexists
(select 列名 from 表名 [where 条件表达式])
说明：可以使用IN代替“=ANY”。
在查询窗口写入以下代码：
selectCID,grade from grade where exists
(select SID from studentxinxi where SID='niit0806')下表显示了使用ALL和ANY关键字的操作符：
|
操作符 |
描述 |
|
>all |
表示大于列表中的最大值 表达式| 列名>all(10,20,30)表示“大于30” |
|
>any |
表示大于列表中的最小值 表达式 列名>any(10,20,30)表示“大于10” |
|
=any |
表示列表中的任何值，它与 in 从句的作用一样。 表达式 列名=any(10,20,30)表示“等于10或20或30” |
|
<>any |
表示不等于任何列表中的值。 表达式 列名<>any(10,20,30)表示“不等于10或20或30“ |
|
<>all |
表示不等于列表中的所有值，它与 not in从句的作用一样 表达式 列名<>all(10,20,30)表示“不等于10和20和30“ |
在查询窗口写入以下代码：
select SID,SAddress from studentxinxi where
SID>all( select SID from grade where grade=98)
|
1、什么时候使用子查询？
2、写出一个查询以显示employee ID和员工支付率大于40的植物。
3、你需要从两个表中抽取所有的列。你将使用哪种类型的连接？
A、交叉连接
B、等值连接
C、自连接
D、右连接
|
1 连接和子查询被用于从多表中抽取数据。
2. 内连接在公共列上使用比较操作符从多表中组合记录。
3. 左外连接返回来自左表的所有行和来自右表的匹配行。
4. 右外连接返回来自右表的所有行和来自左表的匹配行。
5. 完全外连接返回所有来自第一个表的每行和来自第二个表的每行的连接。
6. 等值连接被用于显示连接的表的所有列。
7. 自连接将一行与同一表中的其他行相关。
8. 在IN子查询的从句中返回0或更多值。
9. 在EXISTS子查询的从句中返回true 或 false值。
10. ALL和ANY 关键字被用于在子查询中修改已有的比较操作符。
11. 集合函数也被用于在子查询中以产生来自内部查询的集合值。
12. 包含一个或更多查询的子查询被称为嵌入子查询。
13. 在相关查询被定义为依赖与外部查询进行求值的查询。