牛客题霸刷题-SQL必知必会50道

原创

jeames007 2022-05-29 23:33:29 博主文章分类：SQL专项 ©著作权

文章标签 sql 建表 mysql 文章分类 MySQL 数据库 在51CTO的第一篇博文

©著作权归作者所有：来自51CTO博客作者jeames007的原创作品，请联系作者获取转载授权，否则将追究法律责任

(文章目录)

前言

<font color=#999AAA > SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。 </font> <hr style=" border:solid; width:100px; height:1px;" color=#000000 size=1">

🐴 1.检索数据

在这里插入图片描述

🚀 1.1 从 Customers 表中检索所有的 ID

难度系数：🚩 入门

🚀 建表语句
DROP TABLE IF EXISTS `Customers`;

CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) DEFAULT NULL
);

INSERT `Customers` VALUES ('A'),('B'),('C');

🚀 描述
现有表Customers如下：
+---------+
| cust_id |
+---------+
| A       |
| B       |
| C       |
+---------+

🚀 问题
编写 SQL 语句，从 Customers 表中检索所有的cust_id

🚀 示例答案
返回cust_id列的内容
+---------+
| cust_id |
+---------+
| A       |
| B       |
| C       |
+---------+

🐴🐴 答案
mysql> select cust_id from Customers;

🚀 1.2 检索并列出已订购产品的清单

难度系数：🚩🚩 简单

🚀 建表语句
DROP TABLE IF EXISTS `OrderItems`;

CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '商品id'
);

INSERT `OrderItems` VALUES ('a1'),('a2'),('a3'),('a4'),('a5'),('a6'),('a6');

🚀 描述
表OrderItems含有非空的列prod_id代表商品id，包含了所有已订购的商品（有些已被订购多次）
+---------+
| prod_id |
+---------+
| a1      |
| a2      |
| a3      |
| a4      |
| a5      |
| a6      |
| a6      |
+---------+

🚀 问题
编写SQL 语句，检索并列出所有已订购商品（prod_id）的去重后的清单

🚀 示例答案
返回cust_id列的内容
+---------+
| prod_id |
+---------+
| a1      |
| a2      |
| a3      |
| a4      |
| a5      |
| a6      |
+---------+
6 rows in set (0.04 sec)

🐴🐴 答案
mysql> select distinct prod_id from OrderItems;

🚀 1.3 检索所有列

难度系数：🚩🚩🚩 中等

🚀 建表语句
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 ('a1','andy'),
('a2','ben'),
('a3','tony'),
('a4','tom'),
('a5','an'),
('a6','lee'),
('a7','hex');

🚀 描述
现在有Customers 表（表中含有列cust_id代表客户id，cust_name代表客户姓名）
+---------+-----------+
| cust_id | cust_name |
+---------+-----------+
| a1      | andy      |
| a2      | ben       |
| a3      | tony      |
| a4      | tom       |
| a5      | an        |
| a6      | lee       |
| a7      | hex       |
+---------+-----------+

🚀 问题
需要编写 SQL语句，检索所有列

🚀 示例答案
返回所有列cust_id和cust_name

+---------+-----------+
| cust_id | cust_name |
+---------+-----------+
| a1      | andy      |
| a2      | ben       |
| a3      | tony      |
| a4      | tom       |
| a5      | an        |
| a6      | lee       |
| a7      | hex       |
+---------+-----------+
7 rows in set (0.00 sec)


🐴🐴 答案
mysql> select * from Customers;

🐴 2.排序检索数据

在这里插入图片描述

🚀 2.1 检索顾客名称并且排序

难度系数：🚩🚩🚩 中等

🚀 建表语句
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 ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');

🚀 描述
有表Customers，cust_id代表客户id，cust_name代表客户姓名。
+---------+-----------+
| cust_id | cust_name |
+---------+-----------+
| a1      | andy      |
| a2      | ben       |
| a3      | tony      |
| a4      | tom       |
| a5      | an        |
| a6      | lee       |
| a7      | hex       |
+---------+-----------+

🚀 问题
从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果
注释：按照首字母排序

🚀 示例答案
返回客户姓名cust_name

+-----------+
| cust_name |
+-----------+
| tony      |
| tom       |
| lee       |
| hex       |
| ben       |
| andy      |
| an        |
+-----------+
7 rows in set (0.01 sec)

🐴🐴 答案
mysql> select cust_name from Customers
order by cust_name desc;

🚀 2.2 对顾客ID和日期排序

难度系数：🚩🚩 简单

🚀 建表语句
DROP TABLE IF EXISTS `Orders`;

CREATE TABLE IF NOT EXISTS `Orders` (
  `cust_id` varchar(255) NOT NULL COMMENT '顾客 ID',
  `order_num` varchar(255) NOT NULL COMMENT '订单号',
  `order_date` timestamp NOT NULL COMMENT '订单时间'
);

INSERT INTO `Orders` VALUES ('andy','aaaa','2021-01-01 00:00:00'),
('andy','bbbb','2021-01-01 12:00:00'),
('bob','cccc','2021-01-10 12:00:00'),
('dick','dddd','2021-01-11 00:00:00');

🚀 描述
有Orders表
+---------+-----------+---------------------+
| cust_id | order_num | order_date          |
+---------+-----------+---------------------+
| andy    | aaaa      | 2021-01-01 00:00:00 |
| andy    | bbbb      | 2021-01-01 12:00:00 |
| bob     | cccc      | 2021-01-10 12:00:00 |
| dick    | dddd      | 2021-01-11 00:00:00 |
+---------+-----------+---------------------+

🚀 问题
编写 SQL 语句，从 Orders 表中检索顾客 ID（cust_id）和订单号（order_num），
并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。

🚀 示例答案
返回2列，cust_id和order_num

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| andy    | bbbb      |
| andy    | aaaa      |
| bob     | cccc      |
| dick    | dddd      |
+---------+-----------+
4 rows in set (0.00 sec)

示例解析
首先根据cust_id进行排列，andy在bob和dick前，再根据order_date进行排列，
订单号bbbb的订单时间是 "2021-01-01 12:00:00"大于订单号aaaa的时间"2021-01-01 00:00:00"

🐴🐴 答案
mysql> select cust_id,order_num
from Orders
order by cust_id,order_date desc;

🚀 2.3 按照数量和价格排序

