SQL数据库与表的创建.doc

1创建数据库-创建数据库create database db_NetStore -网上书城on primary(name=db_NetStore,filename=f:datadb_NetStore.mdf,size=10MB,maxsize=50MB)log on(name=db_NetStore_log,filename=f:datadb_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) begin select * from Member where UserName=UserName and Password=Password endgo2、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 -1else begin insert Member(UserName,Password,RealName,Sex,Phonecode,Email,Address,PostCode) values(UserName,Password,RealName,Sex,Phonecode,Email,Address,PostCode) return 1 endgo3、proc_GetUI存储过程用来从用户会员表Member中，查询指定用户ID代号的相关信息：Create proc proc_GetUI( MemberID int)Asif exists(select * from Member where MemberID=MemberID ) begin select * from Member where MemberID=MemberID endgo4、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 Member set UserName=UserName, Password=Password, RealName=RealName, Sex=Sex, Phonecode=Phonecode, Email=Email, Address=Address, PostCode=PostCode where 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)-精品推荐 begin select top 4 * from Booklnfo where isrefinement=1 endelse if(Deplay=2)-特价商品 begin select top 4 * from Booklnfo where IsDiscount=1 endelse if(Deplay=3)-热销商品 begin select top 4 * from Booklnfo where IsHot=1 endGo6、 proc_GIList存储过程用于从商品信息表Booklnfo中分类位索所有商品的相关信息:Create proc proc_GIList( ClassID int,-商品类别号 Deplay int -最新商品-精品推荐-特价商品-热梢商品的代号)