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常用字符串函数

https://www.cnblogs.com/geaozhang/p/6739303.html

第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

posted @ 2022-09-11 19:01 ycylikestuty 阅读(335) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· MySQL错题集

· MySQL知识点

· 【牛客网】SQL必知必会题解

· sql查询练习题

· sql必知必会第5版挑战题

阅读排行：
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 单元测试从入门到精通
· winform 绘制太阳，地球，月球运作规律
· 上周热点回顾（3.3-3.9）

2025年3月

日

一

二

三

四

五

六

ycylikestuty

MySQL语句练习题1——来自牛客网

第1题

第2题

第3题

第4题

第5题

第6题

第7题

第8题

第9题

第10题

第11题

第12题

第13题

第14题

搜索

常用链接

我的标签

随笔分类

随笔档案

阅读排行榜

推荐排行榜

	解法一
	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')

	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

	select order_num,order_date from Orders where order_date<'2020-02-01'
	order by order_date

	select order_num, order_date
	from Orders
	where order_date like '2020-01%'
	order by order_date

	select order_num, order_date
	from Orders
	where left(order_date, 7) = '2020-01'
	order by order_date

	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

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

	select order_num, order_date
	from Orders
	where date_format(order_date, '%Y-%m')='2020-01'
	order by order_date

	#正确写法
	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

	select cust_id,order_date from Orders where order_num in(
	select order_num from OrderItems where prod_id='BR01'
	)
	order by order_date

	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

	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

	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

	#法一：内连接（无虚拟表
	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

	#右连接正确
	# 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

	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

	#法一：使用两个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