目录

• 实体关系图(ERD)
• 项目介绍
• 课程大纲
• 简介
  • 为什么数据分析师也用 SQL？
  • SQL 与 NoSQL
  • 为什么企业选择使用 SQL？
  • 为什么企业喜欢使用数据库
• 数据库如何存储数据
  • 特点
  • 数据库类型
    • SQL 数据库
    • 细微差别
• 语句(Statement)类型
  • 何为语句(Statement)
  • SELECT 语句
  • 规定查询格式
  • LIMIT语句
  • ORDER BY 语句
  • WHERE语句
  • 算术运算符
  • 逻辑运算符
  • LIKE语句
  • IN语句
  • NOT语句
  • AND和BETWEEN语句
  • OR语句
• 概括
  • 命令
  • 其他提示

正文

数据来源: Parch & Posey 造纸公司
数据详情: 销售的纸张有三种:

• 普通纸 - Regular
• 海报纸 - Poster
• 高光铜版纸 - Glossy

实体关系图(ERD)

实体关系图 (ERD) 英文名为Entity Relationship Diagram, 是查看数据库中数据的常用方式。下面是我们将用于 Parch & Posey 数据库的 ERD。每一个电子表格都会以一个表的形式呈现, 这些图可帮助你可视化正在分析的数据，包括:

• 表的名称。
• 每个表中的列。
• 表配合工作的方式。

ERD示例.png

最上方为每个表的名称.
下方的每行代表每个列名.
例如，region 表有两列: id 和 name，而 web_events 表有四列

image.png

注意事项
在 Parch & Posey 数据库中，共有五个表（基本上是 5 个电子表格）:

1. web_events
2. accounts
3. orders
4. sales_reps
5. region

将这些表格连接在一起的 "crow's foot" 表示法显示了一个表中的列与另一个表中的列之间的关联。在第一课中，你将学习使用 SQL 与单个表进行交互的基础知识。在下一节课中，你将进一步了解这些连接对于使用 SQL 和关系数据库的重要性。

练习: ERD基础知识

image.png

项目介绍

对于这些课程结束时的项目，将下载一个程序，在本地计算机上编写代码。然后，通过查询数据库，使用与一家音乐店铺相关联的数据来分析和回答商业问题。

课程大纲

此课程的三节课旨在介绍 SQL 的以下方面：

SQL 基础知识

• 了解 SQL 的工作原理
• 学习 SQL 语言的基础知识
• 学习如何编写代码
• 与在 Excel 中分析的表类似的表进行交互

SQL 连接

• 认识实体关系图 (ERD)
• 如何连接关系数据库中的多个表

SQL 聚合

• 能够汇总数据库中多个表中的数据。

简介

深入学习编写 SQL 查询之前，我们先来看看什么使 SQL 和利用 SQL 的数据库这么受欢迎。

我认为将 SQL 视为一种语言还是很重要的。因此，SQL 的最后一个字是语言。SQL 可用于我们在这节课中使用的数据库以外的任何方面。也就是说，SQL 最受欢迎的功能是与数据库交互。在这节课中，你可以将数据库看作一堆位于一个地方的 excel 电子表格。但并不是所有数据库都是放在一个地方的一堆 excel 电子表格，不过在这节课中，这个解释是合理的。

为什么数据分析师也用 SQL？

世界上许多数据都存储在数据库中,而世界上绝大多数数据库都是通过结构化查询语言(Structured Query Language)来访问的,而结构化语言通常称为SQL,读作/'es kju 'el/, 该语言自20世纪70年代就有了,如今它已成为最常用的数据和数据库访问方式.

SQL有各种各样的函数能够让用户浏览,操作和修改数据, 使用传统关系数据库与 SQL 交互有一些主要优点。

最明显的 5 个优点是：

• SQL 很容易理解。
• 传统的数据库允许我们直接访问数据。
• 传统的数据库可使我们审核和复制数据。
• SQL 是一个可一次分析多个表的很好工具。
• 相对于 Google Analytics 等仪表板工具，SQL 可使我们分析更复杂的问题。

SQL 与 NoSQL

