备注char(20))
CREATETABLE库存信息表(
图书号char(20)CONSTRAINTFK_7foreignkeyreferences 图书信息表(图书号), 图书数量char(20), 入库信息char(20), 出库信息char(20), 备注char(20),)
3.3 载入数据 (1)图书类型
16
（2）出版社
(3)图书信息表
（4）仓库
(5)进货、入库
17
（6）销售、出库
（7）图书类别视图
3.4 创建存储过程 (1)某段时间内进货查询
CREATEprocedurebuysearch @起始时间date,@结束时间date
18
asbegin SELECT* from进货详情表
where进货日期>=@起始时间and进货日期<=@结束时间 end;
(2)某段时间内的销售查询
CREATEproceduresellsearch @起始时间date,@终止时间date asbegin SELECT* from销售详情表
where销售日期>=@起始时间and销售日期<=@终止时间 end;
3.5查询与统计功能
--每月的销售总额和销售总量 Create Proc QueryBook @date CHAR(10) As
Select Sum(SellNum) as TotalNuns,Sum(Sprice) as TotalSales From SellOut
Where DateDiff(mm,@date,SDate)=0 --每月销售的排行榜 Create Proc BookBack @date smalldatetime As
Select top 10 b.Bname,s.SellNum From Book b
inner join SellOut s on b.BookID=s.BookID
--按月查卖了多少本书 Create Proc QueryMonth @date smalldatetime As
Begin
19
Select b.Bname,Sum(b.Bnum) From Book as b
inner join SellOut as s on b.BookID=s.BookID
Where Datediff(mm,@date,s.SDate)=0 Group by b.Bname End
3.6触发器脚本
--使用触发器，实现新书在书库中的插入工作和图书数量修改
CreateTrigger进货On进货详情表 FORInsertAs
declare@nintSelect@n=图书号frominserted
declare@numintSelect@num=图书数量Frominserted
Update库存信息表set图书数量=图书数量+@numWhere图书号=@n
CreateTrigger销售On销售详情表 FORInsertAs
declare@nintSelect@n=图书号frominserted
declare@numintSelect@num=图书数量Frominserted
Update库存信息表set图书数量=图书数量-@numWhere图书号=@n
3.7视图脚本
Create View SBook As
Select Publisher,BookID, Bname,20-Bnum as Num
From Book where Bnum<5 --小于本时就定本书 Group by Publisher, BookID, Bname, 20-Bnum
Create Proc SelectBook As Begin
Select* From SBook Insert Into BuyIn
Select v.BookID,GetDate(),v.Price,v.Num From SBook as v
20