MySQL语句练习题1——来自牛客网
第1题
表OrderItems含有非空的列prod_id代表商品id，包含了所有已订购的商品（有些已被订购多次）
| prod_id |
|---|
| a1 |
| a2 |
| a3 |
| a4 |
| a5 |
| a6 |
| a7 |
编写SQL语句，检索并列出所有已订购商品（prod_id）的去重后的清单
select distinct prod_id from OrderItems
distinct：去重关键字，注意在select后面
用法：select distinct [列名1，列名2，...] from [表名]
select prod_id from OrderItems group by prod_id同样也可以达到去重目的
小数据用distinct ，海量数据用分组，前者是辅助索引，后者是主键索引
第2题
有表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 |
select cust_name from Customers order by cust_name desc
默认按照升序排序asc，所以要指定为降序排序desc
第3题
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 |
【示例解析】
返回的结果中，数量满足大于等于100，且满足prod_id是"BR01"，“BR02”，“BR03"中的任意一个
解法一
select order_num,prod_id,quantity
from OrderItems
where quantity>=100 and (prod_id='BR01' or prod_id='BR02' or prod_id='BR03')
解法二
select order_num,prod_id,quantity
from OrderItems
where quantity>=100 and prod_id in('BR01', 'BR02','BR03')
第4题
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 |
select prod_name,prod_desc from Products where prod_desc like "%toy%"
MySQL中的通配符：
%表示任何字符出现任意次数(包含零个字符)
_表示单个字符
[]表示一个字符集
第5题
给出Customers表如下：
| cust_id | cust_name | cust_contact | cust_city |
|---|---|---|---|
| a1 | Andy Li | Andy Li | Oak Park |
| a2 | Ben Liu | Ben Liu | Oak Park |
| a3 | Tony Dai | Tony Dai | Oak Park |
| a4 | Tom Chen | Tom Chen | Oak Park |
| a5 | An Li | An Li | Oak Park |
| a6 | Lee Chen | Lee Chen | Oak Park |
| a7 | Hex Liu | Hex Liu | Oak Park |
编写SQL语句，返回顾客ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。
【示例结果】
返回顾客id cust_id，顾客名称cust_name，顾客登录名user_login
| cust_id | cust_name | user_login |
|---|---|---|
| a1 | Andy Li | ANOAK |
| a2 | Ben Liu | BEOAK |
| a3 | Tony Dai | TOOAK |
| a4 | Tom Chen | TOOAK |
| a5 | An Li | ANOAK |
| a6 | Lee Chen | LEOAK |
| a7 | Hex Liu | HEOAK |
【示例解析】
例如，登录名是 ANOAK（Andy Li，居住在 Oak Park）
select cust_id,cust_name,
upper(concat(substr(cust_contact,1,2),substr(cust_city,1,3))) as user_login
#注意substr的下标是从1开始的，从0开始是错误的
from Customers
MySQL常用字符串函数
第6题
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 |
我的解法
select order_num,order_date from Orders where order_date<'2020-02-01'
order by order_date
他人的解法
1.字符串匹配（近似查找法）
1.1用like来查找
select order_num, order_date
from Orders
where order_date like '2020-01%'
order by order_date
1.2切割字符串
select order_num, order_date
from Orders
where left(order_date, 7) = '2020-01'
order by order_date
1.3字符串比较
select *
from Orders
where order_date >= '2020-01-01 00:00:00' and order_date <= '2020-01-31 23:59:59'
order by order_date
2.时间函数匹配
2.1
select order_num, order_date
from Orders
where year(order_date) = '2020' and month(order_date) = '1'
order by order_date
2.2利用date_format函数
select order_num, order_date
from Orders
where date_format(order_date, '%Y-%m')='2020-01'
order by order_date
第7题
OrderItems表代表售出的产品，quantity代表售出商品数量
| quantity |
|---|
| 10 |
| 100 |
| 1000 |
| 10001 |
| 2 |
| 15 |
编写SQL语句，确定已售出产品的总数
【示例结果】返回items_ordered列名，表示已售出商品的总数
| items_ordered |
|---|
| 11128 |
select sum(quantity) as items_ordered from OrderItems
count和sum的区别
count：统计查询结果中的行数count(*）统计所有项数，不忽略NULL
count（字段) 忽略NULL
sum：计算某一字段中所有行的数值之和（求和时不计算null）
第8题
OrderItems代表订单商品表，包括：订单号order_num和订单数量quantity
| order_num | quantity |
|---|---|
| a1 | 105 |
| a2 | 1100 |
| a2 | 200 |
| a4 | 1121 |
| a5 | 10 |
| a2 | 19 |
| a7 | 5 |
请编写SQL语句，返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序
【示例结果】返回order_num订单号
| order_num |
|---|
| a1 |
| a2 |
| a4 |
【示例解析】
订单号a1、a2、a4的quantity总和都大于等于100，按顺序为a1、a2、a4
#正确写法
select order_num from OrderItems
group by order_num having sum(quantity)>=100
order by order_num
#错误写法
#select order_num from OrderItems where sum(quantity)>=100
#group by order_num
#order by order_num
where与having的区别
where：过滤指定的行，后面不能用聚合函数sum、max等
having：过滤分组，与group by连用，后面能用聚合函数
第9题
表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表，cust_id代表顾客id，order_date代表订单日期
OrderItems表
| prod_id | order_num |
|---|---|
| BR01 | a0001 |
| BR01 | a0002 |
| BR02 | a0003 |
| BR02 | a0013 |
Orders表
| order_num | cust_id | order_date |
|---|---|---|
| a0001 | cust10 | 2022-01-01 00:00:00 |
| a0002 | cust1 | 2022-01-01 00:01:00 |
| a0003 | cust1 | 2022-01-02 00:00:00 |
| a0013 | cust2 | 2022-01-01 00:20:00 |
编写SQL语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id为"BR01"的产品，然后从Orders表中返回每个订单对应的顾客ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序
【示例结果】返回顾客id cust_id和定单日期order_date
| cust_id | order_date |
|---|---|
| cust10 | 2022-01-01 00:00:00 |
| cust1 | 2022-01-01 00:01:00 |
【示例解析】
产品id为"BR01"的订单a0001和a002的下单顾客cust10和cust1的下单时间分别为2022-01-01 00:00:00和2022-01-01 00:01:00
我的解法
select cust_id,order_date from Orders where order_num in(
select order_num from OrderItems where prod_id='BR01'
)
order by order_date
别人的解法
where
select cust_id, order_date
from Orders o, OrderItems oi
where prod_id = 'BR01' and o.order_num = oi.order_num
order by order_date
子查询
select cust_id, order_date from Orders
where order_num in (
select order_num from OrderItems
where prod_id = 'BR01'
)
order by order_date
左连接left join
select
cust_id,
order_date
from
Orders o
LEFT JOIN OrderItems oi ON o.order_num = oi.order_num
where
prod_id = 'BR01'
order by
order_date
自然连接natural join
select
cust_id,
order_date
from
Orders
NATURAL JOIN OrderItems
where prod_id = 'BR01'
order by order_date
内连接inner join 类似where
select
cust_id,
order_date
from
Orders o
inner JOIN OrderItems oi
on o.order_num = oi.order_num and prod_id = 'BR01'
#注意inner join要用on，不是where
#?也不一定，上面的左连接就用到了where
#有时间查查
order by order_date
join using 类似自然连接
select
cust_id,
order_date
from
Orders
# 相当于自然连接对相同的列进行连接
join OrderItems using(order_num)
where
prod_id = 'BR01'
order by
order_date
第10题
需要一个顾客表，其中包含他们已订购的总金额
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 |
| a0002 | cust1 |
| a0003 | cust1 |
| a0013 | cust2 |
编写SQL语句，返回顾客ID（Orders 表中的cust_id）和total_ordered以便返回每个顾客的订单总数，将结果按金额从大到小排序
提示：使用SUM()计算订单总数
【示例结果】返回顾客id cust_id和total_order下单总额
| cust_id | total_ordered |
|---|---|
| cust2 | 2242 |
| cust1 | 1404 |
| cust10 | 1050 |
【示例解析】cust1在Orders里面的订单有a0003、a0002，a0003的总额是1300,a0002的总额是104，所以最后返回cust1的支付总额是1404
select cust_id,sum(item_price*quantity) as total_ordered
from OrderItems,Orders
where OrderItems.order_num = Orders.order_num
GROUP BY cust_id order by total_ordered desc
第11题
Products表中有产品名称 prod_name、产品id prod_id
| prod_id | prod_name |
|---|---|
| a0001 | egg |
| a0002 | sockets |
| a0013 | coffee |
| a0003 | cola |
OrderItems代表订单商品表，有订单产品 prod_id、售出数量 quantity
| prod_id | quantity |
|---|---|
| a0001 | 105 |
| a0002 | 1100 |
| a0002 | 200 |
| a0013 | 1121 |
| a0003 | 10 |
| a0003 | 19 |
| a0003 | 5 |
编写SQL语句，从Products表中检索所有的产品名称（prod_name），以及名为quant_sold的计算列，其包含所售产品的总数（在OrderItems表上使用子查询和SUM(quantity)检索），返回结果无需排序
【示例结果】返回产品名称prod_name和产品售出数量总和
| prod_name | quant_sold |
|---|---|
| egg | 105 |
| sockets | 1300 |
| coffee | 1121 |
| cola | 34 |
#法一：内连接（无虚拟表
select prod_name,sum(quantity) as quant_sold
from Products,OrderItems
where Products.prod_id=OrderItems.prod_id
group by prod_name
#法二：内连接（有虚拟表
select prod_name,t_sold.sold as quant_sold
from Products,(
select prod_id,sum(quantity) as sold
from OrderItems
group by prod_id
)as t_sold #相当于产生了一个虚拟表
where t_sold.prod_id=Products.prod_id
#法三：子查询
select prod_name,(
select sum(quantity)
from OrderItems
where OrderItems.prod_id = Products.prod_id
)
from Products
第12题
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 |
使用OUTER JOIN联结Customers表和Order表，返回顾客名称cust_name和与之相关的订单号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 |
#右连接 正确
# select cust_name,order_num
# from Orders
# right join Customers using(cust_id)
# order by cust_name
#右连接 错误
#因为要找出所有的顾客，所以应该以顾客表为右连接的主表，而不能以订单表为右连接的主表
select cust_name,order_num
from Customers#左表
right join Orders using(cust_id)#右表
order by cust_name
#左连接
select cust_name,order_num
from Customers
left join Orders using(cust_id)
order by cust_name
内联结：inner join，取两列的交集，匹配不到的不保留
外联结：
left join：左连接，以左边表的列为主，取两列的交集，对于不在右边列存在的名称取null
right join：右连接，以右边表的列为主，取两列的交集，对于不在左边列存在的名称取null
第13题
Products表为产品信息表，有prod_id产品id、prod_name产品名称
| prod_id | prod_name |
|---|---|
| a0001 | egg |
| a0002 | sockets |
| a0013 | coffee |
| a0003 | cola |
| a0023 | soda |
OrderItems表为订单信息表，有order_num订单号、prod_id产品id
| prod_id | order_num |
|---|---|
| a0001 | a105 |
| a0002 | a1100 |
| a0002 | a200 |
| a0013 | a1121 |
| a0003 | a10 |
| a0003 | a19 |
| a0003 | a5 |
使用OUTER JOIN联结Products表和OrderItems表，返回产品名称（prod_name）和每一项产品的总订单数（不是订单号），并按产品名称升序排序
【示例结果】
返回产品名称prod_name和总订单数orders
| prod_name | orders |
|---|---|
| coffee | 1 |
| cola | 3 |
| egg | 1 |
| sockets | 2 |
| soda | 0 |
【示例解析】
返回产品和产品对应的总订单数，但是无实际订单的产品soda也返回
select prod_name,count(order_num) as orders
from Products
left join OrderItems using(prod_id)
group by prod_name
order by prod_name
#注意这里要分组，因为是每一个产品的总订单数，所以要group by
#如果不group by ，count(order_num)计算的将会是全部订单的值
#即每一个产品的总订单数将会相同
#注意count(列名)和count(*)的区别
#count(列名)不会累加为null的行而count(*)会
#所以使用count(order_num)时soda返回0，而如果使用count(*)返回1
第14题
表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表中检索prod_id和 quantity。其中一个SELECT语句过滤数量为100的行，另一个SELECT语句过滤id以BNBG 开头的产品，最后按产品id对结果进行升序排序
【示例结果】
返回产品id prod_id和产品数量quantity
| prod_id | quantity |
|---|---|
| a0002 | 100 |
| BNBG | 10002 |
【示例解析】
产品id a0002因为数量等于100被选取返回；BNBG因为是以BNBG开头的产品所以返回
#法一：使用两个select和union
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
#法二：使用单个select
select prod_id,quantity
from OrderItems
where quantity=100 or prod_id like 'BNBG%'
order by prod_id
join：连接表，对列操作
union：连接表，对行操作
union：将两个表做行拼接，同时自动删除重复的行
union all：将两个表做行拼接，保留重复的行
排序：放在最后，不能先排序再union 即 两个select语句只能有一个order by