难度系数：🚩🚩 简单

🚀 建表语句
DROP TABLE IF EXISTS `OrderItems`;

CREATE TABLE IF NOT EXISTS `OrderItems` (
  `quantity` INT(64) NOT NULL COMMENT '数量',
  `item_price` INT(64) NOT NULL COMMENT '订单价格'
);

INSERT INTO `OrderItems` VALUES (1,100),
(10,1003),
(2,500);

🚀 描述
假设有一个OrderItems表
+----------+------------+
| quantity | item_price |
+----------+------------+
|        1 |        100 |
|       10 |       1003 |
|        2 |        500 |
+----------+------------+

🚀 问题
编写 SQL 语句，显示 OrderItems 表中的数量（quantity）和价格（item_price），
并按数量由多到少、价格由高到低排序。

🚀 示例答案
返回quantity和item_price
+----------+------------+
| quantity | item_price |
+----------+------------+
|       10 |       1003 |
|        2 |        500 |
|        1 |        100 |
+----------+------------+

🐴🐴 答案
mysql> select quantity,item_price
from OrderItems
order by quantity desc,item_price desc;

🐴 3.过滤数据

在这里插入图片描述

🚀 3.1 返回固定价格的产品

难度系数：🚩 入门

🚀 建表语句
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 '产品名称',
  `prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);

INSERT INTO `Products` VALUES ('a0018','sockets',9.49),
('a0019','iphone13',600),
('b0019','gucci t-shirts',1000);

🚀 描述
有表Products
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| a0018   | sockets        |       9.49 |
| a0019   | iphone13       |        600 |
| b0019   | gucci t-shirts |       1000 |
+---------+----------------+------------+

🚀 问题
从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），
只返回价格为 9.49 美元的产品。


🚀 示例答案
返回prod_id和prod_name
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
| a0018   | sockets   |
+---------+-----------+
1 row in set (0.01 sec)

🐴🐴 答案
mysql> select prod_id,prod_name from Products
where prod_price=9.49;

🚀 3.2 返回更高价格的产品

难度系数：🚩🚩 简单

🚀 建表语句
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 '产品名称',
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);

INSERT INTO `Products` VALUES ('a0011','usb',9.49),
('a0019','iphone13',600),
('b0019','gucci t-shirts',1000);

🚀 描述
有表Products
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| a0018   | sockets        |       9.49 |
| a0019   | iphone13       |        600 |
| b0019   | gucci t-shirts |       1000 |
+---------+----------------+------------+

🚀 问题
编写 SQL 语句，从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），
只返回价格为 9 美元或更高的产品。


🚀 示例答案
返回prod_id商品id和prod_name商品名称
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| a0011   | usb            |
| a0019   | iphone13       |
| b0019   | gucci t-shirts |
+---------+----------------+
3 rows in set (0.01 sec)

🐴🐴 答案
mysql> select prod_id,prod_name from Products
where prod_price >=9;

🚀 3.3 返回产品并且按照价格排序

难度系数：🚩🚩 简单

🚀 建表语句
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 '产品名称',
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);

INSERT INTO `Products` VALUES ('a0011','egg',3),
('a0019','sockets',4),
('b0019','coffee',15);

🚀 描述
有Products 表
+---------+-----------+------------+
| prod_id | prod_name | prod_price |
+---------+-----------+------------+
| a0011   | egg       |          3 |
| a0019   | sockets   |          4 |
| b0019   | coffee    |         15 |
+---------+-----------+------------+

🚀 问题
编写 SQL 语句，返回 Products 表中所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），
然后按价格对结果进行排序


🚀 示例答案
返回商品名称prod_name和商品价格prod_price
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| egg       |          3 |
| sockets   |          4 |
+-----------+------------+
2 rows in set (0.01 sec)

🐴🐴 答案
mysql> select prod_name,prod_price from Products 
where prod_price between 3 and 6
order by prod_price;

🚀 3.4 返回更多的产品

难度系数：🚩 入门

🚀 建表语句
DROP TABLE IF EXISTS `OrderItems`;

CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);

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

🚀 描述
OrderItems表含有：订单号order_num，quantity产品数量
+-----------+----------+
| order_num | quantity |
+-----------+----------+
| a1        | 105      |
| a2        | 1100     |
| a2        | 200      |
| a4        | 1121     |
| a5        | 10       |
| a2        | 19       |
| a7        | 5        |
+-----------+----------+

🚀 问题
从 OrderItems 表中检索出所有不同且不重复的订单号（order_num），
其中每个订单都要包含 100 个或更多的产品。


🚀 示例答案
返回订单号列order_num
+-----------+
| order_num |
+-----------+
| a1        |
| a2        |
| a4        |
+-----------+
3 rows in set (0.00 sec)

🐴🐴 答案
mysql> select distinct order_num from OrderItems
where quantity >=100;

🐴 4.高级数据过滤

在这里插入图片描述

🚀 4.1 检索供应商名称

难度系数：🚩🚩 简单

🚀 建表语句
DROP TABLE IF EXISTS `Vendors`;

CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
);

INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
('vivo','CNA','shenzhen'),
('huawei','CNA','xian');

🚀 描述
Vendors表有字段供应商名称（vend_name）、供应商国家（vend_country）、供应商州（vend_state）
+-----------+--------------+------------+
| vend_name | vend_country | vend_state |
+-----------+--------------+------------+
| apple     | USA          | CA         |
| vivo      | CNA          | shenzhen   |
| huawei    | CNA          | xian       |
+-----------+--------------+------------+


🚀 问题
编写 SQL 语句，从 Vendors 表中检索供应商名称（vend_name），
仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个CA）


🚀 示例答案
返回供应商名称vend_name
+-----------+
| vend_name |
+-----------+
| apple     |
+-----------+
1 row in set (0.00 sec)

🐴🐴 答案
mysql> select vend_name from Vendors
where vend_country='USA'
and vend_state='CA';

🚀 4.2 检索并列出已订购产品的清单

难度系数：🚩🚩 简单

🚀 建表语句
DROP TABLE IF EXISTS `OrderItems`;

CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '商品号',
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	quantity INT(255) NOT NULL COMMENT '商品数量'
);

INSERT `OrderItems` 
VALUES ('BR01','a1','105'),
('BR02','a2','1100'),
('BR02','a2','200'),
('BR03','a4','1121'),
('BR017','a5','10'),
('BR02','a2','19'),
('BR017','a7','5');

🚀 描述
OrderItems 表包含了所有已订购的产品（有些已被订购多次）
+---------+-----------+----------+
| prod_id | order_num | quantity |
+---------+-----------+----------+
| BR01    | a1        |      105 |
| BR02    | a2        |     1100 |
| BR02    | a2        |      200 |
| BR03    | a4        |     1121 |
| BR017   | a5        |       10 |
| BR02    | a2        |       19 |
| BR017   | a7        |        5 |
+---------+-----------+----------+


🚀 问题
编写SQL 语句，查找所有订购了数量至少100 个的 BR01、BR02 或BR03 的订单。
你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量（quantity），并按产品 ID 和数量进行过滤。


🚀 示例答案
返回商品id prod_id、订单order_num、数量quantity
+-----------+---------+----------+
| order_num | prod_id | quantity |
+-----------+---------+----------+
| a1        | BR01    |      105 |
| a2        | BR02    |     1100 |
| a2        | BR02    |      200 |
| a4        | BR03    |     1121 |
+-----------+---------+----------+
4 rows in set (0.00 sec)

示例解析
返回的结果中，数量满足大于等于100，且满足prod_id 是"BR01"，“BR02”，“BR03"中的任意一个。

🐴🐴 答案
mysql> select order_num,prod_id,quantity from OrderItems 
where quantity >=100
and prod_id in ('BR01','BR02','BR03');

🚀 4.3 返回所有价格在 3美元到 6美元之间的产品的名称和价格

难度系数：🚩🚩 简单

🚀 建表语句
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 '产品名称',
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);

INSERT INTO `Products` VALUES ('a0011','egg',3),
('a0019','sockets',4),
('b0019','coffee',15);

🚀 描述
有Products 表
+---------+-----------+------------+
| prod_id | prod_name | prod_price |
+---------+-----------+------------+
| a0011   | egg       |          3 |
| a0019   | sockets   |          4 |
| b0019   | coffee    |         15 |
+---------+-----------+------------+

🚀 问题
编写 SQL 语句，返回 Products 表中所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），
然后按价格对结果进行排序


🚀 示例答案
返回商品名称prod_name和商品价格prod_price
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| egg       |          3 |
| sockets   |          4 |
+-----------+------------+
2 rows in set (0.01 sec)

🐴🐴 答案
mysql> select prod_name,prod_price from Products 
where prod_price between 3 and 6
order by prod_price;

🚀 4.4 纠错2

难度系数：🚩 入门

🚀 建表语句
DROP TABLE IF EXISTS `Vendors`;

CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
);

INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
('vivo','CNA','shenzhen'),
('huawei','CNA','xian');

🚀 描述
Vendors表有字段供应商名称（vend_name）、供应商国家（vend_country）、供应商州（vend_state）
+-----------+--------------+------------+
| vend_name | vend_country | vend_state |
+-----------+--------------+------------+
| apple     | USA          | CA         |
| vivo      | CNA          | shenzhen   |
| huawei    | CNA          | xian       |
+-----------+--------------+------------+


🚀 问题
修改正确下面sql，使之正确返回
SELECT vend_name 
FROM Vendors 
ORDER BY vend_name 
WHERE vend_country = 'USA' AND vend_state = 'CA';

🚀 示例答案
返回供应商名称vend_name
+-----------+
| vend_name |
+-----------+
| apple     |
+-----------+
1 row in set (0.00 sec)

🐴🐴 答案
mysql> SELECT vend_name 
FROM Vendors 
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name;

注解：order by 必须位于WHERE之后

🐴 5.高级数据过滤

在这里插入图片描述

🚀 5.1 检索产品名称和描述（一）

难度系数：🚩🚩 简单

🚀 建表语句
DROP TABLE IF EXISTS `Products`;

CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);

INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy');

🚀 描述
Products表
+-----------+----------------+
| prod_name | prod_desc      |
+-----------+----------------+
| a0011     | usb            |
| a0019     | iphone13       |
| b0019     | gucci t-shirts |
| c0019     | gucci toy      |
| d0019     | lego toy       |
+-----------+----------------+

🚀 问题
编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），
仅返回描述中包含 toy 一词的产品名称


🚀 示例答案
返回产品名称和产品描述
+-----------+-----------+
| prod_name | prod_desc |
+-----------+-----------+
| c0019     | gucci toy |
| d0019     | lego toy  |
+-----------+-----------+
2 rows in set (0.00 sec)

🐴🐴 答案
mysql> select prod_name,prod_desc from Products
where prod_desc like '%toy%';

🚀 5.2 检索产品名称和描述（二）

难度系数：🚩🚩 简单

🚀 建表语句
DROP TABLE IF EXISTS `Products`;

CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);

INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy');

🚀 描述
Products表
+-----------+----------------+
| prod_name | prod_desc      |
+-----------+----------------+
| a0011     | usb            |
| a0019     | iphone13       |
| b0019     | gucci t-shirts |
| c0019     | gucci toy      |
| d0019     | lego toy       |
+-----------+----------------+

🚀 问题
编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），
仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序。


🚀 示例答案
返回产品名称和产品描述
+-----------+----------------+
| prod_name | prod_desc      |
+-----------+----------------+
| a0011     | usb            |
| a0019     | iphone13       |
| b0019     | gucci t-shirts |
+-----------+----------------+
3 rows in set (0.01 sec)

🐴🐴 答案
mysql> select prod_name,prod_desc from Products
where prod_desc not like '%toy%'
order by prod_name;

🚀 5.3 检索产品名称和描述（三）

难度系数：🚩🚩 简单

🚀 建表语句
DROP TABLE IF EXISTS `Products`;

CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);

INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego carrots toy');

🚀 描述
Products表
+-----------+------------------+
| prod_name | prod_desc        |
+-----------+------------------+
| a0011     | usb              |
| a0019     | iphone13         |
| b0019     | gucci t-shirts   |
| c0019     | gucci toy        |
| d0019     | lego carrots toy |
+-----------+------------------+

🚀 问题
编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。
有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。


🚀 示例答案
返回产品名称和产品描述
+-----------+------------------+
| prod_name | prod_desc        |
+-----------+------------------+
| d0019     | lego carrots toy |
+-----------+------------------+
1 row in set (0.00 sec)

🐴🐴 答案
mysql> select prod_name,prod_desc
from Products
where prod_desc like '%toy%' and prod_desc like '%carrots%';

🚀 5.4 检索产品名称和描述（四）

难度系数：🚩 入门

🚀 建表语句
DROP TABLE IF EXISTS `Products`;

CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);

INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy carrots ');

🚀 描述
Products表
+-----------+-------------------+
| prod_name | prod_desc         |
+-----------+-------------------+
| a0011     | usb               |
| a0019     | iphone13          |
| b0019     | gucci t-shirts    |
| c0019     | gucci toy         |
| d0019     | lego toy carrots  |
+-----------+-------------------+

🚀 问题
编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），
仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。
提示：只需要用带有三个 % 符号的 LIKE 即可。


🚀 示例答案
返回产品名称和产品描述
+-----------+------------------+
| prod_name | prod_desc        |
+-----------+------------------+
| d0019     | lego carrots toy |
+-----------+------------------+
1 row in set (0.00 sec)

🐴🐴 答案
mysql> select prod_name,prod_desc
from Products
where prod_desc like '%toy%carrots%'；

🐴 6.创建计算字段

在这里插入图片描述

🚀 6.1 别名

难度系数：🚩🚩🚩 中等

🚀 建表语句
DROP TABLE IF EXISTS `Vendors`;

CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_id` VARCHAR(255) NOT NULL COMMENT '供应商id',
  `vend_name` VARCHAR(255) NOT NULL COMMENT '供应商名称',
  `vend_address` VARCHAR(255) NOT NULL COMMENT '供应商地址',
  `vend_city` VARCHAR(255) NOT NULL COMMENT '供应商城市'
);

