MySQL查询练习

use productorder;
# 1.统计所有库存商品的总价值
select sum(pprice*product.pcount) as '所有库存商品总价值' from product;
#  2.查询库存商品中，最高单价、最低单价分别是多少
select max(pprice) as '最高单价',min(pprice) as '最低单价' from product;
#  3.检索产品价格大于3000的有几个
select count(pprice) from product where pprice>3000;
#  4.按产品价格降序并提取前2条数据
select * from product order by pprice desc limit 2;

# 1.检索所有订单订购物品的总数
select sum(ocount) from `order`;
#  2查询所有销售信息，并按销售时间倒序排列
select * from `order` order by odate desc ;
#  3查询数量大于10的订单信息，并按照数量倒序排序
select  * from `order` where ocount > 10 order by ocount desc ;
#  4查询2016年12月份订单数量最高的订单
select * from `order` where odate between '2016-12-01' and '2017-01-01' order by ocount desc limit 1;
#  5.统计所有商品的销售量
select pid,sum(ocount) as '销售量' from `order` group by pid;
#  6.统计销售量大于50的所有商品
select pid,sum(ocount) as '销售量' from `order` group by pid having sum(ocount)>50;
#  7.查询2016-12-05之后销售数量最高的两笔销售信息
select * from `order` where odate > '2016-12-05' order by ocount desc limit 2;

#  1.查询进货量最高的三笔进货信息
select * from recruit order by rcount desc limit 3;
#  2.计算供应商ID 为1的所有产品的平均价格
select avg(rprice) from recruit where vid = 1;
#  3.查询2016-12-01之后进货信息，并按时间降序排列
select * from recruit where rdat > '2016-12-01' order by rdat desc ;
#  4.统计所有商品的进货量，并按倒序排列
select pid, sum(rcount) from recruit group by pid order by sum(rcount) desc ;
#  5.统计12月1 日以后进货量大于100的所有供应商ID，并按进货总量倒序排列
# 12月1日以后也就是从12月2日开始。
select vid,sum(rcount) from recruit where rdat > '2016-12-02' and rcount >10 group by vid order by sum(rcount) desc ;

# 1.查询所有iPhone 6s的入库信息(使用等值连接和内连接)
# 等值连接
select rid,r.pid, vid, rprice, rcount, returncount, rdesc, rdat from product p,recruit r where p.pid=r.pid and p.pname = 'iPhone6s';
# 内连接
select rid,r.pid, vid, rprice, rcount, returncount, rdesc, rdat from product p inner Join recruit r on p.pid=r.pid where pname = 'iPhone6s';
# 子查询
select * from recruit where pid = (select pid from product where pname = 'iPhone6s');
#  2.使用内连接查询所有iPhone 7 12月10日前（不包括12月10日）的销售情况
select oid,o.pid,cid,ocount,oprice,odate from `order` o
    inner JOIN product p on o.pid = p.pid where pname='iPhone7' and odate < '2016-12-10';
#  3.使用左连接查询11月份入库的所有商品名及入库情况
select pname,rid,r.pid, vid, rprice, rcount, returncount, rdesc, rdat from recruit r
    left join product p on p.pid = r.pid where rdat between '2016-11-01' and '2016-12-01';
#  4.查询提供过“iPhone 7”商品的所有供应商名及邮编
select v.vname,v.vzip from vendor v,product p , recruit r
where r.pid = p.pid and r.vid = v.vid and p.pname = 'iPhone7';
#  5.查询顾客马云的订单数量,显示订单号，顾客姓名，订单数量
select o.oid as '订单号',c.cname as '顾客姓名',o.ocount as '订单数量' from `order` o,customer c where o.cid = c.cid and c.cname='马云';
#  6.查询12月份哪些顾客买了iPhone 6s,显示顾客姓名，订单号，产品名称，购买日期
select c.cname as '顾客姓名',o.oid as '订单号',p.pname as '产品名称',o.odate as '购买日期'from customer c,`order` o,product p
where o.cid = c.cid and o.pid = p.pid and p.pname = 'iPhone6s' and o.odate between '2016-12-01' and '2017-01-01';
#  7.计算供应商雷军的所有产品的平均价格，显示供应商姓名，平均价格
select v.vname as '供应商姓名',avg(r.rprice) as '平均价格' from vendor v, recruit r where r.vid = v.vid and v.vname='雷军';
#  8.查询小米Note2 12月份共买了多少台。显示产品名称，买的台数
select p.pname as '产品名称',sum(o.ocount) as '买的台数' from product p, `order` o
where o.pid = p.pid and p.pname='小米Note2'
  and o.odate between '2016-12-01' and '2017-01-01';
#  9.查询小米Note2共有几次订单，显示产品名称，订单数量
select p.pname as '产品名称',count(pname) as '订单数量' from product p, `order` o
where o.pid = p.pid and p.pname='小米Note2';