倉(cāng)庫(kù)訂單模塊數(shù)據(jù)庫(kù)設(shè)計(jì)

1、倉(cāng)庫(kù)訂單模塊數(shù)據(jù)庫(kù)設(shè)計(jì)一、關(guān)系圖I t_contactinfo f dd CHAR(IO)cdate VARCHAR(50)cdraftsman VARCHAR(20)amount INT(ll)/ gid CHAR(IO)gname VARCHAR(50)customername VARCHAR(50) td VARCHAR(20)AddrVARCHAR(200)I t_goodsv gnum INT(ll)二、數(shù)據(jù)字典，oid CHAR( 10) otime VARCHAR(20)status VARCHAR(20)cidCHAR(lO)I t_orderinfo I t orderdet

2、ail did CHAR(IO) dnum IhTT(ll) unitprice INT(ll) oid CHAR( 10)gidCHAR(lO)表1合同信息表t_contactinfo字段名名稱數(shù)據(jù)類型為空備注cid合同編號(hào)char(10)NPKcdate合同日期varchar(50)Ncdraftsman合同發(fā)起人varchar(20)Namount合同金額intNcustomername客戶姓名varchar(50)Ntel聯(lián)系 varchar(20)NAddr發(fā)貨地址varchar(200)表2訂單信息表t_orderinfo字段名名稱數(shù)據(jù)類型為空備注oid訂單編號(hào)char (10)N

3、PKotimeF單日期varchar(20)Nostatus訂單狀態(tài)varchar(20)Ncid所屬合同編號(hào)char (10)NFK表3訂單明細(xì)表t_orderdetail字段名名稱數(shù)據(jù)類型為空備注did明細(xì)編號(hào)char (10)NPKdnum訂購(gòu)數(shù)量intNunitprice單價(jià)intNoid所屬訂單編號(hào)char (10)NFKgid商品編號(hào)char(10)NFK表4商品信息表t_goods字段名名稱MrMZ- Htll數(shù)據(jù)類型為空備注gid商品編號(hào)char (10)NPKgname商品名稱varchar(50)Ngnum庫(kù)存數(shù)量intN三、數(shù)據(jù)庫(kù)操作（一）、創(chuàng)立數(shù)據(jù)庫(kù)create dat

4、abase storedb;2 use storedb;(二)、建表-合同信息表CREATE TABLE t_contactinfo (cid char(10) NOT NULL,cdate varchar(50) NOT NULL, cdraftsman varchar(20) NOT NULL, amount int NOT NULL, customername varchar(50) NOT NULL, tel varchar(20) , Addr varchar(200), PRIMARY KEY (cid);-商品信息表CREATE TABLE t_goods (gid char(1

5、0) NOT NULL,gname varchar(50) NOT NULL,gnum int NOT NULL, PRIMARY KEY (gid) );訂單信息表CREATE TABLE t orderinfo (oid char(10) NOT NULL,otime varchar(20) NOT NULL, ostatus varchar (20) NOT NULL, cid char(10) NOT NULL,3PRIMARY KEY (oid),CONSTRAINT fk_contact_order FOREIGN KEY(cid) REFERENCES t_contactinfo

6、(cid);-創(chuàng)立訂單明細(xì)表CREATE TABLE t_orderdetail (did char(10) NOT NULL,dnum int NOT NULL,unitprice int NOT NULL,oid char(10) NOT NULL,gid char(10) NOT NULL,PRIMARY KEY (did),CONSTRAINT fk_detail_order FOREIGN KEY(oid) REFERENCES t_orderinfo(oid),CONSTRAINT fk_detail_goods FOREIGN KEY (gid) REFERENCES t_goo

7、ds (gid);(三)、插入數(shù)據(jù)一插入商品表INSERT INTO storedb. t_goods(gid, gname, gnum)VALUES ( gOOOl,華碩飛行堡壘 7, 50),C g0002,，聯(lián)想小新 Air4 100),C g0003聯(lián)想拯救者，40),C g0004, ,J 蘋果 AppleMQD32CH，, 60);-插入合同信息表INSERT INTO storedb. t_contactinfo(cid, cdate, cdraftsman, amount, customername, tel, Addr)VALUES ( c000r/ 2019-09-08,林

8、特通1000000,聯(lián)想北京京),4 (c0002,，2019-10-25,趙安生，2000000,華碩北京京),(c0003, 2019-11-02,鐘宇鵬，500000,蘋廣州);-插入訂單信息表INSERT INTO storedb. t orderinfo(oid,otime, ostatus, cid)VALUES ( o0001, 2019-10-26,完成，c0001),(o0002, 2019-10-28,執(zhí)行中2 c0001),(o0003, 2019-11-15,執(zhí)行中，c0002),(o0004, 2019-11-26 / 執(zhí)行中，c0003);插入訂單明細(xì)INSERT INTO storedb.torderdetail(did, dnum, unitprice, oid, gid)VALUES ( d000