数据库课程设计商品信息管理系统

课程设计课程名称：数据库系统概论设计题目：商品信息管理系统设计与实现院系名称信息科学与工程学院班级交通运输1202班姓名与学指导教师1课题简介随着现代科技的高速发展，设备和管理的现代化，在实际工作中如何提高工作效率成为一个很重要的问题。而建立管理信息系统是一个很好的解决办法。经过半年的学习，我们对计算机方面的知识有了很大的提升，本着理论联系实际的宗旨，通过学校提供的这次软件技术实践的机会，在指导教师的帮助下，历经两周时间，我们自行设计一套商品信息管理系统，在下面的各章中，我将以这套商品管理系统为例,谈谈其开发过程和所涉及到的问题。设计目的应用对数据库系统原理的理论学习,通过上机实践的方式将理论知识与实践更好的结合起来,巩固所学知识。数据库应用课程实践：实践和巩固在课堂教学中学习有关知识，熟练掌握对于给定结构的数据库的创建、基本操作、程序系统的建立和调试以及系统评价。数据库原理软件设计实践：实践和巩固在课堂教学中学习的关于关系数据库原理的有关知识和数据库系统的建立方法，熟练掌握对于给定实际问题，为了建立一个关系数据库商品信息管理系统，必须得经过系统调研、需求分析、概念设计、逻辑设计、物理设计、系统调试、维护以及系统评价的一般过程，为毕业设计打下基础。设计内容运用基于E-R模型的数据库设计方法和关系规范化理论做指导完成从系统的分析到设计直至系统的最终实现,开发商品信息系统,完成商品信息管理系统的全部功能,商品入库子系统，商品出库子系统，商品库存子系统。首先做好需求分析，并完成数据流图和数据字典。其次做概念分析，利用实体联系的方法将需求分析的用户需求抽象为信息结构，得到E-R图。然后就是逻辑结构设计，将E-R图转换为计算机系统所支持的逻辑模型设计具体实施进行需求分析，编写数据字典。1、系统功能要求设计有一个存放商品的仓库，每天都有商品出库和入库。每种商品都有商品编号、商品名称、生产厂家、型号、规格等。入库时必须填写入库单据，单据包括商品名称、生产厂家、型号、规格、入库数量、入库日期、入库仓库号、入库仓库名称、送货人姓名。出库时必须填写出库单据，单据包括仓库号、仓库名称、商品编号、商品名称、型号、规格、出库数量、出库日期、提货人姓名。设置商品库存台帐，商品库存台帐是对仓库中目前库存的所有商品的明细记录，商品库存台帐包括商品编号、商品名称、型号、规格、库存数量、库存日期。每当有商品入库或商品出库时都应该自动修改该台帐，最后一次修改的是现在的库存情况。商品的入库和出库过程通过库存台帐更加清晰条理地显示出仓库中商品的库存数量和库存日期等信息，容易对库存内的商品信息进行查询，增加，修改，删除等操作。⑦该仓库的商品型号统一为DA01-DA99格式，规格为CX100-CX199格式主要功能：①商品管理：增加商品：修改商品，删除商品，浏览商品增加商品：实现增加商品的详细资料的功能修改商品：实现修改商品资料的功能删除商品：实现删除该商品的所有资料的功能浏览商品：实现浏览所有商品的功能②库存管理：实现商品的入库，在商品入库时通过触发器或存储过程同时完成商品库存台帐的更新。实现商品的出库，在商品出库时通过触发器或存储过程同时完成商品库存台帐的更新。实现按商品名称查询商品的入库情况及目前的库存量。实现按入库日期查询商品的入库情况及目前的库存量。实现按商品名称查询商品的出库情况及目前的库存量。实现按出库日期查询商品的出库情况及目前的库存量。按时间段查询商品库存情况。实现分别按年、季度和月对入库商品数量的统计。实现分别按年、季度和月对出库商品数量的统计。2、系统模块设计库存物资管理大体可以分为以下3大模块，如下图所示:首先是商品入库模块，该模块主要就是描述把采购回来的商品，分类别的放置到指定的仓库中去，然后是商品出库模块，该模块主要描述从指定的仓库中拿出商品；最后时库存模块，这个模块主要是记录商品的库存数量变化。3、数据字典表表名中文名Ware商品