INSERT INTO `Vendors` VALUES ('a001','tencent cloud','address1','shenzhen'),
('a002','huawei cloud','address2','dongguan'),
('a003','aliyun cloud','address3','alibaba');

🚀 描述
别名的常见用法是在检索出的结果中重命名表的列字段（为了符合特定的报表要求或客户需求）。
有表Vendors代表供应商信息，vend_id供应商id、vend_name供应商名称、vend_address供应商地址、vend_city供应商城市。
+---------+---------------+--------------+-----------+
| vend_id | vend_name     | vend_address | vend_city |
+---------+---------------+--------------+-----------+
| a001    | tencent cloud | address1     | shenzhen  |
| a002    | huawei cloud  | address2     | dongguan  |
| a003    | aliyun cloud  | address3     | alibaba   |
+---------+---------------+--------------+-----------+

🚀 问题
编写 SQL 语句，从 Vendors 表中检索vend_id、vend_name、vend_address 和 vend_city，
将 vend_name重命名为 vname，将 vend_city 重命名为 vcity，
将 vend_address重命名为 vaddress，按供应商名称对结果进行升序排序。


🚀 示例答案
返回vend_id 供应商id、vname 供应商名称、vaddress供应商地址、vcity供应商城市。
+---------+---------------+----------+----------+
| vend_id | vname         | vaddress | vcity    |
+---------+---------------+----------+----------+
| a003    | aliyun cloud  | address3 | alibaba  |
| a002    | huawei cloud  | address2 | dongguan |
| a001    | tencent cloud | address1 | shenzhen |
+---------+---------------+----------+----------+
3 rows in set (0.00 sec)


