表的连接操作

--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
select * from authors--23条
select * from publishers--8条
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--左(外部)连接测试,返回23条记录
--##特别写法##
select au_fname,au_lname,authors.city,pub_name
from authors,publishers
where authors.city*=publishers.city

select au_fname,au_lname,authors.city,pub_name
from authors
left join publishers on authors.city=publishers.city

select au_fname,au_lname,authors.city,pub_name
from authors
left outer join publishers on authors.city=publishers.city
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--右(外部)连接测试,返回9条记录
--注意是返回9条记录,而不是8条,是因为左表authors中城市为Berkeley的有2条记录
--##特别写法##
select au_fname,au_lname,authors.city,pub_name
from authors,publishers
where authors.city=*publishers.city

select au_fname,au_lname,authors.city,pub_name
from authors
right join publishers on authors.city=publishers.city

select au_fname,au_lname,authors.city,pub_name
from authors
right outer join publishers on authors.city=publishers.city
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--完全(外部)连接测试,返回30条=23+8-1记录
select au_fname,au_lname,authors.city,pub_name
from authors
full join publishers on authors.city=publishers.city

select au_fname,au_lname,authors.city,pub_name
from authors
full outer join publishers on authors.city=publishers.city
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--内部连接,返回2条记录
--隐式内连接
select au_fname,au_lname,authors.city,pub_name
from authors,publishers
where authors.city=publishers.city

select au_fname,au_lname,authors.city,pub_name
from authors
inner join publishers on authors.city=publishers.city

select au_fname,au_lname,authors.city,pub_name
from authors
join publishers on authors.city=publishers.city
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--交叉连接
--184条=23X8
select au_fname,au_lname,pub_name
from authors,publishers
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
--自我连接
select * from authors order by city --23条

--将返回49条记录,
/*
其计算方法是:有重复的城市名称则个数求平方,
没有重复的直接累加.
1+2X2+6+5X5+2X2+1+2X2+4
*/
select A.au_id,A.au_fname,A.au_lname,A.city 
from authors A,authors B
where A.city=B.city
order by A.city

--23条,distinct关键字的作用是:使select与from之间的字段合并起来看作唯一的字段,判断其是否重复
select distinct A.city, A.au_fname,A.au_lname
from authors A,authors B
where A.city=B.city
order by A.city   

select distinct A.au_fname,A.au_lname,A.city
from authors A
where A.city=A.city
order by A.city
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值