你可能听说过 NoSQL，它表示 Not only SQL（不仅仅是 SQL）。使用 NoSQL 的数据库时，你编写的数据交互代码会与本节课所介绍的方式有所不同。NoSQL 更适用于基于网络数据的环境，而不太适用于我们现在要介绍的基于电子表格的数据分析。最常用的 NoSQL 语言之一是 MongoDB。Udacity 上有一个关于 MongoDB 的完整课程，你可以免费观看 此处，但这些不是这个程序的重点内容。

为什么企业选择使用 SQL？

以Twitter为例,每次写下一条推文,这条推文就需要存储到某个地方,这样才能让我所有的粉丝看到,Twitter存储了大量关于我推文的消息,例如时间,作者.如果我是在回复或是在转发,那么所有相关的推文链接也会被存储起来,这些信息会用于决定谁能够看到我的推文以及他们看到推文的时间,这就是数据库的功能, 数据库存储信息,以便之后访问,而SQL这门语言允许分析师及其他人访问这些信息.

image.png

为什么企业喜欢使用数据库

• 数据库有大量属性可以很好地 完成这个任务,最重要的是,数据库做了很多工作来检查数据完整性, 数据完整新能够确保被键入的数据符合条件且保持一致性,如下图.

image.png

• 可以快速访问数据 - SQL 可使我们从数据库中快速获取结果。 可以优化代码，快速获取结果
• 可以很容易共享数据 - 多个人可以访问存储在数据库中的数据，所有访问数据库的用户获得的数据都是一样。

数据库如何存储数据

特点

• 数据库表与Excel表类似, 但有些更严格的规则. 以数据库表为例, 该表有列组成, 每列必须有独一无二的列名或描述性列名.
• Excel中每个单元格都能有自己的数据类型. 但是数据库表中, 一列中的所有数据都必须有相同的类型, 将整个列认为是定量离散的或是某种字符串。这说明如果特定列中有一行字符串，那么整个列可能会更改为文本数据类型。

数据库类型

SQL 数据库

数据库的类型有很多，都用于不同的用途。在本课中，我们将使用 Postgres，这是一个流行的开源数据库，具有非常完整的分析函数库。

一些最受欢迎的数据库包括：:

1. MySQL
2. Access
3. Oracle
4. Microsoft SQL Server
5. Postgres

你也可以在其他编程框架中编写 SQL，如 Python、Scala 和 HaDoop。

细微差别

这些 SQL 数据库中的每一个可能在语法和可用函数上存在细微差异 -- 例如，MySQL 中没有像 Postgres 中类似的可用于修改日期的函数。你在 Postgres 中看到的大部分直接适用于在其他框架中和数据库环境中使用 SQL。如需了解存在的差异，可查看文档。大多数 SQL 环境都有很好的在线文档，通过快速的 Google 搜索便可轻松访问.

语句(Statement)类型

何为语句(Statement)

• 语句会告诉数据库你想对数据进行何种操作.
• SQL 语句是可以读取和处理数据的代码。但这不是一个真正的句子。
• SQL 不区分大小写 - 这意味着可以在代码中的任何位置写入大写和小写。
• 另外，还可以使用分号结束 SQL 语句，但某些 SQL 环境结尾时不需要分号。

例如:

1. CREATE TABLE 是一个在数据库中创建新表的语句。
2. DROP TABLE 是删除数据库中表的语句。

但是大多数数据分析工作并不需要创建以及删除数据, DROP 和 CREATE 语句实际上会更改数据库中的数据。在大多数公司，分析师没有权限使用这些类型的语句。这是一个不错的规定 - 原因是实际上更改数据库中的数据是一件需要很大权限的事情. 通常仅数据库管理员具有这个权限. 分析工作需要的是操作和浏览数据, 只是偶尔才会更新基础数据源.

SELECT 语句

SELECT 读取并显示数据。我们将这称为查询(Queries)。

两种用于每个查询的语句

1. SELECT用于提醒查询要返回哪些列。列命由逗号隔开, 最后一个列名后无需加逗号, 也可以使用*选取所有列

2. FROM用于提醒查询在哪个表中查询。注意，这个表中需要有列。

SELECT & FROM称为子句, 有些子句是非必须语句, 但是SELECT,FROM皆为必须项,

Q: 生成一张列表如下图, 记录Parch & Posey曾接下的所有订单, 该公司把订单记录在一张叫Orders(订单)的表里, 每笔订单都有唯一的ID, 可用于索引,还有一个时间戳, 记录了下订单的时间, 还包括每种纸的销售数量和所得营收.

