數(shù)據(jù)庫(kù)課程設(shè)計(jì)(改)

1、東 莞 理 工 學(xué) 院課程設(shè)計(jì)課程名稱(chēng)： 數(shù)據(jù)庫(kù)系統(tǒng)原理 設(shè)計(jì)題目： 庫(kù)存物資管理 院系名稱(chēng) 計(jì)算機(jī)學(xué)院 班 級(jí) 軟件工程（2）班 學(xué)生姓名 潘東揚(yáng) 學(xué)號(hào) 1 組 員葉碧怡、李斯斯、黎小浪、陳開(kāi)貴、馮錫恩、梁志飛、袁杰生、廖裕森、劉浩鋒、黃力、黎熾培、陳耀新 指導(dǎo)教師 歐陽(yáng)驥 時(shí) 間 一周 一、 進(jìn)行需求分析，編寫(xiě)數(shù)據(jù)字典。1、系統(tǒng)功能要求設(shè)計(jì) 有一個(gè)存放商品的倉(cāng)庫(kù)，每天都有商品出庫(kù)和入庫(kù)。 每種商品都有商品編號(hào)、商品名稱(chēng)、生產(chǎn)廠家、型號(hào)、規(guī)格等。 入庫(kù)時(shí)必須填寫(xiě)入庫(kù)單據(jù)，單據(jù)包括商品名稱(chēng)、生產(chǎn)廠家、型號(hào)、規(guī)格、入庫(kù)數(shù)量、入庫(kù)日期、入庫(kù)倉(cāng)庫(kù)號(hào)、入庫(kù)倉(cāng)庫(kù)名稱(chēng)、送貨人姓名。 出庫(kù)時(shí)必須填寫(xiě)出庫(kù)單據(jù)

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

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

4、商品數(shù)量的統(tǒng)計(jì)。2、系統(tǒng)模塊設(shè)計(jì)庫(kù)存物資管理大體可以分為以下3大模塊，如下圖所示:首先是商品入庫(kù)模塊，該模塊主要就是描述把采購(gòu)回來(lái)的商品，分類(lèi)別的放置到指定的倉(cāng)庫(kù)中去，然后是商品出庫(kù)模塊，該模塊主要描述從指定的倉(cāng)庫(kù)中拿出商品；最后時(shí)庫(kù)存模塊，這個(gè)模塊主要是記錄商品的庫(kù)存數(shù)量變化。庫(kù)存物資管理商 品 入 庫(kù) 模 塊商 品 出 庫(kù) 模 塊商 品 庫(kù) 存 模 塊3、數(shù)據(jù)字典表表名中文名Ware商品Depot倉(cāng)庫(kù)Stock庫(kù)存Stocking入庫(kù)Outbound出庫(kù)商品Ware字段名數(shù)據(jù)類(lèi)型是否可以為空Is PKIs FK中文名WNochar(10)NOT NULLYesNo商品編號(hào)WNamevarc

5、har(50)NOT NULLNoNo商品名稱(chēng)WFactoryvarchar(50)NOT NULLNoNo生產(chǎn)廠家WTypevarchar(20)NOT NULLNoNo型號(hào)Specvarchar(20)NOT NULLNoNo規(guī)格倉(cāng)庫(kù) Depot字段名數(shù)據(jù)類(lèi)型是否可以為空Is PKIs FK中文名DNochar(10)NOT NULLYesNo倉(cāng)庫(kù)號(hào)DNamevarchar(50)NOT NULLNoNo倉(cāng)庫(kù)名稱(chēng)庫(kù)存Stock字段名數(shù)據(jù)類(lèi)型是否可以為空Is PKIs FK中文名WNochar(10)NOT NULLYesYes商品編號(hào)Dnochar(10)NOT NULLYesYes倉(cāng)庫(kù)號(hào)

