1、精選優(yōu)質(zhì)文檔-傾情為你奉上課程設(shè)計(jì)課程名稱：數(shù)據(jù)庫系統(tǒng)概論 設(shè)計(jì)題目： 商品信息管理系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)院系名稱 信息科學(xué)與工程學(xué)院 班 級(jí) 計(jì)算機(jī)科學(xué)與技術(shù)本科一班 姓名與學(xué)號(hào)：李* 于* 見* 陳*指導(dǎo)教師 李 * 1課題簡介隨著現(xiàn)代科技的高速發(fā)展，設(shè)備和管理的現(xiàn)代化，在實(shí)際工作中如何提高工作效率成為一個(gè)很重要的問題。而建立管理信息系統(tǒng)是一個(gè)很好的解決辦法。 經(jīng)過半年的學(xué)習(xí)，我們對(duì)計(jì)算機(jī)方面的知識(shí)有了很大的提升，本著理論聯(lián)系實(shí)際的宗旨，通過學(xué)校提供的這次軟件技術(shù)實(shí)踐的機(jī)會(huì)，在指導(dǎo)教師的幫助下，歷經(jīng)兩周時(shí)間，我們自行設(shè)計(jì)一套商品信息管理系統(tǒng)，在下面的各章中，我將以這套商品管理系統(tǒng)為例,談?wù)勂溟_發(fā)過

2、程和所涉及到的問題。2設(shè)計(jì)目的 應(yīng)用對(duì)數(shù)據(jù)庫系統(tǒng)原理的理論學(xué)習(xí),通過上機(jī)實(shí)踐的方式將理論知識(shí)與實(shí)踐更好的結(jié)合起來,鞏固所學(xué)知識(shí)。 數(shù)據(jù)庫應(yīng)用 課程實(shí)踐：實(shí)踐和鞏固在課堂教學(xué)中學(xué)習(xí)有關(guān)知識(shí)，熟練掌握對(duì)于給定結(jié)構(gòu)的數(shù)據(jù)庫的創(chuàng)建、基本操作、程序系統(tǒng)的建立和調(diào)試以及系統(tǒng)評(píng)價(jià)。 數(shù)據(jù)庫原理 軟件設(shè)計(jì)實(shí)踐：實(shí)踐和鞏固在課堂教學(xué)中學(xué)習(xí)的關(guān)于關(guān)系數(shù)據(jù)庫原理的有關(guān)知識(shí)和數(shù)據(jù)庫系統(tǒng)的建立方法，熟練掌握對(duì)于給定實(shí)際問題，為了建立一個(gè)關(guān)系數(shù)據(jù)庫商品信息管理系統(tǒng)，必須得經(jīng)過系統(tǒng)調(diào)研、需求分析、概念設(shè)計(jì)、邏輯設(shè)計(jì)、物理設(shè)計(jì)、系統(tǒng)調(diào)試、維護(hù)以及系統(tǒng)評(píng)價(jià)的一般過程，為畢業(yè)設(shè)計(jì)打下基礎(chǔ)。3設(shè)計(jì)內(nèi)容 運(yùn)用基于E-R 模型的數(shù)據(jù)庫

3、設(shè)計(jì)方法和關(guān)系規(guī)范化理論做指導(dǎo)完成從系統(tǒng)的分析到設(shè)計(jì)直至系統(tǒng)的最終實(shí)現(xiàn),開發(fā)商品信息系統(tǒng),完成商品信息管理系統(tǒng)的全部功能,商品入庫子系統(tǒng)，商品出庫子系統(tǒng)，商品庫存子系統(tǒng)。 首先做好需求分析，并完成數(shù)據(jù)流圖和數(shù)據(jù)字典。 其次做概念分析，利用實(shí)體聯(lián)系的方法將需求分析的用戶需求抽象為信息結(jié)構(gòu)，得到E-R 圖。 然后就是邏輯結(jié)構(gòu)設(shè)計(jì)，將E-R 圖轉(zhuǎn)換為計(jì)算機(jī)系統(tǒng)所支持的邏輯模型4 設(shè)計(jì)具體實(shí)施一、 進(jìn)行需求分析，編寫數(shù)據(jù)字典。1、系統(tǒng)功能要求設(shè)計(jì) 有一個(gè)存放商品的倉庫，每天都有商品出庫和入庫。 每種商品都有商品編號(hào)、商品名稱、生產(chǎn)廠家、型號(hào)、規(guī)格等。 入庫時(shí)必須填寫入庫單據(jù)，單據(jù)包括商品名稱、生產(chǎn)廠家