Depot仓库Stock库存Stocking入库Outbound出库商品Ware字段名数据类型是否可以为空IsPKIsFK中文名WNochar(10)NOTNULLYesNo商品编号WNamevarchar(50)NOTNULLNoNo商品名称WFactoryvarchar(50)NOTNULLNoNo生产厂家WTypevarchar(20)NOTNULLNoNo型号Specvarchar(20)NOTNULLNoNo规格仓库Depot字段名数据类型是否可以为空IsPKIsFK中文名DNochar(10)NOTNULLYesNo仓库号DNamevarchar(50)NOTNULLNoNo仓库名称库存Stock字段名数据类型是否可以为空IsPKIsFK中文名WNochar(10)NOTNULLYesYes商品编号Dnochar(10)NOTNULLYesYes仓库号WAmountIntNOTNULLNoNo库存数量入库Stocking字段名数据类型是否可以为空IsPKIsFK中文名SNochar(10)NOTNULLYesNo入库号WNochar(10)NOTNULLNoYes商品编号Dnochar(10)NOTNULLNoYes仓库号SAmountIntNOTNULLNoNo入库数量SDatesmalldatetimeNOTNULLNoNo入库日期Suppliervarchar(50)NOTNULLNoNo送货人姓名出库Outbound字段名数据类型是否可以为空IsPKIsFK中文名ONochar(10)NOTNULLYesNo出库号WNochar(10)NOTNULLNoYes商品编号Dnochar(10)NOTNULLNoYes仓库号OAmountintNOTNULLNoNo出库数量ODatesmalldatetimeNOTNULLNoNo出库日期Buyersvarchar(50)NOTNULLNoNo提货人姓名设计完整的E-R图。分E-R图:三、进行数据库的逻辑设计。关系模式如下：商品（商品编号、商品名称、生产厂家、型号、规格）商品编号为主键仓库（仓库号、仓库名称）仓库号为主键库存（商品编号，仓库号，库存数量）（商品编号，仓库号）为主键，同时也分别为外键入库（入库号，商品编号，仓库号，入库数量，入库日期，送货人姓名）（入库号）为主键，（商品编号，仓库号）为外键出库（出库号，商品编号，仓库号，出库数量，出库日期，提货人姓名）（出库号）为主键，（商品编号，仓库号）为外键四、完成物理数据库的设计，（包括数据库、表、索引、视图、完整性约束的物理设计。创建数据库createdatabaseMaterial_DB创建商品表useMaterial_DBcreatetableWare(/*定义商品表*/WNochar(10)NOTNULLprimarykey,WNamevarchar(50)NOTNULL,WFactoryvarchar(50)NOTNULL,WTypevarchar(20)NOTNULL,Specvarchar(20)NOTNULL)useMaterial_DB/*插入商品信息*/insertintoWarevalues('100000','毛巾'，'新家园','DA11','CX100');insertintoWarevalues('100001','台灯'，'新家园','DA35','CX110');insertintoWarevalues('100002','电冰箱','海尔','DA61','CX900');insertintoWarevalues('100003','电视','东芝','DA52','CX901');insertintoWarevalues('100004','长城干红'，'中粮华夏','DA25','CX108');insertintoWarevalues('100005','笔记本','东芝','DA24','CX781');WNoWNameWFactoryWTypeSpec100005笔记本东芝D册OTS1100004长城干红中粮华夏DA25«108100002电冰箱DA&1CX900100003电视东芝DA52CX901100000毛巾新探园DA11CX100100001台灯新家园DA35CT110创建仓库表useMaterial_DBcreatetableDepot(/*定义仓库表*/DNochar(10)NOTNULLprimarykey,DNamevarchar(50)NOTNULL)useMaterial_DB/*插入仓库信息*/insertintoDepotvalues('2010001','胜昌');insertintoDepotvalues('2010002','德隆');insertintoDepotvalues('2010003','豪友')；DNoDNameZ010001胜昌Z010002Z010003创建库存表useMaterial_DBcreatetableStock(/*定义库存关系*/WNochar(10)NOTNULL,DNochar(10)NOTNULL,--WDatesmalldatetimeNOTNULL,WAmountintNOTNULL,primarykey(WNo,Dno),foreignkey(WNo)referencesWare(WNo),foreignkey(DNo)referencesDepot(DNo))-L*匸、WNoDNoWAmount1000002010001ZOOlCiOOOQ20100Q270looooa2010003200100001201000180010000120100Q326010000Z2010001ZOO10000Z201000289010000220100Q320010000320100011701000032010002ZOO100C032010003迦100004-201000136010000+201000280100C052010001+2010000520100023001000052010003£00