6、WAmountIntNOT NULLNoNo庫(kù)存數(shù)量入庫(kù)Stocking字段名數(shù)據(jù)類(lèi)型是否可以為空Is PKIs FK中文名SNochar(10)NOT NULLYesNo入庫(kù)號(hào)WNochar(10)NOT NULLNoYes商品編號(hào)Dnochar(10)NOT NULLNoYes倉(cāng)庫(kù)號(hào)SAmountIntNOT NULLNoNo入庫(kù)數(shù)量SDatesmalldatetimeNOT NULLNoNo入庫(kù)日期Suppliervarchar(50)NOT NULLNoNo送貨人姓名出庫(kù)Outbound字段名數(shù)據(jù)類(lèi)型是否可以為空Is PKIs FK中文名ONochar(10)NOT NULLYesNo

7、出庫(kù)號(hào)WNochar(10)NOT NULLNoYes商品編號(hào)Dnochar(10)NOT NULLNoYes倉(cāng)庫(kù)號(hào)OAmountintNOT NULLNoNo出庫(kù)數(shù)量ODatesmalldatetimeNOT NULLNoNo出庫(kù)日期Buyersvarchar(50)NOT NULLNoNo提貨人姓名二、 設(shè)計(jì)完整的E-R圖。商品編號(hào)型號(hào)生產(chǎn)廠家規(guī)格商品名稱(chēng)商品倉(cāng)庫(kù)號(hào)倉(cāng)庫(kù)倉(cāng)庫(kù)名稱(chēng)商品倉(cāng)庫(kù)庫(kù)存nm商品倉(cāng)庫(kù)入庫(kù)nm商品倉(cāng)庫(kù)出庫(kù)nm庫(kù)存關(guān)系：入庫(kù)關(guān)系：出庫(kù)關(guān)系：分E-R圖：總E-R圖：商品編號(hào)型號(hào)生產(chǎn)廠家規(guī)格商品名稱(chēng)商品倉(cāng)庫(kù)號(hào)倉(cāng)庫(kù)倉(cāng)庫(kù)名稱(chēng)入庫(kù)日期送貨人姓名入庫(kù)數(shù)量入庫(kù)入庫(kù)號(hào)nnnmmm庫(kù)存庫(kù)存數(shù)量

8、提貨人姓名出庫(kù)日期出庫(kù)數(shù)量出庫(kù)出庫(kù)號(hào)三、 進(jìn)行數(shù)據(jù)庫(kù)的邏輯設(shè)計(jì)。關(guān)系模式如下：商品（商品編號(hào)、商品名稱(chēng)、生產(chǎn)廠家、型號(hào)、規(guī)格） 商品編號(hào)為主鍵倉(cāng)庫(kù)（倉(cāng)庫(kù)號(hào)、倉(cāng)庫(kù)名稱(chēng)） 倉(cāng)庫(kù)號(hào)為主鍵庫(kù)存（商品編號(hào)，倉(cāng)庫(kù)號(hào)，庫(kù)存數(shù)量） （商品編號(hào)，倉(cāng)庫(kù)號(hào)）為主鍵，同時(shí)也分別為外鍵入庫(kù)（入庫(kù)號(hào)，商品編號(hào)，倉(cāng)庫(kù)號(hào)，入庫(kù)數(shù)量，入庫(kù)日期，送貨人姓名）（入庫(kù)號(hào)）為主鍵，（商品編號(hào)，倉(cāng)庫(kù)號(hào)）為外鍵出庫(kù)（出庫(kù)號(hào)，商品編號(hào)，倉(cāng)庫(kù)號(hào)，出庫(kù)數(shù)量，出庫(kù)日期，提貨人姓名）（出庫(kù)號(hào)）為主鍵，（商品編號(hào)，倉(cāng)庫(kù)號(hào)）為外鍵四、 完成物理數(shù)據(jù)庫(kù)的設(shè)計(jì)，包括數(shù)據(jù)庫(kù)、表、索引、視圖、完整性約束的物理設(shè)計(jì)。創(chuàng)建數(shù)據(jù)庫(kù)create database Ma

