SQL查询 - 表连接

一、连接查询简介

  连接查询中用来连接连个表的条件称为连接条件或连接谓词。其形式为:

[<表1>].<列名1><连接运算符>[<表2>].<列2>  

  常见的连接运算符包括

  1、比较运算符:=、>、<、>=、<=、!=、between和and。

  2、逻辑运算符:not、and、or。

  3、使用between和and连接查询形式为[<表1>].<列名1><between>[<表2>].<列名2>and[<表2>].<列名3>。

二、连接按照结果集分类

  1、内连接:表中的行互相连接。结果集的行数等于每个表满足条件的行数的乘积,参与连接的表示平等的。

  2、外连接:参与连接的表有主次之分,主表的每一行数据去匹配从表的数据列,符合连接条件的数据将直接返回到结果集中,不符合连接条件的数据列将以null填充后返回到结果集中,其中外连接又分左外连接、右外连接和全连接3种。

  (一)、等值连接查询

select p.*,c.* from country as c,person as p where c.countryid = p.countryid

  等上面的等值连接中,两张表都有countryid字段,因此查出来的结果中就会有两列countryid

  (二)、在等值结果中消除数据就是自然连接

select p.name,c.countryname from country as c,person as p where c.countryid = p.countryid

  (三)、自身连接

  一个数据表自己与自己建立连接称为自身连接

、内连接查询

  1、内连接查询的语法结构如下:

      select <属性或表达式列表>
      from <表名> [inner] join <表名>
      on <连接条件>
      [ where <限定条件> ]

  inner可以省略,当只见到join时就是省略了inner。内连接就是传统的连接操作,这里用on子句指定连接条件,用where子句指定其他限定条件:

  select p.name,c.countryname from country as c inner join person p on p.countryid = c.countryid

、左外连接查询

  1、左外连接查询的语法结构:

    select <属性或表达式列表>
    from <表名> left outer join <表名>
    on <连接条件>
    [ where <限定条件> ]

  如:

  select p.name,c.countryname from country as c left join person p on p.countryid = c.countryid 

  在结果表中包含第一个表中满足条件的所有记录,如果是在连接连接上匹配的记录,则第二个表返回相应值,否则第二个表返回null。也就是说,不管第二个表有没有记录都会第一个表的所有字段都会返回,这就是外连接与内连接的区别。

五、右外连接查询

  1、右外连接查询的语法结构如下:

    select <属性或表达式列表>
    from <表名> right outer join <表名>
    on <连接条件>
    [ where <限定条件> ]

  如:

select p.name,c.countryname from country as c right join person p on p.countryid = c.countryid

  在结果表中包含第二个表中满足条件的所有记录。如果是在连接条件上匹配的记录,则第一个表返回相应值,否则第一个表返回null。

六、全外连接查询

  1、全外连接查询的语法结构如下:

   select <属性或表达式列表>
   from <表名> full outer join <表名>
   on <连接条件>
   where <限定条件>]

  如:

  select p.name,c.countryname from country as c full join person p on p.countryid = c.countryid

  在结果表中包含两个表中满足条件的所有记录。如果是在连接条件上匹配的元组,则另一个表返回相应则,没有则返回null。

七、交叉连接

  交叉连接Corss join,用于将第一张表的所有记录与第二张表的所有记录组合一次并返回,这个东西在生成测试数据库时很有用,例如,你定义7个姓,7个名,再交叉连接就能够产生49条记录。

   如有如下表:姓氏表

    技术分享

    名字表:

    技术分享

    执行如下SQL语句:

  select LastName + firstname from name2 cross join name1

       结果如下:

      技术分享

8、联合查询union(union all)

   union是一个特殊的运算符,用于将两个或两个以上的查询产生一个结果集。join将信息水平连接(添加更多列),而union将信息垂直连接(添加更多行)。

   当使用union处理查询时,要注意以下几个关键点。

   (1)、所有union的查询必须在select列表中有相同的列数。即如果第一个查询有3个列数,第二个查询也要只有3个列数。

   (2)、union返回结果的标题集仅从第一个查询中获得,无论第二个查询如何命名或取别名都不会更改。

   (3)、查询中对应的列的数据类型必须隐式一致。注意不要求完全一致,只需要隐式一致。

   (4)、与其他非union不同,union的默认返回选项为distinct,而不是all。union all语句与union的不同点仅仅在于遇到相同的记录,全部保留而已。

   例如还是用第7条的例子,执行如下语句:

select * from name1 
union
select * from name2

   返回结果为:

   技术分享

   由于union默认是distinct查询,因此想要获得所有的记录时,可以用union all,这样就算上述例子中两个表都有‘王‘,也会两条记录一起返回。

   现在来看一下综合示例:

   为了展示一下,建了两张表,并添加了几条记录,如下:

    person表

    技术分享

    country表

    技术分享

   1、查询一个列表,该列表要包含国家名称列表与该国家下的person总数。

select c.countryname,count(p.id) 
from country as c 
inner join person as p on c.countryid = p.countryid 
group by c.countryname

    输出结果:

    技术分享

    现在在来加一个条件,要求人口数按升序排列:

select c.countryname,count(p.id) as co
from country as c 
inner join person as p on c.countryid = p.countryid 
group by c.countryname
order by co asc

    输出结果:

    技术分享

    再加一个条件,要求只输出person数大于2的记录:

select c.countryname,count(p.id) as co
from country as c 
inner join person as p on c.countryid = p.countryid 
group by c.countryname
having count(p.id) > 2
order by co asc

    输出结果为:

    技术分享

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。