投稿
  1. 极客之音首页
  2. 技术随笔

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

Java光头强 • • 技术随笔 • 阅读 187

导读：本篇文章讲解 不知道 MySQL 咋学？刷完牛客这 50 道题就够了！(第八篇)，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

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

前言

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

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

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

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

SQL36 从 Products 表中检索所有的产品名称以及对应的销售总数

描述

Products 表中检索所有的产品名称：prod_name、产品 id：prod_id

prod_id prod_name
a0001 egg
a0002 sockets
a0013 coffee
a0003 cola

OrderItems 代表订单商品表，订单产品：prod_id、售出数量：quantity

prod_id quantity
a0001 105
a0002 1100
a0002 200
a0013 1121
a0003 10
a0003 19
a0003 5

问题

编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity) 检索）。

示例结果

返回产品名称 prod_name 和产品售出数量总和

prod_name quant_sold
egg 105
sockets 1300
coffee 1121
cola 34

示例解析

prod_name 是 cola 的 prod_id 为 a0003，quantity 总量为 34，返回结果无需排序。

示例

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

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');

DROP TABLE IF EXISTS `OrderItems`;

CREATE TABLE IF NOT EXISTS `OrderItems`(

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

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

);

INSERT `OrderItems` VALUES ('a0001',105),('a0002',1100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5);

解答

解法类似于 35 题，同样主要有两种方式，第一种是通过内连接的方式。

1

SELECT prod_name, SUM(quantity) AS quant_sold FROM OrderItems, Products WHERE Products.prod_id = OrderItems.prod_id GROUP BY prod_name;

第二种则是通过子查询的方式。

1

SELECT prod_name, (SELECT SUM(quantity) FROM OrderItems WHERE OrderItems.prod_id = Products.prod_id) FROM Products;

 

SQL37 返回顾客名称和相关订单号

描述

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

cust_id cust_name
cust10 andy
cust1 ben
cust2 tony
cust22 tom
cust221 an
cust2217 hex

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

order_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

问题

编写 SQL 语句，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行升序排序。你可以尝试用两个不同的写法，一个使用简单的等联结语法，另外一个使用 INNER JOIN。

示例结果

cust_name 代表用户名称 cust_name 和订单号 order_num。

cust_name order_num
an a5
andy a1
ben a2
hex a7
tom a4
tony a3

示例解析

顾客名称为 an 的 cust_id 为 cust221，他的订单号为 a5。

示例

1

2

3

4

5

6

7

8

9

10

11

12

13

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');

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');

解答

考察 SQL 中的 INNER JOIN，其实也就是 JOIN。主要用于筛选出两个表中的交集部分。

使用语法如下：

1

2

3

4

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name=table2.column_name;

因此，此题使用 INNER JOIN 实现的方法如下。

1

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

此外，也可以使用最常用的 WHERE 来进行联接。

1

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

SQL38 返回顾客名称和相关订单号以及每个订单的总价

描述

Customers 表有字段，顾客名称：cust_name、顾客 id：cust_id

cust_id cust_name
cust10 andy
cust1 ben
cust2 tony
cust22 tom
cust221 an
cust2217 hex

Orders 订单信息表，含有字段，订单号：order_num、顾客 id：cust_id

order_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

OrderItems 表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price

order_num quantity item_price
a1 1000 10
a2 200 10
a3 10 15
a4 25 50
a5 15 25
a7 7 7

问题

除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

示例结果

返回顾客名称 cust_name、订单号 order_num、订单总额 OrderTotal

cust_name order_num OrderTotal
an a5 375
andy a1 10000
ben a2 2000
hex a7 49
tom a4 1250
tony a3 150

示例解析

例如顾客名称 cust_name 为 an 的顾客的订单 a5 的订单总额为 quantity*item_price = 15 * 25 = 375，最后以 cust_name 和 order_num 来进行升序排序。

示例

复制代码

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

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');

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 `OrderItems`;

CREATE TABLE IF NOT EXISTS `OrderItems`(

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

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

  item_price INT(16) NOT NULL COMMENT '商品价格'

);

