表的连接操作

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