数据库操作练习题

题目：1. 设计 订单表 和 物流表
           2. 订单表
                   订单号、订单数量、商品名称、商品型号、
                   售价、订单生成时间
               物流表
                   物流号、省、市、区、地址、快递单号、收货人、手机号

           3.问题：
              a. 设计2张表；
              b. 获取销售量最好的商品；
              c. 获取销售额最多的商品；
              d. 获取每个省最受欢迎的产品；
              e. 获取消费能力最强的省；
              d. 打印快递单（获取发货地址、收货人、手机和订单号，

                  快递单号中的英文必须大写）；

--订单表

--物流表

-- 获取销售量最好的商品
select goods_name,SUM(order_number) from `order` GROUP BY goods_name

HAVING SUM(order_number)=(SELECT MAX(a.s) from (select SUM(order_number) s from `order` GROUP BY goods_name) a );

-- 获取销售额最多的商品
select goods_name,SUM(order_number*goods_price) from `order` GROUP BY goods_name

HAVING SUM(order_number*goods_price)=(SELECT MAX(a.s) from (select SUM(order_number*goods_price) s from `order` GROUP BY goods_name) a);

-- 消费能力最强的省
SELECT province,SUM(order_number*goods_price) FROM

 (SELECT * from logistics a RIGHT JOIN `order` b on a.orde_id=b.order_id) c GROUP BY province

  HAVING SUM(order_number*goods_price)=(SELECT MAX(a.s) from

    (select SUM(order_number*goods_price) s from

     (SELECT * from logistics a RIGHT JOIN `order` b on a.orde_id=b.order_id) p GROUP BY province) a);

-- 把两张表连接成一张
SELECT * from logistics a RIGHT JOIN `order` b on a.orde_id=b.order_id

-- 获取每个省最受欢迎的产品(存在数据重复问题)
SELECT province,goods_name,sum(order_number) s FROM

  (SELECT * from logistics a RIGHT JOIN `order` b on a.orde_id=b.order_id) c GROUP BY province,

   goods_name HAVING s in

    (SELECT MAX(g.s) from

      (select province,goods_name,SUM(order_number) s from

       (SELECT * from logistics a RIGHT JOIN `order` b on a.orde_id=b.order_id) c GROUP BY  province, goods_name) g GROUP BY province);

-- 获取每个省最受欢迎的产品（推荐）

SELECT f.province,goods_name,max_order_munber FROM

 (SELECT province,goods_name,SUM(order_number) AS sum_order_number FROM

  (SELECT * FROM logistics a RIGHT JOIN `order` b ON a.orde_id=b.order_id) c GROUP BY province,goods_name) e RIGHT JOIN
(SELECT province,MAX(d.s) AS max_order_munber FROM

  (SELECT province,goods_name,SUM(order_number) s FROM

   (SELECT * FROM logistics a RIGHT JOIN `order` b ON a.orde_id=b.order_id) c

    GROUP BY province,goods_name) d

     GROUP BY province) f ON e.province=f.province AND e.sum_order_number=f.max_order_munber

-- 用创建视图的方式解决每个省最受欢迎的产品问题

CREATE VIEW join_view AS (SELECT * FROM logistics a RIGHT JOIN `order` b ON a.orde_id=b.order_id) ;

CREATE OR REPLACE VIEW group_view AS (SELECT province,goods_name,SUM(order_number) AS sum_order_number FROM join_view c GROUP BY province,goods_name);

CREATE VIEW group_province_view AS (SELECT province,MAX(d.sum_order_number) AS max_order_munber FROM group_view d GROUP BY province);

CREATE VIEW result_view AS (SELECT f.province,goods_name,max_order_munber FROM group_view e RIGHT JOIN group_province_view f ON e.province=f.province AND e.sum_order_number=f.max_order_munber);

SELECT * FROM result_view;

--打印结果同上

注：1.or replace关键字可以替换原有视图

   2.创建视图时，不可以有多个查询子句，即每次创建视图as关键字后面只能有一组select ... from...

-- 打印快递单
SELECT CONCAT("订单号",UPPER(order_id),"地址：",province,city,area,"详细地址...","收件人：",consignee," ","手机号：",phone) 快递单 FROM

 (SELECT * from logistics a RIGHT JOIN `order` b on a.orde_id=b.order_id) a;