9、terial_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 (,毛巾,新家園,DA11,CX100);insert into Warevalues (,臺(tái)燈,新

10、家園,DA35,CX110);insert into Warevalues (,電冰箱,海爾,DA61,CX900);insert into Warevalues (,電視,東芝,DA52,CX901);insert into Warevalues (,長(zhǎng)城干紅,中糧華夏,DA25,CX108);insert into Warevalues (,筆記本,東芝,DA24,CX781); 創(chuàng)建倉(cāng)庫(kù)表use Material_DBcreate table Depot( /*定義倉(cāng)庫(kù)表*/DNo char(10) NOT NULL primary key,DName varchar(50) NOT N

11、ULL)use Material_DB /*插入倉(cāng)庫(kù)信息*/insert into Depotvalues (,勝昌);insert into Depotvalues (,德隆);insert into Depotvalues (,豪友);創(chuàng)建庫(kù)存表use Material_DBcreate table Stock ( /*定義庫(kù)存關(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 (

12、WNo) references Ware(WNo),foreign key (DNo) references Depot(DNo)創(chuàng)建入庫(kù)表use Material_DBcreate table Stocking( /*定義入庫(kù)關(guān)系*/SNo char(10) NOT NULL 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,SDat

13、e smalldatetime NOT NULL,Supplier varchar(50) NOT NULL)創(chuàng)建出庫(kù)表use Material_DBcreate table Outbound( /*定義出庫(kù)關(guān)系*/ONo char(10)NOT NULL primary key,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 smalldate

14、time NOT NULL,Buyers varchar(50) NOT NULL)完整約束的說(shuō)明：商品Ware的WNo商品編號(hào)要求在之間，WName不能取空；倉(cāng)庫(kù)Depot的DNo倉(cāng)庫(kù)編號(hào)要求在之間，DName不能取空；該倉(cāng)庫(kù)的商品型號(hào)統(tǒng)一為DA01-DA99格式，規(guī)格為CX100-CX199格式。use Material_DB /*定義表Ware唯一性的約束條件*/alter Table Ware add unique(WName,WFactory,WType,Spec);alter Table Ware /*定義WType的完整性約束條件：商品型號(hào)統(tǒng)一為DA01-DA99格式*/add

15、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 Material_DBalter table Ware /*添加對(duì)表Ware的WNo屬性的完整性約束，要求在之間*/add constraint C4 check(WNo like1-90-90-90-90-90-90-90-90-90-9);alter table Depotadd constraint C5

16、 check(DNo like20100-90-90-9);use Material_DBalter table Waredrop constraint C4;use Material_DBalter table Ware /*添加對(duì)表Ware的WNo屬性的完整性約束，要求在之間*/add constraint C4 check(WNo like1-90-90-90-90-90-9);創(chuàng)建視圖通過(guò)WLST（提取“物料視圖”每個(gè)字的頭字母）視圖來(lái)瀏覽庫(kù)存中相同編號(hào)商品的信息，其中包括：商品編號(hào)WNo、商品名WName、商品廠家WFactory、商品型號(hào)WType、商品規(guī)格Spec、商品數(shù)量SNu

17、m、create view VWname(商品編號(hào),商品名稱(chēng), 庫(kù)存數(shù)量)-視圖ASSELECT Stock.WNo,Ware.WName,sum(Stock.WAmount)from Ware,Stockwhere Stock.WNo=Ware.WNo group by Stock.WNo,Ware.WName;五、 實(shí)現(xiàn)商品的入庫(kù)，在商品入庫(kù)時(shí)通過(guò)觸發(fā)器或存儲(chǔ)過(guò)程同時(shí)完成商品庫(kù)存臺(tái)帳的更新。入庫(kù)表的觸發(fā)器create trigger stocking_into on stocking -這是入庫(kù)表的觸發(fā)器 after insert asbegin declare a char(10),b

18、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 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 value

