SQL · UCB DS100 數據科學的原理與技巧

# SQL > 原文：[https://www.textbook.ds100.org/ch/09/sql_basics.html](https://www.textbook.ds100.org/ch/09/sql_basics.html) ``` # HIDDEN # Clear previously defined variables %reset -f # Set directory for data loading to work properly import os os.chdir(os.path.expanduser('~/notebooks/09')) ``` ``` # HIDDEN import warnings # Ignore numpy dtype warnings. These warnings are caused by an interaction # between numpy and Cython and can be safely ignored. # Reference: https://stackoverflow.com/a/40846742 warnings.filterwarnings("ignore", message="numpy.dtype size changed") warnings.filterwarnings("ignore", message="numpy.ufunc size changed") import numpy as np import matplotlib.pyplot as plt import pandas as pd import seaborn as sns %matplotlib inline import ipywidgets as widgets from ipywidgets import interact, interactive, fixed, interact_manual import nbinteract as nbi sns.set() sns.set_context('talk') np.set_printoptions(threshold=20, precision=2, suppress=True) pd.options.display.max_rows = 7 pd.options.display.max_columns = 8 pd.set_option('precision', 2) # This option stops scientific notation for pandas # pd.set_option('display.float_format', '{:.2f}'.format) ``` ``` # HIDDEN # Creating a table sql_expr = """ CREATE TABLE prices( retailer TEXT, product TEXT, price FLOAT); """ result = sqlite_engine.execute(sql_expr) ``` ``` # HIDDEN # Inserting records into the table sql_expr = """ INSERT INTO prices VALUES ('Best Buy', 'Galaxy S9', 719.00), ('Best Buy', 'iPod', 200.00), ('Amazon', 'iPad', 450.00), ('Amazon', 'Battery pack', 24.87), ('Amazon', 'Chromebook', 249.99), ('Target', 'iPod', 215.00), ('Target', 'Surface Pro', 799.00), ('Target', 'Google Pixel 2', 659.00), ('Walmart', 'Chromebook', 238.79); """ result = sqlite_engine.execute(sql_expr) ``` ``` # HIDDEN import pandas as pd prices = pd.DataFrame([['Best Buy', 'Galaxy S9', 719.00], ['Best Buy', 'iPod', 200.00], ['Amazon', 'iPad', 450.00], ['Amazon', 'Battery pack', 24.87], ['Amazon', 'Chromebook', 249.99], ['Target', 'iPod', 215.00], ['Target', 'Surface Pro', 799.00], ['Target', 'Google Pixel 2', 659.00], ['Walmart', 'Chromebook', 238.79]], columns=['retailer', 'product', 'price']) ``` **sql**（結構化查詢語言）是一種編程語言，它具有對關系數據庫管理系統（RDBMS）中存儲的數據進行定義、邏輯組織、操作和執行計算的操作。 SQL 是一種聲明性語言。這意味著用戶只需要指定他們想要的 _ 數據類型，而不需要指定 _ 如何獲取它。下面是一個示例，其中一個必要的示例用于比較：__ * **聲明性**：從表“a”計算列“x”和“y”，其中“y”中的值大于 100.00。 * **命令式**：對于表“a”中的每個記錄，檢查記錄是否包含大于 100 的“y”值。如果是，則將記錄的“x”和“y”屬性存儲在新表中。返回新表。在本章中，我們將以 python 字符串的形式編寫 SQL 查詢，然后使用`pandas`執行 SQL 查詢并將結果讀取到`pandas`數據幀中。在我們介紹 SQL 語法的基礎知識時，為了便于比較，我們偶爾還會顯示`pandas`等價物。 ### 通過`pandas`[?](#Executing-SQL-Queries-through-pandas)執行 SQL 查詢要從 python 執行 SQL 查詢，我們將使用[sqlachemy](http://docs.sqlalchemy.org/en/latest/core/tutorial.html)庫連接到數據庫。然后我們可以使用`pandas`函數[pd.read_sql](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html)通過這個連接執行 SQL 查詢。 ``` import sqlalchemy # pd.read_sql takes in a parameter for a SQLite engine, which we create below sqlite_uri = "sqlite:///sql_basics.db" sqlite_engine = sqlalchemy.create_engine(sqlite_uri) ``` 此數據庫包含一個關系：`prices`。為了顯示關系，我們運行一個 SQL 查詢。調用`read_sql`將在 RDBMS 上執行 SQL 查詢，然后在`pandas`數據幀中返回結果。 ``` sql_expr = """ SELECT * FROM prices """ pd.read_sql(sql_expr, sqlite_engine) ``` | | 零售商 | 產品 | 價格 | | --- | --- | --- | --- | | 零 | 百思買 | 銀河 S9 | 七百一十九 | | --- | --- | --- | --- | | 1 個 | Best Buy | iPod | 二百 | | --- | --- | --- | --- | | 二 | 亞馬遜 | iPad | 四百五十 | | --- | --- | --- | --- | | 三 | Amazon | 電池組 | 二十四點八七 | | --- | --- | --- | --- | | 四 | Amazon | 彩色電子書 | 二百四十九點九九 | | --- | --- | --- | --- | | 5 個 | 目標 | iPod | 二百一十五 | | --- | --- | --- | --- | | 六 | Target | 微軟 Surface Pro 平板電腦 | 七百九十九 | | --- | --- | --- | --- | | 七 | Target | 谷歌像素 2 | 六百五十九 | | --- | --- | --- | --- | | 8 個 | 沃爾瑪 | Chromebook | 二百三十八點七九 | | --- | --- | --- | --- | 在本節后面，我們將比較 SQL 查詢和`pandas`方法調用，因此我們在`pandas`中創建了相同的數據幀。 ``` prices ``` | | retailer | product | price | | --- | --- | --- | --- | | 0 | Best Buy | Galaxy S9 | 719.00 | | --- | --- | --- | --- | | 1 | Best Buy | iPod | 200.00 | | --- | --- | --- | --- | | 2 | Amazon | iPad | 450.00 | | --- | --- | --- | --- | | 3 | Amazon | Battery pack | 24.87 | | --- | --- | --- | --- | | 4 | Amazon | Chromebook | 249.99 | | --- | --- | --- | --- | | 5 | Target | iPod | 215.00 | | --- | --- | --- | --- | | 6 | Target | Surface Pro | 799.00 | | --- | --- | --- | --- | | 7 | Target | Google Pixel 2 | 659.00 | | --- | --- | --- | --- | | 8 | Walmart | Chromebook | 238.79 | | --- | --- | --- | --- | ## SQL 句法[?](#SQL-Syntax) 所有 SQL 查詢都采用以下常規形式： ``` SELECT [DISTINCT] <column expression list> FROM <relation> [WHERE <predicate>] [GROUP BY <column list>] [HAVING <predicate>] [ORDER BY <column list>] [LIMIT <number>] ``` 注意： 1. **方括號中的所有內容都是可選的。**有效的 SQL 查詢只需要`SELECT`和`FROM`語句。 2. **SQL 語法通常用大寫字母編寫。**雖然不需要大寫，但通常使用大寫字母編寫 SQL 語法。它還可以幫助您直觀地構造查詢，以供其他人閱讀。 3. `FROM`查詢塊可以引用一個或多個表，盡管在本節中，為了簡單起見，我們一次只查看一個表。 ### 從[?](#SELECT-and-FROM)中選擇和 SQL 查詢中的兩個強制語句是： * `SELECT`指示要查看的列。 * `FROM`指示從中選擇這些列的表。要顯示整個`prices`表，我們運行： ``` sql_expr = """ SELECT * FROM prices """ pd.read_sql(sql_expr, sqlite_engine) ``` | | retailer | product | price | | --- | --- | --- | --- | | 0 | Best Buy | Galaxy S9 | 719.00 | | --- | --- | --- | --- | | 1 | Best Buy | iPod | 200.00 | | --- | --- | --- | --- | | 2 | Amazon | iPad | 450.00 | | --- | --- | --- | --- | | 3 | Amazon | Battery pack | 24.87 | | --- | --- | --- | --- | | 4 | Amazon | Chromebook | 249.99 | | --- | --- | --- | --- | | 5 | Target | iPod | 215.00 | | --- | --- | --- | --- | | 6 | Target | Surface Pro | 799.00 | | --- | --- | --- | --- | | 7 | Target | Google Pixel 2 | 659.00 | | --- | --- | --- | --- | | 8 | Walmart | Chromebook | 238.79 | | --- | --- | --- | --- | `SELECT *`返回原始關系中的每一列。為了只顯示`prices`中表示的零售商，我們將`retailer`列添加到`SELECT`語句中。 ``` sql_expr = """ SELECT retailer FROM prices """ pd.read_sql(sql_expr, sqlite_engine) ``` | | retailer | | --- | --- | | 0 | Best Buy | | --- | --- | | 1 | Best Buy | | --- | --- | | 2 | Amazon | | --- | --- | | 3 | Amazon | | --- | --- | | 4 | Amazon | | --- | --- | | 5 | Target | | --- | --- | | 6 | Target | | --- | --- | | 7 | Target | | --- | --- | | 8 | Walmart | | --- | --- | 如果我們想要一個唯一零售商的列表，我們可以調用`DISTINCT`函數來省略重復的值。 ``` sql_expr = """ SELECT DISTINCT(retailer) FROM prices """ pd.read_sql(sql_expr, sqlite_engine) ``` | | retailer | | --- | --- | | 0 | Best Buy | | --- | --- | | 1 | Amazon | | --- | --- | | 2 | Target | | --- | --- | | 3 | Walmart | | --- | --- | 這相當于以下`pandas`代碼的功能： ``` prices['retailer'].unique() ``` ``` array(['Best Buy', 'Amazon', 'Target', 'Walmart'], dtype=object) ``` 每個 RDBMS 都有自己的函數集，可以應用于`SELECT`列表中的屬性，例如比較運算符、數學函數和運算符，以及字符串函數和運算符。在數據 100 中，我們使用 PostgreSQL，這是一種成熟的 RDBMS，有數百個這樣的函數。完整列表可在此處[找到](https://www.postgresql.org/docs/9.2/static/functions.html)。請記住，每個 RDBMS 都有一組不同的函數可用于`SELECT`。以下代碼將所有零售商名稱轉換為大寫，并將產品價格減半。 ``` sql_expr = """ SELECT UPPER(retailer) AS retailer_caps, product, price / 2 AS half_price FROM prices """ pd.read_sql(sql_expr, sqlite_engine) ``` | | 零售商上限 | product | 半價 | | --- | --- | --- | --- | | 0 | 百思買 | Galaxy S9 | 359.500 美元 | | --- | --- | --- | --- | | 1 | BEST BUY | iPod | 10 萬 | | --- | --- | --- | --- | | 2 | 亞馬遜 | iPad | 225.000 美元 | | --- | --- | --- | --- | | 3 | AMAZON | Battery pack | 十二點四三五 | | --- | --- | --- | --- | | 4 | AMAZON | Chromebook | 124.995 年 | | --- | --- | --- | --- | | 5 | 目標 | iPod | 107.500 美元 | | --- | --- | --- | --- | | 6 | TARGET | Surface Pro | 399.500 美元 | | --- | --- | --- | --- | | 7 | TARGET | Google Pixel 2 | 329.500 美元 | | --- | --- | --- | --- | | 8 | 沃爾瑪 | Chromebook | 119.395 條 | | --- | --- | --- | --- | 請注意，我們可以用**別名**將列（指定另一個名稱）與`AS`一起使用，以便這些列在輸出表中以這個新名稱出現。這不會修改源關系中列的名稱。 ### 式中[?](#WHERE) `WHERE`子句允許我們為返回的數據指定某些約束；這些約束通常被稱為**謂詞**。例如，只檢索低于 500 美元的小工具： ``` sql_expr = """ SELECT * FROM prices WHERE price < 500 """ pd.read_sql(sql_expr, sqlite_engine) ``` | | retailer | product | price | | --- | --- | --- | --- | | 0 | Best Buy | iPod | 200.00 | | --- | --- | --- | --- | | 1 | Amazon | iPad | 450.00 | | --- | --- | --- | --- | | 2 | Amazon | Battery pack | 24.87 | | --- | --- | --- | --- | | 3 | Amazon | Chromebook | 249.99 | | --- | --- | --- | --- | | 4 | Target | iPod | 215.00 | | --- | --- | --- | --- | | 5 | Walmart | Chromebook | 238.79 | | --- | --- | --- | --- | 我們還可以使用操作符`AND`、`OR`和`NOT`進一步約束我們的 SQL 查詢。為了在亞馬遜上找到一個不帶電池包的商品，價格低于 300 美元，我們寫下： ``` sql_expr = """ SELECT * FROM prices WHERE retailer = 'Amazon' AND NOT product = 'Battery pack' AND price < 300 """ pd.read_sql(sql_expr, sqlite_engine) ``` | | retailer | product | price | | --- | --- | --- | --- | | 0 | Amazon | Chromebook | 249.99 | | --- | --- | --- | --- | `pandas`中的等效操作為： ``` prices[(prices['retailer'] == 'Amazon') & ~(prices['product'] == 'Battery pack') & (prices['price'] <= 300)] ``` | | retailer | product | price | | --- | --- | --- | --- | | 4 | Amazon | Chromebook | 249.99 | | --- | --- | --- | --- | 有一個細微的區別值得注意：SQL 查詢中 Chromebook 的索引是 0，而數據幀中相應的索引是 4。這是因為 SQL 查詢總是返回一個索引從 0 開始計數的新表，而`pandas`將數據幀`prices`的一部分子集并返回原始索引。我們可以使用[pd.dataframe.reset_index](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html)重置`pandas`中的索引。 ### 聚合函數[?](#Aggregate-Functions) 到目前為止，我們只處理表中現有行的數據；也就是說，我們返回的所有表都是表中找到的條目的一個子集。但是為了進行數據分析，我們需要計算數據的聚合值。在 SQL 中，這些函數稱為**聚合函數**。如果我們想找到`prices`關系中所有小工具的平均價格： ``` sql_expr = """ SELECT AVG(price) AS avg_price FROM prices """ pd.read_sql(sql_expr, sqlite_engine) ``` | | 平均價格 | | --- | --- | | 0 | 395.072222 個 | | --- | --- | 相當于，in`pandas`： ``` prices['price'].mean() ``` ``` 395.0722222222222 ``` postgresql 聚合函數的完整列表可以在這里找到[。盡管我們在數據 100 中使用 PostgreSQL 作為 SQL 的主要版本，但請記住，SQL 還有許多其他變體（MySQL、SQLite 等），它們使用不同的函數名，并具有不同的可用功能。](https://www.postgresql.org/docs/9.2/static/functions.html) ### 分組依據并具有[?](#GROUP-BY-and-HAVING) 使用聚合函數，我們可以執行更復雜的 SQL 查詢。為了對更細粒度的聚合數據進行操作，我們可以使用以下兩個子句： * `GROUP BY`獲取列列表，并將表分組，如`pandas`中的[pd.dataframe.groupby](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html)函數。 * `HAVING`在功能上與`WHERE`相似，但僅用于將謂詞應用于聚合數據。（請注意，為了使用`HAVING`，它前面必須有一個`GROUP BY`子句。） **重要**：使用`GROUP BY`時，`SELECT`子句中的所有列都必須在`GROUP BY`子句中列出或應用聚合函數。我們可以使用這些聲明來找到每個零售商的最高價格。 ``` sql_expr = """ SELECT retailer, MAX(price) as max_price FROM prices GROUP BY retailer """ pd.read_sql(sql_expr, sqlite_engine) ``` | | retailer | 最高價格 | | --- | --- | --- | | 0 | Amazon | 450.00 | | --- | --- | --- | | 1 | Best Buy | 719.00 | | --- | --- | --- | | 2 | Target | 799.00 | | --- | --- | --- | | 3 | Walmart | 238.79 | | --- | --- | --- | 比如說，我們有一個品味很高的客戶，只想找到售價超過 700 美元的零售商。注意，我們必須使用`HAVING`定義聚合列上的謂詞；我們不能使用`WHERE`篩選聚合列。為了計算滿足我們需求的零售商列表和附帶價格，我們運行： ``` sql_expr = """ SELECT retailer, MAX(price) as max_price FROM prices GROUP BY retailer HAVING max_price > 700 """ pd.read_sql(sql_expr, sqlite_engine) ``` | | retailer | max_price | | --- | --- | --- | | 0 | Best Buy | 七百一十九 | | --- | --- | --- | | 1 | Target | 七百九十九 | | --- | --- | --- | 為了進行比較，我們在`pandas`中重新創建了相同的表： ``` max_prices = prices.groupby('retailer').max() max_prices.loc[max_prices['price'] > 700, ['price']] ``` | | price | | --- | --- | | retailer | | | --- | --- | | 百思買 | 719.0 | | --- | --- | | 目標 | 799.0 | | --- | --- | ### 訂貨人和限額[?](#ORDER-BY-and-LIMIT) 這些條款允許我們控制數據的顯示： * `ORDER BY`讓我們按照列值的字典順序顯示數據。默認情況下，order by 使用升序（`ASC`），但我們可以使用`DESC`指定降序。 * `LIMIT`控制顯示多少元組。讓我們在`prices`表中顯示三個最便宜的項目： ``` sql_expr = """ SELECT * FROM prices ORDER BY price ASC LIMIT 3 """ pd.read_sql(sql_expr, sqlite_engine) ``` | | retailer | product | price | | --- | --- | --- | --- | | 0 | Amazon | Battery pack | 24.87 | | --- | --- | --- | --- | | 1 | Best Buy | iPod | 200.00 | | --- | --- | --- | --- | | 2 | Target | iPod | 215.00 | | --- | --- | --- | --- | 注意，我們不必包含`ASC`關鍵字，因為默認情況下`ORDER BY`按升序返回數據。用于比較，在`pandas`中： ``` prices.sort_values('price').head(3) ``` | | retailer | product | price | | --- | --- | --- | --- | | 3 | Amazon | Battery pack | 24.87 | | --- | --- | --- | --- | | 1 | Best Buy | iPod | 200.00 | | --- | --- | --- | --- | | 5 | Target | iPod | 215.00 | | --- | --- | --- | --- | （我們再次看到，索引在`pandas`數據幀中不正常。和以前一樣，`pandas`在數據幀`prices`上返回一個視圖，而 SQL 在每次執行查詢時都顯示一個新表。） ### 概念性 SQL 評估 SQL 查詢中的子句按特定順序執行。不幸的是，此順序與在 SQL 查詢中寫入子句的順序不同。從最初執行到最后： 1. `FROM`：一個或多個源表 2. 【HTG0】：申請選擇資格（刪除行） 3. 【HTG0】：分組和匯總 4. `HAVING`：消除組 5. `SELECT`：選擇列 **關于`WHERE`對`HAVING`**的說明：由于在應用`GROUP BY`之前處理了`WHERE`子句，因此`WHERE`子句不能使用聚合值。要基于聚合值定義謂詞，必須使用`HAVING`子句。 ## 摘要[?](#Summary) 我們介紹了 SQL 語法和使用關系數據庫管理系統進行數據分析所需的最重要的 SQL 語句。