MySQL之day6

集合运算


 

表的加减法


 

1. 加法   union   (并集)   ,  去除重复记录!

mysql> select product_id,product_name
    -> from product
    -> union
    -> select product_id , product_name
    -> from product2;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001       | T恤          |
| 0002       | 打孔器       |
| 0003       | 运行T恤      |
| 0004       | 菜刀         |
| 0005       | 高压锅       |
| 0006       | 叉子         |
| 0007       | 砧板         |
| 0008       | 圆珠笔       |
| 0009       | 电视机       |
| 0010       | 电视机2      |
| 0011       | 4k电视       |
| 0020       | 电视机4      |
| 0001       | T恤衫        |
| 0003       | 运动T恤      |
| 0009       | 手套         |
| 0010       | 水壶         |
+------------+--------------+
16 rows in set (0.28 sec)
View Code

 

注意事项:

  1.   列数 相同
  2.   列类型一致
  3.   order by 只能在最后使用

1.1  包含重复行(记录) 的 集合运算   all

  union all

mysql> select product_id,product_name
    -> from product
    -> union all
    -> select product_id,product_name
    -> from product2;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001       | T恤          |
| 0002       | 打孔器       |
| 0003       | 运行T恤      |
| 0004       | 菜刀         |
| 0005       | 高压锅       |
| 0006       | 叉子         |
| 0007       | 砧板         |
| 0008       | 圆珠笔       |
| 0009       | 电视机       |
| 0010       | 电视机2      |
| 0011       | 4k电视       |
| 0020       | 电视机4      |
| 0001       | T恤衫        |
| 0002       | 打孔器       |
| 0003       | 运动T恤      |
| 0009       | 手套         |
| 0010       | 水壶         |
+------------+--------------+
17 rows in set (0.29 sec)
View Code

 2. 取表中公共部分  intersect     (交集)    mysql 8.0 不支持!!!

mysql> select product_id,product_name
    -> from product
    -> intersect
    -> select product_id,product_name
    -> from product2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select product_id,product_name
from product2' at line 4
View Code

 3. 减法   except    mysql 不支持!!!

 


 

联结(一列为单位对表进行联结)

mysql> select * from product;
+------------+--------------+--------------+------------+----------------+-------------+------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date | co   |
+------------+--------------+--------------+------------+----------------+-------------+------+
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  | NULL |
| 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  | NULL |
| 0003       | 运动T恤      | 衣服         |       4000 |           2800 | NULL        | NULL |
| 0004       | 菜刀         | 厨房用品     |      30000 |           1400 | 2009-09-20  | NULL |
| 0005       | 高压锅       | 厨房用品     |      68000 |           2500 | 2009-01-15  | NULL |
| 0006       | 叉子         | 厨房用品     |       5000 |           NULL | 2009-09-20  | NULL |
| 0007       | 砧板         | 厨房用品     |       8800 |            395 | 2008-04-28  | NULL |
| 0008       | 圆珠笔       | 办公用品     |        100 |           NULL | 2009-11-11  | NULL |
| 0011       | 4k电视       | 家具         |      10000 |           3000 | 2019-07-07  | NULL |
| 0012       | 电视机2      | 家具         |      40000 |           NULL | 2019-07-05  | NULL |
| 0013       | 电视机       | 家具         |       4000 |           3000 | 2019-07-04  | NULL |
+------------+--------------+--------------+------------+----------------+-------------+------+
11 rows in set (0.00 sec)

mysql> select * from product2;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001       | T恤衫        | 衣服         |       1000 |            500 | 2009-09-20  |
| 0002       | 打孔器       | 办公用品     |        500 |            320 | 2009-09-11  |
| 0003       | 运动T恤      | 衣服         |       4000 |           2800 | NULL        |
| 0009       | 手套         | 衣服         |        800 |            500 | NULL        |
| 0010       | 水壶         | 厨房用具     |       2000 |           1700 | 2009-09-20  |
+------------+--------------+--------------+------------+----------------+-------------+
5 rows in set (0.00 sec)

 


 

1. 内联结  inner  join

 

要点:

  1.  注意 on 后面联结条件

  2.  from 后面使用多张表

  3.  select 指定的列       <表别名>.<列名>

可以在后面加选定条件:   

  如:  where   sp.shop_id = '000A';

2. 外联结  outer  join

mysql> select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price
    -> from shopproduct as sp right outer join product as p
    -> on sp.product_id = p.product_id;
+---------+-----------+------------+--------------+------------+
| shop_id | shop_name | product_id | product_name | sale_price |
+---------+-----------+------------+--------------+------------+
| 000A    | 东京      | 0001       | T恤          |       1000 |
| 000A    | 东京      | 0002       | 打孔器       |        500 |
| 000A    | 东京      | 0003       | 运动T恤      |       4000 |
| 000B    | 名古屋    | 0002       | 打孔器       |        500 |
| 000B    | 名古屋    | 0003       | 运动T恤      |       4000 |
| 000B    | 名古屋    | 0004       | 菜刀         |      30000 |
| 000B    | 名古屋    | 0006       | 叉子         |       5000 |
| 000B    | 名古屋    | 0007       | 砧板         |       8800 |
| 000C    | 大阪      | 0003       | 运动T恤      |       4000 |
| 000C    | 大阪      | 0004       | 菜刀         |      30000 |
| 000C    | 大阪      | 0006       | 叉子         |       5000 |
| 000C    | 大阪      | 0007       | 砧板         |       8800 |
| 000D    | 大阪      | 0001       | T恤          |       1000 |
| NULL    | NULL      | NULL       | 高压锅       |      68000 |
| NULL    | NULL      | NULL       | 圆珠笔       |        100 |
| NULL    | NULL      | NULL       | 4k电视       |      10000 |
| NULL    | NULL      | NULL       | 电视机2      |      40000 |
| NULL    | NULL      | NULL       | 电视机       |       4000 |
+---------+-----------+------------+--------------+------------+
18 rows in set (0.30 sec)
View Code

