0
0

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

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

前言

SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。

🐴 1.检索数据

在这里插入图片描述

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

难度系数：🚩 入门

🚀 建表语句
DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)DEFAULTNULL);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 检索并列出已订购产品的清单

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'商品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      |+---------+6rowsinset(0.04 sec)

🐴🐴 答案
mysql>selectdistinct prod_id from OrderItems;

🚀 1.3 检索所有列

难度系数：🚩🚩🚩 中等

🚀 建表语句
DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)NOTNULLCOMMENT'客户id',
    cust_name VARCHAR(255)NOTNULLCOMMENT'客户姓名');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       |+---------+-----------+7rowsinset(0.00 sec)

🐴🐴 答案
mysql>select*from Customers;

🐴 2.排序检索数据

在这里插入图片描述

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

难度系数：🚩🚩🚩 中等

🚀 建表语句
DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)NOTNULLCOMMENT'客户id',
    cust_name VARCHAR(255)NOTNULLCOMMENT'客户姓名');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        |+-----------+7rowsinset(0.01 sec)

🐴🐴 答案
mysql>select cust_name from Customers
orderby cust_name desc;

🚀 2.2 对顾客ID和日期排序

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(`cust_id`varchar(255)NOTNULLCOMMENT'顾客 ID',`order_num`varchar(255)NOTNULLCOMMENT'订单号',`order_date`timestampNOTNULLCOMMENT'订单时间');INSERTINTO`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-0100:00:00|| andy    | bbbb      |2021-01-0112:00:00|| bob     | cccc      |2021-01-1012:00:00|| dick    | dddd      |2021-01-1100: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      |+---------+-----------+4rowsinset(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
orderby cust_id,order_date desc;

🚀 2.3 按照数量和价格排序

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(`quantity`INT(64)NOTNULLCOMMENT'数量',`item_price`INT(64)NOTNULLCOMMENT'订单价格');INSERTINTO`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
orderby quantity desc,item_price desc;

🐴 3.过滤数据

在这里插入图片描述

🚀 3.1 返回固定价格的产品

难度系数：🚩 入门

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_id`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品名称',`prod_price`DOUBLENOTNULLCOMMENT'产品价格');INSERTINTO`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   |+---------+-----------+1rowinset(0.01 sec)

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

🚀 3.2 返回更高价格的产品

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_id`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品名称',`prod_price`DOUBLENOTNULLCOMMENT'产品价格');INSERTINTO`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 |+---------+----------------+3rowsinset(0.01 sec)

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

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

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_id`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品名称',`prod_price`DOUBLENOTNULLCOMMENT'产品价格');INSERTINTO`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|+-----------+------------+2rowsinset(0.01 sec)

🐴🐴 答案
mysql>select prod_name,prod_price from Products 
where prod_price between3and6orderby prod_price;

🚀 3.4 返回更多的产品

难度系数：🚩 入门

🚀 建表语句
DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    quantity VARCHAR(255)NOTNULLCOMMENT'商品数量');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        |+-----------+3rowsinset(0.00 sec)

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

🐴 4.高级数据过滤

在这里插入图片描述

🚀 4.1 检索供应商名称

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`Vendors`;CREATETABLEIFNOTEXISTS`Vendors`(`vend_name`VARCHAR(255)NOTNULLCOMMENT'vend名称',`vend_country`VARCHAR(255)NOTNULLCOMMENT'vend国家',`vend_state`VARCHAR(255)NOTNULLCOMMENT'vend州');INSERTINTO`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     |+-----------+1rowinset(0.00 sec)

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

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

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'商品号',
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    quantity INT(255)NOTNULLCOMMENT'商品数量');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|+-----------+---------+----------+4rowsinset(0.00 sec)

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

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

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

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_id`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品名称',`prod_price`DOUBLENOTNULLCOMMENT'产品价格');INSERTINTO`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|+-----------+------------+2rowsinset(0.01 sec)

🐴🐴 答案
mysql>select prod_name,prod_price from Products 
where prod_price between3and6orderby prod_price;

🚀 4.4 纠错2

难度系数：🚩 入门

