2020-12-22

Task04
1.union的用法
2.集合的运算
3.join的用法

# 老样子,先运行一个表 
database shop;
show databases;
use shop;

1.union用法

# 表的加法:union,非同一表时可用or代替,但nuion效率更高,注意union是去重数据,不去重可用union all;另当两个数据类型不相同的,可用隐式转换 
SELECT product_name FROM Product
where sale_price > 500
UNION
SELECT product_name FROM Product2
where sale_price > 500;
-- 已经去重

SELECT product_name FROM Product
where sale_price > 500
UNION all
SELECT product_name FROM Product2
where sale_price > 500;
-- 不去重

SELECT product_id, 1 product_name, '1' FROM Product
UNION
SELECT product_id, product_name,sale_price FROM Product2;
-- 隐式转换

2.集合的运算

# mysql8.0不能用intersect/except,但是intersect可用and代替,except可以用not in代替 
SELECT * FROM Product
WHERE product_id NOT IN (SELECT product_id FROM Product2)

# 差集,补集与表的减法:A-B减去的是AB的交集;两个bag的差,是元素在两个 bag 的出现次数之差(差为零或负数则不出现).

# 对称差: A-B和B-A取并集
SELECT * FROM Product
WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * FROM Product2
WHERE product_id NOT IN (SELECT product_id FROM Product)

3.join用法

# join连接:连接多个表的列
# inner join内连接:select...from...inner join...on+条件,类似于Vlookup,但是前者对于数据量大的效率更高
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.product_type, P.sale_price, SP.quantity
FROM ShopProduct AS SP
INNER JOIN Product AS P
ON SP.product_id = P.product_id;

-- 查询的执行顺序:两张表是先按照连结列进行了连结, 得到了1张新表, 然后WHERE对这张新表按照两个条件进行了筛选
FROM->WHERE->SELECT

# 自连接:一张表连接

# 内连接: 
SELECT P1.product_id, P1.product_name, P1.product_type, P1.sale_price, P2.avg_price
FROM Product AS P1
INNER JOIN(SELECT product_type,AVG(sale_price) AS avg_price
FROM Product
GROUP BY product_type) AS P2
ON P1.product_type = P2.product_type
WHERE P1.sale_price > P2.avg_price;

# 自然连接: 是内连接的其中一种

# 使用连接求交集(可将null一起取出来)
SELECT P1.* FROM Product AS P1
INNER JOIN Product2 AS P2
ON P1.product_id = P2.product_id;

# 外连接:左连结(左侧是主表),右连结(右侧是主表)(left outer,right outer),全外连结(mysql8.0不支持)
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM Product AS P
LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_id;
# 将where挪到外连接之前进行
SELECT P.product_id, P.product_name, P.sale_price,SP.shop_id, SP.shop_name, SP.quantity
FROM Product AS P
LEFT OUTER JOIN 
(SELECT * FROM ShopProduct
WHERE quantity < 50 ) AS SP
ON SP.product_id = P.product_id

# 多表内连接
CREATE TABLE InventoryProduct
( inventory_id CHAR(4) NOT NULL,
product_id CHAR(4) NOT NULL,
inventory_quantity INTEGER NOT NULL,
PRIMARY KEY (inventory_id, product_id));

--- DML：插入数据
START TRANSACTION;
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0001', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0002', 120);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0003', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0004', 3);
COMMIT;

SELECT product_id, product_1 name, sale_price, SUM(P2_price) AS cum_price
FROM(SELECT P1.product_id, P1.product_name, P1.sale_price, P2.product_id AS P2_id, P2.product_name AS P2_name, P2.sale_price AS P2_price
FROM Product AS P1
LEFT OUTER JOIN Product AS P2
ON ((P1.sale_price > P2.sale_price)
OR (P1.sale_price = P2.sale_price
AND P1.product_id<=P2.product_id))
ORDER BY P1.sale_price,P1.product_id) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY sale_price,cum_price;