数据库课程设计---库存物资管理系统

信息科学与工程学院数据库课程设计题目库存物资管理系统姓名学号班级课程SQLSERVER2005数据库技术任课教师2011年6月20日课程设计任务书及成绩评定题目库存物资管理系统课程设计的任务及要求1、设计任务设计一个库存物资管理系统2、设计要求（1）系统简单实用。（2）利用SQLSERVER2005。（3）用学过的TSQL语言进行管理。课程设计所需的软件、硬件等计算机、SQLSERVER2005软件课程设计进度计划2011年5月5月中旬，进行可行性研究，完成选题2011年5月中旬5月底，根据选题查阅资料、进行概要设计，确定设计方案详细设计，实施设计方案，调试作品2011年6月初，完成课程设计（任务书）的撰写。任课教师评语成绩教师签名日期一、进行需求分析，编写数据字典。1、系统功能要求设计有一个存放商品的仓库，每天都有商品出库和入库。每种商品都有商品编号、商品名称、生产厂家、型号、规格等。入库时必须填写入库单据，单据包括商品名称、生产厂家、型号、规格、入库数量、入库日期、入库仓库号、入库仓库名称、送货人姓名。出库时必须填写出库单据，单据包括仓库号、仓库名称、商品编号、商品名称、型号、规格、出库数量、出库日期、提货人姓名。设置商品库存台帐，商品库存台帐是对仓库中目前库存的所有商品的明细记录，商品库存台帐包括商品编号、商品名称、型号、规格、库存数量、库存日期。每当有商品入库或商品出库时都应该自动修改该台帐，最后一次修改的是现在的库存情况。商品的入库和出库过程通过库存台帐更加清晰条理地显示出仓库中商品的库存数量和库存日期等信息，容易对库存内的商品信息进行查询，增加，修改，删除等操作。该仓库的商品型号统一为DA01DA99格式，规格为CX100CX199格式主要功能商品管理增加商品修改商品，删除商品，浏览商品增加商品实现增加商品的详细资料的功能修改商品实现修改商品资料的功能删除商品实现删除该商品的所有资料的功能浏览商品实现浏览所有商品的功能库存管理实现商品的入库，在商品入库时通过触发器或存储过程同时完成商品库存台帐的更新。实现商品的出库，在商品出库时通过触发器或存储过程同时完成商品库存台帐的更新。实现按商品名称查询商品的入库情况及目前的库存量。实现按入库日期查询商品的入库情况及目前的库存量。实现按商品名称查询商品的出库情况及目前的库存量。实现按出库日期查询商品的出库情况及目前的库存量。按时间段查询商品库存情况。实现分别按年、季度和月对入库商品数量的统计。实现分别按年、季度和月对出库商品数量的统计。2、系统模块设计库存物资管理大体可以分为以下3大模块，如下图所示首先是商品入库模块，该模块主要就是描述把采购回来的商品，分类别的放置到指定的仓库中去，然后是商品出库模块，该模块主要描述从指定的仓库中拿出商品；最后时库存模块，这个模块主要是记录商品的库存数量变化。库存物资管理商品入库模块商品出库模块商品库存模块3、数据字典表表名中文名WARE商品DEPOT仓库STOCK库存STOCKING入库OUTBOUND出库商品WARE字段名数据类型是否可以为空ISPKISFK中文名WNOCHAR10NOTNULLYESNO商品编号WNAMEVARCHAR50NOTNULLNONO商品名称WFACTORYVARCHAR50NOTNULLNONO生产厂家WTYPEVARCHAR20NOTNULLNONO型号SPECVARCHAR20NOTNULLNONO规格仓库DEPOT字段名数据类型是否可以为空ISPKISFK中文名DNOCHAR10NOTNULLYESNO仓库号DNAMEVARCHAR50NOTNULLNONO仓库名称库存STOCK字段名数据类型是否可以为空ISPKISFK中文名WNOCHAR10NOTNULLYESYES商品编号DNOCHAR10NOTNULLYESYES仓库号WAMOUNTINTNOTNULLNONO库存数量入库STOCKING字段名数据类型是否可以为空ISPKISFK中文名SNOCHAR10NOTNULLYESNO入库号WNOCHAR10NOTNULLNOYES商品编号DNOCHAR10NOTNULLNOYES仓库号SAMOUNTINTNOTNULLNONO入库数量SDATESMALLDATETIMENOTNULLNONO入库日期SUPPLIERVARCHAR50NOTNULLNONO送货人姓名出库OUTBOUND字段名数据类型是否可以为空ISPKISFK中文名ONOCHAR10NOTNULLYESNO出库号WNOCHAR10NOTNULLNOYES商品编号DNOCHAR10NOTNULLNOYES仓库号OAMOUNTINTNOTNULLNONO出库数量ODATESMALLDATETIMENOTNULLNONO出库日期BUYERSVARCHAR50NOTNULLNONO提货人姓名二、设计完整的ER图。商品编号型号生产厂家规格商品名称商品仓库号仓库仓库名称商品仓库库存NM商品仓库入库NM商品仓库出库NM库存关系入库关系出库关系分ER图总ER图商品编号型号生产厂家规格商品名称商品仓库号仓库仓库名称入库日期送货人姓名入库数量入库入库号NNNMMM库存库存数量提货人姓名出库日期出库数量出库出库号三、进行数据库的逻辑设计。关系模式如下商品（商品编号、商品名称、生产厂家、型号、规格）商品编号为主键仓库（仓库号、仓库名称）仓库号为主键库存（商品编号，仓库号，库存数量）（商品编号，仓库号）为主键，同时也分别为外键入库（入库号，商品编号，仓库号，入库数量，入库日期，送货人姓名）（入库号）为主键，（商品编号，仓库号）为外键出库（出库号，商品编号，仓库号，出库数量，出库日期，提货人姓名）（出库号）为主键，（商品编号，仓库号）为外键四、完成物理数据库的设计，包括数据库、表、索引、视图、完整性约束的物理设计。创建数据库CREATEDATABASEMATERIAL_DB创建商品表USEMATERIAL_DBCREATETABLEWARE/定义商品表/WNOCHAR10NOTNULLPRIMARYKEY,WNAMEVARCHAR50NOTNULL,WFACTORYVARCHAR50NOTNULL,WTYPEVARCHAR20NOTNULL,SPECVARCHAR20NOTNULLUSEMATERIAL_DB/插入商品信息/INSERTINTOWAREVALUES100000,毛巾,新家园,DA11,CX100INSERTINTOWAREVALUES100001,台灯,新家园,DA35,CX110INSERTINTOWAREVALUES100002,电冰箱,海尔,DA61,CX900INSERTINTOWAREVALUES100003,电视,东芝,DA52,CX901INSERTINTOWAREVALUES100004,长城干红,中粮华夏,DA25,CX108INSERTINTOWAREVALUES100005,笔记本,东芝,DA24,CX781创建仓库表USEMATERIAL_DBCREATETABLEDEPOT/定义仓库表/DNOCHAR10NOTNULLPRIMARYKEY,DNAMEVARCHAR50NOTNULLUSEMATERIAL_DB/插入仓库信息/INSERTINTODEPOTVALUES2010001,胜昌INSERTINTODEPOTVALUES2010002,德隆INSERTINTODEPOTVALUES2010003,豪友创建库存表USEMATERIAL_DBCREATETABLESTOCK/定义库存关系/WNOCHAR10NOTNULL,DNOCHAR10NOTNULL,WDATESMALLDATETIMENOTNULL,WAMOUNTINTNOTNULL,PRIMARYKEYWNO,DNO,FOREIGNKEYWNOREFERENCESWAREWNO,FOREIGNKEYDNOREFERENCESDEPOTDNO创建入库表USEMATERIAL_DBCREATETABLESTOCKING/定义入库关系/SNOCHAR10NOTNULLPRIMARYKEY,WNOCHAR10NOTNULL,DNOCHAR10NOTNULL,FOREIGNKEYWNOREFERENCESWAREWNO,FOREIGNKEYDNOREFERENCESDEPOTDNO,SAMOUNTINTNOTNULL,SDATESMALLDATETIMENOTNULL,SUPPLIERVARCHAR50NOTNULL创建出库表USEMATERIAL_DBCREATETABLEOUTBOUND/定义出库关系/ONOCHAR10NOTNULLPRIMARYKEY,WNOCHAR10NOTNULL,FOREIGNKEYWNOREFERENCESWAREWNO,DNOCHAR10NOTNULL,FOREIGNKEYDNOREFERENCESDEPOTDNO,OAMOUNTINTNOTNULL,ODATESMALLDATETIMENOTNULL,BUYERSVARCHAR50NOTNULL完整约束的说明商品WARE的WNO商品编号要求在100000999999之间，WNAME不能取空；仓库DEPOT的DNO仓库编号要求在20100012010999之间，DNAME不能取空；该仓库的商品型号统一为DA01DA99格式，规格为CX100CX199格式。USEMATERIAL_DB/定义表WARE唯一性的约束条件/ALTERTABLEWAREADDUNIQUEWNAME,WFACTORY,WTYPE,SPECALTERTABLEWARE/定义WTYPE的完整性约束条件商品型号统一为DA01DA99格式/ADDCONSTRAINTC2CHECKWTYPELIKEDA0919ALTERTABLEWARE/定义SPEC的完整性约束条件规格为CX100CX199格式/ADDCONSTRAINTC3CHECKSPECLIKECX190909USEMATERIAL_DBALTERTABLEWARE/添加对表WARE的WNO属性的完整性约束，要求在9999999999之间/ADDCONSTRAINTC4CHECKWNOLIKE19090909090909090909ALTERTABLEDEPOTADDCONSTRAINTC5CHECKDNOLIKE2010090909USEMATERIAL_DBALTERTABLEWAREDROPCONSTRAINTC4USEMATERIAL_DBALTERTABLEWARE/添加对表WARE的WNO属性的完整性约束，要求在999999之间/ADDCONSTRAINTC4CHECKWNOLIKE190909090909创建视图通过WLST（提取“物料视图”每个字的头字母）视图来浏览库存中相同编号商品的信息，其中包括商品编号WNO、商品名WNAME、商品厂家WFACTORY、商品型号WTYPE、商品规格SPEC、商品数量SNUM、CREATEVIEWVWNAME商品编号,商品名称,库存数量视图ASSELECTSTOCKWNO,WAREWNAME,SUMSTOCKWAMOUNTFROMWARE,STOCKWHERESTOCKWNOWAREWNOGROUPBYSTOCKWNO,WAREWNAME五、实现商品的入库，在商品入库时通过触发器或存储过程同时完成商品库存台帐的更新。入库表的触发器CREATETRIGGERSTOCKING_INTOONSTOCKING这是入库表的触发器AFTERINSERTASBEGINDECLAREACHAR10,BCHAR10,DINTSELECTAIDNO,BIWNO,DISAMOUNTFROMINSERTEDASIIFSELECTSTOCKWNOFROMSTOCKWHERESTOCKWNOBANDSTOCKDNOAISNOTNULLBEGINUPDATESTOCKSETSTOCKWAMOUNTSTOCKWAMOUNTDWHERESTOCKWNOBANDSTOCKDNOARETURNENDINSERTINTOSTOCKVALUESB,A,DENDINSERTINTOSTOCKINGVALUES100,100005,2010001,200,20060102,小明INSERTINTOSTOCKINGVALUES101,100005,2010003,200,20060102,小兰INSERTINTOSTOCKINGVALUES102,100001,2010003,200,20060102,小东INSERTINTOSTOCKINGVALUES103,100002,2010001,200,20060102,小红INSERTINTOSTOCKINGVALUES104,100003,2010001,200,20070114,小风INSERTINTOSTOCKINGVALUES105,100005,2010002,200,20060125,小明INSERTINTOSTOCKINGVALUES106,100000,2010002,200,20060202,小明INSERTINTOSTOCKINGVALUES107,100001,2010001,200,20050302,小东INSERTINTOSTOCKINGVALUES108,100002,2010002,200,20070402,小红INSERTINTOSTOCKINGVALUES109,100003,2010003,200,20060402,小风INSERTINTOSTOCKINGVALUES110,100004,2010001,200,20060609,小敏INSERTINTOSTOCKINGVALUES111,100004,2010002,200,20050602,小风INSERTINTOSTOCKINGVALUES112,100005,2010001,200,20050602,小青INSERTINTOSTOCKINGVALUES113,100000,2010003,200,20050602,小明INSERTINTOSTOCKINGVALUES114,100001,2010001,200,20050602,小东INSERTINTOSTOCKINGVALUES115,100002,2010002,200,20070702,小红INSERTINTOSTOCKINGVALUES116,100003,2010003,200,20060802,小风INSERTINTOSTOCKINGVALUES117,100000,2010001,200,20060802,小明INSERTINTOSTOCKINGVALUES118,100001,2010001,200,20050904,小东INSERTINTOSTOCKINGVALUES119,100002,2010002,200,20050602,小红INSERTINTOSTOCKINGVALUES120,100003,2010003,200,20050911,小风INSERTINTOSTOCKINGVALUES121,100002,2010002,200,20050901,小明INSERTINTOSTOCKINGVALUES122,100003,2010002,200,20050708,小敏INSERTINTOSTOCKINGVALUES123,100002,2010003,200,20050411,小敏INSERTINTOSTOCKINGVALUES124,100001,2010001,200,20070708,小红INSERTINTOSTOCKINGVALUES125,100001,2010003,200,20050821,小青INSERTINTOSTOCKINGVALUES126,100005,2010001,200,20050708,小青INSERTINTOSTOCKINGVALUES127,100003,2010003,200,20050901,小兰INSERTINTOSTOCKINGVALUES128,100002,2010002,200,20070708,小兰INSERTINTOSTOCKINGVALUES129,100004,2010001,200,20070708,小兰INSERTINTOSTOCKINGVALUES130,100005,2010002,200,20070708,小红六、实现商品的出库，在商品出库时通过触发器或存储过程同时完成商品库存台帐的更新。出库表触发器CREATETRIGGEROUTBOUND_DONOUTBOUND这是出库表的触发器AFTERINSERTASBEGINDECLAREACHAR10,BCHAR10,DINTSELECTAIDNO,BIWNO,DIOAMOUNTFROMINSERTEDASIIFSELECTSWNOFROMSTOCKASSWHERESDNOAANDSWNOBISNOTNULLBEGINIFSELECTSWAMOUNTFROMSTOCKASS,INSERTEDWHERESWAMOUNTINSERTEDOAMOUNTANDSWNOINSERTEDWNOANDSDNOINSERTEDDNOISNOTNULLBEGINUPDATESTOCKSETSTOCKWAMOUNTSTOCKWAMOUNTDWHERESTOCKDNOAANDSTOCKWNOBENDELSEBEGINPRINT库存量不够ROLLBACKENDENDELSEBEGINPRINT库存中没有这种产品ROLLBACKENDENDINSERTINTOOUTBOUNDVALUES1,100005,2010001,50,20100102,小红INSERTINTOOUTBOUNDVALUES2,100005,2010002,30,20100102,小红INSERTINTOOUTBOUNDVALUES3,100001,2010003,50,20100102,小风INSERTINTOOUTBOUNDVALUES4,100005,2010001,30,20100102,小红INSERTINTOOUTBOUNDVALUES5,100002,2010002,20,20100208,小风INSERTINTOOUTBOUNDVALUES6,100003,2010003,50,20100309,小红INSERTINTOOUTBOUNDVALUES7,100000,2010002,30,20090415,小风INSERTINTOOUTBOUNDVALUES8,100004,2010002,50,20090415,小风INSERTINTOOUTBOUNDVALUES9,100005,2010001,30,20100602,小红INSERTINTOOUTBOUNDVALUES10,100005,2010002,50,20090415,小红INSERTINTOOUTBOUNDVALUES11,100001,2010003,50,20100603,小风INSERTINTOOUTBOUNDVALUES12,100005,2010001,30,20090415,小红INSERTINTOOUTBOUNDVALUES13,100002,2010002,20,20100705,小风INSERTINTOOUTBOUNDVALUES14,100003,2010003,50,20090705,小红INSERTINTOOUTBOUNDVALUES15,100000,2010002,40,20100705,小风INSERTINTOOUTBOUNDVALUES16,100004,2010002,50,20090705,小风INSERTINTOOUTBOUNDVALUES17,100005,2010002,20,20080901,小明INSERTINTOOUTBOUNDVALUES18,100001,2010003,30,20100901,小敏INSERTINTOOUTBOUNDVALUES19,100000,2010002,40,20100901,小敏INSERTINTOOUTBOUNDVALUES20,100002,2010002,50,20080901,小红INSERTINTOOUTBOUNDVALUES21,100000,2010002,20,20100821,小青INSERTINTOOUTBOUNDVALUES22,100003,2010001,30,20080901,小青INSERTINTOOUTBOUNDVALUES23,100002,2010002,40,20080901,小兰INSERTINTOOUTBOUNDVALUES24,100003,2010003,50,20081230,小兰INSERTINTOOUTBOUNDVALUES25,100004,2010002,20,20091230,小兰INSERTINTOOUTBOUNDVALUES26,100005,2010001,40,20081208,小红INSERTINTOOUTBOUNDVALUES27,100001,2010003,10,20101230,小敏INSERTINTOOUTBOUNDVALUES28,100002,2010002,30,20081208,小明INSERTINTOOUTBOUNDVALUES29,100003,2010003,30,20091218,小红INSERTINTOOUTBOUNDVALUES30,100004,2010001,40,20100901,小敏七、实现按商品名称查询商品的入库情况及目前的库存量。/方法一/SELECTSNO,WNAME,SWNO,SDNO,SAMOUNT,WAMOUNTFROMSTOCKASS,WAREASW,STOCKINGASSIWHEREWNAME电冰箱ANDSWNOWWNOANDWWNOSIWNOANDSDNOSIDNO/方法二/DECLAREWNAMEVARCHAR32SETWNAME电冰箱SELECTFROMSTOCKINGWHEREWNOINSELECTWNOFROMWAREWHEREWNAMEWNAME/方法三/CREATEVIEWLSNAMESNO,WNAME,WNO,DNO,SAMOUNT,WAMOUNTASSELECTSNO,WNAME,SWNO,SDNO,SAMOUNT,WAMOUNTFROMSTOCKASS,WAREASW,STOCKINGASSISELECTDISTINCTSISNO,LWNAME,SISAMOUNT,SWAMOUNTFROMSTOCKASS,LSNAMEASL,WAREASW,STOCKINGASSIWHERELWNAME电冰箱ANDSISNOLSNOANDWWNAMELWNAMEANDSWNOSIWNOANDSIWNOWWNOANDWWNOLWNOANDSIDNOLDNOANDSISAMOUNTLSAMOUNTANDSWAMOUNTLWAMOUNT八、实现按入库日期查询商品的入库情况及目前的库存量。/方法一复合条件查询/SELECTSNO,SWNO,SDNO,SAMOUNT,SDATE,SUPPLIER,WAMOUNTFROMSTOCKASS,STOCKINGWHERESDATE20060102000000ANDSWNOSTOCKINGWNOANDSDNOSTOCKINGDNO/方法二建立视图查询/CREATEVIEWWA_STOCKINGSNO,WNO,DNO,SAMOUNT,SDATE,SUPPLIER,WAMOUNTASSELECTSNO,SWNO,SDNO,SAMOUNT,SDATE,SUPPLIER,WAMOUNTFROMSTOCKASSJOINSTOCKINGONSWNOSTOCKINGWNOANDSDNOSTOCKINGDNOANDSDATE20060102000000WITHCHECKOPTION/方法三相关子查询/SELECTSNO,SWNO,SDNO,SAMOUNT,SDATE,SUPPLIER,WAMOUNTFROMSTOCKASS,STOCKINGWHEREEXISTSSELECTWHERESDATE20060102000000ANDSWNOSTOCKINGWNOANDSDNOSTOCKINGDNO九、实现按商品名称查询商品的出库情况及目前的库存量。/方法一/SELECTONO,SWNO,SDNO,OAMOUNT,WNAME,ODATE,BUYERS,WAMOUNTFROMSTOCKASS,OUTBOUND,WAREWHEREWNAME长城干红ANDWAREWNOSWNOANDSWNOOUTBOUNDWNOANDSDNOOUTBOUNDDNO/方法二/创建视图/CREATEVIEWW_S_OONO,WNO,DNO,OAMOUNT,ODATE,BUYERS,WAMOUNT,WNAMEASSELECTONO,STOCKWNO,STOCKDNO,OAMOUNT,ODATE,BUYERS,WAMOUNT,WAREWNAMEFROMWARE,STOCK,OUTBOUNDWHERESTOCKWNOOUTBOUNDWNOANDSTOCKDNOOUTBOUNDDNOANDWAREWNOSTOCKWNOSELECTONO,WNO,DNO,OAMOUNT,WNAME,ODATE,BUYERS,WAMOUNTFROMW_S_OWHEREWNAME长城干红/方法三/DECLAREWNAMEVARCHAR32SETWNAME长城干红SELECTFROMOUTBOUNDWHEREWNOSELECTWNOFROMWAREWHEREWNAMEWNAME/方法四/SELECTONO,SWNO,SDNO,OAMOUNT,WNAME,ODATE,BUYERS,WAMOUNTFROMSTOCKASS,OUTBOUND,WAREWHEREEXISTSSELECTWHEREWNAME长城干红ANDSWNOOUTBOUNDWNOANDSDNOOUTBOUNDDNOANDWAREWNOSWNO十、实现按出库日期查询商品的出库情况及目前的库存量。/方法一/SELECTONO,SWNO,SDNO,OAMOUNT,ODATE,BUYERS,WAMOUNTFROMSTOCKASS,OUTBOUNDWHEREODATE2010010200000ANDSWNOOUTBOUNDWNOANDSDNOOUTBOUNDDNO/方法二/创建视图/CREATEVIEWSOONO,WNO,DNO,OAMOUNT,ODATE,BUYERS,WAMOUNTASSELECTONO,STOCKWNO,STOCKDNO,OAMOUNT,ODATE,BUYERS,WAMOUNTFROMSTOCK,OUTBOUNDWHERESTOCKWNOOUTBOUNDWNOANDSTOCKDNOOUTBOUNDDNOSELECTONO,WNO,DNO,OAMOUNT,ODATE,BUYERS,WAMOUNTFROMSOWHEREODATE2010010200000/方法三/SELECTONO,SWNO,SDNO,OAMOUNT,ODATE,BUYERS,WAMOUNTFROMSTOCKASS,OUTBOUNDWHEREEXISTSSELECTWHEREODATE2010010200000ANDSWNOOUTBOUNDWNOANDSDNOOUTBOUNDDNO十一、按时间段查询商品库存情况。/时间在0102和0102之间的入库出库情况/方法一/SELECTWWNAMEAS商品,SUMSAMOUNTAS入库数量FROMSTOCKINGASS,WAREASWWHEREWWNOSWNOANDSDATEBETWEEN20060102AND20100102GROUPBYWWNAMESELECTWWNAMEAS商品,SUMOAMOUNTAS出库数量FROMOUTBOUNDASO,WAREASWWHEREWWNOOWNOANDODATEBETWEEN20060102AND20100102GROUPBYWWNAME/方法二/SELECTWWNAMEAS商品,SUMSAMOUNTAS入库数量FROMSTOCKINGASS,WAREASWWHEREWWNOSWNOANDSDATEINSELECTSDATEFROMSTOCKINGASSWHERESDATEBETWEEN20060102AND20100102GROUPBYWWNAMESELECTWWNAMEAS商品,SUMOAMOUNTAS出库数量FROMOUTBOUNDASO,WAREASWWHEREWWNOOWNOANDODATEINSELECTODATEFROMOUTBOUNDASOWHEREODATEBETWEEN20060102AND20100102GROUPBYWWNAME/方法三/SELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKING,WAREWHEREEXISTSSELECTWHERESDATEBETWEEN20060102AND20100102ANDSTOCKINGWNOWAREWNOGROUPBYWNAMESELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUND,WAREWHEREEXISTSSELECTWHEREODATEBETWEEN20060102AND20100102ANDOUTBOUNDWNOWAREWNOGROUPBYWNAME十二、实现分别按年、季度和月对入库商品数量的统计。/方法一年/SELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKING,WAREWHEREYEARSDATE2005ANDSTOCKINGWNOWAREWNOGROUPBYWNAME/季度/SELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKING,WAREWHERESDATEBETWEEN20053100000AND200553100000ANDSTOCKINGWNOWAREWNOGROUPBYWNAME/月/SELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKING,WAREWHEREMONTHSDATE6ANDSTOCKINGWNOWAREWNOANDYEARSDATE2005GROUPBYWNAME/方法二年/CREATEVIEWSTOCKING_SUMNASSELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKINGJOINWAREONSTOCKINGWNOWAREWNOWHEREYEARSDATE2005GROUPBYWNAME/月/CREATEVIEWSTOCKING_SUMMASSELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKINGJOINWAREONSTOCKINGWNOWAREWNOWHEREMONTHSDATE6ANDYEARSDATE2005GROUPBYWNAME/季度/CREATEVIEWSTOCKING_SUMJASSELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKINGJOINWAREONSTOCKINGWNOWAREWNOWHERESDATEBETWEEN20053100000AND200553100000GROUPBYWNAME/方法三年/SELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKING,WAREWHEREEXISTSSELECTWHEREYEARSDATE2005ANDSTOCKINGWNOWAREWNOGROUPBYWNAME/季度/SELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKING,WAREWHEREEXISTSSELECTWHERESDATEBETWEEN20053100000AND200553100000ANDSTOCKINGWNOWAREWNOGROUPBYWNAME/月/SELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKING,WAREWHEREEXISTSSELECTWHEREMONTHSDATE6ANDSTOCKINGWNOWAREWNOANDYEARSDATE2005GROUPBYWNAME十三、实现分别按年、季度和月对出库商品数量的统计。/第一种方法复合条件查询按年/SELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUND,WAREWHEREYEARODATE2010ANDOUTBOUNDWNOWAREWNOGROUPBYWNAME/按季度/SELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUND,WAREWHEREODATEBETWEEN2010090100000AND2010113000000ANDOUTBOUNDWNOWAREWNOGROUPBYWNAME/按月/SELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUND,WAREWHEREMONTHODATE9ANDOUTBOUNDWNOWAREWNOANDYEARODATE2010GROUPBYWNAME/第二种方法视图查询按年/CREATEVIEWYEAR_OUTB商品,出库数量ASSELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUNDASOJOINWAREASWONOWNOWWNOWHEREYEARODATE2010GROUPBYWNAMEWITHCHECKOPTION/按季度/CREATEVIEWJIDU_OUTB商品,出库数量ASSELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUNDASOJOINWAREASWONOWNOWWNOWHEREODATEBETWEEN2010090100000AND2010113000000GROUPBYWNAMEWITHCHECKOPTION/按月/CREATEVIEWMONTH_OUTB商品,出库数量ASSELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUNDASOJOINWAREASWONOWNOWWNOWHEREMONTHODATE9ANDYEARODATE2010GROUPBYWNAMEWITHCHECKOPTION/方法三相关子查询/按年/SELECTWNAMEAS商品,SUMOAMOUNTAS出库数量FROMOUTBOUND,WAREWHEREEXISTSSELECTWHEREYEARODATE2010ANDOUTBOUNDWNOWAREWNOGROUPBYWNAME/按季度/SELECTWNAMEAS商品,SUMOAMOUNTAS出库数量FROMOUTBOUND,WAREWHEREEXISTSSELECTWHEREODATEBETWEEN200903100000AND2009053000000ANDOUTBOUNDWNOWAREWNOGROUPBYWNAME/按月/SELECTWNAMEAS商品,SUMOAMOUNTAS出库数量FROMOUTBOUND,WAREWHEREEXISTSSELECTWHEREMONTHODATE9ANDOUTBOUNDWNOWAREWNOANDYEARODATE2010GROUPBYWNAME心得体会通过这个库存物料管理的数据库，我学到了很多东西，首先是我必须学会去查询有关物料管理这个系统有关的组成部分，和相关的需求，只有弄懂了这些才能同组员一起讨论得出一个比较合理的结果。需求分析是最重要的一步，下面的逻辑分析、关系表和表之间的关系都要在这个基础上来设计的，但是里面由于时间的关系，不能做到很完善，包括ER图的构思，里面我也说了一些我自己的看法，库存不能够成为关联，应该是一个关系，像仓库、商品一样才对，还有其实没有数据流通过库存的，它是由触发而更改的，所以不存在由商品库存仓库的关系连接。设计表的属性，和其他同学一起讨论了一些问题，其中包括商品属性要用到多少个字符，那些需要用到时间属性，是要用DATETIME还是SMALLDATETIME因为需要细到分钟，所以决定用SMALLDATETIME，还有后来插入数据时SMALLDATETIME插入的格式写错了，也是通过上面一步一步搜索出来的。其实，这个数据库的分析做得不是很完善，所以实现起来不是很难，但最关键的东西是我们学会了合作，学会了在学习中探讨，学会通过一些手段去查找自己不会的东西，这些才是真正宝贵的。内部资料仅供参考管理员客户管理服务反馈管理业务管理信贷信息管理客户信息表服务反馈表业务、合同信息表信贷信息表分析结果维护维护分析提出依据哈尔滨银行客户关系管理系统数据库全部信息内容提交反馈提交反馈9JWKFFWVGTYMJG6ACZ7HDQ8KQQFHVZFEDSWSYXTYQA9WKXFYEQDJSXUYUP2KNXPRWXMAUE9AQGN8GK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXG89AMUE9AQGN8XPR849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z8VGTYMJG6ACZ7HDQ8KQQFHVZFEDSWSYXTYQA9WKXFYEQDJSXUYUP2KNXPRWXMAUE9AQGN8XPR849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMUE9AQGN8XPR849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVU3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z8