需要查询的表.png

A: SELECT * FROM demo.orders

规定查询格式

大写
大写命令（SELECT、FROM），小写查询中的其他内容是常见做法。这使得查询更容易读取，这在编写更复杂的查询时更为重要。准备编写查询时，这是一个很好的习惯。

表和变量名中不需要空格
通常在列名中使用下划线，避免使用空格。 在 SQL 中使用空格有点麻烦。 在 Postgres 中，如果列或表名称中有空格，就需要使用双引号括住这些列/表名称（例如：FROM \"Table Name\"，而不是 FROM table_name）。在其他环境中，可能会使用方括号（例如：FROM [Table Name]）。

在查询中使用空格
SQL 查询忽略空格，因此可以根据需要在代码之间添加尽可能多的空格和空行，并且查询结果是相同的。我们来看下面这个查询

SELECT account_id FROM orders

等价于这个查询:

SELECT account_id
FROM orders

SQL 不区分大小写
如果你已经使用过其他语言编程，那么可能会熟悉编程语言，如果没有区分大小写键入正确的字符，那么会非常麻烦。 SQL 不区分大小写。 我们来看看下面的查询：

SELECT account_id FROM orders

和这个相同：

select account_id from orders

也和这个相同：

SeLeCt AcCoUnt_id FrOm oRdErS

但是，我会再次提醒你遵循上面讲述的完全大写命令的惯例，而将其他代码片段小写。

分号
根据 SQL 环境，查询结尾可能需要一个执行的分号。 这个"要求"在其他环境中比较灵活。我们认为在每个语句的末尾添加一个分号是最好的做法，如果环境能够一次显示多个结果，那么这样做还可以一次运行多个命令。

最好的做法：

SELECT account_id FROM orders;

因为，我们这里的环境不需要分号，你会看到没有分号的解决方案：

SELECT account_id FROM orders

LIMIT语句

大多数时候只是看一下数据的前几行, 以便了解自己真正关心哪些字段, 以及相对这些字段执行什么操作.

假设, 如果你想看一下离开Posey网站的流量, 那么首先就是弄清楚网站流量表里存储了什么数据, 如果想要查看表的前几行时，LIMIT 语句就能派上用场。这可比加载整个数据集要快得多。

LIMIT 命令始终是查询的最后一部分。下面的例子仅显示订单表的前 10 行和所有列：

SELECT *
FROM orders
LIMIT 10;

可以通过将 10 更改为任何其他数字来更改行数。

ORDER BY 语句

Q1: 假设你是Parch & Posey财务部的职员, 你想查看最新订单来确认发票已经发给了相关顾客, 此时ORDER BY 语句可以实现该操作. 可以使用ORDER BY按日期对订单进行排序.

image.png

ORDER BY 语句可使我们按任意行排序表。如果熟悉 Excel，这与使用过滤器进行排序相似。

ORDER BY 语句始终在 SELECT 和 FROM 语句之后，但位于 LIMIT 语句之前。 学习其他命令时，这些语句的顺序将更为重要。 如果使用 LIMIT 语句，它将始终显示在最后。

SELECT *
FROM demo.orders
ORDER BY occurred_at
LIMIT 10;

可以在 ORDER BY 语句中的列之后添加 DESC，然后按降序排序，因为默认是按升序排序的

SELECT *
FROM demo.orders
ORDER BY occurred_at DESC
LIMIT 10;

Q2: 假设你想按账户(account)对结果进行排序, 然后每个账户里,把订单按从大到小排序, 找出每个账户最大的一笔订单是什么?

你可以对多个列使用ORDER BY 从而实现该操作,排序将按列名写下的先后进行, 因此对于上述问题, 可以先给account_id 和 total_amt_usd进行排序, 然后对结果降序,那么最大值就会出现.

A:
SELECT account_id, total_amt_usd
FROM orders
ORDER BY account_id, total_amt_usd DESC

如果先对total_amt_usd进行降序, 在对account_id进行排序, 那么此时得到的结果的第一条值应该是最大金额数所对应的id,因此ORDER BY 后面的排序是按照顺序进行的.

SELECT account_id, total_amt_usd
FROM orders
ORDER BY total_amt_usd DESC, account_id

