MS sql server 基础知识回顾（二）-表连接和子查询

 

五、表连接

  当数据表中存在许多重复的冗余信息时，就要考虑将这些信息建在另一张新表中，在新表中为原表设置好外键，在进行数据查询的时候，就要使用到连接了，表连接就好像两根线，线的两端分别连接两张表的不同字段上，将两个字段进行关联。表连接分为内连接（inner join）、交叉连接、自连接和外连接等。

1、内连接，语法  inner join table_name on condition ，table_name被关联的表名，condition关联条件。

      例如：有两张表，T_Customer保存着客户的Id，姓名，年龄等信息，T_Order表保存着订单号及订单价格等信息，T_Order中FCustomerId是T_Customer中的FId的外键，下面查询客户姓名为Mike的客户的订单号及订单价格。

select c.FName,o.FNumber,o.FPrice
from T_Order  o
inner join T_Customer c on o.FCustomerId=c.FId
where c.FName='Mike'

2、交叉连接，与内连接相比，交叉连接非常简单，不需要on子句，交叉连接将表中的所有记录都包含到结果集中，交叉连接分为隐式的连接和显示的cross join 连接，其中隐式的交叉连接，所有数据库都支持，cross join显示的交叉连接DB2数据库不支持。例如：查询上两张表中客户的Id，姓名，年龄以及订单信息。

--  隐式的交叉连接
select  T_Customer.Id,T_Customer.FName,T_Customer.FAge,TOrder.FName,TOrder.FNumber
from T_Customer ,TOrder
--  显示的交叉连接
select  T_Customer.Id,T_Customer.FName,T_Customer.FAge,TOrder.FName,TOrder.FNumber
from T_Customer  across join TOrder

3、自连接，其主要用途就是检索一张表中的内部情况，例如检索表T_Order中订单类型相同的订单的列表。

select o1.FNumber ,o1.FName,o1.FType, o2.FNumber ,o2.FName,o2.FType
from T_Order o1
inner join T_Order o2 on o1.FType=o2.FType and o1.FId <o2.FId

（至于为什么要加上and后面的约束及为什么不是 o1.FId <>o2.FId ，感兴趣的朋友我们可以自己试一试，因为存在着信息重复的问题，我们也可以继续交流）

4、外连接，内连接要求连接的两张表必须有匹配的记录，但是如果说T_Order表中的FCustomerId一列有null值，那么内连接是无法将这条信息查询出来的，但是我们是需要这条数据的，这样就需要外部连接了，外连接包括左外连接，右外连接和全外连接，全外连接在数据库MySql中是不支持的，所以，可以使用union all 联合两个查询语句，即左外连接查询和右外连接查询。

左外连接就是将左表中的所有数据，无论是否满足on后面的条件语句，都匹配出来。右外连接就是将右表中的所有数据，无论是否满足on后面的条件语句，都匹配出来。而全外连接，就是将两张表中的所有数据，无论是否满足on后面的条件语句，都匹配出来。

-- 左外连接,取出左表T_Customer中的所有数据，无论是否匹配 T_Customer.FId =T_Order.FCustomerId
select  c.FNumber ,C.FName,o.FNumber ,o.FPrice
from T_Customer c
left outer join T_Order o on T_Customer.FId =T_Order.FCustomerId
-- 右外连接,取出右表T_Order中的所有数据，无论是否匹配 T_Customer.FId =T_Order.FCustomerId
select  c.FNumber ,C.FName,o.FNumber ,o.FPrice
from T_Customer c
right  outer join T_Order o on T_Customer.FId =T_Order.FCustomerId
-- 全外连接,取出两张表中的所有数据，无论是否匹配 T_Customer.FId =T_Order.FCustomerId
select  c.FNumber ,C.FName,o.FNumber ,o.FPrice
from T_Customer c
full outer join T_Order o on T_Customer.FId =T_Order.FCustomerId

六、子查询

sql允许将一个查询语句作为结果集提供给其它sql语句使用，被当做结果集的查询语句就是子查询，可以将子查询看做是一张临时生成的数据表（此时的搜索结果必须是多行多列），这张数据表在开始时被创建，在查询结束时被删除。子查询可以用在select语句，insert语句以及update语句。子查询大大简化了sql语句的编写，提高了效率，但是如果使用不当，容易造成性能问题。

1、单值子查询

单值子查询和普通的sql语句没什么区别，唯一的限制就是子查询的返回值必须只有一行记录，而且只能有一个列，这样的子查询也称作标量子查询。

select 1 as f1 ,2 ,(select MIN (FYearOfPublish)from T_Book ) as N'最早出版年份',
                     (select MAX (FYearOfPublish) from T_Book )as N'最晚出版年份'

2、列值子查询

与标量子查询不同，列值子查询可以返回一个多行多列的结果集，又称为表子查询，可以看做一个临时的表。

--查询出版日期在1800年以前的书的名字及作者的名字，当然这个地方用到了交叉连接的隐式实现
select r.FName ,b.FName ,b.FYearOfPublish 
from T_Reader r,(select FName ,FYearOfPublish  from T_Book where FYearOfPublish <1800) b

