1 create database shopping;
2
3 use shopping;
4
5 CREATE TABLE user (
6 id int(11) NOT NULL AUTO_INCREMENT,
7 username varchar(40) CHARACTER SET latin1 DEFAULT NULL,
8 password varchar(16) CHARACTER SET latin1 DEFAULT NULL,
9 phone varchar(40) CHARACTER SET latin1 DEFAULT NULL,
10 address varchar(255) CHARACTER SET latin1 DEFAULT NULL,
11 rdate datetime DEFAULT NULL,
12 PRIMARY KEY (id)
13 ) ;
14
15 create table category(
16 id int(11) primary key AUTO_INCREMENT,
17 name varchar(255),
18 descr varchar(255),
19 pid int,
20 isleaf int, #0表示leaf,1表示非leaf
21 grade int #第几级
22 );
23
24 create table product(
25
26 id int primary key auto_increment,
27 name varchar(255),
28 descr varchar(255),
29 normalprice double,
30 memberprice double,
31 pdate datetime, #上架时间
32 categoryid int references category(id)
33 );
34
35 create table salesorder( #订单
36 id int primary key auto_increment,
37 userid int, #下单的用户
38 address varchar(255), #送货地址
39 odate datetime, #下单时间
40 ststus int #送货状态
41 );
42
43
44 create table salesitem( #订单中的产品
45 id int primary key auto_increment,
46 productid int,
47 unitprice double,
48 pcount int,
49 orderid int
50 );