b2c電子商務網站資料庫

create database b2cshopdb

use b2cshopdb

create table users

(

userid int identity(1,1) primary key,

username varchar(32),

password varchar(32),

address varchar(200),

telephone varchar(12),

email varchar(30),

state int

)

go

select * from users

insert into users(username,password,address,telephone,email,state) values ('admin','admin','位址','13691116725','[email protected]','1')

create table companys

(

companyid int identity(1,1) primary key,

name varchar(32),

decscription varchar(1000),

address varchar(200),

telephone varchar(20),

email varchar(50),

)

go

insert into companys(name,decscription,address,telephone,email) values('公司名稱','公司介紹','公司位址','8812360','[email protected]')

select * from companys

go

create table categorys

(

categoryid int identity(1,1) primary key,

description varchar(100),

)

go

insert into categorys(description) values ('種類較長的描述')

insert into categorys(description) values ('種類較長的描述2')

go

select * from categorys

create table products

(

productid int identity(1,1)primary key,

description varchar(2000),

pictureid int,

companyid int,

categoryid int,

quantity int,

price money,

pubdate datetime default(getdate()),

details1 text,

details2 text,

details3 text,

details4 text,

details5 text,

)

go

insert into products(description,pictureid,companyid,categoryid,quantity,price,details1,details2,details3,details4,details5) values ('商品描述名稱','1','1','1','200',300,'商品詳細屬性說明一','商品詳細屬性說明2','商品詳細屬性說明3','商品詳細屬性說明4','商品詳細屬性說明5')

insert into products(description,pictureid,companyid,categoryid,quantity,price,details1,details2,details3,details4,details5) values ('商品描述名稱2','2','1','1','400',600,'商品詳細屬性說明1-2','商品詳細屬性說明2-2','商品詳細屬性說明3-2','商品詳細屬性說明4-2','商品詳細屬性說明5-2')

go

select * from products

go

create table shopcarts

(

shopcartid int identity(1,1) primary key,

userid int ,

pubdate datetime default(getdate()),

totalnum int,

totalprice int,

)

go

insert into shopcarts(userid,totalnum,totalprice) values('1','20','300')

go

select * from shopcarts

go

create table productcarts

(

productid int,

shopcartid int,

quantity int,

)

go

insert into productcarts(productid,shopcartid,quantity) values('1','1','20')

go

select * from productcarts

go

create table pictures

(

pictureid int identity(1,1) primary key,

title varchar(50),

pictureurl varchar(100),

)

go

insert into pictures(title,pictureurl) values('圖檔說明','pic/1.jpg')

insert into pictures(title,pictureurl) values('圖檔說明2','pic/2.jpg')

go

go

select * from pictures

go

create table admin

(

adminid int identity(1,1) primary key,

username varchar(32),

password varchar(32),

state int,

)

insert into admin(username,password,state) values('admin','admin','2')

select * from admin