E_market数据库物理实现

1、将当前数据库设置为e.market,以便在e_market数据库屮创建表 usemaster当前指向的操作的数据库goifexists(select*fromsysdatabaseswherename=,e_market1)dropdatabasee_m4tket-删除数据库-创建数据库createdatabasee_marketonprtmary-主文件组(name= 1 e_market_data 1 , -逻辑文件名-物理文件名filename= 1 e : sql 1 e_market_data . mdf 1 ,size=5mb,-初始人小maxsize = 100mb, -最大容鼠

2、filegrowth=15%增长率),filegroupfg-创建了 一个次文件组(name='e_market2_da,filenamee:sql1e_marke12_dmt3.ndf',size=4mbzfilegrowth=10%)logon-日志文件(name=1e_market_log1,filename=1e:sql1e_market_log.ldf1,size=5mb,filegrowth = 0 未启用自动増长name=1e_market2_log1zfilename=1e:sqlle_market2_log.ldf1,size=3mb,filegrowth=1

3、0%,maxsize=100mb)gousee_marketgoifexists(select*fromsysobjectswheren且me='user "fo1 ) droptableuserlnfocreatetableuserlnfo(useridvarchar(20) notnull,-会员号 userpwdvarchar (50) notnull, -会员密码 usernamevarchar (50) notnull,-会员名 genderintnotnull, -性别emailvarchar(50),邮箱useraddressvarchar (200 ),-联系

4、地址phonevarchar (20) notnull-联系电话goifexists(select*fromsysobjectswhere'commoditysort1) droptablecommoditysortcreatetablecommoditysort(sort idintidensity (1, 1) notnull, -种类号sortnamevarchar (50) notnull, 中类名)goifexists(select*fromsysobjectswherename='commodityinfo 1) droptablecommoditylnfocrea

5、tetablecommodityinfo(commodityldi nt identity (1,1) notnull, -商胡编号sortldintnotnull,-种夹编号commoditynamevarchar (50) notnull, -商品名称pictureimagenull,-一商品图片，inpricefloa tn otnull, 一商品进货彳介outpricefloatnotnull,-商品售价amountintnotnull, -商品数量)goifexists(select*fromsysobjectswherename=,orderinfo 1)droptableorde

6、rlnfocreatetableorderlnfo(order i di nt identity (1,1) notnull, -订单编号userldvarchar (20) notnull,会员号commodityldintnotnull, -商品编号amountintnotnull,商品数量paymoneyf loatnotnull, 一一商胡进货仔7paywayvarchar (50) notnull, -商胡售价ordertimedatetimenotnull,confirmintnotnull, -确认收货sendgoodsintnotnull, -确认发货)goaltertable

7、userlnfo addconstraintpk_useridprimarykey (userid),constraintck_userpwdcheck(len(userpwd)>=6),constraintck_gendercheck(gender=0 orgender=l),constraintdf_genderdefault(0)forgender,constraintck_emailcheck(emaillike1%1)goaltertablecommodityinfoaddconstraintpk_commoditytdprimarykey (commodityid)goalt

8、ertableorderlnfoaddconstraintpk_orderidprimarykey (orderld),constraintfk_useridforeignkey(userid)referencesuserinfo(userid),constraintfk_commociityidforeignkey(commodityid)referencescommodityin fo (commodityid),constraintdf_paywaydefault ( 1 网二银行 1 ) forpayway,constraintck_confirmcheck(confirm=0 orc

9、onfirm=l),constraintdf_confirmdefault(0)forconfirm,constraintck_sendgoodscheck(sendgoods = 0 orsendgoods"), constraintdf_sendgoodsdefault(0)forsendgoodsgoaltertablecommoditysortaddconstraintpk_sortidprimarykey (sortld)goaltertablecommoditylnfoaddconstraintfk_sortidforeignkey (sortld)referencesc

10、ommoditysort(sortld) go select * fromcommodityinfo selectfromcommoditysort select * fromorderinfo select * fromuserinfoinsertintocommoditysortvalues ( 1 手方l数码 1)insertintocommodity sort values ( 1 图书音像 1 ) insertintocommoditysortvalues ('家用电器 1)insertuserlnfovalues ( 1 yoyo1 , 1 iloveyou1 z '

11、;卢晓风',1, 1 lxf1 , t匕京海淀 1 , insertintouserlnfovalues ( * feiyang ' , * hongmei ','段飞扬',0, * dfyqq. com * , * 吉林松江jinsertintouserlnfovalues ( * daxia *, * langyashan ','赵可以 * , 0, ' zky123 . com* , 1 上海长宁1 ,insertintocommodity

12、info(sortld,commodityname,inprice,outprice,amount) values (1,'索谢z3 ' , 3300, 42999, 100)insertintocommodityln£o(sortld,commodityname,inprice,outprice,amount) values (2, javascript扌旨南'，20,50,200)insertintocommodityinfo(sortld,commodityname,inprice,outprice, amount) values (1j 华为荣燿c畅玩，,500,799, 80)insertintoorderinfo(userid,commodityid,amount,paymoney,payway,ordertime fsendgoods,confirm)values(1 yoyo 1,1,2,8 589,defaultj 2014-3-26' , lf1)insertintoorderinfo(userid,commodityid,amount,paymoney,payway,ordertime ,confirm,sendgoods)values ( ' feiyang' ,