1.建一张客户表，包含客户名称，联系人，电话号码，公司地址.
2.建一张订单表，包含订单编号，客户ID,供应商ID,订单金额，客户支付状态，供应商支付状态,订单日期.
3.建一张供应商表，包含供应商名称，联系人，电话号，供应商地址
create table customer
(
customerID int primary key identity(1,1) ,
customerName varchar(50),
cuslinkMan varchar(50),
customerPhone varchar(12),
companyAdress varchar(100)
)
create table supplier
(
supplierID INT primary key identity(1,1) ,
supplierName varchar(50),
supLinkeMan varchar(50),
supAdress varchar(100)
)
create table orderForm
(
orderID int identity(1,1),
orderNum varchar(50),
customerID int,
supplierID int,
orderMoney decimal ,
custState int,
orderState int ,
orderTime datetime,
constraint pk_order primary key(orderID) ,
constraint fk_supplier foreign key(supplierID) references supplier(supplierID),
constraint fk_customer foreign key(customerID) references customer(customerID)
)
insert into customer(customerName,cuslinkMan,customerPhone,companyAdress) values('太阳','刘敏','18762676815','无锡西区')
insert into dbo.supplier(supplierName,supLinkeMan,supAdress)
values('安泰','李丹','大连')
insert into dbo.orderForm(orderNum,customerID,supplierID,orderMoney,custState,orderState,orderTime)
values('0001','1','1','10000','0','0','2012.12.21')
练习一：写存储过程，根据订单日期，客户，供应商来查询订单，包含客户，供应商名称，订单详细信息，订单日期可以为空，客户跟供应商可以全选.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
createPROCEDURE 订单查询
@订单日期 datetime,
@客户varchar(50),
@供应商varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT orderNum, C.customerName,S.supplierName ,orderMoney,custState ,orderState ,orderTime
from dbo.orderForm O,dbo.customerC,dbo.supplier S
where
C.customerID=O.customerID
and S.supplierID=O.supplierID
--判断O.orderTime
and
(
@订单日期 =''
or @订单日期 is null or O.orderTime = @订单日期
)
and --判断客户
(
@客户='-1' or C.customerName=@客户
)
and--判断供应商
(
@供应商='-1' or S.supplierName=@供应商
)
END
GO
EXEC 订单查询 '2012-12-21','星星','上海'
练习二：写存储过程，查询订单数最多的前3个客户的订单信息。
方法一：
ALTER PROCEDURE [dbo].[订单总数前三]
AS
BEGIN
SELECT O.*
from dbo.orderForm O
where
O.customerID in
(
SELECT top 3O.customerID -- ,COUNT(O.orderNum) as 'aaa'
from dbo.orderForm O
group by O.customerID
order by COUNT(O.orderNum) DESC
)
END
方法二：
练习三：写存储过程，查询订单金额最多的前3个客户的订单信息。
USE [test]
GO
/****** Object:StoredProcedure [dbo].[订单金额前三]Script Date: 12/26/2012 09:30:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[订单金额前三]
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT O.orderID,C.customerID ,customerName,S.supplierName ,orderMoney,custState ,orderState ,orderTime
from dbo.orderForm O,dbo.customerC,dbo.supplier S
where
C.customerID=O.customerID
and S.supplierID=O.supplierID
and O.customerID in
(
SELECT top 3 O.customerID--, sum(O.orderMoney)AS 订单总金额
from dbo.orderForm O
group by O.customerID
order by sum(O.orderMoney) desc
)
END
练习四：写sql，删除3条供应商数据，并且相应订单的供应商ID也置为null（这里应该是更新操作）
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE 删除供应商信息
-- Add the parameters for the stored procedure here
@供应商1ID int,
@供应商2ID int,
@供应商3ID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
update dbo.orderForm
set supplierID=null
where supplierID=@供应商1ID
or supplierID=@供应商2ID
or supplierID=@供应商3ID
delete from dbo.supplier
where supplierID=@供应商1ID
or supplierID=@供应商2ID
or supplierID=@供应商3ID
END
GO
exec 删除供应商信息 '2','3','4'
练习五：查询不存在供应ID的订单信息，用(not exits)
CREATE PROCEDURE 查询无供应商的订单信息
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from orderForm A wherenot EXISTS
(
select supplierID from dbo.supplier B where B.supplierID=A.supplierID
)
END
GO
exec 查询无供应商的订单信息