<jupyter_start><jupyter_text>SQL 入門> 基本查詢郭耀仁<jupyter_code># 連結資料庫
import sqlite3
import pandas as pd
from test_queries.test_queries_02 import extract_test_queries as etq

conn_twelection = sqlite3.connect('twelection.db')
conn_nba = sqlite3.connect('nba.db')<jupyter_output><empty_output><jupyter_text>摘要- SQL 風格指南- 資料型態- 延續 `SELECT` 和 `FROM`- 隨堂練習- 隨堂練習參考解答  SQL 風格指南  什麼是風格指南> 程式語言的風格指南是一份描述在撰寫指定程式語言風格規範的文件，風格指南會隨著語言、團隊習慣的不同，而有不同的形式與內涵。  為什麼風格指南> 一個被部署在正式環境的應用程式應該要看起來像是由一位軟體工程師所撰寫出來的，即便是由數百名軟體工程師合力撰寫。這樣的程式原始碼能被更良好地維護、減少溝通不效率並降低系統出錯的風險。  如果對風格指南不滿意該怎麼辦才好> 假如剛加入團隊時發現對於風格指南不甚滿意，請先保持靜默，等到過了一段時間在團隊中已經建立的聲譽和信賴感，再針對具有「明顯效益」的風格指南提出建議。  「普遍」的 SQL 風格指南![](https://media.giphy.com/media/121em4mM0An4Pe/giphy.gif)Source:   課程將採用 [<NAME>olywell](https://www.simonholywell.com/) 的 SQL 風格指南Source:   建議遵守的部分- 善用「空白」與「縮排」提升可讀性- 使用 ISO-8601 的日期時間格式 `YYYY-MM-DD HH:MM:SS.SSSSS`- 為了移植便利性，盡可能使用標準 SQL 而非特定 DBMS 的功能或函式- 善用註解    - `/**/` 用來做多行註解    - `--` 用來做單行註解 ![Imgur](https://i.imgur.com/eBYN9Qe.png)Source: Google Search  命名的原則- 由英文字母起始並且不要以底線 `_` 終結- 不得使用保留字- 使用英文字母與數字，並且以底線 `_` 分隔單字- 不要使用多個底線例如 `__`- 盡量只使用很直觀的縮寫作為命名  標準 SQL 的保留字有哪些Source:   以全大寫來撰寫保留字：`SELECT * FROM table_name`  需要換行的時候採「靠右對齊」<jupyter_code>sql_query = """
SELECT *
  FROM teams
 WHERE confName = 'East';
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>何時加入空白- 在 `=` 的前後- 在 `,` 之後<jupyter_code>sql_query = """
SELECT *
  FROM teams
 WHERE divName IN ('Atlantic', 'Southeast');
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>命名與別名- 表格的命名以「複數」名詞為主- 變數的命名以「單數」名詞為主- 建立別名時用 `AS`<jupyter_code>sql_query = """
SELECT fullName AS team_name
  FROM teams
 LIMIT 5;
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>資料型態  使用 `PRAGMA TABLE_INFO(table_name)` 暸解表格的資料型態注意，這是 SQLite 資料庫系統專屬的語法，不同的資料庫系統具有自己語法。<jupyter_code>sql_query = """
PRAGMA TABLE_INFO(careerSummaries);
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>使用 `TYPEOF()` 函式暸解欄位的資料型態注意，這是 SQLite 資料庫系統專屬的函式，不同的資料庫系統具有自己的對應函式。<jupyter_code>sql_query = """
SELECT TYPEOF(points) AS points_dt,
       TYPEOF(ppg) AS ppg_dt
  FROM careerSummaries
 LIMIT 1;
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>資料型態的分類由資料庫系統決定，但大致都有對應這些型態- 數值- 文字- 遺漏值- 布林- 日期與時間  使用 `CAST(column_name AS data_type)` 在查詢時轉換欄位的資料型態<jupyter_code>sql_query = """
SELECT TYPEOF(points) AS points_int,
       TYPEOF(CAST(points AS REAL)) AS points_real
  FROM careerSummaries
 LIMIT 1;
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>查詢特定資料庫系統的資料型態Source:   不同資料型態能夠對應各自的運算符- 文字可以使用 `||` 運算符做連結- 數值可以使用 `+`, `-`, `*`, `/`, `%`<jupyter_code>sql_query = """
SELECT firstName || " " || lastName AS full_name
  FROM players;
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>數值運算符的計算順序1. 小括號2. 次方與根號3. 乘法、除法與餘數4. 加法與減法  例如計算身體質量指數（BMI）的公式\begin{equation}BMI = \frac{weight_{kg}}{height_m^{2}}\end{equation}<jupyter_code>sql_query = """
SELECT firstName,
       lastName,
       weightKilograms / (heightMeters*heightMeters) AS bmi
  FROM players
 LIMIT 10;
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>SQLite 的常用函式Source:   延續 `SELECT` 和 `FROM`  使用 `ORDER BY` 排序查詢結果- 預設遞增（`ASC`）排序，不需要指定- 遞減（`DESC`）排序要指定<jupyter_code>sql_query = """
SELECT personId,
       points
  FROM careerSummaries
 ORDER BY points;
"""
pd.read_sql(sql_query, conn_nba)
sql_query = """
SELECT personId,
       points
  FROM careerSummaries
 ORDER BY points DESC;
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>排序並不僅限於一個變數<jupyter_code>sql_query = """
SELECT firstName,
       lastName,
       dateOfBirthUTC
  FROM players
 ORDER BY firstName,
          dateOfBirthUTC DESC;
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>使用 `WHERE` 篩選觀測值<jupyter_code>sql_query = """
SELECT fullName
  FROM teams
 WHERE confName = 'East';
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>`=` 是完全相同，其他的比較運算符尚有- `!=`: 不等於- `>`, `>=`: 大於、大於等於- `<`, `<=`: 小於、小於等於- `BETWEEN`: 介於一個範圍- `IN`: 屬於集合中的值- `LIKE`: 模糊比對- `NOT`: 非<jupyter_code># 不等於
sql_query = """
SELECT fullName
  FROM teams
 WHERE confName != 'East';
"""
pd.read_sql(sql_query, conn_nba)
# 大於等於
sql_query = """
SELECT personId,
       ppg
  FROM careerSummaries
 WHERE ppg >= 25;
"""
pd.read_sql(sql_query, conn_nba)
# 介於一個範圍
sql_query = """
SELECT personId,
       ppg
  FROM careerSummaries
 WHERE ppg >= 20 AND
       ppg <= 25;
"""
pd.read_sql(sql_query, conn_nba)
# 屬於集合中的值
sql_query = """
SELECT fullName
  FROM teams
 WHERE divName IN ('Atlantic', 'Pacific');
"""
pd.read_sql(sql_query, conn_nba)
# 模糊比對 %
sql_query = """
SELECT firstName,
       lastName
  FROM players
 WHERE firstName LIKE 'L%';
"""
pd.read_sql(sql_query, conn_nba)
# 模糊比對 _
sql_query = """
SELECT firstName,
       lastName
  FROM players
 WHERE firstName LIKE 'L___';
"""
pd.read_sql(sql_query, conn_nba)
# 非
sql_query = """
SELECT fullName
  FROM teams
 WHERE divName NOT IN ('Atlantic', 'Pacific');
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>以 `AND` 與 `OR` 結合 `WHERE` 條件- 以 `AND` 交集兩個以上的條件- 以 `OR` 聯集兩個以上的條件<jupyter_code>sql_query = """
SELECT firstName,
       lastName
  FROM players
 WHERE firstName LIKE 'L%' AND
       lastName LIKE 'J%';
"""
pd.read_sql(sql_query, conn_nba)
sql_query = """
SELECT firstName,
       lastName
  FROM players
 WHERE firstName LIKE 'L%' OR
       lastName LIKE 'J%';
"""
pd.read_sql(sql_query, conn_nba)<jupyter_output><empty_output><jupyter_text>這是目前涵蓋的查詢保留字使用 SQL 語法時，保留字順序必須要遵守。```sqlSELECT DISTINCT CAST(column_name AS data_type) AS alias_name  FROM table_name WHERE conditions ORDER BY column_name DESC LIMIT n_obs;```  隨堂練習[基本查詢：隨堂練習](https://mybinder.org/v2/gh/yaojenkuo/introduction-to-sql/master?filepath=02-exercises.ipynb)  隨堂練習：以 `||`  運算符將 `presidential2020` 表格中 `county`、`town` 與 `village` 連結為一個新的欄位並命名為 `combined_key`，再以 `DISTINCT` 暸解台灣獨一的選舉區有哪些<jupyter_code>expected_output = pd.read_sql(etq('0201'), conn_twelection)
expected_output<jupyter_output><empty_output><jupyter_text>隨堂練習：計算 NBA 球員的身體質量指數（BMI）並由大到小排序，選擇 `firstName`、`lastName` 與 `bmi` 這三個變數<jupyter_code>expected_output = pd.read_sql(etq('0202'), conn_nba)
expected_output<jupyter_output><empty_output><jupyter_text>隨堂練習：使用 `WHERE` 將 `presidential2020` 中臺北市的資料篩選出來<jupyter_code>expected_output = pd.read_sql(etq('0203'), conn_twelection)
expected_output<jupyter_output><empty_output><jupyter_text>隨堂練習：使用 `WHERE` 將 `presidential2020` 表格中的六都（臺北市、新北市、桃園市、臺中市、臺南市與高雄市）的資料篩選出來<jupyter_code>expected_output = pd.read_sql(etq('0204'), conn_twelection)
expected_output<jupyter_output><empty_output><jupyter_text>隨堂練習：查詢 `careerSummaries` 生涯場均得分 `ppg` 超過 20 分的球員 ID `personId`、場均得分 `ppg`<jupyter_code>expected_output = pd.read_sql(etq('0205'), conn_nba)
expected_output<jupyter_output><empty_output><jupyter_text>隨堂練習：從 `careerSummaries` 中找出生涯助攻失誤比（assists/turnovers）最高的前 10 個球員 ID `personId`、助攻失誤比 `ast_to_ratio`\begin{equation}ast\_to\_ratio = \frac{assists}{turnovers}\end{equation}<jupyter_code>expected_output = pd.read_sql(etq('0206'), conn_nba)
expected_output<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13573788546255505, 'fraction_numerical': 0.01032488986784141, 'mean_word_length': 6.704135737009544, 'pattern_counts': {'":': 0, '<': 85, '<?xml version=': 0, '>': 90, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '197037', 'license': 'MIT', 'n_tokens_mistral': 4059, 'n_tokens_neox': 4216, 'n_words': 481, 'path': '02-retrieving-data.ipynb', 'repo_name': 'yaojenkuo/introduction-to-sql', 'size': '7265'}