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

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

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

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

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

前言

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

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

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

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

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

描述

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

order_num item_price quantity
a1 10 105
a2 1 1100
a2 1 200
a4 2 1121
a5 5 10
a2 1 19
a7 7 5

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

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

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

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

问题

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

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

示例结果

cust_name total_price
andy 1050
ben 1319
tom 2242

示例解析

总额（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_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

问题

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

示例结果

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

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

示例

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_num cust_id
a1 cust10
a2 cust1
a3 cust2
a4 cust22
a5 cust221
a7 cust2217

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

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

问题

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

示例结果

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

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

示例解析

基于两张表，返回订单号 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_id prod_name
a0001 egg
a0002 sockets
a0013 coffee
a0003 cola
a0023 soda

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

prod_id order_num
a0001 a105
a0002 a1100
a0002 a200
a0013 a1121
a0003 a10
a0003 a19
a0003 a5

问题

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

示例结果

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

prod_name order_num
coffee a1121
cola a5
cola a19
cola a10
egg a105
sockets a200
sockets a1100
soda NULL

示例解析

返回产品和对应实际支付订单的订单号，但是无实际订单的产品 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_id prod_name
a0001 egg
a0002 sockets
a0013 coffee
a0003 cola
a0023 soda

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

prod_id order_num
a0001 a105
a0002 a1100
a0002 a200
a0013 a1121
a0003 a10
a0003 a19
a0003 a5

问题

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

示例结果

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

prod_name orders
coffee 1
cola 3
egg 1
sockets 2
soda 0

示例解析

返回产品和产品对应的实际支付的订单数，但是无实际订单的产品 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;

 

 

 

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

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

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

相关推荐

发表回复

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