3、select语句中的标量子查询

标量子查询唯一的限制就是子查询的返回值必须只有一行记录，而且只能有一个列，其实标量子查询完全可以返回随当前记录变化而变化的值。比如：

--返回每一类图书的最新出版年份，图书类别
select FId ,FName,
(
    select  MAX (FYearOfPublish)from T_Book b 
    where b.FCategoryId =c.FId  

)as N'最新出版年份' 
 from T_Categroy  c

4、where语句中的标量子查询

标量子查询不仅可以用在select语句中，而且可以用在where语句中，比如：

--返回最喜欢读的书籍类别为Story的读者的ID信息
select FReaderId  from T_ReaderFavorite 
     where FCategoryId =
     (
       select FId from T_Categroy
       where FName ='Story'
     )

   再看一个综合点的例子：假设需要检索每一种书籍类别中出版年份最早的书籍的名称，如果有两本或两本以上的，则都显示他们的名字。要求检索结果中显示类型的名字、书的名字和它的出版年份。

select T_Categroy .FName ,T_Book .FName ,T_Book .FYearOfPublish
from T_Book inner join T_Categroy on T_Book .FCategoryId =T_Categroy .FId
where T_Book .FYearOfPublish =
(
    select MIN (T_Book .FYearOfPublish) from T_Book
    where T_Book .FCategoryId =T_Categroy .FId
)

当然上面的语句中也可以使用别名代换，这样会更简明些。

5、集合运算符与子查询，如果子查询的结果集是多行多列的，那么可以将该子查询看做是一个临时的数据表，但是当结果集是一个多行单列的集合时，就需要使用in，any，all及exists等来查询。

in操作符，是用来匹配多行单列的，可以简单的匹配一个集合中的某一项，比如检索在2001,2003,2005年出版的所有图书

select * from T_Book where FYearofPublish in (2001,2003,2005)

select * from T_Reader where FYearofJoin in (select FYearofPublis from T_Book)

any必须和其他的比较运算符（=，<,>,!=）共同使用，不严谨的说你可以这样认为，any就是任何一个，匹配任何一个的意思，比如

select * from T_Reader where FYearOfJoin =any (  select FYearOfPublish  from T_Book )

select * from T_Reader where FYearOfJoin <>any (  select FYearOfPublish  from T_Book )

这两条语句前一条=any其效果适合in一样的，<>any 效果适合not in 一样的，除了=运算符，any还可以和大于小于大于等于等运算符共同使用，例如：

-- 查询在所有会员出生之前出版的书
select * from T_Book where FYearOfPublish <any( select FYearOfBith  from T_Reader)

exists，是否存在比较运算符，其后面的语句如果为真则返回true，否则返回false。先看一个例子：

 

在这个事例中，如果T_Reader表中存在姓名为Tom的读者信息，那么将返回T_Book表中的信息，反之，不会检索出任何信息。也就是，如果存在，则返回，不存在，则查不到任何信息，看似好像exists的作用并不是很大，但是它往往用在子查询中引用外部字段的检索中，看实例：

-- 检索1950年以前出版的图书的图书类别
select  * from T_Category where exists (select * from T_Book where T_Book.FCategoryId =T_Category.Id and T_Book .FYearofPublis <1950 )

这样就会检索出在1950年以前出版的图书的信息了。

七、子查询在insert语句update语句中的应用

insert语句：一般的，向一个数据表中添加一条数据就是应用insert into 表名（字段1，字段2……）values （值1，值2，……） 比如

insert into T_Person(FId ,FName,FAge) values (1,'Tom',20)

但其实insert还有另外一种用法，就是insert ……select……语句，具体语法为insert into 表名（字段1，字段2……）select （值1，值2，……） from 表

insert into T_Reader2(FId,FName,FAge) select (FId,FName,FAge) from T_Reader

这条语句其实就相当于复制了一个T_Reader表，将其信息插入到了T_Reader2 中。当然，insert……select 还有更复杂的用法，可以向不同结构的表中插入数据，例如：为每个读者的爱好增加“小说”，也就是为每一个读者在其T_ReaderFavorite中添加一条FCategory=1的记录（如果原先读者已经有了此爱好，就不用添加，如果没有此爱好，就添加）。

insert into T_ReaderFavorite(FCategoryId ,FReaderId ) select 1,FId from T_Reader
where not exists
(
  select * from T_ReaderFavorite  where T_ReaderFavorite .FCategoryId =1 and  T_ReaderFavorite .FReaderId =T_Reader .FId
)

update语句：在update语句中子查询也可以有比较复杂的检索，例如：将所有同类图书的本数超过3本的图书的出版日期更新为2008（自连接）

update T_Book set FYearOfPublish =2008 where (select COUNT (*) from T_Book bk2 where bk2 .FCategoryId =T_Book .FCategoryId )>3

 

 

delete语句：例如：将所有同类图书的本数超过3本的图书删除

delete from T_Book where (select count(*) from T_Book bk where bk.FCategoryId=T_Book.FCategoryId)>3