--等值连接
--查询所有产品及它们的供应商名称。
select ProductName,CompanyName from
dbo.Products inner join dbo.Suppliers
on dbo.Products.SupplierID=Suppliers.SupplierID
--非等值连接
--查询所有价格高于20的商品名及供应商
select ProductName,CompanyName,UnitPrice
from Products inner join dbo.Suppliers
on Products.SupplierID = Suppliers.SupplierID
where UnitPrice > 20
order by UnitPrice
select ProductName,CompanyName,UnitPrice
from Products inner join dbo.Suppliers
on Products.SupplierID = Suppliers.SupplierID
and UnitPrice > 20
order by UnitPrice
--=====================================
select * from Products
--复制表
select * into Products_new from Products
select ProductID,ProductName
into Products_new2
from Products
where UnitPrice > 20
--联合查询
select ProductID,ProductName from Products
union all
select ProductID,ProductName from Products_new
-- SELECT select_list
-- [ INTO new_table ]
-- FROM table_source
-- [ WHERE search_condition ]
-- [ GROUP BY group_by_expression ]
-- [ HAVING search_condition ]
-- [ ORDER BY order_expression [ ASC | DESC ] ]
-------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
if exists(select * from sysobjects
where name = 'a' and type = 'U')
drop table a
go
create table a
(
id int,
name varchar(6)
)
insert a values(1,'a')
insert a values(2,'b')
insert a values(3,'c')
insert a values(4,'d')
if exists(select * from sysobjects
where name = 'b' and type = 'U')
drop table b
go
select * into b from a
select * from a
select * from b
--1.
select * from a left join b on a.id=b.id where a.id=1
--2.
select * from a left join b on a.id=b.id and a.id=1
--3.
select * from a left join b on a.id=b.id and b.id=1
--4.
select * from a left join b on a.id=1