问题
1.编写一个查询，返回按从最新到最早排序的 订单 中的前 5 行，但需首先列出每个日期的最大 total_amt_usd。

SELECT *
FROM orders
ORDER BY total_amt_usd DESC, occurred_at DESC
LIMIT 5;

2.编写一个查询，返回按从最早到最新排序的 订单 中的前 10 行，但需首先列出每个日期的最小 total_amt_usd

SELECT *
FROM orders
ORDER BY total_amt_usd, occurred_at
LIMIT 10;

WHERE语句

假设你是Parch & Posey的客户经理, 想要了解客户最近的所有订单, 可以用WHERE 子句生成列表, 列出那位客户的所有订单.

WHERE子句能够让你用一些具体的标准来筛选一系列结果就像在Excel里用的筛选(filter)功能, 不过用WHERE 筛选功能,能回答更有意义的问题.

WHERE子句的顺序
WHERE子句位于FROM后, 但是在ORDER BY或LIMIT前.

作为客户经理, 你要写一个查询, 仅呈现高级客户的订单, 在这里以账户ID 4251来表示.

SELECT *
FROM demo.orders
WHERE account_id = 4251
ORDER BY occurred_at
LIMIT 1000
注意: 如果你写一个WHERE 子句,根据一列的值来筛选数据,就像我们在这里做的,结果只呈现满足条件的行, 背后的原理就是每一行都是一个数据点或观察数据, 该行所包含的所有信息是一个整体, 在这里,一行里的所有信息都与一笔纸张订单有关

WHERE 语句中使用的常用符号包括：

• >（大于）
• <（小于）
• >=（大于或等于）
• <=（小于或等于）
• =（等于）
• !=（不等于）

问题
编写一个查询
1.从订单表提取出大于或等于 1000 的 gloss_amt_usd 美元数额的前五行数据（包含所有列）

SELECT *
FROM orders
WHERE gloss_amt_usd >= 1000
LIMIT 5;

2.从订单表提取出小于 500 的 total_amt_usd美元数额的前十行数据（包含所有列)

SELECT *
FROM orders
WHERE total_amt_usd < 500
LIMIT 10;

你会注意到我们在使用这些 WHERE 语句时，不需要 ORDER BY，除非要实际整理数据。不必对数据进行排序，仍可继续执行条件。

WHERE 与非数字数据一起使用

比较运算符处理非值数据时, 等于和不等于形成了闭集,能让你挑选匹配或不匹配任意值的行.

如果你使用的运算符值为非值, 那你得用单引号（单引号或双引号都可以 - 如果原始文本中有引号，就一定要注意）把值括起来

Q: 过滤账户（accounts ）表格，从该表格中筛选出 Exxon Mobil 的 name、website 和 primary point of contact (primary_poc)。

SELECT name, website, primary_poc
FROM accounts
WHERE name = 'Exxon Mobil';

查询结果.png

算术运算符

假设我们要计算非标准纸张销售数量, 我们先来看看订单表中的每列的订单数量

image.png

海报纸和高光铜版纸为非标准纸, 因此为了达到目的我们需要将这两种纸张的订单量加在一起, 为非标准纸创建一列.

将现有的列组合,生成的新列称为派生列.

操作方法.png

结果.png

派生列可以由简单的算术或任意数量的高级运算得出, 我们可以把列名称换成更具描述性的名字, 要做到这一点, 我们可以生成派生列的那一行最后加上AS, 描述性名称不要用大写字母或空格比如该例子中可以使用nonstandard_qty(非标准纸张数量)

常见运算包括：

• *（乘法）
• +（加法）
• -（减法）
• /（除法）

注意运算顺序以及括号的运用.

逻辑运算符

逻辑运算符包括：

• LIKE
  可用于进行类似于使用 WHERE 和 = 的运算，但是这用于你可能不知道自己想准确查找哪些内容的情况。
• IN
  用于执行类似于使用 WHERE 和 = 的运算，但用于多个条件的情况
• NOT
  这与 IN 和 LIKE一起使用，用于选择 NOT LIKE 或 NOT IN 某个条件的所有行。
• AND & BETWEEN
  可用于组合所有组合条件必须为真的运算。
• OR
  可用于组合至少一个组合条件必须为真的运算。

