Datawhale-MySQL-任務二

1.匯入示例資料庫

• 使用命令列匯入： 教程 MySQL匯入示例資料庫 - MySQL教程™
• 使用Navicat for MySQL視覺化操作匯入：
  • 在資料庫連線的基礎上新建資料庫，自定義資料庫名，字符集和排序規則也自選，最好是和需要匯入的資料庫格式一致：
  • 新建完成資料庫後右鍵點選選擇執行SQL檔案，出現如下圖所示視窗，填寫你需要匯入的資料庫檔案的地址
    
  • 填寫無誤後點選開始即開始匯入，匯入完成後顯示如下，點選關閉即可。
  • 測試匯入結果，查詢例項示例如下，結果正確顯示，說明匯入成功了。

2.SQL是什麼，MySQL是什麼?

• SQL: SQL（發音為字母 S-Q-L或 sequel）是 Structured Query Language（結構
  化查詢語言）的縮寫。SQL是一種專門用來與資料庫溝通的語言。
  SQL有如下的優點:
  • SQL不是某個特定資料庫供應商專有的語言。幾乎所有重要的 DBMS 都支援 SQL，所以學習此語言使你幾乎能與所有資料庫打交道。
  • SQL簡單易學。它的語句全都是由有很強描述性的英語單片語成，而 且這些單詞的數目不多。
  • SQL雖然看上去很簡單，但實際上是一種強有力的語言，靈活使用其 語言元素，可以進行非常複雜和高階的資料庫操作。
• MySQL: MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，目前屬於 Oracle 旗下產品。MySQL 是最流行的關係型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關聯式資料庫管理系統) 應用軟體。
  本質區別：SQL是一種語言，而MySQL是一種資料庫

3.查詢語句（select from）

• 語句解釋：從一個或多個表中檢索資訊。
• 去重語句：使用 DISTINCT關鍵字，顧名思義，它指示資料庫只返回不同的值。
  注：注意：不能部分使用 DISTINCT，DISTINCT關鍵字作用於所有的列，不僅僅是跟在其後的那一列。例如，你指定SELECT DISTINCT vend_id, prod_price，除非指定的兩列完全相同，否則所有的行都會被檢索出來。
• 前N個語句：使用LIMIT關鍵字來限制最多返回多少行，eg:SELECT prod_name
  FROM Products LIMIT 0 OFFSET N;指示 MySQL等 DBMS返回從第 1行起的 N行資料。第一個數字是指從哪兒開始，第二個數字是檢索的行數。 注意：第 0 行:第一個被檢索的行是第 0行，而不是第 1行。因此，LIMIT 1 OFFSET 1會檢索第 2行，而不是第 1行。
• CASE…END判斷語句

4.篩選語句（where）

• 語句解釋：資料庫表一般包含大量的資料，很少需要檢索表中的所有行。通常只會根據特定操作或報告的需要提取表資料的子集。只檢索所需資料需要指定搜尋條件（search criteria），搜尋條件也稱為過濾條件（filter condition）。在SELECT語句中，資料根據WHERE子句中指定的搜尋條件進行過濾。
• 操作符：SQL支援表 4-1列出的所有條件操作符。
• 萬用字元：
  • 1.百分號（%）：在搜尋串中，%表示任何字元出現任意次數。特別注意，除了能匹配一個或多個字元外，%還能匹配 0個字元。%代表搜尋模式中給定位置的 0個、1個或多個字元。但是WHERE prod_name LIKE '%'不會匹配產品名稱為NULL的行。
  • 2.下劃線（_）：下劃線的用途與%一樣，但它只匹配單個字元，而不是多個字元。
  • 方括號（[ ]）：用來指定一個字符集，它必須匹配指定位置（萬用字元的位置）的一個字元。

  使用萬用字元技巧：
  不要過度使用萬用字元。如果其他操作符能達到相同的目的，應該使用其他操作符。
  在確實需要使用萬用字元時，也儘量不要把它們用在搜尋模式的開始處。把萬用字元置於開始處，搜尋起來是最慢的。
  仔細注意萬用字元的位置。如果放錯地方，可能不會返回想要的資料。