19、s(100,200,2006-01-02,小明);insert into stocking values(101,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(104,200,2007-01-14,小風(fēng));insert into stocking values(105,200,2006-01-25,小明);insert into stocking

20、 values(106,200,2006-02-02,小明);insert into stocking 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 stocking values(110,200,2006-06-09,小敏);insert into stocking values(111,200,2005-06-02,小風(fēng));insert into st

21、ocking values(112,200,2005-06-02,小青);insert 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,小紅);insert into stocking values(116,200,2006-08-02,小風(fēng));insert into stocking values(117,200,2006-08-02,小明);insert i

22、nto stocking values(118,200,2005-09-04,小東);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-09-01,小明);insert into stocking values(122,200,2005-07-08,小敏);insert into stocking values(123,200,2005-04-11,小敏);in

23、sert into stocking values(124,200,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,200,2005-09-01,小蘭);insert into stocking values(128,200,2007-07-08,小蘭);insert into stocking values(129,200,2007-07-08,

24、小蘭);insert into stocking values(130,200,2007-07-08,小紅);六、 實(shí)現(xiàn)商品的出庫(kù)，在商品出庫(kù)時(shí)通過(guò)觸發(fā)器或存儲(chǔ)過(guò)程同時(shí)完成商品庫(kù)存臺(tái)帳的更新。出庫(kù)表觸發(fā)器create trigger outbound_d on Outbound -這是出庫(kù)表的觸發(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 wher

25、e 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=inserted.WNo and s.DNo=inserted.DNo) is not nullbeginupdate Stock set Stock.WAmount=Stock.WAmount-dwhere Stock.DNo=a and Stock.WNo=bendelsebeginprint 庫(kù)存量不夠rollbackendendel

