SQL组队学习task4

Serendipity_101
于 2022-08-24 23:37:00 发布
阅读量115
点赞数
文章标签：数据库 sql
本文链接：https://blog.csdn.net/Serendipity_101/article/details/126514853
版权
DROP TABLE IF EXISTS product2;
CREATE TABLE product2 (
  product_id     char(4)      CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  product_name   varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  product_type   varchar(32)  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  sale_price     int          DEFAULT NULL,
  purchase_price int          DEFAULT NULL,
  regist_date    date         DEFAULT NULL,
  PRIMARY KEY (product_id)); 
INSERT INTO product2(product_id,product_name,product_type,sale_price,purchase_price,regist_date) 
values ('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');



-- 分别使用 UNION 或者 OR 谓词,找出成本利润率不足 30%或成本利润率未知的商品。
SELECT product_name, sale_price, purchase_price, round(sale_price/purchase_price, 3) AS cost_rate 
FROM products
WHERE sale_price/purchase_price < 1.3 OR sale_price/purchase_price is null;

SELECT product_name,sale_price
FROM products
WHERE sale_price/purchase_price < 1.3
UNION
SELECT product_name, sale_price
FROM products
WHERE sale_price/purchase_price is null;

# 保留重复行 union all
SELECT *
FROM products
UNION ALL 
SELECT * 
FROM product2;
-- 对product表中成本利润低于50% 或者 售价低于1000的商品提价
SELECT * FROM products
WHERE sale_price < 1000
UNION ALL 
SELECT * FROM products
WHERE sale_price/purchase_price < 1.5;

# 隐式数据类型转化
SELECT SYSDATE(), SYSDATE(), SYSDATE()
UNION 
SELECT "ABC", 12, NULL;
# 交集运算
SELECT p1.product_id, p1.product_name
FROM products AS p1
INNER JOIN product2 AS p2
ON p1.product_id = p2.product_id;
# 差集运算
# 找出只存在于Product表但不存在于Product2表的商品
SELECT product_id, product_name
FROM products
WHERE product_id NOT IN (SELECT product_id FROM product2);
# 法2
SELECT product_id, product_name
FROM products p
WHERE NOT EXISTS (SELECT * FROM product2 AS p2
				  WHERE p.product_id = p2.product_id);
-- 使用NOT谓词进行集合的减法运算, 求出 Product 表中, 售价高于2000、成本利润率不低于 30% 的商品
SELECT * FROM products
WHERE sale_price > 2000
AND product_id NOT IN (SELECT product_id FROM products WHERE sale_price/purchase_price > 1.3);
# 对称差运算: (A-B)+(B-A)
SELECT * FROM products
WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION 
SELECT * FROM product2
WHERE product_id NOT IN (SELECT product_id FROM products);


# 内连接
SELECT  s.shop_id,
		s.shop_name,
        s.product_id,
        p.product_id,
        p.product_type,
        p.sale_price
FROM shopproduct AS s
INNER JOIN 
products AS p
ON s.product_id = p.product_id;

-- 找出每个商店里的衣服类商品的名称及价格等信息
SELECT * FROM products AS p
INNER JOIN shopproduct AS s
ON p.product_id = s.product_id
WHERE product_type = "衣服";
-- 使用子查询
SELECT  SP.shop_id, SP.shop_name, SP.product_id,P.product_name, P.product_type, P.purchase_price
FROM shopproduct AS SP 
INNER JOIN 
(SELECT product_id, product_name, product_type, purchase_price
 FROM products
 WHERE product_type = "衣服") AS P
ON SP.product_id = P.product_id; 
-- 使用连结两个子查询和不使用子查询的方式, 找出东京商店里, 售价低于 2000 的商品信息
SELECT *
FROM (SELECT * FROM shopproduct WHERE shop_id = "000A" ) AS S
INNER JOIN (SELECT * FROM products WHERE sale_price < 2000) AS P
ON S.product_id = P.product_id;

SELECT s.*,p.*
FROM shopproduct as s
inner join 
products as p
on s.product_id = p.product_id
where p.sale_price < 2000 and s.shop_id = "000A";

SELECT sp.shop_id, sp.shop_name, max(sale_price) AS max_sale_price
FROM shopproduct AS sp
INNER JOIN 
products AS p
ON sp.product_id = p.product_id
GROUP BY sp.shop_name;

SELECT *
FROM(SELECT sp.shop_id, sp.shop_name, max(sale_price) AS max_sale_price
FROM shopproduct AS sp
INNER JOIN 
products AS p
ON sp.product_id = p.product_id
GROUP BY sp.shop_name) AS MP
INNER JOIN 
(SELECT product_name, product_type, sale_price FROM products) AS PP 
ON PP.sale_price = MP.max_sale_price;

