SQL练习【个人练习】

前几天做笔试的时候狠狠拷打了SQL，并不是很难的题目，现在想起来感觉当时真的呆， 写完就知道笔试没希望了😭😭🤡，平时被MP惯坏了， 很少写SQL， 导致语法都生疏了， 所以花一天时间把100多道SQL刷完了， 下面是一些我觉得还不错的题(我不会的🤪)。
题目来源是牛客网 SQL必知必会，牛客上的题出的不错但是重复题型很多， 其实一类题刷一道后面就知道怎么写了

常用函数的使用

concat&substring

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

给出 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）

• SQL中的substring都是闭区间, 且下标从1开始

select cust_id, cust_name, upper(
    concat(
        substring(cust_name, 1, 2), substring(cust_city,1,3)
        )) as user_login
from Customers;

like&left切割&正则比较&时间函数(date_format/year/month)

Orders订单表

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

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

【示例结果】

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

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

【示例解析】

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

用like来查找

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;

用正则来查找（效率不如like，能用like就用like）

select order_num, order_date``from Orders``where order_date regexp ``'2020-01'``order by order_date

时间函数匹配

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

利用date_format函数 （参考其中的匹配规则进行匹配）

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

group by

group by经常和一些聚合函数一起使用的, 比如MAX, MIN, Count这些

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

【示例解析】

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

select order_num, count(order_num) as order_lines
from OrderItems
group by order_num
order by order_lines

这道题容易出错的的点:

• 注意要写出group by order_num, 聚合函数配套group by不然没办法做出分组效果
• 

SQL关键字顺序总结:

(1) FROM: 对FROM子句中的左表<left_table>和右表<right_table>执行笛卡儿积，产生虚拟表VT1;
(2) ON: 对虚拟表VT1进行ON筛选，只有那些符合<join_condition>的行才被插入，产生虚拟表VT2;
(3) JOIN: 如果指定了OUTER JOIN(如LEFT OUTER JOIN、RIGHT OUTER JOIN)，那么保留表中未匹配的行作为外部行添加到虚拟表VT2，产生虚拟表VT3。如果FROM子句包含两个以上的表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1~步骤3，直到处理完所有的表;
(4) WHERE: 对虚拟表VT3应用WHERE过滤条件，只有符合<where_condition>的记录才会被插入到VT4;
(5) GROUP By: 根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5;
(6) CUBE|ROllUP: 对VT5进行CUBE或ROLLUP操作，产生VT6;
(7) HAVING: 对虚拟表VT6应用HAVING过滤器，只有符合<having_condition>的记录才会被插入到VT7;
(8) SELECT: 第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中;
(9) DISTINCT: 去除重复，得到虚拟表VT9;
(10) ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，得到虚拟表VT10;
(11) LIMIT: 取出指定行的记录，产生虚拟表VT11，并返回给查询用户

有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

【示例解析】

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

select vend_id, min(prod_price) as cheapest_item
from Products
group by vend_id
order by cheapest_item;

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 distinct(order_num)
from OrderItems
where quantity > 100
order by order_num

• where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。
• having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

# 直接使用聚合
select order_num
from OrderItems
group by order_num
having  sum(quantity) >= 100
order by order_num

子查询

什么叫做子查询

• 子查询是在主查询前面就得到结果的查询, 一般子查询的结果被主查询使用, 而且一般用括号括起来~

OrderItems表示订单商品表，含有字段订单号：order_num、订单价格：item_price；Orders表代表订单信息表，含有顾客id：cust_id和订单号：order_num

OrderItems表

order_num	item_price
a1	10
a2	1
a2	1
a4	2
a5	5
a2	1
a7	7

Orders表

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

cust_id
cust10

【示例解析】

cust10顾客下单的订单为a1，a1的售出价格大于等于10

# 子查询
select cust_id from Orders
where order_num IN (
    select order_num
    from OrderItems
    where item_price>=10
)
#关联查询
select o.cust_id
from Orders as o
inner join OrderItems as ot
on o.order_num=ot.order_num and ot.item_price >=10

表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')
外连接
select cust_id,order_date
from Orders
left join OrderItems on OrderItems.order_num = Orders.order_num
where OrderItems.prod_id='BR01'

OrderItems表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱，OrderItems表有订单号order_num和item_price商品售出价格、quantity商品数量

order_num	item_price	quantity
a1	10	105
a2	1	1100
a2	1	200
a4	2	1121
a5	5	10
a2	1	19
a7	7	5

Orders表含有字段order_num 订单号、cust_id顾客id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

顾客表Customers有字段cust_id 客户id、cust_name 客户姓名

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

【问题】编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）。

提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。

【示例结果】

cust_name	total_price
andy	1050
ben	1319
tom	2242

【示例解析】

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

SELECT c.cust_name,tb.total_price
FROM
    Customers c
    INNER JOIN Orders o
    ON c.cust_id = o.cust_id
    INNER JOIN(
        SELECT order_num,SUM(item_price*quantity) AS total_price
        FROM OrderItems
        GROUP BY order_num
        HAVING total_price >= 1000
    ) tb
    ON tb.order_num = o.order_num
 
ORDER BY tb.total_price ASC;

select c.cust_name,sum(oi.item_price*oi.quantity) total_price
from OrderItems oi,Orders o,Customers c
where oi.order_num=o.order_num and o.cust_id=c.cust_id
group by c.cust_name
having total_price>=1000
order by total_price;

• 很特别的using

Products表为产品信息表含有字段prod_id产品id、prod_name产品名称

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola
a0023	soda

OrderItems表为订单信息表含有字段order_num订单号和产品id prod_id

prod_id	order_num
a0001	a105
a0002	a1100
a0002	a200
a0013	a1121
a0003	a10
a0003	a19
a0003	a5

【问题】

使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按照产品名称升序排序。

【示例结果】

返回产品名称prod_name和订单号order_num

prod_name	order_num
coffee	a1121
cola	a5
cola	a19
cola	a10
egg	a105
sockets	a200
sockets	a1100
soda	NULL

【示例解析】

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

使用using必须满足如下两个条件：

1. 查询必须是等值连接。

2. 等值连接中的列必须具有相同的名称和数据类型。

select prod_name,order_num
from Products
left join OrderItems using(prod_id)
order by prod_name

另外一种解法

(
select a.prod_name,  b.order_num 
from Products a

left join  OrderItems b 
on a.prod_id = b.prod_id

)
union
(
select a.prod_name,  b.order_num 
from Products a

right join  OrderItems b 
on a.prod_id = b.prod_id

)

Union

表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

【示例解析】

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

• 这里要区分union和join
• join—连接表，对列操作
• union–连接表，对行操作。
• • union–将两个表做行拼接，同时自动删除重复的行。
• • union all—将两个表做行拼接，保留重复的行。
    • 注意使用union的时候, 只能有一个order by

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;

• 投机取巧一下 : 直接使用or

select prod_id,quantity from OrderItems
where  quantity=100 or prod_id like 'BNBG%'
order by prod_id;