重拾Sql語句

ltlovezh發表於2017-10-09
SQL

最近做資料上報的報表，發現一些基礎的SQL模糊不清了，這裡持續記錄使用到的SQL操作。

基礎的SQL

基本的SQL語句可以概括為以下虛擬碼：

SELECT 
DISTINCT <select_list> 
FROM <left_table> 
<join_type> JOIN <right_table> 
ON <join_condition> 
WHERE <where_condition> 
GROUP BY <group_by_list> 
HAVING <having_condition> 
ORDER BY <order_by_condition> 
LIMIT <limit_number>複製程式碼

搞清楚上面各個虛擬碼的執行順序，對我們寫SQL有很大的幫助:

  1. 首先是處理FROM <left_table> <join_type> JOIN <right_table> ON <join_condition>，對兩張表根據ON操作符指定的關聯條件，合併成一個臨時表A。

    <join_type>包括：INNER JOIN、Full outer join、LEFT OUTER JOIN、RIGHT OUTER JOIN、CROSS JOIN。

    1. INNER JOIN表示合併的臨時表A中，僅包含符合ON條件的記錄。
    2. LEFT OUTER JOIN表示合併的臨時表A中，不僅包含符合ON條件的記錄，還會把left_table中剩餘的記錄也儲存在臨時表，同時將對應的right_table中的所有列欄位賦值為NULL。
    3. RIGHT OUTER JOIN表示合併的臨時表A中，不僅包含符合ON條件的記錄，還會把right_table中剩餘的記錄也儲存在臨時表A，同時將對應的left_table中的所有列欄位賦值為NULL。
    4. Full outer join表示合併的臨時表A中，不僅包含符合ON條件的記錄，還會把left_table和right_table中剩餘的記錄也儲存在臨時表A，同時將對應的另外一張表的列欄位賦值為NULL。其實就是兩張表的交集。
    5. CROSS JOIN表示兩張表的笛卡爾積，一般很少使用。
      關於JOIN的各種使用，可以參考圖解SQL的JOIN
  2. 第一步把兩張表合併到了一張臨時表A，接下來是對臨時表A處理WHERE <where_condition>指定的過濾條件，刪除一些不符合條件的記錄，得到臨時表B。
  3. 上一步得到了篩選記錄後的臨時表B，接下來針對臨時表B，根據GROUP BY <group_by_list>指定的列欄位，進行分組操作。然後根據HAVING <having_condition>指定的條件對分組進行過濾，得到臨時表C。這裡HAVING指定的條件只能包含分組欄位，或者其他列欄位的聚合函式。
  4. 上一步得到了分組後的臨時表C，接下來就是根據SELECT DISTINCT <select_list>規定的欄位，選出僅包含指定列欄位的臨時表D，如果指定了DISTINCT，那麼還要把重複的行記錄過濾掉。
  5. 上一步得到了篩選列欄位後的臨時表D，然後就是根據ORDER BY <order_by_condition>指定的列欄位，對臨時表D的所有行記錄進行排序，得到臨時表E。
  6. 最後，根據LIMIT <limit_number>指定的條件，從臨時表E中，摘錄出指定數量的行記錄，生成最終的結果表。
    limit的用法是：limit n,m，表示從第n條記錄開始選擇m條記錄。一般可用於列表分頁，對於小資料，使用limit沒有任何問題。但是當資料量非常大的時候，使用limit是非常低效的。因為limit的機制是每次都從頭開始掃描，如果需要從第50萬行開始，讀取10條資料，那麼就需要先掃描定位到第50萬行，然後再讀取10條記錄，而掃描是一個非常低效的過程。複製程式碼

一般情況下，基本的SQL語句都可以按照上面6個步驟進行分析。

SQL函式

上面介紹了基本SQL語句的內部執行順序，下面我們看一些常用的SQL函式，這些函式的使用能幫助我們解決一些複雜的SQL問題。

Case When Then

Case函式很像if else語句，可以進行多條件判斷。Case具有兩種格式：簡單Case函式和Case搜尋函式。

簡單Case函式

CASE sex
    WHEN '1' THEN '男'
    WHEN '2' THEN '女'
ELSE '其他' END複製程式碼

Case搜尋函式

CASE WHEN sex = '1' THEN '男'
     WHEN sex = '2' THEN '女'
ELSE '其他' END

SELECT name, score, 
(CASE WHEN score < 60 THEN '不及格' 
WHEN score BETWEEN 60 AND 90 THEN '良好' 
WHEN score > 90 THEN '優秀' END) as level
FROM student複製程式碼

上面兩種方式，可以實現相同的功能。簡單Case函式的寫法相對比較簡潔，但是和Case搜尋函式相比，功能方面會有些限制，比如寫判斷式。

具體案例

有如下一個資料庫表，標示了各個國家的人口，要求求出亞洲和美洲的人口總數：
| country | people |
| -------- | :-----: |
| brazil | 100 |
| china | 100 |
| india | 100 |
| mexico | 100 |
| usa | 100 |
| england | 100 |

我們只要把屬於亞洲和美洲的國家的人口累加，就可以了。所以sql語句如下所示：

SELECT 
(case country when 'china' then "asia"
             when 'india' then "asia"
             when 'mexico' then "america"
             when 'usa' then "america"
             when 'brazil' then "america"
             else
                "other"
             end) as continent , sum(people) as num FROM leon.TableA
group by
(case country when 'china' then "asia"
             when 'india' then "asia"
             when 'mexico' then "america"
             when 'usa' then "america"
             when 'brazil' then "america"
             else
                "other"
             end);複製程式碼

上述SQL語句首先把country分為亞洲和美洲兩個維度，然後根據這個新維度進行分組，並使用聚合函式，求出各個大洲的總人口。
最後得出的結果表如下所示：
| continent | num |
| -------- | :-----: |
| america | 300 |
| asia | 200 |
| other | 100 |

後續使用到繼續補充...

參考文件

相關文章