SQL的编写与运用

0. 写在前面

最近学习了数据库系统概论，其中涉及到了关于SQL语句的编写，感觉理论知识不足以让我掌握相关的编写方式，因此选择刷力扣上的题目进行复习巩固。

时间不是很多，可能不会经常更新，有时间写几道题目便会在后面进行题解补充和感想撰写。

1. MySQL 编写

1757. 可回收且低脂的产品 - 力扣（LeetCode）

没有啥难度，直接使用SQL语句进行搜索即可

关键字 SELECT 用于指定我们想要从表 Products 中检索的列。在这种情况下，我们想要检索 product_id 列。关键字 WHERE 用于根据特定条件过滤表 Products 中的行，条件是 low_fats 列的值为"Y"（表示低脂肪产品）且 recyclable 列的值为"Y"（表示可回收产品）。我们使用逻辑运算符 AND 将两个条件组合起来，确保最终结果只包含既是低脂肪产品又是可回收产品的产品ID。

sql">selectproduct_id
fromProducts
where low_fats="Y" and recyclable="Y"

584. 寻找用户推荐人 - 力扣（LeetCode）

这道题目的主要难点就是需要知道如何查询到字段值为NULL的人，也就是查询到没有推荐人的人。

初步想法是这样的

sql">SELECT name FROM customer WHERE referee_Id <> 2;
# SELECT name FROM customer WHERE referee_Id != 2;  -- 和 # 都是表示单行注释的意思

但是实际上上面的语句是查询不到NULL字段值的，因为

• NULL是一个特殊值，代表缺失的数据，不应该用NULL == NULL这样的逻辑来判断。
• NULL值不能参与普通的比较运算，如<, >, =, <=, >=。
• 使用IS NULL或IS NOT NULL来查询NULL值是最推荐的做法。

因此正确的代码如下：

sql">SELECT name FROM customer WHERE referee_id != 2 OR referee_id IS NULL;

595. 大的国家 - 力扣（LeetCode）

sql">-- 使用行过滤来识别满足条件的国家
SELECT * 
FROM world 
WHERE area >= 3000000 OR population >= 25000000
-- 然后按照问题的要求返回三列
SELECT name, population, area 
FROM world 
WHERE area >= 3000000 OR population >= 25000000

还可以使用union进行实现

sql"># Write your MySQL query statement beloow
select name, population, area from world where population >= 25000000
union
select name, population, area from world where area >= 3000000;

关于or和union的区别，大致如下：

对于单列来说，使用or是没有问题的，但是or涉及到多个列的时候，每次select只能选取一个ndex，如果选择了area，population就需要进行table-scan，即全部扫描一遍，但是使用union就可以解决这个问题，分别使用area和population上面的index进行查询。 但是这里还会有一个问题就是，UNION会对结果进行排序去重，可能会降低一些performance

$$\begin{gathered} union用于连接两个sql，所以可以查询返回列相同的select语句，所以表可以不是同一张，而or的对象只能是同一张表； \\ union可以实现将判断不同字段的两个sql联合查询，or固然也可以实现，但是很容易让人绕进去，没有union那么通俗易懂。 \end{gathered}$$

1148. 文章浏览 I - 力扣（LeetCode）

这道题目主要的难点就是需要进行重命名，然后需要升序排序。

sql">SELECT DISTINCT author_id AS id # DISTINCT用于消除查询结果中的重复行，只保留唯一的记录。
FROM Views 
WHERE author_id = viewer_id 
ORDER BY id # id DESC  表示按照id降序进行排序

1683. 无效的推文 - 力扣（LeetCode）

题目中的主要难点就是需要知道mysql求解字符串长度的函数

对于SQL表，用于计算字符串中字符数的最佳函数是 CHAR_LENGTH(str)，它返回字符串 str 的长度。另一个常用的函数 LENGTH(str) 在这个问题中也适用，因为列 content 只包含英文字符，没有特殊字符。否则，LENGTH() 可能会返回不同的结果，因为该函数返回字符串 str 的字节数，某些字符包含多于 1 个字节。

以字符 ‘¥’ 为例：CHAR_LENGTH() 返回结果为 1，而 LENGTH() 返回结果为 2，因为该字符串包含 2 个字节。

sql">SELECT tweet_id
FROM tweets
WHERE CHAR_LENGTH(content) > 15# LENGTH(content)>15  也是可以的，但是要是出现了非英文的字符，可能就会出错了

1378. 使用唯一标识码替换员工ID - 力扣（LeetCode）

