【SQL4天必知必会】day2. 通配符过滤、计算、函数、汇总

https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=298
💖牛客题目， SQL必知必会。不断熟悉，不断进步，加油！👍 💬 ⭐️

题目

通配符过滤

# 75. 模糊查询，%号通配符
SELECT prod_name, prod_desc FROM Products
WHERE prod_desc like '%toy%'

# 76. 模糊查询 not like  
SELECT prod_name, prod_desc FROM Products
WHERE prod_desc not like '%toy%' 

# 77. and连接查询条件
SELECT prod_name, prod_desc FROM Products
WHERE prod_desc like '%toy%' AND prod_desc like '%carrots%'

# 78. 三个通配符 %  以先后顺序出现 toy 和 carrots
SELECT prod_name, prod_desc FROM Products
WHERE prod_desc like '%toy%carrots%'

起别名

起别名 可以直接空格 也可以用 AS

# 79. 起别名， （可以直接空格 也可以用 AS）
SELECT vend_id, vend_name vname, vend_address vaddress, vend_city vcity
FROM Vendors 
ORDER BY vname ASC

# 80. 计算打折
SELECT prod_id, prod_price, prod_price * 0.9 sale_price 
FROM Products 

字符串操作

# 81. 字符串操作， 截取、拼接、大写转化
SELECT cust_id, cust_name, UPPER(CONCAT(SUBSTR(cust_contact, 1, 2), SUBSTR(cust_city, 1, 3))) user_login
FROM Customers

# 82. 日期比较
# 1.
SELECT order_num,  order_date FROM Orders
WHERE DATE_FORMAT(order_date, '%Y%m') = '202001'
ORDER BY order_date
# 2.
SELECT order_num,  order_date FROM Orders
WHERE YEAR(order_date) = '2020' AND MONTH(order_date) = '01'
ORDER BY order_date

计算

# 83. 求和
SELECT SUM(quantity) items_ordered FROM OrderItems

# 84. 带 WHERE 的求和， （会先进行 WHERE 过滤 然后再求和的）
SELECT SUM(quantity) items_ordered FROM OrderItems
WHERE prod_id = 'BR01'

# 85. 不超过 10 元的 最大产品价格
# 1. 过滤、排序、 取第一个
SELECT prod_price max_price FROM Products
WHERE prod_price <= 10
ORDER BY prod_price DESC
LIMIT 1
# 2. 直接过滤，然后取最大值 MAX()
SELECT MAX(prod_price) max_price FROM Products
WHERE prod_price <= 10

分组聚合计算

# 86. 分组汇总, 计算订单总数
SELECT order_num, COUNT(*) order_lines FROM OrderItems 
GROUP BY order_num
ORDER BY order_lines ASC

# 87.  分组，计算
SELECT vend_id, MIN(prod_price) cheapest_item FROM Products
GROUP BY vend_id
ORDER BY cheapest_item

# 88. 分组，过滤
SELECT order_num FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity) >= 100
ORDER BY order_num ASC

# 89. 计算、总和
SELECT order_num, SUM(item_price * quantity) total_price
FROM OrderItems
GROUP BY order_num
HAVING total_price >= 1000

# 90. 分组，计算，过滤
SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num 
HAVING items >= 3 
ORDER BY items, order_num;

知识点解析

SQL字符串操作

字符串截取 substr

SUBSTR(string,start_position,[length]) 
# start_position 从 1 开始

字符串拼接

CONCAT('拼接', '字符串')

# 查找字符串位置
INSTR(string,subString,[position],[ocurrence])
# string : 源字符串，  subString： 要查找的子串， position：从源串哪里开始， ocurrence： 第几次出现
INSTR('sss:aa',':')    # 找到 : 第一次出现的索引

其他字符串操作

to_number()  # 字符串转数字

upper() # 转大写

# 替换字符
replace('替换字符串', '替换', '修改') 

SQL 字符串与日期

YEAR(order_date)
MONTH(order_date)
DAY(order_date)

DATE_FORMAT(order_date, '%Y%m') = '202001'