1．创建数据库--创建数据库create database db_NetStore --网上书城on primary(name='db_NetStore',filename='f:\data\db_NetStore.mdf',size=10MB,maxsize=50MB)log on(name='db_NetStore_log',filename='f:\data\db_NetStore.ldf,size=5MB,maxsize=25MB)2.创建表①create table Member --会员信息表(MemberID int not null primary key,--会员ID UserName varchar(50)not null,--会员登录名Password varchar(50)not null,--会员登录密码RealName varchar(50)not null,--会员真实姓名Sex bit not null,--会员性别Phonecode varchar(20)not null,--会员电话号码Email varchar(50)not null,--会员E-mail地址Address varchar(200)not null,--会员详细地址PostCode char(10)not null,--邮编LoadDate datetime not null,--创建时间)②create table Admin --管理员信息表(AdminID int not null primary key,--管理员ID AdminName varchar(50)not null,--管理员登录名Password varchar(50)not null,--管理员登录密码RealName varchar(50)not null,--管理员真实姓名Email varchar(50)not null,--管理员E-mail地址LoadDate datetime not null,--创建时间)③create table Class --商品类别表(ClassID int not null primary key,--商品类别ID ClassName varchar(50)not null,--商品类别名称CategoryUrl varchar(50)not null,--商品类别图片)④create table BookInfo --商品信息表(BookID int not null primary key,--商品ID ClassID int not null,--商品类别ID BookName varchar(50)not null,--商品名称BookIntroduce ntext not null,--商品介绍Author varchar(50)not null,--主编Company varchar(200)not null,--出版社BookUrl varchar(200)not null,--商品图片MarketPrice float not null,--市场价HotPrice float not null,--热销价Isrefinement bit not null,--是否推荐IsHot bit not null,--是否热销IsDiscount bit not null,--是否打折LoadDate datetime not null,--进货日期)⑤create table Image --图片信息表(ImageID int not null primary key,--图片ID ImageName varchar(50)not null,--图片名称ImageUrl varchar(200)not null,--图片地址)⑥create table OrderInfo --订单信息表(OrderID int not null primary key,--订单ID BooksFee float not null,--商品费用ShipFee float not null,--运输费用TotalPrice float not null,--订单总费用ShipType varchar(50)not null,--运输方式ReceiverName varchar(50)not null,--接收人姓名ReceiverPhone varchar(20)not null,--接收人电话ReceiverPostCode char(10),not null,--接收人邮编ReceiverAddress varchar(200)not null,--接收人详细地址ReceiverEmail varchar(50)not null,--接收人E-mail地址OrderDate datetime not null,--订单生成日期IsConfirm bit not null,--是否确认IsSend bit not null,--是否发货IsEnd bit not null,--收货人是否验收AdminID int not null,--跟单号ID代号ConfirmTime datetime not null,--确认时间)⑦create table Detail --订单明细表(DetailID int not null primary key,--订单详细表号BookID int not null,--商品代号Num int not null,--商品数量TotalPrice float not null,--该商品总金额Remark varchar(200)not null,--备注OrderID int not null,--该项对应的订单号)⑧create table LeaveWorld --用户留言表(LWID int not null primary key,--ID代号LWUid nvarchar(50)not null,--留言人姓名LWSubject nvarchar(50)not null,--留言主题LWContent ntext not null,--留言内容LWIP nvarchar(20)not null,--留言人IP地址LWDateTime datetime not null,--留言时间)⑨create table Reply --回复留言表(RID int not null primary key,--ID代号RUName nvarchar(50)not null,--回复留言人姓名RContent ntext not null,--回复留言内容RIP nvarchar(20)not null,--回复留言人IP地址RDateTime datetime not null,--回复留言时间)3存储过程删除存储过程--if object_id('proc_UserLogin') is not null--drop proc proc_UserLogin--go1、proc_UserLogin存储过程用于从数据表Member中获取指定用户名和密码的数据信息：Create proc proc_UserLogin(@UserName varchar(50),@Password varchar(50))As--判断查询信息是否存在if exists(select*from Member where UserName=@UserName and Password=@Password) beginselect*from Memberwhere UserName=@UserName and Password=@Passwordendgo2、proc_AddUser存储过程用来向会员表Member中插入数据信息：Create proc proc_AddUser(@UserName varchar(50),@Password varchar(50),@RealName varchar(50),@Sex bit,@Phonecode char(20),@Email varchar(50),@Address varchar(200),@PostCode char(10))As--通过用户名判断该用户是否存在，如果存在，则返回-1；不存在，则插入该用户信息，并返回if exists(select*from Member where UserName=@UserName )return-1elsebegininsert Member(UserName,Password,RealName,Sex,Phonecode,Email,Address,PostCode)values(@UserName,@Password,@RealName,@Sex,@Phonecode,@Email,@Address,@PostCode) return 1endgo3、proc_GetUI存储过程用来从用户会员表Member中，查询指定用户ID代号的相关信息：Create proc proc_GetUI(@MemberID int)Asif exists(select*from Member where MemberID=@MemberID )beginselect*from Memberwhere MemberID=@MemberIDendgo4、proc_ModifyUser存储过程是通过用户ID代号修改会员信息表Member中的相关信息：Create proc proc_ModifyUser(@UserName varchar(50),@Password varchar(50),@RealName varchar(50),@Sex bit,@Phonecode char(20),@Email varchar(50),@Address varchar(200),@PostCode char(10),@MemberID int)Asupdate Memberset UserName=@UserName,Password=@Password,RealName=@RealName,Sex=@Sex,Phonecode=@Phonecode,Email=@Email,Address=@Address,PostCode=@PostCodewhere MemberID=@MemberIDgo5、proc_DeplayGI存储过程用于从商品信息表BookInfo中分类检索相关商品的前4条信息：--当@Deplay=1时，从数据表Booklnfo中查询Isrefinement(精品推荐)字段为1的商品信息--当@Deplay=2时，从数据表Booklnfo中查询IsDiscount(特价商品)字段为1的商品信息--当@Deplay=3时，从数据表Booklnfo中查询IsHot(热销商品)字段为1的商品信息Create proc proc_DeplayGI(@Deplay int)asif(@Deplay=1)--精品推荐beginselect top 4 *from Booklnfowhere isrefinement=1endelse if(@Deplay=2)--特价商品beginselect top 4 *from Booklnfowhere IsDiscount=1endelse if(@Deplay=3)--热销商品beginselect top 4 *from Booklnfowhere IsHot=1endGo6、proc_GIList存储过程用于从商品信息表Booklnfo中分类位索所有商品的相关信息: Create proc proc_GIList(@ClassID int,--商品类别号@Deplay int--最新商品--精品推荐--特价商品--热梢商品的代号)asif(@ClassID=0)beginif(@Deplay=1)--最新商品beginselect*from Bookinfowhere DATEDIFF(day,LoadDate,getdate())<7endelse if(@Deplay=2)--精品推荐beginselect*from Booklnfowhere Isrefinement=1endelse if(@Deplay=3)--特价商品beginselect*from Bookinfowhere IsDiscount=1endelse if(@Deplay=4)--热销商品beginselect*from Bookinfowhere IsHot=1endendelsebeginselect*from Bookinfowhere ClassID=@ClassIDendgo7、proc_GCN 存储过程用于从商品类别表Class中获取指定商品类别号的商品类别名：create proc proc_GCN(@ClassID int)asif exists(select*from Class where ClassID=@ClassID)beginselect ClassName from Classwhere ClassID=@ClassIDendgo8、Proc_AddOI存储过程用于向订单信息表OrderInfo中插入订单信息，并输出订单ID代号：Create proc proc_AddOI(@BooksFee float,@ShipFee float,@ShipType varchar(50),@Name varchar(50),@Phone varchar(20),@PostCode char(10),@Address varchar(200),@Email varchar(50),@OrderID int output)asInsert into OrderInfo(BooksFee,ShipFee,TotalPrice,ShipType,ReceiverName,ReceiverPhone,ReceiverPostCode,ReceiverAd dress,ReceiverEmail)values(@BooksFee,@ShipFee,(@BooksFee+@ShipFee),@ShipType,@Name,@Phone,@PostCode,@Addre ss,@Email)select @OrderID=@@identitygo9、proc_AddODetail存储过程用于向订单明细表Detail中插入订单中商品的详细信息：create proc proc_AddODetail(@BookID int,@Num int,@OrderID int,@TotailPrice float,@Remark varchar(200))asInsert into Detail(BookID,Num,OrderID,TotailPrice,Remark)values(@BookID,@Num,@OrderID,@TotailPrice,@Remark)Go10、Proc_SearchOI存储过程用于从订单信息表OrderInfo中查找详细订单信息:Create proc proc_SearchOI(@OrderID int,--订单号@NF int,--是否通过接收人的姓名查询@Name varchar(50),--接收人的姓名@IsConfirm int,--是否确认@IsSend int,--是否发送@IsEnd int--接收人是否已收到商品)asdeclare@Msql varchar(1024)set @Msql='select * from OrderInfo whereIsConfirm='+Convert(varchar(20),@IsConfirm)+'and IsSend='+Convert(varchar(20),@IsSend)+'and IsEnd='+Convert(varchar(20),@IsEnd)+''if @OrderID>0beginset @Msql=@Msql+'and OrderID='+convert(varchar(20),@OrderID) endif @NF>0beginset @Msql=@Msql+'and ReceiverName=''+convert(varchar(50),@Name)+' endexec(@Msql)go。