🐴🐴 答案
mysql> select 
vend_id,
vend_name as vname,
vend_address as vaddress,
vend_city as vcity
from Vendors 
order by vend_name;

🚀 6.2 打折

难度系数：🚩🚩 简单

🚀 建表语句
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);

🚀 描述
我们的示例商店正在进行打折促销，所有产品均降价 10%。Products表包含prod_id产品id、prod_price产品价格
+---------+------------+
| prod_id | prod_price |
+---------+------------+
| a0011   |       9.49 |
| a0019   |        600 |
| b0019   |       1000 |
+---------+------------+

🚀 问题
编写 SQL语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。
sale_price 是一个包含促销价格的计算字段。


🚀 示例答案
返回产品id prod_id、产品价格prod_price、销售价格 sale_price
+---------+------------+------------+
| prod_id | prod_price | sale_price |
+---------+------------+------------+
| a0011   |       9.49 |      8.541 |
| a0019   |        600 |        540 |
| b0019   |       1000 |        900 |
+---------+------------+------------+
3 rows in set (0.00 sec)

提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）

🐴🐴 答案
mysql> select prod_id,
prod_price,
prod_price*0.9 sale_price
from Products;

🐴 7.使用函数处理数据

在这里插入图片描述

🚀 7.1 顾客登录名

难度系数：🚩🚩 简单

🚀 建表语句
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');

🚀 描述
我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。
给出 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      |
+---------+-----------+------------+
7 rows in set (0.01 sec)

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

🐴🐴 答案
mysql> select cust_id,
cust_name,
upper(concat(left(cust_name,2),left(cust_city,3))) as user_login
from Customers;

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

难度系数：🚩🚩🚩 中等

🚀 建表语句
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');

🚀 描述
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 |
+-----------+---------------------+
3 rows in set (0.00 sec)

示例解析
a0001、a0002、a0003 时间属于2020年1月

🐴🐴 答案
mysql> select order_num, order_date 
from Orders
where date_format(order_date, '%Y-%m')='2020-01'
order by order_date;

select order_num, order_date
from Orders
where year(order_date) = 2020 and month(order_date) = 1
order by order_date;

🐴 8.汇总数据

在这里插入图片描述

🚀 8.1 确定已售出产品的总数

难度系数：🚩 入门

🚀 建表语句
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);

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

+----------+
| quantity |
+----------+
|       10 |
|      100 |
|     1000 |
|    10001 |
|        2 |
|       15 |
+----------+

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


🚀 示例答案
返回items_ordered列名，表示已售出商品的总数。
+---------------+
| items_ordered |
+---------------+
|         11128 |
+---------------+
1 row in set (0.00 sec)


🐴🐴 答案
mysql> select sum(quantity) items_ordered from OrderItems;

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

难度系数：🚩🚩🚩 中等

🚀 建表语句
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');

🚀 描述
OrderItems表代表售出的产品，quantity代表售出商品数量，产品项为prod_id。
+----------+---------+
| quantity | prod_id |
+----------+---------+
|       10 | AR01    |
|      100 | AR10    |
|     1000 | BR01    |
|    10001 | BR010   |
+----------+---------+

