CREATE table if not EXISTS customers
(
`cust_id
` int(200),
`cust_name
` char(20),
`cust_address
` char(20),
`cust_city
` char(20),
`cust_state
` char(20),
`cust_zip
` int,
`cust_country
` char(20),
`cust_contact
` char(20),
`cust_email
` char(20)
);
CREATE table if not EXISTS orderitems
(
`order_num
` int,
`order_item
` int,
`prod_id
` char(20),
`quantity
` char(10),
`item_price
` int
);
CREATE table if not EXISTS orders
(
`order_num
` int,
`order_date
` char(20),
`cust_id
` int
);
insert into customers
(cust_id
,cust_name
,cust_address
,cust_city
,cust_state
,cust_zip
,cust_country
,cust_contact
,cust_email
)
values
(10001,'xiaotaoCoyote Inc.','200 Maple Lane','Detroit','MI',44444,'USA','Y Lee','ylee@coyote.com'),
(10002,'Mouse House','333 Fromage Lane','Columbus','OH',43333,'USA','Jerry Mouse','NULL'),
(10003,'Wascals','1 Sunny Place','Muncie','IN',42222,'USA','Jim Jones','rabbit@coyote.com'),
(10004,'Yosemite Place','829 Riverside Drive','Phonix','AZ',88888,'USA','Y sam','sam@163.com'),
(10005,'E Fudd','4545 53rd Street','Chicago','IL',54545,'USA','E Fudd',NULL);
insert into orders
(order_num
,order_date
,cust_id
)
values
(20005,'2005-09-01',10001),
(20006,'2005-09-12',10003),
(20007,'2005-09-30',10004),
(20008,'2005-10-03',10005),
(20009,'2005-10-08',10001);
insert into orderitems
(order_num
,order_item
,prod_id
,quantity
,item_price
)
values
(20005,1,'ANV01',10,5.99),
(20005,2,'ANV02',3,9.99),
(20005,3,'TNT2',5,10.00),
(20005,4,'FB',1,10.00),
(20006,1,'JP2000',1,55.00),
(20008,1,'FC',50,2.5),
(20009,3,'FB',1,10.00),
(20009,3,'OL1',1,9.99),
(20009,3,'SLING',1,4.49),
(20009,4,'ANV03',1,14.99);
#子查询训练 查找买了商品’TNT2’的顾客信息 #1.在orderitems里找出TNT2的订单号； #2.在orders里找出第一步找出的订单号的id #3.在customers中找出第二部对应客户的所有信息
#子查询用于计算字段 查询每个客户的订单数