LIKE语句

假设你是Parch&Posey的互联网营销经理, 你希望确定通过谷歌页面进来的网站流量.

image.png

如上图所示, 来自谷歌的引用页URL, 全都有相同的域名, 但往往还跟着一堆冗余的东西.

此时如果使用WHERE referrer_url = 'http://www.google.com',并不会得到结果.因此如果要写出一行筛选代码抓取所有来自谷歌的流量,而不受地址尾部冗余信息影响的话, 你可以使用LIKE运算符.

如果在给定列中, 有很多相似,但又有些不同的值, LIKE运算符就会很有用. LIKE函数需要使用通配符, 在这我们用的通配符是百分号%, ％ 匹配任何数量的字符，产生一组特定的字符或者遵循一组特定的字符, 记住，需要用单引号或双引号将传达给 LIKE 运算符的文本括住，因为这个字符串中的大小写字母不一样。 搜索 'T' 与搜索 't' 不同。

SELECT *
FROM demo.web_events_full
WHERE referrer_url LIKE '%google%';

1.使用 accounts (客户) 表查找所有以 'C' 开头公司名。

SELECT name
FROM accounts
WHERE name LIKE 'C%';

2.名称中包含字符串 'one' 的所有公司名。

SELECT name
FROM accounts
WHERE name LIKE '%one%';

3.所有以 's' 结尾的公司名。

SELECT name
FROM accounts
WHERE name LIKE '%s';

IN语句

假设你是Parch&Posey的销售经理, 你想看看若干重点账户的表现.比如你只想看沃尔玛和苹果公司账户的信息.

IN函数能让你根据若干可能值来筛选数据, 对于非值数据, 要用引号括起来, 对于值数据, 则可直接输入.比如,要实现如上问题,语句如下.

SELECT *
FROM demo.accounts WHERE name IN ('Walmart', 'Apple')

其中在括号内可以放多个值, 但在不同的值之间, 你都得输入一个逗号.

1.使用 客户 表查找 Walmart、Target 和 Nordstrom 的name (客户名称), primary_poc (主要零售店), and sales_rep_id (销售代表 id)。

SELECT name, primary_poc, sales_rep_id
FROM accounts
WHERE name IN ('Walmart','Target','Nordstrom');

2.使用 web_events 表查找有关通过 organic 或 adwords 联系的所有个人信息。

SELECT *
FROM web_events
WHERE channel IN ('organic','adwords');

NOT语句

假设你是Parch&Posey的销售经理, 你在考虑把两位最出色得销售代表提升为管理人员, 但你需要弄清楚, 如何将他们所有得账户, 分摊给其他销售代表.

要实现上述问题, 首先看看Parch&Posey共有多少账户, 现在来看看这两位高业绩销售代表经手得账户.

image.png

要决定把这些账户指派给谁, 还需要考虑其他销售代表手头正在处理得账户, 也就是说你要查看所有, 现在这个查询里没有列出得账户, NOT运算符可以实现上述过程.NOT 运算符是一个非常有用的运算符，用于与之前介绍的两个运算符 IN 和 LIKE 一起运算。通过指定 NOT LIKE 或 NOT IN，我们可以查找到所有不符合特定条件的行。

示例1
SELECT sales_rep_id, name
FROM demo.accounts
WHERE sales_rep_id NOT IN (321500,321570) ORDER BY sales_rep_id

示例2
SELECT *
FROM demo.web_events_full
WHERE referrer_url NOT LIKE %google%

AND和BETWEEN语句

假设你是Parch&Posey的销售经理, 你想知道哪些客户不久前买过纸张, 而且现在又差不多到时间采购了, 假设现在是2017年1月,你可能想查看那些之前3到9个月采购过纸张的客户, 也就是在2016年的4月到10月之间.

要实现以上操作, 可以使用AND. AND 运算符用于 WHERE 语句中，用于一次考虑多个逻辑子句。 使用 AND 连接一个新的语句时，需要指定你感兴趣的列。可以同时连接尽可能多的考虑语句。

SELECT *
FROM orders
WHERE occurred_at >= '2016-04-01' AND occurred_at <= '2016-10-01'
ORDER BY occurred_at DESC;