🚀 问题
确定已售出产品项（prod_id）为"BR01"的总数。

🚀 示例答案
返回商品项已订购订单数
+---------------+
| items_ordered |
+---------------+
|          1000 |
+---------------+
1 row in set (0.00 sec)


🐴🐴 答案
mysql> select sum(quantity) items_ordered from OrderItems
where prod_id='BR01';

🚀 8.3 确定 Products 表中价格不超过 10 美元的最贵产品的价格

难度系数：🚩🚩🚩 中等

🚀 建表语句
DROP TABLE IF EXISTS `Products`;

CREATE TABLE IF NOT EXISTS `Products` (
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);

INSERT INTO `Products` VALUES (9.49),
(600),
(1000);

🚀 描述
Products 表
+------------+
| prod_price |
+------------+
|       9.49 |
|        600 |
|       1000 |
+------------+

🚀 问题
编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。
将计算所得的字段命名为 max_price。

🚀 示例答案
返回max_price
+-----------+
| max_price |
+-----------+
|      9.49 |
+-----------+
1 row in set (0.00 sec)

示例解析：
返回十元以下最高价格max_price。

🐴🐴 答案
mysql> select max(prod_price) as max_price
from Products
where prod_price<=10;

🐴 9.分组数据

在这里插入图片描述

🚀 9.1 返回每个订单号各有多少行数

难度系数：🚩 入门

🚀 建表语句
DROP TABLE IF EXISTS `OrderItems`;

CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
);

INSERT `OrderItems` VALUES ('a002'),('a002'),('a002'),('a004'),('a007');

🚀 描述
OrderItems 表包含每个订单的每个产品
+-----------+
| order_num |
+-----------+
| a002      |
| a002      |
| a002      |
| a004      |
| a007      |
+-----------+

🚀 问题
编写 SQL 语句，返回每个订单号（order_num）各有多少行数（order_lines），
并按 order_lines对结果进行升序排序。


🚀 示例答案
返回订单号order_num和对应订单号的行数order_lines
+-----------+-------------+
| order_num | order_lines |
+-----------+-------------+
| a004      |           1 |
| a007      |           1 |
| a002      |           3 |
+-----------+-------------+
3 rows in set (0.00 sec)

示例解析
订单号a002有3行订单记录也是最多的订单号故排在最后一位返回，相同订单行数的订单无需过多处理。


🐴🐴 答案
mysql> select order_num,count(*) order_lines
from OrderItems 
group by order_num
order by order_lines;

🚀 9.2 每个供应商成本最低的产品

难度系数：🚩🚩🚩 中等

🚀 建表语句
DROP TABLE IF EXISTS `Products`;

CREATE TABLE IF NOT EXISTS `Products` (
`vend_id` VARCHAR(255) NOT NULL COMMENT '供应商ID',
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);

INSERT INTO `Products` VALUES ('a0011',100),
('a0019',0.1),
('b0019',1000),
('b0019',6980),
('b0019',20);

🚀 描述
有Products表，含有字段prod_price代表产品价格，vend_id代表供应商id
+---------+------------+
| vend_id | prod_price |
+---------+------------+
| a0011   |        100 |
| a0019   |        0.1 |
| b0019   |       1000 |
| b0019   |       6980 |
| b0019   |         20 |
+---------+------------+

🚀 问题
编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），
然后从最低成本到最高成本对结果进行升序排序。


🚀 示例答案
返回供应商id vend_id和对应供应商成本最低的产品cheapest_item。
+---------+---------------+
| vend_id | cheapest_item |
+---------+---------------+
| a0019   |           0.1 |
| b0019   |            20 |
| a0011   |           100 |
+---------+---------------+
3 rows in set (0.00 sec)

示例解析
例如b0019成本最低的价格是20，且最后根据成本价格排序返回依次是a0019、b0019、a0011


🐴🐴 答案
mysql> select vend_id,min(prod_price) cheapest_item
from Products
group by vend_id
order by cheapest_item;

🚀 9.3 返回订单数量总和不小于100的所有订单的订单号

难度系数：🚩🚩 简单

🚀 建表语句
DROP TABLE IF EXISTS `OrderItems`;

CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	quantity INT(255) NOT NULL COMMENT '商品数量'
);

INSERT `OrderItems` VALUES ('a1',105),('a2',200),('a4',1121),('a5',10),('a7',5);

🚀 描述
OrderItems代表订单商品表，包括：订单号order_num和订单数量quantity。
+-----------+----------+
| order_num | quantity |
+-----------+----------+
| a1        |      105 |
| a2        |      200 |
| a4        |     1121 |
| a5        |       10 |
| a7        |        5 |
+-----------+----------+

🚀 问题
请编写 SQL 语句，返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序。


🚀 示例答案
返回order_num订单号。
+-----------+
| order_num |
+-----------+
| a1        |
| a2        |
| a4        |
+-----------+
3 rows in set (0.00 sec)

示例解析
订单号a1、a2、a4的quantity总和都大于等于100，按顺序为a1、a2、a4。


🐴🐴 答案
mysql> select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num;

🚀 9.4 计算总和

难度系数：🚩🚩 简单

🚀 建表语句
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);

🚀 描述
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 |
+-----------+------------+----------+

🚀 问题
编写 SQL 语句，根据订单号聚合，返回订单总价不小于1000 的所有订单号，最后的结果按订单号进行升序排序。
提示：总价 = item_price 乘以 quantity


🚀 示例答案
返回order_num和total_price
+-----------+-------------+
| order_num | total_price |
+-----------+-------------+
| a1        |        1050 |
| a2        |        1319 |
| a4        |        2242 |
+-----------+-------------+
3 rows in set (0.00 sec)


🐴🐴 答案
mysql> select order_num,
sum(item_price*quantity) total_price
from OrderItems
group by order_num
having sum(item_price*quantity)>=1000
order by order_num;

🚀 9.5 纠错3

难度系数：🚩🚩 简单

🚀 建表语句
DROP TABLE IF EXISTS `OrderItems`;

CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
);

INSERT `OrderItems` VALUES ('a002'),('a002'),('a002'),('a004'),('a007');

