データベース総合練習

総合練習
あるネットショッピングモールのデータベースの構造は以下の通りです.

#      
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;