5.分組語句（group by）

• 聚集函式：
  
• 語句解釋：使用分組可以將資料分為多個邏輯組，對每個組進行聚集計算。
  • 使用GROUP BY子句的一些重要規定：

    GROUP BY子句可以包含任意數目的列，因而可以對分組進行巢狀，更細緻地進行資料分組。
    如果在GROUP BY子句中巢狀了分組，資料將在最後指定的分組上進行彙總。換句話說，在建立分組時，指定的所有列都一起計算（所以不能從個別的列取回資料）。
    GROUP BY子句中列出的每一列都必須是檢索列或有效的表示式（但不能是聚集函式）。如果在SELECT中使用表示式，則必須在GROUPBY子句中指定相同的表示式。不能使用別名。
    大多數 SQL實現不允許GROUP BY列帶有長度可變的資料型別（如文字或備註型欄位）。
    除聚集計算語句外，SELECT語句中的每一列都必須在GROUPBY子句中給出。
    如果分組列中包含具有NULL值的行，則NULL將作為一個分組返回。如果列中有多行NULL值，它們將分為一組。
    GROUP BY子句必須出現在WHERE子句之後，ORDER BY子句之前。

• HAVING子句：HAVING非常類似於WHERE。唯一的差別是，WHERE過濾行，而HAVING過濾分組。另一種理解方法，WHERE在資料分組前進行過濾，HAVING在資料分組後進行過濾。這是一個重要的區別，WHERE排除的行不包括在分組中。這可能會改變計算值，從而影響 HAVING子句中基於這些值過濾掉的分組。

6.排序語句（order by）

• 語句解釋：ORDER BY子句取一個或多個列的名字，據此對輸出進行排序。
• 正序、逆序：預設的排序順序是升序，若想使用ORDER BY子句進行降序排序，必須使用關鍵字DESC。注：DESC關鍵字只應用到直接位於其前面的列名。如果想在多個列上進行降序排序，必須對每一列指定DESC關鍵字。

7.函式

與幾乎所有 DBMS都等同地支援 SQL語句（如 SELECT）不同，每一個DBMS都有特定的函式。

• 時間函式：取當前時間：Access使用 NOW()； DB2和 PostgreSQL使用 CURRENT_DATE；
  MariaDB和MySQL使用CURDATE()；Oracle使用SYSDATE；SQL
  Server使用GETDATE()；SQLite使用DATE()
• 數值函式：資料型別轉換：Access和Oracle使用多個函式，每種型別的轉換有一個函式；DB2和PostgreSQL使用CAST()；MariaDB、MySQL和SQL Server使用CONVERT()
  
• 字串函式：提取字串的組成部分：Access使用 MID()； DB2、 Oracle、 PostgreSQL和 SQLite使用SUBSTR()；MySQL和SQL Server使用SUBSTRING()
  

8.SQL註釋

• 註釋的作用：
  • 隨著 SQL語句變長，複雜性增加，新增一些描述性的註釋，便於今後參考，或者供專案後續參與人員參考。
  • 適用於 SQL檔案開始處的內容，它可能包含程式設計師的聯絡方式、程式描述以及一些說明。
  • 暫時停止要執行的 SQL程式碼。如果你碰到一個長 SQL語句，而只想測試它的一部分，那麼應該註釋掉一些程式碼，以便 DBMS將其視為註釋而加以忽略。
• 註釋的方法：
  • 行內註釋:

  SELECT prod_name - - 這是一條註釋
  FROM Products;
  - -之後的文字就是註釋
  # 這是一條註釋
  SELECT prod_name
  FROM Products;
  在一行的開始處使用#，這一整行都將作為註釋。

  • 多行註釋:

  /* SELECT prod_name, vend_id
  FROM Products; */
  SELECT prod_name
  FROM Products;
  註釋從/開始，到/結束，/和/之間的任何內容都是註釋。這種方式常用於給程式碼加註釋，就如這個例子演示的，這裡定義了兩個 SELECT語句，但是第一個不會執行，因為它已經被註釋掉了。