🚀 描述
OrderItems表含有order_num订单号
+-----------+
| order_num |
+-----------+
| a002      |
| a002      |
| a002      |
| a004      |
| a007      |
+-----------+

🚀 问题
将下面代码修改正确后执行
SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY items 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;


🚀 示例答案
返回订单号order_num和出现的次数items
+-----------+-------+
| order_num | items |
+-----------+-------+
| a002      |     3 |
+-----------+-------+
1 row in set (0.00 sec)


🐴🐴 答案
mysql> SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

🐴 10.使用子查询

在这里插入图片描述

🚀 10.1 返回购买价格为 10 美元或以上产品的顾客列表

难度系数：🚩🚩 简单

🚀 建表语句

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 '售出价格'
  );
  
  INSERT `OrderItems` VALUES ('a1',10),('a2',1),('a2',1),('a4',2),('a5',5),('a2',1),('a7',7);

  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'),
  ('a2','cust1'),('a4','cust2'),('a5','cust5'),('a2','cust1'),('a7','cust7');

🚀 描述
OrderItems表示订单商品表，含有字段订单号：order_num、订单价格：item_price；
+-----------+------------+
| order_num | item_price |
+-----------+------------+
| a1        |         10 |
| a2        |          1 |
| a2        |          1 |
| a4        |          2 |
| a5        |          5 |
| a2        |          1 |
| a7        |          7 |
+-----------+------------+
Orders表代表订单信息表，含有顾客id：cust_id和订单号：order_num
+-----------+---------+
| order_num | cust_id |
+-----------+---------+
| a1        | cust10  |
| a2        | cust1   |
| a2        | cust1   |
| a4        | cust2   |
| a5        | cust5   |
| a2        | cust1   |
| a7        | cust7   |
+-----------+---------+


🚀 问题
使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。
注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）。

🚀 示例答案
返回顾客id cust_id
+-----------+-------+
| order_num | items |
+-----------+-------+
| a002      |     3 |
+-----------+-------+
1 row in set (0.00 sec)

示例解析:
cust10顾客下单的订单为a1，a1的售出价格大于等于10

🐴🐴 答案
mysql> select cust_id from Orders
where order_num in (
select order_num from OrderItems
where item_price >=10
);

🚀 10.2 确定哪些订单购买了 prod_id 为 BR01 的产品（一）

难度系数：🚩 入门

🚀 建表语句

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

🚀 描述
表OrderItems代表订单商品信息表，prod_id为产品id；
+---------+-----------+
| prod_id | order_num |
+---------+-----------+
| BR01    | a0001     |
| BR01    | a0002     |
| BR02    | a0003     |
| BR02    | a0013     |
+---------+-----------+

Orders表代表订单表有cust_id代表顾客id和订单日期order_date
+-----------+---------+---------------------+
| 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

🐴🐴 答案
mysql> select cust_id,order_date from  Orders
where order_num in 
(
select order_num from OrderItems
where prod_id='BR01'
) order by order_date;

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

难度系数：🚩🚩 简单

🚀 建表语句

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

🚀 描述
你想知道订购 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。

🚀 示例答案
返回顾客email cust_email
+-----------------+
| cust_email      |
+-----------------+
| cust10@cust.com |
| cust1@cust.com  |
+-----------------+
2 rows in set (0.00 sec)

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

🐴🐴 答案
mysql> select cust_email from Customers
where cust_id in (
select cust_id from  Orders
where order_num in 
 (
select order_num from OrderItems
where prod_id='BR01'
 ) 
);

🚀 10.4 返回每个顾客不同订单的总金额

难度系数：🚩🚩🚩 中等

🚀 建表语句

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 ('a0001',10,105),('a0002',1,1100),('a0002',1,200),('a0013',2,1121),('a0003',5,10),('a0003',1,19),('a0003',7,5);

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 ('a0001','cust10'),('a0003','cust1'),('a0013','cust2');

🚀 描述
我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。
OrderItems表代表订单信息，OrderItems表有订单号：order_num和商品售出价格：item_price、商品数量：quantity。
+-----------+------------+----------+
| order_num | item_price | quantity |
+-----------+------------+----------+
| a0001     |         10 |      105 |
| a0002     |          1 |     1100 |
| a0002     |          1 |      200 |
| a0013     |          2 |     1121 |
| a0003     |          5 |       10 |
| a0003     |          1 |       19 |
| a0003     |          7 |        5 |
+-----------+------------+----------+

Orders表订单号：order_num、顾客id：cust_id
+-----------+---------+
| order_num | cust_id |
+-----------+---------+
| a0001     | cust10  |
| a0003     | cust1   |
| a0013     | cust2   |
+-----------+---------+


🚀 问题
编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），
并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。
提示：你之前已经使用 SUM()计算订单总数。

🚀 示例答案
返回顾客id cust_id和total_order下单总额
+---------+---------------+
| cust_id | total_ordered |
+---------+---------------+
| cust2   |          2242 |
| cust10  |          1050 |
| cust1   |           104 |
+---------+---------------+
3 rows in set (0.03 sec)

示例解析:
cust2在Orders里面的订单a0013，a0013的售出价格是2售出数量是1121，总额是2242，最后返回cust2的支付总额是2242。

🐴🐴 答案
mysql> select 
cust_id,
(select
 SUM(item_price*quantity)
FROM OrderItems a 
WHERE a.order_num=b.order_num) total_ordered
from Orders b
ORDER BY total_ordered DESC;

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

难度系数：🚩🚩 简单

🚀 建表语句

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

🚀 描述
 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 | quantity |
+-----------+----------+
| egg       |      105 |
| sockets   |     1300 |
| coffee    |     1121 |
| cola      |       34 |
+-----------+----------+
4 rows in set (0.01 sec)

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

🐴🐴 答案
mysql> SELECT 
    p.prod_name,
    tb.quantity
FROM (
    SELECT
        prod_id,
        SUM(quantity) quantity
    FROM
        OrderItems
    GROUP BY
        prod_id
    ) tb,
    Products p
WHERE
    tb.prod_id = p.prod_id;

🐴 11.联结表

在这里插入图片描述

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

难度系数：🚩🚩 简单

🚀 建表语句

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

🚀 描述
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        |
+-----------+-----------+
6 rows in set (0.00 sec)

示例解析:
顾客名称为an的cust_id为cust221，他的订单号为a5。