# 外连结
SELECT sp.shop_id, sp.shop_name, p.product_name, p.sale_price, p.product_id
FROM products AS p
LEFT OUTER JOIN 
shopproduct AS sp
ON p.product_id = sp.product_id;
-- 找出那些在某个商店库存少于50的商品及对应的商店
SELECT sp.shop_id, sp.shop_name, p.product_id, p.product_name, p.sale_price, sp.quantity
FROM products AS p
LEFT OUTER JOIN 
(SELECT * FROM shopproduct  WHERE quantity < 50) AS sp
ON p.product_id = sp.product_id
ORDER BY shop_id is null, shop_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);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0005', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0006', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0007', 999);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0008', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0001', 10);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0002', 25);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0003', 34);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0004', 19);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0005', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0006', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0007', 0 );
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0008', 18);
COMMIT;

--  使用内连接找出每个商店都有那些商品, 每种商品的库存总量分别是多少
SELECT sp.shop_id, sp.shop_name, p.product_name, p.sale_price, ip.inventory_quantity
FROM shopproduct AS sp
INNER JOIN 
products AS p
ON p.product_id = sp.product_id
INNER JOIN 
InventoryProduct AS ip
ON p.product_id = ip.product_id
WHERE ip.inventory_id = "P001";

# 非等值连结
SELECT product_id, product_name, sale_price, count(p2_id) AS my_rank
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 products AS p1
LEFT OUTER JOIN 
products AS p2
ON p1.sale_price <= p2.sale_price) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY my_rank;

-- 请按照商品的售价从低到高,对售价进行累计求和
SELECT product_id, product_name, sale_price, sum(p2_price) AS sum_price
FROM (SELECT p1.product_id, p1.product_name, p1.sale_price, p2.product_id AS p2_id, p2.sale_price AS p2_price
	  FROM products AS p1
      LEFT OUTER JOIN 
      products AS p2
      ON p1.sale_price >= p2.sale_price
      ORDER BY p1.product_id, p1.sale_price) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY sale_price;

SELECT product_id, product_name, sale_price, sum(p2_price) AS sum_price
FROM (SELECT p1.product_id, p1.product_name, p1.sale_price, p2.product_id AS p2_id, p2.sale_price AS p2_price
	  FROM products AS p1
      LEFT OUTER JOIN 
      products 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) AS X
GROUP BY product_id, sale_price
ORDER BY sale_price, sum_price;

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM shopproduct AS SP
 CROSS JOIN products AS P;

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM shopproduct AS SP , products AS P;

# 练习
SELECT * FROM products
WHERE sale_price > 500
UNION 
SELECT * FROM product2
WHERE sale_price > 500;
-- 借助对称差的实现方式, 求product和product2的交集
SELECT * FROM products AS p1
WHERE 
p1.product_id NOT IN (SELECT product_id FROM product2 )
UNION 
SELECT * FROM product2 AS p2
WHERE 
p2.product_id NOT IN (SELECT product_id FROM products);

-- 每类商品中售价最高的商品都在哪些商店有售 
SELECT s.shop_id, s.shop_name, s.product_id, max_price
FROM shopproduct AS s 
INNER JOIN
(SELECT product_id, max(sale_price) AS max_price FROM products GROUP BY product_type, product_id) AS p 
ON s.product_id = p.product_id;
#  返回结果中，厨具的最高价格是6800，商品是高压锅对应编号0005，但返回的编号却是0004，导致大阪和名古屋有记录，但是在各个商店中均没有高压锅出售；
# 奇怪的是，为什么返回的最高价格的商品的编号不是对应的呢？

SELECT s.shop_id, s.shop_name, s.product_id, p.product_name, max_price
FROM shopproduct AS s 
INNER JOIN
(SELECT p1.product_id, p1.product_name, max(p1.sale_price) as max_price
FROM products p1
inner join 
shopproduct p2
on p1.product_id = p2.product_id
group by product_type) as p
ON s.product_id = p.product_id;

-- 分别使用内连结和关联子查询每一类商品中售价最高的商品
SELECT product_id, product_type, product_name, sale_price 
FROM products AS p1
WHERE sale_price = (SELECT max(sale_price) FROM products AS p2
					WHERE p1.product_type = p2.product_type
					GROUP BY product_type);
# 内连结
SELECT p1.product_id, p1.product_type, p1.product_name, p1.sale_price, p2.max_price
FROM products p1
INNER JOIN 
(SELECT MAX(sale_price) AS max_price, product_type  # 内连结每类商品的最大价格
FROM products 
GROUP BY product_type) AS p2
ON p1.product_type = p2.product_type
WHERE sale_price = p2.max_price;    


-- 在 product 表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和
SELECT product_id, product_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 products AS p1
             INNER JOIN 
             products 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 sale_price) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY sale_price, cum_price;

# 使用窗口函数
SELECT  product_id, product_name, sale_price,
		SUM(sale_price) OVER (ORDER BY sale_price) cum_price
FROM product;


select shop_id, shop_name
from shopproduct a
where product_id in(select product_id from product b
					where a.product_id = b.product_id AND sale_price in (select max(sale_price) from product group by product_type)
					group by b.product_type);
这次的学习主要是表的连结，内连接，外连接，左外连接，右外连接，还涉及了表的交并补集的运算。内连接与关联子查询的区别和转化是本次学习的重点。除了等值连接外，还有非等值连结，主要运用在求排名和计算累计之和等。这次的作业中，求每组商品最高售价的商品在哪些商店有出售，令我比较费解，期待答案。