信息科學(xué)與工程學(xué)院數(shù)據(jù)庫課程設(shè)計(jì)題目庫存物資管理系統(tǒng)姓名學(xué)號(hào)班級(jí)課程SQLSERVER2005數(shù)據(jù)庫技術(shù)任課教師2011年6月20日課程設(shè)計(jì)任務(wù)書及成績?cè)u(píng)定題目庫存物資管理系統(tǒng)課程設(shè)計(jì)的任務(wù)及要求1、設(shè)計(jì)任務(wù)設(shè)計(jì)一個(gè)庫存物資管理系統(tǒng)2、設(shè)計(jì)要求（1）系統(tǒng)簡單實(shí)用。（2）利用SQLSERVER2005。（3）用學(xué)過的TSQL語言進(jìn)行管理。課程設(shè)計(jì)所需的軟件、硬件等計(jì)算機(jī)、SQLSERVER2005軟件課程設(shè)計(jì)進(jìn)度計(jì)劃2011年5月5月中旬，進(jìn)行可行性研究，完成選題2011年5月中旬5月底，根據(jù)選題查閱資料、進(jìn)行概要設(shè)計(jì)，確定設(shè)計(jì)方案詳細(xì)設(shè)計(jì)，實(shí)施設(shè)計(jì)方案，調(diào)試作品2011年6月初，完成課程設(shè)計(jì)（任務(wù)書）的撰寫。任課教師評(píng)語成績教師簽名日期一、進(jìn)行需求分析，編寫數(shù)據(jù)字典。1、系統(tǒng)功能要求設(shè)計(jì)有一個(gè)存放商品的倉庫，每天都有商品出庫和入庫。每種商品都有商品編號(hào)、商品名稱、生產(chǎn)廠家、型號(hào)、規(guī)格等。入庫時(shí)必須填寫入庫單據(jù)，單據(jù)包括商品名稱、生產(chǎn)廠家、型號(hào)、規(guī)格、入庫數(shù)量、入庫日期、入庫倉庫號(hào)、入庫倉庫名稱、送貨人姓名。出庫時(shí)必須填寫出庫單據(jù)，單據(jù)包括倉庫號(hào)、倉庫名稱、商品編號(hào)、商品名稱、型號(hào)、規(guī)格、出庫數(shù)量、出庫日期、提貨人姓名。設(shè)置商品庫存臺(tái)帳，商品庫存臺(tái)帳是對(duì)倉庫中目前庫存的所有商品的明細(xì)記錄，商品庫存臺(tái)帳包括商品編號(hào)、商品名稱、型號(hào)、規(guī)格、庫存數(shù)量、庫存日期。每當(dāng)有商品入庫或商品出庫時(shí)都應(yīng)該自動(dòng)修改該臺(tái)帳，最后一次修改的是現(xiàn)在的庫存情況。商品的入庫和出庫過程通過庫存臺(tái)帳更加清晰條理地顯示出倉庫中商品的庫存數(shù)量和庫存日期等信息，容易對(duì)庫存內(nèi)的商品信息進(jìn)行查詢，增加，修改，刪除等操作。該倉庫的商品型號(hào)統(tǒng)一為DA01DA99格式，規(guī)格為CX100CX199格式主要功能商品管理增加商品修改商品，刪除商品，瀏覽商品增加商品實(shí)現(xiàn)增加商品的詳細(xì)資料的功能修改商品實(shí)現(xiàn)修改商品資料的功能刪除商品實(shí)現(xiàn)刪除該商品的所有資料的功能瀏覽商品實(shí)現(xiàn)瀏覽所有商品的功能庫存管理實(shí)現(xiàn)商品的入庫，在商品入庫時(shí)通過觸發(fā)器或存儲(chǔ)過程同時(shí)完成商品庫存臺(tái)帳的更新。實(shí)現(xiàn)商品的出庫，在商品出庫時(shí)通過觸發(fā)器或存儲(chǔ)過程同時(shí)完成商品庫存臺(tái)帳的更新。實(shí)現(xiàn)按商品名稱查詢商品的入庫情況及目前的庫存量。實(shí)現(xiàn)按入庫日期查詢商品的入庫情況及目前的庫存量。實(shí)現(xiàn)按商品名稱查詢商品的出庫情況及目前的庫存量。實(shí)現(xiàn)按出庫日期查詢商品的出庫情況及目前的庫存量。按時(shí)間段查詢商品庫存情況。實(shí)現(xiàn)分別按年、季度和月對(duì)入庫商品數(shù)量的統(tǒng)計(jì)。實(shí)現(xiàn)分別按年、季度和月對(duì)出庫商品數(shù)量的統(tǒng)計(jì)。2、系統(tǒng)模塊設(shè)計(jì)庫存物資管理大體可以分為以下3大模塊，如下圖所示首先是商品入庫模塊，該模塊主要就是描述把采購回來的商品，分類別的放置到指定的倉庫中去，然后是商品出庫模塊，該模塊主要描述從指定的倉庫中拿出商品；最后時(shí)庫存模塊，這個(gè)模塊主要是記錄商品的庫存數(shù)量變化。庫存物資管理商品入庫模塊商品出庫模塊商品庫存模塊3、數(shù)據(jù)字典表表名中文名WARE商品DEPOT倉庫STOCK庫存STOCKING入庫OUTBOUND出庫商品WARE字段名數(shù)據(jù)類型是否可以為空ISPKISFK中文名WNOCHAR10NOTNULLYESNO商品編號(hào)WNAMEVARCHAR50NOTNULLNONO商品名稱WFACTORYVARCHAR50NOTNULLNONO生產(chǎn)廠家WTYPEVARCHAR20NOTNULLNONO型號(hào)SPECVARCHAR20NOTNULLNONO規(guī)格倉庫DEPOT字段名數(shù)據(jù)類型是否可以為空ISPKISFK中文名DNOCHAR10NOTNULLYESNO倉庫號(hào)DNAMEVARCHAR50NOTNULLNONO倉庫名稱庫存STOCK字段名數(shù)據(jù)類型是否可以為空ISPKISFK中文名WNOCHAR10NOTNULLYESYES商品編號(hào)DNOCHAR10NOTNULLYESYES倉庫號(hào)WAMOUNTINTNOTNULLNONO庫存數(shù)量入庫STOCKING字段名數(shù)據(jù)類型是否可以為空ISPKISFK中文名SNOCHAR10NOTNULLYESNO入庫號(hào)WNOCHAR10NOTNULLNOYES商品編號(hào)DNOCHAR10NOTNULLNOYES倉庫號(hào)SAMOUNTINTNOTNULLNONO入庫數(shù)量SDATESMALLDATETIMENOTNULLNONO入庫日期SUPPLIERVARCHAR50NOTNULLNONO送貨人姓名出庫OUTBOUND字段名數(shù)據(jù)類型是否可以為空ISPKISFK中文名ONOCHAR10NOTNULLYESNO出庫號(hào)WNOCHAR10NOTNULLNOYES商品編號(hào)DNOCHAR10NOTNULLNOYES倉庫號(hào)OAMOUNTINTNOTNULLNONO出庫數(shù)量ODATESMALLDATETIMENOTNULLNONO出庫日期BUYERSVARCHAR50NOTNULLNONO提貨人姓名二、設(shè)計(jì)完整的ER圖。商品編號(hào)型號(hào)生產(chǎn)廠家規(guī)格商品名稱商品倉庫號(hào)倉庫倉庫名稱商品倉庫庫存NM商品倉庫入庫NM商品倉庫出庫NM庫存關(guān)系入庫關(guān)系出庫關(guān)系分ER圖總ER圖商品編號(hào)型號(hào)生產(chǎn)廠家規(guī)格商品名稱商品倉庫號(hào)倉庫倉庫名稱入庫日期送貨人姓名入庫數(shù)量入庫入庫號(hào)NNNMMM庫存庫存數(shù)量提貨人姓名出庫日期出庫數(shù)量出庫出庫號(hào)三、進(jìn)行數(shù)據(jù)庫的邏輯設(shè)計(jì)。關(guān)系模式如下商品（商品編號(hào)、商品名稱、生產(chǎn)廠家、型號(hào)、規(guī)格）商品編號(hào)為主鍵倉庫（倉庫號(hào)、倉庫名稱）倉庫號(hào)為主鍵庫存（商品編號(hào)，倉庫號(hào)，庫存數(shù)量）（商品編號(hào)，倉庫號(hào)）為主鍵，同時(shí)也分別為外鍵入庫（入庫號(hào)，商品編號(hào)，倉庫號(hào)，入庫數(shù)量，入庫日期，送貨人姓名）（入庫號(hào)）為主鍵，（商品編號(hào)，倉庫號(hào)）為外鍵出庫（出庫號(hào)，商品編號(hào)，倉庫號(hào)，出庫數(shù)量，出庫日期，提貨人姓名）（出庫號(hào)）為主鍵，（商品編號(hào)，倉庫號(hào)）為外鍵四、完成物理數(shù)據(jù)庫的設(shè)計(jì)，包括數(shù)據(jù)庫、表、索引、視圖、完整性約束的物理設(shè)計(jì)。創(chuàng)建數(shù)據(jù)庫CREATEDATABASEMATERIAL_DB創(chuàng)建商品表USEMATERIAL_DBCREATETABLEWARE/定義商品表/WNOCHAR10NOTNULLPRIMARYKEY,WNAMEVARCHAR50NOTNULL,WFACTORYVARCHAR50NOTNULL,WTYPEVARCHAR20NOTNULL,SPECVARCHAR20NOTNULLUSEMATERIAL_DB/插入商品信息/INSERTINTOWAREVALUES100000,毛巾,新家園,DA11,CX100INSERTINTOWAREVALUES100001,臺(tái)燈,新家園,DA35,CX110INSERTINTOWAREVALUES100002,電冰箱,海爾,DA61,CX900INSERTINTOWAREVALUES100003,電視,東芝,DA52,CX901INSERTINTOWAREVALUES100004,長城干紅,中糧華夏,DA25,CX108INSERTINTOWAREVALUES100005,筆記本,東芝,DA24,CX781創(chuàng)建倉庫表USEMATERIAL_DBCREATETABLEDEPOT/定義倉庫表/DNOCHAR10NOTNULLPRIMARYKEY,DNAMEVARCHAR50NOTNULLUSEMATERIAL_DB/插入倉庫信息/INSERTINTODEPOTVALUES2010001,勝昌INSERTINTODEPOTVALUES2010002,德隆INSERTINTODEPOTVALUES2010003,豪友創(chuàng)建庫存表USEMATERIAL_DBCREATETABLESTOCK/定義庫存關(guān)系/WNOCHAR10NOTNULL,DNOCHAR10NOTNULL,WDATESMALLDATETIMENOTNULL,WAMOUNTINTNOTNULL,PRIMARYKEYWNO,DNO,FOREIGNKEYWNOREFERENCESWAREWNO,FOREIGNKEYDNOREFERENCESDEPOTDNO創(chuàng)建入庫表USEMATERIAL_DBCREATETABLESTOCKING/定義入庫關(guān)系/SNOCHAR10NOTNULLPRIMARYKEY,WNOCHAR10NOTNULL,DNOCHAR10NOTNULL,FOREIGNKEYWNOREFERENCESWAREWNO,FOREIGNKEYDNOREFERENCESDEPOTDNO,SAMOUNTINTNOTNULL,SDATESMALLDATETIMENOTNULL,SUPPLIERVARCHAR50NOTNULL創(chuàng)建出庫表USEMATERIAL_DBCREATETABLEOUTBOUND/定義出庫關(guān)系/ONOCHAR10NOTNULLPRIMARYKEY,WNOCHAR10NOTNULL,FOREIGNKEYWNOREFERENCESWAREWNO,DNOCHAR10NOTNULL,FOREIGNKEYDNOREFERENCESDEPOTDNO,OAMOUNTINTNOTNULL,ODATESMALLDATETIMENOTNULL,BUYERSVARCHAR50NOTNULL完整約束的說明商品WARE的WNO商品編號(hào)要求在100000999999之間，WNAME不能取空；倉庫DEPOT的DNO倉庫編號(hào)要求在20100012010999之間，DNAME不能取空；該倉庫的商品型號(hào)統(tǒng)一為DA01DA99格式，規(guī)格為CX100CX199格式。USEMATERIAL_DB/定義表WARE唯一性的約束條件/ALTERTABLEWAREADDUNIQUEWNAME,WFACTORY,WTYPE,SPECALTERTABLEWARE/定義WTYPE的完整性約束條件商品型號(hào)統(tǒng)一為DA01DA99格式/ADDCONSTRAINTC2CHECKWTYPELIKEDA0919ALTERTABLEWARE/定義SPEC的完整性約束條件規(guī)格為CX100CX199格式/ADDCONSTRAINTC3CHECKSPECLIKECX190909USEMATERIAL_DBALTERTABLEWARE/添加對(duì)表WARE的WNO屬性的完整性約束，要求在9999999999之間/ADDCONSTRAINTC4CHECKWNOLIKE19090909090909090909ALTERTABLEDEPOTADDCONSTRAINTC5CHECKDNOLIKE2010090909USEMATERIAL_DBALTERTABLEWAREDROPCONSTRAINTC4USEMATERIAL_DBALTERTABLEWARE/添加對(duì)表WARE的WNO屬性的完整性約束，要求在999999之間/ADDCONSTRAINTC4CHECKWNOLIKE190909090909創(chuàng)建視圖通過WLST（提取“物料視圖”每個(gè)字的頭字母）視圖來瀏覽庫存中相同編號(hào)商品的信息，其中包括商品編號(hào)WNO、商品名WNAME、商品廠家WFACTORY、商品型號(hào)WTYPE、商品規(guī)格SPEC、商品數(shù)量SNUM、CREATEVIEWVWNAME商品編號(hào),商品名稱,庫存數(shù)量視圖ASSELECTSTOCKWNO,WAREWNAME,SUMSTOCKWAMOUNTFROMWARE,STOCKWHERESTOCKWNOWAREWNOGROUPBYSTOCKWNO,WAREWNAME五、實(shí)現(xiàn)商品的入庫，在商品入庫時(shí)通過觸發(fā)器或存儲(chǔ)過程同時(shí)完成商品庫存臺(tái)帳的更新。入庫表的觸發(fā)器CREATETRIGGERSTOCKING_INTOONSTOCKING這是入庫表的觸發(fā)器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,小風(fēng)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,小風(fēng)INSERTINTOSTOCKINGVALUES110,100004,2010001,200,20060609,小敏INSERTINTOSTOCKINGVALUES111,100004,2010002,200,20050602,小風(fēng)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,小風(fēng)INSERTINTOSTOCKINGVALUES117,100000,2010001,200,20060802,小明INSERTINTOSTOCKINGVALUES118,100001,2010001,200,20050904,小東INSERTINTOSTOCKINGVALUES119,100002,2010002,200,20050602,小紅INSERTINTOSTOCKINGVALUES120,100003,2010003,200,20050911,小風(fēng)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,小紅六、實(shí)現(xiàn)商品的出庫，在商品出庫時(shí)通過觸發(fā)器或存儲(chǔ)過程同時(shí)完成商品庫存臺(tái)帳的更新。出庫表觸發(fā)器CREATETRIGGEROUTBOUND_DONOUTBOUND這是出庫表的觸發(fā)器AFTERINSERTASBEGINDECLAREACHAR10,BCHAR10,DINTSELECTAIDNO,BIWNO,DIOAMOUNTFROMINSERTEDASIIFSELECTSWNOFROMSTOCKASSWHERESDNOAANDSWNOBISNOTNULLBEGINIFSELECTSWAMOUNTFROMSTOCKASS,INSERTEDWHERESWAMOUNTINSERTEDOAMOUNTANDSWNOINSERTEDWNOANDSDNOINSERTEDDNOISNOTNULLBEGINUPDATESTOCKSETSTOCKWAMOUNTSTOCKWAMOUNTDWHERESTOCKDNOAANDSTOCKWNOBENDELSEBEGINPRINT庫存量不夠ROLLBACKENDENDELSEBEGINPRINT庫存中沒有這種產(chǎn)品ROLLBACKENDENDINSERTINTOOUTBOUNDVALUES1,100005,2010001,50,20100102,小紅INSERTINTOOUTBOUNDVALUES2,100005,2010002,30,20100102,小紅INSERTINTOOUTBOUNDVALUES3,100001,2010003,50,20100102,小風(fēng)INSERTINTOOUTBOUNDVALUES4,100005,2010001,30,20100102,小紅INSERTINTOOUTBOUNDVALUES5,100002,2010002,20,20100208,小風(fēng)INSERTINTOOUTBOUNDVALUES6,100003,2010003,50,20100309,小紅INSERTINTOOUTBOUNDVALUES7,100000,2010002,30,20090415,小風(fēng)INSERTINTOOUTBOUNDVALUES8,100004,2010002,50,20090415,小風(fēng)INSERTINTOOUTBOUNDVALUES9,100005,2010001,30,20100602,小紅INSERTINTOOUTBOUNDVALUES10,100005,2010002,50,20090415,小紅INSERTINTOOUTBOUNDVALUES11,100001,2010003,50,20100603,小風(fēng)INSERTINTOOUTBOUNDVALUES12,100005,2010001,30,20090415,小紅INSERTINTOOUTBOUNDVALUES13,100002,2010002,20,20100705,小風(fēng)INSERTINTOOUTBOUNDVALUES14,100003,2010003,50,20090705,小紅INSERTINTOOUTBOUNDVALUES15,100000,2010002,40,20100705,小風(fēng)INSERTINTOOUTBOUNDVALUES16,100004,2010002,50,20090705,小風(fēng)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,小敏七、實(shí)現(xiàn)按商品名稱查詢商品的入庫情況及目前的庫存量。/方法一/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八、實(shí)現(xiàn)按入庫日期查詢商品的入庫情況及目前的庫存量。/方法一復(fù)合條件查詢/SELECTSNO,SWNO,SDNO,SAMOUNT,SDATE,SUPPLIER,WAMOUNTFROMSTOCKASS,STOCKINGWHERESDATE20060102000000ANDSWNOSTOCKINGWNOANDSDNOSTOCKINGDNO/方法二建立視圖查詢/CREATEVIEWWA_STOCKINGSNO,WNO,DNO,SAMOUNT,SDATE,SUPPLIER,WAMOUNTASSELECTSNO,SWNO,SDNO,SAMOUNT,SDATE,SUPPLIER,WAMOUNTFROMSTOCKASSJOINSTOCKINGONSWNOSTOCKINGWNOANDSDNOSTOCKINGDNOANDSDATE20060102000000WITHCHECKOPTION/方法三相關(guān)子查詢/SELECTSNO,SWNO,SDNO,SAMOUNT,SDATE,SUPPLIER,WAMOUNTFROMSTOCKASS,STOCKINGWHEREEXISTSSELECTWHERESDATE20060102000000ANDSWNOSTOCKINGWNOANDSDNOSTOCKINGDNO九、實(shí)現(xiàn)按商品名稱查詢商品的出庫情況及目前的庫存量。/方法一/SELECTONO,SWNO,SDNO,OAMOUNT,WNAME,ODATE,BUYERS,WAMOUNTFROMSTOCKASS,OUTBOUND,WAREWHEREWNAME長城干紅ANDWAREWNOSWNOANDSWNOOUTBOUNDWNOANDSDNOOUTBOUNDDNO/方法二/創(chuàng)建視圖/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十、實(shí)現(xiàn)按出庫日期查詢商品的出庫情況及目前的庫存量。/方法一/SELECTONO,SWNO,SDNO,OAMOUNT,ODATE,BUYERS,WAMOUNTFROMSTOCKASS,OUTBOUNDWHEREODATE2010010200000ANDSWNOOUTBOUNDWNOANDSDNOOUTBOUNDDNO/方法二/創(chuàng)建視圖/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十一、按時(shí)間段查詢商品庫存情況。/時(shí)間在0102和0102之間的入庫出庫情況/方法一/SELECTWWNAMEAS商品,SUMSAMOUNTAS入庫數(shù)量FROMSTOCKINGASS,WAREASWWHEREWWNOSWNOANDSDATEBETWEEN20060102AND20100102GROUPBYWWNAMESELECTWWNAMEAS商品,SUMOAMOUNTAS出庫數(shù)量FROMOUTBOUNDASO,WAREASWWHEREWWNOOWNOANDODATEBETWEEN20060102AND20100102GROUPBYWWNAME/方法二/SELECTWWNAMEAS商品,SUMSAMOUNTAS入庫數(shù)量FROMSTOCKINGASS,WAREASWWHEREWWNOSWNOANDSDATEINSELECTSDATEFROMSTOCKINGASSWHERESDATEBETWEEN20060102AND20100102GROUPBYWWNAMESELECTWWNAMEAS商品,SUMOAMOUNTAS出庫數(shù)量FROMOUTBOUNDASO,WAREASWWHEREWWNOOWNOANDODATEINSELECTODATEFROMOUTBOUNDASOWHEREODATEBETWEEN20060102AND20100102GROUPBYWWNAME/方法三/SELECTWNAME,SUMSAMOUNTSSUMFROMSTOCKING,WAREWHEREEXISTSSELECTWHERESDATEBETWEEN20060102AND20100102ANDSTOCKINGWNOWAREWNOGROUPBYWNAMESELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUND,WAREWHEREEXISTSSELECTWHEREODATEBETWEEN20060102AND20100102ANDOUTBOUNDWNOWAREWNOGROUPBYWNAME十二、實(shí)現(xiàn)分別按年、季度和月對(duì)入庫商品數(shù)量的統(tǒng)計(jì)。/方法一年/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十三、實(shí)現(xiàn)分別按年、季度和月對(duì)出庫商品數(shù)量的統(tǒng)計(jì)。/第一種方法復(fù)合條件查詢按年/SELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUND,WAREWHEREYEARODATE2010ANDOUTBOUNDWNOWAREWNOGROUPBYWNAME/按季度/SELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUND,WAREWHEREODATEBETWEEN2010090100000AND2010113000000ANDOUTBOUNDWNOWAREWNOGROUPBYWNAME/按月/SELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUND,WAREWHEREMONTHODATE9ANDOUTBOUNDWNOWAREWNOANDYEARODATE2010GROUPBYWNAME/第二種方法視圖查詢按年/CREATEVIEWYEAR_OUTB商品,出庫數(shù)量ASSELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUNDASOJOINWAREASWONOWNOWWNOWHEREYEARODATE2010GROUPBYWNAMEWITHCHECKOPTION/按季度/CREATEVIEWJIDU_OUTB商品,出庫數(shù)量ASSELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUNDASOJOINWAREASWONOWNOWWNOWHEREODATEBETWEEN2010090100000AND2010113000000GROUPBYWNAMEWITHCHECKOPTION/按月/CREATEVIEWMONTH_OUTB商品,出庫數(shù)量ASSELECTWNAME,SUMOAMOUNTOSUMFROMOUTBOUNDASOJOINWAREASWONOWNOWWNOWHEREMONTHODATE9ANDYEARODATE2010GROUPBYWNAMEWITHCHECKOPTION/方法三相關(guān)子查詢/按年/SELECTWNAMEAS商品,SUMOAMOUNTAS出庫數(shù)量FROMOUTBOUND,WAREWHEREEXISTSSELECTWHEREYEARODATE2010ANDOUTBOUNDWNOWAREWNOGROUPBYWNAME/按季度/SELECTWNAMEAS商品,SUMOAMOUNTAS出庫數(shù)量FROMOUTBOUND,WAREWHEREEXISTSSELECTWHEREODATEBETWEEN200903100000AND2009053000000ANDOUTBOUNDWNOWAREWNOGROUPBYWNAME/按月/SELECTWNAMEAS商品,SUMOAMOUNTAS出庫數(shù)量FROMOUTBOUND,WAREWHEREEXISTSSELECTWHEREMONTHODATE9ANDOUTBOUNDWNOWAREWNOANDYEARODATE2010GROUPBYWNAME心得體會(huì)通過這個(gè)庫存物料管理的數(shù)據(jù)庫，我學(xué)到了很多東西，首先是我必須學(xué)會(huì)去查詢有關(guān)物料管理這個(gè)系統(tǒng)有關(guān)的組成部分，和相關(guān)的需求，只有弄懂了這些才能同組員一起討論得出一個(gè)比較合理的結(jié)果。需求分析是最重要的一步，下面的邏輯分析、關(guān)系表和表之間的關(guān)系都要在這個(gè)基礎(chǔ)上來設(shè)計(jì)的，但是里面由于時(shí)間的關(guān)系，不能做到很完善，包括ER圖的構(gòu)思，里面我也說了一些我自己的看法，庫存不能夠成為關(guān)聯(lián)，應(yīng)該是一個(gè)關(guān)系，像倉庫、商品一樣才對(duì)，還有其實(shí)沒有數(shù)據(jù)流通過庫存的，它是由觸發(fā)而更改的，所以不存在由商品庫存?zhèn)}庫的關(guān)系連接。設(shè)計(jì)表的屬性，和其他同學(xué)一起討論了一些問題，其中包括商品屬性要用到多少個(gè)字符，那些需要用到時(shí)間屬性，是要用DATETIME還是SMALLDATETIME因?yàn)樾枰?xì)到分鐘，所以決定用SMALLDATETIME，還有后來插入數(shù)據(jù)時(shí)SMALLDATETIME插入的格式寫錯(cuò)了，也是通過上面一步一步搜索出來的。其實(shí)，這個(gè)數(shù)據(jù)庫的分析做得不是很完善，所以實(shí)現(xiàn)起來不是很難，但最關(guān)鍵的東西是我們學(xué)會(huì)了合作，學(xué)會(huì)了在學(xué)習(xí)中探討，學(xué)會(huì)通過一些手段去查找自己不會(huì)的東西，這些才是真正寶貴的。內(nèi)部資料僅供參考管理員客戶管理服務(wù)反饋管理業(yè)務(wù)管理信貸信息管理客戶信息表服務(wù)反饋表業(yè)務(wù)、合同信息表信貸信息表分析結(jié)果維護(hù)維護(hù)分析提出依據(jù)哈爾濱銀行客戶關(guān)系管理系統(tǒng)數(shù)據(jù)庫全部信息內(nèi)容提交反饋提交反饋9JWKFFWVGTYMJG6ACZ7HDQ8KQQFHVZFEDSWSYXTYQA9WKXFYEQDJSXUYUP2KNXPRWXMAUE9AQGN8GK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXG89AMUE9AQGN8XPR849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z8VGTYMJG6ACZ7HDQ8KQQFHVZFEDSWSYXTYQA9WKXFYEQDJSXUYUP2KNXPRWXMAUE9AQGN8XPR849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMUE9AQGN8XPR849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMYWV3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVU3TNGK8Z89AMYWPAZADNUKNMUWFA5UXY7JND6YWRRWWCVR9CPBKZNMZ849GXGJQVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z89AMVUE9WEWZQCUEQYPEH5PDX2ZVKUMGTXRM6X4NGPPVSTTKSV3TNGK8Z8