要点:  

  左右 主表 

  right  left     二者效果相同!!

mysql> select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price,ip.inventory_quantity
    -> from shopproduct as sp inner join product as p  on sp.product_id = p.product_id
    -> inner join inventoryproduct as ip
    -> on sp.product_id = ip.product_id
    -> where ip.inventory_id ='S001';
+---------+-----------+------------+--------------+------------+--------------------+
| shop_id | shop_name | product_id | product_name | sale_price | inventory_quantity |
+---------+-----------+------------+--------------+------------+--------------------+
| 000A    | 东京      | 0001       | T恤          |       1000 |                  0 |
| 000A    | 东京      | 0002       | 打孔器       |        500 |                120 |
| 000A    | 东京      | 0003       | 运动T恤      |       4000 |                200 |
| 000B    | 名古屋    | 0002       | 打孔器       |        500 |                120 |
| 000B    | 名古屋    | 0003       | 运动T恤      |       4000 |                200 |
| 000B    | 名古屋    | 0004       | 菜刀         |      30000 |                  3 |
| 000B    | 名古屋    | 0006       | 叉子         |       5000 |                 99 |
| 000B    | 名古屋    | 0007       | 砧板         |       8800 |                999 |
| 000C    | 大阪      | 0003       | 运动T恤      |       4000 |                200 |
| 000C    | 大阪      | 0004       | 菜刀         |      30000 |                  3 |
| 000C    | 大阪      | 0006       | 叉子         |       5000 |                 99 |
| 000C    | 大阪      | 0007       | 砧板         |       8800 |                999 |
| 000D    | 大阪      | 0001       | T恤          |       1000 |                  0 |
+---------+-----------+------------+--------------+------------+--------------------+
13 rows in set (0.30 sec)
View Code
mysql> select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price,ip.inventory_quantity
    -> from shopproduct as sp inner join product as p  on sp.product_id = p.product_id
    -> inner join inventoryproduct as ip
    -> on sp.product_id = ip.product_id
    -> where ip.inventory_id ='S001';
+---------+-----------+------------+--------------+------------+--------------------+
| shop_id | shop_name | product_id | product_name | sale_price | inventory_quantity |
+---------+-----------+------------+--------------+------------+--------------------+
| 000A    | 东京      | 0001       | T恤          |       1000 |                  0 |
| 000A    | 东京      | 0002       | 打孔器       |        500 |                120 |
| 000A    | 东京      | 0003       | 运动T恤      |       4000 |                200 |
| 000B    | 名古屋    | 0002       | 打孔器       |        500 |                120 |
| 000B    | 名古屋    | 0003       | 运动T恤      |       4000 |                200 |
| 000B    | 名古屋    | 0004       | 菜刀         |      30000 |                  3 |
| 000B    | 名古屋    | 0006       | 叉子         |       5000 |                 99 |
| 000B    | 名古屋    | 0007       | 砧板         |       8800 |                999 |
| 000C    | 大阪      | 0003       | 运动T恤      |       4000 |                200 |
| 000C    | 大阪      | 0004       | 菜刀         |      30000 |                  3 |
| 000C    | 大阪      | 0006       | 叉子         |       5000 |                 99 |
| 000C    | 大阪      | 0007       | 砧板         |       8800 |                999 |
| 000D    | 大阪      | 0001       | T恤          |       1000 |                  0 |
+---------+-----------+------------+--------------+------------+--------------------+
13 rows in set (0.30 sec)
View Code

 

3. 交叉联结   cross join     -- 没有实际意义


 

窗口函数  MySQL 8.0 已支持

窗口函数具备 分组和排序 两种功能

partition by 分组后的记录集合称为"窗口"

  •  聚合函数 sum   avg  count  max   min
  •  rank     dense_rank      row_number  



 



 

 作为窗口函数使用的聚合函数


 

SELECT product_name , product_type , sale_price , 
            sum(sale_price) over (ORDER BY product_id) as current_sum
from product;


计算移动平均


 

rows ("行") 和 preceding("之前")     ---> 截止到之前 多少 行

rows ("行") 和  pollowing ("之后")     ---> 截止到之后 多少 行

 两者结合   

SELECT product_id, product_name, sale_price,
            AVG (sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg,       --之前
            AVG (sale_price) OVER (ORDER BY product_id ROWS 2 PRECEDING) AS moving_avg2,      --之后
            AVG (sale_price) OVER (ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg3   --两者结合
FROM Product;

grouping运算符

      CUBE       生成的结果集显示了所选列中值的所有组合(group by 后面的列组合)的聚合。   mysql 8.0 还不支持!!
      ROLLUP   生成的结果集显示了所选列中值的某一层次结构的聚合


 rollup   ---同时合计和小计


 

 
SELECT
CASE
        
    WHEN
        GROUPING ( product_type ) = 1 THEN
            '商品种类 合计' ELSE product_type 
            END AS product_type,
    CASE
            
            WHEN GROUPING ( regist_date ) = 1 THEN
            '登记日期 合计' ELSE CAST( regist_date AS DATE ) 
        END AS regist_date,
        SUM( sale_price ) AS sum_price 
    FROM
        Product 
    GROUP BY
    product_type,
    regist_date WITH ROLLUP;
得到相同结果!!!

 

posted @ 2019-07-09 10:43  找工作好爽啊哈哈哈呜  阅读(307)  评论(0编辑  收藏  举报