【牛客网SQL“必知必会”】刷题记录:一些容易遗忘的知识点

已于 2022-06-24 21:23:09 修改
阅读量638 收藏 2
点赞数 1
文章标签： sql 数据库 database
于 2022-06-24 21:22:06 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_45480097/article/details/125420023
版权

目录

【导读】

【SQL13】知识点：字符串 IN (范围选择)

 【SQL14】知识点：BETWEEN，数值范围选择

  【SQL16】知识点：LIKE

 【SQL22】知识点：字符串拼接

 【SQL23】知识点：时间函数格式化

 【SQL25】知识点:WHERE/GROUP BY /HAVING总结

 【SQL31】知识点：执行顺序和什么时候可以使用别名

 【SQL34】知识点：子查询和表连接

  【SQL38】知识点：子查询和连接查询

  【SQL40】知识点：连接查询的写法

【导读】

本人在刷题的时候会发现知识点都遗漏了，于是想把遗漏的知识点总结在一篇博客中，后续复习题目可以直接翻看博客，比直接看错题直接。博客中也有相关知识点的补充和阅读。

【SQL13】知识点：字符串 IN (范围选择)

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

我自己的写法：

SELECT  order_num, prod_id, quantity FROM OrderItems
WHERE prod_id = 'BR01' OR prod_id ='BR02' OR prod_id ='BR03'
HAVING quantity >= 100;

实际上更好的写法：

SELECT  order_num, prod_id, quantity FROM OrderItems
WHERE prod_id IN ('BR01','BR02','BR03')
HAVING quantity >= 100;

 【SQL14】知识点：BETWEEN，数值范围选择

【问题】

我自己的写法：

SELECT prod_name, prod_price FROM Products 
WHERE prod_price >=3 AND prod_price <=6 
ORDER BY prod_price ASC

实际上更好的写法：

SELECT prod_name, prod_price FROM Products 
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price ASC

  【SQL16】知识点：LIKE

【问题】LIKE 的用法

SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%'

LIKE的用法:

LIKE语句的语法格式是：select * from 表名 where 字段名 like 对应值（子串），它主要是针对字符型字段的，它的作用是在一个字符型字段列中检索包含对应子串的。

通配符有两个：

1.百分号(%): 代表零个、一个或多个字符

2.下划线(_)：代表一个数字或字符

【注】如果不想要查找某个字符串，直接在LIKE 前面加 NOT

 【SQL22】知识点：字符串拼接

【问题】

知识点:

  • 字符串的拼接:CONCAT(字符串1，字符串2,......,字符串n)；
  • 大写:UPPER；
  • 字符串的截取:SUBSTRING(字符串,截取开始位置，截取字符数)【备注：SQL从1开始排序，截取时包括截取起始位置的字符】
SELECT cust_id, cust_name, UPPER(CONCAT(SUBSTRING(cust_name,1,2),SUBSTRING(cust_city,1,3))) AS user_login
FROM Customers

实际上更好的写法：

SELECT prod_name, prod_price FROM Products 
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price ASC

 【SQL23】知识点：时间函数格式化

【问题】

我自己的写法，用的是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 order_num, order_date 
from Orders
where left(order_date, 7) = '2020-01'
order by order_date
------------
'正则表达式'
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
-----------
'时间匹配函数2'
select order_num, order_date 
from Orders
where date_format(order_date, '%Y-%m')='2020-01'
order by order_date

知识点：应该是时间函数

  • YEAR(时间字符串),MONTH(时间字符串)
  • DATE_FORMAT(时间字符串,匹配规则)

 【SQL25】知识点:WHERE/GROUP BY /HAVING总结

【问题】

第一种写法：where
SELECT SUM(quantity) AS items_ordered FROM OrderItems
WHERE prod_id ='BR01'
第二种写法:group by +having
SELECT SUM(quantity) AS items_ordered FROM OrderItems
GROUP BY prod_id
HAVING prod_id = 'BR01'

知识点:

注意：where肯定在group by 之前。

一，group by 字句可以和where条件语句结合在一起使用。

当结合在一起时，where在前，group by 在后。

即先对select xx from xx的记录集合用where进行筛选，然后再使用group by 对筛选后的结果进行分组 使用having字句对分组后的结果进行筛选。

二，需要注意having和where的用法区别：

having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。

where肯定在group by 之前。

where  是对记录的限定

having 是对分组后结果的限定

where后的条件表达式里不允许使用聚合函数，而having可以。