26、sebeginprint 庫(kù)存中沒(méi)有這種產(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,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 val

27、ues(6,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,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,30,

28、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,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,30,2010-0

29、9-01,小敏);insert into Outbound values(19,40,2010-09-01,小敏);insert into Outbound values(20,50,2008-09-01,小紅);insert into Outbound values(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(24,50,2008-12-30,小

30、蘭);insert into Outbound values(25,20,2009-12-30,小蘭);insert into Outbound values(26,40,2008-12-08,小紅);insert into Outbound values(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 values(30,40,2010-09-01,小敏);七、

31、實(shí)現(xiàn)按商品名稱(chēng)查詢(xún)商品的入庫(kù)情況及目前的庫(kù)存量。/*方法一*/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 * FROM Stocking WHERE WNo IN (SELECT WNo FROM Ware WHERE WName =

32、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 as w,Stocking as siWHERE l.WName=電冰箱 and si.Sno=l.Sno and w.WN

33、ame=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)按入庫(kù)日期查詢(xún)商品的入庫(kù)情況及目前的庫(kù)存量。/*方法一 復(fù)合條件查詢(xún)*/select SNo,s.WNo,s.DNo,SAmount,SDate,Supplier,WAmount from Stock as s,Stockingwhere SDate=2006-01-02 00:00:00 and s.WNo=Stocking.WN

34、o and s.DNo=Stocking.DNo/*方法二建立視圖查詢(xún)*/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.DNo=Stocking.DNo and SDate=2006-01-02 00:00:00with check option;/*方法三相關(guān)子查詢(xún)*/selec

35、t 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)按商品名稱(chēng)查詢(xún)商品的出庫(kù)情況及目前的庫(kù)存量。/*方法一*/select ONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmount from Stock as s,Outbound,Warewher

36、e WName=長(zhǎng)城干紅 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.DNo,OAmount,ODate,Buyers,WAmount,Ware.WNameFROM Ware,Stock,OutboundWHERE Stock.WNo=Outbound.WNo AND Stock.DNo=Outb

37、ound.DNo AND Ware.WNo=Stock.Wnoselect ONo,WNo,DNo,OAmount,WName,ODate,Buyers,WAmount from W_S_Owhere WName=長(zhǎng)城干紅 /*方法三*/declare WName varchar(32)set WName=長(zhǎng)城干紅select *from Outbound where WNo=(select WNo from Ware where WName=WName);/*方法四*/select ONo,s.WNo,s.DNo,OAmount,WName,ODate,Buyers,WAmount from

38、 Stock as s,Outbound,Warewhere exists (select *where WName=長(zhǎng)城干紅 and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo and Ware.Wno=s.Wno);十、 實(shí)現(xiàn)按出庫(kù)日期查詢(xún)商品的出庫(kù)情況及目前的庫(kù)存量。/*方法一*/ 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

39、.DNo=Outbound.DNo/*方法二*/* 創(chuàng)建視圖*/CREATE VIEW SO(ONo,WNo,DNo,OAmount,ODate,Buyers,WAmount)ASSELECT ONo,Stock.WNo,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

40、 0:00:00/*方法三*/select ONo,s.WNo,s.DNo,OAmount,ODate,Buyers,WAmount from Stock as s,Outboundwhere exists (select * where ODate=2010-01-02 0:00:00 and s.WNo=Outbound.WNo and s.DNo=Outbound.DNo);十一、 按時(shí)間段查詢(xún)商品庫(kù)存情況。/*時(shí)間在-01-02和-01-02之間的入庫(kù)出庫(kù)情況*/*方法一*/select w.WName as 商品,sum(SAmount) as 入庫(kù)數(shù)量from Stocking a

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

42、g as s ,Ware as w where w.WNo=s.WNo and SDate in(select SDatefrom Stocking as swhere SDate between 2006-01-02 and 2010-01-02)group by w.WNameselect w.WName as 商品,sum(OAmount) as 出庫(kù)數(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

43、-02 and 2010-01-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 between 2006

44、-01-02 and 2010-01-02 and Outbound.WNo=Ware.WNo ) group by WName十二、 實(shí)現(xiàn)分別按年、季度和月對(duì)入庫(kù)商品數(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,Warewhere SDate between 2005-3-1 0:00

45、: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 WName,sum(SAmount) Ssumfrom Stocking JOIN Ware on Stoc

46、king.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_SumJasselect WName,sum(SAmount) Ssumfrom Stocking

47、JOIN Ware on Stocking.WNo=Ware.WNowhere SDate between 2005-3-1 0:00:00 and 2005-5-31 0:00:00group 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 St

48、ocking,Ware where exists (select * where SDate between 2005-3-1 0:00:00 and 2005-5-31 0:00:00 and Stocking.WNo=Ware.WNo ) group by WName/*月：*/select WName,sum(SAmount) Ssum from Stocking,Ware where exists (select * where month (SDate)=6 and Stocking.WNo=Ware.WNo and year (SDate)=2005 ) group by WNam

49、e十三、 實(shí)現(xiàn)分別按年、季度和月對(duì)出庫(kù)商品數(shù)量的統(tǒng)計(jì)。/*第一種方法：復(fù)合條件查詢(xún)按年：*/select WName,sum(OAmount) Osumfrom Outbound,Warewhere year (ODate)=2010 and Outbound.WNo=Ware.WNogroup by WName/*按季度：*/select WName,sum(OAmount) Osumfrom Outbound,Warewhere ODate between 2010-09-01 0:00:00 and 2010-11-30 0:00:00 and Outbound.WNo=Ware.WNo

50、group by WName/*按月：*/select WName,sum(OAmount) Osumfrom Outbound,Warewhere month (ODate)=9 and Outbound.WNo=Ware.WNo and year (ODate)=2010group by WName/*第二種方法：視圖查詢(xún)按年：*/create view year_outb(商品,出庫(kù)數(shù)量)as select WName,sum(OAmount) Osumfrom Outbound as o JOIN Ware as w on o.WNo = w.WNowhere year (ODate)=2010 group by WNamewith check option;/*按季度：*/create view jidu_outb(商品,出庫(kù)數(shù)量)as select WName,sum(OAmount) Osumfrom Outbound as o JOIN Ware as w on o.W