数据库操作

某网上商城数据库表结构如下：

# 创建用户表
create table user(
	 userId int primary key auto_increment,
  	 username varchar(20) not null,
  	 password varchar(18) not null,
     address varchar(100),
     phone varchar(11)
);

#创建分类表
create table category(
  cid varchar(32) PRIMARY KEY ,
  cname varchar(100) not null		#分类名称
);

# 商品表
CREATE TABLE `products` (
pid VARCHAR(32) PRIMARY KEY,
	`name` VARCHAR(40),
	price DOUBLE(7,2),
	category_id VARCHAR(32),
	CONSTRAINT fk_products_category_id FOREIGN KEY (category_id)
	REFERENCES category(cid)  
);

#订单表
create table `orders`(
 	 oid VARCHAR(32) PRIMARY KEY,
	totalprice DOUBLE(12,2),
	userId INT ,
	CONSTRAINT fk_orderitem_userId FOREIGN KEY (userId)
	REFERENCES `user`(userId)
);

# 订单项表
create table orderitem(
  oid VARCHAR(32) ,
	pid VARCHAR(32) ,
	num INT ,
	PRIMARY KEY(oid,pid), 
	CONSTRAINT fk_orderitem_oid FOREIGN KEY(oid)
	REFERENCES orders(oid),
	CONSTRAINT fk_orderitem_pid FOREIGN KEY(pid)
	REFERENCES products(pid)
);

#-----------------------------------------------
#初始化数据

#用户表添加数据
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('张三','123','北京昌平沙河','13812345678');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('王五','5678','北京海淀','13812345141');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('赵六','123','北京朝阳','13812340987');
INSERT INTO USER(username,PASSWORD,address,phone) VALUES('田七','123','北京大兴','13812345687');

#给商品表初始化数据
insert into products(pid,name,price,category_id) values('p001','联想',5000,'c001');
insert into products(pid,name,price,category_id) values('p002','海尔',3000,'c001');
insert into products(pid,name,price,category_id) values('p003','雷神',5000,'c001');
insert into products(pid,name,price,category_id) values('p004','JACK JONES',800,'c002');
insert into products(pid,name,price,category_id) values('p005','真维斯',200,'c002');
insert into products(pid,name,price,category_id) values('p006','花花公子',440,'c002');
insert into products(pid,name,price,category_id) values('p007','劲霸',2000,'c002');
insert into products(pid,name,price,category_id) values('p008','香奈儿',800,'c003');
insert into products(pid,name,price,category_id) values('p009','相宜本草',200,'c003');
insert into products(pid,name,price,category_id) values('p010','梅明子',200,null);


#给分类表初始化数据
insert into category values('c001','电器');
insert into category values('c002','服饰');
insert into category values('c003','化妆品');
insert into category values('c004','书籍');

#添加订单
insert into orders values('o6100',18000.50,1);
insert into orders values('o6101',7200.35,1);
insert into orders values('o6102',600.00,2);
insert into orders values('o6103',1300.26,4);

#订单详情表
insert into orderitem values('o6100','p001',1),('o6100','p002',1),('o6101','p003',1);

1>查询所有用户的订单

SELECT username , address,phone, oid,totalprice
FROM `user`
INNER JOIN orders
WHERE `user`.`userId` = orders.`userId`;

 

2>查询用户id为 1 的所有订单详情

SELECT username , address,phone, oid,totalprice 
FROM `user`
INNER JOIN orders
WHERE `user`.`userId` = orders.`userId` AND `user`.userId = 1;

 

1>查看用户为张三的订单

SELECT userId FROM `user` WHERE username = '张三';
SELECT * FROM orders WHERE orders.`userId` = (SELECT userId FROM `user` WHERE username = '张三');

 

2>查询出订单的价格大于800的所有用户信息。

SELECT userId FROM orders WHERE totalprice > 800;

1>查询所有订单信息，每页显示5条数据

SELECT  username,address,phone,price,`name`,cname
FROM `user` AS u
INNER JOIN orders AS o
ON u.`userId` = o.`userId`
INNER JOIN orderitem AS r
ON o.`oid` = r.`oid`
INNER JOIN products AS p
ON r.`pid` = p.`pid`
INNER JOIN category AS c
ON p.`category_id` = c.`cid` LIMIT 0,5;