mybookshop.sql

简单实现的商品购物系统

--drop database mybookshop
create database mybookshop
go
use mybookshop
go
--drop table consignee
----------------收货人信息总表------------------
create table consignee
(
id int identity(1,1),
orderid varchar(100) primary key,  --订单流水号
username varchar(20) not null,--用户名
consignName varchar(20) not null,--收货人名
sex varchar(2),--性别
address varchar(100) not null, --地址
tel varchar(25) not null,--电话
zip varchar(12),--邮编
qq varchar(15),--
email varchar(50),
submittime varchar(30),--下单时间
consigndate  varchar(30)default('暂无'), --发货日期
payclass varchar(30),--银行
ispay varchar(10) default('未付款'),--付款
issales varchar(10) default('未发货') --发货
)
--delete consignee
--select * from consignee
-- where username='xiehuajun'
   
--drop table orders
create table orders   --订单明细表
(
id int identity(1,1) primary key,
orderid varchar(100),  --订单号 (上面的订单总表号)
ordername varchar(40),--订单图书名
price float,  --单价
amount int, --数量
totalprice float not null, --应付总价
) 
GO


select * from orders 


--------------------图书类别表---------------------
create table bookclass
(
bookclassno int primary key IDENTITY(1,1),
classname varchar(20),
)
GO
--select * from bookclass

--drop table bookclass

GO
select * from bookclass

---------------------图书管理员表------------------
--drop table bookadmin
create table bookadmin
(
id int  IDENTITY(1,1) ,
adminuser varchar(20) primary key,
adminpass varchar(15) not null
)
GO
select * from bookadmin
--drop table userinformation
-----------------用户信息表---------------------------
create table userinformation
(
userid int  IDENTITY(1001,1) not null,
username varchar(50) primary key,
userpassword varchar(25) not null,
names varchar(50),
sex varchar(8),
address varchar(100) not null,
tel varchar(25) not null,
post varchar(10),
email varchar(50) not null,
regip varchar(20),
regtime datetime default(getdate()),
images varchar(100),
home varchar(100),
qq varchar(15),
userlevel varchar(5),
other varchar(1000)
)
GO
select * from userinformation
 -- drop table userPoll
create table userPoll
(
   id int  IDENTITY(1001,1) not null,
   userName varchar(35),
   Poll  varchar(4),
   pollDate datetime default(getdate())
)
insert into userPoll values('xiehuajun','A',getdate())
insert into userPoll values('xiehuajun','B',getdate())
insert into userPoll values('xiehuajun','C',getdate())
insert into userPoll values('xiehuajun','D',getdate())
select * from userPoll where Poll='A'
--------------------------用户等级表---------------------
create table userlevel
(
userlevel varchar(5) primary key not null,--用户等级
levelname varchar(25) not null,--名称
levelagio float not null,--打几折
level_totalprice float null,--消费金额
)
GO
select * from userlevel
--drop table userbbs
-----------------------用户留言表--------------------