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

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

前言

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

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

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

 

作者：村雨遥
链接：不知道 MySQL 咋学？刷完牛客这 50 道题就够了！_笔经面经_牛客网
来源：牛客网
 

SQL21 打折

描述

我们的示例商店正在进行打折促销，所有产品均降价 10%。Products 表包含 prod_id产品 id、prod_price 产品价格

问题

编写 SQL语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）

示例结果

返回产品 id prod_id、产品价格 prod_price、销售价格 sale_price

prod_id	prod_price	sale_price
a0011	9.49	8.541
a0019	600	540
b0019	1000	900

示例解析

sale_price 的价格是 prod_price 的 90%

示例

1 2 3 4 5 6 7 8

DROP TABLE IF EXISTS `Products`; CREATE TABLE IF NOT EXISTS `Products` ( `prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID', `prod_price` DOUBLE NOT NULL COMMENT '产品价格' ); INSERT INTO `Products` VALUES ('a0011',9.49), ('a0019',600), ('b0019',1000);

解答

此题考察了取别名以及如何在 SQL 中直接使用算数运算，sale_price 不是表中的数据，而是通过 prod_price 而来，所以需要通过关键字 AS 来进行取别名，最终的实现语句如下。

1	SELECT prod_id, prod_price, prod_price * 0.9 AS sale_price FROM Products;

 

SQL22 顾客登录名

描述

我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。

给出 Customers 表 如下：

cust_id	cust_name	cust_contact	cust_city
a1	Andy Li	Andy Li	Oak Park
a2	Ben Liu	Ben Liu	Oak Park
a3	Tony Dai	Tony Dai	Oak Park
a4	Tom Chen	Tom Chen	Oak Park
a5	An Li	An Li	Oak Park
a6	Lee Chen	Lee Chen	Oak Park
a7	Hex Liu	Hex Liu	Oak Park

问题

编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。

示例结果

返回顾客 id cust_id，顾客名称 cust_name，顾客登录名 user_login

cust_id	cust_name	user_login
a1	Andy Li	ANOAK
a2	Ben Liu	BEOAK
a3	Tony Dai	TOOAK
a4	Tom Chen	TOOAK
a5	An Li	ANOAK
a6	Lee Chen	LEOAK
a7	Hex Liu	HEOAK

示例解析

例如，登录名是 ANOAK（Andy Li，居住在 Oak Park）

示例

1 2 3 4 5 6 7 8

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 '客户姓名',     cust_contact VARCHAR(255) NOT NULL COMMENT '客户联系人',     cust_city VARCHAR(255) NOT NULL COMMENT '客户城市' ); INSERT `Customers` VALUES ('a1','Andy Li','Andy Li','Oak Park'),('a2','Ben Liu','Ben Liu','Oak Park'),('a3','Tony Dai','Tony Dai','Oak Park'),('a4','Tom Chen','Tom Chen','Oak Park'),('a5','An Li','An Li','Oak Park'),('a6','Lee Chen','Lee Chen','Oak Park'),('a7','Hex Liu','Hex Liu','Oak Park');

解答

根据题意以及示例结果，可以看到登录名全是大写，所以需要用到函数 upper()，然后需要将顾客联系人前两个字符和所在城市的前三个字符相拼接，则需要用到函数 concat()，再接着就是需要从 cust_name 和 cust_city 中截取字符串，则需要用到 substring()，最后则是通过将字符串拼接转换之后取别名为 user_login，需要用到关键字 AS。以上提到的三个函数用法如下：

• upper(字符串)：将字符串中所有字符转换为大写。
• substring(字符串, 起始位置, 截取的字符数)，需要注意的是起始位置是从 1 开始的。
• concat(字符串 1, 字符串 2, 字符串 3, …)

1	SELECT cust_id, cust_name, upper(concat(substring(cust_name, 1, 2), substring(cust_city, 1, 3))) AS user_login FROM Customers;

 

SQL23 返回 2020 年 1 月的所有订单的订单号和订单日期

描述

Orders 订单表

order_num	order_date
a0001	2020-01-01 00:00:00
a0002	2020-01-02 00:00:00
a0003	2020-01-01 12:00:00
a0004	2020-02-01 00:00:00
a0005	2020-03-01 00:00:00

问题

编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序

【示例结果】

返回订单号 order_num，和 order_date 订单时间

order_num	order_date
a0001	2020-01-01 00:00:00
a0003	2020-01-01 12:00:00
a0002	2020-01-02 00:00:00

示例解析

a0001、a0002、a0003 时间属于 2020 年 1 月

示例

1 2 3 4 5 6 7 8 9 10

DROP TABLE IF EXISTS `Orders`; CREATE TABLE IF NOT EXISTS `Orders`(     order_num VARCHAR(255) NOT NULL COMMENT '订单号',     order_date TIMESTAMP NOT NULL COMMENT '订单日期' ); INSERT `Orders` VALUES ('a0001','2020-01-01 00:00:00'), ('a0002','2020-01-02 00:00:00'), ('a0003','2020-01-01 12:00:00'), ('a0004','2020-02-01 00:00:00'), ('a0005','2020-03-01 00:00:00');

解答

其实我们依然可以将本题看做模糊查询，只要订单日期满足对应条件即可，可以使用关键字 WHERE … LIKE … 来实现。

1	SELECT order_num, order_date FROM Orders WHERE order_date LIKE '2020-01%' ORDER BY order_date;

此外，SQL 中也提供了对于日期的操作函数 date_format()，用于返回日期的一部分。

1	SELECT order_num, order_date FROM Orders WHERE date_format(order_date, '%Y-%m') = '2020-01' ORDER BY order_date;

另外，我们还可以单独提取出日期中的年份和月份，然后将两个条件串联，筛选出同时满足两个条件的数据。

1	SELECT order_num, order_date FROM Orders WHERE year(order_date) = 2020 AND month(order_date) = 1 ORDER BY order_date;

 

SQL24 确定已售出产品的总数

描述

OrderItems 表代表售出的产品，quantity 代表售出商品数量。

quantity
10
100
1000
10001
2
15

问题

编写 SQL 语句，确定已售出产品的总数。

示例结果

返回 items_ordered 列名，表示已售出商品的总数。

items_ordered
11128

示例

1 2 3 4 5

DROP TABLE IF EXISTS `OrderItems`; CREATE TABLE IF NOT EXISTS `OrderItems`(     quantity INT(16) NOT NULL COMMENT '商品数量' ); INSERT `OrderItems` VALUES (10),(100),(1000),(10001),(2),(15);

解答

考察求和函数以及取别名两个知识点，取别名需要关键字 AS，而且可要可不要。

而求和函数则是 SUM(列名) ，它会统计列中所有记录的综合。

1	SELECT SUM(quantity) items_ordered FROM OrderItems;

 

SQL25 确定已售出产品项 BR01 的总数

描述

OrderItems 表代表售出的产品，quantity 代表售出商品数量，产品项为 prod_id。

quantity	prod_id
10	AR01
100	AR10
1000	BR01
10001	BR010

问题

修改创建的语句，确定已售出产品项（prod_id）为 "BR01" 的总数。

示例结果

返回商品项已订购订单数

items_ordered
1000

示例解析

已订购商品 BR01 的数量 quantity 为 1000。

示例

1 2 3 4 5 6

DROP TABLE IF EXISTS `OrderItems`; CREATE TABLE IF NOT EXISTS `OrderItems`(     quantity INT(16) NOT NULL COMMENT '商品数量',     prod_id VARCHAR(255) NOT NULL COMMENT '商品项' ); INSERT `OrderItems` VALUES (10,'AR01'),(100,'AR10'),(1000,'BR01'),(10001,'BR010');

解答

主要考察的知识点：

• 求和函数：SUM()
• 取别名：AS
• 条件查询：WHERE

需要注意的是 SQL 语句中关键字的先后顺序，否则可能会导致语句出错。

1	SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE prod_id = 'BR01';