数据库原理与SQL基础

实体关系图

实体关系图 (entity relationship diagram，ERD) 是查看数据库中数据的常用方式。下面是我们将用于 Parch & Posey （虚拟公司）数据库的 ERD。这些图可帮助你可视化正在分析的数据，包括:

1. 表的名称。
2. 每个表中的列。
3. 表配合工作的方式。

你可以将下面的每个框看作一个电子表格。

image

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

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

你可以将每个表视为一个单独的电子表格。然后将每个电子表格中的列放在表名下面。例如，region 表有两列: id 和 name，而 web_events 表有四列。

image.png

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

关于 SQL 数据库中所存储数据的几个要点：

1. 数据库中的数据存储在类似于 Excel 电子表格的表中。
   大多数情况下，可以将数据库视为一堆 Excel 电子表格。每个电子表格都有行和列。每行保存有关交易、个人、公司等的数据。而每列所保存的数据与你关心的某一特定行相关，如名称、位置、唯一身份等。

2. 同一列中的所有数据必须符合数据类型。
   将整个列认为是定量离散的或是某种字符串。这说明如果特定列中有一行字符串，那么整个列可能会更改为文本数据类型。 如果想使用此列进行数学计算，这可能会非常糟糕！

3. 列类型一致是快速使用数据库的主要原因之一。
   数据库通常会存储海量数据。因此，知道这些列都是相同类型的数据意味着可快速从数据库获取数据。

数据库类型

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 搜索便可轻松访问。

这里 的文章比较了三种最常见的 SQL 类型：SQLite、PostgreSQL 和 MySQL。尽管你在Lesson中使用的是 PostgreSQL，但在项目中你要使用 SQLite。

一旦你学会了如何在一个环境中编写 SQL，这些基本大都是可以转移到其他环境的。

语句

SQL 的关键是理解语句。SQL 语句是可以读取和处理数据的代码。但这不是一个真正的句子。 SQL 不区分大小写 - 这意味着可以在代码中的任何位置写入大写和小写。另外，还可以使用 分号 结束 SQL 语句，但某些 SQL 环境结尾时不需要分号。

常用语句包括：

1. CREATE TABLE 是一个在数据库中创建新表的语句。
2. DROP TABLE 是删除数据库中表的语句。
3. SELECT 读取并显示数据。我们将这称为查询。

SELECT 语句是分析师使用的通用语句，被称为查询。 DROP 和 CREATE 语句实际上会更改数据库中的数据。在大多数公司，分析师没有权限使用这些类型的语句。这是一个不错的规定 - 原因是实际上更改数据库中的数据是一件需要很大权限的事情。通常仅数据库管理员具有这个权限。

SELECT 用于提醒查询要返回哪些列。

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

SQL 语句 SELECT 和 FROM

Parch & Posey 数据库中的表存储在下面方框的后台。你会注意到 SCHEMA 图表 下的左侧列表（需要先点击刷新按钮）下面，是之前在 ERD 中显示的表列表。 与在大多数其他数据库环境中一样编写查询并运行，然后使用下表查看结果。在这节课中，我们一次只能访问一个表，但在后面的课程中，我们将增加表的数量并在各个表之间进行聚合。

image.png

开始时，可以试着运行上一个视频中看到的查询！ 可以在下面的环境中练习你自己的 SQL 代码。在左侧面板可以看到我们之前在 ERD 中看到的表。在右侧面板可以编写 SQL 代码，可以单击 EVALUATE 按钮就可以运行查询。这可能需要一会儿才能运行。

HISTORY 菜单将显示你以前运行的查询。 可以通过 MENU 从左侧面板删除 SCHEMA 和重置数据库。

SELECT *
FROM orders;

你会注意到 Derek 使用一个 demo 表（他在以后的课程中还会继续采用这种方式），但是你应该使用完全如左侧面板所示的表名来编写自己的查询。 我们将来还会在 Derek 所教授的课程中看到这些（删除了 demo）表格。

image.png

编写的每个查询至少要有两个部分：SELECT 和 FROM。 SELECT 语句用于放置要显示数据的列。FROM 语句用于放置要从中提取数据的表。

规定查询格式

大写

你可能已经注意到，我们大写了 SELECT 和 FROM，而将表和列名称小写。这是一个常见的格式惯例。大写命令（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

SQL 语句 LIMIT

如果想要查看表的前几行时，LIMIT 语句就能派上用场。这可比加载整个数据集要快得多。

LIMIT 命令始终是查询的最后一部分。下面的例子仅显示 orders 表的前 10 行和所有列，OFFSET 表示从第几行开始：

SELECT *
FROM orders
LIMIT 10 OFFSET 5;

SQL 语句 ORDER BY

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

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

提示

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

示例

1. 编写查询，返回 orders 表的先下单的前 10 个订单。包含id、occurred_at 和 total_amt_usd。

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

2. 编写一个查询，返回 orders 表里total_amt_usd 最高的5个 订单。包括id、account_id 和 total_amt_usd。

SELECT id, account_id, total_amt_usd
FROM orders
ORDER BY total_amt_usd DESC 
LIMIT 5;