创建入库表useMaterial_DBcreatetableStocking(/*定义入库关系*/SNochar(10)NOTNULLprimarykey,WNochar(10)NOTNULL,DNochar(10)NOTNULL,foreignkey(WNo)referencesWare(WNo),foreignkey(DNo)referencesDepot(DNo),SAmountIntNOTNULL,SDatesmalldatetimeNOTNULL,Suppliervarchar(50)NOTNULL)/U%ULFV.UUUVUXLMUUV-7皿吕J-iJ....专LUU丄J-J*.二甲亠•”」5NoWNoDNoSAmount5C-dteSupplier1001000052i：i1i：ii：ii：i12002006-1-20:00:no小明10110000520100032002006-1-20:00:00小兰10210000120100032002006-1-2OlOOl0010310000220100012002006-1-20:00:0010410000320100012002007-1-1+0;00;0010510000520100022002006-1-25u：no：on小明1061000002010002200£006-2-20:00:00小明10710000120100012002005-3-20:00:00108100002201000Z2002007-4-20i00!00小红10910000320100032002006-4-20:00:00小风110100004201000120020Q6-6-9Q;Q0;0Q1111000042010002200£005-6-20:00:00小凤1121000052010M12002005-6-20:00:00小書11310000020100032002005-6-20i00!00小■明11410000120100012002005-6-20:00:00小东115100002201000Z2002007-7-20:00;001161000032010003200£006-8-20:00:00小风11710000020100012002006-8-20:00:00小明HS1000012010Q012002005-9-40:00:00小东11910000220100022002005-6-20i00!00小红12010000320100032002005-9-110:00:001Z1100002201000Z2002005-9-10;00;00屮明12210000320100022002005-7-80:00:0012310000220100032002005-4-11i：i：i：i0:0i：i小敏12410000120100012002007-7-80:00:00小红12510000120100032002005-8-21OiOOlOO小書12610000520100012002005-7-60:00:00小書1271000032010J0320020Q5-9-1Q;QO;OQ屮兰12810000220100022002007-7-80:00:00小兰1291000042010Q012002007-7-80:00:00小兰13010000520100022002007-7-80:00!00小红创建出库表useMaterial_DBcreatetableOutbound(/*定义出库关系*/ONochar(10)NOTNULLprimarykey,WNochar(10)NOTNULL,foreignkey(WNo)referencesWare(WNo),DNochar(10)NOTNULL,foreignkey(DNo)referencesDepot(DNo),OAmountintNOTNULl,ODatesmalldatetimeNOTNULL,Buyersvarchar(50)NOTNULL)or-JoWMuDNoOAmountOD^teBuyers11i：ii：ii：ii：i52i：i1i：ii：ii：i15Li2010-1-20:00:00101i：ii：ii：ii：i520100025Li2009-4-15i：i：i：i0:0i：i小虹111000012010003502010-6-30:00:00121000052010001302009-4-150:00:00小虹131i：ii：ii：ii：i220100022d2010-7-50:00:00小凤1斗1i：ii：ii：ii：i32i：i1i：ii：ii：i35Li2009-7-50:00:00小红151i：ii：ii：ii：ii：i2010002斗口2010-7-50:00:00小凤161i：ii：ii：ii：i420100025Li2009-7-50:00:00小凤171000052010002202C08-9-10:00:00小明IS1000012010003302C10-9-10:00:00151000002010002402010-9-10:00:00小颐21000052010002302010-1-20:00:00■1■红201000022010002502008-9-10:00:00孙红211000002010002202010-8-210:00:00■1■書221000032010001302008-9-10:00:00■1■書231000022010002402003-9-10100:00冲兰2斗1000032010003502008-12-300:0...孙兰251000042010002202009-12-300:0...小兰2&1000052010001402008-12-8OiOOiOO271000012010003102010-12-300:0...<1傲281000022010002302008-12-80:00:00孙明29100003Z010003302009-12-180:0...小红31000012010003502010-1-20:00:00小凤301000042010001402010-9-10:00:0041000052010001302010-1-20:00:00小红51000022010002202010-2-60:00:0061000032010003502010-3-90:00:00孙红71000002010002302009-M50:00:00<1枫81000042010002502009-4-150:00:009100005£010001302010-6-20;00:00小红完整约束的说明：商品Ware的WNo商品编号要求在100000〜999999之间，WName不能取空；仓库Depot的DNo仓库编号要求在2010001〜2010999之间，DName不能取空；该仓库的商品型号统一为DA01-DA99格式，规格为CX100-CX199格式。useMaterial_DB/*定义表Ware唯一性的约束条件*/alterTableWareaddunique(WName,WFactory,WType,Spec);alterTableWare/*定义WType的完整性约束条件：商品型号统一为DA01-DA99格式*/addconstraintC2check(WTypelike'[D][A][0-9][1-9]');alterTableWare/*定义Spec的完整性约束条件：规格为CX100-CX199格式*/addconstraintC3check(Speclike'[C][X][1-9][0-9][0-9]');useMaterial_DBaltertableWare/*添加对表Ware的WNo属性的完整性约束，要求在~9999999999之间*/addconstraintC4check(WNolike'[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]');altertableDepotaddconstraintC5check(DNolike'[2][0][1][0][0-9][0-9][0-9]');useMaterial_DBaltertableWaredropconstraintC4;useMaterial_DBaltertableWare/*添加对表Ware的WNo属性的完整性约束，要求在~999999之间*/addconstraintC4check(WNolike'[1-9][0-9][0-9][0-9][0-9][0-9]');创建视图通过WLST(提取“物料视图”每个字的头字母)视图来浏览库存中相同编号商品的信息,其中包括：商品编号WNo、商品名WName、商品厂家WFactory、商品型号WType、商品规格Spec、商品数量SNum、createviewVWname(商品编号，商品名称，库存数量)--视图ASSELECTStock.WNo,Ware.WName,sum(Stock.WAmount)fromWare,StockwhereStock.WNo=Ware.WNogroupbyStock.WNo,Ware.WName;商品舖号商称璋存数量loooao毛巾470loooai台灯10601000Q2电咏箱1290100003电观990100004长城干红440100005笔记本92Q五、实现商品的入库，在商品入库时通过触发器或存储过程同时完成商品库存台帐的更新。入库表的触发器createtriggerstocking_intoonstocking--这是入库表的触发器afterinsertasbegindeclare@achar(10),@bchar(10),@dint;select@a=i.DNo,@b=i.WNo,@d=i.SAmountfrominsertedasi;if(selectStock.WNofromStockwhereStock.WNo=@bandStock.DNo=@a)isnotnullbeginupdateStocksetStock.WAmount=Stock.WAmount+@dwhereStock.WNo=@bandStock.DNo=@a;return;endinsertintostockvalues(@b,@a,@d);endDutbourdWNo^WNo<>DnoOOAmojnt^ODate<>BuyersStdeking■^SMoOWNo<>Dio<?SAmount^SDate^Sjpplierinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvalues('100','100005','2010001'('101','100005','2010003'('102','100001','2010003'('103','100002','2010001'('104','100003','2010001'('105','100005','2010002'('106','100000','2010002'('107','100001','2010001'('108','100002','2010002'('109','100003','2010003'('110','100004','2010001'('111','100004','2010002'('112','100005','2010001'('113','100000','2010003'('114','100001','2010001'('115','100002','2010002'('116','100003','2010003',200,'2006-01-02'，'小明');,200,'2006-01-02'，'小兰');,200,'2006-01-02'，'小东');,200,'2006-01-02'，'小红');,200,'2007-01-14','小风');,200,'2006-01-25'，'小明');,200,'2006-02-02'，'小明');,200,'2005-03-02','小东');,200,'2007-04-02','小红');,200,'2006-04-02','小风');,200,'2006-06-09','小敏');,200,'2005-06-02'，'小风');,200,'2005-06-02'，'小青');,200,'2005-06-02'，'小明');,200,'2005-06-02','小东');,200,'2007-07-02','小红');,200,'2006-08-02'，'小风');insertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvaluesinsertintostockingvalues('117','100000','2010001'('118','100001','2010001'('119','100002','2010002'('120','100003','2010003'('121','100002','2010002'('122','100003','2010002'('123','100002','2010003'('124','100001','2010001'('125','100001','2010003'('126','100005','2010001'('127','100003','2010003'('128','100002','2010002'('129','100004','2010001'('130','100005','2010002',200,'2006-08-02','小明');,200,'2005-09-04','小东');,200,'2005-06-02','小红');,200,'2005-09-11','小风');,200,'2005-09-01','小明');,200,'2005-07-08','小敏');,200,'2005-04-11','小敏');,200,'2007-07-08','小红');,200,'2005-08-21','小青');,200,'2005-07-08','小青');,200,'2005-09-01','小兰');,200,'2007-07-08','小兰');,200,'2007-07-08','小兰');,200,'2007-07-08','小红');六、实现商品的出库，在商品出库时通过触发器或存储过程同时完成商品库存台帐的更新。出库表触发器createtriggeroutbound_donOutbound--这是出库表的触发器afterinsertasbegindeclare@achar(10),@bchar(10),@dint;select@a=i.DNo,@b=i.WNo,@d=i.OAmountfrominsertedasiif(selects.WNofromStockasswheres.DNo=@aands.WNo=@b)isnotnullbeginif(selects.WAmountfromStockass,insertedwheres.WAmount>=inserted.OAmountands.WNo=inserted.WNoands.DNo=inserted.DNo)isnotnullbeginupdateStocksetStock.WAmount=Stock.WAmount-@dwhereStock.DNo=@aandStock.WNo=@bendelsebeginprint'库存量不够'rollback