🚀 建表语句
DROPTABLEIFEXISTS`Vendors`;CREATETABLEIFNOTEXISTS`Vendors`(`vend_name`VARCHAR(255)NOTNULLCOMMENT'vend名称',`vend_country`VARCHAR(255)NOTNULLCOMMENT'vend国家',`vend_state`VARCHAR(255)NOTNULLCOMMENT'vend州');INSERTINTO`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 
ORDERBY vend_name 
WHERE vend_country ='USA'AND vend_state ='CA';

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

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

注解：orderby 必须位于WHERE之后

🐴 5.高级数据过滤

在这里插入图片描述

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

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_desc`VARCHAR(255)NOTNULLCOMMENT'产品名称');INSERTINTO`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  |+-----------+-----------+2rowsinset(0.00 sec)

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

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

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_desc`VARCHAR(255)NOTNULLCOMMENT'产品名称');INSERTINTO`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 |+-----------+----------------+3rowsinset(0.01 sec)

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

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

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_desc`VARCHAR(255)NOTNULLCOMMENT'产品名称');INSERTINTO`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 |+-----------+------------------+1rowinset(0.00 sec)

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

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

难度系数：🚩 入门

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_desc`VARCHAR(255)NOTNULLCOMMENT'产品名称');INSERTINTO`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 |+-----------+------------------+1rowinset(0.00 sec)

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

🐴 6.创建计算字段

在这里插入图片描述

🚀 6.1 别名

难度系数：🚩🚩🚩 中等

🚀 建表语句
DROPTABLEIFEXISTS`Vendors`;CREATETABLEIFNOTEXISTS`Vendors`(`vend_id`VARCHAR(255)NOTNULLCOMMENT'供应商id',`vend_name`VARCHAR(255)NOTNULLCOMMENT'供应商名称',`vend_address`VARCHAR(255)NOTNULLCOMMENT'供应商地址',`vend_city`VARCHAR(255)NOTNULLCOMMENT'供应商城市');INSERTINTO`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 |+---------+---------------+----------+----------+3rowsinset(0.00 sec)

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

🚀 6.2 打折

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_id`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_price`DOUBLENOTNULLCOMMENT'产品价格');INSERTINTO`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|+---------+------------+------------+3rowsinset(0.00 sec)

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

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

🐴 7.使用函数处理数据

在这里插入图片描述

🚀 7.1 顾客登录名

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)NOTNULLCOMMENT'客户id',
    cust_name VARCHAR(255)NOTNULLCOMMENT'客户姓名',
    cust_contact VARCHAR(255)NOTNULLCOMMENT'客户联系人',
    cust_city VARCHAR(255)NOTNULLCOMMENT'客户城市');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      |+---------+-----------+------------+7rowsinset(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 月的所有订单的订单号和订单日期

难度系数：🚩🚩🚩 中等

🚀 建表语句
DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
    order_num VARCHAR(255)NOTNULLCOMMENT'订单号',
    order_date TIMESTAMPNOTNULLCOMMENT'订单日期');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-0100:00:00|| a0002     |2020-01-0200:00:00|| a0003     |2020-01-0112:00:00|| a0004     |2020-02-0100:00:00|| a0005     |2020-03-0100:00:00|+-----------+---------------------+

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

🚀 示例答案
返回订单号order_num，和order_date订单时间
+-----------+---------------------+| order_num | order_date          |+-----------+---------------------+| a0001     |2020-01-0100:00:00|| a0003     |2020-01-0112:00:00|| a0002     |2020-01-0200:00:00|+-----------+---------------------+3rowsinset(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'orderby order_date;select order_num, order_date
from Orders
whereyear(order_date)=2020andmonth(order_date)=1orderby order_date;

🐴 8.汇总数据

在这里插入图片描述

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

难度系数：🚩 入门

🚀 建表语句
DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    quantity INT(16)NOTNULLCOMMENT'商品数量');INSERT`OrderItems`VALUES(10),(100),(1000),(10001),(2),(15);

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

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

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

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

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

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

难度系数：🚩🚩🚩 中等

🚀 建表语句
DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    quantity INT(16)NOTNULLCOMMENT'商品数量',
    prod_id VARCHAR(255)NOTNULLCOMMENT'商品项');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|+---------------+1rowinset(0.00 sec)

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

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

难度系数：🚩🚩🚩 中等

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_price`DOUBLENOTNULLCOMMENT'产品价格');INSERTINTO`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|+-----------+1rowinset(0.00 sec)

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

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

🐴 9.分组数据

在这里插入图片描述

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

难度系数：🚩 入门

🚀 建表语句
DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号');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|+-----------+-------------+3rowsinset(0.00 sec)

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

🐴🐴 答案
mysql>select order_num,count(*) order_lines
from OrderItems 
groupby order_num
orderby order_lines;

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

难度系数：🚩🚩🚩 中等

🚀 建表语句
DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`vend_id`VARCHAR(255)NOTNULLCOMMENT'供应商ID',`prod_price`DOUBLENOTNULLCOMMENT'产品价格');INSERTINTO`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|+---------+---------------+3rowsinset(0.00 sec)

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

