• 不知道 MySQL 咋学？刷完牛客这 50 道题就够了！(第九篇)

    本次内容共分为十篇，每次五题，其它篇章在主页Mysql专栏中

    前言

    你是否还在烦恼 SQL 该从何学起，或者学了 SQL 想找个地方练练手？好巧不巧，最近在工作之余登上牛客，发现了牛客不知道啥时候上线了SQL 必知必会的练习题。

    《SQL 必知必会》作为麻省理工学院、伊利诺伊大学等众多大学的参考教材，由浅入深地讲解了SQL的基本概念和语法。涉及数据的排序、过滤和分组，以及表、视图、联结、子查询、游标、存储过程和触发器等内容。实例丰富，方便查阅，可以说作为一个 CRUD BOY/GIRL 必读书目。

    想着正好给它刷一遍，然后将自己刷题的一些想法总结下，于是有了今天这篇文章，希望能给需要的小伙伴一点点帮助

    SQL41 确定最佳顾客的另一种方式（二）

    描述

    OrderItems 表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱，OrderItems 表有订单号 order_num 和 item_price 商品售出价格、quantity 商品数量

    order_numitem_pricequantity
    a110105
    a211100
    a21200
    a421121
    a5510
    a2119
    a775

    Orders 表含有字段 order_num 订单号、cust_id 顾客 id

    order_numcust_id
    a1cust10
    a2cust1
    a3cust2
    a4cust22
    a5cust221
    a7cust2217

    顾客表 Customers 有字段 cust_id 客户 id、cust_name 客户姓名

    cust_idcust_name
    cust10andy
    cust1ben
    cust2tony
    cust22tom
    cust221an
    cust2217hex

    问题

    编写 SQL 语句，返回订单总价不小于 1000 的客户名称和总额（OrderItems 表中的 order_num）。

    提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。

    示例结果

    cust_nametotal_price
    andy1050
    ben1319
    tom2242

    示例解析

    总额（item_price 乘以 quantity）大于等于 1000 的订单号，例如 a2 对应的顾客id 为 cust1，cust1 的顾客名称 cust_name 是 ben，最后返回 ben 作为 order_num a2 的quantity * item_price 总和的结果 1319。

    示例

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    DROP TABLE IF EXISTS `OrderItems`;

    CREATE TABLE IF NOT EXISTS `OrderItems`(

        order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',

        item_price INT(16) NOT NULL COMMENT '售出价格',

        quantity INT(16) NOT NULL COMMENT '商品数量'

    );

    INSERT `OrderItems` VALUES ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);

    DROP TABLE IF EXISTS `Customers`;

    CREATE TABLE IF NOT EXISTS `Customers`(

        cust_id VARCHAR(255) NOT NULL COMMENT '客户id',

        cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'

    );

    INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

    DROP TABLE IF EXISTS `Orders`;

    CREATE TABLE IF NOT EXISTS `Orders`(

      order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',

      cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'

    );

    INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

    解答

    主要考察的知识点：

    • 取别名：AS 关键字
    • 求和：SUM()
    • GROUP BY 和 HAVING 同时使用用于过滤结果
    • 排序：ORDER BY 关键字

    主要考察对多个 SQL 关键字的运用，同时还需要注意 SQL 中关键字的前后顺序。

    1

    2

    3

    4

    5

    6

    SELECT cust_name, SUM(item_price * quantity) AS total_price

    FROM OrderItems, Orders, Customers

    WHERE OrderItems.order_num = Orders.order_num AND Orders.cust_id = Customers.cust_id

    GROUP BY cust_name

    HAVING total_price >= 1000

    ORDER BY total_price;

     

    SQL42 检索每个顾客的名称和所有的订单号（一）

    描述

    Customers 表代表顾客信息含有顾客 id cust_id 和顾客名称 cust_name

    cust_idcust_name
    cust10andy
    cust1ben
    cust2tony
    cust22tom
    cust221an
    cust2217hex

    Orders 表代表订单信息含有订单号 order_num 和顾客 id cust_id

    order_numcust_id
    a1cust10
    a2cust1
    a3cust2
    a4cust22
    a5cust221
    a7cust2217

    问题

    使用 INNER JOIN 编写 SQL 语句，检索每个顾客的名称（Customers 表中的 cust_name）和所有的订单号（Orders 表中的 order_num），最后根据顾客姓名 cust_name 升序返回。

    示例结果

    返回顾客名称 cust_name 和订单号 order_num

    cust_nameorder_num
    ana5
    andya1
    bena2
    hexa7
    toma4
    tonya3

    示例

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    DROP TABLE IF EXISTS `Customers`;

    CREATE TABLE IF NOT EXISTS `Customers`(

        cust_id VARCHAR(255) NOT NULL COMMENT '客户id',

        cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'

    );

    INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

    DROP TABLE IF EXISTS `Orders`;

    CREATE TABLE IF NOT EXISTS `Orders`(

      order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',

      cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'

    );

    INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

    解答

    主要考察 INNER JOIN 的用法，其使用语法如下：

    1

    2

    3

    4

    SELECT column_name(s)

    FROM table1

    INNER JOIN table2

    ON table1.column_name=table2.column_name;

    要实现本题，利用上述语法填入对应字段名和表名即可，最后则是关键字 ORDER BY 的使用。

    1

    SELECT cust_name, Orders.order_num FROM Customers JOIN Orders ON Orders.cust_id = Customers.cust_id ORDER BY cust_name;

     

    SQL43 检索每个顾客的名称和所有的订单号（二）

    描述

    Orders 表代表订单信息含有订单号 order_num和顾客 id cust_id

    order_numcust_id
    a1cust10
    a2cust1
    a3cust2
    a4cust22
    a5cust221
    a7cust2217

    Customers 表代表顾客信息含有顾客 id cust_id 和 顾客名称 cust_name

    cust_idcust_name
    cust10andy
    cust1ben
    cust2tony
    cust22tom
    cust221an
    cust2217hex
    cust40ace

    问题

    检索每个顾客的名称（Customers 表中的 cust_name）和所有的订单号（Orders 表中的 order_num），列出所有的顾客，即使他们没有下过订单。最后根据顾客姓名 cust_name 升序返回。

    示例结果

    返回顾客名称 cust_name 和订单号 order_num

    cust_nameorder_num
    aceNULL
    ana5
    andya1
    bena2
    hexa7
    toma4
    tonya3

    示例解析

    基于两张表，返回订单号 a1 的顾客名称 andy 等人，没有下单的顾客 ace 也统计了进来。

    示例

    复制代码

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    DROP TABLE IF EXISTS `Customers`;

    CREATE TABLE IF NOT EXISTS `Customers`(

        cust_id VARCHAR(255) NOT NULL COMMENT '客户id',

        cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'

    );

    INSERT `Customers` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex'),('cust40','ace');

    DROP TABLE IF EXISTS `Orders`;

    CREATE TABLE IF NOT EXISTS `Orders`(

      order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',

      cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'

    );

    INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

    解答

    根据题意，主要以 Customers 表中的列为主，然后取 Customers 和 Orders 中的交集。对于 Orders 表中不存在的列则取值 null。所以可以使用外联结中的 LEFT JION，其使用语法如下：

    1

    2

    3

    4

    SELECT column_name(s)

    FROM table1

    LEFT OUTER JOIN table2

    ON table1.column_name=table2.column_name;

    套用上述语法，填入对应表和列名即可实现，最后则是再加入简单的排序即可。

    1

    SELECT cust_name, Orders.order_num FROM Customers LEFT JOIN Orders On Orders.cust_id = Customers.cust_id ORDER BY cust_name;

     

    SQL44 返回产品名称和与之相关的订单号

    描述

    Products 表为产品信息表含有字段 prod_id 产品 id、prod_name 产品名称

    prod_idprod_name
    a0001egg
    a0002sockets
    a0013coffee
    a0003cola
    a0023soda

    OrderItems 表为订单信息表含有字段 order_num 订单号和产品 id prod_id

    prod_idorder_num
    a0001a105
    a0002a1100
    a0002a200
    a0013a1121
    a0003a10
    a0003a19
    a0003a5

    问题

    使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按照产品名称升序排序。

    示例结果

    返回产品名称 prod_name 和订单号 order_num

    prod_nameorder_num
    coffeea1121
    colaa5
    colaa19
    colaa10
    egga105
    socketsa200
    socketsa1100
    sodaNULL

    示例解析

    返回产品和对应实际支付订单的订单号，但是无实际订单的产品 soda 也返回，最后根据产品名称升序排序。

    示例

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    DROP TABLE IF EXISTS `Products`;

    CREATE TABLE IF NOT EXISTS `Products` (

    `prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',

    `prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'

    );

    INSERT INTO `Products` VALUES ('a0001','egg'),

    ('a0002','sockets'),

    ('a0013','coffee'),

    ('a0003','cola'),

    ('a0023','soda');

    DROP TABLE IF EXISTS `OrderItems`;

    CREATE TABLE IF NOT EXISTS `OrderItems`(

        prod_id VARCHAR(255) NOT NULL COMMENT '产品id',

        order_num VARCHAR(255) NOT NULL COMMENT '商品数量'

    );

    INSERT `OrderItems` VALUES ('a0001','a105'),('a0002','a1100'),('a0002','a200'),('a0013','a1121'),('a0003','a10'),('a0003','a19'),('a0003','a5');

    解答

    此题解法类似于 43 题，主要使用 OUTER JOIN 中的左联结 LEFT JOIN，主要清除相关语法，然后套用填入表名和列名即可。

    1

    2

    3

    SELECT prod_name, OrderItems.order_num FROM Products

    LEFT JOIN OrderItems ON OrderItems.prod_id = Products.prod_id

    ORDER BY prod_name;

     

    SQL45 返回产品名称和每一项产品的总订单数

    描述

    Products 表为产品信息表含有字段 prod_id 产品 id、prod_name 产品名称

    prod_idprod_name
    a0001egg
    a0002sockets
    a0013coffee
    a0003cola
    a0023soda

    OrderItems 表为订单信息表含有字段 order_num 订单号和产品 id prod_id

    prod_idorder_num
    a0001a105
    a0002a1100
    a0002a200
    a0013a1121
    a0003a10
    a0003a19
    a0003a5

    问题

    使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和每一项产品的总订单数（不是订单号），并按产品名称升序排序。

    示例结果

    返回产品名称 prod_name 和订单号订单数 orders

    prod_nameorders
    coffee1
    cola3
    egg1
    sockets2
    soda0

    示例解析

    返回产品和产品对应的实际支付的订单数，但是无实际订单的产品 soda 也返回，最后根据产品名称升序排序。

    示例

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    DROP TABLE IF EXISTS `Products`;

    CREATE TABLE IF NOT EXISTS `Products` (

    `prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',

    `prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'

    );

    INSERT INTO `Products` VALUES ('a0001','egg'),

    ('a0002','sockets'),

    ('a0013','coffee'),

    ('a0003','cola'),

    ('a0023','soda');

    DROP TABLE IF EXISTS `OrderItems`;

    CREATE TABLE IF NOT EXISTS `OrderItems`(

        prod_id VARCHAR(255) NOT NULL COMMENT '产品id',

        order_num VARCHAR(255) NOT NULL COMMENT '商品数量'

    );

    INSERT `OrderItems` VALUES ('a0001','a105'),('a0002','a1100'),('a0002','a200'),('a0013','a1121'),('a0003','a10'),('a0003','a19'),('a0003','a5');

    解答

    考察的知识点仍然是外连接，只不过加入了 COUNT() 函数用于分组统计，最后同样是简单的排序。

    1

    2

    3

    SELECT prod_name, COUNT(order_num) AS orders

    FROM Products LEFT JOIN OrderItems ON OrderItems.prod_id = Products.prod_id

    GROUP BY prod_name ORDER BY prod_name;

     

     

     

  • 相关阅读:
    什么是应用性能管理（APM）
    2022最新版-李宏毅机器学习深度学习课程-P23 为什么用了验证集结果还是过拟合
    VJ第一周个人训练赛
    轻量级的搜索引擎MeiliSearch
    相机突然断电，保存的DAT视频文件如何修复
    vue webpack/vite的区别
    c++视觉图像线性混合
    竞赛选题 深度学习的口罩佩戴检测 - opencv 卷积神经网络 机器视觉 深度学习
    Spring Boot (三)
    上周热点回顾（6.12-6.18）
  • 原文地址：https://blog.csdn.net/w20001118/article/details/125896416