sql 表连接

2018年01月12日 15:00 | 1006次浏览

一、连接查询简介

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

[<表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

结果:


感觉本站内容不错,读后有收获?小额赞助,鼓励网站分享出更好的教程