shop.sql

实现了网上购物系统的主要功能。包括购物车和订单处理等功能。

create database shop;
use shop

create table category (
	catid varchar(30) not null,
	name varchar(80) null,
	descn varchar(255) null,
	constraint pk_category primary key (catid)
);
insert into category values('A','java','giglnjk');
insert into category values('B','jsp','ginjk');
insert into category values('C','ejb','giglnjk');
insert into category values('D','mvc','giglnjk');
insert into category values('E','jstl','giglnjk');


create table product (
    productid varchar(30) not null,
    category varchar(30) not null,
    name varchar(80) null,
    descn varchar(255) null,
    isPromot smallint default 0,
    registerTime datetime default '2005-10-29',
    constraint pk_product primary key (productid),
        constraint fk_product_1 foreign key (category)
        references category (catid)
);
insert into product values('1','A','core java 1','mxh',1,'2005-10-29');
insert into product values('2','A','core java 2','mxhkkk',1,'2005-10-29');
insert into product values('3','A','core java','mxhkkk',1,'2005-10-29');
insert into product values('4','B','core java 3','mxhkkk',1,'2005-10-29');
insert into product values('5','B','core java 5','mxhkkk',1,'2005-10-29');

create index productCat on product (category);
create index productName on product (name);

create table item (
    itemid varchar(30) not null,
    productid varchar(30) not null,
    listprice decimal(10,2) null,
    unitcost decimal(10,2) null,
    supplier varchar(50)  null,
    status varchar(2) null,
    attr1 varchar(80) null,
    attr2 varchar(80) null,
    attr3 varchar(80) null,
    attr4 varchar(80) null,
    attr5 varchar(80) null,
    constraint pk_item primary key (itemid),
        constraint fk_item_1 foreign key (productid)
        references product (productid) 
);

insert into item values('0','1',80,50,'mxh','1','','','','','');
insert into item values('1','2',80,60,'mxh','1','','','','','');
insert into item values('2','3',80,40,'mxh','1','','','','','');
insert into item values('3','4',80,20,'mxh','1','','','','','');
insert into item values('4','5',80,10,'mxh','1','','','','','');