建表.sql

来自「这是用ASP.NET和SQLserver编写的购物网站的程序」· SQL 代码 · 共 78 行

create table account    
(userid varchar(20) not null unique,
 username varchar(20) not null,
 e_mail varchar(40) not null,
 addr varchar(80),
 city varchar(20),
 pro  varchar(20),
 zip  varchar(20),
 phone varchar(20),
 primary key (userid));   

create table signon     
(userid varchar(20) not null unique,
 password varchar(25) not null,
 foreign key(userid) references account(userid));

create table orders     
(orderid int not null unique,
 userid varchar(20) not null,
 orderdate datetime,
 totalprice decimal(12,2),
 status bit,
 primary key(orderid),
 foreign key(userid) references account(userid));

create table product    
(productid varchar(10) not null,
 name varchar(20) not null,
 decn varchar(80),
 primary key(productid));

create table item        
(itemid varchar(10) not null unique,
 itemname varchar(50)not null,
 productid varchar(10),
 listprice decimal(10,2),
 qty int,
 image varchar(50),
decn varchar(200),
 primary key(itemid),
 foreign key(productid) references product(productid));

create table lineitem    
(orderid int not null ,
 itemid varchar(10),
 quentity int,
 untiprice decimal(10,2),
 totalprice decimal(12,2) ,
 primary key(orderid,itemid));

create table ordercost
(cost decimal(5,2))

create table commentary  
(username varchar(10),
 date datetime,
 content varchar(200))

create table shopping    
(userid varchar(20) not null,
 itemid varchar(10) not null,
 itemname varchar(50)not null,
 untiprice decimal(10,2) not null,
 qty int not null,
 primary key(userid,itemid),
 foreign key(itemid) references item(itemid))
create table currenttime
(ctime datetime)
create table top6       
(itemid varchar(10) not null unique,
 itemname varchar(50)not null,
 productid varchar(10),
 listprice decimal(10,2),
 qty int,
 image varchar(50),
 decn varchar(200)
 primary key(itemid),
 foreign key(productid) references product(productid));