SQL学习笔记_Aliyun4

傅里叶不会分析

已于 2022-10-29 19:25:58 修改

阅读量342

点赞数

分类专栏：数据库文章标签： sql 学习数据库

于 2022-10-28 18:29:50 首次发布

本文链接：https://blog.csdn.net/Utopia2JJ/article/details/127576853

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SQL学习笔记_Aliyun4

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

UNION

对两张表进行并集运算，并且UNION等集合运算符通常都会出去重复的记录

若想要包含重复的数据行则使用关键字UNION ALL

-- 假设连锁店想要增加毛利率超过 50%或者售价低于 800 的货物的存货量, 请使用 UNION 对分别满足上述两个条件的商品的查询结果求并集.
SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM product 
 WHERE sale_price<800
  
 UNION
 
SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM product 
 WHERE sale_price>1.5*purchase_price;

SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM product 
 WHERE sale_price < 800 
    OR sale_price > 1.5 * purchase_price;
    #这里WHERE...OR等效于UNION的用法

在这里之所以可以等效是因为用的是同一张表UNION，如果是两张不同的表合在一起则只能靠UNION

JOIN

UNION和INTERSECT等集合运算的特征就是以行方向为单位进行操作，而表的连结（JOIN）就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算.

INNER JOIN

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

进行连结时需要在FROM子句中使用多张表
必须使用ON子句来指定连接条件
SELECT子句中的列最好按照表名，列名的格式来使用

练习：

在这里插入图片描述

-- 不使用子查询
SELECT SP.*,P.*
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id=P.product_id
WHERE shop_id='000A'
AND sale_price<2000;

结合GROUP BY子句使用内连结

最简单的情形, 是在内连结之前就使用 GROUP BY 子句，但是如果分组列和被聚合的列不在同一张表, 且二者都未被用于连结两张表, 则只能先连结, 再聚合.

-- 每个商店中，售价最高的商品的售价分别是多少？
SELECT SP.shop_id,SP.shop_name,MAX(P.sale_price) AS max_price
FROM shopproduct AS SP 
INNER JOIN product AS P
    ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name

-- 如何获取每个商店里售价最高的商品的名称和售价?
SELECT *
FROM shopproduct AS SP 
INNER JOIN product AS P
    ON SP.product_id = P.product_id
WHERE P.sale_price=(SELECT MAX(P.sale_price) from 
INNER JOIN product AS P
    ON SP.product_id = P.product_id
GROUP BY SP.shop_id,SP.shop_name)

利用关联子查询找组内最大值（TopN问题）

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT '' COMMENT '姓名',
`age` int(3) NULL DEFAULT 0 COMMENT '年龄',
`c_class` int(4) NULL DEFAULT 0 COMMENT '班级',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
 
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', 22, 1);
INSERT INTO `student` VALUES (2, '李四', 26, 1);
INSERT INTO `student` VALUES (3, '王五', 20, 2);
INSERT INTO `student` VALUES (4, '赵六', 20, 2);
INSERT INTO `student` VALUES (5, '孙七', 22, 3);
INSERT INTO `student` VALUES (6, '李八', 28, 3);
INSERT INTO `student` VALUES (7, '阿九', 28, 3);



-- ----------------------------
-- 利用关联子查询找组内最大值
-- ----------------------------

SELECT *
FROM student as s
WHERE age=(SELECT MAX(age) FROM student as s1 where s1.c_class=s.c_class group by c_class)

自连结SELF JOIN

之前的内连结, 连结的都是不一样的两个表。但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法。

-- 利用自连结，找出售价高于该类商品平均价格的商品

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;

上一章使用关联子查询达到该目的

SELECT  P1.product_id,P1.product_name,P1.sale_price
FROM product
WHERE sale_price>(SELECT AVG(sale_price) 
                  FROM product as p2 
                  WHERE product.product_type=p2.product_type
                  GROUP BY product_type);

自然连结(NATURAL JOIN)

两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。
使用自然连结还可以求出两张表或子查询的公共部分(求交集)

SELECT *  FROM shopproduct NATURAL JOIN product

外连结（OUTER JOIN）

内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行。

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结。

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。（所以左连结和右连结没有本质区别）

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

外连结要点 1: 选取出单张表中全部的信息

在实际的业务中,例如想要生成固定行数的单据时,就需要使用外连结.如果使用内连结的话,根据 SELECT 语句执行时商店库存状况的不同,结果的行数也会发生改变,生成的单据的版式也会受到影响,而使用外连结能够得到固定行数的结果

外连结要点 2：使用 LEFT、RIGHT 来指定主表.

使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表。通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT,则更换为 LEFT), 两种方式会到完全相同的结果。

