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中找出第二部对应客户的所有信息
#select order_num FROM orderitems WHERE prod_id='TNT2';
#select cust_id FROM orders WHERE order_num IN(select order_num FROM orderitems WHERE prod_id='TNT2');
#select DISTINCT * from customers WHERE cust_id IN(select cust_id FROM orders WHERE order_num IN(select #order_num FROM orderitems WHERE prod_id='TNT2'));
#子查询用于计算字段 查询每个客户的订单数
#select count(*) as orders1 FROM orders WHERE cust_id = 10001;
#select cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS ordernums #FROM customers ORDER BY cust_name;