🐴🐴 答案
mysql>select vend_id,min(prod_price) cheapest_item
from Products
groupby vend_id
orderby cheapest_item;

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

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    quantity INT(255)NOTNULLCOMMENT'商品数量');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        |+-----------+3rowsinset(0.00 sec)

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

🐴🐴 答案
mysql>select order_num
from OrderItems
groupby order_num
havingsum(quantity)>=100orderby order_num;

🚀 9.4 计算总和

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    item_price INT(16)NOTNULLCOMMENT'售出价格',
    quantity INT(16)NOTNULLCOMMENT'商品数量');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|+-----------+-------------+3rowsinset(0.00 sec)

🐴🐴 答案
mysql>select order_num,sum(item_price*quantity) total_price
from OrderItems
groupby order_num
havingsum(item_price*quantity)>=1000orderby order_num;

🚀 9.5 纠错3

难度系数：🚩🚩 简单

🚀 建表语句
DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号');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 
GROUPBY items 
HAVINGCOUNT(*)>=3ORDERBY items, order_num;

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

🐴🐴 答案
mysql>SELECT order_num,COUNT(*)AS items 
FROM OrderItems 
GROUPBY order_num
HAVINGCOUNT(*)>=3ORDERBY items, order_num;

🐴 10.使用子查询

在这里插入图片描述

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

难度系数：🚩🚩 简单

🚀 建表语句

DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    item_price INT(16)NOTNULLCOMMENT'售出价格');INSERT`OrderItems`VALUES('a1',10),('a2',1),('a2',1),('a4',2),('a5',5),('a2',1),('a7',7);DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    cust_id VARCHAR(255)NOTNULLCOMMENT'顾客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|+-----------+-------+1rowinset(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 的产品（一）

难度系数：🚩 入门

🚀 建表语句

DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'产品id',
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号');INSERT`OrderItems`VALUES('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    cust_id VARCHAR(255)NOTNULLCOMMENT'顾客id',
    order_date TIMESTAMPNOTNULLCOMMENT'下单时间');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-0100:00:00|| a0002     | cust1   |2022-01-0100:01:00|| a0003     | cust1   |2022-01-0200:00:00|| a0013     | cust2   |2022-01-0100: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-0100:00:00|| cust1   |2022-01-0100:01:00|+---------+---------------------+

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

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

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

难度系数：🚩🚩 简单

🚀 建表语句

DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'产品id',
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号');INSERT`OrderItems`VALUES('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    cust_id VARCHAR(255)NOTNULLCOMMENT'顾客id',
    order_date TIMESTAMPNOTNULLCOMMENT'下单时间');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');DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)NOTNULLCOMMENT'顾客id',
    cust_email VARCHAR(255)NOTNULLCOMMENT'顾客email');INSERT`Customers`VALUES('cust10','[email protected]'),('cust1','[email protected]'),('cust2','[email protected]');

🚀 描述
你想知道订购 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-0100:00:00|| a0002     | cust1   |2022-01-0100:01:00|| a0003     | cust1   |2022-01-0200:00:00|| a0013     | cust2   |2022-01-0100:20:00|+-----------+---------+---------------------+

Customers表代表顾客信息，cust_id为顾客id，cust_email为顾客email
+---------+-----------------+| cust_id | cust_email      |+---------+-----------------+| cust10  | [email protected]|| cust1   | [email protected]|| cust2   | [email protected]|+---------+-----------------+

🚀 问题
返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。
提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

🚀 示例答案
返回顾客email cust_email
+-----------------+| cust_email      |+-----------------+| [email protected]|| [email protected]|+-----------------+2rowsinset(0.00 sec)

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

🐴🐴 答案
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 返回每个顾客不同订单的总金额

难度系数：🚩🚩🚩 中等

🚀 建表语句

DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    item_price INT(16)NOTNULLCOMMENT'售出价格',
    quantity INT(16)NOTNULLCOMMENT'商品数量');INSERT`OrderItems`VALUES('a0001',10,105),('a0002',1,1100),('a0002',1,200),('a0013',2,1121),('a0003',5,10),('a0003',1,19),('a0003',7,5);DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
  order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
  cust_id VARCHAR(255)NOTNULLCOMMENT'顾客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|+---------+---------------+3rowsinset(0.03 sec)

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

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

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

难度系数：🚩🚩 简单

🚀 建表语句

DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_id`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品名称');INSERTINTO`Products`VALUES('a0001','egg'),('a0002','sockets'),('a0013','coffee'),('a0003','cola');DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'产品id',
    quantity INT(16)NOTNULLCOMMENT'商品数量');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|+-----------+----------+4rowsinset(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
    GROUPBY
        prod_id
    ) tb,
    Products p
WHERE
    tb.prod_id = p.prod_id;

🐴 11.联结表

在这里插入图片描述

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

难度系数：🚩🚩 简单

🚀 建表语句

DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
  order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
  cust_id VARCHAR(255)NOTNULLCOMMENT'顾客id');INSERT`Orders`VALUES('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)NOTNULLCOMMENT'客户id',
    cust_name VARCHAR(255)NOTNULLCOMMENT'客户姓名');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），
并按顾客名称再按订单号对结果进行升序排序。
你可以尝试用两个不同的写法，一个使用简单的等联结语法，另外一个使用 INNERJOIN。

🚀 示例答案
cust_name代表用户名称cust_name和订单号order_num。
+-----------+-----------+| cust_name | order_num |+-----------+-----------+| an        | a5        || andy      | a1        || ben       | a2        || hex       | a7        || tom       | a4        || tony      | a3        |+-----------+-----------+6rowsinset(0.00 sec)

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

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

使用内联结
mysql>select cust_name,order_num
from Customers
INNERJOIN Orders ON Orders.cust_id=Customers.cust_id
orderby cust_name,order_num;

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
  order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
  cust_id VARCHAR(255)NOTNULLCOMMENT'顾客id');INSERT`Orders`VALUES('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)NOTNULLCOMMENT'客户id',
    cust_name VARCHAR(255)NOTNULLCOMMENT'客户姓名');INSERT`Customers`VALUES('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
  order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
  quantity INT(16)NOTNULLCOMMENT'商品数量',
  item_price INT(16)NOTNULLCOMMENT'商品价格');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|+-----------+-----------+------------+6rowsinset(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 INNERJOIN Orders o ON
    c.cust_id = o.cust_id INNERJOIN OrderItems oi ON
    o.order_num = oi.order_num
GROUPBY
    cust_name,
    order_num
ORDERBY
    cust_name,
    order_num;

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'产品id',
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号');INSERT`OrderItems`VALUES('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    cust_id VARCHAR(255)NOTNULLCOMMENT'顾客id',
    order_date TIMESTAMPNOTNULLCOMMENT'下单时间');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-0100:00:00|| a0002     | cust1   |2022-01-0100:01:00|| a0003     | cust1   |2022-01-0200:00:00|| a0013     | cust2   |2022-01-0100: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-0100:00:00|| cust1   |2022-01-0100:01:00|+---------+---------------------+2rowsinset(0.00 sec)

示例解析:
产品id为BR01的订单a0001和a002的下单顾客cust10和cust1的下单时间分别为2022-01-0100:00:00和2022-01-0100: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'orderby order_date;

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

难度系数：🚩🚩 简单

🚀 建表语句

DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'产品id',
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号');INSERT`OrderItems`VALUES('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    cust_id VARCHAR(255)NOTNULLCOMMENT'顾客id',
    order_date TIMESTAMPNOTNULLCOMMENT'下单时间');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');DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)NOTNULLCOMMENT'顾客id',
    cust_email VARCHAR(255)NOTNULLCOMMENT'顾客email');INSERT`Customers`VALUES('cust10','[email protected]'),('cust1','[email protected]'),('cust2','[email protected]');

🚀 描述
有表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-0100:00:00|| a0002     | cust1   |2022-01-0100:01:00|| a0003     | cust1   |2022-01-0200:00:00|| a0013     | cust2   |2022-01-0100:20:00|+-----------+---------+---------------------+

Customers表代表顾客信息，cust_id为顾客id，cust_email为顾客email
+---------+-----------------+| cust_id | cust_email      |+---------+-----------------+| cust10  | [email protected]|| cust1   | [email protected]|| cust2   | [email protected]|+---------+-----------------+

🚀 问题
返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。
提示：涉及到 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id，
但是必须使用 INNERJOIN 语法。

🚀 示例答案
返回顾客email cust_email
+-----------------+| cust_email      |+-----------------+| [email protected]|| [email protected]|+-----------------+2rowsinset(0.00 sec)

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

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

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
    item_price INT(16)NOTNULLCOMMENT'售出价格',
    quantity INT(16)NOTNULLCOMMENT'商品数量');INSERT`OrderItems`VALUES('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)NOTNULLCOMMENT'客户id',
    cust_name VARCHAR(255)NOTNULLCOMMENT'客户姓名');INSERT`Customers`VALUES('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
  order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
  cust_id VARCHAR(255)NOTNULLCOMMENT'顾客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）。按总额对结果进行排序，请使用INNERJOIN 语法。

🚀 示例答案
+-----------+-------------+| cust_name | total_price |+-----------+-------------+| andy      |1050|| ben       |1319|| tom       |2242|+-----------+-------------+3rowsinset(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
groupby c.cust_name
having total_price >=1000orderby total_price;

🐴 12.创建高级联结

在这里插入图片描述

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)NOTNULLCOMMENT'客户id',
    cust_name VARCHAR(255)NOTNULLCOMMENT'客户姓名');INSERT`Customers`VALUES('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
  order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
  cust_id VARCHAR(255)NOTNULLCOMMENT'顾客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 |+-----------+----------+

🚀 问题
使用 INNERJOIN 编写 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        |+-----------+-----------+6rowsinset(0.00 sec)

🐴🐴 答案
mysql>SELECT
  c.cust_name cust_name,
  o.order_num order_num
FROM
  Customers c
  INNERJOIN Orders o ON c.cust_id = o.cust_id
ORDERBY
  cust_name;

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_id VARCHAR(255)NOTNULLCOMMENT'客户id',
    cust_name VARCHAR(255)NOTNULLCOMMENT'客户姓名');INSERT`Customers`VALUES('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex'),('cust40','ace');DROPTABLEIFEXISTS`Orders`;CREATETABLEIFNOTEXISTS`Orders`(
  order_num VARCHAR(255)NOTNULLCOMMENT'商品订单号',
  cust_id VARCHAR(255)NOTNULLCOMMENT'顾客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        |+-----------+-----------+7rowsinset(0.00 sec)

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

🐴🐴 答案
mysql>SELECT
  c.cust_name cust_name,
  o.order_num order_num
FROM
  Customers c
  LEFTJOIN Orders o ON c.cust_id = o.cust_id
ORDERBY
  c.cust_name;

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

难度系数：🚩🚩 简单

🚀 建表语句

DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_id`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品名称');INSERTINTO`Products`VALUES('a0001','egg'),('a0002','sockets'),('a0013','coffee'),('a0003','cola'),('a0023','soda');DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'产品id',
    order_num VARCHAR(255)NOTNULLCOMMENT'商品数量');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        |+---------+-----------+