结合WHERE子句使用左连结

-- 使用外连结从shopproduct表和product表中找出那些在某个商店库存少于50的商品及对应的商店

-- 失败查询，因为在WHERE过滤的时候NULL值已经被筛选掉了，所以我们先得进行筛选再用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 shopproduct AS SP
    ON SP.product_id = P.product_id
 WHERE quantity< 50;
 
 -- 正确使用左连结
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 s(SELECT * 
                   FROM shopproduct
                   WHERE quantity<50) AS SP
                   ON SP.product_id=P.product_id;

多表连结

多表内连结

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 = 'P001';

多表外连结

SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,IP.inventory_quantity
  FROM product AS P
  LEFT OUTER JOIN shopproduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id;

非等值连结进行排名

-- 对 product 表中的商品按照售价赋予排名
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 product as P1
    LEFT OUTER JOIN product as P2
    	ON 	P1.sale_price<=P2.sale_price) AS X
 GROUP BY product_id,product_name,sale_price
 ORDER BY my_rank;
 -- -------------
 -- 上述排名方案存在一些问题--如果两个商品的价格相等, 则会导致两个商品的排名错误, 例如, 叉子和打孔器的排名应该都是第六, 但上述查询导致二者排名都是第七. 试修改上述查询使得二者的排名均为第六。
 
  -- COUNT(DISTINCT P2_id)
  -- -------------

交叉连结CROSS JOIN（笛卡尔积）

之前的无论是外连结内连结, 一个共同的必备条件就是连结条件–ON 子句, 用来指定连结的条件.（或者USING BY）在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积（两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合）

交叉连结没有应用到实际业务之中的原因有两个.一是其结果没有实用价值,二是由于其结果行数太多,需要花费大量的运算时间和高性能设备的支持。

连结的特定语法和过时语法

不推荐使用理由主要有以下三点:

第一,使用这样的语法无法马上判断出到底是内连结还是外连结（又或者是其他种类的连结）。

第二,由于连结条件都写在 WHERE 子句之中,因此无法在短时间内分辨出哪部分是连结条件,哪部分是用来选取记录的限制条件。

第三,我们不知道这样的语法到底还能使用多久.每个 DBMS 的开发者都会考虑放弃过时的语法,转而支持新的语法.虽然并不是马上就不能使用了,但那一天总会到来的。

-- 过时语法
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM shopproduct SP, product P
 WHERE SP.product_id = P.product_id
   AND SP.shop_id = '000A';

练习4.1

-- 找出 product 和 product2 中售价高于 500 的商品的基本信息。
SELECT *
FROM product
WHERE sale_price>500
UNION
SELECT *
FROM product2
WHERE sale_price>500;

练习4.2

-- 借助对称差的实现方式, 求product和product2的交集。
#两个集合的交可以看作是两个集合的并去掉两个集合的对称差。
SELECT *
FROM (SELECT * FROM product UNION SELECT * FROM product2)
WHERE product_id NOT IN (
  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));

练习4.3

-- 每类商品中售价最高的商品都在哪些商店有售 ？
SELECT SP.shop_id,SP.shop_name,MP.MAX_SALEPRICE
FROM shopproduct as SP
INNER JOIN (SELECT product_id,product_name,MAX(sale_price) AS MAX_SALEPRICE
            FROM product
            GROUP BY product_type) #分类后找出返回各类的最大值
) AS MP
ON MP.product_id=SP.product_id

练习4.4

-- 分别使用内连结和关联子查询每一类商品中售价最高的商品。
# 关联子查询
SELECT product_type,product_name,sale_price 
FROM product AS P1 
WHERE sale_price =(SELECT MAX(sale_price) FROM
				product AS P2 
				WHERE P1.product_type = P2.product_type
                 GROUP BY product_type);
             
# 内连结
SELECT product_type,product_name,Mx.max_price
FROM product AS P1
INNER JOIN (SELECT product_type,MAX(sale_price) as max_price
            FROM product
            GROUP BY product_type
) AS MX ON MX.product_type=P1.product_type
WHERE MX.max_price=P1.sale_price

练习4.5

#SELECT product_id, product_name, sale_price,SUM(SELECT P2.sale_price) AS salesum
#FROM (SELECT P1.product_id, P1.product_name,P1.sale_price,P2.product_id,P2.product_name,P2.sale_price
     #FROM product AS P1
     #WHERE P1.sale_price>(SELECT P2.sale_price FROM product AS P2)) AS X
#GROUP BY product_id,product_name,sale_price
#ORDER BY sale_price,salesum
-- 用关联子查询实现：在 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 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;

References

https://zhuanlan.zhihu.com/p/531723523

https://zhuanlan.zhihu.com/p/93653419