9.SQL程式碼規範

• 命名建議：
  • 使用統一的、描述性強的欄位命名規則
  • 保證欄位名是獨一無二且不是保留字的，不要使用連續的下劃線，不用下劃線結尾
  • 最好以字母開頭
• 格式建議：
  • 最好使用標準SQL函式而不是特定供應商Oracle、Mysql等的函式以提高可移植性。
  • 大小寫的運用：系統關鍵字大寫，欄位表名小寫
  • 靈活使用空格和縮排來增強可讀性——兩大法寶空白隔道與垂直間距

  select name,id,sex
  from (select *
  from school_score
  where class_cd=110)
  where sex = ‘man’
  and exam_dt = ‘2016-06-01’;
  - -空白隔道+垂直間距+大小寫+縮排（感受下是不是下面的看得更清晰有序呢）
  SELECT name, id, sex
  　FROM (SELECT *
  　　　　　　FROM school_score
  　　　　　WHERE class_cd = 110)
  　WHERE sex = ‘man’
  　　 AND exam_dt = ‘2016-06-01’;

• 語法建議：
  • 儘量使用BETWEEN而不是多個AND
  • 同樣，使用 IN 而不是多個OR
  • 利用CASE語句巢狀處理更復雜的邏輯結構
  • 避免UNION語句與臨時表
• Tools：
  • SQLinForm（SQL Formatter for Professionals - Also Free Online SQL Beautifiers & Plugins available）外掛
  • 對於Sublime重度愛好者可以安裝zsong/SqlBeautifier外掛，CTRL+K 加 CTRT+F 實現格式轉換，相比SQLinForm，自定義功能略顯單薄，但也足夠可用。
  • 喜歡離線操作的老鐵，可以使用Notepad++的使用者可以安裝外掛或者下載官方程式設計程式，勾選LIVE FORMATTING，實現實時自動整理格式的功能。

10.作業

• 專案一：查詢重複的電子郵箱（難度：簡單）
  建立 email表，並插入如下三行資料
  ±—±--------+
  | Id | Email |
  ±—±--------+
  | 1 | a@b.com |
  | 2 | c@d.com |
  | 3 | a@b.com |
  ±—±--------+
  編寫一個 SQL 查詢，查詢 email 表中所有重複的電子郵箱。
  根據以上輸入，你的查詢應返回以下結果：
  ±--------+
  | Email |
  ±--------+
  | a@b.com |
  ±--------+
  說明：所有電子郵箱都是小寫字母。

結果截圖

• 專案二：查詢大國（難度：簡單）
  建立如下 World 表
  ±----------------±-----------±-----------±-------------±--------------+
  | name | continent | area | population | gdp |
  ±----------------±-----------±-----------±-------------±--------------+
  | Afghanistan | Asia | 652230 | 25500100 | 20343000 |
  | Albania | Europe | 28748 | 2831741 | 12960000 |
  | Algeria | Africa | 2381741 | 37100000 | 188681000 |
  | Andorra | Europe | 468 | 78115 | 3712000 |
  | Angola | Africa | 1246700 | 20609294 | 100990000 |
  ±----------------±-----------±-----------±-------------±--------------+
  如果一個國家的面積超過300萬平方公里，或者(人口超過2500萬並且gdp超過2000萬)，那麼這個國家就是大國家。
  編寫一個SQL查詢，輸出表中所有大國家的名稱、人口和麵積。
  例如，根據上表，我們應該輸出:
  ±-------------±------------±-------------+
  | name | population | area |
  ±-------------±------------±-------------+
  | Afghanistan | 25500100 | 652230 |
  | Algeria | 37100000 | 2381741 |
  ±-------------±------------±-------------+

結果截圖