4、、型號(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)一為DA01-DA99格

5、式，規(guī)格為CX100-CX199格式主要功能： 商品管理： 增加商品：修改商品，刪除商品，瀏覽商品增加商品：實(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)按出庫日期查詢商品的出庫情況及目前的庫存量。按

6、時(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ù)類型是否

7、可以為空Is PKIs FK中文名WNochar(10)NOT NULLYesNo商品編號(hào)WNamevarchar(50)NOT NULLNoNo商品名稱WFactoryvarchar(50)NOT NULLNoNo生產(chǎn)廠家WType varchar(20)NOT NULLNoNo型號(hào)Specvarchar(20)NOT NULLNoNo規(guī)格倉庫 Depot字段名數(shù)據(jù)類型是否可以為空Is PKIs FK中文名DNochar(10)NOT NULLYesNo倉庫號(hào)DNamevarchar(50)NOT NULLNoNo倉庫名稱庫存Stock 字段名數(shù)據(jù)類型是否可以為空Is P

8、KIs FK中文名WNochar(10)NOT NULLYesYes商品編號(hào)Dnochar(10)NOT NULLYesYes倉庫號(hào)WAmount IntNOT NULLNoNo庫存數(shù)量入庫Stocking字段名數(shù)據(jù)類型是否可以為空Is PKIs FK中文名SNochar(10)NOT NULLYesNo入庫號(hào)WNochar(10)NOT NULLNoYes商品編號(hào)Dnochar(10)NOT NULLNoYes倉庫號(hào)SAmountIntNOT NULLNoNo入庫數(shù)量SDatesmalldatetimeNOT NULLNoNo入庫日期Suppliervarchar(50)NOT N

9、ULLNoNo送貨人姓名出庫Outbound字段名數(shù)據(jù)類型是否可以為空Is PKIs FK中文名ONochar(10)NOT NULLYesNo出庫號(hào)WNochar(10)NOT NULLNoYes商品編號(hào)Dnochar(10)NOT NULLNoYes倉庫號(hào)OAmountintNOT NULLNoNo出庫數(shù)量ODatesmalldatetimeNOT NULLNoNo出庫日期Buyersvarchar(50)NOT NULLNoNo提貨人姓名二、 設(shè)計(jì)完整的E-R圖。商品編號(hào)型號(hào)生產(chǎn)廠家規(guī)格商品名稱商品倉庫號(hào)倉庫倉庫名稱商品倉庫庫存nm商品倉庫入庫nm商品倉庫出庫nm庫存關(guān)系：入庫關(guān)系：出庫

10、關(guān)系：分E-R圖：總E-R圖：商品編號(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)）為主鍵，（商品

