s数据库之多表查询

use test
go
select * from dbo.buyers

select * from dbo.sales
--交叉连接
select buyers.buyer_id,buyer_name,sales.buyer_id,prod_id,qty
from dbo.buyers cross join dbo.sales
where buyers.buyer_id = sales.buyer_id

select buyer_name,prod_id,qty
from dbo.buyers , dbo.sales
where buyers.buyer_id = sales.buyer_id

--内连接
select buyer_name,prod_id,qty
from dbo.buyers inner join dbo.sales
on buyers.buyer_id = sales.buyer_id

--外连接
select buyers.buyer_id, buyer_name,prod_id,qty
from dbo.buyers left outer join dbo.sales
on buyers.buyer_id = sales.buyer_id

select buyers.buyer_id, buyer_name,prod_id,qty
from dbo.buyers right outer join dbo.sales
on buyers.buyer_id = sales.buyer_id

select buyers.buyer_id, buyer_name,prod_id,qty
from  dbo.sales right outer join dbo.buyers
on buyers.buyer_id = sales.buyer_id

select buyers.buyer_id, buyer_name,prod_id,qty
from  dbo.sales full outer join dbo.buyers
on buyers.buyer_id = sales.buyer_id

--自连接
--购买了相同产品的购物者信息
select a.buyer_id,a.prod_id,a.qty ,b.buyer_id
from sales a , sales b
where a.prod_id = b.prod_id
and a.buyer_id != b.buyer_id

select * from sales

--查询所有员工的姓名 和 其领导的姓名
use Northwind
go
select  yg.EmployeeID,yg.LastName,yg.FirstName,
 yg.ReportsTo, ld.LastName,ld.FirstName
from dbo.Employees yg ,Employees ld
where yg.ReportsTo = ld.EmployeeID

 

 

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

--先创建一个示例库备用
use master
go
if exists(select * from sysdatabases
    where name = 'test')
drop database test
go
create database test
go

--使用示例库
use test
go

--在示例库中创建表buyers备用
create table buyers
(
 buyer_id int,
 buyer_name varchar(50)
)
go

--在表buyers中插入一些数据备用
insert into buyers values (1,'Adam Barr')
insert into buyers values (2,'Sean Chai')
insert into buyers values (3,'Eva Corets')
insert into buyers values (4,'Mary O’Melia')
go

--在示例库中创建表sales备用
create table sales
(
 buyer_id int,
 prod_id int,
 qty int
)
go

--在表sales中插入一些数据备用
insert into sales values (1,2,15)
insert into sales values (1,3,5)
insert into sales values (4,1,37)
insert into sales values (3,5,11)
insert into sales values (4,2,1003)
go
----------------------------------------------------------------------------------------

-----例：查询所有出了书的出版社名pub_name和
--所出的书名title。
select * from titles

select * from publishers
--交叉连接
select pub_name,title
from titles, publishers
where titles.pub_id =  publishers.pub_id

--内连接
select pub_name,title
from dbo.publishers inner join dbo.titles
on publishers.pub_id = titles.pub_id

select pub_name,title,au_id
from dbo.publishers,dbo.titles ,dbo.titleauthor
where publishers.pub_id = titles.pub_id
and  titles.title_id =titleauthor.title_id

--外连接
select pub_name,title
from dbo.publishers left outer join dbo.titles
on publishers.pub_id = titles.pub_id

 

 

--------------------------------------------------------------------------------------