SQL的編寫與運用

0. 寫在前面

最近學習了數據庫系統概論，其中涉及到了關于SQL語句的編寫，感覺理論知識不足以讓我掌握相關的編寫方式，因此選擇刷力扣上的題目進行復習鞏固。

時間不是很多，可能不會經常更新，有時間寫幾道題目便會在后面進行題解補充和感想撰寫。

1. MySQL 編寫

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

沒有啥難度，直接使用SQL語句進行搜索即可

關鍵字 SELECT 用于指定我們想要從表 Products 中檢索的列。在這種情況下，我們想要檢索 product_id 列。關鍵字 WHERE 用于根據特定條件過濾表 Products 中的行，條件是 low_fats 列的值為"Y"（表示低脂肪產品）且 recyclable 列的值為"Y"（表示可回收產品）。我們使用邏輯運算符 AND 將兩個條件組合起來，確保最終結果只包含既是低脂肪產品又是可回收產品的產品ID。

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

584. 尋找用戶推薦人 - 力扣（LeetCode）

這道題目的主要難點就是需要知道如何查詢到字段值為NULL的人，也就是查詢到沒有推薦人的人。

初步想法是這樣的

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值是最推薦的做法。

因此正確的代碼如下：

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

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

-- 使用行過濾來識別滿足條件的國家
SELECT * 
FROM world 
WHERE area >= 3000000 OR population >= 25000000
-- 然后按照問題的要求返回三列
SELECT name, population, area 
FROM world 
WHERE area >= 3000000 OR population >= 25000000

還可以使用union進行實現

# 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）

這道題目主要的難點就是需要進行重命名，然后需要升序排序。

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 個字節。

SELECT tweet_id
FROM tweets
WHERE CHAR_LENGTH(content) > 15# LENGTH(content)>15  也是可以的，但是要是出現了非英文的字符，可能就會出錯了

1378. 使用唯一標識碼替換員工ID - 力扣（LeetCode）

SELECT  # 指定要返回的列EmployeeUNI.unique_id, Employees.name
FROM Employees
LEFT JOIN  # 將表進行連接,LEFT JOIN 的特點是，即使 EmployeeUNI表中沒有匹配的記錄，Employees 表中的所有記錄都會出現在結果集中。EmployeeUNI
ON # 指定連接條件Employees.id = EmployeeUNI.id;

1068. 產品銷售分析 I - 力扣（LeetCode）

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的就可以了。

因為左連接是以左表為主導，確保左表的所有記錄都會出現在結果集中，而右表中的記錄只有在與左表有匹配時才會出現。

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耗時長

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可以進行使用

# 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的使用方式

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 而不是直接簡單的使用=號進行判斷

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進行填充即可。最后對相應位置進行排序即可。

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;