创建电子商务数据库.doc

计算机工程系实验报告 成 绩：课程名称：数据库系统概论 实验项目名称：创建电子商务数据库 指导教师（签名）： 班级：计算机142班 姓名：张荣 学号：20140902239 实 验 目 的: 创建一个完善的电子商务数据库，并具有良好的开发性和维护性实 验 环 境:SQL server 2008实 验 内 容 及 过 程: -1.创建电子商务数据库（E_Market）use masterGoif exists (select * from sysdatabases where name=E_Market)drop database E_MarketGOCREATE database E_Marketon primary( name=E_Market, filename=c:E_Market.mdf, size=10MB, maxsize=100MB, filegrowth=10%)GO-2.创建E_Market的基本表-2.1 用户信息表 UserInfouse E_MarketGOif exists (select * from sysdatabases where name=UserInfo)drop database UserInfoGOCREATE TABLE UserInfo( UserID char(20) not null,-用户编号 UserPwd char(50)not null,-用户密码 Username char(50) not null,-用户名 Usersex char(2) not null,-性别 UserAddress char(50) not null,-地址 UserEmail char(50) not null,-邮箱 UserPhone char(20) not null,-联系电话)GO-2.2商品类别表 CommoditySortuse E_MarketIF EXISTS(select * FROM SYSOBJECTS WHERE name=CommoditySortGOCREATE TABLE CommoditySort( SortID int identity(1,1) not null,-类别编号 SortName char(50) not null,-类别名称)GO-2.3商品信息表 CommodityinfoUSE E_MarketIF EXISTS(SELECT * FROM SYSOBJECTS WHERE name=CommodityInfoGOCREATE TABLE CommodityInfo( CommodityId int identity (1,1) not null, SortId int not null, CommodityName char(50) not null, Picture image null, InPrice float not null, OutPrice float not null,)GO-2.4订单信息表 OrderInfoUSE E_MarketIF EXISTS(SELECT * FROM SYSOBJECTS WHERE name=OrderInfoGO CREATE TABLE OrderInfo( OrderId int identity(1,1) not null,-订单编号 UserId char(20) not null,-用户编号 CommodityId int not null,-商品编号 Amount int not null,-购买数量 PayMoney float not null,-支付金额 Payway char(50) not null,-支付方式 Ordertime datetime not null,-购买日期 Confirm int not null,-确认收货 SendGoods int not null, -确定发货)GO-2.5进货表 stockgoodInfoUSE E_MarketIF EXISTS(SELECT * FROM SYSOBJECTS WHERE name=stockgoodInfoGO CREATE TABLE stockgoodInfo( stockgoodNAME char(50)not null,-货物编号 stockgoodnumb char(50)not null,-货物数量 stockgoodprice float not null,-进货价格)GO-2.6售货表 sellgoodInfoUSE E_MarketIF EXISTS(SELECT * FROM SYSOBJECTS WHERE name=sellgoodInfoGO CREATE TABLE sellgoodInfo( sellgoodId char (50) not null-货物编号 sellgoodprice char not null -销售价格 sellgoodnumb int (50) not null-销售数量 sellgoodgain float not null-销售盈利)Go-2.7销售员表 sellerinfoUSE E_MarketIF EXISTS(SELECT * FROM SYSOBJECTS WHERE name=sellerinfoGO CREATE TABLE SellerInfo( sellername char(50) not null-销售员名字 sellersex char(2) not null-销售员性别 sellerPN char(20) not null-销售员电话 selleracm float not null-销售员业绩)GO-3.创建约束-3.1 修改用户表alter table UserInfoadd constraint PK_UserId primary key (userId), constraint CK_UserPwd check(len(userPwd)=6), constraint CK_UserSex check(Usersex=男 or Usersex=女), constraint CK_UserEmail check(UserEmail like 123456,Go-3.2 修改商品类别表alter table CommoditySortadd constraint PK_SortId primary key(SortId),Go-3.2 修改商品信息表alter table Commodityinfoadd constraint PK_CommodityId primary key(CommodityId)，Go -3.4修改订单表alter table orderInfoadd constraint PK_OrderId primary key(OrderId), constraint FK_UserId foreign key(UserId), references UserInfo (UserId), constraint FK_CommodityId foreign key(CommodityId), references commodityInfo(CommodityId), constraint DF_Payway DEFAULT(网上银行) for PayWay, constraint CK_Confirm check(Confirm=0 or confirm=1), constraint DF_Confirm DEFAULT(0) FOR Confirm, constraint CK_SendGoods check(SendGoods=0 or SendGoods=1), constraint DF_SendGoods DEFAULT(0) FOR SendGoods,GO-3.5修改进货表alter table stockgoodInfoadd constraint PK_stockgoodId primary key(stockgoodId),GO-3.6修改售货表alter table sellgoodInfoadd constraint PK_sellgoodId primary key(sellgoodId),GO-3.7修改销售员表alter table sellerinfoadd constraint PK_sellername primary key(sellername), constraint CK_sellerSex check(sellerSex=男 or sellerSex=女), constraint UQ_sellerPN unique(sellerPN),GO -查询基本表select * from UserInfoselect * from CommoditySortselect * from CommodityInfoselect * from orderInfoselect * from stockgoodInfoselect * from sellgoodInfoselect * from sellerinfoGo-5.插入数据-5.1 UserInfoinsert into UserInfovalues (huhu,111111,虎虎,男,北京,111111,010-987654321)insert into UserInfovalues (tingting,211111,婷婷,女,上海,211111,010-123456789)-5.2 Commoditysortinsert into CommoditySortselect 图书音像union select 汽车用品unionselect 家具用品-5.3 CommodityInfoinsert into CommodityInfovalues(1,iphone6,null,5000,6000,100)insert into CommodityInfoValues(2,数据库原理,null,40,60,50)insert into CommodityInfovalues (3,坐垫,null,1000.2000,30)-5.4 OrderInfoInsert into OrderInfovalues (huhu,1,5,300