綜合練習

某網上商城資料庫表結構如下：

# 創建用戶表
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 foreign key(category_id) references category(cid)
);

#訂單表
create table `orders`(
  `oid` varchar(32) PRIMARY KEY ,
  `totalprice` double(12,2), #總計
  `userId` int,
   constraint foreign key(userId) references user(userId) #外鍵
);

# 訂單項表
create table orderitem(
  oid varchar(32),	#訂單id
  pid varchar(32),	#商品id
  num int ,         #購買商品數量
  primary key(oid,pid), #主鍵
  foreign key(oid) references orders(oid),
  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);

14.1綜合練習1-【多表查詢】

1>查詢所有用戶的訂單

SELECT o.oid,o.totalprice, u.userId,u.username,u.phone 
FROM orders o INNER JOIN USER u ON o.userId=u.userId; 

2>查詢用戶id為 1 的所有訂單詳情

SELECT o.oid,o.totalprice, u.userId,u.username,u.phone ,oi.pid
FROM  orders o INNER JOIN USER u ON o.userId=u.userId
INNER JOIN orderitem oi ON o.oid=oi.oid
where u.userid=1;

14.2綜合練習2-【子查詢】

1>查看用戶為張三的訂單

SELECT * FROM orders WHERE userId=(SELECT userid FROM USER WHERE username='張三');

2>查詢出訂單的價格大于800的所有用戶資訊，

SELECT * FROM USER WHERE userId IN (SELECT DISTINCT userId FROM orders WHERE totalprice>800);

14.3綜合練習3-【分頁查詢】

1>查詢所有訂單資訊，每頁顯示5條資料

#查詢第一頁
SELECT * FROM orders LIMIT 0,5;