🐴🐴 答案
等联结语法:
mysql> select cust_name,order_num
from Customers,Orders
where Customers.cust_id=Orders.cust_id
order by cust_name,order_num;

使用内联结
mysql> select cust_name,order_num
from Customers
INNER JOIN Orders ON Orders.cust_id=Customers.cust_id
order by cust_name,order_num;

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

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

🚀 描述
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 |
+-----------+-----------+------------+
6 rows in set (0.00 sec)

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

🐴🐴 答案
mysql> SELECT 
    c.cust_name cust_name,
    o.order_num order_num,
    SUM(oi.quantity * oi.item_price) OrderTotal
FROM Customers c INNER JOIN Orders o ON
    c.cust_id = o.cust_id INNER JOIN OrderItems oi ON
    o.order_num = oi.order_num
GROUP BY
    cust_name,
    order_num
ORDER BY
    cust_name,
    order_num;

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

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

🚀 描述
表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 |
+---------+---------------------+
2 rows in set (0.00 sec)

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

🐴🐴 答案
mysql> select o.cust_id as cust_id, o.order_date as order_date
from OrderItems oi join Orders o on oi.order_num=o.order_num
where oi.prod_id='BR01'
order by order_date;

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

难度系数：🚩🚩 简单

🚀 建表语句

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代表订单商品信息表，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  |
+-----------------+
2 rows in set (0.00 sec)

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

🐴🐴 答案
mysql> SELECT 
    c.cust_email cust_email
FROM
    OrderItems oi INNER JOIN Orders o ON
    (oi.prod_id = 'BR01') AND
    (oi.order_num = o.order_num) INNER JOIN Customers c ON
    o.cust_id = c.cust_id;

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

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

🚀 描述
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 |
+-----------+-------------+
3 rows in set (0.00 sec)

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

🐴🐴 答案
mysql> select c.cust_name, sum(oi.item_price * oi.quantity) as total_price
from Orders o join Customers c on o.cust_id=c.cust_id
join OrderItems oi on o.order_num=oi.order_num
group by c.cust_name
having total_price >= 1000
order by total_price;

🐴 12.创建高级联结

在这里插入图片描述

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

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

🚀 描述
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        |
+-----------+-----------+
6 rows in set (0.00 sec)


🐴🐴 答案
mysql> SELECT
  c.cust_name cust_name,
  o.order_num order_num
FROM
  Customers c
  INNER JOIN Orders o ON c.cust_id = o.cust_id
ORDER BY
  cust_name;

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

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

🚀 描述
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        |
+-----------+-----------+
7 rows in set (0.00 sec)

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

🐴🐴 答案
mysql> SELECT
  c.cust_name cust_name,
  o.order_num order_num
FROM
  Customers c
  LEFT JOIN Orders o ON c.cust_id = o.cust_id
ORDER BY
  c.cust_name;

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

难度系数：🚩🚩 简单

🚀 建表语句

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

🚀 描述
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      |
+-----------+-----------+
8 rows in set (0.00 sec)

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


🐴🐴 答案
mysql> SELECT p.prod_name, i.order_num
FROM Products p
LEFT OUTER JOIN OrderItems i USING(prod_id)
ORDER BY p.prod_name

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

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

🚀 描述
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 |
+-----------+--------+
5 rows in set (0.00 sec)

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


🐴🐴 答案
mysql> select a1.prod_name,count(a2.order_num) prod_id
from Products as a1 
left join OrderItems as a2 on a1.prod_id = a2.prod_id
group by a1.prod_name
order by a1.prod_name;

🚀 12.5 列出供应商及其可供产品的数量

难度系数：🚩🚩 简单

🚀 建表语句

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_id` VARCHAR(255) NOT NULL COMMENT 'vend名称'
);
INSERT INTO `Vendors` VALUES ('a0002'),
('a0013'),
('a0003'),
('a0010');

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`vend_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','prod_id_iphone'),
('a00113','prod_id_tea'),
('a0003','prod_id_vivo phone'),
('a0010','prod_id_huawei phone');

🚀 描述
有Vendors表含有vend_id供应商id.
+---------+
| vend_id |
+---------+
| a0002   |
| a0013   |
| a0003   |
| a0010   |
+---------+

有Products表含有供应商id和供应产品id
+---------+----------------------+
| vend_id | prod_id              |
+---------+----------------------+
| a0001   | egg                  |
| a0002   | prod_id_iphone       |
| a00113  | prod_id_tea          |
| a0003   | prod_id_vivo phone   |
| a0010   | prod_id_huawei phone |
+---------+----------------------+

🚀 问题
列出供应商（Vendors 表中的 vend_id）及其可供产品的数量，包括没有产品的供应商。
你需要使用 OUTER JOIN 和 COUNT()聚合函数来计算 Products 表中每种产品的数量，最后根据vend_id 升序排序。
注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。

🚀 示例答案
返回供应商id和对应供应商供应的产品的个数
+---------+---------+
| vend_id | prod_id |
+---------+---------+
| a0002   |       1 |
| a0003   |       1 |
| a0010   |       1 |
| a0013   |       0 |
+---------+---------+
4 rows in set (0.00 sec)

示例解析:
供应商a00013供应的商品不在Products表中所以为0，其他供应商供应的产品为1个。


🐴🐴 答案
mysql> select vend_id,count(prod_id) prod_id
from Vendors
left join Products using(vend_id)
group by Vendors.vend_id
order by vend_id;

🐴 13.组合查询

在这里插入图片描述

🚀 13.1 将两个 SELECT 语句结合起来（一）

难度系数：🚩🚩🚩 中等

🚀 建表语句

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',100),('a0002',200),
('a0013',1121),('a0003',10),('a0003',19),('a0003',5),('BNBG',10002);

🚀 描述
表OrderItems包含订单产品信息，字段prod_id代表产品id、quantity代表产品数量
+---------+----------+
| prod_id | quantity |
+---------+----------+
| a0001   | 105      |
| a0002   | 100      |
| a0002   | 200      |
| a0013   | 1121     |
| a0003   | 10       |
| a0003   | 19       |
| a0003   | 5        |
| BNBG    | 10002    |
+---------+----------+

🚀 问题
 将两个 SELECT 语句结合起来，以便从 OrderItems表中检索产品 id（prod_id）和 quantity。
 其中，一个 SELECT 语句过滤数量为 100 的行，
 另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。

