數(shù)據(jù)庫課程設(shè)計商品信息管理系統(tǒng)

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

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

3、的最終實(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è)計，將E-R 圖轉(zhuǎn)換為計算機(jī)系統(tǒng)所支持的邏輯模型4 設(shè)計具體實(shí)施一、 進(jìn)行需求分析，編寫數(shù)據(jù)字典。1、系統(tǒng)功能要求設(shè)計 有一個存放商品的倉庫，每天都有商品出庫和入庫。 每種商品都有商品編號、商品名稱、生產(chǎn)廠家、型號、規(guī)格等。 入庫時必須填寫入庫單據(jù)，單據(jù)包括商品名稱、生產(chǎn)廠家、型號、規(guī)格、入庫數(shù)量、入庫日期、入庫倉庫號、入庫倉庫名稱、

4、送貨人姓名。 出庫時必須填寫出庫單據(jù)，單據(jù)包括倉庫號、倉庫名稱、商品編號、商品名稱、型號、規(guī)格、出庫數(shù)量、出庫日期、提貨人姓名。 設(shè)置商品庫存臺帳，商品庫存臺帳是對倉庫中目前庫存的所有商品的明細(xì)記錄，商品庫存臺帳包括商品編號、商品名稱、型號、規(guī)格、庫存數(shù)量、庫存日期。每當(dāng)有商品入庫或商品出庫時都應(yīng)該自動修改該臺帳，最后一次修改的是現(xiàn)在的庫存情況。 商品的入庫和出庫過程通過庫存臺帳更加清晰條理地顯示出倉庫中商品的庫存數(shù)量和庫存日期等信息，容易對庫存內(nèi)的商品信息進(jìn)行查詢，增加，修改，刪除等操作。 該倉庫的商品型號統(tǒng)一為DA01-DA99格式，規(guī)格為CX100-CX199格式主要功能： 商品管理：

5、增加商品：修改商品，刪除商品，瀏覽商品增加商品：實(shí)現(xiàn)增加商品的詳細(xì)資料的功能修改商品：實(shí)現(xiàn)修改商品資料的功能刪除商品：實(shí)現(xiàn)刪除該商品的所有資料的功能瀏覽商品：實(shí)現(xiàn)瀏覽所有商品的功能 庫存管理： 實(shí)現(xiàn)商品的入庫，在商品入庫時通過觸發(fā)器或存儲過程同時完成商品庫存臺帳的更新。實(shí)現(xiàn)商品的出庫，在商品出庫時通過觸發(fā)器或存儲過程同時完成商品庫存臺帳的更新。實(shí)現(xiàn)按商品名稱查詢商品的入庫情況及目前的庫存量。實(shí)現(xiàn)按入庫日期查詢商品的入庫情況及目前的庫存量。實(shí)現(xiàn)按商品名稱查詢商品的出庫情況及目前的庫存量。實(shí)現(xiàn)按出庫日期查詢商品的出庫情況及目前的庫存量。按時間段查詢商品庫存情況。實(shí)現(xiàn)分別按年、季度和月對入庫商品數(shù)量

6、的統(tǒng)計。實(shí)現(xiàn)分別按年、季度和月對出庫商品數(shù)量的統(tǒng)計。2、系統(tǒng)模塊設(shè)計庫存物資管理大體可以分為以下3大模塊，如下圖所示:首先是商品入庫模塊，該模塊主要就是描述把采購回來的商品，分類別的放置到指定的倉庫中去，然后是商品出庫模塊，該模塊主要描述從指定的倉庫中拿出商品；最后時庫存模塊，這個模塊主要是記錄商品的庫存數(shù)量變化。商品信息管理商 品 入 庫 模 塊商 品 出 庫 模 塊商 品 庫 存 模 塊3、數(shù)據(jù)字典表表名中文名Ware商品Depot倉庫Stock 庫存Stocking入庫Outbound出庫商品Ware字段名數(shù)據(jù)類型是否可以為空Is PKIs FK中文名WNochar(10)NO

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

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

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

10、稱商品倉庫號倉庫倉庫名稱入庫日期送貨人姓名入庫數(shù)量入庫入庫號nnnmmm庫存庫存數(shù)量提貨人姓名出庫日期出庫數(shù)量出庫出庫號三、 進(jìn)行數(shù)據(jù)庫的邏輯設(shè)計。關(guān)系模式如下：商品（商品編號、商品名稱、生產(chǎn)廠家、型號、規(guī)格） 商品編號為主鍵倉庫（倉庫號、倉庫名稱） 倉庫號為主鍵庫存（商品編號，倉庫號，庫存數(shù)量） （商品編號，倉庫號）為主鍵，同時也分別為外鍵入庫（入庫號，商品編號，倉庫號，入庫數(shù)量，入庫日期，送貨人姓名）（入庫號）為主鍵，（商品編號，倉庫號）為外鍵出庫（出庫號，商品編號，倉庫號，出庫數(shù)量，出庫日期，提貨人姓名）（出庫號）為主鍵，（商品編號，倉庫號）為外鍵四、 完成物理數(shù)據(jù)庫的設(shè)計，（包括數(shù)據(jù)庫