区别：

 where:

  •     where是一个约束声明,使用where来约束来自数据库的数据;
  •     where是在结果返回之前起作用的;
  •     where中不能使用聚合函数。

 having:

  •     having是一个过滤声明;
  •     在查询返回结果集以后，对查询结果进行的过滤操作;
  •     在having中可以使用聚合函数。

三，当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：

1.执行where xx对全表数据做筛选，返回第1个结果集。

2.针对第1个结果集使用group by分组，返回第2个结果集。

3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。

4.针对第3个结集执行having xx进行筛选，返回第4个结果集。

5.针对第4个结果集排序。

 【SQL31】知识点：执行顺序和什么时候可以使用别名

【问题】代码纠错，很简单，但是会有一些易错点

 知识点：SQL执行顺序（与MYSQL不太一样）

from where group by having order by select limit

也就是说 where 和 group having order by 是不能使用别名的，因为这个时候我们还没执行到select，别名是在select中生成的

 MYSQL完整的书写顺序

 MYSQL完整的执行顺序

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->SELECT子句->ORDER BY子句->LIMIT子句->最终结果  

注意：

在mysql中，group by中可以使用别名；where中不能使用别名；order by中可以使用别名。

所以我觉得，为了减少错误还是少用别名

附上详细链接:

sql中group by后使用别名_我家大宝最可爱的博客-优快云博客

 【SQL34】知识点：子查询和表连接

【问题】

返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

我自己的写法：（十分拉跨，两次子查询）

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

实际上更好的写法：

知识点

  • 内连接
SELECT C.cust_email FROM OrderItems O,Customers C,Orders OS
WHERE O.prod_id = 'BR01' AND O.order_num = OS.order_num AND OS.cust_id =C.cust_id
  •  表连接
SELECT C.cust_email FROM OrderItems O
JOIN Orders OS
ON OS.order_num = O.order_num
JOIN Customers C
ON OS.cust_id =C.cust_id
WHERE O.prod_id = 'BR01'

  【SQL38】知识点：子查询和连接查询

【问题】

我自己的写法：可以看到我用了两次子查询，子查询的效率并不高，而且要取很多别名。因此要换成连接查询

SELECT C.cust_name,B.order_nums,B.OrderTotal FROM Customers C
JOIN
(SELECT cust_id ,A.OrderT OrderTotal,A.order_num order_nums FROM Orders O
JOIN(
SELECT
  order_num, quantity * item_price AS OrderT
FROM
  OrderItems) A ON O.order_num = A.order_num) B
ON B.cust_id =C.cust_id
ORDER BY C.cust_name, B.OrderTotal

两次连接查询：

SELECT C.cust_name, OT.order_num, OT.OrderTotal FROM Orders O
JOIN(
SELECT order_num,SUM(quantity*item_price) OrderTotal FROM OrderItems GROUP BY order_num ) OT
ON
O.order_num = OT.order_num
JOIN(
SELECT cust_name,cust_id FROM Customers ) C
ON
C.cust_id = O.cust_id
ORDER BY cust_name,OrderTotal

更好的写法：比我自己写的好太多了！JOIN 后面直接写表，没有再查询

select c.cust_name,os.order_num,sum(os.quantity*os.item_price) OrderTotal from Orders o
join OrderItems os
on os.order_num=o.order_num
join Customers c
on c.cust_id=o.cust_id
group by c.cust_name,os.order_num
order by c.cust_name,os.order_num;

提问：为什么要GROUP BY两次？

回答：

  1. GROUP BY os.order_num是因为可能存在同个订单下有多个产品，所以用了聚合函数SUM
  2. GROUP BY c.cust_name是因为每个顾客对应着一份订单。我们按照订单分组求总价，自然而然也要按照顾客分组，求出每个订单对应的顾客。其实就是因为每个顾客可能下了多个订单，所以在custmoers表中会出现多个顾客名（但是题目给的没有，我们只是考虑到了这个情况），因此需要分组。如果不用SUM，即不对order_num分组，cust_name也不需要分组

 知识点：子查询和连接查询

  【SQL40】知识点：连接查询的写法

【问题】

【问题】返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

提示：涉及到 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id，但是必须使用 INNER JOIN 语法。

我自己的写法：

SELECT c.cust_email FROM OrderItems os
JOIN
Orders o
ON
o.order_num = os.order_num
JOIN
Customers c
ON
c.cust_id = o.cust_id
WHERE
os.prod_id = 'BR01'

实际上更好的写法：

区别在于把ON后面的内容一起写到JOIN中

select cust_email
from OrderItems join Orders using(order_num)
join Customers using(cust_id)
where prod_id='BR01'

目前为止【牛客网MYSQL必知必会系列】刷完一遍，继续刷下一专题

郑粗心
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值