# 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 语句。
- 一、数据科学的生命周期
- 二、数据生成
- 三、处理表格数据
- 四、数据清理
- 五、探索性数据分析
- 六、数据可视化
- Web 技术
- 超文本传输协议
- 处理文本
- python 字符串方法
- 正则表达式
- regex 和 python
- 关系数据库和 SQL
- 关系模型
- SQL
- SQL 连接
- 建模与估计
- 模型
- 损失函数
- 绝对损失和 Huber 损失
- 梯度下降与数值优化
- 使用程序最小化损失
- 梯度下降
- 凸性
- 随机梯度下降法
- 概率与泛化
- 随机变量
- 期望和方差
- 风险
- 线性模型
- 预测小费金额
- 用梯度下降拟合线性模型
- 多元线性回归
- 最小二乘-几何透视
- 线性回归案例研究
- 特征工程
- 沃尔玛数据集
- 预测冰淇淋评级
- 偏方差权衡
- 风险和损失最小化
- 模型偏差和方差
- 交叉验证
- 正规化
- 正则化直觉
- L2 正则化：岭回归
- L1 正则化：LASSO 回归
- 分类
- 概率回归
- Logistic 模型
- Logistic 模型的损失函数
- 使用逻辑回归
- 经验概率分布的近似
- 拟合 Logistic 模型
- 评估 Logistic 模型
- 多类分类
- 统计推断
- 假设检验和置信区间
- 置换检验
- 线性回归的自举（真系数的推断）
- 学生化自举
- P-HACKING
- 向量空间回顾
- 参考表
- Pandas
- Seaborn
- Matplotlib
- Scikit Learn