myshopping.sql

来自「struts hibernate框架 商场买卖」· SQL 代码 · 共 83 行

#数据库命名全部小写
create database myshopping;

use myshopping;
create table admin (
	id int primary key auto_increment,
	name varchar(32),
	password varchar(32),
	email varchar(100)
);
create table user 
(
id int primary key auto_increment not null,
username varchar(32) not null,
password varchar(32)not null,
email varchar(100),
question varchar(100),
answer varchar(100),
phone varchar(40),
addr varchar(255),
rdate datetime,
name varchar(32),
postalcode varchar(10),
qq varchar(32)
);

create table category 
(
id int primary key auto_increment,
pid int,
name varchar(32),
descr varchar(255)
);

create table item
(
id int primary key auto_increment,
name varchar(32),
descr varchar(255),
price double,
pdate datetime,
pro_storage long,
imageUrl varchar(100),
category_id int 
);

create table cart
(
id int primary key auto_increment,
totalprice double,
quantity long,
item_id int
);

create table salesorder
(
id int primary key auto_increment,
orderno varchar(32),
orderdate datetime,
addr varchar(255),
odate datetime,
status int,
cart_id int,
user_id int
);

alter table item add constraint FK_category_id
foreign key (category_id) references category(id);

alter table salesorder add constraint FK_cart_id
foreign key (cart_id) references cart(id);

alter table salesorder add constraint FK_user_id
foreign key (user_id) references user(id);

alter table cart add constraint FK_item_id
foreign key (item_id) references item(id);


#select pid, sum(pcount) sumCount from salesitem group by pid order by sumCount desc limit 0, 10