课堂演示.sql

主要介绍了sql的基础教程

select j.EmployeeID,i.ReportsTo
from dbo.Employees i inner join dbo.Employees j
on i.EmployeeID = j.EmployeeID
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
--交叉联接示例
SELECT sales.buyer_id,buyer_name, qty
FROM buyers CROSS JOIN sales
--加where条件的交叉联接（等于内联接）
SELECT sales.buyer_id,buyer_name, qty
FROM buyers CROSS JOIN sales
where buyers.buyer_id=sales.buyer_id
--和带where条件的交叉联接等效的写法
select buyer_name,qty 
from buyers,sales
where buyers.buyer_id=sales.buyer_id
--左外联接示例
SELECT buyer_name, buyers.buyer_id, qty
FROM buyers LEFT OUTER JOIN sales
ON buyers.buyer_id = sales.buyer_id
--右外联接示例
SELECT buyer_name, buyers.buyer_id, qty
FROM buyers right OUTER JOIN sales
ON buyers.buyer_id = sales.buyer_id
--全外联接示例
SELECT buyer_name, buyers.buyer_id, qty
FROM buyers full OUTER JOIN sales
ON buyers.buyer_id = sales.buyer_id
--自联接示例
--查找居住在 California 的 Oakland 相同邮码区域中的作者。
USE pubs
go
SELECT a.au_fname, a.au_lname
FROM authors a INNER JOIN authors b 
ON a.zip = b.zip
WHERE a.city = 'Oakland'