🚀 问题
使用 OUTERJOIN 联结 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|+-----------+-----------+8rowsinset(0.00 sec)

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

🐴🐴 答案
mysql>SELECT p.prod_name, i.order_num
FROM Products p
LEFTOUTERJOIN OrderItems i USING(prod_id)ORDERBY p.prod_name

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_id`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品名称');INSERTINTO`Products`VALUES('a0001','egg'),('a0002','sockets'),('a0013','coffee'),('a0003','cola'),('a0023','soda');DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'产品id',
    order_num VARCHAR(255)NOTNULLCOMMENT'商品数量');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        |+---------+-----------+

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

🚀 示例答案
返回产品名称prod_name和订单号订单数orders
+-----------+--------+| prod_name | orders |+-----------+--------+| coffee    |1|| cola      |3|| egg       |1|| sockets   |2|| soda      |0|+-----------+--------+5rowsinset(0.00 sec)

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

🐴🐴 答案
mysql>select a1.prod_name,count(a2.order_num) prod_id
from Products as a1 
leftjoin OrderItems as a2 on a1.prod_id = a2.prod_id
groupby a1.prod_name
orderby a1.prod_name;

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

难度系数：🚩🚩 简单

🚀 建表语句

DROPTABLEIFEXISTS`Vendors`;CREATETABLEIFNOTEXISTS`Vendors`(`vend_id`VARCHAR(255)NOTNULLCOMMENT'vend名称');INSERTINTO`Vendors`VALUES('a0002'),('a0013'),('a0003'),('a0010');DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`vend_id`VARCHAR(255)NOTNULLCOMMENT'产品 ID',`prod_id`VARCHAR(255)NOTNULLCOMMENT'产品名称');INSERTINTO`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）及其可供产品的数量，包括没有产品的供应商。
你需要使用 OUTERJOIN 和 COUNT()聚合函数来计算 Products 表中每种产品的数量，最后根据vend_id 升序排序。
注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。

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

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