🚀 示例答案
返回产品id prod_id和产品数量quantity
+---------+----------+
| prod_id | quantity |
+---------+----------+
| a0002   | 100      |
| BNBG    | 10002    |
+---------+----------+
2 rows in set (0.01 sec)

示例解析:
产品id a0002因为数量等于100被选取返回；BNBG因为是以 BNBG 开头的产品所以返回；最后以产品id进行排序返回。

🐴🐴 答案
mysql> select prod_id,quantity
from OrderItems
where quantity=100
union 
select prod_id,quantity
from OrderItems
where prod_id like 'BNBG%'
order by prod_id;

关键词：union

用法：

join---连接表，对列操作
union--连接表，对行操作。
union--将两个表做行拼接，同时自动删除重复的行。
union all---将两个表做行拼接，保留重复的行。
思路：

筛选条件：like用法。where quantity=100，where prod_id like 'BNBG%'
排序：放在最后进行排序，不能先排序在拼接。order by prod_id

🚀 13.2 将两个 SELECT 语句结合起来（二）

难度系数：🚩🚩 简单

🚀 建表语句

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',100),('a0002',200),
('a0013',1121),('a0003',10),('a0003',19),('a0003',5),('BNBG',10002);

🚀 描述
表OrderItems包含订单产品信息，字段prod_id代表产品id、quantity代表产品数量
+---------+----------+
| prod_id | quantity |
+---------+----------+
| a0001   | 105      |
| a0002   | 100      |
| a0002   | 200      |
| a0013   | 1121     |
| a0003   | 10       |
| a0003   | 19       |
| a0003   | 5        |
| BNBG    | 10002    |
+---------+----------+

🚀 问题
 将两个 SELECT 语句结合起来，以便从 OrderItems表中检索产品 id（prod_id）和 quantity。
 其中，一个 SELECT 语句过滤数量为 100 的行，
 另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。
 注意：这次仅使用单个 SELECT 语句。

🚀 示例答案
返回产品id prod_id和产品数量quantity
+---------+----------+
| prod_id | quantity |
+---------+----------+
| a0002   | 100      |
| BNBG    | 10002    |
+---------+----------+
2 rows in set (0.00 sec)

示例解析:
产品id a0002因为数量等于100被选取返回；
BNBG因为是以 BNBG 开头的产品所以返回；最后以产品id进行排序返回。

🐴🐴 答案
mysql> select
    prod_id,
    quantity
from
    OrderItems
where
    quantity = 100 or prod_id like "BNBG%"
order by
    prod_id;

🚀 13.3 组合 Products 表中的产品名称和 Customers 表中的顾客名称

难度系数：🚩🚩 简单

🚀 建表语句

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('flower'),
('rice'),
('ring'),
('umbrella');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('andy'),('ben'),('tony'),('tom'),('an'),('lee'),('hex');

🚀 描述
Products表含有字段prod_name代表产品名称
+-----------+
| prod_name |
+-----------+
| flower    |
| rice      |
| ring      |
| umbrella  |
+-----------+

Customers表代表顾客信息，cust_name代表顾客名称
+-----------+
| cust_name |
+-----------+
| andy      |
| ben       |
| tony      |
| tom       |
| an        |
| lee       |
| hex       |
+-----------+

🚀 问题
编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，
然后按产品名称对结果进行升序排序。

🚀 示例答案
+-----------+
| prod_name |
+-----------+
| an        |
| andy      |
| ben       |
| flower    |
| hex       |
| lee       |
| rice      |
| ring      |
| tom       |
| tony      |
| umbrella  |
+-----------+
11 rows in set (0.00 sec)

示例解析:
拼接cust_name和prod_name并根据结果升序排序


🐴🐴 答案
mysql> SELECT
  prod_name
FROM
  Products
UNION ALL
SELECT
  cust_name prod_name
FROM
  Customers
ORDER BY
  prod_name;

🚀 13.4 纠错4

难度系数：🚩🚩 简单

🚀 建表语句

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_name VARCHAR(255) NOT NULL COMMENT '顾客id',
    cust_contact VARCHAR(255) NOT NULL COMMENT '顾客联系方式',
    cust_state VARCHAR(255) NOT NULL COMMENT '顾客州',
    cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'
  );
INSERT `Customers` VALUES ('cust10','8695192','MI','cust10@cust.com'),('cust1','8695193','MI','cust1@cust.com'),('cust2','8695194','IL','cust2@cust.com');

🚀 描述
表Customers含有字段cust_name顾客名、cust_contact顾客联系方式、cust_state顾客州、cust_email顾客email
+-----------+--------------+------------+-----------------+
| cust_name | cust_contact | cust_state | cust_email      |
+-----------+--------------+------------+-----------------+
| cust10    | 8695192      | MI         | cust10@cust.com |
| cust1     | 8695193      | MI         | cust1@cust.com  |
| cust2     | 8695194      | IL         | cust2@cust.com  |
+-----------+--------------+------------+-----------------+

🚀 问题
修正下面错误的SQL
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
ORDER BY cust_name; 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'ORDER BY cust_name;


🚀 示例答案
返回顾客名称：cust_name、顾客联系方式：cust_contact、顾客email：cust_email
+-----------+--------------+-----------------+
| cust_name | cust_contact | cust_email      |
+-----------+--------------+-----------------+
| cust1     | 8695193      | cust1@cust.com  |
| cust10    | 8695192      | cust10@cust.com |
| cust2     | 8695194      | cust2@cust.com  |
+-----------+--------------+-----------------+
3 rows in set (0.00 sec)

示例解析:
返回住在"IL"和"MI"的顾客信息，最后根据顾客名称升序排序。


🐴🐴 答案
方法一：

使用union子句进行双重查询，再将两次查询的结果结合在一起，但是由于使用了union子句，
所以order by 子句只能使用一次且只能在最后一个 select 中使用；具体如下：
select cust_name,cust_contact,cust_email
from Customers
where cust_state = 'MI'
union
select cust_name,cust_contact,cust_email
from Customers
where cust_state = 'IL'
order by cust_name;

方法二： 使用 or 进行多条件查询，具体如下：
select cust_name,cust_contact,cust_email
from Customers
where cust_state = 'MI'
or cust_state = 'IL'
order by cust_name;

在这里插入图片描述