3. 编写一个查询，基于 total ，返回orders 表里的前 20 个 订单。包括id、account_id 和total

SELECT id, account_id, total
FROM orders
ORDER BY total
LIMIT 20;

我们可以一次 ORDER BY 多列。这个语句可以按照从左至右列出的列进行排序。 我们也可以也可以使用 DESC 来翻转排序。

示例

1. 查询 orders表格，按照订单日期的从新到旧降序排列，同时每个日期下的订单按照total_amt_usd降序排列，显示前5行。

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

2. 查询orders表格，按照订单日期的从旧到新升序排列，同时每个日期下的订单按照total_amt_usd升序排列，显示前10行。

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

SQL 语句 WHERE

我们可以基于必须满足的条件，使用 WHERE 语句来为表格创建子集。下面的视频介绍了如何使用这个语句，在接下来的课程中，我们将学习一些与 WHERE 语句一起使用的常见运算符。
WHERE 语句在 FROM 后，ORDER BY 和 LIMIT 前。
WHERE 语句中使用的常用符号包括：

1. >（大于）
2. <（小于）
3. >=（大于或等于）
4. <=（小于或等于）
5. =（等于）
6. !=（不等于）

示例

1. 从 orders 表提取出gloss_amt_usd大于或等于 1000 美元的前五行数据（包含所有列）。

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

2. 从 orders 表提取出total_amt_usd小于 500 美元的前十行数据（包含所有列）。

SELECT *
FROM orders
WHERE total_amt_usd < 500
LIMIT 10;

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

WHERE 语句也可以与非数字数据一起使用。我们可以使用 = 和 ！= 运算符。 还需要确保在文本数据中使用单引号，而不是双引号（如果原始文本中有引号，就一定要注意）。

通常将 WHERE 与非数字数据字段一起使用时，我们会使用LIKE、NOT 或 IN 运算符。

LIKE 如果操作数匹配某个模式，则值为TRUE。
LIKE 通常与 %等通配符配合使用，例如LOVING%可以表示LOVING、LOVING you、LOVING？
NOT 对其他布尔运算符的值取反。
IN 如果操作数与一个表达式列表中的某个相等，则值为TRUE。

当NOT 、 AND、OR 出现在同一表达式中，优先级为NOT 、 AND、OR。
例如

3>5 OR 6>3 AND NOT 6>4 = FALSE

1. NOT 6>4 = FALSE
2. 6>3 AND FALSE = FALSE
3. 3>5 OR FALSE = FALSE

示例

1. 从 accounts 表格中筛选出 Exxon Mobil 的 name（客户名称），同时包含website 和 primary point of contact (primary_poc) 等数据。

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

SQL 语句 GROUP BY 和 HAVING

GROUP BY 通常与聚合函数如SUM、AVE等一起使用，每一组如“男”/“女” GROUP BY Sex 各产生一个平均值

HAVING 子句只能与 SELECT 一起使用，且通常在 GROUP BY子句中使用，语法如下：

HAVING <searching condition>

示例

SELECT 课程名， COUNT(课程名) AS 重复数量 
FROM course
GROUP BY 课程名
HAVING COUNT(课程名)>1
ORDER BY 课程名

显示结果

其他提示

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

SELECT col1, col2
FROM table1
WHERE col3  > 5 AND col4 LIKE '%os%'
ORDER BY col5
LIMIT 10;

注意，你可以检索不同于 ORDER BY 和 WHERE 语句中所使用列的列。假定这些列名均以这样的方式（col1、col2、col3、col4、col5）存在于一个名为 table1 的表中，此查询便会很好地运行。

后续内容

在后续的 SQL 课程中，你将学习 JOIN（连接），聚合，子查询和临时表格。

JOIN 语句

Select query with INNER JOIN on multiple tables

SELECT column, another_table_column, …
FROM mytable
INNER JOIN another_table 
    ON mytable.id = another_table.id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

Select query with LEFT/RIGHT/FULL JOINs on multiple tables
SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table 
    ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

When joining table A to table B, a LEFT JOIN simply includes rows from A regardless of whether a matching row is found in B. The RIGHT JOIN is the same, but reversed, keeping rows in B regardless of whether a match is found in A. Finally, a FULL JOIN simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.

Select query with constraints on NULL values
SELECT column, another_column, …
FROM mytable
WHERE column IS/IS NOT NULL
  AND/OR another_condition
  AND/OR …;

正确的顺序

Complete SELECT query
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

INSERT INTO 语句

Insert statement with values for all columns

INSERT INTO mytable
(column, another_column, …)
VALUES (value_or_expr, another_value_or_expr, …),
       (value_or_expr_2, another_value_or_expr_2, …),
       …;

UPDATE 语句

用来更新 table 中的数据
Update statement with values

UPDATE mytable
SET column = value_or_expr, 
    other_column = another_value_or_expr, 
    …
WHERE condition;

一般 condition 里对行进行约束

删除行

Delete statement with condition

DELETE FROM mytable
WHERE condition;

创建表格

CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

Table data types

概括

命令