🐴🐴 答案
mysql>select vend_id,count(prod_id) prod_id
from Vendors
leftjoin Products using(vend_id)groupby Vendors.vend_id
orderby vend_id;

🐴 13.组合查询

在这里插入图片描述

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

难度系数：🚩🚩🚩 中等

🚀 建表语句

DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'产品id',
    quantity VARCHAR(255)NOTNULLCOMMENT'商品数量');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|+---------+----------+2rowsinset(0.01 sec)

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

🐴🐴 答案
mysql>select prod_id,quantity
from OrderItems
where quantity=100unionselect prod_id,quantity
from OrderItems
where prod_id like'BNBG%'orderby prod_id;

关键词：union

用法：

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

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

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

难度系数：🚩🚩 简单

🚀 建表语句

DROPTABLEIFEXISTS`OrderItems`;CREATETABLEIFNOTEXISTS`OrderItems`(
    prod_id VARCHAR(255)NOTNULLCOMMENT'产品id',
    quantity VARCHAR(255)NOTNULLCOMMENT'商品数量');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|+---------+----------+2rowsinset(0.00 sec)

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

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

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

难度系数：🚩🚩 简单

🚀 建表语句

DROPTABLEIFEXISTS`Products`;CREATETABLEIFNOTEXISTS`Products`(`prod_name`VARCHAR(255)NOTNULLCOMMENT'产品名称');INSERTINTO`Products`VALUES('flower'),('rice'),('ring'),('umbrella');DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_name VARCHAR(255)NOTNULLCOMMENT'客户姓名');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  |+-----------+11rowsinset(0.00 sec)

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

🐴🐴 答案
mysql>SELECT
  prod_name
FROM
  Products
UNIONALLSELECT
  cust_name prod_name
FROM
  Customers
ORDERBY
  prod_name;

🚀 13.4 纠错4

难度系数：🚩🚩 简单

🚀 建表语句

DROPTABLEIFEXISTS`Customers`;CREATETABLEIFNOTEXISTS`Customers`(
    cust_name VARCHAR(255)NOTNULLCOMMENT'顾客id',
    cust_contact VARCHAR(255)NOTNULLCOMMENT'顾客联系方式',
    cust_state VARCHAR(255)NOTNULLCOMMENT'顾客州',
    cust_email VARCHAR(255)NOTNULLCOMMENT'顾客email');INSERT`Customers`VALUES('cust10','8695192','MI','[email protected]'),('cust1','8695193','MI','[email protected]'),('cust2','8695194','IL','[email protected]');

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

🚀 问题
修正下面错误的SQLSELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state ='MI'ORDERBY cust_name;UNIONSELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state ='IL'ORDERBY cust_name;

🚀 示例答案
返回顾客名称：cust_name、顾客联系方式：cust_contact、顾客email：cust_email
+-----------+--------------+-----------------+| cust_name | cust_contact | cust_email      |+-----------+--------------+-----------------+| cust1     |8695193| [email protected]|| cust10    |8695192| [email protected]|| cust2     |8695194| [email protected]|+-----------+--------------+-----------------+3rowsinset(0.00 sec)

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

🐴🐴 答案
方法一：

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

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

在这里插入图片描述

体系化学习SQL，请关注CSDN博客
https://blog.csdn.net/weixin_41645135/category_11653817.html

在这里插入图片描述

标签： mysql sql 数据库
本文转载自: https://blog.csdn.net/weixin_41645135/article/details/125021153
版权归原作者 IT邦德 所有， 如有侵权，请联系我们删除。
登录后发布评论

“牛客题霸刷题-SQL必知必会50道”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航