需要注意的是AND运算符能运行两个完整的独立的逻辑语句, 虽然它们都在同一列名下运行, 但我们不能只输入WHERE occurred_at >= '2016-04-01 'AND <='2016-10-01', 因为<='2016-10-01'并不是一个逻辑语句,无法评估真假, 查询结果会报错.

BETWEEN 运算符
有时使用 BETWEEN 比使用 AND 使语句更清楚一些。特别是在 AND 语句的不同部分使用相同的列时，就可以使语句比较清晰。 在上一个视频中，我们可能已经使用了BETWEEN。

而不是编写：

WHERE column >= 6 AND column <= 10
编写成以下这样，或许会更好:

WHERE column BETWEEN 6 AND 10

1.编写一个查询，返回所有订单，其中 standard_qty 超过 1000，poster_qty 是 0，gloss_qty 也是 0。

SELECT *
FROM orders
WHERE standard_qty > 1000 AND poster_qty = 0 AND gloss_qty = 0;

2.使用客户表查找所有不以 'C' 开始但以 's' 结尾的公司名。

SELECT name
FROM accounts
WHERE name NOT LIKE 'C%' AND name LIKE '%s';

3.使用 web_events 表查找通过 organic 或 adwords 联系，并在 2016 年的任何时间开通帐户的个人全部信息，并按照从最新到最旧的顺序排列。

SELECT *
FROM web_events
WHERE channel IN ('organic','adwords') AND occurred_at BETWEEN '2016-01-01' AND '2017-01-01'
ORDER BY occurred_at DESC;

OR语句

假设你是Parch&Posey的销售经理, 你想出了另一个好办法来提高营收, 向既有客户销售新的纸张种类, 为了找出最佳的潜在客户, 你想查看哪些没订过某种纸的既有客户, 不同类型纸张的订单, 在订单表里有三个不同的列标出, 所以你需要写一个查询, 对这三列进行逻辑比较.

image.png

为了实现上述操作, 你可以使用OR, OR是SQL里的逻辑运算符, 可用于选择满足两个条件之一的行. 与 AND 运算符类似，OR 运算符可以组合多个语句。 使用 OR 连接新的语句时，需要指定你感兴趣的列。可以同时连接尽可能多的考虑语句。

SELECT account_id, occurred_at, standard_qty, gloss_qty, poster_qty
FROM orders
WHERE standard_qty = 0 OR gloss_qty = 0 OR poster_qty = 0

算术运算符（+、*、-、/ ) 可以使用 OR 运算符将 LIKE、IN、NOT、AND 和 BETWEEN逻辑连接到一起。将多个运算组合到一起时，可能经常需要使用括号来确保我们要执行的逻辑能得到正确执行。

如果我要找出没有同时包含三种纸张的订单, 最佳潜在客户可能是那些既没有订过某种纸张, 又是最近才下订单的顾客.

保留之前查询的筛选条件, 再查询2016-10-01之后的订单, 查询如下

SELECT account_id, occurred_at, standard_qty, gloss_qty, poster_qty
FROM orders
WHERE (standard_qty = 0 OR gloss_qty = 0 OR poster_qty = 0)
AND occurred_at >= '2016-10-01'

1.查找 订单 (orders) id 的列表，其中 gloss_qty 或 poster_qty 大于 4000。只在结果表中包含 id 字段。

SELECT id
FROM orders
WHERE gloss_qty > 4000 OR poster_qty > 4000;

2.编写一个查询，返回订单 (orders) 的列表，其中标准数量 (standard_qty)为零，光泽度 (gloss_qty) 或海报数量 (poster_qty)超过 1000。

SELECT *
FROM orders
WHERE standard_qty = 0 AND (gloss_qty > 1000 OR poster_qty > 1000);

3.查找以 'C' 或 'W' 开头的所有公司名 (company names)，主要联系人 (primary contact) 包含 'ana' 或 'Ana'，但不包含 'eana'。

SELECT *
FROM accounts
WHERE (name LIKE 'C%' OR name LIKE 'W%')
AND ((primary_poc LIKE '%ana%' OR primary_poc LIKE '%Ana%')
AND primary_poc NOT LIKE '%eana%')

概括

命令

其他提示

尽管 SQL 不区分大小写（它不在乎你将语句全部大写还是小写），但我们讨论了一些最佳实践。关键词的顺序非常重要！