db.txt

shoppingCar 购物车

创建product表
create table product(id int primary key,pname varchar(20) not null,
			description varchar(64),price double(8,2) not null);
创建user表
create table user(id int primary key,userName varchar(20) not null unique,
			password varchar(20) not null,address varchar(256),postCode varchar(10),
			email varchar(32),homePhone varchar(32),
			cellPhone varchar(32),officePhone varchar(32));
创建item表
create table item(id int primary key, amount int ,
			product_id int not null references product(id),
			order_id int not null references t_order(id));
创建order表
create table t_order(id int primary key,
			cost double(10,2),
			user_id int not null references user(id) );

往表product中插入数据
insert into product(id,pname,description,price) values(1,'pen','pen',5.00);
insert into product(id,pname,description,price) values(2,'pencil','pencil',2.00);
insert into product(id,pname,description,price) values(3,'rubber','rubber',1.00);
insert into product(id,pname,description,price) values(4,'notebook','notebook',15.00);
insert into product(id,pname,description,price) values(5,'gluewater','gluewater',10.00);
insert into product(id,pname,description,price) values(6,'pencilcase','pencilcase',8.00);
insert into product(id,pname,description,price) values(7,'bellpen','bellpen',12.00);
insert into product(id,pname,description,price) values(8,'colorpen','colorpen',20.00);
查看所有商品
select id,pname,description,price from product;
根据用户名和密码查找用户

查看所有的用户
select id,userName,password,address,postCode,email,homePhone,cellPhone,officePhone 
		from user where 1=1;
往表user中插入数据
insert into user(id,userName,password,address,postCode,email,homePhone,cellPhone,officePhone) 
		values(1,'tom','11','taiyuan','003201','[e-mail]','88886666','88886666','88886666');
更新用户信息
update user set 
往表item中插入数据
insert into item(id,amount,product_id,order_id) values(1);
往表t_order中插入数据
insert into t_order(id,user_id,cost) values();

查询用户id为3的用户的所有定单,商品,商品数量,总金额
 select distinct p.pname,i.amount,o.oid,o.cost from product p,item i,t_order o 
 where i.id in( select id from item where order_id in( select id from t_order 
 where user_id=3)) and i.product_id=p.id and i.order_id=o.id