11、編號(hào)，倉庫號(hào)）為外鍵四、 完成物理數(shù)據(jù)庫的設(shè)計(jì)，（包括數(shù)據(jù)庫、表、索引、視圖、完整性約束的物理設(shè)計(jì)。）創(chuàng)建數(shù)據(jù)庫create database Material_DB創(chuàng)建商品表use Material_DBcreate table Ware( /*定義商品表*/WNo char(10) NOT NULL primary key,WName varchar(50) NOT NULL,WFactory varchar(50) NOT NULL,WType varchar(20) NOT NULL,Spec varchar(20) NOT NULL)use Material_DB /*插入商品信息*

12、/insert into Warevalues ('','毛巾','新家園','DA11','CX100');insert into Warevalues ('','臺(tái)燈','新家園','DA35','CX110');insert into Warevalues ('','電冰箱','海爾','DA61','CX900');insert into Warev

13、alues ('','電視','東芝','DA52','CX901');insert into Warevalues ('','長城干紅','中糧華夏','DA25','CX108');insert into Warevalues ('','筆記本','東芝','DA24','CX781'); 創(chuàng)建倉庫表use Material_DBcreate table

14、 Depot( /*定義倉庫表*/DNo char(10) NOT NULL primary key,DName varchar(50) NOT NULL)use Material_DB /*插入倉庫信息*/insert into Depotvalues ('','勝昌');insert into Depotvalues ('','德隆');insert into Depotvalues ('','豪友');創(chuàng)建庫存表use Material_DBcreate table Stock ( /*定義庫

15、存關(guān)系*/WNo char(10) NOT NULL,DNo char(10) NOT NULL,-WDate smalldatetime NOT NULL,WAmount int NOT NULL,primary key(WNo,Dno),foreign key (WNo) references Ware(WNo),foreign key (DNo) references Depot(DNo)創(chuàng)建入庫表use Material_DBcreate table Stocking( /*定義入庫關(guān)系*/SNo char(10) NOT NULL primary key,WNo char(10) N

16、OT NULL,DNo char(10) NOT NULL,foreign key (WNo) references Ware(WNo),foreign key (DNo) references Depot(DNo),SAmount Int NOT NULL,SDate smalldatetime NOT NULL,Supplier varchar(50) NOT NULL)創(chuàng)建出庫表use Material_DBcreate table Outbound( /*定義出庫關(guān)系*/ONo char(10)NOT NULL primary key,WNo char(10)NOT NULL ,for

17、eign key (WNo)references Ware(WNo),DNo char(10) NOT NULL,foreign key (DNo) references Depot(DNo),OAmount int NOT NULl ,ODate smalldatetime NOT NULL,Buyers varchar(50) NOT NULL)完整約束的說明：商品Ware的WNo商品編號(hào)要求在之間，WName不能取空；倉庫Depot的DNo倉庫編號(hào)要求在之間，DName不能取空；該倉庫的商品型號(hào)統(tǒng)一為DA01-DA99格式，規(guī)格為CX100-CX199格式。use Material_DB

18、 /*定義表Ware唯一性的約束條件*/alter Table Ware add unique(WName,WFactory,WType,Spec);alter Table Ware /*定義WType的完整性約束條件：商品型號(hào)統(tǒng)一為DA01-DA99格式*/add constraint C2 check(WType like 'DA0-91-9');alter Table Ware /*定義Spec的完整性約束條件：規(guī)格為CX100-CX199格式*/add constraint C3 check(Spec like 'CX1-90-90-9');use Ma

19、terial_DBalter table Ware /*添加對(duì)表Ware的WNo屬性的完整性約束，要求在之間*/add constraint C4 check(WNo like'1-90-90-90-90-90-90-90-90-90-9');alter table Depotadd constraint C5 check(DNo like'20100-90-90-9');use Material_DBalter table Waredrop constraint C4;use Material_DBalter table Ware /*添加對(duì)表Ware的WNo

20、屬性的完整性約束，要求在之間*/add constraint C4 check(WNo like'1-90-90-90-90-90-9');創(chuàng)建視圖通過WLST（提取“物料視圖”每個(gè)字的頭字母）視圖來瀏覽庫存中相同編號(hào)商品的信息，其中包括：商品編號(hào)WNo、商品名WName、商品廠家WFactory、商品型號(hào)WType、商品規(guī)格Spec、商品數(shù)量SNum、create view VWname(商品編號(hào),商品名稱, 庫存數(shù)量)-視圖ASSELECT Stock.WNo,Ware.WName,sum(Stock.WAmount)from Ware,Stockwhere Stock.W

21、No=Ware.WNo group by Stock.WNo,Ware.WName;五、 實(shí)現(xiàn)商品的入庫，在商品入庫時(shí)通過觸發(fā)器或存儲(chǔ)過程同時(shí)完成商品庫存臺(tái)帳的更新。入庫表的觸發(fā)器create trigger stocking_into on stocking -這是入庫表的觸發(fā)器 after insert asbegin declare a char(10),b char(10),d int;select a=i.DNo,b=i.WNo,d=i.SAmountfrom inserted as i;if(select Stock.WNo from Stock where Stock.WNo=b

22、 and Stock.DNo=a) is not nullbeginupdate Stockset Stock.WAmount=Stock.WAmount+dwhere Stock.WNo=b and Stock.DNo=a;return;endinsert into stock values(b,a,d);endinsert into stocking values('100','','',200,'2006-01-02','小明');insert into stocking values('101

23、9;,'','',200,'2006-01-02','小蘭');insert into stocking values('102','','',200,'2006-01-02','小東');insert into stocking values('103','','',200,'2006-01-02','小紅');insert into stocking values(&#

24、39;104','','',200,'2007-01-14','小風(fēng)');insert into stocking values('105','','',200,'2006-01-25','小明');insert into stocking values('106','','',200,'2006-02-02','小明');insert into stocking

25、values('107','','',200,'2005-03-02','小東');insert into stocking values('108','','',200,'2007-04-02','小紅');insert into stocking values('109','','',200,'2006-04-02','小風(fēng)');insert into

26、stocking values('110','','',200,'2006-06-09','小敏');insert into stocking values('111','','',200,'2005-06-02','小風(fēng)');insert into stocking values('112','','',200,'2005-06-02','小青');ins

27、ert into stocking values('113','','',200,'2005-06-02','小明');insert into stocking values('114','','',200,'2005-06-02','小東');insert into stocking values('115','','',200,'2007-07-02','小紅&

28、#39;);insert into stocking values('116','','',200,'2006-08-02','小風(fēng)');insert into stocking values('117','','',200,'2006-08-02','小明');insert into stocking values('118','','',200,'2005-09-04'

29、,'小東');insert into stocking values('119','','',200,'2005-06-02','小紅');insert into stocking values('120','','',200,'2005-09-11','小風(fēng)');insert into stocking values('121','','',200,'2005-0

30、9-01','小明');insert into stocking values('122','','',200,'2005-07-08','小敏');insert into stocking values('123','','',200,'2005-04-11','小敏');insert into stocking values('124','','',200,&#

31、39;2007-07-08','小紅');insert into stocking values('125','','',200,'2005-08-21','小青');insert into stocking values('126','','',200,'2005-07-08','小青');insert into stocking values('127','','

32、9;,200,'2005-09-01','小蘭');insert into stocking values('128','','',200,'2007-07-08','小蘭');insert into stocking values('129','','',200,'2007-07-08','小蘭');insert into stocking values('130',''

33、,'',200,'2007-07-08','小紅');六、 實(shí)現(xiàn)商品的出庫，在商品出庫時(shí)通過觸發(fā)器或存儲(chǔ)過程同時(shí)完成商品庫存臺(tái)帳的更新。出庫表觸發(fā)器create trigger outbound_d on Outbound -這是出庫表的觸發(fā)器 after insert as begin declare a char(10),b char(10),d int ; select a=i.DNo,b=i.WNo,d=i.OAmount from inserted as iif(select s.WNo from Stock as s where s.

34、DNo=a and s.WNo=b ) is not nullbeginif(select s.WAmount from Stock as s ,inserted where s.WAmount>=inserted.OAmount and s.WNo=inserted.WNo and s.DNo=inserted.DNo) is not nullbeginupdate Stock set Stock.WAmount=Stock.WAmount-dwhere Stock.DNo=a and Stock.WNo=bendelsebeginprint '庫存量不夠'rollba

35、ckendendelsebeginprint '庫存中沒有這種產(chǎn)品'rollbackendendinsert into Outbound values('1','','',50,'2010-01-02','小紅');insert into Outbound values('2','','',30,'2010-01-02','小紅');insert into Outbound values('3','

36、;','',50,'2010-01-02','小風(fēng)');insert into Outbound values('4','','',30,'2010-01-02','小紅');insert into Outbound values('5','','',20,'2010-02-08','小風(fēng)');insert into Outbound values('6','

37、;','',50,'2010-03-09','小紅');insert into Outbound values('7','','',30,'2009-04-15','小風(fēng)');insert into Outbound values('8','','',50,'2009-04-15','小風(fēng)');insert into Outbound values('9','

38、;','',30,'2010-06-02','小紅');insert into Outbound values('10','','',50,'2009-04-15','小紅');insert into Outbound values('11','','',50,'2010-06-03','小風(fēng)');insert into Outbound values('12',&

39、#39;','',30,'2009-04-15','小紅');insert into Outbound values('13','','',20,'2010-07-05','小風(fēng)');insert into Outbound values('14','','',50,'2009-07-05','小紅');insert into Outbound values('15'

40、;,'','',40,'2010-07-05','小風(fēng)');insert into Outbound values('16','','',50,'2009-07-05','小風(fēng)');insert into Outbound values('17','','',20,'2008-09-01','小明');insert into Outbound values('18&

41、#39;,'','',30,'2010-09-01','小敏');insert into Outbound values('19','','',40,'2010-09-01','小敏');insert into Outbound values('20','','',50,'2008-09-01','小紅');insert into Outbound values('

42、21','','',20,'2010-08-21','小青');insert into Outbound values('22','','',30,'2008-09-01','小青');insert into Outbound values('23','','',40,'2008-09-01','小蘭');insert into Outbound values(&#

43、39;24','','',50,'2008-12-30','小蘭');insert into Outbound values('25','','',20,'2009-12-30','小蘭');insert into Outbound values('26','','',40,'2008-12-08','小紅');insert into Outbound values

44、('27','','',10,'2010-12-30','小敏');insert into Outbound values('28','','',30,'2008-12-08','小明');insert into Outbound values('29','','',30,'2009-12-18','小紅');insert into Outbound val

45、ues('30','','',40,'2010-09-01','小敏');七、 實(shí)現(xiàn)按商品名稱查詢商品的入庫情況及目前的庫存量。/*方法一*/select SNo,WName,s.WNo,s.DNo,SAmount,WAmount from Stock as s,Ware as w,Stocking as siwhere WName='電冰箱' and s.WNo=w.WNo and w.WNo=si.WNo and s.DNo=si.DNo/*方法二*/ declare WName varchar

46、(32)SET WName ='電冰箱'SELECT * FROM Stocking WHERE WNo IN (SELECT WNo FROM Ware WHERE WName = WName);/*方法三*/CREATE VIEW LSName(SNo,WName,WNo,DNo,SAmount,WAmount)ASselect SNo,WName,s.WNo,s.DNo,SAmount,WAmountfrom Stock as s,Ware as w,Stocking as si select distinct si.Sno,l.WName,si.SAmount,s.WA

47、mountFROM Stock as s,LSName as l,Ware as w,Stocking as siWHERE l.WName='電冰箱' and si.Sno=l.Sno and w.WName=l.WName and s.WNo=si.WNo and si.WNo=w.WNo and w.WNo=l.WNo and si.DNo=l.DNo and si.SAmount=l.SAmount and s.WAmount=l.WAmount 八、 實(shí)現(xiàn)按入庫日期查詢商品的入庫情況及目前的庫存量。/*方法一 復(fù)合條件查詢*/select SNo,s.WNo,s.DN

48、o,SAmount,SDate,Supplier,WAmount from Stock as s,Stockingwhere SDate='2006-01-02 00:00:00' and s.WNo=Stocking.WNo and s.DNo=Stocking.DNo/*方法二建立視圖查詢*/create view WA_Stocking(SNo,WNo,DNo,SAmount,SDate,Supplier,WAmount)as select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount from Stock as s JOI

49、N Stocking on s.WNo=Stocking.WNo and s.DNo=Stocking.DNo and SDate='2006-01-02 00:00:00'with check option;/*方法三相關(guān)子查詢*/select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount from Stock as s,Stockingwhere exists (select * where SDate='2006-01-02 00:00:00' and s.WNo=Stocking.WNo and s.DNo

50、=Stocking.DNo);九、 實(shí)現(xiàn)按商品名稱查詢商品的出庫情況及目前的庫存量。/*方法一*/select ONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmount from Stock as s,Outbound,Warewhere WName='長城干紅' and Ware.Wno=s.Wno and s.Wno=Outbound.Wno and s.DNo=Outbound.DNo/*方法二*/* 創(chuàng)建視圖*/CREATE VIEW W_S_O(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount

51、,WName)ASSELECT ONo,Stock.WNo,Stock.DNo,OAmount,ODate,Buyers,WAmount,Ware.WNameFROM Ware,Stock,OutboundWHERE Stock.WNo=Outbound.WNo AND Stock.DNo=Outbound.DNo AND Ware.WNo=Stock.Wnoselect ONo,WNo,DNo,OAmount,WName,ODate,Buyers,WAmount from W_S_Owhere WName='長城干紅' /*方法三*/declare WName varchar

52、(32)set WName='長城干紅'select *from Outbound where WNo=(select WNo from Ware where WName=WName);/*方法四*/select ONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmount from Stock as s,Outbound,Warewhere exists (select *where WName='長城干紅' and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo and Ware.W

53、no=s.Wno);十、 實(shí)現(xiàn)按出庫日期查詢商品的出庫情況及目前的庫存量。/*方法一*/ select ONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmount from Stock as s,Outboundwhere ODate='2010-01-02 0:00:00' and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo/*方法二*/* 創(chuàng)建視圖*/CREATE VIEW SO(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount)ASSELECT ONo,Stock.WNo

54、,Stock.DNo,OAmount,ODate,Buyers,WAmountFROM Stock,OutboundWHERE Stock.WNo=Outbound.WNo AND Stock.DNo=Outbound.DNo select ONo,WNo,DNo,OAmount,ODate,Buyers,WAmountfrom SOwhere ODate='2010-01-02 0:00:00'/*方法三*/select ONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmount from Stock as s,Outboundwhere exi

55、sts (select * where ODate='2010-01-02 0:00:00' and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo);十一、 按時(shí)間段查詢商品庫存情況。/*時(shí)間在-01-02和-01-02之間的入庫出庫情況*/*方法一*/select w.WName as 商品,sum(SAmount) as 入庫數(shù)量from Stocking as s ,Ware as w where w.WNo=s.WNo and SDate between '2006-01-02' and '2010-01-0

56、2'group by w.WNameselect w.WName as 商品,sum(OAmount) as 出庫數(shù)量from Outbound as o ,Ware as w where w.WNo=o.WNo and ODate between '2006-01-02' and '2010-01-02'group by w.WName/*方法二*/select w.WName as 商品,sum(SAmount) as 入庫數(shù)量from Stocking as s ,Ware as w where w.WNo=s.WNo and SDate in(s

57、elect SDatefrom Stocking as swhere SDate between '2006-01-02' and '2010-01-02')group by w.WNameselect w.WName as 商品,sum(OAmount) as 出庫數(shù)量from Outbound as o ,Ware as w where w.WNo=o.WNo and ODate in(select ODatefrom Outbound as owhere ODate between '2006-01-02' and '2010-01

58、-02')group by w.WName/*方法三*/select WName,sum(SAmount) Ssum from Stocking,Ware where exists (select * where SDate between '2006-01-02' and '2010-01-02' and Stocking.WNo=Ware.WNo ) group by WNameselect WName,sum(OAmount) Osum from Outbound,Ware where exists (select * where ODate be

59、tween '2006-01-02' and '2010-01-02' and Outbound.WNo=Ware.WNo ) group by WName十二、 實(shí)現(xiàn)分別按年、季度和月對(duì)入庫商品數(shù)量的統(tǒng)計(jì)。/*方法一：年：*/select WName,sum(SAmount) Ssumfrom Stocking,Warewhere year (SDate)=2005 and Stocking.WNo=Ware.WNogroup by WName/*季度：*/select WName,sum(SAmount) Ssum from Stocking,Warewhe

60、re SDate between '2005-3-1 0:00:00' and '2005-5-31 0:00:00' and Stocking.WNo=Ware.WNogroup by WName/*月：*/select WName,sum(SAmount) Ssumfrom Stocking,Warewhere month (SDate)=6 and Stocking.WNo=Ware.WNo and year (SDate)=2005group by WName/*方法二：年：*/create view Stocking_SumNasselect WNam

61、e,sum(SAmount) Ssumfrom Stocking JOIN Ware on Stocking.WNo=Ware.WNowhere year (SDate)=2005group by WName/*月：*/create view Stocking_SumMasselect WName,sum(SAmount) Ssumfrom Stocking JOIN Ware on Stocking.WNo=Ware.WNowhere month (SDate)=6 and year (SDate)=2005group by WName/*季度：*/create view Stocking_

62、SumJasselect WName,sum(SAmount) Ssumfrom Stocking JOIN Ware on Stocking.WNo=Ware.WNowhere SDate between '2005-3-1 0:00:00' and '2005-5-31 0:00:00'group by WName/*方法三：年：*/select WName,sum(SAmount) Ssum from Stocking,Ware where exists (select * where year (SDate)=2005 and Stocking.WNo=Ware.WNo ) group by WName/*季度：*/select WName,sum(SAmount) Ssum from Stocking,Ware where exists (sele