INSERT `OrderItems` VALUES ('a1',1000,10),('a2',200,10),('a3',10,15),('a4',25,50),('a5',15,25),('a7',7,7);

解答

SQL 语句先后顺序：

1

2

3

4

5

SELECT  ……

FROM ……

WHERE ……

GROUP BY ……

ORDER BY ……

书写 SQL 语句时，一定要遵守以上关键字的先后顺序。然后根据题意将各个条件组合即可。

1

2

3

4

5

SELECT cust_name, Orders.order_num, SUM(quantity * item_price) AS OrderTotal

FROM Customers, Orders, OrderItems

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

GROUP BY Customers.cust_name, Orders.order_num

ORDER BY cust_name, Orders.order_num;

 

SQL39 确定哪些订单购买了 prod_id 为 BR01 的产品（二）

描述

表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客id和订单日期 order_date

OrderItems 表

prod_id order_num
BR01 a0001
BR01 a0002
BR02 a0003
BR02 a0013

Orders 表

order_num cust_id order_date
a0001 cust10 2022-01-01 00:00:00
a0002 cust1 2022-01-01 00:01:00
a0003 cust1 2022-01-02 00:00:00
a0013 cust2 2022-01-01 00:20:00

问题

编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 “BR01” 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。

提示：这一次使用联结和简单的等联结语法。

示例结果

返回顾客 id cust_id 和定单日期 order_date

cust_id order_date
cust10 2022-01-01 00:00:00
cust1 2022-01-01 00:01:00

示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的下单时间分别为 2022-01-01 00:00:00 和 2022-01-01 00:01:00

示例

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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 ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  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',

    order_date TIMESTAMP NOT NULL COMMENT '下单时间'

  );

  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

解答

多个条件的组合查询，先查询出产品 id 为 BR01 的 order_num，然后从查询出的结果列中再去筛选出 Orders 表中 order_num，最后则是正序排序即可。

1

2

3

4

SELECT cust_id, order_date

FROM Orders, (SELECT order_num FROM OrderItems WHERE prod_id = 'BR01') AS result

WHERE result.order_num = Orders.order_num

ORDER BY order_date;

 

SQL40 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）

描述

有表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date；Customers 表含有 cust_email 顾客邮件和 cust_id 顾客 id

OrderItems 表

prod_id order_num
BR01 a0001
BR01 a0002
BR02 a0003
BR02 a0013

Orders 表

order_num cust_id order_date
a0001 cust10 2022-01-01 00:00:00
a0002 cust1 2022-01-01 00:01:00
a0003 cust1 2022-01-02 00:00:00
a0013 cust2 2022-01-01 00:20:00

Customers 表代表顾客信息，cust_id 为顾客 id，cust_email 为顾客 email

cust_id cust_email
cust10 cust10@cust.com
cust1 cust1@cust.com
cust2 cust2@cust.com

问题

返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

提示：涉及到 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id，但是必须使用 INNER JOIN 语法。

示例结果

返回顾客 email cust_email

cust_email
cust10@cust.com
cust1@cust.com

示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的顾客 email cust_email 分别是：cust10@cust.com 、cust1@cust.com

示例

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

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 ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  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',

    order_date TIMESTAMP NOT NULL COMMENT '下单时间'

  );

  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

DROP TABLE IF EXISTS `Customers`;

CREATE TABLE IF NOT EXISTS `Customers`(

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

    cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'

  );

INSERT `Customers` VALUES ('cust10','cust10@cust.com'),('cust1','cust1@cust.com'),('cust2','cust2@cust.com');

解答

多个内联接的组合，先是筛选出 OrderItems 和 Orders 表中 order_num 相同的列，然后根据筛选出的结果列中的 cust_id 从 Customers 表中找对应的顾客信息即可。

1

2

3

4

5

6

SELECT 

    cust_email

FROM

    Customers JOIN Orders ON

    Orders.cust_id = Customers.cust_id  

    JOIN OrderItems  ON OrderItems.prod_id = 'BR01' AND OrderItems.order_num = Order

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/116111.html

赞 (0)
Java光头强的头像Java光头强
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！