endendelsebeginprint'库存中没有这种产品'rollbackendendStdStdeking

*^SMo

g恤

◎DioAmount<>SDate<^SjpplierinsertintoOutboundinsertintoOutboundvalues('1',insertintoOutboundvalues('2',insertintoOutboundvalues('3',insertintoOutboundvalues('4','100005','2010001',50,'2010-01-02','100005','2010002',30,'2010-01-02','100001','2010003',50,'2010-01-02','100005','2010001',30,'2010-01-02','小红');'小红');'小风')；'小红');/*/*方法三/*/*方法三/*/*方法三*/insertintoOutboundvalues('5','100002','2010002',20,'2010-02-08','小风');insertintoOutboundvalues('6','100003','2010003',50,'2010-03-09','小红');insertintoOutboundvalues('7','100000','2010002',30,'2009-04-15','小风');insertintoOutboundvalues('8','100004','2010002',50,'2009-04-15','小风');insertintoOutboundvalues('9','100005','2010001',30,'2010-06-02','小红');insertintoOutboundvalues('10','100005','2010002',50,'2009-04-15','小红');insertintoOutboundvalues('11','100001','2010003',50,'2010-06-03','小风');insertintoOutboundvalues('12','100005','2010001',30,'2009-04-15','小红');insertintoOutboundvalues('13','100002','2010002',20,'2010-07-05','小风');insertintoOutboundvalues('14','100003','2010003',50,'2009-07-05','小红');insertintoOutboundvalues('15','100000','2010002',40,'2010-07-05','小风');insertintoOutboundvalues('16','100004','2010002',50,'2009-07-05','小风');insertintoOutboundvalues('17','100005','2010002',20,'2008-09-01','小明');insertintoOutboundvalues('18','100001','2010003',30,'2010-09-01','小敏');insertintoOutboundvalues('19','100000','2010002',40,'2010-09-01','小敏');insertintoOutboundvalues('20','100002','2010002',50,'2008-09-01','小红');insertintoOutboundvalues('21','100000','2010002',20,'2010-08-21','小青');insertintoOutboundvalues('22','100003','2010001',30,'2008-09-01','小青');insertintoOutboundvalues('23','100002','2010002',40,'2008-09-01','小兰');insertintoOutboundvalues('24','100003','2010003',50,'2008-12-30','小兰');insertintoOutboundvalues('25','100004','2010002',20,'2009-12-30','小兰');insertintoOutboundvalues('26','100005','2010001',40,'2008-12-08','小红');insertintoOutboundvalues('27','100001','2010003',10,'2010-12-30','小敏');insertintoOutboundvalues('28','100002','2010002',30,'2008-12-08','小明');insertintoOutboundvalues('29','100003','2010003',30,'2009-12-18','小红');insertintoOutboundvalues('30','100004','2010001',40,'2010-09-01','小敏');七、实现按商品名称查询商品的入库情况及目前的库存量。/*方法一*/selectSNo,WName,s.WNo,s.DNo,SAmount,WAmountfromStockass,Wareasw,StockingassiwhereWName='电冰箱'ands.WNo=w.WNoandw.WNo=si.WNoands.DNo=si.DNo/*方法二*/declare@WNamevarchar(32)SET@WName='电冰箱'SELECT*FROMStockingWHEREWNoIN(SELECTWNoFROMWareWHEREWName=@WName);CREATEVIEWLSName(SNo,WName,WNo,DNo,SAmount,WAmount)ASselectSNo,WName,s.WNo,s.DNo,SAmount,WAmountfromStockass,Wareasw,Stockingassiselectdistinctsi.Sno,l.WName,si.SAmount,s.WAmountFROMStockass,LSNameasl,Wareasw,StockingassiWHEREl.WName='电冰箱'andsi.Sno=l.Snoandw.WName=l.WNameands.WNo=si.WNoandsi.WNo=w.WNoandw.WNo=l.WNoandsi.DNo=l.DNoandsi.SAmount=l.SAmountands.WAmount=l.WAmountJ结果消息SNoWNameWNoDNoSArmountWAmount1103电冰箱10000220100012002002108电冰箱10000220100022008403115电冰箱10000220100022008404119电冰箱10000220100022008405121电冰箱1000022010002200840G123电冰箱10000220100032002007128电冰箱1000022010002200840丿八、实现按入库日期査询商品的入库情况及目前的库存量/*方法一复合条件查询*/selectSNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmountfromStockass,StockingwhereSDate='2006-01-0200:00:00'ands.WNo=Stocking.WNoands.DNo=Stocking.DNo/*方法二建立视图查询*/createviewWA_Stocking(SNoWNoDNoSAmountSDateSupplier,WAmountasselectSNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmountfromStockassJOINStockingons.WNo=Stocking.WNoands.DNo=Stocking.DNoandSDate='2006-01-0200:00:00'withcheckoption;/*/*方法三*/相关子查询*/selectSNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmountfromStockass,Stockingwhereexists(select*whereSDate='2006-01-0200:00:00'ands.WNo=Stocking.WNoands.DNo=Stocking.DNo);SNoWeDNqSAmountSDateSupplierWAmaunt二10010000520100012002006-01-0200:00:00小明420210110000520100032002006-01-0200:00:00小兰200310210000120100032002006-01-0200:00:00260斗10310000220100012002006-01-0200:00:00小红200九、实现按商品名称査询商品的出库情况及目前的库存量。/*方法一*/selectONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmountfromStockass,Outbound,WarewhereWName='长城干红'andWare.Wno=s.Wnoands.Wno=Outbound.Wnoands.DNo=Outbound.DNo/*方法二*//*创建视图*/CREATEVIEWW_S_O(ONoWNoDNoOAmountODateBuyersWAmount,WNameASSELECTONo,Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmount,Ware.WNameFROMWare,Stock,OutboundWHEREStock.WNo=Outbound.WNoANDStock.DNo=Outbound.DNoANDWare.WNo=Stock.WnoselectONo,WNo,DNo,OAmount,WName,ODate,Buyers,WAmountfromW_S_OwhereWName='长城干红'/*方法三*/declare@WNamevarchar(32)set@WName='长城干红'select*fromOutboundwhereWNo=(selectWNofromWarewhereWName=@WName);/*方法四*/selectONo,s•WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmountfromStockass,Outbound,Warewhereexists(select*whereWName='长城干红'ands.WNo=Outbound.WNoands.DNo=Outbound.DNoandWare.Wno=s.Wno);J结果消息□MoWNoDNoOArriountWName□DateBuyersWAmount116100004201000250长城干红2009-07-0503:00:0080225100004201000220坂城干红2009-12-3000:00:00水兰80330100004201000140长城干红2010-09-0103:00:0036048100004201000250氏城干红2009-04-1500:00:0080十、实现按出库日期査询商品的出库情况及目前的库存量。/*方法一*/selectONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmountfromStockass,OutboundwhereODate='2010-01-020:00:00'ands.WNo=Outbound.WNoands.DNo=Outbound.DNo/*方法二*//*创建视图*/CREATEVIEWSO(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount)ASSELECTONo,Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmountFROMStock,OutboundWHEREStock.WNo=Outbound.WNoANDStock.DNo=Outbound.DNoselectONo,WNo,DNo,OAmount,ODate,Buyers,WAmountfromSOwhereODate='2010-01-020:00:00'selectONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmountfromStockass,Outboundwhereexists(select*whereODate='2010-01-020:00:00'ands.WNo=Outbound.WNoands.DNo=Outbound.DNo);J结果消息ONoWNoDNoOAmountODateBuyersWAmount11000052010001502010-01-0200:00:00小红420221000052010002302010-01-0200:00:00小红300331000012010003502010-01-0200:00:00小风260441000052010001302010-01-0200:00:00小红420按时间段查询商品库存情况。/*时间在-01-02和-01-02之间的入库出库情况*//*方法一*/selectw.WNameas商品,sum(SAmount)as入库数量fromStockingass,Wareaswwherew.WNo=s.WNoandSDatebetween'2006-01-02'and'2010-01-02'groupbyw.WNameselectw.WNameas商品,sum(OAmount)as出库数量fromOutboundaso,Wareaswwherew.WNo=o.WNoandODatebetween'2006-01-02'and'2010-01-02groupbyw.WName/*方法二*/selectw.WNameas商品,sum(SAmount)as入库数量fromStockingass,Wareaswwherew.WNo=s.WNoandSDatein(selectSDatefromStockingasswhereSDatebetween'2006-01-02'and'2010-01-02')groupbyw.WNameselectw.WNameas商品,sum(OAmount)as出库数量fromOutboundaso,Wareaswwherew.WNo=o.WNoandODatein(selectODatefromOutboundasoand'2010-01-02'whereODatebetweenand'2010-01-02'groupbyw.WName/*方法三*/selectWName,sum(SAmount)SsumfromStocking,Warewhereexists(select*whereSDatebetween'2006-01-02and'2010-01-02'andStocking.WNo=whereSDatebetween'2006-01-02groupbyWNameselectWName,sum(OAmount)OsumfromOutbound,Warewhereexists(select*whereODatebetween'2006-01-02'and'2010-01-02'andOutbound.WNo=Ware.WNo)groupbyWName自结果A消息商品入库数量1笔记本8002长城干红4003电冰■箱9004电视&005毛巾4006台灯400商話岀库数量1笔记本2502怅城干红1203电冰箱1204电视1605毛巾306台灯50十二、实现分别按年、季度和月对入库商品数量的统计。/*方法年：*/selectWName,sum(SAmount)SsumfromStocking,Warewhereyear(SDate)=2005andStocking.WNo=Ware.WNogroupbyWName/*季度：*/selectWName,sum(SAmount)SsumfromStocking,WarewhereSDatebetween'2005-3-10:00:00'and'2005-5-310:00:00'andStocking.WNo=Ware.WNogroupbyWName/*月：*/selectWName,sum(SAmount)SsumfromStocking,Warewheremonth(SDate)=6andStocking.WNo=Ware.WNoandyear(SDate)=2005groupbyWName/*方法二：年：*/createviewStocking_SumNasselectWName,sum(SAmount)SsumfromStockingJOINWareonStocking.WNo=Ware.WNowhereyear(SDate)=2005groupbyWName/*月：*/createviewStocking_SumMasselectWName,sum(SAmount)SsumfromStockingJOINWareonStocking.WNo=Ware.WNowheremonth(SDate)=6andyear(SDate)=2005groupbyWName))groupbyWName/*季度：*/createviewStocking_SumJasselectWName,sum(SAmount)SsumfromStockingJOINWareonStocking.WNo=Ware.WNowhereSDatebetween'2005-3-10:00:00'and'2005-5-310:00:00'groupbyWName/*方法三：年：*/selectWName,sum(SAmount)SsumfromStocking,Warewhereexists(select*whereyear(SDate)=2005andStocking.WNo=Ware.WNo)groupbyWName/*季度：*/selectWName,sum(SAmount)SsumfromStocking,Warewhereexists(select*whereSDatebetween'2005-3-10:00:00'and'2005-5-310:00:00'andStocking.WNo=Ware.WNo)groupbyWName/*月：*/selectWName,sum(SAmount)SsumfromStocking,Warewhereexists(select*wheremonth(SDate)=6andStocking.WNo=Ware.WNoandyear(SDate)=2005WNameSsum1筆记本4002按城干红2003电冰箱6004电视eao5毛巾2006台灯800WNameGsum1电冰箱|2002台灯200WNameSsum1笔记本2002怏城干红2003电冰箱2004毛巾2005台灯200十三、实现分别按年、季度和月对出库商品数量的统计。/*第一种方法：复合条件查询按年：*/selectWName,sum(OAmount)OsumfromOutbound,Warewhereyear(ODate)=2010andOutbound.WNo=Ware.WNogroupbyWName/*按季度：*/selectWName,sum(OAmount)OsumfromOutbound,WarewhereODatebetween'2010-09-010:00:00'and'2010-11-300:00:00'andOutbound.WNo=Ware.WNogroupbyWName/*按月：*/selectWName,sum(OAmount)OsumfromOutbound,Warewheremonth(ODate)=9andOu