资源描述：
《数据库 商品销售系统》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、创建数据库：CREATEDATABASEgoodstradeONPRIMARY(NAME='goodstrade',FILENAME='d:goodstrade.mdf',SIZE=10240KB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)LOGON(NAME='test_log',FILENAME='d:goodstrade_log.ldf',SIZE=1024KB,MAXSIZE=5120KB,FILEGROWTH=1024KB)GO创建表base：createtablebase(bnochar(20)primarykey，bnamechar(2
2、0)null,bsquintnull)Bnobnamebsqu1234创建表goods：createtablegoods(gnochar(20)primarykey,gnamechar(20)null,pricechar(20)NULL，propricechar(20)NULL，gnumintnull，SalesDegreechar(4)null，bnochar(20)null)gnoganmepricepropricegnumSalesDegreebno0001洗衣粉88010002肥皂38010003香皂48030004洗发水12804createtriggertri_up
3、dateondealingforupdateasbegindeclare@todaydatetime;declare@monthagodatetime;declare@tdealingsumint;select@today=getdate();select@monthage=(selectdateadd(month,-1,getdate()));select@tdealingsum=(selectsum(dealingnum)fromdealingwheredealingdatebetween@@monthagoand@@todaygroupbygno)if(@tdealing
4、sum>=10)updategoodssetSalesDegree='热销'wheredealing.gno=goods.gno;if(@tdealingsum<10and@tdealingsum>=3)updategoodssetSalesDegree='正常'wheredealing.gno=goods.gno;if(@tdealingsum<3)updategoodssetSalesDegree='冷销'andsetproprice=price*0.8wheredealing.gno=goods.gno;end;创建表consumer：createtableconsume
5、r(cnochar(20)primarykey,cnamechar(20)null,csexchar(20)nullctelchar(11)null)cnocnamecsexctel001张民男002李月女003张兰女004王克男005刘海男创建表seller：createtableseller(snochar(20)primarykey,snamechar(20)null,ssexchar(20)nullsalaryintnullstelchar(11)null)snosnamessexsalarystel001002003004创建表deal：createtabledeal
6、ing(gnochar(20)null,snochar(20)NULL,cnochar(20)null,dealingnumintnull,DealingDatedatetimeNOTNULL,DealingPricemoneyNOTNULL,Foreignkeygnoreferencesgoods(gno)Foreignkeysnoreferencesseller(sno))gnosnocnamedealingnumdealingdateDealingPrice0001000200030004createtriggerinsert_dealingondealingforins
7、ert,updateasbegindeclare@tdealingnumint;declare@tgnochar(20);select@tdealingnum=dealingnumfrominserted;select@tgno=gnofrominserted;endCREATETRIGGERtr_insertdealONdealingFORINSERT,UPDATEASBeginDECLARE@tgnochar(20);declare@tdealingnumint;SELECT@tgno=