sql">SELECT  # 指定要返回的列EmployeeUNI.unique_id, Employees.name
FROM Employees
LEFT JOIN  # 将表进行连接,LEFT JOIN 的特点是，即使 EmployeeUNI表中没有匹配的记录，Employees 表中的所有记录都会出现在结果集中。EmployeeUNI
ON # 指定连接条件Employees.id = EmployeeUNI.id;

1068. 产品销售分析 I - 力扣（LeetCode）

sql">SELECT p.product_name , s.year , s.price
# 指定了查询返回的结果集应包含哪些列
FROM Sales s # 定义了一个主表，并指定了一个别名
LEFT JOIN Product p
# 将Sales表与另一个名为Product的表进行连接，并给Product表指定了一个别名p。
# LEFT JOIN意味着即使Product表中没有匹配项，也会返回Sales表中的所有记录。如果没有匹配项，则结果中的Product字段将为NULL。
ON s.product_id = p.product_id
# 定义了两个表之间的连接条件

1581. 进店却未进行过交易的顾客 - 力扣（LeetCode）

这题主要就是需要看懂题目表达的意思：题目翻译过来就是左连接，然后找出来null的就可以了。

因为左连接是以左表为主导，确保左表的所有记录都会出现在结果集中，而右表中的记录只有在与左表有匹配时才会出现。

sql">select customer_id ,count(customer_id) count_no_trans
from Visits v
left join Transactions t
on v.visit_id=t.visit_id
where transaction_id is null
group by customer_id;

然后还可以使用不相关子查询进行求解：只要是visit_id不在Transactions表中但是在visits表中的话，则这样的数据是满足条件的，基于此，查询代码如下：但是下面每次检查not in耗时长

sql">SELECT customer_id,count(visit_id) as count_no_trans
FROM Visits
WHERE visit_id not in (SELECT DISTINCT visit_id FROM Transactions)
# 使用 DISTINCT 可以避免重复排除同一个 visit_id，确保每个 visit_id 只被考虑一次。
GROUP BY customer_id
# 将数据分组，以便对每组数据执行聚合函数

197. 上升的温度 - 力扣（LeetCode）

这题一开始的想法就是将表和自己进行笛卡尔积，但是后面发现不太对，因为只需要找出比其前一天温度高的天气即可，看解析后发现有个函数datediff可以进行使用

sql"># Write your MySQL query statement below
select w2.id
from Weather w1,Weather w2
Where datediff(w2.recordDate,w1.recordDate)=1 and w2.temperature>w1.temperature

1661. 每台机器的进程平均运行时间 - 力扣（LeetCode）

这道题目主要是需要知道round的使用方式

sql">select a.machine_id ,
round(avg(a.timestamp-b.timestamp),3) as processing_time  
from Activity a
join Activity b
on  # 下面的是连接条件:a、b表的机器、进程id都对应相等，且a表类型是end，b表类型是start 将这样的行按机器id分组
a.machine_id=b.machine_id and
a.process_id=b.process_id and
a.activity_type='end' and
b.activity_type='start'
group by a.machine_id

577. 员工奖金 - 力扣（LeetCode）

还是做题目了有效果的，我一看题目要求和样例，我就知道需要考虑以下事情：

1、由于奖金不是所有员工有的，因此Bonus表和Employee表进行连接的话，需要对Bonus进行左连接。

2、要是想要取出null值的话，需要使用is null 而不是直接简单的使用=号进行判断

sql">select name,bonus
from Employee
left join bonus
on Employee.empId=bonus.empId
where bonus<1000 or bonus is null

1280. 学生们参加各科测试的次数 - 力扣（LeetCode）

首先需要统计每个学生参加每个科目的考试次数，因此需要使用group by，考虑到还需要返回其他的属性，因此这里需要使用到子查询。同时考虑到学生和科目都是有可能进行连接的，因此这里选择使用笛卡尔积将学生表和科目表进行连接，也就是使用cross join进行连接。然后再将子查询得到的表进行左连接，考虑到会有null的情况，使用ifnull进行填充即可。最后对相应位置进行排序即可。

sql">SELECT s.student_id, s.student_name, sub.subject_name, IFNULL(grouped.attended_exams, 0) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN (SELECT student_id, subject_name, COUNT(*) AS attended_exams  # 前面两个属性是用来进行连接的，后面的才是需要查询的属性FROM ExaminationsGROUP BY student_id, subject_name
) grouped 
ON s.student_id = grouped.student_id AND sub.subject_name = grouped.subject_name
ORDER BY s.student_id, sub.subject_name;