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)
注意事项:
- 列数 相同
- 列类型一致
- 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)
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
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)
要点:
左右 主表
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)
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)
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; 得到相同结果!!!
