学步园

--建立客户信息表
create table CustomInfo
(
   cid nvarchar(20) not null primary key,
   cpassword nvarchar(30) not null check(len(cpassword)>=6),
   csum int not null default(0)
)
go

insert into CustomInfo(cid,cpassword,csum)
values('张三','zhangsan',0)
go
insert into CustomInfo(cid,cpassword,csum)
values('李四','lisi123',0)
go
insert into CustomInfo(cid,cpassword,csum)
values('王五','wangwu',0)
go

--创建购买记录表
create table BuyNote
(
  buyannal datetime not null,
  cid nvarchar(20) not null foreign key references CustomInfo(cid),
  pid nvarchar(10) not null foreign key references Product (ProductID),
  [count] int not null default (1)
)
go

--建立积分等级对照表
create table IntegralLevel
(
startcount int not null primary key,
[Name] nvarchar(10) not null
)
go

insert into IntegralLevel(startcount,[Name])
values(0,'普通卡用户')
go
insert into IntegralLevel(startcount,[Name])
values(10,'铜卡用户')
go
insert into IntegralLevel(startcount,[Name])
values(20,'银卡用户')
go
insert into IntegralLevel(startcount,[Name])
values(40,'金卡用户')
go
insert into IntegralLevel(startcount,[Name])
values(60,'贵宾卡用户')
go

--建立一个触发器
create trigger BuyFoodTrigger
on BuyNote
for insert
as
begin
declare @cid nvarchar(20),@count int
select @cid =cid,@count =[count] from inserted
update CustomInfo set csum = csum + @count where cid = @cid
print '感谢您的购买，您的积分已经被记录下来！'
end
go

--制作购物的SP
create procedure BuyFood
@cid nvarchar(20),
@pid nvarchar(10),
@count int
as
begin

  insert into BuyNote(buyannal,cid,pid,[count]) values(getdate(),@cid,@pid,@count)
print '恭喜您购买成功了！'
end
go

execute BuyFood '张三','zhangsan',5
go

--建立一个总的客户信息表
create table TotalCustom
(
   cid nvarchar(20) not null primary key,
   cpassword nvarchar(30) not null check(len(cpassword)>=6),
   csum int not null default(0)
)
go
insert into ToTalCustom(cid,cpassword,csum)
values('张三丰','zhangsanfeng',0)
go
insert into ToTalCustom(cid,cpassword,csum)
values('王老五','wanglaowu',0)
go

--制作一个用于查级别的标量函数
create function GetLevelByID
(
@cid nvarchar(20)
)
returns nvarchar(28)
as
begin
declare @csum int,@level nvarchar(25)
set @level = @cid + '==>无此卡号'
if exists (select cid from CustomInfo where cid = @cid)
   begin
   set @csum = (select csum from CustomInfo where cid = @cid)
   set @level = (select top 1 [Name] from IntegralLevel where startcount<=@csum order by desc)
   set @level = @cid +'---'+@level
   end
return @level
end
go