11、、表、索引、視圖、完整性約束的物理設(shè)計。）創(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 /*插入商品信息*/insert into Warevalues ('

12、100000','毛巾','新家園','DA11','CX100');insert into Warevalues ('100001','臺燈','新家園','DA35','CX110');insert into Warevalues ('100002','電冰箱','海爾','DA61','CX900');insert into Warevalues ('

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

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

15、Bcreate table Stock ( /*定義庫存關(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

16、 primary key,WNo char(10) NOT 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 ke

17、y,WNo char(10)NOT NULL ,foreign 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商品編號要求在100000999999之間，WName不能取空；倉庫Depot的DNo倉庫編號要求在20100012010999之間，DName不能取空；該倉

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

19、aint C3 check(Spec like 'CX1-90-90-9');use Material_DBalter table Ware /*添加對表Ware的WNo屬性的完整性約束，要求在9999999999之間*/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 Waredr

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

21、T Stock.WNo,Ware.WName,sum(Stock.WAmount)from Ware,Stockwhere Stock.WNo=Ware.WNo group by Stock.WNo,Ware.WName;五、 實(shí)現(xiàn)商品的入庫，在商品入庫時通過觸發(fā)器或存儲過程同時完成商品庫存臺帳的更新。入庫表的觸發(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.SAmou

22、ntfrom inserted as i;if(select Stock.WNo from Stock where Stock.WNo=b 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','100005','2010001&#

23、39;,200,'2006-01-02','小明');insert into stocking values('101','100005','2010003',200,'2006-01-02','小蘭');insert into stocking values('102','100001','2010003',200,'2006-01-02','小東');insert into stocking valu

24、es('103','100002','2010001',200,'2006-01-02','小紅');insert into stocking values('104','100003','2010001',200,'2007-01-14','小風(fēng)');insert into stocking values('105','100005','2010002',200,'2006-01

25、-25','小明');insert into stocking values('106','100000','2010002',200,'2006-02-02','小明');insert into stocking values('107','100001','2010001',200,'2005-03-02','小東');insert into stocking values('108',

26、9;100002','2010002',200,'2007-04-02','小紅');insert into stocking values('109','100003','2010003',200,'2006-04-02','小風(fēng)');insert into stocking values('110','100004','2010001',200,'2006-06-09','小敏'

27、;);insert into stocking values('111','100004','2010002',200,'2005-06-02','小風(fēng)');insert into stocking values('112','100005','2010001',200,'2005-06-02','小青');insert into stocking values('113','100000','2

28、010003',200,'2005-06-02','小明');insert into stocking values('114','100001','2010001',200,'2005-06-02','小東');insert into stocking values('115','100002','2010002',200,'2007-07-02','小紅');insert into stock

29、ing values('116','100003','2010003',200,'2006-08-02','小風(fēng)');insert into stocking values('117','100000','2010001',200,'2006-08-02','小明');insert into stocking values('118','100001','2010001',200,'

30、;2005-09-04','小東');insert into stocking values('119','100002','2010002',200,'2005-06-02','小紅');insert into stocking values('120','100003','2010003',200,'2005-09-11','小風(fēng)');insert into stocking values('121&

31、#39;,'100002','2010002',200,'2005-09-01','小明');insert into stocking values('122','100003','2010002',200,'2005-07-08','小敏');insert into stocking values('123','100002','2010003',200,'2005-04-11',

32、9;小敏');insert into stocking values('124','100001','2010001',200,'2007-07-08','小紅');insert into stocking values('125','100001','2010003',200,'2005-08-21','小青');insert into stocking values('126','100005'

33、;,'2010001',200,'2005-07-08','小青');insert into stocking values('127','100003','2010003',200,'2005-09-01','小蘭');insert into stocking values('128','100002','2010002',200,'2007-07-08','小蘭');insert in

34、to stocking values('129','100004','2010001',200,'2007-07-08','小蘭');insert into stocking values('130','100005','2010002',200,'2007-07-08','小紅');六、 實(shí)現(xiàn)商品的出庫，在商品出庫時通過觸發(fā)器或存儲過程同時完成商品庫存臺帳的更新。出庫表觸發(fā)器create trigger outbound_d on O

35、utbound -這是出庫表的觸發(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.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

36、=inserted.WNo and s.DNo=inserted.DNo) is not nullbeginupdate Stock set Stock.WAmount=Stock.WAmount-dwhere Stock.DNo=a and Stock.WNo=bendelsebeginprint '庫存量不夠'rollbackendendelsebeginprint '庫存中沒有這種產(chǎn)品'rollbackendendinsert into Outbound values('1','100005','2010001

37、9;,50,'2010-01-02','小紅');insert into Outbound values('2','100005','2010002',30,'2010-01-02','小紅');insert into Outbound values('3','100001','2010003',50,'2010-01-02','小風(fēng)');insert into Outbound values('

38、4','100005','2010001',30,'2010-01-02','小紅');insert into Outbound values('5','100002','2010002',20,'2010-02-08','小風(fēng)');insert into Outbound values('6','100003','2010003',50,'2010-03-09','小紅&

39、#39;);insert into Outbound values('7','100000','2010002',30,'2009-04-15','小風(fēng)');insert into Outbound values('8','100004','2010002',50,'2009-04-15','小風(fēng)');insert into Outbound values('9','100005','201000

40、1',30,'2010-06-02','小紅');insert into Outbound values('10','100005','2010002',50,'2009-04-15','小紅');insert into Outbound values('11','100001','2010003',50,'2010-06-03','小風(fēng)');insert into Outbound values

41、('12','100005','2010001',30,'2009-04-15','小紅');insert into Outbound values('13','100002','2010002',20,'2010-07-05','小風(fēng)');insert into Outbound values('14','100003','2010003',50,'2009-07-05'

42、,'小紅');insert into Outbound values('15','100000','2010002',40,'2010-07-05','小風(fēng)');insert into Outbound values('16','100004','2010002',50,'2009-07-05','小風(fēng)');insert into Outbound values('17','100005'

43、,'2010002',20,'2008-09-01','小明');insert into Outbound values('18','100001','2010003',30,'2010-09-01','小敏');insert into Outbound values('19','100000','2010002',40,'2010-09-01','小敏');insert into Out

44、bound values('20','100002','2010002',50,'2008-09-01','小紅');insert into Outbound values('21','100000','2010002',20,'2010-08-21','小青');insert into Outbound values('22','100003','2010001',30,'200

45、8-09-01','小青');insert into Outbound values('23','100002','2010002',40,'2008-09-01','小蘭');insert into Outbound values('24','100003','2010003',50,'2008-12-30','小蘭');insert into Outbound values('25','

46、;100004','2010002',20,'2009-12-30','小蘭');insert into Outbound values('26','100005','2010001',40,'2008-12-08','小紅');insert into Outbound values('27','100001','2010003',10,'2010-12-30','小敏');ins

47、ert into Outbound values('28','100002','2010002',30,'2008-12-08','小明');insert into Outbound values('29','100003','2010003',30,'2009-12-18','小紅');insert into Outbound values('30','100004','2010001'

48、,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(32)SET WName ='電冰箱'SELECT * F

49、ROM 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.WAmountFROM Stock as s,LSName as l,Ware

50、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.DNo,SAmount,SDate,Supplier,WAmount from

51、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 JOIN Stocking on s.WNo=Stocking.WNo and s

52、.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=Stocking.DNo);九、 實(shí)現(xiàn)按商品名稱查詢商品的出庫情況及目前的

53、庫存量。/*方法一*/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,WName)ASSELECT ONo,Stock.WNo,Stock.DN

54、o,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(32)set WName='長城干紅'select *fr

55、om 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.Wno=s.Wno);十、 實(shí)現(xiàn)按出庫日期查詢商品的出庫情況及目前的庫存量。/

56、*方法一*/ 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,Stock.DNo,OAmount,ODate,Buyers,WAmoun

57、tFROM 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 exists (select * where ODate='2010-01

58、-02 0:00:00' and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo);十一、 按時間段查詢商品庫存情況。/*時間在-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-02'group by w.WNameselect w.WName a

59、s 商品,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(select SDatefrom Stocking as swhere SDa

60、te 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-02')group by w.WName/*方法三*/select

61、 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 between '2006-01-02' and '20

62、10-01-02' and Outbound.WNo=Ware.WNo ) group by WName十二、 實(shí)現(xiàn)分別按年、季度和月對入庫商品數(shù)量的統(tǒng)計。/*方法一：年：*/select WName,sum(SAmount) Ssumfrom Stocking,Warewhere year (SDate)=2005 and Stocking.WNo=Ware.WNogroup by WName/*季度：*/select WName,sum(SAmount) Ssum from Stocking,Warewhere 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/*方法二：